flutter-sport/lib/db/message_db.dart

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

import 'package:flutter/material.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:provider/provider.dart';
import 'package:sport/bean/login.dart';
import 'package:sport/bean/message.dart';
import 'package:sport/provider/user_model.dart';
import 'package:sqflite/sqflite.dart';

class MessageItem {
  int? status;
  int? curId;
  int? id;

  // 时间
  DateTime? dateTime;
  int? day;
  int? time;

  String? type;
  MessageData? data;
  bool? self;

  MessageItem({this.status, this.curId, this.id, this.dateTime, this.day, this.time, this.type, this.data, this.self});

  MessageItem.fromJson(Map<String, dynamic> map) {
    id = map['id'];
    dateTime = DateTime.parse(map['created_at']);
    day = dateTime?.day;
    time = dateTime?.millisecondsSinceEpoch;
    if (map['data'] != null) {
      try {
        data = MessageData.fromJson(json.decode(map['data']));
      } catch (e) {
        print(e);
      }
    }
    self = (map['self'] ?? 0) == 1;
    curId = map['curId'];
    type = map['type'];
  }
}

class MessageDB {
  static final MessageDB _instance = new MessageDB.internal();

  factory MessageDB() => _instance;

  static Database? _db;

  MessageDB.internal();

  Future<Database> get db async {
    if (_db != null) {
      return _db!;
    }
    _db = await initDb();

    return _db!;
  }

  final String TABLE = 'Message';
  final String USERTABLE = "user";

  initDb() async {
    String databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'message.db');

    var db = await openDatabase(path, version: 6, onCreate: _onCreate, onUpgrade: _onUpgrade);
    return db;
  }

  FutureOr<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (newVersion > oldVersion) {
      try {
        await db.execute("drop table $TABLE");
      } catch (e) {
        print(e);
      }
      try {
        await db.execute("drop table $USERTABLE");
      } catch (e) {
        print(e);
      }
    }
    await _onCreate(db, newVersion);
  }

  // @messageId 本地数据库的id
  // @status    该条数据是否是已读的
  // @message   信息
  // @userId    跟谁聊天的信息 跟谁 跟谁 跟谁 跟谁 ...
  FutureOr<void> _onCreate(Database db, int version) async {
    // 表1: message表
    await db.execute(
        'CREATE TABLE $TABLE(id INTEGER PRIMARY KEY, chat_id INTEGER, status INTEGER, self INTEGER, type TEXT, data TEXT, curId INTEGER, created_at TEXT)');

    // 表2: 用户表
    await db.execute('CREATE TABLE $USERTABLE(id INTEGER PRIMARY KEY, me INTEGER, user_id INTEGER, name TEXT , avatar TEXT, isTop INTEGER)');
  }

  Future<int> createChatUser(int me, MessageUser user) async {
    var dbClient = await db;
    var result = await dbClient.insert(USERTABLE, {
      "me": me,
      "user_id": user.id,
      "name": user.name,
      "avatar": user.avatar,
      "isTop": 0,
    });
    return result;
  }

  Future insertAll(List<Map<String, dynamic>> items) async {
    var dbClient = await db;
    var batch = dbClient.batch();
    items.forEach((item) {
      batch.insert(TABLE, item);
    });
    batch.commit();
  }

  // 最新的那些...
  Future<List<Map<String, dynamic>>> findLatest() async {
    var dbClient = await db;
    return await dbClient.rawQuery(
      'SELECT * FROM $TABLE ORDER BY curId DESC LIMIT 1',
    );
  }

  Future<List<Map<String, dynamic>>> findAll() async {
    var dbClient = await db;
    return await dbClient.rawQuery(
      'SELECT * FROM $TABLE ORDER BY messageId DESC',
    );
  }

  Future<List<Map<String, dynamic>>> findHasUserId(int me, int userId) async {
    var dbClient = await db;
    return await dbClient.rawQuery("SELECT * FROM $USERTABLE where me = $me and user_id = $userId limit 1");
  }

  // 更新状态...
  Future updateStatus(int chatId) async {
    var dbClient = await db;

    // status = 0：已读状态, 1：未读
    await dbClient.update(
      TABLE,
      {"status": 0},
      where: "chat_id = ?",
      whereArgs: [chatId],
    );
  }

  Future updateData(MessageItem item) async {
    var dbClient = await db;
    item.status = 0;
    await dbClient.update(
      TABLE,
      {"data": json.encode(item.data)},
      where: "id = ?",
      whereArgs: [item.id],
    );
  }

  // 更新状态...
  Future updateIsTop(int id, int isTop) async {
    var dbClient = await db;
    // status = 0 表示 没有置顶  ... 1:已经置顶
    await dbClient.update(
      USERTABLE,
      {"isTop": isTop},
      // Ensure that the Dog has a matching id.
      where: "id = ?",
      // Pass the Dog's id as a whereArg to prevent SQL injection.
      whereArgs: [id],
    );
  }

  Future<List<Map<String, dynamic>>> getMessageForUserId(int chatId) async {
    var dbClient = await db;
    return await dbClient.rawQuery(
      'SELECT * FROM $TABLE WHERE chat_id = $chatId order by id desc',
    );
  }

  // 删除数据表格
  Future<int> delete() async {
    var dbClient = await db;
    return await dbClient.delete("$TABLE");
  }

  // 获取还有多少未读的数量
  Future<List<Map<String, dynamic>>> getMessageUnRead(String self) async {
    var dbClient = await db;
    return await dbClient.rawQuery("select u.*, c.*, ccc.cout from "
        "$TABLE c, "
        "(select chat_id, max(id) mid FROM $TABLE group by chat_id) cc,"
        "(select chat_id, sum(status) cout FROM $TABLE group by chat_id) ccc, "
        "$USERTABLE u "
        "where u.me = $self and u.id = ccc.chat_id and cc.chat_id = ccc.chat_id and c.id = cc.mid order by u.isTop desc, c.id desc;");
  }

  // 删除数据库
  void deleteTable() async {
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'message.db');
    await deleteDatabase(path);
    print('删除数据库');
  }

  Future<int> deleteMessage(int messageId) async {
    var dbClient = await db;
    return await dbClient.delete("$TABLE", where: "messageId = ?", whereArgs: [messageId]);
  }

  Future<int> deleteUserIdMessage(int userId) async {
    var dbClient = await db;
    return await dbClient.delete("$TABLE", where: "chat_id=?", whereArgs: [userId]);
  }

  Future<int> deleteMessageIdMessage(int messageId) async {
    var dbClient = await db;
    return await dbClient.delete("$TABLE", where: "id=?", whereArgs: [messageId]);
  }

  void updateChatUser(int id, MessageUser other) async {
    var dbClient = await db;
    await dbClient.update(
      USERTABLE,
      {"name": other.name, "avatar": other.avatar},
      where: "id = ?",
      whereArgs: [id],
    );
  }

//  Future<int> delete(int time) async {
//    var dbClient = await db;
//    return await dbClient.delete(TABLE, where: "time <= ?", whereArgs: [time]);
//  }
}