<?php

declare(strict_types=1);

/**
 * Patient-facing lifetime visit and transaction ledger.
 *
 * Every query is scoped by patient_id. Only finalized/addendum clinical
 * records are exposed; drafts and internal review notes remain staff-only.
 */

function portal_positive_page(mixed $value): int
{
    return max(1, (int) $value);
}

function portal_page_offset(int $page, int $perPage): int
{
    return max(0, ($page - 1) * $perPage);
}

function portal_page_count(int $total, int $perPage): int
{
    return max(1, (int) ceil(max(0, $total) / max(1, $perPage)));
}

function portal_pagination_url(int $patientId, string $view, string $pageKey, int $page): string
{
    return 'patient-portal.php?' . http_build_query([
        'patient_id' => $patientId,
        'view' => $view,
        $pageKey => max(1, $page),
    ]);
}

function portal_render_pagination(int $patientId, string $view, string $pageKey, int $page, int $total, int $perPage): void
{
    $pages = portal_page_count($total, $perPage);
    if ($pages <= 1) return;
    $page = min(max(1, $page), $pages);
    ?>
    <nav class="portal-pagination" aria-label="<?= e(ucfirst($view)) ?> pages">
        <span>Page <?= $page ?> of <?= $pages ?> · <?= $total ?> records</span>
        <div>
            <?php if ($page > 1): ?><a class="btn ghost" href="<?= e(portal_pagination_url($patientId, $view, $pageKey, $page - 1)) ?>">Previous</a><?php endif; ?>
            <?php if ($page < $pages): ?><a class="btn ghost" href="<?= e(portal_pagination_url($patientId, $view, $pageKey, $page + 1)) ?>">Next</a><?php endif; ?>
        </div>
    </nav>
    <?php
}

function portal_appointment_services(PDO $pdo, array $appointmentIds): array
{
    $appointmentIds = array_values(array_unique(array_filter(array_map('intval', $appointmentIds), static fn(int $id): bool => $id > 0)));
    if (!$appointmentIds || !schema_table_exists(table('appointment_services'))) return [];
    $marks = implode(',', array_fill(0, count($appointmentIds), '?'));
    $stmt = $pdo->prepare(
        'SELECT * FROM `' . table('appointment_services') . '` WHERE appointment_id IN (' . $marks . ') '
        . 'ORDER BY appointment_id,sequence_no,id'
    );
    $stmt->execute($appointmentIds);
    $grouped = [];
    foreach ($stmt->fetchAll() as $row) $grouped[(int) $row['appointment_id']][] = $row;
    return $grouped;
}

function portal_invoice_detail_maps(PDO $pdo, array $invoiceIds): array
{
    $invoiceIds = array_values(array_unique(array_filter(array_map('intval', $invoiceIds), static fn(int $id): bool => $id > 0)));
    $result = ['items'=>[], 'payments'=>[], 'returns'=>[], 'allocations'=>[]];
    if (!$invoiceIds) return $result;
    $marks = implode(',', array_fill(0, count($invoiceIds), '?'));

    if (schema_table_exists(table('invoice_items'))) {
        $stmt = $pdo->prepare('SELECT * FROM `' . table('invoice_items') . '` WHERE invoice_id IN (' . $marks . ') ORDER BY invoice_id,id');
        $stmt->execute($invoiceIds);
        foreach ($stmt->fetchAll() as $row) $result['items'][(int) $row['invoice_id']][] = $row;
    }

    $paymentIds = [];
    if (schema_table_exists(table('payments'))) {
        $stmt = $pdo->prepare('SELECT * FROM `' . table('payments') . '` WHERE invoice_id IN (' . $marks . ') ORDER BY invoice_id,created_at,id');
        $stmt->execute($invoiceIds);
        foreach ($stmt->fetchAll() as $row) {
            $result['payments'][(int) $row['invoice_id']][] = $row;
            $paymentIds[] = (int) $row['id'];
        }
    }

    if (schema_table_exists(table('payment_adjustments'))) {
        $stmt = $pdo->prepare(
            'SELECT * FROM `' . table('payment_adjustments') . '` WHERE invoice_id IN (' . $marks . ') '
            . 'AND status IN ("approved","processed") ORDER BY invoice_id,COALESCE(processed_at,approved_at,requested_at),id'
        );
        $stmt->execute($invoiceIds);
        foreach ($stmt->fetchAll() as $row) $result['returns'][(int) $row['invoice_id']][] = $row;
    }

    if ($paymentIds && schema_table_exists(table('payment_item_allocations'))) {
        $payMarks = implode(',', array_fill(0, count($paymentIds), '?'));
        $stmt = $pdo->prepare(
            'SELECT * FROM `' . table('payment_item_allocations') . '` WHERE payment_id IN (' . $payMarks . ') '
            . 'ORDER BY payment_id,sequence_no,id'
        );
        $stmt->execute($paymentIds);
        foreach ($stmt->fetchAll() as $row) $result['allocations'][(int) $row['payment_id']][] = $row;
    }

    return $result;
}

function portal_visit_ledger_base_sql(): string
{
    $visits = table('visits');
    $appointments = table('appointments');
    $encounters = table('clinical_encounters');
    $queues = table('queue_entries');
    $invoices = table('invoices');

    $visitRows = 'SELECT '
        . 'CONCAT("V",v.id) event_key,"visit" event_source,v.id visit_id,v.visit_code,v.source_type,v.state_code,v.outcome_code,v.integrity_status,'
        . 'COALESCE(v.started_at,a.start_at,e.finalized_at,e.created_at,v.created_at) event_at,'
        . 'v.appointment_id,v.queue_entry_id,v.encounter_id,v.primary_invoice_id invoice_id,'
        . 'COALESCE(NULLIF(a.service_name,""),NULLIF(q.service_name,""),"Dental visit") service_name,'
        . 'COALESCE(NULLIF(e.dentist_name,""),NULLIF(a.dentist_name,""),NULLIF(a.inhouse_provider_name,""),"Clinic care team") provider_name,'
        . 'e.assistant_name,e.status encounter_status,e.diagnosis,e.patient_advice,e.clinical_disposition,e.finalized_at,'
        . 'i.invoice_no,i.amount invoice_amount,i.paid_amount,i.status invoice_status,a.status appointment_status,a.start_at appointment_at,q.queue_no '
        . 'FROM `' . $visits . '` v '
        . 'LEFT JOIN `' . $appointments . '` a ON a.id=v.appointment_id '
        . 'LEFT JOIN `' . $encounters . '` e ON e.id=v.encounter_id AND e.status IN ("finalized","addendum") '
        . 'LEFT JOIN `' . $queues . '` q ON q.id=v.queue_entry_id '
        . 'LEFT JOIN `' . $invoices . '` i ON i.id=v.primary_invoice_id '
        . 'WHERE v.patient_id=?';

    $legacyAppointments = 'SELECT '
        . 'CONCAT("A",a.id) event_key,"legacy_appointment" event_source,NULL visit_id,CONCAT("APPT-",a.id) visit_code,"appointment" source_type,a.status state_code,NULL outcome_code,"review" integrity_status,'
        . 'a.start_at event_at,a.id appointment_id,e.queue_entry_id,e.id encounter_id,i.id invoice_id,a.service_name,'
        . 'COALESCE(NULLIF(e.dentist_name,""),NULLIF(a.dentist_name,""),NULLIF(a.inhouse_provider_name,""),"Clinic care team") provider_name,'
        . 'e.assistant_name,e.status encounter_status,e.diagnosis,e.patient_advice,e.clinical_disposition,e.finalized_at,'
        . 'i.invoice_no,i.amount invoice_amount,i.paid_amount,i.status invoice_status,a.status appointment_status,a.start_at appointment_at,q.queue_no '
        . 'FROM `' . $appointments . '` a '
        . 'LEFT JOIN `' . $visits . '` v ON v.appointment_id=a.id '
        . 'LEFT JOIN `' . $encounters . '` e ON e.id=(SELECT MAX(e2.id) FROM `' . $encounters . '` e2 WHERE e2.patient_id=a.patient_id AND e2.appointment_id=a.id AND e2.status IN ("finalized","addendum")) '
        . 'LEFT JOIN `' . $invoices . '` i ON i.id=(SELECT MAX(i2.id) FROM `' . $invoices . '` i2 WHERE i2.patient_id=a.patient_id AND (i2.encounter_id=e.id OR i2.visit_id=a.visit_id)) '
        . 'LEFT JOIN `' . $queues . '` q ON q.id=e.queue_entry_id '
        . 'WHERE a.patient_id=? AND v.id IS NULL';

    $legacyWalkins = 'SELECT '
        . 'CONCAT("E",e.id) event_key,"legacy_encounter" event_source,NULL visit_id,CONCAT("ENC-",e.id) visit_code,"walk_in" source_type,e.status state_code,e.clinical_disposition outcome_code,"review" integrity_status,'
        . 'COALESCE(e.finalized_at,e.created_at) event_at,NULL appointment_id,e.queue_entry_id,e.id encounter_id,i.id invoice_id,'
        . 'COALESCE(NULLIF(q.service_name,""),"Walk-in dental visit") service_name,COALESCE(NULLIF(e.dentist_name,""),"Clinic care team") provider_name,'
        . 'e.assistant_name,e.status encounter_status,e.diagnosis,e.patient_advice,e.clinical_disposition,e.finalized_at,'
        . 'i.invoice_no,i.amount invoice_amount,i.paid_amount,i.status invoice_status,NULL appointment_status,NULL appointment_at,q.queue_no '
        . 'FROM `' . $encounters . '` e '
        . 'LEFT JOIN `' . $visits . '` v ON v.encounter_id=e.id '
        . 'LEFT JOIN `' . $queues . '` q ON q.id=e.queue_entry_id '
        . 'LEFT JOIN `' . $invoices . '` i ON i.id=(SELECT MAX(i2.id) FROM `' . $invoices . '` i2 WHERE i2.patient_id=e.patient_id AND i2.encounter_id=e.id) '
        . 'WHERE e.patient_id=? AND e.appointment_id IS NULL AND e.status IN ("finalized","addendum") AND v.id IS NULL';

    $standaloneInvoices = 'SELECT '
        . 'CONCAT("I",i.id) event_key,"financial_only" event_source,NULL visit_id,CONCAT("INV-",i.invoice_no) visit_code,"walk_in" source_type,i.status state_code,NULL outcome_code,"review" integrity_status,'
        . 'i.created_at event_at,NULL appointment_id,NULL queue_entry_id,NULL encounter_id,i.id invoice_id,i.description service_name,"Clinic finance" provider_name,'
        . 'NULL assistant_name,NULL encounter_status,NULL diagnosis,NULL patient_advice,NULL clinical_disposition,NULL finalized_at,'
        . 'i.invoice_no,i.amount invoice_amount,i.paid_amount,i.status invoice_status,NULL appointment_status,NULL appointment_at,NULL queue_no '
        . 'FROM `' . $invoices . '` i '
        . 'LEFT JOIN `' . $visits . '` v ON v.primary_invoice_id=i.id '
        . 'WHERE i.patient_id=? AND i.encounter_id IS NULL AND i.queue_entry_id IS NULL AND i.status<>"draft" AND v.id IS NULL';

    return $visitRows . ' UNION ALL ' . $legacyAppointments . ' UNION ALL ' . $legacyWalkins . ' UNION ALL ' . $standaloneInvoices;
}

function portal_visit_ledger(PDO $pdo, int $patientId, int $page, int $perPage): array
{
    if (!schema_table_exists(table('visits'))) return ['total'=>0,'rows'=>[],'details'=>[]];
    $base = portal_visit_ledger_base_sql();
    $args = [$patientId,$patientId,$patientId,$patientId];
    $count = $pdo->prepare('SELECT COUNT(*) FROM (' . $base . ') portal_ledger');
    $count->execute($args);
    $total = (int) $count->fetchColumn();
    $pages = portal_page_count($total, $perPage);
    $page = min(max(1, $page), $pages);
    $offset = portal_page_offset($page, $perPage);
    $stmt = $pdo->prepare('SELECT * FROM (' . $base . ') portal_ledger ORDER BY event_at DESC,event_key DESC LIMIT ' . (int)$perPage . ' OFFSET ' . (int)$offset);
    $stmt->execute($args);
    $rows = $stmt->fetchAll();

    $appointmentIds = array_column($rows, 'appointment_id');
    $encounterIds = array_values(array_unique(array_filter(array_map('intval', array_column($rows, 'encounter_id')))));
    $invoiceIds = array_column($rows, 'invoice_id');
    $details = [
        'appointment_services' => portal_appointment_services($pdo, $appointmentIds),
        'procedures' => [],
        'prescriptions' => [],
        'followups' => [],
        'invoice' => portal_invoice_detail_maps($pdo, $invoiceIds),
    ];

    if ($encounterIds) {
        $marks = implode(',', array_fill(0, count($encounterIds), '?'));
        if (schema_table_exists(table('procedures'))) {
            $stmt = $pdo->prepare(
                'SELECT * FROM `' . table('procedures') . '` WHERE encounter_id IN (' . $marks . ') '
                . 'AND status="completed" ORDER BY encounter_id,COALESCE(ended_at,created_at),id'
            );
            $stmt->execute($encounterIds);
            foreach ($stmt->fetchAll() as $row) $details['procedures'][(int) $row['encounter_id']][] = $row;
        }
        if (schema_table_exists(table('prescriptions')) && schema_table_exists(table('prescription_items'))) {
            $stmt = $pdo->prepare(
                'SELECT r.encounter_id,r.id prescription_id,r.prescription_no,r.issued_at,r.status,i.* '
                . 'FROM `' . table('prescriptions') . '` r LEFT JOIN `' . table('prescription_items') . '` i ON i.prescription_id=r.id '
                . 'WHERE r.encounter_id IN (' . $marks . ') AND r.status="issued" ORDER BY r.encounter_id,r.issued_at,r.id,i.id'
            );
            $stmt->execute($encounterIds);
            foreach ($stmt->fetchAll() as $row) $details['prescriptions'][(int) $row['encounter_id']][] = $row;
        }
        if (schema_table_exists(table('follow_ups'))) {
            $stmt = $pdo->prepare('SELECT * FROM `' . table('follow_ups') . '` WHERE encounter_id IN (' . $marks . ') ORDER BY encounter_id,recommended_date,id');
            $stmt->execute($encounterIds);
            foreach ($stmt->fetchAll() as $row) $details['followups'][(int) $row['encounter_id']][] = $row;
        }
    }

    return ['total'=>$total,'page'=>$page,'per_page'=>$perPage,'rows'=>$rows,'details'=>$details];
}
