<?php

declare(strict_types=1);

// REVIEW STAGE ONLY. This file contains SELECTs; --execute and all other modes
// are deliberately rejected before a database connection is opened.
$mode = (string)($argv[1] ?? '');
$recoveryOrderNo = (string)($argv[2] ?? '');
if (!in_array($mode, ['--audit', '--recover-audit'], true)
    || ($mode === '--recover-audit'
        && !preg_match('/^CDXPAROM[A-F0-9]{8}A$/D', $recoveryOrderNo))) {
    fwrite(STDERR, "Order-machine fixture DML is disabled pending review\n");
    exit(78);
}
$localPath = '/workspace/zhct/zhctproject/store/application/config/local/database.php';
if (!is_file($localPath)) throw new RuntimeException('Code local DB config absent');
$local = require $localPath;
$runtimeA = require '/tmp/zhct-http-store-a.l5omH8/application/database.php';
$runtimeB = require '/tmp/zhct-http-store-mysql.dQTTBP/application/database.php';
if (!is_array($local) || !is_array($runtimeA) || !is_array($runtimeB)
    || (string)($runtimeA['type'] ?? '') !== 'mysql'
    || (string)($runtimeB['type'] ?? '') !== 'mysql'
    || (string)($runtimeA['prefix'] ?? '') !== 'ydy_'
    || (string)($runtimeB['prefix'] ?? '') !== 'ydy_') {
    throw new RuntimeException('Only approved code-local Store MySQL is auditable');
}
foreach (['hostname', 'database', 'username', 'password'] as $key) {
    if (($runtimeA[$key] ?? null) !== ($local[$key] ?? null)
        || ($runtimeB[$key] ?? null) !== ($local[$key] ?? null)) {
        throw new RuntimeException('Isolated A/B differs from code local DB');
    }
}
if ((int)($runtimeA['hostport'] ?? 3306) !== (int)($runtimeB['hostport'] ?? 3306)) {
    throw new RuntimeException('Isolated A/B DB port mismatch');
}
$config = array_replace($runtimeA, $local);
$dsn = 'mysql:host=' . (string)$config['hostname']
    . ';port=' . (int)($config['hostport'] ?? 3306)
    . ';dbname=' . (string)$config['database']
    . ';charset=' . (string)($config['charset'] ?? 'utf8');
$pdo = new PDO($dsn, (string)$config['username'], (string)$config['password'],
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_CASE => PDO::CASE_LOWER,
        PDO::ATTR_TIMEOUT => 5]);
$identity = (string)$pdo->query(
    "SELECT CONCAT(@@server_uuid,'|',@@hostname,'|',@@port,'|',DATABASE())"
)->fetchColumn();
if (!hash_equals('003eb5aecf1cd4595b609c1f7f20b9ee325b578309fb9fe14fedaf77584fa401',
    hash('sha256', $identity))) {
    throw new RuntimeException('Not approved local development MySQL');
}
if ($mode === '--recover-audit') {
    $stmt = $pdo->prepare('SELECT id,user_id,restaurant_id,order_no,pay_status,'
        . 'print_status,pay_price,create_time FROM ydy_meal_order WHERE order_no=?');
    $stmt->execute([$recoveryOrderNo]);
    $orders = $stmt->fetchAll();
    $stmt = $pdo->prepare('SELECT id,restaurant_id,meal_order_id,order_no,'
        . 'meal_date,meal_times,prepare_status,pickup_no FROM ydy_meal_order_pickup'
        . ' WHERE order_no=?');
    $stmt->execute([$recoveryOrderNo]);
    $pickups = $stmt->fetchAll();
    if (count($orders) > 1 || count($pickups) > 1) {
        throw new RuntimeException('Recovery marker cardinality is not safe');
    }
    $safeOrders = array_map(static function (array $row): array {
        return [
            'id' => (int)$row['id'], 'user_id' => (int)$row['user_id'],
            'restaurant_id' => (int)$row['restaurant_id'],
            'pay_status' => (int)$row['pay_status'],
            'print_status' => (int)$row['print_status'],
            'pay_price' => (string)$row['pay_price'],
            'create_time' => (string)$row['create_time'],
        ];
    }, $orders);
    $safePickups = array_map(static function (array $row): array {
        return [
            'id' => (int)$row['id'], 'restaurant_id' => (int)$row['restaurant_id'],
            'meal_order_id' => (int)$row['meal_order_id'],
            'meal_date' => (string)$row['meal_date'],
            'meal_times' => (int)$row['meal_times'],
            'prepare_status' => (int)$row['prepare_status'],
            'pickup_no_sha' => hash('sha256', (string)$row['pickup_no']),
        ];
    }, $pickups);
    echo json_encode([
        'mode' => 'recovery_audit_only_no_delete',
        'marker_sha' => hash('sha256', $recoveryOrderNo),
        'order_count' => count($safeOrders), 'pickup_count' => count($safePickups),
        'orders' => $safeOrders, 'pickups' => $safePickups,
    ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), PHP_EOL;
    exit(0);
}
date_default_timezone_set('PRC');
$today = date('Y-m-d');
$recentFrom = date('Y-m-d 00:00:00', strtotime('-2 day'));
$scalar = static function (string $sql, array $params = []) use ($pdo): int {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return (int)$stmt->fetchColumn();
};
$counts = [
    'recent_paid_pickup' => $scalar(
        'SELECT COUNT(*) FROM ydy_meal_order m JOIN ydy_meal_order_pickup p'
        . ' ON p.meal_order_id=m.id WHERE m.is_delete=0 AND m.pay_status=20'
        . ' AND m.create_time>=?', [$recentFrom]),
    'recent_paid_cdxpay_without_pickup' => $scalar(
        'SELECT COUNT(*) FROM ydy_meal_order m JOIN ydy_user u ON u.id=m.user_id'
        . ' LEFT JOIN ydy_meal_order_pickup p ON p.meal_order_id=m.id'
        . " WHERE u.account LIKE 'CDXPAY%' AND u.is_delete=0"
        . ' AND m.is_delete=0 AND m.pay_status=20 AND m.create_time>=?'
        . ' AND p.id IS NULL', [$recentFrom]),
    'historical_paid_cdxpay' => $scalar(
        'SELECT COUNT(*) FROM ydy_meal_order m JOIN ydy_user u ON u.id=m.user_id'
        . " WHERE u.account LIKE 'CDXPAY%' AND u.is_delete=0"
        . ' AND m.is_delete=0 AND m.pay_status=20 AND m.create_time<?', [$recentFrom]),
    'fixture_order_prefix' => $scalar(
        "SELECT COUNT(*) FROM ydy_meal_order WHERE order_no LIKE 'CDXPAROM%'"),
    'fixture_pickup_prefix' => $scalar(
        "SELECT COUNT(*) FROM ydy_meal_order_pickup WHERE order_no LIKE 'CDXPAROM%'"),
    'today_prepare_or_ready' => $scalar(
        "SELECT COUNT(*) FROM ydy_meal_order_pickup p JOIN ydy_restaurants r"
        . ' ON r.id=p.restaurant_id WHERE p.meal_date=?'
        . ' AND p.prepare_status IN(10,20) AND p.pickup_no<>\'\''
        . ' AND r.is_delete=2 AND r.status=1', [$today]),
];
$syntheticUsers = $pdo->query(
    "SELECT id,account,name,type,status,ai_user_id FROM ydy_user WHERE account LIKE 'CDXPAY%'"
    . ' AND is_delete=0 ORDER BY id DESC'
);
$eligibleUsers = [];
$eligibleAiIds = [];
foreach ($syntheticUsers as $row) {
    $account = (string)$row['account'];
    if (preg_match('/^CDXPAY[A-F0-9]{8}$/D', $account)
        && (string)$row['name'] === $account
        && (int)$row['type'] === 1 && (int)$row['status'] === 1) {
        $eligibleUsers[] = (int)$row['id'];
        $eligibleAiIds[(int)$row['id']] = (int)$row['ai_user_id'];
    }
}
$metadata = [];
$stmt = $pdo->prepare('SELECT TABLE_NAME,COLUMN_NAME,IS_NULLABLE,COLUMN_DEFAULT,'
    . 'DATA_TYPE,EXTRA,CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS'
    . ' WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?)'
    . ' ORDER BY TABLE_NAME,ORDINAL_POSITION');
$stmt->execute(['ydy_meal_order', 'ydy_meal_order_pickup']);
foreach ($stmt as $row) {
    $table = (string)$row['table_name'];
    $metadata[$table][] = [
        'name' => (string)$row['column_name'],
        'nullable' => (string)$row['is_nullable'],
        'default_present' => $row['column_default'] !== null,
        'type' => (string)$row['data_type'],
        'extra' => (string)$row['extra'],
        'max_length' => $row['character_maximum_length'] === null
            ? null : (int)$row['character_maximum_length'],
    ];
}
$triggerCount = $scalar('SELECT COUNT(*) FROM information_schema.TRIGGERS'
    . ' WHERE TRIGGER_SCHEMA=DATABASE() AND EVENT_OBJECT_TABLE IN (?,?)',
    ['ydy_meal_order', 'ydy_meal_order_pickup']);
$fkCount = $scalar('SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE'
    . ' WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?)'
    . ' AND REFERENCED_TABLE_NAME IS NOT NULL',
    ['ydy_meal_order', 'ydy_meal_order_pickup']);
$uniqueIndexes = [];
$stmt = $pdo->prepare('SELECT TABLE_NAME,INDEX_NAME,GROUP_CONCAT(COLUMN_NAME'
    . ' ORDER BY SEQ_IN_INDEX) AS COLS FROM information_schema.STATISTICS'
    . ' WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?) AND NON_UNIQUE=0'
    . ' GROUP BY TABLE_NAME,INDEX_NAME ORDER BY TABLE_NAME,INDEX_NAME');
$stmt->execute(['ydy_meal_order', 'ydy_meal_order_pickup']);
foreach ($stmt as $row) {
    $uniqueIndexes[] = [
        'table' => (string)$row['table_name'],
        'index' => (string)$row['index_name'],
        'columns' => (string)$row['cols'],
    ];
}
$activeType2 = $pdo->query('SELECT r.id,r.name,r.morning_start,r.morning_end,'
    . 'r.noon_start,r.noon_end,r.night_start,r.night_end,'
    . '(SELECT COUNT(*) FROM ydy_equipment e WHERE e.restaurant_id=r.id'
    . ' AND e.del_flag=0) AS equipment_count'
    . ' FROM ydy_restaurants r WHERE r.is_delete=2 AND r.status=1'
    . ' AND r.stall_type=2 ORDER BY equipment_count ASC,r.id ASC');
$restaurantCandidates = [];
$firstDeviceFree = null;
foreach ($activeType2 as $row) {
    if ($firstDeviceFree === null && (int)$row['equipment_count'] === 0) {
        $firstDeviceFree = $row;
    }
    $restaurantCandidates[] = [
        'id' => (int)$row['id'],
        'name_is_synthetic' => preg_match('/^CDX[A-Z0-9]*/D', (string)$row['name']) === 1,
        'active_equipment_count' => (int)$row['equipment_count'],
        'hours_present' => trim((string)$row['morning_start']) !== ''
            && trim((string)$row['noon_start']) !== ''
            && trim((string)$row['night_start']) !== '',
    ];
    if (count($restaurantCandidates) >= 30) break;
}
$candidateContext = null;
if ($firstDeviceFree !== null) {
    $clock = date('H:i:s');
    $mealTimes = 4;
    foreach ([1 => ['morning_start', 'morning_end'],
        2 => ['noon_start', 'noon_end'], 3 => ['night_start', 'night_end']] as $meal => $fields) {
        if ($clock >= (string)$firstDeviceFree[$fields[0]] . ':00'
            && $clock <= (string)$firstDeviceFree[$fields[1]] . ':59') {
            $mealTimes = $meal;
            break;
        }
    }
    $candidateContext = [
        'restaurant_id' => (int)$firstDeviceFree['id'],
        'active_equipment_count' => 0,
        'current_meal_times' => $mealTimes,
        'today_pickup_rows' => $scalar(
            'SELECT COUNT(*) FROM ydy_meal_order_pickup WHERE restaurant_id=?'
            . ' AND meal_date=?', [(int)$firstDeviceFree['id'], $today]),
        'last_30_days_pickup_rows' => $scalar(
            'SELECT COUNT(*) FROM ydy_meal_order_pickup WHERE restaurant_id=?'
            . ' AND meal_date>=?', [(int)$firstDeviceFree['id'],
                date('Y-m-d', strtotime('-30 day'))]),
    ];
}
$requiredWithoutDefault = [];
$selectedDefaults = [];
foreach ($metadata as $table => $columns) {
    foreach ($columns as $column) {
        if ($column['nullable'] === 'NO' && !$column['default_present']
            && stripos($column['extra'], 'auto_increment') === false) {
            $requiredWithoutDefault[$table][] = $column['name'];
        }
    }
}
$stmt = $pdo->prepare('SELECT TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,EXTRA'
    . ' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()'
    . ' AND TABLE_NAME IN (?,?) AND COLUMN_NAME IN (?,?,?,?,?,?,?,?,?,?,?,?)'
    . ' ORDER BY TABLE_NAME,COLUMN_NAME');
$stmt->execute(['ydy_meal_order', 'ydy_meal_order_pickup', 'id', 'order_no',
    'supplement_id', 'supplement_no', 'pay_status', 'pay_price', 'source',
    'meal_date', 'meal_times', 'prepare_status', 'pickup_no', 'print_status']);
foreach ($stmt as $column) {
    $selectedDefaults[(string)$column['table_name']][(string)$column['column_name']] = [
        'default' => $column['column_default'],
        'nullable' => (string)$column['is_nullable'],
        'extra' => (string)$column['extra'],
    ];
}
$tableEngines = [];
$stmt = $pdo->prepare('SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES'
    . ' WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?)');
$stmt->execute(['ydy_meal_order', 'ydy_meal_order_pickup']);
foreach ($stmt as $row) $tableEngines[(string)$row['table_name']] = (string)$row['engine'];
echo json_encode([
    'mode' => 'audit_only_dml_disabled', 'date' => $today,
    'recent_from' => $recentFrom, 'counts' => $counts,
    'eligible_existing_cdxpay_users' => count($eligibleUsers),
    'eligible_user_id_sample' => array_slice($eligibleUsers, 0, 3),
    'eligible_user_ai_id_sample' => array_map(static function ($id) use ($eligibleAiIds): int {
        return $eligibleAiIds[$id] ?? 0;
    }, array_slice($eligibleUsers, 0, 3)),
    'table_column_counts' => array_map('count', $metadata),
    'schema_fingerprint_sha' => hash('sha256', serialize([
        $metadata, $uniqueIndexes, $triggerCount, $fkCount, $tableEngines,
    ])),
    'required_without_default' => $requiredWithoutDefault,
    'selected_defaults' => $selectedDefaults, 'table_engines' => $tableEngines,
    'unique_indexes' => $uniqueIndexes,
    'trigger_count' => $triggerCount, 'outgoing_fk_count' => $fkCount,
    'active_type2_restaurant_sample' => $restaurantCandidates,
    'device_free_restaurant_candidate' => $candidateContext,
], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), PHP_EOL;