<?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;

const BRANCH='njch';
function execSql(string $sql,array $params=[]):void{Database::execute(BRANCH,$sql,$params);}
function qddl(string $ddl):string{return str_replace(']~','] ~',$ddl);}
function addColumn(string $table,string $column,string $definition):void{
 $ddl=str_replace("'","''",'ALTER TABLE '.$table.' ADD ('.$column.' '.$definition.')');
 execSql("DECLARE n NUMBER; BEGIN SELECT COUNT(*) INTO n FROM USER_TAB_COLUMNS WHERE TABLE_NAME='".strtoupper($table)."' AND COLUMN_NAME='".strtoupper($column)."'; IF n=0 THEN EXECUTE IMMEDIATE '".$ddl."'; END IF; END;");
}
function createTable(string $name,string $ddl):void{execSql("BEGIN EXECUTE IMMEDIATE q'~".qddl($ddl)."~'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -955 THEN RAISE; END IF; END;");}
function createSequence(string $name):void{execSql("BEGIN EXECUTE IMMEDIATE 'CREATE SEQUENCE ".$name." START WITH 1 INCREMENT BY 1 NOCACHE'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -955 THEN RAISE; END IF; END;");}
function createIndex(string $name,string $ddl):void{$safe=str_replace("'","''",$ddl);execSql("DECLARE n NUMBER; BEGIN SELECT COUNT(*) INTO n FROM USER_INDEXES WHERE INDEX_NAME='".strtoupper($name)."'; IF n=0 THEN EXECUTE IMMEDIATE '".$safe."'; END IF; END;");}
function createConstraint(string $table,string $name,string $clause):void{$safe=str_replace("'","''",'ALTER TABLE '.$table.' ADD CONSTRAINT '.$name.' '.$clause);execSql("DECLARE n NUMBER; BEGIN SELECT COUNT(*) INTO n FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME='".strtoupper($name)."'; IF n=0 THEN EXECUTE IMMEDIATE '".$safe."'; END IF; END;");}

try{
 $id=Database::fetchOne(BRANCH,"SELECT UPPER(SYS_CONTEXT('USERENV','DB_NAME')) DB_NAME,UPPER(SYS_CONTEXT('USERENV','CURRENT_SCHEMA')) CURRENT_SCHEMA FROM dual");
 if(strtoupper((string)($id['db_name']??''))!=='ORANJC'||strtoupper((string)($id['current_schema']??''))!=='WATANY') throw new RuntimeException('CONTROL_DB_IDENTITY_REQUIRED_ORANJC_WATANY');
 $base=Database::fetchOne(BRANCH,"SELECT COUNT(*) OBJECT_COUNT FROM USER_TABLES WHERE TABLE_NAME IN ('DASH_DIAR_CASES','DASH_DIAR_CASE_ASSIGNMENTS','DASH_DIAR_CASE_HISTORY')");
 if((int)($base['object_count']??0)!==3) throw new RuntimeException('DIAR4B_BASE_SCHEMA_REQUIRED');

 addColumn('DASH_DIAR_CASES','WORKFLOW_STAGE_CODE',"VARCHAR2(30) DEFAULT 'NEW' NOT NULL");
 addColumn('DASH_DIAR_CASES','SUPERVISOR_USERNAME','VARCHAR2(128)');
 addColumn('DASH_DIAR_CASES','MANAGEMENT_APPROVER','VARCHAR2(128)');
 addColumn('DASH_DIAR_CASES','REOPEN_COUNT','NUMBER DEFAULT 0 NOT NULL');
 addColumn('DASH_DIAR_CASES','LAST_ACTION_AT','DATE');
 createConstraint('DASH_DIAR_CASES','CK_DIAR_CASE_WF_STAGE',"CHECK (WORKFLOW_STAGE_CODE IN ('NEW','ASSIGNED','UNDER_REVIEW','INFORMATION_REQUIRED','FINDING_PROPOSED','SUPERVISOR_REVIEW','MANAGEMENT_APPROVAL','CORRECTIVE_ACTION','CLOSED'))");
 createConstraint('DASH_DIAR_CASES','CK_DIAR_CASE_REOPEN','CHECK (REOPEN_COUNT>=0)');
 createIndex('IX_DIAR_CASE_WF_QUEUE','CREATE INDEX IX_DIAR_CASE_WF_QUEUE ON DASH_DIAR_CASES (WORKFLOW_STAGE_CODE,DUE_AT,SEVERITY_CODE,UPDATED_AT)');

 createTable('DASH_DIAR_CASE_INFO_REQUESTS',"CREATE TABLE DASH_DIAR_CASE_INFO_REQUESTS (
  REQUEST_ID NUMBER NOT NULL,CASE_ID NUMBER NOT NULL,REQUESTED_FROM VARCHAR2(200) NOT NULL,
  REQUEST_TEXT VARCHAR2(4000) NOT NULL,STATUS_CODE VARCHAR2(20) DEFAULT 'OPEN' NOT NULL,
  REQUESTED_BY VARCHAR2(128) NOT NULL,REQUESTED_AT DATE DEFAULT SYSDATE NOT NULL,DUE_AT DATE,
  RESPONSE_TEXT VARCHAR2(4000),RESPONDED_BY VARCHAR2(128),RESPONDED_AT DATE,
  CONSTRAINT PK_DIAR_INFO_REQ PRIMARY KEY (REQUEST_ID),
  CONSTRAINT FK_DIAR_INFO_CASE FOREIGN KEY (CASE_ID) REFERENCES DASH_DIAR_CASES(CASE_ID),
  CONSTRAINT CK_DIAR_INFO_STATUS CHECK (STATUS_CODE IN ('OPEN','RESPONDED','CANCELLED'))
 )");
 createTable('DASH_DIAR_CASE_FINDINGS',"CREATE TABLE DASH_DIAR_CASE_FINDINGS (
  FINDING_ID NUMBER NOT NULL,CASE_ID NUMBER NOT NULL,OUTCOME_CODE VARCHAR2(40) NOT NULL,
  SUMMARY_TEXT VARCHAR2(4000) NOT NULL,ROOT_CAUSE_TEXT VARCHAR2(4000) NOT NULL,
  RECOMMENDED_ACTION_TEXT VARCHAR2(4000),REQUIRES_CORRECTIVE_ACTION NUMBER(1) DEFAULT 0 NOT NULL,
  STATUS_CODE VARCHAR2(30) DEFAULT 'SUBMITTED' NOT NULL,PROPOSED_BY VARCHAR2(128) NOT NULL,
  PROPOSED_AT DATE DEFAULT SYSDATE NOT NULL,UPDATED_BY VARCHAR2(128) NOT NULL,UPDATED_AT DATE DEFAULT SYSDATE NOT NULL,
  CONSTRAINT PK_DIAR_FINDING PRIMARY KEY (FINDING_ID),
  CONSTRAINT FK_DIAR_FIND_CASE FOREIGN KEY (CASE_ID) REFERENCES DASH_DIAR_CASES(CASE_ID),
  CONSTRAINT CK_DIAR_FIND_CORR CHECK (REQUIRES_CORRECTIVE_ACTION IN (0,1)),
  CONSTRAINT CK_DIAR_FIND_STATUS CHECK (STATUS_CODE IN ('DRAFT','SUBMITTED','RETURNED','SUPERVISOR_APPROVED','APPROVED','REJECTED','SUPERSEDED'))
 )");
 createTable('DASH_DIAR_CASE_APPROVALS',"CREATE TABLE DASH_DIAR_CASE_APPROVALS (
  APPROVAL_ID NUMBER NOT NULL,CASE_ID NUMBER NOT NULL,FINDING_ID NUMBER NOT NULL,
  APPROVAL_LEVEL VARCHAR2(20) NOT NULL,DECISION_CODE VARCHAR2(20) NOT NULL,
  DECISION_COMMENT VARCHAR2(4000) NOT NULL,DECIDED_BY VARCHAR2(128) NOT NULL,DECIDED_AT DATE DEFAULT SYSDATE NOT NULL,
  CONSTRAINT PK_DIAR_APPROVAL PRIMARY KEY (APPROVAL_ID),
  CONSTRAINT FK_DIAR_APPR_CASE FOREIGN KEY (CASE_ID) REFERENCES DASH_DIAR_CASES(CASE_ID),
  CONSTRAINT FK_DIAR_APPR_FIND FOREIGN KEY (FINDING_ID) REFERENCES DASH_DIAR_CASE_FINDINGS(FINDING_ID),
  CONSTRAINT CK_DIAR_APPR_LEVEL CHECK (APPROVAL_LEVEL IN ('SUPERVISOR','MANAGEMENT')),
  CONSTRAINT CK_DIAR_APPR_DECISION CHECK (DECISION_CODE IN ('APPROVED','RETURNED','REJECTED'))
 )");
 createTable('DASH_DIAR_CORRECTIVE_ACTIONS',"CREATE TABLE DASH_DIAR_CORRECTIVE_ACTIONS (
  ACTION_ID NUMBER NOT NULL,CASE_ID NUMBER NOT NULL,ACTION_TITLE VARCHAR2(500) NOT NULL,
  ACTION_DESCRIPTION VARCHAR2(4000),ACTION_OWNER VARCHAR2(128) NOT NULL,DUE_AT DATE,
  STATUS_CODE VARCHAR2(20) DEFAULT 'OPEN' NOT NULL,CREATED_BY VARCHAR2(128) NOT NULL,CREATED_AT DATE DEFAULT SYSDATE NOT NULL,
  COMPLETION_NOTE VARCHAR2(4000),COMPLETED_BY VARCHAR2(128),COMPLETED_AT DATE,
  CONSTRAINT PK_DIAR_CORR_ACTION PRIMARY KEY (ACTION_ID),
  CONSTRAINT FK_DIAR_CORR_CASE FOREIGN KEY (CASE_ID) REFERENCES DASH_DIAR_CASES(CASE_ID),
  CONSTRAINT CK_DIAR_CORR_STATUS CHECK (STATUS_CODE IN ('OPEN','IN_PROGRESS','COMPLETED','CANCELLED'))
 )");
 foreach(['DASH_DIAR_INFO_REQ_SEQ','DASH_DIAR_FINDING_SEQ','DASH_DIAR_APPROVAL_SEQ','DASH_DIAR_CORR_ACTION_SEQ'] as $seq) createSequence($seq);
 createIndex('IX_DIAR_INFO_CASE','CREATE INDEX IX_DIAR_INFO_CASE ON DASH_DIAR_CASE_INFO_REQUESTS (CASE_ID,STATUS_CODE,REQUESTED_AT)');
 createIndex('IX_DIAR_FIND_CASE','CREATE INDEX IX_DIAR_FIND_CASE ON DASH_DIAR_CASE_FINDINGS (CASE_ID,STATUS_CODE,PROPOSED_AT)');
 createIndex('IX_DIAR_APPR_CASE','CREATE INDEX IX_DIAR_APPR_CASE ON DASH_DIAR_CASE_APPROVALS (CASE_ID,APPROVAL_LEVEL,DECIDED_AT)');
 createIndex('IX_DIAR_CORR_CASE','CREATE INDEX IX_DIAR_CORR_CASE ON DASH_DIAR_CORRECTIVE_ACTIONS (CASE_ID,STATUS_CODE,DUE_AT)');

 execSql("UPDATE DASH_DIAR_CASES c SET
  WORKFLOW_STAGE_CODE=CASE
   WHEN c.STATUS_CODE='CLOSED' THEN 'CLOSED'
   WHEN c.STATUS_CODE='ESCALATED' THEN 'SUPERVISOR_REVIEW'
   WHEN c.STATUS_CODE='EVIDENCE_REQUESTED' THEN 'INFORMATION_REQUIRED'
   WHEN c.STATUS_CODE IN ('EXPLAINED_ACTIVITY','CONFIRMED_ANOMALY','FALSE_POSITIVE') THEN 'FINDING_PROPOSED'
   WHEN c.STATUS_CODE='UNDER_REVIEW' THEN 'UNDER_REVIEW'
   WHEN EXISTS (SELECT 1 FROM DASH_DIAR_CASE_ASSIGNMENTS a WHERE a.CASE_ID=c.CASE_ID AND a.STATUS_CODE='ACTIVE') THEN 'ASSIGNED'
   ELSE 'NEW' END,
  DUE_AT=NVL(c.DUE_AT,c.CREATED_AT+CASE c.SEVERITY_CODE WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 3 WHEN 'MEDIUM' THEN 7 ELSE 14 END),
  LAST_ACTION_AT=NVL(c.UPDATED_AT,c.CREATED_AT),REOPEN_COUNT=NVL(c.REOPEN_COUNT,0)
 WHERE c.LAST_ACTION_AT IS NULL");

 // Add only DIAR role-template overrides. No user is assigned or elevated here.
 $acl=Database::fetchOne(BRANCH,"SELECT COUNT(*) OBJECT_COUNT FROM USER_TABLES WHERE TABLE_NAME IN ('DASH_ACL_ROLES','DASH_ACL_ROLE_OVERRIDES')");
 if((int)($acl['object_count']??0)===2){
  $grants=[
   'DIAR_INVESTIGATOR'=>[
    'diar.investigation.view','diar.investigation.review','diar.investigation.evidence',
    'diar.investigation.escalate','diar.investigation.request_information','diar.investigation.finding.propose'
   ],
   'DIAR_INV_SUPERVISOR'=>[
    'diar.investigation.view','diar.investigation.assign','diar.investigation.review',
    'diar.investigation.evidence','diar.investigation.escalate','diar.investigation.close',
    'diar.investigation.request_information','diar.investigation.finding.propose',
    'diar.investigation.supervisor_review','diar.investigation.corrective_action'
   ],
   'DIAR_AUDIT_DIRECTOR'=>[
    'diar.investigation.view','diar.investigation.assign','diar.investigation.review',
    'diar.investigation.evidence','diar.investigation.escalate','diar.investigation.close',
    'diar.investigation.request_information','diar.investigation.finding.propose',
    'diar.investigation.supervisor_review','diar.investigation.management_approve',
    'diar.investigation.corrective_action','diar.investigation.reopen'
   ],
   'DIAR_READ_ONLY_AUDITOR'=>['diar.investigation.view'],
  ];
  foreach($grants as $role=>$permissions){foreach($permissions as $permission){execSql("MERGE INTO DASH_ACL_ROLE_OVERRIDES t USING (SELECT ROLE_CODE FROM DASH_ACL_ROLES WHERE ROLE_CODE=:role_code) s ON (t.ROLE_CODE=s.ROLE_CODE AND t.PERMISSION_CODE=:permission_match) WHEN MATCHED THEN UPDATE SET t.EFFECT='ALLOW',t.UPDATED_BY='DIAR-6B.4',t.UPDATED_AT=SYSDATE WHEN NOT MATCHED THEN INSERT (ROLE_CODE,PERMISSION_CODE,EFFECT,UPDATED_BY,UPDATED_AT) VALUES (s.ROLE_CODE,:permission_insert,'ALLOW','DIAR-6B.4',SYSDATE)",['role_code'=>$role,'permission_match'=>$permission,'permission_insert'=>$permission]);}}
 }

 $check=Database::fetchOne(BRANCH,"SELECT
  (SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME='DASH_DIAR_CASES' AND COLUMN_NAME IN ('WORKFLOW_STAGE_CODE','SUPERVISOR_USERNAME','MANAGEMENT_APPROVER','REOPEN_COUNT','LAST_ACTION_AT')) WF_COLUMNS,
  (SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME IN ('DASH_DIAR_CASE_INFO_REQUESTS','DASH_DIAR_CASE_FINDINGS','DASH_DIAR_CASE_APPROVALS','DASH_DIAR_CORRECTIVE_ACTIONS')) WF_TABLES,
  (SELECT COUNT(*) FROM DASH_DIAR_CASES WHERE WORKFLOW_STAGE_CODE IS NULL) NULL_STAGES
 FROM dual");
 if((int)($check['wf_columns']??0)!==5||(int)($check['wf_tables']??0)!==4||(int)($check['null_stages']??-1)!==0) throw new RuntimeException('POST_MIGRATION_VERIFY_FAILED');
 echo 'DIAR_6B_4_ORACLE_MIGRATION|PASS|DB=ORANJC|SCHEMA=WATANY|ADDITIVE_COLUMNS=5|NEW_TABLES=4|NEW_SEQUENCES=4|ROLE_TEMPLATES=UPDATED|USER_ASSIGNMENTS=NONE|SOURCE_MM_WRITES=NONE'.PHP_EOL;
 exit(0);
}catch(Throwable $e){fwrite(STDERR,'DIAR_6B_4_ORACLE_MIGRATION|FAIL|ERROR='.$e->getMessage().PHP_EOL);exit(21);}
