import 'dart:developer';

import 'package:hub/features/module/index.dart';
import 'package:hub/features/storage/index.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class DatabaseService {
  static const String _dbName = 'database.db';
  static const int _dbVersion = 3;

  static final DatabaseService instance = DatabaseService._internal();
  factory DatabaseService() => instance;
  DatabaseService._internal();

  static Database? _database;
  static Database get database {
    if (_database == null) {
      throw Exception('Database has not been initialized. Call init() first.');
    }
    return _database!;
  }

  static bool isInitialized = false;

  Future<void> init() async {
    if (isInitialized) return;

    _database = await openDatabase(
      join(await getDatabasesPath(), _dbName),
      version: _dbVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
      onDowngrade: _onDowngrade,
      onOpen: _onOpen,
      onConfigure: _onConfigure,
    );
    log('Database initialized');
    await LicenseRepositoryImpl().updateLicense();
    isInitialized = true;
  }

  Future<void> _onConfigure(Database database) async {
    log('Configuring database...');
  }

  Future<void> _onOpen(Database database) async {
    log('Opening database...');
    await _executePragmas(database);
  }

  Future<void> _onCreate(Database database, int version) async {
    log('Creating database...');
    await database.execute(createKeychainTable);
    await _onUpgrade(database, 1, _dbVersion);
  }

  Future<void> _onUpgrade(
      Database database, int oldVersion, int newVersion) async {
    log('Upgrading database from version $oldVersion to $newVersion...');
    if (oldVersion < 2 && newVersion >= 2) {
      await database.execute(LicenseConstants.createLicenseTable);
      await database.execute(LicenseConstants.updatePetsHistoryTrigger);
      await database.execute(LicenseConstants.updateDisplayTrigger);
      await database.execute(LicenseConstants.insertDisplayTrigger);
      await database.execute(LicenseConstants.updatePeopleDisplayTrigger);
    }
    if (oldVersion < 3 && newVersion >= 3) {
      await database.execute(LocalsConstants.createLocalsTable);
    }
  }

  Future<void> _onDowngrade(
      Database database, int oldVersion, int newVersion) async {
    log('Downgrading database from version $oldVersion to $newVersion...');
    if (oldVersion >= 2 && newVersion < 2) {
      await database.execute(LicenseConstants.deleteLicenseTable);
      await database.execute(LicenseConstants.dropPeopleDisplayTrigger);
      await database.execute(LicenseConstants.dropInsertDisplayTrigger);
      await database.execute(LicenseConstants.dropUpdateDisplayTrigger);
      await database.execute(LicenseConstants.dropPetsHistoryTrigger);
    }
    if (oldVersion >= 3 && newVersion > 3) {
      await database.execute(LocalsConstants.deleteLocalsTable);
    }
  }

  Future<void> _executePragmas(Database database) async {
    // await database.execute('PRAGMA journal_mode = WAL;');
    // await database.execute('PRAGMA synchronous = NORMAL;');
    // await database.execute('PRAGMA temp_store = MEMORY;');
    // await database.execute('PRAGMA foreign_keys = ON;');
  }
}