<?php
declare(strict_types=1);

// SELECT-only diagnosis of the paused B cleanup gate. No DML or HTTP.
if (($argv[1] ?? '') !== 'B') exit(78);
$marker = 'CDXCRW33BC20DED86E7';
$root = '/tmp/zhct-http-store-mysql.dQTTBP';
$cfg = require $root . '/application/database.php';
$local = require '/workspace/zhct/zhctproject/store/application/config/local/database.php';
foreach (['hostname', 'database', 'username', 'password'] as $key) {
    if (($cfg[$key] ?? null) !== ($local[$key] ?? null)
        || ($cfg['ai_zhct'][$key] ?? null) !== ($local['ai_zhct'][$key] ?? null)) {
        throw new RuntimeException('not local');
    }
}
$dsn = static function (array $c): string {
    return 'mysql:host=' . $c['hostname'] . ';port=' . ($c['hostport'] ?? 3306)
        . ';dbname=' . $c['database'] . ';charset=utf8mb4';
};
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_CASE => PDO::CASE_LOWER, PDO::ATTR_TIMEOUT => 5];
$store = new PDO($dsn($cfg), (string)$cfg['username'], (string)$cfg['password'], $options);
$ac = $cfg['ai_zhct'];
$ai = new PDO($dsn($ac), (string)$ac['username'], (string)$ac['password'], $options);
$identity = (string)$store->query("SELECT CONCAT(@@server_uuid,'|',@@hostname,'|',@@port,'|',DATABASE())")->fetchColumn();
if (hash('sha256', $identity) !== '003eb5aecf1cd4595b609c1f7f20b9ee325b578309fb9fe14fedaf77584fa401') {
    throw new RuntimeException('identity');
}
$one = static function (PDO $db, string $sql, array $args = []) {
    $s = $db->prepare($sql);
    $s->execute($args);
    return $s->fetchColumn();
};
$u = $ai->prepare('SELECT USER_ID,BALANCE FROM yoshop_user WHERE NICK_NAME=?');
$u->execute([$marker]);
$user = $u->fetch(PDO::FETCH_ASSOC);
$o = $ai->prepare('SELECT ORDER_ID,REFUNDABLE_MONEY,REFUND_MONEY FROM yoshop_recharge_order WHERE ORDER_NO=?');
$o->execute(['R' . $marker]);
$order = $o->fetch(PDO::FETCH_ASSOC);
$userId = (int)($user['user_id'] ?? 0);
$orderId = (int)($order['order_id'] ?? 0);
$out = ['user_found' => $userId > 0, 'recharge_found' => $orderId > 0,
    'balance' => number_format((float)($user['balance'] ?? 0), 2, '.', ''),
    'refundable' => number_format((float)($order['refundable_money'] ?? 0), 2, '.', ''),
    'refunded' => number_format((float)($order['refund_money'] ?? 0), 2, '.', ''),
    'unexpected_references' => [], 'errors' => [], 'catalogs' => []];
$stmt = $ai->prepare("SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
    . " WHERE TABLE_SCHEMA=? AND UPPER(COLUMN_NAME) IN ('USER_ID','RECHARGE_ORDER_ID')"
    . " ORDER BY TABLE_NAME,COLUMN_NAME");
$stmt->execute([(string)$ac['database']]);
$refs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$out['catalogs']['ai_reference_columns'] = count($refs);
$skip = ['YOSHOP_USER' => true, 'YOSHOP_RECHARGE_ORDER' => true,
    'YOSHOP_CASH_REFUND_ORDER' => true, 'YOSHOP_USER_BALANCE_LOG' => true];
foreach ($refs as $ref) {
    $physical = (string)$ref['table_name'];
    $table = strtoupper($physical);
    $column = strtoupper((string)$ref['column_name']);
    if (!preg_match('/^YOSHOP_[A-Z0-9_]+$/', $table)) {
        $out['errors'][] = ['phase' => 'ai_catalog', 'table' => $table];
        continue;
    }
    if ($column === 'USER_ID' && isset($skip[$table])) continue;
    $id = $column === 'USER_ID' ? $userId : $orderId;
    try {
        $n = (int)$one($ai, 'SELECT COUNT(*) FROM ' . $physical . ' WHERE ' . $column . '=?', [$id]);
        if ($n > 0) $out['unexpected_references'][] = ['table' => $table,
            'column' => $column, 'count' => $n];
    } catch (Throwable $e) {
        $out['errors'][] = ['phase' => 'ai_scan', 'table' => $table,
            'column' => $column, 'class' => get_class($e),
            'code' => (string)$e->getCode(),
            'message_sha12' => substr(hash('sha256', $e->getMessage()), 0, 12)];
    }
}
$stmt = $store->prepare("SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
    . " WHERE TABLE_SCHEMA=? AND UPPER(COLUMN_NAME) IN ('STAFF_UUID') ORDER BY TABLE_NAME");
$stmt->execute([(string)$cfg['database']]);
$refs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$out['catalogs']['store_staff_uuid_columns'] = count($refs);
foreach ($refs as $ref) {
    $physical = (string)$ref['table_name'];
    $table = strtoupper($physical);
    if (in_array($table, ['YDY_STAFF', 'YDY_STAFF_DEPARTMENT'], true)) continue;
    if (!preg_match('/^YDY_[A-Z0-9_]+$/', $table)) {
        $out['errors'][] = ['phase' => 'store_catalog', 'table' => $table];
        continue;
    }
    try {
        $n = (int)$one($store, 'SELECT COUNT(*) FROM ' . $physical . ' WHERE STAFF_UUID=?', [$marker]);
        if ($n > 0) $out['unexpected_references'][] = ['table' => $table,
            'column' => 'STAFF_UUID', 'count' => $n];
    } catch (Throwable $e) {
        $out['errors'][] = ['phase' => 'store_scan', 'table' => $table,
            'class' => get_class($e), 'code' => (string)$e->getCode(),
            'message_sha12' => substr(hash('sha256', $e->getMessage()), 0, 12)];
    }
}
$r = $ai->prepare('SELECT ID FROM yoshop_cash_refund_order WHERE STAFF_UUID=? ORDER BY ID');
$r->execute([$marker]);
$refundIds = array_map('intval', $r->fetchAll(PDO::FETCH_COLUMN));
$out['refund_count'] = count($refundIds);
foreach ($refundIds as $refundId) {
    $out['audit_by_target'][] = (int)$one($store,
        'SELECT COUNT(*) FROM ydy_security_audit_log WHERE MODULE=? AND TARGET_ID=? AND USER_UUID<>?',
        ['cash_refund', (string)$refundId, $marker]);
    $out['foreign_refund_logs'][] = (int)$one($ai,
        'SELECT COUNT(*) FROM yoshop_cash_refund_order_log WHERE REFUND_ID=? AND OPERATOR<>?',
        [$refundId, $marker]);
    $out['foreign_balance_logs'][] = (int)$one($ai,
        'SELECT COUNT(*) FROM yoshop_user_balance_log WHERE ORDER_ID=? AND SCENE=60 AND USER_ID<>?',
        [$refundId, $userId]);
}
echo json_encode($out, JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE), PHP_EOL;