<?php

declare(strict_types=1);

/**
 * Detects and safely repairs historical operational state that can survive
 * after the clinical or financial transaction itself is no longer active.
 *
 * This implementation is deliberately schema-aware because older upgraded
 * DentalCare databases may not contain every newer convenience column.
 */

function dental_operational_safe_expression(string $tableName, string $alias, string $column, string $fallback = 'NULL'): string
{
    return schema_column_exists($tableName, $column) ? $alias . '.`' . $column . '`' : $fallback;
}

function dental_operational_log_failure(string $context, Throwable $exception): string
{
    $reference = strtoupper(substr(hash('sha256', $context . '|' . $exception->getMessage() . '|' . microtime(true)), 0, 12));
    if (function_exists('dental_runtime_write_log')) {
        dental_runtime_write_log($context . ': ' . $exception, $reference);
    } else {
        error_log('DentalCare operational repair [' . $reference . '] ' . $context . ': ' . $exception->getMessage());
    }
    return $reference;
}

function dental_operational_state_snapshot(PDO $pdo): array
{
    $result = [
        'chairs' => [],
        'cashier_empty' => [],
        'cashier_requires_reconciliation' => [],
        'errors' => [],
        'counts' => [
            'chairs' => 0,
            'cashier_empty' => 0,
            'cashier_requires_reconciliation' => 0,
        ],
    ];

    try {
        $encounters = table('clinical_encounters');
        $queue = table('queue_entries');
        $patients = table('patients');
        if (schema_table_exists($encounters) && schema_table_exists($queue)) {
            $required = ['id', 'status', 'queue_entry_id'];
            $missing = array_values(array_filter($required, static fn(string $column): bool => !schema_column_exists($encounters, $column)));
            if (!schema_column_exists($queue, 'id') || !schema_column_exists($queue, 'status')) {
                $missing[] = 'queue_entries.id/status';
            }
            $hasChairId = schema_column_exists($queue, 'chair_id');
            $hasChairName = schema_column_exists($queue, 'chair_name');
            if ($missing) {
                $result['errors'][] = 'Chair-state inspection is unavailable because required encounter or queue fields are missing.';
            } elseif ($hasChairId || $hasChairName) {
                $chairCondition = [];
                if ($hasChairId) $chairCondition[] = 'COALESCE(q.chair_id,0)>0';
                if ($hasChairName) $chairCondition[] = 'COALESCE(q.chair_name,"")<>""';

                $patientJoin = '';
                $patientName = '"Patient record"';
                if (schema_table_exists($patients) && schema_column_exists($encounters, 'patient_id') && schema_column_exists($patients, 'id')) {
                    $patientJoin = ' LEFT JOIN `' . $patients . '` p ON p.id=e.patient_id ';
                    if (schema_column_exists($patients, 'first_name') && schema_column_exists($patients, 'last_name')) {
                        $patientName = 'TRIM(CONCAT(COALESCE(p.first_name,"")," ",COALESCE(p.last_name,"")))';
                    }
                }

                $orderCandidates = [];
                foreach (['finalized_at', 'updated_at', 'created_at'] as $column) {
                    if (schema_column_exists($encounters, $column)) $orderCandidates[] = 'e.`' . $column . '`';
                }
                $orderExpression = $orderCandidates ? 'COALESCE(' . implode(',', $orderCandidates) . ')' : 'e.id';

                $sql = 'SELECT e.id encounter_id,'
                    . dental_operational_safe_expression($encounters, 'e', 'patient_id', '0') . ' patient_id,'
                    . dental_operational_safe_expression($encounters, 'e', 'appointment_id', 'NULL') . ' encounter_appointment_id,'
                    . 'e.queue_entry_id,e.status encounter_status,'
                    . dental_operational_safe_expression($encounters, 'e', 'completion_destination', 'NULL') . ' completion_destination,'
                    . dental_operational_safe_expression($queue, 'q', 'queue_no', 'CONCAT("#",q.id)') . ' queue_no,'
                    . 'q.status queue_status,'
                    . dental_operational_safe_expression($queue, 'q', 'chair_id', 'NULL') . ' chair_id,'
                    . dental_operational_safe_expression($queue, 'q', 'chair_name', 'NULL') . ' chair_name,'
                    . dental_operational_safe_expression($queue, 'q', 'appointment_id', 'NULL') . ' queue_appointment_id,'
                    . $patientName . ' patient_name '
                    . 'FROM `' . $encounters . '` e '
                    . 'JOIN `' . $queue . '` q ON q.id=e.queue_entry_id '
                    . $patientJoin
                    . 'WHERE e.status IN ("finalized","addendum") '
                    . 'AND (' . implode(' OR ', $chairCondition) . ') '
                    . 'ORDER BY ' . $orderExpression . ' ASC,e.id ASC LIMIT 250';
                $result['chairs'] = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
                $result['counts']['chairs'] = count($result['chairs']);
            }
        }
    } catch (Throwable $exception) {
        $reference = dental_operational_log_failure('Operational chair-state inspection failed', $exception);
        $result['errors'][] = 'Chair-state inspection failed. Reference: ' . $reference . '.';
    }

    try {
        $sessions = table('cashier_sessions');
        if (schema_table_exists($sessions)) {
            if (!schema_column_exists($sessions, 'id') || !schema_column_exists($sessions, 'status') || !schema_column_exists($sessions, 'opened_at')) {
                $result['errors'][] = 'Cashier-session inspection is unavailable because required session fields are missing.';
            } else {
                $paymentCount = schema_table_exists(table('payments')) && schema_column_exists(table('payments'), 'cashier_session_id')
                    ? '(SELECT COUNT(*) FROM `' . table('payments') . '` p WHERE p.cashier_session_id=cs.id)'
                    : '0';
                $checkoutCount = schema_table_exists(table('cashier_checkouts')) && schema_column_exists(table('cashier_checkouts'), 'cashier_session_id')
                    ? '(SELECT COUNT(*) FROM `' . table('cashier_checkouts') . '` co WHERE co.cashier_session_id=cs.id)'
                    : '0';

                $users = table('users');
                $userJoin = '';
                $cashierName = '"Cashier account"';
                if (schema_table_exists($users) && schema_column_exists($sessions, 'cashier_user_id') && schema_column_exists($users, 'id')) {
                    $userJoin = ' LEFT JOIN `' . $users . '` u ON u.id=cs.cashier_user_id ';
                    if (schema_column_exists($users, 'name')) {
                        $cashierName = 'COALESCE(NULLIF(TRIM(u.name),""),"Cashier account")';
                    } elseif (schema_column_exists($users, 'first_name') && schema_column_exists($users, 'last_name')) {
                        $cashierName = 'COALESCE(NULLIF(TRIM(CONCAT(COALESCE(u.first_name,"")," ",COALESCE(u.last_name,""))),""),"Cashier account")';
                    }
                }

                $sql = 'SELECT cs.id,'
                    . dental_operational_safe_expression($sessions, 'cs', 'branch_id', 'NULL') . ' branch_id,'
                    . dental_operational_safe_expression($sessions, 'cs', 'cashier_user_id', 'NULL') . ' cashier_user_id,'
                    . dental_operational_safe_expression($sessions, 'cs', 'opening_cash', '0') . ' opening_cash,'
                    . 'cs.opened_at,'
                    . dental_operational_safe_expression($sessions, 'cs', 'window_name', 'NULL') . ' window_name,'
                    . dental_operational_safe_expression($sessions, 'cs', 'locked_patient_id', '0') . ' locked_patient_id,'
                    . dental_operational_safe_expression($sessions, 'cs', 'locked_queue_entry_id', '0') . ' locked_queue_entry_id,'
                    . $paymentCount . ' payment_count,' . $checkoutCount . ' checkout_count,'
                    . $cashierName . ' cashier_name '
                    . 'FROM `' . $sessions . '` cs '
                    . $userJoin
                    . 'WHERE cs.status="open" AND cs.opened_at < DATE_SUB(NOW(),INTERVAL 18 HOUR) '
                    . 'ORDER BY cs.opened_at ASC,cs.id ASC LIMIT 250';
                $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
                foreach ($rows as $row) {
                    $safe = (int)($row['locked_patient_id'] ?? 0) === 0
                        && (int)($row['locked_queue_entry_id'] ?? 0) === 0
                        && (int)($row['payment_count'] ?? 0) === 0
                        && (int)($row['checkout_count'] ?? 0) === 0;
                    if ($safe) {
                        $result['cashier_empty'][] = $row;
                    } else {
                        $result['cashier_requires_reconciliation'][] = $row;
                    }
                }
                $result['counts']['cashier_empty'] = count($result['cashier_empty']);
                $result['counts']['cashier_requires_reconciliation'] = count($result['cashier_requires_reconciliation']);
            }
        }
    } catch (Throwable $exception) {
        $reference = dental_operational_log_failure('Operational cashier-session inspection failed', $exception);
        $result['errors'][] = 'Cashier-session inspection failed. Reference: ' . $reference . '.';
    }

    return $result;
}

function dental_repair_safe_operational_state(PDO $pdo, int $userId, string $reason): array
{
    $reason = trim($reason);
    if (mb_strlen($reason) < 10) {
        throw new RuntimeException('Enter a reason of at least 10 characters for the audited repair.');
    }

    $result = [
        'chairs_released' => 0,
        'empty_cashier_sessions_closed' => 0,
        'cashier_sessions_requiring_reconciliation' => 0,
        'skipped' => 0,
        'errors' => [],
    ];

    $snapshot = dental_operational_state_snapshot($pdo);
    foreach ((array)($snapshot['errors'] ?? []) as $error) {
        $result['errors'][] = (string)$error;
    }

    $encounters = table('clinical_encounters');
    $queue = table('queue_entries');
    foreach ((array)($snapshot['chairs'] ?? []) as $candidate) {
        $encounterId = (int)($candidate['encounter_id'] ?? 0);
        $queueId = (int)($candidate['queue_entry_id'] ?? 0);
        if ($encounterId < 1 || $queueId < 1) {
            $result['skipped']++;
            continue;
        }

        try {
            $pdo->beginTransaction();
            $select = 'SELECT e.id encounter_id,e.status encounter_status,e.queue_entry_id,'
                . dental_operational_safe_expression($encounters, 'e', 'appointment_id', 'NULL') . ' encounter_appointment_id,'
                . dental_operational_safe_expression($encounters, 'e', 'completion_destination', 'NULL') . ' completion_destination,'
                . 'q.status queue_status,'
                . dental_operational_safe_expression($queue, 'q', 'appointment_id', 'NULL') . ' queue_appointment_id,'
                . dental_operational_safe_expression($queue, 'q', 'chair_id', 'NULL') . ' chair_id,'
                . dental_operational_safe_expression($queue, 'q', 'chair_name', 'NULL') . ' chair_name '
                . 'FROM `' . $encounters . '` e JOIN `' . $queue . '` q ON q.id=e.queue_entry_id '
                . 'WHERE e.id=? AND q.id=? FOR UPDATE';
            $stmt = $pdo->prepare($select);
            $stmt->execute([$encounterId, $queueId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
            if (!$row || !in_array((string)$row['encounter_status'], ['finalized', 'addendum'], true)) {
                $pdo->rollBack();
                $result['skipped']++;
                continue;
            }

            $hasChair = ((int)($row['chair_id'] ?? 0) > 0) || trim((string)($row['chair_name'] ?? '')) !== '';
            if (!$hasChair) {
                $pdo->rollBack();
                $result['skipped']++;
                continue;
            }

            $oldStatus = trim((string)($row['queue_status'] ?? ''));
            $destination = trim((string)($row['completion_destination'] ?? ''));
            $target = $oldStatus;
            if (!in_array($oldStatus, ['completed', 'cancelled', 'waiting_billing', 'cashier_called'], true)) {
                $target = $destination === 'no_payment' ? 'completed' : 'waiting_billing';
            }

            $sets = ['status=?'];
            $args = [$target];
            if (schema_column_exists($queue, 'chair_id')) $sets[] = 'chair_id=NULL';
            if (schema_column_exists($queue, 'chair_name')) $sets[] = 'chair_name=NULL';
            if (schema_column_exists($queue, 'seated_at')) $sets[] = 'seated_at=NULL';
            if ($target === 'waiting_billing' && schema_column_exists($queue, 'billing_at')) $sets[] = 'billing_at=COALESCE(billing_at,NOW())';
            if ($target === 'completed' && schema_column_exists($queue, 'completed_at')) $sets[] = 'completed_at=COALESCE(completed_at,NOW())';
            if (schema_column_exists($queue, 'updated_at')) $sets[] = 'updated_at=NOW()';
            $args[] = $queueId;
            $update = $pdo->prepare('UPDATE `' . $queue . '` SET ' . implode(',', $sets) . ' WHERE id=?');
            $update->execute($args);

            if ($oldStatus !== $target && schema_table_exists(table('queue_status_history'))) {
                $history = table('queue_status_history');
                $columns = ['queue_entry_id', 'previous_status', 'new_status'];
                $values = [$queueId, $oldStatus !== '' ? $oldStatus : null, $target];
                $placeholders = ['?', '?', '?'];
                if (schema_column_exists($history, 'notes')) {
                    $columns[] = 'notes'; $values[] = mb_substr($reason, 0, 255); $placeholders[] = '?';
                }
                if (schema_column_exists($history, 'changed_by')) {
                    $columns[] = 'changed_by'; $values[] = $userId > 0 ? $userId : null; $placeholders[] = '?';
                }
                if (schema_column_exists($history, 'created_at')) {
                    $columns[] = 'created_at'; $placeholders[] = 'NOW()';
                }
                $pdo->prepare('INSERT INTO `' . $history . '` (`' . implode('`,`', $columns) . '`) VALUES (' . implode(',', $placeholders) . ')')->execute($values);
            }

            $appointmentId = (int)($row['queue_appointment_id'] ?? 0);
            if ($appointmentId < 1) $appointmentId = (int)($row['encounter_appointment_id'] ?? 0);
            $appointments = table('appointments');
            if ($appointmentId > 0 && schema_table_exists($appointments)) {
                $appointmentSet = [];
                $appointmentArgs = [];
                $appointmentStatus = match ($target) {
                    'waiting_billing', 'cashier_called' => 'awaiting_billing',
                    'completed' => 'completed',
                    'cancelled' => 'cancelled',
                    default => null,
                };
                if ($appointmentStatus !== null && schema_column_exists($appointments, 'status')) {
                    $appointmentSet[] = 'status=?';
                    $appointmentArgs[] = $appointmentStatus;
                }
                if (schema_column_exists($appointments, 'chair_id')) $appointmentSet[] = 'chair_id=NULL';
                if (schema_column_exists($appointments, 'chair_name')) $appointmentSet[] = 'chair_name=NULL';
                if (schema_column_exists($appointments, 'updated_at')) $appointmentSet[] = 'updated_at=NOW()';
                if ($appointmentSet) {
                    $appointmentArgs[] = $appointmentId;
                    $pdo->prepare('UPDATE `' . $appointments . '` SET ' . implode(',', $appointmentSet) . ' WHERE id=?')->execute($appointmentArgs);
                }
            }

            if (schema_column_exists($encounters, 'chair_released_at')) {
                $pdo->prepare('UPDATE `' . $encounters . '` SET chair_released_at=COALESCE(chair_released_at,NOW()) WHERE id=?')->execute([$encounterId]);
            }

            $issues = table('visit_integrity_issues');
            if (schema_table_exists($issues)
                && schema_column_exists($issues, 'issue_code')
                && schema_column_exists($issues, 'entity_type')
                && schema_column_exists($issues, 'entity_id')
                && schema_column_exists($issues, 'status')) {
                $issueSet = ['status="resolved"'];
                $issueArgs = [];
                if (schema_column_exists($issues, 'resolved_by')) { $issueSet[] = 'resolved_by=?'; $issueArgs[] = $userId > 0 ? $userId : null; }
                if (schema_column_exists($issues, 'resolved_at')) $issueSet[] = 'resolved_at=NOW()';
                if (schema_column_exists($issues, 'resolution_action')) { $issueSet[] = 'resolution_action=?'; $issueArgs[] = 'release_chair'; }
                if (schema_column_exists($issues, 'resolution_reason')) { $issueSet[] = 'resolution_reason=?'; $issueArgs[] = mb_substr($reason, 0, 1000); }
                if (schema_column_exists($issues, 'new_values_json')) { $issueSet[] = 'new_values_json=?'; $issueArgs[] = json_encode(['status' => $target, 'chair_id' => null, 'chair_name' => null], JSON_UNESCAPED_SLASHES); }
                $issueArgs[] = $queueId;
                $pdo->prepare('UPDATE `' . $issues . '` SET ' . implode(',', $issueSet)
                    . ' WHERE issue_code="finalized_encounter_chair_occupied" AND entity_type="queue_entry" AND entity_id=? AND status IN ("open","in_review")')
                    ->execute($issueArgs);
            }

            audit_required('production_stale_chair_repaired', 'queue_entry', $queueId, [
                'encounter_id' => $encounterId,
                'old_status' => $oldStatus,
                'new_status' => $target,
                'old_chair_id' => (int)($row['chair_id'] ?? 0) ?: null,
                'old_chair_name' => (string)($row['chair_name'] ?? ''),
                'reason' => $reason,
            ]);
            $pdo->commit();
            $result['chairs_released']++;
        } catch (Throwable $exception) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            $reference = dental_operational_log_failure('Stale chair repair failed for encounter ' . $encounterId . ' / queue ' . $queueId, $exception);
            $result['errors'][] = 'Queue ' . ((string)($candidate['queue_no'] ?? ('#' . $queueId))) . ' was not changed. Reference: ' . $reference . '.';
        }
    }

    $sessions = table('cashier_sessions');
    $result['cashier_sessions_requiring_reconciliation'] = count((array)($snapshot['cashier_requires_reconciliation'] ?? []));
    foreach ((array)($snapshot['cashier_empty'] ?? []) as $candidate) {
        $sessionId = (int)($candidate['id'] ?? 0);
        if ($sessionId < 1) {
            $result['skipped']++;
            continue;
        }
        try {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare('SELECT * FROM `' . $sessions . '` WHERE id=? FOR UPDATE');
            $stmt->execute([$sessionId]);
            $session = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
            if (!$session || (string)($session['status'] ?? '') !== 'open') {
                $pdo->rollBack();
                $result['skipped']++;
                continue;
            }

            $paymentCount = 0;
            if (schema_table_exists(table('payments')) && schema_column_exists(table('payments'), 'cashier_session_id')) {
                $countStmt = $pdo->prepare('SELECT COUNT(*) FROM `' . table('payments') . '` WHERE cashier_session_id=?');
                $countStmt->execute([$sessionId]);
                $paymentCount = (int)$countStmt->fetchColumn();
            }
            $checkoutCount = 0;
            if (schema_table_exists(table('cashier_checkouts')) && schema_column_exists(table('cashier_checkouts'), 'cashier_session_id')) {
                $countStmt = $pdo->prepare('SELECT COUNT(*) FROM `' . table('cashier_checkouts') . '` WHERE cashier_session_id=?');
                $countStmt->execute([$sessionId]);
                $checkoutCount = (int)$countStmt->fetchColumn();
            }
            $lockedPatient = schema_column_exists($sessions, 'locked_patient_id') ? (int)($session['locked_patient_id'] ?? 0) : 0;
            $lockedQueue = schema_column_exists($sessions, 'locked_queue_entry_id') ? (int)($session['locked_queue_entry_id'] ?? 0) : 0;
            if ($paymentCount > 0 || $checkoutCount > 0 || $lockedPatient > 0 || $lockedQueue > 0) {
                $pdo->rollBack();
                $result['cashier_sessions_requiring_reconciliation']++;
                continue;
            }

            $openingCash = round((float)($session['opening_cash'] ?? 0), 2);
            $sets = ['status="closed"'];
            $args = [];
            if (schema_column_exists($sessions, 'closed_at')) $sets[] = 'closed_at=COALESCE(closed_at,NOW())';
            if (schema_column_exists($sessions, 'expected_cash')) { $sets[] = 'expected_cash=?'; $args[] = $openingCash; }
            if (schema_column_exists($sessions, 'counted_cash')) { $sets[] = 'counted_cash=?'; $args[] = $openingCash; }
            if (schema_column_exists($sessions, 'variance')) $sets[] = 'variance=0';
            if (schema_column_exists($sessions, 'close_notes')) { $sets[] = 'close_notes=?'; $args[] = mb_substr('Automatically closed as an abandoned empty session. ' . $reason, 0, 1000); }
            foreach (['locked_patient_id', 'locked_queue_entry_id', 'locked_invoice_id'] as $column) {
                if (schema_column_exists($sessions, $column)) $sets[] = '`' . $column . '`=NULL';
            }
            if (schema_column_exists($sessions, 'transaction_locked_at')) $sets[] = 'transaction_locked_at=NULL';
            if (schema_column_exists($sessions, 'transaction_lock_note')) $sets[] = 'transaction_lock_note=NULL';
            $args[] = $sessionId;
            $update = $pdo->prepare('UPDATE `' . $sessions . '` SET ' . implode(',', $sets) . ' WHERE id=? AND status="open"');
            $update->execute($args);
            if ($update->rowCount() !== 1) {
                throw new RuntimeException('The cashier session changed while the cleanup was running.');
            }

            audit_required('production_empty_cashier_session_closed', 'cashier_session', $sessionId, [
                'opened_at' => (string)($session['opened_at'] ?? ''),
                'opening_cash' => $openingCash,
                'payment_count' => 0,
                'checkout_count' => 0,
                'reason' => $reason,
            ]);
            $pdo->commit();
            $result['empty_cashier_sessions_closed']++;
        } catch (Throwable $exception) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            $reference = dental_operational_log_failure('Empty cashier-session repair failed for session ' . $sessionId, $exception);
            $result['errors'][] = 'Cashier session #' . $sessionId . ' was not changed. Reference: ' . $reference . '.';
        }
    }

    return $result;
}
