part of utils;

class SqlManager {
  static const _version = 1;

  static const _name = "zi_jing_app_flutter.db";

  static Database? _database;

  ///初始化
  static init() async {
    // open the database
    var databasesPath = await getDatabasesPath();
    // var databasesPath = await Tools.getDirectory();
    String dbName = _name;
    Console.log('Sql-----------databasesPath---------$databasesPath');
    String path = databasesPath + dbName;
    if (Platform.isIOS) {
      path = "$databasesPath/$dbName";
    }
    _database = await openDatabase(path, version: _version,
        onCreate: (Database db, int version) async {
      // When creating the db, create the table
      // await db.execute("CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)");
      // 笔记表
      await db.execute("CREATE TABLE $kNoteTable ("
          "id INTEGER PRIMARY KEY, "
          "types INTEGER, "
          "book_id INTEGER, "
          "chapter_id INTEGER, "
          "notes_id INTEGER, "
          "is_open INTEGER, "
          "del INTEGER DEFAULT 0, "
          "color TEXT, "
          "content TEXT, "
          "upload INTEGER DEFAULT 0, "
          "positioning TEXT, "
          "note_content TEXT)");
      // // 阅读章节表
      await db.execute("CREATE TABLE $kReadTable ("
          "id INTEGER PRIMARY KEY, "
          "book_id INTEGER, "
          "chapter_id INTEGER)");
    });
  }

  static Future<Database?> getCurrentDatabase() async {
    if (_database == null) {
      await init();
    }
    return _database;
  }

  /// 关闭数据库
  static Future<void> closeDatabase() async {
    await _database?.close();
  }

  /// 查询划线高亮笔记
  static Future<Map<String, dynamic>> queryLocalNote(
      {required int bookId, required int chapterId}) async {
    Database? db = await SqlManager.getCurrentDatabase();
    Map<String, dynamic> returnMap = {};
    // 划线
    List<Map<String, dynamic>>? lineResult = await db?.query(
      kNoteTable,
      columns: ['types', 'chapter_id', 'positioning', 'content', 'color', 'id'],
      where: 'book_id = ? and chapter_id = ? and types = ? and del = ?',
      whereArgs: [bookId, chapterId, 1, 0],
    );
    // 高亮
    List<Map<String, dynamic>>? colorResult = await db?.query(
      kNoteTable,
      columns: ['types', 'chapter_id', 'positioning', 'content', 'color', 'id'],
      where: 'book_id = ? and chapter_id = ? and types = ? and del = ?',
      whereArgs: [bookId, chapterId, 2, 0],
    );
    returnMap['line_list'] = lineResult;
    returnMap['color_line'] = colorResult;
    Console.log('Sql-----------queryLocalNote---------------------$returnMap');
    return returnMap;
  }

  /// 添加划线高亮笔记
  static Future<int> addLocalNote(Map<String, dynamic> data) async {
    Database? db = await SqlManager.getCurrentDatabase();
    final result = await db?.insert(
      kNoteTable,
      data,
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
    return result ?? 0;
  }

  /// 删除划线高亮笔记
  static Future<int> delLocalNote(
      {required int noteId, required int id}) async {
    Database? db = await SqlManager.getCurrentDatabase();
    if (noteId == 0) {
      final result = await db?.update(
        kNoteTable,
        {'del': 1},
        where: 'id = ?',
        whereArgs: [id],
      );
      return result ?? 0;
    } else {
      final result = await db?.update(
        kNoteTable,
        {'del': 1},
        where: 'notes_id = ?',
        whereArgs: [noteId],
      );
      return result ?? 0;
    }
  }

  /// 修改划线高亮笔记
  static Future<int> updateLocalNote(
      {required int notesId,
      required int id,
      required Map<String, dynamic> data}) async {
    Database? db = await SqlManager.getCurrentDatabase();
    if (notesId == 0) {
      final result = await db?.update(
        kNoteTable,
        data,
        where: 'id = ?',
        whereArgs: [id],
      );
      return result ?? 0;
    } else {
      final result = await db?.update(
        kNoteTable,
        data,
        where: 'notes_id = ?',
        whereArgs: [notesId],
      );
      return result ?? 0;
    }
  }

  /// 查询所有没有上传的数据
  static Future<List<Map<String, dynamic>>> queryNoUploadData() async {
    Database? db = await SqlManager.getCurrentDatabase();
    List<Map<String, dynamic>>? results = await db?.query(
      kNoteTable,
      where: 'upload = ? and del = ?',
      whereArgs: [0, 0],
    );
    // List<Map<String, dynamic>>? results = await db?.query(
    //   'members_book_notes',
    //   where: 'del = ?',
    //   whereArgs: [0],
    // );
    return results ?? [];
  }

  /// 将所有 upload 为 0 的数据的 upload 字段值更新为 1
  static Future<void> updateUploadStatus() async {
    Database? db = await SqlManager.getCurrentDatabase();
    final result = await db?.update(
      kNoteTable,
      {'upload': 1},
      where: 'upload = ?',
      whereArgs: [0],
    );
    Console.log('Sql---------------更新数据----------------$result');
  }

  /// 将上传成功的id数据的notes_id 更新为服务器对应数值 并设置upload 为1
  static Future<void> updateNotesId(List<Map<String, dynamic>> data) async {
    Database? db = await SqlManager.getCurrentDatabase();
    // 构建批量更新的SQL语句
    String sql = 'UPDATE members_book_notes SET notes_id = CASE id ';

    // 构建 WHEN THEN 语句
    String whenThenClause = '';
    for (var data in data) {
      int id = data['id'];
      int nodeId = data['notes_id'];
      whenThenClause += 'WHEN $id THEN $nodeId ';
    }
    // 添加 WHEN THEN 语句到 SQL 语句
    sql += whenThenClause;

    // 添加 ELSE 语句
    sql += 'ELSE notes_id END, upload = 1 WHERE id IN (';
    sql += data.map((data) => data['id'].toString()).join(', ');
    sql += ')';

    // 执行 SQL 语句
    await db?.execute(sql);
  }

  static Future<void> clear() async {
    Database? db = await SqlManager.getCurrentDatabase();
    db?.delete(kNoteTable);
    db?.delete(kReadTable);
  }

  /// 根据 book_id 查询当前读到的 章节
  static Future<String> queryReadHistoryByBookId(int bookId) async {
    try {
      Database? db = await SqlManager.getCurrentDatabase();
      if (!db!.isOpen) {
        await init();
      }
      List<Map<String, dynamic>> results = await db.query(
        kReadTable,
        where: 'book_id = ?',
        whereArgs: [bookId],
      );
      return results.isNotEmpty ? results.first['chapter_id'].toString() : '';
    } catch (e) {
      Console.log('Error querying read history by book id: $e');
      return '';
    }
  }

  /// 根据 book_id 写入当前读到的 章节
  static Future<int> updateReadHistoryByBookId(
      int bookId, int chapterId) async {
    Database? db = await SqlManager.getCurrentDatabase();

    final queryResult = await queryReadHistoryByBookId(bookId);
    if (queryResult.isEmpty) {
      Console.log('Sql----------没有当前书籍的数据----------------------');
      final result = await db?.insert(
        'read_history',
        {'chapter_id': chapterId, 'book_id': bookId},
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
      Console.log('Sql----------插入数据结果：$result----------------------');
      return result ?? 0;
    } else {
      Console.log('Sql----------有当前书籍的数据----------------------');
      final result = await db?.update(
        'read_history',
        {'chapter_id': chapterId},
        where: 'book_id = ?',
        whereArgs: [bookId],
      );
      Console.log('Sql----------更新数据结果：$result----------------------');
      return result ?? 0;
    }
  }

  /// 插入数据
  static Future<bool> insertData(Map<String, dynamic> data) async {
    Database? db = await SqlManager.getCurrentDatabase();
    final result = await db?.insert(
      'members_book_notes',
      data,
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
    if (result != null) {
      return true;
    }
    return false;
  }

  /// 查询所有数据
  static Future<List<Map<String, dynamic>>?> queryAllData() async {
    Database? db = await SqlManager.getCurrentDatabase();
    return await db?.query('members_book_notes');
  }

  /// 根据 ID 查询数据
  static Future<Map<String, dynamic>?> queryDataById(int id) async {
    Database? db = await SqlManager.getCurrentDatabase();
    List<Map<String, dynamic>>? results = await db?.query(
      'members_book_notes',
      where: 'id = ?',
      whereArgs: [id],
    );
    return results?.first ?? {};
  }

  /// 更新数据
  static Future<void> updateData(int id, Map<String, dynamic> newData) async {
    Database? db = await SqlManager.getCurrentDatabase();
    await db?.update(
      'members_book_notes',
      newData,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// 删除数据
  static Future<void> deleteData(int id) async {
    Database? db = await SqlManager.getCurrentDatabase();
    await db?.delete(
      'members_book_notes',
      where: 'id = ?',
      whereArgs: [id],
    );
  }
}
