# Complete Optimization Problem and Solution: customers_card_transactions

## 1. Problem Context and Goals

### Context  
A financial institution is focused on optimizing the allocation of credit limits to customer accounts. The primary decision involves determining the appropriate credit limit for each account, which is treated as a continuous variable. The institution aims to maximize the total expected transaction volume, which is directly influenced by the credit limits assigned to each account. This objective aligns with the operational goal of maximizing the sum of expected transaction volumes across all accounts, where each account's expected volume acts as a coefficient in the optimization process.

The business configuration includes critical parameters such as the total credit available for allocation across all accounts, which serves as a constraint to ensure that the total assigned credit does not exceed this limit. Additionally, there is a minimum credit limit requirement for an account to be considered active, ensuring that accounts have sufficient credit to facilitate meaningful transactions. These parameters are essential for maintaining a balance between business growth and risk management.

The data used in this optimization process reflects current operational information, ensuring that decisions are based on realistic and up-to-date insights. The constraints are designed to be linear, avoiding any nonlinear relationships such as variable products or divisions, and are directly tied to the business configuration parameters.

### Goals  
The primary goal of this optimization problem is to maximize the total expected transaction volume across all customer accounts. This is achieved by strategically allocating credit limits to each account, with the objective of maximizing the sum of the products of expected transaction volumes and their respective credit limits. Success in this optimization is measured by the increase in total expected transaction volume, which is directly linked to the coefficients derived from the expected volumes of each account. The optimization goal is clearly defined in linear terms, ensuring a straightforward and efficient solution process.

## 2. Constraints    

The optimization problem is subject to several linear constraints that ensure the solution is both feasible and aligned with business objectives:

- The total credit allocated across all accounts must not exceed the total available credit. This constraint ensures that the institution operates within its financial capacity and manages risk effectively.
- Each account's credit limit must not exceed its maximum allowable credit limit. This constraint is crucial for maintaining individual account risk limits and preventing overexposure.
- For an account to be considered active, its credit limit must meet or exceed the minimum credit limit requirement. This ensures that active accounts have enough credit to support meaningful transactions and contribute to the overall transaction volume.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, facilitating a straightforward optimization process.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization data, modifying existing tables to fill mapping gaps, and moving certain parameters to configuration logic for better management.

CREATE TABLE account_expected_transaction_volume (
  account_id INTEGER,
  expected_volume FLOAT
);

CREATE TABLE account_credit_limits (
  account_id INTEGER,
  credit_limit FLOAT,
  max_credit_limit FLOAT,
  is_active BOOLEAN
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles:

- **account_expected_transaction_volume**: This table stores the expected transaction volume for each account. The expected volume serves as a coefficient in the optimization objective, representing the potential transaction activity associated with each account.

  - **account_id**: A unique identifier for each account, used to index expected transaction volumes.
  - **expected_volume**: The anticipated transaction volume for the account, acting as a coefficient in the objective function.

- **account_credit_limits**: This table contains information about the credit limits and status of each account. It plays a crucial role in defining the constraints of the optimization problem.

  - **account_id**: A unique identifier for each account, used to index credit limits.
  - **credit_limit**: The credit limit allocated to the account, which is the decision variable in the optimization model.
  - **max_credit_limit**: The maximum allowable credit limit for the account, serving as a constraint bound.
  - **is_active**: A boolean indicator of whether the account is active, determining if the credit limit can be greater than zero.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical credit limits and transaction volumes observed in financial institutions, ensuring a balance between risk management and business growth objectives.

-- Realistic data for account_expected_transaction_volume
INSERT INTO account_expected_transaction_volume (account_id, expected_volume) VALUES (1, 1200.0);
INSERT INTO account_expected_transaction_volume (account_id, expected_volume) VALUES (2, 1800.0);
INSERT INTO account_expected_transaction_volume (account_id, expected_volume) VALUES (3, 2200.0);

-- Realistic data for account_credit_limits
INSERT INTO account_credit_limits (account_id, credit_limit, max_credit_limit, is_active) VALUES (1, 800.0, 2000.0, True);
INSERT INTO account_credit_limits (account_id, credit_limit, max_credit_limit, is_active) VALUES (2, 1500.0, 2500.0, True);
INSERT INTO account_credit_limits (account_id, credit_limit, max_credit_limit, is_active) VALUES (3, 1800.0, 3000.0, True);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_i \): Credit limit allocated to account \( i \). This is a continuous variable representing the decision to be made for each account.

#### Objective Function
Maximize the total expected transaction volume across all accounts:

\[
\text{Maximize } \sum_{i} \text{expected\_volume}_i \times x_i
\]

Where:
- \(\text{expected\_volume}_i\) is the expected transaction volume for account \( i \) from the `account_expected_transaction_volume` table.

#### Constraints
1. Total credit allocated across all accounts must not exceed the total available credit:
   \[
   \sum_{i} x_i \leq \text{Total\_Available\_Credit}
   \]

2. Each account's credit limit must not exceed its maximum allowable credit limit:
   \[
   x_i \leq \text{max\_credit\_limit}_i \quad \forall i
   \]

3. For an account to be considered active, its credit limit must meet or exceed the minimum credit limit requirement:
   \[
   x_i \geq \text{Minimum\_Credit\_Limit} \quad \text{if } \text{is\_active}_i = \text{True}
   \]

Where:
- \(\text{max\_credit\_limit}_i\) is the maximum allowable credit limit for account \( i \) from the `account_credit_limits` table.
- \(\text{is\_active}_i\) is a boolean indicator from the `account_credit_limits` table.
- \(\text{Total\_Available\_Credit}\) and \(\text{Minimum\_Credit\_Limit}\) are business configuration parameters.

Data Source Verification:
- \(\text{expected\_volume}_i\) from `account_expected_transaction_volume.expected_volume`
- \(\text{max\_credit\_limit}_i\) from `account_credit_limits.max_credit_limit`
- \(\text{is\_active}_i\) from `account_credit_limits.is_active`
- \(\text{Total\_Available\_Credit}\) and \(\text{Minimum\_Credit\_Limit}\) are assumed to be provided as business configuration parameters outside the database schema.

This linear model can be solved using standard linear programming techniques to determine the optimal credit limits for maximizing expected transaction volumes while adhering to the constraints.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_credit_limits():
    """Optimize credit limits to maximize expected transaction volume."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("customers_card_transactions")
    
    # Data from the database
    account_ids = [1, 2, 3]
    expected_volumes = [1200.0, 1800.0, 2200.0]
    max_credit_limits = [2000.0, 2500.0, 3000.0]
    is_active = [True, True, True]
    
    # Business configuration parameters
    Total_Available_Credit = 5000.0
    Minimum_Credit_Limit = 500.0
    
    # CRITICAL: Validate array lengths before loops
    assert len(account_ids) == len(expected_volumes) == len(max_credit_limits) == len(is_active), "Array length mismatch"
    
    # 2. VARIABLES
    # Variable dictionaries for credit limits
    x = {i: model.addVar(vtype=GRB.CONTINUOUS, name=f"x_{account_ids[i]}", lb=0) 
         for i in range(len(account_ids))}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total expected transaction volume
    model.setObjective(gp.quicksum(expected_volumes[i] * x[i] for i in range(len(account_ids))), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Total credit allocated must not exceed the total available credit
    model.addConstr(gp.quicksum(x[i] for i in range(len(account_ids))) <= Total_Available_Credit, name="total_credit_limit")
    
    # Each account's credit limit must not exceed its maximum allowable credit limit
    for i in range(len(account_ids)):
        model.addConstr(x[i] <= max_credit_limits[i], name=f"max_credit_limit_{account_ids[i]}")
    
    # For an account to be considered active, its credit limit must meet or exceed the minimum credit limit requirement
    for i in range(len(account_ids)):
        if is_active[i]:
            model.addConstr(x[i] >= Minimum_Credit_Limit, name=f"min_credit_limit_{account_ids[i]}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(len(account_ids)):
            if x[i].x > 1e-6:
                print(f"x[{account_ids[i]}] = {x[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
optimize_credit_limits()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 9900000.0
**Execution Time**: 0.64 seconds
**Reliability**: high
**Analysis**: Gurobipy achieved an optimal solution quickly, indicating efficient handling of the problem size and constraints.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_credit_limits():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="customers_card_transactions")

    # Data from the database
    account_ids = [1, 2, 3]
    expected_volumes = [1200.0, 1800.0, 2200.0]
    max_credit_limits = [2000.0, 2500.0, 3000.0]
    is_active = [True, True, True]

    # Business configuration parameters
    Total_Available_Credit = 5000.0
    Minimum_Credit_Limit = 500.0

    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(account_ids) == len(expected_volumes) == len(max_credit_limits) == len(is_active), "Array length mismatch"
    safe_range = range(len(account_ids))  # Safe indexing

    # 2. VARIABLES
    # Variable dictionaries (most common)
    x = {i: mdl.continuous_var(name=f"x_{account_ids[i]}", lb=0) for i in safe_range}

    # 3. OBJECTIVE FUNCTION
    # Use mdl.sum() for summations (DOCplex specific)
    objective = mdl.sum(expected_volumes[i] * x[i] for i in safe_range)
    mdl.maximize(objective)

    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS

    # Total credit allocated across all accounts must not exceed the total available credit
    total_credit_usage = mdl.sum(x[i] for i in safe_range)
    mdl.add_constraint(total_credit_usage <= Total_Available_Credit, ctname="total_credit_limit")

    # Each account's credit limit must not exceed its maximum allowable credit limit
    for i in safe_range:
        mdl.add_constraint(x[i] <= max_credit_limits[i], ctname=f"max_credit_limit_{account_ids[i]}")

    # For an account to be considered active, its credit limit must meet or exceed the minimum credit limit requirement
    for i in safe_range:
        if is_active[i]:
            mdl.add_constraint(x[i] >= Minimum_Credit_Limit, ctname=f"min_credit_limit_{account_ids[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(x[i])
            if value > 1e-6:
                print(f"x[{account_ids[i]}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Run the optimization
optimize_credit_limits()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 9900000.0
**Execution Time**: 9.32 seconds
**Reliability**: high
**Analysis**: DOCplex also reached an optimal solution, though with a longer execution time compared to Gurobipy, suggesting it is reliable but less efficient.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def optimize_credit_limits():
    """Optimize credit limits to maximize expected transaction volume."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Example data based on the problem context
    account_ids = [1, 2, 3]
    expected_volumes = {1: 1200.0, 2: 1800.0, 3: 2200.0}
    max_credit_limits = {1: 2000.0, 2: 2500.0, 3: 3000.0}
    is_active = {1: True, 2: True, 3: True}
    
    # Business configuration parameters
    Total_Available_Credit = 5000.0
    Minimum_Credit_Limit = 500.0
    
    # CRITICAL: Validate array lengths before indexing
    assert len(expected_volumes) == len(max_credit_limits) == len(is_active) == len(account_ids), "Array length mismatch"
    
    # 3. SETS
    model.I = pyo.Set(initialize=account_ids)
    
    # 4. PARAMETERS
    model.expected_volume = pyo.Param(model.I, initialize=expected_volumes)
    model.max_credit_limit = pyo.Param(model.I, initialize=max_credit_limits)
    model.is_active = pyo.Param(model.I, initialize=is_active)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.expected_volume[i] * model.x[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Total credit constraint
    def total_credit_rule(model):
        return sum(model.x[i] for i in model.I) <= Total_Available_Credit
    model.total_credit_constraint = pyo.Constraint(rule=total_credit_rule)
    
    # Maximum credit limit constraint
    def max_credit_limit_rule(model, i):
        return model.x[i] <= model.max_credit_limit[i]
    model.max_credit_limit_constraint = pyo.Constraint(model.I, rule=max_credit_limit_rule)
    
    # Minimum credit limit for active accounts
    def min_credit_limit_rule(model, i):
        if model.is_active[i]:
            return model.x[i] >= Minimum_Credit_Limit
        else:
            return pyo.Constraint.Skip
    model.min_credit_limit_constraint = pyo.Constraint(model.I, rule=min_credit_limit_rule)
    
    # 8. SOLVING WITH GUROBI
    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)
    
    # 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):.2f}")
        
        # Extract variable values
        print("\nVariable values:")
        for i in model.I:
            x_val = pyo.value(model.x[i])
            if x_val > 1e-6:  # Only print non-zero values
                print(f"x[{i}] = {x_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

# Run the optimization
optimize_credit_limits()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 9900000.0
**Execution Time**: 4.19 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution with a moderate execution time, demonstrating good reliability and efficiency.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 9900000.00 | 0.64s | N/A | N/A |
| Docplex | OPTIMAL | 9900000.00 | 9.32s | N/A | N/A |
| Pyomo | OPTIMAL | 9900000.00 | 4.19s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 9900000.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its fastest execution time while maintaining high reliability, making it suitable for large-scale or time-sensitive applications.

### Business Interpretation
**Overall Strategy**: The optimal allocation of credit limits maximizes the expected transaction volume, ensuring efficient use of available credit resources.
**Objective Value Meaning**: The optimal objective value of 9900000.0 represents the maximum expected transaction volume achievable under the given constraints.
**Resource Allocation Summary**: Resources should be allocated to accounts in a way that maximizes transaction volume while respecting credit limits and activation requirements.
**Implementation Recommendations**: Implement the solution by adjusting credit limits according to the optimal values determined, ensuring compliance with all constraints and business rules.