<?php

declare(strict_types=1);

// Deliberately SELECT-only. No HTTP, no token, no DML, no export file.
$lane = (string)getenv('CDX_SUBSIDY_LANE');
$dialect = $lane === 'C' ? 'dm' : 'mysql';
if (!in_array($lane, ['A', 'B', 'C'], true)
    || ($argv[1] ?? '') !== '--preflight-only') {
    fwrite(STDERR, "Subsidy preflight only; execution is disabled\n");
    exit(78);
}

$getConfig = static function (string $path): array {
    $config = require $path;
    if (!is_array($config)) throw new RuntimeException('Invalid DB configuration');
    return $config;
};
if ($dialect === 'mysql') {
    $cfg = $getConfig('/workspace/zhct/zhctproject/store/application/config/local/database.php');
    if (!in_array((string)($cfg['type'] ?? 'mysql'), ['mysql', 'mysqli'], true)) {
        throw new RuntimeException('Not the approved local MySQL driver');
    }
    $aiCfg = array_merge($cfg, (array)($cfg['ai_zhct'] ?? []));
    $makeDsn = static function (array $row): string {
        return 'mysql:host=' . $row['hostname'] . ';port=' . (int)($row['hostport'] ?? 3306)
            . ';dbname=' . $row['database'] . ';charset=' . ($row['charset'] ?? 'utf8');
    };
    $storeDsn = $makeDsn($cfg);
    $aiDsn = $makeDsn($aiCfg);
} else {
    if ((string)getenv('POC_HOST') !== '127.0.0.1'
        || (string)getenv('POC_PORT') !== '5237'
        || strtoupper((string)getenv('POC_SCHEMA')) !== 'ZHCT_APP'
        || strtoupper((string)getenv('POC_USER')) !== 'ZHCT_APP'
        || (string)getenv('POC_AI_HOST') !== '127.0.0.1'
        || (string)getenv('POC_AI_PORT') !== '5238'
        || strtoupper((string)getenv('POC_AI_SCHEMA')) !== 'AIZHCT_APP'
        || strtoupper((string)getenv('POC_AI_USER')) !== 'AIZHCT_APP') {
        throw new RuntimeException('DM target is not the isolated test pair');
    }
    $cfg = ['username' => getenv('POC_USER'), 'password' => getenv('POC_PASSWORD')];
    $aiCfg = ['username' => getenv('POC_AI_USER'), 'password' => getenv('POC_AI_PASSWORD')];
    $storeDsn = 'dm:host=127.0.0.1;port=5237;schema=ZHCT_APP';
    $aiDsn = 'dm:host=127.0.0.1;port=5238;schema=AIZHCT_APP';
    $machineId = trim((string)@file_get_contents('/etc/machine-id'));
    if ($machineId === '' || gethostname() === false
        || hash('sha256', gethostname() . '|' . $machineId)
            !== 'd56d81988bb0f106946cce91c608fdd0ef1116e5150b23376ea87e086eace4b9') {
        throw new RuntimeException('DM host identity does not match approved test machine');
    }
}

$connect = static function (string $dsn, array $config): PDO {
    try {
        return new PDO($dsn, (string)$config['username'], (string)$config['password'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_CASE => PDO::CASE_LOWER,
            PDO::ATTR_TIMEOUT => 5,
        ]);
    } catch (Throwable $e) {
        throw new RuntimeException('Approved DB connection failed: ' . get_class($e));
    }
};
$store = $connect($storeDsn, $cfg);
$ai = $connect($aiDsn, $aiCfg);
$mysqlIdentitySql = "SELECT CONCAT(@@server_uuid,'|',@@hostname,'|',@@port,'|',DATABASE())";
$storeIdentity = $dialect === 'mysql'
    ? (string)$store->query($mysqlIdentitySql)->fetchColumn()
    : 'ZHCT_APP|5237|' . (string)$store->query(
        "SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL")->fetchColumn();
$aiIdentity = $dialect === 'mysql'
    ? (string)$ai->query($mysqlIdentitySql)->fetchColumn()
    : 'AIZHCT_APP|5238|' . (string)$ai->query(
        "SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL")->fetchColumn();
$approved = $dialect === 'mysql'
    ? ['003eb5aecf1cd4595b609c1f7f20b9ee325b578309fb9fe14fedaf77584fa401',
        '210f925e093f71e26a8c8923f0f626e3be0a15ccff3d386bba21feef695c8a5b']
    : ['bc98485aed26beab348770a82ae38dbf922b04ab88a210cd7282c4caf0156194',
        '628c12663da929e593de74c9c58e5bcfab0530fbfbcb1d90a9ed7b736a45fd5e'];
if (!hash_equals($approved[0], hash('sha256', $storeIdentity))
    || !hash_equals($approved[1], hash('sha256', $aiIdentity))) {
    throw new RuntimeException('Database identity differs from approved local/test pair');
}

$s = $dialect === 'dm' ? 'YDY_' : 'ydy_';
$a = $dialect === 'dm' ? 'YOSHOP_' : 'yoshop_';
$userTable = $s . ($dialect === 'dm' ? 'USER' : 'user');
$staffTable = $s . ($dialect === 'dm' ? 'STAFF' : 'staff');
$subsidyTable = $a . ($dialect === 'dm' ? 'USER_SUBSIDY' : 'user_subsidy');
$candidates = $dialect === 'dm'
    ? ['CDXPAY03721E84']
    : ['CDXPAYD914599B', 'CDXPAY7E0B0221'];
$staffSql = 'SELECT u.ACCOUNT,s.NAME,s.AI_USER_ID,s.IS_SHOW FROM ' . $userTable . ' u'
    . ' JOIN ' . $staffTable . ' s ON s.UUID=u.STAFF_UUID'
    . ' WHERE u.ACCOUNT=? AND s.NAME=? AND u.IS_DELETE=0';
$staffStmt = $store->prepare($staffSql);
$nameStmt = $store->prepare('SELECT COUNT(*) FROM ' . $staffTable . ' WHERE NAME=?');
$likeNameStmt = $store->prepare('SELECT COUNT(*) FROM ' . $staffTable . ' WHERE NAME LIKE ?');
$operatorStmt = $store->prepare('SELECT COUNT(*) FROM ' . $userTable
    . ' WHERE TYPE=0 AND IS_DELETE=0 AND ACCOUNT LIKE ?');
$operatorStmt->execute(['CDX%']);
$syntheticOperatorCount = (int)$operatorStmt->fetchColumn();
$operatorStmt->execute(['admin']);
$adminOperatorCount = (int)$operatorStmt->fetchColumn();
$sourceSql = 'SELECT ID,USER_ID,MONEY,USED_MONEY,PUSH_DATE,IS_DELETE FROM '
    . $subsidyTable . ' WHERE USER_ID=? AND IS_DELETE=0 ORDER BY ID DESC'
    . ($dialect === 'dm' ? ' FETCH FIRST 3 ROWS ONLY' : ' LIMIT 3');
$sourceStmt = $ai->prepare($sourceSql);
$toString = static function ($value): string {
    return is_resource($value) ? (string)stream_get_contents($value) : (string)$value;
};
$results = [];
foreach ($candidates as $candidate) {
    $staffStmt->execute([$candidate, $candidate]);
    $staffRows = $staffStmt->fetchAll(PDO::FETCH_ASSOC);
    $nameStmt->execute([$candidate]);
    $nameCount = (int)$nameStmt->fetchColumn();
    $likeNameStmt->execute(['%' . $candidate . '%']);
    $likeNameCount = (int)$likeNameStmt->fetchColumn();
    $state = ['marker' => $candidate, 'staff_rows' => count($staffRows),
        'exact_name_count' => $nameCount, 'like_name_count' => $likeNameCount,
        'is_show' => null,
        'ai_user_id_positive' => false, 'subsidy_rows_bounded' => 0,
        'unique_push_date' => false, 'date' => null,
        'money_2dp' => null, 'used_money_2dp' => null,
        'source_digest' => null];
    if (count($staffRows) === 1) {
        $staff = $staffRows[0];
        $userId = (int)$staff['ai_user_id'];
        $state['is_show'] = (int)$staff['is_show'];
        $state['ai_user_id_positive'] = $userId > 0;
        if ($userId > 0 && preg_match('/^CDXPAY[A-F0-9]{8}$/D', $candidate)) {
            $sourceStmt->execute([$userId]);
            $sourceRows = $sourceStmt->fetchAll(PDO::FETCH_ASSOC);
            $state['subsidy_rows_bounded'] = count($sourceRows);
            if (count($sourceRows) === 1) {
                $row = $sourceRows[0];
                $date = substr($toString($row['push_date']), 0, 10);
                $state['unique_push_date'] = (bool)preg_match('/^\d{4}-\d{2}-\d{2}$/D', $date);
                $state['date'] = $state['unique_push_date'] ? $date : null;
                $state['money_2dp'] = number_format((float)$toString($row['money']), 2, '.', '');
                $state['used_money_2dp'] = number_format((float)$toString($row['used_money']), 2, '.', '');
                $state['source_digest'] = hash('sha256', implode('|', array_map($toString, $row)));
            }
        }
    }
    $results[] = $state;
}
echo json_encode(['lane' => $lane, 'mode' => 'select-only',
    'store_fingerprint' => hash('sha256', $storeIdentity),
    'ai_fingerprint' => hash('sha256', $aiIdentity),
    'candidate_count' => count($candidates),
    'existing_test_operator_count' => $syntheticOperatorCount,
    'existing_admin_operator_count' => $adminOperatorCount,
    'candidates' => $results],
    JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL;