/**
 * robot-manager 模块 — 数据质量校验专属检查
 *
 * 通用检查（权限种子 / FK / 枚举）由主脚本自动执行；此处补充本模块特有规则。
 */

export interface QualityCheck {
  name: string;
  fn: (
    query: (sql: string) => string,
    queryRows: (sql: string) => string[],
  ) => { status: 'pass' | 'fail' | 'warn'; detail: string };
}

export const checks: QualityCheck[] = [
  {
    name: 'RobotStatus 枚举含 10 个值（含 SOLD / REPAIRED）',
    fn: (_query, queryRows) => {
      const rows = queryRows(
        "SELECT enumlabel FROM pg_enum e JOIN pg_type t ON t.oid = e.enumtypid WHERE t.typname = 'RobotStatus' ORDER BY e.enumsortorder",
      );
      const labels = rows.map((r) => r.trim());
      const required = [
        'ORDERED', 'IN_TRANSIT', 'BONDED', 'IN_STOCK', 'RESERVED',
        'SOLD', 'DELIVERED', 'REPAIR', 'REPAIRED', 'CANCELLED',
      ];
      const missing = required.filter((s) => !labels.includes(s));
      if (missing.length > 0) return { status: 'fail', detail: `缺少枚举值: ${missing.join(', ')}` };
      if (labels.length !== required.length)
        return { status: 'warn', detail: `意外枚举值数量 ${labels.length}（预期 ${required.length}）: ${labels.join(', ')}` };
      return { status: 'pass', detail: `10 个状态枚举值全部存在` };
    },
  },
  {
    name: 'RobotFieldDef 复合唯一 (scope, key) 已建立',
    fn: (_query, queryRows) => {
      const rows = queryRows(
        "SELECT indexname FROM pg_indexes WHERE schemaname='robot_manager' AND tablename='robot_field_defs' AND indexdef ILIKE '%(scope%key)%'",
      );
      if (rows.length === 0)
        return { status: 'fail', detail: '未找到 (scope, key) 复合唯一索引' };
      return { status: 'pass', detail: `索引: ${rows.join(', ')}` };
    },
  },
  {
    name: 'RobotOption 表已移除（v5）',
    fn: (_query, queryRows) => {
      const rows = queryRows(
        "SELECT table_name FROM information_schema.tables WHERE table_schema='robot_manager' AND table_name='robot_options'",
      );
      if (rows.length > 0)
        return { status: 'fail', detail: 'robot_options 表仍然存在（v5 应该已删除）' };
      return { status: 'pass', detail: 'robot_options 已移除，字典已统一到 FieldDef' };
    },
  },
  {
    name: 'scope=unit 字段全部带 group',
    fn: (_query, queryRows) => {
      const rows = queryRows(
        "SELECT key FROM robot_manager.robot_field_defs WHERE scope='unit' AND (\"group\" IS NULL OR \"group\" = '')",
      );
      if (rows.length > 0)
        return { status: 'fail', detail: `缺 group: ${rows.join(', ')}` };
      return { status: 'pass', detail: '所有 unit 字段均有 group' };
    },
  },
  {
    name: 'scope=service_record / location 系统字典存在',
    fn: (_query, queryRows) => {
      const rows = queryRows(
        "SELECT scope || ':' || key FROM robot_manager.robot_field_defs WHERE scope IN ('service_record','location') ORDER BY scope, key",
      );
      const set = new Set(rows.map((r) => r.trim()));
      const required = ['service_record:serviceType', 'location:locationType'];
      const missing = required.filter((r) => !set.has(r));
      if (missing.length > 0)
        return { status: 'fail', detail: `缺系统字典: ${missing.join(', ')}` };
      return { status: 'pass', detail: `系统字典齐全: ${rows.join(', ')}` };
    },
  },
  {
    name: 'select 类型字段 options 非空',
    fn: (_query, queryRows) => {
      const rows = queryRows(
        "SELECT key FROM robot_manager.robot_field_defs WHERE type='select' AND (options IS NULL OR jsonb_array_length(options) = 0)",
      );
      if (rows.length > 0)
        return { status: 'fail', detail: `select 字段 options 为空: ${rows.join(', ')}` };
      return { status: 'pass', detail: '所有 select 字段均有 options' };
    },
  },
  {
    name: 'RobotUnit 无孤儿 modelId / skuId 引用',
    fn: (_query, queryRows) => {
      const missingModel = queryRows(
        "SELECT u.id FROM robot_manager.robot_units u LEFT JOIN robot_manager.robot_models m ON m.id = u.model_id WHERE m.id IS NULL AND u.deleted_at IS NULL",
      );
      const missingSku = queryRows(
        "SELECT u.id FROM robot_manager.robot_units u LEFT JOIN robot_manager.robot_skus s ON s.id = u.sku_id WHERE s.id IS NULL AND u.deleted_at IS NULL",
      );
      if (missingModel.length > 0 || missingSku.length > 0)
        return {
          status: 'fail',
          detail: `孤儿 modelId: ${missingModel.length}, 孤儿 skuId: ${missingSku.length}`,
        };
      return { status: 'pass', detail: 'RobotUnit 外键引用全部有效' };
    },
  },
  {
    name: '5 角色 × seed 声明权限 = 运行时权限（防盲区 #6 seed drift）',
    fn: (_query, queryRows) => {
      const expected: Record<string, number> = {
        Administrator: 18,
        RobotManagerRLE: 18,
        RobotManagerSupplyChain: 9,
        RobotManagerSales: 6,
        RobotManagerFinance: 3,
      };
      const issues: string[] = [];
      for (const [roleCode, expectedCount] of Object.entries(expected)) {
        const rows = queryRows(
          `SELECT p.action FROM platform_iam.role_permission_rel rp
             JOIN platform_iam.permissions p ON p.id = rp.permission_id
             JOIN platform_iam.roles r ON r.id = rp.role_id
             WHERE r.code = '${roleCode}' AND p.resource = 'robot-manager'`,
        );
        const actual = rows.length;
        if (actual !== expectedCount) {
          issues.push(`${roleCode}: 预期 ${expectedCount}, 实际 ${actual}`);
        }
      }
      if (issues.length > 0) return { status: 'fail', detail: issues.join('; ') };
      return { status: 'pass', detail: `5 角色权限数一致` };
    },
  },
];