# 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);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- \( x_{t,d} \): Binary decision variable indicating whether template \( t \) is used for document \( d \).  
  - \( x_{t,d} = 1 \) if template \( t \) is used for document \( d \), otherwise \( x_{t,d} = 0 \).  
  - \( t \in \{1, 2, 3\} \), \( d \in \{101, 102, 103\} \).

#### Objective Function
Minimize the total cost of document creation:  
\[
\text{Minimize } Z = \sum_{t} \sum_{d} c_t \cdot x_{t,d}
\]  
where \( c_t \) is the cost of template \( t \).  

#### Constraints
1. **Single Template per Document**: Each document must use exactly one template.  
\[
\sum_{t} x_{t,d} = 1 \quad \forall d
\]  
2. **Template Validity**: A template can only be used if it is valid.  
\[
x_{t,d} \leq v_t \quad \forall t, d
\]  
where \( v_t \) is a binary indicator of the validity of template \( t \).  

#### Data Source Verification
- \( c_t \): Cost of template \( t \) from `template_costs.cost`.  
- \( v_t \): Validity status of template \( t \) from `template_validity.is_valid` (1 if valid, 0 otherwise).  

#### Numerical Example
Using the provided data:  
- \( c_1 = 12.5 \), \( c_2 = 18.0 \), \( c_3 = 10.0 \).  
- \( v_1 = 1 \), \( v_2 = 0 \), \( v_3 = 1 \).  

The complete linear programming formulation is:  

**Objective Function**:  
\[
\text{Minimize } Z = 12.5x_{1,101} + 18.0x_{2,101} + 10.0x_{3,101} + 12.5x_{1,102} + 18.0x_{2,102} + 10.0x_{3,102} + 12.5x_{1,103} + 18.0x_{2,103} + 10.0x_{3,103}
\]  

**Constraints**:  
1. Single Template per Document:  
\[
x_{1,101} + x_{2,101} + x_{3,101} = 1  
x_{1,102} + x_{2,102} + x_{3,102} = 1  
x_{1,103} + x_{2,103} + x_{3,103} = 1  
\]  
2. Template Validity:  
\[
x_{1,101} \leq 1, \quad x_{2,101} \leq 0, \quad x_{3,101} \leq 1  
x_{1,102} \leq 1, \quad x_{2,102} \leq 0, \quad x_{3,102} \leq 1  
x_{1,103} \leq 1, \quad x_{2,103} \leq 0, \quad x_{3,103} \leq 1  
\]  

This is a complete, immediately solvable linear programming model.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Document Template Management Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def optimize_template_allocation():
    """Optimize the allocation of document templates to minimize total cost."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("template_allocation")
    
    # Data from the database
    template_ids = [1, 2, 3]
    document_ids = [101, 102, 103]
    
    # Template costs
    template_costs = {
        1: 12.5,
        2: 18.0,
        3: 10.0
    }
    
    # Template validity
    template_validity = {
        1: 1,
        2: 0,
        3: 1
    }
    
    # CRITICAL: Validate array lengths before loops
    assert len(template_ids) == len(template_costs) == len(template_validity), "Template data length mismatch"
    
    # 2. VARIABLES
    # Decision variables: x[t, d] = 1 if template t is used for document d, else 0
    x = model.addVars(template_ids, document_ids, vtype=GRB.BINARY, name="x")
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total cost of document creation
    model.setObjective(
        gp.quicksum(template_costs[t] * x[t, d] for t in template_ids for d in document_ids),
        GRB.MINIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Constraint 1: Single Template per Document
    for d in document_ids:
        model.addConstr(
            gp.quicksum(x[t, d] for t in template_ids) == 1,
            name=f"single_template_{d}"
        )
    
    # Constraint 2: Template Validity
    for t in template_ids:
        for d in document_ids:
            model.addConstr(
                x[t, d] <= template_validity[t],
                name=f"validity_{t}_{d}"
            )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for t in template_ids:
            for d in document_ids:
                if x[t, d].x > 0.5:
                    print(f"Template {t} is used for document {d}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
if __name__ == "__main__":
    optimize_template_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 30.0
**Execution Time**: 0.18 seconds
**Reliability**: high
**Analysis**: Gurobipy found the optimal solution quickly with the lowest execution time.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCplex 2.29.245 Implementation for Document Template Optimization
"""

from docplex.mp.model import Model

def optimize_template_allocation():
    """Optimize the allocation of document templates to minimize total cost."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="template_allocation")
    
    # Data from the problem
    template_ids = [1, 2, 3]
    document_ids = [101, 102, 103]
    
    # Costs and validity data
    template_costs = {1: 12.5, 2: 18.0, 3: 10.0}
    template_validity = {1: True, 2: False, 3: True}
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(template_ids) == len(template_costs) == len(template_validity), "Array length mismatch"
    
    # 2. VARIABLES
    # Decision variables: x[t, d] = 1 if template t is used for document d, else 0
    x = {(t, d): mdl.binary_var(name=f"x_{t}_{d}") for t in template_ids for d in document_ids}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total cost of document creation
    objective = mdl.sum(template_costs[t] * x[t, d] for t in template_ids for d in document_ids)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Constraint 1: Single Template per Document
    for d in document_ids:
        mdl.add_constraint(mdl.sum(x[t, d] for t in template_ids) == 1, ctname=f"single_template_{d}")
    
    # Constraint 2: Template Validity
    for t in template_ids:
        for d in document_ids:
            mdl.add_constraint(x[t, d] <= int(template_validity[t]), ctname=f"validity_{t}_{d}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for t in template_ids:
            for d in document_ids:
                if solution.get_value(x[t, d]) > 0:
                    print(f"Template {t} is used for document {d}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
if __name__ == "__main__":
    optimize_template_allocation()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 30.0
**Execution Time**: 0.96 seconds
**Reliability**: high
**Analysis**: DOCplex also found the optimal solution but took longer compared to Gurobipy.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

import pyomo.environ as pyo
from pyomo.opt import SolverFactory

def template_optimization():
    """Optimize template allocation for document creation."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    templates = [1, 2, 3]
    documents = [101, 102, 103]
    
    # Template costs
    template_costs = {1: 12.5, 2: 18.0, 3: 10.0}
    
    # Template validity
    template_validity = {1: 1, 2: 0, 3: 1}
    
    # CRITICAL: Validate array lengths before indexing
    assert len(template_costs) == len(template_validity) == len(templates), "Array length mismatch"
    
    # 3. SETS (Pyomo way to define indices)
    model.T = pyo.Set(initialize=templates)  # Set of templates
    model.D = pyo.Set(initialize=documents)  # Set of documents
    
    # 4. PARAMETERS (data containers)
    model.cost = pyo.Param(model.T, initialize=template_costs)
    model.valid = pyo.Param(model.T, initialize=template_validity)
    
    # 5. VARIABLES
    # Binary decision variable: x[t, d] = 1 if template t is used for document d
    model.x = pyo.Var(model.T, model.D, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    # Minimize the total cost of document creation
    def obj_rule(model):
        return sum(model.cost[t] * model.x[t, d] for t in model.T for d in model.D)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Constraint 1: Each document must use exactly one template
    def single_template_rule(model, d):
        return sum(model.x[t, d] for t in model.T) == 1
    model.single_template_constraint = pyo.Constraint(model.D, rule=single_template_rule)
    
    # Constraint 2: A template can only be used if it is valid
    def validity_rule(model, t, d):
        return model.x[t, d] <= model.valid[t]
    model.validity_constraint = pyo.Constraint(model.T, model.D, rule=validity_rule)
    
    # 8. SOLVING WITH GUROBI (your available solver)
    solver = SolverFactory('gurobi')
    
    # Optional: Set solver options
    solver.options['TimeLimit'] = 300  # 5 minutes
    solver.options['MIPGap'] = 0.01    # 1% gap
    
    # Solve the model
    results = solver.solve(model, tee=True)  # tee=True shows solver output
    
    # 9. RESULT PROCESSING
    # Check solver status
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nVariable values:")
        for t in model.T:
            for d in model.D:
                x_val = pyo.value(model.x[t, d])
                if x_val > 1e-6:  # Only print non-zero values
                    print(f"x[{t}, {d}] = {int(x_val)}")
        
    elif results.solver.termination_condition == pyo.TerminationCondition.infeasible:
        print("Problem is infeasible")
    elif results.solver.termination_condition == pyo.TerminationCondition.unbounded:
        print("Problem is unbounded")
    else:
        print(f"Solver terminated with condition: {results.solver.termination_condition}")
    
    return model

# Execute the optimization
if __name__ == "__main__":
    template_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 30.0
**Execution Time**: 0.84 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution with a reasonable execution time, though not as fast as Gurobipy.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 30.00 | 0.18s | N/A | N/A |
| Docplex | OPTIMAL | 30.00 | 0.96s | N/A | N/A |
| Pyomo | OPTIMAL | 30.00 | 0.84s | N/A | N/A |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Majority Vote Optimal Value**: 30.0

### Final Recommendation
**Recommended Optimal Value**: 30.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its faster execution time while still providing the same optimal solution as the other solvers.

### Optimal Decision Variables
- **x_1,101** = 0.000
  - *Business Meaning*: Template 1 is not used for document 101.
- **x_2,101** = 0.000
  - *Business Meaning*: Template 2 is not used for document 101.
- **x_3,101** = 1.000
  - *Business Meaning*: Template 3 is used for document 101.
- **x_1,102** = 0.000
  - *Business Meaning*: Template 1 is not used for document 102.
- **x_2,102** = 0.000
  - *Business Meaning*: Template 2 is not used for document 102.
- **x_3,102** = 1.000
  - *Business Meaning*: Template 3 is used for document 102.
- **x_1,103** = 0.000
  - *Business Meaning*: Template 1 is not used for document 103.
- **x_2,103** = 0.000
  - *Business Meaning*: Template 2 is not used for document 103.
- **x_3,103** = 1.000
  - *Business Meaning*: Template 3 is used for document 103.

### Business Interpretation
**Overall Strategy**: The optimal solution minimizes the total cost of document creation by using the cheapest valid template (Template 3) for all documents.
**Objective Value Meaning**: The total cost of document creation is minimized to $30.0 by using the cheapest valid template for all documents.
**Resource Allocation Summary**: All documents should be created using Template 3, as it is the cheapest valid option.
**Implementation Recommendations**: Ensure Template 3 is available and valid for all documents. Update the document creation process to use Template 3 exclusively to achieve cost savings.