<?php
class V44ProductionRepository {
    private $db;

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

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

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

    public function rows($sql) {
        $a = array();
        $r = @$this->db->query($sql);
        if ($r) { while($x=$r->fetch_assoc()) $a[]=$x; }
        return $a;
    }

    public function ensureTables() {
        $tables = array();

        $tables[] = "CREATE TABLE IF NOT EXISTS wr_chairman_production_score_v44 (
            id INT AUTO_INCREMENT PRIMARY KEY, production_key VARCHAR(700) NOT NULL, run_key VARCHAR(80) NOT NULL,
            race_date DATE NULL, venue VARCHAR(80) NOT NULL DEFAULT '', race_num INT NOT NULL DEFAULT 0, horse_num VARCHAR(20) NOT NULL DEFAULT '', horse_name VARCHAR(180) NOT NULL DEFAULT '',
            v43_score DECIMAL(10,4) NOT NULL DEFAULT 0, optimized_score DECIMAL(10,4) NOT NULL DEFAULT 0, calibrated_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            win_score DECIMAL(10,4) NOT NULL DEFAULT 0, place_score DECIMAL(10,4) NOT NULL DEFAULT 0, q_score DECIMAL(10,4) NOT NULL DEFAULT 0, trio_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            risk_score DECIMAL(10,4) NOT NULL DEFAULT 0, confidence_score DECIMAL(10,4) NOT NULL DEFAULT 0, stability_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            race_rank INT NOT NULL DEFAULT 0, role_tag VARCHAR(80) NOT NULL DEFAULT 'WATCH', final_decision VARCHAR(30) NOT NULL DEFAULT 'WATCH', production_grade VARCHAR(30) NOT NULL DEFAULT 'C', production_state VARCHAR(80) NOT NULL DEFAULT 'PRODUCTION_WATCH',
            score_gap DECIMAL(10,4) NOT NULL DEFAULT 0, top_probability DECIMAL(10,4) NOT NULL DEFAULT 0, api_payload LONGTEXT NULL, reason_zh TEXT NULL, trace TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_v44_prod(production_key), KEY idx_v44_prod_run(run_key), KEY idx_v44_prod_race(race_date,venue,race_num), KEY idx_v44_prod_rank(race_rank), KEY idx_v44_prod_score(calibrated_score)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

        $tables[] = "CREATE TABLE IF NOT EXISTS wr_chairman_race_optimizer_v44 (
            id INT AUTO_INCREMENT PRIMARY KEY, optimizer_key VARCHAR(700) NOT NULL, run_key VARCHAR(80) NOT NULL,
            race_date DATE NULL, venue VARCHAR(80) NOT NULL DEFAULT '', race_num INT NOT NULL DEFAULT 0,
            horse_count INT NOT NULL DEFAULT 0, race_strength DECIMAL(10,4) NOT NULL DEFAULT 0, race_stability DECIMAL(10,4) NOT NULL DEFAULT 0, race_entropy DECIMAL(10,4) NOT NULL DEFAULT 0,
            top_horse VARCHAR(20) NOT NULL DEFAULT '', top_score DECIMAL(10,4) NOT NULL DEFAULT 0, second_horse VARCHAR(20) NOT NULL DEFAULT '', second_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            gap12 DECIMAL(10,4) NOT NULL DEFAULT 0, gap14 DECIMAL(10,4) NOT NULL DEFAULT 0, race_mode VARCHAR(80) NOT NULL DEFAULT 'BALANCED', race_hint VARCHAR(120) NOT NULL DEFAULT 'RACE_REVIEW',
            q_suggestion VARCHAR(180) NOT NULL DEFAULT '', trio_suggestion VARCHAR(260) NOT NULL DEFAULT '', sixup_suggestion VARCHAR(260) NOT NULL DEFAULT '', avoid_suggestion VARCHAR(260) NOT NULL DEFAULT '',
            json_payload LONGTEXT NULL, reason_zh TEXT NULL, trace TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_v44_optimizer(optimizer_key), KEY idx_v44_optimizer_run(run_key), KEY idx_v44_optimizer_race(race_date,venue,race_num)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

        $tables[] = "CREATE TABLE IF NOT EXISTS wr_chairman_analysis_api_v44 (
            id INT AUTO_INCREMENT PRIMARY KEY, api_key VARCHAR(700) NOT NULL, run_key VARCHAR(80) NOT NULL,
            race_date DATE NULL, venue VARCHAR(80) NOT NULL DEFAULT '', race_num INT NOT NULL DEFAULT 0, api_type VARCHAR(80) NOT NULL DEFAULT 'ANALYSIS_PHP_READY',
            json_payload LONGTEXT NULL, php_hint LONGTEXT NULL, reason_zh TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_v44_api(api_key), KEY idx_v44_api_run(run_key), KEY idx_v44_api_race(race_date,venue,race_num)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

        $tables[] = "CREATE TABLE IF NOT EXISTS wr_chairman_parameter_learning_v44 (
            id INT AUTO_INCREMENT PRIMARY KEY, param_key VARCHAR(700) NOT NULL, run_key VARCHAR(80) NOT NULL,
            parameter_name VARCHAR(120) NOT NULL DEFAULT '', old_weight DECIMAL(10,4) NOT NULL DEFAULT 0, suggested_weight DECIMAL(10,4) NOT NULL DEFAULT 0, learning_delta DECIMAL(10,4) NOT NULL DEFAULT 0,
            sample_rows INT NOT NULL DEFAULT 0, confidence_score DECIMAL(10,4) NOT NULL DEFAULT 0, reason_zh TEXT NULL, trace TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_v44_param(param_key), KEY idx_v44_param_run(run_key), KEY idx_v44_param_name(parameter_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

        $tables[] = "CREATE TABLE IF NOT EXISTS wr_chairman_production_run_log_v44 (
            id INT AUTO_INCREMENT PRIMARY KEY, run_key VARCHAR(80) NOT NULL, race_date DATE NULL, venue VARCHAR(80) NOT NULL DEFAULT '', context_mode VARCHAR(40) NOT NULL DEFAULT 'BALANCED', status VARCHAR(30) NOT NULL DEFAULT 'START',
            source_rows INT NOT NULL DEFAULT 0, production_rows INT NOT NULL DEFAULT 0, optimizer_rows INT NOT NULL DEFAULT 0, api_rows INT NOT NULL DEFAULT 0, param_rows INT NOT NULL DEFAULT 0,
            top_horse VARCHAR(40) NOT NULL DEFAULT '', top_score DECIMAL(10,4) NOT NULL DEFAULT 0, message TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_v44_log_run(run_key), KEY idx_v44_log_date(race_date,venue)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

        foreach($tables as $sql){ @$this->db->query($sql); }
    }

    public function sourceRows($raceDate, $venue='', $raceNum=0) {
        $where=array();
        if($raceDate) $where[]='race_date='.$this->q($raceDate);
        if($venue!=='') $where[]='venue='.$this->q($venue);
        if((int)$raceNum>0) $where[]='race_num='.(int)$raceNum;

        if($this->tableExists('wr_chairman_integration_score_v43')){
            $sql='SELECT * FROM wr_chairman_integration_score_v43'.($where?' WHERE '.implode(' AND ',$where):'').' ORDER BY race_date, venue, race_num, race_rank, production_score DESC LIMIT 1500';
            $r=$this->rows($sql);
            if($r) return $r;
        }

        if($this->tableExists('wr_chairman_horse_decision_v42')){
            $sql='SELECT race_date,venue,race_num,horse_num,horse_name,horse_score AS production_score,horse_score AS base_horse_score,chairman_alignment AS chairman_score,win_score,place_score,q_score,trio_score,risk_score,confidence_score,final_decision,0 AS race_rank,"WATCH" AS role_tag,"V42_FALLBACK" AS integration_state FROM wr_chairman_horse_decision_v42'.($where?' WHERE '.implode(' AND ',$where):'').' ORDER BY race_date, venue, race_num, horse_score DESC LIMIT 1500';
            return $this->rows($sql);
        }

        return array();
    }

    public function insertOrUpdate($table, array $arr, array $update=array()) {
        $cols=array_keys($arr);
        $vals=array();
        foreach($cols as $c){
            $vals[] = is_numeric($arr[$c]) ? $arr[$c] : $this->q($arr[$c]);
        }
        $sql='INSERT INTO '.$table.' ('.implode(',',$cols).') VALUES ('.implode(',',$vals).')';
        if($update){
            $u=array();
            foreach($update as $c){ $u[]=$c.'=VALUES('.$c.')'; }
            $sql.=' ON DUPLICATE KEY UPDATE '.implode(',',$u);
        }
        return @$this->db->query($sql);
    }

    public function begin(){ @$this->db->begin_transaction(); }
    public function commit(){ @$this->db->commit(); }
}
