import { Injectable, Logger, OnModuleInit } from '@nestjs/common';
import { Cron, CronExpression } from '@nestjs/schedule';
import { DataSource } from 'typeorm';
import { WalletService } from '../wallet/wallet.service';
import { EmailService } from '../email/email.service';

@Injectable()
export class CronService implements OnModuleInit {
  private readonly logger = new Logger(CronService.name);

  constructor(
    private readonly walletService: WalletService,
    private readonly dataSource: DataSource,
    private readonly emailService: EmailService,
  ) { }

  async onModuleInit() {
    this.logger.log('Initializing CronService and checking/creating tracking and settings tables...');
    try {
      // 1. Initial schedules table
      await this.dataSource.query(`
        CREATE TABLE IF NOT EXISTS user_email_schedules (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT NOT NULL,
          exact_roles_last_sent TIMESTAMP NULL DEFAULT NULL,
          skills_last_sent TIMESTAMP NULL DEFAULT NULL,
          new_jobs_last_sent TIMESTAMP NULL DEFAULT NULL,
          UNIQUE KEY unique_user (user_id)
        )
      `);
      this.logger.log('user_email_schedules table checked/initialized.');

      // 2. Initialize logs tables
      await this.createEmailLogsTables();

      // 3. Seed default settings
      await this.seedDefaultSettings();

      // 4. Clean up excess test skipped logs to keep log history tidy
      await this.cleanSkippedTestLogs();
    } catch (err) {
      this.logger.error('Failed setup in CronService onModuleInit:', err.stack);
    }
  }

  private async cleanSkippedTestLogs() {
    try {
      await this.dataSource.query(`
        DELETE FROM email_sending_logs 
        WHERE status = 'skipped' AND reason = 'Skipped sending - Test Mode is active'
      `);
      this.logger.log('Cleaned up excess test skipped logs from database.');
    } catch (err) {
      this.logger.error('Failed to clean up test skipped logs:', err.stack);
    }
  }

  private async createEmailLogsTables() {
    try {
      await this.dataSource.query(`
        CREATE TABLE IF NOT EXISTS email_sending_logs (
          id INT AUTO_INCREMENT PRIMARY KEY,
          recipient_email VARCHAR(255) NOT NULL,
          recipient_name VARCHAR(255) NULL,
          recipient_type ENUM('seeker', 'recruiter') NOT NULL,
          email_type VARCHAR(100) NOT NULL,
          status ENUM('sent', 'skipped', 'failed') NOT NULL,
          reason VARCHAR(255) NULL,
          sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);
      await this.dataSource.query(`
        CREATE TABLE IF NOT EXISTS email_sending_log_jobs (
          id INT AUTO_INCREMENT PRIMARY KEY,
          log_id INT NOT NULL,
          job_post_id INT NOT NULL,
          FOREIGN KEY (log_id) REFERENCES email_sending_logs(id) ON DELETE CASCADE
        )
      `);
      this.logger.log('Email sending logs tables checked/initialized.');
    } catch (err) {
      this.logger.error('Failed to create email sending logs tables:', err.stack);
    }
  }

  private async seedDefaultSettings() {
    try {
      const defaultSettings = [
        { key: 'email_management_seeker_send_time', value: '09:00', desc: 'Hour of day to send seeker match alert emails (HH:MM)' },
        { key: 'email_management_seeker_max_jobs', value: '3', desc: 'Maximum number of jobs to recommend per email' },
        { key: 'email_management_seeker_duplication_days', value: '7', desc: 'Do not recommend the same job within this many days' },
        { key: 'email_management_recruiter_send_time', value: '09:00', desc: 'Hour of day to send recruiter alert emails (HH:MM)' },
        { key: 'email_management_recruiter_max_candidates', value: '3', desc: 'Maximum number of candidates to recommend per email' },
        { key: 'email_management_recruiter_duplication_days', value: '7', desc: 'Do not recommend the same candidate within this many days' },
        { key: 'email_management_test_mode', value: 'false', desc: 'If true, matching emails are only sent to whitelisted test emails' },
        { key: 'email_management_test_whitelist', value: 'saumyajyoti.k@gmail.com,sujit124baral@gmail.com', desc: 'Comma-separated whitelisted emails for test mode' }
      ];

      for (const setting of defaultSettings) {
        const rows = await this.dataSource.query(
          'SELECT id FROM config_setting WHERE setting_key = ?',
          [setting.key]
        );
        if (rows.length === 0) {
          await this.dataSource.query(
            'INSERT INTO config_setting (setting_key, setting_value, description) VALUES (?, ?, ?)',
            [setting.key, setting.value, setting.desc]
          );
          this.logger.log(`Seeded default config setting: ${setting.key}`);
        }
      }
    } catch (err) {
      this.logger.error('Failed to seed default config settings:', err.stack);
    }
  }

  private async getNumberConfigSetting(key: string, defaultValue: number): Promise<number> {
    try {
      const rows = await this.dataSource.query(
        'SELECT setting_value FROM config_setting WHERE setting_key = ?',
        [key]
      );
      if (rows.length > 0 && rows[0]?.setting_value) {
        const parsed = parseInt(rows[0].setting_value, 10);
        return isNaN(parsed) ? defaultValue : parsed;
      }
    } catch (err) {
      this.logger.error(`Error fetching config setting ${key}:`, err.stack);
    }
    return defaultValue;
  }

  private async createEmailLog(
    recipientEmail: string,
    recipientName: string,
    recipientType: 'seeker' | 'recruiter',
    emailType: string,
    status: 'sent' | 'skipped' | 'failed',
    reason: string | null,
    jobIds: number[] = []
  ): Promise<void> {
    try {
      const insertResult = await this.dataSource.query(`
        INSERT INTO email_sending_logs (recipient_email, recipient_name, recipient_type, email_type, status, reason)
        VALUES (?, ?, ?, ?, ?, ?)
      `, [recipientEmail, recipientName, recipientType, emailType, status, reason]);

      const logId = insertResult.insertId;
      if (logId && jobIds.length > 0) {
        for (const jobId of jobIds) {
          await this.dataSource.query(`
            INSERT INTO email_sending_log_jobs (log_id, job_post_id)
            VALUES (?, ?)
          `, [logId, jobId]);
        }
      }
    } catch (err) {
      this.logger.error(`Failed to write email log for ${recipientEmail}:`, err.stack);
    }
  }

  private async getExcludedJobIds(recipientEmail: string, duplicationDays: number): Promise<number[]> {
    try {
      const rows = await this.dataSource.query(`
        SELECT DISTINCT j.job_post_id 
        FROM email_sending_log_jobs j
        INNER JOIN email_sending_logs l ON l.id = j.log_id
        WHERE l.recipient_email = ? 
          AND l.sent_at >= NOW() - INTERVAL ? DAY
      `, [recipientEmail, duplicationDays]);
      return rows.map((row: any) => Number(row.job_post_id));
    } catch (err) {
      this.logger.error(`Failed to fetch duplicate jobs for ${recipientEmail}:`, err.stack);
      return [];
    }
  }

  private async getTestWhitelist(): Promise<{ isTestMode: boolean; whitelist: string[] }> {
    try {
      const testModeRows = await this.dataSource.query(
        "SELECT setting_value FROM config_setting WHERE setting_key = 'email_management_test_mode'"
      );
      const isTestMode = testModeRows.length > 0 ? testModeRows[0].setting_value === 'true' : true;

      const whitelistRows = await this.dataSource.query(
        "SELECT setting_value FROM config_setting WHERE setting_key = 'email_management_test_whitelist'"
      );
      const whitelist = whitelistRows.length > 0
        ? whitelistRows[0].setting_value.split(',').map((e: string) => e.trim().toLowerCase())
        : ['saumyajyoti.k@gmail.com', 'sujit124baral@gmail.com'];

      return { isTestMode, whitelist };
    } catch (err) {
      this.logger.error('Error fetching whitelist settings:', err.stack);
      return { isTestMode: true, whitelist: ['saumyajyoti.k@gmail.com', 'sujit124baral@gmail.com'] };
    }
  }

  /**
   * Cron job that runs every 5 minutes to send recruiter profile completion reminders.
   * Reminds recruiters registered >= 15 minutes ago who haven't completed onboarding yet.
   */
  @Cron(CronExpression.EVERY_5_MINUTES)
  async handleRecruiterProfileReminders() {
    this.logger.log('Checking for recruiters who need profile completion reminders...');
    try {
      const recruiters = await this.dataSource.query(`
        SELECT u.id, u.full_name as fullName, u.email 
        FROM users u
        WHERE u.role = 'recruiter'
          AND (u.onboarding_status IS NULL OR u.onboarding_status != 'completed')
          AND u.created_at <= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
          AND u.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
          AND u.deleted_at IS NULL
          AND NOT EXISTS (
            SELECT 1 FROM email_sending_logs l 
            WHERE l.recipient_email = u.email 
              AND l.email_type = 'recruiter_profile_reminder'
          )
      `);

      this.logger.log(`Found ${recruiters.length} recruiters needing profile reminder emails.`);

      for (const recruiter of recruiters) {
        if (!recruiter.email) continue;
        try {
          const sent = await this.emailService.sendTemplateEmail(
            'recruiter_profile_reminder',
            recruiter.email,
            recruiter.fullName || 'Recruiter',
            { preferred_name: recruiter.fullName || 'Recruiter' }
          );

          await this.dataSource.query(`
            INSERT INTO email_sending_logs (recipient_email, recipient_name, recipient_type, email_type, status, reason)
            VALUES (?, ?, 'recruiter', 'recruiter_profile_reminder', ?, ?)
          `, [
            recruiter.email,
            recruiter.fullName || 'Recruiter',
            sent ? 'sent' : 'failed',
            sent ? 'Sent profile completion reminder email successfully' : 'Template inactive or missing'
          ]);
        } catch (err) {
          this.logger.error(`Failed to send reminder email to recruiter ${recruiter.email}:`, err.stack);
        }
      }
    } catch (error) {
      this.logger.error('Error in recruiter profile reminder cron:', error.stack);
    }
  }

  /**
   * Daily periodic email dispatcher for matches
   * Runs daily at 9:00 AM
   */
  @Cron(CronExpression.EVERY_DAY_AT_9AM)
  async handlePeriodicMatchingEmails() {
    this.logger.log('Starting daily periodic matching emails check...');
    try {
      await this.processExactRoleMatchingEmails();
      await this.processSkillMatchingEmails();
      await this.processNewJobsMatchingEmails();
      await this.processRecruiterMatchEmails();
      this.logger.log('Daily periodic matching emails check completed.');
    } catch (error) {
      this.logger.error('Error in daily periodic matching emails check:', error.stack);
    }
  }

  private isTestOrGuestUser(user: { email: string; full_name?: string }): boolean {
    const email = (user.email || '').toLowerCase();
    const name = (user.full_name || '').toLowerCase();
    return email.includes('test') || email.includes('guest') || name.includes('test') || name.includes('guest');
  }

  private isTestOrGuestJob(job: { title: string; company_name?: string }): boolean {
    const title = (job.title || '').toLowerCase();
    const company = (job.company_name || '').toLowerCase();
    return title.includes('test') || title.includes('guest') || company.includes('test') || company.includes('guest');
  }

  async processExactRoleMatchingEmails() {
    this.logger.log('Processing 7-day exact role matching emails...');
    try {
      const maxJobs = await this.getNumberConfigSetting('email_management_seeker_max_jobs', 3);
      const duplicationDays = await this.getNumberConfigSetting('email_management_seeker_duplication_days', 7);
      const { isTestMode, whitelist } = await this.getTestWhitelist();

      // 1. Fetch eligible candidates (direct whitelist filter in SQL if Test Mode is active)
      let userQuery = `
        SELECT u.id as user_id, u.email, u.full_name
        FROM users u
        LEFT JOIN user_email_schedules ues ON ues.user_id = u.id
        WHERE u.role = 'seeker' AND u.status = 'active'
          AND (ues.exact_roles_last_sent IS NULL OR ues.exact_roles_last_sent <= NOW() - INTERVAL 7 DAY)
      `;
      const queryParams: any[] = [];

      if (isTestMode) {
        userQuery += ` AND LOWER(u.email) IN (${whitelist.map(() => '?').join(',')})`;
        queryParams.push(...whitelist);
      }

      const eligibleUsers = await this.dataSource.query(userQuery, queryParams);
      this.logger.log(`Found ${eligibleUsers.length} users eligible for exact role match emails.`);

      for (const user of eligibleUsers) {
        if (this.isTestOrGuestUser(user)) {
          this.logger.log(`Skipping exact role matching email for test/guest user: ${user.email}`);
          continue;
        }
        try {
          // 2. Fetch user preferred roles
          const preferredRoles = await this.dataSource.query(`
            SELECT spr.role_id
            FROM seeker_preferred_roles spr
            INNER JOIN seeker_preferences sp ON spr.seeker_preference_id = sp.id
            WHERE sp.user_id = ?
          `, [user.user_id]);

          // Fetch profile profession_id as a fallback
          const profileRoles = await this.dataSource.query(`
            SELECT profession_id
            FROM seeker_profiles
            WHERE user_id = ? AND profession_id IS NOT NULL
          `, [user.user_id]);

          const roleIds = Array.from(new Set([
            ...preferredRoles.map((r: any) => r.role_id),
            ...profileRoles.map((r: any) => r.profession_id)
          ])).filter(Boolean);

          if (roleIds.length === 0) {
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'exact_roles_match',
              'skipped',
              'No preferred roles or profile profession defined'
            );
            continue;
          }

          // Fetch de-duplication list
          const excludedJobIds = await this.getExcludedJobIds(user.email, duplicationDays);

          // 3. Find open jobs matching roleIds
          const jobs = await this.dataSource.query(`
            SELECT jp.id, jp.title, jp.salary_min, jp.salary_max, c.name as company_name, city.city_name as city, s.state_name as state
            FROM job_posts jp
            LEFT JOIN companies c ON jp.company_id = c.id
            LEFT JOIN city ON jp.city_id = city.city_id
            LEFT JOIN state s ON jp.state_id = s.state_id
            WHERE jp.status = 'open' AND jp.role_id IN (${roleIds.map(() => '?').join(',')})
            ORDER BY jp.created_at DESC
          `, roleIds);

          let filteredJobs = jobs.filter((j: any) => !excludedJobIds.includes(Number(j.id)) && !this.isTestOrGuestJob(j)).slice(0, maxJobs);

          if (filteredJobs.length === 0) {
            this.logger.log(`No exact role match jobs found for ${user.email}. Querying random open jobs instead...`);
            const randomJobs = await this.dataSource.query(`
              SELECT jp.id, jp.title, jp.salary_min, jp.salary_max, c.name as company_name, city.city_name as city, s.state_name as state
              FROM job_posts jp
              LEFT JOIN companies c ON jp.company_id = c.id
              LEFT JOIN city ON jp.city_id = city.city_id
              LEFT JOIN state s ON jp.state_id = s.state_id
              WHERE jp.status = 'open'
              ORDER BY RAND()
              LIMIT 15
            `);
            filteredJobs = randomJobs.filter((j: any) =>
              !excludedJobIds.includes(Number(j.id)) && !this.isTestOrGuestJob(j)
            ).slice(0, maxJobs);
          }

          if (filteredJobs.length === 0) {
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'exact_roles_match',
              'skipped',
              'No open jobs found matching candidates preferred roles and no random open jobs available'
            );
            continue;
          }

          // 4. Format jobs list with their skills
          const formattedJobs = await this.formatJobsForEmail(filteredJobs);

          // 5. Send email
          const context = {
            subject: 'Exact Job Matches for your preferred Roles - JobVumi',
            cta_url: `${process.env.FRONTEND_ORIGIN || 'https://jobvumi.com'}/jobs`,
            candidate_name: user.full_name,
            jobs: formattedJobs,
          };

          const success = await this.emailService.sendTemplateEmail(
            'exact_roles_match',
            user.email,
            user.full_name,
            context
          );

          if (success) {
            await this.updateLastSent(user.user_id, 'exact_roles_last_sent');
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'exact_roles_match',
              'sent',
              'Successfully delivered via Brevo API',
              filteredJobs.map((j: any) => Number(j.id))
            );
          } else {
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'exact_roles_match',
              'failed',
              'Brevo email relay dispatch failed'
            );
          }
        } catch (err: any) {
          this.logger.error(`Failed to process exact role matching email for user ${user.user_id}:`, err.stack);
          await this.createEmailLog(
            user.email,
            user.full_name,
            'seeker',
            'exact_roles_match',
            'failed',
            err.message || 'Internal process error'
          );
        }
      }
    } catch (err) {
      this.logger.error('Error in processExactRoleMatchingEmails:', err.stack);
    }
  }

  async processSkillMatchingEmails() {
    this.logger.log('Processing 5-day skill matching emails...');
    try {
      const maxJobs = await this.getNumberConfigSetting('email_management_seeker_max_jobs', 3);
      const duplicationDays = await this.getNumberConfigSetting('email_management_seeker_duplication_days', 7);
      const { isTestMode, whitelist } = await this.getTestWhitelist();

      // 1. Fetch eligible candidates
      let userQuery = `
        SELECT u.id as user_id, u.email, u.full_name
        FROM users u
        LEFT JOIN user_email_schedules ues ON ues.user_id = u.id
        WHERE u.role = 'seeker' AND u.status = 'active'
          AND (ues.skills_last_sent IS NULL OR ues.skills_last_sent <= NOW() - INTERVAL 5 DAY)
      `;
      const queryParams: any[] = [];

      if (isTestMode) {
        userQuery += ` AND LOWER(u.email) IN (${whitelist.map(() => '?').join(',')})`;
        queryParams.push(...whitelist);
      }

      const eligibleUsers = await this.dataSource.query(userQuery, queryParams);
      this.logger.log(`Found ${eligibleUsers.length} users eligible for skill match emails.`);

      for (const user of eligibleUsers) {
        if (this.isTestOrGuestUser(user)) {
          this.logger.log(`Skipping skill matching email for test/guest user: ${user.email}`);
          continue;
        }
        try {
          // 2. Fetch user preferred skills
          const preferredSkills = await this.dataSource.query(`
            SELECT sps.skill_id
            FROM seeker_preferred_skills sps
            INNER JOIN seeker_preferences sp ON sps.seeker_preference_id = sp.id
            WHERE sp.user_id = ?
          `, [user.user_id]);

          // Fetch user profile skills
          const profileSkills = await this.dataSource.query(`
            SELECT ss.skill_id
            FROM seeker_skills ss
            INNER JOIN seeker_profiles spr ON ss.seeker_id = spr.id
            WHERE spr.user_id = ?
          `, [user.user_id]);

          const skillIds = Array.from(new Set([
            ...preferredSkills.map((s: any) => s.skill_id),
            ...profileSkills.map((s: any) => s.skill_id)
          ])).filter(Boolean);

          if (skillIds.length === 0) {
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'skills_match',
              'skipped',
              'No skills defined in preferences or profile'
            );
            continue;
          }

          // Fetch de-duplication list
          const excludedJobIds = await this.getExcludedJobIds(user.email, duplicationDays);

          // 3. Find open jobs matching skillIds
          const jobs = await this.dataSource.query(`
            SELECT jp.id, jp.title, jp.salary_min, jp.salary_max, c.name as company_name, city.city_name as city, s.state_name as state,
                   COUNT(jps.skill_id) as matched_skills_count
            FROM job_posts jp
            INNER JOIN job_post_skills jps ON jp.id = jps.job_post_id
            LEFT JOIN companies c ON jp.company_id = c.id
            LEFT JOIN city ON jp.city_id = city.city_id
            LEFT JOIN state s ON jp.state_id = s.state_id
            WHERE jp.status = 'open' AND jps.skill_id IN (${skillIds.map(() => '?').join(',')})
            GROUP BY jp.id
            ORDER BY matched_skills_count DESC, jp.created_at DESC
          `, skillIds);

          let filteredJobs = jobs.filter((j: any) => !excludedJobIds.includes(Number(j.id)) && !this.isTestOrGuestJob(j)).slice(0, maxJobs);

          if (filteredJobs.length === 0) {
            this.logger.log(`No skill match jobs found for ${user.email}. Querying random open jobs instead...`);
            const randomJobs = await this.dataSource.query(`
              SELECT jp.id, jp.title, jp.salary_min, jp.salary_max, c.name as company_name, city.city_name as city, s.state_name as state
              FROM job_posts jp
              LEFT JOIN companies c ON jp.company_id = c.id
              LEFT JOIN city ON jp.city_id = city.city_id
              LEFT JOIN state s ON jp.state_id = s.state_id
              WHERE jp.status = 'open'
              ORDER BY RAND()
              LIMIT 15
            `);
            filteredJobs = randomJobs.filter((j: any) =>
              !excludedJobIds.includes(Number(j.id)) && !this.isTestOrGuestJob(j)
            ).slice(0, maxJobs);
          }

          if (filteredJobs.length === 0) {
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'skills_match',
              'skipped',
              'No open jobs found matching candidate skills and no random open jobs available'
            );
            continue;
          }

          // 4. Format jobs list with their skills
          const formattedJobs = await this.formatJobsForEmail(filteredJobs);

          // 5. Send email
          const context = {
            subject: 'Job Openings Matching your Skills - JobVumi',
            cta_url: `${process.env.FRONTEND_ORIGIN || 'https://jobvumi.com'}/jobs`,
            candidate_name: user.full_name,
            jobs: formattedJobs,
          };

          const success = await this.emailService.sendTemplateEmail(
            'skills_match',
            user.email,
            user.full_name,
            context
          );

          if (success) {
            await this.updateLastSent(user.user_id, 'skills_last_sent');
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'skills_match',
              'sent',
              'Successfully delivered via Brevo API',
              filteredJobs.map((j: any) => Number(j.id))
            );
          } else {
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'skills_match',
              'failed',
              'Brevo email relay dispatch failed'
            );
          }
        } catch (err: any) {
          this.logger.error(`Failed to process skill matching email for user ${user.user_id}:`, err.stack);
          await this.createEmailLog(
            user.email,
            user.full_name,
            'seeker',
            'skills_match',
            'failed',
            err.message || 'Internal process error'
          );
        }
      }
    } catch (err) {
      this.logger.error('Error in processSkillMatchingEmails:', err.stack);
    }
  }

  async processNewJobsMatchingEmails() {
    this.logger.log('Processing 3-day new matching jobs emails...');
    try {
      const maxJobs = await this.getNumberConfigSetting('email_management_seeker_max_jobs', 3);
      const duplicationDays = await this.getNumberConfigSetting('email_management_seeker_duplication_days', 7);
      const { isTestMode, whitelist } = await this.getTestWhitelist();

      // 1. Fetch eligible candidates
      let userQuery = `
        SELECT u.id as user_id, u.email, u.full_name
        FROM users u
        LEFT JOIN user_email_schedules ues ON ues.user_id = u.id
        WHERE u.role = 'seeker' AND u.status = 'active'
          AND (ues.new_jobs_last_sent IS NULL OR ues.new_jobs_last_sent <= NOW() - INTERVAL 3 DAY)
      `;
      const queryParams: any[] = [];

      if (isTestMode) {
        userQuery += ` AND LOWER(u.email) IN (${whitelist.map(() => '?').join(',')})`;
        queryParams.push(...whitelist);
      }

      const eligibleUsers = await this.dataSource.query(userQuery, queryParams);
      this.logger.log(`Found ${eligibleUsers.length} users eligible for new jobs emails.`);

      for (const user of eligibleUsers) {
        if (this.isTestOrGuestUser(user)) {
          this.logger.log(`Skipping new jobs matching email for test/guest user: ${user.email}`);
          continue;
        }
        try {
          // 2. Fetch user preferred roles
          const preferredRoles = await this.dataSource.query(`
            SELECT spr.role_id
            FROM seeker_preferred_roles spr
            INNER JOIN seeker_preferences sp ON spr.seeker_preference_id = sp.id
            WHERE sp.user_id = ?
          `, [user.user_id]);

          // Fetch profile profession_id
          const profileRoles = await this.dataSource.query(`
            SELECT profession_id
            FROM seeker_profiles
            WHERE user_id = ? AND profession_id IS NOT NULL
          `, [user.user_id]);

          const roleIds = Array.from(new Set([
            ...preferredRoles.map((r: any) => r.role_id),
            ...profileRoles.map((r: any) => r.profession_id)
          ])).filter(Boolean);

          // Fetch user preferred skills
          const preferredSkills = await this.dataSource.query(`
            SELECT sps.skill_id
            FROM seeker_preferred_skills sps
            INNER JOIN seeker_preferences sp ON sps.seeker_preference_id = sp.id
            WHERE sp.user_id = ?
          `, [user.user_id]);

          // Fetch user profile skills
          const profileSkills = await this.dataSource.query(`
            SELECT ss.skill_id
            FROM seeker_skills ss
            INNER JOIN seeker_profiles spr ON ss.seeker_id = spr.id
            WHERE spr.user_id = ?
          `, [user.user_id]);

          const skillIds = Array.from(new Set([
            ...preferredSkills.map((s: any) => s.skill_id),
            ...profileSkills.map((s: any) => s.skill_id)
          ])).filter(Boolean);

          if (roleIds.length === 0 && skillIds.length === 0) {
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'new_jobs_match',
              'skipped',
              'No roles or skills defined to match new jobs'
            );
            continue;
          }

          // Fetch de-duplication list
          const excludedJobIds = await this.getExcludedJobIds(user.email, duplicationDays);

          // 3. Find new open jobs (created in last 3 days) matching roles or skills
          let jobs: any[] = [];
          if (roleIds.length > 0 && skillIds.length > 0) {
            jobs = await this.dataSource.query(`
              SELECT jp.id, jp.title, jp.salary_min, jp.salary_max, c.name as company_name, city.city_name as city, s.state_name as state
              FROM job_posts jp
              LEFT JOIN companies c ON jp.company_id = c.id
              LEFT JOIN city ON jp.city_id = city.city_id
              LEFT JOIN state s ON jp.state_id = s.state_id
              WHERE jp.status = 'open' 
                AND jp.created_at >= NOW() - INTERVAL 3 DAY
                AND (
                  jp.role_id IN (${roleIds.map(() => '?').join(',')})
                  OR jp.id IN (
                    SELECT DISTINCT job_post_id 
                    FROM job_post_skills 
                    WHERE skill_id IN (${skillIds.map(() => '?').join(',')})
                  )
                )
              ORDER BY jp.created_at DESC
            `, [...roleIds, ...skillIds]);
          } else if (roleIds.length > 0) {
            jobs = await this.dataSource.query(`
              SELECT jp.id, jp.title, jp.salary_min, jp.salary_max, c.name as company_name, city.city_name as city, s.state_name as state
              FROM job_posts jp
              LEFT JOIN companies c ON jp.company_id = c.id
              LEFT JOIN city ON jp.city_id = city.city_id
              LEFT JOIN state s ON jp.state_id = s.state_id
              WHERE jp.status = 'open' 
                AND jp.created_at >= NOW() - INTERVAL 3 DAY
                AND jp.role_id IN (${roleIds.map(() => '?').join(',')})
              ORDER BY jp.created_at DESC
            `, roleIds);
          } else if (skillIds.length > 0) {
            jobs = await this.dataSource.query(`
              SELECT jp.id, jp.title, jp.salary_min, jp.salary_max, c.name as company_name, city.city_name as city, s.state_name as state
              FROM job_posts jp
              LEFT JOIN companies c ON jp.company_id = c.id
              LEFT JOIN city ON jp.city_id = city.city_id
              LEFT JOIN state s ON jp.state_id = s.state_id
              WHERE jp.status = 'open' 
                AND jp.created_at >= NOW() - INTERVAL 3 DAY
                AND jp.id IN (
                  SELECT DISTINCT job_post_id 
                  FROM job_post_skills 
                  WHERE skill_id IN (${skillIds.map(() => '?').join(',')})
                )
              ORDER BY jp.created_at DESC
            `, skillIds);
          }

          let filteredJobs = jobs.filter((j: any) => !excludedJobIds.includes(Number(j.id)) && !this.isTestOrGuestJob(j)).slice(0, maxJobs);

          if (filteredJobs.length === 0) {
            this.logger.log(`No matching new jobs found for ${user.email}. Querying random open jobs instead...`);
            const randomJobs = await this.dataSource.query(`
              SELECT jp.id, jp.title, jp.salary_min, jp.salary_max, c.name as company_name, city.city_name as city, s.state_name as state
              FROM job_posts jp
              LEFT JOIN companies c ON jp.company_id = c.id
              LEFT JOIN city ON jp.city_id = city.city_id
              LEFT JOIN state s ON jp.state_id = s.state_id
              WHERE jp.status = 'open'
              ORDER BY RAND()
              LIMIT 15
            `);
            filteredJobs = randomJobs.filter((j: any) =>
              !excludedJobIds.includes(Number(j.id)) && !this.isTestOrGuestJob(j)
            ).slice(0, maxJobs);
          }

          if (filteredJobs.length === 0) {
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'new_jobs_match',
              'skipped',
              'No new open jobs matching user profile and no random open jobs available'
            );
            continue;
          }

          // 4. Format jobs list with their skills
          const formattedJobs = await this.formatJobsForEmail(filteredJobs);

          // 5. Send email
          const context = {
            subject: 'New Job Opportunities for You - JobVumi',
            cta_url: `${process.env.FRONTEND_ORIGIN || 'https://jobvumi.com'}/jobs`,
            candidate_name: user.full_name,
            jobs: formattedJobs,
          };

          const success = await this.emailService.sendTemplateEmail(
            'new_jobs_match',
            user.email,
            user.full_name,
            context
          );

          if (success) {
            await this.updateLastSent(user.user_id, 'new_jobs_last_sent');
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'new_jobs_match',
              'sent',
              'Successfully delivered via Brevo API',
              filteredJobs.map((j: any) => Number(j.id))
            );
          } else {
            await this.createEmailLog(
              user.email,
              user.full_name,
              'seeker',
              'new_jobs_match',
              'failed',
              'Brevo email relay dispatch failed'
            );
          }
        } catch (err: any) {
          this.logger.error(`Failed to process new jobs matching email for user ${user.user_id}:`, err.stack);
          await this.createEmailLog(
            user.email,
            user.full_name,
            'seeker',
            'new_jobs_match',
            'failed',
            err.message || 'Internal process error'
          );
        }
      }
    } catch (err) {
      this.logger.error('Error in processNewJobsMatchingEmails:', err.stack);
    }
  }

  async processRecruiterMatchEmails() {
    this.logger.log('Processing recruiter matching candidate alert emails (placeholder)...');
    try {
      const { isTestMode, whitelist } = await this.getTestWhitelist();

      let recruiterQuery = `
        SELECT id as user_id, email, full_name
        FROM users
        WHERE role = 'recruiter' AND status = 'active'
      `;
      const queryParams: any[] = [];

      if (isTestMode) {
        recruiterQuery += ` AND LOWER(email) IN (${whitelist.map(() => '?').join(',')})`;
        queryParams.push(...whitelist);
      }

      const recruiters = await this.dataSource.query(recruiterQuery, queryParams);

      for (const rec of recruiters) {
        if (this.isTestOrGuestUser(rec)) {
          this.logger.log(`Skipping recruiter matching email for test/guest recruiter: ${rec.email}`);
          continue;
        }
        try {
          // Query up to 3 random active candidates who are not test/guest profiles
          const candidates = await this.dataSource.query(`
            SELECT u.id as user_id, u.full_name, u.email, sp.professional_title,
                   (SELECT name FROM roles WHERE id = sp.profession_id) as profession_name,
                   sp.skills_ids
            FROM users u
            INNER JOIN seeker_profiles sp ON u.id = sp.user_id
            WHERE u.role = 'seeker' AND u.status = 'active'
              AND LOWER(u.full_name) NOT LIKE '%test%'
              AND LOWER(u.full_name) NOT LIKE '%guest%'
              AND LOWER(u.email) NOT LIKE '%test%'
              AND LOWER(u.email) NOT LIKE '%guest%'
            ORDER BY RAND()
            LIMIT 3
          `);

          if (candidates.length === 0) {
            await this.createEmailLog(
              rec.email,
              rec.full_name,
              'recruiter',
              'recruiter_candidate_match',
              'skipped',
              'No active candidates available to recommend'
            );
            continue;
          }

          const formattedCandidates = [];
          for (const cand of candidates) {
            let skillNames = '';
            if (cand.skills_ids) {
              try {
                const skillIds = typeof cand.skills_ids === 'string' ? JSON.parse(cand.skills_ids) : cand.skills_ids;
                if (Array.isArray(skillIds) && skillIds.length > 0) {
                  const skills = await this.dataSource.query(`
                    SELECT skill_name FROM skills WHERE id IN (${skillIds.map(() => '?').join(',')})
                  `, skillIds);
                  skillNames = skills.map((s: any) => s.skill_name).join(', ');
                }
              } catch (e) {
                // Ignore skill parsing issues
              }
            }
            formattedCandidates.push({
              full_name: cand.full_name,
              profession_name: cand.profession_name || cand.professional_title || 'Professional',
              skills: skillNames || 'Not specified'
            });
          }

          const success = await this.emailService.sendTemplateEmail(
            'recruiter_candidate_match',
            rec.email,
            rec.full_name,
            {
              recruiter_name: rec.full_name,
              candidates: formattedCandidates
            }
          );

          if (success) {
            await this.createEmailLog(
              rec.email,
              rec.full_name,
              'recruiter',
              'recruiter_candidate_match',
              'sent',
              'Successfully delivered via Brevo API with random candidate profiles'
            );
          } else {
            await this.createEmailLog(
              rec.email,
              rec.full_name,
              'recruiter',
              'recruiter_candidate_match',
              'failed',
              'Failed to send recruiter candidate match email'
            );
          }
        } catch (err: any) {
          this.logger.error(`Failed to process recruiter matching email for user ${rec.user_id}:`, err.stack);
          await this.createEmailLog(
            rec.email,
            rec.full_name,
            'recruiter',
            'recruiter_candidate_match',
            'failed',
            err.message || 'Internal process error'
          );
        }
      }
    } catch (err) {
      this.logger.error('Error in processRecruiterMatchEmails:', err.stack);
    }
  }

  private async formatJobsForEmail(jobs: any[]): Promise<any[]> {
    const formatted: any[] = [];
    for (const job of jobs) {
      try {
        const skills = await this.dataSource.query(`
          SELECT s.skill_name
          FROM job_post_skills jps
          INNER JOIN skills s ON jps.skill_id = s.id
          WHERE jps.job_post_id = ?
        `, [job.id]);

        const skillsList = skills.map((s: any) => s.skill_name).join(' • ');

        let salaryText = '';
        if (job.salary_min && job.salary_max) {
          salaryText = `${job.salary_min.toLocaleString()} - ${job.salary_max.toLocaleString()}`;
        } else if (job.salary_min) {
          salaryText = `${job.salary_min.toLocaleString()}+`;
        }

        formatted.push({
          title: job.title,
          company_name: job.company_name || 'Confidential Company',
          city: job.city || '',
          state: job.state || '',
          salary_text: salaryText,
          skills_list: skillsList,
          job_url: `${process.env.FRONTEND_ORIGIN || 'https://jobvumi.com'}/job/${job.id}`
        });
      } catch (err) {
        this.logger.error(`Error formatting job ID ${job.id} for email:`, err.stack);
      }
    }
    return formatted;
  }

  private async updateLastSent(userId: number, columnName: string): Promise<void> {
    try {
      await this.dataSource.query(`
        INSERT INTO user_email_schedules (user_id, ${columnName})
        VALUES (?, NOW())
        ON DUPLICATE KEY UPDATE ${columnName} = NOW()
      `, [userId]);
    } catch (err) {
      this.logger.error(`Error updating last sent field '${columnName}' for user ${userId}:`, err.stack);
    }
  }

  /**
   * Daily Credit Expiry Check
   * Runs at midnight every day
   */
  @Cron(CronExpression.EVERY_DAY_AT_MIDNIGHT)
  async handleCreditExpiry() {
    this.logger.log('Starting daily credit expiry check...');

    try {
      const wallets = await this.dataSource.query(
        `SELECT user_id FROM wallets 
             WHERE job_credits > 0 OR database_credits > 0 OR ai_credits > 0`,
      );

      this.logger.log(`Found ${wallets.length} wallets to check.`);

      for (const wallet of wallets) {
        try {
          await this.walletService.checkAndProcessExpiry(wallet.user_id);
        } catch (err) {
          this.logger.error(
            `Failed to process expiry for user ${wallet.user_id}`,
            err.stack,
          );
        }
      }

      this.logger.log('Daily credit expiry check completed.');
    } catch (error) {
      this.logger.error('Critical error in digest loop', error.stack);
    }
  }

  /**
   * Subscription Expiry Check
   * Runs daily at midnight
   */
  @Cron(CronExpression.EVERY_DAY_AT_MIDNIGHT)
  async handleSubscriptionExpiry() {
    this.logger.log('Starting subscription expiry check...');
    try {
      await this.dataSource.query(
        `UPDATE user_subscriptions 
                 SET status = 'expired' 
                 WHERE status = 'active' AND ends_at < NOW()`,
      );
      this.logger.log(`Subscription expiry check completed.`);
    } catch (error) {
      this.logger.error(
        'Critical error updating expired subscriptions',
        error.stack,
      );
    }
  }

  /**
   * Job Expiry Check
   * Automatically closes jobs that have passed their expiration date
   */
  @Cron(CronExpression.EVERY_DAY_AT_MIDNIGHT)
  async handleJobExpiry() {
    this.logger.log('Starting job expiry cleanup...');
    try {
      await this.dataSource.query(
        `UPDATE job_posts 
                 SET status = 'closed' 
                 WHERE status = 'open' AND expires_at < NOW()`,
      );
      this.logger.log(
        `Job expiry cleanup completed. Status set to closed for expired jobs.`,
      );
    } catch (error) {
      this.logger.error('Critical error in job expiry cleanup', error.stack);
    }
  }
}
