<?php

declare(strict_types=1);

/**
 * Superuser-controlled demo login seeder for client orientation.
 * Creates fictional role accounts only; it never creates a demo Superuser.
 */
final class DemoAccountService
{
    public static function accountTemplates(): array
    {
        return [
            ['role'=>'clinic_admin','employee_no'=>'DEMO-ADM-001','name'=>'Demo Administrator','first_name'=>'Demo','middle_name'=>null,'last_name'=>'Administrator','email'=>'demo.admin@mborrega-dentalcare.local','description'=>'Clinic manager demo account for services, schedules, staff, and reports.'],
            ['role'=>'receptionist','employee_no'=>'DEMO-REC-001','name'=>'Demo Receptionist','first_name'=>'Demo','middle_name'=>null,'last_name'=>'Receptionist','email'=>'demo.reception@mborrega-dentalcare.local','description'=>'Front desk demo account for appointment intake, queue, check-in, and follow-ups.'],
            ['role'=>'dentist','employee_no'=>'DEMO-DEN-001','name'=>'Dr. Demo Santos','first_name'=>'Demo','middle_name'=>null,'last_name'=>'Santos','email'=>'demo.dentist@mborrega-dentalcare.local','description'=>'Dentist demo account with an active fictional Doctor Profile for treatment workflow testing.'],
            ['role'=>'dental_assistant','employee_no'=>'DEMO-DA-001','name'=>'Demo Dental Assistant','first_name'=>'Demo','middle_name'=>null,'last_name'=>'Assistant','email'=>'demo.assistant@mborrega-dentalcare.local','description'=>'Dental assistant demo account for vitals, chair support, sterilization, and clinical assistance.'],
            ['role'=>'cashier','employee_no'=>'DEMO-CAS-001','name'=>'Demo Cashier','first_name'=>'Demo','middle_name'=>null,'last_name'=>'Cashier','email'=>'demo.cashier@mborrega-dentalcare.local','description'=>'Cashier demo account for POS payment, digital reference-code testing, and receipt workflow.'],
            ['role'=>'inventory_officer','employee_no'=>'DEMO-INV-001','name'=>'Demo Inventory Officer','first_name'=>'Demo','middle_name'=>null,'last_name'=>'Inventory','email'=>'demo.inventory@mborrega-dentalcare.local','description'=>'Inventory and sterilization demo account for stock, instruments, and operations workflow.'],
            ['role'=>'staff','employee_no'=>'DEMO-STF-001','name'=>'Demo General Staff','first_name'=>'Demo','middle_name'=>null,'last_name'=>'Staff','email'=>'demo.staff@mborrega-dentalcare.local','description'=>'General Staff demo account for reception, scheduling, queue, cashier session, payment, receipt, and checkout tasks.'],
        ];
    }



    public static function patientTemplates(): array
    {
        return [
            [
                'patient_no'=>'DEMO-PAT-001','first_name'=>'Juan Miguel','middle_name'=>'Ramos','last_name'=>'Dela Cruz','birth_date'=>'1988-05-12','sex'=>'Male','mobile'=>'0917-555-0101','email'=>'demo.patient.juan@mborrega-dentalcare.local','address'=>'Demo Address, Legazpi City','medical_alerts'=>'Hypertension; verify blood pressure before treatment.','allergies'=>'None reported','medications'=>'Amlodipine 5 mg once daily','conditions'=>'Controlled hypertension','dental_history'=>'Demo orientation patient: scheduled consultation with check-in and waiting queue entry.','scenario'=>'Front desk check-in and doctor intake walkthrough',
            ],
            [
                'patient_no'=>'DEMO-PAT-002','first_name'=>'Maria Elena','middle_name'=>'Santos','last_name'=>'Reyes','birth_date'=>'1995-10-02','sex'=>'Female','mobile'=>'0917-555-0102','email'=>'demo.patient.maria@mborrega-dentalcare.local','address'=>'Demo Address, Daraga, Albay','medical_alerts'=>'Penicillin allergy.','allergies'=>'Penicillin','medications'=>'None','conditions'=>'No chronic condition reported','dental_history'=>'Demo orientation patient: future confirmed prophylaxis appointment.','scenario'=>'Appointment calendar, provider schedule, and reminder walkthrough',
            ],
            [
                'patient_no'=>'DEMO-PAT-003','first_name'=>'Carlo Andre','middle_name'=>'Lim','last_name'=>'Garcia','birth_date'=>'2014-03-18','sex'=>'Male','mobile'=>'0917-555-0103','email'=>'demo.guardian.carlo@mborrega-dentalcare.local','address'=>'Demo Address, Guinobatan, Albay','medical_alerts'=>'Minor patient; guardian consent required.','allergies'=>'None reported','medications'=>'None','conditions'=>'Minor patient','dental_history'=>'Demo orientation patient: pediatric visit and guardian-verification example.','scenario'=>'Guardian notes and pediatric reception workflow',
            ],
            [
                'patient_no'=>'DEMO-PAT-004','first_name'=>'Ana Beatriz','middle_name'=>'Flores','last_name'=>'Lim','birth_date'=>'1979-07-24','sex'=>'Female','mobile'=>'0917-555-0104','email'=>'demo.patient.ana@mborrega-dentalcare.local','address'=>'Demo Address, Tabaco City','medical_alerts'=>'Diabetes history; ask for latest meal and blood sugar status.','allergies'=>'Latex sensitivity','medications'=>'Metformin 500 mg','conditions'=>'Type 2 diabetes','dental_history'=>'Demo orientation patient: completed treatment with invoice and payment sample.','scenario'=>'Billing, payment, and receipt walkthrough',
            ],
            [
                'patient_no'=>'DEMO-PAT-005','first_name'=>'Roberto','middle_name'=>'Villanueva','last_name'=>'Santos','birth_date'=>'1966-12-09','sex'=>'Male','mobile'=>'0917-555-0105','email'=>'demo.patient.roberto@mborrega-dentalcare.local','address'=>'Demo Address, Ligao City','medical_alerts'=>'Takes aspirin; review bleeding risk before extraction.','allergies'=>'None reported','medications'=>'Aspirin 80 mg','conditions'=>'Cardiac maintenance medication','dental_history'=>'Demo orientation patient: unpaid billing balance sample.','scenario'=>'Cashier queue and outstanding balance walkthrough',
            ],
        ];
    }

    public static function readiness(PDO $pdo): array
    {
        $checks = [];
        $checks[] = ['label'=>'Users table', 'ready'=>schema_table_exists(table('users')), 'detail'=>table('users')];
        $checks[] = ['label'=>'Roles table', 'ready'=>schema_table_exists(table('roles')), 'detail'=>table('roles')];
        $checks[] = ['label'=>'Demo marker column', 'ready'=>schema_column_exists(table('users'), 'is_demo'), 'detail'=>'users.is_demo'];
        $checks[] = ['label'=>'Status column', 'ready'=>schema_column_exists(table('users'), 'status'), 'detail'=>'users.status'];
        $checks[] = ['label'=>'Patients table', 'ready'=>schema_table_exists(table('patients')), 'detail'=>table('patients')];
        $checks[] = ['label'=>'Patient demo marker column', 'ready'=>schema_column_exists(table('patients'), 'is_demo'), 'detail'=>'patients.is_demo'];
        $missingRoles = [];
        try {
            $stmt = $pdo->prepare('SELECT code FROM `' . table('roles') . '` WHERE code IN (' . implode(',', array_fill(0, count(self::accountTemplates()), '?')) . ')');
            $codes = array_values(array_unique(array_column(self::accountTemplates(), 'role')));
            $stmt = $pdo->prepare('SELECT code FROM `' . table('roles') . '` WHERE code IN (' . implode(',', array_fill(0, count($codes), '?')) . ')');
            $stmt->execute($codes);
            $found = array_map('strval', $stmt->fetchAll(PDO::FETCH_COLUMN));
            $missingRoles = array_values(array_diff($codes, $found));
        } catch (Throwable $exception) {
            $missingRoles = ['unavailable'];
        }
        $checks[] = ['label'=>'Required demo roles', 'ready'=>empty($missingRoles), 'detail'=>empty($missingRoles) ? 'Available' : implode(', ', $missingRoles)];
        $ready = !in_array(false, array_column($checks, 'ready'), true);
        return ['ready'=>$ready, 'checks'=>$checks];
    }

    public static function counts(PDO $pdo): array
    {
        $counts = ['total'=>0,'active'=>0,'inactive'=>0,'dentist_profiles'=>0,'templates'=>count(self::accountTemplates()),'patient_templates'=>count(self::patientTemplates()),'patients'=>0,'appointment_requests'=>0,'appointments'=>0,'invoices'=>0,'services'=>0,'inventory_items'=>0,'sterilization_records'=>0];
        try {
            $counts['total'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('users') . '` WHERE is_demo=1')->fetchColumn();
            $counts['active'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('users') . '` WHERE is_demo=1 AND status="active"')->fetchColumn();
            $counts['inactive'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('users') . '` WHERE is_demo=1 AND status<>"active"')->fetchColumn();
            if (schema_table_exists(table('dentist_profiles'))) {
                $counts['dentist_profiles'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('dentist_profiles') . '` dp JOIN `' . table('users') . '` u ON u.id=dp.user_id WHERE u.is_demo=1')->fetchColumn();
            }
            if (schema_table_exists(table('patients')) && schema_column_exists(table('patients'), 'is_demo')) {
                $counts['patients'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('patients') . '` WHERE is_demo=1')->fetchColumn();
            }
            if (schema_table_exists(table('appointment_requests')) && schema_column_exists(table('appointment_requests'), 'is_demo')) {
                $counts['appointment_requests'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('appointment_requests') . '` WHERE is_demo=1')->fetchColumn();
            }
            if (schema_table_exists(table('appointments'))) {
                $counts['appointments'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('appointments') . '` a JOIN `' . table('patients') . '` p ON p.id=a.patient_id WHERE p.is_demo=1')->fetchColumn();
            }
            if (schema_table_exists(table('invoices'))) {
                $counts['invoices'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('invoices') . '` i JOIN `' . table('patients') . '` p ON p.id=i.patient_id WHERE p.is_demo=1')->fetchColumn();
            }
            if (schema_table_exists(table('demo_record_registry'))) {
                $counts['services'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('demo_record_registry') . '` WHERE entity_type="service_catalog"')->fetchColumn();
                $counts['inventory_items'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('demo_record_registry') . '` WHERE entity_type="inventory_item"')->fetchColumn();
                $counts['sterilization_records'] = (int)$pdo->query('SELECT COUNT(*) FROM `' . table('demo_record_registry') . '` WHERE entity_type IN ("sterilization_equipment","instrument_set","sterilization_batch","sterilization_batch_set","sterilization_batch_event")')->fetchColumn();
            }
        } catch (Throwable) {}
        return $counts;
    }

    public static function existingAccounts(PDO $pdo): array
    {
        try {
            $stmt = $pdo->query('SELECT u.id,u.employee_no,u.name,u.email,u.status,u.is_demo,r.code role_code,r.name role_name,u.updated_at,u.created_at FROM `' . table('users') . '` u JOIN `' . table('roles') . '` r ON r.id=u.role_id WHERE u.is_demo=1 OR u.employee_no LIKE "DEMO-%" OR u.email LIKE "demo.%@mborrega-dentalcare.local" ORDER BY r.name,u.name');
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable) {
            return [];
        }
    }


    public static function existingPatients(PDO $pdo): array
    {
        try {
            if (!schema_table_exists(table('patients')) || !schema_column_exists(table('patients'), 'is_demo')) return [];
            $stmt = $pdo->query('SELECT id,patient_no,first_name,middle_name,last_name,birth_date,sex,mobile,email,status,created_at FROM `' . table('patients') . '` WHERE is_demo=1 OR patient_no LIKE "DEMO-PAT-%" ORDER BY patient_no,last_name,first_name');
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable) {
            return [];
        }
    }

    public static function createOrRefreshPatients(PDO $pdo, bool $createWorkflowEntries = true): array
    {
        $readiness = self::readiness($pdo);
        $patientReady = schema_table_exists(table('patients')) && schema_column_exists(table('patients'), 'is_demo');
        if (!$patientReady) {
            throw new RuntimeException('Demo patient seeder is not ready. Apply all pending updates first so patients.is_demo exists.');
        }
        $pdo->beginTransaction();
        $result = ['created'=>0,'updated'=>0,'skipped'=>0,'patients'=>[],'appointments'=>0,'requests'=>0,'encounters'=>0,'invoices'=>0,'payments'=>0,'warnings'=>[]];
        try {
            $branchId = self::defaultBranchId($pdo);
            $dentistId = self::demoDentistUserId($pdo);
            if ($dentistId < 1) {
                $result['warnings'][] = 'No demo dentist account was found. Create/refresh demo accounts first to complete doctor-workflow entries.';
                $dentistId = self::anyDentistUserId($pdo);
            }
            $dentistName = $dentistId > 0 ? self::userNameById($pdo, $dentistId) : 'Demo Dentist';
            $chair = self::ensureDemoChair($pdo, $branchId, $result['warnings']);
            $services = self::ensureDemoServices($pdo, $branchId, $result['warnings']);
            foreach (self::patientTemplates() as $index => $template) {
                $row = self::upsertPatient($pdo, $template, $branchId, $dentistId);
                $result[$row['action']] = (int)($result[$row['action']] ?? 0) + 1;
                $result['patients'][] = $row;
                if ((int)$row['patient_id'] > 0) {
                    self::ensureMedicalHistory($pdo, (int)$row['patient_id'], $template);
                }
            }
            if ($createWorkflowEntries) {
                self::ensureDemoPatientWorkflow($pdo, $branchId, $dentistId, $dentistName, $chair, $services, $result);
            }
            setting_set('system','demo_patients_created_at',date('Y-m-d H:i:s'));
            setting_set('system','demo_patients_ready','1');
            audit('demo_patients_seeded','system',null,$result);
            $pdo->commit();
        } catch (Throwable $exception) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $exception;
        }
        return $result;
    }

    public static function createOrRefresh(PDO $pdo, bool $resetPasswords = true, bool $createDentistProfile = true): array
    {
        $readiness = self::readiness($pdo);
        if (empty($readiness['ready'])) {
            $failed = array_map(static fn(array $row): string => $row['label'] . ': ' . $row['detail'], array_filter($readiness['checks'], static fn(array $row): bool => empty($row['ready'])));
            throw new RuntimeException('Demo account seeder is not ready. ' . implode('; ', $failed));
        }
        $pdo->beginTransaction();
        $result = ['created'=>0,'updated'=>0,'skipped'=>0,'password_reset'=>$resetPasswords ? 1 : 0,'accounts'=>[],'warnings'=>[]];
        try {
            $branchId = self::defaultBranchId($pdo);
            foreach (self::accountTemplates() as $template) {
                $row = self::upsertAccount($pdo, $template, $branchId, $resetPasswords);
                $result[$row['action']] = (int)($result[$row['action']] ?? 0) + 1;
                $result['accounts'][] = $row;
                if ($template['role'] === 'dentist' && $createDentistProfile && $row['user_id'] > 0) {
                    self::ensureDentistProfile($pdo, (int)$row['user_id'], $branchId, $result['warnings']);
                    self::ensureDoctorAvailability($pdo, (int)$row['user_id'], $branchId, $result['warnings']);
                }
            }
            self::ensureCashierWindow($pdo, $branchId, $result['warnings']);
            setting_set('system','demo_accounts_created_at',date('Y-m-d H:i:s'));
            setting_set('system','demo_accounts_ready','1');
            setting_set('system','demo_accounts_default_password_notice','Random one-time passwords are generated only when demo accounts are created or explicitly reset, and are displayed once on the Demo Accounts page.');
            audit('demo_accounts_seeded','system',null,self::auditSafeAccountResult($result));
            $pdo->commit();
        } catch (Throwable $exception) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $exception;
        }
        return $result;
    }

    public static function deactivate(PDO $pdo): int
    {
        $stmt = $pdo->prepare('UPDATE `' . table('users') . '` SET status="inactive",updated_at=NOW() WHERE is_demo=1 AND id<>?');
        $stmt->execute([(int)(current_user()['id'] ?? 0)]);
        $count = $stmt->rowCount();
        setting_set('system','demo_accounts_active','0');
        audit('demo_accounts_deactivated','system',null,['count'=>$count]);
        return $count;
    }

    public static function reactivate(PDO $pdo): int
    {
        $stmt = $pdo->prepare('UPDATE `' . table('users') . '` SET status="active",updated_at=NOW() WHERE is_demo=1 AND id<>?');
        $stmt->execute([(int)(current_user()['id'] ?? 0)]);
        $count = $stmt->rowCount();
        setting_set('system','demo_accounts_active','1');
        audit('demo_accounts_reactivated','system',null,['count'=>$count]);
        return $count;
    }

    private static function upsertAccount(PDO $pdo, array $template, int $branchId, bool $resetPassword): array
    {
        $roleId = self::roleId($pdo, (string)$template['role']);
        $find = $pdo->prepare('SELECT u.*,r.code role_code FROM `' . table('users') . '` u JOIN `' . table('roles') . '` r ON r.id=u.role_id WHERE u.email=? OR u.employee_no=? LIMIT 1');
        $find->execute([(string)$template['email'], (string)$template['employee_no']]);
        $existing = $find->fetch(PDO::FETCH_ASSOC);
        $password = '';
        if ($existing) {
            if ((int)($existing['is_demo'] ?? 0) !== 1) {
                return ['action'=>'skipped','user_id'=>(int)$existing['id'],'role'=>(string)$template['role'],'name'=>(string)$existing['name'],'email'=>(string)$existing['email'],'employee_no'=>(string)$existing['employee_no'],'password'=>'','password_available'=>false,'message'=>'Skipped because a non-demo account already uses this email or employee number.'];
            }
            $sets = ['branch_id=?','role_id=?','employee_no=?','name=?','first_name=?','middle_name=?','last_name=?','email=?','status="active"','email_verified_at=COALESCE(email_verified_at,NOW())','must_change_password=0','is_demo=1','updated_at=NOW()'];
            $params = [$branchId ?: null,$roleId,(string)$template['employee_no'],(string)$template['name'],(string)$template['first_name'], $template['middle_name'], (string)$template['last_name'], (string)$template['email']];
            if ($resetPassword) {
                $password = self::generateTemporaryPassword();
                $sets[] = 'password_hash=?';
                $params[] = password_hash($password, PASSWORD_DEFAULT);
            }
            $params[] = (int)$existing['id'];
            $pdo->prepare('UPDATE `' . table('users') . '` SET ' . implode(',', $sets) . ' WHERE id=?')->execute($params);
            return ['action'=>'updated','user_id'=>(int)$existing['id'],'role'=>(string)$template['role'],'name'=>(string)$template['name'],'email'=>(string)$template['email'],'employee_no'=>(string)$template['employee_no'],'password'=>$password,'password_available'=>$password !== '','message'=>$resetPassword ? 'Updated with a new one-time password.' : 'Updated; the existing password was retained.'];
        }
        $password = self::generateTemporaryPassword();
        $stmt = $pdo->prepare('INSERT INTO `' . table('users') . '` (branch_id,role_id,employee_no,name,first_name,middle_name,last_name,email,password_hash,status,email_verified_at,must_change_password,is_demo,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,"active",NOW(),0,1,NOW(),NOW())');
        $stmt->execute([$branchId ?: null,$roleId,(string)$template['employee_no'],(string)$template['name'],(string)$template['first_name'],$template['middle_name'],(string)$template['last_name'],(string)$template['email'],password_hash($password, PASSWORD_DEFAULT)]);
        return ['action'=>'created','user_id'=>(int)$pdo->lastInsertId(),'role'=>(string)$template['role'],'name'=>(string)$template['name'],'email'=>(string)$template['email'],'employee_no'=>(string)$template['employee_no'],'password'=>$password,'password_available'=>true,'message'=>'Created with a one-time password.'];
    }

    private static function generateTemporaryPassword(int $length = 18): string
    {
        $length = max(16, min(64, $length));
        $groups = [
            'ABCDEFGHJKLMNPQRSTUVWXYZ',
            'abcdefghijkmnopqrstuvwxyz',
            '23456789',
            '!@#$%*-_',
        ];
        $characters = [];
        foreach ($groups as $group) {
            $characters[] = $group[random_int(0, strlen($group) - 1)];
        }
        $alphabet = implode('', $groups);
        while (count($characters) < $length) {
            $characters[] = $alphabet[random_int(0, strlen($alphabet) - 1)];
        }
        for ($index = count($characters) - 1; $index > 0; $index--) {
            $swap = random_int(0, $index);
            [$characters[$index], $characters[$swap]] = [$characters[$swap], $characters[$index]];
        }
        return implode('', $characters);
    }

    private static function auditSafeAccountResult(array $result): array
    {
        $safe = [
            'created' => (int)($result['created'] ?? 0),
            'updated' => (int)($result['updated'] ?? 0),
            'skipped' => (int)($result['skipped'] ?? 0),
            'password_reset' => (int)($result['password_reset'] ?? 0),
            'warnings' => array_values(array_map('strval', (array)($result['warnings'] ?? []))),
            'accounts' => [],
        ];
        foreach ((array)($result['accounts'] ?? []) as $account) {
            $safe['accounts'][] = [
                'action' => (string)($account['action'] ?? ''),
                'user_id' => (int)($account['user_id'] ?? 0),
                'role' => (string)($account['role'] ?? ''),
                'email' => (string)($account['email'] ?? ''),
                'employee_no' => (string)($account['employee_no'] ?? ''),
                'password_generated' => !empty($account['password_available']),
                'message' => (string)($account['message'] ?? ''),
            ];
        }
        return $safe;
    }

    private static function roleId(PDO $pdo, string $roleCode): int
    {
        $stmt = $pdo->prepare('SELECT id FROM `' . table('roles') . '` WHERE code=? LIMIT 1');
        $stmt->execute([$roleCode]);
        $id = (int)$stmt->fetchColumn();
        if ($id < 1) throw new RuntimeException('Missing role: ' . $roleCode . '. Apply all pending updates first.');
        return $id;
    }

    private static function defaultBranchId(PDO $pdo): int
    {
        if (!schema_table_exists(table('branches'))) return 0;
        $id = (int)$pdo->query('SELECT id FROM `' . table('branches') . '` WHERE is_active=1 ORDER BY id LIMIT 1')->fetchColumn();
        if ($id > 0) return $id;
        $stmt = $pdo->prepare('INSERT INTO `' . table('branches') . '` (name,code,email,phone,address,timezone,is_active,created_at,updated_at) VALUES ("Demo Dental Clinic","DEMO",NULL,NULL,"Fictional demo branch for training only","Asia/Manila",1,NOW(),NOW()) ON DUPLICATE KEY UPDATE is_active=1,updated_at=NOW()');
        $stmt->execute();
        return (int)$pdo->query('SELECT id FROM `' . table('branches') . '` WHERE code="DEMO" ORDER BY id LIMIT 1')->fetchColumn();
    }

    private static function ensureDentistProfile(PDO $pdo, int $userId, int $branchId, array &$warnings): void
    {
        if (!schema_table_exists(table('dentist_profiles'))) { $warnings[] = 'Dentist profile table is unavailable.'; return; }
        $name = (string)$pdo->query('SELECT name FROM `' . table('users') . '` WHERE id=' . $userId)->fetchColumn();
        $specialtyId = self::ensureSpecialty($pdo, 'General Dentistry');
        $profileTable = table('dentist_profiles');
        $stmt = $pdo->prepare('SELECT id FROM `' . $profileTable . '` WHERE user_id=? LIMIT 1');
        $stmt->execute([$userId]);
        $profileId = (int)($stmt->fetchColumn() ?: 0);
        $values = [
            'user_id' => $userId,
            'branch_id' => $branchId ?: null,
            'professional_title' => 'Dr.',
            'display_name' => $name,
            'specialty_id' => $specialtyId ?: null,
            'license_number' => 'DEMO-PRC-2026-001',
            'license_expiry' => date('Y-m-d', strtotime('+2 years')),
            'years_in_practice' => 5,
            'languages_json' => json_encode(['English','Filipino'], JSON_UNESCAPED_UNICODE),
            'public_biography' => 'Fictional dentist profile used for client orientation and workflow testing only.',
            'employment_type' => 'full_time',
            'primary_branch_id' => $branchId ?: null,
            'default_chair_name' => 'Demo Chair 1',
            'default_duration_minutes' => 30,
            'maximum_daily_appointments' => 16,
            'accepting_new_patients' => 1,
            'public_profile_enabled' => 1,
            'profile_status' => 'active',
            'credential_status' => 'verified',
            'credential_verified_by' => (int)(current_user()['id'] ?? 0) ?: null,
            'credential_verified_at' => date('Y-m-d H:i:s'),
            'credential_note' => 'Fictional demo credential for training/orientation only.',
            'updated_at' => date('Y-m-d H:i:s'),
        ];
        if ($profileId > 0) {
            self::updateRecord($pdo, 'dentist_profiles', $values, 'id=?', [$profileId]);
        } else {
            $values['created_at'] = date('Y-m-d H:i:s');
            self::insertRecord($pdo, 'dentist_profiles', $values);
        }
        if (schema_table_exists(table('dentist_branch_assignments')) && $branchId > 0) {
            try {
                $pdo->prepare('INSERT INTO `' . table('dentist_branch_assignments') . '` (dentist_user_id,branch_id,is_primary,status,created_at) VALUES (?,?,1,"active",NOW()) ON DUPLICATE KEY UPDATE is_primary=1,status="active"')->execute([$userId,$branchId]);
            } catch (Throwable $exception) {
                $warnings[] = 'Demo dentist branch assignment could not be saved: ' . $exception->getMessage();
            }
        }
    }

    private static function ensureSpecialty(PDO $pdo, string $name): int
    {
        if (!schema_table_exists(table('dentist_specialties'))) return 0;
        $stmt = $pdo->prepare('SELECT id FROM `' . table('dentist_specialties') . '` WHERE name=? LIMIT 1');
        $stmt->execute([$name]);
        $id = (int)$stmt->fetchColumn();
        if ($id > 0) return $id;
        $code = strtolower(preg_replace('/[^a-z0-9]+/', '_', $name) ?? 'general_dentistry');
        $code = trim($code, '_') ?: 'general_dentistry';
        $values = ['code'=>$code,'name'=>$name,'description'=>'Demo specialty for orientation records','is_active'=>1,'created_at'=>date('Y-m-d H:i:s'),'updated_at'=>date('Y-m-d H:i:s')];
        $id = self::insertRecord($pdo, 'dentist_specialties', $values);
        if ($id < 1) {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('dentist_specialties') . '` WHERE name=? OR code=? LIMIT 1');
            $stmt->execute([$name,$code]);
            $id = (int)($stmt->fetchColumn() ?: 0);
        }
        return $id;
    }

    private static function ensureDoctorAvailability(PDO $pdo, int $userId, int $branchId, array &$warnings): void
    {
        if ($branchId < 1 || !schema_table_exists(table('doctor_availability_rules'))) return;
        $existing = $pdo->prepare('SELECT COUNT(*) FROM `' . table('doctor_availability_rules') . '` WHERE doctor_user_id=? AND branch_id=? AND status IN ("approved","active")');
        $existing->execute([$userId,$branchId]);
        if ((int)$existing->fetchColumn() > 0) return;
        $insert = $pdo->prepare('INSERT INTO `' . table('doctor_availability_rules') . '` (doctor_user_id,branch_id,day_of_week,start_time,end_time,effective_from,effective_until,status,created_by,approved_by,created_at,updated_at) VALUES (?,?,?,?,?,CURDATE(),NULL,"active",?,?,NOW(),NOW())');
        foreach ([1,2,3,4,5] as $day) {
            $insert->execute([$userId,$branchId,$day,'09:00:00','17:00:00',(int)(current_user()['id'] ?? 0) ?: null,(int)(current_user()['id'] ?? 0) ?: null]);
        }
    }

    private static function ensureCashierWindow(PDO $pdo, int $branchId, array &$warnings): void
    {
        if ($branchId < 1 || !schema_table_exists(table('cashier_windows'))) return;
        $stmt = $pdo->prepare('INSERT INTO `' . table('cashier_windows') . '` (branch_id,window_code,window_name,is_active,created_by,created_at,updated_at) VALUES (?,"DEMO-W1","Demo Cashier Window 1",1,?,NOW(),NOW()) ON DUPLICATE KEY UPDATE window_name=VALUES(window_name),is_active=1,updated_at=NOW()');
        $stmt->execute([$branchId,(int)(current_user()['id'] ?? 0) ?: null]);
    }

    private static function upsertPatient(PDO $pdo, array $template, int $branchId, int $dentistId): array
    {
        $patientNo = (string)$template['patient_no'];
        $find = $pdo->prepare('SELECT * FROM `' . table('patients') . '` WHERE patient_no=? OR email=? LIMIT 1');
        $find->execute([$patientNo, (string)$template['email']]);
        $existing = $find->fetch(PDO::FETCH_ASSOC);
        $values = [
            'branch_id'=>$branchId ?: null,
            'primary_dentist_user_id'=>$dentistId ?: null,
            'primary_dentist_assigned_at'=>$dentistId > 0 ? date('Y-m-d H:i:s') : null,
            'primary_dentist_change_reason'=>'Demo orientation assignment',
            'patient_no'=>$patientNo,
            'first_name'=>(string)$template['first_name'],
            'middle_name'=>(string)($template['middle_name'] ?? ''),
            'last_name'=>(string)$template['last_name'],
            'preferred_name'=>(string)$template['first_name'],
            'birth_date'=>(string)$template['birth_date'],
            'sex'=>(string)$template['sex'],
            'civil_status'=>'Demo',
            'nationality'=>'Filipino',
            'occupation'=>'Demo patient',
            'preferred_language'=>'English / Filipino',
            'communication_preference'=>'email',
            'mobile'=>(string)$template['mobile'],
            'email'=>(string)$template['email'],
            'address'=>(string)$template['address'],
            'emergency_contact'=>'Demo Emergency Contact - 0917-555-0199',
            'medical_alerts'=>(string)$template['medical_alerts'],
            'status'=>'active',
            'is_demo'=>1,
            'created_by'=>(int)(current_user()['id'] ?? 0) ?: null,
            'updated_at'=>date('Y-m-d H:i:s'),
        ];
        if ($existing) {
            if ((int)($existing['is_demo'] ?? 0) !== 1 && !str_starts_with((string)($existing['patient_no'] ?? ''), 'DEMO-PAT-')) {
                return ['action'=>'skipped','patient_id'=>(int)$existing['id'],'patient_no'=>(string)$existing['patient_no'],'name'=>(string)$existing['first_name'].' '.(string)$existing['last_name'],'message'=>'Skipped because a non-demo patient already uses this patient number or email.'];
            }
            self::updateRecord($pdo, 'patients', $values, 'id=?', [(int)$existing['id']]);
            return ['action'=>'updated','patient_id'=>(int)$existing['id'],'patient_no'=>$patientNo,'name'=>$template['first_name'].' '.$template['last_name'],'message'=>'Updated demo patient.'];
        }
        $values['created_at'] = date('Y-m-d H:i:s');
        $patientId = self::insertRecord($pdo, 'patients', $values);
        self::registerDemoRecord($pdo, 'patient', $patientId, $patientNo);
        return ['action'=>'created','patient_id'=>$patientId,'patient_no'=>$patientNo,'name'=>$template['first_name'].' '.$template['last_name'],'message'=>'Created demo patient.'];
    }

    private static function ensureMedicalHistory(PDO $pdo, int $patientId, array $template): void
    {
        if (!schema_table_exists(table('medical_histories'))) return;
        $exists = $pdo->prepare('SELECT id FROM `' . table('medical_histories') . '` WHERE patient_id=? AND dental_history LIKE "Demo orientation patient:%" ORDER BY id DESC LIMIT 1');
        $exists->execute([$patientId]);
        $id = (int)$exists->fetchColumn();
        $values = [
            'patient_id'=>$patientId,
            'allergies'=>(string)$template['allergies'],
            'medications'=>(string)$template['medications'],
            'conditions'=>(string)$template['conditions'],
            'dental_history'=>(string)$template['dental_history'],
            'confirmed_at'=>date('Y-m-d H:i:s'),
            'source_type'=>'staff_recorded',
            'verification_status'=>'pending_review',
            'created_by'=>(int)(current_user()['id'] ?? 0) ?: null,
            'created_at'=>date('Y-m-d H:i:s'),
        ];
        if ($id > 0) {
            self::updateRecord($pdo, 'medical_histories', $values, 'id=?', [$id]);
        } else {
            $historyId = self::insertRecord($pdo, 'medical_histories', $values);
            self::registerDemoRecord($pdo, 'medical_history', $historyId, 'medical-'.$patientId);
        }
    }

    private static function ensureDemoPatientWorkflow(PDO $pdo, int $branchId, int $dentistId, string $dentistName, array $chair, array $services, array &$result): void
    {
        $patients = self::patientIdMap($pdo);
        if (!$patients) return;
        $consultation = $services['consultation'] ?? ['id'=>null,'name'=>'Dental Consultation','fee'=>500,'duration'=>30,'provider_type'=>'dentist'];
        $prophylaxis = $services['prophylaxis'] ?? ['id'=>null,'name'=>'Oral Prophylaxis','fee'=>1200,'duration'=>45,'provider_type'=>'dentist'];
        $restoration = $services['restoration'] ?? ['id'=>null,'name'=>'Tooth Restoration','fee'=>1800,'duration'=>60,'provider_type'=>'dentist'];
        $bpCheck = $services['bp_check'] ?? ['id'=>null,'name'=>'Blood Pressure Check','fee'=>0,'duration'=>15,'provider_type'=>'inhouse'];

        if (isset($patients['DEMO-PAT-001'])) {
            $start = date('Y-m-d H:i:s', strtotime('+2 hours'));
            $appointmentId = self::ensureDemoAppointment($pdo, $branchId, $patients['DEMO-PAT-001'], $consultation, $dentistId, $dentistName, $chair, $start, 'confirmed', 'DEMO-WF-CHECKIN-001');
            if ($appointmentId > 0) $result['appointments']++;
            $queueId = self::ensureDemoQueue($pdo, $branchId, $patients['DEMO-PAT-001'], $appointmentId, $consultation, $dentistId, $dentistName, $chair, 'waiting', 'DEMO-Q-WAITING-001');
            if ($queueId > 0) self::registerDemoRecord($pdo, 'queue_entry', $queueId, 'DEMO-Q-WAITING-001');
        }
        if (isset($patients['DEMO-PAT-002'])) {
            $start = date('Y-m-d 10:00:00', strtotime('+1 day'));
            $appointmentId = self::ensureDemoAppointment($pdo, $branchId, $patients['DEMO-PAT-002'], $prophylaxis, $dentistId, $dentistName, $chair, $start, 'confirmed', 'DEMO-WF-FUTURE-002');
            if ($appointmentId > 0) $result['appointments']++;
        }
        if (isset($patients['DEMO-PAT-003'])) {
            $requestId = self::ensureDemoRequest($pdo, $branchId, $patients['DEMO-PAT-003'], $bpCheck, $dentistId, $dentistName, 'DEMOREQ003');
            if ($requestId > 0) $result['requests']++;
        }
        if (isset($patients['DEMO-PAT-004'])) {
            $encounterId = self::ensureDemoEncounter($pdo, $branchId, $patients['DEMO-PAT-004'], $dentistId, $dentistName, null, 'finalized', 'Completed demo restoration and oral hygiene instruction.', 'DEMO-ENC-PAID-004');
            if ($encounterId > 0) $result['encounters']++;
            $invoiceId = self::ensureDemoInvoice($pdo, $branchId, $patients['DEMO-PAT-004'], $restoration, $encounterId, (float)$restoration['fee'], (float)$restoration['fee'], 'paid', 'DEMO-INV-PAID-004');
            if ($invoiceId > 0) { $result['invoices']++; $paymentId = self::ensureDemoPayment($pdo, $invoiceId, (float)$restoration['fee'], 'gcash', 'DEMO-GCASH-004'); if ($paymentId > 0) $result['payments']++; }
        }
        if (isset($patients['DEMO-PAT-005'])) {
            $invoiceId = self::ensureDemoInvoice($pdo, $branchId, $patients['DEMO-PAT-005'], $consultation, null, (float)$consultation['fee'], 0.0, 'unpaid', 'DEMO-INV-UNPAID-005');
            if ($invoiceId > 0) $result['invoices']++;
        }
    }

    private static function ensureDemoAppointment(PDO $pdo, int $branchId, int $patientId, array $service, int $dentistId, string $dentistName, array $chair, string $startAt, string $status, string $key): int
    {
        if (!schema_table_exists(table('appointments'))) return 0;
        $stmt = $pdo->prepare('SELECT id FROM `' . table('appointments') . '` WHERE patient_id=? AND notes LIKE ? LIMIT 1');
        $stmt->execute([$patientId, '%'.$key.'%']);
        $id = (int)$stmt->fetchColumn();
        $duration = max(15, (int)($service['duration'] ?? 30));
        $endAt = date('Y-m-d H:i:s', strtotime($startAt . ' +' . $duration . ' minutes'));
        $providerType = (string)($service['provider_type'] ?? 'dentist');
        $values = [
            'branch_id'=>$branchId ?: null,'patient_id'=>$patientId,'service_id'=>$service['id'] ?? null,'dentist_user_id'=>$providerType === 'dentist' ? ($dentistId ?: null) : null,'dentist_name'=>$providerType === 'dentist' ? $dentistName : null,'inhouse_provider_user_id'=>null,'inhouse_provider_name'=>$providerType === 'inhouse' ? 'Demo In-house Care Team' : null,'service_name'=>(string)$service['name'],'provider_type'=>$providerType,'start_at'=>$startAt,'end_at'=>$endAt,'chair_id'=>$chair['id'] ?? null,'chair_name'=>$chair['name'] ?? null,'status'=>$status,'appointment_origin'=>'demo','booking_source'=>'staff','reason_for_visit'=>'Fictional demo workflow entry.','confirmation_status'=>'confirmed','confirmation_source'=>'demo','patient_agreed_at'=>date('Y-m-d H:i:s'),'schedule_bypass'=>1,'schedule_bypass_reason'=>'Demo orientation seed','notes'=>'Demo orientation workflow: '.$key,'created_by'=>(int)(current_user()['id'] ?? 0) ?: null,'updated_at'=>date('Y-m-d H:i:s'),
        ];
        if ($id > 0) { self::updateRecord($pdo, 'appointments', $values, 'id=?', [$id]); return $id; }
        $values['created_at'] = date('Y-m-d H:i:s');
        $id = self::insertRecord($pdo, 'appointments', $values);
        self::registerDemoRecord($pdo, 'appointment', $id, $key);
        return $id;
    }

    private static function ensureDemoQueue(PDO $pdo, int $branchId, int $patientId, int $appointmentId, array $service, int $dentistId, string $dentistName, array $chair, string $status, string $key): int
    {
        if (!schema_table_exists(table('queue_entries'))) return 0;
        $stmt = $pdo->prepare('SELECT id FROM `' . table('queue_entries') . '` WHERE notes LIKE ? LIMIT 1');
        $stmt->execute(['%'.$key.'%']);
        $id = (int)$stmt->fetchColumn();
        $values = ['branch_id'=>$branchId ?: null,'appointment_id'=>$appointmentId ?: null,'patient_id'=>$patientId,'source_type'=>'appointment','queue_no'=>'D'.date('md').'-01','priority'=>'regular','service_id'=>$service['id'] ?? null,'service_name'=>(string)$service['name'],'provider_type'=>(string)($service['provider_type'] ?? 'dentist'),'dentist_user_id'=>$dentistId ?: null,'dentist_name'=>$dentistName,'chair_id'=>$chair['id'] ?? null,'chair_name'=>$chair['name'] ?? null,'reason'=>'Demo patient check-in.','notes'=>'Demo orientation queue: '.$key,'status'=>$status,'checked_in_by'=>(int)(current_user()['id'] ?? 0) ?: null,'checked_in_at'=>date('Y-m-d H:i:s'),'updated_at'=>date('Y-m-d H:i:s')];
        if ($id > 0) { self::updateRecord($pdo, 'queue_entries', $values, 'id=?', [$id]); return $id; }
        $id = self::insertRecord($pdo, 'queue_entries', $values);
        return $id;
    }

    private static function ensureDemoRequest(PDO $pdo, int $branchId, int $patientId, array $service, int $dentistId, string $dentistName, string $reference): int
    {
        if (!schema_table_exists(table('appointment_requests'))) return 0;
        $patient = self::patientById($pdo, $patientId);
        if (!$patient) return 0;
        $stmt = $pdo->prepare('SELECT id FROM `' . table('appointment_requests') . '` WHERE public_reference=? LIMIT 1');
        $stmt->execute([$reference]);
        $id = (int)$stmt->fetchColumn();
        $preferredDate = date('Y-m-d', strtotime('+2 days'));
        $values = ['public_reference'=>$reference,'branch_id'=>$branchId ?: null,'patient_type'=>'returning','patient_id'=>$patientId,'patient_no_entered'=>(string)$patient['patient_no'],'first_name'=>(string)$patient['first_name'],'middle_name'=>(string)($patient['middle_name'] ?? ''),'last_name'=>(string)$patient['last_name'],'birth_date'=>(string)$patient['birth_date'],'sex'=>(string)($patient['sex'] ?? ''),'mobile'=>(string)($patient['mobile'] ?? '0917-555-0103'),'email'=>(string)($patient['email'] ?? ''),'address'=>(string)($patient['address'] ?? ''),'guardian_name'=>'Demo Guardian','guardian_mobile'=>'0917-555-0199','service_name'=>(string)$service['name'],'service_id'=>$service['id'] ?? null,'preferred_dentist'=>$dentistName,'preferred_dentist_user_id'=>$dentistId ?: null,'provider_preference_mode'=>'clinic_decides','preferred_date'=>$preferredDate,'preferred_time'=>'14:00:00','alternate_date'=>date('Y-m-d', strtotime('+3 days')),'alternate_time'=>'10:00:00','urgency'=>'routine','reason'=>'Demo public/portal request for client orientation.','allergies'=>'See health information relevant to dental care.','medications'=>'See health information relevant to dental care.','conditions'=>'Minor demo dental-safety record.','status'=>'submitted','consent_at'=>date('Y-m-d H:i:s'),'submitted_ip'=>'127.0.0.1','is_demo'=>1,'created_at'=>date('Y-m-d H:i:s'),'updated_at'=>date('Y-m-d H:i:s')];
        if ($id > 0) { self::updateRecord($pdo, 'appointment_requests', $values, 'id=?', [$id]); return $id; }
        $id = self::insertRecord($pdo, 'appointment_requests', $values);
        self::registerDemoRecord($pdo, 'appointment_request', $id, $reference);
        return $id;
    }

    private static function ensureDemoEncounter(PDO $pdo, int $branchId, int $patientId, int $dentistId, string $dentistName, ?int $appointmentId, string $status, string $procedureDone, string $key): int
    {
        if (!schema_table_exists(table('clinical_encounters'))) return 0;
        $stmt = $pdo->prepare('SELECT id FROM `' . table('clinical_encounters') . '` WHERE patient_id=? AND soap_note LIKE ? LIMIT 1');
        $stmt->execute([$patientId, '%'.$key.'%']);
        $id = (int)$stmt->fetchColumn();
        $values = ['branch_id'=>$branchId ?: null,'patient_id'=>$patientId,'appointment_id'=>$appointmentId,'dentist_user_id'=>$dentistId ?: null,'dentist_name'=>$dentistName,'visit_type'=>'scheduled','chief_complaint'=>'Demo restoration follow-up.','history_present_condition'=>'Fictional sample only.','examination'=>'Demo intraoral exam documented.','diagnosis'=>'Dental caries, demo record.','treatment_plan'=>'Restore affected tooth and reinforce oral hygiene.','clinical_disposition'=>'billing','patient_advice'=>'Return for routine recall.','procedure_done'=>$procedureDone,'soap_note'=>'Demo orientation clinical note: '.$key,'follow_up_date'=>date('Y-m-d', strtotime('+6 months')),'follow_up_purpose'=>'Routine recall','status'=>$status,'created_at'=>date('Y-m-d H:i:s', strtotime('-7 days')),'updated_at'=>date('Y-m-d H:i:s'),'finalized_at'=>$status === 'finalized' ? date('Y-m-d H:i:s', strtotime('-7 days +45 minutes')) : null,'completion_destination'=>'billing','billing_handoff_amount'=>1800,'billing_handoff_at'=>date('Y-m-d H:i:s', strtotime('-7 days +45 minutes'))];
        if ($id > 0) { self::updateRecord($pdo, 'clinical_encounters', $values, 'id=?', [$id]); return $id; }
        $id = self::insertRecord($pdo, 'clinical_encounters', $values);
        self::registerDemoRecord($pdo, 'clinical_encounter', $id, $key);
        return $id;
    }

    private static function ensureDemoInvoice(PDO $pdo, int $branchId, int $patientId, array $service, ?int $encounterId, float $amount, float $paid, string $status, string $invoiceNo): int
    {
        if (!schema_table_exists(table('invoices'))) return 0;
        $stmt = $pdo->prepare('SELECT id FROM `' . table('invoices') . '` WHERE invoice_no=? LIMIT 1');
        $stmt->execute([$invoiceNo]);
        $id = (int)$stmt->fetchColumn();
        $values = ['branch_id'=>$branchId ?: null,'invoice_no'=>$invoiceNo,'patient_id'=>$patientId,'service_id'=>$service['id'] ?? null,'encounter_id'=>$encounterId,'description'=>'Demo billing item: '.(string)$service['name'],'subtotal'=>$amount,'discount_type'=>'none','discount_value'=>0,'discount_amount'=>0,'tax_amount'=>0,'amount'=>$amount,'paid_amount'=>$paid,'status'=>$status,'created_by'=>(int)(current_user()['id'] ?? 0) ?: null,'created_at'=>date('Y-m-d H:i:s'),'updated_at'=>date('Y-m-d H:i:s'),'pos_finalized_at'=>$status === 'paid' ? date('Y-m-d H:i:s') : null,'pos_finalized_by'=>$status === 'paid' ? ((int)(current_user()['id'] ?? 0) ?: null) : null];
        if ($id > 0) { self::updateRecord($pdo, 'invoices', $values, 'id=?', [$id]); return $id; }
        $id = self::insertRecord($pdo, 'invoices', $values);
        self::registerDemoRecord($pdo, 'invoice', $id, $invoiceNo);
        return $id;
    }

    private static function ensureDemoPayment(PDO $pdo, int $invoiceId, float $amount, string $method, string $reference): int
    {
        if (!schema_table_exists(table('payments'))) return 0;
        $stmt = $pdo->prepare('SELECT id FROM `' . table('payments') . '` WHERE invoice_id=? AND reference_no=? LIMIT 1');
        $stmt->execute([$invoiceId, $reference]);
        $id = (int)$stmt->fetchColumn();
        $values = ['invoice_id'=>$invoiceId,'source_type'=>'patient','status'=>'posted','amount'=>$amount,'adjustment_total'=>0,'tendered_amount'=>$amount,'change_amount'=>0,'method'=>$method,'reference_no'=>$reference,'payer_name'=>'Demo Payor','payer_relationship'=>'self','notes'=>'Demo payment for client orientation.','received_by'=>(int)(current_user()['id'] ?? 0) ?: null,'created_at'=>date('Y-m-d H:i:s')];
        if ($id > 0) { self::updateRecord($pdo, 'payments', $values, 'id=?', [$id]); return $id; }
        $id = self::insertRecord($pdo, 'payments', $values);
        self::registerDemoRecord($pdo, 'payment', $id, $reference);
        return $id;
    }

    private static function ensureDemoServices(PDO $pdo, int $branchId, array &$warnings): array
    {
        $services = [
            'consultation'=>['name'=>'Demo Dental Consultation','description'=>'Fictional consultation service for orientation.','duration'=>30,'fee'=>500,'provider_type'=>'dentist','requires_dentist'=>1],
            'prophylaxis'=>['name'=>'Demo Oral Prophylaxis','description'=>'Fictional cleaning service for schedule and billing walkthrough.','duration'=>45,'fee'=>1200,'provider_type'=>'dentist','requires_dentist'=>1],
            'restoration'=>['name'=>'Demo Tooth Restoration','description'=>'Fictional restoration service for treatment-to-billing workflow.','duration'=>60,'fee'=>1800,'provider_type'=>'dentist','requires_dentist'=>1],
            'bp_check'=>['name'=>'Demo Blood Pressure Check','description'=>'Fictional in-house check-up service that does not require a dentist.','duration'=>15,'fee'=>0,'provider_type'=>'inhouse','requires_dentist'=>0],
            'extraction'=>['name'=>'Demo Tooth Extraction','description'=>'Fictional extraction service for surgical consent and billing orientation.','duration'=>60,'fee'=>2200,'provider_type'=>'dentist','requires_dentist'=>1],
            'root_canal_consult'=>['name'=>'Demo Root Canal Consultation','description'=>'Fictional endodontic consultation service for treatment planning.','duration'=>45,'fee'=>1000,'provider_type'=>'dentist','requires_dentist'=>1],
            'ortho_consult'=>['name'=>'Demo Orthodontic Consultation','description'=>'Fictional orthodontic consultation service for public booking preview.','duration'=>45,'fee'=>800,'provider_type'=>'dentist','requires_dentist'=>1],
        ];
        if (!schema_table_exists(table('service_catalog'))) { $warnings[] = 'Service catalog table is unavailable; demo entries use text-only service names.'; return $services; }
        foreach ($services as $key => $service) {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('service_catalog') . '` WHERE name=? LIMIT 1');
            $stmt->execute([$service['name']]);
            $id = (int)$stmt->fetchColumn();
            $values = ['branch_id'=>$branchId ?: null,'name'=>$service['name'],'description'=>$service['description'],'duration_minutes'=>$service['duration'],'standard_fee'=>$service['fee'],'fee_status'=>$service['fee'] > 0 ? 'billable' : 'no_charge','no_charge_reason'=>$service['fee'] > 0 ? null : 'Demo no-charge in-house check','preparation_minutes'=>0,'cleanup_minutes'=>0,'provider_type'=>$service['provider_type'],'provider_role_code'=>$service['provider_type'] === 'inhouse' ? 'dental_assistant' : 'dentist','slot_capacity'=>1,'requires_chair'=>1,'requires_dentist'=>$service['requires_dentist'],'is_public'=>1,'booking_scope'=>'public','is_active'=>1,'updated_at'=>date('Y-m-d H:i:s')];
            if ($id > 0) {
                self::updateRecord($pdo, 'service_catalog', $values, 'id=?', [$id]);
            } else {
                $values['created_at'] = date('Y-m-d H:i:s');
                $id = self::insertRecord($pdo, 'service_catalog', $values);
                self::registerDemoRecord($pdo, 'service_catalog', $id, (string)$service['name']);
            }
            $services[$key]['id'] = $id;
        }
        return $services;
    }

    private static function ensureDemoChair(PDO $pdo, int $branchId, array &$warnings): array
    {
        $chair = ['id'=>null,'name'=>'Demo Chair 1'];
        if ($branchId < 1 || !schema_table_exists(table('clinic_chairs'))) return $chair;
        $stmt = $pdo->prepare('SELECT id,chair_name FROM `' . table('clinic_chairs') . '` WHERE branch_id=? AND chair_code="DEMO-1" LIMIT 1');
        $stmt->execute([$branchId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $values = ['branch_id'=>$branchId,'chair_code'=>'DEMO-1','chair_name'=>'Demo Chair 1','room_name'=>'Demo Operatory','usage_mode'=>'dentist_bound','status'=>'available','notes'=>'Fictional demo chair for orientation only.','created_by'=>(int)(current_user()['id'] ?? 0) ?: null,'updated_at'=>date('Y-m-d H:i:s')];
        if ($row) {
            self::updateRecord($pdo, 'clinic_chairs', $values, 'id=?', [(int)$row['id']]);
            return ['id'=>(int)$row['id'],'name'=>(string)$row['chair_name']];
        }
        $values['created_at'] = date('Y-m-d H:i:s');
        $id = self::insertRecord($pdo, 'clinic_chairs', $values);
        self::registerDemoRecord($pdo, 'clinic_chair', $id, 'DEMO-1');
        return ['id'=>$id,'name'=>'Demo Chair 1'];
    }

    private static function patientIdMap(PDO $pdo): array
    {
        if (!schema_table_exists(table('patients'))) return [];
        $rows = $pdo->query('SELECT id,patient_no FROM `' . table('patients') . '` WHERE patient_no LIKE "DEMO-PAT-%"')->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $map = [];
        foreach ($rows as $row) $map[(string)$row['patient_no']] = (int)$row['id'];
        return $map;
    }

    private static function patientById(PDO $pdo, int $patientId): array
    {
        $stmt = $pdo->prepare('SELECT * FROM `' . table('patients') . '` WHERE id=? LIMIT 1');
        $stmt->execute([$patientId]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    }

    private static function demoDentistUserId(PDO $pdo): int
    {
        $stmt = $pdo->prepare('SELECT id FROM `' . table('users') . '` WHERE email=? AND is_demo=1 AND status="active" LIMIT 1');
        $stmt->execute(['demo.dentist@mborrega-dentalcare.local']);
        return (int)$stmt->fetchColumn();
    }

    private static function anyDentistUserId(PDO $pdo): int
    {
        try {
            $stmt = $pdo->query('SELECT u.id FROM `' . table('users') . '` u JOIN `' . table('roles') . '` r ON r.id=u.role_id WHERE r.code="dentist" AND u.status="active" ORDER BY u.id LIMIT 1');
            return (int)$stmt->fetchColumn();
        } catch (Throwable) { return 0; }
    }

    private static function userNameById(PDO $pdo, int $userId): string
    {
        if ($userId < 1) return '';
        $stmt = $pdo->prepare('SELECT name FROM `' . table('users') . '` WHERE id=? LIMIT 1');
        $stmt->execute([$userId]);
        return (string)$stmt->fetchColumn();
    }

    private static function insertRecord(PDO $pdo, string $logicalTable, array $values): int
    {
        $physical = table($logicalTable);
        if (!schema_table_exists($physical)) return 0;

        // Some production databases have strict NOT NULL branch_id columns without
        // a database default. Demo seeding should not crash when a legacy/demo
        // helper omits branch_id for related records such as payments,
        // prescriptions, follow-ups, recalls, communications, or link rows.
        // Fill it only when the target table actually has the column and the
        // caller did not already provide a branch_id value.
        if (!array_key_exists('branch_id', $values) && schema_column_exists($physical, 'branch_id')) {
            $branchId = self::defaultBranchId($pdo);
            if ($branchId > 0) {
                $values = ['branch_id' => $branchId] + $values;
            }
        }

        $columns = [];
        $params = [];
        foreach ($values as $column => $value) {
            if (schema_column_exists($physical, (string)$column)) {
                $columns[] = '`' . str_replace('`','``',(string)$column) . '`';
                $params[] = $value;
            }
        }
        if (!$columns) return 0;
        $placeholders = implode(',', array_fill(0, count($columns), '?'));
        $stmt = $pdo->prepare('INSERT INTO `' . $physical . '` (' . implode(',', $columns) . ') VALUES (' . $placeholders . ')');
        $stmt->execute($params);
        return (int)$pdo->lastInsertId();
    }

    private static function updateRecord(PDO $pdo, string $logicalTable, array $values, string $where, array $whereParams): void
    {
        $physical = table($logicalTable);
        if (!schema_table_exists($physical)) return;
        $sets = [];
        $params = [];
        foreach ($values as $column => $value) {
            if (schema_column_exists($physical, (string)$column)) {
                $sets[] = '`' . str_replace('`','``',(string)$column) . '`=?';
                $params[] = $value;
            }
        }
        if (!$sets) return;
        $params = array_merge($params, $whereParams);
        $pdo->prepare('UPDATE `' . $physical . '` SET ' . implode(',', $sets) . ' WHERE ' . $where)->execute($params);
    }

    private static function registerDemoRecord(PDO $pdo, string $entityType, int $entityId, string $externalKey): void
    {
        if ($entityId < 1 || !schema_table_exists(table('demo_record_registry'))) return;
        try {
            $registry = table('demo_record_registry');
            if (schema_column_exists($registry, 'branch_id')) {
                $stmt = $pdo->prepare('INSERT INTO `' . $registry . '` (branch_id,batch_id,entity_type,entity_id,external_key,created_at) VALUES (?,NULL,?,?,?,NOW()) ON DUPLICATE KEY UPDATE external_key=VALUES(external_key)');
                $stmt->execute([self::defaultBranchId($pdo) ?: null, $entityType, $entityId, $externalKey]);
            } else {
                $stmt = $pdo->prepare('INSERT INTO `' . $registry . '` (batch_id,entity_type,entity_id,external_key,created_at) VALUES (NULL,?,?,?,NOW()) ON DUPLICATE KEY UPDATE external_key=VALUES(external_key)');
                $stmt->execute([$entityType, $entityId, $externalKey]);
            }
        } catch (Throwable) {}
    }


    public static function orientationModeEnabled(): bool
    {
        try {
            return (string) setting_get('system', 'orientation_mode_enabled', '0') === '1';
        } catch (Throwable) {
            return false;
        }
    }

    public static function setOrientationMode(PDO $pdo, bool $enabled): void
    {
        if ($enabled) {
            $previous = (string) setting_get('system', 'orientation_previous_email_queue_enabled', '');
            if ($previous === '') {
                setting_set('system', 'orientation_previous_email_queue_enabled', (string) setting_get('email_queue', 'automatic_enabled', '1'));
            }
            setting_set('system', 'orientation_mode_enabled', '1');
            setting_set('email_queue', 'automatic_enabled', '0');
            setting_set('system', 'orientation_mode_enabled_at', date('Y-m-d H:i:s'));
            setting_set('system', 'orientation_mode_enabled_by', (string)(current_user()['id'] ?? 0));
            audit('orientation_mode_enabled', 'system', null, ['email_queue_paused' => true]);
            return;
        }
        $restore = (string) setting_get('system', 'orientation_previous_email_queue_enabled', '');
        if ($restore !== '') {
            setting_set('email_queue', 'automatic_enabled', in_array($restore, ['0','1'], true) ? $restore : '1');
        }
        setting_set('system', 'orientation_mode_enabled', '0');
        setting_set('system', 'orientation_mode_disabled_at', date('Y-m-d H:i:s'));
        setting_set('system', 'orientation_previous_email_queue_enabled', '');
        audit('orientation_mode_disabled', 'system', null, ['email_queue_restored' => $restore !== '']);
    }

    public static function createFullDemoClinic(PDO $pdo): array
    {
        $result = [
            'accounts' => null,
            'patients' => null,
            'clinic_profile' => 0,
            'hours' => 0,
            'chairs' => 0,
            'services' => 0,
            'inventory_items' => 0,
            'inventory_lots' => 0,
            'sterilization_records' => 0,
            'clinical_samples' => 0,
            'warnings' => [],
        ];

        $result['accounts'] = self::createOrRefresh($pdo, true, true);
        $result['patients'] = self::createOrRefreshPatients($pdo, true);

        $pdo->beginTransaction();
        try {
            $branchId = self::defaultBranchId($pdo);
            $result['clinic_profile'] = self::ensureDemoClinicProfile($pdo, $branchId, $result['warnings']);
            $result['hours'] = self::ensureDemoOperatingHours($pdo, $branchId, $result['warnings']);
            $result['chairs'] = self::ensureAdditionalDemoChairs($pdo, $branchId, $result['warnings']);
            $services = self::ensureDemoServices($pdo, $branchId, $result['warnings']);
            $result['services'] = count(array_filter($services, static fn(array $service): bool => (int)($service['id'] ?? 0) > 0));
            $inventory = self::ensureFullDemoInventory($pdo, $branchId, $result['warnings']);
            $result['inventory_items'] = (int)($inventory['items'] ?? 0);
            $result['inventory_lots'] = (int)($inventory['lots'] ?? 0);
            $result['sterilization_records'] = self::ensureDemoSterilization($pdo, $branchId, $result['warnings']);
            $result['clinical_samples'] = self::ensureDemoClinicalSamples($pdo, $branchId, $result['warnings']);
            setting_set('system', 'full_demo_clinic_dataset_ready', '1');
            setting_set('system', 'full_demo_clinic_dataset_created_at', date('Y-m-d H:i:s'));
            setting_set('system', 'orientation_mode_foundation_ready', '1');
            $auditResult = $result;
            if (is_array($auditResult['accounts'] ?? null)) {
                $auditResult['accounts'] = self::auditSafeAccountResult((array)$auditResult['accounts']);
            }
            audit('full_demo_clinic_dataset_seeded', 'system', null, $auditResult);
            $pdo->commit();
        } catch (Throwable $exception) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $exception;
        }
        return $result;
    }

    private static function ensureDemoClinicProfile(PDO $pdo, int $branchId, array &$warnings): int
    {
        setting_set('clinic', 'phone', '0917-555-0123');
        setting_set('clinic', 'public_email', 'demo.clinic@mborrega-dentalcare.local');
        setting_set('clinic', 'address', '2F Demo Health Building, Rizal Street, Legazpi City, Albay');
        setting_set('clinic', 'default_dentist', 'Dr. Demo Santos');
        setting_set('clinic', 'default_chair', 'Demo Chair 1');
        setting_set('clinic', 'public_tagline', 'Fictional training clinic for DentalCare orientation');
        setting_set('clinic', 'public_intro', 'This is a fictional demo clinic profile used to show appointment intake, front desk workflow, treatment, billing, inventory, and reports. Replace all demo details before live use.');
        setting_set('clinic', 'public_hours', "Monday-Friday: 8:00 AM-5:00 PM\nSaturday: 8:00 AM-12:00 PM\nSunday: Closed");
        setting_set('clinic', 'public_services', "Demo Dental Consultation\nDemo Oral Prophylaxis\nDemo Tooth Restoration\nDemo Tooth Extraction\nDemo Root Canal Consultation\nDemo Orthodontic Consultation\nDemo Blood Pressure Check");
        setting_set('clinic', 'public_appointment_note', 'Demo appointment requests are fictional. During real operation, every submitted request must be reviewed and confirmed by the clinic.');
        if ($branchId > 0 && schema_table_exists(table('branches'))) {
            try {
                $stmt = $pdo->prepare('SELECT code,name FROM `' . table('branches') . '` WHERE id=? LIMIT 1');
                $stmt->execute([$branchId]);
                $branch = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
                if ((string)($branch['code'] ?? '') === 'DEMO' || stripos((string)($branch['name'] ?? ''), 'demo') !== false) {
                    self::updateRecord($pdo, 'branches', [
                        'name' => 'MBorrega DentalCare Demo Clinic',
                        'code' => 'DEMO',
                        'email' => 'demo.clinic@mborrega-dentalcare.local',
                        'phone' => '0917-555-0123',
                        'address' => '2F Demo Health Building, Rizal Street, Legazpi City, Albay',
                        'timezone' => 'Asia/Manila',
                        'is_active' => 1,
                        'updated_at' => date('Y-m-d H:i:s'),
                    ], 'id=?', [$branchId]);
                }
            } catch (Throwable $exception) {
                $warnings[] = 'Demo branch profile could not be refreshed: ' . $exception->getMessage();
            }
        }
        return 1;
    }

    private static function ensureDemoOperatingHours(PDO $pdo, int $branchId, array &$warnings): int
    {
        if ($branchId < 1 || !schema_table_exists(table('branch_operating_hours'))) {
            $warnings[] = 'Operating hours table is unavailable; setup wizard will still show the hours from clinic settings.';
            return 0;
        }
        $days = [
            1 => ['08:00:00','17:00:00',1],
            2 => ['08:00:00','17:00:00',1],
            3 => ['08:00:00','17:00:00',1],
            4 => ['08:00:00','17:00:00',1],
            5 => ['08:00:00','17:00:00',1],
            6 => ['08:00:00','12:00:00',1],
            0 => [null,null,0],
        ];
        $count = 0;
        foreach ($days as $day => [$open, $close, $isOpen]) {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('branch_operating_hours') . '` WHERE branch_id=? AND day_of_week=? LIMIT 1');
            $stmt->execute([$branchId, $day]);
            $id = (int)$stmt->fetchColumn();
            $values = ['branch_id'=>$branchId,'day_of_week'=>$day,'is_open'=>$isOpen,'open_time'=>$open,'close_time'=>$close,'slot_interval_minutes'=>30,'updated_at'=>date('Y-m-d H:i:s')];
            if ($id > 0) {
                self::updateRecord($pdo, 'branch_operating_hours', $values, 'id=?', [$id]);
            } else {
                $values['created_at'] = date('Y-m-d H:i:s');
                $id = self::insertRecord($pdo, 'branch_operating_hours', $values);
                self::registerDemoRecord($pdo, 'branch_operating_hours', $id, 'DEMO-HOURS-' . $day);
            }
            $count++;
        }
        return $count;
    }

    private static function ensureAdditionalDemoChairs(PDO $pdo, int $branchId, array &$warnings): int
    {
        if ($branchId < 1 || !schema_table_exists(table('clinic_chairs'))) return 0;
        $templates = [
            ['DEMO-1','Demo Chair 1','Demo Operatory 1','dentist_bound','available'],
            ['DEMO-2','Demo Chair 2','Demo Operatory 2','shared','available'],
            ['DEMO-XR','Demo Imaging / Prep Bay','Demo Imaging Area','shared','available'],
        ];
        $count = 0;
        foreach ($templates as [$code,$name,$room,$mode,$status]) {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('clinic_chairs') . '` WHERE branch_id=? AND chair_code=? LIMIT 1');
            $stmt->execute([$branchId, $code]);
            $id = (int)$stmt->fetchColumn();
            $values = ['branch_id'=>$branchId,'chair_code'=>$code,'chair_name'=>$name,'room_name'=>$room,'usage_mode'=>$mode,'status'=>$status,'notes'=>'Fictional demo resource for client orientation only.','created_by'=>(int)(current_user()['id'] ?? 0) ?: null,'updated_at'=>date('Y-m-d H:i:s')];
            if ($id > 0) {
                self::updateRecord($pdo, 'clinic_chairs', $values, 'id=?', [$id]);
            } else {
                $values['created_at'] = date('Y-m-d H:i:s');
                $id = self::insertRecord($pdo, 'clinic_chairs', $values);
                self::registerDemoRecord($pdo, 'clinic_chair', $id, $code);
            }
            $count++;
        }
        return $count;
    }

    private static function ensureFullDemoInventory(PDO $pdo, int $branchId, array &$warnings): array
    {
        $result = ['items'=>0,'lots'=>0,'transactions'=>0];
        if (!schema_table_exists(table('inventory_items'))) {
            $warnings[] = 'Inventory item table is unavailable; inventory demo records were skipped.';
            return $result;
        }
        $items = [
            ['DEMO-GLOVES-M','Demo Nitrile Gloves Medium','PPE','box',350,12,8,24],
            ['DEMO-MASKS','Demo Surgical Masks','PPE','box',180,6,10,40],
            ['DEMO-COMP-A2','Demo Composite Resin A2','Restorative','syringe',950,180,3,12],
            ['DEMO-ANES-LIDO','Demo Lidocaine Cartridge','Anesthesia','cartridge',48,48,20,80],
            ['DEMO-GAUZE','Demo Sterile Gauze Pack','Consumable','pack',80,8,15,60],
            ['DEMO-POUCH','Demo Sterilization Pouch','Sterilization','pack',420,14,10,30],
        ];
        foreach ($items as [$sku,$name,$category,$unit,$purchase,$usage,$reorder,$stock]) {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('inventory_items') . '` WHERE sku=? LIMIT 1');
            $stmt->execute([$sku]);
            $id = (int)$stmt->fetchColumn();
            $values = ['branch_id'=>$branchId ?: null,'sku'=>$sku,'name'=>$name,'category'=>$category,'unit'=>$unit,'supplier_name'=>'Demo Dental Supply Co.','purchase_cost'=>$purchase,'usage_cost'=>$usage,'reorder_level'=>$reorder,'current_stock'=>$stock,'status'=>'active','updated_at'=>date('Y-m-d H:i:s')];
            if ($id > 0) {
                self::updateRecord($pdo, 'inventory_items', $values, 'id=?', [$id]);
            } else {
                $values['created_at'] = date('Y-m-d H:i:s');
                $id = self::insertRecord($pdo, 'inventory_items', $values);
                self::registerDemoRecord($pdo, 'inventory_item', $id, $sku);
            }
            if ($id > 0) {
                $result['items']++;
                if (schema_table_exists(table('inventory_lots'))) {
                    $lotNo = $sku . '-LOT-2026';
                    $lotStmt = $pdo->prepare('SELECT id FROM `' . table('inventory_lots') . '` WHERE item_id=? AND lot_number=? LIMIT 1');
                    $lotStmt->execute([$id, $lotNo]);
                    $lotId = (int)$lotStmt->fetchColumn();
                    $lotValues = ['item_id'=>$id,'lot_number'=>$lotNo,'expiry_date'=>date('Y-m-d', strtotime('+18 months')),'quantity_received'=>$stock,'quantity_remaining'=>$stock,'received_at'=>date('Y-m-d', strtotime('-14 days')),'status'=>'active'];
                    if ($lotId > 0) self::updateRecord($pdo, 'inventory_lots', $lotValues, 'id=?', [$lotId]);
                    else { $lotValues['created_at']=date('Y-m-d H:i:s'); $lotId=self::insertRecord($pdo, 'inventory_lots', $lotValues); self::registerDemoRecord($pdo, 'inventory_lot', $lotId, $lotNo); }
                    if ($lotId > 0) $result['lots']++;
                    if (schema_table_exists(table('inventory_transactions'))) {
                        $ref = 'DEMO-STOCK-' . $sku;
                        $txStmt = $pdo->prepare('SELECT id FROM `' . table('inventory_transactions') . '` WHERE reference_type="demo_seed" AND reference_id=? LIMIT 1');
                        $txStmt->execute([$ref]);
                        $txId = (int)$txStmt->fetchColumn();
                        $txValues = ['item_id'=>$id,'lot_id'=>$lotId ?: null,'branch_id'=>$branchId ?: null,'transaction_type'=>'purchase','quantity'=>$stock,'unit_cost'=>$purchase,'reference_type'=>'demo_seed','reference_id'=>$ref,'notes'=>'Fictional opening stock for client orientation.','user_id'=>(int)(current_user()['id'] ?? 0) ?: null,'created_at'=>date('Y-m-d H:i:s')];
                        if ($txId > 0) self::updateRecord($pdo, 'inventory_transactions', $txValues, 'id=?', [$txId]);
                        else { $txId=self::insertRecord($pdo, 'inventory_transactions', $txValues); self::registerDemoRecord($pdo, 'inventory_transaction', $txId, $ref); }
                        if ($txId > 0) $result['transactions']++;
                    }
                }
                if ($branchId > 0 && schema_table_exists(table('inventory_branch_stocks'))) {
                    $stockStmt = $pdo->prepare('SELECT id FROM `' . table('inventory_branch_stocks') . '` WHERE item_id=? AND branch_id=? LIMIT 1');
                    $stockStmt->execute([$id, $branchId]);
                    $stockId = (int)$stockStmt->fetchColumn();
                    $stockValues = ['item_id'=>$id,'branch_id'=>$branchId,'current_stock'=>$stock,'reorder_level'=>$reorder,'last_reconciled_at'=>date('Y-m-d H:i:s'),'updated_at'=>date('Y-m-d H:i:s')];
                    if ($stockId > 0) self::updateRecord($pdo, 'inventory_branch_stocks', $stockValues, 'id=?', [$stockId]);
                    else { $stockId=self::insertRecord($pdo, 'inventory_branch_stocks', $stockValues); self::registerDemoRecord($pdo, 'inventory_branch_stock', $stockId, $sku . '-BRANCH'); }
                }
            }
        }
        return $result;
    }

    private static function ensureDemoSterilization(PDO $pdo, int $branchId, array &$warnings): int
    {
        if ($branchId < 1 || !schema_table_exists(table('instrument_sets'))) {
            $warnings[] = 'Instrument-set table is unavailable; sterilization demo records were skipped.';
            return 0;
        }
        $count = 0;
        $operatorId = self::demoUserIdByEmail($pdo, 'demo.assistant@mborrega-dentalcare.local') ?: (int)(current_user()['id'] ?? 0);
        $equipmentId = 0;
        if (schema_table_exists(table('sterilization_equipment'))) {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('sterilization_equipment') . '` WHERE branch_id=? AND name=? LIMIT 1');
            $stmt->execute([$branchId, 'Demo Autoclave A']);
            $equipmentId = (int)$stmt->fetchColumn();
            $values = ['branch_id'=>$branchId,'name'=>'Demo Autoclave A','serial_number'=>'DEMO-AUTO-001','manufacturer'=>'DemoMed','model'=>'SterilePro Demo','last_service_date'=>date('Y-m-d', strtotime('-30 days')),'next_service_date'=>date('Y-m-d', strtotime('+11 months')),'status'=>'active','created_by'=>(int)(current_user()['id'] ?? 0) ?: null,'updated_at'=>date('Y-m-d H:i:s')];
            if ($equipmentId > 0) self::updateRecord($pdo, 'sterilization_equipment', $values, 'id=?', [$equipmentId]);
            else { $values['created_at']=date('Y-m-d H:i:s'); $equipmentId=self::insertRecord($pdo, 'sterilization_equipment', $values); self::registerDemoRecord($pdo, 'sterilization_equipment', $equipmentId, 'DEMO-AUTO-001'); }
            if ($equipmentId > 0) $count++;
        }
        $setIds = [];
        foreach ([['DEMO-REST-SET','Demo Restoration Set','Mouth mirror, explorer, cotton pliers, composite instruments'],['DEMO-PROPHY-SET','Demo Prophylaxis Set','Mouth mirror, scaler, probe, polishing angle']] as [$code,$name,$contents]) {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('instrument_sets') . '` WHERE branch_id=? AND set_code=? LIMIT 1');
            $stmt->execute([$branchId, $code]);
            $setId = (int)$stmt->fetchColumn();
            $values = ['branch_id'=>$branchId,'set_code'=>$code,'name'=>$name,'contents'=>$contents,'status'=>'sterile','prepared_by'=>$operatorId ?: null,'prepared_at'=>date('Y-m-d H:i:s', strtotime('-2 hours')),'updated_at'=>date('Y-m-d H:i:s'),'created_by'=>(int)(current_user()['id'] ?? 0) ?: null];
            if ($setId > 0) self::updateRecord($pdo, 'instrument_sets', $values, 'id=?', [$setId]);
            else { $values['created_at']=date('Y-m-d H:i:s'); $setId=self::insertRecord($pdo, 'instrument_sets', $values); self::registerDemoRecord($pdo, 'instrument_set', $setId, $code); }
            if ($setId > 0) { $setIds[] = $setId; $count++; }
        }
        if (schema_table_exists(table('sterilization_batches'))) {
            $cycleNo = 'DEMO-CYCLE-' . date('Ymd');
            $stmt = $pdo->prepare('SELECT id FROM `' . table('sterilization_batches') . '` WHERE cycle_no=? LIMIT 1');
            $stmt->execute([$cycleNo]);
            $batchId = (int)$stmt->fetchColumn();
            $values = ['branch_id'=>$branchId,'sterilizer_name'=>'Demo Autoclave A','cycle_no'=>$cycleNo,'load_no'=>'DEMO-LOAD-01','cycle_started_at'=>date('Y-m-d H:i:s', strtotime('-90 minutes')),'cycle_completed_at'=>date('Y-m-d H:i:s', strtotime('-45 minutes')),'operator_user_id'=>$operatorId ?: null,'indicator_result'=>'pass','biological_result'=>'not_required_demo','release_status'=>'released','notes'=>'Fictional released sterilization cycle for orientation.','created_at'=>date('Y-m-d H:i:s')];
            if ($batchId > 0) self::updateRecord($pdo, 'sterilization_batches', $values, 'id=?', [$batchId]);
            else { $batchId=self::insertRecord($pdo, 'sterilization_batches', $values); self::registerDemoRecord($pdo, 'sterilization_batch', $batchId, $cycleNo); }
            if ($batchId > 0) {
                $count++;
                foreach ($setIds as $setId) {
                    self::updateRecord($pdo, 'instrument_sets', ['last_batch_id'=>$batchId,'status'=>'sterile','updated_at'=>date('Y-m-d H:i:s')], 'id=?', [$setId]);
                    if (schema_table_exists(table('sterilization_batch_sets'))) {
                        $stmt = $pdo->prepare('SELECT id FROM `' . table('sterilization_batch_sets') . '` WHERE batch_id=? AND instrument_set_id=? LIMIT 1');
                        $stmt->execute([$batchId, $setId]);
                        $linkId = (int)$stmt->fetchColumn();
                        if ($linkId < 1) { $linkId=self::insertRecord($pdo, 'sterilization_batch_sets', ['batch_id'=>$batchId,'instrument_set_id'=>$setId,'loaded_by'=>$operatorId ?: null,'loaded_at'=>date('Y-m-d H:i:s', strtotime('-90 minutes'))]); self::registerDemoRecord($pdo, 'sterilization_batch_set', $linkId, $cycleNo . '-' . $setId); }
                    }
                }
                if (schema_table_exists(table('sterilization_batch_events'))) {
                    $eventKey = 'DEMO-STER-EVENT-' . $cycleNo;
                    $stmt = $pdo->prepare('SELECT id FROM `' . table('sterilization_batch_events') . '` WHERE batch_id=? AND notes LIKE ? LIMIT 1');
                    $stmt->execute([$batchId, '%' . $eventKey . '%']);
                    $eventId = (int)$stmt->fetchColumn();
                    $eventValues = ['batch_id'=>$batchId,'previous_status'=>'pending','new_status'=>'released','event_data_json'=>json_encode(['demo'=>true,'indicator'=>'pass']),'notes'=>'Fictional sterilization release event. ' . $eventKey,'changed_by'=>$operatorId ?: null,'created_at'=>date('Y-m-d H:i:s')];
                    if ($eventId > 0) self::updateRecord($pdo, 'sterilization_batch_events', $eventValues, 'id=?', [$eventId]);
                    else { $eventId=self::insertRecord($pdo, 'sterilization_batch_events', $eventValues); self::registerDemoRecord($pdo, 'sterilization_batch_event', $eventId, $eventKey); }
                }
            }
        }
        return $count;
    }

    private static function ensureDemoClinicalSamples(PDO $pdo, int $branchId, array &$warnings): int
    {
        $patientMap = self::patientIdMap($pdo);
        $patientId = (int)($patientMap['DEMO-PAT-004'] ?? 0);
        if ($patientId < 1) return 0;
        $dentistId = self::demoDentistUserId($pdo) ?: self::anyDentistUserId($pdo);
        $created = 0;
        if (schema_table_exists(table('prescriptions'))) {
            $rxNo = 'DEMO-RX-2026-001';
            $stmt = $pdo->prepare('SELECT id FROM `' . table('prescriptions') . '` WHERE prescription_no=? LIMIT 1');
            $stmt->execute([$rxNo]);
            $rxId = (int)$stmt->fetchColumn();
            $values = ['patient_id'=>$patientId,'dentist_user_id'=>$dentistId ?: null,'prescription_no'=>$rxNo,'issued_at'=>date('Y-m-d H:i:s', strtotime('-7 days')),'status'=>'issued','notes'=>'Fictional prescription sample for orientation only.','created_at'=>date('Y-m-d H:i:s', strtotime('-7 days'))];
            if ($rxId > 0) self::updateRecord($pdo, 'prescriptions', $values, 'id=?', [$rxId]);
            else { $rxId=self::insertRecord($pdo, 'prescriptions', $values); self::registerDemoRecord($pdo, 'prescription', $rxId, $rxNo); }
            if ($rxId > 0) {
                $created++;
                if (schema_table_exists(table('prescription_items'))) {
                    $stmt = $pdo->prepare('SELECT id FROM `' . table('prescription_items') . '` WHERE prescription_id=? AND medication_name=? LIMIT 1');
                    $stmt->execute([$rxId, 'Demo Ibuprofen']);
                    if ((int)$stmt->fetchColumn() < 1) {
                        $itemId = self::insertRecord($pdo, 'prescription_items', ['prescription_id'=>$rxId,'medication_name'=>'Demo Ibuprofen','generic_name'=>'Ibuprofen','strength'=>'400 mg','dosage'=>'1 tablet','frequency'=>'Every 8 hours as needed','route'=>'oral','duration_days'=>3,'quantity'=>'9 tablets','instructions'=>'Fictional medication instruction for documentation demo only.']);
                        self::registerDemoRecord($pdo, 'prescription_item', $itemId, $rxNo . '-ITEM');
                    }
                }
            }
        }
        if (schema_table_exists(table('follow_ups'))) {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('follow_ups') . '` WHERE patient_id=? AND purpose=? LIMIT 1');
            $stmt->execute([$patientId, 'Demo restoration follow-up']);
            $followId = (int)$stmt->fetchColumn();
            $values = ['patient_id'=>$patientId,'dentist_user_id'=>$dentistId ?: null,'purpose'=>'Demo restoration follow-up','recommended_date'=>date('Y-m-d', strtotime('+14 days')),'maximum_date'=>date('Y-m-d', strtotime('+21 days')),'urgency'=>'routine','status'=>'pending','instructions'=>'Fictional follow-up used for orientation checklist.','created_at'=>date('Y-m-d H:i:s')];
            if ($followId > 0) self::updateRecord($pdo, 'follow_ups', $values, 'id=?', [$followId]);
            else { $followId=self::insertRecord($pdo, 'follow_ups', $values); self::registerDemoRecord($pdo, 'follow_up', $followId, 'DEMO-FOLLOW-001'); }
            if ($followId > 0) $created++;
        }
        if (schema_table_exists(table('recalls'))) {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('recalls') . '` WHERE patient_id=? AND recall_type=? LIMIT 1');
            $stmt->execute([$patientId, 'Demo six-month recall']);
            $recallId = (int)$stmt->fetchColumn();
            $values = ['patient_id'=>$patientId,'recall_type'=>'Demo six-month recall','due_date'=>date('Y-m-d', strtotime('+6 months')),'status'=>'future','notes'=>'Fictional recall reminder sample.','created_at'=>date('Y-m-d H:i:s')];
            if ($recallId > 0) self::updateRecord($pdo, 'recalls', $values, 'id=?', [$recallId]);
            else { $recallId=self::insertRecord($pdo, 'recalls', $values); self::registerDemoRecord($pdo, 'recall', $recallId, 'DEMO-RECALL-001'); }
            if ($recallId > 0) $created++;
        }
        if (schema_table_exists(table('communications'))) {
            $key = 'DEMO-COMM-001';
            $stmt = $pdo->prepare('SELECT id FROM `' . table('communications') . '` WHERE summary LIKE ? LIMIT 1');
            $stmt->execute(['%' . $key . '%']);
            $commId = (int)$stmt->fetchColumn();
            $values = ['patient_id'=>$patientId,'user_id'=>(int)(current_user()['id'] ?? 0) ?: null,'channel'=>'phone','direction'=>'outbound','recipient'=>'0917-555-0104','subject'=>'Demo follow-up call','summary'=>'Fictional follow-up call note for orientation. ' . $key,'delivery_status'=>'logged','created_at'=>date('Y-m-d H:i:s')];
            if ($commId > 0) self::updateRecord($pdo, 'communications', $values, 'id=?', [$commId]);
            else { $commId=self::insertRecord($pdo, 'communications', $values); self::registerDemoRecord($pdo, 'communication', $commId, $key); }
            if ($commId > 0) $created++;
        }
        return $created;
    }

    private static function demoUserIdByEmail(PDO $pdo, string $email): int
    {
        try {
            $stmt = $pdo->prepare('SELECT id FROM `' . table('users') . '` WHERE email=? AND is_demo=1 LIMIT 1');
            $stmt->execute([$email]);
            return (int)$stmt->fetchColumn();
        } catch (Throwable) {
            return 0;
        }
    }

}
