-- =====================================================================
-- Migration 0011 — Hardening & ops (roadmap Step 12).
--
-- 1. pg_cron schedules: alert detector + chain verifier.
-- 2. Daily off-site hash anchor (writes the day's last row_hash per shop
--    to app.daily_anchors; an external job copies these to S3/Glacier).
-- 3. HMAC + PIN secret rotation procedures with audit.
-- 4. DDL lockdown advisory (event trigger blocking DDL by anyone other
--    than the migration role).
-- 5. NTP / clock-skew guard at INSERT time on app.transactions.
--
-- Threat-model rows: 4, 7, 17, 22, 23, 25.
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1. pg_cron schedules. Supabase ships pg_cron in the `extensions`
--    schema. Each task runs as the table owner thanks to SECURITY DEFINER.
-- ---------------------------------------------------------------------
create extension if not exists pg_cron;

-- Run alert detectors every 5 minutes.
do $$ begin
  perform cron.schedule('app_alert_detectors_5m',
                        '*/5 * * * *',
                        $cmd$ select app.run_alert_detectors(); $cmd$);
exception when others then null;     -- already scheduled
end $$;

-- Verify the hash chain hourly per shop. We don't bail loudly here;
-- run_alert_detectors() raises a chain_break alert if verify_chain fails.
do $$ begin
  perform cron.schedule('app_chain_verify_hourly',
                        '7 * * * *',
                        $cmd$ select 1 from (
                                select s.id, (select bool_and(ok) from app.verify_chain(s.id))
                                from app.shops s
                              ) v; $cmd$);
exception when others then null;
end $$;

-- ---------------------------------------------------------------------
-- 2. Daily off-site anchor.
--    The most important fraud control after recon: every night, copy
--    the last row_hash per shop into a row that is written ONCE,
--    timestamped, and exported to immutable storage. If anyone tampers
--    with history, today's anchor will not chain back to yesterday's.
-- ---------------------------------------------------------------------
create table if not exists app.daily_anchors (
  id          uuid primary key default gen_random_uuid(),
  shop_id     uuid not null references app.shops(id) on delete restrict,
  anchor_date date not null,
  last_txn_id uuid,
  last_ref_no bigint,
  last_row_hash bytea,
  txn_count_to_date bigint not null,
  computed_at timestamptz not null default now(),
  unique (shop_id, anchor_date)
);
alter table app.daily_anchors enable row level security;
alter table app.daily_anchors force  row level security;
revoke insert, update, delete on app.daily_anchors from authenticated;

create or replace function app._daily_anchors_guard()
returns trigger language plpgsql as $$
begin
  raise exception 'daily_anchors are append-only';
end;
$$;
drop trigger if exists trg_daily_anchors_guard on app.daily_anchors;
create trigger trg_daily_anchors_guard before update or delete on app.daily_anchors
  for each row execute function app._daily_anchors_guard();

drop policy if exists daily_anchors_select on app.daily_anchors;
create policy daily_anchors_select on app.daily_anchors
  for select to authenticated
  using (app.has_any_role_in_shop(shop_id,
           array['owner','auditor']::app.business_role[]));
grant select on app.daily_anchors to authenticated;

create or replace function app.write_daily_anchors()
returns int
language plpgsql
security definer
set search_path = app, public
as $$
declare n int := 0; r record;
begin
  for r in
    with last_row as (
      select distinct on (shop_id)
             shop_id, id, reference_no, row_hash, occurred_at
      from app.transactions
      where (occurred_at at time zone 'UTC')::date
            = (now() at time zone 'UTC')::date - 1
      order by shop_id, reference_no desc
    )
    select lr.shop_id, lr.id, lr.reference_no, lr.row_hash,
           (now() at time zone 'UTC')::date - 1 as anchor_date,
           (select count(*) from app.transactions t
              where t.shop_id = lr.shop_id
                and t.reference_no <= lr.reference_no) as cnt
    from last_row lr
  loop
    insert into app.daily_anchors(shop_id, anchor_date, last_txn_id,
                                  last_ref_no, last_row_hash, txn_count_to_date)
      values (r.shop_id, r.anchor_date, r.id,
              r.reference_no, r.row_hash, r.cnt)
    on conflict (shop_id, anchor_date) do nothing;
    n := n + 1;
  end loop;

  perform app.log_auth_event('daily_anchor_written', null, null,
    jsonb_build_object('rows', n));
  return n;
end;
$$;
revoke all on function app.write_daily_anchors() from public;
grant execute on function app.write_daily_anchors() to authenticated;

-- 02:15 Beirut time = 23:15 UTC the previous day; at that hour the till
-- is closed and the day's last txn already exists.
do $$ begin
  perform cron.schedule('app_daily_anchor',
                        '15 23 * * *',
                        $cmd$ select app.write_daily_anchors(); $cmd$);
exception when others then null;
end $$;

-- ---------------------------------------------------------------------
-- 3. Secret rotation with audit. The HMAC key was created in 0007;
--    rotating it invalidates all printed receipts but new ones become
--    forgery-resistant. PIN rotation is per-user.
-- ---------------------------------------------------------------------
create table if not exists app.secret_rotations (
  id            uuid primary key default gen_random_uuid(),
  secret_name   text not null,
  rotated_by    uuid not null references auth.users(id) default auth.uid(),
  rotated_at    timestamptz not null default now(),
  reason        text not null check (length(btrim(reason)) >= 5)
);
alter table app.secret_rotations enable row level security;
alter table app.secret_rotations force  row level security;
revoke insert, update, delete on app.secret_rotations from authenticated;
drop policy if exists secret_rotations_select on app.secret_rotations;
create policy secret_rotations_select on app.secret_rotations
  for select to authenticated
  using (app.is_owner_anywhere());
grant select on app.secret_rotations to authenticated;

create or replace function app._secret_rotations_guard()
returns trigger language plpgsql as $$
begin
  raise exception 'secret_rotations are append-only';
end;
$$;
drop trigger if exists trg_secret_rotations_guard on app.secret_rotations;
create trigger trg_secret_rotations_guard before update or delete on app.secret_rotations
  for each row execute function app._secret_rotations_guard();

create or replace function app.log_secret_rotation(p_name text, p_reason text)
returns uuid
language plpgsql
security definer
set search_path = app, public
as $$
declare rid uuid;
begin
  if not app.is_owner_anywhere() then
    raise exception 'owner role required';
  end if;
  insert into app.secret_rotations(secret_name, reason)
    values (p_name, p_reason) returning id into rid;
  return rid;
end;
$$;
revoke all on function app.log_secret_rotation(text, text) from public;
grant execute on function app.log_secret_rotation(text, text) to authenticated;

-- ---------------------------------------------------------------------
-- 4. DDL lockdown advisory.
--    Anyone with `authenticated` should not be able to issue DDL anyway,
--    but Supabase ships a `service_role` key. This event trigger raises
--    if DDL is attempted from anything other than the migration owner.
-- ---------------------------------------------------------------------
create or replace function app._ddl_lock()
returns event_trigger
language plpgsql
as $$
begin
  -- Allow the role that owns the schema (typically `postgres` running
  -- supabase migrations) and the cron worker. Block everyone else.
  if current_user not in ('postgres', 'supabase_admin') then
    raise exception 'DDL is locked: caller % may not modify schema', current_user;
  end if;
end;
$$;
drop event trigger if exists app_ddl_lock;
create event trigger app_ddl_lock
  on ddl_command_start
  execute function app._ddl_lock();

-- ---------------------------------------------------------------------
-- 5. Clock-skew guard. A till with a manipulated clock can backdate or
--    pre-date transactions to hide them from a shift. Reject inserts
--    whose `occurred_at` is more than 5 minutes off server `now()`.
-- ---------------------------------------------------------------------
create or replace function app._txn_clock_guard()
returns trigger language plpgsql as $$
begin
  if abs(extract(epoch from (new.occurred_at - now()))) > 300 then
    raise exception
      'clock skew rejected: occurred_at=% server now()=%', new.occurred_at, now();
  end if;
  return new;
end;
$$;
drop trigger if exists trg_txn_clock_guard on app.transactions;
-- Fires before the existing txn_before_insert (alphabetical 'a' < 't').
create trigger trg_a_txn_clock_guard before insert on app.transactions
  for each row execute function app._txn_clock_guard();

-- End migration 0011 ----------------------------------------------------