<?php
/**
 * Site-wide Player Directory (unified profiles).
 *
 * @package Chess_Podium
 */
 
if (!defined('ABSPATH')) {
    exit;
}
 
final class ChessPodium_PlayerDirectory
{
    public static function get_players_table(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'chess_podium_players';
    }
 
    public static function get_aliases_table(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'chess_podium_player_aliases';
    }
 
    public static function get_gp_map_table(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'chess_podium_gp_player_map';
    }
 
    public static function create_tables(): void
    {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $wpdb->get_charset_collate();
        $players = self::get_players_table();
        $aliases = self::get_aliases_table();
        $gpMap = self::get_gp_map_table();
 
        // Note: dbDelta requires PRIMARY KEY.
        $sqlPlayers = "CREATE TABLE $players (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            slug VARCHAR(191) NOT NULL,
            display_name VARCHAR(191) NOT NULL,
            fide_id VARCHAR(20) NULL,
            country VARCHAR(2) NULL,
            bio TEXT NULL,
            avatar_attachment_id BIGINT UNSIGNED NULL,
            chesscom_username VARCHAR(50) NULL,
            lichess_username VARCHAR(50) NULL,
            wp_user_id BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY fide_id (fide_id),
            UNIQUE KEY wp_user_id (wp_user_id)
        ) $charset;";
 
        $sqlAliases = "CREATE TABLE $aliases (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            player_id BIGINT UNSIGNED NOT NULL,
            alias_type VARCHAR(20) NOT NULL,
            alias_value VARCHAR(191) NOT NULL,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY alias_unique (alias_type, alias_value),
            KEY player_id (player_id)
        ) $charset;";
 
        $sqlGpMap = "CREATE TABLE $gpMap (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            grand_prix_id BIGINT UNSIGNED NOT NULL,
            player_key VARCHAR(191) NOT NULL,
            player_id BIGINT UNSIGNED NOT NULL,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY gp_player_key (grand_prix_id, player_key),
            KEY player_id (player_id),
            KEY grand_prix_id (grand_prix_id)
        ) $charset;";
 
        dbDelta($sqlPlayers);
        dbDelta($sqlAliases);
        dbDelta($sqlGpMap);
    }
 
    public static function sanitize_slug(string $slug): string
    {
        $slug = sanitize_title($slug);
        return $slug !== '' ? $slug : '';
    }
 
    public static function normalize_name_alias(string $name): string
    {
        $name = trim(mb_strtolower((string) $name));
        $name = preg_replace('/\s+/', ' ', $name);
        return trim((string) $name);
    }
 
    public static function normalize_fide_id_alias(string $fideId): string
    {
        $fid = preg_replace('/\D+/', '', (string) $fideId);
        return $fid ? (string) $fid : '';
    }
 
    public static function get_by_slug(string $slug): ?object
    {
        global $wpdb;
        $slug = self::sanitize_slug($slug);
        if ($slug === '') {
            return null;
        }
        $table = self::get_players_table();
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE slug = %s", $slug));
    }
 
    public static function get_by_id(int $playerId): ?object
    {
        global $wpdb;
        if ($playerId <= 0) {
            return null;
        }
        $table = self::get_players_table();
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE id = %d", $playerId));
    }
 
    public static function get_by_wp_user_id(int $wpUserId): ?object
    {
        global $wpdb;
        if ($wpUserId <= 0) {
            return null;
        }
        $table = self::get_players_table();
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE wp_user_id = %d", $wpUserId));
    }
 
    public static function find_by_fide_id(string $fideId): ?object
    {
        global $wpdb;
        $fid = self::normalize_fide_id_alias($fideId);
        if ($fid === '') {
            return null;
        }
        $table = self::get_players_table();
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE fide_id = %s", $fid));
        if ($row) {
            return $row;
        }
        $aliases = self::get_aliases_table();
        $pid = $wpdb->get_var($wpdb->prepare("SELECT player_id FROM $aliases WHERE alias_type = %s AND alias_value = %s", 'fide_id', $fid));
        return $pid ? self::get_by_id((int) $pid) : null;
    }
 
    public static function find_by_name(string $name): ?object
    {
        global $wpdb;
        $alias = self::normalize_name_alias($name);
        if ($alias === '') {
            return null;
        }
        $aliases = self::get_aliases_table();
        $pid = $wpdb->get_var($wpdb->prepare("SELECT player_id FROM $aliases WHERE alias_type = %s AND alias_value = %s", 'name', $alias));
        return $pid ? self::get_by_id((int) $pid) : null;
    }

    /**
     * Find an existing unified profile for a tournament roster row (no auto-create).
     */
    public static function find_for_tournament_player(object $tournamentPlayer): ?object
    {
        $fid = self::normalize_fide_id_alias((string) ($tournamentPlayer->fide_id ?? ''));
        if ($fid !== '') {
            $byF = self::find_by_fide_id($fid);
            if ($byF) {
                return $byF;
            }
        }
        return self::find_by_name((string) ($tournamentPlayer->name ?? ''));
    }
 
    /**
     * Resolve (or create) a unified profile for a tournament/GP player identity.
     * Returns the unified player row.
     */
    public static function resolve_or_create(string $name, string $fideId = '', string $country = ''): ?object
    {
        $name = sanitize_text_field($name);
        $fideId = self::normalize_fide_id_alias($fideId);
        $country = ChessPodiumPlugin::sanitize_country_code($country);
 
        if ($fideId !== '') {
            $existing = self::find_by_fide_id($fideId);
            if ($existing) {
                self::ensure_aliases((int) $existing->id, $name, $fideId);
                return $existing;
            }
        }
        $byName = self::find_by_name($name);
        if ($byName) {
            self::ensure_aliases((int) $byName->id, $name, $fideId);
            return $byName;
        }
 
        // Create new.
        $slugBase = self::sanitize_slug($name);
        if ($slugBase === '') {
            return null;
        }
        $slug = self::unique_slug($slugBase);
        global $wpdb;
        $table = self::get_players_table();
        $now = current_time('mysql');
        $wpdb->insert($table, [
            'slug' => $slug,
            'display_name' => $name,
            'fide_id' => $fideId !== '' ? $fideId : null,
            'country' => $country !== '' ? $country : null,
            'bio' => null,
            'avatar_attachment_id' => null,
            'chesscom_username' => null,
            'lichess_username' => null,
            'wp_user_id' => null,
            'created_at' => $now,
            'updated_at' => $now,
        ], ['%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%d', '%s', '%s']);
        $id = (int) $wpdb->insert_id;
        if ($id <= 0) {
            return null;
        }
        self::ensure_aliases($id, $name, $fideId);
        return self::get_by_id($id);
    }
 
    private static function unique_slug(string $base): string
    {
        global $wpdb;
        $table = self::get_players_table();
        $slug = $base;
        for ($i = 0; $i < 200; $i++) {
            $exists = (int) $wpdb->get_var($wpdb->prepare("SELECT 1 FROM $table WHERE slug = %s", $slug));
            if (!$exists) {
                return $slug;
            }
            $slug = $base . '-' . ($i + 2);
        }
        return $base . '-' . time();
    }
 
    private static function ensure_aliases(int $playerId, string $name, string $fideId): void
    {
        global $wpdb;
        $aliases = self::get_aliases_table();
        $now = current_time('mysql');
        $nameAlias = self::normalize_name_alias($name);
        if ($nameAlias !== '') {
            $wpdb->query($wpdb->prepare(
                "INSERT IGNORE INTO $aliases (player_id, alias_type, alias_value, created_at) VALUES (%d, %s, %s, %s)",
                $playerId,
                'name',
                $nameAlias,
                $now
            ));
        }
        $fid = self::normalize_fide_id_alias($fideId);
        if ($fid !== '') {
            $wpdb->query($wpdb->prepare(
                "INSERT IGNORE INTO $aliases (player_id, alias_type, alias_value, created_at) VALUES (%d, %s, %s, %s)",
                $playerId,
                'fide_id',
                $fid,
                $now
            ));
        }
    }
 
    public static function get_unified_id_for_gp_key(int $grandPrixId, string $playerKey): int
    {
        global $wpdb;
        $gpMap = self::get_gp_map_table();
        $pid = $wpdb->get_var($wpdb->prepare(
            "SELECT player_id FROM $gpMap WHERE grand_prix_id = %d AND player_key = %s",
            $grandPrixId,
            $playerKey
        ));
        return $pid ? (int) $pid : 0;
    }
 
    public static function map_gp_key_to_player(int $grandPrixId, string $playerKey, int $playerId): void
    {
        global $wpdb;
        $gpMap = self::get_gp_map_table();
        $now = current_time('mysql');
        $wpdb->query($wpdb->prepare(
            "INSERT INTO $gpMap (grand_prix_id, player_key, player_id, created_at)
             VALUES (%d, %s, %d, %s)
             ON DUPLICATE KEY UPDATE player_id = VALUES(player_id)",
            $grandPrixId,
            $playerKey,
            $playerId,
            $now
        ));
    }
}

