# Complete Optimization Problem and Solution: game_injury

## 1. Problem Context and Goals

### Context  
A sports league is focused on reducing the total number of injuries across all scheduled games while ensuring that stadiums operate within their capacity limits and maintain a minimum average attendance. The league must make decisions on which games to schedule, represented by binary decision variables, and manage the usage percentage of each stadium, represented by continuous decision variables. The injury risk for each game is calculated based on historical data, specifically the ratio of historical injuries to the total number of games played. This risk value is used as a coefficient in the optimization objective.  

The league operates under specific business configurations:  
- **Maximum capacity percentage for each stadium**: Each stadium has a predefined maximum capacity percentage, ensuring that usage does not exceed safe operational limits.  
- **Minimum average attendance required for each stadium**: To meet financial and operational goals, each stadium must maintain a minimum average attendance level.  

These configurations ensure that the optimization problem remains linear, avoiding complex nonlinear relationships such as variable products or divisions. The operational parameters and business logic are designed to align with a linear optimization formulation, focusing on minimizing injury risk while adhering to capacity and attendance constraints.

### Goals  
The primary goal of the optimization is to minimize the total injury risk across all scheduled games. This is achieved by summing the injury risk for each game multiplied by the decision to schedule that game. Success is measured by achieving the lowest possible total injury risk while ensuring that all stadiums operate within their capacity limits and meet the minimum average attendance requirements. The optimization process uses realistic business data and configurations to ensure that the results are both mathematically consistent and operationally feasible.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Stadium Capacity Constraint**: The total usage of each stadium, calculated as the sum of scheduled games multiplied by the stadium's capacity percentage, must not exceed the stadium's maximum capacity percentage. This ensures that stadiums operate within safe and manageable limits.  
2. **Minimum Attendance Constraint**: The total attendance across all scheduled games for each stadium must meet or exceed the minimum average attendance requirement. This ensures that financial and operational goals are met while maintaining fan engagement.  

These constraints are designed to align with linear mathematical forms, avoiding any nonlinear relationships such as variable products or divisions. They ensure that the optimization problem remains tractable and aligned with the league's operational requirements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for injury risk, game scheduling, and stadium usage. Configuration logic updates include scalar parameters for stadium capacity and minimum average attendance, and formulas for injury risk calculation.

CREATE TABLE injury_risk (
  game_id INTEGER,
  risk_value FLOAT
);

CREATE TABLE game_scheduling (
  game_id INTEGER,
  is_scheduled BOOLEAN
);

CREATE TABLE stadium_usage (
  stadium_id INTEGER,
  usage_percentage FLOAT
);

CREATE TABLE stadium (
  stadium_id INTEGER,
  capacity_percentage FLOAT,
  average_attendance INTEGER
);
```

### Data Dictionary  
- **Injury Risk Table**:  
  - **Purpose**: Stores the risk of injury for each game, calculated based on historical data.  
  - **Optimization Role**: Provides coefficients for the objective function, representing the injury risk for each game.  

- **Game Scheduling Table**:  
  - **Purpose**: Stores decisions on whether each game is scheduled.  
  - **Optimization Role**: Represents binary decision variables in the optimization problem.  

- **Stadium Usage Table**:  
  - **Purpose**: Tracks the percentage of capacity used for each stadium.  
  - **Optimization Role**: Represents continuous decision variables in the optimization problem.  

- **Stadium Table**:  
  - **Purpose**: Stores capacity and attendance data for each stadium.  
  - **Optimization Role**: Provides constraint bounds for stadium capacity and minimum average attendance.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic sports league scenarios, considering historical injury rates, stadium capacities, and attendance trends. Data was generated to ensure a balance between minimizing injury risk and meeting attendance and capacity constraints.

-- Realistic data for injury_risk
INSERT INTO injury_risk (game_id, risk_value) VALUES (1, 0.15);
INSERT INTO injury_risk (game_id, risk_value) VALUES (2, 0.1);
INSERT INTO injury_risk (game_id, risk_value) VALUES (3, 0.2);

-- Realistic data for game_scheduling
INSERT INTO game_scheduling (game_id, is_scheduled) VALUES (1, True);
INSERT INTO game_scheduling (game_id, is_scheduled) VALUES (2, False);
INSERT INTO game_scheduling (game_id, is_scheduled) VALUES (3, True);

-- Realistic data for stadium_usage
INSERT INTO stadium_usage (stadium_id, usage_percentage) VALUES (1, 0.75);
INSERT INTO stadium_usage (stadium_id, usage_percentage) VALUES (2, 0.8);
INSERT INTO stadium_usage (stadium_id, usage_percentage) VALUES (3, 0.7);

-- Realistic data for stadium
INSERT INTO stadium (stadium_id, capacity_percentage, average_attendance) VALUES (1, 0.85, 5500);
INSERT INTO stadium (stadium_id, capacity_percentage, average_attendance) VALUES (2, 0.9, 6000);
INSERT INTO stadium (stadium_id, capacity_percentage, average_attendance) VALUES (3, 0.8, 5000);
```