"""Inspecta el contingut de demo/temp/audiodescriptions.db.

Mostra:
- Si el fitxer existeix
- Nombre total de files
- Parelles (sha1sum, version)
- Resultats de consultes parametritzades vs literals per a uns sha1sum/version de mostra
"""

from __future__ import annotations

import json
import sqlite3
from pathlib import Path

DB_PATH = Path(__file__).resolve().parent.parent / "temp" / "audiodescriptions.db"

SAMPLE_SHA1S = (
    "8ff4b2aaccfeee31ecc59b96e1ae90273de78864",
    "3df04d2b7df70210fcceb7b9d9a35731bb43a39c",
    "150f0d2abfe26602e38dc3cc1a0030d16c8ed0a2",
)
SAMPLE_VERSIONS = ("Salamandra", "MoE")


def main() -> None:
    print(f"DB path: {DB_PATH}")
    if not DB_PATH.exists():
        print("❌ DB file does not exist")
        return

    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()

    cur.execute("SELECT COUNT(*) FROM audiodescriptions")
    total = cur.fetchone()[0]
    print(f"Total rows: {total}")
    
    # Ver esquema de la tabla
    cur.execute("PRAGMA table_info(audiodescriptions)")
    columns = cur.fetchall()
    print("Columns:", [col[1] for col in columns])

    cur.execute(
        "SELECT sha1sum, version FROM audiodescriptions ORDER BY sha1sum, version"
    )
    pairs = [dict(row) for row in cur.fetchall()]
    print("Pairs:")
    print(json.dumps(pairs, ensure_ascii=False, indent=2))

    for sha1 in SAMPLE_SHA1S:
        for version in SAMPLE_VERSIONS:
            cur.execute(
                "SELECT COUNT(*) FROM audiodescriptions WHERE sha1sum=? AND version=?",
                (sha1, version),
            )
            count_param = cur.fetchone()[0]

            cur.execute(
                "SELECT COUNT(*) FROM audiodescriptions WHERE sha1sum=:sha AND version=:ver",
                {"sha": sha1, "ver": version},
            )
            count_named = cur.fetchone()[0]

            cur.execute(
                f"SELECT COUNT(*) FROM audiodescriptions WHERE sha1sum='{sha1}' AND version='{version}'"
            )
            count_literal = cur.fetchone()[0]
            
            # Test con LOWER() - igual que la función (sin updated_at/created_at)
            cur.execute(
                "SELECT * FROM audiodescriptions WHERE sha1sum = ? AND LOWER(version) = LOWER(?) ORDER BY rowid DESC LIMIT 1",
                (sha1, version),
            )
            rows_lower = cur.fetchall()
            count_lower = len(rows_lower)

            print(
                f"sha1={sha1} version={version} -> param={count_param} "
                f"named={count_named} literal={count_literal} SELECT*_LOWER={count_lower}"
            )

    conn.close()


if __name__ == "__main__":
    main()