<?php

namespace App\Http\Controllers;

use App\Models\EmployeeLeave;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ReportController extends Controller
{
    public function generateReport(Request $request)
    {
        // dd($request->all());

        switch($request->modules){
            case '1':
                return $this->generateLeaveReport($request);
                break;
            case '2':
                return $this->generateOvertimeReport($request);
                break;
            default:
                return response()->json(['message' => 'You need to select module']);
        }
    }

    public function generateLeaveReport($request) {
        // Validate the request
        DB::enableQueryLog();
    
        $request->validate([
            'columns' => 'required|array',
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'leave_id' => 'nullable|integer',
            'status' => 'nullable|string',
        ]);
    
        // Start the query
        $query = DB::table('employee_leaves')
            ->join('employees', 'employee_leaves.employee_id', '=', 'employees.employee_id')
            ->join('leave_types', 'employee_leaves.leave_id', '=', 'leave_types.id');
    
        // Apply filters
        if ($request->filled('leave_id')) {
            $query->where('employee_leaves.leave_id', $request->leave_id);
        }
    
        if ($request->filled('status')) {
            $query->where('employee_leaves.status', $request->status);
        }
    
        if ($request->filled('from_date') && $request->filled('to_date')) {
            $fromDate = Carbon::parse($request->from_date)->startOfDay();
            $toDate = Carbon::parse($request->to_date)->endOfDay();
    
            $query->whereBetween('employee_leaves.date_from', [$fromDate, $toDate]);
            $query->whereBetween('employee_leaves.date_to', [$fromDate, $toDate]);
        }
    
        // Set the selected columns
        $selectedColumns = $request->columns;
    
        // Prepare transformed columns
        $transformedColumns = [];
    
        // Conditionally include status if 'status' is in $columns
        if (in_array('status', $selectedColumns)) {
            $transformedColumns[] = DB::raw("
                CASE 
                    WHEN employee_leaves.status = 0 THEN 'Pending / For Recommendation' 
                    WHEN employee_leaves.status = 1 THEN 'For Approval' 
                    WHEN employee_leaves.status = 2 THEN 'Approved' 
                    ELSE 'Denied' 
                END as status
            ");
        }
    
        // Handle transformed columns dynamically
        if (in_array('leave_id', $selectedColumns)) {
            $transformedColumns[] = 'leave_types.name as leave_id';
        }
    
        if (in_array('without_pay', $selectedColumns)) {
            $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.without_pay = 0 THEN 'No' ELSE 'Yes' END as without_pay");
        }
    
        if (in_array('half_day', $selectedColumns)) {
            $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.half_day = 0 THEN 'No' ELSE 'Yes' END as half_day");
        }
    
        if (in_array('description', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_leaves.description as description');
        }

        if (in_array('created_at', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_leaves.created_at as created_at');
        }
    
        // Conditionally join employees for recommending_id
        if (in_array('recommending_id', $selectedColumns)) {
            $query->leftJoin('employees as recommending_employee', 'employee_leaves.recommending_id', '=', 'recommending_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id");
        }
    
        // Conditionally join employees for approver_id
        if (in_array('approver_id', $selectedColumns)) {
            $query->leftJoin('employees as approver_employee', 'employee_leaves.approver_id', '=', 'approver_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id");
        }
        
        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name");
        $transformedColumns[] = "employees.employee_id";

        // Remove 'description' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'description';
        });

        // Remove 'created_at' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'created_at';
        });
    
        // Merge selected columns with transformed columns
        $finalColumns = array_merge($selectedColumns, $transformedColumns);
    
        // Select the final columns
        $query->select($finalColumns);
    
        // Fetch the results
        $leaves = $query->get();
    
        // dd(DB::getQueryLog());
    
        // Return the response as JSON
        return response()->json([
            'data' => $leaves,
            'total_hours' => $query->sum('hours'), // This line can also be used if not grouping
            'total_days' => $query->sum('days'), // This line can also be used if not grouping
        ]);
    }
    
    public function generateOvertimeReport($request) {
        // dd($request->all());
        // Validate the request
        DB::enableQueryLog();
    
        $request->validate([
            'columns' => 'required|array',
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'charge_to' => 'nullable|integer',
            'status' => 'nullable|string',
        ]);
    
        // Start the query
        $query = DB::table('employee_overtimes')
            ->join('employees', 'employee_overtimes.employee_id', '=', 'employees.employee_id')
            ->leftjoin('companies', 'employee_overtimes.charge_to', '=', 'companies.id');
    
        // Apply filters
        if ($request->filled('charge_to')) {
            $query->where('employee_overtimes.charge_to', $request->charge_to);
        }
    
        if ($request->filled('status')) {
            $query->where('employee_overtimes.status', $request->status);
        }
    
        if ($request->filled('from_date') && $request->filled('to_date')) {
            $fromDate = Carbon::parse($request->from_date)->startOfDay();
            $toDate = Carbon::parse($request->to_date)->endOfDay();
    
            $query->whereBetween('employee_overtimes.date_from', [$fromDate, $toDate]);
            $query->whereBetween('employee_overtimes.date_to', [$fromDate, $toDate]);
        }
    
        // Set the selected columns
        $selectedColumns = $request->columns;
    
        // Prepare transformed columns
        $transformedColumns = [];
    
        // Conditionally include status if 'status' is in $columns
        if (in_array('status', $selectedColumns)) {
            $transformedColumns[] = DB::raw("
                CASE 
                    WHEN employee_overtimes.status = 0 THEN 'Pending / For Recommendation' 
                    WHEN employee_overtimes.status = 1 THEN 'For Approval' 
                    WHEN employee_overtimes.status = 2 THEN 'Approved' 
                    ELSE 'Denied' 
                END as status
            ");
        }
    
        // Handle transformed columns dynamically
        if (in_array('charge_to', $selectedColumns)) {
            $transformedColumns[] = 'companies.name as charge_to';
        }
    
        if (in_array('meal', $selectedColumns)) {
            $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.meal = 0 THEN 'No' ELSE 'Yes' END as meal");
        }
    
        if (in_array('transportation', $selectedColumns)) {
            $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.transportation = 0 THEN 'No' ELSE 'Yes' END as transportation");
        }
    
        if (in_array('description', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_overtimes.description as description');
        }

        if (in_array('created_at', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_overtimes.created_at as created_at');
        }
    
        // Conditionally join employees for recommending_id
        if (in_array('recommending_id', $selectedColumns)) {
            $query->leftJoin('employees as recommending_employee', 'employee_overtimes.recommending_id', '=', 'recommending_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id");
        }
    
        // Conditionally join employees for approver_id
        if (in_array('approver_id', $selectedColumns)) {
            $query->leftJoin('employees as approver_employee', 'employee_overtimes.approver_id', '=', 'approver_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id");
        }
        
        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name");
        $transformedColumns[] = "employees.employee_id";
        
        // Remove 'description' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'description';
        });
        
        // Remove 'created_at' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'created_at';
        });
    
        // Merge selected columns with transformed columns
        $finalColumns = array_merge($selectedColumns, $transformedColumns);
    
        // Select the final columns
        $query->select($finalColumns);
    
        // Fetch the results
        $leaves = $query->get();
    
        // dd(DB::getQueryLog());
    
        // Return the response as JSON
        return response()->json([
            'data' => $leaves,
            'total_hours' => $query->sum('hours'), // This line can also be used if not grouping
        ]);
    }
}
