<?php

declare(strict_types=1);

// Rebuild one A-export business row offline from approved MySQL source.
// No HTTP, actor, DML, queue, or original XLS recovery.
$cfg = require '/workspace/zhct/zhctproject/store/application/config/local/database.php';
$pdo = new PDO('mysql:host=' . $cfg['hostname'] . ';port='
    . (int)($cfg['hostport'] ?? 3306) . ';dbname=' . $cfg['database']
    . ';charset=utf8', (string)$cfg['username'], (string)$cfg['password'],
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_TIMEOUT => 5]);
$identity = (string)$pdo->query(
    "SELECT CONCAT(@@server_uuid,'|',@@hostname,'|',@@port,'|',DATABASE())"
)->fetchColumn();
if (hash('sha256', $identity)
    !== '003eb5aecf1cd4595b609c1f7f20b9ee325b578309fb9fe14fedaf77584fa401') {
    throw new RuntimeException('Approved development MySQL identity mismatch');
}
$sql = 'SELECT DATE(meal.BIND_TIME) AS BIND_DATE,meal.MEAL_DATE,'
    . ' u.NAME AS USER_NAME,dep.NAME AS DEPARTMENT_NAME,u.MOBILE,meal.ORDER_NO,'
    . ' meal.TOTAL_PRICE,(meal.PAY_PRICE-meal.REFUND_AMOUNT) AS PAY_AMOUNT,'
    . ' meal.REMARK,d.NAME AS DISHES_NAME,menu.NUMBER AS DISH_NUMBER,'
    . ' menu.TOTAL_PRICE AS DISH_TOTAL_PRICE,'
    . ' CASE WHEN menu.NUMBER=0 THEN 0 ELSE ROUND(menu.TOTAL_PRICE/menu.NUMBER,2)'
    . ' END AS UNIT_PRICE FROM ydy_meal_order meal'
    . ' JOIN ydy_user u ON u.ID=meal.USER_ID'
    . ' JOIN (SELECT x.STAFF_UUID,x.DEPARTMENT_UUID FROM ydy_staff_department x'
    . ' JOIN (SELECT STAFF_UUID,MIN(ID) MIN_ID FROM ydy_staff_department'
    . ' GROUP BY STAFF_UUID) y ON y.MIN_ID=x.ID) sd ON sd.STAFF_UUID=u.STAFF_UUID'
    . ' JOIN ydy_department dep ON dep.UUID=sd.DEPARTMENT_UUID AND dep.IS_SHOW=1'
    . ' JOIN ydy_initial_menu menu ON menu.MEAL_ORDER_ID=meal.ID'
    . ' LEFT JOIN ydy_dishes d ON d.UUID=menu.DISHES_UUID'
    . ' WHERE meal.ORDER_STATUS=30 AND meal.REFUND_STATUS IN (0,1,2)'
    . ' AND meal.IS_DELETE=0 AND u.IS_DELETE=0'
    . " AND menu.DISHES_UUID NOT IN ('custom_dish','package_fee')"
    . ' AND meal.RESTAURANT_ID=1 AND meal.MEAL_DATE=?'
    . ' AND meal.BIND_TIME BETWEEN ? AND ? ORDER BY menu.ID LIMIT 12';
$stmt = $pdo->prepare($sql);
$stmt->execute(['2026-09-18', '2026-09-18 00:00:00', '2026-09-18 23:59:59']);
$source = array_map('array_change_key_case', $stmt->fetchAll(PDO::FETCH_ASSOC));
if (count($source) !== 11) throw new RuntimeException('Audited source changed');
$first = $source[0];
$money = static function ($value): string {
    return number_format((float)$value, 2, '.', '');
};
$header = ['序号', '下单日期', '就餐日期', '姓名', '所属部门', '手机号', '订单号',
    '订单金额', '实付金额', '备注', '菜品名称', '菜品单价', '菜品份数', '菜品总价'];
$row = [1, (string)$first['bind_date'], (string)$first['meal_date'],
    (string)$first['user_name'], (string)$first['department_name'],
    (string)$first['mobile'], (string)$first['order_no'],
    $money($first['total_price']), $money($first['pay_amount']),
    (string)$first['remark'], (string)$first['dishes_name'],
    $money($first['unit_price']), $money($first['dish_number']),
    $money($first['dish_total_price'])];
$labels = ['index', 'bind_date', 'meal_date', 'user_name', 'department_name',
    'mobile', 'order_no', 'total_price', 'pay_amount', 'remark',
    'dishes_name', 'unit_price', 'dish_number', 'dish_total_price'];
$temporary = sys_get_temp_dir() . '/cdx-export3-diagnostic-'
    . strtoupper(bin2hex(random_bytes(8)));
if (!mkdir($temporary, 0700)) throw new RuntimeException('Private diagnostic dir unavailable');
chmod($temporary, 0700);
$xlsPath = $temporary . '/offline.xls';
try {
    require_once '/tmp/zhct-http-store-a.l5omH8/vendor/autoload.php';
    $book = new PHPExcel();
    $book->setActiveSheetIndex(0);
    $book->getActiveSheet()->fromArray([$header, $row]);
    $writer = PHPExcel_IOFactory::createWriter($book, 'Excel5');
    $writer->save($xlsPath);
    chmod($xlsPath, 0600);
    unset($writer, $book);
    $reader = PHPExcel_IOFactory::createReader('Excel5');
    $reader->setReadDataOnly(true);
    $read = $reader->load($xlsPath);
    $sheet = $read->getSheet(0);
    $firstMismatch = null;
    $moneyColumns = [];
    $identityColumns = [];
    foreach ($labels as $index => $name) {
        $raw = $sheet->getCellByColumnAndRow($index, 2)->getValue();
        $actual = $raw === null ? '' : (string)$raw;
        $expected = (string)$row[$index];
        if (in_array($index, [7, 8, 11, 12, 13], true)) {
            $moneyColumns[$name] = ['source_type' => gettype($row[$index]),
                'readback_type' => gettype($raw),
                'cent_equal' => $money($expected) === $money($actual)];
        }
        if (in_array($index, [5, 6], true)) {
            $identityColumns[$name] = ['source_type' => gettype($row[$index]),
                'readback_type' => gettype($raw),
                'exact_text_equal' => $actual === $expected];
        }
        if ($actual !== $expected && $firstMismatch === null) {
            $moneyField = in_array($index, [7, 8, 11, 12, 13], true);
            $firstMismatch = ['column_index_0based' => $index, 'field' => $name,
                'source_type' => gettype($row[$index]),
                'readback_type' => gettype($raw),
                'source_length' => strlen($expected),
                'readback_length' => strlen($actual),
                'exact_text_equal' => false,
                'numeric_equal' => is_numeric($expected) && is_numeric($actual)
                    ? (float)$expected === (float)$actual : false,
                'money_cent_equal' => $moneyField
                    ? $money($expected) === $money($actual) : null];
        }
    }
    $read->disconnectWorksheets();
    echo json_encode(['offline_reconstruction' => true,
        'source_rows' => count($source),
        'first_mismatch' => $firstMismatch,
        'identity_columns' => $identityColumns,
        'money_columns' => $moneyColumns,
        'original_http_xls_available' => false], JSON_UNESCAPED_SLASHES), PHP_EOL;
} finally {
    if (is_file($xlsPath)) unlink($xlsPath);
    rmdir($temporary);
}