-- Aggie-server counterpart of add_mail_classification.sql.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p 'stacie_Aggie_v1.0' < db/aggie_add_mail_classification.sql

USE `stacie_Aggie_v1.0`;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'stacie_Aggie_v1.0' 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 = 'stacie_Aggie_v1.0' 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 = 'stacie_Aggie_v1.0' 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)
);