<?php
declare(strict_types=1);

// Depends on the adjacent read-only identity/column probe; no DML or business HTTP.
require __DIR__ . '/c_readonly_preflight.php';

$stage = 'candidate_date_counts';
$weekStart = '2027-12-13';
$weekEnd = '2027-12-19';
$count = static function (PDO $pdo, string $sql, array $bind): int {
    $statement = $pdo->prepare($sql);
    $statement->execute($bind);
    return (int)$statement->fetchColumn();
};
$counts = [
    'ai_meal_week' => $count($ai,
        'SELECT COUNT(*) FROM YOSHOP_MEAL WHERE MEAL_DATE>=? AND MEAL_DATE<=?'
        . ' AND IS_DELETE=0', [$weekStart, $weekEnd]),
    'ai_meal_infos_week' => $count($ai,
        'SELECT COUNT(*) FROM YOSHOP_MEAL_INFOS WHERE MEAL_DATE>=? AND MEAL_DATE<=?'
        . ' AND IS_DELETE=0', [$weekStart, $weekEnd]),
    'store_body_scale_week' => $count($store,
        "SELECT COUNT(*) FROM YDY_MEASUREMENT_SESSION WHERE DEVICE_TYPE='body_scale'"
        . ' AND START_TIME>=? AND START_TIME<?',
        [$weekStart . ' 00:00:00', '2027-12-20 00:00:00']),
    'mapped_dishes_available' => $count($ai,
        'SELECT COUNT(*) FROM YOSHOP_DISHES d INNER JOIN YOSHOP_DISHES_FOODS df'
        . ' ON d.ID=df.DISHES_ID INNER JOIN YOSHOP_FOODS f ON f.ID=df.FOODS_ID'
        . ' WHERE d.IS_DELETE=0 AND d.WEIGHT>0', []),
];
$stage = 'fixture_table_metadata';
$tableNames = [
    'YOSHOP_USER', 'YOSHOP_STAFF', 'YOSHOP_MEAL', 'YOSHOP_MEAL_INFOS',
    'YDY_MEASUREMENT_SESSION', 'YDY_HEALTH_RECORD',
];
$metadata = [];
$selectedTypes = [];
$wantedTypes = [
    'YOSHOP_STAFF' => ['CREATE_TIME'],
    'YOSHOP_MEAL' => ['MEAL_DATE', 'MEAL_TIME', 'ENERGY'],
    'YOSHOP_MEAL_INFOS' => ['MEAL_DATE', 'DISHES_WEIGHT', 'DISHES_NUMBER'],
    'YDY_MEASUREMENT_SESSION' => ['START_TIME', 'CREATED_AT'],
    'YDY_HEALTH_RECORD' => ['START_TIME', 'VALUE'],
];
foreach ($tableNames as $table) {
    $pdo = strpos($table, 'YDY_') === 0 ? $store : $ai;
    $statement = $pdo->prepare('SELECT COLUMN_NAME,DATA_TYPE,NULLABLE,DATA_DEFAULT'
        . ' FROM USER_TAB_COLUMNS WHERE TABLE_NAME=? ORDER BY COLUMN_ID');
    $statement->execute([$table]);
    $columns = $statement->fetchAll(PDO::FETCH_ASSOC);
    if (!$columns) throw new RuntimeException('Fixture table metadata unavailable');
    $required = [];
    foreach ($columns as $column) {
        $column = array_change_key_case($column, CASE_UPPER);
        $name = strtoupper((string)$column['COLUMN_NAME']);
        if (in_array($name, $wantedTypes[$table] ?? [], true)) {
            $selectedTypes[$table][$name] = strtoupper((string)$column['DATA_TYPE']);
        }
        if (strtoupper((string)$column['NULLABLE']) !== 'N') continue;
        $hasDefault = isset($column['DATA_DEFAULT'])
            && trim((string)$column['DATA_DEFAULT']) !== '';
        if (!$hasDefault) $required[] = $name;
    }
    $metadata[$table] = ['column_count' => count($columns),
        'not_null_without_default' => $required];
}
echo json_encode(['status' => 'health-read-c-fixture-design-probe',
    'candidate_week' => [$weekStart, $weekEnd],
    'aggregate_counts_only' => $counts,
    'metadata_only' => $metadata,
    'selected_column_types' => $selectedTypes,
    'business_http_count' => 0, 'row_values_read' => 0,
    'writes' => 0], JSON_UNESCAPED_SLASHES), PHP_EOL;