-- Phase 0 of the CRM upgrade program: the "one door in" safety rail. Every future integration
-- that can create/update a Contact (quote forms, chatbots, voice AI, email parsing) must resolve
-- through ContactResolutionService rather than inserting into dim_contacts directly. This
-- migration only adds an audit table and lookup indexes — no existing table is altered.
-- Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/contact_resolution.sql

USE db_abos_v0.1;

-- One row per resolver decision: CREATE (no match found) or MERGE (matched an existing contact
-- and filled in previously-empty fields). Never records an overwrite of a non-null field, because
-- the resolver never performs one — this table is the receipt proving that.
CREATE TABLE IF NOT EXISTS fact_contact_merge_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    contact_id INT NOT NULL,
    action VARCHAR(16) NOT NULL COMMENT 'CREATE or MERGE',
    source VARCHAR(64) NOT NULL COMMENT 'Caller identity, e.g. QUOTE_FORM, CHATBOT, VOICE_AI, EMAIL_PARSER, MANUAL',
    matched_on VARCHAR(16) NULL COMMENT 'EMAIL or PHONE — which field found the existing contact on a MERGE',
    fields_filled VARCHAR(500) NULL COMMENT 'Comma-separated list of previously-empty fields that were filled in, e.g. "phone,role"',
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_contact_merge_log_tenant_contact (tenant_id, contact_id),
    INDEX idx_contact_merge_log_created (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lookup indexes the resolver depends on for its match-before-create step. Contacts without an
-- email or phone are unaffected (MySQL indexes NULLs, they just never match a lookup).
-- MySQL has no CREATE INDEX IF NOT EXISTS, so guard via information_schema like the rest of db/.
SET @idx_email_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_contacts' AND INDEX_NAME = 'idx_dim_contacts_tenant_email'
);
SET @sql = IF(@idx_email_exists = 0,
    'CREATE INDEX idx_dim_contacts_tenant_email ON dim_contacts (tenant_id, email)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_phone_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_contacts' AND INDEX_NAME = 'idx_dim_contacts_tenant_phone'
);
SET @sql = IF(@idx_phone_exists = 0,
    'CREATE INDEX idx_dim_contacts_tenant_phone ON dim_contacts (tenant_id, phone)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;