import 'dart:io';
import 'dart:math';
import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;
import 'package:flutter_riverpod/flutter_riverpod.dart';

import 'tables.dart';
import 'ride_timestamp_migration.dart';

part 'database.g.dart';

@DriftDatabase(tables: [Rides, PerformanceRuns, TelemetrySamples, RideEvents, GarageMotorcycles, FieldTestSessions, FieldTestEvents, FieldTestSummaries, FieldTestGpsRecords, FieldTestImuRecords])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());
  AppDatabase.forTesting(super.e);

  static final Random _random = Random.secure();

  @override
  int get schemaVersion => 8;

  @override
  MigrationStrategy get migration => MigrationStrategy(
    onCreate: (Migrator m) async {
      await m.createAll();
      await createRideTimestampArchive(this);
      // cloudRideId is generated by Drift on fresh databases. The uniqueness
      // constraint is kept as an explicit index so the nullable migration
      // column remains compatible with older generated source versions.
      await customStatement('CREATE UNIQUE INDEX IF NOT EXISTS rides_cloud_ride_id_idx ON rides(cloud_ride_id)');
    },
    onUpgrade: (Migrator m, int from, int to) async {
      if (from < 2) {
        await m.addColumn(rides, rides.distance);
        await m.addColumn(rides, rides.movingTime);
        await m.addColumn(rides, rides.stoppedTime);
        await m.addColumn(rides, rides.averageSpeed);
        await m.addColumn(rides, rides.topSpeed);
        await m.addColumn(rides, rides.elevationGain);
        await m.addColumn(rides, rides.elevationLoss);
        await m.createTable(telemetrySamples);
        await m.createTable(rideEvents);
      }
      if (from < 3) {
        await m.createTable(garageMotorcycles);
        await m.addColumn(rides, rides.motorcycleId);
        await m.addColumn(rides, rides.motorcycleYear);
        await m.addColumn(rides, rides.motorcycleMake);
        await m.addColumn(rides, rides.motorcycleModel);
        await m.addColumn(rides, rides.motorcycleVariant);
      }
      if (from < 4) {
        await m.createTable(fieldTestSessions);
        await m.createTable(fieldTestEvents);
        await m.createTable(fieldTestGpsRecords);
        await m.createTable(fieldTestImuRecords);
      }
      if (from < 5) {
        await m.createTable(fieldTestSummaries);
        try {
          await m.addColumn(fieldTestEvents, fieldTestEvents.rideId);
          await m.addColumn(fieldTestEvents, fieldTestEvents.telemetryTimestamp);
          await m.addColumn(fieldTestEvents, fieldTestEvents.gpsAgeMs);
          await m.addColumn(fieldTestEvents, fieldTestEvents.speed);
          await m.addColumn(fieldTestEvents, fieldTestEvents.latitude);
          await m.addColumn(fieldTestEvents, fieldTestEvents.longitude);
          await m.addColumn(fieldTestEvents, fieldTestEvents.gpsAccuracy);
          await m.addColumn(fieldTestEvents, fieldTestEvents.leanAngle);
          await m.addColumn(fieldTestEvents, fieldTestEvents.longitudinalG);
          await m.addColumn(fieldTestEvents, fieldTestEvents.lateralG);
          await m.addColumn(fieldTestEvents, fieldTestEvents.overallConfidence);
          await m.addColumn(fieldTestEvents, fieldTestEvents.metadata);
        } catch (_) {}
      }
      if (from < 6) {
        await m.addColumn(rides, rides.cloudRideId);
        await customStatement('CREATE UNIQUE INDEX IF NOT EXISTS rides_cloud_ride_id_idx ON rides(cloud_ride_id)');

        // Existing MotorCloud records were keyed by the deterministic legacy
        // identity ride_<localId>_<UTC yyyyMMdd_HHmmss>. Preserve that identity
        // for pre-v6 rides so their cloud records remain addressable. New rides
        // receive a reinstall-safe random stable ID in startRide().
        final rows = await customSelect('SELECT id, start_time FROM rides WHERE cloud_ride_id IS NULL').get();
        for (final row in rows) {
          final id = row.data['id'];
          final rawStart = row.data['start_time'];
          if (id is! int || rawStart == null) continue;
          final startTime = _decodeStoredDateTime(rawStart);
          if (startTime == null) continue;
          final legacyId = _legacyCloudRideId(id, startTime);
          await customStatement('UPDATE rides SET cloud_ride_id = ? WHERE id = ?', [legacyId, id]);
        }
      }
      if (from < 7) {
        await m.addColumn(rides, rides.cloudOwnerUid);
      }
      if (from < 8) {
        await migrateRideTimestamps(this);
      }
    },
  );

  DateTime? _decodeStoredDateTime(Object raw) {
    return readRideDateTime(raw);
  }

  String _legacyCloudRideId(int localRideId, DateTime startTimeUtc) {
    String two(int value) => value.toString().padLeft(2, '0');
    final stamp = '${startTimeUtc.year.toString().padLeft(4, '0')}${two(startTimeUtc.month)}${two(startTimeUtc.day)}_${two(startTimeUtc.hour)}${two(startTimeUtc.minute)}${two(startTimeUtc.second)}';
    return 'ride_${localRideId}_$stamp';
  }

  String _newCloudRideId() {
    final bytes = List<int>.generate(16, (_) => _random.nextInt(256));
    final hex = bytes.map((b) => b.toRadixString(16).padLeft(2, '0')).join();
    return 'ride_$hex';
  }

  Future<String?> getCloudRideId(int rideId) async {
    final rows = await customSelect('SELECT cloud_ride_id FROM rides WHERE id = ?', variables: [Variable<int>(rideId)]).get();
    if (rows.isEmpty) return null;
    return rows.first.data['cloud_ride_id'] as String?;
  }

  Future<String> ensureCloudRideId(int rideId) async {
    final existing = await getCloudRideId(rideId);
    if (existing != null && existing.isNotEmpty) return existing;
    final created = _newCloudRideId();
    await customStatement('UPDATE rides SET cloud_ride_id = ? WHERE id = ?', [created, rideId]);
    return created;
  }

  Future<int> startRide({
    DateTime? startTime,
    int? motorcycleId,
    int? motorcycleYear,
    String? motorcycleMake,
    String? motorcycleModel,
    String? motorcycleVariant,
  }) async {
    return into(rides).insert(RidesCompanion.insert(
      cloudRideId: Value(_newCloudRideId()),
      startTime: startTime ?? DateTime.now(),
      motorcycleId: Value(motorcycleId),
      motorcycleYear: Value(motorcycleYear),
      motorcycleMake: Value(motorcycleMake),
      motorcycleModel: Value(motorcycleModel),
      motorcycleVariant: Value(motorcycleVariant),
    ));
  }

  Future<String?> getCloudOwnerUid(int rideId) async {
    final rows = await customSelect(
      'SELECT cloud_owner_uid FROM rides WHERE id = ?',
      variables: [Variable<int>(rideId)],
    ).get();
    if (rows.isEmpty) return null;
    return rows.first.data['cloud_owner_uid'] as String?;
  }

  /// Claims only an unclaimed ride. Existing ownership is immutable.
  Future<bool> claimRideForCloudOwner(int rideId, String uid) async {
    final normalizedUid = uid.trim();
    if (normalizedUid.isEmpty) return false;
    final changed = await (update(rides)
          ..where((r) => r.id.equals(rideId) & r.cloudOwnerUid.isNull()))
        .write(RidesCompanion(cloudOwnerUid: Value(normalizedUid)));
    return changed == 1 || await getCloudOwnerUid(rideId) == normalizedUid;
  }

  Future<int> clearCloudOwnerUid(String uid) async {
    if (uid.trim().isEmpty) return 0;
    return (update(rides)..where((r) => r.cloudOwnerUid.equals(uid.trim())))
        .write(const RidesCompanion(cloudOwnerUid: Value(null)));
  }

  Future<void> endRide(int rideId, RidesCompanion data) async {
    await (update(rides)..where((t) => t.id.equals(rideId))).write(data.copyWith(endTime: Value(DateTime.now())));
  }

  /// Persists a completed performance run. Called by [PerformanceTimerService].
  Future<int> addPerformanceRun(PerformanceRunsCompanion entry) {
    return into(performanceRuns).insert(entry);
  }
}

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'motorsignal.sqlite'));
    return NativeDatabase.createInBackground(file);
  });
}

final databaseProvider = Provider<AppDatabase>((ref) {
  final db = AppDatabase();
  ref.onDispose(db.close);
  return db;
});