supabase_schema.sql

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 1. Primary Table: video_analyses
CREATE TABLE IF NOT EXISTS video_analyses (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    filename TEXT NOT NULL,
    file_size BIGINT NOT NULL,
    prediction TEXT NOT NULL CHECK (prediction IN ('real', 'fake')),
    confidence DECIMAL(5,4) NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
    models_used TEXT[] NOT NULL DEFAULT '{}',
    processing_time DECIMAL(6,2) NOT NULL DEFAULT 0,
    analysis_result JSONB,
    user_ip TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for video_analyses
CREATE INDEX IF NOT EXISTS idx_video_analyses_created_at ON video_analyses (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_video_analyses_prediction ON video_analyses (prediction);
CREATE INDEX IF NOT EXISTS idx_video_analyses_confidence ON video_analyses (confidence);

-- 2. Users Table
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(255),
  role VARCHAR(50) DEFAULT 'analyst',
  organization VARCHAR(255),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login TIMESTAMP
);

-- 3. Video Evidence Table (Encrypted)
CREATE TABLE IF NOT EXISTS video_evidence (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id VARCHAR(255) NOT NULL UNIQUE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  user_email VARCHAR(255),
  filename VARCHAR(255) NOT NULL,
  file_size BIGINT NOT NULL,
  file_hash VARCHAR(64) NOT NULL,
  mime_type VARCHAR(100),
  encrypted_link TEXT NOT NULL, -- AES-256-GCM encrypted Cloudinary links
  cloudinary_public_id VARCHAR(255),
  cloudinary_version INTEGER,
  status VARCHAR(50) DEFAULT 'uploaded',
  analysis_status VARCHAR(50) DEFAULT 'pending',
  analysis_result JSONB,
  prediction VARCHAR(50),
  confidence DECIMAL(5, 4),
  metadata JSONB DEFAULT '{}',
  upload_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  analysis_timestamp TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Forensic Analysis Table
CREATE TABLE IF NOT EXISTS forensic_analysis (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id VARCHAR(255) NOT NULL UNIQUE REFERENCES video_evidence(case_id),
  analysis_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  analyst_id UUID REFERENCES users(id),
  prediction VARCHAR(50) NOT NULL,
  confidence DECIMAL(5, 4) NOT NULL,
  preprocessing_results JSONB,
  core_model_results JSONB,
  postprocessing_results JSONB,
  forensic_certificate TEXT,
  chain_of_custody JSONB DEFAULT '{}',
  legal_admissibility VARCHAR(50),
  expert_recommendation TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. Access Logs Table (Audit Trail)
CREATE TABLE IF NOT EXISTS video_access_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id VARCHAR(255) NOT NULL,
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  user_email VARCHAR(255),
  action VARCHAR(100) NOT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Enable Row Level Security (RLS) on all tables
ALTER TABLE video_analyses ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE video_evidence ENABLE ROW LEVEL SECURITY;
ALTER TABLE forensic_analysis ENABLE ROW LEVEL SECURITY;
ALTER TABLE video_access_logs ENABLE ROW LEVEL SECURITY;

-- Create policies (examples - adjust as needed)
-- Allow public read access to video_analyses for now (as per current app usage)
CREATE POLICY "Public read access" ON video_analyses FOR SELECT USING (true);
CREATE POLICY "Public insert access" ON video_analyses FOR INSERT WITH CHECK (true);

-- Users can only see their own data
CREATE POLICY "Users can view own data" ON users FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update own data" ON users FOR UPDATE USING (auth.uid() = id);

-- Evidence policies
CREATE POLICY "Users can view own evidence" ON video_evidence FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own evidence" ON video_evidence FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Helper functions for timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
CREATE TRIGGER update_video_evidence_updated_at BEFORE UPDATE ON video_evidence FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
CREATE TRIGGER update_forensic_analysis_updated_at BEFORE UPDATE ON forensic_analysis FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();