<?php

namespace AIPT\Core\Database;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Central class managing all database migration operations.
 *
 * @since 2.1.3
 */
class Migration {
    /**
     * Singleton instance
     *
     * @var Migration
     */
    private static $instance = null;

    private static $tables_checked = false;

    private $history_table;
    private $automation_jobs_table;
    private $job_logs_table;
    private $job_products_table;
    private $draft_content_table;
    private $chatbot_messages_table;
    private $store_index_profiles_table;
    private $seo_meta_history_table;
    private $prompt_templates_table;

    /**
     * Returns singleton instance.
     *
     * @return Migration
     */
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Constructor
     */
    private function __construct() {
        global $wpdb;
        $this->history_table = $wpdb->prefix . 'aipt_bulk_generator_history';
        $this->automation_jobs_table = $wpdb->prefix . 'aipt_automation_jobs';
        $this->job_logs_table = $wpdb->prefix . 'aipt_job_logs';
        $this->job_products_table = $wpdb->prefix . 'aipt_job_products';
        $this->draft_content_table = $wpdb->prefix . 'aipt_draft_content';
        $this->chatbot_messages_table = $wpdb->prefix . 'aipt_chatbot_messages';
        $this->store_index_profiles_table = $wpdb->prefix . 'aipt_store_index_profiles';
        $this->seo_meta_history_table = $wpdb->prefix . 'aipt_seo_meta_history';
        $this->prompt_templates_table = $wpdb->prefix . 'aipt_prompt_templates';

        if (is_admin()) {
            add_action('init', [$this, 'check_tables'], 1);
        }

        $this->maybe_bootstrap_schema();

	        add_action('upgrader_process_complete', [ $this, 'on_upgrader_process_complete' ], 10, 2);

    }

    /**
     * Ensures DB schema is present and up-to-date with minimal overhead.
     * - Runs very early on every request
     * - Uses a short transient lock to avoid race conditions
     * - Throttles full checks with a transient to reduce overhead
     */
    private function maybe_bootstrap_schema(): void {
        if (self::$tables_checked) {
            return;
        }

        $lock_key = 'aipt_schema_migration_lock';

        $target_version = defined('AIPT_VERSION') ? AIPT_VERSION : '0';
        $current_schema = get_option('aipt_schema_version', '0');

        if ($current_schema === $target_version) {

            $this->verify_required_tables();
            self::$tables_checked = true;
            return;
        }

        if (get_site_transient($lock_key)) {

            return;
        }

        set_site_transient($lock_key, 1, 60);

        try {

            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            $this->create_plugin_tables();
            update_option('aipt_schema_version', $target_version, false);
        } catch (\Exception $e) {

        } finally {
            delete_site_transient($lock_key);
        }

        self::$tables_checked = true;

    }

    /**
     * Runs all migration operations.
     */
    private function run_migrations() {
        $current_version = get_option('aipt_version', '0');

        if (version_compare($current_version, AIPT_VERSION, '<')) {

            update_option('aipt_previous_version', $current_version, false);
            update_option('aipt_version', AIPT_VERSION, false);
        }
    }

    /**
     * Ensures schema immediately after plugin update/install.
     *
     * This only triggers once during update/install; no overhead on normal requests.
     *
     * @param mixed $upgrader WP_Upgrader instance.
     * @param array $hook_extra Extra hook arguments.
     * @return void
     */
    public function on_upgrader_process_complete($upgrader, $hook_extra) {

        if (empty($hook_extra['type']) || $hook_extra['type'] !== 'plugin') {
            return;
        }
        if (empty($hook_extra['action']) || !in_array($hook_extra['action'], ['update', 'install'], true)) {
            return;
        }
        if (empty($hook_extra['plugins']) || !is_array($hook_extra['plugins'])) {
            return;
        }

        $plugin_file = trailingslashit(AIPT_PLUGIN_DIR) . 'ai-product-tools.php';
        $this_plugin = function_exists('plugin_basename') ? plugin_basename($plugin_file) : 'ai-product-tools/ai-product-tools.php';
        if (!in_array($this_plugin, $hook_extra['plugins'], true)) {
            return;
        }

        $this->check_tables();
    }

    public function check_tables() {
        if (self::$tables_checked) {
            return;
        }

        try {

            $target_version = defined('AIPT_VERSION') ? AIPT_VERSION : '0';
            $current_schema = get_option('aipt_schema_version', '0');
            if ($current_schema !== $target_version) {
                require_once ABSPATH . 'wp-admin/includes/upgrade.php';
                $this->create_plugin_tables();
                update_option('aipt_schema_version', $target_version, false);
            }

            $this->run_migrations();
        } catch (\Exception $e) {
        }

        self::$tables_checked = true;
    }

    /**
     * Create required tables during plugin activation
     * This method is called from Activator.php
     *
     * Uses dbDelta() for idempotent table creation and schema updates.
     */
    public function create_activation_tables() {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$this->history_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            product_id bigint(20) NOT NULL,
            description_type varchar(10) NOT NULL,
            generated_text longtext NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            error_message text DEFAULT NULL,
            updated_at datetime NULL DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY product_id (product_id),
            KEY status (status),
            KEY idx_product_type (product_id,description_type),
            KEY idx_status_created (status,created_at)
        ) {$charset_collate};";

        dbDelta( $sql );

        update_option('aipt_db_version', AIPT_VERSION, false);

        $this->create_plugin_tables();
    }

    /**
     * Create all plugin database tables.
     *
     * Creates tables for:
     * - Automation jobs (jobs, logs, products, draft content)
     * - Chatbot messages queue
     *
     * Uses dbDelta() for idempotent table creation and schema updates.
     * dbDelta() handles both creation and updates, so no need to check if tables exist.
     *
     * Note: dbDelta() requires specific formatting:
     * - Two spaces before PRIMARY KEY definition
     * - Each field on its own line
     * - Use KEY instead of INDEX
     * - ON UPDATE CURRENT_TIMESTAMP is not supported; timestamps handled in PHP
     */
    private function create_plugin_tables() {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset_collate = $wpdb->get_charset_collate();

        $sql_jobs = "CREATE TABLE {$this->automation_jobs_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            description text DEFAULT NULL,
            tool_type varchar(50) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            progress int(3) NOT NULL DEFAULT 0,
            total_products int(11) NOT NULL DEFAULT 0,
            processed_products int(11) NOT NULL DEFAULT 0,
            failed_products int(11) NOT NULL DEFAULT 0,
            settings longtext NOT NULL,
            schedule_config longtext NOT NULL,
            product_filter longtext DEFAULT NULL,
            next_run datetime DEFAULT NULL,
            started_at datetime DEFAULT NULL,
            completed_at datetime DEFAULT NULL,
            error_message text DEFAULT NULL,
            created_by bigint(20) NOT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT NULL,
            last_progress_logged_at datetime DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY status (status),
            KEY tool_type (tool_type),
            KEY next_run (next_run),
            KEY created_by (created_by),
            KEY idx_status_created (status,created_at)
        ) {$charset_collate};";

        $sql_logs = "CREATE TABLE {$this->job_logs_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            job_id bigint(20) NOT NULL,
            level varchar(20) NOT NULL,
            message text NOT NULL,
            context longtext DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY job_id (job_id),
            KEY level (level),
            KEY created_at (created_at),
            KEY idx_job_logs_filtering (job_id,level,created_at)
        ) {$charset_collate};";

        $sql_products = "CREATE TABLE {$this->job_products_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            job_id bigint(20) NOT NULL,
            product_id bigint(20) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            result longtext DEFAULT NULL,
            error_message text DEFAULT NULL,
            processed_at datetime DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            processing_token varchar(64) DEFAULT NULL,
            processing_started_at datetime DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY job_id (job_id),
            KEY product_id (product_id),
            KEY status (status),
            KEY processing_token (processing_token),
            KEY idx_job_status_id (job_id,status,id),
            KEY idx_job_claim_processing (job_id,status,processing_token,id),
            UNIQUE KEY job_product (job_id,product_id)
        ) {$charset_collate};";

        $sql_draft = "CREATE TABLE {$this->draft_content_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            job_id bigint(20) NOT NULL,
            product_id bigint(20) NOT NULL,
            content_type varchar(50) NOT NULL,
            original_content longtext DEFAULT NULL,
            generated_content longtext NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            created_by bigint(20) NOT NULL,
            approved_by bigint(20) DEFAULT NULL,
            approved_at datetime DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY job_id (job_id),
            KEY product_id (product_id),
            KEY content_type (content_type),
            KEY status (status),
            KEY created_by (created_by),
            UNIQUE KEY job_product_content (job_id,product_id,content_type),
            KEY idx_status_created (status,created_at)
        ) {$charset_collate};";

        $sql_chatbot = "CREATE TABLE {$this->chatbot_messages_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            session_id varchar(36) NOT NULL,
            user_name varchar(100) DEFAULT NULL,
            user_email varchar(500) DEFAULT NULL,
            message text NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'queued',
            admin_response text DEFAULT NULL,
            ai_response text DEFAULT NULL,
            retry_count tinyint(3) unsigned NOT NULL DEFAULT 0,
            error_message varchar(255) DEFAULT NULL,
            consent_given tinyint(1) DEFAULT NULL,
            consent_timestamp bigint(20) unsigned DEFAULT NULL,
            record_type varchar(20) NOT NULL DEFAULT 'error',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY idx_session_id (session_id),
            KEY idx_session_status (session_id,status),
            KEY idx_status_created (status,created_at),
            KEY idx_queue_retrieval (status,retry_count,created_at),
            KEY idx_record_type (record_type),
            KEY idx_session_record_type (session_id,record_type)
        ) {$charset_collate};";

        $sql_store_index_profiles = "CREATE TABLE {$this->store_index_profiles_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(50) NOT NULL,
            is_default tinyint(1) NOT NULL DEFAULT 0,
            is_active tinyint(1) NOT NULL DEFAULT 0,
            settings longtext NOT NULL,
            status longtext NOT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY name (name),
            KEY is_default (is_default),
            KEY is_active (is_active),
            KEY idx_active_default (is_active,is_default)
        ) {$charset_collate};";

        $sql_prompt_templates = "CREATE TABLE {$this->prompt_templates_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            description text DEFAULT NULL,
            generator_type varchar(50) NOT NULL,
            prompt_data longtext NOT NULL,
            wizard_state longtext DEFAULT NULL,
            is_default tinyint(1) NOT NULL DEFAULT 0,
            source varchar(20) NOT NULL DEFAULT 'local',
            community_id varchar(36) DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY generator_type (generator_type),
            KEY source (source),
            KEY idx_generator_default (generator_type,is_default)
        ) {$charset_collate};";

        dbDelta( $sql_jobs );
        dbDelta( $sql_logs );
        dbDelta( $sql_products );
        dbDelta( $sql_draft );
        dbDelta( $sql_chatbot );
        dbDelta( $sql_store_index_profiles );
        dbDelta( $sql_prompt_templates );
        $this->create_seo_meta_history_table();

        $this->ensure_default_store_index_profile();
    }

    /**
     * Verify that all required tables exist and create any missing ones.
     *
     * This handles edge cases where schema version was set but some tables
     * weren't created (e.g., new table added in same version after initial release).
     *
     * @since 2.6.0
     * @return void
     */
    private function verify_required_tables(): void {

        $throttle_key = 'aipt_verify_tables_throttle';
        if (get_transient($throttle_key)) {
            return; 
        }

        global $wpdb;

        $required_tables = [
            $this->history_table,
            $this->automation_jobs_table,
            $this->job_logs_table,
            $this->job_products_table,
            $this->draft_content_table,
            $this->chatbot_messages_table,
            $this->store_index_profiles_table,
            $this->seo_meta_history_table,
            $this->prompt_templates_table,
        ];

        $escaped_prefix = $wpdb->esc_like($wpdb->prefix . 'aipt_') . '%';

        $existing_tables = $wpdb->get_col($wpdb->prepare("SHOW TABLES LIKE %s", $escaped_prefix));

        $missing_tables = array_diff($required_tables, $existing_tables);

        if (empty($missing_tables)) {

            set_transient($throttle_key, true, 5 * MINUTE_IN_SECONDS);
            return;
        }

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $core_tables = [
            $this->history_table,
            $this->automation_jobs_table,
            $this->job_logs_table,
            $this->job_products_table,
            $this->draft_content_table,
            $this->chatbot_messages_table,
            $this->store_index_profiles_table,
            $this->prompt_templates_table,
        ];
        $core_tables_missing = array_intersect($missing_tables, $core_tables);

        if (!empty($core_tables_missing)) {

            $this->create_plugin_tables();
        } elseif (in_array($this->seo_meta_history_table, $missing_tables, true)) {

            $this->create_seo_meta_history_table();
        }

        set_transient($throttle_key, true, 5 * MINUTE_IN_SECONDS);
    }

    /**
     * Create SEO Meta History table.
     *
     * Stores history of SEO metadata generation and approvals.
     *
     * @since 2.6.0
     */
    private function create_seo_meta_history_table(): void {
        if ( ! function_exists( 'dbDelta' ) ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }

        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$this->seo_meta_history_table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id bigint(20) UNSIGNED NOT NULL,
            meta_type varchar(50) NOT NULL,
            previous_value text,
            generated_value text NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            variant_index tinyint(3) UNSIGNED DEFAULT 0,
            plugin_slug varchar(50) DEFAULT '',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY product_id (product_id),
            KEY status (status),
            KEY created_at (created_at),
            KEY product_status (product_id, status),
            KEY idx_product_meta_status (product_id, meta_type, status),
            KEY idx_product_status_created (product_id, status, created_at)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Ensure default Store Index profile exists.
     *
     * Creates a default profile with default settings if none exists.
     * Handles concurrent writers gracefully by catching duplicate key errors.
     *
     * @since 2.5.0
     */
    private function ensure_default_store_index_profile(): void {
        global $wpdb;

        $sql = "SELECT id FROM {$this->store_index_profiles_table} WHERE is_default = %d";

        $default_exists = $wpdb->get_var($wpdb->prepare($sql, 1));

        if ( $default_exists ) {
            return; 
        }

        $settings = array(
            'enabled'            => false,
            'max_products'       => 10000,
            'batch_size'         => 100,
            'categories'         => array(),
            'tags'               => array(),
            'stock_filter'       => 'all',
            'date_filter_days'   => 0,
            'filter_mode'        => 'all',
            'scheduled_enabled'  => false,
            'schedule_frequency' => 'daily',
            'included_fields'    => \AIPT\Api\StoreIndex\ProfileManager::getDefaultIncludedFields(),
        );

        $status = array(
            'status'                   => 'idle',
            'last_build'               => null,
            'product_count'            => 0,
            'category_count'           => 0,
            'file_size'                => 0,
            'trimmed_count'            => 0,
            'error'                    => null,
            'build_progress'           => 0,
            'progress_percent'         => 0,
            'processed_product_count'  => 0,
            'estimated_total_products' => 0,
            'build_job_id'             => 0,
        );

        $suppress = $wpdb->suppress_errors( true );

        $result = $wpdb->insert(
            $this->store_index_profiles_table,
            array(
                'name'       => 'Default',
                'is_default' => 1,
                'is_active'  => 1,
                'settings'   => wp_json_encode( $settings ),
                'status'     => wp_json_encode( $status ),
                'created_at' => current_time( 'mysql' ),
            ),
            array( '%s', '%d', '%d', '%s', '%s', '%s' )
        );

        $wpdb->suppress_errors( $suppress );

        if ( false === $result ) {
            $last_error = $wpdb->last_error;

            if ( strpos( $last_error, 'Duplicate entry' ) !== false || strpos( $last_error, '1062' ) !== false ) {
                return; 
            }

            error_log(
                sprintf(
                    '[AIPT] Migration: Failed to create default Store Index profile: %s',
                    $last_error ?: 'Unknown database error'
                )
            );
        }
    }

    /**
     * Drop all plugin tables.
     *
     * Should only be called during plugin uninstallation.
     * Validates the calling context to ensure this is a legitimate uninstall:
     *
     * - WP-CLI context: Trusted privileged environment
     * - WP_UNINSTALL_PLUGIN: Standard WordPress uninstall mechanism
     * - Admin with manage_options: Web-based uninstall with proper capability
     *
     * @throws \RuntimeException If called from untrusted context.
     * @return void
     */
    public function drop_tables(): void {

        $is_cli = defined('WP_CLI') && WP_CLI;
        $is_wp_uninstall = defined('WP_UNINSTALL_PLUGIN');
        $has_capability = current_user_can('manage_options');

        if (!$is_cli && !$is_wp_uninstall && !$has_capability) {

            throw new \RuntimeException('Insufficient permissions to drop tables.');
        }

        global $wpdb;

        try {

            $wpdb->query("DROP TABLE IF EXISTS {$this->history_table}");
            $wpdb->query("DROP TABLE IF EXISTS {$this->draft_content_table}");
            $wpdb->query("DROP TABLE IF EXISTS {$this->job_products_table}");
            $wpdb->query("DROP TABLE IF EXISTS {$this->job_logs_table}");
            $wpdb->query("DROP TABLE IF EXISTS {$this->automation_jobs_table}");
            $wpdb->query("DROP TABLE IF EXISTS {$this->chatbot_messages_table}");
            $wpdb->query("DROP TABLE IF EXISTS {$this->store_index_profiles_table}");
            $wpdb->query("DROP TABLE IF EXISTS {$this->seo_meta_history_table}");
            $wpdb->query("DROP TABLE IF EXISTS {$this->prompt_templates_table}");

            delete_option('aipt_db_version');
            delete_option('aipt_chatbot_settings');
            delete_option('aipt_chatbot_appearance_presets');
        } catch (\Exception $e) {
            throw $e;
        }
    }
}
