# 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 various buildings to enhance its market value. The decision-making process involves determining whether a company should be allocated space in a building, represented by a binary decision variable. The primary objective is to maximize the total market value by strategically assigning office spaces to companies. This decision is influenced by the market value contribution of each company and is subject to constraints such as the number of available stories in each building and a minimum sales performance threshold. The business configuration includes a minimum sales requirement, ensuring that only companies with significant sales performance are considered for allocation. This approach ensures that the allocation process is both efficient and aligned with the company's strategic goals.

### Goals  
The primary goal of this optimization problem is to maximize the total market value of the companies by effectively allocating office spaces in various buildings. The metric for optimization is the total market value, which is calculated based on the market value contribution of each company that is allocated space. Success in this context is measured by the ability to maximize this total market value while adhering to the constraints related to building capacities and sales performance. The optimization process is linear, focusing on maximizing the sum of market values associated with the allocated companies.

## 2. Constraints    

The allocation of office spaces is subject to several constraints that ensure the feasibility and strategic alignment of the decision-making process:

- The total number of companies allocated to a building must not exceed the number of available stories in that building. This constraint ensures that the physical capacity of each building is respected.
- The total sales performance of the companies allocated to office spaces must meet or exceed a predefined minimum sales requirement. This constraint ensures that only companies with a significant sales performance are considered, aligning with the company's strategic objectives.

These constraints are designed to naturally lead to linear mathematical formulations, ensuring that the optimization process remains efficient and effective.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables, modifying existing tables to fill mapping gaps, and moving scalar parameters to configuration logic. Business configuration logic updated to include minimum sales requirement as a scalar parameter.

CREATE TABLE Companies (
  Market_Value_billion FLOAT,
  Sales_billion FLOAT
);

CREATE TABLE buildings (
  Stories INTEGER
);

CREATE TABLE Office_Allocations (
  building_id INTEGER,
  company_id INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive overview of the tables and columns, detailing their business purposes and roles in the optimization process:

- **Companies Table**: This table stores information about each company, including their market value and sales performance. The market value is used as a coefficient in the objective function, while sales performance is used in sales-based constraints.
  - **Market_Value_billion**: Represents the market value contribution of each company, crucial for calculating the total market value.
  - **Sales_billion**: Indicates the sales performance of each company, used to ensure that only companies meeting the minimum sales requirement are considered.

- **Buildings Table**: This table contains information about the buildings, specifically the number of available stories. This data is used to ensure that the allocation does not exceed the building's capacity.
  - **Stories**: Represents the maximum number of stories available in each building, serving as a constraint in the allocation process.

- **Office_Allocations Table**: This table represents the allocation of office spaces to companies within buildings. It is central to the decision-making process, mapping the allocation decisions.
  - **building_id**: Identifies the building where the office space is allocated.
  - **company_id**: Identifies the company that is allocated office space.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical market values and sales figures for mid-sized companies and realistic building capacities in urban areas.

-- Realistic data for Companies
INSERT INTO Companies (Market_Value_billion, Sales_billion) VALUES (12.0, 6.5);
INSERT INTO Companies (Market_Value_billion, Sales_billion) VALUES (18.0, 8.0);
INSERT INTO Companies (Market_Value_billion, Sales_billion) VALUES (14.5, 7.0);

-- Realistic data for buildings
INSERT INTO buildings (Stories) VALUES (12);
INSERT INTO buildings (Stories) VALUES (18);
INSERT INTO buildings (Stories) VALUES (15);

-- Realistic data for Office_Allocations
INSERT INTO Office_Allocations (building_id, company_id) VALUES (1, 101);
INSERT INTO Office_Allocations (building_id, company_id) VALUES (2, 102);
INSERT INTO Office_Allocations (building_id, company_id) VALUES (3, 103);
```

## 4. Mathematical Optimization Formulation

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

#### Objective Function
Maximize the total market value of the allocated companies:

\[
\text{Maximize } \sum_{i} \sum_{j} \text{Market\_Value\_billion}_j \times x_{ij}
\]

Data Source Verification:
- Coefficient \(\text{Market\_Value\_billion}_j\) comes from the `Companies.Market_Value_billion` column.

#### Constraints
1. **Building Capacity Constraints**: The total number of companies allocated to a building must not exceed the number of available stories in that building.

\[
\sum_{j} x_{ij} \leq \text{Stories}_i \quad \forall i
\]

Data Source Verification:
- Coefficient \(\text{Stories}_i\) comes from the `buildings.Stories` column.

2. **Minimum Sales Performance Constraint**: The total sales performance of the companies allocated to office spaces must meet or exceed a predefined minimum sales requirement.

\[
\sum_{i} \sum_{j} \text{Sales\_billion}_j \times x_{ij} \geq \text{Minimum\_Sales\_Requirement}
\]

Data Source Verification:
- Coefficient \(\text{Sales\_billion}_j\) comes from the `Companies.Sales_billion` column.
- \(\text{Minimum\_Sales\_Requirement}\) is a scalar parameter from business configuration logic.

3. **Binary Constraints**: Ensure that the decision variables are binary.

\[
x_{ij} \in \{0, 1\} \quad \forall i, j
\]

This formulation provides a complete linear optimization model that can be solved using standard linear or mixed-integer programming solvers. The objective is to maximize the total market value by allocating office spaces to companies while respecting building capacities and ensuring a minimum level of sales performance.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_office_allocation():
    """Optimize office space allocation to maximize market value."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("company_office")
    
    # Data from the problem context
    market_values = [12.0, 18.0, 14.5]  # Market values in billions
    sales = [6.5, 8.0, 7.0]  # Sales in billions
    stories = [12, 18, 15]  # Number of stories in each building
    minimum_sales_requirement = 20.0  # Minimum sales requirement in billions
    
    n_companies = len(market_values)
    n_buildings = len(stories)
    
    # CRITICAL: Validate array lengths before loops
    assert len(market_values) == len(sales) == n_companies, "Company data length mismatch"
    assert len(stories) == n_buildings, "Building data length mismatch"
    
    # 2. VARIABLES
    # Decision variables: x[i, j] = 1 if company j is allocated to building i
    x = model.addVars(n_buildings, n_companies, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total market value
    model.setObjective(gp.quicksum(market_values[j] * x[i, j] for i in range(n_buildings) for j in range(n_companies)), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    
    # Building capacity constraints
    model.addConstrs((gp.quicksum(x[i, j] for j in range(n_companies)) <= stories[i] for i in range(n_buildings)), name="building_capacity")
    
    # Minimum sales performance constraint
    model.addConstr(gp.quicksum(sales[j] * x[i, j] for i in range(n_buildings) for j in range(n_companies)) >= minimum_sales_requirement, name="min_sales")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in range(n_buildings):
            for j in range(n_companies):
                if x[i, j].x > 1e-6:
                    print(f"Company {j} is allocated to Building {i}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_office_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 133.5
**Execution Time**: 0.19 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with a very short execution time, indicating efficient performance and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_office_allocation():
    """Optimize the allocation of office spaces to maximize market value."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="company_office")
    
    # Data from the problem context
    market_values = [12.0, 18.0, 14.5]  # Market values in billions
    sales = [6.5, 8.0, 7.0]  # Sales in billions
    stories = [12, 18, 15]  # Number of stories in each building
    minimum_sales_requirement = 20.0  # Minimum sales requirement in billions
    
    # Validate array lengths to prevent IndexError
    assert len(market_values) == len(sales), "Array length mismatch for companies"
    assert len(stories) > 0, "No buildings available"
    
    num_companies = len(market_values)
    num_buildings = len(stories)
    
    # Safe indexing range
    safe_range_companies = range(num_companies)
    safe_range_buildings = range(num_buildings)
    
    # 2. VARIABLES
    # Binary decision variables for allocation
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i in safe_range_buildings for j in safe_range_companies}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize the total market value of the allocated companies
    objective = mdl.sum(market_values[j] * x[i, j] for i in safe_range_buildings for j in safe_range_companies)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    
    # Building Capacity Constraints
    for i in safe_range_buildings:
        mdl.add_constraint(mdl.sum(x[i, j] for j in safe_range_companies) <= stories[i], ctname=f"capacity_{i}")
    
    # Minimum Sales Performance Constraint
    total_sales = mdl.sum(sales[j] * x[i, j] for i in safe_range_buildings for j in safe_range_companies)
    mdl.add_constraint(total_sales >= minimum_sales_requirement, ctname="min_sales")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range_buildings:
            for j in safe_range_companies:
                if solution.get_value(x[i, j]) > 0.5:  # Check if the company is allocated
                    print(f"Company {j} is allocated to Building {i}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Run the optimization
optimize_office_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 133.5
**Execution Time**: 5.93 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, but with a longer execution time compared to Gurobipy. Despite this, the result is consistent and reliable.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def company_office_optimization():
    """Optimize office space allocation to maximize market value"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Example data based on the problem context
    market_values = [12.0, 18.0, 14.5]  # Market values in billions
    sales = [6.5, 8.0, 7.0]  # Sales in billions
    stories = [12, 18, 15]  # Number of stories in each building
    minimum_sales_requirement = 20.0  # Minimum sales requirement in billions
    
    n_companies = len(market_values)
    n_buildings = len(stories)
    
    # CRITICAL: Validate array lengths before indexing
    assert len(market_values) == len(sales) == n_companies, "Array length mismatch for companies"
    assert len(stories) == n_buildings, "Array length mismatch for buildings"
    
    # 3. SETS
    model.I = pyo.RangeSet(1, n_buildings)  # Buildings
    model.J = pyo.RangeSet(1, n_companies)  # Companies
    
    # 4. PARAMETERS
    model.market_value = pyo.Param(model.J, initialize={j+1: market_values[j] for j in range(n_companies)})
    model.sales = pyo.Param(model.J, initialize={j+1: sales[j] for j in range(n_companies)})
    model.stories = pyo.Param(model.I, initialize={i+1: stories[i] for i in range(n_buildings)})
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, model.J, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.market_value[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
    
    # Building Capacity Constraints
    def capacity_rule(model, i):
        return sum(model.x[i, j] for j in model.J) <= model.stories[i]
    model.capacity_constraint = pyo.Constraint(model.I, rule=capacity_rule)
    
    # Minimum Sales Performance Constraint
    def sales_rule(model):
        return sum(model.sales[j] * model.x[i, j] for i in model.I for j in model.J) >= minimum_sales_requirement
    model.sales_constraint = pyo.Constraint(rule=sales_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("\nAllocation decisions:")
        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 allocations
                    print(f"Building {i} -> Company {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
company_office_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 133.5
**Execution Time**: 2.32 seconds
**Reliability**: high
**Analysis**: Pyomo achieved the optimal solution with a moderate execution time, demonstrating reliability and consistency with the other solvers.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 133.50 | 0.19s | N/A | N/A |
| Docplex | OPTIMAL | 133.50 | 5.93s | N/A | N/A |
| Pyomo | OPTIMAL | 133.50 | 2.32s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 133.5
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is preferred due to its fastest execution time and high reliability, making it suitable for scenarios requiring quick decision-making.

### Business Interpretation
**Overall Strategy**: The optimal allocation maximizes the market value of companies allocated to buildings while meeting all constraints.
**Objective Value Meaning**: The optimal objective value of 133.5 billion represents the maximum achievable market value from the allocation of companies to buildings.
**Resource Allocation Summary**: Resources should be allocated to maximize market value while adhering to building capacity and sales performance constraints.
**Implementation Recommendations**: Implement the solution by allocating companies to buildings as per the solver's decision variables, ensuring compliance with all constraints.