-- =====================================================================
-- 0029_my_active_shift.sql
--
-- Shift Control needs visibility into both OPEN and DECLARED shifts so a
-- cashier can declare, review the drawer, and still finalize after a
-- refresh. Transaction entry keeps using my_open_shift so no sales can be
-- posted once close has been declared.
-- =====================================================================

set search_path = app, public;

create or replace function app.my_active_shift(p_shop uuid)
returns table (
  shift_id uuid,
  till_id uuid,
  opened_at timestamptz,
  status app.shift_status,
  opening_usd numeric,
  opening_lbp numeric,
  declared_at timestamptz,
  declared_close_usd numeric,
  declared_close_lbp numeric
)
language sql
security definer
set search_path = app, public
stable
as $$
  select
    id,
    till_id,
    opened_at,
    status,
    opening_usd,
    opening_lbp,
    declared_at,
    declared_close_usd,
    declared_close_lbp
  from app.shifts
  where shop_id = p_shop
    and user_id = auth.uid()
    and status <> 'closed'
  order by opened_at desc
  limit 1;
$$;

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