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

try {
    $pdo = getConnection();
    
    // Keywords for products that shouldn't have blister presentations
    $keywords = [
        'suspension', 'jarabe', 'gotas', 'aceite', 'jabon', 'jeringa', 'vial', 
        'crema', 'shampoo', 'spray', 'gel', 'pomada', 'ampolla', 'inyect', 
        'solucion', 'suero', 'alcohol', 'gasa', 'venda', 'algodon', 'panal', 
        'toalla', 'cepillo', 'pasta', 'desodorante', 'talco', 'liquida', 'liquido',
        'elixir', 'unguento', 'locion', 'aerosol', 'colirio', 'enema', 'ovulo', 'jabón'
    ];
    
    $whereTerms = [];
    $params = [];
    foreach ($keywords as $idx => $kw) {
        $whereTerms[] = "p.NombreComercial LIKE :kw_$idx OR p.NombreGenerico LIKE :kw_$idx";
        $params[":kw_$idx"] = "%$kw%";
    }
    
    $whereClause = "(" . implode(" OR ", $whereTerms) . ")";
    
    $sql = "SELECT p.id, p.CodigoInterno, p.NombreComercial, p.NombreGenerico 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
            WHERE $whereClause";
            
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $products = $stmt->fetchAll();
    
    echo "Found " . count($products) . " products that match non-tablet/liquid keywords and have active blister=10 & caja=100:\n\n";
    
    // Show first 50
    $shown = 0;
    foreach ($products as $p) {
        if ($shown < 50) {
            echo "[{$p['CodigoInterno']}] {$p['NombreComercial']} (Generic: {$p['NombreGenerico']}) (ID: {$p['id']})\n";
            $shown++;
        }
    }
    
    if (count($products) > 50) {
        echo "... and " . (count($products) - 50) . " more products.\n";
    }

} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
