=== Problem Context ===
# Complete Optimization Problem and Solution: small_bank_1

## 1. Problem Context and Goals

### Context  
A small bank is focused on optimizing the allocation of funds between savings and checking accounts for its customers to maximize interest income while ensuring liquidity and regulatory compliance. The bank must decide how much to allocate to each customer's savings and checking accounts based on their total available funds. The interest rates for savings and checking accounts are fixed at 3% and 1%, respectively, and these rates directly influence the bank's income.  

The bank must adhere to several operational rules:  
- Each customer's total allocation to savings and checking accounts cannot exceed their total available funds.  
- Savings accounts must maintain a minimum balance of $100 to avoid fees and ensure account activity.  
- Checking accounts must maintain a minimum balance of $50 for the same reasons.  
- Savings accounts cannot exceed a maximum balance of $50,000 to manage risk and liquidity.  
- Checking accounts cannot exceed a maximum balance of $30,000 for the same purpose.  
- The bank’s total funds across all accounts cannot exceed $1,000,000, reflecting its operational scale.  

These rules ensure that the bank’s decisions are both profitable and compliant with regulatory requirements. The problem is structured to maintain a linear relationship between decision variables and constraints, ensuring computational efficiency and scalability.

### Goals  
The primary goal of this optimization is to maximize the bank’s total interest income from savings and checking accounts. This is achieved by strategically allocating each customer’s funds between their savings and checking accounts, weighted by the respective interest rates. Success is measured by the total interest income generated, which is directly influenced by the interest rates and the allocated balances. The optimization ensures that all operational and regulatory constraints are met while achieving the highest possible income.

## 2. Constraints  

The optimization must adhere to the following constraints:  
1. **Total Funds Constraint**: For each customer, the sum of their savings and checking account balances cannot exceed their total available funds.  
2. **Minimum Savings Balance Constraint**: Each customer’s savings account balance must be at least $100.  
3. **Minimum Checking Balance Constraint**: Each customer’s checking account balance must be at least $50.  
4. **Maximum Savings Balance Constraint**: Each customer’s savings account balance cannot exceed $50,000.  
5. **Maximum Checking Balance Constraint**: Each customer’s checking account balance cannot exceed $30,000.  
6. **Total Bank Funds Constraint**: The sum of all savings and checking account balances across all customers cannot exceed the bank’s total available funds of $1,000,000.  

These constraints ensure that the bank’s allocations are both feasible and compliant with operational and regulatory requirements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for missing optimization requirements and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE CUSTOMER_FUNDS (
  custid INTEGER,
  total_funds FLOAT
);

CREATE TABLE SAVINGS (
  custid INTEGER,
  balance FLOAT
);

CREATE TABLE CHECKING (
  custid INTEGER,
  balance FLOAT
);
```

### Data Dictionary  
- **CUSTOMER_FUNDS**:  
  - **Purpose**: Tracks the total funds available for each customer to allocate between savings and checking accounts.  
  - **Columns**:  
    - `custid`: Unique identifier for each customer.  
    - `total_funds`: Total funds available for the customer, used to constrain the sum of their account balances.  

- **SAVINGS**:  
  - **Purpose**: Tracks the balance in each customer’s savings account.  
  - **Columns**:  
    - `custid`: Unique identifier for each customer.  
    - `balance`: The balance in the savings account, which is a decision variable in the optimization.  

- **CHECKING**:  
  - **Purpose**: Tracks the balance in each customer’s checking account.  
  - **Columns**:  
    - `custid`: Unique identifier for each customer.  
    - `balance`: The balance in the checking account, which is a decision variable in the optimization.  


=== Schema ===
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for missing optimization requirements and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE CUSTOMER_FUNDS (
  custid INTEGER,
  total_funds FLOAT
);

CREATE TABLE SAVINGS (
  custid INTEGER,
  balance FLOAT
);

CREATE TABLE CHECKING (
  custid INTEGER,
  balance FLOAT
);



=== LLM Response ===
```sql
-- Query Description: Retrieve the total funds available for each customer, which is essential for the Total Funds Constraint.
-- This data ensures that the sum of savings and checking balances does not exceed the customer's total funds.
SELECT custid, total_funds
FROM CUSTOMER_FUNDS;

-- Query Description: Retrieve the current balances in savings accounts for each customer.
-- This data is needed to ensure the Minimum and Maximum Savings Balance Constraints are met.
SELECT custid, balance
FROM SAVINGS;

-- Query Description: Retrieve the current balances in checking accounts for each customer.
-- This data is needed to ensure the Minimum and Maximum Checking Balance Constraints are met.
SELECT custid, balance
FROM CHECKING;

-- Query Description: Retrieve the sum of all savings and checking account balances across all customers.
-- This data is crucial for the Total Bank Funds Constraint to ensure the bank's total funds do not exceed $1,000,000.
SELECT SUM(s.balance) + SUM(c.balance) AS total_bank_funds
FROM SAVINGS s
JOIN CHECKING c ON s.custid = c.custid;

-- Query Description: Retrieve the total funds available for each customer along with their current savings and checking balances.
-- This data is useful for verifying the Total Funds Constraint for each customer.
SELECT cf.custid, cf.total_funds, s.balance AS savings_balance, c.balance AS checking_balance
FROM CUSTOMER_FUNDS cf
LEFT JOIN SAVINGS s ON cf.custid = s.custid
LEFT JOIN CHECKING c ON cf.custid = c.custid;

-- Query Description: Retrieve customers whose savings account balances are below the minimum required balance of $100.
-- This data helps identify customers who need adjustments to meet the Minimum Savings Balance Constraint.
SELECT custid, balance
FROM SAVINGS
WHERE balance < 100;

-- Query Description: Retrieve customers whose checking account balances are below the minimum required balance of $50.
-- This data helps identify customers who need adjustments to meet the Minimum Checking Balance Constraint.
SELECT custid, balance
FROM CHECKING
WHERE balance < 50;

-- Query Description: Retrieve customers whose savings account balances exceed the maximum allowed balance of $50,000.
-- This data helps identify customers who need adjustments to meet the Maximum Savings Balance Constraint.
SELECT custid, balance
FROM SAVINGS
WHERE balance > 50000;

-- Query Description: Retrieve customers whose checking account balances exceed the maximum allowed balance of $30,000.
-- This data helps identify customers who need adjustments to meet the Maximum Checking Balance Constraint.
SELECT custid, balance
FROM CHECKING
WHERE balance > 30000;

-- Query Description: Retrieve the total interest income generated from savings and checking accounts.
-- This data is essential for the objective function to maximize the bank's total interest income.
SELECT SUM(s.balance * 0.03) + SUM(c.balance * 0.01) AS total_interest_income
FROM SAVINGS s
JOIN CHECKING c ON s.custid = c.custid;

-- Query Description: Retrieve the total funds available for each customer along with the sum of their savings and checking balances.
-- This data helps ensure that the sum of savings and checking balances does not exceed the customer's total funds.
SELECT cf.custid, cf.total_funds, (s.balance + c.balance) AS total_allocated_funds
FROM CUSTOMER_FUNDS cf
LEFT JOIN SAVINGS s ON cf.custid = s.custid
LEFT JOIN CHECKING c ON cf.custid = c.custid;
```

These queries are designed to retrieve the necessary data for the optimization problem, ensuring that all constraints are met and the objective function is maximized. Each query serves a specific purpose in the context of the problem, from verifying constraints to calculating the total interest income.
