-- Migration 022: inbound_messages table for reply inbox
-- Stores inbound replies from all outreach channels.
-- AI triage category (ai_category) is populated by InboundRouter after classification.

CREATE TABLE IF NOT EXISTS inbound_messages (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id       BIGINT UNSIGNED NOT NULL,
    lead_id      BIGINT UNSIGNED NULL,
    channel      ENUM('email','linkedin','x','instagram','facebook') NOT NULL DEFAULT 'email',
    from_address VARCHAR(255) NOT NULL,
    body         TEXT NOT NULL,
    ai_category  ENUM('interested','soft_no','hard_no','out_of_office','question','unknown') NOT NULL DEFAULT 'unknown',
    is_read      TINYINT(1) NOT NULL DEFAULT 0,
    received_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_im_org_read   (org_id, is_read),
    KEY idx_im_org_ts     (org_id, received_at),
    KEY idx_im_lead       (lead_id),

    CONSTRAINT fk_im_org  FOREIGN KEY (org_id)  REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_im_lead FOREIGN KEY (lead_id) REFERENCES leads(id)         ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
