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

    class integrazo_fwcrm_form_FieldMapping
    {
        private $wpdb;
        private $table_name;

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

        // Determine the correct table name based on multisite or single-site
        // private function get_table_name()
        // {
        //     // Each site will have its own table with the site-specific prefix
        //     return $this->wpdb->prefix . 'integrazo_fwcrm_form_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() . "integrazo_fwcrm_form_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)
                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);
        }




        // 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("DROP TABLE IF EXISTS " . 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;
        }
        // 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)
        {
            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
            ];

            // 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)
            ];

            // 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.', 'crm-integration-freshworks-any-form'), $wpdb->last_error);
            }
            // Check if the option exists before adding it
            if (get_option('integrazo_fwcrm_form_review_enabled') === false) {
                add_option('integrazo_fwcrm_form_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)
        {
            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
            ];

            // 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)
            ];

            // 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.', 'crm-integration-freshworks-any-form'), $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);

            // Fetch the row from the database.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            // $result = $wpdb->get_row(
            //     // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            //     $wpdb->prepare(
            //         "SELECT * FROM {$table_name} WHERE integration_id = %d",
            //         $integration_id
            //     ),
            //     OBJECT // Change to ARRAY_A if you prefer an associative array.
            // );
            // 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;
        }



        // Retrieve mapping based on form_id and form_group_id
        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;
        }
        public function get_mapping_by_post_id_prefix($form_post_id, $form_group_id)
        {
            global $wpdb;
            $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 LIKE %s AND form_group_id = %d",
                    $wpdb->esc_like($form_post_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);

            // Query the database for the integration name based on the integration_id.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            // $integration_name = $wpdb->get_var(
            //     // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            //     $wpdb->prepare(
            //         "SELECT integration_name FROM {$table_name} WHERE integration_id = %d",
            //         $integration_id
            //     )
            // );
            // 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 = integrazo_fwcrm_form_getPremiumFields();
            array_push($premium_fields, 'tags', '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 FROM {$table_name}");
            $non_premium_count = 0;
            if (!empty($rows)) {
                foreach ($rows as $row) {
                    $field_mapping = json_decode($row->field_mapping, true);
                    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, '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
                        }

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