#!/usr/bin/env python3
"""
Schema Analyzer Tool
Extracts complete schema information from the database.
"""

import sqlite3
import json
import os

def analyze_schema(db_path="database.sqlite"):
    """Extract comprehensive schema information."""

    os.makedirs("tool_output", exist_ok=True)

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    schema_info = {
        "database_name": db_path,
        "tables": {},
        "total_tables": 0,
        "total_rows": 0
    }

    try:
        # Get all tables
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
        tables = cursor.fetchall()

        schema_info["total_tables"] = len(tables)

        for table_name, in tables:
            if table_name.startswith("sqlite_"):
                continue

            table_info = {
                "columns": [],
                "row_count": 0,
                "primary_key": None,
                "indexes": []
            }

            # Get row count
            try:
                cursor.execute(f"SELECT COUNT(*) FROM `{table_name}`")
                row_count = cursor.fetchone()[0]
                table_info["row_count"] = row_count
                schema_info["total_rows"] += row_count
            except Exception as e:
                table_info["row_count"] = 0
                table_info["error"] = str(e)

            # Get column information
            cursor.execute(f"PRAGMA table_info(`{table_name}`)")
            columns = cursor.fetchall()

            for col in columns:
                col_info = {
                    "name": col[1],
                    "type": col[2],
                    "not_null": bool(col[3]),
                    "default_value": col[4],
                    "is_primary_key": bool(col[5])
                }
                table_info["columns"].append(col_info)

                if col_info["is_primary_key"]:
                    table_info["primary_key"] = col[1]

            # Get indexes
            cursor.execute(f"PRAGMA index_list(`{table_name}`)")
            indexes = cursor.fetchall()
            for idx in indexes:
                table_info["indexes"].append({
                    "name": idx[1],
                    "unique": bool(idx[2])
                })

            schema_info["tables"][table_name] = table_info

    except Exception as e:
        schema_info["error"] = str(e)

    finally:
        conn.close()

    # Write output
    with open("tool_output/schema_analysis.json", "w") as f:
        json.dump(schema_info, f, indent=2)

    print(f"Schema analysis complete: {schema_info['total_tables']} tables, {schema_info['total_rows']} total rows")
    print("Results saved to tool_output/schema_analysis.json")

if __name__ == "__main__":
    analyze_schema()