ساختار پایگاه داده (اسامی جداول با فیلدهای مورد نیاز) یک سیستم جامع صندوق وام با قابلیت ثبت نام اعضا و تعیین مدیران صندوق و اعطای وام

CREATE TABLE Members (
    MemberID INT PRIMARY KEY AUTO_INCREMENT,
    MemberCode VARCHAR(20) UNIQUE NOT NULL,
    NationalCode VARCHAR(10) UNIQUE NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    FatherName NVARCHAR(50),
    BirthDate DATE,
    Gender ENUM('Male', 'Female') NOT NULL,
    Mobile VARCHAR(11) NOT NULL,
    Email VARCHAR(100),
    Address TEXT,
    City NVARCHAR(50),
    Province NVARCHAR(50),
    PostalCode VARCHAR(10),
    EmploymentStatus ENUM('Employed', 'SelfEmployed', 'Unemployed', 'Retired') DEFAULT 'Employed',
    JobTitle NVARCHAR(100),
    Employer NVARCHAR(100),
    MonthlyIncome DECIMAL(12,2) DEFAULT 0,
    MaritalStatus ENUM('Single', 'Married', 'Divorced', 'Widowed') DEFAULT 'Single',
    MemberType ENUM('Normal', 'Premium', 'Gold') DEFAULT 'Normal',
    JoinDate DATE NOT NULL,
    IsActive BOOLEAN DEFAULT TRUE,
    CreditScore INT DEFAULT 100, -- امتیاز اعتباری
    MaxLoanAmount DECIMAL(15,2) DEFAULT 0, -- حداکثر مبلغ وام مجاز
    ProfileImage VARCHAR(255),
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_national_code (NationalCode),
    INDEX idx_member_code (MemberCode),
    INDEX idx_active (IsActive),
    INDEX idx_credit_score (CreditScore)
);

CREATE TABLE FundManagers (
    ManagerID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL, -- ارتباط با سیستم احراز هویت
    ManagerCode VARCHAR(20) UNIQUE NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    NationalCode VARCHAR(10) UNIQUE NOT NULL,
    Mobile VARCHAR(11) NOT NULL,
    Email VARCHAR(100),
    ManagerType ENUM('SuperAdmin', 'Admin', 'LoanOfficer', 'Accountant') DEFAULT 'LoanOfficer',
    PermissionLevel INT DEFAULT 1,
    CanApproveLoan BOOLEAN DEFAULT FALSE,
    CanManageMembers BOOLEAN DEFAULT TRUE,
    CanViewReports BOOLEAN DEFAULT TRUE,
    MaxApprovalAmount DECIMAL(15,2) DEFAULT 0, -- حداکثر مبلغ مجاز برای تایید
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    INDEX idx_manager_code (ManagerCode),
    INDEX idx_type (ManagerType)
);

CREATE TABLE LoanTypes (
    LoanTypeID INT PRIMARY KEY AUTO_INCREMENT,
    TypeCode VARCHAR(20) UNIQUE NOT NULL,
    TypeName NVARCHAR(100) NOT NULL,
    Description TEXT,
    InterestRate DECIMAL(5,2) DEFAULT 0, -- نرخ سود
    IsQardHasana BOOLEAN DEFAULT TRUE, -- قرض الحسنه
    MinAmount DECIMAL(15,2) NOT NULL,
    MaxAmount DECIMAL(15,2) NOT NULL,
    MinTerm INT NOT NULL, -- حداقل مدت (ماه)
    MaxTerm INT NOT NULL, -- حداکثر مدت (ماه)
    GracePeriod INT DEFAULT 0, -- دوره تنفس (ماه)
    EarlySettlementAllowed BOOLEAN DEFAULT TRUE,
    EarlySettlementPenaltyRate DECIMAL(5,2) DEFAULT 0, -- جریمه تسویه زودهنگام
    RequiredCreditScore INT DEFAULT 0, -- امتیاز اعتباری مورد نیاز
    RequiredGuarantors INT DEFAULT 0, -- تعداد ضامن مورد نیاز
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_active (IsActive),
    INDEX idx_type_code (TypeCode)
);

CREATE TABLE LoanApplications (
    ApplicationID INT PRIMARY KEY AUTO_INCREMENT,
    ApplicationNumber VARCHAR(50) UNIQUE NOT NULL,
    MemberID INT NOT NULL,
    LoanTypeID INT NOT NULL,
    AppliedAmount DECIMAL(15,2) NOT NULL,
    RequestedTerm INT NOT NULL, -- مدت درخواستی (ماه)
    Purpose NVARCHAR(500), -- هدف دریافت وام
    ApplicationDate DATE NOT NULL,
    Status ENUM('Draft', 'Pending', 'UnderReview', 'Approved', 'Rejected', 'Cancelled') DEFAULT 'Draft',
    ReviewedBy INT, -- مدیر بررسی کننده
    ReviewedAt DATETIME,
    ApprovedBy INT, -- مدیر تایید کننده
    ApprovedAt DATETIME,
    ApprovedAmount DECIMAL(15,2) DEFAULT 0, -- مبلغ تایید شده
    ApprovedTerm INT DEFAULT 0, -- مدت تایید شده
    RejectionReason TEXT,
    CreditScoreAtApplication INT, -- امتیاز اعتباری در زمان درخواست
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (LoanTypeID) REFERENCES LoanTypes(LoanTypeID),
    FOREIGN KEY (ReviewedBy) REFERENCES FundManagers(ManagerID),
    FOREIGN KEY (ApprovedBy) REFERENCES FundManagers(ManagerID),
    INDEX idx_member (MemberID),
    INDEX idx_status (Status),
    INDEX idx_application_date (ApplicationDate),
    INDEX idx_application_number (ApplicationNumber)
);

CREATE TABLE Guarantors (
    GuarantorID INT PRIMARY KEY AUTO_INCREMENT,
    ApplicationID INT NOT NULL,
    MemberID INT NOT NULL, -- عضو ضامن
    GuarantorNationalCode VARCHAR(10) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    FatherName NVARCHAR(50),
    Mobile VARCHAR(11),
    Relationship ENUM('Family', 'Friend', 'Colleague', 'Other') DEFAULT 'Family',
    IsAccepted BOOLEAN DEFAULT FALSE, -- تایید ضامن
    AcceptedAt DATETIME,
    CreditScore INT, -- امتیاز اعتباری ضامن
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ApplicationID) REFERENCES LoanApplications(ApplicationID) ON DELETE CASCADE,
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    INDEX idx_application (ApplicationID),
    INDEX idx_member (MemberID)
);

CREATE TABLE Loans (
    LoanID INT PRIMARY KEY AUTO_INCREMENT,
    LoanNumber VARCHAR(50) UNIQUE NOT NULL,
    ApplicationID INT NOT NULL,
    MemberID INT NOT NULL,
    LoanTypeID INT NOT NULL,
    LoanAmount DECIMAL(15,2) NOT NULL,
    InterestRate DECIMAL(5,2) NOT NULL,
    Term INT NOT NULL, -- مدت وام (ماه)
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    GracePeriod INT DEFAULT 0,
    MonthlyInstallment DECIMAL(15,2) NOT NULL, -- قسط ماهانه
    TotalPayable DECIMAL(15,2) NOT NULL, -- کل مبلغ قابل پرداخت
    RemainingPrincipal DECIMAL(15,2) NOT NULL, -- اصل باقیمانده
    PaidPrincipal DECIMAL(15,2) DEFAULT 0, -- اصل پرداخت شده
    PaidInterest DECIMAL(15,2) DEFAULT 0, -- سود پرداخت شده
    LatePaymentPenalty DECIMAL(15,2) DEFAULT 0, -- جریمه دیرکرد
    Status ENUM('Active', 'Completed', 'Defaulted', 'SettledEarly', 'WrittenOff') DEFAULT 'Active',
    NextDueDate DATE, -- تاریخ سررسید بعدی
    LastPaymentDate DATE, -- تاریخ آخرین پرداخت
    EarlySettlementDate DATE, -- تاریخ تسویه زودهنگام
    EarlySettlementAmount DECIMAL(15,2) DEFAULT 0, -- مبلغ تسویه زودهنگام
    CreatedBy INT NOT NULL, -- مدیر ثبت کننده
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ApplicationID) REFERENCES LoanApplications(ApplicationID),
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (LoanTypeID) REFERENCES LoanTypes(LoanTypeID),
    FOREIGN KEY (CreatedBy) REFERENCES FundManagers(ManagerID),
    INDEX idx_loan_number (LoanNumber),
    INDEX idx_member (MemberID),
    INDEX idx_status (Status),
    INDEX idx_due_date (NextDueDate)
);

CREATE TABLE Installments (
    InstallmentID BIGINT PRIMARY KEY AUTO_INCREMENT,
    LoanID INT NOT NULL,
    InstallmentNumber INT NOT NULL, -- شماره قسط
    DueDate DATE NOT NULL,
    PrincipalAmount DECIMAL(15,2) NOT NULL, -- مبلغ اصل
    InterestAmount DECIMAL(15,2) NOT NULL, -- مبلغ سود
    TotalAmount DECIMAL(15,2) NOT NULL, -- کل مبلغ قسط
    PaidAmount DECIMAL(15,2) DEFAULT 0, -- مبلغ پرداخت شده
    PaidDate DATE, -- تاریخ پرداخت
    PenaltyAmount DECIMAL(15,2) DEFAULT 0, -- جریمه دیرکرد
    Status ENUM('Pending', 'Paid', 'Overdue', 'Partial', 'Waived') DEFAULT 'Pending',
    DaysOverdue INT DEFAULT 0, -- تعداد روز تاخیر
    PaymentMethod ENUM('Cash', 'BankTransfer', 'Check', 'AutoDebit') DEFAULT 'Cash',
    PaymentReference VARCHAR(100), -- شماره مرجع پرداخت
    Notes TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (LoanID) REFERENCES Loans(LoanID) ON DELETE CASCADE,
    UNIQUE KEY unique_loan_installment (LoanID, InstallmentNumber),
    INDEX idx_loan (LoanID),
    INDEX idx_due_date (DueDate),
    INDEX idx_status (Status)
);

CREATE TABLE Payments (
    PaymentID BIGINT PRIMARY KEY AUTO_INCREMENT,
    PaymentNumber VARCHAR(50) UNIQUE NOT NULL,
    LoanID INT NOT NULL,
    MemberID INT NOT NULL,
    PaymentDate DATE NOT NULL,
    PaymentAmount DECIMAL(15,2) NOT NULL,
    PrincipalAmount DECIMAL(15,2) NOT NULL, -- سهم اصل
    InterestAmount DECIMAL(15,2) NOT NULL, -- سهم سود
    PenaltyAmount DECIMAL(15,2) DEFAULT 0, -- سهم جریمه
    PaymentMethod ENUM('Cash', 'BankTransfer', 'Check', 'AutoDebit') DEFAULT 'Cash',
    BankName NVARCHAR(100),
    CheckNumber VARCHAR(50),
    TransactionReference VARCHAR(100),
    ReceivedBy INT, -- مدیر دریافت کننده
    Description TEXT,
    IsConfirmed BOOLEAN DEFAULT FALSE,
    ConfirmedBy INT,
    ConfirmedAt DATETIME,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (LoanID) REFERENCES Loans(LoanID),
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (ReceivedBy) REFERENCES FundManagers(ManagerID),
    FOREIGN KEY (ConfirmedBy) REFERENCES FundManagers(ManagerID),
    INDEX idx_payment_number (PaymentNumber),
    INDEX idx_loan (LoanID),
    INDEX idx_member (MemberID),
    INDEX idx_payment_date (PaymentDate)
);

CREATE TABLE PaymentAllocations (
    AllocationID BIGINT PRIMARY KEY AUTO_INCREMENT,
    PaymentID BIGINT NOT NULL,
    InstallmentID BIGINT NOT NULL,
    AllocatedAmount DECIMAL(15,2) NOT NULL,
    AllocationType ENUM('Principal', 'Interest', 'Penalty') NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (PaymentID) REFERENCES Payments(PaymentID) ON DELETE CASCADE,
    FOREIGN KEY (InstallmentID) REFERENCES Installments(InstallmentID),
    INDEX idx_payment (PaymentID),
    INDEX idx_installment (InstallmentID)
);

CREATE TABLE LoanCreditTransfers (
    TransferID INT PRIMARY KEY AUTO_INCREMENT,
    FromMemberID INT NOT NULL, -- منتقل کننده
    ToMemberID INT NOT NULL, -- منتقل‌شونده
    TransferAmount DECIMAL(15,2) NOT NULL, -- مبلغ انتقالی
    TransferDate DATE NOT NULL,
    Reason NVARCHAR(500),
    Status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    ApprovedBy INT,
    ApprovedAt DATETIME,
    RejectionReason TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (FromMemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (ToMemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (ApprovedBy) REFERENCES FundManagers(ManagerID),
    INDEX idx_from_member (FromMemberID),
    INDEX idx_to_member (ToMemberID),
    INDEX idx_status (Status)
);

CREATE TABLE CreditScores (
    ScoreID INT PRIMARY KEY AUTO_INCREMENT,
    MemberID INT NOT NULL,
    Score INT NOT NULL,
    ScoreType ENUM('Initial', 'LoanPayment', 'LatePayment', 'EarlySettlement', 'Adjustment') DEFAULT 'Initial',
    Description NVARCHAR(500),
    EffectiveDate DATE NOT NULL,
    ExpiryDate DATE,
    CreatedBy INT, -- مدیر ثبت کننده
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (CreatedBy) REFERENCES FundManagers(ManagerID),
    INDEX idx_member (MemberID),
    INDEX idx_effective_date (EffectiveDate)
);

CREATE TABLE SystemSettings (
    SettingID INT PRIMARY KEY AUTO_INCREMENT,
    SettingKey VARCHAR(100) UNIQUE NOT NULL,
    SettingValue TEXT,
    SettingType ENUM('String', 'Number', 'Boolean', 'JSON') DEFAULT 'String',
    Description TEXT,
    UpdatedBy INT,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (UpdatedBy) REFERENCES FundManagers(ManagerID)
);

CREATE TABLE AuditLog (
    LogID BIGINT PRIMARY KEY AUTO_INCREMENT,
    UserID INT, -- کاربر انجام دهنده
    UserType ENUM('Member', 'Manager') DEFAULT 'Manager',
    ActionType VARCHAR(100) NOT NULL,
    TableName VARCHAR(50) NOT NULL,
    RecordID INT,
    Description TEXT,
    OldValues JSON,
    NewValues JSON,
    IPAddress VARCHAR(45),
    UserAgent TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user (UserID),
    INDEX idx_action (ActionType),
    INDEX idx_created (CreatedAt)
);

CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    Username VARCHAR(50) UNIQUE NOT NULL,
    PasswordHash VARCHAR(255) NOT NULL,
    Email VARCHAR(100),
    UserType ENUM('Member', 'Manager') NOT NULL,
    ReferenceID INT NOT NULL, -- MemberID یا ManagerID
    IsActive BOOLEAN DEFAULT TRUE,
    LastLogin DATETIME,
    FailedLoginAttempts INT DEFAULT 0,
    LockedUntil DATETIME,
    PasswordResetToken VARCHAR(100),
    TokenExpiry DATETIME,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_username (Username),
    INDEX idx_user_type (UserType, ReferenceID)
);

-- درج انواع وام
INSERT INTO LoanTypes (TypeCode, TypeName, Description, InterestRate, IsQardHasana, MinAmount, MaxAmount, MinTerm, MaxTerm, GracePeriod, RequiredCreditScore, RequiredGuarantors) VALUES
('QARD', 'قرض الحسنه', 'وام قرض الحسنه بدون سود', 0, TRUE, 1000000, 50000000, 6, 36, 3, 50, 1),
('URGENT', 'وام فوری', 'وام با سود پایین برای نیازهای فوری', 4, FALSE, 500000, 20000000, 3, 24, 1, 40, 1),
('HOUSING', 'وام مسکن', 'وام برای خرید و ساخت مسکن', 6, FALSE, 5000000, 200000000, 12, 60, 6, 70, 2),
('MARRIAGE', 'وام ازدواج', 'وام برای هزینه‌های ازدواج', 2, FALSE, 2000000, 30000000, 6, 36, 3, 60, 1);

-- درج تنظیمات سیستم
INSERT INTO SystemSettings (SettingKey, SettingValue, SettingType, Description) VALUES
('max_loan_per_member', '500000000', 'Number', 'حداکثر مجموع وام هر عضو'),
('min_credit_score', '30', 'Number', 'حداقل امتیاز اعتباری برای دریافت وام'),
('late_payment_penalty_rate', '2', 'Number', 'نرخ جریمه دیرکرد (درصد)'),
('early_settlement_discount', '5', 'Number', 'تخفیف تسویه زودهنگام (درصد)'),
('max_active_loans', '2', 'Number', 'حداکثر تعداد وام‌های فعال همزمان'),
('credit_transfer_fee', '1', 'Number', 'کارمزد انتقال امتیاز (درصد)');

-- ایجاد کاربر ادمین پیش‌فرض
INSERT INTO FundManagers (UserID, ManagerCode, FirstName, LastName, NationalCode, Mobile, ManagerType, PermissionLevel, CanApproveLoan, MaxApprovalAmount) 
VALUES (1, 'MGR001', 'مدیر', 'سیستم', '0012345678', '09123456789', 'SuperAdmin', 100, TRUE, 1000000000);

INSERT INTO Users (Username, PasswordHash, UserType, ReferenceID) 
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Manager', 1);