<?php
declare(strict_types=1);
/**
 * Booking Service
 * 
 * Handles appointment/service bookings with calendar management
 * - Time slot availability checking
 * - Booking creation and management
 * - Calendar integration
 * - Buffer times between appointments
 * - Staff assignment (future)
 * 
 * @package Instinctor
 * @since 1.9.19
 */

namespace Instinctor\Services;

use Instinctor\Core\Config;
use Instinctor\Core\DB;
use Instinctor\Core\Router;
use Instinctor\Core\Mailer;

if ( ! defined( 'ABSPATH' ) ) { exit; }


class BookingService
{
    private static ?self $instance = null;
    
    public static function instance(): self
    {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    private function __construct()
    {
        // Register REST endpoints.
        // In WP mode, rest_api_init fires before dispatch. In Standalone mode,
        // that action never fires, so we register directly.
        add_action('rest_api_init', [$this, 'registerEndpoints']);
        if (defined('INSTINCTOR_ENV') && INSTINCTOR_ENV === 'standalone') {
            $this->registerEndpoints();
        }
    }

    /**
     * Register REST API endpoints
     */
    public function registerEndpoints(): void
    {
        Router::restRaw('/bookings/availability', [
            'methods' => 'GET',
            'callback' => [$this, 'getAvailability'],
            'permission_callback' => '__return_true'
        ]);

        Router::restRaw('/bookings/slots', [
            'methods' => 'GET',
            'callback' => [$this, 'getTimeSlots'],
            'permission_callback' => '__return_true'
        ]);

        Router::restRaw('/bookings/staff', [
            'methods' => 'GET',
            'callback' => [$this, 'getStaffForService'],
            'permission_callback' => '__return_true'
        ]);

        Router::restRaw('/bookings/create', [
            'methods' => 'POST',
            'callback' => [$this, 'handleCreateBooking'],
            'permission_callback' => '__return_true'
        ]);
    }

    /**
     * Get default booking settings
     */
    public function getDefaultSettings(): array
    {
        return [
            'default_duration' => 60, // minutes
            'buffer_before' => 0, // minutes before appointment
            'buffer_after' => 15, // minutes after appointment
            'min_advance' => 0, // hours in advance required (0 = bookable immediately)
            'max_advance' => 60, // days in advance allowed
            'slot_interval' => 30, // minutes between slot starts
            'allow_cancellation' => true,
            'cancellation_deadline' => 24, // hours before
            'require_payment' => true,
            'auto_refund_within_window' => true, // refund the deposit when a booking is cancelled inside the cancellation window
            'send_reminders' => true,
            'reminder_hours' => 24,
            'notify_sms' => '', // owner mobile — texted on each new booking request (empty = off)
            'notify_customer_sms' => false, // also text the customer an acknowledgement (if they leave a phone)
            'business_hours' => [
                'monday' => ['enabled' => true, 'start' => '09:00', 'end' => '17:00'],
                'tuesday' => ['enabled' => true, 'start' => '09:00', 'end' => '17:00'],
                'wednesday' => ['enabled' => true, 'start' => '09:00', 'end' => '17:00'],
                'thursday' => ['enabled' => true, 'start' => '09:00', 'end' => '17:00'],
                'friday' => ['enabled' => true, 'start' => '09:00', 'end' => '17:00'],
                'saturday' => ['enabled' => false, 'start' => '10:00', 'end' => '14:00'],
                'sunday' => ['enabled' => false, 'start' => '10:00', 'end' => '14:00'],
            ],
            'blocked_dates' => [], // Array of Y-m-d dates
            'timezone' => wp_timezone_string()
        ];
    }

    /**
     * Get booking settings
     */
    public function getSettings(): array
    {
        $defaults = $this->getDefaultSettings();
        $saved = Config::get('instinctor_booking_settings', []);
        return array_merge($defaults, $saved);
    }

    /**
     * Save booking settings
     */
    public function saveSettings(array $settings): bool
    {
        return Config::set('instinctor_booking_settings', $settings);
    }

    /**
     * Check if a product is bookable
     */
    public function isProductBookable(int $productId): bool
    {
        $p = DB::prefix();
        $meta = DB::get_var(DB::prepare(
            "SELECT meta FROM {$p}instinctor_products WHERE id = %d",
            $productId
        ));
        
        $metaData = json_decode($meta ?? '{}', true);
        return !empty($metaData['is_bookable']);
    }

    /**
     * Get product booking settings
     */
    public function getProductBookingSettings(int $productId): array
    {
        $p = DB::prefix();
        $meta = DB::get_var(DB::prepare(
            "SELECT meta FROM {$p}instinctor_products WHERE id = %d",
            $productId
        ));
        
        $metaData = json_decode($meta ?? '{}', true);
        $globalSettings = $this->getSettings();
        
        return [
            'duration' => $metaData['booking_duration'] ?? $globalSettings['default_duration'],
            'buffer_before' => $metaData['booking_buffer_before'] ?? $globalSettings['buffer_before'],
            'buffer_after' => $metaData['booking_buffer_after'] ?? $globalSettings['buffer_after'],
            'max_per_slot' => $metaData['booking_max_per_slot'] ?? 1,
        ];
    }

    /**
     * Get available dates for a product
     */
    public function getAvailability(\WP_REST_Request $request): \WP_REST_Response
    {
        $p = DB::prefix();
        $productId = intval($request->get_param('product_id'));
        // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
        $month = $request->get_param('month') ?? date('Y-m');
        // phpcs:enable WordPress.DateTime.RestrictedFunctions
        
        if (!$productId) {
            return new \WP_REST_Response(['error' => 'Product ID required'], 400);
        }

        $settings = $this->getSettings();
        $productSettings = $this->getProductBookingSettings($productId);
        
        // Get start and end of month
        $startDate = new \DateTime($month . '-01');
        $endDate = clone $startDate;
        $endDate->modify('last day of this month');
        
        // Get existing bookings for this product in the month
        $existingBookings = DB::get_results(DB::prepare(
            "SELECT start_time, end_time FROM {$p}instinctor_bookings 
             WHERE product_id = %d AND status NOT IN ('cancelled') 
             AND start_time >= %s AND start_time <= %s",
            $productId,
            $startDate->format('Y-m-d 00:00:00'),
            $endDate->format('Y-m-d 23:59:59')
        ));

        // Build availability map
        $availability = [];
        $now = new \DateTime();
        $minAdvance = clone $now;
        $minAdvance->modify('+' . $settings['min_advance'] . ' hours');
        $maxAdvance = clone $now;
        $maxAdvance->modify('+' . $settings['max_advance'] . ' days');

        $current = clone $startDate;
        while ($current <= $endDate) {
            $dateStr = $current->format('Y-m-d');
            $dayName = strtolower($current->format('l'));
            $businessHours = $settings['business_hours'][$dayName] ?? null;
            
            $available = true;
            $reason = null;
            
            // Check if day is enabled
            if (!$businessHours || !$businessHours['enabled']) {
                $available = false;
                $reason = 'closed';
            }
            // Check if date is blocked
            elseif (in_array($dateStr, $settings['blocked_dates'] ?? [])) {
                $available = false;
                $reason = 'blocked';
            }
            // Check min advance
            elseif ($current < $minAdvance) {
                $available = false;
                $reason = 'too_soon';
            }
            // Check max advance
            elseif ($current > $maxAdvance) {
                $available = false;
                $reason = 'too_far';
            }
            
            $availability[$dateStr] = [
                'available' => $available,
                'reason' => $reason,
                'hours' => $businessHours
            ];
            
            $current->modify('+1 day');
        }

        return new \WP_REST_Response([
            'product_id' => $productId,
            'month' => $month,
            'availability' => $availability,
            'settings' => [
                'duration' => $productSettings['duration'],
                'timezone' => $settings['timezone']
            ]
        ]);
    }

    /**
     * Get available time slots for a specific date
     */
    /**
     * Return available slots for a service on a given date.
     * Direct variant of getTimeSlots() — no REST wrapper.
     *
     * @param  int    $serviceId
     * @param  string $date       Y-m-d
     * @return array  [ ['time'=>'HH:mm', 'display'=>'g:i A', 'available'=>bool], ... ]
     */
    public function getSlotsForDate(int $serviceId, string $date, int $staffId = 0): array
    {
        $p = DB::prefix();

        $service = DB::get_row(DB::prepare(
            "SELECT duration, buffer_before, buffer_after, capacity, availability
             FROM {$p}instinctor_services
             WHERE id = %d AND status = 'active'",
            $serviceId
        ), ARRAY_A);

        if (!$service) return [];

        $settings     = $this->getSettings();
        $duration     = intval($service['duration'] ?: 60);
        $bufferBefore = intval($service['buffer_before'] ?: 0);
        $bufferAfter  = intval($service['buffer_after'] ?: 0);

        $availability = $service['availability'] ? json_decode($service['availability'], true) : null;
        $dateObj      = new \DateTime($date);
        $dayName      = strtolower($dateObj->format('l'));

        if ($availability) {
            $dayData = $availability[$dayName] ?? null;
            if (!$dayData || empty($dayData['open'])) return [];
            $dayStart = $dayData['from'] ?? '09:00';
            $dayEnd   = $dayData['to']   ?? '17:00';
        } else {
            $businessHours = $settings['business_hours'][$dayName] ?? null;
            if (!$businessHours || !$businessHours['enabled']) return [];
            $dayStart = $businessHours['start'];
            $dayEnd   = $businessHours['end'];
        }

        // Staff Phase 1: when the shop has bookable team members, availability
        // is per-staff (their working hours + their own bookings), not
        // service-wide capacity. Zero staff => fall through to legacy below.
        if ($this->anyBookableStaffExist()) {
            $slotInterval = intval($settings['slot_interval'] ?? $duration);
            $minAdvance   = time() + (intval($settings['min_advance'] ?? 0) * 3600);
            return $this->computeStaffSlots($serviceId, $date, $staffId, $dayStart, $dayEnd, $duration, $slotInterval, $bufferBefore, $bufferAfter, $minAdvance);
        }

        $existingBookings = DB::get_results(DB::prepare(
            "SELECT start_time, end_time FROM {$p}instinctor_bookings
             WHERE product_id = %d AND status NOT IN ('cancelled')
             AND DATE(start_time) = %s",
            $serviceId, $date
        ));

        $bookedTimes = [];
        foreach ($existingBookings as $b) {
            $bookedTimes[] = [
                'start' => strtotime($b->start_time),
                'end'   => strtotime($b->end_time),
            ];
        }

        $slotInterval = intval($settings['slot_interval'] ?? $duration);
        $minAdvance   = time() + (intval($settings['min_advance'] ?? 0) * 3600);
        $startTs      = strtotime($date . ' ' . $dayStart);
        $endTs        = strtotime($date . ' ' . $dayEnd);
        $durationSec  = $duration * 60;
        $intervalSec  = $slotInterval * 60;
        $bufBefore    = $bufferBefore * 60;
        $bufAfter     = $bufferAfter * 60;

        $slots = [];
        $cur   = $startTs;
        while ($cur + $durationSec <= $endTs) {
            $slotEnd   = $cur + $durationSec;
            $available = ($cur >= $minAdvance);
            if ($available) {
                foreach ($bookedTimes as $booked) {
                    if (($cur - $bufBefore) < $booked['end'] && ($slotEnd + $bufAfter) > $booked['start']) {
                        $available = false;
                        break;
                    }
                }
            }
            $slots[] = [
                // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
                'time'      => date('H:i', $cur),
                // phpcs:enable WordPress.DateTime.RestrictedFunctions
                // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
                'display'   => date('g:i A', $cur),
                // phpcs:enable WordPress.DateTime.RestrictedFunctions
                'available' => $available,
            ];
            $cur += $intervalSec;
        }

        return $slots;
    }

    /**
     * Staff Phase 1 — is there at least one active, bookable team member?
     * When false, every booking path falls through to the original
     * service-level (capacity + shop-hours) logic, so a shop with no staff
     * behaves exactly as before. Cached per request. */
    private $_hasBookableStaff = null;
    private function anyBookableStaffExist(): bool
    {
        if ($this->_hasBookableStaff !== null) return $this->_hasBookableStaff;
        $p = DB::prefix();
        $n = (int) DB::get_var("SELECT COUNT(*) FROM {$p}instinctor_staff WHERE status = 'active' AND bookable = 1");
        return $this->_hasBookableStaff = ($n > 0);
    }

    /**
     * Qualified, bookable staff for a service: those explicitly assigned in
     * instinctor_service_staff, or ALL bookable staff when the service has no
     * assignments. Returns [] when no staff qualify. */
    private function getBookableStaffForService(int $serviceId): array
    {
        $p = DB::prefix();
        $all = DB::get_results(
            "SELECT id, name, title, photo_url, calendar_color, commission_rate
             FROM {$p}instinctor_staff
             WHERE status = 'active' AND bookable = 1
             ORDER BY sort_order ASC, id ASC",
            ARRAY_A
        );
        if (!$all) return [];
        $rows = DB::get_results(DB::prepare(
            "SELECT staff_id FROM {$p}instinctor_service_staff WHERE service_id = %d",
            $serviceId
        ));
        if ($rows) {
            $set = [];
            foreach ($rows as $r) $set[(int) $r->staff_id] = true;
            $all = array_values(array_filter($all, fn($s) => isset($set[(int) $s['id']])));
        }
        return $all;
    }

    /**
     * Public REST handler: staff available for a given service.
     * Used by the frontend booking widget to populate the stylist picker.
     */
    public function getStaffForService(\WP_REST_Request $request): \WP_REST_Response
    {
        $serviceId = intval($request->get_param('service_id') ?: 0);
        if (!$serviceId) {
            return new \WP_REST_Response(['staff' => []]);
        }
        return new \WP_REST_Response(['staff' => array_values($this->getBookableStaffForService($serviceId))]);
    }

    /**
     * Staff-aware slot generation. A slot is available when at least one
     * qualified staff member (or the specifically-requested one) is (a) within
     * a working block for that weekday and (b) free of their own bookings and
     * time-off across ALL services, buffers included. The service's day window
     * (dayStart/dayEnd) is the outer grid bound; staff working hours are the
     * real gate. Returns the same [time, display, available] shape as the
     * legacy generators. */
    private function computeStaffSlots(
        int $serviceId, string $date, int $staffId,
        string $dayStart, string $dayEnd,
        int $durMin, int $intMin, int $bufBMin, int $bufAMin, int $minAdvanceTs
    ): array {
        $p   = DB::prefix();
        $dow = (int) (new \DateTime($date))->format('w'); // 0=Sun … 6=Sat

        $staff = $this->getBookableStaffForService($serviceId);
        if ($staffId > 0) {
            $staff = array_values(array_filter($staff, fn($s) => (int) $s['id'] === $staffId));
        }

        $ids = array_map(fn($s) => (int) $s['id'], $staff);
        $blocks = [];  // staff_id => [[startTs,endTs], …] working blocks that day
        $busy   = [];  // staff_id => [[startTs,endTs], …] bookings + time-off
        if ($ids) {
            $in = implode(',', array_map('intval', $ids));
            foreach (DB::get_results(
                "SELECT staff_id, start_time, end_time FROM {$p}instinctor_staff_hours
                 WHERE day_of_week = {$dow} AND staff_id IN ({$in})"
            ) as $h) {
                $blocks[(int) $h->staff_id][] = [strtotime($date . ' ' . $h->start_time), strtotime($date . ' ' . $h->end_time)];
            }
            foreach (DB::get_results(DB::prepare(
                "SELECT staff_id, start_time, end_time FROM {$p}instinctor_bookings
                 WHERE staff_id IN ({$in}) AND status NOT IN ('cancelled') AND DATE(start_time) = %s",
                $date
            )) as $b) {
                $busy[(int) $b->staff_id][] = [strtotime($b->start_time), strtotime($b->end_time)];
            }
            $dayS = $date . ' 00:00:00';
            $dayE = $date . ' 23:59:59';
            foreach (DB::get_results(DB::prepare(
                "SELECT staff_id, start_datetime, end_datetime FROM {$p}instinctor_staff_timeoff
                 WHERE staff_id IN ({$in}) AND start_datetime <= %s AND end_datetime >= %s",
                $dayE, $dayS
            )) as $o) {
                $busy[(int) $o->staff_id][] = [strtotime($o->start_datetime), strtotime($o->end_datetime)];
            }
        }

        $slots   = [];
        $cur     = strtotime($date . ' ' . $dayStart);
        $end     = strtotime($date . ' ' . $dayEnd);
        $durSec  = $durMin * 60;
        $intSec  = max(60, $intMin * 60);
        $bB      = $bufBMin * 60;
        $bA      = $bufAMin * 60;

        while ($cur + $durSec <= $end) {
            $slotEnd = $cur + $durSec;
            $available = false;
            if ($cur >= $minAdvanceTs) {
                foreach ($staff as $s) {
                    $sid = (int) $s['id'];
                    $inHours = false;
                    foreach ($blocks[$sid] ?? [] as $blk) {
                        if ($cur >= $blk[0] && $slotEnd <= $blk[1]) { $inHours = true; break; }
                    }
                    if (!$inHours) continue;
                    $free = true;
                    foreach ($busy[$sid] ?? [] as $bz) {
                        if (($cur - $bB) < $bz[1] && ($slotEnd + $bA) > $bz[0]) { $free = false; break; }
                    }
                    if ($free) { $available = true; break; }
                }
            }
            // phpcs:disable WordPress.DateTime.RestrictedFunctions -- local-timezone formatting intentional (matches legacy generators).
            $slots[] = ['time' => date('H:i', $cur), 'display' => date('g:i A', $cur), 'available' => $available];
            // phpcs:enable WordPress.DateTime.RestrictedFunctions
            $cur += $intSec;
        }
        return $slots;
    }

    /**
     * Reservation availability: covers-per-slot, party-size aware. A slot is
     * open when the sum of already-reserved guests overlapping it, plus the
     * requested party, fits the service capacity (total covers per slot). Used
     * by restaurant reservations; the appointment path (getSlotsForDate) is
     * left untouched so salon/services behavior is unchanged.
     */
    public function getReservationSlots(int $serviceId, string $date, int $partySize): array
    {
        $p         = DB::prefix();
        $partySize = max(1, $partySize);

        $service = DB::get_row(DB::prepare(
            "SELECT duration, capacity, availability
             FROM {$p}instinctor_services
             WHERE id = %d AND status = 'active'",
            $serviceId
        ), ARRAY_A);
        if (!$service) return [];

        $settings = $this->getSettings();
        $duration = intval($service['duration'] ?: 90);
        $capacity = max(1, intval($service['capacity'] ?: 1));

        $availability = $service['availability'] ? json_decode($service['availability'], true) : null;
        $dayName      = strtolower((new \DateTime($date))->format('l'));
        if ($availability) {
            $dayData = $availability[$dayName] ?? null;
            if (!$dayData || empty($dayData['open'])) return [];
            $dayStart = $dayData['from'] ?? '17:00';
            $dayEnd   = $dayData['to']   ?? '22:00';
        } else {
            $businessHours = $settings['business_hours'][$dayName] ?? null;
            if (!$businessHours || !$businessHours['enabled']) return [];
            $dayStart = $businessHours['start'];
            $dayEnd   = $businessHours['end'];
        }

        // Existing reservations for the day, each with its party size.
        $existing = DB::get_results(DB::prepare(
            "SELECT start_time, end_time, guest_count FROM {$p}instinctor_bookings
             WHERE product_id = %d AND status NOT IN ('cancelled')
             AND DATE(start_time) = %s",
            $serviceId, $date
        ));
        $booked = [];
        foreach ($existing as $b) {
            $booked[] = ['start' => strtotime($b->start_time), 'end' => strtotime($b->end_time), 'covers' => max(1, (int) $b->guest_count)];
        }

        $minAdvance  = time() + (intval($settings['min_advance'] ?? 0) * 3600);
        $intervalSec = max(300, intval($settings['slot_interval'] ?? 30) * 60);
        $durationSec = $duration * 60;
        $endTs       = strtotime($date . ' ' . $dayEnd);

        $slots = [];
        $cur   = strtotime($date . ' ' . $dayStart);
        while ($cur + $durationSec <= $endTs) {
            $slotEnd = $cur + $durationSec;
            $covers  = 0;
            foreach ($booked as $b) {
                if ($cur < $b['end'] && $slotEnd > $b['start']) $covers += $b['covers'];
            }
            $slots[] = [
                // phpcs:disable WordPress.DateTime.RestrictedFunctions -- local-timezone formatting; gmdate would force UTC.
                'time'      => date('H:i', $cur),
                'display'   => date('g:i A', $cur),
                // phpcs:enable WordPress.DateTime.RestrictedFunctions
                'available' => ($cur >= $minAdvance) && (($covers + $partySize) <= $capacity),
            ];
            $cur += $intervalSec;
        }
        return $slots;
    }

    public function getTimeSlots(\WP_REST_Request $request): \WP_REST_Response
    {
        $p = DB::prefix();
        $productId = intval($request->get_param('service_id') ?: $request->get_param('product_id'));
        $date = $request->get_param('date'); // Y-m-d format

        if (!$productId || !$date) {
            return new \WP_REST_Response(['error' => 'Service ID and date required'], 400);
        }

        // Restaurant reservations: when a party size is supplied, use the
        // covers-per-slot availability instead of the appointment overlap logic.
        $partySize = intval($request->get_param('party_size') ?: 0);
        if ($partySize > 0) {
            return new \WP_REST_Response(['slots' => $this->getReservationSlots($productId, $date, $partySize)]);
        }

        $settings = $this->getSettings();

        // Look up service from instinctor_services
        $service = DB::get_row(DB::prepare(
            "SELECT duration, buffer_before, buffer_after, capacity, availability FROM {$p}instinctor_services WHERE id = %d AND status = 'active'",
            $productId
        ), ARRAY_A);

        if (!$service) {
            return new \WP_REST_Response(['error' => 'Service not found'], 404);
        }

        $productSettings = [
            'duration'      => intval($service['duration'] ?: 60),
            'buffer_before' => intval($service['buffer_before'] ?: 0),
            'buffer_after'  => intval($service['buffer_after'] ?: 0),
            'max_per_slot'  => intval($service['capacity'] ?: 1),
        ];

        // Per-service availability (single start/end + day toggles)
        // Falls back to global business_hours if not set
        $availability = $service['availability'] ? json_decode($service['availability'], true) : null;
        $dateObj = new \DateTime($date);
        $dayName = strtolower($dateObj->format('l'));

        if ($availability) {
            $dayData = $availability[$dayName] ?? null;
            if (!$dayData || empty($dayData['open'])) {
                return new \WP_REST_Response(['slots' => [], 'message' => 'Closed']);
            }
            $dayStart = $dayData['from'] ?? '09:00';
            $dayEnd   = $dayData['to']   ?? '17:00';
        } else {
            // Global fallback
            $businessHours = $settings['business_hours'][$dayName] ?? null;
            if (!$businessHours || !$businessHours['enabled']) {
                return new \WP_REST_Response(['slots' => [], 'message' => 'Closed']);
            }
            $dayStart = $businessHours['start'];
            $dayEnd   = $businessHours['end'];
        }

        // Staff Phase 1: per-staff availability when the shop has team members.
        if ($this->anyBookableStaffExist()) {
            $staffId = intval($request->get_param('staff_id') ?: 0);
            $slots = $this->computeStaffSlots(
                $productId, $date, $staffId, $dayStart, $dayEnd,
                $productSettings['duration'], intval($settings['slot_interval']),
                $productSettings['buffer_before'], $productSettings['buffer_after'],
                time() + ($settings['min_advance'] * 3600)
            );
            return new \WP_REST_Response([
                'product_id' => $productId, 'date' => $date, 'slots' => $slots,
                'duration' => $productSettings['duration'], 'timezone' => $settings['timezone'],
                'staff_id' => $staffId,
            ]);
        }

        // Get existing bookings for this date
        $existingBookings = DB::get_results(DB::prepare(
            "SELECT start_time, end_time FROM {$p}instinctor_bookings
             WHERE product_id = %d AND status NOT IN ('cancelled')
             AND DATE(start_time) = %s",
            $productId,
            $date
        ));

        // Build booked times array
        $bookedTimes = [];
        foreach ($existingBookings as $booking) {
            $bookedTimes[] = [
                'start' => strtotime($booking->start_time),
                'end' => strtotime($booking->end_time)
            ];
        }

        // Generate time slots
        $slots = [];
        $startTime = strtotime($date . ' ' . $dayStart);
        $endTime   = strtotime($date . ' ' . $dayEnd);
        $duration = $productSettings['duration'] * 60; // Convert to seconds
        $interval = $settings['slot_interval'] * 60;
        $bufferBefore = $productSettings['buffer_before'] * 60;
        $bufferAfter = $productSettings['buffer_after'] * 60;
        $now = time();
        $minAdvance = $now + ($settings['min_advance'] * 3600);

        $currentSlot = $startTime;
        while ($currentSlot + $duration <= $endTime) {
            $slotEnd = $currentSlot + $duration;
            $slotWithBuffer = [
                'start' => $currentSlot - $bufferBefore,
                'end' => $slotEnd + $bufferAfter
            ];
            
            $available = true;
            
            // Check if slot is in the past or too soon
            if ($currentSlot < $minAdvance) {
                $available = false;
            }
            
            // Check for conflicts with existing bookings
            if ($available) {
                foreach ($bookedTimes as $booked) {
                    // Check if slots overlap (including buffers)
                    if ($slotWithBuffer['start'] < $booked['end'] && $slotWithBuffer['end'] > $booked['start']) {
                        $available = false;
                        break;
                    }
                }
            }
            
            $slots[] = [
                // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
                'time' => date('H:i', $currentSlot),
                // phpcs:enable WordPress.DateTime.RestrictedFunctions
                // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
                'display' => date('g:i A', $currentSlot),
                // phpcs:enable WordPress.DateTime.RestrictedFunctions
                'available' => $available
            ];
            
            $currentSlot += $interval;
        }

        return new \WP_REST_Response([
            'product_id' => $productId,
            'date' => $date,
            'slots' => $slots,
            'duration' => $productSettings['duration'],
            'timezone' => $settings['timezone']
        ]);
    }

    /**
     * Create a booking
     */
    public function createBooking(array $data): array
    {
        $p = DB::prefix();
        $productId = intval($data['product_id'] ?? 0);
        $customerId = intval($data['customer_id'] ?? 0);
        $date = sanitize_text_field($data['date'] ?? '');
        $time = sanitize_text_field($data['time'] ?? '');
        $orderId = intval($data['order_id'] ?? 0);
        
        if (!$productId || !$customerId || !$date || !$time) {
            return ['success' => false, 'error' => 'Missing required fields'];
        }

        // Get product settings
        $productSettings = $this->getProductBookingSettings($productId);
        $duration = $productSettings['duration'];
        
        // Calculate start and end times
        $startTime = $date . ' ' . $time . ':00';
        $endDateTime = new \DateTime($startTime);
        $endDateTime->modify('+' . $duration . ' minutes');
        $endTime = $endDateTime->format('Y-m-d H:i:s');

        // Verify slot is available
        if (!$this->isSlotAvailable($productId, $startTime, $endTime)) {
            return ['success' => false, 'error' => 'This time slot is no longer available'];
        }

        // Create the booking
        $result = DB::insert($p . 'instinctor_bookings', [
            'product_id' => $productId,
            'customer_id' => $customerId,
            'order_id' => $orderId,
            'owner_id' => \Instinctor\Core\Tenant::currentOwnerId(),
            'status' => $orderId ? 'confirmed' : 'pending',
            'start_time' => $startTime,
            'end_time' => $endTime,
            'timezone' => $this->getSettings()['timezone'],
            'customer_note' => sanitize_textarea_field($data['note'] ?? ''),
            'meta' => json_encode([
                'duration' => $duration,
                'created_via' => $data['created_via'] ?? 'checkout'
            ]),
            'created_at' => current_time('mysql')
        ]);

        if ($result === false) {
            return ['success' => false, 'error' => 'Failed to create booking'];
        }

        $bookingId = DB::insert_id();

        // Send confirmation email
        $this->sendBookingConfirmation($bookingId);

        return [
            'success' => true,
            'booking_id' => $bookingId,
            'start_time' => $startTime,
            'end_time' => $endTime
        ];
    }

    /**
     * REST handler for frontend booking block — service-based flow
     */
    public function handleCreateBooking(\WP_REST_Request $request): \WP_REST_Response
    {
        $p = DB::prefix();
        $body = $request->get_json_params();

        $serviceId = intval($body['service_id'] ?? 0);
        $date      = sanitize_text_field($body['date'] ?? '');
        $time      = sanitize_text_field($body['time'] ?? '');
        $name      = sanitize_text_field($body['name'] ?? '');
        $email     = sanitize_email($body['email'] ?? '');
        $phone     = sanitize_text_field($body['phone'] ?? '');
        $notes     = sanitize_textarea_field($body['notes'] ?? '');
        $guests    = max(1, intval($body['guest_count'] ?? 1));

        if (!$serviceId || !$date || !$time || !$name || !$email) {
            return new \WP_REST_Response(['success' => false, 'message' => 'Missing required fields'], 400);
        }

        // Verify service exists and is active
        $service = DB::get_row(DB::prepare(
            "SELECT id, name, duration, buffer_before, buffer_after, capacity FROM {$p}instinctor_services WHERE id = %d AND status = 'active'",
            $serviceId
        ));
        if (!$service) {
            return new \WP_REST_Response(['success' => false, 'message' => 'Service not found'], 404);
        }

        $duration = intval($service->duration ?: 60);
        $startTime = $date . ' ' . $time . ':00';
        $endDateTime = new \DateTime($startTime);
        $endDateTime->modify('+' . $duration . ' minutes');
        $endTime = $endDateTime->format('Y-m-d H:i:s');

        // Check slot availability (uses service_id in product_id column)
        $bufferBefore = intval($service->buffer_before) * 60;
        $bufferAfter  = intval($service->buffer_after) * 60;
        // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
        $startBuf = date('Y-m-d H:i:s', strtotime($startTime) - $bufferBefore);
        // phpcs:enable WordPress.DateTime.RestrictedFunctions
        // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
        $endBuf   = date('Y-m-d H:i:s', strtotime($endTime) + $bufferAfter);
        // phpcs:enable WordPress.DateTime.RestrictedFunctions

        $bookedCovers = (int) DB::get_var(DB::prepare(
            "SELECT COALESCE(SUM(guest_count),0) FROM {$p}instinctor_bookings
             WHERE product_id = %d AND status NOT IN ('cancelled')
             AND (start_time < %s AND end_time > %s)",
            $serviceId, $endBuf, $startBuf
        ));

        if (($bookedCovers + $guests) > intval($service->capacity ?: 1)) {
            return new \WP_REST_Response(['success' => false, 'message' => 'This time slot is no longer available'], 409);
        }

        // Tenant this booking belongs to, from the service being booked.
        $ownerId = self::ownerForService($serviceId);

        // Find or create customer by email (scoped to this tenant so two salons
        // can each hold the same client email without colliding).
        $customer = DB::get_row(DB::prepare(
            "SELECT id FROM {$p}instinctor_customers WHERE email = %s AND " . \Instinctor\Core\Tenant::scopeSqlFor($ownerId, 'owner_id') . " LIMIT 1",
            $email
        ));
        $customerId = 0;
        if ($customer) {
            $customerId = (int) $customer->id;
        } else {
            $nameParts = explode(' ', $name, 2);
            DB::insert($p . 'instinctor_customers', [
                'first_name' => $nameParts[0],
                'last_name'  => $nameParts[1] ?? '',
                'email'      => $email,
                'phone'      => $phone,
                'owner_id'   => $ownerId,
                'created_at' => current_time('mysql'),
            ]);
            $customerId = (int) DB::insert_id();
        }

        // Insert booking
        DB::insert($p . 'instinctor_bookings', [
            'product_id'    => $serviceId,
            'customer_id'   => $customerId,
            'owner_id'      => $ownerId,
            'order_id'      => 0,
            'status'        => 'pending',
            'start_time'    => $startTime,
            'end_time'      => $endTime,
            'guest_count'   => $guests,
            'timezone'      => $this->getSettings()['timezone'],
            'customer_note' => $notes,
            'meta'          => wp_json_encode([
                'duration'     => $duration,
                'created_via'  => 'booking',
                'service_name' => $service->name,
                'phone'        => $phone,
                'guest_count'  => $guests,
            ]),
            'created_at'    => current_time('mysql'),
        ]);

        $bookingId = (int) DB::insert_id();
        if (!$bookingId) {
            return new \WP_REST_Response(['success' => false, 'message' => 'Failed to create booking'], 500);
        }

        // Send confirmation email to the customer + SMS the owner.
        try {
            $this->sendBookingConfirmation($bookingId);
        } catch (\Exception $e) {
            // Non-fatal — booking still created
        }
        try {
            $this->notifyNewBooking($bookingId);
        } catch (\Throwable $e) {
            // Non-fatal — booking still created
        }

        return new \WP_REST_Response([
            'success'    => true,
            'id'         => $bookingId,
            'start_time' => $startTime,
            'end_time'   => $endTime,
        ]);
    }

    /**
     * Check if a time slot is available
     */
    public function isSlotAvailable(int $productId, string $startTime, string $endTime): bool
    {
        $p = DB::prefix();
        $productSettings = $this->getProductBookingSettings($productId);
        $bufferBefore = $productSettings['buffer_before'] * 60;
        $bufferAfter = $productSettings['buffer_after'] * 60;
        
        // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
        $startWithBuffer = date('Y-m-d H:i:s', strtotime($startTime) - $bufferBefore);
        // phpcs:enable WordPress.DateTime.RestrictedFunctions
        // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
        $endWithBuffer = date('Y-m-d H:i:s', strtotime($endTime) + $bufferAfter);
        // phpcs:enable WordPress.DateTime.RestrictedFunctions
        
        // Check for overlapping bookings
        $conflict = DB::get_var(DB::prepare(
            "SELECT COUNT(*) FROM {$p}instinctor_bookings 
             WHERE product_id = %d 
             AND status NOT IN ('cancelled') 
             AND (
                 (start_time < %s AND end_time > %s) OR
                 (start_time >= %s AND start_time < %s)
             )",
            $productId,
            $endWithBuffer,
            $startWithBuffer,
            $startWithBuffer,
            $endWithBuffer
        ));

        return $conflict == 0;
    }

    /**
     * Update booking status
     */
    public function updateStatus(int $bookingId, string $status): array
    {
        
        $validStatuses = ['pending', 'confirmed', 'cancelled', 'completed', 'no_show'];
        if (!in_array($status, $validStatuses)) {
            return ['success' => false, 'error' => 'Invalid status'];
        }

        $result = DB::update(
            DB::prefix() . 'instinctor_bookings',
            ['status' => $status],
            ['id' => $bookingId]
        );

        if ($result === false) {
            return ['success' => false, 'error' => 'Failed to update booking'];
        }

        // Send notifications based on status
        if ($status === 'confirmed') {
            $this->sendBookingConfirmation($bookingId);
        } elseif ($status === 'cancelled') {
            $this->sendCancellationNotice($bookingId);
            // Admin-initiated cancellation: refund any held deposit. A no-show is
            // marked with the 'no_show' status instead, which forfeits it.
            try { $this->maybeRefundDeposit($bookingId, true); } catch (\Throwable $e) {}
        }

        return ['success' => true];
    }

    /**
     * Cancel a booking
     */
    public function cancelBooking(int $bookingId, string $reason = ''): array
    {
        $p = DB::prefix();
        
        $booking = DB::get_row(DB::prepare(
            "SELECT * FROM {$p}instinctor_bookings WHERE id = %d",
            $bookingId
        ));

        if (!$booking) {
            return ['success' => false, 'error' => 'Booking not found'];
        }

        $settings = $this->getSettings();
        
        // Check cancellation deadline
        if ($settings['cancellation_deadline'] > 0) {
            $deadline = strtotime($booking->start_time) - ($settings['cancellation_deadline'] * 3600);
            if (time() > $deadline) {
                return ['success' => false, 'error' => 'Cancellation deadline has passed'];
            }
        }

        $meta = json_decode($booking->meta ?? '{}', true);
        $meta['cancelled_at'] = current_time('mysql');
        $meta['cancellation_reason'] = $reason;

        DB::update(
            DB::prefix() . 'instinctor_bookings',
            [
                'status' => 'cancelled',
                'meta' => json_encode($meta)
            ],
            ['id' => $bookingId]
        );

        $this->sendCancellationNotice($bookingId);
        // Customer cancelled inside the window (the deadline check above already
        // enforced that): refund the deposit if auto-refund is on.
        try { $this->maybeRefundDeposit($bookingId, false); } catch (\Throwable $e) {}

        return ['success' => true];
    }

    /**
     * Reschedule a booking
     */
    public function rescheduleBooking(int $bookingId, string $newDate, string $newTime): array
    {
        $p = DB::prefix();
        
        $booking = DB::get_row(DB::prepare(
            "SELECT * FROM {$p}instinctor_bookings WHERE id = %d",
            $bookingId
        ));

        if (!$booking) {
            return ['success' => false, 'error' => 'Booking not found'];
        }

        $meta = json_decode($booking->meta ?? '{}', true);
        $duration = $meta['duration'] ?? 60;
        
        $newStartTime = $newDate . ' ' . $newTime . ':00';
        $newEndDateTime = new \DateTime($newStartTime);
        $newEndDateTime->modify('+' . $duration . ' minutes');
        $newEndTime = $newEndDateTime->format('Y-m-d H:i:s');

        // Verify new slot is available
        if (!$this->isSlotAvailable($booking->product_id, $newStartTime, $newEndTime)) {
            return ['success' => false, 'error' => 'New time slot is not available'];
        }

        // Store old time in meta
        $meta['rescheduled_from'] = $booking->start_time;
        $meta['rescheduled_at'] = current_time('mysql');

        DB::update(
            DB::prefix() . 'instinctor_bookings',
            [
                'start_time' => $newStartTime,
                'end_time' => $newEndTime,
                'meta' => json_encode($meta)
            ],
            ['id' => $bookingId]
        );

        $this->sendRescheduleNotice($bookingId);

        return [
            'success' => true,
            'new_start_time' => $newStartTime,
            'new_end_time' => $newEndTime
        ];
    }

    /**
     * Verify a Stripe PaymentIntent actually succeeded for the expected deposit
     * amount. Independent server-side check so a forged payment_intent_id from
     * the public booking form can never mint a "paid" deposit.
     */
    public function verifyDepositPayment(string $paymentIntentId, float $expectedAmount): bool
    {
        $paymentIntentId = trim($paymentIntentId);
        if ($paymentIntentId === '') return false;
        $secret = (string) Config::get('instinctor_stripe_secret_key', '');
        if ($secret === '') return false;

        $resp = wp_remote_get('https://api.stripe.com/v1/payment_intents/' . rawurlencode($paymentIntentId), [
            'headers' => ['Authorization' => 'Bearer ' . $secret],
        ]);
        if (is_wp_error($resp)) return false;
        $body = json_decode(wp_remote_retrieve_body($resp), true);
        if (($body['status'] ?? '') !== 'succeeded') return false;

        // Amount must match to the cent, so a cheaper intent can't be replayed.
        $expectedCents = (int) round($expectedAmount * 100);
        $paidCents     = (int) ($body['amount_received'] ?? $body['amount'] ?? 0);
        return $paidCents === $expectedCents;
    }

    /**
     * Record a paid booking deposit as a lightweight order so it reuses the
     * existing refund + reporting machinery (RefundService keys off the order's
     * payment_intent_id). Returns the new order id, or 0 on failure.
     */
    public function createDepositOrder(int $customerId, object $service, float $amount, string $paymentIntentId): int
    {
        $ordersTable = DB::prefix() . 'instinctor_orders';
        DB::insert($ordersTable, [
            'order_number'      => 'PENDING',
            'customer_id'       => $customerId,
            'status'            => 'processing',
            'currency'          => Config::get('instinctor_currency', 'USD'),
            'subtotal'          => $amount,
            'tax_total'         => 0,
            'shipping_total'    => 0,
            
            'discount_total'    => 0,
            'total'             => $amount,
            'payment_method'    => 'stripe',
            'payment_status'    => 'paid',
            'transaction_id'    => $paymentIntentId,
            'payment_intent_id' => $paymentIntentId,
            'customer_note'     => '',
            'meta'              => wp_json_encode([
                'type'         => 'booking_deposit',
                'service_id'   => (int) ($service->id ?? 0),
                'service_name' => (string) ($service->name ?? ''),
            ]),
            'created_at'        => current_time('mysql'),
        ]);
        $orderId = (int) DB::insert_id();
        if ($orderId > 0) {
            DB::update($ordersTable, ['order_number' => 'DEP-' . str_pad((string) $orderId, 5, '0', STR_PAD_LEFT)], ['id' => $orderId]);
        }
        return $orderId;
    }

    /**
     * Create a Stripe PaymentIntent for a booking deposit. Mirrors
     * CheckoutService::createPaymentIntent but stays self-contained so the
     * booking flow carries no checkout/cart dependency.
     */
    public function createDepositIntent(float $amount): array
    {
        $secret = (string) Config::get('instinctor_stripe_secret_key', '');
        if ($secret === '') return ['success' => false, 'error' => 'Stripe not configured'];

        $resp = wp_remote_post('https://api.stripe.com/v1/payment_intents', [
            'headers' => [
                'Authorization' => 'Bearer ' . $secret,
                'Content-Type'  => 'application/x-www-form-urlencoded',
            ],
            'body' => [
                'amount'                     => (int) round($amount * 100),
                'currency'                   => strtolower((string) Config::get('instinctor_currency', 'USD')),
                'automatic_payment_methods'  => ['enabled' => 'true'],
                'metadata'                   => ['type' => 'booking_deposit'],
            ],
        ]);
        if (is_wp_error($resp)) return ['success' => false, 'error' => $resp->get_error_message()];
        $body = json_decode(wp_remote_retrieve_body($resp), true);
        if (!empty($body['error'])) return ['success' => false, 'error' => $body['error']['message']];
        return [
            'success'           => true,
            'client_secret'     => $body['client_secret'] ?? '',
            'payment_intent_id' => $body['id'] ?? '',
        ];
    }

    /**
     * Refund a booking's deposit when the cancellation is eligible. Eligible =
     * a salon-initiated cancellation ($force=true), or a customer cancellation
     * inside the policy window while auto-refund is enabled. No-shows and late
     * cancellations forfeit the deposit and never reach a refund here.
     */
    public function maybeRefundDeposit(int $bookingId, bool $force = false): void
    {
        $p = DB::prefix();
        $booking = DB::get_row(DB::prepare("SELECT * FROM {$p}instinctor_bookings WHERE id = %d", $bookingId));
        if (!$booking || (int) $booking->order_id <= 0) return;

        $order = DB::get_row(DB::prepare("SELECT * FROM {$p}instinctor_orders WHERE id = %d", (int) $booking->order_id));
        if (!$order) return;
        $ometa = json_decode($order->meta ?? '{}', true) ?: [];
        if (($ometa['type'] ?? '') !== 'booking_deposit') return;
        if (($order->payment_status ?? '') !== 'paid') return; // already refunded or never paid

        if (!$force) {
            $settings = $this->getSettings();
            if (empty($settings['auto_refund_within_window'])) return;
            $deadlineHrs = (float) ($settings['cancellation_deadline'] ?? 0);
            if ($deadlineHrs > 0 && time() > (strtotime($booking->start_time) - $deadlineHrs * 3600)) {
                return; // outside the window — deposit is forfeit
            }
        }

        $res = \Instinctor\Services\RefundService::processStripeRefund((int) $order->id, (float) $order->total, 'Booking cancelled within policy window');
        if (!empty($res['success'])) {
            DB::update($p . 'instinctor_orders', ['payment_status' => 'refunded', 'status' => 'refunded'], ['id' => (int) $order->id]);
            $bmeta = json_decode($booking->meta ?? '{}', true) ?: [];
            $bmeta['deposit_refunded'] = current_time('mysql');
            DB::update($p . 'instinctor_bookings', ['meta' => wp_json_encode($bmeta)], ['id' => $bookingId]);
        }
    }

    /**
     * Get bookings for calendar view
     */
    public function getBookingsForCalendar(string $start, string $end, ?int $productId = null): array
    {
        $p = DB::prefix();
        
        // product_id points at either a product OR a salon service; COALESCE so
        // the real name shows instead of falling back to "Appointment".
        $query = "SELECT b.*, COALESCE(p.name, s.name) as product_name, c.first_name, c.last_name, c.email
                  FROM {$p}instinctor_bookings b
                  LEFT JOIN {$p}instinctor_products p ON b.product_id = p.id
                  LEFT JOIN {$p}instinctor_services s ON b.product_id = s.id
                  LEFT JOIN {$p}instinctor_customers c ON b.customer_id = c.id
                  WHERE " . \Instinctor\Core\Tenant::scopeSql('b.owner_id') . " AND b.start_time >= %s AND b.start_time <= %s";
        
        $params = [$start, $end];
        
        if ($productId) {
            $query .= " AND b.product_id = %d";
            $params[] = $productId;
        }
        
        $query .= " ORDER BY b.start_time ASC";
        
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query uses $wpdb->prepare with safe params
        $bookings = DB::get_results(DB::prepare($query, $params));
        
        // Format for FullCalendar
        $events = [];
        $statusColors = [
            'pending' => '#f0ad4e',
            'confirmed' => '#5cb85c',
            'cancelled' => '#d9534f',
            'completed' => '#5bc0de',
            'no_show' => '#777'
        ];
        
        foreach ($bookings as $booking) {
            $customerName = trim($booking->first_name . ' ' . $booking->last_name);
            if (!$customerName) {
                $customerName = $booking->email ?? 'Customer #' . $booking->customer_id;
            }

            // A salon booking stores the service name in its own meta at booking
            // time — authoritative, and sidesteps product/service id collisions
            // (product_id can point at either table). Fall back to the join.
            $bmeta   = json_decode((string) ($booking->meta ?? ''), true) ?: [];
            $svcName = trim((string) ($bmeta['service_name'] ?? '')) ?: (string) ($booking->product_name ?? 'Service');

            $events[] = [
                'id' => $booking->id,
                'title' => $customerName . ' - ' . $svcName,
                'start' => $booking->start_time,
                'end' => $booking->end_time,
                'backgroundColor' => $statusColors[$booking->status] ?? '#337ab7',
                'borderColor' => $statusColors[$booking->status] ?? '#337ab7',
                'extendedProps' => [
                    'status' => $booking->status,
                    'customer_name' => $customerName,
                    'customer_email' => $booking->email,
                    'product_name' => $svcName,
                    'product_id' => $booking->product_id,
                    'order_id' => $booking->order_id,
                    'customer_note' => $booking->customer_note
                ]
            ];
        }
        
        return $events;
    }

    /**
     * Owner (tenant) a booking should belong to, derived from the service being
     * booked: booking.owner_id = service.owner_id. Null for an unowned/vendor
     * service and on single-tenant WP, so bookings there stay NULL exactly as
     * before. Data-driven, so it works during a booking POST that has no ambient
     * page-owner context.
     */
    private static function ownerForService(int $serviceId): ?int
    {
        if ($serviceId <= 0) return null;
        $oid = DB::get_var(DB::prepare(
            "SELECT owner_id FROM " . DB::prefix() . "instinctor_services WHERE id = %d",
            $serviceId
        ));
        return ($oid === null || $oid === '') ? null : (int) $oid;
    }

    /**
     * Get booking statistics
     */
    public function getStats(): array
    {
        $table = DB::prefix() . 'instinctor_bookings';
        
        // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
        $today = date('Y-m-d');
        // phpcs:enable WordPress.DateTime.RestrictedFunctions
        // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
        $weekStart = date('Y-m-d', strtotime('monday this week'));
        // phpcs:enable WordPress.DateTime.RestrictedFunctions
        // phpcs:disable WordPress.DateTime.RestrictedFunctions -- date() used intentionally for local-timezone formatting; gmdate() would force UTC and change behavior.
        $monthStart = date('Y-m-01');
        // phpcs:enable WordPress.DateTime.RestrictedFunctions
        
        // Tenant scope: vendor/WP (null owner) -> "owner_id IS NULL" -> unchanged.
        $scope = \Instinctor\Core\Tenant::scopeSql('owner_id');
        return [
            'today' => (int) DB::get_var(DB::prepare(
                "SELECT COUNT(*) FROM $table WHERE {$scope} AND DATE(start_time) = %s AND status NOT IN ('cancelled')",
                $today
            )),
            'this_week' => (int) DB::get_var(DB::prepare(
                "SELECT COUNT(*) FROM $table WHERE {$scope} AND start_time >= %s AND status NOT IN ('cancelled')",
                $weekStart
            )),
            'this_month' => (int) DB::get_var(DB::prepare(
                "SELECT COUNT(*) FROM $table WHERE {$scope} AND start_time >= %s AND status NOT IN ('cancelled')",
                $monthStart
            )),
            'pending' => (int) DB::get_var(
                "SELECT COUNT(*) FROM $table WHERE {$scope} AND status = 'pending'"
            ),
            'confirmed' => (int) DB::get_var(
                "SELECT COUNT(*) FROM $table WHERE {$scope} AND status = 'confirmed' AND start_time >= NOW()"
            ),
            'completed' => (int) DB::get_var(
                "SELECT COUNT(*) FROM $table WHERE {$scope} AND status = 'completed'"
            )
        ];
    }

    /**
     * Get bookable products
     */
    public function getBookableProducts(): array
    {
        $p = DB::prefix();
        
        $products = DB::get_results(
            "SELECT id, name, price, meta FROM {$p}instinctor_products WHERE status = 'published'"
        );
        
        $settings = $this->getSettings();
        $bookable = [];
        
        foreach ($products as $product) {
            $meta = json_decode($product->meta ?? '{}', true);
            if (!empty($meta['is_bookable'])) {
                $bookable[] = [
                    'id' => $product->id,
                    'name' => $product->name,
                    'price' => $product->price,
                    'duration' => $meta['booking_duration'] ?? $settings['default_duration'],
                    'buffer_before' => $meta['booking_buffer_before'] ?? $settings['buffer_before'],
                    'buffer_after' => $meta['booking_buffer_after'] ?? $settings['buffer_after'],
                    'max_per_slot' => $meta['booking_max_per_slot'] ?? 1
                ];
            }
        }
        
        return $bookable;
    }

    /**
     * Send booking confirmation email
     */
    public function sendBookingConfirmation(int $bookingId): void
    {
        $p = DB::prefix();

        // Service name lives in instinctor_services (NOT products); join there.
        $booking = DB::get_row(DB::prepare(
            "SELECT b.*, s.name as service_name, c.email, c.first_name
             FROM {$p}instinctor_bookings b
             LEFT JOIN {$p}instinctor_services s ON b.product_id = s.id
             LEFT JOIN {$p}instinctor_customers c ON b.customer_id = c.id
             WHERE b.id = %d",
            $bookingId
        ));

        if (!$booking || !$booking->email) {
            return;
        }

        $storeName = Config::get('instinctor_store_name', get_bloginfo('name'));
        $service   = $booking->service_name ?: __('your booking', 'instinctor');
        /* translators: %s: store name. */
        $subject = sprintf(__('Booking request received - %s', 'instinctor'), $storeName);

        $dateFormatted = date_i18n(
            Config::get('date_format', 'Y-m-d') . ' ' . Config::get('time_format', 'H:i'),
            strtotime($booking->start_time)
        );

        // Real newlines (double-quoted) — the old single-quoted string emitted literal \n.
        $message = sprintf(
            /* translators: 1: customer name, 2: service name, 3: date/time, 4: store name */
            __("Hi %1\$s,\n\nWe received your request for %2\$s on %3\$s. We will be in touch shortly to confirm.\n\nThank you,\n%4\$s", 'instinctor'),
            $booking->first_name ?: 'there',
            $service,
            $dateFormatted,
            $storeName
        );

        Mailer::send($booking->email, $subject, $message);
    }

    /**
     * SMS on a new booking request: text the business owner (if
     * instinctor_booking_settings.notify_sms is set) and, when
     * notify_customer_sms is on, text the customer too (if they left a phone).
     * Routes through the platform SMS relay (Telnyx) the same way chat SMS does.
     */
    public function notifyNewBooking(int $bookingId): void
    {
        $settings = $this->getSettings();
        $ownerTo  = trim((string)($settings['notify_sms'] ?? ''));
        $custOn   = !empty($settings['notify_customer_sms']);
        // Restaurant reservations always text the guest a confirmation (SMS is the
        // primary channel), so don't bail even when the generic notify flags are
        // off. The initializer stays generic; the assignment is stripped from the
        // free build, where $isRestaurant is always false.
        $isRestaurant = false;
        
        if ($ownerTo === '' && !$custOn && !$isRestaurant) {
            return; // nothing to send
        }

        $p = DB::prefix();
        $b = DB::get_row(DB::prepare(
            "SELECT b.*, s.name as service_name, c.first_name, c.last_name, c.phone
             FROM {$p}instinctor_bookings b
             LEFT JOIN {$p}instinctor_services s ON b.product_id = s.id
             LEFT JOIN {$p}instinctor_customers c ON b.customer_id = c.id
             WHERE b.id = %d",
            $bookingId
        ));
        if (!$b) {
            return;
        }

        $when    = date_i18n('D M j, g:i A', strtotime($b->start_time));
        $who     = trim(($b->first_name ?? '') . ' ' . ($b->last_name ?? ''));
        $phone   = trim((string)($b->phone ?? ''));
        $service = $b->service_name ?: 'a service';
        $store   = Config::get('instinctor_store_name', get_bloginfo('name'));

        // Owner notification
        if ($ownerTo !== '') {
            $ownerMsg = sprintf(
                'New booking request: %s on %s.%s%s - %s',
                $service,
                $when,
                $who ? ' From ' . $who . '.' : '',
                $phone ? ' ' . $phone . '.' : '',
                $store
            );
            $this->sendRelaySms($ownerTo, $ownerMsg);
        }

        // Customer confirmation.
        if ($phone !== '') {
            $custMsg = '';
            
            if ($custMsg === '' && $custOn) {
                $custMsg = sprintf(
                    '%sthanks for your request for %s on %s. We will be in touch shortly to confirm. - %s',
                    $b->first_name ? 'Hi ' . $b->first_name . ', ' : '',
                    $service,
                    $when,
                    $store
                );
            }
            if ($custMsg !== '') {
                $this->sendRelaySms($phone, $custMsg);
            }
        }
    }

    /**
     * Send a one-way SMS through the platform relay (Telnyx). Standalone sites
     * authenticate by site_url; the platform sends from its shared number.
     */
    private function sendRelaySms(string $to, string $message): bool
    {
        return \Instinctor\Core\Sms::send($to, $message);
    }

    /**
     * Best-effort US/E.164 normalization so any entered format (215-729-9860,
     * (215) 729 9860, 12157299860) reaches Telnyx as +12157299860.
     * Returns '' if there aren't enough digits to be a real number.
     */
    private static function normalizeSmsNumber(string $raw): string
    {
        $raw = trim($raw);
        $hasPlus = str_starts_with($raw, '+');
        $digits  = preg_replace('/\D+/', '', $raw);
        if ($hasPlus) {
            return strlen($digits) >= 8 ? '+' . $digits : '';
        }
        if (strlen($digits) === 10) {
            return '+1' . $digits;
        }
        if (strlen($digits) === 11 && $digits[0] === '1') {
            return '+' . $digits;
        }
        return strlen($digits) >= 8 ? '+' . $digits : '';
    }

    /**
     * Send cancellation notice
     */
    private function sendCancellationNotice(int $bookingId): void
    {
        $p = DB::prefix();
        
        $booking = DB::get_row(DB::prepare(
            "SELECT b.*, p.name as product_name, c.email, c.first_name 
             FROM {$p}instinctor_bookings b
             LEFT JOIN {$p}instinctor_products p ON b.product_id = p.id
             LEFT JOIN {$p}instinctor_customers c ON b.customer_id = c.id
             WHERE b.id = %d",
            $bookingId
        ));

        if (!$booking || !$booking->email) {
            return;
        }

        /* translators: placeholder values */
        $storeName = Config::get('instinctor_store_name', get_bloginfo('name'));
        /* translators: %s: store name. */
        $subject = sprintf(__('Booking Cancelled - %s', 'instinctor'), $storeName);
        
        $dateFormatted = date_i18n(
            Config::get('date_format', 'Y-m-d') . ' ' . Config::get('time_format', 'H:i'),
            strtotime($booking->start_time)
        );
        
$message = sprintf(
    /* translators: 1: customer name, 2: product name, 3: booking date/time, 4: store name */
    __( 'Hi %1$s,\n\nYour booking for %2$s on %3$s has been cancelled.\n\nIf this was a mistake, please contact us.\n\n%4$s', 'instinctor' ),
    $booking->first_name ?: 'there',
    $booking->product_name,
    $dateFormatted,
    $storeName
);

        Mailer::send($booking->email, $subject, $message);
    }

    /**
     * Send reschedule notice
     */
    private function sendRescheduleNotice(int $bookingId): void
    {
        $p = DB::prefix();
        
        $booking = DB::get_row(DB::prepare(
            "SELECT b.*, p.name as product_name, c.email, c.first_name 
             FROM {$p}instinctor_bookings b
             LEFT JOIN {$p}instinctor_products p ON b.product_id = p.id
             LEFT JOIN {$p}instinctor_customers c ON b.customer_id = c.id
             WHERE b.id = %d",
            $bookingId
        ));

        if (!$booking || !$booking->email) {
            return;
        }

        $storeName = Config::get('instinctor_store_name', get_bloginfo('name'));
        /* translators: %s: store name. */
        $subject = sprintf(__('Booking Rescheduled - %s', 'instinctor'), $storeName);
        
        $dateFormatted = date_i18n(
            Config::get('date_format', 'Y-m-d') . ' ' . Config::get('time_format', 'H:i'),
            strtotime($booking->start_time)
        );
        
$message = sprintf(
    /* translators: 1: customer name, 2: product name, 3: booking date/time, 4: store name */
    __( 'Hi %1$s,\n\nYour booking for %2$s has been rescheduled to %3$s.\n\nThank you!\n\n%4$s', 'instinctor' ),
    $booking->first_name ?: 'there',
    $booking->product_name,
    $dateFormatted,
    $storeName
);

        Mailer::send($booking->email, $subject, $message);
    }

    /**
     * Process bookings during checkout
     */
    public function processCheckoutBooking(int $orderId, array $cartItem, int $customerId): ?int
    {
        if (empty($cartItem['booking_date']) || empty($cartItem['booking_time'])) {
            return null;
        }

        $result = $this->createBooking([
            'product_id' => $cartItem['product_id'],
            'customer_id' => $customerId,
            'order_id' => $orderId,
            'date' => $cartItem['booking_date'],
            'time' => $cartItem['booking_time'],
            'note' => $cartItem['booking_note'] ?? '',
            'created_via' => 'checkout'
        ]);

        return $result['success'] ? $result['booking_id'] : null;
    }
}
