scripts/create-tables.sql

-- ==========================================
-- Speaking and Writing Tasks Tables
-- Run this SQL in the Supabase dashboard SQL editor
-- ==========================================

-- Create speaking_tasks table
CREATE TABLE speaking_tasks (
    id BIGINT NOT NULL,
    task_type TEXT NOT NULL,
    task_description TEXT,
    rephrased_task TEXT,
    vocabulary JSONB,
    response TEXT,
    response_alternative TEXT,
    brainstorm JSONB,
    brainstorm_alternative JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (id, task_type)  -- COMPOSITE PRIMARY KEY - ALLOWS SAME ID WITH DIFFERENT TASK_TYPES
);

-- Create index for task_type filtering (useful for your app)
CREATE INDEX IF NOT EXISTS speaking_tasks_task_type_idx 
ON speaking_tasks (task_type);

-- Enable Row Level Security
ALTER TABLE speaking_tasks ENABLE ROW LEVEL SECURITY;

-- RLS policies for speaking_tasks
CREATE POLICY "Allow read access to authenticated users" ON speaking_tasks
FOR SELECT USING (auth.role() = 'authenticated');

CREATE POLICY "Allow insert to service role" ON speaking_tasks
FOR INSERT WITH CHECK (auth.role() = 'service_role');

CREATE POLICY "Allow update/delete to service role" ON speaking_tasks
FOR ALL USING (auth.role() = 'service_role');

-- ==========================================

-- Create writing_tasks table
CREATE TABLE writing_tasks (
    id BIGINT NOT NULL,
    task_type TEXT NOT NULL,
    task_description TEXT,
    rephrased_task TEXT,
    vocabulary JSONB,
    response TEXT,
    response_alternative TEXT,
    brainstorm JSONB,
    brainstorm_alternative JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (id, task_type)  -- COMPOSITE PRIMARY KEY - ALLOWS SAME ID WITH DIFFERENT TASK_TYPES
);

-- Create index for task_type filtering (useful for your app)
CREATE INDEX IF NOT EXISTS writing_tasks_task_type_idx 
ON writing_tasks (task_type);

-- Enable Row Level Security
ALTER TABLE writing_tasks ENABLE ROW LEVEL SECURITY;

-- RLS policies for writing_tasks
CREATE POLICY "Allow read access to authenticated users" ON writing_tasks
FOR SELECT USING (auth.role() = 'authenticated');

CREATE POLICY "Allow insert to service role" ON writing_tasks
FOR INSERT WITH CHECK (auth.role() = 'service_role');

CREATE POLICY "Allow update/delete to service role" ON writing_tasks
FOR ALL USING (auth.role() = 'service_role');

-- ==========================================
-- Verification Queries (run these after creation)
-- ==========================================

-- Check if tables exist
SELECT table_name, table_type 
FROM information_schema.tables 
WHERE table_schema = 'public' 
AND table_name IN ('speaking_tasks', 'writing_tasks');

-- Check indexes
SELECT indexname, tablename 
FROM pg_indexes 
WHERE tablename IN ('speaking_tasks', 'writing_tasks')
AND schemaname = 'public';

-- Check RLS policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual 
FROM pg_policies 
WHERE tablename IN ('speaking_tasks', 'writing_tasks');