# Complete Optimization Problem and Solution: musical

## 1. Problem Context and Goals

### Context  
A theater company is planning multiple musical performances and aims to maximize audience engagement by optimally assigning actors to roles. The engagement metric is calculated as a weighted combination of each actor's age and their performance duration. The company has a set of actors, each with a specific age and performance duration, and a set of roles that need to be filled. The decision to assign an actor to a role is binary, meaning an actor is either assigned to a role or not. 

The company has established two weights to balance the importance of age and performance duration in the engagement metric. The weight for age is set to 0.6, emphasizing the value of experience, while the weight for performance duration is set to 0.4, ensuring a balanced consideration of performance length. Additionally, the company has set a maximum total duration limit of 100 minutes for all performances combined to ensure the event remains within a feasible timeframe.

The goal is to assign actors to roles in such a way that the total engagement is maximized, while ensuring that each actor is assigned to at most one role, each role is filled by exactly one actor, and the total duration of all performances does not exceed the allowed limit.

### Goals  
The primary optimization goal is to maximize the total audience engagement across all performances. This is achieved by assigning actors to roles in a way that maximizes the weighted sum of their ages and performance durations. Success is measured by the total engagement score, which is calculated using the predefined weights for age and performance duration. The solution must also respect the constraints on actor assignments and the total performance duration.

## 2. Constraints    

The optimization problem is subject to the following constraints:

1. **Actor Assignment Limit**: Each actor can be assigned to at most one role. This ensures that no actor is overcommitted and can focus on a single performance.
   
2. **Role Fulfillment**: Each role must be filled by exactly one actor. This ensures that all roles are covered and no role is left unassigned.

3. **Total Duration Limit**: The combined duration of all performances must not exceed the maximum allowed total duration of 100 minutes. This ensures that the event remains within the planned timeframe.

These constraints ensure that the assignment of actors to roles is both feasible and aligned with the company's operational requirements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for roles, actors, and assignments. Configuration logic updates include adding weights for engagement metric and maximum duration.

CREATE TABLE roles (
  role_id INTEGER,
  musical_id INTEGER,
  role_name STRING
);

CREATE TABLE actors (
  actor_id INTEGER,
  age INTEGER,
  performance_duration INTEGER
);

CREATE TABLE assignments (
  assignment_id INTEGER,
  actor_id INTEGER,
  role_id INTEGER,
  assignment_status BOOLEAN
);
```

### Data Dictionary  
- **Roles Table**: Contains information about the roles available in different musicals. Each role has a unique identifier, a musical identifier, and a role name.
  - **role_id**: Unique identifier for each role.
  - **musical_id**: Identifier for the musical the role belongs to.
  - **role_name**: Name of the role.

- **Actors Table**: Contains information about the actors, including their age and performance duration.
  - **actor_id**: Unique identifier for each actor.
  - **age**: Age of the actor, used in the engagement metric.
  - **performance_duration**: Duration of the actor's performance, used in the engagement metric.

- **Assignments Table**: Tracks the assignments of actors to roles. Each assignment has a unique identifier, an actor identifier, a role identifier, and a status indicating whether the actor is assigned to the role.
  - **assignment_id**: Unique identifier for each assignment.
  - **actor_id**: Identifier for the actor assigned.
  - **role_id**: Identifier for the role assigned.
  - **assignment_status**: Indicates if the actor is assigned to the role (True/False).

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic theater company scenarios, considering typical actor ages, performance durations, and role assignments. The weights and maximum duration were set to balance the engagement metric and ensure feasible solutions.

-- Realistic data for roles
INSERT INTO roles (role_id, musical_id, role_name) VALUES (1, 1, 'Lead');
INSERT INTO roles (role_id, musical_id, role_name) VALUES (2, 1, 'Supporting');
INSERT INTO roles (role_id, musical_id, role_name) VALUES (3, 2, 'Chorus');

-- Realistic data for actors
INSERT INTO actors (actor_id, age, performance_duration) VALUES (1, 28, 15);
INSERT INTO actors (actor_id, age, performance_duration) VALUES (2, 35, 20);
INSERT INTO actors (actor_id, age, performance_duration) VALUES (3, 22, 10);

-- Realistic data for assignments
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (1, 1, 1, True);
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (2, 2, 2, True);
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (3, 3, 3, True);
```