import { PipelineStage, Types } from 'mongoose';
import { LeadStage } from '@/modules/master/leadStage/leadStage.model';
import { Lead } from '../lead/lead.model';
import * as leadService from '@/modules/lead/lead.service';

import { Team } from '../teams/teams.model';
import { Tasks } from '../tasks/tasks.model';
import { TaskActivityType, TaskStatus } from '../tasks/tasks.constant';
import User from '../user/user.model';
import { Customer } from '../customer/customer.model';
import Project from '../project/project.model';
import Source from '../master/constructionStatus/source/source.model';
import { CaptureLead } from '../captureLeads/captureLeads.model';
import { UnitBookingOrHold } from '../activity/unitBookingOrHold/unitBookingOrHold.model';
import { Company } from '../company/company.model';
import IndividualProperties from '../individualProperties/individualProperties.model';
import {
  ActivityStats,
  CaptureLeadStages,
  ComparisonTime,
  emptyPaginateResult,
  LeadSourceConversionData,
  LeadSourceData,
  LeadSourcePerformanceData,
  LostLeadAnalysisResult,
  ProjectConversionData,
  ReasonDistribution,
  SalesComparisonResult,
  SalesConversionResponse,
  SalesSummaryReport,
  SalesAgingClosedDealItem,
  SalesAgingOpenDealItem,
  SalesAgingReportData,
  StageAtLossDistribution,
  StagePerformance,
  TeamActivityData,
  TeamActivityDetailsFilters,
  TeamActivityDetailsReportData,
  TeamActivityStatusByTypeItem,
  TeamMemberLite,
  TransactionTypeConversionData,
} from './reports.interface';
import { CustomerPayment } from '../customer/payment/payment.model';
import { ITeamPopulated } from '../teams/teams.interface';
import {
  CompanyType,
  LeadInterestType,
  UserType,
} from '@/shared/constants/enum.constant';
import { PaginateOptions } from '@/shared/utils/plugins/paginate/paginate';
import { getObjectId } from '@/shared/utils/commonHelper';
import {
  buildTeamActivityEnrichedPipeline,
  buildTeamActivityFacetStage,
  TeamActivityReportView,
} from './pipelines/teamActivityDetails.pipeline';
import {
  buildClosedSalesAgingPipeline,
  buildOpenSalesAgingPipeline,
  buildSalesAgingEntityMatchStages,
  ClosedLeadAggregateRow,
  OpenLeadAggregateRow,
} from './pipelines/salesAging.pipeline';
import {
  buildSalesSummaryAnalyticsPipeline,
  buildSalesSummaryBarChartPipeline,
  buildSalesSummaryBasePipeline,
  buildSalesSummaryDetailedListPipeline,
  buildSalesSummaryLineChartPipeline,
  buildSalesSummaryPieChartPipeline,
  SalesSummaryReportView,
} from './pipelines/salesSummary.pipeline';

const DAY_IN_MS = 1000 * 60 * 60 * 24;

const OPEN_AGING_BUCKETS = [
  '0-7 days',
  '8-15 days',
  '16-30 days',
  '31-60 days',
  '60+ days',
] as const;

const getTransactionTypeLabel = (interestType?: string): string => {
  const normalized = (interestType || '').toLowerCase();

  if (normalized === LeadInterestType.BUY) return 'Buy';
  if (normalized === LeadInterestType.SELL) return 'Sell';
  if (normalized === LeadInterestType.RENT) return 'Rent';
  if (normalized === LeadInterestType.LEASE) return 'Lease';

  return normalized
    ? normalized.charAt(0).toUpperCase() + normalized.slice(1)
    : 'Unknown';
};

const getAgingBucketLabel = (
  agingDays: number,
): (typeof OPEN_AGING_BUCKETS)[number] => {
  if (agingDays <= 7) return '0-7 days';
  if (agingDays <= 15) return '8-15 days';
  if (agingDays <= 30) return '16-30 days';
  if (agingDays <= 60) return '31-60 days';

  return '60+ days';
};

const getDayDifference = (fromDate: Date, toDate: Date) =>
  Math.max(0, Math.ceil((toDate.getTime() - fromDate.getTime()) / DAY_IN_MS));

const ACTIVITY_TYPE_LABELS: Record<string, string> = {
  [TaskActivityType.CALL]: 'Call',
  [TaskActivityType.MEETING]: 'Meeting',
  [TaskActivityType.SITE_VISIT]: 'Site Visit',
  [TaskActivityType.OTHER]: 'Other',
};

const TASK_STATUS_LABELS: Record<string, string> = {
  [TaskStatus.PENDING]: 'Pending',
  [TaskStatus.COMPLETED]: 'Completed',
  [TaskStatus.OVERDUE]: 'Overdue',
  [TaskStatus.CANCELLED]: 'Cancelled',
};

const parseObjectIds = (value?: string): Types.ObjectId[] => {
  if (!value) return [];

  return value
    .split(',')
    .map((id) => id.trim())
    .filter(Boolean)
    .filter((id) => Types.ObjectId.isValid(id))
    .map((id) => getObjectId(id));
};

const escapeRegex = (value: string) =>
  value.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');

const getSalesSummaryReportView = (view?: string): SalesSummaryReportView =>
  view === 'overview' || view === 'detail' ? view : 'all';

const toPositiveInteger = (value: unknown, fallback: number) => {
  const parsed = parseInt(String(value ?? ''), 10);
  return Number.isFinite(parsed) && parsed > 0 ? parsed : fallback;
};

const getEmptySalesSummaryDetailedList = (
  options: PaginateOptions = {},
) => ({
  results: [],
  page: toPositiveInteger(options.page, 1),
  limit: toPositiveInteger(options.limit, 10),
  totalPages: 0,
  totalResults: 0,
});

export const leadDistributionStage = async (company: Types.ObjectId, filter: any) => {
  // Date filters 
  const dateFilter: any = {};

  if (filter?.startDate) {
    const s = new Date(filter.startDate);
    if (!isNaN(s.getTime())) dateFilter.$gte = s;
  }

  if (filter?.endDate) {
    const e = new Date(filter.endDate);
    if (!isNaN(e.getTime())) dateFilter.$lte = e;
  }
  const hasDateFilter = Object.keys(dateFilter).length > 0;
  // Get all stages for company
  const stages = await LeadStage.find({ company, isActive: true })
    .sort({ position: 1 })
    .lean();

  if (!stages.length) return { stageData: [], statics: {} };

  // Aggregate lead data
  const leadAgg = await Lead.aggregate([
    { $match: { 
        company,
        ...(hasDateFilter && { createdAt: dateFilter }),
      } 
    },
    {
      $group: {
        _id: '$leadStage',
        count: { $sum: 1 },
        avgScore: { $avg: '$leadScore' },
      },
    },
  ]);

  // Map counts per stageId
  const stageStatsMap = leadAgg.reduce<
    Record<string, { count: number; avgScore: number }>
  >((acc, s) => {
    acc[s._id?.toString()] = {
      count: s.count,
      avgScore: Math.round(s.avgScore || 0),
    };
    return acc;
  }, {});

  // Total leads
  const totalLeads = leadAgg.reduce((sum, s) => sum + s.count, 0);

  // Calculate overall avg leadScore
  const totalScoreSum = leadAgg.reduce(
    (sum, s) => sum + (s.avgScore || 0) * s.count,
    0,
  );
  const avgLeadScore =
    totalLeads > 0 ? Math.round(totalScoreSum / totalLeads) : 0;

  // Build stageData table
  const stageData = stages.map((stage) => {
    const stats = stageStatsMap[stage._id.toString()] || {
      count: 0,
      avgScore: 0,
    };

    const percentage =
      totalLeads > 0
        ? ((stats.count / totalLeads) * 100).toFixed(1) + '%'
        : '0%';

    return {
      id: stage._id.toString(),
      stage: stage.stageName,
      count: stats.count,
      percentage,
      avgScore: stats.avgScore,
      color: stage.color,
    };
  });

  // Find bottleneck (stage with highest count)
  let bottleneck = { stage: null, count: 0 };
  if (stageData.length > 0) {
    const maxStage = stageData.reduce((prev, curr) =>
      curr.count > prev.count ? curr : prev,
    );
    bottleneck = { stage: maxStage.stage, count: maxStage.count };
  }

  // Calculate conversion rate = leads in "Lead Won" / totalLeads
  const wonStage = stages.find((s) => s.stageName.toLowerCase() === 'lead won');
  let conversionRate = '0%';
  if (wonStage) {
    const wonCount = stageStatsMap[wonStage._id.toString()]?.count || 0;
    conversionRate =
      totalLeads > 0 ? ((wonCount / totalLeads) * 100).toFixed(1) + '%' : '0%';
  }

  return {
    stageData,
    statics: {
      totalLeads,
      conversionRate,
      bottleneck,
      avgLeadScore,
    },
  };
};

export const lostLeadAnalysis = async (
  company: Types.ObjectId,
  filter: { leadStage: unknown },
  options: {},
): Promise<LostLeadAnalysisResult> => {
  // 1. Find the "Lead Lost" stage for the company
  const lostStage = await LeadStage.findOne({
    company,
    stageName: { $regex: /^lead lost$/i },
  });

  if (!lostStage)
    return {
      summary: {
        totalLostLeads: 0,
        averageLeadAge: 0,
        topLostReason: null,
      },
      reasonDistribution: [],
      stageAtLossDistribution: [],
      tableData: emptyPaginateResult,
    };

  filter.leadStage = lostStage._id;

  // 2. Build pipeline with correct PipelineStage typing
  const pipeline: PipelineStage[] = [
    { $match: { company, leadStage: lostStage._id } },

    {
      $addFields: {
        leadAgeDays: {
          $divide: [
            { $subtract: ['$updatedAt', '$createdAt'] },
            1000 * 60 * 60 * 24,
          ],
        },
      },
    },

    {
      $facet: {
        summary: [
          {
            $group: {
              _id: null,
              totalLostLeads: { $sum: 1 },
              averageLeadAge: { $avg: '$leadAgeDays' },
            },
          },
        ],

        reasonDistribution: [
          {
            $group: {
              _id: '$reason',
              leadsLost: { $sum: 1 },
            },
          },
          { $sort: { leadsLost: -1 } },
        ],

        stageAtLossDistribution: [
          {
            $group: {
              _id: '$previousLeadStage',
              leadsLost: { $sum: 1 },
            },
          },
        ],
      },
    },
  ];

  const result = await Lead.aggregate<{
    summary: { totalLostLeads: number; averageLeadAge: number }[];
    reasonDistribution: { _id: string; leadsLost: number }[];
    stageAtLossDistribution: { _id: Types.ObjectId; leadsLost: number }[];
  }>(pipeline);

  if (!result.length)
    return {
      summary: {
        totalLostLeads: 0,
        averageLeadAge: 0,
        topLostReason: null,
      },
      reasonDistribution: [],
      stageAtLossDistribution: [],
      tableData: emptyPaginateResult,
    };

  const { summary, reasonDistribution, stageAtLossDistribution } = result[0];

  const totalLostLeads = summary[0]?.totalLostLeads || 0;
  const averageLeadAge = summary[0]?.averageLeadAge || 0;
  const topLostReason = reasonDistribution?.[0]?._id || null;

  // Format reason distribution
  const reasonDistributionFormatted: ReasonDistribution[] =
    reasonDistribution.map((r) => ({
      reason: r._id || 'Unknown',
      leadsLost: r.leadsLost,
      percentageOfLost:
        totalLostLeads > 0
          ? Math.round((r.leadsLost / totalLostLeads) * 100)
          : 0,
    }));

  // Populate Stage names for previousLeadStage
  const previousStageIds = stageAtLossDistribution
    .map((s) => s._id)
    .filter(Boolean);

  const previousStages = await LeadStage.find({
    _id: { $in: previousStageIds },
  }).lean();

  const stageMap = previousStages.reduce<Record<string, string>>((acc, s) => {
    acc[s._id.toString()] = s.stageName;
    return acc;
  }, {});

  const stageAtLossDistributionFormatted: StageAtLossDistribution[] =
    stageAtLossDistribution.map((s) => ({
      stage: stageMap[s._id?.toString()] || 'Unknown',
      leadsLost: s.leadsLost,
      percentageOfLost:
        totalLostLeads > 0
          ? Math.round((s.leadsLost / totalLostLeads) * 100)
          : 0,
    }));

  const tableData = await leadService.queryLeads(filter, options);

  return {
    summary: {
      totalLostLeads,
      averageLeadAge: Number(averageLeadAge.toFixed(2)),
      topLostReason,
    },
    reasonDistribution: reasonDistributionFormatted,
    stageAtLossDistribution: stageAtLossDistributionFormatted,
    tableData,
  };
};

export const teamActivityPerformance = async (
  userId: Types.ObjectId,
  company: Types.ObjectId,
  filter: any,
): Promise<TeamActivityData[]> => {
  const dateFilter: any = {};
  if (filter?.startDate) {
    const s = new Date(filter.startDate);
    if (!isNaN(s.getTime())) dateFilter.$gte = s;
  }
  if (filter?.endDate) {
    const e = new Date(filter.endDate);
    if (!isNaN(e.getTime())) dateFilter.$lte = e;
  }
  const hasDateFilter = Object.keys(dateFilter).length > 0;
  
  // 1. Find team where this user is the lead
  const team = await Team.findOne({ lead: userId, companyId: company })
    .populate('lead', 'firstName lastName')
    .populate('members', 'firstName lastName');

  if (!team) return [];

  // 2. Collect team members (lead + members)
  const memberIds = [
    team.lead?._id,
    ...(team.members || []).map((m) => m._id),
  ].filter(Boolean);

  // 3. For each member, prepare async work
  const tasks = memberIds.map(async (memberId) => {
    const member: TeamMemberLite | undefined = team.lead?._id.equals(memberId)
      ? (team.lead as TeamMemberLite)
      : (team.members as TeamMemberLite[]).find((m) => m._id.equals(memberId));

    const fullName = [member?.firstName, member?.lastName]
      .filter(Boolean)
      .join(' ');

    /* =========================
       TASK STATS
    ========================== */
    const taskAgg = await Tasks.aggregate([
      {
        $match: {
          companyId: company,
          assignedTo: memberId,
          ...(hasDateFilter && { activityDate: dateFilter }),
        },
      },
      {
        $group: {
          _id: {
            activityType: '$activityType',
            status: '$status',
          },
          count: { $sum: 1 },
        },
      },
    ]);

    const activityStats: Record<TaskActivityType, ActivityStats> = {
      [TaskActivityType.CALL]: { total: 0, success: 0, pending: 0 },
      [TaskActivityType.MEETING]: { total: 0, success: 0, pending: 0 },
      [TaskActivityType.SITE_VISIT]: { total: 0, success: 0, pending: 0 },
      [TaskActivityType.OTHER]: { total: 0, success: 0, pending: 0 },
    };

    // ✅ Count ONLY success & pending
    taskAgg.forEach((t) => {
      const type = t._id.activityType as TaskActivityType;
      const status = t._id.status as TaskStatus;

      if (!activityStats[type]) return;

      if (status === TaskStatus.COMPLETED)
        activityStats[type].success += t.count;
      else if (status === TaskStatus.PENDING)
        activityStats[type].pending += t.count;
    });

    // ✅ DERIVE TOTAL = success + pending
    Object.values(activityStats).forEach((stat) => {
      stat.total = stat.success + stat.pending;
    });

    const calls = activityStats[TaskActivityType.CALL];
    const meetings = activityStats[TaskActivityType.MEETING];
    const siteVisits = activityStats[TaskActivityType.SITE_VISIT];

    const callsSuccessRate = calls.total
      ? Math.round((calls.success / calls.total) * 100)
      : 0;

    const meetingsSuccessRate = meetings.total
      ? Math.round((meetings.success / meetings.total) * 100)
      : 0;

    const siteVisitsSuccessRate = siteVisits.total
      ? Math.round((siteVisits.success / siteVisits.total) * 100)
      : 0;

    const totalActivities =
      calls.total +
      meetings.total +
      siteVisits.total +
      activityStats[TaskActivityType.OTHER].total;

    const overallSuccessRate = totalActivities
      ? Math.round(
          ((calls.success + meetings.success + siteVisits.success) /
            totalActivities) *
            100,
        )
      : 0;

    /* =========================
       SALES STATS
    ========================== */
    const salesAgg = await Customer.aggregate([
      { $match: { company, 'sales.soldBy': memberId } },
      { $unwind: '$sales' },
      { $match: { 'sales.soldBy': memberId,
        ...(hasDateFilter && { createdAt: dateFilter }), // or salesAt if exists
       } },
      {
        $lookup: {
          from: 'unitbookingorholds',
          localField: 'unitBookingOrHold',
          foreignField: '_id',
          as: 'unitBookingOrHold',
        },
      },
      {
        $unwind: {
          path: '$unitBookingOrHold',
          preserveNullAndEmptyArrays: true,
        },
      },
      {
        $group: {
          _id: null,
          count: { $sum: 1 },
          totalValue: { $sum: '$unitBookingOrHold.bookingAmount' },
        },
      },
    ]);

    const sales = salesAgg[0] || { count: 0, totalValue: 0 };

    const activitySaleRatio =
      totalActivities > 0
        ? Math.round((sales.count / totalActivities) * 100)
        : 0;

    return {
      id: memberId.toString(),
      teamMember: fullName,
      calls,
      callsSuccessRate,
      meetings,
      meetingsSuccessRate,
      siteVisits,
      siteVisitsSuccessRate,
      totalActivities,
      overallSuccessRate,
      sales: {
        count: sales.count,
        value: sales.totalValue,
      },
      activitySaleRatio,
    } as TeamActivityData;
  });

  // 4. Run all queries concurrently
  return Promise.all(tasks);
};

export const teamActivityDetailsReport = async (
  company: Types.ObjectId,
  filters: TeamActivityDetailsFilters = {},
  options: PaginateOptions = {},
): Promise<TeamActivityDetailsReportData> => {
  const page = Math.max(1, parseInt(String(options.page || 1), 10) || 1);
  const limit = Math.max(1, parseInt(String(options.limit || 10), 10) || 10);
  const skip = (page - 1) * limit;

  const assignedToIds = parseObjectIds(filters.assignedTo);
  const projectIds = parseObjectIds(filters.projectId || filters.project);
  const propertyIds = parseObjectIds(filters.propertyId);
  const leadStageIds = parseObjectIds(filters.leadStageId || filters.leadStage);
  const sourceIds = parseObjectIds(filters.sourceId || filters.source);
  const companyDoc = await Company.findById(company)
    .select('companyType')
    .lean();
  const isBrokerCompany = companyDoc?.companyType === CompanyType.BROKER;
  const reportView: TeamActivityReportView =
    filters.view === 'overview' || filters.view === 'detail'
      ? filters.view
      : 'all';

  const activityTypes = (filters.activityType || '')
    .split(',')
    .map((value) => value.trim())
    .filter(Boolean)
    .filter((value) =>
      Object.values(TaskActivityType).includes(value as TaskActivityType),
    );

  const activityStatuses = (filters.status || '')
    .split(',')
    .map((value) => value.trim())
    .filter(Boolean)
    .filter((value) => Object.values(TaskStatus).includes(value as TaskStatus));

  const search = (filters.search || '').trim();
  const searchRegex = search ? new RegExp(escapeRegex(search), 'i') : null;

  const taskMatch: Record<string, unknown> = {
    companyId: company,
  };

  if (assignedToIds.length > 0)
    taskMatch.assignedTo =
      assignedToIds.length === 1 ? assignedToIds[0] : { $in: assignedToIds };

  if (activityTypes.length > 0) taskMatch.activityType = { $in: activityTypes };

  if (activityStatuses.length > 0) taskMatch.status = { $in: activityStatuses };

  if (filters.startDate || filters.endDate) {
    const activityDate: Record<string, Date> = {};
    if (filters.startDate) {
      const startDate = new Date(filters.startDate);
      if (!Number.isNaN(startDate.getTime())) activityDate.$gte = startDate;
    }
    if (filters.endDate) {
      const endDate = new Date(filters.endDate);
      if (!Number.isNaN(endDate.getTime())) activityDate.$lte = endDate;
    }
    if (Object.keys(activityDate).length > 0)
      taskMatch.activityDate = activityDate;
  }

  const selectedEntityIds = isBrokerCompany
    ? propertyIds.length > 0
      ? propertyIds
      : projectIds
    : projectIds;
  const entityMatchField = isBrokerCompany
    ? 'effectivePropertyId'
    : 'effectiveProjectId';
  const enrichedPipeline = buildTeamActivityEnrichedPipeline({
    company,
    taskMatch,
    isBrokerCompany,
    selectedEntityIds,
    entityMatchField,
    leadStageIds,
    sourceIds,
    searchRegex,
  });

  const facetStage = buildTeamActivityFacetStage({
    reportView,
    skip,
    limit,
  });

  const [
    aggregateResult,
    distinctAssignedToIds,
    entityOptions,
    leadStages,
    leadSources,
  ] = await Promise.all([
    Tasks.aggregate<any>([
      ...enrichedPipeline,
      {
        $facet: facetStage,
      },
    ] as any[]),
    Tasks.distinct('assignedTo', {
      companyId: company,
      assignedTo: { $ne: null },
    }),
    isBrokerCompany
      ? IndividualProperties.find({
          companyId: company,
          isDeleted: { $ne: true },
        })
          .select('_id title')
          .sort({ title: 1 })
          .lean()
      : Project.find({ companyId: company })
          .select('_id projectName')
          .sort({ projectName: 1 })
          .lean(),
    LeadStage.find({ company, isActive: true })
      .select('_id stageName')
      .sort({ position: 1 })
      .lean(),
    Source.find({ company }).select('_id name').sort({ name: 1 }).lean(),
  ]);

  const reportData = aggregateResult?.[0] || {};
  const summary =
    reportView === 'detail'
      ? {
          totalActivities: 0,
          completed: 0,
          pending: 0,
          overdue: 0,
          cancelled: 0,
        }
      : reportData.summary?.[0] || {
          totalActivities: 0,
          completed: 0,
          pending: 0,
          overdue: 0,
          cancelled: 0,
        };

  const activityDistribution =
    reportView === 'detail'
      ? []
      : (reportData.activityDistribution || []).map(
          (item: { _id: string; count: number }) => ({
            activityType: item._id || 'Other',
            count: item.count || 0,
          }),
        );

  const statusByTypeMap = new Map<string, TeamActivityStatusByTypeItem>();
  (reportView === 'detail'
    ? []
    : reportData.activityStatusByType || []
  ).forEach(
    (row: { _id: { activityType: string; status: string }; count: number }) => {
      const type = row._id?.activityType || 'Other';
      const status = (row._id?.status || '').toLowerCase();

      if (!statusByTypeMap.has(type))
        statusByTypeMap.set(type, {
          activityType: type,
          completed: 0,
          pending: 0,
          overdue: 0,
          cancelled: 0,
        });

      const stat = statusByTypeMap.get(type)!;
      if (status === 'completed') stat.completed = row.count;
      else if (status === 'pending') stat.pending = row.count;
      else if (status === 'overdue') stat.overdue = row.count;
      else if (status === 'cancelled') stat.cancelled = row.count;
    },
  );

  const activityStatusByType = Array.from(statusByTypeMap.values());

  const totalResults =
    reportView === 'overview'
      ? 0
      : reportData.tableDataCount?.[0]?.totalResults || 0;
  const totalPages = totalResults > 0 ? Math.ceil(totalResults / limit) : 0;
  const results = reportView === 'overview' ? [] : reportData.tableData || [];

  const teamMembers = distinctAssignedToIds.length
    ? await User.find(
        { _id: { $in: distinctAssignedToIds } },
        'firstName lastName',
      )
        .sort({ firstName: 1, lastName: 1 })
        .lean()
    : [];

  return {
    summary: {
      totalActivities: Number(summary.totalActivities || 0),
      completed: Number(summary.completed || 0),
      pending: Number(summary.pending || 0),
      overdue: Number(summary.overdue || 0),
      cancelled: Number(summary.cancelled || 0),
    },
    activityDistribution,
    activityStatusByType,
    tableData: {
      results,
      page,
      limit,
      totalPages,
      totalResults,
    },
    filterOptions: {
      teamMembers: teamMembers.map((member) => ({
        id: member._id.toString(),
        name:
          `${member.firstName || ''} ${member.lastName || ''}`.trim() ||
          'Unknown',
      })),
      activityTypes: Object.values(TaskActivityType).map((value) => ({
        value,
        label: ACTIVITY_TYPE_LABELS[value] || value,
      })),
      activityStatuses: Object.values(TaskStatus).map((value) => ({
        value,
        label: TASK_STATUS_LABELS[value] || value,
      })),
      projects: entityOptions.map((entity) => ({
        id: entity._id.toString(),
        name: isBrokerCompany
          ? (entity as { title?: string }).title || 'Untitled Property'
          : (entity as { projectName?: string }).projectName ||
            'Untitled Project',
      })),
      leadStages: leadStages.map((stage) => ({
        id: stage._id.toString(),
        name: stage.stageName,
      })),
      leadSources: leadSources.map((source) => ({
        id: source._id.toString(),
        name: source.name,
      })),
    },
  };
};

/* =========================
   LEAD SOURCE PERFORMANCE
========================= */

export const leadSourcePerformance = async (
  company: Types.ObjectId,
  filters?: {
    projectId?: string;
    project?: string;
    startDate?: Date;
    endDate?: Date;
  },
): Promise<LeadSourcePerformanceData[]> => {
  // 1. Get all stages sorted by position
  const stages = await LeadStage.find({ company, isActive: true })
    .sort({ position: 1 })
    .lean();

  // console.log("date : ",filters.startDate);
  // 2. Get all sources for this company
  const sources = await Source.find({ company }).lean();

  // 3. Build match criteria with filters
  const matchCriteria: any = { company };

  // Add project filter (for buy leads only)
  const projectFilter = filters?.projectId || filters?.project;
  if (projectFilter) {
    const parseIds = (ids: string) => {
      const arr = ids.split(',').map((id) => getObjectId(id.trim()));
      return arr.length === 1 ? arr[0] : { $in: arr };
    };
    matchCriteria.project = parseIds(projectFilter);
  }

  // Add date range filter
  if (filters?.startDate || filters?.endDate) {
    matchCriteria.createdAt = {};
    if (filters.startDate)
      matchCriteria.createdAt.$gte = new Date(filters.startDate);
    if (filters.endDate)
      matchCriteria.createdAt.$lte = new Date(filters.endDate);
  }

  // 3. Aggregate leads by source + stage
  const leadAgg = await Lead.aggregate([
    { $match: matchCriteria },
    {
      $group: {
        _id: { source: '$source', stage: '$leadStage' },
        count: { $sum: 1 },
      },
    },
  ]);

  // Aggregate leads by source + captureLead + stage
  const captureLeadAgg = await Lead.aggregate([
    { $match: { ...matchCriteria, captureLead: { $exists: true, $ne: null } } },
    {
      $group: {
        _id: {
          source: '$source',
          captureLead: '$captureLead',
          stage: '$leadStage',
        },
        count: { $sum: 1 },
      },
    },
  ]);

  // Fetch capture leads grouped by source ID
  const captureLeadsBySource: Record<string, any[]> = Object.fromEntries(
    await Promise.all(
      sources.map(async (source) => {
        const sourceId = source._id.toString();
        const captureLeadsForSource = await CaptureLead.find({
          company,
          source: source._id,
          isActive: true,
        }).lean();

        return [sourceId, captureLeadsForSource];
      }),
    ),
  );

  // Build name map for all capture leads
  const allCaptureLeads = Object.values(captureLeadsBySource).flat();
  const captureLeadNameMap = Object.fromEntries(
    allCaptureLeads.map((cl) => [cl._id.toString(), cl.name]),
  );

  // Lookup maps
  const sourceMap = Object.fromEntries(
    sources.map((s) => [s._id.toString(), s.name]),
  );

  const stageMap = Object.fromEntries(
    stages.map((st) => [st._id.toString(), st.stageName]),
  );

  // Stage name to field mapping function
  const mapStageNameToField = (
    stageName: string,
  ):
    | keyof Pick<
        LeadSourcePerformanceData,
        | 'newLeads'
        | 'contacted'
        | 'qualified'
        | 'proposal'
        | 'negotiation'
        | 'won'
        | 'lost'
      >
    | null => {
    const normalized = stageName.toLowerCase().trim();

    if (normalized === 'new lead' || normalized === 'new inquiry')
      return 'newLeads';
    if (normalized === 'contacted') return 'contacted';
    if (normalized === 'qualified') return 'qualified';
    if (normalized === 'proposal') return 'proposal';
    if (normalized === 'negotiation') return 'negotiation';
    if (normalized === 'lead won' || normalized === 'won') return 'won';
    if (normalized === 'lead lost' || normalized === 'lost') return 'lost';

    return null; // Unknown stage
  };

  // Organize counts per source
  const grouped: Record<
    string,
    { source: string; stageCounts: Record<string, number>; total: number }
  > = {};

  for (const row of leadAgg) {
    const sourceId = row._id.source?.toString();
    const stageId = row._id.stage?.toString();
    if (!sourceId || !stageId) continue;

    if (!grouped[sourceId])
      grouped[sourceId] = {
        source: sourceMap[sourceId],
        stageCounts: {},
        total: 0,
      };

    grouped[sourceId].stageCounts[stageId] = row.count;
    grouped[sourceId].total += row.count;
  }

  // Organize captureLead counts per source → captureLead → stageName
  // Structure: captureLeadGrouped[sourceId][captureLeadId][stageName] = count
  // Initialize with all capture leads for all sources, all stages set to 0
  const captureLeadGrouped: Record<
    string,
    Record<string, Record<string, number>>
  > = {};

  // Initialize structure with capture leads for each source
  sources.forEach((source) => {
    const sourceId = source._id.toString();
    captureLeadGrouped[sourceId] = {};

    const captureLeadsForSource = captureLeadsBySource[sourceId] || [];
    captureLeadsForSource.forEach((cl) => {
      const clId = cl._id.toString();
      captureLeadGrouped[sourceId][clId] = {};
      // Initialize all stages with 0 count
      stages.forEach((stage) => {
        const stageName = stage.stageName;
        captureLeadGrouped[sourceId][clId][stageName] = 0;
      });
    });
  });

  // Update counts based on actual data
  for (const row of captureLeadAgg) {
    const sourceId = row._id.source?.toString();
    const clId = row._id.captureLead?.toString();
    const stageId = row._id.stage?.toString();
    if (!sourceId || !clId || !stageId) continue;

    const stageName = stageMap[stageId];
    if (!stageName) continue;

    // Update count if structure exists
    if (captureLeadGrouped[sourceId]?.[clId]?.[stageName] !== undefined)
      captureLeadGrouped[sourceId][clId][stageName] = row.count;
  }

  // 4. Build response with transitions for all sources
  const response: LeadSourcePerformanceData[] = sources.map((source) => {
    const sourceId = source._id.toString();
    const data = grouped[sourceId] || {
      source: source.name,
      stageCounts: {},
      total: 0,
    };

    const stagesPerf: Record<string, StagePerformance> = {};
    let prevStageCount = data.total;

    for (let i = 0; i < stages.length; i++) {
      const stage = stages[i];
      const stageId = stage._id.toString();
      const stageName = stage.stageName;

      const count = data.stageCounts[stageId] || 0;

      if (i > 0) {
        const prevStageName = stages[i - 1].stageName;
        const key =
          prevStageName.replace(/\s+/g, '').toLowerCase() +
          'To' +
          stageName
            .replace(/\s+/g, '')
            .toLowerCase()
            .replace(/^\w/, (c) => c.toUpperCase());

        const percentage =
          prevStageCount > 0 ? Math.round((count / prevStageCount) * 100) : 0;

        stagesPerf[key] = { count, percentage, stageName: prevStageName };
      }

      prevStageCount = count;
    }

    // final bookings = leads in "Lead Won"
    const finalBookings = Object.entries(data.stageCounts).reduce(
      (acc, [stageId, cnt]) => {
        const stName = stageMap[stageId]?.toLowerCase();
        if (stName && stName.includes('won')) return acc + cnt;

        return acc;
      },
      0,
    );

    const conversionRate =
      data.total > 0 ? Math.round((finalBookings / data.total) * 100) : 0;

    // Calculate individual stage counts
    const stageCounts = {
      newLeads: 0,
      contacted: 0,
      qualified: 0,
      proposal: 0,
      negotiation: 0,
      won: 0,
      lost: 0,
    };

    // Map each stage count to the appropriate field
    Object.entries(data.stageCounts).forEach(([stageId, count]) => {
      const stageName = stageMap[stageId];
      if (stageName) {
        const fieldName = mapStageNameToField(stageName);
        if (fieldName) stageCounts[fieldName] += count;
      }
    });

    // Build captuerLeads array for this source
    const clGroupForSource = captureLeadGrouped[sourceId] || {};
    const captuerLeads: CaptureLeadStages[] = Object.entries(
      clGroupForSource,
    ).map(([clId, stagesRecord]) => ({
      name: captureLeadNameMap[clId] || clId,
      stages: stagesRecord,
    }));

    return {
      id: sourceId,
      source: data.source,
      totalLeads: data.total,
      stages: stagesPerf,
      captuerLeads,
      finalBookings,
      conversionRate,
      ...stageCounts,
    };
  });

  return response;
};

export const salesByLeadSource = async (
  company: Types.ObjectId,
  filters?: {
    projectId?: string;
    project?: string;
    startDate?: Date;
    endDate?: Date;
  },
): Promise<LeadSourceData[]> => {
  // 1. Build match criteria with filters
  const matchCriteria: any = { company };

  // Add project filter (for buy leads only)
  const projectFilter = filters?.projectId || filters?.project;
  if (projectFilter) {
    const parseIds = (ids: string) => {
      const arr = ids.split(',').map((id) => getObjectId(id.trim()));
      return arr.length === 1 ? arr[0] : { $in: arr };
    };
    matchCriteria.project = parseIds(projectFilter);
  }

  // Add date range filter
  if (filters?.startDate || filters?.endDate) {
    matchCriteria.createdAt = {};
    if (filters.startDate) matchCriteria.createdAt.$gte = filters.startDate;
    if (filters.endDate) matchCriteria.createdAt.$lte = filters.endDate;
  }

  // 1. Aggregate leads grouped by source
  const leadsAgg = await Lead.aggregate([
    { $match: matchCriteria },
    {
      $lookup: {
        from: 'sources',
        localField: 'source',
        foreignField: '_id',
        as: 'source',
      },
    },
    { $unwind: '$source' },
    {
      $lookup: {
        from: 'leadstages',
        localField: 'leadStage',
        foreignField: '_id',
        as: 'leadStage',
      },
    },
    { $unwind: '$leadStage' },
    {
      $group: {
        _id: '$source._id',
        sourceName: { $first: '$source.name' },
        totalLeads: { $sum: 1 },
        qualifiedLeads: {
          $sum: {
            $cond: [{ $eq: ['$leadStage.stageName', 'Lead Won'] }, 1, 0],
          },
        },
        dealsClosed: {
          $sum: {
            $cond: [{ $eq: ['$leadStage.stageName', 'Lead Lost'] }, 1, 0],
          },
        },
        leadIds: { $push: '$_id' },
        createdDates: { $push: '$createdAt' },
        stageDates: { $push: '$updatedAt' }, // approximate closure date
      },
    },
  ]);

  // 2. Gather customers + payments linked to won leads
  const leadIds = leadsAgg.flatMap((s) => s.leadIds);
  const customers = await Customer.find(
    { company, leadId: { $in: leadIds } },
    '_id leadId createdAt',
  );

  const customerPayments = await CustomerPayment.find(
    { company, customerId: { $in: customers.map((c) => c._id) } },
    'customerId totalAmount',
  );

  const paymentMap = new Map<string, number>();
  customerPayments.forEach((p) => {
    paymentMap.set(
      p.customerId.toString(),
      (paymentMap.get(p.customerId.toString()) ?? 0) + (p.totalAmount || 0),
    );
  });

  // 3. Company-wide totals for contribution %
  const totalCompanyCustomers = await Customer.countDocuments({ 
    company,
  });

  // 4. Build response
  const result: LeadSourceData[] = leadsAgg.map((src) => {
    const wonCustomers = customers.filter((c) =>
      src.leadIds.some((l: Types.ObjectId) => l.equals(c.leadId)),
    );

    const totalTransactionValue = wonCustomers.reduce(
      (sum, c) => sum + (paymentMap.get(c._id.toString()) ?? 0),
      0,
    );

    const avgDealValue =
      src.qualifiedLeads > 0 ? totalTransactionValue / src.qualifiedLeads : 0;

    // ✅ conversion rate uses qualified leads
    const conversionRate =
      src.totalLeads > 0 ? (src.qualifiedLeads / src.totalLeads) * 100 : 0;

    // ✅ contribution = share of customers this source produced
    const contributionToSales =
      totalCompanyCustomers > 0
        ? (wonCustomers.length / totalCompanyCustomers) * 100
        : 0;

    // closure time (rough: updatedAt - createdAt for won leads)
    const closureDurations: number[] = [];
    src.createdDates.forEach((created: Date, idx: number) => {
      if (src.stageDates[idx] && created)
        closureDurations.push(
          (+src.stageDates[idx] - +created) / (1000 * 60 * 60 * 24), // days
        );
    });
    const avgClosureTime =
      closureDurations.length > 0
        ? closureDurations.reduce((a, b) => a + b, 0) / closureDurations.length
        : 0;

    return {
      id: src._id.toString(),
      source: src.sourceName,
      totalLeads: src.totalLeads,
      qualifiedLeads: src.qualifiedLeads,
      dealsClosed: src.dealsClosed,
      conversionRate: Number(conversionRate.toFixed(2)),
      totalTransactionValue,
      avgDealValue,
      contributionToSales: Number(contributionToSales.toFixed(2)),
      avgClosureTime: Number(avgClosureTime.toFixed(2)),
    };
  });

  return result;
};

export const salesAgingReport = async (
  company: Types.ObjectId,
  filters?: {
    projectId?: string;
    project?: string;
    propertyId?: string;
    assignedTo?: string;
    view?: 'closed' | 'open' | 'all';
    startDate?: string | Date;
    endDate?: string | Date;
  },
  context?: {
    userId?: string | Types.ObjectId;
    userType?: string;
  },
): Promise<SalesAgingReportData> => {
  const companyDoc = await Company.findById(company)
    .select('companyType')
    .lean();
  const isBrokerCompany = companyDoc?.companyType === CompanyType.BROKER;
  const matchCriteria: Record<string, unknown> = { company };
  const reportView =
    filters?.view === 'open' || filters?.view === 'closed'
      ? filters.view
      : 'all';
  const shouldIncludeClosed = reportView !== 'open';
  const shouldIncludeOpen = reportView !== 'closed';

  const isAdminUser =
    context?.userType === UserType.ADMIN ||
    context?.userType === UserType.SUPERADMIN;

  const requestedAssignedTo =
    filters?.assignedTo && Types.ObjectId.isValid(filters.assignedTo)
      ? getObjectId(filters.assignedTo)
      : null;

  if (isAdminUser) {
    if (requestedAssignedTo) matchCriteria.assignedTo = requestedAssignedTo;
  } else if (
    context?.userId &&
    Types.ObjectId.isValid(String(context.userId))
  ) {
    matchCriteria.assignedTo = getObjectId(String(context.userId));
  }

  const entityFilter = isBrokerCompany
    ? filters?.propertyId || filters?.projectId || filters?.project
    : filters?.projectId || filters?.project;
  const entityIds = parseObjectIds(entityFilter);

  if (filters?.startDate || filters?.endDate) {
    const createdAt: Record<string, Date> = {};

    if (filters.startDate) createdAt.$gte = new Date(filters.startDate);
    if (filters.endDate) createdAt.$lte = new Date(filters.endDate);

    matchCriteria.createdAt = createdAt;
  }

  const entityMatchStages = buildSalesAgingEntityMatchStages({
    entityIds,
    isBrokerCompany,
  });

  const [wonStages, lostStages] = await Promise.all([
    LeadStage.find({
      company,
      stageName: { $regex: /lead won|won/i },
    })
      .select('_id')
      .lean(),
    LeadStage.find({
      company,
      stageName: { $regex: /lead lost|lost/i },
    })
      .select('_id')
      .lean(),
  ]);

  const wonStageIds = wonStages.map((stage) => stage._id);
  const lostStageIds = lostStages.map((stage) => stage._id);
  const terminalStageIds = [...wonStageIds, ...lostStageIds];

  const closedLeads: ClosedLeadAggregateRow[] =
    shouldIncludeClosed && wonStageIds.length
      ? await Lead.aggregate<ClosedLeadAggregateRow>(
          buildClosedSalesAgingPipeline({
            matchCriteria,
            wonStageIds,
            entityMatchStages,
            isBrokerCompany,
          }),
        )
      : [];

  const openLeads: OpenLeadAggregateRow[] = shouldIncludeOpen
    ? await Lead.aggregate<OpenLeadAggregateRow>(
        buildOpenSalesAgingPipeline({
          matchCriteria,
          terminalStageIds,
          entityMatchStages,
          isBrokerCompany,
        }),
      )
    : [];

  const openLeadIds = openLeads.map((lead) => lead._id).filter(Boolean);

  const leadTaskMap = new Map<
    string,
    {
      lastActivityDate: Date | null;
      nextPendingDate: Date | null;
      nextPendingFutureDate: Date | null;
    }
  >();

  if (openLeadIds.length) {
    const leadTasks = await Tasks.find(
      {
        companyId: company,
        leadId: { $in: openLeadIds },
      },
      'leadId activityDate status',
    ).lean();

    leadTasks.forEach((task: any) => {
      if (!task?.leadId || !task?.activityDate) return;

      const leadId = task.leadId.toString();
      const activityDate = new Date(task.activityDate);

      const existing = leadTaskMap.get(leadId) || {
        lastActivityDate: null,
        nextPendingDate: null,
        nextPendingFutureDate: null,
      };

      if (
        !existing.lastActivityDate ||
        activityDate > existing.lastActivityDate
      )
        existing.lastActivityDate = activityDate;

      if (task.status === TaskStatus.PENDING) {
        if (
          !existing.nextPendingDate ||
          activityDate < existing.nextPendingDate
        )
          existing.nextPendingDate = activityDate;

        if (activityDate >= new Date())
          if (
            !existing.nextPendingFutureDate ||
            activityDate < existing.nextPendingFutureDate
          )
            existing.nextPendingFutureDate = activityDate;
      }

      leadTaskMap.set(leadId, existing);
    });
  }

  const now = new Date();

  const closedDeals: SalesAgingClosedDealItem[] = closedLeads.map((lead) => {
    const leadCreatedDate = new Date(lead.leadCreatedDate || now);
    const dealClosedDate = new Date(lead.dealClosedDate || leadCreatedDate);
    const closureDuration =
      typeof lead.closureDuration === 'number'
        ? Math.max(0, Math.round(lead.closureDuration))
        : getDayDifference(leadCreatedDate, dealClosedDate);

    return {
      id: lead._id.toString(),
      leadId: lead._id.toString(),
      teamMember: {
        id: lead.assignedToId?.toString() || '',
        name: lead.teamMemberName || 'Unassigned',
        avatar: lead.teamMemberAvatar || null,
      },
      transactionType: getTransactionTypeLabel(lead.interestType),
      projectId: lead.projectId?.toString() || null,
      projectProperty: lead.projectProperty || 'N/A',
      customerName: lead.customerName || 'Unknown',
      dealValue: Number(lead.dealValue || 0),
      leadCreatedDate: leadCreatedDate.toISOString(),
      dealClosedDate: dealClosedDate.toISOString(),
      closureDuration,
    };
  });

  const openDeals: SalesAgingOpenDealItem[] = openLeads.map((lead) => {
    const leadCreatedDate = new Date(lead.leadCreatedDate || now);
    const leadTaskStats = leadTaskMap.get(lead._id.toString());

    const fallbackLastActivity = new Date(
      lead.baseLastActivityDate || leadCreatedDate,
    );
    const taskLastActivity = leadTaskStats?.lastActivityDate;

    const lastActivityDate =
      taskLastActivity && taskLastActivity > fallbackLastActivity
        ? taskLastActivity
        : fallbackLastActivity;

    const nextFollowup =
      leadTaskStats?.nextPendingFutureDate ||
      leadTaskStats?.nextPendingDate ||
      null;

    const agingDays = getDayDifference(leadCreatedDate, now);

    return {
      id: lead._id.toString(),
      leadId: lead._id.toString(),
      teamMember: {
        id: lead.assignedToId?.toString() || '',
        name: lead.teamMemberName || 'Unassigned',
        avatar: lead.teamMemberAvatar || null,
      },
      customerName: lead.customerName || 'Unknown',
      projectId: lead.projectId?.toString() || null,
      projectProperty: lead.projectProperty || 'N/A',
      leadStage: lead.leadStage || 'Unknown',
      leadCreatedDate: leadCreatedDate.toISOString(),
      agingBucket: getAgingBucketLabel(agingDays),
      lastActivityDate: lastActivityDate.toISOString(),
      nextFollowup: nextFollowup ? nextFollowup.toISOString() : null,
    };
  });

  const totalClosedDeals = closedDeals.length;
  const totalDealValue = closedDeals.reduce(
    (sum, deal) => sum + deal.dealValue,
    0,
  );
  const totalClosureDuration = closedDeals.reduce(
    (sum, deal) => sum + deal.closureDuration,
    0,
  );

  const avgClosureDuration =
    totalClosedDeals > 0
      ? Number((totalClosureDuration / totalClosedDeals).toFixed(1))
      : 0;

  const fastestClosure =
    totalClosedDeals > 0
      ? Math.min(...closedDeals.map((deal) => deal.closureDuration))
      : 0;

  const closureDurationByTransactionTypeMap = closedDeals.reduce<
    Record<string, { totalDays: number; deals: number }>
  >((acc, deal) => {
    if (!acc[deal.transactionType])
      acc[deal.transactionType] = { totalDays: 0, deals: 0 };

    acc[deal.transactionType].totalDays += deal.closureDuration;
    acc[deal.transactionType].deals += 1;

    return acc;
  }, {});

  const closureDurationByTransactionType = Object.entries(
    closureDurationByTransactionTypeMap,
  )
    .map(([transactionType, value]) => ({
      transactionType,
      avgDays: value.deals
        ? Number((value.totalDays / value.deals).toFixed(1))
        : 0,
      deals: value.deals,
    }))
    .sort((a, b) => b.deals - a.deals);

  const closureDurationDistribution = [
    {
      bucket: '0-15 days',
      count: closedDeals.filter((deal) => deal.closureDuration <= 15).length,
    },
    {
      bucket: '16-30 days',
      count: closedDeals.filter(
        (deal) => deal.closureDuration > 15 && deal.closureDuration <= 30,
      ).length,
    },
    {
      bucket: '31-45 days',
      count: closedDeals.filter(
        (deal) => deal.closureDuration > 30 && deal.closureDuration <= 45,
      ).length,
    },
    {
      bucket: '45+ days',
      count: closedDeals.filter((deal) => deal.closureDuration > 45).length,
    },
  ];

  const leadsByAgingBucket = OPEN_AGING_BUCKETS.map((bucket) => ({
    bucket,
    count: openDeals.filter((deal) => deal.agingBucket === bucket).length,
  }));

  const leadsByStage = Object.entries(
    openDeals.reduce<Record<string, number>>((acc, deal) => {
      acc[deal.leadStage] = (acc[deal.leadStage] || 0) + 1;
      return acc;
    }, {}),
  )
    .map(([stage, count]) => ({ stage, count }))
    .sort((a, b) => b.count - a.count);

  const openOverview = {
    totalOpenLeads: openDeals.length,
    leadsNeedingFollowup: openDeals.filter((deal) => deal.nextFollowup !== null)
      .length,
    agingOverThirtyDays: openDeals.filter((deal) =>
      ['31-60 days', '60+ days'].includes(deal.agingBucket),
    ).length,
    noFollowupScheduled: openDeals.filter((deal) => deal.nextFollowup === null)
      .length,
    leadsByAgingBucket,
    leadsByStage,
  };

  return {
    closedOverview: {
      totalClosedDeals,
      avgClosureDuration,
      fastestClosure,
      totalDealValue,
      closureDurationByTransactionType,
      closureDurationDistribution,
    },
    openOverview,
    closedDeals,
    openDeals,
  };
};

export const salesConversionRate = async (
  userId: Types.ObjectId,
  company: Types.ObjectId,
  filter: any,
): Promise<SalesConversionResponse> => {
  try {
    // Find teams where the user is a lead
    const teams = await Team.find({
      lead: userId,
      companyId: company,
      ...filter,
    })
      .populate('members', 'firstName lastName email')
      .populate('lead', 'firstName lastName email')
      .lean<ITeamPopulated[]>();

      // console.log("teams ================>",teams);
    // Get all team member IDs (including the lead)
    const allMembers = teams.reduce(
      (acc, team) => {
        if (team.lead)
          acc.push({
            _id: team.lead._id,
            name: team.lead.firstName + ' ' + team.lead.lastName,
          });

        acc.push(
          ...team.members.map((member) => ({
            _id: member._id,
            name: member.firstName + ' ' + member.lastName,
          })),
        );
        return acc;
      },
      [] as { _id: Types.ObjectId; name: string }[],
    );

    // Remove duplicates based on ID
    const uniqueMembers = allMembers.filter(
      (member, index, self) =>
        index ===
        self.findIndex((m) => m._id.toString() === member._id.toString()),
    );

    // Get lead stage for "lead won" (case insensitive)
    const leadWonStage = await LeadStage.findOne({
      stageName: { $regex: /^lead won$/i },
      company: company,
    });

    if (!leadWonStage) throw new Error('Lead Won stage not found');

    // 1. Team Member Conversion Data using Promise.all
    const teamMemberPromises = uniqueMembers.map(async (member) => {
      const [totalLeads, closedLeads] = await Promise.all([
        Lead.countDocuments({
          assignedTo: member._id,
          company: company,
        }),
        Lead.find({
          assignedTo: member._id,
          company: company,
          leadStage: leadWonStage._id,
        }),
      ]);

      const dealsClosed = closedLeads.length;
      const conversionRate =
        totalLeads > 0 ? (dealsClosed / totalLeads) * 100 : 0;

      // Calculate average days for closed deals
      let avgDays = 0;
      if (closedLeads.length > 0) {
        const totalDays = closedLeads.reduce((sum, lead) => {
          const daysDiff = Math.ceil(
            (lead.updatedAt.getTime() - lead.createdAt.getTime()) /
              (1000 * 60 * 60 * 24),
          );
          return sum + daysDiff;
        }, 0);
        avgDays = Math.round(totalDays / closedLeads.length);
      }

      return {
        id: member._id.toString(),
        name: member.name || 'Unknown',
        leadsHandled: totalLeads,
        dealsClosed,
        conversionRate: parseFloat(conversionRate.toFixed(1)),
        avgDays,
      };
    });

    const teamMemberConversionData = await Promise.all(teamMemberPromises);

    // 2. Lead Source Conversion Data
    const sourceAggregation = await Lead.aggregate([
      { $match: { company: company } },
      {
        $lookup: {
          from: 'sources',
          localField: 'source',
          foreignField: '_id',
          as: 'sourceInfo',
        },
      },
      { $unwind: '$sourceInfo' },
      {
        $group: {
          _id: '$source',
          sourceName: { $first: '$sourceInfo.name' },
          totalLeads: { $sum: 1 },
          closedDeals: {
            $sum: {
              $cond: [{ $eq: ['$leadStage', leadWonStage._id] }, 1, 0],
            },
          },
          avgDays: {
            $avg: {
              $cond: [
                { $eq: ['$leadStage', leadWonStage._id] },
                {
                  $divide: [
                    { $subtract: ['$updatedAt', '$createdAt'] },
                    1000 * 60 * 60 * 24,
                  ],
                },
                null,
              ],
            },
          },
        },
      },
    ]);

    const leadSourceConversionData: LeadSourceConversionData[] =
      sourceAggregation.map((item) => ({
        id: item._id,
        source: item.sourceName || 'Unknown',
        leads: item.totalLeads,
        deals: item.closedDeals,
        conversionRate: parseFloat(
          ((item.closedDeals / item.totalLeads) * 100).toFixed(1),
        ),
        avgDays: Math.round(item.avgDays || 0),
      }));

    // 3. Transaction Type Conversion Data
    const transactionTypeAggregation = await Lead.aggregate([
      { $match: { company: company } },
      {
        $group: {
          _id: '$interestType',
          totalLeads: { $sum: 1 },
          closedDeals: {
            $sum: {
              $cond: [{ $eq: ['$leadStage', leadWonStage._id] }, 1, 0],
            },
          },
        },
      },
    ]);

    const transactionTypeConversionData: TransactionTypeConversionData[] =
      transactionTypeAggregation.map((item, index) => ({
        id: `TT-${String(index + 1).padStart(3, '0')}`,
        type: item._id.charAt(0).toUpperCase() + item._id.slice(1),
        leads: item.totalLeads,
        deals: item.closedDeals,
        conversionRate: parseFloat(
          ((item.closedDeals / item.totalLeads) * 100).toFixed(1),
        ),
      }));

    // 4. Project Conversion Data (only for 'buy' interest type)
    const projectAggregation = await Lead.aggregate([
      {
        $match: {
          company: company,
          interestType: 'buy',
          project: { $exists: true, $ne: null },
        },
      },
      {
        $lookup: {
          from: 'projects',
          localField: 'project',
          foreignField: '_id',
          as: 'projectInfo',
        },
      },
      { $unwind: '$projectInfo' },
      {
        $group: {
          _id: '$project',
          projectName: { $first: '$projectInfo.projectName' },
          totalLeads: { $sum: 1 },
          closedDeals: {
            $sum: {
              $cond: [{ $eq: ['$leadStage', leadWonStage._id] }, 1, 0],
            },
          },
          totalSales: {
            $sum: {
              $cond: [
                { $eq: ['$leadStage', leadWonStage._id] },
                { $ifNull: ['$budget', 0] },
                0,
              ],
            },
          },
        },
      },
    ]);

    const projectConversionData: ProjectConversionData[] =
      projectAggregation.map((item) => ({
        id: item._id,
        projectName: item.projectName || 'Unknown',
        leads: item.totalLeads,
        deals: item.closedDeals,
        conversionRate: parseFloat(
          ((item.closedDeals / item.totalLeads) * 100).toFixed(1),
        ),
        totalSales: item.totalSales
          ? parseFloat(item.totalSales.toString())
          : 0,
      }));

    return {
      teamMemberConversionData,
      leadSourceConversionData,
      transactionTypeConversionData,
      projectConversionData,
    };
  } catch (error) {
    console.error('Error in salesConversionRate:', error);
    throw error;
  }
};

export const salesPerformanceByAgent = async (company: Types.ObjectId,filter:any) => {
  const pipeline = [
    {
      $match: {
        company,
        assignedTo: { $ne: null },
      },
    },
    {
      $lookup: {
        from: 'leadstages',
        localField: 'leadStage',
        foreignField: '_id',
        as: 'leadStage',
      },
    },
    { $unwind: { path: '$leadStage', preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: 'users',
        localField: 'assignedTo',
        foreignField: '_id',
        as: 'agent',
      },
    },
    { $unwind: '$agent' },
    {
      $addFields: {
        isWon: { $eq: ['$leadStage.stageName', 'Lead Won'] },
        transactionValue: {
          $switch: {
            branches: [
              {
                case: { $eq: ['$interestType', LeadInterestType.SELL] },
                then: '$askingPrice',
              },
              {
                case: { $eq: ['$interestType', LeadInterestType.RENT] },
                then: '$rentAmount',
              },
              {
                case: { $eq: ['$interestType', LeadInterestType.LEASE] },
                then: '$leaseAmount',
              },
              {
                case: { $eq: ['$interestType', LeadInterestType.BUY] },
                then: '$budget',
              },
            ],
            default: 0,
          },
        },
        closureTime: {
          $cond: {
            if: { $eq: ['$leadStage.stageName', 'Lead Won'] },
            then: {
              $divide: [
                { $subtract: ['$updatedAt', '$createdAt'] },
                1000 * 60 * 60 * 24,
              ],
            }, // days
            else: null,
          },
        },
      },
    },
    {
      $group: {
        _id: '$assignedTo',
        name: {
          $first: { $concat: ['$agent.firstName', ' ', '$agent.lastName'] },
        },
        totalLeads: { $sum: 1 },
        sellCount: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$interestType', LeadInterestType.SELL] },
                  '$isWon',
                ],
              },
              1,
              0,
            ],
          },
        },
        rentCount: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$interestType', LeadInterestType.RENT] },
                  '$isWon',
                ],
              },
              1,
              0,
            ],
          },
        },
        leaseCount: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$interestType', LeadInterestType.LEASE] },
                  '$isWon',
                ],
              },
              1,
              0,
            ],
          },
        },
        buyCount: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$interestType', LeadInterestType.BUY] },
                  '$isWon',
                ],
              },
              1,
              0,
            ],
          },
        },
        totalDeals: { $sum: { $cond: ['$isWon', 1, 0] } },
        totalTransactionValue: {
          $sum: { $cond: ['$isWon', '$transactionValue', 0] },
        },
        closureTimes: { $push: '$closureTime' },
      },
    },
    {
      $project: {
        id: '$_id',
        _id: 0,
        name: 1,
        sellCount: 1,
        rentCount: 1,
        leaseCount: 1,
        buyCount: 1,
        totalDeals: 1,
        totalTransactionValue: { $toDouble: '$totalTransactionValue' },
        avgDealValue: {
          $cond: [
            { $gt: ['$totalDeals', 0] },
            {
              $round: [
                {
                  $toDouble: {
                    $divide: ['$totalTransactionValue', '$totalDeals'],
                  },
                },
                2,
              ],
            },
            0,
          ],
        },
        fastestClosure: {
          $reduce: {
            input: {
              $filter: {
                input: '$closureTimes',
                as: 'c',
                cond: { $ne: ['$$c', null] },
              },
            },
            initialValue: null,
            in: {
              $cond: [
                {
                  $or: [
                    { $eq: ['$$value', null] },
                    { $lt: ['$$this', '$$value'] },
                  ],
                },
                '$$this',
                '$$value',
              ],
            },
          },
        },
        avgClosureTime: {
          $avg: {
            $filter: {
              input: '$closureTimes',
              as: 'c',
              cond: { $ne: ['$$c', null] },
            },
          },
        },
        conversionRate: {
          $cond: [
            { $gt: ['$totalLeads', 0] },
            {
              $round: [
                {
                  $multiply: [{ $divide: ['$totalDeals', '$totalLeads'] }, 100],
                },
                0,
              ],
            },
            0,
          ],
        },
        totalLeads: 1,
      },
    },
  ];

  const results = await Lead.aggregate(pipeline,filter);
  // console.log("results ================>",results);
  return results;
};

export const salesByPropertyType = async (company: Types.ObjectId, filter: any) => {
  const pipeline = [
    { $match: { company } },
    { $unwind: '$sales' },
    { $match: { 'sales.kind': 'Property' } },
    {
      $lookup: {
        from: 'individualproperties',
        localField: 'sales.property',
        foreignField: '_id',
        as: 'property',
      },
    },
    { $unwind: '$property' },
    {
      $lookup: {
        from: 'categories',
        localField: 'property.propertyType',
        foreignField: '_id',
        as: 'category',
      },
    },
    { $unwind: { path: '$category', preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: 'subcategories',
        localField: 'property.subcategory',
        foreignField: '_id',
        as: 'subCategory',
      },
    },
    { $unwind: { path: '$subCategory', preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: 'configurations',
        localField: 'property.configuration',
        foreignField: '_id',
        as: 'configuration',
      },
    },
    { $unwind: { path: '$configuration', preserveNullAndEmptyArrays: true } },

    {
      $lookup: {
        from: 'customerpayments',
        let: { customerId: '$_id' },
        pipeline: [
          { $match: { $expr: { $eq: ['$customerId', '$$customerId'] } } },
          { $group: { _id: null, totalValue: { $sum: '$totalAmount' } } },
        ],
        as: 'payment',
      },
    },
    {
      $addFields: {
        transactionValue: {
          $ifNull: [{ $arrayElemAt: ['$payment.totalValue', 0] }, 0],
        },
      },
    },

    {
      $group: {
        _id: {
          category: '$category.name',
          subCategory: '$subCategory.name',
          configuration: '$configuration.name',
        },
        sellCount: {
          $sum: { $cond: [{ $eq: ['$property.listingType', 'sell'] }, 1, 0] },
        },
        rentCount: {
          $sum: { $cond: [{ $eq: ['$property.listingType', 'rent'] }, 1, 0] },
        },
        leaseCount: {
          $sum: { $cond: [{ $eq: ['$property.listingType', 'lease'] }, 1, 0] },
        },
        preLeaseCount: {
          $sum: {
            $cond: [{ $eq: ['$property.listingType', 'preLeased'] }, 1, 0],
          },
        },
        totalTransactions: { $sum: 1 },
        totalValue: { $sum: '$transactionValue' },
      },
    },
    {
      $project: {
        _id: 0,
        category: '$_id.category',
        subCategory: '$_id.subCategory',
        configuration: '$_id.configuration',
        sellCount: 1,
        rentCount: 1,
        leaseCount: 1,
        preLeaseCount: 1,
        totalTransactions: 1,
        totalValue: 1,
      },
    },
  ];
// console.log("pipeline ================>");
  const results = await Customer.aggregate(pipeline,filter); // apply filter 
  // console.log("results ================>",JSON.stringify(results));
  // add sequential IDs here
  return results.map((item, index) => ({
    id: `PT-${String(index + 1).padStart(3, '0')}`, // PT-001, PT-002 ...
    ...item,
  }));
};

export const monthlyYearlySalesComparison = async (
  company: Types.ObjectId,
  time: ComparisonTime,
): Promise<SalesComparisonResult[]> => {
  const now = new Date();
  const currentYear = now.getFullYear();

  // 1️⃣ Setup grouping
  let groupId: Record<string, unknown> = {};
  let periodFormatter: (doc: {
    year: number;
    month?: number;
    quarter?: number;
  }) => string;
  let match: Record<string, unknown> = { company };

  if (time === 'monthly') {
    groupId = {
      year: { $year: '$sales.salesAt' },
      month: { $month: '$sales.salesAt' },
    };
    periodFormatter = (d) => {
      const date = new Date(d.year, (d.month ?? 1) - 1);
      return (
        date.toLocaleString('en-US', { month: 'short' }) +
        '-' +
        String(d.year).slice(-2)
      );
    };
    match['sales.salesAt'] = {
      $gte: new Date(currentYear, 0, 1),
      $lte: new Date(currentYear, 11, 31),
    };
  } else if (time === 'quarterly') {
    groupId = {
      year: { $year: '$sales.salesAt' },
      quarter: { $ceil: { $divide: [{ $month: '$sales.salesAt' }, 3] } },
    };
    periodFormatter = (d) => `Q${d.quarter}-${String(d.year).slice(-2)}`;
    match['sales.salesAt'] = {
      $gte: new Date(currentYear, 0, 1),
      $lte: new Date(currentYear, 11, 31),
    };
  } else {
    groupId = { year: { $year: '$sales.salesAt' } };
    periodFormatter = (d) => `${d.year}`;
    match['sales.salesAt'] = {
      $gte: new Date(currentYear - 3, 0, 1),
      $lte: new Date(currentYear, 11, 31),
    };
  }

  // 2️⃣ Aggregate from Mongo
  const results = await Customer.aggregate<{
    _id: { year: number; month?: number; quarter?: number };
    sellDeals: number;
    rentDeals: number;
    leaseDeals: number;
    preLeaseDeals: number;
    totalDeals: number;
    totalValue: number;
  }>([
    { $match: match },
    { $unwind: '$sales' },
    {
      $lookup: {
        from: 'individualproperties',
        localField: 'sales.property',
        foreignField: '_id',
        as: 'property',
      },
    },
    { $unwind: '$property' },
    {
      $group: {
        _id: groupId,
        sellDeals: {
          $sum: { $cond: [{ $eq: ['$property.listingType', 'sell'] }, 1, 0] },
        },
        rentDeals: {
          $sum: { $cond: [{ $eq: ['$property.listingType', 'rent'] }, 1, 0] },
        },
        leaseDeals: {
          $sum: { $cond: [{ $eq: ['$property.listingType', 'lease'] }, 1, 0] },
        },
        preLeaseDeals: {
          $sum: {
            $cond: [{ $eq: ['$property.listingType', 'preLeased'] }, 1, 0],
          },
        },
        totalDeals: { $sum: 1 },
        totalValue: { $sum: { $ifNull: ['$property.price', 0] } },
      },
    },
    { $sort: { '_id.year': 1, '_id.month': 1, '_id.quarter': 1 } },
  ]);

  // 3️⃣ Generate full timeline (fill missing periods)
  const periods: { year: number; month?: number; quarter?: number }[] = [];
  if (time === 'monthly')
    for (let m = 1; m <= now.getMonth() + 1; m++)
      periods.push({ year: currentYear, month: m });
  else if (time === 'quarterly')
    for (let q = 1; q <= 4; q++)
      periods.push({ year: currentYear, quarter: q });
  else
    for (let y = currentYear - 3; y <= currentYear; y++)
      periods.push({ year: y });

  // 4️⃣ First pass: build raw data (no growth yet)
  const formatted: SalesComparisonResult[] = periods.map((p, idx) => {
    const found = results.find(
      (r) =>
        r._id.year === p.year &&
        (p.month ? r._id.month === p.month : true) &&
        (p.quarter ? r._id.quarter === p.quarter : true),
    );

    const totalDeals = found?.totalDeals ?? 0;
    const totalValue = found?.totalValue ?? 0;
    const avgDealValue =
      totalDeals > 0 ? Math.round(totalValue / totalDeals) : 0;

    return {
      id: `${time === 'monthly' ? 'M' : time === 'quarterly' ? 'Q' : 'Y'}-${String(
        idx + 1,
      ).padStart(3, '0')}`,
      period: periodFormatter(p),
      sellDeals: found?.sellDeals ?? 0,
      rentDeals: found?.rentDeals ?? 0,
      leaseDeals: found?.leaseDeals ?? 0,
      preLeaseDeals: found?.preLeaseDeals ?? 0,
      totalDeals,
      totalValue,
      avgDealValue,
      growthPercentage: null, // filled later
    };
  });

  // 5️⃣ Second pass: calculate growth %
  for (let i = 1; i < formatted.length; i++) {
    const prev = formatted[i - 1];
    const curr = formatted[i];

    if (prev.totalDeals > 0)
      curr.growthPercentage = Math.round(
        ((curr.totalDeals - prev.totalDeals) / prev.totalDeals) * 100,
      );
    else
      curr.growthPercentage =
        prev.totalDeals === 0 && curr.totalDeals > 0 ? 100 : null;
  }

  return formatted;
};

export const salesSummary = async ({
  companyId,
  filter,
  options,
}: {
  companyId: string | Types.ObjectId;
  filter: Record<string, string>;
  options: PaginateOptions;
}): Promise<SalesSummaryReport> => {
  const {
    startDate,
    endDate,
    projectId,
    propertyId,
    sourceId,
    source,
    view,
  } = filter;

  const company = await Company.findById(companyId)
    .select('companyType')
    .lean();
  if (!company) throw new Error('Invalid company or user type mismatch');
  const isBrokerCompany = company.companyType === CompanyType.BROKER;
  const reportView = getSalesSummaryReportView(view);
  const shouldLoadOverview = reportView !== 'detail';
  const shouldLoadDetail = reportView !== 'overview';

  const selectedEntityIds = parseObjectIds(
    isBrokerCompany ? propertyId || projectId : projectId || propertyId,
  );
  const selectedSourceIds = parseObjectIds(sourceId || source);
  const basePipeline = buildSalesSummaryBasePipeline({
    companyId,
    isBrokerCompany,
    startDate,
    endDate,
    selectedEntityIds,
    selectedSourceIds,
  });

  const [
    analyticsResult,
    pieChartData,
    barChartData,
    lineChartData,
    detailedListRaw,
  ] = await Promise.all([
    shouldLoadOverview
      ? UnitBookingOrHold.aggregate(buildSalesSummaryAnalyticsPipeline(basePipeline))
      : Promise.resolve([]),
    shouldLoadOverview
      ? UnitBookingOrHold.aggregate(buildSalesSummaryPieChartPipeline(basePipeline))
      : Promise.resolve([]),
    shouldLoadOverview
      ? UnitBookingOrHold.aggregate(buildSalesSummaryBarChartPipeline(basePipeline))
      : Promise.resolve([]),
    shouldLoadOverview
      ? UnitBookingOrHold.aggregate(buildSalesSummaryLineChartPipeline(basePipeline))
      : Promise.resolve([]),
    shouldLoadDetail
      ? UnitBookingOrHold.paginate(
          {},
          {
            ...options,
            aggregation: buildSalesSummaryDetailedListPipeline(basePipeline),
          },
        )
      : Promise.resolve(getEmptySalesSummaryDetailedList(options)),
  ]);

  const analyticsData = shouldLoadOverview
    ? (analyticsResult as Array<{
        totalSalesValue?: number;
        amountCollected?: number;
        totalBookedUnits?: number;
      }>)[0] || {
        totalSalesValue: 0,
        amountCollected: 0,
        totalBookedUnits: 0,
      }
    : {
        totalSalesValue: 0,
        amountCollected: 0,
        totalBookedUnits: 0,
      };

  const analytics = {
    totalSalesValue: Math.round((analyticsData.totalSalesValue || 0) * 100) / 100,
    amountCollected: Math.round((analyticsData.amountCollected || 0) * 100) / 100,
    collectionRate:
      analyticsData.totalSalesValue && analyticsData.totalSalesValue > 0
        ? Math.round(
            ((analyticsData.amountCollected || 0) /
              analyticsData.totalSalesValue) *
              10000,
          ) / 100
        : 0,
    averageSalesValue:
      analyticsData.totalBookedUnits && analyticsData.totalBookedUnits > 0
        ? Math.round(
            ((analyticsData.totalSalesValue || 0) /
              analyticsData.totalBookedUnits) *
              100,
          ) / 100
        : 0,
    totalSoldUnits: analyticsData.totalSoldUnits || 0,
  };

  const companyObjectId = getObjectId(String(companyId));

  const [entityOptions, sourceOptions] = await Promise.all([
    isBrokerCompany
      ? IndividualProperties.find({
          companyId: companyObjectId,
          isDeleted: { $ne: true },
        })
          .select('_id title')
          .sort({ title: 1 })
          .lean()
      : Project.find({ companyId: companyObjectId })
          .select('_id projectName')
          .sort({ projectName: 1 })
          .lean(),
    Source.find({ company: companyObjectId })
      .select('_id name')
      .sort({ name: 1 })
      .lean(),
  ]);

  return {
    analytics,
    charts: {
      pieChart: shouldLoadOverview
        ? (pieChartData as Array<{
            _id?: Types.ObjectId;
            name?: string;
            value?: number;
          }>).map((item) => ({
            id: item._id?.toString() || '',
            name: item.name || 'Unknown',
            value: item.value || 0,
          }))
        : [],
      barChart: shouldLoadOverview
        ? (barChartData as Array<{
            _id?: Types.ObjectId;
            source?: string;
            value?: number;
          }>).map((item) => ({
            id: item._id?.toString() || '',
            source: item.source || 'Unknown',
            value: item.value || 0,
          }))
        : [],
      lineChart: shouldLoadOverview
        ? (lineChartData as Array<{
            month: string;
            sales?: number;
            count?: number;
          }>).map((item) => ({
            month: item.month,
            sales: item.sales || 0,
            count: item.count || 0,
          }))
        : [],
    },
    filterOptions: {
      entities: entityOptions.map((entity) => ({
        id: entity._id.toString(),
        name: isBrokerCompany
          ? (entity as { title?: string }).title || 'Untitled Property'
          : (entity as { projectName?: string }).projectName ||
            'Untitled Project',
      })),
      sources: sourceOptions.map((src) => ({
        id: src._id.toString(),
        name: src.name || 'Unknown',
      })),
    },
    detailedList: detailedListRaw || getEmptySalesSummaryDetailedList(options),
  };
};