/* eslint-disable @typescript-eslint/no-explicit-any */
import { Company } from '@/modules/company/company.model';
import {
  Status,
  CompanyType,
  InvoiceStatus,
  ActivityType,
} from '@/shared/constants/enum.constant';
import dayjs from 'dayjs';
import { Types } from 'mongoose';
import User from '../user/user.model';
import { Invoice } from '../invoice/invoice.model';
import { Contact } from '../contacts/contacts.model';
import { Lead } from '../lead/lead.model';
import { Tasks } from '../tasks/tasks.model';
import { individualProperties } from '../individualProperties';
import { Project, Unit } from '../project';
import { Activity } from '../activity/activity.model';
import { Campaign } from '../campaign/campaign.model';

type GrowthType = 'increase' | 'decrease' | 'neutral';

type DateFilter = { startDate?: string; endDate?: string };

export const getCompanyStatsAggregation = async (
  companyId: Types.ObjectId,
  filter?: { startDate?: string; endDate?: string },
) => {
  const now = dayjs();
  const thisMonthStart = now.startOf('month').toDate();
  const thisMonthEnd = now.endOf('month').toDate();
  const sixMonthsAgo = now.startOf('month').subtract(6, 'month').toDate();

  // Create date filter for createdAt based on the provided filter
  const createDateFilter = () => {
    const dateFilter: any = {};

    if (filter?.startDate && filter?.endDate)
      // If both dates are provided, use the filter range
      dateFilter.createdAt = {
        $gte: new Date(filter.startDate),
        $lte: new Date(filter.endDate),
      };
    else if (filter?.startDate)
      // If only startDate is provided
      dateFilter.createdAt = { $gte: new Date(filter.startDate) };
    else if (filter?.endDate)
      // If only endDate is provided
      dateFilter.createdAt = { $lte: new Date(filter.endDate) };

    return dateFilter;
  };

  const dateFilter = createDateFilter();

  const [overallStats] = await Company.aggregate([
    {
      $facet: {
        total: [
          {
            $match: {
              status: Status.ACTIVE,
              _id: { $ne: companyId },
              ...dateFilter,
            },
          },
          { $count: 'count' },
        ],
        newThisMonth: [
          {
            $match: {
              status: Status.ACTIVE,
              _id: { $ne: companyId },
              // Use filter dates if provided, otherwise use thisMonth dates
              createdAt:
                filter?.startDate && filter?.endDate
                  ? {
                      $gte: new Date(filter.startDate),
                      $lte: new Date(filter.endDate),
                    }
                  : { $gte: thisMonthStart, $lte: thisMonthEnd },
            },
          },
          { $count: 'count' },
        ],
        brokers: [
          {
            $match: {
              status: Status.ACTIVE,
              companyType: CompanyType.BROKER,
              _id: { $ne: companyId },
              ...dateFilter,
            },
          },
          { $count: 'count' },
        ],
        builders: [
          {
            $match: {
              status: Status.ACTIVE,
              companyType: CompanyType.BUILDER,
              _id: { $ne: companyId },
              ...dateFilter,
            },
          },
          { $count: 'count' },
        ],
      },
    },
    {
      $project: {
        total: { $ifNull: [{ $arrayElemAt: ['$total.count', 0] }, 0] },
        newThisMonth: {
          $ifNull: [{ $arrayElemAt: ['$newThisMonth.count', 0] }, 0],
        },
        brokers: { $ifNull: [{ $arrayElemAt: ['$brokers.count', 0] }, 0] },
        builders: { $ifNull: [{ $arrayElemAt: ['$builders.count', 0] }, 0] },
      },
    },
  ]);

  // For trends, use filter dates if provided, otherwise use sixMonthsAgo to now
  const trendStartDate = filter?.startDate
    ? new Date(filter.startDate)
    : sixMonthsAgo;
  const trendEndDate = filter?.endDate
    ? new Date(filter.endDate)
    : now.toDate();

  const trends = await Company.aggregate([
    {
      $match: {
        status: Status.ACTIVE,
        _id: { $ne: companyId },
        createdAt: { $gte: trendStartDate, $lte: trendEndDate },
      },
    },
    {
      $group: {
        _id: {
          year: { $year: '$createdAt' },
          month: { $month: '$createdAt' },
          type: '$companyType',
        },
        count: { $sum: 1 },
      },
    },
    { $sort: { '_id.year': 1, '_id.month': 1 } },
  ]);

  const brokerTrend: { month: string; value: number }[] = [];
  const builderTrend: { month: string; value: number }[] = [];

  // Generate trend data based on the date range
  if (filter?.startDate && filter?.endDate) {
    // If filter is provided, generate months between startDate and endDate
    const startDate = dayjs(filter.startDate);
    const endDate = dayjs(filter.endDate);

    let current = startDate.startOf('month');
    const end = endDate.startOf('month');

    while (current.isSame(end) || current.isBefore(end)) {
      const y = current.year();
      const m = current.month() + 1;
      const monthName = current.format('MMM');

      const brokerRecord = trends.find(
        (t) =>
          t._id.year === y &&
          t._id.month === m &&
          t._id.type === CompanyType.BROKER,
      );
      const builderRecord = trends.find(
        (t) =>
          t._id.year === y &&
          t._id.month === m &&
          t._id.type === CompanyType.BUILDER,
      );

      brokerTrend.push({ month: monthName, value: brokerRecord?.count || 0 });
      builderTrend.push({ month: monthName, value: builderRecord?.count || 0 });

      current = current.add(1, 'month');
    }
  } else {
    // Original logic for last 6 months
    for (let i = 6; i >= 0; i--) {
      const d = now.subtract(i, 'month');
      const y = d.year();
      const m = d.month() + 1;
      const monthName = d.format('MMM');

      const brokerRecord = trends.find(
        (t) =>
          t._id.year === y &&
          t._id.month === m &&
          t._id.type === CompanyType.BROKER,
      );
      const builderRecord = trends.find(
        (t) =>
          t._id.year === y &&
          t._id.month === m &&
          t._id.type === CompanyType.BUILDER,
      );

      brokerTrend.push({ month: monthName, value: brokerRecord?.count || 0 });
      builderTrend.push({ month: monthName, value: builderRecord?.count || 0 });
    }
  }

  const changeType = overallStats.newThisMonth > 0 ? 'increase' : 'decrease';

  return {
    total: overallStats.total,
    newThisMonth: overallStats.newThisMonth,
    changeType,
    brokers: overallStats.brokers,
    builders: overallStats.builders,
    brokerTrend,
    builderTrend,
  };
};

export const getUserStatsAggregation = async (
  userId: Types.ObjectId,
  filter?: { startDate?: string; endDate?: string },
) => {
  const now = dayjs();
  const thisMonthEnd = now.endOf('month').toDate();
  const prevMonthEnd = now.subtract(1, 'month').endOf('month').toDate();
  const sixMonthsAgo = now.startOf('month').subtract(6, 'month').toDate();

  // Create date filter for createdAt based on the provided filter
  const createDateFilter = (additionalConditions?: any) => {
    const dateFilter: any = {
      status: Status.ACTIVE,
      isDeleted: false,
      _id: { $ne: userId },
      ...additionalConditions,
    };

    if (filter?.startDate && filter?.endDate)
      // If both dates are provided, use the filter range
      dateFilter.createdAt = {
        $gte: new Date(filter.startDate),
        $lte: new Date(filter.endDate),
      };
    else if (filter?.startDate)
      // If only startDate is provided
      dateFilter.createdAt = { $gte: new Date(filter.startDate) };
    else if (filter?.endDate)
      // If only endDate is provided
      dateFilter.createdAt = { $lte: new Date(filter.endDate) };

    return dateFilter;
  };

  // --- 1. Current & previous month totals
  const [monthlyStats] = await User.aggregate([
    {
      $facet: {
        currentMonth: [
          {
            $match:
              filter?.startDate || filter?.endDate
                ? createDateFilter()
                : {
                    status: Status.ACTIVE,
                    isDeleted: false,
                    _id: { $ne: userId },
                    createdAt: { $lte: thisMonthEnd }, // all active users up to this month
                  },
          },
          { $count: 'count' },
        ],
        prevMonth: [
          {
            $match:
              filter?.startDate || filter?.endDate
                ? createDateFilter()
                : {
                    status: Status.ACTIVE,
                    isDeleted: false,
                    _id: { $ne: userId },
                    createdAt: { $lte: prevMonthEnd }, // all active users up to previous month
                  },
          },
          { $count: 'count' },
        ],
      },
    },
    {
      $project: {
        currentMonthTotal: {
          $ifNull: [{ $arrayElemAt: ['$currentMonth.count', 0] }, 0],
        },
        prevMonthTotal: {
          $ifNull: [{ $arrayElemAt: ['$prevMonth.count', 0] }, 0],
        },
      },
    },
  ]);

  const { currentMonthTotal, prevMonthTotal } = monthlyStats || {
    currentMonthTotal: 0,
    prevMonthTotal: 0,
  };

  // --- 2. Monthly trend for last 6 months + current
  // Determine date range for trends
  const trendStartDate = filter?.startDate
    ? new Date(filter.startDate)
    : sixMonthsAgo;
  const trendEndDate = filter?.endDate
    ? new Date(filter.endDate)
    : now.toDate();

  const trendData = await User.aggregate([
    {
      $match: {
        status: Status.ACTIVE,
        isDeleted: false,
        _id: { $ne: userId },
        createdAt: { $gte: trendStartDate, $lte: trendEndDate },
      },
    },
    {
      $group: {
        _id: {
          year: { $year: '$createdAt' },
          month: { $month: '$createdAt' },
        },
        count: { $sum: 1 },
      },
    },
    { $sort: { '_id.year': 1, '_id.month': 1 } },
  ]);

  // --- 3. Build cumulative trend
  const trend: { month: string; value: number }[] = [];
  const monthNames = [
    'Jan',
    'Feb',
    'Mar',
    'Apr',
    'May',
    'Jun',
    'Jul',
    'Aug',
    'Sep',
    'Oct',
    'Nov',
    'Dec',
  ];

  let runningTotal = 0;

  if (filter?.startDate && filter?.endDate) {
    // Generate trend data based on filter date range
    const startDate = dayjs(filter.startDate);
    const endDate = dayjs(filter.endDate);

    let current = startDate.startOf('month');
    const end = endDate.startOf('month');

    while (current.isSame(end) || current.isBefore(end)) {
      const y = current.year();
      const m = current.month() + 1;
      const monthName = monthNames[m - 1];

      // new users created in this month
      const monthRecord = trendData.find(
        (t) => t._id.year === y && t._id.month === m,
      );

      runningTotal += monthRecord?.count || 0;
      trend.push({ month: monthName, value: runningTotal });

      current = current.add(1, 'month');
    }
  } else {
    // Original logic for last 6 months
    for (let i = 6; i >= 0; i--) {
      const d = now.subtract(i, 'month');
      const y = d.year();
      const m = d.month() + 1;
      const monthName = monthNames[m - 1];

      // new users created in this month
      const monthRecord = trendData.find(
        (t) => t._id.year === y && t._id.month === m,
      );

      runningTotal += monthRecord?.count || 0;
      trend.push({ month: monthName, value: runningTotal });
    }
  }

  // --- 4. Calculate percent change
  let percentChange = 0;
  let changeType: 'increase' | 'decrease' | 'neutral' = 'neutral';

  if (prevMonthTotal > 0)
    percentChange =
      ((currentMonthTotal - prevMonthTotal) / prevMonthTotal) * 100;

  if (percentChange > 0) changeType = 'increase';
  else if (percentChange < 0) changeType = 'decrease';

  return {
    currentMonthTotal,
    percentChange: Number(percentChange.toFixed(1)),
    changeType,
    trend,
  };
};

export const getFinancialSummary = async (filter?: {
  startDate?: string;
  endDate?: string;
}) => {
  const now = dayjs();
  const sixMonthsAgo = now.subtract(6, 'month').startOf('month').toDate();

  // Create date filter for issuedDate based on the provided filter
  const createIssuedDateFilter = () => {
    if (filter?.startDate && filter?.endDate)
      return {
        issuedDate: {
          $gte: new Date(filter.startDate),
          $lte: new Date(filter.endDate),
        },
      };
    else if (filter?.startDate)
      return {
        issuedDate: { $gte: new Date(filter.startDate) },
      };
    else if (filter?.endDate)
      return {
        issuedDate: { $lte: new Date(filter.endDate) },
      };
    else
      // Default to last 6 months if no filter
      return {
        issuedDate: { $gte: sixMonthsAgo },
      };
  };

  // Group invoices by month and status
  const monthlyData = await Invoice.aggregate([
    {
      $match: createIssuedDateFilter(),
    },
    {
      $group: {
        _id: {
          month: { $month: '$issuedDate' },
          year: { $year: '$issuedDate' },
          status: '$status',
        },
        total: { $sum: '$finalPrice' },
      },
    },
    {
      $sort: { '_id.year': 1, '_id.month': 1 },
    },
  ]);

  // Initialize trend data
  const receivedTrend: { month: string; value: number }[] = [];
  const receivableTrend: { month: string; value: number }[] = [];

  const monthNames = [
    'Jan',
    'Feb',
    'Mar',
    'Apr',
    'May',
    'Jun',
    'Jul',
    'Aug',
    'Sep',
    'Oct',
    'Nov',
    'Dec',
  ];

  // Build trends based on filter or default 6 months
  if (filter?.startDate && filter?.endDate) {
    // Generate trend data based on filter date range
    const startDate = dayjs(filter.startDate);
    const endDate = dayjs(filter.endDate);

    let current = startDate.startOf('month');
    const end = endDate.startOf('month');

    while (current.isSame(end) || current.isBefore(end)) {
      const month = current.month() + 1; // 1–12
      const year = current.year();
      const monthName = monthNames[month - 1];

      const monthData = monthlyData.filter(
        (m) => m._id.month === month && m._id.year === year,
      );

      const received =
        monthData.find((m) => m._id.status === InvoiceStatus.PAID)?.total || 0;
      const receivable =
        monthData.find((m) => m._id.status === InvoiceStatus.UNPAID)?.total ||
        0;

      receivedTrend.push({ month: monthName, value: received });
      receivableTrend.push({ month: monthName, value: receivable });

      current = current.add(1, 'month');
    }
  } else {
    // Original logic for last 6 months
    for (let i = 6; i >= 0; i--) {
      const targetMonth = now.subtract(i, 'month');
      const month = targetMonth.month() + 1; // 1–12
      const year = targetMonth.year();
      const monthName = monthNames[month - 1];

      const monthData = monthlyData.filter(
        (m) => m._id.month === month && m._id.year === year,
      );

      const received =
        monthData.find((m) => m._id.status === InvoiceStatus.PAID)?.total || 0;
      const receivable =
        monthData.find((m) => m._id.status === InvoiceStatus.UNPAID)?.total ||
        0;

      receivedTrend.push({ month: monthName, value: received });
      receivableTrend.push({ month: monthName, value: receivable });
    }
  }

  // Totals
  const totalValue =
    receivedTrend.reduce((acc, cur) => acc + cur.value, 0) +
    receivableTrend.reduce((acc, cur) => acc + cur.value, 0);

  const received = receivedTrend.reduce((acc, cur) => acc + cur.value, 0);
  const receivable = receivableTrend.reduce((acc, cur) => acc + cur.value, 0);

  // Compare last two months for % change
  const prevMonth = receivedTrend[receivedTrend.length - 2]?.value ?? 0;
  const currentMonth = receivedTrend[receivedTrend.length - 1]?.value ?? 0;
  
  const percentIncrease =
    prevMonth === 0 ? 0 : ((currentMonth - prevMonth) / prevMonth) * 100;

  const changeType = currentMonth >= prevMonth ? 'increase' : 'decrease';

  return {
    totalValue,
    received,
    receivable,
    percentIncrease: percentIncrease.toFixed(2),
    changeType,
    receivedTrend,
    receivableTrend,
  };
};

export const getRevenueMetrics = async (
  excludedUserId: Types.ObjectId,
  excludedCompanyId: Types.ObjectId,
  filter?: { startDate?: string; endDate?: string },
) => {
  const now = dayjs();
  const startOfMonth = now.startOf('month').toDate();
  const endOfMonth = now.endOf('month').toDate();

  const startOfLastMonth = now.subtract(1, 'month').startOf('month').toDate();
  const endOfLastMonth = now.subtract(1, 'month').endOf('month').toDate();

  // Helper function to create date filter for issuedDate
  const createIssuedDateFilter = (additionalConditions?: any) => {
    const baseFilter = {
      company: { $ne: excludedCompanyId },
      ...additionalConditions,
    };

    if (filter?.startDate && filter?.endDate)
      return {
        ...baseFilter,
        issuedDate: {
          $gte: new Date(filter.startDate),
          $lte: new Date(filter.endDate),
        },
      };
    else if (filter?.startDate)
      return {
        ...baseFilter,
        issuedDate: { $gte: new Date(filter.startDate) },
      };
    else if (filter?.endDate)
      return {
        ...baseFilter,
        issuedDate: { $lte: new Date(filter.endDate) },
      };

    return baseFilter;
  };

  // Helper function to create date filter for createdAt (users)
  const createCreatedAtFilter = (additionalConditions?: any) => {
    const baseFilter = {
      status: Status.ACTIVE,
      isDeleted: false,
      _id: { $ne: excludedUserId },
      'company.id': { $ne: excludedCompanyId },
      ...additionalConditions,
    };

    if (filter?.startDate && filter?.endDate)
      return {
        ...baseFilter,
        createdAt: {
          $gte: new Date(filter.startDate),
          $lte: new Date(filter.endDate),
        },
      };
    else if (filter?.startDate)
      return {
        ...baseFilter,
        createdAt: { $gte: new Date(filter.startDate) },
      };
    else if (filter?.endDate)
      return {
        ...baseFilter,
        createdAt: { $lte: new Date(filter.endDate) },
      };

    return baseFilter;
  };

  // --- MRR (current month or filtered period) ---
  const [{ total: MRR = 0 } = {}] = await Invoice.aggregate([
    {
      $match:
        filter?.startDate || filter?.endDate
          ? createIssuedDateFilter({ status: InvoiceStatus.PAID })
          : {
              status: InvoiceStatus.PAID,
              issuedDate: { $gte: startOfMonth, $lte: endOfMonth },
              company: { $ne: excludedCompanyId },
            },
    },
    { $group: { _id: null, total: { $sum: '$finalPrice' } } },
  ]);

  // --- Last month MRR (or comparison period) ---
  let lastMonthMRR = 0;
  if (filter?.startDate && filter?.endDate) {
    // For filtered data, calculate previous period of same duration
    const filterStartDate = dayjs(filter.startDate);
    const filterEndDate = dayjs(filter.endDate);
    const duration = filterEndDate.diff(filterStartDate, 'day');

    const prevPeriodEnd = filterStartDate.subtract(1, 'day');
    const prevPeriodStart = prevPeriodEnd.subtract(duration, 'day');

    const [{ total: prevPeriodMRR = 0 } = {}] = await Invoice.aggregate([
      {
        $match: {
          status: InvoiceStatus.PAID,
          issuedDate: {
            $gte: prevPeriodStart.toDate(),
            $lte: prevPeriodEnd.toDate(),
          },
          company: { $ne: excludedCompanyId },
        },
      },
      { $group: { _id: null, total: { $sum: '$finalPrice' } } },
    ]);
    lastMonthMRR = prevPeriodMRR;
  } else {
    // Original last month logic
    const [{ total: originalLastMonthMRR = 0 } = {}] = await Invoice.aggregate([
      {
        $match: {
          status: InvoiceStatus.PAID,
          issuedDate: { $gte: startOfLastMonth, $lte: endOfLastMonth },
          company: { $ne: excludedCompanyId },
        },
      },
      { $group: { _id: null, total: { $sum: '$finalPrice' } } },
    ]);
    lastMonthMRR = originalLastMonthMRR;
  }

  // --- Active Users (exclude the user + company) ---
  const activeUsers = await User.countDocuments(
    filter?.startDate || filter?.endDate
      ? createCreatedAtFilter()
      : {
          status: Status.ACTIVE,
          isDeleted: false,
          _id: { $ne: excludedUserId },
          'company.id': { $ne: excludedCompanyId },
        },
  );

  // For ARPU growth baseline
  let lastMonthActiveUsers = 0;
  if (filter?.startDate && filter?.endDate) {
    // For filtered data, get users from previous period
    const filterStartDate = dayjs(filter.startDate);
    const filterEndDate = dayjs(filter.endDate);
    const duration = filterEndDate.diff(filterStartDate, 'day');

    const prevPeriodEnd = filterStartDate.subtract(1, 'day');
    const prevPeriodStart = prevPeriodEnd.subtract(duration, 'day');

    lastMonthActiveUsers = await User.countDocuments({
      status: Status.ACTIVE,
      isDeleted: false,
      _id: { $ne: excludedUserId },
      'company.id': { $ne: excludedCompanyId },
      createdAt: {
        $gte: prevPeriodStart.toDate(),
        $lte: prevPeriodEnd.toDate(),
      },
    });
  } else {
    // Original logic
    lastMonthActiveUsers = await User.countDocuments({
      status: Status.ACTIVE,
      isDeleted: false,
      _id: { $ne: excludedUserId },
      'company.id': { $ne: excludedCompanyId },
      createdAt: { $lte: endOfLastMonth },
    });
  }

  // --- ARPU ---
  const ARPU = activeUsers > 0 ? MRR / activeUsers : 0;
  const lastMonthARPU =
    lastMonthActiveUsers > 0 ? lastMonthMRR / lastMonthActiveUsers : 0;

  // --- ARR ---
  const ARR = MRR * 12;
  const lastMonthARR = lastMonthMRR * 12;

  // --- Churn (companies) ---
  let churnRate = 0;
  let lastMonthChurn = 0;

  if (filter?.startDate && filter?.endDate) {
    // For filtered period, calculate churn based on filter dates
    const filterStartDate = dayjs(filter.startDate);
    const filterEndDate = dayjs(filter.endDate);

    // Companies with paid invoices before filter period
    const companiesBeforeFilter = await Invoice.aggregate<{
      _id: Types.ObjectId;
    }>([
      {
        $match: {
          status: InvoiceStatus.PAID,
          issuedDate: { $lt: filterStartDate.toDate() },
          company: { $ne: excludedCompanyId },
        },
      },
      { $group: { _id: '$company' } },
    ]);
    const customersAtFilterStartSet = new Set(
      companiesBeforeFilter.map((d) => d._id.toString()),
    );

    // Companies that paid during filter period
    const companiesDuringFilter = await Invoice.aggregate<{
      _id: Types.ObjectId;
    }>([
      {
        $match: {
          status: InvoiceStatus.PAID,
          issuedDate: {
            $gte: filterStartDate.toDate(),
            $lte: filterEndDate.toDate(),
          },
          company: { $ne: excludedCompanyId },
        },
      },
      { $group: { _id: '$company' } },
    ]);
    const customersDuringFilterSet = new Set(
      companiesDuringFilter.map((d) => d._id.toString()),
    );

    const customersAtStart = customersAtFilterStartSet.size;
    const customersLost = [...customersAtFilterStartSet].filter(
      (c) => !customersDuringFilterSet.has(c),
    ).length;

    churnRate =
      customersAtStart > 0 ? (customersLost / customersAtStart) * 100 : 0;

    // For comparison, calculate previous period churn
    const duration = filterEndDate.diff(filterStartDate, 'day');
    const prevPeriodEnd = filterStartDate.subtract(1, 'day');
    const prevPeriodStart = prevPeriodEnd.subtract(duration, 'day');

    const companiesBeforePrevPeriod = await Invoice.aggregate<{
      _id: Types.ObjectId;
    }>([
      {
        $match: {
          status: InvoiceStatus.PAID,
          issuedDate: { $lt: prevPeriodStart.toDate() },
          company: { $ne: excludedCompanyId },
        },
      },
      { $group: { _id: '$company' } },
    ]);

    const companiesDuringPrevPeriod = await Invoice.aggregate<{
      _id: Types.ObjectId;
    }>([
      {
        $match: {
          status: InvoiceStatus.PAID,
          issuedDate: {
            $gte: prevPeriodStart.toDate(),
            $lte: prevPeriodEnd.toDate(),
          },
          company: { $ne: excludedCompanyId },
        },
      },
      { $group: { _id: '$company' } },
    ]);

    const prevPeriodStartSet = new Set(
      companiesBeforePrevPeriod.map((d) => d._id.toString()),
    );
    const prevPeriodPaidSet = new Set(
      companiesDuringPrevPeriod.map((d) => d._id.toString()),
    );

    const prevPeriodCustomersAtStart = prevPeriodStartSet.size;
    const prevPeriodCustomersLost = [...prevPeriodStartSet].filter(
      (c) => !prevPeriodPaidSet.has(c),
    ).length;

    lastMonthChurn =
      prevPeriodCustomersAtStart > 0
        ? (prevPeriodCustomersLost / prevPeriodCustomersAtStart) * 100
        : 0;
  } else {
    // Original churn calculation logic
    const companiesBeforeThisMonth = await Invoice.aggregate<{
      _id: Types.ObjectId;
    }>([
      {
        $match: {
          status: InvoiceStatus.PAID,
          issuedDate: { $lt: startOfMonth },
          company: { $ne: excludedCompanyId },
        },
      },
      { $group: { _id: '$company' } },
    ]);
    const customersAtStartSet = new Set(
      companiesBeforeThisMonth.map((d) => d._id.toString()),
    );

    const companiesThisMonth = await Invoice.aggregate<{ _id: Types.ObjectId }>(
      [
        {
          $match: {
            status: InvoiceStatus.PAID,
            issuedDate: { $gte: startOfMonth, $lte: endOfMonth },
            company: { $ne: excludedCompanyId },
          },
        },
        { $group: { _id: '$company' } },
      ],
    );
    const customersThisMonthSet = new Set(
      companiesThisMonth.map((d) => d._id.toString()),
    );

    const customersAtStart = customersAtStartSet.size;
    const customersLost = [...customersAtStartSet].filter(
      (c) => !customersThisMonthSet.has(c),
    ).length;

    churnRate =
      customersAtStart > 0 ? (customersLost / customersAtStart) * 100 : 0;

    // Last month churn calculation
    const companiesBeforeLastMonth = await Invoice.aggregate<{
      _id: Types.ObjectId;
    }>([
      {
        $match: {
          status: InvoiceStatus.PAID,
          issuedDate: { $lt: startOfLastMonth },
          company: { $ne: excludedCompanyId },
        },
      },
      { $group: { _id: '$company' } },
    ]);
    const lastMonthStartSet = new Set(
      companiesBeforeLastMonth.map((d) => d._id.toString()),
    );

    const companiesDuringLastMonth = await Invoice.aggregate<{
      _id: Types.ObjectId;
    }>([
      {
        $match: {
          status: InvoiceStatus.PAID,
          issuedDate: { $gte: startOfLastMonth, $lte: endOfLastMonth },
          company: { $ne: excludedCompanyId },
        },
      },
      { $group: { _id: '$company' } },
    ]);
    const lastMonthPaidSet = new Set(
      companiesDuringLastMonth.map((d) => d._id.toString()),
    );

    const lastMonthCustomersAtStart = lastMonthStartSet.size;
    const lastMonthCustomersLost = [...lastMonthStartSet].filter(
      (c) => !lastMonthPaidSet.has(c),
    ).length;

    lastMonthChurn =
      lastMonthCustomersAtStart > 0
        ? (lastMonthCustomersLost / lastMonthCustomersAtStart) * 100
        : 0;
  }

  // --- Growth helpers ---
  const pct = (curr: number, prev: number) =>
    prev === 0 ? 0 : ((curr - prev) / prev) * 100;

  const clamp2 = (n: number) => Number(n.toFixed(2));

  const mrrGrowth = pct(MRR, lastMonthMRR);
  const arrGrowth = pct(ARR, lastMonthARR);
  const arpuGrowth = pct(ARPU, lastMonthARPU);
  const churnGrowth = pct(churnRate, lastMonthChurn);

  const gt = (g: number): GrowthType =>
    g > 0 ? 'increase' : g < 0 ? 'decrease' : 'neutral';

  return {
    mrr: {
      value: clamp2(MRR),
      growth: clamp2(Math.abs(mrrGrowth)),
      growthType: gt(mrrGrowth),
    },
    arr: {
      value: clamp2(ARR),
      growth: clamp2(Math.abs(arrGrowth)),
      growthType: gt(arrGrowth),
    },
    arpu: {
      value: clamp2(ARPU),
      growth: clamp2(Math.abs(arpuGrowth)),
      growthType: gt(arpuGrowth),
    },
    churnRate: {
      value: clamp2(churnRate),
      growth: clamp2(Math.abs(churnGrowth)),
      growthType: gt(churnGrowth),
    },
  };
};

export const getMonthlyDataPerformanceAllCompanies = async (
  filter?: DateFilter,
) => {
  const now = new Date();

  // Default: current month
  let startOfMonth = new Date(now.getFullYear(), now.getMonth(), 1, 0, 0, 0);
  let endOfMonth = new Date(now.getFullYear(), now.getMonth() + 1, 1, 0, 0, 0);

  // Default: previous month
  let startOfPrevMonth = new Date(
    now.getFullYear(),
    now.getMonth() - 1,
    1,
    0,
    0,
    0,
  );
  let endOfPrevMonth = new Date(now.getFullYear(), now.getMonth(), 1, 0, 0, 0);

  // Override with filter if provided
  if (filter?.startDate && filter?.endDate) {
    startOfMonth = new Date(filter.startDate);
    endOfMonth = new Date(filter.endDate);

    // For comparison, previous period same duration
    const duration = endOfMonth.getTime() - startOfMonth.getTime();
    startOfPrevMonth = new Date(startOfMonth.getTime() - duration);
    endOfPrevMonth = new Date(startOfMonth.getTime());
  }

  // Helper for change calculation
  function calcChange(current: number, prev: number) {
    if (prev === 0) return { changePercentage: 0, changeType: 'decrease' };
    const change = ((current - prev) / prev) * 100;
    return {
      changePercentage: Math.abs(change),
      changeType: change >= 0 ? 'increase' : 'decrease',
    };
  }

  // Count documents
  const [contactsAddedCurr, contactsAddedPrev] = await Promise.all([
    Contact.countDocuments({
      createdAt: { $gte: startOfMonth, $lt: endOfMonth },
    }),
    Contact.countDocuments({
      createdAt: { $gte: startOfPrevMonth, $lt: endOfPrevMonth },
    }),
  ]);

  const [leadsAddedCurr, leadsAddedPrev] = await Promise.all([
    Lead.countDocuments({ createdAt: { $gte: startOfMonth, $lt: endOfMonth } }),
    Lead.countDocuments({
      createdAt: { $gte: startOfPrevMonth, $lt: endOfPrevMonth },
    }),
  ]);

  const [tasksCreatedCurr, tasksCreatedPrev] = await Promise.all([
    Tasks.countDocuments({
      createdAt: { $gte: startOfMonth, $lt: endOfMonth },
    }),
    Tasks.countDocuments({
      createdAt: { $gte: startOfPrevMonth, $lt: endOfPrevMonth },
    }),
  ]);

  const [propertiesAddedCurr, propertiesAddedPrev] = await Promise.all([
    individualProperties.countDocuments({
      createdAt: { $gte: startOfMonth, $lt: endOfMonth },
    }),
    individualProperties.countDocuments({
      createdAt: { $gte: startOfPrevMonth, $lt: endOfPrevMonth },
    }),
  ]);

  const [projectsAddedCurr, projectsAddedPrev] = await Promise.all([
    Project.countDocuments({
      createdAt: { $gte: startOfMonth, $lt: endOfMonth },
    }),
    Project.countDocuments({
      createdAt: { $gte: startOfPrevMonth, $lt: endOfPrevMonth },
    }),
  ]);

  const [salesMadeCurr, salesMadePrev] = await Promise.all([
    Unit.countDocuments({
      status: 'sold',
      createdAt: { $gte: startOfMonth, $lt: endOfMonth },
    }),
    Unit.countDocuments({
      status: 'sold',
      createdAt: { $gte: startOfPrevMonth, $lt: endOfPrevMonth },
    }),
  ]);

  // Calculate changes
  const contactsChange = calcChange(contactsAddedCurr, contactsAddedPrev);
  const leadsChange = calcChange(leadsAddedCurr, leadsAddedPrev);
  const tasksChange = calcChange(tasksCreatedCurr, tasksCreatedPrev);
  const propertiesChange = calcChange(propertiesAddedCurr, propertiesAddedPrev);
  const projectsChange = calcChange(projectsAddedCurr, projectsAddedPrev);
  const salesChange = calcChange(salesMadeCurr, salesMadePrev);

  return {
    contactsAdded: {
      value: contactsAddedCurr,
      timeframe: 'This month',
      changePercentage: contactsChange.changePercentage,
      changeType: contactsChange.changeType,
    },
    leadsAdded: {
      value: leadsAddedCurr,
      timeframe: 'This month',
      changePercentage: leadsChange.changePercentage,
      changeType: leadsChange.changeType,
    },
    tasksCreated: {
      value: tasksCreatedCurr,
      timeframe: 'This month',
      changePercentage: tasksChange.changePercentage,
      changeType: tasksChange.changeType,
    },
    propertiesAdded: {
      value: propertiesAddedCurr,
      descriptor: 'Units + Individual',
      changePercentage: propertiesChange.changePercentage,
      changeType: propertiesChange.changeType,
    },
    projectsAdded: {
      value: projectsAddedCurr,
      timeframe: 'This month',
      changePercentage: projectsChange.changePercentage,
      changeType: projectsChange.changeType,
    },
    salesMade: {
      value: salesMadeCurr,
      descriptor: 'Total Units Sold',
      changePercentage: salesChange.changePercentage,
      changeType: salesChange.changeType,
    },
  };
};

export const getConversionRatios = async (filter?: DateFilter) => {
  const dateQuery: any = {};
  if (filter?.startDate && filter?.endDate)
    dateQuery.createdAt = {
      $gte: new Date(filter.startDate),
      $lte: new Date(filter.endDate),
    };

  // Counts
  const totalContacts = await Contact.countDocuments(dateQuery);
  const totalLeads = await Lead.countDocuments(dateQuery);

  const siteVisits = await Activity.countDocuments({
    type: ActivityType.SITE_VISIT,
    ...dateQuery,
  });

  const bookings = await Activity.countDocuments({
    type: ActivityType.BOOK,
    ...dateQuery,
  });

  // Derived ratios
  const convertedToLeads = totalLeads;
  const contactsToLeadsPct =
    totalContacts > 0 ? (convertedToLeads / totalContacts) * 100 : 0;
  const leadsToSiteVisitPct =
    totalLeads > 0 ? (siteVisits / totalLeads) * 100 : 0;
  const siteVisitToBookingPct =
    siteVisits > 0 ? (bookings / siteVisits) * 100 : 0;

  const defaultChange = 0;
  const defaultChangeType: 'increase' | 'decrease' | 'no change' = 'no change';

  return {
    contactsToLeads: {
      title: 'Contacts → Leads Ratio',
      subtitle: '% of contacts converted to leads',
      percentage: Number(contactsToLeadsPct.toFixed(1)),
      change: defaultChange,
      changeType: defaultChangeType,
      metrics: { totalContacts, convertedToLeads },
      chartData: [
        {
          name: 'Converted',
          value: Number(contactsToLeadsPct.toFixed(1)),
          color: '#10b981',
        },
        {
          name: 'Unconverted',
          value: Number((100 - contactsToLeadsPct).toFixed(1)),
          color: '#e5e7eb',
        },
      ],
    },
    leadsToSiteVisit: {
      title: 'Leads → Site Visit Ratio',
      subtitle: '% of leads converted to site visits',
      percentage: Number(leadsToSiteVisitPct.toFixed(1)),
      change: defaultChange,
      changeType: defaultChangeType,
      metrics: { totalLeads, siteVisits },
      chartData: [
        {
          name: 'Converted',
          value: Number(leadsToSiteVisitPct.toFixed(1)),
          color: '#3b82f6',
        },
        {
          name: 'Unconverted',
          value: Number((100 - leadsToSiteVisitPct).toFixed(1)),
          color: '#e5e7eb',
        },
      ],
    },
    siteVisitToBooking: {
      title: 'Site Visit → Booking Ratio',
      subtitle: '% of site visits converted to bookings',
      percentage: Number(siteVisitToBookingPct.toFixed(1)),
      change: defaultChange,
      changeType: defaultChangeType,
      metrics: { siteVisits, bookings },
      chartData: [
        {
          name: 'Converted',
          value: Number(siteVisitToBookingPct.toFixed(1)),
          color: '#f59e0b',
        },
        {
          name: 'Unconverted',
          value: Number((100 - siteVisitToBookingPct).toFixed(1)),
          color: '#e5e7eb',
        },
      ],
    },
  };
};

export const getLeadSources = async (filter?: DateFilter) => {
  const matchFilter: any = {};

  if (filter?.startDate || filter?.endDate) {
    matchFilter.createdAt = {};
    if (filter.startDate)
      matchFilter.createdAt.$gte = new Date(filter.startDate);
    if (filter.endDate) matchFilter.createdAt.$lte = new Date(filter.endDate);
  }

  const leadSources = await Lead.aggregate([
    { $match: matchFilter },
    {
      $lookup: {
        from: 'sources',
        localField: 'source',
        foreignField: '_id',
        as: 'source',
      },
    },
    { $unwind: '$source' },
    { $match: { 'source.isDefault': true } },
    {
      $lookup: {
        from: 'leadstages',
        localField: 'leadStage',
        foreignField: '_id',
        as: 'leadStage',
      },
    },
    { $unwind: '$leadStage' },
    {
      $group: {
        _id: '$source.name',
        leadsReceived: { $sum: 1 },
        salesMade: {
          $sum: {
            $cond: [{ $eq: ['$leadStage.stageName', 'Lead Won'] }, 1, 0],
          },
        },
      },
    },
    {
      $project: {
        _id: 0,
        source: '$_id',
        leadsReceived: 1,
        salesMade: 1,
        conversionRate: {
          $cond: [
            { $eq: ['$leadsReceived', 0] },
            0,
            {
              $round: [
                {
                  $multiply: [
                    { $divide: ['$salesMade', '$leadsReceived'] },
                    100,
                  ],
                },
                1,
              ],
            },
          ],
        },
      },
    },
    { $sort: { leadsReceived: -1 } },
  ]);

  return leadSources;
};

export const getUsageOverview = async (filter?: DateFilter) => {
  const query: any = {};

  // If date filter is provided, filter by createdAt
  if (filter?.startDate || filter?.endDate) {
    query.createdAt = {};
    if (filter.startDate) query.createdAt.$gte = new Date(filter.startDate);
    if (filter.endDate) query.createdAt.$lte = new Date(filter.endDate);
  }

  // Count documents in each collection
  const [
    leadsCount,
    projectsCount,
    propertiesCount,
    tasksCount,
    campaignsCount,
  ] = await Promise.all([
    Lead.countDocuments(query),
    Project.countDocuments(query),
    individualProperties.countDocuments(query),
    Tasks.countDocuments(query),
    Campaign.countDocuments(query),
  ]);

  const total =
    leadsCount + projectsCount + propertiesCount + tasksCount + campaignsCount;

  // Calculate percentage
  const getPercentage = (count: number) =>
    total === 0 ? 0 : Math.round((count / total) * 100);

  return [
    { feature: 'Leads', percentage: getPercentage(leadsCount) },
    { feature: 'Projects', percentage: getPercentage(projectsCount) },
    { feature: 'Tasks', percentage: getPercentage(tasksCount) },
    { feature: 'Campaigns', percentage: getPercentage(campaignsCount) },
    {
      feature: 'Other',
      percentage:
        100 -
        getPercentage(leadsCount) -
        getPercentage(projectsCount) -
        getPercentage(tasksCount) -
        getPercentage(campaignsCount),
    },
  ];
};

export const getExpiringSubscriptions = async (filter?: DateFilter) => {
  const companies = await Company.find().select('name planValidity').lean();

  const today = dayjs();
  const start = filter?.startDate ? dayjs(filter.startDate) : null;
  const end = filter?.endDate ? dayjs(filter.endDate) : null;

  const result = companies
    .map((c) => {
      if (!c.planValidity?.validfor || !c.planValidity?.validUnit) return null;

      // Calculate expiry date dynamically
      const expiryDate = today.add(
        c.planValidity.validfor,
        c.planValidity.validUnit,
      );

      const diffDays = expiryDate.diff(today, 'day');

      // Must be positive
      if (diffDays <= 0) return null;

      // Apply filter if provided
      if (
        (start && expiryDate.isBefore(start)) ||
        (end && expiryDate.isAfter(end))
      )
        return null;

      return { companyName: c.name, expiresIn: diffDays };
    })
    .filter(Boolean)
    .sort((a, b) => a!.expiresIn - b!.expiresIn)
    .slice(0, 5) as { companyName: string; expiresIn: number }[];

  return result;
};

export const getLowEngagementList = async (filter?: DateFilter) => {
  const today = dayjs();
  const start = filter?.startDate ? dayjs(filter.startDate) : null;
  const end = filter?.endDate ? dayjs(filter.endDate) : null;

  // Aggregate admins grouped by company
  const data = await User.aggregate([
    { $match: { userType: 'admin', isDeleted: false } },
    {
      $group: {
        _id: '$company.id',
        companyId: { $first: '$company.id' },
        lastActive: { $max: '$updatedAt' }, // latest activity of admins
      },
    },
    {
      $lookup: {
        from: 'companies',
        localField: 'companyId',
        foreignField: '_id',
        as: 'company',
      },
    },
    { $unwind: '$company' },
    {
      $project: {
        _id: 0,
        companyName: '$company.name',
        lastActive: 1,
      },
    },
  ]);

  // Transform + filter
  let result = data.map((c) => {
    const lastActive = dayjs(c.lastActive);
    const daysAgo = today.diff(lastActive, 'day');

    // Apply optional date filter
    if (start && lastActive.isBefore(start)) return null;
    if (end && lastActive.isAfter(end)) return null;

    let status = null;
    if (daysAgo >= 15 && daysAgo <= 30) status = 'Risk';
    if (daysAgo > 30) status = 'Dormant';

    if (!status) return null;

    return {
      companyName: c.companyName,
      lastActive: `${daysAgo} days ago`,
      status,
    };
  });

  // Clean nulls + sort + top 5
  result = result.filter(Boolean).sort((a, b) => {
    const da = parseInt(a!.lastActive.split(' ')[0], 10);
    const db = parseInt(b!.lastActive.split(' ')[0], 10);
    return db - da; // most inactive first
  });

  return result.slice(0, 5);
};

export const getTopActiveClients = async (filter?: DateFilter) => {
  const start = filter?.startDate ? dayjs(filter.startDate) : null;
  const end = filter?.endDate ? dayjs(filter.endDate) : null;

  // -------- Logins (admins only) --------
  const loginAgg = await User.aggregate([
    { $match: { userType: 'admin', isDeleted: false } },
    {
      $group: {
        _id: '$company.id',
        logins: { $sum: 1 },
        lastLogin: { $max: '$updatedAt' },
      },
    },
  ]);

  // -------- Leads --------
  const leadAgg = await Lead.aggregate([
    {
      $group: {
        _id: '$company',
        leads: { $sum: 1 },
      },
    },
  ]);

  // -------- Tasks --------
  const taskAgg = await Tasks.aggregate([
    {
      $group: {
        _id: '$companyId',
        tasks: { $sum: 1 },
      },
    },
  ]);

  // -------- Combine data --------
  const combined: Record<string, any> = {};

  loginAgg.forEach((item) => {
    if (!item._id) return;
    combined[item._id.toString()] = {
      companyId: item._id.toString(),
      logins: item.logins || 0,
      leads: 0,
      tasks: 0,
      lastLogin: item.lastLogin,
    };
  });

  leadAgg.forEach((item) => {
    if (!item._id) return;
    if (!combined[item._id.toString()])
      combined[item._id.toString()] = {
        companyId: item._id.toString(),
        logins: 0,
        leads: 0,
        tasks: 0,
      };

    combined[item._id.toString()].leads = item.leads || 0;
  });

  taskAgg.forEach((item) => {
    if (!item._id) return;
    if (!combined[item._id.toString()])
      combined[item._id.toString()] = {
        companyId: item._id.toString(),
        logins: 0,
        leads: 0,
        tasks: 0,
      };

    combined[item._id.toString()].tasks = item.tasks || 0;
  });

  // -------- Fetch company names --------
  const companyIds = Object.keys(combined).map((id) => id);
  const companies = await Company.find({ _id: { $in: companyIds } }).select(
    'name',
  );

  const companyMap = companies.reduce((acc, c) => {
    acc[c._id.toString()] = c.name;
    return acc;
  }, {} as Record<string, string>);

  // -------- Calculate total activities --------
  const totalActivity = Object.values(combined).reduce(
    (sum, c: any) => sum + (c.logins || 0) + (c.leads || 0) + (c.tasks || 0),
    0,
  );

  // -------- Final result --------
  let result = Object.values(combined)
    .map((c: any) => {
      // Skip if no company exists
      const companyName = companyMap[c.companyId];
      if (!companyName) return null;

      // Optional date filter based on lastLogin
      if (start && c.lastLogin && dayjs(c.lastLogin).isBefore(start)) return null;
      if (end && c.lastLogin && dayjs(c.lastLogin).isAfter(end)) return null;

      const activity = (c.logins || 0) + (c.leads || 0) + (c.tasks || 0);
      const trendPercentage = totalActivity
        ? Math.round((activity / totalActivity) * 100)
        : 0;

      return {
        companyName,
        trend: trendPercentage,
        trendType: 'up',
      };
    })
    .filter(Boolean) // Remove nulls
    .sort((a, b) => b!.trend - a!.trend);

  return result.slice(0, 5);
};