<?php
declare(strict_types=1);

namespace App\Modules\HR\Models;

use App\Core\Model;
use App\Core\App;

class HrDepartmentPosition extends Model
{
    protected static string $table = 'hr_department_positions';
    protected static string $primaryKey = 'id';
    protected static bool $timestamps = true;
    protected static bool $softDelete = false;
    protected static bool $autoTrackAuthor = false;

    protected static array $fillable = [
        'department_id', 'job_title_id', 'authorized_count',
    ];

    public static function getForDepartment(int $departmentId): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT dp.*, jt.name_ar as job_title_name
             FROM hr_department_positions dp
             JOIN hr_job_titles jt ON jt.id = dp.job_title_id
             WHERE dp.department_id = ?
             ORDER BY jt.name_ar ASC",
            [$departmentId]
        );
    }

    public static function getStaffingReport(int $departmentId): array
    {
        $db = App::getInstance()->db();
        $positions = $db->select(
            "SELECT dp.id, dp.job_title_id, dp.authorized_count, jt.name_ar as job_title_name,
                    (SELECT COUNT(*) FROM hr_employee_profiles ep
                     WHERE ep.department_id = dp.department_id
                       AND ep.job_title_id = dp.job_title_id
                       AND ep.employment_status = 'active'
                       AND ep.is_archived = 0) as actual_count
             FROM hr_department_positions dp
             JOIN hr_job_titles jt ON jt.id = dp.job_title_id
             WHERE dp.department_id = ?
             ORDER BY jt.name_ar ASC",
            [$departmentId]
        );

        return $positions;
    }
}
