# Complete Optimization Problem and Solution: customers_card_transactions

## 1. Problem Context and Goals

### Context  
A bank is focused on reducing the total transaction fees incurred by its customers by strategically allocating transactions across different card types. Each card type has a distinct fee structure, and the bank aims to optimize these allocations to minimize costs. The bank must ensure that the total transaction amount per customer does not exceed their predefined account limits. This operational decision-making process involves determining the appropriate transaction amounts for each card type while adhering to customer-specific constraints.  

The business configuration includes key parameters such as the fee associated with each card type, which directly influences the total transaction fee calculation. Additionally, the maximum transaction amount allowed per customer serves as a critical constraint to ensure compliance with account limits. The total transaction fee is calculated by summing the product of the fee for each card type and the corresponding transaction amount allocated to that card. This approach ensures a linear relationship between the decision variables and the objective, avoiding any nonlinear complexities such as variable products or divisions.  

### Goals  
The primary goal of this optimization problem is to minimize the total transaction fees incurred by the bank’s customers. This is achieved by optimizing the allocation of transaction amounts across different card types, each with its own fee structure. Success is measured by the reduction in the total fee, which is directly influenced by the fees associated with each card type and the transaction amounts allocated to them. The optimization process ensures that the total transaction amount per customer remains within their account limits, maintaining operational feasibility while achieving cost efficiency.  

## 2. Constraints  

The optimization problem is subject to one key constraint: the total transaction amount allocated to all card types for a given customer must not exceed their account limit. This ensures that the bank’s operational decisions remain within the bounds of each customer’s financial capacity. The constraint is expressed as the sum of transaction amounts across all card types for a customer being less than or equal to their account limit. This linear constraint aligns with the business requirement of maintaining customer-specific transaction limits while optimizing fee costs.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added Transaction_Allocations table to map missing decision variables, updated business configuration logic to include scalar parameters and formulas, and ensured schema follows normalization principles.

CREATE TABLE Card_Fees (
  card_type_code STRING,
  fee FLOAT
);

CREATE TABLE Account_Limits (
  customer_id INTEGER,
  account_limit FLOAT
);

CREATE TABLE Transaction_Allocations (
  customer_id INTEGER,
  card_id STRING,
  transaction_amount FLOAT
);
```

### Data Dictionary  
- **Card_Fees**:  
  - **Business Purpose**: Stores the fee associated with each card type.  
  - **Optimization Role**: Provides the coefficients for the objective function.  
  - **Columns**:  
    - `card_type_code`: Represents the type of card (e.g., VISA, MASTERCARD).  
    - `fee`: The fee associated with the card type, used to calculate the total transaction fee.  

- **Account_Limits**:  
  - **Business Purpose**: Defines the maximum transaction amount allowed per customer.  
  - **Optimization Role**: Provides the bounds for the constraints.  
  - **Columns**:  
    - `customer_id`: Unique identifier for the customer.  
    - `account_limit`: The maximum transaction amount allowed for the customer.  

- **Transaction_Allocations**:  
  - **Business Purpose**: Tracks the amount of transaction allocated to each card type per customer.  
  - **Optimization Role**: Represents the decision variables in the optimization model.  
  - **Columns**:  
    - `customer_id`: Unique identifier for the customer.  
    - `card_id`: Unique identifier for the card.  
    - `transaction_amount`: The amount of transaction allocated to the card.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical banking transaction fees, account limits, and transaction amounts, ensuring they align with real-world scenarios and enable a meaningful optimization problem.

-- Realistic data for Card_Fees
INSERT INTO Card_Fees (card_type_code, fee) VALUES ('VISA', 0.02);
INSERT INTO Card_Fees (card_type_code, fee) VALUES ('MASTERCARD', 0.03);
INSERT INTO Card_Fees (card_type_code, fee) VALUES ('AMEX', 0.035);

-- Realistic data for Account_Limits
INSERT INTO Account_Limits (customer_id, account_limit) VALUES (1, 1000);
INSERT INTO Account_Limits (customer_id, account_limit) VALUES (2, 1500);
INSERT INTO Account_Limits (customer_id, account_limit) VALUES (3, 2000);

-- Realistic data for Transaction_Allocations
INSERT INTO Transaction_Allocations (customer_id, card_id, transaction_amount) VALUES (1, 'CARD1', 100);
INSERT INTO Transaction_Allocations (customer_id, card_id, transaction_amount) VALUES (2, 'CARD2', 200);
INSERT INTO Transaction_Allocations (customer_id, card_id, transaction_amount) VALUES (3, 'CARD3', 300);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
Let \( x_{i,j} \) represent the transaction amount allocated to card type \( j \) for customer \( i \), where:
- \( i \in \{1, 2, 3\} \) (customer IDs)
- \( j \in \{\text{VISA}, \text{MASTERCARD}, \text{AMEX}\} \) (card types)

#### Objective Function
Minimize the total transaction fees:
\[
\text{Minimize } Z = 0.02 \cdot (x_{1,\text{VISA}} + x_{2,\text{VISA}} + x_{3,\text{VISA}}) + 0.03 \cdot (x_{1,\text{MASTERCARD}} + x_{2,\text{MASTERCARD}} + x_{3,\text{MASTERCARD}}) + 0.035 \cdot (x_{1,\text{AMEX}} + x_{2,\text{AMEX}} + x_{3,\text{AMEX}})
\]

#### Constraints
Ensure the total transaction amount for each customer does not exceed their account limit:
\[
x_{1,\text{VISA}} + x_{1,\text{MASTERCARD}} + x_{1,\text{AMEX}} \leq 1000 \quad \text{(Customer 1)}
\]
\[
x_{2,\text{VISA}} + x_{2,\text{MASTERCARD}} + x_{2,\text{AMEX}} \leq 1500 \quad \text{(Customer 2)}
\]
\[
x_{3,\text{VISA}} + x_{3,\text{MASTERCARD}} + x_{3,\text{AMEX}} \leq 2000 \quad \text{(Customer 3)}
\]

Non-negativity constraints:
\[
x_{i,j} \geq 0 \quad \forall i, j
\]

#### Data Source Verification
- **Objective Function Coefficients**:  
  - \( 0.02 \): `Card_Fees.fee` for VISA  
  - \( 0.03 \): `Card_Fees.fee` for MASTERCARD  
  - \( 0.035 \): `Card_Fees.fee` for AMEX  

- **Constraint Constants**:  
  - \( 1000 \): `Account_Limits.account_limit` for Customer 1  
  - \( 1500 \): `Account_Limits.account_limit` for Customer 2  
  - \( 2000 \): `Account_Limits.account_limit` for Customer 3  

This is a complete, immediately solvable LINEAR mathematical model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy Implementation for Customer Card Transactions Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def optimize_transaction_fees():
    """Optimize transaction fees by allocating transactions across card types."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("customer_card_transactions")
    
    # Data from the problem
    card_types = ['VISA', 'MASTERCARD', 'AMEX']
    customers = [1, 2, 3]
    card_fees = {'VISA': 0.02, 'MASTERCARD': 0.03, 'AMEX': 0.035}
    account_limits = {1: 1000, 2: 1500, 3: 2000}
    
    # CRITICAL: Validate array lengths before loops
    assert len(card_types) == len(card_fees), "Card types and fees length mismatch"
    assert len(customers) == len(account_limits), "Customers and account limits length mismatch"
    
    # 2. VARIABLES
    # Decision variables: x[i,j] = transaction amount for customer i on card type j
    x = model.addVars(customers, card_types, vtype=GRB.CONTINUOUS, name="x", lb=0)
    
    # 3. OBJECTIVE FUNCTION
    # Minimize total transaction fees
    model.setObjective(
        gp.quicksum(card_fees[j] * x[i, j] for i in customers for j in card_types),
        GRB.MINIMIZE
    )
    
    # 4. CONSTRAINTS
    # Ensure total transaction amount per customer does not exceed account limit
    for i in customers:
        model.addConstr(
            gp.quicksum(x[i, j] for j in card_types) <= account_limits[i],
            name=f"account_limit_{i}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in customers:
            for j in card_types:
                if x[i, j].x > 1e-6:
                    print(f"x[{i},{j}] = {x[i, j].x:.3f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
if __name__ == "__main__":
    optimize_transaction_fees()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 0.0
**Execution Time**: 0.18 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution with zero transaction fees, indicating no transactions were allocated. This is likely due to the objective of minimizing fees, which is achieved by not processing any transactions. The execution time was the fastest among the solvers.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCPLEX Implementation for Customer Card Transactions Optimization
"""

from docplex.mp.model import Model

def optimize_transaction_fees():
    """Optimize transaction fees by allocating amounts across card types."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="customers_card_transactions")
    
    # Data from the problem
    card_types = ['VISA', 'MASTERCARD', 'AMEX']
    card_fees = {'VISA': 0.02, 'MASTERCARD': 0.03, 'AMEX': 0.035}
    customers = [1, 2, 3]
    account_limits = {1: 1000, 2: 1500, 3: 2000}
    
    # Validate array lengths
    assert len(card_types) == len(card_fees), "Card types and fees mismatch"
    assert len(customers) == len(account_limits), "Customers and limits mismatch"
    
    # 2. VARIABLES
    # Decision variables: x[i][j] = transaction amount for customer i on card j
    x = {(i, j): mdl.continuous_var(name=f"x_{i}_{j}", lb=0) 
         for i in customers for j in card_types}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize total transaction fees
    total_fees = mdl.sum(card_fees[j] * x[i, j] for i in customers for j in card_types)
    mdl.minimize(total_fees)
    
    # 4. CONSTRAINTS
    # Ensure total transaction amount per customer does not exceed account limit
    for i in customers:
        total_transaction = mdl.sum(x[i, j] for j in card_types)
        mdl.add_constraint(total_transaction <= account_limits[i], ctname=f"limit_{i}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in customers:
            for j in card_types:
                value = solution.get_value(x[i, j])
                if value > 1e-6:
                    print(f"Customer {i}, Card {j}: {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
if __name__ == "__main__":
    optimize_transaction_fees()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 0.0
**Execution Time**: 1.19 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution with zero transaction fees, consistent with Gurobipy. The execution time was slower than Gurobipy but still reasonable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def optimize_transaction_fees():
    """Optimize transaction fees by allocating transactions across card types."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    customers = [1, 2, 3]
    card_types = ['VISA', 'MASTERCARD', 'AMEX']
    
    # Fees for each card type
    card_fees = {
        'VISA': 0.02,
        'MASTERCARD': 0.03,
        'AMEX': 0.035
    }
    
    # Account limits for each customer
    account_limits = {
        1: 1000,
        2: 1500,
        3: 2000
    }
    
    # CRITICAL: Validate array lengths before indexing
    assert len(customers) == len(account_limits), "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.Customers = pyo.Set(initialize=customers)
    model.CardTypes = pyo.Set(initialize=card_types)
    
    # 4. PARAMETERS (data containers)
    model.CardFees = pyo.Param(model.CardTypes, initialize=card_fees)
    model.AccountLimits = pyo.Param(model.Customers, initialize=account_limits)
    
    # 5. VARIABLES
    # Transaction amounts allocated to each card type for each customer
    model.TransactionAmounts = pyo.Var(model.Customers, model.CardTypes, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    # Minimize the total transaction fees
    def obj_rule(model):
        return sum(model.CardFees[card] * model.TransactionAmounts[cust, card] 
                   for cust in model.Customers for card in model.CardTypes)
    model.Objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    # Ensure the total transaction amount for each customer does not exceed their account limit
    def account_limit_rule(model, cust):
        return sum(model.TransactionAmounts[cust, card] for card in model.CardTypes) <= model.AccountLimits[cust]
    model.AccountLimitConstraint = pyo.Constraint(model.Customers, rule=account_limit_rule)
    
    # 8. SOLVING WITH GUROBI (your available solver)
    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)  # tee=True shows solver output
    
    # 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)}")
        
        # Extract variable values
        print("\nTransaction Amounts:")
        for cust in model.Customers:
            for card in model.CardTypes:
                amount = pyo.value(model.TransactionAmounts[cust, card])
                if amount > 1e-6:  # Only print non-zero values
                    print(f"Customer {cust}, Card {card}: {amount:.3f}")
        
    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
if __name__ == "__main__":
    optimize_transaction_fees()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 0.0
**Execution Time**: 0.94 seconds
**Reliability**: high
**Analysis**: Pyomo confirmed the same optimal solution with zero transaction fees. The execution time was slower than Gurobipy but faster than DOCplex.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 0.00 | 0.18s | N/A | N/A |
| Docplex | OPTIMAL | 0.00 | 1.19s | N/A | N/A |
| Pyomo | OPTIMAL | 0.00 | 0.94s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 0.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its faster execution time and high reliability. All solvers agree on the optimal solution, so the choice is based on performance.

### Optimal Decision Variables
- **x_1_VISA** = 0.000
  - *Business Meaning*: Transaction amount allocated to VISA for Customer 1
- **x_1_MASTERCARD** = 0.000
  - *Business Meaning*: Transaction amount allocated to MASTERCARD for Customer 1
- **x_1_AMEX** = 0.000
  - *Business Meaning*: Transaction amount allocated to AMEX for Customer 1
- **x_2_VISA** = 0.000
  - *Business Meaning*: Transaction amount allocated to VISA for Customer 2
- **x_2_MASTERCARD** = 0.000
  - *Business Meaning*: Transaction amount allocated to MASTERCARD for Customer 2
- **x_2_AMEX** = 0.000
  - *Business Meaning*: Transaction amount allocated to AMEX for Customer 2
- **x_3_VISA** = 0.000
  - *Business Meaning*: Transaction amount allocated to VISA for Customer 3
- **x_3_MASTERCARD** = 0.000
  - *Business Meaning*: Transaction amount allocated to MASTERCARD for Customer 3
- **x_3_AMEX** = 0.000
  - *Business Meaning*: Transaction amount allocated to AMEX for Customer 3

### Business Interpretation
**Overall Strategy**: The optimal solution suggests that no transactions should be processed to minimize transaction fees. This is a theoretical result and may not be practical for business operations.
**Objective Value Meaning**: The optimal objective value of 0.0 indicates that the minimum transaction fees are achieved by not processing any transactions.
**Resource Allocation Summary**: No transactions should be processed to minimize fees. This is a theoretical result and may require revisiting the model constraints or objective function for practical implementation.
**Implementation Recommendations**: Review the model to ensure it aligns with business goals. Consider adding constraints to enforce minimum transaction amounts or revising the objective function to balance fees and transaction volume.