import { Injectable, Logger } from "@nestjs/common"
import { Cron, CronExpression } from "@nestjs/schedule"
import { PayrollService } from "../payroll/payroll.service"
import { EmployeesService } from "../employees/employees.service"
import { CompanyRepository } from "../company/repositories/company.repository"
import { CompanySubscriptionRepository } from "../company/repositories/company-subscription.repository"
import { CompanyProfileRepository } from "../company/repositories/company-profile.repository"
import { appSetting, defaultCompanyName } from "../../config/app.config"
import moment from "moment"
import { PayrollCycleStatus } from "../payroll/entities/payroll-cycle.entity"
import { SubscriptionStatus } from "../company/entities/company-subscription.entity"
import { mailSubject } from "../../common/emails/email-subjects"
import { subscriptionExpiryReminderEmail } from "../../common/emails/templates/subscription-expiry-reminder"
import { aiPrompt, sendEmailNotification } from "../../utils/helpers"
import { subscriptionExpireEmail } from "src/common/emails/templates/subscription-expire"
import { projectThresholdAlertEmail } from "../../common/emails/templates/project-threshold-alert"
import { projectBudgetFullAlertEmail } from "../../common/emails/templates/project-budget-full-alert"
import { monthlyReportEmail } from "../../common/emails/templates/monthly-report"
import { ProjectRepository } from "../projects/repositories/project.repository"
import { AuthRepository } from "../auth/repositories/auth.repository"
import { TimeTrackingRepository } from "../time-tracking/repositories/time-tracking.repository"
import { ClockInRecordRepository } from "../time-tracking/repositories/clock-in-record.repository"
import { LeaveRequestRepository } from "../leave-requests/repositories/leave-request.repository"
import { DataSource, In, Repository } from "typeorm"
import { InjectRepository } from "@nestjs/typeorm"
import { NotificationService } from "../notification/notification.service"
import { notificationTitles } from "../../constants/notification.constant"
import { PartyPayment } from "../party-types/entities/party-payment.entity"
import { PartyPaymentHistory } from "../party-types/entities/party-payment-history.entity"
import { Project } from "../projects/entities/project.entity"
import { ClockInRecord } from "../time-tracking/entities/clock-in-record.entity"
import { TimeTracking } from "../time-tracking/entities/time-tracking.entity"
import { GeminiService } from "src/gemini/gemini.service"
import { MisReport } from "../mis/entities/mis-report.entity"

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

  constructor(
    private readonly payrollService: PayrollService,
    private readonly employeesService: EmployeesService,
    private readonly companyRepository: CompanyRepository,
    private readonly companySubscriptionRepository: CompanySubscriptionRepository,
    private readonly companyProfileRepository: CompanyProfileRepository,
    private readonly projectRepository: ProjectRepository,
    private readonly authRepository: AuthRepository,
    private readonly timeTrackingRepository: TimeTrackingRepository,
    private readonly clockInRecordRepository: ClockInRecordRepository,
    private readonly leaveRequestRepository: LeaveRequestRepository,
    private readonly dataSource: DataSource,
    private readonly notificationService: NotificationService,
    @InjectRepository(PartyPayment)
    private readonly partyPaymentRepository: Repository<PartyPayment>,
    @InjectRepository(PartyPaymentHistory)
    private readonly partyPaymentHistoryRepository: Repository<PartyPaymentHistory>,
    @InjectRepository(ClockInRecord)
    private readonly clockInRecordEntityRepository: Repository<ClockInRecord>,
    @InjectRepository(TimeTracking)
    private readonly timeTrackingEntityRepository: Repository<TimeTracking>,
    @InjectRepository(Project)
    private readonly projectEntityRepository: Repository<Project>,
    @InjectRepository(MisReport)
    private readonly misReportEntityRepository: Repository<MisReport>,
    private readonly geminiService: GeminiService,
  ) {}

  /**
   * Runs daily at midnight to check if payroll should be generated
   */
  @Cron(CronExpression.EVERY_DAY_AT_MIDNIGHT)
  async handlePayrollGenerationCron() {
    try {
      console.log("🔄 Running daily payroll generation check...")

      const today = moment()
      const currentDate = today.date()
      const payrollGenerateDate = appSetting.payroll_generate_date
      // Get previous month & year instead of current month
      const lastMonth = today.clone().subtract(1, "month")

      // Check if today matches the payroll generation date
      if (currentDate === payrollGenerateDate) {
        console.log(
          `📅 Today is payroll cycle creation day (${payrollGenerateDate}). Starting payroll cycle creation...`,
        )

        // Get all active companies
        const companies: any = await this.companyRepository.getByParams({
          where: { status: 1 }, // Active companies
          select: ["id", "name"],
        })

        // Handle both companies.data and direct companies array
        const companiesArray = companies.data || companies || []

        if (!companiesArray || companiesArray.length === 0) {
          console.warn(
            "⚠️ No active companies found for payroll cycle creation",
          )
          return
        }

        let totalProcessed = 0
        let totalErrors = 0

        // Create payroll cycles for each company
        for (const company of companiesArray) {
          try {
            console.log(
              `🏢 Creating payroll cycle for company: ${company.name} (ID: ${company.id})`,
            )

            const result = await this.payrollService.createPayrollCycle(
              {
                month: lastMonth.month() + 1, // Correct last month (1–12)
                year: lastMonth.year(),
                status: PayrollCycleStatus.DRAFT, // Draft status
              },
              undefined, // No token for system operation
              company.id, // System company ID
              1, // System user ID
            )

            if (result.success) {
              totalProcessed++
              console.log(
                `✅ Payroll cycle created successfully for ${company.name}`,
              )
            } else {
              // If cycle already exists, it's not an error
              if (result.message && result.message.includes("already exist")) {
                console.log(
                  `ℹ️ Payroll cycle already exists for ${company.name} - ${today.month() + 1}/${today.year()}`,
                )
              } else {
                totalErrors++
                console.error(
                  `❌ Failed to create payroll cycle for ${company.name}: ${result.message}`,
                )
              }
            }
          } catch (error) {
            totalErrors++
            console.error(
              `❌ Error creating payroll cycle for company ${company.name}:`,
              error.message,
            )
          }
        }

        console.log(
          `🎉 Payroll cycle creation completed. Companies processed: ${totalProcessed}, Errors: ${totalErrors}`,
        )
      } else {
        console.log(
          `📅 Today (${currentDate}) is not payroll cycle creation day (${payrollGenerateDate}). Skipping...`,
        )
      }
    } catch (error) {
      console.error("❌ Error in payroll generation cron:", error.message)
    }
  }

  /**
   * Runs on December 31st at 11:59 PM to process year-end carry forward
   */
  @Cron("59 23 31 12 *")
  async handleYearEndCarryForwardCron() {
    try {
      const currentYear = new Date().getFullYear()
      console.log(
        `🗓️ Running year-end carry forward processing for year ${currentYear}...`,
      )

      const result = await this.employeesService.processYearEndCarryForward(
        currentYear,
        1,
      )

      if (result.success) {
        console.log(
          `✅ Year-end carry forward completed successfully: ${result.message}`,
        )
      } else {
        console.error(`❌ Year-end carry forward failed: ${result.message}`)
      }
    } catch (error) {
      console.error("❌ Error in year-end carry forward cron:", error.message)
    }
  }

  /**
   * Runs daily at midnight to check and send subscription expiry reminders
   *
   * To test manually, run: npm run cron:run -- subscription_expiry_reminders
   */
  @Cron(CronExpression.EVERY_DAY_AT_MIDNIGHT)
  async handleSubscriptionExpiryRemindersCron() {
    try {
      console.log("📧 Running daily subscription expiry reminders check...")
      await this.checkAndSendSubscriptionExpiryReminders()
    } catch (error) {
      console.error(
        "❌ Error in subscription expiry reminders cron:",
        error.message,
      )
    }
  }

  /**
   * Runs hourly to check project budget thresholds and send alerts
   *
   * To test manually, run: npm run cron:run -- project_alerts
   */
  @Cron(CronExpression.EVERY_HOUR)
  async handleProjectAlertsCron() {
    try {
      console.log("🚨 Running hourly project budget alerts check...")
      await this.checkAndSendProjectAlerts()
    } catch (error) {
      console.error("❌ Error in project alerts cron:", error.message)
    }
  }

  /**
   * Runs on the 1st of every month at 9:00 AM to send monthly reports
   *
   * To test manually, run: npm run cron:run -- monthly_reports
   */
  @Cron("0 9 1 * *")
  async handleMonthlyReportsCron() {
    try {
      console.log("📊 Running monthly reports generation...")
      await this.generateAndSendMonthlyReports()
    } catch (error) {
      console.error("❌ Error in monthly reports cron:", error.message)
    }
  }

  /**
   * Check and send subscription expiry reminders and handle expired subscriptions
   *
   * Process:
   * 1. Send reminders at 30, 15, 7, 2, 1 days before expiry
   * 2. Send expiry email when subscription_end_date < today
   * 3. Mark is_expiry_email_send = 1 and change status to EXPIRED
   */
  async checkAndSendSubscriptionExpiryReminders() {
    try {
      console.log("🔍 Checking for subscription expiry reminders...")

      const today = moment().startOf("day")
      const reminderDays = [30, 15, 7, 2, 1] // Days before expiry to send reminders

      // Get all active company subscriptions with company details
      const subscriptions: any =
        await this.companySubscriptionRepository.getByParams({
          where: {
            status: SubscriptionStatus.ACTIVE,
            is_expiry_email_send: 0, // Only get subscriptions where expiry email hasn't been sent
          },
          relations: ["company"],
          select: [
            "id",
            "company_id",
            "subscription_end_date",
            "is_expiry_email_send",
            "company",
          ],
        })

      const subscriptionsArray = subscriptions.data || subscriptions || []

      if (!subscriptionsArray || subscriptionsArray.length === 0) {
        console.log("ℹ️ No active subscriptions found for expiry reminders")
        return
      }

      let totalProcessed = 0
      let totalEmails = 0
      let totalErrors = 0

      for (const subscription of subscriptionsArray) {
        try {
          const expiryDate = moment(subscription.subscription_end_date).startOf(
            "day",
          )
          const daysUntilExpiry = expiryDate.diff(today, "days")

          console.log(
            `📅 Checking subscription for company ${subscription.company?.name || subscription.company_id}: ${daysUntilExpiry} days until expiry`,
          )

          // Check if subscription has expired (send expiry email)
          if (daysUntilExpiry < 0) {
            console.log(
              `🚨 Subscription EXPIRED for company: ${subscription.company?.name || subscription.company_id} (${Math.abs(daysUntilExpiry)} days ago)`,
            )

            // Send expiry email and update status
            await this.sendExpiryEmailAndUpdateStatus(subscription, expiryDate)
            totalEmails++
          }
          // Check if this subscription needs a reminder (before expiry)
          else if (reminderDays.includes(daysUntilExpiry)) {
            console.log(
              `📧 Sending ${daysUntilExpiry}-day expiry reminder for company: ${subscription.company?.name || subscription.company_id}`,
            )

            // Get the appropriate email subject based on days left
            let emailSubject = ""
            switch (daysUntilExpiry) {
              case 30:
                emailSubject = mailSubject.subscriptionExpiry30Days
                break
              case 15:
                emailSubject = mailSubject.subscriptionExpiry15Days
                break
              case 7:
                emailSubject = mailSubject.subscriptionExpiry7Days
                break
              case 2:
                emailSubject = mailSubject.subscriptionExpiry2Days
                break
              case 1:
                emailSubject = mailSubject.subscriptionExpiry1Day
                break
              default:
                emailSubject = `Your subscription expires in ${daysUntilExpiry} days`
            }

            // Generate email content
            const companyName =
              subscription.company?.name || `Company ${subscription.company_id}`
            const formattedExpiryDate = expiryDate.format("MMMM DD, YYYY")
            const emailHtml = subscriptionExpiryReminderEmail(
              companyName,
              formattedExpiryDate,
              daysUntilExpiry,
            )

            // Get company email and send reminder
            const companyEmail = await this.getCompanyEmail(subscription)

            if (companyEmail) {
              // Send email notification
              await sendEmailNotification(companyEmail, emailHtml, emailSubject)

              totalEmails++
              console.log(`✅ Expiry reminder email sent to ${companyEmail}`)
            } else {
              console.warn(
                `⚠️ No email found for company ${subscription.company?.name || subscription.company_id} (ID: ${subscription.company_id})`,
              )
            }
          }

          totalProcessed++
        } catch (error) {
          totalErrors++
          console.error(
            `❌ Error processing subscription expiry reminder for company ${subscription.company_id}:`,
            error.message,
          )
        }
      }

      console.log(
        `🎉 Subscription expiry processing completed. Subscriptions processed: ${totalProcessed}, Emails sent: ${totalEmails}, Errors: ${totalErrors}`,
      )
    } catch (error) {
      console.error("❌ Error in subscription expiry reminders:", error.message)
      throw error
    }
  }

  /**
   * Send expiry email and update subscription status to EXPIRED
   */
  private async sendExpiryEmailAndUpdateStatus(
    subscription: any,
    expiryDate: any,
  ) {
    try {
      const companyName =
        subscription.company?.name || `Company ${subscription.company_id}`
      const formattedExpiryDate = expiryDate.format("MMMM DD, YYYY")

      // Create expiry email content (different from reminder)
      const emailSubject = mailSubject.subscriptionExpired
      const emailHtml = subscriptionExpireEmail(
        companyName,
        formattedExpiryDate,
      )

      // Get company email
      const companyEmail = await this.getCompanyEmail(subscription)

      if (companyEmail) {
        // Send expiry email
        await sendEmailNotification(companyEmail, emailHtml, emailSubject)
        console.log(`✅ Subscription expiry email sent to ${companyEmail}`)

        // Update subscription: mark email sent and change status to EXPIRED
        await this.companySubscriptionRepository.save(
          {
            is_expiry_email_send: 1,
            status: SubscriptionStatus.EXPIRED,
          },
          { id: subscription.id },
        )

        console.log(
          `🔒 Subscription ${subscription.id} marked as EXPIRED and expiry email sent`,
        )
      } else {
        // Even if no email, still update status to EXPIRED
        await this.companySubscriptionRepository.save(
          {
            is_expiry_email_send: 1,
            status: SubscriptionStatus.EXPIRED,
          },
          { id: subscription.id },
        )

        console.warn(
          `⚠️ No email found for company ${companyName}, but subscription ${subscription.id} marked as EXPIRED`,
        )
      }
    } catch (error) {
      console.error(
        `❌ Error sending expiry email for subscription ${subscription.id}:`,
        error.message,
      )
      throw error
    }
  }

  /**
   * Get company email from subscription or company profile
   */
  private async getCompanyEmail(subscription: any): Promise<string | null> {
    // Try to get email from company relation first
    let companyEmail = subscription.company?.email

    // If company email is not available, try to get company profile email
    if (!companyEmail && subscription.company_id) {
      const companyProfile: any =
        await this.companyProfileRepository.getByParams({
          where: { company_id: subscription.company_id },
          select: ["email"],
          findOne: true,
        })

      if (companyProfile?.email) {
        companyEmail = companyProfile.email
      }
    }

    return companyEmail || null
  }

  /**
   * Check and send project budget threshold and 100% alerts
   */
  async checkAndSendProjectAlerts() {
    try {
      console.log("🔍 Checking for project budget alerts...")

      // Get all active projects with their time tracking data
      const projects: any = await this.projectRepository.getByParams({
        relations: ["company", "client"],
        select: [
          "id",
          "name",
          "budget",
          "remaining_budget",
          "threshold_percentage",
          "company_id",
          "client_id",
          "company",
          "client",
          "is_threshold_mail_sent",
          "is_over_budget_mail_sent",
        ],
      })

      const projectsArray = projects.data || projects || []

      if (!projectsArray || projectsArray.length === 0) {
        console.log("ℹ️ No active projects found for budget alerts")
        return
      }

      let totalProcessed = 0
      let totalAlerts = 0
      let totalErrors = 0

      for (const project of projectsArray) {
        try {
          // Calculate spent amount from time tracking
          const spentAmount = project.budget - project.remaining_budget
          const budgetPercentage = (spentAmount / project.budget) * 100
          const thresholdPercentage = project.threshold_percentage || 85

          console.log(
            `📊 Checking project ${project.name}: ${budgetPercentage.toFixed(1)}% of budget used`,
          )

          // Check if project has exceeded 100% budget
          if (budgetPercentage >= 100) {
            if (project.is_over_budget_mail_sent === 0) {
              console.log(
                `🚨 Project ${project.name} has exceeded 100% of budget - sending alert`,
              )
              await this.sendProjectBudgetFullAlert(project, spentAmount)
              totalAlerts++
            } else {
              console.log(
                `ℹ️ Project ${project.name} over budget alert already sent - skipping`,
              )
            }
          }
          // Check if project has reached threshold but not yet 100%
          else if (budgetPercentage >= thresholdPercentage) {
            if (project.is_threshold_mail_sent === 0) {
              console.log(
                `⚠️ Project ${project.name} has reached ${budgetPercentage.toFixed(1)}% threshold - sending alert`,
              )
              await this.sendProjectThresholdAlert(project, spentAmount)
              totalAlerts++
            } else {
              console.log(
                `ℹ️ Project ${project.name} threshold alert already sent - skipping`,
              )
            }
          }

          totalProcessed++
        } catch (error) {
          totalErrors++
          console.error(
            `❌ Error processing project alert for ${project.name}:`,
            error.message,
          )
        }
      }

      console.log(
        `🎉 Project alerts processing completed. Projects processed: ${totalProcessed}, Alerts sent: ${totalAlerts}, Errors: ${totalErrors}`,
      )
    } catch (error) {
      console.error("❌ Error in project alerts:", error.message)
      throw error
    }
  }

  /**
   * Send project threshold alert email to super admin
   */
  private async sendProjectThresholdAlert(project: any, spentAmount: number) {
    try {
      // Get super admin email for the company
      const superAdminEmail = await this.getSuperAdminEmail(project.company_id)

      if (!superAdminEmail) {
        console.warn(
          `⚠️ No super admin found for company ${project.company?.name || project.company_id}`,
        )
        return
      }

      const emailSubject = mailSubject.projectThresholdAlert
      const emailHtml = projectThresholdAlertEmail(
        project.name,
        project.client?.name || "N/A",
        project.budget,
        spentAmount,
        project.threshold_percentage || 85,
        project.remaining_budget,
      )

      await sendEmailNotification(superAdminEmail, emailHtml, emailSubject)

      // Send push notification to admin users
      try {
        await this.sendProjectBudgetNotifications(
          project,
          "threshold",
          spentAmount,
          project.threshold_percentage || 85,
        )
      } catch (notificationError) {
        console.error(
          "❌ Error sending threshold push notifications:",
          notificationError.message,
        )
      }

      // Update the flag to prevent duplicate emails
      await this.projectRepository.save({
        id: project.id,
        is_threshold_mail_sent: 1,
      })

      console.log(
        `✅ Threshold alert email sent to ${superAdminEmail} for project ${project.name}`,
      )
    } catch (error) {
      console.error(
        `❌ Error sending threshold alert for project ${project.name}:`,
        error.message,
      )
      throw error
    }
  }

  /**
   * Send project budget full alert email to super admin
   */
  private async sendProjectBudgetFullAlert(project: any, spentAmount: number) {
    try {
      // Get super admin email for the company
      const superAdminEmail = await this.getSuperAdminEmail(project.company_id)

      if (!superAdminEmail) {
        console.warn(
          `⚠️ No super admin found for company ${project.company?.name || project.company_id}`,
        )
        return
      }

      const overBudgetAmount = spentAmount - project.budget
      const emailSubject = mailSubject.projectBudgetFullAlert
      const emailHtml = projectBudgetFullAlertEmail(
        project.name,
        project.client?.name || "N/A",
        project.budget,
        spentAmount,
        overBudgetAmount,
      )

      await sendEmailNotification(superAdminEmail, emailHtml, emailSubject)

      // Send push notification to admin users
      try {
        await this.sendProjectBudgetNotifications(
          project,
          "over_budget",
          spentAmount,
          100,
        )
      } catch (notificationError) {
        console.error(
          "❌ Error sending over-budget push notifications:",
          notificationError.message,
        )
      }

      // Update the flag to prevent duplicate emails
      await this.projectRepository.save({
        id: project.id,
        is_over_budget_mail_sent: 1,
      })

      console.log(
        `✅ Budget full alert email sent to ${superAdminEmail} for project ${project.name}`,
      )
    } catch (error) {
      console.error(
        `❌ Error sending budget full alert for project ${project.name}:`,
        error.message,
      )
      throw error
    }
  }

  /**
   * Send push notifications for project budget alerts
   */
  private async sendProjectBudgetNotifications(
    project: any,
    alertType: "threshold" | "over_budget",
    spentAmount: number,
    percentage: number,
  ) {
    try {
      // Get admin users for the company
      const adminUsers: any = await this.dataSource.query(
        `
        SELECT DISTINCT u.id as employee_id
        FROM users u
        INNER JOIN roles r ON u.role_id = r.id
        WHERE u.company_id = $1 
          AND u.status = 1 
          AND u.deleted_at IS NULL
          AND (r.name ILIKE '%admin%' OR r.name ILIKE '%manager%')
      `,
        [project.company_id],
      )

      const adminEmployeeIds = adminUsers.map((admin: any) => admin.employee_id)

      if (adminEmployeeIds.length === 0) {
        console.log(
          "⚠️ No admin users found to notify about project budget alert",
        )
        return
      }

      const clientName = project.client?.name || "N/A"
      const budgetPercentage = (spentAmount / project.budget) * 100

      let notificationTitle: string
      let notificationMessage: string

      if (alertType === "threshold") {
        notificationTitle = notificationTitles.PROJECT_BUDGET_ALERT
        notificationMessage = `Project "${project.name}" has reached ${budgetPercentage.toFixed(1)}% of its budget (${percentage}% threshold). Client: ${clientName}. Budget: $${project.budget.toLocaleString()}, Spent: $${spentAmount.toLocaleString()}.`
      } else {
        notificationTitle = notificationTitles.PROJECT_BUDGET_ALERT
        const overBudgetAmount = spentAmount - project.budget
        notificationMessage = `Project "${project.name}" has exceeded its budget by $${overBudgetAmount.toLocaleString()}. Client: ${clientName}. Budget: $${project.budget.toLocaleString()}, Spent: $${spentAmount.toLocaleString()}.`
      }

      // Send notification to each admin individually
      for (const adminEmployeeId of adminEmployeeIds) {
        await this.notificationService.sendAndSaveNotification({
          title: notificationTitle,
          description: notificationMessage,
          company_id: project.company_id,
          employee_id: adminEmployeeId,
          type: "PROJECT",
          data: {
            project_id: project.id,
            project_name: project.name,
            client_name: clientName,
            budget: project.budget.toString(),
            spent_amount: spentAmount.toString(),
            percentage: budgetPercentage.toFixed(1),
            type: alertType,
          },
        })
      }

      console.log(
        `📱 Project budget ${alertType} notifications sent to ${adminEmployeeIds.length} admin users`,
      )
    } catch (error) {
      console.error(
        "❌ Error in sendProjectBudgetNotifications:",
        error.message,
      )
      throw error
    }
  }

  /**
   * Get super admin email for a company
   */
  private async getSuperAdminEmail(companyId: number): Promise<string | null> {
    try {
      const superAdmin: any = await this.authRepository.getByParams({
        where: {
          company_id: companyId,
          slug: "super_admin",
          status: 1,
        },
        select: ["email"],
        findOne: true,
      })

      return superAdmin?.email || null
    } catch (error) {
      console.error(
        `❌ Error getting super admin email for company ${companyId}:`,
        error.message,
      )
      return null
    }
  }

  /**
   * Generate and send monthly reports to all company super admins
   */
  async generateAndSendMonthlyReports() {
    try {
      console.log("📊 Generating monthly reports...")

      const today = moment()
      const lastMonth = today.clone().subtract(1, "month")
      const reportMonth = lastMonth.format("MMMM")
      const reportYear = lastMonth.year()
      const startDate = lastMonth.startOf("month").format("YYYY-MM-DD")
      const endDate = lastMonth.endOf("month").format("YYYY-MM-DD")

      console.log(
        `📅 Generating reports for ${reportMonth} ${reportYear} (${startDate} to ${endDate})`,
      )

      // Get all active companies
      const companies: any = await this.companyRepository.getByParams({
        where: { status: 1 },
        select: ["id", "name"],
      })

      const companiesArray = companies || []

      if (!companiesArray || companiesArray.length === 0) {
        console.log("ℹ️ No active companies found for monthly reports")
        return
      }

      let totalProcessed = 0
      let totalReports = 0
      let totalErrors = 0

      for (const company of companiesArray) {
        try {
          console.log(
            `🏢 Generating monthly report for company: ${company.name} (ID: ${company.id})`,
          )

          await this.generateCompanyMonthlyReport(
            company,
            reportMonth,
            reportYear,
            startDate,
            endDate,
          )

          totalReports++
          totalProcessed++
        } catch (error) {
          totalErrors++
          console.error(
            `❌ Error generating monthly report for company ${company.name}:`,
            error.message,
          )
        }
      }

      console.log(
        `🎉 Monthly reports generation completed. Companies processed: ${totalProcessed}, Reports sent: ${totalReports}, Errors: ${totalErrors}`,
      )
    } catch (error) {
      console.error("❌ Error in monthly reports generation:", error.message)
      throw error
    }
  }

  /**
   * Generate and send monthly report for a specific company
   */
  private async generateCompanyMonthlyReport(
    company: any,
    reportMonth: string,
    reportYear: number,
    startDate: string,
    endDate: string,
  ) {
    try {
      // Get super admin email for the company
      const superAdminEmail = await this.getSuperAdminEmail(company.id)

      if (!superAdminEmail) {
        console.warn(
          `⚠️ No super admin found for company ${company.name}, skipping report`,
        )
        return
      }

      // Generate project summary
      const projectSummaries = await this.getProjectSummaryForMonth(
        company.id,
        startDate,
        endDate,
      )

      // Generate employee summary
      const employeeSummaries = await this.getEmployeeSummaryForMonth(
        company.id,
        startDate,
        endDate,
      )

      // Generate leave report
      const leaveReports = await this.getLeaveReportForMonth(
        company.id,
        startDate,
        endDate,
      )

      // Calculate totals
      const totalProjectHours = projectSummaries.reduce(
        (sum, p) => sum + p.totalHours,
        0,
      )
      const totalProjectCost = projectSummaries.reduce(
        (sum, p) => sum + p.totalCost,
        0,
      )
      const totalEmployeeHours = employeeSummaries.reduce(
        (sum, e) => sum + e.totalHours,
        0,
      )
      const totalEmployeeCost = employeeSummaries.reduce(
        (sum, e) => sum + e.totalCost,
        0,
      )
      const totalLeaves = leaveReports.reduce(
        (sum, l) => sum + l.totalLeaves,
        0,
      )

      // Generate and send email
      const emailSubject = mailSubject.monthlyProjectReport
      const emailHtml = monthlyReportEmail(
        company.name,
        reportMonth,
        reportYear,
        projectSummaries,
        employeeSummaries,
        leaveReports,
        totalProjectHours,
        totalProjectCost,
        totalEmployeeHours,
        totalEmployeeCost,
        totalLeaves,
      )

      await sendEmailNotification(superAdminEmail, emailHtml, emailSubject)
      console.log(
        `✅ Monthly report email sent to ${superAdminEmail} for company ${company.name}`,
      )
    } catch (error) {
      console.error(
        `❌ Error generating monthly report for company ${company.name}:`,
        error.message,
      )
      throw error
    }
  }

  /**
   * Get project summary data for the month
   */
  private async getProjectSummaryForMonth(
    companyId: number,
    startDate: string,
    endDate: string,
  ) {
    try {
      const query = `
        SELECT 
          p.name as "projectName",
          COALESCE(c.name, 'No Client') as "clientName",
          COALESCE(SUM(tt.total_minutes), 0) as "totalMinutes",
          COALESCE(SUM(tt.cost), 0) as "totalCost"
        FROM projects p
        LEFT JOIN clients c ON (p.client_id = c.id AND c.deleted_at IS NULL)
        LEFT JOIN time_tracking tt ON (
          p.id = tt.project_id 
          AND tt.deleted_at IS NULL 
          AND tt.end_time IS NOT NULL
          AND tt.start_time >= $1::timestamp
          AND tt.start_time < ($2::date + INTERVAL '1 day')::timestamp
        )
        WHERE p.company_id = $3 
          AND p.deleted_at IS NULL
        GROUP BY p.id, p.name, c.name
        HAVING COALESCE(SUM(tt.total_minutes), 0) > 0
        ORDER BY COALESCE(SUM(tt.cost), 0) DESC
      `

      const results = await this.dataSource.query(query, [
        startDate,
        endDate,
        companyId,
      ])

      return results.map((row: any) => ({
        projectName: row.projectName,
        clientName: row.clientName,
        totalHours: parseFloat(row.totalMinutes) / 60 || 0,
        totalCost: parseFloat(row.totalCost) || 0,
      }))
    } catch (error) {
      console.error("❌ Error getting project summary:", error.message)
      return []
    }
  }

  /**
   * Get employee summary data for the month
   */
  private async getEmployeeSummaryForMonth(
    companyId: number,
    startDate: string,
    endDate: string,
  ) {
    try {
      const query = `
        SELECT 
          CONCAT(e.first_name, ' ', COALESCE(e.last_name, '')) as "employeeName",
          COALESCE(SUM(tt.total_minutes), 0) as "totalMinutes",
          COALESCE(SUM(tt.cost), 0) as "totalCost",
          COALESCE(COUNT(DISTINCT lr.id), 0) as "approvedLeaves"
        FROM employees e
        LEFT JOIN time_tracking tt ON (
          e.id = tt.employee_id 
          AND tt.deleted_at IS NULL 
          AND tt.end_time IS NOT NULL
          AND tt.start_time >= $1::timestamp
          AND tt.start_time < ($2::date + INTERVAL '1 day')::timestamp
        )
        LEFT JOIN leave_requests lr ON (
          e.id = lr.employee_id 
          AND lr.deleted_at IS NULL 
          AND lr.status = 'approved'
          AND (
            (lr.from_date >= $3::date AND lr.from_date <= $4::date)
            OR (lr.to_date >= $5::date AND lr.to_date <= $6::date)
            OR (lr.from_date <= $7::date AND lr.to_date >= $8::date)
          )
        )
        WHERE e.company_id = $9 
          AND e.deleted_at IS NULL 
          AND e.status = 1
        GROUP BY e.id, e.first_name, e.last_name
        ORDER BY COALESCE(SUM(tt.cost), 0) DESC
      `

      const results = await this.dataSource.query(query, [
        startDate,
        endDate,
        startDate,
        endDate,
        startDate,
        endDate,
        startDate,
        endDate,
        companyId,
      ])

      return results.map((row: any) => ({
        employeeName: row.employeeName,
        totalHours: parseFloat(row.totalMinutes) / 60 || 0,
        totalCost: parseFloat(row.totalCost) || 0,
        approvedLeaves: parseInt(row.approvedLeaves) || 0,
      }))
    } catch (error) {
      console.error("❌ Error getting employee summary:", error.message)
      return []
    }
  }

  /**
   * Get leave report data for the month
   */
  private async getLeaveReportForMonth(
    companyId: number,
    startDate: string,
    endDate: string,
  ) {
    try {
      const query = `
        SELECT 
          CONCAT(e.first_name, ' ', COALESCE(e.last_name, '')) as "employeeName",
          COALESCE(SUM(lr.duration_days), 0) as "totalLeaves"
        FROM employees e
        INNER JOIN leave_requests lr ON (
          e.id = lr.employee_id 
          AND lr.deleted_at IS NULL 
          AND lr.status = 'approved'
          AND (
            (lr.from_date >= $1::date AND lr.from_date <= $2::date)
            OR (lr.to_date >= $3::date AND lr.to_date <= $4::date)
            OR (lr.from_date <= $5::date AND lr.to_date >= $6::date)
          )
        )
        WHERE e.company_id = $7 
          AND e.deleted_at IS NULL 
          AND e.status = 1
        GROUP BY e.id, e.first_name, e.last_name
        HAVING COALESCE(SUM(lr.duration_days), 0) > 0
        ORDER BY COALESCE(SUM(lr.duration_days), 0) DESC
      `

      const results = await this.dataSource.query(query, [
        startDate,
        endDate,
        startDate,
        endDate,
        startDate,
        endDate,
        companyId,
      ])

      return results.map((row: any) => ({
        employeeName: row.employeeName,
        totalLeaves: parseFloat(row.totalLeaves) || 0,
      }))
    } catch (error) {
      console.error("❌ Error getting leave report:", error.message)
      return []
    }
  }

  /**
   * Create payroll cycle for a specific company and month (for manual execution)
   */
  async generatePayrollForCompany(
    companyId: number,
    month: number,
    year: number,
  ) {
    try {
      this.logger.log(
        `🏢 Creating payroll cycle for company ${companyId}, ${month}/${year}`,
      )

      const result = await this.payrollService.createPayrollCycle(
        {
          month,
          year,
          status: PayrollCycleStatus.DRAFT, // Draft status
        },
        undefined, // No token for system operation
        companyId, // System company ID
        1, // System user ID
      )

      if (result.success) {
        this.logger.log(`✅ Payroll cycle created successfully`)
        return {
          success: true,
          message: `Payroll cycle created for ${month}/${year}`,
          data: (result as any).data,
        }
      } else {
        this.logger.error(
          `❌ Failed to create payroll cycle: ${result.message}`,
        )
        return {
          success: false,
          message: result.message,
        }
      }
    } catch (error) {
      this.logger.error(`❌ Error creating payroll cycle:`, error.message)
      return {
        success: false,
        message: error.message,
      }
    }
  }

  /**
   * Process year-end carry forward for a specific year (for manual execution)
   */
  async processYearEndCarryForward(year: number) {
    try {
      this.logger.log(`🗓️ Processing year-end carry forward for year ${year}`)

      const result = await this.employeesService.processYearEndCarryForward(
        year,
        1,
      )

      if (result.success) {
        this.logger.log(`✅ Year-end carry forward completed successfully`)
        return {
          success: true,
          message: result.message,
          data: result.data,
        }
      } else {
        this.logger.error(`❌ Year-end carry forward failed: ${result.message}`)
        return {
          success: false,
          message: result.message,
        }
      }
    } catch (error) {
      this.logger.error(
        `❌ Error processing year-end carry forward:`,
        error.message,
      )
      return {
        success: false,
        message: error.message,
      }
    }
  }

  /**
   * Get cron status and next run information
   */
  getCronStatus() {
    const today = moment()
    const currentDate = today.date()
    const currentMonth = today.month() + 1 // moment months are 0-indexed
    const payrollGenerateDate = appSetting.payroll_generate_date

    let nextPayrollRunDate: moment.Moment

    if (currentDate < payrollGenerateDate) {
      // Next run is this month
      nextPayrollRunDate = today.clone().date(payrollGenerateDate)
    } else {
      // Next run is next month
      nextPayrollRunDate = today
        .clone()
        .add(1, "month")
        .date(payrollGenerateDate)
    }

    // Calculate next year-end carry forward date (December 31st)
    let nextCarryForwardDate: moment.Moment
    if (currentMonth === 12 && currentDate === 31) {
      // Today is December 31st, next run is next year
      nextCarryForwardDate = today.clone().add(1, "year").month(11).date(31)
    } else if (currentMonth === 12 && currentDate < 31) {
      // This year, December 31st
      nextCarryForwardDate = today.clone().month(11).date(31)
    } else {
      // Next year, December 31st
      nextCarryForwardDate = today.clone().add(1, "year").month(11).date(31)
    }

    return {
      payroll: {
        generate_date: payrollGenerateDate,
        current_date: currentDate,
        is_payroll_day: currentDate === payrollGenerateDate,
        next_run_date: nextPayrollRunDate.format("YYYY-MM-DD"),
        days_until_next_run: nextPayrollRunDate.diff(today, "days"),
      },
      carry_forward: {
        is_carry_forward_day: currentMonth === 12 && currentDate === 31,
        next_run_date: nextCarryForwardDate.format("YYYY-MM-DD"),
        days_until_next_run: nextCarryForwardDate.diff(today, "days"),
      },
    }
  }

  /**
   * Runs daily at midnight to process monthly party payments
   * Creates payment history records and deducts from project budgets
   */
  @Cron(CronExpression.EVERY_DAY_AT_MIDNIGHT)
  async handleMonthlyPartyPayments() {
    try {
      this.logger.log("Starting monthly party payments processing...")

      const today = moment().startOf("day")

      // Fetch all active monthly payments
      const activeMonthlyPayments = await this.partyPaymentRepository
        .createQueryBuilder("pp")
        .leftJoinAndSelect("pp.project", "project")
        .where("pp.payment_type = :paymentType", { paymentType: "MONTHLY" })
        .andWhere("pp.start_date <= :today", {
          today: today.format("YYYY-MM-DD"),
        })
        .andWhere("(pp.end_date IS NULL OR pp.end_date >= :today)", {
          today: today.format("YYYY-MM-DD"),
        })
        .andWhere("pp.deleted_at IS NULL")
        .andWhere("project.deleted_at IS NULL")
        .getMany()

      this.logger.log(
        `Found ${activeMonthlyPayments.length} active monthly payments to process`,
      )

      let processedCount = 0
      let errorCount = 0

      for (const payment of activeMonthlyPayments) {
        try {
          await this.processMonthlyPayment(payment, today)
          processedCount++
        } catch (error) {
          errorCount++
          this.logger.error(
            `Error processing payment ID ${payment.id}: ${error.message}`,
          )
        }
      }

      this.logger.log(
        `Monthly payments processing completed. Processed: ${processedCount}, Errors: ${errorCount}`,
      )
    } catch (error) {
      this.logger.error(
        `Error in monthly party payments cron: ${error.message}`,
      )
    }
  }

  /**
   * Process a single monthly payment and create history records
   */
  private async processMonthlyPayment(
    payment: PartyPayment,
    today: moment.Moment,
  ) {
    const startDate = moment(payment.start_date).startOf("day")
    const endDate = payment.end_date
      ? moment(payment.end_date).startOf("day")
      : null

    // Calculate all billing months that should have been processed by now
    const billingMonths = this.calculateBillingMonths(startDate, endDate, today)

    for (const billingMonth of billingMonths) {
      try {
        // Check if history already exists (idempotency check)
        const existingHistory =
          await this.partyPaymentHistoryRepository.findOne({
            where: {
              party_payment_id: payment.id,
              billing_month: billingMonth.toDate(),
            },
          })

        if (existingHistory) {
          // Already processed, skip
          continue
        }

        // Process this billing month in a transaction
        await this.processMonthlyPaymentInTransaction(payment, billingMonth)

        this.logger.log(
          `Processed payment ID ${payment.id} for billing month ${billingMonth.format("YYYY-MM-DD")}`,
        )
      } catch (error) {
        // Log error but continue with other months
        this.logger.error(
          `Error processing payment ID ${payment.id} for month ${billingMonth.format("YYYY-MM-DD")}: ${error.message}`,
        )
      }
    }
  }

  /**
   * Calculate all billing months that should be processed
   */
  private calculateBillingMonths(
    startDate: moment.Moment,
    endDate: moment.Moment | null,
    today: moment.Moment,
  ): moment.Moment[] {
    const billingMonths: moment.Moment[] = []

    // Start from the month after the start date
    const currentBillingMonth = startDate.clone().add(1, "month")

    // Continue until we reach today or end_date (whichever comes first)
    while (currentBillingMonth.isSameOrBefore(today, "day")) {
      // If there's an end date, don't go beyond it
      if (endDate && currentBillingMonth.isAfter(endDate, "day")) {
        break
      }

      billingMonths.push(currentBillingMonth.clone())
      currentBillingMonth.add(1, "month")
    }

    return billingMonths
  }

  /**
   * Process a single monthly payment in a database transaction
   */
  private async processMonthlyPaymentInTransaction(
    payment: PartyPayment,
    billingMonth: moment.Moment,
  ) {
    const queryRunner = this.dataSource.createQueryRunner()
    await queryRunner.connect()
    await queryRunner.startTransaction()

    try {
      // Fetch project with lock to prevent concurrent modifications
      const project = await queryRunner.manager.findOne(Project, {
        where: { id: payment.project_id },
        lock: { mode: "pessimistic_write" },
      })

      if (!project) {
        throw new Error(`Project not found for payment ID ${payment.id}`)
      }

      // Check if project has sufficient budget
      const currentBudget = parseFloat(project.remaining_budget.toString())
      const paymentAmount = parseFloat(payment.amount.toString())

      if (currentBudget < paymentAmount) {
        this.logger.warn(
          `Insufficient budget for payment ID ${payment.id}. Required: ${paymentAmount}, Available: ${currentBudget}`,
        )

        // Still create history record but mark it as failed/pending
        await this.createPaymentHistory(queryRunner, payment, billingMonth)
        await queryRunner.commitTransaction()
        return
      }

      // Create payment history record
      await this.createPaymentHistory(queryRunner, payment, billingMonth)

      // Deduct amount from project budget
      const newBudget = currentBudget - paymentAmount

      // Update project using queryRunner's manager to ensure it's within the transaction
      project.remaining_budget = newBudget
      project.updated_at = new Date()

      await queryRunner.manager.save(Project, project)

      // Check if budget is below threshold and send notification
      await this.checkBudgetThreshold(project, newBudget, queryRunner)

      await queryRunner.commitTransaction()
    } catch (error) {
      await queryRunner.rollbackTransaction()
      throw error
    } finally {
      await queryRunner.release()
    }
  }

  /**
   * Create payment history record
   */
  private async createPaymentHistory(
    queryRunner: any,
    payment: PartyPayment,
    billingMonth: moment.Moment,
  ) {
    const historyRecord = {
      party_payment_id: payment.id,
      party_id: payment.party_id,
      party_type: payment.party_type,
      project_id: payment.project_id,
      company_id: payment.company_id,
      amount: payment.amount,
      billing_month: billingMonth.toDate(),
      created_by: null, // System generated
      created_at: new Date(),
      updated_at: new Date(),
    }

    try {
      await queryRunner.manager.insert(PartyPaymentHistory, historyRecord)
    } catch (error) {
      // Handle unique constraint violation (duplicate entry)
      if (error.code === "ER_DUP_ENTRY" || error.code === "23505") {
        this.logger.warn(
          `Duplicate payment history entry detected for payment ID ${payment.id}, billing month ${billingMonth.format("YYYY-MM-DD")}`,
        )
        return // Silently ignore duplicates
      }
      throw error
    }
  }

  /**
   * Check budget threshold and send notifications if needed
   */
  private async checkBudgetThreshold(
    project: Project,
    newBudget: number,
    queryRunner: any,
  ) {
    const originalBudget = parseFloat(project.budget.toString())
    const thresholdPercentage = project.threshold_percentage || 85
    const thresholdAmount = (originalBudget * thresholdPercentage) / 100
    const usedBudget = originalBudget - newBudget
    const usedPercentage = (usedBudget / originalBudget) * 100

    // Send threshold alert if budget crosses threshold
    if (
      newBudget <= originalBudget - thresholdAmount &&
      project.remaining_budget > originalBudget - thresholdAmount
    ) {
      try {
        // Get company and project owner details for notification
        const company = await queryRunner.manager.findOne("companies", {
          where: { id: project.company_id },
        })

        if (company) {
          // Send threshold notification
          await this.notificationService.createNotification({
            title: notificationTitles.PROJECT_BUDGET_ALERT,
            description: `Project "${project.name}" has crossed ${thresholdPercentage}% budget threshold. Used: ${usedPercentage.toFixed(1)}%`,
            company_id: project.company_id,
            employee_id: project.created_by,
          })
        }
      } catch (notificationError) {
        this.logger.error(
          `Error sending budget threshold notification: ${notificationError.message}`,
        )
      }
    }

    // Send budget exhausted alert if budget is fully used
    if (newBudget <= 0 && project.remaining_budget > 0) {
      try {
        await this.notificationService.createNotification({
          title: "Project Budget Exhausted",
          description: `Project "${project.name}" budget has been completely exhausted.`,
          company_id: project.company_id,
          employee_id: project.created_by,
        })
      } catch (notificationError) {
        this.logger.error(
          `Error sending budget exhausted notification: ${notificationError.message}`,
        )
      }
    }
  }

  /**
   * Auto Clock-Out and Stop Time Tracking at 11:57 PM IST daily
   * Runs at 6:27 PM UTC (11:57 PM IST - 5:30 hours offset)
   */
  @Cron("27 18 * * *")
  async handleAutoClockOutAndStopTimeTracking() {
    try {
      this.logger.log(
        "🕚 Running auto clock-out and stop time tracking at 11:57 PM IST...",
      )

      const today = moment.tz("Asia/Kolkata")
      const startOfDay = today.clone().startOf("day").utc().toDate()
      const endOfDay = today.clone().endOf("day").utc().toDate()
      const clockOutTime = today
        .clone()
        .set({ hour: 23, minute: 57, second: 59 })
        .utc()
        .toDate()

      // Task 1: Auto Clock-Out for ClockInRecord
      await this.autoClockOutRecords(startOfDay, endOfDay, clockOutTime)

      // Task 2: Auto Stop Time Tracking Activities
      await this.autoStopTimeTrackingActivities(
        startOfDay,
        endOfDay,
        clockOutTime,
      )

      this.logger.log(
        "✅ Auto clock-out and stop time tracking completed successfully",
      )
    } catch (error) {
      this.logger.error(`❌ Error in auto clock-out process: ${error.message}`)
    }
  }

  /**
   * Auto clock-out open ClockInRecord entries
   */
  private async autoClockOutRecords(
    startOfDay: Date,
    endOfDay: Date,
    clockOutTime: Date,
  ) {
    try {
      // Find all open clock-in records for today
      const openClockInRecords = await this.clockInRecordEntityRepository
        .createQueryBuilder("cir")
        .leftJoinAndSelect("cir.employee", "employee")
        .where("cir.clock_in >= :startOfDay", { startOfDay })
        .andWhere("cir.clock_in <= :endOfDay", { endOfDay })
        .andWhere("cir.clock_out IS NULL")
        .andWhere("cir.deleted_at IS NULL")
        .getMany()

      this.logger.log(
        `Found ${openClockInRecords.length} open clock-in records to auto clock-out`,
      )

      for (const record of openClockInRecords) {
        try {
          // Calculate duration in minutes
          const clockInTime = new Date(record.clock_in)
          const durationMinutes = Math.floor(
            (clockOutTime.getTime() - clockInTime.getTime()) / (1000 * 60),
          )

          // Update the record
          await this.clockInRecordEntityRepository.update(record.id, {
            clock_out: clockOutTime,
            duration: durationMinutes,
            updated_by: null, // System update
          })

          this.logger.log(
            `Auto clocked-out employee ${record.employee?.first_name} ${record.employee?.last_name} (ID: ${record.id}) - Duration: ${durationMinutes} minutes`,
          )
        } catch (recordError) {
          this.logger.error(
            `Error auto clocking-out record ID ${record.id}: ${recordError.message}`,
          )
        }
      }
    } catch (error) {
      this.logger.error(`Error in autoClockOutRecords: ${error.message}`)
    }
  }

  /**
   * Auto stop open time tracking activities
   */
  private async autoStopTimeTrackingActivities(
    startOfDay: Date,
    endOfDay: Date,
    endTime: Date,
  ) {
    try {
      // Find all open time tracking records for today
      const openTimeTrackingRecords = await this.timeTrackingEntityRepository
        .createQueryBuilder("tt")
        .leftJoinAndSelect("tt.employee", "employee")
        .leftJoinAndSelect("tt.project", "project")
        .where("tt.start_time >= :startOfDay", { startOfDay })
        .andWhere("tt.start_time <= :endOfDay", { endOfDay })
        .andWhere("tt.end_time IS NULL")
        .andWhere("tt.deleted_at IS NULL")
        .getMany()

      this.logger.log(
        `Found ${openTimeTrackingRecords.length} open time tracking records to auto stop`,
      )

      for (const record of openTimeTrackingRecords) {
        try {
          // Calculate time and cost using the same logic as stopTimeTracking
          const { total_minutes, cost } = await this.calculateTimeAndCost(
            record.start_time,
            endTime,
            record.employee_id,
          )

          // Update the record
          await this.timeTrackingEntityRepository.update(record.id, {
            end_time: endTime,
            total_minutes: total_minutes,
            cost: cost,
            updated_by: null, // System update
          })

          this.logger.log(
            `Auto stopped time tracking for employee ${record.employee?.first_name} ${record.employee?.last_name} (ID: ${record.id}) - Project: ${record.project?.name} - Duration: ${total_minutes} minutes`,
          )
        } catch (recordError) {
          this.logger.error(
            `Error auto stopping time tracking record ID ${record.id}: ${recordError.message}`,
          )
        }
      }
    } catch (error) {
      this.logger.error(
        `Error in autoStopTimeTrackingActivities: ${error.message}`,
      )
    }
  }

  /**
   * Calculate time and cost for time tracking (copied from TimeTrackingService)
   */
  private async calculateTimeAndCost(
    startTime: Date,
    endTime: Date,
    employeeId: number,
  ): Promise<{ total_minutes: number; cost: number }> {
    try {
      // Calculate total minutes
      const startMoment = moment(startTime)
      const endMoment = moment(endTime)
      const total_minutes = endMoment.diff(startMoment, "minutes")

      // Get employee salary for cost calculation
      const employee: any = await this.employeesService.findOne(employeeId)
      if (!employee || !employee.data) {
        return { total_minutes, cost: 0 }
      }

      const employeeData = employee.data
      const gross_salary = employeeData.gross_salary || 0

      if (gross_salary <= 0) {
        return { total_minutes, cost: 0 }
      }

      // Get company settings for working hours calculation
      const companySettings: any = await this.companyRepository.getByParams({
        where: { id: employeeData.company_id },
        findOne: true,
      })

      const workingHoursPerDay = companySettings?.working_hours_per_day ?? 8
      const workingDaysPerMonth = companySettings?.working_days_per_month ?? 22

      const totalWorkingMinutesPerMonth =
        workingHoursPerDay > 0 && workingDaysPerMonth > 0
          ? workingHoursPerDay * workingDaysPerMonth * 60
          : 0

      if (totalWorkingMinutesPerMonth <= 0) {
        return { total_minutes, cost: 0 }
      }

      const perMinuteRate = gross_salary / totalWorkingMinutesPerMonth
      const calculatedCost = perMinuteRate * total_minutes

      const cost = Number.isFinite(calculatedCost)
        ? Math.round(calculatedCost * 100) / 100
        : 0

      return { total_minutes, cost }
    } catch (error) {
      this.logger.error(`Error calculating time and cost: ${error.message}`)
      return { total_minutes: 0, cost: 0 }
    }
  }

  /**
   * Runs at 6:15 AM IST (12:45 AM UTC)
   * On 1st of every month
   */
  @Cron("45 0 1 * *")
  async handleAutoFillMisReportOfPreviousMonth() {
    try {
      this.logger.log(
        "Running auto fill MIS runs at 6:15 AM IST (12:45 AM UTC)",
      )

      const lastMonthDate = moment().subtract(1, "month")

      const lastMonth = lastMonthDate.month() + 1
      const year = lastMonthDate.year()

      const startDateOfLastMonth = lastMonthDate
        .clone()
        .startOf("month")
        .format("YYYY-MM-DD")
      const endDateOfLastMonth = lastMonthDate
        .clone()
        .endOf("month")
        .format("YYYY-MM-DD")

      const company: any = await this.companyRepository.getByParams({
        where: {
          name: defaultCompanyName,
          status: 1,
        },
        findOne: true,
      })

      if (!company) {
        throw new Error(`Company Not Found: ${defaultCompanyName}`)
      }

      // get all active employees
      const employees: any = await this.employeesService.getAllEmployees(
        company?.id,
      )

      const employeeList = Array.isArray(employees)
        ? employees
        : JSON.parse(employees || "[]")

      const employeeIds = employeeList.map((i: any) => i.id)

      const where: any = {
        start_time: {
          bt: {
            start: new Date(startDateOfLastMonth),
            end: new Date(endDateOfLastMonth),
          },
        },
        activityType: {
          is_productive: 1,
          status: 1,
        },
      }

      const lastMonthTimeSheetRecords: any =
        await this.timeTrackingRepository.getByParams({
          where: {
            ...where,
          },
          whereIn: {
            employee_id: employeeIds,
          },
          whereNotNull: ["description"],
          relations: [
            "project:id,name",
            "activityType:id,name,is_productive,is_business_task,is_technical_task,is_personal_task",
          ],
          select: ["id", "description", "company_id", "employee_id"],
        })

      const formatTimeSheetData = new Map()

      if (!lastMonthTimeSheetRecords?.length) {
        throw new Error(`Time Sheet Records Not Found`)
      }

      for (const record of lastMonthTimeSheetRecords) {
        const empId = record.employee_id

        // ✅ Initialize employee
        if (!formatTimeSheetData.has(empId)) {
          formatTimeSheetData.set(empId, {
            business_task: {},
            technical_task: {},
            personal_task: {},
          })
        }

        const employeeData = formatTimeSheetData.get(empId)

        const project = record?.project?.name ?? "UNKNOWN"
        const activity = record?.activityType?.name ?? "UNKNOWN"
        const description = record?.description ?? ""

        // ✅ Helper: project → activity → Set(descriptions)
        const pushNested = (group, project, activity, desc) => {
          if (!group[project]) {
            group[project] = {}
          }

          if (!group[project][activity]) {
            group[project][activity] = new Set()
          }

          group[project][activity].add(desc)
        }

        // ✅ Categorize
        if (record?.activityType?.is_business_task === 1) {
          pushNested(employeeData.business_task, project, activity, description)
        }

        if (record?.activityType?.is_technical_task === 1) {
          pushNested(
            employeeData.technical_task,
            project,
            activity,
            description,
          )
        }

        if (record?.activityType?.is_personal_task === 1) {
          pushNested(employeeData.personal_task, project, activity, description)
        }
      }

      const findFilledMisRecords = await this.misReportEntityRepository.find({
        where: {
          employee_id: In(employeeIds),
          month: lastMonth,
          year: year,
        },
        select: ["id", "employee_id"],
      })

      const findFilledMisEmployeeIds = findFilledMisRecords.map(
        (i) => i.employee_id,
      )

      // Process employees in batches to respect rate limits
      const employeeEntries = Array.from(formatTimeSheetData.entries())
      const BATCH_SIZE = 5 // Process 5 employees at a time (matches free tier limit)
      let processedCount = 0

      this.logger.log(
        `Processing ${employeeEntries.length} employees in batches of ${BATCH_SIZE}`,
      )

      for (let i = 0; i < employeeEntries.length; i += BATCH_SIZE) {
        const batch = employeeEntries.slice(i, i + BATCH_SIZE)
        this.logger.log(
          `Processing batch ${Math.floor(i / BATCH_SIZE) + 1}/${Math.ceil(employeeEntries.length / BATCH_SIZE)} (employees ${i + 1}-${Math.min(i + BATCH_SIZE, employeeEntries.length)})`,
        )

        // Process batch sequentially to respect rate limits
        for (const [empId, empData] of batch) {
          try {
            const payload = {
              business_task: this.convertToOutput(empData.business_task),
              technical_task: this.convertToOutput(empData.technical_task),
              personal_task: this.convertToOutput(empData.personal_task),
            }

            const output = this.convertToNestedString(payload)

            this.logger.log(`Analyzing MIS data for employee ${empId}`)

            const response = await this.geminiService.generateText(
              aiPrompt({ output: output }),
            )

            this.logger.log(
              `MIS data analyzed successfully for employee ${empId}`,
            )

            if (response) {
              const cleaned = response.replace(/```json|```/g, "").trim()

              let parsed = null

              try {
                parsed = JSON.parse(cleaned)
              } catch (e) {
                parsed = null
                this.logger.warn(
                  `Invalid JSON from Gemini for employee ${empId}: ${e.message}`,
                )
              }

              if (parsed) {
                const obj = {
                  company_id: company?.id,
                  employee_id: empId,
                  month: lastMonth,
                  year: year,
                  business_tasks:
                    parsed?.business_task ?? parsed?.business_tasks ?? "",
                  technical_tasks:
                    parsed?.technical_task ?? parsed?.technical_tasks ?? "",
                  personal_tasks:
                    parsed?.personal_task ?? parsed?.personal_tasks ?? "",
                  status: "draft",
                }

                if (findFilledMisEmployeeIds.includes(empId)) {
                  await this.misReportEntityRepository.update(
                    {
                      employee_id: empId,
                      month: lastMonth,
                      year: year,
                    },
                    obj,
                  )
                  this.logger.log(`Updated MIS record for employee ${empId}`)
                } else {
                  await this.misReportEntityRepository.save(obj)
                  this.logger.log(
                    `Created new MIS record for employee ${empId}`,
                  )
                }
                processedCount++
              } else {
                this.logger.warn(
                  `Failed to parse Gemini response for employee ${empId}`,
                )
                processedCount++
              }
            } else {
              this.logger.warn(`No response from Gemini for employee ${empId}`)
              processedCount++
            }
          } catch (error) {
            // Check if this is a critical Gemini API failure that should stop the entire process
            const isCriticalApiFailure =
              error.message?.includes("CRITICAL_API_FAILURE") ||
              error.message?.includes("Gemini API failed after") ||
              error.message?.includes("429") ||
              error.message?.includes("quota") ||
              error.message?.includes("RESOURCE_EXHAUSTED")

            if (isCriticalApiFailure) {
              this.logger.error(
                `Critical Gemini API failure for employee ${empId}: ${error.message}. ` +
                  `Processed ${processedCount}/${employeeEntries.length} employees before failure. ` +
                  `Stopping MIS auto-fill process to prevent incomplete data processing.`,
              )
              throw error // Re-throw to stop the entire cron job
            }

            // For non-critical errors (parsing, database issues, etc.), continue with other employees
            this.logger.error(
              `Non-critical error processing employee ${empId}: ${error.message}. Continuing with other employees.`,
            )
            processedCount++
            continue
          }
        }

        // Add a small delay between batches to be extra safe with rate limits
        if (i + BATCH_SIZE < employeeEntries.length) {
          this.logger.log("Waiting 5 seconds before processing next batch...")
          await new Promise((resolve) => setTimeout(resolve, 5000))
        }
      }

      this.logger.log(
        `Auto fill MIS completed successfully. Processed ${processedCount}/${employeeEntries.length} employees.`,
      )
    } catch (error) {
      // Check if this was a critical API failure
      const isCriticalApiFailure =
        error.message?.includes("CRITICAL_API_FAILURE") ||
        error.message?.includes("Gemini API failed after") ||
        error.message?.includes("429") ||
        error.message?.includes("quota") ||
        error.message?.includes("RESOURCE_EXHAUSTED")

      if (isCriticalApiFailure) {
        this.logger.error(
          `Auto fill MIS process STOPPED due to critical Gemini API failure: ${error.message}. ` +
            `This prevents incomplete data processing. Please check your Gemini API quota and retry later.`,
        )
      } else {
        this.logger.error(`Error in Auto fill MIS: ${error.message}`)
      }
    }
  }

  private convertToNestedString = (data) => {
    let result = ""

    const formatSection = (title, sectionData) => {
      if (!sectionData || sectionData.length === 0) return ""

      let sectionStr = `${title}:\n`

      sectionData.forEach((project) => {
        sectionStr += `${project.project}:\n`

        project.activity.forEach((activity) => {
          sectionStr += `  ${activity.name}:\n`

          activity.descriptions.forEach((desc) => {
            sectionStr += `    • ${desc}\n`
          })
        })

        sectionStr += "\n"
      })

      return sectionStr
    }

    result += formatSection("Business Task", data.business_task)
    result += formatSection("Technical Task", data.technical_task)
    result += formatSection("Personal Task", data.personal_task)

    return result.trim()
  }

  private convertToOutput = (data) => {
    const result = []

    Object.entries(data).forEach(([project, activities]) => {
      const activityList = []

      Object.entries(activities).forEach(([activity, descriptionsSet]) => {
        activityList.push({
          name: activity,
          descriptions: Array.from(descriptionsSet),
        })
      })

      result.push({
        project,
        activity: activityList,
      })
    })

    return result
  }

  /**
   * Manual cron execution for testing/admin purposes
   */
  async runManually(cronName: string) {
    this.logger.log(`🔧 Running cron manually: ${cronName}`)

    switch (cronName) {
      case "generate_payroll":
        await this.handlePayrollGenerationCron()
        break
      case "year_end_carry_forward":
        await this.handleYearEndCarryForwardCron()
        break
      case "subscription_expiry_reminders":
        await this.handleSubscriptionExpiryRemindersCron()
        break
      case "project_alerts":
        await this.handleProjectAlertsCron()
        break
      case "monthly_reports":
        await this.handleMonthlyReportsCron()
        break
      case "auto_clock_out_and_stop_time_tracking":
        await this.handleAutoClockOutAndStopTimeTracking()
        break
      case "auto_fill_mis_report_of_previous_month":
        await this.handleAutoFillMisReportOfPreviousMonth()
        break
      default:
        this.logger.error(`❌ Unknown cron type: ${cronName}`)
        throw new Error(`Unknown cron type: ${cronName}`)
    }
  }
}