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

try {
    $pdo = getConnection();
    $pdo->beginTransaction();
    
    // 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) . ")";
    
    // 1. Identify product IDs to deactivate
    $sqlSelect = "SELECT 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
                  WHERE $whereClause";
                  
    $stmtSelect = $pdo->prepare($sqlSelect);
    $stmtSelect->execute($params);
    $productIds = $stmtSelect->fetchAll(PDO::FETCH_COLUMN);
    
    echo "Identified " . count($productIds) . " products to update.\n";
    
    if (count($productIds) > 0) {
        // 2. Perform the update: Deactivate blister (2) and caja (3) for identified products
        $inClause = implode(',', array_fill(0, count($productIds), '?'));
        
        $sqlUpdate = "UPDATE producto_presentaciones 
                      SET activo = 0, fecha_actualizacion = NOW()
                      WHERE producto_id IN ($inClause) AND presentacion_id IN (2, 3) AND factor_conversion IN (10, 100)";
                      
        $stmtUpdate = $pdo->prepare($sqlUpdate);
        $stmtUpdate->execute($productIds);
        
        $updatedRows = $stmtUpdate->rowCount();
        echo "Successfully updated (deactivated) $updatedRows presentation rows in database.\n";
    } else {
        echo "No products required updates.\n";
    }
    
    $pdo->commit();
    echo "Transaction committed successfully.\n";

} catch (Exception $e) {
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Error: " . $e->getMessage() . "\n";
}
