-- 🚀 SNAPPY ENGLISH - FULL SUPABASE SCHEMA
-- This creates all 7 production tables for the hybrid migration
-- Copy this entire script and run it in Supabase SQL Editor

-- ===================================
-- ENABLE UUID EXTENSION
-- ===================================
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ===================================
-- 1. IDIOMS TABLE
-- ===================================
CREATE TABLE IF NOT EXISTS public.idioms (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_date TIMESTAMPTZ DEFAULT NOW(),
    updated_date TIMESTAMPTZ DEFAULT NOW(),
    created_by TEXT,
    
    -- Core fields
    phrase TEXT NOT NULL,
    meaning TEXT NOT NULL,
    origin TEXT,
    example_sentence TEXT NOT NULL,
    category TEXT NOT NULL,
    difficulty TEXT NOT NULL,
    thinking_style TEXT DEFAULT 'universal',
    thinking_style_tags TEXT[] DEFAULT '{}',
    
    -- Media
    image_url TEXT,
    video_url TEXT,
    animation_url TEXT,
    
    -- Learning aids
    pronunciation_tip TEXT,
    usage_note TEXT,
    mnemonic_tip TEXT,
    visual_cue TEXT,
    qr_code_url TEXT,
    
    -- Status
    is_premium BOOLEAN DEFAULT false,
    is_approved BOOLEAN DEFAULT false,
    contributor_name TEXT,
    
    CONSTRAINT idioms_category_check CHECK (category IN ('animals', 'body', 'colours', 'food', 'weather', 'time', 'money', 'emotions', 'nature', 'general')),
    CONSTRAINT idioms_difficulty_check CHECK (difficulty IN ('beginner', 'intermediate', 'advanced')),
    CONSTRAINT idioms_thinking_style_check CHECK (thinking_style IN ('efficient', 'critical', 'creative', 'universal'))
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_idioms_category ON public.idioms(category);
CREATE INDEX IF NOT EXISTS idx_idioms_difficulty ON public.idioms(difficulty);
CREATE INDEX IF NOT EXISTS idx_idioms_thinking_style ON public.idioms(thinking_style);
CREATE INDEX IF NOT EXISTS idx_idioms_is_approved ON public.idioms(is_approved);

-- ===================================
-- 2. GRAMMAR TOPICS TABLE
-- ===================================
CREATE TABLE IF NOT EXISTS public.grammar_topics (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_date TIMESTAMPTZ DEFAULT NOW(),
    updated_date TIMESTAMPTZ DEFAULT NOW(),
    created_by TEXT,
    
    -- Core fields
    title TEXT NOT NULL,
    category TEXT,
    explanation TEXT NOT NULL,
    correct_examples TEXT[] DEFAULT '{}',
    common_mistakes TEXT[] DEFAULT '{}',
    
    -- Resources
    pdf_url TEXT,
    is_premium_pdf BOOLEAN DEFAULT false
);

CREATE INDEX IF NOT EXISTS idx_grammar_category ON public.grammar_topics(category);

-- ===================================
-- 3. USER PROGRESS TABLE
-- ===================================
CREATE TABLE IF NOT EXISTS public.user_progress (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_date TIMESTAMPTZ DEFAULT NOW(),
    updated_date TIMESTAMPTZ DEFAULT NOW(),
    created_by TEXT NOT NULL,
    
    -- Progress tracking
    idiom_id UUID REFERENCES public.idioms(id) ON DELETE CASCADE,
    status TEXT DEFAULT 'new',
    times_practiced INTEGER DEFAULT 0,
    times_correct INTEGER DEFAULT 0,
    last_practiced TIMESTAMPTZ,
    
    -- Personal notes
    is_favourite BOOLEAN DEFAULT false,
    custom_sentence TEXT,
    notes TEXT,
    custom_mnemonic TEXT,
    mnemonic_type TEXT,
    
    CONSTRAINT user_progress_status_check CHECK (status IN ('new', 'learning', 'practising', 'mastered')),
    CONSTRAINT user_progress_mnemonic_type_check CHECK (mnemonic_type IN ('visual', 'story', 'rhyme', 'association', 'acronym', 'other'))
);

CREATE INDEX IF NOT EXISTS idx_user_progress_created_by ON public.user_progress(created_by);
CREATE INDEX IF NOT EXISTS idx_user_progress_idiom_id ON public.user_progress(idiom_id);
CREATE INDEX IF NOT EXISTS idx_user_progress_status ON public.user_progress(status);

-- ===================================
-- 4. DAILY CROSSWORDS TABLE
-- ===================================
CREATE TABLE IF NOT EXISTS public.daily_crosswords (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_date TIMESTAMPTZ DEFAULT NOW(),
    updated_date TIMESTAMPTZ DEFAULT NOW(),
    created_by TEXT,
    
    -- Puzzle metadata
    puzzle_date DATE NOT NULL UNIQUE,
    difficulty TEXT DEFAULT 'standard',
    thinking_style TEXT DEFAULT 'universal',
    grid_size INTEGER NOT NULL,
    
    -- Puzzle data (stored as JSONB for flexibility)
    puzzle_data JSONB NOT NULL,
    clues_across JSONB NOT NULL,
    clues_down JSONB NOT NULL,
    
    -- Stats
    total_words INTEGER,
    estimated_time_minutes INTEGER,
    average_solve_time NUMERIC DEFAULT 0,
    total_attempts INTEGER DEFAULT 0,
    completion_rate NUMERIC DEFAULT 0,
    
    CONSTRAINT daily_crosswords_difficulty_check CHECK (difficulty IN ('mini', 'standard', 'cryptic')),
    CONSTRAINT daily_crosswords_thinking_style_check CHECK (thinking_style IN ('efficient', 'critical', 'creative', 'universal'))
);

CREATE INDEX IF NOT EXISTS idx_crosswords_puzzle_date ON public.daily_crosswords(puzzle_date);
CREATE INDEX IF NOT EXISTS idx_crosswords_difficulty ON public.daily_crosswords(difficulty);
CREATE INDEX IF NOT EXISTS idx_crosswords_thinking_style ON public.daily_crosswords(thinking_style);

-- ===================================
-- 5. VOCABULARY WORDS TABLE
-- ===================================
CREATE TABLE IF NOT EXISTS public.vocabulary_words (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_date TIMESTAMPTZ DEFAULT NOW(),
    updated_date TIMESTAMPTZ DEFAULT NOW(),
    created_by TEXT,
    
    -- Word data
    word TEXT NOT NULL,
    part_of_speech TEXT NOT NULL,
    definition TEXT NOT NULL,
    example_sentence TEXT NOT NULL,
    phonetic_spelling TEXT,
    
    -- Classification
    topic TEXT,
    level TEXT DEFAULT 'A2',
    
    -- Analysis (from AI)
    syllable_count INTEGER,
    syllable_breaks TEXT,
    stress_pattern TEXT,
    stress_position INTEGER,
    morphology_data JSONB,
    
    CONSTRAINT vocabulary_level_check CHECK (level IN ('A1', 'A2', 'B1', 'B2', 'C1', 'C2'))
);

CREATE INDEX IF NOT EXISTS idx_vocabulary_word ON public.vocabulary_words(word);
CREATE INDEX IF NOT EXISTS idx_vocabulary_level ON public.vocabulary_words(level);
CREATE INDEX IF NOT EXISTS idx_vocabulary_topic ON public.vocabulary_words(topic);

-- ===================================
-- 6. CURRENCY PACKS TABLE
-- ===================================
CREATE TABLE IF NOT EXISTS public.currency_packs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_date TIMESTAMPTZ DEFAULT NOW(),
    updated_date TIMESTAMPTZ DEFAULT NOW(),
    created_by TEXT,
    
    -- Pack details
    pack_name TEXT NOT NULL,
    pearl_count INTEGER DEFAULT 0,
    egg_count INTEGER DEFAULT 0,
    bonus_amount INTEGER DEFAULT 0,
    
    -- Pricing
    price_display TEXT NOT NULL,
    stripe_price_id TEXT NOT NULL UNIQUE,
    
    -- Display
    is_popular BOOLEAN DEFAULT false,
    display_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_currency_packs_active ON public.currency_packs(is_active);
CREATE INDEX IF NOT EXISTS idx_currency_packs_stripe_price_id ON public.currency_packs(stripe_price_id);

-- ===================================
-- 7. CONVERSATION SESSIONS TABLE
-- ===================================
CREATE TABLE IF NOT EXISTS public.conversation_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_date TIMESTAMPTZ DEFAULT NOW(),
    updated_date TIMESTAMPTZ DEFAULT NOW(),
    created_by TEXT NOT NULL,
    
    -- Session metadata
    user_email TEXT NOT NULL,
    agent_name TEXT DEFAULT 'conversational_tutor',
    conversation_id TEXT NOT NULL,
    
    -- Session data
    duration_minutes INTEGER DEFAULT 0,
    messages_exchanged INTEGER DEFAULT 0,
    idioms_detected TEXT[] DEFAULT '{}',
    idioms_used_correctly INTEGER DEFAULT 0,
    
    -- Rewards & metrics
    pearls_earned INTEGER DEFAULT 0,
    milestones_reached TEXT[] DEFAULT '{}',
    session_quality_score NUMERIC,
    
    -- Timing
    session_start TIMESTAMPTZ,
    session_end TIMESTAMPTZ,
    
    -- Topics
    topics_discussed TEXT[] DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS idx_conversation_user_email ON public.conversation_sessions(user_email);
CREATE INDEX IF NOT EXISTS idx_conversation_created_by ON public.conversation_sessions(created_by);
CREATE INDEX IF NOT EXISTS idx_conversation_agent ON public.conversation_sessions(agent_name);

-- ===================================
-- TRIGGERS FOR UPDATED_DATE
-- ===================================
CREATE OR REPLACE FUNCTION update_updated_date()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_date = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER idioms_updated_date BEFORE UPDATE ON public.idioms
    FOR EACH ROW EXECUTE FUNCTION update_updated_date();

CREATE TRIGGER grammar_topics_updated_date BEFORE UPDATE ON public.grammar_topics
    FOR EACH ROW EXECUTE FUNCTION update_updated_date();

CREATE TRIGGER user_progress_updated_date BEFORE UPDATE ON public.user_progress
    FOR EACH ROW EXECUTE FUNCTION update_updated_date();

CREATE TRIGGER daily_crosswords_updated_date BEFORE UPDATE ON public.daily_crosswords
    FOR EACH ROW EXECUTE FUNCTION update_updated_date();

CREATE TRIGGER vocabulary_words_updated_date BEFORE UPDATE ON public.vocabulary_words
    FOR EACH ROW EXECUTE FUNCTION update_updated_date();

CREATE TRIGGER currency_packs_updated_date BEFORE UPDATE ON public.currency_packs
    FOR EACH ROW EXECUTE FUNCTION update_updated_date();

CREATE TRIGGER conversation_sessions_updated_date BEFORE UPDATE ON public.conversation_sessions
    FOR EACH ROW EXECUTE FUNCTION update_updated_date();

-- ===================================
-- ROW LEVEL SECURITY (RLS)
-- ===================================
-- Enable RLS on all tables
ALTER TABLE public.idioms ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.grammar_topics ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.daily_crosswords ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.vocabulary_words ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.currency_packs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.conversation_sessions ENABLE ROW LEVEL SECURITY;

-- Allow service role (backend functions) full access
CREATE POLICY "Service role full access" ON public.idioms FOR ALL USING (true);
CREATE POLICY "Service role full access" ON public.grammar_topics FOR ALL USING (true);
CREATE POLICY "Service role full access" ON public.user_progress FOR ALL USING (true);
CREATE POLICY "Service role full access" ON public.daily_crosswords FOR ALL USING (true);
CREATE POLICY "Service role full access" ON public.vocabulary_words FOR ALL USING (true);
CREATE POLICY "Service role full access" ON public.currency_packs FOR ALL USING (true);
CREATE POLICY "Service role full access" ON public.conversation_sessions FOR ALL USING (true);

-- ===================================
-- SUCCESS MESSAGE
-- ===================================
DO $$
BEGIN
    RAISE NOTICE '✅ SUCCESS! All 7 tables created successfully!';
    RAISE NOTICE '📊 Tables created: idioms, grammar_topics, user_progress, daily_crosswords, vocabulary_words, currency_packs, conversation_sessions';
    RAISE NOTICE '🔒 Row Level Security enabled on all tables';
    RAISE NOTICE '🎯 Ready for Phase 3: Code Migration!';
END $$;