-- Migration 0015: create_marketing_system
-- 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 15 — MARKETING SYSTEM (Phase 4)
-- Additive only. `campaigns`/`campaign_recipients` (Phase 1) gain new
-- nullable columns; everything else is a new table. `automation_workflows`/
-- `automation_steps`/`automation_runs` (Phase 1) are reused as-is — the
-- automation ENGINE built in Phase 4 is new application code, not a
-- new table.
-- =====================================================================

-- Recurring campaigns follow the exact same pattern proven for
-- recurring appointments in Phase 3: the parent stays flagged, each
-- occurrence is a REAL child campaign row (own recipients, own stats),
-- never a virtual/computed send.
ALTER TABLE campaigns
    ADD COLUMN parent_campaign_id BIGINT UNSIGNED NULL AFTER email_template_id,
    ADD COLUMN is_recurring_parent TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN recurrence_rule JSON NULL COMMENT 'Same shape as appointments.recurrence_rule: {frequency, interval, until}',
    ADD COLUMN audience_segment_id BIGINT UNSIGNED NULL COMMENT 'Set when the audience was built from a saved segment rather than an ad-hoc filter',
    ADD KEY idx_campaigns_parent (parent_campaign_id);

ALTER TABLE campaign_recipients
    ADD COLUMN bounced_at DATETIME NULL AFTER clicked_at,
    ADD COLUMN replied_at DATETIME NULL AFTER bounced_at,
    ADD COLUMN unsubscribe_token CHAR(64) NULL AFTER replied_at,
    ADD UNIQUE KEY uk_campaign_recipients_unsub_token (unsubscribe_token);

-- Birthday Email automation needs a birthday to fire on.
ALTER TABLE customers
    ADD COLUMN date_of_birth DATE NULL AFTER phone;

-- Global suppression list: once an address unsubscribes, EVERY future
-- campaign (regardless of which one they unsubscribed from) must skip
-- it — this is checked by AudienceSegmentService, not just the
-- originating campaign's recipient list.
CREATE TABLE IF NOT EXISTS email_unsubscribes (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email             VARCHAR(191)    NOT NULL,
    business_line_id  BIGINT UNSIGNED NULL COMMENT 'NULL = unsubscribed from all A&B communications, not just one brand',
    reason            VARCHAR(255)    NULL,
    unsubscribed_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_email_unsubscribes_email_bl (email, business_line_id),
    CONSTRAINT fk_email_unsubscribes_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Saved, reusable audience definitions ("Filtered Audience") — the
-- filter itself is a small JSON DSL interpreted by
-- AudienceSegmentService::resolve(), e.g.
-- {"type":"leads","business_line":"homecare","stage":"quoted"}.
CREATE TABLE IF NOT EXISTS audience_segments (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name              VARCHAR(150)    NOT NULL,
    description       VARCHAR(255)    NULL,
    filter_definition JSON            NOT NULL,
    created_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,
    CONSTRAINT fk_audience_segments_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The actual send queue — decouples "the campaign was scheduled/sent"
-- from "the SMTP transaction happened," so a bulk send of 5,000
-- recipients never blocks an HTTP request. bin/process_email_queue.php
-- (a worker, run on a schedule) claims pending rows and sends them.
CREATE TABLE IF NOT EXISTS email_queue (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id     BIGINT UNSIGNED NULL COMMENT 'NULL = a personal/one-off email, not part of a campaign',
    campaign_recipient_id BIGINT UNSIGNED NULL,
    to_email        VARCHAR(191)    NOT NULL,
    to_name         VARCHAR(191)    NULL,
    from_email      VARCHAR(191)    NULL COMMENT 'NULL = use the workspace default from config/mail.php',
    subject         VARCHAR(255)    NOT NULL,
    body_html       MEDIUMTEXT      NOT NULL,
    status          ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
    attempts        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    last_error      VARCHAR(500)    NULL,
    scheduled_for   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Worker only sends rows where this is <= NOW()',
    sent_at         DATETIME        NULL,
    created_by      BIGINT UNSIGNED NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_email_queue_status_schedule (status, scheduled_for),
    KEY idx_email_queue_campaign (campaign_id),
    CONSTRAINT fk_email_queue_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_email_queue_recipient FOREIGN KEY (campaign_recipient_id) REFERENCES campaign_recipients(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_email_queue_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SMTP Settings: a single-row table (like billing_accounts) so the
-- workspace's mail transport is admin-configurable from the UI instead
-- of only via .env. MailService prefers this row when present and
-- falls back to config/mail.php otherwise — see that class's Phase 4
-- update.
CREATE TABLE IF NOT EXISTS smtp_settings (
    id                    TINYINT UNSIGNED PRIMARY KEY DEFAULT 1 COMMENT 'Always row id=1 — single workspace-wide config',
    host                  VARCHAR(191) NULL,
    port                  SMALLINT UNSIGNED NULL,
    encryption            ENUM('tls','ssl','none') NOT NULL DEFAULT 'tls',
    username              VARCHAR(191) NULL,
    password_encrypted    VARCHAR(500) NULL,
    from_address          VARCHAR(191) NULL,
    from_name             VARCHAR(191) NULL,
    is_configured         TINYINT(1) NOT NULL DEFAULT 0,
    updated_by            BIGINT UNSIGNED NULL,
    updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_smtp_settings_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT chk_smtp_settings_single_row CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Automation engine (Phase 4) needs to know WHEN a paused ("wait")
-- step should resume — added here rather than in Section 8 because
-- it's Phase 4 application code (AutomationEngine) that needs it, even
-- though the parent table itself dates to Phase 1.
ALTER TABLE automation_runs
    ADD COLUMN next_step_at DATETIME NULL AFTER current_step_id,
    ADD KEY idx_automation_runs_due (status, next_step_at);


SET FOREIGN_KEY_CHECKS = 1;
