<?php

namespace Bookaro\Repository;

defined( 'ABSPATH' ) || exit;

// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is assembled from a custom table name built from $wpdb->prefix with all dynamic values bound via $wpdb->prepare() before execution.

use DateTime;
use wpdb;
use Bookaro\Entity\Booking;
use Bookaro\Utils\DBUtils;
use function bookaro_get_user_meta;
use function bookaro_get_option;
use function wp_timezone;

/**
 * Central database handler for Bookaro.
 */
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
class BookingRepository extends AbstractRepository
{
    public const TABLE                 = 'bookaro_bookings';
    // Use the same table names as ScheduleService to avoid mismatches.
    public const AVAILABILITY_TABLE    = 'bookaro_provider_availability';
    public const TIMESLOT_TABLE        = 'bookaro_provider_timeslots';
    public const EXCEPTIONS_TABLE      = 'bookaro_provider_exceptions';
    public const NOTIFICATION_LOG_TABLE = 'bookaro_notification_logs';

    private \wpdb  $wpdb;
    private string $table_name;
    private string $transient_prefix;

    public function __construct(?wpdb $db = null)
    {
        global $wpdb;
        $this->wpdb             = $db ?: $wpdb;
        $this->table_name       = $this->wpdb->prefix . self::TABLE;
        $this->transient_prefix = 'bookaro_slots_' . BOOKARO_VERSION . '_';

        foreach ( [ 'created','updated','deleted' ] as $e ) {
            add_action( "bookaro_booking_{$e}", [ self::class, 'on_booking_changed' ], 10, 1 );
        }
    }

    private function generate_cache_key( int $provider_id, string $from, string $to, int $duration_minutes = 0 ): string
    {
        $duration_minutes = max( 0, (int) $duration_minutes );
        return $this->transient_prefix . $provider_id . '_' . $from . '_' . $to . '_' . $duration_minutes;
    }

    /**
     * Booking table name (with WP prefix).
     */
    public function get_table_name(): string
    {
        return $this->table_name;
    }

    /**
     * Ensure required booking table indexes exist.
     */
    public function ensure_indexes(): void
    {
        DBUtils::add_index_if_missing(
            $this->wpdb,
            $this->table_name,
            'provider_created_idx',
            'provider_id, created_at'
        );
        DBUtils::add_index_if_missing(
            $this->wpdb,
            $this->table_name,
            'customer_idx',
            'customer_id'
        );
        DBUtils::add_index_if_missing(
            $this->wpdb,
            $this->table_name,
            'booking_date_to_idx',
            'booking_date_to'
        );
    }

    /**
     * Verify that all Bookaro tables exist in the database.
     */
    public function tables_exist(): bool
    {
        $tables = [
            $this->table_name,
            $this->get_availability_table(),
            $this->get_timeslot_table(),
            $this->get_exceptions_table(),
            $this->get_notification_log_table(),
        ];

        foreach ( $tables as $table ) {
            $exists = $this->wpdb->get_var(
                $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table )
            );
            if ( empty( $exists ) ) {
                return false;
            }
        }

        return true;
    }

    /**
     * Access the underlying wpdb instance.
     */
    public function get_wpdb(): \wpdb
    {
        return $this->wpdb;
    }

    /**
     * Availability rules table name.
     */
    public function get_availability_table(): string
    {
        return $this->wpdb->prefix . self::AVAILABILITY_TABLE;
    }

    /**
     * Timeslot templates table name.
     */
    public function get_timeslot_table(): string
    {
        return $this->wpdb->prefix . self::TIMESLOT_TABLE;
    }

    /**
     * Date‐based exceptions table name.
     */
    public function get_exceptions_table(): string
    {
        return $this->wpdb->prefix . self::EXCEPTIONS_TABLE;
    }

    /**
     * Notification log table name.
     */
    public function get_notification_log_table(): string
    {
        return $this->wpdb->prefix . self::NOTIFICATION_LOG_TABLE;
    }

    /**
     * Check if the notification log table exists.
     */
    public function notification_table_exists(): bool
    {
        $table = $this->get_notification_log_table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $exists = $this->wpdb->get_var(
            $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table )
        );
        return ! empty( $exists );
    }

    /**
     * Determine if the provider uses day-long (1440 minute) intervals.
     */
    public function is_day_interval(int $provider_id): bool
    {
        $table        = esc_sql( $this->get_timeslot_table() );
        $max_interval = $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT MAX(interval_m) FROM {$table} WHERE provider_id = %d",
                $provider_id
            )
        );

        return (int) $max_interval === 1440;
    }

    /**
     * Create or update all plugin tables.
     */
    public function create_table(): void
    {
        $charset = $this->wpdb->get_charset_collate();
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        dbDelta("
            CREATE TABLE {$this->table_name} (
                id              BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                listing_id      BIGINT(20)          NOT NULL,
                service_id      BIGINT(20) UNSIGNED NULL,
                service_label   VARCHAR(255)        NOT NULL,
                price           DECIMAL(10,2)       NOT NULL DEFAULT '0.00',
                customer_id    BIGINT(20)          NOT NULL,
                customer_phone VARCHAR(40)         NOT NULL,
                provider_id     BIGINT(20)          NOT NULL,
                staff_id        BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
                booking_date    DATETIME            NOT NULL,
                booking_date_to DATETIME            NULL,
                status          VARCHAR(20)         NOT NULL DEFAULT 'pending',
                notes           TEXT                NULL,
                cancel_reason   TEXT                NULL,
                created_at      DATETIME            NOT NULL,
                updated_at      DATETIME            NOT NULL,
                PRIMARY KEY (id),
                KEY provider_idx (provider_id),
                KEY provider_created_idx (provider_id, created_at),
                KEY listing_idx  (listing_id),
                KEY status_idx   (status),
                KEY date_idx     (booking_date),
                KEY customer_idx (customer_id),
                KEY booking_date_to_idx (booking_date_to)
            ) $charset;
        ");

        dbDelta("
            CREATE TABLE {$this->get_availability_table()} (
                provider_id BIGINT(20) NOT NULL,
                weekday     TINYINT(1)  NOT NULL,
                is_open     TINYINT(1)  NOT NULL DEFAULT 1,
                PRIMARY KEY (provider_id, weekday),
                INDEX idx_provider_weekday (provider_id, weekday)
            ) $charset;
        ");

        dbDelta("
            CREATE TABLE {$this->get_timeslot_table()} (
                id          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                provider_id BIGINT(20)          NOT NULL,
                weekday     TINYINT(1)          NOT NULL,
                start_time  TIME                NOT NULL,
                end_time    TIME                NOT NULL,
                interval_m  SMALLINT(4)         NOT NULL,
                PRIMARY KEY (id),
                INDEX idx_provider (provider_id),
                INDEX idx_weekday  (weekday)
            ) $charset;
        ");

        dbDelta("
            CREATE TABLE {$this->get_exceptions_table()} (
                id              BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                provider_id     BIGINT(20)          NOT NULL,
                exception_date  DATE                NOT NULL,
                is_open         TINYINT(1)          NOT NULL DEFAULT 0,
                PRIMARY KEY (id),
                UNIQUE KEY uq_provider_date (provider_id, exception_date),
                INDEX idx_ex_provider (provider_id)
            ) $charset;
        ");

        dbDelta("
            CREATE TABLE {$this->get_notification_log_table()} (
                id            BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                booking_id    BIGINT(20) UNSIGNED NOT NULL,
                channel       VARCHAR(20)         NOT NULL,
                template_key  VARCHAR(64)         NULL,
                status        VARCHAR(20)         NOT NULL,
                summary       TEXT                NULL,
                error_message TEXT                NULL,
                created_at    DATETIME            NOT NULL,
                PRIMARY KEY (id),
                KEY booking_idx (booking_id),
                KEY channel_idx (channel),
                KEY created_idx (created_at)
            ) $charset;
        ");
    }

    /**
     * Check whether a notification with the given booking + channel + template
     * has already produced a successful send. Used by the email/SMS
     * dispatchers to skip duplicate sends when concurrent crons or retries
     * race each other.
     */
    public function notification_log_has_send(
        int $booking_id,
        string $channel,
        string $template_key = '',
        string $status = 'sent'
    ): bool {
        $booking_id = absint( $booking_id );
        if ( $booking_id < 1 || ! $this->notification_table_exists() ) {
            return false;
        }
        $table = esc_sql( $this->get_notification_log_table() );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $count = (int) $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$table}
                 WHERE booking_id = %d
                   AND channel = %s
                   AND template_key = %s
                   AND status = %s",
                $booking_id,
                $channel,
                $template_key,
                $status
            )
        );
        return $count > 0;
    }

    /**
     * Insert a notification log row.
     */
    public function insert_notification_log(
        int $booking_id,
        string $channel,
        string $status,
        string $summary = '',
        string $error_message = '',
        string $template_key = ''
    ): bool {
        $booking_id = absint( $booking_id );
        if ( $booking_id < 1 ) {
            return false;
        }
        if ( ! $this->notification_table_exists() ) {
            return false;
        }

        $data = [
            'booking_id'    => $booking_id,
            'channel'       => $channel,
            'template_key'  => $template_key,
            'status'        => $status,
            'summary'       => $summary,
            'error_message' => $error_message,
            'created_at'    => current_time( 'mysql' ),
        ];

        $result = $this->wpdb->insert(
            $this->get_notification_log_table(),
            $data,
            [ '%d', '%s', '%s', '%s', '%s', '%s', '%s' ]
        );

        return false !== $result;
    }

    /**
     * Fetch notification logs for a booking.
     *
     * @return array<array<string,mixed>>
     */
    public function get_notification_logs( int $booking_id, int $limit = 50 ): array
    {
        $booking_id = absint( $booking_id );
        $limit = max( 1, min( 200, absint( $limit ) ) );
        if ( $booking_id < 1 ) {
            return [];
        }
        if ( ! $this->notification_table_exists() ) {
            return [];
        }

        $table = esc_sql( $this->get_notification_log_table() );
        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT *
                 FROM {$table}
                 WHERE booking_id = %d
                 ORDER BY created_at DESC, id DESC
                 LIMIT %d",
                $booking_id,
                $limit
            ),
            ARRAY_A
        );

        foreach ( $rows as &$row ) {
            $summary = $row['summary'] ?? '';
            $decoded = is_string( $summary ) ? json_decode( $summary, true ) : null;
            if ( is_array( $decoded ) ) {
                $row['summary_data'] = $decoded;
            } else {
                $row['summary_data'] = [];
            }
        }
        unset( $row );

        return $rows;
    }

    /**
     * Purge notification logs older than the given number of days.
     */
    public function purge_notification_logs_older_than( int $days ): int
    {
        $days = absint( $days );
        if ( $days < 1 || ! $this->notification_table_exists() ) {
            return 0;
        }
        // Bug 40: clamp before multiplying. With a huge $days input
        // (e.g., 999999999) the days*DAY_IN_SECONDS overflow pushed the
        // computed cutoff into the negative epoch range, gmdate emitted
        // an out-of-spec year like "-2735881-05-25", MySQL rejected the
        // DELETE as "Incorrect DATETIME value", and the purge silently
        // did nothing while spamming debug.log. 100 years (36,500) is
        // way past any realistic retention setting but stays inside the
        // MySQL DATETIME range when subtracted from `now`.
        $days = min( $days, 36500 );
        $table = esc_sql( $this->get_notification_log_table() );

        // Compute cutoff in site-local time to match how rows are inserted
        // (NotificationLogService uses current_time('mysql')). gmdate() here
        // would treat the column as UTC and on non-UTC sites silently delete
        // rows up to ~14 hours earlier than the configured retention window.
        // The Bug 40 clamp above keeps the modify() subtraction inside the
        // MySQL DATETIME range.
        $cutoff = ( new \DateTimeImmutable( current_time( 'mysql' ), wp_timezone() ) )
            ->modify( '-' . $days . ' days' )
            ->format( 'Y-m-d H:i:s' );

        // Batch the delete. A single unbounded DELETE on a large notification
        // log table can lock the table for minutes and indirectly stall the
        // booking insert path; batched deletes keep each statement short.
        $batch_size = (int) apply_filters( 'bookaro_notification_log_purge_batch', 5000 );
        $batch_size = max( 100, min( 50000, $batch_size ) );
        $max_batches = (int) apply_filters( 'bookaro_notification_log_purge_max_batches', 200 );
        $max_batches = max( 1, $max_batches );

        $total_deleted = 0;
        for ( $i = 0; $i < $max_batches; $i++ ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $deleted = $this->wpdb->query(
                $this->wpdb->prepare(
                    "DELETE FROM {$table} WHERE created_at < %s LIMIT %d",
                    $cutoff,
                    $batch_size
                )
            );
            if ( ! is_numeric( $deleted ) || (int) $deleted < 1 ) {
                break;
            }
            $total_deleted += (int) $deleted;
            if ( (int) $deleted < $batch_size ) {
                break;
            }
        }

        return $total_deleted;
    }
    /**
     * Fetch available slots for a provider/service within a date range.
     * Uses transient caching and gap computation.
     *
     * @param int    $provider_id
     * @param int    $listing_id
     * @param string $from       YYYY-MM-DD
     * @param string $to         YYYY-MM-DD
     * @return array             [ ['start'=>'Y-m-d H:i:s','end'=>'Y-m-d H:i:s'], … ]
     */
    /**
     * Fetch available slots using repository's booked slots lookup instead of complex SQL joins.
     */
    /**
     * Fetch available slots for a provider within a date range.
     * Caches only when from === to (i.e. single-day lookups).
     *
     * @param int    $provider_id
     * @param string $from       YYYY-MM-DD
     * @param string $to         YYYY-MM-DD
     * @return array             [ ['start'=>'Y-m-d H:i:s','end'=>'Y-m-d H:i:s'], … ]
     */
    public function get_available_slots( int $provider_id, string $from, string $to, int $duration_minutes = 0, int $exclude_booking_id = 0 ): array {
        $min_notice_hours = absint( bookaro_get_option( 'booking_min_notice_hours', 0 ) );
        $max_window_days  = absint( bookaro_get_option( 'booking_max_window_days', 0 ) );
        $is_single_day = ( $from === $to );
        $exclude_booking_id = absint( $exclude_booking_id );
        $availability_id = $provider_id;
        $use_cache = $is_single_day && $min_notice_hours < 1 && $max_window_days < 1 && $exclude_booking_id < 1;
        $cache_key     = $this->generate_cache_key( $provider_id, $from, $to, $duration_minutes );

        if ( $use_cache ) {
            if ( false !== ( $cached = get_transient( $cache_key ) ) ) {
                return $cached;
            }
        }

        $booked = $this->get_booked_slots( $provider_id, $from, $to, $exclude_booking_id );

        $occupied = [];
        foreach ( $booked as $date => $times ) {
            foreach ( $times as $tm ) {
                $key = "{$date} {$tm}";
                $occupied[ $key ] = ( $occupied[ $key ] ?? 0 ) + 1;
            }
        }

        $capacity = (int) bookaro_get_user_meta( $availability_id, 'bookaro_slots_per_interval', 0 );
        if ( $capacity < 1 ) {
            $capacity = 1;
        }

        // 2) Compute gaps
        $slots = $this->compute_gaps( $availability_id, $from, $to, $occupied, $capacity, $duration_minutes );

        // Bug 50: always filter slots that have already started, regardless
        // of whether booking_min_notice_hours is configured. Without this
        // guard the booking modal happily offers "today 9:00 AM" at 2 PM —
        // the customer fills the contact form, clicks Confirm, and only
        // then sees the server's bookaro_past_date error. min_notice_hours
        // (when set) becomes an ADDITIONAL constraint on top of "must be
        // in the future", not the trigger for any past-time filtering.
        $tz = wp_timezone();
        $now = new \DateTime( 'now', $tz );
        $min_start = $min_notice_hours > 0
            ? ( clone $now )->modify( '+' . $min_notice_hours . ' hours' )
            : $now;
        $max_end = $max_window_days > 0
            ? ( clone $now )->modify( '+' . $max_window_days . ' days' )->setTime( 23, 59, 59 )
            : null;

        $slots = array_values( array_filter( $slots, static function ( array $slot ) use ( $min_start, $max_end, $tz ): bool {
            $slot_start = new \DateTime( $slot['start'], $tz );
            $slot_end = ! empty( $slot['end'] )
                ? new \DateTime( $slot['end'], $tz )
                : clone $slot_start;
            if ( $min_start && $slot_start < $min_start ) {
                return false;
            }
            if ( $max_end && ( $slot_start > $max_end || $slot_end > $max_end ) ) {
                return false;
            }
            return true;
        } ) );

        if ( $use_cache ) {
            set_transient( $cache_key, $slots, HOUR_IN_SECONDS );
        }

        return $slots;
    }



    /**
     * Invalidate slot cache for this provider/service/date range.
     */
    private function invalidate_slot_cache( int $provider_id, string $from, string $to ): void {
        $prefix = $this->transient_prefix . $provider_id . '_' . $from . '_' . $to . '_';
        $like = $this->wpdb->esc_like( '_transient_' . $prefix );
        $options_table = esc_sql( $this->wpdb->options );
        $results = $this->wpdb->get_col(
            $this->wpdb->prepare(
                "SELECT option_name FROM {$options_table} WHERE option_name LIKE %s",
                $like . '%'
            )
        );

        foreach ( $results as $option_name ) {
            $key = substr( $option_name, strlen( '_transient_' ) );
            delete_transient( $key );
        }
    }

    /**
     * Delete all cached slot transients for a provider.
     */
    public function flush_provider_slot_cache( int $provider_id ): void {
        $like = $this->wpdb->esc_like( '_transient_' . $this->transient_prefix );
        $options_table = esc_sql( $this->wpdb->options );
        $results = $this->wpdb->get_col(
            $this->wpdb->prepare(
                "SELECT option_name FROM {$options_table} WHERE option_name LIKE %s",
                $like . '%'
            )
        );

        foreach ( $results as $option_name ) {
            $key = substr( $option_name, strlen( '_transient_' ) );
            delete_transient( $key );
        }
    }

    /**
     * Compute free slots by subtracting occupied bookings from availability.
     *
     * @param int    $provider_id
     * @param int    $listing_id
     * @param string $from
     * @param string $to
     * @param array  $occupied   [ 'Y-m-d H:i' => count, … ]
     * @param int    $capacity
     * @return array             [ ['start'=>'…','end'=>'…'], … ]
     */
    private function compute_gaps(int $availability_id, string $from, string $to, array $occupied, int $capacity, int $duration_minutes = 0): array
    {
        $schedule   = new \Bookaro\Service\ScheduleService($this);
        $weekly     = $schedule->get_weekly_availability($availability_id);
        $timeslots  = $schedule->get_timeslots($availability_id);
        $exceptions = $schedule->get_exceptions($availability_id);

        // Map timeslots by weekday
        $slot_map = [];
        foreach ($timeslots as $tpl) {
            $d = (int)$tpl->weekday;
            $slot_map[$d][] = $tpl;
        }

        $duration_minutes = max(0, (int) $duration_minutes);

        $tz = wp_timezone();
        $start_dt = new \DateTime($from, $tz);
        $end_dt   = new \DateTime($to, $tz);
        $end_dt->modify('+1 day');

        $result = [];
        while ($start_dt < $end_dt) {
            $date_str = $start_dt->format('Y-m-d');
            $dow      = (int)$start_dt->format('w');
            $is_open  = $exceptions[$date_str] ?? $weekly[$dow] ?? 0;

            if ($is_open) {
                foreach ($slot_map[$dow] ?? [] as $tpl) {
                    $slot_start = new \DateTime("{$date_str} {$tpl->start_time}", $tz);
                    $slot_end   = new \DateTime("{$date_str} {$tpl->end_time}", $tz);
                    $interval   = (int) $tpl->interval_m;

                    if ($interval <= 0) {
                        continue;
                    }

                    if ($interval === 1440) {
                        $required_days = $duration_minutes > 0 ? (int) ceil($duration_minutes / 1440) : 1;
                        $range_ok = true;
                        $check_dt = clone $start_dt;
                        for ($i = 0; $i < $required_days; $i++) {
                            $check_date = $check_dt->format('Y-m-d');
                            $check_dow  = (int) $check_dt->format('w');
                            $check_open = $exceptions[$check_date] ?? $weekly[$check_dow] ?? 0;
                            if (!$check_open) {
                                $range_ok = false;
                                break;
                            }
                            $key   = "{$check_date} 00:00";
                            $count = $occupied[$key] ?? 0;
                            if ($count >= $capacity) {
                                $range_ok = false;
                                break;
                            }
                            $check_dt->modify('+1 day');
                        }

                        if ($range_ok) {
                            $end_range = clone $start_dt;
                            if ($required_days > 1) {
                                $end_range->modify('+' . ($required_days - 1) . ' day');
                            }
                            $end_dt_for_slot = new \DateTime($end_range->format('Y-m-d') . ' ' . $tpl->end_time, $tz);
                            $result[] = [
                                'start' => $slot_start->format('Y-m-d H:i:s'),
                                'end'   => $end_dt_for_slot->format('Y-m-d H:i:s'),
                            ];
                        }
                        break;
                    }

                    $required = $duration_minutes > 0 ? $duration_minutes : $interval;
                    $steps = max(1, (int) ceil($required / $interval));
                    $step_interval = new \DateInterval("PT{$interval}M");

                    $cur = clone $slot_start;
                    while ($cur < $slot_end) {
                        $booking_end = (clone $cur)->modify('+' . $required . ' minutes');
                        if ($booking_end > $slot_end) {
                            break;
                        }

                        $can_fit = true;
                        for ($i = 0; $i < $steps; $i++) {
                            $check = (clone $cur)->modify('+' . ($interval * $i) . ' minutes');
                            $key   = $check->format('Y-m-d H:i');
                            if (($occupied[$key] ?? 0) >= $capacity) {
                                $can_fit = false;
                                break;
                            }
                        }

                        if ($can_fit) {
                            $result[] = [
                                'start' => $cur->format('Y-m-d H:i:s'),
                                'end'   => $booking_end->format('Y-m-d H:i:s'),
                            ];
                        }

                        $cur->add($step_interval);
                    }
                }
            }

            $start_dt->modify('+1 day');
        }

        return $result;
    }

    /**
     * Returns booked slots grouped by date between $from and $to,
     * treating both pending and approved as occupied.
     *
     * @return array<string,string[]> ['YYYY-MM-DD'=>['HH:MM',…], …]
     */
    public function get_booked_slots(int $provider_id, string $from, string $to, int $exclude_booking_id = 0): array
    {
        $buffer_before = absint( bookaro_get_option( 'booking_buffer_before_minutes', 0 ) );
        $buffer_after  = absint( bookaro_get_option( 'booking_buffer_after_minutes', 0 ) );
        $tz            = wp_timezone();
        $range_start   = new \DateTime( "{$from} 00:00:00", $tz );
        $range_end     = ( new \DateTime( "{$to} 00:00:00", $tz ) )->modify( '+1 day' );
        $query_start   = clone $range_start;
        $query_end     = new \DateTime( "{$to} 23:59:59", $tz );
        if ( $buffer_after > 0 ) {
            $query_start->modify( '-' . $buffer_after . ' minutes' );
        }
        if ( $buffer_before > 0 ) {
            $query_end->modify( '+' . $buffer_before . ' minutes' );
        }

        $exclude_booking_id = absint( $exclude_booking_id );
        $availability_id = $provider_id;
        $table = esc_sql( $this->table_name );
        $base_sql = "
                SELECT booking_date, booking_date_to
             FROM {$table}
             WHERE provider_id = %d
               AND booking_date <= %s
               AND (booking_date_to IS NULL OR booking_date_to >= %s)
               AND status NOT IN ('cancelled','rejected','no_show')
        ";
        $params = [
            $provider_id,
            $query_end->format( 'Y-m-d H:i:s' ),
            $query_start->format( 'Y-m-d H:i:s' ),
        ];

        if ( $exclude_booking_id > 0 ) {
            $rows = $this->wpdb->get_results(
                $this->wpdb->prepare(
                    $base_sql . ' AND id <> %d',
                    array_merge( $params, [ $exclude_booking_id ] )
                ),
                ARRAY_A
            );
        } else {
            $rows = $this->wpdb->get_results(
                $this->wpdb->prepare( $base_sql, $params ),
                ARRAY_A
            );
        }

        $out         = [];
        $is_day      = $this->is_day_interval( $availability_id );
        $schedule    = new \Bookaro\Service\ScheduleService( $this );
        $timeslots   = $schedule->get_timeslots( $availability_id );
        $slot_map    = [];
        foreach ($timeslots as $tpl) {
            $slot_map[(int) $tpl->weekday][] = $tpl;
        }

        $resolve_interval = static function (\DateTime $dt) use ($slot_map, $tz): int {
            $weekday = (int) $dt->format('w');
            $slots   = $slot_map[$weekday] ?? [];

            foreach ($slots as $tpl) {
                $start_time = new \DateTime($dt->format('Y-m-d') . ' ' . $tpl->start_time, $tz);
                $end_time   = new \DateTime($dt->format('Y-m-d') . ' ' . $tpl->end_time, $tz);
                if ($dt >= $start_time && $dt < $end_time) {
                    return (int) $tpl->interval_m;
                }
            }

            if (!empty($slots)) {
                return (int) ($slots[0]->interval_m ?? 30);
            }

            return 30;
        };

        foreach ($rows as $r) {
            $booking_start = new \DateTime( $r['booking_date'], $tz );
            $end   = $r['booking_date_to'] ? new \DateTime( $r['booking_date_to'], $tz ) : null;

            $all_day = $end && $booking_start->format('H:i:s') === '00:00:00' && $end->format('H:i:s') === '00:00:00';

            if (($is_day && $end) || $all_day) {
                $cur     = clone $booking_start;
                $end_inc = (clone $end)->modify('+1 day');
                while ($cur < $end_inc) {
                    if ($cur >= $range_start && $cur < $range_end) {
                        $date = $cur->format('Y-m-d');
                        $out[$date][] = '00:00';
                    }
                    $cur->modify('+1 day');
                }
            } else {
                $interval = $resolve_interval($booking_start);
                if ($interval < 1) {
                    $interval = 30;
                }
                $end_dt = $end ? clone $end : (clone $booking_start)->modify('+' . $interval . ' minutes');
                if ($end_dt <= $booking_start) {
                    $end_dt = (clone $booking_start)->modify('+' . $interval . ' minutes');
                }

                // Round 8 fix (Issue #22): the buffered occupancy window is
                // [booking_start - buffer_before, booking_end + buffer_after).
                // Previously we walked this window in `interval` steps starting
                // at `booking_start - buffer_before`, which produced occupancy
                // keys at times that don't necessarily align to the slot grid
                // (e.g. a 60-min slot grid starting 09:00 with a buffered 10:00
                // booking would produce a key at "09:50" — neither 09:00 nor
                // 10:00, so compute_gaps() never matches and the 10:00 slot
                // incorrectly reports as free even though there's a booking on
                // it). The fix: walk the day's slot template grid and emit one
                // occupancy entry per slot whose time range overlaps the
                // buffered booking window. This produces keys at the exact
                // slot-grid times that compute_gaps() looks up.
                $blocked_start = clone $booking_start;
                if ( $buffer_before > 0 ) {
                    $blocked_start->modify( '-' . $buffer_before . ' minutes' );
                }
                $blocked_end = clone $end_dt;
                if ( $buffer_after > 0 ) {
                    $blocked_end->modify( '+' . $buffer_after . ' minutes' );
                }

                $weekday = (int) $booking_start->format( 'w' );
                $slots_for_day = $slot_map[ $weekday ] ?? [];
                foreach ( $slots_for_day as $tpl ) {
                    $tpl_step = (int) $tpl->interval_m;
                    if ( $tpl_step <= 0 ) {
                        continue;
                    }
                    $tpl_start = new \DateTime( $booking_start->format( 'Y-m-d' ) . ' ' . $tpl->start_time, $tz );
                    $tpl_end   = new \DateTime( $booking_start->format( 'Y-m-d' ) . ' ' . $tpl->end_time,   $tz );

                    $cur = clone $tpl_start;
                    while ( $cur < $tpl_end ) {
                        $cur_end = ( clone $cur )->modify( '+' . $tpl_step . ' minutes' );
                        // Overlap test: [cur, cur_end) overlaps [blocked_start, blocked_end)
                        if ( $cur < $blocked_end && $cur_end > $blocked_start ) {
                            if ( $cur >= $range_start && $cur < $range_end ) {
                                $out[ $cur->format( 'Y-m-d' ) ][] = $cur->format( 'H:i' );
                            }
                        }
                        $cur = $cur_end;
                    }
                }
            }
        }
        return $out;
    }


    /**
     * Receives [$args] = [ 'provider_id'=>…, 'listing_id'=>…, 'date'=>… ]
     * and clears the relevant transient.
     */
    public static function on_booking_changed( $args ): void
    {
        if ( is_int( $args ) ) {
            $args = [ 'booking_id' => $args ];
        }

        if ( ! is_array( $args ) ) {
            return;
        }

        $provider_id = ! empty( $args['provider_id'] ) ? (int) $args['provider_id'] : 0;
        $date = $args['date'] ?? '';
        $date_to = $args['date_to'] ?? '';
        $db = null;

        if ( ( $provider_id < 1 || empty( $date ) ) && ! empty( $args['booking_id'] ) ) {
            $db = new self();
            $booking = $db->get_booking( (int) $args['booking_id'] );
            if ( $booking ) {
                if ( $provider_id < 1 ) {
                    $provider_id = (int) $booking->provider_id;
                }
                if ( empty( $date ) && ! empty( $booking->booking_date ) ) {
                    $tz = wp_timezone();
                    $date = ( new \DateTime( (string) $booking->booking_date, $tz ) )->format( 'Y-m-d' );
                }
                if ( empty( $date_to ) && ! empty( $booking->booking_date_to ) ) {
                    $tz = wp_timezone();
                    $date_to = ( new \DateTime( (string) $booking->booking_date_to, $tz ) )->format( 'Y-m-d' );
                }
            }
        }

        if ( $provider_id < 1 || empty( $date ) ) {
            return;
        }

        if ( ! $db ) {
            $db = new self();
        }

        $start = new \DateTime( (string) $date );
        $end   = ! empty( $date_to )
            ? new \DateTime( (string) $date_to )
            : clone $start;

        while ( $start <= $end ) {
            $date = $start->format( 'Y-m-d' );
            $db->invalidate_slot_cache( (int) $provider_id, $date, $date );
            $start->modify( '+1 day' );
        }
    }
    /**
     * Fetch bookings for CSV export with optional provider and date range filters.
     *
     * @return array<array<string,mixed>>
     */
    public function get_export_bookings( int $provider_id = 0, string $start_date = '', string $end_date = '' ): array {
        // Pro-only query. Lite returns an empty result set.
        return [];
    }

    /**
     * Get booking counts by status for a date range.
     *
     * @return array<string,int>
     */
    public function get_booking_status_counts( int $provider_id, string $start, string $end ): array
    {
        // Pro-only query. Lite returns an empty result set.
        return [];
    }

    /**
     * Get service usage stats for a date range.
     *
     * @return array<int,array<string,mixed>>
     */
    public function get_service_usage( int $provider_id, string $start, string $end, int $limit = 0 ): array
    {
        // Pro-only query. Lite returns an empty result set.
        return [];
    }

    /**
     * Retrieve a single booking row by its ID.
     *
     * @param  int $booking_id
     * @return Booking|null
     */
    public function get_booking( int $booking_id ): ?Booking {
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->table_name} WHERE id = %d",
                $booking_id
            )
        );
        return $row ? Booking::fromWpdbRow( $row ) : null;
    }

    /**
     * Find a recent pending booking that matches the same request fingerprint.
     *
     * @return array{id:int,created_at:string}|array{}
     */
    public function find_recent_pending_duplicate(
        int $customer_id,
        int $provider_id,
        int $listing_id,
        int $service_id,
        string $service_label,
        string $booking_date,
        string $booking_date_to,
        int $within_hours = 24
    ): array {
        $customer_id = absint( $customer_id );
        $provider_id = absint( $provider_id );
        $listing_id = absint( $listing_id );
        $service_id = absint( $service_id );
        $within_hours = absint( $within_hours );
        $service_label = sanitize_text_field( $service_label );
        $booking_date = sanitize_text_field( $booking_date );
        $booking_date_to = sanitize_text_field( $booking_date_to );

        if ( $customer_id < 1 || $provider_id < 1 || $within_hours < 1 || $booking_date === '' ) {
            return [];
        }

        $cutoff = wp_date(
            'Y-m-d H:i:s',
            time() - ( $within_hours * HOUR_IN_SECONDS ),
            wp_timezone()
        );

        $table = esc_sql( $this->table_name );
        // Bug 55: when the caller passes booking_date_to='' (the normal
        // case — customer picks only a start time, server computes the
        // end from service_duration before INSERT), the stored row's
        // booking_date_to ends up non-empty. Comparing
        // COALESCE(stored, '') = '' would only ever match rows where the
        // stored end was NULL/empty, which is effectively never for
        // slot-based bookings. That killed the duplicate-pending guard
        // for the normal flow — a customer could resubmit the same
        // booking N times within `duplicate_pending_hours` and each
        // would slip past the check (slot-conflict caught it at
        // capacity=1 only by accident; at capacity>1 the guard was the
        // last defense). When the payload's end is empty, drop the
        // booking_date_to filter so we match purely on start +
        // customer/provider/service — that's the realistic
        // "same booking" identity from the customer's perspective. When
        // the payload's end IS provided (day-interval / multi-day
        // bookings), keep the explicit equality check.
        $sql = "
            SELECT id, created_at
            FROM {$table}
            WHERE status = 'pending'
              AND customer_id = %d
              AND provider_id = %d
              AND listing_id = %d
              AND booking_date = %s
              AND created_at >= %s
        ";
        $params = [
            $customer_id,
            $provider_id,
            $listing_id,
            $booking_date,
            $cutoff,
        ];

        if ( $booking_date_to !== '' ) {
            $sql .= ' AND COALESCE(booking_date_to, \'\') = %s';
            $params[] = $booking_date_to;
        }

        if ( $service_id > 0 ) {
            $sql .= ' AND service_id = %d';
            $params[] = $service_id;
        } else {
            $sql .= ' AND service_id = 0 AND service_label = %s';
            $params[] = $service_label;
        }

        $sql .= ' ORDER BY created_at DESC, id DESC LIMIT 1';
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare( $sql, $params ),
            ARRAY_A
        );
        if ( ! is_array( $row ) ) {
            return [];
        }

        return [
            'id' => absint( $row['id'] ?? 0 ),
            'created_at' => sanitize_text_field( (string) ( $row['created_at'] ?? '' ) ),
        ];
    }

    /**
     * Fetch all bookings for a provider, joining owner data.
     *
     * @param int $provider_id
     * @return array<array<string,mixed>>
     */
    public function get_provider_bookings( int $provider_id ): array {
        $table = esc_sql( $this->table_name );
        $users = esc_sql( $this->wpdb->users );
        // Bug 65: cap the result set at a reasonable maximum so a
        // provider with tens of thousands of historical bookings can't
        // OOM the PHP process — or the Vue runtime that has to hold the
        // whole list to filter/paginate client-side. The dashboard's
        // pagination is client-side, so the cap also doubles as a
        // sanity limit on payload size. Operators with huge archives
        // can raise the cap via filter.
        $limit = (int) apply_filters( 'bookaro_dashboard_max_bookings', 1000, 'provider', $provider_id );
        if ( $limit < 1 ) {
            $limit = 1000;
        }
        return $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT b.*, u.display_name AS owner_name, u.user_email AS owner_email
                 FROM {$table} b
                 LEFT JOIN {$users} u ON u.ID = b.customer_id
                 WHERE b.provider_id = %d
                 ORDER BY b.created_at DESC
                 LIMIT %d",
                $provider_id,
                $limit
            ),
            ARRAY_A
        );
    }

    /**
     * Fetch all bookings for a customer, joining provider data.
     *
     * @param int $owner_id
     * @return array<array<string,mixed>>
     */
    public function get_owner_bookings( int $owner_id ): array {
        $table    = esc_sql( $this->table_name );
        $posts    = esc_sql( $this->wpdb->posts );
        $postmeta = esc_sql( $this->wpdb->postmeta );

        // Bug 65: sibling cap on the customer-side dashboard endpoint.
        // The Vue paginates client-side after this fetch, so we must
        // limit how much we hand back to avoid both DB exhaustion and
        // bloated JSON payloads. 1000 covers any reasonable customer
        // history; the filter lets sites with edge cases opt out.
        $limit = (int) apply_filters( 'bookaro_dashboard_max_bookings', 1000, 'owner', $owner_id );
        if ( $limit < 1 ) {
            $limit = 1000;
        }
        return $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT b.*, p.post_title AS provider_name,
                        COALESCE(m_email_bookaro.meta_value, m_email_job.meta_value) AS provider_email,
                        COALESCE(m_phone_bookaro.meta_value, m_phone_job.meta_value) AS provider_phone
                 FROM {$table} b
                 LEFT JOIN {$posts} p ON p.ID = b.listing_id
                 LEFT JOIN {$postmeta} m_email_job ON m_email_job.post_id = b.listing_id AND m_email_job.meta_key = '_job_email'
                 LEFT JOIN {$postmeta} m_email_bookaro ON m_email_bookaro.post_id = b.listing_id AND m_email_bookaro.meta_key = %s
                 LEFT JOIN {$postmeta} m_phone_job ON m_phone_job.post_id = b.listing_id AND m_phone_job.meta_key = '_job_phone'
                 LEFT JOIN {$postmeta} m_phone_bookaro ON m_phone_bookaro.post_id = b.listing_id AND m_phone_bookaro.meta_key = %s
                 WHERE b.customer_id = %d
                 ORDER BY b.created_at DESC
                 LIMIT %d",
                BOOKARO_PROVIDER_EMAIL_META,
                BOOKARO_PROVIDER_PHONE_META,
                $owner_id,
                $limit
            ),
            ARRAY_A
        );
    }

    /**
     * Update one or more columns on a booking.
     *
     * @param  int   $booking_id
     * @param  array $data       Associative array column => new value
     * @return bool              True if update succeeded
     */
    public function update_booking( int $booking_id, array $data ): bool {
        if ( $booking_id < 1 ) {
            return false;
        }

        if ( ! array_key_exists( 'updated_at', $data ) ) {
            $data['updated_at'] = current_time( 'mysql' );
        }

        $formats = [];
        foreach ( $data as $val ) {
            if ( is_int( $val ) ) {
                $formats[] = '%d';
            } elseif ( is_float( $val ) || ( is_numeric( $val ) && strpos( (string)$val, '.', ) !== false ) ) {
                $formats[] = '%f';
            } else {
                $formats[] = '%s';
            }
        }

        $where        = [ 'id' => $booking_id ];
        $where_format = [ '%d' ];

        $updated = $this->wpdb->update(
            $this->table_name,
            $data,
            $where,
            $formats,
            $where_format
        );

        return false !== $updated;
    }

    /**
     * Delete a booking by id.
     */
    public function delete_booking( int $booking_id ): bool {
        if ( $booking_id < 1 ) {
            return false;
        }

        // Bug 33: before yanking the row, capture the provider_id and clear
        // any cron events scheduled against this booking. Otherwise
        // bookaro_send_reminder + bookaro_send_review_invite keep firing
        // later for a booking that no longer exists (the callbacks then
        // look up the row, find nothing, and silently return — wasted
        // cron slots), and the slot transient cache stays pinned as
        // occupied for up to an hour. Both paths are admin-triggered
        // (per-row Delete + bulk Delete) — leave create-rollback callers
        // unaffected since brand-new bookings have nothing scheduled yet.
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT provider_id FROM {$this->table_name} WHERE id = %d",
                $booking_id
            )
        );
        $provider_id = $row ? (int) $row->provider_id : 0;

        if ( function_exists( 'wp_clear_scheduled_hook' ) ) {
            wp_clear_scheduled_hook( 'bookaro_send_reminder', [ $booking_id, 'reminder_24h' ] );
            wp_clear_scheduled_hook( 'bookaro_send_reminder', [ $booking_id, 'reminder_1h' ] );
            wp_clear_scheduled_hook( 'bookaro_send_review_invite', [ $booking_id ] );
        }

        $deleted = $this->wpdb->delete( $this->table_name, [ 'id' => $booking_id ], [ '%d' ] );

        if ( false === $deleted ) {
            return false;
        }

        if ( $deleted ) {
            // Bug 39: drop the per-booking notification log rows alongside
            // the booking row. Without this, deleting a booking leaves
            // orphan rows in wp_bookaro_notification_logs that point to a
            // booking_id which no longer exists. They're effectively
            // unreachable (the booking page 404s) but stay in the DB until
            // purge_notification_logs_older_than ages them out (180 days
            // by default) and a privacy export run AFTER the delete misses
            // them because get_user_booking_ids resolves through the
            // bookings table first.
            if ( $this->notification_table_exists() ) {
                $this->wpdb->delete(
                    $this->get_notification_log_table(),
                    [ 'booking_id' => $booking_id ],
                    [ '%d' ]
                );
            }
            if ( $provider_id > 0 ) {
                $this->flush_provider_slot_cache( $provider_id );
            }
            do_action( 'bookaro_booking_deleted', $booking_id );
        }

        return true;
    }



    public function get_notes( int $booking_id ): string {
        $row = $this->get_booking( $booking_id );
        if ( ! $row ) {
            return '';
        }
        return $row->notes
            ?? ( is_string( $row->customer_data )
                ? json_decode( $row->customer_data, true )['notes'] ?? ''
                : '' );
    }

    public function get_cancel_reason( int $booking_id ): string {
        $row = $this->get_booking( $booking_id );
        return $row->cancel_reason ?? '';
    }

    /**
     * @throws \RuntimeException
     * @throws \Exception
     */
    public function get_start_datetime( int $booking_id ): \DateTime {
        $row = $this->get_booking( $booking_id );
        if ( ! $row || empty( $row->booking_date ) ) {
            throw new \RuntimeException( 'Booking not found.' );
        }
        return new \DateTime( $row->booking_date, wp_timezone() );
    }

    /**
     * Get booking end datetime. Uses `booking_date_to` when provided,
     * otherwise defaults to one hour after the start time.
     *
     * @throws \RuntimeException
     * @throws \Exception
     */
    public function get_end_datetime( int $booking_id ): \DateTime {
        $booking = $this->get_booking( $booking_id );
        if ( ! $booking || empty( $booking->booking_date ) ) {
            throw new \RuntimeException( 'Booking not found.' );
        }
        if ( $booking && ! empty( $booking->booking_date_to ) ) {
            return new \DateTime( $booking->booking_date_to, wp_timezone() );
        }

        $dt = new \DateTime( $booking->booking_date, wp_timezone() );

        // Attempt to determine the duration based on provider timeslots.
        $table    = esc_sql( $this->get_timeslot_table() );
        $weekday  = (int) $dt->format( 'w' );
        $time     = $dt->format( 'H:i:s' );
        $interval = $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT interval_m FROM {$table} WHERE provider_id = %d AND weekday = %d AND start_time <= %s AND end_time > %s ORDER BY start_time DESC LIMIT 1",
                $booking->provider_id,
                $weekday,
                $time,
                $time
            )
        );

        if ( $interval ) {
            return ( clone $dt )->modify( "+{$interval} minutes" );
        }

        return $dt->modify( '+1 hour' );
    }

    /**
     * Fetch bookings for customer calendar sync.
     *
     * @return array<Booking>
     */
    public function get_owner_sync_bookings( int $owner_id ): array {
        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->table_name}
                 WHERE customer_id = %d
                   AND status IN (%s, %s)",
                $owner_id,
                'approved',
                'cancelled'
            )
        );

        return array_map( [ Booking::class, 'fromWpdbRow' ], $rows );
    }

    public function count_total( int $provider_id ): int {
        return (int) $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->table_name} WHERE provider_id = %d",
                $provider_id
            )
        );
    }

    /**
     * Count all bookings for a given owner.
     */
    public function count_owner_total( int $owner_id ): int {
        return (int) $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->table_name} WHERE customer_id = %d",
                $owner_id
            )
        );
    }

    /**
     * Count all bookings site-wide in a given status (e.g. "completed").
     */
    public function count_by_status( string $status ): int {
        $status = sanitize_key( $status );
        if ( '' === $status ) {
            return 0;
        }
        return (int) $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->table_name} WHERE status = %s",
                $status
            )
        );
    }

}
// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
