<?php

namespace App\Modules\HR\Analytics\Repositories;

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class HrAnalyticsRepository
{
    public function employees(int $tenantId): Collection
    {
        return DB::table('employees as e')
            ->leftJoin('departments as d', 'd.id', '=', 'e.department_id')
            ->where('e.tenant_id', $tenantId)
            ->get(['e.hire_date', 'e.updated_at', 'e.is_active', 'e.gender', 'd.name as department']);
    }

    public function payroll(int $tenantId, string $from): Collection
    {
        return DB::table('payroll_employee_snapshots as s')
            ->join('payroll_periods as p', 'p.id', '=', 's.payroll_period_id')
            ->where('s.tenant_id', $tenantId)->whereDate('p.start_date', '>=', $from)
            ->get(['p.start_date as date', 's.gross_amount as value']);
    }

    public function overtime(int $tenantId, string $from): Collection
    {
        return DB::table('overtime_requests')->where('tenant_id', $tenantId)
            ->where('status', 'approved')->whereDate('attendance_date', '>=', $from)
            ->get(['attendance_date as date', 'overtime_minutes as value']);
    }

    public function leave(int $tenantId, string $from): Collection
    {
        return DB::table('leave_requests')->where('tenant_id', $tenantId)
            ->whereIn('status', ['approved', 'hr_approved'])->whereDate('start_date', '>=', $from)
            ->get(['start_date as date', 'total_days as value']);
    }

    public function absences(int $tenantId, string $from): Collection
    {
        return DB::table('attendance_records')->where('tenant_id', $tenantId)
            ->where('status', 'absent')->whereDate('attendance_date', '>=', $from)
            ->get(['attendance_date as date']);
    }

    public function applications(int $tenantId, string $from): Collection
    {
        return DB::table('recruitment_applications')->where('tenant_id', $tenantId)
            ->whereDate('applied_at', '>=', $from)
            ->get(['status', 'applied_at', 'hired_at']);
    }
}
