<?php

function migration_table_exists(PDO $pdo, string $table): bool
{
    $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?');
    $stmt->execute([$table]);
    return (bool)$stmt->fetchColumn();
}

function migration_column_exists(PDO $pdo, string $table, string $column): bool
{
    $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?');
    $stmt->execute([$table, $column]);
    return (bool)$stmt->fetchColumn();
}

function app_migration_sql(): array
{
    return [
        "CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','manager','agent') NOT NULL DEFAULT 'manager', is_active TINYINT(1) NOT NULL DEFAULT 1, two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0, two_factor_secret TEXT NULL, two_factor_confirmed_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS contacts (id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NULL, phone VARCHAR(30) NULL, email VARCHAR(190) NULL, company VARCHAR(190) NULL, tags VARCHAR(255) NULL, timezone VARCHAR(80) NULL, lifecycle_stage VARCHAR(50) NOT NULL DEFAULT 'lead', score INT NOT NULL DEFAULT 0, owner_user_id INT NULL, unsubscribed_sms TINYINT(1) NOT NULL DEFAULT 0, unsubscribed_email TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_contacts_phone (phone), INDEX idx_contacts_email (email), INDEX idx_contacts_stage (lifecycle_stage), INDEX idx_contacts_score (score))",
        "CREATE TABLE IF NOT EXISTS templates (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, type ENUM('sms','email') NOT NULL, subject VARCHAR(255) NULL, body MEDIUMTEXT NOT NULL, media_path VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS campaigns (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, channel ENUM('sms','email') NOT NULL, template_id INT NULL, created_by INT NULL, status ENUM('draft','queued','processing','sent','failed') NOT NULL DEFAULT 'draft', recipient_filter VARCHAR(190) NULL, scheduled_at DATETIME NULL, subject_override VARCHAR(255) NULL, body_override MEDIUMTEXT NULL, segment_name VARCHAR(190) NULL, segment_id INT NULL, recurring_parent_id INT NULL, next_occurrence_at DATETIME NULL, last_run_at DATETIME NULL, approval_status VARCHAR(30) NOT NULL DEFAULT 'not_required', approved_by INT NULL, approved_at DATETIME NULL, ab_enabled TINYINT(1) NOT NULL DEFAULT 0, variant_a_subject VARCHAR(255) NULL, variant_a_body MEDIUMTEXT NULL, variant_b_subject VARCHAR(255) NULL, variant_b_body MEDIUMTEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS messages (id INT AUTO_INCREMENT PRIMARY KEY, campaign_id INT NULL, contact_id INT NULL, channel ENUM('sms','email') NOT NULL, direction ENUM('inbound','outbound') NOT NULL, subject VARCHAR(255) NULL, body MEDIUMTEXT NULL, from_address VARCHAR(255) NULL, to_address VARCHAR(255) NULL, status VARCHAR(50) NOT NULL DEFAULT 'queued', external_id VARCHAR(255) NULL, media_url VARCHAR(500) NULL, attachment_path VARCHAR(255) NULL, metadata_json LONGTEXT NULL, thread_key VARCHAR(255) NULL, in_reply_to_external_id VARCHAR(255) NULL, references_header TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_messages_contact (contact_id), INDEX idx_messages_external (external_id), INDEX idx_messages_created (created_at), INDEX idx_messages_status (status), INDEX idx_messages_thread (thread_key))",
        "CREATE TABLE IF NOT EXISTS campaign_recipients (id INT AUTO_INCREMENT PRIMARY KEY, campaign_id INT NOT NULL, contact_id INT NOT NULL, address VARCHAR(255) NOT NULL, status ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued', message_id INT NULL, error_message TEXT NULL, processed_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS password_resets (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, email VARCHAR(190) NOT NULL, token_hash CHAR(64) NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_password_resets_hash (token_hash))",
        "CREATE TABLE IF NOT EXISTS settings (id INT AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(190) NOT NULL UNIQUE, setting_value TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS message_events (id INT AUTO_INCREMENT PRIMARY KEY, message_id INT NOT NULL, event_type ENUM('open','click','delivery','bounce','other') NOT NULL, event_value VARCHAR(500) NULL, metadata_json LONGTEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_message_events_message (message_id), INDEX idx_message_events_type (event_type))",
        "CREATE TABLE IF NOT EXISTS audit_logs (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, action VARCHAR(190) NOT NULL, entity_type VARCHAR(100) NOT NULL, entity_id INT NULL, details_json LONGTEXT NULL, ip_address VARCHAR(64) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_audit_logs_action (action), INDEX idx_audit_logs_entity (entity_type, entity_id))",
        "CREATE TABLE IF NOT EXISTS queue_jobs (id INT AUTO_INCREMENT PRIMARY KEY, job_type VARCHAR(100) NOT NULL, payload_json LONGTEXT NULL, status ENUM('queued','processing','done','failed') NOT NULL DEFAULT 'queued', attempts INT NOT NULL DEFAULT 0, last_error TEXT NULL, available_at DATETIME NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_queue_jobs_lookup (status, job_type, available_at))",
        "CREATE TABLE IF NOT EXISTS queue_dead_letters (id INT AUTO_INCREMENT PRIMARY KEY, queue_job_id INT NULL, job_type VARCHAR(100) NOT NULL, payload_json LONGTEXT NULL, last_error TEXT NULL, attempts INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS segments (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, channel ENUM('all','sms','email') NOT NULL DEFAULT 'all', tag_filter VARCHAR(190) NULL, company_filter VARCHAR(190) NULL, rule_json LONGTEXT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS user_permissions (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, permission_key VARCHAR(190) NOT NULL, is_allowed TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_user_permission (user_id, permission_key))",
        "CREATE TABLE IF NOT EXISTS suppression_list (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NULL, message_id INT NULL, channel ENUM('sms','email') NOT NULL, address VARCHAR(255) NOT NULL, normalized_address VARCHAR(255) NOT NULL, reason VARCHAR(190) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_suppression (channel, normalized_address))",
        "CREATE TABLE IF NOT EXISTS user_integration_profiles (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL UNIQUE, preferred_channel ENUM('sms','email') NOT NULL DEFAULT 'email', use_personal_twilio TINYINT(1) NOT NULL DEFAULT 0, twilio_sid VARCHAR(255) NULL, twilio_token TEXT NULL, twilio_from_number VARCHAR(50) NULL, use_personal_mail TINYINT(1) NOT NULL DEFAULT 0, mail_from_name VARCHAR(190) NULL, mail_from_email VARCHAR(190) NULL, smtp_enabled TINYINT(1) NOT NULL DEFAULT 0, smtp_host VARCHAR(190) NULL, smtp_port INT NOT NULL DEFAULT 587, smtp_username VARCHAR(190) NULL, smtp_password TEXT NULL, smtp_secure VARCHAR(20) NULL, reply_to_email VARCHAR(190) NULL, default_sms_footer TEXT NULL, default_email_footer TEXT NULL, signature_name VARCHAR(190) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS custom_fields (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, field_key VARCHAR(190) NOT NULL UNIQUE, field_type VARCHAR(50) NOT NULL DEFAULT 'text', sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS contact_custom_values (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, field_key VARCHAR(190) NOT NULL, field_value LONGTEXT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_contact_field (contact_id, field_key))",
        "CREATE TABLE IF NOT EXISTS consent_logs (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, channel ENUM('sms','email') NOT NULL, status VARCHAR(50) NOT NULL, source VARCHAR(190) NULL, proof_note TEXT NULL, ip_address VARCHAR(64) NULL, user_agent VARCHAR(255) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS workflows (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, trigger_event VARCHAR(80) NOT NULL DEFAULT 'contact_created', channel ENUM('sms','email') NOT NULL DEFAULT 'email', template_id INT NULL, delay_minutes INT NOT NULL DEFAULT 0, target_segment_id INT NULL, filters_json LONGTEXT NULL, action_json LONGTEXT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS contact_tasks (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, title VARCHAR(255) NOT NULL, due_at DATETIME NULL, status ENUM('open','done') NOT NULL DEFAULT 'open', assigned_user_id INT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS contact_notes (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, note_body MEDIUMTEXT NOT NULL, visibility VARCHAR(30) NOT NULL DEFAULT 'internal', created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS canned_replies (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(190) NOT NULL, channel ENUM('sms','email') NOT NULL DEFAULT 'email', subject VARCHAR(255) NULL, body MEDIUMTEXT NOT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS conversation_states (id INT AUTO_INCREMENT PRIMARY KEY, conversation_key VARCHAR(255) NOT NULL UNIQUE, status ENUM('open','pending','closed') NOT NULL DEFAULT 'open', assigned_user_id INT NULL, internal_note TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS accounts (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, industry VARCHAR(190) NULL, website VARCHAR(255) NULL, owner_user_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS pipelines (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, stage_order_json LONGTEXT NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS reminders (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, title VARCHAR(255) NOT NULL, remind_at DATETIME NULL, status ENUM('open','done') NOT NULL DEFAULT 'open', assigned_user_id INT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS login_history (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, email VARCHAR(190) NULL, status VARCHAR(30) NOT NULL DEFAULT 'success', message VARCHAR(255) NULL, ip_address VARCHAR(64) NULL, user_agent VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS contact_lists (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, channel ENUM('both','sms','email') NOT NULL DEFAULT 'both', description VARCHAR(255) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS contact_list_members (id INT AUTO_INCREMENT PRIMARY KEY, list_id INT NOT NULL, contact_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_list_member (list_id, contact_id))",
        "CREATE TABLE IF NOT EXISTS mail_folders (id INT AUTO_INCREMENT PRIMARY KEY, parent_id INT NULL, folder_key VARCHAR(190) NOT NULL UNIQUE, name VARCHAR(190) NOT NULL, is_system TINYINT(1) NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 100, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS mail_conversations (id INT AUTO_INCREMENT PRIMARY KEY, conversation_key VARCHAR(255) NOT NULL UNIQUE, folder_key VARCHAR(190) NULL, focus_bucket VARCHAR(20) NOT NULL DEFAULT 'focused', is_starred TINYINT(1) NOT NULL DEFAULT 0, is_archived TINYINT(1) NOT NULL DEFAULT 0, snoozed_until DATETIME NULL, assigned_user_id INT NULL, shared_mailbox_id INT NULL, sla_status VARCHAR(30) NOT NULL DEFAULT 'normal', approval_status VARCHAR(30) NOT NULL DEFAULT 'not_required', approval_requested_user_id INT NULL, approval_decided_user_id INT NULL, approval_decided_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS mail_labels (id INT AUTO_INCREMENT PRIMARY KEY, label_key VARCHAR(190) NOT NULL UNIQUE, name VARCHAR(190) NOT NULL, color_token VARCHAR(30) NOT NULL DEFAULT 'blue', created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS mail_conversation_labels (id INT AUTO_INCREMENT PRIMARY KEY, conversation_key VARCHAR(255) NOT NULL, label_id INT NOT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_mail_conversation_label (conversation_key, label_id))",
        "CREATE TABLE IF NOT EXISTS mail_rules (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, match_field VARCHAR(50) NOT NULL DEFAULT 'from_address', match_operator VARCHAR(20) NOT NULL DEFAULT 'contains', match_value VARCHAR(255) NOT NULL, target_folder_key VARCHAR(190) NULL, target_bucket VARCHAR(20) NULL, target_label_key VARCHAR(190) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 100, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS mail_drafts (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, draft_type VARCHAR(30) NOT NULL DEFAULT 'compose', conversation_key VARCHAR(255) NULL, contact_id INT NULL, to_address VARCHAR(255) NULL, subject VARCHAR(255) NULL, body MEDIUMTEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS mail_internal_notes (id INT AUTO_INCREMENT PRIMARY KEY, conversation_key VARCHAR(255) NOT NULL, note_body MEDIUMTEXT NOT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS shared_mailboxes (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, email_address VARCHAR(190) NOT NULL, description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS automation_rules (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, trigger_event VARCHAR(80) NOT NULL DEFAULT 'message_received', condition_json LONGTEXT NULL, action_json LONGTEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 100, last_run_at DATETIME NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS reputation_snapshots (id INT AUTO_INCREMENT PRIMARY KEY, provider VARCHAR(80) NOT NULL DEFAULT 'manual', domain_name VARCHAR(190) NOT NULL, source_name VARCHAR(190) NOT NULL DEFAULT 'Manual import', inbox_rate DECIMAL(6,2) NOT NULL DEFAULT 0, spam_rate DECIMAL(6,2) NOT NULL DEFAULT 0, bounce_rate DECIMAL(6,2) NOT NULL DEFAULT 0, complaint_rate DECIMAL(6,2) NOT NULL DEFAULT 0, reputation_score DECIMAL(6,2) NOT NULL DEFAULT 0, sample_size INT NOT NULL DEFAULT 0, observed_on DATE NOT NULL, metadata_json LONGTEXT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS event_webhook_logs (id INT AUTO_INCREMENT PRIMARY KEY, provider VARCHAR(80) NOT NULL DEFAULT 'unknown', event_type VARCHAR(80) NOT NULL DEFAULT 'other', message_id INT NULL, campaign_id INT NULL, external_id VARCHAR(255) NULL, signature_status VARCHAR(30) NOT NULL DEFAULT 'unknown', replay_status VARCHAR(30) NOT NULL DEFAULT 'captured', payload_json LONGTEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS connected_mailboxes (id INT AUTO_INCREMENT PRIMARY KEY, provider VARCHAR(50) NOT NULL DEFAULT 'gmail', name VARCHAR(190) NOT NULL, email_address VARCHAR(190) NOT NULL, account_identifier VARCHAR(255) NULL, scope_json LONGTEXT NULL, oauth_status VARCHAR(30) NOT NULL DEFAULT 'planned', sync_state VARCHAR(30) NOT NULL DEFAULT 'idle', access_token TEXT NULL, refresh_token TEXT NULL, token_expires_at DATETIME NULL, history_cursor VARCHAR(255) NULL, delta_link TEXT NULL, watch_topic VARCHAR(255) NULL, webhook_endpoint VARCHAR(255) NULL, subscription_id VARCHAR(255) NULL, subscription_expires_at DATETIME NULL, last_synced_at DATETIME NULL, last_error TEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_connected_mailbox_email (provider, email_address), INDEX idx_connected_mailboxes_provider (provider), INDEX idx_connected_mailboxes_state (sync_state))",
        "CREATE TABLE IF NOT EXISTS mailbox_webhook_subscriptions (id INT AUTO_INCREMENT PRIMARY KEY, connected_mailbox_id INT NOT NULL, provider VARCHAR(50) NOT NULL DEFAULT 'gmail', resource_name VARCHAR(255) NOT NULL DEFAULT 'mail', resource_scope VARCHAR(80) NOT NULL DEFAULT 'messages', subscription_id VARCHAR(255) NULL, expires_at DATETIME NULL, verification_state VARCHAR(30) NOT NULL DEFAULT 'planned', metadata_json LONGTEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_mailbox_scope (connected_mailbox_id, resource_scope), INDEX idx_mailbox_subscriptions_expires (expires_at))",
        "CREATE TABLE IF NOT EXISTS deliverability_domains (id INT AUTO_INCREMENT PRIMARY KEY, domain_name VARCHAR(190) NOT NULL UNIQUE, provider_hint VARCHAR(80) NOT NULL DEFAULT 'sendgrid', postmaster_status VARCHAR(30) NOT NULL DEFAULT 'planned', dkim_selector VARCHAR(80) NOT NULL DEFAULT 's1', spf_include VARCHAR(255) NOT NULL DEFAULT 'include:sendgrid.net', dmarc_policy VARCHAR(30) NOT NULL DEFAULT 'quarantine', return_path_domain VARCHAR(190) NULL, tracking_domain VARCHAR(190) NULL, bimi_svg_url VARCHAR(255) NULL, one_click_unsubscribe_enabled TINYINT(1) NOT NULL DEFAULT 1, warmup_profile VARCHAR(255) NULL, complaint_threshold DECIMAL(6,3) NOT NULL DEFAULT 0.100, bounce_threshold DECIMAL(6,2) NOT NULL DEFAULT 3.00, daily_volume_target INT NOT NULL DEFAULT 5000, last_checked_at DATETIME NULL, dns_summary_json LONGTEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_deliverability_domains_status (postmaster_status))",
        "CREATE TABLE IF NOT EXISTS domain_dns_checks (id INT AUTO_INCREMENT PRIMARY KEY, deliverability_domain_id INT NOT NULL, record_type VARCHAR(20) NOT NULL DEFAULT 'TXT', host_name VARCHAR(255) NOT NULL, expected_value TEXT NOT NULL, observed_value TEXT NULL, status VARCHAR(20) NOT NULL DEFAULT 'planned', details_json LONGTEXT NULL, checked_at DATETIME NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_domain_dns_checks_domain (deliverability_domain_id), INDEX idx_domain_dns_checks_status (status))",
        "CREATE TABLE IF NOT EXISTS connector_configs (id INT AUTO_INCREMENT PRIMARY KEY, connector_key VARCHAR(100) NOT NULL, name VARCHAR(190) NOT NULL, base_url VARCHAR(255) NULL, auth_type VARCHAR(40) NOT NULL DEFAULT 'bearer', access_token TEXT NULL, refresh_token TEXT NULL, config_json LONGTEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS mail_saved_views (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, filter_json LONGTEXT NULL, is_shared TINYINT(1) NOT NULL DEFAULT 0, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
        "CREATE TABLE IF NOT EXISTS mail_sender_profiles (id INT AUTO_INCREMENT PRIMARY KEY, display_name VARCHAR(190) NOT NULL, alias_email VARCHAR(190) NOT NULL, reply_to_email VARCHAR(190) NULL, signature_html MEDIUMTEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)",
    ];
}

function app_migration_alters(PDO $pdo): array
{
    $alters = [];
    $columns = [
        'contacts' => [
            'timezone' => "ALTER TABLE contacts ADD COLUMN timezone VARCHAR(80) NULL AFTER tags",
            'account_id' => "ALTER TABLE contacts ADD COLUMN account_id INT NULL AFTER owner_user_id",
            'pipeline_id' => "ALTER TABLE contacts ADD COLUMN pipeline_id INT NULL AFTER account_id",
            'lifecycle_stage' => "ALTER TABLE contacts ADD COLUMN lifecycle_stage VARCHAR(50) NOT NULL DEFAULT 'lead' AFTER timezone",
            'score' => "ALTER TABLE contacts ADD COLUMN score INT NOT NULL DEFAULT 0 AFTER lifecycle_stage",
            'owner_user_id' => "ALTER TABLE contacts ADD COLUMN owner_user_id INT NULL AFTER score",
        ],
        'campaigns' => [
            'scheduled_at' => "ALTER TABLE campaigns ADD COLUMN scheduled_at DATETIME NULL",
            'subject_override' => "ALTER TABLE campaigns ADD COLUMN subject_override VARCHAR(255) NULL",
            'body_override' => "ALTER TABLE campaigns ADD COLUMN body_override MEDIUMTEXT NULL",
            'segment_name' => "ALTER TABLE campaigns ADD COLUMN segment_name VARCHAR(190) NULL",
            'segment_id' => "ALTER TABLE campaigns ADD COLUMN segment_id INT NULL AFTER segment_name",
            'recurring_parent_id' => "ALTER TABLE campaigns ADD COLUMN recurring_parent_id INT NULL AFTER segment_id",
            'next_occurrence_at' => "ALTER TABLE campaigns ADD COLUMN next_occurrence_at DATETIME NULL AFTER recurring_parent_id",
            'last_run_at' => "ALTER TABLE campaigns ADD COLUMN last_run_at DATETIME NULL AFTER next_occurrence_at",
            'created_by' => "ALTER TABLE campaigns ADD COLUMN created_by INT NULL",
            'approval_status' => "ALTER TABLE campaigns ADD COLUMN approval_status VARCHAR(30) NOT NULL DEFAULT 'not_required'",
            'approved_by' => "ALTER TABLE campaigns ADD COLUMN approved_by INT NULL",
            'approved_at' => "ALTER TABLE campaigns ADD COLUMN approved_at DATETIME NULL",
            'ab_enabled' => "ALTER TABLE campaigns ADD COLUMN ab_enabled TINYINT(1) NOT NULL DEFAULT 0",
            'variant_a_subject' => "ALTER TABLE campaigns ADD COLUMN variant_a_subject VARCHAR(255) NULL",
            'variant_a_body' => "ALTER TABLE campaigns ADD COLUMN variant_a_body MEDIUMTEXT NULL",
            'variant_b_subject' => "ALTER TABLE campaigns ADD COLUMN variant_b_subject VARCHAR(255) NULL",
            'variant_b_body' => "ALTER TABLE campaigns ADD COLUMN variant_b_body MEDIUMTEXT NULL",
            'recurring_enabled' => "ALTER TABLE campaigns ADD COLUMN recurring_enabled TINYINT(1) NOT NULL DEFAULT 0",
            'recurring_rule' => "ALTER TABLE campaigns ADD COLUMN recurring_rule VARCHAR(80) NULL",
            'send_time_optimization' => "ALTER TABLE campaigns ADD COLUMN send_time_optimization TINYINT(1) NOT NULL DEFAULT 0",
            'builder_json' => "ALTER TABLE campaigns ADD COLUMN builder_json LONGTEXT NULL",
            'contact_list_id' => "ALTER TABLE campaigns ADD COLUMN contact_list_id INT NULL AFTER segment_id",
        ],
        'messages' => [
            'media_url' => "ALTER TABLE messages ADD COLUMN media_url VARCHAR(500) NULL",
            'attachment_path' => "ALTER TABLE messages ADD COLUMN attachment_path VARCHAR(255) NULL",
            'metadata_json' => "ALTER TABLE messages ADD COLUMN metadata_json LONGTEXT NULL",
            'thread_key' => "ALTER TABLE messages ADD COLUMN thread_key VARCHAR(255) NULL",
            'in_reply_to_external_id' => "ALTER TABLE messages ADD COLUMN in_reply_to_external_id VARCHAR(255) NULL",
            'references_header' => "ALTER TABLE messages ADD COLUMN references_header TEXT NULL",
        ],
        'queue_jobs' => [
            'attempts' => "ALTER TABLE queue_jobs ADD COLUMN attempts INT NOT NULL DEFAULT 0",
            'last_error' => "ALTER TABLE queue_jobs ADD COLUMN last_error TEXT NULL",
            'updated_at' => "ALTER TABLE queue_jobs ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
        ],
        'contact_lists' => [],
        'contact_list_members' => [],
        'segments' => [
            'rule_json' => "ALTER TABLE segments ADD COLUMN rule_json LONGTEXT NULL AFTER company_filter",
        ],
        'mail_conversations' => [
            'is_pinned' => "ALTER TABLE mail_conversations ADD COLUMN is_pinned TINYINT(1) NOT NULL DEFAULT 0 AFTER is_starred",
            'is_muted' => "ALTER TABLE mail_conversations ADD COLUMN is_muted TINYINT(1) NOT NULL DEFAULT 0 AFTER is_pinned",
            'sensitivity_level' => "ALTER TABLE mail_conversations ADD COLUMN sensitivity_level VARCHAR(30) NOT NULL DEFAULT 'standard' AFTER is_muted",
            'claimed_by_user_id' => "ALTER TABLE mail_conversations ADD COLUMN claimed_by_user_id INT NULL AFTER assigned_user_id",
            'claimed_at' => "ALTER TABLE mail_conversations ADD COLUMN claimed_at DATETIME NULL AFTER claimed_by_user_id",
        ],

    ];
    foreach ($columns as $table => $map) {
        if (!migration_table_exists($pdo, $table)) {
            continue;
        }
        foreach ($map as $column => $sql) {
            if (!migration_column_exists($pdo, $table, $column)) {
                $alters[] = $sql;
            }
        }
    }
    return $alters;
}

function app_seed_defaults(PDO $pdo, ?string $adminName = null, ?string $adminEmail = null, ?string $adminPassword = null): void
{
    $versionFile = dirname(__DIR__) . '/VERSION';
    $version = is_file($versionFile) ? trim((string)file_get_contents($versionFile)) : (string)config('app.version', '15.5');
    if ($version === '') {
        $version = (string)config('app.version', '15.5');
    }
    $stmt = $pdo->prepare('INSERT INTO settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), updated_at = NOW()');
    $stmt->execute(['schema_version', $version]);
    $stmt->execute(['worker_last_heartbeat', date('Y-m-d H:i:s')]);
    $stmt->execute(['quiet_hours_enabled', '0']);
    $stmt->execute(['quiet_hours_start', '20:00']);
    $stmt->execute(['quiet_hours_end', '08:00']);
    $stmt->execute(['quiet_hours_timezone', (string)config('app.timezone', 'America/Chicago')]);
    $stmt->execute(['suppression_automation_enabled', '1']);
    $adminEmail = trim((string)$adminEmail);
    if ($adminEmail !== '' && $adminPassword !== null && $adminPassword !== '') {
        $stmt = $pdo->prepare('SELECT id FROM users WHERE email = ? LIMIT 1');
        $stmt->execute([$adminEmail]);
        $id = $stmt->fetchColumn();
        if ($id) {
            $stmt = $pdo->prepare('UPDATE users SET name = ?, password_hash = ?, role = "admin", is_active = 1 WHERE id = ?');
            $stmt->execute([$adminName ?: 'System Admin', password_hash($adminPassword, PASSWORD_DEFAULT), $id]);
        } else {
            $stmt = $pdo->prepare('INSERT INTO users (name, email, password_hash, role, is_active) VALUES (?, ?, ?, "admin", 1)');
            $stmt->execute([$adminName ?: 'System Admin', $adminEmail, password_hash($adminPassword, PASSWORD_DEFAULT)]);
        }
    } else {
        $pdo->exec("INSERT IGNORE INTO users (id, name, email, password_hash, role, is_active) VALUES (1, 'System Admin', 'admin@example.com', '$2y$12$YzTPQ9mij7.9u0Q03l8plujOfiXbkhoYwkAnsJgiBIJjjiLSfZDzC', 'admin', 1)");
    }
    $pdo->exec("INSERT INTO templates (name, type, subject, body, media_path) VALUES ('Welcome SMS', 'sms', NULL, 'Hi {{first_name}}, thanks for connecting with {{company}}. Reply STOP to unsubscribe.', NULL) ON DUPLICATE KEY UPDATE name = VALUES(name)");
    $pdo->exec("INSERT INTO templates (name, type, subject, body, media_path) VALUES ('Promo Email', 'email', 'Special offer for {{first_name}}', '<h2>Hello {{first_name}}</h2><p>We have a new offer for {{company}}.</p><p><a href=\"https://example.com/offers\">View Offer</a></p><p><a href=\"{{unsubscribe_url}}\">Unsubscribe</a></p>', NULL) ON DUPLICATE KEY UPDATE name = VALUES(name)");
}


function app_apply_data_repairs(PDO $pdo): array
{
    $ran = [];
    if (migration_table_exists($pdo, 'messages')) {
        $sql = "UPDATE messages m
            LEFT JOIN campaign_recipients cr ON cr.message_id = m.id AND cr.status = 'failed'
            SET m.status = 'failed',
                m.metadata_json = CASE
                    WHEN m.metadata_json IS NULL OR m.metadata_json = '' THEN JSON_OBJECT('repair_tag', 'v16.4.1_outbox_status', 'failed_at', NOW(), 'repair_reason', 'stuck_queued_email')
                    ELSE JSON_SET(m.metadata_json, '$.repair_tag', 'v16.4.1_outbox_status', '$.failed_at', NOW(), '$.repair_reason', 'stuck_queued_email')
                END,
                m.updated_at = NOW()
            WHERE m.channel = 'email'
              AND m.direction = 'outbound'
              AND m.status IN ('queued', 'processing')
              AND (
                    cr.id IS NOT NULL
                    OR m.created_at < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
                  )";
        $pdo->exec($sql);
        $ran[] = 'repair:messages_outbox_status';
    }
    if (migration_table_exists($pdo, 'mail_conversations') && migration_column_exists($pdo, 'mail_conversations', 'folder_key')) {
        $pdo->exec("UPDATE mail_conversations SET folder_key = NULL, updated_at = NOW() WHERE folder_key IN ('outbox', 'sent')");
        $ran[] = 'repair:mail_conversations_release_system_folders';
    }
    return $ran;
}

function app_run_migrations(PDO $pdo, bool $seedDefaults = true, ?string $adminName = null, ?string $adminEmail = null, ?string $adminPassword = null): array
{
    $ran = [];
    foreach (app_migration_sql() as $sql) {
        $pdo->exec($sql);
        $ran[] = $sql;
    }
    foreach (app_migration_alters($pdo) as $sql) {
        $pdo->exec($sql);
        $ran[] = $sql;
    }
    foreach (app_apply_data_repairs($pdo) as $repair) {
        $ran[] = $repair;
    }
    if ($seedDefaults) {
        app_seed_defaults($pdo, $adminName, $adminEmail, $adminPassword);
    }
    return $ran;
}
