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

## 1. Problem Context and Goals

### Context  
A global theme park chain is focused on enhancing visitor satisfaction by strategically distributing roller coasters across its parks. The decision involves determining the number of roller coasters of each type to install in each park, considering visitor satisfaction scores, budget limits, available space, and maximum roller coaster capacity per park. Each roller coaster type has a fixed cost and space requirement, which are critical for ensuring that the installation plans are financially viable and physically feasible. The goal is to make operational decisions that maximize visitor satisfaction while adhering to these constraints.  

The business configuration includes the cost of each roller coaster type, set at $500,000, and the space required for each roller coaster type, set at 2,000 square units. These parameters are used to calculate budget and space constraints, ensuring that the optimization problem remains linear and aligned with real-world operational limitations.  

### Goals  
The primary goal of this optimization problem is to maximize the total visitor satisfaction across all parks. This is achieved by determining the optimal number of roller coasters of each type to install in each park, weighted by their respective visitor satisfaction scores. Success is measured by the overall increase in visitor satisfaction, which is directly influenced by the number and type of roller coasters installed. The optimization ensures that the decisions are made within the bounds of budget, space, and maximum roller coaster capacity for each park.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Budget Constraint**: The total cost of installing roller coasters in each park must not exceed the park’s allocated budget. The cost of each roller coaster type is fixed at $500,000, and the total cost is calculated by multiplying this cost by the number of roller coasters of each type installed in the park.  
2. **Space Constraint**: The total space occupied by roller coasters in each park must not exceed the park’s available space. Each roller coaster type requires 2,000 square units of space, and the total space is calculated by multiplying this requirement by the number of roller coasters of each type installed in the park.  
3. **Maximum Roller Coasters Constraint**: The total number of roller coasters installed in each park must not exceed the park’s maximum allowed capacity. This ensures that the park’s infrastructure and operational capabilities are not overwhelmed.  

These constraints ensure that the optimization problem remains linear and aligned with the operational realities of the theme parks.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for visitor satisfaction scores, budget limits, available space, and maximum roller coasters per park. Configuration logic updates include scalar parameters for cost and space requirements of roller coaster types.

CREATE TABLE visitor_satisfaction_scores (
  park_id INTEGER,
  coaster_type STRING,
  score FLOAT,
  num_coasters INTEGER
);

CREATE TABLE park_budgets (
  park_id INTEGER,
  budget INTEGER
);

CREATE TABLE park_available_space (
  park_id INTEGER,
  space INTEGER
);

CREATE TABLE park_max_coasters (
  park_id INTEGER,
  max_coasters INTEGER
);
```

### Data Dictionary  
- **visitor_satisfaction_scores**:  
  - **park_id**: Unique identifier for each park.  
  - **coaster_type**: Type of roller coaster (e.g., Wooden, Steel, Inverted).  
  - **score**: Visitor satisfaction score for the roller coaster type in the park.  
  - **num_coasters**: Number of roller coasters of this type in the park.  

- **park_budgets**:  
  - **park_id**: Unique identifier for each park.  
  - **budget**: Budget limit for the park.  

- **park_available_space**:  
  - **park_id**: Unique identifier for each park.  
  - **space**: Available space for roller coasters in the park.  

- **park_max_coasters**:  
  - **park_id**: Unique identifier for each park.  
  - **max_coasters**: Maximum number of roller coasters allowed in the park.  


=== Schema ===
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for visitor satisfaction scores, budget limits, available space, and maximum roller coasters per park. Configuration logic updates include scalar parameters for cost and space requirements of roller coaster types.

CREATE TABLE visitor_satisfaction_scores (
  park_id INTEGER,
  coaster_type STRING,
  score FLOAT,
  num_coasters INTEGER
);

CREATE TABLE park_budgets (
  park_id INTEGER,
  budget INTEGER
);

CREATE TABLE park_available_space (
  park_id INTEGER,
  space INTEGER
);

CREATE TABLE park_max_coasters (
  park_id INTEGER,
  max_coasters INTEGER
);



=== LLM Response ===
```sql
-- Query Description: Retrieve visitor satisfaction scores for each roller coaster type in each park.
-- This data is crucial for the objective function, as it determines the weight of each roller coaster type in maximizing visitor satisfaction.
SELECT park_id, coaster_type, score
FROM visitor_satisfaction_scores;

-- Query Description: Retrieve the budget limits for each park.
-- This data is essential for the budget constraint, ensuring that the total cost of roller coasters does not exceed the park's budget.
SELECT park_id, budget
FROM park_budgets;

-- Query Description: Retrieve the available space for each park.
-- This data is necessary for the space constraint, ensuring that the total space occupied by roller coasters does not exceed the park's available space.
SELECT park_id, space
FROM park_available_space;

-- Query Description: Retrieve the maximum number of roller coasters allowed in each park.
-- This data is important for the maximum roller coasters constraint, ensuring that the total number of roller coasters does not exceed the park's capacity.
SELECT park_id, max_coasters
FROM park_max_coasters;

-- Query Description: Retrieve the current number of roller coasters of each type in each park.
-- This data can be used to understand the current distribution and to ensure that the optimization does not violate existing constraints.
SELECT park_id, coaster_type, num_coasters
FROM visitor_satisfaction_scores;

-- Query Description: Retrieve aggregated visitor satisfaction scores per park.
-- This summary data can help in understanding the overall visitor satisfaction per park and can be used to prioritize parks with lower satisfaction scores.
SELECT park_id, SUM(score * num_coasters) AS total_satisfaction
FROM visitor_satisfaction_scores
GROUP BY park_id;

-- Query Description: Retrieve the total cost of current roller coasters per park.
-- This data is useful for understanding the current budget utilization and ensuring that the optimization does not exceed the budget.
SELECT park_id, SUM(num_coasters * 500000) AS total_cost
FROM visitor_satisfaction_scores
GROUP BY park_id;

-- Query Description: Retrieve the total space occupied by current roller coasters per park.
-- This data is useful for understanding the current space utilization and ensuring that the optimization does not exceed the available space.
SELECT park_id, SUM(num_coasters * 2000) AS total_space
FROM visitor_satisfaction_scores
GROUP BY park_id;

-- Query Description: Retrieve the total number of current roller coasters per park.
-- This data is useful for understanding the current capacity utilization and ensuring that the optimization does not exceed the maximum allowed number of roller coasters.
SELECT park_id, SUM(num_coasters) AS total_coasters
FROM visitor_satisfaction_scores
GROUP BY park_id;

-- Query Description: Retrieve a combined view of all constraints per park.
-- This query provides a comprehensive view of the constraints for each park, which is essential for ensuring that the optimization respects all constraints.
SELECT pb.park_id, pb.budget, pas.space, pmc.max_coasters
FROM park_budgets pb
JOIN park_available_space pas ON pb.park_id = pas.park_id
JOIN park_max_coasters pmc ON pb.park_id = pmc.park_id;

-- Query Description: Retrieve a combined view of visitor satisfaction scores and current roller coaster counts per park.
-- This query provides a detailed view of the current state of visitor satisfaction and roller coaster distribution, which is useful for the optimization process.
SELECT vss.park_id, vss.coaster_type, vss.score, vss.num_coasters
FROM visitor_satisfaction_scores vss
ORDER BY vss.park_id, vss.coaster_type;
```

These queries are designed to retrieve the necessary data for the optimization problem, including the objective function coefficients, decision variables, and constraint parameters. They also provide aggregated and summary data to help understand the current state and ensure that the optimization respects all constraints.
