supplements/lib/services/database_sync_service.dart

import 'dart:io' as io;
import 'package:flutter/foundation.dart';
import 'package:path/path.dart';
import 'package:shared_preferences/shared_preferences.dart';
import 'package:sqflite/sqflite.dart';
import 'package:supplements/logging.dart';
import 'package:webdav_client/webdav_client.dart';

import '../models/supplement.dart';
import '../models/supplement_intake.dart';
import 'database_helper.dart';

enum SyncStatus {
  idle,
  downloading,
  merging,
  uploading,
  completed,
  error,
}

// Legacy record-level sync status for models
enum RecordSyncStatus {
  pending,
  synced,
  modified,
}

class DatabaseSyncService {
  static const String _remoteDbFileName = 'supplements.db';

  // SharedPreferences keys for persistence
  static const String _keyServerUrl = 'sync_server_url';
  static const String _keyUsername = 'sync_username';
  static const String _keyPassword = 'sync_password';
  static const String _keyRemotePath = 'sync_remote_path';

  Client? _client;
  String? _remotePath;

  // Store configuration values
  String? _serverUrl;
  String? _username;
  String? _password;
  String? _configuredRemotePath;

  final DatabaseHelper _databaseHelper = DatabaseHelper.instance;

  SyncStatus _status = SyncStatus.idle;
  String? _lastError;
  DateTime? _lastSyncTime;

  // Getters
  SyncStatus get status => _status;
  String? get lastError => _lastError;
  DateTime? get lastSyncTime => _lastSyncTime;
  bool get isConfigured => _client != null;

  // Configuration getters
  String? get serverUrl => _serverUrl;
  String? get username => _username;
  String? get password => _password;
  String? get remotePath => _configuredRemotePath;

  // Callbacks for UI updates
  Function(SyncStatus)? onStatusChanged;
  Function(String)? onError;
  Function()? onSyncCompleted;

  DatabaseSyncService() {
    loadSavedConfiguration();
  }

  // Load saved configuration from SharedPreferences
  Future<void> loadSavedConfiguration() async {
    try {
      final prefs = await SharedPreferences.getInstance();
      _serverUrl = prefs.getString(_keyServerUrl);
      _username = prefs.getString(_keyUsername);
      _password = prefs.getString(_keyPassword);
      _configuredRemotePath = prefs.getString(_keyRemotePath);

      // If we have saved configuration, set up the client
      if (_serverUrl != null && _username != null && _password != null && _configuredRemotePath != null) {
        _remotePath = _configuredRemotePath!.endsWith('/') ? _configuredRemotePath : '$_configuredRemotePath/';

        _client = newClient(
          _serverUrl!,
          user: _username!,
          password: _password!,
          debug: kDebugMode,
        );
      }
    } catch (e) {
      if (kDebugMode) {
        printLog('Error loading saved sync configuration: $e');
      }
    }
  }

  // Save configuration to SharedPreferences
  Future<void> _saveConfiguration() async {
    try {
      final prefs = await SharedPreferences.getInstance();
      if (_serverUrl != null) await prefs.setString(_keyServerUrl, _serverUrl!);
      if (_username != null) await prefs.setString(_keyUsername, _username!);
      if (_password != null) await prefs.setString(_keyPassword, _password!);
      if (_configuredRemotePath != null) await prefs.setString(_keyRemotePath, _configuredRemotePath!);
    } catch (e) {
      if (kDebugMode) {
        printLog('Error saving sync configuration: $e');
      }
    }
  }

  void configure({
    required String serverUrl,
    required String username,
    required String password,
    required String remotePath,
  }) {
    // Store configuration values
    _serverUrl = serverUrl;
    _username = username;
    _password = password;
    _configuredRemotePath = remotePath;

    _remotePath = remotePath.endsWith('/') ? remotePath : '$remotePath/';

    _client = newClient(
      serverUrl,
      user: username,
      password: password,
      debug: kDebugMode,
    );

    // Save configuration to persistent storage
    _saveConfiguration();
  }

  Future<bool> testConnection() async {
    if (_client == null) return false;

    try {
      await _client!.ping();
      return true;
    } catch (e) {
      if (kDebugMode) {
        printLog('Connection test failed: $e');
      }
      return false;
    }
  }

  Future<void> syncDatabase() async {
    if (_client == null) {
      throw Exception('Sync not configured');
    }

    _setStatus(SyncStatus.downloading);

    try {
      // Step 1: Download remote database (if it exists)
      final remoteDbPath = await _downloadRemoteDatabase();

      // Step 2: Merge databases
      _setStatus(SyncStatus.merging);
      await _mergeDatabases(remoteDbPath);

      // Step 3: Upload merged database
      _setStatus(SyncStatus.uploading);
      await _uploadLocalDatabase();

      // Step 4: Cleanup - for now we'll skip cleanup to avoid file issues
      // TODO: Implement proper cleanup once file operations are working

      _lastSyncTime = DateTime.now();
      _setStatus(SyncStatus.completed);
      onSyncCompleted?.call();

    } catch (e) {
      _lastError = e.toString();
      _setStatus(SyncStatus.error);
      onError?.call(_lastError!);
      if (kDebugMode) {
        printLog('Sync failed: $e');
      }
      rethrow;
    }
  }

  Future<String?> _downloadRemoteDatabase() async {
    try {
      // Check if remote database exists
      final files = await _client!.readDir(_remotePath!);
      final remoteDbExists = files.any((file) => file.name == _remoteDbFileName);

      if (!remoteDbExists) {
        if (kDebugMode) {
          printLog('No remote database found, will upload local database');
        }
        return null;
      }

      if (kDebugMode) {
        printLog('Remote database found, downloading...');
      }

      // Download the remote database
      final remoteDbBytes = await _client!.read('$_remotePath$_remoteDbFileName');

      // Create a temporary file path for the downloaded database
      final tempDir = await getDatabasesPath();
      final tempDbPath = join(tempDir, 'remote_supplements.db');

      // Write the downloaded database to a temporary file
      final tempFile = io.File(tempDbPath);
      await tempFile.writeAsBytes(remoteDbBytes);

      if (kDebugMode) {
        printLog('Downloaded remote database (${remoteDbBytes.length} bytes) to: $tempDbPath');
      }

      return tempDbPath;

    } catch (e) {
      if (kDebugMode) {
        printLog('Failed to download remote database: $e');
      }
      return null;
    }
  }

  Future<void> _mergeDatabases(String? remoteDbPath) async {
    if (remoteDbPath == null) {
      if (kDebugMode) {
        printLog('No remote database to merge');
      }
      return;
    }

    if (kDebugMode) {
      printLog('Starting database merge from: $remoteDbPath');
    }

    final localDb = await _databaseHelper.database;
    final remoteDb = await openDatabase(remoteDbPath, readOnly: true);

    try {
      // Check what tables exist in remote database
      if (kDebugMode) {
        final tables = await remoteDb.rawQuery("SELECT name FROM sqlite_master WHERE type='table'");
        printLog('Remote database tables: ${tables.map((t) => t['name']).toList()}');

        // Count records in each table
        try {
          final supplementCount = await remoteDb.rawQuery('SELECT COUNT(*) as count FROM supplements');
          printLog('Remote supplements count: ${supplementCount.first['count']}');
        } catch (e) {
          printLog('Error counting supplements: $e');
        }

        try {
          final intakeCount = await remoteDb.rawQuery('SELECT COUNT(*) as count FROM supplement_intakes');
          printLog('Remote intakes count: ${intakeCount.first['count']}');
        } catch (e) {
          printLog('Error counting intakes: $e');
        }
      }

      // Merge supplements
      await _mergeSupplements(localDb, remoteDb);

      // Merge intakes
      await _mergeIntakes(localDb, remoteDb);

      if (kDebugMode) {
        printLog('Database merge completed successfully');
      }

    } finally {
      await remoteDb.close();
    }
  }

  Future<void> _mergeSupplements(Database localDb, Database remoteDb) async {
    if (kDebugMode) {
      printLog('Starting supplement merge...');
    }

    // Get all supplements from remote database
    final remoteMaps = await remoteDb.query('supplements');
    final remoteSupplements =
        remoteMaps.map((map) => Supplement.fromMap(map)).toList();

    if (kDebugMode) {
      printLog(
          'Found ${remoteSupplements.length} supplements in remote database');
      for (final supplement in remoteSupplements) {
        printLog(
            'Remote supplement: ${supplement.name} (syncId: ${supplement.syncId}, deleted: ${supplement.isDeleted})');
      }
    }

    for (final remoteSupplement in remoteSupplements) {
      if (remoteSupplement.syncId.isEmpty) {
        if (kDebugMode) {
          printLog(
              'Skipping supplement ${remoteSupplement.name} - no syncId');
        }
        continue;
      }

      // Find existing supplement by syncId
      final existingMaps = await localDb.query(
        'supplements',
        where: 'syncId = ?',
        whereArgs: [remoteSupplement.syncId],
      );

      if (existingMaps.isEmpty) {
        // New supplement from remote - insert it
        if (!remoteSupplement.isDeleted) {
          // Manually create a new map without the id to ensure it's null
          final mapToInsert = remoteSupplement.toMap();
          mapToInsert.remove('id');
          await localDb.insert('supplements', mapToInsert);
          if (kDebugMode) {
            printLog(
                '✓ Inserted new supplement: ${remoteSupplement.name}');
          }
        } else {
          if (kDebugMode) {
            printLog(
                'Skipping deleted supplement: ${remoteSupplement.name}');
          }
        }
      } else {
        // Existing supplement - update if remote is newer
        final existingSupplement = Supplement.fromMap(existingMaps.first);

        if (remoteSupplement.lastModified
            .isAfter(existingSupplement.lastModified)) {
          final supplementToUpdate =
              remoteSupplement.copyWith(id: existingSupplement.id);
          await localDb.update(
            'supplements',
            supplementToUpdate.toMap(),
            where: 'id = ?',
            whereArgs: [existingSupplement.id],
          );
          if (kDebugMode) {
            printLog(
                '✓ Updated supplement: ${remoteSupplement.name}');
          }
        } else {
          if (kDebugMode) {
            printLog(
                'Local supplement ${remoteSupplement.name} is newer, keeping local version');
          }
        }
      }
    }

    if (kDebugMode) {
      printLog('Supplement merge completed');
    }
  }

  Future<void> _mergeIntakes(Database localDb, Database remoteDb) async {
    if (kDebugMode) {
      printLog('Starting intake merge...');
    }

    // Get all intakes from remote database
    final remoteMaps = await remoteDb.query('supplement_intakes');
    final remoteIntakes = remoteMaps.map((map) => SupplementIntake.fromMap(map)).toList();

    if (kDebugMode) {
      printLog('Found ${remoteIntakes.length} intakes in remote database');
    }

    for (final remoteIntake in remoteIntakes) {
      if (remoteIntake.syncId.isEmpty) {
        if (kDebugMode) {
          printLog('Skipping intake - no syncId');
        }
        continue;
      }

      // Find existing intake by syncId
      final existingMaps = await localDb.query(
        'supplement_intakes',
        where: 'syncId = ?',
        whereArgs: [remoteIntake.syncId],
      );

      if (existingMaps.isEmpty) {
        // New intake from remote - need to find local supplement ID
        if (!remoteIntake.isDeleted) {
          final localSupplementId = await _findLocalSupplementId(localDb, remoteIntake.supplementId, remoteDb);
          if (localSupplementId != null) {
            final intakeToInsert = remoteIntake.copyWith(
              id: null,
              supplementId: localSupplementId,
            );
            await localDb.insert('supplement_intakes', intakeToInsert.toMap());
            if (kDebugMode) {
              printLog('✓ Inserted new intake: ${remoteIntake.syncId}');
            }
          } else {
            if (kDebugMode) {
              printLog('Could not find local supplement for intake ${remoteIntake.syncId}');
            }
          }
        } else {
          if (kDebugMode) {
            printLog('Skipping deleted intake: ${remoteIntake.syncId}');
          }
        }
      } else {
        // Existing intake - update if remote is newer
        final existingIntake = SupplementIntake.fromMap(existingMaps.first);

        if (remoteIntake.lastModified.isAfter(existingIntake.lastModified)) {
          final intakeToUpdate = remoteIntake.copyWith(id: existingIntake.id);
          await localDb.update(
            'supplement_intakes',
            intakeToUpdate.toMap(),
            where: 'id = ?',
            whereArgs: [existingIntake.id],
          );
          if (kDebugMode) {
            printLog('✓ Updated intake: ${remoteIntake.syncId}');
          }
        } else {
          if (kDebugMode) {
            printLog('Local intake ${remoteIntake.syncId} is newer, keeping local version');
          }
        }
      }
    }

    if (kDebugMode) {
      printLog('Intake merge completed');
    }
  }

  Future<int?> _findLocalSupplementId(Database localDb, int remoteSupplementId, Database remoteDb) async {
    // Get the remote supplement
    final remoteSupplementMaps = await remoteDb.query(
      'supplements',
      where: 'id = ?',
      whereArgs: [remoteSupplementId],
    );

    if (remoteSupplementMaps.isEmpty) return null;

    final remoteSupplement = Supplement.fromMap(remoteSupplementMaps.first);

    // Find the local supplement with the same syncId
    final localSupplementMaps = await localDb.query(
      'supplements',
      where: 'syncId = ?',
      whereArgs: [remoteSupplement.syncId],
    );

    if (localSupplementMaps.isEmpty) return null;

    return localSupplementMaps.first['id'] as int;
  }

  Future<void> _uploadLocalDatabase() async {
    try {
      // Get the local database path
      final localDb = await _databaseHelper.database;
      final dbPath = localDb.path;

      if (kDebugMode) {
        printLog('Reading database from: $dbPath');
      }

      // Read the database file
      final dbFile = io.File(dbPath);
      if (!await dbFile.exists()) {
        throw Exception('Database file not found at: $dbPath');
      }

      final dbBytes = await dbFile.readAsBytes();

      if (kDebugMode) {
        printLog('Database file size: ${dbBytes.length} bytes');
      }

      if (dbBytes.isEmpty) {
        throw Exception('Database file is empty');
      }

      // Ensure remote directory exists
      try {
        await _client!.readDir(_remotePath!);
      } catch (e) {
        if (kDebugMode) {
          printLog('Creating remote directory: $_remotePath');
        }
        await _client!.mkdir(_remotePath!);
      }

      // Upload the database file
      final remoteUrl = '$_remotePath$_remoteDbFileName';
      await _client!.write(remoteUrl, dbBytes);

      if (kDebugMode) {
        printLog('Successfully uploaded database (${dbBytes.length} bytes) to: $remoteUrl');
      }

    } catch (e) {
      if (kDebugMode) {
        printLog('Failed to upload database: $e');
      }
      rethrow;
    }
  }

  void _setStatus(SyncStatus status) {
    _status = status;
    onStatusChanged?.call(status);
  }

  void clearError() {
    _lastError = null;
  }
}