<?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 Service {

    private static function table(): string {
        global $wpdb;
        return $wpdb->prefix . 'vizyt_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', 'price', 'duration', '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['category_id'] ) ) {
            $where[]        = 'category_id = %d';
            $where_values[] = (int) $args['category_id'];
        }

        if ( isset( $args['search'] ) && $args['search'] !== '' ) {
            $where[]        = 'name LIKE %s';
            $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;
    }

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

        // Validate required fields
        $name = isset( $data['name'] ) ? sanitize_text_field( $data['name'] ) : '';
        $name = substr( $name, 0, 200 );
        if ( empty( $name ) ) {
            return false;
        }

        $duration = isset( $data['duration'] ) ? absint( $data['duration'] ) : 0;
        if ( $duration <= 0 ) {
            return false;
        }

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

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

        if ( isset( $data['price'] ) ) {
            $fields['price'] = number_format( floatval( $data['price'] ), 2, '.', '' );
        }

        $default_currency   = \Vizyt\Admin\Settings::get_default_currency();
        $fields['currency'] = isset( $data['currency'] ) && in_array( strtoupper( (string) $data['currency'] ), vizyt_currencies(), true )
            ? strtoupper( (string) $data['currency'] )
            : $default_currency;

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

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

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

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

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

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

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

        if ( isset( $data['color'] ) ) {
            if ( preg_match( '/^#[0-9a-fA-F]{6}$/', $data['color'] ) ) {
                $fields['color'] = $data['color'];
            } else {
                $fields['color'] = null;
            }
        }

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

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

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

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

                if ( isset( $data['staff_ids'] ) && is_array( $data['staff_ids'] ) && ! self::setStaffIds( $id, $data['staff_ids'] ) ) {
                    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'] ) ) {
            $name = substr( sanitize_text_field( $data['name'] ), 0, 200 );
            if ( $name !== '' ) {
                $fields['name'] = $name;
            }
        }

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

        if ( isset( $data['duration'] ) ) {
            $dur = absint( $data['duration'] );
            if ( $dur >= 1 ) {
                $fields['duration'] = $dur;
            }
        }

        if ( isset( $data['price'] ) ) {
            $fields['price'] = number_format( floatval( $data['price'] ), 2, '.', '' );
        }

        if ( isset( $data['currency'] ) ) {
            $fields['currency'] = in_array( strtoupper( (string) $data['currency'] ), vizyt_currencies(), true )
                ? strtoupper( (string) $data['currency'] )
                : 'UAH';
        }

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

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

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

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

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

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

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

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

        if ( isset( $data['color'] ) ) {
            if ( preg_match( '/^#[0-9a-fA-F]{6}$/', $data['color'] ) ) {
                $fields['color'] = $data['color'];
            } else {
                $fields['color'] = null;
            }
        }

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

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

        $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;
        $wpdb->delete( Staff::mapTable(), [ 'service_id' => $id ] );
        return $wpdb->delete( self::table(), [ 'id' => $id ] ) !== false;
    }

    /**
     * Staff explicitly mapped to this service (empty = every active staff member,
     * see Staff::forService()).
     *
     * @return int[]
     */
    public static function getStaffIds( int $service_id ): array {
        return self::getStaffIdsFor( [ $service_id ] )[ $service_id ] ?? [];
    }

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

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

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT service_id, staff_id FROM %i WHERE service_id IN (' . implode( ',', array_fill( 0, count( $service_ids ), '%d' ) ) . ') ORDER BY service_id ASC, staff_id ASC',
                array_merge( [ Staff::mapTable() ], $service_ids )
            )
        );

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

        return $grouped;
    }

    /**
     * Replace who provides this service. Unknown staff ids are ignored. Pairs
     * that stay keep their custom_price / custom_duration; new pairs are
     * inserted first, then stale ones deleted (no empty window).
     *
     * @return bool false when the service does not exist or a write failed.
     */
    public static function setStaffIds( int $service_id, array $staff_ids ): bool {
        global $wpdb;

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

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

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

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

    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 ) );
        }

        $status = $args['status'];

        if ( is_array( $status ) ) {
            if ( empty( $status ) ) {
                return 0;
            }
            return (int) $wpdb->get_var(
                $wpdb->prepare(
                    'SELECT COUNT(*) FROM %i WHERE status IN (' . implode( ', ', array_fill( 0, count( $status ), '%s' ) ) . ')',
                    array_merge( [ $table ], array_values( $status ) )
                )
            );
        }

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

    public static function getPublicList( ?int $category_id = null ): array {
        global $wpdb;
        if ( $category_id !== null ) {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT s.id, s.name, s.description, s.duration, s.price, s.currency,
                            s.color, s.image_url, s.category_id, c.name AS category_name
                       FROM %i s
                       LEFT JOIN {$wpdb->prefix}vizyt_categories c ON c.id = s.category_id
                      WHERE s.status = 'active' AND s.category_id = %d ORDER BY s.sort_order ASC",
                    self::table(),
                    $category_id
                )
            );
        }

        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT s.id, s.name, s.description, s.duration, s.price, s.currency,
                        s.color, s.image_url, s.category_id, c.name AS category_name
                   FROM %i s
                   LEFT JOIN {$wpdb->prefix}vizyt_categories c ON c.id = s.category_id
                  WHERE s.status = 'active' ORDER BY s.sort_order ASC",
                self::table()
            )
        );
    }

    /**
     * Bookings of any status that reference this service. A service with
     * bookings is not deleted (the REST API answers 409); it is disabled instead.
     */
    public static function bookingCount( int $id ): int {
        global $wpdb;

        return (int) $wpdb->get_var(
            $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->prefix}vizyt_bookings WHERE service_id = %d", $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_services', $ids );
    }
}