# 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);
```