<?php
/**
 * WinRacing V47 — Chairman Pre-Race Ensemble Decision Engine
 *
 * IMPORTANT:
 * - PRE-RACE ONLY.
 * - Does NOT require racing_results.
 * - Does NOT use finish_position as a feature.
 * - Results/backtest are a later stage.
 *
 * Sources:
 * - wr_chairman_production_score_v44
 * - wr_chairman_race_optimizer_v44
 * - analysis_cache
 * - p1_power_history (pre-race fields only)
 * - race_7_scores
 */
class ChairmanPreRaceDecisionEngineV47 {
    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 tableExists($table) {
        $r = @$this->db->query("SHOW TABLES LIKE " . $this->q($table));
        return $r && $r->num_rows > 0;
    }

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

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

    public function ensureTables() {
        $this->db->query("CREATE TABLE IF NOT EXISTS wr_chairman_pre_race_decision_v47 (
            id INT AUTO_INCREMENT PRIMARY KEY,
            decision_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 '',
            source_rank INT NOT NULL DEFAULT 0,
            v44_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            p1_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            db7_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            signal_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            bias_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,
            ensemble_score DECIMAL(10,4) NOT NULL DEFAULT 0,
            pre_race_rank INT NOT NULL DEFAULT 0,
            decision_grade VARCHAR(20) NOT NULL DEFAULT 'C',
            decision_role VARCHAR(80) NOT NULL DEFAULT 'WATCH',
            decision_action VARCHAR(30) NOT NULL DEFAULT 'WATCH',
            banker_flag TINYINT NOT NULL DEFAULT 0,
            q_flag TINYINT NOT NULL DEFAULT 0,
            trio_flag TINYINT NOT NULL DEFAULT 0,
            cold_flag TINYINT NOT NULL DEFAULT 0,
            avoid_flag TINYINT NOT NULL DEFAULT 0,
            reason_zh TEXT NULL,
            source_json LONGTEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_v47_decision(decision_key),
            KEY idx_v47_race(race_date,venue,race_num),
            KEY idx_v47_run(run_key),
            KEY idx_v47_score(ensemble_score)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $this->db->query("CREATE TABLE IF NOT EXISTS wr_chairman_pre_race_summary_v47 (
            id INT AUTO_INCREMENT PRIMARY KEY,
            summary_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,
            race_confidence DECIMAL(10,4) NOT NULL DEFAULT 0,
            race_mode VARCHAR(80) NOT NULL DEFAULT 'MIXED',
            banker_horse VARCHAR(20) NOT NULL DEFAULT '',
            q_top2 VARCHAR(120) NOT NULL DEFAULT '',
            trio_top4 VARCHAR(180) NOT NULL DEFAULT '',
            cold_horses VARCHAR(180) NOT NULL DEFAULT '',
            avoid_horses VARCHAR(220) NOT NULL DEFAULT '',
            decision_summary TEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_v47_summary(summary_key),
            KEY idx_v47_summary_race(race_date,venue,race_num),
            KEY idx_v47_summary_run(run_key)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $this->db->query("CREATE TABLE IF NOT EXISTS wr_chairman_pre_race_run_log_v47 (
            id INT AUTO_INCREMENT PRIMARY KEY,
            run_key VARCHAR(80) NOT NULL,
            race_date DATE NULL,
            venue VARCHAR(80) NOT NULL DEFAULT '',
            status VARCHAR(30) NOT NULL DEFAULT 'DONE',
            source_rows INT NOT NULL DEFAULT 0,
            decision_rows INT NOT NULL DEFAULT 0,
            summary_rows INT NOT NULL DEFAULT 0,
            note VARCHAR(255) NOT NULL DEFAULT 'PRE_RACE_ONLY',
            message TEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_v47_log(run_key),
            KEY idx_v47_log_date(race_date,venue)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    private function loadV44($raceDate,$venue='',$raceNum=0) {
        if (!$this->tableExists('wr_chairman_production_score_v44')) return array();

        $runKey = $this->latestRunKey('wr_chairman_production_score_v44',$raceDate,$venue,$raceNum);
        if ($runKey === '') return array();

        $w = array(
            "race_date=".$this->q($raceDate),
            "run_key=".$this->q($runKey)
        );
        if ($venue !== '') $w[] = "venue=".$this->q($venue);
        if ((int)$raceNum > 0) $w[] = "race_num=".(int)$raceNum;

        return $this->rows("SELECT * FROM wr_chairman_production_score_v44 WHERE ".implode(' AND ',$w)." ORDER BY race_num,race_rank,calibrated_score DESC");
    }

    private function loadAnalysisCache($raceDate) {
        $map=array();
        if (!$this->tableExists('analysis_cache')) return $map;

        $rows=$this->rows("SELECT * FROM analysis_cache WHERE race_date=".$this->q($raceDate));
        foreach($rows as $r){
            $k=(int)$r['race_num'].'|'.(int)$r['horse_num'];
            $map[$k]=$r;
        }
        return $map;
    }

    private function loadP1($raceDate) {
        $map=array();
        if (!$this->tableExists('p1_power_history')) return $map;

        // finish_position intentionally NOT selected / NOT used.
        $rows=$this->rows("SELECT race_num,horse_num,horse_name,p1_power,matrix_score,p1_count,p2_count,expert_count,bias_score
                           FROM p1_power_history WHERE race_date=".$this->q($raceDate));
        foreach($rows as $r){
            $k=(int)$r['race_num'].'|'.(int)$r['horse_num'];
            $map[$k]=$r;
        }
        return $map;
    }

    private function loadDb7($raceDate) {
        $map=array();
        if (!$this->tableExists('race_7_scores')) return $map;

        $rows=$this->rows("SELECT race_no,horse_number,score FROM race_7_scores WHERE race_date=".$this->q($raceDate));
        foreach($rows as $r){
            $k=(int)$r['race_no'].'|'.(int)$r['horse_number'];
            $map[$k]=(float)$r['score'];
        }
        return $map;
    }

    private function grade($score) {
        if ($score >= 78) return 'A+';
        if ($score >= 70) return 'A';
        if ($score >= 62) return 'B+';
        if ($score >= 54) return 'B';
        if ($score >= 46) return 'C';
        return 'D';
    }

    public function build($raceDate,$venue='',$raceNum=0,$mode='BALANCED') {
        $this->ensureTables();

        $source=$this->loadV44($raceDate,$venue,$raceNum);
        $cache=$this->loadAnalysisCache($raceDate);
        $p1=$this->loadP1($raceDate);
        $db7=$this->loadDb7($raceDate);

        $runKey=date('YmdHis').'_'.mt_rand(1000,9999);
        $grouped=array();

        foreach($source as $r){
            $rn=(int)$r['race_num'];
            $hn=(int)$r['horse_num'];
            $k=$rn.'|'.$hn;

            $c=isset($cache[$k])?$cache[$k]:array();
            $p=isset($p1[$k])?$p1[$k]:array();
            $seven=isset($db7[$k])?$db7[$k]:0;

            $v44=$this->n($r['calibrated_score']);
            $conf=$this->n($r['confidence_score']);
            $risk=$this->n($r['risk_score']);
            $q=$this->n($r['q_score']);
            $trio=$this->n($r['trio_score']);
            $stability=$this->n($r['stability_score']);

            $p1Power=$this->n(isset($p['p1_power'])?$p['p1_power']:0);
            $matrix=$this->n(isset($p['matrix_score'])?$p['matrix_score']:0);
            $expertCount=$this->n(isset($p['expert_count'])?$p['expert_count']:0);
            $p1Norm=$this->clamp(($p1Power*0.55)+($matrix*0.35)+($expertCount*2.0),0,100);

            $db7Norm=$this->clamp(($seven/7)*100,0,100);

            $cacheConf=$this->n(isset($c['confidence_pct'])?$c['confidence_pct']:0);
            $cacheBias=$this->n(isset($c['bias_score'])?$c['bias_score']:0);
            $simulated=$this->n(isset($c['simulated_score'])?$c['simulated_score']:0);
            $leaderTop2=$this->n(isset($c['leader_top2_rate'])?$c['leader_top2_rate']:0);
            $resonance=$this->n(isset($c['resonance_top2_rate'])?$c['resonance_top2_rate']:0);
            $signal=$this->clamp(
                $cacheConf*0.36 +
                $simulated*0.18 +
                $leaderTop2*0.24 +
                $resonance*0.22,
                0,100
            );

            $bias=$this->clamp(50 + $cacheBias + $this->n(isset($p['bias_score'])?$p['bias_score']:0),0,100);

            $fakeRisk=!empty($c['is_fake_risk'])?1:0;
            $radar=!empty($c['is_radar_pick'])?1:0;

            // Pre-race fusion only. No result fields.
            $ensemble =
                $v44*0.38 +
                $conf*0.10 +
                $q*0.10 +
                $trio*0.08 +
                $stability*0.06 +
                $p1Norm*0.12 +
                $db7Norm*0.08 +
                $signal*0.06 +
                $bias*0.02 -
                $risk*0.10 -
                ($fakeRisk?8:0) +
                ($radar?3:0);

            if ($mode==='AGGRESSIVE') $ensemble += ($radar?4:0) + ($q*0.02);
            if ($mode==='CONSERVATIVE') $ensemble += ($stability*0.04) - ($risk*0.04);
            if ($mode==='DEFENSIVE') $ensemble += ($conf*0.04) - ($risk*0.07);

            $ensemble=$this->clamp($ensemble,0,100);

            $grouped[$rn][]=array(
                'source'=>$r,
                'source_rank'=>(int)$r['race_rank'],
                'v44'=>$v44,
                'p1'=>$p1Norm,
                'db7'=>$db7Norm,
                'signal'=>$signal,
                'bias'=>$bias,
                'risk'=>$risk,
                'confidence'=>$conf,
                'ensemble'=>$ensemble,
                'fake_risk'=>$fakeRisk,
                'radar'=>$radar,
                'source_json'=>json_encode(array(
                    'v44'=>$v44,'p1'=>$p1Norm,'db7'=>$db7Norm,'signal'=>$signal,
                    'bias'=>$bias,'risk'=>$risk,'confidence'=>$conf,
                    'fake_risk'=>$fakeRisk,'radar'=>$radar,
                    'pre_race_only'=>true
                ),JSON_UNESCAPED_UNICODE)
            );
        }

        $decisionCount=0;
        $summaryCount=0;

        $this->db->begin_transaction();

        foreach($grouped as $rn=>$rows){
            usort($rows,function($a,$b){
                if($a['ensemble']==$b['ensemble']) return $a['source_rank']<=>$b['source_rank'];
                return $a['ensemble']<$b['ensemble']?1:-1;
            });

            $topScore=isset($rows[0])?$rows[0]['ensemble']:0;
            $secondScore=isset($rows[1])?$rows[1]['ensemble']:0;
            $gap12=$topScore-$secondScore;

            $banker='';
            $qTop=array();
            $trioTop=array();
            $cold=array();
            $avoid=array();

            foreach($rows as $idx=>&$x){
                $rank=$idx+1;
                $x['pre_race_rank']=$rank;

                $bankerFlag=0;$qFlag=0;$trioFlag=0;$coldFlag=0;$avoidFlag=0;
                $role='WATCH';$action='WATCH';

                if($rank===1 && $x['ensemble']>=58 && $x['risk']<65 && !$x['fake_risk']){
                    $bankerFlag=1;
                    $role=($gap12>=4.5?'主席膽':'主力膽');
                    $action=($x['ensemble']>=68?'BUY':'COVER');
                    $banker=(string)$x['source']['horse_num'];
                } elseif($rank<=2 && $x['ensemble']>=54 && $x['risk']<72){
                    $qFlag=1;$role='Q膽/六環Top2';$action='COVER';
                } elseif($rank<=4 && $x['ensemble']>=48){
                    $trioFlag=1;$role='單T腳/三腳';$action='COVER';
                } elseif($rank>=4 && $x['ensemble']>=46 && $x['radar'] && !$x['fake_risk']){
                    $coldFlag=1;$role='爆冷腳';$action='WATCH';
                }

                if($x['fake_risk'] || $x['risk']>=78 || $x['ensemble']<38){
                    $avoidFlag=1;$role='避開';$action='SKIP';
                }

                if($qFlag || $rank<=2) $qTop[]=(string)$x['source']['horse_num'];
                if($trioFlag || $rank<=4) $trioTop[]=(string)$x['source']['horse_num'];
                if($coldFlag) $cold[]=(string)$x['source']['horse_num'];
                if($avoidFlag) $avoid[]=(string)$x['source']['horse_num'];

                $reason='V47賽前融合：V44 '.number_format($x['v44'],1).
                    '｜P1 '.number_format($x['p1'],1).
                    '｜7分 '.number_format($x['db7'],1).
                    '｜Signal '.number_format($x['signal'],1).
                    '｜Risk '.number_format($x['risk'],1).
                    '｜Final '.number_format($x['ensemble'],1).
                    '｜只使用賽前資料，未使用賽果。';

                $key=sha1($runKey.'|V47|'.$raceDate.'|'.$venue.'|'.$rn.'|'.$x['source']['horse_num']);

                $sql="INSERT INTO wr_chairman_pre_race_decision_v47
                    (decision_key,run_key,race_date,venue,race_num,horse_num,horse_name,source_rank,
                     v44_score,p1_score,db7_score,signal_score,bias_score,risk_score,confidence_score,
                     ensemble_score,pre_race_rank,decision_grade,decision_role,decision_action,
                     banker_flag,q_flag,trio_flag,cold_flag,avoid_flag,reason_zh,source_json)
                    VALUES (".
                    $this->q($key).",".
                    $this->q($runKey).",".
                    $this->q($raceDate).",".
                    $this->q($venue).",".
                    (int)$rn.",".
                    $this->q($x['source']['horse_num']).",".
                    $this->q($x['source']['horse_name']).",".
                    (int)$x['source_rank'].",".
                    $x['v44'].",".
                    $x['p1'].",".
                    $x['db7'].",".
                    $x['signal'].",".
                    $x['bias'].",".
                    $x['risk'].",".
                    $x['confidence'].",".
                    $x['ensemble'].",".
                    $rank.",".
                    $this->q($this->grade($x['ensemble'])).",".
                    $this->q($role).",".
                    $this->q($action).",".
                    $bankerFlag.",".
                    $qFlag.",".
                    $trioFlag.",".
                    $coldFlag.",".
                    $avoidFlag.",".
                    $this->q($reason).",".
                    $this->q($x['source_json']).")";
                $this->db->query($sql);
                $decisionCount++;
            }
            unset($x);

            if($banker==='' && isset($rows[0]) && !$rows[0]['fake_risk']){
                $banker=(string)$rows[0]['source']['horse_num'];
            }

            $avg=0;
            foreach($rows as $x) $avg+=$x['ensemble'];
            $avg=count($rows)?$avg/count($rows):0;

            $modeLabel=$gap12>=6?'重心清晰':($avg>=52?'穩健場':($avg>=44?'混戰場':'低信心場'));

            $summary='V47賽前決策：R'.$rn.'｜'.$modeLabel.
                '｜膽 '.$banker.
                '｜Q/六環 '.implode(',',array_slice(array_values(array_unique($qTop)),0,2)).
                '｜單T '.implode(',',array_slice(array_values(array_unique($trioTop)),0,4)).
                '｜冷 '.implode(',',array_slice(array_values(array_unique($cold)),0,2)).
                '｜避 '.implode(',',array_slice(array_values(array_unique($avoid)),0,6)).
                '｜未使用賽果。';

            $summaryKey=sha1($runKey.'|V47SUMMARY|'.$raceDate.'|'.$venue.'|'.$rn);

            $sql="INSERT INTO wr_chairman_pre_race_summary_v47
                (summary_key,run_key,race_date,venue,race_num,race_confidence,race_mode,
                 banker_horse,q_top2,trio_top4,cold_horses,avoid_horses,decision_summary)
                VALUES (".
                $this->q($summaryKey).",".
                $this->q($runKey).",".
                $this->q($raceDate).",".
                $this->q($venue).",".
                (int)$rn.",".
                $this->clamp($topScore*0.65+$gap12*3+($avg*0.15),0,100).",".
                $this->q($modeLabel).",".
                $this->q($banker).",".
                $this->q(implode(',',array_slice(array_values(array_unique($qTop)),0,2))).",".
                $this->q(implode(',',array_slice(array_values(array_unique($trioTop)),0,4))).",".
                $this->q(implode(',',array_slice(array_values(array_unique($cold)),0,2))).",".
                $this->q(implode(',',array_slice(array_values(array_unique($avoid)),0,6))).",".
                $this->q($summary).")";
            $this->db->query($sql);
            $summaryCount++;
        }

        $msg='Built V47 PRE-RACE: source '.count($source).' | decision '.$decisionCount.' | races '.$summaryCount.' | results not used';
        $this->db->query("INSERT INTO wr_chairman_pre_race_run_log_v47
            (run_key,race_date,venue,status,source_rows,decision_rows,summary_rows,note,message)
            VALUES (".
            $this->q($runKey).",".
            $this->q($raceDate).",".
            $this->q($venue).",'DONE',".
            count($source).",".
            $decisionCount.",".
            $summaryCount.",'PRE_RACE_ONLY',".
            $this->q($msg).")");

        $this->db->commit();

        return array(
            'status'=>'BUILT',
            'run_key'=>$runKey,
            'race_date'=>$raceDate,
            'venue'=>$venue,
            'race_num'=>(int)$raceNum,
            'mode'=>$mode,
            'pre_race_only'=>true,
            'results_used'=>false,
            'source_rows'=>count($source),
            'decision_rows'=>$decisionCount,
            'summary_rows'=>$summaryCount,
            'message'=>$msg
        );
    }

    public function latest($raceDate,$venue='',$raceNum=0) {
        $this->ensureTables();

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

        $r=@$this->db->query("SELECT run_key FROM wr_chairman_pre_race_run_log_v47
                              WHERE race_date=".$this->q($raceDate).
                              ($venue!==''?" AND venue=".$this->q($venue):"").
                              " ORDER BY created_at DESC,id DESC LIMIT 1");
        $runKey='';
        if($r && ($row=$r->fetch_assoc())) $runKey=(string)$row['run_key'];

        if($runKey==='') return array(
            'status'=>'EMPTY','pre_race_only'=>true,'results_used'=>false,
            'summary'=>array(),'ranking'=>array()
        );

        $summary=$this->rows("SELECT * FROM wr_chairman_pre_race_summary_v47
                             WHERE {$where} AND run_key=".$this->q($runKey).
                             " ORDER BY race_num");
        $ranking=$this->rows("SELECT * FROM wr_chairman_pre_race_decision_v47
                             WHERE {$where} AND run_key=".$this->q($runKey).
                             " ORDER BY race_num,pre_race_rank");

        return array(
            'status'=>'OK',
            'run_key'=>$runKey,
            'pre_race_only'=>true,
            'results_used'=>false,
            'summary'=>$summary,
            'ranking'=>$ranking
        );
    }
}
