# Complete Optimization Problem and Solution: cre_Doc_Template_Mgt

## 1. Problem Context and Goals

### Context  
A company manages a variety of document templates and needs to optimize the allocation of these templates to specific documents. The primary business decision involves determining whether a particular template should be used for a given document. This decision is represented by a binary choice: a template is either used or not used for a document. The company aims to minimize the total cost of document creation by selecting the most cost-effective templates for each document. Each document must use exactly one valid template, and the template chosen must meet specific content requirements, such as ensuring a minimum paragraph count. The cost of using each template is known and varies depending on the template. Additionally, some templates may be invalid for use, and this validity status is predefined. The optimization process ensures that only valid templates are considered for allocation.

### Goals  
The goal of this optimization problem is to minimize the total cost of document creation. This is achieved by selecting the most cost-effective templates for each document while ensuring that each document uses exactly one valid template. Success is measured by the total cost incurred, which is the sum of the costs of the templates selected for all documents. The optimization process ensures that the allocation of templates is both cost-efficient and compliant with the company’s operational requirements.

## 2. Constraints    

1. **Single Template per Document**: Each document must use exactly one template. This ensures that every document is assigned a template without exception.  
2. **Template Validity**: A template can only be used for a document if it is marked as valid. This ensures that only approved templates are considered for allocation.  

These constraints ensure that the template allocation process is both efficient and compliant with the company’s operational standards.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a new table for decision variables and updating the data dictionary. Business configuration logic remains unchanged as it already meets the requirements.

CREATE TABLE template_costs (
  template_id INTEGER,
  cost FLOAT
);

CREATE TABLE template_validity (
  template_id INTEGER,
  is_valid BOOLEAN
);

CREATE TABLE template_usage (
  template_id INTEGER,
  document_id INTEGER,
  use_template BOOLEAN
);
```

### Data Dictionary  
- **template_costs**: This table contains the cost associated with using each template.  
  - *template_id*: A unique identifier for each template.  
  - *cost*: The cost of using the template, which is used as a coefficient in the objective function.  

- **template_validity**: This table indicates whether a template is valid for use.  
  - *template_id*: A unique identifier for each template.  
  - *is_valid*: A boolean value indicating the validity status of the template, used as a constraint bound in the optimization model.  

- **template_usage**: This table represents the decision of whether a template is used for a specific document.  
  - *template_id*: A unique identifier for each template.  
  - *document_id*: A unique identifier for each document.  
  - *use_template*: A boolean value indicating whether the template is used for the document, serving as the decision variable in the optimization model.  


### Retrieved Values

**Query 1: Query Description: Retrieve the cost of each valid template. This is crucial for the objective function, which aims to minimize the total cost of document creation.**

```sql
SELECT tc.template_id, tc.cost FROM template_costs tc JOIN template_validity tv ON tc.template_id = tv.template_id WHERE tv.is_valid = TRUE;
```

**Results (CSV format):**
```csv
template_id,cost
1,12.5
3,10.0
```

**Query 2: Query Description: Retrieve the list of valid templates. This is important for ensuring that only valid templates are considered in the optimization process.**

```sql
SELECT template_id FROM template_validity WHERE is_valid = TRUE;
```

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

**Query 3: Query Description: Retrieve the current usage of templates across documents. This helps in understanding the existing allocation and can be used to validate constraints.**

```sql
SELECT tu.document_id, tu.template_id, tu.use_template FROM template_usage tu JOIN template_validity tv ON tu.template_id = tv.template_id WHERE tv.is_valid = TRUE;
```

**Results (CSV format):**
```csv
document_id,template_id,use_template
101,1,1
103,3,1
```

**Query 4: Query Description: Retrieve the total cost incurred by the current template usage. This provides a baseline for the optimization process.**

```sql
SELECT SUM(tc.cost) AS total_cost FROM template_usage tu JOIN template_costs tc ON tu.template_id = tc.template_id JOIN template_validity tv ON tu.template_id = tv.template_id WHERE tv.is_valid = TRUE AND tu.use_template = TRUE;
```

**Results (CSV format):**
```csv
total_cost
22.5
```

**Query 5: Query Description: Retrieve the number of documents each valid template is currently used for. This helps in understanding the distribution of template usage.**

```sql
SELECT tu.template_id, COUNT(tu.document_id) AS document_count FROM template_usage tu JOIN template_validity tv ON tu.template_id = tv.template_id WHERE tv.is_valid = TRUE AND tu.use_template = TRUE GROUP BY tu.template_id;
```

**Results (CSV format):**
```csv
template_id,document_count
1,1
3,1
```

**Query 6: Query Description: Retrieve the list of documents that are not currently assigned any valid template. This is important for ensuring that all documents are assigned a template.**

```sql
SELECT tu.document_id FROM template_usage tu JOIN template_validity tv ON tu.template_id = tv.template_id WHERE tv.is_valid = TRUE AND tu.use_template = TRUE ) assigned ON d.document_id = assigned.document_id WHERE assigned.document_id IS NULL;
```

**Error:** Execution failed on sql 'SELECT tu.document_id FROM template_usage tu JOIN template_validity tv ON tu.template_id = tv.template_id WHERE tv.is_valid = TRUE AND tu.use_template = TRUE ) assigned ON d.document_id = assigned.document_id WHERE assigned.document_id IS NULL;': near ")": syntax error

**Query 7: Query Description: Retrieve the list of templates that are not currently used for any document. This helps in identifying underutilized templates.**

```sql
SELECT tu.template_id FROM template_usage tu JOIN template_validity tv ON tu.template_id = tv.template_id WHERE tv.is_valid = TRUE AND tu.use_template = TRUE ) used ON tc.template_id = used.template_id WHERE used.template_id IS NULL AND tc.template_id IN (SELECT template_id FROM template_validity WHERE is_valid = TRUE);
```

**Error:** Execution failed on sql 'SELECT tu.template_id FROM template_usage tu JOIN template_validity tv ON tu.template_id = tv.template_id WHERE tv.is_valid = TRUE AND tu.use_template = TRUE ) used ON tc.template_id = used.template_id WHERE used.template_id IS NULL AND tc.template_id IN (SELECT template_id FROM template_validity WHERE is_valid = TRUE);': near ")": syntax error

