# 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.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic climbing scenarios, considering factors like difficulty levels of mountains, climber experience, and time constraints.

-- Realistic data for climber_assignment
INSERT INTO climber_assignment (Climber_ID, Mountain_ID) VALUES (1, 1);
INSERT INTO climber_assignment (Climber_ID, Mountain_ID) VALUES (2, 2);
INSERT INTO climber_assignment (Climber_ID, Mountain_ID) VALUES (3, 3);

-- Realistic data for total_time_limit
INSERT INTO total_time_limit (Total_Time_Limit) VALUES (100);

-- Realistic data for climber_points
INSERT INTO climber_points (Climber_ID, Mountain_ID, Points) VALUES (1, 1, 10);
INSERT INTO climber_points (Climber_ID, Mountain_ID, Points) VALUES (2, 2, 20);
INSERT INTO climber_points (Climber_ID, Mountain_ID, Points) VALUES (3, 3, 30);

-- Realistic data for climber_time
INSERT INTO climber_time (Climber_ID, Mountain_ID, Time) VALUES (1, 1, 5);
INSERT INTO climber_time (Climber_ID, Mountain_ID, Time) VALUES (2, 2, 10);
INSERT INTO climber_time (Climber_ID, Mountain_ID, Time) VALUES (3, 3, 15);
```