<?php
/**
 * Chairman Auto Production Service v4.5 / V45.1 FIX
 * PHP 7.4 compatible.
 *
 * Fixes:
 * - Rebuild no longer accumulates duplicate rows.
 * - Race #0 means ALL RACES.
 * - Counts return latest stable dataset only.
 * - Gateway can read a deterministic latest run.
 */
class ChairmanAutoProductionService {
    private $db;
    private $engine;

    public function __construct(mysqli $db, V44RankingOptimizerEngine $engine) {
        $this->db = $db;
        $this->engine = $engine;
    }

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

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

    private function scalar($sql, $default=0) {
        $rs = @$this->db->query($sql);
        if ($rs && ($row = $rs->fetch_row())) return $row[0];
        return $default;
    }

    private function where($raceDate, $venue='', $raceNum=0, $alias='') {
        $p = $alias !== '' ? $alias . '.' : '';
        $w = array($p . "race_date=" . $this->q($raceDate));
        if ($venue !== '') $w[] = $p . "venue=" . $this->q($venue);
        if ((int)$raceNum > 0) $w[] = $p . "race_num=" . (int)$raceNum;
        return implode(" AND ", $w);
    }

    private function latestRunKey($table, $raceDate, $venue='', $raceNum=0) {
        if (!$this->tableExists($table)) return '';
        $where = $this->where($raceDate, $venue, $raceNum);
        return (string)$this->scalar(
            "SELECT run_key FROM {$table} WHERE {$where} ORDER BY created_at DESC, id DESC LIMIT 1",
            ''
        );
    }

    private function countLatestRun($table, $raceDate, $venue='', $raceNum=0) {
        if (!$this->tableExists($table)) return 0;
        $runKey = $this->latestRunKey($table, $raceDate, $venue, $raceNum);
        if ($runKey === '') return 0;

        $where = $this->where($raceDate, $venue, $raceNum);
        return (int)$this->scalar(
            "SELECT COUNT(*) FROM {$table} WHERE {$where} AND run_key=" . $this->q($runKey),
            0
        );
    }

    public function inspect($raceDate, $venue='', $raceNum=0) {
        $sourceRows = 0;
        $sourceTable = 'NONE';
        $where = $this->where($raceDate, $venue, $raceNum);

        if ($this->tableExists('wr_chairman_integration_score_v43')) {
            $sourceRows = (int)$this->scalar(
                "SELECT COUNT(*) FROM wr_chairman_integration_score_v43 WHERE {$where}",
                0
            );
            $sourceTable = 'wr_chairman_integration_score_v43';
        } elseif ($this->tableExists('wr_chairman_horse_decision_v42')) {
            $sourceRows = (int)$this->scalar(
                "SELECT COUNT(*) FROM wr_chairman_horse_decision_v42 WHERE {$where}",
                0
            );
            $sourceTable = 'wr_chairman_horse_decision_v42';
        }

        $productionRunKey = $this->latestRunKey(
            'wr_chairman_production_score_v44',
            $raceDate, $venue, $raceNum
        );
        $optimizerRunKey = $this->latestRunKey(
            'wr_chairman_race_optimizer_v44',
            $raceDate, $venue, $raceNum
        );

        $productionRows = $this->countLatestRun(
            'wr_chairman_production_score_v44',
            $raceDate, $venue, $raceNum
        );
        $optimizerRows = $this->countLatestRun(
            'wr_chairman_race_optimizer_v44',
            $raceDate, $venue, $raceNum
        );

        return array(
            'race_date' => $raceDate,
            'venue' => $venue,
            'race_num' => (int)$raceNum,
            'race_scope' => ((int)$raceNum > 0 ? 'RACE_' . (int)$raceNum : 'ALL_RACES'),
            'source_table' => $sourceTable,
            'source_rows' => $sourceRows,
            'production_rows' => $productionRows,
            'optimizer_rows' => $optimizerRows,
            'production_run_key' => $productionRunKey,
            'optimizer_run_key' => $optimizerRunKey,
            'ready' => ($productionRows > 0 && $optimizerRows > 0)
        );
    }

    private function purgeScope($raceDate, $venue='', $raceNum=0) {
        $tables = array(
            'wr_chairman_production_score_v44',
            'wr_chairman_race_optimizer_v44',
            'wr_chairman_analysis_api_v44'
        );

        $deleted = array();
        foreach ($tables as $table) {
            if (!$this->tableExists($table)) {
                $deleted[$table] = 0;
                continue;
            }

            $where = $this->where($raceDate, $venue, $raceNum);
            @$this->db->query("DELETE FROM {$table} WHERE {$where}");
            $deleted[$table] = (int)$this->db->affected_rows;
        }

        return $deleted;
    }

    public function ensure($raceDate, $venue='', $raceNum=0, $mode='BALANCED', $force=false) {
        $before = $this->inspect($raceDate, $venue, $raceNum);

        if (!$force && $before['ready']) {
            return array(
                'status' => 'READY_EXISTING',
                'built' => false,
                'before' => $before,
                'after' => $before,
                'message' => 'Latest V44 Production already exists. No rebuild required.',
                'time' => date('c')
            );
        }

        if ($before['source_rows'] <= 0) {
            return array(
                'status' => 'SOURCE_EMPTY',
                'built' => false,
                'before' => $before,
                'after' => $before,
                'message' => 'No V43/V42 source rows found for this race date.',
                'time' => date('c')
            );
        }

        // Critical V45.1 fix:
        // remove old rows in the selected scope before a rebuild.
        $purged = $this->purgeScope($raceDate, $venue, $raceNum);

        $build = $this->engine->build($raceDate, $venue, $raceNum, $mode);
        $after = $this->inspect($raceDate, $venue, $raceNum);

        return array(
            'status' => $after['ready'] ? 'BUILT_READY' : 'BUILD_INCOMPLETE',
            'built' => true,
            'purged' => $purged,
            'before' => $before,
            'build' => $build,
            'after' => $after,
            'message' => isset($build['message']) ? $build['message'] : 'V44 build completed.',
            'time' => date('c')
        );
    }
}
