<?php
/**
 * Mt5ReportParser - the one, canonical MT5 "Trade History Report" parser.
 *
 * Replaces the old ad-hoc HTML sniffer that used to live inline in
 * api/mt5/parse.php. That implementation never accounted for two facts
 * about real MT5 exports, confirmed by parsing Kevin's own real downloaded
 * statements (Maven Trade + HFM, both HTML and XLSX) before writing this:
 *
 *   1. MT5's HTML/XLSX report parts are UTF-16LE with a BOM (FF FE), not
 *      UTF-8/ASCII. Every ASCII-pattern regex the old parser ran against
 *      the raw upload bytes could never match - it silently returned 0
 *      trades and an EMPTY errors array for every real MT5 file, with no
 *      indication anything had gone wrong.
 *   2. The whole report - Positions, Orders, Deals, Results - lives inside
 *      ONE <table> (or one worksheet), as stacked sections each with their
 *      OWN header row and column layout, not one uniform table. The old
 *      parser picked a single "best" header row and misapplied it to every
 *      row that followed, including rows from a differently-shaped section.
 *
 * This parser is format-agnostic (HTML/HTM, real XLSX via a zero-dependency
 * PharData reader - this server has no ext-zip - and CSV) and
 * section-aware: it segments the report into its real MT5 sections by
 * scanning for known section-title rows, and maps each section by its own
 * FIXED, well-documented MT5 column order (verified identical across both
 * real broker exports tested) rather than fuzzy name-guessing across a
 * blended row set.
 *
 * The "Positions" section - one row per closed round-trip position, with
 * MT5's own open+close pairing already done - is the primary trade source:
 * it maps 1:1 onto this app's mt5_position_id-keyed reconciliation model.
 * "Deals" (one row per individual fill, including non-trade balance/credit
 * operations) is kept as secondary evidence - it is NOT used for primary
 * matching (pairing in/out deals into positions is what MT5 already did
 * for us in the Positions section; redoing it here would just be a second,
 * probably worse, independent implementation of the same computation).
 * "Orders" is informational only. "Results" (MT5's own computed summary
 * stats) is parsed and returned so a caller can sanity-check the parse
 * against MT5's own numbers rather than trusting this parser blindly.
 */
class Mt5ReportParser
{
    public const MAX_ROWS = 5000;

    private const SECTION_TITLES = ['positions', 'orders', 'deals', 'results'];

    // MT5's fixed Positions column order (verified identical across both
    // real broker statements this was built against).
    private const POSITIONS_COLS = [
        0 => 'open_time', 1 => 'ticket', 2 => 'symbol', 3 => 'type', 4 => 'volume',
        5 => 'price', 6 => 'sl', 7 => 'tp', 8 => 'close_time', 9 => 'close_price',
        10 => 'commission', 11 => 'swap', 12 => 'profit',
    ];

    // MT5's "Cost" column (present in the raw markup between Order and
    // Commission) is always marked class="hidden" by MT5 itself - in both
    // the header row AND every data row, confirmed against real exports,
    // so it never actually occupies a real position once hidden cells are
    // stripped (see rowsFromHtmlFragment()'s own comment on why they must
    // be stripped at all). There is genuinely no "cost" column to map.
    private const DEALS_COLS = [
        0 => 'time', 1 => 'deal_id', 2 => 'symbol', 3 => 'type', 4 => 'direction',
        5 => 'volume', 6 => 'price', 7 => 'order_id', 8 => 'commission',
        9 => 'fee', 10 => 'swap', 11 => 'profit', 12 => 'balance', 13 => 'comment',
    ];

    private const ORDERS_COLS = [
        0 => 'open_time', 1 => 'order_id', 2 => 'symbol', 3 => 'type', 4 => 'volume',
        5 => 'price', 6 => 'sl', 7 => 'tp', 8 => 'time', 9 => 'state', 10 => 'comment',
    ];

    /**
     * @param string $rawContent Raw uploaded file bytes, untouched.
     * @param string $extension  'html'|'htm'|'xlsx'|'csv'
     */
    public static function parse(string $rawContent, string $extension): array
    {
        $extension = strtolower($extension);
        $warnings = [];

        try {
            if ($extension === 'xlsx') {
                $grid = self::gridFromXlsx($rawContent);
            } elseif ($extension === 'csv') {
                $text = self::normalizeTextEncoding($rawContent);
                $grid = self::gridFromCsv($text);
            } else { // html / htm
                $text = self::normalizeTextEncoding($rawContent);
                $grid = self::gridFromHtml($text);
            }
        } catch (\Throwable $e) {
            return self::fail('Could not read this file as an MT5 report: ' . $e->getMessage());
        }

        if (empty($grid)) {
            return self::fail('This file does not contain any readable rows.');
        }

        $sections = self::segmentSections($grid);

        $account = self::parseAccountHeader($sections['preamble']);

        // ── Genuine-MT5-report gate ─────────────────────────────────────
        // Must run BEFORE any of the heavier row mapping below, and before
        // api/mt5/parse.php ever saves the file to disk or writes a
        // broker_reconciliations row - see looksLikeGenuineMt5Report()'s
        // own docblock for why this exists and the real, already-uploaded
        // non-MT5 file it was found against.
        if (!self::looksLikeGenuineMt5Report($grid, $account, $sections, $text ?? null)) {
            return self::fail(
                "This file doesn't look like a genuine MT5 Trade History Report - it's missing every real "
                . "MT5 report marker (account/login header, \"Trade History Report\" title, or a Positions/"
                . "Orders/Deals/Results section). Export it from MT5 itself: open the Toolbox's History tab, "
                . 'right-click anywhere in the list, choose "Report", then "HTML" or "Excel" - and upload that '
                . 'file, not a report from another platform or an unrelated file.'
            );
        }

        $positions = self::mapSectionRows($sections['positions'], self::POSITIONS_COLS);
        $deals = self::mapSectionRows($sections['deals'], self::DEALS_COLS);
        $orders = self::mapSectionRows($sections['orders'], self::ORDERS_COLS);

        // The Positions section has no Comment column of its own - but if
        // a trader pasted TraderAlign's own "TraderAlign-<hash>" comment
        // into the order when placing the trade, MT5 keeps it against the
        // entry DEAL, not the position. Confirmed against a real statement
        // that a position's own ticket equals its entry ("in") deal's
        // Order id, so this thread-through makes the comment reachable
        // from the position row api/mt5/reconcile.php's Path A (comment-
        // hash) matching actually looks at.
        $entryComments = [];
        foreach ($deals as $d) {
            if (($d['direction'] ?? null) === 'in' && !empty($d['comment']) && !empty($d['order_id'])) {
                $entryComments[$d['order_id']] = $d['comment'];
            }
        }
        foreach ($positions as &$p) {
            if (isset($entryComments[$p['ticket']])) {
                $p['comment'] = $entryComments[$p['ticket']];
            }
        }
        unset($p);
        $reportSummary = self::parseResultsBlock($sections['results']);

        // Positions rows are the primary trade candidates. Cap at MAX_ROWS
        // - a statement legitimately exceeding this is almost certainly a
        // multi-year full-history export; the trader should narrow the
        // date range in MT5 and re-export, not silently lose trades.
        if (count($positions) > self::MAX_ROWS) {
            $warnings[] = 'This statement has ' . count($positions) . ' closed positions - only the first '
                . self::MAX_ROWS . ' were processed. Narrow the date range in MT5 and re-export if you need the rest.';
            $positions = array_slice($positions, 0, self::MAX_ROWS);
        }

        $balanceEvents = array_values(array_filter($deals, function ($d) {
            $type = strtolower((string)($d['type'] ?? ''));
            return $type === 'balance' || $type === 'credit';
        }));
        $tradeDeals = array_values(array_filter($deals, function ($d) {
            $type = strtolower((string)($d['type'] ?? ''));
            return $type !== 'balance' && $type !== 'credit';
        }));

        if (empty($positions) && empty($sections['positions']) && $sections['found_positions_title'] === false) {
            $warnings[] = 'No "Positions" section was found in this report - it may not be a standard MT5 Trade History export.';
        }

        // Self-consistency check against MT5's own Results summary - this
        // parser should never silently disagree with MT5's own arithmetic.
        // Confirmed against a real statement: Positions' own "Profit"
        // column is the raw price P&L only - the position's commission
        // (and swap) is reported as a SEPARATE field on the same row, not
        // pre-netted into "Profit". MT5's own "Total Net Profit" is
        // profit + commission + swap summed across every position, so
        // that's what this compares against - comparing raw profit alone
        // would flag every real, correctly-parsed statement as suspect.
        if (isset($reportSummary['total_net_profit']) && !empty($positions)) {
            $ourSum = array_sum(array_map(
                fn($p) => (float)($p['profit'] ?? 0) + (float)($p['commission'] ?? 0) + (float)($p['swap'] ?? 0),
                $positions
            ));
            $theirSum = (float)$reportSummary['total_net_profit'];
            if (abs($ourSum - $theirSum) > max(0.5, abs($theirSum) * 0.01)) {
                $warnings[] = sprintf(
                    'Heads up: the closed positions parsed from this report sum to %s, but MT5\'s own "Total Net Profit" says %s. Double-check this statement before trusting the reconciliation.',
                    number_format($ourSum, 2),
                    number_format($theirSum, 2)
                );
            }
        }

        return [
            'success' => true,
            'error' => null,
            'format' => $extension,
            'account' => $account,
            'positions' => $positions,
            'deals' => $tradeDeals,
            'balance_events' => $balanceEvents,
            'orders' => $orders,
            'report_summary' => $reportSummary,
            'warnings' => $warnings,
            'is_empty_statement' => empty($positions) && empty($tradeDeals) && empty($orders),
        ];
    }

    /**
     * The one real account-identity check both api/mt5/parse.php (first,
     * fast opinion at upload time) and api/mt5/reconcile.php (the real,
     * server-side gate, re-derived independently from the saved file
     * rather than trusted from the client) both call.
     *
     * Login + Server is the real compound identifier, per explicit
     * direction: a login number is only guaranteed unique within one
     * broker server's namespace, not globally, so the server name is the
     * second half of the real key, not an optional nicety. The password
     * is never part of identification at all, it's authentication only
     * and this app never uses it for anything besides an optional,
     * encrypted-at-rest personal reference field.
     *
     * Found live, from a real upload: a genuine mismatch (a prop firm
     * challenge account with no saved broker login, statement from a
     * completely unrelated FBS demo account) was reported as
     * "matches: true" by an earlier version of this check, because it
     * fell back to comparing currency alone whenever no login was on
     * file, and two accounts sharing "USD" proves nothing. A currency
     * match is not evidence of anything and must never be reported as a
     * real match. A later version then let a wrong account selection get
     * silently upgraded to a permanent "verified" match via a one-click
     * "save this number" button during the upload flow itself, an
     * equally real flaw for the same reason: confirming identity must
     * happen deliberately, with the account's real context visible, not
     * as a rushed side effect of an unrelated action.
     *
     * Three real outcomes only:
     *   - 'verified_match'    the TraderAlign account has a saved MT5
     *                         login that equals the statement's own
     *                         login exactly, AND, when this account also
     *                         has a saved broker server, that server
     *                         matches too (compared loosely, stripped of
     *                         punctuation/case, since "MavenTrade-Server"
     *                         and "MavenTradeServer" are the same real
     *                         server typed two different ways, not a real
     *                         mismatch). A saved login with no saved
     *                         server is still treated as a genuine match
     *                         on its own, login-namespace collisions
     *                         between different brokers are not a
     *                         realistic real-world risk to model here.
     *   - 'verified_mismatch' the TraderAlign account has a saved login
     *                         and it does NOT equal the statement's, OR
     *                         the login matches but a saved server
     *                         clearly does not. Either one hard-blocks
     *                         reconciliation, no override.
     *   - 'unverified'        the TraderAlign account has no saved login
     *                         at all, so there is genuinely nothing to
     *                         compare against. Honestly reported as "we
     *                         can't tell", never quietly treated as a
     *                         match, and (per the fix above) never
     *                         resolvable from this page either, it now
     *                         also hard-blocks reconciliation until the
     *                         trader confirms the account's real login
     *                         deliberately, elsewhere.
     *
     * The identity used for matching is Login (`broker_login`) first,
     * then Account Number (`account_number`) if Login is empty. Both
     * fields exist on the account-details editor, and traders naturally
     * put the MT5 login (the number printed on the statement) into
     * Login. An earlier version of this check read only
     * `account_number`, so filling Login left the upload "unverified",
     * and a leftover email in Account Number (a real live case) hard-
     * blocked every genuine statement even after the correct login was
     * saved.
     */
    public static function matchAccountIdentity(array $statementAccount, ?string $savedAccountNumber, ?string $savedBrokerServer = null, ?string $savedBrokerLogin = null): array
    {
        $stmtNum = $statementAccount['number'] ?? null;
        $stmtNum = $stmtNum !== null ? trim((string)$stmtNum) : '';
        $stmtServer = $statementAccount['server'] ?? null;
        $stmtServer = $stmtServer !== null ? trim((string)$stmtServer) : '';

        $savedAccountNumber = $savedAccountNumber !== null ? trim((string)$savedAccountNumber) : '';
        $savedBrokerServer = $savedBrokerServer !== null ? trim((string)$savedBrokerServer) : '';
        $savedBrokerLogin = $savedBrokerLogin !== null ? trim((string)$savedBrokerLogin) : '';

        $candidates = [];
        foreach ([$savedBrokerLogin, $savedAccountNumber] as $value) {
            if ($value !== '' && !in_array($value, $candidates, true)) {
                $candidates[] = $value;
            }
        }
        $savedDisplay = $savedBrokerLogin !== '' ? $savedBrokerLogin : $savedAccountNumber;

        $result = [
            'statement_account_number' => $stmtNum !== '' ? $stmtNum : null,
            'statement_server' => $stmtServer !== '' ? $stmtServer : null,
            'saved_account_number' => $savedDisplay !== '' ? $savedDisplay : null,
            'saved_broker_login' => $savedBrokerLogin !== '' ? $savedBrokerLogin : null,
            'saved_server' => $savedBrokerServer !== '' ? $savedBrokerServer : null,
        ];

        if ($candidates === [] || $stmtNum === '') {
            $result['status'] = 'unverified';
            return $result;
        }

        $matchedIdentifier = null;
        foreach ($candidates as $candidate) {
            if (self::loginIdentifiersEqual($stmtNum, $candidate)) {
                $matchedIdentifier = $candidate;
                break;
            }
        }

        if ($matchedIdentifier === null) {
            $result['status'] = 'verified_mismatch';
            $result['reason'] = 'login';
            return $result;
        }

        $result['saved_account_number'] = $matchedIdentifier;

        if ($savedBrokerServer !== '' && $stmtServer !== '') {
            $normalize = fn(string $s) => strtolower(preg_replace('/[^a-z0-9]/i', '', $s));
            if ($normalize($savedBrokerServer) !== $normalize($stmtServer)) {
                $result['status'] = 'verified_mismatch';
                $result['reason'] = 'server';
                return $result;
            }
        }

        $result['status'] = 'verified_match';
        return $result;
    }

    /**
     * MT5 statement logins are numeric and must match exactly. Other
     * identifiers (rare alphanumeric logins) compare case-insensitively
     * so a typed email/login is not rejected only for casing.
     */
    private static function loginIdentifiersEqual(string $statementLogin, string $savedIdentifier): bool
    {
        if ($statementLogin === $savedIdentifier) {
            return true;
        }
        return strcasecmp($statementLogin, $savedIdentifier) === 0;
    }

    private static function fail(string $msg): array
    {
        return [
            'success' => false, 'error' => $msg, 'format' => null, 'account' => [],
            'positions' => [], 'deals' => [], 'balance_events' => [], 'orders' => [],
            'report_summary' => [], 'warnings' => [], 'is_empty_statement' => false,
        ];
    }

    /**
     * Real, structural "is this actually a genuine MT5 Trade History
     * Report" gate - independent from, and stricter than, the old
     * "no Positions section" logic further down, which only ever produced
     * a soft warning string while still letting the file all the way
     * through to be saved to disk and written into broker_reconciliations.
     *
     * Added 2026-08-20 after finding two REAL, already-uploaded files
     * sitting in this exact pipeline's own data directory that are not
     * MT5 exports at all - a TradingView "Strategy Tester" CSV
     * ("Trade #,Type,Date and time,Signal,Price USD,Size (qty),...") that
     * had been silently accepted with 0 parsed rows, a soft warning, a
     * real broker_reconciliations row written to the database, and the
     * raw file saved under data/mt5-uploads/<user>/ - confirmed live via
     * direct query before writing this fix. This closes that gap: any
     * file passing the extension/MIME allow-list in api/mt5/parse.php
     * that isn't ALSO a genuine MT5 report is now rejected outright,
     * before it is ever parsed into trade rows, saved, or persisted -
     * matching content of any kind, including unrelated or inappropriate
     * material dressed up as a spreadsheet, must never be silently
     * accepted just because the container format (.html/.csv/.xlsx) is
     * well-formed.
     *
     * Any ONE of these real, MT5-specific fingerprints is required:
     *   1. MT5's own <meta name="generator" content="client terminal">
     *      tag, hardcoded into every HTML report the terminal itself
     *      generates - not something an unrelated export would contain.
     *   2. The literal "Trade History Report" title MT5 writes at the top
     *      of every real HTML/XLSX export (confirmed verbatim, in both
     *      real broker statements this parser was built against).
     *   3. The compound "NUMBER (CURRENCY, SERVER, mode, ...)" account
     *      header pattern - parseAccountHeader() only ever produces both
     *      a number AND a server from MT5's own idiosyncratic "Account:"
     *      row format, never from a generic trade-log export.
     *   4. A literal, single-cell "Positions"/"Orders"/"Deals"/"Results"
     *      section-title row - MT5's own report structure.
     *
     * A file with none of these is rejected. A genuinely empty MT5
     * statement (a real account with zero trades in the exported range)
     * still always carries signal 1, 2, or 3 - it's the file's own
     * masthead, unrelated to whether any trades happened - so this never
     * blocks a real, legitimately-empty statement.
     */
    private static function looksLikeGenuineMt5Report(array $grid, array $account, array $sections, ?string $rawText): bool
    {
        if ($rawText !== null
            && stripos($rawText, 'generator') !== false
            && stripos($rawText, 'client terminal') !== false
        ) {
            return true;
        }

        // Flatten the parsed grid (title/header rows are always near the
        // top, but this scans the whole thing rather than assuming a
        // fixed row count, since XLSX/CSV variants can shift things by a
        // row or two) into one search string - covers the report title in
        // every format without needing per-format raw-text handling.
        $flat = '';
        foreach ($grid as $row) {
            foreach ($row as $cell) {
                $flat .= ' ' . $cell;
            }
        }
        if (stripos($flat, 'Trade History Report') !== false) return true;

        if (!empty($account['number']) && !empty($account['server'])) return true;

        return $sections['found_any_section_title'] ?? false;
    }

    // ── Encoding ──────────────────────────────────────────────────────

    /** Converts a raw MT5 text export to plain UTF-8, handling the two real
     * encodings observed: UTF-16LE with a BOM (the actual MT5 default) and
     * UTF-8 (with or without a BOM), so the rest of the pipeline can use
     * ordinary ASCII-pattern regexes safely. */
    private static function normalizeTextEncoding(string $raw): string
    {
        if (substr($raw, 0, 2) === "\xFF\xFE") {
            return mb_convert_encoding(substr($raw, 2), 'UTF-8', 'UTF-16LE');
        }
        if (substr($raw, 0, 2) === "\xFE\xFF") {
            return mb_convert_encoding(substr($raw, 2), 'UTF-8', 'UTF-16BE');
        }
        if (substr($raw, 0, 3) === "\xEF\xBB\xBF") {
            return substr($raw, 3);
        }
        return $raw;
    }

    // ── HTML → grid ───────────────────────────────────────────────────

    private static function gridFromHtml(string $html): array
    {
        $html = preg_replace('/<script[^>]*>.*?<\/script>/is', '', $html);
        $html = preg_replace('/<style[^>]*>.*?<\/style>/is', '', $html);
        $html = preg_replace('/<!--.*?-->/s', '', $html);
        $html = str_replace('&nbsp;', ' ', $html);

        // The whole report is one <table> in every real MT5 export seen,
        // but fall back to scanning the raw body for <tr> if a broker's
        // variant doesn't wrap it in <table> at all.
        preg_match_all('/<table[^>]*>(.*?)<\/table>/is', $html, $tableMatches);
        $rows = [];
        if (!empty($tableMatches[1])) {
            foreach ($tableMatches[1] as $tableHtml) {
                $rows = array_merge($rows, self::rowsFromHtmlFragment($tableHtml));
            }
        } else {
            $rows = self::rowsFromHtmlFragment($html);
        }
        return $rows;
    }

    private static function rowsFromHtmlFragment(string $html): array
    {
        $rows = [];
        preg_match_all('/<tr[^>]*>(.*?)<\/tr>/is', $html, $matches);
        foreach ($matches[1] as $rowHtml) {
            $cells = [];
            // MT5's own Positions data rows (not the header row above them)
            // carry a single class="hidden" <td> that its own CSS
            // (.hidden{display:none}) deliberately never renders - a
            // real, confirmed quirk of the export, not a broken row. Left
            // in, it silently shifts every following cell one column to
            // the right relative to the header row it's supposed to align
            // with, corrupting volume/price/SL/TP/commission/swap/profit.
            preg_match_all('/<t[dh]\b[^>]*>(.*?)<\/t[dh]>/is', $rowHtml, $tagMatches, PREG_SET_ORDER);
            $visibleCellsHtml = [];
            foreach ($tagMatches as $tag) {
                if (preg_match('/class\s*=\s*["\']hidden["\']/i', $tag[0])) continue;
                $visibleCellsHtml[] = $tag[1];
            }
            foreach ($visibleCellsHtml as $cellHtml) {
                $cellHtml = preg_replace('/<table[^>]*>.*?<\/table>/is', '', $cellHtml);
                $cellHtml = preg_replace('/<img[^>]*>/i', '', $cellHtml);
                $cellHtml = preg_replace('/<br\s*\/?>/i', ' ', $cellHtml);
                $val = trim(strip_tags($cellHtml));
                $val = html_entity_decode($val, ENT_QUOTES | ENT_HTML5, 'UTF-8');
                $val = preg_replace('/\s+/', ' ', $val);
                $cells[] = $val;
            }
            $rows[] = $cells;
        }
        return $rows;
    }

    // ── CSV → grid ────────────────────────────────────────────────────

    private static function gridFromCsv(string $content): array
    {
        $content = str_replace(["\r\n", "\r"], "\n", $content);
        $lines = array_filter(explode("\n", $content), fn($l) => trim($l) !== '');

        $grid = [];
        foreach ($lines as $line) {
            $delims = [
                ',' => substr_count($line, ','),
                ';' => substr_count($line, ';'),
                "\t" => substr_count($line, "\t"),
            ];
            arsort($delims);
            $delim = key($delims) ?: ',';
            $grid[] = array_map('trim', str_getcsv($line, $delim));
        }
        return $grid;
    }

    // ── XLSX (real OOXML zip) → grid ─────────────────────────────────
    //
    // No ext-zip on this server, so a real .zip/.xlsx is read via PharData
    // (built into every PHP install, no extension required - verified
    // against Kevin's real Maven/HFM .xlsx exports).

    private static function gridFromXlsx(string $rawContent): array
    {
        if (substr($rawContent, 0, 2) !== 'PK') {
            throw new \RuntimeException('This does not look like a real .xlsx file.');
        }

        $tmpFile = tempnam(sys_get_temp_dir(), 'mt5xlsx_') . '.xlsx';
        file_put_contents($tmpFile, $rawContent);

        try {
            $phar = new \PharData($tmpFile);

            $sharedStrings = [];
            if (isset($phar['xl/sharedStrings.xml'])) {
                $ssRaw = $phar['xl/sharedStrings.xml']->getContent();
                $ssXml = self::normalizeTextEncoding($ssRaw);
                $sharedStrings = self::parseSharedStrings($ssXml);
            }

            $sheetPath = 'xl/worksheets/sheet1.xml';
            if (!isset($phar[$sheetPath])) {
                // Fall back: take whichever worksheet is first.
                $sheetPath = null;
                foreach (new \RecursiveIteratorIterator(new \RecursiveDirectoryIterator("phar://{$tmpFile}/xl/worksheets")) as $f) {
                    if ($f->isFile() && preg_match('/sheet\d+\.xml$/i', $f->getFilename())) {
                        $sheetPath = 'xl/worksheets/' . $f->getFilename();
                        break;
                    }
                }
                if ($sheetPath === null) {
                    throw new \RuntimeException('No worksheet found inside this .xlsx file.');
                }
            }

            $sheetRaw = $phar[$sheetPath]->getContent();
            $sheetXml = self::normalizeTextEncoding($sheetRaw);

            return self::gridFromSheetXml($sheetXml, $sharedStrings);
        } finally {
            @unlink($tmpFile);
        }
    }

    private static function parseSharedStrings(string $xml): array
    {
        preg_match_all('/<si>(.*?)<\/si>/s', $xml, $m);
        $strings = [];
        foreach ($m[1] as $si) {
            preg_match_all('/<t[^>]*>(.*?)<\/t>/s', $si, $tm);
            $text = implode('', $tm[1]);
            $strings[] = html_entity_decode(strip_tags($text), ENT_QUOTES | ENT_HTML5, 'UTF-8');
        }
        return $strings;
    }

    private static function gridFromSheetXml(string $xml, array $sharedStrings): array
    {
        preg_match_all('/<row[^>]*r="(\d+)"[^>]*>(.*?)<\/row>/s', $xml, $rowMatches, PREG_SET_ORDER);

        $grid = [];
        foreach ($rowMatches as $rowMatch) {
            $rowNum = (int)$rowMatch[1];
            $rowXml = $rowMatch[2];

            // Cells may be self-closed (<c r="B1" s="1"/>, no content) or
            // wrapped (<c r="A1" t="s"><v>0</v></c>). These are two
            // genuinely different shapes - a single lazy "match up to the
            // next </c>" pattern would let a self-closed cell's match
            // swallow a LATER sibling cell's real closing tag, corrupting
            // every cell after it. The alternation below tries the
            // self-closed form first at each position, so it's resolved
            // unambiguously before the open/close form is even attempted.
            preg_match_all('/<c\b([^>]*?)\/>|<c\b([^>]*)>(.*?)<\/c>/s', $rowXml, $cellMatches, PREG_SET_ORDER);

            $rowCells = [];
            $maxCol = -1;
            foreach ($cellMatches as $cm) {
                $selfClosed = substr(rtrim($cm[0]), -2) === '/>';
                $attrs = $selfClosed ? $cm[1] : ($cm[2] ?? '');
                $inner = $selfClosed ? '' : ($cm[3] ?? '');
                if (!preg_match('/r="([A-Z]+)\d+"/', $attrs, $refMatch)) continue;
                $colIdx = self::colLetterToIndex($refMatch[1]);
                $maxCol = max($maxCol, $colIdx);

                $type = null;
                if (preg_match('/\bt="([a-z]+)"/', $attrs, $tMatch)) $type = $tMatch[1];

                $value = '';
                if (preg_match('/<v>(.*?)<\/v>/s', $inner, $vMatch)) {
                    $raw = $vMatch[1];
                    if ($type === 's') {
                        $value = $sharedStrings[(int)$raw] ?? '';
                    } else {
                        $value = $raw;
                    }
                } elseif ($type === 'inlineStr' && preg_match('/<t[^>]*>(.*?)<\/t>/s', $inner, $isMatch)) {
                    $value = $isMatch[1];
                }
                $rowCells[$colIdx] = html_entity_decode((string)$value, ENT_QUOTES | ENT_HTML5, 'UTF-8');
            }

            $dense = [];
            for ($i = 0; $i <= $maxCol; $i++) {
                $dense[] = $rowCells[$i] ?? '';
            }
            $grid[$rowNum] = $dense;
        }

        ksort($grid, SORT_NUMERIC);
        return array_values($grid);
    }

    private static function colLetterToIndex(string $letters): int
    {
        $idx = 0;
        foreach (str_split($letters) as $ch) {
            $idx = $idx * 26 + (ord($ch) - 64);
        }
        return $idx - 1;
    }

    // ── Section segmentation (format-agnostic, operates on a plain grid) ──

    private static function segmentSections(array $grid): array
    {
        $out = [
            'preamble' => [], 'positions' => [], 'orders' => [], 'deals' => [], 'results' => [],
            'found_positions_title' => false, 'found_any_section_title' => false,
        ];

        $current = 'preamble';
        foreach ($grid as $row) {
            $nonEmpty = array_values(array_filter($row, fn($v) => trim((string)$v) !== ''));
            if (count($nonEmpty) === 1) {
                $label = strtolower(trim((string)$nonEmpty[0]));
                if (in_array($label, self::SECTION_TITLES, true)) {
                    $current = $label;
                    $out['found_any_section_title'] = true;
                    if ($label === 'positions') $out['found_positions_title'] = true;
                    continue; // title row itself isn't data
                }
            }
            $out[$current][] = $row;
        }
        return $out;
    }

    // ── Section row → structured trade mapping ───────────────────────

    private static function mapSectionRows(array $rows, array $colMap): array
    {
        $out = [];
        foreach ($rows as $row) {
            $nonEmpty = array_filter($row, fn($v) => trim((string)$v) !== '');
            if (count($nonEmpty) < 3) continue; // header row / stray spacer row

            $first = trim((string)($row[0] ?? ''));
            // Every real data row in Positions/Orders/Deals starts with an
            // MT5 timestamp (YYYY.MM.DD HH:MM:SS). Anything else here is a
            // header row, a "Balance:"/"Equity:" mini-summary line, or a
            // footer - not trade data. This is what correctly lets a
            // section "end" without needing an explicit terminator.
            if (!preg_match('/^\d{4}\.\d{2}\.\d{2}\s+\d{2}:\d{2}/', $first)) continue;

            $mapped = [];
            foreach ($colMap as $idx => $field) {
                $mapped[$field] = isset($row[$idx]) ? trim((string)$row[$idx]) : '';
            }
            $normalized = self::normalizeRow($mapped);
            if ($normalized) $out[] = $normalized;
        }
        return $out;
    }

    private static function normalizeRow(array $r): ?array
    {
        $symbol = strtoupper(preg_replace('/\s+/', '', (string)($r['symbol'] ?? '')));
        $type = strtolower(trim((string)($r['type'] ?? '')));

        $direction = 'long';
        if (preg_match('/sell|short/', $type)) $direction = 'short';

        $out = [
            'ticket' => trim((string)($r['ticket'] ?? $r['deal_id'] ?? $r['order_id'] ?? '')),
            'symbol' => $symbol,
            'type' => in_array($type, ['balance', 'credit'], true) ? $type : $direction,
            'raw_type' => $type,
            'direction' => $r['direction'] ?? null, // deals only: in/out
            'volume' => self::toFloat($r['volume'] ?? '0'),
            'price' => self::toFloat($r['price'] ?? '0'),
            'sl' => isset($r['sl']) && $r['sl'] !== '' ? self::toFloat($r['sl']) : null,
            'tp' => isset($r['tp']) && $r['tp'] !== '' ? self::toFloat($r['tp']) : null,
            'close_price' => isset($r['close_price']) && $r['close_price'] !== '' ? self::toFloat($r['close_price']) : null,
            'profit' => self::toFloat($r['profit'] ?? '0'),
            'commission' => isset($r['commission']) ? self::toFloat($r['commission']) : null,
            'swap' => isset($r['swap']) ? self::toFloat($r['swap']) : null,
            'time' => self::toDate($r['open_time'] ?? $r['time'] ?? '') ?? date('Y-m-d H:i:s'),
            'close_time' => isset($r['close_time']) ? self::toDate($r['close_time']) : null,
            'comment' => !empty($r['comment']) ? trim((string)$r['comment']) : null,
            'order_id' => $r['order_id'] ?? null,
            'balance_after' => isset($r['balance']) && $r['balance'] !== '' ? self::toFloat($r['balance']) : null,
        ];

        if ($out['symbol'] === '' && !in_array($out['type'], ['balance', 'credit'], true)) return null;
        if ($out['ticket'] === '') return null;

        return $out;
    }

    // ── Header / account identity block ───────────────────────────────

    private static function parseAccountHeader(array $preambleRows): array
    {
        $account = ['name' => null, 'number' => null, 'currency' => null, 'server' => null, 'mode' => null, 'company' => null, 'generated_at' => null];

        foreach ($preambleRows as $row) {
            if (count($row) < 2) continue;
            $label = strtolower(trim((string)$row[0]));
            // HTML rows put the value in the very next cell; XLSX rows
            // (built from real spreadsheet columns) leave the merged-cell
            // gap as empty strings first - so find the first non-empty
            // cell after the label instead of assuming index 1.
            $value = '';
            for ($i = 1; $i < count($row); $i++) {
                if (trim((string)$row[$i]) !== '') { $value = trim((string)$row[$i]); break; }
            }
            if ($value === '') continue;

            if (strpos($label, 'name') === 0) {
                $account['name'] = $value;
            } elseif (strpos($label, 'account') === 0) {
                // "10018301 (USD, MavenTrade-Server, real, Hedge)"
                if (preg_match('/^(\S+)\s*\(([^)]*)\)/', $value, $m)) {
                    $account['number'] = $m[1];
                    $parts = array_map('trim', explode(',', $m[2]));
                    $account['currency'] = $parts[0] ?? null;
                    $account['server'] = $parts[1] ?? null;
                    $account['mode'] = $parts[2] ?? null; // real/demo
                } else {
                    $account['number'] = $value;
                }
            } elseif (strpos($label, 'company') === 0) {
                $account['company'] = $value;
            } elseif (strpos($label, 'date') === 0) {
                $account['generated_at'] = self::toDate($value);
            }
        }

        return $account;
    }

    private static function parseResultsBlock(array $rows): array
    {
        $summary = [];
        $patterns = [
            'total_net_profit' => 'total net profit',
            'gross_profit' => 'gross profit',
            'gross_loss' => 'gross loss',
            'profit_factor' => 'profit factor',
            'total_trades' => 'total trades',
            'profit_trades' => 'profit trades',
            'loss_trades' => 'loss trades',
        ];

        // Results rows are label:value pairs, several per row
        // ("Total Net Profit: | -241.64 | Gross Profit: | 444.38 | ...").
        // In HTML these sit in directly adjacent cells; in XLSX (real
        // spreadsheet columns, matching the merged-cell layout MT5 draws)
        // there's a gap of empty cells between a label and its value, so
        // this scans forward for the next non-empty cell rather than
        // assuming row[$i+1].
        foreach ($rows as $row) {
            $n = count($row);
            for ($i = 0; $i < $n; $i++) {
                $label = strtolower(trim((string)$row[$i], " \t:"));
                if ($label === '') continue;
                $key = array_search($label, $patterns, true);
                if ($key === false || isset($summary[$key])) continue;

                $value = '';
                for ($j = $i + 1; $j < $n; $j++) {
                    if (trim((string)$row[$j]) !== '') { $value = trim((string)$row[$j]); break; }
                }
                if ($value === '') continue;
                // "0 (0.00%)" style values - take the leading number
                if (preg_match('/-?[\d\s,]+\.?\d*/', $value, $numMatch)) {
                    $summary[$key] = self::toFloat($numMatch[0]);
                }
            }
        }
        return $summary;
    }

    // ── Shared value coercion ──────────────────────────────────────────

    private static function toFloat($v): float
    {
        $v = trim((string)$v);
        if ($v === '' || $v === '-') return 0.0;
        // MT5 uses a plain space as the thousands separator (e.g. "10 000 000.00").
        $v = str_replace(["\u{00A0}", ' '], '', $v);
        $v = preg_replace('/[$€£¥₹]/u', '', $v);
        $v = str_replace(',', '', $v);
        // Orders' Volume column reads "0.01/0.01" (requested/filled) - take
        // only the leading numeric token rather than concatenating both
        // halves into one invalid string.
        if (preg_match('/^-?[0-9.]+/', $v, $m)) $v = $m[0];
        return is_numeric($v) ? (float)$v : 0.0;
    }

    private static function toDate(?string $v): ?string
    {
        $v = trim((string)$v);
        if ($v === '') return null;
        if (preg_match('/(\d{4})\.(\d{2})\.(\d{2})\s+(\d{2}):(\d{2})(?::(\d{2}))?/', $v, $m)) {
            return sprintf('%04d-%02d-%02d %02d:%02d:%02d', $m[1], $m[2], $m[3], $m[4], $m[5], $m[6] ?? '00');
        }
        $ts = strtotime($v);
        return $ts ? date('Y-m-d H:i:s', $ts) : null;
    }
}
