<?php
/**
 * Musikbibliothek: Einträge in der DB (Tabelle musik).
 */
if (!defined('FUNCTIONS_ROOT')) {
    return;
}

/**
 * Alle Songs aus der DB inkl. Playlist-Zuordnung (Many-to-Many). Toleriert fehlende playlist_tracks (Migration 011).
 * @return array<int, array{id: int, dateiname: string, titel: string|null, playlist_ids: int[], playlists: string, groesse: int, erstellt_am: string}>
 */
function function_musik_alle(): array {
    $pdo = $GLOBALS['pdo'] ?? null;
    if (!$pdo) return [];
    try {
        $sql = "SELECT m.id, m.dateiname, m.titel, m.groesse, m.erstellt_am,
                (SELECT GROUP_CONCAT(pt.playlist_id) FROM playlist_tracks pt WHERE pt.musik_id = m.id) AS playlist_ids,
                (SELECT GROUP_CONCAT(a.name ORDER BY a.name) FROM playlist_tracks pt JOIN alben a ON a.id = pt.playlist_id WHERE pt.musik_id = m.id) AS playlists
                FROM musik m ORDER BY m.erstellt_am DESC";
        $stmt = $pdo->query($sql);
        if (!$stmt) return [];
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($rows as &$r) {
            $ids = isset($r['playlist_ids']) && $r['playlist_ids'] !== '' ? $r['playlist_ids'] : '';
            $r['playlist_ids'] = $ids === '' ? [] : array_map('intval', explode(',', $ids));
            $r['playlists'] = isset($r['playlists']) ? $r['playlists'] : '';
        }
        return $rows;
    } catch (Throwable $e) {
        try {
            $stmt = $pdo->query("SELECT id, dateiname, titel, groesse, erstellt_am FROM musik ORDER BY erstellt_am DESC");
            if (!$stmt) return [];
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            foreach ($rows as &$r) { $r['playlist_ids'] = []; $r['playlists'] = ''; }
            return $rows;
        } catch (Throwable $e2) {
            return [];
        }
    }
}

/**
 * Einen Song eintragen (nach Upload). Playlist-Zuordnung erfolgt separat über function_alben_playlists_setzen_fuer_musik.
 */
function function_musik_einfuegen(string $dateiname, int $groesse, ?string $titel = null): int {
    $pdo = $GLOBALS['pdo'] ?? null;
    if (!$pdo) return 0;
    $sql = "INSERT INTO musik (dateiname, titel, groesse) VALUES (:dateiname, :titel, :groesse)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        'dateiname' => $dateiname,
        'titel'     => $titel,
        'groesse'   => $groesse,
    ]);
    return $stmt->rowCount() > 0 ? (int) $pdo->lastInsertId() : 0;
}

/**
 * Titel eines Songs aktualisieren. Playlists werden über function_alben_playlists_setzen_fuer_musik gesetzt.
 */
function function_musik_aktualisieren(int $id, ?string $titel = null): bool {
    $pdo = $GLOBALS['pdo'] ?? null;
    if (!$pdo) return false;
    if ($titel === null) return true;
    $stmt = $pdo->prepare("UPDATE musik SET titel = :titel WHERE id = :id");
    $stmt->execute(['titel' => $titel, 'id' => $id]);
    return $stmt->rowCount() >= 0;
}

/**
 * Song anhand Dateiname löschen (DB-Eintrag).
 */
function function_musik_loeschen_nach_dateiname(string $dateiname): bool {
    $pdo = $GLOBALS['pdo'] ?? null;
    if (!$pdo) return false;
    $stmt = $pdo->prepare("DELETE FROM musik WHERE dateiname = :dateiname");
    $stmt->execute(['dateiname' => $dateiname]);
    return $stmt->rowCount() > 0;
}
