-- Wave 2 Phase C: product image uploads.
--
-- Adds the product_images table, the Storage bucket policies, and a
-- single anon-callable RPC (insert_pending_product_image) so the
-- customer-setup.html intake form can upload photos of each fence type
-- before a customers row exists.
--
-- The upload flow lives on customer-setup.html:
--
--   1. Homeowner-side browser uploads the file directly to Supabase
--      Storage at pending/<slug>/<timestamp>-<filename>. Storage RLS
--      below permits anon INSERTs only under the pending/ prefix and
--      with a hard size cap, so no one can stuff the bucket.
--   2. Browser calls insert_pending_product_image(...) to record the
--      upload with customer_id=NULL and pending_slug=<slug>.
--   3. Jake runs the customer-seed SQL to create the customers row,
--      then runs the claim UPDATE (documented in
--      estimator/RUNBOOK.md) to match rows by pending_slug.
--
-- One-time manual step Jake must do in Supabase UI before this works:
--
--   Supabase → Storage → "New bucket" → name: product-images,
--   Public: ON. (Public so fence-card thumbnails in the estimator
--   resolve without signed URLs.) The policies below only bind once
--   the bucket exists.
--
-- Apply by pasting into Supabase SQL Editor. Idempotent: re-runs skip
-- existing rows / policies.

-- ============================================================
-- 1. product_images table. The base table was created in Wave 1
-- (20260424120000_initial_schema.sql) with customer_id NOT NULL and
-- no pending_slug / claimed_at columns, on the assumption photos
-- would only ever be attached after a customers row existed. Phase C
-- introduces the pre-customer intake upload, so we relax customer_id
-- and add the pending-claim columns + constraint + index here.
-- All clauses use IF NOT EXISTS / DROP CONSTRAINT ... IF EXISTS so
-- this migration is idempotent against a fresh or a Wave-1-only db.
-- ============================================================
alter table public.product_images
  alter column customer_id drop not null;

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

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

alter table public.product_images
  drop constraint if exists product_images_owner_check;

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

create index if not exists product_images_pending_slug_idx
  on public.product_images (pending_slug)
  where pending_slug is not null;

-- ============================================================
-- 2. RLS. Default-deny; anon can only call the RPC below, not read
-- or write directly. Authenticated users (future /my/updates) will
-- see their own customer's images via a policy added in Phase D.
-- ============================================================
alter table public.product_images enable row level security;

-- No policies granted yet for anon or authenticated. They must go
-- through the RPC. Service role still has full access (bypasses RLS).

-- ============================================================
-- 3. insert_pending_product_image RPC. Anon-callable; used by the
-- customer-setup intake form once the file is already uploaded to
-- Storage. Validates the slug format, rejects oversized sort_order
-- values, and caps the pending pool per slug to stop someone
-- scripting thousands of INSERTs with the same slug.
-- ============================================================
create or replace function public.insert_pending_product_image(
  p_slug         text,
  p_product_key  text,
  p_storage_path text,
  p_alt_text     text default null,
  p_sort_order   integer default 0
)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_id    uuid;
  v_count integer;
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_product_key is null or length(trim(p_product_key)) = 0 then
    raise exception 'product_key required' using errcode = '22023';
  end if;
  if p_storage_path is null or length(trim(p_storage_path)) = 0 then
    raise exception 'storage_path required' using errcode = '22023';
  end if;
  if p_sort_order < 0 or p_sort_order > 999 then
    raise exception 'sort_order out of range' using errcode = '22023';
  end if;

  -- Abuse guard: no more than 50 pending images per slug. A real
  -- customer uploading photos of fence types will be well under this.
  select count(*) into v_count from public.product_images
   where pending_slug = p_slug and customer_id is null;
  if v_count >= 50 then
    raise exception 'Too many pending images for slug %', p_slug
      using errcode = '53300';
  end if;

  insert into public.product_images (
    pending_slug, product_key, storage_path, alt_text, sort_order
  ) values (
    p_slug, trim(p_product_key), trim(p_storage_path),
    nullif(trim(coalesce(p_alt_text, '')), ''),
    p_sort_order
  )
  returning id into v_id;

  return v_id;
end;
$$;

revoke all on function public.insert_pending_product_image(
  text, text, text, text, integer
) from public;

grant execute on function public.insert_pending_product_image(
  text, text, text, text, integer
) to anon;

grant execute on function public.insert_pending_product_image(
  text, text, text, text, integer
) to authenticated;

-- ============================================================
-- 4. Storage bucket policies. Bucket itself must be created manually
-- in Supabase UI first (see header comment). The policies below rely
-- on storage.objects, which Supabase provisions.
--
-- Policies are dropped + recreated to stay idempotent. Names are
-- stable so re-runs are safe.
-- ============================================================
drop policy if exists "product-images: anon pending upload" on storage.objects;
drop policy if exists "product-images: anyone read"         on storage.objects;

create policy "product-images: anon pending upload"
on storage.objects for insert
to anon, authenticated
with check (
  bucket_id = 'product-images'
  and (storage.foldername(name))[1] = 'pending'
);

create policy "product-images: anyone read"
on storage.objects for select
to anon, authenticated
using (bucket_id = 'product-images');

-- Claim / cleanup of pending images is Jake's runbook step (see
-- estimator/RUNBOOK.md "Phase C: claim uploaded images"). Not
-- automated here because the seed SQL is pasted per-customer.
