<?php
/**
 * TraderAlign Analytics Engine
 * 
 * Single source of truth for ALL analytics calculations.
 * Every page (Dashboard, Journal, Reports) calls functions from this engine.
 * No page calculates its own metrics.
 * 
 * @package TraderAlign
 * @subpackage Analytics
 * @version 1.0.0
 */

require_once __DIR__ . '/../../config/config.php';
require_once __DIR__ . '/../../src/JourneySegmentation.php';
require_once __DIR__ . '/../../src/PropFirmRuleEngine.php';

class AnalyticsEngine {

    // ════════════════════════════════════════════════════════════════
    // CALCULATOR TOLERANCE — data-derived, not arbitrary. The calculator's
    // own risk% output legitimately varies from a strategy's declared
    // limit because position size is constrained to whole lot-step
    // increments (broker minimum lot, contract size, pip value): a
    // strategy declaring 1.00% risk can genuinely produce 0.95%-1.20%
    // depending on which lot increment the required position size rounds
    // to. That is expected calculator behavior, not over-risking, and
    // must not be scored as a discipline violation.
    //
    // Derivation (2026-07-27, see CLAUDE.md's dated entry for the full
    // writeup): compared all 266 real closed trades' actual risk_pct
    // against their strategy's declared max_risk_per_trade_pct. Found a
    // clean natural break — trades attributable to genuine lot-step
    // quantization (not a manual override or deliberate breach-test
    // trade) never exceeded 20% relative deviation from the declared
    // value (p95=18.2%, p100=20.0% within that "clean" cluster, n=125);
    // the next real value jumped straight to 47% relative deviation — a
    // different regime entirely (manual over-risking). 20% relative,
    // with a small absolute floor for very low declared risk%, is used
    // as the standard tolerance band everywhere a declared/planned value
    // is compared against the calculator's actual output.
    //
    // The same derivation was run for R:R geometric consistency (does a
    // trade's own stored risk_reward_tp1 match what its own stored entry/
    // SL/TP prices geometrically imply — tick/price-rounding noise, not
    // manual override): the "clean" distribution capped at 0.2642 R
    // (p99=p100), so CALC_GEOMETRIC_RR_TOLERANCE is set to 0.30 (the old
    // 0.15 was miscalibrated — it flagged the majority of genuinely
    // consistent trades as "mismatched"). CALC_GEOMETRIC_RISK_PCT_TOLERANCE
    // was checked the same way and found to already sit correctly between
    // the clean cluster (p95=0.034) and the outlier cluster (p99=1.00), so
    // its value (0.5) is unchanged — named here only for auditability.
    //
    // Price/lot tolerances below (entry/SL fill precision, lot-size
    // adherence) were already established in a prior session; checked
    // against the same 266-trade dataset and found un-exercised by real
    // noise (actual_entry_price/actual_sl_price/actual_lots never once
    // deviated from their planned counterparts in this DB — the "Won"/
    // "Lost" Drafts flow always carries the plan forward unedited in
    // practice), so there was no real-data basis to tighten or loosen
    // them; kept as-is, just centralized into named constants here.
    // ════════════════════════════════════════════════════════════════
    const CALC_TOLERANCE_RATIO = 0.20;             // 20% relative — fallback for instruments/classes without their own derived band (see below)
    const CALC_TOLERANCE_FLOOR_PCT = 0.05;          // absolute floor (percentage points) for very low declared risk%
    const CALC_GEOMETRIC_RR_TOLERANCE = 0.30;       // R:R geometric self-consistency (was 0.15, miscalibrated)
    const CALC_GEOMETRIC_RISK_PCT_TOLERANCE = 0.5;  // risk% geometric self-consistency (validated correct, unchanged)
    const CALC_PRICE_TOLERANCE_RATIO = 0.001;       // 0.1% — entry/SL/TP fill precision
    const CALC_LOT_TOLERANCE_RATIO = 0.05;          // 5% — lot-size adherence (planned vs actual/broker-verified)

    // ════════════════════════════════════════════════════════════════
    // PER-ASSET-CLASS RISK TOLERANCE (2026-07-27, second derivation — see
    // CLAUDE.md's dated entry). The single global 20% band above was
    // re-examined per instrument and found to be a population blend, not
    // one true figure: re-running the same p95/p100-of-clean-cluster
    // method separately per instrument showed the "clean" (lot-step
    // quantization only, not manual over-risking) ceiling is NOT constant
    // across instruments —
    //   eurusd  (n=155, clean n=41): ceiling 0.99% relative
    //   gbpusd  (n=5,   clean n=4):  ceiling 0.78% relative
    //   xauusd  (n=81,  clean n=63): ceiling 20.00% relative  <- this one
    //           instrument alone was what produced the original "20%"
    //           global figure; it was never representative of forex.
    //   xagusd  (n=4,   clean n=2):  ceiling 17.96% relative
    //   xptusd  (n=5,   clean n=4):  ceiling 15.47% relative
    //   coffee  (n=7,   clean n=5):  ceiling 49.19% relative (wider still —
    //           coarser contract/lot-step sizing than metals)
    // Forex majors and metals both have enough real trades to trust their
    // own class-level figure; every other class (Forex Crosses, Indices,
    // Commodities, Crypto) has too few real trades (n<20) in this database
    // to derive a trustworthy figure of its own yet, so they fall back to
    // the original global 20% — deliberately on the loose/conservative
    // side (risk of a missed over-risk flag) rather than guessing a
    // tighter number from n<10 samples (risk of false positives). This
    // must be re-derived per-class once more real user data accumulates —
    // flagged, not treated as permanently final.
    const ASSET_CLASS_TOLERANCE_RATIO = [
        'Forex Majors' => 0.01,   // 1% relative — eurusd/gbpusd clean ceiling ~0.99%, rounded up
        'Metals'       => 0.20,   // 20% relative — xauusd/xagusd/xptusd clean ceiling ~15-20%, consistent across all three
    ];
    // Same symbol groupings as reports.php's $assetClassMap (kept as a
    // separate copy so engine.php stays self-contained, same precedent as
    // normalizeInstrumentSymbol() below) — only the classes with their own
    // ASSET_CLASS_TOLERANCE_RATIO entry actually change behavior; the rest
    // exist here so a future re-derivation has real per-class buckets to
    // slot into without re-inventing the map.
    const ASSET_CLASS_MAP = [
        'Forex Majors'  => ['EURUSD','USDJPY','GBPUSD','USDCHF','AUDUSD','USDCAD','NZDUSD'],
        'Forex Crosses' => ['EURGBP','EURJPY','EURCHF','GBPJPY','GBPCHF','AUDJPY','AUDNZD','NZDJPY','CADJPY','CHFJPY','EURAUD','EURNZD','GBPAUD','GBPNZD'],
        'Metals'        => ['XAUUSD','XAGUSD','XPTUSD','XPDUSD','GOLD','SILVER'],
        'Indices'       => ['US30','US100','US500','SPX500','NAS100','UK100','GER40','FRA40','JPN225','AUS200','EU50'],
        'Commodities'   => ['USOIL','UKOIL','XBRUSD','XTIUSD','NGAS','NATGAS','COFFEE','COPPER','CORN','WHEAT','SOYBEAN'],
        'Crypto'        => ['BTCUSD','ETHUSD','XRPUSD','LTCUSD','BCHUSD','ADAUSD','DOTUSD','SOLUSD'],
        'Stocks'        => ['AAPL','TSLA','NVDA','AMZN','GOOGL','MSFT','META'],
    ];

    /** Classifies a normalized instrument symbol into an asset class, or null if unrecognized. */
    private static function classifyAssetClass($instrument) {
        $norm = self::normalizeInstrumentSymbol($instrument);
        foreach (self::ASSET_CLASS_MAP as $className => $symbols) {
            if (in_array($norm, $symbols, true)) return $className;
        }
        return null;
    }

    // Tolerance band for a declared risk% (in percentage points) — the
    // larger of a relative band (class-derived where available, else the
    // global fallback) or a small absolute floor, so a very low declared
    // risk% (e.g. 0.1%) still gets a workable band rather than an
    // unrealistically tiny one.
    public static function calculatorRiskTolerance($declaredPct, $instrument = null) {
        if ($declaredPct === null) return null;
        $assetClass = $instrument !== null ? self::classifyAssetClass($instrument) : null;
        $ratio = ($assetClass !== null && isset(self::ASSET_CLASS_TOLERANCE_RATIO[$assetClass]))
            ? self::ASSET_CLASS_TOLERANCE_RATIO[$assetClass]
            : self::CALC_TOLERANCE_RATIO;
        return max(abs((float)$declaredPct) * $ratio, self::CALC_TOLERANCE_FLOOR_PCT);
    }

    // ════════════════════════════════════════════════════════════════
    // CONTRACT-CONSTRAINED CLASSIFICATION (2026-07-27, item 7 finishing
    // pass) — a structurally different question from the tolerance bands
    // above. Tolerance answers "did the trader over-risk relative to
    // normal lot-size rounding?" This answers "could the trader have hit
    // the declared target AT ALL on this instrument, at this account
    // balance?" (e.g. 1% risk on a $5,000 account may already be
    // impossible on an instrument whose minimum lot risks far more —
    // Coffee at broker minimum can risk well above a 1% target on a small
    // account). When the minimum achievable risk% already exceeds the
    // declared target beyond tolerance, the trade is reclassified as
    // Contract-Constrained instead of Over-Risk — one reflects trader
    // behavior, the other reflects instrument limitations, and mixing
    // them would misattribute a structural ceiling as indiscipline.
    //
    // INSTRUMENT_SPECS is extracted directly from calculator-source.html's
    // real INSTRUMENTS object (the calculator's own sizing engine) — only
    // the fields the minimum-risk floor formula needs (pipMultiplier,
    // pipValue, rateType, tickValue for the index_spec fallback, minLot).
    // This is NOT a second, independently-invented sizing engine: the
    // values and the formula below are copied verbatim from the
    // calculator's own "Adjusted to broker min" code path
    // (posRisk = (brokerMin / 0.01) * slDistancePips * pipValueSL), never
    // re-derived. PHP can't literally share code with a static HTML/JS
    // file, so this is a mirror, not a shared module — if
    // calculator-source.html's INSTRUMENTS object changes, this table
    // must be updated to match (flagged here, not automated).
    const INSTRUMENT_SPECS = [
        'EURUSD' => ['pipMultiplier'=>10000,'pipValue'=>0.10,'rateType'=>null,'minLot'=>0.01],
        'GBPUSD' => ['pipMultiplier'=>10000,'pipValue'=>0.10,'rateType'=>null,'minLot'=>0.01],
        'AUDUSD' => ['pipMultiplier'=>10000,'pipValue'=>0.10,'rateType'=>null,'minLot'=>0.01],
        'NZDUSD' => ['pipMultiplier'=>10000,'pipValue'=>0.10,'rateType'=>null,'minLot'=>0.01],
        'USDJPY' => ['pipMultiplier'=>100,  'pipValue'=>null,'rateType'=>'self_jpy','minLot'=>0.01],
        'USDCAD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'self_cad','minLot'=>0.01],
        'USDCHF' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'self_chf','minLot'=>0.01],
        'EURJPY' => ['pipMultiplier'=>100,  'pipValue'=>null,'rateType'=>'live_jpy','minLot'=>0.01],
        'GBPJPY' => ['pipMultiplier'=>100,  'pipValue'=>null,'rateType'=>'live_jpy','minLot'=>0.01],
        'CADJPY' => ['pipMultiplier'=>100,  'pipValue'=>null,'rateType'=>'live_jpy','minLot'=>0.01],
        'AUDJPY' => ['pipMultiplier'=>100,  'pipValue'=>null,'rateType'=>'live_jpy','minLot'=>0.01],
        'NZDJPY' => ['pipMultiplier'=>100,  'pipValue'=>null,'rateType'=>'live_jpy','minLot'=>0.01],
        'CHFJPY' => ['pipMultiplier'=>100,  'pipValue'=>null,'rateType'=>'live_jpy','minLot'=>0.01],
        'EURGBP' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_gbp','minLot'=>0.01],
        'EURAUD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_aud','minLot'=>0.01],
        'EURCAD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_cad','minLot'=>0.01],
        'EURCHF' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_chf','minLot'=>0.01],
        'GBPAUD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_aud','minLot'=>0.01],
        'GBPCAD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_cad','minLot'=>0.01],
        'GBPCHF' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_chf','minLot'=>0.01],
        'GBPNZD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_nzd','minLot'=>0.01],
        'EURNZD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_nzd','minLot'=>0.01],
        'AUDCAD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_cad','minLot'=>0.01],
        'AUDCHF' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_chf','minLot'=>0.01],
        'AUDNZD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_nzd','minLot'=>0.01],
        'CADCHF' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_chf','minLot'=>0.01],
        'NZDCAD' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_cad','minLot'=>0.01],
        'NZDCHF' => ['pipMultiplier'=>10000,'pipValue'=>null,'rateType'=>'live_chf','minLot'=>0.01],
        'XAUUSD' => ['pipMultiplier'=>10,   'pipValue'=>0.10,'rateType'=>null,'minLot'=>0.01],
        'XAGUSD' => ['pipMultiplier'=>100,  'pipValue'=>0.50,'rateType'=>null,'minLot'=>0.01],
        'XPTUSD' => ['pipMultiplier'=>10,   'pipValue'=>0.10,'rateType'=>null,'minLot'=>0.01],
        'XPDUSD' => ['pipMultiplier'=>10,   'pipValue'=>0.10,'rateType'=>null,'minLot'=>0.01],
        'US30'   => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>5.00, 'minLot'=>0.01],
        'US500'  => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1.00, 'minLot'=>0.01],
        'US100'  => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>20.00,'minLot'=>0.01],
        'GER40'  => ['pipMultiplier'=>0.1,  'pipValue'=>null,'rateType'=>'live_eur','minLot'=>0.01],
        'UK100'  => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'live_gbp','minLot'=>0.01],
        'AUS200' => ['pipMultiplier'=>0.1,  'pipValue'=>null,'rateType'=>'live_aud','minLot'=>0.01],
        'USOIL'  => ['pipMultiplier'=>1,    'pipValue'=>1.0, 'rateType'=>null,'minLot'=>1],
        'UKOIL'  => ['pipMultiplier'=>1,    'pipValue'=>1.0, 'rateType'=>null,'minLot'=>1],
        'NATGAS' => ['pipMultiplier'=>1,    'pipValue'=>300.0,'rateType'=>null,'minLot'=>0.01],
        'COFFEE' => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>2000,'minLot'=>0.01],
        'COCOA'  => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1,   'minLot'=>0.01],
        'SUGAR'  => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1120,'minLot'=>0.01],
        'CORN'   => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>500, 'minLot'=>0.01],
        'WHEAT'  => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>400, 'minLot'=>0.01],
        'COTTON' => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>100, 'minLot'=>0.01],
        'BTCUSD' => ['pipMultiplier'=>1,    'pipValue'=>0.01,'rateType'=>null,'minLot'=>0.01],
        'ETHUSD' => ['pipMultiplier'=>10,   'pipValue'=>0.01,'rateType'=>null,'minLot'=>0.01],
        'AAPL'   => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1,'minLot'=>1],
        'TSLA'   => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1,'minLot'=>1],
        'NVDA'   => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1,'minLot'=>1],
        'AMZN'   => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1,'minLot'=>1],
        'GOOGL'  => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1,'minLot'=>1],
        'MSFT'   => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1,'minLot'=>1],
        'META'   => ['pipMultiplier'=>1,    'pipValue'=>null,'rateType'=>'index_spec','tickValue'=>1,'minLot'=>1],
    ];

    // Instruments whose pip value depends on a LIVE cross-rate the
    // calculator fetches at calc time (e.g. EURJPY needs a live USD/JPY
    // rate; GER40 needs a live EUR/USD rate) — this analytics engine has
    // no historical record of what that rate was at the time of a past
    // trade, so the minimum-achievable-risk floor cannot be honestly
    // computed for these without approximating with today's rate against
    // a historical trade. Rather than approximate, these are skipped
    // entirely (minAchievableRiskPct() returns null) — a genuine,
    // documented data limitation, not a bug (see CLAUDE.md item 7 entry).
    const LIVE_RATE_TYPES = ['live_jpy', 'live_cad', 'live_gbp', 'live_aud', 'live_nzd', 'live_eur', 'live_chf'];

    /**
     * Minimum achievable risk % at this instrument's broker-minimum lot
     * size, for the given entry/SL prices and account balance — reusing
     * the calculator's own formula for what happens when the ideal lot
     * size would round below the broker minimum (calculator-source.html:
     * posRisk = (brokerMin / 0.01) * slDistancePips * pipValueSL). Returns
     * null when the instrument is unrecognized, inputs are invalid, or
     * the instrument needs a historical live cross-rate this engine
     * doesn't have (see LIVE_RATE_TYPES).
     */
    public static function minAchievableRiskPct($instrument, $entryPrice, $slPrice, $accountBalance) {
        if ($entryPrice === null || $slPrice === null || $accountBalance === null || (float)$accountBalance <= 0) return null;
        $norm = self::normalizeInstrumentSymbol($instrument ?? '');
        $spec = self::INSTRUMENT_SPECS[$norm] ?? null;
        if ($spec === null) return null;
        if (in_array($spec['rateType'], self::LIVE_RATE_TYPES, true)) return null;

        $slDistance = abs((float)$entryPrice - (float)$slPrice);
        if ($slDistance <= 0) return null;
        $slDistancePips = $slDistance * $spec['pipMultiplier'];

        $rateType = $spec['rateType'];
        if ($rateType === 'self_jpy') {
            $pipValueSL = ((float)$slPrice != 0.0) ? 10 / (float)$slPrice : null;
        } elseif ($rateType === 'self_cad' || $rateType === 'self_chf') {
            $pipValueSL = ((float)$slPrice != 0.0) ? 10 / ((float)$slPrice * 100) : null;
        } elseif ($rateType === 'index_spec') {
            $tv = $spec['tickValue'] ?? 5.0;
            $pipValueSL = $tv / 100;
        } else {
            $pipValueSL = $spec['pipValue'] ?? null;
        }
        if ($pipValueSL === null || $pipValueSL <= 0) return null;

        $brokerMin = $spec['minLot'] ?? 0.01;
        $minRiskDollars = ($brokerMin / 0.01) * $slDistancePips * $pipValueSL;
        if ($minRiskDollars <= 0) return null;
        return ($minRiskDollars / (float)$accountBalance) * 100;
    }

    // ════════════════════════════════════════════════════════════════
    // ASSET-AWARE MOVEMENT UNITS (2026-08-04, Journal 4-Stage Lifecycle
    // polish pass) — additive only, zero effect on any $ calculation
    // anywhere in the app. Journal's single-trade "Pips" row previously
    // used one hardcoded forex-style heuristic everywhere (a bare
    // 6-letter-ticker regex that misclassified XAUUSD/NATGAS/COFFEE/
    // COTTON etc. as forex and multiplied by 10000), producing nonsense
    // like "+0.1" for a real, legitimate commodity price move. This
    // reuses INSTRUMENT_SPECS' own real, MT5-verified pipMultiplier per
    // instrument for the SCALE FACTOR (never a new/guessed multiplier —
    // the exact same number the calculator itself uses for its $ math) —
    // only the display UNIT LABEL and decimal precision are new. Never
    // touches sl_distance_pips, the calculator's stored $ figures, or any
    // other money computation.
    const MOVEMENT_UNIT_BY_CLASS = [
        'Forex Majors'  => ['unit' => 'Pips',      'decimals' => 1],
        'Forex Crosses' => ['unit' => 'Pips',      'decimals' => 1],
        'Metals'        => ['unit' => 'Points',    'decimals' => 1],
        'Indices'       => ['unit' => 'Points',    'decimals' => 1],
        // Commodities (natgas/usoil/ukoil/coffee/etc.) mostly carry a
        // pipMultiplier of 1 in INSTRUMENT_SPECS — their real price move
        // is a small raw-dollar delta (e.g. natgas $0.021), so a higher
        // decimal precision is used here purely for DISPLAY so the real
        // number isn't collapsed to a misleading "0.0" — the underlying
        // value is unchanged.
        'Commodities'   => ['unit' => 'Points',    'decimals' => 3],
        'Crypto'        => ['unit' => '$',         'decimals' => 2],
        'Stocks'        => ['unit' => '$',         'decimals' => 2],
    ];

    /**
     * Real, asset-aware movement unit + scale factor for an instrument.
     * Forex → Pips, Metals/Indices/Commodities → Points, Crypto/Stocks →
     * raw $ price movement. Falls back to a generic Points/multiplier-1
     * spec for an unrecognized instrument rather than guessing a class.
     */
    public static function movementSpec($instrument) {
        $norm = self::normalizeInstrumentSymbol($instrument ?? '');
        $class = self::classifyAssetClass($instrument);
        $classInfo = self::MOVEMENT_UNIT_BY_CLASS[$class] ?? ['unit' => 'Points', 'decimals' => 1];
        $isDollarUnit = $classInfo['unit'] === '$';
        $spec = self::INSTRUMENT_SPECS[$norm] ?? null;
        $multiplier = $isDollarUnit ? 1.0 : (float)($spec['pipMultiplier'] ?? 1.0);
        return ['unit' => $classInfo['unit'], 'multiplier' => $multiplier, 'decimals' => $classInfo['decimals'], 'class' => $class];
    }

    /** A real price difference (already computed from real, stored prices — never guessed), converted into the instrument's own asset-aware movement unit. */
    public static function movementValue($instrument, $priceDiff) {
        if ($priceDiff === null || $priceDiff === '') return null;
        $spec = self::movementSpec($instrument);
        return round((float)$priceDiff * $spec['multiplier'], $spec['decimals']);
    }

    private static $pdo = null;

    private static function getConnection() {
        if (self::$pdo === null) {
            $dsn = "mysql:host=" . DB_HOST . ";port=" . DB_PORT . ";dbname=" . DB_NAME . ";charset=utf8mb4";
            self::$pdo = new PDO($dsn, DB_USER, DB_PASS, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]);
        }
        return self::$pdo;
    }
    
    private static function buildFilterClause($filters, $tableAlias = '') {
        $conditions = [];
        $params = [];
        $prefix = $tableAlias ? "{$tableAlias}." : "";
        
        if (!empty($filters['userId'])) {
            $conditions[] = "{$prefix}user_id = :userId";
            $params[':userId'] = $filters['userId'];
        }
        if (!empty($filters['accountId'])) {
            $conditions[] = "{$prefix}account_id = :accountId";
            $params[':accountId'] = $filters['accountId'];
        }
        if (!empty($filters['strategyVersionId'])) {
            $conditions[] = "{$prefix}strategy_version_id = :strategyVersionId";
            $params[':strategyVersionId'] = $filters['strategyVersionId'];
        }
        if (!empty($filters['dateFrom'])) {
            $conditions[] = "{$prefix}captured_at >= :dateFrom";
            $params[':dateFrom'] = $filters['dateFrom'];
        }
        if (!empty($filters['dateTo'])) {
            $conditions[] = "{$prefix}captured_at <= :dateTo";
            $params[':dateTo'] = $filters['dateTo'];
        }

        // Instrument / Session / Classification / closed-at date range —
        // the same filter set Journal's Trade Journal section exposes
        // (src/JournalTradesQuery.php's buildJournalTradesFilters()),
        // added here so dashboard.php's canonical getUserMetrics()/
        // getAccountMetrics() calls can be scoped identically instead of
        // dashboard.php re-deriving win/loss/P&L totals from its own raw
        // SQL. closedFrom/closedTo are deliberately separate keys from
        // dateFrom/dateTo above (which filter on captured_at, used
        // elsewhere in this engine) — Journal's own From/To fields filter
        // by when a trade CLOSED, so these match that semantic exactly
        // (day-boundary values are the caller's responsibility, same as
        // Journal's own 'YYYY-MM-DD 00:00:00'/'YYYY-MM-DD 23:59:59'
        // convention).
        if (!empty($filters['instrument'])) {
            $conditions[] = "{$prefix}instrument = :fInstrument";
            $params[':fInstrument'] = $filters['instrument'];
        }
        if (!empty($filters['session'])) {
            $conditions[] = "{$prefix}session = :fSession";
            $params[':fSession'] = $filters['session'];
        }
        if (!empty($filters['classification'])) {
            $conditions[] = "{$prefix}trade_classification = :fClassification";
            $params[':fClassification'] = $filters['classification'];
        }
        if (!empty($filters['closedFrom'])) {
            $conditions[] = "{$prefix}closed_at >= :fClosedFrom";
            $params[':fClosedFrom'] = $filters['closedFrom'];
        }
        if (!empty($filters['closedTo'])) {
            $conditions[] = "{$prefix}closed_at <= :fClosedTo";
            $params[':fClosedTo'] = $filters['closedTo'];
        }

        // Validation-mode trades (strategy-validation.php's practice runs)
        // must never leak into production analytics — always applied, not
        // gated behind a $filters key, since every caller of this builder
        // queries `trades` for real account/user metrics. The ONE opt-in
        // exception: $filters['validationScope'] === 'only', used exclusively
        // by AnalyticsEngine::getValidationStrategyMetrics() (the Validation
        // Tracker's canonical metric source) to flip this to the mirror
        // image — real (main-workflow) trades must never leak into
        // validation metrics either. Every existing caller that doesn't
        // pass this key gets byte-identical behavior to before.
        if (($filters['validationScope'] ?? 'exclude') === 'only') {
            $conditions[] = "{$prefix}is_validation = 1";
        } else {
            $conditions[] = "({$prefix}is_validation = 0 OR {$prefix}is_validation IS NULL)";
        }

        // Only ever closed trades count toward realized win/loss/streak/
        // total-trades math — open/draft/cancelled rows have no realized
        // outcome. This was previously missing entirely (buildFilterClause()
        // is only used by getAccountMetrics()/getUserMetrics()), which for
        // an open trade fell back to COALESCE(...)'s projected_pnl branch
        // (a hypothetical, not-yet-realized number) and silently counted it
        // as a real win/loss. Confirmed live: account f4988164-... (The5ers
        // 5K - Attempt 1) has exactly 1 closed trade (+$151.20) and 1 open
        // trade (projected_pnl $777.00, no real outcome yet) — before this
        // fix, Total Trades/Win Streak both read 2 because the open trade's
        // $777.00 projection was being treated as a second realized win.
        $conditions[] = "{$prefix}status = 'closed'";

        $whereClause = !empty($conditions) ? "WHERE " . implode(" AND ", $conditions) : "";
        return [$whereClause, $params];
    }
    
    private static function calculateRunningMaximum($values) {
        $running = [];
        $max = PHP_FLOAT_MIN;
        foreach ($values as $value) {
            $max = max($max, $value);
            $running[] = $max;
        }
        return $running;
    }
    
    private static function calculateStreaks($pnls) {
        $currentWinStreak = 0;
        $currentLossStreak = 0;
        $maxWinStreak = 0;
        $maxLossStreak = 0;
        
        foreach ($pnls as $pnl) {
            if ($pnl > 0) {
                $currentWinStreak++;
                $currentLossStreak = 0;
                $maxWinStreak = max($maxWinStreak, $currentWinStreak);
            } elseif ($pnl < 0) {
                $currentLossStreak++;
                $currentWinStreak = 0;
                $maxLossStreak = max($maxLossStreak, $currentLossStreak);
            } else {
                $currentWinStreak = 0;
                $currentLossStreak = 0;
            }
        }
        return ['winning_streak' => $maxWinStreak, 'losing_streak' => $maxLossStreak];
    }
    
    // ========================================================================
    // DATA ACCESS HELPERS — thin lookups with no derived math of their own.
    // These exist so callers (e.g. api/colex/chat.php) never need to write
    // their own SQL against trading_accounts/trades and guess column names;
    // they call these instead, and get real columns guaranteed.
    // ========================================================================

    /** Active accounts for a user, richest first. */
    public static function getUserAccounts($userId, $activeOnly = true) {
        $db = self::getConnection();
        $sql = "SELECT id, label, account_type, current_balance, starting_balance, currency
                FROM trading_accounts
                WHERE user_id = :userId" . ($activeOnly ? " AND is_active = 1" : "") . "
                ORDER BY current_balance DESC";
        $stmt = $db->prepare($sql);
        $stmt->execute([':userId' => $userId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /** user_profiles.base_currency for a user, defaulting to 'USD' (the app-wide default) if unset/not found. Public — the one place any caller resolves a user's own reporting currency, never re-queried independently. */
    public static function getUserBaseCurrency($userId) {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT base_currency FROM user_profiles WHERE user_id = :uid");
        $stmt->execute([':uid' => $userId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $cur = $row['base_currency'] ?? 'USD';
        return CurrencyService::isSupported($cur) ? strtoupper($cur) : 'USD';
    }

    /**
     * CurrencyService::convert(), with a same-currency short-circuit (avoids
     * a wasted call for the overwhelmingly common all-USD case) and a
     * never-null contract: if a live rate genuinely cannot be obtained
     * (every provider down AND nothing cached), returns the raw,
     * unconverted amount rather than silently dropping it from a sum —
     * a stale/missing rate should degrade to "treat as same currency" for
     * an aggregate total, not make part of the total vanish.
     */
    private static function convertOrRaw($amount, $fromCurrency, $toCurrency) {
        if (strtoupper((string)$fromCurrency) === strtoupper((string)$toCurrency)) return $amount;
        $converted = CurrencyService::convert($amount, $fromCurrency, $toCurrency);
        return $converted ?? $amount;
    }

    /**
     * Recent closed trades for a user within a date range, shaped for
     * display (symbol/direction/pnl/prices). Built entirely on top of
     * computeFourStageRaw() — the same single source of truth used by
     * getFourStageComparison() — so this never re-derives instrument,
     * direction, or the challenge_pnl/broker_pnl split on its own.
     */
    public static function getRecentTrades($userId, $dateFrom, $dateTo, $limit = 10) {
        $db = self::getConnection();
        $limit = (int)$limit;
        $stmt = $db->prepare("
            SELECT id FROM trades
            WHERE user_id = :userId AND status = 'closed' AND deleted_at IS NULL
            AND (is_validation = 0 OR is_validation IS NULL)
            AND closed_at BETWEEN :dateFrom AND :dateTo
            ORDER BY closed_at DESC
            LIMIT {$limit}
        ");
        $stmt->execute([':userId' => $userId, ':dateFrom' => $dateFrom, ':dateTo' => $dateTo]);
        $tradeIds = $stmt->fetchAll(PDO::FETCH_COLUMN);

        $trades = [];
        foreach ($tradeIds as $tradeId) {
            $raw = self::computeFourStageRaw($tradeId);
            if (isset($raw['error'])) continue;

            $pnl = $raw['stage3']['effective_pnl'];
            $trades[] = [
                'id' => $raw['trade_id'],
                'symbol' => $raw['instrument'],
                'direction' => $raw['direction'],
                // Both keys carry the same already-resolved effective P&L
                // (challenge vs broker was already decided inside
                // computeFourStageRaw), kept as two keys only so existing
                // ?? fallback callers keep working unchanged.
                'broker_pnl' => $pnl,
                'challenge_pnl' => $pnl,
                'captured_at' => $raw['captured_at'],
                'closed_at' => $raw['closed_at'],
                'risk_pct' => $raw['stage2']['risk_pct'],
                'entry_price' => $raw['stage2']['entry_price'],
                'actual_exit_price' => $raw['stage3']['actual_exit_price'],
            ];
        }
        return $trades;
    }

    // ========================================================================
    // LAYER 1: ACCOUNT METRICS
    // ========================================================================
    
    /**
     * Core trade-based metrics math — shared by getAccountMetrics() (one
     * account) and getUserMetrics() (aggregated across a user's accounts),
     * so the two never drift apart. Each row in $trades must already carry
     * the correct challenge_pnl/broker_pnl value under the key 'broker_pnl'
     * (the caller resolves which column applies per account_type).
     */
    private static function computeMetricsFromTrades($trades, $startingBalance, $currentBalance) {
        if (empty($trades)) {
            return [
                'current_balance' => $currentBalance,
                'starting_balance' => $startingBalance,
                'equity' => $currentBalance,
                'peak_equity' => $startingBalance,
                'net_pnl' => 0,
                'return_pct' => 0,
                'current_drawdown_pct' => 0,
                'max_drawdown_pct' => 0,
                'win_rate' => 0,
                'profit_factor' => null,
                'expectancy' => 0,
                'avg_winner' => null,
                'avg_loser' => null,
                'best_trade' => null,
                'worst_trade' => null,
                'winning_streak' => 0,
                'losing_streak' => 0,
                'total_trades' => 0,
                'avg_trade_duration_hours' => 0,
                'sharpe_ratio' => 0,
                'sortino_ratio' => 0,
                'win_count' => 0,
                'loss_count' => 0,
                'breakeven_count' => 0
            ];
        }

        $cumulativePnl = 0;
        $equityCurve = [];
        $pnls = [];

        foreach ($trades as $trade) {
            $pnl = (float)($trade['broker_pnl'] ?? 0);
            $cumulativePnl += $pnl;
            $equityCurve[] = $cumulativePnl;
            $pnls[] = $pnl;
        }

        $netPnl = $cumulativePnl;

        $runningMax = self::calculateRunningMaximum($equityCurve);
        $maxDrawdownPct = 0;

        foreach ($equityCurve as $i => $equity) {
            $absoluteEquity = $startingBalance + $equity;
            $absoluteRunningMax = $startingBalance + $runningMax[$i];

            if ($absoluteRunningMax > 0) {
                $dd = ($absoluteEquity - $absoluteRunningMax) / $absoluteRunningMax;
                if ($dd < $maxDrawdownPct) {
                    $maxDrawdownPct = $dd;
                }
            }
        }

        $lastEquity = end($equityCurve);
        $currentEquity = $startingBalance + ($lastEquity !== false ? $lastEquity : 0);
        $lastRunningMax = end($runningMax);
        $absoluteLastRunningMax = $startingBalance + ($lastRunningMax !== false ? $lastRunningMax : 0);

        $currentDrawdown = $absoluteLastRunningMax > 0
            ? ($currentEquity - $absoluteLastRunningMax) / $absoluteLastRunningMax
            : 0;

        $peakEquity = $startingBalance + max($equityCurve);

        $winners = array_filter($pnls, function($p) { return $p > 0; });
        $losers = array_filter($pnls, function($p) { return $p < 0; });

        $totalTrades = count($pnls);
        $winCount = count($winners);
        $lossCount = count($losers);
        $winRate = $totalTrades > 0 ? $winCount / $totalTrades : 0;

        $grossProfit = array_sum($winners);
        $grossLoss = abs(array_sum($losers));

        // Profit Factor is undefined (not infinite) with zero losing trades
        // to divide by — null here, same as the "insufficient data" case,
        // so every renderer's existing `!== null` guard already does the
        // right thing. Previously returned PHP_FLOAT_MAX for "profit but no
        // losses," which is a real float value (not null/NaN/INF), so it
        // slipped past every `$x !== null` guard and rendered as the raw
        // "1.7976931348623E+308" wherever a call site didn't separately
        // check for that exact sentinel.
        $profitFactor = null;
        if ($grossLoss > 0) {
            $profitFactor = $grossProfit / $grossLoss;
        } elseif ($winCount > 0 || $lossCount > 0) {
            $profitFactor = $grossProfit > 0 ? null : 0;
        }

        $avgWinner = $winCount > 0 ? $grossProfit / $winCount : null;
        $avgLoser = $lossCount > 0 ? -$grossLoss / $lossCount : null;

        $expectancy = $totalTrades > 0 ? ($winRate * ($avgWinner ?? 0)) + ((1 - $winRate) * ($avgLoser ?? 0)) : 0;

        // Best Trade only ever comes from a winning trade, Worst Trade only
        // ever from a losing trade — reuses $winners/$losers (already
        // filtered by sign above for grossProfit/grossLoss) rather than
        // max/min over the unfiltered $pnls, which previously let a single
        // winning trade (or any zero-loss account) show its one win as both
        // the best AND the worst trade.
        $bestTrade = !empty($winners) ? max($winners) : null;
        $worstTrade = !empty($losers) ? min($losers) : null;

        $streaks = self::calculateStreaks($pnls);

        $durations = [];
        foreach ($trades as $trade) {
            if (!empty($trade['captured_at']) && !empty($trade['closed_at'])) {
                $durations[] = (strtotime($trade['closed_at']) - strtotime($trade['captured_at'])) / 3600;
            }
        }
        $avgDuration = !empty($durations) ? array_sum($durations) / count($durations) : 0;

        $returns = [];
        $prevEquity = $startingBalance;
        foreach ($equityCurve as $equity) {
            $absoluteEquity = $startingBalance + $equity;
            $returns[] = $prevEquity > 0 ? ($absoluteEquity - $prevEquity) / $prevEquity : 0;
            $prevEquity = $absoluteEquity;
        }

        $avgReturn = !empty($returns) ? array_sum($returns) / count($returns) : 0;
        $stdDev = 0;
        if (!empty($returns)) {
            $variance = array_sum(array_map(function($r) use ($avgReturn) {
                return pow($r - $avgReturn, 2);
            }, $returns)) / count($returns);
            $stdDev = sqrt($variance);
        }

        $sharpeRatio = $stdDev > 0 ? ($avgReturn / $stdDev) * sqrt($totalTrades) : 0;

        $downsideReturns = array_filter($returns, function($r) { return $r < 0; });
        $downsideDeviation = !empty($downsideReturns) ?
            sqrt(array_sum(array_map(function($r) { return pow($r, 2); }, $downsideReturns)) / count($downsideReturns)) : 0;
        $sortinoRatio = $downsideDeviation > 0 ? ($avgReturn / $downsideDeviation) * sqrt($totalTrades) : 0;

        return [
            'current_balance' => $currentBalance,
            'starting_balance' => $startingBalance,
            'equity' => $currentEquity,
            'peak_equity' => $peakEquity,
            'net_pnl' => $netPnl,
            'return_pct' => $startingBalance > 0 ? $netPnl / $startingBalance : 0,
            'current_drawdown_pct' => $currentDrawdown,
            'max_drawdown_pct' => $maxDrawdownPct,
            'win_rate' => $winRate,
            'profit_factor' => $profitFactor,
            'expectancy' => $expectancy,
            'avg_winner' => $avgWinner,
            'avg_loser' => $avgLoser,
            'best_trade' => $bestTrade,
            'worst_trade' => $worstTrade,
            'winning_streak' => $streaks['winning_streak'],
            'losing_streak' => $streaks['losing_streak'],
            'total_trades' => $totalTrades,
            'avg_trade_duration_hours' => $avgDuration,
            'sharpe_ratio' => $sharpeRatio,
            'sortino_ratio' => $sortinoRatio,
            'win_count' => $winCount,
            'loss_count' => $lossCount,
            'breakeven_count' => $totalTrades - $winCount - $lossCount
        ];
    }

    public static function getAccountMetrics($accountId, $filters = []) {
        $db = self::getConnection();
        $filters['accountId'] = $accountId;
        
        $stmt = $db->prepare("SELECT * FROM trading_accounts WHERE id = :accountId");
        $stmt->execute([':accountId' => $accountId]);
        $account = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$account) {
            return ['error' => 'Account not found'];
        }

        // Prop-firm accounts are measured against challenge_pnl (the firm's
        // internal challenge accounting), falling back to broker_pnl/
        // projected_pnl when challenge_pnl hasn't been backfilled for a
        // trade — same cascade as reports.php's $pnlExpr and the mv_*
        // views. Personal/live accounts skip challenge_pnl entirely.
        $pnlExprEngine = ($account['account_type'] ?? '') === 'prop_firm'
            ? "COALESCE(t.challenge_pnl, t.broker_pnl, t.projected_pnl, 0)"
            : "COALESCE(t.broker_pnl, t.projected_pnl, 0)";

        list($whereClause, $params) = self::buildFilterClause($filters, 't');

        $sql = "
            SELECT
                t.id, t.captured_at, t.closed_at,
                {$pnlExprEngine} AS broker_pnl, t.entry_price, t.actual_exit_price,
                t.actual_lots, t.broker_commission
            FROM trades t
            {$whereClause}
            ORDER BY t.captured_at ASC
        ";
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $trades = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $startingBalance = (float)($account['starting_balance'] ?? 0);
        $currentBalance = (float)($account['current_balance'] ?? $startingBalance);

        return self::computeMetricsFromTrades($trades, $startingBalance, $currentBalance);
    }

    /**
     * Same metrics as getAccountMetrics(), aggregated across ALL of a
     * user's active accounts instead of a single accountId. Colex coaches
     * on the trader's whole book — picking one "primary" account (e.g. the
     * largest balance) silently drops every trade sitting in the other
     * accounts, which is what produced the 0-trades / $0 P&L responses.
     */
    public static function getUserMetrics($userId, $filters = []) {
        $db = self::getConnection();

        // Optional Prop Firm / Live account-type scope (2026-08-20 Dashboard
        // premium-polish pass — additive, defaults to null/every account,
        // so every existing caller (Colex, TraderProfileService,
        // ColexHelpers) that doesn't pass this key is byte-identical to
        // before). 'prop_firm' | 'personal' | null.
        $accountType = $filters['accountType'] ?? null;
        $accounts = self::getUserAccounts($userId);
        if ($accountType !== null) {
            $accounts = array_values(array_filter($accounts, fn($a) => ($a['account_type'] ?? null) === $accountType));
        }

        // Currency conversion — Account Currency / FX Single Source of
        // Truth (2026-08-11, see CLAUDE.md). This is the one real
        // multi-currency boundary in this engine: every other method here
        // (getAccountMetrics(), getChallengeMetrics(), etc.) operates on a
        // single account and never mixes currencies. Here, a user's
        // accounts may genuinely carry different currencies, so every
        // balance/P&L figure is converted into the user's own declared
        // user_profiles.base_currency via CurrencyService before being
        // summed — never a bare `+=` across accounts that might not share
        // a unit. Inert (CurrencyService::convert() is a same-currency
        // identity, rate 1.0) for the common case where every account is
        // USD, which is still every real account in this database today.
        $baseCurrency = self::getUserBaseCurrency($userId);

        $startingBalance = 0;
        $currentBalance = 0;
        foreach ($accounts as $acct) {
            $acctCurrency = $acct['currency'] ?? 'USD';
            $startingBalance += self::convertOrRaw((float)($acct['starting_balance'] ?? 0), $acctCurrency, $baseCurrency);
            $currentBalance += self::convertOrRaw((float)($acct['current_balance'] ?? 0), $acctCurrency, $baseCurrency);
        }

        if (empty($accounts)) {
            $metrics = self::computeMetricsFromTrades([], 0, 0);
            $metrics['account_count'] = 0;
            $metrics['currency'] = $baseCurrency;
            return $metrics;
        }

        $filters['userId'] = $userId;
        list($whereClause, $params) = self::buildFilterClause($filters, 't');
        if ($accountType !== null) {
            $whereClause .= " AND ta.account_type = :fAccountType";
            $params[':fAccountType'] = $accountType;
        }

        // Each trade's effective P&L depends on its own account's type
        // (challenge_pnl for prop-firm, falling back to broker_pnl/
        // projected_pnl when challenge_pnl hasn't been backfilled;
        // broker_pnl/projected_pnl for personal) — resolved per row via
        // the join, same cascade as getAccountMetrics()/computeFourStageRaw().
        // ta.currency is selected so each row can be converted into
        // $baseCurrency below, before this shared trade list is handed to
        // computeMetricsFromTrades() — that function stays currency-
        // agnostic; all conversion happens here, once.
        $sql = "
            SELECT
                t.id, t.captured_at, t.closed_at,
                CASE WHEN ta.account_type = 'prop_firm' THEN COALESCE(t.challenge_pnl, t.broker_pnl, t.projected_pnl, 0) ELSE COALESCE(t.broker_pnl, t.projected_pnl, 0) END AS broker_pnl,
                COALESCE(ta.currency, 'USD') AS account_currency,
                t.entry_price, t.actual_exit_price, t.actual_lots, t.broker_commission
            FROM trades t
            LEFT JOIN trading_accounts ta ON ta.id = t.account_id
            {$whereClause}
            ORDER BY t.captured_at ASC
        ";

        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $trades = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($trades as &$t) {
            $t['broker_pnl'] = self::convertOrRaw((float)$t['broker_pnl'], $t['account_currency'] ?? 'USD', $baseCurrency);
        }
        unset($t);

        $metrics = self::computeMetricsFromTrades($trades, $startingBalance, $currentBalance);
        $metrics['account_count'] = count($accounts);
        $metrics['currency'] = $baseCurrency;
        return $metrics;
    }

    /**
     * Validation Workflow — canonical metrics for ONE validation strategy
     * version's closed, is_validation=1 trades, spanning every account the
     * user has traded it on (accounts are universal, not validation-
     * specific — see CLAUDE.md's Validation Workflow section). Single
     * source of truth for public/journal-validation.php ("Validation
     * Tracker") and the graduation -> main Strategy prefill step — no page
     * independently recomputes win rate/expectancy/profit factor/avg R/
     * rule adherence.
     *
     * win_rate/win_count/loss_count/breakeven_count/profit_factor/
     * expectancy/avg_winner/avg_loser/total_trades come straight from
     * getUserMetrics() (the exact same code every other page's metrics go
     * through) via the validationScope='only' escape hatch added to
     * buildFilterClause() above — zero duplicate formula.
     *
     * avg_r_multiple reuses computeFourStageRaw()'s own Stage 3
     * r_multiple field (via the public getFourStageRaw() wrapper) — the
     * same technique getFourStagePeriodSummary() already uses for its own
     * avg_r_multiple, just re-scoped to this one strategy's validation
     * trades instead of a date range. Trade count is hard-capped at 100 by
     * the Validation Workflow's own business rule, so looping per-trade
     * here is cheap.
     *
     * rule_adherence_pct re-derives TradingArchiveEngine::segmentMetrics()'s
     * exact "ticked_pct < 100 => skipped" rule (TradingArchiveEngine.php
     * ~line 575) — that logic is private and account/phase-segment-scoped,
     * not callable for a strategy-scoped query, so the same formula is
     * necessarily re-implemented here rather than reinvented.
     */
    public static function getValidationStrategyMetrics($userId, $strategyVersionId, $dateFrom = null, $dateTo = null, $validationScope = 'only') {
        $db = self::getConnection();

        $userMetricFilters = [
            'strategyVersionId' => $strategyVersionId,
            'validationScope' => $validationScope,
        ];
        if ($dateFrom) { $userMetricFilters['dateFrom'] = $dateFrom; }
        if ($dateTo) { $userMetricFilters['dateTo'] = $dateTo; }
        $metrics = self::getUserMetrics($userId, $userMetricFilters);

        // $validationScope='exclude' is used exactly once: the graduation
        // prefill step in strategy.php, called AFTER ValidationService::
        // graduate() has already flipped these trades' is_validation to 0
        // — by that point they're real (main-workflow) trades, so this
        // must read them the normal way, not require is_validation=1
        // (which would now find zero rows and silently zero out the
        // prefill). Everything below (Avg R loop, rule adherence) mirrors
        // that same scope so the whole return array stays internally
        // consistent.
        $tradeIsValidationSql = $validationScope === 'exclude'
            ? "(t.is_validation = 0 OR t.is_validation IS NULL)"
            : "t.is_validation = 1";

        // One query: every closed trade for this strategy in scope
        // (optionally date-bounded, for Validation Tracker's lightweight
        // Weekly/Monthly/Quarterly view — spec §22), left-joined to its
        // checklist result. Reused for both the Avg R loop (trade IDs) and
        // rule adherence (ticked_pct) below — same date window as the
        // getUserMetrics() call above, so every figure on a given period
        // view is scoped consistently.
        $dateSql = '';
        $dateParams = [];
        if ($dateFrom) { $dateSql .= " AND t.captured_at >= :dateFrom"; $dateParams[':dateFrom'] = $dateFrom; }
        if ($dateTo) { $dateSql .= " AND t.captured_at <= :dateTo"; $dateParams[':dateTo'] = $dateTo; }

        $stmt = $db->prepare(
            "SELECT t.id, cr.ticked_pct
             FROM trades t
             LEFT JOIN checklist_results cr ON cr.trade_lifecycle_id = t.trade_lifecycle_id
             WHERE t.user_id = :uid AND t.strategy_version_id = :svid
               AND {$tradeIsValidationSql} AND t.status = 'closed' AND t.deleted_at IS NULL{$dateSql}"
        );
        $stmt->execute(array_merge([':uid' => $userId, ':svid' => $strategyVersionId], $dateParams));
        $tradeRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $rMultiples = [];
        $adherentCount = 0;
        foreach ($tradeRows as $row) {
            $raw = self::getFourStageRaw($row['id']);
            if ($raw && isset($raw['stage3']['r_multiple']) && $raw['stage3']['r_multiple'] !== null) {
                $rMultiples[] = $raw['stage3']['r_multiple'];
            }
            // Same rule as TradingArchiveEngine::segmentMetrics()'s
            // checklist_skip_rate: no result, or < 100% ticked, = not adherent.
            if ($row['ticked_pct'] !== null && (float)$row['ticked_pct'] >= 100) {
                $adherentCount++;
            }
        }
        $metrics['avg_r_multiple'] = !empty($rMultiples) ? round(array_sum($rMultiples) / count($rMultiples), 2) : null;
        $metrics['rule_adherence_pct'] = !empty($tradeRows) ? round(($adherentCount / count($tradeRows)) * 100, 1) : null;

        return $metrics;
    }

    // Per-request memoization cache — reports.php's Boundary Analysis,
    // Accounts Needing Attention, and Most Severely Breached sections each
    // independently loop over the SAME set of a user's prop-firm accounts
    // calling this method (confirmed live: on a 79-account real account,
    // these three loops previously issued ~168 calls, ~6 queries each,
    // where only ~80 were ever actually distinct — the other ~88 were
    // re-computing an identical result for an account already resolved
    // moments earlier in the very same request). Safe to cache: this is a
    // pure read (no write anywhere in this method or anything it calls),
    // and nothing in this app mutates a trade/account mid-request, so a
    // second call for the same $accountId within one request is
    // guaranteed to return the same result as the first. Safe across
    // requests too, in the sense that it can never leak stale data to a
    // LATER request — this is a static property, reset to empty at the
    // start of every new PHP process, matching this app's established
    // request-per-process deployment model (see ValidationService's own
    // $profileCache for the same reasoning, applied here for the first
    // time to a per-account rather than per-user key). Verified live
    // (2026-08-18 Reports performance pass): cut this account's real
    // Reports page-render time from ~1.44s to ~0.97s with zero change in
    // any rendered number — confirmed via a byte-for-byte diff of the
    // rendered Boundary Analysis / Accounts Needing Attention / Most
    // Severely Breached sections before and after.
    private static $challengeMetricsCache = [];

    // Per-request memoization caches for computeFourStageRaw()'s two
    // per-trade lookups (trading_accounts by account_id, strategy_versions
    // JOIN strategies by strategy_version_id) — see that method's own
    // comments at each cache's use site for the full reasoning. Same
    // request-scoped-static pattern as $challengeMetricsCache above.
    private static $fourStageAccountCache = [];
    private static $fourStageStrategyVersionCache = [];

    public static function getChallengeMetrics($accountId) {
        if (array_key_exists($accountId, self::$challengeMetricsCache)) {
            return self::$challengeMetricsCache[$accountId];
        }
        $result = self::computeChallengeMetricsUncached($accountId);
        self::$challengeMetricsCache[$accountId] = $result;
        return $result;
    }

    // Real body, unchanged line-for-line from before the memoization cache
    // above was added — only ever called by getChallengeMetrics() itself,
    // exactly once per distinct $accountId per request.
    private static function computeChallengeMetricsUncached($accountId) {
        $db = self::getConnection();

        // Get challenge account data
        $stmt = $db->prepare("
            SELECT ca.*,
                   ta.starting_balance,
                   ta.current_balance,
                   ta.account_type,
                   ta.lifecycle_state AS ta_lifecycle_state
            FROM challenge_accounts ca
            JOIN trading_accounts ta ON ca.account_id = ta.id
            WHERE ca.account_id = :accountId
        ");
        $stmt->execute([':accountId' => $accountId]);
        $challenge = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$challenge) {
            return ['error' => 'Challenge account not found'];
        }

        // Get account metrics for calculations
        $accountMetrics = self::getAccountMetrics($accountId);
        if (isset($accountMetrics['error'])) {
            return ['error' => 'Could not calculate account metrics'];
        }

        // ── SINGLE SOURCE OF TRUTH CONSOLIDATION (per
        // docs/architecture/SINGLE_SOURCE_OF_TRUTH_MIGRATION_PLAN.md,
        // Wave 2/3.6) ──
        //
        // Every phase/balance/drawdown/pass/breach field this method
        // returns used to be computed by a second, independently-
        // maintained copy of the exact same formula PropFirmRuleEngine::
        // compute() already owns (phase derivation from lifecycle_state,
        // the rules-JSON threshold cascade, the phase-reset-aware
        // balance via JourneySegmentation, the breach-level/daily-loss
        // math) — see git history for the full ~250-line duplicate this
        // replaced. That duplication was live-dangerous specifically
        // because THIS method is the one dashboard.php's resolve_trade_id
        // (the sole lifecycle mutator — the code that actually caps
        // challenge_pnl and writes breach/pass) and drafts.php's live
        // pre-check both call; PropFirmRuleEngine was already the
        // canonical, already-verified engine for every read-only display
        // consumer (Calculator, Trading Center, Journal, Trading Archive,
        // Colex), but the real money-mutating write path still ran its
        // own separate copy. Any future change to the boundary formula
        // (e.g. supporting a Trailing Maximum Loss Model)
        // would otherwise have had to be built twice, in two places, with
        // a real risk of the two silently disagreeing — exactly the
        // failure mode CLAUDE.md's "Single Source of Truth Rule" incident
        // already documents happening once before with account balance.
        //
        // This method is now a thin, byte-verified wrapper (see
        // scripts/verify_prop_firm_rule_engine.php's golden-value log,
        // re-run after this consolidation with zero field-level
        // differences across every real prop-firm account): it still owns
        // account_metrics/challenge_type/status/challenge_data (data
        // PropFirmRuleEngine was never scoped to own — see that class's
        // own header comment), but current_stage/phase_balance/phase/
        // distance_to_pass/daily_drawdown/overall_drawdown/
        // distance_to_breach/excess_amounts are computed exactly once, by
        // PropFirmRuleEngine::compute().
        $pfr = PropFirmRuleEngine::compute($accountId);
        if (isset($pfr['error'])) {
            // Should be unreachable in practice — the query above already
            // confirmed this exact challenge_accounts row exists — but
            // fail safely rather than propagate a null-shaped result.
            return ['error' => 'Could not calculate account metrics'];
        }

        return [
            'account_id' => $accountId,
            'current_stage' => $pfr['current_stage'],
            // Phase-reset-aware current balance — the ONE correct
            // "current balance" for breach/pass boundary math on a
            // prop-firm account still mid-evaluation: this phase's own
            // fresh starting_balance plus only this phase's own real
            // closed trades' effective P&L. Deliberately NOT the same
            // value as account_metrics.current_balance below (that's the
            // general-purpose, whole-account trading_accounts.
            // current_balance — correct for Reports/Dashboard/Colex's
            // broader "lifetime ledger" purposes, but wrong for THIS
            // function's own phase-scoped boundary calculations). Any
            // caller that needs the trader's real current balance for a
            // breach/pass comparison (dashboard.php's resolve_trade_id,
            // drafts.php's live pre-check) must read THIS field, not
            // account_metrics.current_balance.
            'phase_balance' => $pfr['phase_balance'],
            'challenge_type' => $challenge['challenge_type'] ?? 'standard',
            'status' => $challenge['lifecycle_state'] ?? 'active',

            // Phase information — PropFirmRuleEngine::resolveRules()'s
            // exact cascade (phase-specific key -> flat key -> default).
            'phase' => $pfr['phase'],

            // Distance to pass
            'distance_to_pass' => $pfr['distance_to_pass'],

            // Daily drawdown
            'daily_drawdown' => $pfr['daily_drawdown'],

            // Overall drawdown. max_drawdown_percentage is the one field
            // in this sub-array PropFirmRuleEngine deliberately does not
            // own — it's accountMetrics' PEAK-EQUITY-relative stat
            // ("worst this account has ever drawn down from its own peak
            // equity"), a genuinely different metric from the starting-
            // balance-anchored breach/limit math every other field here
            // answers, so it's merged in from $accountMetrics exactly as
            // it always was.
            'overall_drawdown' => $pfr['overall_drawdown'] + [
                'max_drawdown_percentage' => round(abs($accountMetrics['max_drawdown_pct'] ?? 0) * 100, 2),
            ],

            // Distance to breach
            'distance_to_breach' => $pfr['distance_to_breach'],

            // Excess amounts
            'excess_amounts' => $pfr['excess_amounts'],

            // Reference to full account metrics
            'account_metrics' => $accountMetrics,

            // Raw challenge data
            'challenge_data' => [
                'started_at' => $challenge['started_at'] ?? null,
                'expires_at' => $challenge['expires_at'] ?? null,
                'lifecycle_state' => $challenge['lifecycle_state'] ?? 'active'
            ]
        ];
    }
    
    // ========================================================================
    // LAYER 2: LIFECYCLE JOURNEY
    // ========================================================================
    
    public static function getLifecycleJourney($accountId) {
        $db = self::getConnection();

        // Prop-firm accounts are measured against challenge_pnl, falling
        // back to broker_pnl/projected_pnl when challenge_pnl hasn't been
        // backfilled — same cascade as getAccountMetrics()/reports.php.
        $acctStmt = $db->prepare("SELECT account_type FROM trading_accounts WHERE id = :accountId");
        $acctStmt->execute([':accountId' => $accountId]);
        $acctRow = $acctStmt->fetch(PDO::FETCH_ASSOC);
        $pnlColumn = ($acctRow['account_type'] ?? '') === 'prop_firm'
            ? "COALESCE(challenge_pnl, broker_pnl, projected_pnl, 0)"
            : "COALESCE(broker_pnl, projected_pnl, 0)";

        // Get lifecycle events
        $stmt = $db->prepare("
            SELECT * FROM lifecycle_events  
            WHERE account_id = :accountId 
            ORDER BY occurred_at ASC
        ");
        $stmt->execute([':accountId' => $accountId]);
        $events = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        if (empty($events)) {
            // No lifecycle events, construct from trades
            return self::constructLifecycleFromTrades($accountId);
        }
        
        $journey = [
            'account_id' => $accountId,
            'total_phases' => count($events),
            'phases' => [],
            'summary' => [
                'total_days' => 0,
                'total_trades' => 0,
                'total_profit' => 0,
                'lifetime_return_pct' => 0,
                'lifetime_drawdown_pct' => 0
            ]
        ];
        
        $phaseCount = count($events);
        $totalTrades = 0;
        $totalProfit = 0;
        
        foreach ($events as $index => $event) {
            $eventDate = $event['occurred_at'];
            $eventType = $event['event_type'] ?? 'unknown';
            $metadata = !empty($event['event_metadata']) ? json_decode($event['event_metadata'], true) : [];
            
            // Determine date range for this phase
            $endDate = ($index < $phaseCount - 1) 
                ? $events[$index + 1]['occurred_at'] 
                : date('Y-m-d H:i:s');
            
            // Get trades for this phase
            $stmt = $db->prepare("
                SELECT 
                    COUNT(*) as trade_count,
                    SUM({$pnlColumn}) as total_pnl,
                    MIN(CASE WHEN {$pnlColumn} < 0 THEN {$pnlColumn} END) as worst_trade,
                    MAX(CASE WHEN {$pnlColumn} > 0 THEN {$pnlColumn} END) as best_trade,
                    AVG({$pnlColumn}) as avg_pnl,
                    SUM(CASE WHEN {$pnlColumn} > 0 THEN 1 ELSE 0 END) as winning_trades,
                    SUM(CASE WHEN {$pnlColumn} < 0 THEN 1 ELSE 0 END) as losing_trades
                FROM trades
                WHERE account_id = :accountId
                AND status = 'closed'
                AND (is_validation = 0 OR is_validation IS NULL)
                AND captured_at >= :startDate
                AND captured_at < :endDate
            ");
            $stmt->execute([
                ':accountId' => $accountId,
                ':startDate' => $eventDate,
                ':endDate' => $endDate
            ]);
            $tradeStats = $stmt->fetch(PDO::FETCH_ASSOC);
            
            $tradeCount = (int)($tradeStats['trade_count'] ?? 0);
            $phaseProfit = (float)($tradeStats['total_pnl'] ?? 0);
            $winningTrades = (int)($tradeStats['winning_trades'] ?? 0);
            $losingTrades = (int)($tradeStats['losing_trades'] ?? 0);
            
            // Calculate phase duration in days
            $phaseDurationDays = round((strtotime($endDate) - strtotime($eventDate)) / (60 * 60 * 24), 1);
            
            // Calculate drawdown for this phase
            $phaseDrawdown = 0;
            if ($tradeCount > 0) {
                $stmt = $db->prepare("
                    SELECT {$pnlColumn} AS broker_pnl
                    FROM trades
                    WHERE account_id = :accountId
                    AND status = 'closed'
                    AND (is_validation = 0 OR is_validation IS NULL)
                    AND captured_at >= :startDate
                    AND captured_at < :endDate
                    ORDER BY captured_at ASC
                ");
                $stmt->execute([
                    ':accountId' => $accountId,
                    ':startDate' => $eventDate,
                    ':endDate' => $endDate
                ]);
                $phaseTrades = $stmt->fetchAll(PDO::FETCH_COLUMN);
                
                // Calculate running drawdown for this phase
                $peak = 0;
                $maxDrawdown = 0;
                foreach ($phaseTrades as $pnl) {
                    $peak = max($peak, $pnl);
                    $drawdown = $pnl - $peak;
                    $maxDrawdown = min($maxDrawdown, $drawdown);
                }
                $phaseDrawdown = abs($maxDrawdown);
            }
            
            $phaseData = [
                'phase_number' => $index + 1,
                'phase_name' => $metadata['phase_name'] ?? ucfirst(str_replace('_', ' ', $eventType)),
                'phase_type' => $eventType,
                'start_date' => $eventDate,
                'end_date' => $endDate,
                'duration_days' => $phaseDurationDays,
                'trades' => [
                    'total' => $tradeCount,
                    'winning' => $winningTrades,
                    'losing' => $losingTrades,
                    'breakeven' => $tradeCount - $winningTrades - $losingTrades,
                    'win_rate' => $tradeCount > 0 ? round(($winningTrades / $tradeCount) * 100, 1) : 0
                ],
                'profit' => [
                    'total' => round($phaseProfit, 2),
                    'average_per_trade' => $tradeCount > 0 ? round($phaseProfit / $tradeCount, 2) : 0,
                    'best_trade' => isset($tradeStats['best_trade']) && $tradeStats['best_trade'] !== null ? round((float)$tradeStats['best_trade'], 2) : null,
                    'worst_trade' => isset($tradeStats['worst_trade']) && $tradeStats['worst_trade'] !== null ? round((float)$tradeStats['worst_trade'], 2) : null
                ],
                'drawdown' => round($phaseDrawdown, 2),
                'metadata' => $metadata
            ];
            
            $journey['phases'][] = $phaseData;
            $totalTrades += $tradeCount;
            $totalProfit += $phaseProfit;
        }
        
        // Calculate summary
        if (!empty($journey['phases'])) {
            $firstDate = $journey['phases'][0]['start_date'];
            $lastDate = $journey['phases'][count($journey['phases']) - 1]['end_date'];
            $journey['summary']['total_days'] = round((strtotime($lastDate) - strtotime($firstDate)) / (60 * 60 * 24), 1);
        }
        
        $journey['summary']['total_trades'] = $totalTrades;
        $journey['summary']['total_profit'] = round($totalProfit, 2);
        
        // Get overall account metrics for lifetime stats
        $accountMetrics = self::getAccountMetrics($accountId);
        if (!isset($accountMetrics['error'])) {
            $journey['summary']['lifetime_return_pct'] = round($accountMetrics['return_pct'] * 100, 2);
            $journey['summary']['lifetime_drawdown_pct'] = round($accountMetrics['max_drawdown_pct'] * 100, 2);
        }
        
        return $journey;
    }
    
    private static function constructLifecycleFromTrades($accountId) {
        $db = self::getConnection();

        // Prop-firm accounts are measured against challenge_pnl, falling
        // back to broker_pnl/projected_pnl when challenge_pnl hasn't been
        // backfilled — same cascade as getAccountMetrics()/getLifecycleJourney().
        $acctStmt = $db->prepare("SELECT account_type FROM trading_accounts WHERE id = :accountId");
        $acctStmt->execute([':accountId' => $accountId]);
        $acctRow = $acctStmt->fetch(PDO::FETCH_ASSOC);
        $pnlColumn = ($acctRow['account_type'] ?? '') === 'prop_firm'
            ? "COALESCE(challenge_pnl, broker_pnl, projected_pnl, 0)"
            : "COALESCE(broker_pnl, projected_pnl, 0)";

        // Group trades by month as phases when no lifecycle events exist
        $stmt = $db->prepare("
            SELECT 
                DATE_FORMAT(captured_at, '%Y-%m') as phase_period,
                MIN(captured_at) as phase_start_date,
                MAX(captured_at) as phase_end_date,
                COUNT(*) as trade_count,
                SUM({$pnlColumn}) as total_pnl,
                SUM(CASE WHEN {$pnlColumn} > 0 THEN 1 ELSE 0 END) as winning_trades,
                SUM(CASE WHEN {$pnlColumn} < 0 THEN 1 ELSE 0 END) as losing_trades,
                MIN(CASE WHEN {$pnlColumn} < 0 THEN {$pnlColumn} END) as worst_trade,
                MAX(CASE WHEN {$pnlColumn} > 0 THEN {$pnlColumn} END) as best_trade
            FROM trades
            WHERE account_id = :accountId
            AND status = 'closed'
            AND (is_validation = 0 OR is_validation IS NULL)
            GROUP BY DATE_FORMAT(captured_at, '%Y-%m')
            ORDER BY phase_start_date ASC
        ");
        $stmt->execute([':accountId' => $accountId]);
        $periods = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $phases = [];
        $totalTrades = 0;
        $totalProfit = 0;
        
        foreach ($periods as $index => $period) {
            $tradeCount = (int)($period['trade_count'] ?? 0);
            $phaseProfit = (float)($period['total_pnl'] ?? 0);
            $winningTrades = (int)($period['winning_trades'] ?? 0);
            $losingTrades = (int)($period['losing_trades'] ?? 0);
            
            $phaseDurationDays = round((strtotime($period['phase_end_date']) - strtotime($period['phase_start_date'])) / (60 * 60 * 24), 1);
            
            $phases[] = [
                'phase_number' => $index + 1,
                'phase_name' => 'Trading Period ' . ($index + 1),
                'phase_type' => 'trading_period',
                'start_date' => $period['phase_start_date'],
                'end_date' => $period['phase_end_date'],
                'duration_days' => $phaseDurationDays,
                'trades' => [
                    'total' => $tradeCount,
                    'winning' => $winningTrades,
                    'losing' => $losingTrades,
                    'breakeven' => $tradeCount - $winningTrades - $losingTrades,
                    'win_rate' => $tradeCount > 0 ? round(($winningTrades / $tradeCount) * 100, 1) : 0
                ],
                'profit' => [
                    'total' => round($phaseProfit, 2),
                    'average_per_trade' => $tradeCount > 0 ? round($phaseProfit / $tradeCount, 2) : 0,
                    'best_trade' => isset($period['best_trade']) && $period['best_trade'] !== null ? round((float)$period['best_trade'], 2) : null,
                    'worst_trade' => isset($period['worst_trade']) && $period['worst_trade'] !== null ? round((float)$period['worst_trade'], 2) : null
                ],
                'drawdown' => 0,
                'metadata' => [
                    'period' => $period['phase_period'],
                    'auto_generated' => true
                ]
            ];
            
            $totalTrades += $tradeCount;
            $totalProfit += $phaseProfit;
        }
        
        $accountMetrics = self::getAccountMetrics($accountId);
        
        return [
            'account_id' => $accountId,
            'total_phases' => count($phases),
            'phases' => $phases,
            'summary' => [
                'total_days' => !empty($phases) ? round((strtotime(end($phases)['end_date']) - strtotime($phases[0]['start_date'])) / (60 * 60 * 24), 1) : 0,
                'total_trades' => $totalTrades,
                'total_profit' => round($totalProfit, 2),
                'lifetime_return_pct' => isset($accountMetrics['error']) ? 0 : round($accountMetrics['return_pct'] * 100, 2),
                'lifetime_drawdown_pct' => isset($accountMetrics['error']) ? 0 : round($accountMetrics['max_drawdown_pct'] * 100, 2)
            ],
            'note' => 'Phases auto-generated from trading periods (no lifecycle events found)'
        ];
    }
    
    // ========================================================================
    // LAYER 7: STRATEGY & EXECUTION - FOUR STAGE COMPARISON
    // ========================================================================
    
    /**
     * RAW numeric computation for the true 4-Stage architecture — no
     * display formatting. This is the actual single source of truth;
     * getFourStageComparison() (per-trade display) and
     * getFourStagePeriodSummary() (period aggregate for Reports) both
     * call this and never duplicate the calculation logic themselves.
     */
    // $preloadedTrade — performance fix (2026-08-18): getFourStagePeriodSummary()
    // below calls this once per trade in a reporting period (up to hundreds
    // of trades for a real account's Monthly/Quarterly/Annual view or
    // Trader Profile's Growth Timeline), and until this fix each call
    // re-queried "SELECT * FROM trades WHERE id = :tradeId" one row at a
    // time even though the caller already knows the full list of trade IDs
    // up front and could fetch them all in a single batched query. Optional
    // and purely additive — every other caller (getFourStageRaw(), the
    // public single-trade wrapper Journal/Colex use) passes nothing and
    // gets byte-identical behavior to before: a fresh per-ID SELECT.
    private static function computeFourStageRaw($tradeId, bool $allowValidation = false, ?array $preloadedTrade = null) {
        $db = self::getConnection();

        if ($preloadedTrade !== null) {
            $trade = $preloadedTrade;
        } else {
            $stmt = $db->prepare("SELECT * FROM trades WHERE id = :tradeId");
            $stmt->execute([':tradeId' => $tradeId]);
            $trade = $stmt->fetch(PDO::FETCH_ASSOC);
        }

        if (!$trade || (!empty($trade['is_validation']) && !$allowValidation)) {
            // A validation-mode trade has a completely real 4-Stage story
            // of its own — it just must never leak into a MAIN-workflow
            // view (Reports' period aggregates, Colex's context cards,
            // both of which call this with the default $allowValidation
            // = false and have already separately scoped their own trade
            // sets to the main workflow). $allowValidation = true is only
            // ever passed by a caller that has already verified, itself,
            // that this specific trade belongs to the CURRENT workflow
            // (see getFourStageRaw()'s own doc comment) — never trusted
            // blindly from here.
            return ['error' => 'Trade not found'];
        }

        $direction = $trade['direction'] ?? 'long';
        $instrument = $trade['instrument_display'] ?? $trade['instrument'] ?? 'Unknown';

        // Account type — needed for the P&L rule (challenge_pnl for prop,
        // broker_pnl for personal) applied consistently across Stage 3.
        //
        // Performance fix (2026-08-18): memoized per-request, keyed by
        // account_id. computeFourStageRaw() is called once per trade by
        // getFourStagePeriodSummary()'s loop — a period can easily span
        // hundreds of trades sharing a much smaller set of accounts
        // (confirmed live: 269 trades across a 2-month Trader Profile
        // Growth Timeline window, only 83 distinct accounts), so this
        // re-ran the identical query for the same account dozens of times
        // in a single request. Safe to cache: account_type/current_balance
        // don't change mid-request (nothing in this read-only path writes
        // to trading_accounts), so a second lookup for the same account_id
        // is guaranteed to return what the first one already returned.
        $tradeAccountType = null;
        $accountCurrentBalance = null;
        if (!empty($trade['account_id'])) {
            $acctId = $trade['account_id'];
            if (!array_key_exists($acctId, self::$fourStageAccountCache)) {
                $acctStmt = $db->prepare("SELECT account_type, current_balance FROM trading_accounts WHERE id = :accountId");
                $acctStmt->execute([':accountId' => $acctId]);
                self::$fourStageAccountCache[$acctId] = $acctStmt->fetch(PDO::FETCH_ASSOC);
            }
            $acctRow = self::$fourStageAccountCache[$acctId];
            $tradeAccountType = $acctRow['account_type'] ?? null;
            $accountCurrentBalance = isset($acctRow['current_balance']) ? (float)$acctRow['current_balance'] : null;
        }

        // ════════════════════════════════════════════════════════════
        // STAGE 1: STRATEGY EDGE SNAPSHOT — simulated perfect execution.
        // "If the trader had followed the strategy exactly as designed."
        // Entry/SL are held constant from the actual trade (market-
        // structure/setup facts, not something a risk% overrides).
        // Position size, max loss, max profit, and TP are recomputed
        // from the STRATEGY's declared risk% and R:R — not whatever was
        // actually used — by scaling the trade's own already-computed
        // real numbers (never inventing pip/contract-value formulas).
        // This never adopts the trader's actual deviations; if the
        // trade departed from the declared edge, that's explained in
        // consistency_notes, not silently absorbed into this stage.
        // ════════════════════════════════════════════════════════════
        $stage1 = ['available' => false];
        $strategyVersion = null;
        if (!empty($trade['strategy_version_id'])) {
            // Performance fix (2026-08-18): memoized per-request, keyed by
            // strategy_version_id — same reasoning as the account cache
            // above, and a much bigger win in practice: a trader typically
            // has a handful of strategies but hundreds of trades against
            // each (confirmed live: the same 269-trade window above used
            // only 4 distinct strategy_version_ids), so this query was
            // being re-run for the same, unchanging strategy declaration
            // over and over. trading_style joined in from strategies (not
            // on strategy_versions itself) — needed for the 4-Stage
            // Summary's "Timeframe" row (Row 6), which reuses the exact
            // style-bucket concept already established by
            // getStyleDriftSignal() rather than a new one.
            $svId = $trade['strategy_version_id'];
            if (!array_key_exists($svId, self::$fourStageStrategyVersionCache)) {
                $svStmt = $db->prepare("SELECT sv.max_risk_per_trade_pct, sv.backtested_avg_rr, sv.backtested_win_rate, sv.session_preference, sv.instruments_traded, sv.timeframes_traded, s.trading_style FROM strategy_versions sv JOIN strategies s ON s.id = sv.strategy_id WHERE sv.id = :svId");
                $svStmt->execute([':svId' => $svId]);
                self::$fourStageStrategyVersionCache[$svId] = $svStmt->fetch(PDO::FETCH_ASSOC);
            }
            $strategyVersion = self::$fourStageStrategyVersionCache[$svId];
        }

        if ($strategyVersion) {
            $declaredRiskPct = isset($strategyVersion['max_risk_per_trade_pct']) ? (float)$strategyVersion['max_risk_per_trade_pct'] : null;
            $declaredRr = isset($strategyVersion['backtested_avg_rr']) ? (float)$strategyVersion['backtested_avg_rr'] : null;
            $entryPrice = isset($trade['entry_price']) ? (float)$trade['entry_price'] : null;
            $slPrice = isset($trade['sl_price']) ? (float)$trade['sl_price'] : null;
            $balanceAtEntry = isset($trade['balance_at_entry']) && $trade['balance_at_entry'] !== null
                ? (float)$trade['balance_at_entry']
                : $accountCurrentBalance;

            $declaredMaxLoss = ($declaredRiskPct !== null && $balanceAtEntry !== null)
                ? round($balanceAtEntry * $declaredRiskPct / 100, 2) : null;
            $declaredMaxProfit = ($declaredMaxLoss !== null && $declaredRr !== null)
                ? round($declaredMaxLoss * $declaredRr, 2) : null;

            // Scale actual position size by the ratio of declared to
            // actual dollar risk — sidesteps needing instrument-specific
            // pip/contract-value formulas entirely, using only a ratio
            // of two already-computed real numbers on this trade.
            $actualMaxLoss = isset($trade['max_potential_loss']) ? (float)$trade['max_potential_loss'] : null;
            $actualLots = isset($trade['lots_per_position']) ? (float)$trade['lots_per_position'] : (isset($trade['actual_lots']) ? (float)$trade['actual_lots'] : null);
            $declaredLots = null;
            $sizingNote = null;
            if ($declaredMaxLoss !== null && $actualMaxLoss !== null && $actualMaxLoss > 0 && $actualLots !== null) {
                $declaredLots = round($actualLots * ($declaredMaxLoss / $actualMaxLoss), 4);
            } else {
                $sizingNote = 'Position size could not be simulated. This trade has no recorded max potential loss to scale from.';
            }

            $declaredTpPrice = null;
            if ($entryPrice !== null && $slPrice !== null && $declaredRr !== null) {
                $distance = abs($entryPrice - $slPrice);
                $declaredTpPrice = $direction === 'long'
                    ? round($entryPrice + ($distance * $declaredRr), 5)
                    : round($entryPrice - ($distance * $declaredRr), 5);
            }

            // Consistency check — did the ACTUAL trade (Stage 2) already
            // deviate from the declared edge? If so, explain why this
            // stage doesn't reflect that deviation, per the confirmed
            // design ("the Strategy Edge Snapshot should not adopt those
            // inconsistent values").
            $consistencyNotes = [];
            $actualRiskPct = isset($trade['risk_pct']) ? (float)$trade['risk_pct'] : null;
            // Tolerance-based, not a flat +0.01pp buffer (see CALC_TOLERANCE_RATIO's
            // doc comment above) — a calculator producing 1.04% for a 1.00%-
            // declared strategy is legitimate lot-step quantization, not
            // over-risking, and must not trip this note/the Strategy
            // Adherence stat below.
            $riskOverage = null;
            $contractConstrained = false;
            $riskTolerance = self::calculatorRiskTolerance($declaredRiskPct, $trade['instrument'] ?? null);
            // Structural check FIRST (item 7, 2026-07-27): before ever
            // calling a trade "Over-Risk," ask whether the declared target
            // was even mathematically reachable on this instrument, at
            // this account balance, at the broker's own minimum lot size.
            // If not, this is a Contract-Constrained trade, not a
            // behavioral over-risk — the two must never be mixed.
            $minAchievableRiskPct = self::minAchievableRiskPct($trade['instrument'] ?? null, $entryPrice, $slPrice, $balanceAtEntry);
            if ($declaredRiskPct !== null && $minAchievableRiskPct !== null && $riskTolerance !== null && $minAchievableRiskPct > $declaredRiskPct + $riskTolerance) {
                $contractConstrained = true;
                $consistencyNotes[] = "This instrument's broker-minimum lot size, at this account's balance, already risks at least " . round($minAchievableRiskPct, 2) . "%, above the declared {$declaredRiskPct}% strategy limit. No valid lot size existed to hit the declared target on this trade; this reflects an instrument/contract constraint, not over-risking.";
            } elseif ($declaredRiskPct !== null && $actualRiskPct !== null && $riskTolerance !== null && $actualRiskPct > $declaredRiskPct + $riskTolerance) {
                $riskOverage = $actualRiskPct - $declaredRiskPct;
                $consistencyNotes[] = "The submitted trade risked {$actualRiskPct}%, above the declared {$declaredRiskPct}% strategy limit (tolerance: ±" . round($riskTolerance, 2) . "pp for normal lot-size rounding, calibrated to this instrument's own class). This snapshot continues using the declared {$declaredRiskPct}% to preserve the original edge, rather than reflecting the over-risked trade.";
            }
            $actualRr = isset($trade['risk_reward_tp1']) ? (float)$trade['risk_reward_tp1'] : null;
            $rrMismatch = null;
            if ($declaredRr !== null && $actualRr !== null && abs($actualRr - $declaredRr) > 0.15) {
                $rrMismatch = $actualRr - $declaredRr;
                $consistencyNotes[] = "The submitted Entry, Stop Loss, and Take Profit imply a " . number_format($actualRr, 2) . ":1 reward ratio, which no longer matches the declared {$declaredRr}:1 edge. This snapshot continues calculating from the declared {$declaredRr}:1 ratio rather than adopting the inconsistent one.";
            }

            // Declared session(s) — strategy_versions.session_preference is a
            // JSON array of strings; joined for display, not chosen from.
            $declaredSessions = [];
            if (!empty($strategyVersion['session_preference'])) {
                $decodedSessions = json_decode($strategyVersion['session_preference'], true);
                if (is_array($decodedSessions)) $declaredSessions = array_values(array_filter($decodedSessions));
            }
            // Session Drift input — did the trade's ACTUAL session (a real,
            // per-trade field, distinct from the strategy's declared
            // preference list above) fall inside the strategy's own declared
            // session_preference? Null (not false) whenever either side is
            // missing, so the period aggregate's denominator only counts
            // trades where this was actually checkable. Case/whitespace-
            // insensitive since both sides are free-text-ish labels.
            $sessionAdherent = null;
            if (!empty($declaredSessions) && !empty($trade['session'])) {
                $normDeclaredSessions = array_map(fn($s) => strtolower(trim((string)$s)), $declaredSessions);
                $sessionAdherent = in_array(strtolower(trim((string)$trade['session'])), $normDeclaredSessions, true);
            }
            // Instrument Drift input — did the trade's instrument fall inside
            // the strategy's own declared instruments_traded list? Same
            // normalization (strip separators, uppercase) already used by
            // reports.php's Asset Class Edge Detection card, duplicated here
            // as a small private helper so engine.php stays self-contained.
            $declaredInstruments = [];
            if (!empty($strategyVersion['instruments_traded'])) {
                $decodedInstruments = json_decode($strategyVersion['instruments_traded'], true);
                if (is_array($decodedInstruments)) $declaredInstruments = array_values(array_filter($decodedInstruments));
            }
            $instrumentAdherent = null;
            if (!empty($declaredInstruments)) {
                $normDeclaredInstruments = array_map([self::class, 'normalizeInstrumentSymbol'], $declaredInstruments);
                $instrumentAdherent = in_array(self::normalizeInstrumentSymbol($trade['instrument'] ?? ''), $normDeclaredInstruments, true);
            }

            // Declared Timeframe (Row 6, Stage 1) — 2026-07-27 item 2. Real
            // declared data now that Strategy Builder captures a mandatory
            // timeframes_traded list (mirrors the declaredSessions pattern
            // above exactly — a strategy-level declaration, joined for
            // display, not a per-trade actual value).
            $declaredTimeframes = [];
            if (!empty($strategyVersion['timeframes_traded'])) {
                $decodedTimeframes = json_decode($strategyVersion['timeframes_traded'], true);
                if (is_array($decodedTimeframes)) $declaredTimeframes = array_values(array_filter($decodedTimeframes));
            }

            // Pip-equivalent of max loss/profit. Entry/SL are held constant
            // between Stage 1 and the real trade (see the note at the top of
            // this method), so the SL pip distance is identical — reused
            // directly from the trade's own sl_distance_pips rather than
            // re-derived, and max_profit_pips scales it by the same
            // declared R:R already used for the $ figure above. Null
            // whenever sl_distance_pips itself is null (trades saved before
            // the calculator's own pip computation started being persisted
            // — see api/trades/draft.php) rather than guessed at.
            $slPipsStage1 = (isset($trade['sl_distance_pips']) && $trade['sl_distance_pips'] !== null && $trade['sl_distance_pips'] !== '')
                ? (float)$trade['sl_distance_pips'] : null;
            $declaredMaxProfitPips = ($slPipsStage1 !== null && $declaredRr !== null) ? round($slPipsStage1 * $declaredRr, 1) : null;

            $stage1 = [
                'available' => true,
                'instrument' => $instrument, 'direction' => $direction,
                'session' => !empty($declaredSessions) ? implode(', ', $declaredSessions) : null,
                'timeframes' => !empty($declaredTimeframes) ? implode(', ', $declaredTimeframes) : null,
                'trading_style' => $strategyVersion['trading_style'] ?? null,
                'account_balance' => $balanceAtEntry,
                'entry_price' => $entryPrice, 'sl_price' => $slPrice, 'tp_price' => $declaredTpPrice,
                'risk_pct' => $declaredRiskPct, 'risk_reward' => $declaredRr,
                'lots' => $declaredLots, 'sizing_note' => $sizingNote,
                'max_loss' => $declaredMaxLoss, 'max_profit' => $declaredMaxProfit,
                'max_loss_pips' => $slPipsStage1, 'max_profit_pips' => $declaredMaxProfitPips,
                'risk_overage' => $riskOverage, 'rr_mismatch' => $rrMismatch,
                'contract_constrained' => $contractConstrained,
                'min_achievable_risk_pct' => $minAchievableRiskPct,
                'consistency_notes' => $consistencyNotes,
                'session_adherent' => $sessionAdherent, 'instrument_adherent' => $instrumentAdherent,
                // Journal's single-trade 4-Stage Lifecycle redesign
                // (2026-08-03) needs the FULL declared universe (a strategy
                // may declare 2 or 100+ instruments/sessions/timeframes) to
                // render an expandable "+N more" list at Stage 1, distinct
                // from every other stage's single actual value for the same
                // metric. Additive only — 'session'/'timeframes' above stay
                // exactly as every existing consumer (Reports' period
                // aggregate, Colex) already reads them: joined strings.
                'declared_instruments_list' => $declaredInstruments,
                'declared_sessions_list' => $declaredSessions,
                'declared_timeframes_list' => $declaredTimeframes,
            ];
        }

        // ════════════════════════════════════════════════════════════
        // STAGE 2: CALCULATOR INTENT — what the trader actually entered
        // and intended to execute, sealed at save time. Can legitimately
        // differ from Stage 1; that's the point of comparing them.
        // ════════════════════════════════════════════════════════════
        $s2EntryPrice = isset($trade['entry_price']) ? (float)$trade['entry_price'] : null;
        $s2SlPrice = isset($trade['sl_price']) ? (float)$trade['sl_price'] : null;
        $s2MaxProfit = isset($trade['max_potential_profit']) ? (float)$trade['max_potential_profit'] : null;
        $s2SlPips = (isset($trade['sl_distance_pips']) && $trade['sl_distance_pips'] !== null && $trade['sl_distance_pips'] !== '')
            ? (float)$trade['sl_distance_pips'] : null;

        $stage2 = [
            'instrument' => $instrument, 'direction' => $direction,
            'session' => $trade['session'] ?? null,
            // Planned Timeframe (Row 6, Stage 2) — 2026-07-27 item 2. The
            // Calculator's own single-trade planned timeframe, mirroring
            // how 'session' above is this trade's own value (not a
            // strategy-level declared list, unlike Stage 1's 'timeframes').
            'timeframe' => $trade['planned_timeframe'] ?? null,
            'entry_price' => $s2EntryPrice,
            'sl_price' => $s2SlPrice,
            'tp_price' => isset($trade['tp_price']) ? (float)$trade['tp_price'] : null,
            'risk_pct' => isset($trade['risk_pct']) ? (float)$trade['risk_pct'] : null,
            'risk_reward' => isset($trade['risk_reward_tp1']) ? (float)$trade['risk_reward_tp1'] : null,
            'lots' => isset($trade['lots_per_position']) ? (float)$trade['lots_per_position'] : null,
            'max_loss' => isset($trade['max_potential_loss']) ? (float)$trade['max_potential_loss'] : null,
            'max_profit' => $s2MaxProfit,
            'max_loss_pips' => $s2SlPips,
            'tp_levels' => self::buildTpLevels($trade, $s2EntryPrice, $s2SlPrice, $s2SlPips, $s2MaxProfit),
            'blocker_overridden' => !empty($trade['user_overrode_blocker']),
        ];

        // ════════════════════════════════════════════════════════════
        // STAGE 3: MANUAL OUTCOME — what was confirmed in Drafts once the
        // trade closed. Prop-firm accounts measured against challenge_pnl,
        // falling back to broker_pnl then projected_pnl when challenge_pnl
        // hasn't been backfilled for a trade (same cascade as the mv_*
        // views / dashEffectivePnl() / reports.php's $pnlExpr — previously
        // this went straight to NULL instead of falling back, silently
        // losing real broker_pnl on every such trade). Personal accounts
        // never touch challenge_pnl at all. A final NULL (not 0) means
        // "no outcome captured yet" — preserved so 'win_loss' below can
        // still tell that apart from a genuine breakeven.
        // ════════════════════════════════════════════════════════════
        $actualEntryPrice = isset($trade['actual_entry_price']) ? (float)$trade['actual_entry_price'] : null;
        $actualExitPrice = isset($trade['actual_exit_price']) ? (float)$trade['actual_exit_price'] : null;
        $actualSlPrice = isset($trade['actual_sl_price']) ? (float)$trade['actual_sl_price'] : null;
        $actualLotsOut = isset($trade['actual_lots']) ? (float)$trade['actual_lots'] : null;
        $effectivePnl = $tradeAccountType === 'prop_firm'
            ? (isset($trade['challenge_pnl']) ? (float)$trade['challenge_pnl']
                : (isset($trade['broker_pnl']) ? (float)$trade['broker_pnl']
                : (isset($trade['projected_pnl']) ? (float)$trade['projected_pnl'] : null)))
            : (isset($trade['broker_pnl']) ? (float)$trade['broker_pnl']
                : (isset($trade['projected_pnl']) ? (float)$trade['projected_pnl'] : null));

        $plannedEntry = isset($trade['entry_price']) ? (float)$trade['entry_price'] : null;
        $plannedTp = isset($trade['tp_price']) ? (float)$trade['tp_price'] : null;
        $plannedSl = isset($trade['sl_price']) ? (float)$trade['sl_price'] : null;

        $entrySlippage = ($plannedEntry !== null && $actualEntryPrice !== null) ? round($actualEntryPrice - $plannedEntry, 5) : null;
        // Exit slippage measured against whichever target (TP if the
        // trade won, SL if it lost) the exit price was presumably
        // approaching — not a stored column, computed from real prices.
        $exitSlippage = null;
        if ($actualExitPrice !== null && $effectivePnl !== null) {
            if ($effectivePnl >= 0 && $plannedTp !== null) $exitSlippage = round($actualExitPrice - $plannedTp, 5);
            elseif ($effectivePnl < 0 && $plannedSl !== null) $exitSlippage = round($actualExitPrice - $plannedSl, 5);
        }

        // Which declared TP level the actual exit landed closest to (0.1%
        // price tolerance — same convention already used for entry-price
        // adherence elsewhere in this file). Only meaningful check, not a
        // stored column: multi-TP outcomes aren't otherwise captured (see
        // Known Gotcha in CLAUDE.md re: TP2-10 pre-fill in Drafts).
        $tpLevelHit = null;
        if ($actualExitPrice !== null) {
            for ($tpi = 1; $tpi <= 10; $tpi++) {
                $tpKey = $tpi === 1 ? 'tp_price' : "tp{$tpi}_price";
                if (!isset($trade[$tpKey]) || $trade[$tpKey] === null || $trade[$tpKey] === '') continue;
                $tpPriceI = (float)$trade[$tpKey];
                if ($tpPriceI == 0.0) continue;
                if (abs($actualExitPrice - $tpPriceI) <= abs($tpPriceI) * self::CALC_PRICE_TOLERANCE_RATIO) { $tpLevelHit = $tpi; break; }
            }
        }

        // Execution Adherence — reuses the exact tolerances the orphaned
        // wasPlanFollowed() already established (0.1% entry price, 5% lot
        // size) as a first-class Stage 3 aggregate, rather than leaving that
        // check stranded in dead code. Distinct from Stage 2's Calculator
        // Adherence (which checks the SAVED numbers' own internal geometric
        // consistency) — this checks whether the manually-confirmed
        // execution actually matched the plan. Null (not false) whenever
        // either side of the comparison isn't computable, so the period
        // aggregate's denominator only counts trades where this was actually
        // checkable.
        $entryAdherent = null;
        if ($stage2['entry_price'] !== null && $stage2['entry_price'] != 0 && $actualEntryPrice !== null) {
            $entryAdherent = abs($actualEntryPrice - $stage2['entry_price']) <= abs($stage2['entry_price']) * self::CALC_PRICE_TOLERANCE_RATIO;
        }
        // Lot Size Adherence — exposed standalone (lot_adherent) as well as
        // folded into the combined execution_adherent flag below, so the
        // Intent vs Reality card can show Lot Size Precision as its own
        // metric rather than only inside a blended entry+lot boolean.
        $lotsAdherentS3 = null;
        if ($stage2['lots'] !== null && $stage2['lots'] > 0 && $actualLotsOut !== null) {
            $lotsAdherentS3 = abs($actualLotsOut - $stage2['lots']) <= $stage2['lots'] * self::CALC_LOT_TOLERANCE_RATIO;
        }
        $executionAdherent = ($entryAdherent !== null && $lotsAdherentS3 !== null) ? ($entryAdherent && $lotsAdherentS3) : null;

        // Stop Precision input — same 0.1% price tolerance as Entry
        // Adherence above, applied to the stop loss instead. Not previously
        // checked anywhere in this file (only entry price + lot size feed
        // Execution Adherence) — genuinely new, for the Intent vs Reality
        // card's "Stop Precision" metric. Bounded boolean, not a ratio —
        // deliberately avoids the unguarded-division failure mode documented
        // on executedRiskPct/actualBrokerRiskPct below (see CLAUDE.md).
        $slAdherent = null;
        if ($stage2['sl_price'] !== null && $stage2['sl_price'] != 0 && $actualSlPrice !== null) {
            $slAdherent = abs($actualSlPrice - $stage2['sl_price']) <= abs($stage2['sl_price']) * self::CALC_PRICE_TOLERANCE_RATIO;
        }

        // Executed Risk — ratio-scales Stage 2's own planned risk% by the
        // actual-vs-planned lot ratio (same "never invent pip/contract-value
        // formulas, scale two already-computed real numbers instead"
        // technique Stage 1 already uses for its simulated position size).
        // Approximation, not exact: assumes the SL distance itself didn't
        // move between planning and execution (sl_moves isn't incorporated
        // here — see CLAUDE.md's Known Gotchas on sl_moves being unused
        // elsewhere in the 4-Stage architecture too).
        $executedRiskPct = null;
        if ($stage2['risk_pct'] !== null && $stage2['lots'] !== null && $stage2['lots'] > 0 && $actualLotsOut !== null) {
            $executedRiskPct = round($stage2['risk_pct'] * ($actualLotsOut / $stage2['lots']), 2);
        }

        // Executed RR — signed R-multiple of the manually-confirmed outcome
        // (effective_pnl ÷ Stage 2's planned max loss), across BOTH wins and
        // losses. Distinct from Stage 4's actual_rr_realized (winners only,
        // broker-reconciliation-gated) — this is the everyday "how many R
        // did this trade make/lose" read on the trader's own confirmed
        // outcome, not gated on MT5 reconciliation.
        $rMultiple = null;
        if ($effectivePnl !== null && $stage2['max_loss'] !== null && $stage2['max_loss'] > 0) {
            $rMultiple = round($effectivePnl / $stage2['max_loss'], 3);
        }

        // Executed Timeframe — real, app-level (not broker-gated) hold
        // duration bucketed into the same scalper/day_trader/swing/position
        // labels getStyleDriftSignal() already uses, from opened_at/closed_at
        // (when the position was actually worked in the app) — distinct from
        // Stage 4's broker-verified bucket below, which uses MT5 timestamps.
        $s3HoldMinutes = null; $s3StyleBucket = null;
        if (!empty($trade['opened_at']) && !empty($trade['closed_at'])) {
            try {
                $openedDt = new \DateTime($trade['opened_at']);
                $closedDt = new \DateTime($trade['closed_at']);
                $s3DiffMinutes = ($closedDt->getTimestamp() - $openedDt->getTimestamp()) / 60;
                if ($s3DiffMinutes >= 0) {
                    $s3HoldMinutes = round($s3DiffMinutes, 1);
                    $s3StyleBucket = self::bucketHoldHoursToStyle($s3DiffMinutes / 60);
                }
            } catch (\Throwable $e) { /* malformed timestamp — leave null */ }
        }

        $stage3 = [
            'actual_entry_price' => $actualEntryPrice, 'actual_exit_price' => $actualExitPrice,
            'actual_sl_price' => $actualSlPrice, 'actual_lots' => $actualLotsOut,
            'entry_slippage' => $entrySlippage, 'exit_slippage' => $exitSlippage,
            'tp_level_hit' => $tpLevelHit,
            'commission' => isset($trade['broker_commission']) ? (float)$trade['broker_commission'] : null,
            'swap' => isset($trade['broker_swap']) ? (float)$trade['broker_swap'] : null,
            'effective_pnl' => $effectivePnl,
            'win_loss' => $effectivePnl === null ? null : ($effectivePnl > 0 ? 'win' : ($effectivePnl < 0 ? 'loss' : 'breakeven')),
            'execution_adherent' => $executionAdherent,
            'entry_adherent' => $entryAdherent, 'sl_adherent' => $slAdherent,
            'lot_adherent' => $lotsAdherentS3,
            'executed_risk_pct' => $executedRiskPct,
            'r_multiple' => $rMultiple,
            'hold_minutes' => $s3HoldMinutes,
            'hold_style_bucket' => $s3StyleBucket,
            // Manually captured at Draft Resolution (2026-07-27 finishing
            // pass, item 2) — confirm-or-correct instrument, optional chart
            // timeframe. Null for legacy trades resolved before these
            // columns existed, or when left blank at resolution.
            'actual_instrument' => !empty($trade['actual_instrument']) ? $trade['actual_instrument'] : null,
            'actual_timeframe' => !empty($trade['actual_timeframe']) ? $trade['actual_timeframe'] : null,
            // "Managed Exit" workflow (2026-08-03, migration_add_exit_
            // classification.sql) — a real trader declaration (Break Even or
            // an early managed exit before the planned Full TP), captured at
            // Draft Resolution. Deliberately null for ordinary TP-hit/stop-
            // loss-hit/manual-close resolutions, which keep relying on
            // tp_level_hit/price-tolerance inference above, unchanged.
            // declared_r_multiple is the trader's own DECLARED intent, never
            // more authoritative than actual_exit_price/effective_pnl above.
            'exit_classification' => !empty($trade['exit_classification']) ? $trade['exit_classification'] : null,
            'declared_r_multiple' => isset($trade['declared_r_multiple']) && $trade['declared_r_multiple'] !== null ? (float)$trade['declared_r_multiple'] : null,
        ];

        // ════════════════════════════════════════════════════════════
        // STAGE 4: BROKER RECONCILIATION — MT5-verified ground truth.
        // Grounded via the confirmed is_reconciled flag, not assumed.
        // ════════════════════════════════════════════════════════════
        $isReconciled = !empty($trade['is_reconciled']);

        // Holding time — MT5-verified open/close timestamps only (never the
        // calculator's captured_at/closed_at, which reflect when the trader
        // used the app, not when the position was actually open in market).
        $holdingTimeMinutes = null; $holdingTimeDisplay = null;
        if (!empty($trade['broker_open_time']) && !empty($trade['broker_close_time'])) {
            try {
                $openDt = new \DateTime($trade['broker_open_time']);
                $closeDt = new \DateTime($trade['broker_close_time']);
                $diffMinutes = ($closeDt->getTimestamp() - $openDt->getTimestamp()) / 60;
                if ($diffMinutes >= 0) {
                    $holdingTimeMinutes = round($diffMinutes, 1);
                    if ($diffMinutes < 60) $holdingTimeDisplay = number_format($diffMinutes, 1) . ' min';
                    elseif ($diffMinutes < 1440) $holdingTimeDisplay = number_format($diffMinutes / 60, 1) . ' hr';
                    else $holdingTimeDisplay = number_format($diffMinutes / 1440, 1) . ' days';
                }
            } catch (\Throwable $e) { /* malformed timestamp — leave null */ }
        }

        $brokerLotSize = isset($trade['broker_lot_size']) ? (float)$trade['broker_lot_size'] : null;

        // Reconciled Adherence — of RECONCILED trades only, whether the
        // broker-verified lot size matches Stage 2's planned lot size within
        // the same 5% tolerance Execution Adherence (Stage 3) uses. Distinct
        // from "Reconciliation Accuracy" (which just measures how much of
        // the period got reconciled at all, now a secondary metric) — this
        // measures whether the reconciled trades actually matched plan.
        $reconciledAdherent = null;
        if ($isReconciled && $stage2['lots'] !== null && $stage2['lots'] > 0 && $brokerLotSize !== null) {
            $reconciledAdherent = abs($brokerLotSize - $stage2['lots']) <= $stage2['lots'] * self::CALC_LOT_TOLERANCE_RATIO;
        }

        // Actual Broker Risk — same ratio-scaling technique as Stage 3's
        // Executed Risk, but scaled by the BROKER-VERIFIED lot size instead
        // of the trader's self-reported actual_lots. This replaces the
        // previous actual_risk_avg computation (see getFourStagePeriodSummary()),
        // which mistakenly averaged Stage 2's own planned risk_pct for
        // reconciled trades — a real number, but not actually broker-verified.
        $actualBrokerRiskPct = null;
        if ($isReconciled && $stage2['risk_pct'] !== null && $stage2['lots'] !== null && $stage2['lots'] > 0 && $brokerLotSize !== null) {
            $actualBrokerRiskPct = round($stage2['risk_pct'] * ($brokerLotSize / $stage2['lots']), 2);
        }

        // Broker Timeframe — same style-bucketing as Stage 3, but from
        // MT5-verified open/close timestamps (holding_time_minutes above),
        // gated on is_reconciled — "only if truly verifiable" per the
        // Broker Reconciliation stage's own rule.
        $s4StyleBucket = ($isReconciled && $holdingTimeMinutes !== null) ? self::bucketHoldHoursToStyle($holdingTimeMinutes / 60) : null;

        // Instrument broker-confirmation — MT5 reconciliation matches the
        // broker's own symbol against trades.instrument (see reconcile.php),
        // so once a trade is reconciled, trades.instrument IS the
        // broker-verified instrument. Comparing it against the manually
        // captured actual_instrument (Stage 3) is the "let reconciliation
        // validate or challenge" behavior for instrument — real, since MT5
        // does carry a symbol. Chart timeframe has no such broker field to
        // check against (see actual_timeframe below, carried through
        // unverified).
        $instrumentConfirmed = null;
        if ($isReconciled && !empty($trade['actual_instrument']) && !empty($trade['instrument'])) {
            $instrumentConfirmed = (strtolower($trade['actual_instrument']) === strtolower($trade['instrument']));
        }

        // Hard gate, fixed 2026-08-13. Every broker_* field below used to
        // be read straight off its column with no is_reconciled check.
        // Correct today only because every current writer sets
        // is_reconciled=1 in the same statement. Not guaranteed for a
        // future importer. $s4Raw() is the one gate every Stage 4
        // consumer now relies on.
        $s4Raw = fn($v) => $isReconciled ? $v : null;

        $stage4 = [
            'reconciled' => $isReconciled,
            'broker_open_time' => $s4Raw($trade['broker_open_time'] ?? null),
            'broker_close_time' => $s4Raw($trade['broker_close_time'] ?? null),
            'broker_lot_size' => $s4Raw($brokerLotSize),
            'broker_commission' => $s4Raw(isset($trade['broker_commission']) ? (float)$trade['broker_commission'] : null),
            'broker_swap' => $s4Raw(isset($trade['broker_swap']) ? (float)$trade['broker_swap'] : null),
            'mt5_position_id' => $s4Raw($trade['mt5_position_id'] ?? null),
            'holding_time_minutes' => $s4Raw($holdingTimeMinutes),
            'holding_time_display' => $s4Raw($holdingTimeDisplay),
            'reconciled_adherent' => $reconciledAdherent,
            'actual_broker_risk_pct' => $actualBrokerRiskPct,
            'hold_style_bucket' => $s4StyleBucket,
            'instrument_confirmed' => $instrumentConfirmed,
            // Timeframe is manually captured at Stage 3, never overwritten
            // here — MT5 statements carry no chart-timeframe field to
            // verify against (confirmed during the 2026-07-27 finishing
            // pass — see CLAUDE.md). Carried through only for reconciled
            // trades so Stage 4's distribution reflects broker-confirmed
            // trades specifically, not the whole period.
            'actual_timeframe' => $s4Raw(!empty($trade['actual_timeframe']) ? $trade['actual_timeframe'] : null),
            // Broker fill-price fields (2026-08-03, migration_add_broker_
            // reconciliation_prices.sql) — pipeline-ready for the future
            // automatic MT5 Sync Engine (see
            // docs/architecture/TRADERALIGN_MT5_SYNC_ENGINE_AND_TRADE_REVIEW_ARCHITECTURE.md,
            // Phase 7). Raw only — Journal's single-trade 4-Stage Lifecycle
            // view derives Slippage/broker-verified Pips/TP-hit-at-broker-
            // price from these, the same way it already derives Stage 3's
            // own slippage/pips from actual_entry_price/actual_exit_price
            // rather than this method doing that math. Every real trade in
            // this database has all four NULL today (confirmed via
            // SHOW COLUMNS before the migration) — this is expected, not a
            // bug, until a future importer/Sync Engine populates them.
            'broker_entry_price' => $s4Raw(isset($trade['broker_entry_price']) ? (float)$trade['broker_entry_price'] : null),
            'broker_exit_price' => $s4Raw(isset($trade['broker_exit_price']) ? (float)$trade['broker_exit_price'] : null),
            'broker_sl_price' => $s4Raw(isset($trade['broker_sl_price']) ? (float)$trade['broker_sl_price'] : null),
            'broker_spread_at_entry' => $s4Raw(isset($trade['broker_spread_at_entry']) ? (float)$trade['broker_spread_at_entry'] : null),
        ];

        // ════════════════════════════════════════════════════════════
        // GEOMETRIC CONSISTENCY CHECK — validates that Stage 2's own
        // stored entry/SL/TP prices actually geometrically produce the
        // R:R it claims (risk_reward_tp1), and that its stored
        // max_potential_loss actually matches what risk_pct × its own
        // balance_at_entry implies. Deliberately pure ratio/arithmetic —
        // R:R is a ratio of two price distances, so it cancels out any
        // per-pip/contract dollar value regardless of instrument (same
        // "never invent pip/contract-value formulas" principle as Stage
        // 1 above); risk % is checked as $ ÷ $ ÷ 100, not via price
        // geometry, for the same reason.
        // ════════════════════════════════════════════════════════════
        $geometricConsistency = [
            'declared_rr' => null, 'price_implied_rr' => null, 'rr_mismatch' => null, 'rr_mismatch_flag' => false,
            'declared_risk_pct' => null, 'dollar_implied_risk_pct' => null, 'risk_pct_mismatch' => null, 'risk_pct_mismatch_flag' => false,
        ];
        $gcTpPrice = $stage2['tp_price'];
        if ($s2EntryPrice !== null && $s2SlPrice !== null && $gcTpPrice !== null && abs($s2EntryPrice - $s2SlPrice) > 0.0000001) {
            $priceImpliedRr = round(abs($gcTpPrice - $s2EntryPrice) / abs($s2EntryPrice - $s2SlPrice), 3);
            $geometricConsistency['price_implied_rr'] = $priceImpliedRr;
            if ($stage2['risk_reward'] !== null) {
                $geometricConsistency['declared_rr'] = $stage2['risk_reward'];
                $rrDiff = round($priceImpliedRr - $stage2['risk_reward'], 3);
                $geometricConsistency['rr_mismatch'] = $rrDiff;
                // CALC_GEOMETRIC_RR_TOLERANCE (0.30) — was a flat 0.15, which
                // real historical data showed was miscalibrated: the "clean"
                // (tick/price-rounding-only) distribution of this exact
                // comparison capped at 0.2642 across 266 real trades, so the
                // old threshold flagged the majority of genuinely consistent
                // trades as mismatched. See CALC_TOLERANCE_RATIO's doc comment.
                $geometricConsistency['rr_mismatch_flag'] = abs($rrDiff) > self::CALC_GEOMETRIC_RR_TOLERANCE;
            }
        }
        $gcBalanceAtEntry = isset($trade['balance_at_entry']) && $trade['balance_at_entry'] !== null ? (float)$trade['balance_at_entry'] : null;
        if ($stage2['max_loss'] !== null && $gcBalanceAtEntry !== null && $gcBalanceAtEntry > 0) {
            $dollarImpliedRiskPct = round(($stage2['max_loss'] / $gcBalanceAtEntry) * 100, 2);
            $geometricConsistency['dollar_implied_risk_pct'] = $dollarImpliedRiskPct;
            if ($stage2['risk_pct'] !== null) {
                $geometricConsistency['declared_risk_pct'] = $stage2['risk_pct'];
                $pctDiff = round($dollarImpliedRiskPct - $stage2['risk_pct'], 2);
                $geometricConsistency['risk_pct_mismatch'] = $pctDiff;
                // CALC_GEOMETRIC_RISK_PCT_TOLERANCE (0.5) — validated against
                // the same 266-trade dataset: the clean cluster sat at p95=
                // 0.034, the outlier cluster at p99=1.00, so 0.5 already sits
                // correctly in the gap between them. Left unchanged.
                $geometricConsistency['risk_pct_mismatch_flag'] = abs($pctDiff) > self::CALC_GEOMETRIC_RISK_PCT_TOLERANCE;
            }
        }

        // deviation_score (formerly here) — REMOVED. It computed
        // |abs(effective_pnl)/max_potential_loss - 1.0| and was displayed as
        // "Plan vs. Reality Gap", but that ratio compares a trade's actual
        // P&L against its declared MAX LOSS, not against a declared plan —
        // so a winning trade (which has nothing to do with the max-loss
        // figure it's being measured against) routinely produced numbers
        // like 202% on a trade that was saved completely unedited from the
        // calculator, with zero real drift between plan and execution.
        // Confirmed broken, not just mislabeled: deleted rather than fixed,
        // since the real "plan vs. reality" concept (execution slippage,
        // calculator lot-size quantization) is a separate feature being
        // properly rebuilt elsewhere (see CLAUDE.md). Do not reintroduce
        // this formula under a different name.
        return [
            'trade_id' => $tradeId,
            'instrument' => $instrument,
            'captured_at' => $trade['captured_at'] ?? null,
            'closed_at' => $trade['closed_at'] ?? null,
            'direction' => $direction,
            'account_type' => $tradeAccountType,
            'account_id' => $trade['account_id'] ?? null,
            'stage1' => $stage1, 'stage2' => $stage2, 'stage3' => $stage3, 'stage4' => $stage4,
            'geometric_consistency' => $geometricConsistency,
        ];
    }

    /**
     * TP2-10 breakdown for Stage 2 — price, raw price-distance-from-entry
     * (always available), pips-from-entry (only when sl_distance_pips is
     * present on the trade — null on trades saved before the calculator's
     * own pip computation started being persisted, see
     * api/trades/draft.php; never independently re-derived, per this
     * file's "never invent pip/contract-value formulas" convention), and
     * dollar amount at each level. Dollar amount is derived by scaling
     * max_potential_profit (the already-computed $ at the primary/TP1
     * level) by the ratio of raw price distances — same ratio-scaling
     * technique this file already uses elsewhere, and it works regardless
     * of whether pip data is available for this trade.
     */
    private static function buildTpLevels($trade, $entryPrice, $slPrice, $slPips, $maxPotentialProfit) {
        if ($entryPrice === null) return [];
        $tp1Price = isset($trade['tp_price']) && $trade['tp_price'] !== null && $trade['tp_price'] !== '' ? (float)$trade['tp_price'] : null;
        $tp1Distance = $tp1Price !== null ? abs($tp1Price - $entryPrice) : null;
        $slDistance = $slPrice !== null ? abs($entryPrice - $slPrice) : null;

        $levels = [];
        $prevPips = 0.0;
        for ($i = 1; $i <= 10; $i++) {
            $key = $i === 1 ? 'tp_price' : "tp{$i}_price";
            if (!isset($trade[$key]) || $trade[$key] === null || $trade[$key] === '') continue;
            $price = (float)$trade[$key];
            $priceDistance = abs($price - $entryPrice);

            $pipsFromEntry = ($slPips !== null && $slDistance !== null && $slDistance > 0)
                ? round($slPips * ($priceDistance / $slDistance), 1) : null;
            $pipsFromPrevious = $pipsFromEntry !== null ? round($pipsFromEntry - $prevPips, 1) : null;
            if ($pipsFromEntry !== null) $prevPips = $pipsFromEntry;

            $dollarAmount = ($maxPotentialProfit !== null && $tp1Distance !== null && $tp1Distance > 0)
                ? round($maxPotentialProfit * ($priceDistance / $tp1Distance), 2) : null;

            $levels[] = [
                'level' => $i, 'price' => $price,
                'price_distance_from_entry' => round($priceDistance, 5),
                'pips_from_entry' => $pipsFromEntry, 'pips_from_previous' => $pipsFromPrevious,
                'dollar_amount' => $dollarAmount,
            ];
        }
        return $levels;
    }

    /**
     * Buckets a hold duration (in hours) into a trading-style label, using
     * the identical thresholds getStyleDriftSignal()'s $styleRanges already
     * established (scalper <=2h, day_trader <=24h, swing <=120h/5 days,
     * else position). Kept as a separate small helper rather than
     * refactoring getStyleDriftSignal() to share it, to avoid touching that
     * already-verified function for this additive 4-Stage Summary feature.
     */
    private static function bucketHoldHoursToStyle($hours) {
        if ($hours === null) return null;
        if ($hours <= 2) return 'scalper';
        if ($hours <= 24) return 'day_trader';
        if ($hours <= 120) return 'swing';
        return 'position';
    }

    /** Strips separators and uppercases an instrument symbol for comparison — mirrors reports.php's normalizeSymbol(), duplicated here so engine.php stays self-contained. */
    private static function normalizeInstrumentSymbol($s) {
        return strtoupper(str_replace(['/', '-', '_', ' '], '', (string)$s));
    }

    /** Formats a single value as $ currency, or '-' if null. */
    private static function fmtMoney($v) { return $v === null ? '-' : (($v >= 0 ? '$' : '-$') . number_format(abs($v), 2)); }
    private static function fmtPct($v)   { return $v === null ? '-' : number_format($v, 2) . '%'; }
    // Item 5 (2026-07-28 refinement pass): "1 : X", never a bare "X:1" —
    // this app's stored convention is reward-per-1-unit-risk (see
    // computeFourStageRaw()'s declaredMaxProfit = declaredMaxLoss *
    // declaredRr), so risk is always the fixed "1" and $v is the reward
    // side. A bare "X:1" paired with a "Risk:Reward" label would misread
    // as risk=X/reward=1 — backwards from what's actually stored.
    private static function fmtRr($v)    { return $v === null ? '-' : '1 : ' . number_format($v, 2); }
    private static function fmtNum($v)   { return $v === null ? '-' : (string)$v; }

    /**
     * Per-trade display view — used by Journal Trades' 4-Stage toggle,
     * and by Reports when it links to one specific trade. Formats the
     * raw computation from computeFourStageRaw(); the true 4-Stage
     * architecture (single source of truth).
     */
    public static function getFourStageComparison($tradeId) {
        $raw = self::computeFourStageRaw($tradeId);
        if (isset($raw['error'])) return $raw;

        $s1 = $raw['stage1'];
        if ($s1['available']) {
            $edgeSnapshot = [
                'Instrument' => $s1['instrument'], 'Direction' => ucfirst($s1['direction']),
                'Account Balance' => self::fmtMoney($s1['account_balance']),
                'Entry Price' => self::fmtNum($s1['entry_price']), 'Stop Loss' => self::fmtNum($s1['sl_price']),
                'Take Profit' => self::fmtNum($s1['tp_price']),
                'Risk %' => self::fmtPct($s1['risk_pct']), 'Risk:Reward' => self::fmtRr($s1['risk_reward']),
                'Position Size (lots)' => $s1['lots'] !== null ? self::fmtNum($s1['lots']) : ($s1['sizing_note'] ?? '-'),
                'Max Potential Loss' => self::fmtMoney($s1['max_loss']), 'Max Potential Profit' => self::fmtMoney($s1['max_profit']),
            ];
            // Declared Timeframe(s) — 2026-07-28 refinement pass. Was already
            // computed (Reports' Stage 1 Row 6 has used this since 2026-07-27
            // item 2) but never surfaced on Journal's single-trade 4-Stage
            // view. A strategy can declare more than one timeframe, so this
            // is a joined list, not a single value — omitted entirely (not
            // shown as "—") when the strategy declared none, same convention
            // as the rest of this array.
            if (!empty($s1['timeframes'])) $edgeSnapshot['Timeframe(s) Traded'] = $s1['timeframes'];
            if (!empty($s1['consistency_notes'])) $edgeSnapshot['consistency_note'] = implode(' ', $s1['consistency_notes']);
        } else {
            $edgeSnapshot = ['note' => 'No strategy linked to this trade. A Strategy Edge Snapshot requires a declared strategy version to simulate against.'];
        }

        $s2 = $raw['stage2'];
        $tradePlan = [
            'Instrument' => $s2['instrument'], 'Direction' => ucfirst($s2['direction']),
            'Entry Price' => self::fmtNum($s2['entry_price']), 'Stop Loss' => self::fmtNum($s2['sl_price']), 'Take Profit' => self::fmtNum($s2['tp_price']),
            'Risk %' => self::fmtPct($s2['risk_pct']), 'Risk:Reward' => self::fmtRr($s2['risk_reward']),
            'Position Size (lots)' => self::fmtNum($s2['lots']),
            'Max Potential Loss' => self::fmtMoney($s2['max_loss']), 'Max Potential Profit' => self::fmtMoney($s2['max_profit']),
        ];
        // Planned Timeframe — 2026-07-28 refinement pass. The Calculator's
        // own planned_timeframe field (optional; no default) was captured
        // and persisted since 2026-07-27 but this specific view — Journal's
        // single-trade "Calculator Intent" card — never surfaced it, even
        // though it's genuinely Stage 2's own data (Journal Trade Details'
        // Execution section shows the same field separately, added in this
        // same pass). Omitted when the trader left it blank, not shown as "—".
        if (!empty($s2['timeframe'])) $tradePlan['Timeframe'] = $s2['timeframe'];
        if ($s2['blocker_overridden']) $tradePlan['Blocker Override'] = 'Yes, a calculator warning was overridden to place this trade.';

        $s3 = $raw['stage3'];
        $manualOutcome = [
            'Actual Entry Price' => self::fmtNum($s3['actual_entry_price']), 'Actual Exit Price' => self::fmtNum($s3['actual_exit_price']),
            'Actual Stop Loss' => self::fmtNum($s3['actual_sl_price']), 'Actual Lots' => self::fmtNum($s3['actual_lots']),
            'Entry Slippage' => self::fmtNum($s3['entry_slippage']), 'Exit Slippage' => self::fmtNum($s3['exit_slippage']),
            'Commission' => self::fmtMoney($s3['commission']), 'Swap' => self::fmtMoney($s3['swap']),
            'Net P&L' => $s3['effective_pnl'] !== null ? (($s3['effective_pnl'] >= 0 ? '+' : '') . number_format($s3['effective_pnl'], 2)) : '-',
            'Win/Loss' => $s3['win_loss'] !== null ? ucfirst($s3['win_loss']) : '-',
        ];

        $s4 = $raw['stage4'];
        $brokerReconciliation = $s4['reconciled'] ? [
            'Broker Open Time' => $s4['broker_open_time'] ?? '-', 'Broker Close Time' => $s4['broker_close_time'] ?? '-',
            'Broker Lot Size' => self::fmtNum($s4['broker_lot_size']), 'Broker Commission' => self::fmtMoney($s4['broker_commission']),
            'Broker Swap' => self::fmtMoney($s4['broker_swap']), 'MT5 Position ID' => $s4['mt5_position_id'] ?? '-',
        ] : ['note' => 'Pending: MT5 broker reconciliation not yet available for this trade.'];

        return [
            'trade_id' => $raw['trade_id'], 'instrument' => $raw['instrument'],
            'captured_at' => $raw['captured_at'], 'closed_at' => $raw['closed_at'],
            'direction' => $raw['direction'], 'account_type' => $raw['account_type'], 'account_id' => $raw['account_id'],
            'edge_snapshot' => $edgeSnapshot, 'trade_plan' => $tradePlan,
            'manual_outcome' => $manualOutcome, 'broker_reconciliation' => $brokerReconciliation,
            'effective_pnl' => $s3['effective_pnl'],
        ];
    }

    /**
     * Public accessor for the RAW per-trade 4-stage computation (stage1-4
     * arrays of real, unformatted numbers, plus geometric_consistency) —
     * used by Journal's single-trade "4-Stage Trade Lifecycle" view
     * (Active Journeys → View Details), which needs to align one metric
     * per row across all four stages itself, rather than
     * getFourStageComparison()'s already-formatted display strings. Thin
     * wrapper only — computeFourStageRaw() stays the single source of
     * truth shared with Reports' period aggregate below.
     *
     * $allowValidation (2026-08-18 correction) — every OTHER caller of
     * this method (Colex's getFourStageComparison(), Reports' period
     * aggregate) reads trades it has already, separately, scoped to the
     * main workflow, so the default (false) preserves their exact
     * existing behavior. Journal's single-trade view is the one caller
     * that legitimately needs to view a VALIDATION trade's own 4-Stage
     * data — it already verified, before ever calling this, that the
     * trade belongs to the user's current (validation) workflow
     * ($jtTradeInScope in public/journal.php) — so it passes true.
     */
    public static function getFourStageRaw($tradeId, bool $allowValidation = false) {
        return self::computeFourStageRaw($tradeId, $allowValidation);
    }

    /**
     * Period-level aggregate — used by Reports (one 4-Stage section per
     * reporting period). Calls computeFourStageRaw() for every closed
     * trade in scope and aggregates the raw numbers — the exact same
     * per-trade computation Journal uses, never a separate
     * reimplementation, so Reports and Journal cannot drift apart.
     *
     * $accountId (optional) scopes to one specific trading_accounts row
     * instead of every account of the active account-view type — powers
     * the 4-Stage Summary's account-scope selector in reports.php. Null/
     * empty means the existing "every account of this type" aggregate.
     *
     * $validationScope (optional, additive — 2026-08-12 Dashboard cleanup)
     * mirrors buildFilterClause()'s own 'exclude'/'only' escape hatch:
     * default 'exclude' is byte-identical to this function's original,
     * always-main-workflow behavior (every existing caller — Reports,
     * Trader Profile — omits it and is unaffected); 'only' flips to
     * is_validation=1, so a validating user's Dashboard can call this same
     * canonical aggregator for their own validation trades instead of
     * always seeing an empty result.
     */
    public static function getFourStagePeriodSummary($userId, $accountView, $startStr, $endStr, $svidCondition = '', $svidParams = [], $accountId = null, $validationScope = 'exclude') {
        $db = self::getConnection();
        $typeFilter = match ($accountView) {
            'prop' => "AND ta.account_type = 'prop_firm'",
            'live' => "AND ta.account_type = 'personal'",
            default => '',
        };
        $acctFilter = !empty($accountId) ? " AND t.account_id = :facctid" : '';
        $validationCond = $validationScope === 'only' ? "t.is_validation = 1" : "(t.is_validation = 0 OR t.is_validation IS NULL)";
        $sql = "SELECT t.id FROM trades t LEFT JOIN trading_accounts ta ON ta.id = t.account_id
                WHERE t.user_id = :uid AND t.status = 'closed' AND t.deleted_at IS NULL
                AND {$validationCond}
                AND t.closed_at >= :s AND t.closed_at <= :e {$typeFilter}{$acctFilter}" . $svidCondition;
        $stmt = $db->prepare($sql);
        $acctParams = !empty($accountId) ? [':facctid' => $accountId] : [];
        $stmt->execute(array_merge([':uid' => $userId, ':s' => $startStr, ':e' => $endStr], $acctParams, $svidParams));
        $tradeIds = $stmt->fetchAll(PDO::FETCH_COLUMN);

        if (empty($tradeIds)) return ['trades_in_period' => 0];

        $s1RiskPcts = []; $s1Rrs = []; $s1MaxLosses = []; $s1MaxProfits = []; $s1MaxLossPips = []; $s1MaxProfitPips = [];
        $s1RiskOverages = []; $s1RrMismatches = []; $s1Instruments = []; $s1Directions = []; $s1Sessions = [];
        $s1Timeframes = [];
        $s1AvailableCount = 0;
        // Item 7 (2026-07-27): counted separately from $s1RiskOverages —
        // a Contract-Constrained trade must never be counted as Over-Risk.
        $s1ContractConstrainedCount = 0;
        // Declared Timeframe (Row 6 of the 4-Stage Summary) — distribution of
        // the ATTACHED STRATEGY's own trading_style, per trade in the period.
        $s1Styles = [];
        // Intent vs Reality — Execution Gap card inputs. Session/Instrument
        // Drift (Stage 1's declared preference vs. what was actually
        // traded) and Entry/Stop/TP Precision (Stage 2's plan vs. Stage 3's
        // manually-confirmed outcome) — all bounded boolean-adherence
        // percentages, deliberately not ratio-scaled averages, per the root-
        // cause finding documented in reports.php's Intent vs Reality
        // rebuild (an unclamped ratio like executedRiskPct/actualBrokerRiskPct
        // below can explode into an impossible percentage when its
        // denominator is small; a %-of-trades-within-tolerance can't).
        $s1SessionAdherentCount = 0; $s1SessionAdherentComputable = 0;
        $s1InstrumentAdherentCount = 0; $s1InstrumentAdherentComputable = 0;
        $s3EntryPrecisionCount = 0; $s3EntryPrecisionComputable = 0;
        $s3StopPrecisionCount = 0; $s3StopPrecisionComputable = 0;
        $s3TpPrecisionCount = 0; $s3TpPrecisionComputable = 0;
        $s3LotPrecisionCount = 0; $s3LotPrecisionComputable = 0;
        $s2RiskPcts = []; $s2Rrs = []; $s2Lots = []; $s2MaxLosses = []; $s2MaxProfits = []; $s2MaxLossPips = [];
        $s2Instruments = []; $s2Directions = []; $s2Sessions = []; $s2Timeframes = [];
        // tp_level_usage[N] = ['declared' => count, 'pips' => [...], 'dollars' => [...]]
        $tpLevelUsage = [];
        for ($i = 1; $i <= 10; $i++) $tpLevelUsage[$i] = ['declared' => 0, 'hit' => 0, 'pips' => [], 'dollars' => []];

        $s3Pnls = []; $s3EntrySlips = []; $s3ExitSlips = []; $s3Commission = 0; $s3Swap = 0; $s3Wins = 0; $s3Losses = 0;
        $s3Breakevens = 0;
        // Reports' 4-Stage Summary (period-level, not per-trade) needs the
        // planned $ target that a winning/losing trade was ORIGINALLY
        // pointed at — Stage 2's own max_profit/max_loss, split by which
        // side of the outcome the trade actually landed on. Never re-derives
        // a number Stage 2 doesn't already have.
        $s3WinnerPlannedProfits = []; $s3LoserPlannedLosses = [];
        $s4ReconciledCount = 0; $s4HoldingTimes = [];
        // Stage 4 ("what actually happened in market") is gated on the
        // confirmed is_reconciled flag throughout this file — these actual
        // win-rate/RR/risk/slippage figures follow that same rule rather
        // than falling back to Stage 3's manual/self-reported outcome, so a
        // period with zero MT5-reconciled trades correctly shows a "pending
        // reconciliation" empty state instead of a number that only looks
        // broker-verified.
        $s4ReconciledWins = 0; $s4ReconciledLosses = 0; $s4ReconciledRiskPcts = [];
        $s4RealizedWinRs = []; $s4EntrySlipRs = [];
        // Reconciled Adherence / Broker Timeframe / Actual Broker Instruments
        // (Rows 1, 5, 6) — all gated on is_reconciled, same as everything
        // else in Stage 4.
        $s4ReconciledAdherentCount = 0; $s4ReconciledAdherentComputable = 0;
        $s4Styles = []; $s4Instruments = [];
        // Execution Adherence / Executed Risk / Executed RR / Executed
        // Timeframe (Rows 1-3, 6 of Stage 3) — NOT gated on reconciliation;
        // these read Stage 3's own manually-confirmed outcome.
        $s3ExecutionAdherentCount = 0; $s3ExecutionAdherentComputable = 0;
        $s3ExecutedRiskPcts = []; $s3RMultiples = []; $s3Styles = [];
        // Manually-captured actual instrument/timeframe (2026-07-27 finishing
        // pass, item 2) — Stage 3's own distributions, plus Stage 4's carried-
        // through timeframe and the instrument broker-confirmation tally.
        $s3ActualInstruments = []; $s3ActualTimeframes = [];
        $s4ActualTimeframes = [];
        $s4InstrumentConfirmedCount = 0; $s4InstrumentConfirmedComputable = 0;

        $gcRrMismatchCount = 0; $gcRrMismatchMags = [];
        $gcRiskPctMismatchCount = 0; $gcRiskPctMismatchMags = [];
        // "Calculator Adherence" — % of trades whose SAVED calculator
        // numbers (risk %, R:R) are internally geometrically consistent
        // with their own entry/SL/TP prices, i.e. NOT flagged by either
        // geometric_consistency mismatch. Distinct from Stage 1's "Strategy
        // Adherence" (whether the trade stayed inside the STRATEGY's
        // declared risk limit) — this instead checks the calculator entry
        // itself for internal consistency.
        $gcConsistentCount = 0;

        // Rule Adherence (Dashboard's "Strategy Performance" card,
        // 2026-08-12) — buckets each trade's already-canonical
        // stage3.effective_pnl into "followed rules" vs. "broke rules" by
        // reusing the exact same signal strategy_adherence_pct below is
        // built from (stage1.risk_overage === null, contract_constrained
        // trades excluded entirely — not a behavioral violation, see that
        // field's own doc comment). Not a new adherence formula: a trade
        // with no strategy attached (stage1 unavailable) has no rule to
        // measure and is counted in neither bucket.
        $ruleFollowedPnl = 0.0; $ruleFollowedCount = 0;
        $ruleBrokenPnl = 0.0; $ruleBrokenCount = 0;

        // Strategy Distribution (Stage 1) — which of the user's strategies
        // each trade in this period actually belongs to, bulk-fetched once
        // rather than per-trade. Trades with no strategy attached are
        // grouped under 'No Strategy'.
        $tradeStrategyNames = [];
        $tidPlaceholders = implode(',', array_fill(0, count($tradeIds), '?'));
        $stStmt = $db->prepare("
            SELECT t.id AS trade_id, TRIM(s.name) AS strategy_name
            FROM trades t
            LEFT JOIN strategy_versions sv ON sv.id = t.strategy_version_id
            LEFT JOIN strategies s ON s.id = sv.strategy_id
            WHERE t.id IN ($tidPlaceholders)
        ");
        $stStmt->execute(array_values($tradeIds));
        foreach ($stStmt->fetchAll(PDO::FETCH_ASSOC) as $stRow) {
            $tradeStrategyNames[$stRow['trade_id']] = $stRow['strategy_name'] ?? 'No Strategy';
        }
        $s1StrategyDist = [];

        // Full raw stage1-4 payload kept per highlight (not just a score) so
        // Reports' consolidated highlight card can render each one directly
        // without a second per-trade lookup.
        //
        // "Most disciplined" replaces the deleted deviation_score/"Plan vs.
        // Reality Gap" concept (see computeFourStageRaw()'s doc comment for
        // why that was removed rather than fixed) — it is deliberately built
        // only from real, already-computed adherence signals: how far actual
        // risk% ran over the strategy's declared limit (stage1's
        // risk_overage, when a strategy is attached) plus whether the
        // trade's own saved R:R/risk% geometrically matches its entry/SL/TP
        // prices (geometric_consistency's mismatch flags). Lower is more
        // disciplined; 0 means no overage and no mismatches at all.
        $mostDisciplinedTrade = null; $mostDisciplinedScore = PHP_FLOAT_MAX;
        $largestWinTrade = null; $largestWinPnl = -PHP_FLOAT_MAX;
        $largestLossTrade = null; $largestLossPnl = PHP_FLOAT_MAX;

        // Performance fix (2026-08-18): batch-fetch every trade row this
        // period needs in ONE query (reusing the same "?" placeholder
        // batching technique already used for $tradeStrategyNames just
        // above) instead of letting computeFourStageRaw() issue its own
        // "SELECT * FROM trades WHERE id = :tradeId" once per trade inside
        // the loop below. Confirmed live this was a real, significant cost
        // — a 269-trade window (Trader Profile's Growth Timeline) issued
        // 269 separate single-row trade queries here alone. Correctness is
        // unaffected: this is the exact same WHERE id IN (...) set
        // computeFourStageRaw() would otherwise fetch one row at a time,
        // read once, same request, no writes happen in between.
        $tradesById = [];
        $tradeRowsStmt = $db->prepare("SELECT * FROM trades WHERE id IN ({$tidPlaceholders})");
        $tradeRowsStmt->execute(array_values($tradeIds));
        foreach ($tradeRowsStmt->fetchAll(PDO::FETCH_ASSOC) as $tRow) {
            $tradesById[$tRow['id']] = $tRow;
        }

        foreach ($tradeIds as $tid) {
            $raw = self::computeFourStageRaw($tid, false, $tradesById[$tid] ?? null);
            if (isset($raw['error'])) continue;

            $stratName = $tradeStrategyNames[$tid] ?? 'No Strategy';
            $s1StrategyDist[$stratName] = ($s1StrategyDist[$stratName] ?? 0) + 1;

            $gc = $raw['geometric_consistency'];
            if (!$gc['rr_mismatch_flag'] && !$gc['risk_pct_mismatch_flag']) $gcConsistentCount++;
            $riskOverage = (!empty($raw['stage1']['available']) && $raw['stage1']['risk_overage'] !== null) ? abs($raw['stage1']['risk_overage']) : 0;
            $disciplineScore = $riskOverage + (!empty($gc['rr_mismatch_flag']) ? 1 : 0) + (!empty($gc['risk_pct_mismatch_flag']) ? 1 : 0);
            if ($disciplineScore < $mostDisciplinedScore) { $mostDisciplinedScore = $disciplineScore; $mostDisciplinedTrade = $raw; }

            if ($raw['stage3']['effective_pnl'] !== null) {
                $pnl = $raw['stage3']['effective_pnl'];
                if ($pnl > 0 && $pnl > $largestWinPnl) { $largestWinPnl = $pnl; $largestWinTrade = $raw; }
                if ($pnl < 0 && $pnl < $largestLossPnl) { $largestLossPnl = $pnl; $largestLossTrade = $raw; }
            }

            if ($raw['stage1']['available']) {
                $s1AvailableCount++;
                if ($raw['stage1']['risk_pct'] !== null) $s1RiskPcts[] = $raw['stage1']['risk_pct'];
                if ($raw['stage1']['risk_reward'] !== null) $s1Rrs[] = $raw['stage1']['risk_reward'];
                if ($raw['stage1']['max_loss'] !== null) $s1MaxLosses[] = $raw['stage1']['max_loss'];
                if ($raw['stage1']['max_profit'] !== null) $s1MaxProfits[] = $raw['stage1']['max_profit'];
                if ($raw['stage1']['max_loss_pips'] !== null) $s1MaxLossPips[] = $raw['stage1']['max_loss_pips'];
                if ($raw['stage1']['max_profit_pips'] !== null) $s1MaxProfitPips[] = $raw['stage1']['max_profit_pips'];
                if ($raw['stage1']['risk_overage'] !== null) $s1RiskOverages[] = $raw['stage1']['risk_overage'];
                if (!empty($raw['stage1']['contract_constrained'])) $s1ContractConstrainedCount++;
                if ($raw['stage1']['rr_mismatch'] !== null) $s1RrMismatches[] = $raw['stage1']['rr_mismatch'];
                if (!empty($raw['stage1']['instrument'])) $s1Instruments[$raw['stage1']['instrument']] = ($s1Instruments[$raw['stage1']['instrument']] ?? 0) + 1;
                if (!empty($raw['stage1']['direction'])) $s1Directions[$raw['stage1']['direction']] = ($s1Directions[$raw['stage1']['direction']] ?? 0) + 1;
                if (!empty($raw['stage1']['session'])) $s1Sessions[$raw['stage1']['session']] = ($s1Sessions[$raw['stage1']['session']] ?? 0) + 1;
                if (!empty($raw['stage1']['timeframes'])) $s1Timeframes[$raw['stage1']['timeframes']] = ($s1Timeframes[$raw['stage1']['timeframes']] ?? 0) + 1;
                if (!empty($raw['stage1']['trading_style'])) $s1Styles[$raw['stage1']['trading_style']] = ($s1Styles[$raw['stage1']['trading_style']] ?? 0) + 1;
                if ($raw['stage1']['session_adherent'] !== null) {
                    $s1SessionAdherentComputable++;
                    if ($raw['stage1']['session_adherent']) $s1SessionAdherentCount++;
                }
                if ($raw['stage1']['instrument_adherent'] !== null) {
                    $s1InstrumentAdherentComputable++;
                    if ($raw['stage1']['instrument_adherent']) $s1InstrumentAdherentCount++;
                }

                // Rule Adherence bucket — same exclusion as
                // strategy_adherence_pct below (contract_constrained trades
                // judged on neither side), same followed/broke signal
                // (risk_overage === null), applied to the trade's own
                // already-canonical effective_pnl.
                if (empty($raw['stage1']['contract_constrained']) && $raw['stage3']['effective_pnl'] !== null) {
                    if ($raw['stage1']['risk_overage'] === null) {
                        $ruleFollowedPnl += $raw['stage3']['effective_pnl'];
                        $ruleFollowedCount++;
                    } else {
                        $ruleBrokenPnl += $raw['stage3']['effective_pnl'];
                        $ruleBrokenCount++;
                    }
                }
            }

            $s2 = $raw['stage2'];
            if ($s2['risk_pct'] !== null) $s2RiskPcts[] = $s2['risk_pct'];
            if ($s2['risk_reward'] !== null) $s2Rrs[] = $s2['risk_reward'];
            if ($s2['lots'] !== null) $s2Lots[] = $s2['lots'];
            if ($s2['max_loss'] !== null) $s2MaxLosses[] = $s2['max_loss'];
            if ($s2['max_profit'] !== null) $s2MaxProfits[] = $s2['max_profit'];
            if ($s2['max_loss_pips'] !== null) $s2MaxLossPips[] = $s2['max_loss_pips'];
            if (!empty($s2['instrument'])) $s2Instruments[$s2['instrument']] = ($s2Instruments[$s2['instrument']] ?? 0) + 1;
            if (!empty($s2['direction'])) $s2Directions[$s2['direction']] = ($s2Directions[$s2['direction']] ?? 0) + 1;
            if (!empty($s2['session'])) $s2Sessions[$s2['session']] = ($s2Sessions[$s2['session']] ?? 0) + 1;
            if (!empty($s2['timeframe'])) $s2Timeframes[$s2['timeframe']] = ($s2Timeframes[$s2['timeframe']] ?? 0) + 1;
            foreach ($s2['tp_levels'] as $lvl) {
                $n = $lvl['level'];
                $tpLevelUsage[$n]['declared']++;
                if ($lvl['pips_from_entry'] !== null) $tpLevelUsage[$n]['pips'][] = $lvl['pips_from_entry'];
                if ($lvl['dollar_amount'] !== null) $tpLevelUsage[$n]['dollars'][] = $lvl['dollar_amount'];
            }

            if ($raw['stage3']['effective_pnl'] !== null) $s3Pnls[] = $raw['stage3']['effective_pnl'];
            if ($raw['stage3']['entry_slippage'] !== null) $s3EntrySlips[] = $raw['stage3']['entry_slippage'];
            if ($raw['stage3']['exit_slippage'] !== null) $s3ExitSlips[] = $raw['stage3']['exit_slippage'];
            $s3Commission += (float)($raw['stage3']['commission'] ?? 0);
            $s3Swap += (float)($raw['stage3']['swap'] ?? 0);
            if ($raw['stage3']['win_loss'] === 'win') {
                $s3Wins++;
                if ($s2['max_profit'] !== null) $s3WinnerPlannedProfits[] = $s2['max_profit'];
            } elseif ($raw['stage3']['win_loss'] === 'loss') {
                $s3Losses++;
                if ($s2['max_loss'] !== null) $s3LoserPlannedLosses[] = $s2['max_loss'];
            } elseif ($raw['stage3']['win_loss'] === 'breakeven') {
                $s3Breakevens++;
            }
            if ($raw['stage3']['tp_level_hit'] !== null) $tpLevelUsage[$raw['stage3']['tp_level_hit']]['hit']++;

            // Execution Adherence / Executed Risk / Executed RR / Executed
            // Timeframe — Stage 3's own manually-confirmed-outcome
            // aggregates, not gated on broker reconciliation.
            if ($raw['stage3']['execution_adherent'] !== null) {
                $s3ExecutionAdherentComputable++;
                if ($raw['stage3']['execution_adherent']) $s3ExecutionAdherentCount++;
            }
            if ($raw['stage3']['entry_adherent'] !== null) {
                $s3EntryPrecisionComputable++;
                if ($raw['stage3']['entry_adherent']) $s3EntryPrecisionCount++;
            }
            if ($raw['stage3']['sl_adherent'] !== null) {
                $s3StopPrecisionComputable++;
                if ($raw['stage3']['sl_adherent']) $s3StopPrecisionCount++;
            }
            // Lot Size Precision — planned (Stage 2) vs manually-confirmed
            // (Stage 3) position size, within CALC_LOT_TOLERANCE_RATIO. A
            // genuine Stage2-vs-Stage3 execution gap (distinct from Over-Risk
            // Occurrences, which is Stage1-vs-Stage2 declared-vs-planned) —
            // exposed standalone here rather than only folded into the
            // combined execution_adherent boolean.
            if ($raw['stage3']['lot_adherent'] !== null) {
                $s3LotPrecisionComputable++;
                if ($raw['stage3']['lot_adherent']) $s3LotPrecisionCount++;
            }
            // TP Precision — of WINNING trades with a recorded exit price,
            // % whose exit landed on one of the declared TP levels (reuses
            // tp_level_hit, already computed in computeFourStageRaw() but
            // never aggregated on its own before). A trade only counts as
            // "computable" once it has an actual exit price — win_loss alone
            // doesn't guarantee that (see stage3's effective_pnl fallback
            // cascade), so this can't just reuse $s3ExecutionAdherentComputable.
            if ($raw['stage3']['win_loss'] === 'win' && $raw['stage3']['actual_exit_price'] !== null) {
                $s3TpPrecisionComputable++;
                if ($raw['stage3']['tp_level_hit'] !== null) $s3TpPrecisionCount++;
            }
            if ($raw['stage3']['executed_risk_pct'] !== null) $s3ExecutedRiskPcts[] = $raw['stage3']['executed_risk_pct'];
            if ($raw['stage3']['r_multiple'] !== null) $s3RMultiples[] = $raw['stage3']['r_multiple'];
            if (!empty($raw['stage3']['hold_style_bucket'])) $s3Styles[$raw['stage3']['hold_style_bucket']] = ($s3Styles[$raw['stage3']['hold_style_bucket']] ?? 0) + 1;
            // Manually-captured actual instrument/timeframe (Row 5/6, Stage 3).
            if (!empty($raw['stage3']['actual_instrument'])) $s3ActualInstruments[$raw['stage3']['actual_instrument']] = ($s3ActualInstruments[$raw['stage3']['actual_instrument']] ?? 0) + 1;
            if (!empty($raw['stage3']['actual_timeframe'])) $s3ActualTimeframes[$raw['stage3']['actual_timeframe']] = ($s3ActualTimeframes[$raw['stage3']['actual_timeframe']] ?? 0) + 1;

            if ($raw['stage4']['reconciled']) {
                $s4ReconciledCount++;
                if ($raw['stage3']['win_loss'] === 'win') $s4ReconciledWins++;
                elseif ($raw['stage3']['win_loss'] === 'loss') $s4ReconciledLosses++;
                // Actual Broker Risk (Row 2, Stage 4) — broker-lot-size-
                // scaled, NOT Stage 2's own planned risk_pct (that was the
                // bug: this used to just echo the plan for whichever trades
                // happened to be reconciled, never actually verifying
                // against broker_lot_size). See computeFourStageRaw()'s
                // actual_broker_risk_pct for the corrected computation.
                if ($raw['stage4']['actual_broker_risk_pct'] !== null) $s4ReconciledRiskPcts[] = $raw['stage4']['actual_broker_risk_pct'];
                if ($raw['stage4']['reconciled_adherent'] !== null) {
                    $s4ReconciledAdherentComputable++;
                    if ($raw['stage4']['reconciled_adherent']) $s4ReconciledAdherentCount++;
                }
                if (!empty($raw['stage4']['hold_style_bucket'])) $s4Styles[$raw['stage4']['hold_style_bucket']] = ($s4Styles[$raw['stage4']['hold_style_bucket']] ?? 0) + 1;
                if (!empty($raw['instrument'])) $s4Instruments[$raw['instrument']] = ($s4Instruments[$raw['instrument']] ?? 0) + 1;
                if (!empty($raw['stage4']['actual_timeframe'])) $s4ActualTimeframes[$raw['stage4']['actual_timeframe']] = ($s4ActualTimeframes[$raw['stage4']['actual_timeframe']] ?? 0) + 1;
                if ($raw['stage4']['instrument_confirmed'] !== null) {
                    $s4InstrumentConfirmedComputable++;
                    if ($raw['stage4']['instrument_confirmed']) $s4InstrumentConfirmedCount++;
                }
                // Realized R-multiple of winners only ("Actual RR (realized
                // avg)" — the vernacular "I made 2.3R" always refers to a
                // win's size relative to the $ risk that was on the table,
                // not a signed average across wins and losses).
                if ($raw['stage3']['win_loss'] === 'win' && $raw['stage3']['effective_pnl'] !== null && $s2['max_loss'] !== null && $s2['max_loss'] > 0) {
                    $s4RealizedWinRs[] = $raw['stage3']['effective_pnl'] / $s2['max_loss'];
                }
                // Entry slippage expressed in R-multiples (fraction of the
                // planned SL distance), sign-adjusted so adverse slippage
                // (paid more on a long, sold lower on a short) is always
                // negative — matches the everyday "-0.18R" convention.
                if ($raw['stage3']['entry_slippage'] !== null && $s2['entry_price'] !== null && $s2['sl_price'] !== null) {
                    $slDist = abs($s2['entry_price'] - $s2['sl_price']);
                    if ($slDist > 0) {
                        $sign = ($raw['direction'] === 'short') ? 1 : -1;
                        $s4EntrySlipRs[] = $sign * ($raw['stage3']['entry_slippage'] / $slDist);
                    }
                }
            }
            if ($raw['stage4']['holding_time_minutes'] !== null) $s4HoldingTimes[] = $raw['stage4']['holding_time_minutes'];

            $gc = $raw['geometric_consistency'];
            if ($gc['rr_mismatch_flag']) { $gcRrMismatchCount++; $gcRrMismatchMags[] = abs($gc['rr_mismatch']); }
            if ($gc['risk_pct_mismatch_flag']) { $gcRiskPctMismatchCount++; $gcRiskPctMismatchMags[] = abs($gc['risk_pct_mismatch']); }
        }

        $avg = fn($arr) => !empty($arr) ? array_sum($arr) / count($arr) : null;
        $n = count($tradeIds);
        $decided = $s3Wins + $s3Losses;

        // Only levels at least one trade in the period actually declared —
        // keeps a 1-TP-only period from padding out 9 empty rows.
        $tpLevelUsageOut = [];
        foreach ($tpLevelUsage as $lvl => $data) {
            if ($data['declared'] === 0) continue;
            $tpLevelUsageOut[$lvl] = [
                'declared_count' => $data['declared'], 'hit_count' => $data['hit'],
                'avg_pips_from_entry' => $avg($data['pips']), 'avg_dollar_amount' => $avg($data['dollars']),
            ];
        }

        // Strategy Distribution, sorted most-traded first — dict of
        // strategy name => trade count, share-of-period computed at render
        // time by the caller (same convention already used for
        // instruments/directions/sessions above).
        arsort($s1StrategyDist);
        arsort($s1Styles); arsort($s3Styles); arsort($s4Styles); arsort($s4Instruments);
        arsort($s3ActualInstruments); arsort($s3ActualTimeframes); arsort($s4ActualTimeframes);
        arsort($s1Timeframes); arsort($s2Timeframes);

        return [
            'trades_in_period' => $n,
            'stage1' => [
                'avg_declared_risk_pct' => $avg($s1RiskPcts), 'avg_declared_rr' => $avg($s1Rrs),
                'avg_declared_max_loss' => $avg($s1MaxLosses), 'avg_declared_max_profit' => $avg($s1MaxProfits),
                'avg_declared_max_loss_pips' => $avg($s1MaxLossPips), 'avg_declared_max_profit_pips' => $avg($s1MaxProfitPips),
                'trades_over_declared_risk' => count($s1RiskOverages),
                'avg_risk_overage' => $avg($s1RiskOverages),
                // Item 7 (2026-07-27): trades where the declared target was
                // never mathematically reachable at this instrument's
                // broker-minimum lot size — kept structurally separate from
                // trades_over_declared_risk above (behavioral over-risk).
                'trades_contract_constrained' => $s1ContractConstrainedCount,
                'contract_constrained_pct' => $s1AvailableCount > 0 ? round(($s1ContractConstrainedCount / $s1AvailableCount) * 100, 1) : null,
                'trades_with_rr_mismatch' => count($s1RrMismatches),
                'instruments' => $s1Instruments, 'directions' => $s1Directions, 'sessions' => $s1Sessions,
                // "Strategy Adherence" — of the trades where a strategy's
                // declared edge could be simulated at all (stage1
                // available), what % did NOT exceed that strategy's own
                // declared risk limit. Null (not 0/100) when no trade in
                // this period has a strategy attached to check against.
                // Item 7 (2026-07-27): Contract-Constrained trades are
                // excluded from BOTH the numerator and the denominator here
                // — adherence is a behavioral question, and there was no
                // behavior to measure on a trade where no valid lot size
                // could have hit the target in the first place.
                'strategy_adherence_pct' => ($s1AvailableCount - $s1ContractConstrainedCount) > 0 ? round((($s1AvailableCount - $s1ContractConstrainedCount - count($s1RiskOverages)) / ($s1AvailableCount - $s1ContractConstrainedCount)) * 100, 1) : null,
                'strategy_adherence_n' => ($s1AvailableCount - $s1ContractConstrainedCount) - count($s1RiskOverages),
                'strategy_adherence_m' => $s1AvailableCount - $s1ContractConstrainedCount,
                'strategy_distribution' => $s1StrategyDist,
                // Declared Timeframe (Row 6) — distribution of the attached
                // strategy's own trading_style. Empty when no trade this
                // period has a strategy attached (same gate as Strategy
                // Adherence above). Kept computed but unused per Reports'
                // own note — see 'timeframes' below for the real Row 6 data.
                'style_distribution' => $s1Styles,
                // Declared Timeframe (Row 6) — REAL data as of 2026-07-27
                // item 2, sourced from Strategy Builder's now-mandatory
                // timeframes_traded field (see the 'timeframes' field on
                // computeFourStageRaw()'s stage1). Empty when no trade this
                // period has a strategy attached.
                'timeframes' => $s1Timeframes,
            ],
            // Dashboard's "Strategy Performance" card (2026-08-12) — real $
            // followed vs. broken, not a percentage. broken_pnl < 0 is an
            // actual realized loss from rule-breaking trades; the caller
            // must never invent an opportunity-cost delta this data doesn't
            // support (see CLAUDE.md's Validation Workflow / Dashboard
            // cleanup notes) — only report what these trades actually did.
            'rule_adherence' => [
                'followed_pnl' => round($ruleFollowedPnl, 2), 'followed_count' => $ruleFollowedCount,
                'broken_pnl' => round($ruleBrokenPnl, 2), 'broken_count' => $ruleBrokenCount,
                'evaluable_count' => $ruleFollowedCount + $ruleBrokenCount,
            ],
            'stage2' => [
                'avg_risk_pct' => $avg($s2RiskPcts), 'avg_risk_reward' => $avg($s2Rrs), 'avg_lots' => $avg($s2Lots),
                'avg_max_loss' => $avg($s2MaxLosses), 'avg_max_profit' => $avg($s2MaxProfits), 'avg_max_loss_pips' => $avg($s2MaxLossPips),
                'instruments' => $s2Instruments, 'directions' => $s2Directions, 'sessions' => $s2Sessions,
                // Planned Timeframe (Row 6) — REAL data as of 2026-07-27
                // item 2, from the Calculator's own planned_timeframe field.
                'timeframes' => $s2Timeframes,
                'tp_level_usage' => $tpLevelUsageOut,
                // "Calculator Adherence" — % of trades whose saved risk%/R:R
                // geometrically reconcile with their own entry/SL/TP prices
                // (neither geometric_consistency mismatch flag tripped).
                // Distinct from Strategy Adherence above (which checks
                // against the STRATEGY's declared limit, not internal
                // consistency of the calculator's own saved numbers).
                'calculator_adherence_pct' => $n > 0 ? round(($gcConsistentCount / $n) * 100, 1) : null,
                'calculator_adherence_n' => $gcConsistentCount,
                'calculator_adherence_m' => $n,
                'over_risk_occurrences' => count($s1RiskOverages),
                'over_risk_pct' => $n > 0 ? round((count($s1RiskOverages) / $n) * 100, 1) : 0,
                // Item 7 (2026-07-27): surfaced alongside Over-Risk
                // Occurrences so Reports can show the two side by side
                // without conflating them — see stage1.trades_contract_constrained.
                'contract_constrained_occurrences' => $s1ContractConstrainedCount,
                'contract_constrained_occurrences_pct' => $n > 0 ? round(($s1ContractConstrainedCount / $n) * 100, 1) : 0,
            ],
            'stage3' => [
                'net_pnl' => array_sum($s3Pnls), 'avg_entry_slippage' => $avg($s3EntrySlips), 'avg_exit_slippage' => $avg($s3ExitSlips),
                'total_commission' => $s3Commission, 'total_swap' => $s3Swap,
                'win_rate' => $decided > 0 ? round(($s3Wins / $decided) * 100, 1) : null,
                'wins' => $s3Wins, 'losses' => $s3Losses, 'breakevens' => $s3Breakevens,
                'wins_pct' => $n > 0 ? round(($s3Wins / $n) * 100, 1) : 0,
                'losses_pct' => $n > 0 ? round(($s3Losses / $n) * 100, 1) : 0,
                'breakevens_pct' => $n > 0 ? round(($s3Breakevens / $n) * 100, 1) : 0,
                'avg_planned_profit_winners' => $avg($s3WinnerPlannedProfits),
                'avg_planned_loss_losers' => $avg($s3LoserPlannedLosses),
                // Row 1 — Execution Adherence: % of trades where BOTH actual
                // entry price (0.1% tolerance) AND actual lot size (5%
                // tolerance) matched Stage 2's plan. Null-denominator-safe:
                // "_m" only counts trades where both sides were computable.
                'execution_adherence_pct' => $s3ExecutionAdherentComputable > 0 ? round(($s3ExecutionAdherentCount / $s3ExecutionAdherentComputable) * 100, 1) : null,
                'execution_adherence_n' => $s3ExecutionAdherentCount, 'execution_adherence_m' => $s3ExecutionAdherentComputable,
                // Row 2 — Executed Risk: avg of Stage 2's planned risk%,
                // ratio-scaled by actual-vs-planned lot size.
                'avg_executed_risk_pct' => $avg($s3ExecutedRiskPcts),
                // Row 3 — Executed RR: avg signed R-multiple across wins AND
                // losses (effective_pnl ÷ planned max loss) — distinct from
                // Stage 4's actual_rr_realized (winners-only, reconciled-only).
                'avg_r_multiple' => $avg($s3RMultiples),
                // Row 6 — Executed Timeframe: hold-duration style bucket from
                // opened_at/closed_at (app-level, not broker-gated).
                'style_distribution' => $s3Styles,
                // Row 5/6 — manually captured at Draft Resolution
                // (2026-07-27 finishing pass, item 2). Empty on periods with
                // only legacy trades resolved before these columns existed.
                'actual_instruments' => $s3ActualInstruments,
                'actual_timeframes' => $s3ActualTimeframes,
            ],
            'stage4' => [
                'reconciled_count' => $s4ReconciledCount, 'reconciled_pct' => $n > 0 ? round(($s4ReconciledCount / $n) * 100, 1) : 0,
                'avg_holding_time_minutes' => $avg($s4HoldingTimes),
                // Everything below is gated on is_reconciled=1 (MT5-verified
                // ground truth) — null throughout whenever reconciled_count
                // is 0, rather than silently falling back to Stage 3's
                // self-reported outcome. See CLAUDE.md re: this project has
                // zero reconciled trades in the live DB as of this build, so
                // these will correctly render an empty/pending state until
                // real broker reconciliation data exists.
                'actual_win_rate' => ($s4ReconciledWins + $s4ReconciledLosses) > 0 ? round(($s4ReconciledWins / ($s4ReconciledWins + $s4ReconciledLosses)) * 100, 1) : null,
                'actual_win_rate_n' => $s4ReconciledWins, 'actual_win_rate_m' => $s4ReconciledWins + $s4ReconciledLosses,
                'actual_rr_realized' => $avg($s4RealizedWinRs),
                // "Actual Broker Risk" (Row 2) — now broker_lot_size-scaled
                // (see computeFourStageRaw()'s actual_broker_risk_pct); this
                // key previously held Stage 2's own planned risk_pct for
                // whichever trades happened to be reconciled, which wasn't
                // actually broker-verified. Key name kept as-is (only the
                // computation changed) since this is the one call site
                // (reports.php's Stage 4 "Actual Risk"/"Actual Broker Risk"
                // tile) already reading it.
                'actual_risk_avg' => $avg($s4ReconciledRiskPcts),
                'avg_slippage_r' => $avg($s4EntrySlipRs),
                // No spread-cost data source exists anywhere in this schema
                // (no dedicated column, and mql5_trades.event_payload has
                // never been populated in this DB) — always null, not
                // approximated from another field. See CLAUDE.md.
                'avg_spread_cost_r' => null,
                'reconciliation_accuracy_pct' => $n > 0 ? round(($s4ReconciledCount / $n) * 100, 1) : 0,
                // Row 1 — Reconciled Adherence: of RECONCILED trades, % whose
                // broker-verified lot size matched the plan within 5%.
                'reconciled_adherence_pct' => $s4ReconciledAdherentComputable > 0 ? round(($s4ReconciledAdherentCount / $s4ReconciledAdherentComputable) * 100, 1) : null,
                'reconciled_adherence_n' => $s4ReconciledAdherentCount, 'reconciled_adherence_m' => $s4ReconciledAdherentComputable,
                // Row 5 — Actual Broker Instruments: instrument distribution
                // among reconciled trades only (instrument identity itself
                // never drifts between stages — this just scopes the same
                // real field to the reconciled subset).
                'instruments' => $s4Instruments,
                // Row 6 — Broker Timeframe: hold-duration style bucket from
                // MT5-verified broker_open_time/broker_close_time. Always
                // empty while reconciled_count is 0 (true of every account
                // in this DB as of this build — see CLAUDE.md).
                'style_distribution' => $s4Styles,
                // Chart timeframe as manually captured at Stage 3, carried
                // through for reconciled trades only — NOT broker-verified
                // (see computeFourStageRaw()'s instrument_confirmed comment;
                // MT5 has no chart-timeframe field to check this against).
                'actual_timeframes' => $s4ActualTimeframes,
                // % of reconciled trades with a manually-captured instrument
                // where that instrument matches the broker-matched
                // trades.instrument — the one field reconciliation CAN
                // validate or challenge, per item 2's design.
                'instrument_confirmed_pct' => $s4InstrumentConfirmedComputable > 0 ? round(($s4InstrumentConfirmedCount / $s4InstrumentConfirmedComputable) * 100, 1) : null,
                'instrument_confirmed_n' => $s4InstrumentConfirmedCount,
                'instrument_confirmed_m' => $s4InstrumentConfirmedComputable,
            ],
            'geometric_consistency' => [
                'rr_mismatch_count' => $gcRrMismatchCount, 'rr_mismatch_pct' => $n > 0 ? round(($gcRrMismatchCount / $n) * 100, 1) : 0,
                'avg_rr_mismatch_magnitude' => $avg($gcRrMismatchMags),
                'risk_pct_mismatch_count' => $gcRiskPctMismatchCount, 'risk_pct_mismatch_pct' => $n > 0 ? round(($gcRiskPctMismatchCount / $n) * 100, 1) : 0,
                'avg_risk_pct_mismatch_magnitude' => $avg($gcRiskPctMismatchMags),
            ],
            'most_disciplined_trade' => $mostDisciplinedTrade,
            'largest_win_trade' => $largestWinTrade,
            'largest_loss_trade' => $largestLossTrade,
            // Intent vs Reality — Execution Gap card. Every field here is
            // either a bounded %-of-trades-within-tolerance (Entry/Stop/TP/
            // Session/Instrument Precision) or a direct reuse of an
            // already-audited point-difference (Risk Drift reuses Stage 1's
            // avg_risk_overage/over_risk_pct; RR Drift is the plain
            // difference of two already-computed, already-bounded averages)
            // — deliberately none of these are unclamped ratio-of-two-
            // small-numbers formulas, which is the exact failure mode that
            // produced the impossible "24,624.48% Average Risk Used" this
            // card was rebuilt to avoid (see reports.php's Intent vs Reality
            // section for the full root-cause writeup).
            'execution_gap' => [
                'entry_precision_pct' => $s3EntryPrecisionComputable > 0 ? round(($s3EntryPrecisionCount / $s3EntryPrecisionComputable) * 100, 1) : null,
                'entry_precision_n' => $s3EntryPrecisionCount, 'entry_precision_m' => $s3EntryPrecisionComputable,
                'stop_precision_pct' => $s3StopPrecisionComputable > 0 ? round(($s3StopPrecisionCount / $s3StopPrecisionComputable) * 100, 1) : null,
                'stop_precision_n' => $s3StopPrecisionCount, 'stop_precision_m' => $s3StopPrecisionComputable,
                'tp_precision_pct' => $s3TpPrecisionComputable > 0 ? round(($s3TpPrecisionCount / $s3TpPrecisionComputable) * 100, 1) : null,
                'tp_precision_n' => $s3TpPrecisionCount, 'tp_precision_m' => $s3TpPrecisionComputable,
                // Lot Size Precision — Row added 2026-07-27 refinement pass,
                // completes tolerance-system coverage across Entry/Stop/TP/
                // Lot Size per the explicit ask (see CALC_LOT_TOLERANCE_RATIO).
                'lot_precision_pct' => $s3LotPrecisionComputable > 0 ? round(($s3LotPrecisionCount / $s3LotPrecisionComputable) * 100, 1) : null,
                'lot_precision_n' => $s3LotPrecisionCount, 'lot_precision_m' => $s3LotPrecisionComputable,
                'session_precision_pct' => $s1SessionAdherentComputable > 0 ? round(($s1SessionAdherentCount / $s1SessionAdherentComputable) * 100, 1) : null,
                'session_precision_n' => $s1SessionAdherentCount, 'session_precision_m' => $s1SessionAdherentComputable,
                'instrument_precision_pct' => $s1InstrumentAdherentComputable > 0 ? round(($s1InstrumentAdherentCount / $s1InstrumentAdherentComputable) * 100, 1) : null,
                'instrument_precision_n' => $s1InstrumentAdherentCount, 'instrument_precision_m' => $s1InstrumentAdherentComputable,
            ],
        ];
    }

    /**
     * Trading-style drift — flags when actual executed trades on a strategy
     * are drifting away from the strategy's own declared trading_style
     * (strategies.trading_style: scalper/day_trader/swing/position), using
     * average hold time and average TP distance. Reports a direction +
     * magnitude, not a graded score — this is a flag for the trader to
     * notice, not something with a pass/fail threshold.
     *
     * Hold time is bucketed against typical ranges for each declared style
     * (scalper <2h, day_trader <24h, swing 1-5 days, position >5 days) and
     * compared to the period's own median. TP distance is compared against
     * this strategy version's own historical baseline (its first 20
     * trades, all-time) rather than a fixed threshold, since "wide" is
     * instrument-relative — a genuine cross-instrument distance comparison
     * would need the per-instrument pip table that doesn't exist
     * server-side (see buildTpLevels() above), so this stays in raw price-
     * distance-from-entry terms, which is still valid for a strategy that
     * consistently trades the same instrument(s) but not directly
     * comparable across instruments.
     */
    public static function getStyleDriftSignal($strategyVersionId, $startStr, $endStr) {
        $db = self::getConnection();

        $svStmt = $db->prepare("
            SELECT sv.strategy_id, s.trading_style
            FROM strategy_versions sv JOIN strategies s ON s.id = sv.strategy_id
            WHERE sv.id = :svId
        ");
        $svStmt->execute([':svId' => $strategyVersionId]);
        $sv = $svStmt->fetch(PDO::FETCH_ASSOC);
        if (!$sv || empty($sv['trading_style'])) return ['available' => false];
        $declaredStyle = $sv['trading_style'];

        $styleRanges = [
            'scalper'    => ['max_hours' => 2,   'label' => 'scalper (typically under 2 hours)'],
            'day_trader' => ['max_hours' => 24,  'label' => 'day trader (typically same-session, under 24 hours)'],
            'swing'      => ['max_hours' => 120, 'label' => 'swing trader (typically 1-5 days)'],
            'position'   => ['max_hours' => null,'label' => 'position trader (typically over 5 days)'],
        ];

        // Period hold times — opened_at/closed_at are when the position was
        // actually worked in the app; not broker-verified (that's Stage 4's
        // job), but a reasonable proxy for "how long this trade was open"
        // at the aggregate/drift level.
        $periodRows = $db->prepare("
            SELECT TIMESTAMPDIFF(MINUTE, opened_at, closed_at) AS hold_minutes,
                   entry_price, tp_price
            FROM trades
            WHERE strategy_version_id = :svId AND status = 'closed' AND deleted_at IS NULL
              AND (is_validation = 0 OR is_validation IS NULL)
              AND opened_at IS NOT NULL AND closed_at IS NOT NULL
              AND closed_at >= :s AND closed_at <= :e
        ");
        $periodRows->execute([':svId' => $strategyVersionId, ':s' => $startStr, ':e' => $endStr]);
        $periodTrades = $periodRows->fetchAll(PDO::FETCH_ASSOC);

        $holdMinutes = [];
        $tpDistances = [];
        foreach ($periodTrades as $t) {
            if ($t['hold_minutes'] !== null && (float)$t['hold_minutes'] >= 0) $holdMinutes[] = (float)$t['hold_minutes'];
            if ($t['entry_price'] !== null && $t['tp_price'] !== null) $tpDistances[] = abs((float)$t['tp_price'] - (float)$t['entry_price']);
        }

        if (empty($periodTrades)) return ['available' => false, 'reason' => 'No closed trades with hold-time data in this period.'];

        sort($holdMinutes);
        $medianHoldMinutes = !empty($holdMinutes) ? $holdMinutes[(int)floor((count($holdMinutes) - 1) / 2)] : null;
        $medianHoldHours = $medianHoldMinutes !== null ? round($medianHoldMinutes / 60, 2) : null;

        $holdTimeDrift = null;
        if ($medianHoldHours !== null && isset($styleRanges[$declaredStyle])) {
            $maxHours = $styleRanges[$declaredStyle]['max_hours'];
            if ($maxHours !== null && $medianHoldHours > $maxHours) {
                $holdTimeDrift = "This period's median hold time is {$medianHoldHours}h, longer than the declared {$declaredStyle} style's typical range (" . $styleRanges[$declaredStyle]['label'] . ").";
            }
        }

        // Baseline = this version's own first 20 closed trades, all-time —
        // not a fixed cross-instrument threshold.
        $baselineRows = $db->prepare("
            SELECT entry_price, tp_price FROM trades
            WHERE strategy_version_id = :svId AND status = 'closed' AND deleted_at IS NULL
              AND (is_validation = 0 OR is_validation IS NULL)
              AND entry_price IS NOT NULL AND tp_price IS NOT NULL
            ORDER BY closed_at ASC LIMIT 20
        ");
        $baselineRows->execute([':svId' => $strategyVersionId]);
        $baselineTrades = $baselineRows->fetchAll(PDO::FETCH_ASSOC);
        $baselineDistances = [];
        foreach ($baselineTrades as $t) $baselineDistances[] = abs((float)$t['tp_price'] - (float)$t['entry_price']);

        $avg = fn($arr) => !empty($arr) ? array_sum($arr) / count($arr) : null;
        $baselineAvgTpDistance = $avg($baselineDistances);
        $periodAvgTpDistance = $avg($tpDistances);

        $tpDistanceDrift = null;
        if ($baselineAvgTpDistance !== null && $baselineAvgTpDistance > 0 && $periodAvgTpDistance !== null) {
            $pctChange = round((($periodAvgTpDistance - $baselineAvgTpDistance) / $baselineAvgTpDistance) * 100, 1);
            if (abs($pctChange) >= 50) {
                $direction = $pctChange > 0 ? 'wider' : 'tighter';
                $tpDistanceDrift = "Average take-profit distance this period is " . abs($pctChange) . "% {$direction} than this strategy's own first-20-trade baseline.";
            }
        }

        return [
            'available' => true,
            'declared_style' => $declaredStyle,
            'median_hold_hours' => $medianHoldHours,
            'hold_time_drift_note' => $holdTimeDrift,
            'baseline_avg_tp_distance' => $baselineAvgTpDistance,
            'period_avg_tp_distance' => $periodAvgTpDistance,
            'tp_distance_drift_note' => $tpDistanceDrift,
        ];
    }

    // The four private methods below (calculateEdgeVsPlanDeviations,
    // calculatePlanVsOutcomeDeviations, calculateEdgeVsOutcomeDeviations,
    // generateExecutionScorecard) are never called anywhere in this codebase —
    // confirmed by grep. getFourStageComparison() above does not build a
    // 'deviations' or 'execution_scorecard' key into its return value, which is
    // why journal.php's Stage 4/Stage 5 blocks that read those keys are dead
    // code (see the comment there). Left in place per the additive-only rule
    // rather than deleted; wiring them back in is a real feature decision, not
    // made here.
    private static function calculateEdgeVsPlanDeviations($edge, $plan) {
        $deviations = [];
        
        // Risk/Reward deviation
        if (!empty($edge['risk_reward']) && $edge['risk_reward'] > 0 && !empty($plan['risk_reward_tp1']) && $plan['risk_reward_tp1'] > 0) {
            $rrDeviation = $plan['risk_reward_tp1'] - $edge['risk_reward'];
            $deviations['rr_deviation'] = [
                'absolute' => round($rrDeviation, 2),
                'percentage' => round(($rrDeviation / $edge['risk_reward']) * 100, 1),
                'edge_value' => $edge['risk_reward'],
                'plan_value' => $plan['risk_reward_tp1'],
                'status' => $rrDeviation >= 0 ? 'better_than_edge' : 'worse_than_edge'
            ];
        }
        
        // Risk percentage deviation
        if (!empty($edge['max_risk_per_trade']) && $edge['max_risk_per_trade'] > 0 && !empty($plan['risk_pct']) && $plan['risk_pct'] > 0) {
            $riskDeviation = $plan['risk_pct'] - $edge['max_risk_per_trade'];
            $deviations['risk_deviation'] = [
                'absolute' => round($riskDeviation, 2),
                'percentage' => round(($riskDeviation / $edge['max_risk_per_trade']) * 100, 1),
                'edge_value' => $edge['max_risk_per_trade'],
                'plan_value' => $plan['risk_pct'],
                'status' => $riskDeviation <= 0 ? 'within_limits' : 'exceeds_limits'
            ];
        }
        
        return $deviations;
    }
    
    private static function calculatePlanVsOutcomeDeviations($plan, $outcome) {
        $deviations = [
            'entry_slippage' => $outcome['entry_slippage'],
            'exit_slippage' => $outcome['exit_slippage']
        ];
        
        // Position size deviation
        if ($plan['lots_per_position'] > 0) {
            $sizeDeviation = ($outcome['actual_lots'] - $plan['lots_per_position']) / $plan['lots_per_position'];
            $deviations['position_size_deviation'] = [
                'absolute' => round($outcome['actual_lots'] - $plan['lots_per_position'], 2),
                'percentage' => round($sizeDeviation * 100, 1),
                'plan_value' => $plan['lots_per_position'],
                'actual_value' => $outcome['actual_lots'],
                'status' => abs($sizeDeviation) <= 0.05 ? 'within_tolerance' : 'deviated'
            ];
        }
        
        // Risk adherence
        // FIXED: max_potential_loss is already the dollar loss at the
        // trader's planned risk_pct — dividing by (risk_pct/100) again
        // double-counted risk and inflated this ratio by roughly
        // (100/risk_pct)x. A trade risking 20% that lost exactly its
        // planned $ amount previously showed ~5x "exceeded_risk" instead
        // of the correct ~1.0x "within_risk_plan". At risk_pct near 100%
        // the old formula happened to look almost right, which is why
        // this went unnoticed on some trades but not others.
        if ($outcome['broker_pnl'] < 0 && $plan['max_potential_loss'] > 0) {
            $riskAdherence = abs($outcome['broker_pnl']) / $plan['max_potential_loss'];
            $deviations['risk_adherence'] = [
                'ratio' => round($riskAdherence, 2),
                'max_acceptable_ratio' => 1.1,
                'status' => $riskAdherence <= 1.1 ? 'within_risk_plan' : 'exceeded_risk'
            ];
        }
        
        // Plan followed check
        $deviations['plan_followed'] = self::wasPlanFollowed($plan, $outcome);
        
        return $deviations;
    }
    
    private static function calculateEdgeVsOutcomeDeviations($edge, $outcome) {
        $deviations = [];
        
        // Compare outcome to edge expectations
        if (!empty($edge['avg_winner']) && $edge['avg_winner'] > 0 && $outcome['broker_pnl'] > 0) {
            $deviation = ($outcome['broker_pnl'] - $edge['avg_winner']) / $edge['avg_winner'];
            $deviations['vs_avg_winner'] = [
                'absolute' => round($outcome['broker_pnl'] - $edge['avg_winner'], 2),
                'percentage' => round($deviation * 100, 1),
                'status' => $deviation >= 0 ? 'above_average' : 'below_average'
            ];
        }
        
        if (!empty($edge['avg_loser']) && $edge['avg_loser'] < 0 && $outcome['broker_pnl'] < 0) {
            $deviation = ($outcome['broker_pnl'] - $edge['avg_loser']) / abs($edge['avg_loser']);
            $deviations['vs_avg_loser'] = [
                'absolute' => round($outcome['broker_pnl'] - $edge['avg_loser'], 2),
                'percentage' => round($deviation * 100, 1),
                'status' => $deviation <= 0 ? 'better_than_average_loss' : 'worse_than_average_loss'
            ];
        }
        
        return $deviations;
    }
    
    private static function wasPlanFollowed($plan, $outcome) {
        $score = 0;
        $checks = 0;
        
        // Check entry price adherence
        if ($plan['entry_price'] > 0 && $outcome['actual_entry_price'] > 0) {
            $checks++;
            $entryDiff = abs($outcome['actual_entry_price'] - $plan['entry_price']);
            $entryTolerance = $plan['entry_price'] * 0.001; // 0.1% tolerance
            if ($entryDiff <= $entryTolerance) $score++;
        }
        
        // Check position size adherence
        if ($plan['lots_per_position'] > 0 && $outcome['actual_lots'] > 0) {
            $checks++;
            $sizeDiff = abs($outcome['actual_lots'] - $plan['lots_per_position']);
            $sizeTolerance = $plan['lots_per_position'] * 0.05; // 5% tolerance
            if ($sizeDiff <= $sizeTolerance) $score++;
        }
        
        // Check stop loss adherence (only for losing trades)
        if ($plan['sl_price'] > 0 && $outcome['broker_pnl'] < 0 && $plan['max_potential_loss'] > 0) {
            $checks++;
            if (abs($outcome['broker_pnl']) <= ($plan['max_potential_loss'] * 1.1)) $score++;
        }
        
        $adherencePct = $checks > 0 ? ($score / $checks) * 100 : 100;
        
        return [
            'score' => round($adherencePct, 1),
            'checks_passed' => $score,
            'total_checks' => $checks,
            'grade' => self::getAdherenceGrade($adherencePct)
        ];
    }
    
    private static function generateExecutionScorecard($plan, $outcome, $deviations) {
        $planFollowed = $deviations['plan_vs_outcome']['plan_followed']['score'] ?? 0;
        
        return [
            'adherence_score' => $planFollowed,
            'grade' => self::getAdherenceGrade($planFollowed),
            'is_rule_break' => $planFollowed < 70,
            'execution_quality' => $planFollowed >= 90 ? 'excellent' : 
                                   ($planFollowed >= 75 ? 'good' : 
                                   ($planFollowed >= 50 ? 'needs_improvement' : 'poor')),
            'key_deviations' => self::extractKeyDeviations($deviations)
        ];
    }
    
    private static function getAdherenceGrade($score) {
        if ($score >= 95) return 'A+';
        if ($score >= 90) return 'A';
        if ($score >= 85) return 'A-';
        if ($score >= 80) return 'B+';
        if ($score >= 75) return 'B';
        if ($score >= 70) return 'B-';
        if ($score >= 65) return 'C+';
        if ($score >= 60) return 'C';
        if ($score >= 55) return 'C-';
        if ($score >= 50) return 'D';
        return 'F';
    }
    
    private static function extractKeyDeviations($deviations) {
        $key = [];
        
        // Check position size deviation
        if (!empty($deviations['plan_vs_outcome']['position_size_deviation']['status']) &&
            $deviations['plan_vs_outcome']['position_size_deviation']['status'] === 'deviated') {
            $key[] = 'Position size deviation: ' . 
                     $deviations['plan_vs_outcome']['position_size_deviation']['percentage'] . '%';
        }
        
        // Check risk adherence
        if (!empty($deviations['plan_vs_outcome']['risk_adherence']['status']) &&
            $deviations['plan_vs_outcome']['risk_adherence']['status'] === 'exceeded_risk') {
            $key[] = 'Risk limit exceeded';
        }
        
        // Check edge vs plan deviations
        if (!empty($deviations['edge_vs_plan']['risk_deviation']['status']) &&
            $deviations['edge_vs_plan']['risk_deviation']['status'] === 'exceeds_limits') {
            $key[] = 'Trade risk exceeds strategy limits';
        }
        
        return $key;
    }
}