# Complete Optimization Problem and Solution: gas_company

## 1. Problem Context and Goals

### Context  
A gas company is focused on optimizing the allocation of its gas stations to different companies to maximize overall profits. The company must decide which gas station should be assigned to which company, considering the potential profit each station can generate for a specific company. Each gas station can only be assigned to one company at a time, ensuring no overlaps in ownership. Additionally, the company must respect the maximum rank of each company, which limits the total number of gas stations a company can manage. The maximum rank is a predefined value that reflects the company's capacity to handle stations without overextending its resources. For example, a company with a maximum rank of 150 can manage up to 150 gas stations. This ensures that the allocation is both profitable and operationally feasible.

### Goals  
The primary goal is to maximize the total profit generated from the allocation of gas stations to companies. This is achieved by assigning each gas station to the company that yields the highest profit while ensuring that no station is assigned to more than one company and that no company exceeds its maximum rank. Success is measured by the total profit generated from the assignments, which is directly influenced by the profit values associated with each station-company pair.

## 2. Constraints    

1. **Single Assignment Constraint**: Each gas station can be assigned to at most one company. This ensures that no station is shared or duplicated across multiple companies, maintaining clear ownership and operational boundaries.  
2. **Maximum Rank Constraint**: The total number of gas stations assigned to a company cannot exceed its maximum rank. This ensures that companies do not overextend their capacity and can effectively manage the stations assigned to them.  

These constraints ensure that the allocation is both profitable and operationally feasible, aligning with the company's business objectives and resource limitations.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes and configuration logic updates implemented to address missing profit and rank data, ensuring complete optimization model mapping.

CREATE TABLE station_profit (
  station_id INTEGER,
  company_id INTEGER,
  profit FLOAT,
  assignment BOOLEAN
);

CREATE TABLE company_rank (
  company_id INTEGER,
  max_rank INTEGER
);
```

### Data Dictionary  
- **station_profit**: This table contains information about the profit generated by each gas station when assigned to a specific company.  
  - **station_id**: A unique identifier for each gas station.  
  - **company_id**: A unique identifier for each company.  
  - **profit**: The profit generated when a specific gas station is assigned to a specific company. This value is used as a coefficient in the optimization objective.  
  - **assignment**: A binary indicator that determines whether a gas station is assigned to a company. This is the decision variable in the optimization problem.  

- **company_rank**: This table defines the maximum rank allowed for each company, which limits the number of gas stations a company can manage.  
  - **company_id**: A unique identifier for each company.  
  - **max_rank**: The maximum number of gas stations a company can manage. This value is used as a constraint bound in the optimization problem.  


### Retrieved Values

**Query 1: This data is crucial for the objective function coefficients, as it represents the profit generated by assigning a specific station to a specific company.**

```sql
SELECT station_id, company_id, profit FROM station_profit;
```

**Results (CSV format):**
```csv
station_id,company_id,profit
1,1,5000.0
2,2,7500.0
3,3,10000.0
```

**Query 2: This data is essential for the maximum rank constraint, ensuring that no company exceeds its allowed number of stations.**

```sql
SELECT company_id, max_rank FROM company_rank;
```

**Results (CSV format):**
```csv
company_id,max_rank
1,100
2,150
3,200
```

**Query 3: This data helps in evaluating the current state and ensuring that the single assignment constraint is respected.**

```sql
SELECT station_id, company_id FROM station_profit WHERE assignment = TRUE;
```

**Results (CSV format):**
```csv
station_id,company_id
2,2
```

**Query 4: This aggregated data is important for verifying that the maximum rank constraint is not violated.**

```sql
SELECT company_id, COUNT(station_id) AS assigned_stations FROM station_profit WHERE assignment = TRUE GROUP BY company_id;
```

**Results (CSV format):**
```csv
company_id,assigned_stations
2,1
```

**Query 5: This data helps in identifying the most profitable assignments, which can be prioritized in the optimization process.**

```sql
SELECT station_id, company_id, profit FROM station_profit ORDER BY profit DESC;
```

**Results (CSV format):**
```csv
station_id,company_id,profit
3,3,10000.0
2,2,7500.0
1,1,5000.0
```

**Query 6: This data is important for ensuring that all stations are considered in the optimization process.**

```sql
SELECT station_id FROM station_profit WHERE assignment = FALSE;
```

**Results (CSV format):**
```csv
station_id
1
3
```

**Query 7: This data helps in identifying companies that can still accept more stations without violating the maximum rank constraint.**

```sql
SELECT company_id, COUNT(station_id) AS assigned_stations FROM station_profit WHERE assignment = TRUE GROUP BY company_id ) AS assigned ON cr.company_id = assigned.company_id WHERE assigned.assigned_stations IS NULL OR assigned.assigned_stations < cr.max_rank;
```

**Error:** Execution failed on sql 'SELECT company_id, COUNT(station_id) AS assigned_stations FROM station_profit WHERE assignment = TRUE GROUP BY company_id ) AS assigned ON cr.company_id = assigned.company_id WHERE assigned.assigned_stations IS NULL OR assigned.assigned_stations < cr.max_rank;': near ")": syntax error

**Query 8: This joined data is useful for evaluating both the profit potential and the capacity constraints simultaneously.**

```sql
SELECT sp.station_id, sp.company_id, sp.profit, cr.max_rank FROM station_profit sp JOIN company_rank cr ON sp.company_id = cr.company_id;
```

**Results (CSV format):**
```csv
station_id,company_id,profit,max_rank
1,1,5000.0,100
2,2,7500.0,150
3,3,10000.0,200
```

**Query 9: This aggregated data helps in understanding the current total profit, which can be used as a baseline for optimization.**

```sql
SELECT SUM(profit) AS total_profit FROM station_profit WHERE assignment = TRUE;
```

**Results (CSV format):**
```csv
total_profit
7500.0
```

**Query 10: This data provides a comprehensive view of each company's capacity and current utilization, which is crucial for ensuring that the maximum rank constraint is respected.**

```sql
SELECT cr.company_id, cr.max_rank, COUNT(sp.station_id) AS assigned_stations FROM company_rank cr LEFT JOIN station_profit sp ON cr.company_id = sp.company_id AND sp.assignment = TRUE GROUP BY cr.company_id, cr.max_rank;
```

**Results (CSV format):**
```csv
company_id,max_rank,assigned_stations
1,100,0
2,150,1
3,200,0
```

