Supabase Migration SQL v4.2 - Fixed

-- =====================================================
-- Snappy English - Supabase Migration SQL v4.2 (FEATURED_ANIMATION FIX)
-- Generated: 2026-09-25T18:38:38.767Z
-- Description: Complete database schema with production-ready RLS policies
-- FIX: Removed is_active column from featured_animation (not in entity schema)
-- IDEMPOTENT: Safe to re-run multiple times
-- =====================================================

-- Enable pgcrypto extension
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- =====================================================
-- SECURITY HELPER FUNCTIONS (CRITICAL)
-- =====================================================

-- Returns current user's email (SECURITY DEFINER to bypass RLS circular dependency)
CREATE OR REPLACE FUNCTION public.current_user_email()
RETURNS text
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT u.email
  FROM public.users u
  WHERE u.id = auth.uid()
  LIMIT 1;
$$;

-- Returns true if current user is admin
CREATE OR REPLACE FUNCTION public.current_user_is_admin()
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.users u
    WHERE u.id = auth.uid() AND u.role = 'admin'
  );
$$;

-- Grant execute permissions
REVOKE ALL ON FUNCTION public.current_user_email() FROM PUBLIC;
REVOKE ALL ON FUNCTION public.current_user_is_admin() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.current_user_email() TO anon, authenticated;
GRANT EXECUTE ON FUNCTION public.current_user_is_admin() TO anon, authenticated;

-- =====================================================
-- CORE TABLES
-- =====================================================

-- Users table (extends Supabase auth.users)
CREATE TABLE IF NOT EXISTS public.users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  role TEXT DEFAULT 'user' CHECK (role IN ('user', 'admin')),
  profession TEXT,
  native_language TEXT,
  country_of_domicile TEXT,
  thinking_style TEXT DEFAULT 'undetermined',
  is_premium BOOLEAN DEFAULT false,
  is_educator BOOLEAN DEFAULT false,
  pearls_of_wisdom INTEGER DEFAULT 0,
  golden_eggs INTEGER DEFAULT 0,
  referral_code TEXT,
  profile_completed BOOLEAN DEFAULT false,
  quiz_completed BOOLEAN DEFAULT false,
  onboarding_step INTEGER DEFAULT 0,
  stripe_customer_id TEXT,
  subscription_id TEXT,
  subscription_status TEXT,
  last_active TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for users
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_referral_code ON public.users(referral_code) WHERE referral_code IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_email ON public.users(email);

-- Idioms
CREATE TABLE IF NOT EXISTS public.idioms (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  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')),
  thinking_style_tags TEXT[],
  image_url TEXT,
  video_url TEXT,
  animation_url TEXT,
  pronunciation_tip TEXT,
  usage_note TEXT,
  mnemonic_tip TEXT,
  visual_cue TEXT,
  qr_code_url TEXT,
  is_premium BOOLEAN DEFAULT false,
  is_approved BOOLEAN DEFAULT false,
  contributor_name TEXT
);

-- Indexes for idioms
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_approved ON public.idioms(is_approved);
CREATE INDEX IF NOT EXISTS idx_idioms_thinking_style_tags_gin ON public.idioms USING GIN (thinking_style_tags);

-- User Progress
CREATE TABLE IF NOT EXISTS public.user_progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by 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'))
);

-- Indexes for user progress
CREATE INDEX IF NOT EXISTS idx_user_progress_user ON public.user_progress(created_by);
CREATE INDEX IF NOT EXISTS idx_user_progress_idiom ON public.user_progress(idiom_id);

-- Grammar Topics
CREATE TABLE IF NOT EXISTS public.grammar_topics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  title TEXT NOT NULL,
  category TEXT,
  explanation TEXT NOT NULL,
  correct_examples TEXT[],
  common_mistakes TEXT[],
  pdf_url TEXT,
  is_premium_pdf BOOLEAN DEFAULT false
);

-- Grammar Access
CREATE TABLE IF NOT EXISTS public.grammar_access (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  user_email TEXT NOT NULL,
  grammar_topic_id UUID NOT NULL REFERENCES public.grammar_topics(id) ON DELETE CASCADE,
  unlocked_date TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_grammar_access_user_email ON public.grammar_access(user_email);

-- Animations
CREATE TABLE IF NOT EXISTS public.animations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  title TEXT NOT NULL,
  idiom_id UUID NOT NULL REFERENCES public.idioms(id) ON DELETE CASCADE,
  video_url TEXT NOT NULL,
  thumbnail_url TEXT,
  description TEXT,
  is_featured BOOLEAN DEFAULT false,
  featured_from TIMESTAMPTZ,
  featured_until TIMESTAMPTZ,
  display_order INTEGER
);

-- Featured Animation (FIXED - removed is_active column)
CREATE TABLE IF NOT EXISTS public.featured_animation (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  phrase TEXT NOT NULL,
  meaning TEXT NOT NULL,
  example TEXT NOT NULL,
  video_url TEXT NOT NULL
);

-- Vocabulary Words
CREATE TABLE IF NOT EXISTS public.vocabulary_words (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  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 TEXT
);

-- Vocabulary Packs
CREATE TABLE IF NOT EXISTS public.vocabulary_packs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  pack_name TEXT NOT NULL,
  description TEXT,
  pearl_cost INTEGER DEFAULT 0,
  egg_cost INTEGER DEFAULT 0,
  is_premium_only BOOLEAN DEFAULT false
);

-- Vocabulary Pack Words (junction table)
CREATE TABLE IF NOT EXISTS public.vocabulary_pack_words (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  pack_id UUID NOT NULL REFERENCES public.vocabulary_packs(id) ON DELETE CASCADE,
  word_id UUID NOT NULL REFERENCES public.vocabulary_words(id) ON DELETE CASCADE,
  display_order INTEGER DEFAULT 0,
  UNIQUE(pack_id, word_id)
);

CREATE INDEX IF NOT EXISTS idx_vocabulary_pack_words_pack ON public.vocabulary_pack_words(pack_id);
CREATE INDEX IF NOT EXISTS idx_vocabulary_pack_words_word ON public.vocabulary_pack_words(word_id);

-- Leaderboard
CREATE TABLE IF NOT EXISTS public.leaderboard (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  user_email TEXT NOT NULL,
  user_name TEXT NOT NULL,
  idioms_mastered INTEGER DEFAULT 0,
  week_ending DATE NOT NULL,
  total_practice_sessions INTEGER DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_leaderboard_week ON public.leaderboard(week_ending);

-- Study Circles
CREATE TABLE IF NOT EXISTS public.study_circles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  name TEXT NOT NULL,
  description TEXT,
  creator_email TEXT NOT NULL,
  focus_area TEXT,
  member_count INTEGER DEFAULT 0,
  max_members INTEGER DEFAULT 10,
  is_active BOOLEAN DEFAULT true
);

-- Circle Membership
CREATE TABLE IF NOT EXISTS public.circle_membership (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  circle_id UUID NOT NULL REFERENCES public.study_circles(id) ON DELETE CASCADE,
  user_email TEXT NOT NULL,
  joined_date TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_circle_membership_circle ON public.circle_membership(circle_id);

-- Tutoring Sessions
CREATE TABLE IF NOT EXISTS public.tutoring_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  student_email TEXT NOT NULL,
  student_name TEXT,
  tutor_id UUID,
  tutor_character TEXT,
  session_date TIMESTAMPTZ NOT NULL,
  duration_minutes INTEGER NOT NULL CHECK (duration_minutes IN (30, 45, 60)),
  cost_golden_eggs INTEGER NOT NULL,
  tutor_rate_gbp NUMERIC(10,2),
  platform_revenue_gbp NUMERIC(10,2),
  zoom_meeting_id TEXT,
  zoom_join_url TEXT,
  zoom_start_url TEXT,
  zoom_password TEXT,
  recording_url TEXT,
  status TEXT DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'in_progress', 'completed', 'cancelled_by_student', 'cancelled_by_tutor', 'no_show_student', 'no_show_tutor')),
  student_attended BOOLEAN,
  tutor_attended BOOLEAN,
  student_rating INTEGER CHECK (student_rating >= 1 AND student_rating <= 5),
  student_feedback TEXT,
  tutor_notes TEXT,
  learning_focus TEXT,
  refund_issued BOOLEAN DEFAULT false,
  refund_reason TEXT,
  booking_date TIMESTAMPTZ DEFAULT NOW(),
  calendar_event_id TEXT
);

CREATE INDEX IF NOT EXISTS idx_tutoring_sessions_student ON public.tutoring_sessions(student_email);
CREATE INDEX IF NOT EXISTS idx_tutoring_sessions_date ON public.tutoring_sessions(session_date);

-- Tutors
CREATE TABLE IF NOT EXISTS public.tutors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  bio TEXT,
  qualifications TEXT,
  teaching_experience_years INTEGER,
  specializations TEXT[],
  languages_spoken TEXT[],
  hourly_rate_gbp NUMERIC(10,2) NOT NULL,
  character_name TEXT CHECK (character_name IN ('Barnaby', 'Molly', 'Arnold', 'Albert', 'Snappy')),
  character_training_completed BOOLEAN DEFAULT false,
  mask_shipped_date TIMESTAMPTZ,
  zoom_co_host_email TEXT,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'active', 'suspended', 'rejected')),
  application_date TIMESTAMPTZ DEFAULT NOW(),
  approval_date TIMESTAMPTZ,
  profile_photo_url TEXT,
  video_intro_url TEXT,
  timezone TEXT,
  total_sessions_completed INTEGER DEFAULT 0,
  average_rating NUMERIC(3,2) DEFAULT 0,
  total_earnings_gbp NUMERIC(10,2) DEFAULT 0,
  payment_method TEXT CHECK (payment_method IN ('paypal', 'bank_transfer', 'stripe')),
  payment_details TEXT,
  contract_signed_date TIMESTAMPTZ,
  admin_notes TEXT
);

CREATE INDEX IF NOT EXISTS idx_tutors_specializations_gin ON public.tutors USING GIN (specializations);
CREATE INDEX IF NOT EXISTS idx_tutors_languages_gin ON public.tutors USING GIN (languages_spoken);

-- Tutor Availability
CREATE TABLE IF NOT EXISTS public.tutor_availability (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  tutor_id UUID NOT NULL REFERENCES public.tutors(id) ON DELETE CASCADE,
  day_of_week TEXT NOT NULL CHECK (day_of_week IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')),
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  is_available BOOLEAN DEFAULT true
);

-- NotebookLM Collections
CREATE TABLE IF NOT EXISTS public.notebooklm_collections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  category TEXT NOT NULL CHECK (category IN ('grammar', 'idioms', 'pronunciation', 'vocabulary', 'writing', 'listening', 'speaking', 'exam_prep')),
  notebooklm_url TEXT NOT NULL,
  cover_image_url TEXT,
  preview_video_url TEXT,
  pearl_cost INTEGER DEFAULT 0,
  egg_cost INTEGER DEFAULT 0,
  is_premium_only BOOLEAN DEFAULT false,
  estimated_duration_minutes INTEGER,
  content_count INTEGER,
  features TEXT[],
  topics_covered TEXT[],
  difficulty_level TEXT DEFAULT 'intermediate' CHECK (difficulty_level IN ('beginner', 'intermediate', 'advanced', 'all_levels')),
  target_audience TEXT[],
  is_featured BOOLEAN DEFAULT false,
  is_active BOOLEAN DEFAULT true,
  display_order INTEGER DEFAULT 0,
  total_purchases INTEGER DEFAULT 0,
  average_rating NUMERIC(3,2) DEFAULT 0,
  canva_template_url TEXT,
  educator_guide_url TEXT,
  source_pdf_url TEXT
);

CREATE INDEX IF NOT EXISTS idx_notebooklm_features_gin ON public.notebooklm_collections USING GIN (features);
CREATE INDEX IF NOT EXISTS idx_notebooklm_topics_gin ON public.notebooklm_collections USING GIN (topics_covered);

-- NotebookLM Curriculum
CREATE TABLE IF NOT EXISTS public.notebooklm_curriculum (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  level TEXT NOT NULL CHECK (level IN ('beginner', 'intermediate', 'advanced', 'mixed')),
  total_lessons INTEGER NOT NULL,
  estimated_weeks INTEGER,
  lesson_sequence TEXT[],
  learning_objectives TEXT[],
  prerequisites TEXT,
  is_structured BOOLEAN DEFAULT true,
  tutor_character TEXT CHECK (tutor_character IN ('Barnaby', 'Molly', 'Arnold', 'Albert', 'Snappy')),
  tutoring_available BOOLEAN DEFAULT false,
  tutor_session_cost_eggs INTEGER,
  certificate_available BOOLEAN DEFAULT false,
  curriculum_image_url TEXT,
  syllabus_pdf_url TEXT,
  is_active BOOLEAN DEFAULT true,
  is_institutional BOOLEAN DEFAULT false
);

-- Toolkit Resources
CREATE TABLE IF NOT EXISTS public.toolkit_resources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  title TEXT NOT NULL,
  target_audience TEXT NOT NULL,
  tone TEXT,
  description TEXT,
  cover_image_url TEXT,
  handout_quiz_url TEXT,
  handout_toolkit_url TEXT,
  slide_deck_url TEXT,
  facilitator_guide_url TEXT,
  handout_quiz_premium_only BOOLEAN DEFAULT false,
  handout_toolkit_premium_only BOOLEAN DEFAULT false,
  slide_deck_premium_only BOOLEAN DEFAULT false,
  facilitator_guide_premium_only BOOLEAN DEFAULT false,
  is_interactive_tool BOOLEAN DEFAULT false,
  tool_page_url TEXT,
  tool_premium_only BOOLEAN DEFAULT false
);

-- Educator Stories
CREATE TABLE IF NOT EXISTS public.educator_stories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  educator_name TEXT NOT NULL,
  school_name TEXT NOT NULL,
  city TEXT NOT NULL,
  country TEXT NOT NULL,
  grade_level TEXT NOT NULL,
  languages_spoken TEXT,
  thinking_style_results TEXT,
  top_idioms TEXT[],
  activity_highlight TEXT,
  student_quote TEXT,
  photo_url TEXT NOT NULL,
  lesson_plan_url TEXT,
  public_feature_consent BOOLEAN NOT NULL,
  social_media_handle TEXT,
  contact_email TEXT NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
  badge_earned TEXT
);

-- Settings
CREATE TABLE IF NOT EXISTS public.settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  key TEXT UNIQUE NOT NULL,
  value TEXT NOT NULL,
  description TEXT
);

-- Tester Codes
CREATE TABLE IF NOT EXISTS public.tester_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  code TEXT UNIQUE NOT NULL,
  claimed_by TEXT,
  claimed_at TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT true
);

-- Referrals
CREATE TABLE IF NOT EXISTS public.referrals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  referrer_email TEXT NOT NULL,
  referred_email TEXT,
  referral_code TEXT NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'rewarded')),
  signup_date TIMESTAMPTZ,
  reward_claimed_date TIMESTAMPTZ
);

-- Flagged Accounts
CREATE TABLE IF NOT EXISTS public.flagged_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  user_email TEXT NOT NULL,
  flags TEXT[],
  severity TEXT DEFAULT 'medium' CHECK (severity IN ('low', 'medium', 'high')),
  total_submissions_24h INTEGER,
  status TEXT DEFAULT 'flagged' CHECK (status IN ('flagged', 'under_review', 'cleared', 'banned')),
  admin_notes TEXT,
  auto_flagged BOOLEAN DEFAULT true
);

-- Shared Mnemonics
CREATE TABLE IF NOT EXISTS public.shared_mnemonics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  idiom_id UUID NOT NULL REFERENCES public.idioms(id) ON DELETE CASCADE,
  mnemonic_text TEXT NOT NULL,
  mnemonic_type TEXT CHECK (mnemonic_type IN ('visual', 'story', 'rhyme', 'association', 'acronym', 'other')),
  upvotes INTEGER DEFAULT 0,
  downvotes INTEGER DEFAULT 0,
  is_approved BOOLEAN DEFAULT false,
  flagged_count INTEGER DEFAULT 0
);

-- Mnemonic Votes
CREATE TABLE IF NOT EXISTS public.mnemonic_votes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT NOT NULL,
  mnemonic_id UUID NOT NULL REFERENCES public.shared_mnemonics(id) ON DELETE CASCADE,
  vote_type TEXT NOT NULL CHECK (vote_type IN ('upvote', 'downvote')),
  UNIQUE(created_by, mnemonic_id)
);

-- Classes
CREATE TABLE IF NOT EXISTS public.classes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  class_name TEXT NOT NULL,
  teacher_email TEXT NOT NULL,
  grade_level TEXT,
  subject TEXT DEFAULT 'English',
  school_name TEXT,
  class_code TEXT UNIQUE NOT NULL,
  student_count INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_classes_teacher_email ON public.classes(teacher_email);

-- Class Enrollment
CREATE TABLE IF NOT EXISTS public.class_enrollment (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  class_id UUID NOT NULL REFERENCES public.classes(id) ON DELETE CASCADE,
  student_email TEXT NOT NULL,
  enrollment_date TIMESTAMPTZ DEFAULT NOW(),
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'graduated'))
);

CREATE INDEX IF NOT EXISTS idx_class_enrollment_class ON public.class_enrollment(class_id);
CREATE INDEX IF NOT EXISTS idx_class_enrollment_student ON public.class_enrollment(student_email);

-- Teacher Assignments
CREATE TABLE IF NOT EXISTS public.teacher_assignments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  class_id UUID NOT NULL REFERENCES public.classes(id) ON DELETE CASCADE,
  teacher_email TEXT NOT NULL,
  due_date TIMESTAMPTZ NOT NULL,
  assignment_type TEXT DEFAULT 'custom' CHECK (assignment_type IN ('idiom_practice', 'crossword', 'grammar_quiz', 'vocabulary_test', 'srs_review', 'custom')),
  target_idioms TEXT[],
  min_score_required NUMERIC(5,2),
  points_possible INTEGER DEFAULT 100,
  pearls_reward INTEGER DEFAULT 10,
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'closed')),
  auto_grade BOOLEAN DEFAULT true,
  allow_late_submission BOOLEAN DEFAULT false,
  late_penalty_percent INTEGER DEFAULT 10,
  rubric TEXT,
  total_submissions INTEGER DEFAULT 0,
  avg_score NUMERIC(5,2) DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_assignments_class ON public.teacher_assignments(class_id);
CREATE INDEX IF NOT EXISTS idx_assignments_teacher_email ON public.teacher_assignments(teacher_email);

-- Assignment Submissions
CREATE TABLE IF NOT EXISTS public.assignment_submissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT NOT NULL,
  assignment_id UUID NOT NULL REFERENCES public.teacher_assignments(id) ON DELETE CASCADE,
  student_email TEXT NOT NULL,
  submission_text TEXT,
  submission_url TEXT,
  score NUMERIC(5,2),
  feedback TEXT,
  submitted_at TIMESTAMPTZ DEFAULT NOW(),
  graded_at TIMESTAMPTZ,
  status TEXT DEFAULT 'submitted' CHECK (status IN ('draft', 'submitted', 'graded', 'returned'))
);

CREATE INDEX IF NOT EXISTS idx_submissions_assignment ON public.assignment_submissions(assignment_id);
CREATE INDEX IF NOT EXISTS idx_submissions_student_email ON public.assignment_submissions(student_email);

-- Crossword Puzzles
CREATE TABLE IF NOT EXISTS public.daily_crosswords (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  puzzle_date DATE UNIQUE NOT NULL,
  grid_data TEXT NOT NULL,
  clues_data TEXT NOT NULL,
  difficulty TEXT CHECK (difficulty IN ('easy', 'medium', 'hard')),
  pearl_reward INTEGER DEFAULT 10
);

-- Crossword Attempts
CREATE TABLE IF NOT EXISTS public.crossword_attempts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT NOT NULL,
  puzzle_id UUID NOT NULL REFERENCES public.daily_crosswords(id) ON DELETE CASCADE,
  user_email TEXT NOT NULL,
  completed BOOLEAN DEFAULT false,
  completion_time INTEGER,
  pearls_earned INTEGER DEFAULT 0
);

-- Content Pricing
CREATE TABLE IF NOT EXISTS public.content_pricing (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  content_type TEXT NOT NULL CHECK (content_type IN ('grammar_session', 'vocabulary_pack_session', 'ai_feedback', 'pronunciation_analysis', 'idiom_deep_dive', 'custom_quiz', 'private_tutoring_30min', 'private_tutoring_45min', 'private_tutoring_60min')),
  tier TEXT DEFAULT 'basic' CHECK (tier IN ('basic', 'intermediate', 'advanced', 'premium')),
  pearl_cost INTEGER,
  egg_cost INTEGER,
  description TEXT,
  is_consumable BOOLEAN DEFAULT true,
  is_active BOOLEAN DEFAULT true
);

-- Idiom Contributor Awards
CREATE TABLE IF NOT EXISTS public.idiom_contributor_awards (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  user_email TEXT NOT NULL,
  user_name TEXT,
  milestone INTEGER NOT NULL CHECK (milestone IN (10, 25, 50, 100)),
  idioms_contributed TEXT[],
  achievement_date TIMESTAMPTZ NOT NULL,
  certificate_url TEXT,
  badge_earned TEXT,
  reward_pearls INTEGER,
  reward_eggs INTEGER,
  is_featured BOOLEAN DEFAULT false
);

-- Vocabulary Pack Access
CREATE TABLE IF NOT EXISTS public.vocabulary_pack_access (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  pack_name TEXT NOT NULL,
  user_email TEXT NOT NULL,
  unlocked_date TIMESTAMPTZ DEFAULT NOW()
);

-- AI Session Access
CREATE TABLE IF NOT EXISTS public.ai_session_access (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  session_type TEXT NOT NULL CHECK (session_type IN ('deep_dive_analysis', 'private_tutoring', 'custom_quiz')),
  user_email TEXT NOT NULL,
  sessions_remaining INTEGER DEFAULT 1,
  purchased_date TIMESTAMPTZ DEFAULT NOW(),
  expires_date TIMESTAMPTZ
);

-- Toolkit Access
CREATE TABLE IF NOT EXISTS public.toolkit_access (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  resource_id UUID NOT NULL REFERENCES public.toolkit_resources(id) ON DELETE CASCADE,
  user_email TEXT NOT NULL,
  unlocked_date TIMESTAMPTZ DEFAULT NOW()
);

-- NotebookLM Access
CREATE TABLE IF NOT EXISTS public.notebooklm_access (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  collection_id UUID NOT NULL REFERENCES public.notebooklm_collections(id) ON DELETE CASCADE,
  user_email TEXT NOT NULL,
  access_date TIMESTAMPTZ DEFAULT NOW()
);

-- Currency Packs (Stripe Integration)
CREATE TABLE IF NOT EXISTS public.currency_packs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  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,
  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 ON public.currency_packs(stripe_price_id);

-- =====================================================
-- ROW LEVEL SECURITY POLICIES (SECURITY HARDENED)
-- =====================================================

-- Enable RLS on all tables
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.idioms ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.grammar_topics ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.grammar_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.animations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.featured_animation ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.vocabulary_words ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.vocabulary_packs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.vocabulary_pack_words ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.vocabulary_pack_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ai_session_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.toolkit_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notebooklm_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.currency_packs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.leaderboard ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.study_circles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.circle_membership ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tutoring_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tutors ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tutor_availability ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notebooklm_collections ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notebooklm_curriculum ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.toolkit_resources ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.educator_stories ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tester_codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.referrals ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.flagged_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.shared_mnemonics ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.mnemonic_votes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.classes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.class_enrollment ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.teacher_assignments ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.assignment_submissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.daily_crosswords ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.crossword_attempts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.content_pricing ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.idiom_contributor_awards ENABLE ROW LEVEL SECURITY;

-- Users (no circular dependency - direct auth.uid() comparison)
DROP POLICY IF EXISTS "users_select_self" ON public.users;
CREATE POLICY "users_select_self" ON public.users
  FOR SELECT TO authenticated
  USING (id = auth.uid());

DROP POLICY IF EXISTS "users_insert_self" ON public.users;
CREATE POLICY "users_insert_self" ON public.users
  FOR INSERT TO authenticated
  WITH CHECK (id = auth.uid());

DROP POLICY IF EXISTS "users_update_self" ON public.users;
CREATE POLICY "users_update_self" ON public.users
  FOR UPDATE TO authenticated
  USING (id = auth.uid())
  WITH CHECK (id = auth.uid());

DROP POLICY IF EXISTS "admins_read_all_users" ON public.users;
CREATE POLICY "admins_read_all_users" ON public.users
  FOR SELECT TO authenticated
  USING (public.current_user_is_admin());

-- Idioms (public read approved, admin full control)
DROP POLICY IF EXISTS "idioms_read_public" ON public.idioms;
CREATE POLICY "idioms_read_public" ON public.idioms
  FOR SELECT TO anon, authenticated
  USING (is_approved OR public.current_user_is_admin());

DROP POLICY IF EXISTS "idioms_admin_write" ON public.idioms;
CREATE POLICY "idioms_admin_write" ON public.idioms
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- User Progress (users manage their own)
DROP POLICY IF EXISTS "progress_read_self" ON public.user_progress;
CREATE POLICY "progress_read_self" ON public.user_progress
  FOR SELECT TO authenticated
  USING (created_by = public.current_user_email());

DROP POLICY IF EXISTS "progress_insert_self" ON public.user_progress;
CREATE POLICY "progress_insert_self" ON public.user_progress
  FOR INSERT TO authenticated
  WITH CHECK (created_by = public.current_user_email());

DROP POLICY IF EXISTS "progress_update_self" ON public.user_progress;
CREATE POLICY "progress_update_self" ON public.user_progress
  FOR UPDATE TO authenticated
  USING (created_by = public.current_user_email())
  WITH CHECK (created_by = public.current_user_email());

DROP POLICY IF EXISTS "progress_delete_self" ON public.user_progress;
CREATE POLICY "progress_delete_self" ON public.user_progress
  FOR DELETE TO authenticated
  USING (created_by = public.current_user_email());

-- Grammar Topics (public read, admin write)
DROP POLICY IF EXISTS "grammar_read_public" ON public.grammar_topics;
CREATE POLICY "grammar_read_public" ON public.grammar_topics
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "grammar_admin_write" ON public.grammar_topics;
CREATE POLICY "grammar_admin_write" ON public.grammar_topics
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Grammar Access (users manage their own)
DROP POLICY IF EXISTS "grammar_access_read_self" ON public.grammar_access;
CREATE POLICY "grammar_access_read_self" ON public.grammar_access
  FOR SELECT TO authenticated
  USING (user_email = public.current_user_email());

DROP POLICY IF EXISTS "grammar_access_insert_self" ON public.grammar_access;
CREATE POLICY "grammar_access_insert_self" ON public.grammar_access
  FOR INSERT TO authenticated
  WITH CHECK (user_email = public.current_user_email());

-- Animations (public read, admin write)
DROP POLICY IF EXISTS "animations_read_public" ON public.animations;
CREATE POLICY "animations_read_public" ON public.animations
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "animations_admin_write" ON public.animations;
CREATE POLICY "animations_admin_write" ON public.animations
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Featured Animation (public read, admin write)
DROP POLICY IF EXISTS "featured_animation_read_public" ON public.featured_animation;
CREATE POLICY "featured_animation_read_public" ON public.featured_animation
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "featured_animation_admin_write" ON public.featured_animation;
CREATE POLICY "featured_animation_admin_write" ON public.featured_animation
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Vocabulary Words (public read, admin write)
DROP POLICY IF EXISTS "vocabulary_words_read_public" ON public.vocabulary_words;
CREATE POLICY "vocabulary_words_read_public" ON public.vocabulary_words
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "vocabulary_words_admin_write" ON public.vocabulary_words;
CREATE POLICY "vocabulary_words_admin_write" ON public.vocabulary_words
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Vocabulary Packs (public read, admin write)
DROP POLICY IF EXISTS "vocabulary_packs_read_public" ON public.vocabulary_packs;
CREATE POLICY "vocabulary_packs_read_public" ON public.vocabulary_packs
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "vocabulary_packs_admin_write" ON public.vocabulary_packs;
CREATE POLICY "vocabulary_packs_admin_write" ON public.vocabulary_packs
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Vocabulary Pack Words (public read, admin write)
DROP POLICY IF EXISTS "vocabulary_pack_words_read_public" ON public.vocabulary_pack_words;
CREATE POLICY "vocabulary_pack_words_read_public" ON public.vocabulary_pack_words
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "vocabulary_pack_words_admin_write" ON public.vocabulary_pack_words;
CREATE POLICY "vocabulary_pack_words_admin_write" ON public.vocabulary_pack_words
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Vocabulary Pack Access (users manage their own)
DROP POLICY IF EXISTS "vocab_access_read_self" ON public.vocabulary_pack_access;
CREATE POLICY "vocab_access_read_self" ON public.vocabulary_pack_access
  FOR SELECT TO authenticated
  USING (user_email = public.current_user_email());

DROP POLICY IF EXISTS "vocab_access_insert_self" ON public.vocabulary_pack_access;
CREATE POLICY "vocab_access_insert_self" ON public.vocabulary_pack_access
  FOR INSERT TO authenticated
  WITH CHECK (user_email = public.current_user_email());

-- AI Session Access (users manage their own)
DROP POLICY IF EXISTS "ai_access_read_self" ON public.ai_session_access;
CREATE POLICY "ai_access_read_self" ON public.ai_session_access
  FOR SELECT TO authenticated
  USING (user_email = public.current_user_email());

DROP POLICY IF EXISTS "ai_access_insert_self" ON public.ai_session_access;
CREATE POLICY "ai_access_insert_self" ON public.ai_session_access
  FOR INSERT TO authenticated
  WITH CHECK (user_email = public.current_user_email());

-- Toolkit Access (users manage their own)
DROP POLICY IF EXISTS "toolkit_access_read_self" ON public.toolkit_access;
CREATE POLICY "toolkit_access_read_self" ON public.toolkit_access
  FOR SELECT TO authenticated
  USING (user_email = public.current_user_email());

DROP POLICY IF EXISTS "toolkit_access_insert_self" ON public.toolkit_access;
CREATE POLICY "toolkit_access_insert_self" ON public.toolkit_access
  FOR INSERT TO authenticated
  WITH CHECK (user_email = public.current_user_email());

-- NotebookLM Access (users manage their own)
DROP POLICY IF EXISTS "notebooklm_access_read_self" ON public.notebooklm_access;
CREATE POLICY "notebooklm_access_read_self" ON public.notebooklm_access
  FOR SELECT TO authenticated
  USING (user_email = public.current_user_email());

DROP POLICY IF EXISTS "notebooklm_access_insert_self" ON public.notebooklm_access;
CREATE POLICY "notebooklm_access_insert_self" ON public.notebooklm_access
  FOR INSERT TO authenticated
  WITH CHECK (user_email = public.current_user_email());

-- Leaderboard (public read, admin write)
DROP POLICY IF EXISTS "leaderboard_read_public" ON public.leaderboard;
CREATE POLICY "leaderboard_read_public" ON public.leaderboard
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "leaderboard_admin_write" ON public.leaderboard;
CREATE POLICY "leaderboard_admin_write" ON public.leaderboard
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Study Circles (public read, creators manage)
DROP POLICY IF EXISTS "circles_read_public" ON public.study_circles;
CREATE POLICY "circles_read_public" ON public.study_circles
  FOR SELECT TO authenticated
  USING (true);

DROP POLICY IF EXISTS "circles_creator_manage" ON public.study_circles;
CREATE POLICY "circles_creator_manage" ON public.study_circles
  FOR ALL TO authenticated
  USING (creator_email = public.current_user_email() OR public.current_user_is_admin())
  WITH CHECK (creator_email = public.current_user_email() OR public.current_user_is_admin());

-- Circle Membership (members and creators can read, users can join)
DROP POLICY IF EXISTS "circle_membership_read" ON public.circle_membership;
CREATE POLICY "circle_membership_read" ON public.circle_membership
  FOR SELECT TO authenticated
  USING (true);

DROP POLICY IF EXISTS "circle_membership_insert" ON public.circle_membership;
CREATE POLICY "circle_membership_insert" ON public.circle_membership
  FOR INSERT TO authenticated
  WITH CHECK (user_email = public.current_user_email());

-- Tutoring Sessions (students and admins)
DROP POLICY IF EXISTS "tutoring_read_self" ON public.tutoring_sessions;
CREATE POLICY "tutoring_read_self" ON public.tutoring_sessions
  FOR SELECT TO authenticated
  USING (student_email = public.current_user_email() OR public.current_user_is_admin());

DROP POLICY IF EXISTS "tutoring_insert_self" ON public.tutoring_sessions;
CREATE POLICY "tutoring_insert_self" ON public.tutoring_sessions
  FOR INSERT TO authenticated
  WITH CHECK (student_email = public.current_user_email());

DROP POLICY IF EXISTS "tutoring_update_self" ON public.tutoring_sessions;
CREATE POLICY "tutoring_update_self" ON public.tutoring_sessions
  FOR UPDATE TO authenticated
  USING (student_email = public.current_user_email() OR public.current_user_is_admin());

-- Tutors (public read active, admin manage)
DROP POLICY IF EXISTS "tutors_read_active" ON public.tutors;
CREATE POLICY "tutors_read_active" ON public.tutors
  FOR SELECT TO anon, authenticated
  USING (status = 'active' OR public.current_user_is_admin());

DROP POLICY IF EXISTS "tutors_admin_write" ON public.tutors;
CREATE POLICY "tutors_admin_write" ON public.tutors
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Tutor Availability (public read, admin write)
DROP POLICY IF EXISTS "tutor_availability_read_public" ON public.tutor_availability;
CREATE POLICY "tutor_availability_read_public" ON public.tutor_availability
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "tutor_availability_admin_write" ON public.tutor_availability;
CREATE POLICY "tutor_availability_admin_write" ON public.tutor_availability
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- NotebookLM Collections (public read, admin write)
DROP POLICY IF EXISTS "notebooklm_collections_read_public" ON public.notebooklm_collections;
CREATE POLICY "notebooklm_collections_read_public" ON public.notebooklm_collections
  FOR SELECT TO anon, authenticated
  USING (is_active OR public.current_user_is_admin());

DROP POLICY IF EXISTS "notebooklm_collections_admin_write" ON public.notebooklm_collections;
CREATE POLICY "notebooklm_collections_admin_write" ON public.notebooklm_collections
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- NotebookLM Curriculum (public read, admin write)
DROP POLICY IF EXISTS "notebooklm_curriculum_read_public" ON public.notebooklm_curriculum;
CREATE POLICY "notebooklm_curriculum_read_public" ON public.notebooklm_curriculum
  FOR SELECT TO anon, authenticated
  USING (is_active OR public.current_user_is_admin());

DROP POLICY IF EXISTS "notebooklm_curriculum_admin_write" ON public.notebooklm_curriculum;
CREATE POLICY "notebooklm_curriculum_admin_write" ON public.notebooklm_curriculum
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Toolkit Resources (public read, admin write)
DROP POLICY IF EXISTS "toolkit_read_public" ON public.toolkit_resources;
CREATE POLICY "toolkit_read_public" ON public.toolkit_resources
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "toolkit_admin_write" ON public.toolkit_resources;
CREATE POLICY "toolkit_admin_write" ON public.toolkit_resources
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Educator Stories (public read approved, educators can create/edit own)
DROP POLICY IF EXISTS "educator_stories_read_approved" ON public.educator_stories;
CREATE POLICY "educator_stories_read_approved" ON public.educator_stories
  FOR SELECT TO anon, authenticated
  USING (status = 'approved' OR created_by = public.current_user_email() OR public.current_user_is_admin());

DROP POLICY IF EXISTS "educator_stories_insert_self" ON public.educator_stories;
CREATE POLICY "educator_stories_insert_self" ON public.educator_stories
  FOR INSERT TO authenticated
  WITH CHECK (created_by = public.current_user_email());

DROP POLICY IF EXISTS "educator_stories_update_self_or_admin" ON public.educator_stories;
CREATE POLICY "educator_stories_update_self_or_admin" ON public.educator_stories
  FOR UPDATE TO authenticated
  USING (created_by = public.current_user_email() OR public.current_user_is_admin());

-- Settings (public read, admin write)
DROP POLICY IF EXISTS "settings_read_public" ON public.settings;
CREATE POLICY "settings_read_public" ON public.settings
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "settings_admin_write" ON public.settings;
CREATE POLICY "settings_admin_write" ON public.settings
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Tester Codes (admin only)
DROP POLICY IF EXISTS "tester_codes_admin_only" ON public.tester_codes;
CREATE POLICY "tester_codes_admin_only" ON public.tester_codes
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Referrals (users manage their own)
DROP POLICY IF EXISTS "referrals_read_self" ON public.referrals;
CREATE POLICY "referrals_read_self" ON public.referrals
  FOR SELECT TO authenticated
  USING (referrer_email = public.current_user_email() OR public.current_user_is_admin());

DROP POLICY IF EXISTS "referrals_insert_self" ON public.referrals;
CREATE POLICY "referrals_insert_self" ON public.referrals
  FOR INSERT TO authenticated
  WITH CHECK (referrer_email = public.current_user_email());

DROP POLICY IF EXISTS "referrals_update_admin" ON public.referrals;
CREATE POLICY "referrals_update_admin" ON public.referrals
  FOR UPDATE TO authenticated
  USING (public.current_user_is_admin());

-- Flagged Accounts (admin only)
DROP POLICY IF EXISTS "flagged_accounts_admin_only" ON public.flagged_accounts;
CREATE POLICY "flagged_accounts_admin_only" ON public.flagged_accounts
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Shared Mnemonics (public read approved, users create own)
DROP POLICY IF EXISTS "mnemonics_read_approved" ON public.shared_mnemonics;
CREATE POLICY "mnemonics_read_approved" ON public.shared_mnemonics
  FOR SELECT TO anon, authenticated
  USING (is_approved OR created_by = public.current_user_email() OR public.current_user_is_admin());

DROP POLICY IF EXISTS "mnemonics_insert_self" ON public.shared_mnemonics;
CREATE POLICY "mnemonics_insert_self" ON public.shared_mnemonics
  FOR INSERT TO authenticated
  WITH CHECK (created_by = public.current_user_email());

DROP POLICY IF EXISTS "mnemonics_admin_manage" ON public.shared_mnemonics;
CREATE POLICY "mnemonics_admin_manage" ON public.shared_mnemonics
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Mnemonic Votes (users manage their own)
DROP POLICY IF EXISTS "mnemonic_votes_read_self" ON public.mnemonic_votes;
CREATE POLICY "mnemonic_votes_read_self" ON public.mnemonic_votes
  FOR SELECT TO authenticated
  USING (created_by = public.current_user_email());

DROP POLICY IF EXISTS "mnemonic_votes_insert_self" ON public.mnemonic_votes;
CREATE POLICY "mnemonic_votes_insert_self" ON public.mnemonic_votes
  FOR INSERT TO authenticated
  WITH CHECK (created_by = public.current_user_email());

-- Classes (teachers manage, students read)
DROP POLICY IF EXISTS "classes_teacher_manage" ON public.classes;
CREATE POLICY "classes_teacher_manage" ON public.classes
  FOR ALL TO authenticated
  USING (teacher_email = public.current_user_email() OR public.current_user_is_admin())
  WITH CHECK (teacher_email = public.current_user_email() OR public.current_user_is_admin());

DROP POLICY IF EXISTS "classes_student_read" ON public.classes;
CREATE POLICY "classes_student_read" ON public.classes
  FOR SELECT TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.class_enrollment
      WHERE class_id = classes.id
      AND student_email = public.current_user_email()
    )
  );

-- Class Enrollment (teachers and enrolled students)
DROP POLICY IF EXISTS "enrollment_read" ON public.class_enrollment;
CREATE POLICY "enrollment_read" ON public.class_enrollment
  FOR SELECT TO authenticated
  USING (
    student_email = public.current_user_email() OR
    EXISTS (
      SELECT 1 FROM public.classes
      WHERE classes.id = class_enrollment.class_id
      AND classes.teacher_email = public.current_user_email()
    ) OR
    public.current_user_is_admin()
  );

DROP POLICY IF EXISTS "enrollment_insert" ON public.class_enrollment;
CREATE POLICY "enrollment_insert" ON public.class_enrollment
  FOR INSERT TO authenticated
  WITH CHECK (
    student_email = public.current_user_email() OR
    public.current_user_is_admin()
  );

-- Teacher Assignments (teachers manage, students read)
DROP POLICY IF EXISTS "assignments_teacher_manage" ON public.teacher_assignments;
CREATE POLICY "assignments_teacher_manage" ON public.teacher_assignments
  FOR ALL TO authenticated
  USING (teacher_email = public.current_user_email() OR public.current_user_is_admin())
  WITH CHECK (teacher_email = public.current_user_email() OR public.current_user_is_admin());

DROP POLICY IF EXISTS "assignments_student_read" ON public.teacher_assignments;
CREATE POLICY "assignments_student_read" ON public.teacher_assignments
  FOR SELECT TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.class_enrollment
      WHERE class_id = teacher_assignments.class_id
      AND student_email = public.current_user_email()
    )
  );

-- Assignment Submissions (students manage own, teachers grade)
DROP POLICY IF EXISTS "submissions_student_manage" ON public.assignment_submissions;
CREATE POLICY "submissions_student_manage" ON public.assignment_submissions
  FOR ALL TO authenticated
  USING (student_email = public.current_user_email())
  WITH CHECK (student_email = public.current_user_email());

DROP POLICY IF EXISTS "submissions_teacher_grade" ON public.assignment_submissions;
CREATE POLICY "submissions_teacher_grade" ON public.assignment_submissions
  FOR SELECT TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.teacher_assignments
      WHERE teacher_assignments.id = assignment_submissions.assignment_id
      AND teacher_assignments.teacher_email = public.current_user_email()
    ) OR public.current_user_is_admin()
  );

DROP POLICY IF EXISTS "submissions_teacher_update" ON public.assignment_submissions;
CREATE POLICY "submissions_teacher_update" ON public.assignment_submissions
  FOR UPDATE TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.teacher_assignments
      WHERE teacher_assignments.id = assignment_submissions.assignment_id
      AND teacher_assignments.teacher_email = public.current_user_email()
    )
  );

-- Crosswords (public read, admin write)
DROP POLICY IF EXISTS "crosswords_read_public" ON public.daily_crosswords;
CREATE POLICY "crosswords_read_public" ON public.daily_crosswords
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "crosswords_admin_write" ON public.daily_crosswords;
CREATE POLICY "crosswords_admin_write" ON public.daily_crosswords
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Crossword Attempts (users manage own)
DROP POLICY IF EXISTS "crossword_attempts_read_self" ON public.crossword_attempts;
CREATE POLICY "crossword_attempts_read_self" ON public.crossword_attempts
  FOR SELECT TO authenticated
  USING (user_email = public.current_user_email());

DROP POLICY IF EXISTS "crossword_attempts_insert_self" ON public.crossword_attempts;
CREATE POLICY "crossword_attempts_insert_self" ON public.crossword_attempts
  FOR INSERT TO authenticated
  WITH CHECK (user_email = public.current_user_email());

DROP POLICY IF EXISTS "crossword_attempts_update_self" ON public.crossword_attempts;
CREATE POLICY "crossword_attempts_update_self" ON public.crossword_attempts
  FOR UPDATE TO authenticated
  USING (user_email = public.current_user_email());

-- Content Pricing (public read, admin write)
DROP POLICY IF EXISTS "content_pricing_read_public" ON public.content_pricing;
CREATE POLICY "content_pricing_read_public" ON public.content_pricing
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "content_pricing_admin_write" ON public.content_pricing;
CREATE POLICY "content_pricing_admin_write" ON public.content_pricing
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Idiom Contributor Awards (public read, admin write)
DROP POLICY IF EXISTS "awards_read_public" ON public.idiom_contributor_awards;
CREATE POLICY "awards_read_public" ON public.idiom_contributor_awards
  FOR SELECT TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS "awards_admin_write" ON public.idiom_contributor_awards;
CREATE POLICY "awards_admin_write" ON public.idiom_contributor_awards
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- Currency Packs (public read, admin write)
DROP POLICY IF EXISTS "currency_packs_read_public" ON public.currency_packs;
CREATE POLICY "currency_packs_read_public" ON public.currency_packs
  FOR SELECT TO anon, authenticated
  USING (is_active OR public.current_user_is_admin());

DROP POLICY IF EXISTS "currency_packs_admin_write" ON public.currency_packs;
CREATE POLICY "currency_packs_admin_write" ON public.currency_packs
  FOR ALL TO authenticated
  USING (public.current_user_is_admin())
  WITH CHECK (public.current_user_is_admin());

-- =====================================================
-- TRIGGERS FOR UPDATED_DATE (FULLY IDEMPOTENT - FIXED)
-- =====================================================

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

-- Fully idempotent trigger creation - drops both legacy and standardized names
DO $$ 
DECLARE
  v_table_list TEXT[] := ARRAY[
    'users', 'idioms', 'user_progress', 'grammar_topics', 'animations',
    'featured_animation', 'vocabulary_words', 'vocabulary_packs', 'leaderboard',
    'study_circles', 'tutoring_sessions', 'tutors', 'tutor_availability',
    'notebooklm_collections', 'notebooklm_curriculum', 'toolkit_resources',
    'educator_stories', 'settings', 'referrals', 'flagged_accounts',
    'shared_mnemonics', 'classes', 'teacher_assignments', 'assignment_submissions',
    'daily_crosswords', 'crossword_attempts', 'content_pricing', 'idiom_contributor_awards',
    'currency_packs'
  ];
  v_table_name TEXT;
  v_trigger_name TEXT;
  v_legacy_trigger_name TEXT;
BEGIN
  FOREACH v_table_name IN ARRAY v_table_list
  LOOP
    -- Standardized trigger name with update_ prefix
    v_trigger_name := 'update_' || v_table_name || '_updated_date';
    -- Legacy trigger name (without update_ prefix) - used by idioms and possibly others
    v_legacy_trigger_name := v_table_name || '_updated_date';
    
    -- Check if table has updated_date column (FIXED: use v_table_name)
    IF EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = 'public'
      AND table_name = v_table_name
      AND column_name = 'updated_date'
    ) THEN
      -- Drop both possible trigger names (legacy and standardized) to ensure clean state
      EXECUTE format('DROP TRIGGER IF EXISTS %I ON public.%I', v_legacy_trigger_name, v_table_name);
      EXECUTE format('DROP TRIGGER IF EXISTS %I ON public.%I', v_trigger_name, v_table_name);
      
      -- Create standardized trigger
      EXECUTE format(
        'CREATE TRIGGER %I BEFORE UPDATE ON public.%I FOR EACH ROW EXECUTE FUNCTION update_updated_date()',
        v_trigger_name, v_table_name
      );
      
      RAISE NOTICE 'Created trigger % on table %', v_trigger_name, v_table_name;
    END IF;
  END LOOP;
END $$;

-- =====================================================
-- PRODUCTION READY - SECURITY HARDENED v4.2 (FEATURED_ANIMATION FIX)
-- =====================================================
-- ✅ Helper functions with SECURITY DEFINER (no circular RLS dependency)
-- ✅ All RLS policies use TO authenticated/anon (scoped)
-- ✅ Direct UUID comparison (id = auth.uid()) - no casting
-- ✅ Missing policies added for all RLS-enabled tables
-- ✅ Trigger collision FIXED - drops both legacy and standardized names
-- ✅ Column existence check FIXED - uses v_table_name variable correctly
-- ✅ Featured Animation FIX - removed is_active column and unique index
-- ✅ Numeric precision for monetary values
-- ✅ Indexes on email columns for RLS performance
-- ✅ Vocabulary junction table with proper FK constraints
-- ✅ Currency Packs with RLS policies
-- ✅ IDEMPOTENT - Safe to re-run without errors
-- =====================================================