-- =====================================================================
-- 0021_fee_schedule.sql
--
-- Today the cashier types fee_usd / fee_lbp / commission_usd / commission_lbp
-- by hand on every OMT_SEND, OMT_RECEIVE, WU_*, WHISH_SEND, EDL_BILL,
-- recharge and goods sale. There is no server-side anchor for what the
-- fee is *supposed* to be, which means a cashier can:
--
--   * pocket part of the customer's fee by recording a smaller fee
--     than they collected,
--   * record a larger fee than the official sheet to siphon shop
--     commission, then refund the excess to themselves later.
--
-- This migration adds an opt-in per-shop fee schedule:
--
--   app.fee_schedule(shop_id, service_code, currency,
--                    min_amount, max_amount,
--                    fee_fixed, fee_pct,
--                    commission_fixed, commission_pct,
--                    tolerance)
--
-- and a deferred constraint trigger that, *only when at least one row
-- exists for the shop+service+currency*, validates the fee/commission
-- on the transaction against the bracket the gross falls into. Shops
-- that don't seed the table keep working exactly as before.
-- =====================================================================

set search_path = app, public;

create table if not exists app.fee_schedule (
  id                 uuid primary key default gen_random_uuid(),
  shop_id            uuid not null references app.shops(id) on delete cascade,
  service_code       text not null references app.services(code),
  currency           app.currency_code not null,
  -- Inclusive lower bound, exclusive upper bound (use a very large
  -- max_amount for the "and above" bracket).
  min_amount         numeric(18,2) not null check (min_amount >= 0),
  max_amount         numeric(18,2) not null,
  fee_fixed          numeric(18,2) not null default 0 check (fee_fixed >= 0),
  fee_pct            numeric(7,4)  not null default 0 check (fee_pct >= 0 and fee_pct <= 100),
  commission_fixed   numeric(18,2) not null default 0 check (commission_fixed >= 0),
  commission_pct     numeric(7,4)  not null default 0 check (commission_pct >= 0 and commission_pct <= 100),
  -- Allowed absolute tolerance between scheduled and recorded fee. Set
  -- non-zero for services priced in LBP rounded to nearest 1000.
  tolerance          numeric(18,2) not null default 0 check (tolerance >= 0),
  effective_from     timestamptz not null default now(),
  effective_to       timestamptz,
  created_at         timestamptz not null default now(),
  created_by         uuid not null references auth.users(id) default auth.uid(),
  check (max_amount > min_amount),
  check (effective_to is null or effective_to > effective_from)
);

create index if not exists idx_fee_schedule_lookup
  on app.fee_schedule(shop_id, service_code, currency, effective_from desc);

alter table app.fee_schedule enable row level security;
alter table app.fee_schedule force  row level security;

-- Owners/managers of the shop can read and edit. Cashiers can read.
drop policy if exists fee_schedule_select on app.fee_schedule;
create policy fee_schedule_select on app.fee_schedule
  for select using (
    app.has_any_role_in_shop(shop_id,
      array['cashier','manager','owner']::app.business_role[])
  );

drop policy if exists fee_schedule_write on app.fee_schedule;
create policy fee_schedule_write on app.fee_schedule
  for all using (
    app.has_any_role_in_shop(shop_id,
      array['manager','owner']::app.business_role[])
  ) with check (
    app.has_any_role_in_shop(shop_id,
      array['manager','owner']::app.business_role[])
  );

-- ---------------------------------------------------------------------
-- Append-only on history: once published, a row's bracket cannot be
-- mutated; managers must close it (set effective_to) and insert a new
-- one. This preserves a clean audit trail of what fees were in force.
-- ---------------------------------------------------------------------
create or replace function app._fee_schedule_immutable()
returns trigger language plpgsql as $$
begin
  if tg_op = 'DELETE' then
    raise exception 'fee_schedule rows are append-only; close them with effective_to';
  end if;

  -- Only effective_to may move forward (close a bracket). Everything
  -- else must stay put.
  if (old.shop_id, old.service_code, old.currency, old.min_amount,
      old.max_amount, old.fee_fixed, old.fee_pct,
      old.commission_fixed, old.commission_pct, old.tolerance,
      old.effective_from)
   is distinct from
     (new.shop_id, new.service_code, new.currency, new.min_amount,
      new.max_amount, new.fee_fixed, new.fee_pct,
      new.commission_fixed, new.commission_pct, new.tolerance,
      new.effective_from)
  then
    raise exception 'fee_schedule columns are immutable; close the row and insert a new one';
  end if;

  if old.effective_to is not null then
    raise exception 'fee_schedule row already closed';
  end if;
  if new.effective_to is null or new.effective_to <= now() - interval '1 minute' then
    raise exception 'effective_to must be set to a current/future timestamp to close a bracket';
  end if;
  return new;
end;
$$;
drop trigger if exists trg_fee_schedule_immutable on app.fee_schedule;
create trigger trg_fee_schedule_immutable
  before update or delete on app.fee_schedule
  for each row execute function app._fee_schedule_immutable();

-- ---------------------------------------------------------------------
-- Lookup helper: returns the active bracket for a (shop, service,
-- currency, gross). Returns NULL if no schedule applies.
-- ---------------------------------------------------------------------
create or replace function app.compute_scheduled_fee(
  p_shop      uuid,
  p_service   text,
  p_currency  app.currency_code,
  p_gross     numeric
) returns table (
  expected_fee        numeric,
  expected_commission numeric,
  tolerance           numeric,
  bracket_id          uuid
)
language sql
stable
security definer
set search_path = app, public
as $$
  select
    coalesce(fs.fee_fixed,0)        + coalesce(fs.fee_pct,0)        / 100.0 * p_gross,
    coalesce(fs.commission_fixed,0) + coalesce(fs.commission_pct,0) / 100.0 * p_gross,
    fs.tolerance,
    fs.id
  from app.fee_schedule fs
  where fs.shop_id      = p_shop
    and fs.service_code = p_service
    and fs.currency     = p_currency
    and fs.min_amount  <= p_gross
    and fs.max_amount  >  p_gross
    and fs.effective_from <= now()
    and (fs.effective_to is null or fs.effective_to > now())
  order by fs.effective_from desc
  limit 1;
$$;

revoke all on function app.compute_scheduled_fee(uuid, text, app.currency_code, numeric) from public;
grant execute on function app.compute_scheduled_fee(uuid, text, app.currency_code, numeric) to authenticated;

-- ---------------------------------------------------------------------
-- Constraint trigger: validates fee/commission against the schedule
-- when a matching bracket exists. Runs on INSERT (transactions are
-- append-only). Fired DEFERRED so the txn row is fully populated before
-- we look it up.
-- ---------------------------------------------------------------------
create or replace function app._fee_schedule_check()
returns trigger
language plpgsql
security definer
set search_path = app, public
as $$
declare
  rec_usd record;
  rec_lbp record;
  diff numeric;
begin
  -- Only validate completed money transactions; refunds, voids and
  -- non-monetary services bypass.
  if new.status <> 'completed' then return null; end if;
  if new.service_code in ('REFUND','OPENING_FLOAT','SAFE_DROP','BANK_DEPOSIT')
  then return null; end if;

  if coalesce(new.gross_usd, 0) > 0 then
    select * into rec_usd
      from app.compute_scheduled_fee(new.shop_id, new.service_code, 'USD'::app.currency_code, new.gross_usd);

    if rec_usd.bracket_id is not null then
      diff := abs(coalesce(new.fee_usd,0) - rec_usd.expected_fee);
      if diff > rec_usd.tolerance then
        raise exception
          'fee_usd % deviates from schedule % (tolerance %, bracket %)',
          new.fee_usd, rec_usd.expected_fee, rec_usd.tolerance, rec_usd.bracket_id;
      end if;
      diff := abs(coalesce(new.commission_usd,0) - rec_usd.expected_commission);
      if diff > rec_usd.tolerance then
        raise exception
          'commission_usd % deviates from schedule % (tolerance %, bracket %)',
          new.commission_usd, rec_usd.expected_commission, rec_usd.tolerance, rec_usd.bracket_id;
      end if;
    end if;
  end if;

  if coalesce(new.gross_lbp, 0) > 0 then
    select * into rec_lbp
      from app.compute_scheduled_fee(new.shop_id, new.service_code, 'LBP'::app.currency_code, new.gross_lbp);

    if rec_lbp.bracket_id is not null then
      diff := abs(coalesce(new.fee_lbp,0) - rec_lbp.expected_fee);
      if diff > rec_lbp.tolerance then
        raise exception
          'fee_lbp % deviates from schedule % (tolerance %, bracket %)',
          new.fee_lbp, rec_lbp.expected_fee, rec_lbp.tolerance, rec_lbp.bracket_id;
      end if;
      diff := abs(coalesce(new.commission_lbp,0) - rec_lbp.expected_commission);
      if diff > rec_lbp.tolerance then
        raise exception
          'commission_lbp % deviates from schedule % (tolerance %, bracket %)',
          new.commission_lbp, rec_lbp.expected_commission, rec_lbp.tolerance, rec_lbp.bracket_id;
      end if;
    end if;
  end if;

  return null;
end;
$$;

drop trigger if exists trg_fee_schedule_check on app.transactions;
create constraint trigger trg_fee_schedule_check
  after insert on app.transactions
  deferrable initially deferred
  for each row execute function app._fee_schedule_check();