<?php


namespace Vizyt\Models;

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Vizyt's own tables ({prefix}vizyt_*); bookings and availability must be read fresh on every request.

class Schedule {

    private static function table(): string {
        global $wpdb;
        return $wpdb->prefix . 'vizyt_schedules';
    }

    private static function isValidTime( string $time ): bool {
        return (bool) preg_match( '/^([01]\d|2[0-3]):[0-5]\d(:[0-5]\d)?$/', $time );
    }

    /**
     * Get all schedules for a staff member, each with a `breaks` array.
     * Ordered by day_of_week ASC.
     */
    public static function getByStaff( int $staff_id ): array {
        global $wpdb;

        $schedules = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT * FROM %i WHERE staff_id = %d ORDER BY day_of_week ASC',
                self::table(),
                $staff_id
            )
        );

        if ( empty( $schedules ) ) {
            return [];
        }

        $ids              = array_column( $schedules, 'id' );
        $breaks_by_sched  = ScheduleBreak::getByScheduleIds( $ids );

        foreach ( $schedules as $schedule ) {
            $schedule->breaks = $breaks_by_sched[ (int) $schedule->id ] ?? [];
        }

        return $schedules;
    }

    /**
     * Get the schedule for a specific day. Returns null if not set (= day off).
     * The returned object includes a `breaks` array.
     */
    public static function getForDay( int $staff_id, int $day_of_week ): ?object {
        global $wpdb;

        $row = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM %i WHERE staff_id = %d AND day_of_week = %d',
                self::table(),
                $staff_id,
                $day_of_week
            )
        );

        if ( ! $row ) {
            return null;
        }

        $row->breaks = ScheduleBreak::getBySchedule( (int) $row->id );

        return $row;
    }

    /**
     * Create or update the schedule for a given staff + day combination.
     * Returns the schedule ID on success, false on validation failure.
     */
    public static function upsert( int $staff_id, int $day_of_week, string $start_time, string $end_time ): int|false {
        global $wpdb;

        if ( $day_of_week < 1 || $day_of_week > 7 ) {
            return false;
        }

        if ( ! self::isValidTime( $start_time ) || ! self::isValidTime( $end_time ) ) {
            return false;
        }

        $start_time = substr( $start_time, 0, 5 );
        $end_time   = substr( $end_time, 0, 5 );

        if ( $start_time >= $end_time ) {
            return false;
        }

        $existing = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT id FROM %i WHERE staff_id = %d AND day_of_week = %d',
                self::table(),
                $staff_id,
                $day_of_week
            )
        );

        if ( $existing ) {
            $wpdb->update(
                self::table(),
                [ 'start_time' => $start_time, 'end_time' => $end_time ],
                [ 'id' => (int) $existing->id ]
            );
            return (int) $existing->id;
        }

        $result = $wpdb->insert(
            self::table(),
            [
                'staff_id'    => $staff_id,
                'day_of_week' => $day_of_week,
                'start_time'  => $start_time,
                'end_time'    => $end_time,
            ]
        );

        return $result ? (int) $wpdb->insert_id : false;
    }

    /**
     * Delete a schedule by ID (cascades its breaks).
     */
    public static function delete( int $id ): bool {
        global $wpdb;
        ScheduleBreak::deleteBySchedule( $id );
        return $wpdb->delete( self::table(), [ 'id' => $id ] ) !== false;
    }

    /**
     * Delete the schedule for a given staff + day (cascades its breaks).
     */
    public static function deleteByStaffAndDay( int $staff_id, int $day_of_week ): bool {
        global $wpdb;

        $row = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT id FROM %i WHERE staff_id = %d AND day_of_week = %d',
                self::table(),
                $staff_id,
                $day_of_week
            )
        );

        if ( ! $row ) {
            return false;
        }

        return self::delete( (int) $row->id );
    }

    // -----------------------------------------------------------------------
    // Weekly editor (admin "Графік" tab, GET/PUT /staff/{id}/schedule)
    // -----------------------------------------------------------------------

    /** ISO days, Monday first. */
    const DAYS = [ 1, 2, 3, 4, 5, 6, 7 ];

    /** Breaks per day the editor accepts. */
    const MAX_BREAKS = 6;

    /**
     * "9:00", "09:00" or "09:00:00" → "09:00"; null for anything else.
     *
     * @param mixed $value
     */
    public static function normalizeTime( $value ): ?string {
        if ( ! is_string( $value ) || ! preg_match( '/^([01]?\d|2[0-3]):([0-5]\d)(?::[0-5]\d)?$/', trim( $value ), $m ) ) {
            return null;
        }

        return sprintf( '%02d:%s', (int) $m[1], $m[2] );
    }

    private static function minutes( string $hm ): int {
        return (int) substr( $hm, 0, 2 ) * 60 + (int) substr( $hm, 3, 2 );
    }

    /**
     * The week as the admin editor shows it: seven days, Monday first. A day
     * without a schedule row is a day off.
     *
     * @return array<int, array{day: int, enabled: bool, start: string, end: string, breaks: array<int, array{start: string, end: string}>}>
     */
    public static function getWeek( int $staff_id ): array {
        $by_day = [];
        foreach ( self::getByStaff( $staff_id ) as $row ) {
            $by_day[ (int) $row->day_of_week ] = $row;
        }

        $week = [];
        foreach ( self::DAYS as $day ) {
            $row    = $by_day[ $day ] ?? null;
            $week[] = [
                'day'     => $day,
                'enabled' => null !== $row,
                'start'   => $row ? substr( (string) $row->start_time, 0, 5 ) : '',
                'end'     => $row ? substr( (string) $row->end_time, 0, 5 ) : '',
                'breaks'  => $row ? array_map(
                    static fn( $b ) => [
                        'start' => substr( (string) $b->start_time, 0, 5 ),
                        'end'   => substr( (string) $b->end_time, 0, 5 ),
                    ],
                    $row->breaks
                ) : [],
            ];
        }

        return $week;
    }

    /**
     * Check a week sent by the admin editor. Every day 1–7 must appear once.
     * A working day needs start < end; breaks must be HH:MM, start < end,
     * inside the working hours and must not overlap (touching is fine).
     * Days off drop their hours and breaks.
     *
     * Error codes: not_a_list, bad_day, missing_days, bad_time, end_before_start,
     * breaks_not_a_list, too_many_breaks, break_order, break_outside, break_overlap.
     * `field` is "days", "day", "start", "end", "breaks" or "breaks.N" (N = index sent).
     *
     * @param mixed $days
     * @return array{days: array<int, array{day: int, enabled: bool, start: string, end: string, breaks: array<int, array{start: string, end: string}>}>, errors: array<int, array{day: int, field: string, code: string}>}
     */
    public static function validateWeek( $days ): array {
        if ( ! is_array( $days ) ) {
            return [ 'days' => [], 'errors' => [ [ 'day' => 0, 'field' => 'days', 'code' => 'not_a_list' ] ] ];
        }

        $errors = [];
        $clean  = [];

        foreach ( $days as $entry ) {
            $day = is_array( $entry ) && isset( $entry['day'] ) && is_numeric( $entry['day'] ) ? (int) $entry['day'] : 0;
            if ( $day < 1 || $day > 7 || isset( $clean[ $day ] ) ) {
                $errors[] = [ 'day' => $day, 'field' => 'day', 'code' => 'bad_day' ];
                continue;
            }

            $clean[ $day ] = [ 'day' => $day, 'enabled' => false, 'start' => '', 'end' => '', 'breaks' => [] ];
            // JSON true/false, "true"/"false", 1/0 and "1"/"0" all work.
            if ( ! isset( $entry['enabled'] ) || ! rest_sanitize_boolean( $entry['enabled'] ) ) {
                continue;
            }

            $start = self::normalizeTime( $entry['start'] ?? null );
            $end   = self::normalizeTime( $entry['end'] ?? null );
            if ( null === $start ) {
                $errors[] = [ 'day' => $day, 'field' => 'start', 'code' => 'bad_time' ];
            }
            if ( null === $end ) {
                $errors[] = [ 'day' => $day, 'field' => 'end', 'code' => 'bad_time' ];
            }
            if ( null === $start || null === $end ) {
                continue;
            }
            if ( $start >= $end ) {
                $errors[] = [ 'day' => $day, 'field' => 'end', 'code' => 'end_before_start' ];
                continue;
            }

            $breaks_in = $entry['breaks'] ?? [];
            if ( ! is_array( $breaks_in ) ) {
                $errors[] = [ 'day' => $day, 'field' => 'breaks', 'code' => 'breaks_not_a_list' ];
                continue;
            }
            if ( count( $breaks_in ) > self::MAX_BREAKS ) {
                $errors[] = [ 'day' => $day, 'field' => 'breaks', 'code' => 'too_many_breaks' ];
                continue;
            }

            $breaks = [];
            $valid  = true;
            foreach ( array_values( $breaks_in ) as $i => $break ) {
                $field = 'breaks.' . $i;
                $b_s   = is_array( $break ) ? self::normalizeTime( $break['start'] ?? null ) : null;
                $b_e   = is_array( $break ) ? self::normalizeTime( $break['end'] ?? null ) : null;
                if ( null === $b_s || null === $b_e ) {
                    $errors[] = [ 'day' => $day, 'field' => $field, 'code' => 'bad_time' ];
                    $valid    = false;
                } elseif ( $b_s >= $b_e ) {
                    $errors[] = [ 'day' => $day, 'field' => $field, 'code' => 'break_order' ];
                    $valid    = false;
                } elseif ( $b_s < $start || $b_e > $end ) {
                    $errors[] = [ 'day' => $day, 'field' => $field, 'code' => 'break_outside' ];
                    $valid    = false;
                } else {
                    $breaks[] = [ 'start' => $b_s, 'end' => $b_e, 'index' => $i ];
                }
            }

            // Sorted by start, a break overlaps when it starts before the
            // latest end seen so far (not only the previous break's end:
            // 10–14 contains both 11–11:30 and 12–12:30).
            usort( $breaks, static fn( $a, $b ) => strcmp( $a['start'], $b['start'] ) ?: $a['index'] <=> $b['index'] );
            $max_end = '';
            foreach ( $breaks as $break ) {
                if ( '' !== $max_end && $break['start'] < $max_end ) {
                    $errors[] = [ 'day' => $day, 'field' => 'breaks.' . $break['index'], 'code' => 'break_overlap' ];
                    $valid    = false;
                }
                $max_end = max( $max_end, $break['end'] );
            }

            $clean[ $day ] = [
                'day'     => $day,
                'enabled' => true,
                'start'   => $start,
                'end'     => $end,
                'breaks'  => $valid ? array_map( static fn( $b ) => [ 'start' => $b['start'], 'end' => $b['end'] ], $breaks ) : [],
            ];
        }

        if ( ! $errors && 7 !== count( $clean ) ) {
            $errors[] = [ 'day' => 0, 'field' => 'days', 'code' => 'missing_days' ];
        }

        ksort( $clean );

        return [ 'days' => array_values( $clean ), 'errors' => $errors ];
    }

    /**
     * Working minutes in a (validated) week: hours minus breaks.
     *
     * @param array<int, array{enabled: bool, start: string, end: string, breaks: array}> $days
     */
    public static function totalMinutes( array $days ): int {
        $total = 0;
        foreach ( $days as $day ) {
            if ( empty( $day['enabled'] ) ) {
                continue;
            }
            $total += self::minutes( $day['end'] ) - self::minutes( $day['start'] );
            foreach ( $day['breaks'] as $break ) {
                $total -= self::minutes( $break['end'] ) - self::minutes( $break['start'] );
            }
        }

        return $total;
    }

    /**
     * Replace the whole week of a staff member in one transaction: schedule
     * rows and their breaks are deleted and written again. Pass the `days`
     * of validateWeek() — this method does not validate.
     */
    public static function replaceWeek( int $staff_id, array $days ): bool {
        global $wpdb;

        $table  = self::table();
        $breaks = $wpdb->prefix . 'vizyt_schedule_breaks';

        return true === \Vizyt\Support\Tx::run(
            static function () use ( $wpdb, $table, $breaks, $staff_id, $days ) {
                // One statement, so breaks of a day added meanwhile go too (no orphans).
                if ( false === $wpdb->query( $wpdb->prepare( 'DELETE b FROM %i b JOIN %i s ON s.id = b.schedule_id WHERE s.staff_id = %d', $breaks, $table, $staff_id ) ) ) {
                    return false;
                }
                if ( false === $wpdb->delete( $table, [ 'staff_id' => $staff_id ] ) ) {
                    return false;
                }

                foreach ( $days as $day ) {
                    if ( empty( $day['enabled'] ) ) {
                        continue;
                    }
                    $row = [
                        'staff_id'    => $staff_id,
                        'day_of_week' => (int) $day['day'],
                        'start_time'  => $day['start'],
                        'end_time'    => $day['end'],
                    ];
                    if ( ! $wpdb->insert( $table, $row ) ) {
                        return false;
                    }
                    $schedule_id = (int) $wpdb->insert_id;
                    foreach ( $day['breaks'] as $break ) {
                        $ok = $wpdb->insert(
                            $breaks,
                            [ 'schedule_id' => $schedule_id, 'start_time' => $break['start'], 'end_time' => $break['end'] ]
                        );
                        if ( ! $ok ) {
                            return false;
                        }
                    }
                }

                return true;
            }
        );
    }

    /**
     * Working hours of many staff members on one date (business timezone
     * date, YYYY-MM-DD), with the special days applied (see workingDays()).
     * null = not working that day.
     *
     * @param int[] $staff_ids
     * @return array<int, array{start: string, end: string}|null> Every requested id has a key.
     */
    public static function hoursOn( array $staff_ids, string $date ): array {
        $out = [];
        foreach ( self::workingDays( $staff_ids, $date, $date ) as $id => $days ) {
            $day        = $days[ $date ] ?? null;
            $out[ $id ] = $day ? [ 'start' => $day['start'], 'end' => $day['end'] ] : null;
        }

        return $out;
    }

    /**
     * Working time of many staff members on every date of a range (business
     * timezone dates, inclusive), in three queries. The special days apply
     * in this order (documented on SpecialDay):
     *   1. a business closure — nobody works;
     *   2. the person's day off;
     *   3. the person's special hours (no breaks);
     *   4. the weekly schedule with its breaks (no row = day off);
     *   5. business special hours cap 3/4 to their overlap (none = day off);
     *      breaks are clipped to the new window.
     *
     * @param int[] $staff_ids
     * @return array<int, array<string, array{start: string, end: string, breaks: array<int, array{start: string, end: string}>}|null>>
     *         staff id => date => hours or null. Every requested id and date has a key.
     */
    public static function workingDays( array $staff_ids, string $date_from, string $date_to ): array {
        global $wpdb;

        $staff_ids = array_values( array_unique( array_filter( array_map( 'absint', $staff_ids ) ) ) );
        $valid     = static fn( $d ) => is_string( $d ) && preg_match( '/^(\d{4})-(\d{2})-(\d{2})$/', $d, $m ) && checkdate( (int) $m[2], (int) $m[3], (int) $m[1] );
        if ( ! $staff_ids || ! $valid( $date_from ) || ! $valid( $date_to ) || $date_to < $date_from ) {
            return array_fill_keys( $staff_ids, [] );
        }

        // Weekly rows and their breaks: staff id => day of week => hours.
        $weekly = [];
        $rows   = (array) $wpdb->get_results(
            $wpdb->prepare(
                'SELECT id, staff_id, day_of_week, start_time, end_time FROM %i WHERE staff_id IN (' . implode( ',', array_fill( 0, count( $staff_ids ), '%d' ) ) . ')',
                array_merge( [ self::table() ], $staff_ids )
            )
        );
        $breaks = ScheduleBreak::getByScheduleIds( array_map( static fn( $r ) => (int) $r->id, $rows ) );
        foreach ( $rows as $row ) {
            $weekly[ (int) $row->staff_id ][ (int) $row->day_of_week ] = [
                'start'  => substr( (string) $row->start_time, 0, 5 ),
                'end'    => substr( (string) $row->end_time, 0, 5 ),
                'breaks' => array_map(
                    static fn( $b ) => [ 'start' => substr( (string) $b->start_time, 0, 5 ), 'end' => substr( (string) $b->end_time, 0, 5 ) ],
                    $breaks[ (int) $row->id ] ?? []
                ),
            ];
        }

        // Special days of these people and of the whole business: date => scope => row.
        $special = [];
        $list    = (array) $wpdb->get_results(
            $wpdb->prepare(
                "SELECT staff_id, date, is_working, start_time, end_time FROM {$wpdb->prefix}vizyt_special_days
                  WHERE date >= %s AND date <= %s AND (staff_id IS NULL OR staff_id IN (" . implode( ',', array_fill( 0, count( $staff_ids ), '%d' ) ) . '))',
                array_merge( [ $date_from, $date_to ], $staff_ids )
            )
        );
        foreach ( $list as $row ) {
            $special[ (string) $row->date ][ null === $row->staff_id ? 'business' : (int) $row->staff_id ] = [
                'working' => (bool) (int) $row->is_working && $row->start_time && $row->end_time,
                'start'   => $row->start_time ? substr( (string) $row->start_time, 0, 5 ) : '',
                'end'     => $row->end_time ? substr( (string) $row->end_time, 0, 5 ) : '',
            ];
        }

        $out = array_fill_keys( $staff_ids, [] );
        for ( $day = new \DateTimeImmutable( $date_from ); $day->format( 'Y-m-d' ) <= $date_to; $day = $day->modify( '+1 day' ) ) {
            $date     = $day->format( 'Y-m-d' );
            $dow      = (int) $day->format( 'N' );
            $business = $special[ $date ]['business'] ?? null;

            foreach ( $staff_ids as $id ) {
                if ( $business && ! $business['working'] ) {
                    $out[ $id ][ $date ] = null; // 1. closed
                    continue;
                }
                $own = $special[ $date ][ $id ] ?? null;
                if ( $own ) {
                    $hours = $own['working'] ? [ 'start' => $own['start'], 'end' => $own['end'], 'breaks' => [] ] : null; // 2, 3
                } else {
                    $hours = $weekly[ $id ][ $dow ] ?? null; // 4
                }
                if ( $hours && $business ) { // 5. capped by the business hours
                    $start = max( $hours['start'], $business['start'] );
                    $end   = min( $hours['end'], $business['end'] );
                    if ( $start >= $end ) {
                        $hours = null;
                    } else {
                        $clipped = [];
                        foreach ( $hours['breaks'] as $break ) {
                            $from = max( $break['start'], $start );
                            $to   = min( $break['end'], $end );
                            if ( $from < $to ) {
                                $clipped[] = [ 'start' => $from, 'end' => $to ];
                            }
                        }
                        $hours = [ 'start' => $start, 'end' => $end, 'breaks' => $clipped ];
                    }
                }
                $out[ $id ][ $date ] = $hours;
            }
        }

        return $out;
    }

    /**
     * Weekly working days per staff member (1 = Monday … 7 = Sunday), for
     * the greyed weekly days off of the Days off grid (5a).
     *
     * @param int[] $staff_ids
     * @return array<int, int[]> Every requested id has a key.
     */
    public static function weekdaysFor( array $staff_ids ): array {
        global $wpdb;

        $staff_ids = array_values( array_unique( array_filter( array_map( 'absint', $staff_ids ) ) ) );
        if ( ! $staff_ids ) {
            return [];
        }

        $out  = array_fill_keys( $staff_ids, [] );
        $rows = (array) $wpdb->get_results(
            $wpdb->prepare(
                'SELECT staff_id, day_of_week FROM %i WHERE staff_id IN (' . implode( ',', array_fill( 0, count( $staff_ids ), '%d' ) ) . ') ORDER BY day_of_week ASC',
                array_merge( [ self::table() ], $staff_ids )
            )
        );
        foreach ( $rows as $row ) {
            $out[ (int) $row->staff_id ][] = (int) $row->day_of_week;
        }

        return $out;
    }

    /**
     * Number of working days in the weekly schedule, per staff member.
     *
     * @param int[] $staff_ids
     * @return array<int, int> Every requested id has a key.
     */
    public static function workingDaysFor( array $staff_ids ): array {
        global $wpdb;

        $staff_ids = array_values( array_unique( array_filter( array_map( 'absint', $staff_ids ) ) ) );
        if ( ! $staff_ids ) {
            return [];
        }

        $out  = array_fill_keys( $staff_ids, 0 );
        $rows = (array) $wpdb->get_results(
            $wpdb->prepare(
                'SELECT staff_id, COUNT(*) AS days FROM %i WHERE staff_id IN (' . implode( ',', array_fill( 0, count( $staff_ids ), '%d' ) ) . ') GROUP BY staff_id',
                array_merge( [ self::table() ], $staff_ids )
            )
        );
        foreach ( $rows as $row ) {
            $out[ (int) $row->staff_id ] = (int) $row->days;
        }

        return $out;
    }
}