# Complete Optimization Problem and Solution: workshop_paper

## 1. Problem Context and Goals

### Context  
The business problem involves optimizing the selection of workshop submissions to maximize the overall quality of accepted papers while respecting the capacity constraints of each workshop. Each submission is evaluated and assigned a score that represents its quality. The decision to accept or reject a submission is binary, meaning a submission is either accepted (1) or rejected (0). The operational goal is to maximize the total quality score of all accepted submissions across all workshops. This is achieved by summing the scores of the accepted submissions, ensuring that the number of accepted submissions for each workshop does not exceed its predefined capacity. The business configuration includes a scalar parameter for the submission score, which is used as the coefficient in the objective function. The objective function is designed to maximize the total score of accepted submissions, aligning with the business goal of enhancing the overall quality of the workshop.

### Goals  
The primary goal of this optimization problem is to maximize the total quality score of the accepted workshop submissions. This is measured by summing the scores of all submissions that are accepted, ensuring that the selection process adheres to the capacity limits of each workshop. Success is determined by achieving the highest possible total score while respecting the workshop capacity constraints, thereby ensuring a high-quality and well-organized workshop.

## 2. Constraints    

The optimization problem must adhere to the following constraints:  
1. **Workshop Capacity Constraint**: The total number of accepted submissions for each workshop must not exceed the workshop's capacity. This ensures that the workshop does not become overcrowded and maintains a manageable number of presentations.  
2. **Binary Decision Constraint**: Each submission must be either accepted or rejected, represented by a binary decision variable. This ensures a clear and definitive selection process for each submission.  

These constraints are designed to ensure that the optimization problem remains linear and avoids any nonlinear relationships, such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added submission_scores table to address missing Scores[i] mapping. Updated business configuration logic with scalar parameters for submission scores and formulas for optimization objective.

CREATE TABLE workshop_capacity (
  workshop_id INTEGER,
  capacity INTEGER
);

CREATE TABLE submission_workshop_mapping (
  submission_id INTEGER,
  workshop_id INTEGER,
  accepted BOOLEAN
);

CREATE TABLE submission_scores (
  submission_id INTEGER,
  score FLOAT
);
```

### Data Dictionary  
- **workshop_capacity**:  
  - **business_purpose**: Defines the maximum number of submissions that can be accepted for each workshop.  
  - **optimization_role**: Provides the bounds for the workshop capacity constraints.  
  - **columns**:  
    - **workshop_id**: Unique identifier for the workshop, used to index the capacity constraint.  
    - **capacity**: Maximum number of submissions that can be accepted for the workshop, serving as the bound for the constraint.  

- **submission_workshop_mapping**:  
  - **business_purpose**: Maps each submission to a specific workshop.  
  - **optimization_role**: Contains the business data used to determine which submissions are assigned to which workshops.  
  - **columns**:  
    - **submission_id**: Unique identifier for the submission, used to index the decision variable.  
    - **workshop_id**: Unique identifier for the workshop, used to index the capacity constraint.  
    - **accepted**: Binary indicator of whether the submission is accepted, serving as the decision variable in the optimization model.  

- **submission_scores**:  
  - **business_purpose**: Stores the quality scores assigned to each submission.  
  - **optimization_role**: Provides the coefficients for the objective function.  
  - **columns**:  
    - **submission_id**: Unique identifier for the submission, used to index the score.  
    - **score**: Quality score of the submission, used as the coefficient in the objective function.  


### Retrieved Values

**Query 1: This data ensures that the number of accepted submissions does not exceed the workshop's capacity.**

```sql
SELECT workshop_id, capacity FROM workshop_capacity;
```

**Results (CSV format):**
```csv
workshop_id,capacity
1,12
2,15
3,10
```

**Query 2: This data is crucial for determining which submissions are assigned to which workshops and whether they are accepted or rejected.**

```sql
SELECT submission_id, workshop_id, accepted FROM submission_workshop_mapping;
```

**Results (CSV format):**
```csv
submission_id,workshop_id,accepted
1,1,0
2,2,0
3,3,0
```

**Query 3: This data is necessary to maximize the total quality score of accepted submissions.**

```sql
SELECT submission_id, score FROM submission_scores;
```

**Results (CSV format):**
```csv
submission_id,score
1,8.7
2,7.5
3,9.2
```

**Query 4: This aggregated data helps in verifying the workshop capacity constraint.**

```sql
SELECT workshop_id, COUNT(submission_id) AS accepted_submissions FROM submission_workshop_mapping WHERE accepted = TRUE GROUP BY workshop_id;
```

**Results (CSV format):**
```csv
workshop_id,accepted_submissions
```

**Query 5: This data helps in understanding the current quality score distribution across workshops and can be used to optimize the selection process.**

```sql
SELECT swm.workshop_id, SUM(ss.score) AS total_quality_score FROM submission_workshop_mapping swm JOIN submission_scores ss ON swm.submission_id = ss.submission_id WHERE swm.accepted = TRUE GROUP BY swm.workshop_id;
```

**Results (CSV format):**
```csv
workshop_id,total_quality_score
```

**Query 6: This data can be used to identify high-quality submissions that could potentially be accepted to improve the overall quality score.**

```sql
SELECT ss.submission_id, ss.score FROM submission_scores ss LEFT JOIN submission_workshop_mapping swm ON ss.submission_id = swm.submission_id WHERE swm.accepted IS NULL OR swm.accepted = FALSE;
```

**Results (CSV format):**
```csv
submission_id,score
1,8.7
2,7.5
3,9.2
```

**Query 7: This data is useful for comparing the current number of accepted submissions against the workshop's capacity.**

```sql
SELECT wc.workshop_id, wc.capacity, COUNT(swm.submission_id) AS accepted_submissions FROM workshop_capacity wc LEFT JOIN submission_workshop_mapping swm ON wc.workshop_id = swm.workshop_id AND swm.accepted = TRUE GROUP BY wc.workshop_id, wc.capacity;
```

**Results (CSV format):**
```csv
workshop_id,capacity,accepted_submissions
1,12,0
2,15,0
3,10,0
```

**Query 8: This comprehensive data is useful for understanding the relationship between submissions, workshops, and their quality scores.**

```sql
SELECT swm.submission_id, swm.workshop_id, swm.accepted, ss.score FROM submission_workshop_mapping swm JOIN submission_scores ss ON swm.submission_id = ss.submission_id;
```

**Results (CSV format):**
```csv
submission_id,workshop_id,accepted,score
1,1,0,8.7
2,2,0,7.5
3,3,0,9.2
```

