'use strict';

/**
 * Production Storage (WIP) = one balance per company + plant + material (shared across all POs/operations).
 * Aggregates existing per-PO rows before switching schema.
 */
module.exports = {
  async up(queryInterface, Sequelize) {
    const sequelize = queryInterface.sequelize;

    const merged = await sequelize.query(
      `SELECT company_id, plant_id, material_id,
              SUM(available_qty) AS total_qty,
              MAX(uom_id) AS uom_id
       FROM production_wip_balance
       GROUP BY company_id, plant_id, material_id`,
      { type: Sequelize.QueryTypes.SELECT }
    );

    await queryInterface.removeConstraint('production_wip_balance', 'uq_pwb_po_material').catch(() => {});

    const poFkRows = await sequelize.query(
      `SELECT DISTINCT CONSTRAINT_NAME AS name FROM information_schema.KEY_COLUMN_USAGE
       WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'production_wip_balance'
         AND COLUMN_NAME = 'production_order_id' AND REFERENCED_TABLE_NAME IS NOT NULL`,
      { type: Sequelize.QueryTypes.SELECT }
    );
    for (const row of poFkRows || []) {
      if (row?.name) {
        await queryInterface.removeConstraint('production_wip_balance', row.name).catch(() => {});
      }
    }

    await queryInterface.changeColumn('production_wip_balance', 'production_order_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
    });

    await queryInterface.addConstraint('production_wip_balance', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'pwb_fk_production_order_id',
      references: { table: 'production_order', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });

    await sequelize.query('DELETE FROM production_wip_balance');

    const now = new Date();
    if (Array.isArray(merged) && merged.length > 0) {
      await queryInterface.bulkInsert(
        'production_wip_balance',
        merged.map((r) => ({
          company_id: r.company_id,
          plant_id: r.plant_id,
          production_order_id: null,
          material_id: r.material_id,
          uom_id: r.uom_id,
          available_qty: Number(r.total_qty) || 0,
          created_at: now,
          updated_at: now,
        }))
      );
    }

    await queryInterface.addConstraint('production_wip_balance', {
      fields: ['company_id', 'plant_id', 'material_id'],
      type: 'unique',
      name: 'uq_pwb_company_plant_material',
    });
  },

  async down() {
    throw new Error('Down migration not supported: plant pool merge is irreversible without backup.');
  },
};
