<?php
/**
 * WinRacing V50 — Ultimate Chairman Decision Engine
 * PHP 7.4 compatible.
 *
 * Sources:
 * - V49 Chairman Consensus
 * - V49 Race Consensus
 *
 * PRE-RACE ONLY:
 * - Does not read racing_results
 * - Does not use finish_position
 *
 * Outputs:
 * - One final race decision per race
 * - Daily best banker
 * - Daily Q bankers
 * - Daily value horses
 * - Daily avoid list
 * - Portfolio confidence
 */
class ChairmanUltimateDecisionEngineV50 {
    private $db;

    public function __construct(mysqli $db) {
        $this->db = $db;
        @$this->db->set_charset('utf8mb4');
    }

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

    private function n($v, $d=0) {
        return is_numeric($v) ? (float)$v : $d;
    }

    private function clamp($v, $min=0, $max=100) {
        return max($min, min($max, $v));
    }

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

    private function latestRunKey($table,$date,$venue='') {
        $w=array("race_date=".$this->q($date));
        if($venue!=='') $w[]="venue=".$this->q($venue);
        $rs=@$this->db->query(
            "SELECT run_key FROM {$table}
             WHERE ".implode(" AND ",$w)."
             ORDER BY created_at DESC,id DESC LIMIT 1"
        );
        if($rs && ($r=$rs->fetch_assoc())) return (string)$r['run_key'];
        return '';
    }

    public function ensureTables() {
        $this->db->query("CREATE TABLE IF NOT EXISTS wr_chairman_ultimate_race_v50 (
            id INT AUTO_INCREMENT PRIMARY KEY,
            decision_key VARCHAR(700) NOT NULL,
            run_key VARCHAR(80) NOT NULL,
            source_v49_run VARCHAR(80) NOT NULL,
            race_date DATE NULL,
            venue VARCHAR(80) NOT NULL DEFAULT '',
            race_num INT NOT NULL DEFAULT 0,
            banker_horse VARCHAR(20) NOT NULL DEFAULT '',
            banker_name VARCHAR(180) NOT NULL DEFAULT '',
            banker_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            consensus_pct DECIMAL(10,4) NOT NULL DEFAULT 0,
            stability_index DECIMAL(10,4) NOT NULL DEFAULT 0,
            ultimate_confidence DECIMAL(10,4) NOT NULL DEFAULT 0,
            star_rating INT NOT NULL DEFAULT 1,
            race_state VARCHAR(40) NOT NULL DEFAULT 'WATCH',
            final_tag VARCHAR(80) NOT NULL DEFAULT 'WATCH',
            final_action VARCHAR(30) NOT NULL DEFAULT 'WATCH',
            q_top2 VARCHAR(120) NOT NULL DEFAULT '',
            trio_top4 VARCHAR(180) NOT NULL DEFAULT '',
            value_horses VARCHAR(180) NOT NULL DEFAULT '',
            avoid_horses VARCHAR(220) NOT NULL DEFAULT '',
            final_reason TEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_v50_race(decision_key),
            KEY idx_v50_race(race_date,venue,race_num),
            KEY idx_v50_run(run_key)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $this->db->query("CREATE TABLE IF NOT EXISTS wr_chairman_ultimate_day_v50 (
            id INT AUTO_INCREMENT PRIMARY KEY,
            day_key VARCHAR(700) NOT NULL,
            run_key VARCHAR(80) NOT NULL,
            source_v49_run VARCHAR(80) NOT NULL,
            race_date DATE NULL,
            venue VARCHAR(80) NOT NULL DEFAULT '',
            best_banker_race INT NOT NULL DEFAULT 0,
            best_banker_horse VARCHAR(20) NOT NULL DEFAULT '',
            best_banker_name VARCHAR(180) NOT NULL DEFAULT '',
            best_banker_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            top3_bankers VARCHAR(255) NOT NULL DEFAULT '',
            q_bankers VARCHAR(255) NOT NULL DEFAULT '',
            value_horses TEXT NULL,
            avoid_horses TEXT NULL,
            portfolio_confidence DECIMAL(10,4) NOT NULL DEFAULT 0,
            portfolio_state VARCHAR(40) NOT NULL DEFAULT 'WATCH',
            final_summary TEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_v50_day(day_key),
            KEY idx_v50_day(race_date,venue),
            KEY idx_v50_day_run(run_key)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $this->db->query("CREATE TABLE IF NOT EXISTS wr_chairman_ultimate_run_log_v50 (
            id INT AUTO_INCREMENT PRIMARY KEY,
            run_key VARCHAR(80) NOT NULL,
            source_v49_run VARCHAR(80) NOT NULL,
            race_date DATE NULL,
            venue VARCHAR(80) NOT NULL DEFAULT '',
            status VARCHAR(30) NOT NULL DEFAULT 'DONE',
            race_rows INT NOT NULL DEFAULT 0,
            message TEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_v50_log(run_key),
            KEY idx_v50_log_date(race_date,venue)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    private function stars($confidence,$tag,$clarity) {
        $s=1;
        if($confidence>=45) $s=2;
        if($confidence>=55) $s=3;
        if($confidence>=65) $s=4;
        if($confidence>=76) $s=5;
        if($tag==='KING' && $clarity==='VERY_CLEAR') $s=5;
        return $s;
    }

    private function raceState($confidence,$clarity,$tag) {
        if($tag==='KING' && $confidence>=74) return 'PRIMARY';
        if($tag==='MAIN_BANKER' && $confidence>=64) return 'STRONG';
        if($clarity==='CLEAR' && $confidence>=58) return 'COVER';
        if($clarity==='MIXED') return 'MIXED';
        return 'WATCH';
    }

    public function build($date,$venue='') {
        $this->ensureTables();

        $v49Run=$this->latestRunKey('wr_chairman_consensus_run_log_v49',$date,$venue);
        if($v49Run==='') {
            return array(
                'status'=>'SOURCE_EMPTY',
                'message'=>'V49 source not found. Build V49 first.'
            );
        }

        $w="race_date=".$this->q($date)." AND run_key=".$this->q($v49Run);
        if($venue!=='') $w.=" AND venue=".$this->q($venue);

        $summary=$this->rows("SELECT * FROM wr_chairman_race_consensus_v49 WHERE {$w} ORDER BY race_num");
        $ranking=$this->rows("SELECT * FROM wr_chairman_consensus_v49 WHERE {$w} ORDER BY race_num,final_rank");

        $rankMap=array();
        foreach($ranking as $r){
            $rn=(int)$r['race_num'];
            if(!isset($rankMap[$rn])) $rankMap[$rn]=array();
            $rankMap[$rn][]=$r;
        }

        $runKey=date('YmdHis').'_'.mt_rand(1000,9999);
        $dayCandidates=array();
        $allValue=array();
        $allAvoid=array();
        $raceRows=0;

        $this->db->begin_transaction();

        foreach($summary as $s){
            $rn=(int)$s['race_num'];
            $rows=isset($rankMap[$rn])?$rankMap[$rn]:array();
            if(!$rows) continue;

            usort($rows,function($a,$b){
                return (int)$a['final_rank'] <=> (int)$b['final_rank'];
            });

            $top=$rows[0];
            $tag=(string)$top['final_tag'];
            $clarity=(string)$s['race_clarity'];
            $consensus=$this->n($s['race_consensus_pct']);
            $stability=$this->n($s['race_stability_index']);
            $score=$this->n($top['chairman_final_score']);

            $confidence=$this->clamp(
                $score*0.45 +
                $consensus*0.30 +
                $stability*0.25 +
                ($tag==='KING'?6:0) +
                ($tag==='MAIN_BANKER'?3:0),
                0,100
            );

            $stars=$this->stars($confidence,$tag,$clarity);
            $state=$this->raceState($confidence,$clarity,$tag);
            $action=(string)$top['final_action'];

            $reason='V50 Ultimate：R'.$rn.
                '｜膽 #'.$top['horse_num'].' '.$top['horse_name'].
                '｜Final '.number_format($score,1).
                '｜Consensus '.number_format($consensus,1).
                '｜RSI '.number_format($stability,1).
                '｜Ultimate '.number_format($confidence,1).
                '｜Stars '.$stars.
                '｜State '.$state.
                '｜Tag '.$tag.'。';

            $key=sha1($runKey.'|V50RACE|'.$date.'|'.$venue.'|'.$rn);

            $sql="INSERT INTO wr_chairman_ultimate_race_v50
                (decision_key,run_key,source_v49_run,race_date,venue,race_num,banker_horse,
                 banker_name,banker_score,consensus_pct,stability_index,ultimate_confidence,
                 star_rating,race_state,final_tag,final_action,q_top2,trio_top4,
                 value_horses,avoid_horses,final_reason)
                VALUES (".
                $this->q($key).",".
                $this->q($runKey).",".
                $this->q($v49Run).",".
                $this->q($date).",".
                $this->q($venue).",".
                $rn.",".
                $this->q($top['horse_num']).",".
                $this->q($top['horse_name']).",".
                $score.",".
                $consensus.",".
                $stability.",".
                $confidence.",".
                $stars.",".
                $this->q($state).",".
                $this->q($tag).",".
                $this->q($action).",".
                $this->q($s['q_top2']).",".
                $this->q($s['trio_top4']).",".
                $this->q($s['value_horses']).",".
                $this->q($s['avoid_horses']).",".
                $this->q($reason).")";
            $this->db->query($sql);
            $raceRows++;

            $dayCandidates[]=array(
                'race_num'=>$rn,
                'horse_num'=>(string)$top['horse_num'],
                'horse_name'=>(string)$top['horse_name'],
                'confidence'=>$confidence,
                'stars'=>$stars,
                'state'=>$state,
                'tag'=>$tag,
                'q_top2'=>(string)$s['q_top2']
            );

            if(trim((string)$s['value_horses'])!=='') $allValue[]='R'.$rn.':'.$s['value_horses'];
            if(trim((string)$s['avoid_horses'])!=='') $allAvoid[]='R'.$rn.':'.$s['avoid_horses'];
        }

        usort($dayCandidates,function($a,$b){
            return $a['confidence']<$b['confidence']?1:-1;
        });

        $best=isset($dayCandidates[0])?$dayCandidates[0]:array(
            'race_num'=>0,'horse_num'=>'','horse_name'=>'','confidence'=>0
        );

        $top3=array();
        foreach(array_slice($dayCandidates,0,3) as $x){
            $top3[]='R'.$x['race_num'].'#'.$x['horse_num'];
        }

        $qBankers=array();
        foreach($dayCandidates as $x){
            if($x['stars']>=4 || in_array($x['state'],array('PRIMARY','STRONG'),true)){
                $qBankers[]='R'.$x['race_num'].':'.$x['q_top2'];
            }
        }

        $portfolioConfidence=0;
        foreach(array_slice($dayCandidates,0,5) as $x) $portfolioConfidence+=$x['confidence'];
        $portfolioConfidence=count(array_slice($dayCandidates,0,5))
            ? $portfolioConfidence/count(array_slice($dayCandidates,0,5))
            : 0;

        $portfolioState='WATCH';
        if($portfolioConfidence>=72) $portfolioState='STRONG_DAY';
        elseif($portfolioConfidence>=62) $portfolioState='GOOD_DAY';
        elseif($portfolioConfidence>=52) $portfolioState='MIXED_DAY';

        $daySummary='V50 Daily Ultimate：最佳膽 R'.$best['race_num'].' #'.$best['horse_num'].' '.$best['horse_name'].
            '｜Confidence '.number_format($best['confidence'],1).
            '｜Top3 '.implode(',',$top3).
            '｜Portfolio '.number_format($portfolioConfidence,1).
            '｜State '.$portfolioState.
            '｜未使用賽果。';

        $dayKey=sha1($runKey.'|V50DAY|'.$date.'|'.$venue);

        $this->db->query("INSERT INTO wr_chairman_ultimate_day_v50
            (day_key,run_key,source_v49_run,race_date,venue,best_banker_race,
             best_banker_horse,best_banker_name,best_banker_score,top3_bankers,
             q_bankers,value_horses,avoid_horses,portfolio_confidence,
             portfolio_state,final_summary)
            VALUES (".
            $this->q($dayKey).",".
            $this->q($runKey).",".
            $this->q($v49Run).",".
            $this->q($date).",".
            $this->q($venue).",".
            (int)$best['race_num'].",".
            $this->q($best['horse_num']).",".
            $this->q($best['horse_name']).",".
            $this->n($best['confidence']).",".
            $this->q(implode(',',$top3)).",".
            $this->q(implode(' | ',$qBankers)).",".
            $this->q(implode(' | ',$allValue)).",".
            $this->q(implode(' | ',$allAvoid)).",".
            $portfolioConfidence.",".
            $this->q($portfolioState).",".
            $this->q($daySummary).")");

        $message='Built V50 Ultimate: races '.$raceRows.' | portfolio '.$portfolioState;
        $this->db->query("INSERT INTO wr_chairman_ultimate_run_log_v50
            (run_key,source_v49_run,race_date,venue,status,race_rows,message)
            VALUES (".
            $this->q($runKey).",".
            $this->q($v49Run).",".
            $this->q($date).",".
            $this->q($venue).",'DONE',".
            $raceRows.",".
            $this->q($message).")");

        $this->db->commit();

        return array(
            'status'=>'BUILT',
            'run_key'=>$runKey,
            'race_rows'=>$raceRows,
            'pre_race_only'=>true,
            'results_used'=>false,
            'message'=>$message
        );
    }

    public function latest($date,$venue='') {
        $this->ensureTables();
        $run=$this->latestRunKey('wr_chairman_ultimate_run_log_v50',$date,$venue);
        if($run==='') return array(
            'status'=>'EMPTY',
            'pre_race_only'=>true,
            'results_used'=>false,
            'day'=>array(),
            'races'=>array()
        );

        $w="race_date=".$this->q($date)." AND run_key=".$this->q($run);
        if($venue!=='') $w.=" AND venue=".$this->q($venue);

        $day=$this->rows("SELECT * FROM wr_chairman_ultimate_day_v50 WHERE {$w} ORDER BY id DESC LIMIT 1");
        $races=$this->rows("SELECT * FROM wr_chairman_ultimate_race_v50 WHERE {$w} ORDER BY race_num");

        return array(
            'status'=>'OK',
            'run_key'=>$run,
            'pre_race_only'=>true,
            'results_used'=>false,
            'day'=>isset($day[0])?$day[0]:array(),
            'races'=>$races
        );
    }
}
