# 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);
```