<?php
/**
 * Database schema and table management.
 *
 * @link       https://github.com/sitestaffr/sitestaffr-plugin
 * @since      1.0.0
 *
 * @package    SiteStaffr
 * @subpackage SiteStaffr/includes
 */

/**
 * Database schema and table management class.
 *
 * This class defines all code necessary to create and manage
 * custom database tables for the plugin.
 *
 * @since      1.0.0
 * @package    SiteStaffr
 * @subpackage SiteStaffr/includes
 * @author     SiteStaffr
 */
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
class SiteStaffr_Database {

    /**
     * Create plugin database tables.
     *
     * Called during plugin activation to create custom tables
     * for storing calls, transcripts, and AI metadata.
     *
     * @since    1.0.0
     */
    public static function create_tables() {
        global $wpdb;

        // Table names with WordPress prefix
        $calls_table = $wpdb->prefix . 'phoneease_calls';
        $transcripts_table = $wpdb->prefix . 'phoneease_transcripts';
        $ai_metadata_table = $wpdb->prefix . 'phoneease_ai_metadata';
        $number_assignments_table = $wpdb->prefix . 'phoneease_number_assignments';
        $training_history_table = $wpdb->prefix . 'phoneease_training_history';
        $knowledge_files_table = $wpdb->prefix . 'phoneease_knowledge_files';
        $billing_cycle_snapshots_table = $wpdb->prefix . 'phoneease_billing_cycle_snapshots';
        $knowledge_table = $wpdb->prefix . 'phoneease_knowledge';
        $blog_posts_table = $wpdb->prefix . 'sitestaffr_blog_posts';

        // Charset and collation
        $charset_collate = 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';

        // SQL for calls table
        $sql_calls = "CREATE TABLE $calls_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            session_id VARCHAR(128) NOT NULL,
            service_type VARCHAR(20) NOT NULL DEFAULT 'phone',
            persona VARCHAR(50) NOT NULL DEFAULT 'default',
            ended_by VARCHAR(20) NULL,
            from_number VARCHAR(20) NULL,
            to_number VARCHAR(20) NULL,
            visitor_ip VARCHAR(45) NULL,
            call_status VARCHAR(20) NOT NULL,
            is_billable TINYINT(1) DEFAULT 1,
            call_duration INT UNSIGNED DEFAULT 0,
            recording_url VARCHAR(500),
            started_at BIGINT UNSIGNED NOT NULL DEFAULT 0,
            ended_at DATETIME,
            is_read TINYINT(1) DEFAULT 0,
            is_test_call TINYINT(1) DEFAULT 0,
            reviewed_at DATETIME NULL,
            handled_at DATETIME NULL,
            ai_summary TEXT NULL,
            ai_followup TEXT NULL,
            ai_model VARCHAR(100) NULL,
            is_qualified_lead TINYINT(1) NOT NULL DEFAULT 0,
            lead_name VARCHAR(191) NULL,
            lead_phone VARCHAR(64) NULL,
            lead_email VARCHAR(191) NULL,
            lead_interest TEXT NULL,
            salesforce_sync VARCHAR(20) NULL,
            salesforce_lead_id VARCHAR(32) NULL,
            source_page_url TEXT NULL,
            source_page_title VARCHAR(255) NULL,
            deleted_at DATETIME DEFAULT NULL,
            purged_at DATETIME DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_session_id (session_id),
            INDEX idx_service_type (service_type),
            INDEX idx_persona (persona),
            INDEX idx_from_number (from_number),
            INDEX idx_visitor_ip (visitor_ip),
            INDEX idx_started_at (started_at),
            INDEX idx_status (call_status),
            INDEX idx_is_billable (is_billable),
            INDEX idx_is_read (is_read),
            INDEX idx_handled_at (handled_at),
            INDEX idx_is_qualified_lead (is_qualified_lead),
            INDEX idx_is_test_call (is_test_call),
            INDEX idx_created_at (created_at),
            INDEX idx_deleted_at (deleted_at),
            INDEX idx_purged_at (purged_at)
        ) $charset_collate;";

        // SQL for transcripts table
        $sql_transcripts = "CREATE TABLE $transcripts_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            call_id BIGINT UNSIGNED NOT NULL,
            speaker VARCHAR(10) NOT NULL,
            message_text TEXT NOT NULL,
            confidence_score DECIMAL(3,2),
            source_chunks LONGTEXT NULL,
            timestamp DATETIME NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_call_id (call_id),
            INDEX idx_speaker (speaker),
            INDEX idx_timestamp (timestamp)
        ) $charset_collate;";

        // SQL for AI metadata table
        $sql_ai_metadata = "CREATE TABLE $ai_metadata_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            call_id BIGINT UNSIGNED NOT NULL,
            prompt_tokens INT UNSIGNED DEFAULT 0,
            completion_tokens INT UNSIGNED DEFAULT 0,
            cached_tokens INT UNSIGNED DEFAULT 0 COMMENT 'Cache read tokens from prompt caching',
            total_tokens INT UNSIGNED DEFAULT 0,
            model_used VARCHAR(50),
            operation_type VARCHAR(20) DEFAULT 'conversation',
            api_response_time_ms INT UNSIGNED,
            error_message TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_call_id (call_id),
            INDEX idx_operation_type (operation_type)
        ) $charset_collate;";

        // SQL for number assignments table (tracks provisioned numbers)
        $sql_number_assignments = "CREATE TABLE $number_assignments_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            site_id BIGINT UNSIGNED DEFAULT 1,
            phone_number VARCHAR(20) NOT NULL,
            phone_number_sid VARCHAR(50),
            area_code VARCHAR(10),
            friendly_name VARCHAR(100),
            assigned_at DATETIME NOT NULL,
            released_at DATETIME,
            status VARCHAR(20) DEFAULT 'active',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE INDEX idx_phone_number (phone_number),
            INDEX idx_site_id (site_id),
            INDEX idx_status (status),
            INDEX idx_area_code (area_code)
        ) $charset_collate;";

        // SQL for training history table (stores AI training conversations)
        $sql_training_history = "CREATE TABLE $training_history_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            user_id BIGINT UNSIGNED NOT NULL,
            user_message TEXT NOT NULL,
            ai_response TEXT NOT NULL,
            timestamp DATETIME NOT NULL,
            tokens_used INT,
            status VARCHAR(20) NOT NULL DEFAULT 'active',
            INDEX idx_user_id (user_id),
            INDEX idx_timestamp (timestamp),
            INDEX idx_status (status)
        ) $charset_collate;";

        // SQL for knowledge files table (stores uploaded documents for AI training)
        $sql_knowledge_files = "CREATE TABLE $knowledge_files_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            filename VARCHAR(255) NOT NULL,
            file_path VARCHAR(500) NOT NULL,
            file_url VARCHAR(500) NOT NULL,
            file_size BIGINT UNSIGNED NOT NULL,
            mime_type VARCHAR(100) NOT NULL,
            user_id BIGINT UNSIGNED NOT NULL,
            uploaded_at DATETIME NOT NULL,
            deleted_at DATETIME DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_id (user_id),
            INDEX idx_uploaded_at (uploaded_at),
            INDEX idx_deleted_at (deleted_at)
        ) $charset_collate;";

        // SQL for billing cycle snapshots (historical plan/minute state per cycle).
        $sql_billing_cycle_snapshots = "CREATE TABLE $billing_cycle_snapshots_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            period_start DATETIME NOT NULL,
            period_end DATETIME NOT NULL,
            plan_name VARCHAR(50) NOT NULL,
            subscription_status VARCHAR(30) NOT NULL,
            included_seconds_remaining INT NOT NULL DEFAULT 0,
            addon_seconds_remaining INT NOT NULL DEFAULT 0,
            included_minutes_remaining INT NOT NULL DEFAULT 0,
            addon_minutes_remaining INT NOT NULL DEFAULT 0,
            source VARCHAR(30) NOT NULL DEFAULT 'middleware_sync',
            captured_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_period_window (period_start, period_end),
            INDEX idx_period_start (period_start),
            INDEX idx_captured_at (captured_at)
        ) $charset_collate;";

        // SQL for knowledge table (stores page/post text chunks and embeddings for RAG)
        $sql_knowledge = "CREATE TABLE $knowledge_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            post_id BIGINT UNSIGNED NOT NULL,
            post_title VARCHAR(255) NOT NULL,
            post_type VARCHAR(20) NOT NULL DEFAULT 'page',
            chunk_index INT UNSIGNED NOT NULL DEFAULT 0,
            chunk_text LONGTEXT NOT NULL,
            chunk_title VARCHAR(200) DEFAULT NULL,
            embedding LONGBLOB DEFAULT NULL,
            source VARCHAR(10) NOT NULL DEFAULT 'auto',
            hit_count INT UNSIGNED NOT NULL DEFAULT 0,
            synced_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_post_id (post_id),
            INDEX idx_post_type (post_type),
            INDEX idx_synced_at (synced_at),
            INDEX idx_hit_count (hit_count),
            INDEX idx_source (source),
            FULLTEXT KEY idx_chunk_text_ft (chunk_title, chunk_text)
        ) $charset_collate;";

        // SQL for blog posts table (Blog Agent — tracks generation jobs and draft mapping)
        $sql_blog_posts = "CREATE TABLE $blog_posts_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            job_id VARCHAR(64) NOT NULL,
            wp_post_id BIGINT UNSIGNED NULL,
            topic TEXT NOT NULL,
            keyword VARCHAR(255) NOT NULL DEFAULT '',
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            request_payload LONGTEXT NULL,
            response_payload LONGTEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            completed_at DATETIME NULL,
            INDEX idx_job_id (job_id),
            INDEX idx_status (status),
            INDEX idx_wp_post_id (wp_post_id),
            INDEX idx_created_at (created_at)
        ) $charset_collate;";

        // Include upgrade.php for dbDelta function
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // Execute table creation
        dbDelta( $sql_calls );
        dbDelta( $sql_transcripts );
        dbDelta( $sql_ai_metadata );
        dbDelta( $sql_number_assignments );
        dbDelta( $sql_training_history );
        dbDelta( $sql_knowledge_files );
        dbDelta( $sql_billing_cycle_snapshots );
        dbDelta( $sql_knowledge );
        dbDelta( $sql_blog_posts );

        // Run database upgrades for existing installations
        self::upgrade_database();

        // Run minute-based billing migration
        self::run_minute_based_migration();

        // Initialize default settings using WordPress options
        self::init_default_settings();
    }

    /**
     * Initialize default plugin settings.
     *
     * Uses WordPress wp_options instead of custom table.
     *
     * @since    1.0.0
     */
    public static function init_default_settings() {
        $default_settings = array(
            'claude_api_key'       => '',
            'business_name'        => '',
            'business_context'     => '',
            'call_greeting'        => 'Hello, thank you for calling. How can I help you today?',
            'enabled'              => true,
            'data_retention_days'  => 90,
        );

        // Only add if not already set
        if ( false === get_option( 'sitestaffr_settings' ) ) {
            add_option( 'sitestaffr_settings', $default_settings );
        }
    }

    /**
     * Get the knowledge table name.
     *
     * @since    1.7.0
     * @return   string  Full table name with WordPress prefix.
     */
    public static function get_knowledge_table() {
        global $wpdb;
        return $wpdb->prefix . 'phoneease_knowledge';
    }

    /**
     * Get the blog posts table name.
     *
     * @since 1.21.0
     * @return string Full table name with WordPress prefix.
     */
    public static function get_blog_posts_table() {
        global $wpdb;
        return $wpdb->prefix . 'sitestaffr_blog_posts';
    }

    /**
     * Drop plugin database tables.
     *
     * Called during plugin uninstallation to remove custom tables.
     * Only use this if you want to completely remove all data.
     *
     * @since    1.0.0
     */
    public static function drop_tables() {
        global $wpdb;

        $calls_table = $wpdb->prefix . 'phoneease_calls';
        $transcripts_table = $wpdb->prefix . 'phoneease_transcripts';
        $ai_metadata_table = $wpdb->prefix . 'phoneease_ai_metadata';
        $number_assignments_table = $wpdb->prefix . 'phoneease_number_assignments';
        $training_history_table = $wpdb->prefix . 'phoneease_training_history';
        $knowledge_files_table = $wpdb->prefix . 'phoneease_knowledge_files';
        $billing_cycle_snapshots_table = $wpdb->prefix . 'phoneease_billing_cycle_snapshots';
        $knowledge_table = $wpdb->prefix . 'phoneease_knowledge';
        $blog_posts_table = $wpdb->prefix . 'sitestaffr_blog_posts';

        // Temporarily disable foreign key checks to ensure DROP succeeds
        // even on hosts where constraint ordering is enforced strictly.
        $wpdb->query( 'SET FOREIGN_KEY_CHECKS = 0' );

        // Drop all tables. Check $wpdb->last_error after each statement
        // so silent failures are surfaced.
        $tables_to_drop = array(
            $ai_metadata_table,
            $transcripts_table,
            $calls_table,
            $number_assignments_table,
            $training_history_table,
            $knowledge_files_table,
            $billing_cycle_snapshots_table,
            $knowledge_table,
            $blog_posts_table,
        );

        foreach ( $tables_to_drop as $table ) {
            $wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) );
            if ( ! empty( $wpdb->last_error ) ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr drop_tables: Failed to drop ' . $table . ' — ' . $wpdb->last_error );
            }
        }

        // Re-enable foreign key checks.
        $wpdb->query( 'SET FOREIGN_KEY_CHECKS = 1' );

        // Remove options
        delete_option( 'sitestaffr_db_version' );
        delete_option( 'sitestaffr_settings' );
    }

    /**
     * Record a phone number assignment.
     *
     * @since    1.0.0
     * @param    string    $phone_number      Phone number in E.164 format.
     * @param    string    $phone_number_sid  Legacy phone number SID.
     * @param    string    $area_code         Area code.
     * @param    string    $friendly_name     Friendly name from upstream provider.
     * @return   int|false                    Insert ID on success, false on failure.
     */
    public static function record_number_assignment( $phone_number, $phone_number_sid, $area_code = '', $friendly_name = '' ) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_number_assignments';

        $result = $wpdb->insert(
            $table_name,
            array(
                'site_id'          => get_current_blog_id(),
                'phone_number'     => $phone_number,
                'phone_number_sid' => $phone_number_sid,
                'area_code'        => $area_code,
                'friendly_name'    => $friendly_name,
                'assigned_at'      => current_time( 'mysql' ),
                'status'           => 'active',
                'created_at'       => current_time( 'mysql' ),
            ),
            array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
        );

        if ( $result === false ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to record number assignment - ' . $wpdb->last_error );
            return false;
        }

        return $wpdb->insert_id;
    }

    /**
     * Get active number assignment for current site.
     *
     * @since    1.0.0
     * @return   object|null    Number assignment object or null.
     */
    public static function get_active_number_assignment() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_number_assignments';
        $site_id = get_current_blog_id();

        return $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM %i WHERE site_id = %d AND status = 'active' LIMIT 1",
                $table_name,
                $site_id
            )
        );
    }

    /**
     * Check if site already has a provisioned number.
     *
     * @since    1.0.0
     * @return   bool    True if site has an active number.
     */
    public static function has_active_number() {
        return self::get_active_number_assignment() !== null;
    }

    /**
     * Mark a number as released.
     *
     * @since    1.0.0
     * @param    string    $phone_number    Phone number to release.
     * @return   bool                       True on success, false on failure.
     */
    public static function release_number_assignment( $phone_number ) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_number_assignments';

        $result = $wpdb->update(
            $table_name,
            array(
                'status'      => 'released',
                'released_at' => current_time( 'mysql' ),
            ),
            array( 'phone_number' => $phone_number ),
            array( '%s', '%s' ),
            array( '%s' )
        );

        return $result !== false;
    }

    /**
     * Check if database tables exist.
     *
     * @since    1.0.0
     * @return   bool    True if tables exist, false otherwise.
     */
    public static function tables_exist() {
        global $wpdb;

        $calls_table = $wpdb->prefix . 'phoneease_calls';

        return $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $calls_table ) )
        ) === $calls_table;
    }

    /**
     * Get current database version.
     *
     * @since    1.0.0
     * @return   string    Database version number.
     */
    public static function get_db_version() {
        return get_option( 'sitestaffr_db_version', '0.0.0' );
    }

    /**
     * Check if database needs upgrade.
     *
     * @since    1.0.0
     * @return   bool    True if upgrade needed, false otherwise.
     */
    public static function needs_upgrade() {
        return version_compare( self::get_db_version(), '1.0.0', '<' );
    }

    /**
     * Get plugin settings.
     *
     * @since    1.0.0
     * @return   array    Plugin settings array.
     */
    public static function get_settings() {
        return get_option( 'sitestaffr_settings', array() );
    }

    /**
     * Update plugin settings.
     *
     * @since    1.0.0
     * @param    array    $settings    Settings array to update.
     * @return   bool     True if updated, false otherwise.
     */
    public static function update_settings( $settings ) {
        return update_option( 'sitestaffr_settings', $settings );
    }

    /**
     * Get the last test call timestamp and status.
     *
     * @since    1.0.0
     * @return   object|null    Test call data or null if none.
     */
    public static function get_last_test_call() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_calls';

        return $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id, session_id, call_status, call_duration, created_at
                 FROM %i
                 WHERE is_test_call = %d
                 ORDER BY created_at DESC
                 LIMIT 1",
                $table_name,
                1
            )
        );
    }

    /**
     * Log a test call to the database.
     *
     * @since    1.0.0
     * @param    string    $session_id     Session identifier.
     * @param    string    $from_number    From phone number (provisioned number) - can be null for WebRTC.
     * @param    string    $to_number      To phone number (owner's phone) - can be null for WebRTC.
     * @param    string    $service_type   Service type: 'phone' (default) or 'webrtc'.
     * @return   int|false                 Insert ID on success, false on failure.
     */
    public static function log_test_call( $session_id, $from_number = null, $to_number = null, $service_type = 'phone' ) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_calls';

        $result = $wpdb->insert(
            $table_name,
            array(
                'session_id'   => $session_id,
                'service_type' => $service_type,
                'from_number'  => $from_number,
                'to_number'    => $to_number,
                'call_status'  => 'initiated',
                'started_at'   => time(), // Unix timestamp (timezone-agnostic)
                'is_read'      => 0,
                'is_test_call' => 1,
                'created_at'   => current_time( 'mysql' ),
            ),
            array( '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%s' ) // started_at is now %d (int) instead of %s (string)
        );

        if ( $result === false ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to log test call - ' . $wpdb->last_error );
            return false;
        }

        return $wpdb->insert_id;
    }

    /**
     * Mark a call as a test call.
     *
     * @since    1.0.0
     * @param    string    $session_id    Session identifier.
     * @return   bool                     True on success, false on failure.
     */
    public static function mark_as_test_call( $session_id ) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_calls';

        $result = $wpdb->update(
            $table_name,
            array( 'is_test_call' => 1 ),
            array( 'session_id' => $session_id ),
            array( '%d' ),
            array( '%s' )
        );

        return $result !== false;
    }

    /**
     * Check if a call is a test call.
     *
     * @since    1.0.0
     * @param    string    $session_id    Session identifier.
     * @return   bool                     True if test call, false otherwise.
     */
    public static function is_test_call( $session_id ) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_calls';

        $is_test = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT is_test_call FROM %i WHERE session_id = %s',
                $table_name,
                $session_id
            )
        );

        return (bool) $is_test;
    }

    /**
     * Upgrade database schema for existing installations.
     *
     * Checks for missing columns and adds them if needed.
     * Safe to run on every plugin load - only performs ALTER TABLE if columns are missing.
     *
     * @since    1.0.0
     */
    public static function upgrade_database() {
        global $wpdb;

        $calls_table = $wpdb->prefix . 'phoneease_calls';

        // Check if table exists first
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $calls_table ) )
        );

        if ( $table_exists !== $calls_table ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Calls table does not exist, skipping upgrade' );
            return false;
        }

        // Track if any upgrades were performed
        $upgrades_performed = false;

        // Migrate call_sid → session_id (VARCHAR(34) → VARCHAR(128)) for OpenAI Realtime session IDs.
        $has_call_sid = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'call_sid'
            )
        );

        if ( ! empty( $has_call_sid ) ) {
            $rename_result = $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i CHANGE COLUMN `call_sid` `session_id` VARCHAR(128) NOT NULL', $calls_table )
            );

            if ( false === $rename_result ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to rename call_sid to session_id - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully renamed call_sid to session_id VARCHAR(128)' );
                $upgrades_performed = true;

                // Rename the index as well.
                $old_index = $wpdb->get_results(
                    $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_call_sid'", $calls_table )
                );
                if ( ! empty( $old_index ) ) {
                    $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP INDEX `idx_call_sid`, ADD INDEX `idx_session_id` (`session_id`)', $calls_table ) );
                }
            }
        }

        // Check if is_billable column exists
        $column_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'is_billable'
            )
        );

        // Add is_billable column if it doesn't exist
        if ( empty( $column_exists ) ) {
            // Add column
            $result = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN is_billable TINYINT(1) DEFAULT 1 AFTER call_status',
                    $calls_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add is_billable column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added is_billable column to calls table' );
                $upgrades_performed = true;

                // Check if index already exists before adding
                $index_exists = $wpdb->get_results(
                    $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_is_billable'", $calls_table )
                );

                if ( empty( $index_exists ) ) {
                    $index_result = $wpdb->query(
                        $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_is_billable (is_billable)', $calls_table )
                    );

                    if ( $index_result === false ) {
                        SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add idx_is_billable index - ' . $wpdb->last_error );
                    } else {
                        SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added idx_is_billable index' );
                    }
                }
            }
        }

        // Check if ai_summary column exists
        $ai_summary_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'ai_summary'
            )
        );

        // Add ai_summary and ai_followup columns if they don't exist
        if ( empty( $ai_summary_exists ) ) {
            // Add ai_summary column
            $result = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN ai_summary TEXT NULL AFTER reviewed_at',
                    $calls_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add ai_summary column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added ai_summary column to calls table' );
                $upgrades_performed = true;
            }

            // Add ai_followup column
            $result2 = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN ai_followup TEXT NULL AFTER ai_summary',
                    $calls_table
                )
            );

            if ( $result2 === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add ai_followup column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added ai_followup column to calls table' );
            }
        }

        // Check if ai_model column exists
        $ai_model_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'ai_model'
            )
        );

        // Add ai_model column if it doesn't exist
        if ( empty( $ai_model_exists ) ) {
            // Add ai_model column
            $result = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN ai_model VARCHAR(100) NULL AFTER ai_followup',
                    $calls_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add ai_model column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added ai_model column to calls table' );
                $upgrades_performed = true;
            }
        }

        // Check if started_at needs to be converted from DATETIME to BIGINT UNSIGNED
        // This fixes the critical bug where Unix timestamps were stored in DATETIME columns
        $started_at_column = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'started_at'
            )
        );

        if ( ! empty( $started_at_column ) ) {
            $column_type = strtoupper( $started_at_column[0]->Type );

            // Check if column is still DATETIME (needs conversion to BIGINT UNSIGNED)
            if ( strpos( $column_type, 'DATETIME' ) !== false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Converting started_at from DATETIME to BIGINT UNSIGNED' );

                // Step 1: Add temporary column to store converted Unix timestamps
                $wpdb->query(
                    $wpdb->prepare(
                        'ALTER TABLE %i ADD COLUMN started_at_temp BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER started_at',
                        $calls_table
                    )
                );

                // Step 2: Convert existing DATETIME values to Unix timestamps
                // For valid DATETIME values, convert using UNIX_TIMESTAMP()
                // For corrupted/invalid values (which is most of them due to this bug), set to 0
                $wpdb->query(
                    $wpdb->prepare(
                        "UPDATE %i SET started_at_temp = CASE WHEN started_at > '1970-01-01 00:00:01' AND started_at < '2038-01-19 03:14:07' THEN UNIX_TIMESTAMP(started_at) ELSE 0 END",
                        $calls_table
                    )
                );

                // Step 3: Drop the old DATETIME column
                $wpdb->query(
                    $wpdb->prepare( 'ALTER TABLE %i DROP COLUMN started_at', $calls_table )
                );

                // Step 4: Rename the temp column to started_at
                $wpdb->query(
                    $wpdb->prepare( 'ALTER TABLE %i CHANGE COLUMN started_at_temp started_at BIGINT UNSIGNED NOT NULL DEFAULT 0', $calls_table )
                );

                // Step 5: Verify index still exists (should have been preserved)
                $index_exists = $wpdb->get_results(
                    $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_started_at'", $calls_table )
                );

                // Recreate index if it was dropped during column change
                if ( empty( $index_exists ) ) {
                    $wpdb->query(
                        $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_started_at (started_at)', $calls_table )
                    );
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Recreated idx_started_at index after column type change' );
                }

                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully converted started_at from DATETIME to BIGINT UNSIGNED' );
                $upgrades_performed = true;

                // Update database version to prevent re-running this migration
                update_option( 'sitestaffr_db_version', '1.1.0' );
                SiteStaffr_Logger::legacy( 'SiteStaffr: Updated database version to 1.1.0' );
            }
        }

        // Check if operation_type column exists in ai_metadata table
        $ai_metadata_table = $wpdb->prefix . 'phoneease_ai_metadata';
        $operation_type_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $ai_metadata_table,
                'operation_type'
            )
        );

        // Add operation_type column if it doesn't exist
        if ( empty( $operation_type_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare(
                    "ALTER TABLE %i ADD COLUMN operation_type VARCHAR(20) DEFAULT 'conversation' AFTER model_used",
                    $ai_metadata_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add operation_type column to ai_metadata - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added operation_type column to ai_metadata table' );
                $upgrades_performed = true;

                // Add index for operation_type
                $index_exists = $wpdb->get_results(
                    $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_operation_type'", $ai_metadata_table )
                );

                if ( empty( $index_exists ) ) {
                    $index_result = $wpdb->query(
                        $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_operation_type (operation_type)', $ai_metadata_table )
                    );

                    if ( $index_result === false ) {
                        SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add idx_operation_type index - ' . $wpdb->last_error );
                    } else {
                        SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added idx_operation_type index' );
                    }
                }
            }
        }

        // Check if cached_tokens column exists in ai_metadata table
        $cached_tokens_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $ai_metadata_table,
                'cached_tokens'
            )
        );

        // Add cached_tokens column if it doesn't exist
        if ( empty( $cached_tokens_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare(
                    "ALTER TABLE %i ADD COLUMN cached_tokens INT UNSIGNED DEFAULT 0 COMMENT 'Cache read tokens from prompt caching' AFTER completion_tokens",
                    $ai_metadata_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add cached_tokens column to ai_metadata - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added cached_tokens column to ai_metadata table' );
                $upgrades_performed = true;
            }
        }

        // Add source_chunks column to transcripts table (chunk attribution).
        $transcripts_table = $wpdb->prefix . 'phoneease_transcripts';
        $source_chunks_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $transcripts_table,
                'source_chunks'
            )
        );

        if ( empty( $source_chunks_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN source_chunks LONGTEXT NULL AFTER confidence_score',
                    $transcripts_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add source_chunks column to transcripts - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added source_chunks column to transcripts table' );
                $upgrades_performed = true;
            }
        }

        // Check if last_response_id column exists (for context chaining)
        $last_response_id_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'last_response_id'
            )
        );

        // Add last_response_id column if it doesn't exist
        if ( empty( $last_response_id_exists ) ) {
            // Add last_response_id column after ai_model
            $result = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN last_response_id VARCHAR(255) NULL AFTER ai_model',
                    $calls_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add last_response_id column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added last_response_id column to calls table' );
                $upgrades_performed = true;
            }
        }

        // Check if visitor_ip column exists (WebRTC moderation and call details context)
        $visitor_ip_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'visitor_ip'
            )
        );

        if ( empty( $visitor_ip_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN visitor_ip VARCHAR(45) NULL AFTER to_number',
                    $calls_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add visitor_ip column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added visitor_ip column to calls table' );
                $upgrades_performed = true;
            }
        }

        // Ensure visitor_ip index exists.
        $visitor_ip_index_exists = $wpdb->get_results(
            $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_visitor_ip'", $calls_table )
        );
        if ( empty( $visitor_ip_index_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_visitor_ip (visitor_ip)', $calls_table )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add idx_visitor_ip index - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added idx_visitor_ip index' );
                $upgrades_performed = true;
            }
        }

        // Check if deleted_at column exists (soft-delete for trash feature)
        $deleted_at_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'deleted_at'
            )
        );

        if ( empty( $deleted_at_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN deleted_at DATETIME DEFAULT NULL AFTER ai_model',
                    $calls_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add deleted_at column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added deleted_at column to calls table' );
                $upgrades_performed = true;
            }

            // Add index for deleted_at
            $index_exists = $wpdb->get_results(
                $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_deleted_at'", $calls_table )
            );

            if ( empty( $index_exists ) ) {
                $index_result = $wpdb->query(
                    $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_deleted_at (deleted_at)', $calls_table )
                );

                if ( $index_result === false ) {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add idx_deleted_at index - ' . $wpdb->last_error );
                } else {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added idx_deleted_at index' );
                }
            }
        }

        // Check if purged_at column exists (anonymize-on-delete for usage data preservation)
        $purged_at_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'purged_at'
            )
        );

        if ( empty( $purged_at_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN purged_at DATETIME DEFAULT NULL AFTER deleted_at',
                    $calls_table
                )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add purged_at column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added purged_at column to calls table' );
                $upgrades_performed = true;
            }

            // Add index for purged_at
            $index_exists = $wpdb->get_results(
                $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_purged_at'", $calls_table )
            );

            if ( empty( $index_exists ) ) {
                $index_result = $wpdb->query(
                    $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_purged_at (purged_at)', $calls_table )
                );

                if ( $index_result === false ) {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add idx_purged_at index - ' . $wpdb->last_error );
                } else {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added idx_purged_at index' );
                }
            }
        }

        // Add persona column if it doesn't exist.
        $has_persona = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'persona'
            )
        );

        if ( empty( $has_persona ) ) {
            $result = $wpdb->query(
                $wpdb->prepare(
                    "ALTER TABLE %i ADD COLUMN persona VARCHAR(50) NOT NULL DEFAULT 'default' AFTER service_type",
                    $calls_table
                )
            );

            if ( false === $result ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add persona column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added persona column to calls table' );
                $upgrades_performed = true;

                $index_exists = $wpdb->get_results(
                    $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_persona'", $calls_table )
                );
                if ( empty( $index_exists ) ) {
                    $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD INDEX `idx_persona` (`persona`)', $calls_table ) );
                }
            }
        }

        // Add ended_by column if it doesn't exist (chat-ended indicator, 1.25.0).
        // No index: only read per-row on the detail view and email notification.
        $has_ended_by = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'ended_by'
            )
        );

        if ( empty( $has_ended_by ) ) {
            $result = $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN ended_by VARCHAR(20) NULL AFTER persona',
                    $calls_table
                )
            );

            if ( false === $result ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add ended_by column - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added ended_by column to calls table' );
                $upgrades_performed = true;
            }
        }

        // Run WebRTC support migration (v0.12.22)
        // Adds service_type column and makes phone fields nullable
        $webrtc_migration_performed = self::migrate_webrtc_support();
        if ( $webrtc_migration_performed ) {
            $upgrades_performed = true;
        }

        // Ensure billing cycle snapshots table exists for historical cycle rendering.
        if ( self::ensure_billing_cycle_snapshots_table() ) {
            $upgrades_performed = true;
        }

        // Add phoneease_knowledge table (added in 1.7.0).
        $knowledge_table = $wpdb->prefix . 'phoneease_knowledge';
        $knowledge_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $knowledge_table ) )
        );
        if ( $knowledge_exists !== $knowledge_table ) {
            $charset_collate = 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
            $sql_knowledge = "CREATE TABLE $knowledge_table (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                PRIMARY KEY (id),
                post_id BIGINT UNSIGNED NOT NULL,
                post_title VARCHAR(255) NOT NULL,
                post_type VARCHAR(20) NOT NULL DEFAULT 'page',
                chunk_index INT UNSIGNED NOT NULL DEFAULT 0,
                chunk_text LONGTEXT NOT NULL,
                chunk_title VARCHAR(200) DEFAULT NULL,
                embedding LONGBLOB DEFAULT NULL,
                source VARCHAR(10) NOT NULL DEFAULT 'auto',
                hit_count INT UNSIGNED NOT NULL DEFAULT 0,
                synced_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_post_id (post_id),
                INDEX idx_post_type (post_type),
                INDEX idx_synced_at (synced_at),
                INDEX idx_hit_count (hit_count),
                INDEX idx_source (source),
                FULLTEXT KEY idx_chunk_text_ft (chunk_title, chunk_text)
            ) $charset_collate;";
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            dbDelta( $sql_knowledge );
        }

        // ── Knowledge table: add source + hit_count columns (Knowledge Architecture v2) ──
        $knowledge_table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $knowledge_table ) )
        );

        if ( $knowledge_table_exists ) {
            // Add source column if missing.
            $source_exists = $wpdb->get_results(
                $wpdb->prepare(
                    "SHOW COLUMNS FROM %i WHERE Field = 'source'",
                    $knowledge_table
                )
            );
            if ( empty( $source_exists ) ) {
                $result = $wpdb->query(
                    $wpdb->prepare(
                        "ALTER TABLE %i ADD COLUMN source VARCHAR(10) NOT NULL DEFAULT 'auto' AFTER embedding",
                        $knowledge_table
                    )
                );
                if ( false === $result ) {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add source column to knowledge table - ' . $wpdb->last_error );
                }
                // Add index.
                $wpdb->query(
                    $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_source (source)', $knowledge_table )
                );
            }

            // Add hit_count column if missing.
            $hit_count_exists = $wpdb->get_results(
                $wpdb->prepare(
                    "SHOW COLUMNS FROM %i WHERE Field = 'hit_count'",
                    $knowledge_table
                )
            );
            if ( empty( $hit_count_exists ) ) {
                $result = $wpdb->query(
                    $wpdb->prepare(
                        'ALTER TABLE %i ADD COLUMN hit_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER source',
                        $knowledge_table
                    )
                );
                if ( false === $result ) {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add hit_count column to knowledge table - ' . $wpdb->last_error );
                }
                // Add index.
                $wpdb->query(
                    $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_hit_count (hit_count)', $knowledge_table )
                );
            }

            // Add chunk_title column if missing (AI-generated per-chunk title, 2026-04-18).
            $chunk_title_exists = $wpdb->get_results(
                $wpdb->prepare(
                    "SHOW COLUMNS FROM %i WHERE Field = 'chunk_title'",
                    $knowledge_table
                )
            );
            if ( empty( $chunk_title_exists ) ) {
                $result = $wpdb->query(
                    $wpdb->prepare(
                        'ALTER TABLE %i ADD COLUMN chunk_title VARCHAR(200) DEFAULT NULL AFTER chunk_text',
                        $knowledge_table
                    )
                );
                if ( false === $result ) {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add chunk_title column to knowledge table - ' . $wpdb->last_error );
                }
            }

            // Add FULLTEXT index for hybrid keyword+vector search (2026-04-21).
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $has_ft = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(1) FROM information_schema.STATISTICS
                     WHERE table_schema = DATABASE()
                       AND table_name = %s
                       AND index_name = 'idx_chunk_text_ft'",
                    $knowledge_table
                )
            );
            if ( intval( $has_ft ) === 0 ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query(
                    $wpdb->prepare(
                        'ALTER TABLE %i ADD FULLTEXT KEY idx_chunk_text_ft (chunk_title, chunk_text)',
                        $knowledge_table
                    )
                );
            }
        }

        // Add sitestaffr_blog_posts table (Blog Agent, added in Phase 1).
        $blog_posts_table = $wpdb->prefix . 'sitestaffr_blog_posts';
        $blog_posts_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $blog_posts_table ) )
        );
        if ( $blog_posts_exists !== $blog_posts_table ) {
            $charset_collate = 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
            $sql_blog_posts = "CREATE TABLE $blog_posts_table (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                PRIMARY KEY (id),
                job_id VARCHAR(64) NOT NULL,
                wp_post_id BIGINT UNSIGNED NULL,
                topic TEXT NOT NULL,
                keyword VARCHAR(255) NOT NULL DEFAULT '',
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                request_payload LONGTEXT NULL,
                response_payload LONGTEXT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                completed_at DATETIME NULL,
                INDEX idx_job_id (job_id),
                INDEX idx_status (status),
                INDEX idx_wp_post_id (wp_post_id),
                INDEX idx_created_at (created_at)
            ) $charset_collate;";
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            dbDelta( $sql_blog_posts );
        }

        // ── Qualified leads: add flag + denormalized lead columns to calls table ──
        $lead_columns = array(
            'is_qualified_lead' => 'TINYINT(1) NOT NULL DEFAULT 0',
            'lead_name'         => 'VARCHAR(191) NULL',
            'lead_phone'        => 'VARCHAR(64) NULL',
            'lead_email'        => 'VARCHAR(191) NULL',
            'lead_interest'     => 'TEXT NULL',
        );
        foreach ( $lead_columns as $column => $definition ) {
            $exists = $wpdb->get_results(
                $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $calls_table, $column )
            );
            if ( empty( $exists ) ) {
                // Column definitions are constant strings from the array above (not user input).
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $result = $wpdb->query(
                    $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN ' . $column . ' ' . $definition, $calls_table )
                );
                if ( false === $result ) {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add ' . $column . ' column to calls table - ' . $wpdb->last_error );
                } else {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added ' . $column . ' column to calls table' );
                    $upgrades_performed = true;
                }
            }
        }
        // ── Conversation source page (where on the site the widget was opened) ──
        $source_page_columns = array(
            'source_page_url'   => 'TEXT NULL',
            'source_page_title' => 'VARCHAR(255) NULL',
        );
        foreach ( $source_page_columns as $column => $definition ) {
            $exists = $wpdb->get_results(
                $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $calls_table, $column )
            );
            if ( empty( $exists ) ) {
                // Column definitions are constant strings from the array above (not user input).
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $result = $wpdb->query(
                    $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN ' . $column . ' ' . $definition, $calls_table )
                );
                if ( false === $result ) {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add ' . $column . ' column to calls table - ' . $wpdb->last_error );
                } else {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added ' . $column . ' column to calls table' );
                    $upgrades_performed = true;
                }
            }
        }

        $qlead_index = $wpdb->get_results(
            $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_is_qualified_lead'", $calls_table )
        );
        if ( empty( $qlead_index ) ) {
            $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_is_qualified_lead (is_qualified_lead)', $calls_table )
            );
        }

        // ── Lead handled/follow-up state (public leads sheet + dashboard "Followed up") ──
        // Deliberate "we followed up" flag, distinct from is_read ("viewed"). NULL = not
        // yet handled.
        $handled_col = $wpdb->get_results(
            $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $calls_table, 'handled_at' )
        );
        if ( empty( $handled_col ) ) {
            $result = $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN handled_at DATETIME NULL AFTER reviewed_at', $calls_table )
            );
            if ( false === $result ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add handled_at column to calls table - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added handled_at column to calls table' );
                $upgrades_performed = true;

                // One-time seed: rows already marked read were, under the old model,
                // treated as "followed up" (read == handled). Seed handled_at from
                // them so this upgrade doesn't resurrect already-handled leads as
                // fresh follow-ups on the dashboard + public leads sheet. Runs only
                // on first column add; forward-looking handling stays decoupled from
                // read/viewed.
                // Use created_at only (guaranteed on every schema version — some
                // legacy installs lack reviewed_at, see calls_table_has_reviewed_at_column).
                $wpdb->query(
                    $wpdb->prepare(
                        'UPDATE %i SET handled_at = created_at WHERE handled_at IS NULL AND is_read = 1',
                        $calls_table
                    )
                );
            }
        }
        $handled_index = $wpdb->get_results(
            $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_handled_at'", $calls_table )
        );
        if ( empty( $handled_index ) ) {
            $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_handled_at (handled_at)', $calls_table )
            );
        }

        // Widen lead_interest from the original VARCHAR(255) to TEXT so the full
        // reason is stored without truncation (existing installs created it as VARCHAR).
        $lead_interest_col = $wpdb->get_results(
            $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $calls_table, 'lead_interest' )
        );
        if ( ! empty( $lead_interest_col ) && false !== stripos( (string) $lead_interest_col[0]->Type, 'varchar' ) ) {
            $result = $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i MODIFY lead_interest TEXT NULL', $calls_table )
            );
            if ( false !== $result ) {
                $upgrades_performed = true;
            }
        }

        // ── Salesforce lead-sync state (native Salesforce integration) ──
        // Per-lead push status (pending|synced|queued|failed|skipped; NULL = never
        // attempted) + the created Salesforce Lead Id. Drives the leads-list sync
        // indicator + Retry.
        $salesforce_columns = array(
            'salesforce_sync'    => 'VARCHAR(20) NULL',
            'salesforce_lead_id' => 'VARCHAR(32) NULL',
        );
        foreach ( $salesforce_columns as $column => $definition ) {
            $exists = $wpdb->get_results(
                $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $calls_table, $column )
            );
            if ( empty( $exists ) ) {
                // Column definitions are constant strings from the array above (not user input).
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $result = $wpdb->query(
                    $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN ' . $column . ' ' . $definition, $calls_table )
                );
                if ( false === $result ) {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add ' . $column . ' column to calls table - ' . $wpdb->last_error );
                } else {
                    SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added ' . $column . ' column to calls table' );
                    $upgrades_performed = true;
                }
            }
        }

        return true;
    }

    /**
     * Ensure billing cycle snapshots table exists.
     *
     * @since 0.14.97
     * @return bool True when table exists/created.
     */
    private static function ensure_billing_cycle_snapshots_table() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_billing_cycle_snapshots';
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) )
        );
        if ( $table_exists === $table_name ) {
            // Table already exists, no schema change performed.
            return false;
        }

        $charset_collate = 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
        $sql = "CREATE TABLE $table_name (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            period_start DATETIME NOT NULL,
            period_end DATETIME NOT NULL,
            plan_name VARCHAR(50) NOT NULL,
            subscription_status VARCHAR(30) NOT NULL,
            included_seconds_remaining INT NOT NULL DEFAULT 0,
            addon_seconds_remaining INT NOT NULL DEFAULT 0,
            included_minutes_remaining INT NOT NULL DEFAULT 0,
            addon_minutes_remaining INT NOT NULL DEFAULT 0,
            source VARCHAR(30) NOT NULL DEFAULT 'middleware_sync',
            captured_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_period_window (period_start, period_end),
            INDEX idx_period_start (period_start),
            INDEX idx_captured_at (captured_at)
        ) $charset_collate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );

        $created_table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) )
        );

        if ( $created_table_exists === $table_name ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Ensured billing cycle snapshots table exists' );
            return true;
        }

        SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to ensure billing cycle snapshots table - ' . $wpdb->last_error );
        return false;
    }

    /**
     * Migrate database schema for WebRTC support (v0.12.22).
     *
     * Adds service_type column and makes phone number fields nullable
     * to support both legacy phone calls and WebRTC browser conversations.
     *
     * This migration is safe to run multiple times (idempotent).
     *
     * @since    0.12.22
     * @return   bool    True on success, false on failure.
     */
    public static function migrate_webrtc_support() {
        global $wpdb;

        $calls_table = $wpdb->prefix . 'phoneease_calls';

        // Check if table exists first
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $calls_table ) )
        );

        if ( $table_exists !== $calls_table ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Calls table does not exist, skipping WebRTC migration' );
            return false;
        }

        // Check if migration already completed
        $service_type_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $calls_table,
                'service_type'
            )
        );

        if ( ! empty( $service_type_exists ) ) {
            // Migration already completed, no schema change performed.
            return false;
        }

        SiteStaffr_Logger::legacy( 'SiteStaffr: Running WebRTC support migration' );

        // Add service_type column (default 'phone' for existing records)
        $result1 = $wpdb->query(
            $wpdb->prepare(
                "ALTER TABLE %i ADD COLUMN service_type VARCHAR(20) NOT NULL DEFAULT 'phone' AFTER session_id",
                $calls_table
            )
        );

        if ( $result1 === false ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add service_type column - ' . $wpdb->last_error );
            return false;
        }

        SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added service_type column' );

        // Add index for service_type
        $index_exists = $wpdb->get_results(
            $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'idx_service_type'", $calls_table )
        );

        if ( empty( $index_exists ) ) {
            $result2 = $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i ADD INDEX idx_service_type (service_type)', $calls_table )
            );

            if ( $result2 === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to add idx_service_type index - ' . $wpdb->last_error );
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully added idx_service_type index' );
            }
        }

        // Make from_number nullable
        $result3 = $wpdb->query(
            $wpdb->prepare( 'ALTER TABLE %i MODIFY from_number VARCHAR(20) NULL', $calls_table )
        );

        if ( $result3 === false ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to make from_number nullable - ' . $wpdb->last_error );
            return false;
        }

        SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully made from_number nullable' );

        // Make to_number nullable
        $result4 = $wpdb->query(
            $wpdb->prepare( 'ALTER TABLE %i MODIFY to_number VARCHAR(20) NULL', $calls_table )
        );

        if ( $result4 === false ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Failed to make to_number nullable - ' . $wpdb->last_error );
            return false;
        }

        SiteStaffr_Logger::legacy( 'SiteStaffr: Successfully made to_number nullable' );
        SiteStaffr_Logger::legacy( 'SiteStaffr: WebRTC support migration completed successfully' );

        return true;
    }

    /**
     * Get call transcripts for a specific call.
     *
     * @since    1.0.0
     * @param    int    $call_id    Call ID.
     * @return   array              Array of transcript objects.
     */
    public static function get_call_transcripts( $call_id ) {
        global $wpdb;

        $transcripts_table = $wpdb->prefix . 'phoneease_transcripts';

        return $wpdb->get_results(
            $wpdb->prepare(
                'SELECT * FROM %i WHERE call_id = %d ORDER BY id ASC',
                $transcripts_table,
                $call_id
            )
        );
    }

    /**
     * Update AI summary for a specific call.
     *
     * Stores the AI-generated summary and follow-up text in the database
     * for reuse across features (Dashboard, emails, etc.).
     *
     * @since    1.0.0
     * @param    int       $call_id       Call ID.
     * @param    string    $ai_summary    AI-generated summary text.
     * @param    string    $ai_followup   AI-generated follow-up text.
     * @return   int|false                Number of rows updated, or false on error.
     */
    public static function update_call_ai_summary( $call_id, $ai_summary = '', $ai_followup = '' ) {
        global $wpdb;

        $calls_table = $wpdb->prefix . 'phoneease_calls';

        return $wpdb->update(
            $calls_table,
            array(
                'ai_summary'  => $ai_summary,
                'ai_followup' => $ai_followup,
            ),
            array( 'id' => $call_id ),
            array( '%s', '%s' ),
            array( '%d' )
        );
    }

    /**
     * Clean up old call records based on data retention policy.
     *
     * This method should be called by a WordPress cron job.
     *
     * @since    1.0.0
     */
    public static function cleanup_old_calls() {
        global $wpdb;

        $settings = self::get_settings();
        $retention_days = isset( $settings['data_retention_days'] ) ? intval( $settings['data_retention_days'] ) : 90;

        if ( $retention_days <= 0 ) {
            return; // Retention disabled
        }

        $calls_table = $wpdb->prefix . 'phoneease_calls';
        $cutoff_date = current_datetime()->modify( '-' . $retention_days . ' days' )->format( 'Y-m-d H:i:s' );

        // Delete old calls (cascades to transcripts and ai_metadata)
        $wpdb->query(
            $wpdb->prepare(
                'DELETE FROM %i WHERE created_at < %s',
                $calls_table,
                $cutoff_date
            )
        );
    }

    /**
     * Run minute-based billing migration.
     *
     * Checks current database version and runs migration if needed.
     * Safe to run multiple times (idempotent).
     *
     * @since    1.0.0
     * @return   bool    True on success, false on failure.
     */
    public static function run_minute_based_migration() {
        $current_db_version = get_option( 'sitestaffr_db_version', '0.0.0' );

        // Only run migration if database version is less than 1.0.0
        if ( version_compare( $current_db_version, '1.0.0', '>=' ) ) {
            return true;
        }

        // Load migration class
        require_once SITESTAFFR_PLUGIN_DIR . 'includes/migrations/migration-minute-based-v1.php';

        // Run migration
        $result = SiteStaffr_Migration_Minute_Based_V1::up();

        if ( ! $result ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Minute-based migration failed' );
        }

        return $result;
    }

    /**
     * Rollback minute-based billing migration (for testing).
     *
     * CAUTION: This will delete billing and metrics data.
     * Only use for testing or development.
     *
     * @since    1.0.0
     * @return   bool    True on success, false on failure.
     */
    public static function rollback_minute_based_migration() {
        SiteStaffr_Logger::legacy( 'SiteStaffr: Rolling back minute-based billing migration' );

        // Load migration class
        require_once SITESTAFFR_PLUGIN_DIR . 'includes/migrations/migration-minute-based-v1.php';

        // Run rollback
        $result = SiteStaffr_Migration_Minute_Based_V1::down();

        if ( $result ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Minute-based migration rollback completed successfully' );
        } else {
            SiteStaffr_Logger::legacy( 'SiteStaffr: Minute-based migration rollback failed' );
        }

        return $result;
    }
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
