import sqlite3
import os
from pathlib import Path

DB_DIR = r"\\tower\JNJEMAILS\db"
MSG_DIR = r"\\tower\JNJEMAILS"


def find_latest_db(db_dir):
    dbs = sorted(Path(db_dir).glob("*.db"), key=lambda p: p.stat().st_mtime)
    if not dbs:
        raise FileNotFoundError(f"Žádná .db databáze v {db_dir}")
    return dbs[-1]


def count_msg_files(msg_dir):
    return sum(1 for f in Path(msg_dir).iterdir() if f.suffix.lower() == ".msg")


def stats(db_path):
    con = sqlite3.connect(db_path)
    cur = con.cursor()

    total = cur.execute("SELECT COUNT(*) FROM messages").fetchone()[0]

    date_range = cur.execute(
        "SELECT MIN(received_at), MAX(received_at) FROM messages WHERE received_at IS NOT NULL"
    ).fetchone()

    top_senders = cur.execute(
        "SELECT sender, COUNT(*) AS n FROM messages GROUP BY sender ORDER BY n DESC LIMIT 10"
    ).fetchall()

    by_folder = cur.execute(
        "SELECT folder, COUNT(*) AS n FROM messages GROUP BY folder ORDER BY n DESC"
    ).fetchall()

    by_source = cur.execute(
        "SELECT source, COUNT(*) AS n FROM messages GROUP BY source ORDER BY n DESC"
    ).fetchall()

    by_month = cur.execute(
        """SELECT SUBSTR(received_at, 1, 7) AS month, COUNT(*) AS n
           FROM messages WHERE received_at IS NOT NULL
           GROUP BY month ORDER BY month"""
    ).fetchall()

    con.close()
    return total, date_range, top_senders, by_folder, by_source, by_month


def main():
    db_path = find_latest_db(DB_DIR)
    print(f"Databáze: {db_path.name}")
    print(f"Velikost: {db_path.stat().st_size / 1024 / 1024:.1f} MB")

    msg_count = count_msg_files(MSG_DIR)
    print(f".msg souborů ve složce: {msg_count:,}")

    total, date_range, top_senders, by_folder, by_source, by_month = stats(db_path)

    print(f"\n{'-'*50}")
    print(f"  Emailu v databazi: {total:,}")
    if date_range[0]:
        print(f"  Nejstarsi:         {date_range[0]}")
        print(f"  Nejnovejsi:        {date_range[1]}")

    if by_folder:
        print(f"\n  Slozky:")
        for folder, n in by_folder:
            print(f"    {folder or '(bez slozky)':<35} {n:>6,}")

    if by_source:
        print(f"\n  Zdroje (source):")
        for src, n in by_source:
            print(f"    {src or '(prazdny)':<35} {n:>6,}")

    if by_month:
        print(f"\n  Emaily po mesicich:")
        for month, n in by_month:
            bar = "#" * min(n // 20, 40)
            print(f"    {month}  {bar:<40} {n:>5,}")

    if top_senders:
        print(f"\n  Top 10 odesilatelU:")
        for sender, n in top_senders:
            print(f"    {(sender or '(neznamy)')[:50]:<52} {n:>5,}")

    print(f"{'-'*50}")


if __name__ == "__main__":
    main()