<?php

declare(strict_types=1);

/**
 * Read-only: dump the current product structure - the sys_category tree and
 * every product record with its category relations - to compare against the
 * agency's "Merged site map" V2 (Products tab, columns J/K/L).
 *
 * Usage:  php migrations/check_product_structure.php
 */

$root = null;
$dir = __DIR__;
for ($i = 0; $i < 5; $i++) {
    if (is_file($dir . '/config/system/settings.php')) {
        $root = $dir;
        break;
    }
    $dir = dirname($dir);
}
if ($root === null) {
    exit("Could not locate project root above " . __DIR__ . "\n");
}

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

// --- sys_category tree ------------------------------------------------------
$cats = $pdo->query(
    "SELECT uid, parent, title, hidden, sorting FROM sys_category WHERE deleted = 0 ORDER BY parent, sorting"
)->fetchAll();
$byParent = [];
foreach ($cats as $cat) {
    $byParent[(int)$cat['parent']][] = $cat;
}

// product count per category (MM to our product table)
$counts = [];
foreach ($pdo->query(
    "SELECT mm.uid_local AS cat, COUNT(*) AS n
     FROM sys_category_record_mm mm
     JOIN tx_vitec_domain_model_product p ON p.uid = mm.uid_foreign AND p.deleted = 0
     WHERE mm.tablenames = 'tx_vitec_domain_model_product' AND mm.fieldname = 'categories'
     GROUP BY mm.uid_local"
) as $row) {
    $counts[(int)$row['cat']] = (int)$row['n'];
}

echo "=== sys_category tree (uid | title | hidden? | #products) ===\n";
$printTree = function (int $parent, int $depth) use (&$printTree, $byParent, $counts): void {
    foreach ($byParent[$parent] ?? [] as $cat) {
        $uid = (int)$cat['uid'];
        printf(
            "%s%d | %s%s%s\n",
            str_repeat('    ', $depth),
            $uid,
            $cat['title'],
            $cat['hidden'] ? ' | HIDDEN' : '',
            isset($counts[$uid]) ? ' | ' . $counts[$uid] . ' products' : ''
        );
        $printTree($uid, $depth + 1);
    }
};
$printTree(0, 0);

// --- products with their categories ----------------------------------------
$products = $pdo->query(
    "SELECT uid, title, slug, hidden, legacy, subproduct, supportproduct
     FROM tx_vitec_domain_model_product WHERE deleted = 0 ORDER BY title"
)->fetchAll();

$catTitles = [];
foreach ($cats as $cat) {
    $catTitles[(int)$cat['uid']] = $cat['title'];
}
$prodCats = [];
foreach ($pdo->query(
    "SELECT uid_foreign AS product, uid_local AS cat
     FROM sys_category_record_mm
     WHERE tablenames = 'tx_vitec_domain_model_product' AND fieldname = 'categories'"
) as $row) {
    $prodCats[(int)$row['product']][] = $catTitles[(int)$row['cat']] ?? ('?' . $row['cat']);
}

echo "\n=== products (uid | title | slug | flags | categories) ===\n";
foreach ($products as $p) {
    $uid = (int)$p['uid'];
    $flags = [];
    foreach (['hidden', 'legacy', 'subproduct', 'supportproduct'] as $flag) {
        if ($p[$flag]) {
            $flags[] = $flag;
        }
    }
    printf(
        "%d | %s | %s | %s | %s\n",
        $uid,
        $p['title'],
        $p['slug'],
        $flags === [] ? '-' : implode(',', $flags),
        isset($prodCats[$uid]) ? implode(' + ', $prodCats[$uid]) : 'KEINE KATEGORIE'
    );
}
printf("\n%d categories, %d products total\n", count($cats), count($products));