import 'package:guru_utils/database/database.dart';
import 'package:guru_utils/property/app_property.dart';
import 'package:guru_utils/property/storage/property_storage.dart';
import 'package:guru_utils/datetime/datetime_utils.dart';
import 'package:guru_utils/log/log.dart';
import 'package:sqflite/sqflite.dart';
import 'package:json_annotation/json_annotation.dart';

/// Created by Haoyi on 2022/8/24

part 'property_database.g.dart';

@JsonSerializable()
class PropertyEntity {
  static const tbName = "properties";
  static const dbKey = "key";
  static const dbValue = "value";
  static const dbGroup = "gp";
  static const dbUsage = "usage";
  static const dbTag = "tag";
  static const dbUpdateAt = "upt";

  @JsonKey(name: dbKey)
  final String key;

  @JsonKey(name: dbValue, defaultValue: '')
  final String value;

  @JsonKey(name: dbUsage, defaultValue: PropertyUsage.general)
  final int usage;

  @JsonKey(name: dbGroup, defaultValue: PropertyKey.defaultGroup)
  final String group;

  @JsonKey(name: dbTag, defaultValue: '')
  final String tag;

  @JsonKey(name: dbUpdateAt, defaultValue: 0)
  final int updateAt;

  PropertyEntity(this.key, this.value, this.usage, this.group, this.tag, this.updateAt);

  factory PropertyEntity.fromMap(Map<String, dynamic> json) => _$PropertyEntityFromJson(json);

  Map<String, dynamic> toMap() => _$PropertyEntityToJson(this);

  static Future createTable(Transaction delegate) async {
    // v1
    const v1Fields = "$dbKey TEXT PRIMARY KEY,"
        "$dbUsage INTEGER DEFAULT ${PropertyUsage.general},"
        "$dbValue TEXT DEFAULT '',"
        "$dbGroup TEXT DEFAULT ${PropertyKey.defaultGroup},"
        "$dbTag TEXT DEFAULT '',"
        "$dbUpdateAt INTEGER DEFAULT 0";

    const cmd = "CREATE TABLE $tbName ("
        "$v1Fields"
        ");";

    Log.d("#### cmd: $cmd");

    await delegate.execute(cmd);
    await delegate.execute("CREATE INDEX property_idx ON $tbName ($dbKey);");
    await delegate.execute("CREATE INDEX property_group_idx ON $tbName ($dbGroup);");
    await delegate.execute("CREATE INDEX property_usage_idx ON $tbName ($dbUsage);");
  }
}

mixin PropertyDatabase on PropertyStorage {
  late AppDatabase appDb;

  void setDatabase(AppDatabase appDatabase) {
    appDb = appDatabase;
  }

  @override
  Future<bool> setProperty(PropertyKey key, String value) async {
    final propertyMap = {
      PropertyEntity.dbKey: key.key,
      PropertyEntity.dbValue: value,
      PropertyEntity.dbGroup: key.group,
      PropertyEntity.dbUsage: key.usage,
      PropertyEntity.dbTag: key.tag,
      PropertyEntity.dbUpdateAt: DateTimeUtils.currentTimeInMillis()
    };

    return (await appDb.getDb().insert(PropertyEntity.tbName, propertyMap,
            conflictAlgorithm: ConflictAlgorithm.replace)) >
        0;
  }

  Future<String?> _getProperty(DatabaseExecutor db, PropertyKey key) async {
    final result = await db.rawQuery(
        "SELECT * FROM ${PropertyEntity.tbName} WHERE ${PropertyEntity.dbKey} = '${key.key}'");
    if (result.isNotEmpty) {
      final value = result[0][PropertyEntity.dbValue];
      if (value is String) {
        return value;
      }
    }
    return null;
  }

  @override
  Future<String?> getProperty(PropertyKey key) {
    return _getProperty(appDb.getDb(), key);
  }

  @override
  Future<String?> getOrCreateProperty(PropertyKey key, {String? Function()? ifAbsent}) {
    if (ifAbsent == null) {
      return _getProperty(appDb.getDb(), key);
    } else {
      return appDb.runInTransaction((txn) async {
        final result = await _getProperty(txn, key);
        if (result != null) {
          return result;
        }
        final value = ifAbsent();
        if (value != null) {
          final now = DateTimeUtils.currentTimeInMillis();
          final propertyMap = {
            PropertyEntity.dbKey: key.key,
            PropertyEntity.dbValue: value,
            PropertyEntity.dbGroup: key.group,
            PropertyEntity.dbUsage: key.usage,
            PropertyEntity.dbTag: key.tag,
            PropertyEntity.dbUpdateAt: now
          };
          await txn.insert(PropertyEntity.tbName, propertyMap,
              conflictAlgorithm: ConflictAlgorithm.replace);
          return value;
        }
        return null;
      });
    }
  }

  @override
  Future<Map<PropertyKey, String>> getProperties(List<PropertyKey> keys) {
    return appDb.runInTransaction((txn) async {
      final Map<PropertyKey, String> result = <PropertyKey, String>{};
      for (var key in keys) {
        final property = await txn.rawQuery(
            "SELECT * FROM ${PropertyEntity.tbName} WHERE ${PropertyEntity.dbKey} = '${key.key}'");
        if (property.isNotEmpty) {
          final value = property[0][PropertyEntity.dbValue];
          if (value is String) {
            result[key] = value;
          }
        }
      }
      return result;
    });
  }

  @override
  Future<bool> setProperties(Map<PropertyKey, String> kv) async {
    final db = appDb.getDb();
    final batch = db.batch();
    final now = DateTimeUtils.currentTimeInMillis();
    for (var entry in kv.entries) {
      final key = entry.key;
      final propertyMap = {
        PropertyEntity.dbKey: key.key,
        PropertyEntity.dbValue: entry.value,
        PropertyEntity.dbGroup: key.group,
        PropertyEntity.dbUsage: key.usage,
        PropertyEntity.dbTag: key.tag,
        PropertyEntity.dbUpdateAt: now
      };
      batch.insert(PropertyEntity.tbName, propertyMap,
          conflictAlgorithm: ConflictAlgorithm.replace);
    }
    await batch.commit();
    return true;
  }

  @override
  Future<Map<PropertyKey, String>> getPropertiesByTag(String tag) async {
    final db = appDb.getDb();
    // Log.d("getPropertiesByTag:by $tag");
    final result = await db.query(PropertyEntity.tbName, where: "${PropertyEntity.dbTag} = '$tag'");
    if (result.isNotEmpty) {
      Log.d("getPropertiesByTag:$result");
      return {for (var map in result) _toKey(map): map[PropertyEntity.dbValue] as String};
    }
    return <PropertyKey, String>{};
  }

  @override
  Future<Map<PropertyKey, String>> getPropertiesByUsage(int usage) async {
    final db = appDb.getDb();
    // Log.d("getPropertiesByTag:by $tag");
    final result =
        await db.query(PropertyEntity.tbName, where: "${PropertyEntity.dbUsage} = $usage");
    if (result.isNotEmpty) {
      Log.d("getPropertiesByUsage:$result");
      return {for (var map in result) _toKey(map): map[PropertyEntity.dbValue] as String};
    }
    return <PropertyKey, String>{};
  }

  @override
  Future<bool> removeProperty(PropertyKey key) async {
    final db = appDb.getDb();
    return await db.delete(PropertyEntity.tbName, where: "${PropertyEntity.dbKey} = '${key.key}'") > 0;
  }

  @override
  Future<List<PropertyKey>> removeAllWithTag(String tag) async {
    return appDb.runInTransaction((txn) async {
      final result =
          await txn.query(PropertyEntity.tbName, where: "${PropertyEntity.dbTag} = '$tag'");
      if (result.isNotEmpty) {
        final keys = result.map((map) => _toKey(map)).toList();
        await txn.delete(PropertyEntity.tbName, where: "${PropertyEntity.dbTag} = '$tag'");
        return keys;
      }
      return [];
    });
  }

  PropertyKey _toKey(Map<String, Object?> data) {
    final group = (data[PropertyEntity.dbGroup] ?? PropertyKey.defaultGroup) as String;
    return PropertyKey(
        name: (data[PropertyEntity.dbKey] as String).replaceFirst("$group@", ''),
        usage: data[PropertyEntity.dbUsage] as int,
        group: group,
        tag: data[PropertyEntity.dbTag] as String);
  }

  @override
  Future<Map<PropertyKey, String>> loadAllProperties() async {
    final db = appDb.getDb();
    final result = await db.rawQuery("SELECT * FROM ${PropertyEntity.tbName}");
    if (result.isNotEmpty) {
      return {for (var map in result) _toKey(map): map[PropertyEntity.dbValue] as String};
    }
    return <PropertyKey, String>{};
  }
}
