#!/usr/bin/env python3
"""
Unified Analyzer - Orchestrates all analysis tools
Combines approaches from iter8, iter10, and iter20 for optimal results
"""

import os
import sys
import subprocess
import json

def run_unified_analysis():
    """Orchestrate all analysis tools with robust error handling."""
    
    print("Starting Unified Database Analysis...")
    print("=" * 60)
    
    # Create output directory
    os.makedirs("tool_output", exist_ok=True)
    
    # Tools to run in priority order
    tools = [
        ("schema_validator.py", "Validating schema and column locations", True),
        ("value_sampler.py", "Sampling values with exact formatting", True),
        ("relationship_detector.py", "Detecting relationships and join paths", False),
        ("pattern_selector.py", "Selecting context-appropriate patterns", False)
    ]
    
    results = {}
    outputs = []
    critical_failures = []
    
    for tool_name, description, is_critical in tools:
        print(f"\n{description}...")
        print("-" * 40)
        
        try:
            result = subprocess.run(
                [sys.executable, f"tools/{tool_name}"],
                capture_output=True,
                text=True,
                timeout=30
            )
            
            if result.returncode == 0:
                results[tool_name] = "Success"
                print(f"✓ {tool_name} completed successfully")
                
                # Read tool output
                output_file = f"tool_output/{tool_name.replace('.py', '_output.txt')}"
                if os.path.exists(output_file):
                    with open(output_file, 'r', encoding='utf-8', errors='ignore') as f:
                        outputs.append(f.read())
            else:
                results[tool_name] = f"Failed: {result.stderr[:100]}"
                print(f"✗ {tool_name} failed")
                if is_critical:
                    critical_failures.append(tool_name)
                    
        except subprocess.TimeoutExpired:
            results[tool_name] = "Timeout"
            print(f"✗ {tool_name} timed out")
            if is_critical:
                critical_failures.append(tool_name)
                
        except Exception as e:
            results[tool_name] = str(e)[:100]
            print(f"✗ {tool_name} error: {e}")
            if is_critical:
                critical_failures.append(tool_name)
    
    # Compile final documentation
    print("\n" + "=" * 60)
    print("Compiling unified documentation...")
    
    doc_sections = []
    
    # Header
    doc_sections.append("# UNIFIED DATABASE ANALYSIS")
    doc_sections.append("# Precision Cross-Pollination Results")
    doc_sections.append("")
    doc_sections.append("This analysis combines the best techniques from top-performing agents.")
    doc_sections.append("Use the schema validation for column locations, value samples for exact matching,")
    doc_sections.append("and query patterns as templates for your SQL generation.")
    doc_sections.append("")
    
    if critical_failures:
        doc_sections.append("⚠ WARNING: Some critical tools failed. Analysis may be incomplete.")
        doc_sections.append(f"Failed tools: {', '.join(critical_failures)}")
        doc_sections.append("")
    
    doc_sections.append("=" * 80)
    
    # Add all successful outputs
    for i, output in enumerate(outputs):
        if output:
            doc_sections.append("")
            doc_sections.append(output)
            doc_sections.append("")
            if i < len(outputs) - 1:
                doc_sections.append("=" * 80)
    
    # Add quick reference guide
    doc_sections.append("")
    doc_sections.append("=" * 80)
    doc_sections.append("# QUICK REFERENCE GUIDE")
    doc_sections.append("")
    doc_sections.append("## Query Building Process:")
    doc_sections.append("1. Check evidence for column mappings and formulas")
    doc_sections.append("2. Find columns in SCHEMA VALIDATION section")
    doc_sections.append("3. Use exact values from VALUE SAMPLES section")
    doc_sections.append("4. Apply patterns from QUERY PATTERNS section")
    doc_sections.append("5. Return only the requested columns")
    doc_sections.append("")
    doc_sections.append("## Common Fixes:")
    doc_sections.append("- 'no such column': Check SCHEMA VALIDATION for correct table")
    doc_sections.append("- Case mismatch: Use exact case from VALUE SAMPLES")
    doc_sections.append("- NULL issues: Use IS NULL, not = NULL")
    doc_sections.append("- Division by zero: Use NULLIF(denominator, 0)")
    doc_sections.append("")
    doc_sections.append("## Remember:")
    doc_sections.append("- Evidence overrides all other rules")
    doc_sections.append("- Never guess column names")
    doc_sections.append("- Match values exactly")
    doc_sections.append("- Keep queries simple")
    
    # Write final documentation
    final_doc = "\n".join(doc_sections)
    
    with open("tool_output/unified_analysis.txt", "w", encoding='utf-8') as f:
        f.write(final_doc)
    
    # Save analysis summary
    summary = {
        "tools_run": len(tools),
        "tools_succeeded": sum(1 for r in results.values() if r == "Success"),
        "critical_failures": critical_failures,
        "results": results
    }
    
    with open("tool_output/analysis_summary.json", "w") as f:
        json.dump(summary, f, indent=2)
    
    print(f"\n✅ Analysis complete!")
    print(f"   Output: tool_output/unified_analysis.txt")
    print(f"   Size: {len(final_doc)} characters")
    print(f"   Tools succeeded: {summary['tools_succeeded']}/{summary['tools_run']}")
    
    if critical_failures:
        print(f"   ⚠ Critical failures: {', '.join(critical_failures)}")
    
    return final_doc

if __name__ == "__main__":
    run_unified_analysis()