ALTER TABLE agent_bots ADD COLUMN IF NOT EXISTS captain_assistant_id BIGINT;

-- This data repair and its unique indexes must see a stable snapshot. These
-- locks block concurrent writes while allowing reads, so run this migration
-- in a maintenance window sized for the deduplication.
LOCK TABLE agent_bots, agent_bot_inboxes, captain_inboxes, conversations,
  agent_bot_presence_events, bot_rules, bot_trigger_configs
  IN SHARE ROW EXCLUSIVE MODE;

UPDATE agent_bots
SET captain_assistant_id = (config ->> 'assistant_id')::BIGINT
WHERE bot_type = 'captain'
  AND captain_assistant_id IS NULL
  AND config ->> 'assistant_id' ~ '^[0-9]+$';

UPDATE conversations conversation
SET assignee_agent_bot_id = canonical.keep_id
FROM (
  SELECT account_id, captain_assistant_id, MIN(id) AS keep_id,
         ARRAY_AGG(id) AS duplicate_ids
  FROM agent_bots
  WHERE captain_assistant_id IS NOT NULL
  GROUP BY account_id, captain_assistant_id
  HAVING COUNT(*) > 1
) canonical
WHERE conversation.assignee_agent_bot_id = ANY(canonical.duplicate_ids)
  AND conversation.assignee_agent_bot_id <> canonical.keep_id;

UPDATE agent_bot_inboxes binding
SET agent_bot_id = canonical.keep_id
FROM (
  SELECT account_id, captain_assistant_id, MIN(id) AS keep_id,
         ARRAY_AGG(id) AS duplicate_ids
  FROM agent_bots
  WHERE captain_assistant_id IS NOT NULL
  GROUP BY account_id, captain_assistant_id
  HAVING COUNT(*) > 1
) canonical
WHERE binding.agent_bot_id = ANY(canonical.duplicate_ids)
  AND binding.agent_bot_id <> canonical.keep_id;

UPDATE agent_bot_presence_events event
SET agent_bot_id = canonical.keep_id
FROM (
  SELECT account_id, captain_assistant_id, MIN(id) AS keep_id,
         ARRAY_AGG(id) AS duplicate_ids
  FROM agent_bots
  WHERE captain_assistant_id IS NOT NULL
  GROUP BY account_id, captain_assistant_id
  HAVING COUNT(*) > 1
) canonical
WHERE event.agent_bot_id = ANY(canonical.duplicate_ids)
  AND event.agent_bot_id <> canonical.keep_id;

UPDATE bot_rules rule
SET agent_bot_id = canonical.keep_id
FROM (
  SELECT account_id, captain_assistant_id, MIN(id) AS keep_id,
         ARRAY_AGG(id) AS duplicate_ids
  FROM agent_bots
  WHERE captain_assistant_id IS NOT NULL
  GROUP BY account_id, captain_assistant_id
  HAVING COUNT(*) > 1
) canonical
WHERE rule.agent_bot_id = ANY(canonical.duplicate_ids)
  AND rule.agent_bot_id <> canonical.keep_id;

UPDATE bot_trigger_configs config
SET agent_bot_id = canonical.keep_id
FROM (
  SELECT account_id, captain_assistant_id, MIN(id) AS keep_id,
         ARRAY_AGG(id) AS duplicate_ids
  FROM agent_bots
  WHERE captain_assistant_id IS NOT NULL
  GROUP BY account_id, captain_assistant_id
  HAVING COUNT(*) > 1
) canonical
WHERE config.agent_bot_id = ANY(canonical.duplicate_ids)
  AND config.agent_bot_id <> canonical.keep_id;

DELETE FROM agent_bot_inboxes duplicate
USING agent_bot_inboxes canonical
WHERE duplicate.id > canonical.id
  AND duplicate.agent_bot_id = canonical.agent_bot_id
  AND duplicate.inbox_id = canonical.inbox_id;

DELETE FROM agent_bots duplicate
USING agent_bots canonical
WHERE duplicate.id > canonical.id
  AND duplicate.account_id IS NOT DISTINCT FROM canonical.account_id
  AND duplicate.captain_assistant_id = canonical.captain_assistant_id
  AND duplicate.captain_assistant_id IS NOT NULL;

DELETE FROM captain_inboxes duplicate
USING captain_inboxes canonical
WHERE duplicate.id > canonical.id
  AND duplicate.inbox_id = canonical.inbox_id
  AND duplicate.deleted_at IS NULL
  AND canonical.deleted_at IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_agent_bots_captain_assistant
  ON agent_bots(account_id, captain_assistant_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_agent_bot_inboxes_bot_inbox
  ON agent_bot_inboxes(agent_bot_id, inbox_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_captain_inboxes_active_inbox
  ON captain_inboxes(inbox_id) WHERE deleted_at IS NULL;