<?php

declare(strict_types=1);

// Read-only aggregate diagnostic. No dish/food/person names or rows emitted.
$pid=(string)getenv('POC_RUNTIME_PID');
if (!ctype_digit($pid)) {
    fwrite(STDERR,"Approved C runtime PID required\n");
    exit(2);
}
try {
    $cmdline=(string)file_get_contents('/proc/'.$pid.'/cmdline');
    if (strpos($cmdline,'127.0.0.1:18080')===false
        || strpos($cmdline,
            '/tmp/zhct-dm-poc-20260928.fcpiiN/store/public')===false) {
        throw new RuntimeException('Unexpected C runtime');
    }
    $env=array_fill_keys(['POC_HOST','POC_PORT','POC_SCHEMA',
        'POC_USER','POC_PASSWORD'],'');
    foreach (explode("\0",(string)file_get_contents('/proc/'.$pid.'/environ')) as $entry) {
        $pair=explode('=',$entry,2);
        if (count($pair)===2 && array_key_exists($pair[0],$env)) {
            $env[$pair[0]]=$pair[1];
        }
    }
    if ($env['POC_HOST']!=='127.0.0.1' || $env['POC_PORT']!=='5237'
        || strtoupper($env['POC_SCHEMA'])!=='ZHCT_APP'
        || strtoupper($env['POC_USER'])!=='ZHCT_APP'
        || $env['POC_PASSWORD']==='') {
        throw new RuntimeException('Unexpected C DB');
    }
    $db=new PDO('dm:host=127.0.0.1;port=5237;schema=ZHCT_APP',
        $env['POC_USER'],$env['POC_PASSWORD'],[
            PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC,
        ]);
    if (strtoupper((string)$db->query(
        "SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL")
        ->fetchColumn())!=='ZHCT_APP') {
        throw new RuntimeException('Wrong schema');
    }
    $queries=[
        'active_dishes'=>'SELECT COUNT(*) FROM YDY_DISHES d WHERE d.STATUS=1'
            .' AND d.IS_DEL=1 AND d.IS_DELETE=0 AND d.STORE_ID=10001',
        'positive_weight_energy'=>'SELECT COUNT(*) FROM YDY_DISHES d'
            .' 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',
        'category_1_to_6'=>'SELECT COUNT(*) FROM YDY_DISHES d'
            .' 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',
        'with_component'=>'SELECT COUNT(*) FROM YDY_DISHES d'
            .' JOIN YDY_DISH c ON c.DISHES_UUID=d.UUID'
            .' 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',
        'with_food'=>'SELECT COUNT(*) 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',
        'minute_sort_1_or_2'=>'SELECT COUNT(*) 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 (1,2)',
    ];
    $counts=[];
    foreach ($queries as $key=>$sql) {
        $counts[$key]=(int)$db->query($sql)->fetchColumn();
    }
    $distribution=$db->query('SELECT c.MINUTE_SORT SORT,COUNT(*) N'
        .' 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 GROUP BY c.MINUTE_SORT ORDER BY N DESC'
        .' FETCH FIRST 30 ROWS ONLY')->fetchAll(PDO::FETCH_ASSOC);
    $validSorts=[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];
    $validSql=implode(',',array_map('intval',$validSorts));
    $candidateSql='SELECT d.UUID D_UUID,d.NAME D_NAME,f.ID F_ID,f.NAME F_NAME'
        .' 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 ('.$validSql.')'
        .' ORDER BY d.ID DESC FETCH FIRST 200 ROWS ONLY';
    $rows=$db->query($candidateSql)->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 ?');
    $firstCandidateUuid=null;
    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']++;
            if ($firstCandidateUuid===null) {
                $firstCandidateUuid=(string)$row['D_UUID'];
            }
        }
    }
    if ($firstCandidateUuid!==null) {
        $recipeStmt=$db->prepare('SELECT COUNT(*) FROM YDY_RECIPES'
            .' WHERE DISHES_UUID=? AND MEAL_TIMES=1');
        $recipeStmt->execute([$firstCandidateUuid]);
        $gate['first_candidate_recipe_count']=(int)$recipeStmt->fetchColumn();
        $actualStmt=$db->prepare('SELECT COUNT(*) FROM YDY_RECIPES r'
            .' WHERE r.DISHES_UUID=? AND r.MEAL_TIMES=1 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)');
        $actualStmt->execute([$firstCandidateUuid]);
        $gate['first_candidate_actual_menu_pair_count']=
            (int)$actualStmt->fetchColumn();
        $gate['first_candidate_uuid_sha12']=
            substr(hash('sha256',$firstCandidateUuid),0,12);
    }
    $counts['recognized_food_structure_relation']=(int)$db->query(
        'SELECT COUNT(*) 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 ('.$validSql.')')->fetchColumn();
    $baseJoin=' 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 JOIN YDY_RECIPES r'
        .' ON r.DISHES_UUID=d.UUID 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 ('.$validSql.') AND r.MEAL_TIMES=1';
    $counts['recognized_with_recipe']=(int)$db->query(
        'SELECT COUNT(DISTINCT d.UUID)'.$baseJoin)->fetchColumn();
    $counts['recognized_with_actual_menu']=(int)$db->query(
        'SELECT COUNT(DISTINCT d.UUID)'.$baseJoin
        .' 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();
    echo json_encode(['counts'=>$counts,'minute_sort_distribution'=>$distribution,
        'anonymous_gate'=>$gate],
        JSON_UNESCAPED_SLASHES).PHP_EOL;
} catch (Throwable $error) {
    echo json_encode(['status'=>'error','class'=>get_class($error),
        'code'=>$error->getCode()]).PHP_EOL;
    exit(1);
}