<?php

declare(strict_types=1);

/**
 * DentalCare role-satisfaction and task-report helpers.
 * These helpers intentionally use defensive schema checks so the role reports
 * stay readable on upgraded copies that are still catching up through the
 * skipped-update guard.
 */

function dental_role_count(PDO $pdo, string $sql, array $args = []): int
{
    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($args);
        return (int) $stmt->fetchColumn();
    } catch (Throwable $exception) {
        error_log('Role satisfaction count fallback: ' . $exception->getMessage());
        return 0;
    }
}

function dental_role_sum(PDO $pdo, string $sql, array $args = []): float
{
    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($args);
        return (float) $stmt->fetchColumn();
    } catch (Throwable $exception) {
        error_log('Role satisfaction sum fallback: ' . $exception->getMessage());
        return 0.0;
    }
}

function dental_role_rows(PDO $pdo, string $sql, array $args = [], int $limit = 50): array
{
    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($args);
        return array_slice($stmt->fetchAll(), 0, max(1, $limit));
    } catch (Throwable $exception) {
        error_log('Role satisfaction rows fallback: ' . $exception->getMessage());
        return [];
    }
}

function dental_role_branch_clause(string $alias = '', int $branchId = 0, string $column = 'branch_id'): array
{
    $prefix = $alias !== '' ? $alias . '.' : '';
    if ($branchId > 0) return [' AND ' . $prefix . '`' . $column . '`=?', [$branchId]];
    if (superuser_primary_workspace()) return ['', []];
    return [' AND 1=0', []];
}

function dental_role_satisfaction_permissions(): array
{
    return [
        'super_admin' => [
            'label' => 'Superuser',
            'must_do' => ['System setup', 'Skipped-update recovery', 'Security/session control', 'Backup and release readiness', 'Role-permission audit'],
            'reports' => ['System health', 'Security event log', 'Update chain', 'Release readiness', 'Permission matrix'],
            'must_not_do' => ['Sign as dentist', 'Diagnose patients'],
        ],
        'clinic_admin' => [
            'label' => 'Administrator',
            'must_do' => ['Daily operations closeout', 'Provider/chair assignment', 'Staff and schedule control', 'Cashier exception review', 'Inventory and clinical-completion oversight'],
            'reports' => ['Daily closeout', 'Owner dashboard', 'Provider production', 'Balance aging', 'Cashier exceptions', 'Clinical incomplete records'],
            'must_not_do' => ['Finalize dentist clinical notes as the doctor', 'Take routine cashier payments as a shortcut'],
        ],
        'receptionist' => [
            'label' => 'Receptionist',
            'must_do' => ['Patient registration', 'Appointment requests', 'Schedule changes', 'Check-in/walk-in intake', 'Queue front-desk flow', 'Follow-up booking'],
            'reports' => ['Front-desk daily report', 'Expected arrivals', 'Unconfirmed/late appointments', 'Online request aging', 'Waitlist and follow-up booking work'],
            'must_not_do' => ['Clinical diagnosis', 'Prescription', 'Payment posting', 'Financial adjustment'],
        ],
        'dentist' => [
            'label' => 'Dentist',
            'must_do' => ['Assess and treat assigned patients', 'Finalize encounters', 'Treatment plans', 'Prescriptions', 'Follow-up orders', 'Clinical media review'],
            'reports' => ['My clinical completion', 'Unsigned/incomplete records', 'Procedure activity', 'Follow-ups due', 'X-ray/document review pending'],
            'must_not_do' => ['Cash drawer reconciliation', 'Inventory purchasing approval', 'Override unrelated provider records'],
        ],
        'dental_assistant' => [
            'label' => 'Dental Assistant',
            'must_do' => ['Chair preparation', 'Screening/vitals', 'Clinical draft support', 'Document/X-ray upload', 'Sterilization handoff', 'Instrument pack return'],
            'reports' => ['Assistant worklog', 'Chairside queue', 'Vitals/screenings recorded', 'Uploads made', 'Sterilization handoff', 'Unreturned instrument packs'],
            'must_not_do' => ['Diagnose', 'Prescribe', 'Finalize clinical record', 'Take payment', 'Create suppliers or inventory catalogue items'],
        ],
        'cashier' => [
            'label' => 'Cashier',
            'must_do' => ['Open cashier window', 'Search payor', 'View payables', 'Receive payment', 'Print invoice/receipt/statement', 'Close drawer session'],
            'reports' => ['My session report inside Billing', 'My payment summary', 'My receipt/reprint log', 'My pending void/refund requests'],
            'must_not_do' => ['Open patient management', 'Edit appointments', 'Open clinical records', 'Use Reports module', 'Use Insurance/HMO'],
        ],
        'inventory_officer' => [
            'label' => 'Inventory Officer',
            'must_do' => ['Stock movement', 'Lots and expiry', 'Receiving', 'Stock count', 'Quarantine', 'Sterilization records'],
            'reports' => ['Low stock', 'Expiry', 'Quarantine', 'Receiving and stock movement', 'Sterilization exceptions', 'Supply usage'],
            'must_not_do' => ['Clinical diagnosis', 'Patient payment', 'User management'],
        ],
        'staff' => [
            'label' => 'General Staff',
            'must_do' => ['Patient registration', 'Appointment and walk-in intake', 'Queue coordination', 'Patient communications', 'Open own cashier session', 'Receive payment', 'Issue receipts', 'Complete checkout', 'Reconcile own drawer'],
            'reports' => ['Front-desk daily report', 'Expected arrivals', 'Queue work', 'Own payment summary', 'Own receipt/reprint log', 'Own cashier sessions'],
            'must_not_do' => ['Clinical diagnosis or finalization', 'Approve discounts/refunds/waivers', 'Inventory adjustment', 'System setup'],
        ],
    ];
}

function dental_role_satisfaction_repair(PDO $pdo): void
{
    if (!schema_table_exists(table('permissions')) || !schema_table_exists(table('roles')) || !schema_table_exists(table('role_permissions'))) return;

    $permissions = [
        ['reports.frontdesk', 'View front-desk operational reports', 'Reports'],
        ['reports.assistant', 'View dental assistant worklog reports', 'Reports'],
        ['reports.clinical', 'View dentist clinical completion reports', 'Reports'],
        ['reports.inventory', 'View inventory and sterilization reports', 'Reports'],
        ['appointment.service_disposition', 'Defer, move, or mark appointment services not performed', 'Patient Care'],
        ['cashier.statement.print', 'Print cashier invoices, receipts, and statements', 'Finance'],
        ['cashier.session.manage', 'Open and close own cashier sessions', 'Finance'],
        ['records.print', 'Print and export authorized records', 'Reports'],
    ];
    $insert = $pdo->prepare('INSERT INTO `' . table('permissions') . '` (code,name,module_group,description) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE name=VALUES(name),module_group=VALUES(module_group),description=VALUES(description)');
    foreach ($permissions as [$code, $name, $group]) $insert->execute([$code, $name, $group, $name]);

    $roleIds = [];
    foreach ($pdo->query('SELECT id,code FROM `' . table('roles') . '`') as $row) $roleIds[(string) $row['code']] = (int) $row['id'];
    $permIds = [];
    foreach ($pdo->query('SELECT id,code FROM `' . table('permissions') . '`') as $row) $permIds[(string) $row['code']] = (int) $row['id'];

    $grant = $pdo->prepare('INSERT INTO `' . table('role_permissions') . '` (role_id,permission_id,allowed) VALUES (?,?,1) ON DUPLICATE KEY UPDATE allowed=1');
    $revoke = $pdo->prepare('UPDATE `' . table('role_permissions') . '` SET allowed=0 WHERE role_id=? AND permission_id=?');

    $grants = [
        'receptionist' => ['reports.view', 'reports.frontdesk', 'appointment.service_disposition'],
        'dentist' => ['reports.view', 'reports.clinical', 'appointment.service_disposition'],
        'dental_assistant' => ['reports.view', 'reports.assistant', 'appointment.service_disposition'],
        'inventory_officer' => ['reports.view', 'reports.inventory'],
        'cashier' => ['billing.manage', 'cashier.statement.print', 'cashier.session.manage'],
        'staff' => ['patients.view', 'patients.manage', 'appointments.manage', 'queue.manage', 'communications.manage', 'reports.view', 'reports.frontdesk', 'records.print', 'appointment.service_disposition', 'billing.manage', 'cashier.statement.print', 'cashier.session.manage'],
        'clinic_admin' => ['reports.view', 'reports.frontdesk', 'reports.assistant', 'reports.clinical', 'reports.inventory', 'appointment.service_disposition', 'cashier.statement.print', 'cashier.session.manage'],
    ];
    foreach ($grants as $role => $codes) {
        if (!isset($roleIds[$role])) continue;
        foreach ($codes as $code) if (isset($permIds[$code])) $grant->execute([$roleIds[$role], $permIds[$code]]);
    }

    // Cashier is deliberately payment-only. Routine receipt/invoice/statement prints are now
    // authorized through billing/cashier permissions, not general records.print or reports.view.
    if (isset($roleIds['cashier'])) {
        foreach (['patients.view', 'patients.manage', 'appointments.manage', 'queue.manage', 'reports.view', 'records.print', 'insurance.manage', 'clinical.view', 'clinical.manage', 'inventory.manage', 'sterilization.manage', 'communications.manage'] as $code) {
            if (isset($permIds[$code])) $revoke->execute([$roleIds['cashier'], $permIds[$code]]);
        }
    }

    // Dental Assistants use materials inside the assigned encounter and may
    // manage sterilization records, but supplier/catalog maintenance belongs
    // to Inventory or Administration.
    if (isset($roleIds['dental_assistant'])) {
        foreach (['inventory.catalog','inventory.manage'] as $code) {
            if (isset($permIds[$code])) $revoke->execute([$roleIds['dental_assistant'], $permIds[$code]]);
        }
    }

    if (schema_table_exists(table('role_satisfaction_audits'))) {
        $summary = json_encode(dental_role_satisfaction_permissions(), JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
        $pdo->prepare('INSERT INTO `' . table('role_satisfaction_audits') . '` (status,summary_json,reviewed_by,reviewed_at) VALUES ("repaired",?,?,NOW())')
            ->execute([$summary ?: '{}', (int) (current_user()['id'] ?? 0)]);
    }
}

function dental_admin_daily_closeout(PDO $pdo, int $branchId): array
{
    [$apptBranchSql, $apptArgs] = dental_role_branch_clause('a', $branchId);
    [$qBranchSql, $qArgs] = dental_role_branch_clause('q', $branchId);
    [$plainBranchSql, $plainArgs] = dental_role_branch_clause('', $branchId);

    return [
        'appointments_today' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointments') . '` a WHERE DATE(a.start_at)=CURDATE()' . $apptBranchSql, $apptArgs),
        'checked_in_today' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('queue_entries') . '` q WHERE DATE(q.checked_in_at)=CURDATE()' . $qBranchSql, $qArgs),
        'patients_unfinished' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('queue_entries') . '` q WHERE DATE(q.checked_in_at)=CURDATE() AND q.status NOT IN ("completed","cancelled","paid")' . $qBranchSql, $qArgs),
        'online_requests_open' => schema_table_exists(table('appointment_requests')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointment_requests') . '` WHERE status IN ("submitted","reviewing","needs_verification")' . $plainBranchSql, $plainArgs) : 0,
        'provider_assignment_pending' => schema_table_exists(table('appointment_assignment_queue')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointment_assignment_queue') . '` WHERE status IN ("pending","assignment_pending")' . $plainBranchSql, $plainArgs) : 0,
        'schedule_conflicts' => schema_table_exists(table('appointment_safety_events')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointment_safety_events') . '` WHERE DATE(created_at)=CURDATE()' . $plainBranchSql, $plainArgs) : 0,
        'cashier_sessions_pending' => schema_table_exists(table('cashier_sessions')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('cashier_sessions') . '` WHERE status="closed_pending_review"' . $plainBranchSql, $plainArgs) : 0,
        'clinical_records_need_review' => schema_table_exists(table('clinical_encounters')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('clinical_encounters') . '` WHERE status IN ("draft","pending_review")' . $plainBranchSql, $plainArgs) : 0,
        'low_stock_items' => schema_table_exists(table('inventory_branch_stocks')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('inventory_branch_stocks') . '` bs JOIN `' . table('inventory_items') . '` i ON i.id=bs.item_id WHERE i.status="active" AND bs.current_stock<=bs.reorder_level' . ($branchId > 0 ? ' AND bs.branch_id=?' : (superuser_primary_workspace() ? '' : ' AND 1=0')), $branchId > 0 ? [$branchId] : []) : 0,
    ];
}

function dental_frontdesk_report(PDO $pdo, int $branchId): array
{
    [$apptBranchSql, $apptArgs] = dental_role_branch_clause('a', $branchId);
    [$qBranchSql, $qArgs] = dental_role_branch_clause('q', $branchId);
    [$reqBranchSql, $reqArgs] = dental_role_branch_clause('', $branchId);

    $expectedSql = 'SELECT a.id,a.start_at,a.status,a.service_name,CONCAT(p.first_name," ",p.last_name) patient,p.patient_no,p.mobile FROM `' . table('appointments') . '` a JOIN `' . table('patients') . '` p ON p.id=a.patient_id WHERE DATE(a.start_at)=CURDATE()' . $apptBranchSql . ' ORDER BY a.start_at LIMIT 40';
    $requestRows = schema_table_exists(table('appointment_requests')) ? dental_role_rows($pdo, 'SELECT public_reference,created_at,service_name,status,CONCAT(first_name," ",last_name) patient,mobile FROM `' . table('appointment_requests') . '` WHERE status IN ("submitted","reviewing","needs_verification")' . $reqBranchSql . ' ORDER BY created_at LIMIT 25', $reqArgs, 25) : [];
    return [
        'expected_arrivals' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointments') . '` a WHERE DATE(a.start_at)=CURDATE() AND a.status NOT IN ("cancelled","no_show")' . $apptBranchSql, $apptArgs),
        'checked_in' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('queue_entries') . '` q WHERE DATE(q.checked_in_at)=CURDATE()' . $qBranchSql, $qArgs),
        'late_or_due' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointments') . '` a WHERE a.start_at<NOW() AND DATE(a.start_at)=CURDATE() AND a.status IN ("confirmed","scheduled","pending")' . $apptBranchSql, $apptArgs),
        'unconfirmed' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointments') . '` a WHERE DATE(a.start_at) BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 3 DAY) AND a.confirmation_status NOT IN ("confirmed","patient_confirmed")' . $apptBranchSql, $apptArgs),
        'open_requests' => schema_table_exists(table('appointment_requests')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointment_requests') . '` WHERE status IN ("submitted","reviewing","needs_verification")' . $reqBranchSql, $reqArgs) : 0,
        'aged_requests' => schema_table_exists(table('appointment_requests')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointment_requests') . '` WHERE status IN ("submitted","reviewing","needs_verification") AND created_at<DATE_SUB(NOW(),INTERVAL 24 HOUR)' . $reqBranchSql, $reqArgs) : 0,
        'followups_need_booking' => schema_table_exists(table('follow_ups')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('follow_ups') . '` WHERE status="pending" AND appointment_id IS NULL AND recommended_date<=DATE_ADD(CURDATE(),INTERVAL 14 DAY)' . $reqBranchSql, $reqArgs) : 0,
        'waitlist_waiting' => schema_table_exists(table('appointment_waitlist')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('appointment_waitlist') . '` WHERE status="waiting"' . $reqBranchSql, $reqArgs) : 0,
        'expected_rows' => dental_role_rows($pdo, $expectedSql, $apptArgs, 40),
        'request_rows' => $requestRows,
    ];
}

function dental_assistant_worklog(PDO $pdo, int $branchId, int $userId): array
{
    [$qBranchSql, $qArgs] = dental_role_branch_clause('q', $branchId);
    [$encBranchSql, $encArgs] = dental_role_branch_clause('c', $branchId);
    [$docBranchSql, $docArgs] = dental_role_branch_clause('d', $branchId);
    return [
        'chairside_patients' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('queue_entries') . '` q WHERE DATE(q.checked_in_at)=CURDATE() AND q.status IN ("seated","in_treatment","treatment","called")' . $qBranchSql, $qArgs),
        'vitals_recorded_today' => schema_table_exists(table('vital_signs')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('vital_signs') . '` WHERE DATE(recorded_at)=CURDATE() AND recorded_by=?', [$userId]) : 0,
        'drafts_assisted' => schema_table_exists(table('clinical_encounters')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('clinical_encounters') . '` c WHERE DATE(c.created_at)=CURDATE() AND c.assistant_user_id=?' . $encBranchSql, array_merge([$userId], $encArgs)) : 0,
        'uploads_today' => schema_table_exists(table('documents')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('documents') . '` d WHERE DATE(d.created_at)=CURDATE() AND d.uploaded_by=?' . $docBranchSql, array_merge([$userId], $docArgs)) : 0,
        'unreturned_instruments' => schema_table_exists(table('instrument_usage')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('instrument_usage') . '` WHERE assigned_by=? AND returned_at IS NULL', [$userId]) : 0,
        'queue_rows' => dental_role_rows($pdo, 'SELECT q.queue_no,q.status,q.service_name,q.checked_in_at,q.chair_name,CONCAT(p.first_name," ",p.last_name) patient,p.patient_no FROM `' . table('queue_entries') . '` q JOIN `' . table('patients') . '` p ON p.id=q.patient_id WHERE DATE(q.checked_in_at)=CURDATE() AND q.status IN ("called","seated","in_treatment","treatment","billing")' . $qBranchSql . ' ORDER BY q.checked_in_at DESC LIMIT 30', $qArgs, 30),
    ];
}

function dental_dentist_completion_report(PDO $pdo, int $dentistId): array
{
    return [
        'draft_records' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('clinical_encounters') . '` WHERE dentist_user_id=? AND status IN ("draft","pending_review")', [$dentistId]),
        'finalized_30' => dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('clinical_encounters') . '` WHERE dentist_user_id=? AND status IN ("finalized","addendum") AND COALESCE(finalized_at,created_at)>=DATE_SUB(NOW(),INTERVAL 30 DAY)', [$dentistId]),
        'completion_needs_review' => schema_table_exists(table('clinical_record_completion_reviews')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('clinical_record_completion_reviews') . '` r JOIN `' . table('clinical_encounters') . '` c ON c.id=r.encounter_id WHERE c.dentist_user_id=? AND r.review_status IN ("missing","needs_review")', [$dentistId]) : 0,
        'documents_pending_review' => schema_table_exists(table('documents')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('documents') . '` d JOIN `' . table('clinical_encounters') . '` c ON c.id=d.encounter_id WHERE c.dentist_user_id=? AND d.reviewed_at IS NULL', [$dentistId]) : 0,
        'followups_due' => schema_table_exists(table('follow_ups')) ? dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('follow_ups') . '` WHERE dentist_user_id=? AND status IN ("pending","scheduled") AND recommended_date<=DATE_ADD(CURDATE(),INTERVAL 14 DAY)', [$dentistId]) : 0,
    ];
}

function dental_cashier_my_report(PDO $pdo, int $branchId, int $cashierId): array
{
    $sessionRows = [];
    if (schema_table_exists(table('cashier_sessions'))
        && schema_column_exists(table('cashier_sessions'), 'cashier_user_id')
        && schema_column_exists(table('cashier_sessions'), 'opened_at')) {
        $sessionRows = dental_role_rows($pdo, 'SELECT * FROM `' . table('cashier_sessions') . '` WHERE branch_id=? AND cashier_user_id=? ORDER BY opened_at DESC LIMIT 8', [$branchId, $cashierId], 8);
    }
    $paymentsToday = 0;
    $amountToday = 0.0;
    if (schema_table_exists(table('payments'))
        && schema_table_exists(table('invoices'))
        && schema_column_exists(table('payments'), 'received_by')
        && schema_column_exists(table('payments'), 'created_at')) {
        $paymentsToday = dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('payments') . '` py JOIN `' . table('invoices') . '` i ON i.id=py.invoice_id WHERE i.branch_id=? AND py.received_by=? AND DATE(py.created_at)=CURDATE()', [$branchId, $cashierId]);
        $amountToday = dental_role_sum($pdo, 'SELECT COALESCE(SUM(py.amount),0) FROM `' . table('payments') . '` py JOIN `' . table('invoices') . '` i ON i.id=py.invoice_id WHERE i.branch_id=? AND py.received_by=? AND DATE(py.created_at)=CURDATE()', [$branchId, $cashierId]);
    }
    $pendingReturns = 0;
    if (schema_table_exists(table('payment_adjustments'))
        && schema_table_exists(table('invoices'))
        && schema_column_exists(table('payment_adjustments'), 'requested_by')
        && schema_column_exists(table('payment_adjustments'), 'status')) {
        $pendingReturns = dental_role_count($pdo, 'SELECT COUNT(*) FROM `' . table('payment_adjustments') . '` pa JOIN `' . table('invoices') . '` i ON i.id=pa.invoice_id WHERE i.branch_id=? AND pa.requested_by=? AND pa.status IN ("requested","approved")', [$branchId, $cashierId]);
    }
    return [
        'payments_today' => $paymentsToday,
        'amount_today' => $amountToday,
        'pending_returns' => $pendingReturns,
        'session_rows' => $sessionRows,
    ];
}
