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

try {
    $pdo = getConnection();
    $pdo->beginTransaction();
    
    // Keywords for tablet/capsule products that should have active presentations
    $restoreKeywords = [
        'tableta', 'tabletas', 'capsula', 'capsulas', 'gelatina', 
        'blanda', 'blandas', 'caps', 'comprimido', 'comprimidos', 
        'pastilla', 'pastillas', 'tablet', 'tablets', 'tabs', 'tab'
    ];
    
    $whereTerms = [];
    $params = [];
    foreach ($restoreKeywords 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 restore (were deactivated on June 8th but match tablet/capsule keywords)
    $sqlSelect = "SELECT DISTINCT pp.producto_id FROM producto_presentaciones pp
                  JOIN productos p ON pp.producto_id = p.id
                  WHERE pp.presentacion_id IN (2, 3) AND pp.activo = 0 AND DATE(pp.fecha_actualizacion) = '2026-06-08'
                  AND $whereClause";
                  
    $stmtSelect = $pdo->prepare($sqlSelect);
    $stmtSelect->execute($params);
    $productIds = $stmtSelect->fetchAll(PDO::FETCH_COLUMN);
    
    echo "Identified " . count($productIds) . " false positive products to restore.\n";
    
    if (count($productIds) > 0) {
        // 2. Perform the update: Reactivate blister (2) and caja (3) for these products
        $inClause = implode(',', array_fill(0, count($productIds), '?'));
        
        $sqlUpdate = "UPDATE producto_presentaciones 
                      SET activo = 1, 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 restored (reactivated) $updatedRows presentation rows in database.\n";
    } else {
        echo "No products required restoration.\n";
    }
    
    $pdo->commit();
    echo "Transaction committed successfully.\n";

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