<?php

declare(strict_types=1);

require_once __DIR__.'/../vendor/autoload.php';

use Doctrine\DBAL\DriverManager;

$conn = DriverManager::getConnection([
    'driver'   => 'pdo_pgsql',
    'host'     => 'localhost',
    'port'     => 5432,
    'dbname'   => 'inventory',
    'user'     => 'ferme_user',
    'password' => 'fermerecette',
]);

echo "--- Piece 'Arbre du palier pied E1' ---\n";
$rows = $conn->fetchAllAssociative("SELECT p.name, cfv.value, cf.name as field_name FROM pieces p JOIN custom_field_values cfv ON cfv.pieceid = p.id JOIN custom_fields cf ON cf.id = cfv.customfieldid WHERE p.id = 'cl3d978dd4b071daff8fb185f7' ORDER BY cf.orderindex");
foreach ($rows as $r) {
    echo sprintf("  %s: '%s'\n", $r['field_name'], $r['value']);
}

echo "\n--- Composant 'Cage écureuil pied E8' ---\n";
$rows = $conn->fetchAllAssociative("SELECT c.name, cfv.value, cf.name as field_name FROM composants c JOIN custom_field_values cfv ON cfv.composantid = c.id JOIN custom_fields cf ON cf.id = cfv.customfieldid WHERE c.id = 'cl5b5e336095de8d4ece81b2dc' ORDER BY cf.orderindex");
foreach ($rows as $r) {
    echo sprintf("  %s: '%s'\n", $r['field_name'], $r['value']);
}

echo "\n--- Count empty piece values (ModelType Arbre) ---\n";
$count = $conn->fetchOne("SELECT COUNT(*) FROM pieces p JOIN custom_field_values cfv ON cfv.pieceid = p.id WHERE p.typepieceid = 'cmgujpyjf002q4705j6hv1nkk' AND (cfv.value = '' OR cfv.value IS NULL)");
echo sprintf("  Empty values: %d\n", $count);

echo "\n--- Count orphaned CustomField definitions ---\n";
$count = $conn->fetchOne('SELECT COUNT(*) FROM custom_fields WHERE typecomposantid IS NULL AND typepieceid IS NULL AND typeproductid IS NULL');
echo sprintf("  Orphaned CFs: %d\n", $count);