# Complete Optimization Problem and Solution: climbing

## 1. Problem Context and Goals

### Context  
A climbing organization is focused on optimizing the assignment of climbers to mountains to maximize the total points earned by the climbers. Each climber can be assigned to only one mountain, and the organization must ensure that the total time spent by all climbers does not exceed a predefined limit. The decision to assign a climber to a mountain is represented as a binary choice: either the climber is assigned to the mountain (1) or not (0). 

The points earned by a climber when assigned to a specific mountain are determined based on the difficulty and prestige of the mountain. For example, a beginner-level climb might earn 10 points, while more challenging climbs could earn 20 or 30 points. Similarly, the time spent by a climber on a specific mountain is also predefined, with beginner climbs taking 5 hours and more difficult climbs taking up to 15 hours. These points and time values are critical for determining the optimal assignments.

The organization’s goal is to make these assignments in a way that maximizes the total points earned while adhering to the total time constraint. This problem naturally lends itself to a linear optimization formulation, as the relationships between assignments, points, and time are straightforward and additive.

### Goals  
The primary goal of this optimization problem is to maximize the total points earned by climbers through their assignments to mountains. Success is measured by the sum of points earned across all climbers, where each climber’s contribution to the total points depends on the mountain they are assigned to. The organization aims to achieve this goal while ensuring that the total time spent by all climbers does not exceed the predefined limit and that each climber is assigned to exactly one mountain.

## 2. Constraints  

The optimization problem is subject to two key constraints:  
1. **Total Time Constraint**: The sum of the time spent by all climbers on their assigned mountains must not exceed the organization’s predefined total time limit. This ensures that the climbing schedule remains feasible and manageable.  
2. **Single Assignment Constraint**: Each climber must be assigned to exactly one mountain. This ensures fairness and prevents overloading any single climber with multiple assignments.  

These constraints are naturally linear, as they involve straightforward sums of time and assignments without any multiplicative or divisive relationships between variables.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added tables for points and time spent by climbers on mountains, updated configuration logic for scalar parameters and formulas, and ensured all optimization requirements are mapped.

CREATE TABLE climber_assignment (
  Climber_ID INTEGER,
  Mountain_ID INTEGER
);

CREATE TABLE total_time_limit (
  Total_Time_Limit INTEGER
);

CREATE TABLE climber_points (
  Climber_ID INTEGER,
  Mountain_ID INTEGER,
  Points INTEGER
);

CREATE TABLE climber_time (
  Climber_ID INTEGER,
  Mountain_ID INTEGER,
  Time INTEGER
);
```

### Data Dictionary  
- **climber_assignment**: Tracks which climber is assigned to which mountain.  
  - *Climber_ID*: Unique identifier for a climber, used to identify the climber in assignment decisions.  
  - *Mountain_ID*: Unique identifier for a mountain, used to identify the mountain in assignment decisions.  

- **total_time_limit**: Stores the maximum total time allowed for all climbers.  
  - *Total_Time_Limit*: The maximum total time allowed for all climbers, serving as a constraint bound for the optimization problem.  

- **climber_points**: Records the points earned by a climber when assigned to a specific mountain.  
  - *Climber_ID*: Unique identifier for a climber, used in points calculations.  
  - *Mountain_ID*: Unique identifier for a mountain, used in points calculations.  
  - *Points*: The points earned by a climber when assigned to a specific mountain, serving as the objective coefficient for the optimization problem.  

- **climber_time**: Records the time spent by a climber when assigned to a specific mountain.  
  - *Climber_ID*: Unique identifier for a climber, used in time calculations.  
  - *Mountain_ID*: Unique identifier for a mountain, used in time calculations.  
  - *Time*: The time spent by a climber when assigned to a specific mountain, serving as a constraint coefficient for the total time limit.  


### Retrieved Values

**Query 1: This is crucial as it defines the maximum allowable total time for all climbers, which is a key constraint.**

```sql
SELECT Total_Time_Limit FROM total_time_limit;
```

**Results (CSV format):**
```csv
Total_Time_Limit
100
```

**Query 2: This data is essential for the objective function, as it represents the points to be maximized.**

```sql
SELECT Climber_ID, Mountain_ID, Points FROM climber_points;
```

**Results (CSV format):**
```csv
Climber_ID,Mountain_ID,Points
1,1,10
2,2,20
3,3,30
```

**Query 3: This data is necessary for the total time constraint, as it represents the time associated with each assignment.**

```sql
SELECT Climber_ID, Mountain_ID, Time FROM climber_time;
```

**Results (CSV format):**
```csv
Climber_ID,Mountain_ID,Time
1,1,5
2,2,10
3,3,15
```

**Query 4: This data helps in understanding the current state of assignments, which may be useful for initializing or validating the optimization model.**

```sql
SELECT Climber_ID, Mountain_ID FROM climber_assignment;
```

**Results (CSV format):**
```csv
Climber_ID,Mountain_ID
1,1
2,2
3,3
```

**Query 5: This aggregated data can help in understanding the potential contribution of each climber to the overall objective and constraints.**

```sql
SELECT cp.Climber_ID, SUM(cp.Points) AS Total_Points, SUM(ct.Time) AS Total_Time FROM climber_points cp JOIN climber_time ct ON cp.Climber_ID = ct.Climber_ID AND cp.Mountain_ID = ct.Mountain_ID GROUP BY cp.Climber_ID;
```

**Results (CSV format):**
```csv
Climber_ID,Total_Points,Total_Time
1,10,5
2,20,10
3,30,15
```

**Query 6: This aggregated data can help in understanding the potential contribution of each mountain to the overall objective and constraints.**

```sql
SELECT cp.Mountain_ID, SUM(cp.Points) AS Total_Points, SUM(ct.Time) AS Total_Time FROM climber_points cp JOIN climber_time ct ON cp.Climber_ID = ct.Climber_ID AND cp.Mountain_ID = ct.Mountain_ID GROUP BY cp.Mountain_ID;
```

**Results (CSV format):**
```csv
Mountain_ID,Total_Points,Total_Time
1,10,5
2,20,10
3,30,15
```

**Query 7: This data can be useful for identifying the best possible assignment for each climber, which can serve as an initial heuristic for the optimization.**

```sql
SELECT cp.Climber_ID, MAX(cp.Points) AS Max_Points, ct.Time FROM climber_points cp JOIN climber_time ct ON cp.Climber_ID = ct.Climber_ID AND cp.Mountain_ID = ct.Mountain_ID GROUP BY cp.Climber_ID;
```

**Results (CSV format):**
```csv
Climber_ID,Max_Points,Time
1,10,5
2,20,10
3,30,15
```

**Query 8: This data can be useful for identifying the most time-efficient assignment for each climber, which can help in meeting the total time constraint.**

```sql
SELECT ct.Climber_ID, MIN(ct.Time) AS Min_Time, cp.Points FROM climber_time ct JOIN climber_points cp ON ct.Climber_ID = cp.Climber_ID AND ct.Mountain_ID = cp.Mountain_ID GROUP BY ct.Climber_ID;
```

**Results (CSV format):**
```csv
Climber_ID,Min_Time,Points
1,5,10
2,10,20
3,15,30
```

**Query 9: This aggregated data provides a high-level overview of the potential total points and total time, which can be useful for initial feasibility checks.**

```sql
SELECT SUM(cp.Points) AS Total_Points, SUM(ct.Time) AS Total_Time FROM climber_points cp JOIN climber_time ct ON cp.Climber_ID = ct.Climber_ID AND cp.Mountain_ID = ct.Mountain_ID;
```

**Results (CSV format):**
```csv
Total_Points,Total_Time
60,30
```

