CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    display_name VARCHAR(120) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    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 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(64) PRIMARY KEY,
    setting_value MEDIUMTEXT NULL,
    is_secret TINYINT(1) NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    subject VARCHAR(255) NOT NULL DEFAULT '',
    html_body MEDIUMTEXT NOT NULL,
    plain_text MEDIUMTEXT NULL,
    category VARCHAR(80) NOT NULL DEFAULT 'General',
    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,
    INDEX idx_templates_name (name),
    CONSTRAINT fk_templates_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS drafts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(190) NOT NULL DEFAULT 'Untitled draft',
    payload_json LONGTEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_drafts_user_updated (user_id, updated_at),
    CONSTRAINT fk_drafts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(36) NOT NULL UNIQUE,
    idempotency_key VARCHAR(100) NULL UNIQUE,
    created_by BIGINT UNSIGNED NULL,
    from_name VARCHAR(190) NOT NULL,
    from_email VARCHAR(190) NOT NULL,
    reply_to VARCHAR(190) NULL,
    thread_key CHAR(64) NULL,
    in_reply_to VARCHAR(500) NULL,
    references_header TEXT NULL,
    source_inbound_id BIGINT UNSIGNED NULL,
    recipients_json LONGTEXT NOT NULL,
    cc_json LONGTEXT NULL,
    bcc_json LONGTEXT NULL,
    subject VARCHAR(255) NOT NULL,
    html_body MEDIUMTEXT NOT NULL,
    plain_text MEDIUMTEXT NOT NULL,
    status ENUM('draft','scheduled','queued','processing','sent','temporary_failed','permanent_failed','cancelled') NOT NULL DEFAULT 'queued',
    attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    scheduled_at DATETIME NULL,
    next_attempt_at DATETIME NULL,
    processing_token VARCHAR(100) NULL,
    processing_started_at DATETIME NULL,
    sent_at DATETIME NULL,
    smtp_message_id VARCHAR(255) NULL,
    last_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_messages_status_due (status, scheduled_at, next_attempt_at),
    INDEX idx_messages_created (created_at),
    INDEX idx_messages_sender (from_email),
    INDEX idx_messages_thread (thread_key, created_at),
    CONSTRAINT fk_messages_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message_id BIGINT UNSIGNED NOT NULL,
    stored_name VARCHAR(100) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(150) NOT NULL,
    size_bytes BIGINT UNSIGNED NOT NULL,
    sha256 CHAR(64) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_attachments_message (message_id),
    CONSTRAINT fk_attachments_message FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mailbox_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email_address VARCHAR(190) NOT NULL UNIQUE,
    display_name VARCHAR(190) NOT NULL DEFAULT '',
    quota_mb INT UNSIGNED NOT NULL DEFAULT 500,
    notification_email VARCHAR(190) NULL,
    notification_enabled TINYINT(1) NOT NULL DEFAULT 0,
    cpanel_status VARCHAR(40) NOT NULL DEFAULT 'created',
    last_synced_at DATETIME NULL,
    last_sync_error TEXT 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,
    CONSTRAINT fk_mailboxes_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    first_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    company VARCHAR(190) NULL,
    phone VARCHAR(60) NULL,
    tags VARCHAR(500) NULL,
    consent_status ENUM('unknown','consented','unsubscribed','bounced') NOT NULL DEFAULT 'unknown',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_contacts_company (company),
    INDEX idx_contacts_status (consent_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action_name VARCHAR(120) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    metadata_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_created (created_at),
    INDEX idx_audit_action (action_name),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO templates (name, subject, html_body, plain_text, category, created_by)
SELECT 'Professional Welcome', 'Welcome to {{company_name}}', '<div style="text-align:center;padding:12px"><h1 style="margin:0 0 12px;font-size:30px">Welcome, {{first_name}}</h1><p style="font-size:16px;color:#4b5563">We are delighted to have you with {{company_name}}.</p><p><a href="#" style="display:inline-block;background:#5b5ce2;color:#fff;text-decoration:none;padding:12px 22px;border-radius:10px">Get Started</a></p></div>', 'Welcome, {{first_name}}. We are delighted to have you with {{company_name}}.', 'Welcome', NULL
WHERE NOT EXISTS (SELECT 1 FROM templates WHERE name = 'Professional Welcome');

INSERT INTO templates (name, subject, html_body, plain_text, category, created_by)
SELECT 'Payment Confirmation', 'Payment received – {{invoice_number}}', '<h2 style="margin-top:0">Payment received</h2><p>Hello {{first_name}},</p><p>We have received your payment of <strong>{{amount}}</strong> for invoice <strong>{{invoice_number}}</strong>.</p><p>Thank you for your business.</p>', 'Payment received. We received {{amount}} for invoice {{invoice_number}}.', 'Billing', NULL
WHERE NOT EXISTS (SELECT 1 FROM templates WHERE name = 'Payment Confirmation');


CREATE TABLE IF NOT EXISTS inbound_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mailbox_id BIGINT UNSIGNED NOT NULL,
    mailbox_email VARCHAR(190) NOT NULL,
    pop_uidl VARCHAR(255) NOT NULL,
    message_id_header VARCHAR(500) NOT NULL,
    in_reply_to VARCHAR(500) NULL,
    references_header TEXT NULL,
    thread_key CHAR(64) NOT NULL,
    from_name VARCHAR(190) NULL,
    from_email VARCHAR(190) NOT NULL,
    reply_to_name VARCHAR(190) NULL,
    reply_to_email VARCHAR(190) NULL,
    to_json LONGTEXT NULL,
    cc_json LONGTEXT NULL,
    subject VARCHAR(500) NOT NULL DEFAULT '(No subject)',
    html_body MEDIUMTEXT NULL,
    text_body MEDIUMTEXT NULL,
    received_at DATETIME NOT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    is_starred TINYINT(1) NOT NULL DEFAULT 0,
    has_attachments TINYINT(1) NOT NULL DEFAULT 0,
    raw_headers MEDIUMTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_inbound_mailbox_uidl (mailbox_id, pop_uidl),
    KEY idx_inbound_mailbox_received (mailbox_id, received_at),
    KEY idx_inbound_unread (is_read, received_at),
    KEY idx_inbound_thread (thread_key, received_at),
    KEY idx_inbound_sender (from_email),
    CONSTRAINT fk_inbound_mailbox FOREIGN KEY (mailbox_id) REFERENCES mailbox_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inbound_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inbound_message_id BIGINT UNSIGNED NOT NULL,
    stored_name VARCHAR(100) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(150) NOT NULL,
    size_bytes BIGINT UNSIGNED NOT NULL,
    content_id VARCHAR(255) NULL,
    is_inline TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_inbound_attachment_message (inbound_message_id),
    CONSTRAINT fk_inbound_attachment_message FOREIGN KEY (inbound_message_id) REFERENCES inbound_messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;