<?php

namespace App\Modules\HR\Reports\Data;

use App\Modules\HR\Attendance\Models\AttendanceDetail;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class TimesheetSummaryReport
{
    public function getData($filters)
    {
        $from = Carbon::parse($filters['from_date'])->startOfDay();
        $to   = Carbon::parse($filters['to_date'])->endOfDay();

        $query = AttendanceDetail::query()
            ->join('attendance', 'attendance.id', '=', 'attendance_details.attendance_id')
            ->join('employees', 'employees.id', '=', 'attendance_details.employee_id')
            ->join('departments', 'departments.id', '=', 'employees.department_id')
            ->join('designations', 'designations.id', '=', 'employees.designation_id');

        // Date filter
        $query->whereBetween('attendance.attendance_date', [$from, $to]);

        // Branch filter
        if (!empty($filters['branch_id'])) {
            $query->where('employees.branch_id', $filters['branch_id']);
        }

        // Department filter
        if (!empty($filters['department_id'])) {
            $query->where('employees.department_id', $filters['department_id']);
        }

        // Employee filter
        if (!empty($filters['employee_id'])) {
            $query->where('employees.id', $filters['employee_id']);
        }

        $records = $query->select(
                'attendance.attendance_date',
                'employees.employee_code',
                DB::raw("CONCAT(employees.first_name,' ',employees.last_name) as employee_name"),
                'departments.name as department_name',
                'designations.name as designation_name',

                DB::raw("SUM(CASE WHEN attendance_status = 'Present' THEN 1 ELSE 0 END) as present"),
                DB::raw("SUM(CASE WHEN attendance_status = 'Absent' THEN 1 ELSE 0 END) as absent"),
                DB::raw("SUM(CASE WHEN attendance_status = 'Leave' THEN 1 ELSE 0 END) as leave_count"),
                DB::raw("SUM(CASE WHEN attendance_status = 'Day Off' THEN 1 ELSE 0 END) as day_off"),

                DB::raw("SUM(ot_minutes) / 60 as single_ot"),
                DB::raw("SUM(dot_minutes) / 60 as double_ot"),

                DB::raw("SUM(late_minutes) as late_minutes")
            )
            ->groupBy(
                'attendance.attendance_date',
                'employees.employee_code',
                'employees.first_name',
                'employees.last_name',
                'departments.name',
                'designations.name'
            )
            ->orderBy('attendance.attendance_date')
            ->get();

        return [
            'records' => $records,
            'from' => $from,
            'to' => $to
        ];
    }
}