<?php

declare(strict_types=1);

require_once DENTAL_ROOT . '/app/transaction_integrity.php';

final class DataImportService
{
    private const MAX_UPLOAD_BYTES = 25 * 1024 * 1024;

    private const DEFINITIONS = [
        'staff' => [
            'label' => 'Staff',
            'required' => ['employee_id','first_name','last_name','role_code','email'],
        ],
        'dentists' => [
            'label' => 'Dentists',
            'required' => ['employee_id','first_name','last_name','email','specialty'],
        ],
        'returning_patients' => [
            'label' => 'Returning Patients',
            'required' => ['patient_number','first_name','last_name','date_of_birth'],
        ],
        'new_patients' => [
            'label' => 'New Patient Requests',
            'required' => ['request_reference','first_name','last_name','date_of_birth','requested_service','preferred_date','preferred_time'],
        ],
    ];

    public static function storageDirectory(): string
    {
        $path = DENTAL_ROOT . '/storage/private/imports';
        if (!is_dir($path) && !mkdir($path, 0750, true) && !is_dir($path)) {
            throw new RuntimeException('Unable to create protected import storage.');
        }
        if (!is_writable($path)) {
            throw new RuntimeException('The protected import directory is not writable.');
        }
        return $path;
    }

    public static function storeUpload(array $file): array
    {
        if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
            throw new RuntimeException('Choose an Excel, CSV, or ZIP data file to upload.');
        }
        $size = (int) ($file['size'] ?? 0);
        if ($size <= 0 || $size > self::MAX_UPLOAD_BYTES) {
            throw new RuntimeException('Import files must be between 1 byte and 25 MB.');
        }
        $original = basename((string) ($file['name'] ?? 'import'));
        $extension = strtolower(pathinfo($original, PATHINFO_EXTENSION));
        if (!in_array($extension, ['xlsx','csv','zip'], true)) {
            throw new RuntimeException('Only .xlsx, .csv, and .zip import files are accepted.');
        }
        $stored = 'import_' . date('Ymd_His') . '_' . bin2hex(random_bytes(5)) . '.' . $extension;
        $path = self::storageDirectory() . '/' . $stored;
        if (!move_uploaded_file((string) $file['tmp_name'], $path)) {
            throw new RuntimeException('The import file could not be stored. Check storage/private permissions.');
        }
        @chmod($path, 0640);
        return ['original' => $original, 'stored' => $stored, 'extension' => $extension, 'path' => $path];
    }

    public static function storedPath(string $storedName): string
    {
        $storedName = basename($storedName);
        $path = self::storageDirectory() . '/' . $storedName;
        if (!is_file($path)) {
            throw new RuntimeException('The stored import file is missing. Upload it again.');
        }
        return $path;
    }

    public static function parse(string $path, string $originalName): array
    {
        $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
        $datasets = match ($extension) {
            'xlsx' => self::parseXlsx($path),
            'zip' => self::parseZip($path),
            'csv' => self::parseSingleCsv($path, $originalName),
            default => throw new RuntimeException('Unsupported import format.'),
        };
        if (!$datasets) {
            throw new RuntimeException('No recognized DentalCare dataset was found in the uploaded file.');
        }
        return self::validate($datasets);
    }

    private static function parseSingleCsv(string $path, string $name): array
    {
        $content = file_get_contents($path);
        if ($content === false) throw new RuntimeException('Unable to read the CSV file.');
        $rows = self::parseCsvContent($content);
        $dataset = self::detectDataset($name, $rows[0] ?? []);
        if ($dataset === null) throw new RuntimeException('The CSV columns do not match Staff, Dentists, Returning Patients, or New Patients.');
        return [$dataset => self::rowsToAssociative($rows)];
    }

    private static function parseZip(string $path): array
    {
        if (!class_exists(ZipArchive::class)) throw new RuntimeException('The PHP ZIP extension is required to read the import pack.');
        $zip = new ZipArchive();
        if ($zip->open($path) !== true) throw new RuntimeException('The ZIP import pack could not be opened.');
        $datasets = [];
        for ($i = 0; $i < $zip->numFiles; $i++) {
            $name = (string) $zip->getNameIndex($i);
            if (strtolower(pathinfo($name, PATHINFO_EXTENSION)) !== 'csv') continue;
            $content = $zip->getFromIndex($i);
            if (!is_string($content)) continue;
            $rows = self::parseCsvContent($content);
            $dataset = self::detectDataset($name, $rows[0] ?? []);
            if ($dataset !== null) $datasets[$dataset] = self::rowsToAssociative($rows);
        }
        $zip->close();
        return $datasets;
    }

    private static function parseCsvContent(string $content): array
    {
        $content = preg_replace('/^\xEF\xBB\xBF/', '', $content) ?? $content;
        $stream = fopen('php://temp', 'w+b');
        if (!$stream) throw new RuntimeException('Unable to prepare the CSV parser.');
        fwrite($stream, $content);
        rewind($stream);
        $rows = [];
        while (($row = fgetcsv($stream)) !== false) {
            if (count($row) === 1 && trim((string) $row[0]) === '') continue;
            $rows[] = array_map(static fn($value): string => trim((string) $value), $row);
        }
        fclose($stream);
        return $rows;
    }

    private static function parseXlsx(string $path): array
    {
        if (!class_exists(ZipArchive::class)) throw new RuntimeException('The PHP ZIP extension is required to read Excel files.');
        if (!class_exists(DOMDocument::class)) throw new RuntimeException('The PHP DOM extension is required to read Excel files.');
        $zip = new ZipArchive();
        if ($zip->open($path) !== true) throw new RuntimeException('The Excel workbook could not be opened.');
        $workbookXml = $zip->getFromName('xl/workbook.xml');
        $relsXml = $zip->getFromName('xl/_rels/workbook.xml.rels');
        if (!is_string($workbookXml) || !is_string($relsXml)) {
            $zip->close();
            throw new RuntimeException('The Excel workbook structure is incomplete.');
        }
        $relationships = [];
        $rels = new DOMDocument();
        $rels->loadXML($relsXml, LIBXML_NONET | LIBXML_COMPACT);
        foreach ($rels->getElementsByTagName('Relationship') as $node) {
            $relationships[$node->getAttribute('Id')] = ltrim($node->getAttribute('Target'), '/');
        }
        $shared = [];
        $sharedXml = $zip->getFromName('xl/sharedStrings.xml');
        if (is_string($sharedXml)) {
            $doc = new DOMDocument();
            if ($doc->loadXML($sharedXml, LIBXML_NONET | LIBXML_COMPACT)) {
                foreach ($doc->getElementsByTagName('si') as $si) {
                    $parts = [];
                    foreach ($si->getElementsByTagName('t') as $textNode) $parts[] = $textNode->textContent;
                    $shared[] = implode('', $parts);
                }
            }
        }
        $workbook = new DOMDocument();
        $workbook->loadXML($workbookXml, LIBXML_NONET | LIBXML_COMPACT);
        $xpath = new DOMXPath($workbook);
        $xpath->registerNamespace('x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $xpath->registerNamespace('r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
        $datasets = [];
        foreach ($xpath->query('//x:sheets/x:sheet') ?: [] as $sheetNode) {
            $sheetName = (string) $sheetNode->getAttribute('name');
            $relId = (string) $sheetNode->getAttributeNS('http://schemas.openxmlformats.org/officeDocument/2006/relationships', 'id');
            $target = $relationships[$relId] ?? '';
            if ($target === '') continue;
            if (!str_starts_with($target, 'xl/')) $target = 'xl/' . ltrim($target, '/');
            $sheetXml = $zip->getFromName($target);
            if (!is_string($sheetXml)) continue;
            $rows = self::parseXlsxSheet($sheetXml, $shared);
            $dataset = self::detectDataset($sheetName, $rows[0] ?? []);
            if ($dataset !== null) $datasets[$dataset] = self::rowsToAssociative($rows);
        }
        $zip->close();
        return $datasets;
    }

    private static function parseXlsxSheet(string $xml, array $shared): array
    {
        $doc = new DOMDocument();
        if (!$doc->loadXML($xml, LIBXML_NONET | LIBXML_COMPACT)) return [];
        $xpath = new DOMXPath($doc);
        $xpath->registerNamespace('x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $rows = [];
        foreach ($xpath->query('//x:sheetData/x:row') ?: [] as $rowNode) {
            $row = [];
            foreach ($xpath->query('./x:c', $rowNode) ?: [] as $cellNode) {
                $reference = (string) $cellNode->getAttribute('r');
                if (!preg_match('/^([A-Z]+)/', $reference, $match)) continue;
                $index = self::columnIndex($match[1]);
                $type = (string) $cellNode->getAttribute('t');
                $value = '';
                if ($type === 'inlineStr') {
                    $texts = [];
                    foreach ($xpath->query('.//x:is//x:t', $cellNode) ?: [] as $node) $texts[] = $node->textContent;
                    $value = implode('', $texts);
                } else {
                    $valueNode = $xpath->query('./x:v', $cellNode)?->item(0);
                    $raw = $valueNode?->textContent ?? '';
                    if ($type === 's') $value = $shared[(int) $raw] ?? '';
                    elseif ($type === 'b') $value = $raw === '1' ? '1' : '0';
                    else $value = $raw;
                }
                $row[$index] = trim($value);
            }
            if ($row) {
                ksort($row);
                $max = max(array_keys($row));
                $dense = [];
                for ($i = 0; $i <= $max; $i++) $dense[] = $row[$i] ?? '';
                $rows[] = $dense;
            }
        }
        return $rows;
    }

    private static function columnIndex(string $letters): int
    {
        $value = 0;
        foreach (str_split($letters) as $letter) $value = $value * 26 + (ord($letter) - 64);
        return $value - 1;
    }

    private static function rowsToAssociative(array $rows): array
    {
        if (!$rows) return [];
        $headers = array_map([self::class, 'normalizeHeader'], array_shift($rows));
        $result = [];
        foreach ($rows as $rowNumber => $row) {
            $record = ['_row' => $rowNumber + 2];
            foreach ($headers as $index => $header) {
                if ($header === '') continue;
                $value = trim((string) ($row[$index] ?? ''));
                if (in_array($header, ['date_of_birth','preferred_date','alternate_date','last_visit_date','recall_due_date','license_expiry'], true)) {
                    $value = self::normalizeDate($value);
                }
                $record[$header] = $value;
            }
            if (count(array_filter($record, static fn($v, $k): bool => $k !== '_row' && trim((string) $v) !== '', ARRAY_FILTER_USE_BOTH)) > 0) {
                $result[] = $record;
            }
        }
        return $result;
    }

    private static function normalizeHeader(string $header): string
    {
        $header = strtolower(trim($header));
        $header = preg_replace('/[^a-z0-9]+/', '_', $header) ?? $header;
        return trim($header, '_');
    }

    private static function detectDataset(string $name, array $headers): ?string
    {
        $name = strtolower(str_replace([' ','-'], '_', basename($name)));
        if (str_contains($name, 'returning_patient')) return 'returning_patients';
        if (str_contains($name, 'new_patient')) return 'new_patients';
        if (str_contains($name, 'dentist')) return 'dentists';
        if (str_contains($name, 'staff')) return 'staff';
        $headers = array_map([self::class, 'normalizeHeader'], $headers);
        if (in_array('request_reference', $headers, true)) return 'new_patients';
        if (in_array('patient_number', $headers, true)) return 'returning_patients';
        if (in_array('specialty', $headers, true) && in_array('clinical_privileges', $headers, true)) return 'dentists';
        if (in_array('role_code', $headers, true) && in_array('employee_id', $headers, true)) return 'staff';
        return null;
    }

    private static function validate(array $datasets): array
    {
        $report = ['datasets' => [], 'total_rows' => 0, 'errors' => [], 'warnings' => []];
        foreach ($datasets as $type => $rows) {
            if (!isset(self::DEFINITIONS[$type])) continue;
            $definition = self::DEFINITIONS[$type];
            $headers = $rows ? array_values(array_filter(array_keys($rows[0]), static fn($h): bool => $h !== '_row')) : [];
            $missing = array_values(array_diff($definition['required'], $headers));
            $errors = [];
            $warnings = [];
            if ($missing) $errors[] = 'Missing required columns: ' . implode(', ', $missing) . '.';
            foreach ($rows as $row) {
                foreach ($definition['required'] as $required) {
                    if (trim((string) ($row[$required] ?? '')) === '') {
                        $errors[] = 'Row ' . (int) ($row['_row'] ?? 0) . ': ' . $required . ' is required.';
                        break;
                    }
                }
                $email = trim((string) ($row['email'] ?? ''));
                if ($email !== '' && !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                    $errors[] = 'Row ' . (int) ($row['_row'] ?? 0) . ': email is invalid.';
                }
            }
            $report['datasets'][$type] = [
                'label' => $definition['label'],
                'row_count' => count($rows),
                'headers' => $headers,
                'errors' => array_values(array_unique($errors)),
                'warnings' => array_values(array_unique($warnings)),
                'sample' => array_slice($rows, 0, 5),
            ];
            $report['total_rows'] += count($rows);
            if ($errors) $report['errors'][] = $definition['label'] . ' requires correction.';
        }
        if (!$report['total_rows']) $report['errors'][] = 'The recognized datasets contain no records.';
        $report['valid'] = !$report['errors'];
        return ['datasets' => $datasets, 'report' => $report];
    }

    public static function import(PDO $pdo, array $datasets, int $batchId, array $selectedTypes): array
    {
        $order = ['staff','dentists','returning_patients','new_patients'];
        $result = ['imported' => [], 'skipped' => [], 'errors' => [], 'details' => []];
        foreach ($order as $type) {
            if (!in_array($type, $selectedTypes, true) || empty($datasets[$type])) continue;
            $result['imported'][$type] = 0;
            $result['skipped'][$type] = 0;
            foreach ($datasets[$type] as $row) {
                try {
                    $pdo->beginTransaction();
                    $message = match ($type) {
                        'staff' => self::importStaff($pdo, $row, $batchId),
                        'dentists' => self::importDentist($pdo, $row, $batchId),
                        'returning_patients' => self::importReturningPatient($pdo, $row, $batchId),
                        'new_patients' => self::importNewPatientRequest($pdo, $row, $batchId),
                        default => throw new RuntimeException('Unsupported dataset.'),
                    };
                    $pdo->commit();
                    $result['imported'][$type]++;
                    $result['details'][] = ['type'=>$type,'row'=>(int)($row['_row']??0),'status'=>'imported','message'=>$message];
                } catch (DuplicateDemoRecord $duplicate) {
                    if ($pdo->inTransaction()) $pdo->rollBack();
                    $result['skipped'][$type]++;
                    $result['details'][] = ['type'=>$type,'row'=>(int)($row['_row']??0),'status'=>'skipped','message'=>$duplicate->getMessage()];
                } catch (Throwable $exception) {
                    if ($pdo->inTransaction()) $pdo->rollBack();
                    $result['errors'][] = $type . ' row ' . (int)($row['_row']??0) . ': ' . $exception->getMessage();
                    $result['details'][] = ['type'=>$type,'row'=>(int)($row['_row']??0),'status'=>'failed','message'=>$exception->getMessage()];
                }
            }
        }
        return $result;
    }

    private static function importStaff(PDO $pdo, array $row, int $batchId): string
    {
        $roleCode = self::normalizeRole((string) ($row['role_code'] ?? 'staff'));
        if ($roleCode === 'super_admin') {
            throw new DuplicateDemoRecord('Demo Superuser row skipped. The real installation Superuser is preserved.');
        }
        $employeeNo = trim((string) ($row['employee_id'] ?? ''));
        $email = strtolower(trim((string) ($row['email'] ?? '')));
        if (self::userExists($pdo, $employeeNo, $email)) throw new DuplicateDemoRecord('Staff account already exists for ' . ($employeeNo ?: $email) . '.');
        $roleId = self::roleId($pdo, $roleCode);
        $branchId = self::branchId($pdo, (string) ($row['branch_name'] ?? ''));
        $name = self::fullName($row);
        $password = (string) ($row['temporary_password'] ?? 'DemoUser!2026');
        if (strlen($password) < 10) $password = 'DemoUser!2026';
        $status = strtolower((string) ($row['account_status'] ?? 'active')) === 'inactive' ? 'inactive' : 'active';
        $stmt = $pdo->prepare('INSERT INTO `' . table('users') . '` (branch_id,role_id,employee_no,name,email,password_hash,status,email_verified_at,must_change_password,is_demo,created_at) VALUES (?,?,?,?,?,?,?,NOW(),1,1,NOW())');
        $stmt->execute([$branchId,$roleId,$employeeNo?:null,$name,$email,password_hash($password,PASSWORD_DEFAULT),$status]);
        $userId = (int) $pdo->lastInsertId();
        self::registerDemo($pdo,$batchId,'user',$userId,$employeeNo ?: $email);
        return $name . ' created as ' . $roleCode . '.';
    }

    private static function importDentist(PDO $pdo, array $row, int $batchId): string
    {
        $employeeNo = trim((string) ($row['employee_id'] ?? ''));
        $email = strtolower(trim((string) ($row['email'] ?? '')));
        if (self::userExists($pdo, $employeeNo, $email)) throw new DuplicateDemoRecord('Dentist account already exists for ' . ($employeeNo ?: $email) . '.');
        $branchId = self::branchId($pdo, (string) ($row['branch_name'] ?? ''));
        $roleId = self::roleId($pdo, 'dentist');
        $name = self::fullName($row);
        $password = (string) ($row['temporary_password'] ?? 'DemoDentist!2026');
        if (strlen($password) < 10) $password = 'DemoDentist!2026';
        $stmt = $pdo->prepare('INSERT INTO `' . table('users') . '` (branch_id,role_id,employee_no,name,email,password_hash,status,email_verified_at,must_change_password,is_demo,created_at) VALUES (?,?,?,?,?,? ,"active",NOW(),1,1,NOW())');
        $stmt->execute([$branchId,$roleId,$employeeNo?:null,$name,$email,password_hash($password,PASSWORD_DEFAULT)]);
        $userId = (int) $pdo->lastInsertId();
        self::registerDemo($pdo,$batchId,'user',$userId,$employeeNo ?: $email);

        $specialtyId = self::specialtyId($pdo, (string) ($row['specialty'] ?? 'General Dentistry'), $batchId);
        $employment = self::employmentType((string) ($row['employment_type'] ?? 'Full-time'));
        $languages = self::splitList((string) ($row['languages'] ?? ''));
        $profile = $pdo->prepare('INSERT INTO `' . table('dentist_profiles') . '` (user_id,professional_title,display_name,specialty_id,subspecialty,license_number,license_expiry,years_in_practice,languages_json,public_biography,employment_type,primary_branch_id,default_chair_name,default_duration_minutes,maximum_daily_appointments,accepting_new_patients,public_profile_enabled,profile_status,created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"active",NOW())');
        $profile->execute([
            $userId,trim((string)($row['professional_title']??'Dr.')),$name,$specialtyId,trim((string)($row['subspecialty']??''))?:null,
            trim((string)($row['license_number']??''))?:null,self::normalizeDate((string)($row['license_expiry']??''))?:null,
            self::nullableInt($row['years_in_practice']??null),json_encode($languages,JSON_UNESCAPED_UNICODE),trim((string)($row['public_biography']??''))?:null,
            $employment,$branchId,trim((string)($row['default_chair']??''))?:null,max(15,(int)($row['default_duration_minutes']??30)),
            self::nullableInt($row['maximum_daily_appointments']??null),self::truthy($row['accepting_new_patients']??'1')?1:0,self::truthy($row['public_profile_enabled']??'0')?1:0,
        ]);
        $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]);

        foreach (self::splitList((string) ($row['services'] ?? '')) as $serviceName) {
            $serviceId = self::serviceId($pdo,$serviceName,$branchId,$batchId,(int)($row['default_duration_minutes']??30));
            $pdo->prepare('INSERT INTO `' . table('dentist_service_assignments') . '` (dentist_user_id,service_id,is_active,custom_duration_minutes,created_at) VALUES (?,?,1,NULL,NOW()) ON DUPLICATE KEY UPDATE is_active=1')->execute([$userId,$serviceId]);
        }
        foreach (self::splitList((string) ($row['clinical_privileges'] ?? '')) as $privilegeCode) {
            $privilegeCode = self::normalizePrivilege($privilegeCode);
            $stmt = $pdo->prepare('SELECT id FROM `' . table('clinical_privileges') . '` WHERE code=? AND is_active=1');
            $stmt->execute([$privilegeCode]);
            $privilegeId = (int) $stmt->fetchColumn();
            if ($privilegeId > 0) {
                $pdo->prepare('INSERT INTO `' . table('dentist_clinical_privileges') . '` (dentist_user_id,privilege_id,status,effective_from,approved_by,created_at) VALUES (?,? ,"granted",CURDATE(),?,NOW()) ON DUPLICATE KEY UPDATE status="granted"')->execute([$userId,$privilegeId,(int)(current_user()['id']??0)]);
            }
        }
        self::importAvailability($pdo,$userId,$branchId,(string)($row['weekly_availability']??''),$batchId);
        return $name . ' created with Doctor Profile, privileges, services, and availability.';
    }

    private static function importReturningPatient(PDO $pdo, array $row, int $batchId): string
    {
        $patientNo = trim((string) ($row['patient_number'] ?? ''));
        $first = trim((string) ($row['first_name'] ?? ''));
        $last = trim((string) ($row['last_name'] ?? ''));
        $birth = self::normalizeDate((string) ($row['date_of_birth'] ?? ''));
        $dup = $pdo->prepare('SELECT id FROM `' . table('patients') . '` WHERE patient_no=? OR (first_name=? AND last_name=? AND birth_date=?) LIMIT 1');
        $dup->execute([$patientNo,$first,$last,$birth]);
        if ((int) $dup->fetchColumn() > 0) throw new DuplicateDemoRecord('Patient already exists: ' . $patientNo . '.');
        $branchId = self::branchId($pdo, '');
        $alerts = self::medicalSummary($row);
        $status = strtolower((string)($row['patient_status']??'active')) === 'inactive' ? 'inactive' : 'active';
        $stmt = $pdo->prepare('INSERT INTO `' . table('patients') . '` (branch_id,patient_no,first_name,middle_name,last_name,birth_date,sex,mobile,email,address,medical_alerts,status,is_demo,created_by,created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,1,?,NOW())');
        $stmt->execute([$branchId,$patientNo,$first,trim((string)($row['middle_name']??''))?:null,$last,$birth,trim((string)($row['sex']??''))?:null,trim((string)($row['mobile']??''))?:null,strtolower(trim((string)($row['email']??'')))?:null,trim((string)($row['address']??''))?:null,null,$status,(int)(current_user()['id']??0)]);
        $patientId = (int) $pdo->lastInsertId();
        self::registerDemo($pdo,$batchId,'patient',$patientId,$patientNo);
        integrity_insert_reported_medical_history($pdo,$patientId,[
            'allergies'=>self::cleanNone($row['allergies']??''),
            'medications'=>self::cleanNone($row['current_medications']??''),
            'conditions'=>self::cleanNone($row['medical_conditions']??''),
            'dental_history'=>trim((string)($row['notes']??'')),
        ],'staff_recorded',(int)(current_user()['id']??0),'Imported record pending dentist verification.');
        $guardian = trim((string)($row['guardian_name']??''));
        if ($guardian !== '') {
            integrity_insert_pending_guardian($pdo,$patientId,[
                'name'=>$guardian,
                'relationship'=>trim((string)($row['guardian_relationship']??'')),
                'mobile'=>trim((string)($row['guardian_mobile']??'')),
                'email'=>trim((string)($row['guardian_email']??'')),
                'address'=>'',
            ]);
        }
        $dentistId = self::dentistByEmployeeNo($pdo,(string)($row['preferred_dentist_employee_id']??''));
        $dentistName = self::userName($pdo,$dentistId) ?: 'Imported historical dentist';
        $lastVisit = self::normalizeDate((string)($row['last_visit_date']??''));
        $lastProcedure = trim((string)($row['last_procedure']??''));
        if ($lastVisit !== '' && $lastProcedure !== '') {
            $pdo->prepare('INSERT INTO `' . table('clinical_encounters') . '` (branch_id,patient_id,dentist_user_id,dentist_name,chief_complaint,procedure_done,soap_note,status,created_at,finalized_at) VALUES (?,?,?,?,"Imported historical visit",?,"Imported demo history.","finalized",?,?)')->execute([$branchId,$patientId,$dentistId?:null,$dentistName,$lastProcedure,$lastVisit.' 09:00:00',$lastVisit.' 09:30:00']);
            $encounterId=(int)$pdo->lastInsertId();
            $pdo->prepare('INSERT INTO `' . table('procedures') . '` (patient_id,encounter_id,dentist_user_id,procedure_name,started_at,ended_at,outcome,status,created_at) VALUES (?,?,?,?,?,?,"Imported completed procedure","completed",?)')->execute([$patientId,$encounterId,$dentistId?:null,$lastProcedure,$lastVisit.' 09:00:00',$lastVisit.' 09:30:00',$lastVisit.' 09:30:00']);
        }
        $plan = trim((string)($row['active_treatment_plan']??''));
        if ($plan !== '' && strtolower($plan) !== 'completed') {
            $pdo->prepare('INSERT INTO `' . table('treatment_plans') . '` (patient_id,dentist_user_id,title,phase,status,version_no,estimate_total,notes,created_at) VALUES (?,?,?,"Imported plan","in_progress",1,0,?,NOW())')->execute([$patientId,$dentistId?:null,$plan,trim((string)($row['notes']??''))?:null]);
        }
        $balance=(float)($row['outstanding_balance']??0);
        if ($balance > 0) {
            $invoiceNo='DEMO-INV-'.$patientId.'-'.date('ymdHis');
            $pdo->prepare('INSERT INTO `' . table('invoices') . '` (branch_id,invoice_no,patient_id,description,amount,paid_amount,status,created_by,created_at) VALUES (?,?,?,?,?,0,"unpaid",?,NOW())')->execute([$branchId,$invoiceNo,$patientId,'Imported outstanding balance',$balance,(int)(current_user()['id']??0)]);
        }
        $recall = self::normalizeDate((string)($row['recall_due_date']??''));
        if ($recall !== '') {
            $pdo->prepare('INSERT INTO `' . table('recalls') . '` (patient_id,recall_type,due_date,status,notes,created_at) VALUES (?,"Imported recall",?,IF(?<CURDATE(),"overdue","future"),?,NOW())')->execute([$patientId,$recall,$recall,trim((string)($row['notes']??''))?:null]);
        }
        return $patientNo . ' imported. Medical information and guardian authority remain pending verification; history, balance, and recall data were retained.';
    }

    private static function importNewPatientRequest(PDO $pdo, array $row, int $batchId): string
    {
        $reference = trim((string)($row['request_reference']??''));
        $dup=$pdo->prepare('SELECT id FROM `' . table('appointment_requests') . '` WHERE public_reference=? LIMIT 1');$dup->execute([$reference]);
        if((int)$dup->fetchColumn()>0)throw new DuplicateDemoRecord('Appointment request already exists: '.$reference.'.');
        $branchId=self::branchId($pdo,'');
        $dentistId=self::dentistByEmployeeNo($pdo,(string)($row['preferred_dentist_employee_id']??''));
        $dentistName=self::userName($pdo,$dentistId);
        $serviceName=trim((string)($row['requested_service']??'Consultation'));
        $serviceId=self::serviceId($pdo,$serviceName,$branchId,$batchId,30);
        $urgency=strtolower((string)($row['urgency']??'regular'));
        $urgency=in_array($urgency,['urgent','emergency'],true)?'urgent':'routine';
        $statusMap=['pending'=>'submitted','for verification'=>'needs_verification','confirmed'=>'approved','declined'=>'declined','cancelled'=>'cancelled'];
        $status=$statusMap[strtolower(trim((string)($row['request_status']??'pending')))]??'submitted';
        $stmt=$pdo->prepare('INSERT INTO `' . table('appointment_requests') . '` (public_reference,branch_id,patient_type,first_name,middle_name,last_name,birth_date,sex,mobile,email,address,emergency_contact,guardian_name,guardian_mobile,service_name,service_id,preferred_dentist,preferred_dentist_user_id,preferred_date,preferred_time,alternate_date,alternate_time,urgency,reason,allergies,medications,conditions,status,consent_at,submitted_ip,is_demo,created_at) VALUES (?,? ,"new",?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),"127.0.0.1",1,NOW())');
        $stmt->execute([
            $reference,$branchId,trim((string)($row['first_name']??'')),trim((string)($row['middle_name']??''))?:null,trim((string)($row['last_name']??'')),self::normalizeDate((string)($row['date_of_birth']??'')),trim((string)($row['sex']??''))?:null,trim((string)($row['mobile']??'')),strtolower(trim((string)($row['email']??'')))?:null,trim((string)($row['address']??''))?:null,
            trim((string)($row['guardian_name']??''))!==''?trim((string)($row['guardian_name']??'')).' ('.trim((string)($row['guardian_relationship']??'Guardian')).')':null,trim((string)($row['guardian_name']??''))?:null,trim((string)($row['guardian_mobile']??''))?:null,$serviceName,$serviceId,$dentistName?:null,$dentistId?:null,self::normalizeDate((string)($row['preferred_date']??'')),self::normalizeTime((string)($row['preferred_time']??'')),self::normalizeDate((string)($row['alternate_date']??''))?:null,self::normalizeTime((string)($row['alternate_time']??''))?:null,$urgency,trim((string)($row['dental_concern']??'Appointment request')),self::cleanNone($row['allergies']??''),self::cleanNone($row['current_medications']??''),self::cleanNone($row['medical_conditions']??''),$status
        ]);
        $requestId=(int)$pdo->lastInsertId();
        self::registerDemo($pdo,$batchId,'appointment_request',$requestId,$reference);
        return $reference . ' imported as a public appointment request.';
    }

    private static function importAvailability(PDO $pdo,int $userId,int $branchId,string $schedule,int $batchId):void
    {
        $dayMap=['sun'=>0,'mon'=>1,'tue'=>2,'wed'=>3,'thu'=>4,'fri'=>5,'sat'=>6];
        foreach(array_filter(array_map('trim',explode(';',$schedule))) as $segment){
            if(!preg_match('/^(Sun|Mon|Tue|Wed|Thu|Fri|Sat)\s+(.+)$/i',$segment,$match))continue;
            $day=$dayMap[strtolower($match[1])]??null;if($day===null)continue;
            foreach(array_filter(array_map('trim',explode(',',$match[2]))) as $block){
                if(!preg_match('/^(\d{1,2}:\d{2})\s*-\s*(\d{1,2}:\d{2})$/',$block,$times))continue;
                $stmt=$pdo->prepare('INSERT INTO `' . table('doctor_availability_rules') . '` (doctor_user_id,branch_id,day_of_week,start_time,end_time,effective_from,status,created_by,approved_by,created_at) VALUES (?,?,?,?,?,CURDATE(),"active",?,?,NOW())');
                $stmt->execute([$userId,$branchId,$day,self::normalizeTime($times[1]),self::normalizeTime($times[2]),(int)(current_user()['id']??0),(int)(current_user()['id']??0)]);
                self::registerDemo($pdo,$batchId,'availability_rule',(int)$pdo->lastInsertId(),$userId.'-'.$day.'-'.$block);
            }
        }
    }

    private static function branchId(PDO $pdo,string $name):int
    {
        $name=trim($name);
        if($name!==''){$stmt=$pdo->prepare('SELECT id FROM `' . table('branches') . '` WHERE name=? AND is_active=1 LIMIT 1');$stmt->execute([$name]);$id=(int)$stmt->fetchColumn();if($id>0)return $id;}
        $id=(int)$pdo->query('SELECT id FROM `' . table('branches') . '` WHERE is_active=1 ORDER BY id LIMIT 1')->fetchColumn();
        if($id<=0)throw new RuntimeException('Create an active clinic branch before importing data.');
        return $id;
    }

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

    private static function specialtyId(PDO $pdo,string $name,int $batchId):int
    {
        $name=trim($name)?:'General Dentistry';$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=trim(preg_replace('/[^a-z0-9]+/','_',strtolower($name))??'general_dentistry','_');
        $pdo->prepare('INSERT INTO `' . table('dentist_specialties') . '` (code,name,description,is_active,created_at) VALUES (?,?,"Imported demo specialty",1,NOW())')->execute([$code,$name]);$id=(int)$pdo->lastInsertId();self::registerDemo($pdo,$batchId,'dentist_specialty',$id,$name);return $id;
    }

    private static function serviceId(PDO $pdo,string $name,int $branchId,int $batchId,int $duration):int
    {
        $name=trim($name)?:'Consultation';$stmt=$pdo->prepare('SELECT id FROM `' . table('service_catalog') . '` WHERE name=? AND (branch_id=? OR branch_id IS NULL) AND is_active=1 ORDER BY branch_id DESC LIMIT 1');$stmt->execute([$name,$branchId]);$id=(int)$stmt->fetchColumn();if($id>0)return $id;
        $pdo->prepare('INSERT INTO `' . table('service_catalog') . '` (branch_id,name,description,duration_minutes,is_public,is_active,created_at) VALUES (?,?,"Imported demo service",?,1,1,NOW())')->execute([$branchId,$name,max(15,$duration)]);$id=(int)$pdo->lastInsertId();self::registerDemo($pdo,$batchId,'service_catalog',$id,$name);return $id;
    }

    private static function dentistByEmployeeNo(PDO $pdo,string $employeeNo):int
    {
        $employeeNo=trim($employeeNo);if($employeeNo==='')return 0;
        $stmt=$pdo->prepare('SELECT u.id FROM `' . table('users') . '` u JOIN `' . table('roles') . '` r ON r.id=u.role_id AND r.code="dentist" WHERE u.employee_no=? LIMIT 1');$stmt->execute([$employeeNo]);return (int)$stmt->fetchColumn();
    }

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

    private static function userExists(PDO $pdo,string $employeeNo,string $email):bool
    {$stmt=$pdo->prepare('SELECT COUNT(*) FROM `' . table('users') . '` WHERE (employee_no IS NOT NULL AND employee_no<>"" AND employee_no=?) OR email=?');$stmt->execute([$employeeNo,$email]);return (int)$stmt->fetchColumn()>0;}

    private static function registerDemo(PDO $pdo,int $batchId,string $type,int $id,string $external=''):void
    {$pdo->prepare('INSERT INTO `' . table('demo_record_registry') . '` (batch_id,entity_type,entity_id,external_key,created_at) VALUES (?,?,?,?,NOW()) ON DUPLICATE KEY UPDATE batch_id=VALUES(batch_id),external_key=VALUES(external_key)')->execute([$batchId,$type,$id,$external?:null]);}

    private static function normalizeRole(string $role):string
    {
        $key=strtolower(trim(str_replace([' ','-'],'_',$role)));
        return ['administrator'=>'clinic_admin','admin'=>'clinic_admin','superuser'=>'super_admin','general_staff'=>'staff','assistant'=>'dental_assistant','doctor'=>'dentist'][$key]??$key;
    }

    private static function normalizePrivilege(string $value):string
    {return trim(preg_replace('/[^a-z0-9]+/','_',strtolower($value))??'','_');}

    private static function employmentType(string $value):string
    {$key=strtolower(trim(str_replace([' ','-'],'_',$value)));return in_array($key,['full_time','part_time','visiting','contractual'],true)?$key:'full_time';}

    private static function fullName(array $row):string
    {return trim(implode(' ',array_filter([trim((string)($row['first_name']??'')),trim((string)($row['middle_name']??'')),trim((string)($row['last_name']??''))])));}

    private static function splitList(string $value):array
    {return array_values(array_filter(array_map('trim',preg_split('/[;,]+/',$value)?:[]),static fn($v):bool=>$v!==''));}

    private static function truthy(mixed $value):bool
    {return in_array(strtolower(trim((string)$value)),['1','true','yes','y','on'],true);}

    private static function nullableInt(mixed $value):?int
    {$value=trim((string)$value);return $value===''?null:(int)$value;}

    private static function cleanNone(mixed $value):?string
    {return function_exists('dentalcare_normalize_medical_text') ? dentalcare_normalize_medical_text($value) : (trim((string)$value)===''?null:trim((string)$value));}

    private static function medicalSummary(array $row):string
    {
        $parts=[];foreach(['allergies'=>'Allergies','medical_conditions'=>'Conditions','current_medications'=>'Medications'] as $key=>$label){$value=self::cleanNone($row[$key]??'');if($value!==null)$parts[]=$label.': '.$value;}return implode("\n",$parts);
    }

    public static function normalizeDate(string $value):string
    {
        $value=trim($value);if($value==='')return '';
        if(is_numeric($value)){ $serial=(float)$value;if($serial>20000&&$serial<100000)return gmdate('Y-m-d',(int)round(($serial-25569)*86400)); }
        $timestamp=strtotime($value);return $timestamp===false?'':date('Y-m-d',$timestamp);
    }

    private static function normalizeTime(string $value):string
    {$value=trim($value);if($value==='')return '';$timestamp=strtotime($value);return $timestamp===false?'':date('H:i:s',$timestamp);}

    public static function createBackup(PDO $pdo,string $label='pre_reset'):string
    {
        // From v0.5.3 onward, destructive resets create a complete recovery
        // package so database rows and uploaded clinical files stay together.
        if (function_exists('backup_printing_schema_ready') ? backup_printing_schema_ready() : false) {
            require_once DENTAL_ROOT . '/app/Services/SystemBackupService.php';
            $result=(new SystemBackupService($pdo))->create('complete');
            return (string)$result['file_name'];
        }
        $prefix=(string)app_config('database.prefix','dent_');$like=str_replace(['\\','%','_'],['\\\\','\\%','\\_'],$prefix).'%';
        $stmt=$pdo->prepare('SELECT table_name FROM information_schema.tables WHERE table_schema=? AND table_name LIKE ? ESCAPE "\\" ORDER BY table_name');$stmt->execute([(string)app_config('database.name'),$like]);$tables=$stmt->fetchAll(PDO::FETCH_COLUMN);if(!$tables)throw new RuntimeException('No DentalCare tables were found for backup.');
        $file='dentalcare_'.$label.'_'.date('Ymd_His').'.sql';$path=backup_directory().'/'.$file;$handle=fopen($path,'wb');if(!$handle)throw new RuntimeException('Unable to create the automatic backup.');
        fwrite($handle,"-- DentalCare automatic backup\n-- Created ".date(DATE_ATOM)."\nSET FOREIGN_KEY_CHECKS=0;\n\n");
        foreach($tables as $tableName){$safe=str_replace('`','``',(string)$tableName);$create=$pdo->query('SHOW CREATE TABLE `'.$safe.'`')->fetch(PDO::FETCH_NUM);fwrite($handle,'DROP TABLE IF EXISTS `'.$safe."`;\n".($create[1]??'').";\n\n");$rows=$pdo->query('SELECT * FROM `'.$safe.'`');while($row=$rows->fetch(PDO::FETCH_ASSOC)){$columns=array_map(static fn($column):string=>'`'.str_replace('`','``',(string)$column).'`',array_keys($row));$values=array_map(static fn($v):string=>$v===null?'NULL':$pdo->quote((string)$v),array_values($row));fwrite($handle,'INSERT INTO `'.$safe.'` ('.implode(',',$columns).') VALUES ('.implode(',',$values).");\n");}fwrite($handle,"\n");}
        fwrite($handle,"SET FOREIGN_KEY_CHECKS=1;\n");fclose($handle);@chmod($path,0640);$size=filesize($path)?:0;
        $pdo->prepare('INSERT INTO `' . table('backup_logs') . '` (file_name,file_size,status,created_by,notes,created_at) VALUES (?,? ,"created",?,"Automatic database backup before data reset",NOW())')->execute([$file,$size,(int)(current_user()['id']??0)]);
        return $file;
    }

    public static function verifyPassword(PDO $pdo,string $password):void
    {
        $stmt=$pdo->prepare('SELECT password_hash FROM `' . table('users') . '` WHERE id=?');$stmt->execute([(int)(current_user()['id']??0)]);$hash=(string)$stmt->fetchColumn();
        if($hash===''||!password_verify($password,$hash))throw new RuntimeException('The Superuser password is incorrect.');
    }

    public static function reset(PDO $pdo,string $type):array
    {
        if(!in_array($type,['demo_only','clinic_activity','factory_defaults'],true))throw new RuntimeException('Invalid reset type.');
        $backup=self::createBackup($pdo,$type);
        $summary=['type'=>$type,'backup'=>$backup,'deleted'=>[]];
        $pdo->exec('SET FOREIGN_KEY_CHECKS=0');
        try{
            $pdo->beginTransaction();
            if($type==='demo_only')self::resetDemo($pdo,$summary);
            elseif($type==='clinic_activity')self::resetActivity($pdo,$summary);
            else self::resetFactory($pdo,$summary);
            $pdo->commit();
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}finally{$pdo->exec('SET FOREIGN_KEY_CHECKS=1');}
        $pdo->prepare('INSERT INTO `' . table('data_reset_logs') . '` (reset_type,backup_file,summary_json,performed_by,created_at) VALUES (?,?,?,?,NOW())')->execute([$type,$backup,json_encode($summary,JSON_UNESCAPED_SLASHES),(int)(current_user()['id']??0)]);
        audit('data_reset_completed','system',null,$summary);
        return $summary;
    }

    private static function resetDemoRegistryExtras(PDO $pdo,array &$summary):void
    {
        if(!self::tableExists($pdo,'demo_record_registry'))return;
        $deleteMap=[
            'prescription_item'=>'prescription_items','prescription'=>'prescriptions','follow_up'=>'follow_ups','recall'=>'recalls','communication'=>'communications',
            'sterilization_batch_event'=>'sterilization_batch_events','sterilization_batch_set'=>'sterilization_batch_sets','instrument_usage'=>'instrument_usage','sterilization_batch'=>'sterilization_batches','instrument_set'=>'instrument_sets','sterilization_equipment'=>'sterilization_equipment',
            'inventory_transaction'=>'inventory_transactions','inventory_branch_stock'=>'inventory_branch_stocks','inventory_document_history'=>'inventory_document_history','inventory_document_line'=>'inventory_document_lines','inventory_document'=>'inventory_documents','inventory_lot'=>'inventory_lots','inventory_item'=>'inventory_items',
            'branch_operating_hours'=>'branch_operating_hours','clinic_chair'=>'clinic_chairs',
        ];
        foreach($deleteMap as $entityType=>$logicalTable){
            if(!self::tableExists($pdo,$logicalTable))continue;
            try{$stmt=$pdo->prepare('SELECT entity_id FROM `' . table('demo_record_registry') . '` WHERE entity_type=?');$stmt->execute([$entityType]);$ids=array_map('intval',$stmt->fetchAll(PDO::FETCH_COLUMN));}catch(Throwable){$ids=[];}
            $ids=array_values(array_filter($ids,static fn(int $id):bool=>$id>0));
            if(!$ids)continue;
            self::deleteWhere($pdo,$logicalTable,'id IN ('.implode(',',$ids).')',$summary);
        }
    }

    private static function resetDemo(PDO $pdo,array &$summary):void
    {
        // Demo reset must remove both the visible demo patients and every linked
        // transaction created for the demo workflow. Older demo copies may have
        // DEMO-PAT-* records that were not correctly tagged with is_demo=1, so
        // we use both the official marker and the official demo-number pattern.
        $patientFilters=[];
        if(self::columnExists($pdo,'patients','is_demo'))$patientFilters[]='is_demo=1';
        if(self::columnExists($pdo,'patients','patient_no'))$patientFilters[]='patient_no LIKE "DEMO-PAT-%"';
        if(self::columnExists($pdo,'patients','email'))$patientFilters[]='email LIKE "%@mborrega-dentalcare.local"';
        $patientWhere=$patientFilters?'('.implode(' OR ',$patientFilters).')':'0=1';
        $patientIds=array_map('intval',$pdo->query('SELECT id FROM `' . table('patients') . '` WHERE '.$patientWhere)->fetchAll(PDO::FETCH_COLUMN));

        $userFilters=[];
        if(self::columnExists($pdo,'users','is_demo'))$userFilters[]='is_demo=1';
        if(self::columnExists($pdo,'users','employee_no'))$userFilters[]='employee_no LIKE "DEMO-%"';
        if(self::columnExists($pdo,'users','email'))$userFilters[]='email LIKE "demo.%@mborrega-dentalcare.local"';
        $userWhere=$userFilters?'('.implode(' OR ',$userFilters).')':'0=1';
        $userIds=array_map('intval',$pdo->query('SELECT id FROM `' . table('users') . '` WHERE '.$userWhere.' AND id<>'.(int)(current_user()['id']??0))->fetchAll(PDO::FETCH_COLUMN));

        self::deletePatientGraph($pdo,$patientIds,$summary);
        if($userIds){
            $in=implode(',',$userIds);
            foreach(['dentist_clinical_privileges'=>'dentist_user_id','dentist_branch_assignments'=>'dentist_user_id','dentist_service_assignments'=>'dentist_user_id','dentist_documents'=>'dentist_user_id','dentist_templates'=>'dentist_user_id','dentist_favorite_procedures'=>'dentist_user_id','dentist_assistant_assignments'=>'dentist_user_id','doctor_availability_rules'=>'doctor_user_id','doctor_availability_exceptions'=>'doctor_user_id','dentist_profiles'=>'user_id'] as $tableName=>$column){
                if(self::tableExists($pdo,$tableName)&&self::columnExists($pdo,$tableName,$column))self::deleteWhere($pdo,$tableName,$column.' IN ('.$in.')',$summary);
            }
            self::detachUserReferences($pdo,$userIds,$summary);
            self::deleteWhere($pdo,'users','id IN ('.$in.')',$summary);
        }
        if(self::tableExists($pdo,'appointment_requests')){
            $requestFilters=[];
            if(self::columnExists($pdo,'appointment_requests','is_demo'))$requestFilters[]='is_demo=1';
            if(self::columnExists($pdo,'appointment_requests','reference_no'))$requestFilters[]='reference_no LIKE "DEMO%"';
            if(self::columnExists($pdo,'appointment_requests','patient_id')&&$patientIds)$requestFilters[]='patient_id IN ('.implode(',',$patientIds).')';
            if($requestFilters)self::deleteWhere($pdo,'appointment_requests','('.implode(' OR ',$requestFilters).')',$summary);
        }
        if(self::tableExists($pdo,'demo_record_registry')){
            $serviceIds=array_map('intval',$pdo->query('SELECT entity_id FROM `' . table('demo_record_registry') . '` WHERE entity_type="service_catalog"')->fetchAll(PDO::FETCH_COLUMN));
            if($serviceIds){$serviceIn=implode(',',$serviceIds);if(self::tableExists($pdo,'dentist_service_assignments'))self::deleteWhere($pdo,'dentist_service_assignments','service_id IN ('.$serviceIn.')',$summary);self::deleteWhere($pdo,'service_catalog','id IN ('.$serviceIn.')',$summary);}
            $specialtyIds=array_map('intval',$pdo->query('SELECT entity_id FROM `' . table('demo_record_registry') . '` WHERE entity_type="dentist_specialty"')->fetchAll(PDO::FETCH_COLUMN));
            if($specialtyIds){$specialtyIn=implode(',',$specialtyIds);if(self::tableExists($pdo,'dentist_profiles')&&self::columnExists($pdo,'dentist_profiles','specialty_id'))$pdo->exec('UPDATE `' . table('dentist_profiles') . '` SET specialty_id=NULL WHERE specialty_id IN ('.$specialtyIn.')');self::deleteWhere($pdo,'dentist_specialties','id IN ('.$specialtyIn.')',$summary);}
        }
        self::resetDemoRegistryExtras($pdo,$summary);
        if(self::tableExists($pdo,'demo_record_registry'))self::deleteWhere($pdo,'demo_record_registry','1=1',$summary);
        if(self::tableExists($pdo,'settings')){foreach(['orientation_mode_enabled','full_demo_clinic_dataset_ready','demo_accounts_ready','demo_patients_ready','demo_patients_created_at','demo_accounts_created_at','full_demo_clinic_dataset_created_at'] as $key){$pdo->prepare('DELETE FROM `' . table('settings') . '` WHERE group_name="system" AND setting_key=?')->execute([$key]);}}
        if(self::tableExists($pdo,'data_import_batches'))$pdo->exec('UPDATE `' . table('data_import_batches') . '` SET status="cancelled" WHERE status IN ("completed","partial","previewed")');
        $summary['demo_reset_integrity']='Removed demo patients by marker/pattern and deleted linked appointments, queue rows, encounters, invoices, payments, outbox links, clinical samples, and registry records.';
    }

    private static function resetActivity(PDO $pdo,array &$summary):void
    {
        $tables=['financial_correction_addenda','billing_correction_requests','clinical_encounter_transfers','inhouse_checkup_results','appointment_waitlist','payment_item_allocations','payment_adjustments','cashier_checkouts','payments','invoice_items','cashier_sessions','appointment_status_history','queue_status_history','periodontal_measurements','treatment_plan_items','treatment_plan_reassignments','dental_chart_reviews','clinical_note_addenda','prescription_items','patient_record_releases','patient_portal_requests','patient_portal_invites','patient_portal_accounts','insurance_claim_history','insurance_claims','patient_insurance','communications','recalls','follow_ups','laboratory_cases','referrals','consent_forms','documents','prescriptions','instrument_usage','sterilization_batch_events','sterilization_batch_sets','procedures','treatment_plans','periodontal_charts','odontogram_entries','vital_signs','medical_alerts','patient_guardians','medical_histories','invoices','clinical_encounters','queue_entries','appointments','appointment_requests','patients','inventory_document_history','inventory_document_lines','inventory_transactions','inventory_documents','inventory_lots','inventory_branch_stocks','sterilization_batches','chair_session_assignments','chair_move_events','chair_status_events'];
        foreach($tables as $name)if(self::tableExists($pdo,$name))self::deleteWhere($pdo,$name,'1=1',$summary);
        if(self::tableExists($pdo,'instrument_sets'))$pdo->exec('UPDATE `' . table('instrument_sets') . '` SET status="dirty",last_batch_id=NULL,prepared_by=NULL,prepared_at=NULL,updated_at=NOW()');
        self::resetChairResources($pdo,false);
        if(self::tableExists($pdo,'demo_record_registry'))self::deleteWhere($pdo,'demo_record_registry','1=1',$summary);
        if(self::tableExists($pdo,'data_import_batches'))$pdo->exec('UPDATE `' . table('data_import_batches') . '` SET status="cancelled" WHERE status<>"failed"');
        self::resetPatientNumberSequenceAfterFullPatientWipe($pdo,$summary,'clinic_activity');
    }

    private static function resetPatientNumberSequenceAfterFullPatientWipe(PDO $pdo,array &$summary,string $resetType):void
    {
        // Full activity/factory resets delete all patient records. In that case
        // the visible patient-number counter must restart at the first clinic
        // number, e.g. P20260001, instead of continuing from the old dataset.
        // Demo-only reset intentionally does NOT call this because live records
        // may remain and reusing numbers would be unsafe.
        if(!self::tableExists($pdo,'settings'))return;
        $settingsTable=table('settings');
        $stmt=$pdo->prepare('INSERT INTO `' . $settingsTable . '` (group_name,setting_key,setting_value,is_secret,created_at,updated_at) VALUES ("patient_numbering","next_number","1",0,NOW(),NOW()) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),is_secret=0,updated_at=NOW()');
        $stmt->execute();
        $summary['patient_numbering']=[
            'next_number'=>'1',
            'next_visible_number'=>(function_exists('dental_patient_number_preview')?dental_patient_number_preview(null,1):('P'.date('Y').'0001')),
            'reset_type'=>$resetType,
            'rule'=>'Restart only after full patient-data reset. Demo-only reset preserves the counter.'
        ];
    }

    private static function resetFactory(PDO $pdo,array &$summary):void
    {
        self::resetActivity($pdo,$summary);
        $current=(int)(current_user()['id']??0);
        $uploadFiles=[];if(self::tableExists($pdo,'system_uploads')){$uploadFiles=$pdo->query('SELECT stored_name FROM `' . table('system_uploads') . '`')->fetchAll(PDO::FETCH_COLUMN);}
        foreach(['dentist_clinical_privileges','dentist_branch_assignments','dentist_service_assignments','dentist_documents','dentist_templates','dentist_favorite_procedures','dentist_assistant_assignments','doctor_availability_rules','doctor_availability_exceptions','dentist_profiles'] as $name)if(self::tableExists($pdo,$name))self::deleteWhere($pdo,$name,'1=1',$summary);
        $removeUserIds=array_map('intval',$pdo->query('SELECT id FROM `' . table('users') . '` WHERE id<>'.$current)->fetchAll(PDO::FETCH_COLUMN));
        if($removeUserIds)self::detachUserReferences($pdo,$removeUserIds,$summary);
        if(self::tableExists($pdo,'users'))self::deleteWhere($pdo,'users','id<>'.$current,$summary);
        self::resetChairResources($pdo,true);
        if(self::tableExists($pdo,'doctor_chair_assignments'))self::deleteWhere($pdo,'doctor_chair_assignments','1=1',$summary);
        foreach(['inventory_suppliers','instrument_sets','sterilization_equipment','inventory_items','system_uploads','email_outbox','email_log','smtp_diagnostics','branch_operating_exceptions'] as $name)if(self::tableExists($pdo,$name))self::deleteWhere($pdo,$name,'1=1',$summary);
        if(self::tableExists($pdo,'audit_logs')){
            $summary['preserved'][]='audit_logs';
            audit('factory_reset_audit_preserved','audit_log',null,['reason'=>'Audit history is append-only and is not erased by application data reset.']);
        }
        foreach($uploadFiles as $stored){$path=private_system_upload_directory().'/'.basename((string)$stored);if(is_file($path))@unlink($path);}
        self::seedDefaultServices($pdo);
        self::seedDefaultFacilities($pdo);
        self::seedDefaultInventoryCatalog($pdo);
    }



    private static function resetChairResources(PDO $pdo,bool $clearDefaultProvider):void
    {
        if(!self::tableExists($pdo,'clinic_chairs')) return;
        $physical=table('clinic_chairs');
        $sets=['status=CASE WHEN status="inactive" THEN status ELSE "available" END'];
        if(function_exists('schema_column_exists') && schema_column_exists($physical,'current_status_note')) $sets[]='current_status_note=NULL';
        if(function_exists('schema_column_exists') && schema_column_exists($physical,'last_released_at')) $sets[]='last_released_at=NOW()';
        if($clearDefaultProvider && function_exists('schema_column_exists') && schema_column_exists($physical,'default_provider_user_id')) $sets[]='default_provider_user_id=NULL';
        if(function_exists('schema_column_exists') && schema_column_exists($physical,'updated_at')) $sets[]='updated_at=NOW()';
        $pdo->exec('UPDATE `' . $physical . '` SET ' . implode(',',$sets));
    }

    private static function detachUserReferences(PDO $pdo,array $userIds,array &$summary):void
    {
        if(!$userIds)return;
        $in=implode(',',array_map('intval',$userIds));
        $stmt=$pdo->prepare('SELECT k.TABLE_NAME,k.COLUMN_NAME,c.IS_NULLABLE FROM information_schema.KEY_COLUMN_USAGE k JOIN information_schema.COLUMNS c ON c.TABLE_SCHEMA=k.TABLE_SCHEMA AND c.TABLE_NAME=k.TABLE_NAME AND c.COLUMN_NAME=k.COLUMN_NAME WHERE k.TABLE_SCHEMA=? AND k.REFERENCED_TABLE_NAME=?');
        $stmt->execute([(string)app_config('database.name'),table('users')]);
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $reference){
            $physical=(string)$reference['TABLE_NAME'];$column=(string)$reference['COLUMN_NAME'];
            if($physical===table('users'))continue;
            $safeTable=str_replace('`','``',$physical);$safeColumn=str_replace('`','``',$column);
            if((string)$reference['IS_NULLABLE']==='YES'){
                $count=(int)$pdo->exec('UPDATE `'.$safeTable.'` SET `'.$safeColumn.'`=NULL WHERE `'.$safeColumn.'` IN ('.$in.')');
                if($count>0)$summary['detached_user_references'][$physical.'.'.$column]=($summary['detached_user_references'][$physical.'.'.$column]??0)+$count;
            }else{
                $count=(int)$pdo->exec('DELETE FROM `'.$safeTable.'` WHERE `'.$safeColumn.'` IN ('.$in.')');
                if($count>0)$summary['deleted'][$physical]=($summary['deleted'][$physical]??0)+$count;
            }
        }
    }

    private static function deletePatientGraph(PDO $pdo,array $patientIds,array &$summary):void
    {
        if(!$patientIds)return;
        $patientIds=array_values(array_unique(array_filter(array_map('intval',$patientIds),static fn(int $id):bool=>$id>0)));
        if(!$patientIds)return;
        $in=implode(',',$patientIds);

        $appointmentIds=self::idsByColumn($pdo,'appointments','patient_id',$patientIds);
        $requestIds=self::idsByColumn($pdo,'appointment_requests','patient_id',$patientIds);
        $queueIds=self::idsByColumn($pdo,'queue_entries','patient_id',$patientIds);
        if($appointmentIds)$queueIds=array_values(array_unique(array_merge($queueIds,self::idsByColumn($pdo,'queue_entries','appointment_id',$appointmentIds))));
        $encounterIds=self::idsByColumn($pdo,'clinical_encounters','patient_id',$patientIds);
        if($appointmentIds)$encounterIds=array_values(array_unique(array_merge($encounterIds,self::idsByColumn($pdo,'clinical_encounters','appointment_id',$appointmentIds))));
        if($queueIds)$encounterIds=array_values(array_unique(array_merge($encounterIds,self::idsByColumn($pdo,'clinical_encounters','queue_entry_id',$queueIds))));
        $invoiceIds=self::idsByColumn($pdo,'invoices','patient_id',$patientIds);
        if($encounterIds)$invoiceIds=array_values(array_unique(array_merge($invoiceIds,self::idsByColumn($pdo,'invoices','encounter_id',$encounterIds))));
        if($queueIds)$invoiceIds=array_values(array_unique(array_merge($invoiceIds,self::idsByColumn($pdo,'invoices','queue_entry_id',$queueIds))));
        $invoiceItemIds=$invoiceIds?self::idsByColumn($pdo,'invoice_items','invoice_id',$invoiceIds):[];
        $paymentIds=$invoiceIds?self::idsByColumn($pdo,'payments','invoice_id',$invoiceIds):[];
        $appointmentServiceIds=$appointmentIds?self::idsByColumn($pdo,'appointment_services','appointment_id',$appointmentIds):[];
        $clinicalUsageIds=$encounterIds?self::idsByColumn($pdo,'clinical_usage_items','encounter_id',$encounterIds):[];

        // Delete dependent rows first. FK checks are disabled by reset(), but we
        // still delete explicitly so no orphaned demo records survive.
        if($paymentIds)self::deleteByColumnIn($pdo,'payment_item_allocations','payment_id',$paymentIds,$summary);
        if($invoiceIds)self::deleteByColumnIn($pdo,'payment_item_allocations','invoice_id',$invoiceIds,$summary);
        if($invoiceItemIds)self::deleteByColumnIn($pdo,'payment_item_allocations','invoice_item_id',$invoiceItemIds,$summary);
        if($paymentIds)self::deleteByColumnIn($pdo,'payment_adjustments','payment_id',$paymentIds,$summary);
        if($invoiceIds)self::deleteByColumnIn($pdo,'payment_adjustments','invoice_id',$invoiceIds,$summary);
        if($invoiceIds)self::deleteByColumnIn($pdo,'payments','invoice_id',$invoiceIds,$summary);
        if($invoiceIds)self::deleteByColumnIn($pdo,'invoice_items','invoice_id',$invoiceIds,$summary);
        if($clinicalUsageIds)self::deleteByColumnIn($pdo,'invoice_items','clinical_usage_item_id',$clinicalUsageIds,$summary);

        if($appointmentServiceIds){
            self::deleteByColumnIn($pdo,'appointment_service_dispositions','appointment_service_id',$appointmentServiceIds,$summary);
            self::deleteByColumnIn($pdo,'appointment_services','source_appointment_service_id',$appointmentServiceIds,$summary);
        }
        if($appointmentIds){
            foreach([['appointment_service_dispositions','source_appointment_id'],['appointment_service_dispositions','destination_appointment_id'],['appointment_status_history','appointment_id'],['appointment_reminders','appointment_id'],['appointment_services','appointment_id'],['appointment_waitlist','appointment_id']] as [$name,$column])self::deleteByColumnIn($pdo,$name,$column,$appointmentIds,$summary);
            self::deleteByColumnIn($pdo,'clinical_encounters','appointment_id',$appointmentIds,$summary);
            self::deleteByColumnIn($pdo,'queue_entries','appointment_id',$appointmentIds,$summary);
        }
        if($queueIds){
            foreach([['queue_status_history','queue_entry_id'],['chair_session_assignments','queue_entry_id'],['chair_move_events','queue_entry_id'],['chair_status_events','queue_entry_id'],['chair_visit_movements','queue_entry_id'],['clinical_encounters','queue_entry_id'],['invoices','queue_entry_id']] as [$name,$column])self::deleteByColumnIn($pdo,$name,$column,$queueIds,$summary);
        }
        if($encounterIds){
            foreach([['clinical_encounter_transfers','encounter_id'],['clinical_note_addenda','encounter_id'],['clinical_usage_items','encounter_id'],['clinical_assessments','encounter_id'],['invoice_items','procedure_id'],['procedures','encounter_id'],['prescriptions','encounter_id'],['documents','encounter_id'],['consent_forms','encounter_id'],['instrument_usage','encounter_id']] as [$name,$column])self::deleteByColumnIn($pdo,$name,$column,$encounterIds,$summary);
        }
        if($requestIds){
            if(self::tableExists($pdo,'patient_record_releases') && self::tableExists($pdo,'patient_portal_requests')){
                self::deleteWhere($pdo,'patient_record_releases','request_id IN (SELECT id FROM `' . table('patient_portal_requests') . '` WHERE appointment_request_id IN ('.implode(',',array_map('intval',$requestIds)).'))',$summary);
            }
            foreach([['patient_portal_requests','appointment_request_id'],['communications','appointment_request_id']] as [$name,$column])self::deleteByColumnIn($pdo,$name,$column,$requestIds,$summary);
        }

        $parentQueries=[
            ['financial_correction_addenda','patient_id IN ('.$in.')'],
            ['billing_correction_requests','patient_id IN ('.$in.')'],
            ['clinical_encounter_transfers','patient_id IN ('.$in.')'],
            ['payments','invoice_id IN (SELECT id FROM `' . table('invoices') . '` WHERE patient_id IN ('.$in.'))'],
            ['invoice_items','invoice_id IN (SELECT id FROM `' . table('invoices') . '` WHERE patient_id IN ('.$in.'))'],
            ['payment_item_allocations','invoice_id IN (SELECT id FROM `' . table('invoices') . '` WHERE patient_id IN ('.$in.'))'],
            ['appointment_status_history','appointment_id IN (SELECT id FROM `' . table('appointments') . '` WHERE patient_id IN ('.$in.'))'],
            ['appointment_services','appointment_id IN (SELECT id FROM `' . table('appointments') . '` WHERE patient_id IN ('.$in.'))'],
            ['queue_status_history','queue_entry_id IN (SELECT id FROM `' . table('queue_entries') . '` WHERE patient_id IN ('.$in.'))'],
            ['periodontal_measurements','chart_id IN (SELECT id FROM `' . table('periodontal_charts') . '` WHERE patient_id IN ('.$in.'))'],
            ['treatment_plan_items','treatment_plan_id IN (SELECT id FROM `' . table('treatment_plans') . '` WHERE patient_id IN ('.$in.'))'],
            ['clinical_note_addenda','encounter_id IN (SELECT id FROM `' . table('clinical_encounters') . '` WHERE patient_id IN ('.$in.'))'],
            ['prescription_items','prescription_id IN (SELECT id FROM `' . table('prescriptions') . '` WHERE patient_id IN ('.$in.'))'],
        ];foreach($parentQueries as [$name,$where])if(self::tableExists($pdo,$name))self::deleteWhere($pdo,$name,$where,$summary);

        foreach(['inhouse_checkup_results','appointment_waitlist','insurance_claim_history','insurance_claims','patient_insurance','communications','recalls','follow_ups','laboratory_cases','referrals','consent_forms','documents','prescriptions','clinical_usage_items','clinical_assessments','procedures','treatment_plans','dental_chart_reviews','periodontal_charts','odontogram_entries','vital_signs','medical_alerts','patient_guardians','medical_histories','invoices','clinical_encounters','queue_entries','appointments','appointment_series','appointment_requests','patient_record_releases','patient_portal_requests','patient_portal_invites','patient_portal_accounts'] as $name)if(self::tableExists($pdo,$name)&&self::columnExists($pdo,$name,'patient_id'))self::deleteWhere($pdo,$name,'patient_id IN ('.$in.')',$summary);
        if(self::tableExists($pdo,'patients'))self::deleteWhere($pdo,'patients','id IN ('.$in.')',$summary);
    }

    private static function idsByColumn(PDO $pdo,string $name,string $column,array $ids):array
    {
        $ids=array_values(array_unique(array_filter(array_map('intval',$ids),static fn(int $id):bool=>$id>0)));
        if(!$ids||!self::tableExists($pdo,$name)||!self::columnExists($pdo,$name,$column))return [];
        try{return array_map('intval',$pdo->query('SELECT id FROM `' . table($name) . '` WHERE `'.str_replace('`','``',$column).'` IN ('.implode(',',$ids).')')->fetchAll(PDO::FETCH_COLUMN));}
        catch(Throwable){return [];}
    }

    private static function deleteByColumnIn(PDO $pdo,string $name,string $column,array $ids,array &$summary):void
    {
        $ids=array_values(array_unique(array_filter(array_map('intval',$ids),static fn(int $id):bool=>$id>0)));
        if(!$ids||!self::tableExists($pdo,$name)||!self::columnExists($pdo,$name,$column))return;
        self::deleteWhere($pdo,$name,'`'.str_replace('`','``',$column).'` IN ('.implode(',',$ids).')',$summary);
    }

    private static function deleteWhere(PDO $pdo,string $name,string $where,array &$summary):void
    {
        if(!self::tableExists($pdo,$name))return;$count=(int)$pdo->exec('DELETE FROM `' . table($name) . '` WHERE '.$where);$summary['deleted'][$name]=($summary['deleted'][$name]??0)+$count;
    }

    private static function tableExists(PDO $pdo,string $name):bool
    {$stmt=$pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=? AND table_name=?');$stmt->execute([(string)app_config('database.name'),table($name)]);return (int)$stmt->fetchColumn()>0;}

    private static function columnExists(PDO $pdo,string $name,string $column):bool
    {
        if(!self::tableExists($pdo,$name))return false;
        $stmt=$pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=? AND table_name=? AND column_name=?');
        $stmt->execute([(string)app_config('database.name'),table($name),$column]);
        return (int)$stmt->fetchColumn()>0;
    }


    private static function seedDefaultInventoryCatalog(PDO $pdo):void
    {
        if(!self::tableExists($pdo,'inventory_items'))return;
        $defaults = [
            ['SUP-PPE-001','Nitrile Examination Gloves - Small','PPE','box'],
            ['SUP-PPE-002','Nitrile Examination Gloves - Medium','PPE','box'],
            ['SUP-PPE-003','Nitrile Examination Gloves - Large','PPE','box'],
            ['SUP-PPE-004','Surgical Face Masks','PPE','box'],
            ['SUP-PPE-005','Disposable Isolation Gowns','PPE','pack'],
            ['SUP-PPE-006','Disposable Head Caps','PPE','pack'],
            ['SUP-PPE-007','Face Shields','PPE','piece'],
            ['SUP-DSB-001','Patient Bibs','Disposable supplies','pack'],
            ['SUP-DSB-002','Cotton Rolls','Disposable supplies','pack'],
            ['SUP-DSB-003','Cotton Pellets','Disposable supplies','pack'],
            ['SUP-DSB-004','Sterile Gauze Pads 2 x 2','Disposable supplies','pack'],
            ['SUP-DSB-005','Sterile Gauze Pads 4 x 4','Disposable supplies','pack'],
            ['SUP-DSB-006','Saliva Ejectors','Disposable supplies','pack'],
            ['SUP-DSB-007','High-Volume Evacuation Tips','Disposable supplies','pack'],
            ['SUP-DSB-008','Disposable Air-Water Syringe Tips','Disposable supplies','pack'],
            ['SUP-DSB-009','Disposable Prophy Cups','Disposable supplies','pack'],
            ['SUP-DSB-010','Disposable Prophy Brushes','Disposable supplies','pack'],
            ['SUP-ANE-001','Lidocaine 2% with Epinephrine Cartridges','Anesthetics','box'],
            ['SUP-ANE-002','Articaine 4% with Epinephrine Cartridges','Anesthetics','box'],
            ['SUP-ANE-003','Mepivacaine 3% Cartridges','Anesthetics','box'],
            ['SUP-ANE-004','Topical Anesthetic Gel','Anesthetics','tube'],
            ['SUP-ANE-005','Dental Needles - Short','Anesthetics','box'],
            ['SUP-ANE-006','Dental Needles - Long','Anesthetics','box'],
            ['SUP-RES-001','Phosphoric Acid Etchant Gel 37%','Restorative','syringe'],
            ['SUP-RES-002','Universal Dental Bonding Agent','Restorative','bottle'],
            ['SUP-RES-003','Universal Composite Resin','Restorative','syringe'],
            ['SUP-RES-004','Flowable Composite Resin','Restorative','syringe'],
            ['SUP-RES-005','Glass Ionomer Restorative Cement','Restorative','kit'],
            ['SUP-RES-006','Temporary Filling Material','Restorative','jar'],
            ['SUP-RES-007','Calcium Hydroxide Liner','Restorative','kit'],
            ['SUP-RES-008','Matrix Bands','Restorative','box'],
            ['SUP-RES-009','Interdental Wedges','Restorative','box'],
            ['SUP-RES-010','Articulating Paper','Restorative','box'],
            ['SUP-PRE-001','Prophylaxis Paste','Preventive','jar'],
            ['SUP-PRE-002','Fluoride Varnish','Preventive','box'],
            ['SUP-PRE-003','Pit and Fissure Sealant','Preventive','syringe'],
            ['SUP-END-001','Gutta-Percha Points','Endodontics','box'],
            ['SUP-END-002','Absorbent Paper Points','Endodontics','box'],
            ['SUP-END-003','Sodium Hypochlorite Irrigant','Endodontics','bottle'],
            ['SUP-END-004','EDTA Root Canal Lubricant','Endodontics','syringe'],
            ['SUP-END-005','Endodontic Irrigation Syringes','Endodontics','box'],
            ['SUP-END-006','Rubber Dam Sheets','Endodontics','box'],
            ['SUP-IMP-001','Alginate Impression Material','Impression','bag'],
            ['SUP-IMP-002','PVS Impression Material','Impression','kit'],
            ['SUP-IMP-003','Bite Registration Material','Impression','cartridge'],
            ['SUP-LAB-001','Dental Stone','Laboratory','bag'],
            ['SUP-LAB-002','Cold-Cure Acrylic Resin','Laboratory','kit'],
            ['SUP-IPC-001','Sterilization Pouches - Small','Infection control','box'],
            ['SUP-IPC-002','Sterilization Pouches - Medium','Infection control','box'],
            ['SUP-IPC-003','Sterilization Pouches - Large','Infection control','box'],
            ['SUP-IPC-004','Chemical Indicator Tape','Infection control','roll'],
            ['SUP-IPC-005','Biological Indicator Tests','Infection control','box'],
            ['SUP-IPC-006','Enzymatic Instrument Cleaner','Infection control','bottle'],
            ['SUP-IPC-007','Surface Disinfectant','Infection control','bottle'],
            ['SUP-IPC-008','Ultrasonic Cleaning Solution','Infection control','bottle'],
            ['SUP-XRY-001','Digital Sensor Sleeves','Radiography','box'],
            ['SUP-XRY-002','Phosphor Plate Barrier Envelopes','Radiography','box'],
            ['SUP-XRY-003','Intraoral X-Ray Film','Radiography','box'],
            ['SUP-ORT-001','Orthodontic Elastics','Orthodontics','pack'],
            ['SUP-ORT-002','Orthodontic Archwires','Orthodontics','pack'],
            ['SUP-ORT-003','Orthodontic Brackets','Orthodontics','kit'],
            ['SUP-SUR-001','Sutures','Surgery','box'],
            ['SUP-SUR-002','Hemostatic Sponges','Surgery','box'],
            ['SUP-SUR-003','Surgical Blades','Surgery','box'],
        ];
        $stmt=$pdo->prepare('INSERT INTO `' . table('inventory_items') . '` (sku,name,category,unit,track_lots,is_clinical_supply,purchase_cost,usage_cost,reorder_level,current_stock,status,created_at) VALUES (?,?,?,?,1,1,0,0,0,0,"active",NOW()) ON DUPLICATE KEY UPDATE name=VALUES(name),category=VALUES(category),unit=VALUES(unit),status="active"');
        foreach($defaults as $item)$stmt->execute($item);
    }

    private static function seedDefaultServices(PDO $pdo):void
    {
        if (function_exists('dentalcare_seed_default_services')) {
            dentalcare_seed_default_services($pdo);
            return;
        }
        if(!self::tableExists($pdo,'service_catalog'))return;
        $dentistDefaults=[['Dental Examination',30],['Oral Prophylaxis',45],['Tooth Restoration',60],['Tooth Extraction',60],['Root Canal Consultation',60],['Orthodontic Consultation',45],['Denture Consultation',45],['Emergency Dental Visit',60]];
        $dentist=$pdo->prepare('INSERT INTO `' . table('service_catalog') . '` (branch_id,name,duration_minutes,standard_fee,provider_type,provider_role_code,slot_capacity,requires_chair,is_public,is_active,created_at) SELECT NULL,?,?,0,"dentist","dentist",1,1,1,1,NOW() WHERE NOT EXISTS (SELECT 1 FROM `' . table('service_catalog') . '` WHERE name=? AND provider_type="dentist")');
        foreach($dentistDefaults as [$name,$duration])$dentist->execute([$name,$duration,$name]);
    }

    private static function seedDefaultFacilities(PDO $pdo):void
    {
        if (function_exists('dentalcare_seed_default_facilities')) {
            dentalcare_seed_default_facilities($pdo);
        }
    }

}

final class DuplicateDemoRecord extends RuntimeException {}
