<?php

namespace Modules\Hrm\Services;

use App\Models\AttendanceCorrectionModel;
use App\Models\DepartmentModel;
use App\Models\EmployeeAssetAssignmentModel;
use App\Models\EmployeeModel;
use App\Models\HolidayModel;
use App\Models\LeaveRequestModel;
use App\Models\PayrollItemModel;
use App\Models\PayrollRunModel;
use App\Models\PayrollSettlementModel;
use Config\Database;
use Modules\Attendance\Services\AttendanceService;
use Modules\Documents\Services\DocumentExpiryService;

class HrmDashboardService
{
    protected EmployeeModel $employees;
    protected DepartmentModel $departments;
    protected LeaveRequestModel $leaveRequests;
    protected AttendanceCorrectionModel $corrections;
    protected PayrollRunModel $payrollRuns;
    protected PayrollItemModel $payrollItems;
    protected PayrollSettlementModel $settlements;
    protected EmployeeAssetAssignmentModel $assetAssignments;
    protected DocumentExpiryService $documentExpiry;
    protected HolidayModel $holidays;
    protected AttendanceService $attendance;

    public function __construct()
    {
        $this->employees        = new EmployeeModel();
        $this->departments      = new DepartmentModel();
        $this->leaveRequests    = new LeaveRequestModel();
        $this->corrections      = new AttendanceCorrectionModel();
        $this->payrollRuns      = new PayrollRunModel();
        $this->payrollItems     = new PayrollItemModel();
        $this->settlements      = new PayrollSettlementModel();
        $this->assetAssignments = new EmployeeAssetAssignmentModel();
        $this->documentExpiry   = new DocumentExpiryService();
        $this->holidays         = new HolidayModel();
        $this->attendance       = new AttendanceService();
    }

    public function overview(): array
    {
        $today     = date('Y-m-d');
        $monthStart = date('Y-m-01');
        $year      = (int) date('Y');
        $month     = (int) date('n');

        return [
            'employees'   => $this->employeeStats($monthStart),
            'attendance'  => $this->attendanceStats($today),
            'leave'       => $this->leaveStats($today),
            'payroll'     => $this->payrollStats($year, $month),
            'assets'      => $this->assetStats(),
            'documents'   => $this->documentExpiry->summary($today),
            'departments' => $this->departments->where('status', 'active')->countAllResults(),
            'holidays'    => $this->upcomingHolidays($today),
            'recentHires' => $this->recentHires(),
            'byDepartment'=> $this->employeesByDepartment(),
        ];
    }

    private function employeeStats(string $monthStart): array
    {
        $db = Database::connect();

        $statusRows = $db->table('employees')
            ->select('status, COUNT(*) as total')
            ->where('deleted_at', null)
            ->groupBy('status')
            ->get()
            ->getResultArray();

        $byStatus = [];
        foreach ($statusRows as $row) {
            $byStatus[$row['status']] = (int) $row['total'];
        }

        $total = array_sum($byStatus);

        $newThisMonth = $this->employees
            ->where('deleted_at', null)
            ->groupStart()
                ->where('hire_date >=', $monthStart)
                ->orWhere('created_at >=', $monthStart . ' 00:00:00')
            ->groupEnd()
            ->countAllResults();

        return [
            'total'          => $total,
            'active'         => $byStatus['active'] ?? 0,
            'inactive'       => $byStatus['inactive'] ?? 0,
            'terminated'     => $byStatus['terminated'] ?? 0,
            'on_leave'       => $byStatus['on_leave'] ?? 0,
            'new_this_month' => $newThisMonth,
        ];
    }

    private function attendanceStats(string $today): array
    {
        $stats = $this->attendance->getTodayStats($today);
        $stats['pending_corrections'] = $this->corrections
            ->where('status', 'pending')
            ->countAllResults();

        return $stats;
    }

    private function leaveStats(string $today): array
    {
        $nextWeek = date('Y-m-d', strtotime($today . ' +7 days'));

        $pending = $this->leaveRequests
            ->select('leave_requests.*, e.full_name, e.employee_number, lt.name as leave_type_name')
            ->join('employees e', 'e.id = leave_requests.employee_id')
            ->join('leave_types lt', 'lt.id = leave_requests.leave_type_id', 'left')
            ->where('leave_requests.status', 'pending')
            ->where('e.deleted_at', null)
            ->orderBy('leave_requests.created_at', 'DESC')
            ->limit(5)
            ->findAll();

        $upcoming = $this->leaveRequests
            ->select('leave_requests.*, e.full_name, e.employee_number, lt.name as leave_type_name')
            ->join('employees e', 'e.id = leave_requests.employee_id')
            ->join('leave_types lt', 'lt.id = leave_requests.leave_type_id', 'left')
            ->where('leave_requests.status', 'approved')
            ->where('leave_requests.start_date >=', $today)
            ->where('leave_requests.start_date <=', $nextWeek)
            ->where('e.deleted_at', null)
            ->orderBy('leave_requests.start_date', 'ASC')
            ->limit(5)
            ->findAll();

        return [
            'pending_count'  => (new LeaveRequestModel())->where('status', 'pending')->countAllResults(),
            'pending'        => $pending,
            'upcoming'       => $upcoming,
        ];
    }

    private function payrollStats(int $year, int $month): array
    {
        $run    = $this->payrollRuns->findByPeriod($year, $month);
        $totals = $run ? $this->payrollItems->getRunTotals((int) $run['id']) : null;

        $openSettlements = $this->settlements
            ->whereIn('status', ['draft', 'approved'])
            ->countAllResults();

        return [
            'run'              => $run,
            'totals'           => $totals,
            'open_settlements' => $openSettlements,
            'period_label'     => date('F Y', mktime(0, 0, 0, $month, 1, $year)),
        ];
    }

    private function assetStats(): array
    {
        return [
            'assigned' => $this->assetAssignments->where('status', 'assigned')->countAllResults(),
        ];
    }

    private function upcomingHolidays(string $today): array
    {
        $end = date('Y-m-d', strtotime($today . ' +30 days'));

        return $this->holidays->getInRange($today, $end);
    }

    private function recentHires(): array
    {
        return $this->employees
            ->select('employees.*, d.name as department_name')
            ->join('departments d', 'd.id = employees.department_id', 'left')
            ->where('employees.deleted_at', null)
            ->orderBy('employees.hire_date', 'DESC')
            ->orderBy('employees.created_at', 'DESC')
            ->limit(5)
            ->findAll();
    }

    private function employeesByDepartment(): array
    {
        return Database::connect()->query("
            SELECT COALESCE(d.name, 'Unassigned') AS label, COUNT(e.id) AS total
            FROM employees e
            LEFT JOIN departments d ON d.id = e.department_id
            WHERE e.deleted_at IS NULL AND e.status = 'active'
            GROUP BY d.id, d.name
            ORDER BY total DESC
            LIMIT 8
        ")->getResultArray();
    }
}
