# Complete Optimization Problem and Solution: phone_market

## 1. Problem Context and Goals

### Context  
A phone retailer is focused on optimizing the distribution of its phone stock across various markets to maximize revenue. The decision-making process involves determining the number of phones to allocate to each market, represented by the decision variable Num_of_stock[Market_ID, Phone_ID], which is an integer. The retailer aims to maximize revenue by considering the price of each phone model and the number of units allocated to each market. The operational parameters include a maximum stock limit that can be allocated to each shop, ensuring that stock levels remain manageable and do not exceed storage capacity. This constraint is crucial for maintaining efficient inventory management and is reflected in the business configuration as a scalar parameter. The retailer's strategy is to align stock distribution with market demand while adhering to these constraints, ensuring that the optimization process remains linear and straightforward.

### Goals  
The primary goal of the optimization process is to maximize the total revenue generated from phone sales. This is achieved by strategically distributing the available phone stock to various markets, taking into account the price of each phone model and the number of units allocated. The success of this optimization is measured by the total revenue, which is calculated as the sum of the product of the price of each phone model and the number of units allocated to each market. The optimization goal is clearly defined as maximizing this revenue metric, ensuring that the process remains linear and focused on achieving the highest possible financial return.

## 2. Constraints    

The optimization process is subject to several constraints that ensure the feasibility and efficiency of the stock distribution strategy. The first constraint ensures that the total number of phones allocated to all markets does not exceed the available stock for each phone model. This constraint is critical for maintaining inventory balance and preventing over-allocation. The second constraint limits the number of phones that can be allocated to each shop, ensuring that stock levels remain within manageable limits and do not exceed the maximum stock capacity defined in the business configuration. These constraints are expressed in linear terms, aligning with the retailer's operational requirements and ensuring that the optimization process remains straightforward and effective.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added a new table for phone prices to address missing objective coefficients. Updated existing tables to ensure all optimization requirements are met. Moved scalar parameters to configuration logic.

CREATE TABLE phone_market (
  Market_ID INTEGER,
  Phone_ID INTEGER,
  Num_of_stock INTEGER,
  available_stock INTEGER
);

CREATE TABLE phone_prices (
  Phone_ID INTEGER,
  Price FLOAT
);
```

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

- **phone_market**: This table links phone models to markets, detailing the stock allocation for each market. It plays a crucial role in defining the decision variables for the optimization process.
  - **Market_ID**: An integer identifier for each market, used to allocate stock to specific markets.
  - **Phone_ID**: An integer identifier for each phone model, used to allocate specific phone models.
  - **Num_of_stock**: An integer representing the number of phones allocated to a market, serving as the decision variable for stock allocation.
  - **available_stock**: An integer indicating the total available stock for each phone model, serving as a constraint bound for stock allocation.

- **phone_prices**: This table stores the price of each phone model, providing the coefficients for the revenue calculation in the optimization model.
  - **Phone_ID**: An integer identifier for each phone model, linking the price to specific phone models.
  - **Price**: A float representing the price of each phone model, used as a coefficient for revenue calculation.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical market sizes, phone pricing strategies, and stock management practices in the retail phone industry.

-- Realistic data for phone_market
INSERT INTO phone_market (Market_ID, Phone_ID, Num_of_stock, available_stock) VALUES (1, 101, 30, 150);
INSERT INTO phone_market (Market_ID, Phone_ID, Num_of_stock, available_stock) VALUES (2, 102, 50, 200);
INSERT INTO phone_market (Market_ID, Phone_ID, Num_of_stock, available_stock) VALUES (3, 103, 20, 100);

-- Realistic data for phone_prices
INSERT INTO phone_prices (Phone_ID, Price) VALUES (101, 299.99);
INSERT INTO phone_prices (Phone_ID, Price) VALUES (102, 399.99);
INSERT INTO phone_prices (Phone_ID, Price) VALUES (103, 499.99);
```