<?php

namespace App\Modules\HR\Reports\Repositories;

use App\Modules\HR\Reports\Support\HrReportKey;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class WorkforceReportRepository
{
    /** @return Collection<int, object> */
    public function departments(int $tenantId): Collection
    {
        return DB::table('departments')->where('tenant_id', $tenantId)
            ->where('is_active', true)->orderBy('name')->get(['id', 'name']);
    }

    /** @param array<string, mixed> $filters */
    public function paginate(
        int $tenantId,
        HrReportKey $report,
        array $filters,
    ): LengthAwarePaginator {
        $query = match ($report) {
            HrReportKey::EMPLOYEE_SKILLS_MATRIX => $this->skills($tenantId),
            HrReportKey::PERFORMANCE_KPI_SUMMARY => $this->performance($tenantId),
            default => $this->employees($tenantId, $report),
        };

        $this->filter($query, $filters, $report);

        return $query->paginate((int) ($filters['per_page'] ?? 25))->withQueryString();
    }

    private function employees(int $tenantId, HrReportKey $report): Builder
    {
        $query = DB::table('employees as e')
            ->leftJoin('departments as d', 'd.id', '=', 'e.department_id')
            ->where('e.tenant_id', $tenantId);

        return match ($report) {
            HrReportKey::EMPLOYEE_HEADCOUNT => $query
                ->selectRaw('d.name as department, COUNT(e.id) as headcount')
                ->groupBy('d.id', 'd.name')->orderBy('d.name'),
            HrReportKey::EMPLOYEE_TURNOVER => $query
                ->select('e.employee_code', 'e.full_name', 'd.name as department', 'e.hire_date', 'e.updated_at as inactive_at')
                ->where('e.is_active', false)->latest('e.updated_at'),
            HrReportKey::EMPLOYEE_DEMOGRAPHICS => $query
                ->select('e.gender', 'e.nationality', 'e.marital_status')
                ->selectRaw('COUNT(e.id) as employees')
                ->groupBy('e.gender', 'e.nationality', 'e.marital_status'),
            default => $query
                ->leftJoin('positions as p', 'p.id', '=', 'e.position_id')
                ->select('e.employee_code', 'e.full_name', 'e.email', 'e.phone_1', 'd.name as department', 'p.title as position', 'e.hire_date', 'e.is_active')
                ->orderBy('e.full_name'),
        };
    }

    private function skills(int $tenantId): Builder
    {
        return DB::table('employee_skills as s')
            ->join('employees as e', 'e.id', '=', 's.employee_id')
            ->leftJoin('departments as d', 'd.id', '=', 'e.department_id')
            ->where('s.tenant_id', $tenantId)
            ->select('e.employee_code', 'e.full_name', 'd.name as department', 's.name as skill', 's.level', 's.years_experience', 's.is_certified')
            ->orderBy('e.full_name');
    }

    private function performance(int $tenantId): Builder
    {
        return DB::table('hr_performance_reviews as r')
            ->join('employees as e', 'e.id', '=', 'r.employee_id')
            ->leftJoin('departments as d', 'd.id', '=', 'e.department_id')
            ->leftJoin('hr_performance_cycles as c', 'c.id', '=', 'r.cycle_id')
            ->where('r.tenant_id', $tenantId)
            ->select('e.employee_code', 'e.full_name', 'd.name as department', 'c.name as cycle', 'r.status', 'r.final_score', 'r.reviewed_at')
            ->latest('r.reviewed_at');
    }

    /** @param array<string, mixed> $filters */
    private function filter(Builder $query, array $filters, HrReportKey $report): void
    {
        if (! empty($filters['department_id'])) {
            $query->where('e.department_id', $filters['department_id']);
        }
        $date = match ($report) {
            HrReportKey::PERFORMANCE_KPI_SUMMARY => 'r.reviewed_at',
            default => 'e.hire_date',
        };
        if (! empty($filters['date_from'])) {
            $query->whereDate($date, '>=', $filters['date_from']);
        }
        if (! empty($filters['date_to'])) {
            $query->whereDate($date, '<=', $filters['date_to']);
        }
    }
}
