<?php
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
class YATOON_Cron {

    private $square_api;

    public function __construct() {
        $this->square_api = new YATOON_Square_API();

        if (class_exists('YATOON_Activator')) {
            add_filter('cron_schedules', array('YATOON_Activator', 'cron_schedules'));
        }

        // Register custom cron intervals for Square sync
        add_filter('cron_schedules', array( $this, 'add_sync_intervals' ) );

        add_action('yatoon_sync_square_data',           array($this, 'sync_square_data'));
        add_action('yatoon_sync_square_bookings_only',  array($this, 'sync_bookings_only'));
        add_action('yatoon_check_waitlist_availability', array($this, 'check_waitlist_availability'));
        add_action('yatoon_send_reminders',              array($this, 'send_appointment_reminders'));
        add_action('yatoon_send_review_requests',        array($this, 'send_review_requests'));
        add_action('template_redirect',                  array($this, 'handle_waitlist_claim'));

        // Waitlist cron
        if (!wp_next_scheduled('yatoon_check_waitlist_availability')) {
            wp_schedule_event(time() + 300, '15min', 'yatoon_check_waitlist_availability');
        }

        // Reminder cron - runs every 30 min, sends emails/SMS for upcoming appointments
        if (!wp_next_scheduled('yatoon_send_reminders')) {
            wp_schedule_event(time() + 600, 'yatoon_30min', 'yatoon_send_reminders');
        }

        // Review request cron - runs hourly, sends review requests after appointment
        if (!wp_next_scheduled('yatoon_send_review_requests')) {
            wp_schedule_event(time() + 1800, 'hourly', 'yatoon_send_review_requests');
        }

        // Square bookings-only sync (lightweight, runs frequently)
        $this->maybe_reschedule_booking_sync();
    }

    /**
     * Add custom cron interval options.
     */
    public function add_sync_intervals( $schedules ) {
        $schedules['yatoon_5min']  = array( 'interval' => 300,  'display' => 'Every 5 minutes' );
        $schedules['yatoon_10min'] = array( 'interval' => 600,  'display' => 'Every 10 minutes' );
        $schedules['yatoon_15min'] = array( 'interval' => 900,  'display' => 'Every 15 minutes' );
        $schedules['yatoon_30min'] = array( 'interval' => 1800, 'display' => 'Every 30 minutes' );
        $schedules['yatoon_60min'] = array( 'interval' => 3600, 'display' => 'Every hour' );
        return $schedules;
    }

    /**
     * Schedule or reschedule the bookings-only sync based on current settings.
     * Called on construct + when settings are saved.
     */
    public static function maybe_reschedule_booking_sync() {
        $enabled  = get_option( 'yatoon_booking_sync_enabled', '1' ) === '1';
        $interval = get_option( 'yatoon_booking_sync_interval', 'yatoon_15min' );

        $current = wp_next_scheduled( 'yatoon_sync_square_bookings_only' );

        if ( ! $enabled ) {
            // Disabled - clear any scheduled event
            if ( $current ) {
                wp_clear_scheduled_hook( 'yatoon_sync_square_bookings_only' );
            }
            return;
        }

        if ( $current ) {
            // Already scheduled - check if interval changed
            $current_interval = wp_get_schedule( 'yatoon_sync_square_bookings_only' );
            if ( $current_interval === $interval ) {
                return; // No change needed
            }
            // Interval changed - reschedule
            wp_clear_scheduled_hook( 'yatoon_sync_square_bookings_only' );
        }

        wp_schedule_event( time() + 60, $interval, 'yatoon_sync_square_bookings_only' );
    }

    /**
     * Check active waitlist requests and send one expiring offer per opening.
     * Final availability is always revalidated by the normal booking checkout.
     */
    public function check_waitlist_availability( $limit = 25 ) {
        if (get_option('yatoon_waitlist_auto_notify', '1') !== '1') {
            return array('checked' => 0, 'notified' => 0, 'skipped' => 'disabled');
        }

        global $wpdb;
        $table = $wpdb->prefix . 'yatoon_waitlist';
        $wpdb->query( "UPDATE {$table} SET status='expired', claim_status='expired' WHERE status='active' AND claim_status IN ('offered','clicked') AND claim_expires_at IS NOT NULL AND claim_expires_at<UTC_TIMESTAMP()" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $items = $wpdb->get_results($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            "SELECT w.*, s.name AS service_name
             FROM $table w
             LEFT JOIN {$wpdb->prefix}yatoon_services s ON w.service_id = s.id
             WHERE w.status = 'active'
               AND COALESCE(w.notified, 0) = 0
             ORDER BY w.created_at ASC
             LIMIT %d",
            max(1, intval($limit))
        ));

        $checked = 0;
        $notified = 0;
        foreach ((array) $items as $item) {
            ++$checked;
            $match = $this->find_waitlist_opening($item);
            if (!$match) {
                continue;
            }
            $already_offered = (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE id<>%d AND status='active' AND claim_status IN ('offered','clicked') AND claim_expires_at>UTC_TIMESTAMP() AND service_id=%d AND matched_start_at=%s",
                absint( $item->id ), absint( $item->service_id ), (string) $match['start_at']
            ) );
            if ( $already_offered ) { continue; }
            $claim_minutes = min( 120, max( 10, absint( get_option( 'yatoon_waitlist_claim_minutes', 30 ) ) ) );
            $claim_token   = wp_generate_password( 48, false, false );
            $claim_expires = gmdate( 'Y-m-d H:i:s', time() + ( $claim_minutes * MINUTE_IN_SECONDS ) );

            if ($this->send_waitlist_opening_notice($item, $match, $claim_token, $claim_minutes)) {
                $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
                    $table,
                    array(
                        'notified'         => 1,
                        'notified_at'      => current_time('mysql'),
                        'matched_date'     => $match['date'],
                        'matched_time'     => $match['time'],
                        'matched_start_at' => $match['start_at'],
                        'claim_token'      => $claim_token,
                        'claim_expires_at' => $claim_expires,
                        'claim_status'     => 'offered',
                    ),
                    array('id' => intval($item->id)),
                    array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s'),
                    array('%d')
                );
                ++$notified;
            }
        }

        return array('checked' => $checked, 'notified' => $notified);
    }

    private function find_waitlist_opening( $item ) {
        $dates = $this->get_waitlist_candidate_dates($item->preferred_date, !empty($item->flexible_dates));
        foreach ($dates as $date) {
            $slots = YATOON_Business_Hours::generate_slots($date, intval($item->service_id), intval($item->staff_id));
            foreach ((array) $slots as $slot) {
                if ($this->waitlist_time_matches($slot['time'], $item->preferred_time, !empty($item->flexible_times))) {
                    return array(
                        'date'     => $date,
                        'time'     => $slot['display'],
                        'start_at' => $slot['start_at'],
                    );
                }
            }
        }
        return false;
    }

    private function get_waitlist_candidate_dates( $preferred_date, $flexible ) {
        $dates = array();
        $base_ts = strtotime($preferred_date . ' 12:00:00');
        if (!$base_ts) {
            return $dates;
        }

        $offsets = $flexible ? array(0, 1, -1, 2, -2, 3, -3) : array(0);
        $today = current_time('Y-m-d');
        foreach ($offsets as $offset) {
            $date = gmdate('Y-m-d', strtotime(( $offset >= 0 ? '+' : '' ) . $offset . ' days', $base_ts));
            if ($date >= $today && !in_array($date, $dates, true)) {
                $dates[] = $date;
            }
        }
        sort($dates);
        return $dates;
    }

    private function waitlist_time_matches( $time, $preferred_time, $flexible ) {
        if ($flexible || empty($preferred_time) || $preferred_time === 'any') {
            return true;
        }

        $minutes = $this->time_to_minutes($time);
        $windows = array(
            'morning'        => array(10 * 60, 12 * 60),
            'afternoon'      => array(12 * 60, 16 * 60),
            'late_afternoon' => array(16 * 60, 19 * 60),
        );
        if (!isset($windows[ $preferred_time ])) {
            return true;
        }
        return $minutes >= $windows[ $preferred_time ][0] && $minutes < $windows[ $preferred_time ][1];
    }

    private function time_to_minutes( $time ) {
        $parts = explode(':', $time);
        return ( intval($parts[0] ?? 0) * 60 ) + intval($parts[1] ?? 0);
    }

    private function waitlist_booking_url() {
        $booking_url = get_option('yatoon_book_new_url', '');
        if (!$booking_url) {
            $booking_page = get_page_by_path('booking');
            $booking_url = $booking_page ? get_permalink($booking_page->ID) : get_option('yatoon_booking_page_url', home_url('/'));
        }
        return $booking_url;
    }

    private function send_waitlist_opening_notice( $item, $match, $claim_token, $claim_minutes ) {
        $business_name = get_option('yatoon_business_name', get_bloginfo('name'));
        $business_email = get_option('yatoon_business_email', get_option('admin_email'));
        $booking_url = add_query_arg( 'yatoon_waitlist_claim', rawurlencode( $claim_token ), home_url( '/' ) );
        $date_label = yatoon_format_local('l, F j, Y', $match['date']);

        $subject = sprintf('Opening available for %s - %s', $item->service_name ?: 'your service', $business_name);
        $message = sprintf(
            "Hi %s,\n\nGood news - we found an opening for your waitlist request.\n\nService: %s\nDate: %s\nTime: %s\n\nYour private booking link is active for %d minutes. We confirm availability again when you finish booking.\n\nClaim this opening: %s\n\n%s",
            $item->customer_name,
            $item->service_name ?: 'Selected service',
            $date_label,
            $match['time'],
            $claim_minutes,
            $booking_url,
            $business_name
        );

        $headers = array('Content-Type: text/plain; charset=UTF-8');
        $sent_customer = wp_mail($item->customer_email, $subject, $message, $headers);

        $admin_subject = sprintf('Waitlist match found - %s', $item->customer_name);
        $admin_message = sprintf(
            "A waitlist opening was found and the customer was notified.\n\nCustomer: %s\nEmail: %s\nPhone: %s\nService: %s\nOpening: %s at %s\n\nWaitlist ID: %d",
            $item->customer_name,
            $item->customer_email,
            $item->customer_phone,
            $item->service_name ?: 'Selected service',
            $date_label,
            $match['time'],
            intval($item->id)
        );
        wp_mail($business_email, $admin_subject, $admin_message, $headers);

        return (bool) $sent_customer;
    }

    /** Validate an expiring waitlist offer and hand it to the normal booking flow. */
    public function handle_waitlist_claim() {
        if ( empty( $_GET['yatoon_waitlist_claim'] ) ) { return; } // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- The random single-purpose token is the credential.
        $token = sanitize_text_field( wp_unslash( $_GET['yatoon_waitlist_claim'] ) ); // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        if ( strlen( $token ) < 32 ) { wp_die( esc_html__( 'This waitlist offer is invalid.', 'yatoon-booking-system' ), '', array( 'response' => 400 ) ); }
        global $wpdb;
        $table = $wpdb->prefix . 'yatoon_waitlist';
        $item = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE claim_token=%s LIMIT 1", $token ) );
        if ( ! $item || 'active' !== $item->status || ! in_array( $item->claim_status, array( 'offered', 'clicked' ), true ) ) {
            wp_die( esc_html__( 'This waitlist offer is no longer active.', 'yatoon-booking-system' ), '', array( 'response' => 410 ) );
        }
        if ( ! $item->claim_expires_at || strtotime( $item->claim_expires_at . ' UTC' ) < time() ) {
            $wpdb->update( $table, array( 'status' => 'expired', 'claim_status' => 'expired' ), array( 'id' => $item->id ), array( '%s', '%s' ), array( '%d' ) );
            wp_die( esc_html__( 'This waitlist offer has expired. Please join the waitlist again for another opening.', 'yatoon-booking-system' ), '', array( 'response' => 410 ) );
        }
        $wpdb->update( $table, array( 'claim_status' => 'clicked' ), array( 'id' => $item->id ), array( '%s' ), array( '%d' ) );
        $url = add_query_arg( array(
            'yatoon_service'        => absint( $item->service_id ),
            'yatoon_waitlist_offer' => rawurlencode( $token ),
            'yatoon_offer_date'     => sanitize_text_field( $item->matched_date ),
            'yatoon_offer_time'     => sanitize_text_field( $item->matched_time ),
        ), $this->waitlist_booking_url() );
        wp_safe_redirect( $url );
        exit;
    }

    /**
     * Sync Square data to WordPress
     */
    public function sync_square_data( $force = false ) {
        // Respect the admin "Enable automatic sync" toggle (skip check if triggered manually)
        if ( ! $force && get_option('yatoon_auto_sync_enabled', '') !== '1') {
            yatoon_safe_log('SBS Auto Sync: skipped - auto sync is disabled in settings.');
            return;
        }

        $log_entry = array(
            'timestamp' => current_time('mysql'),
            'status' => 'started',
            'details' => array(),
        );

        try {
            // Sync locations
            yatoon_safe_log('SBS Sync: starting sync_locations');
            $locations_result = $this->sync_locations();
            $log_entry['details']['locations'] = $locations_result;
            yatoon_safe_log('SBS Sync: sync_locations done');

            // Sync services
            yatoon_safe_log('SBS Sync: starting sync_services');
            $services_result = $this->sync_services();
            $log_entry['details']['services'] = $services_result;
            yatoon_safe_log('SBS Sync: sync_services done');

            // -- Validate & fix variation IDs --------------------------------------
            try {
                yatoon_safe_log('SBS Sync: starting validate_and_fix_variation_ids');
                $fixed = $this->validate_and_fix_variation_ids();
                if ($fixed > 0) {
                    $log_entry['details']['variation_id_fixes'] = $fixed;
                    yatoon_safe_log("SBS Sync: auto-fixed $fixed variation IDs that were rejected by Appointments API");
                }
                yatoon_safe_log('SBS Sync: validate_and_fix done');
            } catch (\Throwable $e) {
                yatoon_safe_log('SBS Sync: variation ID validation failed: ' . $e->getMessage() . ' in ' . $e->getFile() . ':' . $e->getLine());
            }

            // Sync staff
            yatoon_safe_log('SBS Sync: starting sync_staff');
            $staff_result = $this->sync_staff();
            $log_entry['details']['staff'] = $staff_result;
            yatoon_safe_log('SBS Sync: sync_staff done');

            // Sync recent bookings
            yatoon_safe_log('SBS Sync: starting sync_recent_bookings');
            $bookings_result = $this->sync_recent_bookings();
            $log_entry['details']['bookings'] = $bookings_result;
            yatoon_safe_log('SBS Sync: sync_recent_bookings done');

            $log_entry['status'] = 'completed';
            $this->save_log($log_entry);

            return $log_entry;

        } catch (\Throwable $e) {
            $log_entry['status'] = 'failed';
            $log_entry['error'] = $e->getMessage() . ' in ' . $e->getFile() . ':' . $e->getLine();
            yatoon_safe_log('SBS Sync fatal: ' . $log_entry['error']);
            $this->save_log($log_entry);
            return false;
        }
    }

    /**
     * 4.6.2: batch "does this Square object already exist locally?" lookups.
     *
     * What was wrong: every sync_* method below asked the database once per
     * Square object whether a local row existed, then issued the update or
     * insert. A catalog of 200 services with variations meant 600+ round trips
     * per full sync, which is what made the sync cron time out on shared hosts.
     *
     * This changes only *where the answer comes from*, never the sync
     * semantics: the caller still branches on "row found" vs "not found" and
     * still runs the same $wpdb->update() / $wpdb->insert(). No
     * INSERT ... ON DUPLICATE KEY UPDATE, because square_id is not declared
     * UNIQUE on these tables and a silent behaviour change there would be far
     * more dangerous than the queries it saves.
     *
     * @param string   $table      Fully-qualified table name (built from $wpdb->prefix).
     * @param string[] $square_ids Square object ids to look up.
     * @param string   $columns    Extra columns to select alongside square_id.
     * @return array square_id => row object, first row wins on duplicates.
     */
    private function prefetch_by_square_id( $table, $square_ids, $columns = 'id' ) {
        global $wpdb;

        $ids = array();
        foreach ( (array) $square_ids as $id ) {
            $id = (string) $id;
            if ( '' !== $id ) {
                $ids[ $id ] = true;
            }
        }
        $ids = array_keys( $ids );
        $map = array();
        if ( empty( $ids ) ) {
            return $map;
        }

        // Chunked so a very large catalog never builds one giant statement.
        foreach ( array_chunk( $ids, 200 ) as $chunk ) {
            $placeholders = implode( ',', array_fill( 0, count( $chunk ), '%s' ) );
            $rows         = $wpdb->get_results( $wpdb->prepare(
                "SELECT square_id, {$columns} FROM {$table} WHERE square_id IN ({$placeholders})",
                $chunk
            ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            foreach ( (array) $rows as $row ) {
                $key = (string) $row->square_id;
                if ( ! isset( $map[ $key ] ) ) {
                    $map[ $key ] = $row;
                }
            }
        }

        return $map;
    }

    /**
     * 4.6.2: batch the service-option existence lookup.
     *
     * The per-row query this replaces was
     *   SELECT id, group_name FROM options WHERE square_id = %s AND service_id = %d
     * so the map is keyed on "square_id|service_id" to reproduce it exactly.
     *
     * @param string   $table          Fully-qualified options table name.
     * @param string[] $variation_sqids Square variation ids.
     * @return array "square_id|service_id" => row object.
     */
    private function prefetch_options_by_square_and_service( $table, $variation_sqids ) {
        global $wpdb;

        $ids = array();
        foreach ( (array) $variation_sqids as $id ) {
            $id = (string) $id;
            if ( '' !== $id ) {
                $ids[ $id ] = true;
            }
        }
        $ids = array_keys( $ids );
        $map = array();
        if ( empty( $ids ) ) {
            return $map;
        }

        foreach ( array_chunk( $ids, 200 ) as $chunk ) {
            $placeholders = implode( ',', array_fill( 0, count( $chunk ), '%s' ) );
            $rows         = $wpdb->get_results( $wpdb->prepare(
                "SELECT id, square_id, service_id, group_name FROM {$table} WHERE square_id IN ({$placeholders})",
                $chunk
            ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            foreach ( (array) $rows as $row ) {
                $key = (string) $row->square_id . '|' . (int) $row->service_id;
                if ( ! isset( $map[ $key ] ) ) {
                    $map[ $key ] = $row;
                }
            }
        }

        return $map;
    }

    /**
     * Sync locations
     */
    private function sync_locations() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'yatoon_locations';

        $response = $this->square_api->get_locations();

        if (is_wp_error($response)) {
            throw new Exception('Failed to fetch locations: ' . esc_html( $response->get_error_message() ));
        }

        $count = 0;

        if (isset($response['locations']) && is_array($response['locations'])) {
            // 4.6.2: one IN() lookup instead of a SELECT per location.
            $existing_map = $this->prefetch_by_square_id(
                $table_name,
                wp_list_pluck($response['locations'], 'id')
            );

            foreach ($response['locations'] as $location) {
                $data = array(
                    'square_id' => $location['id'],
                    'name' => $location['name'],
                    'address' => isset($location['address']) ? json_encode($location['address']) : '',
                    'phone' => isset($location['phone_number']) ? $location['phone_number'] : '',
                    'timezone' => isset($location['timezone']) ? $location['timezone'] : '',
                    'is_active' => isset($location['status']) && $location['status'] === 'ACTIVE' ? 1 : 0,
                );

                $square_id = (string) $location['id'];
                $existing  = isset($existing_map[ $square_id ]) ? (int) $existing_map[ $square_id ]->id : 0;

                if ($existing) {
                    $wpdb->update($table_name, $data, array('id' => $existing));
                } else {
                    $wpdb->insert($table_name, $data);
                    // Keep the map authoritative: if Square ever returns the
                    // same id twice in one payload the second pass must update
                    // the row we just inserted, exactly as the per-row SELECT
                    // used to do, instead of inserting a duplicate.
                    if ($wpdb->insert_id) {
                        $existing_map[ $square_id ] = (object) array('id' => (int) $wpdb->insert_id, 'square_id' => $square_id);
                    }
                }

                ++$count;
            }
        }

        return array('synced' => $count);
    }

    /**
     * Sync services
     */
    private function sync_services() {
        global $wpdb;
        $table_services = $wpdb->prefix . 'yatoon_services';
        $table_options   = $wpdb->prefix . 'yatoon_service_options';

        // Use include_related_objects=true to ensure item_variation_data.service_duration is returned
        $response = $this->square_api->get_catalog_items_with_related('ITEM,ITEM_VARIATION,IMAGE,CATEGORY');

        if (is_wp_error($response)) {
            // Fallback to standard catalog list
            $response = $this->square_api->get_catalog_items('ITEM,ITEM_VARIATION,IMAGE,CATEGORY');
            if (is_wp_error($response)) {
                throw new Exception('Failed to fetch catalog items: ' . esc_html( $response->get_error_message() ));
            }
        }

        // ① Build category_id => name map
        $category_map = array();
        // ② Build image_id => url map
        $image_map    = array();

        if (isset($response['objects'])) {
            foreach ($response['objects'] as $obj) {
                if ($obj['type'] === 'CATEGORY' && isset($obj['category_data']['name'])) {
                    $category_map[ $obj['id'] ] = $obj['category_data']['name'];
                }
                if ($obj['type'] === 'IMAGE' && isset($obj['image_data']['url'])) {
                    $image_map[ $obj['id'] ] = $obj['image_data']['url'];
                }
            }
        }

        yatoon_safe_log('Category map: ' . json_encode($category_map));
        yatoon_safe_log('Image map keys: ' . implode(', ', array_keys($image_map)));

        $services_count = 0;
        $options_count  = 0;

        if (isset($response['objects']) && is_array($response['objects'])) {
            // 4.6.2: pre-pass over the payload to collect every id the loop
            // below will look up, so existence checks become two IN() queries
            // instead of two queries per service plus one per variation.
            $wanted_service_sqids   = array();
            $wanted_variation_sqids = array();
            foreach ($response['objects'] as $scan_object) {
                if (!isset($scan_object['type']) || $scan_object['type'] !== 'ITEM') { continue;
                }
                $scan_item = isset($scan_object['item_data']) ? $scan_object['item_data'] : array();
                if (!isset($scan_item['product_type']) || $scan_item['product_type'] !== 'APPOINTMENTS_SERVICE') { continue;
                }
                $wanted_service_sqids[] = $scan_object['id'];
                if (isset($scan_item['variations']) && is_array($scan_item['variations'])) {
                    foreach ($scan_item['variations'] as $scan_variation) {
                        if (isset($scan_variation['id'])) {
                            $wanted_variation_sqids[] = $scan_variation['id'];
                        }
                    }
                }
            }

            $service_map = $this->prefetch_by_square_id($table_services, $wanted_service_sqids);

            // Options are matched on (square_id AND service_id), so this map is
            // keyed on both - the same variation id can legitimately exist
            // under more than one local service. group_name is carried along
            // because the Main-vs-Add-on price-modifier rule below needs it.
            $option_map = $this->prefetch_options_by_square_and_service($table_options, $wanted_variation_sqids);

            foreach ($response['objects'] as $object) {
                if ($object['type'] !== 'ITEM') { continue;
                }

                $item_data   = $object['item_data'];
                $is_bookable = isset($item_data['product_type']) &&
                               $item_data['product_type'] === 'APPOINTMENTS_SERVICE';
                if (!$is_bookable) { continue;
                }

                // Parse category name
                $category_name = '';
                // Newer Square API: categories is an array
                if (isset($item_data['categories']) && is_array($item_data['categories'])) {
                    foreach ($item_data['categories'] as $cat) {
                        $cid = isset($cat['id']) ? $cat['id'] : '';
                        if ($cid && isset($category_map[ $cid ])) {
                            $category_name = $category_map[ $cid ];
                            break;
                        }
                    }
                }
                // Older Square API: category_id is a string
                if (empty($category_name) && isset($item_data['category_id'])) {
                    $cid = $item_data['category_id'];
                    $category_name = isset($category_map[ $cid ]) ? $category_map[ $cid ] : $cid;
                }

                // Parse image URL
                $image_url = '';
                if (isset($item_data['image_ids']) && !empty($item_data['image_ids'])) {
                    $fid = $item_data['image_ids'][0];
                    if (isset($image_map[ $fid ])) { $image_url = $image_map[ $fid ];
                    }
                }
                if (empty($image_url) && isset($item_data['image_id'])) {
                    $fid = $item_data['image_id'];
                    if (isset($image_map[ $fid ])) { $image_url = $image_map[ $fid ];
                    }
                }

                // Default values
                $duration = 60;
                $price    = 0;
                if (isset($item_data['variations']) && !empty($item_data['variations'])) {
                    $vd = $item_data['variations'][0]['item_variation_data'];
                    if (isset($vd['service_duration'])) {       $duration = intval($vd['service_duration'] / 60000);
                    }
                    if (isset($vd['price_money']['amount'])) {  $price    = $vd['price_money']['amount'] / 100;
                    }
                }

                $service_data = array(
                    'square_id'        => $object['id'],
                    'name'             => $item_data['name'],
                    'description'      => isset($item_data['description']) ? $item_data['description'] : '',
                    'category'         => $category_name,
                    'image_url'        => $image_url,
                    'duration_minutes' => $duration,
                    'price'            => $price,
                    'is_active'        => !isset($object['is_deleted']) || !$object['is_deleted'],
                );

                // 4.6.2: existence answered from the prefetched map.
                $service_sqid = (string) $object['id'];
                $existing     = isset($service_map[ $service_sqid ]) ? (int) $service_map[ $service_sqid ]->id : 0;
                if ($existing) {
                    $wpdb->update($table_services, $service_data, array('id' => $existing));
                    $service_id = $existing;
                } else {
                    $wpdb->insert($table_services, $service_data);
                    $service_id = $wpdb->insert_id;
                    // Keep the map authoritative for duplicate ids in one payload.
                    if ($service_id) {
                        $service_map[ $service_sqid ] = (object) array('id' => (int) $service_id, 'square_id' => $service_sqid);
                    }
                }
                ++$services_count;

                // Sync variations (options)
                if (isset($item_data['variations']) && is_array($item_data['variations'])) {
                    foreach ($item_data['variations'] as $variation) {
                        $vd = $variation['item_variation_data'];

                        // Parse duration - service_duration is in milliseconds
                        $var_duration = 0;
                        if (isset($vd['service_duration']) && $vd['service_duration'] > 0) {
                            $var_duration = intval($vd['service_duration'] / 60000);
                        }

                        // Fallback 1: if still 0, try fetching the variation directly from Square
                        // (catalog/list sometimes omits service_duration even with include_related_objects)
                        if ($var_duration <= 0) {
                            $var_resp = $this->square_api->get_catalog_object($variation['id']);
                            if (!is_wp_error($var_resp) && isset($var_resp['object']['item_variation_data']['service_duration'])) {
                                $var_duration = intval($var_resp['object']['item_variation_data']['service_duration'] / 60000);
                            }
                        }

                        // Fallback 2: use the service-level duration
                        if ($var_duration <= 0) {
                            $var_duration = $service_data['duration_minutes'];
                        }

                        // Look up the existing row (if any) so we know whether this is a
                        // Main or Add-on option. Main options replace/modify the base
                        // service price, so their modifier is stored relative to it
                        // (variation_price - service_price). Add-on options STACK on top
                        // of whatever Main option is also selected, so their modifier must
                        // be the actual flat amount to add - NOT relative to the base price,
                        // otherwise combining a discounted Main option (e.g. a Fill-in priced
                        // below the base service) with an Add-on double-subtracts the base
                        // price and can produce a negative total_price at booking time.
                        // 4.6.2: same (square_id, service_id) lookup, served
                        // from the prefetched composite map.
                        $option_key       = (string) $variation['id'] . '|' . (int) $service_id;
                        $existing_opt_row = isset($option_map[ $option_key ]) ? $option_map[ $option_key ] : null;
                        $is_addon_opt = $existing_opt_row && isset($existing_opt_row->group_name) && $existing_opt_row->group_name === 'Add-on';
                        $sq_variation_price = isset($vd['price_money']['amount']) ? ( $vd['price_money']['amount'] / 100 ) : 0;

                        $option_data = array(
                            'service_id'       => $service_id,
                            'square_id'        => $variation['id'],
                            'name'             => $vd['name'],
                            'duration_minutes' => $var_duration,
                            'price_modifier'   => $is_addon_opt
                                                  ? $sq_variation_price
                                                  : ( $sq_variation_price - $service_data['price'] ),
                        );
                        if ($existing_opt_row) {
                            $wpdb->update($table_options, $option_data, array('id' => $existing_opt_row->id));
                        } else {
                            $wpdb->insert($table_options, $option_data);
                            // Keep the map authoritative for duplicate variation
                            // ids inside one payload (was implicit when the
                            // existence check hit the database every time).
                            if ($wpdb->insert_id) {
                                $option_map[ $option_key ] = (object) array(
                                    'id'         => (int) $wpdb->insert_id,
                                    'square_id'  => (string) $variation['id'],
                                    'service_id' => (int) $service_id,
                                    'group_name' => isset($option_data['group_name']) ? $option_data['group_name'] : '',
                                );
                            }
                        }
                        ++$options_count;
                    }
                }
            }
        }

        return array('services' => $services_count, 'options' => $options_count);
    }

    /**
     * Sync staff
     */
    private function sync_staff() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'yatoon_staff';
        $table_staff_services = $wpdb->prefix . 'yatoon_staff_services';

        // ① Get booking profiles (which staff allow online booking + which services)
        $profiles_resp = $this->square_api->get_team_member_booking_profiles();
        $bookable_ids  = array(); // square_id => true
        $staff_service_map = array(); // square_staff_id => [square_service_ids]

        if ( !is_wp_error($profiles_resp) && isset($profiles_resp['team_member_booking_profiles']) ) {
            foreach ($profiles_resp['team_member_booking_profiles'] as $profile) {
                $tid = isset($profile['team_member_id']) ? $profile['team_member_id'] : '?';
                $bookable = !empty($profile['is_bookable']);
                $has_services = isset($profile['service_variation_ids']) && is_array($profile['service_variation_ids']) && count($profile['service_variation_ids']) > 0;
                yatoon_safe_log("SBS profile: $tid bookable=" . ( $bookable?'true':'false' ) . ' service_variation_ids=' . ( $has_services ? implode(',', $profile['service_variation_ids']) : 'none' ));

                if ( $bookable && !empty($tid) ) {
                    $bookable_ids[ $tid ] = true;
                    if ( $has_services ) {
                        $staff_service_map[ $tid ] = $profile['service_variation_ids'];
                    }
                }
            }
        } elseif ( is_wp_error($profiles_resp) ) {
            yatoon_safe_log('SBS: get_team_member_booking_profiles WP_Error: ' . $profiles_resp->get_error_message());
        } else {
            yatoon_safe_log('SBS: get_team_member_booking_profiles response keys: ' . implode(', ', array_keys((array) $profiles_resp)));
        }
        yatoon_safe_log('Bookable team member IDs from Square: ' . implode(', ', array_keys($bookable_ids)));

        // ② Get all ACTIVE staff members
        $response = $this->square_api->get_team_members();

        if (is_wp_error($response)) {
            throw new Exception('Failed to fetch team members: ' . esc_html( $response->get_error_message() ));
        }

        $count = 0;

        if (isset($response['team_members']) && is_array($response['team_members'])) {
            // 4.6.2: one IN() lookup for the whole team instead of one SELECT
            // per member, plus one lookup for every variation id any booking
            // profile referenced (previously up to two SELECTs per variation
            // per staff member).
            $table_options   = $wpdb->prefix . 'yatoon_service_options';
            $table_svc       = $wpdb->prefix . 'yatoon_services';
            $staff_map       = $this->prefetch_by_square_id($table_name, wp_list_pluck($response['team_members'], 'id'), 'id, is_bookable');

            $all_variation_sqids = array();
            foreach ($staff_service_map as $profile_variation_ids) {
                foreach ((array) $profile_variation_ids as $profile_variation_id) {
                    $all_variation_sqids[] = $profile_variation_id;
                }
            }
            // Variation id -> local service_id (via yatoon_service_options),
            // with the same fallback to yatoon_services.square_id the per-row
            // code used. Both maps keep the first row per square_id, matching
            // the old "LIMIT 1" with no ORDER BY.
            $variation_service_map = $this->prefetch_by_square_id($table_options, $all_variation_sqids, 'service_id');
            $service_by_sqid_map   = $this->prefetch_by_square_id($table_svc, $all_variation_sqids, 'id');

            foreach ($response['team_members'] as $member) {
                $square_id  = $member['id'];
                // Determine online booking permission from Square booking profile
                $is_bookable_from_square = isset($bookable_ids[ $square_id ]) ? 1 : 0;

                // Check if record exists locally (preserve admin-set is_bookable if so)
                // 4.6.2: answered from the prefetched map; same columns, same
                // "row or null" contract the code below already handled.
                $existing = isset($staff_map[ (string) $square_id ]) ? $staff_map[ (string) $square_id ] : null;

                $data = array(
                    'square_id'   => $square_id,
                    'name'        => isset($member['given_name']) && isset($member['family_name'])
                                     ? trim($member['given_name'] . ' ' . $member['family_name'])
                                     : ( isset($member['given_name']) ? $member['given_name'] : 'Unknown' ),
                    'email'       => isset($member['email_address']) ? $member['email_address'] : '',
                    'phone'       => isset($member['phone_number'])  ? $member['phone_number']  : '',
                    'is_active'   => isset($member['status']) && $member['status'] === 'ACTIVE' ? 1 : 0,
                    // New record: use Square setting; existing: keep unchanged (admin setting takes priority)
                    'is_bookable' => $existing ? (int) $existing->is_bookable : $is_bookable_from_square,
                );

                if ($existing) {
                    $wpdb->update($table_name, $data, array('id' => $existing->id));
                    $local_staff_id = $existing->id;
                } else {
                    $wpdb->insert($table_name, $data);
                    $local_staff_id = $wpdb->insert_id;
                    // Keep the map authoritative for duplicate ids in one payload.
                    if ($local_staff_id) {
                        $staff_map[ (string) $square_id ] = (object) array(
                            'id'          => (int) $local_staff_id,
                            'square_id'   => (string) $square_id,
                            'is_bookable' => (int) $data['is_bookable'],
                        );
                    }
                }

                // ③ Sync staff-service associations from Square booking profile
                //
                // KEY FIX: Square's booking profile returns service_variation_ids (VARIATION level IDs),
                // NOT catalog ITEM IDs. Our yatoon_services table stores ITEM IDs in square_id.
                // So we must look up via yatoon_service_options (which stores variation IDs) to find
                // the correct local service_id. Without this fix the match always fails and the
                // yatoon_staff_services table stays empty, causing ALL staff to appear eligible for ALL services.
                //
                // IMPORTANT: We only auto-sync if Square provides data for this staff member.
                // If Square returns no service_variation_ids for this member, we PRESERVE whatever
                // the admin has manually configured in the local yatoon_staff_services table.
                if ($local_staff_id && isset($staff_service_map[ $square_id ]) && !empty($staff_service_map[ $square_id ])) {
                    $new_service_ids = array();

                    foreach ($staff_service_map[ $square_id ] as $square_variation_id) {
                        // 4.6.2: both lookups now hit the prefetched maps built
                        // before this loop. Resolution order is unchanged:
                        // yatoon_service_options first, then the
                        // yatoon_services.square_id fallback.
                        $variation_key = (string) $square_variation_id;
                        $opt_row = isset($variation_service_map[ $variation_key ]) ? $variation_service_map[ $variation_key ] : null;

                        if ($opt_row && $opt_row->service_id) {
                            $new_service_ids[] = intval($opt_row->service_id);
                        } else {
                            // Fallback: try matching directly against yatoon_services.square_id
                            // (handles edge case where item ID was stored instead of variation ID)
                            $svc_row = isset($service_by_sqid_map[ $variation_key ]) ? $service_by_sqid_map[ $variation_key ] : null;
                            if ($svc_row) {
                                $new_service_ids[] = intval($svc_row->id);
                            }
                        }
                    }

                    $new_service_ids = array_unique($new_service_ids);

                    if (!empty($new_service_ids)) {
                        // Replace associations only when we successfully resolved at least one service
                        $wpdb->delete($table_staff_services, array('staff_id' => $local_staff_id));
                        foreach ($new_service_ids as $svc_id) {
                            $wpdb->insert($table_staff_services, array(
                                'staff_id'   => $local_staff_id,
                                'service_id' => $svc_id,
                            ), array('%d', '%d'));
                        }
                        yatoon_safe_log("SBS Sync: staff {$data['name']} (local_id=$local_staff_id) -> " . count($new_service_ids) . ' service(s) from Square');
                    } else {
                        // Square returned variation IDs but none matched local services - do NOT clear existing manual config
                        yatoon_safe_log("SBS Sync: staff {$data['name']} - Square returned " . count($staff_service_map[ $square_id ]) . ' variation ID(s) but none matched local services. Preserving existing manual staff-service config.');
                    }
                } elseif ($local_staff_id && !isset($staff_service_map[ $square_id ])) {
                    // Square returned no booking profile data for this staff - preserve manual config
                    yatoon_safe_log("SBS Sync: staff {$data['name']} - no Square booking profile data; preserving existing manual staff-service config.");
                }

                ++$count;
            }
        }

        return array('synced' => $count, 'bookable_from_square' => count($bookable_ids));
    }

    /**
     * Sync recent bookings (called as part of full sync).
     */
    private function sync_recent_bookings() {
        return $this->do_sync_bookings( '-7 days', '+23 days' );
    }

    /**
     * Lightweight bookings-only sync - triggered by the frequent cron.
     * Only syncs today + next 7 days to minimize API calls.
     */
    public function sync_bookings_only() {
        if ( get_option( 'yatoon_booking_sync_enabled', '1' ) !== '1' ) {
            $health = array( 'status' => 'disabled', 'checked_at' => current_time( 'mysql' ) );
            update_option( 'yatoon_square_sync_health', $health, false );
            if ( class_exists( 'YATOON_Health_History' ) ) { YATOON_Health_History::record( 'square_sync', $health );
            }
            return array( 'updated' => 0, 'inserted' => 0, 'cancelled' => 0 );
        }
        $started = microtime( true );
        update_option( 'yatoon_square_sync_health', array( 'status' => 'running', 'started_at' => current_time( 'mysql' ) ), false );
        try {
            $result = $this->do_sync_bookings( 'today', '+7 days' );
        } catch ( Throwable $exception ) {
            $result = new WP_Error( 'square_sync_exception', $exception->getMessage() );
        }
        if ( is_wp_error( $result ) ) {
            $health = array(
                'status' => 'failed', 'checked_at' => current_time( 'mysql' ),
                'duration_ms' => (int) round( ( microtime( true ) - $started ) * 1000 ),
                'error' => sanitize_text_field( $result->get_error_message() ),
            );
            update_option( 'yatoon_square_sync_health', $health, false );
            if ( class_exists( 'YATOON_Health_History' ) ) { YATOON_Health_History::record( 'square_sync', $health );
            }
            return $result;
        }
        $health = array(
            'status' => 'ok', 'checked_at' => current_time( 'mysql' ),
            'duration_ms' => (int) round( ( microtime( true ) - $started ) * 1000 ),
            'updated' => absint( $result['updated'] ?? 0 ), 'inserted' => absint( $result['inserted'] ?? 0 ),
            'cancelled' => absint( $result['cancelled'] ?? 0 ), 'error' => '',
        );
        update_option( 'yatoon_square_sync_health', $health, false );
        if ( class_exists( 'YATOON_Health_History' ) ) { YATOON_Health_History::record( 'square_sync', $health );
        }
        yatoon_safe_log( 'SBS Booking Sync: updated=' . ( $result['updated'] ?? 0 ) . ' inserted=' . ( $result['inserted'] ?? 0 ) );
        return $result;
    }

    /**
     * Core booking sync logic - shared by full sync and lightweight sync.
     *
     * @param string $from  strtotime-compatible start (e.g. '-7 days', 'today')
     * @param string $to    strtotime-compatible end   (e.g. '+23 days', '+7 days')
     */
    private function do_sync_bookings( $from = '-7 days', $to = '+23 days' ) {
        global $wpdb;
        $table        = $wpdb->prefix . 'yatoon_bookings';
        $table_staff  = $wpdb->prefix . 'yatoon_staff';
        $table_opts   = $wpdb->prefix . 'yatoon_service_options';
        $table_svcs   = $wpdb->prefix . 'yatoon_services';
        $timezone     = yatoon_timezone_string();
        $tz_obj       = new DateTimeZone( $timezone );

        $start_at = ( new DateTime( $from, $tz_obj ) )->setTimezone( new DateTimeZone('UTC') )->format( 'Y-m-d\TH:i:s\Z' );
        $end_at   = ( new DateTime( $to,   $tz_obj ) )->setTimezone( new DateTimeZone('UTC') )->format( 'Y-m-d\TH:i:s\Z' );

        $square_bookings = $this->square_api->list_all_bookings( $start_at, $end_at );
        if ( empty( $square_bookings ) ) {
            return array( 'updated' => 0, 'inserted' => 0, 'cancelled' => 0 );
        }

        $status_map = array(
            'ACCEPTED'   => 'confirmed',
            'PENDING'    => 'pending',
            'CANCELLED_BY_CUSTOMER' => 'cancelled',
            'CANCELLED_BY_SELLER'   => 'cancelled',
            'NO_SHOW'    => 'no_show',
        );

        // Index Square bookings by ID
        $sq_map = array();
        foreach ( $square_bookings as $sb ) {
            if ( ! empty( $sb['id'] ) ) { $sq_map[ $sb['id'] ] = $sb;
            }
        }

        // Load existing local bookings that have a square_booking_id in range
        $local_rows = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            "SELECT id, square_booking_id, status, start_time, end_time, staff_id,
                    customer_name, customer_email, customer_phone
             FROM {$table}
             WHERE start_time >= %s AND start_time <= %s AND square_booking_id IS NOT NULL AND square_booking_id != ''",
            ( new DateTime( $from, $tz_obj ) )->format('Y-m-d H:i:s'),
            ( new DateTime( $to,   $tz_obj ) )->format('Y-m-d H:i:s')
        ) );

        $local_sq_ids = array();
        $updated = 0;
        $cancelled = 0;
        $customer_cache = array();

        // Reconcile every multi-segment Square booking once before the per-row
        // status/customer pass. This also catches Design segments added or edited
        // directly in Square without requiring the overall start time to change.
        $reconciled_square_ids = array();
        foreach ( $local_rows as $reconcile_local ) {
            $reconcile_id = (string) $reconcile_local->square_booking_id;
            if ( isset( $reconciled_square_ids[ $reconcile_id ] ) || !isset( $sq_map[ $reconcile_id ] ) ) { continue;
            }
            $reconciled_square_ids[ $reconcile_id ] = true;
            $updated += yatoon_reconcile_square_booking_segments( $sq_map[ $reconcile_id ] );
        }

        foreach ( $local_rows as $local ) {
            $local_sq_ids[] = $local->square_booking_id;
            if ( ! isset( $sq_map[ $local->square_booking_id ] ) ) { continue;
            }

            $sb         = $sq_map[ $local->square_booking_id ];
            $sq_status  = $sb['status'] ?? '';
            $new_status = isset( $status_map[ $sq_status ] ) ? $status_map[ $sq_status ] : null;

            $update = array();

            // Customer identity belongs to the Square Customer record, not
            // customer_note. Repair placeholder/blank local identities on every
            // sync, independently of the confirmation-email setting.
            $sq_customer_id = $sb['customer_id'] ?? '';
            $needs_customer = !$local->customer_name
                || strcasecmp(trim($local->customer_name), 'Square Customer') === 0
                || ( !$local->customer_email && !$local->customer_phone );
            if ($sq_customer_id && $needs_customer) {
                if (!array_key_exists($sq_customer_id, $customer_cache)) {
                    $customer_cache[ $sq_customer_id ] = $this->square_api->get_customer($sq_customer_id);
                }
                $sq_customer = $customer_cache[ $sq_customer_id ];
                if ($sq_customer) {
                    $real_name = trim(( $sq_customer['given_name'] ?? '' ) . ' ' . ( $sq_customer['family_name'] ?? '' ));
                    if ($real_name) { $update['customer_name'] = $real_name;
                    }
                    if (!empty($sq_customer['email_address'])) { $update['customer_email'] = $sq_customer['email_address'];
                    }
                    if (!empty($sq_customer['phone_number'])) { $update['customer_phone'] = $sq_customer['phone_number'];
                    }
                }
            }
            if ( $new_status && $new_status !== $local->status ) {
                $update['status'] = $new_status;
                if ( $new_status === 'cancelled' ) { ++$cancelled;
                }
            }

            if ( ! empty( $update ) ) {
                $update['updated_at'] = current_time( 'mysql' );
                $wpdb->update( $table, $update, array( 'id' => $local->id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                ++$updated;
                if ( ! empty( $update['status'] ) ) {
                    do_action( 'yatoon_booking_status_changed', $local->id, $update['status'] );
                }
            }
        }

        // Insert NEW Square bookings not in local DB
        $inserted = 0;
        foreach ( $square_bookings as $sb ) {
            if ( empty( $sb['id'] ) || in_array( $sb['id'], $local_sq_ids, true ) ) { continue;
            }
            if ( empty( $sb['appointment_segments'] ) ) { continue;
            }

            $sq_status    = $sb['status'] ?? '';
            $local_status = $status_map[ $sq_status ] ?? 'confirmed';
            if ( $local_status === 'cancelled' ) { continue;
            }

            $start_at_sq = $sb['start_at'] ?? '';
            if ( ! $start_at_sq ) { continue;
            }

            $start_dt = new DateTime( $start_at_sq );
            $start_dt->setTimezone( $tz_obj );

            // A single Square booking can bundle several appointment segments -
            // e.g. one guest getting a service from staff A followed by a
            // different service from staff B in the same visit. Group segments
            // into one "row" per staff/main-service run (folding any following
            // Design/Add-on segments into the row they belong to) instead of
            // only ever looking at the first segment - otherwise every segment
            // after the first silently never gets a local booking row, and
            // that staff member's calendar in Yatoon never shows the visit.
            $groups = array();
            $current = null;
            foreach ( $sb['appointment_segments'] as $seg_item ) {
                $seg_var = (string) ( $seg_item['service_variation_id'] ?? '' );
                if ( ! $seg_var ) { continue;
                }
                $seg_opt = $wpdb->get_row( $wpdb->prepare( "SELECT id, service_id, duration_minutes, group_name FROM {$table_opts} WHERE square_id=%s LIMIT 1", $seg_var ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                if ( ! $seg_opt ) { continue;
                }
                $seg_team = (string) ( $seg_item['team_member_id'] ?? '' );
                $seg_dur  = intval( $seg_item['duration_minutes'] ?? 0 );
                if ( $seg_dur <= 0 ) { $seg_dur = intval( $seg_opt->duration_minutes );
                }
                $is_addon = ( (string) $seg_opt->group_name === 'Add-on' );

                $starts_new_group = ( $current === null )
                    || ( $current['team_member_id'] !== $seg_team )
                    || ! $is_addon;

                if ( $starts_new_group ) {
                    if ( $current !== null ) { $groups[] = $current;
                    }
                    $current = array(
                        'team_member_id' => $seg_team,
                        'service_id'     => intval( $seg_opt->service_id ),
                        'duration'       => $seg_dur,
                        'option_ids'     => array( intval( $seg_opt->id ) ),
                    );
                } else {
                    $current['duration'] += $seg_dur;
                    $current['option_ids'][] = intval( $seg_opt->id );
                }
            }
            if ( $current !== null ) { $groups[] = $current;
            }
            if ( empty( $groups ) ) { continue;
            }

            $sq_customer = null;
            $sq_customer_id = $sb['customer_id'] ?? '';
            if ($sq_customer_id) {
                if (!array_key_exists($sq_customer_id, $customer_cache)) {
                    $customer_cache[ $sq_customer_id ] = $this->square_api->get_customer($sq_customer_id);
                }
                $sq_customer = $customer_cache[ $sq_customer_id ];
            }
            $real_customer_name = $sq_customer
                ? trim(( $sq_customer['given_name'] ?? '' ) . ' ' . ( $sq_customer['family_name'] ?? '' ))
                : '';

            $group_booking_id = wp_generate_uuid4();
            $cursor = clone $start_dt;
            $first_new_booking_id = 0;
            $first_group_meta     = null;

            foreach ( $groups as $group_index => $group ) {
                $staff = $wpdb->get_row( $wpdb->prepare( "SELECT id FROM {$table_staff} WHERE square_id=%s LIMIT 1", $group['team_member_id'] ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                if ( ! $staff ) {
                    // Can't place this segment without a matching local staff
                    // record, but still advance the cursor so any following
                    // groups keep the correct start time.
                    $cursor = ( clone $cursor )->modify( '+' . max( 1, intval( $group['duration'] ) ) . ' minutes' );
                    continue;
                }

                $group_start = clone $cursor;
                $group_end   = ( clone $group_start )->modify( '+' . max( 1, intval( $group['duration'] ) ) . ' minutes' );
                $cursor      = clone $group_end;

                yatoon_db_insert( $table, array( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                    'square_booking_id' => $sb['id'],
                    'customer_name'     => $real_customer_name ?: ( $sb['customer_note'] ?: 'Square Customer' ),
                    'customer_email'    => $sq_customer['email_address'] ?? '',
                    'customer_phone'    => $sq_customer['phone_number'] ?? '',
                    'service_id'        => $group['service_id'],
                    'staff_id'          => $staff->id,
                    'start_time'        => $group_start->format( 'Y-m-d H:i:s' ),
                    'end_time'          => $group_end->format( 'Y-m-d H:i:s' ),
                    'status'            => $local_status,
                    'square_synced'     => 1,
                    'total_price'       => 0,
                    'group_booking_id'  => $group_booking_id,
                    'service_order'     => $group_index,
                ) );
                $new_booking_id = $wpdb->insert_id;
                if ( ! $new_booking_id ) { continue;
                }

                // Insert booking_options for this row's segments (main + any
                // folded add-ons) so service/design names show in Staff Portal.
                $table_booking_opts = $wpdb->prefix . 'yatoon_booking_options';
                foreach ( $group['option_ids'] as $option_id ) {
                    $wpdb->insert( $table_booking_opts, array( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                        'booking_id' => $new_booking_id,
                        'option_id'  => $option_id,
                    ) );
                }

                if ( ! $first_new_booking_id ) {
                    $first_new_booking_id = $new_booking_id;
                    $first_group_meta = array(
                        'service_id' => $group['service_id'],
                        'staff_id'   => $staff->id,
                        'start'      => $group_start,
                        'end'        => $group_end,
                    );
                }

                ++$inserted;
            }

            // -- Send one confirmation email for the whole visit, addressed to
            //    the first segment's staff/service/time (Square-originated
            //    bookings only send one email per booking regardless of how
            //    many local rows the visit was split into). -------------------
            if ( $first_new_booking_id && $first_group_meta && get_option( 'yatoon_send_confirmation_email', '1' ) === '1' ) {
                $sq_customer_id = $sb['customer_id'] ?? '';
                $customer_email = '';
                $customer_name  = $sb['customer_note'] ?: '';
                $customer_phone = '';

                if ( $sq_customer_id ) {
                    $sq_customer = $this->square_api->get_customer( $sq_customer_id );
                    if ( $sq_customer ) {
                        $customer_email = $sq_customer['email_address'] ?? '';
                        $customer_phone = $sq_customer['phone_number']  ?? '';
                        if ( empty( $customer_name ) ) {
                            $customer_name = trim(
                                ( $sq_customer['given_name'] ?? '' ) . ' ' .
                                ( $sq_customer['family_name'] ?? '' )
                            );
                        }
                    }
                }

                if ( $customer_email ) {
                    // Update local record with real customer info
                    $wpdb->update( $table, array( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                        'customer_name'  => $customer_name  ?: 'Guest',
                        'customer_email' => $customer_email,
                        'customer_phone' => $customer_phone,
                    ), array( 'id' => $first_new_booking_id ), array( '%s', '%s', '%s' ), array( '%d' ) );

                    // Generate manage token
                    $manage_token = '';
                    if ( class_exists( 'Yatoon_Manage_Booking' ) ) {
                        $manage_token = Yatoon_Manage_Booking::create_token( $first_new_booking_id );
                    }

                    // Build booking data array for notification
                    $svc_row   = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}yatoon_services WHERE id = %d LIMIT 1", $first_group_meta['service_id'] ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                    $staff_row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}yatoon_staff WHERE id = %d LIMIT 1", $first_group_meta['staff_id'] ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery

                    $booking_data = array(
                        'booking_id'     => $first_new_booking_id,
                        'customer_name'  => $customer_name ?: 'Guest',
                        'customer_email' => $customer_email,
                        'customer_phone' => $customer_phone,
                        'service_name'   => $svc_row ? $svc_row->name : '',
                        'staff_name'     => $staff_row ? $staff_row->name : '',
                        'start_time'     => $first_group_meta['start']->format( 'Y-m-d H:i:s' ),
                        'end_time'       => $first_group_meta['end']->format( 'Y-m-d H:i:s' ),
                        'total_price'    => 0,
                        'notes'          => '',
                        'manage_token'   => $manage_token,
                        'selected_options' => array(),
                    );

                    // Fire the same hook used by online bookings
                    do_action( 'yatoon_booking_created', $booking_data );

                    // Direct send if YATOON_Ajax available
                    if ( class_exists( 'YATOON_Notifications' ) ) {
                        $notifier = new YATOON_Notifications();
                        if ( method_exists( $notifier, 'send_customer_confirmation_from_data' ) ) {
                            $notifier->send_customer_confirmation_from_data( $booking_data, $svc_row, $staff_row );
                        }
                    }
                }
            }
        }

        return array( 'updated' => $updated, 'inserted' => $inserted, 'cancelled' => $cancelled );
    }

    /**
     * Validate all variation IDs are accepted by the Square Appointments API
     * If rejected, find the correct ID and auto-fix
     */
    private function validate_and_fix_variation_ids() {
        global $wpdb;
        $table_options  = $wpdb->prefix . 'yatoon_service_options';
        $table_services = $wpdb->prefix . 'yatoon_services';

        // Use tomorrow as test date
        $tz       = yatoon_timezone();
        $tomorrow = new DateTime('tomorrow', $tz);
        $start    = ( clone $tomorrow )->setTime(0,0,0)->format('c');
        $end      = ( clone $tomorrow )->setTime(23,59,59)->format('c');

        // Get all options with their parent service
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $options = $wpdb->get_results(
            "SELECT o.*, s.square_id as item_square_id
             FROM $table_options o
             JOIN $table_services s ON s.id = o.service_id
             WHERE o.square_id != ''
             ORDER BY o.service_id, o.id ASC"
        );

        $fixed = 0;
        $tested_items = array(); // avoid fetching catalog object multiple times per service

        foreach ($options as $opt) {
            // Test this variation against Appointments API
            $resp = $this->square_api->search_availability($opt->square_id, $start, $end, null);

            // If it works (even 0 results), ID is valid
            if (!is_wp_error($resp)) { continue;
            }

            $err = $resp->get_error_message();
            // Only fix "Service variation not found" errors - not other errors (auth, etc.)
            if (strpos($err, 'Service variation not found') === false &&
                strpos($err, 'service_variation_id') === false) { continue;
            }

            yatoon_safe_log("SBS: variation '{$opt->name}' (id={$opt->id}) rejected by Appointments API - trying to find correct ID");

            // Fetch the catalog item to get all variation IDs
            $item_sq_id = $opt->item_square_id;
            if (isset($tested_items[ $item_sq_id ])) {
                $all_variation_ids = $tested_items[ $item_sq_id ];
            } else {
                $cat = $this->square_api->get_catalog_object($item_sq_id);
                $all_variation_ids = array();
                if (!is_wp_error($cat) && isset($cat['object']['item_data']['variations'])) {
                    foreach ($cat['object']['item_data']['variations'] as $v) {
                        $all_variation_ids[] = array(
                            'id'   => $v['id'],
                            'name' => isset($v['item_variation_data']['name']) ? $v['item_variation_data']['name'] : '',
                        );
                    }
                }
                $tested_items[ $item_sq_id ] = $all_variation_ids;
            }

            // Try each variation ID - find one that Appointments API accepts
            foreach ($all_variation_ids as $candidate) {
                if ($candidate['id'] === $opt->square_id) { continue; // already tried
                }
                $test = $this->square_api->search_availability($candidate['id'], $start, $end, null);
                if (!is_wp_error($test)) {
                    // This ID works! Update the database
                    $wpdb->update(
                        $table_options,
                        array('square_id' => $candidate['id']),
                        array('id' => $opt->id),
                        array('%s'), array('%d')
                    );
                    yatoon_safe_log("SBS: fixed variation '{$opt->name}' - replaced {$opt->square_id} with {$candidate['id']}");
                    ++$fixed;
                    break;
                }
            }
        }

        return $fixed;
    }

    /**
     * Send appointment reminders (email + SMS) for upcoming bookings.
     * Runs every 30 minutes via cron.
     */
    public function send_appointment_reminders() {
        if ( get_option( 'yatoon_reminder_enabled', '0' ) !== '1' ) { return;
        }

        global $wpdb;
        $table    = $wpdb->prefix . 'yatoon_bookings';
        $tz       = yatoon_timezone();
        $hours    = max( 1, intval( get_option( 'yatoon_reminder_hours_before', 24 ) ) );
        $now      = new DateTime( 'now', $tz );
        $window_s = ( clone $now )->modify( "+{$hours} hours -30 minutes" )->format( 'Y-m-d H:i:s' );
        $window_e = ( clone $now )->modify( "+{$hours} hours +30 minutes" )->format( 'Y-m-d H:i:s' );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $bookings = $wpdb->get_results( $wpdb->prepare(
            "SELECT b.*, s.name AS service_name, s.duration_minutes,
                    st.name AS staff_name
             FROM {$table} b
             LEFT JOIN {$wpdb->prefix}yatoon_services s ON s.id = b.service_id
             LEFT JOIN {$wpdb->prefix}yatoon_staff    st ON st.id = b.staff_id
             WHERE b.start_time BETWEEN %s AND %s
               AND b.status NOT IN ('cancelled','CANCELLED_BY_CUSTOMER','CANCELLED_BY_SELLER','no_show')
               AND b.reminder_sent = 0
               AND (b.customer_email != '' OR b.customer_phone != '')",
            $window_s, $window_e
        ) );

        foreach ( (array) $bookings as $booking ) {
            $sent = false;

            // Email reminder
            if ( class_exists( 'YATOON_Notifications' ) ) {
                $notifier = new YATOON_Notifications();
                if ( method_exists( $notifier, 'send_reminder_email' ) ) {
                    $sent = $notifier->send_reminder_email( (array) $booking );
                }
            }

            // SMS reminder
            if ( class_exists( 'YATOON_Twilio' ) && get_option( 'yatoon_twilio_reminder_sms', '0' ) === '1' ) {
                $sent = YATOON_Twilio::send_reminder( (array) $booking ) || $sent;
}

            // WhatsApp reminder via an approved Meta template.
            if ( class_exists( 'YATOON_WhatsApp' ) && get_option( 'yatoon_whatsapp_reminder_enabled', '0' ) === '1' ) {
                $sent = YATOON_WhatsApp::send_reminder( (array) $booking ) || $sent;
            }
            if ( $sent ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->update( $table, array( 'reminder_sent' => 1 ), array( 'id' => $booking->id ), array( '%d' ), array( '%d' ) );
                yatoon_safe_log( "Yatoon Reminder: sent for booking #{$booking->id}" );
            }
        }
    }

    /**
     * Send review request emails after completed appointments.
     * Runs hourly via cron.
     */
    public function send_review_requests() {
        if ( get_option( 'yatoon_review_enabled', '0' ) !== '1' ) { return;
        }

        global $wpdb;
        $table    = $wpdb->prefix . 'yatoon_bookings';
        $tz       = yatoon_timezone();
        $hours    = max( 1, intval( get_option( 'yatoon_review_hours_after', 3 ) ) );
        $now      = new DateTime( 'now', $tz );
        $window_s = ( clone $now )->modify( "-{$hours} hours -60 minutes" )->format( 'Y-m-d H:i:s' );
        $window_e = ( clone $now )->modify( "-{$hours} hours" )->format( 'Y-m-d H:i:s' );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $bookings = $wpdb->get_results( $wpdb->prepare(
            "SELECT b.*, s.name AS service_name, st.name AS staff_name
             FROM {$table} b
             LEFT JOIN {$wpdb->prefix}yatoon_services s ON s.id = b.service_id
             LEFT JOIN {$wpdb->prefix}yatoon_staff    st ON st.id = b.staff_id
             WHERE b.end_time BETWEEN %s AND %s
               AND b.status IN ('confirmed','ACCEPTED')
               AND b.review_requested = 0
               AND b.customer_email != ''",
            $window_s, $window_e
        ) );

        foreach ( (array) $bookings as $booking ) {
            do_action( 'yatoon_send_review_request', $booking->id );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update( $table, array( 'review_requested' => 1 ), array( 'id' => $booking->id ), array( '%d' ), array( '%d' ) );
            yatoon_safe_log( "Yatoon Review Request: sent for booking #{$booking->id}" );
        }
    }

    /**
     * Save sync log
     */
    private function save_log( $log_entry ) {
        $logs = get_option('yatoon_sync_logs', array());
        if (!is_array($logs)) {
            $logs = array(); // reset if corrupted/non-array value stored
        }
        $logs[] = $log_entry;

        // Keep only the most recent 100 log entries
        $logs = array_slice($logs, -100);

        update_option('yatoon_sync_logs', $logs);
    }
}
// phpcs:enable
