# Complete Optimization Problem and Solution: farm

## 1. Problem Context and Goals

### Context  
The farm is focused on optimizing its allocation of livestock to various competitions with the aim of maximizing the number of competitions won. This involves making strategic decisions about which competitions to participate in, represented by the decision variable: is_participating[Competition_ID, Farm_ID]. This binary variable indicates whether a farm is participating in a specific competition. The operational goal is to maximize the total number of competitions won, which is calculated by summing the product of win coefficients and participation indicators across all competitions and farms. The win coefficients are determined based on the importance of each competition, reflecting the potential impact of participation on winning. 

The farm operates under a constraint that limits the total number of livestock that can be allocated to competitions, ensuring that resources are not overextended. This constraint is defined by the livestock allocation threshold, a scalar parameter that represents the maximum capacity for livestock allocation. The business logic includes a formula for calculating win coefficients, which is based on the importance of each competition. This approach ensures that the optimization problem remains linear, avoiding any nonlinear relationships such as variable products or divisions.

### Goals  
The primary goal of the optimization is to maximize the total number of competitions won by strategically allocating livestock to competitions. This involves optimizing the metric of total competitions won, which is calculated by summing the products of win coefficients and participation indicators. Success is measured by the ability to maximize this metric while adhering to the livestock allocation constraints. The optimization goal is clearly defined in linear terms, focusing on maximizing the sum of weighted participation decisions.

## 2. Constraints    

The farm must adhere to a constraint that limits the total number of livestock allocated to competitions for each farm. This constraint ensures that the sum of participation decisions for each farm does not exceed the livestock allocation threshold. This threshold is a critical parameter that reflects the farm's capacity to allocate livestock without overextending its resources. The constraint is expressed in a linear form, ensuring that the optimization problem remains within the realm of linear programming.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and objective coefficients, modifying existing tables to fill mapping gaps, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE participation (
  Competition_ID INTEGER,
  Farm_ID INTEGER,
  is_participating BOOLEAN
);

CREATE TABLE win_coefficients (
  Competition_ID INTEGER,
  Farm_ID INTEGER,
  coefficient FLOAT
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles:

- **Participation Table**: This table tracks the participation of farms in various competitions. It includes:
  - **Competition_ID**: A unique identifier for each competition, linking participation to specific events.
  - **Farm_ID**: A unique identifier for each farm, linking participation to specific farms.
  - **is_participating**: A binary indicator showing whether a farm is participating in a competition, serving as the decision variable in the optimization.

- **Win Coefficients Table**: This table stores the coefficients that represent the impact of participation on winning competitions. It includes:
  - **Competition_ID**: A unique identifier for each competition, linking coefficients to specific events.
  - **Farm_ID**: A unique identifier for each farm, linking coefficients to specific farms.
  - **Coefficient**: A numerical value representing the win impact, used in the objective function to calculate potential wins.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical farm sizes and competition structures, ensuring that the number of livestock and win coefficients reflect realistic scenarios for farm competitions.

-- Realistic data for participation
INSERT INTO participation (Competition_ID, Farm_ID, is_participating) VALUES (1, 101, True);
INSERT INTO participation (Competition_ID, Farm_ID, is_participating) VALUES (2, 102, False);
INSERT INTO participation (Competition_ID, Farm_ID, is_participating) VALUES (3, 103, True);

-- Realistic data for win_coefficients
INSERT INTO win_coefficients (Competition_ID, Farm_ID, coefficient) VALUES (1, 101, 2.0);
INSERT INTO win_coefficients (Competition_ID, Farm_ID, coefficient) VALUES (2, 102, 1.5);
INSERT INTO win_coefficients (Competition_ID, Farm_ID, coefficient) VALUES (3, 103, 2.5);
INSERT INTO win_coefficients (Competition_ID, Farm_ID, coefficient) VALUES (1, 102, 1.2);
INSERT INTO win_coefficients (Competition_ID, Farm_ID, coefficient) VALUES (2, 103, 1.8);
```