<?php

declare(strict_types=1);

/**
 * Clinical-to-billing integrity helpers.
 *
 * A completed procedure may be billed only from an approved treatment-plan
 * item or an active service-catalog entry. The approved amount is snapshotted
 * on the procedure so a later catalogue price change cannot rewrite history.
 */

function clinical_billing_schema_ready(): bool
{
    return schema_table_exists(table('procedures'))
        && schema_table_exists(table('service_catalog'))
        && schema_table_exists(table('invoice_items'))
        && schema_column_exists(table('service_catalog'), 'fee_status')
        && schema_column_exists(table('treatment_plan_items'), 'service_id')
        && schema_column_exists(table('procedures'), 'approved_fee_snapshot')
        && schema_column_exists(table('procedures'), 'fee_status')
        && schema_column_exists(table('procedures'), 'billing_status');
}

function clinical_billing_normalize_fee_status(?string $status, float $amount): string
{
    $status = (string)$status;
    if (!in_array($status, ['unconfigured','billable','no_charge'], true)) {
        return $amount > 0.001 ? 'billable' : 'unconfigured';
    }
    if ($status === 'billable' && $amount <= 0.001) return 'unconfigured';
    return $status;
}

function clinical_billing_service(PDO $pdo, int $serviceId, int $branchId): ?array
{
    if ($serviceId < 1) return null;
    $fields = 's.id,s.branch_id,s.name,s.standard_fee,s.is_active';
    foreach (['fee_status','no_charge_reason','fee_authorized_by','fee_authorized_at'] as $column) {
        $fields .= schema_column_exists(table('service_catalog'), $column) ? ',s.`' . $column . '`' : ',NULL `' . $column . '`';
    }
    $stmt = $pdo->prepare(
        'SELECT ' . $fields . ' FROM `' . table('service_catalog') . '` s '
        . 'WHERE s.id=? AND s.is_active=1 AND (s.branch_id=? OR s.branch_id IS NULL) '
        . 'ORDER BY CASE WHEN s.branch_id=? THEN 0 ELSE 1 END LIMIT 1'
    );
    $stmt->execute([$serviceId, $branchId, $branchId]);
    $row = $stmt->fetch();
    if (!$row) return null;
    $row['approved_fee'] = round((float)($row['standard_fee'] ?? 0), 2);
    $row['fee_status'] = clinical_billing_normalize_fee_status((string)($row['fee_status'] ?? ''), (float)$row['approved_fee']);
    $row['source_type'] = 'service_catalog';
    $row['service_id'] = (int)$row['id'];
    $row['procedure_name'] = (string)$row['name'];
    $row['procedure_code'] = null;
    $row['treatment_plan_item_id'] = null;
    return $row;
}

function clinical_billing_treatment_item(PDO $pdo, int $itemId, int $patientId, int $branchId): ?array
{
    if ($itemId < 1) return null;
    $itemFields = 'i.*,p.patient_id,p.branch_id plan_branch_id,p.status plan_status,p.title plan_title,p.dentist_user_id plan_dentist_user_id';
    $serviceJoin = '';
    $serviceFields = ',NULL service_name,NULL service_standard_fee,NULL service_fee_status,NULL service_no_charge_reason,NULL service_fee_authorized_by,NULL service_fee_authorized_at';
    if (schema_column_exists(table('treatment_plan_items'), 'service_id')) {
        $serviceJoin = ' LEFT JOIN `' . table('service_catalog') . '` s ON s.id=i.service_id ';
        $serviceFields = ',s.name service_name,s.standard_fee service_standard_fee'
            . (schema_column_exists(table('service_catalog'), 'fee_status') ? ',s.fee_status service_fee_status' : ',NULL service_fee_status')
            . (schema_column_exists(table('service_catalog'), 'no_charge_reason') ? ',s.no_charge_reason service_no_charge_reason' : ',NULL service_no_charge_reason')
            . (schema_column_exists(table('service_catalog'), 'fee_authorized_by') ? ',s.fee_authorized_by service_fee_authorized_by' : ',NULL service_fee_authorized_by')
            . (schema_column_exists(table('service_catalog'), 'fee_authorized_at') ? ',s.fee_authorized_at service_fee_authorized_at' : ',NULL service_fee_authorized_at');
    }
    $stmt = $pdo->prepare(
        'SELECT ' . $itemFields . $serviceFields . ' FROM `' . table('treatment_plan_items') . '` i '
        . 'JOIN `' . table('treatment_plans') . '` p ON p.id=i.treatment_plan_id '
        . $serviceJoin
        . 'WHERE i.id=? AND p.patient_id=? AND (p.branch_id=? OR p.branch_id IS NULL) LIMIT 1'
    );
    $stmt->execute([$itemId, $patientId, $branchId]);
    $row = $stmt->fetch();
    if (!$row) return null;

    $amount = round((float)($row['estimated_fee'] ?? 0), 2);
    $status = schema_column_exists(table('treatment_plan_items'), 'fee_status')
        ? (string)($row['fee_status'] ?? '')
        : '';
    $status = clinical_billing_normalize_fee_status($status, $amount);
    $reason = trim((string)($row['no_charge_reason'] ?? ''));
    $authorizedBy = (int)($row['fee_authorized_by'] ?? 0) ?: null;
    $authorizedAt = $row['fee_authorized_at'] ?? null;

    if ($status === 'unconfigured' && (int)($row['service_id'] ?? 0) > 0) {
        $serviceAmount = round((float)($row['service_standard_fee'] ?? 0), 2);
        $serviceStatus = clinical_billing_normalize_fee_status((string)($row['service_fee_status'] ?? ''), $serviceAmount);
        if ($serviceStatus !== 'unconfigured') {
            $status = $serviceStatus;
            $amount = $serviceStatus === 'billable' ? $serviceAmount : 0.0;
            $reason = trim((string)($row['service_no_charge_reason'] ?? ''));
            $authorizedBy = (int)($row['service_fee_authorized_by'] ?? 0) ?: null;
            $authorizedAt = $row['service_fee_authorized_at'] ?? null;
        }
    }

    $row['approved_fee'] = $amount;
    $row['fee_status'] = $status;
    $row['no_charge_reason'] = $reason;
    $row['fee_authorized_by'] = $authorizedBy;
    $row['fee_authorized_at'] = $authorizedAt;
    $row['source_type'] = 'treatment_plan';
    $row['service_id'] = (int)($row['service_id'] ?? 0) ?: null;
    $row['treatment_plan_item_id'] = (int)$row['id'];
    return $row;
}

function clinical_billing_assert_source(array $source): void
{
    $status = (string)($source['fee_status'] ?? 'unconfigured');
    $amount = round((float)($source['approved_fee'] ?? 0), 2);
    $name = trim((string)($source['procedure_name'] ?? $source['name'] ?? 'procedure'));
    if ($status === 'billable' && $amount > 0.001) return;
    if ($status === 'no_charge') {
        if (mb_strlen(trim((string)($source['no_charge_reason'] ?? ''))) < 5) {
            throw new RuntimeException($name . ' is marked no-charge but has no authorization reason. Correct the service fee setup first.');
        }
        if (empty($source['fee_authorized_by']) || empty($source['fee_authorized_at'])) {
            throw new RuntimeException($name . ' is marked no-charge but has no recorded authorizer. Correct the service fee setup first.');
        }
        return;
    }
    throw new RuntimeException('The approved fee for ' . $name . ' is not configured. An Administrator must set a positive standard fee or explicitly authorize it as no-charge before treatment is finalized.');
}

function clinical_billing_resolve_source(PDO $pdo, array $encounter, int $serviceId, int $treatmentItemId): array
{
    $patientId = (int)($encounter['patient_id'] ?? 0);
    $branchId = (int)($encounter['branch_id'] ?? 0);
    if ($treatmentItemId > 0) {
        $source = clinical_billing_treatment_item($pdo, $treatmentItemId, $patientId, $branchId);
        if (!$source) throw new RuntimeException('The selected treatment-plan item is unavailable for this patient or branch.');
        if (!in_array((string)($source['status'] ?? ''), ['accepted','scheduled','in_progress'], true)) {
            throw new RuntimeException('The treatment-plan item must be accepted, scheduled, or in progress before it can be performed.');
        }
        clinical_billing_assert_source($source);
        return $source;
    }
    $source = clinical_billing_service($pdo, $serviceId, $branchId);
    if (!$source) throw new RuntimeException('Select an active service from the clinic service catalogue.');
    clinical_billing_assert_source($source);
    return $source;
}

function clinical_billing_procedures(PDO $pdo, int $encounterId, bool $forUpdate = false): array
{
    if ($encounterId < 1) return [];
    $sql = 'SELECT p.*,du.name dentist_name,au.name assistant_name,eu.name entered_by_name,vu.name verified_by_name FROM `' . table('procedures') . '` p '
        . 'LEFT JOIN `' . table('users') . '` du ON du.id=p.dentist_user_id '
        . 'LEFT JOIN `' . table('users') . '` au ON au.id=p.assistant_user_id '
        . 'LEFT JOIN `' . table('users') . '` eu ON eu.id=p.entered_by_user_id '
        . 'LEFT JOIN `' . table('users') . '` vu ON vu.id=p.verified_by_user_id '
        . 'WHERE p.encounter_id=? AND p.status NOT IN ("cancelled") ORDER BY p.id';
    if ($forUpdate) $sql .= ' FOR UPDATE';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$encounterId]);
    return $stmt->fetchAll() ?: [];
}

function clinical_billing_performed_statuses(): array
{
    return ['completed'];
}

function clinical_billing_non_billable_statuses(): array
{
    return ['deferred','moved_follow_up','not_performed','cancelled'];
}

function clinical_billing_pending_statuses(): array
{
    return ['planned','in_progress','interrupted'];
}

function clinical_billing_is_performed(array $procedure): bool
{
    return in_array((string)($procedure['status'] ?? ''), clinical_billing_performed_statuses(), true);
}

function clinical_billing_is_excluded_from_handoff(array $procedure): bool
{
    return !clinical_billing_is_performed($procedure);
}

function clinical_billing_procedure_fee_status(array $procedure): string
{
    $procedureStatus = (string)($procedure['status'] ?? '');
    if (in_array($procedureStatus, clinical_billing_non_billable_statuses(), true)) return 'not_billable';
    if (in_array($procedureStatus, clinical_billing_pending_statuses(), true)) return 'pending_handoff';
    $amount = isset($procedure['approved_fee_snapshot']) ? (float)$procedure['approved_fee_snapshot'] : 0.0;
    $status = (string)($procedure['fee_status'] ?? '');
    if ($status === 'blocked') return 'unconfigured';
    return clinical_billing_normalize_fee_status($status, $amount);
}

function clinical_billing_handoff_procedures(array $procedures): array
{
    return array_values(array_filter($procedures, static function(array $procedure): bool {
        return clinical_billing_is_performed($procedure);
    }));
}

function clinical_billing_excluded_procedures(array $procedures): array
{
    return array_values(array_filter($procedures, static function(array $procedure): bool {
        return clinical_billing_is_excluded_from_handoff($procedure);
    }));
}

function clinical_billing_event(PDO $pdo, array $encounter, ?int $invoiceId, string $eventType, string $label, array $payload, int $userId): void
{
    if (!schema_table_exists(table('clinical_billing_handoff_events'))) return;
    $json = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRESERVE_ZERO_FRACTION);
    if ($json === false) $json = '{}';
    $pdo->prepare(
        'INSERT INTO `' . table('clinical_billing_handoff_events') . '` '
        . '(branch_id,patient_id,encounter_id,appointment_id,queue_entry_id,invoice_id,event_type,event_label,payload_json,created_by,created_at) '
        . 'VALUES (?,?,?,?,?,?,?,?,?,?,NOW())'
    )->execute([
        (int)($encounter['branch_id'] ?? 0) ?: null,
        (int)($encounter['patient_id'] ?? 0) ?: null,
        (int)($encounter['id'] ?? 0) ?: null,
        (int)($encounter['appointment_id'] ?? 0) ?: null,
        (int)($encounter['queue_entry_id'] ?? 0) ?: null,
        $invoiceId ?: null,
        $eventType,
        $label,
        $json,
        $userId ?: null,
    ]);
}

function clinical_billing_assert_procedure(array $procedure): void
{
    if (clinical_billing_is_excluded_from_handoff($procedure)) return;
    $name = trim((string)($procedure['procedure_name'] ?? 'procedure'));
    if ((int)($procedure['service_id'] ?? 0) < 1 && (int)($procedure['treatment_plan_item_id'] ?? 0) < 1) {
        throw new RuntimeException($name . ' is not linked to an approved service or treatment-plan item. Correct the billing source before finalizing.');
    }
    $status = clinical_billing_procedure_fee_status($procedure);
    $amount = round((float)($procedure['approved_fee_snapshot'] ?? 0), 2);
    if ($status === 'billable' && $amount > 0.001) return;
    if ($status === 'no_charge') {
        if (mb_strlen(trim((string)($procedure['no_charge_reason'] ?? ''))) < 5 || empty($procedure['fee_authorized_by']) || empty($procedure['fee_authorized_at'])) {
            throw new RuntimeException($name . ' does not contain a complete no-charge authorization snapshot.');
        }
        return;
    }
    throw new RuntimeException($name . ' has no approved fee. Correct the service or treatment-plan fee before finalizing the visit.');
}

function clinical_billing_insert_procedure(PDO $pdo, array $encounter, array $source, array $data, int $userId): int
{
    clinical_billing_assert_source($source);
    $responsibleDentistId = (int)($data['responsible_dentist_user_id'] ?? $encounter['dentist_user_id'] ?? $userId);
    $enteredByUserId = (int)($data['entered_by_user_id'] ?? $userId);
    $enteredByRole = trim((string)($data['entered_by_role'] ?? 'dentist')) ?: 'dentist';
    $assistantUserId = (int)($data['assistant_user_id'] ?? 0) ?: null;
    $status = (string)$source['fee_status'];
    $amount = $status === 'no_charge' ? 0.0 : round((float)$source['approved_fee'], 2);
    $name = trim((string)$source['procedure_name']);
    if ($name === '') throw new RuntimeException('The selected billing source has no procedure name.');
    $tooth = trim((string)($data['tooth_region'] ?? $source['tooth_region'] ?? $source['tooth_number'] ?? '')) ?: null;
    $startedAt = $data['started_at'] ?? date('Y-m-d H:i:s');
    $sql = 'INSERT INTO `' . table('procedures') . '` '
        . '(patient_id,branch_id,service_id,encounter_id,treatment_plan_item_id,consent_form_id,dentist_user_id,assistant_user_id,entered_by_user_id,entered_by_role,procedure_name,tooth_region,started_at,anesthetic,materials,complications,outcome,status,'
        . 'service_name_snapshot,service_code_snapshot,approved_fee_snapshot,fee_source,fee_status,no_charge_reason,fee_authorized_by,fee_authorized_at,billing_status,created_at) '
        . 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"in_progress",?,?,?,?,?,?,?, ?,"ready",NOW())';
    $pdo->prepare($sql)->execute([
        (int)$encounter['patient_id'], (int)($encounter['branch_id'] ?? 0) ?: null, (int)($source['service_id'] ?? 0) ?: null,
        (int)$encounter['id'], (int)($source['treatment_plan_item_id'] ?? 0) ?: null, null, $responsibleDentistId,
        $assistantUserId, $enteredByUserId, $enteredByRole, $name, $tooth, $startedAt,
        trim((string)($data['anesthetic'] ?? '')) ?: null, trim((string)($data['materials'] ?? '')) ?: null,
        trim((string)($data['complications'] ?? '')) ?: null, trim((string)($data['outcome'] ?? '')) ?: null,
        $name, trim((string)($source['procedure_code'] ?? '')) ?: null, $amount,
        (string)$source['source_type'], $status === 'no_charge' ? 'no_charge' : 'billable',
        trim((string)($source['no_charge_reason'] ?? '')) ?: null, (int)($source['fee_authorized_by'] ?? 0) ?: null,
        $source['fee_authorized_at'] ?? null,
    ]);
    return (int)$pdo->lastInsertId();
}

function clinical_billing_invoice_number(PDO $pdo, int $encounterId): string
{
    $base = 'INV-' . date('ymd') . '-' . str_pad((string)$encounterId, 6, '0', STR_PAD_LEFT);
    $candidate = $base;
    $suffix = 0;
    $stmt = $pdo->prepare('SELECT COUNT(*) FROM `' . table('invoices') . '` WHERE invoice_no=?');
    do {
        $stmt->execute([$candidate]);
        if ((int)$stmt->fetchColumn() === 0) return $candidate;
        $suffix++;
        $candidate = $base . '-' . str_pad((string)$suffix, 2, '0', STR_PAD_LEFT);
    } while ($suffix < 99);
    return $base . '-' . random_int(100, 999);
}

/**
 * Create or refresh the clinical invoice from all completed procedure snapshots.
 * Returns the invoice ID. A valid explicitly no-charge visit still receives a
 * zero-valued invoice line so Cashier can distinguish it from a missing fee.
 */
function clinical_billing_sync_invoice(PDO $pdo, array $encounter, array $procedures, int $userId): int
{
    $handoffProcedures = clinical_billing_handoff_procedures($procedures);
    $excludedProcedures = clinical_billing_excluded_procedures($procedures);
    if (!$handoffProcedures) {
        clinical_billing_event($pdo, $encounter, null, 'clinical_handoff_empty', 'No performed procedures were sent to Cashier', [
            'excluded_count' => count($excludedProcedures),
            'excluded_procedures' => array_map(static function(array $procedure): array {
                return [
                    'procedure_id' => (int)($procedure['id'] ?? 0),
                    'procedure_name' => (string)($procedure['procedure_name'] ?? ''),
                    'status' => (string)($procedure['status'] ?? ''),
                    'billing_status' => (string)($procedure['billing_status'] ?? ''),
                ];
            }, $excludedProcedures),
        ], $userId);
        return 0;
    }
    foreach ($handoffProcedures as $procedure) clinical_billing_assert_procedure($procedure);

    $encounterId = (int)$encounter['id'];
    $stmt = $pdo->prepare('SELECT * FROM `' . table('invoices') . '` WHERE encounter_id=? AND status NOT IN ("voided","refunded") ORDER BY id DESC LIMIT 1 FOR UPDATE');
    $stmt->execute([$encounterId]);
    $invoice = $stmt->fetch() ?: null;
    $locked = $invoice && ((float)($invoice['paid_amount'] ?? 0) > 0.001 || !empty($invoice['charge_locked_at']));
    if ($locked) {
        $check = $pdo->prepare('SELECT COUNT(*) FROM `' . table('invoice_items') . '` WHERE invoice_id=? AND procedure_id=?');
        foreach ($handoffProcedures as $procedure) {
            $check->execute([(int)$invoice['id'], (int)$procedure['id']]);
            if ((int)$check->fetchColumn() < 1) {
                throw new RuntimeException('This invoice already has payment or approval activity and cannot accept a newly completed procedure. A supervisor must resolve the account before finalization.');
            }
        }
        return (int)$invoice['id'];
    }

    if (!$invoice) {
        $invoiceNo = clinical_billing_invoice_number($pdo, $encounterId);
        $pdo->prepare(
            'INSERT INTO `' . table('invoices') . '` '
            . '(branch_id,invoice_no,patient_id,encounter_id,queue_entry_id,description,subtotal,discount_type,discount_value,discount_amount,tax_amount,amount,paid_amount,status,created_by,created_at,updated_at) '
            . 'VALUES (?,?,?,?,?,?,0,"none",0,0,0,0,0,"unpaid",?,NOW(),NOW())'
        )->execute([
            (int)($encounter['branch_id'] ?? 0) ?: null, $invoiceNo, (int)$encounter['patient_id'], $encounterId,
            (int)($encounter['queue_entry_id'] ?? 0) ?: null, 'Dentist-finalized procedures', $userId,
        ]);
        $invoiceId = (int)$pdo->lastInsertId();
    } else {
        $invoiceId = (int)$invoice['id'];
        $pdo->prepare('DELETE FROM `' . table('invoice_items') . '` WHERE invoice_id=? AND source_type="clinical_procedure"')->execute([$invoiceId]);
    }

    $insert = $pdo->prepare(
        'INSERT INTO `' . table('invoice_items') . '` '
        . '(invoice_id,service_id,treatment_plan_item_id,procedure_id,source_type,item_code,description,quantity,unit_price,gross_amount,discount_amount,net_amount,notes,source_authorized_by,source_authorized_at,source_hash,created_by,created_at) '
        . 'VALUES (?,?,?,?,"clinical_procedure",?,?,1,?,?,0,?,?,?,?,?,?,NOW())'
    );
    $total = 0.0;
    $sourceHashes = [];
    $names = [];
    foreach ($handoffProcedures as $procedure) {
        $feeStatus = clinical_billing_procedure_fee_status($procedure);
        $amount = $feeStatus === 'no_charge' ? 0.0 : round((float)$procedure['approved_fee_snapshot'], 2);
        $description = trim((string)$procedure['procedure_name'] . (!empty($procedure['tooth_region']) ? ' · ' . (string)$procedure['tooth_region'] : ''));
        $notes = $feeStatus === 'no_charge' ? 'Authorized no-charge: ' . trim((string)$procedure['no_charge_reason']) : null;
        $payload = [
            'source_type'=>'clinical_procedure','service_id'=>(int)($procedure['service_id'] ?? 0) ?: null,
            'procedure_id'=>(int)$procedure['id'],'treatment_plan_item_id'=>(int)($procedure['treatment_plan_item_id'] ?? 0) ?: null,
            'description'=>$description,'quantity'=>1.0,'unit_price'=>$amount,'fee_status'=>$feeStatus,
        ];
        $hash = hash('sha256', (string)json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
        $insert->execute([
            $invoiceId, (int)($procedure['service_id'] ?? 0) ?: null, (int)($procedure['treatment_plan_item_id'] ?? 0) ?: null,
            (int)$procedure['id'], 'PROC-' . (int)$procedure['id'], $description, $amount, $amount, $amount, $notes,
            (int)($procedure['fee_authorized_by'] ?? 0) ?: $userId, $procedure['fee_authorized_at'] ?? date('Y-m-d H:i:s'), $hash, $userId,
        ]);
        $total += $amount;
        $sourceHashes[] = $hash;
        $names[] = $description;
        if (schema_column_exists(table('procedures'), 'cashier_handoff_status') && schema_column_exists(table('procedures'), 'cashier_handoff_at')) {
            $pdo->prepare('UPDATE `' . table('procedures') . '` SET billing_status=?,cashier_handoff_status="sent_to_cashier",cashier_handoff_at=NOW() WHERE id=?')
                ->execute([$feeStatus === 'no_charge' ? 'no_charge' : 'invoiced', (int)$procedure['id']]);
        } else {
            $pdo->prepare('UPDATE `' . table('procedures') . '` SET billing_status=? WHERE id=?')
                ->execute([$feeStatus === 'no_charge' ? 'no_charge' : 'invoiced', (int)$procedure['id']]);
        }
    }
    foreach ($excludedProcedures as $procedure) {
        if ((int)($procedure['id'] ?? 0) > 0 && schema_column_exists(table('procedures'), 'billing_status')) {
            if (schema_column_exists(table('procedures'), 'cashier_handoff_status')) {
                $pdo->prepare('UPDATE `' . table('procedures') . '` SET billing_status=?,cashier_handoff_status="excluded" WHERE id=? AND billing_status NOT IN ("invoiced","no_charge")')
                    ->execute(['not_billable', (int)$procedure['id']]);
            } else {
                $pdo->prepare('UPDATE `' . table('procedures') . '` SET billing_status=? WHERE id=? AND billing_status NOT IN ("invoiced","no_charge")')
                    ->execute(['not_billable', (int)$procedure['id']]);
            }
        }
    }
    clinical_billing_event($pdo, $encounter, $invoiceId, 'clinical_handoff_finalized', 'Performed services sent to Cashier', [
        'included_count' => count($handoffProcedures),
        'excluded_count' => count($excludedProcedures),
        'included_procedures' => array_map(static function(array $procedure): array {
            return [
                'procedure_id' => (int)($procedure['id'] ?? 0),
                'procedure_name' => (string)($procedure['procedure_name'] ?? ''),
                'status' => (string)($procedure['status'] ?? ''),
                'fee_status' => (string)($procedure['fee_status'] ?? ''),
                'approved_fee_snapshot' => round((float)($procedure['approved_fee_snapshot'] ?? 0), 2),
            ];
        }, $handoffProcedures),
        'excluded_procedures' => array_map(static function(array $procedure): array {
            return [
                'procedure_id' => (int)($procedure['id'] ?? 0),
                'procedure_name' => (string)($procedure['procedure_name'] ?? ''),
                'status' => (string)($procedure['status'] ?? ''),
                'reason' => (string)($procedure['disposition_reason'] ?? $procedure['outcome'] ?? ''),
            ];
        }, $excludedProcedures),
    ], $userId);

    // Preserve any separately billable clinical usage already linked to an
    // unlocked invoice. Procedure refreshes must not silently erase material,
    // medicine, equipment, or diagnostic-service amounts.
    $lineTotals=$pdo->prepare('SELECT COALESCE(SUM(gross_amount),0) gross_total,COALESCE(GROUP_CONCAT(source_hash ORDER BY id SEPARATOR "|"),"") source_hashes FROM `' . table('invoice_items') . '` WHERE invoice_id=?');
    $lineTotals->execute([$invoiceId]);
    $invoiceLines=$lineTotals->fetch()?:[];
    $total=round((float)($invoiceLines['gross_total']??$total),2);
    $description = implode('; ', array_slice($names, 0, 5));
    if (count($names) > 5) $description .= '; +' . (count($names) - 5) . ' more';
    $invoiceHash = hash('sha256', (string)($invoiceLines['source_hashes']??implode('|',$sourceHashes)));
    $pdo->prepare(
        'UPDATE `' . table('invoices') . '` SET description=?,subtotal=?,discount_type="none",discount_value=0,discount_amount=0,tax_amount=0,amount=?,status="unpaid",charge_source_hash=?,updated_at=NOW() WHERE id=?'
    )->execute([$description ?: 'Dentist-finalized procedures', $total, $total, $invoiceHash, $invoiceId]);
    return $invoiceId;
}
