<?php
namespace Bookaro\Service;

defined('ABSPATH') || exit;

use Bookaro\Repository\BookingRepository;
use function bookaro_get_user_meta;

/**
 * Manages provider availability: weekly rules, timeslots, and exceptions.
 */
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
class ScheduleService
{
    public const AVAIL_TABLE     = 'bookaro_provider_availability';
    public const TIMESLOT_TABLE  = 'bookaro_provider_timeslots';
    public const EXCEPTION_TABLE = 'bookaro_provider_exceptions';
    public const DAY_NAMES       = [ 'Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat' ];
    public const DEFAULT_INTERVAL_MINUTES = 30;
    public const DEFAULT_SLOTS_PER_INTERVAL = 1;

    private const DEFAULT_RANGE_MAX_DAYS = 370;
    private const DEFAULT_TEMPLATE_WEEKDAYS = [ 'Mon', 'Tue', 'Wed', 'Thu', 'Fri' ];
    private const DEFAULT_TEMPLATE_START_TIME = '09:00:00';
    private const DEFAULT_TEMPLATE_END_TIME   = '17:00:00';
    private const FULL_DAY_START_TIME         = '00:00:00';
    private const FULL_DAY_END_TIME           = '23:59:00';

    private BookingRepository $db;
    private \wpdb $wpdb;

    public function __construct(BookingRepository $db)
    {
        global $wpdb;
        $this->db   = $db;
        $this->wpdb = $wpdb;
    }

    /**
     * (Optional) Hook registrations, if you need init hooks later.
     */
    public function register_hooks(): void
    {
        // e.g. add_action('init', [ $this, 'maybe_something' ]);
    }

    /**
     * Fetch weekly availability (0–6 => 0|1).
     */
    public function get_weekly_availability(int $provider_id): array
    {
        $transient_key = "bookaro_avail_weekly_{$provider_id}";
        $cached = get_transient($transient_key);
        if ($cached !== false) {
            return $cached;
        }

        $table = esc_sql( $this->wpdb->prefix . self::AVAIL_TABLE );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $avail = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT weekday, is_open FROM {$table} WHERE provider_id = %d",
                $provider_id
            ),
            ARRAY_A
        ) ?: [];

        $out = [];
        foreach ($avail as $row) {
            $out[(int) $row['weekday']] = (int) $row['is_open'];
        }

        set_transient($transient_key, $out, HOUR_IN_SECONDS);
        return $out;
    }

    /**
     * Fetch provider slot capacity per interval.
     */
    public function get_slots_per_interval( int $provider_id ): int
    {
        $slots_per_interval = (int) bookaro_get_user_meta(
            $provider_id,
            'bookaro_slots_per_interval',
            self::DEFAULT_SLOTS_PER_INTERVAL
        );

        return $this->normalize_slots_per_interval( $slots_per_interval );
    }

    /**
     * Persist provider slot capacity per interval.
     */
    public function set_slots_per_interval( int $provider_id, int $slots_per_interval ): void
    {
        update_user_meta(
            $provider_id,
            'bookaro_slots_per_interval',
            $this->normalize_slots_per_interval( $slots_per_interval )
        );
    }

    /**
     * Persist weekly availability.
     */
    public function set_weekly_availability(int $provider_id, array $flags): void
    {
        $table = $this->wpdb->prefix . self::AVAIL_TABLE;
        $this->wpdb->delete($table, ['provider_id' => $provider_id], ['%d']);

        foreach ($flags as $weekday => $is_open) {
            $this->wpdb->insert(
                $table,
                [
                    'provider_id' => $provider_id,
                    'weekday'     => intval($weekday),
                    'is_open'     => $is_open ? 1 : 0,
                ],
                ['%d','%d','%d']
            );
        }

        delete_transient("bookaro_avail_weekly_{$provider_id}");

        // clear any cached slots since availability changed
        $this->db->flush_provider_slot_cache( $provider_id );
    }

    /**
     * Fetch timeslot templates.
     */
    public function get_timeslots(int $provider_id): array
    {
        $transient_key = "bookaro_timeslots_{$provider_id}";
        $cached = get_transient($transient_key);
        if ($cached !== false) {
            return $cached;
        }

        $table   = esc_sql( $this->wpdb->prefix . self::TIMESLOT_TABLE );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $results = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT id, weekday, start_time, end_time, interval_m FROM {$table} WHERE provider_id = %d ORDER BY weekday, start_time",
                $provider_id
            )
        ) ?: [];

        set_transient($transient_key, $results, HOUR_IN_SECONDS);
        return $results;
    }

    /**
     * Persist timeslot templates.
     */
    public function set_timeslots(int $provider_id, array $slots): void
    {
        $table = $this->wpdb->prefix . self::TIMESLOT_TABLE;
        $this->wpdb->delete($table, ['provider_id' => $provider_id], ['%d']);

        foreach ($slots as $slot) {
            if (!isset($slot['weekday'], $slot['start_time'], $slot['end_time'], $slot['interval_m'])) {
                continue;
            }

            // Bug 63: defend the storage layer against bad input even
            // when callers skip normalize_timeslots_payload. The REST
            // endpoint already validates via normalize, but third-party
            // code calling set_timeslots() directly could insert
            // nonsense (weekday=7, "9 to 5" instead of HH:MM:SS,
            // interval_m=-30) that the picker silently ignores at read
            // time. Reject rows that fail the same shape check the
            // normalizer enforces.
            $weekday = (int) $slot['weekday'];
            if ( ! isset( self::DAY_NAMES[ $weekday ] ) ) {
                continue;
            }
            $start = $this->normalize_time_string( $slot['start_time'] );
            $end   = $this->normalize_time_string( $slot['end_time'] );
            if ( ! $start || ! $end || ! $this->is_valid_time_range( $start, $end ) ) {
                continue;
            }
            $interval = $this->normalize_interval( $slot['interval_m'] );

            $this->wpdb->insert(
                $table,
                [
                    'provider_id' => $provider_id,
                    'weekday'     => $weekday,
                    'start_time'  => $start,
                    'end_time'    => $end,
                    'interval_m'  => $interval,
                ],
                ['%d','%d','%s','%s','%d']
            );
        }

        delete_transient("bookaro_timeslots_{$provider_id}");

        // flush cached slots after timeslot changes
        $this->db->flush_provider_slot_cache( $provider_id );
    }

    /**
     * Fetch date-based exceptions (Y-m-d => is_open).
     */
    public function get_exceptions(int $provider_id): array
    {
        $transient_key = "bookaro_exceptions_{$provider_id}";
        $cached = get_transient($transient_key);
        if ($cached !== false) {
            return $cached;
        }

        $table = $this->wpdb->prefix . self::EXCEPTION_TABLE;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $rows  = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT exception_date, is_open FROM {$table} WHERE provider_id = %d",
                $provider_id
            ),
            ARRAY_A
        ) ?: [];

        $mapped = array_column($rows, 'is_open', 'exception_date');
        set_transient($transient_key, $mapped, HOUR_IN_SECONDS);
        return $mapped;
    }

    /**
     * Build the shared provider settings payload used by provider settings UIs.
     *
     * @return array<string,mixed>
     */
    public function get_provider_settings_payload( int $provider_id ): array
    {
        $weekly             = $this->get_weekly_availability( $provider_id );
        $timeslots          = $this->get_timeslots( $provider_id );
        $exceptions         = $this->get_exceptions( $provider_id );
        $slots_per_interval = $this->get_slots_per_interval( $provider_id );
        $has_saved_schedule = ! empty( $weekly ) || ! empty( $timeslots );

        $availability = $has_saved_schedule
            ? $this->build_availability_payload( $weekly, $timeslots, $slots_per_interval )
            : $this->get_default_availability_payload( $slots_per_interval );

        return [
            'weekly'             => $weekly,
            'timeslots'          => $timeslots,
            'exceptions'         => $exceptions,
            'availability'       => $availability,
            'slots_per_interval' => $slots_per_interval,
            'schedule_meta'      => $this->build_schedule_meta( $availability, $has_saved_schedule ),
        ];
    }

    /**
     * Convert the UI availability payload into the existing weekly/timeslot storage model.
     *
     * @param array<string,mixed> $availability
     * @return array<string,mixed>
     */
    public function normalize_availability_payload( array $availability ): array
    {
        $interval            = $this->normalize_interval( $availability['interval'] ?? self::DEFAULT_INTERVAL_MINUTES );
        $slots_per_interval  = $this->normalize_slots_per_interval( $availability['slots'] ?? self::DEFAULT_SLOTS_PER_INTERVAL );
        $selected_weekdays   = $this->normalize_weekdays( $availability['weekdays'] ?? [] );
        $times_by_day        = is_array( $availability['times'] ?? null ) ? $availability['times'] : [];
        $weekday_lookup      = array_fill_keys( $selected_weekdays, true );
        $weekly              = $this->get_empty_weekly_flags();
        $timeslots           = [];

        foreach ( self::DAY_NAMES as $weekday => $day_name ) {
            $blocks = [];
            if ( is_array( $times_by_day[ $day_name ] ?? null ) ) {
                $blocks = $times_by_day[ $day_name ];
            }

            $normalized_blocks = [];
            foreach ( $blocks as $block ) {
                if ( ! is_array( $block ) ) {
                    continue;
                }
                $start = $this->normalize_time_string( $block['from'] ?? '' );
                $end   = $this->normalize_time_string( $block['to'] ?? '' );
                if ( ! $start || ! $end || ! $this->is_valid_time_range( $start, $end ) ) {
                    continue;
                }

                $normalized_blocks[] = [
                    'weekday'    => $weekday,
                    'start_time' => $start,
                    'end_time'   => $end,
                    'interval_m' => $interval,
                ];
            }

            if ( $interval === 1440 && ! empty( $weekday_lookup[ $day_name ] ) && empty( $normalized_blocks ) ) {
                $normalized_blocks[] = [
                    'weekday'    => $weekday,
                    'start_time' => self::FULL_DAY_START_TIME,
                    'end_time'   => self::FULL_DAY_END_TIME,
                    'interval_m' => $interval,
                ];
            }

            if ( ! empty( $normalized_blocks ) ) {
                $weekday_lookup[ $day_name ] = true;
            }

            if ( ! empty( $weekday_lookup[ $day_name ] ) ) {
                $weekly[ $weekday ] = 1;
            }

            foreach ( $normalized_blocks as $slot ) {
                $timeslots[] = $slot;
            }
        }

        $timeslots = $this->sort_timeslots( $timeslots );

        return [
            'weekly'             => $weekly,
            'timeslots'          => $timeslots,
            'slots_per_interval' => $slots_per_interval,
            'availability'       => $this->build_availability_payload( $weekly, $timeslots, $slots_per_interval ),
        ];
    }

    /**
     * Normalize raw weekly flags for storage.
     *
     * @param array<mixed> $weekly
     * @return array<int,int>
     */
    public function normalize_weekly_flags( array $weekly ): array
    {
        $normalized = $this->get_empty_weekly_flags();

        foreach ( self::DAY_NAMES as $weekday => $_day_name ) {
            $normalized[ $weekday ] = ! empty( $weekly[ $weekday ] ) ? 1 : 0;
        }

        return $normalized;
    }

    /**
     * Normalize raw legacy timeslot templates for storage.
     *
     * @param array<mixed> $timeslots
     * @return array<int,array<string,int|string>>
     */
    public function normalize_timeslots_payload( array $timeslots ): array
    {
        $normalized = [];

        foreach ( $timeslots as $slot ) {
            $weekday = isset( $slot['weekday'] ) ? (int) $slot['weekday'] : -1;
            if ( ! isset( self::DAY_NAMES[ $weekday ] ) ) {
                continue;
            }

            $start    = $this->normalize_time_string( $slot['start_time'] ?? '' );
            $end      = $this->normalize_time_string( $slot['end_time'] ?? '' );
            $interval = $this->normalize_interval( $slot['interval_m'] ?? self::DEFAULT_INTERVAL_MINUTES );
            if ( ! $start || ! $end || ! $this->is_valid_time_range( $start, $end ) ) {
                continue;
            }

            $normalized[] = [
                'weekday'    => $weekday,
                'start_time' => $start,
                'end_time'   => $end,
                'interval_m' => $interval,
            ];
        }

        return $this->sort_timeslots( $normalized );
    }

    /**
     * Normalize closed-date lists or exception maps for storage.
     *
     * @param array<mixed> $exceptions
     * @return array<string,int>
     */
    public function normalize_exceptions_payload( array $exceptions ): array
    {
        $mapped = [];
        $is_list = $this->is_list_array( $exceptions );

        if ( $is_list ) {
            foreach ( $exceptions as $date ) {
                $normalized_date = $this->normalize_date_string( $date );
                if ( ! $normalized_date ) {
                    continue;
                }
                $mapped[ $normalized_date ] = 0;
            }
        } else {
            foreach ( $exceptions as $date => $is_open ) {
                $normalized_date = $this->normalize_date_string( $date );
                if ( ! $normalized_date ) {
                    continue;
                }
                $mapped[ $normalized_date ] = ! empty( $is_open ) ? 1 : 0;
            }
        }

        ksort( $mapped );

        return $mapped;
    }

    /**
     * Normalize a slot-capacity value.
     *
     * @param mixed $slots_per_interval
     */
    public function normalize_slots_per_interval( $slots_per_interval ): int
    {
        $slots_per_interval = (int) $slots_per_interval;
        if ( $slots_per_interval < 1 ) {
            $slots_per_interval = self::DEFAULT_SLOTS_PER_INTERVAL;
        }

        return $slots_per_interval;
    }

    /**
     * Persist date exceptions.
     */
    public function set_exceptions(int $provider_id, array $exceptions): void
    {
        $table = $this->wpdb->prefix . self::EXCEPTION_TABLE;
        $this->wpdb->delete($table, ['provider_id' => $provider_id], ['%d']);

        foreach ($exceptions as $date => $is_open) {
            // Bug 63: same defensive normalization as set_timeslots —
            // reject anything that isn't a real Y-m-d date so callers
            // that bypass normalize_exceptions_payload can't poison the
            // table with strings like "2099-99-99" or "next tuesday".
            $iso = $this->normalize_date_string( is_string( $date ) ? $date : '' );
            if ( '' === $iso ) {
                continue;
            }
            $this->wpdb->insert(
                $table,
                [
                    'provider_id'    => $provider_id,
                    'exception_date' => $iso,
                    'is_open'        => $is_open ? 1 : 0,
                ],
                ['%d','%s','%d']
            );
        }

        delete_transient("bookaro_exceptions_{$provider_id}");

        // slot availability may change due to exceptions
        $this->db->flush_provider_slot_cache( $provider_id );
    }

    /**
     * Apply a date range of exceptions (bulk close/open).
     *
     * @return int| \WP_Error Number of dates updated or WP_Error on validation failure.
     */
    public function apply_exception_range( int $provider_id, string $from, string $to, int $is_open = 0 ) {
        $range = $this->build_date_range( $from, $to );
        if ( is_wp_error( $range ) ) {
            return $range;
        }

        $exceptions = [];
        foreach ( $range as $date ) {
            $exceptions[ $date ] = $is_open ? 1 : 0;
        }

        $count = $this->upsert_exceptions( $provider_id, $exceptions );
        $this->flush_exception_cache( $provider_id );

        return $count;
    }

    /**
     * Copy exceptions from one week to another.
     *
     * @return int|\WP_Error Number of dates updated or WP_Error on validation failure.
     */
    public function copy_exception_week( int $provider_id, string $source_start, string $target_start, bool $replace = true ) {
        $source = $this->parse_date( $source_start );
        $target = $this->parse_date( $target_start );
        if ( ! $source || ! $target ) {
            return new \WP_Error(
                'bookaro_invalid_date',
                __( 'Invalid date supplied.', 'mybookaro' )
            );
        }

        $source_end = $source->modify( '+6 days' )->format( 'Y-m-d' );
        $target_end = $target->modify( '+6 days' )->format( 'Y-m-d' );

        $source_range = $this->build_date_range( $source->format( 'Y-m-d' ), $source_end, 7 );
        if ( is_wp_error( $source_range ) ) {
            return $source_range;
        }

        $existing = $this->get_exceptions( $provider_id );
        $mapped = [];
        foreach ( $source_range as $index => $date ) {
            if ( ! array_key_exists( $date, $existing ) ) {
                continue;
            }
            $target_date = $target->modify( '+' . $index . ' days' )->format( 'Y-m-d' );
            $mapped[ $target_date ] = (int) $existing[ $date ];
        }

        if ( $replace ) {
            $this->delete_exception_range_raw( $provider_id, $target->format( 'Y-m-d' ), $target_end );
        }

        $count = 0;
        if ( ! empty( $mapped ) ) {
            $count = $this->upsert_exceptions( $provider_id, $mapped );
        }

        $this->flush_exception_cache( $provider_id );

        return $count;
    }

    /**
     * Build the UI availability payload from stored weekly/timeslot records.
     *
     * @param array<int,int|string>                    $weekly
     * @param array<int,object|array<string,mixed>>    $timeslots
     * @return array<string,mixed>
     */
    private function build_availability_payload( array $weekly, array $timeslots, int $slots_per_interval ): array
    {
        $availability = [
            'weekdays' => [],
            'interval' => self::DEFAULT_INTERVAL_MINUTES,
            'slots'    => $this->normalize_slots_per_interval( $slots_per_interval ),
            'times'    => $this->get_empty_times_map(),
        ];
        $interval_set = false;

        foreach ( self::DAY_NAMES as $weekday => $day_name ) {
            if ( ! empty( $weekly[ $weekday ] ) ) {
                $availability['weekdays'][] = $day_name;
            }
        }

        foreach ( $timeslots as $slot ) {
            $weekday = $this->read_timeslot_field( $slot, 'weekday' );
            if ( ! is_numeric( $weekday ) || ! isset( self::DAY_NAMES[ (int) $weekday ] ) ) {
                continue;
            }

            $day_name = self::DAY_NAMES[ (int) $weekday ];
            $start    = $this->normalize_time_string( $this->read_timeslot_field( $slot, 'start_time' ) );
            $end      = $this->normalize_time_string( $this->read_timeslot_field( $slot, 'end_time' ) );
            $interval = $this->normalize_interval( $this->read_timeslot_field( $slot, 'interval_m' ) );
            if ( ! $start || ! $end || ! $this->is_valid_time_range( $start, $end ) ) {
                continue;
            }

            if ( ! $interval_set ) {
                $availability['interval'] = $interval;
                $interval_set = true;
            }
            $availability['times'][ $day_name ][] = [
                'from' => $start,
                'to'   => $end,
            ];

            if ( ! in_array( $day_name, $availability['weekdays'], true ) ) {
                $availability['weekdays'][] = $day_name;
            }
        }

        $availability['weekdays'] = $this->normalize_weekdays( $availability['weekdays'] );

        foreach ( self::DAY_NAMES as $day_name ) {
            if ( empty( $availability['times'][ $day_name ] ) ) {
                continue;
            }
            usort(
                $availability['times'][ $day_name ],
                static function ( array $a, array $b ): int {
                    return strcmp( (string) ( $a['from'] ?? '' ), (string) ( $b['from'] ?? '' ) );
                }
            );
        }

        return $availability;
    }

    /**
     * Build a starter weekly schedule for first-time providers.
     *
     * @return array<string,mixed>
     */
    private function get_default_availability_payload( int $slots_per_interval ): array
    {
        $availability = [
            'weekdays' => self::DEFAULT_TEMPLATE_WEEKDAYS,
            'interval' => self::DEFAULT_INTERVAL_MINUTES,
            'slots'    => $this->normalize_slots_per_interval( $slots_per_interval ),
            'times'    => $this->get_empty_times_map(),
        ];

        foreach ( self::DEFAULT_TEMPLATE_WEEKDAYS as $day_name ) {
            $availability['times'][ $day_name ][] = [
                'from' => self::DEFAULT_TEMPLATE_START_TIME,
                'to'   => self::DEFAULT_TEMPLATE_END_TIME,
            ];
        }

        return $availability;
    }

    /**
     * Build a list of dates (Y-m-d) between two dates (inclusive).
     *
     * @return array|\WP_Error
     */
    private function build_date_range( string $from, string $to, ?int $max_days = null ) {
        $start = $this->parse_date( $from );
        $end   = $this->parse_date( $to );
        if ( ! $start || ! $end ) {
            return new \WP_Error(
                'bookaro_invalid_date',
                __( 'Invalid date supplied.', 'mybookaro' )
            );
        }
        if ( $end < $start ) {
            return new \WP_Error(
                'bookaro_invalid_range',
                __( 'Invalid date range.', 'mybookaro' )
            );
        }

        $days = (int) $start->diff( $end )->days + 1;
        $limit = $max_days ?? (int) apply_filters( 'bookaro_exceptions_range_max_days', self::DEFAULT_RANGE_MAX_DAYS, $from, $to );
        if ( $limit > 0 && $days > $limit ) {
            return new \WP_Error(
                'bookaro_range_too_large',
                sprintf(
                    /* translators: %d = max days */
                    __( 'Date range too large. Maximum %d days.', 'mybookaro' ),
                    $limit
                )
            );
        }

        $dates = [];
        $cursor = $start;
        while ( $cursor <= $end ) {
            $dates[] = $cursor->format( 'Y-m-d' );
            $cursor = $cursor->modify( '+1 day' );
        }

        return $dates;
    }

    private function parse_date( string $date ): ?\DateTimeImmutable
    {
        $date = trim( $date );
        if ( $date === '' ) {
            return null;
        }
        $dt = \DateTimeImmutable::createFromFormat( 'Y-m-d', $date, wp_timezone() );
        if ( ! $dt || $dt->format( 'Y-m-d' ) !== $date ) {
            return null;
        }
        return $dt;
    }

    /**
     * @return array<int,int>
     */
    private function get_empty_weekly_flags(): array
    {
        return array_fill( 0, count( self::DAY_NAMES ), 0 );
    }

    /**
     * @return array<string,array<int,array<string,string>>>
     */
    private function get_empty_times_map(): array
    {
        return array_fill_keys( self::DAY_NAMES, [] );
    }

    /**
     * @param array<mixed> $weekdays
     * @return array<int,string>
     */
    private function normalize_weekdays( array $weekdays ): array
    {
        $normalized = [];

        foreach ( self::DAY_NAMES as $day_name ) {
            if ( in_array( $day_name, $weekdays, true ) ) {
                $normalized[] = $day_name;
            }
        }

        return $normalized;
    }

    /**
     * @param mixed $interval
     */
    private function normalize_interval( $interval ): int
    {
        $interval = (int) $interval;
        if ( $interval < 1 ) {
            $interval = self::DEFAULT_INTERVAL_MINUTES;
        }

        return $interval;
    }

    /**
     * @param mixed $value
     */
    private function normalize_time_string( $value ): ?string
    {
        if ( ! is_string( $value ) ) {
            return null;
        }

        $value = trim( $value );
        if ( preg_match( '/^\d{2}:\d{2}$/', $value ) === 1 ) {
            $value .= ':00';
        }

        if ( preg_match( '/^\d{2}:\d{2}:\d{2}$/', $value ) !== 1 ) {
            return null;
        }

        $dt = \DateTimeImmutable::createFromFormat( 'H:i:s', $value, new \DateTimeZone( 'UTC' ) );
        if ( ! $dt || $dt->format( 'H:i:s' ) !== $value ) {
            return null;
        }

        return $value;
    }

    private function is_valid_time_range( string $start, string $end ): bool
    {
        return strcmp( $start, $end ) < 0;
    }

    /**
     * @param array<int,array<string,int|string>> $timeslots
     * @return array<int,array<string,int|string>>
     */
    private function sort_timeslots( array $timeslots ): array
    {
        usort(
            $timeslots,
            static function ( array $a, array $b ): int {
                $weekday_compare = ( (int) ( $a['weekday'] ?? 0 ) ) <=> ( (int) ( $b['weekday'] ?? 0 ) );
                if ( $weekday_compare !== 0 ) {
                    return $weekday_compare;
                }

                return strcmp( (string) ( $a['start_time'] ?? '' ), (string) ( $b['start_time'] ?? '' ) );
            }
        );

        return $timeslots;
    }

    /**
     * @param object|array<string,mixed> $slot
     * @return mixed
     */
    private function read_timeslot_field( $slot, string $field )
    {
        if ( is_array( $slot ) ) {
            return $slot[ $field ] ?? null;
        }

        if ( is_object( $slot ) ) {
            return $slot->{$field} ?? null;
        }

        return null;
    }

    /**
     * @param mixed $date
     */
    private function normalize_date_string( $date ): ?string
    {
        if ( ! is_string( $date ) ) {
            return null;
        }

        $parsed = $this->parse_date( $date );
        if ( ! $parsed ) {
            return null;
        }

        return $parsed->format( 'Y-m-d' );
    }

    /**
     * @param array<mixed> $values
     */
    private function is_list_array( array $values ): bool
    {
        if ( $values === [] ) {
            return true;
        }

        return array_keys( $values ) === range( 0, count( $values ) - 1 );
    }

    private function upsert_exceptions( int $provider_id, array $exceptions ): int
    {
        if ( empty( $exceptions ) ) {
            return 0;
        }
        $table = $this->wpdb->prefix . self::EXCEPTION_TABLE;
        $count = 0;
        foreach ( $exceptions as $date => $is_open ) {
            $result = $this->wpdb->replace(
                $table,
                [
                    'provider_id'    => $provider_id,
                    'exception_date' => sanitize_text_field( (string) $date ),
                    'is_open'        => $is_open ? 1 : 0,
                ],
                [ '%d', '%s', '%d' ]
            );
            if ( false !== $result ) {
                $count++;
            }
        }
        return $count;
    }

    private function delete_exception_range_raw( int $provider_id, string $from, string $to ): void
    {
        $table = $this->wpdb->prefix . self::EXCEPTION_TABLE;
        $this->wpdb->query(
            $this->wpdb->prepare(
                "DELETE FROM {$table} WHERE provider_id = %d AND exception_date BETWEEN %s AND %s",
                $provider_id,
                $from,
                $to
            )
        );
    }

    private function flush_exception_cache( int $provider_id ): void
    {
        delete_transient( "bookaro_exceptions_{$provider_id}" );
        $this->db->flush_provider_slot_cache( $provider_id );
    }

    /**
     * Public cache-invalidation entry point.
     *
     * Use this after any bulk-write or migration that touches
     * `bookaro_provider_availability`, `bookaro_provider_timeslots`,
     * or `bookaro_provider_exceptions` directly (i.e. without going through
     * ScheduleService::set_availability / save_timeslots / save_exceptions,
     * which already invalidate caches as a side effect).
     *
     * Pass `0` (or omit) to flush caches for all providers in one sweep.
     * Pass a specific user/provider ID to flush only that provider.
     *
     * @param int $provider_id Provider/user ID, or 0 for all.
     */
    public function flush_caches_for_provider( int $provider_id = 0 ): void
    {
        if ( $provider_id > 0 ) {
            delete_transient( "bookaro_avail_weekly_{$provider_id}" );
            delete_transient( "bookaro_timeslots_{$provider_id}" );
            delete_transient( "bookaro_exceptions_{$provider_id}" );
            $this->db->flush_provider_slot_cache( $provider_id );
            return;
        }

        // Sweep all providers. We look at the distinct provider_ids in the
        // schedule tables so we don't iterate every WP user.
        $tables = [
            $this->wpdb->prefix . self::AVAIL_TABLE,
            $this->wpdb->prefix . self::TIMESLOT_TABLE,
            $this->wpdb->prefix . self::EXCEPTION_TABLE,
        ];
        $provider_ids = [];
        foreach ( $tables as $table ) {
            $rows = $this->wpdb->get_col( "SELECT DISTINCT provider_id FROM {$table}" );
            if ( is_array( $rows ) ) {
                foreach ( $rows as $id ) {
                    $provider_ids[ (int) $id ] = true;
                }
            }
        }
        foreach ( array_keys( $provider_ids ) as $id ) {
            if ( $id < 1 ) {
                continue;
            }
            delete_transient( "bookaro_avail_weekly_{$id}" );
            delete_transient( "bookaro_timeslots_{$id}" );
            delete_transient( "bookaro_exceptions_{$id}" );
            $this->db->flush_provider_slot_cache( $id );
        }
    }

    /**
     * Build first-run guidance metadata for provider availability UI.
     *
     * @param array<string,mixed> $availability
     * @return array<string,mixed>
     */
    private function build_schedule_meta( array $availability, bool $has_saved_schedule ): array
    {
        $using_default_template = ! $has_saved_schedule;
        $buffer_before = absint( function_exists( 'bookaro_get_option' ) ? bookaro_get_option( 'booking_buffer_before_minutes', 0 ) : 0 );
        $buffer_after = absint( function_exists( 'bookaro_get_option' ) ? bookaro_get_option( 'booking_buffer_after_minutes', 0 ) : 0 );
        $slots_per_interval = $this->normalize_slots_per_interval( $availability['slots'] ?? self::DEFAULT_SLOTS_PER_INTERVAL );

        $buffer_summary = ( $buffer_before > 0 || $buffer_after > 0 )
            ? sprintf(
                /* translators: 1: minutes before booking, 2: minutes after booking */
                __( 'Global booking buffers are active (%1$d minutes before, %2$d minutes after). These buffers can hide nearby start times.', 'mybookaro' ),
                $buffer_before,
                $buffer_after
            )
            : __( 'Global booking buffers are currently off (0 minutes before / after).', 'mybookaro' );

        return [
            'using_default_template' => $using_default_template,
            'has_saved_schedule'     => $has_saved_schedule,
            'buffer_before_minutes'  => $buffer_before,
            'buffer_after_minutes'   => $buffer_after,
            'buffer_summary'         => $buffer_summary,
            'guidance'               => [
                'title' => __( 'First-run checklist', 'mybookaro' ),
                'description' => __( 'Confirm these basics before publishing your availability.', 'mybookaro' ),
                'items' => [
                    __( 'Start with your real working days and first time blocks, then save once to publish a clean baseline schedule.', 'mybookaro' ),
                    ( $slots_per_interval > 1 )
                        ? sprintf(
                            /* translators: %d: overlapping bookings allowed per start time */
                            __( 'Capacity is currently %d bookings per start time. Keep this only if you intentionally accept overlapping bookings.', 'mybookaro' ),
                            $slots_per_interval
                        )
                        : __( 'Capacity is set to 1 booking per start time. Keep this until you intentionally accept overlapping bookings.', 'mybookaro' ),
                    $buffer_summary,
                    __( 'After saving, run one test booking and confirm the offered slots match your expected availability.', 'mybookaro' ),
                ],
            ],
        ];
    }
}
// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
