-- Migration 007: Fix gamification trigger that blocks auth signup
--
-- Problem: trigger `on_auth_user_created_gamification` fires after every
-- auth.users INSERT and writes to user_gamification + xu_transactions. If those
-- tables are missing or schema-drifted, the trigger raises and the entire
-- signup transaction rolls back → POST /auth/v1/signup returns 500.
--
-- Fix:
--  1. Re-create gamification tables idempotently so the trigger has a target.
--  2. Wrap inserts in BEGIN/EXCEPTION block so any future schema break logs a
--     warning instead of breaking auth signup.
--  3. Replace the trigger function in place (DROP not needed because of CREATE
--     OR REPLACE).
--
-- Run in Supabase Dashboard → SQL Editor.

-- ============================================================
-- 1. Ensure tables exist (idempotent — same shape as 002_gamification.sql)
-- ============================================================
CREATE TABLE IF NOT EXISTS user_gamification (
  user_id        UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  xp             INT NOT NULL DEFAULT 0,
  level          TEXT NOT NULL DEFAULT 'beginner'
                   CHECK (level IN ('beginner', 'bronze', 'silver', 'gold', 'master')),
  streak         INT NOT NULL DEFAULT 0,
  longest_streak INT NOT NULL DEFAULT 0,
  last_active    DATE,
  xu             INT NOT NULL DEFAULT 50,
  freeze_count   INT NOT NULL DEFAULT 0,
  created_at     TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS xu_transactions (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  type        TEXT NOT NULL
                CHECK (type IN (
                  'earn_welcome', 'earn_daily', 'earn_streak', 'earn_ads',
                  'spend_freeze', 'spend_writing', 'spend_test'
                )),
  amount      INT NOT NULL,
  balance     INT NOT NULL,
  description TEXT,
  created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_xu_transactions_user_date
  ON xu_transactions(user_id, created_at DESC);

-- Re-apply RLS (idempotent — OR REPLACE not supported on policies, so drop-then-create)
ALTER TABLE user_gamification ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can read own gamification" ON user_gamification;
DROP POLICY IF EXISTS "Users can insert own gamification" ON user_gamification;
DROP POLICY IF EXISTS "Users can update own gamification" ON user_gamification;
CREATE POLICY "Users can read own gamification"
  ON user_gamification FOR SELECT  USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own gamification"
  ON user_gamification FOR INSERT  WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own gamification"
  ON user_gamification FOR UPDATE  USING (auth.uid() = user_id);

ALTER TABLE xu_transactions ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can read own xu transactions" ON xu_transactions;
DROP POLICY IF EXISTS "Users can insert own xu transactions" ON xu_transactions;
CREATE POLICY "Users can read own xu transactions"
  ON xu_transactions FOR SELECT  USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own xu transactions"
  ON xu_transactions FOR INSERT  WITH CHECK (auth.uid() = user_id);

-- ============================================================
-- 2. Replace trigger function with one that NEVER blocks signup
-- ============================================================
-- Why the EXCEPTION wrapper:
-- A trigger error inside auth.users INSERT rolls back the whole transaction,
-- meaning a broken gamification side-effect kills the user's ability to sign
-- up. We swallow gamification failures and log a warning so signup always
-- succeeds; missing rows can be backfilled later.
CREATE OR REPLACE FUNCTION handle_new_user_gamification()
RETURNS TRIGGER AS $$
BEGIN
  BEGIN
    INSERT INTO user_gamification (user_id, xu)
    VALUES (NEW.id, 50)
    ON CONFLICT (user_id) DO NOTHING;

    INSERT INTO xu_transactions (user_id, type, amount, balance, description)
    VALUES (NEW.id, 'earn_welcome', 50, 50, 'Welcome bonus');
  EXCEPTION WHEN OTHERS THEN
    RAISE WARNING 'gamification side-effect failed for user %: %', NEW.id, SQLERRM;
  END;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================
-- 3. Re-attach the trigger (idempotent)
-- ============================================================
DROP TRIGGER IF EXISTS on_auth_user_created_gamification ON auth.users;
CREATE TRIGGER on_auth_user_created_gamification
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION handle_new_user_gamification();
