<?php

declare(strict_types=1);

// SELECT-only evidence collector. There is intentionally no HTTP or DML mode.
$lane = (string)getenv('CDX_PLATE_DELETE_LANE');
if (!in_array($lane, ['A', 'B', 'C'], true)
    || ($argv[1] ?? '') !== '--preflight-only') {
    fwrite(STDERR, "Plate-delete writes disabled\n");
    exit(78);
}
$dm = $lane === 'C';
if (!$dm) {
    $cfg = require '/workspace/zhct/zhctproject/store/application/config/local/database.php';
    if (!is_array($cfg)) throw new RuntimeException('Invalid local DB config');
    $dsn = 'mysql:host=' . $cfg['hostname'] . ';port=' . (int)($cfg['hostport'] ?? 3306)
        . ';dbname=' . $cfg['database'] . ';charset=' . ($cfg['charset'] ?? 'utf8');
} 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') {
        throw new RuntimeException('Unexpected DM target');
    }
    $machineId = trim((string)@file_get_contents('/etc/machine-id'));
    if ($machineId === '' || gethostname() === false
        || hash('sha256', gethostname() . '|' . $machineId)
            !== 'd56d81988bb0f106946cce91c608fdd0ef1116e5150b23376ea87e086eace4b9') {
        throw new RuntimeException('Unexpected DM host');
    }
    $cfg = ['username' => getenv('POC_USER'), 'password' => getenv('POC_PASSWORD')];
    $dsn = 'dm:host=127.0.0.1;port=5237;schema=ZHCT_APP';
}
try {
    $db = new PDO($dsn, (string)$cfg['username'], (string)$cfg['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));
}
$identity = $dm
    ? 'ZHCT_APP|5237|' . (string)$db->query(
        "SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL")->fetchColumn()
    : (string)$db->query(
        "SELECT CONCAT(@@server_uuid,'|',@@hostname,'|',@@port,'|',DATABASE())")
        ->fetchColumn();
$approved = $dm
    ? 'bc98485aed26beab348770a82ae38dbf922b04ab88a210cd7282c4caf0156194'
    : '003eb5aecf1cd4595b609c1f7f20b9ee325b578309fb9fe14fedaf77584fa401';
if (!hash_equals($approved, hash('sha256', $identity))) {
    throw new RuntimeException('Unexpected DB identity');
}

$tables = $dm
    ? ['YDY_UPLOAD_FILE', 'YDY_PLATE_DISHES', 'YDY_EQUIPMENT']
    : ['ydy_upload_file', 'ydy_plate_dishes', 'ydy_equipment'];
$metadataSql = $dm
    ? 'SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,NULLABLE FROM USER_TAB_COLUMNS'
        . ' WHERE TABLE_NAME IN (?,?,?) ORDER BY TABLE_NAME,COLUMN_ID'
    : 'SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,IS_NULLABLE AS NULLABLE'
        . ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE()'
        . ' AND TABLE_NAME IN (?,?,?) ORDER BY TABLE_NAME,ORDINAL_POSITION';
$metadataStmt = $db->prepare($metadataSql);
$metadataStmt->execute($tables);
$schema = [];
foreach ($metadataStmt->fetchAll(PDO::FETCH_ASSOC) as $column) {
    $table = strtolower((string)$column['table_name']);
    $schema[$table][] = [strtolower((string)$column['column_name']),
        strtoupper((string)$column['data_type']), (string)$column['nullable']];
}
foreach (array_map('strtolower', $tables) as $table) {
    if (empty($schema[$table])) throw new RuntimeException('Missing expected table schema');
}
$suffix = $dm ? ' FETCH FIRST 5 ROWS ONLY' : ' LIMIT 5';
$fileTable = $tables[0];
$plateTable = $tables[1];
$equipmentTable = $tables[2];
$activeFiles = $db->query('SELECT ID,TYPE,IS_DELETE FROM ' . $fileTable
    . ' WHERE TYPE=1 AND IS_DELETE=0 ORDER BY ID DESC' . $suffix)
    ->fetchAll(PDO::FETCH_ASSOC);
$deletedFiles = $db->query('SELECT ID,TYPE,IS_DELETE FROM ' . $fileTable
    . ' WHERE TYPE=1 AND IS_DELETE=1 ORDER BY ID DESC' . $suffix)
    ->fetchAll(PDO::FETCH_ASSOC);
$equipment = $db->query('SELECT ID,TYPE,DEL_FLAG FROM ' . $equipmentTable
    . ' WHERE TYPE=8 AND DEL_FLAG=0 ORDER BY ID DESC' . $suffix)
    ->fetchAll(PDO::FETCH_ASSOC);
$plates = $db->query('SELECT ID,EQUIPMENT_ID,MENU_DATE,IS_DELETE,'
    . ' MORNING_FILE_ID,NOON_FILE_ID,NIGHT_FILE_ID,NIGHT_SNACK_FILE_ID FROM '
    . $plateTable . ' WHERE IS_DELETE=0 ORDER BY ID DESC' . $suffix)
    ->fetchAll(PDO::FETCH_ASSOC);
$fileLookup = $db->prepare('SELECT IS_DELETE FROM ' . $fileTable . ' WHERE ID=?');
$referenceStates = ['active' => 0, 'deleted' => 0, 'missing' => 0];
foreach ($plates as $plate) {
    foreach (['morning_file_id', 'noon_file_id', 'night_file_id',
        'night_snack_file_id'] as $field) {
        $id = (int)$plate[$field];
        if ($id <= 0) continue;
        $fileLookup->execute([$id]);
        $state = $fileLookup->fetchColumn();
        $referenceStates[$state === false ? 'missing'
            : ((int)$state === 1 ? 'deleted' : 'active')]++;
    }
}
$futureDate = '2099-12-31';
$futureRowCount = null;
if ($equipment) {
    $stmt = $db->prepare('SELECT COUNT(*) FROM ' . $plateTable
        . ' WHERE EQUIPMENT_ID=? AND MENU_DATE=? AND IS_DELETE=0');
    $stmt->execute([(int)$equipment[0]['id'], $futureDate]);
    $futureRowCount = (int)$stmt->fetchColumn();
}
echo json_encode(['lane' => $lane, 'mode' => 'select-only',
    'database_fingerprint' => hash('sha256', $identity),
    'schema' => $schema,
    'active_type1_file_samples' => count($activeFiles),
    'deleted_type1_file_samples' => count($deletedFiles),
    'active_type8_equipment_samples' => count($equipment),
    'active_plate_rows_sampled' => count($plates),
    'sampled_plate_file_references' => $referenceStates,
    'future_date_unoccupied_for_one_existing_type8_equipment'
        => $futureRowCount === 0], JSON_UNESCAPED_SLASHES) . PHP_EOL;