<?php

namespace App\Http\Controllers;

use App\Models\TndEMDTrans;
use App\Models\WorkBGTrans;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class EmdReportController extends Controller
{
  public function emdRegister(Request $request)
{
    try {
        
        $request->validate([
            'from_date' => 'nullable|date',
            'to_date' => 'nullable|date|after_or_equal:from_date',
            'status' => 'nullable|integer|exists:action_master,Act_Id',
            'tender_id' => 'nullable|integer|exists:tender_entry,TE_Id',
            'vendor_id' => 'nullable|integer|exists:subledg_master,SL_Id',
        ]);

        
        $query = TndEMDTrans::with([
            'subledger:SL_Id,SL_Name,SL_Code',
            'tender:TE_Id,TnT_ID,TE_Date,Proposal_Amount,Description,Dpt_id', 
            'tender.tendertype:TnT_ID,TnT_Name', 
            'hod:Usr_Id,Usr_Name',
            'statusAction'
        ]);

        
        if ($request->filled('from_date')) {
            $query->whereDate('TES_Date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('TES_Date', '<=', $request->to_date);
        }

        if ($request->filled('status')) {
            $query->where('Status', $request->status);
        }

        if ($request->filled('tender_id')) {
            $query->where('TE_Id', $request->tender_id);
        }

        if ($request->filled('vendor_id')) {
            $query->where('SL_Id', $request->vendor_id);
        }

        
        $query->orderBy('TES_Date', 'desc')
              ->orderBy('created_at', 'desc');

        
        $emdEntries = $query->get();

        
        $totalEMD = $emdEntries->sum('EMD_Amt');

        
        $tenderWiseTotals = $emdEntries->filter(function ($item) {
            return $item->tender !== null;
        })->groupBy('TE_Id')->map(function ($items) {
            $firstItem = $items->first();
            return [
                'tender_id' => $firstItem->TE_Id,
                'tender_no' => $firstItem->tender->TnT_ID ?? null,
                'tender_name' => $firstItem->tender->Description ?? null,
                'total_emd' => number_format($items->sum('EMD_Amt'), 2),
                'entry_count' => $items->count()
            ];
        })->values();

        
        $reportData = [
            'report_name' => 'EMD Register',
            'generated_date' => now()->format('Y-m-d H:i:s'),
            'filters_applied' => [
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'status' => $request->status,
                'tender_id' => $request->tender_id,
                'vendor_id' => $request->vendor_id,
            ],
            'summary' => [
                'total_records' => $emdEntries->count(),
                'total_emd_amount' => number_format($totalEMD, 2),
                'tender_wise_totals' => $tenderWiseTotals,
            ],
            'entries' => $emdEntries->map(function ($entry) {
                
                $maturityDate = null;
                if ($entry->TES_Date) {
                    $maturityDate = date('Y-m-d', strtotime($entry->TES_Date . ' +1 year'));
                }

                return [
                    'emd_entry_id' => $entry->TET_Id,

                    'vendor_id' => $entry->SL_Id,
                    'vendor_name' => $entry->subledger->SL_Name ?? null,

                    'tender_id' => $entry->TE_Id,
                    'tender_no' => $entry->tender->TnT_ID ?? null,
                    'tender_name' => $entry->tender->Description ?? null, 
                    'tender_date' => $entry->tender ? $entry->tender->TE_Date : null,
                    'tender_type' => $entry->tender->tendertype->TnT_Name ?? null, 
                    'amount_proposed' => ($entry->tender && $entry->tender->Proposal_Amount) ? number_format($entry->tender->Proposal_Amount, 2) : null,

                    'account_budget_head' => [
                        'id' => $entry->subledger->SL_Id ?? null,
                        'code' => $entry->subledger->SL_Code ?? null,
                        'name' => $entry->subledger->SL_Name ?? null,
                    ],

                    'emd_submission_date' => $entry->TES_Date,
                    'emd_start_date' => $entry->TE_Date,
                    'emd_expiry_date' => $entry->TEE_Date,
                    'emd_release_date' => $entry->TER_Date,
                    'emd_amount' => number_format($entry->EMD_Amt, 2),
                    'maturity_date' => $maturityDate,
                    'for_account' => $entry->For_Acc == 'YES' ? 'Yes' : ($entry->For_Acc == 'NO' ? 'No' : $entry->For_Acc),

                    'status_id' => $entry->Status,
                    'status_name' => $entry->statusAction->St_Name ?? 'Unknown',
                    'status_stage' => $entry->statusAction->stage ?? null,

                    'reference_cd' => $entry->TET_CD,
                    'reference_bill' => $entry->TET_BillNo,
                    'hod_name' => $entry->hod->Usr_Name ?? null,
                    'hod_approval_date' => $entry->TET_HDT,
                    'remarks' => $entry->Remarks,
                    'created_at' => $entry->created_at,
                    'updated_at' => $entry->updated_at,
                ];
            }),
        ];

        return response()->json([
            'success' => true,
            'message' => 'EMD Register report generated successfully',
            'data' => $reportData
        ], 200);

    } catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Error generating EMD Register report',
            'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage(),
            'line' => $e->getLine(),
            'file' => $e->getFile()
        ], 500);
    }
}

     public function bgRegister(Request $request)
{
    try {
        $request->validate([
            'from_date' => 'nullable|date',
            'to_date' => 'nullable|date|after_or_equal:from_date',
            'status' => 'nullable|string|in:draft,hod_app,approved,rejected',
            'tender_id' => 'nullable|integer|exists:tender_entry,TE_Id',
            'vendor_id' => 'nullable|integer|exists:subledg_master,SL_Id',
            'work_allocation_id' => 'nullable|integer|exists:work_allocation_book,WA_Id',
        ]);

        $query = WorkBGTrans::with([
            'subledger:SL_Id,SL_Name,SL_Code',
            'workAllocation:WA_Id,WA_RefId,TE_Id,WO_Amount,WO_Date',
            'workAllocation.tender:TE_Id,TnT_ID',
            'hod:Usr_Id,Usr_Name'
        ]);

        if ($request->filled('from_date')) {
            $query->whereDate('WBT_Date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('WBT_Date', '<=', $request->to_date);
        }

        if ($request->filled('status')) {
            $query->where('Status', $request->status);
        }

        if ($request->filled('vendor_id')) {
            $query->where('SL_Id', $request->vendor_id);
        }

        if ($request->filled('work_allocation_id')) {
            $query->where('WA_Id', $request->work_allocation_id);
        }

        if ($request->filled('tender_id')) {
            $query->whereHas('workAllocation', function ($q) use ($request) {
                $q->where('TE_Id', $request->tender_id);
            });
        }

        $query->orderBy('WBT_Date', 'desc')
              ->orderBy('created_at', 'desc');

        $bgEntries = $query->get();

        $totalBG = $bgEntries->sum('BG_Amt');

        $tenderWiseTotals = $bgEntries->groupBy('WA_Id')->map(function ($items) {
            $firstItem = $items->first();
            $tenderNo = $firstItem->workAllocation->tender->TnT_ID ?? null;
            $workOrderNo = $firstItem->workAllocation->WA_RefId ?? null;

            return [
                'wa_id' => $firstItem->WA_Id,
                'tender_id' => $firstItem->workAllocation->TE_Id ?? null, 
                'tender_no' => $tenderNo,
                'work_order_no' => $workOrderNo,
                'total_bg' => number_format($items->sum('BG_Amt'), 2),
                'entry_count' => $items->count()
            ];
        })->values();

        
        $reportData = [
            'report_name' => 'BG Register',
            'generated_date' => now()->format('Y-m-d H:i:s'),
            'filters_applied' => [
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'status' => $request->status,
                'tender_id' => $request->tender_id,
                'vendor_id' => $request->vendor_id,
                'work_allocation_id' => $request->work_allocation_id,
            ],
            'summary' => [
                'total_records' => $bgEntries->count(),
                'total_bg_amount' => number_format($totalBG, 2),
                'tender_wise_totals' => $tenderWiseTotals,
            ],
            'entries' => $bgEntries->map(function ($entry) {
                
                $maturityDate = null;
                if ($entry->BG_Amt && $entry->WBT_Date) {
                    $maturityDate = date('Y-m-d', strtotime($entry->WBT_Date . ' +1 year'));
                }

                
                $statusMap = [
                    'draft' => 'Draft',
                    'hod_app' => 'HOD Approved',
                    'approved' => 'Approved',
                    'rejected' => 'Rejected'
                ];
                $statusDisplay = $statusMap[$entry->Status] ?? ucfirst($entry->Status);

                return [

                    'vendor_id' => $entry->SL_Id,
                    'vendor_name' => $entry->subledger->SL_Name ?? null,

                    'tender_id' => $entry->workAllocation->TE_Id ?? null,
                    'tender_no' => $entry->workAllocation->tender->TnT_ID ?? null,

                    'work_allocation_id' => $entry->WA_Id,
                    'work_order_no' => $entry->workAllocation->WA_RefId ?? null,
                    'work_order_date' => $entry->workAllocation->WO_Date ?? null,
                    'order_amount' => $entry->workAllocation->WO_Amount ? number_format($entry->workAllocation->WO_Amount, 2) : null,

                    'account_budget_head' => [
                        'id' => $entry->subledger->SL_Id ?? null,
                        'code' => $entry->subledger->SL_Code ?? null,
                        'name' => $entry->subledger->SL_Name ?? null,
                    ],

                    'bg_date' => $entry->WBT_Date,
                    'bg_amount' => $entry->BG_Amt ? number_format($entry->BG_Amt, 2) : '0.00',
                    'bg_type' => $entry->WBT_Type ?? 'Receipt',
                    'maturity_date' => $maturityDate,

                    'status' => $entry->Status,
                    'status_display' => $statusDisplay,

                    'reference_cd' => $entry->WBT_CD,
                    'reference_bill' => $entry->WBT_BillNo,
                    'hod_name' => $entry->hod->Usr_Name ?? null,
                    'hod_approval_date' => $entry->WBT_HDT,
                    'remarks' => $entry->Remarks,
                ];
            }),
        ];

        return response()->json([
            'success' => true,
            'message' => 'BG Register report generated successfully',
            'data' => $reportData
        ], 200);

    } catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Error generating BG Register report',
            'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage(),
            'line' => $e->getLine(),
            'file' => $e->getFile()
        ], 500);
    }
}

    private function getStatusDisplay($status)
{
    $statuses = [
        'draft' => 'Draft',
        'hod_app' => 'HOD Approved',
        'approved' => 'Approved',
        'rejected' => 'Rejected'
    ];

    return $statuses[$status] ?? ucfirst($status);
}


 public function departmentwiseEmdRegister(Request $request)
{
    try {
        $request->validate([
            'from_date' => 'nullable|date',
            'to_date' => 'nullable|date|after_or_equal:from_date',
            'status' => 'nullable|integer|exists:action_master,Act_Id',
            'tender_id' => 'nullable|integer|exists:tender_entry,TE_Id',
            'vendor_id' => 'nullable|integer|exists:subledg_master,SL_Id',
            'department_id' => 'nullable|integer|exists:dept_master,Dpt_id',
        ]);

        $query = TndEMDTrans::with([
            'subledger:SL_Id,SL_Name,SL_Code',
            'tender:TE_Id,TnT_ID,TE_Date,Proposal_Amount,Description,Dpt_id', 
            'tender.tendertype:TnT_ID,TnT_Name', 
            'tender.department:Dpt_id,Dpt_Name',
            'hod:Usr_Id,Usr_Name',
            'statusAction'
        ]);

        
        if ($request->filled('from_date')) {
            $query->whereDate('TES_Date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('TES_Date', '<=', $request->to_date);
        }

        if ($request->filled('status')) {
            $query->where('Status', $request->status);
        }

        if ($request->filled('tender_id')) {
            $query->where('TE_Id', $request->tender_id);
        }

        if ($request->filled('vendor_id')) {
            $query->where('SL_Id', $request->vendor_id);
        }

    
        if ($request->filled('department_id')) {
            $query->whereHas('tender', function ($q) use ($request) {
                $q->where('Dpt_id', $request->department_id);
            });
        }

        $query->orderBy('TES_Date', 'desc')
              ->orderBy('created_at', 'desc');

        $emdEntries = $query->get();


        $departmentWiseData = $emdEntries->filter(function ($item) {
            return $item->tender !== null && $item->tender->department !== null;
        })->groupBy(function ($item) {
            return $item->tender->department->Dpt_id ?? 'no-department';
        })->map(function ($items, $deptId) {
            $firstItem = $items->first();
            $department = $firstItem->tender->department ?? null;

            $deptTotalEMD = $items->sum('EMD_Amt');

            $tenderWiseTotals = $items->groupBy('TE_Id')->map(function ($tenderItems) {
                $tenderFirst = $tenderItems->first();
                return [
                    'tender_id' => $tenderFirst->TE_Id,
                    'tender_no' => $tenderFirst->tender->TnT_ID ?? null,
                    'tender_name' => $tenderFirst->tender->Description ?? null, 
                    'tender_date' => $tenderFirst->tender->TE_Date ?? null,
                    'tender_type' => $tenderFirst->tender->tendertype->TnT_Name ?? null, 
                    'amount_proposed' => ($tenderFirst->tender && $tenderFirst->tender->Proposal_Amount) ? number_format($tenderFirst->tender->Proposal_Amount, 2) : null,
                    'total_emd' => number_format($tenderItems->sum('EMD_Amt'), 2),
                    'entry_count' => $tenderItems->count()
                ];
            })->values();

            return [
                'department' => [
                    'id' => $deptId === 'no-department' ? null : $department->Dpt_id,
                    'name' => $deptId === 'no-department' ? 'No Department Assigned' : ($department->Dpt_Name ?? 'Unknown'),
                ],
                'summary' => [
                    'total_records' => $items->count(),
                    'total_department_emd' => number_format($deptTotalEMD, 2),
                    'tender_wise_totals' => $tenderWiseTotals,
                ],
                'entries' => $items->map(function ($entry) {
                    $maturityDate = null;
                    if ($entry->TES_Date) {
                        $maturityDate = date('Y-m-d', strtotime($entry->TES_Date . ' +1 year'));
                    }

                    return [
                        'emd_entry_id' => $entry->TET_Id,

                        'vendor_id' => $entry->SL_Id,
                        'vendor_name' => $entry->subledger->SL_Name ?? null,

                        'tender_id' => $entry->TE_Id,
                        'tender_no' => $entry->tender->TnT_ID ?? null,
                        'tender_name' => $entry->tender->Description ?? null, 
                        'tender_date' => $entry->tender->TE_Date ?? null,
                        'tender_type' => $entry->tender->tendertype->TnT_Name ?? null, 
                        'amount_proposed' => ($entry->tender && $entry->tender->Proposal_Amount) ? number_format($entry->tender->Proposal_Amount, 2) : null,

                        
                        'account_budget_head' => [
                            'id' => $entry->subledger->SL_Id ?? null,
                            'code' => $entry->subledger->SL_Code ?? null,
                            'name' => $entry->subledger->SL_Name ?? null,
                        ],

                        'emd_submission_date' => $entry->TES_Date,
                        'emd_start_date' => $entry->TE_Date,
                        'emd_expiry_date' => $entry->TEE_Date,
                        'emd_release_date' => $entry->TER_Date,
                        'emd_amount' => number_format($entry->EMD_Amt, 2),
                        'maturity_date' => $maturityDate,
                        'for_account' => $entry->For_Acc == 'YES' ? 'Yes' : ($entry->For_Acc == 'NO' ? 'No' : $entry->For_Acc),

                        'status_id' => $entry->Status,
                        'status_name' => $entry->statusAction->St_Name ?? 'Unknown',
                        'status_stage' => $entry->statusAction->stage ?? null,

                        'reference_cd' => $entry->TET_CD,
                        'reference_bill' => $entry->TET_BillNo,
                        'hod_name' => $entry->hod->Usr_Name ?? null,
                        'hod_approval_date' => $entry->TET_HDT,
                        'remarks' => $entry->Remarks,
                        'created_at' => $entry->created_at,
                        'updated_at' => $entry->updated_at,
                    ];
                })->values()
            ];
        })->values();

        
        $grandTotalEMD = $emdEntries->sum('EMD_Amt');

        
        $reportData = [
            'report_name' => 'Departmentwise EMD Register',
            'generated_date' => now()->format('Y-m-d H:i:s'),
            'filters_applied' => [
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'status' => $request->status,
                'tender_id' => $request->tender_id,
                'vendor_id' => $request->vendor_id,
                'department_id' => $request->department_id,
            ],
            'grand_summary' => [
                'total_departments' => $departmentWiseData->count(),
                'total_records' => $emdEntries->count(),
                'grand_total_emd' => number_format($grandTotalEMD, 2),
            ],
            'department_wise_data' => $departmentWiseData,
        ];

        return response()->json([
            'success' => true,
            'message' => 'Departmentwise EMD Register report generated successfully',
            'data' => $reportData
        ], 200);

    } catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Error generating Departmentwise EMD Register report',
            'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage(),
            'line' => $e->getLine(),
            'file' => $e->getFile()
        ], 500);
    }
}
   /**
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function departmentwiseBgRegister(Request $request)
    {
        try {
           
            $request->validate([
                'from_date' => 'nullable|date',
                'to_date' => 'nullable|date|after_or_equal:from_date',
                'status' => 'nullable|string|in:draft,hod_app,approved,rejected',
                'tender_id' => 'nullable|integer|exists:tender_entry,TE_Id',
                'vendor_id' => 'nullable|integer|exists:subledg_master,SL_Id',
                'department_id' => 'nullable|integer|exists:dept_master,Dpt_id',
                'work_allocation_id' => 'nullable|integer|exists:work_allocation_book,WA_Id',
            ]);

            
            $query = WorkBGTrans::with([
                'subledger:SL_Id,SL_Name,SL_Code',
                'workAllocation:WA_Id,WA_RefId,TE_Id,WO_Amount,WO_Date',
                'workAllocation.tender:TE_Id,TnT_ID,Dpt_id',
                'workAllocation.tender.department:Dpt_id,Dpt_Name',
                'hod:Usr_Id,Usr_Name'
            ]);

            if ($request->filled('from_date')) {
                $query->whereDate('WBT_Date', '>=', $request->from_date);
            }

            if ($request->filled('to_date')) {
                $query->whereDate('WBT_Date', '<=', $request->to_date);
            }

            if ($request->filled('status')) {
                $query->where('Status', $request->status);
            }

            if ($request->filled('vendor_id')) {
                $query->where('SL_Id', $request->vendor_id);
            }

            if ($request->filled('work_allocation_id')) {
                $query->where('WA_Id', $request->work_allocation_id);
            }

            if ($request->filled('tender_id')) {
                $query->whereHas('workAllocation', function ($q) use ($request) {
                    $q->where('TE_Id', $request->tender_id);
                });
            }

            if ($request->filled('department_id')) {
                $query->whereHas('workAllocation.tender', function ($q) use ($request) {
                    $q->where('Dpt_id', $request->department_id);
                });
            }

            $query->orderBy('WBT_Date', 'desc')
                  ->orderBy('created_at', 'desc');

            $bgEntries = $query->get();

            $departmentWiseData = $bgEntries->groupBy(function ($item) {
                return $item->workAllocation->tender->department->Dpt_id ?? 'no-department';
            })->map(function ($items, $deptId) {
                $firstItem = $items->first();
                $department = $firstItem->workAllocation->tender->department ?? null;

                $deptTotalBG = $items->sum('BG_Amt');

                $tenderWiseTotals = $items->groupBy('WA_Id')->map(function ($waItems) {
                    $waFirst = $waItems->first();
                    $tenderNo = $waFirst->workAllocation->tender->TnT_ID ?? null;
                    $workOrderNo = $waFirst->workAllocation->WA_RefId ?? null;

                    return [
                        'wa_id' => $waFirst->WA_Id,
                        'tender_no' => $tenderNo,
                        'work_order_no' => $workOrderNo,
                        'total_bg' => number_format($waItems->sum('BG_Amt'), 2),
                        'entry_count' => $waItems->count()
                    ];
                })->values();

                return [
                    'department' => [
                        'id' => $deptId === 'no-department' ? null : $department->Dpt_id,
                        'name' => $deptId === 'no-department' ? 'No Department Assigned' : ($department->Dpt_Name ?? 'Unknown'),
                    ],
                    'summary' => [
                        'total_records' => $items->count(),
                        'total_department_bg' => number_format($deptTotalBG, 2),
                        'tender_wise_totals' => $tenderWiseTotals,
                    ],
                    'entries' => $items->map(function ($entry) {
                        $maturityDate = null;
                        if ($entry->BG_Amt && $entry->WBT_Date) {
                            $maturityDate = date('Y-m-d', strtotime($entry->WBT_Date . ' +1 year'));
                        }

                        return [
                            'account_budget_head' => [
                                'id' => $entry->subledger->SL_Id ?? null,
                                'code' => $entry->subledger->SL_Code ?? null,
                                'name' => $entry->subledger->SL_Name ?? null,
                            ],
                            'tender_no' => $entry->workAllocation->tender->TnT_ID ?? null,
                            'work_order_no' => $entry->workAllocation->WA_RefId ?? null,
                            'work_order_date' => $entry->workAllocation->WO_Date ?? null,
                            'order_amount' => $entry->workAllocation->WO_Amount ? number_format($entry->workAllocation->WO_Amount, 2) : null,
                            'vendor_name' => $entry->subledger->SL_Name ?? null,
                            'bg_date' => $entry->WBT_Date,
                            'bg_amount' => $entry->BG_Amt ? number_format($entry->BG_Amt, 2) : '0.00',
                            'bg_type' => $entry->WBT_Type ?? 'Receipt',
                            'maturity_date' => $maturityDate,
                            'status' => $entry->Status,
                            'status_display' => $this->getStatusDisplay($entry->Status),
                            'reference_cd' => $entry->WBT_CD,
                            'reference_bill' => $entry->WBT_BillNo,
                            'hod_name' => $entry->hod->Usr_Name ?? null,
                            'hod_approval_date' => $entry->WBT_HDT,
                            'remarks' => $entry->Remarks,
                        ];
                    })->values()
                ];
            })->values();

            
            $grandTotalBG = $bgEntries->sum('BG_Amt');

            
            $reportData = [
                'report_name' => 'Departmentwise BG Register',
                'generated_date' => now()->format('Y-m-d H:i:s'),
                'filters_applied' => [
                    'from_date' => $request->from_date,
                    'to_date' => $request->to_date,
                    'status' => $request->status,
                    'tender_id' => $request->tender_id,
                    'vendor_id' => $request->vendor_id,
                    'department_id' => $request->department_id,
                    'work_allocation_id' => $request->work_allocation_id,
                ],
                'grand_summary' => [
                    'total_departments' => $departmentWiseData->count(),
                    'total_records' => $bgEntries->count(),
                    'grand_total_bg' => number_format($grandTotalBG, 2),
                ],
                'department_wise_data' => $departmentWiseData,
            ];

            return response()->json([
                'success' => true,
                'message' => 'Departmentwise BG Register report generated successfully',
                'data' => $reportData
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error generating Departmentwise BG Register report',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage(),
                'line' => $e->getLine(),
                'file' => $e->getFile()
            ], 500);
        }
    }

}
