import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class ReportsService {
  constructor(private readonly dataSource: DataSource) {}
  // Re-triggering build to recover watcher - force refresh

  async getOverview(
    from?: string,
    to?: string,
    company?: string,
    alertPage?: number,
    alertLimit?: number,
  ) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();
    const safeAlertLimit = Math.min(
      Math.max(Math.floor(alertLimit || 5), 1),
      50,
    );
    const requestedAlertPage = Math.max(Math.floor(alertPage || 1), 1);

    const companyFilter = company ? ' AND c.name = ?' : '';
    const companyParams = company ? [company] : [];

    const [kpis] = await this.dataSource.query(
      `
      SELECT
        (SELECT COUNT(*) FROM companies c WHERE is_deleted = 0 AND status = 1 AND is_admin_company = 0 AND is_approved = 1 ${companyFilter}) AS activeCompanies,
        (SELECT COUNT(*) FROM unlocked_profiles up INNER JOIN companies c ON up.recruiter_user_id = c.user_id WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND up.action_type = 'CV Download' AND DATE(up.unlocked_at) = CURRENT_DATE() ${companyFilter}) AS cvViewsToday,
        (SELECT COUNT(*) FROM job_posts jp INNER JOIN companies c ON jp.company_id = c.id WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND DATE(jp.created_at) = CURRENT_DATE() ${companyFilter}) AS jobsToday,
        (SELECT COUNT(*) FROM job_applications ja INNER JOIN job_posts jp ON ja.job_post_id = jp.id INNER JOIN companies c ON jp.company_id = c.id WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND DATE(ja.applied_at) = CURRENT_DATE() ${companyFilter}) AS appsToday,
        (SELECT COALESCE(SUM(total_amount), 0) FROM payments p INNER JOIN companies c ON p.user_id = c.user_id WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND p.status = 'success' AND DATE_FORMAT(p.created_at, '%Y-%m') = DATE_FORMAT(CURRENT_DATE(), '%Y-%m') ${companyFilter}) AS revenueMonth
      `,
      [
        ...companyParams,
        ...companyParams,
        ...companyParams,
        ...companyParams,
        ...companyParams,
      ],
    );

    const topCompanies = await this.dataSource.query(
      `
      SELECT 
        c.id, 
        c.name,
        (SELECT COUNT(*) FROM unlocked_profiles up WHERE up.recruiter_user_id = c.user_id AND up.action_type = 'CV Download') AS cvViews,
        (SELECT COUNT(*) FROM job_posts jp WHERE jp.company_id = c.id) AS jobPosts,
        (SELECT COUNT(*) FROM job_applications ja 
         INNER JOIN job_posts jp ON ja.job_post_id = jp.id 
         WHERE jp.company_id = c.id) AS applications,
        COALESCE((SELECT SUM(total_amount) FROM payments p WHERE p.user_id = c.user_id AND p.status = 'success'), 0) AS revenue,
        COALESCE((SELECT GROUP_CONCAT(pl.name ORDER BY us.created_at DESC SEPARATOR ', ') FROM user_subscriptions us INNER JOIN plans pl ON us.plan_id = pl.id WHERE us.user_id = c.user_id AND us.status = 'active'), 'Free') AS plan
      FROM companies c
      WHERE c.is_deleted = 0 AND c.status = 1 AND c.is_admin_company = 0 AND c.is_approved = 1 ${companyFilter}
      ORDER BY applications DESC, revenue DESC
      LIMIT 10
    `,
      [...companyParams],
    );

    const funnel = await this.dataSource.query(
      `
      SELECT 'Job Views' AS stage, COUNT(*) AS count, '#5b5ef4' AS color 
      FROM job_views jv 
      INNER JOIN job_posts jp ON jv.job_post_id = jp.id 
      INNER JOIN companies c ON jp.company_id = c.id 
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND jv.viewed_at >= ? AND jv.viewed_at <= ? ${companyFilter}
      UNION ALL
      SELECT 'Started Application' AS stage, COUNT(*), '#3b82f6' 
      FROM user_activity_logs al
      INNER JOIN job_posts jp ON al.job_id = jp.id 
      INNER JOIN companies c ON jp.company_id = c.id 
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND al.action_type = 'apply_button_click' AND al.created_at >= ? AND al.created_at <= ? ${companyFilter}
      UNION ALL
      SELECT 'Completed Application' AS stage, COUNT(*), '#8b5cf6' 
      FROM job_applications ja 
      INNER JOIN job_posts jp ON ja.job_post_id = jp.id 
      INNER JOIN companies c ON jp.company_id = c.id 
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND ja.applied_at >= ? AND ja.applied_at <= ? ${companyFilter}
      UNION ALL
      SELECT 'Shortlisted' AS stage, COUNT(*), '#f59e0b' 
      FROM job_applications ja 
      INNER JOIN job_posts jp ON ja.job_post_id = jp.id 
      INNER JOIN companies c ON jp.company_id = c.id 
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND ja.status = 'Shortlisted' AND ja.applied_at >= ? AND ja.applied_at <= ? ${companyFilter}
      UNION ALL
      SELECT 'Hired' AS stage, COUNT(*), '#10b981' 
      FROM job_applications ja 
      INNER JOIN job_posts jp ON ja.job_post_id = jp.id 
      INNER JOIN companies c ON jp.company_id = c.id 
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND ja.status = 'Hired' AND ja.applied_at >= ? AND ja.applied_at <= ? ${companyFilter}
    `,
      [
        dFrom,
        dTo,
        ...companyParams,
        dFrom,
        dTo,
        ...companyParams,
        dFrom,
        dTo,
        ...companyParams,
        dFrom,
        dTo,
        ...companyParams,
        dFrom,
        dTo,
        ...companyParams,
      ],
    );

    const trend = await this.dataSource.query(
      `
      SELECT 
        DATE_FORMAT(d.d, '%Y-%m-%d') as date,
        COALESCE((SELECT COUNT(*) FROM unlocked_profiles up INNER JOIN companies c ON up.recruiter_user_id = c.user_id WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND up.action_type = 'CV Download' AND DATE(up.unlocked_at) = DATE(d.d) ${companyFilter}), 0) as cvViews,
        COALESCE((SELECT COUNT(*) FROM job_posts jp INNER JOIN companies c ON jp.company_id = c.id WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND DATE(jp.created_at) = DATE(d.d) ${companyFilter}), 0) as jobPosts,
        COALESCE((SELECT COUNT(*) FROM job_applications ja INNER JOIN job_posts jp ON ja.job_post_id = jp.id INNER JOIN companies c ON jp.company_id = c.id WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND DATE(ja.applied_at) = DATE(d.d) ${companyFilter}), 0) as applications
      FROM (
        SELECT DATE_SUB(DATE(?), INTERVAL (a.n + b.n * 10) DAY) as d
        FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
      ) d
      WHERE d.d >= ? AND d.d <= ?
      ORDER BY date ASC
    `,
      [...companyParams, ...companyParams, ...companyParams, dTo, dFrom, dTo],
    );

    const [alertsCountRes] = await this.dataSource.query(
      `
      SELECT COUNT(*) as total
      FROM user_subscriptions us
      JOIN plans pl ON us.plan_id = pl.id
      JOIN companies c ON us.user_id = c.user_id
      WHERE us.status = 'active'
        AND DATE(us.ends_at) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
        AND c.is_admin_company = 0
        AND c.is_approved = 1
        ${companyFilter}
    `,
      [...companyParams],
    );

    const totalAlerts = parseInt(alertsCountRes?.total || '0', 10);
    const alertPages = Math.max(1, Math.ceil(totalAlerts / safeAlertLimit));
    const safeAlertPage = Math.min(requestedAlertPage, alertPages);
    const alertOffset = (safeAlertPage - 1) * safeAlertLimit;

    const planAlertsRaw = await this.dataSource.query(
      `
      SELECT 
        us.id,
        pl.name AS plan_name,
        c.name AS company_name,
        DATE_FORMAT(us.ends_at, '%Y-%m-%d %H:%i:%s') as ends_at
      FROM user_subscriptions us
      JOIN plans pl ON us.plan_id = pl.id
      JOIN companies c ON us.user_id = c.user_id
      WHERE us.status = 'active'
        AND DATE(us.ends_at) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
        AND c.is_admin_company = 0
        AND c.is_approved = 1
        ${companyFilter}
      ORDER BY us.ends_at ASC
      LIMIT ${safeAlertLimit} OFFSET ${alertOffset}
    `,
      [...companyParams],
    );

    const alerts = planAlertsRaw.map((r: any) => {
      const endsAt = r.ends_at ? new Date(r.ends_at) : null;
      const now = new Date();
      // Normalize to dates (ignore time)
      const toDate = (d: Date) =>
        new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate()));
      let msgDate = '';
      if (endsAt) {
        const dEnds = toDate(endsAt).getTime();
        const dNow = toDate(now).getTime();
        const diffDays = Math.round((dEnds - dNow) / (1000 * 60 * 60 * 24));
        if (diffDays === 0) msgDate = 'expiring today';
        else if (diffDays === 1) msgDate = 'expiring tomorrow';
        else
          msgDate = `is expiring on ${endsAt.toLocaleString('en-US', { month: 'short', day: '2-digit' })}`;
      } else {
        msgDate = 'is expiring soon';
      }

      const msg = `${r.plan_name} for ${r.company_name} ${msgDate}`;
      const time = endsAt ? endsAt.toISOString().split('T')[0] : null;
      return { id: `plan-${r.id}`, type: 'warning', msg, time };
    });

    const firstCount = funnel.length > 0 ? parseInt(funnel[0].count, 10) : 0;

    const sessionRes = await this.dataSource.query(
      `
      SELECT AVG(TIMESTAMPDIFF(SECOND, min_t, max_t)) as avg_s
      FROM (
        SELECT user_id, DATE(created_at) as dt, MIN(created_at) as min_t, MAX(created_at) as max_t
        FROM user_activity_logs
        WHERE created_at >= ? AND created_at <= ? AND user_type IN ('recruiter', 'company', 'franchise')
        GROUP BY user_id, DATE(created_at)
        HAVING TIMESTAMPDIFF(SECOND, min_t, max_t) > 0
      ) as sessions
    `,
      [dFrom, dTo],
    );
    const avgS = sessionRes[0]?.avg_s ? parseInt(sessionRes[0].avg_s, 10) : 0;
    const avgSessionStr =
      avgS > 0 ? `${Math.floor(avgS / 60)}m ${avgS % 60}s` : '0m 0s';

    return {
      success: true,
      data: {
        kpis: {
          activeCompanies: parseInt(kpis.activeCompanies, 10),
          cvViewsToday: parseInt(kpis.cvViewsToday || '0', 10),
          jobsToday: parseInt(kpis.jobsToday || '0', 10),
          appsToday: parseInt(kpis.appsToday || '0', 10),
          revenueMonth: parseFloat(kpis.revenueMonth || '0'),
          avgSession: avgSessionStr,
        },
        topCompanies: topCompanies.map((c: any) => ({
          ...c,
          cvViews: parseInt(c.cvViews, 10),
          jobPosts: parseInt(c.jobPosts, 10),
          applications: parseInt(c.applications, 10),
          revenue: parseFloat(c.revenue),
        })),
        trend: trend.map((t: any) => ({
          ...t,
          cvViews: parseInt(t.cvViews),
          jobPosts: parseInt(t.jobPosts),
          applications: parseInt(t.applications),
        })),
        funnel: funnel.map((f: any) => {
          const count = parseInt(f.count, 10);
          return {
            ...f,
            count,
            pct:
              firstCount > 0
                ? Math.round((count / firstCount) * 100)
                : count > 0
                  ? 100
                  : 0,
          };
        }),
        alerts,
        alertsPagination: {
          page: safeAlertPage,
          limit: safeAlertLimit,
          total: totalAlerts,
          pages: alertPages,
        },
      },
    };
  }

  async getCvAccess(from?: string, to?: string, company?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    const companyFilter = company ? ' AND c.name = ?' : '';
    const companyParams = company ? [company] : [];

    const companies = await this.dataSource.query(
      `
      SELECT 
        c.id, 
        c.name AS company,
        (SELECT COUNT(*) FROM unlocked_profiles up WHERE up.recruiter_user_id = c.user_id AND up.action_type = 'Profile View' AND up.unlocked_at >= ? AND up.unlocked_at <= ?) AS cvViews,
        (SELECT COUNT(*) FROM unlocked_profiles up WHERE up.recruiter_user_id = c.user_id AND up.action_type = 'CV Download' AND up.unlocked_at >= ? AND up.unlocked_at <= ?) AS downloads
      FROM companies c
      WHERE c.is_deleted = 0 AND c.status = 1 AND c.is_admin_company = 0 AND c.is_approved = 1 ${companyFilter}
      ORDER BY downloads DESC
    `,
      [dFrom, dTo, dFrom, dTo, ...companyParams],
    );

    const companyIds = companies.map((c: any) => c.id);
    let jobs = [];
    if (companyIds.length > 0) {
      jobs = await this.dataSource.query(
        `
        SELECT 
          jp.id, jp.company_id, jp.title,
          DATE_FORMAT(jp.created_at, '%Y-%m-%d') as posted,
          (
            SELECT COUNT(*)
            FROM unlocked_profiles up
            WHERE up.recruiter_user_id = c.user_id
              AND up.action_type = 'Profile View'
              AND up.unlocked_at >= ? AND up.unlocked_at <= ?
              AND EXISTS (
                SELECT 1
                FROM job_applications ja3
                INNER JOIN seeker_profiles sp3 ON sp3.id = ja3.seeker_id
                WHERE ja3.job_post_id = jp.id
                  AND sp3.user_id = up.seeker_user_id
              )
          ) AS views,
          (SELECT COUNT(*) FROM job_applications ja WHERE ja.job_post_id = jp.id AND ja.applied_at >= ? AND ja.applied_at <= ?) AS applicants,
          (
            SELECT COUNT(*)
            FROM unlocked_profiles up
            WHERE up.recruiter_user_id = c.user_id
              AND up.action_type = 'CV Download'
              AND up.unlocked_at >= ? AND up.unlocked_at <= ?
              AND EXISTS (
                SELECT 1
                FROM job_applications ja2
                INNER JOIN seeker_profiles sp2 ON sp2.id = ja2.seeker_id
                WHERE ja2.job_post_id = jp.id
                  AND sp2.user_id = up.seeker_user_id
              )
          ) AS downloads
        FROM job_posts jp
        INNER JOIN companies c ON c.id = jp.company_id
        WHERE jp.company_id IN (${companyIds.join(',')})
      `,
        [dFrom, dTo, dFrom, dTo, dFrom, dTo],
      );
    }

    const jobIds = jobs.map((j: any) => j.id);
    let logs = [];
    if (jobIds.length > 0) {
      logs = await this.dataSource.query(
        `
        SELECT 
          ja.id, ja.job_post_id,
          COALESCE(u.full_name, CONCAT('Candidate #', COALESCE(sp.user_id, ja.seeker_id))) AS candidate,
          DATE_FORMAT(ja.applied_at, '%Y-%m-%d %H:%i:%s') AS time,
          (
            SELECT DATE_FORMAT(up.unlocked_at, '%Y-%m-%d %H:%i:%s')
            FROM unlocked_profiles up
            INNER JOIN job_posts jp3 ON jp3.id = ja.job_post_id
            INNER JOIN companies c3 ON c3.id = jp3.company_id
            WHERE up.recruiter_user_id = c3.user_id
              AND up.seeker_user_id = COALESCE(sp.user_id, ja.seeker_id)
              AND up.action_type = 'Profile View'
              AND up.unlocked_at >= ? AND up.unlocked_at <= ?
            ORDER BY up.unlocked_at DESC
            LIMIT 1
          ) AS viewed_at,
          (
            SELECT DATE_FORMAT(up.unlocked_at, '%Y-%m-%d %H:%i:%s')
            FROM unlocked_profiles up
            INNER JOIN job_posts jp2 ON jp2.id = ja.job_post_id
            INNER JOIN companies c2 ON c2.id = jp2.company_id
            WHERE up.recruiter_user_id = c2.user_id
              AND up.seeker_user_id = COALESCE(sp.user_id, ja.seeker_id)
              AND up.action_type = 'CV Download'
              AND up.unlocked_at >= ? AND up.unlocked_at <= ?
            ORDER BY up.unlocked_at DESC
            LIMIT 1
          ) AS download_at,
          'Applied' AS action
        FROM job_applications ja 
        LEFT JOIN seeker_profiles sp ON sp.id = ja.seeker_id
        LEFT JOIN users u ON u.id = sp.user_id
        WHERE ja.job_post_id IN (${jobIds.join(',')}) AND ja.applied_at >= ? AND ja.applied_at <= ?
      `,
        [dFrom, dTo, dFrom, dTo, dFrom, dTo],
      );
    }

    for (const c of companies) {
      c.jobs = jobs
        .filter((j: any) => j.company_id == c.id)
        .map((j: any) => ({
          ...j,
          views: parseInt(j.views, 10) || 0,
          downloads: parseInt(j.downloads, 10) || 0,
          applicants: parseInt(j.applicants, 10) || 0,
          logs: logs.filter((l: any) => l.job_post_id == j.id),
        }));
    }

    return { success: true, data: companies };
  }

  async getJobsApplications(from?: string, to?: string, company?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    const companyFilter = company ? ' AND c.name = ?' : '';
    const companyParams = company ? [company] : [];

    const companies = await this.dataSource.query(
      `
      SELECT 
        c.id, 
        c.name AS company,
        (SELECT COUNT(*) FROM job_posts jp WHERE jp.company_id = c.id AND jp.created_at >= ? AND jp.created_at <= ?) AS jobsPosted,
        (SELECT COUNT(*) FROM job_applications ja INNER JOIN job_posts jp ON ja.job_post_id = jp.id WHERE jp.company_id = c.id AND ja.applied_at >= ? AND ja.applied_at <= ?) AS totalApps
      FROM companies c
      WHERE c.is_deleted = 0 AND c.status = 1 AND c.is_admin_company = 0 AND c.is_approved = 1 ${companyFilter}
      ORDER BY totalApps DESC
    `,
      [dFrom, dTo, dFrom, dTo, ...companyParams],
    );

    const companyIds = companies.map((c: any) => c.id);
    let jobs = [];
    if (companyIds.length > 0) {
      jobs = await this.dataSource.query(
        `
        SELECT 
          jp.id, jp.company_id, jp.title,
          (SELECT COUNT(*) FROM job_applications ja WHERE ja.job_post_id = jp.id AND ja.applied_at >= ? AND ja.applied_at <= ?) AS applications,
          (SELECT COUNT(*) FROM job_applications ja WHERE ja.job_post_id = jp.id AND ja.status = 'Shortlisted' AND ja.applied_at >= ? AND ja.applied_at <= ?) AS shortlisted
        FROM job_posts jp WHERE jp.company_id IN (${companyIds.join(',')})
      `,
        [dFrom, dTo, dFrom, dTo],
      );
    }

    const jobIds = jobs.map((j: any) => j.id);
    let applicants = [];
    if (jobIds.length > 0) {
      applicants = await this.dataSource.query(
        `
        SELECT 
          ja.id, ja.job_post_id, 
          COALESCE(u.full_name, CONCAT('Candidate #', ja.seeker_id)) AS name,
          DATE_FORMAT(ja.applied_at, '%Y-%m-%d') AS appliedDate,
          ja.status
        FROM job_applications ja 
        LEFT JOIN seeker_profiles sp ON ja.seeker_id = sp.id
        LEFT JOIN users u ON sp.user_id = u.id
        WHERE ja.job_post_id IN (${jobIds.join(',')}) AND ja.applied_at >= ? AND ja.applied_at <= ?
      `,
        [dFrom, dTo],
      );
    }

    for (const c of companies) {
      c.jobs = jobs
        .filter((j: any) => j.company_id == c.id)
        .map((j: any) => ({
          ...j,
          applications: parseInt(j.applications, 10) || 0,
          shortlisted: parseInt(j.shortlisted, 10) || 0,
          applicants: applicants.filter((a: any) => a.job_post_id == j.id),
        }));
    }

    return { success: true, data: companies };
  }

  async getUsageLimits(from?: string, to?: string, company?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    const companyFilter = company ? ' AND c.name = ?' : '';
    const companyParams = company ? [company] : [];

    // Step 1 — Get Companies
    const companies = await this.dataSource.query(
      `
      SELECT
        c.id,
        c.name as company,
        w.id as wallet_id,
        c.user_id
      FROM companies c
      LEFT JOIN wallets w ON w.user_id = c.user_id
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 ${companyFilter}
      `,
      [...companyParams],
    );

    if (companies.length === 0) return { success: true, data: [] };

    const walletIds = companies
      .map((c) => c.wallet_id)
      .filter((id) => id !== null);
    const userIds = companies.map((c) => c.user_id);

    // Step 2 & 4 — Calculate Credit Allocation and Lifetime Used
    const aggregatedBalances =
      walletIds.length > 0
        ? await this.dataSource.query(
            `
      SELECT
        wallet_id,
        credit_type,
        SUM(CASE WHEN type='credit' THEN amount ELSE 0 END) as total_allocated,
        SUM(CASE WHEN type='credit' AND is_expired=0 THEN amount ELSE 0 END) as active_allocated,
        SUM(CASE WHEN type='debit' THEN amount ELSE 0 END) as lifetime_used
      FROM wallet_transactions
      WHERE wallet_id IN (${walletIds.join(',')})
      GROUP BY wallet_id, credit_type
      `,
          )
        : [];

    // Step 3 — Calculate Usage (Respect date filter)
    const usageData =
      walletIds.length > 0
        ? await this.dataSource.query(
            `
      SELECT
        wallet_id,
        credit_type,
        SUM(amount) as used
      FROM wallet_transactions
      WHERE type='debit'
        AND created_at BETWEEN ? AND ?
        AND wallet_id IN (${walletIds.join(',')})
      GROUP BY wallet_id, credit_type
      `,
            [dFrom, dTo],
          )
        : [];

    // Step 5 — Determine Plan
    const subscriptions =
      userIds.length > 0
        ? await this.dataSource.query(
            `
      SELECT
        us.user_id,
        p.name,
        us.starts_at,
        us.ends_at,
        p.validity_days
      FROM user_subscriptions us
      JOIN plans p ON p.id = us.plan_id
      WHERE us.user_id IN (${userIds.join(',')})
        AND us.status='active'
      ORDER BY us.starts_at DESC
      `,
          )
        : [];

    // Earliest Credit Expiry for Validity Step 6
    const creditExpiries =
      walletIds.length > 0
        ? await this.dataSource.query(
            `
      SELECT wallet_id, MIN(expires_at) as earliest_expiry
      FROM wallet_transactions
      WHERE type='credit'
        AND is_expired=0
        AND expires_at IS NOT NULL
        AND wallet_id IN (${walletIds.join(',')})
      GROUP BY wallet_id
      `,
          )
        : [];

    // Step 10 — Timeline Data
    const timelineData =
      walletIds.length > 0
        ? await this.dataSource.query(
            `
      SELECT
        wallet_id,
        DATE(created_at) as day,
        SUM(CASE WHEN credit_type='job' THEN amount END) as jobs,
        SUM(CASE WHEN credit_type='database' THEN amount END) as db,
        SUM(CASE WHEN credit_type='ai' THEN amount END) as ai
      FROM wallet_transactions
      WHERE type='debit'
        AND created_at BETWEEN ? AND ?
        AND wallet_id IN (${walletIds.join(',')})
      GROUP BY wallet_id, DATE(created_at)
      `,
            [dFrom, dTo],
          )
        : [];

    // Detail Page Data (Step 14) — Credit batches (Admin Credits)
    const allTransactions =
      walletIds.length > 0
        ? await this.dataSource.query(
            `SELECT wallet_id, type, credit_type, amount, reference_type, reference_id, created_at, expires_at, is_expired 
       FROM wallet_transactions 
       WHERE wallet_id IN (${walletIds.join(',')}) 
       ORDER BY created_at ASC`,
          )
        : [];

    const planDetailsRaw =
      userIds.length > 0
        ? await this.dataSource.query(
            `SELECT us.id as subscription_id, us.user_id, pl.name, us.starts_at, us.ends_at, us.status 
       FROM user_subscriptions us 
       JOIN plans pl ON us.plan_id = pl.id 
       WHERE us.user_id IN (${userIds.join(',')}) 
       ORDER BY (us.status = 'active') DESC, us.ends_at DESC`,
          )
        : [];

    const now = new Date();
    const result = companies.map((c) => {
      const walletId = c.wallet_id ? Number(c.wallet_id) : null;
      const userId = Number(c.user_id);

      const balances = (type: string) =>
        aggregatedBalances.find(
          (b) => b.wallet_id == walletId && b.credit_type === type,
        ) || { total_allocated: 0, active_allocated: 0, lifetime_used: 0 };

      const usage = (type: string) =>
        usageData.find(
          (u) => u.wallet_id == walletId && u.credit_type === type,
        ) || { used: 0 };

      const sub = subscriptions.find((s) => s.user_id == userId);
      const isSubValid = sub && sub.ends_at && new Date(sub.ends_at) >= now;

      // Detail View (Step 14) - FIFO calculation for batch breakdown
      const adminAdjustmentsParsed = [];
      const summaries: any = {
        job: { consumed: 0, expired: 0, balance: 0, total: 0 },
        ai: { consumed: 0, expired: 0, balance: 0, total: 0 },
        database: { consumed: 0, expired: 0, balance: 0, total: 0 },
      };

      let firstUsableCredit: any = null;

      if (walletId) {
        const types: Array<'job' | 'ai' | 'database'> = [
          'job',
          'ai',
          'database',
        ];
        for (const creditType of types) {
          const txs = allTransactions.filter(
            (t) => t.wallet_id == walletId && t.credit_type === creditType,
          );
          const credits = txs.filter((t) => t.type === 'credit');
          const debits = txs.filter((t) => t.type === 'debit');

          let totalSpent = debits.reduce(
            (acc, t) => acc + Number(t.amount || 0),
            0,
          );
          summaries[creditType].consumed = totalSpent;

          for (const credit of credits) {
            let batchUsed = 0;
            const creditAmount = Number(credit.amount || 0);
            summaries[creditType].total += creditAmount;

            if (totalSpent >= creditAmount) {
              batchUsed = creditAmount;
              totalSpent -= batchUsed;
            } else {
              batchUsed = totalSpent;
              totalSpent = 0;
            }

            const isExpired =
              credit.is_expired == 1 ||
              (credit.expires_at && new Date(credit.expires_at) < now);
            const batchRemaining = creditAmount - batchUsed;
            const status = isExpired
              ? 'Expired'
              : batchRemaining === 0
                ? 'Fully Used'
                : 'Active';

            if (isExpired) {
              summaries[creditType].expired += batchRemaining;
            } else {
              summaries[creditType].balance += batchRemaining;
              // FIFO Plan Logic: First credit with balance determines the plan
              if (batchRemaining > 0 && !firstUsableCredit) {
                firstUsableCredit = credit;
              }
            }

            const adminTypes = [
              'admin_adjustment',
              'refund',
              'custom_plan',
              'promotion',
              'manual_fix',
              'onboarding',
            ];
            if (adminTypes.includes(credit.reference_type)) {
              adminAdjustmentsParsed.push({
                type: creditType,
                amount: creditAmount,
                used: batchUsed,
                remaining: isExpired ? 0 : batchRemaining,
                expiry: credit.expires_at,
                is_expired: isExpired,
                status: status,
                created_at: credit.created_at,
                source: credit.reference_type,
              });
            }
          }
        }
      }

      // Final Plan Name Determination (FIFO)
      let planName = 'Free Tier';
      if (firstUsableCredit) {
        if (firstUsableCredit.reference_type === 'custom_plan') {
          planName = 'Custom Plan';
        } else if (firstUsableCredit.reference_type === 'subscription') {
          const s = planDetailsRaw.find(
            (p) => p.subscription_id == firstUsableCredit.reference_id,
          );
          planName = s ? s.name : sub ? sub.name : 'Active Plan';
        } else if (sub) {
          planName = sub.name;
        }
      } else if (isSubValid) {
        planName = sub.name;
      }

      // Step 6 — Determine Validity (Refined Logic)
      const getFriendlyValidity = (expiry: Date | string) => {
        const dNow = new Date();
        dNow.setHours(0, 0, 0, 0);
        const dExp = new Date(expiry);
        dExp.setHours(0, 0, 0, 0);

        const diffTime = dExp.getTime() - dNow.getTime();
        const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));

        if (diffDays < 0) return '-';
        if (diffDays === 0) return 'Expiring today';
        if (diffDays === 1) return 'Expiring tomorrow';
        if (diffDays <= 7) return `Expiring in ${diffDays} days`;

        return `Expires on ${dExp.toLocaleDateString('en-GB', { day: 'numeric', month: 'short' })}`;
      };

      let validity = '-';
      if (isSubValid) {
        validity = getFriendlyValidity(sub.ends_at);
      } else if (walletId) {
        const earliest = creditExpiries.find((e) => e.wallet_id == walletId);
        if (earliest && earliest.earliest_expiry) {
          validity = getFriendlyValidity(earliest.earliest_expiry);
        }
      }

      const getSummary = (type: string) => {
        const b = balances(type);
        const u = usage(type);
        const totalAllocated = Number(b.total_allocated || 0);
        const activeAllocated = Number(b.active_allocated || 0);
        const lifetimeUsed = Number(b.lifetime_used || 0);
        return {
          used: Number(u.used || 0),
          allocated: totalAllocated,
          activeAllocated: activeAllocated,
          remaining: Math.max(
            0,
            activeAllocated -
              (lifetimeUsed - (totalAllocated - activeAllocated)),
          ),
        };
      };

      const jobs = getSummary('job');
      const db = getSummary('database');
      const ai = getSummary('ai');

      // Timeline (Step 10)
      const companyTimeline = timelineData
        .filter((t) => t.wallet_id == walletId)
        .map((t) => ({
          day: t.day,
          jobs: Number(t.jobs || 0),
          db: Number(t.db || 0),
          ai: Number(t.ai || 0),
        }));

      return {
        company: c.company,
        plan: planName,
        validity: validity,

        jobsUsed: jobs.used, // This is filtered usage
        jobsTotalConsumed: summaries.job.consumed,
        jobsExpired: summaries.job.expired,
        jobsBalance: summaries.job.balance,
        jobsTotalAllocated: summaries.job.total,

        dbUsed: db.used,
        dbTotalConsumed: summaries.database.consumed,
        dbExpired: summaries.database.expired,
        dbBalance: summaries.database.balance,
        dbTotalAllocated: summaries.database.total,

        aiUsed: ai.used,
        aiTotalConsumed: summaries.ai.consumed,
        aiExpired: summaries.ai.expired,
        aiBalance: summaries.ai.balance,
        aiTotalAllocated: summaries.ai.total,

        timeline: companyTimeline,
        adminAdjustments: JSON.stringify(adminAdjustmentsParsed),
        planDetails: JSON.stringify(
          planDetailsRaw.filter((p) => p.user_id == userId),
        ),
      };
    });

    return { success: true, data: result };
  }

  async getPayments(from?: string, to?: string, company?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    const companyFilter = company ? ' AND c.name = ?' : '';
    const companyParams = company ? [company] : [];

    const data = await this.dataSource.query(
      `
      SELECT 
        p.id,
        c.name AS company,
        COALESCE(
          (SELECT GROUP_CONCAT(pl.name ORDER BY us.created_at DESC SEPARATOR ', ') FROM user_subscriptions us INNER JOIN plans pl ON us.plan_id = pl.id WHERE us.user_id = p.user_id AND us.status = 'active'),
          'Custom'
        ) AS plan,
        p.total_amount AS amountPaid,
        'Active' AS renewalStatus,
        p.created_at AS date,
        p.gateway AS method,
        p.status
      FROM payments p
      INNER JOIN companies c ON p.user_id = c.user_id
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND p.created_at >= ? AND p.created_at <= ? ${companyFilter}
      ORDER BY p.created_at DESC
      LIMIT 100
    `,
      [dFrom, dTo, ...companyParams],
    );

    return { success: true, data };
  }

  async getTopJobs(from?: string, to?: string, company?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    const companyFilter = company ? ' AND c.name = ?' : '';
    const companyParams = company ? [company] : [];

    const data = await this.dataSource.query(
      `
      SELECT 
        jp.id, 
        jp.title, 
        c.name AS company,
        (SELECT COUNT(*) FROM job_views jv WHERE jv.job_post_id = jp.id AND jv.viewed_at >= ? AND jv.viewed_at <= ?) AS views,
        (SELECT COUNT(*) FROM job_applications ja WHERE ja.job_post_id = jp.id AND ja.applied_at >= ? AND ja.applied_at <= ?) AS applications
      FROM job_posts jp
      INNER JOIN companies c ON jp.company_id = c.id
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND jp.status = 1 AND jp.created_at >= ? AND jp.created_at <= ? ${companyFilter}
      ORDER BY applications DESC
      LIMIT 10
    `,
      [dFrom, dTo, dFrom, dTo, dFrom, dTo, ...companyParams],
    );

    const result = data.map((d: any) => {
      const v = parseInt(d.views, 10) || 1; // Prevent div by 0
      const a = parseInt(d.applications, 10) || 0;
      return {
        ...d,
        conversionRate: ((a / v) * 100).toFixed(1) + '%',
      };
    });

    return { success: true, data: result };
  }

  async getEngagement(from?: string, to?: string, company?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    const companyFilter = company ? ' AND c.name = ?' : '';
    const companyParams = company ? [company] : [];

    const viewCondition = `
      (
        ual.action_type = 'job_details_view'
        OR ual.action_type = 'job_view'
        OR (ual.action_type = 'page_view' AND ual.job_id IS NOT NULL)
      )
    `;
    const applyCondition = `
      (
        ual.action_type = 'job_apply_submit'
        OR ual.action_type = 'job_apply_complete'
      )
    `;
    const timeSpentExpr =
      "COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.timeSpentSeconds')) AS UNSIGNED), 0)";
    const formatDuration = (seconds: number) => {
      const safeSeconds = Math.max(0, Math.round(seconds || 0));
      const h = Math.floor(safeSeconds / 3600);
      const m = Math.floor((safeSeconds % 3600) / 60);
      const s = safeSeconds % 60;

      if (h > 0) return `${h}h ${m}m`;
      if (m > 0) return s > 0 ? `${m}m ${s}s` : `${m}m`;
      return `${s}s`;
    };
    const formatDurationOrNA = (seconds: number) => {
      const safeSeconds = Math.max(0, Math.round(seconds || 0));
      return safeSeconds > 0 ? formatDuration(safeSeconds) : 'N/A';
    };

    const data = await this.dataSource.query(
      `
      SELECT
        jp.id,
        jp.title,
        c.name AS company,
        COALESCE(SUM(CASE WHEN ${viewCondition} THEN 1 ELSE 0 END), 0) AS views,
        COALESCE(COUNT(DISTINCT CASE WHEN ${viewCondition} THEN ual.user_id END), 0) AS uniqueViewers,
        COALESCE(
          COUNT(DISTINCT CASE WHEN ${applyCondition} THEN ual.user_id ELSE NULL END),
          0
        ) AS applications,
        COALESCE(SUM(CASE WHEN ${viewCondition} THEN ${timeSpentExpr} ELSE 0 END), 0) AS totalTimeSpentSeconds
      FROM job_posts jp
      INNER JOIN companies c ON jp.company_id = c.id
      LEFT JOIN user_activity_logs ual
        ON ual.job_id = jp.id
        AND ual.user_type = 'seeker'
        AND ual.created_at >= ? AND ual.created_at <= ?
      WHERE c.is_admin_company = 0
        AND c.is_approved = 1
        AND c.is_deleted = 0
        ${companyFilter}
      GROUP BY jp.id, jp.title, c.name
      HAVING views > 0 OR applications > 0 OR totalTimeSpentSeconds > 0
      ORDER BY views DESC, applications DESC, totalTimeSpentSeconds DESC
      LIMIT 100
    `,
      [dFrom, dTo, ...companyParams],
    );

    const candidateRows = await this.dataSource.query(
      `
      SELECT
        ual.job_id AS jobId,
        u.id AS userId,
        u.full_name AS name,
        COALESCE(SUM(CASE WHEN ${viewCondition} THEN 1 ELSE 0 END), 0) AS viewCount,
        COALESCE(
          COUNT(DISTINCT CASE WHEN ${applyCondition} THEN ual.user_id ELSE NULL END),
          0
        ) AS applicationCount,
        COALESCE(SUM(CASE WHEN ${viewCondition} THEN ${timeSpentExpr} ELSE 0 END), 0) AS timeSpentSeconds,
        COALESCE(SUM(CASE WHEN ual.action_type = 'apply_button_click' THEN 1 ELSE 0 END), 0) AS applyClicks,
        TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at)) AS spanSeconds,
        COUNT(*) AS activityCount
      FROM user_activity_logs ual
      INNER JOIN users u ON u.id = ual.user_id AND u.role = 'seeker'
      INNER JOIN job_posts jp ON jp.id = ual.job_id
      INNER JOIN companies c ON c.id = jp.company_id
      WHERE ual.user_type = 'seeker'
        AND ual.job_id IS NOT NULL
        AND ual.created_at >= ? AND ual.created_at <= ?
        AND c.is_admin_company = 0
        AND c.is_approved = 1
        AND c.is_deleted = 0
        ${companyFilter}
      GROUP BY ual.job_id, u.id, u.full_name
      HAVING viewCount > 0 OR applicationCount > 0 OR timeSpentSeconds > 0 OR applyClicks > 0
      ORDER BY timeSpentSeconds DESC, applicationCount DESC, viewCount DESC
    `,
      [dFrom, dTo, ...companyParams],
    );

    const candidatesByJob = new Map<number, any[]>();
    const jobTimeTotals = new Map<
      number,
      { totalSeconds: number; candidateCount: number }
    >();
    candidateRows.forEach((row: any) => {
      const parts: string[] = [];
      const views = parseInt(row.viewCount, 10) || 0;
      const applications = parseInt(row.applicationCount, 10) || 0;
      const applyClicks = parseInt(row.applyClicks, 10) || 0;
      const explicitSeconds = parseInt(row.timeSpentSeconds, 10) || 0;
      const spanSeconds = parseInt(row.spanSeconds, 10) || 0;
      const activityCount = parseInt(row.activityCount, 10) || 0;
      const effectiveSeconds =
        explicitSeconds > 0
          ? explicitSeconds
          : activityCount > 1
            ? spanSeconds
            : 0;

      if (views > 0) parts.push(`${views} view${views === 1 ? '' : 's'}`);
      if (applications > 0)
        parts.push(
          `${applications} application${applications === 1 ? '' : 's'}`,
        );
      if (applyClicks > 0)
        parts.push(`${applyClicks} apply click${applyClicks === 1 ? '' : 's'}`);

      const jobId = parseInt(row.jobId, 10);
      const candidate = {
        name: row.name || 'Unknown',
        timeSpent: formatDurationOrNA(effectiveSeconds),
        actions: parts.join(', ') || 'Viewed',
      };

      if (!candidatesByJob.has(jobId)) candidatesByJob.set(jobId, []);
      candidatesByJob.get(jobId)!.push(candidate);

      if (effectiveSeconds > 0) {
        const current = jobTimeTotals.get(jobId) || {
          totalSeconds: 0,
          candidateCount: 0,
        };
        current.totalSeconds += effectiveSeconds;
        current.candidateCount += 1;
        jobTimeTotals.set(jobId, current);
      }
    });

    const result = data.map((d: any) => {
      const views = parseInt(d.views, 10) || 0;
      const applications = parseInt(d.applications, 10) || 0;
      const totalTimeSpentSeconds = parseInt(d.totalTimeSpentSeconds, 10) || 0;
      const timing = jobTimeTotals.get(parseInt(d.id, 10));
      const avgSeconds =
        timing && timing.candidateCount > 0
          ? timing.totalSeconds / timing.candidateCount
          : totalTimeSpentSeconds > 0 && views > 0
            ? totalTimeSpentSeconds / views
            : 0;

      return {
        id: d.id,
        title: d.title,
        company: d.company,
        views,
        avgTime: formatDurationOrNA(avgSeconds),
        applyRate:
          views > 0 ? ((applications / views) * 100).toFixed(1) + '%' : '0%',
        candidates: candidatesByJob.get(parseInt(d.id, 10)) || [],
      };
    });

    return { success: true, data: result };
  }

  async getSessions(from?: string, to?: string, company?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    const companyFilter = company ? ' AND c.name = ?' : '';
    const companyParams = company ? [company] : [];
    const loginCond =
      "(ual.action_type LIKE 'auth_%' OR ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login' OR ual.action_type = 'login')";
    const formatDurationSmart = (seconds: number) => {
      if (!seconds || seconds <= 0) return 'N/A';
      const h = Math.floor(seconds / 3600);
      const m = Math.floor((seconds % 3600) / 60);
      if (h > 0) return `${h}h ${m}m`;
      return `${m}m`;
    };

    const data = await this.dataSource.query(
      `
      SELECT 
        c.id AS companyId,
        u.id AS recruiterId,
        c.name AS company,
        COALESCE(SUM(CASE WHEN ${loginCond} THEN 1 ELSE 0 END), 0) AS sessions,
        COALESCE(
          ROUND(
            TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at))
            / NULLIF(COUNT(DISTINCT DATE(CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30'))), 0)
          ),
          0
        ) AS avgDurationSeconds,
        DATE_FORMAT(CONVERT_TZ(MAX(CASE WHEN ${loginCond} THEN ual.created_at ELSE NULL END), @@session.time_zone, '+05:30'), '%Y-%m-%d %H:%i:%s') AS lastLogin,
        COUNT(DISTINCT DATE(CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30'))) AS activeDays
      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.created_at >= ? AND ual.created_at <= ?
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND c.is_deleted = 0 ${companyFilter}
      GROUP BY c.id, c.name, u.id
      HAVING sessions > 0 OR activeDays > 0
      ORDER BY lastLogin DESC
    `,
      [dFrom, dTo, ...companyParams],
    );

    const result = await Promise.all(
      data.map(async (d: any) => {
        const rawLogs = await this.dataSource.query(
          `
          SELECT
            DATE_FORMAT(CONVERT_TZ(sub.created_at, @@session.time_zone, '+05:30'), '%Y-%m-%d') AS day,
            DATE_FORMAT(CONVERT_TZ(sub.created_at, @@session.time_zone, '+05:30'), '%Y-%m-%d %H:%i:%s') AS ts,
            sub.action_type,
            sub.ip_address,
            sub.device_type,
            sub.browser
          FROM user_activity_logs sub
          WHERE sub.user_id = ?
            AND sub.user_type = 'recruiter'
            AND (sub.company_id = ? OR sub.company_id IS NULL)
            AND sub.created_at >= ? AND sub.created_at <= ?
          ORDER BY sub.created_at DESC
          LIMIT 5000
        `,
          [d.recruiterId, d.companyId, dFrom, dTo],
        );

        const byDay = new Map<string, any[]>();
        rawLogs.forEach((r: any) => {
          if (!byDay.has(r.day)) byDay.set(r.day, []);
          byDay.get(r.day)!.push(r);
        });

        const dayLogs = Array.from(byDay.entries())
          .sort(([a], [b]) => b.localeCompare(a))
          .map(([day, rows]) => {
            const rowsAsc = [...rows].sort((a: any, b: any) =>
              String(a.ts).localeCompare(String(b.ts)),
            );
            const firstLogin = rowsAsc.find(
              (x: any) =>
                String(x.action_type || '').startsWith('auth_') ||
                x.action_type === 'auth_signin' ||
                x.action_type === 'auth_login' ||
                x.action_type === 'login',
            );
            const lastLogout = [...rowsAsc]
              .reverse()
              .find(
                (x: any) =>
                  x.action_type === 'auth_logout' || x.action_type === 'logout',
              );
            const start = firstLogin?.ts || rowsAsc[0]?.ts || null;
            const end =
              lastLogout?.ts || rowsAsc[rowsAsc.length - 1]?.ts || null;

            let durationSec = 0;
            if (start && end) {
              const s = new Date(`${start.replace(' ', 'T')}+05:30`);
              const e = new Date(`${end.replace(' ', 'T')}+05:30`);
              durationSec = Math.max(
                0,
                Math.floor((e.getTime() - s.getTime()) / 1000),
              );
            }

            const latest = rows[0] || {};
            return {
              date: day,
              loginTime: start || 'N/A',
              logoutTime: end || 'N/A',
              duration: formatDurationSmart(durationSec),
              durationSec,
              ip: latest.ip_address || 'N/A',
              device:
                `${latest.device_type || ''} ${latest.browser || ''}`.trim() ||
                'N/A',
            };
          })
          .slice(0, 31);

        const durationSeconds = dayLogs
          .map((x: any) => Number(x.durationSec || 0))
          .filter((x: number) => x > 0);
        const avgSec = durationSeconds.length
          ? Math.round(
              durationSeconds.reduce((a: number, b: number) => a + b, 0) /
                durationSeconds.length,
            )
          : d.avgDurationSeconds
            ? Number(d.avgDurationSeconds)
            : 0;

        return {
          company: d.company,
          sessions: parseInt(d.sessions, 10),
          activeDays: parseInt(d.activeDays, 10),
          lastLogin: d.lastLogin,
          avgDuration: formatDurationSmart(avgSec),
          logs: dayLogs.map((x: any) => ({
            date: x.date,
            loginTime: x.loginTime,
            logoutTime: x.logoutTime,
            duration: x.duration,
            ip: x.ip,
            device: x.device,
          })),
        };
      }),
    );

    return { success: true, data: result };
  }

  async getCompanies() {
    const data = await this.dataSource.query(`
      SELECT DISTINCT name 
      FROM companies 
      WHERE is_deleted = 0 AND is_admin_company = 0 AND is_approved = 1
      ORDER BY name ASC
    `);
    return { success: true, data: data.map((c: any) => c.name) };
  }

  async getCityAnalytics(
    from?: string,
    to?: string,
    company?: string,
    state?: string,
    industry?: string,
    distPage: number = 1,
    appsPage: number = 1,
    demandPage: number = 1,
    emptyPage: number = 1,
    distS?: string,
    appsS?: string,
    demandS?: string,
    emptyS?: string,
    page: number = 1,
    search?: string,
  ) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setDate(new Date().getDate() - 30));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    const limit = 20;

    const companyFilter = company && company !== 'All' ? ' AND c.name = ?' : '';
    const stateFilter = state && state !== 'All' ? ' AND s.state_name = ?' : '';
    const industryFilter =
      industry && industry !== 'All' ? ' AND ind.name = ?' : '';

    const params: any[] = [dFrom, dTo];
    if (company && company !== 'All') params.push(company);
    if (state && state !== 'All') params.push(state);
    if (industry && industry !== 'All') params.push(industry);

    // 1. Distribution
    const distSearchFilter = distS ? ' AND ct.city_name LIKE ?' : '';
    const distTotal = await this.dataSource.query(
      `
      SELECT COUNT(DISTINCT ct.city_name) as total
      FROM job_posts jp
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN companies c ON jp.company_id = c.id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND jp.created_at >= ? AND jp.created_at <= ?
      ${companyFilter} ${stateFilter} ${industryFilter} ${distSearchFilter}
    `,
      distS ? [...params, `%${distS}%`] : params,
    );

    const distParams = distS
      ? [...params, `%${distS}%`, limit, (distPage - 1) * limit]
      : [...params, limit, (distPage - 1) * limit];
    const distribution = await this.dataSource.query(
      `
      SELECT ct.city_name as city, s.state_name as state, COUNT(jp.id) as jobs, '+0%' as \`change\`
      FROM job_posts jp
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN companies c ON jp.company_id = c.id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND jp.created_at >= ? AND jp.created_at <= ?
      ${companyFilter} ${stateFilter} ${industryFilter} ${distSearchFilter}
      GROUP BY ct.city_name, s.state_name
      ORDER BY jobs DESC
      LIMIT ? OFFSET ?
    `,
      distParams,
    );

    // 2. Applications
    const appsSearchFilter = appsS ? ' AND ct.city_name LIKE ?' : '';
    const appsTotal = await this.dataSource.query(
      `
      SELECT COUNT(DISTINCT ct.city_name) as total
      FROM job_applications ja
      INNER JOIN job_posts jp ON ja.job_post_id = jp.id
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN companies c ON jp.company_id = c.id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND ja.applied_at >= ? AND ja.applied_at <= ?
      ${companyFilter} ${stateFilter} ${industryFilter} ${appsSearchFilter}
    `,
      appsS ? [...params, `%${appsS}%`] : params,
    );

    const appsParams = appsS
      ? [...params, `%${appsS}%`, limit, (appsPage - 1) * limit]
      : [...params, limit, (appsPage - 1) * limit];
    const applications = await this.dataSource.query(
      `
      SELECT ct.city_name as city, s.state_name as state, COUNT(ja.id) as apps, 'Medium' as growth
      FROM job_applications ja
      INNER JOIN job_posts jp ON ja.job_post_id = jp.id
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN companies c ON jp.company_id = c.id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND ja.applied_at >= ? AND ja.applied_at <= ?
      ${companyFilter} ${stateFilter} ${industryFilter} ${appsSearchFilter}
      GROUP BY ct.city_name, s.state_name
      ORDER BY apps DESC
      LIMIT ? OFFSET ?
    `,
      appsParams,
    );

    // 3. Demand Score
    const demandSearchFilter = demandS ? ' AND ct.city_name LIKE ?' : '';
    const demandTotal = await this.dataSource.query(
      `
      SELECT COUNT(DISTINCT ct.city_name) as total
      FROM job_posts jp
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN companies c ON jp.company_id = c.id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND jp.created_at >= ? AND jp.created_at <= ?
      ${companyFilter} ${stateFilter} ${industryFilter} ${demandSearchFilter}
    `,
      demandS ? [...params, `%${demandS}%`] : params,
    );

    const demandParams = demandS
      ? [...params, `%${demandS}%`, limit, (demandPage - 1) * limit]
      : [...params, limit, (demandPage - 1) * limit];
    const demand = await this.dataSource.query(
      `
      SELECT ct.city_name as city, s.state_name as state,
             ROUND(COUNT(ja.id) / NULLIF(COUNT(DISTINCT jp.id), 0), 1) as score,
             CONCAT('1:', ROUND(COUNT(ja.id) / NULLIF(COUNT(DISTINCT jp.id), 0), 1)) as ratio
      FROM job_posts jp
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      LEFT JOIN job_applications ja ON ja.job_post_id = jp.id
      INNER JOIN companies c ON jp.company_id = c.id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE c.is_admin_company = 0 AND c.is_approved = 1 AND jp.created_at >= ? AND jp.created_at <= ?
      ${companyFilter} ${stateFilter} ${industryFilter} ${demandSearchFilter}
      GROUP BY ct.city_name, s.state_name
      ORDER BY score DESC
      LIMIT ? OFFSET ?
    `,
      demandParams,
    );

    // 4. Empty Cities
    const emptySearchFilter = emptyS ? ' AND ct.city_name LIKE ?' : '';
    const emptyCountParams: any[] = [dFrom, dTo];
    if (company && company !== 'All') emptyCountParams.push(company);
    if (state && state !== 'All') emptyCountParams.push(state);
    if (industry && industry !== 'All') emptyCountParams.push(industry);
    const outerStateParams = state && state !== 'All' ? [state] : [];

    const emptyTotal = await this.dataSource.query(
      `
      SELECT COUNT(*) as total
      FROM city ct
      INNER JOIN state s ON ct.state_id = s.state_id
      WHERE NOT EXISTS (
        SELECT 1
        FROM job_posts jp2 
        INNER JOIN companies c2 ON jp2.company_id = c2.id
        LEFT JOIN industries ind2 ON jp2.industry_id = ind2.id
        WHERE jp2.city_id = ct.city_id AND c2.is_admin_company = 0 AND c2.is_approved = 1 
          AND jp2.created_at >= ? AND jp2.created_at <= ?
        ${companyFilter.replace(/c\./g, 'c2.')} ${stateFilter.replace(/s\./g, 's2.')} ${industryFilter.replace(/ind\./g, 'ind2.')}
      )
      ${stateFilter} ${emptySearchFilter}
    `,
      emptyS
        ? [...emptyCountParams, ...outerStateParams, `%${emptyS}%`]
        : [...emptyCountParams, ...outerStateParams],
    );

    const emptyParamsWithSearch = emptyS
      ? [
          ...emptyCountParams,
          ...outerStateParams,
          `%${emptyS}%`,
          limit,
          (emptyPage - 1) * limit,
        ]
      : [
          ...emptyCountParams,
          ...outerStateParams,
          limit,
          (emptyPage - 1) * limit,
        ];

    const empty = await this.dataSource.query(
      `
      SELECT ct.city_name as city, s.state_name as state, COALESCE(DATE_FORMAT(MAX(jp.created_at), '%Y-%m-%d'), 'Never') as lastJob
      FROM city ct
      INNER JOIN state s ON ct.state_id = s.state_id
      LEFT JOIN job_posts jp ON jp.city_id = ct.city_id
      WHERE NOT EXISTS (
        SELECT 1
        FROM job_posts jp2 
        INNER JOIN companies c2 ON jp2.company_id = c2.id
        LEFT JOIN industries ind2 ON jp2.industry_id = ind2.id
        WHERE jp2.city_id = ct.city_id AND c2.is_admin_company = 0 AND c2.is_approved = 1 
          AND jp2.created_at >= ? AND jp2.created_at <= ?
        ${companyFilter.replace(/c\./g, 'c2.')} ${stateFilter.replace(/s\./g, 's2.')} ${industryFilter.replace(/ind\./g, 'ind2.')}
      )
      ${stateFilter} ${emptySearchFilter}
      GROUP BY ct.city_id, ct.city_name, s.state_name
      ORDER BY lastJob DESC
      LIMIT ? OFFSET ?
    `,
      emptyParamsWithSearch,
    );

    // 5. Detailed Jobs Table
    const tableWhere: string[] = [
      'c.is_admin_company = 0',
      'c.is_approved = 1',
      'jp.created_at >= ?',
      'jp.created_at <= ?',
    ];
    const tableParams: any[] = [dFrom, dTo];
    if (company && company !== 'All') {
      tableWhere.push('c.name = ?');
      tableParams.push(company);
    }
    if (state && state !== 'All') {
      tableWhere.push('s.state_name = ?');
      tableParams.push(state);
    }
    if (industry && industry !== 'All') {
      tableWhere.push('ind.name = ?');
      tableParams.push(industry);
    }
    if (search) {
      tableWhere.push(
        '(ct.city_name LIKE ? OR jp.title LIKE ? OR c.name LIKE ?)',
      );
      tableParams.push(`%${search}%`, `%${search}%`, `%${search}%`);
    }

    const rows = await this.dataSource.query(
      `
      SELECT
        jp.id,
        jp.title,
        ct.city_name AS city,
        s.state_name AS state,
        c.name AS company,
        DATE_FORMAT(CONVERT_TZ(jp.created_at, @@session.time_zone, '+05:30'), '%Y-%m-%d') AS postedDate,
        DATE_FORMAT(CONVERT_TZ(jp.expires_at, @@session.time_zone, '+05:30'), '%Y-%m-%d') AS lastDateToApply,
        jp.status
      FROM job_posts jp
      INNER JOIN companies c ON c.id = jp.company_id
      LEFT JOIN city ct ON ct.city_id = jp.city_id
      LEFT JOIN state s ON s.state_id = jp.state_id
      LEFT JOIN industries ind ON ind.id = jp.industry_id
      WHERE ${tableWhere.join(' AND ')}
      ORDER BY jp.created_at DESC
      LIMIT ? OFFSET ?
      `,
      [...tableParams, limit, (Math.max(page, 1) - 1) * limit],
    );

    const countRows = await this.dataSource.query(
      `
      SELECT COUNT(*) AS total
      FROM job_posts jp
      INNER JOIN companies c ON c.id = jp.company_id
      LEFT JOIN city ct ON ct.city_id = jp.city_id
      LEFT JOIN state s ON s.state_id = jp.state_id
      LEFT JOIN industries ind ON ind.id = jp.industry_id
      WHERE ${tableWhere.join(' AND ')}
      `,
      tableParams,
    );

    return {
      success: true,
      data: {
        distribution: {
          data: distribution,
          total: parseInt(distTotal[0]?.total || 0, 10),
        },
        applications: {
          data: applications,
          total: parseInt(appsTotal[0]?.total || 0, 10),
        },
        demand: {
          data: demand,
          total: parseInt(demandTotal[0]?.total || 0, 10),
        },
        empty: { data: empty, total: parseInt(emptyTotal[0]?.total || 0, 10) },
        jobs: {
          data: rows,
          meta: {
            total: parseInt(countRows[0]?.total || 0, 10),
            page,
            totalPages: Math.ceil(
              parseInt(countRows[0]?.total || 0, 10) / limit,
            ),
            limit,
          },
        },
      },
    };
  }

  async getAssociates() {
    const rows = await this.dataSource.query(`
      SELECT DISTINCT u.full_name AS name
      FROM associates a
      INNER JOIN users u ON u.id = a.user_id
      ORDER BY u.full_name ASC
    `);
    return {
      success: true,
      data: rows.map((r: any) => r.name).filter(Boolean),
    };
  }

  async getAssociateOverview(from?: string, to?: string, associate?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();
    const associateFilter =
      associate && associate !== 'All' ? ' AND u.full_name = ?' : '';
    const associateParams = associate && associate !== 'All' ? [associate] : [];
    //start

    /**
     * @developer_note
     * KPIs represent strictly "Today" (00:00:00 to 23:59:59 IST).
     * We use an explicit date range (kpisStart, kpisEnd) instead of the simpler
     * MySQL DATE() = CURDATE() to avoid timezone discrepancies between
     * the server and DB, and to align better with index-optimized lookups.
     * To revert: Remove these range calculations and use CURDATE() in the queries below.
     */
    const kpisStart = new Date();
    kpisStart.setHours(0, 0, 0, 0);
    const kpisEnd = new Date();
    kpisEnd.setHours(23, 59, 59, 999);
    //end
    const [kpis] = await this.dataSource.query(
      `
      SELECT
        (
          SELECT COUNT(*)
          FROM associates a
          INNER JOIN users u ON u.id = a.user_id
          WHERE a.status = 1 ${associateFilter}
        ) AS activeAssociates,
        (
          SELECT COUNT(*)
          FROM user_activity_logs ual
          INNER JOIN users u ON u.id = ual.user_id AND u.role = 'associate'
          WHERE ual.user_type = 'associate'
            AND ual.action_type IN ('candidate_view', 'cv_download', 'excel_download')
          
            AND ual.created_at >= ? AND ual.created_at <= ?
            -- AND DATE(ual.created_at) = CURDATE()
            ${associateFilter}
        ) AS cvViewsToday,
        (
          SELECT COUNT(*)
          FROM job_posts jp
          INNER JOIN associates a ON a.id = jp.associate_id
          INNER JOIN users u ON u.id = a.user_id
          WHERE jp.associate_id IS NOT NULL
            AND jp.created_at >= ? AND jp.created_at <= ?
            -- AND DATE(jp.created_at) = CURDATE()
            ${associateFilter}
        ) AS jobsToday,
        (
          SELECT COUNT(*)
          FROM job_applications ja
          INNER JOIN job_posts jp ON jp.id = ja.job_post_id
          INNER JOIN associates a ON a.id = jp.associate_id
          INNER JOIN users u ON u.id = a.user_id
          WHERE jp.associate_id IS NOT NULL
            AND ja.applied_at >= ? AND ja.applied_at <= ?
            -- AND DATE(ja.applied_at) = CURDATE()
            ${associateFilter}
        ) AS appsToday
      `,
      [
        ...associateParams, // Params for activeAssociates
        kpisStart,
        kpisEnd, // Params for cvViewsToday
        ...associateParams,
        kpisStart,
        kpisEnd, // Params for jobsToday
        ...associateParams,
        kpisStart,
        kpisEnd, // Params for appsToday
        ...associateParams,
      ],
    );

    const topAssociates = await this.dataSource.query(
      `
      SELECT
        a.id,
        u.full_name AS name,
        COALESCE((
          SELECT COUNT(*)
          FROM user_activity_logs ual
          WHERE ual.user_id = u.id
            AND ual.user_type = 'associate'
            AND ual.action_type IN ('candidate_view', 'cv_download', 'excel_download')
            AND ual.created_at >= ? AND ual.created_at <= ?
        ), 0) AS cvViews,
        COALESCE((
          SELECT COUNT(*)
          FROM job_posts jp
          WHERE jp.associate_id = a.id
            AND jp.created_at >= ? AND jp.created_at <= ?
        ), 0) AS jobPosts,
        COALESCE((
          SELECT COUNT(*)
          FROM job_applications ja
          INNER JOIN job_posts jp ON jp.id = ja.job_post_id
          WHERE jp.associate_id = a.id
            AND ja.applied_at >= ? AND ja.applied_at <= ?
        ), 0) AS applications,
        DATE_FORMAT(CONVERT_TZ(MAX(ual.created_at), @@session.time_zone, '+05:30'), '%Y-%m-%d %H:%i:%s') AS lastActive
      FROM associates a
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN user_activity_logs ual
        ON ual.user_id = u.id
        AND ual.user_type = 'associate'
        AND ual.created_at >= ? AND ual.created_at <= ?
      WHERE 1=1 ${associateFilter}
      GROUP BY a.id, u.full_name
      ORDER BY applications DESC, jobPosts DESC, cvViews DESC
      LIMIT 10
      `,
      [dFrom, dTo, dFrom, dTo, dFrom, dTo, dFrom, dTo, ...associateParams],
    );

    const trend = await this.dataSource.query(
      `
      SELECT 
        DATE_FORMAT(d.d, '%Y-%m-%d') AS date,
        COALESCE((
          SELECT COUNT(*)
          FROM user_activity_logs ual
          INNER JOIN users u ON u.id = ual.user_id AND u.role = 'associate'
          WHERE ual.user_type = 'associate'
            AND ual.action_type IN ('candidate_view', 'cv_download', 'excel_download')
            AND DATE(ual.created_at) = DATE(d.d)
            ${associateFilter}
        ), 0) AS cvViews,
        COALESCE((
          SELECT COUNT(*)
          FROM job_posts jp
          INNER JOIN associates a ON a.id = jp.associate_id
          INNER JOIN users u ON u.id = a.user_id
          WHERE DATE(jp.created_at) = DATE(d.d)
            ${associateFilter}
        ), 0) AS jobPosts,
        COALESCE((
          SELECT COUNT(*)
          FROM job_applications ja
          INNER JOIN job_posts jp ON jp.id = ja.job_post_id
          INNER JOIN associates a ON a.id = jp.associate_id
          INNER JOIN users u ON u.id = a.user_id
          WHERE DATE(ja.applied_at) = DATE(d.d)
            ${associateFilter}
        ), 0) AS applications
      FROM (
        SELECT DATE_SUB(DATE(?), INTERVAL (a.n + b.n * 10) DAY) AS d
        FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
      ) d
      WHERE d.d >= ? AND d.d <= ?
      ORDER BY date ASC
      `,
      [
        ...associateParams,
        ...associateParams,
        ...associateParams,
        dTo,
        dFrom,
        dTo,
      ],
    );

    const funnel = await this.dataSource.query(
      `
      SELECT 'Job Views' AS stage, COUNT(*) AS count, '#5b5ef4' AS color
      FROM job_views jv
      INNER JOIN job_posts jp ON jp.id = jv.job_post_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      WHERE jp.associate_id IS NOT NULL
        AND jv.viewed_at >= ? AND jv.viewed_at <= ?
        ${associateFilter}
      UNION ALL
      SELECT 'Started Application', COUNT(*), '#3b82f6'
      FROM user_activity_logs al
      INNER JOIN job_posts jp ON jp.id = al.job_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      WHERE jp.associate_id IS NOT NULL
        AND al.action_type = 'apply_button_click'
        AND al.created_at >= ? AND al.created_at <= ?
        ${associateFilter}
      UNION ALL
      SELECT 'Completed Application', COUNT(*), '#8b5cf6'
      FROM job_applications ja
      INNER JOIN job_posts jp ON jp.id = ja.job_post_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      WHERE jp.associate_id IS NOT NULL
        AND ja.applied_at >= ? AND ja.applied_at <= ?
        ${associateFilter}
      UNION ALL
      SELECT 'Shortlisted', COUNT(*), '#f59e0b'
      FROM job_applications ja
      INNER JOIN job_posts jp ON jp.id = ja.job_post_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      WHERE jp.associate_id IS NOT NULL
        AND ja.status = 'Shortlisted'
        AND ja.applied_at >= ? AND ja.applied_at <= ?
        ${associateFilter}
      UNION ALL
      SELECT 'Hired', COUNT(*), '#10b981'
      FROM job_applications ja
      INNER JOIN job_posts jp ON jp.id = ja.job_post_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      WHERE jp.associate_id IS NOT NULL
        AND ja.status = 'Hired'
        AND ja.applied_at >= ? AND ja.applied_at <= ?
        ${associateFilter}
      `,
      [
        dFrom,
        dTo,
        ...associateParams,
        dFrom,
        dTo,
        ...associateParams,
        dFrom,
        dTo,
        ...associateParams,
        dFrom,
        dTo,
        ...associateParams,
        dFrom,
        dTo,
        ...associateParams,
      ],
    );

    const avgSessionRes = await this.dataSource.query(
      `
      SELECT AVG(TIMESTAMPDIFF(SECOND, min_t, max_t)) AS avg_s
      FROM (
        SELECT ual.user_id, DATE(ual.created_at) AS dt, MIN(ual.created_at) AS min_t, MAX(ual.created_at) AS max_t
        FROM user_activity_logs ual
        INNER JOIN users u ON u.id = ual.user_id
        WHERE ual.created_at >= ? AND ual.created_at <= ?
          AND ual.user_type = 'associate'
          ${associateFilter}
        GROUP BY ual.user_id, DATE(ual.created_at)
        HAVING TIMESTAMPDIFF(SECOND, min_t, max_t) > 0
      ) sessions
      `,
      [dFrom, dTo, ...associateParams],
    );
    const avgS = avgSessionRes[0]?.avg_s
      ? parseInt(avgSessionRes[0].avg_s, 10)
      : 0;
    const avgSession =
      avgS > 0 ? `${Math.floor(avgS / 60)}m ${avgS % 60}s` : '0m 0s';

    return {
      success: true,
      data: {
        kpis: {
          activeAssociates: parseInt(kpis.activeAssociates || '0', 10),
          cvViewsToday: parseInt(kpis.cvViewsToday || '0', 10),
          jobsToday: parseInt(kpis.jobsToday || '0', 10),
          appsToday: parseInt(kpis.appsToday || '0', 10),
          avgSession,
        },
        topAssociates: topAssociates.map((r: any) => ({
          ...r,
          cvViews: parseInt(r.cvViews || '0', 10),
          jobPosts: parseInt(r.jobPosts || '0', 10),
          applications: parseInt(r.applications || '0', 10),
        })),
        trend: trend.map((r: any) => ({
          ...r,
          cvViews: parseInt(r.cvViews || '0', 10),
          jobPosts: parseInt(r.jobPosts || '0', 10),
          applications: parseInt(r.applications || '0', 10),
        })),
        funnel: funnel.map((f: any) => ({
          ...f,
          count: parseInt(f.count || '0', 10),
        })),
      },
    };
  }

  // async getAssociateCvAccess(from?: string, to?: string, associate?: string) {
  //   const dFrom = from
  //     ? new Date(from)
  //     : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
  //   const dTo = to
  //     ? new Date(new Date(to).setHours(23, 59, 59, 999))
  //     : new Date();
  //   const associateFilter =
  //     associate && associate !== 'All' ? ' AND u.full_name = ?' : '';
  //   const params = associate && associate !== 'All' ? [associate] : [];

  //   const logs = await this.dataSource.query(
  //     `
  //     SELECT
  //       a.id AS associateId,
  //       u.full_name AS associate,
  //       COALESCE(
  //         ual.job_id,
  //         ja.job_post_id,
  //         CAST(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobId')), 'null') AS UNSIGNED),
  //         0
  //       ) AS jobId,
  //       COALESCE(jp.title, jp_ja.title, jp_json.title, 'General Candidate Search') AS title,
  //       COALESCE(
  //         u_seeker.full_name,
  //         u_seeker_direct.full_name,
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_name')),
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.applicantName'))
  //       ) AS candidateName,
  //       COALESCE(
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.seekerProfileId')),
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_id')),
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidateUserId')),
  //         'N/A'
  //       ) AS candidateId,
  //       DATE_FORMAT(CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30'), '%Y-%m-%d %H:%i:%s') AS activityAt,
  //       ual.action_type AS actionType
  //     FROM user_activity_logs ual
  //     INNER JOIN users u ON u.id = ual.user_id AND u.role = 'associate'
  //     INNER JOIN associates a ON a.user_id = u.id
  //     LEFT JOIN job_posts jp ON jp.id = ual.job_id
  //     LEFT JOIN job_applications ja ON ja.id = ual.application_id
  //     LEFT JOIN job_posts jp_ja ON jp_ja.id = ja.job_post_id
  //     LEFT JOIN job_posts jp_json ON jp_json.id = CAST(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobId')), 'null') AS UNSIGNED)
  //     LEFT JOIN seeker_profiles sp ON sp.id = CAST(NULLIF(JSON_UNQUOTE(COALESCE(JSON_EXTRACT(ual.action_details, '$.seekerProfileId'), JSON_EXTRACT(ual.action_details, '$.candidate_id'))), 'null') AS UNSIGNED)
  //     LEFT JOIN users u_seeker ON u_seeker.id = sp.user_id
  //     LEFT JOIN users u_seeker_direct ON u_seeker_direct.id = CAST(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidateUserId')), 'null') AS UNSIGNED)
  //     WHERE ual.user_type = 'associate'
  //       AND ual.created_at >= ? AND ual.created_at <= ?
  //       AND ual.action_type IN ('candidate_view', 'profile_view', 'cv_download', 'excel_download')
  //       ${associateFilter}
  //     ORDER BY u.full_name ASC, activityAt DESC
  //     `,
  //     [dFrom, dTo, ...params],
  //   );

  //   const grouped = new Map<string, any>();
  //   logs.forEach((row: any) => {
  //     const key = String(row.associateId);
  //     if (!grouped.has(key)) {
  //       grouped.set(key, {
  //         id: row.associateId,
  //         associate: row.associate,
  //         cvViews: 0,
  //         downloads: 0,
  //         jobs: [],
  //       });
  //     }
  //     const owner = grouped.get(key);
  //     owner.cvViews += 1;
  //     if (
  //       row.actionType === 'cv_download' ||
  //       row.actionType === 'excel_download'
  //     )
  //       owner.downloads += 1;

  //     const jobKey = String(row.jobId);
  //     let job = owner.jobs.find((j: any) => String(j.id) === jobKey);
  //     if (!job) {
  //       job = {
  //         id: row.jobId,
  //         title: row.title,
  //         views: 0,
  //         downloads: 0,
  //         applicants: 0,
  //         logs: [],
  //       };
  //       owner.jobs.push(job);
  //     }
  //     job.views += 1;
  //     if (
  //       row.actionType === 'cv_download' ||
  //       row.actionType === 'excel_download'
  //     )
  //       job.downloads += 1;
  //     job.logs.push({
  //       candidate:
  //         row.candidateName || `Candidate #${row.candidateId || 'N/A'}`,
  //       viewedAt: row.activityAt,
  //       downloaded:
  //         row.actionType === 'cv_download' ||
  //         row.actionType === 'excel_download',
  //       downloadAt:
  //         row.actionType === 'cv_download' ||
  //         row.actionType === 'excel_download'
  //           ? row.activityAt
  //           : 'N/A',
  //     });
  //   });

  //   const result = Array.from(grouped.values()).sort((a, b) => b.cvViews - a.cvViews);
  //   result.forEach((owner: any) => {
  //     owner.jobs.sort((a: any, b: any) => b.views - a.views);
  //     owner.jobs.forEach((job: any) => {
  //       job.applicants = new Set(job.logs.map((l: any) => l.candidate)).size;
  //     });
  //   });

  //   return { success: true, data: result };
  // }
  // async getAssociateCvAccess(from?: string, to?: string, associate?: string) {
  //   const dFrom = from
  //     ? new Date(from)
  //     : new Date(new Date().setFullYear(new Date().getFullYear() - 1));

  //   const dTo = to
  //     ? new Date(new Date(to).setHours(23, 59, 59, 999))
  //     : new Date();

  //   const associateFilter =
  //     associate && associate !== 'All' ? ' AND base.associate = ?' : '';

  //   const params =
  //     associate && associate !== 'All'
  //       ? [dFrom, dTo, associate]
  //       : [dFrom, dTo];

  //   const logs = await this.dataSource.query(
  //     `
  //   SELECT
  //     base.associateId,
  //     base.associate,
  //     base.jobId,
  //     COALESCE(jp.title,'General Candidate Search') AS title,
  //     base.candidateName,
  //     base.candidateId,
  //     base.activityAt,
  //     base.actionType
  //   FROM (

  //     SELECT
  //       a.id AS associateId,
  //       u.full_name AS associate,

  //       COALESCE(
  //         ual.job_id,
  //         ja.job_post_id,
  //         CAST(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.jobId')) AS UNSIGNED)
  //       ) AS jobId,

  //       COALESCE(
  //         u_seeker.full_name,
  //         u_seeker_direct.full_name,
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.candidate_name')),
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.applicantName')),
  //         CONCAT(
  //           'Candidate #',
  //           COALESCE(
  //             JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.candidateUserId')),
  //             JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.candidate_id')),
  //             'N/A'
  //           )
  //         )
  //       ) AS candidateName,

  //       COALESCE(
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.seekerProfileId')),
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.candidate_id')),
  //         JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.candidateUserId')),
  //         'N/A'
  //       ) AS candidateId,

  //       DATE_FORMAT(
  //         CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30'),
  //         '%Y-%m-%d %H:%i:%s'
  //       ) AS activityAt,

  //       ual.action_type AS actionType

  //     FROM user_activity_logs ual

  //     INNER JOIN users u
  //       ON u.id = ual.user_id
  //       AND u.role = 'associate'

  //     INNER JOIN associates a
  //       ON a.user_id = u.id

  //     LEFT JOIN job_applications ja
  //       ON ja.id = ual.application_id

  //     LEFT JOIN seeker_profiles sp
  //       ON sp.id = CAST(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.seekerProfileId')) AS UNSIGNED)

  //     LEFT JOIN users u_seeker
  //       ON u_seeker.id = sp.user_id

  //     LEFT JOIN users u_seeker_direct
  //       ON u_seeker_direct.id = CAST(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details,'$.candidateUserId')) AS UNSIGNED)

  //     WHERE
  //       ual.user_type = 'associate'
  //       AND ual.created_at BETWEEN ? AND ?
  //       AND ual.action_type IN (
  //         'candidate_view',
  //         'profile_view',
  //         'cv_download',
  //         'excel_download'
  //       )

  //   ) base

  //   LEFT JOIN job_posts jp
  //   ON jp.id = base.jobId

  //   WHERE 1=1
  //   ${associateFilter}

  //   ORDER BY base.associate ASC, base.activityAt DESC
  //   `,
  //     params
  //   );

  //   const grouped = new Map<string, any>();

  //   logs.forEach((row: any) => {
  //     const key = String(row.associateId);

  //     if (!grouped.has(key)) {
  //       grouped.set(key, {
  //         id: row.associateId,
  //         associate: row.associate,
  //         cvViews: 0,
  //         downloads: 0,
  //         jobs: [],
  //       });
  //     }

  //     const owner = grouped.get(key);

  //     owner.cvViews += 1;

  //     const isDownload =
  //       row.actionType === 'cv_download' ||
  //       row.actionType === 'excel_download';

  //     if (isDownload) owner.downloads += 1;

  //     const jobKey = String(row.jobId || 0);

  //     let job = owner.jobs.find((j: any) => String(j.id) === jobKey);

  //     if (!job) {
  //       job = {
  //         id: row.jobId || 0,
  //         title: row.title || "General Candidate Search",
  //         views: 0,
  //         downloads: 0,
  //         applicants: 0,
  //         logs: [],
  //       };

  //       owner.jobs.push(job);
  //     }

  //     job.views += 1;

  //     if (isDownload) job.downloads += 1;

  //     job.logs.push({
  //       candidate: row.candidateName,
  //       viewedAt: row.activityAt,
  //       downloaded: isDownload,
  //       downloadAt: isDownload ? row.activityAt : 'N/A',
  //     });
  //   });

  //   const result = Array.from(grouped.values()).sort(
  //     (a, b) => b.cvViews - a.cvViews
  //   );

  //   result.forEach((owner: any) => {
  //     owner.jobs.sort((a: any, b: any) => b.views - a.views);

  //     owner.jobs.forEach((job: any) => {
  //       job.applicants = new Set(job.logs.map((l: any) => l.candidate)).size;
  //     });
  //   });

  //   return { success: true, data: result };
  // }
  // async getAssociateCvAccess(from?: string, to?: string, associate?: string) {

  //   // ─── 1. DATE RANGE ───────────────────────────────────────────────────────
  //   const dFrom = from
  //     ? new Date(from)
  //     : new Date(new Date().setFullYear(new Date().getFullYear() - 1));

  //   const dTo = to
  //     ? new Date(new Date(to).setHours(23, 59, 59, 999))
  //     : new Date();

  //   // ─── 2. PARAMS ───────────────────────────────────────────────────────────
  //   const associateParam = associate && associate !== 'All' ? associate : null;
  //   const params = [dFrom, dTo, associateParam, associateParam];

  //   // ─── 3. QUERY ─────────────────────────────────────────────────────────────
  //   const logs = await this.dataSource.query(
  //     `
  //   SELECT
  //     base.associateId,
  //     base.associate,
  //     base.jobId,
  //     COALESCE(jp.title, 'General Candidate Search') AS title,
  //     base.candidateName,
  //     base.candidateUserId,
  //     base.seekerId,
  //     base.activityAt,
  //     base.actionType

  //   FROM (

  //     SELECT
  //       a.id        AS associateId,
  //       u.full_name AS associate,

  //       /*
  //        * CANDIDATE USER ID (users.id)
  //        * Primary identity key — most stable reference.
  //        * 1. sp.user_id            via seekerProfileId → seeker_profiles → users
  //        * 2. u_seeker_direct.id    via candidateUserId → users directly
  //        * 3. JSON $.candidateUserId
  //        * 4. JSON $.candidate_id
  //        */
  //       COALESCE(
  //         sp.user_id,
  //         u_seeker_direct.id,
  //         CAST(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidateUserId')), 'null') AS UNSIGNED),
  //         CAST(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_id')),    'null') AS UNSIGNED)
  //       ) AS candidateUserId,

  //       /*
  //        * SEEKER ID (seeker_profiles.id)
  //        * Used for job_applications lookup chain.
  //        * Resolved entirely via JOINs — no correlated subqueries.
  //        * 1. sp.id                 via seekerProfileId join (direct)
  //        * 2. sp_via_direct.id      via candidateUserId → users → seeker_profiles
  //        * 3. sp_via_json.id        via candidate_id   → users → seeker_profiles
  //        */
  //       COALESCE(
  //         sp.id,
  //         sp_via_direct.id,
  //         sp_via_json.id
  //       ) AS seekerId,

  //       /*
  //        * JOB ID — 6-level fallback, all via JOINs (no correlated subqueries):
  //        * 1. ual.job_id            direct column on the log
  //        * 2. ja.job_post_id        via application_id join
  //        * 3. JSON $.jobId          regex guarded
  //        * 4. JSON $.job_id         regex guarded
  //        * 5. JSON $.jobPostId      regex guarded
  //        * 6. ja_reverse.job_post_id
  //        *    seekerId → job_applications.seeker_id
  //        *             → job_applications.job_post_id
  //        *             → job_posts.associate_id = a.id ✓
  //        *    pre-aggregated with ROW_NUMBER() — no fan-out, no per-row subquery
  //        */
  //       COALESCE(

  //         ual.job_id,

  //         ja.job_post_id,

  //         CASE
  //           WHEN JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobId'))
  //                  REGEXP '^[0-9]+$'
  //           THEN CAST(
  //                  JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobId'))
  //                  AS UNSIGNED
  //                )
  //           ELSE NULL
  //         END,

  //         CASE
  //           WHEN JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.job_id'))
  //                  REGEXP '^[0-9]+$'
  //           THEN CAST(
  //                  JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.job_id'))
  //                  AS UNSIGNED
  //                )
  //           ELSE NULL
  //         END,

  //         CASE
  //           WHEN JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobPostId'))
  //                  REGEXP '^[0-9]+$'
  //           THEN CAST(
  //                  JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobPostId'))
  //                  AS UNSIGNED
  //                )
  //           ELSE NULL
  //         END,

  //         ja_reverse.job_post_id

  //       ) AS jobId,

  //       /*
  //        * CANDIDATE NAME — 4-level fallback
  //        * 1. u_seeker.full_name       via seekerProfileId → seeker_profiles → users
  //        * 2. u_seeker_direct.full_name via candidateUserId → users
  //        * 3. JSON $.candidate_name
  //        * 4. JSON $.applicantName
  //        * 5. Labeled placeholder with best available ID
  //        */
  //       COALESCE(
  //         u_seeker.full_name,
  //         u_seeker_direct.full_name,
  //         NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_name')), 'null'),
  //         NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.applicantName')),  'null'),
  //         CONCAT(
  //           'Candidate #',
  //           COALESCE(
  //             NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidateUserId')), 'null'),
  //             NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_id')),    'null'),
  //             NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.seekerProfileId')), 'null'),
  //             'Unknown'
  //           )
  //         )
  //       ) AS candidateName,

  //       DATE_FORMAT(
  //         CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30'),
  //         '%Y-%m-%d %H:%i:%s'
  //       ) AS activityAt,

  //       ual.action_type AS actionType

  //     FROM user_activity_logs ual

  //     /*
  //      * ── CORE JOINS ──────────────────────────────────────────────────────
  //      */

  //     INNER JOIN users u
  //       ON  u.id   = ual.user_id
  //       AND u.role = 'associate'

  //     INNER JOIN associates a
  //       ON a.user_id = u.id

  //     -- direct application_id link on the log
  //     LEFT JOIN job_applications ja
  //       ON ja.id = ual.application_id

  //     /*
  //      * ── CANDIDATE RESOLUTION JOINS ──────────────────────────────────────
  //      * Path A: seekerProfileId → seeker_profiles → users
  //      */
  //     LEFT JOIN seeker_profiles sp
  //       ON sp.id = CAST(
  //            NULLIF(
  //              JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.seekerProfileId')),
  //              'null'
  //            ) AS UNSIGNED
  //          )

  //     LEFT JOIN users u_seeker
  //       ON u_seeker.id = sp.user_id

  //     -- Path B: candidateUserId → users → seeker_profiles
  //     LEFT JOIN users u_seeker_direct
  //       ON u_seeker_direct.id = CAST(
  //            NULLIF(
  //              JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidateUserId')),
  //              'null'
  //            ) AS UNSIGNED
  //          )

  //     LEFT JOIN seeker_profiles sp_via_direct
  //       ON sp_via_direct.user_id = u_seeker_direct.id

  //     -- Path C: candidate_id → users → seeker_profiles
  //     LEFT JOIN users u_seeker_json
  //       ON u_seeker_json.id = CAST(
  //            NULLIF(
  //              JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_id')),
  //              'null'
  //            ) AS UNSIGNED
  //          )

  //     LEFT JOIN seeker_profiles sp_via_json
  //       ON sp_via_json.user_id = u_seeker_json.id

  //     /*
  //      * ── REVERSE JOB LOOKUP JOIN ─────────────────────────────────────────
  //      * Pre-aggregated with ROW_NUMBER() BEFORE the main join.
  //      * Runs ONCE for the entire dataset — not once per row.
  //      * Picks the most recent application per (seeker, associate) pair.
  //      * Prevents fan-out when a candidate applied to multiple jobs
  //      * under the same associate.
  //      */
  //     LEFT JOIN (
  //       SELECT
  //         ja2.seeker_id,
  //         ja2.job_post_id,
  //         jp2.associate_id,
  //         ROW_NUMBER() OVER (
  //           PARTITION BY ja2.seeker_id, jp2.associate_id
  //           ORDER BY ja2.applied_at DESC
  //         ) AS rn
  //       FROM   job_applications ja2
  //       INNER  JOIN job_posts jp2
  //              ON jp2.id = ja2.job_post_id
  //     ) ja_reverse
  //       ON  ja_reverse.seeker_id    = COALESCE(sp.id, sp_via_direct.id, sp_via_json.id)
  //       AND ja_reverse.associate_id = a.id
  //       AND ja_reverse.rn           = 1

  //     WHERE
  //       ual.user_type = 'associate'
  //       AND ual.created_at BETWEEN ? AND ?
  //       AND ual.action_type IN (
  //         'candidate_view',
  //         'profile_view',
  //         'cv_download',
  //         'excel_download'
  //       )

  //   ) base

  //   -- title lookup using fully resolved jobId
  //   LEFT JOIN job_posts jp
  //     ON jp.id = base.jobId

  //   -- always parameterized — never string injected
  //   WHERE (? IS NULL OR base.associate = ?)

  //   ORDER BY base.associate ASC, base.activityAt DESC
  //   `,
  //     params,
  //   );

  //   // ─── 4. GROUP ─────────────────────────────────────────────────────────────
  //   const grouped = new Map<string, any>();

  //   for (const row of logs) {
  //     const ownerKey = String(row.associateId);

  //     if (!grouped.has(ownerKey)) {
  //       grouped.set(ownerKey, {
  //         id: row.associateId,
  //         associate: row.associate,
  //         cvViews: 0,
  //         downloads: 0,
  //         jobs: [],
  //       });
  //     }

  //     const owner = grouped.get(ownerKey);
  //     const isDownload = row.actionType === 'cv_download' ||
  //       row.actionType === 'excel_download';

  //     // views and downloads are mutually exclusive counters
  //     if (isDownload) {
  //       owner.downloads += 1;
  //     } else {
  //       owner.cvViews += 1;
  //     }

  //     // NULL jobId gets its own stable bucket — never collapses into id: 0
  //     const jobKey = row.jobId != null ? String(row.jobId) : '__general__';
  //     let job = owner.jobs.find((j: any) => j._key === jobKey);

  //     if (!job) {
  //       job = {
  //         _key: jobKey,
  //         id: row.jobId ?? null,
  //         title: row.title,        // always populated by SQL COALESCE
  //         views: 0,
  //         downloads: 0,
  //         applicants: 0,
  //         logs: [],
  //       };
  //       owner.jobs.push(job);
  //     }

  //     if (isDownload) {
  //       job.downloads += 1;
  //     } else {
  //       job.views += 1;
  //     }

  //     job.logs.push({
  //       candidateUserId: row.candidateUserId,   // users.id       — primary identity
  //       seekerId: row.seekerId,           // seeker_profiles.id — for reference
  //       candidate: row.candidateName,      // display name
  //       viewedAt: row.activityAt,
  //       downloaded: isDownload,
  //       downloadAt: isDownload ? row.activityAt : null,
  //     });
  //   }

  //   // ─── 5. SORT + FINALIZE ───────────────────────────────────────────────────
  //   const result = Array.from(grouped.values())
  //     .sort((a, b) => b.cvViews - a.cvViews);

  //   for (const owner of result) {
  //     owner.jobs.sort((a: any, b: any) => b.views - a.views);

  //     for (const job of owner.jobs) {
  //       // deduplicate by candidateUserId (users.id) — most stable key
  //       // nulls excluded so unresolved candidates don't merge into one
  //       job.applicants = new Set(
  //         job.logs
  //           .map((l: any) => l.candidateUserId)
  //           .filter((id: any) => id != null),
  //       ).size;

  //       // strip internal routing key before returning
  //       delete job._key;
  //     }
  //   }

  //   return { success: true, data: result };
  // }
  async getAssociateCvAccess(from?: string, to?: string, associate?: string) {
    // ─── 1. DATE RANGE ───────────────────────────────────────────────────────
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));

    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    // ─── 2. PARAMS ───────────────────────────────────────────────────────────
    const associateParam = associate && associate !== 'All' ? associate : null;
    const params = [dFrom, dTo, associateParam, associateParam];

    // ─── 3. QUERY ─────────────────────────────────────────────────────────────
    const logs = await this.dataSource.query(
      `
    SELECT
      base.logId,
      base.userType,
      base.associateId,
      base.associate,
      base.jobId,
      COALESCE(jp.title, 'General Candidate Search') AS title,
      base.candidateName,
      base.candidateUserId,
      base.seekerId,
      base.activityAt,
      base.actionType

    FROM (

      SELECT
        ual.id          AS logId,
        ual.user_type   AS userType,
        a.id            AS associateId,
        u.full_name     AS associate,

        /*
         * CANDIDATE USER ID (users.id)
         * 1. sp.user_id            via seekerProfileId → seeker_profiles → users
         * 2. u_seeker_direct.id    via candidateUserId → users directly
         * 3. JSON $.candidateUserId
         * 4. JSON $.candidate_id
         */
        COALESCE(
          sp.user_id,
          u_seeker_direct.id,
          CAST(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidateUserId')), 'null') AS UNSIGNED),
          CAST(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_id')),    'null') AS UNSIGNED)
        ) AS candidateUserId,

        /*
         * SEEKER ID (seeker_profiles.id)
         * 1. sp.id                 via seekerProfileId join (direct)
         * 2. sp_via_direct.id      via candidateUserId → users → seeker_profiles
         * 3. sp_via_json.id        via candidate_id   → users → seeker_profiles
         */
        COALESCE(
          sp.id,
          sp_via_direct.id,
          sp_via_json.id
        ) AS seekerId,

        /*
         * JOB ID — 6-level fallback:
         * 1. ual.job_id
         * 2. ja.job_post_id
         * 3. JSON $.jobId          regex guarded
         * 4. JSON $.job_id         regex guarded
         * 5. JSON $.jobPostId      regex guarded
         * 6. ja_reverse            only when ual.application_id IS NOT NULL
         *                          prevents phantom job assignments
         */
        COALESCE(

          ual.job_id,

          ja.job_post_id,

          CASE
            WHEN JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobId'))
                   REGEXP '^[0-9]+$'
            THEN CAST(
                   JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobId'))
                   AS UNSIGNED
                 )
            ELSE NULL
          END,

          CASE
            WHEN JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.job_id'))
                   REGEXP '^[0-9]+$'
            THEN CAST(
                   JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.job_id'))
                   AS UNSIGNED
                 )
            ELSE NULL
          END,

          CASE
            WHEN JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobPostId'))
                   REGEXP '^[0-9]+$'
            THEN CAST(
                   JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.jobPostId'))
                   AS UNSIGNED
                 )
            ELSE NULL
          END,

          CASE
            WHEN ual.application_id IS NOT NULL
            THEN ja_reverse.job_post_id
            ELSE NULL
          END

        ) AS jobId,

        /*
         * CANDIDATE NAME — 5-level fallback
         * 1. u_seeker.full_name
         * 2. u_seeker_direct.full_name
         * 3. JSON $.candidate_name
         * 4. JSON $.applicantName
         * 5. Labeled placeholder
         */
        COALESCE(
          u_seeker.full_name,
          u_seeker_direct.full_name,
          NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_name')), 'null'),
          NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.applicantName')),  'null'),
          CONCAT(
            'Candidate #',
            COALESCE(
              NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidateUserId')), 'null'),
              NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_id')),    'null'),
              NULLIF(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.seekerProfileId')), 'null'),
              'Unknown'
            )
          )
        ) AS candidateName,

        DATE_FORMAT(
          CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30'),
          '%Y-%m-%d %H:%i:%s'
        ) AS activityAt,

        ual.action_type AS actionType

      FROM user_activity_logs ual

      INNER JOIN users u
        ON  u.id   = ual.user_id
        AND u.role = 'associate'

      INNER JOIN associates a
        ON a.user_id = u.id

      -- direct application_id link on the log
      LEFT JOIN job_applications ja
        ON ja.id = ual.application_id

      -- Path A: seekerProfileId → seeker_profiles → users
      LEFT JOIN seeker_profiles sp
        ON sp.id = CAST(
             NULLIF(
               JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.seekerProfileId')),
               'null'
             ) AS UNSIGNED
           )

      LEFT JOIN users u_seeker
        ON u_seeker.id = sp.user_id

      -- Path B: candidateUserId → users → seeker_profiles
      LEFT JOIN users u_seeker_direct
        ON u_seeker_direct.id = CAST(
             NULLIF(
               JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidateUserId')),
               'null'
             ) AS UNSIGNED
           )

      LEFT JOIN seeker_profiles sp_via_direct
        ON sp_via_direct.user_id = u_seeker_direct.id

      -- Path C: candidate_id → users → seeker_profiles
      LEFT JOIN users u_seeker_json
        ON u_seeker_json.id = CAST(
             NULLIF(
               JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.candidate_id')),
               'null'
             ) AS UNSIGNED
           )

      LEFT JOIN seeker_profiles sp_via_json
        ON sp_via_json.user_id = u_seeker_json.id

      /*
       * Reverse job lookup — ONLY fires when ual.application_id IS NOT NULL
       * ja2.id = ual.application_id → safe direct match, no fan-out
       */
      LEFT JOIN (
        SELECT
          ja2.id          AS application_id,
          ja2.job_post_id,
          jp2.associate_id
        FROM   job_applications ja2
        INNER  JOIN job_posts jp2
               ON jp2.id = ja2.job_post_id
      ) ja_reverse
        ON  ja_reverse.application_id = ual.application_id
        AND ja_reverse.associate_id   = a.id

      WHERE
        ual.user_type = 'associate'
        AND ual.created_at BETWEEN ? AND ?
        AND ual.action_type IN (
          'candidate_view',
          'profile_view',
          'cv_download',
          'excel_download'
        )

    ) base

    LEFT JOIN job_posts jp
      ON jp.id = base.jobId

    WHERE (? IS NULL OR base.associate = ?)

    ORDER BY base.associate ASC, base.activityAt DESC
    `,
      params,
    );

    // ─── DEBUG — remove after investigation ──────────────────────────────────
    console.log('─── CV ACCESS RAW LOGS ─────────────────────────────');
    console.log('─── TOTAL ROWS:', logs.length);
    logs.forEach((row: any, i: number) => {
      console.log(`[${i + 1}]`, {
        logId: row.logId,
        userType: row.userType,
        associateId: row.associateId,
        associate: row.associate,
        actionType: row.actionType,
        jobId: row.jobId,
        title: row.title,
        candidateUserId: row.candidateUserId,
        seekerId: row.seekerId,
        candidateName: row.candidateName,
        activityAt: row.activityAt,
      });
    });
    console.log('─────────────────────────────────────────────────────');
    // ─── END DEBUG ────────────────────────────────────────────────────────────

    // ─── 4. GROUP ─────────────────────────────────────────────────────────────
    const grouped = new Map<string, any>();

    for (const row of logs) {
      const ownerKey = String(row.associateId);

      if (!grouped.has(ownerKey)) {
        grouped.set(ownerKey, {
          id: row.associateId,
          associate: row.associate,
          cvViews: 0,
          downloads: 0,
          jobs: [],
        });
      }

      const owner = grouped.get(ownerKey);
      const isDownload =
        row.actionType === 'cv_download' || row.actionType === 'excel_download';

      if (isDownload) {
        owner.downloads += 1;
      } else {
        owner.cvViews += 1;
      }

      const jobKey = row.jobId != null ? String(row.jobId) : '__general__';
      let job = owner.jobs.find((j: any) => j._key === jobKey);

      if (!job) {
        job = {
          _key: jobKey,
          id: row.jobId ?? null,
          title: row.title,
          views: 0,
          downloads: 0,
          applicants: 0,
          logs: [],
        };
        owner.jobs.push(job);
      }

      if (isDownload) {
        job.downloads += 1;
      } else {
        job.views += 1;
      }

      job.logs.push({
        candidateUserId: row.candidateUserId,
        seekerId: row.seekerId,
        candidate: row.candidateName,
        viewedAt: !isDownload ? row.activityAt : null,
        downloadedAt: isDownload ? row.activityAt : null,
      });
    }

    // ─── 5. SORT + FINALIZE ───────────────────────────────────────────────────
    const result = Array.from(grouped.values()).sort(
      (a, b) => b.cvViews - a.cvViews,
    );

    for (const owner of result) {
      owner.jobs.sort((a: any, b: any) => b.views - a.views);

      for (const job of owner.jobs) {
        // merge multiple entries for same candidate into one row
        const mergedLogs = new Map<string, any>();

        for (const log of job.logs) {
          const key =
            log.candidateUserId != null
              ? String(log.candidateUserId)
              : log.candidate;

          if (!mergedLogs.has(key)) {
            mergedLogs.set(key, {
              candidateUserId: log.candidateUserId,
              seekerId: log.seekerId,
              candidate: log.candidate,
              viewedAt: log.viewedAt ?? null,
              downloadedAt: log.downloadedAt ?? null,
            });
          } else {
            const existing = mergedLogs.get(key);
            if (!existing.viewedAt && log.viewedAt)
              existing.viewedAt = log.viewedAt;
            if (!existing.downloadedAt && log.downloadedAt)
              existing.downloadedAt = log.downloadedAt;
          }
        }

        job.logs = Array.from(mergedLogs.values()).sort((a, b) =>
          (b.viewedAt ?? b.downloadedAt ?? '').localeCompare(
            a.viewedAt ?? a.downloadedAt ?? '',
          ),
        );

        // distinct resolved candidateUserIds only
        job.applicants = new Set(
          job.logs
            .map((l: any) => l.candidateUserId)
            .filter((id: any) => id != null),
        ).size;

        delete job._key;
      }
    }

    return { success: true, data: result };
  }
  async getAssociateJobsApplications(
    from?: string,
    to?: string,
    associate?: string,
  ) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();
    const associateFilter =
      associate && associate !== 'All' ? ' AND u.full_name = ?' : '';
    const params = associate && associate !== 'All' ? [associate] : [];

    const jobs = await this.dataSource.query(
      `
      SELECT
        a.id AS associateId,
        u.full_name AS associate,
        jp.id AS jobId,
        jp.title,
        COUNT(ja.id) AS applications,
        SUM(CASE WHEN ja.status = 'Shortlisted' THEN 1 ELSE 0 END) AS shortlisted
      FROM job_posts jp
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN job_applications ja
        ON ja.job_post_id = jp.id
        AND ja.applied_at >= ? AND ja.applied_at <= ?
      WHERE jp.associate_id IS NOT NULL
        AND jp.created_at >= ? AND jp.created_at <= ?
        ${associateFilter}
      GROUP BY a.id, u.full_name, jp.id, jp.title
      ORDER BY u.full_name ASC, jp.created_at DESC
      `,
      [dFrom, dTo, dFrom, dTo, ...params],
    );

    const applicants = await this.dataSource.query(
      `
      SELECT
        jp.id AS jobId,
        u.full_name AS name,
        DATE_FORMAT(CONVERT_TZ(ja.applied_at, @@session.time_zone, '+05:30'), '%Y-%m-%d') AS appliedDate,
        ja.status
      FROM job_applications ja
      INNER JOIN job_posts jp ON jp.id = ja.job_post_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users au ON au.id = a.user_id
      INNER JOIN seeker_profiles sp ON sp.id = ja.seeker_id
      INNER JOIN users u ON u.id = sp.user_id
      WHERE jp.associate_id IS NOT NULL
        AND ja.applied_at >= ? AND ja.applied_at <= ?
        ${associate && associate !== 'All' ? ' AND au.full_name = ?' : ''}
      ORDER BY ja.applied_at DESC
      `,
      [dFrom, dTo, ...params],
    );

    const byAssociate = new Map<string, any>();
    jobs.forEach((row: any) => {
      const key = String(row.associateId);
      if (!byAssociate.has(key)) {
        byAssociate.set(key, {
          id: row.associateId,
          associate: row.associate,
          jobsPosted: 0,
          totalApps: 0,
          jobs: [],
        });
      }
      const owner = byAssociate.get(key);
      owner.jobsPosted += 1;
      owner.totalApps += parseInt(row.applications || '0', 10);
      owner.jobs.push({
        id: row.jobId,
        title: row.title,
        applications: parseInt(row.applications || '0', 10),
        shortlisted: parseInt(row.shortlisted || '0', 10),
        applicants: applicants.filter(
          (a: any) => String(a.jobId) === String(row.jobId),
        ),
      });
    });

    const result = Array.from(byAssociate.values()).sort(
      (a, b) => b.totalApps - a.totalApps,
    );
    result.forEach((owner: any) => {
      owner.jobs.sort((a: any, b: any) => b.applications - a.applications);
    });

    return { success: true, data: result };
  }

  async getAssociateSessions(from?: string, to?: string, associate?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();
    const associateFilter =
      associate && associate !== 'All' ? ' AND u.full_name = ?' : '';
    const params = associate && associate !== 'All' ? [associate] : [];
    const loginCond =
      "(ual.action_type LIKE 'auth_%' OR ual.action_type = 'auth_signin' OR ual.action_type = 'auth_login' OR ual.action_type = 'login')";
    const formatDurationSmart = (seconds: number) => {
      if (!seconds || seconds <= 0) return 'N/A';
      const h = Math.floor(seconds / 3600);
      const m = Math.floor((seconds % 3600) / 60);
      return h > 0 ? `${h}h ${m}m` : `${m}m`;
    };

    const data = await this.dataSource.query(
      `
      SELECT
        a.id AS associateId,
        u.id AS userId,
        u.full_name AS associate,
        COALESCE(SUM(CASE WHEN ${loginCond} THEN 1 ELSE 0 END), 0) AS sessions,
        COALESCE(
          ROUND(
            TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at))
            / NULLIF(COUNT(DISTINCT DATE(CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30'))), 0)
          ),
          0
        ) AS avgDurationSeconds,
        DATE_FORMAT(CONVERT_TZ(MAX(CASE WHEN ${loginCond} THEN ual.created_at ELSE NULL END), @@session.time_zone, '+05:30'), '%Y-%m-%d %H:%i:%s') AS lastLogin,
        COUNT(DISTINCT DATE(CONVERT_TZ(ual.created_at, @@session.time_zone, '+05:30'))) AS activeDays
      FROM associates a
      INNER JOIN users u ON u.id = a.user_id AND u.role = 'associate'
      LEFT JOIN user_activity_logs ual
        ON ual.user_id = u.id
        AND ual.user_type = 'associate'
        AND ual.created_at >= ? AND ual.created_at <= ?
      WHERE 1=1 ${associateFilter}
      GROUP BY a.id, u.id, u.full_name
      HAVING sessions > 0 OR activeDays > 0
      ORDER BY lastLogin DESC
      `,
      [dFrom, dTo, ...params],
    );

    const result = await Promise.all(
      data.map(async (row: any) => {
        const rawLogs = await this.dataSource.query(
          `
          SELECT
            DATE_FORMAT(CONVERT_TZ(sub.created_at, @@session.time_zone, '+05:30'), '%Y-%m-%d') AS day,
            DATE_FORMAT(CONVERT_TZ(sub.created_at, @@session.time_zone, '+05:30'), '%Y-%m-%d %H:%i:%s') AS ts,
            sub.action_type,
            sub.ip_address,
            sub.device_type,
            sub.browser
          FROM user_activity_logs sub
          WHERE sub.user_id = ?
            AND sub.user_type = 'associate'
            AND sub.created_at >= ? AND sub.created_at <= ?
          ORDER BY sub.created_at DESC
          LIMIT 5000
          `,
          [row.userId, dFrom, dTo],
        );

        const byDay = new Map<string, any[]>();
        rawLogs.forEach((log: any) => {
          if (!byDay.has(log.day)) byDay.set(log.day, []);
          byDay.get(log.day)!.push(log);
        });

        const logs = Array.from(byDay.entries()).map(([day, items]) => {
          const ordered = [...items].reverse();
          const first = ordered[0];
          const last = ordered[ordered.length - 1];
          const durationSeconds = Math.max(
            0,
            Math.round(
              (new Date(last.ts).getTime() - new Date(first.ts).getTime()) /
                1000,
            ),
          );
          return {
            date: day,
            loginTime: first?.ts || 'N/A',
            logoutTime: last?.ts || 'N/A',
            duration: formatDurationSmart(durationSeconds),
            ip: last?.ip_address || first?.ip_address || 'N/A',
            device: last?.device_type || first?.device_type || 'N/A',
          };
        });

        return {
          associate: row.associate,
          sessions: parseInt(row.sessions || '0', 10),
          avgDuration: formatDurationSmart(
            parseInt(row.avgDurationSeconds || '0', 10),
          ),
          lastLogin: row.lastLogin || 'N/A',
          activeDays: parseInt(row.activeDays || '0', 10),
          logs,
        };
      }),
    );

    return { success: true, data: result };
  }

  async getAssociateEngagement(from?: string, to?: string, associate?: string) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();
    const associateFilter =
      associate && associate !== 'All' ? ' AND owner.full_name = ?' : '';
    const params = associate && associate !== 'All' ? [associate] : [];
    const viewCondition = `
      (
        ual.action_type = 'job_details_view'
        OR ual.action_type = 'job_view'
        OR (ual.action_type = 'page_view' AND ual.job_id IS NOT NULL)
      )
    `;
    const applyCondition = `
      (
        ual.action_type = 'job_apply_submit'
        OR ual.action_type = 'job_apply_complete'
      )
    `;
    const timeSpentExpr =
      "COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(ual.action_details, '$.timeSpentSeconds')) AS UNSIGNED), 0)";
    const formatDuration = (seconds: number) => {
      const safeSeconds = Math.max(0, Math.round(seconds || 0));
      const h = Math.floor(safeSeconds / 3600);
      const m = Math.floor((safeSeconds % 3600) / 60);
      const s = safeSeconds % 60;
      if (h > 0) return `${h}h ${m}m`;
      if (m > 0) return s > 0 ? `${m}m ${s}s` : `${m}m`;
      return `${s}s`;
    };
    const formatDurationOrNA = (seconds: number) => {
      const safeSeconds = Math.max(0, Math.round(seconds || 0));
      return safeSeconds > 0 ? formatDuration(safeSeconds) : 'N/A';
    };

    const data = await this.dataSource.query(
      `
      SELECT
        jp.id,
        jp.title,
        owner.full_name AS associate,
        c.name AS company,
        COALESCE(SUM(CASE WHEN ${viewCondition} THEN 1 ELSE 0 END), 0) AS views,
        COALESCE(COUNT(DISTINCT CASE WHEN ${viewCondition} THEN ual.user_id END), 0) AS uniqueViewers,
        COALESCE(COUNT(DISTINCT CASE WHEN ${applyCondition} THEN ual.user_id ELSE NULL END), 0) AS applications
      FROM job_posts jp
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users owner ON owner.id = a.user_id
      LEFT JOIN companies c ON c.id = jp.company_id
      LEFT JOIN user_activity_logs ual
        ON ual.job_id = jp.id
        AND ual.user_type = 'seeker'
        AND ual.created_at >= ? AND ual.created_at <= ?
      WHERE jp.associate_id IS NOT NULL
        ${associateFilter}
      GROUP BY jp.id, jp.title, owner.full_name, c.name
      HAVING views > 0 OR applications > 0
      ORDER BY views DESC, applications DESC
      LIMIT 100
      `,
      [dFrom, dTo, ...params],
    );

    const candidateRows = await this.dataSource.query(
      `
      SELECT
        ual.job_id AS jobId,
        u.full_name AS name,
        COALESCE(SUM(CASE WHEN ${viewCondition} THEN 1 ELSE 0 END), 0) AS viewCount,
        COALESCE(COUNT(DISTINCT CASE WHEN ${applyCondition} THEN ual.user_id ELSE NULL END), 0) AS applicationCount,
        COALESCE(SUM(CASE WHEN ${viewCondition} THEN ${timeSpentExpr} ELSE 0 END), 0) AS timeSpentSeconds,
        COALESCE(SUM(CASE WHEN ual.action_type = 'apply_button_click' THEN 1 ELSE 0 END), 0) AS applyClicks,
        TIMESTAMPDIFF(SECOND, MIN(ual.created_at), MAX(ual.created_at)) AS spanSeconds,
        COUNT(*) AS activityCount
      FROM user_activity_logs ual
      INNER JOIN users u ON u.id = ual.user_id AND u.role = 'seeker'
      INNER JOIN job_posts jp ON jp.id = ual.job_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users owner ON owner.id = a.user_id
      WHERE ual.user_type = 'seeker'
        AND ual.job_id IS NOT NULL
        AND ual.created_at >= ? AND ual.created_at <= ?
        AND jp.associate_id IS NOT NULL
        ${associateFilter}
      GROUP BY ual.job_id, u.id, u.full_name
      HAVING viewCount > 0 OR applicationCount > 0 OR timeSpentSeconds > 0 OR applyClicks > 0
      ORDER BY timeSpentSeconds DESC, applicationCount DESC, viewCount DESC
      `,
      [dFrom, dTo, ...params],
    );

    const candidatesByJob = new Map<number, any[]>();
    const jobTimeTotals = new Map<
      number,
      { totalSeconds: number; candidateCount: number }
    >();
    candidateRows.forEach((row: any) => {
      const views = parseInt(row.viewCount || '0', 10);
      const applications = parseInt(row.applicationCount || '0', 10);
      const applyClicks = parseInt(row.applyClicks || '0', 10);
      const explicitSeconds = parseInt(row.timeSpentSeconds || '0', 10);
      const spanSeconds = parseInt(row.spanSeconds || '0', 10);
      const activityCount = parseInt(row.activityCount || '0', 10);
      const effectiveSeconds =
        explicitSeconds > 0
          ? explicitSeconds
          : activityCount > 1
            ? spanSeconds
            : 0;
      const parts: string[] = [];
      if (views > 0) parts.push(`${views} view${views === 1 ? '' : 's'}`);
      if (applications > 0)
        parts.push(
          `${applications} application${applications === 1 ? '' : 's'}`,
        );
      if (applyClicks > 0)
        parts.push(`${applyClicks} apply click${applyClicks === 1 ? '' : 's'}`);

      const jobId = parseInt(row.jobId, 10);
      if (!candidatesByJob.has(jobId)) candidatesByJob.set(jobId, []);
      candidatesByJob.get(jobId)!.push({
        name: row.name || 'Unknown',
        timeSpent: formatDurationOrNA(effectiveSeconds),
        actions: parts.join(', ') || 'Viewed',
      });

      if (effectiveSeconds > 0) {
        const current = jobTimeTotals.get(jobId) || {
          totalSeconds: 0,
          candidateCount: 0,
        };
        current.totalSeconds += effectiveSeconds;
        current.candidateCount += 1;
        jobTimeTotals.set(jobId, current);
      }
    });

    return {
      success: true,
      data: data.map((row: any) => {
        const views = parseInt(row.views || '0', 10);
        const applications = parseInt(row.applications || '0', 10);
        const timing = jobTimeTotals.get(parseInt(row.id, 10));
        const avgSeconds =
          timing && timing.candidateCount > 0
            ? timing.totalSeconds / timing.candidateCount
            : 0;
        return {
          id: row.id,
          title: row.title,
          associate: row.associate,
          company: row.company || 'N/A',
          views,
          avgTime: formatDurationOrNA(avgSeconds),
          applyRate:
            views > 0 ? ((applications / views) * 100).toFixed(1) + '%' : '0%',
          candidates: candidatesByJob.get(parseInt(row.id, 10)) || [],
        };
      }),
    };
  }

  async getAssociateCityJobPosting(
    from?: string,
    to?: string,
    associate?: string,
    state?: string,
    industry?: string,
    page: number = 1,
    search?: string,
    distPage: number = 1,
    appsPage: number = 1,
    demandPage: number = 1,
    emptyPage: number = 1,
    distS?: string,
    appsS?: string,
    demandS?: string,
    emptyS?: string,
  ) {
    const dFrom = from
      ? new Date(from)
      : new Date(new Date().setFullYear(new Date().getFullYear() - 1));
    const dTo = to
      ? new Date(new Date(to).setHours(23, 59, 59, 999))
      : new Date();

    const limit = 20;
    const associateFilter =
      associate && associate !== 'All' ? ' AND u.full_name = ?' : '';
    const stateFilter = state && state !== 'All' ? ' AND s.state_name = ?' : '';
    const industryFilter =
      industry && industry !== 'All' ? ' AND ind.name = ?' : '';

    const baseParams: any[] = [dFrom, dTo];
    if (associate && associate !== 'All') baseParams.push(associate);
    if (state && state !== 'All') baseParams.push(state);
    if (industry && industry !== 'All') baseParams.push(industry);

    // 1. Distribution
    const distSearchFilter = distS ? ' AND ct.city_name LIKE ?' : '';
    const distTotal = await this.dataSource.query(
      `
      SELECT COUNT(DISTINCT ct.city_name) as total
      FROM job_posts jp
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE jp.associate_id IS NOT NULL AND jp.created_at >= ? AND jp.created_at <= ?
      ${associateFilter} ${stateFilter} ${industryFilter} ${distSearchFilter}
    `,
      distS ? [...baseParams, `%${distS}%`] : baseParams,
    );

    const distParams = distS
      ? [...baseParams, `%${distS}%`, limit, (distPage - 1) * limit]
      : [...baseParams, limit, (distPage - 1) * limit];
    const distribution = await this.dataSource.query(
      `
      SELECT ct.city_name as city, s.state_name as state, COUNT(jp.id) as jobs, '+0%' as \`change\`
      FROM job_posts jp
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE jp.associate_id IS NOT NULL AND jp.created_at >= ? AND jp.created_at <= ?
      ${associateFilter} ${stateFilter} ${industryFilter} ${distSearchFilter}
      GROUP BY ct.city_name, s.state_name
      ORDER BY jobs DESC
      LIMIT ? OFFSET ?
    `,
      distParams,
    );

    // 2. Applications
    const appsSearchFilter = appsS ? ' AND ct.city_name LIKE ?' : '';
    const appsTotal = await this.dataSource.query(
      `
      SELECT COUNT(DISTINCT ct.city_name) as total
      FROM job_applications ja
      INNER JOIN job_posts jp ON ja.job_post_id = jp.id
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE jp.associate_id IS NOT NULL AND ja.applied_at >= ? AND ja.applied_at <= ?
      ${associateFilter} ${stateFilter} ${industryFilter} ${appsSearchFilter}
    `,
      appsS ? [...baseParams, `%${appsS}%`] : baseParams,
    );

    const appsParams = appsS
      ? [...baseParams, `%${appsS}%`, limit, (appsPage - 1) * limit]
      : [...baseParams, limit, (appsPage - 1) * limit];
    const applications = await this.dataSource.query(
      `
      SELECT ct.city_name as city, s.state_name as state, COUNT(ja.id) as apps, 'Medium' as growth
      FROM job_applications ja
      INNER JOIN job_posts jp ON ja.job_post_id = jp.id
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE jp.associate_id IS NOT NULL AND ja.applied_at >= ? AND ja.applied_at <= ?
      ${associateFilter} ${stateFilter} ${industryFilter} ${appsSearchFilter}
      GROUP BY ct.city_name, s.state_name
      ORDER BY apps DESC
      LIMIT ? OFFSET ?
    `,
      appsParams,
    );

    // 3. Demand Score
    const demandSearchFilter = demandS ? ' AND ct.city_name LIKE ?' : '';
    const demandTotal = await this.dataSource.query(
      `
      SELECT COUNT(DISTINCT ct.city_name) as total
      FROM job_posts jp
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE jp.associate_id IS NOT NULL AND jp.created_at >= ? AND jp.created_at <= ?
      ${associateFilter} ${stateFilter} ${industryFilter} ${demandSearchFilter}
    `,
      demandS ? [...baseParams, `%${demandS}%`] : baseParams,
    );

    const demandParams = demandS
      ? [...baseParams, `%${demandS}%`, limit, (demandPage - 1) * limit]
      : [...baseParams, limit, (demandPage - 1) * limit];
    const demand = await this.dataSource.query(
      `
      SELECT ct.city_name as city, s.state_name as state,
             ROUND(COUNT(ja.id) / NULLIF(COUNT(DISTINCT jp.id), 0), 1) as score,
             CONCAT('1:', ROUND(COUNT(ja.id) / NULLIF(COUNT(DISTINCT jp.id), 0), 1)) as ratio
      FROM job_posts jp
      INNER JOIN city ct ON jp.city_id = ct.city_id
      INNER JOIN state s ON jp.state_id = s.state_id
      LEFT JOIN job_applications ja ON ja.job_post_id = jp.id
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN industries ind ON jp.industry_id = ind.id
      WHERE jp.associate_id IS NOT NULL AND jp.created_at >= ? AND jp.created_at <= ?
      ${associateFilter} ${stateFilter} ${industryFilter} ${demandSearchFilter}
      GROUP BY ct.city_name, s.state_name
      ORDER BY score DESC
      LIMIT ? OFFSET ?
    `,
      demandParams,
    );

    // 4. Empty Cities
    const emptySearchFilter = emptyS ? ' AND ct.city_name LIKE ?' : '';
    const outerStateParams = state && state !== 'All' ? [state] : [];

    const emptyTotal = await this.dataSource.query(
      `
      SELECT COUNT(*) as total
      FROM city ct
      INNER JOIN state s ON ct.state_id = s.state_id
      WHERE NOT EXISTS (
        SELECT 1
        FROM job_posts jp2 
        INNER JOIN associates a2 ON a2.id = jp2.associate_id
        INNER JOIN users u2 ON u2.id = a2.user_id
        LEFT JOIN industries ind2 ON jp2.industry_id = ind2.id
        WHERE jp2.city_id = ct.city_id AND jp2.associate_id IS NOT NULL 
          AND jp2.created_at >= ? AND jp2.created_at <= ?
        ${associateFilter.replace(/u\./g, 'u2.')} ${stateFilter.replace(/s\./g, 's2.')} ${industryFilter.replace(/ind\./g, 'ind2.')}
      )
      ${stateFilter} ${emptySearchFilter}
    `,
      emptyS
        ? [...baseParams, ...outerStateParams, `%${emptyS}%`]
        : [...baseParams, ...outerStateParams],
    );

    const emptyParamsWithSearch = emptyS
      ? [
          ...baseParams,
          ...outerStateParams,
          `%${emptyS}%`,
          limit,
          (emptyPage - 1) * limit,
        ]
      : [...baseParams, ...outerStateParams, limit, (emptyPage - 1) * limit];

    const empty = await this.dataSource.query(
      `
      SELECT ct.city_name as city, s.state_name as state, COALESCE(DATE_FORMAT(MAX(jp.created_at), '%Y-%m-%d'), 'Never') as lastJob
      FROM city ct
      INNER JOIN state s ON ct.state_id = s.state_id
      LEFT JOIN job_posts jp ON jp.city_id = ct.city_id
      WHERE NOT EXISTS (
        SELECT 1
        FROM job_posts jp2 
        INNER JOIN associates a2 ON a2.id = jp2.associate_id
        INNER JOIN users u2 ON u2.id = a2.user_id
        LEFT JOIN industries ind2 ON jp2.industry_id = ind2.id
        WHERE jp2.city_id = ct.city_id AND jp2.associate_id IS NOT NULL 
          AND jp2.created_at >= ? AND jp2.created_at <= ?
        ${associateFilter.replace(/u\./g, 'u2.')} ${stateFilter.replace(/s\./g, 's2.')} ${industryFilter.replace(/ind\./g, 'ind2.')}
      )
      ${stateFilter} ${emptySearchFilter}
      GROUP BY ct.city_id, ct.city_name, s.state_name
      ORDER BY ct.city_name ASC
      LIMIT ? OFFSET ?
    `,
      emptyParamsWithSearch,
    );

    // 5. Detailed Jobs Table
    const tableWhere: string[] = [
      'jp.associate_id IS NOT NULL',
      'jp.created_at >= ?',
      'jp.created_at <= ?',
    ];
    const tableParams: any[] = [dFrom, dTo];
    if (associate && associate !== 'All') {
      tableWhere.push('u.full_name = ?');
      tableParams.push(associate);
    }
    if (state && state !== 'All') {
      tableWhere.push('s.state_name = ?');
      tableParams.push(state);
    }
    if (industry && industry !== 'All') {
      tableWhere.push('ind.name = ?');
      tableParams.push(industry);
    }
    if (search) {
      tableWhere.push(
        '(ct.city_name LIKE ? OR jp.title LIKE ? OR c.name LIKE ?)',
      );
      tableParams.push(`%${search}%`, `%${search}%`, `%${search}%`);
    }

    const rows = await this.dataSource.query(
      `
      SELECT
        jp.id,
        jp.title,
        ct.city_name AS city,
        s.state_name AS state,
        c.name AS company,
        u.full_name AS associate,
        DATE_FORMAT(CONVERT_TZ(jp.created_at, @@session.time_zone, '+05:30'), '%Y-%m-%d') AS postedDate,
        DATE_FORMAT(CONVERT_TZ(jp.expires_at, @@session.time_zone, '+05:30'), '%Y-%m-%d') AS lastDateToApply,
        jp.status
      FROM job_posts jp
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN companies c ON c.id = jp.company_id
      LEFT JOIN city ct ON ct.city_id = jp.city_id
      LEFT JOIN state s ON s.state_id = jp.state_id
      LEFT JOIN industries ind ON ind.id = jp.industry_id
      WHERE ${tableWhere.join(' AND ')}
      ORDER BY jp.created_at DESC
      LIMIT ? OFFSET ?
      `,
      [...tableParams, limit, (Math.max(page, 1) - 1) * limit],
    );

    const countRows = await this.dataSource.query(
      `
      SELECT COUNT(*) AS total
      FROM job_posts jp
      INNER JOIN associates a ON a.id = jp.associate_id
      INNER JOIN users u ON u.id = a.user_id
      LEFT JOIN companies c ON c.id = jp.company_id
      LEFT JOIN city ct ON ct.city_id = jp.city_id
      LEFT JOIN state s ON s.state_id = jp.state_id
      LEFT JOIN industries ind ON ind.id = jp.industry_id
      WHERE ${tableWhere.join(' AND ')}
      `,
      tableParams,
    );

    return {
      success: true,
      data: {
        distribution: {
          data: distribution,
          total: parseInt(distTotal[0]?.total || 0, 10),
        },
        applications: {
          data: applications,
          total: parseInt(appsTotal[0]?.total || 0, 10),
        },
        demand: {
          data: demand,
          total: parseInt(demandTotal[0]?.total || 0, 10),
        },
        empty: { data: empty, total: parseInt(emptyTotal[0]?.total || 0, 10) },
        jobs: {
          data: rows,
          meta: {
            total: parseInt(countRows[0]?.total || 0, 10),
            page,
            totalPages: Math.ceil(
              parseInt(countRows[0]?.total || 0, 10) / limit,
            ),
            limit,
          },
        },
      },
    };
  }
}
