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

    class IAFWZDESK_Field_Mapping
    {
        private $wpdb;
        private $table_name;

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

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

            $charset_collate = $wpdb->get_charset_collate();
            $account_table = $wpdb->get_blog_prefix() . "iafwzdesk_account_details";

            $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
                integration_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                integration_name VARCHAR(255) NOT NULL, -- Integration name
                form_group_id INT NOT NULL, -- Form group ID
                form_id VARCHAR(255) NOT NULL, -- Form ID
                account_id BIGINT(20) UNSIGNED NOT NULL, -- Account ID, foreign key
                module_name VARCHAR(255) NOT NULL, -- Module name
                field_mapping JSON NOT NULL, -- JSON field mapping
                record_action VARCHAR(255) NOT NULL, -- Record action details
                integration_status TINYINT(1) DEFAULT 1, -- Status (1 = active, 0 = inactive)
                filter_criteria JSON NULL, -- allows NULL values
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Created time
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last updated time
                 FOREIGN KEY (account_id) REFERENCES $account_table(account_id) ON DELETE CASCADE
            ) $charset_collate;";

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

        // Create a new mapping with JSON field encoding
        public function create_mapping($integration_name, $form_group_id, $form_id, $account_id, $module_name, $record_action, $field_mapping, $filter_criteria = null)
        {
            global $wpdb;

            // Ensure field_mapping is JSON-encoded if it’s an array
            $field_mapping_json = is_array($field_mapping) ? wp_json_encode($field_mapping) : $field_mapping;

            // Prepare data for insertion
            $data = [
                'integration_name' => sanitize_text_field($integration_name), // Sanitize integration_name
                'form_group_id'    => intval($form_group_id), // Ensure form_group_id is an integer
                'form_id'          => sanitize_text_field($form_id), // Ensure form_id is an integer
                'account_id'       => intval($account_id), // Ensure account_id is an integer
                'module_name'      => sanitize_text_field($module_name), // Sanitize module_name
                'record_action'    => sanitize_text_field($record_action), // Sanitize record_action
                'field_mapping'    => sanitize_text_field($field_mapping_json), // Escape JSON for DB
                'filter_criteria'  => $filter_criteria !== null ? sanitize_text_field($filter_criteria) : null // 
            ];

            // Prepare data formats
            $formats = [
                '%s', // integration_name (string)
                '%d', // form_group_id (integer)
                '%s', // form_id (integer)
                '%d', // account_id (integer)
                '%s', // module_name (string)
                '%s', // record_action (string)
                '%s', // field_mapping (JSON string)
                $filter_criteria !== null ? '%s' : '%s'
            ];

            // Insert data into the database
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $result = $wpdb->insert($this->table_name, $data, $formats);

            // Check for errors and return the insert ID or WP_Error
            if ($result === false) {
                return new WP_Error('db_insert_error', __('Failed to create mapping.', 'integrate-with-zoho-desk'), $wpdb->last_error);
            }
            // Check if the option exists before adding it
            if (get_option('iafwzdesk_review_enabled') === false) {
                add_option('iafwzdesk_review_enabled', 'yes');
            }
            return $wpdb->insert_id;
        }



        // Update an existing mapping with JSON field encoding
        public function update_mapping($integration_id, $integration_name, $form_group_id, $form_id, $account_id, $module_name, $record_action, $field_mapping, $filter_criteria = null)
        {
            global $wpdb;

            // Ensure field_mapping is JSON-encoded if it’s an array
            $field_mapping_json = is_array($field_mapping) ? wp_json_encode($field_mapping) : $field_mapping;

            // Prepare data for update
            $data = [
                'integration_name' => sanitize_text_field($integration_name), // Sanitize integration_name
                'form_group_id'    => intval($form_group_id), // Ensure form_group_id is an integer
                'form_id'          => sanitize_text_field($form_id), // Ensure form_id is an integer
                'account_id'       => intval($account_id), // Ensure account_id is an integer
                'module_name'      => sanitize_text_field($module_name), // Sanitize module_name
                'record_action'    => sanitize_text_field($record_action), // Sanitize record_action
                'field_mapping'    => sanitize_text_field($field_mapping_json), // Escape JSON for DB
                'filter_criteria'  => $filter_criteria !== null ? sanitize_text_field($filter_criteria) : null // 
            ];

            // Prepare data formats
            $formats = [
                '%s', // integration_name (string)
                '%d', // form_group_id (integer)
                '%s', // form_id (integer)
                '%d', // account_id (integer)
                '%s', // module_name (string)
                '%s', // record_action (string)
                '%s', // field_mapping (JSON string)
                $filter_criteria !== null ? '%s' : '%s'
            ];

            // Update condition
            $where = ['integration_id' => intval($integration_id)];
            $where_format = ['%d']; // integration_id format

            // Perform the update
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->update(
                $this->table_name,
                $data,
                $where,
                $formats,
                $where_format
            );

            // Return true if update was successful, false otherwise
            if ($result === false) {
                return new WP_Error('db_update_error', __('Failed to update mapping.', 'integrate-with-zoho-desk'), $wpdb->last_error);
            }

            return $wpdb->rows_affected > 0; // Return true if rows were updated, false if no rows were affected
        }


        // Function to update the integration status
        public function update_integration_status($integration_id, $status)
        {
            // Ensure $new_status is either 0 or 1 to represent false or true
            $new_status = $status ? 1 : 0;

            // Update the integration status for the given integration_id
            $result = $this->wpdb->update(
                $this->table_name,
                ['integration_status' => $new_status],
                ['integration_id' => $integration_id],
                ['%d'], // Format for the value being updated
                ['%d']  // Format for the where clause
            );

            // Check if the update was successful and return true/false
            return $result !== false;
        }


        // Retrieve and decode JSON mapping
        public function get_mapping($integration_id)
        {
            global $wpdb;

            // Sanitize the table name.
            $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 integration_id = %d", intval($integration_id)), OBJECT);

            // Check if the result exists and decode the JSON field
            if ($result && isset($result->field_mapping)) {
                $result->field_mapping = json_decode($result->field_mapping, true);
            }

            return $result;
        }


        // Retrieve all mappings and decode JSON field
        public function get_all_mappings()
        {
            global $wpdb;

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

            // Construct the query using the sanitized table name
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $results = $wpdb->get_results("SELECT * FROM {$table_name}", OBJECT);

            // Decode the JSON field for each result if it exists
            if (!empty($results)) {
                foreach ($results as &$result) {
                    if (!empty($result->field_mapping)) {
                        $result->field_mapping = json_decode($result->field_mapping, true);
                    }
                }
            }

            return $results;
        }



        public function get_mapping_by_id($form_id, $form_group_id)
        {
            global $wpdb;

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

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

            // Check if the result exists and decode the JSON field
            if ($result && isset($result->field_mapping)) {
                $result->field_mapping = json_decode($result->field_mapping, true);
            }

            return $result;
        }

        // Retrieve total count of mappings
        public function getFieldMappingTotalCount()
        {
            global $wpdb;

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

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $count = $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}");


            return (int) $count;
        }

        // Delete a mapping based on integration_id
        public function delete_mapping($integration_id)
        {
            // Delete the record from the database
            $deleted = $this->wpdb->delete(
                $this->table_name,
                [
                    'integration_id' => $integration_id // Condition to match the specific record to delete
                ],
                [
                    '%d' // integration_id (integer for the WHERE clause)
                ]
            );

            // Return true if deletion was successful, false otherwise
            return ($deleted !== false);
        }
        public function get_integration_name($integration_id)
        {
            global $wpdb;

            // Sanitize the table name.
            $table_name = esc_sql($this->table_name);
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $integration_name = $wpdb->get_var($wpdb->prepare("SELECT integration_name FROM {$table_name} WHERE integration_id = %d", intval($integration_id)));

            // Return the integration name, or null if it doesn't exist
            return $integration_name ? $integration_name : null;
        }
        public function block_field_mapping()
        {
            global $wpdb;
            $premium_fields = iafwzdesk_getPremiumFields();
            array_push($premium_fields, 'attachment');
            // Ensure table name is escaped for safety
            $table_name = esc_sql($this->table_name);

            // Get all rows from the table
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching	
            $rows = $wpdb->get_results("SELECT integration_id,integration_status, field_mapping, record_action FROM {$table_name}");
            $non_premium_count = 0;
            if (!empty($rows)) {
                foreach ($rows as $row) {
                    $field_mapping = json_decode($row->field_mapping, true);
                    $record_action = $row->record_action;
                    if (is_array($field_mapping) && intval($row->integration_status) === 1) {

                        $has_premium = false;

                        // Iterate through field mapping to check premium and count non-premium fields
                        foreach ($field_mapping as $field_name => $field_data) {
                            if (in_array($field_name, $premium_fields, true)) {
                                $has_premium = true; // Mark as having a premium field
                                break; // Exit the loop immediately if a premium field is found
                            }
                        }
                        $has_custom_fields = false;

                        // Iterate through field mapping to check for custom fields
                        foreach ($field_mapping as $field_name => $field_data) {
                            if (strpos($field_name, 'iafwzdesk_cf_') === 0) { // Check if the field name starts with 'cf_'
                                $has_custom_fields = true; // Mark as having a custom field
                                break; // Exit the loop immediately if a custom field is found
                            }
                        }

                        // If no premium field found, count non-premium fields
                        if (!$has_premium && !$has_custom_fields) {
                            $non_premium_count++; // Count non-premium fields
                        }

                        //------record_action
                        $has_record_action = false;
                        if ($record_action != "add_ignore_existing") {
                            $has_record_action = true;
                        }

                        // If premium fields are present or non-premium count exceeds 2
                        if ($has_premium || $has_custom_fields || $non_premium_count > 2 || $has_record_action) {
                            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching	
                            $wpdb->update(
                                $table_name,
                                [
                                    'integration_status' => 0,       // Data to update
                                    'record_action' => "add_ignore_existing" // Data to update
                                ],
                                [
                                    'integration_id' => $row->integration_id // WHERE condition
                                ],
                                [
                                    '%d', // Format for integration_status
                                    '%s' // Format for record_action
                                ],
                                [
                                    '%d' // Format for WHERE condition (integration_id)
                                ]
                            );
                        }
                    }
                }
            }
        }
    }
    $IAFWZDESK_Field_MappingDBInstance = new IAFWZDESK_Field_Mapping();
}
