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');
    if(databasesPath != null) {
      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?.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],
    );
  }

}
