'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    const [states] = await queryInterface.sequelize.query(`
      SELECT s.id, s.state_code FROM state s
      WHERE s.state_code IN ('MH', 'DL', 'KA', 'TN', 'GJ', 'RJ', 'UP', 'WB')
      ORDER BY s.state_code
    `);
    const stateMap = {};
    (states || []).forEach(state => { stateMap[state.state_code] = state.id; });

    const cities = [
      { state_code: 'MH', city_code: 'MUM', city_name: 'Mumbai', pincode_prefix: '400' },
      { state_code: 'MH', city_code: 'PUN', city_name: 'Pune', pincode_prefix: '411' },
      { state_code: 'DL', city_code: 'DEL', city_name: 'New Delhi', pincode_prefix: '110' },
      { state_code: 'KA', city_code: 'BLR', city_name: 'Bangalore', pincode_prefix: '560' },
      { state_code: 'TN', city_code: 'CHE', city_name: 'Chennai', pincode_prefix: '600' },
      { state_code: 'GJ', city_code: 'AMD', city_name: 'Ahmedabad', pincode_prefix: '380' },
      { state_code: 'RJ', city_code: 'JAI', city_name: 'Jaipur', pincode_prefix: '302' },
      { state_code: 'UP', city_code: 'LKO', city_name: 'Lucknow', pincode_prefix: '226' },
      { state_code: 'WB', city_code: 'KOL', city_name: 'Kolkata', pincode_prefix: '700' }
    ];
    const dialect = queryInterface.sequelize.getDialect ? queryInterface.sequelize.getDialect() : 'mysql';
    if (dialect === 'mysql' || dialect === 'mariadb') {
      for (const r of cities) {
        const state_id = stateMap[r.state_code];
        if (!state_id) continue;
        await queryInterface.sequelize.query(
          `INSERT IGNORE INTO city (state_id, city_code, city_name, pincode_prefix, is_active, created_at, updated_at)
           VALUES (:state_id, :city_code, :city_name, :pincode_prefix, 1, NOW(), NOW())`,
          { replacements: { state_id, city_code: r.city_code, city_name: r.city_name, pincode_prefix: r.pincode_prefix } }
        );
      }
    } else {
      await queryInterface.bulkInsert('city', cities.map((r) => ({
        state_id: stateMap[r.state_code],
        city_code: r.city_code,
        city_name: r.city_name,
        pincode_prefix: r.pincode_prefix,
        is_active: true,
        created_at: new Date(),
        updated_at: new Date()
      })).filter((row) => row.state_id), { ignoreDuplicates: true });
    }
  },

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

