# === Database Schema (Example - Create these tables in your Supabase project) ===
"""
-- users table (Supabase Auth handles this mostly, but you might add custom fields)
CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email VARCHAR(255) UNIQUE,
    credits INTEGER DEFAULT 30, -- Example credit system
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL,
    suspended BOOLEAN DEFAULT FALSE
    -- Add other profile fields as needed
);
-- Function to automatically create a profile when a new user signs up in Auth
create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (id, email)
  values (new.id, new.email);
  return new;
end;
$$;
-- Trigger to call the function after a user is inserted into auth.users
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- study_materials table
CREATE TABLE IF NOT EXISTS public.study_materials (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
    type VARCHAR(50) NOT NULL, -- 'pdf', 'youtube', 'wiki', 'bible', 'arxiv', 'text'
    source_ref TEXT NOT NULL, -- URL, Bible reference, ArXiv ID, filename, or part of the text prompt
    source_content TEXT, -- Store extracted text here (optional, can be large)
    created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL,
    title TEXT -- Optional: Title extracted or generated
);

-- notes table
CREATE TABLE IF NOT EXISTS public.notes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    material_id UUID REFERENCES public.study_materials(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
    content TEXT NOT NULL, -- The generated notes
    created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL,
    tts_audio_url TEXT -- URL to the TTS audio file in Supabase Storage
);

-- quizzes table
CREATE TABLE IF NOT EXISTS public.quizzes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    notes_id UUID REFERENCES public.notes(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
    difficulty VARCHAR(10) NOT NULL, -- 'easy', 'medium', 'hard'
    questions JSONB NOT NULL, -- Store the list of question objects {question: "", options: {A:"", B:"", C:"", D:""}, correct_answer: "A"}
    created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- quiz_attempts table
CREATE TABLE IF NOT EXISTS public.quiz_attempts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    quiz_id UUID REFERENCES public.quizzes(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
    score NUMERIC(5, 2) NOT NULL, -- e.g., 85.00 for 85%
    answers JSONB NOT NULL, -- Store the user's answers {question_index: "selected_option"}
    submitted_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);
"""