-- commslayer_agent_map: maps Commslayer's numeric agent_id to this app's
-- agent_id. Confirmed in conversation: no such mapping exists anywhere
-- in legacy — the legacy fetch (Code.js fetchCommslayerMetrics) returns
-- metrics keyed 'cs_' + a.agent_id, and the client merge
-- (fetchCommslayerData in JavaScript.html) copies those keys straight
-- into state.reports[date].metrics verbatim, with zero reconciliation
-- to state.agents[].id anywhere in the codebase (grepped for
-- commslayerName/commslayerAgentId/cs_ across all of JavaScript.html —
-- zero hits). So this table is new, not a port of anything.
--
-- Depends on 0022 (assign_agent_ids) having already run — seeds
-- reference agent_id values that migration creates.
--
-- RLS reuses the 'performance' feature_key from 0021 (view + write both
-- gated on has_permission('performance', ...)) rather than introducing a
-- new gate — this table is config data for the Commslayer fetch route,
-- same domain as the tables 0021 already covers. No self-view carve-out
-- (unlike 0021's four tables): this is a lookup/config table, not a
-- personal record.
--
-- Seed is the live crosswalk given in conversation. ai_agent_1877
-- (Commslayer's own AI agent, not a person) is deliberately excluded.
-- Edwin's row maps onto his existing 'agent_042', not a new 'edwin'
-- slug — 0022 intentionally left his agent_id unchanged.

-- ============================================================
-- 1. commslayer_agent_map
-- ============================================================

create table if not exists public.commslayer_agent_map (
  agent_id             text primary key,
  commslayer_agent_id  text not null unique,
  updated_at           timestamptz not null default now()
);

alter table public.commslayer_agent_map enable row level security;

create policy "commslayer_agent_map_select_performance_view"
on public.commslayer_agent_map
for select
to authenticated
using (public.has_permission('performance'));

create policy "commslayer_agent_map_insert"
on public.commslayer_agent_map
for insert
to authenticated
with check (public.has_permission('performance', need_write => true));

create policy "commslayer_agent_map_update"
on public.commslayer_agent_map
for update
to authenticated
using (public.has_permission('performance', need_write => true))
with check (public.has_permission('performance', need_write => true));

create policy "commslayer_agent_map_delete"
on public.commslayer_agent_map
for delete
to authenticated
using (public.has_permission('performance', need_write => true));

-- ============================================================
-- 2. Seed the live crosswalk
-- ============================================================

insert into public.commslayer_agent_map (agent_id, commslayer_agent_id) values
  ('jurina',    '8769'),
  ('kenn',      '8708'),
  ('dominic',   '4423'),
  ('berry',     '6131'),
  ('rubyrose',  '9198'),
  ('quinty',    '8689'),
  ('kate',      '8773'),
  ('mayvel',    '8688'),
  ('andrew',    '4422'),
  ('agent_042', '4421'), -- Edwin — existing agent_id, not a new 'edwin' slug
  ('bjorn',     '4136'),
  ('mon',       '8687')
on conflict (agent_id) do update set commslayer_agent_id = excluded.commslayer_agent_id;

-- ============================================================
-- 3. Self-verification guard
-- ============================================================

do $$
declare
  row_count integer;
begin
  select count(*) into row_count from public.commslayer_agent_map;
  if row_count <> 12 then
    raise exception 'Aborting 0023: expected exactly 12 commslayer_agent_map rows, found %', row_count;
  end if;
end $$;