# Complete Optimization Problem and Solution: loan_1

## 1. Problem Context and Goals

### Context  
A bank is focused on optimizing its loan allocation strategy across its branches to maximize the total loan amount disbursed. The bank must ensure that its loan allocation adheres to several operational constraints to maintain financial stability and risk control. Specifically, the bank must respect the capacity of each branch in terms of the number of customers it can serve, the creditworthiness of each customer as reflected by their credit score, and the total loan exposure per customer.  

The bank has established specific operational parameters to guide this process:  
- **Maximum loan amount per customer**: No single customer can receive a loan exceeding $15,000.  
- **Maximum loan amount per credit score unit**: For every unit of a customer’s credit score, the loan amount cannot exceed $600. This ensures that customers with higher credit scores are eligible for larger loans.  
- **Maximum total loan amount per customer**: The cumulative loan amount for any single customer across all branches cannot exceed $60,000, preventing overexposure to any individual customer.  

The decision variable in this optimization is the loan amount to be disbursed, which is a continuous value. The bank aims to allocate these loan amounts in a way that maximizes the total disbursement while adhering to the above constraints.  

### Goals  
The primary goal of this optimization is to maximize the total loan amount disbursed across all branches. Success is measured by the sum of all loan amounts allocated, ensuring that the bank’s resources are utilized effectively while maintaining compliance with the established operational constraints.  

## 2. Constraints  

The optimization must respect the following constraints:  
1. **Branch customer capacity**: The total loan amount allocated to a branch cannot exceed the product of the number of customers in that branch and the maximum loan amount per customer. This ensures that each branch operates within its customer service capacity.  
2. **Customer credit score**: The loan amount allocated to a customer cannot exceed the product of their credit score and the maximum loan amount per credit score unit. This ensures that loan amounts are proportional to the customer’s creditworthiness.  
3. **Total loan exposure per customer**: The sum of all loan amounts allocated to a single customer across all branches cannot exceed the maximum total loan amount per customer. This prevents overexposure to any individual customer.  

These constraints are designed to ensure that the bank’s loan allocation strategy is both efficient and risk-controlled, aligning with its operational and financial goals.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include adding missing parameters to business_configuration_logic.json and ensuring all tables meet the 3-row minimum rule. Configuration logic updates address scalar parameters and formulas identified as missing in the OR expert analysis.

CREATE TABLE loan (
  amount FLOAT
);

CREATE TABLE bank (
  no_of_customers INTEGER
);

CREATE TABLE customer (
  credit_score INTEGER
);
```

### Data Dictionary  
- **Loan Table**:  
  - **Purpose**: Stores details about the loans to be disbursed.  
  - **Optimization Role**: Contains the decision variable for the optimization model.  
  - **Columns**:  
    - **amount**: The loan amount to be disbursed. This is the primary decision variable in the optimization.  

- **Bank Table**:  
  - **Purpose**: Stores information about each branch’s customer capacity.  
  - **Optimization Role**: Provides data for the branch customer capacity constraint.  
  - **Columns**:  
    - **no_of_customers**: The number of customers in a branch, used to calculate the maximum loan allocation per branch.  

- **Customer Table**:  
  - **Purpose**: Stores customer-specific details, particularly their credit scores.  
  - **Optimization Role**: Provides data for the credit score-based loan allocation constraint.  
  - **Columns**:  
    - **credit_score**: The credit score of a customer, used to determine the maximum loan amount they are eligible for.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical banking practices, ensuring that loan amounts, customer capacities, and credit scores are realistic and align with the optimization constraints.

-- Realistic data for loan
INSERT INTO loan (amount) VALUES (5000.0);
INSERT INTO loan (amount) VALUES (10000.0);
INSERT INTO loan (amount) VALUES (7500.0);

-- Realistic data for bank
INSERT INTO bank (no_of_customers) VALUES (150);
INSERT INTO bank (no_of_customers) VALUES (250);
INSERT INTO bank (no_of_customers) VALUES (200);

-- Realistic data for customer
INSERT INTO customer (credit_score) VALUES (720);
INSERT INTO customer (credit_score) VALUES (780);
INSERT INTO customer (credit_score) VALUES (750);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let `amount[loan_ID]` represent the loan amount to be disbursed for each loan identified by `loan_ID`. This is a continuous decision variable.

#### Objective Function
Maximize the total loan amount disbursed across all loans:
\[
\text{Maximize} \quad \sum_{\text{loan\_ID}} \text{amount[loan\_ID]}
\]
**Data Source Verification**: The coefficient for `amount[loan_ID]` is 1, derived from the goal of maximizing the total loan amount.

#### Constraints
1. **Branch Customer Capacity**: The total loan amount allocated to a branch cannot exceed the product of the number of customers in that branch and the maximum loan amount per customer ($15,000):
\[
\sum_{\text{loan\_ID} \in \text{branch\_k}} \text{amount[loan\_ID]} \leq \text{bank.no\_of\_customers} \times 15000 \quad \forall \text{branch\_k}
\]
**Data Source Verification**: `bank.no_of_customers` comes from the `bank` table, and 15000 is the maximum loan amount per customer.

2. **Customer Credit Score**: The loan amount allocated to a customer cannot exceed the product of their credit score and the maximum loan amount per credit score unit ($600):
\[
\text{amount[loan\_ID]} \leq \text{customer.credit\_score} \times 600 \quad \forall \text{loan\_ID}
\]
**Data Source Verification**: `customer.credit_score` comes from the `customer` table, and 600 is the maximum loan amount per credit score unit.

3. **Total Loan Exposure per Customer**: The sum of all loan amounts allocated to a single customer across all branches cannot exceed the maximum total loan amount per customer ($60,000):
\[
\sum_{\text{loan\_ID} \in \text{customer\_m}} \text{amount[loan\_ID]} \leq 60000 \quad \forall \text{customer\_m}
\]
**Data Source Verification**: 60000 is the maximum total loan amount per customer.

#### Data Source Verification Summary
- **Objective Function**: Coefficient 1 for `amount[loan_ID]` is derived from the goal of maximizing total loan amount.
- **Constraint 1**: `bank.no_of_customers` from the `bank` table, and 15000 is the maximum loan amount per customer.
- **Constraint 2**: `customer.credit_score` from the `customer` table, and 600 is the maximum loan amount per credit score unit.
- **Constraint 3**: 60000 is the maximum total loan amount per customer.

This formulation provides a complete, immediately solvable LINEAR mathematical model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy Implementation for Loan Allocation Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def loan_allocation_optimization():
    # 1. MODEL & DATA SETUP
    model = gp.Model("loan_allocation")
    
    # Data from the provided tables
    loan_amounts = [5000.0, 10000.0, 7500.0]  # Example loan amounts
    no_of_customers = [150, 250, 200]  # Number of customers per branch
    credit_scores = [720, 780, 750]  # Credit scores of customers
    
    # Constants
    max_loan_per_customer = 15000
    max_loan_per_credit_score_unit = 600
    max_total_loan_per_customer = 60000
    
    # Validate array lengths
    n_loans = len(loan_amounts)
    n_branches = len(no_of_customers)
    n_customers = len(credit_scores)
    
    assert n_loans == n_branches == n_customers, "Array length mismatch"
    
    # 2. VARIABLES
    # Decision variable: loan amount to be disbursed for each loan
    amount = {i: model.addVar(vtype=GRB.CONTINUOUS, name=f"amount_{i}", lb=0) 
              for i in range(n_loans)}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total loan amount disbursed
    model.setObjective(gp.quicksum(amount[i] for i in range(n_loans)), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Constraint 1: Branch Customer Capacity
    for k in range(n_branches):
        model.addConstr(
            gp.quicksum(amount[i] for i in range(n_loans) if i == k) <= no_of_customers[k] * max_loan_per_customer,
            name=f"branch_capacity_{k}"
        )
    
    # Constraint 2: Customer Credit Score
    for i in range(n_loans):
        model.addConstr(
            amount[i] <= credit_scores[i] * max_loan_per_credit_score_unit,
            name=f"credit_score_limit_{i}"
        )
    
    # Constraint 3: Total Loan Exposure per Customer
    for m in range(n_customers):
        model.addConstr(
            gp.quicksum(amount[i] for i in range(n_loans) if i == m) <= max_total_loan_per_customer,
            name=f"total_exposure_{m}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(n_loans):
            if amount[i].x > 1e-6:
                print(f"amount[{i}] = {amount[i].x:.3f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
if __name__ == "__main__":
    loan_allocation_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 180000.0
**Execution Time**: 0.18 seconds
**Reliability**: high
**Analysis**: Gurobipy achieved the optimal solution efficiently with the fastest execution time.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCPLEX implementation for Loan Allocation Optimization
"""

from docplex.mp.model import Model

def loan_allocation_optimization():
    """Optimize loan allocation across branches to maximize total disbursement"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="loan_allocation")
    
    # Data from the database
    loan_amounts = [5000.0, 10000.0, 7500.0]  # Initial loan amounts
    no_of_customers = [150, 250, 200]         # Number of customers per branch
    credit_scores = [720, 780, 750]           # Credit scores of customers
    
    # Operational parameters
    max_loan_per_customer = 15000
    max_loan_per_credit_score_unit = 600
    max_total_loan_per_customer = 60000
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(loan_amounts) == len(no_of_customers) == len(credit_scores), "Array length mismatch"
    safe_range = range(min(len(loan_amounts), len(no_of_customers), len(credit_scores)))  # Safe indexing
    
    # 2. VARIABLES
    # Decision variable: loan amount to be disbursed for each loan
    amount = {i: mdl.continuous_var(name=f"amount_{i}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total loan amount disbursed
    total_loan = mdl.sum(amount[i] for i in safe_range)
    mdl.maximize(total_loan)
    
    # 4. CONSTRAINTS
    
    # Constraint 1: Branch customer capacity
    for i in safe_range:
        mdl.add_constraint(amount[i] <= no_of_customers[i] * max_loan_per_customer, ctname=f"branch_capacity_{i}")
    
    # Constraint 2: Customer credit score
    for i in safe_range:
        mdl.add_constraint(amount[i] <= credit_scores[i] * max_loan_per_credit_score_unit, ctname=f"credit_score_{i}")
    
    # Constraint 3: Total loan exposure per customer
    for i in safe_range:
        mdl.add_constraint(amount[i] <= max_total_loan_per_customer, ctname=f"total_exposure_{i}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            value = solution.get_value(amount[i])
            if value > 1e-6:
                print(f"amount[{i}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
if __name__ == "__main__":
    loan_allocation_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 180000.0
**Execution Time**: 1.04 seconds
**Reliability**: high
**Analysis**: DOCplex also achieved the optimal solution but with a longer execution time compared to Gurobipy.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

import pyomo.environ as pyo
from pyomo.opt import SolverFactory

def loan_optimization():
    """Optimize loan allocation to maximize total disbursement while adhering to constraints."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Loan amounts (decision variables)
    loan_amounts = [5000.0, 10000.0, 7500.0]
    # Number of customers per branch
    no_of_customers = [150, 250, 200]
    # Customer credit scores
    credit_scores = [720, 780, 750]
    
    # CRITICAL: Validate array lengths before indexing
    assert len(loan_amounts) == len(no_of_customers) == len(credit_scores), "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    n_loans = len(loan_amounts)
    model.L = pyo.RangeSet(1, n_loans)  # 1-based indexing for loans
    
    # 4. PARAMETERS (data containers)
    model.loan_amount = pyo.Param(model.L, initialize={i+1: loan_amounts[i] for i in range(n_loans)})
    model.no_of_customers = pyo.Param(model.L, initialize={i+1: no_of_customers[i] for i in range(n_loans)})
    model.credit_score = pyo.Param(model.L, initialize={i+1: credit_scores[i] for i in range(n_loans)})
    
    # 5. VARIABLES
    # Continuous variables for loan amounts to be disbursed
    model.amount = pyo.Var(model.L, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    # Maximize the total loan amount disbursed
    def obj_rule(model):
        return sum(model.amount[i] for i in model.L)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Constraint 1: Branch customer capacity
    def branch_capacity_rule(model, i):
        return model.amount[i] <= model.no_of_customers[i] * 15000
    model.branch_capacity_constraint = pyo.Constraint(model.L, rule=branch_capacity_rule)
    
    # Constraint 2: Customer credit score
    def credit_score_rule(model, i):
        return model.amount[i] <= model.credit_score[i] * 600
    model.credit_score_constraint = pyo.Constraint(model.L, rule=credit_score_rule)
    
    # Constraint 3: Total loan exposure per customer
    def total_exposure_rule(model, i):
        return model.amount[i] <= 60000
    model.total_exposure_constraint = pyo.Constraint(model.L, rule=total_exposure_rule)
    
    # 8. SOLVING WITH GUROBI (your available solver)
    solver = SolverFactory('gurobi')
    
    # Optional: Set solver options
    solver.options['TimeLimit'] = 300  # 5 minutes
    solver.options['MIPGap'] = 0.01    # 1% gap
    
    # Solve the model
    results = solver.solve(model, tee=True)  # tee=True shows solver output
    
    # 9. RESULT PROCESSING
    # Check solver status
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nLoan amounts disbursed:")
        for i in model.L:
            amount_val = pyo.value(model.amount[i])
            if amount_val > 1e-6:  # Only print non-zero values
                print(f"Loan {i}: {amount_val:.2f}")
        
    elif results.solver.termination_condition == pyo.TerminationCondition.infeasible:
        print("Problem is infeasible")
    elif results.solver.termination_condition == pyo.TerminationCondition.unbounded:
        print("Problem is unbounded")
    else:
        print(f"Solver terminated with condition: {results.solver.termination_condition}")
    
    return model

# Execute the optimization
if __name__ == "__main__":
    loan_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 180000.0
**Execution Time**: 0.98 seconds
**Reliability**: high
**Analysis**: Pyomo achieved the optimal solution but had the longest execution time among the three solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 180000.00 | 0.18s | N/A | N/A |
| Docplex | OPTIMAL | 180000.00 | 1.04s | N/A | N/A |
| Pyomo | OPTIMAL | 180000.00 | 0.98s | N/A | N/A |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Majority Vote Optimal Value**: 180000.0

### Final Recommendation
**Recommended Optimal Value**: 180000.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its optimal solution and significantly faster execution time compared to DOCplex and Pyomo.

### Business Interpretation
**Overall Strategy**: The optimal total loan amount to be disbursed is $180,000, ensuring compliance with all constraints.
**Objective Value Meaning**: The total loan amount disbursed across all loans is maximized to $180,000.
**Resource Allocation Summary**: Loans should be allocated such that branch customer capacity, customer credit scores, and total loan exposure per customer constraints are satisfied.
**Implementation Recommendations**: Ensure that the loan amounts are allocated according to the optimal solution, adhering to the constraints on branch capacity, credit scores, and total exposure per customer.