<?php

declare(strict_types=1);

// Read-only development MySQL 5.6 preflight. Only counts/hashes; no names/rows.
try {
    $path=realpath(__DIR__
        . '/../../../../../zhctproject/store/application/config/local/database.php');
    if (!$path || strpos($path,
        '/zhctproject/store/application/config/local/')===false) {
        throw new RuntimeException('Approved code local DB config absent');
    }
    $load=static function (string $file): array { return require $file; };
    $config=$load($path);
    $runtimes=[
        'A'=>'/tmp/zhct-http-store-a.l5omH8/application/database.php',
        'B'=>'/tmp/zhct-http-store-mysql.dQTTBP/application/database.php',
    ];
    $same=[];
    foreach ($runtimes as $lane=>$file) {
        $selected=$load($file);
        $match=true;
        foreach (['hostname','database','username','password'] as $field) {
            $match=$match && isset($selected[$field],$config[$field])
                && $selected[$field]===$config[$field];
        }
        $same[$lane]=$match
            && (int)($selected['hostport']??3306)
                ===(int)($config['hostport']??3306)
            && ($selected['type']??null)==='mysql';
    }
    if (!$same['A'] || !$same['B']) {
        throw new RuntimeException('A/B runtime DB differs from code local');
    }
    $db=new PDO('mysql:host='.$config['hostname'].';port='
        .(int)($config['hostport']??3306).';dbname='
        .$config['database'].';charset=utf8mb4',
        (string)$config['username'],(string)$config['password'],[
            PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES=>false,
            PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC,
            PDO::ATTR_TIMEOUT=>8,
        ]);
    $version=(string)$db->query('SELECT VERSION()')->fetchColumn();
    if (strpos($version,'5.6.')===false) {
        throw new RuntimeException('Not approved MySQL5.6');
    }
    $valid='1,2,3,4,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,'
        .'21,22,23,24,25,26,27,28,29,34,35,36';
    $base=' FROM ydy_dishes d JOIN ydy_dish c ON c.dishes_uuid=d.uuid'
        .' JOIN ydy_foods_composition f ON f.id=c.composition_id'
        .' WHERE d.status=1 AND d.is_del=1 AND d.is_delete=0'
        .' AND d.store_id=10001 AND d.weight>0 AND d.energy>0'
        .' AND d.category BETWEEN 1 AND 6 AND c.is_delete=0'
        .' AND f.is_delete=0 AND c.minute_sort IN ('.$valid.')';
    $counts=[];
    $counts['recognized_relation']=(int)$db->query(
        'SELECT COUNT(*)'.$base)->fetchColumn();
    $counts['with_recipe']=(int)$db->query('SELECT COUNT(DISTINCT d.uuid)'
        .str_replace(' WHERE ',' JOIN ydy_recipes r ON r.dishes_uuid=d.uuid'
            .' AND r.meal_times=1 WHERE ',$base))->fetchColumn();
    $joinRecipe=str_replace(' WHERE ',' JOIN ydy_recipes r'
        .' ON r.dishes_uuid=d.uuid AND r.meal_times=1 WHERE ',$base);
    $counts['with_actual_menu']=(int)$db->query(
        'SELECT COUNT(DISTINCT d.uuid)'.$joinRecipe
        .' AND EXISTS(SELECT 1 FROM ydy_date_menu dm'
        .' WHERE dm.dishes_uuid=r.dishes_uuid'
        .' AND dm.menu_date=r.menu_date'
        .' AND dm.restaurant_id=r.restaurant_id'
        .' AND dm.meal_times=1 AND dm.meal_type=3)')->fetchColumn();
    $sql='SELECT d.uuid D_UUID,d.name D_NAME,f.id F_ID,f.name F_NAME'
        .$base.' ORDER BY d.id DESC LIMIT 200';
    $rows=$db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    $gate=['top_200_rows'=>count($rows),'safe_name_rows'=>0,
        'unique_dish_name_rows'=>0,'food_match_bounded_rows'=>0];
    $safe=static function (string $value): bool {
        if ($value==='' || mb_strlen($value)>50
            || preg_match('/[\x00-\x1f]/u',$value)) return false;
        foreach (["'",'"','\\','%','_'] as $unsafe) {
            if (strpos($value,$unsafe)!==false) return false;
        }
        return true;
    };
    $dishCount=$db->prepare('SELECT COUNT(*) FROM ydy_dishes'
        .' WHERE name=? AND status=1 AND is_del=1 AND store_id=10001');
    $foodCount=$db->prepare('SELECT COUNT(*) FROM ydy_foods_composition'
        .' WHERE name LIKE ? OR sub_name LIKE ?');
    foreach ($rows as $row) {
        $dishName=(string)$row['D_NAME'];
        $foodName=(string)$row['F_NAME'];
        if (!$safe($dishName) || !$safe($foodName)) continue;
        $gate['safe_name_rows']++;
        $dishCount->execute([$dishName]);
        if ((int)$dishCount->fetchColumn()!==1) continue;
        $gate['unique_dish_name_rows']++;
        $like='%'.$foodName.'%';
        $foodCount->execute([$like,$like]);
        $n=(int)$foodCount->fetchColumn();
        if ($n>=1 && $n<=100) $gate['food_match_bounded_rows']++;
    }
    foreach ([
        'C5E8BD15-C890-11DF-183B-0A5444A528EF'=>'p/cater/select',
        '2CBDEFF2-EADF-628F-7CBD-05A16D3183C7'=>'p/cater/find',
        '39288C13-FDD4-7F47-35E6-3D9B35F9CB2C'=>'p/food/lists',
        '352B7629-DC3D-4C69-9032-DE025F25090C'=>'p/food/readbyid',
    ] as $uuid=>$route) {
        $stmt=$db->prepare('SELECT filter FROM ydy_menu WHERE uuid=?');
        $stmt->execute([$uuid]);
        $values=$stmt->fetchAll(PDO::FETCH_COLUMN);
        $parts=count($values)===1
            ? array_map(static function($v):string {
                return trim(strtolower($v),' /');
            },explode('|',(string)$values[0])) : [];
        if (count($values)!==1 || !in_array($route,$parts,true)) {
            throw new RuntimeException('Four exact read menus not present');
        }
    }
    $markers=[];
    foreach (['ydy_user'=>'account','ydy_role'=>'name',
        'ydy_user_role'=>'create_by','ydy_role_menu'=>'create_by'] as $table=>$field) {
        $markers[$table]=(int)$db->query('SELECT COUNT(*) FROM '.$table
            ." WHERE ".$field." LIKE 'CDXPARCFRO%'")->fetchColumn();
    }
    echo json_encode(['mysql_version'=>$version,'runtime_same_local'=>$same,
        'counts'=>$counts,'anonymous_gate'=>$gate,
        'read_menus_exact'=>4,'permission_marker_counts'=>$markers],
        JSON_UNESCAPED_SLASHES).PHP_EOL;
} catch (Throwable $error) {
    echo json_encode(['status'=>'error','class'=>get_class($error),
        'code'=>$error->getCode()]).PHP_EOL;
    exit(1);
}