import 'dart:async';
import 'dart:convert';

import 'package:dio/dio.dart';
import 'package:drift/drift.dart' as drift;
import 'package:flutter/foundation.dart' show debugPrint;
import 'package:flutter_riverpod/flutter_riverpod.dart';

import '../api/endpoints/me.dart';
import '../api/endpoints/playlists.dart';
import '../auth/auth_provider.dart';
import '../cache/adapters.dart';
import '../cache/audio_cache_manager.dart' show appDbProvider;
import '../cache/cache_first.dart';
import '../cache/connectivity_provider.dart';
import '../cache/db.dart';
import '../library/library_providers.dart' show dioProvider;
import '../models/playlist.dart';
import '../models/system_playlists_status.dart';

final playlistsApiProvider = FutureProvider<PlaylistsApi>((ref) async {
  return PlaylistsApi(await ref.watch(dioProvider.future));
});

/// Drift-first per #357 plan C. Returns cached playlists filtered to
/// match the `kind` family arg:
///   - 'user' → only user-created (systemVariant null), owned by current user
///   - 'system' → only system-generated (systemVariant non-null), owned
///   - 'all' → everything: own user playlists + own system + others' public
///
/// systemVariant column added in drift schema v2 (#357 follow-up); previous
/// behavior leaked system playlists into add-to-playlist sheet because we
/// couldn't filter locally.
final playlistsListProvider =
    StreamProvider.family<PlaylistsList, String>((ref, kind) {
  final db = ref.watch(appDbProvider);
  final user = ref.watch(authControllerProvider).value;

  return cacheFirst<CachedPlaylist, PlaylistsList>(
    driftStream: db.select(db.cachedPlaylists).watch(),
    fetchAndPopulate: () async {
      final api = await ref.read(playlistsApiProvider.future);
      final fresh = await api.list(kind: kind);

      // Reconcile: BuildSystemPlaylists rotates system-playlist UUIDs
      // every rebuild, so insertOrReplace alone leaves stale rows in
      // drift. Tapping one of those stale tiles 404s. Delete every
      // owned drift row whose id isn't in the fresh response, then
      // upsert the fresh set.
      final freshOwnedIds =
          fresh.owned.map((p) => p.id).toSet();
      await db.batch((b) {
        if (user != null) {
          b.deleteWhere(db.cachedPlaylists, (t) {
            return t.userId.equals(user.id) &
                t.id.isNotIn(freshOwnedIds);
          });
        }
        for (final p in fresh.all) {
          b.insert(db.cachedPlaylists, p.toDrift(),
              mode: drift.InsertMode.insertOrReplace);
        }
      });
    },
    toResult: (rows) {
      if (user == null) return PlaylistsList.empty();
      final filtered = rows.where((r) {
        if (kind == 'user') return r.systemVariant == null;
        if (kind == 'system') return r.systemVariant != null;
        return true; // 'all'
      }).toList();
      final owned = filtered
          .where((r) => r.userId == user.id)
          .map((r) => r.toRef())
          .toList();
      final pub = filtered
          .where((r) => r.userId != user.id && r.isPublic)
          .map((r) => r.toRef())
          .toList();
      return PlaylistsList(owned: owned, public: pub);
    },
    isOnline: () async => (await ref
        .read(connectivityProvider.future)
        .timeout(const Duration(seconds: 3), onTimeout: () => true)),
    // Aggregate list — server may add system playlists (For-You /
    // Discover) that the per-user delta sync doesn't always emit.
    // Stale-while-revalidate ensures the home tile row reflects the
    // current server state on every screen visit.
    alwaysRefresh: true,
  );
});

/// Composite shape (playlist + tracks). async* over the playlist watch
/// stream + a one-shot tracks fetch per emission.
final playlistDetailProvider =
    StreamProvider.family<PlaylistDetail, String>((ref, id) async* {
  final db = ref.watch(appDbProvider);
  final user = ref.watch(authControllerProvider).value;

  final playlistQuery = db.select(db.cachedPlaylists)
    ..where((t) => t.id.equals(id));

  final tracksQuery = (db.select(db.cachedPlaylistTracks)
        ..where((t) => t.playlistId.equals(id))
        ..orderBy([(t) => drift.OrderingTerm.asc(t.position)]))
      .join([
    drift.leftOuterJoin(db.cachedTracks,
        db.cachedTracks.id.equalsExp(db.cachedPlaylistTracks.trackId)),
    drift.leftOuterJoin(db.cachedArtists,
        db.cachedArtists.id.equalsExp(db.cachedTracks.artistId)),
    drift.leftOuterJoin(db.cachedAlbums,
        db.cachedAlbums.id.equalsExp(db.cachedTracks.albumId)),
  ]);

  PlaylistDetail emptyDetail() => PlaylistDetail(
        playlist: Playlist(
          id: id,
          userId: user?.id ?? '',
          name: '',
          description: '',
          isPublic: false,
          systemVariant: null,
          trackCount: 0,
          coverUrl: '',
          ownerUsername: '',
          createdAt: '',
          updatedAt: '',
        ),
        tracks: const [],
      );

  Future<bool> isOnline() async {
    try {
      return await ref
          .read(connectivityProvider.future)
          .timeout(const Duration(seconds: 3), onTimeout: () => true);
    } catch (_) {
      return true;
    }
  }

  Future<bool> fetchAndPopulate() async {
    try {
      final api = await ref.read(playlistsApiProvider.future);
      final fresh = await api.get(id).timeout(const Duration(seconds: 10));

      // Collect the track + artist + album rows referenced by these
      // playlist entries. Without writing the cachedTracks rows
      // themselves, the detail-screen JOIN against cachedTracks
      // returns null on every row (only the playlist_tracks join
      // succeeds), so the UI shows a list with empty titles and
      // unplayable tracks.
      final tracks = <String, _TrackRefRow>{};
      final artists = <String, ArtistRefRow>{};
      final albums = <String, AlbumRefRow>{};
      for (final t in fresh.tracks) {
        final tId = t.trackId;
        if (tId != null && tId.isNotEmpty) {
          tracks.putIfAbsent(
            tId,
            () => _TrackRefRow(
              id: tId,
              title: t.title,
              albumId: t.albumId ?? '',
              artistId: t.artistId ?? '',
              durationSec: t.durationSec,
            ),
          );
        }
        final aId = t.artistId;
        final aName = t.artistName;
        if (aId != null && aId.isNotEmpty && aName.isNotEmpty) {
          artists.putIfAbsent(aId, () => ArtistRefRow(aId, aName));
        }
        final lbId = t.albumId;
        final lbTitle = t.albumTitle;
        if (lbId != null && lbId.isNotEmpty && lbTitle.isNotEmpty) {
          albums.putIfAbsent(lbId, () => AlbumRefRow(lbId, lbTitle, aId ?? ''));
        }
      }

      await db.batch((b) {
        b.insert(db.cachedPlaylists, fresh.playlist.toDrift(),
            mode: drift.InsertMode.insertOrReplace);

        // Wipe + re-insert this playlist's track positions so deletions
        // propagate. Without the wipe, removed tracks would linger in
        // drift after the playlist mutated server-side.
        b.deleteWhere(
            db.cachedPlaylistTracks, (t) => t.playlistId.equals(id));

        for (var i = 0; i < fresh.tracks.length; i++) {
          final t = fresh.tracks[i];
          if (t.trackId == null) continue;
          b.insert(
            db.cachedPlaylistTracks,
            CachedPlaylistTracksCompanion.insert(
              playlistId: id,
              trackId: t.trackId!,
              position: drift.Value(i),
            ),
            mode: drift.InsertMode.insertOrReplace,
          );
        }

        if (artists.isNotEmpty) {
          b.insertAllOnConflictUpdate(
            db.cachedArtists,
            artists.values
                .map((a) => CachedArtistsCompanion.insert(
                      id: a.id,
                      name: a.name,
                      sortName: a.name,
                    ))
                .toList(),
          );
        }
        if (tracks.isNotEmpty) {
          // Insert/update cachedTracks so the detail screen's JOIN
          // produces real titles + durations. We don't have
          // track_number / disc_number on the wire (PlaylistTrack
          // omits them), so they default to 0 — UI doesn't surface
          // them on this screen so it's fine.
          b.insertAllOnConflictUpdate(
            db.cachedTracks,
            tracks.values
                .map((t) => CachedTracksCompanion.insert(
                      id: t.id,
                      albumId: t.albumId,
                      artistId: t.artistId,
                      title: t.title,
                      durationMs: drift.Value(t.durationSec * 1000),
                    ))
                .toList(),
          );
        }
        if (albums.isNotEmpty) {
          b.insertAllOnConflictUpdate(
            db.cachedAlbums,
            albums.values
                .map((a) => CachedAlbumsCompanion.insert(
                      id: a.id,
                      artistId: a.artistId,
                      title: a.title,
                      sortTitle: a.title,
                    ))
                .toList(),
          );
        }
      });
      return true;
    } catch (e, st) {
      // 404 = the playlist row in drift is stale (BuildSystemPlaylists
      // rotates UUIDs on each rebuild, so old For-You / Songs-Like
      // tiles can outlive the actual server-side playlist). Wipe the
      // stale row + its track positions so the home tile disappears
      // on next render and we don't keep trying.
      if (e is DioException && e.response?.statusCode == 404) {
        await db.batch((b) {
          b.deleteWhere(
              db.cachedPlaylistTracks, (t) => t.playlistId.equals(id));
          b.deleteWhere(db.cachedPlaylists, (t) => t.id.equals(id));
        });
        return false;
      }
      debugPrint('playlistDetailProvider($id): fetch failed: $e\n$st');
      return false;
    }
  }

  // Once-per-subscription guard so an empty server response doesn't
  // cause repeated re-fetches.
  var fetchAttempted = false;

  await for (final playlistRows in playlistQuery.watch()) {
    if (playlistRows.isEmpty) {
      if (fetchAttempted) {
        yield emptyDetail();
        continue;
      }
      fetchAttempted = true;
      if (!await isOnline()) {
        yield emptyDetail();
        continue;
      }
      final ok = await fetchAndPopulate();
      if (!ok) yield emptyDetail();
      continue;
    }

    final playlist = playlistRows.first.toRef();
    final trackRows = await tracksQuery.get();

    // Same pattern as albumProvider: playlist row exists but no tracks
    // (e.g., playlistsListProvider wrote the row, sync hasn't carried
    // tracks for system playlists). Trigger the same fetch, drift
    // watch re-emits with populated tracks.
    if (trackRows.isEmpty && !fetchAttempted) {
      fetchAttempted = true;
      if (await isOnline()) {
        final ok = await fetchAndPopulate();
        if (ok) continue; // wait for watch re-emit
      }
    }

    final tracks = trackRows.asMap().entries.map((e) {
      final r = e.value;
      final track = r.readTableOrNull(db.cachedTracks);
      final artist = r.readTableOrNull(db.cachedArtists);
      final album = r.readTableOrNull(db.cachedAlbums);
      return PlaylistTrack(
        position: e.key,
        trackId: track?.id,
        title: track?.title ?? '',
        albumId: album?.id,
        albumTitle: album?.title ?? '',
        artistId: artist?.id,
        artistName: artist?.name ?? '',
        durationSec: track == null ? 0 : track.durationMs ~/ 1000,
        streamUrl: null,
      );
    }).toList();

    yield PlaylistDetail(playlist: playlist, tracks: tracks);

    // No SWR refresh here. The aggregate playlistsListProvider does
    // alwaysRefresh (system playlists rotate UUIDs), but per-detail
    // refresh on every visit was multiplying with the prefetcher's
    // parallel fetches and starving user-initiated playback. Pull-
    // to-refresh on the detail page invalidates the provider, which
    // is the right path for an explicit refresh.
  }
});

/// Lightweight tuples used inside fetchAndPopulate to dedupe artist
/// and album rows referenced by playlist tracks before we batch-write
/// them to drift.
class ArtistRefRow {
  ArtistRefRow(this.id, this.name);
  final String id;
  final String name;
}

class AlbumRefRow {
  AlbumRefRow(this.id, this.title, this.artistId);
  final String id;
  final String title;
  final String artistId;
}

class _TrackRefRow {
  _TrackRefRow({
    required this.id,
    required this.title,
    required this.albumId,
    required this.artistId,
    required this.durationSec,
  });
  final String id;
  final String title;
  final String albumId;
  final String artistId;
  final int durationSec;
}

/// Drift-first per #357 pattern. Reads from cached_system_playlists_
/// status (single-row JSON blob populated by /api/me/system-playlists-
/// status). Home Playlists row reads this every render to choose
/// between real cards and "building / pending / failed" placeholders
/// — drift-first means the row paints with the prior status instantly
/// rather than flickering through SystemPlaylistsStatus.empty() while
/// the REST round-trip resolves. SWR refresh on every visit keeps it
/// current.
final systemPlaylistsStatusProvider =
    StreamProvider<SystemPlaylistsStatus>((ref) {
  final db = ref.watch(appDbProvider);
  return cacheFirst<CachedSystemPlaylistsStatusData, SystemPlaylistsStatus>(
    driftStream: db.select(db.cachedSystemPlaylistsStatus).watch(),
    fetchAndPopulate: () async {
      final dio = await ref.read(dioProvider.future);
      final fresh = await MeApi(dio).systemPlaylistsStatus();
      await db.into(db.cachedSystemPlaylistsStatus).insertOnConflictUpdate(
            CachedSystemPlaylistsStatusCompanion.insert(
              json: jsonEncode({
                'in_flight': fresh.inFlight,
                'last_run_at': fresh.lastRunAt,
                'last_error': fresh.lastError,
              }),
              updatedAt: drift.Value(DateTime.now()),
            ),
          );
    },
    toResult: (rows) => rows.isEmpty
        ? SystemPlaylistsStatus.empty()
        : SystemPlaylistsStatus.fromJson(
            jsonDecode(rows.first.json) as Map<String, dynamic>),
    isOnline: () async => (await ref
        .read(connectivityProvider.future)
        .timeout(const Duration(seconds: 3), onTimeout: () => true)),
    alwaysRefresh: true,
    tag: 'systemPlaylistsStatus',
  );
});