query.py

#!/usr/bin/env python3
"""
Query rapida sul DB — eseguibile direttamente sull'host senza Docker.

Interfaccia principale:
  tango query [opzioni]              → brani per data/ora
  tango query catalog [opzioni]      → catalogo brani unici
  tango query stats <tipo>           → statistiche

Default: tango.db (storico normalizzato).
Flag --raw: tracks.db (giornata in corso, non ancora normalizzata).

Esempi diretti:
  python query.py                          # oggi da tango.db
  python query.py --raw                    # oggi da tracks.db
  python query.py 13                       # ore 13:00-13:59 di oggi
  python query.py 13-14                    # ore 13:00-14:59 di oggi
  python query.py 21-23 --date 2026-03-31
  python query.py --date 2026-03-31
  python query.py --from "2026-03-31T21:00" --to "2026-03-31T23:59"
  python query.py --last 20
  python query.py --catalog                        # tutti i brani unici
  python query.py --catalog --orchestra "DI SARLI" # catalogo di un'orchestra
  python query.py --catalog --title "cumparsita"   # cerca un titolo
  python query.py --top-orchestras                 # orchestre più riprodotte
  python query.py --top-titles --limit 20          # titoli più riprodotti (top 20)
  python query.py --top-singers --limit 10         # cantanti più riprodotti
  python query.py --programs                       # passaggi per fascia oraria
"""
import argparse
import os
import re
import sqlite3
from datetime import date

_BASE = os.path.expanduser("~/.local/share/tango-crawler")
TANGO_DB  = os.getenv("NORMALIZED_DB", os.path.join(_BASE, "tango.db"))
TRACKS_DB = os.getenv("DB_PATH",       os.path.join(_BASE, "tracks.db"))


def _tango_query(where: str = "", order: str = "p.fetched_at") -> str:
    """Costruisce la query su tango.db inserendo WHERE prima di GROUP BY."""
    where_clause = f"WHERE {where}" if where else ""
    return f"""
        SELECT p.fetched_at,
               o.name,
               GROUP_CONCAT(s.name, ', '),
               t.name,
               p.year,
               pr.name
        FROM   plays p
        LEFT JOIN orchestras o    ON o.id  = p.orchestra_id
        LEFT JOIN titles t        ON t.id  = p.title_id
        LEFT JOIN programs pr     ON pr.id = p.program_id
        LEFT JOIN play_singers ps ON ps.play_id = p.id
        LEFT JOIN singers s       ON s.id  = ps.singer_id
        {where_clause}
        GROUP BY p.id
        ORDER BY {order}
    """


def _tracks_query(where: str = "", order: str = "id") -> str:
    where_clause = f"WHERE {where}" if where else ""
    return f"SELECT fetched_at, orchestra, singer, track_title, year, program FROM tracks {where_clause} ORDER BY {order}"


def fmt(row) -> str:
    fetched_at, orchestra, singer, track_title, year, program = row
    artist = f"{orchestra} / {singer}" if singer else orchestra or "?"
    title  = track_title or "?"
    suffix = f" ({year})" if year else ""
    slot   = f"[{program}]  " if program else ""
    return f"{fetched_at}  {slot}{artist} — {title}{suffix}"


def parse_hour_range(value: str) -> tuple[str, str]:
    m = re.fullmatch(r"(\d{1,2})(?:-(\d{1,2}))?", value)
    if not m:
        raise argparse.ArgumentTypeError(
            f"Formato non valido: '{value}'. Usa '13' oppure '13-14'."
        )
    h_from = int(m.group(1))
    h_to   = int(m.group(2)) if m.group(2) else h_from
    if not (0 <= h_from <= 23 and 0 <= h_to <= 23):
        raise argparse.ArgumentTypeError("Le ore devono essere tra 0 e 23.")
    return f"{h_from:02d}", f"{h_to:02d}"


def _catalog_query(where: str = "") -> str:
    where_clause = f"WHERE {where}" if where else ""
    return f"""
        SELECT orchestra, title, year, author, times_played, last_seen
        FROM   repertoire
        {where_clause}
        ORDER BY orchestra, title
    """


def fmt_catalog(row) -> str:
    orchestra, title, year, author, times_played, last_seen = row
    year_str   = f" ({year})"    if year   else ""
    author_str = f" [{author}]"  if author else ""
    return f"{orchestra} — {title}{year_str}{author_str}  ×{times_played}  ultimo: {last_seen[:10]}"


def fmt_ranking(rows, label: str = "risultati") -> None:
    if not rows:
        print("Nessun risultato.")
        return
    max_name = max(len(str(r[0])) for r in rows)
    for i, row in enumerate(rows, 1):
        name  = str(row[0])
        count = row[-1]
        extra = f"  ({row[1]})" if len(row) == 3 else ""
        print(f"  {i:>3}.  {name:<{max_name}}{extra}  {count:>6}")
    print(f"\n{len(rows)} {label}.")


_STATS = {
    "top_orchestras": (
        "SELECT o.name, COUNT(*) n FROM plays p "
        "JOIN orchestras o ON o.id = p.orchestra_id "
        "GROUP BY o.id ORDER BY n DESC",
        "orchestre",
    ),
    "top_titles": (
        "SELECT t.name, o.name, COUNT(*) n FROM plays p "
        "JOIN titles t ON t.id = p.title_id "
        "JOIN orchestras o ON o.id = p.orchestra_id "
        "GROUP BY p.title_id ORDER BY n DESC",
        "titoli",
    ),
    "top_singers": (
        "SELECT s.name, COUNT(*) n FROM play_singers ps "
        "JOIN singers s ON s.id = ps.singer_id "
        "GROUP BY s.id ORDER BY n DESC",
        "cantanti",
    ),
    "programs": (
        "SELECT pr.name, COUNT(*) n FROM plays p "
        "JOIN programs pr ON pr.id = p.program_id "
        "GROUP BY pr.id ORDER BY n DESC",
        "fasce orarie",
    ),
}


def _run_stats(key: str, limit: int) -> None:
    sql, label = _STATS[key]
    lc = f" LIMIT {limit}" if limit else ""
    conn = sqlite3.connect(TANGO_DB)
    rows = conn.execute(sql + lc).fetchall()
    conn.close()
    fmt_ranking(rows, label)


def main():
    parser = argparse.ArgumentParser(description="Interroga il DB dei brani")
    parser.add_argument("hours",  nargs="?",  help="Fascia oraria: '13' o '13-14'")
    parser.add_argument("--from", dest="from_ts", help="Da timestamp (YYYY-MM-DDTHH:MM)")
    parser.add_argument("--to",   dest="to_ts",   help="A timestamp  (YYYY-MM-DDTHH:MM)")
    parser.add_argument("--date", help="Giorno (YYYY-MM-DD), default oggi")
    parser.add_argument("--last", type=int, default=0, help="Ultimi N record")
    parser.add_argument("--raw",  action="store_true",
                        help="Usa tracks.db (giornata in corso) invece di tango.db")
    parser.add_argument("--catalog", action="store_true",
                        help="Catalogo brani unici (orchestra+titolo) con conteggio passaggi")
    parser.add_argument("--orchestra", help="Filtra catalogo per orchestra (LIKE, case-insensitive)")
    parser.add_argument("--title",     help="Filtra catalogo per titolo (LIKE, case-insensitive)")
    parser.add_argument("--top-orchestras", action="store_true",
                        help="Orchestre ordinate per numero di passaggi")
    parser.add_argument("--top-titles", action="store_true",
                        help="Titoli ordinati per numero di passaggi")
    parser.add_argument("--top-singers", action="store_true",
                        help="Cantanti ordinati per numero di passaggi")
    parser.add_argument("--programs", action="store_true",
                        help="Passaggi per fascia di palinsesto")
    parser.add_argument("--limit", type=int, default=0,
                        help="Limita il numero di righe stampate")
    args = parser.parse_args()

    limit_clause = f" LIMIT {args.limit}" if args.limit else ""

    for key in _STATS:
        if getattr(args, key, False):
            _run_stats(key, args.limit)
            return

    if args.catalog:
        conn = sqlite3.connect(TANGO_DB)
        filters = []
        params = []
        if args.orchestra:
            filters.append("UPPER(orchestra) LIKE UPPER(?)")
            params.append(f"%{args.orchestra}%")
        if args.title:
            filters.append("UPPER(title) LIKE UPPER(?)")
            params.append(f"%{args.title}%")
        where = " AND ".join(filters)
        rows  = conn.execute(_catalog_query(where) + limit_clause, params).fetchall()
        conn.close()
        if not rows:
            print("Nessun risultato.")
            return
        for row in rows:
            print(fmt_catalog(row))
        print(f"\n{len(rows)} brani nel catalogo.")
        return

    build = _tracks_query if args.raw else _tango_query
    ts    = "fetched_at"  if args.raw else "p.fetched_at"
    db    = TRACKS_DB     if args.raw else TANGO_DB

    # Filtri orchestra/titolo applicabili a tutte le query
    extra_filters = []
    extra_params  = []
    if args.orchestra and not args.raw:
        extra_filters.append("UPPER(o.name) LIKE UPPER(?)")
        extra_params.append(f"%{args.orchestra}%")
    elif args.orchestra:
        extra_filters.append("UPPER(orchestra) LIKE UPPER(?)")
        extra_params.append(f"%{args.orchestra}%")
    if args.title and not args.raw:
        extra_filters.append("UPPER(t.name) LIKE UPPER(?)")
        extra_params.append(f"%{args.title}%")
    elif args.title:
        extra_filters.append("UPPER(track_title) LIKE UPPER(?)")
        extra_params.append(f"%{args.title}%")

    conn = sqlite3.connect(db)

    has_time_filter = args.last or args.hours or args.date or args.from_ts or args.to_ts

    if args.last:
        order = "id DESC" if args.raw else "p.id DESC"
        where_parts = list(extra_filters)
        where = " AND ".join(where_parts)
        rows  = conn.execute(build(where=where, order=order) + " LIMIT ?",
                             extra_params + [args.last]).fetchall()
        rows.reverse()
    elif args.hours:
        day = args.date or date.today().isoformat()
        h_from, h_to = parse_hour_range(args.hours)
        where_parts = [f"{ts} BETWEEN ? AND ?"] + extra_filters
        rows = conn.execute(
            build(where=" AND ".join(where_parts)) + limit_clause,
            [f"{day}T{h_from}:00:00", f"{day}T{h_to}:59:59"] + extra_params,
        ).fetchall()
    elif args.date:
        where_parts = [f"{ts} LIKE ?"] + extra_filters
        rows = conn.execute(
            build(where=" AND ".join(where_parts)) + limit_clause,
            [f"{args.date}%"] + extra_params,
        ).fetchall()
    elif args.from_ts or args.to_ts:
        where_parts = [f"{ts} BETWEEN ? AND ?"] + extra_filters
        rows = conn.execute(
            build(where=" AND ".join(where_parts)) + limit_clause,
            [args.from_ts or "0000-00-00", args.to_ts or "9999-99-99"] + extra_params,
        ).fetchall()
    elif extra_filters:
        where = " AND ".join(extra_filters)
        rows = conn.execute(
            build(where=where) + limit_clause, extra_params,
        ).fetchall()
    else:
        rows = conn.execute(
            build(where=f"{ts} LIKE ?") + limit_clause, (f"{date.today().isoformat()}%",)
        ).fetchall()

    conn.close()

    if not rows:
        print("Nessun risultato.")
        return

    for row in rows:
        print(fmt(row))
    print(f"\n{len(rows)} brani trovati.")


if __name__ == "__main__":
    main()