<?php
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// 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'));

        // 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 notify when a matching opening appears.
     * This is intentionally notification-only; it never creates or holds bookings.
     */
    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';
        $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;
            }

            if ($this->send_waitlist_opening_notice($item, $match)) {
                $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'],
                    ),
                    array('id' => intval($item->id)),
                    array('%d', '%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 send_waitlist_opening_notice($item, $match) {
        $business_name = get_option('yatoon_business_name', get_bloginfo('name'));
        $business_email = get_option('yatoon_business_email', get_option('admin_email'));
        $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('/'));
        }
        $date_label = date_i18n('l, F j, Y', strtotime($match['date']));

        $subject = sprintf('Opening available for %s - %s', $item->service_name ?: 'your service', $business_name);
        $message = sprintf(
            "Hi %s,\n\nGood news - an opening may be available for your waitlist request.\n\nService: %s\nDate: %s\nTime: %s\n\nPlease book online or contact us as soon as possible. This opening is not held automatically and may be taken by another customer.\n\nBook online: %s\n\n%s",
            $item->customer_name,
            $item->service_name ?: 'Selected service',
            $date_label,
            $match['time'],
            $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;
    }
    
    /**
     * 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;
        }
    }
    
    /**
     * 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'])) {
            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,
                );
                
                $existing = $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM $table_name WHERE square_id = %s",
                    $location['id']
                ));
                
                if ($existing) {
                    $wpdb->update($table_name, $data, array('id' => $existing));
                } else {
                    $wpdb->insert($table_name, $data);
                }
                
                $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'])) {
            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'],
                );

                $existing = $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM $table_services WHERE square_id = %s", $object['id']
                ));
                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;
                }
                $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.
                        $existing_opt_row = $wpdb->get_row($wpdb->prepare(
                            "SELECT id, group_name FROM $table_options WHERE square_id = %s AND service_id = %d", $variation['id'], $service_id
                        ));
                        $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);
                        }
                        $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'])) {
            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)
                $existing = $wpdb->get_row($wpdb->prepare(
                    "SELECT id, is_bookable FROM $table_name WHERE square_id = %s", $square_id
                ));
                
                $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;
                }
                
                // ③ 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])) {
                    $table_options  = $wpdb->prefix . 'yatoon_service_options';
                    $new_service_ids = array();

                    foreach ($staff_service_map[$square_id] as $square_variation_id) {
                        // Look up via yatoon_service_options (variation ID -> service_id)
                        $opt_row = $wpdb->get_row($wpdb->prepare(
                            "SELECT service_id FROM $table_options WHERE square_id = %s LIMIT 1",
                            $square_variation_id
                        ));

                        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 = $wpdb->get_row($wpdb->prepare(
                                "SELECT id FROM {$wpdb->prefix}yatoon_services WHERE square_id = %s LIMIT 1",
                                $square_variation_id
                            ));
                            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' ) return array( 'updated' => 0, 'inserted' => 0, 'cancelled' => 0 );
        $result = $this->do_sync_bookings( 'today', '+7 days' );
        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     = ( wp_timezone_string() ?: 'America/Chicago' );
        $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;

            $seg     = $sb['appointment_segments'][0];
            $var_id  = $seg['service_variation_id'] ?? '';
            $sq_team = $seg['team_member_id'] ?? '';
            $start_at_sq = $sb['start_at'] ?? '';
            if ( ! $var_id || ! $start_at_sq ) continue;

            $opt   = $wpdb->get_row( $wpdb->prepare( "SELECT id, service_id, duration_minutes FROM {$table_opts} WHERE square_id=%s LIMIT 1", $var_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $staff = $wpdb->get_row( $wpdb->prepare( "SELECT id FROM {$table_staff} WHERE square_id=%s LIMIT 1", $sq_team ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            if ( ! $opt || ! $staff ) continue;

            $start_dt = new DateTime( $start_at_sq );
            $start_dt->setTimezone( $tz_obj );
            $total_dur = 0;
            foreach ( $sb['appointment_segments'] as $seg_item ) {
                $dur = intval( $seg_item['duration_minutes'] ?? 0 );
                $total_dur += $dur > 0 ? $dur : intval( $opt->duration_minutes );
            }
            if ( $total_dur <= 0 ) $total_dur = intval( $opt->duration_minutes );
            $end_dt = clone $start_dt;
            $end_dt->modify( "+{$total_dur} minutes" );

            $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'] ?? ''))
                : '';

            $wpdb->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'        => $opt->service_id,
                'staff_id'          => $staff->id,
                'start_time'        => $start_dt->format( 'Y-m-d H:i:s' ),
                'end_time'          => $end_dt->format( 'Y-m-d H:i:s' ),
                'status'            => $local_status,
                'square_synced'     => 1,
                'total_price'       => 0,
            ) );
            $new_booking_id = $wpdb->insert_id;

            // Insert booking_options for all segments so service names show in Staff Portal
            if ( $new_booking_id ) {
                $table_booking_opts = $wpdb->prefix . 'yatoon_booking_options';
                foreach ( $sb['appointment_segments'] as $seg_item ) {
                    $seg_var = $seg_item['service_variation_id'] ?? '';
                    if ( ! $seg_var ) continue;
                    $seg_opt = $wpdb->get_row( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                        "SELECT id FROM {$table_opts} WHERE square_id = %s LIMIT 1", $seg_var
                    ) );
                    if ( $seg_opt ) {
                        $wpdb->insert( $table_booking_opts, array( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                            'booking_id' => $new_booking_id,
                            'option_id'  => $seg_opt->id,
                        ) );
                    }
                }
            }
            // -- Send confirmation email for Square-originated bookings ----------
            // Fetch customer email from Square API (not in booking data directly)
            if ( $new_booking_id && 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' => $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( $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", $opt->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", $staff->id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery

                    $booking_data = array(
                        'booking_id'     => $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'     => $start_dt->format( 'Y-m-d H:i:s' ),
                        'end_time'       => $end_dt->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 );
                        }
                    }
                }
            }

            $inserted++;
        }

        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       = new DateTimeZone('America/Indiana/Indianapolis');
        $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       = new DateTimeZone( wp_timezone_string() ?: 'America/Chicago' );
        $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 != ''",
            $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' ) {
                YATOON_Twilio::send_reminder( (array) $booking );
                $sent = true;
            }

            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} ({$booking->customer_email})" );
            }
        }
    }

    /**
     * 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       = new DateTimeZone( wp_timezone_string() ?: 'America/Chicago' );
        $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
