OMT-SM/supabase/migrations/0018_money_movement_coupling.sql

-- =====================================================================
-- Migration 0018 — Money-transfer cash + float coupling.
--
-- Closes the largest hole in the cash-control model: until now,
-- record_omt_send / record_omt_receive / record_bill only inserted into
-- app.transactions and the detail table. They did NOT post anything to
-- app.cash_movements or app.float_movements, so:
--
--   * v_z_report.expected_close_usd = sum(cash_movements) was wrong by
--     the entire transfer turnover, hiding cashier shortages.
--   * The OMT/Whish/WU/biller float balance never moved, so we couldn't
--     tell who owed whom and the matcher could only compare by
--     external_ref, never by money.
--   * Threat-model rows #1, #5, #21 had no DB-level enforcement for
--     money transfers (only recharges had the deferred-trigger
--     constraint).
--
-- This migration:
--   1. Adds app._post_cash_for_txn / app._post_float_for_txn helpers.
--   2. Adds app._get_or_create_float(shop, provider, currency).
--   3. Re-defines record_omt_send / record_omt_receive / record_bill so
--      each posts the cash leg (when payment_method is cash_*) and the
--      provider-float leg in the same SECURITY DEFINER body.
--   4. Adds record_whish_send, record_wu_send, record_wu_receive so the
--      UI does not silently file Whish/WU under provider='OMT'.
--   5. Adds a deferred constraint trigger that requires every completed
--      money-transfer / bill txn to have at least one float_movement
--      row. Recharges already have their own coupling trigger from
--      0005; goods sales have one too.
--
-- Threat-model rows: 1, 5, 7, 8, 14, 21, 24.
-- =====================================================================

-- =====================================================================
-- Helper: get_or_create the float account the txn should debit/credit.
-- =====================================================================
create or replace function app._get_or_create_float(
  p_shop     uuid,
  p_provider app.float_provider,
  p_currency app.currency_code
) returns uuid
language plpgsql
security definer
set search_path = app, public
as $$
declare v_id uuid;
begin
  select id into v_id from app.floats
   where shop_id = p_shop and provider = p_provider and currency = p_currency;
  if v_id is null then
    insert into app.floats(shop_id, provider, currency)
      values (p_shop, p_provider, p_currency)
    returning id into v_id;
    -- Initialise the cached balance row at zero.
    insert into app.float_balances(float_id, balance) values (v_id, 0)
      on conflict (float_id) do nothing;
  end if;
  return v_id;
end;
$$;
revoke all on function app._get_or_create_float(uuid, app.float_provider, app.currency_code)
  from public;

-- =====================================================================
-- Helper: post the cash leg for a customer-facing transaction.
--
-- Convention (matches 0002 cash_movements):
--   * Positive amount = cash into till.
--   * Negative amount = cash out of till.
-- This function takes a "customer movement" sign:
--   * p_customer_paid > 0  -> cash_in   (sale_in)   amount = +p_customer_paid
--   * p_customer_paid < 0  -> cash_out  (payout_out) amount =  p_customer_paid
-- For non-cash payment methods (whish, omt_wallet, card, bank_transfer)
-- the cash leg is skipped — those balances live on their own floats.
-- =====================================================================
create or replace function app._post_cash_for_txn(
  p_txn_id        uuid,
  p_payment_method app.payment_method,
  p_customer_usd  numeric,
  p_customer_lbp  numeric
) returns void
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v_shift uuid;
  v_type  app.cash_movement_type;
begin
  -- Only cash-in-till payment methods produce a cash leg.
  if p_payment_method not in ('cash_usd','cash_lbp') then
    return;
  end if;

  select shift_id into v_shift from app.transactions where id = p_txn_id;
  if v_shift is null then
    raise exception 'txn % not found while posting cash leg', p_txn_id;
  end if;

  -- For cash_usd payment method, only USD leg may move; same for LBP.
  if p_payment_method = 'cash_usd' then
    if coalesce(p_customer_lbp, 0) <> 0 then
      raise exception 'cash_usd payment must not move LBP (got %)', p_customer_lbp;
    end if;
    if coalesce(p_customer_usd, 0) = 0 then return; end if;
    v_type := case when p_customer_usd > 0 then 'sale_in'::app.cash_movement_type
                                            else 'payout_out'::app.cash_movement_type end;
    insert into app.cash_movements(shift_id, type, currency, amount, ref_txn_id, note)
      values (v_shift, v_type, 'USD', p_customer_usd, p_txn_id, 'auto: txn cash leg');
  else  -- cash_lbp
    if coalesce(p_customer_usd, 0) <> 0 then
      raise exception 'cash_lbp payment must not move USD (got %)', p_customer_usd;
    end if;
    if coalesce(p_customer_lbp, 0) = 0 then return; end if;
    v_type := case when p_customer_lbp > 0 then 'sale_in'::app.cash_movement_type
                                            else 'payout_out'::app.cash_movement_type end;
    insert into app.cash_movements(shift_id, type, currency, amount, ref_txn_id, note)
      values (v_shift, v_type, 'LBP', p_customer_lbp, p_txn_id, 'auto: txn cash leg');
  end if;
end;
$$;
revoke all on function app._post_cash_for_txn(uuid, app.payment_method, numeric, numeric)
  from public;

-- =====================================================================
-- Helper: post the provider-float leg for a customer-facing transaction.
--
-- p_amount sign convention on app.float_movements:
--   + : float increases (provider owes shop more, e-recharge wallet
--       topped up, OMT credits us at settlement, ...)
--   - : float decreases (we used it up, we owe provider more cash, ...)
-- =====================================================================
create or replace function app._post_float_for_txn(
  p_txn_id   uuid,
  p_provider app.float_provider,
  p_currency app.currency_code,
  p_amount   numeric,
  p_reason   text
) returns void
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v_shop uuid; v_shift uuid; v_float uuid;
begin
  if coalesce(p_amount, 0) = 0 then return; end if;
  select shop_id, shift_id into v_shop, v_shift
    from app.transactions where id = p_txn_id;
  if v_shop is null then
    raise exception 'txn % not found while posting float leg', p_txn_id;
  end if;
  v_float := app._get_or_create_float(v_shop, p_provider, p_currency);
  insert into app.float_movements(float_id, shift_id, amount, ref_txn_id, reason)
    values (v_float, v_shift, p_amount, p_txn_id, p_reason);
end;
$$;
revoke all on function app._post_float_for_txn(uuid, app.float_provider,
  app.currency_code, numeric, text) from public;

-- =====================================================================
-- Map a money-transfer service code to its float provider.
-- =====================================================================
create or replace function app._money_transfer_provider(p_service text)
returns app.float_provider
language sql
immutable
as $$
  select case p_service
    when 'OMT_SEND'    then 'OMT_CASH'::app.float_provider
    when 'OMT_RECEIVE' then 'OMT_CASH'::app.float_provider
    when 'OMT_BILL'    then 'OMT_CASH'::app.float_provider
    when 'WU_SEND'     then 'OMT_CASH'::app.float_provider  -- WU runs on the OMT cash pool in LB
    when 'WU_RECEIVE'  then 'OMT_CASH'::app.float_provider
    when 'WHISH_SEND'  then 'WHISH'::app.float_provider
    when 'EDL_BILL'    then 'OMT_CASH'::app.float_provider  -- EDL paid via OMT counter
  end;
$$;

-- =====================================================================
-- Re-define record_omt_send to post cash + float in one go.
-- Provider is stamped from the service code, not hard-coded.
-- =====================================================================
create or replace function app.record_omt_send(
  p_shop uuid, p_till uuid,
  p_payment_method app.payment_method,
  p_gross_usd numeric, p_gross_lbp numeric,
  p_fee_usd numeric, p_fee_lbp numeric,
  p_commission_usd numeric, p_commission_lbp numeric,
  p_fx_rate numeric,
  p_external_ref text,
  p_direction app.transfer_direction,
  p_sender_full_name text, p_sender_id_type app.id_doc_type,
  p_sender_id_number text, p_sender_phone text,
  p_sender_dob date, p_sender_nationality text,
  p_beneficiary_full_name text, p_beneficiary_phone text,
  p_destination_country text,
  p_purpose_code text, p_purpose_note text,
  p_kyc_doc_url text,
  p_customer_id uuid,
  p_notes text,
  p_service_code text default 'OMT_SEND'   -- 'OMT_SEND' | 'WU_SEND' | 'WHISH_SEND'
) returns uuid
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v_txn          uuid;
  v_provider_lbl text;
  v_float_prov   app.float_provider;
begin
  if p_service_code not in ('OMT_SEND','WU_SEND','WHISH_SEND') then
    raise exception 'record_omt_send: unsupported service %', p_service_code;
  end if;
  v_provider_lbl := case p_service_code
                      when 'OMT_SEND'   then 'OMT'
                      when 'WU_SEND'    then 'WU'
                      when 'WHISH_SEND' then 'WHISH'
                    end;

  v_txn := app._insert_txn(p_shop, p_till, p_service_code, p_payment_method,
    p_gross_usd, p_gross_lbp, p_fee_usd, p_fee_lbp,
    p_commission_usd, p_commission_lbp, p_fx_rate,
    v_provider_lbl, p_external_ref,
    p_beneficiary_full_name, p_beneficiary_phone,
    p_customer_id, p_notes);

  insert into app.omt_send_details(
    txn_id, direction,
    sender_full_name, sender_id_type, sender_id_number, sender_phone,
    sender_dob, sender_nationality,
    beneficiary_full_name, beneficiary_phone, destination_country,
    purpose_code, purpose_note, kyc_doc_url
  ) values (
    v_txn, p_direction,
    p_sender_full_name, p_sender_id_type, p_sender_id_number, p_sender_phone,
    p_sender_dob, p_sender_nationality,
    p_beneficiary_full_name, p_beneficiary_phone, p_destination_country,
    p_purpose_code, p_purpose_note, p_kyc_doc_url
  );

  -- Cash leg: customer hands over gross + fee in cash.
  perform app._post_cash_for_txn(v_txn, p_payment_method,
    coalesce(p_gross_usd,0) + coalesce(p_fee_usd,0),
    coalesce(p_gross_lbp,0) + coalesce(p_fee_lbp,0));

  -- Float leg: shop now owes the provider gross (we keep fee+comm).
  v_float_prov := app._money_transfer_provider(p_service_code);
  perform app._post_float_for_txn(v_txn, v_float_prov, 'USD',
    -coalesce(p_gross_usd,0), 'send: shop owes provider gross');
  perform app._post_float_for_txn(v_txn, v_float_prov, 'LBP',
    -coalesce(p_gross_lbp,0), 'send: shop owes provider gross');

  return v_txn;
end; $$;
revoke all on function app.record_omt_send(uuid, uuid, app.payment_method,
  numeric, numeric, numeric, numeric, numeric, numeric, numeric,
  text, app.transfer_direction,
  text, app.id_doc_type, text, text, date, text,
  text, text, text, text, text, text, uuid, text, text) from public;
grant execute on function app.record_omt_send(uuid, uuid, app.payment_method,
  numeric, numeric, numeric, numeric, numeric, numeric, numeric,
  text, app.transfer_direction,
  text, app.id_doc_type, text, text, date, text,
  text, text, text, text, text, text, uuid, text, text) to authenticated;

-- =====================================================================
-- Re-define record_omt_receive (also serves WU_RECEIVE).
-- Customer presents code, cashier hands them gross.
-- =====================================================================
create or replace function app.record_omt_receive(
  p_shop uuid, p_till uuid,
  p_payment_method app.payment_method,
  p_gross_usd numeric, p_gross_lbp numeric,
  p_fee_usd numeric, p_fee_lbp numeric,
  p_commission_usd numeric, p_commission_lbp numeric,
  p_fx_rate numeric,
  p_payout_code text,
  p_beneficiary_full_name text,
  p_beneficiary_id_type app.id_doc_type,
  p_beneficiary_id_number text,
  p_beneficiary_phone text,
  p_origin_country text,
  p_kyc_doc_url text,
  p_customer_id uuid,
  p_notes text,
  p_service_code text default 'OMT_RECEIVE' -- or 'WU_RECEIVE'
) returns uuid
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v_txn          uuid;
  v_provider_lbl text;
  v_float_prov   app.float_provider;
  v_net_usd      numeric;
  v_net_lbp      numeric;
begin
  if p_service_code not in ('OMT_RECEIVE','WU_RECEIVE') then
    raise exception 'record_omt_receive: unsupported service %', p_service_code;
  end if;
  v_provider_lbl := case p_service_code
                      when 'OMT_RECEIVE' then 'OMT'
                      when 'WU_RECEIVE'  then 'WU'
                    end;

  v_txn := app._insert_txn(p_shop, p_till, p_service_code, p_payment_method,
    p_gross_usd, p_gross_lbp, p_fee_usd, p_fee_lbp,
    p_commission_usd, p_commission_lbp, p_fx_rate,
    v_provider_lbl, p_payout_code,
    p_beneficiary_full_name, p_beneficiary_phone,
    p_customer_id, p_notes);

  insert into app.omt_receive_details(
    txn_id, payout_code,
    beneficiary_full_name, beneficiary_id_type, beneficiary_id_number,
    beneficiary_phone, origin_country, kyc_doc_url
  ) values (
    v_txn, p_payout_code,
    p_beneficiary_full_name, p_beneficiary_id_type, p_beneficiary_id_number,
    p_beneficiary_phone, p_origin_country, p_kyc_doc_url
  );

  -- Cash leg: shop pays gross out, may collect a small fee from beneficiary.
  --   net cash to till = -gross + fee
  -- (Most LB payouts have no beneficiary-side fee; if fee=0 this just
  -- becomes -gross.)
  v_net_usd := -coalesce(p_gross_usd,0) + coalesce(p_fee_usd,0);
  v_net_lbp := -coalesce(p_gross_lbp,0) + coalesce(p_fee_lbp,0);
  perform app._post_cash_for_txn(v_txn, p_payment_method, v_net_usd, v_net_lbp);

  -- Float leg: provider now owes the shop gross + commission.
  v_float_prov := app._money_transfer_provider(p_service_code);
  perform app._post_float_for_txn(v_txn, v_float_prov, 'USD',
    coalesce(p_gross_usd,0) + coalesce(p_commission_usd,0),
    'receive: provider owes shop gross + commission');
  perform app._post_float_for_txn(v_txn, v_float_prov, 'LBP',
    coalesce(p_gross_lbp,0) + coalesce(p_commission_lbp,0),
    'receive: provider owes shop gross + commission');

  return v_txn;
end; $$;
revoke all on function app.record_omt_receive(uuid, uuid, app.payment_method,
  numeric, numeric, numeric, numeric, numeric, numeric, numeric,
  text, text, app.id_doc_type, text, text, text, text, uuid, text, text) from public;
grant execute on function app.record_omt_receive(uuid, uuid, app.payment_method,
  numeric, numeric, numeric, numeric, numeric, numeric, numeric,
  text, text, app.id_doc_type, text, text, text, text, uuid, text, text) to authenticated;

-- =====================================================================
-- Re-define record_bill (OMT_BILL / EDL_BILL) with cash + float legs.
-- =====================================================================
create or replace function app.record_bill(
  p_shop uuid, p_till uuid, p_service_code text,
  p_payment_method app.payment_method,
  p_gross_usd numeric, p_gross_lbp numeric,
  p_fee_usd numeric, p_fee_lbp numeric,
  p_fx_rate numeric,
  p_external_ref text,
  p_biller_code text, p_account_number text,
  p_period text, p_customer_name text,
  p_customer_id uuid, p_notes text
) returns uuid
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v_txn        uuid;
  v_float_prov app.float_provider;
begin
  if p_service_code not in ('OMT_BILL','EDL_BILL') then
    raise exception 'record_bill only for bill services, got %', p_service_code;
  end if;
  v_txn := app._insert_txn(p_shop, p_till, p_service_code, p_payment_method,
    p_gross_usd, p_gross_lbp, p_fee_usd, p_fee_lbp, 0, 0, p_fx_rate,
    p_biller_code, p_external_ref,
    p_customer_name, null,
    p_customer_id, p_notes);

  insert into app.bill_payment_details(
    txn_id, biller_code, account_number, period, customer_name
  ) values (
    v_txn, p_biller_code, p_account_number, p_period, p_customer_name
  );

  -- Cash leg: customer pays gross + fee.
  perform app._post_cash_for_txn(v_txn, p_payment_method,
    coalesce(p_gross_usd,0) + coalesce(p_fee_usd,0),
    coalesce(p_gross_lbp,0) + coalesce(p_fee_lbp,0));

  -- Float leg: shop now owes the biller's settlement counterparty
  -- gross. EDL/OMT_BILL settle through the OMT cash pool in our model.
  v_float_prov := app._money_transfer_provider(p_service_code);
  if v_float_prov is not null then
    perform app._post_float_for_txn(v_txn, v_float_prov, 'USD',
      -coalesce(p_gross_usd,0), 'bill: shop owes biller gross');
    perform app._post_float_for_txn(v_txn, v_float_prov, 'LBP',
      -coalesce(p_gross_lbp,0), 'bill: shop owes biller gross');
  end if;
  return v_txn;
end; $$;
revoke all on function app.record_bill(uuid, uuid, text, app.payment_method,
  numeric, numeric, numeric, numeric, numeric, text, text, text, text, text, uuid, text)
  from public;
grant execute on function app.record_bill(uuid, uuid, text, app.payment_method,
  numeric, numeric, numeric, numeric, numeric, text, text, text, text, text, uuid, text)
  to authenticated;

-- =====================================================================
-- Convenience wrapper for Whish — same shape as omt_send_details for
-- now (sender + beneficiary). The UI sends WHISH_SEND and gets a
-- correctly tagged provider on the txn row.
-- =====================================================================
create or replace function app.record_whish_send(
  p_shop uuid, p_till uuid,
  p_payment_method app.payment_method,
  p_gross_usd numeric, p_gross_lbp numeric,
  p_fee_usd numeric, p_fee_lbp numeric,
  p_commission_usd numeric, p_commission_lbp numeric,
  p_fx_rate numeric,
  p_external_ref text,
  p_direction app.transfer_direction,
  p_sender_full_name text, p_sender_id_type app.id_doc_type,
  p_sender_id_number text, p_sender_phone text,
  p_beneficiary_full_name text, p_beneficiary_phone text,
  p_purpose_code text, p_purpose_note text,
  p_kyc_doc_url text,
  p_customer_id uuid, p_notes text
) returns uuid
language plpgsql
security definer
set search_path = app, public
as $$
begin
  return app.record_omt_send(
    p_shop, p_till, p_payment_method,
    p_gross_usd, p_gross_lbp, p_fee_usd, p_fee_lbp,
    p_commission_usd, p_commission_lbp, p_fx_rate,
    p_external_ref, p_direction,
    p_sender_full_name, p_sender_id_type, p_sender_id_number, p_sender_phone,
    null, null,
    p_beneficiary_full_name, p_beneficiary_phone, null,
    p_purpose_code, p_purpose_note, p_kyc_doc_url,
    p_customer_id, p_notes,
    'WHISH_SEND'
  );
end; $$;
revoke all on function app.record_whish_send(uuid, uuid, app.payment_method,
  numeric, numeric, numeric, numeric, numeric, numeric, numeric,
  text, app.transfer_direction,
  text, app.id_doc_type, text, text, text, text, text, text, text, uuid, text)
  from public;
grant execute on function app.record_whish_send(uuid, uuid, app.payment_method,
  numeric, numeric, numeric, numeric, numeric, numeric, numeric,
  text, app.transfer_direction,
  text, app.id_doc_type, text, text, text, text, text, text, text, uuid, text)
  to authenticated;

-- =====================================================================
-- Deferred constraint trigger: every completed money-transfer / bill
-- transaction must end up with at least one float_movement row. The
-- trigger fires at COMMIT, so the record_* functions above can post the
-- float leg after the txn insert in the same transaction.
-- =====================================================================
create or replace function app._money_transfer_require_movement()
returns trigger
language plpgsql
as $$
declare
  ok boolean;
  is_money_transfer boolean;
begin
  if new.status <> 'completed' then return null; end if;

  is_money_transfer := new.service_code in
    ('OMT_SEND','OMT_RECEIVE','OMT_BILL','EDL_BILL',
     'WU_SEND','WU_RECEIVE','WHISH_SEND');
  if not is_money_transfer then return null; end if;

  -- If both gross sides are 0, no money moved -> nothing to require.
  if coalesce(new.gross_usd,0) = 0 and coalesce(new.gross_lbp,0) = 0 then
    return null;
  end if;

  select exists (
    select 1 from app.float_movements
    where ref_txn_id = new.id
  ) into ok;
  if not ok then
    raise exception 'money-transfer txn % (service %) has no float_movement leg',
      new.id, new.service_code;
  end if;
  return null;
end;
$$;

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

-- End migration 0018 ----------------------------------------------------