#!/usr/bin/env python3
"""
Database inspection utility for Madomeda
"""
import sqlite3
import json
from pathlib import Path
import sys


def inspect_database(db_path='madomeda.db'):
    """Inspect and display database contents"""
    if not Path(db_path).exists():
        print(f"Database not found: {db_path}")
        return
    
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    # Files
    print("=" * 60)
    print("FILES")
    print("=" * 60)
    cursor.execute("SELECT * FROM files ORDER BY path")
    files = cursor.fetchall()
    for f in files:
        print(f"{f['id']:3d}. {f['path']:<40s} (discovered: {f['discovered_at']})")
    
    # Tags
    print("\n" + "=" * 60)
    print("TAGS")
    print("=" * 60)
    cursor.execute("SELECT tag FROM tags ORDER BY tag")
    tags = cursor.fetchall()
    for tag in tags:
        print(f"  - {tag['tag']}")
    
    # Frontmatter summary
    print("\n" + "=" * 60)
    print("FRONTMATTER HISTORY")
    print("=" * 60)
    cursor.execute("""
        SELECT f.path, fm.read_at, fm.conformant, fm.content
        FROM frontmatter fm
        JOIN files f ON fm.file_id = f.id
        ORDER BY f.path, fm.read_at
    """)
    
    current_file = None
    for row in cursor.fetchall():
        if row['path'] != current_file:
            current_file = row['path']
            print(f"\n{current_file}:")
        
        content = json.loads(row['content'])
        status = "CONFORMANT" if row['conformant'] else "NON-CONFORMANT"
        print(f"  [{row['read_at']}] {status}")
        print(f"    Keys: {', '.join(content.keys())}")
        if 'tags' in content:
            print(f"    Tags: {content['tags']}")
    
    # Commits
    print("\n" + "=" * 60)
    print("GIT COMMITS")
    print("=" * 60)
    cursor.execute("""
        SELECT f.path, c.author_name, c.commit_hash, c.latest_hash
        FROM commits c
        JOIN files f ON c.file_id = f.id
        ORDER BY f.path
    """)
    
    for row in cursor.fetchall():
        print(f"{row['path']}:")
        print(f"  Author: {row['author_name']}")
        print(f"  First commit: {row['commit_hash'][:7]}")
        print(f"  Latest commit: {row['latest_hash'][:7]}")
    
    conn.close()
    print("\n" + "=" * 60)


if __name__ == '__main__':
    db_path = sys.argv[1] if len(sys.argv) > 1 else 'madomeda.db'
    inspect_database(db_path)