# Complete Optimization Problem and Solution: cre_Doc_Template_Mgt

## 1. Problem Context and Goals

### Context  
The company is focused on optimizing the assignment of templates to documents to minimize the total number of templates used, thereby reducing maintenance costs and ensuring document consistency. The decision-making process involves determining whether a template is used, represented by a binary variable for each template. Additionally, each document must be assigned to exactly one template, which is also represented by a binary variable indicating the assignment. The operational parameters are aligned with the objective of minimizing the total cost associated with using templates. This cost is a critical factor in refining the objective function, as it reflects the financial implications of template usage. The business configuration includes the cost associated with each template, which is used to refine the objective function by incorporating these costs. The data presented reflects current operational information, focusing on precise decision-making that leads to linear formulations. Resource limitations are considered to ensure that constraints are linear, avoiding scenarios that require nonlinear relationships. The business configuration parameters, including scalar parameters and business logic formulas, are integral to this process, ensuring consistency and flexibility in parameter tuning.

### Goals  
The primary goal of this optimization problem is to minimize the total cost of templates used across all documents. The metric to optimize is the sum of the costs associated with each template that is used. Success is measured by the ability to minimize this sum, aligning with the expected coefficient sources. The optimization goal is clearly defined in natural language, focusing on minimizing the total cost without using mathematical formulas or symbolic notation.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that each document is assigned exactly one template and that template usage is linked to document assignment. The first constraint ensures that each document is assigned to exactly one template, reflecting the business requirement that every document must have a template. The second constraint links template usage to document assignment, ensuring that a template is only considered used if it is assigned to at least one document. These constraints are described in business terms that naturally lead to linear mathematical forms, avoiding variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include adding a new table for template costs, modifying existing tables to include necessary fields, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE Templates (
  Template_ID INTEGER,
  Cost FLOAT
);

CREATE TABLE Documents (
  Document_ID INTEGER
);

CREATE TABLE Template_Costs (
  Template_ID INTEGER,
  Cost FLOAT
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles. The "Templates" table stores information about document templates, with each template having a unique identifier and an associated cost. This cost is used to refine the objective function in the optimization problem. The "Documents" table contains information about documents, with each document having a unique identifier. This table plays a crucial role in ensuring that each document is assigned a template. The "Template_Costs" table stores cost data for each template, linking the cost to the templates and playing a role in the objective function. This business-oriented data dictionary clearly connects to the expected linear mathematical formulation.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical business scenarios where template costs vary to reflect complexity and usage frequency. Document IDs were assigned sequentially to ensure unique identification.

-- Realistic data for Templates
INSERT INTO Templates (Template_ID, Cost) VALUES (1, 50.0);
INSERT INTO Templates (Template_ID, Cost) VALUES (2, 75.0);
INSERT INTO Templates (Template_ID, Cost) VALUES (3, 100.0);

-- Realistic data for Documents
INSERT INTO Documents (Document_ID) VALUES (101);
INSERT INTO Documents (Document_ID) VALUES (102);
INSERT INTO Documents (Document_ID) VALUES (103);

-- Realistic data for Template_Costs
INSERT INTO Template_Costs (Template_ID, Cost) VALUES (1, 50.0);
INSERT INTO Template_Costs (Template_ID, Cost) VALUES (2, 75.0);
INSERT INTO Template_Costs (Template_ID, Cost) VALUES (3, 100.0);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_{ij} \) be a binary variable that equals 1 if document \( i \) is assigned to template \( j \), and 0 otherwise.
- Let \( y_j \) be a binary variable that equals 1 if template \( j \) is used, and 0 otherwise.

#### Objective Function
Minimize the total cost of templates used:
\[
\text{Minimize } \sum_{j} \text{Cost}_j \times y_j
\]
where \(\text{Cost}_j\) is the cost of template \( j \) from the `Templates` table.

#### Constraints
1. **Each document is assigned to exactly one template:**
   \[
   \sum_{j} x_{ij} = 1 \quad \forall i \in \text{Documents}
   \]

2. **Link template usage to document assignment:**
   \[
   x_{ij} \leq y_j \quad \forall i \in \text{Documents}, \forall j \in \text{Templates}
   \]

3. **Binary constraints:**
   \[
   x_{ij} \in \{0, 1\} \quad \forall i \in \text{Documents}, \forall j \in \text{Templates}
   \]
   \[
   y_j \in \{0, 1\} \quad \forall j \in \text{Templates}
   \]

Data Source Verification:
- \(\text{Cost}_j\) is sourced from `Templates.Cost` for each template \( j \).
- Document indices \( i \) are sourced from `Documents.Document_ID`.
- Template indices \( j \) are sourced from `Templates.Template_ID`.

## 5. Gurobipy Implementation

```python
# Complete GUROBIPY implementation

import gurobipy as gp
from gurobipy import GRB

def optimize_template_assignment():
    """Optimize the assignment of templates to documents to minimize total cost."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("cre_Doc_Template_Mgt")
    
    # Data from the problem context
    template_ids = [1, 2, 3]
    document_ids = [101, 102, 103]
    costs = {1: 50.0, 2: 75.0, 3: 100.0}
    
    # Validate array lengths
    assert len(template_ids) == len(costs), "Array length mismatch for templates and costs"
    
    # 2. VARIABLES
    # Decision variables
    x = model.addVars(document_ids, template_ids, vtype=GRB.BINARY, name="x")
    y = model.addVars(template_ids, vtype=GRB.BINARY, name="y")
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total cost of templates used
    model.setObjective(gp.quicksum(costs[j] * y[j] for j in template_ids), GRB.MINIMIZE)
    
    # 4. CONSTRAINTS
    
    # Each document is assigned to exactly one template
    model.addConstrs((gp.quicksum(x[i, j] for j in template_ids) == 1 for i in document_ids), name="assign_one_template")
    
    # Link template usage to document assignment
    model.addConstrs((x[i, j] <= y[j] for i in document_ids for j in template_ids), name="link_usage")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in document_ids:
            for j in template_ids:
                if x[i, j].x > 1e-6:
                    print(f"Document {i} is assigned to Template {j}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_template_assignment()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 50.0
**Execution Time**: 0.19 seconds
**Reliability**: high
**Analysis**: Gurobipy found an optimal solution quickly with the lowest execution time, indicating efficient performance and reliability.

## 6. DOCplex Implementation

```python
# Complete DOCPLEX implementation

from docplex.mp.model import Model

def optimize_template_assignment():
    """Optimize the assignment of templates to documents to minimize total cost."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="TemplateAssignment")
    
    # Data from the problem context
    template_ids = [1, 2, 3]
    document_ids = [101, 102, 103]
    costs = [50.0, 75.0, 100.0]
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(template_ids) == len(costs), "Array length mismatch"
    safe_range_templates = range(len(template_ids))
    safe_range_documents = range(len(document_ids))
    
    # 2. VARIABLES
    # Binary variables for document-template assignment
    x = {(i, j): mdl.binary_var(name=f"x_{i}_{j}") for i in safe_range_documents for j in safe_range_templates}
    
    # Binary variables for template usage
    y = {j: mdl.binary_var(name=f"y_{j}") for j in safe_range_templates}
    
    # 3. OBJECTIVE FUNCTION
    # Minimize the total cost of templates used
    objective = mdl.sum(costs[j] * y[j] for j in safe_range_templates)
    mdl.minimize(objective)
    
    # 4. CONSTRAINTS
    
    # Constraint 1: Each document is assigned to exactly one template
    for i in safe_range_documents:
        mdl.add_constraint(mdl.sum(x[i, j] for j in safe_range_templates) == 1, ctname=f"assign_doc_{i}")
    
    # Constraint 2: Link template usage to document assignment
    for i in safe_range_documents:
        for j in safe_range_templates:
            mdl.add_constraint(x[i, j] <= y[j], ctname=f"link_{i}_{j}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for j in safe_range_templates:
            if solution.get_value(y[j]) > 0.5:  # Template is used
                print(f"Template {template_ids[j]} is used.")
        for i in safe_range_documents:
            for j in safe_range_templates:
                if solution.get_value(x[i, j]) > 0.5:
                    print(f"Document {document_ids[i]} is assigned to Template {template_ids[j]}.")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")

    return mdl

# Run the optimization
optimize_template_assignment()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 50.0
**Execution Time**: 5.70 seconds
**Reliability**: high
**Analysis**: DOCplex also found an optimal solution, but with a significantly longer execution time compared to Gurobipy, suggesting it is reliable but less efficient.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

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

def cre_Doc_Template_Mgt():
    """Optimization for minimizing template costs in document assignment"""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Define the data based on the provided database schema and current stored values
    template_ids = [1, 2, 3]
    document_ids = [101, 102, 103]
    costs = {1: 50.0, 2: 75.0, 3: 100.0}
    
    # Validate data lengths
    assert len(template_ids) == len(costs), "Mismatch in template data lengths"
    
    # 3. SETS
    model.Templates = pyo.Set(initialize=template_ids)
    model.Documents = pyo.Set(initialize=document_ids)
    
    # 4. PARAMETERS
    model.Cost = pyo.Param(model.Templates, initialize=costs)
    
    # 5. VARIABLES
    model.x = pyo.Var(model.Documents, model.Templates, within=pyo.Binary)
    model.y = pyo.Var(model.Templates, within=pyo.Binary)
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.Cost[j] * model.y[j] for j in model.Templates)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.minimize)
    
    # 7. CONSTRAINTS
    
    # Each document is assigned to exactly one template
    def assignment_rule(model, i):
        return sum(model.x[i, j] for j in model.Templates) == 1
    model.assignment_constraint = pyo.Constraint(model.Documents, rule=assignment_rule)
    
    # Link template usage to document assignment
    def linking_rule(model, i, j):
        return model.x[i, j] <= model.y[j]
    model.linking_constraint = pyo.Constraint(model.Documents, model.Templates, rule=linking_rule)
    
    # 8. SOLVING WITH GUROBI
    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 i in model.Documents:
            for j in model.Templates:
                x_val = pyo.value(model.x[i, j])
                if x_val > 1e-6:  # Only print non-zero values
                    print(f"x[{i},{j}] = {int(x_val)}")
        
        for j in model.Templates:
            y_val = pyo.value(model.y[j])
            print(f"y[{j}] = {int(y_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

# Run the optimization
cre_Doc_Template_Mgt()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 50.0
**Execution Time**: 2.52 seconds
**Reliability**: high
**Analysis**: Pyomo achieved an optimal solution with a moderate execution time, indicating good reliability and efficiency.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 50.00 | 0.19s | N/A | N/A |
| Docplex | OPTIMAL | 50.00 | 5.70s | N/A | N/A |
| Pyomo | OPTIMAL | 50.00 | 2.52s | N/A | N/A |

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

### Final Recommendation
**Recommended Optimal Value**: 50.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its fastest execution time and high reliability, making it the most efficient choice for this problem.

### Business Interpretation
**Overall Strategy**: The optimal solution minimizes the cost of templates used, ensuring cost-effective document management.
**Objective Value Meaning**: The optimal objective value of 50.0 represents the minimum total cost for using the necessary templates to cover all documents.
**Resource Allocation Summary**: Resources should be allocated to ensure each document is assigned to the most cost-effective template, minimizing overall costs.
**Implementation Recommendations**: Implement the solution by configuring the document management system to use the recommended template assignments, ensuring cost efficiency.