<?php

namespace Modules\Dashboard\Models;

use core\Model;
use core\Database;

class Dashboard extends Model
{
    protected string $table = '';

    public function getTodayRevenue(): float
    {
        $db = Database::getInstance();
        $query = $db->table('invoices')
            ->where('DATE(created_at)', date('Y-m-d'))
            ->whereIn('payment_status', ['paid', 'partial']);
        if ($branchId = \core\Auth::branchId()) {
            $query->where('branch_id', $branchId);
        }
        return $query->sum('grand_total');
    }

    public function getTodayAppointments(): int
    {
        $db = Database::getInstance();
        $query = $db->table('appointments')
            ->where('DATE(start_time)', date('Y-m-d'));
        if ($branchId = \core\Auth::branchId()) {
            $query->where('branch_id', $branchId);
        }
        return $query->count();
    }

    public function getActiveCustomersCount(): int
    {
        $db = Database::getInstance();
        $query = $db->table('customers')
            ->where('status', 'active');
        if ($branchId = \core\Auth::branchId()) {
            $query->where('branch_id', $branchId);
        }
        return $query->count();
    }

    public function getLowStockCount(): int
    {
        $db = Database::getInstance();
        $query = $db->table('products')
            ->whereRaw('current_stock <= reorder_level');
        if ($branchId = \core\Auth::branchId()) {
            $query->where('branch_id', $branchId);
        }
        return $query->count();
    }

    public function getRevenueLast7Days(): array
    {
        $db = Database::getInstance();
        $result = [];
        for ($i = 6; $i >= 0; $i--) {
            $date = date('Y-m-d', strtotime("-$i days"));
            $query = $db->table('invoices')
                ->where('DATE(created_at)', $date)
                ->whereIn('payment_status', ['paid', 'partial']);
            if ($branchId = \core\Auth::branchId()) {
                $query->where('branch_id', $branchId);
            }
            $result[] = [
                'date' => $date,
                'total' => $query->sum('grand_total'),
            ];
        }
        return $result;
    }

    public function getTopServices(int $limit = 5): array
    {
        $db = Database::getInstance();
        $query = "SELECT s.name, COUNT(asv.id) as total_count, COALESCE(SUM(asv.price), 0) as total_revenue
                  FROM appointment_services asv
                  INNER JOIN services s ON asv.service_id = s.id
                  INNER JOIN appointments a ON asv.appointment_id = a.id
                  WHERE a.deleted_at IS NULL AND a.status = 'completed'";
        $bindings = [];
        if ($branchId = \core\Auth::branchId()) {
            $query .= " AND a.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $query .= " GROUP BY s.id, s.name ORDER BY total_revenue DESC LIMIT " . (int)$limit;
        return $db->query($query, $bindings);
    }

    public function getTopEmployees(int $limit = 5): array
    {
        $db = Database::getInstance();
        $query = "SELECT e.id, e.first_name, e.last_name, 
                  COUNT(DISTINCT a.id) as total_appointments, COALESCE(SUM(asv.price), 0) as total_revenue
                  FROM appointments a
                  INNER JOIN appointment_services asv ON a.id = asv.appointment_id
                  INNER JOIN employees e ON asv.employee_id = e.id
                  WHERE a.deleted_at IS NULL AND a.status = 'completed'";
        $bindings = [];
        if ($branchId = \core\Auth::branchId()) {
            $query .= " AND a.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $query .= " GROUP BY e.id, e.first_name, e.last_name ORDER BY total_revenue DESC LIMIT " . (int)$limit;
        return $db->query($query, $bindings);
    }
}
