<?php
require_once __DIR__ . '/../config/db.php';

try {
    $pdo = getConnection();
    
    // Count total products
    $total = $pdo->query("SELECT COUNT(*) FROM productos")->fetchColumn();
    echo "Total products: $total\n";
    
    // Count blister presentations with factor 10
    $blisters = $pdo->query("SELECT COUNT(*) FROM producto_presentaciones WHERE presentacion_id = 2 AND factor_conversion = 10 AND activo = 1")->fetchColumn();
    echo "Active blister presentations with factor = 10: $blisters\n";

    // Count caja presentations with factor 100
    $cajas = $pdo->query("SELECT COUNT(*) FROM producto_presentaciones WHERE presentacion_id = 3 AND factor_conversion = 100 AND activo = 1")->fetchColumn();
    echo "Active caja presentations with factor = 100: $cajas\n";
    
    // Count products having BOTH blister=10 and caja=100
    $both = $pdo->query("SELECT COUNT(DISTINCT p.id) FROM productos p
                         JOIN producto_presentaciones pp2 ON p.id = pp2.producto_id AND pp2.presentacion_id = 2 AND pp2.factor_conversion = 10 AND pp2.activo = 1
                         JOIN producto_presentaciones pp3 ON p.id = pp3.producto_id AND pp3.presentacion_id = 3 AND pp3.factor_conversion = 100 AND pp3.activo = 1")->fetchColumn();
    echo "Products with BOTH active blister=10 and caja=100: $both\n";
    
    echo "\nListing some of these products:\n";
    $stmt = $pdo->query("SELECT p.id, p.CodigoInterno, p.NombreComercial FROM productos p
                         JOIN producto_presentaciones pp2 ON p.id = pp2.producto_id AND pp2.presentacion_id = 2 AND pp2.factor_conversion = 10 AND pp2.activo = 1
                         JOIN producto_presentaciones pp3 ON p.id = pp3.producto_id AND pp3.presentacion_id = 3 AND pp3.factor_conversion = 100 AND pp3.activo = 1
                         LIMIT 10");
    $list = $stmt->fetchAll();
    foreach ($list as $item) {
        echo "[{$item['CodigoInterno']}] {$item['NombreComercial']} (ID: {$item['id']})\n";
    }
    
    echo "\nListing 10 most recently created products:\n";
    $stmtRecent = $pdo->query("SELECT id, CodigoInterno, NombreComercial, estado FROM productos ORDER BY id DESC LIMIT 10");
    $recent = $stmtRecent->fetchAll();
    foreach ($recent as $item) {
        $pid = $item['id'];
        $stmtF = $pdo->prepare("SELECT presentacion_id, factor_conversion, activo FROM producto_presentaciones WHERE producto_id = ?");
        $stmtF->execute([$pid]);
        $factors = [];
        foreach ($stmtF->fetchAll() as $f) {
            $factors[] = "pres_id: {$f['presentacion_id']} (factor: {$f['factor_conversion']}, active: {$f['activo']})";
        }
        echo "[{$item['CodigoInterno']}] {$item['NombreComercial']} (ID: $pid, Status: {$item['estado']}) - " . implode(", ", $factors) . "\n";
    }
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
