<?php
/**
 * Excel tournament import (Chess-Results / Aeroflot format).
 * Creates a tournament from .xlsx file with columns: Nr., Nome, FideID, paese, EloI, Title.
 * Supports chess-results "Elenco giocatori per sorteggio" (starting list) Excel export.
 * Nr (start number) is parsed flexibly: "1", "1.", " 1 " all accepted for pairing-id consistency.
 *
 * @package Chess_Podium
 */

if (!defined('ABSPATH')) {
    exit;
}

final class ChessPodium_ExcelImport
{
    private const FIDE_TITLES = ['GM', 'IM', 'FM', 'CM', 'WGM', 'WIM', 'WFM', 'WCM'];

    /**
     * Parse Excel file and return tournament data.
     *
     * @param string $filePath Full path to .xlsx file.
     * @return array{name: string, rounds: int, players: array<array{name: string, rating: int, fide_id: ?string, country: ?string, title: ?string, start_number: ?int}>}|null
     */
    public static function parse(string $filePath): ?array
    {
        $rows = ChessPodium_XlsxReader::read($filePath);
        if ($rows === false || empty($rows)) {
            return null;
        }

        $tournamentName = self::extract_tournament_name($rows);
        $colMap = self::find_header_row($rows);
        if ($colMap === null) {
            return null;
        }

        $players = [];
        $headerRowIdx = $colMap['header_row'];
        $dataRows = array_slice($rows, $headerRowIdx + 1);
        $isPairingFormat = isset($colMap['white_col'], $colMap['black_col']);
        $seenNames = [];

        $addPlayer = static function (string $name, int $rating, $fideId, $country, $title, ?int $startNumber) use (&$players, &$seenNames) {
            $name = trim($name);
            if ($name === '' || is_numeric($name)) {
                return;
            }
            if (in_array(strtolower($name), ['nome', 'name', 'player', 'nr.', 'nr', 'white', 'black', 'bye', '-'], true)) {
                return;
            }
            $key = strtolower($name);
            if (isset($seenNames[$key])) {
                return;
            }
            $seenNames[$key] = true;
            $players[] = [
                'name' => $name,
                'rating' => $rating,
                'fide_id' => $fideId,
                'country' => $country,
                'title' => $title,
                'start_number' => $startNumber ?? count($players) + 1,
            ];
        };

        if ($isPairingFormat) {
            $whiteCol = $colMap['white_col'];
            $blackCol = $colMap['black_col'];
            $ratingCol = $colMap['rating'] ?? null;
            $headerRow = $rows[$headerRowIdx];
            if ($ratingCol === null) {
                foreach ($headerRow as $c => $val) {
                    $h = strtolower(trim((string) $val));
                    if (in_array($h, ['elo', 'eloi', 'rtg', 'rating'], true)) {
                        $ratingCol = $c;
                        break;
                    }
                }
            }
            $rowIdx = 0;
            foreach ($dataRows as $row) {
                $rowIdx++;
                $whiteName = self::extract_player_name_from_cell($row, $whiteCol);
                $blackName = self::extract_player_name_from_cell($row, $blackCol);
                $rating = 0;
                if ($ratingCol !== null && isset($row[$ratingCol])) {
                    $rating = max(0, min(3500, (int) $row[$ratingCol]));
                }
                $addPlayer($whiteName, $rating, null, null, null, $rowIdx * 2 - 1);
                $addPlayer($blackName, $rating, null, null, null, $rowIdx * 2);
            }
        } else {
            $rowIdx = 0;
            foreach ($dataRows as $row) {
                $rowIdx++;
                $name = trim((string) ($row[$colMap['name']] ?? ''));
                $titleFromName = null;
                foreach (self::FIDE_TITLES as $t) {
                    if (preg_match('/^' . preg_quote($t, '/') . '\s+/u', $name)) {
                        $titleFromName = $t;
                        $name = trim(preg_replace('/^' . preg_quote($t, '/') . '\s+/iu', '', $name));
                        break;
                    }
                }
                if ($name === '' || is_numeric($name)) {
                    continue;
                }
                if (in_array(strtolower($name), ['nome', 'name', 'player', 'nr.', 'nr'], true)) {
                    continue;
                }

                $startNumber = null;
                if (isset($colMap['nr']) && $colMap['nr'] !== null) {
                    $nr = trim((string) ($row[$colMap['nr']] ?? ''));
                    $nrDigits = preg_replace('/\D/', '', $nr);
                    if ($nrDigits !== '' && (int) $nrDigits <= 9999) {
                        $startNumber = (int) $nrDigits;
                    }
                }
                if ($startNumber === null) {
                    $startNumber = $rowIdx;
                }

                $rating = 0;
                if (isset($colMap['rating']) && $colMap['rating'] !== null) {
                    $r = $row[$colMap['rating']] ?? 0;
                    $rating = max(0, min(3500, (int) $r));
                }

                $fideId = null;
                if (isset($colMap['fide_id']) && $colMap['fide_id'] !== null) {
                    $raw = trim((string) ($row[$colMap['fide_id']] ?? ''));
                    if ($raw !== '' && ctype_digit($raw)) {
                        $fideId = $raw;
                    }
                }

                $country = null;
                if (isset($colMap['country']) && $colMap['country'] !== null) {
                    $raw = trim((string) ($row[$colMap['country']] ?? ''));
                    if (strlen($raw) === 3 && ctype_upper($raw)) {
                        $country = $raw;
                    }
                }

                $title = $titleFromName;
                if ($title === null && isset($colMap['title']) && $colMap['title'] !== null) {
                    $raw = trim((string) ($row[$colMap['title']] ?? ''));
                    if (in_array($raw, self::FIDE_TITLES, true)) {
                        $title = $raw;
                    }
                }

                $players[] = [
                    'name' => $name,
                    'rating' => $rating,
                    'fide_id' => $fideId,
                    'country' => $country,
                    'title' => $title,
                    'start_number' => $startNumber,
                ];
            }
        }

        if (empty($players)) {
            return null;
        }

        $rounds = self::estimate_rounds(count($players));
        if ($tournamentName === '') {
            $tournamentName = pathinfo($filePath, PATHINFO_FILENAME);
        }

        return [
            'name' => $tournamentName,
            'rounds' => $rounds,
            'players' => $players,
        ];
    }

    private static function extract_tournament_name(array $rows): string
    {
        $skip = ['tratto', 'chess-results', 'aggiornato', 'elenco', 'sorteggio', 'http'];
        foreach (array_slice($rows, 0, 6) as $row) {
            $val = trim((string) ($row[0] ?? ''));
            if ($val === '' || strlen($val) < 4 || preg_match('/^\d+$/', $val)) {
                continue;
            }
            if (preg_match('/^https?:\/\//', $val)) {
                continue;
            }
            $lower = strtolower($val);
            $isSkip = false;
            foreach ($skip as $s) {
                if (strpos($lower, $s) !== false) {
                    $isSkip = true;
                    break;
                }
            }
            if (!$isSkip) {
                return $val;
            }
        }
        return '';
    }

    /**
     * Find header row and map columns.
     * Supports: (1) Player list: Nr., Nome, FideID, paese, EloI, Title
     * (2) Pairing format: Sc., Nr., White, Elo, Pts., Risultato, Black, Elo, Nr. – extracts players from White+Black.
     *
     * @return array{header_row: int, name: int, nr: ?int, rating: ?int, fide_id: ?int, country: ?int, title: ?int, white_col?: int, black_col?: int}|null
     */
    private static function find_header_row(array $rows): ?array
    {
        $nameKeys = ['nome', 'name', 'giocatore', 'player', 'spieler', 'joueur', 'name1', 'nominativo'];
        $nrKeys = ['nr', 'nr.', 'no', 'no.', 'sc.nr', 'scnr', 'sc.', 'startnr', 'start nr'];
        $ratingKeys = ['eloi', 'elo', 'rtg', 'rating', 'eloi', 'elo1', 'elo2'];
        $fideKeys = ['fideid', 'fide id', 'fide']; // exclude 'id' - 'Ident' is national fed ID, not FIDE
        $countryKeys = ['paese', 'fed', 'country', 'nazione', 'land', 'pays'];
        $titleKeys = ['title', 'titolo']; // 'sex' is gender m/w, not FIDE title
        $whiteKeys = ['white', 'bianco', 'blanco', 'weiss', 'blanc'];
        $blackKeys = ['black', 'nero', 'schwarz', 'negro', 'noir'];

        for ($i = 0; $i < min(25, count($rows)); $i++) {
            $row = $rows[$i];
            $headers = array_map(static function ($v) {
                return strtolower(trim((string) $v));
            }, $row);

            $nameCol = null;
            $nrCol = null;
            $ratingCol = null;
            $fideCol = null;
            $countryCol = null;
            $titleCol = null;
            $whiteCol = null;
            $blackCol = null;

            foreach ($headers as $col => $h) {
                if ($h === '') {
                    continue;
                }
                if ($nameCol === null && in_array($h, $nameKeys, true)) {
                    $nameCol = $col;
                }
                if ($nrCol === null && in_array($h, $nrKeys, true)) {
                    $nrCol = $col;
                }
                if ($ratingCol === null && in_array($h, $ratingKeys, true)) {
                    $ratingCol = $col;
                }
                if ($fideCol === null && in_array($h, $fideKeys, true)) {
                    $fideCol = $col;
                }
                if ($countryCol === null && in_array($h, $countryKeys, true)) {
                    $countryCol = $col;
                }
                if ($titleCol === null && in_array($h, $titleKeys, true)) {
                    $titleCol = $col;
                }
                if ($whiteCol === null && in_array($h, $whiteKeys, true)) {
                    $whiteCol = $col;
                }
                if ($blackCol === null && in_array($h, $blackKeys, true)) {
                    $blackCol = $col;
                }
            }

            if ($nameCol !== null) {
                if ($titleCol === null && $nameCol > 0) {
                    $titleCol = $nameCol - 1;
                }
                return [
                    'header_row' => $i,
                    'name' => $nameCol,
                    'nr' => $nrCol,
                    'rating' => $ratingCol,
                    'fide_id' => $fideCol,
                    'country' => $countryCol,
                    'title' => $titleCol,
                ];
            }

            if ($whiteCol !== null && $blackCol !== null) {
                return [
                    'header_row' => $i,
                    'name' => $whiteCol,
                    'nr' => $nrCol,
                    'rating' => $ratingCol,
                    'fide_id' => $fideCol,
                    'country' => $countryCol,
                    'title' => $titleCol,
                    'white_col' => $whiteCol,
                    'black_col' => $blackCol,
                ];
            }
        }
        return null;
    }

    /**
     * Extract player name from row. Chess-results pairing format may have title in col, name in col+1,
     * or "GM Name" in a single cell.
     *
     * @param array $row
     * @param int $col
     * @return string
     */
    private static function extract_player_name_from_cell(array $row, int $col): string
    {
        $val = trim((string) ($row[$col] ?? ''));
        $next = trim((string) ($row[$col + 1] ?? ''));

        if ($val === '' || stripos($val, 'bye') !== false || $val === '-') {
            if ($next !== '' && !ctype_digit($next) && strlen($next) > 2) {
                return $next;
            }
            return '';
        }
        if (in_array(strtoupper($val), self::FIDE_TITLES, true) && $next !== '' && !ctype_digit($next)) {
            return $next;
        }
        foreach (self::FIDE_TITLES as $t) {
            if (stripos($val, $t . ' ') === 0 || preg_match('/^' . preg_quote($t, '/') . '\s+/u', $val)) {
                $val = trim(preg_replace('/^' . preg_quote($t, '/') . '\s+/iu', '', $val));
                break;
            }
        }
        return $val;
    }

    private static function estimate_rounds(int $playerCount): int
    {
        if ($playerCount <= 0) {
            return 5;
        }
        $n = (int) ceil(log($playerCount, 2));
        return max(5, min(15, $n + 1));
    }

    /**
     * List .xlsx files in the chess-podium plugin directory.
     *
     * @return array<string> Full paths to XLSX files.
     */
    public static function list_excel_in_plugin_dir(): array
    {
        $dir = CHESS_PODIUM_PLUGIN_DIR;
        if (!is_dir($dir)) {
            return [];
        }
        $files = [];
        foreach (glob($dir . '/*.[xX][lL][sS][xX]') ?: [] as $path) {
            if (is_file($path)) {
                $files[] = $path;
            }
        }
        usort($files, static fn ($a, $b) => filemtime($b) <=> filemtime($a));
        return $files;
    }
}
