-- READ-ONLY preflight after the additive identity_scope column is present.
-- Run against an operator-approved backup/staging copy, not an implicit production
-- connection. Compatible with SQLite and MySQL. This file never changes data.
-- Results contain customer identifiers; keep them private and do not commit them.

-- 1. Any result is an ambiguous key. Runtime lookup fails closed for these keys,
-- including disabled mappings. Resolve ownership before adding a unique index.
SELECT identity_scope, external_source, external_id,
       COUNT(*) AS mapping_count, COUNT(DISTINCT customer_id) AS owner_count
FROM t_customer_identity
GROUP BY identity_scope, external_source, external_id
HAVING COUNT(*) > 1;

-- 2. Inventory legacy mappings and their conversation channels. A single channel
-- is evidence to investigate, NOT proof of the issuer or ownership. Guest IDs
-- from unauthenticated exchange are never sufficient proof to recover history.
SELECT ci.id AS identity_id, ci.customer_id, ci.external_source, ci.external_id,
       ci.status AS identity_status, c.channel_id AS channel_record_id,
       ch.channel_id AS channel_code, COUNT(c.id) AS conversation_count
FROM t_customer_identity AS ci
LEFT JOIN t_conversation AS c ON c.customer_id = ci.customer_id
LEFT JOIN t_channel AS ch ON ch.id = c.channel_id
WHERE ci.identity_scope = ''
GROUP BY ci.id, ci.customer_id, ci.external_source, ci.external_id, ci.status,
         c.channel_id, ch.channel_id
ORDER BY ci.id, c.channel_id;

-- 3. Mixed legacy/scoped mappings require explicit review of the legacy owner
-- and any new post-upgrade customer before reconciliation. Equal external_id
-- values across channels/issuers are not evidence of equal people.
SELECT legacy.id AS legacy_identity_id, legacy.customer_id AS legacy_customer_id,
       scoped.id AS scoped_identity_id, scoped.customer_id AS scoped_customer_id,
       scoped.identity_scope, legacy.external_source, legacy.external_id
FROM t_customer_identity AS legacy
JOIN t_customer_identity AS scoped
  ON scoped.external_source = legacy.external_source
 AND scoped.external_id = legacy.external_id
WHERE legacy.identity_scope = '' AND scoped.identity_scope <> ''
ORDER BY legacy.id, scoped.id;

-- 4. Scoped mappings without their unique digest require a reviewed key backfill.
-- Application-created scoped mappings always have a key. Never populate it by
-- hashing only external_id: include the scope and external source as implemented
-- by repositories.ScopedCustomerIdentityKey (JSON string array, SHA-256).
SELECT id, customer_id, identity_scope, external_source, external_id
FROM t_customer_identity
WHERE identity_scope <> '' AND scoped_key IS NULL;