-- MySQL schema for the newsletter sender.
-- Import via cPanel → phpMyAdmin → (select your DB) → Import, or run with the mysql CLI.

SET NAMES utf8mb4;

-- Admin accounts for the panel
CREATE TABLE IF NOT EXISTS admins (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Opt-in subscribers
CREATE TABLE IF NOT EXISTS subscribers (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(254) NOT NULL UNIQUE,
    name          VARCHAR(200) NULL,
    status        ENUM('subscribed','unsubscribed','bounced') NOT NULL DEFAULT 'subscribed',
    -- Proof of consent — keep this honest; it's your legal cover.
    consent_source VARCHAR(255) NULL,     -- e.g. "signup form 2026-01", "import: members.csv"
    consent_at    DATETIME NULL,
    unsub_token   CHAR(64) NOT NULL,       -- per-subscriber token for one-click unsubscribe
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Suppression list: addresses that must NEVER be mailed again.
-- Unsubscribes, hard bounces, and complaints land here.
CREATE TABLE IF NOT EXISTS suppressions (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(254) NOT NULL UNIQUE,
    reason     ENUM('unsubscribe','bounce','complaint','manual') NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Campaigns
CREATE TABLE IF NOT EXISTS campaigns (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(255) NOT NULL,
    subject        VARCHAR(500) NOT NULL,
    from_email     VARCHAR(254) NOT NULL,
    from_name      VARCHAR(200) NOT NULL,
    reply_to       VARCHAR(254) NULL,
    html_body      MEDIUMTEXT NOT NULL,
    text_body      MEDIUMTEXT NULL,          -- auto-generated if blank
    batch_size     INT NOT NULL DEFAULT 10,
    pause_seconds  INT NOT NULL DEFAULT 60,
    status         ENUM('draft','queued','sending','paused','done') NOT NULL DEFAULT 'draft',
    -- resume pointer: how many queue rows have been processed
    cursor_pos     INT NOT NULL DEFAULT 0,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-campaign send queue (a snapshot of recipients taken when the campaign is queued)
CREATE TABLE IF NOT EXISTS send_queue (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    campaign_id   INT NOT NULL,
    subscriber_id INT NOT NULL,
    email         VARCHAR(254) NOT NULL,
    name          VARCHAR(200) NULL,
    send_token    CHAR(64) NOT NULL,        -- used for the open-tracking pixel
    status        ENUM('pending','sent','failed','skipped') NOT NULL DEFAULT 'pending',
    error         VARCHAR(500) NULL,
    sent_at       DATETIME NULL,
    position      INT NOT NULL,             -- stable ordering so we never restart from the top
    INDEX (campaign_id, status),
    INDEX (campaign_id, position),
    UNIQUE KEY uniq_campaign_sub (campaign_id, subscriber_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Open events
CREATE TABLE IF NOT EXISTS opens (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    queue_id    BIGINT NOT NULL,
    campaign_id INT NOT NULL,
    opened_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_agent  VARCHAR(500) NULL,
    ip          VARCHAR(64) NULL,
    INDEX (campaign_id),
    INDEX (queue_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;