ALTER PROCEDURE dbo.getPaymentReceiptDetailsByAccountName    
(    
    @AccountId INT = NULL  
)    
AS    
BEGIN    
    SET NOCOUNT ON;    

    DECLARE 
        @OpeningBalance DECIMAL(18,2) = 0,
        @ClosingBalance DECIMAL(18,2) = 0,
        @AccountName NVARCHAR(200) = '',
        @MasterOpening DECIMAL(18,2) = 0,
        @OpeningType VARCHAR(2) = 'DR',
        @MinDate DATE;

    --------------------------------------------------
    -- 🔹 ACCOUNT MASTER
    --------------------------------------------------
    SELECT 
        @AccountName = accountName,
        @MasterOpening = ISNULL(opBalance, 0),
        @OpeningType = ISNULL(openingBalanceType, 'DR')
    FROM tbAccountMaster
    WHERE _id = @AccountId;

    IF (@OpeningType = 'CR')
        SET @MasterOpening = -@MasterOpening;

    --------------------------------------------------
    -- 🔹 MIN DATE (SAFE)
    --------------------------------------------------
    SELECT 
        @MinDate = MIN(TRY_CONVERT(DATE, M.ReceiptDate, 105))
    FROM tbPaymentReceiptMaster M
    LEFT JOIN tbPaymentReceiptDetails D 
        ON M.PaymentReceiptId = D.PaymentReceiptId
    WHERE 
        (M.AccountName = @AccountId OR D.AccountId = @AccountId)
        AND TRY_CONVERT(DATE, M.ReceiptDate, 105) IS NOT NULL;

    --------------------------------------------------
    -- 🔹 OPENING BALANCE
    --------------------------------------------------
    IF @MinDate IS NULL
    BEGIN
        SET @OpeningBalance = @MasterOpening;
    END
    ELSE
    BEGIN
        SELECT 
            @OpeningBalance = @MasterOpening + ISNULL(SUM(
                CASE   
                    WHEN M.VoucherNo = 'Receipt' 
                        THEN TRY_CAST(REPLACE(D.Amount, ',', '') AS DECIMAL(18,2))  
                    WHEN M.VoucherNo = 'Payment' 
                        THEN -TRY_CAST(REPLACE(D.Amount, ',', '') AS DECIMAL(18,2))  
                    ELSE 0  
                END
            ), 0)
        FROM tbPaymentReceiptMaster M
        LEFT JOIN tbPaymentReceiptDetails D  
            ON M.PaymentReceiptId = D.PaymentReceiptId  
        WHERE 
            (M.AccountName = @AccountId OR D.AccountId = @AccountId)
            AND TRY_CONVERT(DATE, M.ReceiptDate, 105) < @MinDate;
    END

    --------------------------------------------------
    -- 🔹 CLOSING BALANCE
    --------------------------------------------------
    IF @MinDate IS NULL
    BEGIN
        SET @ClosingBalance = @MasterOpening;
    END
    ELSE
    BEGIN
        SELECT 
            @ClosingBalance = @OpeningBalance +
            ISNULL(SUM(
                CASE   
                    WHEN M.VoucherNo = 'Receipt' 
                        THEN TRY_CAST(REPLACE(D.Amount, ',', '') AS DECIMAL(18,2))  
                    WHEN M.VoucherNo = 'Payment' 
                        THEN -TRY_CAST(REPLACE(D.Amount, ',', '') AS DECIMAL(18,2))  
                    ELSE 0  
                END
            ), 0)
        FROM tbPaymentReceiptMaster M
        LEFT JOIN tbPaymentReceiptDetails D  
            ON M.PaymentReceiptId = D.PaymentReceiptId  
        WHERE 
            (M.AccountName = @AccountId OR D.AccountId = @AccountId);
    END

    --------------------------------------------------
    -- 🔹 FINAL OUTPUT
    --------------------------------------------------
    SELECT    
        @AccountName AS accountName,

        ABS(@OpeningBalance) AS opBalance,
        CASE 
            WHEN @OpeningBalance >= 0 THEN 'DR'
            ELSE 'CR'
        END AS openingBalanceType,

        --------------------------------------------------
        -- 🔹 DETAILS (DATE SAFE + JSON SAFE)
        --------------------------------------------------
        ISNULL((
            SELECT    
                M.PaymentReceiptId AS MasterPaymentReceiptId,
                M.AccountName AS MasterAccountId,

                D.PaymentReceiptDetailId,  
                D.PaymentReceiptId AS DetailPaymentReceiptId,  
                D.AccountId,  

                A.accountName AS AccountName,  
                M.VoucherNo,  

                -- ✅ SAFE DATE
                TRY_CONVERT(DATE, M.ReceiptDate, 105) AS ReceiptDate,  

                D.Station,  
                D.Narration,  

                TRY_CAST(REPLACE(D.Amount, ',', '') AS DECIMAL(18,2)) AS Amount,  

                D.CrOrDr,  
                D.CompanyId,  

                @OpeningBalance +
                SUM(  
                    CASE   
                        WHEN M.VoucherNo = 'Receipt' 
                            THEN TRY_CAST(REPLACE(D.Amount, ',', '') AS DECIMAL(18,2))  
                        WHEN M.VoucherNo = 'Payment' 
                            THEN -TRY_CAST(REPLACE(D.Amount, ',', '') AS DECIMAL(18,2))  
                        ELSE 0  
                    END  
                ) OVER (  
                    ORDER BY 
                        TRY_CONVERT(DATE, M.ReceiptDate, 105),
                        D.PaymentReceiptDetailId  
                ) AS RunningTotal  

            FROM tbPaymentReceiptMaster M
            LEFT JOIN tbPaymentReceiptDetails D  
                ON M.PaymentReceiptId = D.PaymentReceiptId  
            LEFT JOIN tbAccountMaster A   
                ON A._id = D.AccountId  

            WHERE 
                (M.AccountName = @AccountId OR D.AccountId = @AccountId)

            ORDER BY 
                TRY_CONVERT(DATE, M.ReceiptDate, 105),
                D.PaymentReceiptDetailId  

            FOR JSON PATH, INCLUDE_NULL_VALUES  
        ), '[]') AS Details,  

        ABS(@ClosingBalance) AS clsBalance,
        CASE 
            WHEN @ClosingBalance >= 0 THEN 'DR'
            ELSE 'CR'
        END AS closingBalanceType

    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;  

END