<?php
if (!class_exists('AI_App_Onsite_DB_Handler')) {
    // Define a class for handling database operations
    class AI_App_Onsite_DB_Handler
    {

        protected $wpdb;

        protected $plugin_settings;
        protected $app_properties;
        protected $model_settings;
        protected $license_settings;
        protected $field_selector_settings;
        protected $prompt_editor;
        protected $user_statistics;
        protected $app_usage_statistics;
        protected $app_open_ai_models;
		protected string $response_links;
		protected string $webhooks;
		protected string $rag_reference_sources;
		protected string $rag_token_settings;

        public function __construct($wpdb)
        {

            $this->wpdb                    = $wpdb;
            $this->plugin_settings         = $wpdb->prefix . 'ai_app_onsite_plugin_settings';
            $this->app_properties          = $wpdb->prefix . 'ai_app_onsite_app_properties';
            $this->model_settings          = $wpdb->prefix . 'ai_app_onsite_model_settings';
            $this->license_settings        = $wpdb->prefix . 'ai_app_onsite_license_settings';
            $this->field_selector_settings = $wpdb->prefix . 'ai_app_onsite_field_selector_settings';
            $this->prompt_editor           = $wpdb->prefix . 'ai_app_onsite_prompt_editor';
            $this->user_statistics         = $wpdb->prefix . 'ai_app_onsite_user_statistics';
            $this->app_usage_statistics    = $wpdb->prefix . 'ai_app_onsite_app_usage_statistics';
            $this->app_open_ai_models      = $wpdb->prefix . 'ai_app_onsite_models';
			$this->response_links          = $wpdb->prefix . 'ai_app_onsite_response_links';
			$this->webhooks                = $wpdb->prefix . 'ai_app_onsite_webhooks';
			$this->rag_reference_sources   = $wpdb->prefix . 'ai_app_onsite_rag_reference_sources';
			$this->rag_token_settings      = $wpdb->prefix . 'ai_app_onsite_rag_token_settings';
        }


        public function ai_app_onsite_create_table(): bool
        {

            // Define table names with the proper prefix
			$plugin_settings         = $this->plugin_settings;
			$app_properties          = $this->app_properties;
			$model_settings          = $this->model_settings;
			$license_settings        = $this->license_settings;
			$field_selector_settings = $this->field_selector_settings;
			$prompt_editor           = $this->prompt_editor;
			$user_statistics         = $this->user_statistics;
			$usage_statistics        = $this->app_usage_statistics;
	        $app_open_ai_models      = $this->app_open_ai_models;
			$response_links          = $this->response_links;
			$webhooks                = $this->webhooks;

            // Table name with WordPress prefix
			$plugin_settings_table         = $plugin_settings;
			$app_properties_table          = $app_properties;
			$model_settings_table          = $model_settings;
			$license_settings_table        = $license_settings;
			$field_selector_settings_table = $field_selector_settings;
            $prompt_editor_table           = $prompt_editor;
            $user_statistics_table         = $user_statistics;
			$app_usage_statistics_table    = $usage_statistics;
	        $app_open_ai_models_table      = $app_open_ai_models;
			$response_links_table          = $response_links;
			$webhooks_table                = $webhooks;

            // SQL queries to create the tables (without prepare for static queries)
            $sql_settings = "CREATE TABLE IF NOT EXISTS $plugin_settings_table (
                   id INT(11) NOT NULL AUTO_INCREMENT,
                   app_id INT(30) NOT NULL,
                   agree_terms BOOLEAN,
                   app_selector TEXT,
                   app_email VARCHAR(300),
                   banned_words TEXT,
                   banned_words_csv TEXT,
                   created_at DATETIME NOT NULL,
                   updated_at DATETIME NOT NULL,
                   PRIMARY KEY (id)
               )";

            $sql_app_properties = "CREATE TABLE IF NOT EXISTS $app_properties_table (
                   id INT(11) NOT NULL AUTO_INCREMENT,
                   app_id INT(30) NOT NULL,
                   app_name TEXT,
                   app_name_show TEXT,
                   app_logo TEXT,
                   app_disclaimer VARCHAR(300),
                   app_description VARCHAR(1000),
                   response_message TEXT,
                   email_only_enabled TINYINT(1) DEFAULT 0,
                   output_preview_word_count INT DEFAULT 100,
                   email_field_notice TEXT,
                   submit_button_txt TEXT,
                   title_color TEXT,
                   font_color TEXT,
                   bg_color TEXT,
                   app_corner TEXT,
                   app_height TEXT,
                   short_code_genrated VARCHAR(30) DEFAULT 'NO',
                   created_at DATETIME NOT NULL,
                   updated_at DATETIME NOT NULL,
                   PRIMARY KEY (id)
               )";

            $sql_model_settings = "CREATE TABLE IF NOT EXISTS $model_settings_table (
                   id INT(11) NOT NULL AUTO_INCREMENT,
                   app_id INT(30) NOT NULL,
                   openai_key VARCHAR(255),
                   key_status VARCHAR(11),
                   model VARCHAR(255),
                   temperature FLOAT DEFAULT '1.0',
                   f_penalty FLOAT DEFAULT '1.0',
                   p_penalty FLOAT DEFAULT '1.0',
                   max_tokens INT DEFAULT '1',
                   top_p FLOAT DEFAULT '1.0',
                   created_at DATETIME NOT NULL,
                   updated_at DATETIME NOT NULL,
                   PRIMARY KEY (id)
               )";

            $sql_license_settings = "CREATE TABLE IF NOT EXISTS $license_settings_table (
                id INT(11) NOT NULL AUTO_INCREMENT,
                app_id INT(30) NOT NULL,
                license_key VARCHAR(255),
                key_status VARCHAR(11),
                subscription_expiry_date VARCHAR(255),
                key_disable_status ENUM('true', 'false') DEFAULT 'false',
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                PRIMARY KEY (id)
            )";

            $sql_field_selector_settings = "CREATE TABLE IF NOT EXISTS $field_selector_settings_table (
                   id INT(30) NOT NULL AUTO_INCREMENT,
                   app_id INT(30) NOT NULL,
                   field_uniqueid VARCHAR(30) NOT NULL,
                   field_id VARCHAR(300) NOT NULL,
                   app_width VARCHAR(30) NOT NULL,
                   field_type VARCHAR(30) NOT NULL,
                   field_label VARCHAR(300) NOT NULL,
                   field_maxchar VARCHAR(20) NOT NULL,
                   field_dropdown_options TEXT, 
                   field_sample_text VARCHAR(300) NOT NULL,
                   field_require_field_status TINYINT(1) NOT NULL,
                   field_include_in_export_email TINYINT(1) NOT NULL,
                   field_position INT(30) NOT NULL,
                   field_tag VARCHAR(1000) NOT NULL,
                   created_at DATETIME NOT NULL,
                   updated_at DATETIME NOT NULL,
                   PRIMARY KEY (id)
               )";

            $sql_prompt_editor = "CREATE TABLE IF NOT EXISTS $prompt_editor_table (
                   id INT(30) NOT NULL AUTO_INCREMENT,
                   app_id INT(30) NOT NULL,
                   prompt LONGTEXT NOT NULL,
                   prompt_html LONGTEXT NOT NULL,
                   ai_response_style VARCHAR(30),
                   ai_response_tone VARCHAR(30),
                   eg_ai_output VARCHAR(1000),
                   created_at DATETIME NOT NULL,
                   updated_at DATETIME NOT NULL,
                   PRIMARY KEY (id)
               )";

            $sql_user_statistics = "CREATE TABLE IF NOT EXISTS $user_statistics_table (
                   id INT(30) NOT NULL AUTO_INCREMENT,
                   app_id INT(30) NOT NULL,
                   user_ip VARCHAR(255) NOT NULL,
                   prompt TEXT,
                   user_geolocation TEXT,
                   response_id VARCHAR(255) NOT NULL,
                   data TEXT,
                   used_token VARCHAR(255) NOT NULL,
                   feedback VARCHAR(255) NOT NULL,
                   created_at DATETIME NOT NULL,
                   updated_at DATETIME NOT NULL,
                   PRIMARY KEY (id)
               )";

            $sql_app_usage_statistics = "CREATE TABLE IF NOT EXISTS $app_usage_statistics_table (
                id INT(11) NOT NULL AUTO_INCREMENT,
                app_id INT(30) NOT NULL,
                response_id VARCHAR(255) NULL,
                user_id VARCHAR(50) NOT NULL,
                time_on_app VARCHAR(20),
                app_use CHAR(1) DEFAULT 'N',
                results_visited CHAR(1) DEFAULT 'N',
                time_on_results VARCHAR(20),
                app_reuse CHAR(1) DEFAULT 'N',
                results_liked CHAR(1) DEFAULT 'N',
                results_disliked CHAR(1) DEFAULT 'N',
                webhook_triggered LONGTEXT NULL,
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                PRIMARY KEY (id)
            )";

            $sql_open_ai_models = "CREATE TABLE IF NOT EXISTS $app_open_ai_models_table (
                id INT(11) NOT NULL AUTO_INCREMENT,
                model VARCHAR(255) NOT NULL,
                category VARCHAR(100) NOT NULL,
                description TEXT NOT NULL,
                supported_parameters LONGTEXT NOT NULL,
                endpoint VARCHAR(255) NOT NULL,
                provider VARCHAR(50) NOT NULL DEFAULT 'openai',
                default_tools LONGTEXT DEFAULT NULL,
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                PRIMARY KEY (id)
            )";

			$sql_response_links = "CREATE TABLE IF NOT EXISTS $response_links_table (
				   id INT(30) NOT NULL AUTO_INCREMENT,
				   app_id INT(30) NOT NULL,
				   anchor_text VARCHAR(255) NOT NULL,
                   link_url TEXT NOT NULL,
				   created_at DATETIME NOT NULL,
				   updated_at DATETIME NOT NULL,
				   PRIMARY KEY (id)
			   )";

			$sql_webhooks = "CREATE TABLE IF NOT EXISTS $webhooks_table (
			    id INT(30) NOT NULL AUTO_INCREMENT,
			    app_id INT(30) NOT NULL,
			    webhook_url TEXT NOT NULL,
			    webhook_event ENUM('response_created', 'response_updated') NOT NULL DEFAULT 'response_created',
			    method ENUM('GET', 'POST') NOT NULL DEFAULT 'POST',
			    payload_format ENUM('json', 'form') NOT NULL DEFAULT 'json',
			    webhook_status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
			    created_at DATETIME NOT NULL,
			    updated_at DATETIME NOT NULL,
			    PRIMARY KEY (id)
			)";

            // Array of SQL queries for all tables
            $sql_queries = array(
                $sql_settings,
                $sql_app_properties,
                $sql_model_settings,
                $sql_license_settings,
                $sql_field_selector_settings,
                $sql_prompt_editor,
                $sql_user_statistics,
                $sql_app_usage_statistics,
                $sql_open_ai_models,
	            $sql_response_links,
	            $sql_webhooks
            );

            // Execute the SQL queries
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            $success = true;
            foreach ($sql_queries as $temp) {
                dbDelta($temp);
            }

            $add_or_update_column = function ( $table, $column, $definition, $after = '', $update = false ) {
                $exists = $this->wpdb->get_row(
                    $this->wpdb->prepare( "SHOW COLUMNS FROM `$table` LIKE %s", $column ),
                    ARRAY_A
                );

                if ( ! $exists ) {
                    $after_sql = $after ? " AFTER `$after`" : '';
                    $this->wpdb->query( "ALTER TABLE `$table` ADD COLUMN `$column` $definition$after_sql" );
                }
                else if($update){
                    // --- Compare only type & length ---
                    $current_type = strtoupper( trim( $exists['Type'] ) );

                    // Extract just the data type name from definition (e.g., VARCHAR, TEXT, INT)
                    $definition_parts = preg_split( '/\s+/', trim( $definition ) );
                    $desired_type = strtoupper( $definition_parts[0] );

                    // Extract numeric length (if any)
                    $current_length = '';
                    if ( preg_match( '/\((.*?)\)/', $exists['Type'], $match ) ) {
                        $current_length = $match[1];
                    }

                    $desired_length = '';
                    if ( preg_match( '/\((.*?)\)/', $definition, $match ) ) {
                        $desired_length = $match[1];
                    }

                    $type_diff   = ( strpos( $current_type, $desired_type ) === false );
                    $length_diff = ( $desired_length !== '' && $desired_length !== $current_length );

                    // Only alter if type or length differs
                    if ( $type_diff || $length_diff ) {
                        $this->wpdb->query( "ALTER TABLE `$table` MODIFY `$column` $definition" );
                    }
                }
            };

            $add_or_update_column( $app_properties_table, 'app_corner', 'TEXT', 'bg_color' );
            $add_or_update_column( $app_properties_table, 'app_height', 'TEXT', 'app_corner' );
            $add_or_update_column( $app_properties_table, 'response_header', 'TEXT', 'app_disclaimer' );
            $add_or_update_column( $app_properties_table, 'btn_color', 'TEXT', 'app_height' );
            $add_or_update_column( $app_properties_table, 'btn_font_color', 'TEXT', 'btn_color' );
            $add_or_update_column( $app_properties_table, 'response_bg_color', 'TEXT', 'btn_font_color' );
            $add_or_update_column( $app_properties_table, 'response_message', 'TEXT', 'app_description' );
            $add_or_update_column( $app_properties_table, 'email_only_enabled', 'TINYINT(1) DEFAULT 0', 'response_message' );
            $add_or_update_column( $app_properties_table, 'output_preview_word_count', 'INT DEFAULT 100', 'email_only_enabled' );
            $add_or_update_column( $app_properties_table, 'email_field_notice', 'TEXT', 'output_preview_word_count', true );
            $add_or_update_column( $app_properties_table, 'short_code_genrated', "VARCHAR(300)", '', true);
            $add_or_update_column( $app_properties_table, 'app_description', 'VARCHAR(5000)', '', true );
            $add_or_update_column( $app_properties_table, 'app_disclaimer', 'VARCHAR(500)', '', true );
            $add_or_update_column( $field_selector_settings_table, 'field_sample_text', 'VARCHAR(500)', '', true );
            $add_or_update_column( $user_statistics_table, 'ai_output', 'LONGTEXT', 'prompt' );

            $add_or_update_column( $user_statistics_table, 'webhook_status', "ENUM('queued','sent','failed') DEFAULT NULL", 'feedback' );
            $add_or_update_column( $user_statistics_table, 'webhook_scheduled_at', 'DATETIME NULL', 'webhook_status' );
            $add_or_update_column( $user_statistics_table, 'webhook_attempts', 'INT DEFAULT 0', 'webhook_scheduled_at' );
            $add_or_update_column( $user_statistics_table, 'webhook_last_error', 'TEXT NULL', 'webhook_attempts' );

            // Sync OpenAI models from JSON seeder
            $this->sync_open_ai_models_from_json();

            return $success;
        }

        public function ai_app_onsite_drop_table(): void {
            // Define the table names with the proper prefix
			$plugin_settings_table         = $this->plugin_settings;
			$app_properties_table          = $this->app_properties;
			$model_settings_table          = $this->model_settings;
			$license_settings_table        = $this->license_settings;
			$field_selector_settings_table = $this->field_selector_settings;
			$prompt_editor_table           = $this->prompt_editor;
			$user_statistics_table         = $this->user_statistics;
			$app_usage_statistics_table    = $this->app_usage_statistics;
			$app_open_ai_models_table      = $this->app_open_ai_models;
			$response_links_table          = $this->response_links;
			$webhooks_table                = $this->webhooks;
			$rag_reference_sources_table   = $this->rag_reference_sources;
			$rag_token_settings_table      = $this->rag_token_settings;

            // Drop the tables to delete all data securely
            $this->wpdb->query("DROP TABLE IF EXISTS $plugin_settings_table");
            $this->wpdb->query("DROP TABLE IF EXISTS $app_properties_table");
            $this->wpdb->query("DROP TABLE IF EXISTS $model_settings_table");
            $this->wpdb->query("DROP TABLE IF EXISTS $license_settings_table");
            $this->wpdb->query("DROP TABLE IF EXISTS $field_selector_settings_table");
            $this->wpdb->query("DROP TABLE IF EXISTS $prompt_editor_table");
            $this->wpdb->query("DROP TABLE IF EXISTS $user_statistics_table");
            $this->wpdb->query("DROP TABLE IF EXISTS $app_usage_statistics_table");
            $this->wpdb->query("DROP TABLE IF EXISTS $app_open_ai_models_table");
			$this->wpdb->query("DROP TABLE IF EXISTS $response_links_table");
			$this->wpdb->query("DROP TABLE IF EXISTS $webhooks_table");
			$this->wpdb->query("DROP TABLE IF EXISTS $rag_reference_sources_table");
			$this->wpdb->query("DROP TABLE IF EXISTS $rag_token_settings_table");
        }

        /**
         * Sync OpenAI models from JSON seeder into DB.
         *
         * - Inserts missing models
         * - Updates changed models
         * - Uses JSON file as source of truth
         */
        public function sync_open_ai_models_from_json() {
            global $wpdb;

            $table_name = $wpdb->prefix . 'ai_app_onsite_models';
            $json_file =plugin_dir_path( dirname( __FILE__ ) ) . 'assets/seeds/open_ai_models.json';

            // Bail if JSON file is missing
            if ( ! file_exists( $json_file ) ) {
                error_log( '[AI_App_Onsite_DB_Handler] Seeder JSON file not found: ' . $json_file );
                return;
            }

            // Decode JSON data
            $models_data = json_decode( file_get_contents( $json_file ), true );
            if ( ! is_array( $models_data ) ) {
                error_log( '[AI_App_Onsite_DB_Handler] Invalid JSON format in: ' . $json_file );
                return;
            }

            // Get all existing models from DB in one query for speed
            $existing_rows = $wpdb->get_results( "SELECT * FROM {$table_name}", ARRAY_A );
            $existing_map  = array();
            foreach ( $existing_rows as $row ) {
                $existing_map[ $row['model'] ] = $row;
            }

            $now = current_time( 'mysql' );

            foreach ( $models_data as $model ) {
                // Encode supported_parameters for DB storage
                $encoded_params = wp_json_encode( $model['supported_parameters'] );

                if ( ! isset( $existing_map[ $model['model'] ] ) ) {
                    // INSERT new model
                    $wpdb->insert(
                        $table_name,
                        array(
                            'model'                => sanitize_text_field( $model['model'] ),
                            'category'             => sanitize_text_field( $model['category'] ),
                            'supported_parameters' => $encoded_params,
                            'endpoint'             => sanitize_text_field( $model['endpoint'] ),
                            'provider'             => sanitize_text_field( $model['provider'] ?? 'openai' ),
                            'default_tools'        => isset( $model['default_tools'] ) ? sanitize_text_field( $model['default_tools'] ) : null,
                            'description'          => sanitize_text_field( $model['description'] ?? '' ),
                            'created_at'           => $now,
                            'updated_at'           => $now,
                        ),
                        array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
                    );
                } else {
                    // UPDATE if any field changed
                    $existing = $existing_map[ $model['model'] ];
                    $needs_update = (
                        $existing['category'] !== $model['category'] ||
                        $existing['supported_parameters'] !== $encoded_params ||
                        $existing['endpoint'] !== $model['endpoint'] ||
                        $existing['provider'] !== ( $model['provider'] ?? 'openai' ) ||
                        $existing['default_tools'] !== ( $model['default_tools'] ?? null ) ||
                        $existing['description'] !== ( $model['description'] ?? '' )
                    );

                    if ( $needs_update ) {
                        $wpdb->update(
                            $table_name,
                            array(
                                'category'             => sanitize_text_field( $model['category'] ),
                                'supported_parameters' => $encoded_params,
                                'endpoint'             => sanitize_text_field( $model['endpoint'] ),
                                'provider'             => sanitize_text_field( $model['provider'] ?? 'openai' ),
                                'default_tools'        => isset( $model['default_tools'] ) ? sanitize_text_field( $model['default_tools'] ) : null,
                                'description'          => sanitize_text_field( $model['description'] ?? '' ),
                                'updated_at'           => $now,
                            ),
                            array( 'id' => (int) $existing['id'] ),
                            array( '%s', '%s', '%s', '%s', '%s', '%s', '%s' ),
                            array( '%d' )
                        );
                    }
                }
            }
        }

    }
}
