/**
 * Test Cleanup Utilities
 * 
 * 测试数据清理工具
 * 
 * ⚠️ 安全机制：只允许在测试环境执行
 * 
 * v2.2.0: 新增测试会话ID隔离机制
 * - 每次测试运行生成唯一的会话ID
 * - 所有测试数据都标记会话ID
 * - 清理时只删除指定会话的数据
 * - 支持多测试并行运行
 * 
 * 基于文档: docs/modules/organization/09-test-scenarios.md - 测试数据清理
 */

import { PrismaClient } from '@prisma/client';
import { checkAndAbortIfFatal } from './db-fail-fast';

const prisma = new PrismaClient();

async function cleanupMeetingAttendanceDataForUsers(
  prismaInstance: PrismaClient,
  userIds: string[],
) {
  if (userIds.length === 0) {
    return;
  }

  const meetings = await prismaInstance.meeting.findMany({
    where: { creatorId: { in: userIds } },
    select: { id: true },
  });
  const meetingIds = meetings.map((item) => item.id);

  const series = await prismaInstance.meetingSeries.findMany({
    where: { creatorId: { in: userIds } },
    select: { id: true },
  });
  const meetingSeriesIds = series.map((item) => item.id);

  const templates = await prismaInstance.meetingTemplate.findMany({
    where: { creatorId: { in: userIds } },
    select: { id: true },
  });
  const templateIds = templates.map((item) => item.id);

  const directBindingIds = (await prismaInstance.outlookMeetingBinding.findMany({
    where: {
      OR: [
        { ownerUserId: { in: userIds } },
        { meetingId: { in: meetingIds } },
        { meetingSeriesId: { in: meetingSeriesIds } },
      ],
    },
    select: { id: true },
  })).map((item) => item.id);

  await prismaInstance.outlookEventSyncDiff.deleteMany({
    where: {
      OR: [
        { bindingId: { in: directBindingIds } },
      ],
    },
  }).catch(() => undefined);
  await prismaInstance.outlookEventSourceVersion.deleteMany({
    where: {
      OR: [
        { bindingId: { in: directBindingIds } },
      ],
    },
  }).catch(() => undefined);
  await prismaInstance.outlookSyncEventLog.deleteMany({
    where: {
      OR: [
        { bindingId: { in: directBindingIds } },
      ],
    },
  }).catch(() => undefined);
  await prismaInstance.outlookSeriesOccurrenceExclusion.deleteMany({
    where: { bindingId: { in: directBindingIds } },
  }).catch(() => undefined);
  await prismaInstance.outlookMeetingBinding.deleteMany({
    where: { id: { in: directBindingIds } },
  }).catch(() => undefined);

  await prismaInstance.meetingAttendanceAuditLog.deleteMany({
    where: { userId: { in: userIds } },
  }).catch(() => undefined);
  await prismaInstance.meetingAttendanceLeaveRecord.deleteMany({
    where: { userId: { in: userIds } },
  }).catch(() => undefined);
  await prismaInstance.meetingAttendance.deleteMany({
    where: { userId: { in: userIds } },
  }).catch(() => undefined);
  await prismaInstance.meetingRequiredAttendee.deleteMany({
    where: { userId: { in: userIds } },
  }).catch(() => undefined);
  await prismaInstance.meetingTemplateAttendee.deleteMany({
    where: { userId: { in: userIds } },
  }).catch(() => undefined);

  // v1.0 议程能力：删除 user 引用的附件 / 上传任务 / 议程项 / 议程段
  // 这些表 createdBy / uploadedBy / assigneeUserId / assignedById 是 onDelete:Restrict，
  // 若 user 在别人会议下留有附件/任务，必须先清掉才能删 user
  await prismaInstance.meetingAgendaItemUploadTask.deleteMany({
    where: {
      OR: [
        { assigneeUserId: { in: userIds } },
        { assignedById: { in: userIds } },
        { createdById: { in: userIds } },
      ],
    },
  }).catch((err) => console.warn('cleanup meetingAgendaItemUploadTask failed:', err?.message));
  await prismaInstance.meetingAgendaItemAttachment.deleteMany({
    where: {
      OR: [
        { uploadedById: { in: userIds } },
        { createdById: { in: userIds } },
      ],
    },
  }).catch((err) => console.warn('cleanup meetingAgendaItemAttachment failed:', err?.message));
  await prismaInstance.meetingAttachment.deleteMany({
    where: {
      OR: [
        { uploadedById: { in: userIds } },
        { createdById: { in: userIds } },
      ],
    },
  }).catch((err) => console.warn('cleanup meetingAttachment failed:', err?.message));
  // 议程项 / 议程段（按 presenter / createdBy 找出来的也要清）
  await prismaInstance.meetingAgendaItem.deleteMany({
    where: {
      OR: [
        { presenterUserId: { in: userIds } },
        { createdById: { in: userIds } },
      ],
    },
  }).catch((err) => console.warn('cleanup meetingAgendaItem failed:', err?.message));
  await prismaInstance.meetingAgendaSection.deleteMany({
    where: { createdById: { in: userIds } },
  }).catch((err) => console.warn('cleanup meetingAgendaSection failed:', err?.message));

  if (meetingIds.length > 0) {
    await prismaInstance.meetingAttendance.deleteMany({
      where: { meetingId: { in: meetingIds } },
    }).catch(() => undefined);
    await prismaInstance.meetingRequiredAttendee.deleteMany({
      where: { meetingId: { in: meetingIds } },
    }).catch(() => undefined);
    await prismaInstance.meetingExternalAttendee.deleteMany({
      where: { meetingId: { in: meetingIds } },
    }).catch(() => undefined);
    await prismaInstance.meeting.deleteMany({
      where: { id: { in: meetingIds } },
    }).catch(() => undefined);
  }

  if (meetingSeriesIds.length > 0) {
    await prismaInstance.meetingSeries.deleteMany({
      where: { id: { in: meetingSeriesIds } },
    }).catch(() => undefined);
  }

  if (templateIds.length > 0) {
    await prismaInstance.meetingTemplateAttendee.deleteMany({
      where: { templateId: { in: templateIds } },
    }).catch(() => undefined);
    await prismaInstance.meetingTemplate.deleteMany({
      where: { id: { in: templateIds } },
    }).catch(() => undefined);
  }
}

async function cleanupMeetingAttendanceDataAll(prismaInstance: PrismaClient) {
  await prismaInstance.outlookEventSyncDiff.deleteMany({}).catch(() => undefined);
  await prismaInstance.outlookEventSourceVersion.deleteMany({}).catch(() => undefined);
  await prismaInstance.outlookSyncEventLog.deleteMany({}).catch(() => undefined);
  await prismaInstance.outlookSeriesOccurrenceExclusion.deleteMany({}).catch(() => undefined);
  await prismaInstance.outlookMeetingBinding.deleteMany({}).catch(() => undefined);
  await prismaInstance.outlookEventSnapshot.deleteMany({}).catch(() => undefined);
  await prismaInstance.outlookSyncCursor.deleteMany({}).catch(() => undefined);
  await prismaInstance.outlookSubscription.deleteMany({}).catch(() => undefined);
  await prismaInstance.outlookSyncMailbox.deleteMany({}).catch(() => undefined);

  await prismaInstance.meetingAttendanceAuditLog.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingAttendanceDataAccessLog.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingAttendanceLeaveRecord.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingAttendance.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingRequiredAttendee.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingExternalAttendee.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingTemplateAttendee.deleteMany({}).catch(() => undefined);
  // v1.0 议程能力：按依赖顺序清理（叶子 → 根）
  await prismaInstance.meetingAgendaItemUploadTask.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingAgendaItemAttachment.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingAttachment.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingAgendaItem.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingAgendaSection.deleteMany({}).catch(() => undefined);
  await prismaInstance.meeting.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingSeries.deleteMany({}).catch(() => undefined);
  await prismaInstance.meetingTemplate.deleteMany({}).catch(() => undefined);
}

async function cleanupAuditDataForUsers(
  prismaInstance: PrismaClient,
  userIds: string[],
) {
  if (userIds.length === 0) {
    return;
  }

  const auditLogIds = (await prismaInstance.auditLog.findMany({
    where: { userId: { in: userIds } },
    select: { id: true },
  })).map((item) => item.id);

  if (auditLogIds.length > 0) {
    await prismaInstance.auditDatabaseChangeLog.deleteMany({
      where: { auditLogId: { in: auditLogIds } },
    }).catch(() => undefined);
    await prismaInstance.auditSensitiveOperationLog.deleteMany({
      where: { auditLogId: { in: auditLogIds } },
    }).catch(() => undefined);
  }

  await prismaInstance.auditLog.deleteMany({
    where: { userId: { in: userIds } },
  }).catch(() => undefined);
}

/**
 * 当前测试会话ID
 * 每次测试运行时会被设置为唯一值
 */
let currentTestSessionId: string | null = null;

/**
 * 生成测试会话ID
 * 
 * @returns 固定的测试会话ID（用于测试数据隔离）
 * 
 * @example
 * ```typescript
 * const sessionId = generateTestSessionId(); // test_1234567890000_test
 * ```
 */
export function generateTestSessionId(): string {
  // 使用固定的时间戳，方便调试和数据追踪
  // Jest配置为顺序运行(maxWorkers: 1)，所以无需区分不同测试文件
  const fixedTimestamp = '1234567890000';
  const fixedRandom = 'test';
  return `test_${fixedTimestamp}_${fixedRandom}`;
}

/**
 * 设置当前测试会话ID
 * 
 * @param sessionId - 会话ID
 */
export function setTestSessionId(sessionId: string): void {
  currentTestSessionId = sessionId;
  console.log(`🔖 测试会话ID: ${sessionId}`);
}

/**
 * 获取当前测试会话ID
 * 
 * @returns 当前会话ID，如果未设置则自动生成
 */
export function getTestSessionId(): string {
  if (!currentTestSessionId) {
    currentTestSessionId = generateTestSessionId();
    console.log(`🔖 自动生成测试会话ID: ${currentTestSessionId}`);
  }
  return currentTestSessionId;
}

/**
 * 获取测试会话时间戳
 * 
 * 用于创建测试数据时生成唯一标识
 * 所有使用此时间戳的数据都会在测试结束后被清理
 * 
 * @param withRandom - 是否添加随机后缀（默认false，使用固定时间戳）
 * @returns 会话时间戳（固定值或带随机后缀）
 * 
 * @example
 * ```typescript
 * const ts = getTestTimestamp();         // 1234567890000 (固定时间戳)
 * const ts2 = getTestTimestamp(true);    // 1234567890000_abc (带随机后缀，避免冲突)
 * 
 * // 使用
 * const code = `FF-CN-${ts}`;     // FF-CN-1234567890000
 * const code2 = `FF-US-${ts}`;    // FF-US-1234567890000 (可能冲突，需手动处理)
 * ```
 */
export function getTestTimestamp(withRandom: boolean = false): string {
  const sessionId = getTestSessionId();
  const timestampMatch = sessionId.match(/test_(\d+)_/);
  const timestamp = timestampMatch ? timestampMatch[1] : '1234567890000'; // 使用固定时间戳
  
  if (withRandom) {
    // 添加随机后缀，避免同一测试中创建多个数据时冲突
    const random = Math.random().toString(36).substring(2, 6);
    return `${timestamp}_${random}`;
  }
  
  return timestamp;
}

/**
 * 清除测试会话ID
 */
export function clearTestSessionId(): void {
  currentTestSessionId = null;
}

/**
 * 判断是否应该清理数据
 * 
 * 简化策略（基于 NODE_ENV）:
 * - production: ❌ 禁止清理（抛出错误）
 * - test: ✅ 自动清理
 * - 其他（development等）: ⚠️ 跳过清理（保留数据供调试）
 * 
 * @returns boolean - 是否应该执行清理
 */
function shouldCleanup(): boolean {
  const nodeEnv = process.env.NODE_ENV;
  const allowCleanup = process.env.ALLOW_TEST_DB_CLEANUP;
  
  // 1. 生产环境：严禁清理
  if (nodeEnv === 'production') {
    throw new Error(
      `❌ 禁止在生产环境清理数据!\n` +
      `   NODE_ENV=${nodeEnv}\n` +
      `   请确保使用正确的环境变量`
    );
  }
  
  // 2. 测试环境：自动清理
  if (nodeEnv === 'test') {
    if (allowCleanup !== 'true') {
      throw new Error(
        `❌ 测试环境未显式允许清理!\n` +
        `   ALLOW_TEST_DB_CLEANUP=${allowCleanup || '未设置'}\n` +
        `   请通过测试专用脚本运行，或显式设置 ALLOW_TEST_DB_CLEANUP=true`
      );
    }
    console.log(`✅ 测试环境: 自动清理数据 (NODE_ENV=test)`);
    return true;
  }
  
  // 3. 其他环境（development等）：跳过清理
  console.log(`⚠️  开发环境: 跳过清理，保留数据 (NODE_ENV=${nodeEnv || '未设置'})`);
  return false;
}

function assertSafeTestDatabase(): void {
  const databaseUrl = process.env.DATABASE_URL || '';

  if (!databaseUrl) {
    throw new Error('❌ 未找到 DATABASE_URL，无法确认是否为测试数据库');
  }

  let dbName = '';
  try {
    const parsed = new URL(databaseUrl);
    dbName = parsed.pathname.replace(/^\//, '');
  } catch {
    const match = databaseUrl.match(/\/([^/?]+)(\?|$)/);
    dbName = match?.[1] || '';
  }

  const safeMarkers = ['test', 'integration'];
  const normalized = `${databaseUrl} ${dbName}`.toLowerCase();
  const isSafe = safeMarkers.some((marker) => normalized.includes(marker));

  if (!isSafe) {
    throw new Error(
      `❌ 当前数据库不是测试库，禁止执行清理!\n` +
      `   DATABASE_URL=${databaseUrl}\n` +
      `   解析出的数据库名=${dbName || '未知'}\n` +
      `   请使用 TEST_DATABASE_URL / 测试专用脚本`
    );
  }
}

/**
 * 默认参与前缀清理的 schema 列表（与 backend/prisma/schema/*.prisma 对齐）
 * 不含 base（Prisma datasource 元信息）和 schema 文档（FORM_*.md）
 */
const DEFAULT_PREFIX_SCHEMAS = [
  'corp_approval',
  'corp_hr',
  'corp_request',
  'mfg_inventory',
  'platform_ai',
  'platform_audit',
  'platform_automation',
  'platform_devtracker',
  'platform_feedback',
  'platform_form',
  'platform_iam',
  'platform_knowledge',
  'platform_logging',
  'platform_meeting_attendance',
  'platform_notify',
  'platform_performance',
  'platform_site_attendance',
  'platform_tickets',
  'robot_manager',
];

/**
 * 默认匹配前缀的列名候选
 * 测试 fixture 命名约定下，识别字段一般是 code/name/username/email/slug 之一
 */
const DEFAULT_PREFIX_COLUMNS = ['code', 'name', 'username', 'email', 'slug'];

/**
 * 按前缀过滤 cleanup（schema 无关，零维护负担）
 *
 * 用法（issue #165、testing-standards.md §1.95）：
 *   1. 测试 fixture 命名带前缀（默认 t_，建议 `t_${Date.now()}_xxx`）
 *   2. afterEach/afterAll 调 `cleanupByPrefix(prisma)`
 *   3. schema 加新表无需改 helper（自动从 information_schema 发现）
 *
 * 工作机制：
 *   1. SET session_replication_role = 'replica' 临时禁用 FK 约束
 *   2. 扫指定 schemas 下的 information_schema.columns，找出含 code/name/.. 等
 *      候选列的表
 *   3. 对每张表 DELETE WHERE col LIKE 'prefix%'
 *   4. SET session_replication_role = 'origin' 恢复 FK
 *
 * 注意事项：
 *   - join 表（无 prefix-able 列）不会被本函数清。FK 禁用期间 DELETE 父表后
 *     join 表会留 dangling 行——大多数测试在 force-reset 时整张 schema 重建，
 *     dangling 行不会累积到下一次 force-reset 之后；模块迁移时若仍需精确清
 *     join 表，在 module 自己的 cleanup hook 里加。
 *   - schemas/columns 可覆盖：模块测试可传更窄的范围加速。
 *   - 安全：schema/table/column 名来自 information_schema 查询结果，无 SQL 注入。
 *   - 仍受 shouldCleanup() / assertSafeTestDatabase() 双重保护。
 *
 * @example
 *   const ts = getTestTimestamp();
 *   await prisma.organization.create({ data: { code: `t_${ts}_org`, name: ... } });
 *   // ... 测试 ...
 *   await cleanupByPrefix(prisma);  // 默认 prefix='t_'，扫所有已知 schema
 *
 * @example
 *   // 模块测试只清自己的 schema 加速
 *   await cleanupByPrefix(prisma, { schemas: ['platform_meeting_attendance', 'platform_iam'] });
 */
export async function cleanupByPrefix(
  prismaInstance: PrismaClient,
  options: {
    prefix?: string;
    schemas?: string[];
    columns?: string[];
  } = {},
): Promise<{ tableDeletes: Record<string, number>; skippedSchemas: string[] }> {
  if (!shouldCleanup()) {
    return { tableDeletes: {}, skippedSchemas: [] };
  }
  assertSafeTestDatabase();

  const prefix = options.prefix ?? 't_';
  const schemas = options.schemas ?? DEFAULT_PREFIX_SCHEMAS;
  const columns = options.columns ?? DEFAULT_PREFIX_COLUMNS;

  const tableDeletes: Record<string, number> = {};
  const skippedSchemas: string[] = [];

  await prismaInstance
    .$executeRawUnsafe(`SET session_replication_role = 'replica'`)
    .catch((err) => console.warn(`cleanupByPrefix: 禁用 FK 失败:`, err));

  try {
    for (const schema of schemas) {
      // 扫该 schema 下含候选列的表 — 信息来自 information_schema，可信
      const rows = await prismaInstance.$queryRawUnsafe<
        Array<{ table_name: string; column_name: string }>
      >(
        // 必须过滤 data_type 只取字符串类（character varying / text / character / citext），
        // 否则同名 boolean / int 列做 LIKE 会报 'operator does not exist: boolean ~~ text'。
        `SELECT table_name, column_name
         FROM information_schema.columns
         WHERE table_schema = $1
           AND column_name = ANY($2::text[])
           AND data_type IN ('character varying', 'text', 'character', 'citext')`,
        schema,
        columns,
      ).catch((err) => {
        console.warn(`cleanupByPrefix: schema='${schema}' 元数据查询失败:`, err);
        return [];
      });

      if (rows.length === 0) {
        skippedSchemas.push(schema);
        continue;
      }

      for (const { table_name, column_name } of rows) {
        // schema/table/column 来自 information_schema 查询结果，安全
        const sql = `DELETE FROM "${schema}"."${table_name}" WHERE "${column_name}" LIKE $1`;
        const deleted = await prismaInstance
          .$executeRawUnsafe(sql, `${prefix}%`)
          .catch((err) => {
            console.warn(`cleanupByPrefix: ${schema}.${table_name}.${column_name} DELETE 失败:`, err);
            return 0;
          });
        if (deleted > 0) {
          tableDeletes[`${schema}.${table_name}.${column_name}`] = deleted;
        }
      }
    }
  } finally {
    await prismaInstance
      .$executeRawUnsafe(`SET session_replication_role = 'origin'`)
      .catch((err) => console.warn(`cleanupByPrefix: 恢复 FK 失败:`, err));
  }

  const totalDeleted = Object.values(tableDeletes).reduce((a, b) => a + b, 0);
  console.log(
    `🧹 cleanupByPrefix(prefix='${prefix}'): ${totalDeleted} 行删除，${Object.keys(tableDeletes).length} 张表命中` +
      (skippedSchemas.length > 0 ? `，跳过 ${skippedSchemas.length} 个无候选列的 schema` : ''),
  );

  return { tableDeletes, skippedSchemas };
}

/**
 * 清理所有测试数据（按依赖顺序）
 *
 * 自动判断:
 * - NODE_ENV=test: 执行清理
 * - 其他环境: 跳过清理
 */
export async function cleanupAllTestData() {
  if (!shouldCleanup()) return;
  
  // 1. 清理关联表
  await prisma.userRole.deleteMany({});
  await prisma.rolePermission.deleteMany({});
  await prisma.userDepartment.deleteMany({});
  await prisma.organizationRegion.deleteMany({});

  // 2. 清理主表
  await prisma.user.deleteMany({});
  await prisma.department.deleteMany({});
  await prisma.role.deleteMany({});
  await prisma.permission.deleteMany({});
  await prisma.position.deleteMany({});
  await prisma.organization.deleteMany({});
  await prisma.region.deleteMany({});
}

/**
 * 清理组织相关数据
 */
export async function cleanupOrganizationData() {
  if (!shouldCleanup()) return;
  
  await prisma.userDepartment.deleteMany({});
  await prisma.organizationRegion.deleteMany({});
  await prisma.department.deleteMany({});
  await prisma.organization.deleteMany({});
}

/**
 * 清理用户相关数据
 */
export async function cleanupUserData() {
  if (!shouldCleanup()) return;
  
  await prisma.userRole.deleteMany({});
  await prisma.userDepartment.deleteMany({});
  await prisma.user.deleteMany({});
}

/**
 * 清理权限相关数据
 */
export async function cleanupPermissionData() {
  if (!shouldCleanup()) return;
  
  await prisma.userRole.deleteMany({});
  await prisma.rolePermission.deleteMany({});
  await prisma.role.deleteMany({});
  await prisma.permission.deleteMany({});
}

/**
 * 在每个测试前执行清理（推荐）
 */
export async function beforeEachTest() {
  await cleanupAllTestData();
}

/**
 * 在每个测试后执行清理（可选）
 */
export async function afterEachTest() {
  // 根据需要可以选择性清理
}

/**
 * 在所有测试完成后断开数据库连接
 */
export async function afterAllTests() {
  await prisma.$disconnect();
}

/**
 * 清理数据库（集成测试使用）
 * 
 * v2.2.0: 自动根据环境判断是否清理
 * 
 * 清理策略:
 * - NODE_ENV=test: 自动清理
 * - NODE_ENV=development: 跳过清理（保留数据供调试）
 * - NODE_ENV=production: 禁止清理（抛出错误）
 * 
 * 清理模式:
 * 1. **会话隔离模式**（推荐）: 传入sessionId，只清理该会话数据
 * 2. **全量清理模式**: 不传sessionId，清理所有数据
 * 
 * @param prismaInstance - Prisma 实例
 * @param sessionId - 可选的测试会话ID，只清理该会话的数据
 * 
 * @example
 * ```typescript
 * // 方式1: 会话隔离（推荐）
 * const sessionId = generateTestSessionId();
 * setTestSessionId(sessionId);
 * // ... 运行测试 ...
 * await cleanupDatabase(prisma, sessionId); // 只清理该会话数据
 * 
 * // 方式2: 全量清理
 * await cleanupDatabase(prisma); // 清理所有数据
 * ```
 */
export async function cleanupDatabase(
  prismaInstance: PrismaClient,
  sessionId?: string
) {
  // 自动判断是否清理（基于 NODE_ENV）
  if (!shouldCleanup()) {
    return;
  }

  assertSafeTestDatabase();
  
  // 如果有会话ID，使用会话隔离清理
  if (sessionId) {
    await cleanupBySessionId(prismaInstance, sessionId);
    return;
  }
  
  // 否则，全量清理
  await cleanupAllData(prismaInstance);
}

/**
 * 按会话ID清理数据（推荐方式）
 * 
 * 策略: 使用命名约定识别测试数据
 * - 提取会话ID中的时间戳进行匹配
 * - 测试数据标识字段包含时间戳（Date.now()生成）
 * 
 * 工作原理:
 * 1. 会话ID: test_1703680000000_abc123
 * 2. 提取时间戳: 1703680000000
 * 3. 匹配包含该时间戳的所有数据
 * 
 * 匹配示例:
 * - admin_1703680000000_xyz ✓
 * - FF-CN-1703680000000 ✓
 * - user_1703680000000 ✓
 * - multiorg_user_1703680123456 ✗ (不同时间戳，不会被删除)
 * 
 * @param prismaInstance - Prisma 实例
 * @param sessionId - 测试会话ID (如: test_1703680000000_abc123)
 */
async function cleanupBySessionId(
  prismaInstance: PrismaClient,
  sessionId: string
) {
  try {
    console.log(`🧹 清理测试会话数据: ${sessionId}`);
    
    // 提取时间戳部分（作为清理标识）
    const timestampMatch = sessionId.match(/test_(\d+)_/);
    const timestamp = timestampMatch ? timestampMatch[1] : sessionId;
    
    console.log(`   策略: 匹配包含时间戳 ${timestamp} 的数据`);
    
    // ⚠️ 重要：按依赖顺序清理，避免外键约束错误
    
    // 阶段 1: 查找要删除的实体ID（用于清理关联表）
    const usersToDelete = await prismaInstance.user.findMany({
      where: {
        OR: [
          { username: { contains: timestamp } },
          { email: { contains: timestamp } },
        ],
      },
      select: { id: true },
    });
    const userIds = usersToDelete.map(u => u.id);
    
    const rolesToDelete = await prismaInstance.role.findMany({
      where: {
        isBuiltIn: false,
        OR: [
          { code: { contains: timestamp } },
          { name: { contains: timestamp } },
        ],
      },
      select: { id: true },
    });
    const roleIds = rolesToDelete.map(r => r.id);
    
    const orgsToDelete = await prismaInstance.organization.findMany({
      where: {
        OR: [
          { code: { contains: timestamp } },
          { name: { contains: timestamp } },
        ],
      },
      select: { id: true },
    });
    const orgIds = orgsToDelete.map(o => o.id);
    
    const deptsToDelete = await prismaInstance.department.findMany({
      where: {
        OR: [
          { code: { contains: timestamp } },
          { name: { contains: timestamp } },
        ],
      },
      select: { id: true },
    });
    const deptIds = deptsToDelete.map(d => d.id);
    
    // 阶段 2: 删除关联表（按外键依赖顺序）
    let deletedUserRoles = { count: 0 };
    let deletedRolePermissions = { count: 0 };
    let deletedUserDepts = { count: 0 };
    let deletedOrgRegions = { count: 0 };
    
    if (userIds.length > 0 || roleIds.length > 0 || orgIds.length > 0) {
      await cleanupMeetingAttendanceDataForUsers(prismaInstance, userIds);
      await cleanupAuditDataForUsers(prismaInstance, userIds);

      // UserRole: 关联 User, Role, Organization
      deletedUserRoles = await prismaInstance.userRole.deleteMany({
        where: {
          OR: [
            { userId: { in: userIds } },
            { roleId: { in: roleIds } },
            { organizationId: { in: orgIds } },
          ],
        },
      });
      
      // RolePermission: 关联 Role
      deletedRolePermissions = await prismaInstance.rolePermission.deleteMany({
        where: { roleId: { in: roleIds } },
      });
      
      // UserDepartment: 关联 User, Department
      deletedUserDepts = await prismaInstance.userDepartment.deleteMany({
        where: {
          OR: [
            { userId: { in: userIds } },
            { departmentId: { in: deptIds } },
          ],
        },
      });
      
      // OrganizationRegion: 关联 Organization
      deletedOrgRegions = await prismaInstance.organizationRegion.deleteMany({
        where: { organizationId: { in: orgIds } },
      });
    }
    
    // 阶段 3: 删除主表（无依赖顺序要求）
    const deletedUsers = await prismaInstance.user.deleteMany({
      where: { id: { in: userIds } },
    });

    const deletedOrgs = await prismaInstance.organization.deleteMany({
      where: { id: { in: orgIds } },
    });

    const deletedDepts = await prismaInstance.department.deleteMany({
      where: { id: { in: deptIds } },
    });

    const deletedRoles = await prismaInstance.role.deleteMany({
      where: { id: { in: roleIds } },
    });

    const deletedPositions = await prismaInstance.position.deleteMany({
      where: {
        OR: [
          { code: { contains: timestamp } },
          { name: { contains: timestamp } },
        ],
      },
    });

    const deletedRegions = await prismaInstance.region.deleteMany({
      where: {
        OR: [
          { code: { contains: timestamp } },
          { name: { contains: timestamp } },
        ],
      },
    });

    console.log(`✅ 会话数据清理完成:`);
    console.log(`   关联表:`);
    console.log(`   - UserRole: ${deletedUserRoles.count}`);
    console.log(`   - RolePermission: ${deletedRolePermissions.count}`);
    console.log(`   - UserDepartment: ${deletedUserDepts.count}`);
    console.log(`   - OrganizationRegion: ${deletedOrgRegions.count}`);
    console.log(`   主表:`);
    console.log(`   - 用户: ${deletedUsers.count}`);
    console.log(`   - 组织: ${deletedOrgs.count}`);
    console.log(`   - 部门: ${deletedDepts.count}`);
    console.log(`   - 角色: ${deletedRoles.count}`);
    console.log(`   - 岗位: ${deletedPositions.count}`);
    console.log(`   - 区域: ${deletedRegions.count}`);
  } catch (error) {
    console.error('❌ 会话数据清理失败:', error);
    throw error;
  }
}

/**
 * 全量清理测试数据（保护种子数据）
 *
 * ⚠️ 只清理测试创建的数据，不删除种子数据：
 * - 保留 iam-seed 创建的角色、权限、内置用户
 * - 保留 org-seed 创建的组织、部门、测试用户
 * - 保留 360 模板、等级配置等种子数据
 * - 只清理 performance schema 中的业务数据（周期、KPI、评估等）
 *
 * @param prismaInstance - Prisma 实例
 */
async function cleanupAllData(prismaInstance: PrismaClient) {
  try {
    // --- Organization 模块清理（精确删除测试数据，保留种子数据）---
    // 种子数据标识：is_built_in=true、固定 code（如 Administrator、itadmin、FF_DEFAULT）
    // 测试数据标识：名称/code 包含时间戳数字串
    console.log('🧹 清理 organization 模块测试数据（保留种子）...');

    // 临时禁用 FK 约束（users 表有 50+ FK 引用，逐表清理不现实）
    await prismaInstance.$executeRawUnsafe(`SET session_replication_role = 'replica'`).catch(() => {});

    const exec = async (sql: string) => {
      try { await prismaInstance.$executeRawUnsafe(sql); }
      catch (e: any) {
        // DB 被 admin shutdown / 连接断开 → 后续 cleanup/test 全是噪音，立刻 abort 整轮
        checkAndAbortIfFatal(e, 'cleanupAllData exec');
        console.warn(`⚠️ cleanup SQL failed: ${sql.substring(0, 60)}... → ${e.message?.substring(0, 80)}`);
      }
    };

    // 1. 关联表
    await exec(`DELETE FROM corp_hr.user_departments`);
    await exec(`DELETE FROM platform_iam.user_role_rel`);
    await exec(`DELETE FROM corp_hr.organization_regions`);
    await exec(`DELETE FROM platform_iam.workflow_role_user_rel`);

    // AI 工具授权关联表：FK 关闭期间 cascade 不触发，必须显式删，
    // 否则会留下孤儿 grant（指向已删 role/user），后续查询时 Prisma strict
    // relation 校验抛 "Field role is required, got null"
    await exec(`DELETE FROM platform_iam.ai_tool_grants WHERE role_id IN (SELECT id FROM platform_iam.roles WHERE is_built_in = false)`);
    await exec(`DELETE FROM platform_iam.ai_tool_grants_user WHERE user_id IN (SELECT id FROM platform_iam.users WHERE username NOT IN ('itadmin') AND username NOT LIKE 'demo.perf%')`);

    // 2. 测试创建的用户（保留种子用户）
    await exec(`DELETE FROM platform_iam.users WHERE username NOT IN ('itadmin') AND username NOT LIKE 'demo.perf%'`);

    // 3. 测试创建的角色（保留内置角色）
    await exec(`DELETE FROM platform_iam.roles WHERE is_built_in = false`);

    // 4. 测试创建的流程角色（保留种子）
    await exec(`DELETE FROM platform_iam.workflow_roles WHERE code NOT IN ('APPLICANT','DIRECT_MANAGER','DEPARTMENT_HEAD','SKIP_LEVEL_MANAGER','HR_APPROVER','FINANCE_APPROVER','CEO')`);

    // 5. 组织、部门、岗位、区域（种子里没有，全删）
    await exec(`DELETE FROM corp_hr.departments`);
    await exec(`DELETE FROM corp_hr.organizations`);
    await exec(`DELETE FROM corp_hr.positions WHERE code NOT IN ('CEO','CTO','CFO','COO','VP','DIR','MGR','LEAD','SR_ENG','ENG','JR_ENG','SPEC','ASST','INTERN')`);
    await exec(`DELETE FROM corp_hr.regions`);

    // 恢复 FK 约束
    await prismaInstance.$executeRawUnsafe(`SET session_replication_role = 'origin'`).catch(() => {});

    console.log('✅ Organization 模块测试数据清理完成（种子数据已保留）');

    // --- Performance 模块清理 ---
    console.log('🧹 清理 performance 模块测试数据（保护种子数据）...');

    // 策略：只删除测试创建的数据，保留模块种子数据（performance-demo-seed.sql）。
    // 测试创建的周期名称包含时间戳（如 "1234567890000"），种子周期不包含。
    // 通过关联周期 ID 级联识别：先找到测试创建的周期，再删除依赖这些周期的数据。

    // 1. 找到测试创建的周期 ID（名称包含时间戳数字串的）
    const testCycleCondition = `name ~ '[0-9]{10}' OR name LIKE '%Test%' OR name LIKE '%test%'`;

    // 2. 按 FK 依赖顺序清理关联数据（仅关联测试周期的）
    // 有 cycle_id 外键的表：通过 cycle_id IN (测试周期) 过滤
    const cycleLinkedTables = [
      'evaluation_response',   // 通过 evaluation_task → evaluation_360 → cycle
      'evaluation_task',       // 通过 evaluation_360 → cycle
      'evaluation_360',
      'grade_adjustment_log',  // 通过 performance_result → cycle
      'kpi_assessment',        // 通过 kpi_assignment → cycle
      'kpi_dependency',        // 通过 kpi_assignment → cycle
      'kpi_assignment',
      'performance_result',
    ];

    // 评估响应和任务需要通过评估 360 级联
    await prismaInstance.$executeRawUnsafe(`
      DELETE FROM platform_performance.evaluation_response
      WHERE task_id IN (
        SELECT id FROM platform_performance.evaluation_task
        WHERE evaluation_id IN (
          SELECT id FROM platform_performance.evaluation_360
          WHERE cycle_id IN (SELECT id FROM platform_performance.performance_cycle WHERE ${testCycleCondition})
        )
      )
    `).catch(() => {});

    await prismaInstance.$executeRawUnsafe(`
      DELETE FROM platform_performance.evaluation_task
      WHERE evaluation_id IN (
        SELECT id FROM platform_performance.evaluation_360
        WHERE cycle_id IN (SELECT id FROM platform_performance.performance_cycle WHERE ${testCycleCondition})
      )
    `).catch(() => {});

    await prismaInstance.$executeRawUnsafe(`
      DELETE FROM platform_performance.evaluation_360
      WHERE cycle_id IN (SELECT id FROM platform_performance.performance_cycle WHERE ${testCycleCondition})
    `).catch(() => {});

    // 校准调整日志（通过 performance_result → cycle 级联）
    await prismaInstance.$executeRawUnsafe(`
      DELETE FROM platform_performance.grade_adjustment_log
      WHERE result_id IN (
        SELECT id FROM platform_performance.performance_result
        WHERE cycle_id IN (SELECT id FROM platform_performance.performance_cycle WHERE ${testCycleCondition})
      )
    `).catch(() => {});

    // KPI 相关
    await prismaInstance.$executeRawUnsafe(`
      DELETE FROM platform_performance.kpi_assessment
      WHERE assignment_id IN (
        SELECT id FROM platform_performance.kpi_assignment
        WHERE cycle_id IN (SELECT id FROM platform_performance.performance_cycle WHERE ${testCycleCondition})
      )
    `).catch(() => {});

    await prismaInstance.$executeRawUnsafe(`
      DELETE FROM platform_performance.kpi_dependency
      WHERE source_assignment_id IN (
        SELECT id FROM platform_performance.kpi_assignment
        WHERE cycle_id IN (SELECT id FROM platform_performance.performance_cycle WHERE ${testCycleCondition})
      )
    `).catch(() => {});

    await prismaInstance.$executeRawUnsafe(`
      DELETE FROM platform_performance.kpi_assignment
      WHERE cycle_id IN (SELECT id FROM platform_performance.performance_cycle WHERE ${testCycleCondition})
    `).catch(() => {});

    // 结果
    await prismaInstance.$executeRawUnsafe(`
      DELETE FROM platform_performance.performance_result
      WHERE cycle_id IN (SELECT id FROM platform_performance.performance_cycle WHERE ${testCycleCondition})
    `).catch(() => {});

    // 战略目标（无 cycle_id，用名称匹配）
    await prismaInstance.$executeRawUnsafe("DELETE FROM platform_performance.strategic_objective_assignment WHERE objective_id IN (SELECT id FROM platform_performance.strategic_objective WHERE name ~ '[0-9]{10}' OR name LIKE '%Test%')").catch(() => {});
    await prismaInstance.$executeRawUnsafe("DELETE FROM platform_performance.strategic_objective WHERE name ~ '[0-9]{10}' OR name LIKE '%Test%'").catch(() => {});

    // 最后删除测试周期本身
    await prismaInstance.$executeRawUnsafe(`
      DELETE FROM platform_performance.performance_cycle WHERE ${testCycleCondition}
    `).catch(() => {});

    // 清理测试创建的模板和配置（保留种子的）
    await prismaInstance.$executeRawUnsafe("DELETE FROM platform_performance.evaluation_360_template WHERE name ~ '[0-9]{10}' OR name LIKE '%Test%'").catch(() => {});
    await prismaInstance.$executeRawUnsafe("DELETE FROM platform_performance.grade_config WHERE name ~ '[0-9]{10}' OR name LIKE '%分布配置%' OR name LIKE '%更新后的%' OR name LIKE '%等级配置A%' OR name LIKE '%Test%'").catch(() => {});

    // 不清理种子数据:
    // - platform_iam.users, roles, permissions
    // - corp_hr.organizations, departments
    // - performance_cycle（种子创建的，名称如 "FF Vehicle R&D Q2目标设定"）
    // - 关联的 kpi_assignment, kpi_assessment, evaluation_360, performance_result 等

    console.log('✅ Performance 模块测试数据清理完成（种子数据已保留）');
  } catch (error) {
    console.error('❌ 数据清理失败:', error);
    throw error;
  }
}