<?php
/**
 * Chairman Safe Loader v4.2 FIXED
 * No recursive fetch.
 * Reads local MySQL tables directly.
 * Safe for analysis.php endpoint.
 */
class ChairmanSafeLoader {
    private static function root() {
        return dirname(__DIR__, 2);
    }

    private static function logDir() {
        $dir = dirname(__DIR__) . '/logs';
        if (!is_dir($dir)) @mkdir($dir, 0775, true);
        return $dir;
    }

    private static function log($msg) {
        @file_put_contents(self::logDir().'/chairman_gateway_v42.log', '['.date('Y-m-d H:i:s').'] '.$msg.PHP_EOL, FILE_APPEND);
    }

    private static function q($db, $s) {
        return "'" . $db->real_escape_string((string)$s) . "'";
    }

    private static function rows($db, $sql) {
        $out = array();
        $rs = @$db->query($sql);
        if ($rs) {
            while ($r = $rs->fetch_assoc()) $out[] = $r;
        }
        return $out;
    }

    private static function tableExists($db, $table) {
        $rs = @$db->query("SHOW TABLES LIKE " . self::q($db, $table));
        return $rs && $rs->num_rows > 0;
    }

    public static function status() {
        $root = self::root();
        return array(
            'status' => 'OK',
            'loader_version' => '4.5_V45_1_DEDUP',
            'mode' => 'DIRECT_DB_AUTO_BUILD_V44_LATEST_RUN',
            'root' => $root,
            'config_exists' => file_exists($root . '/config/config.php'),
            'php_version' => PHP_VERSION,
            'time' => date('c')
        );
    }

    public static function payload($raceDate, $venue='', $raceNum=0) {
        $t0 = microtime(true);
        $root = self::root();
        $config = $root . '/config/config.php';

        if (!file_exists($config)) {
            return array(
                'ok'=>false,
                'status'=>'CONFIG_NOT_FOUND',
                'message'=>'config/config.php not found',
                'path'=>$config,
                'race_date'=>$raceDate,
                'elapsed_ms'=>round((microtime(true)-$t0)*1000,2)
            );
        }

        try {
            require $config;
            if (!isset($conn) && isset($db)) $conn = $db;
            if (!isset($conn) || !($conn instanceof mysqli)) {
                return array(
                    'ok'=>false,
                    'status'=>'DB_CONNECTION_NOT_FOUND',
                    'message'=>'$conn mysqli connection not found from config/config.php',
                    'race_date'=>$raceDate,
                    'elapsed_ms'=>round((microtime(true)-$t0)*1000,2)
                );
            }
            @$conn->set_charset('utf8mb4');

            $w = array("race_date=" . self::q($conn, $raceDate));
            if ($venue !== '') $w[] = "venue=" . self::q($conn, $venue);
            if ((int)$raceNum > 0) $w[] = "race_num=" . (int)$raceNum;
            $where = implode(" AND ", $w);

            $summary = array();
            $ranking = array();
            $decision = array();

            if (self::tableExists($conn, 'wr_chairman_race_decision_v45')) {
                $decision = self::rows($conn, "SELECT d.* FROM wr_chairman_race_decision_v45 d INNER JOIN (SELECT race_date,venue,race_num,MAX(id) max_id FROM wr_chairman_race_decision_v45 WHERE {$where} GROUP BY race_date,venue,race_num) x ON d.id=x.max_id ORDER BY d.race_num ASC");
            }

            if (self::tableExists($conn, 'wr_chairman_ensemble_ai_v45')) {
                $latestV45Run = '';
                $rsLatestV45 = @$conn->query("SELECT run_key FROM wr_chairman_ensemble_ai_v45 WHERE {$where} ORDER BY created_at DESC, id DESC LIMIT 1");
                if ($rsLatestV45 && ($latestV45Row = $rsLatestV45->fetch_assoc())) $latestV45Run = (string)$latestV45Row['run_key'];
                if ($latestV45Run !== '') {
                    $ranking = self::rows($conn, "SELECT * FROM wr_chairman_ensemble_ai_v45 WHERE {$where} AND run_key='" . $conn->real_escape_string($latestV45Run) . "' ORDER BY race_num ASC, ensemble_score DESC LIMIT 500");
                }
            }

            if (!$ranking && self::tableExists($conn, 'wr_chairman_production_score_v44')) {
                $latestV44Run = '';
                $rsLatestV44 = @$conn->query("SELECT run_key FROM wr_chairman_production_score_v44 WHERE {$where} ORDER BY created_at DESC, id DESC LIMIT 1");
                if ($rsLatestV44 && ($latestV44Row = $rsLatestV44->fetch_assoc())) $latestV44Run = (string)$latestV44Row['run_key'];
                if ($latestV44Run !== '') {
                    $ranking = self::rows($conn, "SELECT * FROM wr_chairman_production_score_v44 WHERE {$where} AND run_key='" . $conn->real_escape_string($latestV44Run) . "' ORDER BY race_num ASC, race_rank ASC, calibrated_score DESC LIMIT 500");
                }
            }

            // v4.4 Auto Production: if V44 output is missing, build once from V43/V42.
            $autoProduction = array(
                'attempted'=>false,
                'status'=>'NOT_REQUIRED',
                'message'=>'Existing production output found.'
            );

            if (!$ranking) {
                $autoProduction['attempted'] = true;
                try {
                    require_once dirname(__DIR__) . '/bootstrap/chairman_v44_migration_bootstrap.php';
                    require_once dirname(__DIR__) . '/production/ChairmanAutoProductionService.php';

                    $autoService = new ChairmanAutoProductionService($conn, $V44Engine);
                    $autoProduction = $autoService->ensure($raceDate, $venue, $raceNum, 'BALANCED', false);

                    if (self::tableExists($conn, 'wr_chairman_production_score_v44')) {
                        $latestV44Run = '';
                $rsLatestV44 = @$conn->query("SELECT run_key FROM wr_chairman_production_score_v44 WHERE {$where} ORDER BY created_at DESC, id DESC LIMIT 1");
                if ($rsLatestV44 && ($latestV44Row = $rsLatestV44->fetch_assoc())) $latestV44Run = (string)$latestV44Row['run_key'];
                if ($latestV44Run !== '') {
                    $ranking = self::rows($conn, "SELECT * FROM wr_chairman_production_score_v44 WHERE {$where} AND run_key='" . $conn->real_escape_string($latestV44Run) . "' ORDER BY race_num ASC, race_rank ASC, calibrated_score DESC LIMIT 500");
                }
                    }
                } catch (Throwable $autoError) {
                    $autoProduction = array(
                        'attempted'=>true,
                        'status'=>'AUTO_BUILD_ERROR',
                        'message'=>$autoError->getMessage()
                    );
                } catch (Exception $autoError) {
                    $autoProduction = array(
                        'attempted'=>true,
                        'status'=>'AUTO_BUILD_ERROR',
                        'message'=>$autoError->getMessage()
                    );
                }
            }

            if (self::tableExists($conn, 'wr_chairman_race_optimizer_v44')) {
                $latestSummaryRun = '';
                $rsLatestSummary = @$conn->query("SELECT run_key FROM wr_chairman_race_optimizer_v44 WHERE {$where} ORDER BY created_at DESC, id DESC LIMIT 1");
                if ($rsLatestSummary && ($latestSummaryRow = $rsLatestSummary->fetch_assoc())) $latestSummaryRun = (string)$latestSummaryRow['run_key'];
                if ($latestSummaryRun !== '') {
                    $summary = self::rows($conn, "SELECT * FROM wr_chairman_race_optimizer_v44 WHERE {$where} AND run_key='" . $conn->real_escape_string($latestSummaryRun) . "' ORDER BY race_num ASC");
                }
            }

            $topByRace = array();
            foreach ($ranking as $r) {
                $rn = isset($r['race_num']) ? (string)$r['race_num'] : '0';
                if (!isset($topByRace[$rn])) {
                    $topByRace[$rn] = array(
                        'race_num' => (int)$rn,
                        'top_horse' => isset($r['horse_num']) ? $r['horse_num'] : '',
                        'top_name' => isset($r['horse_name']) ? $r['horse_name'] : '',
                        'top_score' => isset($r['ensemble_score']) ? (float)$r['ensemble_score'] : (isset($r['calibrated_score']) ? (float)$r['calibrated_score'] : 0),
                        'role' => isset($r['final_pick_type']) ? $r['final_pick_type'] : (isset($r['role_tag']) ? $r['role_tag'] : ''),
                        'decision' => isset($r['final_decision']) ? $r['final_decision'] : 'WATCH'
                    );
                }
            }

            $payload = array(
                'ok'=>true,
                'source'=>'ChairmanSafeLoader v4.5 V45.1 latest-run direct DB',
                'race_date'=>$raceDate,
                'venue'=>$venue,
                'race_num'=>(int)$raceNum,
                'counts'=>array(
                    'summary'=>count($summary),
                    'ranking'=>count($ranking),
                    'decision'=>count($decision),
                    'top_by_race'=>count($topByRace)
                ),
                'top_by_race'=>array_values($topByRace),
                'auto_production'=>$autoProduction,
                'decision'=>$decision,
                'summary'=>$summary,
                'ranking'=>$ranking,
                'elapsed_ms'=>round((microtime(true)-$t0)*1000,2),
                'ts'=>date('Y-m-d H:i:s')
            );

            self::log('OK race_date='.$raceDate.' ranking='.count($ranking).' decision='.count($decision).' elapsed='.$payload['elapsed_ms'].'ms');
            return $payload;

        } catch (Throwable $e) {
            self::log('ERROR '.$e->getMessage());
            return array(
                'ok'=>false,
                'status'=>'CHAIRMAN_EXCEPTION',
                'error'=>$e->getMessage(),
                'race_date'=>$raceDate,
                'elapsed_ms'=>round((microtime(true)-$t0)*1000,2)
            );
        } catch (Exception $e) {
            self::log('ERROR '.$e->getMessage());
            return array(
                'ok'=>false,
                'status'=>'CHAIRMAN_EXCEPTION',
                'error'=>$e->getMessage(),
                'race_date'=>$raceDate,
                'elapsed_ms'=>round((microtime(true)-$t0)*1000,2)
            );
        }
    }
}
