# Complete Optimization Problem and Solution: medicine_enzyme_interaction

## 1. Problem Context and Goals

### Context  
A pharmaceutical company is preparing for a clinical trial and needs to select a combination of medicines that maximizes the overall effectiveness of the treatment while ensuring patient safety and managing trial complexity. Each medicine has a known effectiveness score, which quantifies its potential benefit, and an adverse interaction score, which measures its potential risks when combined with other medicines. The company must decide which medicines to include in the trial, with the decision for each medicine being binary—either it is selected or it is not.

The selection process is guided by two critical operational parameters:  
1. **Maximum Allowed Total Adverse Interaction Score**: The total adverse interaction score of all selected medicines must not exceed 0.5, ensuring patient safety.  
2. **Maximum Number of Medicines That Can Be Selected**: No more than 3 medicines can be included in the trial to manage complexity and resource allocation.  

Additionally, only medicines that are FDA-approved are eligible for selection. The company aims to make these decisions in a way that maximizes the total effectiveness score of the selected medicines while adhering to these constraints.

### Goals  
The primary goal of this optimization problem is to maximize the total effectiveness score of the medicines selected for the clinical trial. This is achieved by carefully choosing which medicines to include, ensuring that their combined effectiveness is as high as possible. Success is measured by the sum of the effectiveness scores of the selected medicines, with higher values indicating a more effective treatment combination. The optimization process ensures that this goal is achieved while respecting the constraints on adverse interactions, the number of medicines, and FDA approval status.

## 2. Constraints  

The selection of medicines for the clinical trial is subject to the following constraints:  
1. **Adverse Interaction Limit**: The total adverse interaction score of all selected medicines must not exceed the maximum allowed value of 0.5. This ensures that the combined risk of the selected medicines remains within safe limits.  
2. **Medicine Count Limit**: The total number of medicines selected for the trial must not exceed the maximum allowed value of 3. This constraint helps manage the complexity and resource requirements of the trial.  
3. **FDA Approval Requirement**: Only medicines that are FDA-approved can be selected for the trial. This ensures that all included medicines meet regulatory standards for safety and efficacy.  

These constraints ensure that the selected combination of medicines is both effective and safe, while also being practical to implement in a clinical trial setting.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding missing scalar parameters to business configuration logic and ensuring all optimization mappings are complete. No table modifications or deletions were necessary.

CREATE TABLE medicine_effectiveness (
  medicine_id INTEGER,
  effectiveness_score FLOAT
);

CREATE TABLE medicine_adverse_interaction (
  medicine_id INTEGER,
  adverse_interaction_score FLOAT
);

CREATE TABLE medicine (
  medicine_id INTEGER,
  FDA_approved BOOLEAN
);

CREATE TABLE medicine_selection (
  medicine_id INTEGER,
  is_selected BOOLEAN
);
```

### Data Dictionary  
- **medicine_effectiveness**:  
  - **Purpose**: Stores the effectiveness scores of medicines based on enzyme interactions.  
  - **Columns**:  
    - `medicine_id`: Unique identifier for each medicine.  
    - `effectiveness_score`: Quantifies the potential benefit of the medicine.  

- **medicine_adverse_interaction**:  
  - **Purpose**: Stores the adverse interaction scores of medicines based on enzyme interactions.  
  - **Columns**:  
    - `medicine_id`: Unique identifier for each medicine.  
    - `adverse_interaction_score`: Measures the potential risk of the medicine when combined with others.  

- **medicine**:  
  - **Purpose**: Lists all medicines along with their FDA approval status.  
  - **Columns**:  
    - `medicine_id`: Unique identifier for each medicine.  
    - `FDA_approved`: Indicates whether the medicine is approved by the FDA.  

- **medicine_selection**:  
  - **Purpose**: Tracks the selection status of each medicine for the clinical trial.  
  - **Columns**:  
    - `medicine_id`: Unique identifier for each medicine.  
    - `is_selected`: Indicates whether the medicine has been selected for the trial.  


### Retrieved Values

**Query 1: This is crucial for identifying eligible medicines and their respective scores for optimization.**

```sql
SELECT m.medicine_id, me.effectiveness_score, mai.adverse_interaction_score FROM medicine m JOIN medicine_effectiveness me ON m.medicine_id = me.medicine_id JOIN medicine_adverse_interaction mai ON m.medicine_id = mai.medicine_id WHERE m.FDA_approved = TRUE;
```

**Results (CSV format):**
```csv
medicine_id,effectiveness_score,adverse_interaction_score
1,0.85,0.15
3,0.9,0.1
```

**Query 2: This helps in understanding the risk associated with each medicine when combined with others.**

```sql
SELECT medicine_id, adverse_interaction_score FROM medicine_adverse_interaction;
```

**Results (CSV format):**
```csv
medicine_id,adverse_interaction_score
1,0.15
2,0.25
3,0.1
```

**Query 3: This is essential for maximizing the overall effectiveness of the selected medicines.**

```sql
SELECT medicine_id, effectiveness_score FROM medicine_effectiveness;
```

**Results (CSV format):**
```csv
medicine_id,effectiveness_score
1,0.85
2,0.75
3,0.9
```

**Query 4: This ensures that only medicines meeting regulatory standards are considered for selection.**

```sql
SELECT medicine_id FROM medicine WHERE FDA_approved = TRUE;
```

**Results (CSV format):**
```csv
medicine_id
1
3
```

**Query 5: This helps in understanding which medicines are already selected for the trial.**

```sql
SELECT medicine_id, is_selected FROM medicine_selection;
```

**Results (CSV format):**
```csv
medicine_id,is_selected
1,0
2,0
3,0
```

**Query 6: This provides a starting point for selecting the most effective medicines within the constraints.**

```sql
SELECT m.medicine_id, me.effectiveness_score FROM medicine m JOIN medicine_effectiveness me ON m.medicine_id = me.medicine_id WHERE m.FDA_approved = TRUE ORDER BY me.effectiveness_score DESC LIMIT 3;
```

**Results (CSV format):**
```csv
medicine_id,effectiveness_score
3,0.9
1,0.85
```

**Query 7: This ensures that the selected medicines do not exceed the maximum allowed adverse interaction score.**

```sql
SELECT m.medicine_id FROM medicine m JOIN medicine_effectiveness me ON m.medicine_id = me.medicine_id WHERE m.FDA_approved = TRUE ORDER BY me.effectiveness_score DESC LIMIT 3 ) AS top_medicines JOIN medicine_adverse_interaction mai ON top_medicines.medicine_id = mai.medicine_id;
```

**Error:** Execution failed on sql 'SELECT m.medicine_id FROM medicine m JOIN medicine_effectiveness me ON m.medicine_id = me.medicine_id WHERE m.FDA_approved = TRUE ORDER BY me.effectiveness_score DESC LIMIT 3 ) AS top_medicines JOIN medicine_adverse_interaction mai ON top_medicines.medicine_id = mai.medicine_id;': near ")": syntax error

**Query 8: This helps in evaluating different combinations to find the optimal set that maximizes effectiveness while staying within the adverse interaction limit.**

```sql
SELECT m1.medicine_id AS med1, m2.medicine_id AS med2, m3.medicine_id AS med3, (me1.effectiveness_score + me2.effectiveness_score + me3.effectiveness_score) AS total_effectiveness, (mai1.adverse_interaction_score + mai2.adverse_interaction_score + mai3.adverse_interaction_score) AS total_adverse_interaction FROM medicine m1 JOIN medicine_effectiveness me1 ON m1.medicine_id = me1.medicine_id JOIN medicine_adverse_interaction mai1 ON m1.medicine_id = mai1.medicine_id JOIN medicine m2 ON m2.medicine_id > m1.medicine_id JOIN medicine_effectiveness me2 ON m2.medicine_id = me2.medicine_id JOIN medicine_adverse_interaction mai2 ON m2.medicine_id = mai2.medicine_id JOIN medicine m3 ON m3.medicine_id > m2.medicine_id JOIN medicine_effectiveness me3 ON m3.medicine_id = me3.medicine_id JOIN medicine_adverse_interaction mai3 ON m3.medicine_id = mai3.medicine_id WHERE m1.FDA_approved = TRUE AND m2.FDA_approved = TRUE AND m3.FDA_approved = TRUE;
```

**Results (CSV format):**
```csv
med1,med2,med3,total_effectiveness,total_adverse_interaction
```

