-- =====================================================================
-- 0022_atomic_sale_coupling.sql
--
-- Today the cashier UI calls record_recharge() and record_goods_sale()
-- but never separately calls sell_voucher() or posts the e-float debit
-- / sale_out stock movement. The deferred constraint triggers from
-- 0005 (_recharge_require_movement, _goods_sale_require_movement)
-- therefore reject every commit at end-of-transaction… *unless* the
-- trigger never fires because the RLS-protected detail row blocked
-- the INSERT, in which case the txn header silently survives without
-- any inventory or float impact.
--
-- Either way the books are wrong: a "sold" voucher serial keeps
-- showing as in_stock, the e-float balance does not drop, and a phone
-- sold off the shelf does not decrement stock_on_hand.
--
-- This migration folds the inventory/float legs INTO the record_*
-- functions themselves, in the same SECURITY DEFINER transaction:
--
--   record_recharge -> if voucher_serial: mark voucher sold + post
--                       sale_out (-1) for the voucher SKU.
--                     else (e-recharge): post a negative float_movement
--                       for ALFA_ERECHARGE / TOUCH_ERECHARGE / OGERO_ERECHARGE
--                       sized at unit_cost_usd (or gross_usd as fallback).
--
--   record_goods_sale -> post a sale_out stock_movement for the SKU
--                        with -p_qty.
--
-- Both are wrapped in a single transaction so either everything posts
-- or the whole sale rolls back. The deferred coupling triggers from
-- 0005 then pass naturally.
-- =====================================================================

set search_path = app, public;

-- ---------------------------------------------------------------------
-- Internal helper: mark a voucher sold + post sale_out, callable from
-- inside record_recharge. Mirrors app.sell_voucher() but does not check
-- auth.uid() against the txn owner because record_recharge is itself
-- security definer running as the cashier who created the txn.
-- ---------------------------------------------------------------------
create or replace function app._sell_voucher_internal(
  p_txn_id uuid,
  p_serial text
) returns void
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v app.voucher_inventory%rowtype;
  t app.transactions%rowtype;
begin
  select * into t from app.transactions where id = p_txn_id;
  if t.id is null then raise exception 'txn not found'; end if;

  select * into v from app.voucher_inventory
    where serial = p_serial for update;
  if v.serial is null then
    raise exception 'voucher % not found', p_serial;
  end if;
  if v.shop_id <> t.shop_id then
    raise exception 'voucher % belongs to a different shop', p_serial;
  end if;
  if v.status <> 'in_stock' then
    raise exception 'voucher % is not in_stock (status=%)', p_serial, v.status;
  end if;

  update app.voucher_inventory
     set status = 'sold',
         sold_txn_id = p_txn_id,
         sold_at = now(),
         status_changed_by = auth.uid()
   where serial = p_serial;

  insert into app.stock_movements(
    sku, shop_id, shift_id, type, qty_delta, ref_txn_id, reason
  ) values (
    v.sku, v.shop_id, t.shift_id,
    'sale_out'::app.stock_movement_type,
    -1, p_txn_id, 'voucher ' || p_serial
  );
end;
$$;
revoke all on function app._sell_voucher_internal(uuid, text) from public;

-- ---------------------------------------------------------------------
-- Internal helper: post the e-float debit for an e-recharge.
-- ---------------------------------------------------------------------
create or replace function app._erecharge_post_float(
  p_txn_id   uuid,
  p_operator text,
  p_amount   numeric           -- positive cost; the row will be negated
) returns void
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v_provider app.float_provider;
  v_float    uuid;
  t          app.transactions%rowtype;
begin
  if p_amount is null or p_amount <= 0 then
    raise exception 'e-recharge cost must be > 0 (got %)', p_amount;
  end if;

  select * into t from app.transactions where id = p_txn_id;
  if t.id is null then raise exception 'txn not found'; end if;

  v_provider := case upper(p_operator)
    when 'ALFA'  then 'ALFA_ERECHARGE'::app.float_provider
    when 'TOUCH' then 'TOUCH_ERECHARGE'::app.float_provider
    when 'OGERO' then 'OGERO_ERECHARGE'::app.float_provider
    else null
  end;

  if v_provider is null then
    -- Unmapped operator (IDM, CYBERIA, TERRANET): fall back to OMT_DIGITAL
    -- so the recharge_require_movement trigger sees a negative leg.
    v_provider := 'OMT_DIGITAL'::app.float_provider;
  end if;

  v_float := app._get_or_create_float(t.shop_id, v_provider, 'USD'::app.currency_code);

  insert into app.float_movements(
    float_id, shift_id, amount, ref_txn_id, reason
  ) values (
    v_float, t.shift_id, -p_amount, p_txn_id,
    'e-recharge ' || coalesce(p_operator, '?')
  );
end;
$$;
revoke all on function app._erecharge_post_float(uuid, text, numeric) from public;

-- ---------------------------------------------------------------------
-- Re-define record_recharge to fold in voucher / e-float posting, and
-- the cash leg via the helper added in 0018.
-- ---------------------------------------------------------------------
create or replace function app.record_recharge(
  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_operator text, p_msisdn text, p_product_code text,
  p_voucher_serial text, p_e_recharge_ref text,
  p_unit_face_usd numeric, p_unit_cost_usd numeric,
  p_notes text
) returns uuid
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v_txn       uuid;
  v_serial    text := nullif(btrim(p_voucher_serial),'');
  v_eref      text := nullif(btrim(p_e_recharge_ref),'');
  v_cost_usd  numeric;
begin
  if v_serial is null and v_eref is null then
    raise exception 'either voucher_serial or e_recharge_ref is required';
  end if;
  if v_serial is not null and v_eref is not null then
    raise exception 'pass either voucher_serial OR e_recharge_ref, not both';
  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_operator, v_serial, null, p_msisdn, null, p_notes);

  insert into app.recharge_details(
    txn_id, operator, msisdn, product_code,
    voucher_serial, e_recharge_provider_ref,
    unit_face_value_usd, unit_cost_usd
  ) values (
    v_txn, p_operator, p_msisdn, p_product_code,
    v_serial, v_eref,
    p_unit_face_usd, p_unit_cost_usd
  );

  -- ---- inventory / float coupling --------------------------------
  if v_serial is not null then
    perform app._sell_voucher_internal(v_txn, v_serial);
  else
    -- e-recharge: prefer recorded unit_cost_usd, fall back to gross_usd.
    v_cost_usd := coalesce(nullif(p_unit_cost_usd,0), p_gross_usd);
    perform app._erecharge_post_float(v_txn, p_operator, v_cost_usd);
  end if;

  -- ---- cash leg (re-uses helper from 0018) -----------------------
  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)
  );

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

-- ---------------------------------------------------------------------
-- Re-define record_goods_sale to fold in the sale_out stock movement
-- and the cash leg in the same transaction.
-- ---------------------------------------------------------------------
create or replace function app.record_goods_sale(
  p_shop uuid, p_till uuid,
  p_payment_method app.payment_method,
  p_gross_usd numeric, p_gross_lbp numeric,
  p_fx_rate numeric,
  p_sku text, p_qty integer,
  p_unit_cost_usd numeric, p_unit_price_usd numeric,
  p_serial_number text,
  p_customer_id uuid, p_notes text
) returns uuid
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v_txn uuid;
begin
  if p_qty is null or p_qty <= 0 then
    raise exception 'qty must be > 0';
  end if;
  if p_sku is null or btrim(p_sku) = '' then
    raise exception 'sku required';
  end if;

  v_txn := app._insert_txn(p_shop, p_till, 'GOODS_SALE', p_payment_method,
    p_gross_usd, p_gross_lbp, 0, 0, 0, 0, p_fx_rate,
    null, null, null, null, p_customer_id, p_notes);

  insert into app.goods_sale_details(
    txn_id, sku, qty, unit_cost_usd, unit_price_usd, serial_number
  ) values (
    v_txn, p_sku, p_qty, p_unit_cost_usd, p_unit_price_usd, p_serial_number
  );

  -- Stock leg. The 0005 _stock_mov_before_insert trigger validates the
  -- ref_txn_id points at a completed sale in the same shop, and the
  -- _stock_on_hand_apply trigger refuses to go negative.
  insert into app.stock_movements(
    sku, shop_id, shift_id, type, qty_delta, ref_txn_id, reason
  ) values (
    p_sku, p_shop, (select shift_id from app.transactions where id = v_txn),
    'sale_out'::app.stock_movement_type,
    -p_qty,
    v_txn,
    case when p_serial_number is not null
         then 'goods sale serial=' || p_serial_number
         else 'goods sale' end
  );

  -- Cash leg.
  perform app._post_cash_for_txn(
    v_txn, p_payment_method,
    coalesce(p_gross_usd,0),
    coalesce(p_gross_lbp,0)
  );

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

-- ---------------------------------------------------------------------
-- Repair sales also put cash in the till (parts + labour). The original
-- record_repair from 0013 inserts only the txn header + repair detail
-- and never posts cash, so REPAIR variances were silently absorbed by
-- the next cashier's drop. Wrap the existing function so it posts cash.
-- ---------------------------------------------------------------------
create or replace function app.record_repair(
  p_shop uuid, p_till uuid,
  p_payment_method app.payment_method,
  p_gross_usd numeric, p_gross_lbp numeric,
  p_fx_rate numeric,
  p_device_type text, p_device_imei text,
  p_issue_summary text, p_warranty_days integer,
  p_customer_id uuid, p_notes text
) returns uuid
language plpgsql
security definer
set search_path = app, public
as $$
declare v_txn uuid;
begin
  v_txn := app._insert_txn(p_shop, p_till, 'REPAIR', p_payment_method,
    p_gross_usd, p_gross_lbp, 0, 0, 0, 0, p_fx_rate,
    null, null, null, null, p_customer_id, p_notes);

  insert into app.repair_details(
    txn_id, device_type, device_imei, issue_summary, warranty_days
  ) values (
    v_txn, p_device_type, p_device_imei, p_issue_summary, p_warranty_days
  );

  perform app._post_cash_for_txn(
    v_txn, p_payment_method,
    coalesce(p_gross_usd,0),
    coalesce(p_gross_lbp,0)
  );
  return v_txn;
end;
$$;
revoke all on function app.record_repair(uuid, uuid, app.payment_method,
  numeric, numeric, numeric, text, text, text, integer, uuid, text) from public;
grant execute on function app.record_repair(uuid, uuid, app.payment_method,
  numeric, numeric, numeric, text, text, text, integer, uuid, text) to authenticated;