OMT-SM/supabase/migrations/0024_idempotency_and_self_deal.sql

-- =====================================================================
-- 0024_idempotency_and_self_deal.sql
--
-- Two related fraud vectors not yet closed:
--
--  (A) Idempotency / replay. Today the cashier can post the same OMT
--      payout code twice in the same shift and pocket the difference,
--      or post the same WU MTCN twice and let the second one fail to
--      reconcile silently. Nothing on the server enforces uniqueness
--      of `(shop_id, external_ref_provider, external_ref)` for active
--      money-transfer transactions.
--
--  (B) Self-deal. A cashier processing transfers on their own KYC ID
--      (or as the named beneficiary of a payout, or as the sender of
--      a high-value send to themselves) is the classic skim pattern
--      across all Lebanese MFS shops. The DB has all the data — the
--      cashier's user_profiles row, plus sender_id_number /
--      beneficiary_id_number on the detail row — but never compares
--      them.
--
-- This migration:
--  * adds nullable `id_type` / `id_number` / `phone_kyc` columns to
--    `app.user_profiles` (the cashier's own KYC),
--  * unique index on (shop_id, external_ref_provider, external_ref)
--    covering only completed (or pending) money-transfer service
--    codes,
--  * deferred constraint trigger that rejects an OMT/WU/Whish/bill
--    txn whose sender or beneficiary ID matches the cashier's own
--    KYC, unless an `app.system_settings` flag explicitly allows it
--    AND the txn is approved by a manager.
-- =====================================================================

set search_path = app, public;

-- ---------------------------------------------------------------------
-- (A) idempotent external_ref
-- ---------------------------------------------------------------------
-- A *partial unique* index limited to:
--   * completed or pending status (voided rows can re-use a code if
--     the original was void-reversed, which is desired),
--   * money-transfer / bill service codes (recharges and goods sales
--     don't carry meaningful external_ref uniqueness).
create unique index if not exists ux_txn_external_ref_active
  on app.transactions (shop_id, external_ref_provider, external_ref)
  where external_ref is not null
    and external_ref_provider is not null
    and status <> 'voided'
    and service_code in (
      'OMT_SEND','OMT_RECEIVE','WU_SEND','WU_RECEIVE',
      'WHISH_SEND','OMT_BILL','EDL_BILL'
    );

-- ---------------------------------------------------------------------
-- (B) self-deal: extend user_profiles with cashier KYC
-- ---------------------------------------------------------------------
alter table app.user_profiles
  add column if not exists id_type   app.id_doc_type,
  add column if not exists id_number text,
  add column if not exists phone_kyc text;

create index if not exists idx_user_profiles_kyc_id
  on app.user_profiles(id_type, id_number)
  where id_number is not null;

-- Allow a manager to bypass self-deal blocking for a specific txn by
-- setting this knob; default is to block.
insert into app.system_settings(key, value)
  values ('self_deal_block_enabled', 'true')
  on conflict (key) do nothing;

-- ---------------------------------------------------------------------
-- Helper: does an ID belong to the cashier who created the txn?
-- ---------------------------------------------------------------------
create or replace function app._is_cashier_self(
  p_user_id   uuid,
  p_id_type   app.id_doc_type,
  p_id_number text,
  p_phone     text
) returns boolean
language sql
stable
security definer
set search_path = app, public
as $$
  select exists (
    select 1 from app.user_profiles up
    where up.user_id = p_user_id
      and (
            (p_id_number is not null
             and up.id_number is not null
             and up.id_type = p_id_type
             and lower(btrim(up.id_number)) = lower(btrim(p_id_number)))
         or (p_phone is not null
             and up.phone_kyc is not null
             and regexp_replace(up.phone_kyc, '\D', '', 'g')
                 = regexp_replace(p_phone,    '\D', '', 'g'))
          )
  );
$$;
revoke all on function app._is_cashier_self(uuid, app.id_doc_type, text, text) from public;

-- ---------------------------------------------------------------------
-- Constraint trigger: fired AFTER INSERT on the detail rows that carry
-- counter-party identity. Each branch checks the txn owner against
-- the recorded sender / beneficiary KYC.
-- ---------------------------------------------------------------------
create or replace function app._omt_send_self_deal_check()
returns trigger
language plpgsql
security definer
set search_path = app, public
as $$
declare
  t     app.transactions%rowtype;
  block boolean;
begin
  select coalesce(value::boolean, true) into block
    from app.system_settings where key = 'self_deal_block_enabled';
  if not block then return null; end if;

  select * into t from app.transactions where id = new.txn_id;
  if t.id is null then return null; end if;

  if app._is_cashier_self(t.user_id, new.sender_id_type, new.sender_id_number, new.sender_phone)
  then
    raise exception
      'self-deal blocked: cashier (% ) is the SENDER on txn % — manager must process this transfer',
      t.user_id, new.txn_id;
  end if;

  -- A cashier sending to themselves as beneficiary is also self-deal.
  -- We only have name+phone for the beneficiary on send rows, so match
  -- on phone (most reliable) when present.
  if new.beneficiary_phone is not null
     and app._is_cashier_self(t.user_id, null, null, new.beneficiary_phone)
  then
    raise exception
      'self-deal blocked: cashier is the BENEFICIARY phone on txn %',
      new.txn_id;
  end if;
  return null;
end;
$$;

drop trigger if exists trg_omt_send_self_deal on app.omt_send_details;
create constraint trigger trg_omt_send_self_deal
  after insert on app.omt_send_details
  deferrable initially deferred
  for each row execute function app._omt_send_self_deal_check();

create or replace function app._omt_receive_self_deal_check()
returns trigger
language plpgsql
security definer
set search_path = app, public
as $$
declare
  t     app.transactions%rowtype;
  block boolean;
begin
  select coalesce(value::boolean, true) into block
    from app.system_settings where key = 'self_deal_block_enabled';
  if not block then return null; end if;

  select * into t from app.transactions where id = new.txn_id;
  if t.id is null then return null; end if;

  if app._is_cashier_self(t.user_id,
       new.beneficiary_id_type, new.beneficiary_id_number, new.beneficiary_phone)
  then
    raise exception
      'self-deal blocked: cashier is the PAYOUT BENEFICIARY on txn % — manager must process',
      new.txn_id;
  end if;
  return null;
end;
$$;

drop trigger if exists trg_omt_receive_self_deal on app.omt_receive_details;
create constraint trigger trg_omt_receive_self_deal
  after insert on app.omt_receive_details
  deferrable initially deferred
  for each row execute function app._omt_receive_self_deal_check();

-- ---------------------------------------------------------------------
-- Manager-only knob: temporarily allow a single self-deal transfer
-- (e.g. owner sending themselves their own salary). Auto-resets after
-- one INSERT via a session GUC.
-- ---------------------------------------------------------------------
create or replace function app.manager_allow_next_self_deal(
  p_manager_pin text,
  p_shop        uuid
) returns void
language plpgsql
security definer
set search_path = app, public
as $$
begin
  if not app.has_role_in_shop(p_shop, 'manager')
     and not app.has_role_in_shop(p_shop, 'owner')
  then
    raise exception 'manager or owner role required';
  end if;
  if not app.verify_my_pin(p_manager_pin) then
    raise exception 'invalid manager PIN';
  end if;
  perform set_config('app.self_deal_override', 'on', true);  -- session GUC
  perform app.log_auth_event('self_deal_override_granted', p_shop, null, '{}'::jsonb);
end;
$$;
revoke all on function app.manager_allow_next_self_deal(text, uuid) from public;
grant execute on function app.manager_allow_next_self_deal(text, uuid) to authenticated;

-- Wire the override into the self-deal checkers.
create or replace function app._self_deal_overridden()
returns boolean
language sql
stable
as $$
  select coalesce(current_setting('app.self_deal_override', true), 'off') = 'on';
$$;

create or replace function app._omt_send_self_deal_check()
returns trigger
language plpgsql
security definer
set search_path = app, public
as $$
declare
  t     app.transactions%rowtype;
  block boolean;
begin
  if app._self_deal_overridden() then
    perform set_config('app.self_deal_override', 'off', true);
    return null;
  end if;
  select coalesce(value::boolean, true) into block
    from app.system_settings where key = 'self_deal_block_enabled';
  if not block then return null; end if;

  select * into t from app.transactions where id = new.txn_id;
  if t.id is null then return null; end if;

  if app._is_cashier_self(t.user_id, new.sender_id_type, new.sender_id_number, new.sender_phone) then
    raise exception
      'self-deal blocked: cashier is the SENDER on txn %', new.txn_id;
  end if;
  if new.beneficiary_phone is not null
     and app._is_cashier_self(t.user_id, null, null, new.beneficiary_phone)
  then
    raise exception
      'self-deal blocked: cashier is the BENEFICIARY phone on txn %', new.txn_id;
  end if;
  return null;
end;
$$;

create or replace function app._omt_receive_self_deal_check()
returns trigger
language plpgsql
security definer
set search_path = app, public
as $$
declare
  t     app.transactions%rowtype;
  block boolean;
begin
  if app._self_deal_overridden() then
    perform set_config('app.self_deal_override', 'off', true);
    return null;
  end if;
  select coalesce(value::boolean, true) into block
    from app.system_settings where key = 'self_deal_block_enabled';
  if not block then return null; end if;

  select * into t from app.transactions where id = new.txn_id;
  if t.id is null then return null; end if;

  if app._is_cashier_self(t.user_id,
       new.beneficiary_id_type, new.beneficiary_id_number, new.beneficiary_phone)
  then
    raise exception
      'self-deal blocked: cashier is the PAYOUT BENEFICIARY on txn %', new.txn_id;
  end if;
  return null;
end;
$$;

-- ---------------------------------------------------------------------
-- Convenience RPC for the manager UI to set/update a cashier's KYC.
-- ---------------------------------------------------------------------
create or replace function app.set_user_kyc(
  p_user_id   uuid,
  p_shop      uuid,
  p_id_type   app.id_doc_type,
  p_id_number text,
  p_phone_kyc text
) returns void
language plpgsql
security definer
set search_path = app, public
as $$
begin
  if not app.has_role_in_shop(p_shop, 'manager')
     and not app.has_role_in_shop(p_shop, 'owner')
  then
    raise exception 'manager or owner role required';
  end if;
  if p_id_number is null or btrim(p_id_number) = '' then
    raise exception 'id_number required';
  end if;
  -- the user must actually be assigned to this shop
  if not exists(
    select 1 from app.user_shop_assignments
     where user_id = p_user_id and shop_id = p_shop
  ) then
    raise exception 'user is not assigned to that shop';
  end if;

  update app.user_profiles
     set id_type   = p_id_type,
         id_number = btrim(p_id_number),
         phone_kyc = nullif(btrim(p_phone_kyc),'')
   where user_id = p_user_id;

  perform app.log_auth_event('user_kyc_updated', p_shop, null,
    jsonb_build_object('user_id', p_user_id, 'id_type', p_id_type));
end;
$$;
revoke all on function app.set_user_kyc(uuid, uuid, app.id_doc_type, text, text) from public;
grant execute on function app.set_user_kyc(uuid, uuid, app.id_doc_type, text, text) to authenticated;