-- -- -- Enable Row Level Security (optional, for added protection)
-- -- ALTER TABLE registrations ENABLE ROW LEVEL SECURITY;

-- -- -- Policy: allow anyone to insert (public form)
-- -- CREATE POLICY "Allow public registration inserts"
-- --   ON registrations
-- --   FOR INSERT
-- --   WITH CHECK (true);

-- -- -- Policy: only service role can read (admin only)
-- -- CREATE POLICY "Only service role can read registrations"
-- --   ON registrations
-- --   FOR SELECT
-- --   USING (auth.role() = 'service_role');


-- --   -- Add age_range enum and column, drop date_of_birth, drop state
-- -- CREATE TYPE "public"."age_range" AS ENUM('under_18', '18_25', '26_30', '31_35', '36_plus');

-- -- ALTER TABLE "registrations"
-- --   ADD COLUMN "age_range" "age_range",
-- --   DROP COLUMN IF EXISTS "date_of_birth",
-- --   DROP COLUMN IF EXISTS "state",
-- --   DROP COLUMN IF EXISTS "experience";

-- -- -- Backfill existing rows (set a default so NOT NULL can be applied)
-- -- UPDATE "registrations" SET "age_range" = '18_25' WHERE "age_range" IS NULL;

-- -- ALTER TABLE "registrations" ALTER COLUMN "age_range" SET NOT NULL;



-- -- ============================================================
-- -- TechTan Ignite-100 - Full DB Setup
-- -- Run this AFTER drizzle migrations to set up RLS & profiles
-- -- ============================================================

-- -- ── 1. Registrations table RLS ────────────────────────────────────────────

-- ALTER TABLE registrations ENABLE ROW LEVEL SECURITY;

-- -- Public can insert (registration form)
-- DROP POLICY IF EXISTS "Allow public registration inserts" ON registrations;
-- CREATE POLICY "Allow public registration inserts"
--   ON registrations
--   FOR INSERT
--   WITH CHECK (true);

-- -- Only service role can read (admin panel uses service role client)
-- DROP POLICY IF EXISTS "Only service role can read registrations" ON registrations;
-- CREATE POLICY "Only service role can read registrations"
--   ON registrations
--   FOR SELECT
--   USING (auth.role() = 'service_role');

-- -- Only service role can delete
-- DROP POLICY IF EXISTS "Only service role can delete registrations" ON registrations;
-- CREATE POLICY "Only service role can delete registrations"
--   ON registrations
--   FOR DELETE
--   USING (auth.role() = 'service_role');

-- -- ── 2. Profiles table ────────────────────────────────────────────────────

-- -- Create role enum if it doesn't exist
-- DO $$ BEGIN
--   CREATE TYPE "public"."user_role" AS ENUM('user', 'admin');
-- EXCEPTION
--   WHEN duplicate_object THEN NULL;
-- END $$;

-- -- Create profiles table
-- CREATE TABLE IF NOT EXISTS "profiles" (
--   "id" uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
--   "email" text NOT NULL UNIQUE,
--   "full_name" text,
--   "role" "user_role" NOT NULL DEFAULT 'user',
--   "created_at" timestamp DEFAULT now() NOT NULL,
--   "updated_at" timestamp DEFAULT now() NOT NULL
-- );

-- -- Enable RLS on profiles
-- ALTER TABLE "profiles" ENABLE ROW LEVEL SECURITY;

-- -- Users can read their own profile
-- DROP POLICY IF EXISTS "Users can read own profile" ON profiles;
-- CREATE POLICY "Users can read own profile"
--   ON profiles
--   FOR SELECT
--   USING (auth.uid() = id);

-- -- Users can insert their own profile (during signup)
-- DROP POLICY IF EXISTS "Users can insert own profile" ON profiles;
-- CREATE POLICY "Users can insert own profile"
--   ON profiles
--   FOR INSERT
--   WITH CHECK (auth.uid() = id);

-- -- Service role has full access (for admin operations, role checks)
-- DROP POLICY IF EXISTS "Service role can manage all profiles" ON profiles;
-- CREATE POLICY "Service role can manage all profiles"
--   ON profiles
--   FOR ALL
--   USING (auth.role() = 'service_role');

-- -- ── 3. age_range column migration (if upgrading from old schema) ──────────

-- DO $$ BEGIN
--   CREATE TYPE "public"."age_range" AS ENUM('under_18', '18_25', '26_30', '31_35', '36_plus');
-- EXCEPTION
--   WHEN duplicate_object THEN NULL;
-- END $$;

-- -- Add age_range column if it doesn't exist
-- ALTER TABLE "registrations"
--   ADD COLUMN IF NOT EXISTS "age_range" "age_range";

-- -- Drop old columns if they exist (safe — uses IF EXISTS)
-- ALTER TABLE "registrations"
--   DROP COLUMN IF EXISTS "date_of_birth",
--   DROP COLUMN IF EXISTS "state",
--   DROP COLUMN IF EXISTS "experience";

-- -- Backfill and enforce NOT NULL
-- UPDATE "registrations" SET "age_range" = '18_25' WHERE "age_range" IS NULL;

-- -- This will fail silently if already NOT NULL — that's fine
-- DO $$ BEGIN
--   ALTER TABLE "registrations" ALTER COLUMN "age_range" SET NOT NULL;
-- EXCEPTION
--   WHEN others THEN NULL;
-- END $$;



-- ============================================================
-- TechTan Ignite-100 — RLS Policies ONLY
-- Run AFTER drizzle migrations (db:push / db:migrate)
-- This file does NOT change any columns or enums.
-- ============================================================

-- ── 1. registrations table ───────────────────────────────────────────────────

ALTER TABLE registrations ENABLE ROW LEVEL SECURITY;

-- Public can INSERT (registration form, no auth required)
DROP POLICY IF EXISTS "Allow public registration inserts" ON registrations;
CREATE POLICY "Allow public registration inserts"
  ON registrations
  FOR INSERT
  WITH CHECK (true);

-- Only service role can SELECT
DROP POLICY IF EXISTS "Only service role can read registrations" ON registrations;
CREATE POLICY "Only service role can read registrations"
  ON registrations
  FOR SELECT
  USING (auth.role() = 'service_role');

-- Only service role can DELETE
DROP POLICY IF EXISTS "Only service role can delete registrations" ON registrations;
CREATE POLICY "Only service role can delete registrations"
  ON registrations
  FOR DELETE
  USING (auth.role() = 'service_role');

-- ── 2. profiles table ────────────────────────────────────────────────────────

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Users can read their own profile
DROP POLICY IF EXISTS "Users can read own profile" ON profiles;
CREATE POLICY "Users can read own profile"
  ON profiles
  FOR SELECT
  USING (auth.uid() = id);

-- Users can insert their own profile (during signup)
DROP POLICY IF EXISTS "Users can insert own profile" ON profiles;
CREATE POLICY "Users can insert own profile"
  ON profiles
  FOR INSERT
  WITH CHECK (auth.uid() = id);

-- Service role has full access (for admin operations, role checks)
DROP POLICY IF EXISTS "Service role can manage all profiles" ON profiles;
CREATE POLICY "Service role can manage all profiles"
  ON profiles
  FOR ALL
  USING (auth.role() = 'service_role');


ALTER TABLE registrations
ADD COLUMN IF NOT EXISTS referral_code text;