yss-project/CLOUD_FUNCTION_FINAL.js

async function handler(request, response) {
  console.log('🚀 执行高性能 SQL 统计 (完全体 - 最终修正版)...');
  
  try {
    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;

    // --- 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
    `;

    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",
        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
    `;

    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"
    `;

    // 关键修正：通过 Project 表关联查询，获取更多字段
    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
      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 p."company" = $1 
        AND (i."isDeleted" IS NULL OR i."isDeleted" = false)
        AND i."status" IN ('待处理', '处理中')
      ORDER BY i."updatedAt" DESC
      LIMIT 50
    `;

    // --- 执行 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])
    ]);

    // --- 格式化数据 ---

    // 1. Workload
    const workload = workloadResult.map(w => {
      const capacity = w.weeklyCapacity || 3;
      const load = parseFloat(w.weightedLoad || 0);
      const loadRate = Math.round((load / capacity) * 100);
      let status = 'idle';
      if (loadRate > 80) status = 'overload';
      else if (loadRate > 50) status = 'busy';
      
      return {
        id: w.id,
        name: w.name,
        weeklyCapacity: capacity,
        projectCount: parseInt(w.projectCount),
        overdueCount: parseInt(w.overdueCount),
        weightedLoad: load,
        loadRate,
        status
      };
    });

    // 2. Projects
    const spaceAssigneeMap = {};
    const projects = projectsResult.map(p => {
      if (p.projectDate && p.projectDate.designerAssignmentStats) {
          const stats = p.projectDate.designerAssignmentStats;
          if (stats.projectLeader && stats.projectLeader.assignedSpaces) {
              stats.projectLeader.assignedSpaces.forEach(s => { if (s.id) spaceAssigneeMap[s.id] = stats.projectLeader.name; });
          }
          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; });
                  }
              });
          }
          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';
      const days = parseFloat(p.daysLeft);
      if (days < 0) statusStr = 'overdue';
      else if (days <= 3) statusStr = 'urgent';
      
      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(days), 
        isOverdue: days < 0,
        statusStr,
        designerName: p.designerName || '待分配',
        designerIds: p.designerIds || []
      };
    });

    // 3. Space Stats (完全修复聚合逻辑)
    const spaceStats = {};
    
    // 创建项目名称映射，确保能获取到 projectName
    const projectNameMap = {};
    projects.forEach(p => {
      projectNameMap[p.id] = p.name;
    });
    
    spaceStatsResult.forEach(row => {
        if (!spaceStats[row.projectId]) {
            spaceStats[row.projectId] = {
                spaces: []
            };
        }
        
        // 计算单个空间的完成度
        const hasFiles = parseInt(row.totalFiles) > 0;
        let completion = 0;
        if (hasFiles) {
            if (parseInt(row.whiteModel) > 0) completion += 25;
            if (parseInt(row.softDecor) > 0) completion += 25;
            if (parseInt(row.rendering) > 0) completion += 25;
            if (parseInt(row.postProcess) > 0) completion += 25;
        }

        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: hasFiles,
            completionRate: Math.min(100, completion)
        };
        
        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;
        
        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] || '未分配',
                    spaceId: 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')
        };
        
        // 关键：确保 projectName 和 totalByType 存在
        spaceStats[pid] = {
            projectId: pid,
            projectName: projectNameMap[pid] || '未命名项目', 
            totalSpaces,
            spaces: proj.spaces,
            totalDeliverableFiles: proj.spaces.reduce((sum, s) => sum + s.totalFiles, 0),
            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)
            },
            overallCompletionRate,
            phaseProgress
        };
    });

    // 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,
      updatedAt: row.updatedAt,
      dueDate: row.dueDate,
      tags: row.data?.tags || []
    }));

    // 5. Stats
    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, issues } 
    });

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