#!/usr/bin/env python3
"""
Check record counts in all ClaudeTools database tables
"""
import sys
from sqlalchemy import create_engine, text, inspect

# Database connection
DATABASE_URL = "mysql+pymysql://claudetools:CT_e8fcd5a3952030a79ed6debae6c954ed@172.16.3.30:3306/claudetools?charset=utf8mb4"

def get_table_counts():
    """Get row counts for all tables"""
    engine = create_engine(DATABASE_URL)

    with engine.connect() as conn:
        # Get all table names
        inspector = inspect(engine)
        tables = inspector.get_table_names()

        print("=" * 70)
        print("ClaudeTools Database Record Counts")
        print("=" * 70)
        print(f"Database: claudetools @ 172.16.3.30:3306")
        print(f"Total Tables: {len(tables)}")
        print("=" * 70)
        print()

        # Count rows in each table
        counts = {}
        total_records = 0

        for table in sorted(tables):
            result = conn.execute(text(f"SELECT COUNT(*) FROM `{table}`"))
            count = result.scalar()
            counts[table] = count
            total_records += count

        # Group by category
        categories = {
            'Core': ['machines', 'clients', 'projects', 'sessions', 'tags'],
            'MSP Work': ['work_items', 'tasks', 'billable_time', 'work_item_files'],
            'Infrastructure': ['sites', 'infrastructure', 'services', 'networks', 'firewall_rules', 'm365_tenants', 'm365_licenses'],
            'Credentials': ['credentials', 'credential_audit_logs', 'security_incidents'],
            'Context Recall': ['conversation_contexts', 'context_snippets', 'project_states', 'decision_logs'],
            'Learning': ['command_runs', 'file_changes', 'problem_solutions', 'failure_patterns', 'environmental_insights'],
            'Integrations': ['msp_integrations', 'backup_jobs', 'backup_reports'],
            'Junction': ['session_tags', 'session_work_items', 'client_contacts', 'project_repositories']
        }

        # Print by category
        for category, table_list in categories.items():
            category_tables = [t for t in table_list if t in counts]
            if not category_tables:
                continue

            print(f"{category}:")
            print("-" * 70)
            category_total = 0
            for table in category_tables:
                count = counts[table]
                category_total += count
                status = "[OK]" if count > 0 else " "
                print(f"  {status} {table:.<50} {count:>10,}")
            print(f"  {'Subtotal':.<50} {category_total:>10,}")
            print()

        # Print any uncategorized tables
        all_categorized = set()
        for table_list in categories.values():
            all_categorized.update(table_list)

        uncategorized = [t for t in counts.keys() if t not in all_categorized]
        if uncategorized:
            print("Other Tables:")
            print("-" * 70)
            for table in uncategorized:
                count = counts[table]
                status = "[OK]" if count > 0 else " "
                print(f"  {status} {table:.<50} {count:>10,}")
            print()

        # Print summary
        print("=" * 70)
        print(f"TOTAL RECORDS: {total_records:,}")
        print(f"Tables with data: {sum(1 for c in counts.values() if c > 0)}/{len(tables)}")
        print("=" * 70)

        return counts, total_records

if __name__ == "__main__":
    try:
        counts, total = get_table_counts()
        sys.exit(0)
    except Exception as e:
        print(f"ERROR: {e}", file=sys.stderr)
        import traceback
        traceback.print_exc()
        sys.exit(1)