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

    class IAFWZDESK_Error_Log
    {
        private $wpdb;
        private $table_name;

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

        // Function to create the error log table with the specified columns allowing NULL values
        public function create_table()
        {
            global $wpdb;

            $charset_collate = $wpdb->get_charset_collate();
            $field_mapping_table = $wpdb->get_blog_prefix() . "iafwzdesk_field_mapping";

            $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                integration_id BIGINT UNSIGNED NOT NULL, -- Foreign key referencing field_mapping table
                error_type VARCHAR(100) NOT NULL, -- Type of error with increased length
                form_data JSON NOT NULL, -- Form data at the time of error
                crm_data JSON NOT NULL, -- CRM data at the time of error
                api_response JSON NULL, -- API response, allows NULL values
                crm_record_id VARCHAR(255) NULL, -- CRM record ID, allows NULL values
                retry_count INT DEFAULT 0 NOT NULL, -- Number of retry attempts
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Created time
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Auto-updating timestamp for last update
                FOREIGN KEY (integration_id) REFERENCES $field_mapping_table(integration_id) ON DELETE CASCADE
            ) $charset_collate;";

            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);
        }
        // Add a new error log entry
        public function add_error_log($integration_id, $error_type, $form_data, $crm_data, $api_response = null, $retry_count = 0, $crm_record_id = null)
        {
            // Encode data fields to JSON if they are arrays
            $form_data_json = is_array($form_data) ? wp_json_encode($form_data) : $form_data;
            $crm_data_json = is_array($crm_data) ? wp_json_encode($crm_data) : $crm_data;
            $api_response_json = is_array($api_response) ? wp_json_encode($api_response) : $api_response;

            // Insert the new error log entry
            $result = $this->wpdb->insert(
                $this->table_name,
                [
                    'integration_id' => $integration_id,
                    'error_type' => $error_type,
                    'form_data' => $form_data_json,
                    'crm_data' => $crm_data_json,
                    'api_response' => $api_response_json,
                    'crm_record_id' => $crm_record_id,
                    'retry_count' => $retry_count
                ],
                [
                    '%d', // integration_id (integer)
                    '%s', // error_type (string)
                    '%s', // form_data (JSON string)
                    '%s', // crm_data (JSON string)
                    '%s', // api_response (nullable JSON string)
                    '%s', // crm_record_id (nullable string)
                    '%d'  // retry_count (integer)
                ]
            );

            if ($result === false) {
                return false;
            }
            if ($error_type != "SUCCESS") {
                iafwzdesk_send_error_log_details_email_by_log_id($this->wpdb->insert_id);
            }
            return $this->wpdb->insert_id;
        }

        public function update_error_log($log_id, $code, $form_data, $crm_data, $api_response = null, $crm_record_id = null, $retry_count = 0)
        {
            // Encode data fields to JSON if they are arrays
            $form_data_json = is_array($form_data) ? wp_json_encode($form_data) : $form_data;
            $crm_data_json = is_array($crm_data) ? wp_json_encode($crm_data) : $crm_data;
            $api_response_json = is_array($api_response) ? wp_json_encode($api_response) : $api_response;

            // Update the existing error log entry
            $result = $this->wpdb->update(
                $this->table_name,
                [
                    'error_type'     => $code,
                    'form_data'      => $form_data_json,
                    'crm_data'       => $crm_data_json,
                    'api_response'   => $api_response_json,
                    'crm_record_id'  => $crm_record_id,
                    'retry_count'    => $retry_count + 1, // Increment retry count by 1
                ],
                ['id' => $log_id], // Where condition
                [
                    '%s', // error_type (string)
                    '%s', // form_data (JSON string)
                    '%s', // crm_data (JSON string)
                    '%s', // api_response (nullable JSON string)
                    '%s', // crm_record_id (nullable string)
                    '%d', // retry_count (integer)
                ],
                ['%d'] // id (integer)
            );

            return $result !== false;
        }

        // Retrieve an error log by ID
        public function get_error_log($id)
        {
            global $wpdb;

            // Sanitize the table name to ensure it’s safe.
            $table_name = esc_sql($this->table_name);

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

            // Decode the JSON fields if available
            if ($result) {
                $result->form_data = json_decode($result->form_data, true);
                $result->crm_data = json_decode($result->crm_data, true);
                $result->api_response = json_decode($result->api_response, true);
            }

            return $result;
        }

        // Delete an error log by ID
        public function delete_error_log($id)
        {
            return $this->wpdb->delete(
                $this->table_name,
                ['id' => $id],
                ['%d']
            ) !== false;
        }
        // Delete all error logs
        public function delete_all_error_logs()
        {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            return $this->wpdb->query("DELETE FROM {$this->table_name}") !== false;
        }
        // Delete only records where error_type is "SUCCESS"
        public function delete_all_success_logs()
        {
            global $wpdb;
            $table_name = $this->table_name;

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            return $wpdb->query(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->prepare("DELETE FROM {$table_name} WHERE error_type = %s", 'SUCCESS')
            ) !== false;
        }

        // Retrieve all error logs
        public function get_all_error_logs()
        {

            $results = $this->wpdb->get_results("SELECT * FROM " . esc_sql($this->table_name), OBJECT);

            // Decode the JSON fields for each result
            foreach ($results as &$result) {
                $result->form_data = json_decode($result->form_data, true);
                $result->crm_data = json_decode($result->crm_data, true);
                $result->api_response = json_decode($result->api_response, true);
            }

            return $results;
        }
        public function get_all_error_logs_with_timefilter($time_filter = '')
        {
            global $wpdb;

            // Sanitize the table name to ensure it’s safe
            $table_name = esc_sql($this->table_name);

            // Base query
            $query = "SELECT * FROM {$table_name}";

            // Array to store query placeholders and values
            $where_clauses = [];
            $query_params = [];

            // Add WHERE clause based on the time filter
            if ($time_filter) {
                $current_date = current_time('Y-m-d'); // WordPress current date

                switch ($time_filter) {
                    case 'today':
                        $where_clauses[] = "DATE(created_at) = %s";
                        $query_params[] = $current_date;
                        break;
                    case 'yesterday':
                        $where_clauses[] = "DATE(created_at) = DATE_SUB(%s, INTERVAL 1 DAY)";
                        $query_params[] = $current_date;
                        break;
                    case 'last_7_days':
                        $where_clauses[] = "created_at >= DATE_SUB(%s, INTERVAL 7 DAY)";
                        $query_params[] = $current_date;
                        break;
                    case 'last_30_days':
                        $where_clauses[] = "created_at >= DATE_SUB(%s, INTERVAL 30 DAY)";
                        $query_params[] = $current_date;
                        break;
                }

                if (!empty($where_clauses)) {
                    $query .= " WHERE " . implode(' AND ', $where_clauses);
                }
            }

            // Prepare the query
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $prepared_query = $wpdb->prepare($query, $query_params);

            // Fetch results
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $results = $wpdb->get_results($prepared_query, OBJECT);

            // Decode the JSON fields for each result
            foreach ($results as &$result) {
                $result->form_data = json_decode($result->form_data, true);
                $result->crm_data = json_decode($result->crm_data, true);
                $result->api_response = json_decode($result->api_response, true);
            }

            return $results;
        }
        public function get_unique_error_types()
        {
            global $wpdb;

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

            // Prepare and execute the query to fetch distinct error_type values
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $results = $wpdb->get_col("SELECT DISTINCT error_type FROM {$table_name}");

            // Return the results as an array
            return $results ? $results : [];
        }

        public function get_unique_integration_ids()
        {
            global $wpdb;

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

            // Prepare and execute the query to fetch distinct integration_id values
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $results = $wpdb->get_col("SELECT DISTINCT integration_id FROM {$table_name}");

            // Return the results as an array
            return $results ? $results : [];
        }
    }
    $IAFWZDESK_Error_LogDBInstance = new IAFWZDESK_Error_Log();
}
