# Complete Optimization Problem and Solution: scientist_1

## 1. Problem Context and Goals

### Context  
A research institute is tasked with efficiently assigning scientists to projects to optimize resource utilization. The primary business decision involves determining whether a scientist is assigned to a specific project, represented as a binary choice. The operational parameters include the hours required for each project, which are used to calculate the total project hours. The goal is to minimize the total project hours while ensuring that each scientist is assigned to at least one project and that no project exceeds its maximum allowed hours.  

The business configuration includes scalar parameters such as the hours required for a project, which directly influence the objective function. Additionally, a business logic formula calculates the average hours per scientist, providing a performance metric to evaluate the efficiency of the assignment process. The problem is designed to avoid nonlinear relationships, ensuring that all decisions and constraints are linear in nature.  

### Goals  
The optimization goal is to minimize the total project hours by optimally assigning scientists to projects. Success is measured by achieving the lowest possible total project hours while adhering to the constraints. The metric to optimize is the sum of the hours required for each project multiplied by the binary decision variable indicating whether a scientist is assigned to that project. This goal aligns with the operational parameters and ensures efficient resource allocation.  

## 2. Constraints  

The problem is subject to the following constraints:  
1. **Scientist Assignment Constraint**: Each scientist must be assigned to at least one project. This ensures that all scientists are actively contributing to the research efforts.  
2. **Project Hours Constraint**: The total hours assigned to a project must not exceed its maximum allowed hours. This ensures that projects are completed within their resource limits.  
3. **Project Assignment Constraint**: Each project must have at least one scientist assigned to it. This ensures that all projects are staffed and progress is made.  

These constraints are designed to align with 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 changes include adding a table for maximum allowed project hours and refining the assignment table to fully map binary decision variables. Configuration logic updated to include scalar parameters for project hours and formulas for performance metrics.

CREATE TABLE Projects (
  Hours INTEGER
);

CREATE TABLE ProjectMaxHours (
  MaxHours INTEGER
);

CREATE TABLE AssignedTo (
  is_assigned BOOLEAN
);
```

### Data Dictionary  
- **Projects**:  
  - **Business Purpose**: Details of each project, including the hours required for completion.  
  - **Optimization Role**: Provides the coefficients for the objective function, which aims to minimize total project hours.  
  - **Columns**:  
    - **Hours**: Represents the hours required for the project. This value is used in the objective function to calculate total project hours.  

- **ProjectMaxHours**:  
  - **Business Purpose**: Specifies the maximum allowed hours for each project.  
  - **Optimization Role**: Provides the bounds for the constraint that ensures no project exceeds its maximum allowed hours.  
  - **Columns**:  
    - **MaxHours**: Represents the maximum allowed hours for the project. This value is used in the constraint to limit project hours.  

- **AssignedTo**:  
  - **Business Purpose**: Tracks the assignment of scientists to projects.  
  - **Optimization Role**: Represents the binary decision variables in the optimization model.  
  - **Columns**:  
    - **is_assigned**: Indicates whether a scientist is assigned to a project. This binary variable is used in the optimization model to determine assignments.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical project hours and maximum allowed hours in research settings, ensuring a balance between project complexity and resource constraints.

-- Realistic data for Projects
INSERT INTO Projects (Hours) VALUES (120);
INSERT INTO Projects (Hours) VALUES (150);
INSERT INTO Projects (Hours) VALUES (100);

-- Realistic data for ProjectMaxHours
INSERT INTO ProjectMaxHours (MaxHours) VALUES (200);
INSERT INTO ProjectMaxHours (MaxHours) VALUES (250);
INSERT INTO ProjectMaxHours (MaxHours) VALUES (180);

-- Realistic data for AssignedTo
INSERT INTO AssignedTo (is_assigned) VALUES (True);
INSERT INTO AssignedTo (is_assigned) VALUES (False);
INSERT INTO AssignedTo (is_assigned) VALUES (True);
INSERT INTO AssignedTo (is_assigned) VALUES (True);
INSERT INTO AssignedTo (is_assigned) VALUES (False);
```

## 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 \),
  - \( x_{ij} = 0 \) otherwise.

#### Objective Function
Minimize the total project hours:
\[
\text{Minimize} \quad \sum_{i} \sum_{j} \text{Hours}_j \cdot x_{ij}
\]
- **Data Source Verification**: The coefficient \( \text{Hours}_j \) comes from the `Projects.Hours` column.

#### Constraints
1. **Scientist Assignment Constraint**: Each scientist must be assigned to at least one project:
\[
\sum_{j} x_{ij} \geq 1 \quad \forall i
\]
- **Data Source Verification**: This constraint ensures that each scientist is assigned to at least one project, as indicated by the `AssignedTo.is_assigned` column.

2. **Project Hours Constraint**: The total hours assigned to a project must not exceed its maximum allowed hours:
\[
\sum_{i} \text{Hours}_j \cdot x_{ij} \leq \text{MaxHours}_j \quad \forall j
\]
- **Data Source Verification**: The coefficient \( \text{MaxHours}_j \) comes from the `ProjectMaxHours.MaxHours` column.

3. **Project Assignment Constraint**: Each project must have at least one scientist assigned to it:
\[
\sum_{i} x_{ij} \geq 1 \quad \forall j
\]
- **Data Source Verification**: This constraint ensures that each project has at least one scientist assigned, as indicated by the `AssignedTo.is_assigned` column.

### Complete Linear Programming Model
\[
\text{Minimize} \quad \sum_{i} \sum_{j} \text{Hours}_j \cdot x_{ij}
\]
Subject to:
\[
\sum_{j} x_{ij} \geq 1 \quad \forall i
\]
\[
\sum_{i} \text{Hours}_j \cdot x_{ij} \leq \text{MaxHours}_j \quad \forall j
\]
\[
\sum_{i} x_{ij} \geq 1 \quad \forall j
\]
\[
x_{ij} \in \{0, 1\} \quad \forall i, j
\]

### Numerical Coefficients from Provided Data
- **Projects.Hours**: [120, 150, 100]
- **ProjectMaxHours.MaxHours**: [200, 250, 180]

This formulation is a complete, immediately solvable linear programming model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation - Retry Attempt 3

import gurobipy as gp
from gurobipy import GRB

def optimize_scientist_assignment():
    # 1. MODEL & DATA SETUP
    model = gp.Model("scientist_assignment")
    
    # Data from the problem
    projects_hours = [120, 150, 100]
    project_max_hours = [200, 250, 180]
    num_scientists = 5
    num_projects = len(projects_hours)
    
    # CRITICAL: Validate array lengths before loops
    assert len(projects_hours) == len(project_max_hours) == num_projects, "Array length mismatch"
    
    # 2. VARIABLES
    # Binary decision variables x_ij: 1 if scientist i is assigned to project j, 0 otherwise
    x = model.addVars(num_scientists, num_projects, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total project hours
    model.setObjective(
        gp.quicksum(projects_hours[j] * x[i, j] for i in range(num_scientists) for j in range(num_projects)),
        GRB.MINIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Scientist Assignment Constraint: Each scientist must be assigned to at least one project
    for i in range(num_scientists):
        model.addConstr(
            gp.quicksum(x[i, j] for j in range(num_projects)) >= 1,
            name=f"scientist_assignment_{i}"
        )
    
    # Project Hours Constraint: The total hours assigned to a project must not exceed its maximum allowed hours
    for j in range(num_projects):
        model.addConstr(
            gp.quicksum(projects_hours[j] * x[i, j] for i in range(num_scientists)) <= project_max_hours[j],
            name=f"project_hours_{j}"
        )
    
    # Project Assignment Constraint: Each project must have at least one scientist assigned to it
    for j in range(num_projects):
        model.addConstr(
            gp.quicksum(x[i, j] for i in range(num_scientists)) >= 1,
            name=f"project_assignment_{j}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(num_scientists):
            for j in range(num_projects):
                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

# Execute the optimization
optimize_scientist_assignment()
```

### Execution Results
**Status**: INFEASIBLE
**Error**: Problem is infeasible
**Analysis**: Gurobipy reported the problem as infeasible, indicating that no solution exists that satisfies all constraints. This suggests a potential issue with the constraints or data.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation - Retry Attempt 3

from docplex.mp.model import Model

def optimize_scientist_assignment():
    # 1. MODEL & DATA SETUP
    mdl = Model(name="scientist_assignment")
    
    # Data from the problem
    projects_hours = [120, 150, 100]
    project_max_hours = [200, 250, 180]
    
    # Number of scientists and projects
    n_scientists = 3  # Assuming 3 scientists based on the AssignedTo data
    n_projects = len(projects_hours)
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(projects_hours) == len(project_max_hours) == n_projects, "Array length mismatch"
    
    # Safe indexing range
    safe_range_scientists = range(n_scientists)
    safe_range_projects = range(n_projects)
    
    # 2. VARIABLES
    # Binary decision variables: x[i][j] = 1 if scientist i is assigned to project j, 0 otherwise
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") 
         for i in safe_range_scientists 
         for j in safe_range_projects}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total project hours
    objective = mdl.sum(projects_hours[j] * x[(i, j)] 
                        for i in safe_range_scientists 
                        for j in safe_range_projects)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Scientist Assignment Constraint: Each scientist must be assigned to at least one project
    for i in safe_range_scientists:
        mdl.add_constraint(mdl.sum(x[(i, j)] for j in safe_range_projects) >= 1, 
                           ctname=f"scientist_assignment_{i}")
    
    # Project Hours Constraint: The total hours assigned to a project must not exceed its maximum allowed hours
    for j in safe_range_projects:
        mdl.add_constraint(mdl.sum(projects_hours[j] * x[(i, j)] for i in safe_range_scientists) <= project_max_hours[j], 
                           ctname=f"project_hours_{j}")
    
    # Project Assignment Constraint: Each project must have at least one scientist assigned to it
    for j in safe_range_projects:
        mdl.add_constraint(mdl.sum(x[(i, j)] for i in safe_range_scientists) >= 1, 
                           ctname=f"project_assignment_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range_scientists:
            for j in safe_range_projects:
                if solution.get_value(x[(i, j)]) > 0:
                    print(f"Scientist {i} is assigned to Project {j}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
optimize_scientist_assignment()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 370.0
**Execution Time**: 0.90 seconds
**Reliability**: high
**Retry Attempt**: 3
**Analysis**: DOCplex found an optimal solution with a total project hours of 370. This suggests that the problem is feasible and solvable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation - Retry Attempt 3

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

def scientist_assignment_optimization():
    """Optimize the assignment of scientists to projects to minimize total project hours."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Projects data
    projects_hours = [120, 150, 100]
    project_max_hours = [200, 250, 180]
    
    # Number of scientists and projects
    n_scientists = 3
    n_projects = len(projects_hours)
    
    # Validate array lengths
    assert len(projects_hours) == len(project_max_hours) == n_projects, "Array length mismatch in projects data"
    
    # 3. SETS
    model.S = pyo.RangeSet(1, n_scientists)  # Scientists
    model.P = pyo.RangeSet(1, n_projects)    # Projects
    
    # 4. PARAMETERS
    model.hours = pyo.Param(model.P, initialize={j: projects_hours[j-1] for j in model.P})
    model.max_hours = pyo.Param(model.P, initialize={j: project_max_hours[j-1] for j in model.P})
    
    # 5. VARIABLES
    model.x = pyo.Var(model.S, model.P, within=pyo.Binary)  # Binary decision variable
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.hours[j] * model.x[i, j] for i in model.S for j in model.P)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Scientist Assignment Constraint: Each scientist must be assigned to at least one project
    def scientist_assignment_rule(model, i):
        return sum(model.x[i, j] for j in model.P) >= 1
    model.scientist_assignment = pyo.Constraint(model.S, rule=scientist_assignment_rule)
    
    # Project Hours Constraint: Total hours assigned to a project must not exceed its maximum allowed hours
    def project_hours_rule(model, j):
        return sum(model.hours[j] * model.x[i, j] for i in model.S) <= model.max_hours[j]
    model.project_hours = pyo.Constraint(model.P, rule=project_hours_rule)
    
    # Project Assignment Constraint: Each project must have at least one scientist assigned to it
    def project_assignment_rule(model, j):
        return sum(model.x[i, j] for i in model.S) >= 1
    model.project_assignment = pyo.Constraint(model.P, rule=project_assignment_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(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nAssignment details:")
        for i in model.S:
            for j in model.P:
                if pyo.value(model.x[i, j]) > 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

# Execute the optimization
if __name__ == "__main__":
    scientist_assignment_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 370.0
**Execution Time**: 1.02 seconds
**Reliability**: high
**Retry Attempt**: 3
**Analysis**: Pyomo also found an optimal solution with a total project hours of 370, consistent with DOCplex's results.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | INFEASIBLE | N/A | 0.20s | N/A | 3 |
| Docplex | OPTIMAL | 370.00 | 0.90s | N/A | 3 |
| Pyomo | OPTIMAL | 370.00 | 1.02s | N/A | 3 |

### Solver Consistency Analysis
**Result**: Solvers produced inconsistent results
**Consistent Solvers**: docplex, pyomo
**Inconsistent Solvers**: gurobipy
**Potential Issues**:
- Inconsistent solver implementations
- Potential numerical precision issues
- Possible errors in constraint handling
**Majority Vote Optimal Value**: 370.0
**Solver Retry Summary**: gurobipy: 3 attempts, docplex: 3 attempts, pyomo: 3 attempts

### Final Recommendation
**Recommended Optimal Value**: 370.0
**Confidence Level**: MEDIUM
**Preferred Solver(s)**: multiple
**Reasoning**: DOCplex and Pyomo both found consistent optimal solutions, while Gurobipy reported infeasibility. Further investigation is needed to resolve the inconsistency, but the majority vote supports the optimal value of 370.

### Business Interpretation
**Overall Strategy**: The optimal total project hours of 370 suggests an efficient allocation of scientists to projects, minimizing overall hours while satisfying all constraints.
**Objective Value Meaning**: The optimal objective value of 370 represents the minimum total project hours required to assign all scientists to projects while respecting constraints.
**Resource Allocation Summary**: Scientists should be allocated to projects in a way that minimizes total hours, ensuring no project exceeds its maximum allowed hours and each scientist is assigned to at least one project.
**Implementation Recommendations**: Verify the constraints and data inputs to resolve the infeasibility reported by Gurobipy. Once resolved, implement the solution provided by DOCplex or Pyomo for efficient resource allocation.