<?php

declare(strict_types=1);

/**
 * Canonical visit linkage and operational reconciliation.
 *
 * The clinical, scheduling, queue, and finance tables remain authoritative for
 * their own data. The visits table supplies a stable cross-module identity and
 * current operational state without permitting silent edits to signed clinical
 * or posted financial records.
 */

function visit_integrity_schema_ready(): bool
{
    try {
        foreach (['visits','visit_state_history','visit_integrity_issues'] as $tableName) {
            if (!schema_table_exists(table($tableName))) return false;
        }
        foreach (['appointments','queue_entries','clinical_encounters','invoices','follow_ups','visit_workflow_events'] as $tableName) {
            if (!schema_column_exists(table($tableName),'visit_id')) return false;
        }
        return true;
    } catch (Throwable) {
        return false;
    }
}

function visit_make_code(PDO $pdo): string
{
    for ($attempt=0;$attempt<20;$attempt++) {
        $code='V'.date('ymd').'-'.strtoupper(bin2hex(random_bytes(3)));
        $stmt=$pdo->prepare('SELECT COUNT(*) FROM `' . table('visits') . '` WHERE visit_code=?');
        $stmt->execute([$code]);
        if ((int)$stmt->fetchColumn()===0) return $code;
    }
    return 'V'.date('ymdHis').'-'.random_int(100,999);
}

function visit_entity_row(PDO $pdo, string $type, int $id): ?array
{
    if ($id<1 || !visit_integrity_schema_ready()) return null;
    $map=[
        'appointment'=>['appointments','id,visit_id,patient_id,branch_id,series_id,start_at,status'],
        'queue'=>['queue_entries','id,visit_id,patient_id,branch_id,appointment_id,checked_in_at,status'],
        'encounter'=>['clinical_encounters','id,visit_id,patient_id,branch_id,appointment_id,queue_entry_id,created_at,status,paused_at'],
        'invoice'=>['invoices','id,visit_id,patient_id,branch_id,encounter_id,queue_entry_id,created_at,status,amount,paid_amount'],
        'follow_up'=>['follow_ups','id,visit_id,patient_id,branch_id,encounter_id,appointment_id,created_at,status,recommended_date'],
    ];
    if (!isset($map[$type])) return null;
    [$tableName,$columns]=$map[$type];
    $stmt=$pdo->prepare('SELECT '.$columns.' FROM `' . table($tableName) . '` WHERE id=? LIMIT 1');
    $stmt->execute([$id]);
    return $stmt->fetch() ?: null;
}

function visit_context(PDO $pdo, array $links): array
{
    $appointmentId=max(0,(int)($links['appointment_id']??0));
    $queueId=max(0,(int)($links['queue_entry_id']??$links['queue_id']??0));
    $encounterId=max(0,(int)($links['encounter_id']??0));
    $invoiceId=max(0,(int)($links['invoice_id']??0));
    $followUpId=max(0,(int)($links['follow_up_id']??0));
    $rows=[];

    if($invoiceId){$rows['invoice']=visit_entity_row($pdo,'invoice',$invoiceId);if($rows['invoice']){$encounterId=$encounterId?: (int)($rows['invoice']['encounter_id']??0);$queueId=$queueId?: (int)($rows['invoice']['queue_entry_id']??0);}}
    if($followUpId){
        $rows['follow_up']=visit_entity_row($pdo,'follow_up',$followUpId);
        if($rows['follow_up']){
            $sourceEncounterId=(int)($rows['follow_up']['encounter_id']??0);
            if($sourceEncounterId>0)$encounterId=$encounterId?:$sourceEncounterId;
            elseif($appointmentId<1)$appointmentId=(int)($rows['follow_up']['appointment_id']??0);
        }
    }
    if($encounterId){$rows['encounter']=visit_entity_row($pdo,'encounter',$encounterId);if($rows['encounter']){$queueId=$queueId?: (int)($rows['encounter']['queue_entry_id']??0);$appointmentId=$appointmentId?: (int)($rows['encounter']['appointment_id']??0);}}
    if($queueId){$rows['queue']=visit_entity_row($pdo,'queue',$queueId);if($rows['queue'])$appointmentId=$appointmentId?: (int)($rows['queue']['appointment_id']??0);}
    if($appointmentId)$rows['appointment']=visit_entity_row($pdo,'appointment',$appointmentId);

    // Discover downstream records so one call can link the complete visit.
    if($appointmentId && !$queueId){$stmt=$pdo->prepare('SELECT id FROM `' . table('queue_entries') . '` WHERE appointment_id=? ORDER BY id DESC LIMIT 1');$stmt->execute([$appointmentId]);$queueId=(int)($stmt->fetchColumn()?:0);if($queueId)$rows['queue']=visit_entity_row($pdo,'queue',$queueId);}
    if($queueId && !$encounterId){$stmt=$pdo->prepare('SELECT id FROM `' . table('clinical_encounters') . '` WHERE queue_entry_id=? ORDER BY id DESC LIMIT 1');$stmt->execute([$queueId]);$encounterId=(int)($stmt->fetchColumn()?:0);if($encounterId)$rows['encounter']=visit_entity_row($pdo,'encounter',$encounterId);}
    if($queueId && !$invoiceId){$stmt=$pdo->prepare('SELECT id FROM `' . table('invoices') . '` WHERE queue_entry_id=? AND status NOT IN ("voided","refunded") ORDER BY id DESC LIMIT 1');$stmt->execute([$queueId]);$invoiceId=(int)($stmt->fetchColumn()?:0);if($invoiceId)$rows['invoice']=visit_entity_row($pdo,'invoice',$invoiceId);}
    if($encounterId && !$followUpId){$stmt=$pdo->prepare('SELECT id FROM `' . table('follow_ups') . '` WHERE encounter_id=? ORDER BY id DESC LIMIT 1');$stmt->execute([$encounterId]);$followUpId=(int)($stmt->fetchColumn()?:0);if($followUpId)$rows['follow_up']=visit_entity_row($pdo,'follow_up',$followUpId);}

    $patientIds=[];$branchIds=[];$visitIds=[];
    foreach($rows as $row){if(!$row)continue;$pid=(int)($row['patient_id']??0);if($pid)$patientIds[$pid]=true;$bid=(int)($row['branch_id']??0);if($bid)$branchIds[$bid]=true;$vid=(int)($row['visit_id']??0);if($vid)$visitIds[$vid]=true;}
    $explicitPatient=(int)($links['patient_id']??0);if($explicitPatient)$patientIds[$explicitPatient]=true;
    $explicitBranch=(int)($links['branch_id']??0);if($explicitBranch)$branchIds[$explicitBranch]=true;
    if(count($patientIds)>1)throw new RuntimeException('The linked appointment, queue, clinical record, or invoice belongs to a different patient. Automatic visit linking was stopped.');
    if(count($branchIds)>1)throw new RuntimeException('The linked records belong to different clinic branches. Use Operations Exceptions to review the visit before reconnecting it.');
    if(count($visitIds)>1)throw new RuntimeException('The records are already assigned to different visit IDs. Use Operations Exceptions to review the links.');

    return [
        'visit_id'=>(int)(array_key_first($visitIds)??0),
        'patient_id'=>(int)(array_key_first($patientIds)??0),
        'branch_id'=>(int)(array_key_first($branchIds)??0),
        'appointment_id'=>$appointmentId,'queue_entry_id'=>$queueId,'encounter_id'=>$encounterId,'invoice_id'=>$invoiceId,'follow_up_id'=>$followUpId,
        'series_id'=>(int)($rows['appointment']['series_id']??0),
        'source_type'=>$appointmentId>0?'appointment':'walk_in',
        'started_at'=>(string)($rows['queue']['checked_in_at']??$rows['appointment']['start_at']??$rows['encounter']['created_at']??date('Y-m-d H:i:s')),
        'rows'=>$rows,
    ];
}

function visit_ensure(PDO $pdo, array $links, bool $create=true): int
{
    if(!visit_integrity_schema_ready())return 0;
    $context=visit_context($pdo,$links);
    if($context['patient_id']<1)return 0;
    $visitId=(int)$context['visit_id'];
    if($visitId<1 && $create){
        try{
            $pdo->prepare('INSERT INTO `' . table('visits') . '` (visit_code,branch_id,patient_id,appointment_id,queue_entry_id,encounter_id,primary_invoice_id,follow_up_id,series_id,source_type,state_code,started_at,last_transition_at,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,? ,"scheduled",?,NOW(),NOW(),NOW())')
                ->execute([visit_make_code($pdo),$context['branch_id']?:null,$context['patient_id'],$context['appointment_id']?:null,$context['queue_entry_id']?:null,$context['encounter_id']?:null,$context['invoice_id']?:null,$context['follow_up_id']?:null,$context['series_id']?:null,$context['source_type'],$context['started_at']?:null]);
            $visitId=(int)$pdo->lastInsertId();
        }catch(PDOException $insertError){
            if((string)$insertError->getCode()!=='23000')throw $insertError;
            $candidates=[];$candidateArgs=[];
            foreach(['appointment_id','queue_entry_id','encounter_id'] as $column){$value=(int)($context[$column]??0);if($value>0){$candidates[]='`'.$column.'`=?';$candidateArgs[]=$value;}}
            if(!$candidates)throw $insertError;
            $candidateArgs[]=$context['patient_id'];
            $stmt=$pdo->prepare('SELECT id FROM `' . table('visits') . '` WHERE ('.implode(' OR ',$candidates).') AND patient_id=? ORDER BY id LIMIT 1');
            $stmt->execute($candidateArgs);$visitId=(int)($stmt->fetchColumn()?:0);if($visitId<1)throw $insertError;
        }
    }
    if($visitId<1)return 0;

    $pdo->prepare('UPDATE `' . table('visits') . '` SET branch_id=COALESCE(branch_id,?),appointment_id=COALESCE(appointment_id,?),queue_entry_id=COALESCE(queue_entry_id,?),encounter_id=COALESCE(encounter_id,?),primary_invoice_id=COALESCE(primary_invoice_id,?),follow_up_id=COALESCE(follow_up_id,?),series_id=COALESCE(series_id,?),source_type=?,started_at=COALESCE(started_at,?),updated_at=NOW() WHERE id=? AND patient_id=?')
        ->execute([$context['branch_id']?:null,$context['appointment_id']?:null,$context['queue_entry_id']?:null,$context['encounter_id']?:null,$context['invoice_id']?:null,$context['follow_up_id']?:null,$context['series_id']?:null,$context['source_type'],$context['started_at']?:null,$visitId,$context['patient_id']]);

    $linkMap=[
        ['appointments',$context['appointment_id']],['queue_entries',$context['queue_entry_id']],['clinical_encounters',$context['encounter_id']],['invoices',$context['invoice_id']],['follow_ups',$context['follow_up_id']],
    ];
    foreach($linkMap as [$tableName,$id]){
        if((int)$id<1)continue;
        $stmt=$pdo->prepare('UPDATE `' . table($tableName) . '` SET visit_id=? WHERE id=? AND patient_id=? AND (visit_id IS NULL OR visit_id=?)');
        $stmt->execute([$visitId,(int)$id,$context['patient_id'],$visitId]);
    }
    // Link any additional invoices belonging to this same queue/encounter.
    if($context['queue_entry_id']>0)$pdo->prepare('UPDATE `' . table('invoices') . '` SET visit_id=? WHERE queue_entry_id=? AND patient_id=? AND visit_id IS NULL')->execute([$visitId,$context['queue_entry_id'],$context['patient_id']]);
    if($context['encounter_id']>0){
        $pdo->prepare('UPDATE `' . table('invoices') . '` SET visit_id=? WHERE encounter_id=? AND patient_id=? AND visit_id IS NULL')->execute([$visitId,$context['encounter_id'],$context['patient_id']]);
        $pdo->prepare('UPDATE `' . table('follow_ups') . '` SET visit_id=? WHERE encounter_id=? AND patient_id=? AND visit_id IS NULL')->execute([$visitId,$context['encounter_id'],$context['patient_id']]);
    }
    return $visitId;
}

function visit_state_snapshot(PDO $pdo, int $visitId): array
{
    $visitStmt=$pdo->prepare('SELECT * FROM `' . table('visits') . '` WHERE id=? LIMIT 1');$visitStmt->execute([$visitId]);$visit=$visitStmt->fetch();if(!$visit)return [];
    $one=function(string $tableName,string $order='id DESC')use($pdo,$visitId):?array{$stmt=$pdo->prepare('SELECT * FROM `' . table($tableName) . '` WHERE visit_id=? ORDER BY '.$order.' LIMIT 1');$stmt->execute([$visitId]);return $stmt->fetch()?:null;};
    $appointment=$one('appointments');$queue=$one('queue_entries');$encounter=$one('clinical_encounters');$invoice=$one('invoices');$follow=$one('follow_ups');
    $balance=0.0;$invoiceCount=0;
    $stmt=$pdo->prepare('SELECT COUNT(*) c,COALESCE(SUM(CASE WHEN status IN ("unpaid","partial") THEN GREATEST(amount-paid_amount,0) ELSE 0 END),0) balance FROM `' . table('invoices') . '` WHERE visit_id=? AND status NOT IN ("voided","refunded")');$stmt->execute([$visitId]);$fin=$stmt->fetch()?:[];$balance=(float)($fin['balance']??0);$invoiceCount=(int)($fin['c']??0);
    return compact('visit','appointment','queue','encounter','invoice','follow','balance','invoiceCount');
}

function visit_sync_state(PDO $pdo, int $visitId, string $actionCode='sync', ?string $details=null, ?int $changedBy=null): array
{
    if(!visit_integrity_schema_ready()||$visitId<1)return [];
    $snapshot=visit_state_snapshot($pdo,$visitId);if(!$snapshot)return [];
    $visit=$snapshot['visit'];$appointment=$snapshot['appointment'];$queue=$snapshot['queue'];$encounter=$snapshot['encounter'];$follow=$snapshot['follow'];$balance=(float)$snapshot['balance'];
    $appointmentStatus=(string)($appointment['status']??'');$queueStatus=(string)($queue['status']??'');$encounterStatus=(string)($encounter['status']??'');$followStatus=(string)($follow['status']??'');
    $state='scheduled';$ownerRole='receptionist';$ownerUser=null;$outcome=null;$reason=null;$completedAt=null;
    if(in_array($appointmentStatus,['cancelled','no_show','left_before_treatment'],true)||$queueStatus==='cancelled'){$state='closed';$outcome=$appointmentStatus?:'cancelled';$ownerRole=null;$completedAt=(string)($queue['cancelled_at']??$appointment['updated_at']??date('Y-m-d H:i:s'));}
    elseif($queueStatus==='completed'||$appointmentStatus==='completed'){
        if(in_array($followStatus,['pending','deferred'],true)){$state='follow_up';$ownerRole='receptionist';$reason='Dentist follow-up recommendation needs scheduling.';}
        else{$state='completed';$ownerRole=null;$completedAt=(string)($queue['completed_at']??$appointment['updated_at']??date('Y-m-d H:i:s'));}
    }
    elseif($queueStatus==='billing_correction'){$state='exception';$ownerRole='dentist';$ownerUser=(int)($encounter['dentist_user_id']??$queue['dentist_user_id']??0)?:null;$reason='Clinical billing correction required.';}
    elseif(in_array($queueStatus,['waiting_billing','cashier_called'],true)||$balance>0.005||$encounterStatus==='finalized'){$state='cashier';$ownerRole='cashier';}
    elseif($queueStatus==='in_treatment'||in_array($encounterStatus,['draft','pending_review','addendum'],true)){$state='clinical';$ownerRole='dentist';$ownerUser=(int)($encounter['dentist_user_id']??$queue['dentist_user_id']??0)?:null;}
    elseif($queueStatus==='seated'){$state='seated';$ownerRole='dental_assistant';$ownerUser=(int)($queue['dentist_user_id']??0)?:null;}
    elseif($queueStatus==='called'){$state='called';$ownerRole='dental_assistant';}
    elseif($queueStatus==='waiting'){$state='waiting';$ownerRole='receptionist';}
    elseif($appointment){
        $startAt=(string)($appointment['start_at']??'');
        if($startAt!==''&&strtotime($startAt)<time()&&in_array($appointmentStatus,['requested','pending_confirmation','confirmed'],true)){$state='exception';$ownerRole='receptionist';$reason='Past appointment has no attendance outcome.';}
        else{$state='scheduled';$ownerRole='receptionist';}
    }
    $old=(string)($visit['state_code']??'');
    $integrityStatus='ok';
    $issueStmt=$pdo->prepare('SELECT COUNT(*) FROM `' . table('visit_integrity_issues') . '` WHERE visit_id=? AND status IN ("open","in_review")');$issueStmt->execute([$visitId]);if((int)$issueStmt->fetchColumn()>0)$integrityStatus='review';
    $pdo->prepare('UPDATE `' . table('visits') . '` SET state_code=?,outcome_code=?,current_owner_role=?,current_owner_user_id=?,state_reason=?,integrity_status=?,completed_at=?,last_transition_at=IF(state_code<>?,NOW(),last_transition_at),updated_at=NOW() WHERE id=?')
        ->execute([$state,$outcome,$ownerRole,$ownerUser,$reason,$integrityStatus,$completedAt,$state,$visitId]);
    if($old!==$state){
        $pdo->prepare('INSERT INTO `' . table('visit_state_history') . '` (visit_id,previous_state,new_state,action_code,details,changed_by,created_at) VALUES (?,?,?,?,?,?,NOW())')
            ->execute([$visitId,$old?:null,$state,mb_substr($actionCode,0,80),$details!==null?mb_substr($details,0,1000):null,$changedBy?:null]);
    }
    return ['visit_id'=>$visitId,'previous_state'=>$old,'state_code'=>$state,'owner_role'=>$ownerRole,'integrity_status'=>$integrityStatus];
}

function visit_link_and_sync(PDO $pdo, array $links, string $actionCode='sync', ?string $details=null, ?int $changedBy=null): int
{
    $visitId=visit_ensure($pdo,$links,true);
    if($visitId>0)visit_sync_state($pdo,$visitId,$actionCode,$details,$changedBy);
    return $visitId;
}

function visit_issue_upsert(PDO $pdo, array $issue): string
{
    $issueKey=trim((string)($issue['issue_key']??''));
    if($issueKey==='')$issueKey=hash('sha256',implode('|',[(string)($issue['issue_code']??''),(string)($issue['entity_type']??''),(string)($issue['entity_id']??0),(string)($issue['visit_id']??0)]));
    $issuesTable=table('visit_integrity_issues');
    $extended=schema_column_exists($issuesTable,'issue_group')&&schema_column_exists($issuesTable,'source_module')&&schema_column_exists($issuesTable,'due_at')&&schema_column_exists($issuesTable,'metadata_json');
    $metadata=$issue['metadata']??null;
    $metadataJson=is_array($metadata)?json_encode($metadata,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES):(is_string($metadata)&&trim($metadata)!==''?$metadata:null);
    if($extended){
        $stmt=$pdo->prepare('INSERT INTO `' . $issuesTable . '` (issue_key,visit_id,branch_id,patient_id,issue_code,issue_group,source_module,severity,responsible_role,entity_type,entity_id,summary,detail,metadata_json,status,occurrence_count,detected_at,last_detected_at,due_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?, ?,"open",1,NOW(),NOW(),?) ON DUPLICATE KEY UPDATE visit_id=VALUES(visit_id),branch_id=VALUES(branch_id),patient_id=VALUES(patient_id),issue_group=VALUES(issue_group),source_module=VALUES(source_module),severity=VALUES(severity),responsible_role=VALUES(responsible_role),summary=VALUES(summary),detail=VALUES(detail),metadata_json=VALUES(metadata_json),due_at=COALESCE(due_at,VALUES(due_at)),occurrence_count=occurrence_count+IF(status="resolved" OR last_detected_at<DATE_SUB(NOW(),INTERVAL 5 MINUTE),1,0),last_detected_at=IF(status="resolved" OR last_detected_at<DATE_SUB(NOW(),INTERVAL 5 MINUTE),NOW(),last_detected_at),assigned_to_user_id=IF(status="resolved",NULL,assigned_to_user_id),assigned_by_user_id=IF(status="resolved",NULL,assigned_by_user_id),assigned_at=IF(status="resolved",NULL,assigned_at),resolution_action=IF(status="resolved",NULL,resolution_action),resolution_reason=IF(status="resolved",NULL,resolution_reason),status=IF(status="ignored",status,IF(status="in_review","in_review","open")),resolved_by=NULL,resolved_at=NULL');
        $stmt->execute([$issueKey,(int)($issue['visit_id']??0)?:null,(int)($issue['branch_id']??0)?:null,(int)($issue['patient_id']??0)?:null,(string)$issue['issue_code'],(string)($issue['issue_group']??'workflow'),(string)($issue['source_module']??'operations'),(string)($issue['severity']??'warning'),(string)($issue['responsible_role']??'clinic_admin'),(string)($issue['entity_type']??'visit'),(int)($issue['entity_id']??0)?:null,mb_substr((string)$issue['summary'],0,255),mb_substr((string)($issue['detail']??''),0,1000)?:null,$metadataJson,!empty($issue['due_at'])?(string)$issue['due_at']:null]);
    }else{
        $stmt=$pdo->prepare('INSERT INTO `' . $issuesTable . '` (issue_key,visit_id,branch_id,patient_id,issue_code,severity,responsible_role,entity_type,entity_id,summary,detail,status,occurrence_count,detected_at,last_detected_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,"open",1,NOW(),NOW()) ON DUPLICATE KEY UPDATE visit_id=VALUES(visit_id),branch_id=VALUES(branch_id),patient_id=VALUES(patient_id),severity=VALUES(severity),responsible_role=VALUES(responsible_role),summary=VALUES(summary),detail=VALUES(detail),occurrence_count=occurrence_count+IF(status="resolved" OR last_detected_at<DATE_SUB(NOW(),INTERVAL 5 MINUTE),1,0),last_detected_at=IF(status="resolved" OR last_detected_at<DATE_SUB(NOW(),INTERVAL 5 MINUTE),NOW(),last_detected_at),resolution_action=IF(status="resolved",NULL,resolution_action),resolution_reason=IF(status="resolved",NULL,resolution_reason),status=IF(status="ignored",status,IF(status="in_review","in_review","open")),resolved_by=NULL,resolved_at=NULL');
        $stmt->execute([$issueKey,(int)($issue['visit_id']??0)?:null,(int)($issue['branch_id']??0)?:null,(int)($issue['patient_id']??0)?:null,(string)$issue['issue_code'],(string)($issue['severity']??'warning'),(string)($issue['responsible_role']??'clinic_admin'),(string)($issue['entity_type']??'visit'),(int)($issue['entity_id']??0)?:null,mb_substr((string)$issue['summary'],0,255),mb_substr((string)($issue['detail']??''),0,1000)?:null]);
    }
    return $issueKey;
}

function visit_backfill_links(PDO $pdo, int $branchId=0, int $limit=2000): int
{
    if(!visit_integrity_schema_ready())return 0;$linked=0;$limit=max(10,min(10000,$limit));
    $sources=[
        ['appointments','appointment_id','start_at'],['queue_entries','queue_entry_id','checked_in_at'],['clinical_encounters','encounter_id','created_at'],['invoices','invoice_id','created_at'],['follow_ups','follow_up_id','created_at'],
    ];
    foreach($sources as [$tableName,$linkName,$order]){
        $sql='SELECT id,patient_id,branch_id FROM `' . table($tableName) . '` WHERE visit_id IS NULL';$args=[];
        if($branchId>0){$sql.=' AND branch_id=?';$args[]=$branchId;}$sql.=' ORDER BY '.$order.',id LIMIT '.$limit;
        $stmt=$pdo->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $row){try{$vid=visit_ensure($pdo,[$linkName=>(int)$row['id'],'patient_id'=>(int)$row['patient_id'],'branch_id'=>(int)($row['branch_id']??0)],true);if($vid){visit_sync_state($pdo,$vid,'backfill','Canonical visit linked during update.',(int)(current_user()['id']??0));$linked++;}}catch(Throwable $e){error_log('Visit backfill warning for '.$tableName.' #'.$row['id'].': '.$e->getMessage());}}
    }
    return $linked;
}

function visit_scan_integrity(PDO $pdo, int $branchId=0): array
{
    if(!visit_integrity_schema_ready())return ['detected'=>0,'linked'=>0];
    $linked=visit_backfill_links($pdo,$branchId,2000);$keys=[];$detected=0;
    $record=function(array $issue)use($pdo,&$keys,&$detected):void{$key=visit_issue_upsert($pdo,$issue);$keys[$key]=true;$detected++;if(!empty($issue['visit_id']))visit_sync_state($pdo,(int)$issue['visit_id'],'integrity_scan',(string)$issue['summary'],null);};
    $branchClause=$branchId>0?' AND COALESCE(a.branch_id,p.branch_id)=?':'';$args=$branchId>0?[$branchId]:[];
    $stmt=$pdo->prepare('SELECT a.id,a.visit_id,a.patient_id,COALESCE(a.branch_id,p.branch_id) branch_id,a.status,a.start_at,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('appointments') . '` a JOIN `' . table('patients') . '` p ON p.id=a.patient_id WHERE a.start_at<NOW() AND a.status IN ("requested","pending_confirmation","confirmed") AND NOT EXISTS(SELECT 1 FROM `' . table('queue_entries') . '` q WHERE q.appointment_id=a.id)'.$branchClause.' ORDER BY a.start_at LIMIT 1000');$stmt->execute($args);
    foreach($stmt->fetchAll() as $r)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'past_appointment_no_outcome','issue_group'=>'queue','source_module'=>'queue','severity'=>'urgent','responsible_role'=>'receptionist','entity_type'=>'appointment','entity_id'=>$r['id'],'summary'=>'Past appointment has no attendance outcome','detail'=>$r['patient'].' · '.date('M j, Y g:i A',strtotime($r['start_at'])).'. Record check-in, no-show, or cancellation.']);

    $stmt=$pdo->prepare('SELECT a.id,a.visit_id,a.patient_id,COALESCE(a.branch_id,p.branch_id) branch_id,a.status,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('appointments') . '` a JOIN `' . table('patients') . '` p ON p.id=a.patient_id WHERE a.status IN ("checked_in","called","seated","in_treatment","awaiting_billing") AND NOT EXISTS(SELECT 1 FROM `' . table('queue_entries') . '` q WHERE q.appointment_id=a.id)'.$branchClause.' LIMIT 1000');$stmt->execute($args);
    foreach($stmt->fetchAll() as $r)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'operational_appointment_without_queue','issue_group'=>'queue','source_module'=>'queue','severity'=>'critical','responsible_role'=>'clinic_admin','entity_type'=>'appointment','entity_id'=>$r['id'],'summary'=>'Operational appointment has no queue visit','detail'=>$r['patient'].' is marked '.str_replace('_',' ',$r['status']).' but no queue ticket exists.']);

    $queueBranch=$branchId>0?' AND COALESCE(q.branch_id,p.branch_id)=?':'';$qargs=$branchId>0?[$branchId]:[];
    $stmt=$pdo->prepare('SELECT q.id,q.visit_id,q.patient_id,COALESCE(q.branch_id,p.branch_id) branch_id,q.queue_no,q.status,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('queue_entries') . '` q JOIN `' . table('patients') . '` p ON p.id=q.patient_id LEFT JOIN `' . table('appointments') . '` a ON a.id=q.appointment_id WHERE q.appointment_id IS NOT NULL AND a.id IS NULL'.$queueBranch.' LIMIT 1000');$stmt->execute($qargs);
    foreach($stmt->fetchAll() as $r)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'queue_missing_appointment','issue_group'=>'queue','source_module'=>'queue','severity'=>'critical','responsible_role'=>'clinic_admin','entity_type'=>'queue_entry','entity_id'=>$r['id'],'summary'=>'Queue visit points to a missing appointment','detail'=>$r['queue_no'].' · '.$r['patient'].'. The queue record was preserved for review.']);

    $stmt=$pdo->prepare('SELECT q.id,q.visit_id,q.patient_id,COALESCE(q.branch_id,p.branch_id) branch_id,q.queue_no,q.status,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('queue_entries') . '` q JOIN `' . table('patients') . '` p ON p.id=q.patient_id LEFT JOIN `' . table('clinical_encounters') . '` c ON c.queue_entry_id=q.id WHERE q.status="in_treatment" AND c.id IS NULL'.$queueBranch.' LIMIT 1000');$stmt->execute($qargs);
    foreach($stmt->fetchAll() as $r)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'treatment_without_encounter','issue_group'=>'clinical','source_module'=>'clinical','severity'=>'critical','responsible_role'=>'dentist','entity_type'=>'queue_entry','entity_id'=>$r['id'],'summary'=>'Patient is in treatment without a clinical encounter','detail'=>$r['queue_no'].' · '.$r['patient'].'. Start or reconnect the clinical encounter before continuing.']);

    $encBranch=$branchId>0?' AND COALESCE(c.branch_id,p.branch_id)=?':'';$eargs=$branchId>0?[$branchId]:[];
    $stmt=$pdo->prepare('SELECT c.id,c.visit_id,c.patient_id,COALESCE(c.branch_id,p.branch_id) branch_id,c.queue_entry_id,c.billing_handoff_amount,CONCAT(p.first_name," ",p.last_name) patient,COUNT(pr.id) procedure_count FROM `' . table('clinical_encounters') . '` c JOIN `' . table('patients') . '` p ON p.id=c.patient_id JOIN `' . table('procedures') . '` pr ON pr.encounter_id=c.id AND pr.status="completed" LEFT JOIN `' . table('invoices') . '` i ON i.encounter_id=c.id AND i.status NOT IN ("voided","refunded") WHERE c.status="finalized" AND c.completion_destination="cashier" AND c.billing_handoff_amount>0.005 AND i.id IS NULL'.$encBranch.' GROUP BY c.id LIMIT 1000');$stmt->execute($eargs);
    foreach($stmt->fetchAll() as $r)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'finalized_encounter_missing_invoice','issue_group'=>'billing','source_module'=>'finance','severity'=>'critical','responsible_role'=>'dentist','entity_type'=>'clinical_encounter','entity_id'=>$r['id'],'summary'=>'Finalized treatment has no invoice','detail'=>$r['patient'].' · '.$r['procedure_count'].' completed procedure(s) · expected handoff ₱'.number_format((float)$r['billing_handoff_amount'],2).'. Rebuild the approved clinical invoice.']);

    $stmt=$pdo->prepare('SELECT q.id,q.visit_id,q.patient_id,COALESCE(q.branch_id,p.branch_id) branch_id,q.queue_no,CONCAT(p.first_name," ",p.last_name) patient,COALESCE(SUM(CASE WHEN i.status IN ("unpaid","partial") THEN GREATEST(i.amount-i.paid_amount,0) ELSE 0 END),0) balance FROM `' . table('queue_entries') . '` q JOIN `' . table('patients') . '` p ON p.id=q.patient_id JOIN `' . table('invoices') . '` i ON i.queue_entry_id=q.id AND i.status NOT IN ("voided","refunded") WHERE q.status="completed"'.$queueBranch.' GROUP BY q.id HAVING balance>0.005 LIMIT 1000');$stmt->execute($qargs);
    foreach($stmt->fetchAll() as $r)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'completed_visit_open_balance','issue_group'=>'billing','source_module'=>'finance','severity'=>'critical','responsible_role'=>'cashier','entity_type'=>'queue_entry','entity_id'=>$r['id'],'summary'=>'Completed visit still has an unpaid balance','detail'=>$r['queue_no'].' · '.$r['patient'].' · balance ₱'.number_format((float)$r['balance'],2).'. Financial records were not changed automatically.']);

    $sessionBranch=$branchId>0?' AND s.branch_id=?':'';$sargs=$branchId>0?[$branchId]:[];
    $stmt=$pdo->prepare('SELECT s.id,s.branch_id,s.cashier_user_id,s.locked_patient_id,s.locked_queue_entry_id,s.transaction_locked_at,u.name cashier,q.status queue_status FROM `' . table('cashier_sessions') . '` s LEFT JOIN `' . table('users') . '` u ON u.id=s.cashier_user_id LEFT JOIN `' . table('queue_entries') . '` q ON q.id=s.locked_queue_entry_id WHERE s.locked_patient_id IS NOT NULL AND s.locked_patient_id<>0 AND (s.status<>"open" OR s.transaction_locked_at<DATE_SUB(NOW(),INTERVAL 4 HOUR) OR q.status IN ("completed","cancelled"))'.$sessionBranch.' LIMIT 1000');$stmt->execute($sargs);
    foreach($stmt->fetchAll() as $r){$vid=0;if((int)$r['locked_queue_entry_id']>0){$qr=visit_entity_row($pdo,'queue',(int)$r['locked_queue_entry_id']);$vid=(int)($qr['visit_id']??0);}$record(['visit_id'=>$vid,'branch_id'=>$r['branch_id'],'patient_id'=>$r['locked_patient_id'],'issue_code'=>'abandoned_cashier_lock','issue_group'=>'billing','source_module'=>'finance','severity'=>'urgent','responsible_role'=>'clinic_admin','entity_type'=>'cashier_session','entity_id'=>$r['id'],'summary'=>'Cashier transaction lock appears abandoned','detail'=>($r['cashier']?:'Cashier').' · locked since '.($r['transaction_locked_at']?:'unknown time').'.']);}

    $stmt=$pdo->prepare('SELECT q1.id,q1.visit_id,q1.patient_id,q1.branch_id,q1.queue_no,q2.id duplicate_id,q2.queue_no duplicate_no FROM `' . table('queue_entries') . '` q1 JOIN `' . table('queue_entries') . '` q2 ON q2.appointment_id=q1.appointment_id AND q2.id>q1.id AND q1.appointment_id IS NOT NULL WHERE q1.status NOT IN ("completed","cancelled") AND q2.status NOT IN ("completed","cancelled")'.($branchId>0?' AND q1.branch_id=?':'').' LIMIT 1000');$stmt->execute($branchId>0?[$branchId]:[]);
    foreach($stmt->fetchAll() as $r)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'duplicate_active_queue','issue_group'=>'queue','source_module'=>'queue','severity'=>'critical','responsible_role'=>'clinic_admin','entity_type'=>'queue_entry','entity_id'=>$r['duplicate_id'],'summary'=>'Appointment has duplicate active queue tickets','detail'=>$r['queue_no'].' and '.$r['duplicate_no'].' are both active. Do not delete either record; review and close the incorrect ticket.']);

    $stmt=$pdo->prepare('SELECT q1.id,q1.visit_id,q1.patient_id,q1.branch_id,q1.queue_no,q1.chair_name,q2.id duplicate_id,q2.queue_no duplicate_no FROM `' . table('queue_entries') . '` q1 JOIN `' . table('queue_entries') . '` q2 ON q2.id>q1.id AND q2.branch_id=q1.branch_id AND ((q1.chair_id IS NOT NULL AND q1.chair_id=q2.chair_id) OR (q1.chair_id IS NULL AND q2.chair_id IS NULL AND q1.chair_name<>"" AND LOWER(q1.chair_name)=LOWER(q2.chair_name))) WHERE q1.status IN ("seated","in_treatment") AND q2.status IN ("seated","in_treatment")'.($branchId>0?' AND q1.branch_id=?':'').' LIMIT 1000');$stmt->execute($branchId>0?[$branchId]:[]);
    foreach($stmt->fetchAll() as $r)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'duplicate_chair_occupancy','issue_group'=>'chair','source_module'=>'queue','severity'=>'critical','responsible_role'=>'dental_assistant','entity_type'=>'queue_entry','entity_id'=>$r['duplicate_id'],'summary'=>'Two active patients occupy the same chair','detail'=>($r['chair_name']?:'Chair').' · '.$r['queue_no'].' and '.$r['duplicate_no'].'. Release or move the incorrect seating record.']);

    // Resolve scan-generated issues that disappeared after the latest complete scan.
    $knownCodes=['past_appointment_no_outcome','operational_appointment_without_queue','queue_missing_appointment','treatment_without_encounter','finalized_encounter_missing_invoice','completed_visit_open_balance','abandoned_cashier_lock','duplicate_active_queue','duplicate_chair_occupancy'];
    $codeMarks=implode(',',array_fill(0,count($knownCodes),'?'));
    $params=$knownCodes;
    $sql='UPDATE `' . table('visit_integrity_issues') . '` SET status="resolved",resolved_at=NOW(),resolution_action="condition_cleared",resolution_reason="The condition was no longer present during reconciliation." WHERE status IN ("open","in_review") AND issue_code IN ('.$codeMarks.')';
    if($keys){$placeholders=implode(',',array_fill(0,count($keys),'?'));$sql.=' AND issue_key NOT IN ('.$placeholders.')';$params=array_merge($params,array_keys($keys));}
    if($branchId>0){$sql.=' AND branch_id=?';$params[]=$branchId;}
    $pdo->prepare($sql)->execute($params);
    return ['detected'=>$detected,'linked'=>$linked];
}

function visit_issue_record(PDO $pdo, int $issueId, bool $lock=false): ?array
{
    $stmt=$pdo->prepare('SELECT i.*,v.appointment_id visit_appointment_id,v.queue_entry_id visit_queue_id,v.encounter_id visit_encounter_id,v.primary_invoice_id visit_invoice_id,p.patient_no,CONCAT(p.first_name," ",p.last_name) patient_name,b.name branch_name,au.name assigned_to_name,abu.name assigned_by_name FROM `' . table('visit_integrity_issues') . '` i LEFT JOIN `' . table('visits') . '` v ON v.id=i.visit_id LEFT JOIN `' . table('patients') . '` p ON p.id=i.patient_id LEFT JOIN `' . table('branches') . '` b ON b.id=i.branch_id LEFT JOIN `' . table('users') . '` au ON au.id=i.assigned_to_user_id LEFT JOIN `' . table('users') . '` abu ON abu.id=i.assigned_by_user_id WHERE i.id=? LIMIT 1'.($lock?' FOR UPDATE':''));$stmt->execute([$issueId]);return $stmt->fetch()?:null;
}

function visit_resolve_issue(PDO $pdo, int $issueId, string $action, string $reason, int $userId): string
{
    if(!has_role(['super_admin','clinic_admin']))throw new RuntimeException('Only an Administrator can change linked visit states from the Operations Exception Center.');
    $reason=trim($reason);if(mb_strlen($reason)<5)throw new RuntimeException('Enter a clear correction reason.');
    require_once DENTAL_ROOT . '/app/transaction_integrity.php';
    require_once DENTAL_ROOT . '/app/queue_flow.php';
    require_once DENTAL_ROOT . '/app/clinical_billing.php';
    $pdo->beginTransaction();
    try{
        $issue=visit_issue_record($pdo,$issueId,true);if(!$issue)throw new RuntimeException('Workflow issue not found.');if(!in_array((string)$issue['status'],['open','in_review'],true))throw new RuntimeException('This workflow issue has already been closed.');
        $old=[];$new=[];$message='Workflow issue resolved.';$entityId=(int)($issue['entity_id']??0);
        if($action==='mark_no_show'){
            if($issue['entity_type']!=='appointment')throw new RuntimeException('This action is only valid for an appointment exception.');
            $stmt=$pdo->prepare('SELECT * FROM `' . table('appointments') . '` WHERE id=? FOR UPDATE');$stmt->execute([$entityId]);$row=$stmt->fetch();if(!$row)throw new RuntimeException('Appointment not found.');
            if(!in_array((string)$row['status'],['requested','pending_confirmation','confirmed'],true)||strtotime((string)$row['start_at'])>=time())throw new RuntimeException('Only a past unclosed appointment can be marked no-show.');
            $old=['status'=>$row['status']];$pdo->prepare('UPDATE `' . table('appointments') . '` SET status="no_show",updated_at=NOW() WHERE id=? AND status=?')->execute([$entityId,$row['status']]);$pdo->prepare('INSERT INTO `' . table('appointment_status_history') . '` (appointment_id,previous_status,new_status,reason,changed_by,created_at) VALUES (?, ?,"no_show",?,?,NOW())')->execute([$entityId,$row['status'],$reason,$userId]);$new=['status'=>'no_show'];$message='Appointment marked as no-show.';
        }elseif($action==='cancel_appointment'){
            if($issue['entity_type']!=='appointment')throw new RuntimeException('This action is only valid for an appointment exception.');
            $stmt=$pdo->prepare('SELECT * FROM `' . table('appointments') . '` WHERE id=? FOR UPDATE');$stmt->execute([$entityId]);$row=$stmt->fetch();if(!$row)throw new RuntimeException('Appointment not found.');if(in_array((string)$row['status'],['completed','cancelled','no_show'],true))throw new RuntimeException('The appointment is already closed.');
            $old=['status'=>$row['status']];integrity_cancel_appointment_queue($pdo,$entityId,$reason);$pdo->prepare('UPDATE `' . table('appointments') . '` SET status="cancelled",updated_at=NOW() WHERE id=?')->execute([$entityId]);$pdo->prepare('INSERT INTO `' . table('appointment_status_history') . '` (appointment_id,previous_status,new_status,reason,changed_by,created_at) VALUES (?, ?,"cancelled",?,?,NOW())')->execute([$entityId,$row['status'],$reason,$userId]);$new=['status'=>'cancelled'];$message='Appointment and any active linked queue visit were cancelled.';
        }elseif($action==='rebuild_invoice'){
            $encounterId=$issue['entity_type']==='clinical_encounter'?$entityId:(int)($issue['visit_encounter_id']??0);if($encounterId<1)throw new RuntimeException('No clinical encounter is linked to this issue.');
            $stmt=$pdo->prepare('SELECT * FROM `' . table('clinical_encounters') . '` WHERE id=? FOR UPDATE');$stmt->execute([$encounterId]);$enc=$stmt->fetch();if(!$enc||$enc['status']!=='finalized')throw new RuntimeException('Only a finalized encounter can rebuild its invoice.');$procedures=clinical_billing_procedures($pdo,$encounterId,true);if(!$procedures)throw new RuntimeException('No completed approved procedure is available to invoice.');$invoiceId=clinical_billing_sync_invoice($pdo,$enc,$procedures,$userId);$old=['invoice_id'=>(int)($issue['visit_invoice_id']??0)];$new=['invoice_id'=>$invoiceId];$message='The approved clinical invoice was rebuilt.';
        }elseif($action==='release_chair'){
            $queueId=$issue['entity_type']==='queue_entry'?$entityId:(int)($issue['visit_queue_id']??0);
            if($queueId<1)throw new RuntimeException('No queue visit is linked to this issue.');
            $stmt=$pdo->prepare('SELECT q.*,c.status encounter_status,c.paused_at,c.completion_destination FROM `' . table('queue_entries') . '` q LEFT JOIN `' . table('clinical_encounters') . '` c ON c.queue_entry_id=q.id WHERE q.id=? FOR UPDATE');
            $stmt->execute([$queueId]);
            $q=$stmt->fetch();
            if(!$q)throw new RuntimeException('Queue visit not found.');

            $queueStatus=(string)($q['status']??'');
            $chairAssigned=(int)($q['chair_id']??0)>0||trim((string)($q['chair_name']??''))!=='';
            $old=['status'=>$queueStatus,'chair_id'=>$q['chair_id'],'chair_name'=>$q['chair_name']];
            $target=$queueStatus;
            $encounterFinalized=(string)($q['encounter_status']??'')==='finalized';

            if($encounterFinalized){
                $destination=(string)($q['completion_destination']??'cashier');
                if($destination==='no_payment'){
                    $target='completed';
                }elseif(in_array($queueStatus,['seated','in_treatment','waiting','called'],true)||$queueStatus===''){
                    $target='waiting_billing';
                }elseif(!in_array($queueStatus,['waiting_billing','cashier_called','completed','cancelled'],true)){
                    $target='waiting_billing';
                }
            }else{
                if(!in_array($queueStatus,['seated','in_treatment'],true)){
                    throw new RuntimeException('Only an active or finalized treatment visit can use this chair-release correction.');
                }
                if($queueStatus==='in_treatment'&&empty($q['paused_at'])){
                    throw new RuntimeException('Pause or finalize active treatment before releasing its chair.');
                }
                $target='waiting';
            }

            if(!$chairAssigned&&!in_array($queueStatus,['seated','in_treatment'],true)){
                throw new RuntimeException('This visit no longer has a chair assignment to release.');
            }

            $pdo->prepare('UPDATE `' . table('queue_entries') . '` SET status=?,chair_id=NULL,chair_name=NULL,seated_at=NULL,billing_at=IF(?="waiting_billing",COALESCE(billing_at,NOW()),billing_at),completed_at=IF(?="completed",COALESCE(completed_at,NOW()),completed_at),updated_at=NOW() WHERE id=?')
                ->execute([$target,$target,$target,$queueId]);

            if($queueStatus!==$target){
                $pdo->prepare('INSERT INTO `' . table('queue_status_history') . '` (queue_entry_id,previous_status,new_status,notes,changed_by,created_at) VALUES (?,?,?,?,?,NOW())')
                    ->execute([$queueId,$queueStatus,$target,$reason,$userId]);
            }

            if(!empty($q['appointment_id'])){
                $appointmentStatus=['waiting'=>'checked_in','called'=>'checked_in','seated'=>'checked_in','in_treatment'=>'in_treatment','waiting_billing'=>'awaiting_billing','cashier_called'=>'awaiting_billing','completed'=>'completed','cancelled'=>'left_before_treatment'][$target]??null;
                if($appointmentStatus!==null){
                    $appointment=$pdo->prepare('SELECT status FROM `' . table('appointments') . '` WHERE id=? FOR UPDATE');
                    $appointment->execute([(int)$q['appointment_id']]);
                    $oldAppointment=$appointment->fetchColumn();
                    if($oldAppointment!==false){
                        $pdo->prepare('UPDATE `' . table('appointments') . '` SET status=?,chair_id=NULL,chair_name=NULL,updated_at=NOW() WHERE id=?')
                            ->execute([$appointmentStatus,(int)$q['appointment_id']]);
                        if((string)$oldAppointment!==$appointmentStatus&&schema_table_exists(table('appointment_status_history'))){
                            $pdo->prepare('INSERT INTO `' . table('appointment_status_history') . '` (appointment_id,previous_status,new_status,reason,changed_by,created_at) VALUES (?,?,?,?,?,NOW())')
                                ->execute([(int)$q['appointment_id'],(string)$oldAppointment,$appointmentStatus,$reason,$userId]);
                        }
                    }
                }
            }

            $new=['status'=>$target,'chair_id'=>null,'chair_name'=>null];
            $message=$queueStatus===$target
                ? 'The stale chair assignment was cleared without changing the current checkout stage.'
                : 'Chair released and the visit returned to the correct queue stage.';
        }elseif($action==='release_cashier_lock'){
            if($issue['entity_type']!=='cashier_session')throw new RuntimeException('This action is only valid for a cashier-session lock.');$stmt=$pdo->prepare('SELECT * FROM `' . table('cashier_sessions') . '` WHERE id=? FOR UPDATE');$stmt->execute([$entityId]);$session=$stmt->fetch();if(!$session)throw new RuntimeException('Cashier session not found.');$old=['locked_patient_id'=>$session['locked_patient_id'],'locked_queue_entry_id'=>$session['locked_queue_entry_id'],'transaction_locked_at'=>$session['transaction_locked_at']];$pdo->prepare('UPDATE `' . table('cashier_sessions') . '` SET locked_patient_id=NULL,locked_queue_entry_id=NULL,transaction_locked_at=NULL,transaction_lock_note=? WHERE id=?')->execute([mb_substr($reason,0,500),$entityId]);$new=['locked_patient_id'=>null,'locked_queue_entry_id'=>null];$message='Abandoned cashier transaction lock released.';
        }elseif($action==='sync_appointment_status'){
            $queueId=$issue['entity_type']==='queue_entry'?$entityId:(int)($issue['visit_queue_id']??0);if($queueId<1)throw new RuntimeException('No queue visit is linked to this exception.');
            $stmt=$pdo->prepare('SELECT q.*,a.status appointment_status FROM `' . table('queue_entries') . '` q JOIN `' . table('appointments') . '` a ON a.id=q.appointment_id WHERE q.id=? FOR UPDATE');$stmt->execute([$queueId]);$q=$stmt->fetch();if(!$q)throw new RuntimeException('The linked queue or appointment record was not found.');
            $map=['waiting'=>'checked_in','called'=>'checked_in','seated'=>'in_treatment','in_treatment'=>'in_treatment','waiting_billing'=>'awaiting_billing','cashier_called'=>'awaiting_billing','completed'=>'completed','cancelled'=>'cancelled'];$target=$map[(string)$q['status']]??null;if($target===null)throw new RuntimeException('The queue stage cannot be synchronized automatically.');
            $old=['appointment_status'=>$q['appointment_status'],'queue_status'=>$q['status']];$pdo->prepare('UPDATE `' . table('appointments') . '` SET status=?,updated_at=NOW() WHERE id=?')->execute([$target,(int)$q['appointment_id']]);$pdo->prepare('INSERT INTO `' . table('appointment_status_history') . '` (appointment_id,previous_status,new_status,reason,changed_by,created_at) VALUES (?,?,?,?,?,NOW())')->execute([(int)$q['appointment_id'],$q['appointment_status'],$target,$reason,$userId]);$new=['appointment_status'=>$target,'queue_status'=>$q['status']];$message='Appointment status synchronized to the authoritative queue stage.';
        }elseif($action==='complete_paid_queue'){
            $queueId=$issue['entity_type']==='queue_entry'?$entityId:(int)($issue['visit_queue_id']??0);if($queueId<1)throw new RuntimeException('No queue visit is linked to this exception.');
            $stmt=$pdo->prepare('SELECT * FROM `' . table('queue_entries') . '` WHERE id=? FOR UPDATE');$stmt->execute([$queueId]);$q=$stmt->fetch();if(!$q)throw new RuntimeException('Queue visit not found.');if(!in_array((string)$q['status'],['waiting_billing','cashier_called'],true))throw new RuntimeException('The patient is no longer in the cashier stage.');
            $open=$pdo->prepare('SELECT COUNT(*) FROM `' . table('invoices') . '` WHERE queue_entry_id=? AND status NOT IN ("voided","refunded","paid","written_off") AND amount-paid_amount>0.005');$open->execute([$queueId]);if((int)$open->fetchColumn()>0)throw new RuntimeException('An unpaid or partially paid invoice still requires cashier action.');
            $old=['queue_status'=>$q['status']];$pdo->prepare('UPDATE `' . table('queue_entries') . '` SET status="completed",paid_at=COALESCE(paid_at,NOW()),completed_at=COALESCE(completed_at,NOW()),chair_id=NULL,chair_name=NULL,updated_at=NOW() WHERE id=?')->execute([$queueId]);$pdo->prepare('INSERT INTO `' . table('queue_status_history') . '` (queue_entry_id,previous_status,new_status,notes,changed_by,created_at) VALUES (?, ?,"completed",?,?,NOW())')->execute([$queueId,$q['status'],$reason,$userId]);queue_update_appointment_status($pdo,$q['appointment_id']?(int)$q['appointment_id']:null,'completed',$reason);$new=['queue_status'=>'completed'];$message='Settled cashier visit completed and removed from the active queue.';
        }elseif($action==='reconcile_inventory_stock'){
            require_once DENTAL_ROOT . '/app/inventory_integrity.php';$metadata=json_decode((string)($issue['metadata_json']??''),true)?:[];$itemId=(int)($metadata['item_id']??($issue['entity_type']==='inventory_item'?$entityId:0));$branchId=(int)($metadata['branch_id']??$issue['branch_id']??0);if($itemId<1||$branchId<1)throw new RuntimeException('The item and branch could not be identified.');$beforeStmt=$pdo->prepare('SELECT current_stock FROM `' . table('inventory_branch_stocks') . '` WHERE item_id=? AND branch_id=?');$beforeStmt->execute([$itemId,$branchId]);$before=(float)($beforeStmt->fetchColumn()?:0);$after=inventory_reconcile($pdo,$itemId,$branchId);$old=['current_stock'=>$before];$new=['current_stock'=>$after];$message='Branch stock recalculated from posted active lot balances.';
        }elseif($action==='quarantine_lot'){
            if($issue['entity_type']!=='inventory_lot')throw new RuntimeException('This action is only valid for an inventory lot exception.');require_once DENTAL_ROOT . '/app/inventory_integrity.php';$stmt=$pdo->prepare('SELECT id,item_id,branch_id,status,quantity_remaining FROM `' . table('inventory_lots') . '` WHERE id=? FOR UPDATE');$stmt->execute([$entityId]);$lot=$stmt->fetch();if(!$lot)throw new RuntimeException('Inventory lot not found.');if((string)$lot['status']!=='active')throw new RuntimeException('This lot is no longer active.');$old=['status'=>$lot['status'],'quantity_remaining'=>$lot['quantity_remaining']];$pdo->prepare('UPDATE `' . table('inventory_lots') . '` SET status="quarantined",quarantined_at=NOW(),quarantine_reason=?,updated_at=NOW() WHERE id=?')->execute([mb_substr($reason,0,500),$entityId]);$branchStock=inventory_reconcile($pdo,(int)$lot['item_id'],(int)$lot['branch_id']);$new=['status'=>'quarantined','quantity_remaining'=>$lot['quantity_remaining'],'branch_stock'=>$branchStock];$message='Expired or unsafe lot quarantined and branch stock reconciled.';
        }elseif($action==='reconnect_visit'){
            $links=['patient_id'=>(int)$issue['patient_id'],'branch_id'=>(int)$issue['branch_id']];if($issue['entity_type']==='appointment')$links['appointment_id']=$entityId;elseif($issue['entity_type']==='queue_entry')$links['queue_entry_id']=$entityId;elseif($issue['entity_type']==='clinical_encounter')$links['encounter_id']=$entityId;elseif($issue['entity_type']==='invoice')$links['invoice_id']=$entityId;$visitId=visit_ensure($pdo,$links,true);if($visitId<1)throw new RuntimeException('The visit could not be reconnected safely.');$old=['visit_id'=>(int)($issue['visit_id']??0)];$new=['visit_id'=>$visitId];$message='The authoritative records were connected to one canonical visit ID.';
        }elseif($action==='send_to_role'){
            $pdo->prepare('UPDATE `' . table('visit_integrity_issues') . '` SET status="in_review",resolution_action="sent_to_role",resolution_reason=?,resolved_by=NULL,resolved_at=NULL WHERE id=?')->execute([mb_substr($reason,0,1000),$issueId]);if((int)$issue['visit_id']>0)visit_sync_state($pdo,(int)$issue['visit_id'],'exception_routed',$reason,$userId);$pdo->commit();audit('visit_exception_routed','visit_integrity_issue',$issueId,['responsible_role'=>$issue['responsible_role'],'reason'=>$reason]);return 'Issue assigned to '.str_replace('_',' ',(string)$issue['responsible_role']).' for action.';
        }elseif($action==='ignore'){
            $pdo->prepare('UPDATE `' . table('visit_integrity_issues') . '` SET status="ignored",resolved_by=?,resolved_at=NOW(),resolution_action="ignored",resolution_reason=? WHERE id=?')->execute([$userId,mb_substr($reason,0,1000),$issueId]);if((int)$issue['visit_id']>0)visit_sync_state($pdo,(int)$issue['visit_id'],'exception_ignored',$reason,$userId);$pdo->commit();audit('visit_exception_ignored','visit_integrity_issue',$issueId,['reason'=>$reason]);return 'Issue retained in history and marked ignored.';
        }else throw new RuntimeException('Select a valid reconciliation action.');
        $pdo->prepare('UPDATE `' . table('visit_integrity_issues') . '` SET status="resolved",resolved_by=?,resolved_at=NOW(),resolution_action=?,resolution_reason=?,old_values_json=?,new_values_json=? WHERE id=?')->execute([$userId,mb_substr($action,0,80),mb_substr($reason,0,1000),json_encode($old,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES),json_encode($new,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES),$issueId]);
        $visitId=(int)($issue['visit_id']??0);if($visitId<1)$visitId=visit_ensure($pdo,['appointment_id'=>(int)($issue['visit_appointment_id']??0),'queue_entry_id'=>(int)($issue['visit_queue_id']??0),'encounter_id'=>(int)($issue['visit_encounter_id']??0),'invoice_id'=>(int)($issue['visit_invoice_id']??0),'patient_id'=>(int)$issue['patient_id'],'branch_id'=>(int)$issue['branch_id']],true);if($visitId>0)visit_sync_state($pdo,$visitId,'reconcile_'.$action,$reason,$userId);
        $pdo->commit();audit('visit_exception_resolved','visit_integrity_issue',$issueId,['action'=>$action,'reason'=>$reason,'new'=>$new],['old'=>$old]);return $message;
    }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
}
