# 🧾 3. Table Design (Detailed)

---

## `loan_applications` (MAIN TABLE)
This is the core record.

```sql
id (PK)
loan_type_id (FK)
amount_requested
deduction_period
purpose

status (SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED)
created_at
updated_at
```

## `applicants`
One-to-one with loan application.

```sql
id (PK)
loan_application_id (FK)

name
ic_num
phone_num
member_id_num
birth_date
email

employer_name
current_position
position_status

address

bank_account_number
bank_name

basic_salary
total_income
deduction

service_date
service_period
```

## `guarantors`
👉 One loan → **3 guarantors** (One-to-Many)

```sql
id (PK)
loan_application_id (FK)

name
ic_num
unit
address
phone_num
member_id_number

basic_salary
allowance
total_income
```

## `loan_approvals`
One-to-one with loan application.

```sql
id (PK)
loan_application_id (FK)

level
role
approver_id

decision (PENDING, APPROVED, REJECTED)
remarks

approved_at
```

## `loan_evaluations`
```sql
id (PK)
loan_application_id (FK)

calculated_by (FK users)

total_loan
total_payment
period

insurance_per_month

deduction_start_date
deduction_end_date

number_of_shares
total_fees
debt_balance

payment_date

created_at
```

## `documents`
```sql
id
loan_application_id (FK)

file_name
file_url
file_type

uploaded_by
uploaded_at
```

## `loan_status_history`
```sql
id (PK)
loan_application_id (FK)

status (DRAFT, SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED)

changed_by (FK users)
changed_at (timestamp)

remarks (optional)
```

## `repayments`
```sql
id (PK)
loan_application_id (FK)

installment_number (1, 2, 3...)

due_date
amount_due

amount_paid
payment_date

status (PENDING, PAID, LATE)

created_at
```

---

# 🔗 4. Relationships Diagram (Simple)

```id="c2x7qp"
loan_applications
    │
    ├── applicants (1:1)
    ├── guarantors (1:N)
    └── loan_reviews (1:1)
```

---


# 🧩 6. JPA Entity Mapping (Example)

### LoanApplication

```java
@Entity
public class LoanApplication {
    @Id
    @GeneratedValue
    private Long id;

    private BigDecimal amountRequested;
    private Integer deductionPeriod;
    private String purpose;

    @Enumerated(EnumType.STRING)
    private LoanStatus status;

    @OneToOne(mappedBy = "loanApplication", cascade = CascadeType.ALL)
    private Applicant applicant;

    @OneToMany(mappedBy = "loanApplication", cascade = CascadeType.ALL)
    private List<Guarantor> guarantors;

    @OneToOne(mappedBy = "loanApplication", cascade = CascadeType.ALL)
    private LoanReview review;
}
```

---

# ⚠️ 7. Important Constraints

## 🔹 Enforce in Backend (NOT DB)

* Exactly **3 guarantors**
* Valid IC format
* Salary > 0
* Loan amount limits

---

# 🔐 8. Audit & Compliance (Very Important)

Add these fields to key tables:

```sql
created_by
created_at
updated_by
updated_at
```

---

# 🚀 9. Future Enhancements

You can easily extend this design with:

* 📎 `documents` table (for uploads)
* 📊 `repayments` table
* 🧾 `audit_logs`
* 🔄 `loan_status_history`

---

# 🏁 Final Architecture Mapping

Your DB aligns perfectly with your system:

```id="z7wq3f"
Frontend Form → LoanApplication → Applicant + Guarantors
                                      ↓
                                Officer Review
                                      ↓
                                   Decision
```