import sqlite3
from dataclasses import dataclass
from pathlib import Path


@dataclass(frozen=True)
class Song:
    id: str
    track_name: str
    artist: str
    path: Path


def get_media_by_metadata(artist: str, track_name: str, conn: sqlite3.Connection) -> list[Song]:
    r = conn.execute(
        'select id, title, artist, path from media_file where lower(title) = lower(?) and lower(artist) = lower(?);',
        (track_name, artist)).fetchall()
    results = list()
    for res in r:
        results.append(Song(*res))

    return results


def path_clause(path: str):
    if path.endswith('/'):
        return 'LIKE', '%'
    else:
        return '=', ''


def get_media_by_path(conn: sqlite3.Connection, _path: str | Path) -> tuple[Song, ...]:
    path = str(_path)
    clause_and_suffix = path_clause(path)
    clause = clause_and_suffix[0]
    path = path + clause_and_suffix[1]
    sql = f"""
        SELECT id, title, artist, path FROM media_file 
        WHERE path {clause} ? 
        ORDER BY path;"""

    return tuple(
        Song(*res)
        for res in conn.execute(sql, (path,)).fetchall()
    )


# def get_track_metadata(path: Path) -> Song | None:  # ffmpeg edition
#     if not path.exists():
#         raise RuntimeError(f"{str(path)!r} doesn't exists")
#
#     with Popen(['ffprobe',  '-show_format', '-print_format', 'json', str(path)], stdout=PIPE, stderr=PIPE) as p:
#         stdout, stderr = p.communicate()
#         data = json.loads(stdout)
#         try:
#             return Song(artist=data['format']['tags']['Artist'], track_name=data['format']['tags']['Title'], path=path)
#
#         except Exception as e:
#             print(f"Failed to get metadata for file {path.name}")
#             raise e