# Complete Optimization Problem and Solution: city_record

## 1. Problem Context and Goals

### Context  
The sports organization is tasked with strategically allocating matches to various cities to maximize the economic impact, specifically focusing on the GDP contribution of each city. The decision-making process involves determining whether a match is allocated to a city, represented by a binary decision variable. The primary objective is to maximize the total GDP impact, which is calculated by summing the GDP contributions of cities where matches are allocated. This allocation must consider the temperature suitability for each city during the month of the match, ensuring that matches are held in cities with temperatures within a specified range. The temperature range suitable for holding matches is defined as between 15 and 27 degrees Celsius, which is optimal for outdoor sports events. The GDP contribution per match is calculated based on the city's GDP and the importance of the match, ensuring that the allocation decisions align with the organization's economic goals.

### Goals  
The primary goal of this optimization problem is to maximize the total GDP impact from the allocation of matches to cities. This involves selecting cities for match allocation in a way that maximizes the sum of GDP contributions from these cities. The success of this optimization is measured by the total GDP impact achieved, ensuring that the allocation decisions are economically beneficial and align with the organization's strategic objectives.

## 2. Constraints    

The allocation of matches to cities is subject to several constraints to ensure feasibility and alignment with business requirements. Each match can be allocated to at most one city, ensuring that no match is scheduled in multiple locations simultaneously. Additionally, the temperature in the selected city must fall within the specified range of 15 to 27 degrees Celsius during the month of the match. This ensures that the matches are held in suitable weather conditions, maintaining the comfort and safety of participants and spectators.

## 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 include temperature suitability ranges, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE city (
  city_id INTEGER,
  GDP FLOAT
);

CREATE TABLE temperature (
  city_id INTEGER,
  month STRING,
  min_temp FLOAT,
  max_temp FLOAT
);

CREATE TABLE match_allocation (
  match_id INTEGER,
  city_id INTEGER,
  is_allocated BOOLEAN
);
```

### Data Dictionary  
The database schema is designed to support the optimization problem by storing relevant data in a structured format. The `city` table contains information about each city, including a unique identifier and the GDP contribution per match, which serves as a coefficient in the objective function. The `temperature` table records monthly temperature data for each city, providing the bounds for temperature constraints. The `match_allocation` table captures the decision variables, indicating whether a match is allocated to a particular city. Each table and column is mapped to its business purpose and optimization role, ensuring a clear connection to the linear mathematical formulation.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical GDP contributions of cities and realistic temperature ranges for sports events, ensuring a diverse set of cities and conditions.

-- Realistic data for city
INSERT INTO city (city_id, GDP) VALUES (1, 1500.0);
INSERT INTO city (city_id, GDP) VALUES (2, 2500.0);
INSERT INTO city (city_id, GDP) VALUES (3, 1800.0);

-- Realistic data for temperature
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (1, 'Jan', 16.0, 24.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (2, 'Jan', 18.0, 26.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (3, 'Jan', 15.0, 23.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (1, 'Feb', 17.0, 25.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (2, 'Feb', 19.0, 27.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (3, 'Feb', 16.0, 24.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (1, 'Mar', 18.0, 26.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (2, 'Mar', 20.0, 28.0);
INSERT INTO temperature (city_id, month, min_temp, max_temp) VALUES (3, 'Mar', 17.0, 25.0);

-- Realistic data for match_allocation
INSERT INTO match_allocation (match_id, city_id, is_allocated) VALUES (1, 1, True);
INSERT INTO match_allocation (match_id, city_id, is_allocated) VALUES (2, 2, True);
INSERT INTO match_allocation (match_id, city_id, is_allocated) VALUES (3, 3, False);
```