Stored Procedure های کامل برای مدیریت سیستم صندوق وام

DELIMITER $$

CREATE PROCEDURE sp_GetMembers(
    IN p_IsActive BOOLEAN,
    IN p_MemberType ENUM('Normal', 'Premium', 'Gold'),
    IN p_SearchTerm NVARCHAR(100),
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalPages INT
)
BEGIN
    -- محاسبه رکوردهای کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM Members 
    WHERE (p_IsActive IS NULL OR IsActive = p_IsActive)
    AND (p_MemberType IS NULL OR MemberType = p_MemberType)
    AND (p_SearchTerm IS NULL OR 
         FirstName LIKE CONCAT('%', p_SearchTerm, '%') OR 
         LastName LIKE CONCAT('%', p_SearchTerm, '%') OR
         MemberCode LIKE CONCAT('%', p_SearchTerm, '%') OR
         NationalCode LIKE CONCAT('%', p_SearchTerm, '%'));
    
    SET p_TotalPages = CEIL(p_TotalRecords / p_PageSize);
    
    -- گرفتن لیست اعضا
    SELECT 
        m.MemberID,
        m.MemberCode,
        m.NationalCode,
        m.FirstName,
        m.LastName,
        m.FatherName,
        m.Mobile,
        m.Email,
        m.City,
        m.Province,
        m.EmploymentStatus,
        m.MemberType,
        m.JoinDate,
        m.IsActive,
        m.CreditScore,
        m.MaxLoanAmount,
        DATEDIFF(CURDATE(), m.JoinDate) as MembershipDays,
        (SELECT COUNT(*) FROM Loans WHERE MemberID = m.MemberID AND Status = 'Active') as ActiveLoans,
        (SELECT COUNT(*) FROM Loans WHERE MemberID = m.MemberID AND Status IN ('Completed', 'SettledEarly')) as CompletedLoans,
        (SELECT COALESCE(SUM(RemainingPrincipal), 0) FROM Loans WHERE MemberID = m.MemberID AND Status = 'Active') as TotalRemainingDebt
    FROM Members m
    WHERE (p_IsActive IS NULL OR m.IsActive = p_IsActive)
    AND (p_MemberType IS NULL OR m.MemberType = p_MemberType)
    AND (p_SearchTerm IS NULL OR 
         m.FirstName LIKE CONCAT('%', p_SearchTerm, '%') OR 
         m.LastName LIKE CONCAT('%', p_SearchTerm, '%') OR
         m.MemberCode LIKE CONCAT('%', p_SearchTerm, '%') OR
         m.NationalCode LIKE CONCAT('%', p_SearchTerm, '%'))
    ORDER BY m.FirstName, m.LastName
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetMemberInfo(
    IN p_MemberID INT
)
BEGIN
    -- اطلاعات اصلی عضو
    SELECT 
        m.*,
        DATEDIFF(CURDATE(), m.JoinDate) as MembershipDays,
        TIMESTAMPDIFF(YEAR, m.BirthDate, CURDATE()) as Age
    FROM Members m
    WHERE m.MemberID = p_MemberID;
    
    -- تاریخچه امتیاز اعتباری
    SELECT 
        cs.ScoreID,
        cs.Score,
        cs.ScoreType,
        cs.Description,
        cs.EffectiveDate,
        cs.ExpiryDate,
        fm.FirstName as CreatedByFirstName,
        fm.LastName as CreatedByLastName
    FROM CreditScores cs
    LEFT JOIN FundManagers fm ON cs.CreatedBy = fm.ManagerID
    WHERE cs.MemberID = p_MemberID
    ORDER BY cs.EffectiveDate DESC, cs.ScoreID DESC;
    
    -- وام‌های جاری
    SELECT 
        l.LoanID,
        l.LoanNumber,
        lt.TypeName as LoanType,
        l.LoanAmount,
        l.InterestRate,
        l.Term,
        l.StartDate,
        l.EndDate,
        l.RemainingPrincipal,
        l.MonthlyInstallment,
        l.Status,
        l.NextDueDate,
        DATEDIFF(l.NextDueDate, CURDATE()) as DaysToNextDue
    FROM Loans l
    INNER JOIN LoanTypes lt ON l.LoanTypeID = lt.LoanTypeID
    WHERE l.MemberID = p_MemberID
    ORDER BY l.StartDate DESC;
    
    -- آمار وام‌ها
    SELECT 
        COUNT(*) as TotalLoans,
        COUNT(CASE WHEN Status = 'Active' THEN 1 END) as ActiveLoans,
        COUNT(CASE WHEN Status IN ('Completed', 'SettledEarly') THEN 1 END) as CompletedLoans,
        COUNT(CASE WHEN Status = 'Defaulted' THEN 1 END) as DefaultedLoans,
        COALESCE(SUM(CASE WHEN Status = 'Active' THEN RemainingPrincipal ELSE 0 END), 0) as TotalRemainingDebt,
        COALESCE(SUM(LoanAmount), 0) as TotalBorrowedAmount
    FROM Loans
    WHERE MemberID = p_MemberID;
    
    -- اقساط overdue
    SELECT 
        i.InstallmentID,
        i.InstallmentNumber,
        i.DueDate,
        i.TotalAmount,
        i.PaidAmount,
        i.PenaltyAmount,
        i.DaysOverdue,
        l.LoanNumber
    FROM Installments i
    INNER JOIN Loans l ON i.LoanID = l.LoanID
    WHERE l.MemberID = p_MemberID 
    AND i.Status = 'Overdue'
    ORDER BY i.DueDate ASC;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_UpdateMember(
    IN p_MemberID INT,
    IN p_FirstName NVARCHAR(50),
    IN p_LastName NVARCHAR(50),
    IN p_FatherName NVARCHAR(50),
    IN p_Mobile VARCHAR(11),
    IN p_Email VARCHAR(100),
    IN p_Address TEXT,
    IN p_City NVARCHAR(50),
    IN p_Province NVARCHAR(50),
    IN p_PostalCode VARCHAR(10),
    IN p_EmploymentStatus ENUM('Employed', 'SelfEmployed', 'Unemployed', 'Retired'),
    IN p_JobTitle NVARCHAR(100),
    IN p_Employer NVARCHAR(100),
    IN p_MonthlyIncome DECIMAL(12,2),
    IN p_MaritalStatus ENUM('Single', 'Married', 'Divorced', 'Widowed'),
    IN p_MemberType ENUM('Normal', 'Premium', 'Gold'),
    IN p_IsActive BOOLEAN,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در به‌روزرسانی اطلاعات عضو';
    END;
    
    START TRANSACTION;
    
    UPDATE Members 
    SET 
        FirstName = p_FirstName,
        LastName = p_LastName,
        FatherName = p_FatherName,
        Mobile = p_Mobile,
        Email = p_Email,
        Address = p_Address,
        City = p_City,
        Province = p_Province,
        PostalCode = p_PostalCode,
        EmploymentStatus = p_EmploymentStatus,
        JobTitle = p_JobTitle,
        Employer = p_Employer,
        MonthlyIncome = p_MonthlyIncome,
        MaritalStatus = p_MaritalStatus,
        MemberType = p_MemberType,
        IsActive = p_IsActive,
        UpdatedAt = CURRENT_TIMESTAMP
    WHERE MemberID = p_MemberID;
    
    SET p_ResultMessage = 'اطلاعات عضو با موفقیت به‌روزرسانی شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetLoans(
    IN p_MemberID INT,
    IN p_LoanTypeID INT,
    IN p_Status ENUM('Active', 'Completed', 'Defaulted', 'SettledEarly', 'WrittenOff'),
    IN p_OverdueOnly BOOLEAN,
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalPages INT
)
BEGIN
    -- محاسبه رکوردهای کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM Loans l
    WHERE (p_MemberID IS NULL OR l.MemberID = p_MemberID)
    AND (p_LoanTypeID IS NULL OR l.LoanTypeID = p_LoanTypeID)
    AND (p_Status IS NULL OR l.Status = p_Status)
    AND (p_OverdueOnly = FALSE OR (l.Status = 'Active' AND l.NextDueDate < CURDATE()));
    
    SET p_TotalPages = CEIL(p_TotalRecords / p_PageSize);
    
    -- گرفتن لیست وام‌ها
    SELECT 
        l.LoanID,
        l.LoanNumber,
        m.MemberID,
        m.MemberCode,
        CONCAT(m.FirstName, ' ', m.LastName) as MemberName,
        m.NationalCode,
        lt.TypeName as LoanType,
        l.LoanAmount,
        l.InterestRate,
        l.Term,
        l.StartDate,
        l.EndDate,
        l.RemainingPrincipal,
        l.MonthlyInstallment,
        l.Status,
        l.NextDueDate,
        DATEDIFF(l.NextDueDate, CURDATE()) as DaysToNextDue,
        (SELECT COUNT(*) FROM Installments i WHERE i.LoanID = l.LoanID AND i.Status = 'Overdue') as OverdueInstallments,
        (SELECT COUNT(*) FROM Installments i WHERE i.LoanID = l.LoanID AND i.Status = 'Paid') as PaidInstallments,
        CASE 
            WHEN l.Status = 'Active' AND l.NextDueDate < CURDATE() THEN TRUE
            ELSE FALSE
        END as IsOverdue
    FROM Loans l
    INNER JOIN Members m ON l.MemberID = m.MemberID
    INNER JOIN LoanTypes lt ON l.LoanTypeID = lt.LoanTypeID
    WHERE (p_MemberID IS NULL OR l.MemberID = p_MemberID)
    AND (p_LoanTypeID IS NULL OR l.LoanTypeID = p_LoanTypeID)
    AND (p_Status IS NULL OR l.Status = p_Status)
    AND (p_OverdueOnly = FALSE OR (l.Status = 'Active' AND l.NextDueDate < CURDATE()))
    ORDER BY 
        CASE WHEN l.Status = 'Active' AND l.NextDueDate < CURDATE() THEN 0 ELSE 1 END,
        l.NextDueDate ASC,
        l.LoanID DESC
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetLoanDetails(
    IN p_LoanID INT
)
BEGIN
    -- اطلاعات اصلی وام
    SELECT 
        l.*,
        m.MemberCode,
        CONCAT(m.FirstName, ' ', m.LastName) as MemberName,
        m.NationalCode,
        m.Mobile,
        lt.TypeName as LoanType,
        lt.IsQardHasana,
        DATEDIFF(l.EndDate, CURDATE()) as DaysRemaining,
        (l.PaidPrincipal + l.PaidInterest) as TotalPaid,
        (l.TotalPayable - l.PaidPrincipal - l.PaidInterest) as TotalRemaining
    FROM Loans l
    INNER JOIN Members m ON l.MemberID = m.MemberID
    INNER JOIN LoanTypes lt ON l.LoanTypeID = lt.LoanTypeID
    WHERE l.LoanID = p_LoanID;
    
    -- اقساط
    SELECT 
        i.InstallmentID,
        i.InstallmentNumber,
        i.DueDate,
        i.PrincipalAmount,
        i.InterestAmount,
        i.TotalAmount,
        i.PaidAmount,
        i.PenaltyAmount,
        i.Status,
        i.PaidDate,
        i.DaysOverdue,
        i.PaymentMethod,
        CASE 
            WHEN i.DueDate < CURDATE() AND i.Status != 'Paid' THEN TRUE
            ELSE FALSE
        END as IsOverdue,
        (i.TotalAmount + i.PenaltyAmount - i.PaidAmount) as RemainingAmount
    FROM Installments i
    WHERE i.LoanID = p_LoanID
    ORDER BY i.InstallmentNumber;
    
    -- ضامن‌ها
    SELECT 
        g.GuarantorID,
        g.MemberID,
        m.MemberCode,
        CONCAT(m.FirstName, ' ', m.LastName) as GuarantorName,
        m.NationalCode as GuarantorNationalCode,
        m.Mobile as GuarantorMobile,
        g.Relationship,
        g.IsAccepted,
        g.AcceptedAt,
        m.CreditScore as GuarantorCreditScore
    FROM Guarantors g
    INNER JOIN Members m ON g.MemberID = m.MemberID
    WHERE g.ApplicationID = (SELECT ApplicationID FROM Loans WHERE LoanID = p_LoanID);
    
    -- پرداخت‌ها
    SELECT 
        p.PaymentID,
        p.PaymentNumber,
        p.PaymentDate,
        p.PaymentAmount,
        p.PrincipalAmount,
        p.InterestAmount,
        p.PenaltyAmount,
        p.PaymentMethod,
        p.BankName,
        p.CheckNumber,
        p.TransactionReference,
        p.IsConfirmed,
        CONCAT(fm.FirstName, ' ', fm.LastName) as ReceivedByName
    FROM Payments p
    LEFT JOIN FundManagers fm ON p.ReceivedBy = fm.ManagerID
    WHERE p.LoanID = p_LoanID
    ORDER BY p.PaymentDate DESC, p.PaymentID DESC;
    
    -- خلاصه پرداخت‌ها
    SELECT 
        COUNT(*) as TotalPayments,
        COALESCE(SUM(PaymentAmount), 0) as TotalPaidAmount,
        COALESCE(SUM(PrincipalAmount), 0) as TotalPaidPrincipal,
        COALESCE(SUM(InterestAmount), 0) as TotalPaidInterest,
        COALESCE(SUM(PenaltyAmount), 0) as TotalPaidPenalty
    FROM Payments
    WHERE LoanID = p_LoanID AND IsConfirmed = TRUE;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateLoan(
    IN p_ApplicationID INT,
    IN p_CreatedBy INT,
    OUT p_LoanID INT,
    OUT p_LoanNumber VARCHAR(50),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_MemberID INT;
    DECLARE v_LoanTypeID INT;
    DECLARE v_ApprovedAmount DECIMAL(15,2);
    DECLARE v_ApprovedTerm INT;
    DECLARE v_InterestRate DECIMAL(5,2);
    DECLARE v_MonthlyInstallment DECIMAL(15,2);
    DECLARE v_TotalPayable DECIMAL(15,2);
    DECLARE v_StartDate DATE;
    DECLARE v_EndDate DATE;
    DECLARE v_GracePeriod INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد وام';
        SET p_LoanID = -1;
    END;
    
    START TRANSACTION;
    
    -- گرفتن اطلاعات از درخواست تایید شده
    SELECT 
        a.MemberID, a.LoanTypeID, a.ApprovedAmount, a.ApprovedTerm,
        lt.InterestRate, lt.GracePeriod
    INTO v_MemberID, v_LoanTypeID, v_ApprovedAmount, v_ApprovedTerm, 
         v_InterestRate, v_GracePeriod
    FROM LoanApplications a
    INNER JOIN LoanTypes lt ON a.LoanTypeID = lt.LoanTypeID
    WHERE a.ApplicationID = p_ApplicationID AND a.Status = 'Approved';
    
    IF v_MemberID IS NULL THEN
        SET p_ResultMessage = 'درخواست وام یافت نشد یا تایید نشده است';
        SET p_LoanID = -1;
        ROLLBACK;
    ELSE
        -- تولید شماره وام
        SET p_LoanNumber = fn_GenerateLoanNumber();
        
        -- محاسبات وام
        SET v_StartDate = CURDATE();
        SET v_EndDate = DATE_ADD(v_StartDate, INTERVAL v_ApprovedTerm MONTH);
        
        IF v_InterestRate = 0 THEN -- قرض الحسنه
            SET v_MonthlyInstallment = v_ApprovedAmount / v_ApprovedTerm;
            SET v_TotalPayable = v_ApprovedAmount;
        ELSE -- وام با سود
            SET v_MonthlyInstallment = fn_CalculateMonthlyInstallment(v_ApprovedAmount, v_InterestRate, v_ApprovedTerm);
            SET v_TotalPayable = v_MonthlyInstallment * v_ApprovedTerm;
        END IF;
        
        -- ایجاد وام
        INSERT INTO Loans (
            LoanNumber, ApplicationID, MemberID, LoanTypeID, LoanAmount,
            InterestRate, Term, StartDate, EndDate, GracePeriod,
            MonthlyInstallment, TotalPayable, RemainingPrincipal,
            Status, NextDueDate, CreatedBy
        ) VALUES (
            p_LoanNumber, p_ApplicationID, v_MemberID, v_LoanTypeID, v_ApprovedAmount,
            v_InterestRate, v_ApprovedTerm, v_StartDate, v_EndDate, v_GracePeriod,
            v_MonthlyInstallment, v_TotalPayable, v_ApprovedAmount,
            'Active', DATE_ADD(v_StartDate, INTERVAL (v_GracePeriod + 1) MONTH), p_CreatedBy
        );
        
        SET p_LoanID = LAST_INSERT_ID();
        
        -- ایجاد اقساط
        CALL sp_CreateInstallments(p_LoanID, v_ApprovedAmount, v_InterestRate, 
                                 v_ApprovedTerm, v_StartDate, v_GracePeriod);
        
        -- به‌روزرسانی وضعیت درخواست
        UPDATE LoanApplications 
        SET Status = 'Approved' 
        WHERE ApplicationID = p_ApplicationID;
        
        SET p_ResultMessage = 'وام با موفقیت ایجاد شد';
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE FUNCTION fn_GenerateLoanNumber() 
RETURNS VARCHAR(50)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_Year VARCHAR(4);
    DECLARE v_Month VARCHAR(2);
    DECLARE v_Sequence INT;
    
    SET v_Year = YEAR(CURDATE());
    SET v_Month = LPAD(MONTH(CURDATE()), 2, '0');
    
    SELECT COALESCE(MAX(CAST(SUBSTRING(LoanNumber, -6) AS UNSIGNED)), 0) + 1 INTO v_Sequence
    FROM Loans
    WHERE YEAR(CreatedAt) = v_Year AND MONTH(CreatedAt) = v_Month;
    
    RETURN CONCAT('LN', v_Year, v_Month, LPAD(v_Sequence, 6, '0'));
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateInstallments(
    IN p_LoanID INT,
    IN p_LoanAmount DECIMAL(15,2),
    IN p_InterestRate DECIMAL(5,2),
    IN p_Term INT,
    IN p_StartDate DATE,
    IN p_GracePeriod INT
)
BEGIN
    DECLARE v_InstallmentNumber INT DEFAULT 1;
    DECLARE v_DueDate DATE;
    DECLARE v_PrincipalAmount DECIMAL(15,2);
    DECLARE v_InterestAmount DECIMAL(15,2);
    DECLARE v_TotalAmount DECIMAL(15,2);
    DECLARE v_RemainingPrincipal DECIMAL(15,2) DEFAULT p_LoanAmount;
    
    -- محاسبه اقساط
    IF p_InterestRate = 0 THEN -- قرض الحسنه
        SET v_PrincipalAmount = ROUND(p_LoanAmount / p_Term, 2);
    ELSE -- وام با سود
        -- در اینجا می‌توان از روش‌های مختلف محاسبه اقساط استفاده کرد
        SET v_PrincipalAmount = ROUND(p_LoanAmount / p_Term, 2);
        SET v_InterestAmount = ROUND((p_LoanAmount * p_InterestRate / 100) / 12, 2);
    END IF;
    
    SET v_TotalAmount = v_PrincipalAmount + v_InterestAmount;
    
    -- ایجاد اقساط
    WHILE v_InstallmentNumber <= p_Term DO
        -- محاسبه تاریخ سررسید (با در نظر گرفتن دوره تنفس)
        SET v_DueDate = DATE_ADD(p_StartDate, INTERVAL (p_GracePeriod + v_InstallmentNumber) MONTH);
        
        INSERT INTO Installments (
            LoanID, InstallmentNumber, DueDate, PrincipalAmount,
            InterestAmount, TotalAmount, Status
        ) VALUES (
            p_LoanID, v_InstallmentNumber, v_DueDate, v_PrincipalAmount,
            v_InterestAmount, v_TotalAmount, 'Pending'
        );
        
        SET v_RemainingPrincipal = v_RemainingPrincipal - v_PrincipalAmount;
        SET v_InstallmentNumber = v_InstallmentNumber + 1;
    END WHILE;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreatePayment(
    IN p_LoanID INT,
    IN p_PaymentAmount DECIMAL(15,2),
    IN p_PaymentMethod ENUM('Cash', 'BankTransfer', 'Check', 'AutoDebit'),
    IN p_BankName NVARCHAR(100),
    IN p_CheckNumber VARCHAR(50),
    IN p_TransactionReference VARCHAR(100),
    IN p_Description TEXT,
    IN p_ReceivedBy INT,
    OUT p_PaymentID BIGINT,
    OUT p_PaymentNumber VARCHAR(50),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_MemberID INT;
    DECLARE v_RemainingPrincipal DECIMAL(15,2);
    DECLARE v_PenaltyAmount DECIMAL(15,2) DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ثبت پرداخت';
        SET p_PaymentID = -1;
    END;
    
    START TRANSACTION;
    
    -- بررسی وجود وام
    SELECT MemberID, RemainingPrincipal 
    INTO v_MemberID, v_RemainingPrincipal
    FROM Loans 
    WHERE LoanID = p_LoanID AND Status = 'Active';
    
    IF v_MemberID IS NULL THEN
        SET p_ResultMessage = 'وام فعال یافت نشد';
        SET p_PaymentID = -1;
        ROLLBACK;
    ELSE
        -- تولید شماره پرداخت
        SET p_PaymentNumber = fn_GeneratePaymentNumber();
        
        -- محاسبه جریمه‌های overdue
        SELECT COALESCE(SUM(PenaltyAmount), 0) INTO v_PenaltyAmount
        FROM Installments 
        WHERE LoanID = p_LoanID AND Status = 'Overdue';
        
        -- ثبت پرداخت
        INSERT INTO Payments (
            PaymentNumber, LoanID, MemberID, PaymentDate, PaymentAmount,
            PaymentMethod, BankName, CheckNumber, TransactionReference,
            Description, ReceivedBy
        ) VALUES (
            p_PaymentNumber, p_LoanID, v_MemberID, CURDATE(), p_PaymentAmount,
            p_PaymentMethod, p_BankName, p_CheckNumber, p_TransactionReference,
            p_Description, p_ReceivedBy
        );
        
        SET p_PaymentID = LAST_INSERT_ID();
        
        -- تخصیص پرداخت به اقساط
        CALL sp_AllocatePayment(p_PaymentID, p_LoanID, p_PaymentAmount);
        
        SET p_ResultMessage = 'پرداخت با موفقیت ثبت شد';
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_AllocatePayment(
    IN p_PaymentID BIGINT,
    IN p_LoanID INT,
    IN p_PaymentAmount DECIMAL(15,2)
)
BEGIN
    DECLARE v_RemainingAmount DECIMAL(15,2) DEFAULT p_PaymentAmount;
    DECLARE v_InstallmentID BIGINT;
    DECLARE v_DueAmount DECIMAL(15,2);
    DECLARE v_PenaltyAmount DECIMAL(15,2);
    DECLARE v_AllocationAmount DECIMAL(15,2);
    DECLARE v_Done INT DEFAULT FALSE;
    
    DECLARE cur_overdue CURSOR FOR 
        SELECT InstallmentID, (TotalAmount + PenaltyAmount - PaidAmount) as DueAmount, PenaltyAmount
        FROM Installments 
        WHERE LoanID = p_LoanID AND Status IN ('Overdue', 'Pending')
        ORDER BY DueDate ASC;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_Done = TRUE;
    
    OPEN cur_overdue;
    
    payment_allocation: LOOP
        FETCH cur_overdue INTO v_InstallmentID, v_DueAmount, v_PenaltyAmount;
        IF v_Done OR v_RemainingAmount <= 0 THEN
            LEAVE payment_allocation;
        END IF;
        
        -- تخصیص مبلغ به این قسط
        SET v_AllocationAmount = LEAST(v_RemainingAmount, v_DueAmount);
        
        -- به‌روزرسانی وضعیت قسط
        UPDATE Installments 
        SET 
            PaidAmount = PaidAmount + v_AllocationAmount,
            Status = CASE 
                WHEN (PaidAmount + v_AllocationAmount) >= TotalAmount THEN 'Paid'
                WHEN (PaidAmount + v_AllocationAmount) > 0 THEN 'Partial'
                ELSE 'Pending'
            END,
            PaidDate = CASE 
                WHEN (PaidAmount + v_AllocationAmount) >= TotalAmount THEN CURDATE()
                ELSE PaidDate
            END
        WHERE InstallmentID = v_InstallmentID;
        
        -- ثبت تخصیص
        INSERT INTO PaymentAllocations (PaymentID, InstallmentID, AllocatedAmount, AllocationType)
        VALUES (p_PaymentID, v_InstallmentID, v_AllocationAmount, 'Principal');
        
        SET v_RemainingAmount = v_RemainingAmount - v_AllocationAmount;
    END LOOP;
    
    CLOSE cur_overdue;
    
    -- به‌روزرسانی اطلاعات وام
    UPDATE Loans 
    SET 
        RemainingPrincipal = RemainingPrincipal - (p_PaymentAmount - v_RemainingAmount),
        LastPaymentDate = CURDATE(),
        NextDueDate = COALESCE((
            SELECT MIN(DueDate) 
            FROM Installments 
            WHERE LoanID = p_LoanID AND Status IN ('Pending', 'Partial')
        ), EndDate),
        Status = CASE 
            WHEN RemainingPrincipal - (p_PaymentAmount - v_RemainingAmount) <= 0 THEN 'Completed'
            ELSE 'Active'
        END
    WHERE LoanID = p_LoanID;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetOverdueLoansReport(
    IN p_DaysOverdue INT
)
BEGIN
    SELECT 
        l.LoanID,
        l.LoanNumber,
        m.MemberCode,
        CONCAT(m.FirstName, ' ', m.LastName) as MemberName,
        m.Mobile,
        lt.TypeName as LoanType,
        l.LoanAmount,
        l.RemainingPrincipal,
        l.MonthlyInstallment,
        l.NextDueDate,
        DATEDIFF(CURDATE(), l.NextDueDate) as DaysOverdue,
        (SELECT COUNT(*) FROM Installments i WHERE i.LoanID = l.LoanID AND i.Status = 'Overdue') as OverdueInstallmentsCount,
        (SELECT COALESCE(SUM(TotalAmount - PaidAmount + PenaltyAmount), 0) 
         FROM Installments i 
         WHERE i.LoanID = l.LoanID AND i.Status = 'Overdue') as TotalOverdueAmount
    FROM Loans l
    INNER JOIN Members m ON l.MemberID = m.MemberID
    INNER JOIN LoanTypes lt ON l.LoanTypeID = lt.LoanTypeID
    WHERE l.Status = 'Active' 
    AND l.NextDueDate < CURDATE()
    AND (p_DaysOverdue IS NULL OR DATEDIFF(CURDATE(), l.NextDueDate) >= p_DaysOverdue)
    ORDER BY DaysOverdue DESC, l.NextDueDate ASC;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetFundStatistics(
    IN p_StartDate DATE,
    IN p_EndDate DATE
)
BEGIN
    -- آمار کلی
    SELECT 
        (SELECT COUNT(*) FROM Members WHERE IsActive = TRUE) as ActiveMembers,
        (SELECT COUNT(*) FROM Loans WHERE Status = 'Active') as ActiveLoans,
        (SELECT COALESCE(SUM(RemainingPrincipal), 0) FROM Loans WHERE Status = 'Active') as TotalActiveLoanAmount,
        (SELECT COUNT(*) FROM Loans WHERE Status = 'Defaulted') as DefaultedLoans,
        (SELECT COALESCE(SUM(LoanAmount), 0) FROM Loans WHERE Status = 'Defaulted') as TotalDefaultedAmount,
        (SELECT COUNT(*) FROM Loans WHERE CreatedAt BETWEEN p_StartDate AND p_EndDate) as NewLoansInPeriod,
        (SELECT COALESCE(SUM(LoanAmount), 0) FROM Loans WHERE CreatedAt BETWEEN p_StartDate AND p_EndDate) as NewLoanAmountInPeriod,
        (SELECT COALESCE(SUM(PaymentAmount), 0) FROM Payments 
         WHERE PaymentDate BETWEEN p_StartDate AND p_EndDate AND IsConfirmed = TRUE) as TotalCollectionsInPeriod;
    
    -- آمار بر اساس نوع وام
    SELECT 
        lt.TypeName,
        COUNT(l.LoanID) as LoanCount,
        COALESCE(SUM(l.LoanAmount), 0) as TotalAmount,
        COALESCE(SUM(l.RemainingPrincipal), 0) as RemainingAmount,
        ROUND(AVG(l.InterestRate), 2) as AvgInterestRate
    FROM LoanTypes lt
    LEFT JOIN Loans l ON lt.LoanTypeID = l.LoanTypeID
    GROUP BY lt.LoanTypeID
    ORDER BY TotalAmount DESC;
    
    -- پرداخت‌های ماه جاری
    SELECT 
        DATE_FORMAT(PaymentDate, '%Y-%m') as Month,
        COUNT(*) as PaymentCount,
        COALESCE(SUM(PaymentAmount), 0) as TotalAmount,
        COALESCE(SUM(PrincipalAmount), 0) as PrincipalAmount,
        COALESCE(SUM(InterestAmount), 0) as InterestAmount,
        COALESCE(SUM(PenaltyAmount), 0) as PenaltyAmount
    FROM Payments
    WHERE PaymentDate BETWEEN p_StartDate AND p_EndDate AND IsConfirmed = TRUE
    GROUP BY DATE_FORMAT(PaymentDate, '%Y-%m')
    ORDER BY Month DESC;
END$$

DELIMITER ;

<?php
// فراخوانی برای گرفتن لیست اعضا
$isActive = true;
$page = 1;
$pageSize = 20;

$query = "CALL sp_GetMembers(?, NULL, NULL, ?, ?, @totalRecords, @totalPages)";
$stmt = $conn->prepare($query);
$stmt->bind_param("iii", $isActive, $page, $pageSize);
$stmt->execute();
$result = $stmt->get_result();

$members = [];
while ($row = $result->fetch_assoc()) {
    $members[] = $row;
}

// گرفتن خروجی‌ها
$output = $conn->query("SELECT @totalRecords as total_records, @totalPages as total_pages");
$pagination = $output->fetch_assoc();

// فراخوانی برای ایجاد وام جدید
$applicationId = 123;
$createdBy = 1;

$query = "CALL sp_CreateLoan(?, ?, @loanId, @loanNumber, @resultMessage)";
$stmt = $conn->prepare($query);
$stmt->bind_param("ii", $applicationId, $createdBy);
$stmt->execute();

$result = $conn->query("SELECT @loanId as loan_id, @loanNumber as loan_number, @resultMessage as message");
$output = $result->fetch_assoc();

if ($output['loan_id'] > 0) {
    echo "وام با شماره " . $output['loan_number'] . " ایجاد شد";
} else {
    echo "خطا: " . $output['message'];
}

// فراخوانی برای ثبت پرداخت
$loanId = 456;
$paymentAmount = 500000;
$receivedBy = 1;

$query = "CALL sp_CreatePayment(?, ?, 'Cash', NULL, NULL, NULL, 'پرداخت قسط', ?, @paymentId, @paymentNumber, @resultMessage)";
$stmt = $conn->prepare($query);
$stmt->bind_param("idi", $loanId, $paymentAmount, $receivedBy);
$stmt->execute();

$result = $conn->query("SELECT @paymentId as payment_id, @paymentNumber as payment_number, @resultMessage as message");
$output = $result->fetch_assoc();

// فراخوانی برای گزارش overdue
$query = "CALL sp_GetOverdueLoansReport(30)";
$result = $conn->query($query);

$overdueLoans = [];
while ($row = $result->fetch_assoc()) {
    $overdueLoans[] = $row;
}
?>