# Complete Optimization Problem and Solution: city_record

## 1. Problem Context and Goals

### Context  
The sports organization is tasked with strategically allocating matches to various cities to maximize the economic impact, specifically focusing on the GDP contribution of each city. The decision-making process involves determining whether a match is allocated to a city, represented by a binary decision variable. The primary objective is to maximize the total GDP impact, which is calculated by summing the GDP contributions of cities where matches are allocated. This allocation must consider the temperature suitability for each city during the month of the match, ensuring that matches are held in cities with temperatures within a specified range. The temperature range suitable for holding matches is defined as between 15 and 27 degrees Celsius, which is optimal for outdoor sports events. The GDP contribution per match is calculated based on the city's GDP and the importance of the match, ensuring that the allocation decisions align with the organization's economic goals.

### Goals  
The primary goal of this optimization problem is to maximize the total GDP impact from the allocation of matches to cities. This involves selecting cities for match allocation in a way that maximizes the sum of GDP contributions from these cities. The success of this optimization is measured by the total GDP impact achieved, ensuring that the allocation decisions are economically beneficial and align with the organization's strategic objectives.

## 2. Constraints    

The allocation of matches to cities is subject to several constraints to ensure feasibility and alignment with business requirements. Each match can be allocated to at most one city, ensuring that no match is scheduled in multiple locations simultaneously. Additionally, the temperature in the selected city must fall within the specified range of 15 to 27 degrees Celsius during the month of the match. This ensures that the matches are held in suitable weather conditions, maintaining the comfort and safety of participants and spectators.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables, modifying existing tables to include temperature suitability ranges, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE city (
  city_id INTEGER,
  GDP FLOAT
);

CREATE TABLE temperature (
  city_id INTEGER,
  month STRING,
  min_temp FLOAT,
  max_temp FLOAT
);

CREATE TABLE match_allocation (
  match_id INTEGER,
  city_id INTEGER,
  is_allocated BOOLEAN
);
```

### Data Dictionary  
The database schema is designed to support the optimization problem by storing relevant data in a structured format. The `city` table contains information about each city, including a unique identifier and the GDP contribution per match, which serves as a coefficient in the objective function. The `temperature` table records monthly temperature data for each city, providing the bounds for temperature constraints. The `match_allocation` table captures the decision variables, indicating whether a match is allocated to a particular city. Each table and column is mapped to its business purpose and optimization role, ensuring a clear connection to the linear mathematical formulation.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical GDP contributions of cities and realistic temperature ranges for sports events, ensuring a diverse set of cities and conditions.

-- Realistic data for city
INSERT INTO city (city_id, GDP) VALUES (1, 1500.0);
INSERT INTO city (city_id, GDP) VALUES (2, 2500.0);
INSERT INTO city (city_id, GDP) VALUES (3, 1800.0);

-- Realistic data for temperature
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (1, 'Jan', 16.0, 24.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (2, 'Jan', 18.0, 26.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (3, 'Jan', 15.0, 23.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (1, 'Feb', 17.0, 25.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (2, 'Feb', 19.0, 27.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (3, 'Feb', 16.0, 24.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (1, 'Mar', 18.0, 26.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (2, 'Mar', 20.0, 28.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (3, 'Mar', 17.0, 25.0);

-- Realistic data for match_allocation
INSERT INTO match_allocation (match_id, city_id, is_allocated) VALUES (1, 1, True);
INSERT INTO match_allocation (match_id, city_id, is_allocated) VALUES (2, 2, True);
INSERT INTO match_allocation (match_id, city_id, is_allocated) VALUES (3, 3, False);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{m,c} \) be a binary decision variable that equals 1 if match \( m \) is allocated to city \( c \), and 0 otherwise.

#### Objective Function
Maximize the total GDP impact from the allocation of matches to cities:
\[
\text{Maximize } \sum_{m \in \text{matches}} \sum_{c \in \text{cities}} \text{GDP}_c \times x_{m,c}
\]
where \(\text{GDP}_c\) is the GDP contribution of city \( c \).

#### Constraints
1. **Each match is allocated to at most one city:**
   \[
   \sum_{c \in \text{cities}} x_{m,c} \leq 1 \quad \forall m \in \text{matches}
   \]

2. **Temperature suitability constraint:**
   \[
   x_{m,c} = 0 \quad \text{if } \text{min\_temp}_{c,m} < 15 \text{ or } \text{max\_temp}_{c,m} > 27 \quad \forall m \in \text{matches}, \forall c \in \text{cities}
   \]
   This constraint ensures that matches are only allocated to cities where the temperature is within the suitable range.

3. **Binary constraint for decision variables:**
   \[
   x_{m,c} \in \{0, 1\} \quad \forall m \in \text{matches}, \forall c \in \text{cities}
   \]

#### Data Source Verification:
- \(\text{GDP}_c\) is sourced from the `city.GDP` column.
- Temperature constraints are derived from `temperature.min_temp` and `temperature.max_temp` columns for each city and month.
- The decision variable \( x_{m,c} \) is represented in the `match_allocation.is_allocated` column, indicating whether a match is allocated to a city.

This formulation provides a complete and solvable linear model for the given business problem, ensuring that all constraints and objectives are aligned with the data provided.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_match_allocation():
    """Optimize match allocation to maximize GDP impact."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("city_record")
    
    # Data: Cities, GDP, and Temperature suitability
    cities = [1, 2, 3]
    gdp = {1: 1500.0, 2: 2500.0, 3: 1800.0}
    temperature_suitability = {
        (1, 'Jan'): (16.0, 24.0),
        (2, 'Jan'): (18.0, 26.0),
        (3, 'Jan'): (15.0, 23.0),
        (1, 'Feb'): (17.0, 25.0),
        (2, 'Feb'): (19.0, 27.0),
        (3, 'Feb'): (16.0, 24.0),
        (1, 'Mar'): (18.0, 26.0),
        (2, 'Mar'): (20.0, 28.0),
        (3, 'Mar'): (17.0, 25.0)
    }
    
    matches = [1, 2, 3]
    months = ['Jan', 'Feb', 'Mar']
    
    # CRITICAL: Validate array lengths before loops
    assert len(cities) == len(gdp), "Array length mismatch for cities and GDP"
    
    # 2. VARIABLES
    x = model.addVars(matches, cities, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(gp.quicksum(gdp[c] * x[m, c] for m in matches for c in cities), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS - CORRECT SYNTAX PATTERNS
    
    # Each match is allocated to at most one city
    model.addConstrs((gp.quicksum(x[m, c] for c in cities) <= 1 for m in matches), name="match_allocation")
    
    # Temperature suitability constraint
    for m in matches:
        for c in cities:
            for month in months:
                min_temp, max_temp = temperature_suitability[(c, month)]
                if min_temp < 15 or max_temp > 27:
                    model.addConstr(x[m, c] == 0, name=f"temp_suitability_{m}_{c}_{month}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for m in matches:
            for c in cities:
                if x[m, c].x > 1e-6:
                    print(f"Match {m} allocated to City {c}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_match_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 5400.0
**Execution Time**: 0.63 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution with a consistent objective value of 5400.0. The execution time was the fastest among the solvers, indicating efficient performance.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def city_record_optimization():
    """Optimization for match allocation to maximize GDP impact"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="city_record")
    
    # Data: Cities, GDP, and Temperature suitability
    cities = [1, 2, 3]
    gdp = {1: 1500.0, 2: 2500.0, 3: 1800.0}
    temperature_suitability = {
        (1, 'Jan'): (16.0, 24.0),
        (2, 'Jan'): (18.0, 26.0),
        (3, 'Jan'): (15.0, 23.0),
        (1, 'Feb'): (17.0, 25.0),
        (2, 'Feb'): (19.0, 27.0),
        (3, 'Feb'): (16.0, 24.0),
        (1, 'Mar'): (18.0, 26.0),
        (2, 'Mar'): (20.0, 28.0),
        (3, 'Mar'): (17.0, 25.0)
    }
    
    matches = [1, 2, 3]
    months = ['Jan', 'Feb', 'Mar']
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(cities) == len(gdp), "Array length mismatch for cities and GDP"
    
    # 2. VARIABLES
    x = {(m, c): mdl.binary_var(name=f"x_{m}_{c}") for m in matches for c in cities}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(gdp[c] * x[m, c] for m in matches for c in cities)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Each match is allocated to at most one city
    for m in matches:
        mdl.add_constraint(mdl.sum(x[m, c] for c in cities) <= 1, ctname=f"match_{m}_allocation")
    
    # Temperature suitability constraint
    for m in matches:
        for c in cities:
            for month in months:
                min_temp, max_temp = temperature_suitability[(c, month)]
                if min_temp < 15 or max_temp > 27:
                    mdl.add_constraint(x[m, c] == 0, ctname=f"temp_suitability_{m}_{c}_{month}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for m in matches:
            for c in cities:
                value = solution.get_value(x[m, c])
                if value > 0.5:  # Since x is binary, check if it's 1
                    print(f"Match {m} is allocated to City {c}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
city_record_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 5400.0
**Execution Time**: 3.00 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution with the same objective value as Gurobipy, confirming the consistency of results. However, it took longer to execute.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def city_record_optimization():
    """Optimization for match allocation to maximize GDP impact"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    cities = [1, 2, 3]
    matches = [1, 2, 3]
    GDP = {1: 1500.0, 2: 2500.0, 3: 1800.0}
    temperature_data = {
        (1, 'Jan'): (16.0, 24.0),
        (2, 'Jan'): (18.0, 26.0),
        (3, 'Jan'): (15.0, 23.0),
        (1, 'Feb'): (17.0, 25.0),
        (2, 'Feb'): (19.0, 27.0),
        (3, 'Feb'): (16.0, 24.0),
        (1, 'Mar'): (18.0, 26.0),
        (2, 'Mar'): (20.0, 28.0),
        (3, 'Mar'): (17.0, 25.0)
    }
    month = 'Jan'  # Example month for allocation
    
    # CRITICAL: Validate data lengths before indexing
    assert len(cities) == len(GDP), "Mismatch in cities and GDP data length"
    
    # 3. SETS
    model.C = pyo.Set(initialize=cities)
    model.M = pyo.Set(initialize=matches)
    
    # 4. PARAMETERS
    model.GDP = pyo.Param(model.C, initialize=GDP)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.M, model.C, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.GDP[c] * model.x[m, c] for m in model.M for c in model.C)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Each match is allocated to at most one city
    def match_allocation_rule(model, m):
        return sum(model.x[m, c] for c in model.C) <= 1
    model.match_allocation_constraint = pyo.Constraint(model.M, rule=match_allocation_rule)
    
    # Temperature suitability constraint
    def temperature_suitability_rule(model, m, c):
        min_temp, max_temp = temperature_data[(c, month)]
        if min_temp < 15 or max_temp > 27:
            return model.x[m, c] == 0
        return pyo.Constraint.Skip
    model.temperature_suitability_constraint = pyo.Constraint(model.M, model.C, rule=temperature_suitability_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
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nMatch allocations:")
        for m in model.M:
            for c in model.C:
                if pyo.value(model.x[m, c]) > 0.5:  # Binary variable check
                    print(f"Match {m} allocated to City {c}")
        
    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
city_record_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 7500.0
**Execution Time**: 1.81 seconds
**Reliability**: medium
**Analysis**: Pyomo reported a higher optimal value of 7500.0, which is inconsistent with the other solvers. This discrepancy suggests potential issues with model formulation or data interpretation in Pyomo.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 5400.00 | 0.63s | N/A | N/A |
| Docplex | OPTIMAL | 5400.00 | 3.00s | N/A | N/A |
| Pyomo | OPTIMAL | 7500.00 | 1.81s | N/A | N/A |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Consistent Solvers**: gurobipy, docplex
**Inconsistent Solvers**: pyomo
**Potential Issues**:
- Pyomo may have a different interpretation of constraints or objective function.
- Data input discrepancies or model formulation errors specific to Pyomo.
**Majority Vote Optimal Value**: 5400.0

### Final Recommendation
**Recommended Optimal Value**: 5400.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy provided a consistent and optimal solution with the fastest execution time, indicating reliability and efficiency.

### Business Interpretation
**Overall Strategy**: The optimal allocation of matches to cities results in a GDP impact of 5400.0, ensuring matches are held in cities with suitable temperatures.
**Objective Value Meaning**: The optimal objective value of 5400.0 represents the maximum GDP impact achievable by allocating matches to cities within the temperature constraints.
**Resource Allocation Summary**: Matches should be allocated to cities that maximize GDP impact while adhering to temperature suitability constraints.
**Implementation Recommendations**: Verify data inputs and constraints in Pyomo, and use Gurobipy for final implementation due to its reliability and efficiency.