const db = require('../config/db');
const logger = require('../utils/logger');
const { buildMonthlyReportSnapshot } = require('../utils/monthlyReportSnapshot');

// Generate a snapshot for a specific year/month (month is 1..12)
async function generateSnapshotForMonth(year, month) {
  const snapshotMonth = `${year}-${String(month).padStart(2, '0')}`;
  try {
    const [exists] = await db.query('SELECT id FROM monthly_report_snapshots WHERE snapshot_month = ?', [snapshotMonth]);
    if (exists && exists[0]) {
      logger.info(`Snapshot for ${snapshotMonth} already exists, skipping.`);
      return false;
    }

    const [customers] = await db.query(
      `SELECT id, first_name, last_name, ledger_balance, risk_level, balance_status, created_at
       FROM customers`);

    const [payments] = await db.query(
      `SELECT customer_id, amount, created_at
       FROM customer_payments
       ORDER BY created_at ASC`);

    const payload = buildMonthlyReportSnapshot({
      snapshotMonth,
      generatedAt: new Date(),
      accountManager: 'SYSTEM',
      customers: customers || [],
      payments: payments || [],
    });

    await db.query(
      'INSERT INTO monthly_report_snapshots (snapshot_month, generated_at, total_outstanding, data) VALUES (?, NOW(), ?, ?)',
      [snapshotMonth, payload.totalOutstanding, JSON.stringify(payload)]
    );
    logger.info(`Snapshot created for ${snapshotMonth}`);
    return true;
  } catch (err) {
    logger.error('Failed to create snapshot for', snapshotMonth, err.message || err);
    return false;
  }
}

// Generate snapshots for any missing months up to previous month
async function generateMissingSnapshots() {
  try {
    const now = new Date();
    // target is previous month
    const target = new Date(now.getFullYear(), now.getMonth() - 1, 1);
    const targetMonth = target.getMonth() + 1;
    const targetYear = target.getFullYear();

    // Find latest existing snapshot month
    const [rows] = await db.query('SELECT snapshot_month FROM monthly_report_snapshots ORDER BY snapshot_month DESC LIMIT 1');
    let startYear, startMonth;
    if (rows && rows[0] && rows[0].snapshot_month) {
      const parts = rows[0].snapshot_month.split('-');
      startYear = parseInt(parts[0], 10);
      startMonth = parseInt(parts[1], 10) + 1; // start from next month
    } else {
      // No snapshots yet: create for the previous month only
      startYear = targetYear;
      startMonth = targetMonth;
    }

    // Normalize start to valid month/year
    if (startMonth > 12) {
      startMonth = 1;
      startYear += 1;
    }

    // Build list of months to generate from start -> target
    const tasks = [];
    let y = startYear;
    let m = startMonth;
    while (y < targetYear || (y === targetYear && m <= targetMonth)) {
      tasks.push({ y, m });
      m += 1;
      if (m > 12) { m = 1; y += 1; }
    }

    for (const t of tasks) {
      // generate each snapshot sequentially
      await generateSnapshotForMonth(t.y, t.m);
    }
  } catch (err) {
    logger.error('Error generating missing snapshots:', err.message || err);
  }
}

// Schedule a daily check (runs once per hour to cover time drift), but main guarantee is run on startup
function initScheduler() {
  // Run on startup
  generateMissingSnapshots();

  // Run daily at 00:05 server time: use setInterval to check once per hour and attempt missing snapshots
  setInterval(() => {
    const now = new Date();
    // If it's the 1st of month within first hour, attempt to create snapshot for previous month
    if (now.getDate() === 1 && now.getHours() === 0) {
      generateMissingSnapshots();
    }
  }, 60 * 60 * 1000); // every hour

  logger.info('Monthly snapshot scheduler initialized');
}

module.exports = {
  initScheduler,
  generateSnapshotForMonth,
  generateMissingSnapshots,
};
