SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS admin_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(254) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('owner','admin') NOT NULL DEFAULT 'owner',
    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,
    UNIQUE KEY uq_admin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS companies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dot_number VARCHAR(20) NOT NULL,
    company_name VARCHAR(255) NOT NULL,
    dba_name VARCHAR(255) NULL,
    owner_name VARCHAR(255) NULL,
    docket_prefix VARCHAR(20) NULL,
    docket_number VARCHAR(40) NULL,
    company_phone VARCHAR(40) NULL,
    owner_phone VARCHAR(40) NULL,
    email VARCHAR(254) NOT NULL,
    secondary_email VARCHAR(254) NULL,
    address_line_1 VARCHAR(255) NULL,
    address_line_2 VARCHAR(255) NULL,
    city VARCHAR(120) NULL,
    state VARCHAR(50) NULL,
    zip_code VARCHAR(20) NULL,
    country VARCHAR(80) NULL,
    county_code VARCHAR(20) NULL,
    website VARCHAR(255) NULL,
    source VARCHAR(80) NOT NULL DEFAULT 'FMCSA',
    source_updated_date DATE NULL,
    notes TEXT NULL,
    status ENUM('new','sent','followup_due','responded','paused','unsubscribed','bounced') NOT NULL DEFAULT 'new',
    responded TINYINT(1) NOT NULL DEFAULT 0,
    followup_enabled TINYINT(1) NOT NULL DEFAULT 1,
    first_sent_at DATETIME NULL,
    last_sent_at DATETIME NULL,
    next_email_at DATETIME NULL,
    sent_count INT UNSIGNED NOT NULL DEFAULT 0,
    followup_interval_days SMALLINT UNSIGNED NOT NULL DEFAULT 15,
    response_received_at DATETIME NULL,
    response_note TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_companies_dot (dot_number),
    KEY idx_companies_email (email),
    KEY idx_companies_docket (docket_prefix, docket_number),
    KEY idx_companies_status (status),
    KEY idx_companies_next_email (next_email_at),
    KEY idx_companies_state (state),
    KEY idx_companies_city (city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS smtp_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_name VARCHAR(120) NOT NULL,
    host VARCHAR(255) NOT NULL,
    port SMALLINT UNSIGNED NOT NULL DEFAULT 587,
    encryption ENUM('tls','ssl','none') NOT NULL DEFAULT 'tls',
    username VARCHAR(254) NOT NULL,
    password_encrypted TEXT NOT NULL,
    from_name VARCHAR(180) NOT NULL,
    from_email VARCHAR(254) NOT NULL,
    reply_to_email VARCHAR(254) NULL,
    daily_limit INT UNSIGNED NOT NULL DEFAULT 300,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    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 email_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body_html MEDIUMTEXT NOT NULL,
    body_text MEDIUMTEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    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 campaigns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(180) NOT NULL,
    smtp_account_id BIGINT UNSIGNED NULL,
    status ENUM('draft','active','paused','completed') NOT NULL DEFAULT 'draft',
    batch_size SMALLINT UNSIGNED NOT NULL DEFAULT 10,
    daily_limit INT UNSIGNED NULL,
    starts_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_campaign_smtp FOREIGN KEY (smtp_account_id) REFERENCES smtp_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaign_steps (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT UNSIGNED NOT NULL,
    step_number SMALLINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED NOT NULL,
    delay_days_after_previous SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_campaign_step (campaign_id, step_number),
    CONSTRAINT fk_step_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    CONSTRAINT fk_step_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaign_contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT UNSIGNED NOT NULL,
    company_id BIGINT UNSIGNED NOT NULL,
    current_step SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    status ENUM('queued','sent','responded','paused','unsubscribed','bounced','completed') NOT NULL DEFAULT 'queued',
    next_send_at DATETIME NULL,
    last_sent_at DATETIME NULL,
    sent_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_campaign_company (campaign_id, company_id),
    KEY idx_campaign_contact_due (campaign_id, status, next_send_at),
    CONSTRAINT fk_cc_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    CONSTRAINT fk_cc_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT UNSIGNED NOT NULL,
    campaign_contact_id BIGINT UNSIGNED NOT NULL,
    company_id BIGINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED NOT NULL,
    scheduled_at DATETIME NOT NULL,
    status ENUM('pending','processing','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
    attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    locked_at DATETIME NULL,
    worker_token VARCHAR(80) 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,
    KEY idx_queue_due (status, scheduled_at),
    KEY idx_queue_company (company_id),
    CONSTRAINT fk_queue_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    CONSTRAINT fk_queue_cc FOREIGN KEY (campaign_contact_id) REFERENCES campaign_contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_queue_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_queue_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue_id BIGINT UNSIGNED NULL,
    campaign_id BIGINT UNSIGNED NULL,
    company_id BIGINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED NULL,
    recipient_email VARCHAR(254) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    send_status ENUM('sent','failed','bounced') NOT NULL,
    smtp_response TEXT NULL,
    message_id VARCHAR(255) NULL,
    sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_log_company (company_id, sent_at),
    KEY idx_log_campaign (campaign_id, sent_at),
    CONSTRAINT fk_log_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_log_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    CONSTRAINT fk_log_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE SET NULL,
    CONSTRAINT fk_log_queue FOREIGN KEY (queue_id) REFERENCES email_queue(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS suppression_list (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(254) NOT NULL,
    reason ENUM('unsubscribed','bounced','manual') NOT NULL DEFAULT 'manual',
    note TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_suppression_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    duplicate_mode ENUM('skip','update') NOT NULL DEFAULT 'skip',
    total_rows INT UNSIGNED NOT NULL DEFAULT 0,
    inserted_rows INT UNSIGNED NOT NULL DEFAULT 0,
    updated_rows INT UNSIGNED NOT NULL DEFAULT 0,
    skipped_rows INT UNSIGNED NOT NULL DEFAULT 0,
    failed_rows INT UNSIGNED NOT NULL DEFAULT 0,
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    CONSTRAINT fk_import_user FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_failures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    import_log_id BIGINT UNSIGNED NOT NULL,
    csv_row_number INT UNSIGNED NOT NULL,
    raw_data LONGTEXT NULL,
    error_message VARCHAR(500) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_import_failure_log (import_log_id),
    CONSTRAINT fk_failure_import FOREIGN KEY (import_log_id) REFERENCES import_logs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;