<?php
// Exit if accessed directly.
if (!defined('ABSPATH')) {
    exit; // Prevent direct access to the file.
}

// Check if the class already exists to avoid redeclaration.
if (!class_exists('integrazo_fwcrm_form_Account_Table')) {

    class integrazo_fwcrm_form_Account_Table
    {
        private $table_name;

        public function __construct()
        {
            global $wpdb;
            $this->table_name = $wpdb->get_blog_prefix() . 'integrazo_fwcrm_form_account_details';
        }
        // Function to create the table
        public function create_table()
        {
            global $wpdb;

            $table_name = esc_sql($this->table_name); // Ensure the table name is sanitized
            $charset_collate = $wpdb->get_charset_collate();

            // Use the correct SQL for creating the table
            $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
                account_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                account_name VARCHAR(255) NOT NULL,
                instance_url TEXT NOT NULL, -- Updated to allow longer URLs
                api_key TEXT NOT NULL, -- Updated to allow longer API keys
                added_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                modified_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (account_id)
            ) $charset_collate;";

            // Load the WordPress upgrade functions
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            dbDelta($sql); // Create or upgrade the table structure
        }
        // Function to drop the table
        public function drop_table()
        {
            global $wpdb;

            // Ensure table exists before attempting to drop
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange
            $result = $wpdb->query($wpdb->prepare("DROP TABLE IF EXISTS %s", esc_sql($this->table_name)));


            if ($result === false) {
                return new WP_Error('drop_table_error', __('Failed to drop table.', 'crm-integration-freshworks-any-form'), $wpdb->last_error);
            }

            return true;
        }
        // Function to insert data into the table
        public function insert_account($account_name, $instance_url, $api_key)
        {
            global $wpdb;

            // Validate and sanitize the input
            $account_name = sanitize_text_field($account_name);
            $instance_url = esc_url_raw($instance_url); // Since this is a URL
            $api_key = sanitize_textarea_field($api_key); // For potentially long keys

            // Insert data into the table
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $inserted = $wpdb->insert(
                $this->table_name,
                [
                    'account_name' => $account_name,
                    'instance_url' => $instance_url,
                    'api_key'      => $api_key,
                ],
                ['%s', '%s', '%s'] // Data type format
            );

            // Handle insertion errors
            if ($inserted === false) {
                return new WP_Error('db_insert_error', __('Failed to insert account.', 'crm-integration-freshworks-any-form'), $wpdb->last_error);
            }

            return $wpdb->insert_id; // Return the inserted row ID
        }

        // Function to update an account's data by account ID
        public function update_account($account_id, $account_name, $instance_url, $api_key)
        {
            global $wpdb;

            // Validate and sanitize inputs
            $account_id = intval($account_id); // Ensure it's an integer
            $account_name = sanitize_text_field($account_name); // Sanitize text input
            $instance_url = esc_url_raw($instance_url); // Sanitize URL
            $api_key = sanitize_textarea_field($api_key); // Handle potentially long keys

            // Perform the database update
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $updated = $wpdb->update(
                $this->table_name,
                [
                    'account_name' => $account_name,
                    'instance_url' => $instance_url,
                    'api_key'      => $api_key,
                ],
                ['account_id' => $account_id], // Where clause
                ['%s', '%s', '%s'], // Data formats for the values being updated
                ['%d'] // Data format for the where clause
            );

            // Handle update errors
            if ($updated === false) {
                return new WP_Error('db_update_error', __('Failed to update account.', 'crm-integration-freshworks-any-form'), $wpdb->last_error);
            }
            // If no rows were affected, return the account_id
            if ($updated === 0) {
                return $account_id;
            }
            return $updated; // Return the number of rows updated
        }
        // Function to get the API key by account ID
        public function get_api_key($account_id)
        {
            global $wpdb;

            // Validate and sanitize the account ID
            $account_id = intval($account_id);

            // Sanitize the table name
            $table_name = esc_sql($this->table_name);

            // Fetch the API key from the database
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $api_key = $wpdb->get_var(
                $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    "SELECT api_key FROM {$table_name} WHERE account_id = %d",
                    $account_id
                )
            );

            // Return the API key or an error if not found
            if (empty($api_key)) {
                return new WP_Error('key_not_found', __('API key not found for the given account ID.', 'crm-integration-freshworks-any-form'));
            }

            return $api_key;
        }


        // Function to delete an account by account ID
        public function delete_account($account_id)
        {
            global $wpdb;
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $deleted = $wpdb->delete($this->table_name, ['account_id' => intval($account_id)], ['%d']);

            if ($deleted === false) {
                return new WP_Error('db_delete_error', __('Failed to delete account.', 'crm-integration-freshworks-any-form'), $wpdb->last_error);
            }

            return $deleted;
        }

        // Function to delete all accounts (e.g., on plugin deactivation)
        public function delete_all_accounts()
        {
            global $wpdb;
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $deleted = $wpdb->query("DELETE FROM {$this->table_name}");

            if ($deleted === false) {
                return new WP_Error('db_delete_all_error', __('Failed to delete all accounts.', 'crm-integration-freshworks-any-form'), $wpdb->last_error);
            }

            return $deleted;
        }

        public function get_account_data()
        {
            global $wpdb, $blog_id;


            // Fetch all account data from the database
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $data = $wpdb->get_results("SELECT * FROM " . esc_sql($this->table_name), ARRAY_A);

            if (is_wp_error($data) || empty($data)) {
                // Handle errors or return an empty array if no data is found
                return [];
            }

            return $data;
        }

        // Function to get account data by account_id
        public function get_single_account_data($account_id)
        {
            global $wpdb;

            // Sanitize the account_id parameter
            $account_id = intval($account_id);

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $data = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$this->table_name} WHERE account_id = %d", $account_id), ARRAY_A);

            return $data;
        }

        // Function to get the account name by account ID
        public function get_account_name($account_id)
        {
            global $wpdb;
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            return $wpdb->get_var($wpdb->prepare("SELECT account_name FROM {$this->table_name} WHERE account_id = %d", intval($account_id)));
        }

        // Function to get the total number of accounts
        public function get_total_accounts()
        {
            global $wpdb;
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            return $wpdb->get_var("SELECT COUNT(*) FROM {$this->table_name}");
        }

        // Function to check if an account exists by account ID
        public function account_exists($account_id)
        {
            global $wpdb;
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            return $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$this->table_name} WHERE account_id = %d", intval($account_id))) > 0;
        }

        // Function to get the latest account added (e.g., by added_time)
        public function get_latest_account()
        {
            global $wpdb;
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            return $wpdb->get_row("SELECT * FROM {$this->table_name} ORDER BY added_time DESC LIMIT 1", ARRAY_A);
        }
    }

    $integrazo_fwcrm_form_accountDBInstance = new integrazo_fwcrm_form_Account_Table();
}
