-- Phase 4 (part 1) of the CRM upgrade program: structured-data extraction from inbound email via
-- regex/HTML rules — no OCR (that specifically needs a paid vision API and is not built here; see
-- EmailExtractionService's class doc). Extraction never writes to dim_contacts/fact_leads
-- directly; it only produces a JSON result and fires a webhook event for external systems to act
-- on, so ContactResolutionService's rules are not implicated by this feature.
-- Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/email_extraction.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS dim_email_extraction_rules (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    field_key VARCHAR(100) NOT NULL COMMENT 'Output key in the extracted JSON, e.g. "poNumber"',
    pattern_type VARCHAR(16) NOT NULL COMMENT 'REGEX (against bodyText) or HTML_SELECTOR (against bodyHtml, CSS selector)',
    pattern TEXT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    INDEX idx_email_extraction_rules_tenant (tenant_id, is_deleted, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per extraction run against a message — kept even when every rule comes back empty, so
-- "we tried and found nothing" is distinguishable from "we never tried".
CREATE TABLE IF NOT EXISTS fact_email_extractions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    mail_message_id BIGINT NOT NULL,
    extracted_json LONGTEXT NOT NULL COMMENT 'JSON map of field_key -> matched value(s)',
    matched_rule_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_email_extractions_message (mail_message_id),
    INDEX idx_email_extractions_tenant (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;