sql/create_tables.sql

-- Supabase Database Schema for SanadCheck LLM API
-- Execute these scripts in your Supabase SQL Editor

-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create custom user roles enum
CREATE TYPE user_role AS ENUM ('user', 'admin', 'researcher');

-- Create users table (extends Supabase auth.users)
CREATE TABLE IF NOT EXISTS public.users (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email TEXT UNIQUE NOT NULL,
    username TEXT UNIQUE,
    full_name TEXT,
    role user_role DEFAULT 'user'::user_role,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_login TIMESTAMP WITH TIME ZONE,
    
    -- Constraints
    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT username_length CHECK (length(username) >= 3 AND length(username) <= 50),
    CONSTRAINT full_name_length CHECK (length(full_name) <= 100)
);

-- Create user_sessions table for JWT session management
CREATE TABLE IF NOT EXISTS public.user_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    access_token TEXT NOT NULL,
    refresh_token TEXT,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_used TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ended_at TIMESTAMP WITH TIME ZONE,
    user_agent TEXT,
    ip_address INET,
    
    -- Indexes for performance
    CONSTRAINT unique_access_token UNIQUE (access_token)
);

-- Create narrator_extractions table for storing extraction results
CREATE TABLE IF NOT EXISTS public.narrator_extractions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    session_id UUID REFERENCES public.user_sessions(id) ON DELETE SET NULL,
    hadith_text TEXT NOT NULL,
    extracted_narrators TEXT[] NOT NULL DEFAULT '{}',
    sanad_chain TEXT NOT NULL,
    success BOOLEAN NOT NULL DEFAULT FALSE,
    error_message TEXT,
    processing_time_ms INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ip_address INET,
    
    -- Constraints
    CONSTRAINT hadith_text_not_empty CHECK (length(trim(hadith_text)) > 0),
    CONSTRAINT processing_time_positive CHECK (processing_time_ms IS NULL OR processing_time_ms >= 0)
);

-- Create narrator_analyses table for storing individual narrator analysis results
CREATE TABLE IF NOT EXISTS public.narrator_analyses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    extraction_id UUID REFERENCES public.narrator_extractions(id) ON DELETE SET NULL,
    narrator_name TEXT NOT NULL,
    reliability_grade TEXT NOT NULL,
    confidence_level TEXT NOT NULL,
    reasoning TEXT NOT NULL,
    scholarly_consensus TEXT NOT NULL,
    known_issues TEXT,
    biographical_info TEXT NOT NULL,
    recommendation TEXT NOT NULL,
    success BOOLEAN NOT NULL DEFAULT FALSE,
    error_message TEXT,
    processing_time_ms INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT narrator_name_not_empty CHECK (length(trim(narrator_name)) > 0),
    CONSTRAINT reliability_grade_valid CHECK (reliability_grade IN ('Thiqah', 'Saduq', 'Da''if', 'Matruk', 'Majhul')),
    CONSTRAINT confidence_level_valid CHECK (confidence_level IN ('High', 'Medium', 'Low')),
    CONSTRAINT processing_time_positive CHECK (processing_time_ms IS NULL OR processing_time_ms >= 0)
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON public.user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_access_token ON public.user_sessions(access_token);
CREATE INDEX IF NOT EXISTS idx_user_sessions_expires_at ON public.user_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_user_sessions_active ON public.user_sessions(user_id) WHERE ended_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_narrator_extractions_user_id ON public.narrator_extractions(user_id);
CREATE INDEX IF NOT EXISTS idx_narrator_extractions_created_at ON public.narrator_extractions(created_at);
CREATE INDEX IF NOT EXISTS idx_narrator_extractions_success ON public.narrator_extractions(success);

CREATE INDEX IF NOT EXISTS idx_narrator_analyses_user_id ON public.narrator_analyses(user_id);
CREATE INDEX IF NOT EXISTS idx_narrator_analyses_extraction_id ON public.narrator_analyses(extraction_id);
CREATE INDEX IF NOT EXISTS idx_narrator_analyses_narrator_name ON public.narrator_analyses(narrator_name);
CREATE INDEX IF NOT EXISTS idx_narrator_analyses_created_at ON public.narrator_analyses(created_at);

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Add updated_at trigger to users table
CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON public.users 
    FOR EACH ROW 
    EXECUTE FUNCTION public.update_updated_at_column();

-- Row Level Security (RLS) Policies

-- Enable RLS on all tables
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.narrator_extractions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.narrator_analyses ENABLE ROW LEVEL SECURITY;

-- Users table policies
CREATE POLICY "Users can view own profile" ON public.users
    FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON public.users
    FOR UPDATE USING (auth.uid() = id);

CREATE POLICY "Service role can manage all users" ON public.users
    FOR ALL USING (auth.role() = 'service_role');

-- User sessions policies
CREATE POLICY "Users can view own sessions" ON public.user_sessions
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Service role can manage all sessions" ON public.user_sessions
    FOR ALL USING (auth.role() = 'service_role');

-- Narrator extractions policies
CREATE POLICY "Users can view own extractions" ON public.narrator_extractions
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own extractions" ON public.narrator_extractions
    FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Service role can manage all extractions" ON public.narrator_extractions
    FOR ALL USING (auth.role() = 'service_role');

-- Narrator analyses policies
CREATE POLICY "Users can view own analyses" ON public.narrator_analyses
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own analyses" ON public.narrator_analyses
    FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Service role can manage all analyses" ON public.narrator_analyses
    FOR ALL USING (auth.role() = 'service_role');

-- Grant necessary permissions
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA public TO anon, authenticated;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO anon, authenticated;

-- Create a function to automatically create user profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.users (id, email, username, full_name)
    VALUES (
        NEW.id,
        NEW.email,
        COALESCE(NEW.raw_user_meta_data->>'username', NULL),
        COALESCE(NEW.raw_user_meta_data->>'full_name', NULL)
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create trigger to run the function on new user signup
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Create a view for user analytics
CREATE VIEW public.user_analytics AS
SELECT 
    u.id,
    u.email,
    u.role,
    u.created_at,
    u.last_login,
    COUNT(ne.id) as total_extractions,
    COUNT(na.id) as total_analyses,
    COUNT(us.id) as total_sessions
FROM public.users u
LEFT JOIN public.narrator_extractions ne ON u.id = ne.user_id
LEFT JOIN public.narrator_analyses na ON u.id = na.user_id
LEFT JOIN public.user_sessions us ON u.id = us.user_id
GROUP BY u.id, u.email, u.role, u.created_at, u.last_login;

-- Grant select on the view
GRANT SELECT ON public.user_analytics TO authenticated;