/**
 * 组长看板数据云函数 - 支持时间范围筛选
 *
 * 请求参数:
 * - 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 定义 ---

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

    // 项目查询 - 添加时间范围筛选
    // 筛选逻辑：项目的 deadline 或 createdAt 在指定时间范围内
    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",
        (
          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" != '已完成'
        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 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" != '已完成'
            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 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"
    `;

    // 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, spaceStatsResult, issuesResult] = await Promise.all([
      Psql.query(workloadSql, [companyId]),
      Psql.query(projectsSql, queryParams),
      Psql.query(spaceStatsSql, queryParams),
      Psql.query(issuesSql, queryParams)
    ]);

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

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

      // 从 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: p.designerName || '待分配',
        designerIds: p.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 });
  }
}