# Complete Optimization Problem and Solution: loan_1

## 1. Problem Context and Goals

### Context  
A mid-sized bank is focused on optimizing its loan allocation strategy across various branches to enhance its overall customer credit score portfolio. The primary decision involves determining the loan amount for each customer, which is treated as a continuous variable. The bank's operational strategy is to maximize the total credit score of customers receiving loans. This is achieved by considering each customer's credit score as a multiplier for the loan amount allocated to them. 

The bank operates under specific financial constraints. It has a total budget allocated for loans across all branches, which serves as an upper limit on the total loan disbursement. Additionally, there is a cap on the maximum loan amount that can be granted to an individual customer, ensuring risk management and compliance with lending policies. Each branch is also required to meet a minimum threshold of total loan disbursement, ensuring that branch-specific lending goals are achieved.

The business configuration includes:
- A total budget of 1,000,000 for loans across all branches, acting as an upper bound constraint.
- A maximum loan amount of 50,000 per customer, serving as an upper bound constraint for individual loans.
- A minimum total loan amount of 100,000 that must be allocated per branch, functioning as a lower bound constraint.

These parameters are designed to ensure that the bank's lending strategy is both financially viable and aligned with its operational goals.

### Goals  
The bank's optimization goal is to maximize the total credit score of its loan portfolio. This involves maximizing the sum of the products of each customer's credit score and the loan amount allocated to them. The success of this optimization is measured by the increase in the total credit score, which directly correlates with the bank's ability to allocate loans effectively while adhering to its financial constraints. The objective is clearly defined in linear terms, focusing on enhancing the bank's credit score portfolio through strategic loan allocations.

## 2. Constraints    

The bank's loan allocation strategy is subject to several linear constraints:

- The total amount of loans allocated to all customers must not exceed the bank's total budget of 1,000,000. This ensures that the bank operates within its financial limits.
- Each customer can receive a loan amount up to a maximum of 50,000. This constraint is in place to manage risk and ensure compliance with lending policies.
- Each branch must allocate a minimum total loan amount of 100,000. This ensures that branches meet their lending targets and contribute to the bank's overall financial goals.

These constraints are designed to guide the bank's loan allocation process, ensuring that it is both strategic and compliant with financial regulations.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing constraint bounds and updating existing tables to improve mapping adequacy. Configuration logic is updated to include scalar parameters for constraints.

CREATE TABLE customer (
  cust_ID INTEGER,
  credit_score FLOAT
);

CREATE TABLE loan (
  loan_ID INTEGER,
  amount FLOAT,
  branch_ID INTEGER
);

CREATE TABLE branch_loans (
  branch_ID INTEGER,
  min_loans FLOAT
);
```

### Data Dictionary  
The data used in this optimization problem is structured to support the bank's loan allocation strategy:

- **Customer Table**: This table contains information about each customer, including their unique identifier and credit score. The credit score is used as a coefficient in the optimization objective, representing the potential benefit of allocating a loan to that customer.

- **Loan Table**: This table records details about each loan, including the loan's unique identifier, the amount allocated, and the branch associated with the loan. The loan amount is the decision variable in the optimization model, determining how much is allocated to each customer.

- **Branch Loans Table**: This table specifies the minimum loan amount required for each branch. It ensures that each branch meets its lending goals, serving as a lower bound constraint in the optimization model.

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

-- Realistic data for customer
INSERT INTO customer (cust_ID, credit_score) VALUES (1, 720);
INSERT INTO customer (cust_ID, credit_score) VALUES (2, 680);
INSERT INTO customer (cust_ID, credit_score) VALUES (3, 750);

-- Realistic data for loan
INSERT INTO loan (loan_ID, amount, branch_ID) VALUES (101, 30000, 1);
INSERT INTO loan (loan_ID, amount, branch_ID) VALUES (102, 45000, 2);
INSERT INTO loan (loan_ID, amount, branch_ID) VALUES (103, 25000, 3);
INSERT INTO loan (loan_ID, amount, branch_ID) VALUES (104, 20000, 1);
INSERT INTO loan (loan_ID, amount, branch_ID) VALUES (105, 35000, 2);

-- Realistic data for branch_loans
INSERT INTO branch_loans (branch_ID, min_loans) VALUES (1, 120000);
INSERT INTO branch_loans (branch_ID, min_loans) VALUES (2, 150000);
INSERT INTO branch_loans (branch_ID, min_loans) VALUES (3, 100000);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_{i} \): Loan amount allocated to customer \( i \). This is a continuous variable representing the loan amount in dollars.

#### Objective Function
Maximize the total credit score of the loan portfolio:
\[ \text{Maximize } \sum_{i} \text{credit\_score}_{i} \times x_{i} \]

Data Source Verification:
- \(\text{credit\_score}_{i}\) comes from `customer.credit_score`.

#### Constraints

1. **Total Budget Constraint**: The total amount of loans allocated must not exceed the bank's total budget.
   \[
   \sum_{i} x_{i} \leq 1,000,000
   \]
   Data Source Verification:
   - Total budget comes from business configuration.

2. **Individual Loan Cap**: Each customer can receive a loan amount up to a maximum of 50,000.
   \[
   x_{i} \leq 50,000 \quad \forall i
   \]
   Data Source Verification:
   - Maximum loan amount per customer comes from business configuration.

3. **Branch Minimum Loan Requirement**: Each branch must allocate a minimum total loan amount.
   \[
   \sum_{i \in \text{branch } j} x_{i} \geq \text{min\_loans}_{j} \quad \forall j
   \]
   Data Source Verification:
   - \(\text{min\_loans}_{j}\) comes from `branch_loans.min_loans`.

#### Additional Information
- The index \( i \) refers to each customer, and the index \( j \) refers to each branch.
- The allocation of loans to customers is subject to the constraints outlined above, ensuring compliance with the bank's financial and operational goals.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation - Retry Attempt 4

import gurobipy as gp
from gurobipy import GRB

def optimize_loan_allocation():
    # 1. MODEL & DATA SETUP
    model = gp.Model("loan_optimization")
    
    # Data from the problem context
    customers = [
        {"cust_ID": 1, "credit_score": 720},
        {"cust_ID": 2, "credit_score": 680},
        {"cust_ID": 3, "credit_score": 750}
    ]
    
    loans = [
        {"loan_ID": 101, "amount": 30000, "branch_ID": 1},
        {"loan_ID": 102, "amount": 45000, "branch_ID": 2},
        {"loan_ID": 103, "amount": 25000, "branch_ID": 3},
        {"loan_ID": 104, "amount": 20000, "branch_ID": 1},
        {"loan_ID": 105, "amount": 35000, "branch_ID": 2}
    ]
    
    branch_loans = [
        {"branch_ID": 1, "min_loans": 120000},
        {"branch_ID": 2, "min_loans": 150000},
        {"branch_ID": 3, "min_loans": 100000}
    ]
    
    total_budget = 1000000
    max_loan_per_customer = 50000
    
    # CRITICAL: Validate array lengths before loops
    assert len(customers) == len(loans), "Array length mismatch"
    
    # 2. VARIABLES
    # Create a dictionary of decision variables for loan amounts
    x = {loan["loan_ID"]: model.addVar(vtype=GRB.CONTINUOUS, name=f"x_{loan['loan_ID']}", lb=0, ub=max_loan_per_customer)
         for loan in loans}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total credit score of the loan portfolio
    model.setObjective(gp.quicksum(customers[i]["credit_score"] * x[loans[i]["loan_ID"]] for i in range(len(loans))), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    # Total Budget Constraint
    model.addConstr(gp.quicksum(x[loan["loan_ID"]] for loan in loans) <= total_budget, name="total_budget")
    
    # Branch Minimum Loan Requirement
    for branch in branch_loans:
        model.addConstr(
            gp.quicksum(x[loan["loan_ID"]] for loan in loans if loan["branch_ID"] == branch["branch_ID"]) >= branch["min_loans"],
            name=f"min_loans_branch_{branch['branch_ID']}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for loan in loans:
            loan_id = loan["loan_ID"]
            if x[loan_id].x > 1e-6:
                print(f"Loan ID {loan_id}: {x[loan_id].x:.2f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")

# Run the optimization
optimize_loan_allocation()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpkmn06y32.py", line 72, in <module>
    optimize_loan_allocation()
  File "/tmp/tmpkmn06y32.py", line 35, in optimize_loan_allocation
    assert len(customers) == len(loans), "Array length mismatch"
AssertionError: Array length mismatch

**Analysis**: Gurobipy encountered an 'Array length mismatch' error, indicating a potential issue with the input data structure or indexing.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation - Retry Attempt 4

from docplex.mp.model import Model

def optimize_loan_allocation():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="loan_optimization")
    
    # Data from the problem context
    customers = [
        {"cust_ID": 1, "credit_score": 720},
        {"cust_ID": 2, "credit_score": 680},
        {"cust_ID": 3, "credit_score": 750}
    ]
    
    loans = [
        {"loan_ID": 101, "amount": 30000, "branch_ID": 1},
        {"loan_ID": 102, "amount": 45000, "branch_ID": 2},
        {"loan_ID": 103, "amount": 25000, "branch_ID": 3},
        {"loan_ID": 104, "amount": 20000, "branch_ID": 1},
        {"loan_ID": 105, "amount": 35000, "branch_ID": 2}
    ]
    
    branch_loans = [
        {"branch_ID": 1, "min_loans": 120000},
        {"branch_ID": 2, "min_loans": 150000},
        {"branch_ID": 3, "min_loans": 100000}
    ]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(customers) == 3, "Customer data length mismatch"
    assert len(loans) == 5, "Loan data length mismatch"
    assert len(branch_loans) == 3, "Branch loans data length mismatch"
    
    # Safe range for indexing
    safe_range_customers = range(len(customers))
    safe_range_loans = range(len(loans))
    safe_range_branches = range(len(branch_loans))
    
    # 2. VARIABLES
    x = {loan['loan_ID']: mdl.continuous_var(name=f"x_{loan['loan_ID']}", lb=0, ub=50000) for loan in loans}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(customers[i]['credit_score'] * x[loans[i]['loan_ID']] for i in safe_range_loans)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Total Budget Constraint
    total_loan_amount = mdl.sum(x[loan['loan_ID']] for loan in loans)
    mdl.add_constraint(total_loan_amount <= 1000000, ctname="total_budget")
    
    # Branch Minimum Loan Requirement
    for branch in branch_loans:
        branch_id = branch['branch_ID']
        min_loans = branch['min_loans']
        branch_loan_sum = mdl.sum(x[loan['loan_ID']] for loan in loans if loan['branch_ID'] == branch_id)
        mdl.add_constraint(branch_loan_sum >= min_loans, ctname=f"min_loans_branch_{branch_id}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for loan in loans:
            loan_id = loan['loan_ID']
            value = solution.get_value(x[loan_id])
            if value > 1e-6:
                print(f"x[{loan_id}] = {value:.2f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

# Run the optimization
optimize_loan_allocation()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmpsycuiupa.py", line 75, in <module>
    optimize_loan_allocation()
  File "/tmp/tmpsycuiupa.py", line 44, in optimize_loan_allocation
    objective = mdl.sum(customers[i]['credit_score'] * x[loans[i]['loan_ID']] for i in safe_range_loans)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/docplex/mp/model.py", line 3342, in sum
    return self._aggregator.sum(args)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/docplex/mp/aggregator.py", line 198, in sum
    sum_res = self._sum_with_iter(sum_args)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/docplex/mp/aggregator.py", line 221, in _sum_with_iter
    for item in args:
  File "/tmp/tmpsycuiupa.py", line 44, in <genexpr>
    objective = mdl.sum(customers[i]['credit_score'] * x[loans[i]['loan_ID']] for i in safe_range_loans)
IndexError: list index out of range

**Analysis**: DOCplex faced an 'IndexError: list index out of range', suggesting a problem with accessing elements in the data arrays, possibly due to incorrect indexing or data preparation.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation - Retry Attempt 4

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

def optimize_loan_allocation():
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()

    # 2. DATA SETUP
    # Customer data
    customers = [
        {'cust_ID': 1, 'credit_score': 720},
        {'cust_ID': 2, 'credit_score': 680},
        {'cust_ID': 3, 'credit_score': 750}
    ]

    # Loan data
    loans = [
        {'loan_ID': 101, 'amount': 30000, 'branch_ID': 1},
        {'loan_ID': 102, 'amount': 45000, 'branch_ID': 2},
        {'loan_ID': 103, 'amount': 25000, 'branch_ID': 3},
        {'loan_ID': 104, 'amount': 20000, 'branch_ID': 1},
        {'loan_ID': 105, 'amount': 35000, 'branch_ID': 2}
    ]

    # Branch loans data
    branch_loans = [
        {'branch_ID': 1, 'min_loans': 120000},
        {'branch_ID': 2, 'min_loans': 150000},
        {'branch_ID': 3, 'min_loans': 100000}
    ]

    # Validate array lengths
    assert len(customers) == 3, "Customer data length mismatch"
    assert len(loans) == 5, "Loan data length mismatch"
    assert len(branch_loans) == 3, "Branch loans data length mismatch"

    # 3. SETS
    model.C = pyo.Set(initialize=[c['cust_ID'] for c in customers])
    model.L = pyo.Set(initialize=[l['loan_ID'] for l in loans])
    model.B = pyo.Set(initialize=[b['branch_ID'] for b in branch_loans])

    # 4. PARAMETERS
    model.credit_score = pyo.Param(model.C, initialize={c['cust_ID']: c['credit_score'] for c in customers})
    model.min_loans = pyo.Param(model.B, initialize={b['branch_ID']: b['min_loans'] for b in branch_loans})

    # 5. VARIABLES
    model.x = pyo.Var(model.L, within=pyo.NonNegativeReals, bounds=(0, 50000))

    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.credit_score[loans[i]['cust_ID']] * model.x[loans[i]['loan_ID']] for i in range(len(loans)))
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)

    # 7. CONSTRAINTS
    # Total Budget Constraint
    def total_budget_rule(model):
        return sum(model.x[l['loan_ID']] for l in loans) <= 1000000
    model.total_budget_constraint = pyo.Constraint(rule=total_budget_rule)

    # Branch Minimum Loan Requirement
    def branch_min_loan_rule(model, b):
        return sum(model.x[l['loan_ID']] for l in loans if l['branch_ID'] == b) >= model.min_loans[b]
    model.branch_min_loan_constraint = pyo.Constraint(model.B, rule=branch_min_loan_rule)

    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')

    # Solve the model
    results = solver.solve(model, tee=True)

    # 9. RESULT PROCESSING
    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 allocations:")
        for l in loans:
            loan_val = pyo.value(model.x[l['loan_ID']])
            if loan_val > 1e-6:  # Only print non-zero values
                print(f"Loan ID {l['loan_ID']}: {loan_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
optimize_loan_allocation()
```

### Execution Results
**Status**: ERROR
**Error**: Traceback (most recent call last):
  File "/tmp/tmp28ji7lgm.py", line 95, in <module>
    optimize_loan_allocation()
  File "/tmp/tmp28ji7lgm.py", line 54, in optimize_loan_allocation
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 571, in __setattr__
    self.add_component(name, val)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/block.py", line 1101, in add_component
    val.construct(data)
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/objective.py", line 336, in construct
    ans = self._setitem_when_not_present(index, rule(block, index))
  File "/dccstor/nl2opt/miniforge3/envs/nl2opt_optim/lib/python3.10/site-packages/pyomo/core/base/initializer.py", line 485, in __call__
    return self._fcn(parent)
  File "/tmp/tmp28ji7lgm.py", line 53, in obj_rule
    return sum(model.credit_score[loans[i]['cust_ID']] * model.x[loans[i]['loan_ID']] for i in range(len(loans)))
  File "/tmp/tmp28ji7lgm.py", line 53, in <genexpr>
    return sum(model.credit_score[loans[i]['cust_ID']] * model.x[loans[i]['loan_ID']] for i in range(len(loans)))
KeyError: 'cust_ID'

**Analysis**: Pyomo encountered a 'KeyError: 'cust_ID'', indicating a missing or incorrectly referenced key in the data dictionary, which needs to be addressed.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | ERROR | N/A | 0.17s | N/A | 4 |
| Docplex | ERROR | N/A | 2.59s | N/A | 4 |
| Pyomo | ERROR | N/A | 1.21s | N/A | 4 |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Inconsistent Solvers**: gurobipy, docplex, pyomo
**Potential Issues**:
- Data structure inconsistencies across solvers
- Incorrect indexing or key references in data
- Mismatch between customer and loan data arrays
**Solver Retry Summary**: gurobipy: 4 attempts, docplex: 4 attempts, pyomo: 4 attempts

### Final Recommendation
**Confidence Level**: LOW
**Preferred Solver(s)**: multiple
**Reasoning**: All solvers encountered errors due to data issues. Once data issues are resolved, multiple solvers should be tested again to ensure consistency and reliability.

### Business Interpretation
**Overall Strategy**: The current solver errors prevent any reliable business interpretation or decision-making. The data and model setup need to be corrected before proceeding.
**Objective Value Meaning**: The optimal objective value would represent the maximum total credit score achievable within the given budget and constraints.
**Resource Allocation Summary**: Resources should be allocated to maximize the credit score of the loan portfolio while adhering to budget and individual loan constraints.
**Implementation Recommendations**: Review and correct data structures and indexing issues. Ensure that customer and loan data arrays are correctly aligned and referenced. Re-run the optimization with corrected data.