-- ============================================================
-- File: /schema.sql  (run once via phpMyAdmin, not deployed to web root)
-- CQube Email List Manager — Database Schema (v1)
-- Target: MySQL / MariaDB via phpMyAdmin (no CLI/SSH required)
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- contacts: master record, one row per unique email address
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contacts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) DEFAULT NULL,
    last_name VARCHAR(100) DEFAULT NULL,
    status ENUM('pending','active','unsubscribed','bounced','suppressed') NOT NULL DEFAULT 'pending',
    source VARCHAR(100) DEFAULT NULL,          -- e.g. 'DTSMS Website', 'OSHALand Website', 'CSV Import'
    signup_ip VARCHAR(45) DEFAULT NULL,
    confirmed_at DATETIME DEFAULT NULL,        -- set when double opt-in confirmed
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_contacts_email (email),
    KEY idx_contacts_status (status),
    KEY idx_contacts_source (source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- lists: named groupings (DTSMS Customers, OSHALand Prospects, etc.)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS lists (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_lists_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- contact_lists: many-to-many, a contact can be on several lists
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contact_lists (
    contact_id INT UNSIGNED NOT NULL,
    list_id INT UNSIGNED NOT NULL,
    added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (contact_id, list_id),
    CONSTRAINT fk_cl_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_cl_list FOREIGN KEY (list_id) REFERENCES lists(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- tags: free-form labels (TPA, Clinic, DOT, Customer, etc.)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_tags_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contact_tags (
    contact_id INT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (contact_id, tag_id),
    CONSTRAINT fk_ct_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_ct_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- suppressions: permanent do-not-email list. NEVER delete rows.
-- Checked before reactivating any contact via import or signup.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS suppressions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    reason VARCHAR(255) DEFAULT NULL,          -- 'unsubscribed', 'complaint', 'hard_bounce', 'manual'
    date_suppressed DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_suppressions_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- campaigns
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS campaigns (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    from_name VARCHAR(150) NOT NULL,
    from_email VARCHAR(255) NOT NULL,
    html_content MEDIUMTEXT NOT NULL,
    status ENUM('draft','sending','sent','failed') NOT NULL DEFAULT 'draft',
    target_list_id INT UNSIGNED DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME DEFAULT NULL,
    CONSTRAINT fk_campaigns_list FOREIGN KEY (target_list_id) REFERENCES lists(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- campaign_recipients: per-contact send record for a campaign
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS campaign_recipients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT UNSIGNED NOT NULL,
    contact_id INT UNSIGNED NOT NULL,
    status ENUM('queued','sent','delivered','bounced','failed') NOT NULL DEFAULT 'queued',
    resend_id VARCHAR(100) DEFAULT NULL,       -- Resend's message id, for cross-referencing webhooks
    sent_at DATETIME DEFAULT NULL,
    delivered_at DATETIME DEFAULT NULL,
    opened_at DATETIME DEFAULT NULL,
    clicked_at DATETIME DEFAULT NULL,
    UNIQUE KEY uq_campaign_contact (campaign_id, contact_id),
    KEY idx_cr_resend_id (resend_id),
    CONSTRAINT fk_cr_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    CONSTRAINT fk_cr_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- unsubscribe_tokens: secure random tokens used in email links
-- (never put the raw email address in an unsubscribe URL)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS unsubscribe_tokens (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contact_id INT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,              -- sha256 hash of the token; raw token only ever in the emailed link
    purpose ENUM('confirm','unsubscribe','preferences') NOT NULL DEFAULT 'unsubscribe',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    used_at DATETIME DEFAULT NULL,
    UNIQUE KEY uq_token_hash (token_hash),
    KEY idx_ut_contact (contact_id),
    CONSTRAINT fk_ut_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- email_events: raw event log from Resend webhooks
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contact_id INT UNSIGNED DEFAULT NULL,
    campaign_id INT UNSIGNED DEFAULT NULL,
    event VARCHAR(50) NOT NULL,                -- 'delivered','opened','clicked','bounced','complained'
    event_data JSON DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_ee_contact (contact_id),
    KEY idx_ee_campaign (campaign_id),
    CONSTRAINT fk_ee_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    CONSTRAINT fk_ee_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;