scripts/phase2_migration.sql

-- Phase 2: User Analysis History Database Migration
-- This script creates the user_analysis_history table and related indexes
-- Execute this in your Supabase SQL Editor

-- Step 1: Create the user_analysis_history table
CREATE TABLE IF NOT EXISTS user_analysis_history (
    id              UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id      VARCHAR(36)  NOT NULL,
    article_id      VARCHAR(36)  NOT NULL UNIQUE,
    text_preview    VARCHAR(200) NOT NULL,
    predicted_label VARCHAR(50)  NOT NULL CHECK (predicted_label IN ('True', 'Fake', 'Satire', 'Bias')),
    confidence      FLOAT        NOT NULL CHECK (confidence >= 0.0 AND confidence <= 1.0),
    model_name      VARCHAR(100) NOT NULL,
    created_at      TIMESTAMPTZ  DEFAULT NOW() NOT NULL,

    CONSTRAINT fk_article FOREIGN KEY (article_id) REFERENCES predictions(article_id) ON DELETE CASCADE
);

-- Step 2: Create indexes for efficient queries
CREATE INDEX IF NOT EXISTS idx_history_session_created ON user_analysis_history(session_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_history_article ON user_analysis_history(article_id);

-- Step 3: Enable row-level security
ALTER TABLE user_analysis_history ENABLE ROW LEVEL SECURITY;

-- Step 4: Create policy to allow all operations (for development)
-- Note: In production, you should restrict this based on your security requirements
DROP POLICY IF EXISTS "allow_all_history" ON user_analysis_history;
CREATE POLICY "allow_all_history" ON user_analysis_history FOR ALL USING (true) WITH CHECK (true);

-- Step 5: Verify the table was created
SELECT 
    table_name, 
    column_name, 
    data_type, 
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name = 'user_analysis_history'
ORDER BY ordinal_position;

-- Step 6: Verify indexes were created
SELECT 
    indexname,
    indexdef
FROM pg_indexes 
WHERE tablename = 'user_analysis_history';

-- Step 7: Verify RLS policy was created
SELECT 
    policyname,
    permissive,
    roles,
    cmd
FROM pg_policies 
WHERE tablename = 'user_analysis_history';

-- Optional: Insert a test record to verify everything works
-- Uncomment the following lines to test (replace with actual article_id from predictions table)
/*
DO $$
DECLARE
    test_article_id VARCHAR(36);
    test_session_id VARCHAR(36);
BEGIN
    -- First, insert a test prediction
    test_article_id := gen_random_uuid()::text;
    test_session_id := gen_random_uuid()::text;
    
    INSERT INTO predictions (article_id, text, predicted_label, confidence, model_name)
    VALUES (test_article_id, 'Test article for migration verification', 'True', 0.95, 'ensemble');
    
    -- Then, insert a test history record
    INSERT INTO user_analysis_history (session_id, article_id, text_preview, predicted_label, confidence, model_name)
    VALUES (test_session_id, test_article_id, 'Test article for migration verification', 'True', 0.95, 'ensemble');
    
    -- Verify the record was inserted
    IF EXISTS (SELECT 1 FROM user_analysis_history WHERE article_id = test_article_id) THEN
        RAISE NOTICE 'Test record inserted successfully!';
    ELSE
        RAISE EXCEPTION 'Test record insertion failed!';
    END IF;
    
    -- Clean up test data
    DELETE FROM user_analysis_history WHERE article_id = test_article_id;
    DELETE FROM predictions WHERE article_id = test_article_id;
    
    RAISE NOTICE 'Test data cleaned up. Migration verification complete!';
END $$;
*/