'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    const [fiscalYears] = await queryInterface.sequelize.query(`
      SELECT id, start_date, end_date FROM fiscal_year 
      WHERE status = 'ACTIVE' 
      ORDER BY start_date DESC 
      LIMIT 1
    `);

    if (fiscalYears.length === 0) return;

    const fiscalYearId = fiscalYears[0].id;
    const startDate = new Date(fiscalYears[0].start_date);
    const endDate = new Date(fiscalYears[0].end_date);

    const periods = [];
    const periodNames = ['April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', 'January', 'February', 'March'];
    const fiscalStartYear = startDate.getFullYear();

    for (let i = 0; i < 12; i++) {
      let periodYear = fiscalStartYear;
      let monthIndex = 3 + i; // April = 3 (0-based)
      
      // Handle year rollover (periods 10-12 are in the next calendar year)
      if (monthIndex > 11) {
        monthIndex = monthIndex - 12;
        periodYear = fiscalStartYear + 1;
      }

      const periodStart = new Date(periodYear, monthIndex, 1);
      const periodEnd = new Date(periodYear, monthIndex + 1, 0); // Last day of the month

      periods.push({
        fiscal_year_id: fiscalYearId,
        period_number: i + 1,
        period_code: `P${String(i + 1).padStart(2, '0')}`,
        period_name: `${periodNames[i]} ${periodYear}`,
        start_date: periodStart.toISOString().split('T')[0],
        end_date: periodEnd.toISOString().split('T')[0],
        is_closed: false,
        status: 'OPEN',
        created_at: new Date(),
        updated_at: new Date()
      });
    }

    const dialect = queryInterface.sequelize.getDialect ? queryInterface.sequelize.getDialect() : 'mysql';
    if (dialect === 'mysql' || dialect === 'mariadb') {
      for (const p of periods) {
        await queryInterface.sequelize.query(
          `INSERT IGNORE INTO fiscal_period (fiscal_year_id, period_number, period_code, period_name, start_date, end_date, is_closed, status, created_at, updated_at)
           VALUES (:fiscal_year_id, :period_number, :period_code, :period_name, :start_date, :end_date, 0, :status, NOW(), NOW())`,
          { replacements: { fiscal_year_id: p.fiscal_year_id, period_number: p.period_number, period_code: p.period_code, period_name: p.period_name, start_date: p.start_date, end_date: p.end_date, status: p.status } }
        );
      }
    } else {
      await queryInterface.bulkInsert('fiscal_period', periods, { ignoreDuplicates: true });
    }
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.bulkDelete('fiscal_period', null, {});
  }
};

