supabase-setup.sql

-- ============================================================
-- COMPLETE DATABASE SETUP FOR PLUSONE GAME
-- Run this entire script in your new Supabase SQL Editor
-- ============================================================

-- ============================================================
-- 1. CREATE PROFILES TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID REFERENCES auth.users(id) PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT,
    highest_level INTEGER DEFAULT 1,
    high_score INTEGER DEFAULT 0,
    longest_streak INTEGER DEFAULT 0,
    lives INTEGER DEFAULT 3,
    max_lives INTEGER DEFAULT 3,
    last_life_regeneration TIMESTAMPTZ DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add constraint for non-empty username
ALTER TABLE public.profiles
ADD CONSTRAINT username_not_empty CHECK (char_length(trim(username)) > 0);

-- Create indices
CREATE INDEX IF NOT EXISTS profiles_username_idx ON public.profiles (username);
CREATE INDEX IF NOT EXISTS profiles_id_idx ON public.profiles (id);

-- Enable RLS
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- RLS Policies
CREATE POLICY "Public profiles are viewable by everyone"
ON public.profiles
FOR SELECT USING (true);

CREATE POLICY "Users can update own profile"
ON public.profiles
FOR UPDATE USING (auth.uid() = id);

-- ============================================================
-- 2. CREATE GAME_SCORES TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS public.game_scores (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
    score INTEGER NOT NULL,
    level_reached INTEGER NOT NULL,
    streak_in_game INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indices for leaderboard queries
CREATE INDEX IF NOT EXISTS game_scores_user_id_idx ON public.game_scores (user_id);
CREATE INDEX IF NOT EXISTS game_scores_score_idx ON public.game_scores (score DESC);
CREATE INDEX IF NOT EXISTS game_scores_created_at_idx ON public.game_scores (created_at DESC);

-- Enable RLS
ALTER TABLE public.game_scores ENABLE ROW LEVEL SECURITY;

-- RLS Policies
CREATE POLICY "Game scores are viewable by everyone"
ON public.game_scores
FOR SELECT USING (true);

CREATE POLICY "Users can insert own game scores"
ON public.game_scores
FOR INSERT WITH CHECK (auth.uid() = user_id);

-- ============================================================
-- 3. CREATE FEEDBACK TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS public.feedback (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    feedback_text TEXT NOT NULL,
    feedback_type TEXT CHECK (feedback_type IN ('bug', 'suggestion', 'general', 'other')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE public.feedback ENABLE ROW LEVEL SECURITY;

-- Allow anonymous feedback submissions
CREATE POLICY "Allow anonymous feedback submissions"
ON public.feedback
FOR INSERT WITH CHECK (true);

-- ============================================================
-- 4. CREATE TRIGGER FUNCTIONS
-- ============================================================

-- Function to create profile when user signs up
CREATE OR REPLACE FUNCTION public.create_profile_from_auth()
RETURNS TRIGGER AS $$
DECLARE
  username_val TEXT;
BEGIN
  -- Extract username from user metadata if it exists
  username_val := NEW.raw_user_meta_data->>'username';

  -- If no username in metadata, use the email as a fallback
  IF username_val IS NULL OR username_val = '' THEN
    username_val := NEW.email;
  END IF;

  -- Create a new profile entry
  INSERT INTO public.profiles (id, username, email, created_at, updated_at)
  VALUES (
    NEW.id,
    username_val,
    NEW.email,
    NOW(),
    NOW()
  )
  ON CONFLICT (id) DO UPDATE
  SET
    username = CASE
      WHEN profiles.username = profiles.email THEN EXCLUDED.username
      ELSE profiles.username
    END,
    email = EXCLUDED.email,
    updated_at = NOW();

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to sync email changes from auth to profiles
CREATE OR REPLACE FUNCTION public.sync_user_to_profile()
RETURNS TRIGGER AS $$
BEGIN
  -- Update the email in profiles if it changes in auth.users
  UPDATE public.profiles
  SET
    email = NEW.email,
    username = CASE
      WHEN profiles.username = OLD.email THEN NEW.email
      ELSE profiles.username
    END,
    updated_at = NOW()
  WHERE id = NEW.id;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================
-- 5. CREATE TRIGGERS
-- ============================================================

-- Trigger to create profile when user signs up
DROP TRIGGER IF EXISTS create_profile_trigger ON auth.users;
CREATE TRIGGER create_profile_trigger
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.create_profile_from_auth();

-- Trigger to sync email changes
DROP TRIGGER IF EXISTS sync_email_trigger ON auth.users;
CREATE TRIGGER sync_email_trigger
AFTER UPDATE OF email ON auth.users
FOR EACH ROW
WHEN (OLD.email IS DISTINCT FROM NEW.email)
EXECUTE FUNCTION public.sync_user_to_profile();

-- ============================================================
-- 6. CUSTOM RPC FUNCTION (used by achievementService)
-- ============================================================

-- Function to get user emails by IDs
CREATE OR REPLACE FUNCTION public.get_user_emails_by_ids(user_ids UUID[])
RETURNS TABLE (id UUID, email TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT p.id, p.email
  FROM public.profiles p
  WHERE p.id = ANY(user_ids);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================
-- SETUP COMPLETE!
-- ============================================================