import 'package:drift/drift.dart' show Variable;
import 'database.dart';

DateTime? readRideDateTime(Object? value) {
  if (value is DateTime) return value.toUtc();
  final numeric = value is num ? value : int.tryParse(value?.toString() ?? '');
  if (numeric == null) return DateTime.tryParse(value?.toString() ?? '')?.toUtc();
  if (!numeric.isFinite || numeric != numeric.truncateToDouble()) return null;
  try {
    return DateTime.fromMillisecondsSinceEpoch(
      canonicalRideSeconds(numeric.toInt()) * 1000, isUtc: true,
    );
  } on StateError {
    return null;
  }
}

// Only recognize modern ride dates; ambiguous large values require inspection.
int canonicalRideSeconds(int value) {
  if (value >= 946684800000000 && value < 4102444800000000) {
    return value ~/ 1000000;
  }
  if (value >= 946684800000 && value < 4102444800000) {
    return value ~/ 1000;
  }
  if (value.abs() >= 100000000000) {
    throw StateError('Unrecognized ride timestamp units; original data preserved.');
  }
  return value;
}

Future<void> createRideTimestampArchive(AppDatabase db) => db.customStatement('''
  CREATE TABLE IF NOT EXISTS ride_timestamp_archive (
    ride_id INTEGER PRIMARY KEY,
    original_start_time INTEGER NOT NULL,
    original_end_time INTEGER,
    canonical_start_time INTEGER NOT NULL,
    canonical_end_time INTEGER
  )
''');

Future<void> migrateRideTimestamps(AppDatabase db) => db.transaction(() async {
  await createRideTimestampArchive(db);
  final rows = await db.customSelect('SELECT id, start_time, end_time FROM rides').get();
  for (final row in rows) {
    final start = row.data['start_time'];
    final end = row.data['end_time'];
    if (start is! int || (end != null && end is! int)) {
      throw StateError('Unrecognized ride timestamp type; original data preserved.');
    }
    final normalizedStart = canonicalRideSeconds(start);
    final normalizedEnd = end == null ? null : canonicalRideSeconds(end as int);
    if (start == normalizedStart && end == normalizedEnd) continue;
    await db.customStatement('''
      INSERT INTO ride_timestamp_archive
        (ride_id, original_start_time, original_end_time,
         canonical_start_time, canonical_end_time)
      VALUES (?, ?, ?, ?, ?)
      ON CONFLICT(ride_id) DO NOTHING
    ''', [row.data['id'], start, end, normalizedStart, normalizedEnd]);
    // A retained archive from rollback must still describe this exact record.
    final archived = await db.customSelect(
      'SELECT * FROM ride_timestamp_archive WHERE ride_id = ?',
      variables: [Variable<int>(row.data['id'] as int)],
    ).getSingle();
    final original = archived.data;
    if (original['original_start_time'] != start || original['original_end_time'] != end) {
      throw StateError('Ride changed since archived migration; original data preserved.');
    }
    await db.customStatement('UPDATE rides SET start_time = ?, end_time = ? WHERE id = ?',
        [normalizedStart, normalizedEnd, row.data['id']]);
  }
});