VitalApp/lib/database/db_patch/v2.dart

import 'package:vitalapp/database/db.dart';
import 'package:vitalapp/database/entities/exam.dart';
import 'package:vitalapp/database/entities/exam_batch.dart';
import 'package:vitalapp/database/entities/followup.dart';
import 'package:fis_common/logger/logger.dart';

import 'base.dart';

class DatabasePatchV2 extends BaseDatabasePatch {
  DatabasePatchV2(super.database);

  @override
  int get targetVersion => 1;

  @override
  Future<void> executeTask() async {
    // 新增表
    await db.database.execute(FollowUpEntity.TABLE_CREATE_SQL);
    await db.database.execute(ExamEntity.TABLE_CREATE_SQL);
    await db.database.execute(ExamBatchEntity.TABLE_CREATE_SQL);

    // patient表新增字段
    await db.database.execute(
        'ALTER TABLE patients ADD COLUMN overallSyncState INTEGER NOT NULL DEFAULT 0;');
    await db.database.execute(
        'ALTER TABLE patients ADD COLUMN followUpCount INTEGER NOT NULL DEFAULT 0;');
    await db.database.execute(
        'ALTER TABLE patients ADD COLUMN diagnosisCount INTEGER NOT NULL DEFAULT 0;');
    await db.database.execute(
        "ALTER TABLE patients ADD COLUMN orgCode VARCHAR(100) NOT NULL DEFAULT '';");

    await _cleanDuplicatePatients();

    await _calcAndUpdateDiagnosisCount();
    await _updateOverallSyncState();

    // 填充历史数据 patient.overallSyncState 值
    await db.database.execute(
        'UPDATE patients SET overallSyncState = syncState WHERE 1=1 AND isValid = 1;');
  }

  /// 检测并清除重复多余的居民数据
  Future<void> _cleanDuplicatePatients() async {
    final rows = await db.database.query(
        "SELECT code,mid FROM( SELECT code,name, COUNT(0) AS pcount,MAX(id) AS mid FROM patients WHERE 1=1 AND isValid=1 GROUP BY code HAVING pcount>1);");
    if (rows.isEmpty) return;

    for (var row in rows) {
      try {
        final code = row['code'];
        final maxId = row['mid'];

        /// 把旧的记录置为无效
        await db.database.execute(
            "UPDATE patients SET isValid=0, code='${code}_del' WHERE code='$code' AND isValid=1 AND id!=$maxId;");
      } catch (e) {
        logger.e("DatabasePatchV2 cleanDuplicatePatient error,", e);
      }
    }
  }

  /// 计算并更新居民的未上传健康检测数量
  Future<void> _calcAndUpdateDiagnosisCount() async {
    const querySql = "SELECT p.code, COUNT(d.code) AS dcount FROM patients p "
        "LEFT JOIN diagnosis d ON d.patientCode = p.code AND d.isValid = 1 AND d.syncState!=1 "
        "WHERE 1=1 AND p.isValid=1 GROUP BY p.code; ";
    const updateSql = "CREATE TEMPORARY TABLE temp_counts AS $querySql"
        "UPDATE patients SET diagnosisCount =( SELECT dcount FROM temp_counts WHERE code = patients.code) WHERE EXISTS ( SELECT 1 FROM temp_counts WHERE code = patients.code ); "
        "DROP TABLE temp_counts;";
    await db.database.execute(updateSql);
  }

  /// 更新居民新字段OverallSync的值
  Future<void> _updateOverallSyncState() async {
    // 先同步居民档案状态-》整体同步状态
    await db.database
        .execute("UPDATE patients SET overallSyncState = syncState WHERE 1=1;");
    // 有未上传检测数据的，整体同步状态更新为未上传
    await db.database.execute(
        "UPDATE patients SET overallSyncState = 0 WHERE 1=1 AND isValid=1 AND diagnosisCount >0;");
  }
}