import 'package:flutter/foundation.dart' show debugPrint;
import 'package:sqflite/sqflite.dart';
import 'remote_models.dart';

class RemoteRepository {
  final Database db;
  RemoteRepository(this.db);

  /// Assicura che le colonne GPS esistano nella tabella `entry`.
  /// Se mancano, prova ad aggiungerle con ALTER TABLE.
  Future<void> _ensureGpsColumns(DatabaseExecutor dbExec) async {
    try {
      final rows = await dbExec.rawQuery('PRAGMA table_info(entry);');
      final names = rows.map((r) => r['name'] as String).toSet();
      final stmts = <String>[];
      if (!names.contains('latitude')) stmts.add('ALTER TABLE entry ADD COLUMN latitude REAL;');
      if (!names.contains('longitude')) stmts.add('ALTER TABLE entry ADD COLUMN longitude REAL;');
      if (!names.contains('altitude')) stmts.add('ALTER TABLE entry ADD COLUMN altitude REAL;');
      for (final s in stmts) {
        try {
          await dbExec.execute(s);
          debugPrint('[RemoteRepository] executed: $s');
        } catch (e, st) {
          debugPrint('[RemoteRepository] failed to execute $s: $e\n$st');
        }
      }
    } catch (e, st) {
      debugPrint('[RemoteRepository] _ensureGpsColumns error: $e\n$st');
    }
  }

  /// Inserisce o aggiorna tutti gli elementi remoti.
  /// Difensivo: assicura colonne GPS, logga, e in caso di errore riprova senza i campi GPS.
  Future<void> upsertAll(List<RemotePhotoItem> items) async {
    debugPrint('RemoteRepository.upsertAll: items=${items.length}');
    try {
      await db.transaction((txn) async {
        // Assicuriamoci che le colonne GPS esistano prima di inserire
        await _ensureGpsColumns(txn);

        for (final it in items) {
          debugPrint('RemoteRepository: processing remoteId=${it.id} lat=${it.lat} lng=${it.lng}');

          final existing = await txn.query(
            'entry',
            columns: ['id'],
            where: 'remoteId = ?',
            whereArgs: [it.id],
            limit: 1,
          );

          final int? existingId = existing.isNotEmpty ? (existing.first['id'] as int?) : null;

          final row = <String, Object?>{
            'id': existingId,
            'contentId': null,
            'uri': null,
            'path': it.path,
            'sourceMimeType': it.mimeType,
            'width': it.width,
            'height': it.height,
            'sourceRotationDegrees': null,
            'sizeBytes': it.sizeBytes,
            'title': it.name,
            'dateAddedSecs': DateTime.now().millisecondsSinceEpoch ~/ 1000,
            'dateModifiedMillis': null,
            'sourceDateTakenMillis': it.takenAtUtc?.millisecondsSinceEpoch,
            'durationMillis': it.durationMillis,
            'trashed': 0,
            'origin': 1,
            'provider': 'json@patachina',
            // campi GPS (possono essere null)
            'latitude': it.lat,
            'longitude': it.lng,
            'altitude': it.alt,
            // campi remoti
            'remoteId': it.id,
            'remotePath': it.path,
            'remoteThumb1': it.thub1,
            'remoteThumb2': it.thub2,
          };

          try {
            final newId = await txn.insert(
              'entry',
              row,
              conflictAlgorithm: ConflictAlgorithm.replace,
            );

            if (it.location != null) {
              final addr = <String, Object?>{
                'id': newId,
                'addressLine': it.location!.address,
                'countryCode': null,
                'countryName': it.location!.country,
                'adminArea': it.location!.region,
                'locality': it.location!.city,
              };
              await txn.insert(
                'address',
                addr,
                conflictAlgorithm: ConflictAlgorithm.replace,
              );
            }
          } on DatabaseException catch (e, st) {
            debugPrint('[RemoteRepository] insert failed for remoteId=${it.id}: $e\n$st');
            // Fallback: riprova senza i campi GPS (utile se ALTER TABLE non è riuscito)
            final rowNoGps = Map<String, Object?>.from(row)
              ..remove('latitude')
              ..remove('longitude')
              ..remove('altitude');
            try {
              final newId = await txn.insert(
                'entry',
                rowNoGps,
                conflictAlgorithm: ConflictAlgorithm.replace,
              );

              if (it.location != null) {
                final addr = <String, Object?>{
                  'id': newId,
                  'addressLine': it.location!.address,
                  'countryCode': null,
                  'countryName': it.location!.country,
                  'adminArea': it.location!.region,
                  'locality': it.location!.city,
                };
                await txn.insert(
                  'address',
                  addr,
                  conflictAlgorithm: ConflictAlgorithm.replace,
                );
              }
              debugPrint('[RemoteRepository] insert succeeded without GPS for remoteId=${it.id}');
            } catch (e2, st2) {
              debugPrint('[RemoteRepository] retry without GPS failed for remoteId=${it.id}: $e2\n$st2');
              rethrow;
            }
          }
        }
      });
    } catch (e, st) {
      debugPrint('[RemoteRepository] upsertAll ERROR: $e\n$st');
      rethrow;
    }
  }

  Future<int> countRemote() async {
    final rows = await db.rawQuery('SELECT COUNT(1) AS c FROM entry WHERE origin=1');
    return (rows.first['c'] as int?) ?? 0;
  }
}