import { PipelineStage, Types } from 'mongoose';
import { Lead } from '../lead/lead.model';
import { Tasks } from '../tasks/tasks.model';
import { Customer } from '../customer/customer.model';
import { Contact } from '../contacts/contacts.model';
import dayjs from 'dayjs';
import { getObjectId } from '@/shared/utils/commonHelper';
import { Team } from '../teams/teams.model';
import { UnitBookingOrHold } from '../activity/unitBookingOrHold/unitBookingOrHold.model';
import { CustomerPayment } from '../customer/payment/payment.model';
import { Project, Unit } from '../project';
import { LeadStage } from '../master/leadStage/leadStage.model';
import { ActivityType, Status } from '@/shared/constants/enum.constant';
import User from '../user/user.model';
import { Activity } from '../activity/activity.model';

type ActivityStats = {
  pending: number;
  completed: number;
  total: number;
};

type NewActivityStats = {
  due: number;
  overdue: number;
  completed: number;
};

interface UserActivitySummary {
  name: string;
  activities: {
    call: NewActivityStats;
    meetings: NewActivityStats;
    sitevisit: NewActivityStats;
  };
  totalActivitiesAssigned: number;
}

interface TeamActivitySummary {
  [teamName: string]: Array<{ [userName: string]: { activities: any } }>;
}

interface TeamActivitySummaryResult {
  teamName: string;
  users: Array<{
    [userName: string]: {
      activities: {
        call: { due: number; overdue: number; completed: number };
        meetings: { due: number; overdue: number; completed: number };
        sitevisit: { due: number; overdue: number; completed: number };
        bookings: { due: number; overdue: number; completed: number };
      };
      roleName: string;
    };
  }>;
  total: {
    call: number;
    meetings: number;
    sitevisit: number;
    booking: number;
  };
}

/**
 * Builds aggregation pipeline for calculating activity counts
 *
 * @param filter - The MongoDB match filter
 * @returns PipelineStage[] - Aggregation pipeline
 */
export const buildActivityCountPipeline = (
  filter: Record<string, unknown>,
  todayDate: String,
): PipelineStage[] => [
  {
    $match: {
      ...filter,
      status: {
        $in: ['pending', 'overdue'],
      },
      $expr: {
        $eq: [
          { $dateToString: { format: '%Y-%m-%d', date: '$activityDate' } },
          todayDate,
        ],
      },
    },
  },
  {
    $group: {
      _id: '$activityType',
      count: { $sum: 1 },
    },
  },
  {
    $project: {
      _id: 0,
      type: '$_id',
      count: 1,
    },
  },
];

/**
 * Builds aggregation pipeline for lead stage counts per company with overall lead count
 * plus month-over-month change (absolute and labeled)
 *
 * @param companyId - Company to fetch lead stages for
 * @param userType - User role ("admin" or other)
 * @param userId - User's ObjectId (required if userType !== "admin")
 * @param rules - Optional permissions object (e.g., { see_all_leads: boolean })
 * @returns PipelineStage[] - Aggregation pipeline
 */
export const buildLeadStageCountPipeline = (
  companyId: Types.ObjectId,
  userType: string,
  userId?: Types.ObjectId,
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  rules?: any,
): PipelineStage[] => {
  const now = new Date();
  const startOfCurrentMonth = new Date(now.getFullYear(), now.getMonth(), 1);
  const startOfPreviousMonth = new Date(
    now.getFullYear(),
    now.getMonth() - 1,
    1,
  );
  const endOfPreviousMonth = new Date(now.getFullYear(), now.getMonth(), 0); // last day of prev month

  // --- Determine lead visibility filter ---
  const baseConditions: Record<string, unknown>[] = [
    { $eq: ['$leadStage', '$$stageId'] },
    { $eq: ['$company', companyId] },
  ];

  // Non-admins have conditional access
  if (userType !== 'admin')
    if (!rules?.see_all_leads)
      baseConditions.push({ $eq: ['$assignedTo', userId] });

  // Match active lead stages for company
  const leadStageMatch: PipelineStage.Match = {
    $match: {
      company: companyId,
      isActive: true,
    },
  };

  // Lookup all leads for stage (overall count)
  const overallLeadLookup: PipelineStage.Lookup = {
    $lookup: {
      from: 'leads',
      let: { stageId: '$_id' },
      pipeline: [
        {
          $match: {
            $expr: { $and: baseConditions },
          },
        },
      ],
      as: 'allLeads',
    },
  };

  // Lookup leads created in current month
  const currentMonthLookup: PipelineStage.Lookup = {
    $lookup: {
      from: 'leads',
      let: { stageId: '$_id' },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                ...baseConditions,
                { $gte: ['$createdAt', startOfCurrentMonth] },
              ],
            },
          },
        },
      ],
      as: 'currentMonthLeads',
    },
  };

  // Lookup leads created in previous month
  const previousMonthLookup: PipelineStage.Lookup = {
    $lookup: {
      from: 'leads',
      let: { stageId: '$_id' },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                ...baseConditions,
                { $gte: ['$createdAt', startOfPreviousMonth] },
                { $lte: ['$createdAt', endOfPreviousMonth] },
              ],
            },
          },
        },
      ],
      as: 'previousMonthLeads',
    },
  };

  const sortByPosition: PipelineStage.Sort = {
    $sort: { position: 1 },
  };

  // Project final output with counts and change type
  const projectCount: PipelineStage.Project = {
    $project: {
      _id: 0,
      stageName: 1,
      position: 1,
      leadCount: { $size: '$allLeads' },
      absoluteChange: {
        $abs: {
          $subtract: [
            { $size: '$currentMonthLeads' },
            { $size: '$previousMonthLeads' },
          ],
        },
      },
      changeType: {
        $cond: [
          {
            $gt: [
              {
                $subtract: [
                  { $size: '$currentMonthLeads' },
                  { $size: '$previousMonthLeads' },
                ],
              },
              0,
            ],
          },
          'increase',
          {
            $cond: [
              {
                $lt: [
                  {
                    $subtract: [
                      { $size: '$currentMonthLeads' },
                      { $size: '$previousMonthLeads' },
                    ],
                  },
                  0,
                ],
              },
              'decrease',
              'no change',
            ],
          },
        ],
      },
    },
  };

  return [
    leadStageMatch,
    overallLeadLookup,
    currentMonthLookup,
    previousMonthLookup,
    sortByPosition,
    projectCount,
  ];
};

/**
 * Aggregation pipeline to get property counts grouped by category with category name
 * Month-over-month absolute change and increase/decrease label with user-based filters
 *
 * @param companyId - Company to filter properties for
 * @param userType - 'admin' or other role
 * @param userId - User's ObjectId (if not admin)
 * @returns PipelineStage[] - Aggregation pipeline
 */
export const buildPropertyCategoryCountPipeline = (
  companyId: Types.ObjectId,
  userType: string,
  userId?: Types.ObjectId,
): PipelineStage[] => {
  const now = new Date();
  const startOfCurrentMonth = new Date(now.getFullYear(), now.getMonth(), 1);
  const startOfPreviousMonth = new Date(
    now.getFullYear(),
    now.getMonth() - 1,
    1,
  );
  const endOfPreviousMonth = new Date(now.getFullYear(), now.getMonth(), 0);

  // Base filter
  const matchFilter: Record<string, unknown> = {
    companyId,
  };
  if (userType !== 'admin' && userId) matchFilter['createdBy'] = userId;

  return [
    { $match: matchFilter },

    // Group by category ObjectId
    {
      $group: {
        _id: '$propertyType', // grouping by referenced category Id
        propertiesCount: { $sum: 1 },
      },
    },

    // Lookup for the category document to get the category name
    {
      $lookup: {
        from: 'categories', // collection name for category schema
        localField: '_id',
        foreignField: '_id',
        as: 'categoryInfo',
      },
    },

    // Unwind the categoryInfo array
    { $unwind: { path: '$categoryInfo', preserveNullAndEmptyArrays: true } },

    // Lookup current month properties per category Id
    {
      $lookup: {
        from: 'individualProperties',
        let: { categoryId: '$_id' },
        pipeline: [
          { $match: matchFilter },
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ['$propertyType', '$$categoryId'] },
                  { $gte: ['$createdAt', startOfCurrentMonth] },
                ],
              },
            },
          },
        ],
        as: 'currentMonthProperties',
      },
    },

    // Lookup previous month properties per category Id
    {
      $lookup: {
        from: 'individualProperties',
        let: { categoryId: '$_id' },
        pipeline: [
          { $match: matchFilter },
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ['$propertyType', '$$categoryId'] },
                  { $gte: ['$createdAt', startOfPreviousMonth] },
                  { $lte: ['$createdAt', endOfPreviousMonth] },
                ],
              },
            },
          },
        ],
        as: 'previousMonthProperties',
      },
    },

    // Project desired fields
    {
      $project: {
        _id: 0,
        categoryName: '$categoryInfo.name',
        propertiesCount: 1,
        absoluteChange: {
          $abs: {
            $subtract: [
              { $size: '$currentMonthProperties' },
              { $size: '$previousMonthProperties' },
            ],
          },
        },
        changeType: {
          $cond: [
            {
              $gt: [
                {
                  $subtract: [
                    { $size: '$currentMonthProperties' },
                    { $size: '$previousMonthProperties' },
                  ],
                },
                0,
              ],
            },
            'increase',
            {
              $cond: [
                {
                  $lt: [
                    {
                      $subtract: [
                        { $size: '$currentMonthProperties' },
                        { $size: '$previousMonthProperties' },
                      ],
                    },
                    0,
                  ],
                },
                'decrease',
                'no change',
              ],
            },
          ],
        },
      },
    },
  ];
};

/**
 * Builds aggregation pipeline to get lead counts by interestType for a company with month-over-month changes
 * Filter differs for admin vs non-admin user
 *
 * @param companyId - Company ObjectId to filter leads
 * @param userType - 'admin' or other
 * @param userId - User ObjectId (for non-admin filter)
 * @returns PipelineStage[] - Aggregation pipeline
 */
export const buildLeadInterestTypeCountPipeline = (
  companyId: Types.ObjectId,
  userType: string,
  userId?: Types.ObjectId,
): PipelineStage[] => {
  const now = new Date();
  const startOfCurrentMonth = new Date(now.getFullYear(), now.getMonth(), 1);
  const startOfPreviousMonth = new Date(
    now.getFullYear(),
    now.getMonth() - 1,
    1,
  );
  const endOfPreviousMonth = new Date(now.getFullYear(), now.getMonth(), 0);

  // Filter base for aggregation, varying by admin vs non-admin
  const baseFilter: Record<string, unknown> =
    userType === 'admin'
      ? { company: companyId }
      : { company: companyId, createdBy: userId };

  return [
    { $match: baseFilter },

    // Group by interestType: total count of leads per interest type
    {
      $group: {
        _id: '$interestType',
        totalCount: { $sum: 1 },
      },
    },

    // Lookup current month leads count per interestType (filtered)
    {
      $lookup: {
        from: 'leads',
        let: { interestType: '$_id' },
        pipeline: [
          { $match: baseFilter },
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ['$interestType', '$$interestType'] },
                  { $gte: ['$createdAt', startOfCurrentMonth] },
                ],
              },
            },
          },
        ],
        as: 'currentMonthLeads',
      },
    },

    // Lookup previous month leads count per interestType (filtered)
    {
      $lookup: {
        from: 'leads',
        let: { interestType: '$_id' },
        pipeline: [
          { $match: baseFilter },
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ['$interestType', '$$interestType'] },
                  { $gte: ['$createdAt', startOfPreviousMonth] },
                  { $lte: ['$createdAt', endOfPreviousMonth] },
                ],
              },
            },
          },
        ],
        as: 'previousMonthLeads',
      },
    },

    // Project desired fields: interestType, overall counts, absolute change, change type label
    {
      $project: {
        _id: 0,
        interestType: '$_id',
        totalCount: 1,
        absoluteChange: {
          $abs: {
            $subtract: [
              { $size: '$currentMonthLeads' },
              { $size: '$previousMonthLeads' },
            ],
          },
        },
        changeType: {
          $cond: [
            {
              $gt: [
                {
                  $subtract: [
                    { $size: '$currentMonthLeads' },
                    { $size: '$previousMonthLeads' },
                  ],
                },
                0,
              ],
            },
            'increase',
            {
              $cond: [
                {
                  $lt: [
                    {
                      $subtract: [
                        { $size: '$currentMonthLeads' },
                        { $size: '$previousMonthLeads' },
                      ],
                    },
                    0,
                  ],
                },
                'decrease',
                'no change',
              ],
            },
          ],
        },
      },
    },
  ];
};

/**
 * Get CRM performance metrics for a company
 */
export async function getSalesPerformanceMetrics(
  companyId: Types.ObjectId,
  userType: string,
  brokerId?: Types.ObjectId,
  rules?: any,
) {
  const companyIdFilter = { companyId: companyId };
  const createdByFilter = brokerId ? { createdBy: brokerId } : {};

  const taskFilter =
    userType === 'admin'
      ? companyIdFilter
      : { ...companyIdFilter, ...createdByFilter };

  let totalContacts: number;
  let contactsWithLeads: Types.ObjectId[];
  let totalLeads: number;
  let leadsWithCustomers: Types.ObjectId[];
  let baseFilter: Record<string, unknown>;

  const getBrokerVisibleContactIds = async (): Promise<Types.ObjectId[]> => {
    if (!brokerId) return [];
    const userId = brokerId;
    const userCreatedContacts = await Contact.find(
      { createdBy: userId, company: companyId },
      { _id: 1 },
    ).lean();
    const userCreatedContactIds = userCreatedContacts.map((c) => c._id);

    const leadsAssignedToUser = await Lead.find(
      {
        assignedTo: userId,
        company: companyId,
        contact: { $exists: true, $ne: null },
      },
      { contact: 1 },
    ).lean();
    const assignedLeadContactIds = leadsAssignedToUser
      .filter((l) => l.contact)
      .map((l) => l.contact as Types.ObjectId);

    return [...new Set([...userCreatedContactIds, ...assignedLeadContactIds])];
  };

  let visibleContactIds: Types.ObjectId[] | undefined;

  if (userType === 'admin' || rules?.see_all_contacts) {
    // Admin or user with see_all_contacts can see all contacts
    baseFilter = { company: companyId };
    totalContacts = await Contact.countDocuments(baseFilter);
    contactsWithLeads = await Lead.distinct('contact', {
      ...baseFilter,
      contact: { $exists: true, $ne: null },
    });
    totalLeads = await Lead.countDocuments(baseFilter);
  } else {
    visibleContactIds = await getBrokerVisibleContactIds();

    totalContacts = visibleContactIds.length;

    const leadsWithVisibleContacts = await Lead.find(
      { contact: { $in: visibleContactIds }, company: companyId },
      { contact: 1 },
    ).lean();
    contactsWithLeads = leadsWithVisibleContacts
      .filter((l) => l.contact)
      .map((l) => l.contact as Types.ObjectId);

    totalLeads = await Lead.countDocuments({
      $or: [
        { createdBy: brokerId, company: companyId },
        { assignedTo: brokerId, company: companyId },
      ],
    });

    baseFilter = { company: companyId };
  }

  if (userType === 'admin' || rules?.see_all_customers) {
    // Admin or user with see_all_customers — same scope as company-wide customers
    leadsWithCustomers = await Customer.distinct('contactId', {
      company: companyId,
      contactId: { $exists: true, $ne: null },
    });
  } else {
    const contactIdsForCustomers =
      visibleContactIds ?? (await getBrokerVisibleContactIds());

    const customersWithVisibleContacts = await Customer.find(
      { 
        contactId: { $in: contactIdsForCustomers }, 
        company: companyId,
      },
      { contactId: 1 },
    ).lean();
    leadsWithCustomers = customersWithVisibleContacts
      .filter((c) => c.contactId)
      .map((c) => c.contactId as Types.ObjectId);
  }

  const convertedContacts = contactsWithLeads.length;
  const contactsToLeadsPercent =
    totalContacts > 0 ? (convertedContacts / totalContacts) * 100 : 0;

  const convertedLeads = leadsWithCustomers.length;
  const leadsToCustomersPercent =
    totalLeads > 0 ? (convertedLeads / totalLeads) * 100 : 0;

  // 3. Task Completion %
  const totalTasks = await Tasks.countDocuments(taskFilter);
  const completedTasks = await Tasks.countDocuments({
    ...taskFilter,
    status: 'completed',
  });

  const taskCompletionPercent =
    totalTasks > 0 ? (completedTasks / totalTasks) * 100 : 0;

  // 4. Avg Days to Close (Lead Won or Lead Lost)
  const closedLeads = await Lead.aggregate([
    {
      $match: { ...baseFilter, leadStage: { $in: ['Lead Won', 'Lead Lost'] } },
    },
    {
      $project: {
        createdAt: 1,
        updatedAt: 1,
        diffDays: {
          $divide: [
            { $subtract: ['$updatedAt', '$createdAt'] },
            1000 * 60 * 60 * 24, // ms → days
          ],
        },
      },
    },
    {
      $group: {
        _id: null,
        avgDays: { $avg: '$diffDays' },
      },
    },
  ]);

  const avgDaysToClose = closedLeads[0]?.avgDays || 0;

  return {
    contactsToLeadsPercent,
    leadsToCustomersPercent,
    taskCompletionPercent,
    avgDaysToClose,
  };
}

export const getPersonalTargetPipeline = ({
  companyId,
  memberId,
}: {
  companyId: Types.ObjectId;
  memberId: Types.ObjectId;
}) => {
  const now = dayjs(); // current date
  const currentMonth = now.month() + 1; // dayjs months are 0-indexed
  const currentYear = now.year();

  return [
    {
      $match: {
        companyId: getObjectId(companyId),
        member: getObjectId(memberId),
        'period.month': currentMonth,
        'period.year': currentYear,
      },
    },
    {
      $group: {
        _id: null,
        salesAmountTarget: { $sum: '$salesAmount.target' },
        salesAmountAchieved: { $sum: '$salesAmount.achieved' },
        unitsSoldTarget: { $sum: '$unitsSold.target' },
        unitsSoldAchieved: { $sum: '$unitsSold.achieved' },
        meetingsTarget: { $sum: '$meetings.target' },
        meetingsAchieved: { $sum: '$meetings.achieved' },
        siteVisitsTarget: { $sum: '$siteVisits.target' },
        siteVisitsAchieved: { $sum: '$siteVisits.achieved' },
        callsTarget: { $sum: '$calls.target' },
        callsAchieved: { $sum: '$calls.achieved' },
      },
    },
    {
      $project: {
        _id: 0,
        salesAmount: {
          target: '$salesAmountTarget',
          achieved: '$salesAmountAchieved',
        },
        unitsSold: {
          target: '$unitsSoldTarget',
          achieved: '$unitsSoldAchieved',
        },
        meetings: {
          target: '$meetingsTarget',
          achieved: '$meetingsAchieved',
        },
        siteVisits: {
          target: '$siteVisitsTarget',
          achieved: '$siteVisitsAchieved',
        },
        calls: {
          target: '$callsTarget',
          achieved: '$callsAchieved',
        },
      },
    },
  ];
};

export const getBrokerStats = async (
  userType: string,
  companyId: Types.ObjectId,
  brokerId?: Types.ObjectId,
) => {
  // Apply common filter
  const filter =
    userType === 'admin'
      ? { company: companyId }
      : { company: companyId, createdBy: brokerId };

  const taskFilter =
    userType === 'admin' ? { companyId } : { companyId, createdBy: brokerId };

  /** ---------- 1. Active Leads Count ---------- */
  const activeLeadsCount = await Lead.countDocuments({
    ...filter,
  }).populate({
    path: 'leadStage',
    match: { stageName: { $nin: [/lead won/i, /lead lost/i] } },
  });

  /** ---------- 2. Hot Leads Count (leadScore > 70) ---------- */
  const hotLeadsCount = await Lead.countDocuments({
    ...filter,
    leadScore: { $gte: 70 },
  });

  /** ---------- 3. Tasks Due Today Count ---------- */
  const today = new Date();
  const startOfDay = new Date(today.setHours(0, 0, 0, 0));
  const endOfDay = new Date(today.setHours(23, 59, 59, 999));

  const tasksDueTodayCount = await Tasks.countDocuments({
    ...taskFilter,
    activityDate: { $gte: startOfDay, $lte: endOfDay },
  });

  /** ---------- 4. Leads Won This Month ---------- */
  const startOfMonth = new Date(
    new Date().getFullYear(),
    new Date().getMonth(),
    1,
  );
  const endOfMonth = new Date(
    new Date().getFullYear(),
    new Date().getMonth() + 1,
    0,
  );

  const leadsWonThisMonthCount = await Lead.countDocuments({
    ...filter,
    updatedAt: { $gte: startOfMonth, $lte: endOfMonth },
  }).populate({
    path: 'leadStage',
    match: { stageName: { $in: [/lead won/i, /lead lost/i] } },
  });

  return {
    activeLeadsCount,
    hotLeadsCount,
    tasksDueTodayCount,
    leadsWonThisMonthCount,
  };
};

export const getTeamActivitySummaryReport = async (
  userId: Types.ObjectId,
  companyId: Types.ObjectId,
  userType: string,
  filters?: {
    projectId?: string;
    startDate?: Date;
    endDate?: Date;
    source?: Types.ObjectId | { $in: Types.ObjectId[] };
  },
): Promise<TeamActivitySummaryResult[] | null> => {
  const companyObjectId = getObjectId(companyId);
  const userObjectId = getObjectId(userId);

  const teamMatch: any = { companyId: companyObjectId };

  if (userType !== 'admin') {
    teamMatch.lead = userObjectId;
  }

  const memberIdsResult = await Team.aggregate([
    { $match: teamMatch },
    {
      $group: {
        _id: null,
        leads: { $addToSet: { $ifNull: ['$lead', null] } },
        members: { $push: '$members' },
      },
    },
    {
      $project: {
        memberIds: {
          $reduce: {
            input: '$members',
            initialValue: '$leads',
            in: { $setUnion: ['$$value', '$$this'] },
          },
        },
      },
    },
    {
      $project: {
        memberIds: { $setDifference: ['$memberIds', [null]] },
      },
    },
  ]);

  let memberIds: Types.ObjectId[] = memberIdsResult[0]?.memberIds ?? [];

  if (userType !== 'admin') {
    const selfIncluded = memberIds.some((id: Types.ObjectId) =>
      id.equals(userObjectId),
    );
    if (!selfIncluded) {
      memberIds = [userObjectId, ...memberIds];
    }
  }

  if (!memberIds.length) return null;

  /* All teams that any of memberIds belong to (as lead or member), so each row shows full team list */
  const allTeamMembersMatch: any = {
    companyId: companyObjectId,
    $or: [{ lead: { $in: memberIds } }, { members: { $in: memberIds } }],
  };

  const allTeamMembers = await Team.aggregate([
    { $match: allTeamMembersMatch },
    {
      $project: {
        name: 1,
        lead: 1,
        members: 1,
      },
    },
    {
      $facet: {
        leads: [
          { $match: { lead: { $ne: null } } },
          { $project: { userId: '$lead', teamName: '$name' } },
        ],
        members: [
          { $unwind: { path: '$members', preserveNullAndEmptyArrays: false } },
          { $match: { members: { $ne: null } } },
          { $project: { userId: '$members', teamName: '$name' } },
        ],
      },
    },
    {
      $project: {
        combined: { $concatArrays: ['$leads', '$members'] },
      },
    },
    { $unwind: '$combined' },
    { $replaceRoot: { newRoot: '$combined' } },
    {
      $group: {
        _id: '$userId',
        teams: { $addToSet: '$teamName' },
      },
    },
  ]);

  // Get user details for all team members
  const userDetails = await User.aggregate([
    {
      $match: {
        _id: { $in: memberIds },
        'company.id': companyObjectId,
        status: Status.ACTIVE,
      },
    },
    {
      $lookup: {
        from: 'roles',
        let: { roleIds: '$company.role' },
        pipeline: [
          { $match: { $expr: { $in: ['$_id', '$$roleIds'] } } },
          { $project: { _id: 0, name: 1 } },
        ],
        as: 'companyRoles',
      },
    },
    {
      $project: {
        firstName: 1,
        lastName: 1,
        roleName: { $arrayElemAt: ['$companyRoles.name', 0] },
      },
    },
  ]);

  // Create a map of user details by ID
  const userDetailsMap = new Map<
    string,
    {
      firstName: string;
      lastName: string;
      _id: Types.ObjectId;
      roleName: string;
    }
  >();
  userDetails.forEach((user: any) => {
    userDetailsMap.set(user._id.toString(), user);
  });

  // Create member data for all team members
  const allMembersData = memberIds.map((memberId: Types.ObjectId) => {
    const userDetail = userDetailsMap.get(memberId.toString());
    const memberTeams =
      allTeamMembers
        .filter((m: any) => m._id?.toString() === memberId.toString())
        .map((m: any) => m.teams)
        .flat() || [];

    const name = userDetail
      ? `${userDetail.firstName || ''} ${userDetail.lastName || ''}`.trim()
      : 'Unknown';

    return {
      _id: memberId,
      name: name,
      roleName: userDetail?.roleName || 'unknown',
      teams:
        memberTeams.length > 0
          ? memberTeams.map((teamName: string) => ({
              teamName,
              teamRole: 'member',
            }))
          : [{ teamName: 'No Team', teamRole: 'member' }],
      activities: {
        call: { due: 0, overdue: 0, completed: 0 },
        meetings: { due: 0, overdue: 0, completed: 0 },
        sitevisit: { due: 0, overdue: 0, completed: 0 },
      },
      totalActivitiesAssigned: 0,
    };
  });

  const taskMatch: Record<string, unknown> = {
    assignedTo: { $in: memberIds },
    companyId: companyObjectId,
    activityType: { $in: ['call', 'meeting', 'siteVisit'] },
  };

  if (filters?.projectId) {
    taskMatch.project = getObjectId(filters.projectId);
  }

  const today = new Date();

  const dateFilter: Record<string, unknown> = {};

  if (filters?.startDate || filters?.endDate) {
    const activityDate: Record<string, Date> = {};
    if (filters.startDate) {
      const start = new Date(filters.startDate);
      activityDate.$gte = start;
    }
    if (filters.endDate) {
      const end = new Date(filters.endDate);
      activityDate.$lte = end;
    }
    dateFilter.activityDate = activityDate;
  }

  const summaries = await Tasks.aggregate([
    {
      $match: {
        ...taskMatch,
        ...(Object.keys(dateFilter).length ? dateFilter : {}),
      },
    },
    {
      $lookup: {
        from: 'users',
        localField: 'assignedTo',
        foreignField: '_id',
        as: 'userDoc',
        pipeline: [
          { $match: { 'company.id': companyObjectId, status: Status.ACTIVE } },
          { $project: { firstName: 1, lastName: 1 } },
        ],
      },
    },
    { $unwind: { path: '$userDoc', preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: 'leads',
        localField: 'leadId',
        foreignField: '_id',
        as: 'leadDoc',
      },
    },
    { $unwind: { path: '$leadDoc', preserveNullAndEmptyArrays: true } },
    ...(filters?.source
      ? [
          {
            $match: { 'leadDoc.source': filters.source },
          },
        ]
      : []),
    {
      $lookup: {
        from: 'teams',
        let: { userId: '$assignedTo' },
        pipeline: [
          {
            $match: {
              companyId: companyObjectId,
              $expr: {
                $or: [
                  { $eq: ['$lead', '$$userId'] },
                  { $in: ['$$userId', '$members'] },
                ],
              },
            },
          },
          {
            $project: {
              name: 1,
              lead: 1,
              members: 1,
            },
          },
        ],
        as: 'teamDocs',
      },
    },
    {
      $group: {
        _id: '$userDoc._id',
        name: {
          $first: {
            $concat: [
              { $ifNull: ['$userDoc.firstName', 'Unknown'] },
              ' ',
              { $ifNull: ['$userDoc.lastName', ''] },
            ],
          },
        },

        teamDocs: { $first: '$teamDocs' },
        /* eslint-disable camelcase */

        call_due: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'call'] },
                  { $eq: ['$status', 'pending'] },
                  { $gte: ['$activityDate', today] },
                ],
              },
              1,
              0,
            ],
          },
        },
        call_overdue: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'call'] },
                  { $eq: ['$status', 'overdue'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        call_completed: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'call'] },
                  { $eq: ['$status', 'completed'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        meeting_due: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'meeting'] },
                  { $eq: ['$status', 'pending'] },
                  { $gte: ['$activityDate', today] },
                ],
              },
              1,
              0,
            ],
          },
        },
        meeting_overdue: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'meeting'] },
                  { $eq: ['$status', 'overdue'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        meeting_completed: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'meeting'] },
                  { $eq: ['$status', 'completed'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        siteVisit_due: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'siteVisit'] },
                  { $eq: ['$status', 'pending'] },
                  { $gte: ['$activityDate', today] },
                ],
              },
              1,
              0,
            ],
          },
        },
        siteVisit_overdue: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'siteVisit'] },
                  { $eq: ['$status', 'overdue'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        siteVisit_completed: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'siteVisit'] },
                  { $eq: ['$status', 'completed'] },
                ],
              },
              1,
              0,
            ],
          },
        },
      },
    },

    /* ================================
       COMPUTE TEAM ROLE PER TEAM
     ================================= */
    {
      $addFields: {
        teams: {
          $map: {
            input: { $ifNull: ['$teamDocs', []] },
            as: 'team',
            in: {
              teamName: '$$team.name',
              teamRole: {
                $cond: [{ $eq: ['$$team.lead', '$_id'] }, 'lead', 'member'],
              },
            },
          },
        },
      },
    },
    {
      $project: {
        _id: 1,
        name: { $trim: { input: '$name' } },

        teams: 1,

        activities: {
          call: {
            due: { $ifNull: ['$call_due', 0] },
            overdue: { $ifNull: ['$call_overdue', 0] },
            completed: { $ifNull: ['$call_completed', 0] },
          },
          meetings: {
            due: { $ifNull: ['$meeting_due', 0] },
            overdue: { $ifNull: ['$meeting_overdue', 0] },
            completed: { $ifNull: ['$meeting_completed', 0] },
          },
          sitevisit: {
            due: { $ifNull: ['$siteVisit_due', 0] },
            overdue: { $ifNull: ['$siteVisit_overdue', 0] },
            completed: { $ifNull: ['$siteVisit_completed', 0] },
          },
        },

        totalActivitiesAssigned: {
          $add: [
            { $add: ['$call_due', '$call_overdue', '$call_completed'] },
            {
              $add: ['$meeting_due', '$meeting_overdue', '$meeting_completed'],
            },
            {
              $add: [
                '$siteVisit_due',
                '$siteVisit_overdue',
                '$siteVisit_completed',
              ],
            },
          ],
        },
      },
    },

    { $sort: { totalActivitiesAssigned: -1 } },
  ]);

  /* ================================
     GET BOOKING COUNTS BY TEAM MEMBER
  ================================= */
  const bookingMatchCriteria: any = {
    soldBy: { $in: memberIds },
    action: { $in: ['book', 'hold'] }, // Include both bookings and holds
  };

  // Add date range filter for bookings
  if (filters?.startDate || filters?.endDate) {
    const bookingDateFilter: any = {};
    if (filters.startDate) bookingDateFilter.$gte = filters.startDate;
    if (filters.endDate) bookingDateFilter.$lte = filters.endDate;
    bookingMatchCriteria.createdAt = bookingDateFilter;
  }

  const bookingCounts = await UnitBookingOrHold.aggregate([
    { $match: bookingMatchCriteria },
    {
      $lookup: {
        from: 'leads',
        localField: 'lead',
        foreignField: '_id',
        as: 'leadDoc',
      },
    },
    { $unwind: { path: '$leadDoc', preserveNullAndEmptyArrays: true } },
    ...(filters?.source
      ? [
          {
            $match: { 'leadDoc.source': filters.source },
          },
        ]
      : []),
    {
      $lookup: {
        from: 'users',
        localField: 'soldBy',
        foreignField: '_id',
        as: 'userDoc',
        pipeline: [
          { $match: { 'company.id': companyObjectId, status: Status.ACTIVE } },
          { $project: { firstName: 1, lastName: 1 } },
        ],
      },
    },
    { $unwind: { path: '$userDoc', preserveNullAndEmptyArrays: true } },
    {
      $group: {
        _id: '$soldBy',
        name: {
          $first: {
            $concat: [
              { $ifNull: ['$userDoc.firstName', 'Unknown'] },
              ' ',
              { $ifNull: ['$userDoc.lastName', ''] },
            ],
          },
        },
        /* eslint-disable camelcase */
        booking_due: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$action', 'hold'] },
                  { $gte: ['$holdUntil', today] },
                ],
              },
              1,
              0,
            ],
          },
        },
        booking_overdue: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$action', 'hold'] },
                  { $lt: ['$holdUntil', today] },
                ],
              },
              1,
              0,
            ],
          },
        },
        booking_completed: {
          $sum: {
            $cond: [{ $eq: ['$action', 'book'] }, 1, 0],
          },
        },
      },
    },
    {
      $project: {
        _id: 0,
        userId: '$_id',
        name: { $trim: { input: '$name' } },
        bookings: {
          due: { $ifNull: ['$booking_due', 0] },
          overdue: { $ifNull: ['$booking_overdue', 0] },
          completed: { $ifNull: ['$booking_completed', 0] },
        },
      },
    },
  ]);

  // Create a map of summaries by user name for quick lookup
  const summaryMap = new Map<string, any>();
  summaries.forEach((user: any) => {
    summaryMap.set(user.name, user);
  });

  // Create a map of booking counts by user name
  const bookingMap = new Map<string, any>();
  bookingCounts.forEach((booking: any) => {
    bookingMap.set(booking.name, booking.bookings);
  });

  // Merge activity and booking data with all members
  const allMembersWithData = allMembersData.map((member) => {
    const summaryData = summaryMap.get(member.name);
    const bookingData = bookingMap.get(member.name) || {
      due: 0,
      overdue: 0,
      completed: 0,
    };

    return {
      ...member,
      activities: summaryData
        ? {
            ...summaryData.activities,
            bookings: bookingData,
          }
        : {
            call: { due: 0, overdue: 0, completed: 0 },
            meetings: { due: 0, overdue: 0, completed: 0 },
            sitevisit: { due: 0, overdue: 0, completed: 0 },
            bookings: bookingData,
          },
      totalActivitiesAssigned: summaryData?.totalActivitiesAssigned || 0,
    };
  });

  const teamGroups = new Map<string, any[]>();

  allMembersWithData.forEach((user: any) => {
    if (user.teams && user.teams.length > 0) {
      user.teams.forEach((team: any) => {
        const teamName = team.teamName || 'Unknown Team';
        if (!teamGroups.has(teamName)) {
          teamGroups.set(teamName, []);
        }
        const existing = teamGroups
          .get(teamName)
          .find((u: any) => Object.keys(u)[0] === user.name);
        if (!existing) {
          teamGroups.get(teamName).push({
            [user.name]: {
              activities: user.activities,
              roleName: user.roleName,
            },
          });
        }
      });
    } else {
      const teamName = 'No Team';
      if (!teamGroups.has(teamName)) {
        teamGroups.set(teamName, []);
      }
      const existing = teamGroups
        .get(teamName)
        .find((u: any) => Object.keys(u)[0] === user.name);
      if (!existing) {
        teamGroups.get(teamName).push({
          [user.name]: {
            activities: user.activities,
            roleName: user.roleName,
          },
        });
      }
    }
  });

  const callActivityMatch: Record<string, unknown> = {
    assignedTo: { $in: memberIds },
    type: ActivityType.CALL,
  };
  if (filters?.startDate || filters?.endDate) {
    const callActivityDateFilter: Record<string, Date> = {};
    if (filters.startDate) callActivityDateFilter.$gte = new Date(filters.startDate);
    if (filters.endDate) callActivityDateFilter.$lte = new Date(filters.endDate);
    callActivityMatch.createdAt = callActivityDateFilter;
  }

  if (filters?.source) {
    const sourceLeadIds = await Lead.distinct('_id', { source: filters.source, company: getObjectId(companyId), assignedTo: { $in: memberIds } });
    callActivityMatch.lead = { $in: sourceLeadIds };
  }

  const callActivityCounts = await Activity.find(callActivityMatch).select('assignedTo title').lean();
  // const bookingActivityCounts = await Activity.find({ assignedTo: { $in: memberIds }, type: ActivityType.BOOK }).select('assignedTo title').lean();

  for (const activity of callActivityCounts) {
    const member = allMembersWithData.find((summary: any) => summary._id.toString() === activity.assignedTo.toString());
    if (member) {
      member.activities.call.completed += 1;
    }
  }

  const result = Array.from(teamGroups.entries()).map(([teamName, users]) => {
    // Calculate totals for the team
    const totals = users.reduce(
      (acc, userObj) => {
        const userData = Object.values(userObj)[0] as any;
        const userActivities = userData?.activities;
        if (userActivities) {
          // Sum calls
          acc.call +=
            (userActivities.call?.due || 0) +
            (userActivities.call?.overdue || 0) +
            (userActivities.call?.completed || 0);
          // Sum meetings
          acc.meetings +=
            (userActivities.meetings?.due || 0) +
            (userActivities.meetings?.overdue || 0) +
            (userActivities.meetings?.completed || 0);
          // Sum site visits
          acc.sitevisit +=
            (userActivities.sitevisit?.due || 0) +
            (userActivities.sitevisit?.overdue || 0) +
            (userActivities.sitevisit?.completed || 0);
          // Sum bookings
          acc.booking +=
            (userActivities.bookings?.due || 0) +
            (userActivities.bookings?.overdue || 0) +
            (userActivities.bookings?.completed || 0);
        }
        return acc;
      },
      {
        call: 0,
        meetings: 0,
        sitevisit: 0,
        booking: 0,
      },
    );

    return {
      teamName,
      users,
      total: totals,
    };
  });

  return result.length ? result : null;
};

export const getTeamActivitySummary = async (
  userId: Types.ObjectId,
  companyId: Types.ObjectId,
  userType: string,
): Promise<UserActivitySummary[] | null> => {
  const companyObjectId = getObjectId(companyId);
  const userObjectId = getObjectId(userId);

  /* ================================
     STEP 1: GET ALL TEAM MEMBER IDS
  ================================= */
  const teamMatch: any = { companyId: companyObjectId };

  if (userType !== 'admin')
    teamMatch.$or = [{ lead: userObjectId }, { members: userObjectId }];

  const memberIdsResult = await Team.aggregate([
    { $match: teamMatch },
    {
      $group: {
        _id: null,
        leads: { $addToSet: { $ifNull: ['$lead', null] } },
        members: { $push: '$members' },
      },
    },
    {
      $project: {
        memberIds: {
          $reduce: {
            input: '$members',
            initialValue: '$leads',
            in: { $setUnion: ['$$value', '$$this'] },
          },
        },
      },
    },
    {
      $project: {
        memberIds: { $setDifference: ['$memberIds', [null]] },
      },
    },
  ]);

  if (!memberIdsResult.length || !memberIdsResult[0].memberIds?.length)
    return null;

  const memberIds = memberIdsResult[0].memberIds;

  /* ================================
     STEP 2: TASK AGGREGATION
  ================================= */
  const summaries = await Tasks.aggregate([
    {
      $match: {
        assignedTo: { $in: memberIds },
        companyId: companyObjectId,
        activityType: { $in: ['call', 'meeting', 'siteVisit'] },
      },
    },

    /* ================================
       USER LOOKUP
    ================================= */
    {
      $lookup: {
        from: 'users',
        localField: 'assignedTo',
        foreignField: '_id',
        as: 'userDoc',
        pipeline: [
          { $match: { 'company.id': companyObjectId, status: Status.ACTIVE } },
          { $project: { firstName: 1, lastName: 1 } },
        ],
      },
    },
    { $unwind: { path: '$userDoc', preserveNullAndEmptyArrays: true } },

    /* ================================
       TEAM LOOKUP (MULTI-TEAM)
    ================================= */
    {
      $lookup: {
        from: 'teams',
        let: { userId: '$assignedTo' },
        pipeline: [
          {
            $match: {
              companyId: companyObjectId,
              $expr: {
                $or: [
                  { $eq: ['$lead', '$$userId'] },
                  { $in: ['$$userId', '$members'] },
                ],
              },
            },
          },
          {
            $project: {
              name: 1,
              lead: 1,
              members: 1,
            },
          },
        ],
        as: 'teamDocs',
      },
    },

    /* ================================
       GROUP PER USER (UNCHANGED LOGIC)
    ================================= */
    {
      $group: {
        _id: '$userDoc._id',
        name: {
          $first: {
            $concat: [
              { $ifNull: ['$userDoc.firstName', 'Unknown'] },
              ' ',
              { $ifNull: ['$userDoc.lastName', ''] },
            ],
          },
        },

        teamDocs: { $first: '$teamDocs' },
        /* eslint-disable camelcase */

        call_pending: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'call'] },
                  { $eq: ['$status', 'pending'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        call_completed: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'call'] },
                  { $eq: ['$status', 'completed'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        meeting_pending: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'meeting'] },
                  { $eq: ['$status', 'pending'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        meeting_completed: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'meeting'] },
                  { $eq: ['$status', 'completed'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        siteVisit_pending: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'siteVisit'] },
                  { $eq: ['$status', 'pending'] },
                ],
              },
              1,
              0,
            ],
          },
        },
        siteVisit_completed: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $eq: ['$activityType', 'siteVisit'] },
                  { $eq: ['$status', 'completed'] },
                ],
              },
              1,
              0,
            ],
          },
        },
      },
    },

    /* ================================
       COMPUTE TEAM ROLE PER TEAM
    ================================= */
    {
      $addFields: {
        teams: {
          $map: {
            input: { $ifNull: ['$teamDocs', []] },
            as: 'team',
            in: {
              teamName: '$$team.name',
              teamRole: {
                $cond: [{ $eq: ['$$team.lead', '$_id'] }, 'lead', 'member'],
              },
            },
          },
        },
      },
    },

    /* ================================
       FINAL SHAPE (APPEND ONLY)
    ================================= */
    {
      $project: {
        _id: 0,
        name: { $trim: { input: '$name' } },

        teams: 1,

        activities: {
          call: {
            pending: { $ifNull: ['$call_pending', 0] },
            completed: { $ifNull: ['$call_completed', 0] },
            total: {
              $add: [
                { $ifNull: ['$call_pending', 0] },
                { $ifNull: ['$call_completed', 0] },
              ],
            },
          },
          meeting: {
            pending: { $ifNull: ['$meeting_pending', 0] },
            completed: { $ifNull: ['$meeting_completed', 0] },
            total: {
              $add: [
                { $ifNull: ['$meeting_pending', 0] },
                { $ifNull: ['$meeting_completed', 0] },
              ],
            },
          },
          siteVisit: {
            pending: { $ifNull: ['$siteVisit_pending', 0] },
            completed: { $ifNull: ['$siteVisit_completed', 0] },
            total: {
              $add: [
                { $ifNull: ['$siteVisit_pending', 0] },
                { $ifNull: ['$siteVisit_completed', 0] },
              ],
            },
          },
        },

        totalActivitiesAssigned: {
          $add: [
            { $add: ['$call_pending', '$call_completed'] },
            { $add: ['$meeting_pending', '$meeting_completed'] },
            { $add: ['$siteVisit_pending', '$siteVisit_completed'] },
          ],
        },
      },
    },

    { $sort: { totalActivitiesAssigned: -1 } },
  ]);

  return summaries.length ? summaries : null;
};

export const getLeadSourceStats = async (
  userType: string,
  companyId: Types.ObjectId,
  brokerId?: Types.ObjectId,
) => {
  const filter =
    userType === 'admin'
      ? { company: companyId }
      : { company: companyId, createdBy: brokerId };

  const pipeline: PipelineStage[] = [
    { $match: filter },

    // Lookup source info
    {
      $lookup: {
        from: 'sources',
        localField: 'source',
        foreignField: '_id',
        as: 'source',
      },
    },
    { $unwind: '$source' },

    // Lookup lead stage info
    {
      $lookup: {
        from: 'leadstages',
        localField: 'leadStage',
        foreignField: '_id',
        as: 'leadStage',
      },
    },
    { $unwind: { path: '$leadStage', preserveNullAndEmptyArrays: true } },

    // Group by source name
    {
      $group: {
        _id: '$source.name',
        generated: { $sum: 1 },
        won: {
          $sum: {
            $cond: [
              { $eq: [{ $toLower: '$leadStage.stageName' }, 'lead won'] },
              1,
              0,
            ],
          },
        },
        lost: {
          $sum: {
            $cond: [
              { $eq: [{ $toLower: '$leadStage.stageName' }, 'lead lost'] },
              1,
              0,
            ],
          },
        },
      },
    },
    // Compute inProgress
    {
      $addFields: {
        inProgress: {
          $subtract: ['$generated', { $add: ['$won', '$lost'] }],
        },
      },
    },

    // Format output
    {
      $project: {
        _id: 0,
        name: '$_id',
        generated: 1,
        won: 1,
        lost: 1,
        inProgress: 1,
      },
    },
  ];

  const results = await Lead.aggregate(pipeline);
  return results;
};

interface BuilderMetric {
  title: string;
  value: number;
  unit: string;
  change: number;
  isPositive: boolean;
}

interface BuilderMetricStats {
  totalProjects: number;
  [status: string]: number;
}

export const PROJECT_STATUSES = [
  'under_construction',
  'possession_soon',
  'ready_possession',
  'launch',
];

export const calculateBuilderMetrics = async (
  companyId: Types.ObjectId,
): Promise<BuilderMetric[]> => {
  const now = new Date();
  const startOfMonth = new Date(now.getFullYear(), now.getMonth(), 1);
  const startOfPrevMonth = new Date(now.getFullYear(), now.getMonth() - 1, 1);
  const endOfPrevMonth = new Date(now.getFullYear(), now.getMonth(), 0);

  // 1. Bookings this month
  const bookingsThisMonth = await UnitBookingOrHold.countDocuments({
    company: companyId,
    action: 'book',
    createdAt: { $gte: startOfMonth, $lte: now },
  });

  const bookingsPrevMonth = await UnitBookingOrHold.countDocuments({
    company: companyId,
    action: 'book',
    createdAt: { $gte: startOfPrevMonth, $lte: endOfPrevMonth },
  });

  // 2. Revenue closed this month
  const paymentsThisMonth = await CustomerPayment.aggregate([
    { $match: { company: companyId, 'timeline.status': 'paid' } },
    { $unwind: '$timeline' },
    {
      $match: {
        'timeline.status': 'paid',
        'timeline.lastPaidAt': { $gte: startOfMonth, $lte: now },
      },
    },
    { $group: { _id: null, totalPaid: { $sum: '$timeline.paid' } } },
  ]);
  const revenueThisMonth = paymentsThisMonth[0]?.totalPaid || 0;

  const paymentsPrevMonth = await CustomerPayment.aggregate([
    { $match: { company: companyId, 'timeline.status': 'paid' } },
    { $unwind: '$timeline' },
    {
      $match: {
        'timeline.status': 'paid',
        'timeline.lastPaidAt': { $gte: startOfPrevMonth, $lte: endOfPrevMonth },
      },
    },
    { $group: { _id: null, totalPaid: { $sum: '$timeline.paid' } } },
  ]);
  const revenuePrevMonth = paymentsPrevMonth[0]?.totalPaid || 0;

  // 3. Collections received this month (sum of paidAmount)
  const collectionsThisMonth = await CustomerPayment.aggregate([
    { $match: { company: companyId } },
    { $group: { _id: null, totalPaid: { $sum: '$paidAmount' } } },
  ]);
  const collectionsPrevMonth = collectionsThisMonth[0]?.totalPaid || 0;

  // 4. Collections due (totalAmount - paidAmount)
  const dueThisMonth = await CustomerPayment.aggregate([
    { $match: { company: companyId } },
    {
      $group: {
        _id: null,
        totalDue: { $sum: { $subtract: ['$totalAmount', '$paidAmount'] } },
      },
    },
  ]);
  const duePrevMonth = dueThisMonth[0]?.totalDue || 0;

  const calcChange = (current: number, prev: number) => {
    if (prev === 0) return current > 0 ? 100 : 0;
    return Math.round(((current - prev) / prev) * 100);
  };

  const metrics: BuilderMetric[] = [
    {
      title: 'Bookings This Month',
      value: bookingsThisMonth,
      unit: 'units',
      change: calcChange(bookingsThisMonth, bookingsPrevMonth),
      isPositive: bookingsThisMonth >= bookingsPrevMonth,
    },
    {
      title: 'Revenue Closed',
      value: revenueThisMonth,
      unit: '₹',
      change: calcChange(revenueThisMonth, revenuePrevMonth),
      isPositive: revenueThisMonth >= revenuePrevMonth,
    },
    {
      title: 'Collections Received',
      value: collectionsThisMonth[0]?.totalPaid || 0,
      unit: '₹',
      change: calcChange(
        collectionsThisMonth[0]?.totalPaid || 0,
        collectionsPrevMonth,
      ),
      isPositive:
        (collectionsThisMonth[0]?.totalPaid || 0) >= collectionsPrevMonth,
    },
    {
      title: 'Collections Due',
      value: dueThisMonth[0]?.totalDue || 0,
      unit: '₹',
      change: calcChange(dueThisMonth[0]?.totalDue || 0, duePrevMonth),
      isPositive: (dueThisMonth[0]?.totalDue || 0) <= duePrevMonth, // lower due is positive
    },
  ];

  return metrics;
};

export const calculateProjectStats = async (
  companyId: Types.ObjectId,
): Promise<BuilderMetricStats> => {
  if (!companyId) throw new Error('CompanyId is required');

  // Aggregate projects by status
  const statusCounts = await Project.aggregate<{ _id: string; count: number }>([
    { $match: { companyId } },
    {
      $group: {
        _id: '$status',
        count: { $sum: 1 },
      },
    },
  ]);

  // Total projects
  const totalProjects = await Project.countDocuments({ companyId });

  // Initialize all statuses with 0
  const statusCountsObj: BuilderMetricStats = { totalProjects };
  PROJECT_STATUSES.forEach((status) => {
    statusCountsObj[status] = 0;
  });

  // Fill actual counts
  statusCounts.forEach((item) => {
    statusCountsObj[item._id] = item.count;
  });

  return statusCountsObj;
};

export interface PaymentStats {
  totalValue: number;
  receivedAmount: number;
  receivableAmount: number;
  financialData: {
    data: { date: string; value: number }[];
    score: string;
  };
}

export const calculatePaymentStats = async (
  companyId: Types.ObjectId,
): Promise<PaymentStats> => {
  if (!companyId) throw new Error('CompanyId is required');

  const stats = await CustomerPayment.aggregate<{
    totalValue: number;
    receivedAmount: number;
  }>([
    { $match: { company: companyId } },
    
    // Lookup customer to check if booking is cancelled
    {
      $lookup: {
        from: 'customers',
        localField: 'customerId',
        foreignField: '_id',
        as: 'customer',
      },
    },
    { $unwind: { path: '$customer', preserveNullAndEmptyArrays: true } },
    
    // Exclude payments for cancelled bookings
    {
      $match: {
        $or: [
          { 'customer.bookingStatus': { $ne: 'cancelled' } },
          { 'customer.bookingStatus': { $exists: false } },
        ],
      },
    },
    
    {
      $group: {
        _id: null,
        totalValue: { $sum: '$totalAmount' },
        receivedAmount: { $sum: '$paidAmount' },
      },
    },
  ]);

  const totalValue = stats[0]?.totalValue || 0;
  const receivedAmount = stats[0]?.receivedAmount || 0;
  const receivableAmount = totalValue - receivedAmount;
  const financialData = await getFinancialSummaryData(companyId);

  return { totalValue, receivedAmount, receivableAmount, financialData };
};

const getFinancialSummaryData = async (companyId: Types.ObjectId) => {
  if (!companyId) throw new Error('CompanyId is required');

  const now = dayjs();
  const quarter = Math.floor(now.month() / 3);

  // Calculate all date ranges once
  const currentStart = now
    .startOf('year')
    .add(quarter * 3, 'month')
    .startOf('month');
  const currentEnd = currentStart
    .add(3, 'month')
    .subtract(1, 'day')
    .endOf('day');
  const prevStart = currentStart.subtract(3, 'month');
  const prevEnd = currentStart.subtract(1, 'day').endOf('day');

  // Single optimized aggregation using $facet for parallel processing
  const [result] = await CustomerPayment.aggregate([
    {
      $match: {
        company: companyId,
        createdAt: { $gte: prevStart.toDate(), $lte: currentEnd.toDate() },
      },
    },
    
    // Lookup customer to check if booking is cancelled
    {
      $lookup: {
        from: 'customers',
        localField: 'customerId',
        foreignField: '_id',
        as: 'customer',
      },
    },
    { $unwind: { path: '$customer', preserveNullAndEmptyArrays: true } },
    
    // Exclude payments for cancelled bookings
    {
      $match: {
        $or: [
          { 'customer.bookingStatus': { $ne: 'cancelled' } },
          { 'customer.bookingStatus': { $exists: false } },
        ],
      },
    },
    
    {
      $facet: {
        // Current quarter weekly breakdown
        weeklyData: [
          {
            $match: {
              createdAt: {
                $gte: currentStart.toDate(),
                $lte: currentEnd.toDate(),
              },
            },
          },
          {
            $group: {
              _id: {
                week: { $isoWeek: '$createdAt' },
                year: { $isoWeekYear: '$createdAt' },
              },
              totalRevenue: { $sum: '$paidAmount' },
              firstDate: { $min: '$createdAt' },
            },
          },
          { $sort: { firstDate: 1 } },
        ],
        // Current quarter total revenue
        currentTotal: [
          {
            $match: {
              createdAt: {
                $gte: currentStart.toDate(),
                $lte: currentEnd.toDate(),
              },
            },
          },
          {
            $group: {
              _id: null,
              total: { $sum: '$paidAmount' },
            },
          },
        ],
        // Previous quarter total revenue
        prevTotal: [
          {
            $match: {
              createdAt: { $gte: prevStart.toDate(), $lte: prevEnd.toDate() },
            },
          },
          {
            $group: {
              _id: null,
              total: { $sum: '$paidAmount' },
            },
          },
        ],
      },
    },
  ]);

  // Extract results from facets
  const weeklyData = result?.weeklyData || [];
  const currentRevenue = result?.currentTotal?.[0]?.total || 0;
  const prevRevenue = result?.prevTotal?.[0]?.total || 0;

  // Calculate quarter-over-quarter growth percentage
  let score = 0;
  if (prevRevenue === 0 && currentRevenue > 0) {
    score = 100;
  } else if (prevRevenue > 0) {
    score = Number(
      (((currentRevenue - prevRevenue) / prevRevenue) * 100).toFixed(2),
    );
  }

  // Format weekly data for response
  const formatted = weeklyData.map((item) => ({
    date: dayjs(item.firstDate).format('DD-MM-YYYY'),
    value: item.totalRevenue,
  }));

  return {
    data: formatted,
    score: `${score}%`,
  };
};

const getCompanyProjectIds = async (companyId: Types.ObjectId) => {
  const Project = (await import('@/modules/project/project.model')).default;
  const projects = await Project.find({ companyId }, { _id: 1 }).lean();
  return projects.map((p) => p._id);
};

export interface UnitStats {
  totalUnits: number;
  soldUnits: number;
  availableUnits: number;
  salesIncrease: number; // difference from previous month
}

export const calculateUnitStats = async (
  companyId: Types.ObjectId,
): Promise<UnitStats> => {
  if (!companyId) throw new Error('CompanyId is required');

  // 1. Total, sold, and available units
  const unitCounts = await Unit.aggregate<{
    status: string;
    count: number;
    _id: string;
  }>([
    {
      $lookup: {
        from: 'projects',
        localField: 'project',
        foreignField: '_id',
        as: 'project',
      },
    },
    { $unwind: '$project' },
    { $match: { 'project.companyId': companyId } },
    {
      $group: {
        _id: '$status',
        count: { $sum: 1 },
      },
    },
  ]);

  const countsMap: Record<string, number> = {
    sold: 0,
    available: 0,
    hold: 0,
    booked: 0,
  };

  unitCounts.forEach((item) => {
    countsMap[item._id] = item.count;
  });

  const soldUnits = countsMap.sold + countsMap.booked;
  const totalUnits =
    soldUnits + countsMap.available + countsMap.hold;
  const availableUnits = countsMap.available;

  // 2. Sales increase from previous month
  const now = new Date();
  const startCurrentMonth = new Date(now.getFullYear(), now.getMonth(), 1);
  const startPrevMonth = new Date(now.getFullYear(), now.getMonth() - 1, 1);
  const endPrevMonth = new Date(now.getFullYear(), now.getMonth(), 0); // last day prev month

  const prevMonthSold = await Unit.countDocuments({
    status: 'sold',
    createdAt: { $gte: startPrevMonth, $lte: endPrevMonth },
    project: { $in: await getCompanyProjectIds(companyId) },
  });

  const currentMonthSold = await Unit.countDocuments({
    status: 'sold',
    createdAt: { $gte: startCurrentMonth },
    project: { $in: await getCompanyProjectIds(companyId) },
  });

  const salesIncrease = currentMonthSold - prevMonthSold;

  return {
    totalUnits,
    soldUnits,
    availableUnits,
    salesIncrease,
  };
};

export interface LeadStats {
  activeLeadCount: number;
  cold: number;
  warm: number;
  hot: number;
}

export const calculateLeadStats = async (
  companyId: Types.ObjectId,
): Promise<LeadStats> => {
  if (!companyId) throw new Error('CompanyId is required');

  const activeStages = await LeadStage.find({
    company: companyId,
    stageName: { $nin: ['Lead Won', 'Lead Lost', 'lead won', 'lead lost'] },
  }).select('_id');

  const activeStageIds = activeStages.map((s) => s._id);

  // 2. Aggregate leads by leadScore
  const leadBuckets = await Lead.aggregate([
    { $match: { company: companyId, leadStage: { $in: activeStageIds } } },
    {
      $bucket: {
        groupBy: { $ifNull: ['$leadScore', 0] },
        boundaries: [0, 41, 71, 101], // 0-40, 41-70, 71-100+
        default: 'hot', // just in case score > 100
        output: {
          count: { $sum: 1 },
        },
      },
    },
  ]);

  // Initialize counts ($bucket default bucket uses _id 'hot', separate from boundary 71)
  const counts = { cold: 0, warm: 0, hot: 0 };
  leadBuckets.forEach((bucket: { _id: number | string; count: number }) => {
    if (bucket._id === 0) counts.cold = bucket.count;
    else if (bucket._id === 41) counts.warm = bucket.count;
    else counts.hot += bucket.count;
  });

  const activeLeadCount = counts.cold + counts.warm + counts.hot;

  return { activeLeadCount, ...counts };
};

interface LeadsWithoutActivityResult {
  leadsWithoutActivity: number;
}

/**
 * Counts leads assigned to a user that have no activities created for them
 * @param userId - Logged-in user's ObjectId
 * @returns Count of leads without activities
 */
export const countLeadsWithoutActivity = async (
  userId: Types.ObjectId | string,
): Promise<number> => {
  const pipeline = [
    {
      $match: {
        assignedTo: getObjectId(userId),
      },
    },
    {
      $lookup: {
        from: 'activities',
        let: { leadId: '$_id' },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ['$lead', '$$leadId'] },
                  {
                    $not: {
                      $in: ['$title', ['Lead Created', 'Contact Created']],
                    },
                  },
                ],
              },
            },
          },
        ],
        as: 'userActivities',
      },
    },
    {
      $match: {
        userActivities: { $size: 0 },
      },
    },
    {
      $count: 'leadsWithoutActivity',
    },
  ];

  const leadsWithoutActivity: LeadsWithoutActivityResult[] =
    await Lead.aggregate(pipeline);

  return leadsWithoutActivity[0]?.leadsWithoutActivity || 0;
};

export const getActiveLeadsSummary = async (companyId: Types.ObjectId) => {
  const now = dayjs();
  const startOfMonth = now.startOf('month');
  const endOfMonth = now.endOf('month');
  const startOfPrevMonth = now.subtract(1, 'month').startOf('month');
  const endOfPrevMonth = now.subtract(1, 'month').endOf('month');

  const activeStages = await LeadStage.find({
    company: companyId,
    stageName: { $nin: ['Lead Won', 'Lead Lost', 'lead won', 'lead lost'] },
  }).select('_id');

  const activeStageIds = activeStages.map((s) => s._id);

  const weeklyData = await Lead.aggregate([
    {
      $match: {
        company: companyId,
        leadStage: { $in: activeStageIds },
        createdAt: { $gte: startOfMonth.toDate(), $lte: endOfMonth.toDate() },
      },
    },
    {
      $group: {
        _id: {
          week: { $isoWeek: '$createdAt' },
          year: { $isoWeekYear: '$createdAt' },
        },
        hot: {
          $sum: {
            $cond: [{ $gte: [{ $ifNull: ['$leadScore', 0] }, 71] }, 1, 0],
          },
        },
        warm: {
          $sum: {
            $cond: [
              {
                $and: [
                  { $gte: [{ $ifNull: ['$leadScore', 0] }, 41] },
                  { $lte: [{ $ifNull: ['$leadScore', 0] }, 70] },
                ],
              },
              1,
              0,
            ],
          },
        },
        cold: {
          $sum: {
            $cond: [{ $lt: [{ $ifNull: ['$leadScore', 0] }, 41] }, 1, 0],
          },
        },
        firstDate: { $min: '$createdAt' },
      },
    },
    { $sort: { firstDate: 1 } },
  ]);

  const hot = weeklyData.map((w) => ({
    date: dayjs(w.firstDate).format('DD-MM-YYYY'),
    value: w.hot,
  }));
  const warm = weeklyData.map((w) => ({
    date: dayjs(w.firstDate).format('DD-MM-YYYY'),
    value: w.warm,
  }));
  const cold = weeklyData.map((w) => ({
    date: dayjs(w.firstDate).format('DD-MM-YYYY'),
    value: w.cold,
  }));

  const [currentMonthCount, prevMonthCount] = await Promise.all([
    Lead.countDocuments({
      company: companyId,
      leadStage: { $in: activeStageIds },
      createdAt: { $gte: startOfMonth.toDate(), $lte: endOfMonth.toDate() },
    }),
    Lead.countDocuments({
      company: companyId,
      leadStage: { $in: activeStageIds },
      createdAt: {
        $gte: startOfPrevMonth.toDate(),
        $lte: endOfPrevMonth.toDate(),
      },
    }),
  ]);

  let score = 0;
  if (prevMonthCount === 0 && currentMonthCount > 0) {
    score = 100;
  } else if (prevMonthCount > 0) {
    score = ((currentMonthCount - prevMonthCount) / prevMonthCount) * 100;
  }

  return {
    data: { hot, warm, cold },
    score: `${score > 0 ? '+' : ''}${parseFloat(score.toFixed(2))}%`,
  };
};

export const getActivityStats = async (
  userType: string,
  companyId: Types.ObjectId,
  userId?: Types.ObjectId,
) => {
  const taskFilter =
    userType === 'admin'
      ? { companyId: getObjectId(companyId) }
      : {
          companyId: getObjectId(companyId),
          assignedTo: getObjectId(userId),
        };

  const pipeline = [
    {
      $match: {
        ...taskFilter,
        activityType: { $in: ['call', 'meeting', 'siteVisit'] },
      },
    },
    {
      $group: {
        _id: '$activityType',
        pending: {
          $sum: {
            $cond: [
              {
                $in: ['$status', ['pending', 'overdue']],
              },
              1,
              0,
            ],
          },
        },
        completed: {
          $sum: {
            $cond: [{ $eq: ['$status', 'completed'] }, 1, 0],
          },
        },
        total: { $sum: 1 },
      },
    },
  ];

  const results = await Tasks.aggregate(pipeline);

  const activityStats = {
    call: { pending: 0, completed: 0, total: 0 },
    meeting: { pending: 0, completed: 0, total: 0 },
    siteVisit: { pending: 0, completed: 0, total: 0 },
  };

  results.forEach((item) => {
    if (item._id === 'call') {
      activityStats.call = {
        pending: item.pending || 0,
        completed: item.completed || 0,
        total: item.total || 0,
      };
    } else if (item._id === 'meeting') {
      activityStats.meeting = {
        pending: item.pending || 0,
        completed: item.completed || 0,
        total: item.total || 0,
      };
    } else if (item._id === 'siteVisit') {
      activityStats.siteVisit = {
        pending: item.pending || 0,
        completed: item.completed || 0,
        total: item.total || 0,
      };
    }
  });

  return activityStats;
};