yss-project/CLOUD_FUNCTION_MIGRATION_GUIDE.md

组长看板性能优化：云函数迁移指南 (终极修复版)

更新说明

• 修复数据冗余：只查询 phaseDeadlines，不再返回巨大的 data 对象。
• 修复前端报错：补充了 phaseProgress 计算逻辑，确保前端进度条能正常显示。

---

云函数代码实现 (Node.js)

请将以下代码完整覆盖到云函数 (ID: 8qJkylemKn) 中：

async function handler(request, response) {
  console.log('🚀 执行高性能 SQL 统计 (含空间交付物 V2)...');
  
  try {
    // 1. 获取 companyId
    let companyId = 'cDL6R1hgSi';
    if (request.company && request.company.id) companyId = request.company.id;
    else if (request.params && request.params.companyId) companyId = request.params.companyId;
    else if (request.body && request.body.companyId) companyId = request.body.companyId;

    // 2.1 定义 Workload SQL
    const workloadSql = `
      SELECT
        u."objectId" as "id",
        u."name",
        COALESCE((u."data"->'tags'->'capacity'->>'weeklyProjects')::int, 3) as "weeklyCapacity",
        COUNT(DISTINCT pt."project") as "projectCount",
        COUNT(DISTINCT CASE WHEN p."deadline" < NOW() AND p."status" != '已完成' THEN p."objectId" END) as "overdueCount",
        SUM(CASE 
          WHEN p."status" = '已完成' THEN 0 
          ELSE ((CASE WHEN p."data"->>'projectType' = 'hard' THEN 2.0 ELSE 1.0 END) * (CASE WHEN p."deadline" < NOW() THEN 1.5 ELSE 1.0 END)) 
        END) as "weightedLoad"
      FROM "Profile" u
      LEFT JOIN "ProjectTeam" pt ON pt."profile" = u."objectId" AND pt."isDeleted" IS NOT TRUE
      LEFT JOIN "Project" p ON pt."project" = p."objectId" AND p."isDeleted" IS NOT TRUE AND p."status" != '已完成'
      WHERE u."company" = $1 AND u."roleName" = '组员' AND u."isDeleted" IS NOT TRUE
      GROUP BY u."objectId", u."name", u."data"
      ORDER BY "weightedLoad" DESC
    `;

    // 2.2 定义 Projects SQL (✅ 优化：获取 phaseDeadlines 和 date 用于计算负责人)
    const projectsSql = `
      SELECT
        p."objectId" as "id",
        p."title" as "name",
        p."status",
        p."currentStage",
        p."deadline",
        p."updatedAt",
        p."createdAt",
        p."data"->>'urgency' as "urgency",
        p."data"->>'projectType' as "type",
        p."data"->'phaseDeadlines' as "phaseDeadlines",
        p."date" as "projectDate", -- ✅ 获取 date 字段以解析 designerAssignmentStats
        EXTRACT(DAY FROM (p."deadline" - NOW())) as "daysLeft",
        (
          SELECT string_agg(pr."name", ', ')
          FROM "ProjectTeam" pt
          JOIN "Profile" pr ON pt."profile" = pr."objectId"
          WHERE pt."project" = p."objectId" AND pt."isDeleted" IS NOT TRUE
        ) as "designerName",
        (
          SELECT array_agg(pt."profile")
          FROM "ProjectTeam" pt
          WHERE pt."project" = p."objectId" AND pt."isDeleted" IS NOT TRUE
        ) as "designerIds"
      FROM "Project" p
      WHERE p."company" = $1 AND p."isDeleted" IS NOT TRUE AND p."status" != '已完成'
      ORDER BY p."updatedAt" DESC
      LIMIT 1000
    `;

    // 2.3 定义 Space Stats SQL
    const spaceStatsSql = `
      WITH ActiveProjects AS (
          SELECT p."objectId" 
          FROM "Project" p 
          WHERE p."company" = $1 AND p."isDeleted" IS NOT TRUE AND p."status" != '已完成'
          LIMIT 1000
      ),
      ProjectSpaces AS (
          SELECT 
              p."objectId" as "spaceId",
              p."productName" as "spaceName",
              p."productType" as "spaceType",
              p."project" as "projectId"
          FROM "Product" p
          WHERE p."project" IN (SELECT "objectId" FROM ActiveProjects)
            AND (p."isDeleted" IS NULL OR p."isDeleted" = false)
      ),
      Deliverables AS (
          SELECT 
              COALESCE(d."data"->>'spaceId', d."data"->>'productId') as "spaceId",
              COUNT(*) as "fileCount",
              SUM(CASE WHEN 
                  d."fileType" = 'delivery_white_model' OR 
                  d."data"->>'deliveryType' IN ('white_model', 'delivery_white_model') 
                  THEN 1 ELSE 0 END) as "whiteModelCount",
              SUM(CASE WHEN 
                  d."fileType" = 'delivery_soft_decor' OR 
                  d."data"->>'deliveryType' IN ('soft_decor', 'delivery_soft_decor') 
                  THEN 1 ELSE 0 END) as "softDecorCount",
              SUM(CASE WHEN 
                  d."fileType" = 'delivery_rendering' OR 
                  d."data"->>'deliveryType' IN ('rendering', 'delivery_rendering') 
                  THEN 1 ELSE 0 END) as "renderingCount",
              SUM(CASE WHEN 
                  d."fileType" = 'delivery_post_process' OR 
                  d."data"->>'deliveryType' IN ('post_process', 'delivery_post_process') 
                  THEN 1 ELSE 0 END) as "postProcessCount"
          FROM "ProjectFile" d
          WHERE d."project" IN (SELECT "objectId" FROM ActiveProjects)
            AND (d."isDeleted" IS NULL OR d."isDeleted" = false)
            AND (
                d."fileType" LIKE 'delivery_%' OR 
                d."data"->>'uploadStage' = 'delivery'
            )
          GROUP BY COALESCE(d."data"->>'spaceId', d."data"->>'productId')
      )
      SELECT 
          ps."projectId",
          ps."spaceId",
          ps."spaceName",
          ps."spaceType",
          COALESCE(d."fileCount", 0) as "totalFiles",
          COALESCE(d."whiteModelCount", 0) as "whiteModel",
          COALESCE(d."softDecorCount", 0) as "softDecor",
          COALESCE(d."renderingCount", 0) as "rendering",
          COALESCE(d."postProcessCount", 0) as "postProcess"
      FROM ProjectSpaces ps
      LEFT JOIN Deliverables d ON ps."spaceId" = d."spaceId"
    `;

    // 2.4 定义 Issues SQL (🆕 新增：查询待办事项)
    const issuesSql = `
      SELECT
        i."objectId" as "id",
        i."title",
        i."description",
        i."priority",
        i."issueType",
        i."status",
        i."dueDate",
        i."createdAt",
        i."updatedAt",
        i."data",
        p."objectId" as "projectId",
        p."title" as "projectName",
        c."name" as "creatorName",
        a."name" as "assigneeName"
      FROM "ProjectIssue" i
      LEFT JOIN "Project" p ON i."project" = p."objectId"
      LEFT JOIN "Profile" c ON i."creator" = c."objectId"
      LEFT JOIN "Profile" a ON i."assignee" = a."objectId"
      WHERE i."company" = $1 
        AND (i."isDeleted" IS NULL OR i."isDeleted" = false)
        AND i."status" IN ('待处理', '处理中')
      ORDER BY i."updatedAt" DESC
      LIMIT 50
    `;

    // 3. 并行执行 SQL
    const [workloadResult, projectsResult, spaceStatsResult, issuesResult] = await Promise.all([
      Psql.query(workloadSql, [companyId]),
      Psql.query(projectsSql, [companyId]),
      Psql.query(spaceStatsSql, [companyId]),
      Psql.query(issuesSql, [companyId])
    ]);

    console.log(`✅ SQL 完成: ${workloadResult.length} 人, ${projectsResult.length} 项目, ${spaceStatsResult.length} 空间记录, ${issuesResult.length} 待办`);

    // ... (4.1 和 4.2 保持不变)
    
    // 4.4 格式化 Issues
    const zh2enStatus = (status) => {
      const map = {
        '待处理': 'open',
        '处理中': 'in_progress',
        '已解决': 'resolved',
        '已关闭': 'closed'
      };
      return map[status] || 'open';
    };

    const issues = issuesResult.map(row => ({
      id: row.id,
      title: row.title || (row.description ? row.description.slice(0, 40) : '未命名问题'),
      description: row.description,
      priority: row.priority || 'medium',
      type: row.issueType || 'task',
      status: zh2enStatus(row.status),
      projectId: row.projectId || '',
      projectName: row.projectName || '未知项目',
      relatedSpace: row.data?.relatedSpace,
      relatedStage: row.data?.relatedStage,
      assigneeName: row.assigneeName || '未指派',
      creatorName: row.creatorName || '未知',
      createdAt: row.createdAt,
    }));

    // 4.2 格式化 Projects & 构建负责人映射
    const spaceAssigneeMap = {}; // spaceId -> designerName

    const projects = projectsResult.map(p => {
      // ... (前面的格式化逻辑)
      
      // 解析 designerAssignmentStats 构建空间负责人映射
      // ✅ 解析 designerAssignmentStats 构建空间负责人映射
      if (p.projectDate && p.projectDate.designerAssignmentStats) {
          const stats = p.projectDate.designerAssignmentStats;
          
          // 1. 项目负责人
          if (stats.projectLeader && stats.projectLeader.assignedSpaces) {
              stats.projectLeader.assignedSpaces.forEach(s => {
                  if (s.id) spaceAssigneeMap[s.id] = stats.projectLeader.name;
              });
          }
          
          // 2. 团队成员
          if (Array.isArray(stats.teamMembers)) {
              stats.teamMembers.forEach(member => {
                  if (member.assignedSpaces && member.name) {
                      member.assignedSpaces.forEach(s => {
                          if (s.id) spaceAssigneeMap[s.id] = member.name;
                      });
                  }
              });
          }
          
          // 3. 跨组协作
          if (Array.isArray(stats.crossTeamCollaborators)) {
              stats.crossTeamCollaborators.forEach(member => {
                  if (member.assignedSpaces && member.name) {
                      member.assignedSpaces.forEach(s => {
                          if (s.id) spaceAssigneeMap[s.id] = member.name;
                      });
                  }
              });
          }
      }

      let statusStr = 'normal';
      // ... (保持原有的 return 逻辑)
      return {
          // ...
          id: p.id,
          name: p.name,
          status: p.status,
          currentStage: p.currentStage,
          deadline: p.deadline,
          updatedAt: p.updatedAt,
          createdAt: p.createdAt,
          urgency: p.urgency,
          type: p.type,
          phaseDeadlines: p.phaseDeadlines || {},
          daysLeft: Math.ceil(parseFloat(p.daysLeft)),
          isOverdue: parseFloat(p.daysLeft) < 0,
          statusStr,
          designerName: p.designerName || '待分配',
          designerIds: p.designerIds || []
      };
    });

    // 4.3 聚合 Space Stats (✅ 增加 phaseProgress 计算)
    const spaceStats = {};
    
    // 创建 projectId 到 projectName 的映射
    const projectNameMap = {};
    projects.forEach(p => {
      projectNameMap[p.id] = p.name;
    });
    
    spaceStatsResult.forEach(row => {
        if (!spaceStats[row.projectId]) {
            spaceStats[row.projectId] = {
                spaces: []
            };
        }
        
        const spaceInfo = {
            spaceId: row.spaceId,
            spaceName: row.spaceName,
            spaceType: row.spaceType,
            totalFiles: parseInt(row.totalFiles),
            deliverableTypes: {
                whiteModel: parseInt(row.whiteModel),
                softDecor: parseInt(row.softDecor),
                rendering: parseInt(row.rendering),
                postProcess: parseInt(row.postProcess)
            },
            hasDeliverables: parseInt(row.totalFiles) > 0,
            completionRate: parseInt(row.totalFiles) > 0 ? 
                Math.min(100, (
                    (parseInt(row.whiteModel) > 0 ? 25 : 0) +
                    (parseInt(row.softDecor) > 0 ? 25 : 0) +
                    (parseInt(row.rendering) > 0 ? 25 : 0) +
                    (parseInt(row.postProcess) > 0 ? 25 : 0)
                )) : 0
        };
        
        spaceStats[row.projectId].spaces.push(spaceInfo);
    });

    Object.keys(spaceStats).forEach(pid => {
        const proj = spaceStats[pid];
        const totalSpaces = proj.spaces.length;
        
        // 计算整体完成率
        const sumCompletion = proj.spaces.reduce((sum, s) => sum + s.completionRate, 0);
        const overallCompletionRate = totalSpaces > 0 ? Math.round(sumCompletion / totalSpaces) : 0;
        
        // ✅ 关键修复：计算各阶段详细进度 (M/S/R/P)
        const calcPhaseDetails = (typeKey) => {
            const spacesWithFile = proj.spaces.filter(s => s.deliverableTypes[typeKey] > 0);
            const completedCount = spacesWithFile.length;
            const rate = totalSpaces > 0 ? Math.round((completedCount / totalSpaces) * 100) : 0;
            const fileCount = proj.spaces.reduce((sum, s) => sum + s.deliverableTypes[typeKey], 0);
            
            // 获取未完成空间
            const incomplete = proj.spaces
                .filter(s => s.deliverableTypes[typeKey] === 0)
                .map(s => ({
                    spaceName: s.spaceName,
                    // ✅ 使用映射获取负责人，如果没有则显示未分配
                    assignee: spaceAssigneeMap[s.spaceId] || '未分配'
                }));

            return {
                completionRate: rate,
                completedSpaces: completedCount,
                requiredSpaces: totalSpaces,
                totalFiles: fileCount,
                incompleteSpaces: incomplete
            };
        };

        const phaseProgress = {
            modeling: calcPhaseDetails('whiteModel'),
            softDecor: calcPhaseDetails('softDecor'),
            rendering: calcPhaseDetails('rendering'),
            postProcessing: calcPhaseDetails('postProcess')
        };
        
        spaceStats[pid] = {
            projectId: pid,
            projectName: projectNameMap[pid] || '未命名项目', 
            totalSpaces,
            spaces: proj.spaces,
            totalDeliverableFiles: proj.spaces.reduce((sum, s) => sum + s.totalFiles, 0),
            totalByType: proj.totalByType,
            overallCompletionRate,
            phaseProgress // ✅ 现在包含完整详情
        };
        
        // 补充 totalByType 到 spaceStats[pid]
        spaceStats[pid].totalByType = {
            whiteModel: proj.spaces.reduce((sum, s) => sum + s.deliverableTypes.whiteModel, 0),
            softDecor: proj.spaces.reduce((sum, s) => sum + s.deliverableTypes.softDecor, 0),
            rendering: proj.spaces.reduce((sum, s) => sum + s.deliverableTypes.rendering, 0),
            postProcess: proj.spaces.reduce((sum, s) => sum + s.deliverableTypes.postProcess, 0)
        };
    });

    const stats = {
      totalActive: projects.length,
      overdueCount: projects.filter(p => p.isOverdue).length,
      urgentCount: projects.filter(p => p.statusStr === 'urgent').length,
      avgLoadRate: workload.length > 0 ? Math.round(workload.reduce((sum, w) => sum + w.loadRate, 0) / workload.length) : 0
    };

    response.json({ 
      code: 200, 
      success: true, 
      data: { stats, workload, projects, spaceStats } 
    });

  } catch (error) {
    console.error('❌ SQL 执行失败:', error.message);
    response.json({ code: 500, success: false, error: error.message });
  }
}