<?php

namespace BookWellAB;
use wpdb;

defined('ABSPATH') || exit;
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange

if (!class_exists('BookWellAB_Database')) {

    class BookWellAB_Database
    {
        private $supports_typed_settings = null;

        /**
         * Build a prefixed wp_options key (bookwellab_).
         *
         * @param string $suffix Option suffix without leading underscore.
         * @return string
         */
        public static function get_db_store_option_name($suffix)
        {
            return BOOKWELLAB_OPTION_PREFIX . ltrim((string) $suffix, '_');
        }

        /**
         * Build a prefixed transient name (bookwellab_).
         *
         * @param string $suffix Transient suffix without leading underscore.
         * @return string
         */
        public static function get_transient_name($suffix)
        {
            return self::get_db_store_option_name($suffix);
        }

        /**
         * Read a prefixed option, migrating legacy bwab_/beab_ keys when found.
         *
         * @param string $suffix Option suffix without leading underscore.
         * @param mixed  $default Default when missing.
         * @return mixed
         */
        public static function get_db_store_option($suffix, $default = false)
        {
            $option_name = self::get_db_store_option_name($suffix);
            $value = get_option($option_name, null);

            if (null !== $value) {
                return $value;
            }

            $legacy_keys = array(
                'beab_' . ltrim((string) $suffix, '_'),
                'bwab_' . ltrim((string) $suffix, '_'),
            );

            foreach ($legacy_keys as $legacy_key) {
                $legacy_value = get_option($legacy_key, null);
                if (null !== $legacy_value) {
                    update_option($option_name, $legacy_value);
                    return $legacy_value;
                }
            }

            return $default;
        }

        /**
         * Persist a prefixed wp_options value (bookwellab_).
         *
         * @param string $suffix Option suffix without leading underscore.
         * @param mixed  $value  Value to store.
         * @return bool
         */
        public static function update_db_store_option($suffix, $value)
        {
            return update_option(self::get_db_store_option_name($suffix), $value);
        }

        public function get_table_name($type = '')
        {
            global $wpdb;
            $prefix = $wpdb->prefix . 'bookwellab_';

            switch ($type) {
                case 'services':
                    return $prefix . 'services';
                case 'categories':
                    return $prefix . 'categories';
                case 'staff_special_days':
                    return $prefix . 'staff_special_days';
                default:
                    return $prefix . $type;
            }
        }

        public function upsert($table_name, array $data, array $where = null, $where_placehoder = array())
        {
            global $wpdb;
            // If no WHERE, assume insert
            if ($where === null) {
                $result = $wpdb->insert($table_name, $data);
                return $result !== false ? $wpdb->insert_id : false;
            } else {
                // UPDATE with custom WHERE
                $result = $wpdb->update($table_name, $data, $where);
                return $result !== false ? true : false;
            }
        }

        public function get_staff_services($staff_id)
        {
            global $wpdb;

            $table = esc_sql($this->get_table_name('staff_services'));
            $service_table = esc_sql($this->get_table_name('services'));

            $Staff_services_res = $wpdb->get_results($wpdb->prepare(
                "SELECT s.*, ss.bw_service_price, ss.bw_service_duration_val, 
                        ss.bw_service_duration_unit, ss.bw_is_primary
                 FROM {$service_table} s
                 INNER JOIN $table ss ON s.bw_service_id = ss.bw_service_id
                 WHERE ss.bw_staff_id = %d",
                $staff_id
            ), ARRAY_A);

            return $Staff_services_res;

        }

        public function update_staff_services($staff_id, $services)
        {
            global $wpdb;

            $staff_id = absint($staff_id);
            if ($staff_id <= 0) {
                return false;
            }

            $table = esc_sql($this->get_table_name('staff_services'));

            // Replace the full assignment set (insert-only left removed services in the DB).
            $wpdb->delete($table, array('bw_staff_id' => $staff_id), array('%d'));

            if (empty($services) || !is_array($services)) {
                return true;
            }

            foreach ($services as $service) {
                $wpdb->insert($table, [
                    'bw_staff_id' => $staff_id,
                    'bw_service_id' => $service['id'],
                    'bw_service_price' => $service['price'] ?? null,
                    'bw_service_duration_val' => $service['duration_val'] ?? null,
                    'bw_service_duration_unit' => $service['duration_unit'] ?? null,
                    'bw_is_primary' => $service['is_primary'] ?? 0
                ]);
            }

            return true;
        }

        public function get_service_staff($service_id)
        {
            global $wpdb;
            $table = esc_sql($this->get_table_name('staff_services'));
            $service_table = esc_sql($this->get_table_name('services'));
            $staff_table = esc_sql($this->get_table_name('staffs'));

            return $wpdb->get_results($wpdb->prepare(
                "SELECT st.*, ss.bw_is_primary
                 FROM {$staff_table} st
                 INNER JOIN $table ss ON st.bw_staff_id = ss.bw_staff_id
                 WHERE ss.bw_service_id = %d",
                $service_id
            ), ARRAY_A);

        }

        public function update_service_staff($service_id, $staff_list)
        {
            global $wpdb;

            $service_id = absint($service_id);
            if ($service_id <= 0) {
                return false;
            }

            $table = esc_sql($this->get_table_name('staff_services'));

            $wpdb->delete($table, array('bw_service_id' => $service_id), array('%d'));

            if (empty($staff_list) || !is_array($staff_list)) {
                return true;
            }

            foreach ($staff_list as $staff) {
                $staff_id = isset($staff['id']) ? absint($staff['id']) : 0;
                if ($staff_id <= 0) {
                    continue;
                }

                $wpdb->insert(
                    $table,
                    array(
                        'bw_staff_id' => $staff_id,
                        'bw_service_id' => $service_id,
                        'bw_service_price' => $staff['price'] ?? null,
                        'bw_service_duration_val' => $staff['duration_val'] ?? null,
                        'bw_service_duration_unit' => $staff['duration_unit'] ?? null,
                        'bw_is_primary' => $staff['is_primary'] ?? 0,
                    )
                );
            }

            return true;
        }


        /**
         * Run a SELECT query with flexible options.
         *
         * @param string $type     Result type: 'results', 'row', 'col', 'var'.
         * @param string $select   SQL SELECT part (e.g., 'u.ID, u.user_email').
         * @param string $from     SQL FROM and optional JOINs.
         * @param string $where    SQL WHERE clause (optional).
         * @param array  $args     Prepared statement arguments for WHERE clause.      
         * @param string $group_by SQL GROUP BY clause (optional).
         * @param string $order_by SQL ORDER BY clause (optional).
         *
         * @return mixed
         */
        public function select($type = 'results', $select = "*", $from = '', $where = '', $args = [], $order_by = '', $group_by = '')
        {
            global $wpdb;
            $sql = "SELECT $select FROM $from";
            if (!empty($where)) {
                $sql .= " WHERE $where";
            }
            if (!empty($group_by)) {
                $sql .= " GROUP BY $group_by";
            }
            if (!empty($order_by)) {
                $sql .= " ORDER BY $order_by";
            }
            if (!empty($where) && !empty($args)) {
                $sql = $wpdb->prepare($sql, $args);
            }

            switch ($type) {
                case 'results':
                    // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is built by this DB helper and prepared when args are provided.
                    return $wpdb->get_results($sql, ARRAY_A);
                case 'row':
                    // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is built by this DB helper and prepared when args are provided.
                    return $wpdb->get_row($sql, ARRAY_A);
                case 'col':
                    // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is built by this DB helper and prepared when args are provided.
                    return $wpdb->get_col($sql, ARRAY_A);
                case 'var':
                    // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is built by this DB helper and prepared when args are provided.
                    return $wpdb->get_var($sql);
                default:
                    return '';
            }
        }

        public function install_tables()
        {
            global $wpdb;
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';

            $charset_collate = $wpdb->get_charset_collate();

            $customers_table = esc_sql($this->get_table_name('customers'));
            $appointments_table = esc_sql($this->get_table_name('appointments'));
            $services_table = esc_sql($this->get_table_name('services'));
            $categories_table = esc_sql($this->get_table_name('categories'));
            $staff_table = esc_sql($this->get_table_name('staffs'));
            $staff_services_table = esc_sql($this->get_table_name('staff_services'));
            $staff_working_hours_table = esc_sql($this->get_table_name('staff_working_hours'));
            $staff_special_days_table = esc_sql($this->get_table_name('staff_special_days'));
            $notifications_table = esc_sql($this->get_table_name('notifications'));
            $settings_table = esc_sql($this->get_table_name('settings'));
            $spam_tokens_table = esc_sql($this->get_table_name('spam_tokens'));
            $payment_debug_logs_table = esc_sql($this->get_table_name('payment_debug_logs'));

            $sql[] = "CREATE TABLE $services_table (
                bw_service_id INT(11) NOT NULL AUTO_INCREMENT,
                bw_category_id SMALLINT DEFAULT 0,
                bw_service_name VARCHAR(255) NOT NULL,
                bw_service_image VARCHAR(255) NOT NULL,
                bw_service_image_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
                bw_service_price DOUBLE NOT NULL,
                bw_service_duration_val INT(11) NOT NULL,
                bw_service_duration_unit VARCHAR(1) NOT NULL,
                bw_service_description TEXT NOT NULL,
                bw_service_status TINYINT(1) NOT NULL DEFAULT 1,
                bw_service_position INT(11) NOT NULL,
                bw_zoom_enabled TINYINT(1) NOT NULL DEFAULT 0,
                bw_service_create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_service_id),
                KEY bw_service_image_id (bw_service_image_id)
            ) $charset_collate;";

            $sql[] = "CREATE TABLE $categories_table (
                bw_category_id SMALLINT NOT NULL AUTO_INCREMENT,
                bw_category_name VARCHAR(255) NOT NULL,
                bw_category_position SMALLINT NOT NULL,
                bw_category_create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_category_id)
            ) $charset_collate;";

            $sql[] = "CREATE TABLE $staff_table (
                bw_staff_id INT(11) NOT NULL AUTO_INCREMENT,
                bw_wp_user_id INT(11) DEFAULT 0,
                bw_staff_name VARCHAR(255) NOT NULL,
                bw_staff_email VARCHAR(255) NOT NULL,
                bw_staff_phone VARCHAR(20) DEFAULT NULL,
                bw_staff_image VARCHAR(255) DEFAULT NULL,
                bw_staff_position VARCHAR(255) DEFAULT NULL,
                bw_staff_bio TEXT DEFAULT NULL,
                bw_staff_is_active TINYINT(1) DEFAULT 1,
                bw_staff_position_order INT(11) DEFAULT 0,
                bw_zoom_enabled TINYINT(1) NOT NULL DEFAULT 0,
                bw_zoom_user_id VARCHAR(100) DEFAULT NULL,
                bw_staff_create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                bw_staff_update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_staff_id),
                UNIQUE KEY bw_staff_email (bw_staff_email),
                KEY bw_wp_user_id (bw_wp_user_id),
                KEY bw_staff_is_active (bw_staff_is_active)
            ) $charset_collate;";


            $sql[] = "CREATE TABLE $staff_services_table (
                bw_staff_service_id INT(11) NOT NULL AUTO_INCREMENT,
                bw_staff_id INT(11) NOT NULL,
                bw_service_id INT(11) NOT NULL,
                bw_service_price DOUBLE DEFAULT NULL,
                bw_service_duration_val INT(11) DEFAULT NULL,
                bw_service_duration_unit VARCHAR(1) DEFAULT NULL,
                bw_is_primary TINYINT(1) DEFAULT 0,
                bw_create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_staff_service_id),
                UNIQUE KEY bw_staff_service_unique (bw_staff_id, bw_service_id),
                KEY bw_staff_id (bw_staff_id),
                KEY bw_service_id (bw_service_id)
            ) $charset_collate;";

            $sql[] = "CREATE TABLE $staff_working_hours_table (
                bw_hour_id INT(11) NOT NULL AUTO_INCREMENT,
                bw_staff_id INT(11) NOT NULL,
                bw_day_name VARCHAR(20) NOT NULL,
                bw_start_time VARCHAR(20) DEFAULT NULL,
                bw_end_time VARCHAR(20) DEFAULT NULL,
                bw_is_off TINYINT(1) DEFAULT 0,
                bw_breaks LONGTEXT DEFAULT NULL,
                bw_create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_hour_id),
                KEY bw_staff_id (bw_staff_id)
            ) $charset_collate;";

            $sql[] = "CREATE TABLE $staff_special_days_table (
                bw_special_day_id INT(11) NOT NULL AUTO_INCREMENT,
                bw_staff_id INT(11) NOT NULL,
                bw_start_date DATE NOT NULL,
                bw_end_date DATE NOT NULL,
                bw_start_time VARCHAR(20) DEFAULT NULL,
                bw_end_time VARCHAR(20) DEFAULT NULL,
                bw_is_off TINYINT(1) DEFAULT 0,
                bw_breaks LONGTEXT DEFAULT NULL,
                bw_create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_special_day_id),
                KEY bw_staff_id (bw_staff_id)
            ) $charset_collate;";

            $sql[] = "CREATE TABLE $customers_table (
                bw_customer_id INT(11) NOT NULL AUTO_INCREMENT,
                bw_first_name VARCHAR(255) NOT NULL,
                bw_last_name VARCHAR(255) NOT NULL,
                bw_email VARCHAR(255) NOT NULL,
                bw_phone_code VARCHAR(10) DEFAULT NULL,
                bw_phone VARCHAR(50) DEFAULT NULL,
                bw_country_slug VARCHAR(10) DEFAULT NULL,
                bw_wp_user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
                bw_create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_customer_id),
                UNIQUE KEY bw_email (bw_email),
                KEY bw_create_at (bw_create_at),
                KEY bw_wp_user_id (bw_wp_user_id)
            ) $charset_collate;";
            
            $sql[] = "CREATE TABLE $appointments_table (
                bw_appointment_id INT(11) NOT NULL AUTO_INCREMENT,
                bw_appointment_hash VARCHAR(50) NOT NULL,
                bw_service_id INT(11) NOT NULL,
                bw_service_name VARCHAR(255) NOT NULL,
                bw_staff_id INT(11) NOT NULL,
                bw_staff_name VARCHAR(255) NOT NULL,
                bw_customer_id INT(11) NOT NULL,
                bw_date DATE NOT NULL,
                bw_time TIME NOT NULL,
                bw_duration INT(11) NOT NULL COMMENT 'In minutes',
                bw_price DOUBLE NOT NULL,
                bw_status VARCHAR(50) DEFAULT 'pending',
                bw_customer_name VARCHAR(255) NOT NULL,
                bw_customer_email VARCHAR(255) NOT NULL,
                bw_customer_phone_code VARCHAR(10) DEFAULT NULL,
                bw_customer_phone VARCHAR(50) DEFAULT NULL,
                bw_customer_country_slug VARCHAR(10) DEFAULT NULL,
                bw_note TEXT DEFAULT NULL,
                bw_payment_gateway VARCHAR(50) DEFAULT 'manually',
                bw_payment_status VARCHAR(50) DEFAULT 'pending',
                bw_zoom_meeting_id VARCHAR(100) DEFAULT NULL,
                bw_zoom_host_url TEXT DEFAULT NULL,
                bw_zoom_join_url TEXT DEFAULT NULL,
                bw_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_appointment_id),
                KEY bw_customer_id (bw_customer_id),
                KEY bw_appointment_composite (bw_service_id, bw_staff_id, bw_customer_id, bw_status, bw_payment_status, bw_date, bw_time)
            ) $charset_collate;";

            $sql[] = "CREATE TABLE $notifications_table (
                bw_notification_id INT(11) NOT NULL AUTO_INCREMENT,
                bw_notification_name VARCHAR(255) NOT NULL,
                bw_notification_receiver_type ENUM('customer', 'employee') NOT NULL,
                bw_notification_status TINYINT(1) DEFAULT 1,
                bw_notification_type VARCHAR(50) DEFAULT 'default',
                bw_notification_subject VARCHAR(255) NOT NULL,
                bw_notification_message TEXT NOT NULL,
                bw_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_notification_id),
                KEY bw_notification_name (bw_notification_name),
                KEY bw_notification_receiver_type (bw_notification_receiver_type)
            ) $charset_collate;";

            $sql[] = "CREATE TABLE $settings_table (
                bw_setting_id INT(11) NOT NULL AUTO_INCREMENT,
                bw_setting_type VARCHAR(100) NOT NULL DEFAULT 'legacy',
                bw_setting_name VARCHAR(150) NOT NULL,
                bw_setting_value LONGTEXT NOT NULL,
                PRIMARY KEY (bw_setting_id),
                UNIQUE KEY bw_setting_type_name (bw_setting_type, bw_setting_name),
                KEY bw_setting_name (bw_setting_name)
            ) $charset_collate;";

            $sql[] = "CREATE TABLE $spam_tokens_table (
                bw_spam_token_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                bw_token_key_hash VARCHAR(32) NOT NULL,
                bw_token_hash VARCHAR(64) NOT NULL,
                bw_expires_at DATETIME NOT NULL,
                bw_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_spam_token_id),
                UNIQUE KEY bw_token_key_hash (bw_token_key_hash),
                KEY bw_expires_at (bw_expires_at)
            ) $charset_collate;";

            $sql[] = "CREATE TABLE $payment_debug_logs_table (
                bw_log_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                bw_log_ref_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
                bw_log_gateway VARCHAR(50) NOT NULL DEFAULT '',
                bw_log_event VARCHAR(255) NOT NULL DEFAULT '',
                bw_log_event_from VARCHAR(255) NOT NULL DEFAULT '',
                bw_log_data LONGTEXT,
                bw_log_created_at DATETIME NOT NULL,
                PRIMARY KEY (bw_log_id),
                KEY bw_log_gateway (bw_log_gateway),
                KEY bw_log_created_at (bw_log_created_at),
                KEY bw_log_ref_id (bw_log_ref_id)
            ) $charset_collate;";

            $form_fields_table = esc_sql($this->get_table_name('form_fields'));

            $sql[] = "CREATE TABLE $form_fields_table (
                bw_form_field_id SMALLINT NOT NULL AUTO_INCREMENT,
                bw_form_field_name VARCHAR(100) NOT NULL,
                bw_field_type VARCHAR(50) NOT NULL DEFAULT 'Text',
                bw_field_required TINYINT(1) NOT NULL DEFAULT 0,
                bw_field_label TEXT NOT NULL,
                bw_field_placeholder TEXT DEFAULT NULL,
                bw_field_error_message VARCHAR(255) DEFAULT NULL,
                bw_field_is_hide TINYINT(1) NOT NULL DEFAULT 0,
                bw_field_position FLOAT NOT NULL DEFAULT 0,
                bw_field_is_default TINYINT(1) NOT NULL DEFAULT 1,
                bw_field_width VARCHAR(20) NOT NULL DEFAULT 'full',
                bw_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_form_field_id),
                UNIQUE KEY bw_form_field_name (bw_form_field_name),
                KEY bw_field_position (bw_field_position)
            ) $charset_collate;";

            foreach ($sql as $query) {
                dbDelta($query);
            }

            // Migration to add columns if they don't exist
            $this->add_missing_columns();
            $this->install_payment_debug_logs_table();
            $this->install_form_fields_table();
            $this->ensure_form_fields_table_and_data();
            $this->ensure_appointment_indexes();

            // Sync legacy status values to default "Active" (1) if they are null or empty
            $wpdb->query($wpdb->prepare("UPDATE $staff_table SET bw_staff_is_active = %d WHERE bw_staff_is_active IS NULL", 1));
            $wpdb->query($wpdb->prepare("UPDATE $services_table SET bw_service_status = %d WHERE bw_service_status IS NULL", 1));

            $this->migrate_legacy_settings_to_typed_rows();
            $this->migrate_options_to_table();
            $this->ensure_customer_panel_support();
            \BookWellAB\BookWellAB_Default_Pages::maybe_create_default_pages();
            $this->initialize_defaults();
            $this->bookwellab_install_default_notification_data();
            $this->bookwellab_install_default_general_settings_data();
            $this->ensure_zoom_and_debug_log_support();
            $this->ensure_week_starts_on_setting();

        }

        /**
         * Install or update spam token table schema only.
         *
         * @return void
         */
        public function install_spam_tokens_table()
        {
            global $wpdb;
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';

            $charset_collate = $wpdb->get_charset_collate();
            $spam_tokens_table = esc_sql($this->get_table_name('spam_tokens'));

            $query = "CREATE TABLE $spam_tokens_table (
                bw_spam_token_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                bw_token_key_hash VARCHAR(32) NOT NULL,
                bw_token_hash VARCHAR(64) NOT NULL,
                bw_expires_at DATETIME NOT NULL,
                bw_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_spam_token_id),
                UNIQUE KEY bw_token_key_hash (bw_token_key_hash),
                KEY bw_expires_at (bw_expires_at)
            ) $charset_collate;";

            dbDelta($query);
        }

        /**
         * Install or update payment debug logs table schema.
         *
         * @return void
         */
        public function install_payment_debug_logs_table()
        {
            global $wpdb;
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';

            $charset_collate = $wpdb->get_charset_collate();
            $payment_debug_logs_table = esc_sql($this->get_table_name('payment_debug_logs'));

            $query = "CREATE TABLE $payment_debug_logs_table (
                bw_log_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                bw_log_ref_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
                bw_log_gateway VARCHAR(50) NOT NULL DEFAULT '',
                bw_log_event VARCHAR(255) NOT NULL DEFAULT '',
                bw_log_event_from VARCHAR(255) NOT NULL DEFAULT '',
                bw_log_data LONGTEXT,
                bw_log_created_at DATETIME NOT NULL,
                PRIMARY KEY (bw_log_id),
                KEY bw_log_gateway (bw_log_gateway),
                KEY bw_log_created_at (bw_log_created_at),
                KEY bw_log_ref_id (bw_log_ref_id)
            ) $charset_collate;";

            dbDelta($query);
        }

        /**
         * Install or update form fields table schema.
         *
         * @return void
         */
        public function install_form_fields_table()
        {
            global $wpdb;
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';

            $charset_collate = $wpdb->get_charset_collate();
            $form_fields_table = esc_sql($this->get_table_name('form_fields'));

            $query = "CREATE TABLE $form_fields_table (
                bw_form_field_id SMALLINT NOT NULL AUTO_INCREMENT,
                bw_form_field_name VARCHAR(100) NOT NULL,
                bw_field_type VARCHAR(50) NOT NULL DEFAULT 'Text',
                bw_field_required TINYINT(1) NOT NULL DEFAULT 0,
                bw_field_label TEXT NOT NULL,
                bw_field_placeholder TEXT DEFAULT NULL,
                bw_field_error_message VARCHAR(255) DEFAULT NULL,
                bw_field_is_hide TINYINT(1) NOT NULL DEFAULT 0,
                bw_field_position FLOAT NOT NULL DEFAULT 0,
                bw_field_is_default TINYINT(1) NOT NULL DEFAULT 1,
                bw_field_width VARCHAR(20) NOT NULL DEFAULT 'full',
                bw_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (bw_form_field_id),
                UNIQUE KEY bw_form_field_name (bw_form_field_name),
                KEY bw_field_position (bw_field_position)
            ) $charset_collate;";

            dbDelta($query);
            $this->ensure_form_field_width_column();
            $this->maybe_seed_default_form_field_widths();
        }

        /**
         * Ensure form fields width column exists (fresh install + upgrades).
         *
         * @return void
         */
        public function ensure_form_field_width_column()
        {
            global $wpdb;

            $form_fields_table = esc_sql($this->get_table_name('form_fields'));

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$form_fields_table` LIKE 'bw_field_width'");
            if (!empty($row)) {
                return;
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
            $wpdb->query(
                "ALTER TABLE `$form_fields_table` ADD `bw_field_width` VARCHAR(20) NOT NULL DEFAULT 'full' AFTER `bw_field_is_default`"
            );

            $this->maybe_seed_default_form_field_widths();
        }

        /**
         * Seed Half Width on common default fields when width column is first added.
         *
         * @return void
         */
        public function maybe_seed_default_form_field_widths()
        {
            if (self::get_db_store_option('form_field_widths_seeded', '') === 'yes') {
                return;
            }

            global $wpdb;
            $form_fields_table = esc_sql($this->get_table_name('form_fields'));
            $half_fields = ['phone', 'first_name', 'last_name', 'email'];

            foreach ($half_fields as $field_name) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
                $wpdb->update(
                    $form_fields_table,
                    ['bw_field_width' => 'half'],
                    [
                        'bw_form_field_name' => $field_name,
                        'bw_field_width' => 'full',
                    ],
                    ['%s'],
                    ['%s', '%s']
                );
            }

            self::update_db_store_option('form_field_widths_seeded', 'yes');
        }

        /**
         * Whether the form fields table has at least one row.
         *
         * @return bool
         */
        public function form_fields_table_has_rows()
        {
            global $wpdb;

            $form_fields_table = esc_sql($this->get_table_name('form_fields'));
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin-owned table name.
            $count = $wpdb->get_var("SELECT COUNT(*) FROM $form_fields_table");

            return (int) $count > 0;
        }

        /**
         * Fetch all form field rows ordered by position.
         *
         * @return array<int, array<string, mixed>>
         */
        public function get_form_field_rows()
        {
            global $wpdb;

            $form_fields_table = esc_sql($this->get_table_name('form_fields'));
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin-owned table name.
            $rows = $wpdb->get_results(
                "SELECT * FROM $form_fields_table ORDER BY bw_field_position ASC, bw_form_field_id ASC",
                ARRAY_A
            );

            return is_array($rows) ? $rows : [];
        }

        /**
         * Map a database row to the admin/frontend field array shape.
         *
         * @param array<string, mixed> $row Database row.
         * @return array<string, mixed>
         */
        public function map_form_field_row_to_array(array $row)
        {
            $field_width = isset($row['bw_field_width']) ? (string) $row['bw_field_width'] : 'full';
            $field_width = ($field_width === 'half') ? 'half' : 'full';

            $mapped = [
                'id' => isset($row['bw_form_field_id']) ? (int) $row['bw_form_field_id'] : 0,
                'field_name' => isset($row['bw_form_field_name']) ? (string) $row['bw_form_field_name'] : '',
                'field_type' => isset($row['bw_field_type']) ? (string) $row['bw_field_type'] : 'Text',
                'is_required' => !empty($row['bw_field_required']),
                'label' => isset($row['bw_field_label']) ? (string) $row['bw_field_label'] : '',
                'placeholder' => isset($row['bw_field_placeholder']) ? (string) $row['bw_field_placeholder'] : '',
                'error_message' => isset($row['bw_field_error_message']) ? (string) $row['bw_field_error_message'] : '',
                'is_hide' => !empty($row['bw_field_is_hide']),
                'field_position' => isset($row['bw_field_position']) ? (float) $row['bw_field_position'] : 0,
                'is_default' => !empty($row['bw_field_is_default']) ? 1 : 0,
                'field_width' => $field_width,
            ];

            /**
             * Filters a mapped form field row (Pro may attach options/values/meta_key).
             *
             * @param array<string, mixed> $mapped Mapped field.
             * @param array<string, mixed> $row    Database row.
             */
            return apply_filters('bookwellab_form_field_mapped_row', $mapped, $row);
        }

        /**
         * Persist form field rows from the admin/API field shape.
         *
         * @param array<int, array<string, mixed>> $fields Field definitions.
         * @return bool
         */
        public function save_form_field_rows_from_api_format(array $fields)
        {
            global $wpdb;

            if (empty($fields)) {
                return false;
            }

            $form_fields_table = esc_sql($this->get_table_name('form_fields'));

            foreach ($fields as $field) {
                if (!is_array($field)) {
                    continue;
                }

                $field_name = isset($field['field_name']) ? sanitize_key((string) $field['field_name']) : '';
                if ($field_name === '') {
                    continue;
                }

                $label = isset($field['label']) ? (string) $field['label'] : '';
                if ($field_name === 'terms_and_conditions') {
                    $label = wp_kses_post($label);
                } else {
                    $label = sanitize_text_field($label);
                }

                $is_default = array_key_exists('is_default', $field)
                    ? (!empty($field['is_default']) ? 1 : 0)
                    : 1;

                $field_width = isset($field['field_width']) ? sanitize_key((string) $field['field_width']) : 'full';
                $field_width = ($field_width === 'half') ? 'half' : 'full';

                $data = [
                    'bw_form_field_name' => $field_name,
                    'bw_field_type' => sanitize_text_field((string) ($field['field_type'] ?? 'Text')),
                    'bw_field_required' => !empty($field['is_required']) ? 1 : 0,
                    'bw_field_label' => $label,
                    'bw_field_placeholder' => sanitize_text_field((string) ($field['placeholder'] ?? '')),
                    'bw_field_error_message' => sanitize_text_field((string) ($field['error_message'] ?? '')),
                    'bw_field_is_hide' => !empty($field['is_hide']) ? 1 : 0,
                    'bw_field_position' => isset($field['field_position']) ? (float) $field['field_position'] : 0,
                    'bw_field_is_default' => $is_default,
                    'bw_field_width' => $field_width,
                ];

                /**
                 * Filters form field DB row data before insert/update.
                 *
                 * @param array<string, mixed> $data  Columns to write.
                 * @param array<string, mixed> $field Source field definition.
                 */
                $data = apply_filters('bookwellab_form_field_db_data', $data, $field);
                if (!is_array($data) || empty($data['bw_form_field_name'])) {
                    continue;
                }

                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin-owned table name.
                $existing_id = $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT bw_form_field_id FROM $form_fields_table WHERE bw_form_field_name = %s",
                        $field_name
                    )
                );

                if ($existing_id) {
                    $wpdb->update($form_fields_table, $data, ['bw_form_field_id' => (int) $existing_id]);
                } else {
                    $wpdb->insert($form_fields_table, $data);
                }
            }

            return true;
        }

        /**
         * Seed default form fields when the table is empty.
         *
         * @param array<int, array<string, mixed>> $fields Default field definitions.
         * @return void
         */
        public function seed_default_form_fields(array $fields)
        {
            if ($this->form_fields_table_has_rows() || empty($fields)) {
                return;
            }

            $this->save_form_field_rows_from_api_format($fields);
        }

        /**
         * Migrate legacy Booking Form Labels into the form fields table.
         *
         * @return bool
         */
        public function migrate_form_fields_from_label_settings()
        {
            if (!class_exists('\BookWellAB\BookWellAB_Form_Fields')) {
                return false;
            }

            $labels = $this->get_settings_by_type('labels', []);
            if (!is_array($labels) || empty($labels)) {
                return false;
            }

            $general = $this->get_settings_by_type('general', []);
            if (!is_array($general)) {
                $general = [];
            }

            $fields = \BookWellAB\BookWellAB_Form_Fields::build_fields_from_legacy_labels($labels, $general);
            if ($fields === null) {
                return false;
            }

            return (bool) $this->save_form_field_rows_from_api_format($fields);
        }

        /**
         * One-time backfill of form fields from legacy Booking Form Labels (upgrade safety).
         *
         * @return void
         */
        public function maybe_migrate_form_fields_from_label_settings()
        {
            if (self::get_db_store_option('form_fields_labels_migrated', '') === 'yes') {
                return;
            }

            $this->install_form_fields_table();
            $this->migrate_form_fields_from_label_settings();
            self::update_db_store_option('form_fields_labels_migrated', 'yes');
        }

        /**
         * Migrate legacy JSON form field settings into the dedicated table.
         *
         * @return void
         */
        public function migrate_form_fields_from_settings()
        {
            $saved = $this->get_setting_value('form_fields', 'fields', null);
            if (!is_array($saved) || empty($saved)) {
                return;
            }

            $this->save_form_field_rows_from_api_format($saved);
        }

        /**
         * Ensure the form fields table exists and contains default/migrated data.
         *
         * @return void
         */
        public function ensure_form_fields_table_and_data()
        {
            $this->install_form_fields_table();
            $this->ensure_form_field_width_column();

            if (!$this->form_fields_table_has_rows()) {
                $this->migrate_form_fields_from_settings();

                if (!$this->form_fields_table_has_rows()) {
                    $this->migrate_form_fields_from_label_settings();
                }

                if (!$this->form_fields_table_has_rows() && class_exists('\BookWellAB\BookWellAB_Form_Fields')) {
                    $this->seed_default_form_fields(\BookWellAB\BookWellAB_Form_Fields::get_default_fields());
                }
            }

            $this->maybe_migrate_form_fields_from_label_settings();
        }

        /**
         * Common save helper for spam token rows (insert/update).
         *
         * @param string $token_key_hash MD5 hash of token key.
         * @param string $token_hash     HMAC hash of token payload.
         * @param string $expires_at     UTC datetime Y-m-d H:i:s.
         * @return bool
         */
        public static function save_spam_token_row($token_key_hash, $token_hash, $expires_at)
        {
            global $wpdb;
            $bwdb = new self();
            $spam_tokens_table = esc_sql($bwdb->get_table_name('spam_tokens'));

            $token_key_hash = sanitize_key((string) $token_key_hash);
            $token_hash = sanitize_text_field((string) $token_hash);
            $expires_at = sanitize_text_field((string) $expires_at);

            if ('' === $token_key_hash || '' === $token_hash || '' === $expires_at) {
                return false;
            }

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is plugin-controlled.
            $result = $wpdb->query(
                $wpdb->prepare(
                    "INSERT INTO $spam_tokens_table (bw_token_key_hash, bw_token_hash, bw_expires_at)
                     VALUES (%s, %s, %s)
                     ON DUPLICATE KEY UPDATE bw_token_hash = VALUES(bw_token_hash), bw_expires_at = VALUES(bw_expires_at)",
                    $token_key_hash,
                    $token_hash,
                    $expires_at
                )
            );

            return false !== $result;
        }

        /**
         * Common read helper for spam token row by key hash.
         *
         * @param string $token_key_hash MD5 hash of token key.
         * @return array|null
         */
        public static function get_spam_token_row($token_key_hash)
        {
            global $wpdb;
            $bwdb = new self();
            $spam_tokens_table = esc_sql($bwdb->get_table_name('spam_tokens'));
            $token_key_hash = sanitize_key((string) $token_key_hash);
            if ('' === $token_key_hash) {
                return null;
            }

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is plugin-controlled.
            return $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT bw_token_hash, bw_expires_at FROM $spam_tokens_table WHERE bw_token_key_hash = %s LIMIT 1",
                    $token_key_hash
                ),
                ARRAY_A
            );
        }

        /**
         * Common delete helper for spam token row by key hash.
         *
         * @param string $token_key_hash MD5 hash of token key.
         * @return bool
         */
        public static function delete_spam_token_row($token_key_hash)
        {
            global $wpdb;
            $bwdb = new self();
            $spam_tokens_table = esc_sql($bwdb->get_table_name('spam_tokens'));
            $token_key_hash = sanitize_key((string) $token_key_hash);
            if ('' === $token_key_hash) {
                return false;
            }

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is plugin-controlled.
            $result = $wpdb->delete($spam_tokens_table, ['bw_token_key_hash' => $token_key_hash], ['%s']);
            return false !== $result;
        }

        /**
         * Save spam token using raw token key and ttl.
         *
         * @param string $token_key   Raw token key.
         * @param string $token_hash  HMAC hash payload.
         * @param int    $ttl_seconds Expiry in seconds.
         * @return bool
         */
        public static function insert_spam_token($token_key, $token_hash, $ttl_seconds)
        {
            $token_key = (string) $token_key;
            $ttl_seconds = max(60, (int) $ttl_seconds);
            if ('' === $token_key || '' === (string) $token_hash) {
                return false;
            }

            $expires_at = gmdate('Y-m-d H:i:s', time() + $ttl_seconds);
            return self::save_spam_token_row(md5($token_key), (string) $token_hash, $expires_at);
        }

        /**
         * Read spam token hash if not expired.
         *
         * @param string $token_key Raw token key.
         * @return string
         */
        public static function get_spam_token_hash($token_key)
        {
            $token_key = (string) $token_key;
            if ('' === $token_key) {
                return '';
            }

            $row = self::get_spam_token_row(md5($token_key));
            if (!is_array($row) || empty($row['bw_token_hash']) || empty($row['bw_expires_at'])) {
                return '';
            }

            $expires_at = strtotime((string) $row['bw_expires_at'] . ' UTC');
            if (false === $expires_at || $expires_at < time()) {
                self::delete_spam_token($token_key);
                return '';
            }

            return (string) $row['bw_token_hash'];
        }

        /**
         * Delete spam token by raw token key.
         *
         * @param string $token_key Raw token key.
         * @return bool
         */
        public static function delete_spam_token($token_key)
        {
            $token_key = (string) $token_key;
            if ('' === $token_key) {
                return false;
            }
            return self::delete_spam_token_row(md5($token_key));
        }

        /**
         * Delete all expired spam token rows.
         *
         * @return int Number of deleted rows (0 on error).
         */
        public static function delete_expired_spam_tokens()
        {
            global $wpdb;
            $bwdb = new self();
            $spam_tokens_table = esc_sql($bwdb->get_table_name('spam_tokens'));

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is plugin-controlled.
            $result = $wpdb->query($wpdb->prepare("DELETE FROM $spam_tokens_table WHERE bw_expires_at < %s", current_time('mysql', 1)));
            return (false === $result) ? 0 : (int) $result;
        }

        public function initialize_defaults()
        {
            // This is now handled by more granular install functions.
            // Leaving for backward compatibility if needed, but the logic is moved.
        }

        /**
         * Install default notification data
         *
         * @return void
         */
        public function bookwellab_install_default_notification_data()
        {
            global $wpdb;
            $tbl_bookwellab_notifications = esc_sql($this->get_table_name('notifications'));

            // Get current option-based settings to "consider current data"
            $current_options = get_option(BOOKWELLAB_OPTION_PREFIX . 'notification_settings', []);

            $bookwell_default_notifications_name_arr = array('Appointment Approved', 'Appointment Pending', 'Appointment Canceled');

            // Mapping for current options to new names
            $option_mapping = [
                'Appointment Approved' => 'on_approval',
                'Appointment Pending' => 'on_pending',
                'Appointment Canceled' => 'on_cancellation'
            ];

            $receiver_types = array('customer', 'employee');

            foreach ($bookwell_default_notifications_name_arr as $default_notif_name) {
                foreach ($receiver_types as $receiver_type) {
                    // Check if already exists
                    $check_exists = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $tbl_bookwellab_notifications WHERE bw_notification_name = %s AND bw_notification_receiver_type = %s", $default_notif_name, $receiver_type));
                    if ($check_exists > 0) {
                        continue;
                    }

                    $subject = $default_notif_name;
                    $message = '';

                    // Default content if not in options
                    if ($receiver_type == 'customer') {
                        $default_messages = array(
                            'Appointment Approved' => 'Dear %customer_first_name% %customer_last_name%,<br/>Your appointment has been successfully scheduled.<br/>Thank you for choosing %company_name%. We look forward to serving you.<br/>Best regards,<br/>%company_name%',
                            'Appointment Pending' => 'Dear %customer_first_name% %customer_last_name%,<br/>The %service_name% appointment is scheduled and it is waiting for a confirmation.<br/>Thank you for choosing %company_name%,<br/>%company_name%',
                            'Appointment Canceled' => 'Dear %customer_first_name% %customer_last_name%,<br/>The %service_name% appointment, has been canceled.<br/>Thank you for choosing %company_name%,<br/>%company_name%'
                        );
                        $message = isset($default_messages[$default_notif_name]) ? $default_messages[$default_notif_name] : '';
                    } else {
                        $default_messages = array(
                            'Appointment Approved' => 'Hi staff,<br/>You have one confirmed %service_name% appointment. The appointment is added to your schedule.<br/>Thank you,<br/>%company_name%',
                            'Appointment Pending' => 'Hi staff,<br/>You have new appointment in %service_name%. The appointment is waiting for a confirmation.<br/>Thank you,<br/>%company_name%',
                            'Appointment Canceled' => 'Hi staff,<br/>Your %service_name% appointment, has been canceled.<br/>Thank you,<br/>%company_name%'
                        );
                        $message = isset($default_messages[$default_notif_name]) ? $default_messages[$default_notif_name] : '';
                    }

                    // Override with current options if available
                    if (isset($option_mapping[$default_notif_name])) {
                        $opt_key = $option_mapping[$default_notif_name];
                        $receiver_key = ($receiver_type == 'employee') ? 'staff' : 'customer';
                        if (isset($current_options[$opt_key][$receiver_key])) {
                            $subject = !empty($current_options[$opt_key][$receiver_key]['subject']) ? $current_options[$opt_key][$receiver_key]['subject'] : $subject;
                            $message = !empty($current_options[$opt_key][$receiver_key]['message']) ? $current_options[$opt_key][$receiver_key]['message'] : $message;
                        }
                    }

                    $insert_data = array(
                        'bw_notification_name' => $default_notif_name,
                        'bw_notification_receiver_type' => $receiver_type,
                        'bw_notification_status' => 1,
                        'bw_notification_type' => 'default',
                        'bw_notification_subject' => $subject,
                        'bw_notification_message' => $message,
                        'bw_created_at' => current_time('mysql'),
                    );

                    $wpdb->insert($tbl_bookwellab_notifications, $insert_data);

                    // Optional: Update query details in options as requested
                    $db_data = array(
                        'last_query' => $wpdb->last_query,
                        'last_error' => $wpdb->last_error,
                        'response' => ($wpdb->insert_id > 0) ? 1 : 0
                    );
                    self::update_db_store_option(
                        'notif_install_log_' . sanitize_key($default_notif_name . '_' . $receiver_type),
                        wp_json_encode($db_data)
                    );
                }
            }
            // Explicitly set the option with defaults if it doesn't exist or merge it to ensure structure
            if (class_exists('\\BookWellAB\\BookWellAB_Notifications')) {
                $notif_defaults = \BookWellAB\BookWellAB_Notifications::get_default_settings();
                $current_opt = get_option(BOOKWELLAB_OPTION_PREFIX . 'notification_settings', []);
                
                // Merge defaults with current options
                $merged_opt = array_replace_recursive($notif_defaults, $current_opt);
                
                // Ensure only keys present in defaults are kept (removes 'on_rejection', 'on_share_url' etc.)
                $final_opt = array_intersect_key($merged_opt, $notif_defaults);
                
                update_option(BOOKWELLAB_OPTION_PREFIX . 'notification_settings', $final_opt);
            }
        }

        /**
         * Install default general settings data
         *
         * @return void
         */
        public function bookwellab_install_default_general_settings_data()
        {
            if (class_exists('BookWellAB\BookWellAB_Settings')) {
                $settings_obj = \BookWellAB\BookWellAB_Settings::get_instance();
                $defaults = $settings_obj->get_defaults();

                // Get current site settings for dynamic defaults
                $site_name = get_bloginfo('name');
                $admin_email = get_option('admin_email');
                $wp_date_format = get_option('date_format');
                $wp_time_format = get_option('time_format');
                // Adjust defaults based on site settings
                $defaults['general']['company_name'] = $site_name;
                $defaults['general']['date_format'] = $wp_date_format;
                $defaults['general']['time_format'] = $wp_time_format;
                $defaults['general']['week_starts_on'] = '1';
                $defaults['notifications']['admin_email'] = $admin_email;
                $defaults['notifications']['from_name'] = $site_name;
                $defaults['notifications']['from_email'] = $admin_email;

                foreach ($defaults as $setting_type => $type_settings) {
                    if (!is_array($type_settings)) {
                        continue;
                    }
                    foreach ($type_settings as $setting_name => $setting_value) {
                        // Labels follow the active locale at runtime — do not store on install.
                        if ('labels' === $setting_type) {
                            continue;
                        }
                        if ('payment' === $setting_type && in_array($setting_name, ['offline_label', 'manually_label'], true)) {
                            continue;
                        }

                        $existing_setting = $this->get_setting_value($setting_type, $setting_name, null);
                        if (null === $existing_setting) {
                            $this->update_setting_value($setting_type, $setting_name, $setting_value);
                        }
                    }
                }
            }
        }

        public function migrate_legacy_settings_to_typed_rows()
        {
            $main_settings = $this->get_setting('main_settings', []);
            if (is_array($main_settings)) {
                foreach ($main_settings as $setting_type => $type_settings) {
                    if (!is_array($type_settings)) {
                        continue;
                    }
                    foreach ($type_settings as $setting_name => $setting_value) {
                        $existing_value = $this->get_setting_value($setting_type, $setting_name, null);
                        if (null === $existing_value) {
                            $this->update_setting_value($setting_type, $setting_name, $setting_value);
                        }
                    }
                }
            }

        }

        public function migrate_options_to_table()
        {
            global $wpdb;
            $settings_table = esc_sql($this->get_table_name('settings'));

            // Check if table is empty for initial migration. If not, don't touch anything.
            $count = (int) $wpdb->get_var("SELECT COUNT(*) FROM $settings_table");
            if ($count > 0) {
                return; // Already has data in the table, skip migration to avoid overwriting or updating
            }

            $options_to_migrate = [
                'bookwellab_settings' => 'settings',
                'bookwellab_notification_settings' => 'notification_settings'
            ];

            foreach ($options_to_migrate as $option_name => $setting_name) {
                $value = get_option($option_name);
                if ($value) {
                    if ('settings' === $setting_name && is_array($value)) {
                        foreach ($value as $setting_type => $type_settings) {
                            if (!is_array($type_settings)) {
                                continue;
                            }
                            foreach ($type_settings as $type_setting_name => $type_setting_value) {
                                $existing_value = $this->get_setting_value($setting_type, $type_setting_name, null);
                                if (null === $existing_value) {
                                    $this->update_setting_value($setting_type, $type_setting_name, $type_setting_value);
                                }
                            }
                        }
                        continue;
                    }
                    $this->update_setting($setting_name, $value);
                }
            }
        }

        public function get_settings_by_type($setting_type, $default = [])
        {
            if (!$this->has_typed_settings_support()) {
                return $this->get_legacy_settings_by_type($setting_type, $default);
            }

            global $wpdb;
            $settings_table = esc_sql($this->get_table_name('settings'));
            $setting_type = sanitize_key($setting_type);
            if (empty($setting_type)) {
                return $default;
            }

            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT bw_setting_name, bw_setting_value FROM $settings_table WHERE bw_setting_type = %s",
                    $setting_type
                ),
                ARRAY_A
            );

            if (empty($rows)) {
                return $default;
            }

            $settings = [];
            foreach ($rows as $row) {
                $name = isset($row['bw_setting_name']) ? sanitize_key($row['bw_setting_name']) : '';
                if (empty($name)) {
                    continue;
                }
                $raw_value = isset($row['bw_setting_value']) ? $row['bw_setting_value'] : '';
                $settings[$name] = BookWellAB_Input::decode_json_or_original((string) $raw_value);
            }

            return !empty($settings) ? $settings : $default;
        }

        public function get_setting_value($setting_type, $setting_name, $default = null)
        {
            if (!$this->has_typed_settings_support()) {
                $legacy_settings = $this->get_legacy_settings_by_type($setting_type, []);
                return isset($legacy_settings[$setting_name]) ? $legacy_settings[$setting_name] : $default;
            }

            global $wpdb;
            $settings_table = esc_sql($this->get_table_name('settings'));
            $setting_type = sanitize_key($setting_type);
            $setting_name = sanitize_key($setting_name);

            if (empty($setting_type) || empty($setting_name)) {
                return $default;
            }

            $value = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT bw_setting_value FROM $settings_table WHERE bw_setting_type = %s AND bw_setting_name = %s",
                    $setting_type,
                    $setting_name
                )
            );

            if (null === $value) {
                return $default;
            }

            return BookWellAB_Input::decode_json_or_original((string) $value);
        }

        public function update_settings_by_type($setting_type, $settings)
        {
            if (!is_array($settings)) {
                return false;
            }

            foreach ($settings as $setting_name => $setting_value) {
                $this->update_setting_value($setting_type, $setting_name, $setting_value);
            }

            return true;
        }

        public function update_setting_value($setting_type, $setting_name, $setting_value)
        {
            if (!$this->has_typed_settings_support()) {
                return $this->update_legacy_setting_value($setting_type, $setting_name, $setting_value);
            }

            global $wpdb;
            $settings_table = esc_sql($this->get_table_name('settings'));
            $setting_type = sanitize_key($setting_type);
            $setting_name = sanitize_key($setting_name);
            if (empty($setting_type) || empty($setting_name)) {
                return false;
            }

            $value_to_save = (is_array($setting_value) || is_object($setting_value)) ? wp_json_encode($setting_value) : (string) $setting_value;

            $wpdb->query(
                $wpdb->prepare(
                    "INSERT INTO $settings_table (bw_setting_type, bw_setting_name, bw_setting_value)
                     VALUES (%s, %s, %s)
                     ON DUPLICATE KEY UPDATE bw_setting_value = VALUES(bw_setting_value)",
                    $setting_type,
                    $setting_name,
                    $value_to_save
                )
            );

            return true;
        }

        private function has_typed_settings_support()
        {
            if (null !== $this->supports_typed_settings) {
                return $this->supports_typed_settings;
            }

            global $wpdb;
            $settings_table = esc_sql($this->get_table_name('settings'));
            $column_check = $wpdb->get_results("SHOW COLUMNS FROM `$settings_table` LIKE 'bw_setting_type'");
            $this->supports_typed_settings = !empty($column_check);

            return $this->supports_typed_settings;
        }

        private function get_legacy_settings_by_type($setting_type, $default = [])
        {
            $setting_type = sanitize_key($setting_type);
            if ('download_pdf' === $setting_type) {
                $legacy_pdf = $this->get_setting('pdf_settings', $default);
                return is_array($legacy_pdf) ? $legacy_pdf : $default;
            }

            $legacy_main = $this->get_setting('main_settings', []);
            if (!is_array($legacy_main)) {
                return $default;
            }

            return isset($legacy_main[$setting_type]) && is_array($legacy_main[$setting_type]) ? $legacy_main[$setting_type] : $default;
        }

        private function update_legacy_setting_value($setting_type, $setting_name, $setting_value)
        {
            $setting_type = sanitize_key($setting_type);
            $setting_name = sanitize_key($setting_name);
            if (empty($setting_type) || empty($setting_name)) {
                return false;
            }

            if ('download_pdf' === $setting_type) {
                $legacy_pdf = $this->get_setting('pdf_settings', []);
                if (!is_array($legacy_pdf)) {
                    $legacy_pdf = [];
                }
                $legacy_pdf[$setting_name] = $setting_value;
                return $this->update_setting('pdf_settings', $legacy_pdf);
            }

            $legacy_main = $this->get_setting('main_settings', []);
            if (!is_array($legacy_main)) {
                $legacy_main = [];
            }
            if (!isset($legacy_main[$setting_type]) || !is_array($legacy_main[$setting_type])) {
                $legacy_main[$setting_type] = [];
            }
            $legacy_main[$setting_type][$setting_name] = $setting_value;

            return $this->update_setting('main_settings', $legacy_main);
        }

        public function get_setting($name, $default = [])
        {
            global $wpdb;
            $settings_table = esc_sql($this->get_table_name('settings'));

            $value = $wpdb->get_var($wpdb->prepare(
                "SELECT bw_setting_value FROM $settings_table WHERE bw_setting_name = %s",
                $name
            ));

            if ($value === null) {
                return $default;
            }

            return BookWellAB_Input::decode_json_or_original((string) $value);
        }

        public function update_setting($name, $value)
        {
            global $wpdb;
            $settings_table = esc_sql($this->get_table_name('settings'));

            $value_to_save = is_array($value) || is_object($value) ? json_encode($value) : $value;

            $wpdb->query($wpdb->prepare(
                "INSERT INTO $settings_table (bw_setting_name, bw_setting_value) 
                 VALUES (%s, %s) 
                 ON DUPLICATE KEY UPDATE bw_setting_value = VALUES(bw_setting_value)",
                $name,
                $value_to_save
            ));

            return true;
        }

        public function add_missing_columns()
        {
            global $wpdb;
            $customers_table = esc_sql($this->get_table_name('customers'));
            $appointments_table = esc_sql($this->get_table_name('appointments'));
            $settings_table = esc_sql($this->get_table_name('settings'));

            // Check customers table for bw_phone_code
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$customers_table` LIKE 'bw_phone_code'");
            if (empty($row)) {
                $wpdb->query("ALTER TABLE `$customers_table` ADD `bw_phone_code` VARCHAR(10) DEFAULT NULL AFTER `bw_email` ");
            }

            // Check appointments table for bw_customer_phone_code
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$appointments_table` LIKE 'bw_customer_phone_code'");
            if (empty($row)) {
                $wpdb->query("ALTER TABLE `$appointments_table` ADD `bw_customer_phone_code` VARCHAR(10) DEFAULT NULL AFTER `bw_customer_email` ");
            }

            $row = $wpdb->get_results("SHOW COLUMNS FROM `$customers_table` LIKE 'bw_country_slug'");
            if (empty($row)) {
                $wpdb->query("ALTER TABLE `$customers_table` ADD `bw_country_slug` VARCHAR(10) DEFAULT NULL AFTER `bw_phone` ");
            }

            $this->ensure_customer_panel_database();

            $row = $wpdb->get_results("SHOW COLUMNS FROM `$appointments_table` LIKE 'bw_customer_country_slug'");
            if (empty($row)) {
                $wpdb->query("ALTER TABLE `$appointments_table` ADD `bw_customer_country_slug` VARCHAR(10) DEFAULT NULL AFTER `bw_customer_phone` ");
            }

            $row = $wpdb->get_results("SHOW COLUMNS FROM `$appointments_table` LIKE 'bw_payment_transaction_id'");
            if (empty($row)) {
                $wpdb->query("ALTER TABLE `$appointments_table` ADD `bw_payment_transaction_id` VARCHAR(100) DEFAULT NULL AFTER `bw_payment_status` ");
            }

            // Ensure settings table supports type-based records.
            $settings_type_row = $wpdb->get_results("SHOW COLUMNS FROM `$settings_table` LIKE 'bw_setting_type'");
            if (empty($settings_type_row)) {
                $wpdb->query("ALTER TABLE `$settings_table` ADD `bw_setting_type` VARCHAR(100) NOT NULL DEFAULT 'legacy' AFTER `bw_setting_id`");
            }

            $type_name_index = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM `$settings_table` WHERE Key_name = %s", 'bw_setting_type_name'));
            if (empty($type_name_index)) {
                $wpdb->query("ALTER TABLE `$settings_table` ADD UNIQUE KEY `bw_setting_type_name` (`bw_setting_type`,`bw_setting_name`)");
            }

            $legacy_name_unique_index = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM `$settings_table` WHERE Key_name = %s AND Non_unique = %d", 'bw_setting_name', 0));
            if (!empty($legacy_name_unique_index)) {
                $wpdb->query("ALTER TABLE `$settings_table` DROP INDEX `bw_setting_name`");
                $wpdb->query("ALTER TABLE `$settings_table` ADD KEY `bw_setting_name` (`bw_setting_name`)");
            }

            $this->ensure_service_image_id_column();
            $this->ensure_zoom_support();
            $this->ensure_appointment_indexes();
            $this->ensure_payment_debug_logs_table();
            $this->ensure_manually_payment_settings();
            $this->ensure_appearance_theme_setting();
            $this->ensure_appearance_form_options();
        }

        /**
         * Ensure Zoom integration columns exist on appointments, staffs, and services.
         *
         * @return void
         */
        public function ensure_zoom_support()
        {
            global $wpdb;

            $appointments_table = esc_sql($this->get_table_name('appointments'));
            $staffs_table = esc_sql($this->get_table_name('staffs'));
            $services_table = esc_sql($this->get_table_name('services'));

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$appointments_table` LIKE 'bw_zoom_meeting_id'");
            if (empty($row)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query("ALTER TABLE `$appointments_table` ADD `bw_zoom_meeting_id` VARCHAR(100) DEFAULT NULL AFTER `bw_payment_status`");
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$appointments_table` LIKE 'bw_zoom_host_url'");
            if (empty($row)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query("ALTER TABLE `$appointments_table` ADD `bw_zoom_host_url` TEXT DEFAULT NULL AFTER `bw_zoom_meeting_id`");
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$appointments_table` LIKE 'bw_zoom_join_url'");
            if (empty($row)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query("ALTER TABLE `$appointments_table` ADD `bw_zoom_join_url` TEXT DEFAULT NULL AFTER `bw_zoom_host_url`");
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$staffs_table` LIKE 'bw_zoom_enabled'");
            if (empty($row)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query("ALTER TABLE `$staffs_table` ADD `bw_zoom_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `bw_staff_position_order`");
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$staffs_table` LIKE 'bw_zoom_user_id'");
            if (empty($row)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query("ALTER TABLE `$staffs_table` ADD `bw_zoom_user_id` VARCHAR(100) DEFAULT NULL AFTER `bw_zoom_enabled`");
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$services_table` LIKE 'bw_zoom_enabled'");
            if (empty($row)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query("ALTER TABLE `$services_table` ADD `bw_zoom_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `bw_service_position`");
            }

            $this->ensure_zoom_settings();
        }

        /**
         * Seed Zoom settings rows when missing (fresh install + upgrade).
         *
         * @return void
         */
        public function ensure_zoom_settings()
        {
            $zoom_defaults = [
                'enabled' => 'no',
                'account_id' => '',
                'client_id' => '',
                'client_secret' => '',
                'event_title' => '%service_name% - %customer_first_name% %customer_last_name%',
                /* translators: %staff_name%, %appointment_date%, %appointment_time%: runtime Zoom meeting description placeholders. */
                'event_description' => __('Appointment with %staff_name% on %appointment_date% at %appointment_time%', 'bookwellnow-appointment-booking'),
                'oauth_token' => '',
            ];

            if (class_exists('\\BookWellAB\\BookWellAB_Zoom')) {
                \BookWellAB\BookWellAB_Zoom::get_instance();
            }

            if (class_exists('\\BookWellAB\\BookWellAB_Settings')) {
                $defaults = \BookWellAB\BookWellAB_Settings::get_instance()->get_defaults();
                if (isset($defaults['zoom']) && is_array($defaults['zoom'])) {
                    $zoom_defaults = array_merge($zoom_defaults, $defaults['zoom']);
                }
            }

            foreach ($zoom_defaults as $setting_name => $setting_value) {
                $existing = $this->get_setting_value('zoom', $setting_name, null);
                if (null === $existing) {
                    $this->update_setting_value('zoom', $setting_name, $setting_value);
                }
            }
        }

        /**
         * Seed payment / email debug log settings when missing (fresh install + upgrade).
         *
         * @return void
         */
        public function ensure_debug_log_settings()
        {
            $debug_defaults = [
                'enable_offline_log' => 'no',
                'enable_paypal_log' => 'no',
                'enable_email_log' => 'no',
            ];

            if (class_exists('\\BookWellAB\\BookWellAB_Settings')) {
                $defaults = \BookWellAB\BookWellAB_Settings::get_instance()->get_defaults();
                if (isset($defaults['debug_log']) && is_array($defaults['debug_log'])) {
                    foreach (array_keys($debug_defaults) as $key) {
                        if (array_key_exists($key, $defaults['debug_log'])) {
                            $debug_defaults[$key] = $defaults['debug_log'][$key];
                        }
                    }
                }
            }

            foreach ($debug_defaults as $setting_name => $setting_value) {
                $existing = $this->get_setting_value('debug_log', $setting_name, null);
                if (null === $existing) {
                    $this->update_setting_value('debug_log', $setting_name, $setting_value);
                }
            }
        }

        /**
         * Seed Week Starts On setting when missing (fresh install + upgrade).
         *
         * @return void
         */
        public function ensure_week_starts_on_setting()
        {
            $week_starts_on = '1';

            if (class_exists('\\BookWellAB\\BookWellAB_Settings')) {
                $defaults = \BookWellAB\BookWellAB_Settings::get_instance()->get_defaults();
                if (isset($defaults['general']['week_starts_on'])) {
                    $week_starts_on = (string) $defaults['general']['week_starts_on'];
                }
            }

            $existing = $this->get_setting_value('general', 'week_starts_on', null);
            if (null === $existing) {
                $this->update_setting_value('general', 'week_starts_on', $week_starts_on);
            }
        }

        /**
         * Ensure Zoom schema + settings and payment/email debug log infrastructure.
         * Safe for fresh install and upgrades — only adds missing columns / settings / tables.
         *
         * @return void
         */
        public function ensure_zoom_and_debug_log_support()
        {
            $this->ensure_zoom_support();
            $this->ensure_payment_debug_logs_table();
            $this->ensure_debug_log_settings();
        }

        /**
         * Ensure services table stores WordPress attachment ID for service images.
         *
         * @return void
         */
        public function ensure_service_image_id_column()
        {
            global $wpdb;

            $services_table = esc_sql($this->get_table_name('services'));
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table schema check.
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$services_table` LIKE 'bw_service_image_id'");
            if (empty($row)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Adding missing column on upgrade.
                $wpdb->query("ALTER TABLE `$services_table` ADD `bw_service_image_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER `bw_service_image`");
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned index check.
            $image_id_index = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM `$services_table` WHERE Key_name = %s", 'bw_service_image_id'));
            if (empty($image_id_index)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Adding missing index on upgrade.
                $wpdb->query("ALTER TABLE `$services_table` ADD KEY `bw_service_image_id` (`bw_service_image_id`)");
            }

            // Backfill attachment IDs for services that already have an image URL.
            if (class_exists('\\BookWellAB\\BookWellAB_Services')) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table migration.
                $rows = $wpdb->get_results(
                    "SELECT `bw_service_id`, `bw_service_image` FROM `$services_table`
                    WHERE `bw_service_image_id` = 0
                    AND `bw_service_image` IS NOT NULL
                    AND `bw_service_image` != ''
                    LIMIT 200",
                    ARRAY_A
                );
                if (is_array($rows)) {
                    foreach ($rows as $row) {
                        $service_id = isset($row['bw_service_id']) ? absint($row['bw_service_id']) : 0;
                        $image_url = isset($row['bw_service_image']) ? (string) $row['bw_service_image'] : '';
                        if ($service_id <= 0 || $image_url === '') {
                            continue;
                        }
                        $resolved = \BookWellAB\BookWellAB_Services::resolve_service_image_data($image_url, 0);
                        if ((int) $resolved['attachment_id'] <= 0) {
                            continue;
                        }
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Migrating attachment ID onto plugin-owned row.
                        $wpdb->update(
                            $services_table,
                            [
                                'bw_service_image' => $resolved['url'],
                                'bw_service_image_id' => (int) $resolved['attachment_id'],
                            ],
                            ['bw_service_id' => $service_id],
                            ['%s', '%d'],
                            ['%d']
                        );
                    }
                }
            }
        }

        /**
         * Ensure customer panel database schema (customers ↔ WordPress user link).
         *
         * @return void
         */
        public function ensure_customer_panel_database()
        {
            global $wpdb;

            $customers_table = esc_sql($this->get_table_name('customers'));
            $row = $wpdb->get_results("SHOW COLUMNS FROM `$customers_table` LIKE 'bw_wp_user_id'");
            if (empty($row)) {
                $wpdb->query("ALTER TABLE `$customers_table` ADD `bw_wp_user_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER `bw_country_slug` ");
            }

            $wp_user_index = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM `$customers_table` WHERE Key_name = %s", 'bw_wp_user_id'));
            if (empty($wp_user_index)) {
                $wpdb->query("ALTER TABLE `$customers_table` ADD KEY `bw_wp_user_id` (`bw_wp_user_id`)");
            }
        }

        /**
         * Seed default Customer Panel settings when missing.
         *
         * @return void
         */
        public function ensure_customer_panel_settings()
        {
            if (!class_exists('\\BookWellAB\\BookWellAB_Settings')) {
                return;
            }

            $defaults = \BookWellAB\BookWellAB_Settings::get_instance()->get_defaults();
            if (!isset($defaults['customer_panel']) || !is_array($defaults['customer_panel'])) {
                return;
            }

            foreach ($defaults['customer_panel'] as $setting_name => $setting_value) {
                $existing = $this->get_setting_value('customer_panel', $setting_name, null);
                if (null === $existing) {
                    $this->update_setting_value('customer_panel', $setting_name, $setting_value);
                }
            }
        }

        /**
         * Ensure customer panel database, settings, and WordPress role (fresh install + upgrade).
         *
         * @return void
         */
        public function ensure_customer_panel_support()
        {
            $this->ensure_customer_panel_database();
            $this->ensure_customer_panel_settings();

            if (class_exists('\\BookWellAB\\BookWellAB_Customer_Account')) {
                \BookWellAB\BookWellAB_Customer_Account::ensure_customer_role();
            }
        }

        /**
         * Ensure booking form appearance theme setting exists (1.0.3+).
         *
         * @return void
         */
        public function ensure_appearance_theme_setting()
        {
            $existing = $this->get_setting_value('appearance', 'appearance_theme', null);
            if (null === $existing) {
                $this->update_setting_value('appearance', 'appearance_theme', 'default');
            }
        }

        /**
         * Ensure appearance form type / customized form options exist (3.0.1+).
         * Safe for fresh install and upgrades — only inserts missing keys.
         *
         * @return void
         */
        public function ensure_appearance_form_options()
        {
            $appearance_defaults = [
                'form_type' => 'wizard',
                'hide_description' => false,
                'show_staff_designation' => false,
            ];

            if (class_exists('\\BookWellAB\\BookWellAB_Settings')) {
                $defaults = \BookWellAB\BookWellAB_Settings::get_instance()->get_defaults();
                if (isset($defaults['appearance']) && is_array($defaults['appearance'])) {
                    foreach (array_keys($appearance_defaults) as $key) {
                        if (array_key_exists($key, $defaults['appearance'])) {
                            $appearance_defaults[$key] = $defaults['appearance'][$key];
                        }
                    }
                }
            }

            foreach ($appearance_defaults as $setting_name => $setting_value) {
                $existing = $this->get_setting_value('appearance', $setting_name, null);
                if (null === $existing) {
                    $this->update_setting_value('appearance', $setting_name, $setting_value);
                }
            }

            // General toggles used by catalog / wizard (already in defaults; ensure for older DBs).
            $general_defaults = [
                'hide_categories' => false,
                'hide_staff_step' => false,
            ];
            if (class_exists('\\BookWellAB\\BookWellAB_Settings')) {
                $defaults = \BookWellAB\BookWellAB_Settings::get_instance()->get_defaults();
                if (isset($defaults['general']) && is_array($defaults['general'])) {
                    foreach (array_keys($general_defaults) as $key) {
                        if (array_key_exists($key, $defaults['general'])) {
                            $general_defaults[$key] = $defaults['general'][$key];
                        }
                    }
                }
            }
            foreach ($general_defaults as $setting_name => $setting_value) {
                $existing = $this->get_setting_value('general', $setting_name, null);
                if (null === $existing) {
                    $this->update_setting_value('general', $setting_name, $setting_value);
                }
            }
        }

        /**
         * Ensure admin "Manually" payment method settings exist (1.0.3+).
         *
         * @return void
         */
        public function ensure_manually_payment_settings()
        {
            if (!class_exists('\\BookWellAB\\BookWellAB_Settings')) {
                return;
            }

            $defaults = \BookWellAB\BookWellAB_Settings::get_instance()->get_defaults();
            if (!isset($defaults['payment']) || !is_array($defaults['payment'])) {
                return;
            }

            if (array_key_exists('enable_manually', $defaults['payment'])) {
                $existing = $this->get_setting_value('payment', 'enable_manually', null);
                if (null === $existing) {
                    $this->update_setting_value('payment', 'enable_manually', $defaults['payment']['enable_manually']);
                }
            }
        }

        /**
         * Ensure payment debug logs table exists (safety net for sites that already ran 1.0.3 upgrade).
         *
         * @return void
         */
        public function ensure_payment_debug_logs_table()
        {
            global $wpdb;

            $table_name = $this->get_table_name('payment_debug_logs');
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Table existence check for plugin-owned table.
            $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table_name));
            if ($exists !== $table_name) {
                $this->install_payment_debug_logs_table();
            }
        }

        /**
         * Add appointments bw_customer_id index for customer list stats (1.0.3+).
         *
         * @return void
         */
        public function ensure_appointment_customer_index()
        {
            global $wpdb;

            $appointments_table = esc_sql($this->get_table_name('appointments'));
            $customer_id_index_name = 'bw_customer_id';
            $customer_id_index_name_safe = esc_sql($customer_id_index_name);

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table/index names are plugin-controlled and escaped.
            $customer_id_index_exists = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM `{$appointments_table}` WHERE Key_name = %s", $customer_id_index_name_safe));
            if (empty($customer_id_index_exists)) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL uses plugin-controlled escaped table/index names.
                $wpdb->query("ALTER TABLE `{$appointments_table}` ADD KEY `{$customer_id_index_name_safe}` (`{$customer_id_index_name_safe}`)");
            }
        }

        /**
         * Single composite appointment index + customer created-at for new-customer counts.
         */
        public function ensure_appointment_indexes()
        {
            global $wpdb;

            $appointments_table = esc_sql($this->get_table_name('appointments'));
            $customers_table = esc_sql($this->get_table_name('customers'));
            $composite_name = 'bw_appointment_composite';
            $composite_name_safe = esc_sql($composite_name);
            $customer_id_index_name = 'bw_customer_id';
            $allowed_indexes = [
                $composite_name => true,
                $customer_id_index_name => true,
            ];
            $composite_columns = '(`bw_service_id`,`bw_staff_id`,`bw_customer_id`,`bw_status`,`bw_payment_status`,`bw_date`,`bw_time`)';

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table/index names are plugin-controlled and escaped.
            $composite_exists = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM `{$appointments_table}` WHERE Key_name = %s", $composite_name_safe));
            if (empty($composite_exists)) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL uses plugin-controlled escaped table/index names.
                $wpdb->query("ALTER TABLE `{$appointments_table}` ADD KEY `{$composite_name_safe}` {$composite_columns}");
            }

            $this->ensure_appointment_customer_index();

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is plugin-controlled and escaped.
            $appointment_indexes = $wpdb->get_results("SHOW INDEX FROM `{$appointments_table}`", ARRAY_A);
            $dropped_indexes = [];
            if (is_array($appointment_indexes)) {
                foreach ($appointment_indexes as $index_row) {
                    $key_name = isset($index_row['Key_name']) ? (string) $index_row['Key_name'] : '';
                    if ($key_name === '' || $key_name === 'PRIMARY' || isset($allowed_indexes[$key_name]) || isset($dropped_indexes[$key_name])) {
                        continue;
                    }
                    $dropped_indexes[$key_name] = true;
                    $key_name_safe = esc_sql($key_name);
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL uses plugin-controlled escaped table/index names.
                    $wpdb->query("ALTER TABLE `{$appointments_table}` DROP INDEX `{$key_name_safe}`");
                }
            }

            $customer_index_safe = esc_sql('bw_create_at');
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table/index names are plugin-controlled and escaped.
            $customer_index_exists = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM `{$customers_table}` WHERE Key_name = %s", $customer_index_safe));
            if (empty($customer_index_exists)) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL uses plugin-controlled escaped table/index names.
                $wpdb->query("ALTER TABLE `{$customers_table}` ADD KEY `{$customer_index_safe}` (`bw_create_at`)");
            }
        }

        /**
         * @deprecated 1.0 Use ensure_appointment_indexes().
         */
        public function ensure_dashboard_indexes()
        {
            $this->ensure_appointment_indexes();
        }

        /**
         * Drop all plugin-owned custom database tables.
         *
         * @return void
         */
        public function uninstall_tables()
        {
            global $wpdb;

            $table_types = array(
                'staff_services',
                'staff_working_hours',
                'staff_special_days',
                'appointments',
                'staffs',
                'services',
                'categories',
                'customers',
                'notifications',
                'settings',
                'spam_tokens',
                'payment_debug_logs',
                'form_fields',
            );

            foreach ($table_types as $type) {
                $table_name = esc_sql($this->get_table_name($type));
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dropping plugin-owned tables identified by internal suffix.
                $wpdb->query("DROP TABLE IF EXISTS `{$table_name}`");
            }
        }

        /**
         * Delete plugin options and transients from wp_options.
         *
         * @return void
         */
        public static function delete_plugin_options_and_transients()
        {
            global $wpdb;

            $option_prefixes = array(
                $wpdb->esc_like('bookwellab_') . '%',
                $wpdb->esc_like('bwab_') . '%',
                $wpdb->esc_like('beab_') . '%',
            );

            foreach ($option_prefixes as $like) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Uninstall cleanup of plugin-owned options.
                $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM {$wpdb->options} WHERE option_name LIKE %s",
                        $like
                    )
                );
            }

            $transient_patterns = array(
                $wpdb->esc_like('_transient_bookwellab_') . '%',
                $wpdb->esc_like('_transient_timeout_bookwellab_') . '%',
                $wpdb->esc_like('_transient_bwab_') . '%',
                $wpdb->esc_like('_transient_timeout_bwab_') . '%',
                $wpdb->esc_like('_transient_beab_') . '%',
                $wpdb->esc_like('_transient_timeout_beab_') . '%',
            );

            foreach ($transient_patterns as $like) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Uninstall cleanup of plugin-owned transients.
                $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM {$wpdb->options} WHERE option_name LIKE %s",
                        $like
                    )
                );
            }
        }

        /**
         * Remove custom capabilities added by the plugin.
         *
         * @return void
         */
        public static function remove_plugin_capabilities()
        {
            $capabilities = array(
                'bookwellab_manage_services',
                'bookwellab_manage_staff',
                'bookwellab_manage_customers',
                'bookwellab_manage_appointments',
                'bookwellab_manage_settings',
            );

            $role = get_role('administrator');
            if ($role) {
                foreach ($capabilities as $capability) {
                    $role->remove_cap($capability);
                }
            }
        }

        /**
         * Full uninstall cleanup: tables, options, transients, capabilities.
         *
         * @return void
         */
        public static function uninstall_plugin()
        {
            $database = new self();
            $database->uninstall_tables();
            self::delete_plugin_options_and_transients();
            self::remove_plugin_capabilities();
        }
    }
}
// phpcs:enable
