<?php
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/helpers.php';

$pdo = getConnection();

$sucursal_id = intval($_GET['sucursal_id'] ?? 0);
$where_suc = $sucursal_id ? "AND v.sucursal_id=$sucursal_id" : '';
$inv_suc = $sucursal_id ?: 1;

try {
    $top = isset($_GET['top']) ? min((int)$_GET['top'], 50) : 10;

    // Top 10 best sellers (by quantity)
    $sql = "SELECT p.id, p.NombreComercial as producto, p.CodigoInterno as codigo,
                   p.laboratorio,
                   SUM(dv.cantidad) as qty_vendida,
                   COUNT(DISTINCT dv.venta_id) as veces_vendido,
                   COALESCE(SUM(dv.precio * dv.cantidad),0) as total_venta
            FROM detalle_ventas dv
            JOIN productos p ON p.id = dv.producto_id
            WHERE dv.venta_id IN (SELECT id FROM ventas v WHERE v.fecha_y_hora >= DATE_SUB(NOW(), INTERVAL 6 MONTH) $where_suc)
            GROUP BY dv.producto_id
            ORDER BY qty_vendida DESC LIMIT $top";
    $st = $pdo->query($sql);
    $topVendidos = $st->fetchAll(PDO::FETCH_ASSOC);

    // Slow movers (least sold, with stock)
    $sql2 = "SELECT p.id, p.NombreComercial as producto, p.CodigoInterno as codigo,
                    p.laboratorio,
                    COALESCE(i.stock_total, 0) as stock,
                    COALESCE(SUM(dv.cantidad),0) as qty_vendida
             FROM productos p
             LEFT JOIN inventario_sucursal i ON i.producto_id = p.id AND i.sucursal_id = $inv_suc
             LEFT JOIN detalle_ventas dv ON dv.producto_id = p.id AND dv.venta_id IN (SELECT id FROM ventas v WHERE v.fecha_y_hora >= DATE_SUB(NOW(), INTERVAL 6 MONTH) $where_suc)
             WHERE p.estado = 1 AND COALESCE(i.stock_total, 0) > 0
             GROUP BY p.id
             ORDER BY qty_vendida ASC, stock DESC LIMIT $top";
    $st2 = $pdo->query($sql2);
    $lentos = $st2->fetchAll(PDO::FETCH_ASSOC);

    // Inventory about to expire
    $sql3 = "SELECT p.id, p.NombreComercial as producto, p.CodigoInterno as codigo,
                    l.codigo_lote, l.fechavencimiento, 
                    COALESCE(inv.stock_unidad_base,0) as stock
             FROM lote l
             JOIN productos p ON p.id = l.producto_id
             LEFT JOIN inventario inv ON inv.lote_id = l.id AND inv.sucursal_id = $inv_suc
             WHERE l.fechavencimiento BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH)
             AND COALESCE(inv.stock_unidad_base,0) > 0
             AND l.facturado = 0
             ORDER BY l.fechavencimiento ASC LIMIT 20";
    $st3 = $pdo->query($sql3);
    $porVencer = $st3->fetchAll(PDO::FETCH_ASSOC);

    // Expired inventory
    $sql4 = "SELECT p.id, p.NombreComercial as producto, p.CodigoInterno as codigo,
                    l.codigo_lote, l.fechavencimiento,
                    COALESCE(inv.stock_unidad_base,0) as stock
             FROM lote l
             JOIN productos p ON p.id = l.producto_id
             LEFT JOIN inventario inv ON inv.lote_id = l.id AND inv.sucursal_id = $inv_suc
             WHERE l.fechavencimiento < CURDATE()
             AND COALESCE(inv.stock_unidad_base,0) > 0
             AND l.facturado = 0
             ORDER BY l.fechavencimiento ASC LIMIT 20";
    $st4 = $pdo->query($sql4);
    $vencidos = $st4->fetchAll(PDO::FETCH_ASSOC);

    // Total active products
    $st5 = $pdo->query("SELECT COUNT(*) FROM productos WHERE estado=1");
    $total_productos = (int)$st5->fetchColumn();

    $where_inv = $sucursal_id ? "AND inv.sucursal_id=$sucursal_id" : '';
    $st6 = $pdo->query("SELECT COALESCE(SUM(COALESCE(inv.stock_unidad_base,0)),0) FROM inventario inv JOIN lote l ON l.id=inv.lote_id WHERE l.facturado=0 $where_inv");
    $total_unidades = (int)$st6->fetchColumn();

    json_success([
        'top_vendidos' => $topVendidos,
        'lentos' => $lentos,
        'por_vencer' => $porVencer,
        'vencidos' => $vencidos,
        'total_productos' => $total_productos,
        'total_unidades' => $total_unidades
    ]);
} catch (Exception $e) {
    json_error("Error: " . $e->getMessage());
}
