import { Injectable } from '@nestjs/common';
import { InjectRepository, InjectDataSource } from '@nestjs/typeorm';
import { Repository, DataSource } from 'typeorm';
import { ActivityLogs } from './entities/activity_logs.entity';
import geoip from 'geoip-lite';

@Injectable()
export class ActivityLogsService {
  constructor(
    @InjectRepository(ActivityLogs)
    private activityRepo: Repository<ActivityLogs>,
    @InjectDataSource()
    private dataSource: DataSource,
  ) {}

  // Normalize GeoIP lookup result to compact location object
  private getLocationFromIp(ip?: string | null): {
    country?: string;
    region?: string;
    city?: string;
    lat?: number;
    lon?: number;
    timezone?: string;
  } | null {
    try {
      if (!ip || typeof ip !== 'string') return null;
      // If multiple IPs (x-forwarded-for), take the first
      const cleanIp = ip.split(',')[0].trim();
      const res = geoip.lookup(cleanIp);
      if (!res) return null;
      const loc: any = {
        country: res.country || undefined,
        region: res.region || undefined,
        city: res.city || undefined,
        lat: Array.isArray(res.ll) ? res.ll[0] : undefined,
        lon: Array.isArray(res.ll) ? res.ll[1] : undefined,
        timezone: (res as any).timezone || undefined,
      };
      // Drop undefined fields for compact JSON
      Object.keys(loc).forEach(
        (k) => (loc as any)[k] === undefined && delete (loc as any)[k],
      );
      return Object.keys(loc).length ? loc : null;
    } catch {
      return null;
    }
  }

  // 1. Log single event (non-blocking)
  async log(logData: any, manager?: any): Promise<any> {
    try {
      // Normalize page URL from multiple possible sources
      const rawDetails = logData.actionDetails || {};
      let normalizedPageUrl =
        logData.pageUrl ||
        rawDetails.url ||
        rawDetails.pageName ||
        rawDetails.path ||
        null;
      // If '/' but details include a concrete URL (e.g., job/company), prefer that
      if (
        normalizedPageUrl === '/' &&
        typeof rawDetails.url === 'string' &&
        rawDetails.url.trim() !== ''
      ) {
        normalizedPageUrl = rawDetails.url;
      }
      // GeoIP enrichment
      const location = this.getLocationFromIp(logData.ipAddress);

      // Dynamic credit tracking: if billable and credits_used is missing/default,
      // try to fetch from wallet_transactions
      let finalCreditsUsed = logData.credits_used || 0;
      if (logData.is_billable && finalCreditsUsed <= 0 && logData.userId) {
        try {
          const creditTypeMapping: Record<string, string> = {
            job_create: 'job',
            job_clone: 'job',
            cv_download: 'database',
            candidate_view: 'database',
            profile_unlock: 'database',
            excel_download: 'database',
          };
          const creditType =
            creditTypeMapping[logData.actionType] || 'database';

          // Use manager for query if provided, else dataSource
          const queryExecutor = manager || this.dataSource;

          const transResult = await queryExecutor.query(
            `
            SELECT amount 
            FROM wallet_transactions wt
            JOIN wallets w ON wt.wallet_id = w.id
            WHERE w.user_id = ? 
              AND wt.type = 'debit'
              AND wt.credit_type = ?
              AND wt.created_at > NOW() - INTERVAL 5 MINUTE
            ORDER BY wt.created_at DESC
            LIMIT 1
          `,
            [logData.userId, creditType],
          );

          if (transResult && transResult.length > 0) {
            finalCreditsUsed = Math.abs(transResult[0].amount);
          }
        } catch (dbError) {
          console.error('Failed to fetch dynamic credits:', dbError);
        }
      }

      const log = this.activityRepo.create({
        user_id: logData.userId || null,
        user_type: logData.userType || 'guest',
        visitor_id: logData.visitorId || null,
        action_type: logData.actionType,
        action_name: logData.actionName,
        page_url: normalizedPageUrl,
        action_details:
          logData.actionDetails || location
            ? {
                ...(logData.actionDetails || {}),
                ...(location ? { location } : {}),
              }
            : null,
        job_id: logData.jobId || null,
        company_id: logData.companyId || null,
        application_id: logData.applicationId || null,
        status: logData.status || 'success',
        error_message: logData.errorMessage || null,
        ip_address: logData.ipAddress || null,
        device_type: logData.deviceType || 'desktop',
        browser: logData.browser || null,
        is_billable: logData.is_billable || false,
        credits_used: finalCreditsUsed,
      });

      if (manager) {
        await manager.save(ActivityLogs, log);
      } else {
        await this.activityRepo.save(log);
      }
      return { success: true };
    } catch (error) {
      console.error('Activity log error (non-critical):', error);
      // Never throw - logging should not break the app
      return { success: false, error: error.message };
    }
  }

  // 2. Batch logging (for performance)
  async logBatch(logs: any[]): Promise<any> {
    try {
      const entities = logs.map((logData) => {
        const rawDetails = logData.actionDetails || {};
        let normalizedPageUrl =
          logData.pageUrl ||
          rawDetails.url ||
          rawDetails.pageName ||
          rawDetails.path ||
          null;
        if (
          normalizedPageUrl === '/' &&
          typeof rawDetails.url === 'string' &&
          rawDetails.url.trim() !== ''
        ) {
          normalizedPageUrl = rawDetails.url;
        }
        const location = this.getLocationFromIp(logData.ipAddress);
        return this.activityRepo.create({
          user_id: logData.userId || null,
          user_type: logData.userType || 'guest',
          visitor_id: logData.visitorId || null,
          action_type: logData.actionType,
          action_name: logData.actionName,
          page_url: normalizedPageUrl,
          action_details:
            logData.actionDetails || location
              ? {
                  ...(logData.actionDetails || {}),
                  ...(location ? { location } : {}),
                }
              : null,
          job_id: logData.jobId || null,
          company_id: logData.companyId || null,
          application_id: logData.applicationId || null,
          status: logData.status || 'success',
          error_message: logData.errorMessage || null,
          ip_address: logData.ipAddress || null,
          device_type: logData.deviceType || 'desktop',
          browser: logData.browser || null,
        });
      });

      await this.activityRepo.save(entities);
      return { success: true, count: entities.length };
    } catch (error) {
      console.error('Batch activity log error:', error);
      return { success: false, error: error.message };
    }
  }

  // Helper to build date filter (IST timezone aware)
  /**
   * Build date filter for TypeORM queries.
   * Assumes IST as both DB and system timezone (confirmed by user).
   */
  private async buildDateFilter(
    dateRange?: number,
    from?: string,
    to?: string,
  ): Promise<{
    sqlFilter: string;
    params: any;
  }> {
    // Note: IST + IST setup allows for direct comparison.
    // We default to IST behavior but keep a streamlined query logic.

    if (from && to) {
      const fromDate = from.split('T')[0];
      const toDate = to.split('T')[0];

      if (fromDate === toDate) {
        // Same day: Use DATE() function for exact day match
        return {
          sqlFilter:
            "DATE(log.created_at) = STR_TO_DATE(:fromDate, '%Y-%m-%d')",
          params: { fromDate },
        };
      }

      const fromIST = `${fromDate} 00:00:00`;
      const toIST = `${toDate} 23:59:59`;
      return {
        sqlFilter:
          "log.created_at >= STR_TO_DATE(:fromIST, '%Y-%m-%d %H:%i:%s') AND log.created_at <= STR_TO_DATE(:toIST, '%Y-%m-%d %H:%i:%s')",
        params: { fromIST, toIST },
      };
    } else if (from) {
      const fromDate = from.split('T')[0];
      const fromIST = `${fromDate} 00:00:00`;
      return {
        sqlFilter:
          "log.created_at >= STR_TO_DATE(:fromIST, '%Y-%m-%d %H:%i:%s')",
        params: { fromIST },
      };
    } else if (to) {
      const toDate = to.split('T')[0];
      const toIST = `${toDate} 23:59:59`;
      return {
        sqlFilter: "log.created_at <= STR_TO_DATE(:toIST, '%Y-%m-%d %H:%i:%s')",
        params: { toIST },
      };
    } else {
      const range = dateRange || 30;
      return {
        sqlFilter: `log.created_at >= DATE_SUB(NOW(), INTERVAL :range DAY)`,
        params: { range },
      };
    }
  }

  /**
   * Helper to build date filter for raw SQL queries.
   * Assumes IST as both DB and system timezone.
   */
  private async buildDateFilterSQL(
    dateRange?: number,
    from?: string,
    to?: string,
  ): Promise<string> {
    if (from && to) {
      const fromDate = from.split('T')[0];
      const toDate = to.split('T')[0];

      if (fromDate === toDate) {
        return `DATE(created_at) = '${fromDate}'`;
      }

      const fromIST = `${fromDate} 00:00:00`;
      const toIST = `${toDate} 23:59:59`;
      return `created_at >= '${fromIST}' AND created_at <= '${toIST}'`;
    } else if (from) {
      const fromDate = from.split('T')[0];
      return `created_at >= '${fromDate} 00:00:00'`;
    } else if (to) {
      const toDate = to.split('T')[0];
      return `created_at <= '${toDate} 23:59:59'`;
    } else {
      const range = dateRange || 30;
      return `created_at >= DATE_SUB(NOW(), INTERVAL ${range} DAY)`;
    }
  }

  // 3. Get activity stats
  async getActivityStats(
    dateRange?: number,
    from?: string,
    to?: string,
  ): Promise<any> {
    try {
      const dateFilter = await this.buildDateFilterSQL(dateRange, from, to);

      // Total visitors - get guest and registered separately
      const guestVisitorsResult = await this.dataSource.query(`
        SELECT COUNT(DISTINCT visitor_id) as guest_visitors
        FROM user_activity_logs
        WHERE ${dateFilter}
          AND user_id IS NULL
          AND visitor_id IS NOT NULL
          AND (page_url IS NULL OR (
            page_url NOT LIKE '/admin%'
            AND page_url NOT LIKE '%/admin/%'
            AND page_url NOT LIKE '%/adminpanel%'
            AND page_url NOT LIKE '%/superadmin%'
            AND page_url NOT LIKE '%admin-access%'
          ))
      `);

      const registeredVisitorsResult = await this.dataSource.query(`
        SELECT COUNT(DISTINCT user_id) as registered_visitors
        FROM user_activity_logs
        WHERE ${dateFilter}
          AND user_id IS NOT NULL
          AND user_type != 'admin'
          AND (page_url IS NULL OR (
            page_url NOT LIKE '/admin%'
            AND page_url NOT LIKE '%/admin/%'
            AND page_url NOT LIKE '%/adminpanel%'
            AND page_url NOT LIKE '%/superadmin%'
            AND page_url NOT LIKE '%admin-access%'
          ))
      `);

      const guestVisitors = parseInt(
        guestVisitorsResult[0]?.guest_visitors || 0,
      );
      const registeredVisitors = parseInt(
        registeredVisitorsResult[0]?.registered_visitors || 0,
      );
      const totalVisitors = guestVisitors + registeredVisitors;

      // Total page views
      const qbPageViews = this.activityRepo
        .createQueryBuilder('log')
        .where('log.action_type = :type', { type: 'page_view' })
        .andWhere('log.user_type != :admin', { admin: 'admin' });
      if (from && to) {
        qbPageViews
          .andWhere('log.created_at >= :from', { from })
          .andWhere('log.created_at <= :to', { to });
      } else if (from) {
        qbPageViews.andWhere('log.created_at >= :from', { from });
      } else if (to) {
        qbPageViews.andWhere('log.created_at <= :to', { to });
      } else {
        qbPageViews.andWhere(
          'log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)',
          {
            days: dateRange || 30,
          },
        );
      }
      const totalPageViews = await qbPageViews.getCount();

      // Total actions
      const qbActions = this.activityRepo
        .createQueryBuilder('log')
        .where('log.user_type != :admin', { admin: 'admin' });
      if (from && to) {
        qbActions
          .andWhere('log.created_at >= :from', { from })
          .andWhere('log.created_at <= :to', { to });
      } else if (from) {
        qbActions.andWhere('log.created_at >= :from', { from });
      } else if (to) {
        qbActions.andWhere('log.created_at <= :to', { to });
      } else {
        qbActions.andWhere(
          'log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)',
          {
            days: dateRange || 30,
          },
        );
      }
      const totalActions = await qbActions.getCount();

      // Failed actions
      const qbFailed = this.activityRepo
        .createQueryBuilder('log')
        .where('log.status = :failed', { failed: 'failed' })
        .andWhere('log.user_type != :admin', { admin: 'admin' });
      if (from && to) {
        qbFailed
          .andWhere('log.created_at >= :from', { from })
          .andWhere('log.created_at <= :to', { to });
      } else if (from) {
        qbFailed.andWhere('log.created_at >= :from', { from });
      } else if (to) {
        qbFailed.andWhere('log.created_at <= :to', { to });
      } else {
        qbFailed.andWhere(
          'log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)',
          {
            days: dateRange || 30,
          },
        );
      }
      const failedActions = await qbFailed.getCount();

      // Job views
      const qbJobViews = this.activityRepo
        .createQueryBuilder('log')
        .where('log.action_type = :type', { type: 'job_details_view' })
        .andWhere('log.user_type != :admin', { admin: 'admin' });
      if (from && to) {
        qbJobViews
          .andWhere('log.created_at >= :from', { from })
          .andWhere('log.created_at <= :to', { to });
      } else if (from) {
        qbJobViews.andWhere('log.created_at >= :from', { from });
      } else if (to) {
        qbJobViews.andWhere('log.created_at <= :to', { to });
      } else {
        qbJobViews.andWhere(
          'log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)',
          {
            days: dateRange || 30,
          },
        );
      }
      const jobViews = await qbJobViews.getCount();

      // Job applications
      const qbJobApps = this.activityRepo
        .createQueryBuilder('log')
        .where('(log.action_type = :type1 OR log.action_type = :type2)', {
          type1: 'job_apply_complete',
          type2: 'job_apply_submit',
        })
        .andWhere('log.user_type != :admin', { admin: 'admin' });
      if (from && to) {
        qbJobApps
          .andWhere('log.created_at >= :from', { from })
          .andWhere('log.created_at <= :to', { to });
      } else if (from) {
        qbJobApps.andWhere('log.created_at >= :from', { from });
      } else if (to) {
        qbJobApps.andWhere('log.created_at <= :to', { to });
      } else {
        qbJobApps.andWhere(
          'log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)',
          {
            days: dateRange || 30,
          },
        );
      }
      const jobApplications = await qbJobApps.getCount();

      // Activity breakdown by type
      const qbActivity = this.dataSource
        .createQueryBuilder()
        .select('action_type', 'actionType')
        .addSelect('action_name', 'actionName')
        .addSelect('COUNT(*)', 'count')
        .from('user_activity_logs', 'log')
        .andWhere('user_type != :admin', { admin: 'admin' });
      if (from && to) {
        qbActivity
          .where('created_at >= :from', { from })
          .andWhere('created_at <= :to', { to });
      } else if (from) {
        qbActivity.where('created_at >= :from', { from });
      } else if (to) {
        qbActivity.where('created_at <= :to', { to });
      } else {
        qbActivity.where('created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)', {
          days: dateRange || 30,
        });
      }
      const activityBreakdown = await qbActivity
        .groupBy('action_type')
        .addGroupBy('action_name')
        .orderBy('count', 'DESC')
        .getRawMany();

      // User type breakdown
      const qbUserType = this.dataSource
        .createQueryBuilder()
        .select('user_type', 'userType')
        .addSelect(
          'COUNT(DISTINCT CASE WHEN user_id IS NULL THEN visitor_id ELSE user_id END)',
          'count',
        )
        .from('user_activity_logs', 'log')
        .andWhere('user_type != :admin', { admin: 'admin' })
        .andWhere(
          '(page_url IS NULL OR (page_url NOT LIKE :adminRoot AND page_url NOT LIKE :adminInfix AND page_url NOT LIKE :adminPanel AND page_url NOT LIKE :superAdmin AND page_url NOT LIKE :adminAccess))',
          {
            adminRoot: '/admin%',
            adminInfix: '%/admin/%',
            adminPanel: '%/adminpanel%',
            superAdmin: '%/superadmin%',
            adminAccess: '%admin-access%',
          },
        );
      if (from && to) {
        qbUserType
          .where('created_at >= :from', { from })
          .andWhere('created_at <= :to', { to });
      } else if (from) {
        qbUserType.where('created_at >= :from', { from });
      } else if (to) {
        qbUserType.where('created_at <= :to', { to });
      } else {
        qbUserType.where('created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)', {
          days: dateRange || 30,
        });
      }
      const userTypeBreakdown = await qbUserType
        .groupBy('user_type')
        .getRawMany();

      // Daily activity trend
      const qbDaily = this.dataSource
        .createQueryBuilder()
        .select('DATE(created_at)', 'date')
        .addSelect('COUNT(*)', 'count')
        .from('user_activity_logs', 'log')
        .andWhere('user_type != :admin', { admin: 'admin' });
      if (from && to) {
        qbDaily
          .where('created_at >= :from', { from })
          .andWhere('created_at <= :to', { to });
      } else if (from) {
        qbDaily.where('created_at >= :from', { from });
      } else if (to) {
        qbDaily.where('created_at <= :to', { to });
      } else {
        qbDaily.where('created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)', {
          days: dateRange || 30,
        });
      }
      const dailyTrend = await qbDaily
        .groupBy('date')
        .orderBy('date', 'ASC')
        .getRawMany();

      // Average session duration (in seconds): average of per-visitor/user spans
      // Session grouping key: user_id if present, else visitor_id
      const avgSessionResult = await this.dataSource.query(`
        SELECT AVG(LEAST(session_span_seconds, 1800)) as avg_seconds
        FROM (
          SELECT 
            COALESCE(CAST(user_id AS CHAR), visitor_id) as session_key,
            TIMESTAMPDIFF(SECOND, MIN(created_at), MAX(created_at)) as session_span_seconds
          FROM user_activity_logs
          WHERE ${dateFilter}
            AND (user_id IS NOT NULL OR visitor_id IS NOT NULL)
            AND user_type != 'admin'
            AND (page_url IS NULL OR (
              page_url NOT LIKE '/admin%'
              AND page_url NOT LIKE '%/admin/%'
              AND page_url NOT LIKE '%/adminpanel%'
              AND page_url NOT LIKE '%/superadmin%'
              AND page_url NOT LIKE '%admin-access%'
            ))
          GROUP BY COALESCE(CAST(user_id AS CHAR), visitor_id)
        ) as session_spans
      `);

      const averageSessionDurationSeconds =
        parseFloat(avgSessionResult?.[0]?.avg_seconds ?? 0) || 0;

      // Calculate percentages for activity breakdown
      const totalCount = activityBreakdown.reduce(
        (sum: number, item: any) => sum + parseInt(item.count || 0),
        0,
      );
      const activityBreakdownWithPercentage = activityBreakdown.map(
        (item: any) => ({
          ...item,
          action_type: item.actionType || item.action_type,
          action_name: item.actionName || item.action_name,
          count: parseInt(item.count || 0),
          percentage:
            totalCount > 0
              ? ((parseInt(item.count || 0) / totalCount) * 100).toFixed(2)
              : '0.00',
        }),
      );

      return {
        success: true,
        data: {
          total_visitors: totalVisitors,
          guest_visitors: guestVisitors,
          registered_visitors: registeredVisitors,
          total_page_views: totalPageViews,
          total_actions: totalActions,
          failed_actions: failedActions,
          average_session_duration_seconds: Math.max(
            0,
            Math.round(averageSessionDurationSeconds),
          ),
          job_views: jobViews,
          job_applications: jobApplications,
          activityBreakdown: activityBreakdownWithPercentage,
          userTypeBreakdown: (() => {
            const normalized = userTypeBreakdown.map((item: any) => ({
              user_type: item.userType || item.user_type,
              count: parseInt(item.count || 0),
            }));
            // Overwrite guest count to align with guest_visitors (after admin filtering)
            const idx = normalized.findIndex((x) => x.user_type === 'guest');
            if (idx >= 0) normalized[idx].count = guestVisitors;
            else if (guestVisitors > 0) {
              normalized.push({
                user_type: 'guest',
                count: guestVisitors,
              });
            }
            return normalized;
          })(),
          dailyTrend,
        },
      };
    } catch (error) {
      console.error('Error fetching activity stats:', error);
      return { success: false, error: error.message };
    }
  }

  // 4. Get user journey
  async getUserJourney(userId: number, limit: number = 50): Promise<any> {
    try {
      const journey = await this.activityRepo.find({
        where: { user_id: userId },
        order: { created_at: 'DESC' },
        take: limit,
      });

      return { success: true, data: journey };
    } catch (error) {
      console.error('Error fetching user journey:', error);
      return { success: false, error: error.message };
    }
  }

  // 5. Get guest journey by visitor_id
  async getGuestJourney(visitorId: string, limit: number = 50): Promise<any> {
    try {
      const journey = await this.activityRepo.find({
        where: { visitor_id: visitorId },
        order: { created_at: 'DESC' },
        take: limit,
      });

      return { success: true, data: journey };
    } catch (error) {
      console.error('Error fetching guest journey:', error);
      return { success: false, error: error.message };
    }
  }

  // 6. Get popular pages
  async getPopularPages(
    dateRange?: number,
    limit: number = 20,
    from?: string,
    to?: string,
  ): Promise<any> {
    try {
      const qb = this.dataSource
        .createQueryBuilder()
        .select('page_url', 'url')
        .addSelect('COUNT(*)', 'views')
        .from('user_activity_logs', 'log')
        .where('action_type IN (:...types)', {
          types: ['page_view', 'job_details_view', 'company_details_view'],
        })
        .andWhere('user_type != :adminUserType', { adminUserType: 'admin' })
        .andWhere('page_url IS NOT NULL')
        .andWhere('page_url NOT LIKE :adminRoot', { adminRoot: '/admin%' })
        .andWhere('page_url NOT LIKE :adminInfix', { adminInfix: '%/admin/%' })
        .andWhere('page_url NOT LIKE :dashboardInfix', {
          dashboardInfix: '%/dashboard%',
        })
        .andWhere('page_url NOT LIKE :adminPanel', {
          adminPanel: '%/adminpanel%',
        })
        .andWhere('page_url NOT LIKE :superAdmin', {
          superAdmin: '%/superadmin%',
        });
      if (from && to) {
        qb.andWhere('created_at >= :from', { from }).andWhere(
          'created_at <= :to',
          { to },
        );
      } else if (from) {
        qb.andWhere('created_at >= :from', { from });
      } else if (to) {
        qb.andWhere('created_at <= :to', { to });
      } else {
        qb.andWhere('created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)', {
          days: dateRange || 30,
        });
      }
      const pages = await qb
        .groupBy('page_url')
        .orderBy('views', 'DESC')
        .limit(limit)
        .getRawMany();

      return { success: true, data: pages };
    } catch (error) {
      console.error('Error fetching popular pages:', error);
      return { success: false, error: error.message };
    }
  }

  // 7. Get conversion funnel
  async getConversionFunnel(
    funnelType: string = 'job_application',
    dateRange?: number,
    from?: string,
    to?: string,
  ): Promise<any> {
    try {
      let steps: string[] = [];

      if (funnelType === 'job_application') {
        steps = [
          'job_details_view',
          'apply_button_click',
          'job_apply_phone_entered',
          'otp_request',
          'otp_verify',
          'job_apply_complete',
        ];
      } else if (funnelType === 'auth') {
        steps = [
          'signin_button_click',
          'auth_modal_open',
          'auth_phone_entered',
          'otp_request',
          'otp_verify',
          'auth_signin',
        ];
      }

      // Check if from and to are the same day (today filter)
      const isToday = from && to && from.split('T')[0] === to.split('T')[0];

      const funnelData = await Promise.all(
        steps.map(async (step) => {
          const qb = this.activityRepo
            .createQueryBuilder('log')
            .where('log.action_type = :step', { step })
            .andWhere('log.user_type != :admin', { admin: 'admin' });
          if (from && to) {
            if (isToday) {
              // If same day, use DATE comparison for IST
              qb.andWhere('DATE(log.created_at) = DATE(:from)', { from });
            } else {
              qb.andWhere('log.created_at >= :from', { from }).andWhere(
                'log.created_at <= :to',
                { to },
              );
            }
          } else if (from) {
            qb.andWhere('log.created_at >= :from', { from });
          } else if (to) {
            qb.andWhere('log.created_at <= :to', { to });
          } else {
            const range = dateRange || 30;
            qb.andWhere(
              'log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)',
              {
                days: range,
              },
            );
          }
          const count = await qb.getCount();
          return { step, count };
        }),
      );

      return { success: true, data: funnelData };
    } catch (error) {
      console.error('Error fetching conversion funnel:', error);
      return { success: false, error: error.message };
    }
  }

  // 8. Get abandoned applications with phone numbers
  async getAbandonedApplications(
    dateRange?: number,
    from?: string,
    to?: string,
  ): Promise<any> {
    try {
      const qb = this.activityRepo
        .createQueryBuilder('log')
        .where('log.status = :status', { status: 'abandoned' })
        .andWhere('log.action_type LIKE :type', { type: '%abandoned%' })
        .andWhere('log.user_type != :admin', { admin: 'admin' });
      if (from && to) {
        qb.andWhere('log.created_at >= :from', { from }).andWhere(
          'log.created_at <= :to',
          { to },
        );
      } else if (from) {
        qb.andWhere('log.created_at >= :from', { from });
      } else if (to) {
        qb.andWhere('log.created_at <= :to', { to });
      } else {
        qb.andWhere('log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)', {
          days: dateRange || 7,
        });
      }
      const abandoned = await qb.orderBy('log.created_at', 'DESC').getMany();

      // Filter those with phone numbers and format response
      const withPhone = abandoned
        .filter((log) => log.action_details && log.action_details.phone)
        .map((log) => ({
          id: log.id,
          visitorId: log.visitor_id,
          userId: log.user_id,
          phone: log.action_details.phone,
          abandonedAt: log.action_details.abandonedAt,
          reason: log.action_details.reason,
          timeSpent: log.action_details.timeSpent,
          jobId: log.job_id,
          companyId: log.company_id,
          actionType: log.action_type,
          createdAt: log.created_at,
        }));

      return { success: true, data: withPhone };
    } catch (error) {
      console.error('Error fetching abandoned applications:', error);
      return { success: false, error: error.message };
    }
  }

  // 9. Get failed OTP attempts
  async getFailedOTPAttempts(
    dateRange?: number,
    from?: string,
    to?: string,
  ): Promise<any> {
    try {
      const normalizedFrom = from ? this.normalizeDate(from, false) : undefined;
      const normalizedTo = to ? this.normalizeDate(to, true) : undefined;

      const qb = this.activityRepo
        .createQueryBuilder('log')
        .where(
          'log.action_type LIKE :otpPattern AND (log.status = :failedStatus OR log.action_type LIKE :otpFailed OR log.action_type = :otpFailedExact)',
          {
            otpPattern: '%otp%',
            failedStatus: 'failed',
            otpFailed: '%otp%failed%',
            otpFailedExact: 'otp_failed',
          },
        )
        .andWhere('log.user_type != :admin', { admin: 'admin' });

      if (normalizedFrom && normalizedTo) {
        qb.andWhere('log.created_at >= :from', {
          from: normalizedFrom,
        }).andWhere('log.created_at <= :to', { to: normalizedTo });
      } else if (normalizedFrom) {
        qb.andWhere('log.created_at >= :from', { from: normalizedFrom });
      } else if (normalizedTo) {
        qb.andWhere('log.created_at <= :to', { to: normalizedTo });
      } else {
        qb.andWhere('log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)', {
          days: dateRange || 7,
        });
      }
      const failed = await qb.orderBy('log.created_at', 'DESC').getMany();

      return {
        success: true,
        data: failed.map((log) => {
          const details = log.action_details || {};
          return {
            id: log.id,
            phone: details.phone || null,
            email: details.email || null,
            method:
              details.method ||
              (details.email ? 'email' : details.phone ? 'sms' : 'unknown'),
            error_message:
              log.error_message ||
              details.error ||
              details.error_message ||
              'OTP verification failed',
            attempts: details.attempts || 1,
            created_at: log.created_at,
          };
        }),
      };
    } catch (error) {
      console.error('Error fetching failed OTP attempts:', error);
      return { success: false, error: error.message };
    }
  }

  // 10. Get IP-wise statistics
  async getIpWiseStatistics(
    dateRange?: number,
    from?: string,
    to?: string,
  ): Promise<any> {
    try {
      const qb = this.activityRepo
        .createQueryBuilder('log')
        .select('log.ip_address', 'ip_address')
        .addSelect('COUNT(*)', 'total_actions')
        .addSelect('COUNT(DISTINCT log.visitor_id)', 'unique_visitors')
        .addSelect('COUNT(DISTINCT log.user_id)', 'unique_users')
        .addSelect('MIN(log.created_at)', 'first_seen')
        .addSelect('MAX(log.created_at)', 'last_seen')
        .addSelect('GROUP_CONCAT(DISTINCT log.device_type)', 'device_types')
        .addSelect('GROUP_CONCAT(DISTINCT log.browser)', 'browsers')
        .addSelect('GROUP_CONCAT(DISTINCT log.user_type)', 'user_types')
        .where('log.ip_address IS NOT NULL')
        .andWhere('log.ip_address != ""')
        .andWhere('log.user_type != :admin', { admin: 'admin' });
      if (from && to) {
        qb.andWhere('log.created_at >= :from', { from }).andWhere(
          'log.created_at <= :to',
          { to },
        );
      } else if (from) {
        qb.andWhere('log.created_at >= :from', { from });
      } else if (to) {
        qb.andWhere('log.created_at <= :to', { to });
      } else {
        qb.andWhere('log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)', {
          days: dateRange || 30,
        });
      }
      const ipStats = await qb
        .groupBy('log.ip_address')
        .orderBy('total_actions', 'DESC')
        .getRawMany();

      return {
        success: true,
        data: ipStats.map((stat) => ({
          ip_address: stat.ip_address,
          total_actions: parseInt(stat.total_actions || 0),
          unique_visitors: parseInt(stat.unique_visitors || 0),
          unique_users: parseInt(stat.unique_users || 0),
          first_seen: stat.first_seen,
          last_seen: stat.last_seen,
          device_types: stat.device_types
            ? stat.device_types
                .split(',')
                .filter(
                  (d: string, i: number, arr: string[]) => arr.indexOf(d) === i,
                )
            : [],
          browsers: stat.browsers
            ? stat.browsers
                .split(',')
                .filter(
                  (b: string, i: number, arr: string[]) => arr.indexOf(b) === i,
                )
                .slice(0, 3)
            : [],
          user_types: stat.user_types
            ? stat.user_types
                .split(',')
                .filter(
                  (u: string, i: number, arr: string[]) => arr.indexOf(u) === i,
                )
            : [],
        })),
      };
    } catch (error) {
      console.error('Error fetching IP-wise statistics:', error);
      return { success: false, error: error.message };
    }
  }

  // 11. Get security/access logs
  async getSecurityLogs(
    dateRange?: number,
    from?: string,
    to?: string,
  ): Promise<any> {
    try {
      const securityActionTypes = [
        'auth_login',
        'auth_signin',
        'auth_signup',
        'auth_logout',
        'auth_failed',
        'access_denied',
        'unauthorized_access',
        'permission_denied',
        'session_expired',
        'invalid_token',
        'password_reset',
        'account_locked',
      ];

      // Log date range for debugging
      // if (from && to) {
      //   const fromDate = from.split('T')[0];
      //   const toDate = to.split('T')[0];
      //   console.log(
      //     `[Security Logs] Date range: ${fromDate} 00:00:00 to ${toDate} 23:59:59`,
      //   );
      // }

      // Use timezone-aware date filter SQL
      const dateFilterSQL = await this.buildDateFilterSQL(dateRange, from, to);

      // Build raw SQL query with proper date filtering
      const typesPlaceholders = securityActionTypes.map(() => '?').join(',');
      const sql = `
        SELECT 
          id,
          action_name,
          action_type,
          user_id,
          user_type,
          visitor_id,
          ip_address,
          created_at,
          status,
          error_message,
          page_url,
          device_type,
          browser,
          action_details
        FROM user_activity_logs
        WHERE (
          action_type IN (${typesPlaceholders})
          OR action_type LIKE ?
          OR action_type LIKE ?
          OR action_type LIKE ?
          OR action_type LIKE ?
          OR status = ?
        )
        AND (user_type != ? OR status = ?)
        AND (${dateFilterSQL})
        ORDER BY created_at DESC
        LIMIT 1000
      `;

      const params = [
        ...securityActionTypes,
        '%login%',
        '%auth%',
        '%access%',
        '%denied%',
        'failed',
        'admin',
        'failed',
      ];

      const securityLogs = await this.dataSource.query(sql, params);

      return {
        success: true,
        data: securityLogs.map((log: any) => ({
          id: log.id,
          event: log.action_name || log.action_type,
          event_type: log.action_type,
          user_id: log.user_id,
          user_type: log.user_type,
          visitor_id: log.visitor_id,
          ip_address: log.ip_address,
          timestamp: log.created_at,
          status:
            log.status === 'failed'
              ? 'Failed'
              : log.status === 'success'
                ? 'Success'
                : log.status,
          error_message: log.error_message,
          page_url: log.page_url,
          device_type: log.device_type,
          browser: log.browser,
          action_details: log.action_details,
        })),
      };
    } catch (error) {
      console.error('Error fetching security logs:', error);
      return { success: false, error: (error as any).message };
    }
  }

  // 12. Get page time statistics (avg seconds per page)
  async getPageTimeStats(
    dateRange?: number,
    from?: string,
    to?: string,
  ): Promise<any> {
    try {
      const qb = this.dataSource
        .createQueryBuilder()
        .select('page_url', 'page_url')
        .addSelect('COUNT(*)', 'events')
        .addSelect(
          "AVG(COALESCE(JSON_EXTRACT(action_details, '$.timeSpentSeconds'), 0))",
          'avg_seconds',
        )
        .from('user_activity_logs', 'log')
        .where("action_type = 'time_on_page'")
        .andWhere('user_type != :admin', { admin: 'admin' })
        .andWhere(
          '(page_url IS NOT NULL AND page_url NOT LIKE :adminRoot AND page_url NOT LIKE :adminInfix AND page_url NOT LIKE :adminPanel AND page_url NOT LIKE :superAdmin AND page_url NOT LIKE :adminAccess)',
          {
            adminRoot: '/admin%',
            adminInfix: '%/admin/%',
            adminPanel: '%/adminpanel%',
            superAdmin: '%/superadmin%',
            adminAccess: '%admin-access%',
          },
        );
      if (from && to) {
        qb.andWhere('created_at >= :from', { from }).andWhere(
          'created_at <= :to',
          { to },
        );
      } else if (from) {
        qb.andWhere('created_at >= :from', { from });
      } else if (to) {
        qb.andWhere('created_at <= :to', { to });
      } else {
        qb.andWhere('created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)', {
          days: dateRange || 30,
        });
      }
      const rows = await qb
        .groupBy('page_url')
        .orderBy('avg_seconds', 'DESC')
        .limit(50)
        .getRawMany();

      return {
        success: true,
        data: rows.map((r: any) => ({
          page_url: r.page_url,
          avg_seconds: Math.round(parseFloat(r.avg_seconds || 0)),
          events: parseInt(r.events || 0),
        })),
      };
    } catch (error) {
      console.error('Error fetching page time stats:', error);
      return { success: false, error: error.message };
    }
  }

  // 13. Admin aggregate: Recruiter/Company summary
  async getRecruiterCompanySummary(params: {
    from?: string;
    to?: string;
    companyId?: number;
  }): Promise<any> {
    try {
      const { from, to, companyId } = params || {};
      const normalizedFrom = from ? this.normalizeDate(from, false) : undefined;
      const normalizedTo = to ? this.normalizeDate(to, true) : undefined;
      const where: string[] = [];
      const whereParams: any[] = [];
      if (normalizedFrom) {
        where.push('ja.applied_at >= ?');
        whereParams.push(normalizedFrom);
      }
      if (normalizedTo) {
        where.push('ja.applied_at <= ?');
        whereParams.push(normalizedTo);
      }
      if (companyId) {
        where.push('c.id = ?');
        whereParams.push(companyId);
      }
      const whereSQL = where.length ? `WHERE ${where.join(' AND ')}` : '';

      // Total applications per company
      const applicationsPerCompany = await this.dataSource.query(
        `
        SELECT c.id as company_id, c.name as company_name, COUNT(ja.id) as applications_total
        FROM companies c
        LEFT JOIN job_posts jp ON jp.company_id = c.id
        LEFT JOIN job_applications ja ON ja.job_post_id = jp.id
        ${whereSQL}
        GROUP BY c.id, c.name
        `,
        whereParams,
      );

      // Applications seen by recruiters (from user_activity_logs)
      const seenConditions: string[] = [
        "action_type = 'application_view'",
        "user_type = 'recruiter'",
      ];
      const seenParams: any[] = [];
      if (normalizedFrom) {
        seenConditions.push('created_at >= ?');
        seenParams.push(normalizedFrom);
      }
      if (normalizedTo) {
        seenConditions.push('created_at <= ?');
        seenParams.push(normalizedTo);
      }
      if (companyId) {
        seenConditions.push('company_id = ?');
        seenParams.push(companyId);
      }
      const seenPerCompany = await this.dataSource.query(
        `
        SELECT company_id, COUNT(*) as applications_seen
        FROM user_activity_logs
        WHERE ${seenConditions.join(' AND ')}
        GROUP BY company_id
        `,
        seenParams,
      );

      // Recruiter login counts and last login per company
      const loginParams: any[] = [];
      let loginWhere = '';
      if (companyId) {
        loginWhere = 'WHERE c.id = ?';
        loginParams.push(companyId);
      }
      const timeParams: any[] = [];
      const timeSQL = `${normalizedFrom ? ' AND ual.created_at >= ?' : ''}${normalizedTo ? ' AND ual.created_at <= ?' : ''}`;
      if (normalizedFrom) timeParams.push(normalizedFrom);
      if (normalizedTo) timeParams.push(normalizedTo);
      const loginsPerCompany = await this.dataSource.query(
        `
        SELECT c.id as company_id,
               COUNT(ual.id) as recruiter_logins_count,
               MAX(ual.created_at) as last_login_at,
               MIN(ual.created_at) as first_login_at
        FROM companies c
        INNER JOIN users u ON u.id = c.user_id
        LEFT JOIN user_activity_logs ual
          ON ual.user_id = u.id
         AND ual.user_type = 'recruiter'
         AND (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login')
         ${timeSQL}
        ${loginWhere}
        GROUP BY c.id
        `,
        [...timeParams, ...loginParams],
      );

      // Top IPs per company (for recruiter logins)
      const topIpParams: any[] = [];
      const topIpTimeParams: any[] = [];
      const topIpTimeSQL = `${normalizedFrom ? ' AND ual.created_at >= ?' : ''}${normalizedTo ? ' AND ual.created_at <= ?' : ''}`;
      if (normalizedFrom) topIpTimeParams.push(normalizedFrom);
      if (normalizedTo) topIpTimeParams.push(normalizedTo);
      let topIpWhere = '';
      if (companyId) {
        topIpWhere = 'WHERE c.id = ?';
        topIpParams.push(companyId);
      }
      const topIpsRows = await this.dataSource.query(
        `
        SELECT c.id as company_id,
               ual.ip_address,
               COUNT(*) as hits,
               MAX(ual.location_country) as country,
               MAX(ual.location_region) as region,
               MAX(ual.location_city) as city
        FROM companies c
        INNER JOIN users u ON u.id = c.user_id
        LEFT JOIN user_activity_logs ual
          ON ual.user_id = u.id
         AND ual.user_type = 'recruiter'
         AND (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login')
         ${topIpTimeSQL}
        ${topIpWhere}
        GROUP BY c.id, ual.ip_address
        ORDER BY hits DESC
        `,
        [...topIpTimeParams, ...topIpParams],
      );
      const companyIps = topIpsRows.reduce(
        (acc: Map<number, any[]>, r: any) => {
          const cid = parseInt(r.company_id);
          const arr = acc.get(cid) || [];
          arr.push({
            ip_address: r.ip_address,
            hits: parseInt(r.hits || 0),
            country: r.country || null,
            region: r.region || null,
            city: r.city || null,
          });
          acc.set(cid, arr);
          return acc;
        },
        new Map<number, any[]>(),
      );

      // Merge results by company_id
      const seenMap = new Map<number, any>();
      for (const r of seenPerCompany) {
        seenMap.set(parseInt(r.company_id || r.companyId), {
          applications_seen: parseInt(r.applications_seen || 0),
        });
      }
      const loginMap = new Map<number, any>();
      for (const r of loginsPerCompany) {
        loginMap.set(parseInt(r.company_id), {
          recruiter_logins_count: parseInt(r.recruiter_logins_count || 0),
          last_login_at: r.last_login_at || null,
          first_login_at: r.first_login_at || null,
        });
      }

      const data = (applicationsPerCompany || [])
        .map((row: any) => {
          const cid = parseInt(row.company_id);
          const seen = seenMap.get(cid) || { applications_seen: 0 };
          const logins = loginMap.get(cid) || {
            recruiter_logins_count: 0,
            last_login_at: null,
            first_login_at: null,
          };
          return {
            company_id: cid,
            company_name: row.company_name,
            applications_total: parseInt(row.applications_total || 0),
            applications_seen: seen.applications_seen,
            recruiter_logins_count: logins.recruiter_logins_count,
            last_login_at: logins.last_login_at,
            first_login_at: logins.first_login_at,
            top_ips: companyIps.get(cid) || [],
          };
        })
        .filter(
          (r: any) =>
            (r.applications_total || 0) > 0 ||
            (r.applications_seen || 0) > 0 ||
            (r.recruiter_logins_count || 0) > 0,
        );

      return { success: true, data };
    } catch (error) {
      console.error('Error fetching recruiter/company summary:', error);
      return { success: false, error: (error as any).message };
    }
  }

  // 14. Admin aggregate: Associate usage summary
  async getAssociateUsageSummary(params: {
    from?: string;
    to?: string;
    limit?: number;
  }): Promise<any> {
    try {
      const { from, to, limit = 50 } = params || {};
      const timeSQL = `${from ? ' AND ual.created_at >= ?' : ''}${to ? ' AND ual.created_at <= ?' : ''}`;
      const timeParams: any[] = [];
      if (from) timeParams.push(from);
      if (to) timeParams.push(to);

      // Aggregate per associate (user)
      const rows = await this.dataSource.query(
        `
        SELECT 
          u.id as user_id,
          u.full_name as name,
          SUM(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN 1 ELSE 0 END) as logins_count,
          MAX(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN ual.created_at ELSE NULL END) as last_login_at,
          MIN(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN ual.created_at ELSE NULL END) as first_login_at,
          SUM(COALESCE(JSON_EXTRACT(ual.action_details, '$.timeSpentSeconds'), 0)) as total_time_seconds
        FROM users u
        LEFT JOIN user_activity_logs ual
          ON ual.user_id = u.id
         AND ual.user_type = 'associate'
         ${timeSQL}
        WHERE u.role = 'associate'
        GROUP BY u.id, u.full_name
        HAVING (
          SUM(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN 1 ELSE 0 END) > 0
          OR SUM(COALESCE(JSON_EXTRACT(ual.action_details, '$.timeSpentSeconds'), 0)) > 0
          OR MAX(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN ual.created_at ELSE NULL END) IS NOT NULL
        )
        ORDER BY total_time_seconds DESC
        LIMIT ?
        `,
        [...timeParams, limit],
      );

      // Top pages per associate (optional small sample)
      const userIds = rows.map((r: any) => r.user_id);
      let pagesMap = new Map<number, any[]>();
      let ipMap = new Map<number, any[]>();
      if (userIds.length) {
        const pages = await this.dataSource.query(
          `
          SELECT user_id, page_url,
                 SUM(COALESCE(JSON_EXTRACT(action_details, '$.timeSpentSeconds'), 0)) as total_seconds,
                 COUNT(*) as events
          FROM user_activity_logs
          WHERE user_type = 'associate'
            AND action_type = 'time_on_page'
            ${from ? ' AND created_at >= ?' : ''}
            ${to ? ' AND created_at <= ?' : ''}
            AND user_id IN (${userIds.map(() => '?').join(',')})
          GROUP BY user_id, page_url
          ORDER BY total_seconds DESC
          `,
          [...(from ? [from] : []), ...(to ? [to] : []), ...userIds],
        );
        pagesMap = pages.reduce((acc: Map<number, any[]>, p: any) => {
          const uid = parseInt(p.user_id);
          const arr = acc.get(uid) || [];
          arr.push({
            page_url: p.page_url,
            total_seconds: Math.round(parseFloat(p.total_seconds || 0)),
            events: parseInt(p.events || 0),
          });
          acc.set(uid, arr);
          return acc;
        }, new Map());

        const ips = await this.dataSource.query(
          `
          SELECT user_id,
                 ip_address,
                 COUNT(*) as hits,
                 MAX(location_country) as country,
                 MAX(location_region) as region,
                 MAX(location_city) as city
          FROM user_activity_logs
          WHERE user_type = 'associate'
            ${from ? ' AND created_at >= ?' : ''}
            ${to ? ' AND created_at <= ?' : ''}
            AND (action_type = 'auth_signin' OR action_type = 'auth_login')
            AND user_id IN (${userIds.map(() => '?').join(',')})
          GROUP BY user_id, ip_address
          ORDER BY hits DESC
          `,
          [...(from ? [from] : []), ...(to ? [to] : []), ...userIds],
        );
        ipMap = ips.reduce((acc: Map<number, any[]>, p: any) => {
          const uid = parseInt(p.user_id);
          const arr = acc.get(uid) || [];
          arr.push({
            ip_address: p.ip_address,
            hits: parseInt(p.hits || 0),
            country: p.country || null,
            region: p.region || null,
            city: p.city || null,
          });
          acc.set(uid, arr);
          return acc;
        }, new Map());
      }

      const data = rows.map((r: any) => ({
        user_id: parseInt(r.user_id),
        name: r.name,
        logins_count: parseInt(r.logins_count || 0),
        last_login_at: r.last_login_at,
        first_login_at: r.first_login_at,
        total_time_seconds: Math.round(parseFloat(r.total_time_seconds || 0)),
        pages: (pagesMap.get(parseInt(r.user_id)) || []).slice(0, 10),
        locations: ipMap.get(parseInt(r.user_id)) || [],
      }));

      return { success: true, data };
    } catch (error) {
      console.error('Error fetching associate usage summary:', error);
      return { success: false, error: (error as any).message };
    }
  }

  // 15. Admin aggregate: Seeker login summary
  async getSeekerLoginSummary(params: {
    from?: string;
    to?: string;
    limit?: number;
  }): Promise<any> {
    try {
      const { from, to, limit = 50 } = params || {};
      const timeSQL = `${from ? ' AND ual.created_at >= ?' : ''}${to ? ' AND ual.created_at <= ?' : ''}`;
      const timeParams: any[] = [];
      if (from) timeParams.push(from);
      if (to) timeParams.push(to);

      // Per seeker aggregates
      const rows = await this.dataSource.query(
        `
        SELECT 
          u.id as user_id,
          u.full_name as name,
          u.email as email,
          SUM(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN 1 ELSE 0 END) as logins_count,
          MAX(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN ual.created_at ELSE NULL END) as last_login_at,
          MIN(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN ual.created_at ELSE NULL END) as first_login_at,
          SUM(COALESCE(JSON_EXTRACT(ual.action_details, '$.timeSpentSeconds'), 0)) as total_time_seconds
        FROM users u
        LEFT JOIN user_activity_logs ual
          ON ual.user_id = u.id
         AND ual.user_type = 'seeker'
         ${timeSQL}
        WHERE u.role = 'seeker'
        GROUP BY u.id, u.full_name, u.email
        HAVING (
          SUM(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN 1 ELSE 0 END) > 0
          OR SUM(COALESCE(JSON_EXTRACT(ual.action_details, '$.timeSpentSeconds'), 0)) > 0
          OR MAX(CASE WHEN (ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login') THEN ual.created_at ELSE NULL END) IS NOT NULL
        )
        ORDER BY COALESCE(MAX(ual.created_at), '1970-01-01') DESC
        LIMIT ?
        `,
        [...timeParams, limit],
      );

      // Top locations by IP per seeker (fallback to IP-only if no geo)
      const userIds = rows.map((r: any) => r.user_id);
      let locMap = new Map<number, any[]>();
      if (userIds.length) {
        const locs = await this.dataSource.query(
          `
          SELECT user_id,
                 ip_address,
                 COUNT(*) as hits,
                 MAX(location_country) as country,
                 MAX(location_region) as region,
                 MAX(location_city) as city
          FROM user_activity_logs
          WHERE user_type = 'seeker'
            ${from ? ' AND created_at >= ?' : ''}
            ${to ? ' AND created_at <= ?' : ''}
            AND (action_type = 'auth_signin' OR action_type = 'auth_login')
            AND user_id IN (${userIds.map(() => '?').join(',')})
          GROUP BY user_id, ip_address
          ORDER BY hits DESC
          `,
          [...(from ? [from] : []), ...(to ? [to] : []), ...userIds],
        );
        locMap = locs.reduce((acc: Map<number, any[]>, p: any) => {
          const uid = parseInt(p.user_id);
          const arr = acc.get(uid) || [];
          arr.push({
            ip_address: p.ip_address,
            hits: parseInt(p.hits || 0),
            country: p.country || null,
            region: p.region || null,
            city: p.city || null,
          });
          acc.set(uid, arr);
          return acc;
        }, new Map());
      }

      const data = rows.map((r: any) => ({
        user_id: parseInt(r.user_id),
        name: r.name,
        email: r.email,
        logins_count: parseInt(r.logins_count || 0),
        last_login_at: r.last_login_at,
        first_login_at: r.first_login_at,
        total_time_seconds: Math.round(parseFloat(r.total_time_seconds || 0)),
        locations: locMap.get(parseInt(r.user_id)) || [],
      }));

      return { success: true, data };
    } catch (error) {
      console.error('Error fetching seeker login summary:', error);
      return { success: false, error: (error as any).message };
    }
  }

  // Helper function to normalize date strings (add time if missing)
  private normalizeDate(dateStr: string, isEndOfDay: boolean = false): string {
    if (!dateStr) return dateStr;
    // If date string is just YYYY-MM-DD, add time component
    if (/^\d{4}-\d{2}-\d{2}$/.test(dateStr)) {
      return isEndOfDay ? `${dateStr} 23:59:59` : `${dateStr} 00:00:00`;
    }
    // If it already has time, return as is
    return dateStr;
  }

  // Recruiter Activity (companies activity table). Default date: today
  // NEW: Starts from companies table to show all real companies (is_admin_company = 0)
  // Shows companies even if they have no activity logs, as long as they have job posts or applications
  async getRecruiterActivity(params: any): Promise<any> {
    const {
      page = 1,
      limit = 10,
      dateRange = 1,
      from,
      to,
      companyId,
      recruiterId,
      deviceType,
      actionType,
      browser,
      ipAddress,
    } = params || {};

    // Build date filters for main query and subqueries
    const normalizedFrom = from ? this.normalizeDate(from, false) : undefined;
    const normalizedTo = to ? this.normalizeDate(to, true) : undefined;

    // Main WHERE conditions - start from companies table
    const whereConds: string[] = [
      'c.is_admin_company = 0 OR c.is_admin_company IS NULL',
      'c.is_deleted = 0',
      "u.role = 'recruiter'",
    ];
    const args: any[] = [];

    // Date filter for activity logs (timezone-aware for IST)
    // Convert UTC timestamps to IST (+05:30) for date comparison
    let activityDateFilter = '';
    if (normalizedFrom && normalizedTo) {
      // Extract date part (YYYY-MM-DD)
      const fromDate = normalizedFrom.split(' ')[0];
      const toDate = normalizedTo.split(' ')[0];
      // Convert to IST by comparing date part after timezone conversion
      activityDateFilter = `DATE(CONVERT_TZ(ual.created_at, '+00:00', '+05:30')) >= DATE(?) AND DATE(CONVERT_TZ(ual.created_at, '+00:00', '+05:30')) <= DATE(?)`;
      args.push(fromDate, toDate);
    } else if (normalizedFrom) {
      const fromDate = normalizedFrom.split(' ')[0];
      activityDateFilter = `DATE(CONVERT_TZ(ual.created_at, '+00:00', '+05:30')) >= DATE(?)`;
      args.push(fromDate);
    } else if (normalizedTo) {
      const toDate = normalizedTo.split(' ')[0];
      activityDateFilter = `DATE(CONVERT_TZ(ual.created_at, '+00:00', '+05:30')) <= DATE(?)`;
      args.push(toDate);
    } else {
      const days = parseInt(String(dateRange || 1), 10);
      if (days === 1) {
        // Today in IST
        activityDateFilter = `DATE(CONVERT_TZ(ual.created_at, '+00:00', '+05:30')) = DATE(CONVERT_TZ(NOW(), '+00:00', '+05:30'))`;
      } else {
        activityDateFilter = `DATE(CONVERT_TZ(ual.created_at, '+00:00', '+05:30')) >= DATE_SUB(DATE(CONVERT_TZ(NOW(), '+00:00', '+05:30')), INTERVAL ${days - 1} DAY)`;
      }
    }

    // Date filter for job posts (build with actual values for subqueries)
    let postsDateFilter = '';
    if (normalizedFrom && normalizedTo) {
      postsDateFilter = `jp.created_at >= '${normalizedFrom}' AND jp.created_at <= '${normalizedTo}'`;
    } else if (normalizedFrom) {
      postsDateFilter = `jp.created_at >= '${normalizedFrom}'`;
    } else if (normalizedTo) {
      postsDateFilter = `jp.created_at <= '${normalizedTo}'`;
    } else {
      const days = parseInt(String(dateRange || 1), 10);
      postsDateFilter = `jp.created_at >= DATE_SUB(NOW(), INTERVAL ${days} DAY)`;
    }

    // Date filter for applications (build with actual values for subqueries)
    let appsDateFilter = '';
    if (normalizedFrom && normalizedTo) {
      appsDateFilter = `ja.applied_at >= '${normalizedFrom}' AND ja.applied_at <= '${normalizedTo}'`;
    } else if (normalizedFrom) {
      appsDateFilter = `ja.applied_at >= '${normalizedFrom}'`;
    } else if (normalizedTo) {
      appsDateFilter = `ja.applied_at <= '${normalizedTo}'`;
    } else {
      const days = parseInt(String(dateRange || 1), 10);
      appsDateFilter = `ja.applied_at >= DATE_SUB(NOW(), INTERVAL ${days} DAY)`;
    }

    // Additional filters
    if (companyId) {
      whereConds.push('c.id = ?');
      args.push(companyId);
    }
    if (recruiterId) {
      whereConds.push('u.id = ?');
      args.push(recruiterId);
    }
    if (deviceType) {
      whereConds.push('ual.device_type = ?');
      args.push(deviceType);
    }
    if (actionType) {
      whereConds.push('ual.action_type = ?');
      args.push(actionType);
    }
    if (browser) {
      whereConds.push('ual.browser LIKE ?');
      args.push(`%${browser}%`);
    }
    if (ipAddress) {
      whereConds.push('ual.ip_address = ?');
      args.push(ipAddress);
    }

    // Build args for subqueries (need to duplicate date params)
    const postsArgs: any[] = [];
    const appsArgs: any[] = [];
    if (normalizedFrom && normalizedTo) {
      postsArgs.push(normalizedFrom, normalizedTo);
      appsArgs.push(normalizedFrom, normalizedTo);
    } else if (normalizedFrom) {
      postsArgs.push(normalizedFrom);
      appsArgs.push(normalizedFrom);
    } else if (normalizedTo) {
      postsArgs.push(normalizedTo);
      appsArgs.push(normalizedTo);
    }

    const sql = `
      SELECT 
        u.id AS recruiter_id,
        u.full_name AS recruiter_name,
        c.id AS company_id,
        c.name AS company_names,
        -- Job posts count in date range
        COALESCE((
          SELECT COUNT(DISTINCT jp.id)
          FROM job_posts jp
          WHERE jp.company_id = c.id
            ${postsDateFilter ? `AND ${postsDateFilter}` : ''}
        ), 0) AS job_posts_count,
        -- Applications count in date range
        COALESCE((
          SELECT COUNT(ja.id)
          FROM job_posts jp
          INNER JOIN job_applications ja ON ja.job_post_id = jp.id
          WHERE jp.company_id = c.id
            ${appsDateFilter ? `AND ${appsDateFilter}` : ''}
        ), 0) AS applications_count,
        -- Activity metrics from logs (date-filtered)
        -- Applications viewed: count application_view actions or page views on application pages
        COALESCE(SUM(CASE 
          WHEN ual.action_type = 'application_view' 
          OR (ual.action_type = 'page_view' AND (ual.page_url LIKE '%/recruiter/applications%' OR ual.page_url LIKE '%/dashboard/recruiter/applications%'))
          THEN 1 ELSE 0 END), 0) AS total_applications_viewed,
        -- Job posts viewed: count job_details_view, job_view, or page views on job-related pages in recruiter dashboard
        COALESCE(SUM(CASE 
          WHEN ual.action_type = 'job_details_view' 
          OR ual.action_type = 'job_view'
          OR (ual.action_type = 'page_view' AND ual.page_url LIKE '%/recruiter/jobs%' AND ual.job_id IS NOT NULL)
          OR (ual.action_type = 'page_view' AND ual.page_url LIKE '%/dashboard/recruiter/posted_jobs%')
          THEN 1 ELSE 0 END), 0) AS job_posts_viewed,
        COALESCE(SUM(CASE WHEN (ual.action_type LIKE 'auth_%' OR ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login' OR ual.action_type = 'login') THEN 1 ELSE 0 END), 0) AS login_count,
        CONVERT_TZ(MAX(CASE WHEN (ual.action_type LIKE 'auth_%' OR ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login' OR ual.action_type = 'login') THEN ual.created_at ELSE NULL END), @@session.time_zone, '+05:30') AS last_login_time,
        COALESCE(COUNT(DISTINCT CASE WHEN ual.page_url IS NOT NULL THEN ual.page_url END), 0) AS pages_visited,
        COALESCE(SUM(COALESCE(JSON_EXTRACT(ual.action_details, '$.timeSpentSeconds'), 0)), 0) AS time_spent_seconds,
        -- Most recent metadata (not date-filtered) - get from latest activity log
        -- Allow company_id to be null or match c.id
        COALESCE((
          SELECT ual_recent.ip_address
          FROM user_activity_logs ual_recent
          WHERE ual_recent.user_id = u.id
            AND ual_recent.user_type = 'recruiter'
            AND (ual_recent.company_id = c.id OR ual_recent.company_id IS NULL)
            AND ual_recent.ip_address IS NOT NULL
          ORDER BY ual_recent.created_at DESC
          LIMIT 1
        ), NULL) AS ip_address,
        COALESCE((
          SELECT ual_recent.device_type
          FROM user_activity_logs ual_recent
          WHERE ual_recent.user_id = u.id
            AND ual_recent.user_type = 'recruiter'
            AND (ual_recent.company_id = c.id OR ual_recent.company_id IS NULL)
            AND ual_recent.device_type IS NOT NULL
          ORDER BY ual_recent.created_at DESC
          LIMIT 1
        ), NULL) AS device_type,
        COALESCE((
          SELECT ual_recent.browser
          FROM user_activity_logs ual_recent
          WHERE ual_recent.user_id = u.id
            AND ual_recent.user_type = 'recruiter'
            AND (ual_recent.company_id = c.id OR ual_recent.company_id IS NULL)
            AND ual_recent.browser IS NOT NULL
          ORDER BY ual_recent.created_at DESC
          LIMIT 1
        ), NULL) AS browser
      FROM companies c
      INNER JOIN users u ON u.id = c.user_id AND u.role = 'recruiter'
      LEFT JOIN user_activity_logs ual ON ual.user_id = u.id
        AND ual.user_type = 'recruiter'
        AND (ual.company_id = c.id OR ual.company_id IS NULL)
        ${activityDateFilter ? `AND ${activityDateFilter}` : ''}
      WHERE ${whereConds.join(' AND ')}
      GROUP BY c.id, u.id, u.full_name, c.name
      HAVING job_posts_count > 0 
        OR applications_count > 0 
        OR total_applications_viewed > 0 
        OR job_posts_viewed > 0 
        OR login_count > 0 
        OR pages_visited > 0 
        OR time_spent_seconds > 0
      ORDER BY job_posts_count DESC, applications_count DESC, total_applications_viewed DESC
      LIMIT ? OFFSET ?
    `;

    const take = Math.min(Math.max(parseInt(String(limit), 10) || 10, 1), 200);
    const skip = (Math.max(parseInt(String(page), 10) || 1, 1) - 1) * take;

    // Execute query with all args
    const queryArgs = [...args, take, skip];
    const rows = await this.activityRepo.manager.query(sql, queryArgs);

    // Get total count for pagination - use same logic as main query
    const countSql = `
      SELECT COUNT(*) as total
      FROM (
        SELECT 
          c.id,
          u.id AS recruiter_id,
          -- Job posts count in date range
          COALESCE((
            SELECT COUNT(DISTINCT jp.id)
            FROM job_posts jp
            WHERE jp.company_id = c.id
              ${postsDateFilter ? `AND ${postsDateFilter}` : ''}
          ), 0) AS job_posts_count,
          -- Applications count in date range
          COALESCE((
            SELECT COUNT(ja.id)
            FROM job_posts jp
            INNER JOIN job_applications ja ON ja.job_post_id = jp.id
            WHERE jp.company_id = c.id
              ${appsDateFilter ? `AND ${appsDateFilter}` : ''}
          ), 0) AS applications_count,
          -- Activity metrics from logs
          COALESCE(SUM(CASE WHEN ual.action_type = 'application_view' THEN 1 ELSE 0 END), 0) AS total_applications_viewed,
          COALESCE(SUM(CASE WHEN ual.action_type = 'job_details_view' OR ual.action_type = 'job_view' THEN 1 ELSE 0 END), 0) AS job_posts_viewed,
          COALESCE(SUM(CASE WHEN (ual.action_type LIKE 'auth_%' OR ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login' OR ual.action_type = 'login') THEN 1 ELSE 0 END), 0) AS login_count,
          COALESCE(COUNT(DISTINCT CASE WHEN ual.page_url IS NOT NULL THEN ual.page_url END), 0) AS pages_visited,
          COALESCE(SUM(COALESCE(JSON_EXTRACT(ual.action_details, '$.timeSpentSeconds'), 0)), 0) AS time_spent_seconds
        FROM companies c
        INNER JOIN users u ON u.id = c.user_id AND u.role = 'recruiter'
        LEFT JOIN user_activity_logs ual ON ual.user_id = u.id
          AND ual.user_type = 'recruiter'
          AND (ual.company_id = c.id OR (ual.company_id IS NULL AND (ual.action_type LIKE 'auth_%' OR ual.action_type = 'login' OR ual.action_type = 'logout' OR ual.action_type = 'dashboard_action')))
          ${activityDateFilter ? `AND ${activityDateFilter}` : ''}
        WHERE ${whereConds.join(' AND ')}
        GROUP BY c.id, u.id, u.full_name, c.name
        HAVING job_posts_count > 0 
          OR applications_count > 0 
          OR total_applications_viewed > 0 
          OR job_posts_viewed > 0 
          OR login_count > 0 
          OR pages_visited > 0 
          OR time_spent_seconds > 0
      ) AS subquery
    `;

    const countResult = await this.activityRepo.manager.query(countSql, args);
    const total = countResult[0]?.total || 0;

    return {
      success: true,
      data: rows,
      meta: {
        total: total,
        page: parseInt(String(page), 10) || 1,
        totalPages: Math.ceil(total / take),
        limit: take,
      },
    };
  }

  /**
   * Helper method to log company/recruiter activities
   * This can be called from other services to track company actions
   * @param params - Activity log parameters
   */
  async logCompanyActivity(params: {
    userId: number;
    companyId: number;
    actionType: string;
    actionName: string;
    jobId?: number;
    applicationId?: number;
    status?: 'success' | 'failed' | 'cancelled' | 'abandoned';
    actionDetails?: any;
    ipAddress?: string;
    deviceType?: 'mobile' | 'tablet' | 'desktop';
    browser?: string;
  }): Promise<void> {
    try {
      const {
        userId,
        companyId,
        actionType,
        actionName,
        jobId,
        applicationId,
        status = 'success',
        actionDetails = {},
        ipAddress,
        deviceType,
        browser,
      } = params;

      const logEntry = this.activityRepo.create({
        user_id: userId,
        user_type: 'recruiter',
        action_type: actionType,
        action_name: actionName,
        company_id: companyId,
        job_id: jobId || null,
        application_id: applicationId || null,
        status: status,
        action_details: actionDetails,
        ip_address: ipAddress || null,
        device_type: deviceType || 'desktop',
        browser: browser || null,
      });

      await this.activityRepo.save(logEntry);
    } catch (error) {
      // Don't throw - logging failures shouldn't break main functionality
      console.error('Error logging company activity:', error);
    }
  }

  // Agent/Associate Activity. Default date: today
  async getAgentActivity(params: any): Promise<any> {
    const {
      page = 1,
      limit = 10,
      dateRange = 1,
      from,
      to,
      agentId,
      deviceType,
      module,
      browser,
      q,
      minDurationSeconds,
      maxDurationSeconds,
    } = params || {};

    const whereConds: string[] = ["u.role = 'associate'"];
    const args: any[] = [];
    // Main time filter (calendar IST or explicit range)
    if (from && to) {
      const normalizedFrom = this.normalizeDate(from, false);
      const normalizedTo = this.normalizeDate(to, true);
      whereConds.push('ual.created_at >= ?');
      args.push(normalizedFrom);
      whereConds.push('ual.created_at <= ?');
      args.push(normalizedTo);
    } else if (Number(dateRange) === 1) {
      whereConds.push(
        "DATE(CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30')) = DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30'))",
      );
    } else {
      const n = Math.max(1, parseInt(String(dateRange || 1), 10));
      whereConds.push(
        `DATE(CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30')) BETWEEN DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30')) - INTERVAL ${n - 1} DAY AND DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30'))`,
      );
    }

    if (q) {
      whereConds.push('u.full_name LIKE ?');
      args.push(`%${q}%`);
    }
    if (agentId) {
      whereConds.push('ual.user_id = ?');
      args.push(agentId);
    }
    if (deviceType) {
      whereConds.push('ual.device_type = ?');
      args.push(deviceType);
    }
    if (module) {
      whereConds.push('ual.action_type = ?');
      args.push(module);
    }
    if (browser) {
      whereConds.push('ual.browser LIKE ?');
      args.push(`%${browser}%`);
    }

    // Build time filters for subselects (posts/applications) to align with UI presets
    const isToday = !from && !to && Number(dateRange) === 1;
    const nDays = Math.max(1, parseInt(String(dateRange || 1), 10));
    const postsTimeCond = isToday
      ? " AND DATE(CONVERT_TZ(jp.created_at, @@session.time_zone, '+05:30')) = DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30'))"
      : from && to
        ? ' AND jp.created_at >= ? AND jp.created_at <= ?'
        : ` AND DATE(CONVERT_TZ(jp.created_at, @@session.time_zone, '+05:30')) BETWEEN DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30')) - INTERVAL ${nDays - 1} DAY AND DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30'))`;
    const appsTimeCond = isToday
      ? " AND DATE(CONVERT_TZ(ja.applied_at, @@session.time_zone, '+05:30')) = DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30'))"
      : from && to
        ? ' AND ja.applied_at >= ? AND ja.applied_at <= ?'
        : ` AND DATE(CONVERT_TZ(ja.applied_at, @@session.time_zone, '+05:30')) BETWEEN DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30')) - INTERVAL ${nDays - 1} DAY AND DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30'))`;

    const havingConds: string[] = ['COUNT(ual.id) > 0'];
    const havingArgs: any[] = [];
    if (minDurationSeconds) {
      havingConds.push(
        'TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at)) >= ?',
      );
      havingArgs.push(Number(minDurationSeconds));
    }
    if (maxDurationSeconds) {
      havingConds.push(
        'TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at)) <= ?',
      );
      havingArgs.push(Number(maxDurationSeconds));
    }

    const sql = `
      SELECT 
        u.id AS user_id,
        u.full_name AS agent_name,
        CONVERT_TZ(MIN(ual.created_at), @@session.time_zone, '+05:30') AS login_timestamp,
        CONVERT_TZ(MAX(ual.created_at), @@session.time_zone, '+05:30') AS logout_timestamp,
        TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at)) AS total_time_active_seconds,
        -- Posts count in date window (join associates table to get associate.id from user.id)
        COALESCE((
          SELECT COUNT(DISTINCT jp.id) FROM job_posts jp
          INNER JOIN associates a ON a.id = jp.associate_id
          WHERE a.user_id = u.id
            ${postsTimeCond}
        ), 0) AS posts_count,
        -- Applications total in date window (join associates table to get associate.id from user.id)
        COALESCE((
          SELECT COUNT(ja.id) FROM job_posts jp
          INNER JOIN associates a ON a.id = jp.associate_id
          LEFT JOIN job_applications ja ON ja.job_post_id = jp.id
          WHERE a.user_id = u.id
            ${appsTimeCond}
        ), 0) AS applications_total,
        -- Simple module list and actions
        GROUP_CONCAT(DISTINCT ual.action_type ORDER BY ual.action_type SEPARATOR ', ') AS pages_modules,
        COUNT(*) AS actions_performed,
        -- Companies via job_posts in window (join associates table to get associate.id from user.id)
        COALESCE((
          SELECT GROUP_CONCAT(DISTINCT c.name SEPARATOR ', ')
          FROM job_posts jp
          INNER JOIN associates a ON a.id = jp.associate_id
          LEFT JOIN companies c ON c.id = jp.company_id
          WHERE a.user_id = u.id
            ${postsTimeCond}
        ), '') AS companies,
        SUBSTRING_INDEX(GROUP_CONCAT(ual.ip_address ORDER BY ual.created_at DESC), ',', 1) AS ip_address,
        SUBSTRING_INDEX(GROUP_CONCAT(ual.device_type ORDER BY ual.created_at DESC), ',', 1) AS device_type,
        SUBSTRING_INDEX(GROUP_CONCAT(ual.browser ORDER BY ual.created_at DESC), ',', 1) AS browser
      FROM users u
      LEFT JOIN user_activity_logs ual ON ual.user_id = u.id AND ual.user_type = 'associate'
      WHERE ${whereConds.join(' AND ')}
      GROUP BY u.id, u.full_name
      HAVING ${havingConds.join(' AND ')}
      ORDER BY total_time_active_seconds DESC
      LIMIT ? OFFSET ?
    `;
    const take = Math.min(Math.max(parseInt(String(limit), 10) || 10, 1), 200);
    const skip = (Math.max(parseInt(String(page), 10) || 1, 1) - 1) * take;
    const extraArgs: any[] = [];
    // For postsTimeCond and appsTimeCond if using from/to, push parameters in the same order as placeholders
    if (from && to && !isToday) {
      const normalizedFrom = this.normalizeDate(from, false);
      const normalizedTo = this.normalizeDate(to, true);
      // posts created_at range
      extraArgs.push(normalizedFrom, normalizedTo);
      // applications applied_at range
      extraArgs.push(normalizedFrom, normalizedTo);
      // companies posts created_at range
      extraArgs.push(normalizedFrom, normalizedTo);
    }
    const rows = await this.activityRepo.manager.query(sql, [
      ...args,
      ...extraArgs,
      ...havingArgs,
      take,
      skip,
    ]);

    // Count query for pagination
    const countSql = `
      SELECT COUNT(*) as total FROM (
        SELECT u.id
        FROM users u
        LEFT JOIN user_activity_logs ual ON ual.user_id = u.id AND ual.user_type = 'associate'
        WHERE ${whereConds.join(' AND ')}
        GROUP BY u.id
        HAVING ${havingConds.join(' AND ')}
      ) as subquery
    `;
    const countRes = await this.activityRepo.manager.query(countSql, [
      ...args,
      ...havingArgs,
    ]);
    const total = parseInt(countRes[0]?.total || 0, 10);

    return {
      success: true,
      data: rows,
      meta: {
        total: total,
        page: parseInt(String(page), 10) || 1,
        totalPages: Math.ceil(total / take),
        limit: take,
      },
    };
  }

  // Seeker Activity. Default date: today
  async getSeekerActivity(params: any): Promise<any> {
    const {
      page = 1,
      limit = 10,
      dateRange = 1,
      from,
      to,
      candidateId,
      deviceType,
      browser,
      q,
      city,
      jobId,
      minTimeSpentSeconds,
      maxTimeSpentSeconds,
    } = params || {};

    const whereConds: string[] = ["u.role = 'seeker'"];
    const args: any[] = [];

    // Build date filter for JOIN condition (using placeholders)
    // Date filtering is done in JOIN, not WHERE, to ensure all metrics respect the date range
    let joinDateFilter = '';
    const joinArgs: any[] = [];
    if (from && to) {
      const normalizedFrom = this.normalizeDate(from, false);
      const normalizedTo = this.normalizeDate(to, true);
      joinDateFilter = ` AND ual.created_at >= ? AND ual.created_at <= ?`;
      joinArgs.push(normalizedFrom, normalizedTo);
    } else if (Number(dateRange) === 1) {
      joinDateFilter = ` AND DATE(CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30')) = DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30'))`;
    } else {
      const n = Math.max(1, parseInt(String(dateRange || 1), 10));
      joinDateFilter = ` AND DATE(CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30')) BETWEEN DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30')) - INTERVAL ${n - 1} DAY AND DATE(CONVERT_TZ(NOW(), @@session.time_zone, '+05:30'))`;
    }

    // Other filters (not date-related)
    if (q) {
      whereConds.push('u.full_name LIKE ?');
      args.push(`%${q}%`);
    }
    if (candidateId) {
      whereConds.push('ual.user_id = ?');
      args.push(candidateId);
    }
    if (deviceType) {
      whereConds.push('ual.device_type = ?');
      args.push(deviceType);
    }
    if (browser) {
      whereConds.push('ual.browser LIKE ?');
      args.push(`%${browser}%`);
    }
    if (jobId) {
      whereConds.push('ual.job_id = ?');
      args.push(jobId);
    }
    if (city) {
      whereConds.push(`u.id IN (
        SELECT sp.user_id 
        FROM seeker_profiles sp
        INNER JOIN seeker_address sa ON sa.seeker_profile_id = sp.id
        LEFT JOIN city ci ON ci.city_id = sa.communication_city_id OR ci.city_id = sa.permanent_city_id
        WHERE ci.city_name LIKE ?
      )`);
      args.push(`%${city}%`);
    }

    const havingConds: string[] = ['COUNT(ual.id) > 0'];
    const havingArgs: any[] = [];
    if (minTimeSpentSeconds) {
      havingConds.push(
        'TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at)) >= ?',
      );
      havingArgs.push(Number(minTimeSpentSeconds));
    }
    if (maxTimeSpentSeconds) {
      havingConds.push(
        'TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at)) <= ?',
      );
      havingArgs.push(Number(maxTimeSpentSeconds));
    }

    const sql = `
      SELECT 
        u.id AS user_id,
        u.full_name AS candidate_name,
        CONVERT_TZ(MIN(ual.created_at), @@session.time_zone, '+05:30') AS login_time,
        CONVERT_TZ(MAX(ual.created_at), @@session.time_zone, '+05:30') AS logout_time,
        TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at)) AS time_spent_seconds,
        COUNT(DISTINCT CASE WHEN (ual.action_type = 'job_details_view' OR ual.action_type = 'job_view' OR ual.job_id IS NOT NULL) THEN ual.job_id END) AS jobs_viewed,
        COUNT(DISTINCT CASE 
          WHEN (ual.action_type = 'job_apply_submit' OR ual.action_type = 'job_apply_complete') 
          THEN COALESCE(ual.application_id, CONCAT(ual.user_id, '-', ual.job_id))
          ELSE NULL 
        END) AS applications_submitted,
        SUBSTRING_INDEX(GROUP_CONCAT(ual.ip_address ORDER BY ual.created_at DESC), ',', 1) AS ip_address,
        SUBSTRING_INDEX(GROUP_CONCAT(ual.device_type ORDER BY ual.created_at DESC), ',', 1) AS device_type,
        SUBSTRING_INDEX(GROUP_CONCAT(ual.browser ORDER BY ual.created_at DESC), ',', 1) AS browser
      FROM users u
      INNER JOIN user_activity_logs ual ON ual.user_id = u.id 
        AND ual.user_type = 'seeker'
        ${joinDateFilter}
      WHERE ${whereConds.join(' AND ')}
      GROUP BY u.id, u.full_name
      HAVING ${havingConds.join(' AND ')}
      ORDER BY applications_submitted DESC
      LIMIT ? OFFSET ?
    `;
    const take = Math.min(Math.max(parseInt(String(limit), 10) || 10, 1), 200);
    const skip = (Math.max(parseInt(String(page), 10) || 1, 1) - 1) * take;
    const rows = await this.activityRepo.manager.query(sql, [
      ...joinArgs,
      ...args,
      ...havingArgs,
      take,
      skip,
    ]);

    // Count query for pagination
    const countSql = `
      SELECT COUNT(*) as total FROM (
        SELECT u.id
        FROM users u
        INNER JOIN user_activity_logs ual ON ual.user_id = u.id 
          AND ual.user_type = 'seeker'
          ${joinDateFilter}
        WHERE ${whereConds.join(' AND ')}
        GROUP BY u.id
        HAVING ${havingConds.join(' AND ')}
      ) as subquery
    `;
    const countRes = await this.activityRepo.manager.query(countSql, [
      ...joinArgs,
      ...args,
      ...havingArgs,
    ]);
    const total = parseInt(countRes[0]?.total || 0, 10);

    return {
      success: true,
      data: rows,
      meta: {
        total: total,
        page: parseInt(String(page), 10) || 1,
        totalPages: Math.ceil(total / take),
        limit: take,
      },
    };
  }

  /**
   * Get filtered logs for Admin Dashboard
   */
  async getLogs(params: {
    page?: number;
    limit?: number;
    userType?: string;
    actionTypes?: string[];
    dateRange?: number;
    from?: string;
    to?: string;
    userId?: number;
    companyId?: number;
    excludeAdmin?: boolean;
  }): Promise<{
    logs: ActivityLogs[];
    total: number;
    page: number;
    totalPages: number;
  }> {
    try {
      const page = params.page || 1;
      const limit = params.limit || 20;
      const offset = (page - 1) * limit;

      const qb = this.activityRepo.createQueryBuilder('log');

      // Date Filtering
      if (params.from || params.to || params.dateRange) {
        // Use the filter builder which returns SQL string and params
        const dateFilter = await this.buildDateFilter(
          params.dateRange,
          params.from,
          params.to,
        );
        qb.andWhere(dateFilter.sqlFilter, dateFilter.params);
      }

      // User Type Filter
      if (params.userType && params.userType !== 'all') {
        qb.andWhere('log.user_type = :userType', { userType: params.userType });
      }

      // Exclude Admin Filter
      if (params.excludeAdmin) {
        qb.andWhere('log.user_type != :adminType', { adminType: 'admin' });
      }

      // Action Type Filter (Support multiple)
      if (params.actionTypes && params.actionTypes.length > 0) {
        qb.andWhere('log.action_type IN (:...actionTypes)', {
          actionTypes: params.actionTypes,
        });
      }

      // UserId Filter
      if (params.userId) {
        qb.andWhere('log.user_id = :userId', { userId: params.userId });
      }

      // CompanyId Filter
      if (params.companyId) {
        qb.andWhere('log.company_id = :companyId', {
          companyId: params.companyId,
        });
      }

      // Ordering
      qb.orderBy('log.created_at', 'DESC');

      // Pagination
      qb.skip(offset).take(limit);

      const [logs, total] = await qb.getManyAndCount();

      // Enrich logs with Ownership information if a userId is provided (context for comparison)
      if (params.userId && logs.length > 0) {
        // 1. Determine the context owner (Company ID or Associate ID)
        const user = await this.dataSource.query(
          'SELECT role FROM users WHERE id = ?',
          [params.userId],
        );
        const userRole = user[0]?.role;

        let contextId: number | null = null;
        let contextType: 'company' | 'associate' | null = null;

        if (userRole === 'associate') {
          const assoc = await this.dataSource.query(
            'SELECT id FROM associates WHERE user_id = ?',
            [params.userId],
          );
          contextId = assoc[0]?.id;
          contextType = 'associate';
        } else if (userRole === 'recruiter') {
          const comp = await this.dataSource.query(
            'SELECT id FROM companies WHERE user_id = ?',
            [params.userId],
          );
          contextId = comp[0]?.id;
          contextType = 'company';
        }

        // 2. Map ownership if we have a context
        if (contextId) {
          // Batch fetch job owners for performance
          const jobIds = [
            ...new Set(logs.filter((l) => l.job_id).map((l) => l.job_id)),
          ];
          const jobOwners: Record<
            number,
            { company_id: number; associate_id: number }
          > = {};

          if (jobIds.length > 0) {
            const jobs = await this.dataSource.query(
              `SELECT id, company_id, associate_id FROM job_posts WHERE id IN (${jobIds.join(',')})`,
            );
            jobs.forEach((j: any) => {
              jobOwners[j.id] = {
                company_id: j.company_id,
                associate_id: j.associate_id,
              };
            });
          }

          logs.forEach((log) => {
            if (log.job_id && jobOwners[log.job_id]) {
              const owner = jobOwners[log.job_id];
              const isOwn =
                contextType === 'company'
                  ? owner.company_id === contextId
                  : owner.associate_id === contextId;

              log.action_details = log.action_details || {};
              log.action_details.isOwnJob = isOwn;
            }
          });
        }
      }

      return {
        logs,
        total,
        page,
        totalPages: Math.ceil(total / limit),
      };
    } catch (error) {
      console.error('Error fetching activity logs:', error);
      throw error;
    }
  }
}

// 16. Admin aggregate: JV Companies summary (dummy/admin companies)
@Injectable()
export class JVCompaniesSummaryService {
  constructor(private readonly dataSource: DataSource) {}

  // Helper function to normalize date strings (add time if missing)
  private normalizeDate(dateStr: string, isEndOfDay: boolean = false): string {
    if (!dateStr) return dateStr;
    // If date string is just YYYY-MM-DD, add time component
    if (/^\d{4}-\d{2}-\d{2}$/.test(dateStr)) {
      return isEndOfDay ? `${dateStr} 23:59:59` : `${dateStr} 00:00:00`;
    }
    // If it already has time, return as is
    return dateStr;
  }

  async getJVCompaniesSummary(params: {
    from?: string;
    to?: string;
    companyId?: number;
  }): Promise<any> {
    try {
      const { from, to, companyId } = params || {};
      const normalizedFrom = from ? this.normalizeDate(from, false) : undefined;
      const normalizedTo = to ? this.normalizeDate(to, true) : undefined;

      // Company meta (city/state) for JV companies
      const metaWhere: string[] = ['c.is_admin_company = 1'];
      const metaParams: any[] = [];
      if (companyId) {
        metaWhere.push('c.id = ?');
        metaParams.push(companyId);
      }
      const metaSql = `
        SELECT c.id as company_id,
               c.name as company_name,
               ci.city_name as city_name,
               s.state_name as state_name
        FROM companies c
        LEFT JOIN city ci ON ci.city_id = c.city_id
        LEFT JOIN state s ON s.state_id = c.state_id
        WHERE ${metaWhere.join(' AND ')}
      `;
      const metaRows = await (this as any).dataSource.query(
        metaSql,
        metaParams,
      );

      // Posts per company (JV only) - only companies with posts
      const postsWhere: string[] = ['c.is_admin_company = 1'];
      const postsParams: any[] = [];
      if (normalizedFrom) {
        postsWhere.push('jp.created_at >= ?');
        postsParams.push(normalizedFrom);
      }
      if (normalizedTo) {
        postsWhere.push('jp.created_at <= ?');
        postsParams.push(normalizedTo);
      }
      if (companyId) {
        postsWhere.push('c.id = ?');
        postsParams.push(companyId);
      }
      const postsSql = `
        SELECT c.id as company_id, COUNT(DISTINCT jp.id) as posts_count
        FROM companies c
        INNER JOIN job_posts jp ON jp.company_id = c.id
        WHERE ${postsWhere.join(' AND ')}
        GROUP BY c.id
        HAVING COUNT(DISTINCT jp.id) > 0
      `;
      const postsRows = await (this as any).dataSource.query(
        postsSql,
        postsParams,
      );

      // Applications per company (JV only) - only companies with applications
      const appsWhere: string[] = ['c.is_admin_company = 1'];
      const appsParams: any[] = [];
      if (normalizedFrom) {
        appsWhere.push('ja.applied_at >= ?');
        appsParams.push(normalizedFrom);
      }
      if (normalizedTo) {
        appsWhere.push('ja.applied_at <= ?');
        appsParams.push(normalizedTo);
      }
      if (companyId) {
        appsWhere.push('c.id = ?');
        appsParams.push(companyId);
      }
      const appsSql = `
        SELECT c.id as company_id, COUNT(ja.id) as applications_total
        FROM companies c
        INNER JOIN job_posts jp ON jp.company_id = c.id
        INNER JOIN job_applications ja ON ja.job_post_id = jp.id
        WHERE ${appsWhere.join(' AND ')}
        GROUP BY c.id
        HAVING COUNT(ja.id) > 0
      `;
      const appsRows = await (this as any).dataSource.query(
        appsSql,
        appsParams,
      );

      // Applications seen from activity logs (JV only)
      const seenConds: string[] = [
        "action_type = 'application_view'",
        "user_type = 'recruiter'",
        'EXISTS (SELECT 1 FROM companies cx WHERE cx.id = company_id AND cx.is_admin_company = 1)',
      ];
      const seenParams: any[] = [];
      if (normalizedFrom) {
        seenConds.push('created_at >= ?');
        seenParams.push(normalizedFrom);
      }
      if (normalizedTo) {
        seenConds.push('created_at <= ?');
        seenParams.push(normalizedTo);
      }
      if (companyId) {
        seenConds.push('company_id = ?');
        seenParams.push(companyId);
      }
      const seenSql = `
        SELECT company_id, COUNT(*) as applications_seen
        FROM user_activity_logs
        WHERE ${seenConds.join(' AND ')}
        GROUP BY company_id
      `;
      const seenRows = await (this as any).dataSource.query(
        seenSql,
        seenParams,
      );

      // Merge
      const appsMap = new Map<number, number>();
      for (const r of appsRows)
        appsMap.set(
          parseInt(r.company_id),
          parseInt(r.applications_total || 0),
        );
      const seenMap = new Map<number, number>();
      for (const r of seenRows)
        seenMap.set(parseInt(r.company_id), parseInt(r.applications_seen || 0));

      const metaMap = new Map<
        number,
        {
          company_name: string;
          city_name: string | null;
          state_name: string | null;
        }
      >();
      for (const m of metaRows) {
        metaMap.set(parseInt(m.company_id), {
          company_name: m.company_name,
          city_name: m.city_name || null,
          state_name: m.state_name || null,
        });
      }

      const postsMap = new Map<number, number>();
      for (const p of postsRows) {
        postsMap.set(parseInt(p.company_id), parseInt(p.posts_count || 0));
      }

      // Only include companies that have posts or applications
      // Only include companies that have posts or applications (already filtered in SQL)
      const allCompanyIds = new Set<number>([
        ...Array.from(postsMap.keys()),
        ...Array.from(appsMap.keys()),
      ]);

      const data = Array.from(allCompanyIds)
        .map((cid) => {
          const meta = metaMap.get(cid);
          const postsCount = postsMap.get(cid) || 0;
          const appsCount = appsMap.get(cid) || 0;
          return {
            company_id: cid,
            company_name: meta?.company_name || null,
            city_name: meta?.city_name || null,
            state_name: meta?.state_name || null,
            posts_count: postsCount,
            applications_total: appsCount,
            applications_seen: seenMap.get(cid) || 0,
          };
        })
        .sort((a, b) => {
          const p = (b.posts_count || 0) - (a.posts_count || 0);
          if (p !== 0) return p;
          return (b.applications_total || 0) - (a.applications_total || 0);
        });

      return { success: true, data };
    } catch (error) {
      console.error('Error fetching JV companies summary:', error);
      return { success: false, error: (error as any).message };
    }
  }
}

// 12. Get logs with filters and pagination
@Injectable()
export class ActivityLogsQueryService {
  constructor(
    @InjectRepository(ActivityLogs)
    private activityRepo: Repository<ActivityLogs>,
  ) {}

  async getLogs(params: any): Promise<any> {
    const {
      dateRange = 30,
      page = 1,
      limit = 25,
      sortBy = 'created_at',
      sortOrder = 'DESC',
      userType,
      actionType,
      status,
      deviceType,
      browser,
      ipAddress,
      userId,
      visitorId,
      companyId,
      jobId,
      from,
      to,
    } = params || {};

    const qb = this.activityRepo.createQueryBuilder('log');

    // Date filter
    if (from || to) {
      if (from) qb.andWhere('log.created_at >= :from', { from });
      if (to) qb.andWhere('log.created_at <= :to', { to });
    } else {
      qb.andWhere('log.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)', {
        days: dateRange,
      });
    }

    // Field filters
    if (userType) {
      const list = Array.isArray(userType)
        ? userType
        : String(userType).split(',');
      qb.andWhere('log.user_type IN (:...userTypes)', { userTypes: list });
    }
    if (actionType) {
      const list = Array.isArray(actionType)
        ? actionType
        : String(actionType).split(',');
      qb.andWhere('log.action_type IN (:...actionTypes)', {
        actionTypes: list,
      });
    }
    if (status) {
      const list = Array.isArray(status) ? status : String(status).split(',');
      qb.andWhere('log.status IN (:...statuses)', { statuses: list });
    }
    if (deviceType) {
      const list = Array.isArray(deviceType)
        ? deviceType
        : String(deviceType).split(',');
      qb.andWhere('log.device_type IN (:...deviceTypes)', {
        deviceTypes: list,
      });
    }
    if (browser) {
      qb.andWhere('log.browser LIKE :browser', { browser: `%${browser}%` });
    }
    if (ipAddress) {
      qb.andWhere('log.ip_address = :ip', { ip: ipAddress });
    }
    if (userId) {
      qb.andWhere('log.user_id = :uid', { uid: userId });
    }
    if (visitorId) {
      qb.andWhere('log.visitor_id = :vid', { vid: visitorId });
    }
    if (companyId) {
      qb.andWhere('log.company_id = :cid', { cid: companyId });
    }
    if (jobId) {
      qb.andWhere('log.job_id = :jid', { jid: jobId });
    }

    // Exclude admin by default unless explicitly requested
    if (!userType) {
      qb.andWhere('log.user_type != :admin', { admin: 'admin' });
    }

    // Sorting, pagination
    const safeSortBy = [
      'created_at',
      'status',
      'action_type',
      'user_type',
    ].includes(sortBy)
      ? sortBy
      : 'created_at';
    const safeOrder =
      String(sortOrder).toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    qb.orderBy(`log.${safeSortBy}`, safeOrder as any);

    const take = Math.min(Math.max(parseInt(limit, 10) || 25, 1), 200);
    const skip = (Math.max(parseInt(page, 10) || 1, 1) - 1) * take;
    qb.take(take).skip(skip);

    const [rows, total] = await qb.getManyAndCount();

    return {
      success: true,
      data: rows,
      meta: {
        total,
        page: parseInt(page, 10) || 1,
        limit: take,
        totalPages: Math.max(1, Math.ceil(total / take)),
      },
    };
  }
}
