94 lines
3.6 KiB
PL/PgSQL
94 lines
3.6 KiB
PL/PgSQL
-- =====================================================================
|
|
-- 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; |