# Complete Optimization Problem and Solution: wta_1

## 1. Problem Context and Goals

### Context  
The business problem revolves around optimizing the scheduling of players across various tournaments to maximize the total ranking points earned. This involves making decisions about which players should participate in which tournaments, considering their availability, the maximum number of tournaments each player can participate in, and the maximum number of players allowed in each tournament. 

The decision to be made is whether a specific player participates in a specific tournament, represented as a binary choice. The operational parameters include the ranking points each player can earn in each tournament, which directly influence the objective of maximizing the total points. 

Business configuration parameters play a crucial role in defining the constraints. For instance, each player is limited to participating in a maximum of five tournaments to balance their schedule and performance. Additionally, each tournament has a cap on the total ranking points that can be earned, set at 2000 points, to reflect realistic point distributions.

### Goals  
The primary goal of this optimization is to maximize the total ranking points earned by players across all tournaments. This is achieved by strategically deciding which players participate in which tournaments, ensuring that the constraints on player participation and tournament capacity are respected. Success is measured by the total ranking points accumulated, which directly ties back to the operational data on ranking points earned by players in specific tournaments.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the decisions made are feasible and align with business rules:

1. **Player Participation Limit**: Each player can participate in a maximum of five tournaments. This constraint ensures that players are not over-scheduled, balancing their performance and well-being.

2. **Tournament Capacity Limit**: Each tournament has a maximum number of players that can participate. This constraint ensures that the tournaments do not exceed their capacity, maintaining the quality and manageability of the events.

3. **Player Availability**: A player can only participate in a tournament if they are available. This constraint ensures that the scheduling respects the availability status of each player, preventing unrealistic or impossible assignments.

These constraints are designed to be linear, ensuring that the optimization problem remains straightforward and solvable using linear or mixed-integer optimization techniques.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a new table for ranking points, modifying existing tables to align with OR expert's mapping, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE player_availability (
  player_id INTEGER,
  availability BOOLEAN
);

CREATE TABLE player_tournament_participation (
  player_id INTEGER,
  tournament_id INTEGER,
  participation BOOLEAN
);

CREATE TABLE matches (
  match_id INTEGER,
  tournament_id INTEGER,
  draw_size INTEGER,
  max_players_per_tournament INTEGER
);

CREATE TABLE player_tournament_ranking_points (
  player_id INTEGER,
  tournament_id INTEGER,
  ranking_points INTEGER
);
```

### Data Dictionary  
- **player_availability**: Tracks whether players are available to participate in tournaments. The `player_id` uniquely identifies each player, and the `availability` column indicates their participation status.
  
- **player_tournament_participation**: Represents the decision of whether a player participates in a specific tournament. The `player_id` and `tournament_id` link the participation to specific players and tournaments, while the `participation` column is the binary decision variable.

- **matches**: Contains details about matches in tournaments. The `tournament_id` links matches to specific tournaments, and the `max_players_per_tournament` column specifies the maximum number of players allowed in each tournament.

- **player_tournament_ranking_points**: Records the ranking points earned by players in specific tournaments. The `player_id` and `tournament_id` link the points to specific players and tournaments, and the `ranking_points` column provides the points earned, which are used as coefficients in the objective function.


### Retrieved Values

**Query 1: This data is crucial for maximizing the total ranking points earned.**

```sql
SELECT player_id, tournament_id, ranking_points FROM player_tournament_ranking_points;
```

**Results (CSV format):**
```csv
player_id,tournament_id,ranking_points
1,101,100
2,102,200
3,103,300
```

**Query 2: This ensures that only available players are considered for participation in tournaments.**

```sql
SELECT player_id, availability FROM player_availability WHERE availability = TRUE;
```

**Results (CSV format):**
```csv
player_id,availability
1,1
3,1
```

**Query 3: This ensures that the number of players assigned to each tournament does not exceed its capacity.**

```sql
SELECT tournament_id, max_players_per_tournament FROM matches;
```

**Results (CSV format):**
```csv
tournament_id,max_players_per_tournament
101,32
102,64
103,128
```

**Query 4: This helps ensure that no player is assigned to more than five tournaments.**

```sql
SELECT player_id, COUNT(tournament_id) AS num_participations FROM player_tournament_participation WHERE participation = TRUE GROUP BY player_id;
```

**Results (CSV format):**
```csv
player_id,num_participations
1,1
3,1
```

**Query 5: This provides a baseline for measuring the impact of optimization decisions.**

```sql
SELECT player_id, SUM(ranking_points) AS total_ranking_points FROM player_tournament_ranking_points GROUP BY player_id;
```

**Results (CSV format):**
```csv
player_id,total_ranking_points
1,100
2,200
3,300
```

**Query 6: This helps in balancing the distribution of ranking points across tournaments.**

```sql
SELECT tournament_id, SUM(ranking_points) AS total_ranking_points FROM player_tournament_ranking_points GROUP BY tournament_id;
```

**Results (CSV format):**
```csv
tournament_id,total_ranking_points
101,100
102,200
103,300
```

**Query 7: This combines availability and participation data to identify feasible assignments.**

```sql
SELECT pa.player_id, ptp.tournament_id, pa.availability, ptp.participation FROM player_availability pa JOIN player_tournament_participation ptp ON pa.player_id = ptp.player_id WHERE pa.availability = TRUE;
```

**Results (CSV format):**
```csv
player_id,tournament_id,availability,participation
1,101,1,1
3,103,1,1
```

**Query 8: This helps in ensuring that no tournament exceeds its player limit.**

```sql
SELECT m.tournament_id, m.max_players_per_tournament, COUNT(ptp.player_id) AS current_participants FROM matches m LEFT JOIN player_tournament_participation ptp ON m.tournament_id = ptp.tournament_id AND ptp.participation = TRUE GROUP BY m.tournament_id, m.max_players_per_tournament;
```

**Results (CSV format):**
```csv
tournament_id,max_players_per_tournament,current_participants
101,32,1
102,64,0
103,128,1
```

**Query 9: This ensures that players are not over-scheduled.**

```sql
SELECT player_id, COUNT(tournament_id) AS num_participations FROM player_tournament_participation WHERE participation = TRUE GROUP BY player_id ) AS participation_counts ON pa.player_id = participation_counts.player_id WHERE pa.availability = TRUE AND (participation_counts.num_participations IS NULL OR participation_counts.num_participations < 5);
```

**Error:** Execution failed on sql 'SELECT player_id, COUNT(tournament_id) AS num_participations FROM player_tournament_participation WHERE participation = TRUE GROUP BY player_id ) AS participation_counts ON pa.player_id = participation_counts.player_id WHERE pa.availability = TRUE AND (participation_counts.num_participations IS NULL OR participation_counts.num_participations < 5);': near ")": syntax error

