excel_multitab_solution.py

"""
Enhanced Multi-Tab Excel Processing Solution
==========================================

This document outlines how to handle Excel files with multiple tabs and embedded schemas
for process mining analysis.

Current State:
- Basic Excel support: pd.read_excel(file_path) - reads first sheet only
- No multi-tab processing
- No schema detection within Excel files

Enhanced Solution:
"""

# 1. MULTI-TAB EXCEL DATA INGESTION
def load_excel_multi_tab(self, file_path: str) -> Dict[str, Any]:
    """Enhanced Excel loader for multi-tab files with schema detection.
    
    Returns:
        Dictionary containing all tabs with metadata and potential schemas
    """
    import pandas as pd
    from openpyxl import load_workbook
    
    result = {
        'file_path': file_path,
        'tabs': {},
        'metadata': {
            'total_tabs': 0,
            'data_tabs': [],
            'schema_tabs': [],
            'empty_tabs': []
        }
    }
    
    try:
        # Load workbook to get all sheet names
        workbook = load_workbook(file_path, read_only=True)
        sheet_names = workbook.sheetnames
        result['metadata']['total_tabs'] = len(sheet_names)
        
        for sheet_name in sheet_names:
            try:
                # Read each sheet
                df = pd.read_excel(file_path, sheet_name=sheet_name)
                
                if df.empty or len(df.columns) == 0:
                    result['metadata']['empty_tabs'].append(sheet_name)
                    continue
                
                # Analyze sheet content
                sheet_analysis = self._analyze_excel_sheet(df, sheet_name)
                
                if sheet_analysis['is_schema_definition']:
                    result['metadata']['schema_tabs'].append(sheet_name)
                else:
                    result['metadata']['data_tabs'].append(sheet_name)
                
                result['tabs'][sheet_name] = {
                    'data': df,
                    'analysis': sheet_analysis,
                    'metadata': self._analyze_data_structure(df)
                }
                
            except Exception as e:
                logger.warning(f"Failed to process sheet '{sheet_name}': {e}")
                continue
                
    except Exception as e:
        logger.error(f"Failed to load Excel file {file_path}: {e}")
        raise
    
    return result

# 2. SCHEMA DETECTION WITHIN EXCEL SHEETS
def _analyze_excel_sheet(self, df: pd.DataFrame, sheet_name: str) -> Dict[str, Any]:
    """Analyze if Excel sheet contains schema definition or data."""
    
    analysis = {
        'is_schema_definition': False,
        'is_data_table': False,
        'schema_type': None,
        'schema_elements': [],
        'confidence': 0.0
    }
    
    # Check for schema definition patterns
    schema_indicators = {
        'column_headers': ['table_name', 'column_name', 'data_type', 'description', 'nullable'],
        'entity_relationship': ['entity', 'attribute', 'relationship', 'cardinality'],
        'data_dictionary': ['field', 'type', 'length', 'constraints', 'description'],
        'process_mapping': ['process', 'activity', 'input', 'output', 'role']
    }
    
    column_names = [col.lower().replace(' ', '_') for col in df.columns]
    
    for schema_type, indicators in schema_indicators.items():
        matches = sum(1 for indicator in indicators if any(indicator in col for col in column_names))
        confidence = matches / len(indicators)
        
        if confidence > 0.6:  # More than 60% of indicators match
            analysis['is_schema_definition'] = True
            analysis['schema_type'] = schema_type
            analysis['confidence'] = confidence
            analysis['schema_elements'] = self._extract_schema_elements(df, schema_type)
            break
    
    if not analysis['is_schema_definition']:
        analysis['is_data_table'] = True
    
    return analysis

# 3. SCHEMA ELEMENT EXTRACTION FROM EXCEL
def _extract_schema_elements(self, df: pd.DataFrame, schema_type: str) -> List[Dict[str, Any]]:
    """Extract schema elements from Excel schema definition sheets."""
    
    elements = []
    
    if schema_type == 'column_headers':
        # Data dictionary format: table_name, column_name, data_type, description
        for _, row in df.iterrows():
            if pd.notna(row.get('column_name', None)):
                elements.append({
                    'name': str(row.get('column_name', '')),
                    'table': str(row.get('table_name', 'unknown')),
                    'type': str(row.get('data_type', 'unknown')),
                    'description': str(row.get('description', '')),
                    'nullable': str(row.get('nullable', 'unknown')),
                    'source': 'excel_schema_definition'
                })
    
    elif schema_type == 'process_mapping':
        # Process mapping format: process, activity, input, output, role
        for _, row in df.iterrows():
            if pd.notna(row.get('activity', None)):
                elements.append({
                    'name': str(row.get('activity', '')),
                    'process': str(row.get('process', 'unknown')),
                    'type': 'activity',
                    'input': str(row.get('input', '')),
                    'output': str(row.get('output', '')),
                    'role': str(row.get('role', '')),
                    'source': 'excel_process_definition'
                })
    
    return elements

# 4. INTEGRATION WITH MAIN ASSESSMENT FLOW
def process_excel_file_enhanced(self, file_path: str) -> Dict[str, Any]:
    """Process Excel file with multi-tab and schema detection support."""
    
    excel_data = self.load_excel_multi_tab(file_path)
    
    result = {
        'file_path': file_path,
        'datasets': [],
        'schemas': [],
        'metadata': excel_data['metadata']
    }
    
    for tab_name, tab_info in excel_data['tabs'].items():
        if tab_info['analysis']['is_data_table']:
            # Process as data for process mining analysis
            dataset = {
                'file_path': f"{file_path}#{tab_name}",
                'data': tab_info['data'],
                'metadata': tab_info['metadata'],
                'source_type': 'excel_data_tab'
            }
            result['datasets'].append(dataset)
        
        elif tab_info['analysis']['is_schema_definition']:
            # Process as schema definition
            schema = {
                'file_path': f"{file_path}#{tab_name}",
                'type': 'excel_embedded_schema',
                'schema_type': tab_info['analysis']['schema_type'],
                'elements': tab_info['analysis']['schema_elements'],
                'confidence': tab_info['analysis']['confidence'],
                'source_type': 'excel_schema_tab'
            }
            result['schemas'].append(schema)
    
    return result

# 5. REAL-WORLD EXAMPLE SCENARIOS

## Scenario 1: ERP Data Export with Schema Tab
"""
Excel File: "OrderManagement_Export.xlsx"
- Tab 1: "Orders" (data)
- Tab 2: "Schema" (table definitions)
- Tab 3: "ProcessFlow" (activity mappings)

Result:
- 1 data dataset from "Orders" tab
- 2 schema sources: table definitions + process mappings
- Combined assessment with schema-driven candidates + data validation
"""

## Scenario 2: Business Process Documentation
"""
Excel File: "ProcurementProcess.xlsx"
- Tab 1: "ProcessSteps" (activity definitions)
- Tab 2: "DataDictionary" (field definitions)
- Tab 3: "SampleData" (example transactions)

Result:
- Rich schema analysis from process documentation
- Data validation from sample transactions
- High-confidence activity and attribute mapping
"""

## Scenario 3: Multiple Entity Tables
"""
Excel File: "CRM_DataModel.xlsx"
- Tab 1: "Customers" (customer data)
- Tab 2: "Orders" (order data)
- Tab 3: "OrderLines" (order line data)
- Tab 4: "DataModel" (entity relationships)

Result:
- 3 related datasets with relationship mapping
- Schema-driven join key identification
- Multi-table event log assembly recommendations
"""