-- Wave 2 Phase D: customer login + /my/updates RLS + helpers.
--
-- Wave 1 already created public.users and public.update_requests with
-- RLS enabled but no policies, which means authenticated users can't
-- see or write anything through the anon/authenticated keys yet.
-- Phase D adds the minimum set of policies to make /my/updates work:
--
--   users:           a signed-in user can SELECT their own row.
--   customers:       a signed-in user can SELECT their own customer row.
--   update_requests: a signed-in user can SELECT + INSERT requests
--                    scoped to their own customer_id. No UPDATE / DELETE;
--                    Jake resolves requests via the service role (Wave 4
--                    admin dashboard).
--
-- Plus a get_my_customer() RPC that returns the whole page-bootstrap
-- payload in one round trip, so /my/updates.html doesn't have to do
-- three sequential queries on load.
--
-- Signup / user-claim is NOT automated here. After a customer first
-- signs in (which creates the auth.users row via magic link), Jake
-- manually inserts the public.users row linking auth.users.id to the
-- customer (see estimator/RUNBOOK.md "Link user to customer" step).
-- Keeping this manual avoids a trigger that would silently bind any
-- stray auth user to a customer just because the emails matched.
--
-- Apply by pasting into Supabase SQL Editor. Idempotent: CREATE OR
-- REPLACE for the function, DROP POLICY IF EXISTS for every policy.

-- ============================================================
-- 1. users: self-read only. No INSERT / UPDATE / DELETE via the
-- authenticated key; Jake maintains this table via the service role.
-- ============================================================
drop policy if exists "users self read" on public.users;

create policy "users self read"
on public.users for select
to authenticated
using (auth.uid() = id);

-- ============================================================
-- 2. customers: a signed-in user sees only their own customer row.
-- Anon still can't read customers directly; they go through
-- get_customer_config() RPC.
-- ============================================================
drop policy if exists "customers self read" on public.customers;

create policy "customers self read"
on public.customers for select
to authenticated
using (
  id in (select customer_id from public.users where id = auth.uid())
);

-- ============================================================
-- 3. update_requests: self-scope SELECT + INSERT.
-- The INSERT check pins submitted_by_user_id to auth.uid() so the
-- client can't forge someone else's name onto a request.
-- ============================================================
drop policy if exists "update_requests self read"   on public.update_requests;
drop policy if exists "update_requests self insert" on public.update_requests;

create policy "update_requests self read"
on public.update_requests for select
to authenticated
using (
  customer_id in (select customer_id from public.users where id = auth.uid())
);

create policy "update_requests self insert"
on public.update_requests for insert
to authenticated
with check (
  customer_id in (select customer_id from public.users where id = auth.uid())
  and submitted_by_user_id = auth.uid()
);

-- ============================================================
-- 4. get_my_customer(): page-bootstrap helper for /my/updates.
-- Returns null when the signed-in user has no public.users row yet
-- (i.e. they just signed in for the first time and Jake hasn't linked
-- them to a customer). The page uses null to show a "not linked" hint.
-- ============================================================
create or replace function public.get_my_customer()
returns jsonb
language sql
security definer
set search_path = public
as $$
  select jsonb_build_object(
    'user_id',       u.id,
    'user_email',    u.email,
    'role',          u.role,
    'customer_id',   c.id,
    'business_name', c.business_name,
    'slug',          c.slug,
    'status',        c.status
  )
  from public.users u
  join public.customers c on c.id = u.customer_id
  where u.id = auth.uid();
$$;

revoke all on function public.get_my_customer() from public;
grant  execute on function public.get_my_customer() to authenticated;
