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

class Category {

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

    public static function getAll( array $args = [] ): array {
        global $wpdb;
        $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';
        }

        // $orderby is whitelisted above; the direction is one of two literals.
        if ( 'DESC' === $order ) {
            return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY %i DESC', self::table(), $orderby ) );
        }

        return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY %i ASC', self::table(), $orderby ) );
    }

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

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

        $name = isset( $data['name'] ) ? sanitize_text_field( $data['name'] ) : '';
        if ( empty( $name ) ) {
            return false;
        }

        $slug = isset( $data['slug'] ) ? sanitize_title( $data['slug'] ) : sanitize_title( $name );
        $slug = self::uniqueSlug( $slug );

        $result = $wpdb->insert( self::table(), [
            'name'       => $name,
            'slug'       => $slug,
            'description'=> isset( $data['description'] ) ? sanitize_text_field( $data['description'] ) : null,
            'sort_order' => isset( $data['sort_order'] ) ? absint( $data['sort_order'] ) : SortOrder::next( 'vizyt_categories' ),
            'created_at' => current_time( 'mysql', true ),
        ] );

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

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

        $fields = [];
        if ( isset( $data['name'] ) ) {
            $name = sanitize_text_field( $data['name'] );
            if ( '' === $name ) {
                return false;
            }
            $fields['name'] = $name;
        }
        if ( isset( $data['slug'] ) ) {
            $fields['slug'] = self::uniqueSlug( sanitize_title( $data['slug'] ), $id );
        }
        if ( isset( $data['description'] ) ) {
            $fields['description'] = sanitize_text_field( $data['description'] );
        }
        if ( isset( $data['sort_order'] ) ) {
            $fields['sort_order'] = absint( $data['sort_order'] );
        }

        if ( empty( $fields ) ) {
            return false;
        }

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

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

        // Nullify category_id on related services
        $wpdb->update(
            $wpdb->prefix . 'vizyt_services',
            [ 'category_id' => null ],
            [ 'category_id' => $id ]
        );

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

    public static function count(): int {
        global $wpdb;
        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', self::table() ) );
    }

    private static function uniqueSlug( string $slug, ?int $exclude_id = null ): string {
        global $wpdb;
        $original = $slug;
        $counter  = 2;

        while ( true ) {
            $count = $exclude_id
                ? $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE slug = %s AND id != %d', self::table(), $slug, $exclude_id ) )
                : $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE slug = %s', self::table(), $slug ) );
            if ( ! $count ) {
                break;
            }
            $slug = $original . '-' . $counter;
            $counter++;
        }

        return $slug;
    }

    /**
     * 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_categories', $ids );
    }
}