# Complete Optimization Problem and Solution: store_product

## 1. Problem Context and Goals

### Context  
A retail chain is focused on optimizing the allocation of products across its stores to maximize total sales revenue. The key decision involves determining the number of units of each product to allocate to each store. This decision must consider the sales potential of each product in each store, the total available units of each product, and the capacity of each store to handle inventory. Additionally, the allocation must adhere to business-defined thresholds: a minimum number of units must be allocated to ensure product presence in every store, and a maximum number of units must not be exceeded to prevent overstocking and maintain balanced inventory levels.

The sales potential of a product in a store represents the expected contribution of that product to total sales revenue. The total sales revenue is calculated by summing the product of the sales potential and the allocated units for each product-store combination. The business configuration includes a minimum allocation threshold of 10 units and a maximum allocation threshold of 100 units per product per store. These thresholds ensure operational efficiency and inventory balance across the retail chain.

### Goals  
The primary goal of this optimization is to maximize total sales revenue by strategically allocating products to stores. This is achieved by determining the optimal number of units of each product to allocate to each store, considering their sales potential, availability, and store capacities. Success is measured by the total sales revenue generated, which is directly influenced by the sales potential of each product in each store and the number of units allocated. The optimization ensures that the allocation respects store capacities, product availability, and the defined minimum and maximum allocation thresholds.

## 2. Constraints  

The optimization must adhere to the following constraints:  
1. **Store Capacity Constraint**: The total number of units allocated to a store across all products must not exceed the store's capacity. This ensures that the store can handle the allocated inventory without operational strain.  
2. **Product Availability Constraint**: The total number of units allocated for a product across all stores must not exceed the available units of that product. This ensures that the allocation does not exceed the available inventory.  
3. **Minimum Allocation Threshold**: The number of units allocated for a product to a store must be at least the minimum allocation threshold. This ensures a minimum presence of each product in every store.  
4. **Maximum Allocation Threshold**: The number of units allocated for a product to a store must not exceed the maximum allocation threshold. This prevents overstocking and ensures balanced inventory levels across stores.  

These constraints ensure that the allocation decisions are operationally feasible and aligned with business requirements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added allocation table for decision variables, updated business configuration logic with new scalar parameters, and ensured all mappings are complete.

CREATE TABLE sales_potential (
  store_id INTEGER,
  product_id INTEGER,
  sales_potential_value FLOAT
);

CREATE TABLE product_availability (
  product_id INTEGER,
  available_units INTEGER
);

CREATE TABLE store_capacity (
  store_id INTEGER,
  capacity INTEGER
);

CREATE TABLE allocation (
  store_id INTEGER,
  product_id INTEGER,
  allocation_value INTEGER
);
```

### Data Dictionary  
- **sales_potential**: Represents the expected sales contribution of a product in a specific store.  
  - *store_id*: Unique identifier for the store.  
  - *product_id*: Unique identifier for the product.  
  - *sales_potential_value*: The sales potential of the product in the store, used as a coefficient in the objective function.  

- **product_availability**: Represents the total available units of a product across all stores.  
  - *product_id*: Unique identifier for the product.  
  - *available_units*: The total available units of the product, used as an upper bound in the product availability constraint.  

- **store_capacity**: Represents the maximum number of units a store can handle.  
  - *store_id*: Unique identifier for the store.  
  - *capacity*: The maximum number of units the store can handle, used as an upper bound in the store capacity constraint.  

- **allocation**: Represents the number of units of a product allocated to a store.  
  - *store_id*: Unique identifier for the store.  
  - *product_id*: Unique identifier for the product.  
  - *allocation_value*: The number of units of the product allocated to the store, used as the decision variable in the optimization model.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical retail store capacities, product availability, and sales potential, ensuring realistic and meaningful data for optimization.

-- Realistic data for sales_potential
INSERT INTO sales_potential (store_id, product_id, sales_potential_value) VALUES (1, 101, 50.0);
INSERT INTO sales_potential (store_id, product_id, sales_potential_value) VALUES (1, 102, 75.0);
INSERT INTO sales_potential (store_id, product_id, sales_potential_value) VALUES (1, 103, 100.0);

-- Realistic data for product_availability
INSERT INTO product_availability (product_id, available_units) VALUES (101, 500);
INSERT INTO product_availability (product_id, available_units) VALUES (102, 750);
INSERT INTO product_availability (product_id, available_units) VALUES (103, 1000);

-- Realistic data for store_capacity
INSERT INTO store_capacity (store_id, capacity) VALUES (1, 1000);
INSERT INTO store_capacity (store_id, capacity) VALUES (2, 1500);
INSERT INTO store_capacity (store_id, capacity) VALUES (3, 2000);

-- Realistic data for allocation
INSERT INTO allocation (store_id, product_id, allocation_value) VALUES (1, 101, 50);
INSERT INTO allocation (store_id, product_id, allocation_value) VALUES (1, 102, 75);
INSERT INTO allocation (store_id, product_id, allocation_value) VALUES (1, 103, 100);
```