#!/usr/bin/env python3
"""
Comprehensive Validator - Main orchestrator that runs all validation tools.
Combines the best approaches from top-performing agents.
"""

import subprocess
import sys
import os
import json

def run_comprehensive_validation():
    """Run all validation tools and compile hierarchical documentation."""

    print("Starting Precision Schema Validation...")
    print("=" * 80)

    # Create output directory
    os.makedirs('tool_output', exist_ok=True)

    # Tools to run in order (hierarchical validation)
    tools = [
        ("schema_mapper.py", "Level 1: Schema Validation - Column Locations"),
        ("value_validator.py", "Level 2: Value Validation - Exact Case/Format"),
        ("pattern_generator.py", "Level 3: Pattern Validation - Query Templates"),
        ("evidence_translator.py", "Level 4: Evidence Validation - Mapping Rules")
    ]

    all_successful = True

    # Run each tool
    for tool_name, description in tools:
        print(f"\n{description}")
        print("-" * 60)

        try:
            result = subprocess.run(
                [sys.executable, f"tools/{tool_name}"],
                capture_output=True,
                text=True,
                timeout=45
            )

            if result.returncode != 0:
                print(f"Warning: {tool_name} encountered an error")
                print(result.stderr[:500])  # Show first 500 chars of error
                all_successful = False
            else:
                print(f"✓ {tool_name} completed successfully")
                if result.stdout:
                    print(result.stdout[:200])  # Show snippet of output

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

    # Compile final documentation
    print("\n" + "=" * 80)
    print("Compiling Hierarchical Validation Documentation...")
    print("=" * 80)

    output_lines = []

    # Header with critical reminders
    output_lines.append("# PRECISION SCHEMA VALIDATION DOCUMENTATION")
    output_lines.append("# Generated by Cross-Pollinated Precision Validator")
    output_lines.append("")
    output_lines.append("=" * 80)
    output_lines.append("CRITICAL REMINDERS:")
    output_lines.append("1. Check Column Location Reference FIRST to avoid 'no such column' errors")
    output_lines.append("2. Use EXACT case from Value Samples (case sensitivity is crucial)")
    output_lines.append("3. Adapt Query Templates for working SQL patterns")
    output_lines.append("4. Follow Evidence Mappings when evidence is provided")
    output_lines.append("=" * 80)
    output_lines.append("")

    # Read and append each validation level
    validation_levels = [
        ("schema_mapping.json", "LEVEL 1: COLUMN LOCATION REFERENCE", "json"),
        ("value_samples.json", "LEVEL 2: VALUE VALIDATION SAMPLES", "json"),
        ("query_patterns.json", "LEVEL 3: QUERY TEMPLATES", "json"),
        ("evidence_rules.txt", "LEVEL 4: EVIDENCE MAPPING RULES", "text")
    ]

    for filename, section_title, format_type in validation_levels:
        filepath = f"tool_output/{filename}"

        output_lines.append("=" * 80)
        output_lines.append(f"## {section_title}")
        output_lines.append("=" * 80)
        output_lines.append("")

        if os.path.exists(filepath):
            try:
                if format_type == "json":
                    with open(filepath, 'r') as f:
                        data = json.load(f)

                    # Format JSON data for readability
                    if "schema_mapping" in filename:
                        output_lines.append("### Column-to-Table Mapping (USE THIS TO AVOID ERRORS):")
                        output_lines.append("")
                        for table, info in data.get("tables", {}).items():
                            output_lines.append(f"Table: {table}")
                            for col in info.get("columns", []):
                                output_lines.append(f"  - {table}.{col['name']} ({col['type']})")
                            output_lines.append("")

                    elif "value_samples" in filename:
                        output_lines.append("### Sample Values (EXACT CASE):")
                        output_lines.append("")
                        for table, samples in data.items():
                            output_lines.append(f"Table: {table}")
                            if isinstance(samples, dict) and "sample_rows" in samples:
                                for i, row in enumerate(samples["sample_rows"][:3], 1):
                                    output_lines.append(f"  Row {i}: {json.dumps(row, ensure_ascii=False)}")
                            output_lines.append("")

                    elif "query_patterns" in filename:
                        output_lines.append("### Working Query Templates:")
                        output_lines.append("")
                        for pattern in data.get("templates", []):
                            output_lines.append(f"Pattern: {pattern.get('pattern', 'unknown')}")
                            output_lines.append(f"  Template: {pattern.get('template', '')}")
                            output_lines.append(f"  Example: {pattern.get('example', '')}")
                            output_lines.append("")

                else:  # text format
                    with open(filepath, 'r') as f:
                        content = f.read()
                        output_lines.append(content)

            except Exception as e:
                output_lines.append(f"[Error reading {filename}: {e}]")
        else:
            output_lines.append(f"[Warning: {filename} not found - tool may have failed]")

        output_lines.append("")

    # Add quick reference guide
    output_lines.append("=" * 80)
    output_lines.append("## QUICK REFERENCE FOR SQL GENERATION")
    output_lines.append("=" * 80)
    output_lines.append("")
    output_lines.append("### Step-by-Step Process:")
    output_lines.append("1. CHECK EVIDENCE: If provided, use column names and formulas exactly")
    output_lines.append("2. FIND COLUMNS: Use Level 1 schema mapping to locate table.column")
    output_lines.append("3. MATCH VALUES: Use Level 2 samples for exact case/format")
    output_lines.append("4. USE TEMPLATES: Adapt Level 3 patterns for your query")
    output_lines.append("5. APPLY EVIDENCE: Follow Level 4 rules for evidence translation")
    output_lines.append("")
    output_lines.append("### Most Common Fixes:")
    output_lines.append("- 'no such column': Check Level 1 for correct table.column")
    output_lines.append("- Wrong results: Check Level 2 for exact case of values")
    output_lines.append("- Syntax errors: Use Level 3 templates as starting point")
    output_lines.append("- Evidence ignored: Apply Level 4 mapping rules")
    output_lines.append("")
    output_lines.append("### Remember:")
    output_lines.append("- Evidence overrides everything")
    output_lines.append("- Use exact column locations from Level 1")
    output_lines.append("- Match exact case from Level 2")
    output_lines.append("- Adapt templates from Level 3")
    output_lines.append("- Return ONLY requested columns")

    # Write final documentation
    output_path = "tool_output/comprehensive_documentation.txt"
    with open(output_path, 'w') as f:
        f.write('\n'.join(output_lines))

    print(f"\n{'✓' if all_successful else '⚠'} Validation complete!")
    print(f"Documentation written to: {output_path}")

    return all_successful

if __name__ == "__main__":
    success = run_comprehensive_validation()
    sys.exit(0 if success else 1)