'use strict';

module.exports = {
    async up(queryInterface, Sequelize) {
        // Add balance_quantity column to gate_entry_item
        await queryInterface.addColumn('gate_entry_item', 'balance_quantity', {
            type: Sequelize.DECIMAL(18, 3),
            allowNull: true,
            defaultValue: null,
            comment: 'Balance quantity = PO quantity - Received quantity',
            after: 'received_quantity'
        });

        // Add receiving_status column to gate_entry_item
        await queryInterface.addColumn('gate_entry_item', 'receiving_status', {
            type: Sequelize.ENUM('NOT_RECEIVED', 'PARTIAL_RECEIVED', 'FULLY_RECEIVED'),
            allowNull: true,
            defaultValue: null,
            comment: 'Receiving status: NOT_RECEIVED, PARTIAL_RECEIVED, FULLY_RECEIVED',
            after: 'balance_quantity'
        });

        // Add receiving_status column to gate_entry (overall status for the entry)
        await queryInterface.addColumn('gate_entry', 'receiving_status', {
            type: Sequelize.ENUM('NOT_RECEIVED', 'PARTIAL_RECEIVED', 'FULLY_RECEIVED'),
            allowNull: true,
            defaultValue: null,
            comment: 'Overall receiving status for the gate entry',
            after: 'status'
        });

        // Update existing records: compute balance_quantity from po_quantity and received_quantity
        await queryInterface.sequelize.query(`
      UPDATE gate_entry_item
      SET balance_quantity = CASE
        WHEN po_quantity IS NOT NULL AND po_quantity > 0 THEN po_quantity - received_quantity
        ELSE NULL
      END,
      receiving_status = CASE
        WHEN po_quantity IS NULL OR po_quantity <= 0 THEN NULL
        WHEN received_quantity <= 0 THEN 'NOT_RECEIVED'
        WHEN received_quantity < po_quantity THEN 'PARTIAL_RECEIVED'
        ELSE 'FULLY_RECEIVED'
      END
    `);

        // Update existing gate_entry records with overall receiving_status
        await queryInterface.sequelize.query(`
      UPDATE gate_entry ge
      SET receiving_status = (
        SELECT CASE
          WHEN COUNT(*) = 0 THEN NULL
          WHEN SUM(CASE WHEN gei.receiving_status = 'FULLY_RECEIVED' THEN 1 ELSE 0 END) = COUNT(*) THEN 'FULLY_RECEIVED'
          WHEN SUM(CASE WHEN gei.receiving_status = 'NOT_RECEIVED' THEN 1 ELSE 0 END) = COUNT(*) THEN 'NOT_RECEIVED'
          ELSE 'PARTIAL_RECEIVED'
        END
        FROM gate_entry_item gei
        WHERE gei.gate_entry_id = ge.id
        AND gei.po_quantity IS NOT NULL
        AND gei.po_quantity > 0
      )
    `);
    },

    async down(queryInterface, Sequelize) {
        await queryInterface.removeColumn('gate_entry_item', 'balance_quantity');
        await queryInterface.removeColumn('gate_entry_item', 'receiving_status');
        await queryInterface.removeColumn('gate_entry', 'receiving_status');
    }
};
