<?php
if (!defined('ABSPATH')) die('Restricted Access');

/**
 * Checks if the database needs to be updated and triggers the update script.
 * This runs safely after normal plugin updates where activation hooks are not fired.
 */
function aiflt_update_db_check() {
    $current_db_version = get_option('aiflt_db_version', '1.0');
    $health_version = get_option('aiflt_db_health_version', '');

    if (version_compare($current_db_version, AIFLT_DB_VERSION, '<')) {
        aiflt_run_database_updates($current_db_version);
        update_option('aiflt_db_version', AIFLT_DB_VERSION);
        update_option('aiflt_db_health_version', AIFLT_DB_VERSION);
        return;
    }

    /**
     * Safety repair for sites that already reached the current DB version before
     * all 1.0.1 tables were present. This runs once per DB version and makes the
     * upgrade path safe for 1.0.0 -> 1.0.1 and for partial/manual updates.
     */
    if ($health_version !== AIFLT_DB_VERSION) {
        aiflt_run_database_updates($current_db_version);
        update_option('aiflt_db_health_version', AIFLT_DB_VERSION);
    }
}

/**
 * Check whether a plugin-owned table exists.
 */
function aiflt_db_table_exists($table) {
    global $wpdb;
    $table = esc_sql($table);

    if ($table === '') {
        return false;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Lightweight schema health check for plugin-owned tables.
    return (bool) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
}

/**
 * Check whether a plugin-owned table has a column.
 */
function aiflt_db_column_exists($table, $column) {
    global $wpdb;
    $table  = esc_sql($table);
    $column = sanitize_key($column);

    if ($table === '' || $column === '' || !aiflt_db_table_exists($table)) {
        return false;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name is escaped and lookup value is prepared.
    return (bool) $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM {$table} LIKE %s", $column));
}

/**
 * Add a column only when it is missing. Used as a safety net around dbDelta().
 */
function aiflt_db_maybe_add_column($table, $column, $definition) {
    global $wpdb;
    $table  = esc_sql($table);
    $column = sanitize_key($column);

    if ($table === '' || $column === '' || aiflt_db_column_exists($table, $column)) {
        return;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Controlled plugin-owned schema upgrade.
    $wpdb->query("ALTER TABLE {$table} ADD COLUMN {$column} {$definition}");
}

/**
 * Check whether an index exists on a plugin-owned table.
 */
function aiflt_db_index_exists($table, $index) {
    global $wpdb;
    $table = esc_sql($table);
    $index = sanitize_key($index);

    if ($table === '' || $index === '' || !aiflt_db_table_exists($table)) {
        return false;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name is escaped and lookup value is prepared.
    return (bool) $wpdb->get_var($wpdb->prepare("SHOW INDEX FROM {$table} WHERE Key_name = %s", $index));
}

/**
 * Add an index only when it is missing. Used as a safety net around dbDelta().
 */
function aiflt_db_maybe_add_index($table, $index, $columns) {
    global $wpdb;
    $table = esc_sql($table);
    $index = sanitize_key($index);

    if ($table === '' || $index === '' || $columns === '' || !aiflt_db_table_exists($table) || aiflt_db_index_exists($table, $index)) {
        return;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Controlled plugin-owned schema upgrade.
    $wpdb->query("ALTER TABLE {$table} ADD INDEX {$index} ({$columns})");
}

/**
 * Database updates are idempotent. dbDelta creates/updates tables, and the explicit
 * column/index guards protect 1.0.0 installs from dbDelta edge cases.
 *
 * @param string $from_version The DB version we are updating from.
 */
function aiflt_run_database_updates($from_version) {
    global $wpdb;
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    $charset_collate = $wpdb->get_charset_collate();

    $funnel_table = $wpdb->prefix . 'aiflt_funnel_data';
    $sql_funnel_data = "CREATE TABLE {$funnel_table} (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        session_id VARCHAR(255) NOT NULL,
        user_id BIGINT(20) DEFAULT NULL,
        guest_email VARCHAR(255) DEFAULT NULL,
        guest_phone VARCHAR(32) DEFAULT NULL,
        page_name VARCHAR(50) NOT NULL,
        device_type VARCHAR(20) NOT NULL,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
        cart_data LONGTEXT NULL,
        page_speed FLOAT DEFAULT NULL,
        post_id BIGINT(20) DEFAULT NULL,
        order_id BIGINT(20) DEFAULT NULL,
        campaign_id VARCHAR(100) DEFAULT NULL,
        ip_address VARCHAR(45) NULL,
        PRIMARY KEY  (id),
        KEY session_id (session_id),
        KEY page_name (page_name),
        KEY timestamp (timestamp),
        KEY post_id (post_id),
        KEY order_id (order_id),
        KEY guest_email (guest_email(100))
    ) {$charset_collate};";
    dbDelta($sql_funnel_data);

    // Explicit 1.0.0 -> 1.0.1 safety net.
    aiflt_db_maybe_add_column($funnel_table, 'guest_email', 'VARCHAR(255) DEFAULT NULL AFTER user_id');
    aiflt_db_maybe_add_column($funnel_table, 'campaign_id', 'VARCHAR(100) DEFAULT NULL AFTER order_id');
    aiflt_db_maybe_add_column($funnel_table, 'ip_address', 'VARCHAR(45) NULL');
    // 1.0.7: phone typed at checkout, for a WhatsApp message with the cart link.
    aiflt_db_maybe_add_column($funnel_table, 'guest_phone', 'VARCHAR(32) DEFAULT NULL AFTER guest_email');
    // 1.0.10: where the visit came from ("ai:chatgpt", "search"...), for AI traffic reports.
    aiflt_db_maybe_add_column($funnel_table, 'source', 'VARCHAR(30) DEFAULT NULL AFTER device_type');
    aiflt_db_maybe_add_index($funnel_table, 'source', 'source');
    aiflt_db_maybe_add_index($funnel_table, 'session_id', 'session_id');
    aiflt_db_maybe_add_index($funnel_table, 'page_name', 'page_name');
    aiflt_db_maybe_add_index($funnel_table, 'timestamp', 'timestamp');
    aiflt_db_maybe_add_index($funnel_table, 'post_id', 'post_id');
    aiflt_db_maybe_add_index($funnel_table, 'order_id', 'order_id');
    aiflt_db_maybe_add_index($funnel_table, 'guest_email', 'guest_email(100)');

    $behavior_table = $wpdb->prefix . 'aiflt_behavior_data';
    $sql_behavior_data = "CREATE TABLE {$behavior_table} (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        session_id VARCHAR(64) NOT NULL,
        user_id BIGINT(20) UNSIGNED NULL,
        product_id BIGINT(20) UNSIGNED DEFAULT 0,
        page_url TEXT NULL,
        behavior_type VARCHAR(50) NOT NULL,
        behavior_value TEXT NULL,
        traffic_source VARCHAR(50) DEFAULT 'Unknown',
        user_agent VARCHAR(255) NULL,
        ip_address VARCHAR(45) NULL,
        location_data TEXT NULL,
        timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY product_id (product_id),
        KEY behavior_type (behavior_type),
        KEY session_id (session_id),
        KEY timestamp (timestamp)
    ) {$charset_collate};";
    dbDelta($sql_behavior_data);

    // Explicit 1.0.0 -> 1.0.1 safety net.
    aiflt_db_maybe_add_column($behavior_table, 'page_url', 'TEXT NULL AFTER product_id');
    aiflt_db_maybe_add_column($behavior_table, 'location_data', 'TEXT NULL AFTER ip_address');
    aiflt_db_maybe_add_column($behavior_table, 'ip_address', 'VARCHAR(45) NULL');
    aiflt_db_maybe_add_index($behavior_table, 'product_id', 'product_id');
    aiflt_db_maybe_add_index($behavior_table, 'behavior_type', 'behavior_type');
    aiflt_db_maybe_add_index($behavior_table, 'session_id', 'session_id');
    aiflt_db_maybe_add_index($behavior_table, 'timestamp', 'timestamp');

    if (!class_exists('AIFLT_Cart_Recovery')) {
        require_once AIFLT_PLUGIN_PATH . 'includes/AIFLT_Cart_Recovery.php';
    }
    AIFLT_Cart_Recovery::maybe_create_tables(true);

    // 1.0.9: the checkout reassurance line moved from Pro's switches to a free fix.
    $aiflt_pro_checkout = get_option('aiftco_settings');
    if (is_array($aiflt_pro_checkout) && !empty($aiflt_pro_checkout['add_trust_message']) && get_option('aiflt_checkout_reassurance') === false) {
        update_option('aiflt_checkout_reassurance', 'yes');
    }

    if (!class_exists('AIFLT_AI_Usage_Log')) {
        require_once AIFLT_PLUGIN_PATH . 'includes/AIFLT_AI_Usage_Log.php';
    }
    if (class_exists('AIFLT_AI_Usage_Log')) {
        AIFLT_AI_Usage_Log::maybe_create_table(true);
    }

    if (!class_exists('AIFLT_AI_Report_Log')) {
        require_once AIFLT_PLUGIN_PATH . 'includes/AIFLT_AI_Report_Log.php';
    }
    if (class_exists('AIFLT_AI_Report_Log')) {
        AIFLT_AI_Report_Log::maybe_create_table(true);
    }

    if (!class_exists('AIFLT_World_Class_Tools')) {
        require_once AIFLT_PLUGIN_PATH . 'includes/AIFLT_World_Class_Tools.php';
    }
    if (class_exists('AIFLT_World_Class_Tools')) {
        AIFLT_World_Class_Tools::maybe_create_tables(true);
    }

    // 1.0.4: licensing and add-on updates moved into AI Flash Tune Pro. The free
    // plugin no longer contacts the licence or update servers, so its old
    // schedules and cached manifest are removed.
    wp_clear_scheduled_hook('aiflt_weekly_license_check_event');
    wp_clear_scheduled_hook('aiflt_twicedaily_addon_update_check_event');
    delete_option('aiflt_addon_manifest_last_good');
    delete_option('aiflt_addon_manifest_checked_at');
    delete_transient('aiflt_addon_manifest_cache');
    delete_transient('aiflt_license_status_cache');
    delete_transient('aiflt_license_data_cache');

    // 1.0.4: the monthly AI cap is an optional spending limit on the site's own
    // key, not a paid unlock. Sites still on the old default of 20 go to no cap.
    if ((string) get_option('aiflt_free_ai_monthly_limit', '') === '20' && version_compare((string) $from_version, '1.0.4', '<')) {
        update_option('aiflt_free_ai_monthly_limit', 0);
    }

    // Add only missing options so existing user settings are never overwritten.
    add_option('aiflt_ai_provider', 'local');
    add_option('aiflt_ai_external_consent', '0');
    add_option('aiflt_ai_aggregate_only', '1');
    add_option('aiflt_free_ai_monthly_limit', 0);
    add_option('aiflt_require_consent', '0');
    add_option('aiflt_ai_response_language', 'auto');
    add_option('aiflt_zywrap_endpoint', 'https://api.zywrap.com/v1/proxy');
    add_option('aiflt_delete_data_on_uninstall', '0');
    add_option('aiflt_anonymize_ip', '1');
    add_option('aiflt_enable_tracking', '1');
    add_option('aiflt_excluded_roles', ['administrator', 'shop_manager']);
    add_option('aiflt_excluded_ips', '');
}