<?php
namespace BookWellAB;

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

if (!class_exists('BookWellAB_Booking_Helper')) {
    class BookWellAB_Booking_Helper
    {
        /** @var array<string, mixed> */
        private static $slot_booking_context = array();

        /**
         * @param array<string, mixed> $context Booking context for slot generation.
         * @return void
         */
        public static function set_slot_booking_context($context)
        {
            self::$slot_booking_context = is_array($context) ? $context : array();
        }

        /**
         * @return array<string, mixed>
         */
        public static function get_slot_booking_context()
        {
            return self::$slot_booking_context;
        }

        /**
         * Build a site-timezone timestamp from date + time input.
         *
         * @param string $date Date in Y-m-d.
         * @param string $time_raw Time in flexible format.
         * @return int Unix timestamp, or 0 when invalid.
         */
        /**
         * Current Unix timestamp in the WordPress site timezone.
         * Do not use current_time( 'timestamp' ) — it is offset-adjusted and must not be compared to DateTimeImmutable timestamps.
         *
         * @return int
         */
        private static function get_site_now_timestamp()
        {
            return (new \DateTimeImmutable('now', wp_timezone()))->getTimestamp();
        }

        public static function to_site_timestamp($date, $time_raw)
        {
            $date = trim((string) $date);
            if ($date === '' || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                return 0;
            }

            $time_hm = self::normalize_time_to_hm($time_raw);
            if ($time_hm === '') {
                return 0;
            }

            $dt = \DateTimeImmutable::createFromFormat('Y-m-d H:i', $date . ' ' . $time_hm, wp_timezone());
            if (!$dt instanceof \DateTimeImmutable) {
                return 0;
            }

            return $dt->getTimestamp();
        }

        /**
         * Normalize a time string to 24-hour H:i format.
         *
         * @param string $time_raw Raw time string.
         * @return string Normalized time (H:i) or empty string when invalid.
         */
        private static function normalize_time_to_hm($time_raw)
        {
            $time_raw = trim((string) $time_raw);
            if ($time_raw === '') {
                return '';
            }

            if (preg_match('/^\d{1,2}:\d{2}$/', $time_raw)) {
                list($hour, $minute) = array_map('intval', explode(':', $time_raw));
                if ($hour >= 0 && $hour <= 23 && $minute >= 0 && $minute <= 59) {
                    return sprintf('%02d:%02d', $hour, $minute);
                }
            }

            if (preg_match('/^\d{1,2}:\d{2}:\d{2}$/', $time_raw)) {
                list($hour, $minute) = array_map('intval', explode(':', $time_raw));
                if ($hour >= 0 && $hour <= 23 && $minute >= 0 && $minute <= 59) {
                    return sprintf('%02d:%02d', $hour, $minute);
                }
            }

            // Only parse 12-hour strings when am/pm is present (frontend booking uses 24-hour H:i).
            if (preg_match('/\b(am|pm)\b/i', $time_raw)) {
                $time_lower = strtolower($time_raw);
                $twelve_hour_formats = ['g:i a', 'h:i a'];
                foreach ($twelve_hour_formats as $format) {
                    $dt = \DateTimeImmutable::createFromFormat($format, $time_lower, wp_timezone());
            if ($dt instanceof \DateTimeImmutable) {
                return $dt->format('H:i');
                    }
                }
            }

            return '';
        }

        /**
         * Convert a time string to minutes from midnight (site 24-hour time).
         *
         * @param string $time_raw Raw time string.
         * @return int Minutes from midnight, or 0 when invalid.
         */
        public static function time_to_minutes($time_raw)
        {
            $time_hm = self::normalize_time_to_hm($time_raw);
            if ($time_hm === '') {
                return 0;
            }

            list($hour, $minute) = array_map('intval', explode(':', $time_hm));
            return ($hour * 60) + $minute;
        }

        /**
         * Resolve the special-day rule for a staff member on a given date.
         * When multiple rules overlap, Day Off takes precedence over Custom Shift.
         *
         * @param int    $staff_id Staff ID.
         * @param string $date     Date in Y-m-d format.
         * @return array|null Special day row or null when none applies.
         */
        public static function get_staff_special_day_for_date($staff_id, $date)
        {
            global $wpdb;
            $bwdb = new BookWellAB_Database();
            $special_days_table = esc_sql($bwdb->get_table_name('staff_special_days'));
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
            return $wpdb->get_row($wpdb->prepare(
                "SELECT * FROM $special_days_table
                 WHERE bw_staff_id = %d AND %s BETWEEN bw_start_date AND bw_end_date
                 ORDER BY bw_is_off DESC, bw_start_date ASC
                 LIMIT 1",
                $staff_id,
                $date
            ), ARRAY_A);
        }

        /**
         * Get available time slots for a specific staff member and service on a given date.
         * 
         * @param string $date Date in Y-m-d format
         * @param int $staff_id Staff ID
         * @param int $service_id Service ID
         * @return array List of available time slots (H:i format)
         */
        public static function get_staff_available_slots($date, $staff_id, $service_id, $exclude_past_slots = false, $exclude_appointment_id = 0)
        {
            if ($exclude_past_slots) {
                $site_today = wp_date('Y-m-d', null, wp_timezone());
                if ($date < $site_today) {
                    return array();
                }
            }

            $exclude_appointment_id = absint($exclude_appointment_id);

            global $wpdb;
            $bwdb = new BookWellAB_Database();

            // 1. Get Service Duration
            $staff_serv_table = esc_sql($bwdb->get_table_name('staff_services'));
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
            $service_duration = $wpdb->get_row($wpdb->prepare(
                "SELECT bw_service_duration_val, bw_service_duration_unit FROM $staff_serv_table WHERE bw_staff_id = %d AND bw_service_id = %d",
                $staff_id,
                $service_id
            ), ARRAY_A);

            if (!$service_duration) {
                return [];
            }

            $duration_minutes = intval($service_duration['bw_service_duration_val']);
            if ($service_duration['bw_service_duration_unit'] == 'h') {
                $duration_minutes *= 60;
            }

            // Fallback for legacy/invalid staff-service rows with empty duration.
            if ($duration_minutes <= 0) {
                $services_table = esc_sql($bwdb->get_table_name('services'));
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
                $service_defaults = $wpdb->get_row($wpdb->prepare(
                    "SELECT bw_service_duration_val, bw_service_duration_unit FROM $services_table WHERE bw_service_id = %d",
                    $service_id
                ), ARRAY_A);

                if ($service_defaults) {
                    $duration_minutes = intval($service_defaults['bw_service_duration_val']);
                    if (isset($service_defaults['bw_service_duration_unit']) && $service_defaults['bw_service_duration_unit'] === 'h') {
                        $duration_minutes *= 60;
                    }
                }
            }

            if ($duration_minutes <= 0) {
                $duration_minutes = 30;
            }

            $duration_minutes = (int) apply_filters(
                'bookwellab_booking_duration_minutes',
                $duration_minutes,
                $service_id,
                $staff_id,
                array_merge(
                    self::get_slot_booking_context(),
                    array('date' => $date)
                )
            );

            // Get Settings
            $settings = BookWellAB_Settings::get_settings();
            $slot_step_setting = isset($settings['general']['slot_step']) ? $settings['general']['slot_step'] : '15';

            $step_minutes = 15; // Default
            if ($slot_step_setting === 'service_duration') {
                $step_minutes = $duration_minutes;
            } else {
                $step_minutes = intval($slot_step_setting);
                if ($step_minutes <= 0) $step_minutes = 15;
            }

            // 2. Check for Staff Special Day for the requested date
            $special_day = self::get_staff_special_day_for_date($staff_id, $date);

            $breaks = [];
            if ($special_day) {
                if ($special_day['bw_is_off']) {
                    return []; // Special Day Off
                }
                $start_time_str = $special_day['bw_start_time'];
                $end_time_str = $special_day['bw_end_time'];

                if (!empty($special_day['bw_breaks'])) {
                    $breaks_data = BookWellAB_Input::sanitize_break_hours_list($special_day['bw_breaks']);
                    if (!empty($breaks_data)) {
                        foreach ($breaks_data as $brk) {
                            if (isset($brk['start']) && isset($brk['end'])) {
                                $break_start = self::to_site_timestamp($date, $brk['start']);
                                $break_end = self::to_site_timestamp($date, $brk['end']);
                                if ($break_start <= 0 || $break_end <= 0) {
                                    continue;
                                }
                                $breaks[] = [
                                    'start' => $break_start,
                                    'end' => $break_end
                                ];
                            }
                        }
                    }
                }
            } else {
                // 2b. Regular Working Hours
                // Keep weekday lookup locale-agnostic: DB stores canonical English names.
                $weekday_dt = new \DateTimeImmutable($date, wp_timezone());
                $day_name = strtolower($weekday_dt->format('l'));
                $working_hours_table = esc_sql($bwdb->get_table_name('staff_working_hours'));
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
                $working_hours = $wpdb->get_row($wpdb->prepare(
                    "SELECT * FROM $working_hours_table WHERE bw_staff_id = %d AND LOWER(bw_day_name) = %s",
                    $staff_id,
                    $day_name
                ), ARRAY_A);

                if (!$working_hours || $working_hours['bw_is_off']) {
                    return []; // Regular Day off
                }
                $start_time_str = $working_hours['bw_start_time'];
                $end_time_str = $working_hours['bw_end_time'];

                if (!empty($working_hours['bw_breaks'])) {
                    $breaks_data = BookWellAB_Input::sanitize_break_hours_list($working_hours['bw_breaks']);
                    if (!empty($breaks_data)) {
                        foreach ($breaks_data as $brk) {
                            if (isset($brk['start']) && isset($brk['end'])) {
                                $break_start = self::to_site_timestamp($date, $brk['start']);
                                $break_end = self::to_site_timestamp($date, $brk['end']);
                                if ($break_start <= 0 || $break_end <= 0) {
                                    continue;
                                }
                                $breaks[] = [
                                    'start' => $break_start,
                                    'end' => $break_end
                                ];
                            }
                        }
                    }
                }
            }

            // 3. Generate Slots
            $slots = [];
            $start_time = self::to_site_timestamp($date, $start_time_str);
            $end_time = self::to_site_timestamp($date, $end_time_str);
            if ($start_time <= 0 || $end_time <= 0 || $start_time >= $end_time) {
                return [];
            }

            // Existing Appointments
            $appointments_table = esc_sql($bwdb->get_table_name('appointments'));
            if ($exclude_appointment_id > 0) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
            $booked_slots = $wpdb->get_results($wpdb->prepare(
                    "SELECT bw_appointment_id, bw_date, bw_time, bw_duration FROM $appointments_table
                     WHERE bw_staff_id = %d AND bw_date = %s AND bw_appointment_id != %d
                       AND (
                            bw_status IS NULL
                            OR LOWER(TRIM(bw_status)) NOT IN ('cancelled', 'canceled')
                       )",
                    $staff_id,
                    $date,
                    $exclude_appointment_id
                ), ARRAY_A);
            } else {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
                $booked_slots = $wpdb->get_results($wpdb->prepare(
                    "SELECT bw_appointment_id, bw_date, bw_time, bw_duration FROM $appointments_table
                 WHERE bw_staff_id = %d AND bw_date = %s
                   AND (
                        bw_status IS NULL
                        OR LOWER(TRIM(bw_status)) NOT IN ('cancelled', 'canceled')
                   )",
                $staff_id,
                $date
            ), ARRAY_A);
            }

            $booked_intervals = [];
            if ($booked_slots) {
                foreach ($booked_slots as $bs) {
                    if ($exclude_appointment_id > 0 && isset($bs['bw_appointment_id']) && absint($bs['bw_appointment_id']) === $exclude_appointment_id) {
                        continue;
                    }

                    $booked_date_raw = isset($bs['bw_date']) ? trim((string) $bs['bw_date']) : '';
                    if ($booked_date_raw === '') {
                        continue;
                    }
                    $booked_date_norm = '';
                    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $booked_date_raw)) {
                        $booked_date_norm = $booked_date_raw;
                    } else {
                        $booked_date_ts = strtotime($booked_date_raw);
                        if ($booked_date_ts !== false) {
                            $booked_date_norm = wp_date('Y-m-d', $booked_date_ts, wp_timezone());
                        }
                    }
                    if ($booked_date_norm !== $date) {
                        continue;
                    }

                    $booked_time_hm = self::normalize_time_to_hm(isset($bs['bw_time']) ? $bs['bw_time'] : '');
                    if ($booked_time_hm === '') {
                        continue;
                    }
                    $bs_start = self::to_site_timestamp($date, $booked_time_hm);
                    if ($bs_start <= 0) {
                        continue;
                    }
                    $booked_duration = isset($bs['bw_duration']) ? intval($bs['bw_duration']) : 0;
                    if ($booked_duration <= 0) {
                        // Legacy safety: treat malformed/empty durations as a real occupied block.
                        $booked_duration = 30;
                    }
                    $bs_end = $bs_start + ($booked_duration * 60);
                    $booked_intervals[] = [
                        'start' => $bs_start, 
                        'end' => $bs_end
                    ];
                }
            }

            /**
             * Filter booked/busy intervals used when generating staff slots for a day.
             *
             * @param array  $booked_intervals Intervals with start/end unix timestamps.
             * @param string $date             Y-m-d
             * @param int    $staff_id         Staff ID.
             * @param int    $service_id       Service ID.
             * @param array  $context          Extra context (mode, exclude_appointment_id, …).
             */
            $booked_intervals = apply_filters(
                'bookwellab_staff_booked_intervals',
                $booked_intervals,
                $date,
                (int) $staff_id,
                (int) $service_id,
                [
                    'mode' => 'day',
                    'exclude_appointment_id' => $exclude_appointment_id,
                ]
            );
            if (!is_array($booked_intervals)) {
                $booked_intervals = [];
            }

            $current_site_time = $exclude_past_slots ? self::get_site_now_timestamp() : 0;
            
            while ($start_time + ($duration_minutes * 60) <= $end_time) {
                $slot_end_time = $start_time + ($duration_minutes * 60);

                if ($exclude_past_slots && $start_time < $current_site_time) {
                    $start_time += $step_minutes * 60;
                    continue;
                }

                // Check against breaks
                $is_break = false;
                foreach ($breaks as $brk) {
                    if ($start_time < $brk['end'] && $slot_end_time > $brk['start']) {
                        $is_break = true;
                        break;
                    }
                }

                // Check against booked slots (including buffer)
                $is_booked = false;
                if (!$is_break) {
                    foreach ($booked_intervals as $booked) {
                        if ($start_time < $booked['end'] && $slot_end_time > $booked['start']) {
                            $is_booked = true;
                            break;
                        }
                    }
                }

                if (!$is_break && !$is_booked) {
                    $slots[] = wp_date('H:i', $start_time, wp_timezone());
                }

                $start_time += $step_minutes * 60;
            }

            return $slots;
        }

        /**
         * Check if a specific time slot is available for a staff member.
         * 
         * @param string $date Date in Y-m-d format
         * @param string $time Time in H:i format
         * @param int $staff_id Staff ID
         * @param int $service_id Service ID
         * @return bool
         */
        public static function is_slot_available($date, $time, $staff_id, $service_id, $exclude_past_slots = false, $exclude_appointment_id = 0)
        {
            $available_slots = self::get_staff_available_slots($date, $staff_id, $service_id, $exclude_past_slots, $exclude_appointment_id);
            $formatted_time = self::normalize_time_to_hm($time);
            if ($formatted_time === '') {
                return false;
            }

            $normalized_available_slots = array_map([__CLASS__, 'normalize_time_to_hm'], $available_slots);
            $is_available = in_array($formatted_time, $normalized_available_slots, true);

            return apply_filters(
                'bookwellab_is_slot_available',
                $is_available,
                $date,
                $time,
                $staff_id,
                $service_id,
                $exclude_past_slots,
                $exclude_appointment_id
            );
        }

        /**
         * @param int $staff_id Staff ID.
         * @param int $service_id Service ID.
         * @return array{duration_minutes: int, step_minutes: int}|null
         */
        private static function get_slot_generation_config($staff_id, $service_id)
        {
            global $wpdb;
            $bwdb = new BookWellAB_Database();
            $staff_serv_table = esc_sql($bwdb->get_table_name('staff_services'));
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
            $service_duration = $wpdb->get_row($wpdb->prepare(
                "SELECT bw_service_duration_val, bw_service_duration_unit FROM $staff_serv_table WHERE bw_staff_id = %d AND bw_service_id = %d",
                $staff_id,
                $service_id
            ), ARRAY_A);

            if (!$service_duration) {
                return null;
            }

            $duration_minutes = intval($service_duration['bw_service_duration_val']);
            if (isset($service_duration['bw_service_duration_unit']) && $service_duration['bw_service_duration_unit'] === 'h') {
                $duration_minutes *= 60;
            }

            if ($duration_minutes <= 0) {
                $services_table = esc_sql($bwdb->get_table_name('services'));
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
                $service_defaults = $wpdb->get_row($wpdb->prepare(
                    "SELECT bw_service_duration_val, bw_service_duration_unit FROM $services_table WHERE bw_service_id = %d",
                    $service_id
                ), ARRAY_A);

                if ($service_defaults) {
                    $duration_minutes = intval($service_defaults['bw_service_duration_val']);
                    if (isset($service_defaults['bw_service_duration_unit']) && $service_defaults['bw_service_duration_unit'] === 'h') {
                        $duration_minutes *= 60;
                    }
                }
            }

            if ($duration_minutes <= 0) {
                $duration_minutes = 30;
            }

            $duration_minutes = (int) apply_filters(
                'bookwellab_booking_duration_minutes',
                $duration_minutes,
                $service_id,
                $staff_id,
                self::get_slot_booking_context()
            );

            $settings = BookWellAB_Settings::get_settings();
            $slot_step_setting = isset($settings['general']['slot_step']) ? $settings['general']['slot_step'] : '15';
            $step_minutes = 15;
            if ($slot_step_setting === 'service_duration') {
                $step_minutes = $duration_minutes;
            } else {
                $step_minutes = intval($slot_step_setting);
                if ($step_minutes <= 0) {
                    $step_minutes = 15;
                }
            }

            return [
                'duration_minutes' => $duration_minutes,
                'step_minutes' => $step_minutes,
            ];
        }

        /**
         * @param int    $staff_id Staff ID.
         * @param int    $year     Year.
         * @param int    $month    Month 1-12.
         * @return array<string, array> Working hours keyed by lowercase day name.
         */
        private static function load_staff_working_hours_map($staff_id)
        {
            global $wpdb;
            $bwdb = new BookWellAB_Database();
            $working_hours_table = esc_sql($bwdb->get_table_name('staff_working_hours'));
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
            $rows = $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM $working_hours_table WHERE bw_staff_id = %d",
                $staff_id
            ), ARRAY_A);

            $map = [];
            if (!empty($rows)) {
                foreach ($rows as $row) {
                    $day_key = strtolower((string) ($row['bw_day_name'] ?? ''));
                    if ($day_key !== '') {
                        $map[$day_key] = $row;
                    }
                }
            }
            return $map;
        }

        /**
         * @param int    $staff_id   Staff ID.
         * @param string $range_from Y-m-d.
         * @param string $range_to   Y-m-d.
         * @return array<int, array>
         */
        private static function load_staff_special_days_for_range($staff_id, $range_from, $range_to)
        {
            global $wpdb;
            $bwdb = new BookWellAB_Database();
            $special_days_table = esc_sql($bwdb->get_table_name('staff_special_days'));
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
            $rows = $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM $special_days_table
                 WHERE bw_staff_id = %d AND bw_start_date <= %s AND bw_end_date >= %s
                 ORDER BY bw_is_off DESC, bw_start_date ASC",
                $staff_id,
                $range_to,
                $range_from
            ), ARRAY_A);

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

        /**
         * @param string $date         Y-m-d.
         * @param array  $special_days Special day rows.
         * @return array|null
         */
        private static function resolve_special_day_from_list($date, $special_days)
        {
            $matches = [];
            foreach ($special_days as $row) {
                $start = (string) ($row['bw_start_date'] ?? '');
                $end = (string) ($row['bw_end_date'] ?? '');
                if ($start === '' || $end === '') {
                    continue;
                }
                if ($date < $start || $date > $end) {
                    continue;
                }
                $matches[] = $row;
            }

            if (empty($matches)) {
                return null;
            }

            usort(
                $matches,
                static function ($a, $b) {
                    $off_a = !empty($a['bw_is_off']) ? 1 : 0;
                    $off_b = !empty($b['bw_is_off']) ? 1 : 0;
                    if ($off_a !== $off_b) {
                        return $off_b <=> $off_a;
                    }
                    return strcmp((string) ($a['bw_start_date'] ?? ''), (string) ($b['bw_start_date'] ?? ''));
                }
            );

            return $matches[0];
        }

        /**
         * @param string $date Y-m-d.
         * @param array  $row  Appointment row.
         * @return array{start: int, end: int}|null
         */
        private static function appointment_row_to_interval($date, $row)
        {
            $booked_date_raw = isset($row['bw_date']) ? trim((string) $row['bw_date']) : '';
            if ($booked_date_raw === '') {
                return null;
            }

            $booked_date_norm = '';
            if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $booked_date_raw)) {
                $booked_date_norm = $booked_date_raw;
            } else {
                $booked_date_ts = strtotime($booked_date_raw);
                if ($booked_date_ts !== false) {
                    $booked_date_norm = wp_date('Y-m-d', $booked_date_ts, wp_timezone());
                }
            }

            if ($booked_date_norm !== $date) {
                return null;
            }

            $booked_time_hm = self::normalize_time_to_hm(isset($row['bw_time']) ? $row['bw_time'] : '');
            if ($booked_time_hm === '') {
                return null;
            }

            $bs_start = self::to_site_timestamp($date, $booked_time_hm);
            if ($bs_start <= 0) {
                return null;
            }

            $booked_duration = isset($row['bw_duration']) ? intval($row['bw_duration']) : 0;
            if ($booked_duration <= 0) {
                $booked_duration = 30;
            }

            return [
                'start' => $bs_start,
                'end' => $bs_start + ($booked_duration * 60),
            ];
        }

        /**
         * @param string $date              Y-m-d.
         * @param array  $working_hours_map Working hours by day name.
         * @param array  $special_days      Special days for range.
         * @return array{is_off: bool, start_time: string, end_time: string, breaks: array}|null
         */
        private static function resolve_day_schedule_from_context($date, $working_hours_map, $special_days)
        {
            $special_day = self::resolve_special_day_from_list($date, $special_days);
            if ($special_day) {
                if (!empty($special_day['bw_is_off'])) {
                    return ['is_off' => true, 'start_time' => '', 'end_time' => '', 'breaks' => []];
                }
                $breaks = [];
                if (!empty($special_day['bw_breaks'])) {
                    $breaks_data = BookWellAB_Input::sanitize_break_hours_list($special_day['bw_breaks']);
                    if (!empty($breaks_data)) {
                        foreach ($breaks_data as $brk) {
                            if (!isset($brk['start'], $brk['end'])) {
                                continue;
                            }
                            $break_start = self::to_site_timestamp($date, $brk['start']);
                            $break_end = self::to_site_timestamp($date, $brk['end']);
                            if ($break_start <= 0 || $break_end <= 0) {
                                continue;
                            }
                            $breaks[] = ['start' => $break_start, 'end' => $break_end];
                        }
                    }
                }
                return [
                    'is_off' => false,
                    'start_time' => (string) ($special_day['bw_start_time'] ?? '08:00'),
                    'end_time' => (string) ($special_day['bw_end_time'] ?? '17:00'),
                    'breaks' => $breaks,
                ];
            }

            $weekday_dt = new \DateTimeImmutable($date, wp_timezone());
            $day_name = strtolower($weekday_dt->format('l'));
            $working_hours = isset($working_hours_map[$day_name]) ? $working_hours_map[$day_name] : null;
            if (!$working_hours || !empty($working_hours['bw_is_off'])) {
                return ['is_off' => true, 'start_time' => '', 'end_time' => '', 'breaks' => []];
            }

            $breaks = [];
            if (!empty($working_hours['bw_breaks'])) {
                $breaks_data = BookWellAB_Input::sanitize_break_hours_list($working_hours['bw_breaks']);
                if (!empty($breaks_data)) {
                    foreach ($breaks_data as $brk) {
                        if (!isset($brk['start'], $brk['end'])) {
                            continue;
                        }
                        $break_start = self::to_site_timestamp($date, $brk['start']);
                        $break_end = self::to_site_timestamp($date, $brk['end']);
                        if ($break_start <= 0 || $break_end <= 0) {
                            continue;
                        }
                        $breaks[] = ['start' => $break_start, 'end' => $break_end];
                    }
                }
            }

            return [
                'is_off' => false,
                'start_time' => (string) ($working_hours['bw_start_time'] ?? '08:00'),
                'end_time' => (string) ($working_hours['bw_end_time'] ?? '17:00'),
                'breaks' => $breaks,
            ];
        }

        /**
         * @param string $date              Y-m-d.
         * @param array  $schedule          Day schedule.
         * @param array  $slot_config       Slot config.
         * @param array  $booked_intervals  Booked intervals for the day.
         * @return array<int, string> Available slot times (H:i).
         */
        private static function generate_slots_for_day_schedule($date, $schedule, $slot_config, $booked_intervals, $exclude_past_slots = false)
        {
            if ($exclude_past_slots) {
                $site_today = wp_date('Y-m-d', null, wp_timezone());
                if ($date < $site_today) {
                    return array();
                }
            }

            if (!empty($schedule['is_off'])) {
                return array();
            }

            $duration_minutes = (int) $slot_config['duration_minutes'];
            $step_minutes = (int) $slot_config['step_minutes'];
            $breaks = isset($schedule['breaks']) ? $schedule['breaks'] : array();

            $start_time = self::to_site_timestamp($date, $schedule['start_time']);
            $end_time = self::to_site_timestamp($date, $schedule['end_time']);
            if ($start_time <= 0 || $end_time <= 0 || $start_time >= $end_time) {
                return array();
            }

            $current_site_time = $exclude_past_slots ? self::get_site_now_timestamp() : 0;
            $slots = array();

            while ($start_time + ($duration_minutes * 60) <= $end_time) {
                $slot_end_time = $start_time + ($duration_minutes * 60);

                if ($exclude_past_slots && $start_time < $current_site_time) {
                    $start_time += $step_minutes * 60;
                    continue;
                }

                $is_break = false;
                foreach ($breaks as $brk) {
                    if ($start_time < $brk['end'] && $slot_end_time > $brk['start']) {
                        $is_break = true;
                        break;
                    }
                }

                $is_booked = false;
                if (!$is_break) {
                    foreach ($booked_intervals as $booked) {
                        if ($start_time < $booked['end'] && $slot_end_time > $booked['start']) {
                            $is_booked = true;
                            break;
                        }
                    }
                }

                if (!$is_break && !$is_booked) {
                    $slots[] = wp_date('H:i', $start_time, wp_timezone());
                }

                $start_time += $step_minutes * 60;
            }

            return $slots;
        }

        /**
         * @param string $date              Y-m-d.
         * @param array  $schedule          Day schedule.
         * @param array  $slot_config       Slot config.
         * @param array  $booked_intervals  Booked intervals for the day.
         * @param bool   $exclude_past_slots Exclude slots before site now when true.
         * @return bool
         */
        private static function day_schedule_has_available_slot($date, $schedule, $slot_config, $booked_intervals, $exclude_past_slots = false)
        {
            return !empty(self::generate_slots_for_day_schedule($date, $schedule, $slot_config, $booked_intervals, $exclude_past_slots));
        }

        /**
         * @param array $context Month availability context.
         * @param int   $year    Year.
         * @param int   $month   Month 1-12.
         * @return array{disabled_dates: array<int, string>, time_slots_by_date: array<string, array<int, string>>}
         */
        private static function compute_month_calendar_data($context, $year, $month)
        {
            $disabled_dates = array();
            $time_slots_by_date = array();

            for ($day = 1; $day <= (int) $context['days_in_month']; $day++) {
                $date = sprintf('%04d-%02d-%02d', $year, $month, $day);

                if ($date < $context['today']) {
                    $disabled_dates[] = $date;
                    continue;
                }

                $schedule = self::resolve_day_schedule_from_context(
                    $date,
                    $context['working_hours_map'],
                    $context['special_days']
                );
                $booked_intervals = isset($context['appointments_by_date'][$date])
                    ? $context['appointments_by_date'][$date]
                    : array();

                $slots = self::generate_slots_for_day_schedule($date, $schedule, $context['slot_config'], $booked_intervals, true);
                if (empty($slots)) {
                    $disabled_dates[] = $date;
                    continue;
                }

                $time_slots_by_date[$date] = $slots;
            }

            return array(
                'disabled_dates' => $disabled_dates,
                'time_slots_by_date' => $time_slots_by_date,
            );
        }

        /**
         * @param int    $staff_id   Staff ID.
         * @param int    $service_id Service ID.
         * @param int    $year       Year.
         * @param int    $month      Month 1-12.
         * @return array Context array for month calculations.
         */
        private static function build_month_availability_context($staff_id, $service_id, $year, $month, $exclude_appointment_id = 0)
        {
            global $wpdb;

            $slot_config = self::get_slot_generation_config($staff_id, $service_id);
            if ($slot_config === null) {
                return null;
            }

            $exclude_appointment_id = absint($exclude_appointment_id);
            $days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);
            $range_from = sprintf('%04d-%02d-01', $year, $month);
            $range_to = sprintf('%04d-%02d-%02d', $year, $month, $days_in_month);

            $bwdb = new BookWellAB_Database();
            $appointments_table = esc_sql($bwdb->get_table_name('appointments'));
            if ($exclude_appointment_id > 0) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
            $appointment_rows = $wpdb->get_results($wpdb->prepare(
                    "SELECT bw_appointment_id, bw_date, bw_time, bw_duration FROM $appointments_table
                     WHERE bw_staff_id = %d AND bw_date >= %s AND bw_date <= %s AND bw_appointment_id != %d
                       AND (
                            bw_status IS NULL
                            OR LOWER(TRIM(bw_status)) NOT IN ('cancelled', 'canceled')
                       )",
                    $staff_id,
                    $range_from,
                    $range_to,
                    $exclude_appointment_id
                ), ARRAY_A);
            } else {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from trusted plugin schema helper.
                $appointment_rows = $wpdb->get_results($wpdb->prepare(
                    "SELECT bw_appointment_id, bw_date, bw_time, bw_duration FROM $appointments_table
                 WHERE bw_staff_id = %d AND bw_date >= %s AND bw_date <= %s
                   AND (
                        bw_status IS NULL
                        OR LOWER(TRIM(bw_status)) NOT IN ('cancelled', 'canceled')
                   )",
                $staff_id,
                $range_from,
                $range_to
            ), ARRAY_A);
            }

            $appointments_by_date = [];
            if (!empty($appointment_rows)) {
                foreach ($appointment_rows as $row) {
                    if ($exclude_appointment_id > 0 && isset($row['bw_appointment_id']) && absint($row['bw_appointment_id']) === $exclude_appointment_id) {
                        continue;
                    }

                    $booked_date_raw = isset($row['bw_date']) ? trim((string) $row['bw_date']) : '';
                    if ($booked_date_raw === '') {
                        continue;
                    }

                    $date_key = '';
                    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $booked_date_raw)) {
                        $date_key = $booked_date_raw;
                    } else {
                        $booked_date_ts = strtotime($booked_date_raw);
                        if ($booked_date_ts !== false) {
                            $date_key = wp_date('Y-m-d', $booked_date_ts, wp_timezone());
                        }
                    }

                    if ($date_key === '') {
                        continue;
                    }

                    $interval = self::appointment_row_to_interval($date_key, $row);
                    if ($interval === null) {
                        continue;
                    }

                    if (!isset($appointments_by_date[$date_key])) {
                        $appointments_by_date[$date_key] = [];
                    }
                    $appointments_by_date[$date_key][] = $interval;
                }
            }

            /**
             * External busy intervals for a month range (e.g. Google Calendar).
             * Returned as a flat list of {start,end} unix timestamps; merged per day below.
             */
            $extra_intervals = apply_filters(
                'bookwellab_staff_booked_intervals',
                [],
                $range_from,
                (int) $staff_id,
                (int) $service_id,
                [
                    'mode' => 'month',
                    'date_from' => $range_from,
                    'date_to' => $range_to,
                    'exclude_appointment_id' => $exclude_appointment_id,
                ]
            );
            if (is_array($extra_intervals) && !empty($extra_intervals)) {
                $tz = wp_timezone();
                foreach ($extra_intervals as $interval) {
                    if (!is_array($interval) || !isset($interval['start'], $interval['end'])) {
                        continue;
                    }
                    $start_ts = (int) $interval['start'];
                    $end_ts = (int) $interval['end'];
                    if ($start_ts <= 0 || $end_ts <= $start_ts) {
                        continue;
                    }

                    try {
                        $cursor = (new \DateTimeImmutable('@' . $start_ts))->setTimezone($tz);
                        $end_day = (new \DateTimeImmutable('@' . ($end_ts - 1)))->setTimezone($tz)->format('Y-m-d');
                    } catch (\Exception $e) {
                        continue;
                    }

                    while ($cursor->format('Y-m-d') <= $end_day) {
                        $date_key = $cursor->format('Y-m-d');
                        if ($date_key >= $range_from && $date_key <= $range_to) {
                            if (!isset($appointments_by_date[$date_key])) {
                                $appointments_by_date[$date_key] = [];
                            }
                            $appointments_by_date[$date_key][] = [
                                'start' => $start_ts,
                                'end' => $end_ts,
                                'source' => isset($interval['source']) ? $interval['source'] : 'external',
                            ];
                        }
                        $cursor = $cursor->modify('+1 day')->setTime(0, 0, 0);
                    }
                }
            }

            return [
                'slot_config' => $slot_config,
                'working_hours_map' => self::load_staff_working_hours_map($staff_id),
                'special_days' => self::load_staff_special_days_for_range($staff_id, $range_from, $range_to),
                'appointments_by_date' => $appointments_by_date,
                'today' => wp_date('Y-m-d', null, wp_timezone()),
                'days_in_month' => $days_in_month,
            ];
        }

        /**
         * @param int    $staff_id   Staff ID.
         * @param int    $service_id Service ID.
         * @param int    $year       Year.
         * @param int    $month      Month 1-12.
         * @return bool
         */
        public static function month_has_any_bookable_day($staff_id, $service_id, $year, $month)
        {
            $context = self::build_month_availability_context($staff_id, $service_id, $year, $month);
            if ($context === null) {
                return false;
            }

            for ($day = 1; $day <= (int) $context['days_in_month']; $day++) {
                $date = sprintf('%04d-%02d-%02d', $year, $month, $day);
                if ($date < $context['today']) {
                    continue;
                }

                $schedule = self::resolve_day_schedule_from_context(
                    $date,
                    $context['working_hours_map'],
                    $context['special_days']
                );
                $booked_intervals = isset($context['appointments_by_date'][$date])
                    ? $context['appointments_by_date'][$date]
                    : [];

                if (self::day_schedule_has_available_slot($date, $schedule, $context['slot_config'], $booked_intervals, true)) {
                    return true;
                }
            }

            return false;
        }

        /**
         * @param int $staff_id   Staff ID.
         * @param int $service_id Service ID.
         * @param int $year       Year.
         * @param int $month      Month 1-12.
         * @return array<int, string> Disabled dates (Y-m-d).
         */
        public static function get_month_disabled_dates($staff_id, $service_id, $year, $month)
        {
            $context = self::build_month_availability_context($staff_id, $service_id, $year, $month);

            if ($context === null) {
                $disabled_dates = array();
                $days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);
                for ($day = 1; $day <= $days_in_month; $day++) {
                    $disabled_dates[] = sprintf('%04d-%02d-%02d', $year, $month, $day);
                }
                return $disabled_dates;
            }

            return self::compute_month_calendar_data($context, $year, $month)['disabled_dates'];
        }

        /**
         * Admin month calendar: staff schedule only (no past-date blocking).
         *
         * @param array $context Month availability context.
         * @param int   $year    Year.
         * @param int   $month   Month 1-12.
         * @return array{disabled_dates: array<int, string>, time_slots_by_date: array<string, array<int, string>>}
         */
        private static function compute_admin_month_calendar_data($context, $year, $month)
        {
            $disabled_dates = array();
            $time_slots_by_date = array();

            for ($day = 1; $day <= (int) $context['days_in_month']; $day++) {
                $date = sprintf('%04d-%02d-%02d', $year, $month, $day);

                $schedule = self::resolve_day_schedule_from_context(
                    $date,
                    $context['working_hours_map'],
                    $context['special_days']
                );
                $booked_intervals = isset($context['appointments_by_date'][$date])
                    ? $context['appointments_by_date'][$date]
                    : array();

                $slots = self::generate_slots_for_day_schedule($date, $schedule, $context['slot_config'], $booked_intervals, false);
                if (empty($slots)) {
                    $disabled_dates[] = $date;
                    continue;
                }

                $time_slots_by_date[$date] = $slots;
            }

            return array(
                'disabled_dates' => $disabled_dates,
                'time_slots_by_date' => $time_slots_by_date,
            );
        }

        /**
         * Admin booking: disabled dates for a month (staff schedule, past dates allowed).
         *
         * @param int $staff_id              Staff ID.
         * @param int $service_id            Service ID.
         * @param int $year                  Year.
         * @param int $month                 Month 1-12.
         * @param int $exclude_appointment_id Appointment ID to ignore when editing.
         * @return array<int, string>
         */
        public static function get_admin_month_availability($staff_id, $service_id, $year, $month, $exclude_appointment_id = 0)
        {
            $staff_id = absint($staff_id);
            $service_id = absint($service_id);
            $year = absint($year);
            $month = absint($month);
            $exclude_appointment_id = absint($exclude_appointment_id);

            if ($staff_id <= 0 || $service_id <= 0 || $year <= 0 || $month <= 0 || $month > 12) {
                return array();
            }

            $context = self::build_month_availability_context($staff_id, $service_id, $year, $month, $exclude_appointment_id);
            if ($context === null) {
                $disabled_dates = array();
                $days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);
                for ($day = 1; $day <= $days_in_month; $day++) {
                    $disabled_dates[] = sprintf('%04d-%02d-%02d', $year, $month, $day);
                }
            } else {
                $disabled_dates = self::compute_admin_month_calendar_data($context, $year, $month)['disabled_dates'];
            }

            /**
             * Filter admin disabled dates for a month.
             *
             * @param array<int, string> $disabled_dates         Disabled dates Y-m-d.
             * @param int                $staff_id               Staff ID.
             * @param int                $service_id             Service ID.
             * @param int                $year                   Year.
             * @param int                $month                  Month 1-12.
             * @param int                $exclude_appointment_id Excluded appointment ID.
             */
            return apply_filters(
                'bookwellab_admin_disabled_dates',
                $disabled_dates,
                $staff_id,
                $service_id,
                $year,
                $month,
                $exclude_appointment_id
            );
        }

        /**
         * Admin booking: available time slots for a date (staff schedule, past times allowed).
         *
         * @param string $date                   Date Y-m-d.
         * @param int    $staff_id               Staff ID.
         * @param int    $service_id             Service ID.
         * @param int    $exclude_appointment_id Appointment ID to ignore when editing.
         * @return array<int, string>
         */
        public static function get_admin_time_slots($date, $staff_id, $service_id, $exclude_appointment_id = 0)
        {
            $date = sanitize_text_field((string) $date);
            if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                return array();
            }

            $staff_id = absint($staff_id);
            $service_id = absint($service_id);
            $exclude_appointment_id = absint($exclude_appointment_id);

            if ($staff_id <= 0 || $service_id <= 0) {
                return array();
            }

            $slots = self::get_staff_available_slots($date, $staff_id, $service_id, false, $exclude_appointment_id);

            /**
             * Filter admin available time slots.
             *
             * @param array<int, string> $slots                  Slot times H:i.
             * @param string             $date                   Date Y-m-d.
             * @param int                $staff_id               Staff ID.
             * @param int                $service_id             Service ID.
             * @param int                $exclude_appointment_id Excluded appointment ID.
             */
            return apply_filters(
                'bookwellab_admin_available_slots',
                $slots,
                $date,
                $staff_id,
                $service_id,
                $exclude_appointment_id
            );
        }

        /**
         * Find the calendar month to display and its disabled dates.
         *
         * @param int    $staff_id       Staff ID.
         * @param int    $service_id     Service ID.
         * @param int    $start_year     Start year.
         * @param int    $start_month    Start month 1-12.
         * @param string $scan_direction forward|backward.
         * @param int    $max_scan       Max months to scan.
         * @return array{year: int, month: int, disabled_dates: array, has_availability: bool}
         */
        public static function resolve_calendar_month($staff_id, $service_id, $start_year, $start_month, $scan_direction = 'forward', $max_scan = 24)
        {
            $staff_id = absint($staff_id);
            $service_id = absint($service_id);
            $max_scan = max(1, min(24, absint($max_scan)));
            $scan_direction = ($scan_direction === 'backward') ? 'backward' : 'forward';

            $today = wp_date('Y-m-d', null, wp_timezone());
            $today_dt = new \DateTimeImmutable($today, wp_timezone());
            $today_month_start = $today_dt->modify('first day of this month');

            if ($start_year <= 0 || $start_month <= 0) {
                $scan_year = (int) $today_dt->format('Y');
                $scan_month = (int) $today_dt->format('n');
            } else {
                $scan_year = $start_year;
                $scan_month = $start_month;
            }

            $scan_dt = new \DateTimeImmutable(
                sprintf('%04d-%02d-01', $scan_year, $scan_month),
                wp_timezone()
            );

            if ($scan_direction === 'forward' && $scan_dt < $today_month_start) {
                $scan_year = (int) $today_dt->format('Y');
                $scan_month = (int) $today_dt->format('n');
                $scan_dt = $today_month_start;
            }

            if ($scan_direction === 'backward' && $scan_dt < $today_month_start) {
                $scan_year = (int) $today_dt->format('Y');
                $scan_month = (int) $today_dt->format('n');
                $scan_dt = $today_month_start;
            }

            $has_availability = false;

            for ($attempt = 0; $attempt < $max_scan; $attempt++) {
                if (self::month_has_any_bookable_day($staff_id, $service_id, $scan_year, $scan_month)) {
                    $has_availability = true;
                    break;
                }

                if ($scan_direction === 'backward') {
                    $scan_dt = $scan_dt->modify('-1 month');
                    if ($scan_dt < $today_month_start) {
                        break;
                    }
                } else {
                    $scan_dt = $scan_dt->modify('+1 month');
                }

                $scan_year = (int) $scan_dt->format('Y');
                $scan_month = (int) $scan_dt->format('n');
            }

            $context = self::build_month_availability_context($staff_id, $service_id, $scan_year, $scan_month);
            if ($context === null) {
                $disabled_dates = array();
                $days_in_month = cal_days_in_month(CAL_GREGORIAN, $scan_month, $scan_year);
                for ($day = 1; $day <= $days_in_month; $day++) {
                    $disabled_dates[] = sprintf('%04d-%02d-%02d', $scan_year, $scan_month, $day);
                }
                $calendar_data = array(
                    'disabled_dates' => $disabled_dates,
                    'time_slots_by_date' => array(),
                );
            } else {
                $calendar_data = self::compute_month_calendar_data($context, $scan_year, $scan_month);
            }

            return array(
                'year' => $scan_year,
                'month' => $scan_month,
                'disabled_dates' => $calendar_data['disabled_dates'],
                'time_slots_by_date' => $calendar_data['time_slots_by_date'],
                'has_availability' => $has_availability,
            );
        }

        /**
         * Frontend booking calendar: scan to the nearest month with bookable days (past dates excluded).
         *
         * @param int    $staff_id       Staff ID.
         * @param int    $service_id     Service ID.
         * @param int    $start_year     Requested year.
         * @param int    $start_month    Requested month 1-12.
         * @param string $scan_direction forward|backward.
         * @param int    $max_scan       Max months to scan.
         * @return array{year: int, month: int, disabled_dates: array, time_slots_by_date: array, has_availability: bool, site_today: string}
         */
        public static function resolve_calendar_month_for_frontend($staff_id, $service_id, $start_year, $start_month, $scan_direction = 'forward', $max_scan = 24)
        {
            $staff_id = absint($staff_id);
            $service_id = absint($service_id);
            $max_scan = max(1, min(24, absint($max_scan)));
            $scan_direction = ($scan_direction === 'backward') ? 'backward' : 'forward';

            $today = wp_date('Y-m-d', null, wp_timezone());
            $today_dt = new \DateTimeImmutable($today, wp_timezone());
            $today_year = (int) $today_dt->format('Y');
            $today_month = (int) $today_dt->format('n');
            $today_month_start = $today_dt->modify('first day of this month');

            if ($start_year <= 0 || $start_month <= 0) {
                $scan_year = $today_year;
                $scan_month = $today_month;
            } else {
                $scan_year = $start_year;
                $scan_month = $start_month;
            }

            if ($scan_year < $today_year || ($scan_year === $today_year && $scan_month < $today_month)) {
                $scan_year = $today_year;
                $scan_month = $today_month;
            }

            $scan_dt = new \DateTimeImmutable(
                sprintf('%04d-%02d-01', $scan_year, $scan_month),
                wp_timezone()
            );

            $has_availability = false;

            for ($attempt = 0; $attempt < $max_scan; $attempt++) {
                if (self::month_has_any_bookable_day($staff_id, $service_id, $scan_year, $scan_month)) {
                    $has_availability = true;
                    break;
                }

                if ($scan_direction === 'backward') {
                    $scan_dt = $scan_dt->modify('-1 month');
                    if ($scan_dt < $today_month_start) {
                        break;
                    }
                } else {
                    $scan_dt = $scan_dt->modify('+1 month');
                }

                $scan_year = (int) $scan_dt->format('Y');
                $scan_month = (int) $scan_dt->format('n');
            }

            $context = self::build_month_availability_context($staff_id, $service_id, $scan_year, $scan_month);
            if ($context === null) {
                $disabled_dates = array();
                $days_in_month = cal_days_in_month(CAL_GREGORIAN, $scan_month, $scan_year);
                for ($day = 1; $day <= $days_in_month; $day++) {
                    $disabled_dates[] = sprintf('%04d-%02d-%02d', $scan_year, $scan_month, $day);
                }
                $calendar_data = array(
                    'disabled_dates' => $disabled_dates,
                    'time_slots_by_date' => array(),
                );
            } else {
                $calendar_data = self::compute_month_calendar_data($context, $scan_year, $scan_month);
            }

            return array(
                'year' => $scan_year,
                'month' => $scan_month,
                'disabled_dates' => $calendar_data['disabled_dates'],
                'time_slots_by_date' => $calendar_data['time_slots_by_date'],
                'has_availability' => $has_availability && !empty($calendar_data['time_slots_by_date']),
                'site_today' => $today,
            );
        }

        /**
         * Resolve staff working schedule for a specific date.
         *
         * @param int    $staff_id Staff ID.
         * @param string $date     Date in Y-m-d format.
         * @return array{is_off: bool, start_time: string, end_time: string, breaks: array}
         */
        public static function get_staff_day_schedule($staff_id, $date)
        {
            global $wpdb;

            $staff_id = absint($staff_id);
            $schedule = [
                'is_off' => true,
                'start_time' => '08:00',
                'end_time' => '17:00',
                'breaks' => [],
            ];

            $special_day = self::get_staff_special_day_for_date($staff_id, $date);

            if ($special_day) {
                if (!empty($special_day['bw_is_off'])) {
                    return $schedule;
                }
                $schedule['is_off'] = false;
                $schedule['start_time'] = (string) ($special_day['bw_start_time'] ?? '08:00');
                $schedule['end_time'] = (string) ($special_day['bw_end_time'] ?? '17:00');
                if (!empty($special_day['bw_breaks'])) {
                    $schedule['breaks'] = BookWellAB_Input::sanitize_break_hours_list($special_day['bw_breaks']);
                }
                return $schedule;
            }

            $weekday_dt = new \DateTimeImmutable($date, wp_timezone());
            $day_name = strtolower($weekday_dt->format('l'));
            $bwdb = new BookWellAB_Database();
            $working_hours_table = esc_sql($bwdb->get_table_name('staff_working_hours'));
            $working_hours_sql = "SELECT * FROM $working_hours_table WHERE bw_staff_id = %d AND LOWER(bw_day_name) = %s";
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is plugin-controlled; values use prepare().
            $working_hours = $wpdb->get_row($wpdb->prepare($working_hours_sql, $staff_id, $day_name), ARRAY_A);

            if (!$working_hours || !empty($working_hours['bw_is_off'])) {
                return $schedule;
            }

            $schedule['is_off'] = false;
            $schedule['start_time'] = (string) ($working_hours['bw_start_time'] ?? '08:00');
            $schedule['end_time'] = (string) ($working_hours['bw_end_time'] ?? '17:00');
            if (!empty($working_hours['bw_breaks'])) {
                $schedule['breaks'] = BookWellAB_Input::sanitize_break_hours_list($working_hours['bw_breaks']);
            }

            return $schedule;
        }

        /**
         * Build hour labels and minute range for a staff day timeline.
         *
         * @param array  $schedule         Staff schedule.
         * @param array  $appointment_rows Raw appointment rows.
         * @param string $date             Date in Y-m-d format.
         * @return array|null
         */
        public static function build_staff_day_timeline($schedule, $appointment_rows, $date)
        {
            if (!empty($schedule['is_off'])) {
                return null;
            }

            $work_start = self::time_to_minutes($schedule['start_time']);
            $work_end = self::time_to_minutes($schedule['end_time']);
            if ($work_start <= 0 || $work_end <= 0 || $work_start >= $work_end) {
                $work_start = 8 * 60;
                $work_end = 17 * 60;
            }

            $start_minutes = $work_start;
            $end_minutes = $work_end;

            foreach ($appointment_rows as $row) {
                $appt_start = self::time_to_minutes((string) ($row['bw_time'] ?? ''));
                $duration = max(0, (int) ($row['bw_duration'] ?? 0));
                if ($appt_start <= 0) {
                    continue;
                }
                $appt_end = $appt_start + $duration;
                if ($appt_start < $start_minutes) {
                    $start_minutes = $appt_start;
                }
                if ($appt_end > $end_minutes) {
                    $end_minutes = $appt_end;
                }
            }

            $start_minutes = max(0, (int) (floor($start_minutes / 60) * 60));
            $end_minutes = min(24 * 60, (int) (ceil($end_minutes / 60) * 60));
            if ($end_minutes <= $start_minutes) {
                $end_minutes = $start_minutes + (9 * 60);
            }

            $hours = [];
            $hour_base = new \DateTimeImmutable($date . ' 00:00:00', wp_timezone());
            for ($m = $start_minutes; $m <= $end_minutes; $m += 60) {
                $hour_dt = $hour_base->setTime((int) floor($m / 60), $m % 60);
                $hours[] = [
                    'minutes' => $m,
                    'label' => $hour_dt->format('g A'),
                ];
            }

            return [
                'start_minutes' => $start_minutes,
                'end_minutes' => $end_minutes,
                'hours' => $hours,
            ];
        }

        /**
         * Format appointment rows for day timeline views.
         *
         * @param array      $appointment_rows Raw rows.
         * @param string     $date             Date in Y-m-d format.
         * @param array|null $timeline         Timeline data.
         * @return array<int, array<string, mixed>>
         */
        public static function format_staff_day_appointments($appointment_rows, $date, $timeline)
        {
            unset($timeline);
            $items = [];
            $settings = BookWellAB_Settings::get_settings();
            $date_format = isset($settings['general']['date_format']) ? $settings['general']['date_format'] : 'Y-m-d';

            foreach ($appointment_rows as $row) {
                $start_minutes = self::time_to_minutes((string) ($row['bw_time'] ?? ''));
                $duration = max(1, (int) ($row['bw_duration'] ?? 30));
                if ($start_minutes <= 0) {
                    continue;
                }
                $end_minutes = $start_minutes + $duration;
                $start_hm = sprintf('%02d:%02d', (int) floor($start_minutes / 60), $start_minutes % 60);
                $end_hm = sprintf('%02d:%02d', (int) floor($end_minutes / 60), $end_minutes % 60);
                $timezone = wp_timezone();
                $start_dt = \DateTimeImmutable::createFromFormat('Y-m-d H:i', $date . ' ' . $start_hm, $timezone);
                $end_dt = \DateTimeImmutable::createFromFormat('Y-m-d H:i', $date . ' ' . $end_hm, $timezone);
                $time_label = '';
                if ($start_dt instanceof \DateTimeImmutable && $end_dt instanceof \DateTimeImmutable) {
                    $time_label = strtolower(wp_date('g:ia', $start_dt->getTimestamp(), $timezone))
                        . '-' . strtolower(wp_date('g:ia', $end_dt->getTimestamp(), $timezone));
                }

                $phone = trim((string) ($row['bw_customer_phone_code'] ?? '') . (string) ($row['bw_customer_phone'] ?? ''));

                $items[] = [
                    'id' => (int) ($row['bw_appointment_id'] ?? 0),
                    'bw_appointment_id' => (int) ($row['bw_appointment_id'] ?? 0),
                    'service_name' => (string) ($row['bw_service_name'] ?? ($row['service_name'] ?? '')),
                    'customer_name' => (string) ($row['bw_customer_name'] ?? ''),
                    'bw_customer_name' => (string) ($row['bw_customer_name'] ?? ''),
                    'customer_email' => (string) ($row['bw_customer_email'] ?? ''),
                    'bw_customer_email' => (string) ($row['bw_customer_email'] ?? ''),
                    'customer_phone' => $phone,
                    'bw_customer_phone' => (string) ($row['bw_customer_phone'] ?? ''),
                    'status' => (string) ($row['bw_status'] ?? ''),
                    'bw_status' => (string) ($row['bw_status'] ?? ''),
                    'bw_time' => (string) ($row['bw_time'] ?? ''),
                    'bw_end_time' => (string) ($row['bw_end_time'] ?? ''),
                    'start_minutes' => $start_minutes,
                    'end_minutes' => $end_minutes,
                    'date_label' => wp_date($date_format, strtotime($date . ' 00:00:00'), $timezone),
                    'time_label' => $time_label,
                ];
            }

            usort(
                $items,
                static function ($a, $b) {
                    return ((int) $a['start_minutes']) <=> ((int) $b['start_minutes']);
                }
            );

            return $items;
        }

        /**
         * Compute current-time indicator for a day timeline.
         *
         * @param string     $date     Date in Y-m-d format.
         * @param array|null $timeline Timeline data.
         * @return array{now_top: int|null, now_label: string}
         */
        public static function get_day_timeline_now_indicator($date, $timeline)
        {
            $result = [
                'now_top' => null,
                'now_label' => '',
            ];

            if (empty($timeline) || !is_array($timeline)) {
                return $result;
            }

            $timezone = wp_timezone();
            $today = wp_date('Y-m-d', null, $timezone);
            if ($date !== $today) {
                return $result;
            }

            $now_dt = new \DateTimeImmutable('now', $timezone);
            $now_minutes = ((int) $now_dt->format('G')) * 60 + (int) $now_dt->format('i');
            $start_minutes = (int) ($timeline['start_minutes'] ?? 0);
            $end_minutes = (int) ($timeline['end_minutes'] ?? 0);

            if ($now_minutes >= $start_minutes && $now_minutes <= $end_minutes) {
                $result['now_top'] = $now_minutes - $start_minutes;
                $result['now_label'] = strtolower($now_dt->format('g:ia'));
            }

            return $result;
        }

        /**
         * Create an appointment from normalized booking data.
         *
         * @param array<string, mixed> $booking_data Booking payload.
         * @param array<string, mixed> $options      Optional overrides.
         * @return array{success: bool, appointment_id: int, msg?: string}
         */
        public static function create_appointment_from_booking_data($booking_data, $options = [])
        {
            global $wpdb;

            $service_id = isset($booking_data['service_id']) ? absint($booking_data['service_id']) : 0;
            $staff_id = isset($booking_data['staff_id']) ? absint($booking_data['staff_id']) : 0;
            $date = isset($booking_data['date']) ? sanitize_text_field($booking_data['date']) : '';
            $time = isset($booking_data['time']) ? sanitize_text_field($booking_data['time']) : '';
            $customer = isset($booking_data['customer']) && is_array($booking_data['customer']) ? $booking_data['customer'] : [];

            if (!$service_id || !$staff_id || $date === '' || $time === '' || empty($customer['email'])) {
                return ['success' => false, 'appointment_id' => 0, 'msg' => esc_html__('Missing required fields', 'bookwellnow-appointment-booking')];
            }

            $bwdb = new BookWellAB_Database();
            $cust_table = esc_sql($bwdb->get_table_name('customers'));
            $appt_table = esc_sql($bwdb->get_table_name('appointments'));
            $staff_serv_table = esc_sql($bwdb->get_table_name('staff_services'));
            $services_table = esc_sql($bwdb->get_table_name('services'));
            $staffs_table = esc_sql($bwdb->get_table_name('staffs'));

            $email_raw = isset($customer['email']) ? trim((string) $customer['email']) : '';
            $email = sanitize_email($email_raw);
            if ($email === '') {
                return ['success' => false, 'appointment_id' => 0, 'msg' => esc_html__('Please enter a valid email address.', 'bookwellnow-appointment-booking')];
            }

            $first_name = isset($customer['first_name']) ? sanitize_text_field($customer['first_name']) : '';
            $last_name = isset($customer['last_name']) ? sanitize_text_field($customer['last_name']) : '';
            $phone = isset($customer['phone']) ? preg_replace('/\D+/', '', (string) $customer['phone']) : '';
            $phone_code = isset($customer['phone_code']) ? sanitize_text_field($customer['phone_code']) : '';
            $country_slug = BookWellAB_Input::sanitize_country_slug($customer['country_slug'] ?? '');
            $note = isset($customer['note']) ? sanitize_textarea_field($customer['note']) : '';

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $existing_customer = $wpdb->get_row($wpdb->prepare("SELECT * FROM $cust_table WHERE bw_email = %s", $email), ARRAY_A);

            if ($existing_customer) {
                $customer_id = (int) $existing_customer['bw_customer_id'];
                $wpdb->update($cust_table, [
                    'bw_first_name' => $first_name,
                    'bw_last_name' => $last_name,
                    'bw_phone_code' => $phone_code,
                    'bw_phone' => $phone,
                    'bw_country_slug' => $country_slug,
                ], ['bw_customer_id' => $customer_id]);
            } else {
                $wpdb->insert($cust_table, [
                    'bw_first_name' => $first_name,
                    'bw_last_name' => $last_name,
                    'bw_email' => $email,
                    'bw_phone_code' => $phone_code,
                    'bw_phone' => $phone,
                    'bw_country_slug' => $country_slug,
                ]);
                $customer_id = (int) $wpdb->insert_id;
            }

            if ($customer_id <= 0) {
                return ['success' => false, 'appointment_id' => 0, 'msg' => esc_html__('Failed to save customer', 'bookwellnow-appointment-booking')];
            }

            $wp_user_data = [
                'first_name' => $first_name,
                'last_name' => $last_name,
                'email' => $email,
            ];

            /**
             * Filters customer data passed into WP user creation (e.g. booking form password).
             *
             * @param array<string, mixed> $wp_user_data first_name, last_name, email, optional password.
             * @param array<string, mixed> $booking_data Full booking payload.
             */
            $wp_user_data = apply_filters('bookwellab_create_wp_user_customer_data', $wp_user_data, $booking_data);
            if (!is_array($wp_user_data)) {
                $wp_user_data = [
                    'first_name' => $first_name,
                    'last_name' => $last_name,
                    'email' => $email,
                ];
            }

            BookWellAB_Customer_Account::maybe_create_wp_user_for_customer($customer_id, $wp_user_data);

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $service_info = $wpdb->get_row($wpdb->prepare(
                "SELECT bw_service_price, bw_service_duration_val, bw_service_duration_unit FROM $staff_serv_table WHERE bw_staff_id = %d AND bw_service_id = %d",
                $staff_id,
                $service_id
            ), ARRAY_A);

            $price = 0;
            $duration = 60;
            if ($service_info) {
                $price = floatval($service_info['bw_service_price']);
                $duration = intval($service_info['bw_service_duration_val']);
                if (isset($service_info['bw_service_duration_unit']) && $service_info['bw_service_duration_unit'] === 'h') {
                    $duration *= 60;
                }
            }

            $duration = intval($duration);
            if ($duration <= 0) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $service_defaults = $wpdb->get_row($wpdb->prepare(
                    "SELECT bw_service_duration_val, bw_service_duration_unit FROM $services_table WHERE bw_service_id = %d",
                    $service_id
                ), ARRAY_A);
                if ($service_defaults) {
                    $duration = intval($service_defaults['bw_service_duration_val']);
                    if (isset($service_defaults['bw_service_duration_unit']) && $service_defaults['bw_service_duration_unit'] === 'h') {
                        $duration *= 60;
                    }
                }
            }
            if ($duration <= 0) {
                $duration = 30;
            }

            $duration = (int) apply_filters(
                'bookwellab_booking_duration_minutes',
                $duration,
                $service_id,
                $staff_id,
                array('booking_data' => $booking_data)
            );

            $price = (float) apply_filters(
                'bookwellab_booking_price',
                $price,
                $service_id,
                $staff_id,
                $booking_data
            );

            $service_name = isset($booking_data['service_name']) ? sanitize_text_field($booking_data['service_name']) : '';
            $staff_name = isset($booking_data['staff_name']) ? sanitize_text_field($booking_data['staff_name']) : '';

            if ($service_name === '') {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $service_name = (string) $wpdb->get_var($wpdb->prepare("SELECT bw_service_name FROM $services_table WHERE bw_service_id = %d", $service_id));
            }
            if ($staff_name === '') {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $staff_name = (string) $wpdb->get_var($wpdb->prepare("SELECT bw_staff_name FROM $staffs_table WHERE bw_staff_id = %d", $staff_id));
            }

            $settings = BookWellAB_Settings::get_settings();
            $default_payment_status = isset($options['payment_status'])
                ? sanitize_text_field($options['payment_status'])
                : (isset($settings['general']['default_payment_status']) ? $settings['general']['default_payment_status'] : 'pending');
            $default_status = isset($options['bw_status'])
                ? sanitize_text_field($options['bw_status'])
                : (isset($settings['general']['default_status']) ? $settings['general']['default_status'] : 'pending');
            $payment_gateway = isset($options['payment_gateway'])
                ? sanitize_text_field($options['payment_gateway'])
                : (isset($booking_data['payment_gateway']) ? sanitize_text_field($booking_data['payment_gateway']) : 'offline');

            $insert_data = [
                'bw_appointment_hash' => wp_generate_password(20, false),
                'bw_service_id' => $service_id,
                'bw_staff_id' => $staff_id,
                'bw_customer_id' => $customer_id,
                'bw_date' => $date,
                'bw_time' => $time,
                'bw_duration' => $duration,
                'bw_price' => $price,
                'bw_status' => $default_status,
                'bw_customer_name' => trim($first_name . ' ' . $last_name),
                'bw_customer_email' => $email,
                'bw_customer_phone_code' => $phone_code,
                'bw_customer_phone' => $phone,
                'bw_customer_country_slug' => $country_slug,
                'bw_note' => $note,
                'bw_service_name' => $service_name,
                'bw_staff_name' => $staff_name,
                'bw_payment_gateway' => $payment_gateway,
                'bw_payment_status' => $default_payment_status,
            ];

            if (!empty($options['bw_wc_order_id'])) {
                $insert_data['bw_wc_order_id'] = absint($options['bw_wc_order_id']);
            }
            if (!empty($options['bw_wc_transaction_id'])) {
                $insert_data['bw_wc_transaction_id'] = sanitize_text_field($options['bw_wc_transaction_id']);
            }
            if (!empty($options['bw_payment_transaction_id'])) {
                $insert_data['bw_payment_transaction_id'] = sanitize_text_field($options['bw_payment_transaction_id']);
            }

            $inserted = $wpdb->insert($appt_table, $insert_data);
            if ($inserted === false) {
                return ['success' => false, 'appointment_id' => 0, 'msg' => esc_html__('Failed to create appointment', 'bookwellnow-appointment-booking')];
            }

            $appointment_id = (int) $wpdb->insert_id;
            $hash = $insert_data['bw_appointment_hash'];

            do_action('bookwellab_appointment_created', $appointment_id, $booking_data, $options);

            $send_notification = !isset($options['send_notification']) || $options['send_notification'];
            if ($send_notification) {
                $notification_data = [
                    'bw_appointment_id' => $appointment_id,
                    'bw_customer_name' => trim($first_name . ' ' . $last_name),
                    'bw_customer_first_name' => $first_name,
                    'bw_customer_last_name' => $last_name,
                    'bw_customer_email' => $email,
                    'bw_customer_phone' => $phone,
                    'bw_service_name' => $service_name,
                    'bw_staff_name' => $staff_name,
                    'bw_staff_id' => $staff_id,
                    'bw_date' => $date,
                    'bw_time' => $time,
                    'bw_price' => $price,
                    'bw_appointment_hash' => $hash,
                ];
                $trigger = self::get_notification_trigger_for_status($default_status);
                BookWellAB_Notifications::get_instance()->send_notification($trigger, $notification_data);
            }

            return ['success' => true, 'appointment_id' => $appointment_id, 'appointment_hash' => $hash];
        }

        /**
         * Calculate payable booking amount using the same filters as appointment creation.
         *
         * @param array<string, mixed> $booking_data Normalized booking data.
         * @return float
         */
        public static function calculate_booking_payable_amount($booking_data)
        {
            global $wpdb;

            $service_id = isset($booking_data['service_id']) ? absint($booking_data['service_id']) : 0;
            $staff_id = isset($booking_data['staff_id']) ? absint($booking_data['staff_id']) : 0;
            if ($service_id <= 0 || $staff_id <= 0) {
                return 0.0;
            }

            $bwdb = new BookWellAB_Database();
            $staff_serv_table = esc_sql($bwdb->get_table_name('staff_services'));

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $service_info = $wpdb->get_row($wpdb->prepare(
                "SELECT bw_service_price FROM $staff_serv_table WHERE bw_staff_id = %d AND bw_service_id = %d",
                $staff_id,
                $service_id
            ), ARRAY_A);

            $price = 0.0;
            if ($service_info) {
                $price = (float) $service_info['bw_service_price'];
            }

            return (float) apply_filters(
                'bookwellab_booking_price',
                $price,
                $service_id,
                $staff_id,
                $booking_data
            );
        }

        /**
         * Remove a pending appointment when PayPal order creation fails.
         *
         * @param int $appointment_id Appointment ID.
         * @return void
         */
        public static function delete_pending_appointment($appointment_id)
        {
            global $wpdb;

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

            $bwdb = new BookWellAB_Database();
            $appt_table = esc_sql($bwdb->get_table_name('appointments'));

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->delete(
                $appt_table,
                [
                    'bw_appointment_id' => $appointment_id,
                    'bw_payment_gateway' => 'paypal',
                    'bw_payment_status' => 'pending',
                ],
                ['%d', '%s', '%s']
            );
        }

        /**
         * Mark a pending PayPal appointment as cancelled with failed payment.
         *
         * @param int    $appointment_id   Appointment ID.
         * @param string $appointment_hash Appointment hash from create-order response.
         * @return true|\WP_Error
         */
        public static function cancel_paypal_payment($appointment_id, $appointment_hash = '')
        {
            global $wpdb;

            $appointment_id = absint($appointment_id);
            if ($appointment_id <= 0) {
                return new \WP_Error('invalid_request', esc_html__('Invalid appointment.', 'bookwellnow-appointment-booking'));
            }

            $appointment_hash = sanitize_text_field((string) $appointment_hash);
            if ($appointment_hash === '') {
                return new \WP_Error('invalid_request', esc_html__('Invalid appointment token.', 'bookwellnow-appointment-booking'));
            }

            $bwdb = new BookWellAB_Database();
            $appt_table = esc_sql($bwdb->get_table_name('appointments'));

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $appointment = $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM $appt_table WHERE bw_appointment_id = %d", $appointment_id),
                ARRAY_A
            );

            if (empty($appointment)) {
                return new \WP_Error('not_found', esc_html__('Appointment not found.', 'bookwellnow-appointment-booking'));
            }

            $stored_hash = isset($appointment['bw_appointment_hash']) ? (string) $appointment['bw_appointment_hash'] : '';
            if ($stored_hash === '' || !hash_equals($stored_hash, $appointment_hash)) {
                return new \WP_Error('invalid_hash', esc_html__('Invalid appointment token.', 'bookwellnow-appointment-booking'));
            }

            if (($appointment['bw_payment_gateway'] ?? '') !== 'paypal') {
                return new \WP_Error('invalid_gateway', esc_html__('Invalid payment gateway for this appointment.', 'bookwellnow-appointment-booking'));
            }

            $payment_status = isset($appointment['bw_payment_status']) ? sanitize_text_field($appointment['bw_payment_status']) : '';
            if ($payment_status === 'received') {
                return true;
            }

            if ($payment_status === 'failed' && ($appointment['bw_status'] ?? '') === 'cancelled') {
                return true;
            }

            if ($payment_status !== 'pending') {
                return new \WP_Error('invalid_state', esc_html__('This appointment cannot be cancelled.', 'bookwellnow-appointment-booking'));
            }

            $updated = $wpdb->update(
                $appt_table,
                [
                    'bw_status' => 'cancelled',
                    'bw_payment_status' => 'failed',
                ],
                ['bw_appointment_id' => $appointment_id],
                ['%s', '%s'],
                ['%d']
            );

            if ($updated === false) {
                return new \WP_Error('update_failed', esc_html__('Unable to update appointment status.', 'bookwellnow-appointment-booking'));
            }

            return true;
        }

        /**
         * Finalize a PayPal booking after server-side order verification.
         *
         * @param int    $appointment_id  Appointment ID.
         * @param string $transaction_id  PayPal capture ID.
         * @param float  $amount          Captured amount.
         * @param string $currency        Currency code.
         * @return true|\WP_Error
         */
        public static function confirm_paypal_payment($appointment_id, $transaction_id, $amount, $currency)
        {
            global $wpdb;

            $appointment_id = absint($appointment_id);
            $transaction_id = sanitize_text_field((string) $transaction_id);
            if ($appointment_id <= 0 || $transaction_id === '') {
                return new \WP_Error('invalid_request', esc_html__('Invalid payment confirmation request.', 'bookwellnow-appointment-booking'));
            }

            $bwdb = new BookWellAB_Database();
            $appt_table = esc_sql($bwdb->get_table_name('appointments'));

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $appointment = $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM $appt_table WHERE bw_appointment_id = %d", $appointment_id),
                ARRAY_A
            );

            if (empty($appointment)) {
                return new \WP_Error('not_found', esc_html__('Appointment not found.', 'bookwellnow-appointment-booking'));
            }

            if (($appointment['bw_payment_gateway'] ?? '') !== 'paypal') {
                return new \WP_Error('invalid_gateway', esc_html__('Invalid payment gateway for this appointment.', 'bookwellnow-appointment-booking'));
            }

            if (($appointment['bw_payment_status'] ?? '') === 'received') {
                return true;
            }

            $payment_status = isset($appointment['bw_payment_status']) ? sanitize_text_field($appointment['bw_payment_status']) : '';
            $appointment_status = isset($appointment['bw_status']) ? sanitize_text_field($appointment['bw_status']) : '';
            if ($appointment_status === 'cancelled' || $payment_status === 'failed') {
                return new \WP_Error('invalid_state', esc_html__('This appointment cannot be confirmed.', 'bookwellnow-appointment-booking'));
            }

            if ($payment_status !== 'pending') {
                return new \WP_Error('invalid_state', esc_html__('This appointment cannot be confirmed.', 'bookwellnow-appointment-booking'));
            }

            $expected_amount = (float) ($appointment['bw_price'] ?? 0);
            $currency = strtoupper(sanitize_text_field((string) $currency));
            $expected_currency = BookWellAB_Paypal::get_frontend_currency();
            if ($currency !== '' && $expected_currency !== '' && $currency !== $expected_currency) {
                return new \WP_Error('currency_mismatch', esc_html__('PayPal currency does not match.', 'bookwellnow-appointment-booking'));
            }

            if (abs($expected_amount - (float) $amount) > 0.01) {
                return new \WP_Error('amount_mismatch', esc_html__('PayPal amount does not match the appointment total.', 'bookwellnow-appointment-booking'));
            }

            $settings = BookWellAB_Settings::get_settings();
            $default_status = isset($settings['general']['default_status']) ? sanitize_text_field($settings['general']['default_status']) : 'pending';

            $updated = $wpdb->update(
                $appt_table,
                [
                    'bw_payment_status' => 'received',
                    'bw_payment_transaction_id' => $transaction_id,
                    'bw_status' => $default_status,
                ],
                ['bw_appointment_id' => $appointment_id],
                ['%s', '%s', '%s'],
                ['%d']
            );

            if ($updated === false) {
                return new \WP_Error('update_failed', esc_html__('Unable to update appointment payment status.', 'bookwellnow-appointment-booking'));
            }

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $appointment = $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM $appt_table WHERE bw_appointment_id = %d", $appointment_id),
                ARRAY_A
            );

            /**
             * After PayPal payment is confirmed and appointment status is updated.
             * Integrations (Zoom) should run before notifications so placeholders resolve.
             *
             * @param int                  $appointment_id Appointment ID.
             * @param array<string, mixed> $appointment    Appointment row.
             */
            do_action('bookwellab_paypal_appointment_confirmed', $appointment_id, is_array($appointment) ? $appointment : []);

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $appointment = $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM $appt_table WHERE bw_appointment_id = %d", $appointment_id),
                ARRAY_A
            );

            $name_parts = self::split_customer_name((string) ($appointment['bw_customer_name'] ?? ''));
            $notification_data = [
                'bw_appointment_id' => $appointment_id,
                'bw_customer_name' => $appointment['bw_customer_name'] ?? '',
                'bw_customer_first_name' => $name_parts['first_name'],
                'bw_customer_last_name' => $name_parts['last_name'],
                'bw_customer_email' => $appointment['bw_customer_email'] ?? '',
                'bw_customer_phone' => $appointment['bw_customer_phone'] ?? '',
                'bw_service_name' => $appointment['bw_service_name'] ?? '',
                'bw_staff_name' => $appointment['bw_staff_name'] ?? '',
                'bw_staff_id' => isset($appointment['bw_staff_id']) ? (int) $appointment['bw_staff_id'] : 0,
                'bw_date' => $appointment['bw_date'] ?? '',
                'bw_time' => $appointment['bw_time'] ?? '',
                'bw_price' => $appointment['bw_price'] ?? 0,
                'bw_appointment_hash' => $appointment['bw_appointment_hash'] ?? '',
                'bw_zoom_join_url' => $appointment['bw_zoom_join_url'] ?? '',
                'bw_zoom_host_url' => $appointment['bw_zoom_host_url'] ?? '',
            ];

            BookWellAB_Notifications::get_instance()->send_notification(
                self::get_notification_trigger_for_status($default_status),
                $notification_data
            );

            return true;
        }

        /**
         * Map appointment status to the matching notification trigger.
         *
         * @param string $status Appointment status.
         * @return string
         */
        private static function get_notification_trigger_for_status($status)
        {
            $status = strtolower(trim(sanitize_text_field((string) $status)));

            if ($status === 'cancelled' || $status === 'canceled') {
                return 'on_cancellation';
            }

            return ($status === 'approved') ? 'on_approval' : 'on_pending';
        }

        /**
         * Cancel stale pending PayPal appointments that were abandoned during checkout.
         *
         * @param int $minutes Age threshold in minutes.
         * @return int Number of appointments cancelled.
         */
        public static function cleanup_stale_pending_paypal_appointments($minutes = 60)
        {
            global $wpdb;

            $minutes = max(15, absint($minutes));
            $cutoff = gmdate('Y-m-d H:i:s', time() - ($minutes * MINUTE_IN_SECONDS));

            $bwdb = new BookWellAB_Database();
            $appt_table = esc_sql($bwdb->get_table_name('appointments'));

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT bw_appointment_id, bw_appointment_hash FROM {$appt_table}
                     WHERE bw_payment_gateway = %s
                     AND bw_payment_status = %s
                     AND bw_status = %s
                     AND bw_created_at < %s",
                    'paypal',
                    'pending',
                    'pending',
                    $cutoff
                ),
                ARRAY_A
            );

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

            $cancelled = 0;
            foreach ($rows as $row) {
                $appointment_id = isset($row['bw_appointment_id']) ? absint($row['bw_appointment_id']) : 0;
                $appointment_hash = isset($row['bw_appointment_hash']) ? sanitize_text_field((string) $row['bw_appointment_hash']) : '';
                if ($appointment_id <= 0 || $appointment_hash === '') {
                    continue;
                }

                $result = self::cancel_paypal_payment($appointment_id, $appointment_hash);
                if (!is_wp_error($result)) {
                    $cancelled++;
                }
            }

            return $cancelled;
        }

        /**
         * Build a public cancel URL for email notifications (no login required).
         *
         * @param int    $appointment_id   Appointment ID.
         * @param string $appointment_hash Appointment hash.
         * @return string
         */
        public static function get_public_cancel_appointment_url($appointment_id, $appointment_hash = '')
        {
            $appointment_id = absint($appointment_id);
            $appointment_hash = sanitize_text_field((string) $appointment_hash);

            if ($appointment_id <= 0 || $appointment_hash === '') {
                return '';
            }

            return add_query_arg(
                [
                    'bookwellab_cancel' => '1',
                    'appt_id' => $appointment_id,
                    'hash' => $appointment_hash,
                ],
                home_url('/')
            );
        }

        /**
         * HTML cancel link for notification templates.
         *
         * @param int    $appointment_id   Appointment ID.
         * @param string $appointment_hash Appointment hash.
         * @return string
         */
        public static function get_public_cancel_appointment_link_html($appointment_id, $appointment_hash = '')
        {
            $url = self::get_public_cancel_appointment_url($appointment_id, $appointment_hash);
            if ($url === '') {
                return '';
            }

            return sprintf(
                '<a href="%s" target="_blank" rel="noopener noreferrer">%s</a>',
                esc_url($url),
                esc_html__('Cancel appointment', 'bookwellnow-appointment-booking')
            );
        }

        /**
         * Cancel an appointment using the public hash token (guest-safe).
         *
         * @param int    $appointment_id   Appointment ID.
         * @param string $appointment_hash Appointment hash from email link.
         * @return true|\WP_Error
         */
        public static function cancel_appointment_by_hash($appointment_id, $appointment_hash = '')
        {
            global $wpdb;

            $appointment_id = absint($appointment_id);
            if ($appointment_id <= 0) {
                return new \WP_Error('invalid_request', esc_html__('Invalid appointment.', 'bookwellnow-appointment-booking'));
            }

            $appointment_hash = sanitize_text_field((string) $appointment_hash);
            if ($appointment_hash === '') {
                return new \WP_Error('invalid_request', esc_html__('Invalid appointment token.', 'bookwellnow-appointment-booking'));
            }

            $bwdb = new BookWellAB_Database();
            $appt_table = esc_sql($bwdb->get_table_name('appointments'));

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $appointment = $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM $appt_table WHERE bw_appointment_id = %d", $appointment_id),
                ARRAY_A
            );

            if (empty($appointment)) {
                return new \WP_Error('not_found', esc_html__('Appointment not found.', 'bookwellnow-appointment-booking'));
            }

            $stored_hash = isset($appointment['bw_appointment_hash']) ? (string) $appointment['bw_appointment_hash'] : '';
            if ($stored_hash === '' || !hash_equals($stored_hash, $appointment_hash)) {
                return new \WP_Error('invalid_hash', esc_html__('Invalid or expired cancellation link.', 'bookwellnow-appointment-booking'));
            }

            $status = strtolower(trim((string) ($appointment['bw_status'] ?? '')));
            if (in_array($status, ['cancelled', 'canceled'], true)) {
                return new \WP_Error(
                    'already_cancelled',
                    esc_html__('This appointment has already been cancelled.', 'bookwellnow-appointment-booking')
                );
            }

            if (!in_array($status, ['approved', 'pending'], true)) {
                return new \WP_Error('invalid_state', esc_html__('This appointment cannot be cancelled.', 'bookwellnow-appointment-booking'));
            }

            $updated = $wpdb->update(
                $appt_table,
                ['bw_status' => 'cancelled'],
                ['bw_appointment_id' => $appointment_id],
                ['%s'],
                ['%d']
            );

            if ($updated === false) {
                return new \WP_Error('update_failed', esc_html__('Unable to cancel this appointment.', 'bookwellnow-appointment-booking'));
            }

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $updated_appointment = $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM $appt_table WHERE bw_appointment_id = %d", $appointment_id),
                ARRAY_A
            );

            if (is_array($updated_appointment) && !empty($updated_appointment['bw_appointment_id'])) {
                \BookWellAB\BookWellAB_Notifications::get_instance()->send_notification('on_cancellation', $updated_appointment);
                /**
                 * Fires after a customer cancels an appointment via the public email link.
                 *
                 * @param int                  $appointment_id Appointment ID.
                 * @param array<string, mixed> $appointment    Updated appointment row.
                 */
                do_action('bookwellab_appointment_cancelled_by_customer', $appointment_id, $updated_appointment);
            }

            wp_cache_delete('public_confirmation_' . $appointment_id . '_' . md5($appointment_hash), 'bookwellab_booking');

            return true;
        }

        /**
         * Cancel an appointment from the logged-in customer panel.
         *
         * @param int $appointment_id Appointment ID.
         * @param int $customer_id    Customer ID.
         * @return true|\WP_Error
         */
        public static function cancel_appointment_for_customer($appointment_id, $customer_id)
        {
            global $wpdb;

            $appointment_id = absint($appointment_id);
            $customer_id = absint($customer_id);
            if ($appointment_id <= 0 || $customer_id <= 0) {
                return new \WP_Error('invalid_request', esc_html__('Invalid appointment.', 'bookwellnow-appointment-booking'));
            }

            $bwdb = new BookWellAB_Database();
            $appt_table = esc_sql($bwdb->get_table_name('appointments'));

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $appointment = $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT * FROM $appt_table WHERE bw_appointment_id = %d AND bw_customer_id = %d",
                    $appointment_id,
                    $customer_id
                ),
                ARRAY_A
            );

            if (empty($appointment)) {
                return new \WP_Error('not_found', esc_html__('Appointment not found.', 'bookwellnow-appointment-booking'));
            }

            $status = strtolower(trim((string) ($appointment['bw_status'] ?? '')));
            if (in_array($status, ['cancelled', 'canceled'], true)) {
                return new \WP_Error(
                    'already_cancelled',
                    esc_html__('This appointment has already been cancelled.', 'bookwellnow-appointment-booking')
                );
            }

            if (!in_array($status, ['approved', 'pending'], true)) {
                return new \WP_Error('invalid_state', esc_html__('This appointment cannot be cancelled.', 'bookwellnow-appointment-booking'));
            }

            $updated = $wpdb->update(
                $appt_table,
                ['bw_status' => 'cancelled'],
                ['bw_appointment_id' => $appointment_id],
                ['%s'],
                ['%d']
            );

            if ($updated === false) {
                return new \WP_Error('update_failed', esc_html__('Unable to cancel this appointment.', 'bookwellnow-appointment-booking'));
            }

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $updated_appointment = $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM $appt_table WHERE bw_appointment_id = %d", $appointment_id),
                ARRAY_A
            );

            if (is_array($updated_appointment) && !empty($updated_appointment['bw_appointment_id'])) {
                \BookWellAB\BookWellAB_Notifications::get_instance()->send_notification('on_cancellation', $updated_appointment);
                do_action('bookwellab_appointment_cancelled_by_customer', $appointment_id, $updated_appointment);
            }

            return true;
        }

        /**
         * @param string $full_name Customer full name.
         * @return array{first_name: string, last_name: string}
         */
        private static function split_customer_name($full_name)
        {
            $full_name = trim((string) $full_name);
            if ($full_name === '') {
                return ['first_name' => '', 'last_name' => ''];
            }

            $parts = preg_split('/\s+/', $full_name, 2);
            return [
                'first_name' => isset($parts[0]) ? $parts[0] : '',
                'last_name' => isset($parts[1]) ? $parts[1] : '',
            ];
        }
    }
}
// phpcs:enable
