-- =====================================================================
-- 0028_live_drawer.sql
--
-- Cashiers and owners need an in-shift view of what the drawer should
-- currently hold before the blind close. The existing close flow already
-- computes this from app.cash_movements; this migration exposes the same
-- math as a read-only RPC for the live UI.
-- =====================================================================

set search_path = app, public;

create or replace function app.live_drawer(p_shift_id uuid)
returns table (
  shift_id uuid,
  expected_usd numeric,
  expected_lbp numeric,
  customer_in_usd numeric,
  customer_in_lbp numeric,
  payout_out_usd numeric,
  payout_out_lbp numeric,
  dropped_to_safe_usd numeric,
  dropped_to_safe_lbp numeric,
  fx_net_usd numeric,
  fx_net_lbp numeric,
  txn_count bigint,
  last_txn_at timestamptz
)
language sql
security definer
set search_path = app, public
stable
as $$
  with s as (
    select sh.id, sh.shop_id, sh.user_id, sh.status
    from app.shifts sh
    where sh.id = p_shift_id
  ),
  authz as (
    select 1
    from s
    where s.user_id = auth.uid()
       or app.has_any_role_in_shop(
            s.shop_id,
            array['owner','manager','auditor']::app.business_role[]
          )
  ),
  cash as (
    select
      cm.shift_id,
      coalesce(sum(cm.amount) filter (where cm.currency = 'USD'), 0) as expected_usd,
      coalesce(sum(cm.amount) filter (where cm.currency = 'LBP'), 0) as expected_lbp,
      coalesce(sum(cm.amount) filter (where cm.type = 'sale_in' and cm.currency = 'USD'), 0) as customer_in_usd,
      coalesce(sum(cm.amount) filter (where cm.type = 'sale_in' and cm.currency = 'LBP'), 0) as customer_in_lbp,
      coalesce(sum(-cm.amount) filter (where cm.type = 'payout_out' and cm.currency = 'USD'), 0) as payout_out_usd,
      coalesce(sum(-cm.amount) filter (where cm.type = 'payout_out' and cm.currency = 'LBP'), 0) as payout_out_lbp,
      coalesce(sum(-cm.amount) filter (where cm.type = 'drop_to_safe' and cm.currency = 'USD'), 0) as dropped_to_safe_usd,
      coalesce(sum(-cm.amount) filter (where cm.type = 'drop_to_safe' and cm.currency = 'LBP'), 0) as dropped_to_safe_lbp,
      coalesce(sum(cm.amount) filter (where cm.type in ('fx_swap_in', 'fx_swap_out') and cm.currency = 'USD'), 0) as fx_net_usd,
      coalesce(sum(cm.amount) filter (where cm.type in ('fx_swap_in', 'fx_swap_out') and cm.currency = 'LBP'), 0) as fx_net_lbp
    from app.cash_movements cm
    where cm.shift_id = p_shift_id
    group by cm.shift_id
  ),
  tx as (
    select
      t.shift_id,
      count(*) filter (where t.status = 'completed') as txn_count,
      max(t.occurred_at) filter (where t.status = 'completed') as last_txn_at
    from app.transactions t
    where t.shift_id = p_shift_id
    group by t.shift_id
  )
  select
    s.id as shift_id,
    coalesce(cash.expected_usd, 0) as expected_usd,
    coalesce(cash.expected_lbp, 0) as expected_lbp,
    coalesce(cash.customer_in_usd, 0) as customer_in_usd,
    coalesce(cash.customer_in_lbp, 0) as customer_in_lbp,
    coalesce(cash.payout_out_usd, 0) as payout_out_usd,
    coalesce(cash.payout_out_lbp, 0) as payout_out_lbp,
    coalesce(cash.dropped_to_safe_usd, 0) as dropped_to_safe_usd,
    coalesce(cash.dropped_to_safe_lbp, 0) as dropped_to_safe_lbp,
    coalesce(cash.fx_net_usd, 0) as fx_net_usd,
    coalesce(cash.fx_net_lbp, 0) as fx_net_lbp,
    coalesce(tx.txn_count, 0) as txn_count,
    tx.last_txn_at
  from s
  join authz on true
  left join cash on cash.shift_id = s.id
  left join tx on tx.shift_id = s.id;
$$;

revoke all on function app.live_drawer(uuid) from public;
grant execute on function app.live_drawer(uuid) to authenticated;