-- Migration: po_email_matches
--
-- Backing table for the PO email summarization pipeline
-- (scripts/po-email-sync.js). Dedupes processed Gmail messages per PO and
-- preserves Claude's raw extraction for audit / re-runs.
--
-- Idempotent — safe to re-run.

CREATE TABLE IF NOT EXISTS po_email_matches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    po_id INT NOT NULL,
    gmail_thread_id VARCHAR(100) NOT NULL,
    gmail_message_id VARCHAR(100) NOT NULL,
    note_id INT DEFAULT NULL,
    match_reason VARCHAR(64) NOT NULL,
    match_confidence TINYINT NOT NULL,
    from_address VARCHAR(255) DEFAULT NULL,
    is_rep_only TINYINT(1) NOT NULL DEFAULT 0,
    is_action_needed TINYINT(1) NOT NULL DEFAULT 0,
    ai_summary VARCHAR(500) DEFAULT NULL,
    ai_extracted_json LONGTEXT DEFAULT NULL,
    applied_updates_json LONGTEXT DEFAULT NULL,
    internal_date BIGINT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (note_id) REFERENCES purchase_order_notes(id) ON DELETE SET NULL,
    UNIQUE KEY uq_po_message (po_id, gmail_message_id),
    INDEX idx_po (po_id),
    INDEX idx_thread (gmail_thread_id),
    INDEX idx_internal_date (internal_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;