<?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,
        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)
    ) {$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');
    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');

    $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_License_Manager')) {
        require_once AIFLT_PLUGIN_PATH . 'includes/AIFLT_License_Manager.php';
    }
    if (class_exists('AIFLT_License_Manager')) {
        AIFLT_License_Manager::maybe_create_activation_table();
    }

    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();
    }

    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();
    }

    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();
    }

    // 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', 20);
    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', '');
}
