schema.sql

-- Supabase schema for FIPI tasks and user practice attempts.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE IF NOT EXISTS public.fipi_tasks (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    source_url TEXT UNIQUE NOT NULL,
    task_type TEXT DEFAULT 'other',
    images TEXT[] DEFAULT '{}',
    variants TEXT[] DEFAULT '{}',
    rubert_analysis JSONB DEFAULT '{}',
    scraped_at TIMESTAMPTZ DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_fipi_tasks_task_type ON public.fipi_tasks(task_type);
CREATE INDEX IF NOT EXISTS idx_fipi_tasks_scraped_at ON public.fipi_tasks(scraped_at DESC);
CREATE INDEX IF NOT EXISTS idx_fipi_tasks_source_url ON public.fipi_tasks(source_url);
CREATE INDEX IF NOT EXISTS idx_fipi_tasks_title ON public.fipi_tasks USING gin(title gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_fipi_tasks_content ON public.fipi_tasks USING gin(content gin_trgm_ops);

ALTER TABLE public.fipi_tasks ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Public can view all tasks" ON public.fipi_tasks;
DROP POLICY IF EXISTS "Service key can insert tasks" ON public.fipi_tasks;
DROP POLICY IF EXISTS "Service key can update tasks" ON public.fipi_tasks;
DROP POLICY IF EXISTS "Service key can delete tasks" ON public.fipi_tasks;

CREATE POLICY "Public can view all tasks"
    ON public.fipi_tasks
    FOR SELECT
    USING (true);

CREATE POLICY "Service key can insert tasks"
    ON public.fipi_tasks
    FOR INSERT
    WITH CHECK (true);

CREATE POLICY "Service key can update tasks"
    ON public.fipi_tasks
    FOR UPDATE
    USING (true);

CREATE POLICY "Service key can delete tasks"
    ON public.fipi_tasks
    FOR DELETE
    USING (true);

CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_fipi_tasks_updated_at ON public.fipi_tasks;
CREATE TRIGGER update_fipi_tasks_updated_at
    BEFORE UPDATE ON public.fipi_tasks
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE OR REPLACE VIEW public.fipi_tasks_stats AS
SELECT
    COUNT(*) AS total_tasks,
    COUNT(*) FILTER (WHERE task_type = 'writing') AS writing_tasks,
    COUNT(*) FILTER (WHERE task_type = 'test') AS test_tasks,
    COUNT(*) FILTER (WHERE task_type = 'listening') AS listening_tasks,
    COUNT(*) FILTER (WHERE task_type = 'reading') AS reading_tasks,
    COUNT(*) FILTER (WHERE task_type = 'other') AS other_tasks,
    MAX(scraped_at) AS last_scrape
FROM public.fipi_tasks;

CREATE TABLE IF NOT EXISTS public.task_attempts (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    user_id UUID REFERENCES auth.users NOT NULL,
    task_id BIGINT REFERENCES public.fipi_tasks(id) ON DELETE CASCADE NOT NULL,
    submitted_answer TEXT NOT NULL,
    is_correct BOOLEAN NOT NULL,
    check_status TEXT NOT NULL DEFAULT 'not_checked'
);

ALTER TABLE public.task_attempts ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can insert their own task attempts" ON public.task_attempts;
DROP POLICY IF EXISTS "Users can view their own task attempts" ON public.task_attempts;
DROP POLICY IF EXISTS "Users can update their own task attempts" ON public.task_attempts;
DROP POLICY IF EXISTS "Users can delete their own task attempts" ON public.task_attempts;

CREATE POLICY "Users can insert their own task attempts"
    ON public.task_attempts
    FOR INSERT
    WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can view their own task attempts"
    ON public.task_attempts
    FOR SELECT
    USING (auth.uid() = user_id);

CREATE POLICY "Users can update their own task attempts"
    ON public.task_attempts
    FOR UPDATE
    USING (auth.uid() = user_id)
    WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete their own task attempts"
    ON public.task_attempts
    FOR DELETE
    USING (auth.uid() = user_id);

CREATE INDEX IF NOT EXISTS idx_task_attempts_user_created_at ON public.task_attempts(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_task_attempts_task_id ON public.task_attempts(task_id);