<?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.

use Vizyt\Support\SortOrder;
use Vizyt\Support\Tx;

class Staff {

    /**
     * Calendar colours (admin tokens --vizyt-admin-staff-1…6). A staff
     * member's colour is derived from their position in the staff order,
     * never stored (umbrella spec, "Staff colour").
     */
    const PALETTE = [ '#C8643B', '#9567C9', '#4C7FD0', '#4C9A5B', '#B8892E', '#C65A86' ];

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

    /**
     * The staff <-> service mapping table (single definition of its name).
     */
    public static function mapTable(): string {
        global $wpdb;
        return $wpdb->prefix . 'vizyt_staff_services';
    }

    public static function getAll( array $args = [] ): array {
        global $wpdb;

        $page     = max( 1, (int) ( $args['page'] ?? 1 ) );
        $per_page = max( 1, (int) ( $args['per_page'] ?? 20 ) );
        $orderby  = $args['orderby'] ?? 'sort_order';
        $order    = ( $args['order'] ?? 'ASC' ) === 'DESC' ? 'DESC' : 'ASC';

        $allowed_orderby = [ 'sort_order', 'name', 'created_at', 'id' ];
        if ( ! in_array( $orderby, $allowed_orderby, true ) ) {
            $orderby = 'sort_order';
        }

        $where        = [];
        $where_values = [];

        if ( isset( $args['status'] ) ) {
            $where[]        = 'status = %s';
            $where_values[] = $args['status'];
        }

        if ( isset( $args['search'] ) && $args['search'] !== '' ) {
            $where[]        = '(name LIKE %s OR email LIKE %s)';
            $where_values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where_values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
        }

        $where_sql = '';
        if ( ! empty( $where ) ) {
            $where_sql = 'WHERE ' . implode( ' AND ', $where );
        }

        $table  = self::table();
        $offset = ( $page - 1 ) * $per_page;

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where_sql holds only the fixed "column = %s" fragments built above (every value goes through prepare()); $orderby and $order are whitelisted.
        if ( ! empty( $where_values ) ) {
            $count_query = $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} {$where_sql}",
                ...$where_values
            );
            $items_query = $wpdb->prepare(
                "SELECT * FROM {$table} {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d",
                ...array_merge( $where_values, [ $per_page, $offset ] )
            );
        } else {
            $count_query = "SELECT COUNT(*) FROM {$table}";
            $items_query = $wpdb->prepare(
                "SELECT * FROM {$table} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d",
                $per_page,
                $offset
            );
        }

        $total = (int) $wpdb->get_var( $count_query );
        $items = $wpdb->get_results( $items_query );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $pages = $per_page > 0 ? (int) ceil( $total / $per_page ) : 0;

        return [
            'items' => $items,
            'total' => $total,
            'pages' => $pages,
        ];
    }

    public static function getById( int $id ): ?object {
        global $wpdb;
        return $wpdb->get_row(
            $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), $id )
        ) ?: null;
    }

    /**
     * Active staff who provide a service, ordered by sort_order, id.
     *
     * Compatibility rule: a service with no rows in vizyt_staff_services is
     * provided by every active staff member, so sites that never mapped
     * services keep working. This is the only place the rule lives.
     *
     * @return object[] Staff rows.
     */
    public static function forService( int $service_id ): array {
        global $wpdb;

        $table  = self::table();
        $map    = self::mapTable();
        $mapped = (int) $wpdb->get_var(
            $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE service_id = %d', $map, $service_id )
        );

        if ( 0 === $mapped ) {
            return (array) $wpdb->get_results(
                $wpdb->prepare( 'SELECT * FROM %i WHERE status = %s ORDER BY sort_order ASC, id ASC', $table, 'active' )
            );
        }

        return (array) $wpdb->get_results(
            $wpdb->prepare(
                'SELECT s.* FROM %i s
                   INNER JOIN %i m ON m.staff_id = s.id
                  WHERE m.service_id = %d AND s.status = %s
                  ORDER BY s.sort_order ASC, s.id ASC',
                $table,
                $map,
                $service_id,
                'active'
            )
        );
    }

    /**
     * Services explicitly mapped to this staff member (not the compatibility rule).
     *
     * @return int[]
     */
    public static function getServiceIds( int $staff_id ): array {
        return self::getServiceIdsFor( [ $staff_id ] )[ $staff_id ] ?? [];
    }

    /**
     * Bulk variant of getServiceIds(): one query for many staff members.
     *
     * @param int[] $staff_ids
     * @return array<int, int[]> staff id => service ids (every requested id has a key).
     */
    public static function getServiceIdsFor( array $staff_ids ): array {
        global $wpdb;

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

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT staff_id, service_id FROM {$wpdb->prefix}vizyt_staff_services WHERE staff_id IN (" . implode( ',', array_fill( 0, count( $staff_ids ), '%d' ) ) . ') ORDER BY staff_id ASC, service_id ASC',
                $staff_ids
            )
        );

        foreach ( (array) $rows as $row ) {
            $grouped[ (int) $row->staff_id ][] = (int) $row->service_id;
        }

        return $grouped;
    }

    /**
     * Replace the services this staff member provides. Unknown service ids are
     * ignored. Pairs that stay keep their custom_price / custom_duration; new
     * pairs are inserted first and only then stale ones deleted, so the mapping
     * never passes through an empty state.
     *
     * @return bool false when the staff member does not exist or a write failed.
     */
    public static function setServiceIds( int $staff_id, array $service_ids ): bool {
        global $wpdb;

        if ( null === self::getById( $staff_id ) ) {
            return false;
        }

        $ids = array_values( array_unique( array_filter( array_map( 'absint', $service_ids ) ) ) );

        if ( $ids ) {
            $ids = array_map(
                'intval',
                (array) $wpdb->get_col(
                    $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}vizyt_services WHERE id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')', $ids )
                )
            );
        }

        return self::syncMap( 'staff_id', $staff_id, 'service_id', $ids );
    }

    /**
     * Make the set of `$other_col` values mapped to `$fixed_col = $fixed_id`
     * equal to $other_ids: insert missing pairs (one INSERT IGNORE), delete the
     * rest, in one transaction. Columns are internal constants, never user input.
     *
     * @internal Shared with Service::setStaffIds().
     *
     * @param int[] $other_ids Already validated, deduplicated ids.
     * @return bool false when a write failed (nothing is changed then).
     */
    public static function syncMap( string $fixed_col, int $fixed_id, string $other_col, array $other_ids ): bool {
        global $wpdb;

        $map = self::mapTable();

        return true === Tx::run(
            static function () use ( $wpdb, $map, $fixed_col, $fixed_id, $other_col, $other_ids ) {
                if ( ! $other_ids ) {
                    return false !== $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE %i = %d', $map, $fixed_col, $fixed_id ) );
                }

                $args = [ $map, $fixed_col, $other_col ];
                foreach ( $other_ids as $other_id ) {
                    $args[] = $fixed_id;
                    $args[] = $other_id;
                }
                // phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one (%d,%d) pair per id in $args; the sniff cannot count an array.
                $inserted = $wpdb->query( $wpdb->prepare( 'INSERT IGNORE INTO %i (%i, %i) VALUES ' . implode( ',', array_fill( 0, count( $other_ids ), '(%d,%d)' ) ), $args ) );
                // phpcs:enable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
                if ( false === $inserted ) {
                    return false;
                }

                return false !== $wpdb->query(
                    $wpdb->prepare(
                        'DELETE FROM %i WHERE %i = %d AND %i NOT IN (' . implode( ',', array_fill( 0, count( $other_ids ), '%d' ) ) . ')',
                        array_merge( [ $map, $fixed_col, $fixed_id, $other_col ], $other_ids )
                    )
                );
            }
        );
    }

    public static function create( array $data ): int|false {
        global $wpdb;

        $name = isset( $data['name'] ) && is_string( $data['name'] ) ? mb_substr( sanitize_text_field( $data['name'] ), 0, 100 ) : '';
        if ( '' === $name ) {
            return false;
        }

        $schedule = null;
        if ( isset( $data['schedule'] ) ) {
            $checked = Schedule::validateWeek( $data['schedule'] );
            if ( $checked['errors'] ) {
                return false;
            }
            $schedule = $checked['days'];
        }

        $fields = [
            'name' => $name,
        ];

        if ( isset( $data['wp_user_id'] ) ) {
            $fields['wp_user_id'] = absint( $data['wp_user_id'] ) ?: null;
        }

        if ( isset( $data['email'] ) ) {
            $fields['email'] = sanitize_email( $data['email'] );
        }

        if ( isset( $data['phone'] ) ) {
            $fields['phone'] = sanitize_text_field( $data['phone'] );
        }

        if ( isset( $data['job_title'] ) ) {
            $fields['job_title'] = substr( sanitize_text_field( (string) $data['job_title'] ), 0, 150 );
        }

        if ( isset( $data['description'] ) ) {
            $fields['description'] = wp_kses_post( $data['description'] );
        }

        if ( isset( $data['avatar_url'] ) ) {
            $fields['avatar_url'] = esc_url_raw( $data['avatar_url'] );
        }

        if ( isset( $data['status'] ) ) {
            $fields['status'] = in_array( $data['status'], [ 'active', 'inactive' ], true )
                ? $data['status']
                : 'active';
        }

        $fields['sort_order'] = isset( $data['sort_order'] )
            ? absint( $data['sort_order'] )
            : SortOrder::next( 'vizyt_staff' );

        $now                  = current_time( 'mysql', true );
        $fields['created_at'] = $now;
        $fields['updated_at'] = $now;

        // The row, its services and its first week are saved together or not at all.
        $id = Tx::run(
            static function () use ( $wpdb, $fields, $data, $schedule ) {
                if ( ! $wpdb->insert( self::table(), $fields ) ) {
                    return false;
                }

                $id = (int) $wpdb->insert_id;

                if ( isset( $data['service_ids'] ) && is_array( $data['service_ids'] ) && ! self::setServiceIds( $id, $data['service_ids'] ) ) {
                    return false;
                }

                if ( isset( $data['provides_service_ids'] ) && is_array( $data['provides_service_ids'] ) && false === self::setProvidedServiceIds( $id, $data['provides_service_ids'] ) ) {
                    return false;
                }

                if ( null !== $schedule && ! Schedule::replaceWeek( $id, $schedule ) ) {
                    return false;
                }

                return $id;
            }
        );

        return false === $id ? false : (int) $id;
    }

    public static function update( int $id, array $data ): bool {
        global $wpdb;

        $fields = [];

        if ( isset( $data['name'] ) && is_string( $data['name'] ) ) {
            $name = mb_substr( sanitize_text_field( $data['name'] ), 0, 100 );
            if ( $name !== '' ) {
                $fields['name'] = $name;
            }
        }

        if ( array_key_exists( 'wp_user_id', $data ) ) {
            $fields['wp_user_id'] = $data['wp_user_id'] === null ? null : absint( $data['wp_user_id'] );
        }

        if ( isset( $data['email'] ) ) {
            $fields['email'] = sanitize_email( $data['email'] );
        }

        if ( isset( $data['phone'] ) ) {
            $fields['phone'] = sanitize_text_field( $data['phone'] );
        }

        if ( isset( $data['job_title'] ) ) {
            $fields['job_title'] = substr( sanitize_text_field( (string) $data['job_title'] ), 0, 150 );
        }

        if ( isset( $data['description'] ) ) {
            $fields['description'] = wp_kses_post( $data['description'] );
        }

        if ( isset( $data['avatar_url'] ) ) {
            $fields['avatar_url'] = esc_url_raw( $data['avatar_url'] );
        }

        if ( isset( $data['status'] ) ) {
            $fields['status'] = in_array( $data['status'], [ 'active', 'inactive' ], true )
                ? $data['status']
                : 'active';
        }

        if ( isset( $data['sort_order'] ) ) {
            $fields['sort_order'] = absint( $data['sort_order'] );
        }

        // null = no mapping change requested; otherwise whether the write succeeded.
        $mapped = null;
        if ( isset( $data['service_ids'] ) && is_array( $data['service_ids'] ) ) {
            $mapped = self::setServiceIds( $id, $data['service_ids'] );
        }
        if ( isset( $data['provides_service_ids'] ) && is_array( $data['provides_service_ids'] ) ) {
            $mapped = false !== self::setProvidedServiceIds( $id, $data['provides_service_ids'] ) && false !== $mapped;
        }

        if ( empty( $fields ) ) {
            return true === $mapped;
        }

        $fields['updated_at'] = current_time( 'mysql', true );

        return $wpdb->update( self::table(), $fields, [ 'id' => $id ] ) !== false && false !== $mapped;
    }

    public static function delete( int $id ): bool {
        global $wpdb;

        $schedule_ids = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}vizyt_schedules WHERE staff_id = %d",
                $id
            )
        );

        foreach ( $schedule_ids as $schedule_id ) {
            ScheduleBreak::deleteBySchedule( (int) $schedule_id );
        }

        $wpdb->delete( $wpdb->prefix . 'vizyt_schedules', [ 'staff_id' => $id ] );
        SpecialDay::deleteByStaff( $id );
        $wpdb->delete( self::mapTable(), [ 'staff_id' => $id ] );

        return $wpdb->delete( self::table(), [ 'id' => $id ] ) !== false;
    }

    public static function count( array $args = [] ): int {
        global $wpdb;
        $table = self::table();

        if ( ! isset( $args['status'] ) ) {
            return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
        }

        return (int) $wpdb->get_var(
            $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = %s', $table, $args['status'] )
        );
    }

    /**
     * Colour of every staff member: PALETTE by position in sort_order, id
     * order (inactive staff keep their place, so colours do not jump).
     *
     * @return array<int, string> staff id => #RRGGBB
     */
    public static function colors(): array {
        global $wpdb;

        $ids = (array) $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i ORDER BY sort_order ASC, id ASC', self::table() ) );
        $out = [];
        foreach ( array_values( $ids ) as $index => $id ) {
            $out[ (int) $id ] = self::PALETTE[ $index % count( self::PALETTE ) ];
        }

        return $out;
    }

    /**
     * Services each staff member actually provides: the explicitly mapped
     * ones plus every service nobody is mapped to (the compatibility rule of
     * forService()). Any service status.
     *
     * @param int[]                 $staff_ids
     * @param array<int, int[]>|null $explicit getServiceIdsFor( $staff_ids ) when the caller already has it.
     * @return array<int, int[]> staff id => sorted service ids (every requested id has a key).
     */
    public static function providedServiceIdsFor( array $staff_ids, ?array $explicit = null ): array {
        global $wpdb;

        $explicit = $explicit ?? self::getServiceIdsFor( $staff_ids );
        if ( ! $explicit ) {
            return [];
        }

        $open = array_map(
            'intval',
            (array) $wpdb->get_col(
                $wpdb->prepare(
                    "SELECT s.id FROM {$wpdb->prefix}vizyt_services s
                       LEFT JOIN %i m ON m.service_id = s.id
                      WHERE m.service_id IS NULL",
                    self::mapTable()
                )
            )
        );

        foreach ( $explicit as $staff_id => $ids ) {
            $all = array_values( array_unique( array_merge( $ids, $open ) ) );
            sort( $all );
            $explicit[ $staff_id ] = $all;
        }

        return $explicit;
    }

    /**
     * Make this staff member provide exactly $service_ids without changing
     * who else provides each service (the staff drawer's "Послуги" tab):
     * - on for a service mapped to others: this person's row is added;
     * - off for a service mapped to this person and others: only this
     *   person's row is deleted;
     * - off for a service nobody is mapped to (= everyone): it is mapped to
     *   every other staff member, so they keep it;
     * - off where this person is the only one mapped (or nobody else exists):
     *   skipped — with nobody mapped the service would fall back to
     *   everyone, this person included. The admin UI explains this; the REST
     *   response lists such services in `kept_service_ids`.
     * Unknown service ids are ignored.
     *
     * Only this person's rows are written (plus the "everyone else" rows of
     * an open service), and the mapping is read with FOR UPDATE inside the
     * transaction, so a change another request made to someone else's
     * mapping in the meantime is kept.
     *
     * @param int[] $service_ids
     * @return int[]|false Provided service ids afterwards; false when the staff member does not exist or a write failed.
     */
    public static function setProvidedServiceIds( int $staff_id, array $service_ids ) {
        global $wpdb;

        if ( null === self::getById( $staff_id ) ) {
            return false;
        }

        $wanted = array_flip( array_filter( array_map( 'absint', $service_ids ) ) );
        $map    = self::mapTable();
        $staff  = self::table();

        $ok = Tx::run(
            static function () use ( $wpdb, $map, $staff, $wanted, $staff_id ) {
                $services = array_map( 'intval', (array) $wpdb->get_col( "SELECT id FROM {$wpdb->prefix}vizyt_services ORDER BY id ASC" ) );
                if ( ! $services ) {
                    return true;
                }

                // Lock the current mapping (rows and gaps) until the end of the transaction.
                $rows = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT service_id, staff_id FROM %i ORDER BY service_id ASC, staff_id ASC FOR UPDATE', $map ) );
                if ( '' !== $wpdb->last_error ) {
                    return false;
                }

                $mapping = [];
                foreach ( $rows as $row ) {
                    $mapping[ (int) $row->service_id ][] = (int) $row->staff_id;
                }

                $others = null;

                foreach ( $services as $service_id ) {
                    $explicit = $mapping[ $service_id ] ?? [];
                    $provides = ! $explicit || in_array( $staff_id, $explicit, true );
                    $wants    = isset( $wanted[ $service_id ] );

                    if ( $wants === $provides ) {
                        continue;
                    }

                    if ( $wants ) {
                        $done = $wpdb->query( $wpdb->prepare( 'INSERT IGNORE INTO %i (staff_id, service_id) VALUES (%d, %d)', $map, $staff_id, $service_id ) );
                    } elseif ( $explicit ) {
                        if ( [ $staff_id ] === $explicit ) {
                            continue; // The only provider: kept.
                        }
                        $done = $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE staff_id = %d AND service_id = %d', $map, $staff_id, $service_id ) );
                    } else {
                        if ( null === $others ) {
                            $others = array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE id <> %d ORDER BY id ASC', $staff, $staff_id ) ) );
                        }
                        if ( ! $others ) {
                            continue; // Nobody else to hand it to: kept.
                        }
                        $args = [ $map ];
                        foreach ( $others as $other ) {
                            $args[] = $other;
                            $args[] = $service_id;
                        }
                        // phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one (%d,%d) pair per staff member in $args; the sniff cannot count an array.
                        $done = $wpdb->query( $wpdb->prepare( 'INSERT IGNORE INTO %i (staff_id, service_id) VALUES ' . implode( ',', array_fill( 0, count( $others ), '(%d,%d)' ) ), $args ) );
                        // phpcs:enable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
                    }

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

                return true;
            }
        );

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

        return self::providedServiceIdsFor( [ $staff_id ] )[ $staff_id ] ?? [];
    }

    /**
     * Drag-and-drop / move up-down order (see SortOrder::apply()).
     *
     * @param int[] $ids
     * @return int[]|false
     */
    public static function reorder( array $ids ) {
        return SortOrder::apply( 'vizyt_staff', $ids );
    }
}