-- =================================================================
-- 2026 FUNd Run — Supabase schema
-- Adds 2 tables to the existing OutcomeUnite Supabase project.
-- =================================================================
-- HOW TO RUN:
-- 1. Open  https://supabase.com/dashboard/project/nibmokaypwncmonatckf/sql/new
-- 2. Paste this entire file
-- 3. Click "Run"
-- 4. Tell Abdul "Supabase is ready" and he'll migrate the live data over.
-- =================================================================

-- Kids: anyone running in the FUNd Run
CREATE TABLE IF NOT EXISTS public.fund_run_kids (
  id            TEXT PRIMARY KEY,                    -- slug like "maya" or "maya-c"
  first_name    TEXT NOT NULL,
  last_initial  TEXT,
  parent_name   TEXT,
  parent_phone  TEXT,
  parent_email  TEXT,
  age           INTEGER,
  target_laps   INTEGER DEFAULT 20,
  laps          INTEGER DEFAULT 0,
  notes         TEXT,
  created_at    TIMESTAMPTZ DEFAULT now()
);

-- Pledges: sponsors pledging per-lap or flat
CREATE TABLE IF NOT EXISTS public.fund_run_pledges (
  id            TEXT PRIMARY KEY,                    -- generated client-side
  kid_id        TEXT REFERENCES public.fund_run_kids(id) ON DELETE CASCADE,
  sponsor_name  TEXT NOT NULL,
  sponsor_email TEXT,
  sponsor_phone TEXT,
  type          TEXT CHECK (type IN ('per_lap','flat')) NOT NULL,
  amount        NUMERIC(10,2) NOT NULL,
  max_amount    NUMERIC(10,2),
  notes         TEXT,
  source        TEXT DEFAULT 'web' CHECK (source IN ('web','paper','form')),
  paid          BOOLEAN DEFAULT FALSE,
  created_at    TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS fund_run_pledges_kid_id_idx ON public.fund_run_pledges(kid_id);

-- Public access (anon key can read + write)
-- Honor-system fundraiser, no logins required.
-- Admin auth is a client-side PIN (zero protection but enough for our needs).
ALTER TABLE public.fund_run_kids    ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.fund_run_pledges ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "anon read kids"    ON public.fund_run_kids;
DROP POLICY IF EXISTS "anon write kids"   ON public.fund_run_kids;
DROP POLICY IF EXISTS "anon update kids"  ON public.fund_run_kids;
DROP POLICY IF EXISTS "anon delete kids"  ON public.fund_run_kids;
DROP POLICY IF EXISTS "anon read pledges" ON public.fund_run_pledges;
DROP POLICY IF EXISTS "anon write pledges" ON public.fund_run_pledges;
DROP POLICY IF EXISTS "anon update pledges" ON public.fund_run_pledges;
DROP POLICY IF EXISTS "anon delete pledges" ON public.fund_run_pledges;

CREATE POLICY "anon read kids"     ON public.fund_run_kids    FOR SELECT TO anon USING (true);
CREATE POLICY "anon write kids"    ON public.fund_run_kids    FOR INSERT TO anon WITH CHECK (true);
CREATE POLICY "anon update kids"   ON public.fund_run_kids    FOR UPDATE TO anon USING (true);
CREATE POLICY "anon delete kids"   ON public.fund_run_kids    FOR DELETE TO anon USING (true);
CREATE POLICY "anon read pledges"  ON public.fund_run_pledges FOR SELECT TO anon USING (true);
CREATE POLICY "anon write pledges" ON public.fund_run_pledges FOR INSERT TO anon WITH CHECK (true);
CREATE POLICY "anon update pledges" ON public.fund_run_pledges FOR UPDATE TO anon USING (true);
CREATE POLICY "anon delete pledges" ON public.fund_run_pledges FOR DELETE TO anon USING (true);

-- Also let the authenticated role do everything (for future logged-in admin)
CREATE POLICY "auth all kids"     ON public.fund_run_kids    FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "auth all pledges"  ON public.fund_run_pledges FOR ALL TO authenticated USING (true) WITH CHECK (true);
