# Complete Optimization Problem and Solution: university_basketball

## 1. Problem Context and Goals

### Context  
The business problem revolves around selecting basketball teams for a tournament to maximize the overall win percentage of the selected teams. The decision-making process involves choosing which teams to include in the tournament based on their performance, conference affiliation, and geographical location. Each team is represented by a unique identifier, and the decision to select a team is binary—either the team is selected or not.  

The operational parameters guiding this decision include:  
- **Win Percentage**: The win percentage of each team, which serves as the primary metric for evaluating team performance.  
- **Total Teams Selected**: The total number of teams to be selected for the tournament, ensuring a manageable and competitive tournament size.  
- **Conference Diversity**: Minimum requirements for the number of teams selected from the East, West, and South conferences to ensure fair representation across conferences.  
- **Geographical Distribution**: Maximum limits on the number of teams selected from specific locations (New York, Los Angeles, and Chicago) to promote geographical diversity.  

These parameters are derived from the business configuration, which includes scalar values such as the total number of teams to be selected, minimum team requirements per conference, and maximum team limits per location. The decision-making process is designed to be linear, avoiding complex relationships such as variable products or divisions, ensuring a straightforward and efficient optimization formulation.  

### Goals  
The primary goal of this optimization problem is to maximize the total win percentage of the selected teams for the tournament. Success is measured by the cumulative win percentage of the chosen teams, ensuring that the tournament features the highest-performing teams while adhering to the constraints on conference diversity and geographical distribution.  

The optimization process focuses on selecting the best combination of teams based on their win percentages, ensuring that the final selection aligns with the business requirements for team diversity and location limits. This goal is achieved through a linear formulation that directly maps the win percentages of teams to the decision variables, ensuring clarity and simplicity in the optimization process.  

## 2. Constraints  

The optimization problem is subject to the following constraints, which ensure that the selected teams meet the business requirements for diversity and distribution:  

1. **Total Teams Selected**: The total number of teams selected for the tournament must equal the predefined value specified in the business configuration.  
2. **East Conference Diversity**: The number of teams selected from the East conference must meet or exceed the minimum requirement set in the business configuration.  
3. **West Conference Diversity**: The number of teams selected from the West conference must meet or exceed the minimum requirement set in the business configuration.  
4. **South Conference Diversity**: The number of teams selected from the South conference must meet or exceed the minimum requirement set in the business configuration.  
5. **New York Geographical Limit**: The number of teams selected from New York must not exceed the maximum limit specified in the business configuration.  
6. **Los Angeles Geographical Limit**: The number of teams selected from Los Angeles must not exceed the maximum limit specified in the business configuration.  
7. **Chicago Geographical Limit**: The number of teams selected from Chicago must not exceed the maximum limit specified in the business configuration.  

These constraints are designed to ensure that the final selection of teams is balanced and representative, aligning with the business goals for diversity and geographical distribution.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Added missing constraint bounds to business configuration logic and updated data dictionary to reflect these changes. No new tables were created as the missing requirements were better suited for configuration logic.

CREATE TABLE win_percentage (
  Team_ID INTEGER,
  Win_Percent FLOAT
);

CREATE TABLE team_selection (
  Team_ID INTEGER,
  Team_Selection BOOLEAN
);

CREATE TABLE conference_indicator (
  Team_ID INTEGER,
  Conference_Indicator STRING
);

CREATE TABLE location_indicator (
  Team_ID INTEGER,
  Location_Indicator STRING
);
```

### Data Dictionary  
The following tables and columns are used in the optimization problem, with their business purposes and optimization roles clearly defined:  

- **win_percentage**:  
  - **Team_ID**: Unique identifier for each team.  
  - **Win_Percent**: The win percentage of the team, used as the coefficient in the objective function to maximize the total win percentage of selected teams.  

- **team_selection**:  
  - **Team_ID**: Unique identifier for each team.  
  - **Team_Selection**: Binary indicator of whether a team is selected for the tournament, serving as the decision variable in the optimization model.  

- **conference_indicator**:  
  - **Team_ID**: Unique identifier for each team.  
  - **Conference_Indicator**: The conference affiliation of the team, used to enforce constraints on conference diversity.  

- **location_indicator**:  
  - **Team_ID**: Unique identifier for each team.  
  - **Location_Indicator**: The location of the team, used to enforce constraints on geographical distribution.  

### Current Stored Values  
```sql
-- Iteration 3 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic basketball tournament scenarios, considering team performance, conference distribution, and geographical constraints. Data was generated to ensure diversity and competitiveness while respecting business configuration parameters.

-- Realistic data for win_percentage
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (1, 85.0);
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (2, 78.5);
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (3, 72.0);
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (4, 80.5);
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (5, 75.0);

-- Realistic data for team_selection
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (1, False);
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (2, False);
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (3, False);
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (4, False);
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (5, False);

-- Realistic data for conference_indicator
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (1, 'East');
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (2, 'West');
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (3, 'South');
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (4, 'East');
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (5, 'West');

-- Realistic data for location_indicator
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (1, 'New York');
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (2, 'Los Angeles');
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (3, 'Chicago');
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (4, 'New York');
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (5, 'Los Angeles');
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_i \): Binary decision variable indicating whether team \( i \) is selected for the tournament.  
  \( x_i \in \{0, 1\} \) for all \( i \in \text{Team\_ID} \).

#### Objective Function
Maximize the total win percentage of the selected teams:  
\[
\text{Maximize} \quad \sum_{i \in \text{Team\_ID}} \text{Win\_Percent}_i \cdot x_i
\]

#### Constraints
1. **Total Teams Selected**:  
   \[
   \sum_{i \in \text{Team\_ID}} x_i = \text{Total\_Teams}
   \]
2. **East Conference Diversity**:  
   \[
   \sum_{i \in \text{Team\_ID}, \text{Conference\_Indicator}_i = \text{East}} x_i \geq \text{Min\_East}
   \]
3. **West Conference Diversity**:  
   \[
   \sum_{i \in \text{Team\_ID}, \text{Conference\_Indicator}_i = \text{West}} x_i \geq \text{Min\_West}
   \]
4. **South Conference Diversity**:  
   \[
   \sum_{i \in \text{Team\_ID}, \text{Conference\_Indicator}_i = \text{South}} x_i \geq \text{Min\_South}
   \]
5. **New York Geographical Limit**:  
   \[
   \sum_{i \in \text{Team\_ID}, \text{Location\_Indicator}_i = \text{New York}} x_i \leq \text{Max\_NY}
   \]
6. **Los Angeles Geographical Limit**:  
   \[
   \sum_{i \in \text{Team\_ID}, \text{Location\_Indicator}_i = \text{Los Angeles}} x_i \leq \text{Max\_LA}
   \]
7. **Chicago Geographical Limit**:  
   \[
   \sum_{i \in \text{Team\_ID}, \text{Location\_Indicator}_i = \text{Chicago}} x_i \leq \text{Max\_Chicago}
   \]

#### Data Source Verification
- **Win\_Percent\(_i\)**: Coefficient in the objective function comes from `win_percentage.Win_Percent`.
- **Total\_Teams**: Constant in the total teams constraint comes from the business configuration.
- **Min\_East**, **Min\_West**, **Min\_South**: Constants in the conference diversity constraints come from the business configuration.
- **Max\_NY**, **Max\_LA**, **Max\_Chicago**: Constants in the geographical limit constraints come from the business configuration.
- **Conference\_Indicator\(_i\)**: Used in conference diversity constraints, comes from `conference_indicator.Conference_Indicator`.
- **Location\_Indicator\(_i\)**: Used in geographical limit constraints, comes from `location_indicator.Location_Indicator`.

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

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for University Basketball Team Selection
"""

import gurobipy as gp
from gurobipy import GRB

def optimize_basketball_teams():
    """Optimize the selection of basketball teams for a tournament."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("university_basketball")
    
    # Data from the database
    team_ids = [1, 2, 3, 4, 5]
    win_percent = {1: 85.0, 2: 78.5, 3: 72.0, 4: 80.5, 5: 75.0}
    conference_indicator = {1: 'East', 2: 'West', 3: 'South', 4: 'East', 5: 'West'}
    location_indicator = {1: 'New York', 2: 'Los Angeles', 3: 'Chicago', 4: 'New York', 5: 'Los Angeles'}
    
    # Business configuration
    total_teams = 3
    min_east = 1
    min_west = 1
    min_south = 1
    max_ny = 1
    max_la = 1
    max_chicago = 1
    
    # CRITICAL: Validate array lengths before loops
    assert len(team_ids) == len(win_percent) == len(conference_indicator) == len(location_indicator), "Array length mismatch"
    
    # 2. VARIABLES
    x = {i: model.addVar(vtype=GRB.BINARY, name=f"x_{i}") for i in team_ids}
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(gp.quicksum(win_percent[i] * x[i] for i in team_ids), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Total Teams Selected
    model.addConstr(gp.quicksum(x[i] for i in team_ids) == total_teams, name="total_teams")
    
    # East Conference Diversity
    model.addConstr(gp.quicksum(x[i] for i in team_ids if conference_indicator[i] == 'East') >= min_east, name="min_east")
    
    # West Conference Diversity
    model.addConstr(gp.quicksum(x[i] for i in team_ids if conference_indicator[i] == 'West') >= min_west, name="min_west")
    
    # South Conference Diversity
    model.addConstr(gp.quicksum(x[i] for i in team_ids if conference_indicator[i] == 'South') >= min_south, name="min_south")
    
    # New York Geographical Limit
    model.addConstr(gp.quicksum(x[i] for i in team_ids if location_indicator[i] == 'New York') <= max_ny, name="max_ny")
    
    # Los Angeles Geographical Limit
    model.addConstr(gp.quicksum(x[i] for i in team_ids if location_indicator[i] == 'Los Angeles') <= max_la, name="max_la")
    
    # Chicago Geographical Limit
    model.addConstr(gp.quicksum(x[i] for i in team_ids if location_indicator[i] == 'Chicago') <= max_chicago, name="max_chicago")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in team_ids:
            if x[i].x > 1e-6:
                print(f"Team {i} selected: {x[i].x:.0f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
optimize_basketball_teams()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 235.5
**Execution Time**: 0.18 seconds
**Reliability**: high
**Analysis**: Gurobipy found the optimal solution quickly with an execution time of 0.183 seconds, indicating high efficiency and reliability.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCplex implementation for university_basketball optimization problem
"""

from docplex.mp.model import Model

def optimize_basketball_selection():
    """Optimize basketball team selection for tournament"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="university_basketball")
    
    # Business configuration parameters
    Total_Teams = 3
    Min_East = 1
    Min_West = 1
    Min_South = 1
    Max_NY = 1
    Max_LA = 1
    Max_Chicago = 1
    
    # Data from database
    Team_ID = [1, 2, 3, 4, 5]
    Win_Percent = [85.0, 78.5, 72.0, 80.5, 75.0]
    Conference_Indicator = ['East', 'West', 'South', 'East', 'West']
    Location_Indicator = ['New York', 'Los Angeles', 'Chicago', 'New York', 'Los Angeles']
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(Team_ID) == len(Win_Percent) == len(Conference_Indicator) == len(Location_Indicator), "Array length mismatch"
    safe_range = range(min(len(Team_ID), len(Win_Percent), len(Conference_Indicator), len(Location_Indicator)))  # Safe indexing
    
    # 2. VARIABLES
    x = {i: mdl.binary_var(name=f"x_{Team_ID[i]}") for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(Win_Percent[i] * x[i] for i in safe_range)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Total Teams Selected
    mdl.add_constraint(mdl.sum(x[i] for i in safe_range) == Total_Teams, ctname="total_teams")
    
    # Conference Diversity
    mdl.add_constraint(mdl.sum(x[i] for i in safe_range if Conference_Indicator[i] == 'East') >= Min_East, ctname="min_east")
    mdl.add_constraint(mdl.sum(x[i] for i in safe_range if Conference_Indicator[i] == 'West') >= Min_West, ctname="min_west")
    mdl.add_constraint(mdl.sum(x[i] for i in safe_range if Conference_Indicator[i] == 'South') >= Min_South, ctname="min_south")
    
    # Geographical Limits
    mdl.add_constraint(mdl.sum(x[i] for i in safe_range if Location_Indicator[i] == 'New York') <= Max_NY, ctname="max_ny")
    mdl.add_constraint(mdl.sum(x[i] for i in safe_range if Location_Indicator[i] == 'Los Angeles') <= Max_LA, ctname="max_la")
    mdl.add_constraint(mdl.sum(x[i] for i in safe_range if Location_Indicator[i] == 'Chicago') <= Max_Chicago, ctname="max_chicago")
    
    # 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"Team {Team_ID[i]} selected: {value:.0f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

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

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 235.5
**Execution Time**: 1.02 seconds
**Reliability**: medium
**Analysis**: DOCplex also found the optimal solution but took significantly longer (1.023 seconds) compared to Gurobipy, suggesting lower efficiency.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def optimize_basketball_selection():
    """Optimize basketball team selection for the tournament."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Example data (replace with actual data from database)
    team_ids = [1, 2, 3, 4, 5]
    win_percents = {1: 85.0, 2: 78.5, 3: 72.0, 4: 80.5, 5: 75.0}
    conference_indicators = {1: 'East', 2: 'West', 3: 'South', 4: 'East', 5: 'West'}
    location_indicators = {1: 'New York', 2: 'Los Angeles', 3: 'Chicago', 4: 'New York', 5: 'Los Angeles'}
    
    # Business configuration
    total_teams = 3
    min_east = 1
    min_west = 1
    min_south = 1
    max_ny = 1
    max_la = 1
    max_chicago = 1
    
    # CRITICAL: Validate array lengths before indexing
    assert len(team_ids) == len(win_percents) == len(conference_indicators) == len(location_indicators), "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.I = pyo.Set(initialize=team_ids)
    
    # 4. PARAMETERS (data containers)
    model.win_percent = pyo.Param(model.I, initialize=win_percents)
    model.conference_indicator = pyo.Param(model.I, initialize=conference_indicators)
    model.location_indicator = pyo.Param(model.I, initialize=location_indicators)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.win_percent[i] * model.x[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    # Total Teams Selected
    def total_teams_rule(model):
        return sum(model.x[i] for i in model.I) == total_teams
    model.total_teams_constraint = pyo.Constraint(rule=total_teams_rule)
    
    # East Conference Diversity
    def east_conference_rule(model):
        return sum(model.x[i] for i in model.I if model.conference_indicator[i] == 'East') >= min_east
    model.east_conference_constraint = pyo.Constraint(rule=east_conference_rule)
    
    # West Conference Diversity
    def west_conference_rule(model):
        return sum(model.x[i] for i in model.I if model.conference_indicator[i] == 'West') >= min_west
    model.west_conference_constraint = pyo.Constraint(rule=west_conference_rule)
    
    # South Conference Diversity
    def south_conference_rule(model):
        return sum(model.x[i] for i in model.I if model.conference_indicator[i] == 'South') >= min_south
    model.south_conference_constraint = pyo.Constraint(rule=south_conference_rule)
    
    # New York Geographical Limit
    def ny_limit_rule(model):
        return sum(model.x[i] for i in model.I if model.location_indicator[i] == 'New York') <= max_ny
    model.ny_limit_constraint = pyo.Constraint(rule=ny_limit_rule)
    
    # Los Angeles Geographical Limit
    def la_limit_rule(model):
        return sum(model.x[i] for i in model.I if model.location_indicator[i] == 'Los Angeles') <= max_la
    model.la_limit_constraint = pyo.Constraint(rule=la_limit_rule)
    
    # Chicago Geographical Limit
    def chicago_limit_rule(model):
        return sum(model.x[i] for i in model.I if model.location_indicator[i] == 'Chicago') <= max_chicago
    model.chicago_limit_constraint = pyo.Constraint(rule=chicago_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)  # 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("\nSelected Teams:")
        for i in model.I:
            if pyo.value(model.x[i]) > 0.5:  # Only print selected teams
                print(f"Team {i} selected with win percentage {model.win_percent[i]}")
        
    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
if __name__ == "__main__":
    optimize_basketball_selection()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 235.5
**Execution Time**: 1.01 seconds
**Reliability**: medium
**Analysis**: Pyomo found the optimal solution with an execution time of 1.006 seconds, similar to DOCplex, indicating comparable efficiency.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 235.50 | 0.18s | N/A | N/A |
| Docplex | OPTIMAL | 235.50 | 1.02s | N/A | N/A |
| Pyomo | OPTIMAL | 235.50 | 1.01s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 235.5
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its significantly faster execution time while achieving the same optimal value as the other solvers.

### Business Interpretation
**Overall Strategy**: The optimal solution maximizes the total win percentage of the selected teams while satisfying all constraints, ensuring a balanced and competitive tournament.
**Objective Value Meaning**: The optimal value of 235.5 represents the maximum achievable total win percentage of the selected teams, ensuring a high-performing tournament lineup.
**Resource Allocation Summary**: Teams should be selected based on their win percentages while ensuring diversity across conferences and geographical limits.
**Implementation Recommendations**: 1. Use the Gurobipy solver for efficient and reliable results. 2. Verify the selected teams meet all constraints. 3. Communicate the final lineup to stakeholders for tournament planning.