# Complete Optimization Problem and Solution: company_office

## 1. Problem Context and Goals

### Context  
The company is focused on optimizing the allocation of office spaces across multiple buildings to minimize the total leasing cost. The decision involves determining the amount of space to allocate to each company in each building, ensuring that each company's space requirements are met based on their sales and assets. The cost per square foot for leasing space varies by building, and the total available space in each building is limited. The goal is to allocate space in a way that minimizes the overall leasing cost while adhering to these constraints.

Key operational parameters include:
- **Cost per square foot for leasing space in each building**: This is used to calculate the total leasing cost, which is the primary metric to minimize.
- **Minimum space required by each company**: This ensures that each company receives enough space to meet its operational needs based on its sales and assets.
- **Total available space in each building**: This ensures that the total space allocated in each building does not exceed its capacity.

The total leasing cost is calculated by summing the product of the cost per square foot for each building and the space allocated to each company in that building. This calculation is linear and directly tied to the decision variables, ensuring a straightforward optimization formulation.

### Goals  
The primary goal of this optimization problem is to minimize the total leasing cost of office spaces across all buildings. This is achieved by determining the optimal allocation of space to each company in each building, considering the cost per square foot for each building and the space requirements of each company. Success is measured by achieving the lowest possible total leasing cost while ensuring that all companies receive their required space and that no building exceeds its available space capacity.

## 2. Constraints    

The optimization problem is subject to the following constraints:
1. **Minimum Space Requirement**: The total space allocated to each company across all buildings must meet or exceed the company's minimum space requirement. This ensures that each company has enough space to operate effectively based on its sales and assets.
2. **Building Capacity**: The total space allocated in each building must not exceed the building's available space. This ensures that the physical limitations of each building are respected.
3. **Non-Negative Allocation**: The space allocated to each company in each building must be non-negative. This ensures that the allocation is physically meaningful and feasible.

These constraints are linear and directly tied to the decision variables, ensuring that the optimization problem remains linear and tractable.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added space_allocated table to map decision variables, updated business configuration logic with scalar parameters and formulas, and ensured schema follows normalization principles.

CREATE TABLE cost_per_sqft (
  building_id INTEGER,
  cost_per_sqft FLOAT
);

CREATE TABLE required_space (
  company_id INTEGER,
  required_space INTEGER
);

CREATE TABLE available_space (
  building_id INTEGER,
  available_space INTEGER
);

CREATE TABLE space_allocated (
  building_id INTEGER,
  company_id INTEGER,
  space_allocated FLOAT
);
```

### Data Dictionary  
- **cost_per_sqft**: This table contains the cost per square foot for leasing space in each building. It is used to calculate the total leasing cost in the objective function.
  - **building_id**: Unique identifier for each building.
  - **cost_per_sqft**: Cost per square foot for leasing space in the building.
  
- **required_space**: This table contains the minimum space required by each company based on their sales and assets. It is used to ensure that each company's space requirements are met in the constraints.
  - **company_id**: Unique identifier for each company.
  - **required_space**: Minimum space required by the company.
  
- **available_space**: This table contains the total available space in each building. It is used to ensure that the total space allocated in each building does not exceed its capacity in the constraints.
  - **building_id**: Unique identifier for each building.
  - **available_space**: Total available space in the building.
  
- **space_allocated**: This table contains the amount of space allocated to each company in each building. It represents the decision variables in the optimization problem.
  - **building_id**: Unique identifier for each building.
  - **company_id**: Unique identifier for each company.
  - **space_allocated**: Amount of space allocated to the company in the building.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic business scenarios, considering typical office space costs, company space requirements, and building capacities. Data was generated to ensure a balanced optimization problem with feasible constraints.

-- Realistic data for cost_per_sqft
INSERT INTO cost_per_sqft (building_id, cost_per_sqft) VALUES (1, 55.0);
INSERT INTO cost_per_sqft (building_id, cost_per_sqft) VALUES (2, 65.0);
INSERT INTO cost_per_sqft (building_id, cost_per_sqft) VALUES (3, 45.0);

-- Realistic data for required_space
INSERT INTO required_space (company_id, required_space) VALUES (1, 1200);
INSERT INTO required_space (company_id, required_space) VALUES (2, 1800);
INSERT INTO required_space (company_id, required_space) VALUES (3, 900);

-- Realistic data for available_space
INSERT INTO available_space (building_id, available_space) VALUES (1, 6000);
INSERT INTO available_space (building_id, available_space) VALUES (2, 7000);
INSERT INTO available_space (building_id, available_space) VALUES (3, 5000);

-- Realistic data for space_allocated
INSERT INTO space_allocated (building_id, company_id, space_allocated) VALUES (1, 1, 600.0);
INSERT INTO space_allocated (building_id, company_id, space_allocated) VALUES (1, 2, 800.0);
INSERT INTO space_allocated (building_id, company_id, space_allocated) VALUES (2, 3, 900.0);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
Let \( x_{ij} \) be the amount of space allocated to company \( i \) in building \( j \), where:
- \( i \in \{1, 2, 3\} \) (company IDs)
- \( j \in \{1, 2, 3\} \) (building IDs)

#### Objective Function
Minimize the total leasing cost:
\[
\text{Minimize } Z = 55x_{11} + 55x_{21} + 55x_{31} + 65x_{12} + 65x_{22} + 65x_{32} + 45x_{13} + 45x_{23} + 45x_{33}
\]

#### Constraints
1. **Minimum Space Requirement**:
   \[
   x_{11} + x_{12} + x_{13} \geq 1200 \quad \text{(Company 1)}
   \]
   \[
   x_{21} + x_{22} + x_{23} \geq 1800 \quad \text{(Company 2)}
   \]
   \[
   x_{31} + x_{32} + x_{33} \geq 900 \quad \text{(Company 3)}
   \]

2. **Building Capacity**:
   \[
   x_{11} + x_{21} + x_{31} \leq 6000 \quad \text{(Building 1)}
   \]
   \[
   x_{12} + x_{22} + x_{32} \leq 7000 \quad \text{(Building 2)}
   \]
   \[
   x_{13} + x_{23} + x_{33} \leq 5000 \quad \text{(Building 3)}
   \]

3. **Non-Negative Allocation**:
   \[
   x_{ij} \geq 0 \quad \forall i, j
   \]

#### Data Source Verification
- **Objective Function Coefficients**: `cost_per_sqft.cost_per_sqft`
  - Building 1: 55.0
  - Building 2: 65.0
  - Building 3: 45.0
- **Minimum Space Requirement Constants**: `required_space.required_space`
  - Company 1: 1200
  - Company 2: 1800
  - Company 3: 900
- **Building Capacity Constants**: `available_space.available_space`
  - Building 1: 6000
  - Building 2: 7000
  - Building 3: 5000

This formulation provides a complete, immediately solvable LINEAR mathematical model with all numerical coefficients derived from the provided data.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Office Space Allocation Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def optimize_office_allocation():
    """Optimize the allocation of office spaces across multiple buildings to minimize total leasing cost."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("office_allocation")
    
    # Data from the problem
    buildings = [1, 2, 3]
    companies = [1, 2, 3]
    
    cost_per_sqft = {
        1: 55.0,
        2: 65.0,
        3: 45.0
    }
    
    required_space = {
        1: 1200,
        2: 1800,
        3: 900
    }
    
    available_space = {
        1: 6000,
        2: 7000,
        3: 5000
    }
    
    # CRITICAL: Validate array lengths before loops
    assert len(buildings) == len(cost_per_sqft) == len(available_space), "Building data length mismatch"
    assert len(companies) == len(required_space), "Company data length mismatch"
    
    # 2. VARIABLES
    # Decision variables: x_ij = space allocated to company i in building j
    x = model.addVars(companies, buildings, vtype=GRB.CONTINUOUS, name="x", lb=0)
    
    # 3. OBJECTIVE FUNCTION
    # Minimize total leasing cost
    model.setObjective(
        gp.quicksum(cost_per_sqft[j] * x[i, j] for i in companies for j in buildings),
        GRB.MINIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Minimum Space Requirement for each company
    for i in companies:
        model.addConstr(
            gp.quicksum(x[i, j] for j in buildings) >= required_space[i],
            name=f"min_space_company_{i}"
        )
    
    # Building Capacity for each building
    for j in buildings:
        model.addConstr(
            gp.quicksum(x[i, j] for i in companies) <= available_space[j],
            name=f"capacity_building_{j}"
        )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in companies:
            for j in buildings:
                if x[i, j].x > 1e-6:
                    print(f"Space allocated to company {i} in building {j}: {x[i, j].x:.3f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

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

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 175500.0
**Execution Time**: 0.19 seconds
**Reliability**: high
**Analysis**: Gurobipy found the optimal solution quickly with the lowest execution time.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCplex 2.29.245 Implementation for Office Space Allocation Optimization
"""

from docplex.mp.model import Model

def optimize_office_allocation():
    """Optimize the allocation of office spaces across multiple buildings to minimize total leasing cost."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="office_space_allocation")
    
    # Data from the problem
    buildings = [1, 2, 3]
    companies = [1, 2, 3]
    
    cost_per_sqft = {
        1: 55.0,
        2: 65.0,
        3: 45.0
    }
    
    required_space = {
        1: 1200,
        2: 1800,
        3: 900
    }
    
    available_space = {
        1: 6000,
        2: 7000,
        3: 5000
    }
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(buildings) == len(cost_per_sqft) == len(available_space), "Building data length mismatch"
    assert len(companies) == len(required_space), "Company data length mismatch"
    
    # 2. VARIABLES
    # Decision variables: x_{ij} = space allocated to company i in building j
    x = {(i, j): mdl.continuous_var(name=f"x_{i}_{j}", lb=0) for i in companies for j in buildings}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize total leasing cost
    objective = mdl.sum(cost_per_sqft[j] * x[(i, j)] for i in companies for j in buildings)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Minimum Space Requirement for each company
    for i in companies:
        total_space = mdl.sum(x[(i, j)] for j in buildings)
        mdl.add_constraint(total_space >= required_space[i], ctname=f"min_space_{i}")
    
    # Building Capacity for each building
    for j in buildings:
        total_allocated = mdl.sum(x[(i, j)] for i in companies)
        mdl.add_constraint(total_allocated <= available_space[j], ctname=f"capacity_{j}")
    
    # Non-Negative Allocation is already handled by lb=0 in variable definition
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in companies:
            for j in buildings:
                value = solution.get_value(x[(i, j)])
                if value > 1e-6:
                    print(f"Space allocated to Company {i} in Building {j}: {value:.3f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

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

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 175500.0
**Execution Time**: 1.16 seconds
**Reliability**: high
**Analysis**: DOCplex also found the optimal solution but took longer compared to Gurobipy.

## 7. Pyomo Implementation

```python
#!/usr/bin/env python3
"""
Pyomo 6.9.2 Implementation for Office Space Allocation Optimization
"""

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

def optimize_office_allocation():
    """Optimize the allocation of office spaces across multiple buildings to minimize total leasing cost."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Define sets for companies and buildings
    companies = [1, 2, 3]
    buildings = [1, 2, 3]
    
    # Cost per square foot for each building
    cost_per_sqft = {
        1: 55.0,
        2: 65.0,
        3: 45.0
    }
    
    # Minimum space required by each company
    required_space = {
        1: 1200,
        2: 1800,
        3: 900
    }
    
    # Total available space in each building
    available_space = {
        1: 6000,
        2: 7000,
        3: 5000
    }
    
    # CRITICAL: Validate array lengths before indexing
    assert len(cost_per_sqft) == len(buildings), "Cost per sqft data length mismatch"
    assert len(required_space) == len(companies), "Required space data length mismatch"
    assert len(available_space) == len(buildings), "Available space data length mismatch"
    
    # 3. SETS
    model.companies = pyo.Set(initialize=companies)
    model.buildings = pyo.Set(initialize=buildings)
    
    # 4. PARAMETERS
    model.cost_per_sqft = pyo.Param(model.buildings, initialize=cost_per_sqft)
    model.required_space = pyo.Param(model.companies, initialize=required_space)
    model.available_space = pyo.Param(model.buildings, initialize=available_space)
    
    # 5. VARIABLES
    model.space_allocated = pyo.Var(model.companies, model.buildings, within=pyo.NonNegativeReals)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.cost_per_sqft[j] * model.space_allocated[i, j] for i in model.companies for j in model.buildings)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Minimum Space Requirement
    def min_space_rule(model, i):
        return sum(model.space_allocated[i, j] for j in model.buildings) >= model.required_space[i]
    model.min_space_constraint = pyo.Constraint(model.companies, rule=min_space_rule)
    
    # Building Capacity
    def building_capacity_rule(model, j):
        return sum(model.space_allocated[i, j] for i in model.companies) <= model.available_space[j]
    model.building_capacity_constraint = pyo.Constraint(model.buildings, rule=building_capacity_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("\nSpace Allocated:")
        for i in model.companies:
            for j in model.buildings:
                allocated = pyo.value(model.space_allocated[i, j])
                if allocated > 1e-6:  # Only print non-zero values
                    print(f"Company {i} in Building {j}: {allocated:.2f} sqft")
        
    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__":
    optimize_office_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 175500.0
**Execution Time**: 0.93 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution with an execution time between Gurobipy and DOCplex.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 175500.00 | 0.19s | N/A | N/A |
| Docplex | OPTIMAL | 175500.00 | 1.16s | N/A | N/A |
| Pyomo | OPTIMAL | 175500.00 | 0.93s | N/A | N/A |

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

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

### Business Interpretation
**Overall Strategy**: The optimal total leasing cost is $175,500, which is the minimum cost achievable given the constraints.
**Objective Value Meaning**: The optimal total leasing cost of $175,500 ensures that all companies' space requirements are met while minimizing the cost.
**Resource Allocation Summary**: Allocate space to companies in buildings such that the total cost is minimized while meeting all constraints.
**Implementation Recommendations**: Implement the space allocation as per the optimal solution. Ensure that the allocations do not exceed building capacities and meet the minimum space requirements for each company.