#!/usr/bin/env python3
"""
Database Health Check Script

Performs various checks on the SQLite database to ensure it's healthy
and reports any issues found.
"""
import sqlite3
import sys
from pathlib import Path
from datetime import datetime


def check_integrity(db_path: str) -> tuple[bool, list[str]]:
    """
    Run PRAGMA integrity_check on the database.

    Returns:
        Tuple of (is_ok, issues_list)
    """
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        cursor.execute("PRAGMA integrity_check;")
        results = cursor.fetchall()
        conn.close()

        if len(results) == 1 and results[0][0] == "ok":
            return True, []
        else:
            return False, [row[0] for row in results]
    except Exception as e:
        return False, [f"Error running integrity check: {e}"]


def check_foreign_keys(db_path: str) -> tuple[bool, list[str]]:
    """Check for foreign key constraint violations."""
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        cursor.execute("PRAGMA foreign_key_check;")
        results = cursor.fetchall()
        conn.close()

        if not results:
            return True, []
        else:
            issues = [f"FK violation in table {row[0]}, rowid {row[1]}" for row in results]
            return False, issues
    except Exception as e:
        return False, [f"Error checking foreign keys: {e}"]


def check_database_size(db_path: str) -> dict:
    """Get database size information."""
    db_file = Path(db_path)

    if not db_file.exists():
        return {"error": "Database file not found"}

    size_bytes = db_file.stat().st_size
    size_mb = size_bytes / 1024 / 1024

    # Check for WAL and SHM files
    wal_file = Path(f"{db_path}-wal")
    shm_file = Path(f"{db_path}-shm")

    wal_size = wal_file.stat().st_size / 1024 / 1024 if wal_file.exists() else 0
    shm_size = shm_file.stat().st_size / 1024 / 1024 if shm_file.exists() else 0

    return {
        "database_mb": round(size_mb, 2),
        "wal_mb": round(wal_size, 2),
        "shm_mb": round(shm_size, 2),
        "total_mb": round(size_mb + wal_size + shm_size, 2),
    }


def get_database_stats(db_path: str) -> dict:
    """Get various database statistics."""
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # Get journal mode
        cursor.execute("PRAGMA journal_mode;")
        journal_mode = cursor.fetchone()[0]

        # Get page count and page size
        cursor.execute("PRAGMA page_count;")
        page_count = cursor.fetchone()[0]

        cursor.execute("PRAGMA page_size;")
        page_size = cursor.fetchone()[0]

        # Get freelist count
        cursor.execute("PRAGMA freelist_count;")
        freelist_count = cursor.fetchone()[0]

        # Get table count
        cursor.execute("""
            SELECT COUNT(*) FROM sqlite_master
            WHERE type='table' AND name NOT LIKE 'sqlite_%';
        """)
        table_count = cursor.fetchone()[0]

        conn.close()

        return {
            "journal_mode": journal_mode,
            "page_count": page_count,
            "page_size": page_size,
            "freelist_count": freelist_count,
            "table_count": table_count,
            "fragmentation_percent": round((freelist_count / page_count * 100), 2) if page_count > 0 else 0,
        }
    except Exception as e:
        return {"error": str(e)}


def get_table_stats(db_path: str) -> list[dict]:
    """Get statistics for each table."""
    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()

        stats = []
        for (table_name,) in tables:
            cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
            row_count = cursor.fetchone()[0]

            stats.append({
                "table": table_name,
                "rows": row_count,
            })

        conn.close()
        return stats
    except Exception as e:
        return [{"error": str(e)}]


def run_health_check(db_path: str = "amo_data.db") -> dict:
    """Run complete health check on the database."""

    if not Path(db_path).exists():
        return {
            "healthy": False,
            "error": f"Database not found: {db_path}",
        }

    # Check integrity
    integrity_ok, integrity_issues = check_integrity(db_path)

    # Check foreign keys
    fk_ok, fk_issues = check_foreign_keys(db_path)

    # Get sizes
    sizes = check_database_size(db_path)

    # Get stats
    stats = get_database_stats(db_path)

    # Get table stats
    table_stats = get_table_stats(db_path)

    return {
        "healthy": integrity_ok and fk_ok,
        "timestamp": datetime.now().isoformat(),
        "database_path": db_path,
        "integrity": {
            "ok": integrity_ok,
            "issues": integrity_issues,
        },
        "foreign_keys": {
            "ok": fk_ok,
            "issues": fk_issues,
        },
        "sizes": sizes,
        "stats": stats,
        "tables": table_stats,
    }


def print_health_report(report: dict) -> None:
    """Print a formatted health report."""
    print("=" * 80)
    print("DATABASE HEALTH CHECK REPORT")
    print("=" * 80)
    print()

    if "error" in report:
        print(f"❌ ERROR: {report['error']}")
        return

    # Overall status
    if report["healthy"]:
        print("✅ DATABASE STATUS: HEALTHY")
    else:
        print("❌ DATABASE STATUS: ISSUES FOUND")

    print(f"Timestamp: {report['timestamp']}")
    print(f"Database: {report['database_path']}")
    print()

    # Integrity check
    print("-" * 80)
    print("INTEGRITY CHECK")
    print("-" * 80)
    if report["integrity"]["ok"]:
        print("✅ PASSED")
    else:
        print("❌ FAILED")
        print("\nIssues found:")
        for issue in report["integrity"]["issues"][:10]:  # Show first 10
            print(f"  - {issue}")
        if len(report["integrity"]["issues"]) > 10:
            print(f"  ... and {len(report['integrity']['issues']) - 10} more issues")
    print()

    # Foreign key check
    print("-" * 80)
    print("FOREIGN KEY CHECK")
    print("-" * 80)
    if report["foreign_keys"]["ok"]:
        print("✅ PASSED")
    else:
        print("❌ FAILED")
        print("\nIssues found:")
        for issue in report["foreign_keys"]["issues"]:
            print(f"  - {issue}")
    print()

    # Sizes
    print("-" * 80)
    print("DATABASE SIZE")
    print("-" * 80)
    sizes = report["sizes"]
    if "error" not in sizes:
        print(f"Database file:  {sizes['database_mb']:>10.2f} MB")
        print(f"WAL file:       {sizes['wal_mb']:>10.2f} MB")
        print(f"SHM file:       {sizes['shm_mb']:>10.2f} MB")
        print(f"Total:          {sizes['total_mb']:>10.2f} MB")
    else:
        print(f"Error: {sizes['error']}")
    print()

    # Stats
    print("-" * 80)
    print("DATABASE STATISTICS")
    print("-" * 80)
    stats = report["stats"]
    if "error" not in stats:
        print(f"Journal mode:        {stats['journal_mode']}")
        print(f"Page count:          {stats['page_count']:,}")
        print(f"Page size:           {stats['page_size']:,} bytes")
        print(f"Freelist count:      {stats['freelist_count']:,}")
        print(f"Fragmentation:       {stats['fragmentation_percent']:.2f}%")
        print(f"Table count:         {stats['table_count']}")

        if stats['fragmentation_percent'] > 20:
            print("\n⚠️  Warning: High fragmentation detected. Consider running VACUUM.")
    else:
        print(f"Error: {stats['error']}")
    print()

    # Table stats
    print("-" * 80)
    print("TABLE STATISTICS")
    print("-" * 80)
    if report["tables"] and "error" not in report["tables"][0]:
        for table_stat in report["tables"]:
            print(f"{table_stat['table']:40} {table_stat['rows']:>12,} rows")
    else:
        print("Error getting table statistics")
    print()

    # Recommendations
    print("=" * 80)
    print("RECOMMENDATIONS")
    print("=" * 80)

    if not report["healthy"]:
        print("❌ Database has integrity issues. Consider:")
        print("   1. Running the recovery script: python scripts/recover_database.py")
        print("   2. Restoring from a recent backup")
        print("   3. Re-importing data from AMO CRM")

    if stats.get("fragmentation_percent", 0) > 20:
        print("⚠️  High fragmentation detected. Run: sqlite3 amo_data.db VACUUM")

    if sizes.get("wal_mb", 0) > 100:
        print("⚠️  Large WAL file. Consider checkpointing: PRAGMA wal_checkpoint(TRUNCATE)")

    if report["healthy"] and stats.get("fragmentation_percent", 0) <= 20:
        print("✅ Database is in good health. No action needed.")

    print()


def main() -> None:
    """Main entry point."""
    db_path = "amo_data.db"

    if len(sys.argv) > 1:
        db_path = sys.argv[1]

    report = run_health_check(db_path)
    print_health_report(report)

    # Exit with error code if unhealthy
    sys.exit(0 if report.get("healthy", False) else 1)


if __name__ == "__main__":
    main()