#!/usr/bin/env python3
"""
Comprehensive Analyzer - Main orchestrator that runs all tools and compiles results.
This is the primary tool that the agent should run.
"""

import subprocess
import sys
import os

def run_comprehensive_analysis():
    """Run all analysis tools and compile the results."""

    print("Starting comprehensive database analysis...")
    print("=" * 60)

    # Tools to run in order
    tools = [
        ("schema_validator.py", "Validating schema and column locations"),
        ("query_pattern_generator.py", "Generating query patterns"),
        ("value_sampler.py", "Sampling values with exact case"),
        ("evidence_mapper.py", "Mapping evidence patterns")
    ]

    # Run each tool
    for tool_name, description 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:
                print(f"Warning: {tool_name} encountered an error:")
                print(result.stderr)
            else:
                print(result.stdout)

        except subprocess.TimeoutExpired:
            print(f"Warning: {tool_name} timed out after 30 seconds")
        except Exception as e:
            print(f"Error running {tool_name}: {e}")

    # Compile final documentation
    print("\n" + "=" * 60)
    print("Compiling comprehensive documentation...")

    output_sections = []

    # Header
    output_sections.append("# COMPREHENSIVE DATABASE DOCUMENTATION")
    output_sections.append("# Schema-Validated Query Composer Analysis")
    output_sections.append("")
    output_sections.append("This documentation provides everything needed to write accurate SQL queries.")
    output_sections.append("Pay special attention to the column location reference to avoid 'no such column' errors.")
    output_sections.append("")
    output_sections.append("=" * 80)
    output_sections.append("")

    # Read and append each tool's output
    tool_outputs = [
        ("schema_validation.txt", "SCHEMA VALIDATION (Column Locations)"),
        ("value_samples.txt", "VALUE SAMPLES (Exact Case/Format)"),
        ("query_patterns.txt", "QUERY PATTERNS (Working Templates)"),
        ("evidence_mapping.txt", "EVIDENCE MAPPING (Hint Translation)")
    ]

    for filename, section_title in tool_outputs:
        filepath = f"tool_output/{filename}"
        if os.path.exists(filepath):
            output_sections.append(f"\n{'=' * 80}")
            output_sections.append(f"# {section_title}")
            output_sections.append("=" * 80)
            output_sections.append("")

            with open(filepath, 'r') as f:
                content = f.read()
                output_sections.append(content)
        else:
            output_sections.append(f"\n[Warning: {filename} not found]")

    # Add quick reference guide at the end
    output_sections.append("\n" + "=" * 80)
    output_sections.append("# QUICK REFERENCE FOR SQL GENERATION")
    output_sections.append("=" * 80)
    output_sections.append("")
    output_sections.append("## Step-by-step SQL Generation Process:")
    output_sections.append("")
    output_sections.append("1. **Check Evidence**: If evidence provides column names, use them exactly")
    output_sections.append("2. **Find Column Location**: Use SCHEMA VALIDATION section to find table.column")
    output_sections.append("3. **Check Value Format**: Use VALUE SAMPLES for exact case/format")
    output_sections.append("4. **Use Query Pattern**: Adapt a QUERY PATTERN template if applicable")
    output_sections.append("5. **Apply Evidence Mapping**: Translate evidence hints using EVIDENCE MAPPING")
    output_sections.append("")
    output_sections.append("## Most Common Fixes:")
    output_sections.append("")
    output_sections.append("- **'no such column'**: Check SCHEMA VALIDATION for correct table")
    output_sections.append("- **Case mismatch**: Use exact case from VALUE SAMPLES")
    output_sections.append("- **Wrong aggregation**: Check EVIDENCE MAPPING for hint interpretation")
    output_sections.append("- **Missing JOIN**: Check foreign keys in SCHEMA VALIDATION")
    output_sections.append("")
    output_sections.append("Remember: This database analysis is specific to this database.")
    output_sections.append("Use the exact column names and value formats shown above.")

    # Write comprehensive output
    comprehensive_output = "\n".join(output_sections)

    with open("tool_output/comprehensive_documentation.txt", "w") as f:
        f.write(comprehensive_output)

    print("Comprehensive documentation generated!")
    print(f"Output saved to: tool_output/comprehensive_documentation.txt")
    print(f"Total size: {len(comprehensive_output)} characters")

    return comprehensive_output

if __name__ == "__main__":
    run_comprehensive_analysis()