-- Migration 011: campaign_events
-- Tracks per-lead engagement events emitted by campaign dispatchers.
-- Used by LeadScorer (email_open/link_click/email_sent signals) and
-- future analytics (open-rate, CTR per campaign).

CREATE TABLE IF NOT EXISTS campaign_events (
    id          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    org_id      BIGINT UNSIGNED  NOT NULL,
    campaign_id BIGINT UNSIGNED  NULL,
    lead_id     BIGINT UNSIGNED  NOT NULL,
    event_type  VARCHAR(40)      NOT NULL COMMENT 'email_sent|email_open|link_click|unsubscribe|bounce',
    meta_json   JSON             NULL     COMMENT 'optional payload e.g. {url, ip, ua}',
    created_at  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_ce_lead       (lead_id),
    KEY idx_ce_campaign   (campaign_id),
    KEY idx_ce_org_type   (org_id, event_type),
    KEY idx_ce_lead_ts    (lead_id, created_at),

    CONSTRAINT fk_ce_org      FOREIGN KEY (org_id)      REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_ce_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id)     ON DELETE SET NULL,
    CONSTRAINT fk_ce_lead     FOREIGN KEY (lead_id)     REFERENCES leads(id)         ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
