votianlt/app/lib/services/database_service.dart

import 'dart:async';
import 'dart:convert';

import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:votianlt_app/services/developer.dart' as developer;

import '../models/chat_message.dart';
import '../models/job.dart';
import '../models/queued_message.dart';
import '../objectbox.g.dart';
import '../entities/job_entity.dart';
import '../entities/task_status_entity.dart';
import '../entities/user_data_entity.dart';
import '../entities/photo_entity.dart';
import '../entities/queued_message_entity.dart';
import '../entities/chat_message_entity.dart';

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  factory DatabaseService() => _instance;
  DatabaseService._internal();

  Store? _store;
  Completer<void>? _initializingCompleter;

  bool get isInitialized => _store != null;

  /// Initialize ObjectBox database
  Future<void> initialize() async {
    if (_store != null) {
      return;
    }
    if (_initializingCompleter != null) {
      return _initializingCompleter!.future;
    }

    final completer = Completer<void>();
    _initializingCompleter = completer;
    try {
      developer.log(
        'Initializing ObjectBox database...',
        name: 'DatabaseService',
      );

      // Get database path
      final docsDir = await getApplicationDocumentsDirectory();
      final path = join(docsDir.path, 'objectbox');

      // Open ObjectBox store
      _store = await openStore(directory: path);

      developer.log(
        'ObjectBox database initialized successfully',
        name: 'DatabaseService',
      );
      await _logDatabaseStats();
      completer.complete();
    } catch (e, stackTrace) {
      developer.log(
        'Error initializing ObjectBox database: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
      if (!completer.isCompleted) {
        completer.completeError(e);
      }
      rethrow;
    } finally {
      _initializingCompleter = null;
    }
  }

  Future<void> ensureInitialized() async {
    if (isInitialized) {
      return;
    }
    await initialize();
  }

  /// Log database statistics
  Future<void> _logDatabaseStats() async {
    try {
      if (_store == null) return;

      final jobCount = _store!.box<JobEntity>().count();
      final taskStatusCount = _store!.box<TaskStatusEntity>().count();
      final userDataCount = _store!.box<UserDataEntity>().count();

      developer.log(
        'Database stats - Jobs: $jobCount, Task statuses: $taskStatusCount, User data: $userDataCount',
        name: 'DatabaseService',
      );
    } catch (e) {
      developer.log(
        'Error getting database stats: $e',
        name: 'DatabaseService',
      );
    }
  }

  /// Save jobs to database
  Future<void> saveJobs(List<Job> jobs) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      developer.log(
        'Saving ${jobs.length} jobs to database...',
        name: 'DatabaseService',
      );

      final now = DateTime.now();
      final jobBox = _store!.box<JobEntity>();
      final taskStatusBox = _store!.box<TaskStatusEntity>();

      // Clear existing jobs and related task statuses before inserting new ones
      jobBox.removeAll();
      taskStatusBox.removeAll();

      // Save new jobs
      for (final job in jobs) {
        final normalized = job.normalized();
        final jobEntity = JobEntity(
          jobId: normalized.id,
          jobData: jsonEncode(normalized.toJson()),
          createdAt: now,
          updatedAt: now,
        );
        jobBox.put(jobEntity);

        // Also persist task completion states from JSON (adopt status on load)
        // Only set completed=true entries to avoid overwriting local progress with false
        for (final task in normalized.tasks) {
          if (task.completed) {
            final taskStatusEntity = TaskStatusEntity(
              taskId: task.id,
              completed: true,
              completedAt: now,
              createdAt: now,
              updatedAt: now,
            );
            taskStatusBox.put(taskStatusEntity);
          }
        }
      }

      developer.log(
        'Jobs and task statuses saved successfully',
        name: 'DatabaseService',
      );
    } catch (e, stackTrace) {
      developer.log('Error saving jobs: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
    }
  }

  /// Delete a single job by ID
  Future<void> deleteJob(String jobId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      developer.log(
        'Deleting job $jobId from database...',
        name: 'DatabaseService',
      );

      final jobBox = _store!.box<JobEntity>();
      final query = jobBox.query(JobEntity_.jobId.equals(jobId)).build();
      final entities = query.find();
      query.close();

      if (entities.isNotEmpty) {
        jobBox.remove(entities.first.id);
        developer.log(
          'Job $jobId deleted successfully',
          name: 'DatabaseService',
        );
      } else {
        developer.log(
          'Job $jobId not found in database',
          name: 'DatabaseService',
        );
      }
    } catch (e, stackTrace) {
      developer.log('Error deleting job: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
    }
  }

  /// Load jobs from database
  Future<List<Job>> loadJobs() async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return [];
      }

      developer.log('Loading jobs from database...', name: 'DatabaseService');

      final jobBox = _store!.box<JobEntity>();
      final jobEntities = jobBox.getAll();

      final List<Job> jobs = [];

      for (final entity in jobEntities) {
        try {
          final jobData = jsonDecode(entity.jobData);
          final job = Job.fromJson(Map<String, dynamic>.from(jobData));
          jobs.add(job);
        } catch (e) {
          developer.log('Error parsing job data: $e', name: 'DatabaseService');
        }
      }

      // Sort by created_at DESC
      jobs.sort((a, b) => b.createdAt.compareTo(a.createdAt));

      developer.log(
        'Loaded ${jobs.length} jobs from database',
        name: 'DatabaseService',
      );

      // Log message types for job-related messages in database
      if (jobs.isNotEmpty) {
        try {
          final chatBox = _store!.box<ChatMessageEntity>();
          final query =
              chatBox
                  .query(
                    (ChatMessageEntity_.jobId.notNull() |
                        ChatMessageEntity_.jobNumber.notNull()),
                  )
                  .build();
          final messagesWithJobs = query.find();
          query.close();

          developer.log(
            'Found ${messagesWithJobs.length} messages related to jobs in database',
            name: 'DatabaseService',
          );

          // Group by message type and log
          final Map<String, int> messageTypeCount = {};
          for (final msg in messagesWithJobs) {
            final messageType = msg.messageType;
            final jobId = msg.jobId;
            final jobNumber = msg.jobNumber;

            messageTypeCount[messageType] =
                (messageTypeCount[messageType] ?? 0) + 1;

            developer.log(
              'Job-related message: messageType=$messageType, jobId=$jobId, jobNumber=$jobNumber',
              name: 'DatabaseService',
            );
          }

          // Summary log
          if (messageTypeCount.isNotEmpty) {
            developer.log(
              'Message type summary for jobs in database: $messageTypeCount',
              name: 'DatabaseService',
            );
          }
        } catch (e) {
          developer.log(
            'Error logging job message types: $e',
            name: 'DatabaseService',
          );
        }
      }

      return jobs;
    } catch (e, stackTrace) {
      developer.log('Error loading jobs: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
      return [];
    }
  }

  /// Save task completion status
  Future<void> saveTaskStatus(String taskId, bool completed) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final now = DateTime.now();
      final taskStatusBox = _store!.box<TaskStatusEntity>();

      // Find existing entity by taskId
      final query =
          taskStatusBox.query(TaskStatusEntity_.taskId.equals(taskId)).build();
      final existing = query.findFirst();
      query.close();

      if (existing != null) {
        existing.completed = completed;
        existing.completedAt = completed ? now : null;
        existing.updatedAt = now;
        taskStatusBox.put(existing);
      } else {
        final entity = TaskStatusEntity(
          taskId: taskId,
          completed: completed,
          completedAt: completed ? now : null,
          createdAt: now,
          updatedAt: now,
        );
        taskStatusBox.put(entity);
      }

      developer.log(
        'Task status saved: $taskId = $completed',
        name: 'DatabaseService',
      );
    } catch (e, stackTrace) {
      developer.log('Error saving task status: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
    }
  }

  /// Load task completion status
  Future<bool> loadTaskStatus(String taskId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return false;
      }

      final taskStatusBox = _store!.box<TaskStatusEntity>();
      final query =
          taskStatusBox.query(TaskStatusEntity_.taskId.equals(taskId)).build();
      final entity = query.findFirst();
      query.close();

      if (entity != null) {
        return entity.completed;
      }

      return false;
    } catch (e, stackTrace) {
      developer.log('Error loading task status: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
      return false;
    }
  }

  /// Load all task completion statuses
  Future<Map<String, bool>> loadAllTaskStatuses() async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return {};
      }

      final taskStatusBox = _store!.box<TaskStatusEntity>();
      final entities = taskStatusBox.getAll();
      final Map<String, bool> statuses = {};

      for (final entity in entities) {
        statuses[entity.taskId] = entity.completed;
      }

      developer.log(
        'Loaded ${statuses.length} task statuses from database',
        name: 'DatabaseService',
      );
      return statuses;
    } catch (e, stackTrace) {
      developer.log('Error loading task statuses: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
      return {};
    }
  }

  /// Save user ID
  Future<void> saveUserId(String userId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      await saveKeyValue('userId', userId);
      developer.log('User ID saved: $userId', name: 'DatabaseService');
    } catch (e, stackTrace) {
      developer.log('Error saving user ID: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
    }
  }

  /// Load user ID
  Future<String?> loadUserId() async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return null;
      }

      final userId = await loadKeyValue('userId');

      if (userId != null) {
        developer.log('User ID loaded: $userId', name: 'DatabaseService');
        return userId;
      }

      developer.log('No user ID found in database', name: 'DatabaseService');
      return null;
    } catch (e, stackTrace) {
      developer.log('Error loading user ID: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
      return null;
    }
  }

  /// Mark a job as seen (persistently)
  Future<void> setJobSeen(String jobId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }
      await saveKeyValue('job_seen:$jobId', '1');
    } catch (e, stackTrace) {
      developer.log('Error setting job seen: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
    }
  }

  /// Check if a job has been seen
  Future<bool> isJobSeen(String jobId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return false;
      }
      final value = await loadKeyValue('job_seen:$jobId');
      return value != null;
    } catch (e, stackTrace) {
      developer.log('Error checking job seen: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
      return false;
    }
  }

  /// Load seen flags for a set of job IDs (batch)
  Future<Map<String, bool>> loadSeenJobsForIds(Iterable<String> jobIds) async {
    final Map<String, bool> map = {};
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return map;
      }
      if (jobIds.isEmpty) return map;

      final userDataBox = _store!.box<UserDataEntity>();
      final keys = jobIds.map((id) => 'job_seen:$id').toList();

      for (final key in keys) {
        final query =
            userDataBox.query(UserDataEntity_.key.equals(key)).build();
        final entity = query.findFirst();
        query.close();

        final jobId = key.replaceFirst('job_seen:', '');
        map[jobId] = entity != null;
      }
    } catch (e, stackTrace) {
      developer.log('Error loading seen jobs: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
    }
    return map;
  }

  /// Clear all data (for logout)
  Future<void> clearAllData() async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      developer.log('Clearing all database data...', name: 'DatabaseService');

      _store!.box<JobEntity>().removeAll();
      _store!.box<TaskStatusEntity>().removeAll();
      _store!.box<UserDataEntity>().removeAll();

      developer.log('All database data cleared', name: 'DatabaseService');
    } catch (e, stackTrace) {
      developer.log(
        'Error clearing database data: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
    }
  }

  /// Clear all jobs and related data (task statuses, photos).
  /// Preserves user credentials, chat messages, and other user data.
  /// Called after reconnection before notifying server.
  Future<void> clearAllJobsAndRelatedData() async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      developer.log(
        'Clearing all jobs and related data...',
        name: 'DatabaseService',
      );

      final jobBox = _store!.box<JobEntity>();
      final taskStatusBox = _store!.box<TaskStatusEntity>();
      final photoBox = _store!.box<PhotoEntity>();

      final jobCount = jobBox.count();
      final taskStatusCount = taskStatusBox.count();
      final photoCount = photoBox.count();

      jobBox.removeAll();
      taskStatusBox.removeAll();
      photoBox.removeAll();

      // Note: Chat messages are intentionally NOT deleted here
      // to preserve chat history across reconnections.

      developer.log(
        'Cleared $jobCount jobs, $taskStatusCount task statuses, $photoCount photos (chat messages preserved)',
        name: 'DatabaseService',
      );
    } catch (e, stackTrace) {
      developer.log(
        'Error clearing jobs and related data: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
    }
  }

  /// Upsert a single job and update its related task statuses
  Future<void> saveOrUpdateJob(Job job) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final now = DateTime.now();
      final normalized = job.normalized();

      final jobBox = _store!.box<JobEntity>();
      final taskStatusBox = _store!.box<TaskStatusEntity>();

      // Find existing job entity by jobId
      final jobQuery =
          jobBox.query(JobEntity_.jobId.equals(normalized.id)).build();
      final existingJob = jobQuery.findFirst();
      jobQuery.close();

      if (existingJob != null) {
        existingJob.jobData = jsonEncode(normalized.toJson());
        existingJob.updatedAt = now;
        jobBox.put(existingJob);
      } else {
        final jobEntity = JobEntity(
          jobId: normalized.id,
          jobData: jsonEncode(normalized.toJson()),
          createdAt: now,
          updatedAt: now,
        );
        jobBox.put(jobEntity);
      }

      // Update task_status for this job only:
      // 1) Remove any existing statuses for the tasks of this job (to avoid stale entries)
      final taskIds = normalized.tasks.map((t) => t.id).toList();
      if (taskIds.isNotEmpty) {
        for (final taskId in taskIds) {
          final query =
              taskStatusBox
                  .query(TaskStatusEntity_.taskId.equals(taskId))
                  .build();
          final entities = query.find();
          query.close();
          for (final entity in entities) {
            taskStatusBox.remove(entity.id);
          }
        }
      }

      // 2) Insert completed=true entries for tasks coming as completed from JSON
      for (final t in normalized.tasks) {
        if (t.completed) {
          final taskStatusEntity = TaskStatusEntity(
            taskId: t.id,
            completed: true,
            completedAt: now,
            createdAt: now,
            updatedAt: now,
          );
          taskStatusBox.put(taskStatusEntity);
        }
      }

      developer.log(
        'Upserted job ${normalized.id} and updated ${taskIds.length} task statuses',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log('Error in saveOrUpdateJob: $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  Future<void> deleteJobAndRelatedData(Job job) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final trimmedJobId = job.id.trim();
      final jobBox = _store!.box<JobEntity>();
      final userDataBox = _store!.box<UserDataEntity>();
      final taskStatusBox = _store!.box<TaskStatusEntity>();
      final photoBox = _store!.box<PhotoEntity>();

      if (trimmedJobId.isNotEmpty) {
        // Delete job
        final jobQuery =
            jobBox.query(JobEntity_.jobId.equals(trimmedJobId)).build();
        final jobEntities = jobQuery.find();
        jobQuery.close();
        for (final entity in jobEntities) {
          jobBox.remove(entity.id);
        }

        // Delete job_seen flag
        final seenQuery =
            userDataBox
                .query(UserDataEntity_.key.equals('job_seen:$trimmedJobId'))
                .build();
        final seenEntities = seenQuery.find();
        seenQuery.close();
        for (final entity in seenEntities) {
          userDataBox.remove(entity.id);
        }
      }

      final taskIds =
          job.tasks
              .map((task) => task.id.trim())
              .where((id) => id.isNotEmpty)
              .toList();

      if (taskIds.isNotEmpty) {
        for (final taskId in taskIds) {
          // Delete task status
          final taskQuery =
              taskStatusBox
                  .query(TaskStatusEntity_.taskId.equals(taskId))
                  .build();
          final taskEntities = taskQuery.find();
          taskQuery.close();
          for (final entity in taskEntities) {
            taskStatusBox.remove(entity.id);
          }

          // Delete photos
          final photoQuery =
              photoBox.query(PhotoEntity_.taskId.equals(taskId)).build();
          final photoEntities = photoQuery.find();
          photoQuery.close();
          for (final entity in photoEntities) {
            photoBox.remove(entity.id);
          }

          // Delete user data related to task (photos, signatures, barcodes)
          final allUserData = userDataBox.getAll();
          for (final entity in allUserData) {
            if (entity.key.contains(':$taskId')) {
              userDataBox.remove(entity.id);
            }
          }
        }
      }

      final trimmedJobNumber =
          job.jobNumber.trim().isEmpty ? null : job.jobNumber.trim();
      final conversationKeys = <String>[
        if (trimmedJobId.isNotEmpty) 'job:${trimmedJobId.toLowerCase()}',
        if (trimmedJobNumber != null)
          'job_number:${trimmedJobNumber.toLowerCase()}',
      ];
      await deleteChatMessagesForJob(
        jobId: trimmedJobId.isNotEmpty ? trimmedJobId : null,
        jobNumber: trimmedJobNumber,
        conversationKeys: conversationKeys,
      );

      developer.log(
        'Deleted job $trimmedJobId and related local data',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log(
        'Error deleting job ${job.id}: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  /// Save Base64-encoded photos for a task into user_data table (legacy list storage)
  Future<void> saveTaskPhotos(String taskId, List<String> base64Photos) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }
      final key = 'task_photos:$taskId';
      final value = jsonEncode(base64Photos);
      await saveKeyValue(key, value);
      developer.log(
        'Saved ${base64Photos.length} photos for task $taskId',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log('Error saving task photos: $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  /// Load Base64-encoded photos for a task from user_data table
  Future<List<String>> loadTaskPhotos(String taskId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return [];
      }
      final key = 'task_photos:$taskId';
      final raw = await loadKeyValue(key);
      if (raw == null) return [];
      final decoded = jsonDecode(raw);
      if (decoded is List) {
        return decoded.map((e) => e.toString()).toList();
      }
      return [];
    } catch (e, st) {
      developer.log('Error loading task photos: $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return [];
    }
  }

  /// Save photos into the dedicated photos collection (one row per photo)
  Future<void> savePhotosForTask(
    String taskId,
    List<String> base64Photos,
  ) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }
      final photoBox = _store!.box<PhotoEntity>();

      // Clear existing photos for this task first
      final query = photoBox.query(PhotoEntity_.taskId.equals(taskId)).build();
      final existingPhotos = query.find();
      query.close();
      for (final photo in existingPhotos) {
        photoBox.remove(photo.id);
      }

      final now = DateTime.now();
      for (int i = 0; i < base64Photos.length; i++) {
        final data = base64Photos[i];
        final photoEntity = PhotoEntity(
          taskId: taskId,
          photoIndex: i,
          data: data,
          createdAt: now,
        );
        photoBox.put(photoEntity);
      }

      developer.log(
        'Saved ${base64Photos.length} photos into collection for task $taskId',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log(
        'Error saving photos for task to collection: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  /// Save signature SVG for a task into user_data table
  Future<void> saveTaskSignature(String taskId, String svg) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }
      final key = 'task_signature_svg:$taskId';
      await saveKeyValue(key, svg);
      developer.log(
        'Saved signature SVG for task $taskId',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log(
        'Error saving task signature SVG: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  /// Save signature note (Bemerkung) for a task into user_data table
  Future<void> saveTaskSignatureNote(String taskId, String note) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }
      final key = 'task_signature_note:$taskId';
      await saveKeyValue(key, note);
    } catch (e, st) {
      developer.log(
        'Error saving task signature note: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  /// Load signature note (Bemerkung) for a task from user_data table
  Future<String?> loadTaskSignatureNote(String taskId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return null;
      }
      return await loadKeyValue('task_signature_note:$taskId');
    } catch (e, st) {
      developer.log(
        'Error loading task signature note: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return null;
    }
  }

  /// Load signature SVG for a task from user_data table
  Future<String?> loadTaskSignature(String taskId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return null;
      }
      // Try new SVG key first; fallback to legacy PNG key if present
      final svgKey = 'task_signature_svg:$taskId';
      final legacyKey = 'task_signature:$taskId';

      String? result = await loadKeyValue(svgKey);
      result ??= await loadKeyValue(legacyKey);
      return result;
    } catch (e, st) {
      developer.log(
        'Error loading task signature (SVG): $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return null;
    }
  }

  /// Save barcodes for a task into user_data table
  Future<void> saveTaskBarcodes(String taskId, List<String> barcodes) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }
      final key = 'task_barcodes:$taskId';
      final value = jsonEncode(barcodes);
      await saveKeyValue(key, value);
      developer.log(
        'Saved ${barcodes.length} barcodes for task $taskId',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log('Error saving task barcodes: $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  /// Load barcodes for a task from user_data table
  Future<List<String>> loadTaskBarcodes(String taskId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return [];
      }
      final key = 'task_barcodes:$taskId';
      final raw = await loadKeyValue(key);
      if (raw == null) return [];
      final decoded = jsonDecode(raw);
      if (decoded is List) {
        return decoded.map((e) => e.toString()).toList();
      }
      return [];
    } catch (e, st) {
      developer.log('Error loading task barcodes: $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return [];
    }
  }

  /// Close database connections
  Future<void> close() async {
    try {
      _store?.close();
      _store = null;

      developer.log('Database connection closed', name: 'DatabaseService');
    } catch (e, stackTrace) {
      developer.log('Error closing database: $e', name: 'DatabaseService');
      developer.log('Stack trace: $stackTrace', name: 'DatabaseService');
    }
  }

  /// Generic helpers: save and load key-value pairs in user_data table
  Future<void> saveKeyValue(String key, String value) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }
      final now = DateTime.now();
      final userDataBox = _store!.box<UserDataEntity>();

      // Find existing entity by key
      final query = userDataBox.query(UserDataEntity_.key.equals(key)).build();
      final existing = query.findFirst();
      query.close();

      if (existing != null) {
        existing.value = value;
        existing.updatedAt = now;
        userDataBox.put(existing);
      } else {
        final entity = UserDataEntity(
          key: key,
          value: value,
          createdAt: now,
          updatedAt: now,
        );
        userDataBox.put(entity);
      }

      developer.log('Saved key "$key"', name: 'DatabaseService');
    } catch (e, st) {
      developer.log('Error saving key "$key": $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  Future<String?> loadKeyValue(String key) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return null;
      }
      final userDataBox = _store!.box<UserDataEntity>();
      final query = userDataBox.query(UserDataEntity_.key.equals(key)).build();
      final entity = query.findFirst();
      query.close();

      return entity?.value;
    } catch (e, st) {
      developer.log('Error loading key "$key": $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return null;
    }
  }

  Future<void> deleteKeyValue(String key) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }
      final userDataBox = _store!.box<UserDataEntity>();
      final query = userDataBox.query(UserDataEntity_.key.equals(key)).build();
      final entity = query.findFirst();
      query.close();

      if (entity != null) {
        userDataBox.remove(entity.id);
        developer.log('Deleted key "$key"', name: 'DatabaseService');
      }
    } catch (e, st) {
      developer.log('Error deleting key "$key": $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  // Credentials persistence ----------------------------------------------------

  /// Save login credentials for auto-login on app restart
  Future<void> saveCredentials(String email, String password) async {
    final normalizedEmail = email.trim();
    if (normalizedEmail.isEmpty || password.isEmpty) {
      developer.log(
        'Skipping credential save because email or password is empty',
        name: 'DatabaseService',
      );
      return;
    }

    await saveKeyValue('auth_email', normalizedEmail);
    await saveKeyValue('auth_password', password);
    developer.log(
      'Credentials saved for $normalizedEmail',
      name: 'DatabaseService',
    );
  }

  /// Load saved login credentials
  /// Returns null if no credentials are stored
  Future<({String email, String password})?> loadCredentials() async {
    final email = await loadKeyValue('auth_email');
    final password = await loadKeyValue('auth_password');
    final normalizedEmail = email?.trim();

    if (normalizedEmail != null &&
        normalizedEmail.isNotEmpty &&
        password != null &&
        password.isNotEmpty) {
      developer.log(
        'Credentials loaded for $normalizedEmail',
        name: 'DatabaseService',
      );
      return (email: normalizedEmail, password: password);
    }

    if ((email != null && email.isNotEmpty) ||
        (password != null && password.isNotEmpty)) {
      developer.log(
        'Stored credentials are incomplete or empty - removing them',
        name: 'DatabaseService',
      );
      await deleteCredentials();
    }

    developer.log('No valid credentials found', name: 'DatabaseService');
    return null;
  }

  /// Delete saved login credentials (on logout)
  Future<void> deleteCredentials() async {
    await deleteKeyValue('auth_email');
    await deleteKeyValue('auth_password');
    developer.log('Credentials deleted', name: 'DatabaseService');
  }

  // Chat messages persistence -------------------------------------------------

  Future<void> upsertChatMessage(
    ChatMessage message,
    String conversationKey,
  ) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final directionString = chatDirectionToString(message.direction);

      developer.log(
        '[DEBUG_LOG] Upserting message: id=${message.id}, direction=${message.direction} (stored as: $directionString), read=${message.read}',
        name: 'DatabaseService',
      );

      final chatBox = _store!.box<ChatMessageEntity>();

      // Find existing entity by messageId
      final query =
          chatBox
              .query(ChatMessageEntity_.messageId.equals(message.id))
              .build();
      final existing = query.findFirst();
      query.close();

      if (existing != null) {
        existing.conversationKey = conversationKey;
        existing.content = message.content;
        existing.contentType = chatContentTypeToString(message.contentType);
        existing.createdAt = message.createdAt;
        existing.origin = directionString;
        existing.messageType = chatMessageTypeToString(message.messageType);
        existing.jobId = message.jobId;
        existing.jobNumber = message.jobNumber;
        existing.read = message.read;
        existing.pendingSync = message.pendingSync;
        chatBox.put(existing);
      } else {
        final entity = ChatMessageEntity(
          messageId: message.id,
          conversationKey: conversationKey,
          content: message.content,
          contentType: chatContentTypeToString(message.contentType),
          createdAt: message.createdAt,
          origin: directionString,
          messageType: chatMessageTypeToString(message.messageType),
          jobId: message.jobId,
          jobNumber: message.jobNumber,
          read: message.read,
          pendingSync: message.pendingSync,
        );
        chatBox.put(entity);
      }
    } catch (e, st) {
      developer.log(
        'Error saving chat message ${message.id}: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  Future<void> migrateConversationKey(String fromKey, String toKey) async {
    if (fromKey == toKey) {
      return;
    }
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }
      final chatBox = _store!.box<ChatMessageEntity>();
      final query =
          chatBox
              .query(ChatMessageEntity_.conversationKey.equals(fromKey))
              .build();
      final entities = query.find();
      query.close();

      for (final entity in entities) {
        entity.conversationKey = toKey;
        chatBox.put(entity);
      }
    } catch (e, st) {
      developer.log(
        'Error migrating conversation key from "$fromKey" to "$toKey": $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  Future<void> removePendingDuplicates(
    String conversationKey,
    ChatMessage message,
  ) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final chatBox = _store!.box<ChatMessageEntity>();
      final query =
          chatBox
              .query(
                ChatMessageEntity_.conversationKey.equals(conversationKey) &
                    ChatMessageEntity_.pendingSync.equals(true) &
                    ChatMessageEntity_.content.equals(message.content) &
                    ChatMessageEntity_.contentType.equals(
                      chatContentTypeToString(message.contentType),
                    ) &
                    ChatMessageEntity_.messageId.notEquals(message.id),
              )
              .build();
      final entities = query.find();
      query.close();

      for (final entity in entities) {
        chatBox.remove(entity.id);
      }
    } catch (e, st) {
      developer.log(
        'Error removing pending duplicates: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  Future<List<ChatMessage>> loadChatMessages({String? conversationKey}) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return [];
      }

      final chatBox = _store!.box<ChatMessageEntity>();
      List<ChatMessageEntity> entities;

      if (conversationKey != null) {
        final query =
            chatBox
                .query(
                  ChatMessageEntity_.conversationKey.equals(conversationKey),
                )
                .order(ChatMessageEntity_.createdAt)
                .build();
        entities = query.find();
        query.close();
      } else {
        entities = chatBox.getAll();
        entities.sort((a, b) => a.createdAt.compareTo(b.createdAt));
      }

      return entities.map(_chatMessageFromEntity).toList();
    } catch (e, st) {
      developer.log('Error loading chat messages: $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return [];
    }
  }

  Future<Map<String, List<ChatMessage>>> loadAllChatMessagesGrouped() async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return {};
      }

      final chatBox = _store!.box<ChatMessageEntity>();
      final entities = chatBox.getAll();

      // Sort by conversation_key and created_at
      entities.sort((a, b) {
        final keyCompare = a.conversationKey.compareTo(b.conversationKey);
        if (keyCompare != 0) return keyCompare;
        return a.createdAt.compareTo(b.createdAt);
      });

      final Map<String, List<ChatMessage>> grouped = {};
      for (final entity in entities) {
        final key = entity.conversationKey;
        final message = _chatMessageFromEntity(entity);
        final list = grouped.putIfAbsent(key, () => <ChatMessage>[]);
        list.add(message);
      }
      return grouped;
    } catch (e, st) {
      developer.log(
        'Error loading grouped chat messages: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return {};
    }
  }

  Future<void> markConversationRead(String conversationKey) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final chatBox = _store!.box<ChatMessageEntity>();
      final query =
          chatBox
              .query(ChatMessageEntity_.conversationKey.equals(conversationKey))
              .build();
      final entities = query.find();
      query.close();

      for (final entity in entities) {
        entity.read = true;
        chatBox.put(entity);
      }
    } catch (e, st) {
      developer.log(
        'Error marking conversation read: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  Future<void> deleteChatMessage(String messageId) async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final chatBox = _store!.box<ChatMessageEntity>();
      final query =
          chatBox.query(ChatMessageEntity_.messageId.equals(messageId)).build();
      final entities = query.find();
      query.close();

      for (final entity in entities) {
        chatBox.remove(entity.id);
      }
    } catch (e, st) {
      developer.log(
        'Error deleting chat message "$messageId": $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  Future<void> deleteChatMessagesForJob({
    String? jobId,
    String? jobNumber,
    Iterable<String>? conversationKeys,
  }) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final trimmedJobId = jobId?.trim() ?? '';
      final trimmedJobNumber = jobNumber?.trim() ?? '';
      final keysList =
          conversationKeys == null
              ? <String>[]
              : conversationKeys
                  .map((key) => key.trim())
                  .where((key) => key.isNotEmpty)
                  .toSet()
                  .toList();

      if (trimmedJobId.isEmpty &&
          trimmedJobNumber.isEmpty &&
          keysList.isEmpty) {
        developer.log(
          'No chat messages matched deletion criteria for jobId=$jobId jobNumber=$jobNumber',
          name: 'DatabaseService',
        );
        return;
      }

      final chatBox = _store!.box<ChatMessageEntity>();
      final entitiesToDelete = <ChatMessageEntity>[];

      if (trimmedJobId.isNotEmpty) {
        final query =
            chatBox
                .query(ChatMessageEntity_.jobId.equals(trimmedJobId))
                .build();
        entitiesToDelete.addAll(query.find());
        query.close();
      }

      if (trimmedJobNumber.isNotEmpty) {
        final query =
            chatBox
                .query(ChatMessageEntity_.jobNumber.equals(trimmedJobNumber))
                .build();
        entitiesToDelete.addAll(query.find());
        query.close();
      }

      if (keysList.isNotEmpty) {
        for (final key in keysList) {
          final query =
              chatBox
                  .query(ChatMessageEntity_.conversationKey.equals(key))
                  .build();
          entitiesToDelete.addAll(query.find());
          query.close();
        }
      }

      // Remove duplicates by id
      final uniqueIds = <int>{};
      for (final entity in entitiesToDelete) {
        if (uniqueIds.add(entity.id)) {
          chatBox.remove(entity.id);
        }
      }

      developer.log(
        'Deleted chat messages for jobId=$jobId jobNumber=$jobNumber (conversationKeys=${keysList.length})',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log(
        'Error deleting chat messages for jobId=$jobId jobNumber=$jobNumber: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  Future<int> getTotalUnreadMessageCount() async {
    try {
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return 0;
      }

      final chatBox = _store!.box<ChatMessageEntity>();
      final query =
          chatBox.query(ChatMessageEntity_.read.equals(false)).build();
      final count = query.count();
      query.close();

      developer.log(
        '[DEBUG_LOG] Total unread message count: $count',
        name: 'DatabaseService',
      );

      return count;
    } catch (e, st) {
      developer.log(
        'Error getting total unread message count: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return 0;
    }
  }

  ChatMessage _chatMessageFromEntity(ChatMessageEntity entity) {
    return ChatMessage(
      id: entity.messageId,
      content: entity.content,
      contentType: chatContentTypeFromString(entity.contentType),
      createdAt: entity.createdAt,
      direction: chatDirectionFromString(entity.origin),
      messageType: chatMessageTypeFromString(entity.messageType),
      jobId: entity.jobId,
      jobNumber: entity.jobNumber,
      read: entity.read,
      pendingSync: entity.pendingSync,
    );
  }

  // Message Queue Management

  /// Save a failed message to the queue
  Future<void> queueMessage(QueuedMessage message) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final box = _store!.box<QueuedMessageEntity>();

      // Find existing entity by messageId
      final query =
          box.query(QueuedMessageEntity_.messageId.equals(message.id)).build();
      final existing = query.findFirst();
      query.close();

      if (existing != null) {
        existing.topic = message.topic;
        existing.payload = jsonEncode(message.payload);
        existing.createdAt = message.createdAt;
        existing.retryCount = message.retryCount;
        box.put(existing);
      } else {
        final entity = QueuedMessageEntity(
          messageId: message.id,
          topic: message.topic,
          payload: jsonEncode(message.payload),
          createdAt: message.createdAt,
          retryCount: message.retryCount,
        );
        box.put(entity);
      }

      developer.log(
        'Queued message: ${message.id} for topic: ${message.topic}',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log('Error queuing message: $e', name: 'DatabaseService');
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  /// Get all queued messages
  Future<List<QueuedMessage>> getQueuedMessages() async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return [];
      }

      final box = _store!.box<QueuedMessageEntity>();
      final entities = box.getAll();

      // Sort by created_at ASC
      entities.sort((a, b) => a.createdAt.compareTo(b.createdAt));

      return entities.map((entity) {
        return QueuedMessage(
          id: entity.messageId,
          topic: entity.topic,
          payload: jsonDecode(entity.payload),
          createdAt: entity.createdAt,
          retryCount: entity.retryCount,
        );
      }).toList();
    } catch (e, st) {
      developer.log(
        'Error getting queued messages: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return [];
    }
  }

  /// Remove a successfully sent message from the queue
  Future<void> removeQueuedMessage(String messageId) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final box = _store!.box<QueuedMessageEntity>();
      final query =
          box.query(QueuedMessageEntity_.messageId.equals(messageId)).build();
      final entities = query.find();
      query.close();

      for (final entity in entities) {
        box.remove(entity.id);
      }

      developer.log(
        'Removed queued message: $messageId',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log(
        'Error removing queued message: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  /// Update retry count for a message
  Future<void> updateMessageRetryCount(String messageId, int retryCount) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      final box = _store!.box<QueuedMessageEntity>();
      final query =
          box.query(QueuedMessageEntity_.messageId.equals(messageId)).build();
      final entity = query.findFirst();
      query.close();

      if (entity != null) {
        entity.retryCount = retryCount;
        box.put(entity);
      }

      developer.log(
        'Updated retry count for message: $messageId to $retryCount',
        name: 'DatabaseService',
      );
    } catch (e, st) {
      developer.log(
        'Error updating message retry count: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  /// Clear all queued messages (for cleanup)
  Future<void> clearQueuedMessages() async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return;
      }

      _store!.box<QueuedMessageEntity>().removeAll();
      developer.log('Cleared all queued messages', name: 'DatabaseService');
    } catch (e, st) {
      developer.log(
        'Error clearing queued messages: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
    }
  }

  Future<String?> updateChatMessagePendingSync(
    String messageId,
    bool pendingSync,
  ) async {
    try {
      await ensureInitialized();
      if (_store == null) {
        developer.log('Database not initialized', name: 'DatabaseService');
        return null;
      }

      final chatBox = _store!.box<ChatMessageEntity>();
      final query =
          chatBox.query(ChatMessageEntity_.messageId.equals(messageId)).build();
      final entity = query.findFirst();
      query.close();

      if (entity == null) {
        return null;
      }

      entity.pendingSync = pendingSync;
      chatBox.put(entity);
      return entity.conversationKey;
    } catch (e, st) {
      developer.log(
        'Error updating pendingSync for message $messageId: $e',
        name: 'DatabaseService',
      );
      developer.log('Stack trace: $st', name: 'DatabaseService');
      return null;
    }
  }

  // Language preference persistence ----------------------------------------------------

  /// Save language preference
  Future<void> saveLanguagePreference(String languageCode) async {
    await saveKeyValue('language_preference', languageCode);
    developer.log(
      'Language preference saved: $languageCode',
      name: 'DatabaseService',
    );
  }

  /// Load saved language preference
  /// Returns null if no preference is stored
  Future<String?> loadLanguagePreference() async {
    final languageCode = await loadKeyValue('language_preference');
    if (languageCode != null) {
      developer.log(
        'Language preference loaded: $languageCode',
        name: 'DatabaseService',
      );
      return languageCode;
    }
    developer.log('No language preference found', name: 'DatabaseService');
    return null;
  }
}