<?php
namespace App\Models;

use CodeIgniter\Model;

class SaleModel extends Model
{
    protected $table      = 'ventas';
    protected $primaryKey = 'id_venta';
    protected $allowedFields = ['fecha', 'id_usuario', 'total', 'observaciones'];
    protected $returnType    = 'array';
    protected $useTimestamps = false;

    public function getSalesWithDetails($onlyToday = false)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('ventas v');
        $builder->select("v.*, u.username, COUNT(vd.id_detalle) as total_items,
                            GROUP_CONCAT(p.nombre SEPARATOR ', ') as productos")
                  ->join('usuarios u', 'u.id_usuario = v.id_usuario')
                  ->join('venta_detalles vd', 'vd.id_venta = v.id_venta', 'left')
                  ->join('productos p', 'p.id_producto = vd.id_producto', 'left');
        if ($onlyToday) {
            $builder->where('DATE(v.fecha) = CURDATE()');
        }
        return $builder->groupBy('v.id_venta')
                       ->orderBy('v.id_venta', 'DESC')
                       ->get()
                       ->getResultArray();
    }

    public function getSaleWithDetails($id)
    {
        $db = \Config\Database::connect();
        $sale = $db->table('ventas v')
                   ->select('v.*, u.username')
                   ->join('usuarios u', 'u.id_usuario = v.id_usuario')
                   ->where('v.id_venta', $id)
                   ->get()
                   ->getRowArray();
        if ($sale) {
            $sale['details'] = $db->table('venta_detalles vd')
                                  ->select('vd.*, p.nombre as producto_nombre, p.codigo_unico')
                                  ->join('productos p', 'p.id_producto = vd.id_producto')
                                  ->where('vd.id_venta', $id)
                                  ->get()
                                  ->getResultArray();
            $sale['payments'] = $db->table('venta_pagos vp')
                                   ->select('vp.*, fp.nombre as forma_pago_nombre')
                                   ->join('formas_pago fp', 'fp.id_forma_pago = vp.id_forma_pago')
                                   ->where('vp.id_venta', $id)
                                   ->get()
                                   ->getResultArray();
        }
        return $sale;
    }

    public function getTotalSalesToday()
    {
        $db = \Config\Database::connect();
        $result = $db->table('ventas')
                     ->select('COALESCE(SUM(total), 0) as total')
                     ->where('DATE(fecha) = CURDATE()')
                     ->get()
                     ->getRowArray();
        return $result['total'];
    }

    public function getRecentSales($limit = 5)
    {
        $db = \Config\Database::connect();
        return $db->table('ventas v')
                  ->select('v.*, u.username')
                  ->join('usuarios u', 'u.id_usuario = v.id_usuario')
                  ->orderBy('v.id_venta', 'DESC')
                  ->limit($limit)
                  ->get()
                  ->getResultArray();
    }

    public function getSalesByUser($userId, $limit = 5)
    {
        $db = \Config\Database::connect();
        return $db->table('ventas')
                  ->where('id_usuario', $userId)
                  ->orderBy('id_venta', 'DESC')
                  ->limit($limit)
                  ->get()
                  ->getResultArray();
    }

    public function getSalesByUserToday($userId)
    {
        $db = \Config\Database::connect();
        $result = $db->table('ventas')
                     ->select('COALESCE(COUNT(*), 0) as count, COALESCE(SUM(total), 0) as total')
                     ->where('id_usuario', $userId)
                     ->where('DATE(fecha) = CURDATE()')
                     ->get()
                     ->getRowArray();
        return $result;
    }

    public function getDeletionHistory($onlyToday = false)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('stock_movimientos sm')
                  ->select("sm.id_movimiento, sm.fecha, sm.cantidad, sm.observacion,
                            u.username, p.nombre as producto_nombre, p.codigo_unico")
                  ->join('usuarios u', 'u.id_usuario = sm.id_usuario')
                  ->join('productos p', 'p.id_producto = sm.id_producto')
                  ->groupStart()
                      ->like('sm.observacion', 'Devolucion por eliminacion de venta')
                      ->orLike('sm.observacion', '"type":"delete_backup"')
                  ->groupEnd();
        if ($onlyToday) {
            $builder->where('DATE(sm.fecha) = CURDATE()');
        }
        return $builder->orderBy('sm.fecha', 'DESC')
                       ->limit(50)
                       ->get()
                       ->getResultArray();
    }

    public function getDeletionBackups($onlyToday = false)
    {
        $all = $this->getDeletionHistory($onlyToday);
        $backups = [];
        foreach ($all as $row) {
            if (strpos($row['observacion'], '"type":"delete_backup"') !== false) {
                $row['backup_data'] = json_decode($row['observacion'], true);
                $backups[] = $row;
            }
        }
        return $backups;
    }

    public function getSalesLast7Days()
    {
        $db = \Config\Database::connect();
        $result = $db->query("
            SELECT DATE(fecha) as dia, COALESCE(SUM(total), 0) as total
            FROM ventas
            WHERE fecha >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
            GROUP BY DATE(fecha)
            ORDER BY dia ASC
        ")->getResultArray();

        $days = [];
        $totals = [];
        $diasES = ['Sun' => 'Dom','Mon' => 'Lun','Tue' => 'Mar','Wed' => 'Mie','Thu' => 'Jue','Fri' => 'Vie','Sat' => 'Sab'];
        for ($i = 6; $i >= 0; $i--) {
            $date = date('Y-m-d', strtotime("-$i days"));
            $dayName = $diasES[date('D', strtotime("-$i days"))] ?? date('D', strtotime("-$i days"));
            $found = false;
            foreach ($result as $row) {
                if ($row['dia'] == $date) {
                    $days[] = $dayName;
                    $totals[] = (float)$row['total'];
                    $found = true;
                    break;
                }
            }
            if (!$found) {
                $days[] = $dayName;
                $totals[] = 0;
            }
        }
        return ['labels' => $days, 'data' => $totals];
    }

    public function getSalesByCategory()
    {
        $db = \Config\Database::connect();
        return $db->query("
            SELECT c.nombre as label, SUM(vd.cantidad * vd.precio_unitario) as value
            FROM venta_detalles vd
            JOIN productos p ON p.id_producto = vd.id_producto
            JOIN categorias c ON c.id_categoria = p.id_categoria
            GROUP BY c.id_categoria, c.nombre
            ORDER BY value DESC
        ")->getResultArray();
    }

    public function getSalesByPaymentMethod()
    {
        $db = \Config\Database::connect();
        return $db->query("
            SELECT fp.nombre as label, SUM(vp.monto) as value
            FROM venta_pagos vp
            JOIN formas_pago fp ON fp.id_forma_pago = vp.id_forma_pago
            GROUP BY fp.id_forma_pago, fp.nombre
            ORDER BY value DESC
        ")->getResultArray();
    }

    public function getUserSalesLast7Days($userId)
    {
        $db = \Config\Database::connect();
        $result = $db->query("
            SELECT DATE(fecha) as dia, COALESCE(SUM(total), 0) as total
            FROM ventas
            WHERE id_usuario = ? AND fecha >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
            GROUP BY DATE(fecha)
            ORDER BY dia ASC
        ", [$userId])->getResultArray();

        $days = [];
        $totals = [];
        $diasES = ['Sun' => 'Dom','Mon' => 'Lun','Tue' => 'Mar','Wed' => 'Mie','Thu' => 'Jue','Fri' => 'Vie','Sat' => 'Sab'];
        for ($i = 6; $i >= 0; $i--) {
            $date = date('Y-m-d', strtotime("-$i days"));
            $dayName = $diasES[date('D', strtotime("-$i days"))] ?? date('D', strtotime("-$i days"));
            $found = false;
            foreach ($result as $row) {
                if ($row['dia'] == $date) {
                    $days[] = $dayName;
                    $totals[] = (float)$row['total'];
                    $found = true;
                    break;
                }
            }
            if (!$found) {
                $days[] = $dayName;
                $totals[] = 0;
            }
        }
        return ['labels' => $days, 'data' => $totals];
    }

    public function getTotalVentas()
    {
        $db = \Config\Database::connect();
        $result = $db->table('ventas')
                     ->select('COALESCE(SUM(total), 0) as total')
                     ->get()
                     ->getRowArray();
        return $result['total'];
    }

    public function getTotalGanancias()
    {
        $db = \Config\Database::connect();
        $result = $db->table('venta_detalles')
                     ->select('COALESCE(SUM(cantidad * (precio_unitario - costo_unitario)), 0) as total')
                     ->get()
                     ->getRowArray();
        return $result['total'];
    }
}