<?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('IAFWZDESK_Account_Table')) {

    class IAFWZDESK_Account_Table
    {
        private $table_name;

        public function __construct()
        {
            global $wpdb;
            $this->table_name = $wpdb->get_blog_prefix() . 'iafwzdesk_account_details';
        }

        // Function to create the table
        public function create_table()
        {
            global $wpdb;

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE {$this->table_name} (
                account_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                account_name VARCHAR(255) NOT NULL,
                service_name VARCHAR(255) NOT NULL DEFAULT 'zoho_desk',
                data_center VARCHAR(255) NOT NULL,
                refresh_token VARCHAR(255) NOT NULL,
                access_token VARCHAR(255) NOT NULL, 
                org_id VARCHAR(255) NOT NULL,
                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;";

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

        // Function to insert data into the table
        public function insert_account($account_name, $data_center, $refresh_token, $access_token, $org_id)
        {
            global $wpdb;

            // Sanitize inputs
            $account_name   = sanitize_text_field($account_name);
            $data_center    = sanitize_text_field($data_center);
            $refresh_token  = sanitize_text_field($refresh_token);
            $access_token   = sanitize_text_field($access_token);
            $org_id         = sanitize_text_field($org_id);

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $inserted = $wpdb->insert(
                $this->table_name,
                [
                    'account_name'  => $account_name,
                    'data_center'   => $data_center,
                    'refresh_token' => $refresh_token,
                    'access_token'  => $access_token,
                    'org_id'        => $org_id,
                ],
                ['%s', '%s', '%s', '%s', '%s']
            );

            if ($inserted === false) {
                return new WP_Error(
                    'db_insert_error',
                    __('Failed to insert account.', 'integrate-with-zoho-desk'),
                    $wpdb->last_error
                );
            }

            return $wpdb->insert_id;
        }
        // Function to update an account's data by account ID
        public function update_account($account_id, $account_name, $data_center, $refresh_token, $access_token, $org_id)
        {
            global $wpdb;

            // Sanitize inputs
            $account_id     = intval($account_id);
            $account_name   = sanitize_text_field($account_name);
            $data_center    = sanitize_text_field($data_center);
            $refresh_token  = sanitize_text_field($refresh_token);
            $access_token   = sanitize_text_field($access_token);
            $org_id         = sanitize_text_field($org_id);

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $updated = $wpdb->update(
                $this->table_name,
                [
                    'account_name'  => $account_name,
                    'data_center'   => $data_center,
                    'refresh_token' => $refresh_token,
                    'access_token'  => $access_token,
                    'org_id'        => $org_id,
                ],
                ['account_id' => $account_id],
                ['%s', '%s', '%s', '%s', '%s'],
                ['%d']
            );

            // Check for update failure
            if ($updated === false) {
                return new WP_Error(
                    'db_update_error',
                    __('Failed to update account.', 'integrate-with-zoho-desk'),
                    $wpdb->last_error
                );
            }

            return $updated;
        }
        // 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.', 'integrate-with-zoho-desk'), $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 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}");
        }
    }
    $iafwzdesk_accountDBInstance = new IAFWZDESK_Account_Table();
}
