<?php

/**
 * Read-only diagnosis: why do success stories emit empty `markets`?
 *
 * Modeled on migrations/check_bg.php - PDO against the credentials from
 * config/system/settings.php, no TYPO3 bootstrap, SELECT only.
 *
 * Run on the server:  php migrations/check_usecase_markets.php
 */

declare(strict_types=1);

$settings = require __DIR__ . '/../config/system/settings.php';
$db = $settings['DB']['Connections']['Default'];
$pdo = new PDO(
    sprintf('mysql:host=%s;dbname=%s;charset=utf8mb4', $db['host'], $db['dbname']),
    $db['user'],
    $db['password'],
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

function one(PDO $pdo, string $sql): array
{
    return $pdo->query($sql)->fetch(PDO::FETCH_ASSOC) ?: [];
}
function all(PDO $pdo, string $sql): array
{
    return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

echo "=== 1. MM-Tabellen: Zeilen ueberhaupt da? ===\n";
foreach (['tx_vitec_usecase_market_mm', 'tx_vitec_usecase_solution_mm'] as $mm) {
    $n = one($pdo, "SELECT COUNT(*) c FROM {$mm}");
    echo sprintf("%-32s %s Zeilen\n", $mm, $n['c'] ?? '?');
}

echo "\n=== 2. Waisen: MM -> Markets, die es nicht (mehr) gibt ===\n";
echo "verknuepfte Market-uids und ihr Zustand:\n";
$rows = all($pdo, "
    SELECT mm.uid_foreign AS market_uid,
           COUNT(*) AS stories,
           MAX(m.uid IS NULL) AS fehlt_ganz,
           MAX(COALESCE(m.deleted, -1)) AS deleted,
           MAX(COALESCE(m.hidden, -1)) AS hidden,
           MAX(COALESCE(m.title, '?')) AS title
    FROM tx_vitec_usecase_market_mm mm
    LEFT JOIN tx_vitec_domain_model_market m ON m.uid = mm.uid_foreign
    GROUP BY mm.uid_foreign
    ORDER BY mm.uid_foreign
");
foreach ($rows as $r) {
    $state = $r['fehlt_ganz'] ? 'FEHLT KOMPLETT' : (($r['deleted'] ?? 0) ? 'GELOESCHT' : ((($r['hidden'] ?? 0) ? 'versteckt' : 'ok')));
    echo sprintf("  market uid %-5s %-15s %-40s (%s Stories)\n", $r['market_uid'], $state, mb_substr((string)$r['title'], 0, 40), $r['stories']);
}

echo "\n=== 3. Markets-Tabelle: Bestand ===\n";
$m = one($pdo, "SELECT COUNT(*) total, SUM(deleted) del, SUM(hidden) hid, MIN(uid) minuid, MAX(uid) maxuid FROM tx_vitec_domain_model_market");
echo sprintf("gesamt %s | geloescht %s | versteckt %s | uid-Bereich %s-%s\n", $m['total'], $m['del'], $m['hid'], $m['minuid'], $m['maxuid']);
echo "geloeschte Markets (falls vorhanden):\n";
foreach (all($pdo, "SELECT uid, title, FROM_UNIXTIME(tstamp) t FROM tx_vitec_domain_model_market WHERE deleted = 1 ORDER BY uid") as $r) {
    echo sprintf("  uid %-5s %-45s zuletzt %s\n", $r['uid'], mb_substr((string)$r['title'], 0, 45), $r['t']);
}

echo "\n=== 4. sys_category-Zuordnungen der Stories (JSON-Feld `categories`) ===\n";
$c = one($pdo, "SELECT COUNT(*) c FROM sys_category_record_mm WHERE tablenames = 'tx_vitec_domain_model_usecase'");
echo "sys_category_record_mm fuer usecases: " . ($c['c'] ?? '?') . " Zeilen\n";

echo "\n=== 5. Stichprobe: 3 Stories mit ihren MM-Zeilen ===\n";
foreach (all($pdo, "
    SELECT u.uid, u.title,
           (SELECT COUNT(*) FROM tx_vitec_usecase_market_mm mm WHERE mm.uid_local = u.uid) AS mmrows
    FROM tx_vitec_domain_model_usecase u
    WHERE u.deleted = 0
    ORDER BY u.uid LIMIT 3
") as $r) {
    echo sprintf("  story uid %-4s %-40s -> %s MM-Zeile(n)\n", $r['uid'], mb_substr((string)$r['title'], 0, 40), $r['mmrows']);
}

echo "\nFertig - nichts geschrieben.\n";