<?php

declare(strict_types=1);

if (!defined('DENTAL_ROOT')) exit;

require_once DENTAL_ROOT . '/app/visit_integrity.php';

/**
 * Cross-module exception detection. Source records remain authoritative; this
 * service only records discrepancies and exposes narrowly controlled repairs.
 */
function reconciliation_schema_ready(): bool
{
    try {
        $issues=table('visit_integrity_issues');
        if (!visit_integrity_schema_ready()) return false;
        foreach (['issue_group','source_module','assigned_to_user_id','assigned_by_user_id','assigned_at','due_at','metadata_json'] as $column) {
            if (!schema_column_exists($issues,$column)) return false;
        }
        return true;
    } catch (Throwable) {
        return false;
    }
}


function reconciliation_label(string $value): string
{
    $value=trim(str_replace(['_','-'],' ',$value));
    return $value==='' ? 'Unknown' : ucwords($value);
}

function reconciliation_issue_key(string $code,string $entityType,int $entityId,string $identity=''): string
{
    return hash('sha256','reconciliation|'.$code.'|'.$entityType.'|'.$entityId.'|'.$identity);
}

function reconciliation_record(PDO $pdo,array $issue,array &$seen,int &$detected): void
{
    $code=(string)($issue['issue_code']??'workflow_exception');
    $type=(string)($issue['entity_type']??'system');
    $id=(int)($issue['entity_id']??0);
    $identity=(string)($issue['identity']??'');
    $key=reconciliation_issue_key($code,$type,$id,$identity);
    $issue['issue_key']=$key;
    visit_issue_upsert($pdo,$issue);
    $seen[$key]=true;
    $detected++;
    if (!empty($issue['visit_id'])) visit_sync_state($pdo,(int)$issue['visit_id'],'reconciliation_scan',(string)($issue['summary']??'Workflow exception detected.'),null);
}

function reconciliation_resolve_disappeared(PDO $pdo,array $codes,array $seen,int $branchId): void
{
    if (!$codes) return;
    $params=$codes;
    $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 (' . implode(',',array_fill(0,count($codes),'?')) . ')';
    if ($seen) {
        $sql.=' AND issue_key NOT IN (' . implode(',',array_fill(0,count($seen),'?')) . ')';
        $params=array_merge($params,array_keys($seen));
    }
    if ($branchId>0) {$sql.=' AND branch_id=?';$params[]=$branchId;}
    $pdo->prepare($sql)->execute($params);
}

function reconciliation_scan_all(PDO $pdo,int $branchId=0): array
{
    if (!reconciliation_schema_ready()) return ['detected'=>0,'linked'=>0,'modules'=>[]];
    $visit=visit_scan_integrity($pdo,$branchId);
    $seen=[];$detected=0;$modules=['visit'=>(int)($visit['detected']??0),'clinical'=>0,'billing'=>0,'inventory'=>0,'approval'=>0];
    $record=function(array $issue,string $module)use($pdo,&$seen,&$detected,&$modules):void{
        reconciliation_record($pdo,$issue,$seen,$detected);
        $modules[$module]=($modules[$module]??0)+1;
    };

    $patientBranch='COALESCE(p.branch_id,0)';
    $encBranch='COALESCE(c.branch_id,p.branch_id)';
    $queueBranch='COALESCE(q.branch_id,p.branch_id)';

    // Active or finalized clinical record whose queue link is absent or broken.
    $sql='SELECT c.id,c.visit_id,c.patient_id,'.$encBranch.' branch_id,c.status,c.queue_entry_id,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('clinical_encounters') . '` c JOIN `' . table('patients') . '` p ON p.id=c.patient_id LEFT JOIN `' . table('queue_entries') . '` q ON q.id=c.queue_entry_id WHERE c.status IN ("draft","pending_review","finalized") AND (c.queue_entry_id IS NULL OR q.id IS NULL)';$args=[];
    if($branchId>0){$sql.=' AND '.$encBranch.'=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'encounter_without_queue','issue_group'=>'clinical','source_module'=>'clinical','severity'=>'critical','responsible_role'=>'dentist','entity_type'=>'clinical_encounter','entity_id'=>$r['id'],'summary'=>'Clinical encounter is not linked to an active queue visit','detail'=>$r['patient'].' · '.reconciliation_label((string)$r['status']).'. Reconnect the encounter before continuing treatment or discharge.','due_at'=>date('Y-m-d H:i:s',time()+3600)],'clinical');

    // Finalized clinical record must not continue to occupy a chair.
    $sql='SELECT c.id,c.visit_id,c.patient_id,'.$encBranch.' branch_id,c.queue_entry_id,q.queue_no,q.status,q.chair_id,q.chair_name,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('clinical_encounters') . '` c JOIN `' . table('patients') . '` p ON p.id=c.patient_id JOIN `' . table('queue_entries') . '` q ON q.id=c.queue_entry_id WHERE c.status="finalized" AND (q.status IN ("seated","in_treatment") OR q.chair_id IS NOT NULL OR COALESCE(q.chair_name,"")<>"")';$args=[];
    if($branchId>0){$sql.=' AND '.$encBranch.'=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'finalized_encounter_chair_occupied','issue_group'=>'chair','source_module'=>'clinical','severity'=>'critical','responsible_role'=>'dental_assistant','entity_type'=>'queue_entry','entity_id'=>$r['queue_entry_id'],'summary'=>'Finalized treatment is still occupying a chair','detail'=>$r['queue_no'].' · '.$r['patient'].' · '.($r['chair_name']?:'assigned chair').'. Release the chair and restore the correct checkout stage.','due_at'=>date('Y-m-d H:i:s',time()+1800)],'clinical');

    // Dentist-required visit seated or treated without a responsible Dentist.
    $sql='SELECT q.id,q.visit_id,q.patient_id,'.$queueBranch.' 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 WHERE q.provider_type="dentist" AND q.status IN ("seated","in_treatment") AND COALESCE(q.dentist_user_id,0)=0';$args=[];
    if($branchId>0){$sql.=' AND '.$queueBranch.'=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'seated_without_dentist','issue_group'=>'queue','source_module'=>'queue','severity'=>'critical','responsible_role'=>'clinic_admin','entity_type'=>'queue_entry','entity_id'=>$r['id'],'summary'=>'Patient is seated without a responsible Dentist','detail'=>$r['queue_no'].' · '.$r['patient'].'. Assign a credentialed Dentist before clinical work continues.','due_at'=>date('Y-m-d H:i:s',time()+900)],'clinical');

    // A pause configured to release the chair must actually release it.
    $sql='SELECT c.id,c.visit_id,c.patient_id,'.$encBranch.' branch_id,c.queue_entry_id,c.pause_reason,q.queue_no,q.status,q.chair_name,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('clinical_encounters') . '` c JOIN `' . table('patients') . '` p ON p.id=c.patient_id JOIN `' . table('queue_entries') . '` q ON q.id=c.queue_entry_id WHERE c.status IN ("draft","pending_review") AND c.paused_at IS NOT NULL AND c.pause_releases_chair=1 AND (q.status IN ("seated","in_treatment") OR q.chair_id IS NOT NULL OR COALESCE(q.chair_name,"")<>"")';$args=[];
    if($branchId>0){$sql.=' AND '.$encBranch.'=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'paused_treatment_chair_occupied','issue_group'=>'chair','source_module'=>'clinical','severity'=>'urgent','responsible_role'=>'dental_assistant','entity_type'=>'queue_entry','entity_id'=>$r['queue_entry_id'],'summary'=>'Paused treatment still blocks a chair marked for release','detail'=>$r['queue_no'].' · '.$r['patient'].' · '.($r['pause_reason']?:'No pause reason').'.','due_at'=>date('Y-m-d H:i:s',time()+3600)],'clinical');

    // Queue and appointment statuses must represent the same stage.
    $mapping=['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'];
    $sql='SELECT q.id,q.visit_id,q.patient_id,'.$queueBranch.' branch_id,q.queue_no,q.status queue_status,a.id appointment_id,a.status appointment_status,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('queue_entries') . '` q JOIN `' . table('patients') . '` p ON p.id=q.patient_id JOIN `' . table('appointments') . '` a ON a.id=q.appointment_id WHERE q.status IN ("waiting","called","seated","in_treatment","waiting_billing","cashier_called","completed","cancelled")';$args=[];
    if($branchId>0){$sql.=' AND '.$queueBranch.'=?';$args[]=$branchId;}$sql.=' LIMIT 2000';$stmt=$pdo->prepare($sql);$stmt->execute($args);
    foreach($stmt->fetchAll() as $r){$expected=$mapping[(string)$r['queue_status']]??'';if($expected!==''&&(string)$r['appointment_status']!==$expected)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'appointment_queue_status_mismatch','issue_group'=>'queue','source_module'=>'queue','severity'=>'urgent','responsible_role'=>'receptionist','entity_type'=>'queue_entry','entity_id'=>$r['id'],'summary'=>'Appointment and queue statuses do not match','detail'=>$r['queue_no'].' · '.$r['patient'].' · queue: '.reconciliation_label((string)$r['queue_status']).' · appointment: '.reconciliation_label((string)$r['appointment_status']).'.','metadata'=>['expected_appointment_status'=>$expected,'appointment_id'=>(int)$r['appointment_id']],'due_at'=>date('Y-m-d H:i:s',time()+7200)],'clinical');}

    // Care-team attribution and delegated-entry verification.
    $sql='SELECT c.id,c.visit_id,c.patient_id,'.$encBranch.' branch_id,c.assistant_user_id,c.status,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('clinical_encounters') . '` c JOIN `' . table('patients') . '` p ON p.id=c.patient_id WHERE c.assistant_user_id IS NOT NULL AND c.assistant_user_id<>0 AND COALESCE(c.dentist_user_id,0)=0';$args=[];
    if($branchId>0){$sql.=' AND '.$encBranch.'=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'assistant_without_dentist','issue_group'=>'care_team','source_module'=>'clinical','severity'=>'critical','responsible_role'=>'clinic_admin','entity_type'=>'clinical_encounter','entity_id'=>$r['id'],'summary'=>'Dental Assistant is assigned without a responsible Dentist','detail'=>$r['patient'].' · '.reconciliation_label((string)$r['status']).'. A Dental Assistant cannot hold independent clinical responsibility.','due_at'=>date('Y-m-d H:i:s',time()+1800)],'clinical');

    $sql='SELECT pr.id,c.visit_id,pr.patient_id,COALESCE(pr.branch_id,c.branch_id,p.branch_id) branch_id,pr.procedure_name,c.id encounter_id,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('procedures') . '` pr JOIN `' . table('clinical_encounters') . '` c ON c.id=pr.encounter_id JOIN `' . table('patients') . '` p ON p.id=pr.patient_id WHERE pr.entered_by_role="dental_assistant" AND pr.status IN ("in_progress","completed") AND pr.verified_by_user_id IS NULL';$args=[];
    if($branchId>0){$sql.=' AND COALESCE(pr.branch_id,c.branch_id,p.branch_id)=?';$args[]=$branchId;}$sql.=' LIMIT 2000';$stmt=$pdo->prepare($sql);$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'=>'assistant_entry_unverified','issue_group'=>'care_team','source_module'=>'clinical','severity'=>'urgent','responsible_role'=>'dentist','entity_type'=>'procedure','entity_id'=>$r['id'],'summary'=>'Assistant-entered treatment item awaits Dentist verification','detail'=>$r['patient'].' · '.$r['procedure_name'].'. The responsible Dentist must verify or remove the delegated entry.','metadata'=>['encounter_id'=>(int)$r['encounter_id']],'due_at'=>date('Y-m-d H:i:s',time()+14400)],'clinical');

    $sql='SELECT pr.id,c.visit_id,pr.patient_id,COALESCE(pr.branch_id,c.branch_id,p.branch_id) branch_id,pr.procedure_name,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('procedures') . '` pr JOIN `' . table('clinical_encounters') . '` c ON c.id=pr.encounter_id JOIN `' . table('patients') . '` p ON p.id=pr.patient_id WHERE pr.status IN ("in_progress","completed") AND (pr.entered_by_user_id IS NULL OR COALESCE(pr.entered_by_role,"")="")';$args=[];
    if($branchId>0){$sql.=' AND COALESCE(pr.branch_id,c.branch_id,p.branch_id)=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'procedure_missing_author','issue_group'=>'clinical','source_module'=>'clinical','severity'=>'urgent','responsible_role'=>'dentist','entity_type'=>'procedure','entity_id'=>$r['id'],'summary'=>'Treatment entry is missing author attribution','detail'=>$r['patient'].' · '.$r['procedure_name'].'. Review the source encounter before clinical sign-off.','due_at'=>date('Y-m-d H:i:s',time()+14400)],'clinical');

    // Billing reconciliation: paid invoices must leave cashier; line totals and clinical sources must reconcile.
    $sql='SELECT q.id,q.visit_id,q.patient_id,'.$queueBranch.' branch_id,q.queue_no,q.status,CONCAT(p.first_name," ",p.last_name) patient,COUNT(i.id) invoice_count,SUM(CASE WHEN i.status NOT IN ("paid","written_off") AND i.amount-i.paid_amount>0.005 THEN 1 ELSE 0 END) open_count 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 IN ("waiting_billing","cashier_called") GROUP BY q.id HAVING invoice_count>0 AND open_count=0';
    if($branchId>0)$sql='SELECT * FROM ('.$sql.') paidq WHERE paidq.branch_id='.(int)$branchId;$stmt=$pdo->query($sql.' LIMIT 1000');
    foreach($stmt->fetchAll() as $r)$record(['visit_id'=>$r['visit_id'],'branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'paid_visit_still_at_cashier','issue_group'=>'billing','source_module'=>'finance','severity'=>'critical','responsible_role'=>'cashier','entity_type'=>'queue_entry','entity_id'=>$r['id'],'summary'=>'Paid visit is still waiting at Cashier','detail'=>$r['queue_no'].' · '.$r['patient'].'. All active invoices are settled, but the queue was not completed.','due_at'=>date('Y-m-d H:i:s',time()+1800)],'billing');

    // A cashier-stage visit must carry a real invoice or an authorized no-payment outcome.
    $sql='SELECT q.id,q.visit_id,q.patient_id,'.$queueBranch.' branch_id,q.queue_no,c.id encounter_id,c.no_payment_reason,CONCAT(p.first_name," ",p.last_name) patient,COALESCE(SUM(CASE WHEN i.status NOT IN ("voided","refunded") THEN i.amount ELSE 0 END),0) invoice_total 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 LEFT JOIN `' . table('invoices') . '` i ON i.queue_entry_id=q.id WHERE q.status IN ("waiting_billing","cashier_called")';$args=[];
    if($branchId>0){$sql.=' AND '.$queueBranch.'=?';$args[]=$branchId;}$sql.=' GROUP BY q.id HAVING invoice_total<=0.005 AND COALESCE(MAX(c.no_payment_reason),"")="" LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'cashier_zero_without_authorization','issue_group'=>'billing','source_module'=>'finance','severity'=>'critical','responsible_role'=>'dentist','entity_type'=>'queue_entry','entity_id'=>$r['id'],'summary'=>'Patient was sent to Cashier without a charge or no-payment authorization','detail'=>$r['queue_no'].' · '.$r['patient'].'. Return the encounter to the responsible Dentist or complete the authorized no-payment discharge workflow.','metadata'=>['encounter_id'=>(int)($r['encounter_id']??0)],'due_at'=>date('Y-m-d H:i:s',time()+1800)],'billing');

    // Every finalized billable procedure must have one active invoice line.
    $sql='SELECT pr.id procedure_id,c.id encounter_id,c.visit_id,pr.patient_id,COALESCE(pr.branch_id,c.branch_id,p.branch_id) branch_id,pr.procedure_name,pr.approved_fee_snapshot,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('procedures') . '` pr JOIN `' . table('clinical_encounters') . '` c ON c.id=pr.encounter_id JOIN `' . table('patients') . '` p ON p.id=pr.patient_id LEFT JOIN `' . table('invoice_items') . '` ii ON ii.procedure_id=pr.id LEFT JOIN `' . table('invoices') . '` i ON i.id=ii.invoice_id AND i.status NOT IN ("voided","refunded") WHERE c.status="finalized" AND pr.status="completed" AND pr.fee_status="billable" AND COALESCE(pr.approved_fee_snapshot,0)>0.005 GROUP BY pr.id HAVING COUNT(i.id)=0';$args=[];
    if($branchId>0){$sql='SELECT * FROM ('.$sql.') missingline WHERE missingline.branch_id=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'completed_procedure_missing_billing_line','issue_group'=>'billing','source_module'=>'finance','severity'=>'critical','responsible_role'=>'dentist','entity_type'=>'clinical_encounter','entity_id'=>$r['encounter_id'],'identity'=>(string)$r['procedure_id'],'summary'=>'Completed billable procedure is missing from the invoice','detail'=>$r['patient'].' · '.$r['procedure_name'].' · ₱'.number_format((float)$r['approved_fee_snapshot'],2).'. Rebuild the approved invoice before collection.','metadata'=>['procedure_id'=>(int)$r['procedure_id']],'due_at'=>date('Y-m-d H:i:s',time()+3600)],'billing');

    $sql='SELECT i.id,i.visit_id,i.patient_id,COALESCE(i.branch_id,p.branch_id) branch_id,i.invoice_no,i.amount,i.tax_amount,COALESCE(SUM(ii.net_amount),0) line_total,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('invoices') . '` i JOIN `' . table('patients') . '` p ON p.id=i.patient_id LEFT JOIN `' . table('invoice_items') . '` ii ON ii.invoice_id=i.id WHERE i.status NOT IN ("voided","refunded")';$args=[];
    if($branchId>0){$sql.=' AND COALESCE(i.branch_id,p.branch_id)=?';$args[]=$branchId;}$sql.=' GROUP BY i.id HAVING ABS(i.amount-(line_total+i.tax_amount))>0.01 LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'invoice_line_total_mismatch','issue_group'=>'billing','source_module'=>'finance','severity'=>'critical','responsible_role'=>'cashier','entity_type'=>'invoice','entity_id'=>$r['id'],'summary'=>'Invoice total does not match its itemized charges','detail'=>$r['invoice_no'].' · '.$r['patient'].' · invoice ₱'.number_format((float)$r['amount'],2).' versus itemized total ₱'.number_format((float)$r['line_total']+(float)$r['tax_amount'],2).'.','due_at'=>date('Y-m-d H:i:s',time()+3600)],'billing');

    $sql='SELECT MIN(ii.id) id,i.visit_id,i.patient_id,COALESCE(i.branch_id,p.branch_id) branch_id,i.invoice_no,ii.procedure_id,COUNT(*) duplicate_count,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('invoice_items') . '` ii JOIN `' . table('invoices') . '` i ON i.id=ii.invoice_id JOIN `' . table('patients') . '` p ON p.id=i.patient_id WHERE ii.procedure_id IS NOT NULL AND ii.procedure_id<>0 AND i.status NOT IN ("voided","refunded")';$args=[];
    if($branchId>0){$sql.=' AND COALESCE(i.branch_id,p.branch_id)=?';$args[]=$branchId;}$sql.=' GROUP BY i.id,ii.procedure_id HAVING duplicate_count>1 LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'duplicate_procedure_billing_item','issue_group'=>'billing','source_module'=>'finance','severity'=>'critical','responsible_role'=>'cashier','entity_type'=>'invoice','entity_id'=>$r['id'],'identity'=>(string)$r['procedure_id'],'summary'=>'One clinical procedure appears more than once on an invoice','detail'=>$r['invoice_no'].' · '.$r['patient'].' · '.(int)$r['duplicate_count'].' lines reference the same procedure.','due_at'=>date('Y-m-d H:i:s',time()+3600)],'billing');


    // Follow-up recommendation should not disappear after discharge.
    $sql='SELECT c.id,c.visit_id,c.patient_id,'.$encBranch.' branch_id,c.follow_up_date,c.follow_up_purpose,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('clinical_encounters') . '` c JOIN `' . table('patients') . '` p ON p.id=c.patient_id LEFT JOIN `' . table('follow_ups') . '` f ON f.encounter_id=c.id AND f.status NOT IN ("cancelled") WHERE c.status="finalized" AND c.follow_up_date IS NOT NULL AND f.id IS NULL';$args=[];
    if($branchId>0){$sql.=' AND '.$encBranch.'=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'followup_recommendation_missing_record','issue_group'=>'follow_up','source_module'=>'clinical','severity'=>'warning','responsible_role'=>'receptionist','entity_type'=>'clinical_encounter','entity_id'=>$r['id'],'summary'=>'Dentist follow-up recommendation is not in the follow-up queue','detail'=>$r['patient'].' · '.date('M j, Y',strtotime((string)$r['follow_up_date'])).' · '.($r['follow_up_purpose']?:'Follow-up purpose not recorded').'.','due_at'=>date('Y-m-d H:i:s',strtotime((string)$r['follow_up_date'].' 09:00:00'))],'clinical');

    if (schema_table_exists(table('inventory_documents')) && schema_table_exists(table('inventory_transactions'))) {
        // Posted issue must identify an accountable destination.
        $sql='SELECT d.id,d.branch_id,d.patient_id,d.encounter_id,d.procedure_id,d.document_no,d.usage_destination FROM `' . table('inventory_documents') . '` d WHERE d.document_type="issue" AND d.status="posted" AND (COALESCE(d.usage_destination,"")="" OR (d.patient_id IS NULL AND d.requested_for_user_id IS NULL AND d.chair_id IS NULL))';$args=[];
        if($branchId>0){$sql.=' AND d.branch_id=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $r)$record(['branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'inventory_issue_missing_destination','issue_group'=>'inventory','source_module'=>'inventory','severity'=>'critical','responsible_role'=>'inventory_officer','entity_type'=>'inventory_document','entity_id'=>$r['id'],'summary'=>'Posted inventory issue has no accountable destination','detail'=>$r['document_no'].' does not identify a patient encounter, responsible staff member, chair, or department.','due_at'=>date('Y-m-d H:i:s',time()+3600)],'inventory');

        $sql='SELECT d.id,d.branch_id,d.patient_id,d.document_no,d.procedure_id,pr.procedure_name,pr.status procedure_status FROM `' . table('inventory_documents') . '` d JOIN `' . table('procedures') . '` pr ON pr.id=d.procedure_id WHERE d.document_type="issue" AND d.status="posted" AND pr.status="planned"';$args=[];
        if($branchId>0){$sql.=' AND d.branch_id=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $r)$record(['branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'inventory_issue_for_planned_procedure','issue_group'=>'inventory','source_module'=>'inventory','severity'=>'critical','responsible_role'=>'inventory_officer','entity_type'=>'inventory_document','entity_id'=>$r['id'],'summary'=>'Stock was consumed for a procedure that is still only planned','detail'=>$r['document_no'].' · '.$r['procedure_name'].'. Verify the actual treatment status and correct the stock issue through the controlled inventory workflow.','metadata'=>['procedure_id'=>(int)$r['procedure_id']],'due_at'=>date('Y-m-d H:i:s',time()+3600)],'inventory');

        $sql='SELECT d.id,d.branch_id,d.patient_id,d.document_no,d.source_document_id FROM `' . table('inventory_documents') . '` d LEFT JOIN `' . table('inventory_documents') . '` s ON s.id=d.source_document_id AND s.document_type="issue" AND s.status="posted" WHERE d.document_type="return" AND d.status IN ("submitted","approved","posted") AND (d.source_document_id IS NULL OR s.id IS NULL)';$args=[];
        if($branchId>0){$sql.=' AND d.branch_id=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $r)$record(['branch_id'=>$r['branch_id'],'patient_id'=>$r['patient_id'],'issue_code'=>'inventory_return_missing_issue','issue_group'=>'inventory','source_module'=>'inventory','severity'=>'critical','responsible_role'=>'inventory_officer','entity_type'=>'inventory_document','entity_id'=>$r['id'],'summary'=>'Inventory return is not linked to its original issue','detail'=>$r['document_no'].' cannot be validated against an original posted issue.','due_at'=>date('Y-m-d H:i:s',time()+3600)],'inventory');

        $sql='SELECT bs.item_id,bs.branch_id,bs.current_stock,i.name FROM `' . table('inventory_branch_stocks') . '` bs JOIN `' . table('inventory_items') . '` i ON i.id=bs.item_id WHERE bs.current_stock<0';$args=[];
        if($branchId>0){$sql.=' AND bs.branch_id=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $r)$record(['branch_id'=>$r['branch_id'],'issue_code'=>'negative_branch_stock','issue_group'=>'inventory','source_module'=>'inventory','severity'=>'critical','responsible_role'=>'inventory_officer','entity_type'=>'inventory_item','entity_id'=>$r['item_id'],'identity'=>(string)$r['branch_id'],'summary'=>'Inventory balance is negative','detail'=>$r['name'].' · branch balance '.number_format((float)$r['current_stock'],2).'. Reconcile lots and movement history before further issue.','metadata'=>['item_id'=>(int)$r['item_id'],'branch_id'=>(int)$r['branch_id']],'due_at'=>date('Y-m-d H:i:s',time()+1800)],'inventory');

        $sql='SELECT l.id,l.item_id,l.branch_id,l.lot_number,l.expiry_date,l.quantity_remaining,i.name FROM `' . table('inventory_lots') . '` l JOIN `' . table('inventory_items') . '` i ON i.id=l.item_id WHERE l.status="active" AND l.quantity_remaining>0 AND l.expiry_date<CURDATE()';$args=[];
        if($branchId>0){$sql.=' AND l.branch_id=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $r)$record(['branch_id'=>$r['branch_id'],'issue_code'=>'expired_lot_still_active','issue_group'=>'inventory','source_module'=>'inventory','severity'=>'critical','responsible_role'=>'inventory_officer','entity_type'=>'inventory_lot','entity_id'=>$r['id'],'summary'=>'Expired lot remains available for issue','detail'=>$r['name'].' · lot '.($r['lot_number']?:'#'.$r['id']).' · expired '.date('M j, Y',strtotime((string)$r['expiry_date'])).' · '.number_format((float)$r['quantity_remaining'],2).' remaining.','due_at'=>date('Y-m-d H:i:s',time()+1800)],'inventory');

        $sql='SELECT d.id,d.branch_id,d.document_no,d.requested_at,d.destination_branch_id FROM `' . table('inventory_documents') . '` d WHERE d.document_type="transfer" AND d.status="dispatched" AND COALESCE(d.posted_at,d.updated_at,d.requested_at)<DATE_SUB(NOW(),INTERVAL 24 HOUR)';$args=[];
        if($branchId>0){$sql.=' AND (d.source_branch_id=? OR d.destination_branch_id=?)';$args[]=$branchId;$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $r)$record(['branch_id'=>(int)($r['destination_branch_id']??0)?:$r['branch_id'],'issue_code'=>'transfer_not_received','issue_group'=>'inventory','source_module'=>'inventory','severity'=>'urgent','responsible_role'=>'inventory_officer','entity_type'=>'inventory_document','entity_id'=>$r['id'],'summary'=>'Dispatched inventory transfer has not been received','detail'=>$r['document_no'].' has remained in transit for more than 24 hours.','due_at'=>date('Y-m-d H:i:s',time()+14400)],'inventory');

        if(schema_table_exists(table('clinical_usage_items'))){
            $sql='SELECT u.id,c.visit_id,u.patient_id,COALESCE(u.branch_id,c.branch_id,p.branch_id) branch_id,u.encounter_id,u.inventory_item_id,u.description,CONCAT(p.first_name," ",p.last_name) patient FROM `' . table('clinical_usage_items') . '` u JOIN `' . table('clinical_encounters') . '` c ON c.id=u.encounter_id JOIN `' . table('patients') . '` p ON p.id=u.patient_id WHERE u.fulfillment_status="confirmed" AND u.inventory_item_id IS NOT NULL AND NOT EXISTS(SELECT 1 FROM `' . table('inventory_documents') . '` d JOIN `' . table('inventory_document_lines') . '` dl ON dl.document_id=d.id WHERE d.document_type="issue" AND d.status="posted" AND d.encounter_id=u.encounter_id AND dl.item_id=u.inventory_item_id)';$args=[];
            if($branchId>0){$sql.=' AND COALESCE(u.branch_id,c.branch_id,p.branch_id)=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$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'=>'clinical_material_without_stock_movement','issue_group'=>'inventory','source_module'=>'inventory','severity'=>'urgent','responsible_role'=>'inventory_officer','entity_type'=>'clinical_usage','entity_id'=>$r['id'],'summary'=>'Confirmed treatment material has no posted stock issue','detail'=>$r['patient'].' · '.$r['description'].'. Verify the encounter material and post the matching inventory issue.','metadata'=>['encounter_id'=>(int)$r['encounter_id'],'item_id'=>(int)$r['inventory_item_id']],'due_at'=>date('Y-m-d H:i:s',time()+14400)],'inventory');
        }
    }

    // A processed refund or reversal must retain its independent approval trail.
    if(schema_table_exists(table('payment_adjustments'))){
        $sql='SELECT pa.id,pa.branch_id,pa.invoice_id,pa.payment_id,pa.adjustment_type,pa.amount,pa.reason FROM `' . table('payment_adjustments') . '` pa WHERE pa.status="processed" AND (pa.approved_by IS NULL OR pa.approved_at IS NULL)';$args=[];
        if($branchId>0){$sql.=' AND pa.branch_id=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $r)$record(['branch_id'=>$r['branch_id'],'issue_code'=>'processed_adjustment_missing_approval','issue_group'=>'billing','source_module'=>'finance','severity'=>'critical','responsible_role'=>'clinic_admin','entity_type'=>'payment_adjustment','entity_id'=>$r['id'],'summary'=>'Processed payment adjustment has no independent approval record','detail'=>reconciliation_label((string)$r['adjustment_type']).' · ₱'.number_format((float)$r['amount'],2).' · '.$r['reason'].'. Review the immutable payment and approval audit trail.','metadata'=>['invoice_id'=>(int)$r['invoice_id'],'payment_id'=>(int)$r['payment_id']],'due_at'=>date('Y-m-d H:i:s',time()+1800)],'billing');
    }

    // Pending independent approvals appear in a dedicated Approval group.
    $approvalSources=[
        ['inventory_documents','id','branch_id','requested_by','requested_at','document_no','status="submitted"','inventory_document','inventory','Inventory transaction awaiting independent approval'],
        ['discount_authorizations','id','branch_id','requested_by','requested_at','reason','status="pending"','discount_authorization','finance','Discount request awaiting independent approval'],
        ['zero_charge_authorizations','id','branch_id','requested_by','requested_at','reason','status="pending"','zero_charge_authorization','finance','No-payment request awaiting independent approval'],
        ['payment_adjustments','id','branch_id','requested_by','requested_at','reason','status="requested"','payment_adjustment','finance','Payment adjustment awaiting independent approval'],
    ];
    foreach($approvalSources as [$tableName,$idCol,$branchCol,$requesterCol,$dateCol,$labelCol,$where,$entityType,$sourceModule,$summary]){
        if(!schema_table_exists(table($tableName)))continue;
        $sql='SELECT '.$idCol.' id,'.$branchCol.' branch_id,'.$requesterCol.' requested_by,'.$dateCol.' requested_at,'.$labelCol.' label FROM `' . table($tableName) . '` WHERE '.$where;$args=[];
        if($branchId>0){$sql.=' AND '.$branchCol.'=?';$args[]=$branchId;}$sql.=' LIMIT 1000';$stmt=$pdo->prepare($sql);$stmt->execute($args);
        foreach($stmt->fetchAll() as $r)$record(['branch_id'=>$r['branch_id'],'issue_code'=>'approval_'.$tableName,'issue_group'=>'approval','source_module'=>$sourceModule,'severity'=>'approval','responsible_role'=>'clinic_admin','entity_type'=>$entityType,'entity_id'=>$r['id'],'summary'=>$summary,'detail'=>trim((string)$r['label']).' · requested '.date('M j, Y g:i A',strtotime((string)$r['requested_at'])).'.','due_at'=>date('Y-m-d H:i:s',strtotime((string)$r['requested_at'].' +1 day'))],'approval');
    }

    $known=[
        'encounter_without_queue','finalized_encounter_chair_occupied','seated_without_dentist','paused_treatment_chair_occupied','appointment_queue_status_mismatch',
        'assistant_without_dentist','assistant_entry_unverified','procedure_missing_author','paid_visit_still_at_cashier','cashier_zero_without_authorization','completed_procedure_missing_billing_line','invoice_line_total_mismatch',
        'duplicate_procedure_billing_item','followup_recommendation_missing_record','inventory_issue_missing_destination',
        'inventory_return_missing_issue','inventory_issue_for_planned_procedure','negative_branch_stock','expired_lot_still_active','transfer_not_received','clinical_material_without_stock_movement',
        'processed_adjustment_missing_approval','approval_inventory_documents','approval_discount_authorizations','approval_zero_charge_authorizations','approval_payment_adjustments',
    ];
    reconciliation_resolve_disappeared($pdo,$known,$seen,$branchId);
    return ['detected'=>$detected+(int)($visit['detected']??0),'linked'=>(int)($visit['linked']??0),'modules'=>$modules];
}

function reconciliation_assign_issue(PDO $pdo,int $issueId,int $assigneeId,int $assignedBy,string $reason): string
{
    if($issueId<1||$assigneeId<1)throw new RuntimeException('Select an exception and an assignee.');
    $reason=trim($reason);if(mb_strlen($reason)<5)throw new RuntimeException('Enter a brief assignment note.');
    $issue=visit_issue_record($pdo,$issueId,false);if(!$issue)throw new RuntimeException('Exception not found.');
    if(!in_array((string)$issue['status'],['open','in_review'],true))throw new RuntimeException('This exception is already closed.');
    $stmt=$pdo->prepare('SELECT u.id,u.name,u.branch_id,r.code role_code FROM `' . table('users') . '` u JOIN `' . table('roles') . '` r ON r.id=u.role_id WHERE u.id=? AND u.status="active" LIMIT 1');$stmt->execute([$assigneeId]);$user=$stmt->fetch();if(!$user)throw new RuntimeException('The selected staff account is not active.');
    if((int)($issue['branch_id']??0)>0&&(int)($user['branch_id']??0)>0&&(int)$issue['branch_id']!==(int)$user['branch_id'])throw new RuntimeException('The assignee belongs to another branch.');
    $responsibleRole=(string)($issue['responsible_role']??'clinic_admin');
    if(!in_array((string)$user['role_code'],[$responsibleRole,'clinic_admin'],true))throw new RuntimeException('Assign this exception to the responsible role or a Clinic Administrator.');
    $pdo->prepare('UPDATE `' . table('visit_integrity_issues') . '` SET status="in_review",assigned_to_user_id=?,assigned_by_user_id=?,assigned_at=NOW(),resolution_action="assigned",resolution_reason=? WHERE id=?')->execute([$assigneeId,$assignedBy,mb_substr($reason,0,1000),$issueId]);
    audit('reconciliation_issue_assigned','visit_integrity_issue',$issueId,['assigned_to_user_id'=>$assigneeId,'assigned_to_name'=>$user['name'],'reason'=>$reason]);
    return 'Exception assigned to '.$user['name'].'.';
}

function reconciliation_revision(PDO $pdo,int $branchId=0,?string $role=null,?int $userId=null): string
{
    $where=['status IN ("open","in_review")'];$args=[];
    if($branchId>0){$where[]='(branch_id=? OR branch_id IS NULL)';$args[]=$branchId;}
    if($role!==null&&!in_array($role,['super_admin','clinic_admin'],true)){$where[]='(responsible_role=? OR assigned_to_user_id=?)';$args[]=$role;$args[]=$userId?:0;}
    $stmt=$pdo->prepare('SELECT COUNT(*) total,COALESCE(SUM(id),0) id_sum,COALESCE(SUM(CRC32(CONCAT_WS("|",id,severity,status,COALESCE(assigned_to_user_id,0),COALESCE(due_at,""),summary))),0) fingerprint,COALESCE(MAX(COALESCE(assigned_at,detected_at)),"1000-01-01") changed FROM `' . table('visit_integrity_issues') . '` WHERE '.implode(' AND ',$where));$stmt->execute($args);$row=$stmt->fetch()?:[];
    return hash('sha256',implode('|',[(string)($row['total']??0),(string)($row['id_sum']??0),(string)($row['fingerprint']??0),(string)($row['changed']??'')]));
}
