<?php

declare(strict_types=1);

/**
 * Read-only query-regression checks introduced in DentalCare v0.9.4.0.
 *
 * The checks deliberately select only identifiers or constants. They do not
 * read clinical notes, dental findings, payment details, credentials, or file
 * contents. Their purpose is to catch SQL alias ambiguity and missing critical
 * joined-query dependencies before the same failure reaches a staff workflow.
 */

function dental_query_integrity_result(
    string $key,
    string $label,
    string $status,
    string $detail,
    bool $blocking = true
): array {
    return [
        'key' => $key,
        'label' => $label,
        'status' => $status,
        'detail' => $detail,
        'blocking' => $blocking && $status === 'blocked',
    ];
}

function dental_query_integrity_safe_error(Throwable $exception): string
{
    $message = trim(preg_replace('/\s+/', ' ', $exception->getMessage()) ?? '');
    if ($message === '') $message = get_class($exception);
    return mb_substr($message, 0, 220);
}

function dental_query_integrity_source_checks(): array
{
    $path = DENTAL_ROOT . '/modules/administration/transaction_integrity.php';
    if (!is_file($path)) {
        return [dental_query_integrity_result(
            'transaction_integrity_source',
            'Transaction-integrity query source',
            'blocked',
            'The transaction-integrity module is missing from this release.'
        )];
    }

    $source = @file_get_contents($path);
    if (!is_string($source) || $source === '') {
        return [dental_query_integrity_result(
            'transaction_integrity_source',
            'Transaction-integrity query source',
            'blocked',
            'The transaction-integrity module could not be read for regression validation.'
        )];
    }

    $required = [
        'o.created_at>=DATE_SUB',
        "o.branch_id=?",
        "o.status=?",
        'o.route_name LIKE ?',
        'o.action_name LIKE ?',
        'o.entity_type LIKE ?',
        'o.response_reference LIKE ?',
        'o.last_error_message LIKE ?',
    ];
    $missing = [];
    foreach ($required as $token) {
        if (!str_contains($source, $token)) $missing[] = $token;
    }

    $forbiddenPatterns = [
        '/\$where\s*=\s*\[\s*[\'\"]created_at\s*>=/i',
        '/\$where\s*\[\]\s*=\s*[\'\"]branch_id\s*=\s*\?/i',
        '/\$where\s*\[\]\s*=\s*[\'\"]status\s*=\s*\?/i',
        '/\(route_name\s+LIKE\s+\?/i',
    ];
    $unsafe = [];
    foreach ($forbiddenPatterns as $pattern) {
        if (preg_match($pattern, $source) === 1) $unsafe[] = $pattern;
    }

    if ($missing || $unsafe) {
        $parts = [];
        if ($missing) $parts[] = 'Missing alias-qualified tokens: ' . implode(', ', $missing);
        if ($unsafe) $parts[] = 'Unqualified joined filters remain in the module.';
        return [dental_query_integrity_result(
            'transaction_integrity_source',
            'Transaction-integrity query source',
            'blocked',
            implode(' ', $parts)
        )];
    }

    return [dental_query_integrity_result(
        'transaction_integrity_source',
        'Transaction-integrity query source',
        'pass',
        'All joined filters and searchable fields use the operation-request alias.'
    )];
}

function dental_query_integrity_probe(
    PDO $pdo,
    string $key,
    string $label,
    callable $callback
): array {
    try {
        $callback($pdo);
        return dental_query_integrity_result($key, $label, 'pass', 'The read-only joined query prepared and executed successfully.');
    } catch (Throwable $exception) {
        return dental_query_integrity_result(
            $key,
            $label,
            'blocked',
            'Query regression detected: ' . dental_query_integrity_safe_error($exception)
        );
    }
}

function dental_query_integrity_database_checks(PDO $pdo): array
{
    $checks = [];

    $checks[] = dental_query_integrity_probe(
        $pdo,
        'operation_request_join',
        'Protected-operation ledger join',
        static function (PDO $pdo): void {
            $operationTable = table('operation_requests');
            $usersTable = table('users');
            $branchesTable = table('branches');
            if (!schema_table_exists($operationTable)
                || !schema_table_exists($usersTable)
                || !schema_table_exists($branchesTable)) {
                throw new RuntimeException('Required transaction-integrity tables are unavailable.');
            }
            $sql = 'SELECT o.id '
                . 'FROM `' . $operationTable . '` o '
                . 'LEFT JOIN `' . $usersTable . '` u ON u.id=o.user_id '
                . 'LEFT JOIN `' . $branchesTable . '` b ON b.id=o.branch_id '
                . 'WHERE o.created_at>=DATE_SUB(NOW(),INTERVAL 24 HOUR) '
                . 'AND o.status IN ("processing","completed","failed") '
                . 'ORDER BY o.updated_at DESC,o.id DESC LIMIT 1';
            $pdo->query($sql)->fetchColumn();
        }
    );

    $checks[] = dental_query_integrity_probe(
        $pdo,
        'operational_health_join',
        'Operational-health evidence join',
        static function (PDO $pdo): void {
            $snapshotTable = table('operational_health_snapshots');
            $usersTable = table('users');
            if (!schema_table_exists($snapshotTable) || !schema_table_exists($usersTable)) {
                throw new RuntimeException('Operational-health evidence tables are unavailable.');
            }
            $sql = 'SELECT s.id '
                . 'FROM `' . $snapshotTable . '` s '
                . 'LEFT JOIN `' . $usersTable . '` u ON u.id=s.created_by_user_id '
                . 'WHERE s.created_at>=DATE_SUB(NOW(),INTERVAL 30 DAY) '
                . 'ORDER BY s.created_at DESC,s.id DESC LIMIT 1';
            $pdo->query($sql)->fetchColumn();
        }
    );

    $checks[] = dental_query_integrity_probe(
        $pdo,
        'migration_ledger_lookup',
        'Current-release migration lookup',
        static function (PDO $pdo): void {
            $ledgerTable = table('system_migration_ledger');
            if (!schema_table_exists($ledgerTable)) {
                throw new RuntimeException('The system migration ledger is unavailable.');
            }
            $manifest = function_exists('dental_hardening_release_manifest')
                ? dental_hardening_release_manifest()
                : [];
            $migrationKey = trim((string)($manifest['migration_key'] ?? ''));
            if ($migrationKey === '') {
                throw new RuntimeException('The current machine release manifest has no migration key.');
            }
            $stmt = $pdo->prepare('SELECT l.status,l.checksum_sha256 FROM `' . $ledgerTable . '` l WHERE l.migration_key=? ORDER BY l.id DESC LIMIT 1');
            $stmt->execute([$migrationKey]);
            // A missing row is valid before the current migration is applied;
            // the regression check is concerned with prepare/execute safety.
            $stmt->fetch(PDO::FETCH_ASSOC);
        }
    );

    return $checks;
}

function dental_query_integrity_snapshot(PDO $pdo): array
{
    $checks = array_merge(
        dental_query_integrity_source_checks(),
        dental_query_integrity_database_checks($pdo)
    );

    $blocked = count(array_filter($checks, static fn(array $row): bool => ($row['status'] ?? '') === 'blocked'));
    $review = count(array_filter($checks, static fn(array $row): bool => ($row['status'] ?? '') === 'review'));
    $pass = count(array_filter($checks, static fn(array $row): bool => ($row['status'] ?? '') === 'pass'));
    $overall = $blocked > 0 ? 'blocked' : ($review > 0 ? 'review' : 'pass');

    $canonical = array_map(static fn(array $row): array => [
        'key' => (string)($row['key'] ?? ''),
        'status' => (string)($row['status'] ?? ''),
    ], $checks);
    $encoded = json_encode($canonical, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);

    return [
        'overall' => $overall,
        'counts' => ['pass'=>$pass,'review'=>$review,'blocked'=>$blocked,'total'=>count($checks)],
        'checks' => $checks,
        'fingerprint' => is_string($encoded) ? hash('sha256', $encoded) : '',
    ];
}

function dental_query_integrity_health_check(PDO $pdo): array
{
    $snapshot = dental_query_integrity_snapshot($pdo);
    $blocked = (int)($snapshot['counts']['blocked'] ?? 0);
    $passed = (int)($snapshot['counts']['pass'] ?? 0);
    $total = (int)($snapshot['counts']['total'] ?? 0);
    $details = [];
    foreach ((array)($snapshot['checks'] ?? []) as $row) {
        if (($row['status'] ?? '') === 'pass') continue;
        $details[] = (string)($row['label'] ?? 'Query check') . ': ' . (string)($row['detail'] ?? 'Review required.');
    }

    return [
        'key' => 'query_integrity_regression',
        'label' => 'SQL query regression safety',
        'status' => $blocked > 0 ? 'blocked' : 'pass',
        'metric' => $passed . ' / ' . $total . ' passed',
        'detail' => $blocked > 0
            ? implode(' ', array_slice($details, 0, 3))
            : 'Critical joined queries use qualified columns and execute as read-only probes.',
        'blocking' => $blocked > 0,
        'link' => 'system-smoke-test',
    ];
}
