const cron = require('node-cron');
const mysql = require('mysql2/promise');
const SubmissionAutoFillService = require('./auto_fill_missing_quarterly_submissions_service'); // Adjust path
const IPICalculationService = require('./ipi_calculation_service'); 
const { sendEmailService } = require("./email.service"); 

// Database configuration
const dbConfig =  {
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  dialect: process.env.DB_DIALECT || 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};


 
class AutomatedSchedulerService {

 
  constructor() {
    this.isRunning = false;
    this.pool = mysql.createPool(dbConfig);
  } 

  /**
   * Fetch the latest entry from quarterly_windows_configuration_master table
   */
  async getLatestQuarterlyWindow() {
    const connection = await this.pool.getConnection();
    try {
      const query = `
        SELECT 
          survey_name,
          year,
          quarter,
          end_date,
          start_date
        FROM quarterly_windows_configuration_master
        WHERE ipi_calculated = 0
        ORDER BY year DESC, 
                 CASE quarter
                   WHEN 'Q1' THEN 1
                   WHEN 'Q2' THEN 2
                   WHEN 'Q3' THEN 3
                   WHEN 'Q4' THEN 4
                 END DESC
        LIMIT 1
      `;

      //  const query = `
      //   SELECT 
      //     year,
      //     quarter,
      //     end_date,
      //     start_date
      //   FROM quarterly_windows_configuration_master
      //   ORDER BY id DESC
      //   LIMIT 1
      // `;
      
      const [rows] = await connection.query(query);
      return rows[0] || null;
    } finally {
      connection.release();
    }
  }

  /**
   * Execute survey auto submit logic
   */
  async executeSurveyAutoSubmit(year, quarter) {
    console.log(`[${new Date().toISOString()}] Starting survey_auto_submit for ${year} ${quarter}`);
    
    try {
      const autoSubmissionService = new SubmissionAutoFillService(dbConfig);
      
      // Auto-fill missing submissions for specific year and quarter
      const result = await autoSubmissionService.autoFillMissingSubmissions(year, quarter);
      
      // Verify completeness for specific quarter
      const result3 = await autoSubmissionService.verifyCompleteness(year, quarter);

      console.log(`[${new Date().toISOString()}] survey_auto_submit completed successfully`);
      
      return { 
        success: true, 
        autoFillMissingSubmissions: result, 
        verifyCompleteness: result3 
      };
    } catch (error) {
      console.error(`[${new Date().toISOString()}] Error in survey_auto_submit:`, error);
      throw error;
    }
  }

  /**
   * Execute calculate quarter logic
   */
  async executeCalculateQuarter(year, quarter) {
    console.log(`[${new Date().toISOString()}] Starting calculate_quarter for ${year} ${quarter}`);
    
    try {
      if (!['Q1', 'Q2', 'Q3', 'Q4'].includes(quarter)) {
        throw new Error('Invalid quarter. Accepted values: Q1, Q2, Q3, Q4');
      }

      const service = new IPICalculationService(dbConfig);
      const result = await service.runCompleteCalculation(year, quarter);

      await service.close();

      console.log(`[${new Date().toISOString()}] calculate_quarter completed successfully`);
      
      return { 
        success: true, 
        message: `IPI calculated for ${year} ${quarter}`, 
        data: result 
      };
    } catch (error) {
      console.error(`[${new Date().toISOString()}] Error in calculate_quarter:`, error);
      throw error;
    }
  }

  /**
   * Update ipi_calculated flag in quarterly_windows_configuration_master
   */
  async updateIPICalculatedFlag(year, quarter) {
    const connection = await this.pool.getConnection();
    try {
      const query = `
        UPDATE quarterly_windows_configuration_master 
        SET ipi_calculated = 1 
        WHERE year = ? AND quarter = ?
      `; 
      
      await connection.query(query, [year, quarter]);
      console.log(`[${new Date().toISOString()}] Updated ipi_calculated flag to 1 for ${year} ${quarter}`);
    } catch (error) {
      console.error(`[${new Date().toISOString()}] Error updating ipi_calculated flag:`, error);
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * Main scheduler logic - runs daily
   */
  async runScheduledTask() {
    // Prevent concurrent execution
    if (this.isRunning) {
      console.log(`[${new Date().toISOString()}] Scheduler already running, skipping this execution`);
      return;
    }

    this.isRunning = true;

    try {
      console.log(`[${new Date().toISOString()}] ========================================`);
      console.log(`[${new Date().toISOString()}] Starting scheduled task execution`);
      
      // Step 1: Get latest quarterly window configuration
      const latestWindow = await this.getLatestQuarterlyWindow();
      
      if (!latestWindow) {
        console.log(`[${new Date().toISOString()}] No quarterly window configuration found`);
        return;
      }

      console.log(`[${new Date().toISOString()}] Latest window: Year=${latestWindow.year}, Quarter=${latestWindow.quarter}, End Date=${latestWindow.end_date}`);

      // Step 2: Check if current date is greater than end_date
      const currentDate = new Date();
      const endDate = new Date(latestWindow.end_date);

      console.log(`[${new Date().toISOString()}] Current Date: ${currentDate.toISOString()}`);
      console.log(`[${new Date().toISOString()}] End Date: ${endDate.toISOString()}`);

      if (currentDate < endDate) {
        console.log(`[${new Date().toISOString()}] Current date is not greater than end_date. Skipping execution.`);
        return;
      }

      console.log(`[${new Date().toISOString()}] Current date is greater than end_date. Proceeding with execution.`);

      // Step 3: Execute survey_auto_submit
      const surveyResult = await this.executeSurveyAutoSubmit(
        latestWindow.year, 
        latestWindow.quarter
      );

      // Step 4: Execute calculate_quarter after survey_auto_submit completes
      const calculationResult = await this.executeCalculateQuarter(
        latestWindow.year, 
        latestWindow.quarter
      );

      // Step 5: Update ipi_calculated flag to 1
      await this.updateIPICalculatedFlag(latestWindow.year, latestWindow.quarter);

      console.log(`[${new Date().toISOString()}] ========================================`);
      console.log(`[${new Date().toISOString()}] All scheduled tasks completed successfully`);
      console.log(`[${new Date().toISOString()}] Survey Result:`, JSON.stringify(surveyResult, null, 2));
      console.log(`[${new Date().toISOString()}] Calculation Result:`, JSON.stringify(calculationResult, null, 2));

    } catch (error) {
      console.error(`[${new Date().toISOString()}] Error in scheduled task:`, error);
      // You might want to send an alert/notification here
    } finally {
      this.isRunning = false;
    }
  }

  /**
   * Start the scheduler
   * Runs every day at 2:00 AM (you can adjust the time)
   */
  start() {
    console.log(`[${new Date().toISOString()}] Starting automated scheduler...`);
    
    // Run every day at 2:00 AM
    // Format: minute hour day month weekday
    // '0 2 * * *' = At 2:00 AM every day
    cron.schedule('0 2 * * *', () => {
      this.runScheduledTask();
    });

    console.log(`[${new Date().toISOString()}] Scheduler started successfully. Will run daily at 2:00 AM.`);

  }

  /**
   * Manual trigger for testing
   */
  async manualTrigger() {
    console.log(`[${new Date().toISOString()}] Manual trigger initiated...`);
    await this.runScheduledTask();
  }


  













 //------------------------------------------------------------------------------















 


  async getEstablishmentsWithoutSubmission(year, quarter) {

    const connection = await this.pool.getConnection();

    try {
      const query = `
        SELECT 
                  e.id AS establishment_id,
                  e.factory_name,
                  eu.id AS user_id,
                  eu.name AS user_name,
                  eu.email
                FROM establishments e
                LEFT JOIN establishment_users eu 
                  ON eu.establishment_id = e.id AND eu.is_active = 1

                WHERE NOT EXISTS (
                  SELECT 1 
                  FROM submission s
                  WHERE s.establishment_id = e.id
                    AND s.year = ?
                    AND s.quarter = ? )  AND e.is_active = 1
      `;

      const [rows] = await connection.query(query, [year, quarter]);

      return rows;

    } catch (error) {
      console.error("Error fetching pending establishments:", error);
      return [];
    } finally {
      connection.release();
    }
  }

  async sendReminderEmails(rows, latestWindow) {
    try {
      if (!rows.length) {
        console.log("No pending establishments.");
        return;
      }

      const basePlaceholder = { 
        survey_name: latestWindow.survey_name,
        quarter: latestWindow.quarter,
        year: latestWindow.year,
        opens_on: latestWindow.start_date,
        closes_on: latestWindow.end_date,
        portal_url: process.env.FE_BASE_URL,
        support_email: process.env.SUPPORT_EMAIL,
        support_phone: process.env.SUPPORT_PHONE,
        logo_url: `${process.env.APP_BASE_URL}/assets/FCSCLogo.png`     
      };



      const promises = rows.map((r) =>
        sendEmailService(r.email, "submission_last_day_reminder_mail", {
          ...basePlaceholder,
          user_name: r.user_name,
          establishment_name: r.factory_name,
        })
      );

      await Promise.all(promises);

      console.log(`Sent ${promises.length} reminder emails.`);

    } catch (err) {
      console.error("Reminder email error:", err);
    }
  }

  async sendEmailReminders(latestWindow) {
    console.log(
      `[${new Date().toISOString()}] Sending reminders for Year=${latestWindow.year}, Quarter=${latestWindow.quarter}`
    );

    const establishments = await this.getEstablishmentsWithoutSubmission(
      latestWindow.year,
      latestWindow.quarter
    );

    if (!establishments.length) {
      console.log("No pending establishments found.");
      return;
    }

    await this.sendReminderEmails(establishments, latestWindow);

    console.log(`[${new Date().toISOString()}] Email reminders completed.`);
  }

  async getAllQuarterlyWindows() {
    const connection = await this.pool.getConnection();
    try {
      const query = `
        SELECT 
          survey_name,
          year,
          quarter,
          end_date,
          start_date
        FROM quarterly_windows_configuration_master
        WHERE ipi_calculated = 0
        ORDER BY year DESC, 
                 CASE quarter
                   WHEN 'Q1' THEN 1
                   WHEN 'Q2' THEN 2
                   WHEN 'Q3' THEN 3
                   WHEN 'Q4' THEN 4
                 END DESC
      `;

      //  const query = `
      //   SELECT 
      //     year,
      //     quarter,
      //     end_date,
      //     start_date
      //   FROM quarterly_windows_configuration_master
      //   ORDER BY id DESC
      //   LIMIT 1
      // `;
      
      const [rows] = await connection.query(query);
      return rows || null;
    } finally {
      connection.release();
    }
  }

  async runScheduledEmails() {

    // find all quarterly window data ordered by year desc and quarter desc
    const latestWindow = await this.getAllQuarterlyWindows();

    // check if data exists
    if (!latestWindow || latestWindow.length === 0) {
      console.log(`[${new Date().toISOString()}] No quarterly window configuration found for email reminders`);
      return;
    }

    // loop through all windows
    for (const window of latestWindow) {

      console.log(
        `[${new Date().toISOString()}] Processing window: Year=${window.year}, Quarter=${window.quarter}, End Date=${window.end_date}`
      );

      let currentDateStr = new Date().toLocaleDateString("en-CA");
      let endDateStr = new Date(window.end_date).toLocaleDateString("en-CA");

      console.log(`[${new Date().toISOString()}] Current Date: ${currentDateStr}`);
      console.log(`[${new Date().toISOString()}] End Date: ${endDateStr}`);

      // Check if today is the end date
      if (currentDateStr === endDateStr) {
        console.log(`[${new Date().toISOString()}] Today is the last day. Sending reminder emails.`);
        await this.sendEmailReminders(window);
      } else {
        console.log(`[${new Date().toISOString()}] Not the last day. Skipping reminder emails.`);
      }
    }
  }

  // async runScheduledEmails() {

  //   //find latest quarterly window configuration
  //   const latestWindow = await this.getLatestQuarterlyWindow();
        
  //   if (!latestWindow) {
  //     console.log(`[${new Date().toISOString()}] No quarterly window configuration found for email reminders`);
  //     return;
  //   }

  //   console.log(`[${new Date().toISOString()}] Latest window for email reminders: Year=${latestWindow.year}, Quarter=${latestWindow.quarter}, End Date=${latestWindow.end_date}`);


  //   // Check if current date is greater than end_date
  //   const currentDate = new Date();
  //   const endDate = new Date(latestWindow.end_date);

  //   const currentDateStr = new Date().toLocaleDateString("en-CA");
  //   const endDateStr = new Date(latestWindow.end_date).toLocaleDateString("en-CA");

  //   console.log(`[${new Date().toISOString()}] Current Date: ${currentDateStr}`);
  //   console.log(`[${new Date().toISOString()}] End Date: ${endDateStr}`);

  //   if (currentDateStr === endDateStr) {
  //     console.log(`[${new Date().toISOString()}] Today is the last day. Sending reminder emails.`);
  //     await this.sendEmailReminders(latestWindow);
  //   } else {
  //     console.log(`[${new Date().toISOString()}] Not the last day. Skipping reminder emails.`);
  //   }


  // }
  /**
   * Start the scheduler
   * Runs every day at 6:00 AM (you can adjust the time)
   */
  startRemainderEmail() {
    console.log(`[${new Date().toISOString()}] Starting automated email reminder scheduler...`);
    // Run every day at 6:00 AM
    // Format: minute hour day month weekday
    // '0 6 * * *' = At 6:00 AM every day
    cron.schedule('0 6 * * *', () => {
      this.runScheduledEmails();
    });

    console.log(`[${new Date().toISOString()}] Scheduler started successfully. Will run daily at 6:00 AM.`);

  }

}

module.exports = AutomatedSchedulerService;