# 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.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic business scenarios, ensuring that the cost of templates varies appropriately, validity statuses are logical, and template usage aligns with document requirements.

-- Realistic data for template_costs
INSERT INTO template_costs (template_id, cost) VALUES (1, 12.5);
INSERT INTO template_costs (template_id, cost) VALUES (2, 18.0);
INSERT INTO template_costs (template_id, cost) VALUES (3, 10.0);

-- Realistic data for template_validity
INSERT INTO template_validity (template_id, is_valid) VALUES (1, True);
INSERT INTO template_validity (template_id, is_valid) VALUES (2, False);
INSERT INTO template_validity (template_id, is_valid) VALUES (3, True);

-- Realistic data for template_usage
INSERT INTO template_usage (template_id, document_id, use_template) VALUES (1, 101, True);
INSERT INTO template_usage (template_id, document_id, use_template) VALUES (2, 102, False);
INSERT INTO template_usage (template_id, document_id, use_template) VALUES (3, 103, True);
```