/**
 * 组长看板数据云函数 - 支持时间范围筛选
 *
 * 请求参数:
 * - companyId: 公司ID
 * - baseDate: 基准日期 (可选，格式: YYYY-MM-DD，默认为当前日期)
 * - startDate: 开始日期 (可选，覆盖 baseDate 计算)
 * - endDate: 结束日期 (可选，覆盖 baseDate 计算)
 *
 * 时间范围逻辑:
 * - 如果不传时间参数，默认查询当前进行中的项目
 * - 如果传 baseDate，则以该日期为基准，查询该月的项目
 * - 如果传 startDate/endDate，则使用指定的日期范围
 */

async function handler(request, response) {
  console.log('🚀 执行高性能 SQL 统计 (时间范围筛选版)...');

  try {
    // 获取公司ID
    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;

    // 获取时间参数
    let baseDate = request.body?.baseDate || request.params?.baseDate || null;
    let startDate = request.body?.startDate || request.params?.startDate || null;
    let endDate = request.body?.endDate || request.params?.endDate || null;

    // 计算时间范围
    const now = new Date();
    let rangeStart, rangeEnd;

    if (startDate && endDate) {
      // 使用指定范围
      rangeStart = new Date(startDate);
      rangeEnd = new Date(endDate);
    } else if (baseDate) {
      // 基于 baseDate 计算该月的范围
      const base = new Date(baseDate);
      rangeStart = new Date(base.getFullYear(), base.getMonth(), 1); // 月初
      rangeEnd = new Date(base.getFullYear(), base.getMonth() + 1, 0, 23, 59, 59); // 月末
    } else {
      // 默认：查询进行中的项目（不限制时间范围，或使用较宽的时间窗口）
      rangeStart = new Date(now.getFullYear() - 1, 0, 1); // 一年前
      rangeEnd = new Date(now.getFullYear() + 1, 11, 31); // 一年后
    }

    console.log(`📅 查询时间范围: ${rangeStart.toISOString()} ~ ${rangeEnd.toISOString()}`);

    // --- SQL 定义 (性能优化版) ---

    // 1. Workload 查询 - 保持不变，已经够高效
    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. 项目查询 - 优化：分离子查询，先查项目基础信息
    const projectsSql = `
      SELECT
        p."objectId" as "id",
        p."title" as "name",
        p."status",
        p."currentStage",
        p."deadline",
        p."updatedAt",
        p."createdAt",
        p."data",
        p."date" as "projectDate",
        EXTRACT(DAY FROM (p."deadline" - NOW())) as "daysLeft"
      FROM "Project" p
      WHERE p."company" = $1
        AND p."isDeleted" IS NOT TRUE
        AND p."status" != '已完成'
        AND (
          p."deadline" >= $2 AND p."deadline" <= $3
          OR p."createdAt" >= $2 AND p."createdAt" <= $3
          OR p."createdAt" < $2 AND p."deadline" > $3
          OR p."deadline" IS NULL
        )
      ORDER BY p."updatedAt" DESC
      LIMIT 500
    `;

    // 2.1 项目团队成员查询 - 一次性获取所有相关项目的团队成员
    const projectTeamsSql = `
      SELECT
        pt."project" as "projectId",
        string_agg(pr."name", ', ' ORDER BY pr."name") as "designerName",
        array_agg(pt."profile") as "designerIds"
      FROM "ProjectTeam" pt
      JOIN "Profile" pr ON pt."profile" = pr."objectId"
      WHERE pt."isDeleted" IS NOT TRUE
        AND pt."project" IN (
          SELECT p."objectId"
          FROM "Project" p
          WHERE p."company" = $1
            AND p."isDeleted" IS NOT TRUE
            AND p."status" != '已完成'
            AND (
              p."deadline" >= $2 AND p."deadline" <= $3
              OR p."createdAt" >= $2 AND p."createdAt" <= $3
              OR p."createdAt" < $2 AND p."deadline" > $3
              OR p."deadline" IS NULL
            )
        )
      GROUP BY pt."project"
    `;

    // 3. Space Stats - 优化：简化查询结构
    const spaceStatsSql = `
      WITH ActiveProjectIds AS (
          SELECT p."objectId"
          FROM "Project" p
          WHERE p."company" = $1
            AND p."isDeleted" IS NOT TRUE
            AND p."status" != '已完成'
            AND (
              p."deadline" >= $2 AND p."deadline" <= $3
              OR p."createdAt" >= $2 AND p."createdAt" <= $3
              OR p."createdAt" < $2 AND p."deadline" > $3
              OR p."deadline" IS NULL
            )
          LIMIT 500
      ),
      ProjectSpaces AS (
          SELECT
              prod."objectId" as "spaceId",
              prod."productName" as "spaceName",
              prod."productType" as "spaceType",
              prod."project" as "projectId"
          FROM "Product" prod
          WHERE prod."project" IN (SELECT "objectId" FROM ActiveProjectIds)
            AND (prod."isDeleted" IS NULL OR prod."isDeleted" = false)
      ),
      Deliverables AS (
          SELECT
              COALESCE(pf."data"->>'spaceId', pf."data"->>'productId') as "spaceId",
              COUNT(*) as "fileCount",
              SUM(CASE WHEN pf."fileType" = 'delivery_white_model'
                  OR pf."data"->>'deliveryType' IN ('white_model', 'delivery_white_model')
                  THEN 1 ELSE 0 END) as "whiteModelCount",
              SUM(CASE WHEN pf."fileType" = 'delivery_soft_decor'
                  OR pf."data"->>'deliveryType' IN ('soft_decor', 'delivery_soft_decor')
                  THEN 1 ELSE 0 END) as "softDecorCount",
              SUM(CASE WHEN pf."fileType" = 'delivery_rendering'
                  OR pf."data"->>'deliveryType' IN ('rendering', 'delivery_rendering')
                  THEN 1 ELSE 0 END) as "renderingCount",
              SUM(CASE WHEN pf."fileType" = 'delivery_post_process'
                  OR pf."data"->>'deliveryType' IN ('post_process', 'delivery_post_process')
                  THEN 1 ELSE 0 END) as "postProcessCount"
          FROM "ProjectFile" pf
          WHERE pf."project" IN (SELECT "objectId" FROM ActiveProjectIds)
            AND (pf."isDeleted" IS NULL OR pf."isDeleted" = false)
            AND (pf."fileType" LIKE 'delivery_%' OR pf."data"->>'uploadStage' = 'delivery')
          GROUP BY COALESCE(pf."data"->>'spaceId', pf."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"
    `;

    // Issue 查询 - 添加时间范围筛选
    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 ('待处理', '处理中')
        AND (
          i."createdAt" >= $2 OR i."dueDate" >= $2 OR i."dueDate" IS NULL
        )
      ORDER BY i."updatedAt" DESC
      LIMIT 50
    `;

    // --- 执行 SQL (并行优化) ---
    const queryParams = [companyId, rangeStart, rangeEnd];

    const [workloadResult, projectsResult, projectTeamsResult, spaceStatsResult, issuesResult] = await Promise.all([
      Psql.query(workloadSql, [companyId]),
      Psql.query(projectsSql, queryParams),
      Psql.query(projectTeamsSql, queryParams),
      Psql.query(spaceStatsSql, queryParams),
      Psql.query(issuesSql, queryParams)
    ]);

    // 构建项目团队成员映射
    const projectTeamsMap = {};
    projectTeamsResult.forEach(row => {
      projectTeamsMap[row.projectId] = {
        designerName: row.designerName || '待分配',
        designerIds: row.designerIds || []
      };
    });

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

    // 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 => {
      // 从映射中获取设计师信息
      const teamInfo = projectTeamsMap[p.id] || { designerName: '待分配', designerIds: [] };

      // 解析设计师分配信息
      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';

      // 从 data 字段提取扩展属性
      const data = p.data || {};

      return {
        id: p.id,
        name: p.name,
        status: p.status,
        currentStage: p.currentStage,
        deadline: p.deadline,
        updatedAt: p.updatedAt,
        createdAt: p.createdAt,
        urgency: data.urgency,
        type: data.projectType,
        phaseDeadlines: data.phaseDeadlines || {},
        daysLeft: Math.ceil(days),
        isOverdue: days < 0,
        statusStr,
        designerName: teamInfo.designerName,
        designerIds: teamInfo.designerIds,
        // 扩展字段
        data: data
      };
    });

    // 3. Space Stats (完全修复聚合逻辑)
    const spaceStats = {};

    // 创建项目名称映射
    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')
        };

        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
    };

    // 6. 返回时间范围信息
    const timeRange = {
      start: rangeStart.toISOString(),
      end: rangeEnd.toISOString(),
      baseDate: baseDate || now.toISOString().split('T')[0]
    };

    response.json({
      code: 200,
      success: true,
      data: {
        stats,
        workload,
        projects,
        spaceStats,
        issues,
        timeRange // 添加时间范围信息
      }
    });

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