<?php

declare(strict_types=1);

require dirname(__DIR__) . '/app/bootstrap.php';

use App\Core\Database;
use App\Repositories\RevenueRepository;

$hospital = strtolower((string)($argv[1] ?? 'njch'));
$year = (int)($argv[2] ?? date('Y'));
$compareYear = (int)($argv[3] ?? ($year - 1));
$endMonth = max(1, min(12, (int)($argv[4] ?? max(1, ((int)date('n')) - 1))));
$iterations = max(1, min(10, (int)($argv[5] ?? 3)));
if ($year === $compareYear) { fwrite(STDERR, "Current and comparison years must be different.\n"); exit(2); }
$hospitalCfg = config('hospitals.' . $hospital);
if (!$hospitalCfg) { fwrite(STDERR, "Unknown hospital\n"); exit(2); }
$moh = (string)$hospitalCfg['moh_customer_code'];
$outDir = dirname(__DIR__) . '/storage/evidence/revenue-sql-plan-' . date('Ymd_His');
@mkdir($outDir, 0775, true);

function timed(callable $fn): array {
    $start = microtime(true); $error = null; $result = null;
    try { $result = $fn(); } catch (Throwable $e) { $error = $e->getMessage(); }
    return ['ms'=>round((microtime(true)-$start)*1000,3),'result'=>$result,'error'=>$error];
}
function normalize(array $bundle): array {
    $out=[];
    foreach (['total','cash','insurance','moh'] as $type) {
        foreach ($bundle[$type] ?? [] as $row) {
            $m=(int)($row['code']??0);
            $out[$type][$m]=[(float)($row['current_value']??0),(float)($row['compare_value']??0)];
        }
    }
    return $out;
}
function captureRuntimePlan(string $hospital, int $year, int $compareYear, int $endMonth, string $moh): array {
    $connection = Database::connection($hospital);
    $driver = (string)$connection['driver'];
    $conn = $connection['conn'];
    $sql = "SELECT /*+ GATHER_PLAN_STATISTICS */ /* R45P221_RUNTIME_PLAN */\n"
         . " YEARID, VISIT_MONTH,\n"
         . " SUM(NVL(CREDIT_VALUE,0)+NVL(CASH_VALUE,0)) TOTAL_AMOUNT,\n"
         . " SUM(NVL(CASH_VALUE,0)) CASH_AMOUNT,\n"
         . " SUM(NVL(CREDIT_VALUE,0)) INSURANCE_AMOUNT,\n"
         . " SUM(CASE WHEN CUSTOMER_CODE=:moh THEN NVL(CREDIT_VALUE,0)+NVL(CASH_VALUE,0) ELSE 0 END) MOH_AMOUNT\n"
         . " FROM T_INP_OUTP_REVENUE_BATCH\n"
         . " WHERE YEARID IN (:year_id,:compare_year_id) AND VISIT_MONTH BETWEEN 1 AND :end_month\n"
         . " GROUP BY YEARID,VISIT_MONTH";
    $planSql = "SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +COST +BYTES'))";
    $rows=[]; $plan=[];
    if ($driver === 'pdo_oci' && $conn instanceof PDO) {
        $stmt=$conn->prepare($sql);
        $stmt->bindValue(':moh',$moh,PDO::PARAM_STR); $stmt->bindValue(':year_id',$year,PDO::PARAM_INT);
        $stmt->bindValue(':compare_year_id',$compareYear,PDO::PARAM_INT); $stmt->bindValue(':end_month',$endMonth,PDO::PARAM_INT);
        $stmt->execute(); while(($r=$stmt->fetch(PDO::FETCH_ASSOC))!==false){$rows[]=$r;} $stmt->closeCursor();
        $ps=$conn->prepare($planSql); $ps->execute();
        while(($r=$ps->fetch(PDO::FETCH_NUM))!==false){$plan[]=(string)($r[0]??'');} $ps->closeCursor();
    } elseif ($driver === 'oci8') {
        $stmt=oci_parse($conn,$sql); $bindMoh=$moh;$bindYear=$year;$bindCompare=$compareYear;$bindMonth=$endMonth;
        oci_bind_by_name($stmt,':moh',$bindMoh);oci_bind_by_name($stmt,':year_id',$bindYear);oci_bind_by_name($stmt,':compare_year_id',$bindCompare);oci_bind_by_name($stmt,':end_month',$bindMonth);
        if(!oci_execute($stmt,OCI_NO_AUTO_COMMIT)){ $e=oci_error($stmt); throw new RuntimeException($e['message']??'OCI execute failed'); }
        while(($r=oci_fetch_array($stmt,OCI_ASSOC+OCI_RETURN_NULLS))!==false){$rows[]=array_change_key_case($r,CASE_LOWER);} oci_free_statement($stmt);
        $ps=oci_parse($conn,$planSql); if(!oci_execute($ps,OCI_NO_AUTO_COMMIT)){ $e=oci_error($ps); throw new RuntimeException($e['message']??'DBMS_XPLAN failed'); }
        while(($r=oci_fetch_array($ps,OCI_NUM+OCI_RETURN_NULLS))!==false){$plan[]=(string)($r[0]??'');} oci_free_statement($ps);
    } else {
        throw new RuntimeException('Runtime DBMS_XPLAN capture supports PDO_OCI and OCI8. Active driver: '.$driver);
    }
    if (!$plan) { throw new RuntimeException('DBMS_XPLAN returned no plan lines. Check SELECT_CATALOG_ROLE or grant on DBMS_XPLAN.'); }
    return ['driver'=>$driver,'query_rows'=>count($rows),'plan_lines'=>$plan];
}

$repo = new RevenueRepository($hospital);
$from = sprintf('%04d-01-01',$year);
$to = sprintf('%04d-%02d-%02d',$year,$endMonth,(int)(new DateTimeImmutable(sprintf('%04d-%02d-01',$year,$endMonth)))->format('t'));
$legacy=[]; $optimized=[]; $legacyBundle=null; $optimizedBundle=null; $errors=[];
for($i=0;$i<$iterations;$i++) {
    $a=timed(function() use($repo,$year,$compareYear,$moh,$from,$to){
        $ref=new ReflectionClass($repo); $m=$ref->getMethod('monthlyRevenueBundleRange'); $m->setAccessible(true);
        return $m->invoke($repo,$year,$compareYear,$moh,$from,$to);
    });
    $b=timed(function() use($repo,$year,$compareYear,$moh,$from,$to){ return $repo->monthlyRevenueBundle($year,$compareYear,$moh,$from,$to); });
    $legacy[]=$a['ms']; $optimized[]=$b['ms']; $legacyBundle=$a['result']; $optimizedBundle=$b['result'];
    if($a['error']){$errors[]='legacy: '.$a['error'];} if($b['error']){$errors[]='optimized: '.$b['error'];}
    if($a['error']||$b['error']) { break; }
}
$checks=0;$failed=[];$tol=0.01;
$la=is_array($legacyBundle)?normalize($legacyBundle):[]; $op=is_array($optimizedBundle)?normalize($optimizedBundle):[];
foreach(['total','cash','insurance','moh'] as $type) for($m=1;$m<=12;$m++) foreach([0,1] as $side){
    $checks++; $x=$la[$type][$m][$side]??0.0; $y=$op[$type][$m][$side]??0.0;
    if(abs($x-$y)>$tol) $failed[]=['type'=>$type,'month'=>$m,'side'=>$side===0?'current':'compare','legacy'=>$x,'optimized'=>$y,'difference'=>$y-$x];
}
$plan=[];$planError=null;
try { $plan=captureRuntimePlan($hospital,$year,$compareYear,$endMonth,$moh); }
catch(Throwable $e) { $planError=$e->getMessage(); }
$avg=function(array $v): float { return $v?round(array_sum($v)/count($v),3):0.0; };
$legacyAvg=$avg($legacy);$optimizedAvg=$avg($optimized);
$summary=[
 'schema'=>'DDB_REVENUE_SQL_PLAN_PROFILE_V2','hospital'=>$hospital,'year'=>$year,'compare_year'=>$compareYear,'months'=>'1-'.$endMonth,
 'iterations'=>$iterations,'legacy_range_ms'=>$legacy,'optimized_year_month_ms'=>$optimized,
 'legacy_avg_ms'=>$legacyAvg,'optimized_avg_ms'=>$optimizedAvg,
 'improvement_percent'=>$legacyAvg>0?round((($legacyAvg-$optimizedAvg)/$legacyAvg)*100,2):0,
 'numeric_parity'=>count($failed)===0&&count($errors)===0?'PASS':'FAIL','numeric_checks'=>$checks,'failed_checks'=>count($failed),
 'differences'=>$failed,'errors'=>$errors,'plan_available'=>$planError===null,'plan_error'=>$planError,
 'plan_capture_mode'=>'DISPLAY_CURSOR_SAME_SESSION_NO_EXPLAIN_PLAN','oracle_writes'=>'NONE','generated_at'=>date(DATE_ATOM)
];
file_put_contents($outDir.'/REVENUE_SQL_PLAN_PROFILE.json',json_encode($summary,JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES));
file_put_contents($outDir.'/REVENUE_OPTIMIZED_PLAN.json',json_encode($plan,JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES));
if(isset($plan['plan_lines'])) file_put_contents($outDir.'/REVENUE_OPTIMIZED_PLAN.txt',implode(PHP_EOL,$plan['plan_lines']).PHP_EOL);
$fh=fopen($outDir.'/REVENUE_SQL_TIMINGS.csv','wb');fputcsv($fh,['path','iteration','milliseconds']);
foreach($legacy as $i=>$v) fputcsv($fh,['legacy_date_range',$i+1,$v]); foreach($optimized as $i=>$v) fputcsv($fh,['optimized_year_month',$i+1,$v]); fclose($fh);
echo json_encode($summary,JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES).PHP_EOL; echo "Evidence: $outDir".PHP_EOL;
exit($summary['numeric_parity']==='PASS'?0:3);
