Saba-dart/lib/utils/database_helper.dart

import 'dart:io';

import 'package:flutter_secure_storage/flutter_secure_storage.dart';
import 'package:path/path.dart';
import 'package:sqflite_sqlcipher/sqflite.dart';

import 'phone_helper.dart';
import 'protocol_helper.dart';

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;
  static const String _primaryDbName = 'app_v7.db';
  static const String _alternateDbName = 'app_v8.db';

  DatabaseHelper._init();

  final _secureStorage = const FlutterSecureStorage();
  String? _dbPassword;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB(await _resolveDatabasePath());
    return _database!;
  }

  Future<String> _resolveDatabasePath() async {
    final dbPath = await getDatabasesPath();
    final primaryPath = join(dbPath, _primaryDbName);
    final alternatePath = join(dbPath, _alternateDbName);
    final primaryFile = File(primaryPath);
    final alternateFile = File(alternatePath);

    final hasPrimary = await primaryFile.exists();
    final hasAlternate = await alternateFile.exists();

    if (hasPrimary && hasAlternate) {
      final primaryLength = await primaryFile.length();
      final alternateLength = await alternateFile.length();
      if (primaryLength != alternateLength) {
        return primaryLength >= alternateLength ? primaryPath : alternatePath;
      }

      final primaryStat = await primaryFile.stat();
      final alternateStat = await alternateFile.stat();
      return primaryStat.modified.isAfter(alternateStat.modified)
          ? primaryPath
          : alternatePath;
    }

    if (hasPrimary) return primaryPath;
    if (hasAlternate) return alternatePath;
    return primaryPath;
  }

  Future<String> _getDatabasePassword() async {
    if (_dbPassword != null) return _dbPassword!;

    const key = 'saba_db_password_v1';
    var password = await _secureStorage.read(key: key);

    if (password == null) {
      password =
          "saba_${DateTime.now().microsecondsSinceEpoch}_${key.hashCode}";
      await _secureStorage.write(key: key, value: password);
    }

    _dbPassword = password;
    return password;
  }

  Future<void> resetAppData() async {
    final dbPath = await getDatabasesPath();
    final primaryPath = join(dbPath, _primaryDbName);
    final alternatePath = join(dbPath, _alternateDbName);
    final keyPath = join(dbPath, '.saba_local.key');

    if (_database != null) {
      await _database!.close();
      _database = null;
    }

    for (final path in [primaryPath, alternatePath]) {
      try {
        await deleteDatabase(path);
      } catch (_) {}
      final file = File(path);
      if (await file.exists()) {
        try {
          await file.delete();
        } catch (_) {}
      }
    }

    final keyFile = File(keyPath);
    if (await keyFile.exists()) {
      try {
        await keyFile.delete();
      } catch (_) {}
    }
  }

  Future<Database> _initDB(String path) async {
    final password = await _getDatabasePassword();
    try {
      return await openDatabase(
        path,
        password: password,
        version: 12,
        onCreate: _createDB,
        onUpgrade: _onUpgrade,
        onConfigure: _onConfigure,
      );
    } catch (e) {
      if (e.toString().contains('file is not a database') ||
          e.toString().contains('open_failed')) {
        try {
          await deleteDatabase(path);
          final file = File(path);
          if (await file.exists()) {
            await file.delete();
          }
        } catch (_) {}

        return await openDatabase(
          path,
          password: password,
          version: 7,
          onCreate: _createDB,
          onUpgrade: _onUpgrade,
          onConfigure: _onConfigure,
        );
      }
      rethrow;
    }
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      try {
        await db.execute(
            'ALTER TABLE contacts_cache ADD COLUMN peer_fingerprint TEXT');
      } catch (_) {}
    }
    if (oldVersion < 3) {
      try {
        await db.execute(
            'ALTER TABLE contacts_cache ADD COLUMN symmetric_key TEXT');
      } catch (_) {}
    }
    if (oldVersion < 5) {
      try {
        await db.execute('''
          CREATE TABLE IF NOT EXISTS decrypted_cache (
            payload TEXT PRIMARY KEY,
            body TEXT,
            date INTEGER
          )
        ''');
      } catch (_) {}
    }
    if (oldVersion < 6) {
      final migrations = [
        'ALTER TABLE contacts_cache ADD COLUMN ecc_shared_key TEXT',
        'ALTER TABLE contacts_cache ADD COLUMN verification_state TEXT DEFAULT "unverified"',
        'ALTER TABLE contacts_cache ADD COLUMN verified_at INTEGER',
        'ALTER TABLE message_fragments ADD COLUMN packet_mode TEXT',
        'ALTER TABLE message_fragments ADD COLUMN updated_at INTEGER',
      ];
      for (final sql in migrations) {
        try {
          await db.execute(sql);
        } catch (_) {}
      }
    }
    if (oldVersion < 7) {
      try {
        await db.execute('''
          CREATE TABLE IF NOT EXISTS sms_cache (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            sms_id INTEGER UNIQUE,
            address TEXT,
            body TEXT,
            date INTEGER,
            type INTEGER,
            is_me INTEGER,
            is_read INTEGER DEFAULT 1,
            is_secure INTEGER DEFAULT 0,
            packet_id TEXT,
            packet_mode TEXT
          )
        ''');
        await db.execute(
            'CREATE INDEX IF NOT EXISTS idx_sms_address_date ON sms_cache(address, date)');
      } catch (_) {}
    }
    if (oldVersion < 9) {
      try {
        await db.execute(
            'ALTER TABLE message_fragments ADD COLUMN is_me INTEGER DEFAULT 0');
      } catch (_) {}
    }
    if (oldVersion < 10) {
      try {
        await db.execute(
            'ALTER TABLE sms_cache ADD COLUMN is_read INTEGER DEFAULT 1');
        await db.execute(
            'ALTER TABLE group_messages ADD COLUMN is_read INTEGER DEFAULT 1');
      } catch (_) {}
    }
    if (oldVersion < 11) {
      try {
        await db.execute(
            'ALTER TABLE sms_cache ADD COLUMN is_secure INTEGER DEFAULT 0');
        await db.execute(
            'ALTER TABLE group_messages ADD COLUMN is_secure INTEGER DEFAULT 0');
      } catch (_) {}
    }
    if (oldVersion < 12) {
      try {
        await db.execute('ALTER TABLE sms_cache ADD COLUMN packet_id TEXT');
        await db.execute('ALTER TABLE sms_cache ADD COLUMN packet_mode TEXT');
        await db
            .execute('ALTER TABLE group_messages ADD COLUMN packet_id TEXT');
        await db
            .execute('ALTER TABLE group_messages ADD COLUMN packet_mode TEXT');
      } catch (_) {}
    }
  }

  Future<void> _onConfigure(Database db) async {
    await db.execute('PRAGMA foreign_keys = ON');
  }

  Future<void> _createDB(Database db, int version) async {
    await db.execute('''
      CREATE TABLE contacts_cache (
        phone TEXT PRIMARY KEY,
        name TEXT,
        peer_public_key TEXT,
        peer_fingerprint TEXT,
        symmetric_key TEXT,
        ecc_shared_key TEXT,
        secure_state TEXT,
        verification_state TEXT DEFAULT 'unverified',
        verified_at INTEGER,
        mode TEXT DEFAULT 'normal'
      )
    ''');

    await db.execute(
        'CREATE TABLE sms_groups (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, group_key TEXT)');
    await db.execute('''
      CREATE TABLE group_members (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        group_id INTEGER,
        phone TEXT,
        name TEXT,
        FOREIGN KEY (group_id) REFERENCES sms_groups (id) ON DELETE CASCADE
      )
    ''');
    await db.execute('''
      CREATE TABLE group_messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        group_id INTEGER,
        sender_phone TEXT,
        body TEXT,
        date INTEGER,
        is_read INTEGER DEFAULT 1,
        is_secure INTEGER DEFAULT 0,
        packet_id TEXT,
        packet_mode TEXT,
        FOREIGN KEY (group_id) REFERENCES sms_groups (id) ON DELETE CASCADE
      )
    ''');

    await db.execute('''
      CREATE TABLE message_fragments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        phone TEXT,
        msg_id TEXT,
        frag_index INTEGER,
        total_frags INTEGER,
        packet_mode TEXT,
        body TEXT,
        date INTEGER,
        updated_at INTEGER,
        is_me INTEGER DEFAULT 0
      )
    ''');

    await db.execute('''
      CREATE TABLE decrypted_cache (
        payload TEXT PRIMARY KEY,
        body TEXT,
        date INTEGER
      )
    ''');

    await db.execute(
        'CREATE TABLE app_settings (key TEXT PRIMARY KEY, value TEXT)');

    await db.execute('''
      CREATE TABLE IF NOT EXISTS sms_cache (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        sms_id INTEGER UNIQUE,
        address TEXT,
        body TEXT,
        date INTEGER,
        type INTEGER,
        is_me INTEGER,
        is_read INTEGER DEFAULT 1,
        is_secure INTEGER DEFAULT 0,
        packet_id TEXT,
        packet_mode TEXT
      )
    ''');
    await db.execute(
        'CREATE INDEX IF NOT EXISTS idx_sms_address_date ON sms_cache(address, date)');
  }

  String _normalizePhone(String phone) {
    return PhoneHelper.normalizePhone(phone);
  }

  Future<void> cacheContacts(Map<String, String> contactsMap) async {
    final db = await instance.database;
    final batch = db.batch();
    contactsMap.forEach((phone, name) {
      batch.insert(
          'contacts_cache', {'phone': _normalizePhone(phone), 'name': name},
          conflictAlgorithm: ConflictAlgorithm.ignore);
    });
    await batch.commit(noResult: true);
  }

  Future<Map<String, dynamic>?> getContact(String phone) async {
    final db = await instance.database;
    final res = await db.query('contacts_cache',
        where: 'phone = ?', whereArgs: [_normalizePhone(phone)]);
    if (res.isEmpty) return null;
    return Map<String, dynamic>.from(res.first);
  }

  Future<void> updateContactSecurity(
    String phone, {
    String? publicKey,
    String? state,
    String? mode,
    String? peerFingerprint,
    String? symmetricKey,
    String? eccSharedKey,
    String? verificationState,
    int? verifiedAt,
  }) async {
    final db = await instance.database;
    final cleanPhone = _normalizePhone(phone);
    final data = <String, dynamic>{};
    if (publicKey != null) data['peer_public_key'] = publicKey;
    if (state != null) data['secure_state'] = state;
    if (mode != null) data['mode'] = mode;
    if (peerFingerprint != null) data['peer_fingerprint'] = peerFingerprint;
    if (symmetricKey != null) data['symmetric_key'] = symmetricKey;
    if (eccSharedKey != null) data['ecc_shared_key'] = eccSharedKey;
    if (verificationState != null) {
      data['verification_state'] = verificationState;
    }
    if (verifiedAt != null) {
      data['verified_at'] = verifiedAt;
    }

    if (data.isEmpty) return;
    final existing = await getContact(cleanPhone);
    if (existing == null) {
      data['phone'] = cleanPhone;
      await db.insert('contacts_cache', data);
    } else {
      await db.update('contacts_cache', data,
          where: 'phone = ?', whereArgs: [cleanPhone]);
    }
  }

  Future<void> resetAsymmetricSecurity(String phone) async {
    final db = await instance.database;
    final cleanPhone = _normalizePhone(phone);
    await db.update(
        'contacts_cache',
        {
          'peer_public_key': null,
          'peer_fingerprint': null,
          'ecc_shared_key': null,
          'secure_state': 'none',
          'verification_state': 'unverified',
          'verified_at': null,
          'mode': 'normal',
        },
        where: 'phone = ?',
        whereArgs: [cleanPhone]);
  }

  Future<Map<String, String>> getCachedContacts() async {
    final db = await instance.database;
    final result = await db.query('contacts_cache');
    final contacts = <String, String>{};
    for (final row in result) {
      contacts[row['phone'] as String] =
          row['name'] as String? ?? row['phone'] as String;
    }
    return contacts;
  }

  Future<void> saveFragment(
      String phone, String msgId, int index, int total, String body,
      {String packetMode = 'AE', bool isMe = false}) async {
    final db = await instance.database;
    final normalized = _normalizePhone(phone);
    final now = DateTime.now().millisecondsSinceEpoch;
    final existing = await db.query('message_fragments',
        where: 'phone = ? AND msg_id = ? AND frag_index = ?',
        whereArgs: [normalized, msgId, index]);

    if (existing.isEmpty) {
      await db.insert('message_fragments', {
        'phone': normalized,
        'msg_id': msgId,
        'frag_index': index,
        'total_frags': total,
        'packet_mode': packetMode,
        'body': body,
        'date': now,
        'updated_at': now,
        'is_me': isMe ? 1 : 0,
      });
    } else {
      await db.update('message_fragments', {'updated_at': now},
          where: 'phone = ? AND msg_id = ? AND frag_index = ?',
          whereArgs: [normalized, msgId, index]);
    }
  }

  Future<List<Map<String, dynamic>>> getFragments(
      String phone, String msgId) async {
    final db = await instance.database;
    return db.query('message_fragments',
        where: 'phone = ? AND msg_id = ?',
        whereArgs: [_normalizePhone(phone), msgId],
        groupBy: 'frag_index',
        orderBy: 'frag_index ASC');
  }

  Future<List<Map<String, dynamic>>> getPendingPacketsForPhone(
      String phone) async {
    final db = await instance.database;
    return db.rawQuery('''
      SELECT phone, msg_id, packet_mode, COUNT(DISTINCT frag_index) AS received_parts, MAX(total_frags) AS total_parts, MIN(date) AS first_seen, MAX(COALESCE(updated_at, date)) AS last_seen, MAX(is_me) AS isMe
      FROM message_fragments WHERE phone = ? GROUP BY phone, msg_id, packet_mode
      HAVING COUNT(DISTINCT frag_index) < MAX(total_frags) ORDER BY MIN(date) ASC
    ''', [_normalizePhone(phone)]);
  }

  Future<void> clearFragments(String phone, String msgId) async {
    final db = await instance.database;
    await db.delete('message_fragments',
        where: 'phone = ? AND msg_id = ?',
        whereArgs: [_normalizePhone(phone), msgId]);
  }

  Future<void> clearCompletedPackets(String phone) async {
    final db = await instance.database;
    final normalizedPhone = _normalizePhone(phone);
    final completedPackets = await db.rawQuery('''
      SELECT msg_id, packet_mode FROM message_fragments WHERE phone = ? GROUP BY phone, msg_id, packet_mode
      HAVING COUNT(DISTINCT frag_index) >= MAX(total_frags)
    ''', [normalizedPhone]);

    if (completedPackets.isEmpty) return;
    final batch = db.batch();
    for (final row in completedPackets) {
      batch.delete('message_fragments',
          where: 'phone = ? AND msg_id = ? AND packet_mode = ?',
          whereArgs: [normalizedPhone, row['msg_id'], row['packet_mode']]);
    }
    await batch.commit(noResult: true);
  }

  Future<void> clearStalePendingPackets(String phone,
      {required int olderThanMs}) async {
    final db = await instance.database;
    final normalizedPhone = _normalizePhone(phone);
    final stalePackets = await db.rawQuery('''
      SELECT msg_id, packet_mode FROM message_fragments WHERE phone = ? GROUP BY phone, msg_id, packet_mode
      HAVING COUNT(DISTINCT frag_index) < MAX(total_frags) AND MAX(COALESCE(updated_at, date)) < ?
    ''', [normalizedPhone, olderThanMs]);

    if (stalePackets.isEmpty) return;
    final batch = db.batch();
    for (final row in stalePackets) {
      batch.delete('message_fragments',
          where: 'phone = ? AND msg_id = ? AND packet_mode = ?',
          whereArgs: [normalizedPhone, row['msg_id'], row['packet_mode']]);
    }
    await batch.commit(noResult: true);
  }

  Future<void> setSetting(String key, String value) async {
    final db = await instance.database;
    await db.insert('app_settings', {'key': key, 'value': value},
        conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<String?> getSetting(String key) async {
    final db = await instance.database;
    final res =
        await db.query('app_settings', where: 'key = ?', whereArgs: [key]);
    return res.isNotEmpty ? res.first['value'] as String : null;
  }

  Future<void> saveIdentity(
      {required String privateKey,
      required String publicKey,
      required String fingerprint}) async {
    await setSetting('identity_private', privateKey);
    await setSetting('identity_public', publicKey);
    await setSetting('identity_fingerprint', fingerprint);
  }

  Future<Map<String, String>?> getIdentity() async {
    final privateKey = await getSetting('identity_private');
    final publicKey = await getSetting('identity_public');
    final fingerprint = await getSetting('identity_fingerprint');
    if (privateKey == null || publicKey == null || fingerprint == null) {
      return null;
    }
    return {
      'privateKey': privateKey,
      'publicKey': publicKey,
      'fingerprint': fingerprint
    };
  }

  Future<int> createGroup(
      String name, List<Map<String, String>> members) async {
    final db = await instance.database;
    final groupId = await db.insert('sms_groups', {'name': name});
    final batch = db.batch();
    for (final m in members) {
      batch.insert('group_members', {
        'group_id': groupId,
        'phone': _normalizePhone(m['phone']!),
        'name': m['name']
      });
    }
    await batch.commit(noResult: true);
    return groupId;
  }

  Future<List<Map<String, dynamic>>> getGroups() async {
    final db = await instance.database;
    final rows = await db.query('sms_groups', orderBy: 'id DESC');
    return rows.map((row) => Map<String, dynamic>.from(row)).toList();
  }

  Future<int> updateGroupName(int groupId, String newName) async {
    final db = await instance.database;
    return db.update('sms_groups', {'name': newName},
        where: 'id = ?', whereArgs: [groupId]);
  }

  Future<int> updateGroupKey(int groupId, String key) async {
    final db = await instance.database;
    return db.update('sms_groups', {'group_key': key},
        where: 'id = ?', whereArgs: [groupId]);
  }

  Future<List<Map<String, dynamic>>> getGroupsContainingPhone(
      String phone) async {
    final db = await instance.database;
    final rows = await db.rawQuery('''
      SELECT g.* FROM sms_groups g JOIN group_members m ON g.id = m.group_id WHERE m.phone = ?
    ''', [_normalizePhone(phone)]);
    return rows.map((row) => Map<String, dynamic>.from(row)).toList();
  }

  Future<List<Map<String, dynamic>>> getGroupMembers(int groupId) async {
    final db = await instance.database;
    return db
        .query('group_members', where: 'group_id = ?', whereArgs: [groupId]);
  }

  Future<bool> addMemberToGroup(int groupId, String name, String phone) async {
    final db = await instance.database;
    final cleanPhone = _normalizePhone(phone);
    final exists = await db.query('group_members',
        where: 'group_id = ? AND phone = ?', whereArgs: [groupId, cleanPhone]);
    if (exists.isNotEmpty) return false;
    await db.insert('group_members',
        {'group_id': groupId, 'phone': cleanPhone, 'name': name});
    return true;
  }

  Future<int> removeGroupMember(int groupId, String phone) async {
    final db = await instance.database;
    return db.delete('group_members',
        where: 'id = ? AND phone = ?',
        whereArgs: [groupId, _normalizePhone(phone)]);
  }

  Future<int> saveGroupMessage(int groupId, String body, int date,
      {String? senderPhone,
      int isSecure = 0,
      String? packetId,
      String? packetMode}) async {
    final db = await instance.database;
    return await db.insert('group_messages', {
      'group_id': groupId,
      'sender_phone': senderPhone != null ? _normalizePhone(senderPhone) : null,
      'body': body,
      'date': date,
      'is_read': 0,
      'is_secure': isSecure,
      'packet_id': packetId,
      'packet_mode': packetMode,
    });
  }

  Future<List<Map<String, dynamic>>> getGroupMessages(int groupId) async {
    final db = await instance.database;
    return db.query('group_messages',
        where: 'group_id = ?', whereArgs: [groupId], orderBy: 'date DESC');
  }

  Future<void> saveDecrypted(String payload, String decrypted) async {
    final db = await instance.database;
    await db.insert(
        'decrypted_cache',
        {
          'payload': payload,
          'body': decrypted,
          'date': DateTime.now().millisecondsSinceEpoch,
        },
        conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<String?> getDecrypted(String payload) async {
    final db = await instance.database;
    final res = await db
        .query('decrypted_cache', where: 'payload = ?', whereArgs: [payload]);
    if (res.isEmpty) return null;
    return res.first['body'] as String?;
  }

  Future<int> deleteGroupMessage(int messageId) async {
    final db = await instance.database;
    return db.delete('group_messages', where: 'id = ?', whereArgs: [messageId]);
  }

  Future<int> deleteGroup(int groupId) async {
    final db = await instance.database;
    return db.delete('sms_groups', where: 'id = ?', whereArgs: [groupId]);
  }

  // --- SMS Cache for Performance ---

  Future<int> saveSingleSmsToCache(Map<String, dynamic> sms) async {
    final db = await instance.database;
    final data = Map<String, dynamic>.from(sms);
    if (!data.containsKey('is_read')) {
      data['is_read'] = 1; // Default to read if not specified
    }
    return await db.insert('sms_cache', data,
        conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<void> markAsRead(String address) async {
    final db = await instance.database;
    await db.update('sms_cache', {'is_read': 1},
        where: 'address = ?', whereArgs: [_normalizePhone(address)]);
  }

  Future<void> markGroupAsRead(int groupId) async {
    final db = await instance.database;
    await db.update('group_messages', {'is_read': 1},
        where: 'group_id = ?', whereArgs: [groupId]);
  }

  bool _isUnreadExcludedProtocolType(String type) {
    return type == 'key_init' ||
        type == 'key_reply' ||
        type == 'norm' ||
        type == 'nack';
  }

  String? _packetModeForProtocolType(String type) {
    switch (type) {
      case 'sym':
      case 'sfra':
        return 'SYM';
      case 'asym':
      case 'afrag':
        return 'AE';
      default:
        return null;
    }
  }

  String? _unreadLogicalKey(Map<String, dynamic> row, {bool isGroup = false}) {
    final body = row['body'] as String? ?? '';
    final parsed = ProtocolHelper.parseMessage(body);
    final type = parsed['type'] as String? ?? 'plain';
    if (_isUnreadExcludedProtocolType(type)) {
      return null;
    }

    final packetId =
        (row['packet_id'] as String?) ?? (parsed['packetId'] as String?);
    final packetMode =
        (row['packet_mode'] as String?) ?? _packetModeForProtocolType(type);

    if (packetId != null &&
        packetId.isNotEmpty &&
        packetMode != null &&
        packetMode.isNotEmpty) {
      return 'packet::$packetMode::$packetId';
    }

    final smsId = row['sms_id'] as int?;
    if (smsId != null) {
      return 'sms::$smsId';
    }

    final rowId = row['id'] as int?;
    if (rowId != null) {
      return '${isGroup ? 'group' : 'cache'}::$rowId';
    }

    final date = row['date'] as int? ?? 0;
    return 'body::$date::${body.hashCode}';
  }

  Future<int> getUnreadCount(String address) async {
    final db = await instance.database;
    final rows = await db.query(
      'sms_cache',
      columns: ['id', 'sms_id', 'body', 'packet_id', 'packet_mode', 'date'],
      where: 'address = ? AND is_read = 0 AND is_me = 0',
      whereArgs: [_normalizePhone(address)],
      orderBy: 'date DESC, id DESC',
    );

    final logicalMessages = <String>{};
    for (final row in rows) {
      final key = _unreadLogicalKey(Map<String, dynamic>.from(row));
      if (key != null) {
        logicalMessages.add(key);
      }
    }
    return logicalMessages.length;
  }

  Future<int> getGroupUnreadCount(int groupId) async {
    final db = await instance.database;
    final rows = await db.query(
      'group_messages',
      columns: [
        'id',
        'body',
        'packet_id',
        'packet_mode',
        'date',
        'sender_phone'
      ],
      where: 'group_id = ? AND is_read = 0 AND sender_phone IS NOT NULL',
      whereArgs: [groupId],
      orderBy: 'date DESC, id DESC',
    );

    final logicalMessages = <String>{};
    for (final row in rows) {
      final key =
          _unreadLogicalKey(Map<String, dynamic>.from(row), isGroup: true);
      if (key != null) {
        logicalMessages.add(key);
      }
    }
    return logicalMessages.length;
  }

  Future<void> batchInsertSms(List<Map<String, dynamic>> smsList) async {
    final db = await instance.database;
    const duplicateWindowMs = 5000;

    await db.transaction((txn) async {
      for (final sms in smsList) {
        final data = Map<String, dynamic>.from(sms);
        final smsId = data['sms_id'] as int?;
        final address = _normalizePhone(data['address'] as String? ?? '');
        final body = data['body'] as String? ?? '';
        final date = data['date'] as int? ?? 0;
        final isMe = data['is_me'] as int? ?? 0;
        final parsed = ProtocolHelper.parseMessage(body);
        final parsedType = parsed['type'] as String? ?? 'plain';
        final parsedPacketId = parsed['packetId'] as String?;
        final parsedPacketMode = switch (parsedType) {
          'sym' || 'sfra' => 'SYM',
          'asym' || 'afrag' => 'AE',
          _ => null,
        };
        final isSecureProtocol = parsedPacketMode != null;

        data['address'] = address;

        if (smsId != null) {
          final existingBySmsId = await txn.query(
            'sms_cache',
            columns: ['id'],
            where: 'sms_id = ?',
            whereArgs: [smsId],
            limit: 1,
          );
          if (existingBySmsId.isNotEmpty) {
            await txn.delete(
              'sms_cache',
              where:
                  'sms_id IS NULL AND address = ? AND body = ? AND is_me = ? AND ABS(date - ?) <= ?',
              whereArgs: [address, body, isMe, date, duplicateWindowMs],
            );

            if (isSecureProtocol) {
              List<Map<String, Object?>> staleSecureRows = [];
              if (parsedPacketId != null && parsedPacketId.isNotEmpty) {
                staleSecureRows = await txn.query(
                  'sms_cache',
                  columns: ['id'],
                  where:
                      'sms_id IS NULL AND address = ? AND is_me = ? AND is_secure = 1 AND packet_id = ? AND packet_mode = ? AND ABS(date - ?) <= ?',
                  whereArgs: [
                    address,
                    isMe,
                    parsedPacketId,
                    parsedPacketMode,
                    date,
                    duplicateWindowMs,
                  ],
                  orderBy: 'ABS(date - $date) ASC, id DESC',
                  limit: 1,
                );
              }

              if (staleSecureRows.isEmpty) {
                staleSecureRows = await txn.query(
                  'sms_cache',
                  columns: ['id'],
                  where:
                      'sms_id IS NULL AND address = ? AND is_me = ? AND is_secure = 1 AND packet_mode = ? AND ABS(date - ?) <= ?',
                  whereArgs: [
                    address,
                    isMe,
                    parsedPacketMode,
                    date,
                    duplicateWindowMs,
                  ],
                  orderBy: 'ABS(date - $date) ASC, id DESC',
                  limit: 1,
                );
              }

              if (staleSecureRows.isNotEmpty) {
                await txn.delete(
                  'sms_cache',
                  where: 'id = ?',
                  whereArgs: [staleSecureRows.first['id']],
                );
              }
            }
            continue;
          }

          final syncedMatch = await txn.query(
            'sms_cache',
            columns: ['id'],
            where:
                'sms_id IS NULL AND address = ? AND body = ? AND is_me = ? AND ABS(date - ?) <= ?',
            whereArgs: [address, body, isMe, date, duplicateWindowMs],
            orderBy: 'date DESC, id DESC',
            limit: 1,
          );

          if (syncedMatch.isNotEmpty) {
            await txn.update(
              'sms_cache',
              {
                'sms_id': smsId,
                'date': date,
                'type': data['type'],
                'is_me': isMe,
                'address': address,
              },
              where: 'id = ?',
              whereArgs: [syncedMatch.first['id']],
            );
            continue;
          }

          if (isSecureProtocol) {
            List<Map<String, Object?>> secureFallbackMatch = [];
            if (parsedPacketId != null && parsedPacketId.isNotEmpty) {
              secureFallbackMatch = await txn.query(
                'sms_cache',
                columns: ['id'],
                where:
                    'sms_id IS NULL AND address = ? AND is_me = ? AND is_secure = 1 AND packet_id = ? AND packet_mode = ? AND ABS(date - ?) <= ?',
                whereArgs: [
                  address,
                  isMe,
                  parsedPacketId,
                  parsedPacketMode,
                  date,
                  duplicateWindowMs,
                ],
                orderBy: 'ABS(date - $date) ASC, id DESC',
                limit: 1,
              );
            }

            if (secureFallbackMatch.isEmpty) {
              secureFallbackMatch = await txn.query(
                'sms_cache',
                columns: ['id'],
                where:
                    'sms_id IS NULL AND address = ? AND is_me = ? AND is_secure = 1 AND packet_mode = ? AND ABS(date - ?) <= ?',
                whereArgs: [
                  address,
                  isMe,
                  parsedPacketMode,
                  date,
                  duplicateWindowMs,
                ],
                orderBy: 'ABS(date - $date) ASC, id DESC',
                limit: 1,
              );
            }

            if (secureFallbackMatch.isNotEmpty) {
              await txn.update(
                'sms_cache',
                {
                  'sms_id': smsId,
                  'date': date,
                  'type': data['type'],
                  'is_me': isMe,
                  'address': address,
                  'packet_id': parsedPacketId,
                  'packet_mode': parsedPacketMode,
                  'is_secure': 1,
                },
                where: 'id = ?',
                whereArgs: [secureFallbackMatch.first['id']],
              );
              continue;
            }
          }
        }

        await txn.insert(
          'sms_cache',
          data,
          conflictAlgorithm: ConflictAlgorithm.ignore,
        );
      }
    });
  }

  Future<List<Map<String, dynamic>>> getPaginatedSms(
      String address, int limit, int offset,
      {int? afterDate}) async {
    final db = await instance.database;
    final normalized = _normalizePhone(address);
    const orderBy = 'date DESC, sms_id DESC, id DESC';
    if (afterDate == null) {
      return db.query('sms_cache',
          where: 'address = ?',
          whereArgs: [normalized],
          orderBy: orderBy,
          limit: limit,
          offset: offset);
    } else {
      return db.query('sms_cache',
          where: 'address = ? AND date >= ?',
          whereArgs: [normalized, afterDate],
          orderBy: orderBy,
          limit: limit,
          offset: offset);
    }
  }

  Future<int> getSmsCount(String address) async {
    final db = await instance.database;
    final normalized = _normalizePhone(address);
    final result = await db.rawQuery(
        'SELECT COUNT(*) FROM sms_cache WHERE address = ?', [normalized]);
    return Sqflite.firstIntValue(result) ?? 0;
  }

  Future<List<Map<String, dynamic>>> getConversations() async {
    final db = await instance.database;
    // Uses a subquery to find the latest message ID for each unique address
    return await db.rawQuery('''
      SELECT * FROM sms_cache 
      WHERE id IN (SELECT MAX(id) FROM sms_cache GROUP BY address)
      ORDER BY date DESC
    ''');
  }

  Future<int> clearSmsCacheForAddress(String address) async {
    final db = await instance.database;
    final normalized = _normalizePhone(address);
    return db
        .delete('sms_cache', where: 'address = ?', whereArgs: [normalized]);
  }

  Future<int> updateSmsBody(int id, String newBody,
      {int? isSecure, String? packetId, String? packetMode}) async {
    final db = await database;
    final Map<String, dynamic> values = {'body': newBody};
    if (isSecure != null) {
      values['is_secure'] = isSecure;
    }
    if (packetId != null) {
      values['packet_id'] = packetId;
    }
    if (packetMode != null) {
      values['packet_mode'] = packetMode;
    }
    return await db.update(
      'sms_cache',
      values,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<int> consolidatePacketMessage(
    String address, {
    required String packetId,
    required String packetMode,
    required String body,
    required int date,
    required int type,
    required int isMe,
    int isRead = 1,
    int isSecure = 1,
  }) async {
    final db = await database;
    final normalized = _normalizePhone(address);
    final rows = await db.query(
      'sms_cache',
      columns: ['id'],
      where: 'address = ? AND packet_id = ? AND packet_mode = ?',
      whereArgs: [normalized, packetId, packetMode],
      orderBy: 'date DESC, sms_id DESC, id DESC',
    );

    if (rows.isNotEmpty) {
      final keeperId = rows.first['id'] as int;
      await db.update(
        'sms_cache',
        {
          'body': body,
          'date': date,
          'type': type,
          'is_me': isMe,
          'is_read': isRead,
          'is_secure': isSecure,
          'packet_id': packetId,
          'packet_mode': packetMode,
        },
        where: 'id = ?',
        whereArgs: [keeperId],
      );

      if (rows.length > 1) {
        final duplicateIds =
            rows.skip(1).map((row) => row['id'] as int).toList(growable: false);
        await db.delete(
          'sms_cache',
          where: 'id IN (${List.filled(duplicateIds.length, '?').join(', ')})',
          whereArgs: duplicateIds,
        );
      }

      return keeperId;
    }

    return db.insert('sms_cache', {
      'address': normalized,
      'body': body,
      'date': date,
      'type': type,
      'is_me': isMe,
      'is_read': isRead,
      'is_secure': isSecure,
      'packet_id': packetId,
      'packet_mode': packetMode,
    });
  }

  Future<int> deleteSmsFragments(String address, String packetId) async {
    final db = await database;
    return await db.delete(
      'sms_cache',
      where: 'address = ? AND (packet_id = ? OR body LIKE ?)',
      whereArgs: [_normalizePhone(address), packetId, '%|$packetId|%'],
    );
  }

  Future<int> deleteGroupFragments(int groupId, String packetId) async {
    final db = await database;
    return await db.delete(
      'group_messages',
      where: 'group_id = ? AND body LIKE ?',
      whereArgs: [groupId, '%|$packetId|%'],
    );
  }
}