#!/usr/bin/env python3
"""
Unified database analyzer that runs all analysis tools and compiles results.
Generates comprehensive database documentation for SQL generation.
"""

import sqlite3
import json
import os
import subprocess
import sys

def run_all_analyses():
    """Execute all analysis tools and compile results."""

    print("Starting comprehensive database analysis...")

    # Run individual analysis tools
    tools = [
        ("relationship_analyzer.py", "Analyzing relationships"),
        ("value_pattern_extractor.py", "Extracting value patterns"),
        ("aggregation_mapper.py", "Mapping aggregation patterns")
    ]

    for tool, description in tools:
        print(f"\n{description}...")
        try:
            result = subprocess.run([sys.executable, f"tools/{tool}"],
                                  capture_output=True, text=True)
            if result.returncode != 0:
                print(f"Warning: {tool} had issues: {result.stderr}")
        except Exception as e:
            print(f"Error running {tool}: {e}")

    # Compile all results
    compile_results()

def compile_results():
    """Compile all analysis results into a unified output."""

    compiled = {
        "database_profile": {},
        "quick_reference": {},
        "sql_generation_guide": {}
    }

    # Load individual analysis results
    try:
        with open("tool_output/relationships.json") as f:
            relationships = json.load(f)
        with open("tool_output/value_patterns.json") as f:
            value_patterns = json.load(f)
        with open("tool_output/aggregation_patterns.json") as f:
            aggregation = json.load(f)
    except Exception as e:
        print(f"Error loading analysis results: {e}")
        return

    # Build database profile
    conn = sqlite3.connect("database.sqlite")
    cursor = conn.cursor()

    # Get basic stats
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = [row[0] for row in cursor.fetchall()]

    compiled["database_profile"] = {
        "total_tables": len(tables),
        "has_foreign_keys": len(relationships.get("foreign_keys", {})) > 0,
        "has_junction_tables": len(relationships.get("junction_tables", [])) > 0,
        "tables": {}
    }

    # Profile each table
    for table in tables:
        cursor.execute(f"SELECT COUNT(*) FROM {table}")
        row_count = cursor.fetchone()[0]

        cursor.execute(f"PRAGMA table_info({table})")
        columns = cursor.fetchall()

        compiled["database_profile"]["tables"][table] = {
            "row_count": row_count,
            "column_count": len(columns),
            "primary_key": aggregation.get("primary_keys", {}).get(table, []),
            "has_nulls": table in value_patterns.get("nullable_columns", {}),
            "foreign_keys_to": [fk["to_table"] for fk in relationships.get("foreign_keys", {}).get(table, [])]
        }

    # Build quick reference guide
    compiled["quick_reference"] = {
        "junction_tables": [],
        "high_null_columns": [],
        "use_like_for": [],
        "count_distinct_needed": [],
        "common_group_by": []
    }

    # Junction tables
    for jt in relationships.get("junction_tables", []):
        compiled["quick_reference"]["junction_tables"].append({
            "table": jt["table"],
            "connects": jt["connects"],
            "extra_data": jt.get("additional_columns", [])
        })

    # High null columns
    for table, cols in value_patterns.get("nullable_columns", {}).items():
        for col in cols:
            if col["null_percentage"] > 20:
                compiled["quick_reference"]["high_null_columns"].append(f"{table}.{col['column']}")

    # Columns needing LIKE
    for col_path, hint in value_patterns.get("operator_hints", {}).items():
        if "LIKE" in hint.get("recommended", ""):
            compiled["quick_reference"]["use_like_for"].append(col_path)

    # Tables needing DISTINCT
    for table in aggregation.get("distinct_requirements", {}):
        compiled["quick_reference"]["count_distinct_needed"].append(table)

    # Common GROUP BY columns
    for table, candidates in aggregation.get("grouping_candidates", {}).items():
        if candidates:
            compiled["quick_reference"]["common_group_by"].append({
                "table": table,
                "columns": [c["column"] for c in candidates[:3]]
            })

    # Build SQL generation guide
    compiled["sql_generation_guide"] = generate_sql_guide(
        relationships, value_patterns, aggregation, compiled["database_profile"]
    )

    # Save compiled results
    with open("tool_output/unified_analysis.json", "w") as f:
        json.dump(compiled, f, indent=2)

    # Generate final report
    generate_final_report(compiled)

    conn.close()
    print("\nUnified analysis complete - results in tool_output/")

def generate_sql_guide(relationships, value_patterns, aggregation, profile):
    """Generate specific SQL generation guidance."""

    guide = {
        "join_patterns": [],
        "aggregation_rules": [],
        "filter_rules": [],
        "common_mistakes": []
    }

    # Join patterns
    for jt in relationships.get("junction_tables", []):
        if len(jt["connects"]) == 2:
            guide["join_patterns"].append(
                f"To join {jt['connects'][0]} with {jt['connects'][1]}: "
                f"use junction table {jt['table']}"
            )

    # Direct foreign keys
    for table, fks in relationships.get("foreign_keys", {}).items():
        for fk in fks:
            guide["join_patterns"].append(
                f"Join {table} to {fk['to_table']}: "
                f"{table}.{fk['from_column']} = {fk['to_table']}.{fk['to_column']}"
            )

    # Aggregation rules
    for table, pks in aggregation.get("primary_keys", {}).items():
        if pks:
            guide["aggregation_rules"].append(
                f"Count unique {table}: use COUNT(DISTINCT {pks[0]})"
            )

    # Filter rules
    for col_path, hint in value_patterns.get("operator_hints", {}).items():
        guide["filter_rules"].append(f"{col_path}: {hint['recommended']}")

    # Common mistakes to avoid
    if relationships.get("junction_tables"):
        guide["common_mistakes"].append(
            "Don't forget junction tables for many-to-many relationships"
        )

    high_null_tables = [t for t, cols in value_patterns.get("nullable_columns", {}).items() if cols]
    if high_null_tables:
        guide["common_mistakes"].append(
            f"Tables with nulls ({', '.join(high_null_tables[:3])}): "
            "consider IS NOT NULL filters"
        )

    return guide

def generate_final_report(compiled):
    """Generate human-readable final report."""

    report = []
    report.append("# DATABASE ANALYSIS REPORT\n\n")

    # Database overview
    profile = compiled["database_profile"]
    report.append(f"## Overview\n")
    report.append(f"- Tables: {profile['total_tables']}\n")
    report.append(f"- Has Foreign Keys: {profile['has_foreign_keys']}\n")
    report.append(f"- Has Junction Tables: {profile['has_junction_tables']}\n\n")

    # Quick reference
    ref = compiled["quick_reference"]

    if ref["junction_tables"]:
        report.append("## Junction Tables (Many-to-Many)\n")
        for jt in ref["junction_tables"]:
            report.append(f"- {jt['table']}: connects {' <-> '.join(jt['connects'])}\n")
        report.append("\n")

    if ref["high_null_columns"]:
        report.append("## High NULL Columns (affects COUNT)\n")
        for col in ref["high_null_columns"][:10]:
            report.append(f"- {col}\n")
        report.append("\n")

    if ref["use_like_for"]:
        report.append("## Use LIKE Operator For\n")
        for col in ref["use_like_for"][:10]:
            report.append(f"- {col}\n")
        report.append("\n")

    # SQL generation guide
    guide = compiled["sql_generation_guide"]

    if guide["join_patterns"]:
        report.append("## JOIN Patterns\n")
        for pattern in guide["join_patterns"][:10]:
            report.append(f"- {pattern}\n")
        report.append("\n")

    if guide["aggregation_rules"]:
        report.append("## Aggregation Rules\n")
        for rule in guide["aggregation_rules"][:10]:
            report.append(f"- {rule}\n")
        report.append("\n")

    if guide["common_mistakes"]:
        report.append("## Common Mistakes to Avoid\n")
        for mistake in guide["common_mistakes"]:
            report.append(f"- {mistake}\n")
        report.append("\n")

    # Table details
    report.append("## Table Summary\n")
    for table, info in profile["tables"].items():
        report.append(f"\n### {table}\n")
        report.append(f"- Rows: {info['row_count']:,}\n")
        report.append(f"- Columns: {info['column_count']}\n")
        if info["primary_key"]:
            report.append(f"- Primary Key: {', '.join(info['primary_key'])}\n")
        if info["foreign_keys_to"]:
            report.append(f"- Foreign Keys To: {', '.join(info['foreign_keys_to'])}\n")

    # Write report
    with open("tool_output/database_analysis_report.txt", "w") as f:
        f.writelines(report)

    print("Generated database_analysis_report.txt")

if __name__ == "__main__":
    run_all_analyses()