<?php

declare(strict_types=1);

/**
 * DentalCare v1.0.0.0 production data-integrity baseline.
 *
 * All checks are read-only. The only maintenance actions exposed by this
 * helper affect the request-integrity ledger, never clinical or financial
 * business records.
 */

function dental_data_integrity_table_ready(): bool
{
    try {
        return schema_table_exists(table('data_integrity_snapshots'))
            && schema_column_exists(table('data_integrity_snapshots'), 'fingerprint_sha256');
    } catch (Throwable) {
        return false;
    }
}

/** @return mixed */
function dental_data_integrity_safe(callable $callback, mixed $default = null): mixed
{
    try {
        return $callback();
    } catch (Throwable $exception) {
        error_log('DentalCare data-integrity check failed: ' . $exception->getMessage());
        return $default;
    }
}

function dental_data_integrity_check(
    string $key,
    string $label,
    string $status,
    string $metric,
    string $detail,
    string $category = 'Database contract',
    string $link = ''
): array {
    if (!in_array($status, ['pass','review','blocked'], true)) $status = 'review';
    return compact('key','label','status','metric','detail','category','link');
}

function dental_data_integrity_count(PDO $pdo, string $sql, array $arguments = []): int
{
    return (int)dental_data_integrity_safe(static function () use ($pdo, $sql, $arguments): int {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($arguments);
        return (int)$stmt->fetchColumn();
    }, 0);
}

/** @return array<int,array<string,mixed>> */
function dental_data_integrity_engine_checks(PDO $pdo): array
{
    $logicalTables = [
        'patients','appointments','appointment_services','queue_entries',
        'clinical_encounters','procedures','invoices','payments',
        'inventory_items','inventory_transactions','audit_logs','operation_requests',
    ];
    $physical = [];
    foreach ($logicalTables as $logical) {
        $name = table($logical);
        if (schema_table_exists($name)) $physical[] = $name;
    }
    if (!$physical) {
        return [dental_data_integrity_check(
            'critical_table_engines','Transactional table engines','blocked','No critical tables found',
            'The configured database does not expose the expected DentalCare tables.','Database contract'
        )];
    }

    $placeholders = implode(',', array_fill(0, count($physical), '?'));
    $stmt = $pdo->prepare(
        'SELECT table_name,engine,table_collation FROM information_schema.tables '
        . 'WHERE table_schema=? AND table_name IN (' . $placeholders . ') ORDER BY table_name'
    );
    $stmt->execute(array_merge([(string)app_config('database.name')], $physical));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    $nonTransactional = [];
    $nonUtf8 = [];
    foreach ($rows as $row) {
        $engine = strtoupper((string)($row['engine'] ?? ''));
        $collation = strtolower((string)($row['table_collation'] ?? ''));
        if ($engine !== 'INNODB') $nonTransactional[] = (string)$row['table_name'] . ' (' . ($engine ?: 'unknown') . ')';
        if ($collation !== '' && !str_starts_with($collation, 'utf8mb4_')) $nonUtf8[] = (string)$row['table_name'] . ' (' . $collation . ')';
    }

    $checks = [];
    $checks[] = dental_data_integrity_check(
        'critical_table_engines','Transactional table engines',
        $nonTransactional ? 'blocked' : 'pass',
        $nonTransactional ? count($nonTransactional) . ' non-InnoDB' : count($rows) . ' InnoDB tables',
        $nonTransactional
            ? 'Clinical or financial writes cannot be considered atomic until these tables use InnoDB: ' . implode(', ', array_slice($nonTransactional, 0, 8)) . '.'
            : 'Critical clinical, queue, billing, inventory, audit, and request-ledger tables use transactional InnoDB storage.',
        'Database contract'
    );
    $checks[] = dental_data_integrity_check(
        'critical_table_collation','Critical table character set',
        $nonUtf8 ? 'review' : 'pass',
        $nonUtf8 ? count($nonUtf8) . ' legacy collations' : 'utf8mb4 aligned',
        $nonUtf8
            ? 'Review legacy collations before multilingual data import: ' . implode(', ', array_slice($nonUtf8, 0, 8)) . '.'
            : 'Critical tables use utf8mb4-compatible collations.',
        'Database contract'
    );
    return $checks;
}

/** @return array<int,array<string,mixed>> */
function dental_data_integrity_orphan_checks(PDO $pdo): array
{
    $definitions = [
        ['appointments_patient','Appointments without a patient','appointments','a','patient_id','patients','p','id'],
        ['queue_patient','Queue entries without a patient','queue_entries','q','patient_id','patients','p','id'],
        ['queue_appointment','Queue entries with a missing appointment','queue_entries','q','appointment_id','appointments','a','id',true],
        ['encounter_patient','Clinical encounters without a patient','clinical_encounters','ce','patient_id','patients','p','id'],
        ['procedure_patient','Procedures without a patient','procedures','pr','patient_id','patients','p','id'],
        ['procedure_encounter','Procedures with a missing encounter','procedures','pr','encounter_id','clinical_encounters','ce','id',true],
        ['invoice_patient','Invoices without a patient','invoices','i','patient_id','patients','p','id'],
        ['invoice_encounter','Invoices with a missing encounter','invoices','i','encounter_id','clinical_encounters','ce','id',true],
        ['payment_invoice','Payments without an invoice','payments','py','invoice_id','invoices','i','id'],
        ['appointment_service_parent','Appointment services without an appointment','appointment_services','aps','appointment_id','appointments','a','id'],
        ['record_release_patient','Record releases without a patient','patient_record_releases','rr','patient_id','patients','p','id'],
    ];

    $checks = [];
    foreach ($definitions as $definition) {
        [$key,$label,$childLogical,$childAlias,$childColumn,$parentLogical,$parentAlias,$parentColumn] = $definition;
        $nullable = (bool)($definition[8] ?? false);
        $child = table($childLogical);
        $parent = table($parentLogical);
        if (!schema_table_exists($child) || !schema_table_exists($parent)
            || !schema_column_exists($child, $childColumn) || !schema_column_exists($parent, $parentColumn)) {
            continue;
        }
        $sql = 'SELECT COUNT(*) FROM `' . $child . '` ' . $childAlias
            . ' LEFT JOIN `' . $parent . '` ' . $parentAlias
            . ' ON ' . $parentAlias . '.`' . $parentColumn . '`=' . $childAlias . '.`' . $childColumn . '`'
            . ' WHERE ' . $parentAlias . '.`' . $parentColumn . '` IS NULL';
        if ($nullable) $sql .= ' AND ' . $childAlias . '.`' . $childColumn . '` IS NOT NULL';
        $count = dental_data_integrity_count($pdo, $sql);
        $checks[] = dental_data_integrity_check(
            'orphan_' . $key,$label,$count > 0 ? 'review' : 'pass',
            $count > 0 ? $count . ' orphaned row' . ($count === 1 ? '' : 's') : 'No orphaned rows',
            $count > 0
                ? 'Review and reconcile these records before relying on reports, exports, or release evidence. No rows were modified.'
                : 'Every checked child record resolves to its required parent record.',
            'Referential integrity'
        );
    }
    return $checks;
}

/** @return array<int,array<string,mixed>> */
function dental_data_integrity_duplicate_checks(PDO $pdo): array
{
    $checks = [];
    $patients = table('patients');
    if (schema_table_exists($patients) && schema_column_exists($patients,'patient_no')) {
        $activeClause = schema_column_exists($patients,'deleted_at') ? ' AND p.deleted_at IS NULL' : '';
        $count = dental_data_integrity_count($pdo,
            'SELECT COUNT(*) FROM (SELECT p.patient_no FROM `' . $patients . '` p '
            . 'WHERE p.patient_no IS NOT NULL AND TRIM(p.patient_no)<>""' . $activeClause
            . ' GROUP BY p.patient_no HAVING COUNT(*)>1) duplicate_patient_numbers'
        );
        $checks[] = dental_data_integrity_check(
            'duplicate_patient_numbers','Duplicate active patient numbers',$count > 0 ? 'review' : 'pass',
            $count > 0 ? $count . ' duplicated number' . ($count === 1 ? '' : 's') : 'Unique',
            $count > 0
                ? 'Merge or renumber duplicate active patient identities through an authorized correction workflow.'
                : 'No duplicate active patient numbers were detected.',
            'Identity integrity','patient-numbering'
        );
    }

    $payments = table('payments');
    $invoices = table('invoices');
    if (schema_table_exists($payments) && schema_table_exists($invoices)
        && schema_column_exists($payments,'reference_no') && schema_column_exists($payments,'method')
        && schema_column_exists($payments,'invoice_id') && schema_column_exists($invoices,'branch_id')) {
        $count = dental_data_integrity_count($pdo,
            'SELECT COUNT(*) FROM (SELECT i.branch_id,LOWER(TRIM(py.method)) payment_method,LOWER(TRIM(py.reference_no)) payment_reference '
            . 'FROM `' . $payments . '` py JOIN `' . $invoices . '` i ON i.id=py.invoice_id '
            . 'WHERE py.reference_no IS NOT NULL AND TRIM(py.reference_no)<>"" AND LOWER(TRIM(py.method))<>"cash" '
            . 'GROUP BY i.branch_id,LOWER(TRIM(py.method)),LOWER(TRIM(py.reference_no)) HAVING COUNT(*)>1) duplicate_payment_references'
        );
        $checks[] = dental_data_integrity_check(
            'duplicate_payment_references','Repeated non-cash payment references',$count > 0 ? 'review' : 'pass',
            $count > 0 ? $count . ' repeated reference group' . ($count === 1 ? '' : 's') : 'No repeated references',
            $count > 0
                ? 'Review repeated external references for accidental duplicate collection, insurer remittance reuse, or documented legitimate batching.'
                : 'No repeated non-cash reference groups were detected within a branch.',
            'Financial integrity','billing'
        );
    }
    return $checks;
}

/** @return array<string,mixed> */
function dental_data_integrity_source_coverage(): array
{
    $requirements = [
        'Appointment scheduling'=>['modules/patient-care/appointments.php',['beginTransaction','FOR UPDATE','operation_integrity_begin']],
        'Queue transitions'=>['modules/patient-care/queue.php',['beginTransaction','FOR UPDATE','operation_integrity_begin']],
        'Prescriptions'=>['modules/clinical/prescriptions.php',['beginTransaction','operation_integrity_begin','clinical_safety']],
        'Record release'=>['modules/patient-care/record_requests.php',['beginTransaction','operation_integrity_begin','FOR UPDATE']],
        'Cashier register'=>['modules/finance/billing.php',['beginTransaction','request_token','revenue_acquire']],
        'Inventory movements'=>['modules/operations/inventory.php',['beginTransaction','FOR UPDATE','inventory_']],
        'Sterilization traceability'=>['modules/operations/sterilization.php',['beginTransaction','FOR UPDATE','sterilization_']],
    ];
    $missing = [];
    $ready = 0;
    foreach ($requirements as $label => [$relativePath,$needles]) {
        $path = DENTAL_ROOT . '/' . $relativePath;
        $source = is_file($path) ? (string)file_get_contents($path) : '';
        $absent = [];
        foreach ($needles as $needle) if ($source === '' || !str_contains($source, $needle)) $absent[] = $needle;
        if ($absent) $missing[] = $label . ' (' . implode(', ', $absent) . ')'; else $ready++;
    }
    return ['ready'=>$missing === [],'ready_count'=>$ready,'total'=>count($requirements),'missing'=>$missing];
}

/** @return array<string,mixed> */
function dental_data_integrity_snapshot(PDO $pdo): array
{
    $checks = [];

    try {
        $connection = (int)$pdo->query('SELECT 1')->fetchColumn() === 1;
        $checks[] = dental_data_integrity_check(
            'database_connection','Database read contract',$connection ? 'pass' : 'blocked',
            $connection ? 'Connected' : 'Failed',
            $connection ? 'The configured database accepted a read query.' : 'The database did not return the expected value.'
        );
    } catch (Throwable $exception) {
        $checks[] = dental_data_integrity_check('database_connection','Database read contract','blocked','Connection failed',mb_substr($exception->getMessage(),0,180));
    }

    foreach (dental_data_integrity_engine_checks($pdo) as $check) $checks[] = $check;

    $foreignKeys = (int)dental_data_integrity_safe(static fn(): int => (int)$pdo->query('SELECT @@SESSION.foreign_key_checks')->fetchColumn(), 0);
    $checks[] = dental_data_integrity_check(
        'foreign_key_checks','Foreign-key enforcement',$foreignKeys === 1 ? 'pass' : 'blocked',
        $foreignKeys === 1 ? 'Enabled' : 'Disabled',
        $foreignKeys === 1
            ? 'This database session enforces declared foreign-key constraints.'
            : 'Foreign-key checks are disabled for this session. Re-enable them before processing clinical or financial writes.'
    );

    $sqlMode = strtoupper((string)dental_data_integrity_safe(static fn(): string => (string)$pdo->query('SELECT @@SESSION.sql_mode')->fetchColumn(), ''));
    $strict = str_contains($sqlMode,'STRICT_TRANS_TABLES') || str_contains($sqlMode,'STRICT_ALL_TABLES');
    $checks[] = dental_data_integrity_check(
        'strict_sql_mode','Strict SQL mode',$strict ? 'pass' : 'review',
        $strict ? 'Strict mode enabled' : 'Strict mode not detected',
        $strict
            ? 'Invalid or truncated values are rejected instead of silently altered.'
            : 'Enable STRICT_TRANS_TABLES or STRICT_ALL_TABLES at the database server to reduce silent data coercion.'
    );

    foreach (dental_data_integrity_orphan_checks($pdo) as $check) $checks[] = $check;
    foreach (dental_data_integrity_duplicate_checks($pdo) as $check) $checks[] = $check;

    if (is_file(DENTAL_ROOT . '/app/query_integrity.php')) {
        require_once DENTAL_ROOT . '/app/query_integrity.php';
        $query = dental_query_integrity_snapshot($pdo);
        $checks[] = dental_data_integrity_check(
            'joined_query_regression','Joined-query regression safety',
            (string)($query['overall'] ?? 'blocked') === 'pass' ? 'pass' : 'blocked',
            (string)($query['overall'] ?? 'blocked') === 'pass' ? 'All probes passed' : 'Query probes failed',
            (string)($query['overall'] ?? 'blocked') === 'pass'
                ? 'Critical joined queries execute with alias-qualified filters and source-level regression checks.'
                : 'One or more critical joined-query probes failed. Review Operational Health before applying updates.',
            'Query safety','operational-health'
        );
    }

    $coverage = dental_data_integrity_source_coverage();
    $checks[] = dental_data_integrity_check(
        'critical_transaction_coverage','Critical transaction-boundary coverage',
        !empty($coverage['ready']) ? 'pass' : 'review',
        (int)$coverage['ready_count'] . ' / ' . (int)$coverage['total'] . ' workflows evidenced',
        !empty($coverage['ready'])
            ? 'Appointment, queue, prescription, record-release, cashier, inventory, and sterilization workflows show transaction, locking, and duplicate/reference safeguards.'
            : 'Review missing source evidence: ' . implode('; ', array_slice((array)$coverage['missing'],0,5)) . '.',
        'Transaction safety','transaction-integrity'
    );

    if (operation_integrity_schema_ready()) {
        $stale = dental_data_integrity_count($pdo,
            'SELECT COUNT(*) FROM `' . table('operation_requests') . '` o WHERE o.status="processing" AND o.expires_at<NOW()'
        );
        $duplicates = dental_data_integrity_count($pdo,
            'SELECT COALESCE(SUM(o.duplicate_count),0) FROM `' . table('operation_requests') . '` o WHERE o.created_at>=DATE_SUB(NOW(),INTERVAL 24 HOUR)'
        );
        $checks[] = dental_data_integrity_check(
            'request_ledger_health','Request-integrity ledger',$stale > 0 ? 'review' : 'pass',
            $stale . ' stale · ' . $duplicates . ' duplicate submissions blocked',
            $stale > 0
                ? 'Expired processing rows are safe to mark failed from the Data Integrity Center; business records remain authoritative.'
                : 'No expired processing rows were detected. Duplicate attempts are tracked without storing clinical payloads.',
            'Transaction safety','data-integrity-center'
        );
    }

    $counts = ['pass'=>0,'review'=>0,'blocked'=>0,'total'=>count($checks)];
    foreach ($checks as $check) $counts[(string)$check['status']]++;
    $overall = $counts['blocked'] > 0 ? 'blocked' : ($counts['review'] > 0 ? 'review' : 'pass');
    $canonical = [
        'release'=>app_release_version(),
        'installed'=>installed_application_version(),
        'overall'=>$overall,
        'checks'=>array_map(static fn(array $row): array => [
            'key'=>(string)$row['key'],'status'=>(string)$row['status'],'metric'=>(string)$row['metric'],
        ], $checks),
    ];
    $encoded = json_encode($canonical, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);

    return [
        'release'=>app_release_version(),
        'installed'=>installed_application_version(),
        'overall'=>$overall,
        'counts'=>$counts,
        'checks'=>$checks,
        'fingerprint'=>is_string($encoded) ? hash('sha256',$encoded) : '',
        'generated_at'=>date(DATE_ATOM),
    ];
}

function dental_data_integrity_record_snapshot(PDO $pdo, array $snapshot): int
{
    if (!dental_data_integrity_table_ready()) throw new RuntimeException('Apply DentalCare v1.0.0.0 before recording data-integrity evidence.');
    $encoded = json_encode($snapshot, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
    if (!is_string($encoded) || $encoded === '') throw new RuntimeException('Data-integrity evidence could not be encoded.');
    $userId = (int)(current_user()['id'] ?? 0);
    $stmt = $pdo->prepare(
        'INSERT INTO `' . table('data_integrity_snapshots') . '` '
        . '(release_version,installed_version,overall_status,pass_count,review_count,blocked_count,fingerprint_sha256,snapshot_json,created_by_user_id,created_at) '
        . 'VALUES (?,?,?,?,?,?,?,?,?,NOW())'
    );
    $stmt->execute([
        (string)($snapshot['release'] ?? app_release_version()),
        (string)($snapshot['installed'] ?? installed_application_version()),
        (string)($snapshot['overall'] ?? 'review'),
        (int)($snapshot['counts']['pass'] ?? 0),
        (int)($snapshot['counts']['review'] ?? 0),
        (int)($snapshot['counts']['blocked'] ?? 0),
        (string)($snapshot['fingerprint'] ?? ''),
        $encoded,
        $userId > 0 ? $userId : null,
    ]);
    return (int)$pdo->lastInsertId();
}

/** @return array<int,array<string,mixed>> */
function dental_data_integrity_recent_snapshots(PDO $pdo, int $limit = 20): array
{
    if (!dental_data_integrity_table_ready()) return [];
    $limit = max(1,min(100,$limit));
    return (array)dental_data_integrity_safe(static function () use ($pdo,$limit): array {
        return $pdo->query(
            'SELECT s.*,u.name creator_name FROM `' . table('data_integrity_snapshots') . '` s '
            . 'LEFT JOIN `' . table('users') . '` u ON u.id=s.created_by_user_id '
            . 'ORDER BY s.id DESC LIMIT ' . $limit
        )->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }, []);
}

function dental_data_integrity_recover_stale_operations(PDO $pdo): int
{
    if (!operation_integrity_schema_ready()) return 0;
    $stmt = $pdo->prepare(
        'UPDATE `' . table('operation_requests') . '` SET '
        . 'status="failed",last_error_code="STALE_RECOVERED",last_error_message="Expired processing ledger entry recovered by an authorized administrator.",'
        . 'expires_at=NOW(),updated_at=NOW() '
        . 'WHERE status="processing" AND expires_at<NOW()'
    );
    $stmt->execute();
    return $stmt->rowCount();
}

function dental_data_integrity_purge_operation_metadata(PDO $pdo, int $retentionDays = 90): int
{
    if (!operation_integrity_schema_ready()) return 0;
    $retentionDays = max(30,min(3650,$retentionDays));
    $stmt = $pdo->prepare(
        'DELETE FROM `' . table('operation_requests') . '` '
        . 'WHERE status="completed" AND completed_at IS NOT NULL AND completed_at<DATE_SUB(NOW(),INTERVAL ' . $retentionDays . ' DAY)'
    );
    $stmt->execute();
    return $stmt->rowCount();
}

function dental_data_integrity_health_check(PDO $pdo): array
{
    if (!is_file(DENTAL_ROOT . '/app/data_integrity.php')) {
        return dental_data_integrity_check('production_data_integrity','Production data integrity','blocked','Helper missing','The v1.0.0.0 data-integrity helper is missing.');
    }
    $snapshot = dental_data_integrity_snapshot($pdo);
    $overall = (string)($snapshot['overall'] ?? 'blocked');
    return [
        'key'=>'production_data_integrity',
        'label'=>'Production data integrity',
        'status'=>$overall,
        'metric'=>(int)($snapshot['counts']['pass'] ?? 0) . ' pass · ' . (int)($snapshot['counts']['review'] ?? 0) . ' review · ' . (int)($snapshot['counts']['blocked'] ?? 0) . ' blocked',
        'detail'=>$overall === 'pass'
            ? 'Database contracts, query safety, referential checks, and critical transaction evidence passed.'
            : ($overall === 'blocked'
                ? 'A core database or query-safety contract is blocked. Open Data Integrity Center before clinical use.'
                : 'Core contracts are available, but one or more data-quality findings require authorized review.'),
        'blocking'=>$overall === 'blocked',
        'link'=>'data-integrity-center',
    ];
}
