-- Migration 0007: create_email
-- 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 6 — EMAIL
-- =====================================================================

CREATE TABLE IF NOT EXISTS email_accounts (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id           BIGINT UNSIGNED NULL COMMENT 'NULL = shared/team mailbox',
    business_line_id  BIGINT UNSIGNED NULL,
    email_address     VARCHAR(191)    NOT NULL,
    provider          ENUM('gmail','smtp','outlook') NOT NULL DEFAULT 'smtp',
    credentials_encrypted TEXT        NULL COMMENT 'Encrypted OAuth token or SMTP secret, never plaintext',
    is_active         TINYINT(1)      NOT NULL DEFAULT 1,
    connected_at      DATETIME        NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_email_accounts_address (email_address),
    KEY idx_email_accounts_user (user_id),
    CONSTRAINT fk_email_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_email_accounts_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_messages (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email_account_id  BIGINT UNSIGNED NOT NULL,
    direction         ENUM('inbound','outbound') NOT NULL,
    related_lead_id   BIGINT UNSIGNED NULL,
    related_customer_id BIGINT UNSIGNED NULL,
    from_address      VARCHAR(191)    NOT NULL,
    to_address        VARCHAR(191)    NOT NULL,
    subject           VARCHAR(255)    NULL,
    body_html         MEDIUMTEXT      NULL,
    status            ENUM('queued','sent','delivered','failed','received','read') NOT NULL DEFAULT 'queued',
    is_starred        TINYINT(1)      NOT NULL DEFAULT 0,
    sent_at           DATETIME        NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at        DATETIME        NULL,
    KEY idx_email_messages_account (email_account_id, created_at),
    KEY idx_email_messages_lead (related_lead_id),
    KEY idx_email_messages_customer (related_customer_id),
    FULLTEXT KEY ftx_email_messages_subject (subject),
    CONSTRAINT fk_email_messages_account FOREIGN KEY (email_account_id) REFERENCES email_accounts(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_email_messages_lead FOREIGN KEY (related_lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_email_messages_customer FOREIGN KEY (related_customer_id) REFERENCES customers(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_templates (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NULL,
    name              VARCHAR(150)    NOT NULL,
    category          ENUM('homecare','janitorial','transactional','general') NOT NULL DEFAULT 'general',
    subject           VARCHAR(255)    NOT NULL,
    body_html         MEDIUMTEXT      NOT NULL,
    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_email_templates_business_line (business_line_id),
    KEY idx_email_templates_category (category),
    CONSTRAINT fk_email_templates_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_email_templates_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_email_templates_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;


SET FOREIGN_KEY_CHECKS = 1;
