doyin/lib/media-module/services/database_helper.dart

import 'package:path/path.dart';
import 'package:sqflite_sqlcipher/sqflite.dart';
import '../models/chat_model.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;

  factory DatabaseHelper() => _instance;

  DatabaseHelper._internal();

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

  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'chat_database.db');
    return await openDatabase(path, version: 4, onCreate: _onCreate, onUpgrade: _onUpgrade);
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE chat_messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        senderId TEXT NOT NULL,
        receiverId TEXT NOT NULL,
        textMessage TEXT,
        imagePath TEXT,
        voicePath TEXT,
        messageType TEXT NOT NULL,
        timestamp TEXT NOT NULL,
        replyToMessageId TEXT,
        replyToMessageText TEXT,
        replyToMessageSenderId TEXT,
        replyToMessageType TEXT,
        replyToMessageImagePath TEXT
      )
    ''');
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      // Add reply fields to existing table
      await _addColumnIfNotExists(db, 'chat_messages', 'replyToMessageId', 'TEXT');
      await _addColumnIfNotExists(db, 'chat_messages', 'replyToMessageText', 'TEXT');
      await _addColumnIfNotExists(db, 'chat_messages', 'replyToMessageSenderId', 'TEXT');
    }
    if (oldVersion < 3) {
      // Add replyToMessageType field to existing table
      await _addColumnIfNotExists(db, 'chat_messages', 'replyToMessageType', 'TEXT');
    }
    if (oldVersion < 4) {
      // Add replyToMessageImagePath field to existing table
      await _addColumnIfNotExists(db, 'chat_messages', 'replyToMessageImagePath', 'TEXT');
    }
  }

  // Helper method to add column only if it doesn't exist
  Future<void> _addColumnIfNotExists(Database db, String tableName, String columnName, String columnType) async {
    try {
      // Check if column exists by querying table info
      final columns = await db.rawQuery('PRAGMA table_info($tableName)');
      final columnExists = columns.any((column) => column['name'] == columnName);
      
      if (!columnExists) {
        await db.execute('ALTER TABLE $tableName ADD COLUMN $columnName $columnType');
        print('Added column $columnName to table $tableName');
      } else {
        print('Column $columnName already exists in table $tableName');
      }
    } catch (e) {
      print('Error checking/adding column $columnName: $e');
    }
  }

  // Insert a new message
  Future<int> insertMessage(ChatMessage message) async {
    final db = await database;
    return await db.insert('chat_messages', message.toMap());
  }

  // Get all messages between two users
  Future<List<ChatMessage>> getMessages(
    String senderId,
    String receiverId,
  ) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'chat_messages',
      where:
          '(senderId = ? AND receiverId = ?) OR (senderId = ? AND receiverId = ?)',
      whereArgs: [senderId, receiverId, receiverId, senderId],
      orderBy: 'timestamp ASC',
    );

    return List.generate(maps.length, (i) {
      return ChatMessage.fromMap(maps[i]);
    });
  }

  // Get recent messages for a user
  Future<List<ChatMessage>> getRecentMessages(String userId) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.rawQuery(
      '''
      SELECT * FROM chat_messages 
      WHERE senderId = ? OR receiverId = ?
      ORDER BY timestamp DESC
      LIMIT 50
    ''',
      [userId, userId],
    );

    return List.generate(maps.length, (i) {
      return ChatMessage.fromMap(maps[i]);
    });
  }

  // Get last message between two users
  Future<ChatMessage?> getLastMessage(String userId1, String userId2) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'chat_messages',
      where: '(senderId = ? AND receiverId = ?) OR (senderId = ? AND receiverId = ?)',
      whereArgs: [userId1, userId2, userId2, userId1],
      orderBy: 'timestamp DESC',
      limit: 1,
    );

    if (maps.isNotEmpty) {
      return ChatMessage.fromMap(maps.first);
    }
    return null;
  }

  // Delete a message
  Future<int> deleteMessage(int messageId) async {
    final db = await database;
    return await db.delete(
      'chat_messages',
      where: 'id = ?',
      whereArgs: [messageId],
    );
  }

  // Update a message
  Future<int> updateMessage(ChatMessage message) async {
    final db = await database;
    return await db.update(
      'chat_messages',
      message.toMap(),
      where: 'id = ?',
      whereArgs: [message.id],
    );
  }

  // Clear all messages
  Future<int> clearAllMessages() async {
    final db = await database;
    return await db.delete('chat_messages');
  }

  // Drop and recreate the database (for testing)
  Future<void> resetDatabase() async {
    final db = await database;
    await db.close();
    _database = null;
    
    String path = join(await getDatabasesPath(), 'chat_database.db');
    await deleteDatabase(path);
    
    // Reinitialize the database
    await database;
  }

  // Close database
  Future<void> close() async {
    final db = await database;
    db.close();
  }

  // Check database health and table structure
  Future<Map<String, dynamic>> checkDatabaseHealth() async {
    try {
      final db = await database;
      final tables = await db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'");
      final chatMessagesColumns = await db.rawQuery('PRAGMA table_info(chat_messages)');
      
      return {
        'status': 'healthy',
        'database_path': db.path,
        'tables': tables.map((t) => t['name']).toList(),
        'chat_messages_columns': chatMessagesColumns.map((c) => c['name']).toList(),
        'version': 4,
      };
    } catch (e) {
      return {
        'status': 'error',
        'error': e.toString(),
      };
    }
  }

  // Force reset database (use with caution)
  Future<void> forceResetDatabase() async {
    try {
      if (_database != null) {
        await _database!.close();
        _database = null;
      }
      
      String path = join(await getDatabasesPath(), 'chat_database.db');
      await deleteDatabase(path);
      
      // Reinitialize
      await database;
      print('✅ Database reset successfully');
    } catch (e) {
      print('❌ Error resetting database: $e');
      rethrow;
    }
  }
}