=== Problem Context ===
# 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.  


=== Schema ===
-- 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
);



=== LLM Response ===
```sql
-- Query Description: Retrieve all wrestlers with their days held, selection status, team, and eliminations.
-- 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).
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;

-- Query Description: Retrieve the total number of eliminations for each wrestler.
-- This query is useful for enforcing the total eliminations constraint, ensuring that the sum of eliminations by selected wrestlers does not exceed 30.
SELECT 
    wrestler_id,
    SUM(eliminations) AS total_eliminations
FROM 
    wrestler_eliminations
GROUP BY 
    wrestler_id;

-- Query Description: Retrieve the count of wrestlers in each team.
-- This query helps enforce the team balance constraint by ensuring an equal number of wrestlers are selected from Team A and Team B.
SELECT 
    team,
    COUNT(wrestler_id) AS wrestler_count
FROM 
    wrestler_team
GROUP BY 
    team;

-- Query Description: Retrieve the total days held by wrestlers in each team.
-- 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.
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;

-- Query Description: Retrieve wrestlers with the highest days held.
-- This query helps identify the top candidates for selection based on the primary optimization goal of maximizing total days held.
SELECT 
    w.wrestler_id,
    w.Days_held
FROM 
    wrestler w
ORDER BY 
    w.Days_held DESC;

-- Query Description: Retrieve wrestlers with the lowest eliminations.
-- This query helps identify wrestlers who contribute less to the total eliminations, which is useful for staying within the eliminations constraint.
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;

-- Query Description: Retrieve the total number of wrestlers.
-- This query is useful for understanding the overall pool of wrestlers available for selection, which is important for the total wrestlers constraint.
SELECT 
    COUNT(*) AS total_wrestlers
FROM 
    wrestler;

-- Query Description: Retrieve the total days held by all wrestlers.
-- This query provides a baseline for the maximum possible total days held, which is useful for understanding the potential optimization outcome.
SELECT 
    SUM(Days_held) AS total_days_held
FROM 
    wrestler;

-- Query Description: Retrieve wrestlers who are currently selected.
-- This query helps in understanding the current selection status of wrestlers, which is important for the decision-making process.
SELECT 
    w.wrestler_id,
    w.selection_status
FROM 
    wrestler w
WHERE 
    w.selection_status = TRUE;

-- Query Description: Retrieve wrestlers who are not currently selected.
-- This query helps in identifying wrestlers who are potential candidates for selection, which is important for the decision-making process.
SELECT 
    w.wrestler_id,
    w.selection_status
FROM 
    wrestler w
WHERE 
    w.selection_status = FALSE;
```

These queries are designed to retrieve the most relevant data for the optimization problem, including data for the objective function, decision variables, and constraints. They also provide aggregated and summary data that can inform the selection process and ensure that all constraints are met.
