-- Migration 0008: create_campaigns
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 7 — CAMPAIGNS
-- =====================================================================

CREATE TABLE IF NOT EXISTS campaigns (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NULL COMMENT 'NULL = sent to both brands',
    email_template_id BIGINT UNSIGNED NULL,
    name              VARCHAR(191)    NOT NULL,
    subject           VARCHAR(255)    NOT NULL,
    body_html         MEDIUMTEXT      NOT NULL,
    audience_segment  VARCHAR(120)    NULL COMMENT 'Denormalized label, e.g. "Active Clients"',
    audience_filter   JSON            NULL COMMENT 'Structured segment definition for re-evaluation',
    status            ENUM('draft','scheduled','sending','sent','cancelled') NOT NULL DEFAULT 'draft',
    scheduled_at      DATETIME        NULL,
    sent_at           DATETIME        NULL,
    recipients_count  INT UNSIGNED    NOT NULL DEFAULT 0,
    opens_count       INT UNSIGNED    NOT NULL DEFAULT 0,
    clicks_count      INT UNSIGNED    NOT NULL DEFAULT 0,
    unsubscribes_count INT UNSIGNED   NOT NULL DEFAULT 0,
    created_by        BIGINT UNSIGNED NULL,
    updated_by        BIGINT UNSIGNED NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at        DATETIME        NULL,
    KEY idx_campaigns_business_line (business_line_id),
    KEY idx_campaigns_status (status),
    CONSTRAINT fk_campaigns_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_campaigns_template FOREIGN KEY (email_template_id) REFERENCES email_templates(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_campaigns_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_campaigns_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaign_recipients (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id   BIGINT UNSIGNED NOT NULL,
    customer_id   BIGINT UNSIGNED NULL,
    lead_id       BIGINT UNSIGNED NULL,
    email_address VARCHAR(191)    NOT NULL,
    status        ENUM('pending','sent','delivered','opened','clicked','bounced','unsubscribed') NOT NULL DEFAULT 'pending',
    sent_at       DATETIME        NULL,
    opened_at     DATETIME        NULL,
    clicked_at    DATETIME        NULL,
    KEY idx_campaign_recipients_campaign (campaign_id),
    KEY idx_campaign_recipients_customer (customer_id),
    KEY idx_campaign_recipients_lead (lead_id),
    CONSTRAINT fk_campaign_recipients_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_campaign_recipients_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_campaign_recipients_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;
