<?php
require_once '../DBconnection.php';

class DashboardModel extends DBconnection
{
    public function getStats()
    {
        $stats = [];
        $stats['totalCategorias'] = $this->getCount('categorias');
        $stats['totalMarcas'] = $this->getCount('marcas');
        $stats['totalProductos'] = $this->getCount('productos');
        $stats['totalUsuarios'] = $this->getCount('usuario');
        $stats['totalDestacados'] = $this->getCountDestacados();
        $stats['productosMasCaros'] = $this->getProductosMasCaros();
        echo json_encode($stats);
    }

    public function getProductosByCategoria()
    {
        $query = "SELECT 
                    c.nombre AS categoria, 
                    COUNT(p.id) AS total,
                    SUM(p.precio) AS valor_total
                  FROM productos p
                  JOIN categorias c ON p.id_categoria = c.id
                  WHERE p.status = 1
                  GROUP BY c.nombre
                  ORDER BY total DESC";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $labels = array_column($result, 'categoria');
        $values = array_column($result, 'total');
        $valores = array_column($result, 'valor_total');
        
        // Generar colores dinámicos
        $colors = $this->generateColors(count($labels));
        
        echo json_encode([
            'labels' => $labels, 
            'values' => $values, 
            'valores' => $valores,
            'colors' => $colors
        ]);
    }

    public function getProductosByMarca()
    {
        $query = "SELECT 
                    m.nombre AS marca, 
                    COUNT(p.id) AS total,
                    SUM(p.precio) AS valor_total
                  FROM productos p
                  JOIN marcas m ON p.id_marca = m.id
                  WHERE p.status = 1
                  GROUP BY m.nombre
                  ORDER BY total DESC";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $labels = array_column($result, 'marca');
        $values = array_column($result, 'total');
        $valores = array_column($result, 'valor_total');
        
        // Generar colores dinámicos
        $colors = $this->generateColors(count($labels));
        
        echo json_encode([
            'labels' => $labels, 
            'values' => $values, 
            'valores' => $valores,
            'colors' => $colors
        ]);
    }

    public function getProductosDestacados()
    {
        $query = "SELECT 
                    CASE WHEN destacado = 1 THEN 'Destacados' ELSE 'No Destacados' END AS tipo,
                    COUNT(id) AS total 
                  FROM productos 
                  WHERE status = 1
                  GROUP BY destacado";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $labels = array_column($result, 'tipo');
        $values = array_column($result, 'total');
        echo json_encode(['labels' => $labels, 'values' => $values]);
    }

    public function getProductosPorMes()
    {
        // Obtener productos creados por mes (últimos 12 meses)
        // Para fines de demostración, usamos la fecha actual para simular
        $query = "SELECT 
                    MONTH(DATE_SUB(NOW(), INTERVAL RAND()*365 DAY)) as mes,
                    YEAR(DATE_SUB(NOW(), INTERVAL RAND()*365 DAY)) as anio,
                    COUNT(id) AS total,
                    SUM(precio) as valor_total
                  FROM productos 
                  WHERE status = 1
                  GROUP BY mes, anio
                  ORDER BY anio ASC, mes ASC
                  LIMIT 12";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Convertir números de mes a nombres
        $meses = [
            1 => 'Enero', 2 => 'Febrero', 3 => 'Marzo', 4 => 'Abril',
            5 => 'Mayo', 6 => 'Junio', 7 => 'Julio', 8 => 'Agosto',
            9 => 'Septiembre', 10 => 'Octubre', 11 => 'Noviembre', 12 => 'Diciembre'
        ];

        $labels = [];
        $values = [];
        $valores = [];

        foreach ($result as $row) {
            $labels[] = $meses[$row['mes']] . ' ' . $row['anio'];
            $values[] = $row['total'];
            $valores[] = $row['valor_total'];
        }

        echo json_encode([
            'labels' => $labels, 
            'values' => $values,
            'valores' => $valores
        ]);
    }

    public function getRangosPrecios()
    {
        // Obtener distribución de productos por rangos de precio
        $query = "SELECT 
                    CASE 
                        WHEN precio BETWEEN 0 AND 100 THEN 'Hasta $100'
                        WHEN precio BETWEEN 101 AND 500 THEN '$101 - $500'
                        WHEN precio BETWEEN 501 AND 1000 THEN '$501 - $1000'
                        WHEN precio BETWEEN 1001 AND 5000 THEN '$1001 - $5000'
                        ELSE 'Más de $5000'
                    END AS rango,
                    COUNT(id) AS total 
                  FROM productos 
                  WHERE status = 1
                  GROUP BY rango
                  ORDER BY MIN(precio) ASC";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $labels = array_column($result, 'rango');
        $values = array_column($result, 'total');
        
        // Generar colores
        $colors = [
            'rgba(54, 162, 235, 0.7)',
            'rgba(75, 192, 192, 0.7)',
            'rgba(255, 206, 86, 0.7)',
            'rgba(255, 99, 132, 0.7)',
            'rgba(153, 102, 255, 0.7)'
        ];
        
        echo json_encode([
            'labels' => $labels, 
            'values' => $values,
            'colors' => $colors
        ]);
    }

    public function getLastProductos()
    {
        $query = "SELECT 
                    p.id, 
                    p.nombre, 
                    p.precio,
                    p.sku, 
                    c.nombre AS categoria, 
                    m.nombre AS marca,
                    p.destacado,
                    (SELECT ip.imagen FROM imagen_producto ip
                     WHERE ip.id_producto = p.id
                     ORDER BY (ip.principal=1) DESC, ip.id LIMIT 0,1) as imagen
                  FROM productos p
                  JOIN categorias c ON p.id_categoria = c.id
                  JOIN marcas m ON p.id_marca = m.id
                  WHERE p.status = 1
                  ORDER BY p.id DESC LIMIT 10";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode($result);
    }

    public function getTopProductos()
    {
        // En un sistema real, esto podría basarse en ventas o visitas
        // Aquí lo basamos en el precio como ejemplo
        $query = "SELECT 
                    p.id,
                    p.nombre,
                    p.precio,
                    c.nombre as categoria,
                    m.nombre as marca,
                    (SELECT ip.imagen FROM imagen_producto ip
                     WHERE ip.id_producto = p.id
                     ORDER BY (ip.principal=1) DESC, ip.id LIMIT 0,1) as imagen
                  FROM productos p
                  JOIN categorias c ON p.id_categoria = c.id
                  JOIN marcas m ON p.id_marca = m.id
                  WHERE p.status = 1
                  ORDER BY p.precio DESC
                  LIMIT 5";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode($result);
    }

    private function getCount($table)
    {
        $query = "SELECT COUNT(*) AS total FROM $table WHERE status = 1";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'];
    }

    private function getCountDestacados()
    {
        $query = "SELECT COUNT(*) AS total FROM productos WHERE status = 1 AND destacado = 1";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'];
    }

    private function getProductosMasCaros()
    {
        $query = "SELECT COUNT(*) AS total FROM productos WHERE status = 1 AND precio > 1000";
        $stmt = $this->connect()->prepare($query);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'];
    }

    private function generateColors($count)
    {
        $colors = [];
        $hueStep = 360 / $count;
        
        for ($i = 0; $i < $count; $i++) {
            $hue = $i * $hueStep;
            $saturation = 70; // valor entre 0-100
            $lightness = 60; // valor entre 0-100
            
            // Convertir HSL a RGB en formato hexadecimal
            $colors[] = $this->hslToHex($hue, $saturation, $lightness);
        }
        
        return $colors;
    }

    private function hslToHex($h, $s, $l)
    {
        $h /= 360;
        $s /= 100;
        $l /= 100;

        $r = $g = $b = $l;

        $v = ($l <= 0.5) ? ($l * (1.0 + $s)) : ($l + $s - $l * $s);
        if ($v > 0) {
            $m = $l + $l - $v;
            $sv = ($v - $m) / $v;
            $h *= 6.0;
            $sextant = floor($h);
            $fract = $h - $sextant;
            $vsf = $v * $sv * $fract;
            $mid1 = $m + $vsf;
            $mid2 = $v - $vsf;

            switch ($sextant) {
                case 0:
                    $r = $v;
                    $g = $mid1;
                    $b = $m;
                    break;
                case 1:
                    $r = $mid2;
                    $g = $v;
                    $b = $m;
                    break;
                case 2:
                    $r = $m;
                    $g = $v;
                    $b = $mid1;
                    break;
                case 3:
                    $r = $m;
                    $g = $mid2;
                    $b = $v;
                    break;
                case 4:
                    $r = $mid1;
                    $g = $m;
                    $b = $v;
                    break;
                case 5:
                    $r = $v;
                    $g = $m;
                    $b = $mid2;
                    break;
            }
        }

        $r = round($r * 255);
        $g = round($g * 255);
        $b = round($b * 255);

        return "rgba($r, $g, $b, 0.7)";
    }
}

if (isset($_POST['action'])) {
    $dashboard = new DashboardModel();
    switch ($_POST['action']) {
        case 'getStats':
            $dashboard->getStats();
            break;
        case 'getProductosByCategoria':
            $dashboard->getProductosByCategoria();
            break;
        case 'getProductosByMarca':
            $dashboard->getProductosByMarca();
            break;
        case 'getProductosDestacados':
            $dashboard->getProductosDestacados();
            break;
        case 'getProductosPorMes':
            $dashboard->getProductosPorMes();
            break;
        case 'getRangosPrecios':
            $dashboard->getRangosPrecios();
            break;
        case 'getLastProductos':
            $dashboard->getLastProductos();
            break;
        case 'getTopProductos':
            $dashboard->getTopProductos();
            break;
    }
}