# Complete Optimization Problem and Solution: architecture

## 1. Problem Context and Goals

### Context  
A construction company is tasked with optimizing the allocation of projects to architects to minimize the total length of bridges and mills built. The company must decide which bridges and mills to construct, represented by binary decision variables indicating whether a specific bridge or mill is built. Each bridge has a defined length in meters, and each mill has a defined length in feet, which directly contribute to the total length to be minimized.  

The company operates under specific business rules to ensure fair and manageable workloads for architects. Each architect must be assigned to at least one project, as defined by the minimum number of projects per architect, and no architect can be assigned more than three projects, as defined by the maximum number of projects per architect. These rules ensure a balanced distribution of work while preventing overburdening any single architect.  

The operational parameters, such as the lengths of bridges and mills, are sourced from the current database, which includes realistic data generated by experts in business, data, and optimization. The business configuration parameters, including the minimum and maximum number of projects per architect, are defined as scalar values and are critical for enforcing the constraints in the optimization problem.  

### Goals  
The primary goal of this optimization problem is to minimize the total length of bridges and mills built by architects. This is achieved by strategically selecting which bridges and mills to construct, ensuring that the sum of their lengths is as small as possible. Success is measured by the reduction in total length, which directly impacts resource efficiency and cost savings for the company.  

The optimization process ensures that all architects are assigned to at least one project and that no architect is assigned more than three projects. These constraints are critical for maintaining workload balance and operational feasibility.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Minimum Projects per Architect**: Each architect must be assigned to at least one project. This ensures that all architects are actively contributing to the construction efforts.  
2. **Maximum Projects per Architect**: No architect can be assigned more than three projects. This prevents overburdening any single architect and ensures manageable workloads.  

These constraints are enforced using the minimum and maximum number of projects per architect, which are defined as scalar parameters in the business configuration.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding tables for decision variables and constraint bounds, updating the data dictionary, and refining business configuration logic to include scalar parameters for minimum and maximum projects per architect.

CREATE TABLE bridge_lengths (
  bridge_id INTEGER,
  length_meters INTEGER
);

CREATE TABLE mill_lengths (
  mill_id INTEGER,
  length_feet INTEGER
);

CREATE TABLE architect_project_assignments (
  architect_id INTEGER,
  bridge_assignment BOOLEAN,
  mill_assignment BOOLEAN
);

CREATE TABLE architect_constraints (
  architect_id INTEGER,
  min_projects INTEGER,
  max_projects INTEGER
);
```

### Data Dictionary  
- **bridge_lengths**: Stores the length of each bridge in meters, which is used to calculate the total length of bridges built.  
  - `bridge_id`: Unique identifier for each bridge.  
  - `length_meters`: Length of the bridge in meters, used as a coefficient in the optimization objective.  

- **mill_lengths**: Stores the length of each mill in feet, which is used to calculate the total length of mills built.  
  - `mill_id`: Unique identifier for each mill.  
  - `length_feet`: Length of the mill in feet, used as a coefficient in the optimization objective.  

- **architect_project_assignments**: Tracks the assignment of architects to projects, indicating whether a bridge or mill is built.  
  - `architect_id`: Unique identifier for each architect.  
  - `bridge_assignment`: Indicates whether the architect is assigned to build a bridge.  
  - `mill_assignment`: Indicates whether the architect is assigned to build a mill.  

- **architect_constraints**: Defines the minimum and maximum number of projects each architect can handle.  
  - `architect_id`: Unique identifier for each architect.  
  - `min_projects`: Minimum number of projects the architect must be assigned to.  
  - `max_projects`: Maximum number of projects the architect can be assigned to.  


### Retrieved Values

**Query 1: This data is crucial for calculating the total length of bridges and mills built, which is the primary goal of the optimization.**

```sql
SELECT mill_id, length_feet FROM mill_lengths;
```

**Results (CSV format):**
```csv
mill_id,length_feet
1,350
2,450
3,550
```

**Query 2: This data is essential for determining which bridges and mills are currently assigned to architects, and it will be used to enforce the constraints on the number of projects per architect.**

```sql
SELECT architect_id, bridge_assignment, mill_assignment FROM architect_project_assignments;
```

**Results (CSV format):**
```csv
architect_id,bridge_assignment,mill_assignment
1,1,0
2,0,1
3,1,1
```

**Query 3: This data is critical for enforcing the constraints that each architect must be assigned to at least one project and no more than three projects.**

```sql
SELECT architect_id, min_projects, max_projects FROM architect_constraints;
```

**Results (CSV format):**
```csv
architect_id,min_projects,max_projects
1,1,3
2,1,3
3,1,3
```

**Query 4: This aggregated data is useful for ensuring that the constraints on the number of projects per architect are satisfied.**

```sql
SELECT architect_id, SUM(CASE WHEN bridge_assignment = TRUE THEN 1 ELSE 0 END) + SUM(CASE WHEN mill_assignment = TRUE THEN 1 ELSE 0 END) AS total_projects FROM architect_project_assignments GROUP BY architect_id;
```

**Results (CSV format):**
```csv
architect_id,total_projects
1,1
2,1
3,2
```

**Query 5: This aggregated data is useful for evaluating the current total length of projects assigned to each architect, which is directly related to the objective function.**

```sql
SELECT a.architect_id, SUM(b.length_meters) AS total_bridge_length_meters, SUM(m.length_feet) AS total_mill_length_feet FROM architect_project_assignments a LEFT JOIN bridge_lengths b ON a.bridge_assignment = TRUE LEFT JOIN mill_lengths m ON a.mill_assignment = TRUE GROUP BY a.architect_id;
```

**Results (CSV format):**
```csv
architect_id,total_bridge_length_meters,total_mill_length_feet
1,770.0,
2,,1350.0
3,2310.0,4050.0
```

**Query 6: This data is important for identifying architects who need to be assigned additional projects to satisfy the minimum projects constraint.**

```sql
SELECT architect_id, SUM(CASE WHEN bridge_assignment = TRUE THEN 1 ELSE 0 END) + SUM(CASE WHEN mill_assignment = TRUE THEN 1 ELSE 0 END) AS total_projects FROM architect_project_assignments GROUP BY architect_id ) ap ON ac.architect_id = ap.architect_id WHERE ap.total_projects < ac.min_projects;
```

**Error:** Execution failed on sql 'SELECT architect_id, SUM(CASE WHEN bridge_assignment = TRUE THEN 1 ELSE 0 END) + SUM(CASE WHEN mill_assignment = TRUE THEN 1 ELSE 0 END) AS total_projects FROM architect_project_assignments GROUP BY architect_id ) ap ON ac.architect_id = ap.architect_id WHERE ap.total_projects < ac.min_projects;': near ")": syntax error

**Query 7: This data is important for identifying architects who need to have some projects reassigned to satisfy the maximum projects constraint.**

```sql
SELECT architect_id, SUM(CASE WHEN bridge_assignment = TRUE THEN 1 ELSE 0 END) + SUM(CASE WHEN mill_assignment = TRUE THEN 1 ELSE 0 END) AS total_projects FROM architect_project_assignments GROUP BY architect_id ) ap ON ac.architect_id = ap.architect_id WHERE ap.total_projects > ac.max_projects;
```

**Error:** Execution failed on sql 'SELECT architect_id, SUM(CASE WHEN bridge_assignment = TRUE THEN 1 ELSE 0 END) + SUM(CASE WHEN mill_assignment = TRUE THEN 1 ELSE 0 END) AS total_projects FROM architect_project_assignments GROUP BY architect_id ) ap ON ac.architect_id = ap.architect_id WHERE ap.total_projects > ac.max_projects;': near ")": syntax error

**Query 8: This data is useful for identifying potential projects that can be assigned to architects to satisfy the minimum projects constraint or to further optimize the total length.**

```sql
SELECT m.mill_id, m.length_feet FROM mill_lengths m LEFT JOIN architect_project_assignments a ON m.mill_id = a.mill_assignment WHERE a.mill_assignment IS NULL;
```

**Results (CSV format):**
```csv
mill_id,length_feet
2,450
3,550
```

