=== Problem Context ===
# Complete Optimization Problem and Solution: party_host

## 1. Problem Context and Goals

### Context  
A party hosting company is tasked with efficiently assigning hosts to parties while minimizing the total cost of hosting. Each host has an associated cost, and the company must ensure that each party has a sufficient number of hosts, does not exceed the maximum allowed hosts, and that the hosts assigned have expertise that matches the party theme. The decision to assign a host to a party is binary, meaning a host is either assigned or not assigned to a specific party.  

The company operates with the following operational parameters:  
- **Cost per host**: Each host has a specific cost associated with their services, which directly impacts the total cost of hosting parties.  
- **Minimum hosts per party**: Each party requires a minimum number of hosts to ensure adequate service.  
- **Maximum hosts per party**: Each party has a limit on the number of hosts that can be assigned to avoid overstaffing.  
- **Expertise matching**: A host can only be assigned to a party if their expertise aligns with the party’s theme.  

These parameters are critical to the decision-making process and are used to define the objective and constraints of the optimization problem. The goal is to make these assignments in a way that minimizes total costs while adhering to the operational requirements.  

### Goals  
The primary goal of this optimization problem is to minimize the total cost of hosting parties by assigning hosts to parties in the most cost-effective manner. This is achieved by considering the cost associated with each host and ensuring that the assignments respect the minimum and maximum host requirements for each party, as well as the expertise matching criteria.  

Success is measured by the ability to reduce the total cost of hosting while maintaining operational feasibility. The optimization process ensures that the company’s resources are used efficiently, balancing cost savings with the need to meet party hosting requirements.  

## 2. Constraints  

The optimization problem must adhere to the following constraints:  
1. **Minimum hosts per party**: Each party must have at least the minimum number of hosts required to ensure adequate service. This ensures that parties are not understaffed.  
2. **Maximum hosts per party**: Each party cannot exceed the maximum number of hosts allowed. This prevents overstaffing and unnecessary costs.  
3. **Expertise matching**: A host can only be assigned to a party if their expertise matches the party’s theme. This ensures that the hosts assigned are qualified to meet the party’s needs.  

These constraints ensure that the assignments are operationally feasible and align with the company’s business requirements.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added decision variable table 'assign' to address missing optimization requirement. Updated business configuration logic to include scalar parameters and formulas. Ensured schema follows normalization principles and industry standards.

CREATE TABLE cost_per_host (
  Host_ID INTEGER,
  cost INTEGER
);

CREATE TABLE min_hosts (
  Party_ID INTEGER,
  min_hosts INTEGER
);

CREATE TABLE max_hosts (
  Party_ID INTEGER,
  max_hosts INTEGER
);

CREATE TABLE expertise_match (
  Party_ID INTEGER,
  Host_ID INTEGER,
  match BOOLEAN
);

CREATE TABLE assign (
  Party_ID INTEGER,
  Host_ID INTEGER,
  assign BOOLEAN
);
```

### Data Dictionary  
The following tables and columns are used in the optimization problem:  
- **cost_per_host**: Contains the cost associated with each host. This data is used to calculate the total cost of hosting parties.  
  - *Host_ID*: Unique identifier for each host.  
  - *cost*: The cost of the host, used as a coefficient in the objective function.  
- **min_hosts**: Specifies the minimum number of hosts required for each party. This data is used to set the lower bound for the number of hosts assigned to a party.  
  - *Party_ID*: Unique identifier for each party.  
  - *min_hosts*: The minimum number of hosts required for the party, used as a constraint bound.  
- **max_hosts**: Specifies the maximum number of hosts allowed for each party. This data is used to set the upper bound for the number of hosts assigned to a party.  
  - *Party_ID*: Unique identifier for each party.  
  - *max_hosts*: The maximum number of hosts allowed for the party, used as a constraint bound.  
- **expertise_match**: Indicates whether a host’s expertise matches a party’s theme. This data is used to ensure that only qualified hosts are assigned to parties.  
  - *Party_ID*: Unique identifier for each party.  
  - *Host_ID*: Unique identifier for each host.  
  - *match*: A binary indicator (true/false) that determines if the host’s expertise matches the party’s theme, used as a constraint bound.  
- **assign**: Represents the decision to assign a host to a party. This table is used to store the optimization results.  
  - *Party_ID*: Unique identifier for each party.  
  - *Host_ID*: Unique identifier for each host.  
  - *assign*: A binary decision variable (true/false) indicating whether the host is assigned to the party.  


=== Schema ===
-- Iteration 2 Database Schema
-- Objective: Added decision variable table 'assign' to address missing optimization requirement. Updated business configuration logic to include scalar parameters and formulas. Ensured schema follows normalization principles and industry standards.

CREATE TABLE cost_per_host (
  Host_ID INTEGER,
  cost INTEGER
);

CREATE TABLE min_hosts (
  Party_ID INTEGER,
  min_hosts INTEGER
);

CREATE TABLE max_hosts (
  Party_ID INTEGER,
  max_hosts INTEGER
);

CREATE TABLE expertise_match (
  Party_ID INTEGER,
  Host_ID INTEGER,
  match BOOLEAN
);

CREATE TABLE assign (
  Party_ID INTEGER,
  Host_ID INTEGER,
  assign BOOLEAN
);



=== LLM Response ===
```sql
-- Query Description: Retrieve the cost per host, which is essential for the objective function to minimize total cost.
-- This data is used as coefficients in the optimization model.
SELECT Host_ID, cost 
FROM cost_per_host;

-- Query Description: Retrieve the minimum number of hosts required for each party, which is a constraint in the optimization problem.
-- This data ensures that each party is not understaffed.
SELECT Party_ID, min_hosts 
FROM min_hosts;

-- Query Description: Retrieve the maximum number of hosts allowed for each party, which is another constraint in the optimization problem.
-- This data prevents overstaffing and unnecessary costs.
SELECT Party_ID, max_hosts 
FROM max_hosts;

-- Query Description: Retrieve the expertise matching information, which is a constraint in the optimization problem.
-- This data ensures that only qualified hosts are assigned to parties.
SELECT Party_ID, Host_ID, match 
FROM expertise_match 
WHERE match = TRUE;

-- Query Description: Retrieve the current assignments of hosts to parties, which represents the decision variables in the optimization problem.
-- This data is used to store and evaluate the optimization results.
SELECT Party_ID, Host_ID, assign 
FROM assign;

-- Query Description: Retrieve the total cost of current assignments, which is useful for evaluating the current state before optimization.
-- This data helps in understanding the baseline cost that needs to be minimized.
SELECT SUM(cph.cost) AS total_cost
FROM assign a
JOIN cost_per_host cph ON a.Host_ID = cph.Host_ID
WHERE a.assign = TRUE;

-- Query Description: Retrieve the number of hosts currently assigned to each party, which is useful for checking against the minimum and maximum host constraints.
-- This data helps in ensuring that the current assignments are feasible.
SELECT Party_ID, COUNT(Host_ID) AS num_hosts_assigned
FROM assign
WHERE assign = TRUE
GROUP BY Party_ID;

-- Query Description: Retrieve the list of hosts who can be assigned to each party based on expertise matching, which is useful for generating feasible assignments.
-- This data helps in filtering out hosts who do not meet the expertise requirements for each party.
SELECT em.Party_ID, em.Host_ID
FROM expertise_match em
WHERE em.match = TRUE;

-- Query Description: Retrieve the list of parties with their minimum and maximum host requirements, which is useful for setting up the constraints in the optimization model.
-- This data helps in ensuring that the assignments respect the operational requirements.
SELECT mh.Party_ID, mh.min_hosts, mx.max_hosts
FROM min_hosts mh
JOIN max_hosts mx ON mh.Party_ID = mx.Party_ID;

-- Query Description: Retrieve the list of hosts with their costs and expertise matches for each party, which is useful for generating the optimization model.
-- This data combines cost and expertise information, which are critical for the objective function and constraints.
SELECT cph.Host_ID, cph.cost, em.Party_ID, em.match
FROM cost_per_host cph
JOIN expertise_match em ON cph.Host_ID = em.Host_ID
WHERE em.match = TRUE;
```

These queries are designed to retrieve the most relevant data for the optimization problem, including the decision variables, objective function coefficients, and constraint parameters. They also provide aggregated and filtered data to ensure that the optimization model is both accurate and feasible.
