<?php
declare(strict_types=1);

namespace App\Modules\HR\Services;

use App\Core\App;

final class WorkforceReportService
{
    public static function getWorkforceStatement(): array
    {
        $db = App::getInstance()->db();

        $departments = $db->select(
            "SELECT d.id, d.name_ar, d.name_en, d.staffing_capacity,
                    COUNT(CASE WHEN ep.workforce_type = 'insured' AND ep.employment_status = 'active' THEN 1 END) AS insured_count,
                    COUNT(CASE WHEN ep.workforce_type = 'part_time' AND ep.employment_status = 'active' THEN 1 END) AS part_time_count,
                    COUNT(CASE WHEN ep.workforce_type NOT IN ('insured', 'part_time') AND ep.employment_status = 'active' THEN 1 END) AS other_count,
                    COUNT(CASE WHEN ep.employment_status = 'active' THEN 1 END) AS total_active
             FROM hr_departments d
             LEFT JOIN hr_employee_profiles ep ON ep.department_id = d.id AND ep.is_archived = 0
             WHERE d.is_archived = 0 AND d.is_active = 1
             GROUP BY d.id, d.name_ar, d.name_en, d.staffing_capacity
             ORDER BY d.sort_order ASC, d.name_ar ASC",
            []
        );

        $totals = [
            'insured_count'    => 0,
            'part_time_count'  => 0,
            'other_count'      => 0,
            'total_active'     => 0,
            'capacity'         => 0,
            'vacancies'        => 0,
        ];

        foreach ($departments as &$dept) {
            $capacity = (int) ($dept['staffing_capacity'] ?? 0);
            $active = (int) $dept['total_active'];
            $dept['vacancies'] = max(0, $capacity - $active);

            $totals['insured_count'] += (int) $dept['insured_count'];
            $totals['part_time_count'] += (int) $dept['part_time_count'];
            $totals['other_count'] += (int) $dept['other_count'];
            $totals['total_active'] += $active;
            $totals['capacity'] += $capacity;
            $totals['vacancies'] += $dept['vacancies'];
        }
        unset($dept);

        return [
            'departments' => $departments,
            'totals'      => $totals,
            'generated_at'=> date('Y-m-d H:i:s'),
        ];
    }
}
