<?php
// buscar_productos_simplificadosanfe.php
date_default_timezone_set('America/Guatemala');
include 'db_connection.php';
include 'sucursal_config.php'; // Incluir la configuración de la sucursal

// Parámetro de búsqueda
$searchQuery = isset($_GET['query']) ? trim($_GET['query']) : '';
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;

// Columnas en las que se buscará
$searchable_columns = [
    'CodigoInterno',
    'CodigoBarra',
    'NombreComercial',
    'laboratorio',
    'NombreGenerico',
    'FuncionMedica'
];

// Paginación
$limit = 5;
$offset = ($page - 1) * $limit;

// Inicializar variables
$total_products = 0;
$total_pages = 0;
$products = [];

// Función para ejecutar una consulta preparada
function execute_prepared_query($conn, $sql, $types = '', $params = []) {
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return ['success' => false, 'message' => 'Error en la preparación de la consulta: ' . $conn->error];
    }
    if (!empty($types) && !empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    if (!$stmt->execute()) {
        return ['success' => false, 'message' => 'Error en la ejecución de la consulta: ' . $stmt->error];
    }
    $result = $stmt->get_result();
    if ($result === false && $types !== '') {
        return ['success' => false, 'message' => 'Error al obtener el resultado de la consulta: ' . $stmt->error];
    }
    if ($result) {
        $data = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return ['success' => true, 'data' => $data];
    } else {
        $stmt->close();
        return ['success' => true, 'data' => []];
    }
}

// Obtener nombres de sucursales
$sucursales_result = execute_prepared_query($conn, "SELECT id, nombre FROM sucursales");
$sucursal_names = [];
if ($sucursales_result['success'] && count($sucursales_result['data']) > 0) {
    foreach ($sucursales_result['data'] as $sucursal) {
        $sucursal_names[$sucursal['id']] = htmlspecialchars($sucursal['nombre'], ENT_QUOTES, 'UTF-8');
    }
}

// Si hay una consulta de búsqueda
if (!empty($searchQuery)) {
    // 1. Búsqueda Exacta por CodigoInterno o CodigoBarra (LA MÁS ALTA PRIORIDAD)
    $exact_sql = "SELECT * FROM productos WHERE CodigoInterno = ? OR CodigoBarra = ? LIMIT ? OFFSET ?";
    $exact_params = [$searchQuery, $searchQuery, $limit, $offset];
    $exact_types = "ssii";
    $exact_result = execute_prepared_query($conn, $exact_sql, $exact_types, $exact_params);

    if ($exact_result['success'] && count($exact_result['data']) > 0) {
        $products = $exact_result['data'];
        $count_exact_sql = "SELECT COUNT(*) as total FROM productos WHERE CodigoInterno = ? OR CodigoBarra = ?";
        $count_exact_params = [$searchQuery, $searchQuery];
        $count_exact_types = "ss";
        $count_exact_result = execute_prepared_query($conn, $count_exact_sql, $count_exact_types, $count_exact_params);
        if ($count_exact_result['success'] && count($count_exact_result['data']) > 0) {
            $total_products = intval($count_exact_result['data'][0]['total']);
            $total_pages = ceil($total_products / $limit);
        }
    } else {
        // 2. NUEVO: Búsqueda por Frase Completa (SEGUNDA PRIORIDAD)
        $phrase_param = '%' . $searchQuery . '%';
        $phrase_search_columns = ['NombreComercial', 'NombreGenerico', 'laboratorio', 'FuncionMedica'];
        $phrase_where_clauses = [];
        foreach ($phrase_search_columns as $col) {
            $phrase_where_clauses[] = "$col LIKE ?";
        }
        $phrase_where_sql = implode(' OR ', $phrase_where_clauses);

        // Contar resultados de la frase
        $count_phrase_sql = "SELECT COUNT(*) as total FROM productos WHERE $phrase_where_sql";
        $count_phrase_params = array_fill(0, count($phrase_search_columns), $phrase_param);
        $count_phrase_types = str_repeat('s', count($phrase_search_columns));
        $count_phrase_result = execute_prepared_query($conn, $count_phrase_sql, $count_phrase_types, $count_phrase_params);

        if ($count_phrase_result['success'] && $count_phrase_result['data'][0]['total'] > 0) {
            $total_products = intval($count_phrase_result['data'][0]['total']);
            $total_pages = ceil($total_products / $limit);

            // Obtener productos de la frase
            $phrase_sql = "SELECT * FROM productos WHERE $phrase_where_sql LIMIT ? OFFSET ?";
            $phrase_params = array_merge($count_phrase_params, [$limit, $offset]);
            $phrase_types = $count_phrase_types . "ii";
            $phrase_result = execute_prepared_query($conn, $phrase_sql, $phrase_types, $phrase_params);

            if ($phrase_result['success']) {
                $products = $phrase_result['data'];
            }
        } else {
            // 3. Búsqueda por Términos Separados (FALLBACK - Lógica anterior)
            $terms = array_filter(explode(' ', $searchQuery));

            if (!empty($terms)) {
                $where_clauses = [];
                $params = [];
                $param_types = '';

                foreach ($terms as $term) {
                    $term_clauses = [];
                    foreach ($searchable_columns as $column) {
                        $term_clauses[] = "$column LIKE ?";
                        $params[] = '%' . $term . '%';
                        $param_types .= 's';
                    }
                    $where_clauses[] = '(' . implode(' OR ', $term_clauses) . ')';
                }

                $where_sql = implode(' AND ', $where_clauses);
                
                // Contar resultados parciales
                $count_partial_sql = "SELECT COUNT(*) as total FROM productos WHERE $where_sql";
                $count_partial_result = execute_prepared_query($conn, $count_partial_sql, $param_types, $params);

                if ($count_partial_result['success'] && count($count_partial_result['data']) > 0) {
                    $total_products = intval($count_partial_result['data'][0]['total']);
                    $total_pages = ceil($total_products / $limit);
                }
                
                // Obtener productos parciales
                $partial_sql = "SELECT * FROM productos WHERE $where_sql LIMIT ? OFFSET ?";
                $partial_params = array_merge($params, [$limit, $offset]);
                $partial_types = $param_types . "ii";
                $partial_result = execute_prepared_query($conn, $partial_sql, $partial_types, $partial_params);

                if ($partial_result['success']) {
                    $products = $partial_result['data'];
                }
            }
        }
    }
} else {
    // 4. Sin términos de búsqueda, devolver todos los productos con paginación
    $count_sql = "SELECT COUNT(*) as total FROM productos";
    $count_result = execute_prepared_query($conn, $count_sql);

    if ($count_result['success'] && count($count_result['data']) > 0) {
        $total_products = intval($count_result['data'][0]['total']);
        $total_pages = ceil($total_products / $limit);
    }

    $sql = "SELECT * FROM productos LIMIT ? OFFSET ?";
    $all_params = [$limit, $offset];
    $all_types = "ii";

    $all_result = execute_prepared_query($conn, $sql, $all_types, $all_params);

    if ($all_result['success']) {
        $products = $all_result['data'];
    }
}

// 5. Generar la tabla HTML con los resultados
$output = '
<style>
  table#productos-lista { border-collapse: collapse; width: 100%; }
  table#productos-lista th, table#productos-lista td {
    border: 1px solid #ddd;
    padding: 6px;
    font-size: .75rem;
  }
  table#productos-lista thead th {
    background-color: #f0f0f0;
    color: #333;
  }
  .btn-mini {
    padding: .25rem .4rem; 
    font-size: .75rem;
  }
  .pagination { margin-top: 10px; }
  .lab-info {
    background-color: #d0e8f2;
    padding: 2px 4px;
    border-radius: 3px;
    display: inline-block;
    font-weight: bold;
    text-align: center;
    width: 100%;
  }
  .func-info {
    background-color: #f2e8d0;
    padding: 2px 4px;
    border-radius: 3px;
    display: inline-block;
    font-weight: bold;
    text-align: center;
    width: 100%;
  }
  .stock-info {
    background-color: #d4edda;
    padding: 3px 5px;
    border-radius: 4px;
    font-weight: bold;
    color: #155724;
    margin-top: 5px;
    display: inline-block;
    cursor: help;
  }
  .form-check-input {
    width: 1.2em;
    height: 1.2em;
    margin-top: 0.25em;
    cursor: pointer;
    background-color: #e9ecef;
    border: 2px solid #6c757d;
  }
  .form-check-label {
    font-size: 0.9rem;
    cursor: pointer;
    padding-left: 0.25em;
    font-weight: 700;
  }
  .form-check {
    margin-top: 0.25em;
  }
</style>

<table id="productos-lista" class="table">
  <thead>
    <tr>
      <th>Códigos</th>
      <th>Nombre Comercial</th>
      <th>Nombre Genérico</th>
      <th>Precio Unidad</th>
      <th>Precio Blister o docena</th>
      <th>Precio CAJA O MAYOR</th>
      <th>Mixto</th>
    </tr>
  </thead>
  <tbody>
';

if (count($products) > 0) {
    $other_sucursal_ids = [3, 4, 5];

    foreach ($products as $row) {
        // Obtener stock de la sucursal actual
        $stock_sql = "SELECT stock_unidad_base FROM inventario WHERE lote_id = ? AND sucursal_id = ? ORDER BY last_updated DESC LIMIT 1";
        $stock_params = [$row['id'], $sucursal_id];
        $stock_types = "ii";
        $stock_result = execute_prepared_query($conn, $stock_sql, $stock_types, $stock_params);
        
        $existencia = 0;
        if ($stock_result['success'] && count($stock_result['data']) > 0) {
            $existencia = $stock_result['data'][0]['stock_unidad_base'];
        }

        // Obtener stock de otras sucursales para el tooltip
        $tooltip_html_parts = ['<div class="text-start">Stock en otras sucursales:</div>'];
        foreach ($other_sucursal_ids as $other_id) {
            $other_stock_sql = "SELECT stock_unidad_base FROM inventario WHERE lote_id = ? AND sucursal_id = ? ORDER BY last_updated DESC LIMIT 1";
            $other_stock_params = [$row['id'], $other_id];
            $other_stock_types = "ii";
            $other_stock_result = execute_prepared_query($conn, $other_stock_sql, $other_stock_types, $other_stock_params);
            
            $other_existencia = 0;
            if ($other_stock_result['success'] && count($other_stock_result['data']) > 0) {
                $other_existencia = $other_stock_result['data'][0]['stock_unidad_base'];
            }
            
            $sucursal_name = isset($sucursal_names[$other_id]) ? $sucursal_names[$other_id] : "Sucursal {$other_id}";
            $tooltip_html_parts[] = "<div><strong>{$sucursal_name}:</strong> {$other_existencia}</div>";
        }
        $tooltip_html = implode('', $tooltip_html_parts);
        $tooltip_html_escaped = htmlspecialchars($tooltip_html, ENT_QUOTES, 'UTF-8');

        // Escapar caracteres
        $codigoInterno   = htmlspecialchars($row['CodigoInterno'] ?? '', ENT_QUOTES, 'UTF-8');
        $codigoBarra     = htmlspecialchars($row['CodigoBarra'] ?? '', ENT_QUOTES, 'UTF-8');
        $nombreComercial = htmlspecialchars($row['NombreComercial'] ?? '', ENT_QUOTES, 'UTF-8');
        $nombreGenerico  = htmlspecialchars($row['NombreGenerico'] ?? '', ENT_QUOTES, 'UTF-8');
        $funcionMedica   = htmlspecialchars($row['FuncionMedica'] ?? '', ENT_QUOTES, 'UTF-8');
        $laboratorio     = htmlspecialchars($row['laboratorio'] ?? '', ENT_QUOTES, 'UTF-8');

        // Formatear precios
        $precioUnidad    = number_format($row['PrecioVentaUnidad'] ?? 0, 2, '.', ',');
        $precioBlister   = number_format($row['PrecioVentaBlister'] ?? 0, 2, '.', ',');
        $precioMayor     = number_format($row['PrecioVentaMayor'] ?? 0, 2, '.', ',');
        $precioMixto     = number_format($row['PrecioVentaMixto'] ?? 0, 2, '.', ',');

        // Construir fila
        $output .= "
        <tr>
          <td>
            <strong>{$codigoInterno}</strong><br>
            <small style='color: #777;'>{$codigoBarra}</small>
            <div class='stock-info' data-bs-toggle='tooltip' data-bs-html='true' title='{$tooltip_html_escaped}'>Stock: {$existencia}</div>
          </td>
          <td>
            {$nombreComercial}
            <small class='text-muted d-block lab-info'>Lab: {$laboratorio}</small>
          </td>
          <td>
            {$nombreGenerico}
            <small class='text-muted d-block func-info'>Función: {$funcionMedica}</small>
          </td>
          <td>
              <div class='d-flex flex-column align-items-start'>
                  <button class='btn btn-sm btn-success add-to-cart btn-mini mb-1'
                          data-name='{$nombreComercial}'
                          data-lab='{$laboratorio}'
                          data-price='{$row['PrecioVentaUnidad']}'>
                      Q {$precioUnidad}
                  </button>
                  <div class='form-check'>
                      <input class='form-check-input facturar-check' type='checkbox' value='1' id='facturar_unidad_{$row['id']}'
                             data-name='{$nombreComercial}'
                             data-lab='{$laboratorio}'
                             data-price='{$row['PrecioVentaUnidad']}'>
                      <label class='form-check-label' for='facturar_unidad_{$row['id']}'>
                          Facturar
                      </label>
                  </div>
              </div>
          </td>
          <td>
              <div class='d-flex flex-column align-items-start'>
                  <button class='btn btn-sm btn-primary add-to-cart btn-mini mb-1'
                          data-name='{$nombreComercial}'
                          data-lab='{$laboratorio}'
                          data-price='{$row['PrecioVentaBlister']}'>
                      Q {$precioBlister}
                  </button>
                  <div class='form-check'>
                      <input class='form-check-input facturar-check' type='checkbox' value='1' id='facturar_blister_{$row['id']}'
                             data-name='{$nombreComercial}'
                             data-lab='{$laboratorio}'
                             data-price='{$row['PrecioVentaBlister']}'>
                      <label class='form-check-label' for='facturar_blister_{$row['id']}'>
                          Facturar
                      </label>
                  </div>
              </div>
          </td>
          <td>
              <div class='d-flex flex-column align-items-start'>
                  <button class='btn btn-sm btn-primary add-to-cart btn-mini mb-1'
                          data-name='{$nombreComercial}'
                          data-lab='{$laboratorio}'
                          data-price='{$row['PrecioVentaMayor']}'>
                      Q {$precioMayor}
                  </button>
                  <div class='form-check'>
                      <input class='form-check-input facturar-check' type='checkbox' value='1' id='facturar_mayor_{$row['id']}'
                             data-name='{$nombreComercial}'
                             data-lab='{$laboratorio}'
                             data-price='{$row['PrecioVentaMayor']}'>
                      <label class='form-check-label' for='facturar_mayor_{$row['id']}'>
                          Facturar
                      </label>
                  </div>
              </div>
          </td>
          <td>
              <div class='d-flex flex-column align-items-start'>
                  <button class='btn btn-sm btn-warning add-to-cart btn-mini mb-1'
                          data-name='{$nombreComercial}'
                          data-lab='{$laboratorio}'
                          data-price='{$row['PrecioVentaMixto']}'>
                      Q {$precioMixto}
                  </button>
                  <div class='form-check'>
                      <input class='form-check-input facturar-check' type='checkbox' value='1' id='facturar_mixto_{$row['id']}'
                             data-name='{$nombreComercial}'
                             data-lab='{$laboratorio}'
                             data-price='{$row['PrecioVentaMixto']}'>
                      <label class='form-check-label' for='facturar_mixto_{$row['id']}'>
                          Facturar
                      </label>
                  </div>
              </div>
          </td>
        </tr>";
    }
} else {
    $output .= "
    <tr>
      <td colspan='9' class='text-center'>No se encontraron productos.</td>
    </tr>";
}

$output .= '</tbody></table>';

// 6. Generar la paginación si hay más de una página
if ($total_pages > 1) {
    $output .= '<nav><ul class="pagination justify-content-center">';

    // Prev
    if ($page > 1) {
        $prev_page = $page - 1;
        $output .= '<li class="page-item">
            <a class="page-link" href="#" data-page="' . $prev_page . '">&laquo;</a>
        </li>';
    } else {
        $output .= '<li class="page-item disabled">
            <span class="page-link">&laquo;</span>
        </li>';
    }

    // Mostrar un rango de páginas alrededor de la página actual
    $max_display = 5;
    $start = max(1, $page - floor($max_display / 2));
    $end = min($total_pages, $start + $max_display - 1);
    if ($end - $start + 1 < $max_display) {
        $start = max(1, $end - $max_display + 1);
    }

    for ($i = $start; $i <= $end; $i++) {
        if ($i == $page) {
            $output .= '<li class="page-item active"><span class="page-link">' . $i . '</span></li>';
        } else {
            $output .= '<li class="page-item"><a class="page-link" href="#" data-page="' . $i . '">' . $i . '</a></li>';
        }
    }

    // Next
    if ($page < $total_pages) {
        $next_page = $page + 1;
        $output .= '<li class="page-item">
            <a class="page-link" href="#" data-page="' . $next_page . '">&raquo;</a>
        </li>';
    } else {
        $output .= '<li class="page-item disabled">
            <span class="page-link">&raquo;</span>
        </li>';
    }

    $output .= '</ul></nav>';
}

// Inicializar tooltips de Bootstrap
$output .= '<script>
  var tooltipTriggerList = [].slice.call(document.querySelectorAll(\'[data-bs-toggle="tooltip"]\'))
  var tooltipList = tooltipTriggerList.map(function (tooltipTriggerEl) {
    return new bootstrap.Tooltip(tooltipTriggerEl)
  })
</script>';

echo $output;
?>
