<?php

declare(strict_types=1);

date_default_timezone_set('Asia/Shanghai');

/**
 * 智慧营养健康餐厅跨项目只读经营与营养数据采集。
 *
 * 安全边界：
 * - 仅执行 SELECT / information_schema 查询；
 * - 所有数据库账号密码只从 config/local/project-metrics-db.json 读取，不写入输出；
 * - 输出仅保留项目级聚合，不导出姓名、手机号、订单号等个人明细。
 */

const ROOT = '/Users/jack/code/010-cpt/008-zhct/zhctprompt';
const LOCAL_DB_CREDENTIALS = ROOT . '/config/local/project-metrics-db.json';
const OUTPUT_DIR = ROOT . '/work/2026-07-20-smart-canteen-project-value-data';

$projects = [
    project('zhct', '产业园智慧营养健康餐厅', 'rds3nh30726o5tko5b9g502.mysql.rds.aliyuncs.com', 'rds3nh30726o5tko5b9gro.mysql.rds.aliyuncs.com', 'zhct', 'aizhct', 'zhct'),
    project('zhct_bzjk', '滨州健康科技职业学院智慧营养健康餐厅', '10.10.12.63', '10.10.12.63', 'zhct_bzjk', 'aizhct_bzjk', null, 'blocked_missing_local_config_and_private_network'),
    project('zhct_csfzx', '城市副中心智慧营养健康餐厅', '127.0.0.1', '127.0.0.1', 'zhct_csfzx', 'aizhct_csfzx', null, 'blocked_missing_local_config'),
    project('zhct_guoxin', '江苏国信智慧营养健康餐厅', 'rm-2ze9n39s9f40s05ox.mysql.rds.aliyuncs.com', 'rm-2ze9n39s9f40s05oxjo.mysql.rds.aliyuncs.com', 'zhct_guoxin', 'aizhct_guoxin', 'zhct_guoxin'),
    project('zhct_haikai', '海开集团智慧营养健康餐厅', 'rds3nh30726o5tko5b9g502.mysql.rds.aliyuncs.com', 'rds3nh30726o5tko5b9gro.mysql.rds.aliyuncs.com', 'zhct_haikai', null, 'zhct_haikai'),
    project('zhct_hntzy', '湖南体职院', '39.106.45.95', '39.106.45.95', 'zhct_hntzy', 'aizhct_hntzy', 'zhct_hntzy'),
    project('zhct_jc', '机场智慧营养健康餐厅', 'rm-2ze9n39s9f40s05ox.mysql.rds.aliyuncs.com', 'rm-2ze9n39s9f40s05oxjo.mysql.rds.aliyuncs.com', 'zhct_jc_new', 'aizhct_jc_new', 'zhct_jc'),
    project('zhct_jsr', '金斯瑞智慧营养健康餐厅', 'rm-2ze9n39s9f40s05ox.mysql.rds.aliyuncs.com', 'rm-2ze9n39s9f40s05oxjo.mysql.rds.aliyuncs.com', 'zhct_jsr', 'aizhct_jsr', 'zhct_jsr'),
    project('zhct_jx206', '江西206智慧营养健康餐厅', '192.168.0.11', '192.168.0.11', 'zhct_jx206', 'aizhct_jx206', null, 'blocked_missing_local_config_and_private_network'),
    project('zhct_lds', '莱蒂森智慧营养健康餐厅', '127.0.0.1', '127.0.0.1', 'zhct_lds', 'aizhct_lds', null, 'blocked_missing_local_config'),
    project('zhct_rdfz', '人大附中智慧营养健康餐厅', 'rm-2ze9n39s9f40s05ox.mysql.rds.aliyuncs.com', 'rm-2ze9n39s9f40s05oxjo.mysql.rds.aliyuncs.com', 'zhct_rdfz', 'aizhct_rdfz', 'zhct_rdfz'),
    project('zhct_saidi', '赛迪物业智慧营养健康餐厅', 'rm-2ze9n39s9f40s05ox.mysql.rds.aliyuncs.com', 'rm-2ze9n39s9f40s05oxjo.mysql.rds.aliyuncs.com', 'zhct_saidi', 'aizhct_saidi', 'zhct_saidi'),
    project('zhct_sh', '四川射洪智慧营养健康餐厅', 'rds3nh30726o5tko5b9g502.mysql.rds.aliyuncs.com', 'rds3nh30726o5tko5b9gro.mysql.rds.aliyuncs.com', 'zhct_sh', null, 'zhct_sh'),
    project('zhct_stzc', '首通智城智慧营养健康餐厅', 'rm-2zer9po1sbfwqr6p2.mysql.rds.aliyuncs.com', 'rm-2zer9po1sbfwqr6p20o.mysql.rds.aliyuncs.com', 'zhct_stzc', 'aizhct_stzc', 'zhct_stzc'),
    project('zhct_sxjm', '山西焦煤智慧营养健康餐厅', 'rm-2ze9n39s9f40s05ox.mysql.rds.aliyuncs.com', 'rm-2ze9n39s9f40s05oxjo.mysql.rds.aliyuncs.com', 'zhct_sxjm', 'aizhct_sxjm', 'zhct_sxjm'),
    project('zhct_xkbg', '西康宾馆智慧营养健康餐厅', 'rds3nh30726o5tko5b9g502.mysql.rds.aliyuncs.com', 'rds3nh30726o5tko5b9gro.mysql.rds.aliyuncs.com', 'zhct_xkbg', null, 'zhct_xkbg'),
    project('zhct_xwq', '无锡新吴区区政府智慧营养健康餐厅', '2.18.76.172', '2.18.76.172', 'zhct_xwq', null, 'zhct_xwq'),
];

$summaryRows = [];
$mealRows = [];
$dailyMealRows = [];
$dailyDauRows = [];
$runStartedAt = date(DATE_ATOM);

foreach ($projects as $project) {
    $base = baseOutputRow($project);
    if ($project['precheckStatus'] !== null) {
        $summaryRows[] = $base + [
            'status' => $project['precheckStatus'],
            'error_code' => $project['precheckStatus'],
        ];
        fwrite(STDERR, "[blocked] {$project['slug']}: {$project['precheckStatus']}\n");
        continue;
    }

    try {
        $credentials = loadCredentials($project);
        $pdo = connectReadOnly($project, $credentials);
        $tables = requiredTableState($pdo);
        if (!$tables['ydy_staff'] || !$tables['ydy_meal_order']) {
            throw new RuntimeException('required_tables_missing');
        }

        $core = fetchCoreMetrics($pdo);
        $dish = ($tables['ydy_initial_menu'] && $tables['ydy_dishes'])
            ? fetchDishMetrics($pdo)
            : emptyDishMetrics('dish_tables_missing');
        $dau = fetchDauMetrics($pdo, $core['latest_activity_date'] ?? null);
        $quality = deriveNutritionEvidenceQuality($core + $dish);

        $summaryRows[] = $base + [
            'status' => 'ok',
            'error_code' => '',
        ] + $core + $dau + $dish + $quality;

        foreach (fetchMealMetrics($pdo) as $row) {
            $mealRows[] = mealOutputRow($project, $row);
        }
        foreach (fetchDailyMealMetrics($pdo) as $row) {
            $dailyMealRows[] = dailyMealOutputRow($project, $row);
        }
        foreach (fetchDailyDau($pdo) as $row) {
            $dailyDauRows[] = dailyDauOutputRow($project, $row);
        }

        $pdo->commit();

        fwrite(STDERR, "[ok] {$project['slug']}\n");
    } catch (Throwable $e) {
        $summaryRows[] = $base + [
            'status' => classifyError($e),
            'error_code' => sanitizeError($e->getMessage()),
        ];
        fwrite(STDERR, "[error] {$project['slug']}: " . sanitizeError($e->getMessage()) . "\n");
    }
}

writeCsv(OUTPUT_DIR . '/project-metrics.csv', $summaryRows);
writeCsv(OUTPUT_DIR . '/meal-section-metrics.csv', $mealRows);
writeCsv(OUTPUT_DIR . '/daily-meal-metrics.csv', $dailyMealRows);
writeCsv(OUTPUT_DIR . '/daily-active-users.csv', $dailyDauRows);

$metadata = [
    'started_at' => $runStartedAt,
    'finished_at' => date(DATE_ATOM),
    'project_count' => count($projects),
    'success_count' => count(array_filter($summaryRows, static fn(array $row): bool => ($row['status'] ?? '') === 'ok')),
    'blocked_or_failed_count' => count(array_filter($summaryRows, static fn(array $row): bool => ($row['status'] ?? '') !== 'ok')),
    'summary_rows' => count($summaryRows),
    'meal_section_rows' => count($mealRows),
    'daily_meal_rows' => count($dailyMealRows),
    'daily_dau_rows' => count($dailyDauRows),
    'privacy' => 'project-level aggregates only; no credentials or personal details exported',
];
file_put_contents(OUTPUT_DIR . '/run-metadata.json', json_encode($metadata, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE) . PHP_EOL);
echo json_encode($metadata, JSON_UNESCAPED_UNICODE) . PHP_EOL;

function project(
    string $slug,
    string $name,
    string $configuredHost,
    string $connectHost,
    string $database,
    ?string $aiDatabase,
    ?string $credentialKey,
    ?string $precheckStatus = null
): array {
    return compact('slug', 'name', 'configuredHost', 'connectHost', 'database', 'aiDatabase', 'credentialKey', 'precheckStatus');
}

function loadCredentials(array $project): array
{
    static $credentialsByProject = null;

    if ($project['credentialKey'] === null || !is_file(LOCAL_DB_CREDENTIALS)) {
        throw new RuntimeException('credential_source_missing');
    }

    if ($credentialsByProject === null) {
        $decoded = json_decode((string)file_get_contents(LOCAL_DB_CREDENTIALS), true, flags: JSON_THROW_ON_ERROR);
        if (!is_array($decoded)) {
            throw new RuntimeException('local_credential_config_invalid');
        }
        $credentialsByProject = $decoded;
    }

    $config = $credentialsByProject[$project['credentialKey']] ?? null;
    if (!is_array($config) || empty($config['username']) || !array_key_exists('password', $config)) {
        throw new RuntimeException('local_project_credential_incomplete');
    }
    return ['username' => (string)$config['username'], 'password' => (string)$config['password']];
}

function connectReadOnly(array $project, array $credentials): PDO
{
    $dsn = sprintf(
        'mysql:host=%s;port=3306;dbname=%s;charset=utf8mb4',
        $project['connectHost'],
        $project['database']
    );
    $pdo = new PDO($dsn, $credentials['username'], $credentials['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_TIMEOUT => 8,
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET SESSION TRANSACTION READ ONLY',
    ]);
    try {
        $pdo->exec('SET SESSION MAX_EXECUTION_TIME=120000');
    } catch (Throwable) {
        // Older MySQL versions do not support MAX_EXECUTION_TIME; read-only stays enforced.
    }
    $database = $pdo->query('SELECT DATABASE()')->fetchColumn();
    if ($database !== $project['database']) {
        throw new RuntimeException('database_identity_mismatch');
    }
    $pdo->exec('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
    $pdo->beginTransaction();
    return $pdo;
}

function requiredTableState(PDO $pdo): array
{
    $required = ['ydy_staff', 'ydy_meal_order', 'ydy_initial_menu', 'ydy_dishes'];
    $placeholders = implode(',', array_fill(0, count($required), '?'));
    $stmt = $pdo->prepare("SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name IN ($placeholders)");
    $stmt->execute($required);
    $present = array_fill_keys($required, false);
    foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $table) {
        $present[$table] = true;
    }
    return $present;
}

function fetchCoreMetrics(PDO $pdo): array
{
    $netAmount = netAmountExpression($pdo);
    $valid = validOrderPredicate($pdo);
    $validAsOf = "{$valid} AND COALESCE(meal_date, DATE(pay_time), DATE(create_time)) <= CURRENT_DATE()";
    $sql = <<<SQL
SELECT
    (SELECT COUNT(*) FROM ydy_staff) AS registered_users,
    COUNT(*) AS all_order_count,
    ROUND(COALESCE(SUM(total_price), 0), 2) AS all_order_total_price,
    COALESCE(SUM(CASE WHEN pay_status = 20 AND is_delete = 0 THEN 1 ELSE 0 END), 0) AS paid_order_count,
    ROUND(COALESCE(SUM(CASE WHEN pay_status = 20 AND is_delete = 0 THEN total_price ELSE 0 END), 0), 2) AS paid_order_total_price,
    ROUND(COALESCE(SUM(CASE WHEN pay_status = 20 AND is_delete = 0 THEN {$netAmount} ELSE 0 END), 0), 2) AS paid_net_amount,
    COALESCE(SUM(CASE WHEN {$validAsOf} THEN 1 ELSE 0 END), 0) AS valid_order_count,
    ROUND(COALESCE(SUM(CASE WHEN {$validAsOf} THEN total_price ELSE 0 END), 0), 2) AS valid_order_total_price,
    COUNT(DISTINCT CASE WHEN {$validAsOf} AND user_id > 0 THEN user_id END) AS cumulative_active_users,
    MIN(CASE WHEN {$validAsOf} THEN COALESCE(meal_date, DATE(pay_time), DATE(create_time)) END) AS first_activity_date,
    MAX(CASE WHEN {$validAsOf} THEN COALESCE(meal_date, DATE(pay_time), DATE(create_time)) END) AS latest_activity_date,
    COALESCE(SUM(CASE WHEN pay_status = 20 AND is_delete = 0 AND meal_date > CURRENT_DATE() THEN 1 ELSE 0 END), 0) AS future_meal_order_count
FROM ydy_meal_order
SQL;
    return normalizeNumericRow($pdo->query($sql)->fetch() ?: []);
}

function fetchDauMetrics(PDO $pdo, ?string $latestDate): array
{
    $valid = validOrderPredicate($pdo);
    $sql = <<<SQL
SELECT
    COALESCE(MAX(CASE WHEN activity_date = CURRENT_DATE() THEN dau ELSE 0 END), 0) AS current_day_dau,
    ROUND(COALESCE(SUM(dau), 0) / 30, 2) AS last_30_calendar_avg_dau,
    ROUND(COALESCE(AVG(dau), 0), 2) AS last_30_active_day_avg_dau,
    COUNT(activity_date) AS last_30_active_days
FROM (
    SELECT
        COALESCE(meal_date, DATE(pay_time), DATE(create_time)) AS activity_date,
        COUNT(DISTINCT CASE WHEN user_id > 0 THEN user_id END) AS dau
    FROM ydy_meal_order
    WHERE {$valid}
      AND COALESCE(meal_date, DATE(pay_time), DATE(create_time)) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 29 DAY) AND CURRENT_DATE()
    GROUP BY COALESCE(meal_date, DATE(pay_time), DATE(create_time))
) daily
SQL;
    $result = normalizeNumericRow($pdo->query($sql)->fetch() ?: []);
    $result['latest_active_day_dau'] = 0;
    if ($latestDate !== null && $latestDate !== '') {
        $stmt = $pdo->prepare(<<<SQL
SELECT COUNT(DISTINCT CASE WHEN user_id > 0 THEN user_id END)
FROM ydy_meal_order
WHERE {$valid}
  AND COALESCE(meal_date, DATE(pay_time), DATE(create_time)) = :latest_date
SQL
        );
        $stmt->execute(['latest_date' => $latestDate]);
        $result['latest_active_day_dau'] = (int)$stmt->fetchColumn();
    }
    return $result;
}

function fetchDishMetrics(PDO $pdo): array
{
    $valid = validOrderPredicate($pdo, 'meal');
    $sql = <<<SQL
SELECT
    COUNT(*) AS order_dish_row_count,
    COUNT(DISTINCT menu.meal_order_id) AS orders_with_dish_rows,
    ROUND(COALESCE(SUM(GREATEST(COALESCE(menu.number, 0), 0)), 0), 2) AS dish_serving_count,
    ROUND(COALESCE(SUM(GREATEST(COALESCE(menu.weight, 0), 0)), 0), 2) AS dish_weight_g,
    COUNT(DISTINCT CASE WHEN menu.dishes_uuid <> 'custom_dish' THEN menu.dishes_uuid END) AS unique_dish_types,
    ROUND(COALESCE(SUM(CASE
        WHEN dishes.uuid IS NULL THEN 0
        WHEN COALESCE(menu.weight, 0) > 0 AND COALESCE(dishes.weight, 0) > 0 THEN dishes.energy * menu.weight / dishes.weight
        WHEN COALESCE(menu.number, 0) > 0 THEN dishes.energy * menu.number
        ELSE 0 END), 0), 2) AS energy_kcal,
    ROUND(COALESCE(SUM(CASE
        WHEN dishes.uuid IS NULL THEN 0
        WHEN COALESCE(menu.weight, 0) > 0 AND COALESCE(dishes.weight, 0) > 0 THEN dishes.carbohydrate * menu.weight / dishes.weight
        WHEN COALESCE(menu.number, 0) > 0 THEN dishes.carbohydrate * menu.number
        ELSE 0 END), 0), 2) AS carbohydrate_g,
    ROUND(COALESCE(SUM(CASE
        WHEN dishes.uuid IS NULL THEN 0
        WHEN COALESCE(menu.weight, 0) > 0 AND COALESCE(dishes.weight, 0) > 0 THEN dishes.protein * menu.weight / dishes.weight
        WHEN COALESCE(menu.number, 0) > 0 THEN dishes.protein * menu.number
        ELSE 0 END), 0), 2) AS protein_g,
    ROUND(COALESCE(SUM(CASE
        WHEN dishes.uuid IS NULL THEN 0
        WHEN COALESCE(menu.weight, 0) > 0 AND COALESCE(dishes.weight, 0) > 0 THEN dishes.fat * menu.weight / dishes.weight
        WHEN COALESCE(menu.number, 0) > 0 THEN dishes.fat * menu.number
        ELSE 0 END), 0), 2) AS fat_g,
    ROUND(100 * SUM(CASE WHEN dishes.uuid IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS dish_master_link_rate_pct
FROM ydy_initial_menu menu
INNER JOIN ydy_meal_order meal ON meal.id = menu.meal_order_id
LEFT JOIN ydy_dishes dishes ON dishes.uuid = menu.dishes_uuid
WHERE {$valid}
  AND COALESCE(meal.meal_date, DATE(meal.pay_time), DATE(meal.create_time)) <= CURRENT_DATE()
SQL;
    return normalizeNumericRow($pdo->query($sql)->fetch() ?: []) + ['dish_metric_status' => 'ok'];
}

function emptyDishMetrics(string $status): array
{
    return [
        'order_dish_row_count' => '',
        'orders_with_dish_rows' => '',
        'dish_serving_count' => '',
        'dish_weight_g' => '',
        'unique_dish_types' => '',
        'energy_kcal' => '',
        'carbohydrate_g' => '',
        'protein_g' => '',
        'fat_g' => '',
        'dish_master_link_rate_pct' => '',
        'dish_metric_status' => $status,
    ];
}

function deriveNutritionEvidenceQuality(array $metrics): array
{
    $paidOrders = (float)($metrics['valid_order_count'] ?? 0);
    $ordersWithDishes = (float)($metrics['orders_with_dish_rows'] ?? 0);
    $dishWeight = (float)($metrics['dish_weight_g'] ?? 0);
    $energy = (float)($metrics['energy_kcal'] ?? 0);
    $masterLinkRate = (float)($metrics['dish_master_link_rate_pct'] ?? 0);
    $coverage = $paidOrders > 0 ? round(100 * $ordersWithDishes / $paidOrders, 2) : 0.0;
    $avgWeight = $ordersWithDishes > 0 ? round($dishWeight / $ordersWithDishes, 2) : 0.0;
    $avgEnergy = $ordersWithDishes > 0 ? round($energy / $ordersWithDishes, 2) : 0.0;

    if ($paidOrders <= 0) {
        $status = 'no_order_data';
    } elseif (($metrics['dish_metric_status'] ?? '') !== 'ok') {
        $status = 'dish_metric_unavailable';
    } elseif ($coverage < 80) {
        $status = 'insufficient_order_dish_coverage';
    } elseif ($masterLinkRate < 80) {
        $status = 'insufficient_dish_master_link_rate';
    } elseif ($avgWeight < 50 || $avgEnergy < 50) {
        $status = 'missing_or_too_low_nutrition_values';
    } elseif ($avgWeight > 2000 || $avgEnergy > 5000) {
        $status = 'nutrition_outlier_requires_review';
    } else {
        $status = 'usable_aggregate_nutrition_evidence';
    }

    return [
        'order_dish_coverage_pct' => $coverage,
        'avg_dish_weight_per_covered_order_g' => $avgWeight,
        'avg_energy_per_covered_order_kcal' => $avgEnergy,
        'nutrition_evidence_status' => $status,
    ];
}

function fetchMealMetrics(PDO $pdo): array
{
    $netAmount = netAmountExpression($pdo);
    $valid = validOrderPredicate($pdo);
    $sql = <<<SQL
SELECT
    meal_times,
    COUNT(*) AS valid_order_count,
    ROUND(COALESCE(SUM(total_price), 0), 2) AS valid_order_total_price,
    ROUND(COALESCE(SUM({$netAmount}), 0), 2) AS valid_net_amount,
    COUNT(DISTINCT CASE WHEN user_id > 0 THEN user_id END) AS distinct_users
FROM ydy_meal_order
WHERE {$valid}
  AND COALESCE(meal_date, DATE(pay_time), DATE(create_time)) <= CURRENT_DATE()
GROUP BY meal_times
ORDER BY meal_times
SQL;
    return array_map('normalizeNumericRow', $pdo->query($sql)->fetchAll());
}

function fetchDailyMealMetrics(PDO $pdo): array
{
    $netAmount = netAmountExpression($pdo);
    $valid = validOrderPredicate($pdo);
    $sql = <<<SQL
SELECT
    COALESCE(meal_date, DATE(pay_time), DATE(create_time)) AS activity_date,
    meal_times,
    COUNT(*) AS valid_order_count,
    ROUND(COALESCE(SUM(total_price), 0), 2) AS valid_order_total_price,
    ROUND(COALESCE(SUM({$netAmount}), 0), 2) AS valid_net_amount,
    COUNT(DISTINCT CASE WHEN user_id > 0 THEN user_id END) AS distinct_users
FROM ydy_meal_order
WHERE {$valid}
  AND COALESCE(meal_date, DATE(pay_time), DATE(create_time)) <= CURRENT_DATE()
GROUP BY COALESCE(meal_date, DATE(pay_time), DATE(create_time)), meal_times
ORDER BY activity_date, meal_times
SQL;
    return array_map('normalizeNumericRow', $pdo->query($sql)->fetchAll());
}

function fetchDailyDau(PDO $pdo): array
{
    $netAmount = netAmountExpression($pdo);
    $valid = validOrderPredicate($pdo);
    $sql = <<<SQL
SELECT
    COALESCE(meal_date, DATE(pay_time), DATE(create_time)) AS activity_date,
    COUNT(*) AS valid_order_count,
    ROUND(COALESCE(SUM(total_price), 0), 2) AS valid_order_total_price,
    ROUND(COALESCE(SUM({$netAmount}), 0), 2) AS valid_net_amount,
    COUNT(DISTINCT CASE WHEN user_id > 0 THEN user_id END) AS dau
FROM ydy_meal_order
WHERE {$valid}
  AND COALESCE(meal_date, DATE(pay_time), DATE(create_time)) <= CURRENT_DATE()
GROUP BY COALESCE(meal_date, DATE(pay_time), DATE(create_time))
ORDER BY activity_date
SQL;
    return array_map('normalizeNumericRow', $pdo->query($sql)->fetchAll());
}

function netAmountExpression(PDO $pdo): string
{
    $columns = mealOrderColumns($pdo);
    $gross = isset($columns['pay_price']) ? 'COALESCE(pay_price, total_price, 0)' : 'COALESCE(total_price, 0)';
    $refund = isset($columns['refund_amount']) ? 'COALESCE(refund_amount, 0)' : '0';
    return "GREATEST({$gross} - {$refund}, 0)";
}

function validOrderPredicate(PDO $pdo, string $alias = ''): string
{
    $columns = mealOrderColumns($pdo);
    $prefix = $alias === '' ? '' : $alias . '.';
    $parts = [];
    if (isset($columns['pay_status'])) {
        $parts[] = $prefix . 'pay_status = 20';
    }
    if (isset($columns['order_status'])) {
        $parts[] = $prefix . 'order_status = 30';
    }
    if (isset($columns['is_delete'])) {
        $parts[] = $prefix . 'is_delete = 0';
    }
    return $parts === [] ? '1 = 1' : implode(' AND ', $parts);
}

function mealOrderColumns(PDO $pdo): array
{
    static $cache = [];
    $database = (string)$pdo->query('SELECT DATABASE()')->fetchColumn();
    if (!isset($cache[$database])) {
        $stmt = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'ydy_meal_order'");
        $cache[$database] = array_fill_keys($stmt->fetchAll(PDO::FETCH_COLUMN), true);
    }
    return $cache[$database];
}

function baseOutputRow(array $project): array
{
    return [
        'config_slug' => $project['slug'],
        'project_name' => $project['name'],
        'configured_host' => $project['configuredHost'],
        'query_host' => $project['connectHost'],
        'database_name' => $project['database'],
        'ai_database_name' => $project['aiDatabase'] ?? '',
        'credential_mode' => 'local_json',
        'as_of' => date('Y-m-d H:i:s'),
    ];
}

function mealOutputRow(array $project, array $row): array
{
    return [
        'config_slug' => $project['slug'],
        'project_name' => $project['name'],
        'database_name' => $project['database'],
        'meal_times' => $row['meal_times'] ?? '',
        'meal_name' => mealName($row['meal_times'] ?? null),
        'valid_order_count' => $row['valid_order_count'] ?? 0,
        'valid_order_total_price' => $row['valid_order_total_price'] ?? 0,
        'valid_net_amount' => $row['valid_net_amount'] ?? 0,
        'distinct_users' => $row['distinct_users'] ?? 0,
    ];
}

function dailyMealOutputRow(array $project, array $row): array
{
    return [
        'config_slug' => $project['slug'],
        'project_name' => $project['name'],
        'activity_date' => $row['activity_date'] ?? '',
        'meal_times' => $row['meal_times'] ?? '',
        'meal_name' => mealName($row['meal_times'] ?? null),
        'valid_order_count' => $row['valid_order_count'] ?? 0,
        'valid_order_total_price' => $row['valid_order_total_price'] ?? 0,
        'valid_net_amount' => $row['valid_net_amount'] ?? 0,
        'distinct_users' => $row['distinct_users'] ?? 0,
    ];
}

function dailyDauOutputRow(array $project, array $row): array
{
    return [
        'config_slug' => $project['slug'],
        'project_name' => $project['name'],
        'activity_date' => $row['activity_date'] ?? '',
        'valid_order_count' => $row['valid_order_count'] ?? 0,
        'valid_order_total_price' => $row['valid_order_total_price'] ?? 0,
        'valid_net_amount' => $row['valid_net_amount'] ?? 0,
        'dau' => $row['dau'] ?? 0,
    ];
}

function mealName(mixed $mealTimes): string
{
    return match ((int)$mealTimes) {
        1 => '早餐',
        2 => '午餐',
        3 => '晚餐',
        4 => '夜宵',
        default => '其他/未标注',
    };
}

function normalizeNumericRow(array $row): array
{
    foreach ($row as $key => $value) {
        if ($value !== null && $value !== '' && is_numeric($value)) {
            $row[$key] = str_contains((string)$value, '.') ? (float)$value : (int)$value;
        }
    }
    return $row;
}

function writeCsv(string $path, array $rows): void
{
    $handle = fopen($path, 'wb');
    if ($handle === false) {
        throw new RuntimeException('output_open_failed');
    }
    fwrite($handle, "\xEF\xBB\xBF");
    if ($rows !== []) {
        $headers = [];
        foreach ($rows as $row) {
            foreach (array_keys($row) as $key) {
                if (!in_array($key, $headers, true)) {
                    $headers[] = $key;
                }
            }
        }
        fputcsv($handle, $headers, ',', '"', '\\');
        foreach ($rows as $row) {
            fputcsv($handle, array_map(static fn(string $key): mixed => $row[$key] ?? '', $headers), ',', '"', '\\');
        }
    }
    fclose($handle);
}

function classifyError(Throwable $e): string
{
    $message = strtolower($e->getMessage());
    if (str_contains($message, 'access denied')) {
        return 'blocked_access_denied';
    }
    if (str_contains($message, 'unknown database')) {
        return 'blocked_database_missing_or_not_granted';
    }
    if (str_contains($message, 'timed out') || str_contains($message, 'connection refused') || str_contains($message, 'no route')) {
        return 'blocked_network';
    }
    if (str_contains($message, 'required_tables_missing')) {
        return 'blocked_schema_mismatch';
    }
    return 'failed_query';
}

function sanitizeError(string $message): string
{
    $message = preg_replace('/Access denied for user \'[^\']+\'@\'[^\']+\'/i', 'Access denied for configured user', $message) ?? $message;
    $message = preg_replace('/password=[^\s;]+/i', 'password=[REDACTED]', $message) ?? $message;
    return mb_substr($message, 0, 240);
}