<?php

declare(strict_types=1);

// Read-only DM probe of the exact legacy aggregate shape on a confirmed empty date.
if ((string)getenv('POC_DIALECT') !== 'dm') throw new RuntimeException('DM-only SQL probe');
$db = new PDO('dm:host=' . getenv('POC_HOST') . ';port=' . getenv('POC_PORT')
    . ';schema=' . getenv('POC_SCHEMA'), getenv('POC_USER'), getenv('POC_PASSWORD'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$day = '2001-01-02';
$check = $db->prepare('SELECT COUNT(*) FROM YDY_RECIPES WHERE MENU_DATE=?');
$check->execute([$day]);
if ((int)$check->fetchColumn() !== 0) {
    throw new RuntimeException('Fixed test day is not empty; refuse SQL probe');
}
$where = ' AND a.restaurant_id= "1" AND a.meal_times = "1" ';
$primary = 'SELECT e.cook_method,count(e.cook_method) as cook_number,e.menu_date'
    . ' from(SELECT * from (SELECT a.uuid,a.menu_date,b.name,c.composition_name,'
    . 'c.cook_method,c.composition_ratio,c.dishes_uuid,a.meal_times FROM ydy_recipes as a'
    . ' LEFT JOIN ydy_dishes as b ON a.dishes_uuid = b.uuid'
    . ' LEFT JOIN ydy_dish as c ON b.uuid = c.dishes_uuid'
    . ' WHERE a.menu_date BETWEEN "2001-01-02" AND "2001-01-02"'
    . ' AND b.is_del = "1" AND b.`status` = "1" '
    . $where
    . ' group by b.name order by c.composition_ratio desc) as d'
    . ' GROUP by d.dishes_uuid,d.uuid,d.menu_date) as e'
    . ' GROUP by e.cook_method,e.menu_date ORDER BY e.menu_date';
$stageWhere = $where . ' AND c.cook_method in (20,1,2,3,4,5,6,7,8,9,10,11,12,13) ';
$stagePrimary = str_replace($where, $stageWhere, $primary);
$stageSum = 'SELECT e.cook_method,count(e.cook_method) as cook_number'
    . ' from(SELECT * from (SELECT a.uuid,a.menu_date,b.name,c.composition_name,'
    . 'c.cook_method,c.composition_ratio,c.dishes_uuid,a.meal_times FROM ydy_recipes as a'
    . ' LEFT JOIN ydy_dishes as b ON a.dishes_uuid = b.uuid'
    . ' LEFT JOIN ydy_dish as c ON b.uuid = c.dishes_uuid'
    . ' WHERE a.menu_date BETWEEN "2001-01-02" AND "2001-01-02"'
    . ' AND b.is_del = "1" AND b.`status` = "1" '
    . $stageWhere
    . ' order by c.composition_ratio desc) as d GROUP by d.dishes_uuid) as e'
    . ' GROUP by e.cook_method ORDER BY e.menu_date';
$quoteFixed = static function (string $sql): string {
    return str_replace(['"2001-01-02"', '"1"'], ["'2001-01-02'", "'1'"], $sql);
};
$run = static function (string $sql) use ($db): array {
    try {
        $rows = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        return ['status' => 'select-pass', 'row_count' => count($rows)];
    } catch (PDOException $e) {
        $message = $e->getMessage();
        preg_match('/SQLSTATE\[[^]]+\]/', $message, $state);
        preg_match('/-[0-9]{3,6}/', $message, $code);
        return ['status' => 'select-fail', 'sqlstate' => $state[0] ?? '',
            'dm_code' => $code[0] ?? '',
            'invalid_column' => strpos($message, '无效的列名') !== false,
            'group_error' => stripos($message, 'group') !== false
                || strpos($message, '分组') !== false];
    }
};
$result = [
    'empty_day_rows' => 0,
    'primary_original' => $run($primary),
    'primary_single_quote_values' => $run($quoteFixed($primary)),
    'stage_primary_single_quote_values' => $run($quoteFixed($stagePrimary)),
    'stage_sum_single_quote_values' => $run($quoteFixed($stageSum)),
];
echo json_encode($result, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL;