114 lines
3.8 KiB
PL/PgSQL
114 lines
3.8 KiB
PL/PgSQL
-- =====================================================================
|
|
-- 0027_till_management.sql
|
|
--
|
|
-- Owner-facing till management:
|
|
-- - app.create_till(shop, name) -> uuid
|
|
-- - app.rename_till(till, name) -> void
|
|
-- - app.set_till_active(till, is_active) -> void
|
|
-- - v_manage_tills view (owner sees all, including inactive)
|
|
-- =====================================================================
|
|
|
|
set search_path = app, public;
|
|
|
|
create or replace function app.create_till(
|
|
p_shop uuid,
|
|
p_name text
|
|
) returns uuid
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
v_id uuid;
|
|
v_name text := nullif(btrim(p_name), '');
|
|
begin
|
|
if v_name is null then raise exception 'till name is required'; end if;
|
|
if not app.has_role_in_shop(p_shop, 'owner') then
|
|
raise exception 'owner role required';
|
|
end if;
|
|
insert into app.tills(shop_id, name) values (p_shop, v_name)
|
|
returning id into v_id;
|
|
perform app.log_auth_event('till_created', p_shop, null,
|
|
jsonb_build_object('till_id', v_id, 'name', v_name));
|
|
return v_id;
|
|
exception
|
|
when unique_violation then
|
|
raise exception 'a till with this name already exists in the shop';
|
|
end;
|
|
$$;
|
|
revoke all on function app.create_till(uuid, text) from public;
|
|
grant execute on function app.create_till(uuid, text) to authenticated;
|
|
|
|
create or replace function app.rename_till(
|
|
p_till uuid,
|
|
p_name text
|
|
) returns void
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
v_shop uuid;
|
|
v_name text := nullif(btrim(p_name), '');
|
|
begin
|
|
if v_name is null then raise exception 'till name is required'; end if;
|
|
select shop_id into v_shop from app.tills where id = p_till;
|
|
if v_shop is null then raise exception 'till not found'; end if;
|
|
if not app.has_role_in_shop(v_shop, 'owner') then
|
|
raise exception 'owner role required';
|
|
end if;
|
|
update app.tills set name = v_name where id = p_till;
|
|
perform app.log_auth_event('till_renamed', v_shop, null,
|
|
jsonb_build_object('till_id', p_till, 'name', v_name));
|
|
exception
|
|
when unique_violation then
|
|
raise exception 'a till with this name already exists in the shop';
|
|
end;
|
|
$$;
|
|
revoke all on function app.rename_till(uuid, text) from public;
|
|
grant execute on function app.rename_till(uuid, text) to authenticated;
|
|
|
|
create or replace function app.set_till_active(
|
|
p_till uuid,
|
|
p_active boolean
|
|
) returns void
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
v_shop uuid;
|
|
begin
|
|
select shop_id into v_shop from app.tills where id = p_till;
|
|
if v_shop is null then raise exception 'till not found'; end if;
|
|
if not app.has_role_in_shop(v_shop, 'owner') then
|
|
raise exception 'owner role required';
|
|
end if;
|
|
-- Block deactivating a till that has an open shift on it.
|
|
if p_active = false and exists (
|
|
select 1 from app.shifts where till_id = p_till and status <> 'closed'
|
|
) then
|
|
raise exception 'cannot deactivate a till with an open or pending shift';
|
|
end if;
|
|
update app.tills set is_active = coalesce(p_active, true) where id = p_till;
|
|
perform app.log_auth_event(
|
|
case when p_active then 'till_activated' else 'till_deactivated' end,
|
|
v_shop, null, jsonb_build_object('till_id', p_till));
|
|
end;
|
|
$$;
|
|
revoke all on function app.set_till_active(uuid, boolean) from public;
|
|
grant execute on function app.set_till_active(uuid, boolean) to authenticated;
|
|
|
|
-- Owner view that also includes inactive tills, for the management UI.
|
|
drop view if exists app.v_manage_tills;
|
|
create view app.v_manage_tills as
|
|
select t.id as till_id, t.shop_id, t.name, t.is_active, t.created_at
|
|
from app.tills t
|
|
where exists (
|
|
select 1 from app.user_shop_assignments a
|
|
where a.shop_id = t.shop_id
|
|
and a.user_id = auth.uid()
|
|
and a.role in ('owner', 'manager')
|
|
);
|
|
grant select on app.v_manage_tills to authenticated;
|