import 'dart:io';

import 'package:comwell_key_app/database/daos/notifications_dao.dart';
import 'package:comwell_key_app/database/tables/booking_table.dart';
import 'package:comwell_key_app/database/tables/hotel_information_table.dart';
import 'package:comwell_key_app/database/tables/notification_table.dart';
import 'package:comwell_key_app/database/tables/user_table.dart';
import 'package:comwell_key_app/database/tables/upsale_table.dart';
import 'package:comwell_key_app/utils/secure_storage.dart';
import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
// ignore: unused_import
// This import is required for sqlcipher_flutter_libs to override sqlite3's native library loading
import 'package:sqlcipher_flutter_libs/sqlcipher_flutter_libs.dart';
import 'package:sqlite3/sqlite3.dart';
import 'package:uuid/uuid.dart' as uuid;
import 'package:sqlite3/open.dart';
import 'daos/bookings_dao.dart';
import 'daos/hotel_information_dao.dart';
import 'daos/user_dao.dart';
import 'daos/upsales_dao.dart';

part '../.generated/database/comwell_db.g.dart';

final secureStorage = SecureStorage();

@DriftDatabase(tables: [
  BookingEntity,
  UserEntity,
  NotificationPermissionEntity,
  UpsaleEntity,
  HotelInformationEntity
], daos: [
  BookingsDao,
  UserDAO,
  NotificationPermissionDAO,
  UpsalesDAO,
  HotelInformationDAO
])
class ComwellDatabase extends _$ComwellDatabase {
  static const String _cipherKey = "sql_cipher";
  static const String _dbFileName = "comwell.db.enc";

  ComwellDatabase() : super(_openDatabase());

  @override
  int get schemaVersion => 2;

  @override
  MigrationStrategy get migration => destructiveFallback;

  Future<void> deleteDatabase() async {
    try {
      // Try to delete all tables first (while cipher key is still available)
      for (final table in allTables) {
        try {
          await table.delete().go();
        } catch (e) {
          // Ignore errors for individual tables, continue with others
        }
      }
    } catch (e) {
      // If table deletion fails, try to delete the database file directly
      try {
        final path = await getApplicationSupportDirectory();
        final file = File(p.join(path.path, _dbFileName));
        if (await file.exists()) {
          await file.delete();
        }
      } catch (fileError) {
        // If file deletion also fails, ignore - database may already be deleted or inaccessible
      }
    }
  }

  static Future<String> getCipher() async {
    final cipher = await secureStorage.read(_cipherKey);
    if (cipher == null || cipher.isEmpty) {
      final newCipher = const uuid.Uuid().v4obj().toString();
      await secureStorage.write(_cipherKey, newCipher);
    } else {
      return cipher;
    }

    return getCipher();
  }

  static QueryExecutor _openDatabase() {
    return LazyDatabase(() async {
      // sqlcipher_flutter_libs automatically overrides sqlite3 when imported above
      await applyWorkaroundToOpenSqlCipherOnOldAndroidVersions();
      open.overrideFor(OperatingSystem.android, openCipherOnAndroid);
      final path = await getApplicationSupportDirectory();
      final file = File(p.join(path.path, _dbFileName));
      final cipher = await getCipher();

      // Pre-validate the existing database file with SQLCipher. If it is not a
      // valid SQLCipher database (typical when migrating from an unencrypted
      // DB), delete it so Drift can recreate a fresh one.
      if (await file.exists()) {
        try {
          final db = sqlite3.open(file.path);
          try {
            db.execute('pragma key = \"$cipher\"');
            db.execute('select count(*) from sqlite_master');
          } finally {
            db.dispose();
          }
        } on SqliteException catch (e) {
          if (e.message.contains("file is not a database")) {
            await file.delete();
          } else {
            rethrow;
          }
        }
      }

      void setup(Database db) {
        // Apply the encryption key for all connections created by Drift.
        db.execute('pragma key = \"$cipher\"');

        // Optional pragmas for stability / performance.
        db.execute("pragma journal_mode = WAL");
        db.execute("pragma synchronous = NORMAL");
        db.execute("pragma cache_size = 1000");
        db.execute("pragma temp_store = MEMORY");
      }

      // Use synchronous database creation instead of createInBackground
      // to avoid isolate channel issues during concurrent access.
      return NativeDatabase(file, setup: setup);
    });
  }

  /// Recreates the database by deleting the corrupted file
  Future<void> recreateDatabase() async {
    try {
      final path = await getApplicationSupportDirectory();
      final file = File(p.join(path.path, _dbFileName));
      if (await file.exists()) {
        await file.delete();
      }
    } catch (e) {
      // Ignore errors during deletion
    }
  }
}