<?php
namespace AIZLabs\ChatAgent;

class DB {
    private $table_article;
    private $table_agent;
    private $table_conversation;
    private $table_function;
    private $table_global;

    public function __construct() {
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Used for debugging plugin activation
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
        error_log("Chat Agent: DB class instantiated.");
    }

    public function set_table_names() {
        $this->table_article = Config::get_table_name('article');
        $this->table_agent = Config::get_table_name('agent');
        $this->table_conversation = Config::get_table_name('conversation');
        $this->table_function = Config::get_table_name('function');
        $this->table_global = Config::get_table_name('global');
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Used for debugging plugin activation
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
        error_log("Chat Agent: DB tables names set.");
    }

    public function network_activate($network_wide) {
        if (is_multisite() && $network_wide) {
            $aizl_sites = get_sites();
            foreach ($aizl_sites as $aizl_site) {
                switch_to_blog($aizl_site->blog_id);

                $this->set_table_names();
                $this->create_tables();
                restore_current_blog();
            }
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("Chat Agent: Activated on multisite network");
        } else {
            $this->set_table_names();
            $this->create_tables();
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("Chat Agent: Activated on single site");
        }
    }

    public function create_tables() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        // Check if the article table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is from class property
        if ($wpdb->get_var("SHOW TABLES LIKE '{$this->table_article}'") != $this->table_article) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- Creating custom plugin table on activation
            $sql = "CREATE TABLE {$this->table_article} (
                id int UNSIGNED NOT NULL AUTO_INCREMENT,
                file_type varchar(255) NOT NULL,
                file_name varchar(255) NOT NULL,
                file_size int UNSIGNED NOT NULL,
                file_id varchar(255) NOT NULL,
                created_time datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
                PRIMARY KEY (id)
            ) $charset_collate;";

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

            // Log the result for debugging
            if (empty($wpdb->last_error)) {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Table {$this->table_article} was created successfully.");
            } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Chat Agent: Error creating table {$this->table_article}: " . $wpdb->last_error);
            }
        } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("Chat Agent: Table {$this->table_article} already exists.");
        }

        // Check if the agent table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is from class property
        if ($wpdb->get_var("SHOW TABLES LIKE '{$this->table_agent}'") != $this->table_agent) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- Creating custom plugin table on activation
            $sql = "CREATE TABLE {$this->table_agent} (
                id int UNSIGNED NOT NULL AUTO_INCREMENT,
                name varchar(255) NOT NULL,
                instructions text NOT NULL,
                greeting_message text NOT NULL,
                model varchar(255) NOT NULL,
                tools text NOT NULL,
                vector_store_id varchar(255) NOT NULL,
                article_ids text NOT NULL,
                function_ids text NOT NULL,
                scope text NOT NULL,
                created_time datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
                updated_time datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
                PRIMARY KEY (id)
            ) $charset_collate;";

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

            // Log the result for debugging
            if (empty($wpdb->last_error)) {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Table {$this->table_agent} was created successfully.");
            } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Chat Agent: Error creating table {$this->table_agent}: " . $wpdb->last_error);
            }
        } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("Chat Agent: Table {$this->table_agent} already exists.");
        }

        // Check if the conversation table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is from class property
        if ($wpdb->get_var("SHOW TABLES LIKE '{$this->table_conversation}'") != $this->table_conversation) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- Creating custom plugin table on activation
            $sql = "CREATE TABLE {$this->table_conversation} (
                id int UNSIGNED NOT NULL AUTO_INCREMENT,
                agent_id int UNSIGNED NOT NULL,
                session_id varchar(255) NOT NULL,
                user_id int UNSIGNED NULL,
                response_id varchar(255) NULL,
                content text NOT NULL,
                content_time datetime NOT NULL,
                response text NOT NULL,
                source varchar(255) NULL,
                score float NOT NULL,
                response_time datetime NOT NULL,
                PRIMARY KEY (id)
            ) $charset_collate;";

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

            // Log the result for debugging
            if (empty($wpdb->last_error)) {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Table {$this->table_conversation} was created successfully.");
            } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Chat Agent: Error creating table {$this->table_conversation}: " . $wpdb->last_error);
            }
        } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("Chat Agent: Table {$this->table_conversation} already exists.");
        }

        // Check if the functions table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is from class property
        if ($wpdb->get_var("SHOW TABLES LIKE '{$this->table_function}'") != $this->table_function) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- Creating custom plugin table on activation
            $sql = "CREATE TABLE {$this->table_function} (
                id int UNSIGNED NOT NULL AUTO_INCREMENT,
                name varchar(255) NOT NULL,
                description varchar(255) NOT NULL,
                definition text NOT NULL,
                dependency_table varchar(255) NULL,
                dependency_plugin varchar(255) NULL,
                created_time datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
                PRIMARY KEY (id)
            ) $charset_collate;";

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

            // Log the result for debugging
            if (empty($wpdb->last_error)) {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Table {$this->table_function} was created successfully.");
                
                // Insert default track order status function
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->insert(
                    $this->table_function,
                    array(
                        'id' => 1,
                        'name' => 'aizl_track_order_status',
                        'description' => 'Track Order Status',
                        'definition' => '{"type":"function","name":"aizl_track_order","description":"Track Order Status by WooCommerce","parameters":{"type":"object","properties":{"order_id":{"type":"string","description":"Order Number"}},"required":["order_id"],"additionalProperties":false}}',
                        'dependency_table' => 'wc_order_stats',
                        'dependency_plugin' => 'WooCommerce',
                        'created_time' => gmdate('Y-m-d H:i:s')
                    )
                );
                
                if (empty($wpdb->last_error)) {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                    error_log("Chat Agent: Default track order status function inserted successfully.");
                } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                    error_log("Chat Agent: Error inserting default function: " . $wpdb->last_error);
                }

                // Insert default track ticket function
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->insert(
                    $this->table_function,
                    array(
                        'id' => 2,
                        'name' => 'aizl_track_ticket',
                        'description' => 'Track Ticket Status',
                        'definition' => '{"type":"function","name":"aizl_track_ticket","description":"Track Ticket Status by SupportCandy","parameters":{"type":"object","properties":{"ticket_number":{"type":"string","description":"Support ticket number"},"email":{"type":"string","description":"Customer email address"}},"required":["ticket_number","email"],"additionalProperties":false}}',
                        'dependency_table' => 'psmsc_tickets',
                        'dependency_plugin' => 'SupportCandy',
                        'created_time' => gmdate('Y-m-d H:i:s')
                    )
                );
                
                if (empty($wpdb->last_error)) {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                    error_log("Chat Agent: Default track ticket function inserted successfully.");
                } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                    error_log("Chat Agent: Error inserting default track ticket function: " . $wpdb->last_error);
                }

                // Insert default get bestsellers function
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->insert(
                    $this->table_function,
                    array(
                        'id' => 3,
                        'name' => 'aizl_get_bestsellers',
                        'description' => 'Best-Selling Products',
                        'definition' => '{"type":"function","name":"aizl_get_bestsellers","description":"Get the top best-selling products from the store by WooCommerce","parameters":{"type":"object","properties":{"format":{"type":"string","description":"Output format (always use default)","enum":["default"]}},"required":[],"additionalProperties":false}}',
                        'dependency_table' => 'wc_order_product_lookup',
                        'dependency_plugin' => 'WooCommerce',
                        'created_time' => gmdate('Y-m-d H:i:s')
                    )
                );
                
                if (empty($wpdb->last_error)) {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                    error_log("Chat Agent: Default get bestsellers function inserted successfully.");
                } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                    error_log("Chat Agent: Error inserting default get bestsellers function: " . $wpdb->last_error);
                }

                
            } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Chat Agent: Error creating table {$this->table_function}: " . $wpdb->last_error);
            }
        } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("Chat Agent: Table {$this->table_function} already exists.");
        }


        // Check if the global table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is from class property
        if ($wpdb->get_var("SHOW TABLES LIKE '{$this->table_global}'") != $this->table_global) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- Creating custom plugin table on activation
            $sql = "CREATE TABLE {$this->table_global} (
                id int UNSIGNED NOT NULL AUTO_INCREMENT,
                access_key varchar(255) NOT NULL,
                agents_header varchar(255) NOT NULL DEFAULT 'Chat Agent',
                theme_name varchar(255) NOT NULL,
                theme_color varchar(255) NOT NULL,
                PRIMARY KEY (id)
            ) $charset_collate;";

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

            // Log the result for debugging
            if (empty($wpdb->last_error)) {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Chat Agent: Table {$this->table_global} was created successfully.");
            } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log("Chat Agent: Error creating table {$this->table_global}: " . $wpdb->last_error);
            }
        } else {
// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("Chat Agent: Table {$this->table_global} already exists.");
        }


    }
}