import mysql from "mysql2/promise";

import { DOMAIN_SETUP_ASSISTANCE_CHARGE, MAIL_GROWTH_PLAN_CHARGE_PER_MEMBER } from "@/lib/pricing";

declare global {
  var __officialMailPool: mysql.Pool | undefined;
  var __officialMailSchemaPromise: Promise<void> | undefined;
  var __officialMailSchemaVersion: number | undefined;
  var __googleSearchConsoleSchemaPromise: Promise<void> | undefined;
  var __marketingAnalyticsSchemaPromise: Promise<void> | undefined;
}

const OFFICIAL_MAIL_SCHEMA_VERSION = 2026100601;
const OFFICIAL_MAIL_SCHEMA_LOCK_NAME = "official_mail_schema_migrations";
const OFFICIAL_MAIL_SCHEMA_NAME = "official_mail";

function requireEnv(name: string, fallback?: string) {
  const value = process.env[name] ?? fallback;

  if (!value) {
    throw new Error(`${name} is required`);
  }

  return value;
}

export function getDbPool() {
  if (!global.__officialMailPool) {
    global.__officialMailPool = mysql.createPool({
      host: requireEnv("DB_HOST"),
      port: Number(requireEnv("DB_PORT", "3306")),
      user: requireEnv("DB_USER"),
      password: requireEnv("DB_PASSWORD"),
      database: process.env.DB_NAME ?? "official_mail",
      waitForConnections: true,
      connectionLimit: 10,
      namedPlaceholders: true,
      charset: "utf8mb4"
    });
  }

  return global.__officialMailPool;
}

async function hasColumn(tableName: string, columnName: string) {
  const pool = getDbPool();
  const [rows] = await pool.query<mysql.RowDataPacket[]>(
    `
      SELECT 1
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        AND COLUMN_NAME = ?
      LIMIT 1
    `,
    [tableName, columnName]
  );

  return rows.length > 0;
}

async function hasIndex(tableName: string, indexName: string) {
  const pool = getDbPool();
  const [rows] = await pool.query<mysql.RowDataPacket[]>(
    `
      SELECT 1
      FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        AND INDEX_NAME = ?
      LIMIT 1
    `,
    [tableName, indexName]
  );

  return rows.length > 0;
}

async function ensureColumn(tableName: string, columnName: string, ddl: string) {
  if (await hasColumn(tableName, columnName)) {
    return;
  }

  try {
    await getDbPool().query(ddl);
  } catch (error) {
    // Blue/green web and worker processes can overlap during deployment. If an
    // older process added the same column after our check, treat it as applied.
    if ((error as { code?: string } | null)?.code === "ER_DUP_FIELDNAME" && (await hasColumn(tableName, columnName))) {
      return;
    }

    throw error;
  }
}

async function ensureColumnType(tableName: string, columnName: string, expectedColumnType: string, ddl: string) {
  const [rows] = await getDbPool().query<mysql.RowDataPacket[]>(
    `
      SELECT COLUMN_TYPE
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        AND COLUMN_NAME = ?
      LIMIT 1
    `,
    [tableName, columnName]
  );
  const columnType = String(rows[0]?.COLUMN_TYPE ?? "").toLowerCase();

  if (!columnType || columnType === expectedColumnType.toLowerCase()) {
    return;
  }

  await getDbPool().query(ddl);
}

async function ensureIndex(tableName: string, indexName: string, ddl: string) {
  if (await hasIndex(tableName, indexName)) {
    return;
  }

  try {
    await getDbPool().query(ddl);
  } catch (error) {
    if ((error as { code?: string } | null)?.code === "ER_DUP_KEYNAME" && (await hasIndex(tableName, indexName))) {
      return;
    }

    throw error;
  }
}

async function ensureEnumValue(tableName: string, columnName: string, value: string, ddl: string) {
  const [rows] = await getDbPool().query<mysql.RowDataPacket[]>(
    `
      SELECT COLUMN_TYPE
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = ?
        AND COLUMN_NAME = ?
      LIMIT 1
    `,
    [tableName, columnName]
  );
  const columnType = String(rows[0]?.COLUMN_TYPE ?? "");

  if (columnType.includes(`'${value}'`)) {
    return;
  }

  await getDbPool().query(ddl);
}

async function ensureTable(ddl: string) {
  await getDbPool().query(ddl);
}

async function runOfficialMailSchemaMigrations() {
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS app_version_policies (
      platform VARCHAR(32) NOT NULL,
      latest_version VARCHAR(32) NOT NULL DEFAULT '0.0.0',
      latest_build INT UNSIGNED NOT NULL DEFAULT 0,
      minimum_version VARCHAR(32) NOT NULL DEFAULT '0.0.0',
      minimum_build INT UNSIGNED NOT NULL DEFAULT 0,
      update_mode ENUM('recommended', 'required') NOT NULL DEFAULT 'recommended',
      release_notes VARCHAR(1000) NOT NULL DEFAULT '',
      updated_by_email VARCHAR(320) NULL,
      updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (platform)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS app_version_settings (
      id TINYINT UNSIGNED NOT NULL,
      legacy_native_auth_enabled TINYINT(1) NOT NULL DEFAULT 1,
      updated_by_email VARCHAR(320) NULL,
      updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mail_sending_servers (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      server_key VARCHAR(64) NOT NULL,
      label VARCHAR(80) NOT NULL,
      host VARCHAR(253) NOT NULL,
      port SMALLINT UNSIGNED NOT NULL DEFAULT 587,
      security_mode ENUM('starttls', 'tls') NOT NULL DEFAULT 'starttls',
      tls_server_name VARCHAR(253) NULL,
      enabled TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (id),
      UNIQUE KEY uq_mail_sending_servers_key (server_key),
      KEY idx_mail_sending_servers_enabled_label (enabled, label)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureColumn(
    "users",
    "mail_sidebar_width",
    "ALTER TABLE users ADD COLUMN mail_sidebar_width SMALLINT UNSIGNED NOT NULL DEFAULT 270 AFTER mail_configured"
  );
  await ensureColumn(
    "users",
    "account_deleted_at",
    "ALTER TABLE users ADD COLUMN account_deleted_at DATETIME NULL AFTER mail_configured"
  );
  await ensureIndex(
    "users",
    "idx_users_account_deleted_at",
    "ALTER TABLE users ADD INDEX idx_users_account_deleted_at (account_deleted_at)"
  );

  await ensureColumn(
    "domains",
    "mailcow_cleanup_at",
    "ALTER TABLE domains ADD COLUMN mailcow_cleanup_at DATETIME NULL AFTER verified_at"
  );
  await ensureColumn(
    "domains",
    "connection_completed_email_sent_at",
    "ALTER TABLE domains ADD COLUMN connection_completed_email_sent_at DATETIME NULL AFTER verified_at"
  );
  await ensureColumn(
    "domains",
    "dkim_enabled",
    "ALTER TABLE domains ADD COLUMN dkim_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER mailcow_cleanup_at"
  );
  await ensureColumn(
    "domains",
    "operational_benefit_enabled",
    "ALTER TABLE domains ADD COLUMN operational_benefit_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER dkim_enabled"
  );
  await ensureColumn(
    "domains",
    "sending_server_key",
    "ALTER TABLE domains ADD COLUMN sending_server_key VARCHAR(64) NULL AFTER operational_benefit_enabled"
  );
  await ensureIndex(
    "domains",
    "idx_domains_sending_server_key",
    "ALTER TABLE domains ADD INDEX idx_domains_sending_server_key (sending_server_key)"
  );
  await ensureColumn(
    "users",
    "mail_list_width",
    "ALTER TABLE users ADD COLUMN mail_list_width SMALLINT UNSIGNED NOT NULL DEFAULT 456 AFTER mail_sidebar_width"
  );
  await ensureColumn(
    "users",
    "mail_list_height",
    "ALTER TABLE users ADD COLUMN mail_list_height SMALLINT UNSIGNED NOT NULL DEFAULT 420 AFTER mail_list_width"
  );
  await ensureColumn(
    "users",
    "mail_view_mode",
    "ALTER TABLE users ADD COLUMN mail_view_mode VARCHAR(32) NOT NULL DEFAULT 'list-only' AFTER mail_list_height"
  );
  await ensureColumn(
    "users",
    "mail_sort_order",
    "ALTER TABLE users ADD COLUMN mail_sort_order VARCHAR(16) NOT NULL DEFAULT 'latest' AFTER mail_view_mode"
  );
  await ensureColumn(
    "users",
    "preferred_locale",
    "ALTER TABLE users ADD COLUMN preferred_locale VARCHAR(8) NULL AFTER mail_sort_order"
  );
  await ensureColumn(
    "users",
    "mail_signature_enabled",
    "ALTER TABLE users ADD COLUMN mail_signature_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER preferred_locale"
  );
  await ensureColumn(
    "users",
    "mail_signature_html",
    "ALTER TABLE users ADD COLUMN mail_signature_html LONGTEXT NULL AFTER mail_signature_enabled"
  );
  await ensureColumn(
    "users",
    "mail_signature_text",
    "ALTER TABLE users ADD COLUMN mail_signature_text TEXT NULL AFTER mail_signature_html"
  );
  await ensureColumn(
    "users",
    "mail_signature_image_url",
    "ALTER TABLE users ADD COLUMN mail_signature_image_url VARCHAR(2048) NULL AFTER mail_signature_text"
  );
  await ensureColumn(
    "users",
    "mail_signature_image_key",
    "ALTER TABLE users ADD COLUMN mail_signature_image_key VARCHAR(512) NULL AFTER mail_signature_image_url"
  );
  await ensureColumn(
    "users",
    "mail_signature_image_width",
    "ALTER TABLE users ADD COLUMN mail_signature_image_width SMALLINT UNSIGNED NOT NULL DEFAULT 420 AFTER mail_signature_image_key"
  );
  await ensureColumn(
    "users",
    "recovery_email",
    "ALTER TABLE users ADD COLUMN recovery_email VARCHAR(320) NULL AFTER display_name"
  );
  await ensureColumn(
    "users",
    "recovery_email_verified_at",
    "ALTER TABLE users ADD COLUMN recovery_email_verified_at DATETIME NULL AFTER recovery_email"
  );
  await ensureIndex(
    "users",
    "idx_users_recovery_email",
    "ALTER TABLE users ADD INDEX idx_users_recovery_email (recovery_email, recovery_email_verified_at)"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS signup_recovery_email_verifications (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      email VARCHAR(320) NOT NULL,
      code_hash VARCHAR(255) NOT NULL,
      verification_token_hash CHAR(64) NULL,
      expires_at DATETIME NOT NULL,
      resend_available_at DATETIME NOT NULL,
      verified_at DATETIME NULL,
      consumed_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_srev_email_created (email, created_at),
      KEY idx_srev_expires (expires_at),
      KEY idx_srev_verified (verified_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS user_legal_consents (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      user_id BIGINT UNSIGNED NOT NULL,
      terms_version VARCHAR(32) NOT NULL,
      privacy_policy_version VARCHAR(32) NOT NULL,
      consent_source VARCHAR(32) NOT NULL DEFAULT 'signup',
      accepted_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      PRIMARY KEY (id),
      UNIQUE KEY uq_user_legal_consent_versions (
        user_id,
        terms_version,
        privacy_policy_version
      ),
      KEY idx_user_legal_consents_accepted (accepted_at),
      CONSTRAINT fk_user_legal_consents_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS password_reset_tokens (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      user_id BIGINT UNSIGNED NOT NULL,
      recovery_email VARCHAR(320) NOT NULL,
      token_hash CHAR(64) NOT NULL,
      expires_at DATETIME NOT NULL,
      used_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_password_reset_tokens_hash (token_hash),
      KEY idx_password_reset_tokens_user_id (user_id),
      KEY idx_password_reset_tokens_expires (expires_at),
      CONSTRAINT fk_password_reset_tokens_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS find_id_email_request_limits (
      recovery_email VARCHAR(320) NOT NULL,
      window_started_at DATETIME(3) NOT NULL,
      request_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
      created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (recovery_email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS user_auth_activity_events (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      user_id BIGINT UNSIGNED NOT NULL,
      event_type VARCHAR(32) NOT NULL,
      outcome VARCHAR(32) NOT NULL,
      source VARCHAR(16) NOT NULL,
      identity_email VARCHAR(320) NOT NULL,
      ip_address VARCHAR(45) NULL,
      user_agent VARCHAR(512) NULL,
      occurred_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      PRIMARY KEY (id),
      KEY idx_user_auth_activity_user_time (user_id, occurred_at),
      KEY idx_user_auth_activity_identity_time (identity_email, occurred_at),
      CONSTRAINT fk_user_auth_activity_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureIndex("users", "idx_users_created_at", "ALTER TABLE users ADD INDEX idx_users_created_at (created_at)");
  await ensureIndex(
    "domains",
    "idx_domains_status_verified_at",
    "ALTER TABLE domains ADD INDEX idx_domains_status_verified_at (status, verified_at)"
  );
  await ensureIndex(
    "mailbox_messages",
    "idx_mailbox_messages_activity",
    "ALTER TABLE mailbox_messages ADD INDEX idx_mailbox_messages_activity (received_at, direction, transport_status, mailbox_id)"
  );
  await ensureIndex(
    "mailbox_toss_pay_billing_charges",
    "idx_billing_charges_status_requested_at",
    "ALTER TABLE mailbox_toss_pay_billing_charges ADD INDEX idx_billing_charges_status_requested_at (status, requested_at)"
  );
  await ensureIndex(
    "user_auth_activity_events",
    "idx_user_auth_activity_time_event",
    "ALTER TABLE user_auth_activity_events ADD INDEX idx_user_auth_activity_time_event (occurred_at, event_type, outcome)"
  );
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS user_remember_devices (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      user_id BIGINT UNSIGNED NOT NULL,
      selector CHAR(32) NOT NULL,
      token_hash CHAR(64) NOT NULL,
      device_id_hash CHAR(64) NOT NULL,
      user_agent VARCHAR(512) NULL,
      expires_at DATETIME NOT NULL,
      last_used_at DATETIME NULL,
      revoked_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_user_remember_devices_selector (selector),
      KEY idx_user_remember_devices_user (user_id, revoked_at, expires_at),
      KEY idx_user_remember_devices_expiry (expires_at),
      CONSTRAINT fk_user_remember_devices_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_app_sessions (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      user_id BIGINT UNSIGNED NOT NULL,
      selector CHAR(32) NOT NULL,
      token_hash CHAR(64) NOT NULL,
      expires_at DATETIME NOT NULL,
      last_used_at DATETIME NULL,
      revoked_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_app_sessions_selector (selector),
      KEY idx_mailbox_app_sessions_user (user_id, revoked_at, expires_at),
      KEY idx_mailbox_app_sessions_expiry (expires_at),
      CONSTRAINT fk_mailbox_app_sessions_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureIndex(
    "user_remember_devices",
    "idx_user_remember_devices_created_user",
    "ALTER TABLE user_remember_devices ADD INDEX idx_user_remember_devices_created_user (created_at, user_id)"
  );
  await ensureIndex(
    "mailbox_app_sessions",
    "idx_mailbox_app_sessions_created_user",
    "ALTER TABLE mailbox_app_sessions ADD INDEX idx_mailbox_app_sessions_created_user (created_at, user_id)"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_push_devices (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      user_id BIGINT UNSIGNED NOT NULL,
      mailbox_email VARCHAR(320) NOT NULL,
      provider VARCHAR(16) NOT NULL DEFAULT 'fcm',
      platform VARCHAR(16) NOT NULL,
      locale VARCHAR(8) NULL,
      notification_mode VARCHAR(32) NOT NULL DEFAULT 'system',
      device_token VARCHAR(2048) NOT NULL,
      token_hash CHAR(64) NOT NULL,
      enabled TINYINT(1) NOT NULL DEFAULT 1,
      last_notified_message_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
      last_registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      last_success_at DATETIME NULL,
      last_failure_at DATETIME NULL,
      failure_code VARCHAR(120) NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_push_devices_token_hash (token_hash),
      KEY idx_mailbox_push_devices_mailbox (mailbox_email, enabled),
      KEY idx_mailbox_push_devices_user (user_id, enabled),
      CONSTRAINT fk_mailbox_push_devices_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureColumn(
    "mailbox_push_devices",
    "notification_mode",
    "ALTER TABLE mailbox_push_devices ADD COLUMN notification_mode VARCHAR(32) NOT NULL DEFAULT 'system' AFTER locale"
  );

  await ensureColumn(
    "mailboxes",
    "password_ciphertext",
    "ALTER TABLE mailboxes ADD COLUMN password_ciphertext LONGTEXT NULL AFTER email"
  );
  await ensureColumn(
    "mailboxes",
    "password_updated_at",
    "ALTER TABLE mailboxes ADD COLUMN password_updated_at DATETIME NULL AFTER password_ciphertext"
  );
  await ensureColumn(
    "mailboxes",
    "native_app_password_ciphertext",
    "ALTER TABLE mailboxes ADD COLUMN native_app_password_ciphertext LONGTEXT NULL AFTER password_ciphertext"
  );
  await ensureColumn(
    "mailboxes",
    "legacy_app_compat_enabled",
    "ALTER TABLE mailboxes ADD COLUMN legacy_app_compat_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER native_app_password_ciphertext"
  );
  await ensureColumn(
    "mailboxes",
    "external_access_enabled",
    "ALTER TABLE mailboxes ADD COLUMN external_access_enabled TINYINT(1) NULL DEFAULT NULL AFTER password_updated_at"
  );
  await ensureColumn(
    "mailboxes",
    "external_app_password_ciphertext",
    "ALTER TABLE mailboxes ADD COLUMN external_app_password_ciphertext LONGTEXT NULL AFTER external_access_enabled"
  );
  await ensureColumn(
    "mailboxes",
    "external_access_checked_at",
    "ALTER TABLE mailboxes ADD COLUMN external_access_checked_at DATETIME NULL AFTER external_access_enabled"
  );
  await ensureColumn(
    "mailboxes",
    "pop3_access_enabled",
    "ALTER TABLE mailboxes ADD COLUMN pop3_access_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER external_access_enabled"
  );
  await ensureIndex(
    "mailboxes",
    "idx_mailboxes_external_access_check",
    "CREATE INDEX idx_mailboxes_external_access_check ON mailboxes (status, external_access_checked_at)"
  );
  await ensureColumn(
    "mailboxes",
    "last_sync_at",
    "ALTER TABLE mailboxes ADD COLUMN last_sync_at DATETIME NULL AFTER password_updated_at"
  );
  await ensureColumn(
    "mailboxes",
    "full_sync_requested_at",
    "ALTER TABLE mailboxes ADD COLUMN full_sync_requested_at DATETIME(3) NULL AFTER last_sync_at"
  );
  await ensureColumn(
    "mailboxes",
    "last_sync_error",
    "ALTER TABLE mailboxes ADD COLUMN last_sync_error LONGTEXT NULL AFTER full_sync_requested_at"
  );
  await ensureColumn(
    "mailboxes",
    "sync_retry_attempts",
    "ALTER TABLE mailboxes ADD COLUMN sync_retry_attempts INT UNSIGNED NOT NULL DEFAULT 0 AFTER last_sync_error"
  );
  await ensureColumn(
    "mailboxes",
    "next_sync_retry_at",
    "ALTER TABLE mailboxes ADD COLUMN next_sync_retry_at DATETIME NULL AFTER sync_retry_attempts"
  );
  await ensureIndex(
    "mailboxes",
    "idx_mailboxes_sync_retry",
    "CREATE INDEX idx_mailboxes_sync_retry ON mailboxes (status, next_sync_retry_at)"
  );
  await ensureColumn(
    "mailbox_folders",
    "last_content_synced_at",
    "ALTER TABLE mailbox_folders ADD COLUMN last_content_synced_at DATETIME NULL AFTER last_synced_at"
  );
  await ensureColumn(
    "mailbox_folders",
    "last_content_sync_error",
    "ALTER TABLE mailbox_folders ADD COLUMN last_content_sync_error LONGTEXT NULL AFTER last_content_synced_at"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_primary_email_changes (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      domain_id BIGINT UNSIGNED NOT NULL,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      old_email VARCHAR(320) NOT NULL,
      new_email VARCHAR(320) NOT NULL,
      old_local_part VARCHAR(128) NOT NULL,
      new_local_part VARCHAR(128) NOT NULL,
      requested_by_email VARCHAR(320) NOT NULL,
      status ENUM('pending', 'completed', 'failed') NOT NULL DEFAULT 'pending',
      mailcow_completed_at DATETIME NULL,
      completed_at DATETIME NULL,
      error_message VARCHAR(1000) NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_mailbox_primary_email_changes_domain (domain_id, created_at),
      KEY idx_mailbox_primary_email_changes_owner (owner_user_id, created_at),
      KEY idx_mailbox_primary_email_changes_old (old_email, status),
      KEY idx_mailbox_primary_email_changes_retry (domain_id, old_email, new_email, status),
      CONSTRAINT fk_mailbox_primary_email_changes_domain
        FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_primary_email_changes_owner
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_primary_email_changes_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "mailboxes",
    "message_visibility_cutoff_at",
    "ALTER TABLE mailboxes ADD COLUMN message_visibility_cutoff_at DATETIME NULL AFTER last_sync_error"
  );
  await ensureColumn(
    "mailboxes",
    "send_cooldown_until",
    "ALTER TABLE mailboxes ADD COLUMN send_cooldown_until DATETIME NULL AFTER message_visibility_cutoff_at"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_provisioning_recovery_jobs (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      domain_id BIGINT UNSIGNED NOT NULL,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      status ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
      attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
      process_after_at DATETIME NOT NULL,
      processing_started_at DATETIME NULL,
      completed_at DATETIME NULL,
      last_error VARCHAR(500) NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_provisioning_recovery_mailbox (mailbox_id),
      KEY idx_mailbox_provisioning_recovery_due (status, process_after_at),
      KEY idx_mailbox_provisioning_recovery_domain (domain_id),
      CONSTRAINT fk_mailbox_provisioning_recovery_owner
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_provisioning_recovery_domain
        FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_provisioning_recovery_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_message_move_jobs (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      source_folder VARCHAR(191) NOT NULL,
      target_folder VARCHAR(191) NOT NULL,
      message_ids_json LONGTEXT NOT NULL,
      release_spam TINYINT(1) NOT NULL DEFAULT 0,
      status ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
      attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
      process_after_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      processing_started_at DATETIME(3) NULL,
      completed_at DATETIME(3) NULL,
      last_error VARCHAR(500) NULL,
      result_json LONGTEXT NULL,
      undo_payload_json LONGTEXT NULL,
      undo_job_ids_json LONGTEXT NULL,
      created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (id),
      KEY idx_mailbox_message_move_due (status, process_after_at),
      KEY idx_mailbox_message_move_mailbox (mailbox_id, created_at),
      CONSTRAINT fk_mailbox_message_move_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureColumn(
    "mailbox_message_move_jobs",
    "undo_payload_json",
    "ALTER TABLE mailbox_message_move_jobs ADD COLUMN undo_payload_json LONGTEXT NULL AFTER result_json"
  );
  await ensureColumn(
    "mailbox_message_move_jobs",
    "undo_job_ids_json",
    "ALTER TABLE mailbox_message_move_jobs ADD COLUMN undo_job_ids_json LONGTEXT NULL AFTER undo_payload_json"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_message_move_job_items (
      job_id BIGINT UNSIGNED NOT NULL,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      mailbox_message_id BIGINT UNSIGNED NOT NULL,
      created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      PRIMARY KEY (job_id, mailbox_message_id),
      UNIQUE KEY uq_mailbox_message_move_pending (mailbox_id, mailbox_message_id),
      KEY idx_mailbox_message_move_item_message (mailbox_message_id),
      CONSTRAINT fk_mailbox_message_move_item_job
        FOREIGN KEY (job_id) REFERENCES mailbox_message_move_jobs(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_message_move_item_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_message_move_item_message
        FOREIGN KEY (mailbox_message_id) REFERENCES mailbox_messages(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureColumn(
    "mailbox_folders",
    "remote_name",
    "ALTER TABLE mailbox_folders ADD COLUMN remote_name VARCHAR(512) NULL AFTER system_name"
  );
  await ensureColumn(
    "mailbox_folders",
    "remote_total",
    "ALTER TABLE mailbox_folders ADD COLUMN remote_total INT NOT NULL DEFAULT 0 AFTER name"
  );
  await ensureColumn(
    "mailbox_folders",
    "remote_unseen",
    "ALTER TABLE mailbox_folders ADD COLUMN remote_unseen INT NOT NULL DEFAULT 0 AFTER remote_total"
  );
  await ensureColumn(
    "mailbox_folders",
    "last_synced_at",
    "ALTER TABLE mailbox_folders ADD COLUMN last_synced_at DATETIME NULL AFTER remote_unseen"
  );

  await ensureColumn(
    "mailbox_messages",
    "remote_uid",
    "ALTER TABLE mailbox_messages ADD COLUMN remote_uid BIGINT UNSIGNED NULL AFTER folder_id"
  );
  await ensureColumn(
    "mailbox_messages",
    "body_html",
    "ALTER TABLE mailbox_messages ADD COLUMN body_html LONGTEXT NULL AFTER body_text"
  );
  await ensureColumn(
    "mailbox_messages",
    "remote_folder",
    "ALTER TABLE mailbox_messages ADD COLUMN remote_folder VARCHAR(512) NULL AFTER remote_uid"
  );
  await ensureColumn(
    "mailbox_messages",
    "remote_flags",
    "ALTER TABLE mailbox_messages ADD COLUMN remote_flags TEXT NULL AFTER transport_response"
  );
  await ensureColumn(
    "mailbox_messages",
    "remote_internal_date",
    "ALTER TABLE mailbox_messages ADD COLUMN remote_internal_date DATETIME NULL AFTER received_at"
  );
  await ensureColumn(
    "mailbox_messages",
    "synced_at",
    "ALTER TABLE mailbox_messages ADD COLUMN synced_at DATETIME NULL AFTER remote_internal_date"
  );
  await ensureColumn(
    "mailbox_messages",
    "is_starred",
    "ALTER TABLE mailbox_messages ADD COLUMN is_starred TINYINT(1) NOT NULL DEFAULT 0 AFTER is_read"
  );
  await ensureColumn(
    "mailbox_messages",
    "attachments_indexed_at",
    "ALTER TABLE mailbox_messages ADD COLUMN attachments_indexed_at DATETIME NULL AFTER synced_at"
  );
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_message_translations (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      mailbox_message_id BIGINT UNSIGNED NOT NULL,
      target_locale VARCHAR(8) NOT NULL,
      source_hash CHAR(64) NOT NULL,
      source_language_code VARCHAR(16) NOT NULL,
      source_language_label VARCHAR(80) NOT NULL,
      translation_status ENUM('translated', 'same_language') NOT NULL,
      translated_body_html LONGTEXT NULL,
      translated_body_text LONGTEXT NULL,
      provider VARCHAR(32) NOT NULL DEFAULT 'llm_gateway',
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_message_translation_target (mailbox_message_id, target_locale),
      KEY idx_mailbox_message_translations_updated (updated_at),
      CONSTRAINT fk_mailbox_message_translations_message
        FOREIGN KEY (mailbox_message_id) REFERENCES mailbox_messages(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureIndex(
    "mailbox_messages",
    "uq_mailbox_messages_remote",
    "ALTER TABLE mailbox_messages ADD UNIQUE KEY uq_mailbox_messages_remote (mailbox_id, remote_folder, remote_uid)"
  );
  await ensureColumnType(
    "mailbox_delivery_logs",
    "target_address",
    "text",
    "ALTER TABLE mailbox_delivery_logs MODIFY COLUMN target_address TEXT NOT NULL"
  );
  const widenedColumnMigrations = [
    ["users", "email", "varchar(320)", "ALTER TABLE users MODIFY COLUMN email VARCHAR(320) NOT NULL"],
    ["users", "recovery_email", "varchar(320)", "ALTER TABLE users MODIFY COLUMN recovery_email VARCHAR(320) NULL"],
    [
      "signup_recovery_email_verifications",
      "email",
      "varchar(320)",
      "ALTER TABLE signup_recovery_email_verifications MODIFY COLUMN email VARCHAR(320) NOT NULL"
    ],
    [
      "password_reset_tokens",
      "recovery_email",
      "varchar(320)",
      "ALTER TABLE password_reset_tokens MODIFY COLUMN recovery_email VARCHAR(320) NOT NULL"
    ],
    ["domains", "domain", "varchar(253)", "ALTER TABLE domains MODIFY COLUMN domain VARCHAR(253) NOT NULL"],
    ["mailboxes", "email", "varchar(320)", "ALTER TABLE mailboxes MODIFY COLUMN email VARCHAR(320) NOT NULL"],
    [
      "managed_team_mailboxes",
      "email",
      "varchar(320)",
      "ALTER TABLE managed_team_mailboxes MODIFY COLUMN email VARCHAR(320) NOT NULL"
    ],
    [
      "domain_setup_assistance_requests",
      "domain",
      "varchar(253)",
      "ALTER TABLE domain_setup_assistance_requests MODIFY COLUMN domain VARCHAR(253) NOT NULL"
    ],
    [
      "domain_setup_assistance_requests",
      "notification_email",
      "varchar(320)",
      "ALTER TABLE domain_setup_assistance_requests MODIFY COLUMN notification_email VARCHAR(320) NULL"
    ],
    [
      "mailbox_ai_assist_settings",
      "notification_email",
      "varchar(320)",
      "ALTER TABLE mailbox_ai_assist_settings MODIFY COLUMN notification_email VARCHAR(320) NULL"
    ],
    [
      "mailbox_ai_assist_settings",
      "assistant_mailbox_email",
      "varchar(320)",
      "ALTER TABLE mailbox_ai_assist_settings MODIFY COLUMN assistant_mailbox_email VARCHAR(320) NOT NULL"
    ],
    [
      "mailbox_ai_assist_threads",
      "notification_email",
      "varchar(320)",
      "ALTER TABLE mailbox_ai_assist_threads MODIFY COLUMN notification_email VARCHAR(320) NOT NULL"
    ],
    [
      "mailbox_ai_assist_threads",
      "original_sender_email",
      "varchar(320)",
      "ALTER TABLE mailbox_ai_assist_threads MODIFY COLUMN original_sender_email VARCHAR(320) NOT NULL"
    ],
    [
      "mailbox_ai_assist_threads",
      "original_sender_name",
      "text",
      "ALTER TABLE mailbox_ai_assist_threads MODIFY COLUMN original_sender_name TEXT NULL"
    ],
    [
      "mailbox_ai_assist_threads",
      "original_subject",
      "text",
      "ALTER TABLE mailbox_ai_assist_threads MODIFY COLUMN original_subject TEXT NOT NULL"
    ],
    [
      "mailbox_ai_assist_threads",
      "summary_message_id_header",
      "varchar(1024)",
      "ALTER TABLE mailbox_ai_assist_threads MODIFY COLUMN summary_message_id_header VARCHAR(1024) NULL"
    ],
    [
      "mailbox_ai_assist_replies",
      "remote_message_key",
      "varchar(600)",
      "ALTER TABLE mailbox_ai_assist_replies MODIFY COLUMN remote_message_key VARCHAR(600) NOT NULL"
    ],
    [
      "mailbox_ai_assist_replies",
      "message_id_header",
      "varchar(1024)",
      "ALTER TABLE mailbox_ai_assist_replies MODIFY COLUMN message_id_header VARCHAR(1024) NULL"
    ],
    [
      "mailbox_folders",
      "remote_name",
      "varchar(512)",
      "ALTER TABLE mailbox_folders MODIFY COLUMN remote_name VARCHAR(512) NULL"
    ],
    [
      "mailbox_messages",
      "remote_folder",
      "varchar(512)",
      "ALTER TABLE mailbox_messages MODIFY COLUMN remote_folder VARCHAR(512) NULL"
    ],
    ["mailbox_messages", "subject", "text", "ALTER TABLE mailbox_messages MODIFY COLUMN subject TEXT NOT NULL"],
    ["mailbox_messages", "from_name", "text", "ALTER TABLE mailbox_messages MODIFY COLUMN from_name TEXT NULL"],
    [
      "mailbox_messages",
      "from_address",
      "varchar(320)",
      "ALTER TABLE mailbox_messages MODIFY COLUMN from_address VARCHAR(320) NOT NULL"
    ],
    [
      "mailbox_messages",
      "message_id_header",
      "varchar(1024)",
      "ALTER TABLE mailbox_messages MODIFY COLUMN message_id_header VARCHAR(1024) NULL"
    ],
    ["mailbox_messages", "remote_flags", "text", "ALTER TABLE mailbox_messages MODIFY COLUMN remote_flags TEXT NULL"],
    [
      "mailbox_delivery_logs",
      "source_address",
      "varchar(320)",
      "ALTER TABLE mailbox_delivery_logs MODIFY COLUMN source_address VARCHAR(320) NOT NULL"
    ],
    [
      "mailbox_delivery_logs",
      "subject",
      "text",
      "ALTER TABLE mailbox_delivery_logs MODIFY COLUMN subject TEXT NOT NULL"
    ],
    [
      "mailbox_delivery_logs",
      "message_id_header",
      "varchar(1024)",
      "ALTER TABLE mailbox_delivery_logs MODIFY COLUMN message_id_header VARCHAR(1024) NULL"
    ],
    [
      "mailbox_toss_pay_billing_cancellation_requests",
      "requested_by_email",
      "varchar(320)",
      "ALTER TABLE mailbox_toss_pay_billing_cancellation_requests MODIFY COLUMN requested_by_email VARCHAR(320) NOT NULL"
    ],
    [
      "mailbox_toss_pay_billing_cancellation_requests",
      "reviewed_by_email",
      "varchar(320)",
      "ALTER TABLE mailbox_toss_pay_billing_cancellation_requests MODIFY COLUMN reviewed_by_email VARCHAR(320) NULL"
    ],
    [
      "mailbox_toss_pay_billing_refunds",
      "requested_by_email",
      "varchar(320)",
      "ALTER TABLE mailbox_toss_pay_billing_refunds MODIFY COLUMN requested_by_email VARCHAR(320) NOT NULL"
    ],
    [
      "google_search_console_connections",
      "account_email",
      "varchar(320)",
      "ALTER TABLE google_search_console_connections MODIFY COLUMN account_email VARCHAR(320) NOT NULL"
    ],
    [
      "marketing_blog_posts",
      "created_by_email",
      "varchar(320)",
      "ALTER TABLE marketing_blog_posts MODIFY COLUMN created_by_email VARCHAR(320) NULL"
    ],
    [
      "marketing_blog_images",
      "created_by_email",
      "varchar(320)",
      "ALTER TABLE marketing_blog_images MODIFY COLUMN created_by_email VARCHAR(320) NOT NULL"
    ]
  ] as const;

  for (const [tableName, columnName, expectedType, ddl] of widenedColumnMigrations) {
    await ensureColumnType(tableName, columnName, expectedType, ddl);
  }

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_sender_rules (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      sender_address VARCHAR(320) NOT NULL,
      rule_action ENUM('spam') NOT NULL DEFAULT 'spam',
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_sender_rules_mailbox_sender (mailbox_id, sender_address),
      KEY idx_mailbox_sender_rules_sender (sender_address),
      CONSTRAINT fk_mailbox_sender_rules_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureEnumValue(
    "mailbox_sender_rules",
    "rule_action",
    "allow",
    "ALTER TABLE mailbox_sender_rules MODIFY COLUMN rule_action ENUM('spam','allow') NOT NULL DEFAULT 'spam'"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_sender_move_rules (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      sender_address VARCHAR(320) NOT NULL,
      target_folder_id BIGINT UNSIGNED NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_sender_move_rules_mailbox_sender (mailbox_id, sender_address),
      KEY idx_mailbox_sender_move_rules_target (target_folder_id),
      CONSTRAINT fk_mailbox_sender_move_rules_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_sender_move_rules_target_folder
        FOREIGN KEY (target_folder_id) REFERENCES mailbox_folders(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_contacts (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      email VARCHAR(320) NOT NULL,
      display_name VARCHAR(191) NOT NULL DEFAULT '',
      company_name VARCHAR(191) NOT NULL DEFAULT '',
      phone VARCHAR(40) NOT NULL DEFAULT '',
      group_name VARCHAR(120) NOT NULL DEFAULT '',
      memo TEXT NULL,
      is_vip TINYINT(1) NOT NULL DEFAULT 0,
      auto_move_rule_id BIGINT UNSIGNED NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_contacts_mailbox_email (mailbox_id, email),
      KEY idx_mailbox_contacts_mailbox_group (mailbox_id, group_name),
      KEY idx_mailbox_contacts_auto_move_rule (auto_move_rule_id),
      CONSTRAINT fk_mailbox_contacts_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_contacts_auto_move_rule
        FOREIGN KEY (auto_move_rule_id) REFERENCES mailbox_sender_move_rules(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_contact_groups (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      name VARCHAR(120) NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_contact_groups_mailbox_name (mailbox_id, name),
      CONSTRAINT fk_mailbox_contact_groups_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_contact_ignored_addresses (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      email VARCHAR(320) NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_contact_ignored_mailbox_email (mailbox_id, email),
      CONSTRAINT fk_mailbox_contact_ignored_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_custom_spam_rules (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      match_field ENUM('sender','sender_domain','subject','body') NOT NULL,
      match_value VARCHAR(500) NOT NULL,
      rule_action ENUM('spam','allow') NOT NULL DEFAULT 'spam',
      enabled TINYINT(1) NOT NULL DEFAULT 1,
      priority 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,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_custom_spam_rule (mailbox_id, match_field, match_value, rule_action),
      KEY idx_mailbox_custom_spam_rules_apply (mailbox_id, enabled, priority, id),
      CONSTRAINT fk_mailbox_custom_spam_rules_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_spam_settings (
      mailbox_id BIGINT UNSIGNED NOT NULL,
      auto_move_spam TINYINT(1) NOT NULL DEFAULT 1,
      detect_self_spoofing TINYINT(1) NOT NULL DEFAULT 1,
      detect_bounce_spoofing TINYINT(1) NOT NULL DEFAULT 0,
      detect_missing_recipient TINYINT(1) NOT NULL DEFAULT 0,
      detect_unknown_sender TINYINT(1) NOT NULL DEFAULT 0,
      language_filter_enabled TINYINT(1) NOT NULL DEFAULT 0,
      language_filter_mode ENUM('all','selected') NOT NULL DEFAULT 'selected',
      language_filter_languages VARCHAR(64) NOT NULL DEFAULT 'en',
      reported_message_action ENUM('spam','delete') NOT NULL DEFAULT 'spam',
      block_reported_sender TINYINT(1) NOT NULL DEFAULT 1,
      process_previous_reported TINYINT(1) NOT NULL DEFAULT 1,
      hide_spam_folder TINYINT(1) NOT NULL DEFAULT 0,
      retention_days SMALLINT UNSIGNED NOT NULL DEFAULT 30,
      expired_message_action ENUM('trash','delete') NOT NULL DEFAULT 'delete',
      hide_remote_content 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,
      PRIMARY KEY (mailbox_id),
      CONSTRAINT fk_mailbox_spam_settings_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureColumn(
    "mailbox_spam_settings",
    "language_filter_mode",
    "ALTER TABLE mailbox_spam_settings ADD COLUMN language_filter_mode ENUM('all','selected') NOT NULL DEFAULT 'selected' AFTER language_filter_enabled"
  );
  await ensureColumn(
    "mailbox_spam_settings",
    "language_filter_languages",
    "ALTER TABLE mailbox_spam_settings ADD COLUMN language_filter_languages VARCHAR(64) NOT NULL DEFAULT 'en' AFTER language_filter_mode"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS managed_team_mailboxes (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      domain_id BIGINT UNSIGNED NOT NULL,
      local_part VARCHAR(128) NOT NULL,
      email VARCHAR(320) NOT NULL,
      display_name VARCHAR(191) NOT NULL,
      password_ciphertext LONGTEXT NOT NULL,
      status ENUM('active', 'disabled') NOT NULL DEFAULT 'active',
      billing_suspended_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_managed_team_mailboxes_email (email),
      UNIQUE KEY uq_managed_team_mailboxes_owner_local_part (owner_user_id, local_part),
      KEY idx_managed_team_mailboxes_owner_user_id (owner_user_id),
      KEY idx_managed_team_mailboxes_domain_id (domain_id),
      CONSTRAINT fk_managed_team_mailboxes_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_managed_team_mailboxes_domain
        FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureEnumValue(
    "managed_team_mailboxes",
    "status",
    "disabled",
    "ALTER TABLE managed_team_mailboxes MODIFY COLUMN status ENUM('active', 'disabled') NOT NULL DEFAULT 'active'"
  );
  await ensureColumn(
    "managed_team_mailboxes",
    "billing_suspended_at",
    "ALTER TABLE managed_team_mailboxes ADD COLUMN billing_suspended_at DATETIME NULL AFTER status"
  );
  await ensureEnumValue(
    "mailboxes",
    "status",
    "disabled",
    "ALTER TABLE mailboxes MODIFY COLUMN status ENUM('pending_dns', 'active', 'disabled') NOT NULL DEFAULT 'pending_dns'"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_ai_assist_settings (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      enabled TINYINT(1) NOT NULL DEFAULT 0,
      notification_email VARCHAR(320) NULL,
      assistant_mailbox_email VARCHAR(320) NOT NULL,
      baseline_message_id BIGINT UNSIGNED NULL,
      last_summarized_at DATETIME NULL,
      last_relayed_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_ai_assist_settings_owner_user (owner_user_id),
      KEY idx_mailbox_ai_assist_settings_mailbox_id (mailbox_id),
      CONSTRAINT fk_mailbox_ai_assist_settings_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_ai_assist_settings_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_auto_send_settings (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      enabled TINYINT(1) NOT NULL DEFAULT 0,
      subject VARCHAR(255) NOT NULL DEFAULT '',
      random_enabled TINYINT(1) NOT NULL DEFAULT 1,
      recipient_duplicate_scope VARCHAR(16) NOT NULL DEFAULT 'pending',
      send_interval_preset VARCHAR(16) NOT NULL DEFAULT 'daily',
      send_interval_minutes INT UNSIGNED NOT NULL DEFAULT 1440,
      max_send_count TINYINT UNSIGNED NOT NULL DEFAULT 30,
      send_batch_size TINYINT UNSIGNED NOT NULL DEFAULT 1,
      log_page_size SMALLINT UNSIGNED NOT NULL DEFAULT 10,
      next_run_at DATETIME NULL,
      last_sent_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_auto_send_settings_owner_user (owner_user_id),
      KEY idx_mailbox_auto_send_settings_mailbox_id (mailbox_id),
      KEY idx_mailbox_auto_send_settings_due (enabled, next_run_at),
      CONSTRAINT fk_mailbox_auto_send_settings_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_auto_send_settings_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS domain_setup_promotion_codes (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      code VARCHAR(64) NOT NULL,
      label VARCHAR(191) NOT NULL,
      discount_type ENUM('fixed', 'percent') NOT NULL DEFAULT 'percent',
      discount_value INT UNSIGNED NOT NULL DEFAULT 100,
      usage_limit INT UNSIGNED NULL,
      per_user_limit SMALLINT UNSIGNED NOT NULL DEFAULT 1,
      starts_at DATETIME NULL,
      expires_at DATETIME NULL,
      is_active TINYINT(1) NOT NULL DEFAULT 1,
      created_by_email VARCHAR(320) NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_domain_setup_promotion_codes_code (code),
      KEY idx_domain_setup_promotion_codes_active_period (is_active, starts_at, expires_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS domain_dkim_reminder_deliveries (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      domain_id BIGINT UNSIGNED NOT NULL,
      user_id BIGINT UNSIGNED NOT NULL,
      reminder_type ENUM('initial_dns', 'dkim_rotation') NOT NULL,
      recipient_email VARCHAR(320) NOT NULL,
      subject VARCHAR(255) NOT NULL,
      body_text LONGTEXT NOT NULL,
      status ENUM('sending', 'sent', 'failed') NOT NULL DEFAULT 'sending',
      error_message VARCHAR(500) NULL,
      requested_by_email VARCHAR(320) NOT NULL,
      sent_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_domain_dkim_reminders_domain_created (domain_id, created_at),
      KEY idx_domain_dkim_reminders_recipient_created (recipient_email, created_at),
      KEY idx_domain_dkim_reminders_status_created (status, created_at),
      CONSTRAINT fk_domain_dkim_reminders_domain
        FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE,
      CONSTRAINT fk_domain_dkim_reminders_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS domain_setup_assistance_requests (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      user_id BIGINT UNSIGNED NOT NULL,
      domain_id BIGINT UNSIGNED NULL,
      domain VARCHAR(253) NOT NULL,
      provider VARCHAR(80) NOT NULL,
      account_identifier_ciphertext LONGTEXT NULL,
      account_password_ciphertext LONGTEXT NULL,
      notification_email VARCHAR(320) NULL,
      requester_note TEXT NULL,
      original_fee_amount INT UNSIGNED NOT NULL DEFAULT 10890,
      discount_amount INT UNSIGNED NOT NULL DEFAULT 0,
      fee_amount INT UNSIGNED NOT NULL DEFAULT 10890,
      promotion_code_id BIGINT UNSIGNED NULL,
      promotion_code VARCHAR(64) NULL,
      billing_profile_id BIGINT UNSIGNED NULL,
      billing_charge_id BIGINT UNSIGNED NULL,
      payment_status ENUM('ready', 'processing', 'paid', 'failed', 'cancelled', 'not_applicable') NOT NULL DEFAULT 'ready',
      payment_error_message TEXT NULL,
      payment_consent_version VARCHAR(32) NULL,
      payment_consented_at DATETIME NULL,
      charged_at DATETIME NULL,
      consent_version VARCHAR(32) NOT NULL,
      consented_at DATETIME NOT NULL,
      credentials_expire_at DATETIME NOT NULL,
      credentials_purged_at DATETIME NULL,
      status ENUM('pending', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
      admin_note TEXT NULL,
      requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      started_at DATETIME NULL,
      completed_at DATETIME NULL,
      cancelled_at DATETIME NULL,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_domain_setup_assistance_user_status (user_id, status, requested_at),
      KEY idx_domain_setup_assistance_domain_status (domain_id, status, requested_at),
      KEY idx_domain_setup_assistance_status_requested (status, requested_at),
      KEY idx_domain_setup_assistance_credentials_expire (credentials_expire_at, credentials_purged_at),
      CONSTRAINT fk_domain_setup_assistance_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_domain_setup_assistance_domain
        FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS domain_setup_promotion_redemptions (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      promotion_code_id BIGINT UNSIGNED NOT NULL,
      assistance_request_id BIGINT UNSIGNED NOT NULL,
      user_id BIGINT UNSIGNED NOT NULL,
      original_amount INT UNSIGNED NOT NULL,
      discount_amount INT UNSIGNED NOT NULL,
      final_amount INT UNSIGNED NOT NULL,
      status ENUM('reserved', 'redeemed', 'released') NOT NULL DEFAULT 'reserved',
      reserved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      redeemed_at DATETIME NULL,
      released_at DATETIME NULL,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_domain_setup_promotion_redemptions_request (assistance_request_id),
      KEY idx_domain_setup_promotion_redemptions_code_status (promotion_code_id, status),
      KEY idx_domain_setup_promotion_redemptions_user_status (user_id, status),
      CONSTRAINT fk_domain_setup_promotion_redemptions_code
        FOREIGN KEY (promotion_code_id) REFERENCES domain_setup_promotion_codes(id) ON DELETE RESTRICT,
      CONSTRAINT fk_domain_setup_promotion_redemptions_request
        FOREIGN KEY (assistance_request_id) REFERENCES domain_setup_assistance_requests(id) ON DELETE CASCADE,
      CONSTRAINT fk_domain_setup_promotion_redemptions_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS partner_inquiries (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      organization_name VARCHAR(191) NOT NULL,
      contact_name VARCHAR(120) NOT NULL,
      contact_email VARCHAR(320) NOT NULL,
      contact_phone VARCHAR(40) NULL,
      platform_type VARCHAR(64) NOT NULL,
      platform_type_custom VARCHAR(120) NULL,
      estimated_members VARCHAR(32) NULL,
      message TEXT NULL,
      consent_version VARCHAR(32) NOT NULL,
      consented_at DATETIME NOT NULL,
      status ENUM('new','contacted','qualified','closed') NOT NULL DEFAULT 'new',
      requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_partner_inquiries_status_requested (status, requested_at),
      KEY idx_partner_inquiries_email_requested (contact_email, requested_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "partner_inquiries",
    "notification_status",
    "ALTER TABLE partner_inquiries ADD COLUMN notification_status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending' AFTER status"
  );
  await ensureColumn(
    "partner_inquiries",
    "notification_sent_at",
    "ALTER TABLE partner_inquiries ADD COLUMN notification_sent_at DATETIME NULL AFTER notification_status"
  );
  await ensureColumn(
    "partner_inquiries",
    "notification_error",
    "ALTER TABLE partner_inquiries ADD COLUMN notification_error VARCHAR(255) NULL AFTER notification_sent_at"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "original_fee_amount",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN original_fee_amount INT UNSIGNED NOT NULL DEFAULT 10890 AFTER requester_note"
  );
  await getDbPool().query(`
    ALTER TABLE domain_setup_assistance_requests
    MODIFY COLUMN original_fee_amount INT UNSIGNED NOT NULL DEFAULT ${DOMAIN_SETUP_ASSISTANCE_CHARGE},
    MODIFY COLUMN fee_amount INT UNSIGNED NOT NULL DEFAULT ${DOMAIN_SETUP_ASSISTANCE_CHARGE}
  `);
  await ensureColumn(
    "domain_setup_assistance_requests",
    "discount_amount",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN discount_amount INT UNSIGNED NOT NULL DEFAULT 0 AFTER original_fee_amount"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "promotion_code_id",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN promotion_code_id BIGINT UNSIGNED NULL AFTER fee_amount"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "promotion_code",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN promotion_code VARCHAR(64) NULL AFTER promotion_code_id"
  );
  await ensureIndex(
    "domain_setup_assistance_requests",
    "idx_domain_setup_assistance_promotion_code",
    "ALTER TABLE domain_setup_assistance_requests ADD KEY idx_domain_setup_assistance_promotion_code (promotion_code_id, requested_at)"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "notification_email",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN notification_email VARCHAR(320) NULL AFTER account_password_ciphertext"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "billing_profile_id",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN billing_profile_id BIGINT UNSIGNED NULL AFTER fee_amount"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "billing_charge_id",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN billing_charge_id BIGINT UNSIGNED NULL AFTER billing_profile_id"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "payment_status",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN payment_status ENUM('ready', 'processing', 'paid', 'failed', 'cancelled', 'not_applicable') NOT NULL DEFAULT 'ready' AFTER billing_charge_id"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "payment_error_message",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN payment_error_message TEXT NULL AFTER payment_status"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "payment_consent_version",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN payment_consent_version VARCHAR(32) NULL AFTER payment_error_message"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "payment_consented_at",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN payment_consented_at DATETIME NULL AFTER payment_consent_version"
  );
  await ensureColumn(
    "domain_setup_assistance_requests",
    "charged_at",
    "ALTER TABLE domain_setup_assistance_requests ADD COLUMN charged_at DATETIME NULL AFTER payment_consented_at"
  );
  await ensureIndex(
    "domain_setup_assistance_requests",
    "idx_domain_setup_assistance_payment_status",
    "ALTER TABLE domain_setup_assistance_requests ADD KEY idx_domain_setup_assistance_payment_status (payment_status, requested_at)"
  );
  await getDbPool().query(`
    UPDATE domain_setup_assistance_requests
    SET payment_status = CASE
      WHEN status = 'completed' THEN 'not_applicable'
      ELSE 'cancelled'
    END
    WHERE status IN ('completed', 'cancelled')
      AND billing_profile_id IS NULL
      AND payment_status IN ('ready', 'not_applicable')
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS marketing_funnel_events (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      event_type VARCHAR(32) NOT NULL,
      visitor_id VARCHAR(96) NULL,
      user_id BIGINT UNSIGNED NULL,
      domain_id BIGINT UNSIGNED NULL,
      landing_path VARCHAR(191) NULL,
      referrer_host VARCHAR(191) NULL,
      utm_source VARCHAR(120) NULL,
      utm_medium VARCHAR(120) NULL,
      utm_campaign VARCHAR(160) NULL,
      utm_content VARCHAR(160) NULL,
      utm_term VARCHAR(160) NULL,
      client_ip_hash CHAR(64) NULL,
      client_ip VARCHAR(45) NULL,
      client_ip_prefix VARCHAR(64) NULL,
      country_code CHAR(2) NULL,
      user_agent VARCHAR(512) NULL,
      device_type VARCHAR(16) NULL,
      browser_name VARCHAR(32) NULL,
      os_name VARCHAR(32) NULL,
      language VARCHAR(32) NULL,
      timezone VARCHAR(64) NULL,
      screen_width SMALLINT UNSIGNED NULL,
      screen_height SMALLINT UNSIGNED NULL,
      viewport_width SMALLINT UNSIGNED NULL,
      viewport_height SMALLINT UNSIGNED NULL,
      occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_marketing_funnel_events_type_time (event_type, occurred_at),
      KEY idx_marketing_funnel_events_visitor_time (visitor_id, occurred_at),
      KEY idx_marketing_funnel_events_user_time (user_id, occurred_at),
      KEY idx_marketing_funnel_events_domain_time (domain_id, occurred_at),
      KEY idx_marketing_funnel_events_ip_time (client_ip_hash, occurred_at),
      CONSTRAINT fk_marketing_funnel_events_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
      CONSTRAINT fk_marketing_funnel_events_domain
        FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "mailbox_auto_send_settings",
    "send_interval_preset",
    "ALTER TABLE mailbox_auto_send_settings ADD COLUMN send_interval_preset VARCHAR(16) NOT NULL DEFAULT 'daily' AFTER random_enabled"
  );
  await ensureColumn(
    "mailbox_auto_send_settings",
    "recipient_duplicate_scope",
    "ALTER TABLE mailbox_auto_send_settings ADD COLUMN recipient_duplicate_scope VARCHAR(16) NOT NULL DEFAULT 'pending' AFTER random_enabled"
  );
  await ensureColumn(
    "mailbox_auto_send_settings",
    "max_send_count",
    "ALTER TABLE mailbox_auto_send_settings ADD COLUMN max_send_count TINYINT UNSIGNED NOT NULL DEFAULT 30 AFTER send_interval_minutes"
  );
  await ensureColumn(
    "mailbox_auto_send_settings",
    "send_batch_size",
    "ALTER TABLE mailbox_auto_send_settings ADD COLUMN send_batch_size TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER max_send_count"
  );
  await ensureColumn(
    "mailbox_auto_send_settings",
    "log_page_size",
    "ALTER TABLE mailbox_auto_send_settings ADD COLUMN log_page_size SMALLINT UNSIGNED NOT NULL DEFAULT 10 AFTER send_batch_size"
  );
  await getDbPool().query(`
    UPDATE mailbox_auto_send_settings
    SET
      send_interval_preset = CASE
        WHEN send_interval_minutes = 1440 THEN 'daily'
        WHEN send_interval_minutes = 10080 THEN 'weekly'
        WHEN send_interval_minutes = 43200 THEN 'monthly'
        ELSE 'custom'
      END,
      max_send_count = LEAST(30, GREATEST(1, max_send_count)),
      send_batch_size = LEAST(30, GREATEST(1, send_batch_size))
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_auto_send_recipients (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      setting_id BIGINT UNSIGNED NOT NULL,
      email VARCHAR(320) NOT NULL,
      display_name VARCHAR(191) NULL,
      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,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_auto_send_recipients_email (setting_id, email),
      KEY idx_mailbox_auto_send_recipients_setting (setting_id, active),
      CONSTRAINT fk_mailbox_auto_send_recipients_setting
        FOREIGN KEY (setting_id) REFERENCES mailbox_auto_send_settings(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_auto_send_phrases (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      setting_id BIGINT UNSIGNED NOT NULL,
      subject_text VARCHAR(255) NOT NULL DEFAULT '',
      body_text LONGTEXT NOT NULL,
      active TINYINT(1) NOT NULL DEFAULT 1,
      selected 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,
      PRIMARY KEY (id),
      KEY idx_mailbox_auto_send_phrases_setting (setting_id, active, selected),
      CONSTRAINT fk_mailbox_auto_send_phrases_setting
        FOREIGN KEY (setting_id) REFERENCES mailbox_auto_send_settings(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "mailbox_auto_send_phrases",
    "subject_text",
    "ALTER TABLE mailbox_auto_send_phrases ADD COLUMN subject_text VARCHAR(255) NOT NULL DEFAULT '' AFTER setting_id"
  );
  await getDbPool().query(
    `
      UPDATE mailbox_auto_send_phrases p
      INNER JOIN mailbox_auto_send_settings s ON s.id = p.setting_id
      SET p.subject_text = COALESCE(NULLIF(s.subject, ''), ?)
      WHERE p.subject_text = ''
    `,
    ["안녕하세요"]
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_auto_send_deliveries (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      setting_id BIGINT UNSIGNED NOT NULL,
      recipient_email VARCHAR(320) NOT NULL,
      phrase_id BIGINT UNSIGNED NULL,
      subject VARCHAR(255) NOT NULL,
      body_text LONGTEXT NOT NULL,
      status ENUM('sending', 'sent', 'failed') NOT NULL DEFAULT 'sending',
      error_message LONGTEXT NULL,
      sent_message_id BIGINT UNSIGNED NULL,
      scheduled_at DATETIME NOT NULL,
      sent_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_mailbox_auto_send_deliveries_setting (setting_id, created_at),
      KEY idx_mailbox_auto_send_deliveries_status (status, created_at),
      KEY idx_mailbox_auto_send_deliveries_phrase (phrase_id),
      KEY idx_mailbox_auto_send_deliveries_message (sent_message_id),
      CONSTRAINT fk_mailbox_auto_send_deliveries_setting
        FOREIGN KEY (setting_id) REFERENCES mailbox_auto_send_settings(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_auto_send_deliveries_phrase
        FOREIGN KEY (phrase_id) REFERENCES mailbox_auto_send_phrases(id) ON DELETE SET NULL,
      CONSTRAINT fk_mailbox_auto_send_deliveries_message
        FOREIGN KEY (sent_message_id) REFERENCES mailbox_messages(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureIndex(
    "mailbox_auto_send_deliveries",
    "idx_mailbox_auto_send_deliveries_daily_limit",
    "ALTER TABLE mailbox_auto_send_deliveries ADD KEY idx_mailbox_auto_send_deliveries_daily_limit (setting_id, status, sent_at)"
  );
  await ensureIndex(
    "mailbox_auto_send_deliveries",
    "idx_mailbox_auto_send_deliveries_recipient_history",
    "ALTER TABLE mailbox_auto_send_deliveries ADD KEY idx_mailbox_auto_send_deliveries_recipient_history (setting_id, recipient_email, status)"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_ai_assist_threads (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      source_message_id BIGINT UNSIGNED NOT NULL,
      bridge_token VARCHAR(64) NOT NULL,
      notification_email VARCHAR(320) NOT NULL,
      original_sender_email VARCHAR(320) NOT NULL,
      original_sender_name TEXT NULL,
      original_subject TEXT NOT NULL,
      summary_message_id_header VARCHAR(1024) NULL,
      summary_transport_response LONGTEXT NULL,
      summary_status ENUM('sending', 'sent', 'failed') NOT NULL DEFAULT 'sending',
      summary_sent_at DATETIME NULL,
      last_error LONGTEXT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_ai_assist_threads_source_message (source_message_id),
      UNIQUE KEY uq_mailbox_ai_assist_threads_bridge_token (bridge_token),
      KEY idx_mailbox_ai_assist_threads_owner_user_id (owner_user_id),
      KEY idx_mailbox_ai_assist_threads_mailbox_id (mailbox_id),
      CONSTRAINT fk_mailbox_ai_assist_threads_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_ai_assist_threads_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_ai_assist_threads_source_message
        FOREIGN KEY (source_message_id) REFERENCES mailbox_messages(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "mailbox_ai_assist_threads",
    "summary_transport_response",
    "ALTER TABLE mailbox_ai_assist_threads ADD COLUMN summary_transport_response LONGTEXT NULL AFTER summary_message_id_header"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_ai_assist_replies (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      thread_id BIGINT UNSIGNED NOT NULL,
      remote_message_key VARCHAR(600) NOT NULL,
      message_id_header VARCHAR(1024) NULL,
      relay_status ENUM('sending', 'relayed', 'skipped', 'failed') NOT NULL DEFAULT 'sending',
      relay_response LONGTEXT NULL,
      relayed_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_ai_assist_replies_remote_message_key (remote_message_key),
      KEY idx_mailbox_ai_assist_replies_thread_id (thread_id),
      CONSTRAINT fk_mailbox_ai_assist_replies_thread
        FOREIGN KEY (thread_id) REFERENCES mailbox_ai_assist_threads(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_uploaded_assets (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      mailbox_message_id BIGINT UNSIGNED NULL,
      temp_token VARCHAR(64) NOT NULL,
      upload_kind ENUM('attachment', 'inline-image') NOT NULL,
      storage_status ENUM('temporary', 'finalized', 'deleted') NOT NULL DEFAULT 'temporary',
      original_name VARCHAR(255) NOT NULL,
      mime_type VARCHAR(191) NOT NULL,
      size_bytes BIGINT UNSIGNED NOT NULL,
      temp_path VARCHAR(512) NOT NULL,
      storage_bucket VARCHAR(191) NULL,
      storage_key VARCHAR(512) NULL,
      multipart_upload_id VARCHAR(512) NULL,
      public_url VARCHAR(1024) NULL,
      large_attachment TINYINT(1) NOT NULL DEFAULT 0,
      download_count INT UNSIGNED NOT NULL DEFAULT 0,
      expires_at DATETIME NULL,
      finalized_at DATETIME NULL,
      published_at DATETIME NULL,
      deleted_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_uploaded_assets_temp_token (temp_token),
      KEY idx_mailbox_uploaded_assets_owner_user_id (owner_user_id),
      KEY idx_mailbox_uploaded_assets_message_id (mailbox_message_id),
      KEY idx_mailbox_uploaded_assets_status_created_at (storage_status, created_at),
      CONSTRAINT fk_mailbox_uploaded_assets_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_uploaded_assets_message
        FOREIGN KEY (mailbox_message_id) REFERENCES mailbox_messages(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "mailbox_uploaded_assets",
    "large_attachment",
    "ALTER TABLE mailbox_uploaded_assets ADD COLUMN large_attachment TINYINT(1) NOT NULL DEFAULT 0 AFTER public_url",
  );
  await ensureColumn(
    "mailbox_uploaded_assets",
    "download_count",
    "ALTER TABLE mailbox_uploaded_assets ADD COLUMN download_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER large_attachment",
  );
  await ensureColumn(
    "mailbox_uploaded_assets",
    "published_at",
    "ALTER TABLE mailbox_uploaded_assets ADD COLUMN published_at DATETIME NULL AFTER finalized_at",
  );
  await ensureColumn(
    "mailbox_uploaded_assets",
    "multipart_upload_id",
    "ALTER TABLE mailbox_uploaded_assets ADD COLUMN multipart_upload_id VARCHAR(512) NULL AFTER storage_key",
  );
  await getDbPool().query(`
    UPDATE mailbox_uploaded_assets cua
    INNER JOIN mailbox_messages mm ON mm.id = cua.mailbox_message_id
    SET cua.published_at = COALESCE(cua.finalized_at, cua.created_at)
    WHERE cua.large_attachment = 1
      AND cua.storage_status = 'finalized'
      AND cua.published_at IS NULL
      AND mm.transport_status = 'sent'
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_compose_exit_receipts (
      owner_user_id BIGINT UNSIGNED NOT NULL,
      exit_key CHAR(36) NOT NULL,
      mailbox_message_id BIGINT UNSIGNED NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (owner_user_id, exit_key),
      CONSTRAINT fk_compose_exit_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_compose_exit_message FOREIGN KEY (mailbox_message_id) REFERENCES mailbox_messages(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_message_attachments (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      mailbox_message_id BIGINT UNSIGNED NOT NULL,
      attachment_index INT NOT NULL,
      content_disposition ENUM('attachment', 'inline') NOT NULL DEFAULT 'attachment',
      content_id VARCHAR(512) NULL,
      original_name VARCHAR(255) NOT NULL,
      mime_type VARCHAR(191) NOT NULL,
      size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
      storage_status ENUM('metadata', 'cached', 'failed') NOT NULL DEFAULT 'metadata',
      storage_bucket VARCHAR(191) NULL,
      storage_key VARCHAR(512) NULL,
      content_sha256 CHAR(64) NULL,
      last_error TEXT NULL,
      cached_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_message_attachments_message_index (mailbox_message_id, attachment_index),
      KEY idx_mailbox_message_attachments_storage (storage_status, updated_at),
      CONSTRAINT fk_mailbox_message_attachments_message
        FOREIGN KEY (mailbox_message_id) REFERENCES mailbox_messages(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_file_assets (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      file_token VARCHAR(64) NOT NULL,
      original_name VARCHAR(255) NOT NULL,
      mime_type VARCHAR(191) NOT NULL,
      size_bytes BIGINT UNSIGNED NOT NULL,
      storage_status ENUM('uploading', 'active', 'deleted') NOT NULL DEFAULT 'uploading',
      storage_bucket VARCHAR(191) NULL,
      storage_key VARCHAR(512) NOT NULL,
      share_enabled TINYINT(1) NOT NULL DEFAULT 0,
      share_access ENUM('owner', 'public') NOT NULL DEFAULT 'owner',
      share_token CHAR(64) NULL,
      share_updated_at DATETIME NULL,
      activated_at DATETIME NULL,
      deleted_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_file_assets_token (file_token),
      UNIQUE KEY uq_mailbox_file_assets_share_token (share_token),
      KEY idx_mailbox_file_assets_owner_status (owner_user_id, storage_status, created_at),
      KEY idx_mailbox_file_assets_mailbox_status (mailbox_id, storage_status, created_at),
      CONSTRAINT fk_mailbox_file_assets_owner
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_file_assets_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "mailbox_file_assets",
    "share_enabled",
    "ALTER TABLE mailbox_file_assets ADD COLUMN share_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER storage_key"
  );
  await ensureColumn(
    "mailbox_file_assets",
    "share_access",
    "ALTER TABLE mailbox_file_assets ADD COLUMN share_access ENUM('owner', 'public') NOT NULL DEFAULT 'owner' AFTER share_enabled"
  );
  await ensureColumn(
    "mailbox_file_assets",
    "share_token",
    "ALTER TABLE mailbox_file_assets ADD COLUMN share_token CHAR(64) NULL AFTER share_access"
  );
  await ensureColumn(
    "mailbox_file_assets",
    "share_updated_at",
    "ALTER TABLE mailbox_file_assets ADD COLUMN share_updated_at DATETIME NULL AFTER share_token"
  );
  await ensureIndex(
    "mailbox_file_assets",
    "uq_mailbox_file_assets_share_token",
    "ALTER TABLE mailbox_file_assets ADD UNIQUE KEY uq_mailbox_file_assets_share_token (share_token)"
  );
  await ensureIndex(
    "mailbox_file_assets",
    "idx_mailbox_file_assets_status_created",
    "ALTER TABLE mailbox_file_assets ADD KEY idx_mailbox_file_assets_status_created (storage_status, created_at)"
  );

  // This is an immutable storage ledger, not an ownership FK. Account deletion
  // must not erase the actor snapshots or the exact object-storage key.
  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_storage_objects (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id_snapshot BIGINT UNSIGNED NOT NULL,
      owner_email_snapshot VARCHAR(191) NOT NULL,
      mailbox_id_snapshot BIGINT UNSIGNED NULL,
      mailbox_email_snapshot VARCHAR(191) NULL,
      source_kind ENUM('manual_file', 'mail_attachment') NOT NULL,
      source_ref VARCHAR(128) NOT NULL,
      original_name VARCHAR(255) NULL,
      mime_type VARCHAR(191) NULL,
      size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
      storage_bucket VARCHAR(191) NULL,
      storage_key VARCHAR(512) NOT NULL,
      lifecycle_status ENUM('reserved', 'active', 'unlinked', 'deleting', 'deleted') NOT NULL DEFAULT 'reserved',
      unlinked_reason VARCHAR(64) NULL,
      activated_at DATETIME NULL,
      unlinked_at DATETIME NULL,
      delete_after_at DATETIME NULL,
      physical_deleted_at DATETIME 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,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_storage_objects_key (storage_key),
      KEY idx_mailbox_storage_objects_owner (owner_user_id_snapshot, lifecycle_status, created_at),
      KEY idx_mailbox_storage_objects_source (source_kind, source_ref, created_at),
      KEY idx_mailbox_storage_objects_cleanup (lifecycle_status, delete_after_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_file_hidden_attachments (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      mailbox_message_id BIGINT UNSIGNED NOT NULL,
      attachment_index INT NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_file_hidden_attachment (mailbox_id, mailbox_message_id, attachment_index),
      KEY idx_mailbox_file_hidden_owner (owner_user_id, created_at),
      CONSTRAINT fk_mailbox_file_hidden_owner
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_file_hidden_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_file_hidden_message
        FOREIGN KEY (mailbox_message_id) REFERENCES mailbox_messages(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_storage_delete_jobs (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      source_kind ENUM('manual_file', 'mail_attachment') NOT NULL,
      source_ref VARCHAR(128) NOT NULL,
      storage_key VARCHAR(512) NOT NULL,
      status ENUM('pending', 'processing', 'completed') NOT NULL DEFAULT 'pending',
      attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
      process_after_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      processing_started_at DATETIME NULL,
      completed_at DATETIME 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,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_storage_delete_source (source_kind, source_ref, storage_key),
      KEY idx_mailbox_storage_delete_queue (status, process_after_at),
      KEY idx_mailbox_storage_delete_owner (owner_user_id, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await getDbPool().query(`
    INSERT IGNORE INTO mailbox_storage_objects (
      owner_user_id_snapshot,
      owner_email_snapshot,
      mailbox_id_snapshot,
      mailbox_email_snapshot,
      source_kind,
      source_ref,
      original_name,
      mime_type,
      size_bytes,
      storage_bucket,
      storage_key,
      lifecycle_status,
      unlinked_reason,
      activated_at,
      unlinked_at,
      delete_after_at,
      physical_deleted_at
    )
    SELECT
      mfa.owner_user_id,
      LOWER(u.email),
      mfa.mailbox_id,
      LOWER(m.email),
      'manual_file',
      CAST(mfa.id AS CHAR),
      mfa.original_name,
      mfa.mime_type,
      mfa.size_bytes,
      mfa.storage_bucket,
      mfa.storage_key,
      CASE mfa.storage_status
        WHEN 'active' THEN 'active'
        WHEN 'deleted' THEN 'unlinked'
        ELSE 'reserved'
      END,
      IF(mfa.storage_status = 'deleted', 'legacy-logical-delete', NULL),
      mfa.activated_at,
      mfa.deleted_at,
      IF(mfa.storage_status = 'deleted', DATE_ADD(COALESCE(mfa.deleted_at, NOW()), INTERVAL 24 HOUR), NULL),
      NULL
    FROM mailbox_file_assets mfa
    INNER JOIN users u ON u.id = mfa.owner_user_id
    INNER JOIN mailboxes m ON m.id = mfa.mailbox_id
    WHERE mfa.storage_key <> ''
  `);

  await getDbPool().query(`
    INSERT IGNORE INTO mailbox_storage_objects (
      owner_user_id_snapshot,
      owner_email_snapshot,
      mailbox_id_snapshot,
      mailbox_email_snapshot,
      source_kind,
      source_ref,
      original_name,
      mime_type,
      size_bytes,
      storage_bucket,
      storage_key,
      lifecycle_status,
      activated_at
    )
    SELECT
      u.id,
      LOWER(u.email),
      mb.id,
      LOWER(mb.email),
      'mail_attachment',
      CAST(mma.id AS CHAR),
      mma.original_name,
      mma.mime_type,
      mma.size_bytes,
      mma.storage_bucket,
      mma.storage_key,
      'active',
      mma.cached_at
    FROM mailbox_message_attachments mma
    INNER JOIN mailbox_messages mm ON mm.id = mma.mailbox_message_id
    INNER JOIN mailboxes mb ON mb.id = mm.mailbox_id
    INNER JOIN users u ON u.id = mb.user_id
    WHERE mma.storage_key IS NOT NULL
      AND mma.storage_key <> ''
  `);

  await getDbPool().query(`
    INSERT IGNORE INTO mailbox_storage_objects (
      owner_user_id_snapshot,
      owner_email_snapshot,
      source_kind,
      source_ref,
      storage_key,
      lifecycle_status,
      unlinked_reason,
      unlinked_at,
      delete_after_at,
      physical_deleted_at,
      last_error
    )
    SELECT
      msdj.owner_user_id,
      COALESCE(LOWER(u.email), CONCAT('deleted-user-', msdj.owner_user_id)),
      msdj.source_kind,
      msdj.source_ref,
      msdj.storage_key,
      IF(msdj.status = 'completed', 'deleted', 'unlinked'),
      'legacy-delete-job',
      msdj.created_at,
      msdj.process_after_at,
      msdj.completed_at,
      msdj.last_error
    FROM mailbox_storage_delete_jobs msdj
    LEFT JOIN users u ON u.id = msdj.owner_user_id
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_developer_api_keys (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      mailbox_id BIGINT UNSIGNED NOT NULL,
      name VARCHAR(80) NOT NULL,
      key_prefix VARCHAR(24) NOT NULL,
      key_last_four CHAR(4) NOT NULL,
      key_hash CHAR(64) NOT NULL,
      status ENUM('active', 'revoked') NOT NULL DEFAULT 'active',
      last_used_at DATETIME NULL,
      revoked_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_developer_api_keys_hash (key_hash),
      KEY idx_mailbox_developer_api_keys_owner (owner_user_id, status, created_at),
      KEY idx_mailbox_developer_api_keys_mailbox (mailbox_id, status),
      CONSTRAINT fk_mailbox_developer_api_keys_owner
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_developer_api_keys_mailbox
        FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_polar_subscriptions (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      polar_subscription_id CHAR(36) NOT NULL,
      polar_customer_id CHAR(36) NOT NULL,
      external_customer_id VARCHAR(96) NOT NULL,
      product_id CHAR(36) NOT NULL,
      checkout_id CHAR(36) NULL,
      status VARCHAR(32) NOT NULL DEFAULT 'pending',
      currency CHAR(3) NOT NULL DEFAULT 'usd',
      amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
      seats INT UNSIGNED NOT NULL DEFAULT 1,
      recurring_interval VARCHAR(16) NOT NULL DEFAULT 'month',
      current_period_start DATETIME NULL,
      current_period_end DATETIME NULL,
      cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
      canceled_at DATETIME NULL,
      ended_at DATETIME NULL,
      last_event_at DATETIME(3) NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_polar_subscriptions_owner (owner_user_id),
      UNIQUE KEY uq_mailbox_polar_subscriptions_remote (polar_subscription_id),
      KEY idx_mailbox_polar_subscriptions_customer (polar_customer_id),
      KEY idx_mailbox_polar_subscriptions_status (status, current_period_end),
      CONSTRAINT fk_mailbox_polar_subscriptions_owner
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_polar_orders (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      polar_order_id CHAR(36) NOT NULL,
      polar_subscription_id CHAR(36) NULL,
      polar_customer_id CHAR(36) NOT NULL,
      product_id CHAR(36) NULL,
      checkout_id CHAR(36) NULL,
      status VARCHAR(32) NOT NULL,
      paid TINYINT(1) NOT NULL DEFAULT 0,
      currency CHAR(3) NOT NULL,
      subtotal_amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
      tax_amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
      total_amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
      refunded_amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
      billing_reason VARCHAR(32) NULL,
      invoice_number VARCHAR(96) NULL,
      ordered_at DATETIME NOT NULL,
      paid_at DATETIME NULL,
      last_event_at DATETIME(3) NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_polar_orders_remote (polar_order_id),
      KEY idx_mailbox_polar_orders_owner (owner_user_id, ordered_at),
      CONSTRAINT fk_mailbox_polar_orders_owner
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_polar_payments (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      polar_payment_id CHAR(36) NOT NULL,
      polar_checkout_id CHAR(36) NULL,
      polar_order_id CHAR(36) NULL,
      status VARCHAR(32) NOT NULL,
      payment_method VARCHAR(32) NOT NULL,
      payment_trigger VARCHAR(48) NULL,
      currency CHAR(3) NOT NULL,
      amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
      card_brand VARCHAR(32) NULL,
      card_last_four CHAR(4) NULL,
      decline_reason VARCHAR(128) NULL,
      decline_message VARCHAR(500) NULL,
      payment_created_at DATETIME(3) NOT NULL,
      payment_modified_at DATETIME(3) NULL,
      status_changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      first_seen_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      last_synced_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_polar_payments_remote (polar_payment_id),
      KEY idx_mailbox_polar_payments_owner (owner_user_id, payment_created_at),
      KEY idx_mailbox_polar_payments_checkout (polar_checkout_id),
      KEY idx_mailbox_polar_payments_status_changed (status, status_changed_at),
      CONSTRAINT fk_mailbox_polar_payments_owner
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_polar_webhook_events (
      webhook_id VARCHAR(128) NOT NULL,
      event_type VARCHAR(96) NOT NULL,
      status ENUM('processing', 'processed', 'failed') NOT NULL DEFAULT 'processing',
      attempt_count INT UNSIGNED NOT NULL DEFAULT 1,
      last_error VARCHAR(1000) NULL,
      received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      processed_at DATETIME NULL,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (webhook_id),
      KEY idx_mailbox_polar_webhook_events_status (status, received_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_toss_pay_billing_profiles (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      user_token VARCHAR(64) NOT NULL,
      display_id VARCHAR(64) NULL,
      billing_key VARCHAR(96) NULL,
      pending_billing_key VARCHAR(96) NULL,
      replacement_billing_key VARCHAR(96) NULL,
      provider VARCHAR(32) NOT NULL DEFAULT 'legacy_toss_pay',
      provider_customer_key VARCHAR(50) NULL,
      provider_mid VARCHAR(64) NULL,
      status ENUM('inactive', 'pending', 'active', 'removed', 'failed') NOT NULL DEFAULT 'inactive',
      requested_at DATETIME NULL,
      activated_at DATETIME NULL,
      removed_at DATETIME NULL,
      last_action ENUM('ACTIVATED', 'REMOVED') NULL,
      last_processed_at DATETIME NULL,
      pay_method ENUM('TOSS_MONEY', 'CARD') NULL,
      card_method_type VARCHAR(32) NULL,
      card_user_type VARCHAR(32) NULL,
      card_company_no INT NULL,
      card_company_name VARCHAR(128) NULL,
      card_number_masked VARCHAR(64) NULL,
      card_num4_print VARCHAR(8) NULL,
      card_bin_number VARCHAR(16) NULL,
      account_bank_code VARCHAR(16) NULL,
      account_bank_name VARCHAR(128) NULL,
      account_number_masked VARCHAR(64) NULL,
      last_error_code VARCHAR(128) NULL,
      last_error_message LONGTEXT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_toss_pay_billing_profiles_owner_user_id (owner_user_id),
      UNIQUE KEY uq_mailbox_toss_pay_billing_profiles_billing_key (billing_key),
      UNIQUE KEY uq_mailbox_toss_pay_billing_profiles_user_token (user_token),
      CONSTRAINT fk_mailbox_toss_pay_billing_profiles_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "mailbox_toss_pay_billing_profiles",
    "pending_billing_key",
    "ALTER TABLE mailbox_toss_pay_billing_profiles ADD COLUMN pending_billing_key VARCHAR(96) NULL AFTER billing_key"
  );
  await ensureColumn(
    "mailbox_toss_pay_billing_profiles",
    "replacement_billing_key",
    "ALTER TABLE mailbox_toss_pay_billing_profiles ADD COLUMN replacement_billing_key VARCHAR(96) NULL AFTER pending_billing_key"
  );
  await ensureColumn(
    "mailbox_toss_pay_billing_profiles",
    "provider",
    "ALTER TABLE mailbox_toss_pay_billing_profiles ADD COLUMN provider VARCHAR(32) NOT NULL DEFAULT 'legacy_toss_pay' AFTER replacement_billing_key"
  );
  await ensureColumn(
    "mailbox_toss_pay_billing_profiles",
    "provider_customer_key",
    "ALTER TABLE mailbox_toss_pay_billing_profiles ADD COLUMN provider_customer_key VARCHAR(50) NULL AFTER provider"
  );
  await ensureColumn(
    "mailbox_toss_pay_billing_profiles",
    "provider_mid",
    "ALTER TABLE mailbox_toss_pay_billing_profiles ADD COLUMN provider_mid VARCHAR(64) NULL AFTER provider_customer_key"
  );
  await ensureColumnType(
    "mailbox_toss_pay_billing_profiles",
    "billing_key",
    "varchar(200)",
    "ALTER TABLE mailbox_toss_pay_billing_profiles MODIFY COLUMN billing_key VARCHAR(200) NULL"
  );
  await ensureColumnType(
    "mailbox_toss_pay_billing_profiles",
    "pending_billing_key",
    "varchar(200)",
    "ALTER TABLE mailbox_toss_pay_billing_profiles MODIFY COLUMN pending_billing_key VARCHAR(200) NULL"
  );
  await ensureColumnType(
    "mailbox_toss_pay_billing_profiles",
    "replacement_billing_key",
    "varchar(200)",
    "ALTER TABLE mailbox_toss_pay_billing_profiles MODIFY COLUMN replacement_billing_key VARCHAR(200) NULL"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_toss_pay_billing_registrations (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      token_hash CHAR(64) NOT NULL,
      provider_customer_key VARCHAR(50) NOT NULL,
      intent ENUM('billing', 'domain-assistance') NOT NULL DEFAULT 'billing',
      return_app_scheme VARCHAR(255) NULL,
      status ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
      expires_at DATETIME NOT NULL,
      completed_at DATETIME NULL,
      last_error_code VARCHAR(128) NULL,
      last_error_message LONGTEXT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_toss_pay_billing_registrations_token_hash (token_hash),
      KEY idx_mailbox_toss_pay_billing_registrations_owner_status (owner_user_id, status, expires_at),
      CONSTRAINT fk_mailbox_toss_pay_billing_registrations_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_toss_pay_subscriptions (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      billing_profile_id BIGINT UNSIGNED NOT NULL,
      status ENUM('pending', 'active', 'paused', 'cancelled') NOT NULL DEFAULT 'pending',
      billing_cycle ENUM('monthly') NOT NULL DEFAULT 'monthly',
      plan_tier ENUM('growth', 'business') NOT NULL DEFAULT 'growth',
      seat_charge_amount INT UNSIGNED NOT NULL DEFAULT ${MAIL_GROWTH_PLAN_CHARGE_PER_MEMBER},
      next_charge_at DATETIME NULL,
      retry_after_at DATETIME NULL,
      last_charged_at DATETIME NULL,
      last_charge_status ENUM('idle', 'success', 'failed', 'skipped') NOT NULL DEFAULT 'idle',
      consecutive_failures INT UNSIGNED NOT NULL DEFAULT 0,
      billing_grace_started_at DATETIME NULL,
      billing_grace_ends_at DATETIME NULL,
      billing_grace_retry_count INT UNSIGNED NOT NULL DEFAULT 0,
      billing_failure_notice_sent_on DATE NULL,
      billing_member_access_suspended_at DATETIME NULL,
      send_fail_push TINYINT(1) NOT NULL DEFAULT 1,
      cash_receipt TINYINT(1) NOT NULL DEFAULT 0,
      cash_receipt_trade_option VARCHAR(32) NOT NULL DEFAULT 'GENERAL',
      spread_out SMALLINT UNSIGNED NOT NULL DEFAULT 0,
      metadata_text LONGTEXT NULL,
      processing_started_at DATETIME NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_toss_pay_subscriptions_owner_user_id (owner_user_id),
      KEY idx_mailbox_toss_pay_subscriptions_status_next_charge_at (status, next_charge_at),
      KEY idx_mailbox_toss_pay_subscriptions_retry_after_at (retry_after_at),
      CONSTRAINT fk_mailbox_toss_pay_subscriptions_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_toss_pay_subscriptions_billing_profile
        FOREIGN KEY (billing_profile_id) REFERENCES mailbox_toss_pay_billing_profiles(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "seat_charge_amount",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN seat_charge_amount INT UNSIGNED NULL AFTER billing_cycle"
  );
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "plan_tier",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN plan_tier ENUM('growth', 'business') NOT NULL DEFAULT 'growth' AFTER billing_cycle"
  );
  await getDbPool().query(`
    UPDATE mailbox_toss_pay_subscriptions
    SET seat_charge_amount = ${MAIL_GROWTH_PLAN_CHARGE_PER_MEMBER}
    WHERE seat_charge_amount IS NULL
       OR seat_charge_amount = 3900
  `);
  await getDbPool().query(`
    ALTER TABLE mailbox_toss_pay_subscriptions
    MODIFY COLUMN seat_charge_amount INT UNSIGNED NOT NULL DEFAULT ${MAIL_GROWTH_PLAN_CHARGE_PER_MEMBER}
  `);
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "member_access_ends_at",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN member_access_ends_at DATETIME NULL AFTER processing_started_at"
  );
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "billing_grace_started_at",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN billing_grace_started_at DATETIME NULL AFTER consecutive_failures"
  );
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "billing_grace_ends_at",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN billing_grace_ends_at DATETIME NULL AFTER billing_grace_started_at"
  );
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "billing_grace_retry_count",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN billing_grace_retry_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER billing_grace_ends_at"
  );
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "billing_failure_notice_sent_on",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN billing_failure_notice_sent_on DATE NULL AFTER billing_grace_retry_count"
  );
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "billing_member_access_suspended_at",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN billing_member_access_suspended_at DATETIME NULL AFTER billing_failure_notice_sent_on"
  );
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "member_access_processing_started_at",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN member_access_processing_started_at DATETIME NULL AFTER member_access_ends_at"
  );
  await ensureColumn(
    "mailbox_toss_pay_subscriptions",
    "member_access_disabled_at",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD COLUMN member_access_disabled_at DATETIME NULL AFTER member_access_processing_started_at"
  );
  await ensureIndex(
    "mailbox_toss_pay_subscriptions",
    "idx_mailbox_toss_pay_subscriptions_member_access",
    "ALTER TABLE mailbox_toss_pay_subscriptions ADD KEY idx_mailbox_toss_pay_subscriptions_member_access (status, member_access_ends_at, member_access_disabled_at)"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_toss_pay_billing_charges (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      subscription_id BIGINT UNSIGNED NULL,
      billing_profile_id BIGINT UNSIGNED NOT NULL,
      charge_kind ENUM('manual', 'cycle') NOT NULL,
      status ENUM('requested', 'success', 'failed', 'skipped') NOT NULL DEFAULT 'requested',
      order_no VARCHAR(64) NOT NULL,
      amount BIGINT UNSIGNED NOT NULL,
      amount_tax_free BIGINT UNSIGNED NOT NULL DEFAULT 0,
      product_desc VARCHAR(255) NOT NULL,
      requested_at DATETIME NOT NULL,
      approved_at DATETIME NULL,
      pay_method VARCHAR(32) NULL,
      card_company_name VARCHAR(128) NULL,
      card_num4_print VARCHAR(8) NULL,
      card_method_type VARCHAR(32) NULL,
      account_bank_name VARCHAR(128) NULL,
      account_number_masked VARCHAR(64) NULL,
      provider VARCHAR(32) NOT NULL DEFAULT 'legacy_toss_pay',
      pay_token VARCHAR(200) NULL,
      transaction_id VARCHAR(200) NULL,
      response_code INT NULL,
      error_code VARCHAR(128) NULL,
      error_message LONGTEXT NULL,
      raw_response LONGTEXT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_toss_pay_billing_charges_order_no (order_no),
      KEY idx_mailbox_toss_pay_billing_charges_owner_requested_at (owner_user_id, requested_at),
      KEY idx_mailbox_toss_pay_billing_charges_subscription_id (subscription_id),
      CONSTRAINT fk_mailbox_toss_pay_billing_charges_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_toss_pay_billing_charges_subscription
        FOREIGN KEY (subscription_id) REFERENCES mailbox_toss_pay_subscriptions(id) ON DELETE SET NULL,
      CONSTRAINT fk_mailbox_toss_pay_billing_charges_billing_profile
        FOREIGN KEY (billing_profile_id) REFERENCES mailbox_toss_pay_billing_profiles(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumn(
    "mailbox_toss_pay_billing_charges",
    "provider",
    "ALTER TABLE mailbox_toss_pay_billing_charges ADD COLUMN provider VARCHAR(32) NOT NULL DEFAULT 'legacy_toss_pay' AFTER account_number_masked"
  );
  await ensureColumnType(
    "mailbox_toss_pay_billing_charges",
    "pay_token",
    "varchar(200)",
    "ALTER TABLE mailbox_toss_pay_billing_charges MODIFY COLUMN pay_token VARCHAR(200) NULL"
  );
  await ensureColumnType(
    "mailbox_toss_pay_billing_charges",
    "transaction_id",
    "varchar(200)",
    "ALTER TABLE mailbox_toss_pay_billing_charges MODIFY COLUMN transaction_id VARCHAR(200) NULL"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_toss_pay_billing_refunds (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      charge_id BIGINT UNSIGNED NOT NULL,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      refund_no VARCHAR(64) NOT NULL,
      status ENUM('pending', 'success', 'failed') NOT NULL DEFAULT 'pending',
      amount BIGINT UNSIGNED NOT NULL,
      amount_tax_free BIGINT UNSIGNED NOT NULL DEFAULT 0,
      reason VARCHAR(255) NULL,
      pay_token VARCHAR(200) NOT NULL,
      transaction_id VARCHAR(200) NULL,
      requested_by_email VARCHAR(320) NOT NULL,
      requested_at DATETIME NOT NULL,
      refunded_at DATETIME NULL,
      response_code INT NULL,
      error_code VARCHAR(128) NULL,
      error_message LONGTEXT NULL,
      raw_response LONGTEXT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mailbox_toss_pay_billing_refunds_refund_no (refund_no),
      KEY idx_mailbox_toss_pay_billing_refunds_charge_id (charge_id),
      KEY idx_mailbox_toss_pay_billing_refunds_owner_requested_at (owner_user_id, requested_at),
      CONSTRAINT fk_mailbox_toss_pay_billing_refunds_charge
        FOREIGN KEY (charge_id) REFERENCES mailbox_toss_pay_billing_charges(id) ON DELETE CASCADE,
      CONSTRAINT fk_mailbox_toss_pay_billing_refunds_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await ensureColumnType(
    "mailbox_toss_pay_billing_refunds",
    "pay_token",
    "varchar(200)",
    "ALTER TABLE mailbox_toss_pay_billing_refunds MODIFY COLUMN pay_token VARCHAR(200) NOT NULL"
  );
  await ensureColumnType(
    "mailbox_toss_pay_billing_refunds",
    "transaction_id",
    "varchar(200)",
    "ALTER TABLE mailbox_toss_pay_billing_refunds MODIFY COLUMN transaction_id VARCHAR(200) NULL"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_toss_pay_billing_cancellation_requests (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      subscription_id BIGINT UNSIGNED NULL,
      billing_profile_id BIGINT UNSIGNED NULL,
      latest_charge_id BIGINT UNSIGNED NULL,
      request_kind ENUM('cancel_only', 'withdrawal_refund') NOT NULL,
      status ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
      admin_review_status ENUM(
        'not_required',
        'pending_review',
        'approved_full',
        'approved_partial',
        'rejected',
        'cancelled_by_user'
      ) NOT NULL DEFAULT 'not_required',
      requested_by_email VARCHAR(320) NOT NULL,
      reviewed_by_email VARCHAR(320) NULL,
      review_note VARCHAR(255) NULL,
      usage_message_count INT UNSIGNED NOT NULL DEFAULT 0,
      usage_delivery_count INT UNSIGNED NOT NULL DEFAULT 0,
      latest_charge_amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
      approved_refund_amount BIGINT UNSIGNED NULL,
      latest_charge_requested_at DATETIME NULL,
      eligible_until DATETIME NULL,
      requested_at DATETIME NOT NULL,
      processing_started_at DATETIME NULL,
      reviewed_at DATETIME NULL,
      processed_at DATETIME NULL,
      result_text LONGTEXT NULL,
      error_message LONGTEXT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_mtpcbcr_owner_status (
        owner_user_id,
        status,
        requested_at
      ),
      KEY idx_mtpcbcr_status_requested (
        status,
        requested_at
      ),
      CONSTRAINT fk_mtpcbcr_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mtpcbcr_subscription
        FOREIGN KEY (subscription_id) REFERENCES mailbox_toss_pay_subscriptions(id) ON DELETE SET NULL,
      CONSTRAINT fk_mtpcbcr_billing_profile
        FOREIGN KEY (billing_profile_id) REFERENCES mailbox_toss_pay_billing_profiles(id) ON DELETE SET NULL,
      CONSTRAINT fk_mtpcbcr_charge
        FOREIGN KEY (latest_charge_id) REFERENCES mailbox_toss_pay_billing_charges(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureColumn(
    "mailbox_toss_pay_billing_cancellation_requests",
    "admin_review_status",
    `ALTER TABLE mailbox_toss_pay_billing_cancellation_requests
      ADD COLUMN admin_review_status ENUM(
        'not_required',
        'pending_review',
        'approved_full',
        'approved_partial',
        'rejected',
        'cancelled_by_user'
      ) NOT NULL DEFAULT 'not_required' AFTER status`
  );
  await ensureColumn(
    "mailbox_toss_pay_billing_cancellation_requests",
    "reviewed_by_email",
    "ALTER TABLE mailbox_toss_pay_billing_cancellation_requests ADD COLUMN reviewed_by_email VARCHAR(320) NULL AFTER requested_by_email"
  );
  await ensureColumn(
    "mailbox_toss_pay_billing_cancellation_requests",
    "review_note",
    "ALTER TABLE mailbox_toss_pay_billing_cancellation_requests ADD COLUMN review_note VARCHAR(255) NULL AFTER reviewed_by_email"
  );
  await ensureColumn(
    "mailbox_toss_pay_billing_cancellation_requests",
    "approved_refund_amount",
    "ALTER TABLE mailbox_toss_pay_billing_cancellation_requests ADD COLUMN approved_refund_amount BIGINT UNSIGNED NULL AFTER latest_charge_amount"
  );
  await ensureColumn(
    "mailbox_toss_pay_billing_cancellation_requests",
    "reviewed_at",
    "ALTER TABLE mailbox_toss_pay_billing_cancellation_requests ADD COLUMN reviewed_at DATETIME NULL AFTER processing_started_at"
  );
  await ensureIndex(
    "mailbox_toss_pay_billing_cancellation_requests",
    "idx_mtpcbcr_review_requested",
    "ALTER TABLE mailbox_toss_pay_billing_cancellation_requests ADD KEY idx_mtpcbcr_review_requested (admin_review_status, requested_at)"
  );

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS mailbox_withdrawal_cleanup_jobs (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      owner_user_id BIGINT UNSIGNED NOT NULL,
      cancellation_request_id BIGINT UNSIGNED NOT NULL,
      status ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
      payload_json LONGTEXT NOT NULL,
      sync_cutoff_at DATETIME NOT NULL,
      attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
      process_after_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      processing_started_at DATETIME NULL,
      completed_at DATETIME NULL,
      last_error LONGTEXT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uq_mwcj_request (cancellation_request_id),
      KEY idx_mwcj_status_process_after (status, process_after_at),
      KEY idx_mwcj_owner_user_id (owner_user_id),
      CONSTRAINT fk_mwcj_owner_user
        FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
      CONSTRAINT fk_mwcj_request
        FOREIGN KEY (cancellation_request_id) REFERENCES mailbox_toss_pay_billing_cancellation_requests(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await getDbPool().query(`
    UPDATE mailbox_toss_pay_billing_cancellation_requests
    SET admin_review_status = CASE
      WHEN request_kind = 'cancel_only' THEN 'not_required'
      WHEN result_text LIKE 'revoked-by-user:%' THEN 'cancelled_by_user'
      WHEN result_text LIKE 'rejected-by-admin:%' THEN 'rejected'
      WHEN result_text LIKE 'approved-partial:%' THEN 'approved_partial'
      WHEN status IN ('pending', 'processing') THEN 'pending_review'
      ELSE 'approved_full'
    END
    WHERE request_kind = 'withdrawal_refund'
      AND admin_review_status = 'not_required'
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS domain_verification_runs (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      status VARCHAR(24) NOT NULL DEFAULT 'running',
      started_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      completed_at DATETIME(3) NULL,
      total_count INT UNSIGNED NOT NULL DEFAULT 0,
      verified_count INT UNSIGNED NOT NULL DEFAULT 0,
      disconnected_count INT UNSIGNED NOT NULL DEFAULT 0,
      never_connected_count INT UNSIGNED NOT NULL DEFAULT 0,
      newly_disconnected_count INT UNSIGNED NOT NULL DEFAULT 0,
      reconnected_count INT UNSIGNED NOT NULL DEFAULT 0,
      error_count INT UNSIGNED NOT NULL DEFAULT 0,
      error_text TEXT NULL,
      created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      PRIMARY KEY (id),
      KEY idx_domain_verification_runs_started (started_at),
      KEY idx_domain_verification_runs_status_started (status, started_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await ensureTable(`
    CREATE TABLE IF NOT EXISTS domain_verification_results (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      run_id BIGINT UNSIGNED NOT NULL,
      domain_id BIGINT UNSIGNED NULL,
      domain_name VARCHAR(253) NOT NULL,
      owner_email VARCHAR(320) NOT NULL,
      company_name VARCHAR(191) NULL,
      previous_status VARCHAR(24) NOT NULL,
      current_status VARCHAR(24) NOT NULL,
      transition_kind VARCHAR(32) NOT NULL,
      had_connection_evidence TINYINT(1) NOT NULL DEFAULT 0,
      evidence_verified_at TINYINT(1) NOT NULL DEFAULT 0,
      evidence_verified_event TINYINT(1) NOT NULL DEFAULT 0,
      evidence_active_mailbox TINYINT(1) NOT NULL DEFAULT 0,
      evidence_external_inbound TINYINT(1) NOT NULL DEFAULT 0,
      dns_verified TINYINT(1) NOT NULL DEFAULT 0,
      mailcow_provisioned TINYINT(1) NOT NULL DEFAULT 0,
      error_text TEXT NULL,
      checked_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      PRIMARY KEY (id),
      UNIQUE KEY uq_domain_verification_results_run_domain (run_id, domain_name),
      KEY idx_domain_verification_results_domain_checked (domain_id, checked_at),
      KEY idx_domain_verification_results_transition_checked (transition_kind, checked_at),
      CONSTRAINT fk_domain_verification_results_run
        FOREIGN KEY (run_id) REFERENCES domain_verification_runs(id) ON DELETE CASCADE,
      CONSTRAINT fk_domain_verification_results_domain
        FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);

  await getDbPool().query(`
    DELETE mm
    FROM mailbox_messages mm
    INNER JOIN mailbox_delivery_logs mdl ON mdl.mailbox_message_id = mm.id
    WHERE mdl.transport = 'seed'
  `);
  await getDbPool().query(`
    DELETE FROM mailbox_delivery_logs
    WHERE transport = 'seed'
  `);
}

async function runOfficialMailSchemaMigrationsOnce() {
  const connection = await getDbPool().getConnection();
  let lockAcquired = false;

  try {
    const [lockRows] = await connection.query<mysql.RowDataPacket[]>("SELECT GET_LOCK(?, 120) AS acquired", [
      OFFICIAL_MAIL_SCHEMA_LOCK_NAME
    ]);
    lockAcquired = Number(lockRows[0]?.acquired ?? 0) === 1;

    if (!lockAcquired) {
      throw new Error("official-mail-schema-lock-timeout");
    }

    await connection.query(`
      CREATE TABLE IF NOT EXISTS official_mail_schema_state (
        schema_name VARCHAR(64) NOT NULL,
        version BIGINT UNSIGNED NOT NULL,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (schema_name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    const [versionRows] = await connection.query<mysql.RowDataPacket[]>(
      `
        SELECT version
        FROM official_mail_schema_state
        WHERE schema_name = ?
        LIMIT 1
      `,
      [OFFICIAL_MAIL_SCHEMA_NAME]
    );
    const appliedVersion = Number(versionRows[0]?.version ?? 0);

    if (appliedVersion >= OFFICIAL_MAIL_SCHEMA_VERSION) {
      return;
    }

    await runOfficialMailSchemaMigrations();
    await connection.query(
      `
        INSERT INTO official_mail_schema_state (schema_name, version, updated_at)
        VALUES (?, ?, NOW())
        ON DUPLICATE KEY UPDATE
          version = VALUES(version),
          updated_at = NOW()
      `,
      [OFFICIAL_MAIL_SCHEMA_NAME, OFFICIAL_MAIL_SCHEMA_VERSION]
    );
  } finally {
    if (lockAcquired) {
      await connection.query("SELECT RELEASE_LOCK(?)", [OFFICIAL_MAIL_SCHEMA_LOCK_NAME]).catch(() => undefined);
    }
    connection.release();
  }
}

export async function ensureOfficialMailSchema() {
  if (global.__officialMailSchemaVersion !== OFFICIAL_MAIL_SCHEMA_VERSION) {
    global.__officialMailSchemaPromise = undefined;
    global.__officialMailSchemaVersion = OFFICIAL_MAIL_SCHEMA_VERSION;
  }

  if (!global.__officialMailSchemaPromise) {
    global.__officialMailSchemaPromise = runOfficialMailSchemaMigrationsOnce().catch((error) => {
      global.__officialMailSchemaPromise = undefined;
      global.__officialMailSchemaVersion = undefined;
      throw error;
    });
  }

  await global.__officialMailSchemaPromise;
}

// Keep Search Console storage isolated from the legacy schema migration chain.
// Adding analytics must never replay mailbox data normalization updates.
export async function ensureGoogleSearchConsoleSchema() {
  if (!global.__googleSearchConsoleSchemaPromise) {
    global.__googleSearchConsoleSchemaPromise = (async () => {
      await ensureTable(`
        CREATE TABLE IF NOT EXISTS google_search_console_connections (
          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          account_email VARCHAR(320) NOT NULL,
          site_url VARCHAR(255) NOT NULL,
          access_token_ciphertext LONGTEXT NOT NULL,
          refresh_token_ciphertext LONGTEXT NOT NULL,
          token_expires_at DATETIME NULL,
          scopes VARCHAR(512) NOT NULL,
          connected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          last_synced_at DATETIME NULL,
          last_sync_error TEXT NULL,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (id),
          UNIQUE KEY uq_google_search_console_connections_account (account_email),
          KEY idx_google_search_console_connections_site (site_url)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);

      await ensureTable(`
        CREATE TABLE IF NOT EXISTS google_search_console_daily_metrics (
          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          connection_id BIGINT UNSIGNED NOT NULL,
          metric_date DATE NOT NULL,
          dimension_type VARCHAR(24) NOT NULL,
          row_key CHAR(64) NOT NULL,
          page_url VARCHAR(768) NULL,
          query_text VARCHAR(768) NULL,
          clicks INT UNSIGNED NOT NULL DEFAULT 0,
          impressions INT UNSIGNED NOT NULL DEFAULT 0,
          ctr DECIMAL(12,8) NOT NULL DEFAULT 0,
          position DECIMAL(12,4) NOT NULL DEFAULT 0,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (id),
          UNIQUE KEY uq_google_search_console_daily_metric (connection_id, metric_date, dimension_type, row_key),
          KEY idx_google_search_console_daily_metrics_range (connection_id, metric_date, dimension_type),
          CONSTRAINT fk_google_search_console_daily_metrics_connection
            FOREIGN KEY (connection_id) REFERENCES google_search_console_connections(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);

      await ensureTable(`
        CREATE TABLE IF NOT EXISTS google_search_console_sync_runs (
          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          connection_id BIGINT UNSIGNED NOT NULL,
          sync_date DATE NOT NULL,
          status ENUM('success', 'failed') NOT NULL,
          property_row_count INT UNSIGNED NOT NULL DEFAULT 0,
          page_row_count INT UNSIGNED NOT NULL DEFAULT 0,
          query_row_count INT UNSIGNED NOT NULL DEFAULT 0,
          error_message TEXT NULL,
          started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          finished_at DATETIME NULL,
          PRIMARY KEY (id),
          KEY idx_google_search_console_sync_runs_connection_date (connection_id, sync_date),
          CONSTRAINT fk_google_search_console_sync_runs_connection
            FOREIGN KEY (connection_id) REFERENCES google_search_console_connections(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);

      await ensureTable(`
        CREATE TABLE IF NOT EXISTS google_search_console_url_inspections (
          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          connection_id BIGINT UNSIGNED NOT NULL,
          inspection_url VARCHAR(768) NOT NULL,
          url_hash BINARY(32) NOT NULL,
          verdict VARCHAR(32) NOT NULL DEFAULT 'VERDICT_UNSPECIFIED',
          coverage_state VARCHAR(255) NULL,
          robots_txt_state VARCHAR(48) NULL,
          indexing_state VARCHAR(64) NULL,
          page_fetch_state VARCHAR(64) NULL,
          last_crawl_time DATETIME NULL,
          google_canonical VARCHAR(768) NULL,
          user_canonical VARCHAR(768) NULL,
          crawled_as VARCHAR(48) NULL,
          sitemaps_json JSON NULL,
          referring_urls_json JSON NULL,
          inspection_result_link VARCHAR(1024) NULL,
          inspection_error TEXT NULL,
          last_inspected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (id),
          UNIQUE KEY uq_google_search_console_url_inspection (connection_id, url_hash),
          KEY idx_google_search_console_url_inspections_checked (connection_id, last_inspected_at),
          CONSTRAINT fk_google_search_console_url_inspections_connection
            FOREIGN KEY (connection_id) REFERENCES google_search_console_connections(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);
    })().catch((error) => {
      global.__googleSearchConsoleSchemaPromise = undefined;
      throw error;
    });
  }

  await global.__googleSearchConsoleSchemaPromise;
}

// Marketing collection is write-heavy by nature, so its additive schema stays
// outside the legacy migration chain that also normalizes mailbox rows.
export async function ensureMarketingAnalyticsSchema() {
  if (!global.__marketingAnalyticsSchemaPromise) {
    global.__marketingAnalyticsSchemaPromise = (async () => {
      await ensureTable(`
        CREATE TABLE IF NOT EXISTS marketing_funnel_events (
          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          event_type VARCHAR(32) NOT NULL,
          visitor_id VARCHAR(96) NULL,
          ga_client_id VARCHAR(96) NULL,
          ga_session_id VARCHAR(32) NULL,
          user_id BIGINT UNSIGNED NULL,
          domain_id BIGINT UNSIGNED NULL,
          event_reference VARCHAR(191) NULL,
          event_detail VARCHAR(96) NULL,
          event_value DECIMAL(18,4) NULL,
          event_currency CHAR(3) NULL,
          landing_path VARCHAR(191) NULL,
          referrer_host VARCHAR(191) NULL,
          referrer_path VARCHAR(512) NULL,
          utm_source VARCHAR(120) NULL,
          utm_medium VARCHAR(120) NULL,
          utm_campaign VARCHAR(160) NULL,
          utm_content VARCHAR(160) NULL,
          utm_term VARCHAR(160) NULL,
          client_ip_hash CHAR(64) NULL,
          client_ip VARCHAR(45) NULL,
          client_ip_prefix VARCHAR(64) NULL,
          country_code CHAR(2) NULL,
          user_agent VARCHAR(512) NULL,
          device_type VARCHAR(16) NULL,
          browser_name VARCHAR(32) NULL,
          os_name VARCHAR(32) NULL,
          language VARCHAR(32) NULL,
          timezone VARCHAR(64) NULL,
          screen_width SMALLINT UNSIGNED NULL,
          screen_height SMALLINT UNSIGNED NULL,
          viewport_width SMALLINT UNSIGNED NULL,
          viewport_height SMALLINT UNSIGNED NULL,
          occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (id),
          KEY idx_marketing_funnel_events_type_time (event_type, occurred_at),
          KEY idx_marketing_funnel_events_visitor_time (visitor_id, occurred_at),
          KEY idx_marketing_funnel_events_user_time (user_id, occurred_at),
          KEY idx_marketing_funnel_events_domain_time (domain_id, occurred_at),
          KEY idx_marketing_funnel_events_ip_time (client_ip_hash, occurred_at),
          KEY idx_marketing_funnel_events_occurred_at (occurred_at),
          UNIQUE KEY uq_marketing_funnel_events_reference (event_type, event_reference),
          CONSTRAINT fk_marketing_funnel_events_user
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
          CONSTRAINT fk_marketing_funnel_events_domain
            FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);

      const columns = [
        [
          "ga_client_id",
          "ALTER TABLE marketing_funnel_events ADD COLUMN ga_client_id VARCHAR(96) NULL AFTER visitor_id"
        ],
        [
          "ga_session_id",
          "ALTER TABLE marketing_funnel_events ADD COLUMN ga_session_id VARCHAR(32) NULL AFTER ga_client_id"
        ],
        [
          "event_reference",
          "ALTER TABLE marketing_funnel_events ADD COLUMN event_reference VARCHAR(191) NULL AFTER domain_id"
        ],
        [
          "event_detail",
          "ALTER TABLE marketing_funnel_events ADD COLUMN event_detail VARCHAR(96) NULL AFTER event_reference"
        ],
        [
          "event_value",
          "ALTER TABLE marketing_funnel_events ADD COLUMN event_value DECIMAL(18,4) NULL AFTER event_detail"
        ],
        [
          "event_currency",
          "ALTER TABLE marketing_funnel_events ADD COLUMN event_currency CHAR(3) NULL AFTER event_value"
        ],
        [
          "referrer_path",
          "ALTER TABLE marketing_funnel_events ADD COLUMN referrer_path VARCHAR(512) NULL AFTER referrer_host"
        ],
        [
          "client_ip_hash",
          "ALTER TABLE marketing_funnel_events ADD COLUMN client_ip_hash CHAR(64) NULL AFTER utm_term"
        ],
        ["client_ip", "ALTER TABLE marketing_funnel_events ADD COLUMN client_ip VARCHAR(45) NULL AFTER client_ip_hash"],
        [
          "client_ip_prefix",
          "ALTER TABLE marketing_funnel_events ADD COLUMN client_ip_prefix VARCHAR(64) NULL AFTER client_ip"
        ],
        [
          "country_code",
          "ALTER TABLE marketing_funnel_events ADD COLUMN country_code CHAR(2) NULL AFTER client_ip_prefix"
        ],
        [
          "user_agent",
          "ALTER TABLE marketing_funnel_events ADD COLUMN user_agent VARCHAR(512) NULL AFTER country_code"
        ],
        ["device_type", "ALTER TABLE marketing_funnel_events ADD COLUMN device_type VARCHAR(16) NULL AFTER user_agent"],
        [
          "browser_name",
          "ALTER TABLE marketing_funnel_events ADD COLUMN browser_name VARCHAR(32) NULL AFTER device_type"
        ],
        ["os_name", "ALTER TABLE marketing_funnel_events ADD COLUMN os_name VARCHAR(32) NULL AFTER browser_name"],
        ["language", "ALTER TABLE marketing_funnel_events ADD COLUMN language VARCHAR(32) NULL AFTER os_name"],
        ["timezone", "ALTER TABLE marketing_funnel_events ADD COLUMN timezone VARCHAR(64) NULL AFTER language"],
        [
          "screen_width",
          "ALTER TABLE marketing_funnel_events ADD COLUMN screen_width SMALLINT UNSIGNED NULL AFTER timezone"
        ],
        [
          "screen_height",
          "ALTER TABLE marketing_funnel_events ADD COLUMN screen_height SMALLINT UNSIGNED NULL AFTER screen_width"
        ],
        [
          "viewport_width",
          "ALTER TABLE marketing_funnel_events ADD COLUMN viewport_width SMALLINT UNSIGNED NULL AFTER screen_height"
        ],
        [
          "viewport_height",
          "ALTER TABLE marketing_funnel_events ADD COLUMN viewport_height SMALLINT UNSIGNED NULL AFTER viewport_width"
        ]
      ] as const;

      for (const [columnName, ddl] of columns) {
        await ensureColumn("marketing_funnel_events", columnName, ddl);
      }
      await ensureIndex(
        "marketing_funnel_events",
        "uq_marketing_funnel_events_reference",
        "ALTER TABLE marketing_funnel_events ADD UNIQUE KEY uq_marketing_funnel_events_reference (event_type, event_reference)"
      );
      await ensureIndex(
        "marketing_funnel_events",
        "idx_marketing_funnel_events_ip_time",
        "ALTER TABLE marketing_funnel_events ADD KEY idx_marketing_funnel_events_ip_time (client_ip_hash, occurred_at)"
      );
      await ensureIndex(
        "marketing_funnel_events",
        "idx_marketing_funnel_events_occurred_at",
        "ALTER TABLE marketing_funnel_events ADD KEY idx_marketing_funnel_events_occurred_at (occurred_at)"
      );
    })().catch((error) => {
      global.__marketingAnalyticsSchemaPromise = undefined;
      throw error;
    });
  }

  await global.__marketingAnalyticsSchemaPromise;
}