-- Adds mail auto-classification: category, needs_reference_review, classified_at on
-- fact_mail_message (set by MailClassificationService right after import in MailSyncScheduler),
-- and a new fact_mail_reference_suggestions table holding candidate Lead/Deal links for mail
-- whose sender matched more than one Contact reference (source RULE) or was reasoned about by
-- MailAiClassificationScheduler (source AI) — resolved by a human via
-- MailReferenceSuggestionController's approve/reject endpoints.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/add_mail_classification.sql

USE db_abos_v0.1;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'db_abos_v0.1' AND TABLE_NAME = 'fact_mail_message' AND COLUMN_NAME = 'category'
);
SET @ddl = IF(@col_exists = 0,
  'ALTER TABLE fact_mail_message ADD COLUMN category VARCHAR(30) NULL AFTER linked_entity_id',
  'SELECT 1'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'db_abos_v0.1' AND TABLE_NAME = 'fact_mail_message' AND COLUMN_NAME = 'needs_reference_review'
);
SET @ddl = IF(@col_exists = 0,
  'ALTER TABLE fact_mail_message ADD COLUMN needs_reference_review TINYINT(1) NOT NULL DEFAULT 0 AFTER category',
  'SELECT 1'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'db_abos_v0.1' AND TABLE_NAME = 'fact_mail_message' AND COLUMN_NAME = 'classified_at'
);
SET @ddl = IF(@col_exists = 0,
  'ALTER TABLE fact_mail_message ADD COLUMN classified_at TIMESTAMP NULL AFTER needs_reference_review',
  'SELECT 1'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS fact_mail_reference_suggestions (
    id                    BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id             INT NOT NULL,
    mail_message_id       BIGINT NOT NULL,
    candidate_entity_type VARCHAR(20) NOT NULL,
    candidate_entity_id   BIGINT NOT NULL,
    candidate_label       VARCHAR(255) NULL,
    source                VARCHAR(10) NOT NULL,
    confidence            VARCHAR(10) NULL,
    status                VARCHAR(15) NOT NULL DEFAULT 'PENDING',
    created_at            TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at           TIMESTAMP NULL,
    resolved_by           INT NULL,
    is_deleted            TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_mail_ref_suggestions_message (tenant_id, mail_message_id, status, is_deleted),
    KEY idx_mail_ref_suggestions_pending (tenant_id, status, is_deleted, created_at)
);