#!/usr/bin/env python3
"""
Complexity Analyzer - Assesses database complexity to guide analysis depth
"""

import sqlite3
import os

def analyze_complexity(db_path='./database.sqlite'):
    """Analyze database complexity and recommend analysis depth."""
    
    if not os.path.exists(db_path):
        print(f"Database not found at {db_path}")
        return
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # Get table count
    cursor.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table'")
    table_count = cursor.fetchone()[0]
    
    # Get list of tables
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
    tables = [row[0] for row in cursor.fetchall()]
    
    # Calculate total rows and find largest tables
    total_rows = 0
    table_sizes = []
    
    for table in tables:
        try:
            cursor.execute(f"SELECT COUNT(*) FROM [{table}]")
            count = cursor.fetchone()[0]
            total_rows += count
            table_sizes.append((table, count))
        except:
            continue
    
    table_sizes.sort(key=lambda x: x[1], reverse=True)
    
    # Count foreign key relationships
    fk_count = 0
    for table in tables:
        cursor.execute(f"PRAGMA foreign_key_list([{table}])")
        fk_count += len(cursor.fetchall())
    
    # Determine complexity level
    if table_count <= 5 and total_rows < 10000:
        complexity = "SIMPLE"
        recommendation = "Use concise template - focus on attribution and evidence reconciliation"
    elif table_count <= 10 and total_rows < 100000:
        complexity = "MEDIUM"
        recommendation = "Use balanced template - include detailed attribution map and relationships"
    else:
        complexity = "COMPLEX"
        recommendation = "Use comprehensive template - full documentation with warnings and optimization hints"
    
    # Output analysis
    print("=" * 60)
    print("DATABASE COMPLEXITY ANALYSIS")
    print("=" * 60)
    print(f"Complexity Level: {complexity}")
    print(f"Tables: {table_count}")
    print(f"Total Rows: {total_rows:,}")
    print(f"Foreign Keys: {fk_count}")
    print(f"Recommendation: {recommendation}")
    print()
    print("Largest Tables:")
    for table, count in table_sizes[:5]:
        print(f"  - {table}: {count:,} rows")
    
    if table_count > 10:
        print()
        print("Warning: High table count detected")
        print("Focus on:")
        print("  1. Clear attribution mapping (WHO OWNS WHAT)")
        print("  2. Common join paths")
        print("  3. Performance optimization for large tables")
    
    # Check for potential issues
    print()
    print("Schema Characteristics:")
    
    # Check for junction tables (tables with multiple FKs)
    junction_tables = []
    for table in tables:
        cursor.execute(f"PRAGMA foreign_key_list([{table}])")
        fks = cursor.fetchall()
        if len(fks) >= 2:
            junction_tables.append(table)
    
    if junction_tables:
        print(f"  - Junction tables detected: {', '.join(junction_tables)}")
        print("    → Document many-to-many relationships clearly")
    
    # Check for similar table names (potential confusion)
    similar_groups = {}
    for table in tables:
        prefix = table.split('_')[0] if '_' in table else table[:4]
        if prefix not in similar_groups:
            similar_groups[prefix] = []
        similar_groups[prefix].append(table)
    
    confusing_groups = [group for group in similar_groups.values() if len(group) > 2]
    if confusing_groups:
        print("  - Similar table name groups detected:")
        for group in confusing_groups:
            print(f"    → {', '.join(group)}")
        print("    → Create clear attribution map to prevent confusion")
    
    # Save summary to file for agent to read
    os.makedirs('./tool_output', exist_ok=True)
    with open('./tool_output/complexity_analysis.txt', 'w') as f:
        f.write(f"COMPLEXITY: {complexity}\n")
        f.write(f"TABLES: {table_count}\n")
        f.write(f"ROWS: {total_rows}\n")
        f.write(f"FOREIGN_KEYS: {fk_count}\n")
        f.write(f"TEMPLATE: {complexity}\n")
    
    conn.close()
    print()
    print("Analysis saved to ./tool_output/complexity_analysis.txt")

if __name__ == "__main__":
    analyze_complexity()