Shifts

-- better-supabase module: shifts
-- @bsb-block shifts@1 managed
-- @bsb-hash sha256:6185309016fbd4f872e51d246317850fbb2d68b9c83b718028da35c7a573eb97
-- Shift templates that generate shifts on weekdays, shifts planned in draft and published per team and location, assignments checked for overlap, approved leave, required qualifications and rest time, open shift requests, swaps between members, availability, a roster, planned hours against contract hours, and time entries from worked shifts.
-- Managed by `better-supabase add`; re-running it rewrites this file unless it was edited by hand.
-- Change it through `blocks` in better-supabase.config.ts and the module's SQL hooks.

create or replace function better_supabase.actor_may_write()
returns boolean
language sql
stable
security invoker
set search_path = ''
as $$
  select case
    when auth.jwt() is null then true
    when auth.jwt() -> 'act' ->> 'kind' = 'support'
      then coalesce(auth.jwt() -> 'act' -> 'read_only' = 'false'::jsonb, false)
    when auth.jwt() -> 'act' ->> 'kind' = 'agent'
      then coalesce(auth.jwt() -> 'act' -> 'read_only', 'false'::jsonb) = 'false'::jsonb
    when auth.jwt() -> 'act' ->> 'kind' is not null then false
    when auth.jwt() -> 'act' ->> 'session_id' is not null
      and auth.jwt() -> 'act' ->> 'kind' is null then false
    else true
  end
$$;
revoke execute on function better_supabase.actor_may_write() from public;
grant execute on function better_supabase.actor_may_write() to anon, authenticated, service_role;

create or replace function better_supabase.raise_actor_readonly()
returns void
language plpgsql
volatile
set search_path = ''
as $$
begin
  raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
end;
$$;
revoke execute on function better_supabase.raise_actor_readonly() from public;
grant execute on function better_supabase.raise_actor_readonly() to anon, authenticated, service_role;
-- The tenant scope policies call the module's functions, so they go before
-- the module drops or replaces one and come back at the end.
do $$
begin
  begin
    drop policy if exists bsb_tenant_scope on "better_supabase"."shift_templates";
    drop policy if exists bsb_tenant_scope_select on "better_supabase"."shift_templates";
    drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shift_templates";
    drop policy if exists bsb_tenant_scope_update on "better_supabase"."shift_templates";
    drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shift_templates";
  exception when undefined_table or invalid_schema_name then null;
  end;
  begin
    drop policy if exists bsb_tenant_scope on "better_supabase"."shifts";
    drop policy if exists bsb_tenant_scope_select on "better_supabase"."shifts";
    drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shifts";
    drop policy if exists bsb_tenant_scope_update on "better_supabase"."shifts";
    drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shifts";
  exception when undefined_table or invalid_schema_name then null;
  end;
  begin
    drop policy if exists bsb_tenant_scope on "better_supabase"."shift_assignments";
    drop policy if exists bsb_tenant_scope_select on "better_supabase"."shift_assignments";
    drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shift_assignments";
    drop policy if exists bsb_tenant_scope_update on "better_supabase"."shift_assignments";
    drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shift_assignments";
  exception when undefined_table or invalid_schema_name then null;
  end;
  begin
    drop policy if exists bsb_tenant_scope on "better_supabase"."shift_swaps";
    drop policy if exists bsb_tenant_scope_select on "better_supabase"."shift_swaps";
    drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shift_swaps";
    drop policy if exists bsb_tenant_scope_update on "better_supabase"."shift_swaps";
    drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shift_swaps";
  exception when undefined_table or invalid_schema_name then null;
  end;
  begin
    drop policy if exists bsb_tenant_scope on "better_supabase"."shift_availability";
    drop policy if exists bsb_tenant_scope_select on "better_supabase"."shift_availability";
    drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shift_availability";
    drop policy if exists bsb_tenant_scope_update on "better_supabase"."shift_availability";
    drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shift_availability";
  exception when undefined_table or invalid_schema_name then null;
  end;
end $$;

create schema if not exists better_supabase;
grant usage on schema better_supabase to anon, authenticated, service_role;
-- Shifts: templates that generate shifts, shifts planned in draft and
-- published, assignments checked for overlap, leave, qualifications and rest,
-- open shift requests, swaps between members, and availability.

create extension if not exists btree_gist with schema extensions;

create table if not exists "better_supabase"."shift_templates" (
  "id" uuid primary key default gen_random_uuid(),
  "tenant_id" uuid not null,
  "name" text not null check (length("name") between 1 and 200),
  "team_id" uuid references "better_supabase"."teams" ("id") on delete set null,
  "location_id" uuid,
  -- An end at or before the start runs into the next day.
  "starts_time" time not null,
  "ends_time" time not null,
  "break_minutes" integer not null default 0 check ("break_minutes" >= 0 and "break_minutes" <= 720),
  "headcount" integer not null default 1 check ("headcount" >= 1 and "headcount" <= 500),
  -- Qualification type keys (qualifications block) everyone on the shift holds.
  "required_qualifications" text[] not null default '{}',
  -- ISO weekdays, 1 (Monday) to 7 (Sunday), generate_shifts creates shifts on.
  "weekdays" integer[] not null default '{1,2,3,4,5}' check ("weekdays" <@ array[1, 2, 3, 4, 5, 6, 7]),
  "archived_at" timestamptz,
  "created_at" timestamptz not null default now(),
  "updated_at" timestamptz not null default now(),
  unique ("tenant_id", "id")
);
create index if not exists "shift_templates_tenant_idx" on "better_supabase"."shift_templates" ("tenant_id", "name");
create index if not exists "shift_templates_team_idx" on "better_supabase"."shift_templates" ("team_id") where "team_id" is not null;
create index if not exists "shift_templates_location_idx" on "better_supabase"."shift_templates" ("location_id") where "location_id" is not null;
alter table "better_supabase"."shift_templates" enable row level security;
revoke all on "better_supabase"."shift_templates" from public, anon, authenticated;
grant select on "better_supabase"."shift_templates" to authenticated;
grant all on "better_supabase"."shift_templates" to service_role;
drop policy if exists shift_templates_read on "better_supabase"."shift_templates";
create policy shift_templates_read on "better_supabase"."shift_templates"
  for select to authenticated
  using ("tenant_id" in (select better_supabase.tenant_ids_with('shifts.read')));

create table if not exists "better_supabase"."shifts" (
  "id" uuid primary key default gen_random_uuid(),
  "tenant_id" uuid not null,
  "template_id" uuid,
  "team_id" uuid references "better_supabase"."teams" ("id") on delete set null,
  "location_id" uuid,
  "starts_at" timestamptz not null,
  "ends_at" timestamptz not null,
  "break_minutes" integer not null default 0 check ("break_minutes" >= 0 and "break_minutes" <= 720),
  "headcount" integer not null default 1 check ("headcount" >= 1 and "headcount" <= 500),
  "required_qualifications" text[] not null default '{}',
  -- draft: planners see it; published: everyone with shifts.read does.
  "status" text not null default 'draft' check ("status" in ('draft', 'published', 'canceled')),
  "note" text check ("note" is null or length("note") <= 2000),
  "published_at" timestamptz,
  "canceled_at" timestamptz,
  "created_by" uuid,
  "created_at" timestamptz not null default now(),
  "updated_at" timestamptz not null default now(),
  check ("ends_at" > "starts_at"),
  check ("ends_at" - "starts_at" <= interval '24 hours'),
  unique ("tenant_id", "id"),
  foreign key ("tenant_id", "template_id") references "better_supabase"."shift_templates" ("tenant_id", "id") on delete set null ("template_id")
);
create index if not exists "shifts_tenant_idx" on "better_supabase"."shifts" ("tenant_id", "starts_at");
create index if not exists "shifts_template_idx" on "better_supabase"."shifts" ("tenant_id", "template_id") where "template_id" is not null;
create index if not exists "shifts_team_idx" on "better_supabase"."shifts" ("team_id") where "team_id" is not null;
create index if not exists "shifts_location_idx" on "better_supabase"."shifts" ("location_id") where "location_id" is not null;
alter table "better_supabase"."shifts" enable row level security;
revoke all on "better_supabase"."shifts" from public, anon, authenticated;
grant select on "better_supabase"."shifts" to authenticated;
grant all on "better_supabase"."shifts" to service_role;
drop policy if exists shifts_read on "better_supabase"."shifts";
create policy shifts_read on "better_supabase"."shifts"
  for select to authenticated
  using ("tenant_id" in (select better_supabase.tenant_ids_with('shifts.write')) or ("status" <> 'draft' and "tenant_id" in (select better_supabase.tenant_ids_with('shifts.read'))));

create table if not exists "better_supabase"."shift_assignments" (
  "id" uuid primary key default gen_random_uuid(),
  "tenant_id" uuid not null,
  "shift_id" uuid not null,
  "user_id" uuid not null,
  -- requested: the member asked for an open shift; released: no longer on it.
  "status" text not null default 'assigned' check ("status" in ('assigned', 'requested', 'released')),
  -- The shift's times, copied so the exclusion constraint can read them.
  "starts_at" timestamptz not null,
  "ends_at" timestamptz not null,
  "assigned_by" uuid,
  "created_at" timestamptz not null default now(),
  "updated_at" timestamptz not null default now(),
  unique ("shift_id", "user_id"),
  unique ("tenant_id", "id"),
  foreign key ("tenant_id", "shift_id") references "better_supabase"."shifts" ("tenant_id", "id") on delete cascade,
  constraint "shift_assignments_no_overlap" exclude using gist (
    "tenant_id" with =,
    "user_id" with =,
    tstzrange("starts_at", "ends_at") with &&
  ) where ("status" = 'assigned')
);
create index if not exists "shift_assignments_tenant_idx" on "better_supabase"."shift_assignments" ("tenant_id", "shift_id");
create index if not exists "shift_assignments_user_idx" on "better_supabase"."shift_assignments" ("user_id", "starts_at");
alter table "better_supabase"."shift_assignments" enable row level security;
revoke all on "better_supabase"."shift_assignments" from public, anon, authenticated;
grant select on "better_supabase"."shift_assignments" to authenticated;
grant all on "better_supabase"."shift_assignments" to service_role;
drop policy if exists shift_assignments_read on "better_supabase"."shift_assignments";
create policy shift_assignments_read on "better_supabase"."shift_assignments"
  for select to authenticated
  using ("user_id" = (select auth.uid()) or "tenant_id" in (select better_supabase.tenant_ids_with('shifts.read')));

create table if not exists "better_supabase"."shift_swaps" (
  "id" uuid primary key default gen_random_uuid(),
  "tenant_id" uuid not null,
  "assignment_id" uuid not null,
  "from_user_id" uuid not null,
  "to_user_id" uuid not null,
  "status" text not null default 'pending' check ("status" in ('pending', 'approved', 'rejected', 'canceled')),
  "approval_id" uuid,
  "note" text check ("note" is null or length("note") <= 2000),
  "decided_by" uuid,
  "decided_at" timestamptz,
  "created_at" timestamptz not null default now(),
  check ("to_user_id" <> "from_user_id"),
  foreign key ("tenant_id", "assignment_id") references "better_supabase"."shift_assignments" ("tenant_id", "id") on delete cascade
);
create index if not exists "shift_swaps_tenant_idx" on "better_supabase"."shift_swaps" ("tenant_id", "assignment_id");
create unique index if not exists "shift_swaps_pending_idx" on "better_supabase"."shift_swaps" ("assignment_id") where "status" = 'pending';
create index if not exists "shift_swaps_to_user_idx" on "better_supabase"."shift_swaps" ("to_user_id");
alter table "better_supabase"."shift_swaps" enable row level security;
revoke all on "better_supabase"."shift_swaps" from public, anon, authenticated;
grant select on "better_supabase"."shift_swaps" to authenticated;
grant all on "better_supabase"."shift_swaps" to service_role;
drop policy if exists shift_swaps_read on "better_supabase"."shift_swaps";
create policy shift_swaps_read on "better_supabase"."shift_swaps"
  for select to authenticated
  using ("from_user_id" = (select auth.uid()) or "to_user_id" = (select auth.uid()) or "tenant_id" in (select better_supabase.tenant_ids_with('shifts.manage')));

create table if not exists "better_supabase"."shift_availability" (
  "id" uuid primary key default gen_random_uuid(),
  "tenant_id" uuid not null,
  "user_id" uuid not null,
  "on_date" date not null,
  -- Both null: the whole day.
  "from_time" time,
  "until_time" time,
  "kind" text not null check ("kind" in ('available', 'unavailable', 'preferred')),
  "note" text check ("note" is null or length("note") <= 500),
  "created_at" timestamptz not null default now(),
  check (("from_time" is null) = ("until_time" is null)),
  check ("until_time" is null or "until_time" > "from_time"),
  unique ("tenant_id", "user_id", "on_date")
);
create index if not exists "shift_availability_day_idx" on "better_supabase"."shift_availability" ("tenant_id", "on_date");
alter table "better_supabase"."shift_availability" enable row level security;
revoke all on "better_supabase"."shift_availability" from public, anon, authenticated;
grant select on "better_supabase"."shift_availability" to authenticated;
grant all on "better_supabase"."shift_availability" to service_role;
drop policy if exists shift_availability_read on "better_supabase"."shift_availability";
create policy shift_availability_read on "better_supabase"."shift_availability"
  for select to authenticated
  using ("user_id" = (select auth.uid()) or "tenant_id" in (select better_supabase.tenant_ids_with('shifts.write')));

-- Creates a shift template, or changes one (template_id). An end at or before
-- the start runs into the next day. weekdays are ISO weekdays (1 is Monday)
-- generate_shifts creates shifts on.
create or replace function "better_supabase"."save_shift_template"(tenant uuid, name text, starts_time time, ends_time time, weekdays integer[] default '{1,2,3,4,5}', team_id uuid default null, location_id uuid default null, break_minutes integer default 0, headcount integer default 1, required_qualifications text[] default '{}', template_id uuid default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_row "better_supabase"."shift_templates"%rowtype;
  v_name text := nullif(trim(save_shift_template.name), '');
  v_required text[] := array(select distinct k from unnest(coalesce(save_shift_template.required_qualifications, '{}')) k where k is not null order by k);
  v_weekdays integer[] := array(select distinct d from unnest(coalesce(save_shift_template.weekdays, '{}')) d where d is not null order by d);
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', save_shift_template.tenant, 'shifts.write'), false)) then
    raise exception 'not allowed to plan shifts' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if v_name is null or length(v_name) > 200 then
    raise exception 'a shift template needs a name of at most 200 characters' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if save_shift_template.starts_time is null or save_shift_template.ends_time is null then
    raise exception 'a shift template needs a start and an end time' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if not (v_weekdays <@ array[1, 2, 3, 4, 5, 6, 7]) then
    raise exception 'weekdays are 1 (Monday) to 7 (Sunday)' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if coalesce(save_shift_template.break_minutes, 0) not between 0 and 720 then
    raise exception 'break_minutes is between 0 and 720' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if coalesce(save_shift_template.headcount, 1) not between 1 and 500 then
    raise exception 'headcount is between 1 and 500' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if save_shift_template.team_id is not null and not exists (
    select 1 from "better_supabase"."teams" o where o."id" = save_shift_template.team_id and o."tenant_id" = save_shift_template.tenant
  ) then
    raise exception 'the team isn''t in this organization' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if save_shift_template.location_id is not null then
    raise exception 'location_id needs the assets block' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if cardinality(coalesce(v_required, '{}')) > 0 then
    raise exception 'required_qualifications needs the qualifications block' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if save_shift_template.template_id is null then
    insert into "better_supabase"."shift_templates" ("tenant_id", "name", "team_id", "location_id", "starts_time", "ends_time", "break_minutes", "headcount", "required_qualifications", "weekdays")
    values (save_shift_template.tenant, v_name, save_shift_template.team_id, save_shift_template.location_id, save_shift_template.starts_time, save_shift_template.ends_time, coalesce(save_shift_template.break_minutes, 0), coalesce(save_shift_template.headcount, 1), v_required, v_weekdays)
    returning * into v_row;
  else
    update "better_supabase"."shift_templates" x set
      "name" = v_name,
      "team_id" = save_shift_template.team_id,
      "location_id" = save_shift_template.location_id,
      "starts_time" = save_shift_template.starts_time,
      "ends_time" = save_shift_template.ends_time,
      "break_minutes" = coalesce(save_shift_template.break_minutes, 0),
      "headcount" = coalesce(save_shift_template.headcount, 1),
      "required_qualifications" = v_required,
      "weekdays" = v_weekdays,
      "updated_at" = now(),
      "archived_at" = null
    where x."id" = save_shift_template.template_id and x."tenant_id" = save_shift_template.tenant
    returning x.* into v_row;
    if not found then
      raise exception 'shift template not found' using errcode = 'P0002', hint = 'SHIFT_TEMPLATE_NOT_FOUND';
    end if;
  end if;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'name', v_row."name", 'teamId', v_row."team_id", 'locationId', v_row."location_id", 'startsTime', to_char(v_row."starts_time", 'HH24:MI'), 'endsTime', to_char(v_row."ends_time", 'HH24:MI'), 'breakMinutes', v_row."break_minutes", 'headcount', v_row."headcount", 'requiredQualifications', to_jsonb(v_row."required_qualifications"), 'weekdays', to_jsonb(v_row."weekdays"), 'archivedAt', v_row."archived_at", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at");
end;
$$;
revoke execute on function "better_supabase"."save_shift_template"(uuid, text, time, time, integer[], uuid, uuid, integer, integer, text[], uuid) from public, anon;
grant execute on function "better_supabase"."save_shift_template"(uuid, text, time, time, integer[], uuid, uuid, integer, integer, text[], uuid) to authenticated, service_role;

-- Archives a template: generate_shifts no longer takes it; its shifts stay.
create or replace function "better_supabase"."archive_shift_template"(template_id uuid)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_row "better_supabase"."shift_templates"%rowtype;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_row from "better_supabase"."shift_templates" x where x."id" = archive_shift_template.template_id;
  if not found then
    raise exception 'shift template not found' using errcode = 'P0002', hint = 'SHIFT_TEMPLATE_NOT_FOUND';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', v_row."tenant_id", 'shifts.write'), false)) then
    raise exception 'not allowed to plan shifts' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  update "better_supabase"."shift_templates" x set "archived_at" = coalesce(x."archived_at", now()), "updated_at" = now()
  where x."id" = v_row."id"
  returning x.* into v_row;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'name', v_row."name", 'teamId', v_row."team_id", 'locationId', v_row."location_id", 'startsTime', to_char(v_row."starts_time", 'HH24:MI'), 'endsTime', to_char(v_row."ends_time", 'HH24:MI'), 'breakMinutes', v_row."break_minutes", 'headcount', v_row."headcount", 'requiredQualifications', to_jsonb(v_row."required_qualifications"), 'weekdays', to_jsonb(v_row."weekdays"), 'archivedAt', v_row."archived_at", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at");
end;
$$;
revoke execute on function "better_supabase"."archive_shift_template"(uuid) from public, anon;
grant execute on function "better_supabase"."archive_shift_template"(uuid) to authenticated, service_role;

create or replace function "better_supabase"."list_shift_templates"(tenant uuid, include_archived boolean default false)
returns jsonb
language sql
stable
security invoker
set search_path = ''
as $$
  select coalesce(jsonb_agg(jsonb_build_object('id', x."id", 'organizationId', x."tenant_id", 'name', x."name", 'teamId', x."team_id", 'locationId', x."location_id", 'startsTime', to_char(x."starts_time", 'HH24:MI'), 'endsTime', to_char(x."ends_time", 'HH24:MI'), 'breakMinutes', x."break_minutes", 'headcount', x."headcount", 'requiredQualifications', to_jsonb(x."required_qualifications"), 'weekdays', to_jsonb(x."weekdays"), 'archivedAt', x."archived_at", 'createdAt', x."created_at", 'updatedAt', x."updated_at") order by x."name", x."id"), '[]'::jsonb)
  from "better_supabase"."shift_templates" x
  where x."tenant_id" = list_shift_templates.tenant
    and (coalesce(list_shift_templates.include_archived, false) or x."archived_at" is null)
$$;
revoke execute on function "better_supabase"."list_shift_templates"(uuid, boolean) from public, anon;
grant execute on function "better_supabase"."list_shift_templates"(uuid, boolean) to authenticated, service_role;

-- Creates draft shifts from a template on each of its weekdays from from_date
-- until_date (at most 92 days), in the block's time zone. A day that
-- already has the template's shift at the same start is skipped. Returns the
-- shifts it created.
create or replace function "better_supabase"."generate_shifts"(tenant uuid, template_id uuid, from_date date, until_date date)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_template "better_supabase"."shift_templates"%rowtype;
  v_row "better_supabase"."shifts"%rowtype;
  v_day date;
  v_starts timestamptz;
  v_ends timestamptz;
  v_created jsonb := '[]'::jsonb;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', generate_shifts.tenant, 'shifts.write'), false)) then
    raise exception 'not allowed to plan shifts' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  select * into v_template from "better_supabase"."shift_templates" x
  where x."id" = generate_shifts.template_id and x."tenant_id" = generate_shifts.tenant and x."archived_at" is null;
  if not found then
    raise exception 'shift template not found' using errcode = 'P0002', hint = 'SHIFT_TEMPLATE_NOT_FOUND';
  end if;
  if generate_shifts.from_date is null or generate_shifts.until_date is null or generate_shifts.until_date < generate_shifts.from_date then
    raise exception 'until_date is on or after from_date' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if generate_shifts.until_date - generate_shifts.from_date >= 92 then
    raise exception 'generate_shifts covers at most 92 days' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  for v_day in select d::date from generate_series(generate_shifts.from_date::timestamp, generate_shifts.until_date::timestamp, interval '1 day') d loop
    continue when not (extract(isodow from v_day)::integer = any(v_template."weekdays"));
    v_starts := (v_day + v_template."starts_time") at time zone 'UTC';
    v_ends := (v_day + v_template."ends_time" + case when v_template."ends_time" <= v_template."starts_time" then interval '1 day' else interval '0' end) at time zone 'UTC';
    continue when exists (
      select 1 from "better_supabase"."shifts" x
      where x."tenant_id" = v_template."tenant_id" and x."template_id" = v_template."id" and x."starts_at" = v_starts
    );
    insert into "better_supabase"."shifts" ("tenant_id", "template_id", "team_id", "location_id", "starts_at", "ends_at", "break_minutes", "headcount", "required_qualifications", "created_by")
    values (v_template."tenant_id", v_template."id", v_template."team_id", v_template."location_id", v_starts, v_ends, v_template."break_minutes", v_template."headcount", v_template."required_qualifications", (select auth.uid()))
    returning * into v_row;
    null;
    v_created := v_created || jsonb_build_array(jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'templateId', v_row."template_id", 'teamId', v_row."team_id", 'locationId', v_row."location_id", 'startsAt', v_row."starts_at", 'endsAt', v_row."ends_at", 'breakMinutes', v_row."break_minutes", 'headcount', v_row."headcount", 'requiredQualifications', to_jsonb(v_row."required_qualifications"), 'status', v_row."status", 'note', v_row."note", 'publishedAt', v_row."published_at", 'canceledAt', v_row."canceled_at", 'createdBy', v_row."created_by", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at"));
  end loop;
  return v_created;
end;
$$;
revoke execute on function "better_supabase"."generate_shifts"(uuid, uuid, date, date) from public, anon;
grant execute on function "better_supabase"."generate_shifts"(uuid, uuid, date, date) to authenticated, service_role;

-- Creates a draft shift, or changes one (shift_id). Changing the times moves
-- its assignments with it; a move that overlaps another shift of an assigned
-- member is refused.
drop function if exists "better_supabase"."save_shift"(uuid, timestamptz, timestamptz, uuid, uuid, integer, integer, text[], text, uuid, jsonb);
create or replace function "better_supabase"."save_shift"(tenant uuid, starts_at timestamptz, ends_at timestamptz, team_id uuid default null, location_id uuid default null, break_minutes integer default 0, headcount integer default 1, required_qualifications text[] default '{}', note text default null, shift_id uuid default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_row "better_supabase"."shifts"%rowtype;
  v_required text[] := array(select distinct k from unnest(coalesce(save_shift.required_qualifications, '{}')) k where k is not null order by k);
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', save_shift.tenant, 'shifts.write'), false)) then
    raise exception 'not allowed to plan shifts' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if save_shift.starts_at is null or save_shift.ends_at is null or save_shift.ends_at <= save_shift.starts_at then
    raise exception 'a shift ends after it starts' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if save_shift.ends_at - save_shift.starts_at > interval '24 hours' then
    raise exception 'a shift lasts at most 24 hours' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if coalesce(save_shift.break_minutes, 0) not between 0 and 720 then
    raise exception 'break_minutes is between 0 and 720' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if coalesce(save_shift.headcount, 1) not between 1 and 500 then
    raise exception 'headcount is between 1 and 500' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if save_shift.note is not null and length(save_shift.note) > 2000 then
    raise exception 'a note is at most 2000 characters' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if save_shift.team_id is not null and not exists (
    select 1 from "better_supabase"."teams" o where o."id" = save_shift.team_id and o."tenant_id" = save_shift.tenant
  ) then
    raise exception 'the team isn''t in this organization' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if save_shift.location_id is not null then
    raise exception 'location_id needs the assets block' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if cardinality(coalesce(v_required, '{}')) > 0 then
    raise exception 'required_qualifications needs the qualifications block' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  declare
    v_hook regprocedure := to_regprocedure('"public"."before_shift_save"(uuid, uuid)');
  begin
    if v_hook is not null then
      execute format('select %s($1::uuid, $2::uuid)', v_hook::oid::regproc)
        using save_shift.tenant, save_shift.shift_id;
    end if;
  end;
  if save_shift.shift_id is null then
    insert into "better_supabase"."shifts" ("tenant_id", "team_id", "location_id", "starts_at", "ends_at", "break_minutes", "headcount", "required_qualifications", "note", "created_by")
    values (save_shift.tenant, save_shift.team_id, save_shift.location_id, save_shift.starts_at, save_shift.ends_at, coalesce(save_shift.break_minutes, 0), coalesce(save_shift.headcount, 1), v_required, nullif(trim(save_shift.note), ''), (select auth.uid()))
    returning * into v_row;
    null;
  else
    select * into v_row from "better_supabase"."shifts" x
    where x."id" = save_shift.shift_id and x."tenant_id" = save_shift.tenant
    for update;
    if not found then
      raise exception 'shift not found' using errcode = 'P0002', hint = 'SHIFT_NOT_FOUND';
    end if;
    if v_row."status" = 'canceled' then
      raise exception 'the shift is canceled' using errcode = '55000', hint = 'SHIFT_CANCELED';
    end if;
    update "better_supabase"."shifts" x set
      "starts_at" = save_shift.starts_at,
      "ends_at" = save_shift.ends_at,
      "team_id" = save_shift.team_id,
      "location_id" = save_shift.location_id,
      "break_minutes" = coalesce(save_shift.break_minutes, 0),
      "headcount" = coalesce(save_shift.headcount, 1),
      "required_qualifications" = v_required,
      "note" = nullif(trim(save_shift.note), ''),
      "updated_at" = now()
    where x."id" = v_row."id"
    returning x.* into v_row;
    begin
      update "better_supabase"."shift_assignments" x set "starts_at" = v_row."starts_at", "ends_at" = v_row."ends_at", "updated_at" = now()
      where x."shift_id" = v_row."id" and (x."starts_at" <> v_row."starts_at" or x."ends_at" <> v_row."ends_at");
    exception when exclusion_violation then
      raise exception 'an assigned member has another shift at the new time' using errcode = '23P01', hint = 'SHIFT_ASSIGNMENT_OVERLAP';
    end;
  end if;
  declare
    v_hook regprocedure := to_regprocedure('"public"."after_shift_save"(uuid)');
  begin
    if v_hook is not null then
      execute format('select %s($1::uuid)', v_hook::oid::regproc)
        using v_row."id";
    end if;
  end;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'templateId', v_row."template_id", 'teamId', v_row."team_id", 'locationId', v_row."location_id", 'startsAt', v_row."starts_at", 'endsAt', v_row."ends_at", 'breakMinutes', v_row."break_minutes", 'headcount', v_row."headcount", 'requiredQualifications', to_jsonb(v_row."required_qualifications"), 'status', v_row."status", 'note', v_row."note", 'publishedAt', v_row."published_at", 'canceledAt', v_row."canceled_at", 'createdBy', v_row."created_by", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at");
end;
$$;
revoke execute on function "better_supabase"."save_shift"(uuid, timestamptz, timestamptz, uuid, uuid, integer, integer, text[], text, uuid) from public, anon;
grant execute on function "better_supabase"."save_shift"(uuid, timestamptz, timestamptz, uuid, uuid, integer, integer, text[], text, uuid) to authenticated, service_role;

-- Cancels a shift and releases its assignments. Assigned members are
-- notified. Canceling one already canceled changes nothing.
create or replace function "better_supabase"."cancel_shift"(shift_id uuid, reason text default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_row "better_supabase"."shifts"%rowtype;
  v_assignment "better_supabase"."shift_assignments"%rowtype;
  v_users jsonb := '[]'::jsonb;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_row from "better_supabase"."shifts" x where x."id" = cancel_shift.shift_id for update;
  if not found then
    raise exception 'shift not found' using errcode = 'P0002', hint = 'SHIFT_NOT_FOUND';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', v_row."tenant_id", 'shifts.manage'), false)) then
    raise exception 'not allowed to cancel shifts' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if v_row."status" <> 'canceled' then
    declare
    v_hook regprocedure := to_regprocedure('"public"."before_shift_cancel"(uuid)');
  begin
    if v_hook is not null then
      execute format('select %s($1::uuid)', v_hook::oid::regproc)
        using v_row."id";
    end if;
  end;
    v_users := coalesce((select jsonb_agg(x."user_id") from "better_supabase"."shift_assignments" x where x."shift_id" = v_row."id" and x."status" = 'assigned'), '[]'::jsonb);
    update "better_supabase"."shifts" x set "status" = 'canceled', "canceled_at" = now(), "updated_at" = now()
    where x."id" = v_row."id"
    returning x.* into v_row;
    for v_assignment in
      update "better_supabase"."shift_assignments" x set "status" = 'released', "updated_at" = now()
      where x."shift_id" = v_row."id" and x."status" <> 'released'
      returning x.*
    loop
      null;
    end loop;
    null;
    if jsonb_array_length(v_users) > 0 then
      
    end if;
    declare
    v_hook regprocedure := to_regprocedure('"public"."after_shift_cancel"(uuid)');
  begin
    if v_hook is not null then
      execute format('select %s($1::uuid)', v_hook::oid::regproc)
        using v_row."id";
    end if;
  end;
  end if;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'templateId', v_row."template_id", 'teamId', v_row."team_id", 'locationId', v_row."location_id", 'startsAt', v_row."starts_at", 'endsAt', v_row."ends_at", 'breakMinutes', v_row."break_minutes", 'headcount', v_row."headcount", 'requiredQualifications', to_jsonb(v_row."required_qualifications"), 'status', v_row."status", 'note', v_row."note", 'publishedAt', v_row."published_at", 'canceledAt', v_row."canceled_at", 'createdBy', v_row."created_by", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at");
end;
$$;
revoke execute on function "better_supabase"."cancel_shift"(uuid, text) from public, anon;
grant execute on function "better_supabase"."cancel_shift"(uuid, text) to authenticated, service_role;

-- Publishes the draft shifts that start from from_date to until_date (days in the
-- block's time zone), for one team or all. Members then see them, and the
-- assigned members are notified. Returns how many it published.
create or replace function "better_supabase"."publish_shifts"(tenant uuid, from_date date, until_date date, team_id uuid default null)
returns integer
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_row "better_supabase"."shifts"%rowtype;
  v_count integer := 0;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', publish_shifts.tenant, 'shifts.manage'), false)) then
    raise exception 'not allowed to publish shifts' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if publish_shifts.from_date is null or publish_shifts.until_date is null or publish_shifts.until_date < publish_shifts.from_date then
    raise exception 'until_date is on or after from_date' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  for v_row in
    update "better_supabase"."shifts" x set "status" = 'published', "published_at" = now(), "updated_at" = now()
    where x."tenant_id" = publish_shifts.tenant
      and x."status" = 'draft'
      and x."starts_at" >= publish_shifts.from_date::timestamp at time zone 'UTC'
      and x."starts_at" < (publish_shifts.until_date + 1)::timestamp at time zone 'UTC'
      and (publish_shifts.team_id is null or x."team_id" = publish_shifts.team_id)
    returning x.*
  loop
    v_count := v_count + 1;
    null;
    if jsonb_array_length(coalesce((select jsonb_agg(x."user_id") from "better_supabase"."shift_assignments" x where x."shift_id" = v_row."id" and x."status" = 'assigned'), '[]'::jsonb)) > 0 then
      
    end if;
  end loop;
  return v_count;
end;
$$;
revoke execute on function "better_supabase"."publish_shifts"(uuid, date, date, uuid) from public, anon;
grant execute on function "better_supabase"."publish_shifts"(uuid, date, date, uuid) to authenticated, service_role;

-- Checks that a member may work a shift: no other assigned shift overlaps,
-- no approved leave falls on its day, they hold its required qualifications
-- and they keep the minimum rest between shifts. Returns warnings (with
-- qualificationCheck "warn") and raises otherwise.
create or replace function "better_supabase"."shift_assignment_check"(shift_id uuid, user_id uuid, assignment_id uuid default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_shift "better_supabase"."shifts"%rowtype;
  v_warnings jsonb := '[]'::jsonb;
  v_key text;
begin
  select * into v_shift from "better_supabase"."shifts" x where x."id" = shift_assignment_check.shift_id;
  if not found then
    raise exception 'shift not found' using errcode = 'P0002', hint = 'SHIFT_NOT_FOUND';
  end if;
  if shift_assignment_check.user_id is null or not exists (select 1 from "better_supabase"."memberships" mm where mm."organization_id" = v_shift."tenant_id" and mm."user_id" = shift_assignment_check.user_id and mm."disabled_at" is null) then
    raise exception 'the user isn''t a member of this organization' using errcode = '22023', hint = 'SHIFT_ASSIGNMENT_INVALID';
  end if;
  if exists (
    select 1 from "better_supabase"."shift_assignments" x
    where x."tenant_id" = v_shift."tenant_id" and x."user_id" = shift_assignment_check.user_id and x."status" = 'assigned'
      and x."id" is distinct from shift_assignment_check.assignment_id
      and tstzrange(x."starts_at", x."ends_at") && tstzrange(v_shift."starts_at", v_shift."ends_at")
  ) then
    raise exception 'the member has another shift at that time' using errcode = '23P01', hint = 'SHIFT_ASSIGNMENT_OVERLAP';
  end if;
  if exists (
    select 1 from "better_supabase"."shift_assignments" x
    where x."tenant_id" = v_shift."tenant_id" and x."user_id" = shift_assignment_check.user_id and x."status" = 'assigned'
      and x."id" is distinct from shift_assignment_check.assignment_id
      and tstzrange(x."starts_at", x."ends_at") && tstzrange(v_shift."starts_at" - interval '11 hours', v_shift."ends_at" + interval '11 hours')
  ) then
    raise exception 'the member would have less than 11 hours between shifts' using errcode = '23514', hint = 'SHIFT_ASSIGNMENT_REST_TOO_SHORT';
  end if;
  return v_warnings;
end;
$$;
revoke execute on function "better_supabase"."shift_assignment_check"(uuid, uuid, uuid) from public, anon, authenticated;

-- Assigns a member to a shift. Returns the assignment with its warnings.
-- Assigning someone already on the shift changes nothing.
create or replace function "better_supabase"."assign_shift"(shift_id uuid, user_id uuid)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_shift "better_supabase"."shifts"%rowtype;
  v_row "better_supabase"."shift_assignments"%rowtype;
  v_warnings jsonb := '[]'::jsonb;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_shift from "better_supabase"."shifts" x where x."id" = assign_shift.shift_id for update;
  if not found then
    raise exception 'shift not found' using errcode = 'P0002', hint = 'SHIFT_NOT_FOUND';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', v_shift."tenant_id", 'shifts.write'), false)) then
    raise exception 'not allowed to assign shifts' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if v_shift."status" = 'canceled' then
    raise exception 'the shift is canceled' using errcode = '55000', hint = 'SHIFT_CANCELED';
  end if;
  select * into v_row from "better_supabase"."shift_assignments" x where x."shift_id" = v_shift."id" and x."user_id" = assign_shift.user_id;
  if found and v_row."status" = 'assigned' then
    return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'shiftId', v_row."shift_id", 'userId', v_row."user_id", 'status', v_row."status", 'startsAt', v_row."starts_at", 'endsAt', v_row."ends_at", 'assignedBy', v_row."assigned_by", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at") || jsonb_build_object('warnings', v_warnings);
  end if;
  if (select count(*) from "better_supabase"."shift_assignments" x where x."shift_id" = v_shift."id" and x."status" = 'assigned') >= v_shift."headcount" then
    raise exception 'the shift has no open places' using errcode = '23514', hint = 'SHIFT_FULL';
  end if;
  v_warnings := "better_supabase"."shift_assignment_check"(v_shift."id", assign_shift.user_id, v_row."id");
  declare
    v_hook regprocedure := to_regprocedure('"public"."before_shift_assignment_create"(uuid, uuid)');
  begin
    if v_hook is not null then
      execute format('select %s($1::uuid, $2::uuid)', v_hook::oid::regproc)
        using v_shift."id", assign_shift.user_id;
    end if;
  end;
  begin
    insert into "better_supabase"."shift_assignments" ("tenant_id", "shift_id", "user_id", "status", "starts_at", "ends_at", "assigned_by")
  values (v_shift."tenant_id", v_shift."id", assign_shift.user_id, 'assigned', v_shift."starts_at", v_shift."ends_at", (select auth.uid()))
  on conflict ("shift_id", "user_id") do update set "status" = 'assigned', "starts_at" = excluded."starts_at", "ends_at" = excluded."ends_at", "assigned_by" = excluded."assigned_by", "updated_at" = now()
  returning * into v_row;
  exception when exclusion_violation then
    raise exception 'the member has another shift at that time' using errcode = '23P01', hint = 'SHIFT_ASSIGNMENT_OVERLAP';
  end;
  null;
  if v_shift."status" = 'published' then
    
  end if;
  declare
    v_hook regprocedure := to_regprocedure('"public"."after_shift_assignment_create"(uuid)');
  begin
    if v_hook is not null then
      execute format('select %s($1::uuid)', v_hook::oid::regproc)
        using v_row."id";
    end if;
  end;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'shiftId', v_row."shift_id", 'userId', v_row."user_id", 'status', v_row."status", 'startsAt', v_row."starts_at", 'endsAt', v_row."ends_at", 'assignedBy', v_row."assigned_by", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at") || jsonb_build_object('warnings', v_warnings);
end;
$$;
revoke execute on function "better_supabase"."assign_shift"(uuid, uuid) from public, anon;
grant execute on function "better_supabase"."assign_shift"(uuid, uuid) to authenticated, service_role;

-- Takes a member off a shift, or turns down their request for it.
create or replace function "better_supabase"."unassign_shift"(assignment_id uuid, reason text default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_shift "better_supabase"."shifts"%rowtype;
  v_row "better_supabase"."shift_assignments"%rowtype;
  v_was text;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_row from "better_supabase"."shift_assignments" x where x."id" = unassign_shift.assignment_id for update;
  if not found then
    raise exception 'shift assignment not found' using errcode = 'P0002', hint = 'SHIFT_ASSIGNMENT_NOT_FOUND';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', v_row."tenant_id", 'shifts.write'), false)) then
    raise exception 'not allowed to assign shifts' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  select * into v_shift from "better_supabase"."shifts" x where x."id" = v_row."shift_id";
  v_was := v_row."status";
  if v_was <> 'released' then
    update "better_supabase"."shift_assignments" x set "status" = 'released', "updated_at" = now()
    where x."id" = v_row."id"
    returning x.* into v_row;
    null;
    if v_was = 'assigned' and v_shift."status" = 'published' then
      
    end if;
  end if;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'shiftId', v_row."shift_id", 'userId', v_row."user_id", 'status', v_row."status", 'startsAt', v_row."starts_at", 'endsAt', v_row."ends_at", 'assignedBy', v_row."assigned_by", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at");
end;
$$;
revoke execute on function "better_supabase"."unassign_shift"(uuid, text) from public, anon;
grant execute on function "better_supabase"."unassign_shift"(uuid, text) to authenticated, service_role;

-- Asks for a place on a published open shift that hasn't started. A planner
-- decides with decide_shift_request.
create or replace function "better_supabase"."request_open_shift"(shift_id uuid)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_shift "better_supabase"."shifts"%rowtype;
  v_row "better_supabase"."shift_assignments"%rowtype;
  v_user uuid := (select auth.uid());
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_shift from "better_supabase"."shifts" x where x."id" = request_open_shift.shift_id;
  if not found or v_shift."status" = 'draft' then
    raise exception 'shift not found' using errcode = 'P0002', hint = 'SHIFT_NOT_FOUND';
  end if;
  if v_user is null or not coalesce(better_supabase.can('tenant', v_shift."tenant_id", 'shifts.request'), false) then
    raise exception 'not allowed to request shifts' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if v_shift."status" = 'canceled' then
    raise exception 'the shift is canceled' using errcode = '55000', hint = 'SHIFT_CANCELED';
  end if;
  if v_shift."starts_at" <= now() then
    raise exception 'the shift has started' using errcode = '22023', hint = 'SHIFT_ASSIGNMENT_INVALID';
  end if;
  select * into v_row from "better_supabase"."shift_assignments" x where x."shift_id" = v_shift."id" and x."user_id" = v_user;
  if found and v_row."status" <> 'released' then
    return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'shiftId', v_row."shift_id", 'userId', v_row."user_id", 'status', v_row."status", 'startsAt', v_row."starts_at", 'endsAt', v_row."ends_at", 'assignedBy', v_row."assigned_by", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at");
  end if;
  if (select count(*) from "better_supabase"."shift_assignments" x where x."shift_id" = v_shift."id" and x."status" = 'assigned') >= v_shift."headcount" then
    raise exception 'the shift has no open places' using errcode = '23514', hint = 'SHIFT_FULL';
  end if;
  perform "better_supabase"."shift_assignment_check"(v_shift."id", v_user, v_row."id");
  insert into "better_supabase"."shift_assignments" ("tenant_id", "shift_id", "user_id", "status", "starts_at", "ends_at", "assigned_by")
  values (v_shift."tenant_id", v_shift."id", v_user, 'requested', v_shift."starts_at", v_shift."ends_at", (select auth.uid()))
  on conflict ("shift_id", "user_id") do update set "status" = 'requested', "starts_at" = excluded."starts_at", "ends_at" = excluded."ends_at", "assigned_by" = excluded."assigned_by", "updated_at" = now()
  returning * into v_row;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'shiftId', v_row."shift_id", 'userId', v_row."user_id", 'status', v_row."status", 'startsAt', v_row."starts_at", 'endsAt', v_row."ends_at", 'assignedBy', v_row."assigned_by", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at");
end;
$$;
revoke execute on function "better_supabase"."request_open_shift"(uuid) from public, anon;
grant execute on function "better_supabase"."request_open_shift"(uuid) to authenticated, service_role;

-- Approves a request for an open shift (the member is assigned after the
-- same checks as assign_shift) or turns it down.
create or replace function "better_supabase"."decide_shift_request"(assignment_id uuid, approve boolean)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_shift "better_supabase"."shifts"%rowtype;
  v_row "better_supabase"."shift_assignments"%rowtype;
  v_warnings jsonb := '[]'::jsonb;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_row from "better_supabase"."shift_assignments" x where x."id" = decide_shift_request.assignment_id;
  if not found then
    raise exception 'shift assignment not found' using errcode = 'P0002', hint = 'SHIFT_ASSIGNMENT_NOT_FOUND';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', v_row."tenant_id", 'shifts.manage'), false)) then
    raise exception 'not allowed to decide shift requests' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if v_row."status" <> 'requested' then
    raise exception 'the assignment isn''t a pending request' using errcode = '22023', hint = 'SHIFT_ASSIGNMENT_INVALID';
  end if;
  select * into v_shift from "better_supabase"."shifts" x where x."id" = v_row."shift_id" for update;
  if coalesce(decide_shift_request.approve, false) then
    if v_shift."status" = 'canceled' then
      raise exception 'the shift is canceled' using errcode = '55000', hint = 'SHIFT_CANCELED';
    end if;
    if (select count(*) from "better_supabase"."shift_assignments" x where x."shift_id" = v_shift."id" and x."status" = 'assigned') >= v_shift."headcount" then
    raise exception 'the shift has no open places' using errcode = '23514', hint = 'SHIFT_FULL';
  end if;
    v_warnings := "better_supabase"."shift_assignment_check"(v_shift."id", v_row."user_id", v_row."id");
    declare
    v_hook regprocedure := to_regprocedure('"public"."before_shift_assignment_create"(uuid, uuid)');
  begin
    if v_hook is not null then
      execute format('select %s($1::uuid, $2::uuid)', v_hook::oid::regproc)
        using v_shift."id", v_row."user_id";
    end if;
  end;
    begin
      update "better_supabase"."shift_assignments" x set "status" = 'assigned', "starts_at" = v_shift."starts_at", "ends_at" = v_shift."ends_at", "assigned_by" = (select auth.uid()), "updated_at" = now()
      where x."id" = v_row."id"
      returning x.* into v_row;
    exception when exclusion_violation then
      raise exception 'the member has another shift at that time' using errcode = '23P01', hint = 'SHIFT_ASSIGNMENT_OVERLAP';
    end;
    null;
    
    declare
    v_hook regprocedure := to_regprocedure('"public"."after_shift_assignment_create"(uuid)');
  begin
    if v_hook is not null then
      execute format('select %s($1::uuid)', v_hook::oid::regproc)
        using v_row."id";
    end if;
  end;
  else
    update "better_supabase"."shift_assignments" x set "status" = 'released', "updated_at" = now()
    where x."id" = v_row."id"
    returning x.* into v_row;
    null;
    
  end if;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'shiftId', v_row."shift_id", 'userId', v_row."user_id", 'status', v_row."status", 'startsAt', v_row."starts_at", 'endsAt', v_row."ends_at", 'assignedBy', v_row."assigned_by", 'createdAt', v_row."created_at", 'updatedAt', v_row."updated_at") || jsonb_build_object('warnings', v_warnings);
end;
$$;
revoke execute on function "better_supabase"."decide_shift_request"(uuid, boolean) from public, anon;
grant execute on function "better_supabase"."decide_shift_request"(uuid, boolean) to authenticated, service_role;

-- Offers an assigned shift that hasn't started to another member. A manager
-- decides with decide_shift_swap; the member who asked may withdraw it there.
create or replace function "better_supabase"."request_shift_swap"(assignment_id uuid, to_user_id uuid, note text default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_shift "better_supabase"."shifts"%rowtype;
  v_row "better_supabase"."shift_assignments"%rowtype;
  v_swap "better_supabase"."shift_swaps"%rowtype;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_row from "better_supabase"."shift_assignments" x where x."id" = request_shift_swap.assignment_id;
  if not found then
    raise exception 'shift assignment not found' using errcode = 'P0002', hint = 'SHIFT_ASSIGNMENT_NOT_FOUND';
  end if;
  if not (v_row."user_id" = (select auth.uid()) or (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', v_row."tenant_id", 'shifts.write'), false))) then
    raise exception 'not allowed to swap this shift' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  select * into v_shift from "better_supabase"."shifts" x where x."id" = v_row."shift_id";
  if v_row."status" <> 'assigned' or v_shift."status" = 'canceled' or v_shift."starts_at" <= now() then
    raise exception 'only an assigned shift that hasn''t started can be swapped' using errcode = '22023', hint = 'SHIFT_SWAP_INVALID';
  end if;
  if request_shift_swap.to_user_id is null or request_shift_swap.to_user_id = v_row."user_id" or not exists (select 1 from "better_supabase"."memberships" mm where mm."organization_id" = v_row."tenant_id" and mm."user_id" = request_shift_swap.to_user_id and mm."disabled_at" is null) then
    raise exception 'the swap goes to another member of this organization' using errcode = '22023', hint = 'SHIFT_SWAP_INVALID';
  end if;
  if request_shift_swap.note is not null and length(request_shift_swap.note) > 2000 then
    raise exception 'a note is at most 2000 characters' using errcode = '22023', hint = 'SHIFT_SWAP_INVALID';
  end if;
  if exists (select 1 from "better_supabase"."shift_swaps" x where x."assignment_id" = v_row."id" and x."status" = 'pending') then
    raise exception 'the shift already has a pending swap' using errcode = '22023', hint = 'SHIFT_SWAP_INVALID';
  end if;
  insert into "better_supabase"."shift_swaps" ("tenant_id", "assignment_id", "from_user_id", "to_user_id", "note")
  values (v_row."tenant_id", v_row."id", v_row."user_id", request_shift_swap.to_user_id, nullif(trim(request_shift_swap.note), ''))
  returning * into v_swap;
  null;
  
  return jsonb_build_object('id', v_swap."id", 'organizationId', v_swap."tenant_id", 'assignmentId', v_swap."assignment_id", 'fromUserId', v_swap."from_user_id", 'toUserId', v_swap."to_user_id", 'status', v_swap."status", 'approvalId', v_swap."approval_id", 'note', v_swap."note", 'decidedBy', v_swap."decided_by", 'decidedAt', v_swap."decided_at", 'createdAt', v_swap."created_at");
end;
$$;
revoke execute on function "better_supabase"."request_shift_swap"(uuid, uuid, text) from public, anon;
grant execute on function "better_supabase"."request_shift_swap"(uuid, uuid, text) to authenticated, service_role;

-- Approves a pending swap (the other member takes the shift after the same
-- checks as assign_shift) or rejects it. The member who asked may withdraw it
-- (approve false). approval_id links an approvals request.
create or replace function "better_supabase"."decide_shift_swap"(swap_id uuid, approve boolean, approval_id uuid default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_shift "better_supabase"."shifts"%rowtype;
  v_row "better_supabase"."shift_assignments"%rowtype;
  v_swap "better_supabase"."shift_swaps"%rowtype;
  v_status text;
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_swap from "better_supabase"."shift_swaps" x where x."id" = decide_shift_swap.swap_id for update;
  if not found then
    raise exception 'shift swap not found' using errcode = 'P0002', hint = 'SHIFT_SWAP_NOT_FOUND';
  end if;
  if (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', v_swap."tenant_id", 'shifts.manage'), false)) then
    v_status := case when coalesce(decide_shift_swap.approve, false) then 'approved' else 'rejected' end;
  elsif v_swap."from_user_id" = (select auth.uid()) and not coalesce(decide_shift_swap.approve, false) then
    v_status := 'canceled';
  else
    raise exception 'not allowed to decide shift swaps' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if v_swap."status" <> 'pending' then
    raise exception 'the swap is already decided' using errcode = '22023', hint = 'SHIFT_SWAP_INVALID';
  end if;
  if decide_shift_swap.approval_id is not null then
    raise exception 'approval_id needs the approvals block' using errcode = '22023', hint = 'SHIFT_SWAP_INVALID';
  end if;
  select * into v_row from "better_supabase"."shift_assignments" x where x."id" = v_swap."assignment_id";
  select * into v_shift from "better_supabase"."shifts" x where x."id" = v_row."shift_id" for update;
  if v_status = 'approved' then
    if v_row."status" <> 'assigned' or v_shift."status" = 'canceled' then
      raise exception 'the shift is no longer assigned to the member who asked' using errcode = '22023', hint = 'SHIFT_SWAP_INVALID';
    end if;
    perform "better_supabase"."shift_assignment_check"(v_shift."id", v_swap."to_user_id", null);
    update "better_supabase"."shift_assignments" x set "status" = 'released', "updated_at" = now()
    where x."id" = v_row."id"
    returning x.* into v_row;
    null;
    begin
      insert into "better_supabase"."shift_assignments" ("tenant_id", "shift_id", "user_id", "status", "starts_at", "ends_at", "assigned_by")
  values (v_shift."tenant_id", v_shift."id", v_swap."to_user_id", 'assigned', v_shift."starts_at", v_shift."ends_at", (select auth.uid()))
  on conflict ("shift_id", "user_id") do update set "status" = 'assigned', "starts_at" = excluded."starts_at", "ends_at" = excluded."ends_at", "assigned_by" = excluded."assigned_by", "updated_at" = now()
  returning * into v_row;
    exception when exclusion_violation then
      raise exception 'the member has another shift at that time' using errcode = '23P01', hint = 'SHIFT_ASSIGNMENT_OVERLAP';
    end;
    null;
  end if;
  update "better_supabase"."shift_swaps" x set "status" = v_status, "decided_at" = now(), "decided_by" = (select auth.uid())
  where x."id" = v_swap."id"
  returning x.* into v_swap;
  null;
  if v_status <> 'canceled' then
    
  end if;
  if v_status = 'approved' then
    
  end if;
  return jsonb_build_object('id', v_swap."id", 'organizationId', v_swap."tenant_id", 'assignmentId', v_swap."assignment_id", 'fromUserId', v_swap."from_user_id", 'toUserId', v_swap."to_user_id", 'status', v_swap."status", 'approvalId', v_swap."approval_id", 'note', v_swap."note", 'decidedBy', v_swap."decided_by", 'decidedAt', v_swap."decided_at", 'createdAt', v_swap."created_at");
end;
$$;
revoke execute on function "better_supabase"."decide_shift_swap"(uuid, boolean, uuid) from public, anon;
grant execute on function "better_supabase"."decide_shift_swap"(uuid, boolean, uuid) to authenticated, service_role;

-- Sets a member's availability on a day: available, unavailable or
-- preferred, for the whole day or from_time to until_time. kind null clears
-- the day. Planners (shifts.write) set it for others with user_id.
create or replace function "better_supabase"."set_shift_availability"(tenant uuid, on_date date, kind text, from_time time default null, until_time time default null, note text default null, user_id uuid default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_column
declare
  v_row "better_supabase"."shift_availability"%rowtype;
  v_user uuid := coalesce(set_shift_availability.user_id, (select auth.uid()));
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  if v_user is null then
    raise exception 'not allowed to set availability' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if v_user = (select auth.uid()) then
    if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', set_shift_availability.tenant, 'shifts.request'), false)) then
      raise exception 'not allowed to set availability' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
    end if;
  elsif not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', set_shift_availability.tenant, 'shifts.write'), false)) then
    raise exception 'not allowed to set another member''s availability' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if set_shift_availability.on_date is null then
    raise exception 'on_date is required' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if set_shift_availability.kind is null then
    delete from "better_supabase"."shift_availability" x
    where x."tenant_id" = set_shift_availability.tenant and x."user_id" = v_user and x."on_date" = set_shift_availability.on_date;
    return null;
  end if;
  if set_shift_availability.kind not in ('available', 'unavailable', 'preferred') then
    raise exception 'kind is one of available, unavailable, preferred' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if (set_shift_availability.from_time is null) <> (set_shift_availability.until_time is null)
    or set_shift_availability.until_time <= set_shift_availability.from_time then
    raise exception 'from_time and until_time are both set, with until_time after from_time, or both null' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  if set_shift_availability.note is not null and length(set_shift_availability.note) > 500 then
    raise exception 'a note is at most 500 characters' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  insert into "better_supabase"."shift_availability" ("tenant_id", "user_id", "on_date", "from_time", "until_time", "kind", "note")
  values (set_shift_availability.tenant, v_user, set_shift_availability.on_date, set_shift_availability.from_time, set_shift_availability.until_time, set_shift_availability.kind, nullif(trim(set_shift_availability.note), ''))
  on conflict ("tenant_id", "user_id", "on_date") do update set
    "from_time" = excluded."from_time",
    "until_time" = excluded."until_time",
    "kind" = excluded."kind",
    "note" = excluded."note"
  returning * into v_row;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'userId', v_row."user_id", 'onDate', v_row."on_date", 'fromTime', to_char(v_row."from_time", 'HH24:MI'), 'untilTime', to_char(v_row."until_time", 'HH24:MI'), 'kind', v_row."kind", 'note', v_row."note", 'createdAt', v_row."created_at");
end;
$$;
revoke execute on function "better_supabase"."set_shift_availability"(uuid, date, text, time, time, text, uuid) from public, anon;
grant execute on function "better_supabase"."set_shift_availability"(uuid, date, text, time, time, text, uuid) to authenticated, service_role;

-- The availability the caller may read from from_date to until_date: their own,
-- or everyone's (or one member's) with shifts.write.
create or replace function "better_supabase"."list_shift_availability"(tenant uuid, from_date date, until_date date, user_id uuid default null)
returns jsonb
language sql
stable
security invoker
set search_path = ''
as $$
  select coalesce(jsonb_agg(jsonb_build_object('id', x."id", 'organizationId', x."tenant_id", 'userId', x."user_id", 'onDate', x."on_date", 'fromTime', to_char(x."from_time", 'HH24:MI'), 'untilTime', to_char(x."until_time", 'HH24:MI'), 'kind', x."kind", 'note', x."note", 'createdAt', x."created_at") order by x."on_date", x."user_id"), '[]'::jsonb)
  from "better_supabase"."shift_availability" x
  where x."tenant_id" = list_shift_availability.tenant
    and x."on_date" between list_shift_availability.from_date and list_shift_availability.until_date
    and (list_shift_availability.user_id is null or x."user_id" = list_shift_availability.user_id)
$$;
revoke execute on function "better_supabase"."list_shift_availability"(uuid, date, date, uuid) from public, anon;
grant execute on function "better_supabase"."list_shift_availability"(uuid, date, date, uuid) to authenticated, service_role;

-- The shifts the caller may read that start from from_date to until_date (days in
-- the block's time zone), for one team or all, each with its assigned and
-- requested members. Members see published shifts; planners also drafts.
create or replace function "better_supabase"."roster"(tenant uuid, from_date date, until_date date, team_id uuid default null)
returns jsonb
language sql
stable
security invoker
set search_path = ''
as $$
  select coalesce(jsonb_agg(jsonb_build_object('id', x."id", 'organizationId', x."tenant_id", 'templateId', x."template_id", 'teamId', x."team_id", 'locationId', x."location_id", 'startsAt', x."starts_at", 'endsAt', x."ends_at", 'breakMinutes', x."break_minutes", 'headcount', x."headcount", 'requiredQualifications', to_jsonb(x."required_qualifications"), 'status', x."status", 'note', x."note", 'publishedAt', x."published_at", 'canceledAt', x."canceled_at", 'createdBy', x."created_by", 'createdAt', x."created_at", 'updatedAt', x."updated_at") || jsonb_build_object('assignments', coalesce((
    select jsonb_agg(jsonb_build_object('id', y."id", 'organizationId', y."tenant_id", 'shiftId', y."shift_id", 'userId', y."user_id", 'status', y."status", 'startsAt', y."starts_at", 'endsAt', y."ends_at", 'assignedBy', y."assigned_by", 'createdAt', y."created_at", 'updatedAt', y."updated_at") order by y."status", y."user_id")
    from "better_supabase"."shift_assignments" y
    where y."shift_id" = x."id" and y."status" <> 'released'
  ), '[]'::jsonb)) order by x."starts_at", x."id"), '[]'::jsonb)
  from "better_supabase"."shifts" x
  where x."tenant_id" = roster.tenant
    and x."starts_at" >= (roster.from_date)::timestamp at time zone 'UTC'
    and x."starts_at" < (roster.until_date + 1)::timestamp at time zone 'UTC'
    and (roster.team_id is null or x."team_id" = roster.team_id)
$$;
revoke execute on function "better_supabase"."roster"(uuid, date, date, uuid) from public, anon;
grant execute on function "better_supabase"."roster"(uuid, date, date, uuid) to authenticated, service_role;

-- The caller's shifts (assigned or requested) that start from since to until,
-- in every organization, each with its assignment.
create or replace function "better_supabase"."my_shifts"(since timestamptz default now(), until timestamptz default now() + interval '30 days')
returns jsonb
language sql
stable
security invoker
set search_path = ''
as $$
  select coalesce(jsonb_agg(jsonb_build_object('id', x."id", 'organizationId', x."tenant_id", 'templateId', x."template_id", 'teamId', x."team_id", 'locationId', x."location_id", 'startsAt', x."starts_at", 'endsAt', x."ends_at", 'breakMinutes', x."break_minutes", 'headcount', x."headcount", 'requiredQualifications', to_jsonb(x."required_qualifications"), 'status', x."status", 'note', x."note", 'publishedAt', x."published_at", 'canceledAt', x."canceled_at", 'createdBy', x."created_by", 'createdAt', x."created_at", 'updatedAt', x."updated_at") || jsonb_build_object('assignment', jsonb_build_object('id', y."id", 'organizationId', y."tenant_id", 'shiftId', y."shift_id", 'userId', y."user_id", 'status', y."status", 'startsAt', y."starts_at", 'endsAt', y."ends_at", 'assignedBy', y."assigned_by", 'createdAt', y."created_at", 'updatedAt', y."updated_at")) order by x."starts_at"), '[]'::jsonb)
  from "better_supabase"."shift_assignments" y
  join "better_supabase"."shifts" x on x."id" = y."shift_id"
  where y."user_id" = (select auth.uid())
    and y."status" in ('assigned', 'requested')
    and x."status" <> 'draft'
    and x."starts_at" >= coalesce(my_shifts.since, now())
    and x."starts_at" < coalesce(my_shifts.until, now() + interval '30 days')
$$;
revoke execute on function "better_supabase"."my_shifts"(timestamptz, timestamptz) from public, anon;
grant execute on function "better_supabase"."my_shifts"(timestamptz, timestamptz) to authenticated, service_role;

-- The hours each member is planned for in the week from week_start (shifts
-- not canceled, breaks taken off), next to their contract hours when the
-- employees block keeps contracts.
create or replace function "better_supabase"."shift_hours"(tenant uuid, week_start date)
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
#variable_conflict use_column
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or coalesce(better_supabase.can('tenant', shift_hours.tenant, 'shifts.manage'), false)) then
    raise exception 'not allowed to read planned hours' using errcode = '42501', hint = 'SHIFT_FORBIDDEN';
  end if;
  if shift_hours.week_start is null then
    raise exception 'week_start is required' using errcode = '22023', hint = 'SHIFT_INVALID';
  end if;
  return (
    select coalesce(jsonb_agg(jsonb_build_object(
      'userId', u.user_id,
      'shifts', u.assigned,
      'plannedHours', u.planned,
      'contractHours', u.contract,
      'differenceHours', u.planned - u.contract
    ) order by u.planned desc, u.user_id), '[]'::jsonb)
    from (
      select p.user_id, p.assigned, p.planned, null::numeric as contract
      from (
        select y."user_id" as user_id, count(*)::integer as assigned, round((extract(epoch from sum(x."ends_at" - make_interval(mins => x."break_minutes") - x."starts_at")) / 3600)::numeric, 2) as planned
        from "better_supabase"."shift_assignments" y
        join "better_supabase"."shifts" x on x."id" = y."shift_id"
        where y."tenant_id" = shift_hours.tenant
          and y."status" = 'assigned'
          and x."status" <> 'canceled'
          and x."starts_at" >= (shift_hours.week_start)::timestamp at time zone 'UTC'
          and x."starts_at" < (shift_hours.week_start + 7)::timestamp at time zone 'UTC'
        group by y."user_id"
      ) p
    ) u
  );
end;
$$;
revoke execute on function "better_supabase"."shift_hours"(uuid, date) from public, anon;
grant execute on function "better_supabase"."shift_hours"(uuid, date) to authenticated, service_role;

-- Tenant isolation (ADR 0099): permissive policies combine with or, so one
-- the app adds would widen the block's. These restrictive policies keep each
-- table to the caller's organizations and what the block's policies grant.
-- Until memberships installs, the caller is a member of no organization.
do $$
begin
  if to_regprocedure('better_supabase.member_organization_ids(text[])') is null then
    create function better_supabase.member_organization_ids(roles text[] default null)
    returns setof uuid
    language sql
    stable
    set search_path = ''
    as $members$ select null::uuid where false $members$;
  end if;
end $$;
drop policy if exists bsb_tenant_scope on "better_supabase"."shift_templates";
drop policy if exists bsb_tenant_scope_select on "better_supabase"."shift_templates";
create policy bsb_tenant_scope_select on "better_supabase"."shift_templates" as restrictive
  for select to public
  using (
    "tenant_id" in (select better_supabase.member_organization_ids())
    or ("tenant_id" in (select better_supabase.tenant_ids_with('shifts.read')))
  );
drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shift_templates";
create policy bsb_tenant_scope_insert on "better_supabase"."shift_templates" as restrictive
  for insert to public
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_update on "better_supabase"."shift_templates";
create policy bsb_tenant_scope_update on "better_supabase"."shift_templates" as restrictive
  for update to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()))
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shift_templates";
create policy bsb_tenant_scope_delete on "better_supabase"."shift_templates" as restrictive
  for delete to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope on "better_supabase"."shifts";
drop policy if exists bsb_tenant_scope_select on "better_supabase"."shifts";
create policy bsb_tenant_scope_select on "better_supabase"."shifts" as restrictive
  for select to public
  using (
    "tenant_id" in (select better_supabase.member_organization_ids())
    or ("tenant_id" in (select better_supabase.tenant_ids_with('shifts.write')) or ("status" <> 'draft' and "tenant_id" in (select better_supabase.tenant_ids_with('shifts.read'))))
  );
drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shifts";
create policy bsb_tenant_scope_insert on "better_supabase"."shifts" as restrictive
  for insert to public
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_update on "better_supabase"."shifts";
create policy bsb_tenant_scope_update on "better_supabase"."shifts" as restrictive
  for update to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()))
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shifts";
create policy bsb_tenant_scope_delete on "better_supabase"."shifts" as restrictive
  for delete to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope on "better_supabase"."shift_assignments";
drop policy if exists bsb_tenant_scope_select on "better_supabase"."shift_assignments";
create policy bsb_tenant_scope_select on "better_supabase"."shift_assignments" as restrictive
  for select to public
  using (
    "tenant_id" in (select better_supabase.member_organization_ids())
    or ("user_id" = (select auth.uid()) or "tenant_id" in (select better_supabase.tenant_ids_with('shifts.read')))
  );
drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shift_assignments";
create policy bsb_tenant_scope_insert on "better_supabase"."shift_assignments" as restrictive
  for insert to public
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_update on "better_supabase"."shift_assignments";
create policy bsb_tenant_scope_update on "better_supabase"."shift_assignments" as restrictive
  for update to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()))
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shift_assignments";
create policy bsb_tenant_scope_delete on "better_supabase"."shift_assignments" as restrictive
  for delete to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope on "better_supabase"."shift_swaps";
drop policy if exists bsb_tenant_scope_select on "better_supabase"."shift_swaps";
create policy bsb_tenant_scope_select on "better_supabase"."shift_swaps" as restrictive
  for select to public
  using (
    "tenant_id" in (select better_supabase.member_organization_ids())
    or ("from_user_id" = (select auth.uid()) or "to_user_id" = (select auth.uid()) or "tenant_id" in (select better_supabase.tenant_ids_with('shifts.manage')))
  );
drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shift_swaps";
create policy bsb_tenant_scope_insert on "better_supabase"."shift_swaps" as restrictive
  for insert to public
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_update on "better_supabase"."shift_swaps";
create policy bsb_tenant_scope_update on "better_supabase"."shift_swaps" as restrictive
  for update to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()))
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shift_swaps";
create policy bsb_tenant_scope_delete on "better_supabase"."shift_swaps" as restrictive
  for delete to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope on "better_supabase"."shift_availability";
drop policy if exists bsb_tenant_scope_select on "better_supabase"."shift_availability";
create policy bsb_tenant_scope_select on "better_supabase"."shift_availability" as restrictive
  for select to public
  using (
    "tenant_id" in (select better_supabase.member_organization_ids())
    or ("user_id" = (select auth.uid()) or "tenant_id" in (select better_supabase.tenant_ids_with('shifts.write')))
  );
drop policy if exists bsb_tenant_scope_insert on "better_supabase"."shift_availability";
create policy bsb_tenant_scope_insert on "better_supabase"."shift_availability" as restrictive
  for insert to public
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_update on "better_supabase"."shift_availability";
create policy bsb_tenant_scope_update on "better_supabase"."shift_availability" as restrictive
  for update to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()))
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_delete on "better_supabase"."shift_availability";
create policy bsb_tenant_scope_delete on "better_supabase"."shift_availability" as restrictive
  for delete to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()));

create schema if not exists better_supabase;
create table if not exists better_supabase.blocks (
  name text primary key,
  block text,
  version integer not null,
  mode text not null,
  installed_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
alter table better_supabase.blocks enable row level security;
revoke all on better_supabase.blocks from public, anon, authenticated;
grant select on better_supabase.blocks to service_role;