# Complete Optimization Problem and Solution: city_record

## 1. Problem Context and Goals

### Context  
A sports organization is tasked with selecting the optimal set of host cities for a series of matches in a given year. The decision-making process is guided by a set of key factors: the regional population of each city, its GDP, and the average temperature during the match month. These factors are weighted to reflect their relative importance in determining the overall benefit of selecting a city as a host. The weights are as follows: 40% for population, 30% for GDP, and 30% for temperature. The organization must also adhere to several operational constraints: the total number of matches to be hosted is capped at 10, the total budget for hosting cannot exceed $1,000,000, and no single city can host more than 2 matches. The goal is to maximize the overall benefit of the selected host cities while respecting these constraints.

### Goals  
The primary goal of this optimization problem is to maximize the total benefit derived from selecting host cities for the matches. This benefit is calculated as a weighted sum of each city's regional population, GDP, and average temperature during the match month. The weights assigned to these factors ensure that the selection process aligns with the organization's priorities. Success is measured by the ability to select a set of cities that collectively provide the highest possible benefit without exceeding the total number of matches, the budget, or the maximum number of matches per city.

## 2. Constraints    

The optimization process must respect the following constraints:  
1. **Total Matches Constraint**: The total number of matches hosted across all selected cities must not exceed 10.  
2. **Budget Constraint**: The total cost of hosting matches in the selected cities must not exceed the allocated budget of $1,000,000.  
3. **Maximum Matches Per City Constraint**: No single city can host more than 2 matches.  

These constraints ensure that the selection process remains feasible and aligns with the organization's operational limitations.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for city data, match constraints, and objective coefficients. Configuration logic updates include scalar parameters for weights and constraints, and formulas for benefit calculation.

CREATE TABLE city_data (
  city_id INTEGER,
  population INTEGER,
  gdp FLOAT,
  avg_temperature FLOAT,
  hosting_cost FLOAT
);

CREATE TABLE match_constraints (
  constraint_id INTEGER,
  max_matches_per_city INTEGER,
  total_budget FLOAT
);

CREATE TABLE objective_coefficients (
  coefficient_id INTEGER,
  weight_population FLOAT,
  weight_gdp FLOAT,
  weight_temperature FLOAT
);
```

### Data Dictionary  
- **city_data**: Contains information about potential host cities, including their unique identifier, regional population, GDP, average temperature during the match month, and the cost of hosting a match in the city.  
  - **city_id**: Unique identifier for each city.  
  - **population**: Regional population of the city, used as a component in the benefit calculation.  
  - **gdp**: GDP of the city, used as a component in the benefit calculation.  
  - **avg_temperature**: Average temperature during the match month, used as a component in the benefit calculation.  
  - **hosting_cost**: Cost of hosting a match in the city, used to enforce the budget constraint.  

- **match_constraints**: Stores operational constraints related to hosting matches, including the maximum number of matches per city and the total budget.  
  - **constraint_id**: Unique identifier for each constraint.  
  - **max_matches_per_city**: Maximum number of matches that can be hosted in a single city.  
  - **total_budget**: Total budget allocated for hosting matches across all cities.  

- **objective_coefficients**: Contains the weights assigned to each factor in the benefit calculation.  
  - **coefficient_id**: Unique identifier for each coefficient.  
  - **weight_population**: Weight assigned to the regional population in the benefit calculation (40%).  
  - **weight_gdp**: Weight assigned to the GDP in the benefit calculation (30%).  
  - **weight_temperature**: Weight assigned to the average temperature in the benefit calculation (30%).  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic city demographics, economic data, and climate conditions, ensuring they align with the optimization problem's requirements and constraints.

-- Realistic data for city_data
INSERT INTO city_data (city_id, population, gdp, avg_temperature, hosting_cost) VALUES (1, 800000, 75000.0, 22.0, 120000.0);
INSERT INTO city_data (city_id, population, gdp, avg_temperature, hosting_cost) VALUES (2, 1200000, 110000.0, 26.0, 180000.0);
INSERT INTO city_data (city_id, population, gdp, avg_temperature, hosting_cost) VALUES (3, 600000, 60000.0, 20.0, 100000.0);

-- Realistic data for match_constraints
INSERT INTO match_constraints (constraint_id, max_matches_per_city, total_budget) VALUES (1, 2, 1000000.0);
INSERT INTO match_constraints (constraint_id, max_matches_per_city, total_budget) VALUES (2, 2, 1000000.0);
INSERT INTO match_constraints (constraint_id, max_matches_per_city, total_budget) VALUES (3, 2, 1000000.0);

-- Realistic data for objective_coefficients
INSERT INTO objective_coefficients (coefficient_id, weight_population, weight_gdp, weight_temperature) VALUES (1, 0.4, 0.3, 0.3);
INSERT INTO objective_coefficients (coefficient_id, weight_population, weight_gdp, weight_temperature) VALUES (2, 0.4, 0.3, 0.3);
INSERT INTO objective_coefficients (coefficient_id, weight_population, weight_gdp, weight_temperature) VALUES (3, 0.4, 0.3, 0.3);
```