<?php


namespace Vizyt\Support;

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Vizyt's own tables ({prefix}vizyt_*); the order is read with FOR UPDATE inside a transaction, so it must not come from a cache.

/**
 * Drag-and-drop order for categories, services and staff (sort_order column).
 */
final class SortOrder {

    /** Tables (without prefix) that have a sort_order column. Never user input. */
    const TABLES = [ 'vizyt_categories', 'vizyt_services', 'vizyt_staff' ];

    /**
     * Put $ids first, in the given order, then every other row in its current
     * order (sort_order, id), and store sort_order = 1…n. Unknown, zero and
     * repeated ids are ignored. The admin always sends every id; a partial
     * list therefore moves those rows to the top.
     *
     * @param int[] $ids
     * @return int[]|false Every row id in the new order; false on an unknown table or a failed write.
     */
    public static function apply( string $table, array $ids ) {
        global $wpdb;

        if ( ! in_array( $table, self::TABLES, true ) ) {
            return false;
        }

        $full  = $wpdb->prefix . $table;
        $final = [];

        // The current order is read inside the transaction with FOR UPDATE,
        // so a row added or moved by another request meanwhile is not lost.
        $ok = Tx::run(
            static function () use ( $wpdb, $full, $ids, &$final ) {
                $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, sort_order FROM %i ORDER BY sort_order ASC, id ASC FOR UPDATE', $full ) );
                if ( '' !== $wpdb->last_error ) {
                    return false;
                }

                $old = [];
                foreach ( (array) $rows as $row ) {
                    $old[ (int) $row->id ] = (int) $row->sort_order;
                }

                $wanted = [];
                foreach ( $ids as $id ) {
                    $id = is_scalar( $id ) ? absint( $id ) : 0;
                    if ( isset( $old[ $id ] ) && ! in_array( $id, $wanted, true ) ) {
                        $wanted[] = $id;
                    }
                }

                $final = array_merge( $wanted, array_values( array_diff( array_keys( $old ), $wanted ) ) );

                foreach ( $final as $index => $id ) {
                    if ( $old[ $id ] !== $index + 1 && false === $wpdb->update( $full, [ 'sort_order' => $index + 1 ], [ 'id' => $id ] ) ) {
                        return false;
                    }
                }
                return true;
            }
        );

        return false === $ok ? false : $final;
    }

    /**
     * sort_order for a new row: after every existing one.
     */
    public static function next( string $table ): int {
        global $wpdb;

        if ( ! in_array( $table, self::TABLES, true ) ) {
            return 0;
        }

        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COALESCE(MAX(sort_order), 0) + 1 FROM %i', $wpdb->prefix . $table ) );
    }
}