-- Wave 3 Phase A: service-area polygon capture during customer setup.
--
-- Adds pending-claim columns to public.service_areas (mirrors the
-- product_images pattern from Phase C) so customer-setup.html can write
-- a polygon before a customers row exists, and exposes a single
-- anon-callable RPC (upsert_pending_service_area) that validates the
-- polygon with ST_IsValid before storing it as a geography.
--
-- The flow:
--
--   1. Customer draws a polygon on customer-setup.html. The browser
--      converts it to GeoJSON and calls upsert_pending_service_area.
--   2. The RPC casts via ST_GeomFromGeoJSON, validates with ST_IsValid
--      (rejecting self-intersecting "figure 8" shapes), and stores the
--      row with customer_id=NULL and pending_slug=<slug>. Re-submits
--      for the same slug overwrite the prior pending row, so a customer
--      drawing-then-redrawing leaves only one row per slug.
--   3. After Jake applies the customer seed SQL, the runbook's claim
--      UPDATE links the pending row to the new customer_id (mirrors
--      the product_images claim).
--
-- Apply by pasting into Supabase SQL Editor. Idempotent.

-- ============================================================
-- 1. service_areas pending columns. Wave 1 made customer_id NOT NULL
-- because polygons would only be added post-onboarding. Phase A
-- introduces pre-customer capture, so we relax that and add a
-- pending_slug + claim columns. The owner-check constraint guarantees
-- every row still belongs to either a customer or a pending slug.
-- ============================================================
alter table public.service_areas
  alter column customer_id drop not null;

alter table public.service_areas
  add column if not exists pending_slug text;

alter table public.service_areas
  add column if not exists claimed_at timestamptz;

alter table public.service_areas
  drop constraint if exists service_areas_owner_check;

alter table public.service_areas
  add constraint service_areas_owner_check
  check (customer_id is not null or pending_slug is not null);

create index if not exists service_areas_pending_slug_idx
  on public.service_areas (pending_slug)
  where pending_slug is not null;

-- ============================================================
-- 2. RLS. Already enabled in Wave 1. No direct policies for anon or
-- authenticated; access goes through the RPC below. Service role
-- still has full access (bypasses RLS).
-- ============================================================
alter table public.service_areas enable row level security;

-- ============================================================
-- 3. upsert_pending_service_area RPC. Anon-callable. Replaces (delete
-- then insert) any existing pending row for the same slug so re-draws
-- don't accumulate. Validates the polygon before storing — invalid
-- geometries (self-intersecting, wrong type, too many vertices) are
-- rejected with a descriptive error the browser can surface.
-- ============================================================
create or replace function public.upsert_pending_service_area(
  p_slug    text,
  p_polygon jsonb,
  p_name    text default null
)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_id   uuid;
  v_geom geometry;
begin
  if p_slug is null or p_slug !~ '^[a-z0-9-]+$' then
    raise exception 'Invalid slug: %', p_slug using errcode = '22023';
  end if;
  if p_polygon is null then
    raise exception 'polygon required' using errcode = '22023';
  end if;

  -- Parse GeoJSON. ST_GeomFromGeoJSON throws on malformed input;
  -- the calling client sees the captured message.
  begin
    v_geom := ST_SetSRID(ST_GeomFromGeoJSON(p_polygon::text), 4326);
  exception when others then
    raise exception 'Invalid polygon GeoJSON: %', sqlerrm
      using errcode = '22023';
  end;

  if GeometryType(v_geom) <> 'POLYGON' then
    raise exception 'Polygon required (got %)', GeometryType(v_geom)
      using errcode = '22023';
  end if;

  if not ST_IsValid(v_geom) then
    raise exception 'Polygon is invalid (likely self-intersecting): %',
      ST_IsValidReason(v_geom) using errcode = '22023';
  end if;

  if ST_NPoints(v_geom) > 1000 then
    raise exception 'Polygon has too many vertices (%); max 1000',
      ST_NPoints(v_geom) using errcode = '22023';
  end if;

  -- Replace prior pending row for this slug. The DELETE-then-INSERT
  -- pattern keeps per-slug count at 1, so re-draws are idempotent
  -- and there's no need for a separate abuse cap.
  delete from public.service_areas
   where pending_slug = p_slug and customer_id is null;

  insert into public.service_areas (
    pending_slug, polygon, name, is_primary
  ) values (
    p_slug,
    v_geom::geography,
    nullif(trim(coalesce(p_name, '')), ''),
    true
  )
  returning id into v_id;

  return v_id;
end;
$$;

revoke all on function public.upsert_pending_service_area(
  text, jsonb, text
) from public;

grant execute on function public.upsert_pending_service_area(
  text, jsonb, text
) to anon;

grant execute on function public.upsert_pending_service_area(
  text, jsonb, text
) to authenticated;
