# Complete Optimization Problem and Solution: candidate_poll

## 1. Problem Context and Goals

### Context  
A political campaign is focused on maximizing the overall support rate for a candidate by strategically allocating resources to different poll sources. Each poll source has a unique effectiveness in converting unsure voters to supporters, which is influenced by the amount of money invested in that source. The campaign must decide how much to invest in each poll source to achieve the highest possible support rate, while adhering to the following operational parameters:  

- **Total Budget**: The campaign has a fixed budget of $100,000 available for investment across all poll sources. This ensures that the total investment does not exceed the available funds.  
- **Minimum Investment**: Each poll source must receive at least $1,000 in investment to ensure meaningful engagement and impact on support rates.  
- **Maximum Support Rate**: For each poll source, there is a maximum achievable support rate of 80%, reflecting practical limitations in voter conversion.  

The campaign’s decision-making process is guided by two key metrics for each poll source:  
- **Initial Support Rate**: The current level of support for the candidate in each poll source before any additional investment.  
- **Conversion Rate**: The rate at which unsure voters convert to supporters per unit of investment in each poll source.  

By leveraging these metrics, the campaign aims to allocate investments in a way that maximizes the overall support rate while staying within budget and meeting minimum investment requirements.  

### Goals  
The primary goal of this optimization problem is to maximize the total support rate for the candidate across all poll sources. This is achieved by strategically determining the amount of money to invest in each poll source, considering the initial support rate and the conversion rate of unsure voters to supporters. Success is measured by the ability to increase the overall support rate while adhering to the campaign’s budget and investment constraints.  

## 2. Constraints  

The campaign’s investment strategy is subject to the following constraints:  

1. **Budget Constraint**: The total amount of money invested across all poll sources must not exceed the campaign’s total budget of $100,000.  
2. **Minimum Investment Constraint**: Each poll source must receive at least $1,000 in investment to ensure meaningful engagement.  
3. **Maximum Support Rate Constraint**: For each poll source, the combined effect of the initial support rate and the additional support gained from investment must not exceed the maximum achievable support rate of 80%.  

These constraints ensure that the campaign’s investment strategy is both financially feasible and operationally effective.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization parameters and updating business configuration logic to handle scalar parameters and formulas.

CREATE TABLE poll_source_conversion_rates (
  Poll_Source_ID INTEGER,
  Conversion_rate FLOAT
);

CREATE TABLE candidate (
  Poll_Source_ID INTEGER,
  Support_rate FLOAT
);

CREATE TABLE poll_source_investments (
  Poll_Source_ID INTEGER,
  Investment FLOAT
);
```

### Data Dictionary  
The following tables and columns are used in this optimization problem:  

- **poll_source_conversion_rates**:  
  - **Poll_Source_ID**: Unique identifier for each poll source.  
  - **Conversion_rate**: The rate at which unsure voters convert to supporters per unit of investment in the poll source. This is a key coefficient in the optimization objective.  

- **candidate**:  
  - **Poll_Source_ID**: Unique identifier for each poll source.  
  - **Support_rate**: The initial support rate for the candidate in the poll source before any additional investment. This is a key coefficient in the optimization objective.  

- **poll_source_investments**:  
  - **Poll_Source_ID**: Unique identifier for each poll source.  
  - **Investment**: The amount of money invested in the poll source. This is the decision variable in the optimization model.  


### Retrieved Values

**Query 1: This data is crucial for the objective function, as it determines how much additional support can be gained from investments.**

```sql
SELECT c.Poll_Source_ID, c.Support_rate AS Initial_Support_Rate, pcr.Conversion_rate FROM candidate c JOIN poll_source_conversion_rates pcr ON c.Poll_Source_ID = pcr.Poll_Source_ID;
```

**Results (CSV format):**
```csv
Poll_Source_ID,Initial_Support_Rate,Conversion_rate
1,0.3,0.05
2,0.4,0.07
3,0.5,0.1
```

**Query 2: This data is important to understand the current allocation of resources and to ensure that the minimum investment constraint is met.**

```sql
SELECT Poll_Source_ID, Investment FROM poll_source_investments;
```

**Results (CSV format):**
```csv
Poll_Source_ID,Investment
1,1000.0
2,2000.0
3,3000.0
```

**Query 3: This is essential for the budget constraint to ensure that the total investment does not exceed the available budget.**

```sql
SELECT SUM(Investment) AS Total_Investment FROM poll_source_investments;
```

**Results (CSV format):**
```csv
Total_Investment
6000.0
```

**Query 4: Note: The maximum support rate is a fixed value of 80%, but if it were stored in the database, it would be retrieved here.**

```sql
SELECT Poll_Source_ID, 0.8 AS Max_Support_Rate FROM poll_source_conversion_rates;
```

**Results (CSV format):**
```csv
Poll_Source_ID,Max_Support_Rate
1,0.8
2,0.8
3,0.8
```

**Query 5: Note: The minimum investment is a fixed value of $1,000, but if it were stored in the database, it would be retrieved here.**

```sql
SELECT Poll_Source_ID, 1000 AS Min_Investment FROM poll_source_conversion_rates;
```

**Results (CSV format):**
```csv
Poll_Source_ID,Min_Investment
1,1000
2,1000
3,1000
```

**Query 6: Note: The total budget is a fixed value of $100,000, but if it were stored in the database, it would be retrieved here.**

```sql
SELECT 100000 AS Total_Budget;
```

**Results (CSV format):**
```csv
Total_Budget
100000
```

**Query 7: This comprehensive view is useful for understanding the current state and potential impact of investments.**

```sql
SELECT c.Poll_Source_ID, c.Support_rate AS Initial_Support_Rate, pcr.Conversion_rate, psi.Investment FROM candidate c JOIN poll_source_conversion_rates pcr ON c.Poll_Source_ID = pcr.Poll_Source_ID JOIN poll_source_investments psi ON c.Poll_Source_ID = psi.Poll_Source_ID;
```

**Results (CSV format):**
```csv
Poll_Source_ID,Initial_Support_Rate,Conversion_rate,Investment
1,0.3,0.05,1000.0
2,0.4,0.07,2000.0
3,0.5,0.1,3000.0
```

**Query 8: This helps in understanding the impact of current investments and identifying areas for improvement.**

```sql
SELECT c.Poll_Source_ID, c.Support_rate + (pcr.Conversion_rate * psi.Investment) AS Potential_Support_Rate FROM candidate c JOIN poll_source_conversion_rates pcr ON c.Poll_Source_ID = pcr.Poll_Source_ID JOIN poll_source_investments psi ON c.Poll_Source_ID = psi.Poll_Source_ID;
```

**Results (CSV format):**
```csv
Poll_Source_ID,Potential_Support_Rate
1,50.3
2,140.4
3,300.5
```

**Query 9: This is important for enforcing the maximum support rate constraint.**

```sql
SELECT c.Poll_Source_ID, c.Support_rate + (pcr.Conversion_rate * psi.Investment) AS Potential_Support_Rate FROM candidate c JOIN poll_source_conversion_rates pcr ON c.Poll_Source_ID = pcr.Poll_Source_ID JOIN poll_source_investments psi ON c.Poll_Source_ID = psi.Poll_Source_ID WHERE c.Support_rate + (pcr.Conversion_rate * psi.Investment) > 0.8;
```

**Results (CSV format):**
```csv
Poll_Source_ID,Potential_Support_Rate
1,50.3
2,140.4
3,300.5
```

**Query 10: This is important for enforcing the minimum investment constraint.**

```sql
SELECT Poll_Source_ID, Investment FROM poll_source_investments WHERE Investment < 1000;
```

**Results (CSV format):**
```csv
Poll_Source_ID,Investment
```

