import { sendCertificationExpirationReminder } from "./certificationEmailService.ts";
import { getDb } from "./db.ts";

/**
 * Scheduled job to send expiration reminders
 * Run this daily to check for certificates expiring soon
 */
export async function sendExpirationReminders() {
  try {
    const db = await getDb();
    if (!db) {
      throw new Error("Database connection failed");
    }

    console.log("[Training Job] Starting expiration reminder job...");

    // Get certificates expiring in the next 30 days
    const expiringCerts = await db.execute(`
      SELECT 
        ci.id,
        ci.admin_name,
        ci.admin_email,
        ac.title as certification_title,
        ci.expiration_date,
        DATEDIFF(ci.expiration_date, NOW()) as days_until_expiration
      FROM certification_issues ci
      JOIN admin_certifications ac ON ci.certification_id = ac.id
      WHERE 
        ci.is_revoked = false
        AND ci.expiration_date > NOW()
        AND DATEDIFF(ci.expiration_date, NOW()) <= 30
        AND DATEDIFF(ci.expiration_date, NOW()) > 0
      ORDER BY ci.expiration_date ASC
    `);

    console.log(`[Training Job] Found ${expiringCerts.length} certificates expiring soon`);

    let sentCount = 0;
    let failedCount = 0;

    for (const cert of expiringCerts) {
      try {
        const result = await sendCertificationExpirationReminder(
          cert.admin_name,
          cert.admin_email,
          cert.certification_title,
          new Date(cert.expiration_date),
          `${process.env.VITE_APP_URL || "https://playplaycoinkrazy.com"}/verify-certificate/${cert.id}`
        );

        if (result.success) {
          sentCount++;
          console.log(`[Training Job] Sent reminder to ${cert.admin_email}`);
        } else {
          failedCount++;
          console.error(`[Training Job] Failed to send reminder to ${cert.admin_email}: ${result.error}`);
        }
      } catch (error) {
        failedCount++;
        console.error(`[Training Job] Error sending reminder: ${error}`);
      }
    }

    console.log(
      `[Training Job] Expiration reminder job completed: ${sentCount} sent, ${failedCount} failed`
    );
    return { success: true, sent: sentCount, failed: failedCount };
  } catch (error) {
    console.error("[Training Job] Expiration reminder job failed:", error);
    return { success: false, error };
  }
}

/**
 * Scheduled job to update training analytics
 * Run this daily to aggregate training statistics
 */
export async function updateTrainingAnalytics() {
  try {
    const db = await getDb();
    if (!db) {
      throw new Error("Database connection failed");
    }

    console.log("[Training Job] Starting analytics update job...");

    // Get all admins with training progress
    const admins = await db.execute(`
      SELECT DISTINCT admin_id FROM admin_training_progress
    `);

    console.log(`[Training Job] Updating analytics for ${admins.length} admins`);

    let updatedCount = 0;

    for (const admin of admins) {
      try {
        // Get unique modules for this admin
        const modules = await db.execute(`
          SELECT DISTINCT module FROM admin_training_progress 
          WHERE admin_id = ?
        `, [admin.admin_id]);

        for (const module of modules) {
          // Count videos watched
          const videosWatched = await db.execute(`
            SELECT COUNT(*) as count FROM admin_training_progress
            WHERE admin_id = ? AND module = ? AND progress_type = 'video-watched'
          `, [admin.admin_id, module.module]);

          // Count questions answered
          const questionsAnswered = await db.execute(`
            SELECT COUNT(*) as count FROM admin_training_progress
            WHERE admin_id = ? AND module = ? AND progress_type = 'question-answered'
          `, [admin.admin_id, module.module]);

          // Get quiz score
          const quizScore = await db.execute(`
            SELECT AVG(score) as avg_score FROM admin_training_progress
            WHERE admin_id = ? AND module = ? AND progress_type = 'quiz-completed'
          `, [admin.admin_id, module.module]);

          // Check if certification earned
          const certEarned = await db.execute(`
            SELECT COUNT(*) as count FROM admin_training_progress
            WHERE admin_id = ? AND module = ? AND progress_type = 'certification-earned'
          `, [admin.admin_id, module.module]);

          // Calculate total time spent (estimate: 5 min per video + 2 min per question)
          const totalTimeSpent = 
            (videosWatched[0]?.count || 0) * 5 + 
            (questionsAnswered[0]?.count || 0) * 2;

          // Get last activity
          const lastActivity = await db.execute(`
            SELECT MAX(created_at) as last_activity FROM admin_training_progress
            WHERE admin_id = ? AND module = ?
          `, [admin.admin_id, module.module]);

          // Upsert analytics record
          const analyticsId = `analytics-${admin.admin_id}-${module.module}-${Date.now()}`;
          
          await db.execute(`
            INSERT INTO training_analytics 
            (id, admin_id, module, videos_watched, questions_answered, quiz_score, certification_earned, total_time_spent, last_activity, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
            ON DUPLICATE KEY UPDATE
            videos_watched = ?,
            questions_answered = ?,
            quiz_score = ?,
            certification_earned = ?,
            total_time_spent = ?,
            last_activity = ?,
            updated_at = NOW()
          `, [
            analyticsId,
            admin.admin_id,
            module.module,
            videosWatched[0]?.count || 0,
            questionsAnswered[0]?.count || 0,
            quizScore[0]?.avg_score || 0,
            certEarned[0]?.count > 0,
            totalTimeSpent,
            lastActivity[0]?.last_activity,
            videosWatched[0]?.count || 0,
            questionsAnswered[0]?.count || 0,
            quizScore[0]?.avg_score || 0,
            certEarned[0]?.count > 0,
            totalTimeSpent,
            lastActivity[0]?.last_activity,
          ]);

          updatedCount++;
        }
      } catch (error) {
        console.error(`[Training Job] Error updating analytics for admin ${admin.admin_id}:`, error);
      }
    }

    console.log(`[Training Job] Analytics update completed: ${updatedCount} records updated`);
    return { success: true, updated: updatedCount };
  } catch (error) {
    console.error("[Training Job] Analytics update job failed:", error);
    return { success: false, error };
  }
}

/**
 * Scheduled job to revoke expired certificates
 * Run this daily to automatically revoke certificates that have expired
 */
export async function revokeExpiredCertificates() {
  try {
    const db = await getDb();
    if (!db) {
      throw new Error("Database connection failed");
    }

    console.log("[Training Job] Starting certificate revocation job...");

    // Get expired certificates
    const expiredCerts = await db.execute(`
      SELECT id, admin_name, admin_email FROM certification_issues
      WHERE is_revoked = false AND expiration_date < NOW()
    `);

    console.log(`[Training Job] Found ${expiredCerts.length} expired certificates`);

    let revokedCount = 0;

    for (const cert of expiredCerts) {
      try {
        await db.execute(`
          UPDATE certification_issues
          SET is_revoked = true, revoked_at = NOW(), revoked_reason = 'Automatically revoked: Certificate expired'
          WHERE id = ?
        `, [cert.id]);

        revokedCount++;
        console.log(`[Training Job] Revoked certificate for ${cert.admin_name}`);
      } catch (error) {
        console.error(`[Training Job] Error revoking certificate ${cert.id}:`, error);
      }
    }

    console.log(`[Training Job] Certificate revocation completed: ${revokedCount} certificates revoked`);
    return { success: true, revoked: revokedCount };
  } catch (error) {
    console.error("[Training Job] Certificate revocation job failed:", error);
    return { success: false, error };
  }
}

/**
 * Initialize all scheduled jobs
 * Call this on application startup
 */
export function initializeScheduledJobs() {
  console.log("[Training Jobs] Initializing scheduled jobs...");

  // Run expiration reminders daily at 9 AM
  const expirationReminderInterval = setInterval(() => {
    const now = new Date();
    if (now.getHours() === 9 && now.getMinutes() === 0) {
      sendExpirationReminders().catch(console.error);
    }
  }, 60000); // Check every minute

  // Run analytics update daily at 2 AM
  const analyticsUpdateInterval = setInterval(() => {
    const now = new Date();
    if (now.getHours() === 2 && now.getMinutes() === 0) {
      updateTrainingAnalytics().catch(console.error);
    }
  }, 60000); // Check every minute

  // Run certificate revocation daily at 3 AM
  const certificateRevocationInterval = setInterval(() => {
    const now = new Date();
    if (now.getHours() === 3 && now.getMinutes() === 0) {
      revokeExpiredCertificates().catch(console.error);
    }
  }, 60000); // Check every minute

  console.log("[Training Jobs] Scheduled jobs initialized");

  return {
    expirationReminderInterval,
    analyticsUpdateInterval,
    certificateRevocationInterval,
  };
}

/**
 * Stop all scheduled jobs
 */
export function stopScheduledJobs(intervals: any) {
  console.log("[Training Jobs] Stopping scheduled jobs...");
  clearInterval(intervals.expirationReminderInterval);
  clearInterval(intervals.analyticsUpdateInterval);
  clearInterval(intervals.certificateRevocationInterval);
  console.log("[Training Jobs] Scheduled jobs stopped");
}