<?php
declare(strict_types=1);

// A/B user-approved local-development MySQL, metadata/public catalog only.
require __DIR__ . '/../parallel-ai-health-read-batch/ab_readonly_preflight.php';
$stage = 'catalog_table_access';
$aiTables = ['yoshop_dishes_cate', 'yoshop_dishes', 'yoshop_dishes_tag',
    'yoshop_upload_file', 'yoshop_meal_cart'];
$storeTables = ['ydy_recipes', 'ydy_dishes', 'ydy_restaurants',
    'ydy_meal_order', 'ydy_initial_menu'];
foreach ($aiTables as $table) {
    $ai->query('SELECT 1 FROM ' . $table . ' WHERE 1=0');
}
foreach ($storeTables as $table) {
    $store->query('SELECT 1 FROM ' . $table . ' WHERE 1=0');
}
$count = static function (PDO $pdo, string $sql, array $bind = []): int {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($bind);
    return (int)$stmt->fetchColumn();
};
$start = date('Y-m-d');
$end = date('Y-m-d', strtotime($start . ' +7 days'));
$stage = 'catalog_aggregate_counts';
$counts = [
    'ai_top_categories' => $count($ai,
        'SELECT COUNT(*) FROM yoshop_dishes_cate WHERE pid=0'),
    'ai_active_dishes' => $count($ai,
        'SELECT COUNT(*) FROM yoshop_dishes WHERE is_delete=0'),
    'store_active_restaurants' => $count($store,
        'SELECT COUNT(*) FROM ydy_restaurants WHERE status=1 AND is_delete=2'),
    'store_mapped_recipe_rows_next_7_days' => $count($store,
        'SELECT COUNT(*) FROM ydy_recipes r INNER JOIN ydy_dishes d'
        . ' ON d.uuid=r.dishes_uuid WHERE r.menu_date>=? AND r.menu_date<?'
        . ' AND d.is_delete=0 AND d.ai_dishes_id>0', [$start, $end]),
];
$stage = 'natural_group_catalog_ids';
$statement = $store->prepare(
    'SELECT r.restaurant_id,r.menu_date,r.meal_times,d.ai_dishes_id'
    . ' FROM ydy_recipes r INNER JOIN ydy_dishes d ON d.uuid=r.dishes_uuid'
    . ' INNER JOIN ydy_restaurants x ON x.id=r.restaurant_id'
    . ' WHERE r.menu_date>=? AND r.menu_date<?'
    . ' AND d.is_delete=0 AND d.ai_dishes_id>0'
    . ' AND x.status=1 AND x.is_delete=2'
    . ' AND (x.stall_type=1 OR (x.stall_type=2 AND r.menu_date=?))'
    . ' ORDER BY r.restaurant_id,r.menu_date,r.meal_times,r.id DESC');
$statement->execute([$start, $end, $start]);
$groups = [];
$catalogRowsRead = 0;
foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $key = (string)$row['restaurant_id'] . '|'
        . (string)$row['menu_date'] . '|'
        . (string)$row['meal_times'];
    $groups[$key][] = (int)$row['ai_dishes_id'];
    $catalogRowsRead++;
}
$stage = 'zero_other_user_orders_and_ai_relation';
$candidateKey = null;
$eligibleZeroOrderGroups = 0;
foreach ($groups as $key => $ids) {
    if (count($ids) < 1 || count($ids) > 15) continue;
    [$restaurantId, $date, $mealTimes] = explode('|', $key, 3);
    $bind = [(int)$restaurantId, $date, (int)$mealTimes];
    if ($count($store, 'SELECT COUNT(*) FROM ydy_meal_order'
        . ' WHERE restaurant_id=? AND meal_date=? AND meal_times=?', $bind) !== 0) {
        continue;
    }
    if ($count($store, 'SELECT COUNT(*) FROM ydy_initial_menu m'
        . ' INNER JOIN ydy_meal_order o ON o.id=m.meal_order_id'
        . ' WHERE o.restaurant_id=? AND o.meal_date=? AND o.meal_times=?',
        $bind) !== 0) {
        continue;
    }
    $eligibleZeroOrderGroups++;
    $ids = array_values(array_unique($ids));
    $params = implode(',', array_fill(0, count($ids), '?'));
    if ($count($ai, 'SELECT COUNT(*) FROM yoshop_dishes'
        . ' WHERE store_id=10001 AND is_delete=0'
        . ' AND id IN (' . $params . ')', $ids) > 0) {
        $candidateKey = $key;
        break;
    }
}
$stage = 'catalog_numeric_column_metadata';
$metadata = [];
foreach ([['ai', $ai, $config['POC_AI_SCHEMA'], 'yoshop_dishes',
    ['weight', 'energy']],
    ['store', $store, $config['POC_SCHEMA'], 'ydy_dishes',
        ['price']]] as [$side, $pdo, $schema, $table, $columns]) {
    foreach ($columns as $column) {
        $meta = $pdo->prepare('SELECT DATA_TYPE,NUMERIC_PRECISION,NUMERIC_SCALE'
            . ' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=?'
            . ' AND TABLE_NAME=? AND COLUMN_NAME=?');
        $meta->execute([$schema, $table, $column]);
        $row = $meta->fetch(PDO::FETCH_ASSOC);
        $metadata[$side][$column] = is_array($row)
            ? ['type' => strtolower((string)$row['data_type']),
                'precision' => (int)$row['numeric_precision'],
                'scale' => (int)$row['numeric_scale']]
            : null;
    }
}
echo json_encode(['status' => 'catalog-ab-readonly-preflight',
    'target' => $target, 'actual_http_pid_and_local_double_db_match' => true,
    'external_stubs' => true,
    'zero_row_table_groups' => ['ai' => count($aiTables),
        'store' => count($storeTables)],
    'natural_catalog_aggregate_counts' => $counts,
    'catalog_rows_inspected_in_memory' => $catalogRowsRead,
    'eligible_small_zero_order_groups' => $eligibleZeroOrderGroups,
    'nonempty_ai_store_zero_order_candidate_exists' => $candidateKey !== null,
    'candidate_group_hash' => $candidateKey !== null
        ? hash('sha256', $candidateKey) : null,
    'numeric_column_metadata' => $metadata,
    'catalog_ids_or_names_output' => false,
    'personal_or_order_rows_read' => 0,
    'business_http_count' => 0, 'writes' => 0],
    JSON_UNESCAPED_SLASHES), PHP_EOL;