Dungeons-Ground/supabase/bootstrap.sql

-- GENERATED FILE. Rebuild with: pnpm db:bundle
-- Paste this entire file into a new Supabase SQL Editor query and click Run.
-- It is intended for a fresh Dungeons & Ground project. The explicit
-- transaction ensures a failure cannot leave a half-created schema.

begin;

do $$
begin
  if to_regclass('auth.users') is null then
    raise exception 'D&G bootstrap preflight failed: auth.users is missing';
  end if;
  if not exists (
    select 1
    from information_schema.columns
    where table_schema = 'auth'
      and table_name = 'users'
      and column_name = 'is_anonymous'
  ) then
    raise exception 'D&G bootstrap preflight failed: auth.users.is_anonymous is missing';
  end if;
  if to_regclass('public.profiles') is not null
    or to_regclass('public.ai_jobs') is not null
    or to_regtype('public.member_role') is not null
    or to_regprocedure('public.claim_ai_job(text)') is not null then
    raise exception 'D&G bootstrap preflight failed: a partial/existing D&G schema was found. Do not run the fresh bootstrap over it.';
  end if;
end;
$$;


-- ============================================================================
-- 0001_alpha_schema.sql
-- ============================================================================

create extension if not exists pgcrypto;

create type public.member_role as enum ('owner', 'player');
create type public.character_controller as enum ('human', 'ai', 'delegated');
create type public.round_status as enum ('open', 'queued', 'resolving', 'resolved', 'failed');

create table public.allowlist (
  email text primary key check (email <> '' and email = lower(btrim(email))),
  invited_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now()
);

create table public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  display_name text not null default 'Adventurer' check (char_length(display_name) between 1 and 80),
  created_at timestamptz not null default now()
);

create table public.worlds (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references public.profiles(id),
  title text not null check (char_length(title) between 3 and 100),
  genre text not null,
  tone text not null,
  premise text not null,
  content_boundaries jsonb not null default '[]' check (jsonb_typeof(content_boundaries) = 'array'),
  hidden_threat text not null,
  status text not null default 'draft' check (status in ('draft', 'confirmed')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.world_entities (
  id uuid primary key default gen_random_uuid(),
  world_id uuid not null references public.worlds(id) on delete cascade,
  kind text not null check (kind in ('location', 'npc', 'faction', 'quest')),
  name text not null,
  summary text not null,
  tags text[] not null default '{}',
  secrets jsonb not null default '[]' check (jsonb_typeof(secrets) = 'array'),
  -- Generated expressions may only call IMMUTABLE functions. PostgreSQL marks
  -- array_to_string as STABLE, so tags stay queryable through their text[]
  -- column while the FTS document indexes the entity's searchable prose.
  search_document tsvector generated always as (to_tsvector('english'::regconfig, coalesce(name, '') || ' ' || coalesce(summary, ''))) stored,
  created_at timestamptz not null default now()
);
create index world_entities_search_idx on public.world_entities using gin(search_document);

create table public.coauthor_sessions (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references public.profiles(id),
  status text not null default 'collecting' check (status in ('collecting', 'generating', 'ready', 'failed', 'confirmed')),
  messages jsonb not null default '[]' check (jsonb_typeof(messages) = 'array'),
  generated_world jsonb check (generated_world is null or jsonb_typeof(generated_world) = 'object'),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.campaigns (
  id uuid primary key default gen_random_uuid(),
  world_id uuid not null references public.worlds(id),
  owner_id uuid not null references public.profiles(id),
  title text not null,
  current_scene text not null,
  next_prompt text not null default 'What do you do?',
  status text not null default 'lobby' check (status in ('lobby', 'active', 'archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.campaign_members (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  user_id uuid not null references public.profiles(id) on delete cascade,
  role public.member_role not null default 'player',
  active boolean not null default true,
  ai_takeover_allowed boolean not null default false,
  joined_at timestamptz not null default now(),
  unique (campaign_id, user_id)
);

create table public.invites (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  created_by uuid not null references public.profiles(id),
  token_hash text not null unique,
  max_uses integer not null default 1 check (max_uses between 1 and 20),
  uses integer not null default 0 check (uses >= 0 and uses <= max_uses),
  expires_at timestamptz not null,
  created_at timestamptz not null default now()
);

create table public.characters (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  user_id uuid references public.profiles(id) on delete set null,
  name text not null,
  concept text not null,
  controller public.character_controller not null,
  abilities jsonb not null check (jsonb_typeof(abilities) = 'object'),
  hp integer not null check (hp >= 0),
  max_hp integer not null check (max_hp > 0),
  defense integer not null check (defense between 1 and 40),
  proficiency integer not null default 2 check (proficiency between 1 and 10),
  inventory jsonb not null default '[]' check (jsonb_typeof(inventory) = 'array'),
  statuses jsonb not null default '[]' check (jsonb_typeof(statuses) = 'array'),
  persona jsonb not null default '{}' check (jsonb_typeof(persona) = 'object'),
  created_at timestamptz not null default now()
);
alter table public.characters add constraint characters_hp_within_max check (hp <= max_hp);

create table public.rounds (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  number integer not null check (number > 0),
  status public.round_status not null default 'open',
  forced_by uuid references public.profiles(id),
  narration text,
  next_prompt text,
  queued_at timestamptz,
  resolved_at timestamptz,
  error text,
  created_at timestamptz not null default now(),
  unique (campaign_id, number)
);

create table public.player_intents (
  id uuid primary key default gen_random_uuid(),
  round_id uuid not null references public.rounds(id) on delete cascade,
  member_id uuid not null references public.campaign_members(id) on delete cascade,
  character_id uuid not null references public.characters(id),
  action text not null check (char_length(action) between 1 and 2000),
  ready boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (round_id, member_id)
);

create table public.game_events (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  round_id uuid references public.rounds(id),
  event_type text not null,
  payload jsonb not null,
  created_at timestamptz not null default now()
);

create table public.dice_rolls (
  id uuid primary key,
  round_id uuid not null references public.rounds(id) on delete cascade,
  actor_id uuid not null references public.characters(id),
  target_id uuid references public.characters(id),
  check_kind text not null,
  formula text not null,
  rolls integer[] not null,
  kept integer[] not null,
  modifier integer not null,
  total integer not null,
  difficulty integer,
  success boolean,
  created_at timestamptz not null
);

create table public.relationships (
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  source_entity_id uuid not null,
  target_entity_id uuid not null,
  score integer not null default 0 check (score between -100 and 100),
  notes text not null default '',
  primary key (campaign_id, source_entity_id, target_entity_id)
);

create table public.memories (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  round_id uuid references public.rounds(id),
  summary text not null,
  importance integer not null check (importance between 1 and 5),
  tags text[] not null default '{}',
  entity_ids uuid[] not null default '{}',
  search_document tsvector generated always as (to_tsvector('english'::regconfig, coalesce(summary, ''))) stored,
  created_at timestamptz not null default now()
);
create index memories_search_idx on public.memories using gin(search_document);

create table public.story_summaries (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  through_round integer not null,
  summary text not null,
  created_at timestamptz not null default now(),
  unique (campaign_id, through_round)
);

create table public.ai_jobs (
  id uuid primary key default gen_random_uuid(),
  job_type text not null,
  entity_id uuid not null,
  idempotency_key text not null unique,
  status text not null default 'queued' check (status in ('queued', 'running', 'complete', 'failed')),
  attempts integer not null default 0 check (attempts >= 0),
  claimed_by text,
  lease_expires_at timestamptz,
  available_at timestamptz not null default now(),
  max_attempts integer not null default 3 check (max_attempts between 1 and 10),
  error text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (
    (status = 'running' and claimed_by is not null and lease_expires_at is not null)
    or (status <> 'running' and claimed_by is null and lease_expires_at is null)
  )
);
create unique index ai_jobs_round_resolution_unique
on public.ai_jobs(entity_id) where job_type = 'resolve-round';
create index ai_jobs_poll_idx
on public.ai_jobs(available_at, created_at) where status in ('queued', 'running');
create index ai_jobs_expired_lease_idx
on public.ai_jobs(lease_expires_at) where status = 'running';

create table public.ai_usage (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id),
  campaign_id uuid references public.campaigns(id),
  job_id uuid references public.ai_jobs(id),
  model text not null,
  input_tokens integer not null default 0 check (input_tokens >= 0),
  output_tokens integer not null default 0 check (output_tokens >= 0),
  cost_usd numeric(12, 6) not null default 0 check (cost_usd >= 0),
  latency_ms integer check (latency_ms is null or latency_ms >= 0),
  created_at timestamptz not null default now()
);

create table public.audit_entries (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  actor_id uuid references public.profiles(id),
  action text not null,
  entity_type text not null,
  entity_id uuid not null,
  before_state jsonb,
  after_state jsonb,
  created_at timestamptz not null default now()
);

create index campaigns_owner_idx on public.campaigns(owner_id);
create index campaign_members_user_idx on public.campaign_members(user_id, campaign_id) where active;
create index characters_campaign_idx on public.characters(campaign_id);
create index rounds_campaign_status_idx on public.rounds(campaign_id, status);
create index player_intents_round_ready_idx on public.player_intents(round_id, ready);
create index game_events_campaign_round_idx on public.game_events(campaign_id, round_id);
create index memories_campaign_importance_idx on public.memories(campaign_id, importance desc);

create or replace function public.create_profile_for_allowlisted_user()
returns trigger language plpgsql security definer set search_path = '' as $$
begin
  if new.email is null or not exists (
    select 1 from public.allowlist where email = lower(btrim(new.email))
  ) then
    raise exception using
      errcode = '42501',
      message = 'This email is not invited to the Dungeons & Ground alpha.';
  end if;

  insert into public.profiles(id) values (new.id);
  return new;
end;
$$;

revoke all on function public.create_profile_for_allowlisted_user() from public;

create trigger create_profile_for_allowlisted_user
after insert on auth.users
for each row execute function public.create_profile_for_allowlisted_user();

create or replace function public.is_campaign_member(p_campaign_id uuid)
returns boolean language sql stable security definer set search_path = '' as $$
  select exists(
    select 1
    from public.campaign_members
    where campaign_id = p_campaign_id
      and user_id = auth.uid()
      and active
  );
$$;

create or replace function public.is_campaign_owner(p_campaign_id uuid)
returns boolean language sql stable security definer set search_path = '' as $$
  select exists(
    select 1
    from public.campaigns
    where id = p_campaign_id and owner_id = auth.uid()
  );
$$;

revoke all on function public.is_campaign_member(uuid) from public;
revoke all on function public.is_campaign_owner(uuid) from public;
grant execute on function public.is_campaign_member(uuid) to authenticated, service_role;
grant execute on function public.is_campaign_owner(uuid) to authenticated, service_role;

create or replace function public.validate_player_intent_membership()
returns trigger language plpgsql security definer set search_path = '' as $$
declare
  v_round_campaign_id uuid;
  v_member_campaign_id uuid;
  v_member_user_id uuid;
  v_member_active boolean;
  v_character_campaign_id uuid;
  v_character_user_id uuid;
begin
  select campaign_id into v_round_campaign_id
  from public.rounds where id = new.round_id;
  select campaign_id, user_id, active
  into v_member_campaign_id, v_member_user_id, v_member_active
  from public.campaign_members where id = new.member_id;
  select campaign_id, user_id
  into v_character_campaign_id, v_character_user_id
  from public.characters where id = new.character_id;

  if v_round_campaign_id is null
    or v_member_campaign_id is distinct from v_round_campaign_id
    or v_character_campaign_id is distinct from v_round_campaign_id
    or not coalesce(v_member_active, false)
    or v_character_user_id is distinct from v_member_user_id then
    raise exception 'intent member and character must belong to the round campaign';
  end if;
  return new;
end;
$$;

revoke all on function public.validate_player_intent_membership() from public;

create trigger validate_player_intent_membership
before insert or update of round_id, member_id, character_id on public.player_intents
for each row execute function public.validate_player_intent_membership();

alter table public.allowlist enable row level security;
alter table public.profiles enable row level security;
alter table public.worlds enable row level security;
alter table public.world_entities enable row level security;
alter table public.coauthor_sessions enable row level security;
alter table public.campaigns enable row level security;
alter table public.campaign_members enable row level security;
alter table public.invites enable row level security;
alter table public.characters enable row level security;
alter table public.rounds enable row level security;
alter table public.player_intents enable row level security;
alter table public.game_events enable row level security;
alter table public.dice_rolls enable row level security;
alter table public.relationships enable row level security;
alter table public.memories enable row level security;
alter table public.story_summaries enable row level security;
alter table public.ai_jobs enable row level security;
alter table public.ai_usage enable row level security;
alter table public.audit_entries enable row level security;

create policy profiles_self on public.profiles for select using (id = auth.uid());
create policy profiles_self_update on public.profiles for update using (id = auth.uid()) with check (id = auth.uid());
create policy worlds_owner_all on public.worlds for all using (owner_id = auth.uid()) with check (owner_id = auth.uid());
create policy entities_owner_all on public.world_entities for all using (exists(select 1 from worlds where worlds.id = world_id and worlds.owner_id = auth.uid()));
create policy coauthor_owner_all on public.coauthor_sessions for all using (owner_id = auth.uid()) with check (owner_id = auth.uid());
create policy campaigns_members_read on public.campaigns for select using (is_campaign_member(id) or owner_id = auth.uid());
create policy campaigns_owner_write on public.campaigns for all using (owner_id = auth.uid()) with check (
  owner_id = auth.uid()
  and exists (
    select 1 from public.worlds
    where worlds.id = world_id and worlds.owner_id = auth.uid() and worlds.status = 'confirmed'
  )
);
create policy members_member_read on public.campaign_members for select using (is_campaign_member(campaign_id) or is_campaign_owner(campaign_id));
create policy members_owner_insert on public.campaign_members for insert with check (
  is_campaign_owner(campaign_id)
  and (
    (user_id = auth.uid() and role = 'owner')
    or (user_id <> auth.uid() and role = 'player')
  )
);
create policy members_owner_update on public.campaign_members for update using (is_campaign_owner(campaign_id)) with check (
  is_campaign_owner(campaign_id)
  and (
    (user_id = auth.uid() and role = 'owner')
    or (user_id <> auth.uid() and role = 'player')
  )
);
create policy members_owner_delete on public.campaign_members for delete using (is_campaign_owner(campaign_id) and user_id <> auth.uid());
create policy characters_member_read on public.characters for select using (is_campaign_member(campaign_id) or is_campaign_owner(campaign_id));
create policy characters_member_insert on public.characters for insert with check (
  (is_campaign_member(campaign_id) and user_id = auth.uid() and controller = 'human')
  or (is_campaign_owner(campaign_id) and (user_id = auth.uid() or (user_id is null and controller = 'ai')))
);
create policy rounds_member_read on public.rounds for select using (is_campaign_member(campaign_id) or is_campaign_owner(campaign_id));
create policy intents_member_read on public.player_intents for select using (
  exists(
    select 1 from public.rounds
    where rounds.id = round_id
      and (is_campaign_member(rounds.campaign_id) or is_campaign_owner(rounds.campaign_id))
  )
);
create policy intents_self_insert on public.player_intents for insert with check (
  exists(
    select 1
    from public.rounds
    join public.campaign_members on campaign_members.campaign_id = rounds.campaign_id
    join public.characters on characters.campaign_id = rounds.campaign_id
    where rounds.id = round_id
      and rounds.status = 'open'
      and campaign_members.id = member_id
      and campaign_members.user_id = auth.uid()
      and campaign_members.active
      and characters.id = character_id
      and characters.user_id = auth.uid()
      and characters.controller = 'human'
  )
);
create policy intents_self_update on public.player_intents for update using (
  exists(
    select 1 from public.campaign_members
    where campaign_members.id = member_id
      and campaign_members.user_id = auth.uid()
      and campaign_members.active
  )
) with check (
  exists(
    select 1
    from public.rounds
    join public.campaign_members on campaign_members.campaign_id = rounds.campaign_id
    join public.characters on characters.campaign_id = rounds.campaign_id
    where rounds.id = round_id
      and rounds.status = 'open'
      and campaign_members.id = member_id
      and campaign_members.user_id = auth.uid()
      and campaign_members.active
      and characters.id = character_id
      and characters.user_id = auth.uid()
      and characters.controller = 'human'
  )
);
create policy events_member_read on public.game_events for select using (is_campaign_member(campaign_id) or is_campaign_owner(campaign_id));
create policy rolls_member_read on public.dice_rolls for select using (exists(select 1 from public.rounds where rounds.id = round_id and (is_campaign_member(rounds.campaign_id) or is_campaign_owner(rounds.campaign_id))));
create policy relationships_member_read on public.relationships for select using (is_campaign_member(campaign_id) or is_campaign_owner(campaign_id));
create policy memories_member_read on public.memories for select using (is_campaign_member(campaign_id) or is_campaign_owner(campaign_id));
create policy summaries_member_read on public.story_summaries for select using (is_campaign_member(campaign_id) or is_campaign_owner(campaign_id));
create policy usage_self_read on public.ai_usage for select using (user_id = auth.uid());
create policy audit_owner_read on public.audit_entries for select using (is_campaign_owner(campaign_id));

create or replace function public.enqueue_round_resolution(
  p_round_id uuid,
  p_forced_by uuid default null
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_job_id uuid;
begin
  select * into v_round
  from public.rounds
  where id = p_round_id
  for update;
  if not found then raise exception 'round not found'; end if;

  select id into v_job_id
  from public.ai_jobs
  where job_type = 'resolve-round' and entity_id = p_round_id;

  if v_round.status in ('queued', 'resolving', 'resolved') then
    if v_job_id is null and v_round.status <> 'resolved' then
      raise exception 'round status and job outbox are inconsistent';
    end if;
    return v_job_id;
  end if;
  if v_round.status <> 'open' then
    raise exception 'round cannot be queued from status %', v_round.status;
  end if;

  if p_forced_by is not null then
    if not exists (
      select 1 from public.campaigns
      where id = v_round.campaign_id and owner_id = p_forced_by
    ) then
      raise exception 'only the campaign owner can force a round';
    end if;
  elsif exists (
    select 1
    from public.campaign_members member
    where member.campaign_id = v_round.campaign_id
      and member.active
      and not exists (
        select 1 from public.player_intents intent
        where intent.round_id = p_round_id
          and intent.member_id = member.id
          and intent.ready
      )
  ) then
    raise exception 'not all active players are ready';
  end if;

  v_job_id := gen_random_uuid();
  insert into public.ai_jobs(id, job_type, entity_id, idempotency_key, status)
  values (v_job_id, 'resolve-round', p_round_id, v_job_id::text, 'queued');

  update public.rounds
  set status = 'queued', queued_at = now(), forced_by = p_forced_by, error = null
  where id = p_round_id;
  return v_job_id;
end;
$$;

revoke all on function public.enqueue_round_resolution(uuid, uuid) from public, anon, authenticated;
grant execute on function public.enqueue_round_resolution(uuid, uuid) to service_role;

create or replace function public.claim_ai_job(
  p_worker_id text
) returns table(id uuid, job_type text, entity_id uuid)
language plpgsql security definer set search_path = '' as $$
declare
  v_job public.ai_jobs%rowtype;
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;

  select * into v_job
  from public.ai_jobs
  where attempts < max_attempts
    and available_at <= now()
    and (
      status = 'queued'
      or (status = 'running' and lease_expires_at <= now())
    )
  order by created_at
  for update skip locked
  limit 1;

  if not found then return; end if;

  update public.ai_jobs
  set status = 'running',
      attempts = attempts + 1,
      claimed_by = p_worker_id,
      lease_expires_at = now() + interval '3 minutes',
      error = null,
      updated_at = now()
  where public.ai_jobs.id = v_job.id;

  if v_job.job_type = 'resolve-round' then
    update public.rounds set status = 'resolving', error = null where public.rounds.id = v_job.entity_id;
  elsif v_job.job_type = 'generate-world' then
    update public.coauthor_sessions set status = 'generating', updated_at = now() where public.coauthor_sessions.id = v_job.entity_id;
  end if;

  return query select v_job.id, v_job.job_type, v_job.entity_id;
end;
$$;

create or replace function public.claim_ai_job_by_id(
  p_job_id uuid,
  p_worker_id text
) returns table(id uuid, job_type text, entity_id uuid)
language plpgsql security definer set search_path = '' as $$
declare
  v_job public.ai_jobs%rowtype;
begin
  if p_job_id is null then
    raise exception 'job id is required';
  end if;
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;

  select * into v_job
  from public.ai_jobs
  where public.ai_jobs.id = p_job_id
    and attempts < max_attempts
    and available_at <= now()
    and (
      status = 'queued'
      or (status = 'running' and lease_expires_at <= now())
    )
  for update;

  if not found then return; end if;

  update public.ai_jobs
  set status = 'running',
      attempts = attempts + 1,
      claimed_by = p_worker_id,
      lease_expires_at = now() + interval '3 minutes',
      error = null,
      updated_at = now()
  where public.ai_jobs.id = v_job.id;

  if v_job.job_type = 'resolve-round' then
    update public.rounds set status = 'resolving', error = null where public.rounds.id = v_job.entity_id;
  elsif v_job.job_type = 'generate-world' then
    update public.coauthor_sessions set status = 'generating', updated_at = now() where public.coauthor_sessions.id = v_job.entity_id;
  end if;

  return query select v_job.id, v_job.job_type, v_job.entity_id;
end;
$$;

create or replace function public.complete_ai_job(
  p_job_id uuid,
  p_worker_id text
) returns void language plpgsql security definer set search_path = '' as $$
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;
  update public.ai_jobs
  set status = 'complete', claimed_by = null, lease_expires_at = null, updated_at = now()
  where id = p_job_id
    and status = 'running'
    and claimed_by = p_worker_id
    and lease_expires_at > now();
  if not found then raise exception 'job is not owned by this worker'; end if;
end;
$$;

create or replace function public.retry_ai_job(
  p_job_id uuid,
  p_worker_id text,
  p_error text
) returns void language plpgsql security definer set search_path = '' as $$
declare
  v_job public.ai_jobs%rowtype;
  v_retry boolean;
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;
  select * into v_job from public.ai_jobs where id = p_job_id for update;
  if not found or v_job.status <> 'running' or v_job.claimed_by <> p_worker_id then
    raise exception 'job is not owned by this worker';
  end if;
  if v_job.lease_expires_at <= now() then
    raise exception 'job lease has expired';
  end if;
  v_retry := v_job.attempts < v_job.max_attempts;
  update public.ai_jobs
  set status = case when v_retry then 'queued' else 'failed' end,
      claimed_by = null,
      lease_expires_at = null,
      available_at = case when v_retry then now() + make_interval(secs => 5 * power(2, v_job.attempts - 1)::integer) else available_at end,
      error = left(p_error, 2000),
      updated_at = now()
  where id = p_job_id;

  if v_job.job_type = 'resolve-round' then
    update public.rounds
    set status = case when v_retry then 'queued'::public.round_status else 'failed'::public.round_status end,
        error = left(p_error, 2000)
    where id = v_job.entity_id and status <> 'resolved';
  elsif v_job.job_type = 'generate-world' then
    update public.coauthor_sessions
    set status = case when v_retry then 'generating' else 'failed' end, updated_at = now()
    where id = v_job.entity_id and status <> 'ready';
  end if;
end;
$$;

create or replace function public.renew_ai_job_lease(
  p_job_id uuid,
  p_worker_id text
) returns timestamptz language plpgsql security definer set search_path = '' as $$
declare
  v_lease_expires_at timestamptz;
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;
  update public.ai_jobs
  set lease_expires_at = now() + interval '3 minutes', updated_at = now()
  where id = p_job_id
    and status = 'running'
    and claimed_by = p_worker_id
    and lease_expires_at > now()
  returning lease_expires_at into v_lease_expires_at;
  if not found then raise exception 'job is not owned by this worker or lease expired'; end if;
  return v_lease_expires_at;
end;
$$;

revoke all on function public.claim_ai_job(text) from public, anon, authenticated;
revoke all on function public.claim_ai_job_by_id(uuid, text) from public, anon, authenticated;
revoke all on function public.complete_ai_job(uuid, text) from public, anon, authenticated;
revoke all on function public.retry_ai_job(uuid, text, text) from public, anon, authenticated;
revoke all on function public.renew_ai_job_lease(uuid, text) from public, anon, authenticated;
grant execute on function public.claim_ai_job(text) to service_role;
grant execute on function public.claim_ai_job_by_id(uuid, text) to service_role;
grant execute on function public.complete_ai_job(uuid, text) to service_role;
grant execute on function public.retry_ai_job(uuid, text, text) to service_role;
grant execute on function public.renew_ai_job_lease(uuid, text) to service_role;

create or replace function public.commit_round_resolution(
  p_round_id uuid,
  p_narration text,
  p_next_prompt text,
  p_rolls jsonb,
  p_events jsonb,
  p_character_states jsonb,
  p_memory jsonb,
  p_idempotency_key text
) returns void language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_job public.ai_jobs%rowtype;
  v_roll jsonb;
  v_event jsonb;
  v_state jsonb;
  v_character_id uuid;
  v_seen_character_ids uuid[] := '{}';
  v_row_count integer;
begin
  if p_idempotency_key is null or btrim(p_idempotency_key) = '' then
    raise exception 'idempotency key is required';
  end if;
  if p_narration is null or btrim(p_narration) = '' or p_next_prompt is null or btrim(p_next_prompt) = '' then
    raise exception 'narration and next prompt are required';
  end if;
  if coalesce(jsonb_typeof(p_rolls), '') <> 'array'
    or coalesce(jsonb_typeof(p_events), '') <> 'array'
    or coalesce(jsonb_typeof(p_character_states), '') <> 'array' then
    raise exception 'rolls, events, and character states must be arrays';
  end if;

  select * into v_round from public.rounds where id = p_round_id for update;
  if not found then raise exception 'round not found'; end if;
  if v_round.status = 'resolved' then return; end if;
  if v_round.status not in ('queued', 'resolving') then
    raise exception 'round is not queued for resolution';
  end if;

  select * into v_job
  from public.ai_jobs
  where idempotency_key = p_idempotency_key
     or id::text = p_idempotency_key
  for update;
  if not found then
    raise exception 'resolution job not found';
  end if;
  if v_job.job_type <> 'resolve-round' or v_job.entity_id <> p_round_id then
    raise exception 'idempotency key belongs to another job';
  end if;
  if v_job.status = 'complete' then return; end if;
  if v_job.status <> 'running' then
    raise exception 'resolution job must be claimed before commit';
  end if;
  update public.ai_jobs
  set status = 'running',
      error = null,
      updated_at = now()
  where id = v_job.id;
  update public.rounds set status = 'resolving', error = null where id = p_round_id;

  for v_roll in select * from jsonb_array_elements(p_rolls) loop
    if not exists (
      select 1 from public.characters
      where id = (v_roll->>'actorId')::uuid and campaign_id = v_round.campaign_id
    ) or (
      nullif(v_roll->>'targetId', '') is not null
      and not exists (
        select 1 from public.characters
        where id = nullif(v_roll->>'targetId','')::uuid and campaign_id = v_round.campaign_id
      )
    ) then
      raise exception 'roll actor or target is outside the round campaign';
    end if;
    insert into public.dice_rolls(id, round_id, actor_id, target_id, check_kind, formula, rolls, kept, modifier, total, difficulty, success, created_at)
    values ((v_roll->>'id')::uuid, p_round_id, (v_roll->>'actorId')::uuid, nullif(v_roll->>'targetId','')::uuid,
      v_roll->>'checkKind', v_roll->>'formula', array(select jsonb_array_elements_text(v_roll->'rolls')::integer),
      array(select jsonb_array_elements_text(v_roll->'kept')::integer), (v_roll->>'modifier')::integer,
      (v_roll->>'total')::integer, nullif(v_roll->>'difficulty','')::integer, (v_roll->>'success')::boolean,
      (v_roll->>'createdAt')::timestamptz);
  end loop;

  for v_event in select * from jsonb_array_elements(p_events) loop
    if coalesce(btrim(v_event->>'type'), '') = '' then
      raise exception 'event type is required';
    end if;
    insert into public.game_events(campaign_id, round_id, event_type, payload)
    values (v_round.campaign_id, p_round_id, v_event->>'type', v_event);
  end loop;

  for v_state in select * from jsonb_array_elements(p_character_states) loop
    v_character_id := (v_state->>'id')::uuid;
    if v_character_id = any(v_seen_character_ids) then
      raise exception 'duplicate character state for %', v_character_id;
    end if;
    v_seen_character_ids := array_append(v_seen_character_ids, v_character_id);
    if coalesce(jsonb_typeof(v_state->'inventory'), '') <> 'array'
      or coalesce(jsonb_typeof(v_state->'statuses'), '') <> 'array' then
      raise exception 'inventory and statuses must be arrays';
    end if;
    update public.characters
    set hp = (v_state->>'hp')::integer,
        inventory = v_state->'inventory',
        statuses = v_state->'statuses'
    where id = v_character_id and campaign_id = v_round.campaign_id;
    get diagnostics v_row_count = row_count;
    if v_row_count <> 1 then
      raise exception 'character % is outside the round campaign', v_character_id;
    end if;
  end loop;

  if p_memory is not null and p_memory <> 'null'::jsonb then
    if coalesce(jsonb_typeof(p_memory), '') <> 'object' then raise exception 'memory must be an object'; end if;
    insert into public.memories(campaign_id, round_id, summary, importance, tags, entity_ids)
    values (v_round.campaign_id, p_round_id, p_memory->>'summary', (p_memory->>'importance')::integer,
      array(select jsonb_array_elements_text(coalesce(p_memory->'tags', '[]'::jsonb))),
      array(select jsonb_array_elements_text(coalesce(p_memory->'entityIds', '[]'::jsonb))::uuid));
  end if;

  update public.rounds set status = 'resolved', narration = p_narration, next_prompt = p_next_prompt, resolved_at = now() where id = p_round_id;
  update public.campaigns set current_scene = p_narration, next_prompt = p_next_prompt, updated_at = now() where id = v_round.campaign_id;
  update public.ai_jobs
  set status = 'complete', claimed_by = null, lease_expires_at = null, updated_at = now()
  where id = v_job.id;
end;
$$;

revoke all on function public.commit_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text) from public, anon, authenticated;
grant execute on function public.commit_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text) to service_role;

create or replace function public.commit_claimed_round_resolution(
  p_round_id uuid,
  p_narration text,
  p_next_prompt text,
  p_rolls jsonb,
  p_events jsonb,
  p_character_states jsonb,
  p_memory jsonb,
  p_idempotency_key text,
  p_worker_id text
) returns void language plpgsql security definer set search_path = '' as $$
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;
  if not exists (
    select 1 from public.ai_jobs
    where idempotency_key = p_idempotency_key
      and job_type = 'resolve-round'
      and entity_id = p_round_id
      and status = 'running'
      and claimed_by = p_worker_id
      and lease_expires_at > now()
  ) then
    raise exception 'resolution job is not owned by this worker';
  end if;
  perform public.commit_round_resolution(p_round_id, p_narration, p_next_prompt, p_rolls, p_events, p_character_states, p_memory, p_idempotency_key);
end;
$$;

revoke all on function public.commit_claimed_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text) from public, anon, authenticated;
grant execute on function public.commit_claimed_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text) to service_role;

-- ============================================================================
-- 0002_supabase_ai_queue.sql
-- ============================================================================

-- Adds the Redis-free Supabase outbox worker contract to databases created
-- before queue leases were introduced. Safe to apply after the current 0001.

alter table public.ai_jobs add column if not exists claimed_by text;
alter table public.ai_jobs add column if not exists lease_expires_at timestamptz;
alter table public.ai_jobs add column if not exists available_at timestamptz not null default now();
alter table public.ai_jobs add column if not exists max_attempts integer not null default 3;

-- A worker from the old implementation cannot own a database lease. Return
-- such unfinished jobs to the queue before enforcing lease consistency.
update public.ai_jobs
set status = 'queued',
    claimed_by = null,
    lease_expires_at = null,
    available_at = now(),
    updated_at = now()
where status = 'running'
  and (claimed_by is null or lease_expires_at is null);

update public.ai_jobs set available_at = now() where available_at is null;
update public.ai_jobs set max_attempts = 3 where max_attempts is null;
alter table public.ai_jobs alter column available_at set default now();
alter table public.ai_jobs alter column available_at set not null;
alter table public.ai_jobs alter column max_attempts set default 3;
alter table public.ai_jobs alter column max_attempts set not null;
alter table public.ai_jobs enable row level security;

do $$
begin
  if not exists (
    select 1 from pg_constraint
    where conrelid = 'public.ai_jobs'::regclass
      and conname = 'ai_jobs_max_attempts_check'
  ) then
    alter table public.ai_jobs
      add constraint ai_jobs_max_attempts_check check (max_attempts between 1 and 10);
  end if;

  if not exists (
    select 1 from pg_constraint
    where conrelid = 'public.ai_jobs'::regclass
      and conname = 'ai_jobs_lease_consistency_check'
  ) then
    alter table public.ai_jobs
      add constraint ai_jobs_lease_consistency_check check (
        (status = 'running' and claimed_by is not null and lease_expires_at is not null)
        or (status <> 'running' and claimed_by is null and lease_expires_at is null)
      );
  end if;
end;
$$;

create index if not exists ai_jobs_poll_idx
  on public.ai_jobs(available_at, created_at) where status in ('queued', 'running');
create index if not exists ai_jobs_expired_lease_idx
  on public.ai_jobs(lease_expires_at) where status = 'running';

create or replace function public.claim_ai_job(
  p_worker_id text
) returns table(id uuid, job_type text, entity_id uuid)
language plpgsql security definer set search_path = '' as $$
declare
  v_job public.ai_jobs%rowtype;
  v_exhausted public.ai_jobs%rowtype;
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;

  for v_exhausted in
    select * from public.ai_jobs
    where (
        (status = 'running' and lease_expires_at <= now())
        or (status = 'queued' and available_at <= now())
      )
      and attempts >= max_attempts
    order by created_at
    for update skip locked
    limit 25
  loop
    update public.ai_jobs
    set status = 'failed', claimed_by = null, lease_expires_at = null,
        error = coalesce(error, 'Worker lease expired after the final attempt'), updated_at = now()
    where public.ai_jobs.id = v_exhausted.id;
    if v_exhausted.job_type = 'resolve-round' then
      update public.rounds
      set status = 'failed', error = 'Worker lease expired after the final attempt'
      where public.rounds.id = v_exhausted.entity_id and status <> 'resolved';
    elsif v_exhausted.job_type = 'generate-world' then
      update public.coauthor_sessions
      set status = 'failed', updated_at = now()
      where public.coauthor_sessions.id = v_exhausted.entity_id and status <> 'ready';
    end if;
  end loop;

  select * into v_job
  from public.ai_jobs
  where attempts < max_attempts
    and available_at <= now()
    and (
      status = 'queued'
      or (status = 'running' and lease_expires_at <= now())
    )
  order by created_at
  for update skip locked
  limit 1;

  if not found then return; end if;

  update public.ai_jobs
  set status = 'running',
      attempts = attempts + 1,
      claimed_by = p_worker_id,
      lease_expires_at = now() + interval '3 minutes',
      error = null,
      updated_at = now()
  where public.ai_jobs.id = v_job.id;

  if v_job.job_type = 'resolve-round' then
    update public.rounds set status = 'resolving', error = null where public.rounds.id = v_job.entity_id;
  elsif v_job.job_type = 'generate-world' then
    update public.coauthor_sessions set status = 'generating', updated_at = now() where public.coauthor_sessions.id = v_job.entity_id;
  end if;

  return query select v_job.id, v_job.job_type, v_job.entity_id;
end;
$$;

create or replace function public.claim_ai_job_by_id(
  p_job_id uuid,
  p_worker_id text
) returns table(id uuid, job_type text, entity_id uuid)
language plpgsql security definer set search_path = '' as $$
declare
  v_job public.ai_jobs%rowtype;
begin
  if p_job_id is null then raise exception 'job id is required'; end if;
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;

  select * into v_job from public.ai_jobs where public.ai_jobs.id = p_job_id for update;

  if not found then return; end if;

  if (
      (v_job.status = 'running' and v_job.lease_expires_at <= now())
      or (v_job.status = 'queued' and v_job.available_at <= now())
    )
    and v_job.attempts >= v_job.max_attempts then
    update public.ai_jobs
    set status = 'failed', claimed_by = null, lease_expires_at = null,
        error = coalesce(error, 'Worker lease expired after the final attempt'), updated_at = now()
    where public.ai_jobs.id = v_job.id;
    if v_job.job_type = 'resolve-round' then
      update public.rounds
      set status = 'failed', error = 'Worker lease expired after the final attempt'
      where public.rounds.id = v_job.entity_id and status <> 'resolved';
    elsif v_job.job_type = 'generate-world' then
      update public.coauthor_sessions
      set status = 'failed', updated_at = now()
      where public.coauthor_sessions.id = v_job.entity_id and status <> 'ready';
    end if;
    return;
  end if;

  if v_job.attempts >= v_job.max_attempts
    or v_job.available_at > now()
    or not (
      v_job.status = 'queued'
      or (v_job.status = 'running' and v_job.lease_expires_at <= now())
    ) then
    return;
  end if;

  update public.ai_jobs
  set status = 'running',
      attempts = attempts + 1,
      claimed_by = p_worker_id,
      lease_expires_at = now() + interval '3 minutes',
      error = null,
      updated_at = now()
  where public.ai_jobs.id = v_job.id;

  if v_job.job_type = 'resolve-round' then
    update public.rounds set status = 'resolving', error = null where public.rounds.id = v_job.entity_id;
  elsif v_job.job_type = 'generate-world' then
    update public.coauthor_sessions set status = 'generating', updated_at = now() where public.coauthor_sessions.id = v_job.entity_id;
  end if;

  return query select v_job.id, v_job.job_type, v_job.entity_id;
end;
$$;

create or replace function public.complete_ai_job(
  p_job_id uuid,
  p_worker_id text
) returns void language plpgsql security definer set search_path = '' as $$
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;
  update public.ai_jobs
  set status = 'complete', claimed_by = null, lease_expires_at = null, updated_at = now()
  where id = p_job_id
    and status = 'running'
    and claimed_by = p_worker_id
    and lease_expires_at > now();
  if not found then raise exception 'job is not owned by this worker'; end if;
end;
$$;

create or replace function public.retry_ai_job(
  p_job_id uuid,
  p_worker_id text,
  p_error text
) returns void language plpgsql security definer set search_path = '' as $$
declare
  v_job public.ai_jobs%rowtype;
  v_retry boolean;
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;
  select * into v_job from public.ai_jobs where id = p_job_id for update;
  if not found or v_job.status <> 'running' or v_job.claimed_by <> p_worker_id then
    raise exception 'job is not owned by this worker';
  end if;
  if v_job.lease_expires_at <= now() then
    raise exception 'job lease has expired';
  end if;

  v_retry := v_job.attempts < v_job.max_attempts;
  update public.ai_jobs
  set status = case when v_retry then 'queued' else 'failed' end,
      claimed_by = null,
      lease_expires_at = null,
      available_at = case
        when v_retry then now() + make_interval(secs => 5 * power(2, v_job.attempts - 1)::integer)
        else available_at
      end,
      error = left(p_error, 2000),
      updated_at = now()
  where id = p_job_id;

  if v_job.job_type = 'resolve-round' then
    update public.rounds
    set status = case
          when v_retry then 'queued'::public.round_status
          else 'failed'::public.round_status
        end,
        error = left(p_error, 2000)
    where id = v_job.entity_id and status <> 'resolved';
  elsif v_job.job_type = 'generate-world' then
    update public.coauthor_sessions
    set status = case when v_retry then 'generating' else 'failed' end,
        updated_at = now()
    where id = v_job.entity_id and status <> 'ready';
  end if;
end;
$$;

create or replace function public.renew_ai_job_lease(
  p_job_id uuid,
  p_worker_id text
) returns timestamptz language plpgsql security definer set search_path = '' as $$
declare
  v_lease_expires_at timestamptz;
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;
  update public.ai_jobs
  set lease_expires_at = now() + interval '3 minutes', updated_at = now()
  where id = p_job_id
    and status = 'running'
    and claimed_by = p_worker_id
    and lease_expires_at > now()
  returning lease_expires_at into v_lease_expires_at;
  if not found then raise exception 'job is not owned by this worker or lease expired'; end if;
  return v_lease_expires_at;
end;
$$;

-- Replace the pre-lease commit function as well: old databases completed a
-- job without clearing its lease, which conflicts with the new invariant.
create or replace function public.commit_round_resolution(
  p_round_id uuid,
  p_narration text,
  p_next_prompt text,
  p_rolls jsonb,
  p_events jsonb,
  p_character_states jsonb,
  p_memory jsonb,
  p_idempotency_key text
) returns void language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_job public.ai_jobs%rowtype;
  v_roll jsonb;
  v_event jsonb;
  v_state jsonb;
  v_character_id uuid;
  v_seen_character_ids uuid[] := '{}';
  v_row_count integer;
begin
  if p_idempotency_key is null or btrim(p_idempotency_key) = '' then
    raise exception 'idempotency key is required';
  end if;
  if p_narration is null or btrim(p_narration) = '' or p_next_prompt is null or btrim(p_next_prompt) = '' then
    raise exception 'narration and next prompt are required';
  end if;
  if coalesce(jsonb_typeof(p_rolls), '') <> 'array'
    or coalesce(jsonb_typeof(p_events), '') <> 'array'
    or coalesce(jsonb_typeof(p_character_states), '') <> 'array' then
    raise exception 'rolls, events, and character states must be arrays';
  end if;

  select * into v_round from public.rounds where id = p_round_id for update;
  if not found then raise exception 'round not found'; end if;
  if v_round.status = 'resolved' then return; end if;
  if v_round.status not in ('queued', 'resolving') then
    raise exception 'round is not queued for resolution';
  end if;

  select * into v_job
  from public.ai_jobs
  where idempotency_key = p_idempotency_key
     or id::text = p_idempotency_key
  for update;
  if not found then raise exception 'resolution job not found'; end if;
  if v_job.job_type <> 'resolve-round' or v_job.entity_id <> p_round_id then
    raise exception 'idempotency key belongs to another job';
  end if;
  if v_job.status = 'complete' then return; end if;
  if v_job.status <> 'running' then
    raise exception 'resolution job must be claimed before commit';
  end if;

  update public.ai_jobs set error = null, updated_at = now() where id = v_job.id;
  update public.rounds set status = 'resolving', error = null where id = p_round_id;

  for v_roll in select * from jsonb_array_elements(p_rolls) loop
    if not exists (
      select 1 from public.characters
      where id = (v_roll->>'actorId')::uuid and campaign_id = v_round.campaign_id
    ) or (
      nullif(v_roll->>'targetId', '') is not null
      and not exists (
        select 1 from public.characters
        where id = nullif(v_roll->>'targetId', '')::uuid and campaign_id = v_round.campaign_id
      )
    ) then
      raise exception 'roll actor or target is outside the round campaign';
    end if;
    insert into public.dice_rolls(
      id, round_id, actor_id, target_id, check_kind, formula, rolls, kept,
      modifier, total, difficulty, success, created_at
    ) values (
      (v_roll->>'id')::uuid, p_round_id, (v_roll->>'actorId')::uuid,
      nullif(v_roll->>'targetId', '')::uuid, v_roll->>'checkKind', v_roll->>'formula',
      array(select jsonb_array_elements_text(v_roll->'rolls')::integer),
      array(select jsonb_array_elements_text(v_roll->'kept')::integer),
      (v_roll->>'modifier')::integer, (v_roll->>'total')::integer,
      nullif(v_roll->>'difficulty', '')::integer, (v_roll->>'success')::boolean,
      (v_roll->>'createdAt')::timestamptz
    );
  end loop;

  for v_event in select * from jsonb_array_elements(p_events) loop
    if coalesce(btrim(v_event->>'type'), '') = '' then
      raise exception 'event type is required';
    end if;
    insert into public.game_events(campaign_id, round_id, event_type, payload)
    values (v_round.campaign_id, p_round_id, v_event->>'type', v_event);
  end loop;

  for v_state in select * from jsonb_array_elements(p_character_states) loop
    v_character_id := (v_state->>'id')::uuid;
    if v_character_id = any(v_seen_character_ids) then
      raise exception 'duplicate character state for %', v_character_id;
    end if;
    v_seen_character_ids := array_append(v_seen_character_ids, v_character_id);
    if coalesce(jsonb_typeof(v_state->'inventory'), '') <> 'array'
      or coalesce(jsonb_typeof(v_state->'statuses'), '') <> 'array' then
      raise exception 'inventory and statuses must be arrays';
    end if;
    update public.characters
    set hp = (v_state->>'hp')::integer,
        inventory = v_state->'inventory',
        statuses = v_state->'statuses'
    where id = v_character_id and campaign_id = v_round.campaign_id;
    get diagnostics v_row_count = row_count;
    if v_row_count <> 1 then
      raise exception 'character % is outside the round campaign', v_character_id;
    end if;
  end loop;

  if p_memory is not null and p_memory <> 'null'::jsonb then
    if coalesce(jsonb_typeof(p_memory), '') <> 'object' then
      raise exception 'memory must be an object';
    end if;
    insert into public.memories(campaign_id, round_id, summary, importance, tags, entity_ids)
    values (
      v_round.campaign_id,
      p_round_id,
      p_memory->>'summary',
      (p_memory->>'importance')::integer,
      array(select jsonb_array_elements_text(coalesce(p_memory->'tags', '[]'::jsonb))),
      array(select jsonb_array_elements_text(coalesce(p_memory->'entityIds', '[]'::jsonb))::uuid)
    );
  end if;

  update public.rounds
  set status = 'resolved', narration = p_narration, next_prompt = p_next_prompt, resolved_at = now()
  where id = p_round_id;
  update public.campaigns
  set current_scene = p_narration, next_prompt = p_next_prompt, updated_at = now()
  where id = v_round.campaign_id;
  update public.ai_jobs
  set status = 'complete', claimed_by = null, lease_expires_at = null, updated_at = now()
  where id = v_job.id;
end;
$$;

create or replace function public.commit_claimed_round_resolution(
  p_round_id uuid,
  p_narration text,
  p_next_prompt text,
  p_rolls jsonb,
  p_events jsonb,
  p_character_states jsonb,
  p_memory jsonb,
  p_idempotency_key text,
  p_worker_id text
) returns void language plpgsql security definer set search_path = '' as $$
declare
  v_job public.ai_jobs%rowtype;
begin
  if p_worker_id is null or btrim(p_worker_id) = '' then
    raise exception 'worker id is required';
  end if;
  select * into v_job
    from public.ai_jobs
    where (idempotency_key = p_idempotency_key or id::text = p_idempotency_key)
      and job_type = 'resolve-round'
      and entity_id = p_round_id
    for update;
  if not found
    or v_job.status <> 'running'
    or v_job.claimed_by <> p_worker_id
    or v_job.lease_expires_at <= now() then
    raise exception 'resolution job is not owned by this worker';
  end if;

  perform public.commit_round_resolution(
    p_round_id,
    p_narration,
    p_next_prompt,
    p_rolls,
    p_events,
    p_character_states,
    p_memory,
    p_idempotency_key
  );
end;
$$;

revoke all on function public.claim_ai_job(text) from public, anon, authenticated;
revoke all on function public.claim_ai_job_by_id(uuid, text) from public, anon, authenticated;
revoke all on function public.complete_ai_job(uuid, text) from public, anon, authenticated;
revoke all on function public.retry_ai_job(uuid, text, text) from public, anon, authenticated;
revoke all on function public.renew_ai_job_lease(uuid, text) from public, anon, authenticated;
revoke all on function public.commit_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text) from public, anon, authenticated;
revoke all on function public.commit_claimed_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text) from public, anon, authenticated;

grant execute on function public.claim_ai_job(text) to service_role;
grant execute on function public.claim_ai_job_by_id(uuid, text) to service_role;
grant execute on function public.complete_ai_job(uuid, text) to service_role;
grant execute on function public.retry_ai_job(uuid, text, text) to service_role;
grant execute on function public.renew_ai_job_lease(uuid, text) to service_role;
grant execute on function public.commit_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text) to service_role;
grant execute on function public.commit_claimed_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text) to service_role;

-- Make newly-created RPCs visible to PostgREST immediately.
notify pgrst, 'reload schema';

-- ============================================================================
-- 0003_stage_two_multiplayer.sql
-- ============================================================================

-- Stage two multiplayer primitives. All mutating RPCs are service-role only;
-- Nitro authenticates the caller and passes the verified auth.users id.

alter table public.coauthor_sessions
  add column if not exists confirmed_world_id uuid references public.worlds(id) on delete set null;
alter table public.worlds add column if not exists hook text;
alter table public.worlds add column if not exists opening_scene text;

create index if not exists coauthor_sessions_owner_updated_idx
  on public.coauthor_sessions(owner_id, updated_at desc);
create index if not exists invites_campaign_expiry_idx
  on public.invites(campaign_id, expires_at);
create unique index if not exists ai_jobs_world_generation_unique
  on public.ai_jobs(entity_id) where job_type = 'generate-world';

create or replace function public.stage_two_append_coauthor_message(
  p_session_id uuid,
  p_owner_id uuid,
  p_content text
) returns jsonb language plpgsql security definer set search_path = '' as $$
declare
  v_session public.coauthor_sessions%rowtype;
  v_result jsonb;
begin
  if p_content is null or char_length(btrim(p_content)) not between 1 and 5000 then
    raise exception 'message must be between 1 and 5000 characters';
  end if;
  select * into v_session from public.coauthor_sessions
  where id = p_session_id and owner_id = p_owner_id for update;
  if not found then raise exception 'coauthor session not found'; end if;
  if v_session.status in ('generating', 'confirmed') then
    raise exception 'coauthor session cannot be edited in status %', v_session.status;
  end if;
  update public.coauthor_sessions
  set messages = messages || jsonb_build_array(jsonb_build_object('role', 'user', 'content', btrim(p_content))),
      generated_world = null,
      status = 'collecting',
      updated_at = now()
  where id = p_session_id
  returning jsonb_build_object('id', id, 'status', status, 'messages', messages, 'updatedAt', updated_at)
  into v_result;
  return v_result;
end;
$$;

create or replace function public.stage_two_append_coauthor_assistant_message(
  p_session_id uuid,
  p_owner_id uuid,
  p_content text
) returns void language plpgsql security definer set search_path = '' as $$
begin
  if p_content is null or char_length(btrim(p_content)) not between 1 and 5000 then
    raise exception 'assistant message must be between 1 and 5000 characters';
  end if;
  update public.coauthor_sessions
  set messages = messages || jsonb_build_array(jsonb_build_object('role', 'assistant', 'content', btrim(p_content))),
      updated_at = now()
  where id = p_session_id and owner_id = p_owner_id and status = 'collecting';
  if not found then raise exception 'collecting coauthor session not found'; end if;
end;
$$;

create or replace function public.stage_two_open_next_round()
returns trigger language plpgsql security definer set search_path = '' as $$
begin
  if old.status is distinct from new.status
     and new.status in ('resolved'::public.round_status, 'failed'::public.round_status) then
    update public.characters
    set controller = 'human'::public.character_controller
    where campaign_id = new.campaign_id
      and controller = 'delegated'::public.character_controller
      and user_id is not null;
    if new.status = 'resolved'::public.round_status
       and exists (select 1 from public.campaigns where id = new.campaign_id and status = 'active') then
      insert into public.rounds(campaign_id, number, status)
      values (new.campaign_id, new.number + 1, 'open')
      on conflict (campaign_id, number) do nothing;
    end if;
  end if;
  return new;
end;
$$;

drop trigger if exists stage_two_round_resolved_open_next on public.rounds;
create trigger stage_two_round_resolved_open_next
after update of status on public.rounds
for each row execute function public.stage_two_open_next_round();

insert into public.rounds(campaign_id, number, status)
select latest.campaign_id, latest.number + 1, 'open'
from public.rounds latest
join public.campaigns campaign on campaign.id = latest.campaign_id and campaign.status = 'active'
where latest.status = 'resolved'
  and not exists (
    select 1 from public.rounds newer
    where newer.campaign_id = latest.campaign_id and newer.number > latest.number
  )
on conflict (campaign_id, number) do nothing;

create or replace function public.stage_two_upsert_story_summary(
  p_job_id uuid,
  p_campaign_id uuid,
  p_through_round integer,
  p_summary text
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_summary_id uuid;
begin
  if p_through_round < 1 or p_summary is null or char_length(btrim(p_summary)) not between 20 and 6000 then
    raise exception 'invalid story summary';
  end if;
  if not exists (
    select 1
    from public.ai_jobs job
    join public.rounds round on round.id = job.entity_id
    where job.id = p_job_id
      and job.job_type = 'resolve-round'
      and job.status = 'complete'
      and round.campaign_id = p_campaign_id
      and round.number = p_through_round
      and round.status = 'resolved'
  ) then
    raise exception 'completed round job not found';
  end if;
  insert into public.story_summaries(campaign_id, through_round, summary)
  values (p_campaign_id, p_through_round, btrim(p_summary))
  on conflict (campaign_id, through_round) do update set summary = excluded.summary
  returning id into v_summary_id;
  return v_summary_id;
end;
$$;

create or replace function public.stage_two_enqueue_world_generation(
  p_session_id uuid,
  p_owner_id uuid
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_session public.coauthor_sessions%rowtype;
  v_job public.ai_jobs%rowtype;
begin
  select * into v_session from public.coauthor_sessions
  where id = p_session_id and owner_id = p_owner_id for update;
  if not found then raise exception 'coauthor session not found'; end if;
  if v_session.status = 'confirmed' then raise exception 'coauthor session is already confirmed'; end if;
  if jsonb_array_length(v_session.messages) < 1 then raise exception 'at least one message is required'; end if;

  select * into v_job from public.ai_jobs
  where job_type = 'generate-world' and entity_id = p_session_id for update;
  if found then
    if v_job.status in ('queued', 'running') then return v_job.id; end if;
    update public.ai_jobs
    set status = 'queued', attempts = 0, claimed_by = null, lease_expires_at = null,
        available_at = now(), error = null, updated_at = now()
    where id = v_job.id;
  else
    v_job.id := gen_random_uuid();
    insert into public.ai_jobs(id, job_type, entity_id, idempotency_key, status)
    values (v_job.id, 'generate-world', p_session_id, v_job.id::text, 'queued');
  end if;
  update public.coauthor_sessions
  set status = 'generating', generated_world = null, updated_at = now()
  where id = p_session_id;
  return v_job.id;
end;
$$;

create or replace function public.stage_two_confirm_world(
  p_session_id uuid,
  p_owner_id uuid
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_session public.coauthor_sessions%rowtype;
  v_world jsonb;
  v_world_id uuid;
  v_entity jsonb;
begin
  select * into v_session from public.coauthor_sessions
  where id = p_session_id and owner_id = p_owner_id for update;
  if not found then raise exception 'coauthor session not found'; end if;
  if v_session.status = 'confirmed' and v_session.confirmed_world_id is not null then
    return v_session.confirmed_world_id;
  end if;
  if v_session.status <> 'ready' or v_session.generated_world is null then
    raise exception 'coauthor session is not ready to confirm';
  end if;
  v_world := v_session.generated_world;
  if coalesce(v_world->>'title', '') = '' or coalesce(v_world->>'openingScene', '') = '' then
    raise exception 'generated world is incomplete';
  end if;

  insert into public.worlds(
    owner_id, title, genre, tone, premise, content_boundaries,
    hidden_threat, hook, opening_scene, status
  ) values (
    p_owner_id, v_world->>'title', v_world->>'genre', v_world->>'tone', v_world->>'premise',
    coalesce(v_world->'contentBoundaries', '[]'::jsonb), v_world->>'hiddenThreat',
    v_world->>'hook', v_world->>'openingScene', 'confirmed'
  ) returning id into v_world_id;

  v_entity := v_world->'startingLocation';
  insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
  values (v_world_id, 'location', v_entity->>'name', v_entity->>'summary',
    array(select jsonb_array_elements_text(coalesce(v_entity->'tags', '[]'::jsonb))),
    coalesce(v_entity->'secrets', '[]'::jsonb));
  for v_entity in select * from jsonb_array_elements(v_world->'npcs') loop
    insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
    values (v_world_id, 'npc', v_entity->>'name', v_entity->>'summary',
      array(select jsonb_array_elements_text(coalesce(v_entity->'tags', '[]'::jsonb))),
      coalesce(v_entity->'secrets', '[]'::jsonb));
  end loop;
  for v_entity in select * from jsonb_array_elements(v_world->'factions') loop
    insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
    values (v_world_id, 'faction', v_entity->>'name', v_entity->>'summary',
      array(select jsonb_array_elements_text(coalesce(v_entity->'tags', '[]'::jsonb))),
      coalesce(v_entity->'secrets', '[]'::jsonb));
  end loop;
  insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
  values (v_world_id, 'quest', 'Opening Hook', v_world->>'hook', array['opening'], '[]'::jsonb);

  update public.coauthor_sessions
  set status = 'confirmed', confirmed_world_id = v_world_id, updated_at = now()
  where id = p_session_id;
  return v_world_id;
end;
$$;

create or replace function public.stage_two_create_campaign(
  p_world_id uuid,
  p_owner_id uuid,
  p_title text
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_world public.worlds%rowtype;
  v_campaign_id uuid;
begin
  if p_title is null or char_length(btrim(p_title)) not between 3 and 100 then
    raise exception 'campaign title must be between 3 and 100 characters';
  end if;
  select * into v_world from public.worlds
  where id = p_world_id and owner_id = p_owner_id and status = 'confirmed' for share;
  if not found then raise exception 'confirmed world not found'; end if;
  insert into public.campaigns(world_id, owner_id, title, current_scene, next_prompt, status)
  values (p_world_id, p_owner_id, btrim(p_title), coalesce(v_world.opening_scene, v_world.premise), 'What do you do?', 'active')
  returning id into v_campaign_id;
  insert into public.campaign_members(campaign_id, user_id, role)
  values (v_campaign_id, p_owner_id, 'owner');
  insert into public.rounds(campaign_id, number) values (v_campaign_id, 1);
  return v_campaign_id;
end;
$$;

create or replace function public.stage_two_join_campaign(
  p_token_hash text,
  p_user_id uuid
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_invite public.invites%rowtype;
  v_campaign_id uuid;
begin
  if p_token_hash is null or char_length(p_token_hash) <> 64 then raise exception 'invalid invite token'; end if;
  select * into v_invite from public.invites where token_hash = p_token_hash for update;
  if not found or v_invite.expires_at <= now() or v_invite.uses >= v_invite.max_uses then
    raise exception 'invite is invalid or expired';
  end if;
  if not exists (select 1 from public.profiles where id = p_user_id) then raise exception 'profile not found'; end if;
  if not exists (select 1 from public.campaigns where id = v_invite.campaign_id and status <> 'archived') then
    raise exception 'campaign is not available';
  end if;
  v_campaign_id := v_invite.campaign_id;
  if exists (select 1 from public.campaign_members where campaign_id = v_campaign_id and user_id = p_user_id) then
    update public.campaign_members set active = true where campaign_id = v_campaign_id and user_id = p_user_id;
    return v_campaign_id;
  end if;
  insert into public.campaign_members(campaign_id, user_id, role) values (v_campaign_id, p_user_id, 'player');
  update public.invites set uses = uses + 1 where id = v_invite.id;
  return v_campaign_id;
end;
$$;

create or replace function public.stage_two_submit_intent(
  p_round_id uuid,
  p_user_id uuid,
  p_character_id uuid,
  p_action text,
  p_ready boolean default false
) returns jsonb language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_member public.campaign_members%rowtype;
  v_intent_id uuid;
  v_job_id uuid;
begin
  if p_action is null or char_length(btrim(p_action)) not between 1 and 2000 then
    raise exception 'action must be between 1 and 2000 characters';
  end if;
  select * into v_round from public.rounds where id = p_round_id for update;
  if not found or v_round.status <> 'open' then raise exception 'round is not open'; end if;
  select * into v_member from public.campaign_members
  where campaign_id = v_round.campaign_id and user_id = p_user_id and active;
  if not found then raise exception 'active campaign membership not found'; end if;
  if not exists (
    select 1 from public.characters
    where id = p_character_id and campaign_id = v_round.campaign_id
      and user_id = p_user_id and controller = 'human'
  ) then raise exception 'controlled character not found'; end if;

  insert into public.player_intents(round_id, member_id, character_id, action, ready)
  values (p_round_id, v_member.id, p_character_id, btrim(p_action), coalesce(p_ready, false))
  on conflict (round_id, member_id) do update
  set character_id = excluded.character_id, action = excluded.action,
      ready = excluded.ready, updated_at = now()
  returning id into v_intent_id;

  if coalesce(p_ready, false) and not exists (
    select 1 from public.campaign_members member
    where member.campaign_id = v_round.campaign_id and member.active
      and not exists (
        select 1 from public.player_intents intent
        where intent.round_id = p_round_id and intent.member_id = member.id and intent.ready
      )
  ) then
    v_job_id := public.enqueue_round_resolution(p_round_id, null);
  end if;
  return jsonb_build_object('intentId', v_intent_id, 'jobId', v_job_id);
end;
$$;

create or replace function public.stage_two_force_round(
  p_round_id uuid,
  p_owner_id uuid
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_job_id uuid;
begin
  select * into v_round from public.rounds where id = p_round_id for update;
  if not found or v_round.status <> 'open' then raise exception 'round is not open'; end if;
  if not exists (
    select 1 from public.campaigns
    where id = v_round.campaign_id and owner_id = p_owner_id
  ) then raise exception 'only the campaign owner can force a round'; end if;

  update public.characters character
  set controller = 'delegated'::public.character_controller
  from public.campaign_members member
  where character.campaign_id = v_round.campaign_id
    and character.user_id = member.user_id
    and character.controller = 'human'::public.character_controller
    and member.campaign_id = v_round.campaign_id
    and member.active
    and member.ai_takeover_allowed
    and not exists (
      select 1 from public.player_intents intent
      where intent.round_id = p_round_id
        and intent.member_id = member.id
        and intent.ready
    );

  v_job_id := public.enqueue_round_resolution(p_round_id, p_owner_id);
  return v_job_id;
end;
$$;

revoke all on function public.stage_two_append_coauthor_message(uuid, uuid, text) from public, anon, authenticated;
revoke all on function public.stage_two_append_coauthor_assistant_message(uuid, uuid, text) from public, anon, authenticated;
revoke all on function public.stage_two_enqueue_world_generation(uuid, uuid) from public, anon, authenticated;
revoke all on function public.stage_two_confirm_world(uuid, uuid) from public, anon, authenticated;
revoke all on function public.stage_two_create_campaign(uuid, uuid, text) from public, anon, authenticated;
revoke all on function public.stage_two_join_campaign(text, uuid) from public, anon, authenticated;
revoke all on function public.stage_two_submit_intent(uuid, uuid, uuid, text, boolean) from public, anon, authenticated;
revoke all on function public.stage_two_force_round(uuid, uuid) from public, anon, authenticated;
revoke all on function public.stage_two_open_next_round() from public, anon, authenticated;
revoke all on function public.stage_two_upsert_story_summary(uuid, uuid, integer, text) from public, anon, authenticated;
grant execute on function public.stage_two_append_coauthor_message(uuid, uuid, text) to service_role;
grant execute on function public.stage_two_append_coauthor_assistant_message(uuid, uuid, text) to service_role;
grant execute on function public.stage_two_enqueue_world_generation(uuid, uuid) to service_role;
grant execute on function public.stage_two_confirm_world(uuid, uuid) to service_role;
grant execute on function public.stage_two_create_campaign(uuid, uuid, text) to service_role;
grant execute on function public.stage_two_join_campaign(text, uuid) to service_role;
grant execute on function public.stage_two_submit_intent(uuid, uuid, uuid, text, boolean) to service_role;
grant execute on function public.stage_two_force_round(uuid, uuid) to service_role;
grant execute on function public.stage_two_upsert_story_summary(uuid, uuid, integer, text) to service_role;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0004_anonymous_alpha_access.sql
-- ============================================================================

-- Guest access still uses a real authenticated Supabase user. This keeps the
-- existing ownership checks, RLS policies, and multiplayer membership model.
-- Email accounts remain restricted to the allowlist.

create or replace function public.create_profile_for_allowlisted_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_is_anonymous boolean := coalesce(new.is_anonymous, false);
  v_display_name text;
begin
  if not v_is_anonymous and (
    new.email is null or not exists (
      select 1
      from public.allowlist
      where email = lower(btrim(new.email))
    )
  ) then
    raise exception using
      errcode = '42501',
      message = 'This email is not invited to the Dungeons & Ground alpha.';
  end if;

  v_display_name := case
    when v_is_anonymous then 'Guest ' || upper(substr(replace(new.id::text, '-', ''), 1, 6))
    else coalesce(nullif(btrim(new.raw_user_meta_data ->> 'display_name'), ''), 'Adventurer')
  end;

  insert into public.profiles(id, display_name)
  values (new.id, v_display_name)
  on conflict (id) do nothing;

  return new;
end;
$$;

revoke all on function public.create_profile_for_allowlisted_user() from public;

-- An anonymous account can be upgraded later. Recheck the allowlist on the
-- relevant auth.users transition so linking an email cannot bypass the alpha
-- gate. Unrelated token and metadata updates do not fire this trigger.
create or replace function public.enforce_alpha_email_allowlist()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  if not coalesce(new.is_anonymous, false) and (
    new.email is null or not exists (
      select 1
      from public.allowlist
      where email = lower(btrim(new.email))
    )
  ) then
    raise exception using
      errcode = '42501',
      message = 'This email is not invited to the Dungeons & Ground alpha.';
  end if;

  return new;
end;
$$;

revoke all on function public.enforce_alpha_email_allowlist() from public;

drop trigger if exists enforce_alpha_email_allowlist on auth.users;
create trigger enforce_alpha_email_allowlist
before update of email, is_anonymous on auth.users
for each row
when (
  old.email is distinct from new.email
  or old.is_anonymous is distinct from new.is_anonymous
)
execute function public.enforce_alpha_email_allowlist();

-- The schema may be installed after Auth users already exist. Backfill only
-- anonymous users and explicitly allowlisted email accounts; do not silently
-- enable unrelated accounts.
insert into public.profiles(id, display_name)
select
  auth_user.id,
  case
    when coalesce(auth_user.is_anonymous, false)
      then 'Guest ' || upper(substr(replace(auth_user.id::text, '-', ''), 1, 6))
    else coalesce(nullif(btrim(auth_user.raw_user_meta_data ->> 'display_name'), ''), 'Adventurer')
  end
from auth.users auth_user
where coalesce(auth_user.is_anonymous, false)
   or exists (
     select 1
     from public.allowlist
     where allowlist.email = lower(btrim(auth_user.email))
   )
on conflict (id) do nothing;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0005_failed_round_retry.sql
-- ============================================================================

-- Owners can safely retry a round after all worker attempts have failed.
-- The queue has one durable outbox row per round. A retry records the failed
-- state in the audit log, then safely re-queues that same row.
create or replace function public.stage_two_retry_failed_round(
  p_round_id uuid,
  p_owner_id uuid
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_job public.ai_jobs%rowtype;
begin
  select * into v_round
  from public.rounds
  where id = p_round_id
  for update;

  if not found then raise exception 'round not found'; end if;
  if v_round.status <> 'failed'::public.round_status then
    raise exception 'round is not failed';
  end if;
  if not exists (
    select 1 from public.campaigns
    where id = v_round.campaign_id
      and owner_id = p_owner_id
      and status = 'active'
  ) then
    raise exception 'only the campaign owner can retry a failed round';
  end if;
  if exists (
    select 1 from public.rounds
    where campaign_id = v_round.campaign_id
      and id <> p_round_id
      and status in ('open'::public.round_status, 'queued'::public.round_status, 'resolving'::public.round_status)
  ) then
    raise exception 'campaign already has an active round';
  end if;

  select * into v_job
  from public.ai_jobs
  where job_type = 'resolve-round' and entity_id = p_round_id
  for update;
  if not found or v_job.status <> 'failed' then
    raise exception 'failed round job not found';
  end if;

  insert into public.audit_entries(
    campaign_id, actor_id, action, entity_type, entity_id, before_state, after_state
  ) values (
    v_round.campaign_id,
    p_owner_id,
    'retry_failed_round',
    'round',
    p_round_id,
    jsonb_build_object('roundError', v_round.error, 'jobError', v_job.error, 'attempts', v_job.attempts),
    jsonb_build_object('status', 'queued')
  );

  update public.ai_jobs
  set status = 'queued',
      attempts = 0,
      claimed_by = null,
      lease_expires_at = null,
      available_at = now(),
      error = null,
      updated_at = now()
  where id = v_job.id;

  update public.rounds
  set status = 'queued'::public.round_status,
      error = null,
      queued_at = now(),
      resolved_at = null,
      forced_by = p_owner_id
  where id = p_round_id;

  return v_job.id;
end;
$$;

revoke all on function public.stage_two_retry_failed_round(uuid, uuid) from public, anon, authenticated;
grant execute on function public.stage_two_retry_failed_round(uuid, uuid) to service_role;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0006_retry_job_compatibility.sql
-- ============================================================================

-- Replace the first retry implementation for installations that already ran
-- migration 0005 against the one-job-per-round queue constraint.
create or replace function public.stage_two_retry_failed_round(
  p_round_id uuid,
  p_owner_id uuid
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_job public.ai_jobs%rowtype;
begin
  select * into v_round from public.rounds where id = p_round_id for update;
  if not found then raise exception 'round not found'; end if;
  if v_round.status <> 'failed'::public.round_status then raise exception 'round is not failed'; end if;
  if not exists (
    select 1 from public.campaigns
    where id = v_round.campaign_id and owner_id = p_owner_id and status = 'active'
  ) then raise exception 'only the campaign owner can retry a failed round'; end if;
  if exists (
    select 1 from public.rounds
    where campaign_id = v_round.campaign_id
      and id <> p_round_id
      and status in ('open'::public.round_status, 'queued'::public.round_status, 'resolving'::public.round_status)
  ) then raise exception 'campaign already has an active round'; end if;

  select * into v_job
  from public.ai_jobs
  where job_type = 'resolve-round' and entity_id = p_round_id
  for update;
  if not found or v_job.status <> 'failed' then raise exception 'failed round job not found'; end if;

  insert into public.audit_entries(
    campaign_id, actor_id, action, entity_type, entity_id, before_state, after_state
  ) values (
    v_round.campaign_id, p_owner_id, 'retry_failed_round', 'round', p_round_id,
    jsonb_build_object('roundError', v_round.error, 'jobError', v_job.error, 'attempts', v_job.attempts),
    jsonb_build_object('status', 'queued')
  );

  update public.ai_jobs
  set status = 'queued', attempts = 0, claimed_by = null, lease_expires_at = null,
      available_at = now(), error = null, updated_at = now()
  where id = v_job.id;

  update public.rounds
  set status = 'queued'::public.round_status, error = null, queued_at = now(),
      resolved_at = null, forced_by = p_owner_id
  where id = p_round_id;

  return v_job.id;
end;
$$;

create or replace function public.dng_schema_version()
returns integer language sql stable security definer set search_path = '' as $$
  select 6;
$$;

revoke all on function public.stage_two_retry_failed_round(uuid, uuid) from public, anon, authenticated;
revoke all on function public.dng_schema_version() from public, anon, authenticated;
grant execute on function public.stage_two_retry_failed_round(uuid, uuid) to service_role;
grant execute on function public.dng_schema_version() to service_role;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0007_stage_three_four_completion.sql
-- ============================================================================

-- Complete the universe/character alpha slice: resumable coauthor questions and
-- an atomic, server-only character creation path.

alter table public.coauthor_sessions
  add column if not exists current_question jsonb;

alter table public.coauthor_sessions
  drop constraint if exists coauthor_sessions_current_question_object;
alter table public.coauthor_sessions
  add constraint coauthor_sessions_current_question_object check (
    current_question is null or jsonb_typeof(current_question) = 'object'
  );

create or replace function public.stage_two_append_coauthor_message(
  p_session_id uuid,
  p_owner_id uuid,
  p_content text
) returns jsonb language plpgsql security definer set search_path = '' as $$
declare
  v_session public.coauthor_sessions%rowtype;
  v_result jsonb;
begin
  if p_content is null or char_length(btrim(p_content)) not between 1 and 5000 then
    raise exception 'message must be between 1 and 5000 characters';
  end if;
  select * into v_session from public.coauthor_sessions
  where id = p_session_id and owner_id = p_owner_id for update;
  if not found then raise exception 'coauthor session not found'; end if;
  if v_session.status in ('generating', 'confirmed') then
    raise exception 'coauthor session cannot be edited in status %', v_session.status;
  end if;
  update public.coauthor_sessions
  set messages = messages || jsonb_build_array(jsonb_build_object('role', 'user', 'content', btrim(p_content))),
      generated_world = null,
      current_question = null,
      status = 'collecting',
      updated_at = now()
  where id = p_session_id
  returning jsonb_build_object('id', id, 'status', status, 'messages', messages, 'updatedAt', updated_at)
  into v_result;
  return v_result;
end;
$$;

create or replace function public.stage_four_set_coauthor_question(
  p_session_id uuid,
  p_owner_id uuid,
  p_question jsonb
) returns jsonb language plpgsql security definer set search_path = '' as $$
declare
  v_result jsonb;
begin
  if p_question is null
     or jsonb_typeof(p_question) <> 'object'
     or nullif(btrim(p_question->>'id'), '') is null
     or char_length(btrim(p_question->>'label')) not between 5 and 300
     or jsonb_typeof(p_question->'options') <> 'array'
     or jsonb_array_length(p_question->'options') <> 3 then
    raise exception 'invalid coauthor question';
  end if;

  update public.coauthor_sessions
  set messages = messages || jsonb_build_array(jsonb_build_object(
        'role', 'assistant', 'content', btrim(p_question->>'label')
      )),
      current_question = p_question,
      updated_at = now()
  where id = p_session_id and owner_id = p_owner_id and status = 'collecting'
  returning current_question into v_result;
  if not found then raise exception 'collecting coauthor session not found'; end if;
  return v_result;
end;
$$;

create or replace function public.stage_four_create_character(
  p_campaign_id uuid,
  p_actor_id uuid,
  p_controller text,
  p_name text,
  p_concept text,
  p_abilities jsonb,
  p_hp integer,
  p_max_hp integer,
  p_defense integer,
  p_proficiency integer,
  p_inventory jsonb,
  p_statuses jsonb,
  p_persona jsonb
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_character_id uuid;
  v_user_id uuid;
begin
  if p_controller not in ('human', 'ai') then raise exception 'invalid character controller'; end if;
  if not exists (
    select 1 from public.campaign_members
    where campaign_id = p_campaign_id and user_id = p_actor_id and active
  ) then raise exception 'active campaign membership is required'; end if;

  if p_controller = 'ai' then
    if not exists (
      select 1 from public.campaigns where id = p_campaign_id and owner_id = p_actor_id
    ) then raise exception 'only the campaign owner can add AI heroes'; end if;
    v_user_id := null;
  else
    v_user_id := p_actor_id;
    perform pg_catalog.pg_advisory_xact_lock(
      pg_catalog.hashtextextended(p_campaign_id::text || ':' || p_actor_id::text, 0)
    );
    if exists (
      select 1 from public.characters
      where campaign_id = p_campaign_id and user_id = p_actor_id
    ) then raise exception 'this player already has a character'; end if;
  end if;

  insert into public.characters(
    campaign_id, user_id, name, concept, controller, abilities, hp, max_hp,
    defense, proficiency, inventory, statuses, persona
  ) values (
    p_campaign_id, v_user_id, btrim(p_name), btrim(p_concept),
    p_controller::public.character_controller, p_abilities, p_hp, p_max_hp,
    p_defense, p_proficiency, p_inventory, p_statuses, p_persona
  ) returning id into v_character_id;
  return v_character_id;
end;
$$;

create or replace function public.dng_schema_version()
returns integer language sql stable security definer set search_path = '' as $$
  select 7;
$$;

revoke all on function public.stage_four_set_coauthor_question(uuid, uuid, jsonb) from public, anon, authenticated;
revoke all on function public.stage_four_create_character(uuid, uuid, text, text, text, jsonb, integer, integer, integer, integer, jsonb, jsonb, jsonb) from public, anon, authenticated;
revoke all on function public.dng_schema_version() from public, anon, authenticated;
grant execute on function public.stage_four_set_coauthor_question(uuid, uuid, jsonb) to service_role;
grant execute on function public.stage_four_create_character(uuid, uuid, text, text, text, jsonb, integer, integer, integer, integer, jsonb, jsonb, jsonb) to service_role;
grant execute on function public.dng_schema_version() to service_role;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0008_required_email_accounts.sql
-- ============================================================================

-- D&G now uses recoverable email/password accounts. Keep the legacy function
-- names so existing auth.users triggers are upgraded in place.

create or replace function public.create_profile_for_allowlisted_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_display_name text;
begin
  if coalesce(new.is_anonymous, false) or nullif(btrim(new.email), '') is null then
    raise exception using
      errcode = '42501',
      message = 'A Dungeons & Ground account requires an email address.';
  end if;

  v_display_name := coalesce(
    nullif(btrim(new.raw_user_meta_data ->> 'display_name'), ''),
    split_part(new.email, '@', 1),
    'Adventurer'
  );

  insert into public.profiles(id, display_name)
  values (new.id, left(v_display_name, 80))
  on conflict (id) do update
    set display_name = excluded.display_name;

  return new;
end;
$$;

revoke all on function public.create_profile_for_allowlisted_user() from public, anon, authenticated;

create or replace function public.enforce_alpha_email_allowlist()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  if coalesce(new.is_anonymous, false) or nullif(btrim(new.email), '') is null then
    raise exception using
      errcode = '42501',
      message = 'A Dungeons & Ground account requires an email address.';
  end if;
  return new;
end;
$$;

revoke all on function public.enforce_alpha_email_allowlist() from public, anon, authenticated;

-- Installations may contain confirmed email users created before the D&G
-- schema. Give every non-anonymous email account a profile without touching
-- existing campaign ownership or display names.
insert into public.profiles(id, display_name)
select
  auth_user.id,
  left(coalesce(
    nullif(btrim(auth_user.raw_user_meta_data ->> 'display_name'), ''),
    split_part(auth_user.email, '@', 1),
    'Adventurer'
  ), 80)
from auth.users auth_user
where not coalesce(auth_user.is_anonymous, false)
  and nullif(btrim(auth_user.email), '') is not null
on conflict (id) do nothing;

create or replace function public.dng_schema_version()
returns integer language sql stable security definer set search_path = '' as $$
  select 8;
$$;

revoke all on function public.dng_schema_version() from public, anon, authenticated;
grant execute on function public.dng_schema_version() to service_role;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0009_profile_management.sql
-- ============================================================================

alter table public.profiles
  add column description text not null default '' check (char_length(description) <= 500),
  add column avatar_path text check (avatar_path is null or avatar_path = id::text || '/avatar'),
  add column updated_at timestamptz not null default now();

-- Avatars are public profile media, but only the owning authenticated user may
-- create, replace, or remove the one deterministic object in their folder.
insert into storage.buckets(id, name, public, file_size_limit, allowed_mime_types)
values (
  'profile-avatars',
  'profile-avatars',
  true,
  2097152,
  array['image/jpeg', 'image/png', 'image/webp']
)
on conflict (id) do update
set public = excluded.public,
    file_size_limit = excluded.file_size_limit,
    allowed_mime_types = excluded.allowed_mime_types;

create policy dng_profile_avatar_insert
on storage.objects for insert to authenticated
with check (
  bucket_id = 'profile-avatars'
  and name = (select auth.uid())::text || '/avatar'
);

create policy dng_profile_avatar_select_own
on storage.objects for select to authenticated
using (
  bucket_id = 'profile-avatars'
  and name = (select auth.uid())::text || '/avatar'
);

create policy dng_profile_avatar_update
on storage.objects for update to authenticated
using (
  bucket_id = 'profile-avatars'
  and name = (select auth.uid())::text || '/avatar'
)
with check (
  bucket_id = 'profile-avatars'
  and name = (select auth.uid())::text || '/avatar'
);

create policy dng_profile_avatar_delete
on storage.objects for delete to authenticated
using (
  bucket_id = 'profile-avatars'
  and name = (select auth.uid())::text || '/avatar'
);

create or replace function public.dng_schema_version()
returns integer language sql stable security definer set search_path = '' as $$
  select 9;
$$;

revoke all on function public.dng_schema_version() from public, anon, authenticated;
grant execute on function public.dng_schema_version() to service_role;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0010_playable_starter_parties.sql
-- ============================================================================

-- Make a newly launched campaign playable immediately: the owner receives a
-- human-controlled starter, and only actionable members hold the round open.

create or replace function public.stage_two_create_campaign(
  p_world_id uuid,
  p_owner_id uuid,
  p_title text
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_world public.worlds%rowtype;
  v_campaign_id uuid;
  v_owner_name text;
begin
  if p_title is null or char_length(btrim(p_title)) not between 3 and 100 then
    raise exception 'campaign title must be between 3 and 100 characters';
  end if;
  select * into v_world from public.worlds
  where id = p_world_id and owner_id = p_owner_id and status = 'confirmed' for share;
  if not found then raise exception 'confirmed world not found'; end if;

  select nullif(btrim(display_name), '') into v_owner_name
  from public.profiles where id = p_owner_id;
  v_owner_name := left(coalesce(v_owner_name, 'Wayfinder'), 80);

  insert into public.campaigns(world_id, owner_id, title, current_scene, next_prompt, status)
  values (p_world_id, p_owner_id, btrim(p_title), coalesce(v_world.opening_scene, v_world.premise), 'What do you do?', 'active')
  returning id into v_campaign_id;
  insert into public.campaign_members(campaign_id, user_id, role)
  values (v_campaign_id, p_owner_id, 'owner');

  insert into public.characters(
    campaign_id, user_id, name, concept, controller, abilities, hp, max_hp,
    defense, proficiency, inventory, statuses, persona
  ) values (
    v_campaign_id,
    p_owner_id,
    v_owner_name,
    left('An adaptable protagonist ready to confront the opening mystery of ' || v_world.title || '.', 600),
    'human'::public.character_controller,
    '{"str":10,"dex":12,"con":12,"int":11,"wis":13,"cha":10}'::jsonb,
    12,
    12,
    12,
    2,
    '["Field kit","Personal keepsake"]'::jsonb,
    '[]'::jsonb,
    jsonb_build_object(
      'voice', 'Defined by the player.',
      'motivation', 'Discover what the opening scene is hiding.',
      'flaw', 'Still learning what this world demands.',
      'bond', 'Protect the party through the first danger.'
    )
  );

  insert into public.rounds(campaign_id, number) values (v_campaign_id, 1);
  return v_campaign_id;
end;
$$;

-- Members who have not created a character cannot submit an intent and must not
-- hold the round open. Only active members with a human-controlled character
-- participate in the readiness barrier.
create or replace function public.stage_two_submit_intent(
  p_round_id uuid,
  p_user_id uuid,
  p_character_id uuid,
  p_action text,
  p_ready boolean default false
) returns jsonb language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_member public.campaign_members%rowtype;
  v_intent_id uuid;
  v_job_id uuid;
begin
  if p_action is null or char_length(btrim(p_action)) not between 1 and 2000 then
    raise exception 'action must be between 1 and 2000 characters';
  end if;
  select * into v_round from public.rounds where id = p_round_id for update;
  if not found or v_round.status <> 'open' then raise exception 'round is not open'; end if;
  select * into v_member from public.campaign_members
  where campaign_id = v_round.campaign_id and user_id = p_user_id and active;
  if not found then raise exception 'active campaign membership not found'; end if;
  if not exists (
    select 1 from public.characters
    where id = p_character_id and campaign_id = v_round.campaign_id
      and user_id = p_user_id and controller = 'human'
  ) then raise exception 'controlled character not found'; end if;

  insert into public.player_intents(round_id, member_id, character_id, action, ready)
  values (p_round_id, v_member.id, p_character_id, btrim(p_action), coalesce(p_ready, false))
  on conflict (round_id, member_id) do update
  set character_id = excluded.character_id, action = excluded.action,
      ready = excluded.ready, updated_at = now()
  returning id into v_intent_id;

  if coalesce(p_ready, false) and not exists (
    select 1
    from public.campaign_members member
    join public.characters character
      on character.campaign_id = member.campaign_id
     and character.user_id = member.user_id
     and character.controller = 'human'::public.character_controller
    where member.campaign_id = v_round.campaign_id
      and member.active
      and not exists (
        select 1 from public.player_intents intent
        where intent.round_id = p_round_id and intent.member_id = member.id and intent.ready
      )
  ) then
    v_job_id := public.enqueue_round_resolution(p_round_id, null);
  end if;
  return jsonb_build_object('intentId', v_intent_id, 'jobId', v_job_id);
end;
$$;

-- Repair campaigns created before starter parties were automatic.
insert into public.characters(
  campaign_id, user_id, name, concept, controller, abilities, hp, max_hp,
  defense, proficiency, inventory, statuses, persona
)
select
  campaign.id,
  campaign.owner_id,
  left(coalesce(nullif(btrim(profile.display_name), ''), 'Wayfinder'), 80),
  left('An adaptable protagonist ready to confront the opening mystery of ' || world.title || '.', 600),
  'human'::public.character_controller,
  '{"str":10,"dex":12,"con":12,"int":11,"wis":13,"cha":10}'::jsonb,
  12,
  12,
  12,
  2,
  '["Field kit","Personal keepsake"]'::jsonb,
  '[]'::jsonb,
  jsonb_build_object(
    'voice', 'Defined by the player.',
    'motivation', 'Discover what the opening scene is hiding.',
    'flaw', 'Still learning what this world demands.',
    'bond', 'Protect the party through the first danger.'
  )
from public.campaigns campaign
join public.worlds world on world.id = campaign.world_id
left join public.profiles profile on profile.id = campaign.owner_id
where campaign.status = 'active'
  and not exists (
    select 1 from public.characters character
    where character.campaign_id = campaign.id
      and character.user_id = campaign.owner_id
  );

create or replace function public.dng_schema_version()
returns integer language sql stable security definer set search_path = '' as $$
  select 10;
$$;

revoke all on function public.dng_schema_version() from public, anon, authenticated;
grant execute on function public.dng_schema_version() to service_role;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0011_universe_companions.sql
-- ============================================================================

-- Persist the AI companions created with each universe and use that party when
-- a campaign starts. This replaces the temporary hard-coded Echo companion.

alter table public.worlds
  add column if not exists starter_companions jsonb not null default '[]'::jsonb;

alter table public.worlds
  drop constraint if exists worlds_starter_companions_array;
alter table public.worlds
  add constraint worlds_starter_companions_array check (
    jsonb_typeof(starter_companions) = 'array'
    and jsonb_array_length(starter_companions) <= 3
  );

-- Ready drafts created before companions were part of WorldStarter inherit
-- three universe-specific people that the coauthor already generated.
update public.coauthor_sessions session
set generated_world = jsonb_set(
  session.generated_world,
  '{companions}',
  coalesce((
    select jsonb_agg(
      jsonb_build_object(
        'name', npc.value->>'name',
        'concept', left(npc.value->>'summary', 600),
        'abilities', case npc.position
          when 1 then '{"str":10,"dex":14,"con":11,"int":13,"wis":12,"cha":10}'::jsonb
          when 2 then '{"str":13,"dex":10,"con":14,"int":10,"wis":12,"cha":11}'::jsonb
          else '{"str":9,"dex":12,"con":11,"int":14,"wis":13,"cha":12}'::jsonb
        end,
        'hp', 10 + npc.position,
        'maxHp', 10 + npc.position,
        'defense', 11 + npc.position,
        'proficiency', 2,
        'inventory', jsonb_build_array('Universe field kit', 'Travel supplies'),
        'persona', jsonb_build_object(
          'voice', left(npc.value->>'summary', 240),
          'motivation', left('Pursue the goal behind: ' || (npc.value->>'summary'), 300),
          'flaw', 'Their personal agenda can complicate the party''s plans.',
          'bond', left('They belong to ' || (session.generated_world->>'title') || ' and choose to stand with the party.', 300)
        )
      ) order by npc.position
    )
    from jsonb_array_elements(coalesce(session.generated_world->'npcs', '[]'::jsonb))
      with ordinality as npc(value, position)
    where npc.position <= 3
  ), '[]'::jsonb),
  true
),
updated_at = now()
where session.generated_world is not null
  and jsonb_typeof(session.generated_world->'npcs') = 'array'
  and coalesce(jsonb_array_length(session.generated_world->'companions'), 0) = 0;

-- Confirmed universes do not retain the original generation JSON. Build their
-- starter companions from the AI-created NPC entities already stored for them.
update public.worlds world
set starter_companions = coalesce((
  select jsonb_agg(
    jsonb_build_object(
      'name', npc.name,
      'concept', left(npc.summary, 600),
      'abilities', case npc.position
        when 1 then '{"str":10,"dex":14,"con":11,"int":13,"wis":12,"cha":10}'::jsonb
        when 2 then '{"str":13,"dex":10,"con":14,"int":10,"wis":12,"cha":11}'::jsonb
        else '{"str":9,"dex":12,"con":11,"int":14,"wis":13,"cha":12}'::jsonb
      end,
      'hp', 10 + npc.position,
      'maxHp', 10 + npc.position,
      'defense', 11 + npc.position,
      'proficiency', 2,
      'inventory', jsonb_build_array('Universe field kit', 'Travel supplies'),
      'persona', jsonb_build_object(
        'voice', left(npc.summary, 240),
        'motivation', left('Pursue the goal behind: ' || npc.summary, 300),
        'flaw', 'Their personal agenda can complicate the party''s plans.',
        'bond', left('They belong to ' || world.title || ' and choose to stand with the party.', 300)
      )
    ) order by npc.position
  )
  from (
    select entity.name, entity.summary,
      (row_number() over (order by entity.created_at, entity.id))::integer as position
    from public.world_entities entity
    where entity.world_id = world.id and entity.kind = 'npc'
    order by entity.created_at, entity.id
    limit 3
  ) npc
), '[]'::jsonb)
where jsonb_array_length(world.starter_companions) = 0;

create or replace function public.stage_two_confirm_world(
  p_session_id uuid,
  p_owner_id uuid
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_session public.coauthor_sessions%rowtype;
  v_world jsonb;
  v_world_id uuid;
  v_entity jsonb;
begin
  select * into v_session from public.coauthor_sessions
  where id = p_session_id and owner_id = p_owner_id for update;
  if not found then raise exception 'coauthor session not found'; end if;
  if v_session.status = 'confirmed' and v_session.confirmed_world_id is not null then
    return v_session.confirmed_world_id;
  end if;
  if v_session.status <> 'ready' or v_session.generated_world is null then
    raise exception 'coauthor session is not ready to confirm';
  end if;
  v_world := v_session.generated_world;
  if coalesce(v_world->>'title', '') = ''
     or coalesce(v_world->>'openingScene', '') = ''
     or jsonb_typeof(v_world->'companions') <> 'array'
     or jsonb_array_length(v_world->'companions') <> 3 then
    raise exception 'generated world is incomplete';
  end if;

  insert into public.worlds(
    owner_id, title, genre, tone, premise, content_boundaries,
    hidden_threat, hook, opening_scene, starter_companions, status
  ) values (
    p_owner_id, v_world->>'title', v_world->>'genre', v_world->>'tone', v_world->>'premise',
    coalesce(v_world->'contentBoundaries', '[]'::jsonb), v_world->>'hiddenThreat',
    v_world->>'hook', v_world->>'openingScene', v_world->'companions', 'confirmed'
  ) returning id into v_world_id;

  v_entity := v_world->'startingLocation';
  insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
  values (v_world_id, 'location', v_entity->>'name', v_entity->>'summary',
    array(select jsonb_array_elements_text(coalesce(v_entity->'tags', '[]'::jsonb))),
    coalesce(v_entity->'secrets', '[]'::jsonb));
  for v_entity in select * from jsonb_array_elements(v_world->'npcs') loop
    insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
    values (v_world_id, 'npc', v_entity->>'name', v_entity->>'summary',
      array(select jsonb_array_elements_text(coalesce(v_entity->'tags', '[]'::jsonb))),
      coalesce(v_entity->'secrets', '[]'::jsonb));
  end loop;
  for v_entity in select * from jsonb_array_elements(v_world->'factions') loop
    insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
    values (v_world_id, 'faction', v_entity->>'name', v_entity->>'summary',
      array(select jsonb_array_elements_text(coalesce(v_entity->'tags', '[]'::jsonb))),
      coalesce(v_entity->'secrets', '[]'::jsonb));
  end loop;
  insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
  values (v_world_id, 'quest', 'Opening Hook', v_world->>'hook', array['opening'], '[]'::jsonb);

  update public.coauthor_sessions
  set status = 'confirmed', confirmed_world_id = v_world_id, updated_at = now()
  where id = p_session_id;
  return v_world_id;
end;
$$;

create or replace function public.stage_two_create_campaign(
  p_world_id uuid,
  p_owner_id uuid,
  p_title text
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_world public.worlds%rowtype;
  v_campaign_id uuid;
  v_owner_name text;
  v_companion jsonb;
begin
  if p_title is null or char_length(btrim(p_title)) not between 3 and 100 then
    raise exception 'campaign title must be between 3 and 100 characters';
  end if;
  select * into v_world from public.worlds
  where id = p_world_id and owner_id = p_owner_id and status = 'confirmed' for share;
  if not found then raise exception 'confirmed world not found'; end if;
  if jsonb_typeof(v_world.starter_companions) <> 'array'
     or jsonb_array_length(v_world.starter_companions) = 0 then
    raise exception 'confirmed world has no AI companions';
  end if;

  select nullif(btrim(display_name), '') into v_owner_name
  from public.profiles where id = p_owner_id;
  v_owner_name := left(coalesce(v_owner_name, 'Wayfinder'), 80);

  insert into public.campaigns(world_id, owner_id, title, current_scene, next_prompt, status)
  values (p_world_id, p_owner_id, btrim(p_title), coalesce(v_world.opening_scene, v_world.premise), 'What do you do?', 'active')
  returning id into v_campaign_id;
  insert into public.campaign_members(campaign_id, user_id, role)
  values (v_campaign_id, p_owner_id, 'owner');

  insert into public.characters(
    campaign_id, user_id, name, concept, controller, abilities, hp, max_hp,
    defense, proficiency, inventory, statuses, persona
  ) values (
    v_campaign_id,
    p_owner_id,
    v_owner_name,
    left('An adaptable protagonist ready to confront the opening mystery of ' || v_world.title || '.', 600),
    'human'::public.character_controller,
    '{"str":10,"dex":12,"con":12,"int":11,"wis":13,"cha":10}'::jsonb,
    12,
    12,
    12,
    2,
    '["Field kit","Personal keepsake"]'::jsonb,
    '[]'::jsonb,
    jsonb_build_object(
      'voice', 'Defined by the player.',
      'motivation', 'Discover what the opening scene is hiding.',
      'flaw', 'Still learning what this world demands.',
      'bond', 'Protect the party through the first danger.'
    )
  );

  for v_companion in select * from jsonb_array_elements(v_world.starter_companions) loop
    insert into public.characters(
      campaign_id, user_id, name, concept, controller, abilities, hp, max_hp,
      defense, proficiency, inventory, statuses, persona
    ) values (
      v_campaign_id,
      null,
      v_companion->>'name',
      v_companion->>'concept',
      'ai'::public.character_controller,
      v_companion->'abilities',
      (v_companion->>'hp')::integer,
      (v_companion->>'maxHp')::integer,
      (v_companion->>'defense')::integer,
      (v_companion->>'proficiency')::integer,
      v_companion->'inventory',
      '[]'::jsonb,
      v_companion->'persona'
    );
  end loop;

  insert into public.rounds(campaign_id, number) values (v_campaign_id, 1);
  return v_campaign_id;
end;
$$;

-- Remove only the exact temporary Echo inserted by schema v10, preserving any
-- independently authored character that happens to share the name.
delete from public.characters character
where character.user_id is null
  and character.controller = 'ai'::public.character_controller
  and character.name = 'Echo'
  and character.inventory = '["Survey kit","Emergency supplies"]'::jsonb
  and character.persona->>'voice' = 'Observant, concise, and quietly curious.'
  and character.persona->>'motivation' = 'Help the party understand this unfamiliar world.'
  and character.concept like 'A persistent AI companion shaped by the %';

-- Existing active campaigns receive the companion roster belonging to their
-- universe. Name matching keeps this migration idempotent around manual heroes.
insert into public.characters(
  campaign_id, user_id, name, concept, controller, abilities, hp, max_hp,
  defense, proficiency, inventory, statuses, persona
)
select
  campaign.id,
  null,
  companion.value->>'name',
  companion.value->>'concept',
  'ai'::public.character_controller,
  companion.value->'abilities',
  (companion.value->>'hp')::integer,
  (companion.value->>'maxHp')::integer,
  (companion.value->>'defense')::integer,
  (companion.value->>'proficiency')::integer,
  companion.value->'inventory',
  '[]'::jsonb,
  companion.value->'persona'
from public.campaigns campaign
join public.worlds world on world.id = campaign.world_id
cross join lateral jsonb_array_elements(world.starter_companions) companion(value)
where campaign.status = 'active'
  and not exists (
    select 1 from public.characters character
    where character.campaign_id = campaign.id
      and character.controller = 'ai'::public.character_controller
      and lower(character.name) = lower(companion.value->>'name')
  );

create or replace function public.dng_schema_version()
returns integer language sql stable security definer set search_path = '' as $$
  select 11;
$$;

revoke all on function public.dng_schema_version() from public, anon, authenticated;
grant execute on function public.dng_schema_version() to service_role;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0012_versioned_srd_rules.sql
-- ============================================================================

-- Introduce a versioned SRD 5.2.1 rules profile without rebuilding campaigns.
-- Existing narrative, rounds, HP, inventory and statuses remain untouched.

alter table public.campaigns
  add column if not exists ruleset_version smallint;

insert into public.audit_entries(
  campaign_id, actor_id, action, entity_type, entity_id, before_state, after_state
)
select
  campaign.id,
  campaign.owner_id,
  'migrate_ruleset',
  'campaign',
  campaign.id,
  jsonb_build_object('rulesetVersion', coalesce(campaign.ruleset_version, 1)),
  jsonb_build_object(
    'rulesetVersion', 2,
    'ruleset', 'SRD 5.2.1 alpha',
    'preserved', jsonb_build_array('rounds', 'history', 'hp', 'inventory', 'statuses')
  )
from public.campaigns campaign
where coalesce(campaign.ruleset_version, 1) < 2;

update public.campaigns
set ruleset_version = 2
where ruleset_version is null or ruleset_version < 2;

alter table public.campaigns
  alter column ruleset_version set default 2,
  alter column ruleset_version set not null;

alter table public.campaigns
  drop constraint if exists campaigns_ruleset_version_supported;
alter table public.campaigns
  add constraint campaigns_ruleset_version_supported check (ruleset_version = 2);

alter table public.characters
  add column if not exists rules_state jsonb;

-- Compatibility is deliberate: migrated heroes retain the old planner hint
-- only until their profile is explicitly edited. Newly-authored profiles use
-- server-owned skill/save lists and set this flag to false.
update public.characters character
set rules_state = jsonb_build_object(
  'version', 2,
  'level', case
    when character.proficiency <= 2 then 1
    when character.proficiency = 3 then 5
    when character.proficiency = 4 then 9
    when character.proficiency = 5 then 13
    else 17
  end,
  'skillProficiencies', '[]'::jsonb,
  'skillExpertise', '[]'::jsonb,
  'savingThrowProficiencies', '[]'::jsonb,
  'temporaryHp', 0,
  'deathSaves', jsonb_build_object('successes', 0, 'failures', 0),
  'exhaustion', 0,
  'damageResistances', '[]'::jsonb,
  'damageVulnerabilities', '[]'::jsonb,
  'damageImmunities', '[]'::jsonb,
  'resources', jsonb_build_object(
    'hitDice', jsonb_build_object('current', 1, 'max', 1, 'recovery', 'longRest')
  ),
  'legacyProficiencyFallback', true
)
where character.rules_state is null;

alter table public.characters
  alter column rules_state set default '{
    "version":2,
    "level":1,
    "skillProficiencies":[],
    "skillExpertise":[],
    "savingThrowProficiencies":[],
    "temporaryHp":0,
    "deathSaves":{"successes":0,"failures":0},
    "exhaustion":0,
    "damageResistances":[],
    "damageVulnerabilities":[],
    "damageImmunities":[],
    "resources":{"hitDice":{"current":1,"max":1,"recovery":"longRest"}},
    "legacyProficiencyFallback":true
  }'::jsonb,
  alter column rules_state set not null;

alter table public.characters
  drop constraint if exists characters_rules_state_v2;
alter table public.characters
  add constraint characters_rules_state_v2 check (
    jsonb_typeof(rules_state) = 'object'
    and rules_state->>'version' = '2'
    and (rules_state->>'level')::integer between 1 and 20
    and jsonb_typeof(rules_state->'skillProficiencies') = 'array'
    and jsonb_typeof(rules_state->'skillExpertise') = 'array'
    and jsonb_typeof(rules_state->'savingThrowProficiencies') = 'array'
    and (rules_state->>'temporaryHp')::integer between 0 and 999
    and jsonb_typeof(rules_state->'deathSaves') = 'object'
    and (rules_state->'deathSaves'->>'successes')::integer between 0 and 3
    and (rules_state->'deathSaves'->>'failures')::integer between 0 and 3
    and (rules_state->>'exhaustion')::integer between 0 and 6
    and jsonb_typeof(rules_state->'damageResistances') = 'array'
    and jsonb_typeof(rules_state->'damageVulnerabilities') = 'array'
    and jsonb_typeof(rules_state->'damageImmunities') = 'array'
    and jsonb_typeof(rules_state->'resources') = 'object'
    and jsonb_typeof(rules_state->'legacyProficiencyFallback') = 'boolean'
  );

-- Older world drafts gain deterministic proficiency data so a newly-started
-- campaign is fully on rules v2 even when its universe predates this migration.
update public.worlds world
set starter_companions = coalesce((
  select jsonb_agg(
    companion.value || case companion.position
      when 1 then '{"skillProficiencies":["acrobatics","perception","stealth","survival"],"skillExpertise":[],"savingThrowProficiencies":["dex","wis"]}'::jsonb
      when 2 then '{"skillProficiencies":["athletics","intimidation","perception","survival"],"skillExpertise":[],"savingThrowProficiencies":["str","con"]}'::jsonb
      else '{"skillProficiencies":["arcana","history","investigation","perception"],"skillExpertise":["investigation"],"savingThrowProficiencies":["int","wis"]}'::jsonb
    end
    order by companion.position
  )
  from jsonb_array_elements(world.starter_companions) with ordinality as companion(value, position)
), '[]'::jsonb)
where jsonb_typeof(world.starter_companions) = 'array';

update public.coauthor_sessions session
set generated_world = jsonb_set(
  session.generated_world,
  '{companions}',
  coalesce((
    select jsonb_agg(
      companion.value || case companion.position
        when 1 then '{"skillProficiencies":["acrobatics","perception","stealth","survival"],"skillExpertise":[],"savingThrowProficiencies":["dex","wis"]}'::jsonb
        when 2 then '{"skillProficiencies":["athletics","intimidation","perception","survival"],"skillExpertise":[],"savingThrowProficiencies":["str","con"]}'::jsonb
        else '{"skillProficiencies":["arcana","history","investigation","perception"],"skillExpertise":["investigation"],"savingThrowProficiencies":["int","wis"]}'::jsonb
      end
      order by companion.position
    )
    from jsonb_array_elements(session.generated_world->'companions') with ordinality as companion(value, position)
  ), '[]'::jsonb),
  true
)
where session.generated_world is not null
  and jsonb_typeof(session.generated_world->'companions') = 'array';

create or replace function public.stage_two_create_campaign(
  p_world_id uuid,
  p_owner_id uuid,
  p_title text
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_world public.worlds%rowtype;
  v_campaign_id uuid;
  v_owner_name text;
  v_companion jsonb;
begin
  if p_title is null or char_length(btrim(p_title)) not between 3 and 100 then
    raise exception 'campaign title must be between 3 and 100 characters';
  end if;
  select * into v_world from public.worlds
  where id = p_world_id and owner_id = p_owner_id and status = 'confirmed' for share;
  if not found then raise exception 'confirmed world not found'; end if;
  if jsonb_typeof(v_world.starter_companions) <> 'array'
     or jsonb_array_length(v_world.starter_companions) = 0 then
    raise exception 'confirmed world has no AI companions';
  end if;

  select nullif(btrim(display_name), '') into v_owner_name
  from public.profiles where id = p_owner_id;
  v_owner_name := left(coalesce(v_owner_name, 'Wayfinder'), 80);

  insert into public.campaigns(world_id, owner_id, title, current_scene, next_prompt, status, ruleset_version)
  values (p_world_id, p_owner_id, btrim(p_title), coalesce(v_world.opening_scene, v_world.premise), 'What do you do?', 'active', 2)
  returning id into v_campaign_id;
  insert into public.campaign_members(campaign_id, user_id, role)
  values (v_campaign_id, p_owner_id, 'owner');

  insert into public.characters(
    campaign_id, user_id, name, concept, controller, abilities, hp, max_hp,
    defense, proficiency, inventory, statuses, persona, rules_state
  ) values (
    v_campaign_id, p_owner_id, v_owner_name,
    left('An adaptable protagonist ready to confront the opening mystery of ' || v_world.title || '.', 600),
    'human'::public.character_controller,
    '{"str":10,"dex":12,"con":12,"int":11,"wis":13,"cha":10}'::jsonb,
    12, 12, 12, 2,
    '["Field kit","Personal keepsake"]'::jsonb,
    '[]'::jsonb,
    jsonb_build_object(
      'voice', 'Defined by the player.',
      'motivation', 'Discover what the opening scene is hiding.',
      'flaw', 'Still learning what this world demands.',
      'bond', 'Protect the party through the first danger.'
    ),
    '{
      "version":2,"level":1,
      "skillProficiencies":["investigation","perception","persuasion","survival"],
      "skillExpertise":[],"savingThrowProficiencies":["dex","wis"],
      "temporaryHp":0,"deathSaves":{"successes":0,"failures":0},"exhaustion":0,
      "damageResistances":[],"damageVulnerabilities":[],"damageImmunities":[],
      "resources":{"hitDice":{"current":1,"max":1,"recovery":"longRest"}},
      "legacyProficiencyFallback":false
    }'::jsonb
  );

  for v_companion in select * from jsonb_array_elements(v_world.starter_companions) loop
    insert into public.characters(
      campaign_id, user_id, name, concept, controller, abilities, hp, max_hp,
      defense, proficiency, inventory, statuses, persona, rules_state
    ) values (
      v_campaign_id, null, v_companion->>'name', v_companion->>'concept',
      'ai'::public.character_controller, v_companion->'abilities',
      (v_companion->>'hp')::integer, (v_companion->>'maxHp')::integer,
      (v_companion->>'defense')::integer, (v_companion->>'proficiency')::integer,
      v_companion->'inventory', '[]'::jsonb, v_companion->'persona',
      jsonb_build_object(
        'version', 2, 'level', 1,
        'skillProficiencies', coalesce(v_companion->'skillProficiencies', '[]'::jsonb),
        'skillExpertise', coalesce(v_companion->'skillExpertise', '[]'::jsonb),
        'savingThrowProficiencies', coalesce(v_companion->'savingThrowProficiencies', '[]'::jsonb),
        'temporaryHp', 0, 'deathSaves', jsonb_build_object('successes', 0, 'failures', 0),
        'exhaustion', 0, 'damageResistances', '[]'::jsonb,
        'damageVulnerabilities', '[]'::jsonb, 'damageImmunities', '[]'::jsonb,
        'resources', jsonb_build_object('hitDice', jsonb_build_object('current', 1, 'max', 1, 'recovery', 'longRest')),
        'legacyProficiencyFallback', false
      )
    );
  end loop;

  insert into public.rounds(campaign_id, number) values (v_campaign_id, 1);
  return v_campaign_id;
end;
$$;

create or replace function public.stage_five_create_character(
  p_campaign_id uuid,
  p_actor_id uuid,
  p_controller text,
  p_name text,
  p_concept text,
  p_abilities jsonb,
  p_hp integer,
  p_max_hp integer,
  p_defense integer,
  p_proficiency integer,
  p_inventory jsonb,
  p_statuses jsonb,
  p_persona jsonb,
  p_rules_state jsonb
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_character_id uuid;
begin
  if coalesce(jsonb_typeof(p_rules_state), '') <> 'object' or p_rules_state->>'version' <> '2' then
    raise exception 'a version 2 rules profile is required';
  end if;
  v_character_id := public.stage_four_create_character(
    p_campaign_id, p_actor_id, p_controller, p_name, p_concept, p_abilities,
    p_hp, p_max_hp, p_defense, p_proficiency, p_inventory, p_statuses, p_persona
  );
  update public.characters set rules_state = p_rules_state where id = v_character_id;
  return v_character_id;
end;
$$;

create or replace function public.apply_srd_character_rules(
  p_round_id uuid,
  p_character_states jsonb
) returns void language plpgsql security definer set search_path = '' as $$
declare
  v_campaign_id uuid;
  v_state jsonb;
  v_character_id uuid;
  v_seen uuid[] := '{}';
begin
  select campaign_id into v_campaign_id from public.rounds where id = p_round_id;
  if not found then raise exception 'round not found'; end if;
  if coalesce(jsonb_typeof(p_character_states), '') <> 'array' then
    raise exception 'character states must be an array';
  end if;
  for v_state in select * from jsonb_array_elements(p_character_states) loop
    v_character_id := (v_state->>'id')::uuid;
    if v_character_id = any(v_seen) then raise exception 'duplicate character state for %', v_character_id; end if;
    v_seen := array_append(v_seen, v_character_id);
    if coalesce(jsonb_typeof(v_state->'rulesState'), '') <> 'object'
       or v_state->'rulesState'->>'version' <> '2' then
      raise exception 'character % has an invalid rules profile', v_character_id;
    end if;
    update public.characters
    set rules_state = v_state->'rulesState'
    where id = v_character_id and campaign_id = v_campaign_id;
    if not found then raise exception 'character % is outside the round campaign', v_character_id; end if;
  end loop;
end;
$$;

create or replace function public.commit_srd_round_resolution(
  p_round_id uuid,
  p_narration text,
  p_next_prompt text,
  p_rolls jsonb,
  p_events jsonb,
  p_character_states jsonb,
  p_memory jsonb,
  p_idempotency_key text
) returns void language plpgsql security definer set search_path = '' as $$
begin
  if exists (select 1 from public.rounds where id = p_round_id and status = 'resolved') then return; end if;
  perform public.commit_round_resolution(
    p_round_id, p_narration, p_next_prompt, p_rolls, p_events,
    p_character_states, p_memory, p_idempotency_key
  );
  perform public.apply_srd_character_rules(p_round_id, p_character_states);
end;
$$;

create or replace function public.commit_claimed_srd_round_resolution(
  p_round_id uuid,
  p_narration text,
  p_next_prompt text,
  p_rolls jsonb,
  p_events jsonb,
  p_character_states jsonb,
  p_memory jsonb,
  p_idempotency_key text,
  p_worker_id text
) returns void language plpgsql security definer set search_path = '' as $$
begin
  perform public.commit_claimed_round_resolution(
    p_round_id, p_narration, p_next_prompt, p_rolls, p_events,
    p_character_states, p_memory, p_idempotency_key, p_worker_id
  );
  perform public.apply_srd_character_rules(p_round_id, p_character_states);
end;
$$;

create or replace function public.dng_schema_version()
returns integer language sql stable security definer set search_path = '' as $$
  select 12;
$$;

revoke all on function public.stage_five_create_character(uuid, uuid, text, text, text, jsonb, integer, integer, integer, integer, jsonb, jsonb, jsonb, jsonb) from public, anon, authenticated;
revoke all on function public.apply_srd_character_rules(uuid, jsonb) from public, anon, authenticated;
revoke all on function public.commit_srd_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text) from public, anon, authenticated;
revoke all on function public.commit_claimed_srd_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text) from public, anon, authenticated;
revoke all on function public.dng_schema_version() from public, anon, authenticated;
grant execute on function public.stage_five_create_character(uuid, uuid, text, text, text, jsonb, integer, integer, integer, integer, jsonb, jsonb, jsonb, jsonb) to service_role;
grant execute on function public.commit_srd_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text) to service_role;
grant execute on function public.commit_claimed_srd_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text) to service_role;
grant execute on function public.dng_schema_version() to service_role;

notify pgrst, 'reload schema';

-- ============================================================================
-- 0013_memory_and_stability.sql
-- ============================================================================

-- Weeks 7–8: durable world projections, PostgreSQL context retrieval, quotas,
-- usage telemetry, atomic summaries, and owner-audited corrections.

alter table public.ai_usage add column if not exists usage_key text;
alter table public.ai_usage add column if not exists provider text not null default 'openrouter';
alter table public.ai_usage add column if not exists request_kind text not null default 'unknown';
update public.ai_usage set usage_key = id::text where usage_key is null;
alter table public.ai_usage alter column usage_key set not null;
create unique index if not exists ai_usage_usage_key_unique on public.ai_usage(usage_key);
create index if not exists ai_usage_user_created_idx on public.ai_usage(user_id, created_at desc);
create index if not exists ai_usage_campaign_created_idx on public.ai_usage(campaign_id, created_at desc);

create table if not exists public.ai_quota_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles(id) on delete cascade,
  campaign_id uuid references public.campaigns(id) on delete cascade,
  request_kind text not null check (char_length(request_kind) between 1 and 80),
  created_at timestamptz not null default now()
);
create index if not exists ai_quota_events_user_created_idx on public.ai_quota_events(user_id, created_at desc);
create index if not exists ai_quota_events_campaign_created_idx on public.ai_quota_events(campaign_id, created_at desc);
alter table public.ai_quota_events enable row level security;
drop policy if exists quota_events_self_read on public.ai_quota_events;
create policy quota_events_self_read on public.ai_quota_events for select using (user_id = auth.uid());

create table if not exists public.scene_states (
  campaign_id uuid primary key references public.campaigns(id) on delete cascade,
  location_id uuid references public.world_entities(id) on delete set null,
  summary text not null check (char_length(summary) between 1 and 6000),
  active_entity_ids uuid[] not null default '{}',
  tags text[] not null default '{}',
  through_round integer not null default 0 check (through_round >= 0),
  updated_at timestamptz not null default now()
);

create table if not exists public.quest_states (
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  quest_entity_id uuid not null references public.world_entities(id) on delete cascade,
  status text not null default 'hidden' check (status in ('hidden', 'active', 'completed', 'failed')),
  summary text not null check (char_length(summary) between 1 and 1200),
  tags text[] not null default '{}',
  through_round integer not null default 0 check (through_round >= 0),
  updated_at timestamptz not null default now(),
  primary key (campaign_id, quest_entity_id)
);
create index if not exists quest_states_campaign_status_idx on public.quest_states(campaign_id, status, updated_at desc);

create or replace function public.stage_six_ensure_opening_quest()
returns trigger language plpgsql security definer set search_path = '' as $$
begin
  if nullif(btrim(new.hook), '') is not null and not exists (
    select 1 from public.world_entities where world_id = new.id and kind = 'quest'
  ) then
    insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
    values (new.id, 'quest', 'Opening objective', left(btrim(new.hook), 1200), array['opening', 'active'], '[]'::jsonb);
  end if;
  return new;
end;
$$;

drop trigger if exists stage_six_world_opening_quest on public.worlds;
create trigger stage_six_world_opening_quest
after insert or update of hook on public.worlds
for each row execute function public.stage_six_ensure_opening_quest();

insert into public.world_entities(world_id, kind, name, summary, tags, secrets)
select world.id, 'quest', 'Opening objective', left(btrim(world.hook), 1200), array['opening', 'active'], '[]'::jsonb
from public.worlds world
where nullif(btrim(world.hook), '') is not null
  and not exists (select 1 from public.world_entities entity where entity.world_id = world.id and entity.kind = 'quest');

alter table public.scene_states enable row level security;
alter table public.quest_states enable row level security;
drop policy if exists scene_states_member_read on public.scene_states;
drop policy if exists quest_states_member_read on public.quest_states;
create policy scene_states_member_read on public.scene_states for select using (
  public.is_campaign_member(campaign_id) or public.is_campaign_owner(campaign_id)
);
create policy quest_states_member_read on public.quest_states for select using (
  public.is_campaign_member(campaign_id) or public.is_campaign_owner(campaign_id)
);

insert into public.scene_states(campaign_id, summary)
select id, current_scene from public.campaigns
on conflict (campaign_id) do nothing;

insert into public.quest_states(campaign_id, quest_entity_id, status, summary, tags)
select campaign.id, entity.id, case when 'opening' = any(entity.tags) then 'active' else 'hidden' end, entity.summary, entity.tags
from public.campaigns campaign
join public.world_entities entity on entity.world_id = campaign.world_id and entity.kind = 'quest'
on conflict (campaign_id, quest_entity_id) do nothing;

create or replace function public.stage_six_initialize_campaign_state()
returns trigger language plpgsql security definer set search_path = '' as $$
begin
  insert into public.scene_states(campaign_id, summary)
  values (new.id, new.current_scene)
  on conflict (campaign_id) do nothing;

  insert into public.quest_states(campaign_id, quest_entity_id, status, summary, tags)
  select new.id, entity.id, case when 'opening' = any(entity.tags) then 'active' else 'hidden' end, entity.summary, entity.tags
  from public.world_entities entity
  where entity.world_id = new.world_id and entity.kind = 'quest'
  on conflict (campaign_id, quest_entity_id) do nothing;
  return new;
end;
$$;

drop trigger if exists stage_six_campaign_state on public.campaigns;
create trigger stage_six_campaign_state
after insert on public.campaigns
for each row execute function public.stage_six_initialize_campaign_state();

create or replace function public.stage_six_consume_ai_quota(
  p_user_id uuid,
  p_campaign_id uuid,
  p_request_kind text
) returns jsonb language plpgsql security definer set search_path = '' as $$
declare
  v_user_minute integer;
  v_user_day integer;
  v_campaign_day integer := 0;
  v_tokens_day bigint;
begin
  if p_user_id is null or not exists (select 1 from public.profiles where id = p_user_id) then
    raise exception 'AI quota requires a valid user';
  end if;
  if p_campaign_id is not null and not exists (
    select 1 from public.campaigns where id = p_campaign_id
      and (owner_id = p_user_id or exists (
        select 1 from public.campaign_members where campaign_id = p_campaign_id and user_id = p_user_id and active
      ))
  ) then
    raise exception 'AI quota campaign access denied';
  end if;
  if p_request_kind is null or char_length(btrim(p_request_kind)) not between 1 and 80 then
    raise exception 'AI request kind is required';
  end if;

  perform pg_catalog.pg_advisory_xact_lock(pg_catalog.hashtextextended('ai-quota:' || p_user_id::text, 0));
  select count(*) into v_user_minute from public.ai_quota_events
  where user_id = p_user_id and created_at >= now() - interval '1 minute';
  select count(*) into v_user_day from public.ai_quota_events
  where user_id = p_user_id and created_at >= date_trunc('day', now());
  select coalesce(sum(input_tokens + output_tokens), 0) into v_tokens_day from public.ai_usage
  where user_id = p_user_id and created_at >= date_trunc('day', now());
  if p_campaign_id is not null then
    select count(*) into v_campaign_day from public.ai_quota_events
    where campaign_id = p_campaign_id and created_at >= date_trunc('day', now());
  end if;

  if v_user_minute >= 10 then raise exception 'AI rate limit reached; try again in a minute'; end if;
  if v_user_day >= 60 then raise exception 'daily user AI quota reached'; end if;
  if p_campaign_id is not null and v_campaign_day >= 40 then raise exception 'daily campaign AI quota reached'; end if;
  if v_tokens_day >= 250000 then raise exception 'daily AI token quota reached'; end if;

  insert into public.ai_quota_events(user_id, campaign_id, request_kind)
  values (p_user_id, p_campaign_id, btrim(p_request_kind));
  return jsonb_build_object(
    'userRemaining', 59 - v_user_day,
    'campaignRemaining', case when p_campaign_id is null then null else 39 - v_campaign_day end,
    'tokenRemaining', greatest(0, 250000 - v_tokens_day)
  );
end;
$$;

create or replace function public.enqueue_round_resolution(
  p_round_id uuid,
  p_forced_by uuid default null
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_job_id uuid;
  v_owner_id uuid;
begin
  select * into v_round from public.rounds where id = p_round_id for update;
  if not found then raise exception 'round not found'; end if;
  select owner_id into v_owner_id from public.campaigns where id = v_round.campaign_id;

  select id into v_job_id from public.ai_jobs
  where job_type = 'resolve-round' and entity_id = p_round_id;
  if v_round.status in ('queued', 'resolving', 'resolved') then
    if v_job_id is null and v_round.status <> 'resolved' then raise exception 'round status and job outbox are inconsistent'; end if;
    return v_job_id;
  end if;
  if v_round.status <> 'open' then raise exception 'round cannot be queued from status %', v_round.status; end if;

  if p_forced_by is not null then
    if p_forced_by <> v_owner_id then raise exception 'only the campaign owner can force a round'; end if;
  elsif exists (
    select 1 from public.campaign_members member
    join public.characters character on character.campaign_id = member.campaign_id
      and character.user_id = member.user_id and character.controller = 'human'::public.character_controller
    where member.campaign_id = v_round.campaign_id and member.active
      and not exists (
        select 1 from public.player_intents intent
        where intent.round_id = p_round_id and intent.member_id = member.id and intent.ready
      )
  ) then
    raise exception 'not all active players are ready';
  end if;

  perform public.stage_six_consume_ai_quota(v_owner_id, v_round.campaign_id, 'resolve-round');
  v_job_id := gen_random_uuid();
  insert into public.ai_jobs(id, job_type, entity_id, idempotency_key, status)
  values (v_job_id, 'resolve-round', p_round_id, v_job_id::text, 'queued');
  update public.rounds set status = 'queued', queued_at = now(), forced_by = p_forced_by, error = null
  where id = p_round_id;
  return v_job_id;
end;
$$;

create or replace function public.stage_two_enqueue_world_generation(
  p_session_id uuid,
  p_owner_id uuid
) returns uuid language plpgsql security definer set search_path = '' as $$
declare
  v_session public.coauthor_sessions%rowtype;
  v_job public.ai_jobs%rowtype;
  v_existing boolean := false;
begin
  select * into v_session from public.coauthor_sessions
  where id = p_session_id and owner_id = p_owner_id for update;
  if not found then raise exception 'coauthor session not found'; end if;
  if v_session.status = 'confirmed' then raise exception 'coauthor session is already confirmed'; end if;
  if jsonb_array_length(v_session.messages) < 1 then raise exception 'at least one message is required'; end if;

  select * into v_job from public.ai_jobs
  where job_type = 'generate-world' and entity_id = p_session_id for update;
  v_existing := found;
  if v_existing and v_job.status in ('queued', 'running') then return v_job.id; end if;

  perform public.stage_six_consume_ai_quota(p_owner_id, null, 'generate-world');
  if v_existing then
    update public.ai_jobs set status = 'queued', attempts = 0, claimed_by = null,
      lease_expires_at = null, available_at = now(), error = null, updated_at = now()
    where id = v_job.id;
  else
    v_job.id := gen_random_uuid();
    insert into public.ai_jobs(id, job_type, entity_id, idempotency_key, status)
    values (v_job.id, 'generate-world', p_session_id, v_job.id::text, 'queued');
  end if;
  update public.coauthor_sessions set status = 'generating', generated_world = null, updated_at = now()
  where id = p_session_id;
  return v_job.id;
end;
$$;

create or replace function public.stage_six_retrieve_context(
  p_campaign_id uuid,
  p_search_text text,
  p_limit integer default 8
) returns jsonb language plpgsql stable security definer set search_path = '' as $$
declare
  v_world_id uuid;
  v_query tsquery;
  v_limit integer := greatest(1, least(coalesce(p_limit, 8), 12));
  v_entities jsonb;
  v_entity_ids uuid[];
begin
  select world_id into v_world_id from public.campaigns where id = p_campaign_id;
  if not found then raise exception 'campaign not found'; end if;
  v_query := plainto_tsquery('english', left(coalesce(p_search_text, ''), 4000));

  select coalesce(jsonb_agg(item.payload order by item.relevance desc, item.name), '[]'::jsonb),
         coalesce(array_agg(item.id order by item.relevance desc, item.name), '{}')
  into v_entities, v_entity_ids
  from (
    select entity.id, entity.name,
      jsonb_build_object('id', entity.id, 'kind', entity.kind, 'name', entity.name, 'summary', entity.summary, 'tags', entity.tags) as payload,
      case when numnode(v_query) > 0 then ts_rank_cd(entity.search_document, v_query) else 0 end
        + case when exists (select 1 from unnest(entity.tags) tag where position(lower(tag) in lower(coalesce(p_search_text, ''))) > 0) then 2 else 0 end
        + case when quest.status = 'active' then 3 else 0 end
        + case when scene.location_id = entity.id or entity.id = any(scene.active_entity_ids) then 4 else 0 end as relevance
    from public.world_entities entity
    left join public.quest_states quest on quest.campaign_id = p_campaign_id and quest.quest_entity_id = entity.id
    left join public.scene_states scene on scene.campaign_id = p_campaign_id
    where entity.world_id = v_world_id
    order by relevance desc, entity.created_at
    limit v_limit
  ) item;

  return jsonb_build_object(
    'entities', v_entities,
    'memories', coalesce((
      select jsonb_agg(memory_item.payload order by memory_item.relevance desc, memory_item.created_at desc)
      from (
        select memory.created_at,
          jsonb_build_object('summary', memory.summary, 'importance', memory.importance, 'tags', memory.tags, 'entityIds', memory.entity_ids) as payload,
          memory.importance
            + case when numnode(v_query) > 0 then ts_rank_cd(memory.search_document, v_query) * 10 else 0 end
            + case when memory.entity_ids && v_entity_ids then 5 else 0 end
            + case when exists (select 1 from unnest(memory.tags) tag where position(lower(tag) in lower(coalesce(p_search_text, ''))) > 0) then 4 else 0 end as relevance
        from public.memories memory
        where memory.campaign_id = p_campaign_id
        order by relevance desc, memory.created_at desc
        limit v_limit
      ) memory_item
    ), '[]'::jsonb),
    'relationships', coalesce((
      select jsonb_agg(jsonb_build_object(
        'sourceEntityId', item.source_entity_id,
        'targetEntityId', item.target_entity_id,
        'score', item.score,
        'notes', item.notes
      ) order by abs(item.score) desc)
      from (
        select relationship.* from public.relationships relationship
        where relationship.campaign_id = p_campaign_id
          and (
            relationship.source_entity_id = any(v_entity_ids)
            or relationship.target_entity_id = any(v_entity_ids)
            or exists (
              select 1 from public.characters character
              where character.campaign_id = p_campaign_id
                and character.id in (relationship.source_entity_id, relationship.target_entity_id)
            )
          )
        order by abs(relationship.score) desc
        limit 20
      ) item
    ), '[]'::jsonb),
    'activeGoals', coalesce((
      select jsonb_agg(jsonb_build_object(
        'questEntityId', item.quest_entity_id,
        'name', item.name,
        'status', item.status,
        'summary', item.summary,
        'tags', item.tags
      ) order by item.updated_at desc)
      from (
        select quest.*, entity.name from public.quest_states quest
        join public.world_entities entity on entity.id = quest.quest_entity_id
        where quest.campaign_id = p_campaign_id and quest.status = 'active'
        order by quest.updated_at desc
        limit 8
      ) item
    ), '[]'::jsonb)
  );
end;
$$;

create or replace function public.stage_six_apply_round_projections(
  p_round_id uuid,
  p_events jsonb
) returns void language plpgsql security definer set search_path = '' as $$
declare
  v_round public.rounds%rowtype;
  v_event jsonb;
  v_actor uuid;
  v_target uuid;
  v_before jsonb;
  v_after jsonb;
  v_scene_projected boolean := false;
begin
  select * into v_round from public.rounds where id = p_round_id;
  if not found then raise exception 'round not found'; end if;

  for v_event in select * from jsonb_array_elements(coalesce(p_events, '[]'::jsonb)) loop
    v_actor := nullif(v_event->>'actorId', '')::uuid;
    v_target := nullif(v_event->>'targetId', '')::uuid;

    if v_event->>'type' = 'relationship' then
      if v_actor is null or v_target is null or v_actor = v_target
         or (v_event->>'value')::integer not between -20 and 20 then
        raise exception 'invalid relationship projection';
      end if;
      select to_jsonb(relationship) into v_before from public.relationships relationship
      where campaign_id = v_round.campaign_id and source_entity_id = v_actor and target_entity_id = v_target;
      insert into public.relationships(campaign_id, source_entity_id, target_entity_id, score, notes)
      values (v_round.campaign_id, v_actor, v_target, (v_event->>'value')::integer, v_event->>'description')
      on conflict (campaign_id, source_entity_id, target_entity_id) do update
      set score = greatest(-100, least(100, public.relationships.score + excluded.score)), notes = excluded.notes;
      select to_jsonb(relationship) into v_after from public.relationships relationship
      where campaign_id = v_round.campaign_id and source_entity_id = v_actor and target_entity_id = v_target;
      insert into public.audit_entries(campaign_id, action, entity_type, entity_id, before_state, after_state)
      values (v_round.campaign_id, 'project_relationship', 'relationship', v_target, v_before, v_after);
    elsif v_event->>'type' = 'quest' then
      if v_target is null or v_event->>'item' not in ('hidden', 'active', 'completed', 'failed') then
        raise exception 'invalid quest projection';
      end if;
      select to_jsonb(quest) into v_before from public.quest_states quest
      where campaign_id = v_round.campaign_id and quest_entity_id = v_target;
      update public.quest_states set status = v_event->>'item', summary = v_event->>'description',
        through_round = v_round.number, updated_at = now()
      where campaign_id = v_round.campaign_id and quest_entity_id = v_target;
      if not found then raise exception 'quest is outside the round campaign'; end if;
      select to_jsonb(quest) into v_after from public.quest_states quest
      where campaign_id = v_round.campaign_id and quest_entity_id = v_target;
      insert into public.audit_entries(campaign_id, action, entity_type, entity_id, before_state, after_state)
      values (v_round.campaign_id, 'project_quest', 'quest', v_target, v_before, v_after);
    elsif v_event->>'type' = 'narrative' and v_event->>'item' = 'scene' then
      v_scene_projected := true;
      if v_target is not null and not exists (
        select 1 from public.world_entities entity join public.campaigns campaign on campaign.world_id = entity.world_id
        where campaign.id = v_round.campaign_id and entity.id = v_target and entity.kind = 'location'
      ) then raise exception 'scene location is outside the campaign world'; end if;
      insert into public.scene_states(campaign_id, location_id, summary, active_entity_ids, through_round, updated_at)
      values (
        v_round.campaign_id, v_target, v_event->>'description',
        array_remove(array[v_actor, v_target], null), v_round.number, now()
      )
      on conflict (campaign_id) do update set location_id = excluded.location_id,
        summary = excluded.summary, active_entity_ids = excluded.active_entity_ids,
        through_round = excluded.through_round, updated_at = excluded.updated_at;
      update public.campaigns set current_scene = v_event->>'description', updated_at = now()
      where id = v_round.campaign_id;
    end if;
  end loop;

  if not v_scene_projected and nullif(btrim(v_round.narration), '') is not null then
    insert into public.scene_states(campaign_id, summary, through_round, updated_at)
    values (v_round.campaign_id, v_round.narration, v_round.number, now())
    on conflict (campaign_id) do update set summary = excluded.summary,
      through_round = excluded.through_round, updated_at = excluded.updated_at;
  end if;
end;
$$;

create or replace function public.commit_stage_six_round_resolution(
  p_round_id uuid,
  p_narration text,
  p_next_prompt text,
  p_rolls jsonb,
  p_events jsonb,
  p_character_states jsonb,
  p_memory jsonb,
  p_story_summary text,
  p_idempotency_key text
) returns void language plpgsql security definer set search_path = '' as $$
declare v_round public.rounds%rowtype;
begin
  select * into v_round from public.rounds where id = p_round_id for update;
  if not found then raise exception 'round not found'; end if;
  if v_round.status = 'resolved' then return; end if;
  perform public.commit_srd_round_resolution(
    p_round_id, p_narration, p_next_prompt, p_rolls, p_events,
    p_character_states, p_memory, p_idempotency_key
  );
  perform public.stage_six_apply_round_projections(p_round_id, p_events);
  if p_story_summary is not null then
    if v_round.number % 3 <> 0 or char_length(btrim(p_story_summary)) not between 20 and 6000 then
      raise exception 'invalid scheduled story summary';
    end if;
    insert into public.story_summaries(campaign_id, through_round, summary)
    values (v_round.campaign_id, v_round.number, btrim(p_story_summary))
    on conflict (campaign_id, through_round) do update set summary = excluded.summary;
  end if;
end;
$$;

create or replace function public.commit_claimed_stage_six_round_resolution(
  p_round_id uuid,
  p_narration text,
  p_next_prompt text,
  p_rolls jsonb,
  p_events jsonb,
  p_character_states jsonb,
  p_memory jsonb,
  p_story_summary text,
  p_idempotency_key text,
  p_worker_id text
) returns void language plpgsql security definer set search_path = '' as $$
declare v_round public.rounds%rowtype;
begin
  select * into v_round from public.rounds where id = p_round_id for update;
  if not found then raise exception 'round not found'; end if;
  if v_round.status = 'resolved' then return; end if;
  perform public.commit_claimed_srd_round_resolution(
    p_round_id, p_narration, p_next_prompt, p_rolls, p_events,
    p_character_states, p_memory, p_idempotency_key, p_worker_id
  );
  perform public.stage_six_apply_round_projections(p_round_id, p_events);
  if p_story_summary is not null then
    if v_round.number % 3 <> 0 or char_length(btrim(p_story_summary)) not between 20 and 6000 then
      raise exception 'invalid scheduled story summary';
    end if;
    insert into public.story_summaries(campaign_id, through_round, summary)
    values (v_round.campaign_id, v_round.number, btrim(p_story_summary))
    on conflict (campaign_id, through_round) do update set summary = excluded.summary;
  end if;
end;
$$;

create or replace function public.stage_six_adjust_character_state(
  p_campaign_id uuid,
  p_owner_id uuid,
  p_character_id uuid,
  p_patch jsonb,
  p_reason text
) returns jsonb language plpgsql security definer set search_path = '' as $$
declare
  v_character public.characters%rowtype;
  v_before jsonb;
  v_after jsonb;
  v_hp integer;
begin
  if not exists (select 1 from public.campaigns where id = p_campaign_id and owner_id = p_owner_id) then
    raise exception 'only the campaign owner can adjust state';
  end if;
  if p_reason is null or char_length(btrim(p_reason)) not between 3 and 500 then raise exception 'an audit reason is required'; end if;
  if coalesce(jsonb_typeof(p_patch), '') <> 'object' or p_patch - array['hp', 'inventory', 'statuses']::text[] <> '{}'::jsonb then
    raise exception 'only hp, inventory, and statuses may be adjusted';
  end if;
  select * into v_character from public.characters
  where id = p_character_id and campaign_id = p_campaign_id for update;
  if not found then raise exception 'character not found'; end if;
  v_before := jsonb_build_object('hp', v_character.hp, 'inventory', v_character.inventory, 'statuses', v_character.statuses);
  v_hp := coalesce((p_patch->>'hp')::integer, v_character.hp);
  if v_hp < 0 or v_hp > v_character.max_hp then raise exception 'hp must be between zero and max hp'; end if;
  if p_patch ? 'inventory' and jsonb_typeof(p_patch->'inventory') <> 'array' then raise exception 'inventory must be an array'; end if;
  if p_patch ? 'statuses' and jsonb_typeof(p_patch->'statuses') <> 'array' then raise exception 'statuses must be an array'; end if;
  update public.characters set hp = v_hp,
    inventory = coalesce(p_patch->'inventory', inventory),
    statuses = coalesce(p_patch->'statuses', statuses)
  where id = p_character_id
  returning jsonb_build_object('hp', hp, 'inventory', inventory, 'statuses', statuses) into v_after;
  insert into public.audit_entries(campaign_id, actor_id, action, entity_type, entity_id, before_state, after_state)
  values (p_campaign_id, p_owner_id, 'manual_character_adjustment: ' || btrim(p_reason), 'character', p_character_id, v_before, v_after);
  return v_after;
end;
$$;

create or replace function public.dng_schema_version()
returns integer language sql stable security definer set search_path = '' as $$
  select 13;
$$;

revoke all on function public.stage_six_consume_ai_quota(uuid, uuid, text) from public, anon, authenticated;
revoke all on function public.stage_six_retrieve_context(uuid, text, integer) from public, anon, authenticated;
revoke all on function public.stage_six_apply_round_projections(uuid, jsonb) from public, anon, authenticated;
revoke all on function public.commit_stage_six_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text) from public, anon, authenticated;
revoke all on function public.commit_claimed_stage_six_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text, text) from public, anon, authenticated;
revoke all on function public.stage_six_adjust_character_state(uuid, uuid, uuid, jsonb, text) from public, anon, authenticated;
revoke all on function public.dng_schema_version() from public, anon, authenticated;
grant execute on function public.stage_six_consume_ai_quota(uuid, uuid, text) to service_role;
grant execute on function public.stage_six_retrieve_context(uuid, text, integer) to service_role;
grant execute on function public.commit_stage_six_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text) to service_role;
grant execute on function public.commit_claimed_stage_six_round_resolution(uuid, text, text, jsonb, jsonb, jsonb, jsonb, text, text, text) to service_role;
grant execute on function public.stage_six_adjust_character_state(uuid, uuid, uuid, jsonb, text) to service_role;
grant execute on function public.dng_schema_version() to service_role;

notify pgrst, 'reload schema';


do $$
begin
  if to_regclass('public.profiles') is null then
    raise exception 'D&G bootstrap verification failed: public.profiles is missing';
  end if;
  if to_regclass('public.ai_jobs') is null then
    raise exception 'D&G bootstrap verification failed: public.ai_jobs is missing';
  end if;
  if to_regprocedure('public.claim_ai_job(text)') is null then
    raise exception 'D&G bootstrap verification failed: public.claim_ai_job(text) is missing';
  end if;
  if to_regprocedure('public.stage_two_create_campaign(uuid,uuid,text)') is null then
    raise exception 'D&G bootstrap verification failed: stage-two RPCs are missing';
  end if;
  if to_regprocedure('public.stage_two_retry_failed_round(uuid,uuid)') is null then
    raise exception 'D&G bootstrap verification failed: failed-round recovery RPC is missing';
  end if;
  if to_regprocedure('public.dng_schema_version()') is null then
    raise exception 'D&G bootstrap verification failed: schema version RPC is missing';
  end if;
  if to_regprocedure('public.stage_four_create_character(uuid,uuid,text,text,text,jsonb,integer,integer,integer,integer,jsonb,jsonb,jsonb)') is null then
    raise exception 'D&G bootstrap verification failed: character creation RPC is missing';
  end if;
  if public.dng_schema_version() <> 13 then
    raise exception 'D&G bootstrap verification failed: unexpected schema version';
  end if;
end;
$$;

notify pgrst, 'reload schema';
commit;

select
  'Dungeons & Ground database is ready' as result,
  to_regclass('public.profiles') as profiles,
  to_regclass('public.ai_jobs') as ai_jobs,
  to_regprocedure('public.claim_ai_job(text)') as worker_rpc;