import 'dart:io';
import 'dart:async';

import 'package:path_provider/path_provider.dart';
import 'package:sqlite3/sqlite3.dart';
import 'package:path/path.dart' as p;

class DB {
  final Completer<Database?> _dbCompleter = Completer<Database?>();
  static const List<String> migrations = <String>[
    '''
PRAGMA user_version = 1;
CREATE TABLE "key_value" (
	"key"	TEXT NOT NULL UNIQUE,
	"value"	TEXT,
	PRIMARY KEY("key")
);
    ''',
  ];

  Future<void> connect() async {
    String path = await _getDbPath();
    Database candidate = sqlite3.open(path);

    migrate(candidate);

    _dbCompleter.complete(candidate);
  }

  void dispose() async {
    if (_dbCompleter.isCompleted == false) {
      return;
    }
    Database? db = await _dbCompleter.future;
    if (db == null) return;
    db.close();
  }

  static Future<String> _getDbPath() async {
    Directory supportDir = await getApplicationSupportDirectory();
    return p.join(supportDir.path, 'main.sqlite3');
  }

  static void migrate(Database db) {
    for (int i=0; i<migrations.length; i++) {
      int? userVersion = _getUserVersion(db);
      if (userVersion == null) return;

      if (i == userVersion) {
        db.execute(migrations[i]);
      }
    }
  }

  static int? _getUserVersion(Database db) {
    ResultSet result = db.select('PRAGMA user_version;');
    if (result.length != 1) return null;
    return result.first.values.first as int;
  }

  Future<String?> getServerFqdn() async {
    Database? db = await _dbCompleter.future;
    if (db == null) return null;

    ResultSet result = db.select(
      'SELECT value FROM key_value WHERE key = \'server_fqdn\';'
    );
    if (result.length != 1) return null;

    return result[0]['value'];
  }

  Future<void> setServerFqdn(String value) async {
    Database? db = await _dbCompleter.future;
    if (db == null) return;

    String? current = await getServerFqdn();
    if (current == null) {
      db.execute(
        'INSERT INTO key_value (key, value) VALUES (\'server_fqdn\', ?);',
        <Object?>[value],
      );
    } else {
      db.execute(
        'UPDATE key_value SET value = ? WHERE key = \'server_fqdn\';',
        <Object?>[value],
      );
    }
  }
}