<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;

class FinancialTransactionReportController extends Controller
{

   public function getBalanceSheet(Request $request, $orgId = null)
{
    try {
        $orgId = $orgId ?? $request->input('org_id', 1);

        $sql = "
            WITH AllLedgerGroups AS (
                SELECT
                    Gr_Cd AS GroupCode,
                    Gr_Nm AS GroupName,
                    Gr_St AS GroupStatus,
                    Up_Gr AS ParentGroup,
                    Gr_Id,
                    Gr_Cd AS SortOrder
                FROM ledg_grps
                WHERE deleted_at IS NULL
                    AND (Gr_Cd LIKE '01%' OR Gr_Cd LIKE '02%')
            ),
            TransactionBalances AS (
                SELECT
                    lm.Gr_Id,
                    lm.Lg_Id,
                    lm.Lg_Name,
                    SUM(CASE
                        WHEN ft.V_Mode = 'G' THEN COALESCE(ft.Cr_Amt, 0) - COALESCE(ft.Dr_Amt, 0)
                        WHEN ft.V_Mode = 'S' THEN COALESCE(ft.SL_Amt, 0)
                        ELSE 0
                    END) AS RawBalance
                FROM finan_trns ft
                LEFT JOIN ledger_master lm ON ft.Lg_Id = lm.Lg_Id
                WHERE ft.Org_Id = ?
                    AND ft.deleted_at IS NULL
                    AND lm.deleted_at IS NULL
                    AND ft.Vou_Ty IN ('OPB', 'JRN', 'PUR', 'SAL')
                GROUP BY lm.Gr_Id, lm.Lg_Id, lm.Lg_Name
            ),
            -- L4: Ledger Balances
            LedgerBalances AS (
                SELECT
                    CONCAT(alg.SortOrder, '_', tb.Lg_Id) AS SortOrder,
                    CONCAT('LEDGER_', tb.Lg_Id) AS GroupCode,
                    tb.Lg_Name AS GroupName,
                    4 AS GroupStatus,
                    alg.GroupCode AS ParentGroup,
                    tb.RawBalance
                FROM TransactionBalances tb
                INNER JOIN AllLedgerGroups alg ON tb.Gr_Id = alg.Gr_Id
                WHERE ABS(tb.RawBalance) > 0
            ),
            -- L3: Sum of ledgers under each L3 group
            L3Balances AS (
                SELECT
                    alg.GroupCode,
                    alg.GroupName,
                    alg.GroupStatus,
                    alg.ParentGroup,
                    alg.SortOrder,
                    COALESCE(SUM(lb.RawBalance), 0) AS RawBalance
                FROM AllLedgerGroups alg
                LEFT JOIN LedgerBalances lb ON lb.ParentGroup = alg.GroupCode
                WHERE alg.GroupStatus = 3
                GROUP BY alg.GroupCode, alg.GroupName, alg.GroupStatus, alg.ParentGroup, alg.SortOrder
            ),
            -- L2: Sum of L3 groups under each L2 group
            L2Balances AS (
                SELECT
                    alg.GroupCode,
                    alg.GroupName,
                    alg.GroupStatus,
                    alg.ParentGroup,
                    alg.SortOrder,
                    COALESCE(SUM(l3.RawBalance), 0) AS RawBalance
                FROM AllLedgerGroups alg
                LEFT JOIN L3Balances l3 ON l3.ParentGroup = alg.GroupCode
                WHERE alg.GroupStatus = 2
                GROUP BY alg.GroupCode, alg.GroupName, alg.GroupStatus, alg.ParentGroup, alg.SortOrder
            ),
            -- L1: Sum of L2 groups under each L1 group
            L1Balances AS (
                SELECT
                    alg.GroupCode,
                    alg.GroupName,
                    alg.GroupStatus,
                    alg.ParentGroup,
                    alg.SortOrder,
                    COALESCE(SUM(l2.RawBalance), 0) AS RawBalance
                FROM AllLedgerGroups alg
                LEFT JOIN L2Balances l2 ON l2.ParentGroup = alg.GroupCode
                WHERE alg.GroupStatus = 1
                GROUP BY alg.GroupCode, alg.GroupName, alg.GroupStatus, alg.ParentGroup, alg.SortOrder
            ),
            -- Combine all data
            CombinedData AS (
                SELECT GroupCode, GroupName, GroupStatus, ParentGroup, RawBalance, SortOrder FROM L1Balances
                UNION ALL
                SELECT GroupCode, GroupName, GroupStatus, ParentGroup, RawBalance, SortOrder FROM L2Balances
                UNION ALL
                SELECT GroupCode, GroupName, GroupStatus, ParentGroup, RawBalance, SortOrder FROM L3Balances
                UNION ALL
                SELECT GroupCode, GroupName, GroupStatus, ParentGroup, RawBalance, SortOrder FROM LedgerBalances
            ),
            -- Show only non-zero balances
            GroupsToShow AS (
                SELECT * FROM CombinedData
                WHERE ABS(RawBalance) > 0
            ),
            AssetsData AS (
                SELECT
                    GroupCode,
                    CASE
                        WHEN GroupStatus = 1 THEN GroupName
                        WHEN GroupStatus = 2 THEN CONCAT('    ', GroupName)
                        WHEN GroupStatus = 3 THEN CONCAT('        ', GroupName)
                        WHEN GroupStatus = 4 THEN CONCAT('            ', GroupName)
                        ELSE GroupName
                    END AS DisplayName,
                    GroupStatus,
                    ParentGroup,
                    ABS(RawBalance) AS Balance,
                    SortOrder
                FROM GroupsToShow
                WHERE GroupCode LIKE '01%'
            ),
            LiabilitiesData AS (
                SELECT
                    GroupCode,
                    CASE
                        WHEN GroupStatus = 1 THEN GroupName
                        WHEN GroupStatus = 2 THEN CONCAT('    ', GroupName)
                        WHEN GroupStatus = 3 THEN CONCAT('        ', GroupName)
                        WHEN GroupStatus = 4 THEN CONCAT('            ', GroupName)
                        ELSE GroupName
                    END AS DisplayName,
                    GroupStatus,
                    ParentGroup,
                    RawBalance AS Balance,
                    SortOrder
                FROM GroupsToShow
                WHERE GroupCode LIKE '02%' OR GroupCode LIKE 'LEDGER%'
            ),
            TotalAssets AS (
                SELECT COALESCE(SUM(Balance), 0) AS TotalAmount
                FROM AssetsData
                WHERE GroupStatus > 1
            ),
            -- FIX: Only sum L3 groups (not L1 or L2) to avoid double counting
            TotalLiabilities AS (
                SELECT COALESCE(SUM(Balance), 0) AS TotalAmount
                FROM LiabilitiesData
                WHERE GroupStatus = 3
            )
            SELECT
                GroupCode,
                DisplayName,
                GroupStatus,
                Balance,
                SortOrder,
                'ASSETS' AS Section
            FROM AssetsData
            WHERE Balance IS NOT NULL

            UNION ALL

            SELECT
                'TOTAL_ASSETS',
                'TOTAL ASSETS',
                0,
                (SELECT TotalAmount FROM TotalAssets),
                '0',
                'ASSETS_TOTAL'

            UNION ALL

            SELECT
                GroupCode,
                DisplayName,
                GroupStatus,
                Balance,
                SortOrder,
                'LIABILITIES' AS Section
            FROM LiabilitiesData
            WHERE Balance IS NOT NULL

            UNION ALL

            SELECT
                'TOTAL_LIABILITIES',
                'TOTAL LIABILITIES',
                0,
                (SELECT TotalAmount FROM TotalLiabilities),
                '999999',
                'LIABILITIES_TOTAL'

            UNION ALL

            SELECT
                'NET_WORTH',
                'NET WORTH',
                0,
                (SELECT TotalAmount FROM TotalAssets) - (SELECT TotalAmount FROM TotalLiabilities),
                '9999999',
                'EQUITY'

            ORDER BY
                CASE
                    WHEN Section = 'ASSETS' THEN 1
                    WHEN Section = 'ASSETS_TOTAL' THEN 2
                    WHEN Section = 'LIABILITIES' THEN 3
                    WHEN Section = 'LIABILITIES_TOTAL' THEN 4
                    WHEN Section = 'EQUITY' THEN 5
                END,
                SortOrder
        ";

        $results = DB::select($sql, [$orgId]);

        return response()->json([
            'success' => true,
            'message' => 'Balance sheet retrieved successfully',
            'data' => [
                'organisation_id' => (int) $orgId,
                'report_date' => now()->toDateString(),
                'records' => $results
            ]
        ], 200);
    } catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Failed to fetch balance sheet',
            'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
        ], 500);
    }
}

    public function getProfitLoss(Request $request, $orgId = null)
    {
        try {
            $orgId = $orgId ?? $request->input('org_id', 1);
            $fromDate = $request->input('from_date', date('Y-m-01'));
            $toDate = $request->input('to_date', date('Y-m-d'));

            $sql = "
                WITH PLData AS (
                    SELECT
                        lg.Gr_Cd AS GroupCode,
                        lg.Gr_Nm AS GroupName,
                        lg.Gr_St AS GroupStatus,
                        lg.Up_Gr AS ParentGroup,
                        SUM(CASE
                            WHEN ft.V_Mode = 'G' THEN COALESCE(ft.Cr_Amt, 0) - COALESCE(ft.Dr_Amt, 0)
                            WHEN ft.V_Mode = 'S' THEN COALESCE(ft.SL_Amt, 0)
                            ELSE 0
                        END) AS Balance
                    FROM finan_trns ft
                    LEFT JOIN ledg_grps lg ON ft.Lg_Id = lg.Gr_Id
                    WHERE ft.Org_Id = ?
                        AND ft.deleted_at IS NULL
                        AND lg.deleted_at IS NULL
                        AND ft.Vou_Ty IN ('OPB', 'JRN', 'PUR', 'SAL')
                        AND (lg.Gr_Cd LIKE '03%' OR lg.Gr_Cd LIKE '04%')
                        AND DATE(ft.Vou_Dt) BETWEEN ? AND ?
                    GROUP BY lg.Gr_Cd, lg.Gr_Nm, lg.Gr_St, lg.Up_Gr
                    HAVING ABS(SUM(CASE
                            WHEN ft.V_Mode = 'G' THEN COALESCE(ft.Cr_Amt, 0) - COALESCE(ft.Dr_Amt, 0)
                            WHEN ft.V_Mode = 'S' THEN COALESCE(ft.SL_Amt, 0)
                            ELSE 0
                        END)) > 0.01
                ),
                IncomeData AS (
                    SELECT
                        GroupCode,
                        GroupName,
                        GroupStatus,
                        ParentGroup,
                        Balance
                    FROM PLData
                    WHERE GroupCode LIKE '03%'
                ),
                ExpenseData AS (
                    SELECT
                        GroupCode,
                        GroupName,
                        GroupStatus,
                        ParentGroup,
                        Balance
                    FROM PLData
                    WHERE GroupCode LIKE '04%'
                ),
                TotalIncome AS (
                    SELECT SUM(Balance) AS TotalAmount
                    FROM IncomeData
                    WHERE GroupStatus = 1 OR GroupCode = '03000000'
                ),
                TotalExpenses AS (
                    SELECT SUM(Balance) AS TotalAmount
                    FROM ExpenseData
                    WHERE GroupStatus = 1 OR GroupCode = '04000000'
                )
                SELECT
                    GroupCode,
                    CASE
                        WHEN GroupStatus = 1 THEN GroupName
                        WHEN GroupStatus = 2 THEN CONCAT('  ', GroupName)
                        WHEN GroupStatus = 3 THEN CONCAT('    ', GroupName)
                        WHEN GroupStatus = 4 THEN CONCAT('      ', GroupName)
                        ELSE GroupName
                    END AS DisplayName,
                    GroupStatus,
                    Balance,
                    'INCOME' AS Type
                FROM IncomeData

                UNION ALL

                SELECT
                    'TOTAL_INCOME' AS GroupCode,
                    'TOTAL INCOME' AS DisplayName,
                    0 AS GroupStatus,
                    (SELECT TotalAmount FROM TotalIncome) AS Balance,
                    'INCOME_TOTAL' AS Type

                UNION ALL

                SELECT
                    GroupCode,
                    CASE
                        WHEN GroupStatus = 1 THEN GroupName
                        WHEN GroupStatus = 2 THEN CONCAT('  ', GroupName)
                        WHEN GroupStatus = 3 THEN CONCAT('    ', GroupName)
                        WHEN GroupStatus = 4 THEN CONCAT('      ', GroupName)
                        ELSE GroupName
                    END AS DisplayName,
                    GroupStatus,
                    Balance,
                    'EXPENSE' AS Type
                FROM ExpenseData

                UNION ALL

                SELECT
                    'TOTAL_EXPENSES' AS GroupCode,
                    'TOTAL EXPENSES' AS DisplayName,
                    0 AS GroupStatus,
                    (SELECT TotalAmount FROM TotalExpenses) AS Balance,
                    'EXPENSE_TOTAL' AS Type

                UNION ALL

                SELECT
                    'NET_PL' AS GroupCode,
                    CONCAT('NET ', CASE WHEN (SELECT TotalAmount FROM TotalIncome) - (SELECT TotalAmount FROM TotalExpenses) >= 0 THEN 'PROFIT' ELSE 'LOSS' END) AS DisplayName,
                    0 AS GroupStatus,
                    ABS((SELECT TotalAmount FROM TotalIncome) - (SELECT TotalAmount FROM TotalExpenses)) AS Balance,
                    'NET' AS Type

                ORDER BY
                    CASE
                        WHEN Type = 'INCOME' THEN 1
                        WHEN Type = 'INCOME_TOTAL' THEN 2
                        WHEN Type = 'EXPENSE' THEN 3
                        WHEN Type = 'EXPENSE_TOTAL' THEN 4
                        WHEN Type = 'NET' THEN 5
                    END,
                    GroupCode
            ";

            $results = DB::select($sql, [$orgId, $fromDate, $toDate]);

            
            $income = [];
            $expenses = [];
            $summary = [];

            foreach ($results as $row) {
                $type = $row->Type; 

                if ($type === 'INCOME') {
                    $income[] = $row;
                } elseif ($type === 'EXPENSE') {
                    $expenses[] = $row;
                } elseif ($type === 'INCOME_TOTAL') {
                    $summary['total_income'] = (float) $row->Balance;
                } elseif ($type === 'EXPENSE_TOTAL') {
                    $summary['total_expenses'] = (float) $row->Balance;
                } elseif ($type === 'NET') {
                    $summary['net_profit_loss'] = (float) $row->Balance;
                    $summary['net_type'] = str_contains($row->DisplayName, 'PROFIT') ? 'PROFIT' : 'LOSS';
                }
            }

            return response()->json([
                'success' => true,
                'message' => 'Profit & Loss statement retrieved successfully',
                'data' => [
                    'organisation_id' => (int) $orgId,
                    'from_date' => $fromDate,
                    'to_date' => $toDate,
                    'income' => $income,
                    'expenses' => $expenses,
                    'summary' => $summary
                ]
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch profit & loss statement',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }



    public function getAssetRegister(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'as_on_date' => 'nullable|date|date_format:Y-m-d',
        ]);

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

        $asOnDate = $request->input('as_on_date', date('Y-m-d'));

        
        $query = "
        SELECT
            'FIXED ASSETS' AS category,
            Lg_Name AS asset_name,
            'Asset' AS asset_type,
            COALESCE(SUM(ft.Dr_Amt - ft.Cr_Amt), 0) AS current_value
        FROM ledger_master lm
        LEFT JOIN finan_trns ft ON lm.Lg_Id = ft.Lg_Id AND ft.Org_Id = 1 AND ft.deleted_at IS NULL
        WHERE lm.Gr_Id IN (175, 176, 177, 178, 179, 180, 181, 182, 183)  -- Capital Expenditure Groups
            AND lm.deleted_at IS NULL
        GROUP BY lm.Lg_Id, lm.Lg_Name

        UNION ALL

        SELECT
            'CAPITAL WORK IN PROGRESS' AS category,
            Lg_Name AS asset_name,
            'CWIP' AS asset_type,
            COALESCE(SUM(ft.Dr_Amt - ft.Cr_Amt), 0) AS current_value
        FROM ledger_master lm
        LEFT JOIN finan_trns ft ON lm.Lg_Id = ft.Lg_Id AND ft.Org_Id = 1 AND ft.deleted_at IS NULL
        WHERE lm.Lg_Name LIKE '%Work%' OR lm.Lg_Name LIKE '%Project%' OR lm.Lg_Name LIKE '%Construction%'
            AND lm.deleted_at IS NULL
        GROUP BY lm.Lg_Id, lm.Lg_Name

        UNION ALL

        SELECT
            'INTANGIBLE ASSETS' AS category,
            Lg_Name AS asset_name,
            'Intangible' AS asset_type,
            COALESCE(SUM(ft.Dr_Amt - ft.Cr_Amt), 0) AS current_value
        FROM ledger_master lm
        LEFT JOIN finan_trns ft ON lm.Lg_Id = ft.Lg_Id AND ft.Org_Id = 1 AND ft.deleted_at IS NULL
        WHERE lm.Lg_Name LIKE '%Software%' OR lm.Lg_Name LIKE '%License%' OR lm.Lg_Name LIKE '%Patent%'
            AND lm.deleted_at IS NULL
        GROUP BY lm.Lg_Id, lm.Lg_Name

        ORDER BY category, current_value DESC
    ";

        try {
            $results = DB::select($query);

            $assets = [];
            $totalAssets = 0;
            $categoryTotals = [];

            foreach ($results as $row) {
                $amount = (float) $row->current_value;
                $totalAssets += $amount;

                if (!isset($categoryTotals[$row->category])) {
                    $categoryTotals[$row->category] = 0;
                }
                $categoryTotals[$row->category] += $amount;

                $assets[] = [
                    'category' => $row->category,
                    'asset_name' => $row->asset_name,
                    'asset_type' => $row->asset_type,
                    'current_value' => $amount,
                    'current_value_formatted' => number_format($amount, 2)
                ];
            }

            
            $categorySummary = [];
            foreach ($categoryTotals as $category => $amount) {
                $categorySummary[] = [
                    'category' => $category,
                    'total_value' => $amount,
                    'total_value_formatted' => number_format($amount, 2),
                    'asset_count' => count(array_filter($assets, function ($a) use ($category) {
                        return $a['category'] == $category;
                    }))
                ];
            }

            return response()->json([
                'success' => true,
                'message' => 'Asset Register retrieved successfully',
                'data' => [
                    'report_name' => 'FIXED ASSET REGISTER',
                    'municipal_corporation' => 'Gandhinagar Municipal Corporation',
                    'as_on_date' => date('d/m/Y', strtotime($asOnDate)),
                    'generated_on' => now()->format('d/m/Y H:i:s'),
                    'assets' => $assets,
                    'category_summary' => $categorySummary,
                    'total_assets' => $totalAssets,
                    'total_assets_formatted' => number_format($totalAssets, 2)
                ]
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve Asset Register',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }


    public function getLiabilitiesRegister(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'as_on_date' => 'nullable|date|date_format:Y-m-d',
        ]);

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

        $asOnDate = $request->input('as_on_date', date('Y-m-d'));

        $query = "
        -- 1. SECURITY DEPOSITS (from ledger_master)
        SELECT
            'Security Deposits' AS liability_type,
            Lg_Name AS description,
            'Deposit' AS category,
            COALESCE(SUM(ft.Cr_Amt - ft.Dr_Amt), 0) AS outstanding_amount
        FROM ledger_master lm
        LEFT JOIN finan_trns ft ON lm.Lg_Id = ft.Lg_Id AND ft.Org_Id = 1 AND ft.deleted_at IS NULL
        WHERE lm.Lg_Id = 680  -- Vendor Security Deposits ledger
            AND lm.deleted_at IS NULL
        GROUP BY lm.Lg_Id, lm.Lg_Name

        UNION ALL

        -- 2. CONTRACTOR PAYABLES
        SELECT
            'Contractor Payables' AS liability_type,
            Lg_Name AS description,
            'Payable' AS category,
            COALESCE(SUM(ft.Cr_Amt - ft.Dr_Amt), 0) AS outstanding_amount
        FROM ledger_master lm
        LEFT JOIN finan_trns ft ON lm.Lg_Id = ft.Lg_Id AND ft.Org_Id = 1 AND ft.deleted_at IS NULL
        WHERE lm.Lg_Name LIKE '%Contractor%' OR lm.Lg_Name LIKE '%Supplier%' OR lm.Lg_Name LIKE '%Vendor%'
            AND lm.deleted_at IS NULL
        GROUP BY lm.Lg_Id, lm.Lg_Name

        UNION ALL

        -- 3. STATUTORY PAYABLES (PF, TDS, GST, etc.)
        SELECT
            'Statutory Payables' AS liability_type,
            Lg_Name AS description,
            'Statutory' AS category,
            COALESCE(SUM(ft.Cr_Amt - ft.Dr_Amt), 0) AS outstanding_amount
        FROM ledger_master lm
        LEFT JOIN finan_trns ft ON lm.Lg_Id = ft.Lg_Id AND ft.Org_Id = 1 AND ft.deleted_at IS NULL
        WHERE lm.Lg_Name LIKE '%PF%' OR lm.Lg_Name LIKE '%TDS%' OR lm.Lg_Name LIKE '%GST%'
            OR lm.Lg_Name LIKE '%ESI%' OR lm.Lg_Name LIKE '%Professional Tax%'
            AND lm.deleted_at IS NULL
        GROUP BY lm.Lg_Id, lm.Lg_Name

        UNION ALL

        -- 4. EMPLOYEE BENEFITS
        SELECT
            'Employee Benefits' AS liability_type,
            Lg_Name AS description,
            'Benefit' AS category,
            COALESCE(SUM(ft.Cr_Amt - ft.Dr_Amt), 0) AS outstanding_amount
        FROM ledger_master lm
        LEFT JOIN finan_trns ft ON lm.Lg_Id = ft.Lg_Id AND ft.Org_Id = 1 AND ft.deleted_at IS NULL
        WHERE lm.Lg_Name LIKE '%Salary%' OR lm.Lg_Name LIKE '%Pension%' OR lm.Lg_Name LIKE '%Gratuity%'
            OR lm.Lg_Name LIKE '%Leave%'
            AND lm.deleted_at IS NULL
        GROUP BY lm.Lg_Id, lm.Lg_Name

        UNION ALL

        -- 5. GRANT LIABILITIES (Unspent Grants)
        SELECT
            'Grant Liabilities' AS liability_type,
            Lg_Name AS description,
            'Grant' AS category,
            COALESCE(SUM(ft.Cr_Amt - ft.Dr_Amt), 0) AS outstanding_amount
        FROM ledger_master lm
        LEFT JOIN finan_trns ft ON lm.Lg_Id = ft.Lg_Id AND ft.Org_Id = 1 AND ft.deleted_at IS NULL
        WHERE lm.Lg_Name LIKE '%Grant%' AND lm.Lg_Name NOT LIKE '%Income%'
            AND lm.deleted_at IS NULL
        GROUP BY lm.Lg_Id, lm.Lg_Name

        UNION ALL

        -- 6. LOANS & BORROWINGS
        SELECT
            'Loans & Borrowings' AS liability_type,
            Lg_Name AS description,
            'Loan' AS category,
            COALESCE(SUM(ft.Cr_Amt - ft.Dr_Amt), 0) AS outstanding_amount
        FROM ledger_master lm
        LEFT JOIN finan_trns ft ON lm.Lg_Id = ft.Lg_Id AND ft.Org_Id = 1 AND ft.deleted_at IS NULL
        WHERE lm.Lg_Name LIKE '%Loan%' OR lm.Lg_Name LIKE '%Borrowing%' OR lm.Lg_Name LIKE '%Bond%'
            AND lm.deleted_at IS NULL
        GROUP BY lm.Lg_Id, lm.Lg_Name

        ORDER BY liability_type, outstanding_amount DESC
    ";

        try {
            $results = DB::select($query);

            $liabilities = [];
            $totalLiabilities = 0;
            $typeTotals = [];

            foreach ($results as $row) {
                $amount = (float) $row->outstanding_amount;
                if ($amount != 0) {  
                    $totalLiabilities += $amount;

                    if (!isset($typeTotals[$row->liability_type])) {
                        $typeTotals[$row->liability_type] = 0;
                    }
                    $typeTotals[$row->liability_type] += $amount;

                    $liabilities[] = [
                        'liability_type' => $row->liability_type,
                        'description' => $row->description,
                        'category' => $row->category,
                        'outstanding_amount' => $amount,
                        'outstanding_amount_formatted' => number_format($amount, 2)
                    ];
                }
            }

            
            $typeSummary = [];
            foreach ($typeTotals as $type => $amount) {
                $typeSummary[] = [
                    'liability_type' => $type,
                    'total_amount' => $amount,
                    'total_amount_formatted' => number_format($amount, 2),
                    'count' => count(array_filter($liabilities, function ($l) use ($type) {
                        return $l['liability_type'] == $type;
                    }))
                ];
            }

            return response()->json([
                'success' => true,
                'message' => 'Liabilities Register retrieved successfully',
                'data' => [
                    'report_name' => 'LIABILITIES REGISTER',
                    'municipal_corporation' => 'Gandhinagar Municipal Corporation',
                    'as_on_date' => date('d/m/Y', strtotime($asOnDate)),
                    'generated_on' => now()->format('d/m/Y H:i:s'),
                    'liabilities' => $liabilities,
                    'type_summary' => $typeSummary,
                    'total_liabilities' => $totalLiabilities,
                    'total_liabilities_formatted' => number_format($totalLiabilities, 2)
                ]
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve Liabilities Register',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }


    public function getReceiptsAndPaymentsStatement(Request $request)
    {
        
        $validator = Validator::make($request->all(), [
            'start_date' => 'required|date|date_format:Y-m-d',
            'end_date' => 'required|date|date_format:Y-m-d|after_or_equal:start_date',
        ]);

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

        $startDate = $request->input('start_date');
        $endDate = $request->input('end_date');

        
        $approvedStatuses = [12, 13, 14, 15, 24, 25];
        $statusPlaceholders = implode(',', array_fill(0, count($approvedStatuses), '?'));

        try {
           
            $openingBalance = $this->getOpeningBalance($startDate, $approvedStatuses);

           
            $revenueReceipts = DB::select("
                SELECT
                    lm.Lg_Name AS particular,
                    COALESCE(SUM(cv.Amount), 0) AS amount
                FROM chl_voucher cv
                INNER JOIN ledger_master lm ON cv.Lg_Id = lm.Lg_Id
                WHERE cv.Ch_Date BETWEEN ? AND ?
                    AND cv.Is_income = 'Y'
                    AND cv.deleted_at IS NULL
                    AND cv.Status IN ({$statusPlaceholders})
                    AND (
                        lm.Lg_Code LIKE '10%'
                        OR lm.Lg_Code LIKE '101%'
                        OR lm.Lg_Code LIKE '103%'
                        OR lm.Lg_Code LIKE '108%'
                        OR lm.Lg_Code IS NULL
                    )
                    AND lm.Lg_Id NOT IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Grant%'
                        AND Lg_Name LIKE '%Capital%'
                    )
                GROUP BY lm.Lg_Name
                ORDER BY lm.Lg_Name
            ", array_merge([$startDate, $endDate], $approvedStatuses));

  
            $capitalReceipts = DB::select("
                SELECT
                    lm.Lg_Name AS particular,
                    COALESCE(SUM(cv.Amount), 0) AS amount
                FROM chl_voucher cv
                INNER JOIN ledger_master lm ON cv.Lg_Id = lm.Lg_Id
                WHERE cv.Ch_Date BETWEEN ? AND ?
                    AND cv.Is_income = 'Y'
                    AND cv.deleted_at IS NULL
                    AND cv.Status IN ({$statusPlaceholders})
                    AND (
                        lm.Lg_Name LIKE '%Grant%'
                        OR lm.Lg_Name LIKE '%Loan%'
                        OR lm.Lg_Name LIKE '%Deposit%'
                        OR lm.Lg_Name LIKE '%Sale of%'
                        OR lm.Lg_Code LIKE '108%'
                        OR lm.Lg_Code LIKE '15FC%'
                        OR lm.Lg_Code LIKE 'AM%'
                    )
                GROUP BY lm.Lg_Name
                ORDER BY lm.Lg_Name
            ", array_merge([$startDate, $endDate], $approvedStatuses));


            $revenuePayments = DB::select("
                SELECT
                    lm.Lg_Name AS particular,
                    COALESCE(SUM(cv.Amount), 0) AS amount
                FROM chl_voucher cv
                INNER JOIN ledger_master lm ON cv.Lg_Id = lm.Lg_Id
                WHERE cv.Ch_Date BETWEEN ? AND ?
                    AND cv.Is_income = 'N'
                    AND cv.deleted_at IS NULL
                    AND cv.Status IN ({$statusPlaceholders})
                    AND (
                        lm.Lg_Code LIKE '201%'
                        OR lm.Lg_Code LIKE '202%'
                        OR lm.Lg_Code LIKE '203%'
                        OR lm.Lg_Code LIKE '205%'
                        OR lm.Lg_Code LIKE '209%'
                        OR lm.Lg_Code IS NULL
                    )
                    AND lm.Lg_Id NOT IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Construction%'
                        OR Lg_Name LIKE '%Building%'
                        OR Lg_Name LIKE '%Road%'
                        OR Lg_Name LIKE '%Project%'
                    )
                GROUP BY lm.Lg_Name
                ORDER BY lm.Lg_Name
            ", array_merge([$startDate, $endDate], $approvedStatuses));


            $capitalPayments = DB::select("
                SELECT
                    lm.Lg_Name AS particular,
                    COALESCE(SUM(cv.Amount), 0) AS amount
                FROM chl_voucher cv
                INNER JOIN ledger_master lm ON cv.Lg_Id = lm.Lg_Id
                WHERE cv.Ch_Date BETWEEN ? AND ?
                    AND cv.Is_income = 'N'
                    AND cv.deleted_at IS NULL
                    AND cv.Status IN ({$statusPlaceholders})
                    AND (
                        lm.Lg_Code LIKE '401%'
                        OR lm.Lg_Code LIKE '402%'
                        OR lm.Lg_Code LIKE '408%'
                        OR lm.Lg_Name LIKE '%Construction%'
                        OR lm.Lg_Name LIKE '%Building%'
                        OR lm.Lg_Name LIKE '%Road%'
                        OR lm.Lg_Name LIKE '%Project%'
                        OR lm.Lg_Name LIKE '%Work%'
                    )
                GROUP BY lm.Lg_Name
                ORDER BY lm.Lg_Name
            ", array_merge([$startDate, $endDate], $approvedStatuses));


            $totalRevenueReceipts = array_sum(array_column($revenueReceipts, 'amount'));
            $totalCapitalReceipts = array_sum(array_column($capitalReceipts, 'amount'));
            $totalReceipts = $totalRevenueReceipts + $totalCapitalReceipts;

            $totalRevenuePayments = array_sum(array_column($revenuePayments, 'amount'));
            $totalCapitalPayments = array_sum(array_column($capitalPayments, 'amount'));
            $totalPayments = $totalRevenuePayments + $totalCapitalPayments;


            $openingTotal = $openingBalance['cash_in_hand'] + $openingBalance['cash_at_bank'];
            $closingTotal = $openingTotal + $totalReceipts - $totalPayments;

            $closingCashInHand = $openingBalance['cash_in_hand'] + $this->getCashReceipts($startDate, $endDate, $approvedStatuses) - $this->getCashPayments($startDate, $endDate, $approvedStatuses);
            $closingCashAtBank = $openingBalance['cash_at_bank'] + $this->getBankReceipts($startDate, $endDate, $approvedStatuses) - $this->getBankPayments($startDate, $endDate, $approvedStatuses);


            return response()->json([
                'success' => true,
                'message' => 'Receipts & Payments Statement retrieved successfully',
                'data' => [
                    'report_name' => 'Receipts and Payments Statement',
                    'period' => [
                        'start_date' => $startDate,
                        'end_date' => $endDate
                    ],
                    'opening_balance' => [
                        [
                            'particular' => 'Cash in Hand',
                            'amount' => round($openingBalance['cash_in_hand'], 2),
                            'formatted_amount' => number_format($openingBalance['cash_in_hand'], 2)
                        ],
                        [
                            'particular' => 'Cash at Bank',
                            'amount' => round($openingBalance['cash_at_bank'], 2),
                            'formatted_amount' => number_format($openingBalance['cash_at_bank'], 2)
                        ]
                    ],
                    'revenue_receipts' => array_map(function ($item) {
                        return [
                            'particular' => $item->particular,
                            'amount' => round((float)$item->amount, 2),
                            'formatted_amount' => number_format($item->amount, 2)
                        ];
                    }, $revenueReceipts),
                    'capital_receipts' => array_map(function ($item) {
                        return [
                            'particular' => $item->particular,
                            'amount' => round((float)$item->amount, 2),
                            'formatted_amount' => number_format($item->amount, 2)
                        ];
                    }, $capitalReceipts),
                    'revenue_payments' => array_map(function ($item) {
                        return [
                            'particular' => $item->particular,
                            'amount' => round((float)$item->amount, 2),
                            'formatted_amount' => number_format($item->amount, 2)
                        ];
                    }, $revenuePayments),
                    'capital_payments' => array_map(function ($item) {
                        return [
                            'particular' => $item->particular,
                            'amount' => round((float)$item->amount, 2),
                            'formatted_amount' => number_format($item->amount, 2)
                        ];
                    }, $capitalPayments),
                    'closing_balance' => [
                        [
                            'particular' => 'Cash in Hand',
                            'amount' => round($closingCashInHand, 2),
                            'formatted_amount' => number_format($closingCashInHand, 2)
                        ],
                        [
                            'particular' => 'Cash at Bank',
                            'amount' => round($closingCashAtBank, 2),
                            'formatted_amount' => number_format($closingCashAtBank, 2)
                        ]
                    ],
                    'summary' => [
                        'total_opening_balance' => round($openingTotal, 2),
                        'total_opening_balance_formatted' => number_format($openingTotal, 2),
                        'total_revenue_receipts' => round($totalRevenueReceipts, 2),
                        'total_revenue_receipts_formatted' => number_format($totalRevenueReceipts, 2),
                        'total_capital_receipts' => round($totalCapitalReceipts, 2),
                        'total_capital_receipts_formatted' => number_format($totalCapitalReceipts, 2),
                        'total_receipts' => round($totalReceipts, 2),
                        'total_receipts_formatted' => number_format($totalReceipts, 2),
                        'total_revenue_payments' => round($totalRevenuePayments, 2),
                        'total_revenue_payments_formatted' => number_format($totalRevenuePayments, 2),
                        'total_capital_payments' => round($totalCapitalPayments, 2),
                        'total_capital_payments_formatted' => number_format($totalCapitalPayments, 2),
                        'total_payments' => round($totalPayments, 2),
                        'total_payments_formatted' => number_format($totalPayments, 2),
                        'net_surplus_deficit' => round($totalReceipts - $totalPayments, 2),
                        'net_surplus_deficit_formatted' => number_format($totalReceipts - $totalPayments, 2),
                        'total_closing_balance' => round($closingTotal, 2),
                        'total_closing_balance_formatted' => number_format($closingTotal, 2)
                    ]
                ]
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve Receipts & Payments Statement',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ], 500);
        }
    }

  
    private function getOpeningBalance($startDate, $approvedStatuses)
    {
        $statusPlaceholders = implode(',', array_fill(0, count($approvedStatuses), '?'));

        
        $openingFromOPB = DB::select("
            SELECT
                'Cash in Hand' AS particular,
                COALESCE(SUM(
                    CASE
                        WHEN ft.Cr_Amt > 0 THEN -ft.Cr_Amt
                        WHEN ft.Dr_Amt > 0 THEN ft.Dr_Amt
                        ELSE 0
                    END
                ), 0) AS amount
            FROM finan_trns ft
            INNER JOIN ledger_master lm ON ft.Lg_Id = lm.Lg_Id
            WHERE ft.Vou_Ty = 'OPB'
                AND (lm.Lg_Name = 'Cash Account'
                    OR lm.Lg_Name LIKE 'Cash Account -%')
                AND ft.deleted_at IS NULL

            UNION ALL

            SELECT
                'Cash at Bank' AS particular,
                COALESCE(SUM(
                    CASE
                        WHEN ft.Cr_Amt > 0 THEN -ft.Cr_Amt
                        WHEN ft.Dr_Amt > 0 THEN ft.Dr_Amt
                        ELSE 0
                    END
                ), 0) AS amount
            FROM finan_trns ft
            INNER JOIN ledger_master lm ON ft.Lg_Id = lm.Lg_Id
            WHERE ft.Vou_Ty = 'OPB'
                AND lm.Gr_Id = 6
                AND ft.deleted_at IS NULL
        ");

        $cashInHand = 0;
        $cashAtBank = 0;

        foreach ($openingFromOPB as $row) {
            if ($row->particular == 'Cash in Hand') {
                $cashInHand = (float) $row->amount;
            } else {
                $cashAtBank = (float) $row->amount;
            }
        }

        
        if ($cashInHand == 0 && $cashAtBank == 0) {
            $openingFromVouchers = DB::select("
                SELECT
                    'Cash in Hand' AS particular,
                    COALESCE(SUM(
                        CASE
                            WHEN cv.Is_income = 'Y' AND cv.Py_Mode IN ('Cash', 'Pos') THEN cv.Amount
                            WHEN cv.Is_income = 'N' AND cv.Py_Mode IN ('Cash', 'Pos') THEN -cv.Amount
                            ELSE 0
                        END
                    ), 0) AS amount
                FROM chl_voucher cv
                WHERE cv.Ch_Date < ?
                    AND cv.deleted_at IS NULL
                    AND cv.Status IN ({$statusPlaceholders})

                UNION ALL

                SELECT
                    'Cash at Bank' AS particular,
                    COALESCE(SUM(
                        CASE
                            WHEN cv.Is_income = 'Y' AND cv.Py_Mode IN ('Bank', 'Cheque', 'DD', 'Online') THEN cv.Amount
                            WHEN cv.Is_income = 'N' AND cv.Py_Mode IN ('Bank', 'Cheque', 'DD', 'Online') THEN -cv.Amount
                            ELSE 0
                        END
                    ), 0) AS amount
                FROM chl_voucher cv
                WHERE cv.Ch_Date < ?
                    AND cv.deleted_at IS NULL
                    AND cv.Status IN ({$statusPlaceholders})
            ", array_merge([$startDate], $approvedStatuses, [$startDate], $approvedStatuses));

            foreach ($openingFromVouchers as $row) {
                if ($row->particular == 'Cash in Hand') {
                    $cashInHand = (float) $row->amount;
                } else {
                    $cashAtBank = (float) $row->amount;
                }
            }
        }

        return [
            'cash_in_hand' => $cashInHand,
            'cash_at_bank' => $cashAtBank
        ];
    }

   
    private function getCashReceipts($startDate, $endDate, $approvedStatuses)
    {
        $statusPlaceholders = implode(',', array_fill(0, count($approvedStatuses), '?'));

        $result = DB::selectOne("
            SELECT COALESCE(SUM(cv.Amount), 0) AS total
            FROM chl_voucher cv
            WHERE cv.Ch_Date BETWEEN ? AND ?
                AND cv.Is_income = 'Y'
                AND cv.Py_Mode IN ('Cash', 'Pos')
                AND cv.deleted_at IS NULL
                AND cv.Status IN ({$statusPlaceholders})
        ", array_merge([$startDate, $endDate], $approvedStatuses));

        return (float) $result->total;
    }

  
    private function getCashPayments($startDate, $endDate, $approvedStatuses)
    {
        $statusPlaceholders = implode(',', array_fill(0, count($approvedStatuses), '?'));

        $result = DB::selectOne("
            SELECT COALESCE(SUM(cv.Amount), 0) AS total
            FROM chl_voucher cv
            WHERE cv.Ch_Date BETWEEN ? AND ?
                AND cv.Is_income = 'N'
                AND cv.Py_Mode IN ('Cash', 'Pos')
                AND cv.deleted_at IS NULL
                AND cv.Status IN ({$statusPlaceholders})
        ", array_merge([$startDate, $endDate], $approvedStatuses));

        return (float) $result->total;
    }

  
    private function getBankReceipts($startDate, $endDate, $approvedStatuses)
    {
        $statusPlaceholders = implode(',', array_fill(0, count($approvedStatuses), '?'));

        $result = DB::selectOne("
            SELECT COALESCE(SUM(cv.Amount), 0) AS total
            FROM chl_voucher cv
            WHERE cv.Ch_Date BETWEEN ? AND ?
                AND cv.Is_income = 'Y'
                AND cv.Py_Mode IN ('Bank', 'Cheque', 'DD', 'Online')
                AND cv.deleted_at IS NULL
                AND cv.Status IN ({$statusPlaceholders})
        ", array_merge([$startDate, $endDate], $approvedStatuses));

        return (float) $result->total;
    }

   
    private function getBankPayments($startDate, $endDate, $approvedStatuses)
    {
        $statusPlaceholders = implode(',', array_fill(0, count($approvedStatuses), '?'));

        $result = DB::selectOne("
            SELECT COALESCE(SUM(cv.Amount), 0) AS total
            FROM chl_voucher cv
            WHERE cv.Ch_Date BETWEEN ? AND ?
                AND cv.Is_income = 'N'
                AND cv.Py_Mode IN ('Bank', 'Cheque', 'DD', 'Online')
                AND cv.deleted_at IS NULL
                AND cv.Status IN ({$statusPlaceholders})
        ", array_merge([$startDate, $endDate], $approvedStatuses));

        return (float) $result->total;
    }

    /**

     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getContingentLiabilitiesRegister(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'as_on_date' => 'nullable|date|date_format:Y-m-d',
            'fy_year' => 'nullable|string',
        ]);

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

        $asOnDate = $request->input('as_on_date', date('Y-m-d'));
        $fyYear = $request->input('fy_year', '2025-26');

        
        $query = "
        SELECT
            ROW_NUMBER() OVER (ORDER BY amount_rs DESC) AS sr_no,
            liability_type,
            case_or_reference_no,
            amount_rs,
            status,
            remarks,
            expected_resolution_date
        FROM (
            -- 1. COURT CASES / LEGAL DISPUTES
            SELECT
                'Court Cases / Legal Disputes' AS liability_type,
                CONCAT('Case No: ', COALESCE(MAX(Status_Remark), 'Pending')) AS case_or_reference_no,
                SUM(Penalty_Amount) AS amount_rs,
                'Pending - Under Litigation' AS status,
                CONCAT('Total penalty amount from vouchers: ', COUNT(*), ' cases') AS remarks,
                MAX(DATE_ADD(Ch_Date, INTERVAL 90 DAY)) AS expected_resolution_date
            FROM chl_voucher
            WHERE Penalty_Amount > 0
                AND deleted_at IS NULL
                AND Ch_Date <= ?

            UNION ALL

            -- 2. CONTRACTOR CLAIMS / ARBITRATION
            SELECT
                'Contractor Claims / Arbitration' AS liability_type,
                CONCAT('Claim Ref: ', COALESCE(CV_No, 'Pending')) AS case_or_reference_no,
                SUM(Amount) AS amount_rs,
                'Under Arbitration' AS status,
                CONCAT('Contractor: ', COALESCE(MAX(SL_Nm), 'Unknown'), ' - ', COUNT(*), ' claims') AS remarks,
                MAX(DATE_ADD(Ch_Date, INTERVAL 180 DAY)) AS expected_resolution_date
            FROM chl_voucher
            WHERE (Remarks LIKE '%claim%'
                OR Remarks LIKE '%dispute%'
                OR Remarks LIKE '%arbitration%'
                OR Remarks LIKE '%contractor%')
                AND deleted_at IS NULL
                AND Ch_Date <= ?
            GROUP BY CV_No

            UNION ALL

            -- 3. BANK GUARANTEES ISSUED
            SELECT
                'Bank Guarantees Issued' AS liability_type,
                CONCAT('BG Ref: ', COALESCE(Chq_No, 'N/A')) AS case_or_reference_no,
                SUM(Amount) AS amount_rs,
                'Active' AS status,
                CONCAT('Beneficiary: ', COALESCE(MAX(SL_Nm), 'Unknown')) AS remarks,
                MAX(DATE_ADD(Chq_Date, INTERVAL 365 DAY)) AS expected_resolution_date
            FROM chl_voucher
            WHERE Py_Mode = 'DD'
                AND Is_income = 'N'
                AND Remarks LIKE '%BG%'
                AND deleted_at IS NULL
                AND Ch_Date <= ?
            GROUP BY Chq_No

            UNION ALL

            -- 4. PERFORMANCE GUARANTEES
            SELECT
                'Performance Guarantees' AS liability_type,
                CONCAT('PG Ref: ', COALESCE(CV_No, 'N/A')) AS case_or_reference_no,
                SUM(Amount) AS amount_rs,
                'Active' AS status,
                CONCAT('Vendor: ', COALESCE(MAX(SL_Nm), 'Unknown')) AS remarks,
                MAX(DATE_ADD(Ch_Date, INTERVAL 365 DAY)) AS expected_resolution_date
            FROM chl_voucher
            WHERE Lg_Id = 680
                AND Is_income = 'Y'
                AND deleted_at IS NULL
                AND Ch_Date <= ?
            GROUP BY CV_No

            UNION ALL

            -- 5. HIGH VALUE PENDING APPROVALS
            SELECT
                'High Value Pending Approvals' AS liability_type,
                CONCAT('Voucher: ', COALESCE(CV_No, 'N/A')) AS case_or_reference_no,
                SUM(Amount) AS amount_rs,
                CASE
                    WHEN MAX(Status) = 12 THEN 'Pending HOD Approval'
                    WHEN MAX(Status) = 13 THEN 'Pending Account Verification'
                    WHEN MAX(Status) = 14 THEN 'Pending Final Approval'
                    ELSE 'Pending'
                END AS status,
                CONCAT('Department: ', COALESCE(MAX(Dpt_Id), 0), ' - Amount: ', FORMAT(SUM(Amount), 2)) AS remarks,
                MAX(DATE_ADD(Ch_Date, INTERVAL 15 DAY)) AS expected_resolution_date
            FROM chl_voucher
            WHERE Amount > 1000000
                AND Status IN (10, 11, 12, 13, 14)
                AND (Is_income = 'N' OR Is_income IS NULL)
                AND deleted_at IS NULL
                AND Ch_Date <= ?
            GROUP BY CV_No

            UNION ALL

            -- 6. CHEQUE RETURN / DISHONORED TRANSACTIONS
            SELECT
                'Cheque Return / Dishonored' AS liability_type,
                CONCAT('Cheque No: ', COALESCE(Chq_No, 'N/A')) AS case_or_reference_no,
                SUM(Amount) AS amount_rs,
                'Disputed - Under Resolution' AS status,
                CONCAT('Bank: ', COALESCE(MAX(IFSC_Code), 'N/A'), ' - ', COALESCE(MAX(Status_Remark), 'Cheque returned')) AS remarks,
                MAX(DATE_ADD(Chq_Date, INTERVAL 45 DAY)) AS expected_resolution_date
            FROM chl_voucher
            WHERE Chq_No IS NOT NULL
                AND (Status IN (10, 11) OR Status_Remark LIKE '%return%' OR Status_Remark LIKE '%dishonor%')
                AND deleted_at IS NULL
                AND Ch_Date <= ?
            GROUP BY Chq_No

            UNION ALL

            -- 7. LAND ACQUISITION DISPUTES
            SELECT
                'Land Acquisition Disputes' AS liability_type,
                CONCAT('Ref: ', COALESCE(MAX(Remarks), 'Pending')) AS case_or_reference_no,
                SUM(Amount) AS amount_rs,
                'Pending' AS status,
                CONCAT('Remarks: ', COALESCE(MAX(Remarks), 'N/A')) AS remarks,
                MAX(DATE_ADD(Ch_Date, INTERVAL 180 DAY)) AS expected_resolution_date
            FROM chl_voucher
            WHERE (Remarks LIKE '%land%' OR Remarks LIKE '%acquisition%')
                AND deleted_at IS NULL
                AND Ch_Date <= ?

            UNION ALL

            -- 8. EMPLOYEE LEGAL CLAIMS
            SELECT
                'Employee Legal Claims' AS liability_type,
                CONCAT('Emp Claim Ref: ', COALESCE(MAX(Status_Remark), 'Pending')) AS case_or_reference_no,
                SUM(Penalty_Amount) AS amount_rs,
                'Pending - Under Inquiry' AS status,
                'Employee related claims from vouchers' AS remarks,
                MAX(DATE_ADD(Ch_Date, INTERVAL 60 DAY)) AS expected_resolution_date
            FROM chl_voucher
            WHERE (Remarks LIKE '%employee%' OR Remarks LIKE '%staff%' OR Remarks LIKE '%salary%')
                AND Penalty_Amount > 0
                AND deleted_at IS NULL
                AND Ch_Date <= ?
        ) AS contingent_liabilities
        WHERE amount_rs > 0
        ORDER BY amount_rs DESC
    ";

        try {
            $results = DB::select($query, [
                $asOnDate,
                $asOnDate,
                $asOnDate,
                $asOnDate,
                $asOnDate,
                $asOnDate,
                $asOnDate,
                $asOnDate
            ]);

            $totalContingentLiability = 0;
            $formattedResults = [];
            $summaryByType = [];

            foreach ($results as $index => $row) {
                $amount = (float) $row->amount_rs;
                $totalContingentLiability += $amount;

                $formattedResults[] = [
                    'sr_no' => $index + 1,
                    'particulars' => $row->liability_type,
                    'reference_no' => $row->case_or_reference_no,
                    'amount_rs' => $amount,
                    'amount_formatted' => number_format($amount, 2),
                    'status' => $row->status,
                    'remarks' => $row->remarks,
                    'expected_resolution' => $row->expected_resolution_date ? date('d/m/Y', strtotime($row->expected_resolution_date)) : 'N/A'
                ];

                $type = $row->liability_type;
                if (!isset($summaryByType[$type])) {
                    $summaryByType[$type] = ['count' => 0, 'amount' => 0];
                }
                $summaryByType[$type]['count']++;
                $summaryByType[$type]['amount'] += $amount;
            }

            $summaryArray = [];
            foreach ($summaryByType as $type => $data) {
                $summaryArray[] = [
                    'liability_type' => $type,
                    'number_of_cases' => $data['count'],
                    'amount_rs' => $data['amount'],
                    'amount_formatted' => number_format($data['amount'], 2)
                ];
            }

            return response()->json([
                'success' => true,
                'message' => 'Contingent Liabilities Register retrieved successfully',
                'data' => [
                    'report_name' => 'STATEMENT OF CONTINGENT LIABILITIES',
                    'municipal_corporation' => 'Gandhinagar Municipal Corporation',
                    'financial_year' => $fyYear,
                    'as_on_date' => date('d/m/Y', strtotime($asOnDate)),
                    'generated_on' => now()->format('d/m/Y H:i:s'),
                    'liabilities' => $formattedResults,
                    'summary_by_type' => $summaryArray,
                    'total' => [
                        'total_liabilities' => $totalContingentLiability,
                        'total_liabilities_formatted' => number_format($totalContingentLiability, 2),
                        'total_cases' => count($formattedResults)
                    ]
                ]
            ], 200);
        } catch (\Exception $e) {

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

    public function getCashFlowStatement(Request $request)
    {
        
        $validator = Validator::make($request->all(), [
            'start_date' => 'required|date|date_format:Y-m-d',
            'end_date' => 'required|date|date_format:Y-m-d|after_or_equal:start_date',
        ]);

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

        $startDate = $request->input('start_date');
        $endDate = $request->input('end_date');

        
        $approvedStatuses = [12, 13, 14, 15, 24, 25];
        $statusPlaceholders = implode(',', array_fill(0, count($approvedStatuses), '?'));

        
        $query = "
            SELECT
                'A. CASH FLOW FROM OPERATING ACTIVITIES' AS section,
                sub_section,
                amount,
                FORMAT(amount, 2) AS formatted_amount
            FROM (
                -- Revenue Receipts (Operating Inflows)
                SELECT
                    'Revenue Receipts' AS sub_section,
                    COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND (Is_income = 'Y' OR Is_income IS NULL)
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Revenue Payments (Operating Outflows)
                SELECT
                    'Revenue Payments' AS sub_section,
                    -COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND (Is_income = 'N' OR Is_income IS NULL)
                    AND Lg_Id IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Salary%'
                            OR Lg_Name LIKE '%Wages%'
                            OR Lg_Name LIKE '%Repair%'
                            OR Lg_Name LIKE '%Maintenance%'
                            OR Lg_Name LIKE '%Office%'
                            OR Lg_Name LIKE '%Electricity%'
                            OR Lg_Name LIKE '%Exp%'
                            OR Lg_Name LIKE '%Petrol%'
                            OR Lg_Name LIKE '%Diesel%'
                            OR Lg_Name LIKE '%Cleaning%'
                    )
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Operating Surplus before Working Capital Changes
                SELECT
                    'Operating Surplus before Working Capital Changes' AS sub_section,
                    COALESCE(SUM(CASE
                        WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                        ELSE -Amount
                    END), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Net Cash from Operating Activities
                SELECT
                    'Net Cash from Operating Activities (A)' AS sub_section,
                    COALESCE(SUM(CASE
                        WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                        ELSE -Amount
                    END), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})
            ) AS operating

            UNION ALL

            SELECT
                'B. CASH FLOW FROM INVESTING ACTIVITIES' AS section,
                sub_section,
                amount,
                FORMAT(amount, 2) AS formatted_amount
            FROM (
                -- Purchase of Fixed Assets (Capital Payments)
                SELECT
                    'Purchase of Fixed Assets' AS sub_section,
                    -COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND (Is_income = 'N' OR Is_income IS NULL)
                    AND Lg_Id IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Asset%'
                            OR Lg_Name LIKE '%Equipment%'
                            OR Lg_Name LIKE '%Vehicle%'
                            OR Lg_Name LIKE '%Machinery%'
                            OR Lg_Name LIKE '%Construction%'
                            OR Lg_Name LIKE '%Building%'
                            OR Lg_Name LIKE '%Computer%'
                            OR Lg_Name LIKE '%Furniture%'
                    )
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Capital Work in Progress
                SELECT
                    'Capital Work in Progress' AS sub_section,
                    -COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND (Is_income = 'N' OR Is_income IS NULL)
                    AND Lg_Id IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Work%'
                            OR Lg_Name LIKE '%Project%'
                            OR Lg_Name LIKE '%Development%'
                    )
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Sale of Fixed Assets
                SELECT
                    'Sale of Fixed Assets' AS sub_section,
                    COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND Is_income = 'Y'
                    AND Lg_Id IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Sale%'
                            OR Lg_Name LIKE '%Disposal%'
                            OR Lg_Name LIKE '%Asset Sale%'
                    )
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Interest Received
                SELECT
                    'Interest Received' AS sub_section,
                    COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND Is_income = 'Y'
                    AND Lg_Id IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Interest%'
                            OR Lg_Name LIKE '%Bank Interest%'
                    )
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Net Cash from Investing Activities
                SELECT
                    'Net Cash Used in Investing Activities (B)' AS sub_section,
                    COALESCE(SUM(CASE
                        WHEN Lg_Id IN (
                            SELECT Lg_Id FROM ledger_master
                            WHERE Lg_Name LIKE '%Asset%'
                                OR Lg_Name LIKE '%Equipment%'
                                OR Lg_Name LIKE '%Vehicle%'
                                OR Lg_Name LIKE '%Construction%'
                        ) AND (Is_income = 'N' OR Is_income IS NULL) THEN -Amount
                        WHEN Lg_Id IN (
                            SELECT Lg_Id FROM ledger_master
                            WHERE Lg_Name LIKE '%Sale%'
                                OR Lg_Name LIKE '%Interest%'
                        ) AND Is_income = 'Y' THEN Amount
                        ELSE 0
                    END), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})
            ) AS investing

            UNION ALL

            SELECT
                'C. CASH FLOW FROM FINANCING ACTIVITIES' AS section,
                sub_section,
                amount,
                FORMAT(amount, 2) AS formatted_amount
            FROM (
                -- Government Grants Received
                SELECT
                    'Government Grants' AS sub_section,
                    COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND (Is_income = 'Y' OR Is_income IS NULL)
                    AND Lg_Id IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Grant%'
                            OR Lg_Name LIKE '%Subsidy%'
                    )
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Loans Received
                SELECT
                    'Loans Received' AS sub_section,
                    COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND Is_income = 'Y'
                    AND Lg_Id IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Loan%'
                            OR Lg_Name LIKE '%Borrowing%'
                    )
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Loan Repayment
                SELECT
                    'Loan Repayment' AS sub_section,
                    -COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND (Is_income = 'N' OR Is_income IS NULL)
                    AND Lg_Id IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Loan%'
                            OR Lg_Name LIKE '%Repayment%'
                            OR Lg_Name LIKE '%Principal%'
                    )
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Interest Paid
                SELECT
                    'Interest Paid' AS sub_section,
                    -COALESCE(SUM(Amount), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND (Is_income = 'N' OR Is_income IS NULL)
                    AND Lg_Id IN (
                        SELECT Lg_Id FROM ledger_master
                        WHERE Lg_Name LIKE '%Interest%'
                            AND Lg_Name NOT LIKE '%Received%'
                            AND Lg_Name NOT LIKE '%Income%'
                    )
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                -- Net Cash from Financing Activities
                SELECT
                    'Net Cash from Financing Activities (C)' AS sub_section,
                    COALESCE(SUM(CASE
                        WHEN Lg_Id IN (
                            SELECT Lg_Id FROM ledger_master
                            WHERE Lg_Name LIKE '%Grant%' OR Lg_Name LIKE '%Loan%'
                        ) AND (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                        WHEN Lg_Id IN (
                            SELECT Lg_Id FROM ledger_master
                            WHERE Lg_Name LIKE '%Repayment%' OR (Lg_Name LIKE '%Interest%' AND Lg_Name NOT LIKE '%Received%')
                        ) AND (Is_income = 'N' OR Is_income IS NULL) THEN -Amount
                        ELSE 0
                    END), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})
            ) AS financing

            UNION ALL

            -- NET CASH FLOW (A+B+C)
            SELECT
                'D. NET CASH FLOW (A+B+C)' AS section,
                'Total' AS sub_section,
                COALESCE(SUM(CASE
                    WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                    ELSE -Amount
                END), 0) AS amount,
                FORMAT(COALESCE(SUM(CASE
                    WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                    ELSE -Amount
                END), 0), 2) AS formatted_amount
            FROM chl_voucher
            WHERE Ch_Date BETWEEN ? AND ?
                AND deleted_at IS NULL
                AND Status IN ({$statusPlaceholders})

            UNION ALL

            -- OPENING CASH BALANCE
            SELECT
                'E. OPENING CASH BALANCE' AS section,
                'Total' AS sub_section,
                COALESCE(SUM(CASE
                    WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                    ELSE -Amount
                END), 0) AS amount,
                FORMAT(COALESCE(SUM(CASE
                    WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                    ELSE -Amount
                END), 0), 2) AS formatted_amount
            FROM chl_voucher
            WHERE Ch_Date < ?
                AND deleted_at IS NULL
                AND Status IN ({$statusPlaceholders})

            UNION ALL

            -- CLOSING CASH BALANCE
            SELECT
                'F. CLOSING CASH BALANCE (D+E)' AS section,
                'Total' AS sub_section,
                COALESCE((
                    SELECT COALESCE(SUM(CASE
                        WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                        ELSE -Amount
                    END), 0) FROM chl_voucher
                    WHERE Ch_Date BETWEEN ? AND ?
                        AND deleted_at IS NULL
                        AND Status IN ({$statusPlaceholders})
                ) + (
                    SELECT COALESCE(SUM(CASE
                        WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                        ELSE -Amount
                    END), 0) FROM chl_voucher
                    WHERE Ch_Date < ?
                        AND deleted_at IS NULL
                        AND Status IN ({$statusPlaceholders})
                ), 0) AS amount,
                FORMAT(COALESCE((
                    SELECT COALESCE(SUM(CASE
                        WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                        ELSE -Amount
                    END), 0) FROM chl_voucher
                    WHERE Ch_Date BETWEEN ? AND ?
                        AND deleted_at IS NULL
                        AND Status IN ({$statusPlaceholders})
                ) + (
                    SELECT COALESCE(SUM(CASE
                        WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount
                        ELSE -Amount
                    END), 0) FROM chl_voucher
                    WHERE Ch_Date < ?
                        AND deleted_at IS NULL
                        AND Status IN ({$statusPlaceholders})
                ), 0), 2) AS formatted_amount
        ";

        
        $bindings = array_merge(
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            
            [$startDate],
            $approvedStatuses,
            
            [$startDate, $endDate],
            $approvedStatuses,
            [$startDate],
            $approvedStatuses,
            [$startDate, $endDate],
            $approvedStatuses,
            [$startDate],
            $approvedStatuses
        );

        try {
            $results = DB::select($query, $bindings);

            $formattedResults = [];
            $currentSection = '';
            $sectionData = [];

            foreach ($results as $row) {
                if ($row->section != $currentSection) {
                    if (!empty($sectionData)) {
                        $formattedResults[] = $sectionData;
                    }
                    $currentSection = $row->section;
                    $sectionData = [
                        'section' => $row->section,
                        'items' => []
                    ];
                }
                $sectionData['items'][] = [
                    'sub_section' => $row->sub_section,
                    'amount' => (float) $row->amount,
                    'formatted_amount' => $row->formatted_amount
                ];
            }
            if (!empty($sectionData)) {
                $formattedResults[] = $sectionData;
            }

            $summaryQuery = "
                SELECT
                    'Total Receipts (All)' AS category,
                    COALESCE(SUM(CASE WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount ELSE 0 END), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                SELECT
                    'Total Payments (All)' AS category,
                    COALESCE(SUM(CASE WHEN (Is_income = 'N' OR Is_income IS NULL) THEN Amount ELSE 0 END), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                SELECT
                    'Net Cash Flow' AS category,
                    COALESCE(SUM(CASE WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount ELSE -Amount END), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date BETWEEN ? AND ?
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                SELECT
                    'Opening Balance (Cash + Bank)' AS category,
                    COALESCE(SUM(CASE WHEN Py_Mode IN ('Cash', 'Pos', 'Bank', 'Cheque', 'DD', 'Online', 'MChallan', 'Card') THEN
                        CASE WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount ELSE -Amount END
                    ELSE 0 END), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date < ?
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})

                UNION ALL

                SELECT
                    'Closing Balance (Cash + Bank)' AS category,
                    COALESCE(SUM(CASE WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount ELSE -Amount END), 0) AS amount
                FROM chl_voucher
                WHERE Ch_Date <= ?
                    AND deleted_at IS NULL
                    AND Status IN ({$statusPlaceholders})
            ";

            $summaryBindings = array_merge(
                [$startDate, $endDate],
                $approvedStatuses,
                [$startDate, $endDate],
                $approvedStatuses,
                [$startDate, $endDate],
                $approvedStatuses,
                [$startDate],
                $approvedStatuses,
                [$endDate],
                $approvedStatuses
            );

            $summaryResults = DB::select($summaryQuery, $summaryBindings);

            $summary = [];
            foreach ($summaryResults as $row) {
                $summary[] = [
                    'category' => $row->category,
                    'amount' => (float) $row->amount,
                    'formatted_amount' => number_format($row->amount, 2)
                ];
            }

            return response()->json([
                'success' => true,
                'message' => 'Cash Flow Statement retrieved successfully',
                'data' => [
                    'report_name' => 'CASH FLOW STATEMENT',
                    'period' => [
                        'start_date' => $startDate,
                        'end_date' => $endDate
                    ],
                    'generated_on' => now()->format('d/m/Y H:i:s'),
                    'cash_flow_statements' => $formattedResults,
                    'summary' => $summary
                ]
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve Cash Flow Statement',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    /**

     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getCashFlowSummary(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'start_date' => 'required|date|date_format:Y-m-d',
            'end_date' => 'required|date|date_format:Y-m-d|after_or_equal:start_date',
        ]);

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

        $startDate = $request->input('start_date');
        $endDate = $request->input('end_date');

        $approvedStatuses = [12, 13, 14, 15, 24, 25];
        $statusPlaceholders = implode(',', array_fill(0, count($approvedStatuses), '?'));

        $query = "
            SELECT
                'Total Receipts (All)' AS category,
                COALESCE(SUM(CASE WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount ELSE 0 END), 0) AS amount
            FROM chl_voucher
            WHERE Ch_Date BETWEEN ? AND ?
                AND deleted_at IS NULL
                AND Status IN ({$statusPlaceholders})

            UNION ALL

            SELECT
                'Total Payments (All)' AS category,
                COALESCE(SUM(CASE WHEN (Is_income = 'N' OR Is_income IS NULL) THEN Amount ELSE 0 END), 0) AS amount
            FROM chl_voucher
            WHERE Ch_Date BETWEEN ? AND ?
                AND deleted_at IS NULL
                AND Status IN ({$statusPlaceholders})

            UNION ALL

            SELECT
                'Net Cash Flow' AS category,
                COALESCE(SUM(CASE WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount ELSE -Amount END), 0) AS amount
            FROM chl_voucher
            WHERE Ch_Date BETWEEN ? AND ?
                AND deleted_at IS NULL
                AND Status IN ({$statusPlaceholders})

            UNION ALL

            SELECT
                'Opening Balance (Cash + Bank)' AS category,
                COALESCE(SUM(CASE WHEN Py_Mode IN ('Cash', 'Pos', 'Bank', 'Cheque', 'DD', 'Online', 'MChallan', 'Card') THEN
                    CASE WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount ELSE -Amount END
                ELSE 0 END), 0) AS amount
            FROM chl_voucher
            WHERE Ch_Date < ?
                AND deleted_at IS NULL
                AND Status IN ({$statusPlaceholders})

            UNION ALL

            SELECT
                'Closing Balance (Cash + Bank)' AS category,
                COALESCE(SUM(CASE WHEN (Is_income = 'Y' OR Is_income IS NULL) THEN Amount ELSE -Amount END), 0) AS amount
            FROM chl_voucher
            WHERE Ch_Date <= ?
                AND deleted_at IS NULL
                AND Status IN ({$statusPlaceholders})
        ";

        $bindings = array_merge(
            [$startDate, $endDate],
            $approvedStatuses,
            [$startDate, $endDate],
            $approvedStatuses,
            [$startDate, $endDate],
            $approvedStatuses,
            [$startDate],
            $approvedStatuses,
            [$endDate],
            $approvedStatuses
        );

        try {
            $results = DB::select($query, $bindings);

            $summary = [];
            foreach ($results as $row) {
                $summary[] = [
                    'category' => $row->category,
                    'amount' => (float) $row->amount,
                    'formatted_amount' => number_format($row->amount, 2)
                ];
            }

            return response()->json([
                'success' => true,
                'message' => 'Cash Flow Summary retrieved successfully',
                'data' => [
                    'report_name' => 'CASH FLOW SUMMARY',
                    'period' => [
                        'start_date' => $startDate,
                        'end_date' => $endDate
                    ],
                    'generated_on' => now()->format('d/m/Y H:i:s'),
                    'summary' => $summary
                ]
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve Cash Flow Summary',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }
}
