<?php

namespace App\Http\Controllers;

use App\Models\EmployeeGradeMaster;
use App\Models\EmpPersProfile;
use Illuminate\Http\Request;
use Illuminate\Http\JsonResponse;
use App\Models\EmpQuotaMaster;
use App\Models\PayBandMaster;
use App\Models\PayMatrixMaster;
use Illuminate\Support\Facades\DB;

class EmppersProfileController extends Controller
{

    public function index(Request $request): JsonResponse
    {
        try {
            $perPage = $request->get('per_page', 15);
            $search = $request->get('search', '');

            $query = EmpPersProfile::with([
                'employee',
                'grade',
                'grade.payband',
                'designation',
                'empType',
                'empStatus',
                'payBand.commission',
                'payrollBill',
                'payBand',
                'cityGrade',
                'gisfGroup',
                'gisvGroup',
                'woffPattern'
            ])->where('Is_Active', 1);

            if (!empty($search)) {
                $query->where(function ($q) use ($search) {
                    $q->where('EPP_Id', 'LIKE', "%{$search}%")
                        ->orWhere('Emp_Id', 'LIKE', "%{$search}%")
                        ->orWhere('EGM_Id', 'LIKE', "%{$search}%")
                        ->orWhere('Dsg_Id', 'LIKE', "%{$search}%")
                        ->orWhere('ETy_Id', 'LIKE', "%{$search}%")
                        ->orWhere('Est_Id', 'LIKE', "%{$search}%")
                        ->orWhere('Join_Date', 'LIKE', "%{$search}%");

                    $q->orWhereHas('employee', function ($emp) use ($search) {
                        $emp->where('First_Name', 'LIKE', "%{$search}%")
                            ->orWhere('Last_Name', 'LIKE', "%{$search}%")
                            ->orWhere('Emp_Code', 'LIKE', "%{$search}%")
                            ->orWhere('Email_Id', 'LIKE', "%{$search}%")
                            ->orWhere('Phone', 'LIKE', "%{$search}%")
                            ->orWhere('UID_AdharNo', 'LIKE', "%{$search}%")
                            ->orWhere('Bank_AcNo', 'LIKE', "%{$search}%")
                            ->orWhere('Gpf_AcNo', 'LIKE', "%{$search}%")
                            ->orWhere('Cpf_AcNo', 'LIKE', "%{$search}%");
                    });

                    $q->orWhereHas('grade', function ($grade) use ($search) {
                        $grade->where('EGM_Code', 'LIKE', "%{$search}%")
                            ->orWhere('EGM_Name', 'LIKE', "%{$search}%");
                    });

                    $q->orWhereHas('designation', function ($desg) use ($search) {
                        $desg->where('Dsg_Title', 'LIKE', "%{$search}%")
                            ->orWhere('Dsg_Desc', 'LIKE', "%{$search}%")
                            ->orWhere('Dsg_EName', 'LIKE', "%{$search}%");
                    });

                    $q->orWhereHas('empType', function ($type) use ($search) {
                        $type->where('ETy_Name', 'LIKE', "%{$search}%")
                            ->orWhere('ETy_Code', 'LIKE', "%{$search}%");
                    });
                });
            }

            $data = $query->orderBy('EPP_Id', 'desc')
                ->paginate($perPage);

            $allQuotaIds = collect($data->items())
                ->pluck('PHYHDCAP')
                ->flatten()
                ->unique()
                ->filter()
                ->values();

            $quotas = EmpQuotaMaster::whereIn('Qut_Id', $allQuotaIds)->get()
                ->keyBy('Qut_Id');

            $data->getCollection()->transform(function ($item) use ($quotas) {
                $item->quota_details = collect($item->PHYHDCAP ?? [])
                    ->map(function ($id) use ($quotas) {
                        return $quotas[$id] ?? null;
                    })
                    ->filter()
                    ->values();

                $item->departments = $item->department_list;

                return $item;
            });

            return response()->json([
                'success' => true,
                'message' => 'Employee profile list fetched successfully',
                'data' => $data
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function all(Request $request): JsonResponse
    {
        try {
            $search = $request->get('search', '');

            $query = EmpPersProfile::with([
                'employee',
                'grade',
                'designation',
                'empType',
                'empStatus',
                'payrollBill',
                'payBand.commission',
                'payBand',
                'cityGrade',
                'gisfGroup',
                'gisvGroup',
                'woffPattern'
            ])->where('Is_Active', 1);

            if (!empty($search)) {
                $query->where(function ($q) use ($search) {
                    $q->where('EPP_Id', 'LIKE', "%{$search}%")
                        ->orWhere('Emp_Id', 'LIKE', "%{$search}%")
                        ->orWhere('EGM_Id', 'LIKE', "%{$search}%")
                        ->orWhere('Dpt_id', 'LIKE', "%{$search}%")
                        ->orWhere('Dsg_Id', 'LIKE', "%{$search}%")
                        ->orWhere('ETy_Id', 'LIKE', "%{$search}%")
                        ->orWhere('Est_Id', 'LIKE', "%{$search}%")
                        ->orWhere('Join_Date', 'LIKE', "%{$search}%");

                    $q->orWhereHas('employee', function ($emp) use ($search) {
                        $emp->where('First_Name', 'LIKE', "%{$search}%")
                            ->orWhere('Last_Name', 'LIKE', "%{$search}%")
                            ->orWhere('Emp_Code', 'LIKE', "%{$search}%")
                            ->orWhere('Email_Id', 'LIKE', "%{$search}%")
                            ->orWhere('Phone', 'LIKE', "%{$search}%")
                            ->orWhere('UID_AdharNo', 'LIKE', "%{$search}%")
                            ->orWhere('Bank_AcNo', 'LIKE', "%{$search}%")
                            ->orWhere('Gpf_AcNo', 'LIKE', "%{$search}%")
                            ->orWhere('Cpf_AcNo', 'LIKE', "%{$search}%");
                    });

                    $q->orWhereHas('grade', function ($grade) use ($search) {
                        $grade->where('EGM_Code', 'LIKE', "%{$search}%")
                            ->orWhere('EGM_Name', 'LIKE', "%{$search}%");
                    });

                    $q->orWhereHas('designation', function ($desg) use ($search) {
                        $desg->where('Dsg_Title', 'LIKE', "%{$search}%")
                            ->orWhere('Dsg_Desc', 'LIKE', "%{$search}%")
                            ->orWhere('Dsg_EName', 'LIKE', "%{$search}%");
                    });

                    $q->orWhereHas('empType', function ($type) use ($search) {
                        $type->where('ETy_Name', 'LIKE', "%{$search}%")
                            ->orWhere('ETy_Code', 'LIKE', "%{$search}%");
                    });
                });
            }

            $data = $query->get();

            $data->transform(function ($item) {
                $item->departments = $item->department_list; 
                return $item;
            });

            return response()->json([
                'success' => true,
                'data' => $data
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function store(Request $request): JsonResponse
    {
        try {
            $validated = $request->validate([
                'Emp_Id' => 'required|exists:employee_master,Emp_Id',
                'EPP_Date' => 'nullable|date',
                'EGM_Id' => 'nullable|exists:emp_grade_master,EGM_Id',
                'Dpt_id' => 'nullable|array',           
                'Dpt_id.*' => 'exists:dept_master,Dpt_id',
                'Dsg_Id' => 'nullable|exists:designation_master,Dsg_id',
                'ETy_Id' => 'nullable|exists:emp_type_master,ETy_Id',
                'Join_Date' => 'nullable|date',
                'Est_Id' => 'nullable|exists:emp_status_master,Est_Id',
                'Is_Active' => 'boolean',
                'IncMonth' => 'nullable|string|max:20',
                'Basic' => 'nullable|numeric|min:0',
                'FixHRA' => 'nullable|numeric|min:0',
                'SP_Pay' => 'nullable|numeric|min:0',
                'PB_Id' => 'nullable|exists:pay_band_master,PB_Id',
                'DA' => 'nullable|boolean',
                'GISF' => 'nullable|boolean',
                'GISV' => 'nullable|boolean',
                'GISF_Em_gr' => 'nullable|exists:emp_group_ins,EGI_Id',
                'GISV_Em_gr' => 'nullable|exists:emp_group_ins,EGI_Id',
                'HRA' => 'nullable|boolean',
                'CLA' => 'nullable|boolean',
                'MA' => 'nullable|boolean',
                'TA' => 'nullable|boolean',
                'NPA' => 'nullable|boolean',
                'GPF' => 'nullable|string|max:20',
                'HBA' => 'nullable|boolean',
                'HBAINT' => 'nullable|numeric|min:0',
                'MCA' => 'nullable|boolean',
                'MCAINT' => 'nullable|numeric|min:0',
                'FOOD' => 'nullable|boolean',
                'FEST' => 'nullable|boolean',
                'IR' => 'nullable|boolean',
                'CPF_NPS' => 'nullable|string|max:50',
                'NPS_PERCENT' => 'nullable|numeric',
                'PHYHDCAP' => 'nullable|array',
                'PHYHDCAP.*' => 'exists:emp_quota_master,Qut_Id',
                'BillNo' => 'nullable|string|max:50',
                'PBillNo' => 'nullable|exists:payroll_bills,PRB_Id',
                'CityCode' => 'nullable|exists:city_grade,CGR_Id',
                'Status' => 'nullable|boolean',
                'WOP_id' => 'nullable|exists:woff_pattern,WOP_id'
            ]);

            $data = EmpPersProfile::create($validated);

            
            $data->departments = $data->department_list;

            return response()->json([
                'success' => true,
                'message' => 'Employee profile created successfully',
                'data' => $data
            ]);
        } catch (\Illuminate\Validation\ValidationException $ve) {
            return response()->json([
                'success' => false,
                'message' => 'Validation error',
                'errors' => $ve->errors()
            ], 422);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function show($id): JsonResponse
    {
        try {
            $data = EmpPersProfile::with([
                'employee',
                'grade',
                'designation',
                'empType',
                'empStatus',
                'payrollBill',
                'payBand.commission',
                'payBand',
                'cityGrade',
                'gisfGroup',
                'gisvGroup',
                'woffPattern'
            ])->find($id);

            if (!$data) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found'
                ], 404);
            }

            $quotaIds = $data->PHYHDCAP ?? [];
            $data->quota_details = EmpQuotaMaster::whereIn('Qut_Id', $quotaIds)->get();

            
            $data->departments = $data->department_list;

            return response()->json([
                'success' => true,
                'data' => $data
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function update(Request $request, $id): JsonResponse
    {
        DB::beginTransaction();

        try {
            $old = EmpPersProfile::find($id);

            if (!$old) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found'
                ], 404);
            }

            $validated = $request->validate([
                'Emp_Id' => 'sometimes|exists:employee_master,Emp_Id',
                'EPP_Date' => 'nullable|date',
                'EGM_Id' => 'nullable|exists:emp_grade_master,EGM_Id',
                'Dpt_id' => 'nullable|array',         
                'Dpt_id.*' => 'exists:dept_master,Dpt_id',
                'Dsg_Id' => 'nullable|exists:designation_master,Dsg_id',
                'ETy_Id' => 'nullable|exists:emp_type_master,ETy_Id',
                'Join_Date' => 'nullable|date',
                'Est_Id' => 'nullable|exists:emp_status_master,Est_Id',
                'IncMonth' => 'nullable|string|max:20',
                'Basic' => 'nullable|numeric|min:0',
                'FixHRA' => 'nullable|numeric|min:0',
                'SP_Pay' => 'nullable|numeric|min:0',
                'PB_Id' => 'nullable|exists:pay_band_master,PB_Id',
                'DA' => 'nullable|boolean',
                'GISF' => 'nullable|boolean',
                'GISV' => 'nullable|boolean',
                'GISF_Em_gr' => 'nullable|exists:emp_group_ins,EGI_Id',
                'GISV_Em_gr' => 'nullable|exists:emp_group_ins,EGI_Id',
                'HRA' => 'nullable|boolean',
                'CLA' => 'nullable|boolean',
                'MA' => 'nullable|boolean',
                'TA' => 'nullable|boolean',
                'NPA' => 'nullable|boolean',
                'GPF' => 'nullable|string|max:20',
                'HBA' => 'nullable|boolean',
                'HBAINT' => 'nullable|numeric|min:0',
                'MCA' => 'nullable|boolean',
                'MCAINT' => 'nullable|numeric|min:0',
                'FOOD' => 'nullable|boolean',
                'FEST' => 'nullable|boolean',
                'IR' => 'nullable|boolean',
                'CPF_NPS' => 'nullable|string|max:50',
                'NPS_PERCENT' => 'nullable|numeric',
                'PHYHDCAP' => 'nullable|array',
                'PHYHDCAP.*' => 'exists:emp_quota_master,Qut_Id',
                'BillNo' => 'nullable|string|max:50',
                'PBillNo' => 'nullable|exists:payroll_bills,PRB_Id',
                'CityCode' => 'nullable|exists:city_grade,CGR_Id',
                'WOP_id' => 'nullable|exists:woff_pattern,WOP_id'
            ]);

            $old->Is_Active = 0;
            $old->save();

            $newRecord = $old->replicate();

            foreach ($validated as $key => $value) {
                if ($key === 'PHYHDCAP' || $key === 'Dpt_id') {
                    $newRecord->$key = $value;
                } else {
                    $newRecord->$key = $value;
                }
            }

            $newRecord->Is_Active = 1;
            $newRecord->save();

            DB::commit();

            $newRecord->departments = $newRecord->department_list;  

            return response()->json([
                'success' => true,
                'message' => 'New record created & old deactivated',
                'data' => $newRecord
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function destroy($id): JsonResponse
    {
        try {
            $data = EmpPersProfile::find($id);

            if (!$data) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found'
                ], 404);
            }

            $data->delete();

            return response()->json([
                'success' => true,
                'message' => 'Employee profile deleted successfully'
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    
    public function getProfilesByDepartment($deptId)
    {
        try {
            $profiles = EmpPersProfile::whereJsonContains('Dpt_id', (string)$deptId)
                ->with(['employee', 'designation', 'grade'])
                ->where('Is_Active', 1)
                ->get();

            $profiles->transform(function ($profile) {
                $profile->departments = $profile->department_list;
                return $profile;
            });

            return response()->json([
                'success' => true,
                'data' => $profiles
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch profiles',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }


    public function getPayMatricesByPayBand($pb_id): JsonResponse
    {
        try {
            
            $payBand = PayBandMaster::with('commission')->find($pb_id);

            if (!$payBand) {
                return response()->json([
                    'success' => false,
                    'message' => 'Pay band not found'
                ], 404);
            }

            
            $grades = EmployeeGradeMaster::where('PB_ID', $pb_id)
                ->where('Is_Active', 1)
                ->get();

            if ($grades->isEmpty()) {
                return response()->json([
                    'success' => true,
                    'message' => 'No grades found for this pay band',
                    'data' => [
                        'pay_band' => [
                            'PB_Id' => $payBand->PB_Id,
                            'PB_Code' => $payBand->PB_Code,
                            'Min_Pay' => $payBand->Min_Pay,
                            'Max_Pay' => $payBand->Max_Pay,
                            'Min_Pay_formatted' => '₹' . number_format(floatval($payBand->Min_Pay), 2),
                            'Max_Pay_formatted' => '₹' . number_format(floatval($payBand->Max_Pay), 2),
                            'commission' => $payBand->commission
                        ],
                        'pay_matrices' => [],
                        'dropdown_options' => [],
                        'summary' => [
                            'total_grades' => 0,
                            'total_pay_matrices' => 0,
                            'range' => [
                                'min' => $payBand->Min_Pay,
                                'max' => $payBand->Max_Pay,
                                'min_formatted' => '₹' . number_format(floatval($payBand->Min_Pay), 2),
                                'max_formatted' => '₹' . number_format(floatval($payBand->Max_Pay), 2)
                            ]
                        ]
                    ]
                ]);
            }

            $gradeIds = $grades->pluck('EGM_Id');

            
            $payMatrices = PayMatrixMaster::whereIn('EGM_id', $gradeIds)
                ->whereBetween('Basic_Pay', [
                    floatval($payBand->Min_Pay),
                    floatval($payBand->Max_Pay)
                ])
                ->with('empGrade')  
                ->orderBy('EGM_id')
                ->orderBy('Cell_Index')
                ->get();

            
            $formattedMatrices = $payMatrices->map(function ($matrix) {
                return [
                    'PMM_Id' => $matrix->PMM_Id,
                    'Cell_Index' => $matrix->Cell_Index,
                    'Basic_Pay' => $matrix->Basic_Pay,
                    'Basic_Pay_formatted' => '₹' . number_format(floatval($matrix->Basic_Pay), 2),
                    'display_text' => 'Cell ' . $matrix->Cell_Index . ' - ₹' . number_format(floatval($matrix->Basic_Pay), 2),
                    'grade' => $matrix->empGrade ? [
                        'EGM_Id' => $matrix->empGrade->EGM_Id,
                        'EGM_Code' => $matrix->empGrade->EGM_Code,
                        'EGM_Name' => $matrix->empGrade->EGM_Name,
                        'PB_ID' => $matrix->empGrade->PB_ID
                    ] : null
                ];
            })->values();

            
            $dropdownOptions = $payMatrices->map(function ($matrix) {
                $gradeCode = $matrix->empGrade->EGM_Code ?? 'Unknown';
                $gradeName = $matrix->empGrade->EGM_Name ?? 'Unknown';

                return [
                    'value' => $matrix->PMM_Id,
                    'label' => $gradeCode . ' - ' . $gradeName . ' - Cell ' . $matrix->Cell_Index . ' (₹' . number_format(floatval($matrix->Basic_Pay), 2) . ')',
                    'basic_pay' => $matrix->Basic_Pay,
                    'cell_index' => $matrix->Cell_Index,
                    'grade_code' => $gradeCode,
                    'grade_name' => $gradeName
                ];
            })->values();

            
            $groupedByGrade = $payMatrices->groupBy('EGM_id')->map(function ($matrices, $gradeId) use ($grades) {
                $grade = $grades->firstWhere('EGM_Id', $gradeId);

                return [
                    'grade_id' => $gradeId,
                    'grade_code' => $grade->EGM_Code ?? null,
                    'grade_name' => $grade->EGM_Name ?? null,
                    'matrices' => $matrices->map(function ($matrix) {
                        return [
                            'PMM_Id' => $matrix->PMM_Id,
                            'Cell_Index' => $matrix->Cell_Index,
                            'Basic_Pay' => $matrix->Basic_Pay,
                            'Basic_Pay_formatted' => '₹' . number_format(floatval($matrix->Basic_Pay), 2),
                            'display_text' => 'Cell ' . $matrix->Cell_Index . ' - ₹' . number_format(floatval($matrix->Basic_Pay), 2)
                        ];
                    })->values()
                ];
            })->values();

            return response()->json([
                'success' => true,
                'message' => 'Pay matrices within pay band range fetched successfully',
                'data' => [
                    'pay_band' => [
                        'PB_Id' => $payBand->PB_Id,
                        'PB_Code' => $payBand->PB_Code,
                        'Min_Pay' => $payBand->Min_Pay,
                        'Max_Pay' => $payBand->Max_Pay,
                        'Min_Pay_formatted' => '₹' . number_format(floatval($payBand->Min_Pay), 2),
                        'Max_Pay_formatted' => '₹' . number_format(floatval($payBand->Max_Pay), 2),
                        'commission' => $payBand->commission
                    ],
                    'grades' => $grades->map(function ($grade) {
                        return [
                            'EGM_Id' => $grade->EGM_Id,
                            'EGM_Code' => $grade->EGM_Code,
                            'EGM_Name' => $grade->EGM_Name,
                            'PB_ID' => $grade->PB_ID
                        ];
                    }),
                    'grouped_by_grade' => $groupedByGrade,
                    'pay_matrices' => $formattedMatrices,
                    'dropdown_options' => $dropdownOptions,
                    'summary' => [
                        'total_grades' => $grades->count(),
                        'total_pay_matrices' => $payMatrices->count(),
                        'range' => [
                            'min' => $payBand->Min_Pay,
                            'max' => $payBand->Max_Pay,
                            'min_formatted' => '₹' . number_format(floatval($payBand->Min_Pay), 2),
                            'max_formatted' => '₹' . number_format(floatval($payBand->Max_Pay), 2)
                        ]
                    ]
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage(),
                'line' => $e->getLine(),
                'file' => $e->getFile()
            ], 500);
        }
    }

  
    public function getPayMatricesByGrade($grade_id): JsonResponse
    {
        try {
            
            $grade = EmployeeGradeMaster::with('payBand.commission')->find($grade_id);

            if (!$grade) {
                return response()->json([
                    'success' => false,
                    'message' => 'Grade not found'
                ], 404);
            }

            
            $payMatrices = PayMatrixMaster::where('EGM_id', $grade_id)
                ->with('empGrade')
                ->orderBy('Cell_Index')
                ->get();

            if ($payMatrices->isEmpty()) {
                return response()->json([
                    'success' => true,
                    'message' => 'No pay matrices found for this grade',
                    'data' => [
                        'grade' => [
                            'EGM_Id' => $grade->EGM_Id,
                            'EGM_Code' => $grade->EGM_Code,
                            'EGM_Name' => $grade->EGM_Name,
                            'PB_ID' => $grade->PB_ID,
                            'pay_band' => $grade->payBand ? [
                                'PB_Id' => $grade->payBand->PB_Id,
                                'PB_Code' => $grade->payBand->PB_Code,
                                'Min_Pay' => $grade->payBand->Min_Pay,
                                'Max_Pay' => $grade->payBand->Max_Pay,
                                'Min_Pay_formatted' => '₹' . number_format(floatval($grade->payBand->Min_Pay), 2),
                                'Max_Pay_formatted' => '₹' . number_format(floatval($grade->payBand->Max_Pay), 2)
                            ] : null
                        ],
                        'pay_matrices' => [],
                        'dropdown_options' => [],
                        'summary' => [
                            'total_cells' => 0,
                            'min_basic' => null,
                            'max_basic' => null
                        ]
                    ]
                ]);
            }

            
            $formattedMatrices = $payMatrices->map(function ($matrix) {
                return [
                    'PMM_Id' => $matrix->PMM_Id,
                    'Cell_Index' => $matrix->Cell_Index,
                    'Basic_Pay' => $matrix->Basic_Pay,
                    'Basic_Pay_formatted' => '₹' . number_format(floatval($matrix->Basic_Pay), 2),
                    'display_text' => 'Cell ' . $matrix->Cell_Index . ' - ₹' . number_format(floatval($matrix->Basic_Pay), 2),
                    'grade_info' => $matrix->empGrade ? [
                        'EGM_Id' => $matrix->empGrade->EGM_Id,
                        'EGM_Code' => $matrix->empGrade->EGM_Code,
                        'EGM_Name' => $matrix->empGrade->EGM_Name
                    ] : null
                ];
            })->values();

            
            $dropdownOptions = $payMatrices->map(function ($matrix) {
                return [
                    'value' => $matrix->PMM_Id,
                    'label' => 'Cell ' . $matrix->Cell_Index . ' - ₹' . number_format(floatval($matrix->Basic_Pay), 2),
                    'basic_pay' => $matrix->Basic_Pay,
                    'cell_index' => $matrix->Cell_Index,
                    'basic_pay_formatted' => '₹' . number_format(floatval($matrix->Basic_Pay), 2)
                ];
            })->values();

            
            $basicPays = $payMatrices->pluck('Basic_Pay')->map('floatval');

            return response()->json([
                'success' => true,
                'message' => 'Pay matrices for grade fetched successfully',
                'data' => [
                    'grade' => [
                        'EGM_Id' => $grade->EGM_Id,
                        'EGM_Code' => $grade->EGM_Code,
                        'EGM_Name' => $grade->EGM_Name,
                        'PB_ID' => $grade->PB_ID,
                        'pay_band' => $grade->payBand ? [
                            'PB_Id' => $grade->payBand->PB_Id,
                            'PB_Code' => $grade->payBand->PB_Code,
                            'Min_Pay' => $grade->payBand->Min_Pay,
                            'Max_Pay' => $grade->payBand->Max_Pay,
                            'Min_Pay_formatted' => '₹' . number_format(floatval($grade->payBand->Min_Pay), 2),
                            'Max_Pay_formatted' => '₹' . number_format(floatval($grade->payBand->Max_Pay), 2),
                            'commission' => $grade->payBand->commission
                        ] : null
                    ],
                    'pay_matrices' => $formattedMatrices,
                    'dropdown_options' => $dropdownOptions,
                    'summary' => [
                        'total_cells' => $payMatrices->count(),
                        'min_basic' => $basicPays->min(),
                        'max_basic' => $basicPays->max(),
                        'min_basic_formatted' => '₹' . number_format($basicPays->min(), 2),
                        'max_basic_formatted' => '₹' . number_format($basicPays->max(), 2),
                        'increment_steps' => $payMatrices->count() - 1
                    ],
                    'matrix_table' => [
                        'headers' => ['Cell Index', 'Basic Pay', 'PMM_Id'],
                        'rows' => $payMatrices->map(function ($matrix) {
                            return [
                                'cell' => $matrix->Cell_Index,
                                'basic' => '₹' . number_format(floatval($matrix->Basic_Pay), 2),
                                'id' => $matrix->PMM_Id
                            ];
                        })->values()
                    ]
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage(),
                'line' => $e->getLine(),
                'file' => $e->getFile()
            ], 500);
        }
    }

    public function getPayMatrixByBasicPay($basic_pay): JsonResponse
    {
        try {
            
            $payMatrix = PayMatrixMaster::where('Basic_Pay', $basic_pay)
                ->with([
                    'empGrade',
                    'empGrade.payband',
                    'empGrade.payband.commission'
                ])
                ->first();

            if (!$payMatrix) {
                return response()->json([
                    'success' => false,
                    'message' => 'Pay matrix not found for this basic pay amount'
                ], 404);
            }

            
            $allGradeMatrices = PayMatrixMaster::where('EGM_id', $payMatrix->EGM_id)
                ->with('empGrade')
                ->orderBy('Cell_Index')
                ->get();

            
            $currentMatrix = [
                'PMM_Id' => $payMatrix->PMM_Id,
                'EGM_id' => $payMatrix->EGM_id,
                'Cell_Index' => $payMatrix->Cell_Index,
                'Basic_Pay' => $payMatrix->Basic_Pay,
                'Basic_Pay_formatted' => '₹' . number_format(floatval($payMatrix->Basic_Pay), 2),
                'created_at' => $payMatrix->created_at,
                'updated_at' => $payMatrix->updated_at,
                'grade' => $payMatrix->empGrade ? [
                    'EGM_Id' => $payMatrix->empGrade->EGM_Id,
                    'EGM_Code' => $payMatrix->empGrade->EGM_Code,
                    'EGM_Name' => $payMatrix->empGrade->EGM_Name,
                    'PB_ID' => $payMatrix->empGrade->PB_ID,
                    'Is_Active' => $payMatrix->empGrade->Is_Active,
                    'payband' => $payMatrix->empGrade->payband ? [
                        'PB_Id' => $payMatrix->empGrade->payband->PB_Id,
                        'PB_Code' => $payMatrix->empGrade->payband->PB_Code,
                        'Min_Pay' => $payMatrix->empGrade->payband->Min_Pay,
                        'Max_Pay' => $payMatrix->empGrade->payband->Max_Pay,
                        'Min_Pay_formatted' => '₹' . number_format(floatval($payMatrix->empGrade->payband->Min_Pay), 2),
                        'Max_Pay_formatted' => '₹' . number_format(floatval($payMatrix->empGrade->payband->Max_Pay), 2),
                        'commission' => $payMatrix->empGrade->payband->commission
                    ] : null
                ] : null
            ];

            
            $allMatrices = $allGradeMatrices->map(function ($matrix) use ($basic_pay) {
                return [
                    'PMM_Id' => $matrix->PMM_Id,
                    'EGM_id' => $matrix->EGM_id,
                    'Cell_Index' => $matrix->Cell_Index,
                    'Basic_Pay' => $matrix->Basic_Pay,
                    'Basic_Pay_formatted' => '₹' . number_format(floatval($matrix->Basic_Pay), 2),
                    'is_current' => ($matrix->Basic_Pay == $basic_pay),
                    'grade_code' => $matrix->empGrade->EGM_Code ?? null,
                    'grade_name' => $matrix->empGrade->EGM_Name ?? null
                ];
            })->values();

            
            $currentIndex = $allGradeMatrices->search(function ($item) use ($basic_pay) {
                return $item->Basic_Pay == $basic_pay;
            });

            $prevMatrix = $currentIndex > 0 ? $allGradeMatrices[$currentIndex - 1] : null;
            $nextMatrix = $currentIndex < $allGradeMatrices->count() - 1 ? $allGradeMatrices[$currentIndex + 1] : null;

            return response()->json([
                'success' => true,
                'message' => 'Pay matrix details fetched successfully',
                'data' => [
                    'selected_basic_pay' => $basic_pay,
                    'selected_basic_pay_formatted' => '₹' . number_format(floatval($basic_pay), 2),
                    'current_matrix' => $currentMatrix,
                    'all_matrices_for_grade' => $allMatrices,
                    'matrix_navigation' => [
                        'current_position' => $currentIndex + 1,
                        'total_cells' => $allGradeMatrices->count(),
                        'previous' => $prevMatrix ? [
                            'PMM_Id' => $prevMatrix->PMM_Id,
                            'Cell_Index' => $prevMatrix->Cell_Index,
                            'Basic_Pay' => $prevMatrix->Basic_Pay,
                            'Basic_Pay_formatted' => '₹' . number_format(floatval($prevMatrix->Basic_Pay), 2)
                        ] : null,
                        'next' => $nextMatrix ? [
                            'PMM_Id' => $nextMatrix->PMM_Id,
                            'Cell_Index' => $nextMatrix->Cell_Index,
                            'Basic_Pay' => $nextMatrix->Basic_Pay,
                            'Basic_Pay_formatted' => '₹' . number_format(floatval($nextMatrix->Basic_Pay), 2)
                        ] : null
                    ],
                    'summary' => [
                        'grade' => $payMatrix->empGrade->EGM_Code ?? 'Unknown',
                        'grade_name' => $payMatrix->empGrade->EGM_Name ?? 'Unknown',
                        'pay_band' => $payMatrix->empGrade->payband->PB_Code ?? 'Unknown',
                        'pay_band_range' => $payMatrix->empGrade->payband ?
                            '₹' . number_format(floatval($payMatrix->empGrade->payband->Min_Pay), 2) .
                            ' - ₹' . number_format(floatval($payMatrix->empGrade->payband->Max_Pay), 2) : 'Unknown',
                        'current_cell' => $payMatrix->Cell_Index,
                        'current_basic' => '₹' . number_format(floatval($payMatrix->Basic_Pay), 2)
                    ]
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage(),
                'line' => $e->getLine(),
                'file' => $e->getFile()
            ], 500);
        }
    }

    public function getPayMatrixByGrade($egm_id): JsonResponse
    {
        try {

            $payMatrices = PayMatrixMaster::with([
                'empGrade',
                'empGrade.payband'
            ])
                ->where('EGM_id', $egm_id)
                ->orderBy('Cell_Index')
                ->get();

            if ($payMatrices->isEmpty()) {
                return response()->json([
                    'success' => false,
                    'message' => 'No pay matrix found for this grade'
                ], 404);
            }

            return response()->json([
                'success' => true,
                'message' => 'Pay matrix list fetched successfully',
                'data' => $payMatrices
            ]);
        } catch (\Exception $e) {

            return response()->json([
                'success' => false,
                'message' => 'Something went wrong',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }
}
