-- =====================================================================
-- Migration 0015 — Mid-Day Shift Cash Drops
--
-- Enables cashiers to "drop" large sums of accumulated cash (esp USD payout cash)
-- into a safe midway through a shift, removing their liability without 
-- requiring them to close out and open a brand new shift.
-- =====================================================================

create or replace function app.record_cash_drop(
  p_shift_id uuid,
  p_drop_usd numeric,
  p_drop_lbp numeric,
  p_notes text default null
) returns void
language plpgsql
security definer
set search_path = app, public
as $$
declare
  v_shop uuid;
  v_till uuid;
  v_status text;
  v_user uuid;
begin
  select shop_id, till_id, status, user_id
    into v_shop, v_till, v_status, v_user
    from app.shifts
    where id = p_shift_id;
    
  if v_shop is null then
    raise exception 'Shift not found';
  end if;

  if v_user <> auth.uid() and not app.has_role_in_shop(v_shop, 'manager') then
    raise exception 'Only the shift owner or a manager may record a drop';
  end if;
  
  if v_status <> 'open' then
    raise exception 'Must have an open shift to record a soft drop';
  end if;
  
  if p_drop_usd < 0 or p_drop_lbp < 0 then
    raise exception 'Drop amounts cannot be negative';
  end if;
  
  if p_drop_usd = 0 and p_drop_lbp = 0 then
    raise exception 'Must drop > 0 in at least one currency';
  end if;

  -- Create a matching cash_movements record reducing the drawer balance
  insert into app.cash_movements(
    shift_id, movement_type, currency, amount, external_ref
  )
  select 
    p_shift_id,
    'safe_drop',
    case when d.idx = 1 then 'USD' else 'LBP' end,
    case when d.idx = 1 then p_drop_usd else p_drop_lbp end,
    p_notes
  from (values (1), (2)) as d(idx)
  where (d.idx = 1 and p_drop_usd > 0)
     or (d.idx = 2 and p_drop_lbp > 0);

  perform app.log_auth_event('safe_drop_recorded', v_shop, null,
                             jsonb_build_object('shift_id', p_shift_id, 'usd', p_drop_usd, 'lbp', p_drop_lbp));
end;
$$;

revoke all on function app.record_cash_drop(uuid, numeric, numeric, text) from public;
grant execute on function app.record_cash_drop(uuid, numeric, numeric, text) to authenticated;