-- =====================================================================
-- apply_0007_0014.sql  —  ONE-SHOT combined migration for Shamz Events
-- =====================================================================
-- Paste this whole file into the Supabase SQL Editor (Dashboard -> SQL
-- Editor -> New query) and Run it. It applies migrations 0007 through 0014
-- IN ORDER on top of a database currently at 0006.
--
-- Covers: roles + RLS hardening (0007-0008), payouts read/write (0009-0010),
-- admin oversight (0011), refunds (0012), automated gateway refunds (0013),
-- and the encrypted app_secrets table (0014).
--
-- NOTE: running this directly does NOT record these in Supabase migration
-- history. The statements are largely idempotent (if not exists / create or
-- replace / drop policy if exists), but if you later adopt `supabase db push`,
-- run `supabase migration repair --status applied 0007 0008 0009 0010 0011 0012
-- 0013 0014` first so the CLI does not try to re-run them.
--
-- After running, seed your first admin and set APP_SECRETS_KEY in the app
-- environment -- see the bottom of this file.
-- =====================================================================

-- ============================================================
-- BEGIN 0007_roles.sql
-- ============================================================

-- 0007_roles.sql
-- Introduce a real admin / organizer role model.
--
-- Today `profiles.is_organizer` is an unused boolean and the role is set
-- CLIENT-SIDE (see admin/login/page.tsx: `profiles.upsert({ is_organizer: true })`).
-- That means any signed-in user can write their own profile flags, and once a
-- real `role` column exists the same path would let them self-promote to admin.
--
-- This migration:
--   1. adds a checked `role` column (default 'organizer'),
--   2. backfills it for existing rows,
--   3. moves profile creation / role assignment to a SECURITY DEFINER trigger on
--      auth.users, so the client never sets it,
--   4. forbids self-escalation of `role` (only an existing admin or the
--      service role may change it),
--   5. adds an is_admin() helper used by the RLS hardening in 0008.
--
-- Seeding the first admin is a deliberate manual step -- see the bottom of file.

-- ----------------------------------------------------------------- role column
alter table profiles
  add column if not exists role text not null default 'organizer'
    check (role in ('admin', 'organizer'));

-- Backfill existing accounts. Everyone who signed up so far did so through the
-- organizer flow, so they all start as organizers; admins are promoted below.
update profiles set role = 'organizer' where role is null;

-- -------------------------------------------------------------- is_admin helper
-- SECURITY DEFINER so it can read profiles regardless of the caller's RLS, and
-- so RLS policies that call it on OTHER tables don't recurse back into profiles
-- policies. Used throughout 0008.
create or replace function public.is_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.profiles
    where id = auth.uid() and role = 'admin'
  );
$$;

-- --------------------------------------------------- new-user profile creation
-- Create the profile row the moment an auth user is created, server-side, so the
-- client no longer needs to upsert it (and therefore cannot pick its own role).
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, full_name, role, is_organizer)
  values (
    new.id,
    coalesce(
      new.raw_user_meta_data ->> 'full_name',
      new.raw_user_meta_data ->> 'name'
    ),
    'organizer',
    true
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- ----------------------------------------------------- forbid self-escalation
-- Only an existing admin or the service role (auth.uid() is null when using the
-- service-role key / SQL editor) may set or change a profile's role. A normal
-- user updating their own profile keeps whatever role they already have, and any
-- attempt to insert themselves as admin is silently downgraded to organizer.
create or replace function public.enforce_role_change()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if tg_op = 'INSERT' then
    if auth.uid() is not null and not public.is_admin() then
      new.role := 'organizer';
    end if;
  elsif tg_op = 'UPDATE' then
    if new.role is distinct from old.role
       and auth.uid() is not null
       and not public.is_admin() then
      raise exception 'not authorized to change role';
    end if;
  end if;
  return new;
end;
$$;

drop trigger if exists profiles_enforce_role on profiles;
create trigger profiles_enforce_role
  before insert or update on profiles
  for each row execute function public.enforce_role_change();

-- ------------------------------------------------------------ seed first admin
-- Run ONCE, manually, in the Supabase SQL editor (which runs as service role,
-- so the anti-escalation trigger allows it). Replace the email with your own:
--
--   update public.profiles p set role = 'admin'
--   from auth.users u
--   where u.id = p.id and u.email = 'you@example.com';

-- ============================================================
-- BEGIN 0008_rls_admin.sql
-- ============================================================

-- 0008_rls_admin.sql
-- RLS hardening: close the platform-table write hole opened by 0003 and give
-- admins a scoped override on the organizer-owned tables.
--
-- THIS IS THE SECURITY FIX. Until it runs, migration 0003 grants write access on
-- categories / site_settings (platform fees!) / gallery to ANY authenticated
-- user -- so the moment a second organizer signs up, every organizer can edit
-- your fees, taxonomy, and homepage gallery. After this, those tables are
-- admin-only, and organizer data stays scoped to the owner with an admin
-- override for oversight.
--
-- Requires public.is_admin() from 0007.

-- ----------------------------------------------------- platform tables: admin-only writes
-- (reads stay public; only the write policies change from `using (true)` -> is_admin())

drop policy if exists categories_auth_write on categories;
create policy categories_admin_write on categories for all
  using (public.is_admin()) with check (public.is_admin());

drop policy if exists site_settings_auth_write on site_settings;
create policy site_settings_admin_write on site_settings for all
  using (public.is_admin()) with check (public.is_admin());

drop policy if exists gallery_albums_auth_write on gallery_albums;
create policy gallery_albums_admin_write on gallery_albums for all
  using (public.is_admin()) with check (public.is_admin());

drop policy if exists gallery_photos_auth_write on gallery_photos;
create policy gallery_photos_admin_write on gallery_photos for all
  using (public.is_admin()) with check (public.is_admin());

-- Note: the `media` storage bucket policies stay authenticated-writable on
-- purpose -- organizers upload their own event cover images there. Platform
-- gallery *rows* are admin-only (above); the underlying file bucket is shared.

-- ----------------------------------------------------- profiles: admin oversight
-- Admins can read and update any profile (organizer management). Role changes are
-- still governed by the enforce_role_change trigger from 0007.

drop policy if exists profiles_self_select on profiles;
create policy profiles_self_select on profiles for select
  using (auth.uid() = id or public.is_admin());

drop policy if exists profiles_self_update on profiles;
create policy profiles_self_update on profiles for update
  using (auth.uid() = id or public.is_admin());

-- ----------------------------------------------------- events: owner + admin override

drop policy if exists events_public_read_published on events;
create policy events_read on events for select
  using (status = 'published' or organizer_id = auth.uid() or public.is_admin());

drop policy if exists events_organizer_update on events;
create policy events_update on events for update
  using (organizer_id = auth.uid() or public.is_admin());

drop policy if exists events_organizer_delete on events;
create policy events_delete on events for delete
  using (organizer_id = auth.uid() or public.is_admin());

-- events_organizer_insert is unchanged: organizers (and admins) create events
-- under their own organizer_id.

-- ----------------------------------------------------- ticket_types: owner + admin

drop policy if exists ticket_types_organizer_write on ticket_types;
create policy ticket_types_organizer_write on ticket_types for all
  using (
    public.is_admin()
    or exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
  )
  with check (
    public.is_admin()
    or exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
  );

-- ----------------------------------------------------- orders: owner + admin read

drop policy if exists orders_organizer_read on orders;
create policy orders_organizer_read on orders for select
  using (
    public.is_admin()
    or exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
  );

-- ----------------------------------------------------- tickets: owner + admin

drop policy if exists tickets_organizer_all on tickets;
create policy tickets_organizer_all on tickets for all
  using (
    public.is_admin()
    or exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
  )
  with check (
    public.is_admin()
    or exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
  );

-- ============================================================
-- BEGIN 0009_payouts.sql
-- ============================================================

-- 0009_payouts.sql
-- Phase 3 -- earnings (READ side) + organizer payout accounts.
--
-- This is read-only money plumbing: organizers record WHERE they want to be paid
-- (M-Pesa number or bank details) and see what they've earned. Actually moving
-- money (M-Pesa B2C, a `payouts` table, callbacks) is Phase 4 and stays
-- service-role only.
--
-- Two pieces:
--   1. payout_accounts -- one per organizer, owner-writable, admin-readable. The
--      `verified` flag can only be set by an admin / the service role.
--   2. earnings views -- net = gross paid orders minus the per-event platform fee
--      (events.fee_bps). SECURITY INVOKER so each caller's RLS applies: an
--      organizer sees only their own events; an admin (is_admin()) sees everyone.
--
-- Requires public.is_admin() from 0007.

create extension if not exists pgcrypto;

-- ------------------------------------------------------------- payout_accounts
create table if not exists payout_accounts (
  id uuid primary key default gen_random_uuid(),
  organizer_id uuid not null unique references auth.users(id) on delete cascade,
  type text not null check (type in ('mpesa', 'bank')),
  -- M-Pesa payout target (B2C), normalized 2547xxxxxxxx.
  mpesa_msisdn text,
  -- Bank payout target.
  bank_name text,
  bank_account_name text,
  bank_account_number text,
  bank_branch text,
  -- Set by an admin only (see trigger below); gates Phase 4 payout execution.
  verified boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table payout_accounts enable row level security;

-- Organizer fully manages their own row; admins may read all (oversight + payouts).
drop policy if exists payout_accounts_owner_all on payout_accounts;
create policy payout_accounts_owner_all on payout_accounts for all
  using (organizer_id = auth.uid()) with check (organizer_id = auth.uid());

drop policy if exists payout_accounts_admin_read on payout_accounts;
create policy payout_accounts_admin_read on payout_accounts for select
  using (public.is_admin());

-- `verified` is a trust signal an organizer must not be able to grant themselves,
-- AND it must not survive a change of payout destination (otherwise a verified
-- organizer could swap in a different M-Pesa number and keep the trust badge,
-- redirecting their payouts). So for a non-admin caller `verified` is fully
-- derived, never taken from the input: cleared whenever a destination field
-- changes, otherwise preserved. Admins / the service role (auth.uid() is null)
-- set it directly. Also keeps updated_at honest.
create or replace function public.enforce_payout_verification()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  is_privileged boolean := auth.uid() is null or public.is_admin();
begin
  new.updated_at := now();

  if is_privileged then
    return new; -- admin / service role may set verified directly
  end if;

  if tg_op = 'INSERT' then
    new.verified := false;
  elsif tg_op = 'UPDATE' then
    if new.type is distinct from old.type
       or new.mpesa_msisdn is distinct from old.mpesa_msisdn
       or new.bank_name is distinct from old.bank_name
       or new.bank_account_name is distinct from old.bank_account_name
       or new.bank_account_number is distinct from old.bank_account_number
       or new.bank_branch is distinct from old.bank_branch then
      new.verified := false; -- destination changed -> must be re-verified
    else
      new.verified := old.verified; -- unrelated edit -> keep existing status
    end if;
  end if;

  return new;
end;
$$;

drop trigger if exists payout_accounts_verify on payout_accounts;
create trigger payout_accounts_verify
  before insert or update on payout_accounts
  for each row execute function public.enforce_payout_verification();

-- --------------------------------------------------------------- earnings views
-- Per-event earnings. LEFT JOIN keeps zero-sales events visible in the ledger.
-- The WHERE clause scopes the view to the caller (owner or admin) so that an
-- organizer doesn't pick up other organizers' *published* events (which events
-- RLS would otherwise expose) as empty rows.
create or replace view public.event_earnings
with (security_invoker = true) as
select
  e.id          as event_id,
  e.organizer_id,
  e.title,
  e.status,
  count(o.id)                                                                        as paid_orders,
  coalesce(sum(o.total_cents), 0)::bigint                                            as gross_cents,
  coalesce(sum(round(o.total_cents * e.fee_bps / 10000.0)), 0)::bigint               as fee_cents,
  coalesce(sum(o.total_cents - round(o.total_cents * e.fee_bps / 10000.0)), 0)::bigint as net_cents,
  max(o.currency)                                                                   as currency
from events e
left join orders o on o.event_id = e.id and o.status = 'paid'
where e.organizer_id = auth.uid() or public.is_admin()
group by e.id, e.organizer_id, e.title, e.status;

-- Per-organizer rollup. Built on event_earnings, so it inherits the same scoping:
-- one row (their own) for an organizer, one row per organizer for an admin.
create or replace view public.organizer_earnings
with (security_invoker = true) as
select
  organizer_id,
  sum(paid_orders)::bigint as paid_orders,
  sum(gross_cents)::bigint as gross_cents,
  sum(fee_cents)::bigint   as fee_cents,
  sum(net_cents)::bigint   as net_cents,
  max(currency)            as currency
from public.event_earnings
group by organizer_id;

grant select on public.event_earnings to authenticated;
grant select on public.organizer_earnings to authenticated;

-- ============================================================
-- BEGIN 0010_payouts_execution.sql
-- ============================================================

-- 0010_payouts_execution.sql
-- Phase 4 -- payouts (WRITE side). Records each disbursement of an organizer's
-- net earnings: automated M-Pesa B2C, or a manually-reconciled bank transfer.
--
-- Security model: organizers may READ their own payout history, nothing else.
-- Every write happens through the service-role client in the API routes (which
-- bypasses RLS), and only after the route has confirmed the caller is an admin.
-- There is deliberately NO insert/update policy for normal users, so a payout
-- can never be created or altered from a browser session.
--
-- Requires public.is_admin() (0007) and payout_accounts (0009).

create table if not exists payouts (
  id uuid primary key default gen_random_uuid(),
  organizer_id uuid not null references auth.users(id) on delete cascade,
  payout_account_id uuid references payout_accounts(id) on delete set null,
  method text not null check (method in ('mpesa', 'bank')),
  amount_cents bigint not null check (amount_cents > 0),
  currency text not null default 'kes',
  status text not null default 'pending'
    check (status in ('pending', 'processing', 'paid', 'failed')),
  -- Snapshot of where the money went, captured at creation so later edits to
  -- the payout_account don't rewrite history.
  destination text,
  -- M-Pesa B2C references (null for bank).
  b2c_conversation_id text,
  b2c_originator_conversation_id text,
  b2c_transaction_id text,
  failure_reason text,
  -- Free-text admin note, e.g. the bank transfer reference for a manual payout.
  notes text,
  created_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  paid_at timestamptz
);

create index if not exists idx_payouts_organizer on payouts(organizer_id);
create index if not exists idx_payouts_status on payouts(status);
create index if not exists idx_payouts_b2c_originator on payouts(b2c_originator_conversation_id);

alter table payouts enable row level security;

-- Read-only for the owner and admins. No write policy => writes are service-role
-- only (the API routes, after an admin check).
drop policy if exists payouts_owner_read on payouts;
create policy payouts_owner_read on payouts for select
  using (organizer_id = auth.uid() or public.is_admin());

grant select on payouts to authenticated;

-- ============================================================
-- BEGIN 0011_admin_oversight.sql
-- ============================================================

-- 0011_admin_oversight.sql
-- Phase 5 -- admin oversight: organizer suspension/ban, featured-event curation,
-- and an audit log.
--
-- Three new capabilities, each with its own anti-tamper guard so an organizer
-- can't grant themselves what only an admin may grant:
--   1. profiles.status (active | suspended | banned) -- suspended/banned
--      organizers can't create or edit events; banned organizers' published
--      events also disappear from the public site. Only an admin may change it.
--   2. events.featured -- homepage curation. Only an admin may set it.
--   3. audit_log -- admin-readable trail; written by the service role only.
--
-- Requires public.is_admin() (0007).

create extension if not exists pgcrypto;

-- --------------------------------------------------------------- profile status
alter table profiles
  add column if not exists status text not null default 'active'
    check (status in ('active', 'suspended', 'banned'));

-- Is the CURRENT user frozen (suspended or banned)? SECURITY DEFINER so it can
-- read the caller's profile regardless of RLS. Used to gate organizer writes.
create or replace function public.is_suspended()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.profiles
    where id = auth.uid() and status <> 'active'
  );
$$;

-- Is a given organizer banned? SECURITY DEFINER so the public events_read policy
-- can check it without the caller needing to read other profiles (RLS would hide
-- them and the ban check would silently fail open).
create or replace function public.organizer_is_banned(uid uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.profiles
    where id = uid and status = 'banned'
  );
$$;

-- ------------------------------------------------------------- featured events
alter table events
  add column if not exists featured boolean not null default false;

-- ---------------------------------------------------- anti-tamper: profile fields
-- Replaces the 0007 function: now guards BOTH role and status. A non-admin may
-- never change either (role change raises; status change raises). Inserts by a
-- non-admin are forced to the safe defaults.
create or replace function public.enforce_role_change()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if auth.uid() is null or public.is_admin() then
    return new; -- admin / service role may set role + status freely
  end if;

  if tg_op = 'INSERT' then
    new.role := 'organizer';
    new.status := 'active';
  elsif tg_op = 'UPDATE' then
    if new.role is distinct from old.role then
      raise exception 'not authorized to change role';
    end if;
    if new.status is distinct from old.status then
      raise exception 'not authorized to change account status';
    end if;
  end if;

  return new;
end;
$$;
-- (trigger profiles_enforce_role from 0007 already points at this function.)

-- ---------------------------------------------------- anti-tamper: featured flag
create or replace function public.enforce_event_featured()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if auth.uid() is null or public.is_admin() then
    return new;
  end if;
  if tg_op = 'INSERT' then
    new.featured := false;
  elsif tg_op = 'UPDATE' then
    if new.featured is distinct from old.featured then
      new.featured := old.featured; -- silently ignore self-featuring
    end if;
  end if;
  return new;
end;
$$;

drop trigger if exists events_enforce_featured on events;
create trigger events_enforce_featured
  before insert or update on events
  for each row execute function public.enforce_event_featured();

-- ----------------------------------------------- RLS: freeze suspended writes
-- Suspended/banned organizers lose write access to their own events + tickets;
-- admins are unaffected.
drop policy if exists events_organizer_insert on events;
create policy events_organizer_insert on events for insert
  with check (
    public.is_admin()
    or (organizer_id = auth.uid() and not public.is_suspended())
  );

drop policy if exists events_update on events;
create policy events_update on events for update
  using (
    public.is_admin()
    or (organizer_id = auth.uid() and not public.is_suspended())
  );

drop policy if exists ticket_types_organizer_write on ticket_types;
create policy ticket_types_organizer_write on ticket_types for all
  using (
    public.is_admin()
    or (
      not public.is_suspended()
      and exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
    )
  )
  with check (
    public.is_admin()
    or (
      not public.is_suspended()
      and exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
    )
  );

-- --------------------------------------------- RLS: hide banned orgs publicly
-- Rewrites the 0008 read policy to drop banned organizers' published events from
-- public view (owner + admin still see everything).
drop policy if exists events_read on events;
create policy events_read on events for select
  using (
    (status = 'published' and not public.organizer_is_banned(organizer_id))
    or organizer_id = auth.uid()
    or public.is_admin()
  );

-- ------------------------------------------------------------------ audit_log
create table if not exists audit_log (
  id uuid primary key default gen_random_uuid(),
  actor_id uuid references auth.users(id) on delete set null,
  action text not null,
  target_type text,
  target_id text,
  detail jsonb,
  created_at timestamptz not null default now()
);
create index if not exists idx_audit_log_created on audit_log(created_at desc);

alter table audit_log enable row level security;

-- Admin-readable; no write policy => entries are written by the service role
-- only (the admin API routes).
drop policy if exists audit_log_admin_read on audit_log;
create policy audit_log_admin_read on audit_log for select
  using (public.is_admin());

grant select on audit_log to authenticated;

-- ============================================================
-- BEGIN 0012_refunds.sql
-- ============================================================

-- 0012_refunds.sql
-- Refunds: organizers REQUEST, admins ISSUE (the authority split chosen for this
-- build). Money movement is manual/out-of-band for now -- issuing a refund here
-- records it, marks the order `refunded`, and voids its tickets. Because the
-- earnings views and reports only count `status = 'paid'` orders, flipping an
-- order to `refunded` automatically claws the amount back out of the organizer's
-- net / owed balance; no separate ledger entry is needed.
--
-- Requires public.is_admin() (0007).

create extension if not exists pgcrypto;

create table if not exists refund_requests (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references orders(id) on delete cascade,
  -- Denormalized so RLS + admin lists can scope without re-joining orders→events.
  event_id uuid not null references events(id) on delete cascade,
  organizer_id uuid not null references auth.users(id) on delete cascade,
  requested_by uuid references auth.users(id) on delete set null,
  amount_cents bigint not null,
  reason text,
  status text not null default 'pending'
    check (status in ('pending', 'approved', 'rejected')),
  resolution_note text,
  resolved_by uuid references auth.users(id) on delete set null,
  resolved_at timestamptz,
  created_at timestamptz not null default now()
);

create index if not exists idx_refund_requests_status on refund_requests(status);
create index if not exists idx_refund_requests_organizer on refund_requests(organizer_id);
-- At most one OPEN (pending) request per order.
create unique index if not exists uniq_refund_pending_per_order
  on refund_requests(order_id) where status = 'pending';

alter table refund_requests enable row level security;

-- Organizer reads their own requests; admin reads all. Writes (create request,
-- approve/reject) happen through the service role in the API routes after the
-- route validates ownership / admin, so there is intentionally no write policy.
drop policy if exists refund_requests_owner_read on refund_requests;
create policy refund_requests_owner_read on refund_requests for select
  using (organizer_id = auth.uid() or public.is_admin());

grant select on refund_requests to authenticated;

-- ============================================================
-- BEGIN 0013_refund_gateway.sql
-- ============================================================

-- 0013_refund_gateway.sql
-- Automated gateway refunds (extends 0012). Approving a refund used to be an
-- internal-only clawback; the money return was done by hand in each gateway
-- dashboard. Now the resolve route actually calls the original gateway (Stripe
-- refund / PayPal capture refund / M-Pesa reversal), and these columns record
-- the outcome.
--
-- Card and PayPal refunds settle synchronously, so gateway_refund_status lands
-- as 'succeeded' (or 'pending') the moment the request is approved. M-Pesa
-- reversals are asynchronous like B2C payouts: the request is approved with
-- status 'pending', and /api/mpesa/reversal/result later flips it to
-- 'succeeded'/'failed', matched on the conversation ids stored here.
--
-- gateway = 'manual' marks the preserved escape hatch -- an admin recorded the
-- refund without a gateway call (expired window, comp/cash order, gateway down).
--
-- Requires refund_requests (0012). All writes are service-role (the API routes);
-- the existing owner/admin SELECT policy already covers these new columns.

alter table refund_requests
  add column if not exists gateway text,
  add column if not exists gateway_refund_status text
    check (gateway_refund_status in ('pending', 'succeeded', 'failed')),
  add column if not exists gateway_refund_id text,
  add column if not exists gateway_conversation_id text,
  add column if not exists gateway_originator_conversation_id text,
  add column if not exists gateway_error text;

-- The async M-Pesa reversal result callback finds the row by these ids.
create index if not exists idx_refund_requests_gateway_originator
  on refund_requests(gateway_originator_conversation_id);

-- ============================================================
-- BEGIN 0014_app_secrets.sql
-- ============================================================

-- 0014_app_secrets.sql
-- Operator-managed integration secrets (Stripe / PayPal / M-Pesa / Resend),
-- editable from /admin/settings/secrets instead of only .env.local.
--
-- SECURITY: this table holds live payment credentials, so unlike site_settings
-- (which is read/written from the browser) it is SERVICE-ROLE ONLY. RLS is
-- enabled with NO policies, and table privileges are revoked from anon/
-- authenticated, so a browser session can never read or write it. Reads/writes
-- happen exclusively through the service-role client in the server code.
--
-- `value` is NOT plaintext: it is AES-256-GCM ciphertext (base64) produced by
-- src/lib/secrets.ts using the APP_SECRETS_KEY env var. A DB dump never exposes
-- raw keys. Secrets that aren't present here fall back to the matching env var.

create table if not exists app_secrets (
  key text primary key,
  value text not null,
  updated_by uuid references auth.users(id) on delete set null,
  updated_at timestamptz not null default now()
);

alter table app_secrets enable row level security;

-- No policies => no anon/authenticated access at all. Belt-and-suspenders revoke
-- in case default grants are present; the service role bypasses RLS + grants.
revoke all on app_secrets from anon, authenticated;

-- =====================================================================
-- POST-MIGRATION STEPS (edit before running the admin seed)
-- =====================================================================
-- 1) Seed your first admin. REPLACE the email, then run just this statement:
--
-- update public.profiles p set role = 'admin'
-- from auth.users u
-- where u.id = p.id and u.email = 'YOUR-EMAIL-HERE';
--
-- 2) app_secrets (0014) stores integration secrets ENCRYPTED. Set a 32-byte
--    APP_SECRETS_KEY in the app environment (NOT the DB) before saving any
--    secrets from /admin/settings/secrets. Generate one with:
--      node -e "console.log(require('crypto').randomBytes(32).toString('base64'))"
-- =====================================================================