-- ============================================================================
-- Supabase Manual Setup SQL for Deepfake Detection Backend
-- Run this in Supabase SQL Editor: https://supabase.com/dashboard/project/_/sql
-- ============================================================================

-- 1. Create the detection_results table
CREATE TABLE IF NOT EXISTS detection_results (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
    
    -- Essential Fields Only
    filename VARCHAR(255) NOT NULL,
    classification VARCHAR(20) NOT NULL,
    image_size INTEGER,
    prediction FLOAT NOT NULL,
    face_confidence FLOAT,
    processing_time_ms INTEGER,
    
    -- PDF Storage Info
    pdf_filename VARCHAR(255),
    pdf_url VARCHAR(500),
    bucket_name VARCHAR(100),
    file_size_mb FLOAT
);

-- 2. Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_detection_results_created_at ON detection_results(created_at);
CREATE INDEX IF NOT EXISTS idx_detection_results_classification ON detection_results(classification);

-- 3. Enable Row Level Security (RLS)
ALTER TABLE detection_results ENABLE ROW LEVEL SECURITY;

-- 4. Create permissive policies for backend operations
-- Note: These allow public access - adjust based on your security requirements

-- Allow all SELECT operations
DROP POLICY IF EXISTS "Allow all SELECT operations" ON detection_results;
CREATE POLICY "Allow all SELECT operations" ON detection_results
    FOR SELECT USING (true);

-- Allow all INSERT operations
DROP POLICY IF EXISTS "Allow all INSERT operations" ON detection_results;
CREATE POLICY "Allow all INSERT operations" ON detection_results
    FOR INSERT WITH CHECK (true);

-- Allow all UPDATE operations
DROP POLICY IF EXISTS "Allow all UPDATE operations" ON detection_results;
CREATE POLICY "Allow all UPDATE operations" ON detection_results
    FOR UPDATE USING (true) WITH CHECK (true);

-- Allow all DELETE operations
DROP POLICY IF EXISTS "Allow all DELETE operations" ON detection_results;
CREATE POLICY "Allow all DELETE operations" ON detection_results
    FOR DELETE USING (true);

-- ============================================================================
-- STORAGE BUCKET SETUP (Manual Steps via Dashboard)
-- ============================================================================
-- 
-- 1. Go to: Storage → Buckets
-- 2. Create a new bucket named: deepfake-reports
-- 3. Set as PUBLIC bucket (for PDF access)
-- 4. Go to bucket Policies tab and add these policies:
--
-- UPLOAD POLICY:
--    Policy Name: Allow public uploads
--    Allowed operation: INSERT
--    Target roles: public, anon, authenticated, service_role
--    Policy definition (custom): true
--
-- SELECT POLICY:
--    Policy Name: Allow public access
--    Allowed operation: SELECT
--    Target roles: public, anon, authenticated, service_role
--    Policy definition (custom): true
--
-- DELETE POLICY (optional):
--    Policy Name: Allow deletes
--    Allowed operation: DELETE
--    Target roles: authenticated, service_role
--    Policy definition (custom): true
--
-- ============================================================================

-- 5. Verify setup
SELECT 'Table created successfully' AS status, COUNT(*) AS row_count FROM detection_results;