# Complete Optimization Problem and Solution: roller_coaster

## 1. Problem Context and Goals

### Context  
The theme park company is focused on optimizing the allocation of maintenance resources across its roller coasters to minimize downtime while ensuring safety standards are met. The primary business decision involves determining the number of maintenance hours to allocate to each roller coaster. This decision is represented by the continuous variable of maintenance hours for each roller coaster. The operational parameters are aligned with the objective of minimizing total downtime, which is calculated as the sum of the product of downtime coefficients and maintenance hours for each roller coaster.

The business configuration includes a total of 22 maintenance hours available across all roller coasters, which serves as a constraint on the total maintenance hours that can be allocated. Business logic ensures that each roller coaster receives a minimum number of maintenance hours necessary for safety, and there is a limit on the maximum maintenance hours that can be allocated to each roller coaster. These constraints are expressed in natural language to describe linear relationships, ensuring that the problem formulation remains linear and avoids nonlinear scenarios.

### Goals  
The optimization goal is to minimize the total downtime of the roller coasters. This is achieved by optimizing the allocation of maintenance hours, with the metric to optimize being the total downtime, calculated as the sum of the product of downtime coefficients and maintenance hours for each roller coaster. Success is measured by the reduction in total downtime, aligning with the expected coefficient sources. The goal is described in natural language to maintain a clear focus on linear optimization.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that the allocation of maintenance hours is both efficient and safe:

- The total number of maintenance hours allocated across all roller coasters must not exceed the available 22 hours. This ensures that the maintenance resources are used within the limits of what is available.
- Each roller coaster must receive a minimum number of maintenance hours to meet safety requirements. This constraint ensures that safety standards are not compromised.
- The maintenance hours allocated to each roller coaster must not exceed a specified maximum. This prevents over-allocation of resources to any single roller coaster, ensuring a balanced distribution of maintenance efforts.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, avoiding any nonlinear relationships.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema adjustments include adding tables for minimum and maximum maintenance hours constraints, updating business configuration logic for scalar parameters and formulas, and ensuring all mappings are complete and adequate.

CREATE TABLE roller_coaster_maintenance (
  roller_coaster_id INTEGER,
  hours FLOAT
);

CREATE TABLE downtime_coefficients (
  roller_coaster_id INTEGER,
  coefficient FLOAT
);

CREATE TABLE maintenance_constraints (
  roller_coaster_id INTEGER,
  minimum_hours FLOAT,
  maximum_hours FLOAT
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles:

- **Roller Coaster Maintenance Table**: This table stores the number of maintenance hours allocated to each roller coaster. The primary role of this table is to represent the decision variables in the optimization problem.
  - **Roller Coaster ID**: A unique identifier for each roller coaster, used to allocate maintenance hours.
  - **Hours**: The number of maintenance hours allocated, serving as the decision variable for maintenance allocation.

- **Downtime Coefficients Table**: This table represents the impact of maintenance hours on downtime for each roller coaster. It plays a crucial role in the objective function of the optimization problem.
  - **Roller Coaster ID**: A unique identifier for each roller coaster, used to associate downtime coefficients.
  - **Coefficient**: Represents the impact of maintenance hours on downtime, serving as a coefficient in the objective function.

- **Maintenance Constraints Table**: This table stores the minimum and maximum maintenance hours constraints for each roller coaster, ensuring safety and resource allocation limits.
  - **Roller Coaster ID**: A unique identifier for each roller coaster, used to apply constraints.
  - **Minimum Hours**: The minimum maintenance hours required for safety, serving as a lower bound constraint.
  - **Maximum Hours**: The maximum maintenance hours that can be allocated, serving as an upper bound constraint.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical maintenance schedules and downtime impacts for theme park roller coasters, ensuring a balance between safety requirements and operational efficiency.

-- Realistic data for roller_coaster_maintenance
INSERT INTO roller_coaster_maintenance (roller_coaster_id, hours) VALUES (1, 6.0);
INSERT INTO roller_coaster_maintenance (roller_coaster_id, hours) VALUES (2, 9.0);
INSERT INTO roller_coaster_maintenance (roller_coaster_id, hours) VALUES (3, 7.0);

-- Realistic data for downtime_coefficients
INSERT INTO downtime_coefficients (roller_coaster_id, coefficient) VALUES (1, 0.4);
INSERT INTO downtime_coefficients (roller_coaster_id, coefficient) VALUES (2, 0.35);
INSERT INTO downtime_coefficients (roller_coaster_id, coefficient) VALUES (3, 0.25);

-- Realistic data for maintenance_constraints
INSERT INTO maintenance_constraints (roller_coaster_id, minimum_hours, maximum_hours) VALUES (1, 3.0, 10.0);
INSERT INTO maintenance_constraints (roller_coaster_id, minimum_hours, maximum_hours) VALUES (2, 4.0, 12.0);
INSERT INTO maintenance_constraints (roller_coaster_id, minimum_hours, maximum_hours) VALUES (3, 2.5, 9.0);
```