import mysql from "mysql2/promise"; declare global { var __officialMailPool: mysql.Pool | undefined; var __officialMailSchemaPromise: Promise | undefined; var __officialMailSchemaVersion: number | undefined; var __googleSearchConsoleSchemaPromise: Promise | undefined; var __marketingAnalyticsSchemaPromise: Promise | undefined; } const OFFICIAL_MAIL_SCHEMA_VERSION = 20260722; 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( ` 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( ` 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; } await getDbPool().query(ddl); } async function ensureIndex(tableName: string, indexName: string, ddl: string) { if (await hasIndex(tableName, indexName)) { return; } await getDbPool().query(ddl); } async function ensureEnumValue( tableName: string, columnName: string, value: string, ddl: string, ) { const [rows] = await getDbPool().query( ` 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 ensureColumn( "users", "mail_sidebar_width", "ALTER TABLE users ADD COLUMN mail_sidebar_width SMALLINT UNSIGNED NOT NULL DEFAULT 270 AFTER mail_configured", ); await ensureColumn( "domains", "mailcow_cleanup_at", "ALTER TABLE domains ADD COLUMN mailcow_cleanup_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( "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", "recovery_email", "ALTER TABLE users ADD COLUMN recovery_email VARCHAR(191) 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 ensureTable(` CREATE TABLE IF NOT EXISTS signup_recovery_email_verifications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(191) 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 password_reset_tokens ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, recovery_email VARCHAR(191) 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 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", "last_sync_at", "ALTER TABLE mailboxes ADD COLUMN last_sync_at DATETIME NULL AFTER password_updated_at", ); await ensureColumn( "mailboxes", "last_sync_error", "ALTER TABLE mailboxes ADD COLUMN last_sync_error LONGTEXT NULL AFTER last_sync_at", ); 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 ensureColumn( "mailbox_folders", "remote_name", "ALTER TABLE mailbox_folders ADD COLUMN remote_name VARCHAR(191) 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(191) NULL AFTER remote_uid", ); await ensureColumn( "mailbox_messages", "remote_flags", "ALTER TABLE mailbox_messages ADD COLUMN remote_flags VARCHAR(255) 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 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 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 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(191) NOT NULL, display_name VARCHAR(191) NOT NULL, password_ciphertext LONGTEXT NOT NULL, status ENUM('active', 'disabled') NOT NULL DEFAULT 'active', 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 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(191) NULL, assistant_mailbox_email VARCHAR(191) 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, 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 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, 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), 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 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 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(191) NOT NULL, original_sender_email VARCHAR(191) NOT NULL, original_sender_name VARCHAR(191) NULL, original_subject VARCHAR(255) NOT NULL, summary_message_id_header VARCHAR(255) 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(255) NOT NULL, message_id_header VARCHAR(255) 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, public_url VARCHAR(1024) NULL, expires_at DATETIME NULL, finalized_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 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, 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 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', 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, 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", "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", "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, pay_token VARCHAR(64) NULL, transaction_id VARCHAR(64) 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 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(64) NOT NULL, transaction_id VARCHAR(64) NULL, requested_by_email VARCHAR(191) 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 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(191) NOT NULL, reviewed_by_email VARCHAR(191) 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(191) 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 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' `); } 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 = runOfficialMailSchemaMigrations().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(191) 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 `); })().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, user_id BIGINT UNSIGNED NULL, domain_id BIGINT UNSIGNED 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_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), 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 = [ ["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_prefix", "ALTER TABLE marketing_funnel_events ADD COLUMN client_ip_prefix VARCHAR(64) NULL AFTER client_ip_hash"], ["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", "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; }