Melo/lib/library/database.dart

import 'dart:io';

import 'package:drift/drift.dart';
import 'package:flutter/foundation.dart' show visibleForTesting;
import 'package:drift/native.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'package:uuid/uuid.dart';

part 'database.g.dart';

/// Songs. Sync-fähig ab Tag 1: [id] ist eine stabile UUID, [updatedAtMs] und
/// [deleted] (Tombstone) ermöglichen späteren Cloud-Sync ohne Schema-Umbau.
class Songs extends Table {
  TextColumn get id => text()(); // uuid
  TextColumn get path => text().unique()();
  TextColumn get title => text()();
  TextColumn get artist => text().nullable()();
  TextColumn get album => text().nullable()();
  IntColumn get durationMs => integer().nullable()();
  TextColumn get coverPath => text().nullable()();
  IntColumn get dateAddedMs => integer()();
  IntColumn get updatedAtMs => integer()();
  BoolColumn get deleted => boolean().withDefault(const Constant(false))();

  /// Wie oft der Song vollständig gestartet wurde — Grundlage für die
  /// Sortierung "Wie oft abgespielt".
  IntColumn get playCount => integer().withDefault(const Constant(0))();

  /// Sobald die Kategorien eines Songs von Hand geändert wurden, überschreibt
  /// ein erneuter Scan sie nicht mehr mit dem Genre-Tag der Datei.
  BoolColumn get categoriesEdited => boolean().withDefault(const Constant(false))();

  /// Sobald Titel, Künstler oder Album von Hand korrigiert wurden, überschreibt
  /// ein erneuter Scan sie nicht mehr mit den Tags der Datei.
  BoolColumn get metadataEdited => boolean().withDefault(const Constant(false))();

  /// Songtext aus dem Tag der Datei — Grundlage für den automatischen
  /// Songtext ohne Server.
  TextColumn get lyrics => text().nullable()();

  /// ReplayGain des Titels in Dezibel, sofern die Datei den Tag mitbringt —
  /// Grundlage für "Gleiche Lautstärke".
  RealColumn get gainDb => real().nullable()();

  /// ID desselben Titels in der Melo-Cloud. Verbindet den Titel auf dem Gerät
  /// mit dem am Server und ist die Grundlage des Abgleichs: ohne sie gilt ein
  /// Titel als nur lokal vorhanden und wird beim nächsten Sync hochgeladen.
  TextColumn get cloudId => text().nullable()();

  @override
  Set<Column> get primaryKey => {id};
}

/// Vom Nutzer gewählte Musikordner, die gescannt werden.
class Folders extends Table {
  TextColumn get id => text()(); // uuid
  TextColumn get path => text().unique()();
  IntColumn get updatedAtMs => integer()();
  BoolColumn get deleted => boolean().withDefault(const Constant(false))();

  @override
  Set<Column> get primaryKey => {id};
}

/// Nutzer-Playlisten.
class Playlists extends Table {
  TextColumn get id => text()(); // uuid
  TextColumn get name => text()();
  TextColumn get description => text().nullable()();
  IntColumn get createdAtMs => integer()();
  IntColumn get updatedAtMs => integer()();
  BoolColumn get deleted => boolean().withDefault(const Constant(false))();

  @override
  Set<Column> get primaryKey => {id};
}

/// Zuordnung Song ↔ Playlist mit Reihenfolge.
class PlaylistSongs extends Table {
  TextColumn get playlistId => text().references(Playlists, #id)();
  TextColumn get songId => text().references(Songs, #id)();
  IntColumn get position => integer()();
  IntColumn get addedAtMs => integer()();

  @override
  Set<Column> get primaryKey => {playlistId, songId};
}

/// Kategorien eines Songs — ein Song kann mehreren angehören. [position]
/// hält die Reihenfolge fest; die erste Kategorie bestimmt das Cover.
class SongCategories extends Table {
  TextColumn get songId => text().references(Songs, #id)();
  TextColumn get name => text()();
  IntColumn get position => integer()();

  @override
  Set<Column> get primaryKey => {songId, name};
}

/// Favorisierte Songs.
class Favorites extends Table {
  TextColumn get songId => text().references(Songs, #id)();
  IntColumn get createdAtMs => integer()();

  @override
  Set<Column> get primaryKey => {songId};
}

/// Wiedergabe-Historie: letzte Position pro Song (für Resume) + Verlauf.
class PlaybackHistory extends Table {
  TextColumn get songId => text().references(Songs, #id)();
  IntColumn get positionMs => integer()();
  IntColumn get playedAtMs => integer()();

  @override
  Set<Column> get primaryKey => {songId, playedAtMs};
}

/// Bewusst heruntergeladene Server-Titel.
///
/// Die Dateien allein würden nicht reichen: sie heißen nach einem Hash, und
/// ohne diese Zeilen könnte die App nur „37 Dateien, 412 MB" zeigen statt
/// einer Liste mit Titeln und Künstlern.
class Downloads extends Table {
  TextColumn get navidromeId => text()();
  TextColumn get titel => text()();
  TextColumn get kuenstler => text().nullable()();
  TextColumn get album => text().nullable()();
  IntColumn get groesseBytes => integer()();
  IntColumn get geladenAmMs => integer()();

  @override
  Set<Column> get primaryKey => {navidromeId};
}

@DriftDatabase(tables: [
  Songs,
  Folders,
  Playlists,
  PlaylistSongs,
  Favorites,
  PlaybackHistory,
  SongCategories,
  Downloads,
])
class MeloDb extends _$MeloDb {
  MeloDb([QueryExecutor? executor]) : super(executor ?? _open());

  @override
  int get schemaVersion => 10;

  @override
  MigrationStrategy get migration => MigrationStrategy(
        onCreate: (m) => m.createAll(),
        onUpgrade: (m, from, to) async {
          if (from < 2) {
            await m.createTable(playlists);
            await m.createTable(playlistSongs);
            await m.createTable(favorites);
            await m.createTable(playbackHistory);
          }
          if (from < 3) {
            await m.addColumn(songs, songs.playCount);
          }
          if (from < 4) {
            await m.addColumn(songs, songs.categoriesEdited);
            await m.createTable(songCategories);
          }
          if (from < 5) {
            await m.addColumn(songs, songs.lyrics);
          }
          if (from < 6) {
            await m.addColumn(songs, songs.gainDb);
          }
          if (from < 7) {
            await m.addColumn(songs, songs.metadataEdited);
          }
          if (from < 8) {
            await m.addColumn(songs, songs.cloudId);
          }
          if (from < 9) {
            // Album-Titel gelten ab jetzt als Kategorie (siehe
            // kategorienAusTags). Ohne diese Nachrüstung bekämen bestehende
            // Bibliotheken das erst beim nächsten vollständigen Scan zu
            // sehen — der auf Android nur läuft, wenn sich die Anzahl der
            // Dateien ändert.
            await _ergaenzeAlbumKategorien();
          }
          if (from < 10) {
            await m.createTable(downloads);
          }
        },
      );

  /// Überwacht alle Songs (nicht getombstonte). Nach Titel sortiert.
  Stream<List<Song>> watchSongs() {
    return (select(songs)
          ..where((s) => s.deleted.equals(false))
          ..orderBy([(s) => OrderingTerm(expression: s.title)]))
        .watch();
  }

  /// Überwacht die zuletzt hinzugefügten Songs (nicht getombstonte).
  Stream<List<Song>> watchRecent({int limit = 50}) {
    return (select(songs)
          ..where((s) => s.deleted.equals(false))
          ..orderBy([
            (s) => OrderingTerm(expression: s.dateAddedMs, mode: OrderingMode.desc)
          ])
          ..limit(limit))
        .watch();
  }

  /// Überwacht die zuletzt GEHÖRTEN Songs (nicht getombstonte), aus
  /// [PlaybackHistory]. Ein mehrfach gespielter Song erscheint nur einmal —
  /// an der Position seines jüngsten Abspielens. Dafür wird pro [songId]
  /// zuerst der jüngste `playedAtMs`-Eintrag ermittelt (Subquery mit
  /// `GROUP BY`), und darüber zurück auf [songs] verknüpft.
  Stream<List<Song>> watchRecentlyPlayed({int limit = 50}) {
    final letzterPlay = playbackHistory.playedAtMs.max();
    final letztePlaysProSong = selectOnly(playbackHistory)
      ..addColumns([playbackHistory.songId, letzterPlay])
      ..groupBy([playbackHistory.songId]);
    final subquery = Subquery(letztePlaysProSong, 'letzte_plays');
    final letzterPlayRef = subquery.ref(letzterPlay);

    final query = select(songs).join([
      innerJoin(
        subquery,
        subquery.ref(playbackHistory.songId).equalsExp(songs.id),
        useColumns: false,
      ),
    ])
      ..where(songs.deleted.equals(false))
      ..orderBy([
        OrderingTerm(expression: letzterPlayRef, mode: OrderingMode.desc)
      ])
      ..limit(limit);

    return query.watch().map((rows) => rows.map((r) => r.readTable(songs)).toList());
  }


  /// Alle Songs inkl. getombstonte. Intern für ID-Stabilität beim Scan.
  Future<List<Song>> allSongs() => select(songs).get();

  /// Wie viele Titel die Bibliothek kennt (ohne getombstonte).
  Future<int> countSongs() async {
    final anzahl = songs.id.count();
    final zeile = await (selectOnly(songs)
          ..addColumns([anzahl])
          ..where(songs.deleted.equals(false)))
        .getSingle();
    return zeile.read(anzahl) ?? 0;
  }

  /// Upsert Songs nach Pfad. Neue Songs bekommen UUID; bekannte erhalten sie zurück.
  /// Setzt [updatedAtMs] auf die Werte der Companions (normalerweise Scan-Startzeit).
  Future<void> upsertSongs(List<SongsCompanion> items) async {
    await batch((b) => b.insertAllOnConflictUpdate(songs, items));
  }

  /// Tombstone für Songs, deren Datei beim Scan nicht mehr gefunden wurde.
  /// Löscht nur Songs, die in diesem Scan (Zeitstempel [now]) nicht aktualisiert wurden.
  Future<void> markMissing(int now) async {
    await (update(songs)
          ..where((s) =>
              s.deleted.equals(false) &
              s.updatedAtMs.isSmallerThanValue(now)))
        .write(
          SongsCompanion(deleted: const Value(true), updatedAtMs: Value(now)),
        );
  }

  Stream<List<Folder>> watchFolders() =>
      (select(folders)..where((f) => f.deleted.equals(false))).watch();

  Future<List<Folder>> activeFolders() =>
      (select(folders)..where((f) => f.deleted.equals(false))).get();

  Future<void> addFolder(FoldersCompanion folder) =>
      into(folders).insert(folder, onConflict: DoUpdate((_) => folder, target: [folders.path]));

  static const _uuid = Uuid();

  // === Playlists ===
  Stream<List<Playlist>> watchPlaylists() =>
      (select(playlists)
            ..where((p) => p.deleted.equals(false))
            ..orderBy([(p) => OrderingTerm(expression: p.createdAtMs, mode: OrderingMode.desc)]))
          .watch();

  Future<String> createPlaylist(String name, {String? description}) async {
    final id = _uuid.v4();
    final now = DateTime.now().millisecondsSinceEpoch;
    await into(playlists).insert(PlaylistsCompanion.insert(
      id: id,
      name: name,
      description: Value(description),
      createdAtMs: now,
      updatedAtMs: now,
    ));
    return id;
  }

  Future<void> deletePlaylist(String id) async {
    await (update(playlists)..where((p) => p.id.equals(id))).write(
      PlaylistsCompanion(
        deleted: const Value(true),
        updatedAtMs: Value(DateTime.now().millisecondsSinceEpoch),
      ),
    );
  }

  // === PlaylistSongs ===
  Stream<List<Song>> watchPlaylistSongs(String playlistId) {
    final query = select(songs).join([
      innerJoin(playlistSongs, playlistSongs.songId.equalsExp(songs.id)),
    ])
      ..where(playlistSongs.playlistId.equals(playlistId))
      ..orderBy([OrderingTerm(expression: playlistSongs.position)]);
    return query.watch().map((rows) => rows.map((r) => r.readTable(songs)).toList());
  }

  Future<void> addSongToPlaylist(String playlistId, String songId, int position) async {
    await into(playlistSongs).insert(
      PlaylistSongsCompanion.insert(
        playlistId: playlistId,
        songId: songId,
        position: position,
        addedAtMs: DateTime.now().millisecondsSinceEpoch,
      ),
      mode: InsertMode.insertOrReplace,
    );
  }

  Future<void> removeSongFromPlaylist(String playlistId, String songId) async {
    await (delete(playlistSongs)
          ..where((ps) => ps.playlistId.equals(playlistId) & ps.songId.equals(songId)))
        .go();
  }

  Future<void> reorderPlaylistSong(String playlistId, String songId, int newPosition) async {
    await (update(playlistSongs)
          ..where((ps) => ps.playlistId.equals(playlistId) & ps.songId.equals(songId)))
        .write(PlaylistSongsCompanion(position: Value(newPosition)));
  }

  /// Schreibt für die gesamte Playlist neue, lückenlose Positionen (0..N-1)
  /// gemäß [orderedSongIds] — vermeidet Mehrdeutigkeiten beim Umsortieren,
  /// da einzelne [reorderPlaylistSong]-Aufrufe nur einen Song verschieben.
  Future<void> reorderAllPlaylistSongs(String playlistId, List<String> orderedSongIds) async {
    await transaction(() async {
      for (var i = 0; i < orderedSongIds.length; i++) {
        await (update(playlistSongs)
              ..where((ps) =>
                  ps.playlistId.equals(playlistId) & ps.songId.equals(orderedSongIds[i])))
            .write(PlaylistSongsCompanion(position: Value(i)));
      }
    });
  }

  // === Favorites ===
  Future<bool> songExists(String songId) async {
    final row = await (select(songs)..where((s) => s.id.equals(songId))).getSingleOrNull();
    return row != null;
  }

  Future<void> setFavorite(String songId, bool isFavorite) async {
    final existing = await (select(favorites)..where((f) => f.songId.equals(songId))).getSingleOrNull();
    if (isFavorite && existing == null) {
      await into(favorites).insert(FavoritesCompanion.insert(
        songId: songId,
        createdAtMs: DateTime.now().millisecondsSinceEpoch,
      ));
    } else if (!isFavorite && existing != null) {
      await (delete(favorites)..where((f) => f.songId.equals(songId))).go();
    }
  }

  Future<void> toggleFavorite(String songId) async {
    final existing = await (select(favorites)..where((f) => f.songId.equals(songId))).getSingleOrNull();
    if (existing == null) {
      await into(favorites).insert(FavoritesCompanion.insert(
        songId: songId,
        createdAtMs: DateTime.now().millisecondsSinceEpoch,
      ));
    } else {
      await (delete(favorites)..where((f) => f.songId.equals(songId))).go();
    }
  }

  Stream<bool> watchIsFavorite(String songId) {
    return (select(favorites)..where((f) => f.songId.equals(songId)))
        .watchSingleOrNull()
        .map((row) => row != null);
  }

  Stream<List<Song>> watchFavorites() {
    final query = select(songs).join([
      innerJoin(favorites, favorites.songId.equalsExp(songs.id)),
    ])
      ..orderBy([OrderingTerm(expression: favorites.createdAtMs, mode: OrderingMode.desc)]);
    return query.watch().map((rows) => rows.map((r) => r.readTable(songs)).toList());
  }

  /// Songtext eines Songs, sofern beim Scan einer im Tag gefunden wurde.
  Future<String?> lyricsOf(String songId) async {
    final row = await (select(songs)..where((s) => s.id.equals(songId)))
        .getSingleOrNull();
    final text = row?.lyrics?.trim();
    return (text == null || text.isEmpty) ? null : text;
  }

  // === Kategorien ===
  /// Alle Kategorien-Zuordnungen, nach Song gebündelt und in gespeicherter
  /// Reihenfolge — die UI braucht sie immer für die ganze sichtbare Liste.
  Stream<Map<String, List<String>>> watchCategoriesBySong() {
    return (select(songCategories)
          ..orderBy([(c) => OrderingTerm(expression: c.position)]))
        .watch()
        .map((rows) {
      final grouped = <String, List<String>>{};
      for (final row in rows) {
        grouped.putIfAbsent(row.songId, () => []).add(row.name);
      }
      return grouped;
    });
  }

  Future<List<String>> categoriesOf(String songId) async {
    final rows = await (select(songCategories)
          ..where((c) => c.songId.equals(songId))
          ..orderBy([(c) => OrderingTerm(expression: c.position)]))
        .get();
    return rows.map((r) => r.name).toList();
  }

  /// Ersetzt die Kategorien eines Songs. [byUser] markiert den Song als von
  /// Hand bearbeitet, sodass der nächste Scan ihn in Ruhe lässt.
  Future<void> setCategories(
    String songId,
    List<String> names, {
    bool byUser = false,
  }) async {
    await transaction(() async {
      await (delete(songCategories)..where((c) => c.songId.equals(songId))).go();
      for (var i = 0; i < names.length; i++) {
        await into(songCategories).insert(SongCategoriesCompanion.insert(
          songId: songId,
          name: names[i],
          position: i,
        ));
      }
      if (byUser) {
        await (update(songs)..where((s) => s.id.equals(songId)))
            .write(const SongsCompanion(categoriesEdited: Value(true)));
      }
    });
  }

  /// Alle vergebenen Kategorien, alphabetisch — für Vorschläge beim Bearbeiten.
  Future<List<String>> allCategoryNames() async {
    final rows = await (selectOnly(songCategories, distinct: true)
          ..addColumns([songCategories.name]))
        .get();
    final names = rows.map((r) => r.read(songCategories.name)!).toList()
      ..sort((a, b) => a.toLowerCase().compareTo(b.toLowerCase()));
    return names;
  }

  // === Playback History ===
  /// Zählt eine Wiedergabe. Wird beim Start eines Tracks aufgerufen, nicht beim
  /// periodischen Speichern der Position — sonst würde der Zähler hochlaufen,
  /// solange ein Song nur läuft.
  Future<void> incrementPlayCount(String songId) async {
    await customUpdate(
      'UPDATE songs SET play_count = play_count + 1 WHERE id = ?',
      variables: [Variable<String>(songId)],
      updates: {songs},
    );
  }

  Future<void> recordPlayback(String songId, int positionMs) async {
    await into(playbackHistory).insert(PlaybackHistoryCompanion.insert(
      songId: songId,
      positionMs: positionMs,
      playedAtMs: DateTime.now().millisecondsSinceEpoch,
    ));
  }

  /// Der Song mit dieser UUID, oder `null`. Grundlage dafür, aus einer
  /// Adresse im Auto wieder einen Titel zu machen.
  Future<Song?> songById(String id) =>
      (select(songs)..where((s) => s.id.equals(id))).getSingleOrNull();

  /// Der Song mit genau diesem Dateipfad, oder `null`.
  Future<Song?> songByPath(String path) =>
      (select(songs)..where((s) => s.path.equals(path))).getSingleOrNull();

  /// Verwirft das Album-Tag eines Titels und markiert ihn als von Hand
  /// bearbeitet, damit der nächste Scan es nicht wieder hereinholt.
  /// Für YouTube-Downloads: deren Album-Tag ist keine sinnvolle Kategorie.
  Future<void> verwirfAlbum(String songId) async {
    await (update(songs)..where((s) => s.id.equals(songId))).write(
      const SongsCompanion(
        album: Value(null),
        metadataEdited: Value(true),
      ),
    );
  }

  /// Nur für Tests: führt die Nachrüstung aus Schema 9 auf einer bereits
  /// angelegten Datenbank aus.
  @visibleForTesting
  Future<void> ergaenzeAlbumKategorienFuerTest() => _ergaenzeAlbumKategorien();

  /// Trägt für jeden Titel seinen Album-Namen als **erste** Kategorie nach.
  /// Von Hand gepflegte Kategorien (`categoriesEdited`) bleiben unberührt.
  Future<void> _ergaenzeAlbumKategorien() async {
    const betroffen = "SELECT id, TRIM(album) AS album_name FROM songs "
        "WHERE album IS NOT NULL AND TRIM(album) <> '' "
        "AND categories_edited = 0";
    // 1) War der Album-Name schon Kategorie, kommt er gleich wieder vorn
    //    dazu — den alten Eintrag deshalb entfernen.
    await customStatement(
      'DELETE FROM song_categories WHERE EXISTS ('
      'SELECT 1 FROM ($betroffen) b '
      'WHERE b.id = song_categories.song_id AND b.album_name = song_categories.name)',
    );
    // 2) Platz an Position 0 schaffen.
    await customStatement(
      'UPDATE song_categories SET position = position + 1 '
      'WHERE song_id IN (SELECT id FROM ($betroffen))',
    );
    // 3) Album-Name als erste Kategorie setzen.
    await customStatement(
      'INSERT INTO song_categories (song_id, name, position) '
      'SELECT id, album_name, 0 FROM ($betroffen)',
    );
  }

  // === Cloud-Sync ===
  /// Verknüpft einen Titel des Geräts mit seinem Gegenstück in der Cloud.
  Future<void> setCloudId(String songId, String cloudId) async {
    await (update(songs)..where((s) => s.id.equals(songId)))
        .write(SongsCompanion(cloudId: Value(cloudId)));
  }

  /// Titel, die am Server gelöscht wurden, auch auf dem Gerät als gelöscht
  /// markieren. Grabstein statt echtem Löschen — sonst legt der nächste Scan
  /// sie wieder an.
  Future<void> tombstoneByCloudIds(List<String> cloudIds) async {
    if (cloudIds.isEmpty) return;
    final now = DateTime.now().millisecondsSinceEpoch;
    await (update(songs)..where((s) => s.cloudId.isIn(cloudIds))).write(
      SongsCompanion(deleted: const Value(true), updatedAtMs: Value(now)),
    );
  }

  /// Alle Favoriten-Song-IDs — Grundlage für den Favoriten-Abgleich.
  Future<List<String>> favoriteSongIds() async {
    final rows = await select(favorites).get();
    return [for (final r in rows) r.songId];
  }

  /// Wiedergaben seit [sinceMs], neueste zuerst. Grundlage dafür, dem Server
  /// zu melden, was auf diesem Gerät gehört wurde.
  Future<List<PlaybackHistoryData>> historySince(int sinceMs,
      {int limit = 100}) async {
    return (select(playbackHistory)
          ..where((h) => h.playedAtMs.isBiggerThanValue(sinceMs))
          ..orderBy([
            (h) => OrderingTerm(
                expression: h.playedAtMs, mode: OrderingMode.desc)
          ])
          ..limit(limit))
        .get();
  }

  // === Downloads ===
  Stream<List<Download>> watchDownloads() => (select(downloads)
        ..orderBy([
          (d) => OrderingTerm(expression: d.geladenAmMs, mode: OrderingMode.desc)
        ]))
      .watch();

  Future<void> merkeDownload(DownloadsCompanion eintrag) =>
      into(downloads).insert(eintrag, mode: InsertMode.insertOrReplace);

  Future<void> vergissDownload(String navidromeId) => (delete(downloads)
        ..where((d) => d.navidromeId.equals(navidromeId)))
      .go();

  Future<void> vergissAlleDownloads() => delete(downloads).go();

  /// Server-IDs aller heruntergeladenen Titel.
  ///
  /// Einmal je Bildschirm statt einmal je Zeile: die Titelliste eines Albums
  /// zeichnet sich bei jedem Positionswechsel neu, und eine Datei-Abfrage pro
  /// Zeile und Neuzeichnen wäre eine Prüfung pro Sekunde und Titel.
  Future<Set<String>> downloadIds() async {
    final zeilen = await (selectOnly(downloads)
          ..addColumns([downloads.navidromeId]))
        .get();
    return {for (final z in zeilen) z.read(downloads.navidromeId)!};
  }


  Future<int?> lastPosition(String songId) async {
    final row = await (select(playbackHistory)
          ..where((h) => h.songId.equals(songId))
          ..orderBy([(h) => OrderingTerm(expression: h.playedAtMs, mode: OrderingMode.desc)])
          ..limit(1))
        .getSingleOrNull();
    return row?.positionMs;
  }
}

LazyDatabase _open() {
  return LazyDatabase(() async {
    final dir = await getApplicationSupportDirectory();
    final file = File(p.join(dir.path, 'melo.sqlite'));
    return NativeDatabase.createInBackground(file);
  });
}