<?php
/**
 * Feature (Attribute) value ordering — single source of truth.
 *
 * Every place that lists an attribute's values (admin Choices tab, product
 * editor Attributes tab, variation generator, storefront Product Filters,
 * PDP option selectors, product-card swatches) orders them through this
 * class so the per-attribute `sort_mode` on tc_product_features is honoured
 * everywhere at once (roadmap #11).
 *
 *   sort_mode = 'manual'  → ORDER BY v.position, v.id   (store-defined order;
 *                            the default, so existing stores see no change)
 *   sort_mode = 'alpha'   → ORDER BY vt.name (collation-aware, case-insensitive),
 *                            v.id — `position` is NOT rewritten, so switching
 *                            back to 'manual' restores the hand-made order.
 *
 * Only the explicit "Sort A–Z now" admin action rewrites `position`
 * (see FeaturesController::sort_variants_alpha()).
 *
 * The collation is derived from the connection charset so the clause is valid
 * on utf8mb4 (modern WP) and legacy utf8 installs alike; on anything else it
 * falls back to the column's own collation.
 *
 * @package TitanCart\Modules\Products
 */

namespace TitanCart\Modules\Products;

defined( 'ABSPATH' ) || exit;

final class FeatureValueOrder {

    public const MANUAL = 'manual';
    public const ALPHA  = 'alpha';

    /**
     * Normalise an arbitrary input to a valid sort_mode.
     */
    public static function normalize( $mode ): string {
        return $mode === self::ALPHA ? self::ALPHA : self::MANUAL;
    }

    /**
     * TRUE when the site runs on a non-MySQL engine — the WordPress SQLite
     * integration (Playground, Plugin Check's SQLite mode, wp-now). The
     * integration defines DB_ENGINE = 'sqlite'; non-MySQL drop-ins also
     * clear wpdb::$is_mysql. KI-112 (S468).
     */
    public static function is_sqlite(): bool {
        global $wpdb;
        if ( defined( 'DB_ENGINE' ) && DB_ENGINE === 'sqlite' ) return true;
        return isset( $wpdb->is_mysql ) && ! $wpdb->is_mysql;
    }

    /**
     * Collation suffix for name comparisons, e.g. " COLLATE utf8mb4_unicode_ci".
     * Empty string when the connection charset is not utf8mb4/utf8.
     */
    public static function collate(): string {
        global $wpdb;
        // KI-112 (S468): MySQL collation names are a PARSE error on SQLite —
        // and the connection "charset" still reads utf8/utf8mb4 there, so the
        // charset check alone does not protect us. SQLite's BINARY default is
        // an acceptable alpha order (NOCASE only covers ASCII anyway).
        if ( self::is_sqlite() ) return '';
        $charset = isset( $wpdb->charset ) ? strtolower( (string) $wpdb->charset ) : '';
        if ( $charset === 'utf8mb4' ) return ' COLLATE utf8mb4_unicode_ci';
        if ( $charset === 'utf8' )    return ' COLLATE utf8_unicode_ci';
        return '';
    }

    /**
     * ORDER BY fragment (without the "ORDER BY" keyword) for a query that has
     * the features table joined.
     *
     * @param string $f  Alias of tc_product_features.
     * @param string $v  Alias of tc_product_feature_variants.
     * @param string $vt Alias of tc_product_feature_variant_translations.
     */
    public static function clause( string $f = 'f', string $v = 'v', string $vt = 'vt' ): string {
        $c = self::collate();
        // KI-112 (S468): CASE WHEN, not IF() — IF() is MySQL-only, and on
        // SQLite (the WP.org reviewer's environment) it made EVERY choices /
        // values query through this clause fail silently to an empty array:
        // PDP option selectors, storefront filters, the product editor's
        // Attributes tab, the admin Choices list. CASE is byte-identical in
        // behaviour on MySQL/MariaDB and portable to SQLite and Postgres
        // (WordPress-independence goal).
        return "CASE WHEN {$f}.sort_mode = 'alpha' THEN 0 ELSE {$v}.position END ASC, "
             . "CASE WHEN {$f}.sort_mode = 'alpha' THEN {$vt}.name{$c} ELSE '' END ASC, "
             . "{$v}.id ASC";
    }

    /**
     * ORDER BY fragment for a query scoped to ONE feature whose mode is
     * already known (no features join required).
     */
    public static function clause_for_mode( string $mode, string $v = 'v', string $vt = 'vt' ): string {
        if ( self::normalize( $mode ) === self::ALPHA ) {
            return "{$vt}.name" . self::collate() . " ASC, {$v}.id ASC";
        }
        return "{$v}.position ASC, {$v}.id ASC";
    }

    /**
     * Look up a feature's sort_mode. Defaults to 'manual' when the feature (or
     * the column, on a not-yet-migrated install) is absent.
     */
    public static function mode_for( int $feature_id ): string {
        $db  = \titancart_db();
        $val = $db->getField(
            "SELECT sort_mode FROM {$db->table('product_features')} WHERE id = ?i",
            $feature_id
        );
        return self::normalize( $val );
    }
}
