#!/usr/bin/env python3
"""
Extract complete schema information from the database.
"""

import sqlite3
import json
import sys
from pathlib import Path

def get_schema_info(db_path="database.sqlite"):
    """Extract complete schema information."""
    
    schema = {
        "tables": {},
        "indexes": [],
        "views": []
    }
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Get all tables
        cursor.execute("""
            SELECT name FROM sqlite_master 
            WHERE type='table' 
            AND name NOT LIKE 'sqlite_%'
            ORDER BY name
        """)
        tables = cursor.fetchall()
        
        for (table_name,) in tables:
            schema["tables"][table_name] = {
                "columns": {},
                "primary_keys": [],
                "foreign_keys": [],
                "indexes": [],
                "row_count": 0,
                "create_statement": ""
            }
            
            # Get create statement
            cursor.execute(f"""
                SELECT sql FROM sqlite_master 
                WHERE type='table' AND name=?
            """, (table_name,))
            create_stmt = cursor.fetchone()
            if create_stmt:
                schema["tables"][table_name]["create_statement"] = create_stmt[0]
            
            # Get column information
            cursor.execute(f"PRAGMA table_info('{table_name}')")
            columns = cursor.fetchall()
            
            for col in columns:
                col_name = col[1]
                schema["tables"][table_name]["columns"][col_name] = {
                    "type": col[2],
                    "nullable": col[3] == 0,
                    "default": col[4],
                    "primary_key": col[5] > 0
                }
                
                if col[5] > 0:  # Is primary key
                    schema["tables"][table_name]["primary_keys"].append(col_name)
            
            # Get foreign keys
            cursor.execute(f"PRAGMA foreign_key_list('{table_name}')")
            fks = cursor.fetchall()
            
            for fk in fks:
                schema["tables"][table_name]["foreign_keys"].append({
                    "column": fk[3],
                    "references_table": fk[2],
                    "references_column": fk[4]
                })
            
            # Get row count
            cursor.execute(f"SELECT COUNT(*) FROM '{table_name}'")
            row_count = cursor.fetchone()[0]
            schema["tables"][table_name]["row_count"] = row_count
            
            # Get indexes for this table
            cursor.execute(f"PRAGMA index_list('{table_name}')")
            indexes = cursor.fetchall()
            
            for idx in indexes:
                idx_name = idx[1]
                cursor.execute(f"PRAGMA index_info('{idx_name}')")
                idx_columns = cursor.fetchall()
                
                schema["tables"][table_name]["indexes"].append({
                    "name": idx_name,
                    "unique": idx[2] == 1,
                    "columns": [col[2] for col in idx_columns]
                })
        
        # Get all indexes (global list)
        cursor.execute("""
            SELECT name, tbl_name FROM sqlite_master 
            WHERE type='index'
            AND name NOT LIKE 'sqlite_%'
        """)
        all_indexes = cursor.fetchall()
        
        for idx_name, tbl_name in all_indexes:
            schema["indexes"].append({
                "name": idx_name,
                "table": tbl_name
            })
        
        # Get all views
        cursor.execute("""
            SELECT name, sql FROM sqlite_master 
            WHERE type='view'
            ORDER BY name
        """)
        views = cursor.fetchall()
        
        for view_name, view_sql in views:
            schema["views"].append({
                "name": view_name,
                "definition": view_sql
            })
        
        conn.close()
        
    except sqlite3.Error as e:
        return {"error": f"Database error: {str(e)}"}
    except Exception as e:
        return {"error": f"Unexpected error: {str(e)}"}
    
    return schema

def main():
    """Run schema extraction and output JSON."""
    schema = get_schema_info()
    
    # Output as JSON for other tools to consume
    print(json.dumps(schema, indent=2))
    
    # Also save to file for reference
    output_dir = Path("tool_output")
    output_dir.mkdir(exist_ok=True)
    
    with open(output_dir / "schema.json", 'w') as f:
        json.dump(schema, f, indent=2)

if __name__ == "__main__":
    main()