# Complete Optimization Problem and Solution: farm

## 1. Problem Context and Goals

### Context  
A farm competition organizer is tasked with selecting the best farms to participate in an event hosted by a city. The goal is to maximize the overall quality of the competition by inviting farms that contribute the most to the event's success. The quality of each farm is determined by a weighted sum of the animals it possesses, including horses, cattle, pigs, sheep, and goats. The weights assigned to each animal type reflect their relative importance in the competition.  

The organizer must decide which farms to invite, ensuring that the total number of invited farms does not exceed the city's capacity to host them. Additionally, the city has specific limits on the total number of each type of animal that can be accommodated during the event. These limits include the maximum number of horses, cattle, pigs, and sheep and goats.  

The decision to invite a farm is represented by a binary choice: a farm is either invited (1) or not invited (0). The quality score of each farm is calculated as a weighted sum of its animals, with weights assigned as follows: horses (30%), cattle (25%), pigs (20%), and sheep and goats (25%).  

The city's capacity constraints are defined by the following parameters:  
- The maximum number of farms that can be hosted is 10.  
- The maximum number of horses that can be accommodated is 50.  
- The maximum number of cattle that can be accommodated is 100.  
- The maximum number of pigs that can be accommodated is 200.  
- The maximum number of sheep and goats that can be accommodated is 150.  

These constraints ensure that the event remains manageable and aligns with the city's hosting capabilities.  

### Goals  
The primary goal of the optimization problem is to maximize the total quality score of the invited farms. This is achieved by selecting a combination of farms whose collective quality score is the highest possible, while respecting the city's capacity constraints.  

Success is measured by the total quality score of the invited farms, which is calculated as the sum of the quality scores of each invited farm. The quality score of each farm is derived from the weighted sum of its animals, ensuring that farms with a higher number of animals, particularly those with higher weights, contribute more to the overall score.  

## 2. Constraints  

The optimization problem must adhere to the following constraints:  
1. The total number of invited farms cannot exceed the city's maximum hosting capacity of 10 farms.  
2. The total number of horses from all invited farms cannot exceed the city's maximum capacity of 50 horses.  
3. The total number of cattle from all invited farms cannot exceed the city's maximum capacity of 100 cattle.  
4. The total number of pigs from all invited farms cannot exceed the city's maximum capacity of 200 pigs.  
5. The total number of sheep and goats from all invited farms cannot exceed the city's maximum capacity of 150 sheep and goats.  

These constraints ensure that the event remains within the city's logistical and operational limits, while still allowing for the selection of high-quality farms.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for farms, animals, and city capacities, and updating business configuration logic to handle scalar parameters and formulas for optimization.

CREATE TABLE farms (
  farm_id INTEGER,
  invite_status BOOLEAN
);

CREATE TABLE animals (
  farm_id INTEGER,
  horses INTEGER,
  cattle INTEGER,
  pigs INTEGER,
  sheep_and_goats INTEGER
);

CREATE TABLE city_capacities (
  max_farms INTEGER,
  max_horses INTEGER,
  max_cattle INTEGER,
  max_pigs INTEGER,
  max_sheep_goats INTEGER
);
```

### Data Dictionary  
- **farms**: Represents farms eligible for the competition.  
  - **farm_id**: Unique identifier for each farm.  
  - **invite_status**: Indicates whether the farm is invited to the competition (True/False).  

- **animals**: Represents the number of each type of animal on each farm.  
  - **farm_id**: Links to the farms table to identify which farm the animals belong to.  
  - **horses**: Number of horses on the farm.  
  - **cattle**: Number of cattle on the farm.  
  - **pigs**: Number of pigs on the farm.  
  - **sheep_and_goats**: Number of sheep and goats on the farm.  

- **city_capacities**: Represents the city's capacity constraints for hosting the competition.  
  - **max_farms**: Maximum number of farms the city can host.  
  - **max_horses**: Maximum number of horses the city can accommodate.  
  - **max_cattle**: Maximum number of cattle the city can accommodate.  
  - **max_pigs**: Maximum number of pigs the city can accommodate.  
  - **max_sheep_goats**: Maximum number of sheep and goats the city can accommodate.  


### Retrieved Values

**Query 1: Query Description: Retrieve the current invite status of all farms. This is important to know which farms are already invited and which are not, as it affects the optimization decision.**

```sql
SELECT farm_id, invite_status FROM farms;
```

**Results (CSV format):**
```csv
farm_id,invite_status
1,0
2,0
3,0
```

**Query 2: Query Description: Retrieve the number of each type of animal on each farm. This data is crucial for calculating the quality score of each farm, which is a weighted sum of the animals.**

```sql
SELECT farm_id, horses, cattle, pigs, sheep_and_goats FROM animals;
```

**Results (CSV format):**
```csv
farm_id,horses,cattle,pigs,sheep_and_goats
1,5,20,50,30
2,10,30,60,40
3,15,40,70,50
```

**Query 3: Query Description: Retrieve the city's capacity constraints. These constraints are essential for ensuring that the optimization solution does not exceed the city's hosting capabilities.**

```sql
SELECT max_farms, max_horses, max_cattle, max_pigs, max_sheep_goats FROM city_capacities;
```

**Results (CSV format):**
```csv
max_farms,max_horses,max_cattle,max_pigs,max_sheep_goats
10,50,100,200,150
```

**Query 4: Query Description: Calculate the quality score for each farm based on the weighted sum of its animals. The weights are: horses (30%), cattle (25%), pigs (20%), and sheep and goats (25%). This score is the primary metric to maximize in the optimization problem.**

```sql
SELECT farm_id, (horses * 0.30) + (cattle * 0.25) + (pigs * 0.20) + (sheep_and_goats * 0.25) AS quality_score FROM animals;
```

**Results (CSV format):**
```csv
farm_id,quality_score
1,24.0
2,32.5
3,41.0
```

**Query 5: Query Description: Retrieve the total number of each type of animal across all farms. This information is useful for understanding the overall distribution of animals and ensuring that the optimization solution respects the city's animal capacity constraints.**

```sql
SELECT SUM(horses) AS total_horses, SUM(cattle) AS total_cattle, SUM(pigs) AS total_pigs, SUM(sheep_and_goats) AS total_sheep_goats FROM animals;
```

**Results (CSV format):**
```csv
total_horses,total_cattle,total_pigs,total_sheep_goats
30,90,180,120
```

**Query 6: Query Description: Retrieve the total number of each type of animal from the currently invited farms. This helps in understanding the current usage of the city's animal capacity and can be used to adjust the optimization solution accordingly.**

```sql
SELECT SUM(a.horses) AS total_horses, SUM(a.cattle) AS total_cattle, SUM(a.pigs) AS total_pigs, SUM(a.sheep_and_goats) AS total_sheep_goats FROM animals a JOIN farms f ON a.farm_id = f.farm_id WHERE f.invite_status = TRUE;
```

**Results (CSV format):**
```csv
total_horses,total_cattle,total_pigs,total_sheep_goats
,,,
```

**Query 7: Query Description: Retrieve the farms with the highest quality scores. This can be useful for identifying the top candidates for invitation, which can be a starting point for the optimization process.**

```sql
SELECT farm_id, (horses * 0.30) + (cattle * 0.25) + (pigs * 0.20) + (sheep_and_goats * 0.25) AS quality_score FROM animals ORDER BY quality_score DESC;
```

**Results (CSV format):**
```csv
farm_id,quality_score
3,41.0
2,32.5
1,24.0
```

**Query 8: Query Description: Retrieve the farms that, if invited, would not exceed the city's animal capacity constraints. This query helps in identifying feasible candidates for invitation, ensuring that the optimization solution remains within the city's limits.**

```sql
SELECT a.farm_id FROM animals a JOIN city_capacities c ON (SELECT SUM(horses) FROM animals WHERE farm_id IN (SELECT farm_id FROM farms WHERE invite_status = TRUE)) + a.horses <= c.max_horses AND (SELECT SUM(cattle) FROM animals WHERE farm_id IN (SELECT farm_id FROM farms WHERE invite_status = TRUE)) + a.cattle <= c.max_cattle AND (SELECT SUM(pigs) FROM animals WHERE farm_id IN (SELECT farm_id FROM farms WHERE invite_status = TRUE)) + a.pigs <= c.max_pigs AND (SELECT SUM(sheep_and_goats) FROM animals WHERE farm_id IN (SELECT farm_id FROM farms WHERE invite_status = TRUE)) + a.sheep_and_goats <= c.max_sheep_goats;
```

**Results (CSV format):**
```csv
farm_id
```

**Query 9: Query Description: Retrieve the total number of currently invited farms. This is important to ensure that the optimization solution does not exceed the city's maximum hosting capacity of 10 farms.**

```sql
SELECT COUNT(*) AS total_invited_farms FROM farms WHERE invite_status = TRUE;
```

**Results (CSV format):**
```csv
total_invited_farms
0
```

