<?php

namespace App\Http\Controllers;

use App\Models\TenderEntry;
use App\Models\DeptMaster;
use App\Models\FyearMaster;
use App\Models\BudgetEntry;
use App\Models\WorkAllocationBook;
use App\Models\SubLedgerMaster;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;




class TenderReportController extends Controller
{
    public function tenderRegister(Request $request)
{
    $validator = Validator::make($request->all(), [
        'dept_id' => 'nullable|integer|exists:dept_master,Dpt_id',
        'tender_type_id' => 'nullable|integer|exists:tender_type_master,TnT_ID',
        'from_date' => 'nullable|date',
        'to_date' => 'nullable|date',
        'status' => 'nullable|string|in:approved,pending,rejected',
        'min_amount' => 'nullable|numeric|min:0',
        'max_amount' => 'nullable|numeric|min:0',
        'sort_by' => 'nullable|string|in:tender_no,tender_date,tender_type,amount,status',
        'sort_order' => 'nullable|string|in:asc,desc',
        'page' => 'nullable|integer|min:1',
        'per_page' => 'nullable|integer|min:1|max:100'
    ]);

    if ($validator->fails()) {
        return response()->json([
            'success' => false,
            'message' => 'Validation error',
            'errors' => $validator->errors()
        ], 422);
    }

    try {
        $query = TenderEntry::with([
            'department',
            'ledger',
            'tendertype'
        ]);

        // Apply filters
        if ($request->filled('dept_id')) {
            $query->where('Dpt_id', $request->dept_id);
        }

        if ($request->filled('tender_type_id')) {
            $query->where('TnT_ID', $request->tender_type_id);
        }

        // Fixed: Changed Tnd_Date to TE_Date
        if ($request->filled('from_date')) {
            $query->whereDate('TE_Date', '>=', $request->from_date);
        }

        // Fixed: Changed Tnd_Date to TE_Date
        if ($request->filled('to_date')) {
            $query->whereDate('TE_Date', '<=', $request->to_date);
        }

        if ($request->filled('min_amount')) {
            $query->where('Proposal_Amount', '>=', $request->min_amount);
        }

        if ($request->filled('max_amount')) {
            $query->where('Proposal_Amount', '<=', $request->max_amount);
        }

        // Status filter (based on Approved_Amount)
        if ($request->filled('status')) {
            if ($request->status == 'approved') {
                $query->whereNotNull('Approved_Amount')->where('Approved_Amount', '>', 0);
            } elseif ($request->status == 'pending') {
                $query->whereNull('Approved_Amount');
            } elseif ($request->status == 'rejected') {
                $query->where('Approved_Amount', '<=', 0);
            }
        }

        // Sorting - Fixed: Changed Tnd_Date to TE_Date
        $sortField = $request->get('sort_by', 'tender_date');
        $sortOrder = $request->get('sort_order', 'desc');

        switch ($sortField) {
            case 'tender_no':
                $query->orderBy('TE_Id', $sortOrder);
                break;
            case 'tender_date':
                // Fixed: Changed Tnd_Date to TE_Date
                $query->orderBy('TE_Date', $sortOrder);
                break;
            case 'tender_type':
                $query->join('tender_type_master', 'tender_entry.TnT_ID', '=', 'tender_type_master.TnT_ID')
                    ->orderBy('tender_type_master.TnT_Name', $sortOrder)
                    ->select('tender_entry.*');
                break;
            case 'amount':
                $query->orderBy('Proposal_Amount', $sortOrder);
                break;
            case 'status':
                $query->orderBy('Approved_Amount', $sortOrder);
                break;
            default:
                // Fixed: Changed Tnd_Date to TE_Date for default sorting
                $query->orderBy('TE_Date', $sortOrder);
        }

        $perPage = $request->get('per_page', 15);
        $paginatedResults = $query->paginate($perPage);

        $reportData = [];
        $totalProposalAmount = 0;
        $totalApprovedAmount = 0;

        foreach ($paginatedResults as $tender) {
            $proposalAmount = (float) ($tender->Proposal_Amount ?? 0);
            $approvedAmount = (float) ($tender->Approved_Amount ?? 0);

            $totalProposalAmount += $proposalAmount;
            $totalApprovedAmount += $approvedAmount;

            // Determine tender status
            $tenderStatus = $this->getTenderStatus($tender);

            // Format document list
            $documents = [];
            if ($tender->Documents) {
                $docs = is_string($tender->Documents) ? json_decode($tender->Documents, true) : $tender->Documents;
                if (is_array($docs)) {
                    foreach ($docs as $doc) {
                        $documents[] = [
                            'name' => $doc['name'] ?? 'Document',
                            'url' => $doc['url'] ?? null,
                            'type' => $doc['type'] ?? $this->getFileExtension($doc['url'] ?? ''),
                        ];
                    }
                }
            }

            $reportData[] = [
                'tender_register_id' => $tender->TE_Id,
                'tender_no' => 'TND-' . str_pad($tender->TE_Id, 5, '0', STR_PAD_LEFT),
                'tender_id' => $tender->TE_Id,
                'tender_type_id' => $tender->TnT_ID,
                'tender_type_code' => $tender->tendertype->TnT_Code ?? null,
                'tender_type_name' => $tender->tendertype->TnT_Name ?? 'Unknown',
                // Fixed: Changed Tnd_Date to TE_Date
                'tender_date' => $tender->TE_Date,
                'formatted_tender_date' => $tender->TE_Date ? date('d-m-Y', strtotime($tender->TE_Date)) : null,
                'tender_start_date' => $tender->TE_St_Date, // Added start date
                'tender_end_date' => $tender->TE_Ed_Date, // Added end date
                'department_id' => $tender->Dpt_id,
                'department_name' => $tender->department->Dpt_Desc ?? $tender->department->Dpt_Name ?? 'Unknown',
                'ledger_id' => $tender->Lg_Id,
                'ledger_name' => $tender->ledger->Lg_Name ?? $tender->ledger->Lg_Eng_Name ?? 'Unknown',
                'description' => $tender->Description,
                'proposal_amount' => $proposalAmount,
                'formatted_proposal_amount' => '₹ ' . number_format($proposalAmount, 2),
                'approved_amount' => $approvedAmount,
                'formatted_approved_amount' => $approvedAmount > 0 ? '₹ ' . number_format($approvedAmount, 2) : 'Not Approved',
                'remaining_amount' => (float) ($tender->remaining_amount ?? ($proposalAmount - $approvedAmount)),
                'formatted_remaining_amount' => '₹ ' . number_format(($tender->remaining_amount ?? ($proposalAmount - $approvedAmount)), 2),
                'bg_percentage' => $tender->BG_Per,
                'emd_percentage' => $tender->EMD_Per,
                'tender_fee' => $tender->TE_Fee ? number_format($tender->TE_Fee, 2) : null,
                'tender_status' => $tenderStatus,
                'tender_status_code' => $this->getTenderStatusCode($tenderStatus),
                'document_count' => count($documents),
                'documents' => $documents,
                'created_at' => $tender->created_at ? $tender->created_at->format('d-m-Y H:i:s') : null,
                'updated_at' => $tender->updated_at ? $tender->updated_at->format('d-m-Y H:i:s') : null,
            ];
        }

        return response()->json([
            'success' => true,
            'message' => 'Tender register report retrieved successfully',
            'report_name' => 'Tender Register Report',
            'report_date' => now()->format('d-m-Y'),
            'filters_applied' => [
                'dept_id' => $request->dept_id,
                'tender_type_id' => $request->tender_type_id,
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'status' => $request->status,
                'min_amount' => $request->min_amount,
                'max_amount' => $request->max_amount,
            ],
            'summary' => [
                'total_tenders' => $paginatedResults->total(),
                'total_proposal_amount' => $totalProposalAmount,
                'formatted_total_proposal' => '₹ ' . number_format($totalProposalAmount, 2),
                'total_approved_amount' => $totalApprovedAmount,
                'formatted_total_approved' => '₹ ' . number_format($totalApprovedAmount, 2),
                'approved_tenders' => $reportData ? count(array_filter($reportData, function($item) {
                    return $item['tender_status_code'] == 'approved';
                })) : 0,
                'pending_tenders' => $reportData ? count(array_filter($reportData, function($item) {
                    return $item['tender_status_code'] == 'pending';
                })) : 0,
                'rejected_tenders' => $reportData ? count(array_filter($reportData, function($item) {
                    return $item['tender_status_code'] == 'rejected';
                })) : 0,
            ],
            'data' => $reportData,
            'pagination' => [
                'current_page' => $paginatedResults->currentPage(),
                'per_page' => $paginatedResults->perPage(),
                'total' => $paginatedResults->total(),
                'last_page' => $paginatedResults->lastPage(),
                'from' => $paginatedResults->firstItem(),
                'to' => $paginatedResults->lastItem()
            ]
        ]);

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

    private function getTenderStatus($tender)
    {
        if ($tender->Approved_Amount === null) {
            return 'Pending';
        } elseif ($tender->Approved_Amount > 0) {
            return 'Approved';
        } else {
            return 'Rejected';
        }
    }

    private function getTenderStatusCode($status)
    {
        $map = [
            'Pending' => 'pending',
            'Approved' => 'approved',
            'Rejected' => 'rejected'
        ];
        return $map[$status] ?? 'unknown';
    }

    private function getFileExtension($url)
    {
        if (!$url) return 'unknown';
        $path = parse_url($url, PHP_URL_PATH);
        $extension = pathinfo($path, PATHINFO_EXTENSION);
        return $extension ?: 'unknown';
    }

    public function tenderAllocationReport(Request $request)
    {
        $validator = Validator::make($request->all(), [
    'vendor_id' => 'nullable|integer|exists:subledg_master,SL_Id',
    'dept_id' => 'nullable|integer|exists:dept_master,Dpt_id',
    'tender_type_id' => 'nullable|integer|exists:tender_type_master,TnT_ID',
    'from_date' => 'nullable|date',
    'to_date' => 'nullable|date',
    'min_amount' => 'nullable|numeric|min:0',
    'max_amount' => 'nullable|numeric|min:0',
    'sort_by' => 'nullable|string|in:vendor_name,tender_no,tender_date,work_order_no,work_order_amount',
    'sort_order' => 'nullable|string|in:asc,desc',
    'page' => 'nullable|integer|min:1',
    'per_page' => 'nullable|integer|min:1|max:100'
]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'Validation error',
                'errors' => $validator->errors()
            ], 422);
        }

        try {
            $query = WorkAllocationBook::with([
                'subledger',
                'tender',
                'tender.tendertype',
                'tender.department'
            ]);

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

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

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

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

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

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

            if ($request->filled('min_amount')) {
                $query->where('WO_Amount', '>=', $request->min_amount);
            }

            if ($request->filled('max_amount')) {
                $query->where('WO_Amount', '<=', $request->max_amount);
            }

            // Sorting
            $sortField = $request->get('sort_by', 'WO_Date');
            $sortOrder = $request->get('sort_order', 'desc');

            switch ($sortField) {
                case 'tender_no':
                    $query->join('tender_entries', 'work_allocations.TE_Id', '=', 'tender_entries.TE_Id')
                        ->orderBy('tender_entries.TE_Id', $sortOrder)
                        ->select('work_allocations.*');
                    break;
                case 'tender_date':
                    $query->join('tender_entries', 'work_allocations.TE_Id', '=', 'tender_entries.TE_Id')
                        ->orderBy('tender_entries.Tnd_Date', $sortOrder)
                        ->select('work_allocations.*');
                    break;
                case 'vendor_name':
                    $query->join('subledg_master', 'work_allocations.SL_Id', '=', 'subledg_master.SL_Id')
                        ->orderBy('subledg_master.SL_Name', $sortOrder)
                        ->select('work_allocations.*');
                    break;
                case 'work_order_no':
                    $query->orderBy('WA_RefId', $sortOrder);
                    break;
                case 'work_order_amount':
                    $query->orderBy('WO_Amount', $sortOrder);
                    break;
                default:
                    $query->orderBy($sortField, $sortOrder);
            }

            $perPage = $request->get('per_page', 15);
            $paginatedResults = $query->paginate($perPage);

            $reportData = [];
            $totalProposedAmount = 0;
            $totalWorkOrderAmount = 0;

            foreach ($paginatedResults as $allocation) {
                $tender = $allocation->tender;
                $vendor = $allocation->subledger;
                $tenderType = $tender->tendertype ?? null;

                $proposedAmount = (float) ($tender->Proposal_Amount ?? 0);
                $workOrderAmount = (float) ($allocation->WO_Amount ?? 0);

                $totalProposedAmount += $proposedAmount;
                $totalWorkOrderAmount += $workOrderAmount;

                $reportData[] = [
                    'allocation_id' => $allocation->WA_Id,
                    'work_order_no' => $allocation->WA_RefId ?? 'WO-' . str_pad($allocation->WA_Id, 5, '0', STR_PAD_LEFT),
                    'work_order_date' => $allocation->WO_Date,
                    'formatted_work_order_date' => $allocation->WO_Date ? date('d-m-Y', strtotime($allocation->WO_Date)) : null,
                    'work_order_amount' => $workOrderAmount,
                    'formatted_work_order_amount' => '₹ ' . number_format($workOrderAmount, 2),

                    'tender_id' => $tender->TE_Id ?? null,
                    'tender_no' => $tender ? 'TND-' . str_pad($tender->TE_Id, 5, '0', STR_PAD_LEFT) : null,
                    'tender_type_id' => $tender->TnT_ID ?? null,
                    'tender_type_code' => $tenderType->TnT_Code ?? null,
                    'tender_type_name' => $tenderType->TnT_Name ?? 'Unknown',
                    'tender_date' => $tender->Tnd_Date ?? null,
                    'formatted_tender_date' => $tender && $tender->Tnd_Date ? date('d-m-Y', strtotime($tender->Tnd_Date)) : null,
                    'tender_proposed_amount' => $proposedAmount,
                    'formatted_tender_proposed_amount' => '₹ ' . number_format($proposedAmount, 2),
                    'tender_approved_amount' => $tender ? (float) ($tender->Approved_Amount ?? 0) : 0,
                    'formatted_tender_approved_amount' => $tender && $tender->Approved_Amount ? '₹ ' . number_format($tender->Approved_Amount, 2) : 'N/A',
                    'tender_description' => $tender->Description ?? null,

                    'vendor_id' => $vendor->SL_Id ?? null,
                    'vendor_code' => $vendor->SL_Code ?? null,
                    'vendor_name' => $vendor->SL_Name ?? 'Unknown',
                    'vendor_contact' => $vendor->SL_Cont_Pers ?? null,
                    'vendor_phone' => $vendor->SL_Phone ?? null,
                    'vendor_email' => $vendor->SL_Email ?? null,
                    'vendor_pan' => $vendor->PAN_Number ?? null,
                    'vendor_gstin' => $vendor->GSTIN ?? null,

                    'department_id' => $tender->Dpt_id ?? null,
                    'department_name' => $tender->department->Dpt_Desc ?? $tender->department->Dpt_Name ?? null,
                    'ledger_id' => $tender->Lg_Id ?? null,

                    'allocation_percentage' => $proposedAmount > 0 ? round(($workOrderAmount / $proposedAmount) * 100, 2) : 0,
                    'created_at' => $allocation->created_at ? $allocation->created_at->format('d-m-Y H:i:s') : null,
                    'updated_at' => $allocation->updated_at ? $allocation->updated_at->format('d-m-Y H:i:s') : null,
                ];
            }

            return response()->json([
                'success' => true,
                'message' => 'Tender allocation report retrieved successfully',
                'report_name' => 'Tender Allocation Report',
                'report_date' => now()->format('d-m-Y'),
                'filters_applied' => [
                    'dept_id' => $request->dept_id,
                    'tender_type_id' => $request->tender_type_id,
                    'vendor_id' => $request->vendor_id,
                    'tender_id' => $request->tender_id,
                    'from_date' => $request->from_date,
                    'to_date' => $request->to_date,
                    'min_amount' => $request->min_amount,
                    'max_amount' => $request->max_amount,
                ],
                'summary' => [
                    'total_allocations' => $paginatedResults->total(),
                    'total_proposed_amount' => $totalProposedAmount,
                    'formatted_total_proposed' => '₹ ' . number_format($totalProposedAmount, 2),
                    'total_work_order_amount' => $totalWorkOrderAmount,
                    'formatted_total_work_order' => '₹ ' . number_format($totalWorkOrderAmount, 2),
                    'total_vendors' => $reportData ? count(array_unique(array_column($reportData, 'vendor_id'))) : 0,
                    'total_tenders' => $reportData ? count(array_unique(array_column($reportData, 'tender_id'))) : 0,
                ],
                'data' => $reportData,
                'pagination' => [
                    'current_page' => $paginatedResults->currentPage(),
                    'per_page' => $paginatedResults->perPage(),
                    'total' => $paginatedResults->total(),
                    'last_page' => $paginatedResults->lastPage(),
                    'from' => $paginatedResults->firstItem(),
                    'to' => $paginatedResults->lastItem()
                ]
            ]);

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


  public function vendorwiseTenderRegister(Request $request)
{
    $validator = Validator::make($request->all(), [
        'vendor_id' => 'nullable|integer|exists:subledg_master,SL_Id',  // ✅ Fixed table name
        'dept_id' => 'nullable|integer|exists:dept_master,Dpt_id',
        'tender_type_id' => 'nullable|integer|exists:tender_type_master,TnT_ID',
        'from_date' => 'nullable|date',
        'to_date' => 'nullable|date',
        'min_amount' => 'nullable|numeric|min:0',
        'max_amount' => 'nullable|numeric|min:0',
        'sort_by' => 'nullable|string|in:vendor_name,tender_no,tender_date,work_order_no,work_order_amount',
        'sort_order' => 'nullable|string|in:asc,desc',
        'page' => 'nullable|integer|min:1',
        'per_page' => 'nullable|integer|min:1|max:100'
    ]);

    if ($validator->fails()) {
        return response()->json([
            'success' => false,
            'message' => 'Validation error',
            'errors' => $validator->errors()
        ], 422);
    }

    try {
        $query = WorkAllocationBook::with([
            'subledger',
            'tender',
            'tender.tendertype',
            'tender.department'
        ]);

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

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

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

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

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

        if ($request->filled('min_amount')) {
            $query->where('WO_Amount', '>=', $request->min_amount);
        }

        if ($request->filled('max_amount')) {
            $query->where('WO_Amount', '<=', $request->max_amount);
        }

        // Get all results first for vendor-wise grouping
        $allocations = $query->get();

        if ($allocations->isEmpty()) {
            return response()->json([
                'success' => true,
                'message' => 'No vendor allocations found',
                'report_name' => 'Vendorwise Tender Register',
                'report_date' => now()->format('d-m-Y'),
                'filters_applied' => [
                    'vendor_id' => $request->vendor_id,
                    'dept_id' => $request->dept_id,
                    'tender_type_id' => $request->tender_type_id,
                    'from_date' => $request->from_date,
                    'to_date' => $request->to_date,
                    'min_amount' => $request->min_amount,
                    'max_amount' => $request->max_amount,
                ],
                'data' => [],
                'summary' => [
                    'total_vendors' => 0,
                    'total_allocations' => 0,
                    'total_tender_proposed' => 0,
                    'total_work_order_amount' => 0,
                ]
            ]);
        }

        // Group by vendor
        $groupedByVendor = $allocations->groupBy('SL_Id');

        $reportData = [];
        $totalVendors = 0;
        $totalAllocations = 0;
        $grandTotalProposed = 0;
        $grandTotalWorkOrder = 0;

        foreach ($groupedByVendor as $vendorId => $vendorAllocations) {
            $vendor = $vendorAllocations->first()->subledger;
            $vendorName = $vendor->SL_Name ?? 'Unknown Vendor';

            $vendorTotalProposed = 0;
            $vendorTotalWorkOrder = 0;
            $vendorAllocationCount = $vendorAllocations->count();

            $tenderDetails = [];

            foreach ($vendorAllocations as $allocation) {
                $tender = $allocation->tender;
                $tenderType = $tender->tendertype ?? null;

                $proposedAmount = (float) ($tender->Proposal_Amount ?? 0);
                $workOrderAmount = (float) ($allocation->WO_Amount ?? 0);

                $vendorTotalProposed += $proposedAmount;
                $vendorTotalWorkOrder += $workOrderAmount;

                $tenderDetails[] = [
                    'tender_no' => $tender ? 'TND-' . str_pad($tender->TE_Id, 5, '0', STR_PAD_LEFT) : null,
                    'tender_id' => $tender->TE_Id ?? null,
                    'tender_type' => $tenderType->TnT_Name ?? 'Unknown',
                    'tender_type_code' => $tenderType->TnT_Code ?? null,
                    'tender_date' => $tender->Tnd_Date ?? null,
                    'formatted_tender_date' => $tender && $tender->Tnd_Date ? date('d-m-Y', strtotime($tender->Tnd_Date)) : null,
                    'tender_proposed_amount' => $proposedAmount,
                    'formatted_tender_proposed_amount' => '₹ ' . number_format($proposedAmount, 2),
                    'tender_approved_amount' => $tender ? (float) ($tender->Approved_Amount ?? 0) : 0,
                    'formatted_tender_approved_amount' => $tender && $tender->Approved_Amount ? '₹ ' . number_format($tender->Approved_Amount, 2) : 'N/A',
                    'tender_description' => $tender->Description ?? null,
                    'department_name' => $tender->department->Dpt_Desc ?? $tender->department->Dpt_Name ?? null,

                    'work_order_no' => $allocation->WA_RefId ?? 'WO-' . str_pad($allocation->WA_Id, 5, '0', STR_PAD_LEFT),
                    'work_order_id' => $allocation->WA_Id,
                    'work_order_date' => $allocation->WO_Date,
                    'formatted_work_order_date' => $allocation->WO_Date ? date('d-m-Y', strtotime($allocation->WO_Date)) : null,
                    'work_order_amount' => $workOrderAmount,
                    'formatted_work_order_amount' => '₹ ' . number_format($workOrderAmount, 2),
                    'allocation_percentage' => $proposedAmount > 0 ? round(($workOrderAmount / $proposedAmount) * 100, 2) : 0,
                ];
            }

            // Sort tender details if requested
            if ($request->filled('sort_by') && $request->sort_by != 'vendor_name') {
                $sortField = $request->sort_by;
                $sortOrder = $request->sort_order ?? 'asc';

                usort($tenderDetails, function($a, $b) use ($sortField, $sortOrder) {
                    if ($sortField == 'tender_no') {
                        $result = strcmp($a['tender_no'] ?? '', $b['tender_no'] ?? '');
                    } elseif ($sortField == 'tender_date') {
                        $result = strcmp($a['tender_date'] ?? '', $b['tender_date'] ?? '');
                    } elseif ($sortField == 'work_order_no') {
                        $result = strcmp($a['work_order_no'] ?? '', $b['work_order_no'] ?? '');
                    } elseif ($sortField == 'work_order_amount') {
                        $result = ($a['work_order_amount'] ?? 0) <=> ($b['work_order_amount'] ?? 0);
                    } else {
                        $result = 0;
                    }
                    return $sortOrder == 'desc' ? -$result : $result;
                });
            }

            $reportData[] = [
                'vendor_id' => $vendorId,
                'vendor_name' => $vendorName,
                'vendor_code' => $vendor->SL_Code ?? null,
                'vendor_contact' => $vendor->SL_Cont_Pers ?? null,
                'vendor_phone' => $vendor->SL_Phone ?? null,
                'vendor_email' => $vendor->SL_Email ?? null,
                'vendor_pan' => $vendor->PAN_Number ?? null,
                'vendor_gstin' => $vendor->GSTIN ?? null,
                'total_work_orders' => $vendorAllocationCount,
                'total_tender_proposed' => $vendorTotalProposed,
                'formatted_total_tender_proposed' => '₹ ' . number_format($vendorTotalProposed, 2),
                'total_work_order_amount' => $vendorTotalWorkOrder,
                'formatted_total_work_order_amount' => '₹ ' . number_format($vendorTotalWorkOrder, 2),
                'allocation_ratio' => $vendorTotalProposed > 0 ? round(($vendorTotalWorkOrder / $vendorTotalProposed) * 100, 2) : 0,
                'tenders' => $tenderDetails,
            ];

            $totalVendors++;
            $totalAllocations += $vendorAllocationCount;
            $grandTotalProposed += $vendorTotalProposed;
            $grandTotalWorkOrder += $vendorTotalWorkOrder;
        }

        // Sort vendors if requested by vendor_name
        if ($request->filled('sort_by') && $request->sort_by == 'vendor_name') {
            $sortOrder = $request->sort_order ?? 'asc';
            usort($reportData, function($a, $b) use ($sortOrder) {
                $result = strcmp($a['vendor_name'], $b['vendor_name']);
                return $sortOrder == 'desc' ? -$result : $result;
            });
        }

        // Apply pagination
        $page = $request->page ?? 1;
        $perPage = $request->per_page ?? 15;
        $totalRecords = count($reportData);
        $offset = ($page - 1) * $perPage;
        $paginatedData = array_slice($reportData, $offset, $perPage);

        return response()->json([
            'success' => true,
            'message' => 'Vendorwise tender register retrieved successfully',
            'report_name' => 'Vendorwise Tender Register',
            'report_date' => now()->format('d-m-Y'),
            'filters_applied' => [
                'vendor_id' => $request->vendor_id,
                'dept_id' => $request->dept_id,
                'tender_type_id' => $request->tender_type_id,
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'min_amount' => $request->min_amount,
                'max_amount' => $request->max_amount,
            ],
            'summary' => [
                'total_vendors' => $totalVendors,
                'total_allocations' => $totalAllocations,
                'total_tender_proposed' => $grandTotalProposed,
                'formatted_total_tender_proposed' => '₹ ' . number_format($grandTotalProposed, 2),
                'total_work_order_amount' => $grandTotalWorkOrder,
                'formatted_total_work_order_amount' => '₹ ' . number_format($grandTotalWorkOrder, 2),
                'overall_allocation_percentage' => $grandTotalProposed > 0 ? round(($grandTotalWorkOrder / $grandTotalProposed) * 100, 2) : 0,
            ],
            'data' => $paginatedData,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $totalRecords,
                'last_page' => ceil($totalRecords / $perPage),
                'from' => $offset + 1,
                'to' => min($offset + $perPage, $totalRecords)
            ]
        ]);

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

    }


    public function overBudgetedWorkOrders(Request $request)
{
    $validator = Validator::make($request->all(), [
        'dept_id' => 'nullable|integer|exists:dept_master,Dpt_id',
        'fin_year' => 'nullable|integer|exists:fyear_master,FY_Id',
        'tender_type_id' => 'nullable|integer|exists:tender_type_master,TnT_ID',
        'min_over_budget' => 'nullable|numeric|min:0',
        'from_date' => 'nullable|date',
        'to_date' => 'nullable|date',
        'sort_by' => 'nullable|string|in:budget_head,budget_amount,budget_used,tender_amount,over_budget_value,over_budget_percentage',
        'sort_order' => 'nullable|string|in:asc,desc',
        'page' => 'nullable|integer|min:1',
        'per_page' => 'nullable|integer|min:1|max:100'
    ]);

    if ($validator->fails()) {
        return response()->json([
            'success' => false,
            'message' => 'Validation error',
            'errors' => $validator->errors()
        ], 422);
    }

    try {
        $finYear = $request->fin_year ?? date('Y');

        // Get all budget entries with their ledger details
        $budgetQuery = BudgetEntry::with(['ledger', 'department'])
            ->whereIn('Status', ['A', 'R', 'RR'])
            ->where('Is_Latest', true);

        if ($request->filled('dept_id')) {
            $budgetQuery->where('Dpt_Id', $request->dept_id);
        }

        if ($request->filled('fin_year')) {
            $budgetQuery->where('Fin_Year', $request->fin_year);
        }

        $budgetEntries = $budgetQuery->get();

        if ($budgetEntries->isEmpty()) {
            return response()->json([
                'success' => true,
                'message' => 'No budget entries found',
                'report_name' => 'Over Budgeted Work Orders Report',
                'report_date' => now()->format('d-m-Y'),
                'filters_applied' => [
                    'dept_id' => $request->dept_id,
                    'fin_year' => $request->fin_year,
                    'tender_type_id' => $request->tender_type_id,
                    'min_over_budget' => $request->min_over_budget,
                    'from_date' => $request->from_date,
                    'to_date' => $request->to_date,
                ],
                'data' => [],
                'summary' => [
                    'total_over_budget_items' => 0,
                    'total_budget_amount' => 0,
                    'total_budget_used' => 0,
                    'total_tender_amount' => 0,
                    'total_over_budget_value' => 0,
                ]
            ]);
        }

        $reportData = [];
        $totalBudgetAmount = 0;
        $totalBudgetUsed = 0;
        $totalTenderAmount = 0;
        $totalOverBudgetValue = 0;

        foreach ($budgetEntries as $budget) {
            $ledger = $budget->ledger;
            $budgetAmount = (float) $budget->Amount;

            // Get all work orders for this budget (by department and ledger)
            $workOrdersQuery = WorkAllocationBook::with(['tender'])
                ->whereHas('tender', function($q) use ($budget) {
                    $q->where('Dpt_id', $budget->Dpt_Id)
                      ->where('Lg_Id', $budget->Lg_Id);
                });

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

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

            if ($request->filled('tender_type_id')) {
                $workOrdersQuery->whereHas('tender', function($q) use ($request) {
                    $q->where('TnT_ID', $request->tender_type_id);
                });
            }

            $workOrders = $workOrdersQuery->get();

            $totalWorkOrderAmount = $workOrders->sum('WO_Amount');
            $budgetUsed = $totalWorkOrderAmount;
            $overBudgetValue = $budgetUsed - $budgetAmount;

            // Only include if over budget
            if ($overBudgetValue > 0) {
                $overBudgetPercentage = $budgetAmount > 0 ? round(($overBudgetValue / $budgetAmount) * 100, 2) : 0;

                // Apply min over budget filter
                if ($request->filled('min_over_budget') && $overBudgetValue < $request->min_over_budget) {
                    continue;
                }

                $tenderDetails = [];
                foreach ($workOrders as $wo) {
                    $tender = $wo->tender;
                    $tenderDetails[] = [
                        'tender_id' => $tender->TE_Id ?? null,
                        'tender_no' => $tender ? 'TND-' . str_pad($tender->TE_Id, 5, '0', STR_PAD_LEFT) : null,
                        'tender_type' => $tender->tendertype->TnT_Name ?? null,
                        'tender_date' => $tender->Tnd_Date ?? null,
                        'formatted_tender_date' => $tender && $tender->Tnd_Date ? date('d-m-Y', strtotime($tender->Tnd_Date)) : null,
                        'work_order_no' => $wo->WA_RefId ?? 'WO-' . str_pad($wo->WA_Id, 5, '0', STR_PAD_LEFT),
                        'work_order_date' => $wo->WO_Date,
                        'formatted_work_order_date' => $wo->WO_Date ? date('d-m-Y', strtotime($wo->WO_Date)) : null,
                        'work_order_amount' => (float) $wo->WO_Amount,
                        'formatted_work_order_amount' => '₹ ' . number_format($wo->WO_Amount, 2),
                    ];
                }

                $reportData[] = [
                    'budget_id' => $budget->Budg_Id,
                    'budget_head' => ($ledger->Lg_Code ? $ledger->Lg_Code . ' - ' : '') . ($ledger->Lg_Name ?? $ledger->Lg_Eng_Name ?? 'Unknown'),
                    'ledger_id' => $budget->Lg_Id,
                    'ledger_code' => $ledger->Lg_Code ?? '',
                    'ledger_name' => $ledger->Lg_Name ?? $ledger->Lg_Eng_Name ?? 'Unknown',
                    'department_id' => $budget->Dpt_Id,
                    'department_name' => $budget->department->Dpt_Desc ?? $budget->department->Dpt_Name ?? 'Unknown',
                    'financial_year' => $budget->Fin_Year,
                    'budget_amount' => $budgetAmount,
                    'formatted_budget_amount' => '₹ ' . number_format($budgetAmount, 2),
                    'budget_used' => $budgetUsed,
                    'formatted_budget_used' => '₹ ' . number_format($budgetUsed, 2),
                    'total_tender_amount' => $totalWorkOrderAmount,
                    'formatted_total_tender_amount' => '₹ ' . number_format($totalWorkOrderAmount, 2),
                    'over_budget_value' => $overBudgetValue,
                    'formatted_over_budget_value' => '₹ ' . number_format($overBudgetValue, 2),
                    'over_budget_percentage' => $overBudgetPercentage,
                    'work_order_count' => $workOrders->count(),
                    'work_orders' => $tenderDetails,
                    'budget_status' => $budget->Status,
                    'budget_status_text' => $budget->Status == 'A' ? 'Approved' : ($budget->Status == 'R' ? 'Revised' : 'Re-Revised'),
                ];

                $totalBudgetAmount += $budgetAmount;
                $totalBudgetUsed += $budgetUsed;
                $totalTenderAmount += $totalWorkOrderAmount;
                $totalOverBudgetValue += $overBudgetValue;
            }
        }

        if (empty($reportData)) {
            return response()->json([
                'success' => true,
                'message' => 'No over-budgeted work orders found',
                'report_name' => 'Over Budgeted Work Orders Report',
                'report_date' => now()->format('d-m-Y'),
                'filters_applied' => [
                    'dept_id' => $request->dept_id,
                    'fin_year' => $request->fin_year,
                    'tender_type_id' => $request->tender_type_id,
                    'min_over_budget' => $request->min_over_budget,
                    'from_date' => $request->from_date,
                    'to_date' => $request->to_date,
                ],
                'data' => [],
                'summary' => [
                    'total_over_budget_items' => 0,
                    'total_budget_amount' => 0,
                    'total_budget_used' => 0,
                    'total_tender_amount' => 0,
                    'total_over_budget_value' => 0,
                ]
            ]);
        }

        // Apply sorting
        if ($request->filled('sort_by')) {
            $sortField = $request->sort_by;
            $sortOrder = $request->sort_order ?? 'asc';

            usort($reportData, function($a, $b) use ($sortField, $sortOrder) {
                if ($sortField == 'budget_head') {
                    $result = strcmp($a['budget_head'], $b['budget_head']);
                } elseif ($sortField == 'budget_amount') {
                    $result = $a['budget_amount'] <=> $b['budget_amount'];
                } elseif ($sortField == 'budget_used') {
                    $result = $a['budget_used'] <=> $b['budget_used'];
                } elseif ($sortField == 'tender_amount') {
                    $result = $a['total_tender_amount'] <=> $b['total_tender_amount'];
                } elseif ($sortField == 'over_budget_value') {
                    $result = $a['over_budget_value'] <=> $b['over_budget_value'];
                } elseif ($sortField == 'over_budget_percentage') {
                    $result = $a['over_budget_percentage'] <=> $b['over_budget_percentage'];
                } else {
                    $result = 0;
                }
                return $sortOrder == 'desc' ? -$result : $result;
            });
        }

        // Apply pagination
        $page = $request->page ?? 1;
        $perPage = $request->per_page ?? 15;
        $totalRecords = count($reportData);
        $offset = ($page - 1) * $perPage;
        $paginatedData = array_slice($reportData, $offset, $perPage);

        return response()->json([
            'success' => true,
            'message' => 'Over budgeted work orders report retrieved successfully',
            'report_name' => 'Over Budgeted Work Orders Report',
            'report_date' => now()->format('d-m-Y'),
            'filters_applied' => [
                'dept_id' => $request->dept_id,
                'fin_year' => $request->fin_year,
                'tender_type_id' => $request->tender_type_id,
                'min_over_budget' => $request->min_over_budget,
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
            ],
            'summary' => [
                'total_over_budget_items' => count($reportData),
                'total_budget_amount' => $totalBudgetAmount,
                'formatted_total_budget_amount' => '₹ ' . number_format($totalBudgetAmount, 2),
                'total_budget_used' => $totalBudgetUsed,
                'formatted_total_budget_used' => '₹ ' . number_format($totalBudgetUsed, 2),
                'total_tender_amount' => $totalTenderAmount,
                'formatted_total_tender_amount' => '₹ ' . number_format($totalTenderAmount, 2),
                'total_over_budget_value' => $totalOverBudgetValue,
                'formatted_total_over_budget_value' => '₹ ' . number_format($totalOverBudgetValue, 2),
                'average_over_budget_percentage' => $totalBudgetAmount > 0 ? round(($totalOverBudgetValue / $totalBudgetAmount) * 100, 2) : 0,
            ],
            'data' => $paginatedData,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $totalRecords,
                'last_page' => ceil($totalRecords / $perPage),
                'from' => $offset + 1,
                'to' => min($offset + $perPage, $totalRecords)
            ]
        ]);

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

public function nonAllottedTenders(Request $request)
{
    $validator = Validator::make($request->all(), [
        'dept_id' => 'nullable|integer|exists:dept_master,Dpt_id',
        'tender_type_id' => 'nullable|integer|exists:tender_type_master,TnT_ID',
        'from_date' => 'nullable|date',
        'to_date' => 'nullable|date',
        'min_amount' => 'nullable|numeric|min:0',
        'max_amount' => 'nullable|numeric|min:0',
        'status' => 'nullable|string|in:pending,approved,rejected,allotted',
        'include_expired' => 'nullable|boolean',
        'sort_by' => 'nullable|string|in:budget_head,tender_no,tender_date,amount_proposed,status',
        'sort_order' => 'nullable|string|in:asc,desc',
        'page' => 'nullable|integer|min:1',
        'per_page' => 'nullable|integer|min:1|max:100'
    ]);

    if ($validator->fails()) {
        return response()->json([
            'success' => false,
            'message' => 'Validation error',
            'errors' => $validator->errors()
        ], 422);
    }

    try {
        // Get all tenders that are not allotted or are pending
        $query = TenderEntry::with([
            'department',
            'ledger',
            'tendertype'
        ]);

        // Get IDs of tenders that have work allocations (allotted)
        $allottedTenderIds = WorkAllocationBook::pluck('TE_Id')->unique()->toArray();

        // Filter for non-allotted tenders
        if (!empty($allottedTenderIds)) {
            $query->whereNotIn('TE_Id', $allottedTenderIds);
        }

        // Apply status filter based on Approved_Amount
        if ($request->filled('status')) {
            if ($request->status == 'pending') {
                $query->whereNull('Approved_Amount');
            } elseif ($request->status == 'approved') {
                $query->whereNotNull('Approved_Amount')->where('Approved_Amount', '>', 0);
            } elseif ($request->status == 'rejected') {
                $query->where('Approved_Amount', '<=', 0);
            }
            // 'allotted' status is handled by the whereNotIn clause above
        }

        // Apply department filter
        if ($request->filled('dept_id')) {
            $query->where('Dpt_id', $request->dept_id);
        }

        // Apply tender type filter
        if ($request->filled('tender_type_id')) {
            $query->where('TnT_ID', $request->tender_type_id);
        }

        // Apply date range filter - FIXED: Changed Tnd_Date to TE_Date
        if ($request->filled('from_date')) {
            $query->whereDate('TE_Date', '>=', $request->from_date);
        }

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

        // Apply amount filters
        if ($request->filled('min_amount')) {
            $query->where('Proposal_Amount', '>=', $request->min_amount);
        }

        if ($request->filled('max_amount')) {
            $query->where('Proposal_Amount', '<=', $request->max_amount);
        }

        // Include expired tenders option - FIXED: Changed Tnd_Date to TE_Date
        if (!$request->boolean('include_expired')) {
            // Exclude tenders older than 1 year
            $oneYearAgo = now()->subYear()->format('Y-m-d');
            $query->whereDate('TE_Date', '>=', $oneYearAgo);
        }

        // Sorting - FIXED: Changed Tnd_Date to TE_Date
        $sortField = $request->get('sort_by', 'tender_date');
        $sortOrder = $request->get('sort_order', 'desc');

        switch ($sortField) {
            case 'budget_head':
                $query->leftJoin('ledger_master', 'tender_entry.Lg_Id', '=', 'ledger_master.Lg_Id')
                    ->orderBy('ledger_master.Lg_Name', $sortOrder)
                    ->select('tender_entry.*');
                break;
            case 'tender_no':
                $query->orderBy('TE_Id', $sortOrder);
                break;
            case 'tender_date':
                $query->orderBy('TE_Date', $sortOrder);
                break;
            case 'amount_proposed':
                $query->orderBy('Proposal_Amount', $sortOrder);
                break;
            case 'status':
                $query->orderBy('Approved_Amount', $sortOrder);
                break;
            default:
                $query->orderBy('TE_Date', $sortOrder);
        }

        $perPage = $request->get('per_page', 15);
        $paginatedResults = $query->paginate($perPage);

        $reportData = [];
        $totalProposedAmount = 0;
        $totalApprovedAmount = 0;

        foreach ($paginatedResults as $tender) {
            $ledger = $tender->ledger;
            $budgetHead = ($ledger->Lg_Code ? $ledger->Lg_Code . ' - ' : '') . ($ledger->Lg_Name ?? $ledger->Lg_Eng_Name ?? 'Unknown');

            $proposedAmount = (float) ($tender->Proposal_Amount ?? 0);
            $approvedAmount = (float) ($tender->Approved_Amount ?? 0);

            $totalProposedAmount += $proposedAmount;
            $totalApprovedAmount += $approvedAmount;

            // Determine tender status
            $tenderStatus = $this->getTenderAllotmentStatus($tender);

            $reportData[] = [
                'tender_id' => $tender->TE_Id,
                'tender_no' => 'TND-' . str_pad($tender->TE_Id, 5, '0', STR_PAD_LEFT),
                'budget_head' => $budgetHead,
                'ledger_id' => $tender->Lg_Id,
                'ledger_code' => $ledger->Lg_Code ?? '',
                'ledger_name' => $ledger->Lg_Name ?? $ledger->Lg_Eng_Name ?? 'Unknown',
                'account_head' => $budgetHead,
                'department_id' => $tender->Dpt_id,
                'department_name' => $tender->department->Dpt_Desc ?? $tender->department->Dpt_Name ?? 'Unknown',
                'tender_type_id' => $tender->TnT_ID,
                'tender_type' => $tender->tendertype->TnT_Name ?? 'Unknown',
                'tender_type_code' => $tender->tendertype->TnT_Code ?? null,
                'tender_date' => $tender->TE_Date, // FIXED: Changed to TE_Date
                'formatted_tender_date' => $tender->TE_Date ? date('d-m-Y', strtotime($tender->TE_Date)) : null,
                'amount_proposed' => $proposedAmount,
                'formatted_amount_proposed' => '₹ ' . number_format($proposedAmount, 2),
                'amount_approved' => $approvedAmount,
                'formatted_amount_approved' => $approvedAmount > 0 ? '₹ ' . number_format($approvedAmount, 2) : 'N/A',
                'status' => $tenderStatus,
                'status_code' => $this->getTenderStatusCode($tenderStatus),
                'description' => $tender->Description,
                'days_pending' => $tender->TE_Date ? now()->diffInDays(now()->parse($tender->TE_Date)) : 0, // FIXED: Changed to TE_Date
                'document_count' => $tender->Documents ? count(is_string($tender->Documents) ? json_decode($tender->Documents, true) : ($tender->Documents ?? [])) : 0,
                'created_at' => $tender->created_at ? $tender->created_at->format('d-m-Y H:i:s') : null,
                'updated_at' => $tender->updated_at ? $tender->updated_at->format('d-m-Y H:i:s') : null,
            ];
        }

        return response()->json([
            'success' => true,
            'message' => 'Non-allotted / pending tenders report retrieved successfully',
            'report_name' => 'Non-Allotted / Pending Tenders Report',
            'report_date' => now()->format('d-m-Y'),
            'filters_applied' => [
                'dept_id' => $request->dept_id,
                'tender_type_id' => $request->tender_type_id,
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'min_amount' => $request->min_amount,
                'max_amount' => $request->max_amount,
                'status' => $request->status,
                'include_expired' => $request->boolean('include_expired'),
            ],
            'summary' => [
                'total_tenders' => $paginatedResults->total(),
                'total_proposed_amount' => $totalProposedAmount,
                'formatted_total_proposed' => '₹ ' . number_format($totalProposedAmount, 2),
                'total_approved_amount' => $totalApprovedAmount,
                'formatted_total_approved' => '₹ ' . number_format($totalApprovedAmount, 2),
                'average_tender_amount' => $paginatedResults->total() > 0 ? round($totalProposedAmount / $paginatedResults->total(), 2) : 0,
                'formatted_average_amount' => '₹ ' . number_format($paginatedResults->total() > 0 ? round($totalProposedAmount / $paginatedResults->total(), 2) : 0, 2),
            ],
            'data' => $reportData,
            'pagination' => [
                'current_page' => $paginatedResults->currentPage(),
                'per_page' => $paginatedResults->perPage(),
                'total' => $paginatedResults->total(),
                'last_page' => $paginatedResults->lastPage(),
                'from' => $paginatedResults->firstItem(),
                'to' => $paginatedResults->lastItem()
            ]
        ]);

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

private function getTenderAllotmentStatus($tender)
{
    // Check if tender has any work allocations
    $hasAllocation = WorkAllocationBook::where('TE_Id', $tender->TE_Id)->exists();

    if ($hasAllocation) {
        return 'Allotted';
    } elseif ($tender->Approved_Amount === null) {
        return 'Pending';
    } elseif ($tender->Approved_Amount > 0) {
        return 'Approved';
    } elseif ($tender->Approved_Amount <= 0) {
        return 'Rejected';
    } else {
        return 'Unknown';
    }
}




public function departmentwiseActiveWorkOrders(Request $request)
{
    $validator = Validator::make($request->all(), [
        'dept_id' => 'nullable|integer|exists:dept_master,Dpt_id',
        'vendor_id' => 'nullable|integer|exists:subledg_master,SL_Id',
        'from_date' => 'nullable|date',
        'to_date' => 'nullable|date',
        'min_amount' => 'nullable|numeric|min:0',
        'max_amount' => 'nullable|numeric|min:0',
        'status' => 'nullable|string|in:active,completed,cancelled,pending',
        'include_completed' => 'nullable|boolean',
        'sort_by' => 'nullable|string|in:work_order_no,date,vendor_name,work_order_amount,billed_amount,balance_amount,status',
        'sort_order' => 'nullable|string|in:asc,desc',
        'page' => 'nullable|integer|min:1',
        'per_page' => 'nullable|integer|min:1|max:100'
    ]);

    if ($validator->fails()) {
        return response()->json([
            'success' => false,
            'message' => 'Validation error',
            'errors' => $validator->errors()
        ], 422);
    }

    try {
        $query = WorkAllocationBook::with([
            'subledger',
            'tender',
            'tender.department',
            'tender.tendertype'
        ]);

        // Filter by department
        if ($request->filled('dept_id')) {
            $query->whereHas('tender', function($q) use ($request) {
                $q->where('Dpt_id', $request->dept_id);
            });
        }

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

        // Date range filter
        if ($request->filled('from_date')) {
            $query->whereDate('WO_Date', '>=', $request->from_date);
        }

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

        // Amount range filter
        if ($request->filled('min_amount')) {
            $query->where('WO_Amount', '>=', $request->min_amount);
        }

        if ($request->filled('max_amount')) {
            $query->where('WO_Amount', '<=', $request->max_amount);
        }

        // Get all work orders first for processing
        $workOrders = $query->get();

        if ($workOrders->isEmpty()) {
            return response()->json([
                'success' => true,
                'message' => 'No work orders found',
                'report_name' => 'Departmentwise Active Work Orders Report',
                'report_date' => now()->format('d-m-Y'),
                'filters_applied' => [
                    'dept_id' => $request->dept_id,
                    'vendor_id' => $request->vendor_id,
                    'from_date' => $request->from_date,
                    'to_date' => $request->to_date,
                    'min_amount' => $request->min_amount,
                    'max_amount' => $request->max_amount,
                    'status' => $request->status,
                    'include_completed' => $request->boolean('include_completed'),
                ],
                'data' => [],
                'summary' => [
                    'total_departments' => 0,
                    'total_work_orders' => 0,
                    'total_work_order_amount' => 0,
                    'total_billed_amount' => 0,
                    'total_balance_amount' => 0,
                ]
            ]);
        }

        // Group by department
        $groupedByDept = $workOrders->groupBy(function($item) {
            return $item->tender->Dpt_id ?? 'unknown';
        });

        $reportData = [];
        $totalDepts = 0;
        $grandTotalWorkOrders = 0;
        $grandTotalWorkOrderAmount = 0;
        $grandTotalBilledAmount = 0;
        $grandTotalBalanceAmount = 0;

        foreach ($groupedByDept as $deptId => $deptWorkOrders) {
            $dept = $deptWorkOrders->first()->tender->department ?? null;
            $deptName = $dept ? ($dept->Dpt_Desc ?? $dept->Dpt_Name ?? 'Unknown Department') : 'Unknown Department';

            $deptTotalWorkOrders = 0;
            $deptTotalWorkOrderAmount = 0;
            $deptTotalBilledAmount = 0;
            $deptTotalBalanceAmount = 0;

            $workOrderDetails = [];

            foreach ($deptWorkOrders as $wo) {
                $vendor = $wo->subledger;
                $tender = $wo->tender;

                // Calculate billed amount (you can adjust this based on your actual billing data)
                // For now, assuming some percentage is billed or you have a separate billing table
                $billedAmount = $this->calculateBilledAmount($wo);
                $balanceAmount = (float) ($wo->WO_Amount ?? 0) - $billedAmount;

                // Determine work order status
                $workOrderStatus = $this->getWorkOrderStatus($wo, $billedAmount);

                // Apply status filter if provided
                if ($request->filled('status') && $workOrderStatus != $request->status) {
                    continue;
                }

                // Filter out completed if not included
                if (!$request->boolean('include_completed') && $workOrderStatus == 'completed') {
                    continue;
                }

                $workOrderDetails[] = [
                    'work_order_id' => $wo->WA_Id,
                    'work_order_no' => $wo->WA_RefId ?? 'WO-' . str_pad($wo->WA_Id, 5, '0', STR_PAD_LEFT),
                    'work_order_date' => $wo->WO_Date,
                    'formatted_work_order_date' => $wo->WO_Date ? date('d-m-Y', strtotime($wo->WO_Date)) : null,

                    'vendor_id' => $vendor->SL_Id ?? null,
                    'vendor_name' => $vendor->SL_Name ?? 'Unknown Vendor',
                    'vendor_code' => $vendor->SL_Code ?? null,
                    'vendor_contact' => $vendor->SL_Cont_Pers ?? null,

                    'tender_id' => $tender->TE_Id ?? null,
                    'tender_no' => $tender ? 'TND-' . str_pad($tender->TE_Id, 5, '0', STR_PAD_LEFT) : null,
                    'tender_type' => $tender->tendertype->TnT_Name ?? null,

                    'work_order_amount' => (float) ($wo->WO_Amount ?? 0),
                    'formatted_work_order_amount' => '₹ ' . number_format($wo->WO_Amount ?? 0, 2),
                    'billed_amount' => $billedAmount,
                    'formatted_billed_amount' => '₹ ' . number_format($billedAmount, 2),
                    'balance_amount' => $balanceAmount,
                    'formatted_balance_amount' => '₹ ' . number_format($balanceAmount, 2),
                    'balance_percentage' => ($wo->WO_Amount ?? 0) > 0 ? round(($balanceAmount / $wo->WO_Amount) * 100, 2) : 0,

                    'work_order_status' => $workOrderStatus,
                    'status_code' => $this->getWorkOrderStatusCode($workOrderStatus),
                    'description' => $tender->Description ?? null,
                    'completion_percentage' => ($wo->WO_Amount ?? 0) > 0 ? round(($billedAmount / $wo->WO_Amount) * 100, 2) : 0,
                ];

                $deptTotalWorkOrders++;
                $deptTotalWorkOrderAmount += (float) ($wo->WO_Amount ?? 0);
                $deptTotalBilledAmount += $billedAmount;
                $deptTotalBalanceAmount += $balanceAmount;
            }

            // Skip department if no work orders after filtering
            if (empty($workOrderDetails)) {
                continue;
            }

            // Sort work order details if requested
            if ($request->filled('sort_by') && $request->sort_by != 'vendor_name') {
                $sortField = $request->sort_by;
                $sortOrder = $request->sort_order ?? 'asc';

                usort($workOrderDetails, function($a, $b) use ($sortField, $sortOrder) {
                    if ($sortField == 'work_order_no') {
                        $result = strcmp($a['work_order_no'] ?? '', $b['work_order_no'] ?? '');
                    } elseif ($sortField == 'date') {
                        $result = strcmp($a['work_order_date'] ?? '', $b['work_order_date'] ?? '');
                    } elseif ($sortField == 'work_order_amount') {
                        $result = ($a['work_order_amount'] ?? 0) <=> ($b['work_order_amount'] ?? 0);
                    } elseif ($sortField == 'billed_amount') {
                        $result = ($a['billed_amount'] ?? 0) <=> ($b['billed_amount'] ?? 0);
                    } elseif ($sortField == 'balance_amount') {
                        $result = ($a['balance_amount'] ?? 0) <=> ($b['balance_amount'] ?? 0);
                    } elseif ($sortField == 'status') {
                        $result = strcmp($a['work_order_status'] ?? '', $b['work_order_status'] ?? '');
                    } else {
                        $result = 0;
                    }
                    return $sortOrder == 'desc' ? -$result : $result;
                });
            }

            $reportData[] = [
                'department_id' => $deptId,
                'department_name' => $deptName,
                'total_work_orders' => $deptTotalWorkOrders,
                'total_work_order_amount' => $deptTotalWorkOrderAmount,
                'formatted_total_work_order_amount' => '₹ ' . number_format($deptTotalWorkOrderAmount, 2),
                'total_billed_amount' => $deptTotalBilledAmount,
                'formatted_total_billed_amount' => '₹ ' . number_format($deptTotalBilledAmount, 2),
                'total_balance_amount' => $deptTotalBalanceAmount,
                'formatted_total_balance_amount' => '₹ ' . number_format($deptTotalBalanceAmount, 2),
                'overall_completion_percentage' => $deptTotalWorkOrderAmount > 0 ? round(($deptTotalBilledAmount / $deptTotalWorkOrderAmount) * 100, 2) : 0,
                'work_orders' => $workOrderDetails,
            ];

            $totalDepts++;
            $grandTotalWorkOrders += $deptTotalWorkOrders;
            $grandTotalWorkOrderAmount += $deptTotalWorkOrderAmount;
            $grandTotalBilledAmount += $deptTotalBilledAmount;
            $grandTotalBalanceAmount += $deptTotalBalanceAmount;
        }

        // Sort departments if requested by vendor_name (actually department_name)
        if ($request->filled('sort_by') && $request->sort_by == 'vendor_name') {
            $sortOrder = $request->sort_order ?? 'asc';
            usort($reportData, function($a, $b) use ($sortOrder) {
                $result = strcmp($a['department_name'], $b['department_name']);
                return $sortOrder == 'desc' ? -$result : $result;
            });
        }

        // Apply pagination
        $page = $request->page ?? 1;
        $perPage = $request->per_page ?? 15;
        $totalRecords = count($reportData);
        $offset = ($page - 1) * $perPage;
        $paginatedData = array_slice($reportData, $offset, $perPage);

        return response()->json([
            'success' => true,
            'message' => 'Departmentwise active work orders report retrieved successfully',
            'report_name' => 'Departmentwise Active Work Orders Report',
            'report_date' => now()->format('d-m-Y'),
            'filters_applied' => [
                'dept_id' => $request->dept_id,
                'vendor_id' => $request->vendor_id,
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'min_amount' => $request->min_amount,
                'max_amount' => $request->max_amount,
                'status' => $request->status,
                'include_completed' => $request->boolean('include_completed'),
            ],
            'summary' => [
                'total_departments' => $totalDepts,
                'total_work_orders' => $grandTotalWorkOrders,
                'total_work_order_amount' => $grandTotalWorkOrderAmount,
                'formatted_total_work_order_amount' => '₹ ' . number_format($grandTotalWorkOrderAmount, 2),
                'total_billed_amount' => $grandTotalBilledAmount,
                'formatted_total_billed_amount' => '₹ ' . number_format($grandTotalBilledAmount, 2),
                'total_balance_amount' => $grandTotalBalanceAmount,
                'formatted_total_balance_amount' => '₹ ' . number_format($grandTotalBalanceAmount, 2),
                'overall_completion_percentage' => $grandTotalWorkOrderAmount > 0 ? round(($grandTotalBilledAmount / $grandTotalWorkOrderAmount) * 100, 2) : 0,
            ],
            'data' => $paginatedData,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $totalRecords,
                'last_page' => ceil($totalRecords / $perPage),
                'from' => $offset + 1,
                'to' => min($offset + $perPage, $totalRecords)
            ]
        ]);

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

private function calculateBilledAmount($workOrder)
{

    return (float) ($workOrder->WO_Amount ?? 0) * 0.6;
}

private function getWorkOrderStatus($workOrder, $billedAmount)
{
    $totalAmount = (float) ($workOrder->WO_Amount ?? 0);

    if ($totalAmount == 0) {
        return 'pending';
    }

    if ($billedAmount >= $totalAmount) {
        return 'completed';
    } elseif ($billedAmount > 0) {
        return 'active';
    } else {
        return 'pending';
    }
}

private function getWorkOrderStatusCode($status)
{
    $map = [
        'active' => 'active',
        'completed' => 'completed',
        'pending' => 'pending',
        'cancelled' => 'cancelled'
    ];
    return $map[$status] ?? 'unknown';
}

public function activeWorkOrders(Request $request)
{
    $validator = Validator::make($request->all(), [
        'dept_id' => 'nullable|integer|exists:dept_master,Dpt_id',
        'vendor_id' => 'nullable|integer|exists:subledg_master,SL_Id',
        'from_date' => 'nullable|date',
        'to_date' => 'nullable|date',
        'min_amount' => 'nullable|numeric|min:0',
        'max_amount' => 'nullable|numeric|min:0',
        'status' => 'nullable|string|in:active,completed,pending,cancelled',
        'include_completed' => 'nullable|boolean',
        'sort_by' => 'nullable|string|in:work_order_no,date,vendor_name,amount,billed,balance,status',
        'sort_order' => 'nullable|string|in:asc,desc',
        'page' => 'nullable|integer|min:1',
        'per_page' => 'nullable|integer|min:1|max:100'
    ]);

    if ($validator->fails()) {
        return response()->json([
            'success' => false,
            'message' => 'Validation error',
            'errors' => $validator->errors()
        ], 422);
    }

    try {
        $query = WorkAllocationBook::with([
            'subledger',
            'tender',
            'tender.department',
            'tender.tendertype'
        ]);

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

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

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

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

        if ($request->filled('min_amount')) {
            $query->where('WO_Amount', '>=', $request->min_amount);
        }

        if ($request->filled('max_amount')) {
            $query->where('WO_Amount', '<=', $request->max_amount);
        }

        $workOrders = $query->get();

        if ($workOrders->isEmpty()) {
            return response()->json([
                'success' => true,
                'message' => 'No work orders found',
                'report_name' => 'Active Work Orders Report',
                'report_date' => now()->format('d-m-Y'),
                'filters_applied' => [
                    'dept_id' => $request->dept_id,
                    'vendor_id' => $request->vendor_id,
                    'from_date' => $request->from_date,
                    'to_date' => $request->to_date,
                    'min_amount' => $request->min_amount,
                    'max_amount' => $request->max_amount,
                    'status' => $request->status,
                    'include_completed' => $request->boolean('include_completed'),
                ],
                'data' => [],
                'summary' => [
                    'total_work_orders' => 0,
                    'total_amount' => 0,
                    'total_billed' => 0,
                    'total_balance' => 0,
                ]
            ]);
        }

        $reportData = [];
        $totalAmount = 0;
        $totalBilled = 0;
        $totalBalance = 0;

        foreach ($workOrders as $wo) {
            $vendor = $wo->subledger;
            $tender = $wo->tender;

            // Calculate billed amount (customize this based on your actual billing logic)
            $billedAmount = $this->calculateBilledAmount($wo);
            $balanceAmount = (float) ($wo->WO_Amount ?? 0) - $billedAmount;

            // Determine work order status
            $workOrderStatus = $this->getWorkOrderStatus($wo, $billedAmount);

            // Apply status filter
            if ($request->filled('status') && $workOrderStatus != $request->status) {
                continue;
            }

            // Filter out completed if not included
            if (!$request->boolean('include_completed') && $workOrderStatus == 'completed') {
                continue;
            }

            $reportData[] = [
                'work_order_id' => $wo->WA_Id,
                'work_order_no' => $wo->WA_RefId ?? 'WO-' . str_pad($wo->WA_Id, 5, '0', STR_PAD_LEFT),
                'date' => $wo->WO_Date,
                'formatted_date' => $wo->WO_Date ? date('d-m-Y', strtotime($wo->WO_Date)) : null,

                'vendor_id' => $vendor->SL_Id ?? null,
                'vendor_name' => $vendor->SL_Name ?? 'Unknown Vendor',
                'vendor_code' => $vendor->SL_Code ?? null,

                'tender_id' => $tender->TE_Id ?? null,
                'tender_no' => $tender ? 'TND-' . str_pad($tender->TE_Id, 5, '0', STR_PAD_LEFT) : null,
                'department_id' => $tender->Dpt_id ?? null,
                'department_name' => $tender->department->Dpt_Desc ?? $tender->department->Dpt_Name ?? null,

                'amount' => (float) ($wo->WO_Amount ?? 0),
                'formatted_amount' => '₹ ' . number_format($wo->WO_Amount ?? 0, 2),
                'billed' => $billedAmount,
                'formatted_billed' => '₹ ' . number_format($billedAmount, 2),
                'balance' => $balanceAmount,
                'formatted_balance' => '₹ ' . number_format($balanceAmount, 2),
                'balance_percentage' => ($wo->WO_Amount ?? 0) > 0 ? round(($balanceAmount / $wo->WO_Amount) * 100, 2) : 0,

                'status' => $workOrderStatus,
                'status_code' => $this->getWorkOrderStatusCode($workOrderStatus),
                'completion_percentage' => ($wo->WO_Amount ?? 0) > 0 ? round(($billedAmount / $wo->WO_Amount) * 100, 2) : 0,
            ];

            $totalAmount += (float) ($wo->WO_Amount ?? 0);
            $totalBilled += $billedAmount;
            $totalBalance += $balanceAmount;
        }

        // Apply sorting
        if ($request->filled('sort_by')) {
            $sortField = $request->sort_by;
            $sortOrder = $request->sort_order ?? 'asc';

            usort($reportData, function($a, $b) use ($sortField, $sortOrder) {
                if ($sortField == 'work_order_no') {
                    $result = strcmp($a['work_order_no'] ?? '', $b['work_order_no'] ?? '');
                } elseif ($sortField == 'date') {
                    $result = strcmp($a['date'] ?? '', $b['date'] ?? '');
                } elseif ($sortField == 'vendor_name') {
                    $result = strcmp($a['vendor_name'] ?? '', $b['vendor_name'] ?? '');
                } elseif ($sortField == 'amount') {
                    $result = ($a['amount'] ?? 0) <=> ($b['amount'] ?? 0);
                } elseif ($sortField == 'billed') {
                    $result = ($a['billed'] ?? 0) <=> ($b['billed'] ?? 0);
                } elseif ($sortField == 'balance') {
                    $result = ($a['balance'] ?? 0) <=> ($b['balance'] ?? 0);
                } elseif ($sortField == 'status') {
                    $result = strcmp($a['status'] ?? '', $b['status'] ?? '');
                } else {
                    $result = 0;
                }
                return $sortOrder == 'desc' ? -$result : $result;
            });
        }

        // Apply pagination
        $page = $request->page ?? 1;
        $perPage = $request->per_page ?? 15;
        $totalRecords = count($reportData);
        $offset = ($page - 1) * $perPage;
        $paginatedData = array_slice($reportData, $offset, $perPage);

        return response()->json([
            'success' => true,
            'message' => 'Active work orders report retrieved successfully',
            'report_name' => 'Active Work Orders Report',
            'report_date' => now()->format('d-m-Y'),
            'filters_applied' => [
                'dept_id' => $request->dept_id,
                'vendor_id' => $request->vendor_id,
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'min_amount' => $request->min_amount,
                'max_amount' => $request->max_amount,
                'status' => $request->status,
                'include_completed' => $request->boolean('include_completed'),
            ],
            'summary' => [
                'total_work_orders' => $totalRecords,
                'total_amount' => $totalAmount,
                'formatted_total_amount' => '₹ ' . number_format($totalAmount, 2),
                'total_billed' => $totalBilled,
                'formatted_total_billed' => '₹ ' . number_format($totalBilled, 2),
                'total_balance' => $totalBalance,
                'formatted_total_balance' => '₹ ' . number_format($totalBalance, 2),
                'overall_completion_percentage' => $totalAmount > 0 ? round(($totalBilled / $totalAmount) * 100, 2) : 0,
            ],
            'data' => $paginatedData,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $totalRecords,
                'last_page' => ceil($totalRecords / $perPage),
                'from' => $offset + 1,
                'to' => min($offset + $perPage, $totalRecords)
            ]
        ]);

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



}
