<?php
/**
 * V44 Duplicate Cleanup Tool v45.1
 * Removes older V44 build rows for one race date and keeps the latest run.
 * Use:
 * cleanup_v44_duplicates.php?race_date=2026-07-08
 */
require_once dirname(__DIR__,2) . '/config/config.php';
if (!isset($conn) && isset($db)) $conn=$db;
if (!isset($conn) || !($conn instanceof mysqli)) die('DB connection not found');
$conn->set_charset('utf8mb4');

function ch($s){return htmlspecialchars((string)$s,ENT_QUOTES,'UTF-8');}
function cq($db,$s){return "'".$db->real_escape_string((string)$s)."'";}
function existsTable($db,$t){$r=@$db->query("SHOW TABLES LIKE ".cq($db,$t));return $r&&$r->num_rows>0;}

$date=isset($_GET['race_date'])?trim($_GET['race_date']):date('Y-m-d');
$execute=isset($_GET['execute'])&&$_GET['execute']==='1';
$tables=array('wr_chairman_production_score_v44','wr_chairman_race_optimizer_v44','wr_chairman_analysis_api_v44');
$report=array();

foreach($tables as $t){
    if(!existsTable($conn,$t)){ $report[$t]=array('status'=>'MISSING'); continue; }

    $latest='';
    $r=$conn->query("SELECT run_key FROM {$t} WHERE race_date=".cq($conn,$date)." ORDER BY created_at DESC,id DESC LIMIT 1");
    if($r && ($row=$r->fetch_assoc())) $latest=(string)$row['run_key'];

    $before=0;
    $r=$conn->query("SELECT COUNT(*) c FROM {$t} WHERE race_date=".cq($conn,$date));
    if($r && ($row=$r->fetch_assoc())) $before=(int)$row['c'];

    $deleted=0;
    if($execute && $latest!==''){
        $conn->query("DELETE FROM {$t} WHERE race_date=".cq($conn,$date)." AND run_key<>".cq($conn,$latest));
        $deleted=(int)$conn->affected_rows;
    }

    $after=$before-$deleted;
    $report[$t]=array('latest_run_key'=>$latest,'before'=>$before,'deleted'=>$deleted,'after'=>$after);
}
?><!doctype html><html><head><meta charset="utf-8"><title>V44 Duplicate Cleanup</title>
<style>body{background:#050914;color:#eef;font-family:Arial;padding:24px}.box{background:#0c1423;border:1px solid #345;border-radius:12px;padding:16px;margin:12px 0}.gold{color:#ffc107}.ok{color:#32ffa7}a{color:#ffc107}</style></head>
<body><h1>V44 Duplicate Cleanup v45.1</h1>
<div class="box">
<p>Race Date: <b class="gold"><?=ch($date)?></b></p>
<p>Preview mode by default. Execute only after backup.</p>
<p><a href="?race_date=<?=urlencode($date)?>&execute=1">Execute cleanup for this date</a></p>
</div>
<?php foreach($report as $t=>$r):?>
<div class="box"><h2><?=ch($t)?></h2><pre><?=ch(json_encode($r,JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT))?></pre></div>
<?php endforeach;?>
</body></html>
