CREATE TABLE lead_hiscon_snapshots (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    hash CHAR(64) NOT NULL,
    document_datetime DATETIME NULL,
    verification_code VARCHAR(120) NULL,
    payload JSON NOT NULL,
    status ENUM('ok', 'parser_failed') NOT NULL DEFAULT 'ok',
    errors JSON NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_hiscon_snapshot_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_hiscon_snapshot_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    UNIQUE KEY idx_hiscon_snapshots_hash (hash),
    INDEX idx_hiscon_snapshots_lead (lead_id),
    INDEX idx_hiscon_snapshots_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_opportunities (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    hiscon_snapshot_id INT UNSIGNED NULL,
    contract_origin_code VARCHAR(50) NULL,
    contract_number VARCHAR(80) NULL,
    type ENUM('portabilidade', 'refinanciamento', 'novo_consignado', 'outro') NOT NULL,
    source ENUM('manual_simulation', 'rule_engine') NOT NULL DEFAULT 'rule_engine',
    payload JSON NOT NULL,
    status ENUM('nova', 'em_andamento', 'concluida', 'descartada', 'agendada') NOT NULL DEFAULT 'nova',
    priority TINYINT UNSIGNED NOT NULL DEFAULT 3,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    scheduled_for DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_opportunity_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_opportunity_snapshot FOREIGN KEY (hiscon_snapshot_id) REFERENCES lead_hiscon_snapshots (id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_opportunity_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_lead_opportunities_lead (lead_id),
    INDEX idx_lead_opportunities_status (status),
    INDEX idx_lead_opportunities_type (type),
    INDEX idx_lead_opportunities_source (source),
    INDEX idx_lead_opportunities_snapshot (hiscon_snapshot_id),
    INDEX idx_lead_opportunities_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE opportunity_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    condition_expression TEXT NOT NULL,
    action_type ENUM('portabilidade', 'refinanciamento', 'novo_consignado', 'custom') NOT NULL DEFAULT 'custom',
    priority TINYINT UNSIGNED NOT NULL DEFAULT 3,
    schedule ENUM('monthly', 'weekly', 'on_import') NOT NULL DEFAULT 'monthly',
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    valid_from DATETIME NULL,
    valid_to DATETIME NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_opportunity_rules_schedule (schedule),
    INDEX idx_opportunity_rules_enabled (enabled),
    INDEX idx_opportunity_rules_validity (valid_from, valid_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE opportunity_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_opportunity_id INT UNSIGNED NOT NULL,
    rule_id INT UNSIGNED NULL,
    log_type ENUM('created', 'updated', 'closed', 'error') NOT NULL,
    message VARCHAR(255) NULL,
    payload JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_opportunity_log_opportunity FOREIGN KEY (lead_opportunity_id) REFERENCES lead_opportunities (id) ON DELETE CASCADE,
    CONSTRAINT fk_opportunity_log_rule FOREIGN KEY (rule_id) REFERENCES opportunity_rules (id) ON DELETE SET NULL,
    INDEX idx_opportunity_logs_opportunity (lead_opportunity_id),
    INDEX idx_opportunity_logs_rule (rule_id),
    INDEX idx_opportunity_logs_type (log_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;