# Complete Optimization Problem and Solution: scientist_1

## 1. Problem Context and Goals

### Context  
The research organization is focused on optimizing the allocation of its scientists to various projects. The primary decision involves determining which scientists should be assigned to which projects. This decision is represented by binary variables, where each variable indicates whether a specific scientist is assigned to a particular project. The organization aims to maximize the total hours worked on projects, which is a linear objective. This involves summing the hours associated with each project, weighted by the assignment of scientists to those projects.

The business configuration includes specific operational parameters: each scientist must be assigned to a minimum number of projects, and each project must have a minimum number of scientists assigned. These parameters are critical as they ensure that scientists are engaged in multiple projects, promoting collaboration and efficient use of resources, while also guaranteeing that each project receives adequate attention.

The data used in this optimization process reflects current operational information, ensuring that the decision-making process is grounded in realistic scenarios. The constraints and objectives are designed to be linear, avoiding any nonlinear relationships such as variable products or divisions. The operational parameters are directly mapped to expected coefficient sources, ensuring a clear and consistent optimization framework.

### Goals  
The primary goal of this optimization problem is to maximize the total hours worked on projects. This is achieved by optimally assigning scientists to projects in a way that maximizes the sum of the hours associated with each project, weighted by the assignment of scientists. The success of this optimization is measured by the total hours worked, which directly aligns with the expected coefficient sources. The goal is articulated in natural language, focusing on the linear optimization objective without the use of mathematical formulas or symbolic notation.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the feasibility and practicality of the solution:

- Each scientist must be assigned to at least a minimum number of projects. This constraint ensures that scientists are actively engaged and contributing to multiple projects, fostering a collaborative environment.
  
- Each project must have at least a minimum number of scientists assigned. This constraint guarantees that every project receives the necessary attention and resources to be successful.

These constraints are described in business terms that naturally lead to linear mathematical forms, avoiding any nonlinear relationships such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema adjustments include creating new tables for constraint bounds, modifying existing tables to improve mapping adequacy, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE Projects (
  ProjectID INTEGER,
  Hours FLOAT
);

CREATE TABLE AssignedTo (
  ScientistID INTEGER,
  ProjectID INTEGER,
  binary_column BOOLEAN
);

CREATE TABLE ConstraintBounds (
  ConstraintType STRING,
  MinAssignments INTEGER
);
```

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

- **Projects Table**: This table stores information about each project, including the number of hours associated with it. The hours serve as coefficients in the optimization objective, representing the value of assigning scientists to projects.

- **AssignedTo Table**: This table tracks the assignment of scientists to projects. It includes a binary column that indicates whether a scientist is assigned to a project. This information is crucial for defining the decision variables in the optimization model.

- **ConstraintBounds Table**: This table stores the bounds for assignment constraints. It specifies the minimum number of assignments required for both scientists and projects, ensuring that the optimization model adheres to business requirements.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical project hours and realistic minimum assignment constraints to ensure a balanced workload among scientists and projects.

-- Realistic data for Projects
INSERT INTO Projects (ProjectID, Hours) VALUES (1, 15.0);
INSERT INTO Projects (ProjectID, Hours) VALUES (2, 25.0);
INSERT INTO Projects (ProjectID, Hours) VALUES (3, 35.0);

-- Realistic data for AssignedTo
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (101, 1, True);
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (101, 2, True);
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (102, 2, True);
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (102, 3, True);
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (103, 3, True);

-- Realistic data for ConstraintBounds
INSERT INTO ConstraintBounds (ConstraintType, MinAssignments) VALUES ('scientist', 2);
INSERT INTO ConstraintBounds (ConstraintType, MinAssignments) VALUES ('project', 1);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{ij} \) be a binary decision variable where \( x_{ij} = 1 \) if scientist \( i \) is assigned to project \( j \), and \( x_{ij} = 0 \) otherwise.

#### Objective Function
Maximize the total hours worked on projects:
\[
\text{Maximize } \sum_{j} \sum_{i} \text{Hours}_j \times x_{ij}
\]
where \(\text{Hours}_j\) is the number of hours associated with project \( j \) from the `Projects` table.

#### Constraints
1. **Minimum Projects per Scientist:**
   Each scientist must be assigned to at least a minimum number of projects.
   \[
   \sum_{j} x_{ij} \geq \text{MinAssignments}_{\text{scientist}} \quad \forall i
   \]
   where \(\text{MinAssignments}_{\text{scientist}}\) is the minimum number of projects a scientist must be assigned to, from the `ConstraintBounds` table.

2. **Minimum Scientists per Project:**
   Each project must have at least a minimum number of scientists assigned.
   \[
   \sum_{i} x_{ij} \geq \text{MinAssignments}_{\text{project}} \quad \forall j
   \]
   where \(\text{MinAssignments}_{\text{project}}\) is the minimum number of scientists required for each project, from the `ConstraintBounds` table.

3. **Binary Assignment:**
   Each assignment variable must be binary.
   \[
   x_{ij} \in \{0, 1\} \quad \forall i, j
   \]

Data Source Verification:
- \(\text{Hours}_j\) is sourced from `Projects.Hours`.
- \(\text{MinAssignments}_{\text{scientist}}\) is sourced from `ConstraintBounds` where `ConstraintType` is 'scientist'.
- \(\text{MinAssignments}_{\text{project}}\) is sourced from `ConstraintBounds` where `ConstraintType` is 'project'.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_scientist_allocation():
    """Optimize the allocation of scientists to projects to maximize total hours worked."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("scientist_allocation")
    
    # Data from the database
    projects = [(1, 15.0), (2, 25.0), (3, 35.0)]
    assignments = [(101, 1), (101, 2), (102, 2), (102, 3), (103, 3)]
    constraint_bounds = {'scientist': 2, 'project': 1}
    
    # Extracting data
    project_ids = [p[0] for p in projects]
    hours = {p[0]: p[1] for p in projects}
    scientist_ids = list(set(a[0] for a in assignments))
    
    # Validate data lengths
    assert len(project_ids) == len(hours), "Mismatch in project data lengths"
    
    # 2. VARIABLES
    # Decision variables: x[i, j] = 1 if scientist i is assigned to project j
    x = model.addVars(scientist_ids, project_ids, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total hours worked on projects
    model.setObjective(gp.quicksum(hours[j] * x[i, j] for i in scientist_ids for j in project_ids), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Minimum projects per scientist
    min_projects_per_scientist = constraint_bounds['scientist']
    model.addConstrs((gp.quicksum(x[i, j] for j in project_ids) >= min_projects_per_scientist for i in scientist_ids), 
                     name="min_projects_per_scientist")
    
    # Minimum scientists per project
    min_scientists_per_project = constraint_bounds['project']
    model.addConstrs((gp.quicksum(x[i, j] for i in scientist_ids) >= min_scientists_per_project for j in project_ids), 
                     name="min_scientists_per_project")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in scientist_ids:
            for j in project_ids:
                if x[i, j].x > 1e-6:
                    print(f"Scientist {i} assigned to Project {j}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_scientist_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 225.0
**Execution Time**: 0.20 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with the shortest execution time, indicating efficient performance and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_scientist_allocation():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="scientist_allocation")

    # Data from the database
    projects = [(1, 15.0), (2, 25.0), (3, 35.0)]
    assignments = [(101, 1), (101, 2), (102, 2), (102, 3), (103, 3)]
    constraint_bounds = {'scientist': 2, 'project': 1}

    # Extracting data
    project_ids = [p[0] for p in projects]
    hours = {p[0]: p[1] for p in projects}
    scientist_ids = list(set(a[0] for a in assignments))

    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(project_ids) == len(hours), "Array length mismatch"
    safe_range_projects = range(len(project_ids))
    safe_range_scientists = range(len(scientist_ids))

    # 2. VARIABLES
    x = {(s, p): mdl.binary_var(name=f"x_{s}_{p}") for s in scientist_ids for p in project_ids}

    # 3. OBJECTIVE FUNCTION
    objective = mdl.sum(hours[p] * x[s, p] for s in scientist_ids for p in project_ids)
    mdl.maximize(objective)

    # 4. CONSTRAINTS

    # Minimum Projects per Scientist
    for s in scientist_ids:
        mdl.add_constraint(mdl.sum(x[s, p] for p in project_ids) >= constraint_bounds['scientist'], ctname=f"min_projects_{s}")

    # Minimum Scientists per Project
    for p in project_ids:
        mdl.add_constraint(mdl.sum(x[s, p] for s in scientist_ids) >= constraint_bounds['project'], ctname=f"min_scientists_{p}")

    # 5. SOLVING & RESULTS
    solution = mdl.solve()

    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for s in scientist_ids:
            for p in project_ids:
                value = solution.get_value(x[s, p])
                if value > 1e-6:
                    print(f"x[{s},{p}] = {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Run the optimization
optimize_scientist_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 225.0
**Execution Time**: 3.21 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, albeit with a longer execution time compared to Gurobipy, but still reliable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

import pyomo.environ as pyo
from pyomo.opt import SolverFactory

def scientist_optimization():
    """Optimization model for assigning scientists to projects"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Data from the database
    projects_data = {1: 15.0, 2: 25.0, 3: 35.0}
    assigned_to_data = [
        (101, 1, True), (101, 2, True),
        (102, 2, True), (102, 3, True),
        (103, 3, True)
    ]
    constraint_bounds_data = {
        'scientist': 2,
        'project': 1
    }
    
    # Extract unique scientist and project IDs
    scientist_ids = set(scientist for scientist, _, _ in assigned_to_data)
    project_ids = set(project for _, project, _ in assigned_to_data)
    
    # 3. SETS
    model.I = pyo.Set(initialize=scientist_ids)
    model.J = pyo.Set(initialize=project_ids)
    
    # 4. PARAMETERS
    model.hours = pyo.Param(model.J, initialize=projects_data)
    model.min_assignments_scientist = pyo.Param(initialize=constraint_bounds_data['scientist'])
    model.min_assignments_project = pyo.Param(initialize=constraint_bounds_data['project'])
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, model.J, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.hours[j] * model.x[i, j] for i in model.I for j in model.J)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Minimum Projects per Scientist
    def min_projects_per_scientist_rule(model, i):
        return sum(model.x[i, j] for j in model.J) >= model.min_assignments_scientist
    model.min_projects_per_scientist = pyo.Constraint(model.I, rule=min_projects_per_scientist_rule)
    
    # Minimum Scientists per Project
    def min_scientists_per_project_rule(model, j):
        return sum(model.x[i, j] for i in model.I) >= model.min_assignments_project
    model.min_scientists_per_project = pyo.Constraint(model.J, rule=min_scientists_per_project_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # Optional: Set solver options
    solver.options['TimeLimit'] = 300  # 5 minutes
    solver.options['MIPGap'] = 0.01    # 1% gap
    
    # Solve the model
    results = solver.solve(model, tee=True)  # tee=True shows solver output
    
    # 9. RESULT PROCESSING
    # Check solver status
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nAssignment of scientists to projects:")
        for i in model.I:
            for j in model.J:
                x_val = pyo.value(model.x[i, j])
                if x_val > 0.5:  # Only print assignments
                    print(f"Scientist {i} assigned to Project {j}")
        
    elif results.solver.termination_condition == pyo.TerminationCondition.infeasible:
        print("Problem is infeasible")
    elif results.solver.termination_condition == pyo.TerminationCondition.unbounded:
        print("Problem is unbounded")
    else:
        print(f"Solver terminated with condition: {results.solver.termination_condition}")
    
    return model

# Run the optimization
scientist_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 225.0
**Execution Time**: 3.64 seconds
**Reliability**: high
**Analysis**: Pyomo achieved the optimal solution with the longest execution time among the solvers, but the result is consistent and reliable.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 225.00 | 0.20s | N/A | N/A |
| Docplex | OPTIMAL | 225.00 | 3.21s | N/A | N/A |
| Pyomo | OPTIMAL | 225.00 | 3.64s | N/A | N/A |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Majority Vote Optimal Value**: 225.0

### Final Recommendation
**Recommended Optimal Value**: 225.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its faster execution time while maintaining the same optimal solution as the other solvers.

### Business Interpretation
**Overall Strategy**: The optimal allocation of scientists to projects maximizes the total hours worked, ensuring efficient use of resources.
**Objective Value Meaning**: The optimal objective value of 225.0 represents the maximum total hours that can be allocated to projects, optimizing resource utilization.
**Resource Allocation Summary**: Each scientist is assigned to projects in a way that maximizes total hours worked while meeting minimum assignment constraints.
**Implementation Recommendations**: Implement the solution by assigning scientists to projects as per the optimal decision variables, ensuring compliance with constraints.