#!/usr/bin/env python3
"""
Schema Analyzer - Extracts complete database schema with relationships
"""
import sqlite3
import json
import os

def analyze_schema(db_path):
    """Extract complete schema information from the database"""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    schema_info = {
        "tables": {},
        "foreign_keys": {},
        "indexes": {},
        "views": []
    }
    
    # Get all tables
    cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='table' ORDER BY name")
    tables = cursor.fetchall()
    
    for table_name, create_sql in tables:
        # Get table info
        cursor.execute(f"PRAGMA table_info('{table_name}')")
        columns = cursor.fetchall()
        
        # Get row count
        cursor.execute(f"SELECT COUNT(*) FROM '{table_name}'")
        row_count = cursor.fetchone()[0]
        
        # Get foreign keys
        cursor.execute(f"PRAGMA foreign_key_list('{table_name}')")
        foreign_keys = cursor.fetchall()
        
        # Store table information
        schema_info["tables"][table_name] = {
            "row_count": row_count,
            "create_sql": create_sql,
            "columns": [
                {
                    "cid": col[0],
                    "name": col[1],
                    "type": col[2],
                    "notnull": bool(col[3]),
                    "default": col[4],
                    "primary_key": bool(col[5])
                }
                for col in columns
            ],
            "primary_keys": [col[1] for col in columns if col[5]],
            "foreign_keys": [
                {
                    "column": fk[3],
                    "references_table": fk[2],
                    "references_column": fk[4]
                }
                for fk in foreign_keys
            ]
        }
        
        # Store foreign key relationships
        for fk in foreign_keys:
            key = f"{table_name}.{fk[3]} -> {fk[2]}.{fk[4]}"
            schema_info["foreign_keys"][key] = {
                "from_table": table_name,
                "from_column": fk[3],
                "to_table": fk[2],
                "to_column": fk[4]
            }
    
    # Get indexes
    cursor.execute("SELECT name, tbl_name, sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL")
    indexes = cursor.fetchall()
    for idx_name, table_name, create_sql in indexes:
        if table_name not in schema_info["indexes"]:
            schema_info["indexes"][table_name] = []
        schema_info["indexes"][table_name].append({
            "name": idx_name,
            "sql": create_sql
        })
    
    # Get views
    cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='view'")
    views = cursor.fetchall()
    schema_info["views"] = [{"name": v[0], "sql": v[1]} for v in views]
    
    conn.close()
    
    # Create output directory if it doesn't exist
    os.makedirs('tool_output', exist_ok=True)
    
    # Save detailed schema
    with open('tool_output/schema.json', 'w') as f:
        json.dump(schema_info, f, indent=2)
    
    # Generate summary
    print("=" * 60)
    print("SCHEMA ANALYSIS COMPLETE")
    print("=" * 60)
    print(f"Tables: {len(schema_info['tables'])}")
    print(f"Total rows: {sum(t['row_count'] for t in schema_info['tables'].values())}")
    print(f"Foreign key relationships: {len(schema_info['foreign_keys'])}")
    print(f"Views: {len(schema_info['views'])}")
    print()
    
    print("TABLE SUMMARY:")
    for table_name, info in schema_info["tables"].items():
        pk_str = f"PK: {', '.join(info['primary_keys'])}" if info['primary_keys'] else "No PK"
        fk_count = len(info['foreign_keys'])
        fk_str = f"FKs: {fk_count}" if fk_count > 0 else "No FKs"
        print(f"  {table_name}: {info['row_count']} rows, {len(info['columns'])} columns, {pk_str}, {fk_str}")
    
    print()
    print("Results saved to tool_output/schema.json")

if __name__ == "__main__":
    analyze_schema("database.sqlite")