import sqlite3
import os
import sys
from pathlib import Path

SCRIPTS_DIR = os.path.dirname(os.path.abspath(__file__))
ROOT_DIR = os.path.dirname(SCRIPTS_DIR)
DB_DIR = Path(ROOT_DIR) / "database"


def get_databases():
    if not DB_DIR.exists():
        print(f"Database folder not found: {DB_DIR}")
        sys.exit(1)
    dbs = sorted(DB_DIR.glob("*.sqlite"))
    if not dbs:
        print("No .sqlite files found in database/")
        sys.exit(1)
    return dbs


def check_db(db_path):
    conn = sqlite3.connect(str(db_path), timeout=30)
    conn.row_factory = sqlite3.Row

    total = conn.execute("SELECT COUNT(*) FROM posts").fetchone()[0]
    if total == 0:
        conn.close()
        return None

    images = conn.execute(
        "SELECT COUNT(*) FROM posts WHERE images IS NOT NULL AND images != '' AND images != '[]'"
    ).fetchone()[0]
    ai_title = conn.execute(
        "SELECT COUNT(*) FROM posts WHERE ai_title IS NOT NULL AND ai_title != ''"
    ).fetchone()[0]
    ai_article = conn.execute(
        "SELECT COUNT(*) FROM posts WHERE ai_content IS NOT NULL AND ai_content != ''"
    ).fetchone()[0]

    conn.close()
    return {
        "total": total,
        "images": images,
        "ai_title": ai_title,
        "ai_article": ai_article,
    }


def pct(filled, total):
    return (filled / total * 100) if total > 0 else 0


def bar(filled, total, width=20):
    filled_blocks = int(width * filled / total) if total > 0 else 0
    return "#" * filled_blocks + "-" * (width - filled_blocks)


def main():
    dbs = get_databases()

    grand_total = 0
    grand_images = 0
    grand_ai_title = 0
    grand_ai_article = 0

    for db_path in dbs:
        stats = check_db(db_path)
        if stats is None:
            continue

        name = db_path.stem
        t = stats["total"]
        img = stats["images"]
        title = stats["ai_title"]
        article = stats["ai_article"]

        grand_total += t
        grand_images += img
        grand_ai_title += title
        grand_ai_article += article

        
        print(f"{name},IMAGE {img} dari {t} keyword,TITLE {title} dari {t} keyword,ARTICLE {article} dari {t} keyword")

if __name__ == "__main__":
    main()