# Complete Optimization Problem and Solution: wrestler

## 1. Problem Context and Goals

### Context  
In the context of organizing a wrestling event, the goal is to select a team of wrestlers that maximizes the total number of days their reigns have been held, while ensuring a balanced and competitive match. The selection process involves making binary decisions for each wrestler—whether to include them in the event or not. The operational parameters guiding this decision include the number of days each wrestler has held their reign, the maximum number of wrestlers that can be selected, and the maximum number of eliminations allowed across all selected wrestlers.  

The business configuration sets a limit of six wrestlers that can be selected for the event, ensuring the match remains manageable and competitive. Additionally, the total number of eliminations by the selected wrestlers must not exceed thirty, maintaining a feasible upper limit for competitive dynamics. The selection must also ensure an equal number of wrestlers from Team A and Team B, promoting a balanced team composition.  

### Goals  
The primary optimization goal is to maximize the total number of days the selected wrestlers have held their reigns. This is achieved by selecting wrestlers whose combined days held contribute the most to the overall total, while adhering to the constraints on the number of wrestlers, eliminations, and team balance. Success is measured by the total days held by the selected wrestlers, ensuring the event features wrestlers with the most significant and impactful reigns.  

## 2. Constraints  

The selection process is subject to the following constraints:  
1. **Total Wrestlers Constraint**: The total number of wrestlers selected for the event must not exceed the maximum allowed limit of six. This ensures the event remains manageable and competitive.  
2. **Total Eliminations Constraint**: The total number of eliminations by the selected wrestlers must not exceed the maximum allowed limit of thirty. This maintains a feasible upper limit for competitive dynamics.  
3. **Team Balance Constraint**: The number of wrestlers selected from Team A must equal the number of wrestlers selected from Team B. This ensures a balanced and fair team composition for the event.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for team assignments and eliminations, modifying the wrestler table to include optimization-relevant data, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE wrestler (
  Days_held INTEGER,
  selection_status BOOLEAN
);

CREATE TABLE wrestler_team (
  wrestler_id INTEGER,
  team STRING
);

CREATE TABLE wrestler_eliminations (
  wrestler_id INTEGER,
  eliminations INTEGER
);
```

### Data Dictionary  
- **wrestler**: Stores wrestler information relevant to optimization.  
  - **Days_held**: The number of days the wrestler's reign has been held. This is used as the coefficient for maximizing the total days held.  
  - **selection_status**: Indicates whether the wrestler is selected for the event. This is the binary decision variable in the optimization process.  
- **wrestler_team**: Contains team assignments for each wrestler.  
  - **wrestler_id**: Unique identifier for the wrestler, linking them to their team.  
  - **team**: The team assignment (Team A or Team B), used to enforce team balance constraints.  
- **wrestler_eliminations**: Tracks the number of eliminations per wrestler.  
  - **wrestler_id**: Unique identifier for the wrestler, linking them to their elimination count.  
  - **eliminations**: The number of eliminations by the wrestler, used to enforce the total eliminations constraint.  


### Retrieved Values

**Query 1: This query is essential as it combines all necessary data for the optimization problem: days held (objective function), selection status (decision variable), team (for balance constraint), and eliminations (for eliminations constraint).**

```sql
SELECT w.Days_held, w.selection_status, wt.team, we.eliminations FROM wrestler w JOIN wrestler_team wt ON w.wrestler_id = wt.wrestler_id JOIN wrestler_eliminations we ON w.wrestler_id = we.wrestler_id;
```

**Error:** Execution failed on sql 'SELECT w.Days_held, w.selection_status, wt.team, we.eliminations FROM wrestler w JOIN wrestler_team wt ON w.wrestler_id = wt.wrestler_id JOIN wrestler_eliminations we ON w.wrestler_id = we.wrestler_id;': no such column: w.wrestler_id

**Query 2: This query is useful for enforcing the total eliminations constraint, ensuring that the sum of eliminations by selected wrestlers does not exceed 30.**

```sql
SELECT wrestler_id, SUM(eliminations) AS total_eliminations FROM wrestler_eliminations GROUP BY wrestler_id;
```

**Results (CSV format):**
```csv
wrestler_id,total_eliminations
1,8
2,12
3,5
```

**Query 3: This query helps enforce the team balance constraint by ensuring an equal number of wrestlers are selected from Team A and Team B.**

```sql
SELECT team, COUNT(wrestler_id) AS wrestler_count FROM wrestler_team GROUP BY team;
```

**Results (CSV format):**
```csv
team,wrestler_count
Team A,2
Team B,1
```

**Query 4: This query is useful for understanding the distribution of days held across teams, which can inform the selection process to maximize total days held while maintaining team balance.**

```sql
SELECT wt.team, SUM(w.Days_held) AS total_days_held FROM wrestler w JOIN wrestler_team wt ON w.wrestler_id = wt.wrestler_id GROUP BY wt.team;
```

**Error:** Execution failed on sql 'SELECT wt.team, SUM(w.Days_held) AS total_days_held FROM wrestler w JOIN wrestler_team wt ON w.wrestler_id = wt.wrestler_id GROUP BY wt.team;': no such column: w.wrestler_id

**Query 5: This query helps identify the top candidates for selection based on the primary optimization goal of maximizing total days held.**

```sql
SELECT w.wrestler_id, w.Days_held FROM wrestler w ORDER BY w.Days_held DESC;
```

**Error:** Execution failed on sql 'SELECT w.wrestler_id, w.Days_held FROM wrestler w ORDER BY w.Days_held DESC;': no such column: w.wrestler_id

**Query 6: This query helps identify wrestlers who contribute less to the total eliminations, which is useful for staying within the eliminations constraint.**

```sql
SELECT w.wrestler_id, we.eliminations FROM wrestler w JOIN wrestler_eliminations we ON w.wrestler_id = we.wrestler_id ORDER BY we.eliminations ASC;
```

**Error:** Execution failed on sql 'SELECT w.wrestler_id, we.eliminations FROM wrestler w JOIN wrestler_eliminations we ON w.wrestler_id = we.wrestler_id ORDER BY we.eliminations ASC;': no such column: w.wrestler_id

**Query 7: This query is useful for understanding the overall pool of wrestlers available for selection, which is important for the total wrestlers constraint.**

```sql
SELECT COUNT(*) AS total_wrestlers FROM wrestler;
```

**Results (CSV format):**
```csv
total_wrestlers
3
```

**Query 8: This query provides a baseline for the maximum possible total days held, which is useful for understanding the potential optimization outcome.**

```sql
SELECT SUM(Days_held) AS total_days_held FROM wrestler;
```

**Results (CSV format):**
```csv
total_days_held
500
```

**Query 9: This query helps in understanding the current selection status of wrestlers, which is important for the decision-making process.**

```sql
SELECT w.wrestler_id, w.selection_status FROM wrestler w WHERE w.selection_status = TRUE;
```

**Error:** Execution failed on sql 'SELECT w.wrestler_id, w.selection_status FROM wrestler w WHERE w.selection_status = TRUE;': no such column: w.wrestler_id

**Query 10: This query helps in identifying wrestlers who are potential candidates for selection, which is important for the decision-making process.**

```sql
SELECT w.wrestler_id, w.selection_status FROM wrestler w WHERE w.selection_status = FALSE;
```

**Error:** Execution failed on sql 'SELECT w.wrestler_id, w.selection_status FROM wrestler w WHERE w.selection_status = FALSE;': no such column: w.wrestler_id

