Database Schema Generator

-- Snappy English Database Schema for Supabase
-- Phase 2: Create all tables with proper types and indexes
-- Run this in Supabase SQL Editor

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ======================
-- IDIOMS TABLE
-- ======================
CREATE TABLE public.idioms (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  phrase TEXT NOT NULL,
  meaning TEXT NOT NULL,
  origin TEXT,
  example_sentence TEXT NOT NULL,
  category TEXT NOT NULL CHECK (category IN ('animals', 'body', 'colours', 'food', 'weather', 'time', 'money', 'emotions', 'nature', 'general')),
  difficulty TEXT NOT NULL CHECK (difficulty IN ('beginner', 'intermediate', 'advanced')),
  thinking_style TEXT DEFAULT 'universal' CHECK (thinking_style IN ('efficient', 'critical', 'creative', 'universal')),
  image_url TEXT,
  video_url TEXT,
  pronunciation_tip TEXT,
  usage_note TEXT,
  mnemonic_tip TEXT,
  visual_cue TEXT,
  is_premium BOOLEAN DEFAULT false,
  is_approved BOOLEAN DEFAULT false,
  contributor_name TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT
);

-- Idioms indexes
CREATE INDEX idx_idioms_category ON public.idioms(category);
CREATE INDEX idx_idioms_difficulty ON public.idioms(difficulty);
CREATE INDEX idx_idioms_thinking_style ON public.idioms(thinking_style);
CREATE INDEX idx_idioms_approved ON public.idioms(is_approved);

-- ======================
-- GRAMMAR TOPICS TABLE
-- ======================
CREATE TABLE public.grammar_topics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  explanation TEXT NOT NULL,
  correct_examples TEXT[] NOT NULL,
  common_mistakes TEXT[],
  pdf_url TEXT,
  is_premium_pdf BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT
);

CREATE INDEX idx_grammar_category ON public.grammar_topics(category);

-- ======================
-- USER PROGRESS TABLE
-- ======================
CREATE TABLE public.user_progress (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_email TEXT NOT NULL,
  idiom_id UUID NOT NULL REFERENCES public.idioms(id) ON DELETE CASCADE,
  status TEXT DEFAULT 'new' CHECK (status IN ('new', 'learning', 'practising', 'mastered')),
  times_practiced INTEGER DEFAULT 0,
  times_correct INTEGER DEFAULT 0,
  last_practiced TIMESTAMPTZ,
  is_favourite BOOLEAN DEFAULT false,
  custom_sentence TEXT,
  notes TEXT,
  custom_mnemonic TEXT,
  mnemonic_type TEXT CHECK (mnemonic_type IN ('visual', 'story', 'rhyme', 'association', 'acronym', 'other')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  UNIQUE(user_email, idiom_id)
);

CREATE INDEX idx_progress_user ON public.user_progress(user_email);
CREATE INDEX idx_progress_status ON public.user_progress(status);

-- ======================
-- DAILY CROSSWORD TABLE
-- ======================
CREATE TABLE public.daily_crosswords (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  puzzle_date DATE NOT NULL UNIQUE,
  difficulty TEXT DEFAULT 'standard' CHECK (difficulty IN ('mini', 'standard', 'cryptic')),
  thinking_style TEXT DEFAULT 'universal' CHECK (thinking_style IN ('efficient', 'critical', 'creative', 'universal')),
  grid_size INTEGER NOT NULL,
  puzzle_data JSONB NOT NULL,
  clues_across JSONB NOT NULL,
  clues_down JSONB NOT NULL,
  total_words INTEGER,
  estimated_time_minutes INTEGER,
  average_solve_time NUMERIC DEFAULT 0,
  total_attempts INTEGER DEFAULT 0,
  completion_rate NUMERIC DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT
);

CREATE INDEX idx_crossword_date ON public.daily_crosswords(puzzle_date);
CREATE INDEX idx_crossword_difficulty ON public.daily_crosswords(difficulty);

-- ======================
-- VOCABULARY WORDS TABLE
-- ======================
CREATE TABLE public.vocabulary_words (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  word TEXT NOT NULL,
  part_of_speech TEXT NOT NULL,
  definition TEXT NOT NULL,
  example_sentence TEXT NOT NULL,
  phonetic_spelling TEXT,
  topic TEXT,
  level TEXT DEFAULT 'A2' CHECK (level IN ('A1', 'A2', 'B1', 'B2', 'C1', 'C2')),
  syllable_count INTEGER,
  syllable_breaks TEXT,
  stress_pattern TEXT,
  stress_position INTEGER,
  morphology_data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT
);

CREATE INDEX idx_vocab_topic ON public.vocabulary_words(topic);
CREATE INDEX idx_vocab_level ON public.vocabulary_words(level);

-- ======================
-- CURRENCY PACKS TABLE (for Shop)
-- ======================
CREATE TABLE public.currency_packs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  pack_name TEXT NOT NULL,
  pearl_count INTEGER DEFAULT 0,
  egg_count INTEGER DEFAULT 0,
  bonus_amount INTEGER DEFAULT 0,
  price_display TEXT NOT NULL,
  stripe_price_id TEXT NOT NULL UNIQUE,
  is_popular BOOLEAN DEFAULT false,
  display_order INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_packs_active ON public.currency_packs(is_active);
CREATE INDEX idx_packs_order ON public.currency_packs(display_order);

-- ======================
-- CONVERSATION SESSIONS TABLE
-- ======================
CREATE TABLE public.conversation_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_email TEXT NOT NULL,
  agent_name TEXT DEFAULT 'conversational_tutor',
  conversation_id TEXT NOT NULL,
  duration_minutes INTEGER DEFAULT 0,
  messages_exchanged INTEGER DEFAULT 0,
  idioms_detected TEXT[],
  idioms_used_correctly INTEGER DEFAULT 0,
  pearls_earned INTEGER DEFAULT 0,
  session_quality_score NUMERIC,
  session_start TIMESTAMPTZ,
  session_end TIMESTAMPTZ,
  topics_discussed TEXT[],
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT
);

CREATE INDEX idx_sessions_user ON public.conversation_sessions(user_email);

-- ======================
-- Row Level Security (RLS) Policies
-- ======================

-- 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;

-- Idioms: Everyone can read, only contributors can write
CREATE POLICY "Idioms are viewable by everyone" ON public.idioms
  FOR SELECT USING (true);

CREATE POLICY "Users can insert idioms" ON public.idioms
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

-- Grammar: Everyone can read
CREATE POLICY "Grammar topics are viewable by everyone" ON public.grammar_topics
  FOR SELECT USING (true);

-- User Progress: Users can only see/modify their own
CREATE POLICY "Users can view own progress" ON public.user_progress
  FOR SELECT USING (user_email = auth.email());

CREATE POLICY "Users can insert own progress" ON public.user_progress
  FOR INSERT WITH CHECK (user_email = auth.email());

CREATE POLICY "Users can update own progress" ON public.user_progress
  FOR UPDATE USING (user_email = auth.email());

-- Crosswords: Everyone can read
CREATE POLICY "Crosswords are viewable by everyone" ON public.daily_crosswords
  FOR SELECT USING (true);

-- Vocabulary: Everyone can read
CREATE POLICY "Vocabulary is viewable by everyone" ON public.vocabulary_words
  FOR SELECT USING (true);

-- Currency Packs: Everyone can read
CREATE POLICY "Currency packs are viewable by everyone" ON public.currency_packs
  FOR SELECT USING (true);

-- Conversation Sessions: Users can only see their own
CREATE POLICY "Users can view own sessions" ON public.conversation_sessions
  FOR SELECT USING (user_email = auth.email());

-- ======================
-- Updated_at trigger function
-- ======================
CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply updated_at triggers
CREATE TRIGGER set_updated_at BEFORE UPDATE ON public.idioms
  FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();

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

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

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

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

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

-- ======================
-- Success message
-- ======================
DO $$ 
BEGIN 
  RAISE NOTICE 'Database schema created successfully! 🎉';
  RAISE NOTICE 'Tables: idioms, grammar_topics, user_progress, daily_crosswords, vocabulary_words, currency_packs, conversation_sessions';
  RAISE NOTICE 'RLS policies enabled for security';
  RAISE NOTICE 'Indexes created for performance';
  RAISE NOTICE 'Ready for Phase 3: Data Migration';
END $$;