<?php
header('Content-Type: application/json; charset=utf-8');
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/helpers.php';

$pdo = getConnection();
$action = $_GET['action'] ?? '';

try {
    switch ($action) {

        // ======================= RESUMEN / DASHBOARD =======================
        case 'resumen':
            $sucursal_id = intval($_GET['sucursal_id'] ?? 0);
            $dias = max(1, min(365, intval($_GET['dias'] ?? 30)));
            $filtro_suc = $sucursal_id ? "AND v.sucursal_id=$sucursal_id" : '';

            // Ventas ultimos N dias
            $s = $pdo->query("SELECT COUNT(*) as total_ventas, COALESCE(SUM(total),0) as total_vendido,
                COALESCE(AVG(total),0) as ticket_promedio FROM ventas v WHERE v.fecha_y_hora >= DATE_SUB(NOW(), INTERVAL $dias DAY) $filtro_suc");
            $ventas = $s->fetch();

            // Productos vendidos
            $s2 = $pdo->query("SELECT COALESCE(SUM(dv.cantidad),0) as unidades_vendidas FROM detalle_ventas dv
                JOIN ventas v ON v.id=dv.venta_id WHERE v.fecha_y_hora >= DATE_SUB(NOW(), INTERVAL $dias DAY) $filtro_suc");
            $unidades = $s2->fetchColumn();

            $filtro_inv_suc = $sucursal_id ? "AND sucursal_id=$sucursal_id" : '';

            // Productos con bajo stock
            $s3 = $pdo->query("SELECT COUNT(*) as bajo_stock FROM inventario_sucursal WHERE stock_total <= stock_minimo AND stock_minimo > 0 $filtro_inv_suc");
            $bajo_stock = intval($s3->fetchColumn());

            // Productos agotados (stock = 0)
            $s4 = $pdo->query("SELECT COUNT(*) as agotados FROM inventario_sucursal WHERE stock_total <= 0 $filtro_inv_suc");
            $agotados = intval($s4->fetchColumn());

            // Ventas por sucursal
            $filtro_suc_group = $sucursal_id ? "AND v.sucursal_id=$sucursal_id" : '';
            $s5 = $pdo->query("SELECT s.id, s.nombre, COUNT(v.id) as ventas, COALESCE(SUM(v.total),0) as total
                FROM ventas v JOIN sucursales s ON s.id=v.sucursal_id
                WHERE v.fecha_y_hora >= DATE_SUB(NOW(), INTERVAL $dias DAY) $filtro_suc_group
                GROUP BY s.id ORDER BY total DESC");
            $ventas_suc = $s5->fetchAll();

            // Top productos mas vendidos
            $s6 = $pdo->query("SELECT p.id, p.NombreComercial as nombre, SUM(dv.cantidad) as cantidad, SUM(dv.subtotal) as total
                FROM detalle_ventas dv JOIN ventas v ON v.id=dv.venta_id
                JOIN productos p ON p.id=dv.producto_id
                WHERE v.fecha_y_hora >= DATE_SUB(NOW(), INTERVAL $dias DAY) $filtro_suc
                GROUP BY p.id ORDER BY cantidad DESC LIMIT 20");
            $top_productos = $s6->fetchAll();

            json_success([
                'ventas' => [
                    'total_ventas' => intval($ventas['total_ventas']),
                    'total_vendido' => floatval($ventas['total_vendido']),
                    'ticket_promedio' => floatval($ventas['ticket_promedio']),
                    'unidades_vendidas' => intval($unidades)
                ],
                'inventario' => [
                    'bajo_stock' => $bajo_stock,
                    'agotados' => $agotados
                ],
                'ventas_por_sucursal' => $ventas_suc,
                'top_productos' => $top_productos
            ]);
            break;

        // ======================= QUIEBRES (STOCKOUT PREDICTION) =======================
        case 'quiebres':
            $sucursal_id = intval($_GET['sucursal_id'] ?? 0);
            $dias_historial = max(30, min(365, intval($_GET['dias_historial'] ?? 90)));

            $where_suc = $sucursal_id ? "AND inv.sucursal_id=$sucursal_id" : '';
            $join_suc = $sucursal_id ? "AND v.sucursal_id=$sucursal_id" : '';

            // Get all products with stock, compute sales velocity
            $q = "SELECT inv.id, inv.producto_id, inv.sucursal_id, inv.stock_total, inv.stock_minimo,
                p.NombreComercial as producto, p.CodigoInterno as codigo, p.id_categoria,
                c.nombre as categoria, s.nombre as sucursal,
                COALESCE(lc.dias_entrega, 7) as lead_time_dias,
                COALESCE(vs.ventas_30d, 0) as ventas_30d,
                COALESCE(vs.cantidad_30d, 0) as cantidad_30d
                FROM inventario_sucursal inv
                JOIN productos p ON p.id=inv.producto_id
                JOIN sucursales s ON s.id=inv.sucursal_id
                LEFT JOIN categorias c ON c.id=p.id_categoria
                LEFT JOIN lugarcompra lc ON lc.id=1 -- default supplier lead time
                LEFT JOIN (
                    SELECT dv.producto_id, v.sucursal_id,
                        COUNT(DISTINCT v.id) as ventas_30d,
                        SUM(dv.cantidad) as cantidad_30d
                    FROM detalle_ventas dv
                    JOIN ventas v ON v.id=dv.venta_id
                    WHERE v.fecha_y_hora >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                    GROUP BY dv.producto_id, v.sucursal_id
                ) vs ON vs.producto_id=inv.producto_id $join_suc
                WHERE inv.stock_total >= 0
                ORDER BY inv.stock_total ASC LIMIT 500";

            $rows = $pdo->query($q)->fetchAll();
            $result = [];

            foreach ($rows as $r) {
                $stock = intval($r['stock_total']);
                $minimo = intval($r['stock_minimo']);
                $ventas_30d = intval($r['cantidad_30d']);
                $velocidad_diaria = $ventas_30d / 30.0;
                $lead_time = max(1, intval($r['lead_time_dias']));

                $dias_restantes = $velocidad_diaria > 0 ? floor($stock / $velocidad_diaria) : 999;
                $punto_reorden = ceil($velocidad_diaria * $lead_time * 1.5); // 1.5x safety factor
                $alerta = 'ok';
                $color = 'text-emerald-400';

                if ($stock <= 0) {
                    $alerta = 'agotado';
                    $color = 'text-red-400';
                } elseif ($stock <= $minimo && $minimo > 0) {
                    $alerta = 'critico';
                    $color = 'text-red-400';
                } elseif ($dias_restantes <= $lead_time) {
                    $alerta = 'por_agotarse';
                    $color = 'text-amber-400';
                } elseif ($dias_restantes <= $lead_time * 2) {
                    $alerta = 'atencion';
                    $color = 'text-yellow-400';
                }

                $result[] = [
                    'producto_id' => $r['producto_id'],
                    'producto' => $r['producto'],
                    'codigo' => $r['codigo'],
                    'categoria' => $r['categoria'],
                    'sucursal' => $r['sucursal'],
                    'stock_actual' => $stock,
                    'stock_minimo' => $minimo,
                    'ventas_30d' => $ventas_30d,
                    'velocidad_diaria' => round($velocidad_diaria, 2),
                    'dias_restantes' => $dias_restantes,
                    'lead_time' => $lead_time,
                    'punto_reorden' => $punto_reorden,
                    'alerta' => $alerta,
                    'color' => $color
                ];
            }

            // Sort: worst first
            $order = ['agotado'=>0, 'critico'=>1, 'por_agotarse'=>2, 'atencion'=>3, 'ok'=>4];
            usort($result, function($a, $b) use ($order) {
                return ($order[$a['alerta']] ?? 9) - ($order[$b['alerta']] ?? 9);
            });

            $stats = [
                'agotados' => count(array_filter($result, fn($r) => $r['alerta']==='agotado')),
                'criticos' => count(array_filter($result, fn($r) => $r['alerta']==='critico')),
                'por_agotarse' => count(array_filter($result, fn($r) => $r['alerta']==='por_agotarse')),
                'atencion' => count(array_filter($result, fn($r) => $r['alerta']==='atencion')),
                'ok' => count(array_filter($result, fn($r) => $r['alerta']==='ok')),
            ];

            json_success(['productos'=>$result, 'stats'=>$stats]);
            break;

        // ======================= ESTACIONALIDAD =======================
        case 'estacionalidad':
            $anio = intval($_GET['anio'] ?? date('Y'));
            $categoria_id = intval($_GET['categoria_id'] ?? 0);
            $sucursal_id = intval($_GET['sucursal_id'] ?? 0);
            $where_cat = $categoria_id ? "AND p.id_categoria=$categoria_id" : '';
            $where_suc_est = $sucursal_id ? "AND v.sucursal_id=$sucursal_id" : '';

            // Sales by month for given year
            $q = "SELECT MONTH(v.fecha_y_hora) as mes,
                    COUNT(DISTINCT v.id) as total_ventas,
                    COALESCE(SUM(dv.cantidad),0) as unidades_vendidas,
                    COALESCE(SUM(dv.subtotal),0) as total_vendido
                FROM ventas v
                JOIN detalle_ventas dv ON dv.venta_id=v.id
                JOIN productos p ON p.id=dv.producto_id
                WHERE YEAR(v.fecha_y_hora)=$anio $where_cat $where_suc_est
                GROUP BY MONTH(v.fecha_y_hora)
                ORDER BY mes";
            $mensual = $pdo->query($q)->fetchAll();

            // Same for previous year
            $q2 = "SELECT MONTH(v.fecha_y_hora) as mes,
                    COALESCE(SUM(dv.subtotal),0) as total_vendido
                FROM ventas v
                JOIN detalle_ventas dv ON dv.venta_id=v.id
                JOIN productos p ON p.id=dv.producto_id
                WHERE YEAR(v.fecha_y_hora)=($anio-1) $where_cat $where_suc_est
                GROUP BY MONTH(v.fecha_y_hora)
                ORDER BY mes";
            $anterior = $pdo->query($q2)->fetchAll();

            // Sales by weekday
            $q3 = "SELECT DAYOFWEEK(v.fecha_y_hora) as dia,
                    COUNT(*) as ventas, COALESCE(SUM(v.total),0) as total
                FROM ventas v
                WHERE YEAR(v.fecha_y_hora)=$anio $where_suc_est
                GROUP BY dia ORDER BY dia";
            $semana = $pdo->query($q3)->fetchAll();

            // Categories
            $cats = $pdo->query("SELECT * FROM categorias ORDER BY nombre")->fetchAll();
            // Sales by category for the year
            $q4 = "SELECT c.id, c.nombre, COUNT(DISTINCT v.id) as ventas,
                    COALESCE(SUM(dv.cantidad),0) as unidades,
                    COALESCE(SUM(dv.subtotal),0) as total
                FROM detalle_ventas dv
                JOIN ventas v ON v.id=dv.venta_id
                JOIN productos p ON p.id=dv.producto_id
                JOIN categorias c ON c.id=p.id_categoria
                WHERE YEAR(v.fecha_y_hora)=$anio $where_cat $where_suc_est
                GROUP BY c.id ORDER BY total DESC";
            $cats_ventas = $pdo->query($q4)->fetchAll();

            json_success([
                'mensual' => $mensual,
                'anterior' => $anterior,
                'semana' => $semana,
                'categorias' => $cats,
                'categorias_ventas' => $cats_ventas,
                'anio' => $anio,
                'anio_anterior' => $anio - 1
            ]);
            break;

        // ======================= ALERTAS DE COMPRAS INTELIGENTES =======================
        case 'alertas_compras':
            $sucursal_id = intval($_GET['sucursal_id'] ?? 0);
            $where_suc = $sucursal_id ? "AND inv.sucursal_id=$sucursal_id" : '';

            // Products that need reordering
            $q = "SELECT inv.*, p.NombreComercial as producto, p.CodigoInterno as codigo,
                    c.nombre as categoria, s.nombre as sucursal,
                    plc.costo_compra, plc.lugar_compra_id, lc.nombre as proveedor,
                    lc.dias_entrega as lead_time,
                    COALESCE(vs.cantidad_30d, 0) as cantidad_30d,
                    COALESCE(vs.total_30d, 0) as total_30d
                FROM inventario_sucursal inv
                JOIN productos p ON p.id=inv.producto_id
                JOIN sucursales s ON s.id=inv.sucursal_id
                LEFT JOIN categorias c ON c.id=p.id_categoria
                LEFT JOIN producto_lugares_compra plc ON plc.producto_id=p.id AND plc.activo=1
                LEFT JOIN lugarcompra lc ON lc.id=plc.lugar_compra_id
                LEFT JOIN (
                    SELECT dv.producto_id, v.sucursal_id,
                        SUM(dv.cantidad) as cantidad_30d,
                        SUM(dv.subtotal) as total_30d
                    FROM detalle_ventas dv
                    JOIN ventas v ON v.id=dv.venta_id
                    WHERE v.fecha_y_hora >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                    GROUP BY dv.producto_id, v.sucursal_id
                ) vs ON vs.producto_id=inv.producto_id AND vs.sucursal_id=inv.sucursal_id
                WHERE inv.stock_total >= 0 $where_suc
                HAVING cantidad_30d > 0 OR inv.stock_total <= inv.stock_minimo
                ORDER BY (inv.stock_total / NULLIF(vs.cantidad_30d/30, 0)) ASC
                LIMIT 300";

            $rows = $pdo->query($q)->fetchAll();
            $alertas = [];

            foreach ($rows as $r) {
                $stock = intval($r['stock_total']);
                $minimo = max(1, intval($r['stock_minimo']));
                $velocidad = intval($r['cantidad_30d']) / 30.0;
                $lead = max(1, intval($r['lead_time'] ?: 7));

                if ($velocidad <= 0) continue;

                $dias_restantes = floor($stock / $velocidad);
                $reorden = ceil($velocidad * $lead * 1.5);
                $cantidad_sugerida = max($minimo, $reorden) + ceil($velocidad * $lead * 2) - $stock;
                if ($cantidad_sugerida <= 0) continue;

                // Get best supplier for this product
                $proveedor = $r['proveedor'] ?: 'Varios';
                $costo = floatval($r['costo_compra'] ?? 0);
                $inversion = $cantidad_sugerida * $costo;

                $urgencia = $dias_restantes <= $lead ? 'urgente' : ($dias_restantes <= $lead * 2 ? 'programar' : 'optimo');

                $alertas[] = [
                    'producto_id' => $r['producto_id'],
                    'producto' => $r['producto'],
                    'codigo' => $r['codigo'],
                    'categoria' => $r['categoria'],
                    'sucursal' => $r['sucursal'],
                    'stock_actual' => $stock,
                    'stock_minimo' => $minimo,
                    'velocidad_diaria' => round($velocidad, 2),
                    'dias_restantes' => $dias_restantes,
                    'lead_time' => $lead,
                    'punto_reorden' => $reorden,
                    'cantidad_sugerida' => max(1, $cantidad_sugerida),
                    'proveedor' => $proveedor,
                    'costo_unitario' => $costo,
                    'inversion_estimada' => round($inversion, 2),
                    'urgencia' => $urgencia
                ];
            }

            usort($alertas, fn($a, $b) =>
                ($a['dias_restantes'] <=> $b['dias_restantes'])
            );

            $stats = [
                'urgentes' => count(array_filter($alertas, fn($a) => $a['urgencia']==='urgente')),
                'programar' => count(array_filter($alertas, fn($a) => $a['urgencia']==='programar')),
                'optimo' => count(array_filter($alertas, fn($a) => $a['urgencia']==='optimo')),
                'inversion_total' => round(array_sum(array_column($alertas, 'inversion_estimada')), 2)
            ];

            json_success(['alertas'=>$alertas, 'stats'=>$stats]);
            break;

        default:
            json_error('Accion no valida');
    }
} catch (Exception $e) {
    json_error('Error: ' . $e->getMessage());
}
