# MASTER TEMPLATE: Integration Guide
# How to integrate master templates into existing codebase

## OVERVIEW

This guide explains how to integrate the master templates into the existing AI assistant system to improve question decomposition and SQL generation accuracy.

## FILES CREATED

### 1. question_decomposer_master.txt
- Comprehensive question classification rules
- Examples for each question type
- Common mistake patterns
- Validation rules

### 2. sql_generator_master.txt
- Database schema information
- SQL generation rules
- Column alias guidelines
- Date function specifications

### 3. validation_rules.txt
- Question decomposition validation
- SQL generation validation
- Error correction rules
- Testing scenarios

### 4. examples_by_category.txt
- Correct/incorrect examples by category
- Common mistake patterns
- Validation checklists

## INTEGRATION STEPS

### Step 1: Update Question Decomposer

#### Current File: test/rightally_ai_assistant/graph/nodes/question_decomposer.py

#### Changes Needed:
1. Replace the existing prompt_template with content from question_decomposer_master.txt
2. Add validation logic using rules from validation_rules.txt
3. Implement error correction using patterns from examples_by_category.txt

#### Implementation:
```python
def __init__(self):
    # Load master template
    template_path = os.path.join(os.path.dirname(__file__), '..', '..', 'templates', 'question_decomposer_master.txt')
    with open(template_path, 'r', encoding='utf-8') as f:
        self.prompt_template = f.read()
```

### Step 2: Update SQL Generator

#### Current File: test/rightally_ai_assistant/graph/nodes/sql_generator.py

#### Changes Needed:
1. Replace existing prompt with content from sql_generator_master.txt
2. Add validation using rules from validation_rules.txt
3. Implement cleanup functions based on examples

#### Implementation:
```python
def __init__(self):
    # Load master template
    template_path = os.path.join(os.path.dirname(__file__), '..', '..', 'templates', 'sql_generator_master.txt')
    with open(template_path, 'r', encoding='utf-8') as f:
        self.prompt_template = f.read()
```

### Step 3: Add Validation Functions

#### Create: test/rightally_ai_assistant/utils/validation.py

#### Functions to Implement:
1. validate_question_classification()
2. validate_table_usage()
3. validate_column_usage()
4. validate_sql_syntax()
5. validate_aliases()

### Step 4: Add Error Correction Functions

#### Create: test/rightally_ai_assistant/utils/error_correction.py

#### Functions to Implement:
1. fix_column_aliases()
2. fix_date_functions()
3. fix_table_usage()
4. fix_question_interpretation()

## IMPLEMENTATION DETAILS

### 1. Template Loading

#### For Question Decomposer:
```python
def load_master_template(self):
    template_path = os.path.join(os.path.dirname(__file__), '..', '..', 'templates', 'question_decomposer_master.txt')
    try:
        with open(template_path, 'r', encoding='utf-8') as f:
            return f.read()
    except FileNotFoundError:
        # Fallback to current template
        return self.current_prompt_template
```

#### For SQL Generator:
```python
def load_master_template(self):
    template_path = os.path.join(os.path.dirname(__file__), '..', '..', 'templates', 'sql_generator_master.txt')
    try:
        with open(template_path, 'r', encoding='utf-8') as f:
            return f.read()
    except FileNotFoundError:
        # Fallback to current template
        return self.current_prompt_template
```

### 2. Validation Integration

#### Add to Question Decomposer:
```python
def validate_decomposition(self, decomposition):
    from utils.validation import validate_question_classification, validate_table_usage
    from utils.error_correction import fix_question_interpretation
    
    # Validate question classification
    if not validate_question_classification(decomposition):
        decomposition = fix_question_interpretation(decomposition)
    
    # Validate table usage
    if not validate_table_usage(decomposition):
        decomposition = fix_table_usage(decomposition)
    
    return decomposition
```

#### Add to SQL Generator:
```python
def validate_sql(self, sql):
    from utils.validation import validate_sql_syntax, validate_aliases
    from utils.error_correction import fix_column_aliases, fix_date_functions
    
    # Validate SQL syntax
    if not validate_sql_syntax(sql):
        sql = fix_sql_syntax(sql)
    
    # Validate aliases
    if not validate_aliases(sql):
        sql = fix_column_aliases(sql)
    
    # Fix date functions
    sql = fix_date_functions(sql)
    
    return sql
```

### 3. Testing Integration

#### Create Test Suite:
```python
# test_templates.py
def test_question_classification():
    # Test each question category
    pass

def test_sql_generation():
    # Test SQL generation for each category
    pass

def test_validation():
    # Test validation rules
    pass

def test_error_correction():
    # Test error correction functions
    pass
```

## MIGRATION STRATEGY

### Phase 1: Template Integration
1. Update question_decomposer.py to use master template
2. Update sql_generator.py to use master template
3. Test with existing questions

### Phase 2: Validation Integration
1. Create validation utility functions
2. Integrate validation into decomposition and SQL generation
3. Test validation with edge cases

### Phase 3: Error Correction Integration
1. Create error correction utility functions
2. Integrate error correction into the pipeline
3. Test error correction with problematic questions

### Phase 4: Testing and Refinement
1. Test with comprehensive question set
2. Refine templates based on results
3. Add more examples as needed

## BENEFITS OF INTEGRATION

### 1. Consistency
- Standardized question classification
- Consistent SQL generation patterns
- Uniform error handling

### 2. Maintainability
- Centralized template management
- Easy to update examples
- Clear validation rules

### 3. Accuracy
- Reduced error rates
- Better question interpretation
- Improved SQL generation

### 4. Scalability
- Easy to add new question types
- Extensible validation rules
- Modular error correction

## ROLLBACK PLAN

### If Issues Arise:
1. Keep current templates as fallback
2. Implement gradual migration
3. A/B test new vs old templates
4. Monitor error rates

### Fallback Implementation:
```python
def load_template_with_fallback(self):
    try:
        return self.load_master_template()
    except Exception:
        return self.current_prompt_template
```

## MONITORING AND MAINTENANCE

### 1. Performance Monitoring
- Track question classification accuracy
- Monitor SQL generation success rate
- Measure error correction effectiveness

### 2. Template Maintenance
- Regular review of examples
- Update based on user feedback
- Add new question patterns as needed

### 3. Validation Updates
- Refine validation rules based on errors
- Add new validation patterns
- Update error correction logic

## CONCLUSION

The master templates provide a comprehensive, maintainable solution for improving question decomposition and SQL generation accuracy. The integration should be done gradually with proper testing and monitoring to ensure system stability.