# 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.  


### Retrieved Values

**Query 1: This is essential for the branch customer capacity constraint.**

```sql
SELECT no_of_customers FROM bank;
```

**Results (CSV format):**
```csv
no_of_customers
150
250
200
```

**Query 2: This is crucial for the credit score-based loan allocation constraint.**

```sql
SELECT credit_score FROM customer;
```

**Results (CSV format):**
```csv
credit_score
720
780
750
```

**Query 3: This is important for the total loan exposure per customer constraint.**

```sql
SELECT amount FROM loan;
```

**Results (CSV format):**
```csv
amount
5000.0
10000.0
7500.0
```

**Query 4: This helps in enforcing the credit score-based loan allocation constraint.**

```sql
SELECT credit_score, credit_score * 600 AS max_loan_per_credit_score FROM customer;
```

**Results (CSV format):**
```csv
credit_score,max_loan_per_credit_score
720,432000
780,468000
750,450000
```

**Query 5: These are fixed constraints and need to be considered in the optimization.**

```sql
SELECT 15000 AS max_loan_per_customer, 60000 AS max_total_loan_exposure;
```

**Results (CSV format):**
```csv
max_loan_per_customer,max_total_loan_exposure
15000,60000
```

**Query 6: This is essential for the branch customer capacity constraint.**

```sql
SELECT no_of_customers, no_of_customers * 15000 AS max_loan_allocation_per_branch FROM bank;
```

**Results (CSV format):**
```csv
no_of_customers,max_loan_allocation_per_branch
150,2250000
250,3750000
200,3000000
```

**Query 7: This is the primary objective to maximize in the optimization problem.**

```sql
SELECT SUM(amount) AS total_loan_disbursed FROM loan;
```

**Results (CSV format):**
```csv
total_loan_disbursed
22500.0
```

**Query 8: This helps in making informed decisions on loan allocation.**

```sql
SELECT b.no_of_customers, c.credit_score FROM bank b JOIN customer c ON b.branch_id = c.branch_id;  -- Assuming there is a branch_id column in both tables;
```

**Error:** Execution failed on sql 'SELECT b.no_of_customers, c.credit_score FROM bank b JOIN customer c ON b.branch_id = c.branch_id;  -- Assuming there is a branch_id column in both tables;': no such column: b.branch_id

