<?php

declare(strict_types=1);
$options=getopt('', ['target::']);
$root=rtrim((string)($options['target']??'C:\\xampp\\htdocs\\ddb-new'),"\\/");
$bootstrap=$root.DIRECTORY_SEPARATOR.'app'.DIRECTORY_SEPARATOR.'bootstrap.php';
if(!is_file($bootstrap)){fwrite(STDERR,"TARGET_BOOTSTRAP_NOT_FOUND|$bootstrap\n");exit(10);}require $bootstrap;
use App\Core\Database;
try{
 $id=Database::fetchOne('njch',"SELECT UPPER(SYS_CONTEXT('USERENV','DB_NAME')) DB_NAME,UPPER(SYS_CONTEXT('USERENV','CURRENT_SCHEMA')) CURRENT_SCHEMA FROM dual");
 $row=Database::fetchOne('njch',"SELECT COUNT(*) TOTAL_CASES,SUM(CASE WHEN STORE_CODE IN (56,57) THEN 1 ELSE 0 END) EXCLUDED_STORE_CASES,SUM(CASE WHEN STORE_CODE IS NULL OR STORE_CODE NOT IN (56,57) THEN 1 ELSE 0 END) GOVERNED_CASES,SUM(CASE WHEN WORKFLOW_STAGE_CODE IS NULL THEN 1 ELSE 0 END) NULL_STAGES,SUM(CASE WHEN WORKFLOW_STAGE_CODE NOT IN ('NEW','ASSIGNED','UNDER_REVIEW','INFORMATION_REQUIRED','FINDING_PROPOSED','SUPERVISOR_REVIEW','MANAGEMENT_APPROVAL','CORRECTIVE_ACTION','CLOSED') THEN 1 ELSE 0 END) BAD_STAGES FROM DASH_DIAR_CASES");
 $byStore=Database::fetchAll('njch',"SELECT STORE_CODE,COUNT(*) CASE_COUNT,SUM(CASE WHEN WORKFLOW_STAGE_CODE='CLOSED' THEN 1 ELSE 0 END) CLOSED_CASES,SUM(CASE WHEN WORKFLOW_STAGE_CODE<>'CLOSED' THEN 1 ELSE 0 END) OPEN_CASES FROM DASH_DIAR_CASES WHERE STORE_CODE IN (56,57) GROUP BY STORE_CODE ORDER BY STORE_CODE");
 echo 'DIAR_6B_4_R1_1_INTEGRITY_DIAGNOSTICS|PASS|DB='.(string)($id['db_name']??'').'|SCHEMA='.(string)($id['current_schema']??'').'|TOTAL_CASES='.(int)($row['total_cases']??0).'|GOVERNED_CASES='.(int)($row['governed_cases']??0).'|EXCLUDED_STORE_CASES='.(int)($row['excluded_store_cases']??0).'|NULL_STAGES='.(int)($row['null_stages']??0).'|BAD_STAGES='.(int)($row['bad_stages']??0).'|WRITES=NONE'.PHP_EOL;
 foreach($byStore as $s){echo 'EXCLUDED_STORE_DETAIL|STORE='.(string)($s['store_code']??'').'|CASES='.(int)($s['case_count']??0).'|OPEN='.(int)($s['open_cases']??0).'|CLOSED='.(int)($s['closed_cases']??0).PHP_EOL;}
 exit(0);
}catch(Throwable $e){fwrite(STDERR,'DIAR_6B_4_R1_1_INTEGRITY_DIAGNOSTICS|FAIL|ERROR='.$e->getMessage().PHP_EOL);exit(20);}
