#!/usr/bin/env python3
"""
Extract and format schema information from SQLite database.
This tool is available to subagents for database analysis.
"""

import sqlite3
import json
import sys
from pathlib import Path

def analyze_schema(db_path: str) -> dict:
    """Extract comprehensive schema information from database."""
    conn = sqlite3.connect(f'file:{db_path}?mode=ro', uri=True)
    cursor = conn.cursor()
    
    schema = {
        'tables': {},
        'indexes': [],
        'views': []
    }
    
    # Get all tables with their CREATE statements
    cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
    tables = cursor.fetchall()
    
    for table_name, create_sql in tables:
        schema['tables'][table_name] = {
            'create_sql': create_sql,
            'columns': [],
            'primary_keys': [],
            'foreign_keys': [],
            'indexes': []
        }
        
        # Get column information
        cursor.execute(f'PRAGMA table_info("{table_name}")')
        for col in cursor.fetchall():
            col_info = {
                'name': col[1],
                'type': col[2],
                'nullable': not col[3],
                'default': col[4],
                'primary_key': bool(col[5])
            }
            schema['tables'][table_name]['columns'].append(col_info)
            if col[5]:  # Is primary key
                schema['tables'][table_name]['primary_keys'].append(col[1])
        
        # Get foreign keys
        cursor.execute(f'PRAGMA foreign_key_list("{table_name}")')
        for fk in cursor.fetchall():
            schema['tables'][table_name]['foreign_keys'].append({
                'from_column': fk[3],
                'to_table': fk[2],
                'to_column': fk[4]
            })
        
        # Get indexes for this table
        cursor.execute(f'PRAGMA index_list("{table_name}")' )
        for idx in cursor.fetchall():
            schema['tables'][table_name]['indexes'].append({
                'name': idx[1],
                'unique': bool(idx[2])
            })
    
    # Get all indexes
    cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL")
    indexes = cursor.fetchall()
    for idx_name, idx_sql in indexes:
        schema['indexes'].append({
            'name': idx_name,
            'sql': idx_sql
        })
    
    # Get views
    cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='view'")
    views = cursor.fetchall()
    for view_name, view_sql in views:
        schema['views'].append({
            'name': view_name,
            'sql': view_sql
        })
    
    conn.close()
    return schema

def main():
    if len(sys.argv) != 2:
        print("Usage: python analyze_schema.py <database.sqlite>")
        sys.exit(1)
    
    db_path = sys.argv[1]
    if not Path(db_path).exists():
        print(f"Error: Database not found: {db_path}")
        sys.exit(1)
    
    schema = analyze_schema(db_path)
    print(json.dumps(schema, indent=2))

if __name__ == "__main__":
    main()