'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('serial_number_tracking', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      company_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'company',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      plant_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'plant',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      material_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'material',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      serial_number: {
        type: Sequelize.STRING(100),
        allowNull: false,
        comment: 'Unique serial number'
      },
      batch_number: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: 'Batch number if material is batch managed'
      },
      current_status: {
        type: Sequelize.ENUM('IN_STOCK', 'ISSUED', 'IN_PRODUCTION', 'DELIVERED', 'SCRAPPED', 'RETURNED', 'QUARANTINED'),
        allowNull: false,
        defaultValue: 'IN_STOCK',
        comment: 'Current status of the serial number'
      },
      storage_location: {
        type: Sequelize.STRING(10),
        allowNull: true,
        comment: 'Current storage location'
      },
      reference_type: {
        type: Sequelize.ENUM('GRN', 'GATE_ENTRY', 'MATERIAL_ISSUE', 'PRODUCTION_ORDER', 'DELIVERY', 'SALES_INVOICE', 'SCRAP', 'RETURN', 'TRANSFER', 'ADJUSTMENT', 'OTHER'),
        allowNull: true,
        comment: 'Type of transaction that last moved this serial number'
      },
      reference_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: 'ID of the transaction that last moved this serial number'
      },
      reference_number: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: 'Reference number of the transaction'
      },
      transaction_date: {
        type: Sequelize.DATEONLY,
        allowNull: true,
        comment: 'Date of last transaction'
      },
      production_order_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'production_order',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        comment: 'Production Order if serial is in production'
      },
      customer_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'business_partner',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        comment: 'Customer if serial is delivered'
      },
      remarks: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      created_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      updated_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')
      }
    });

    // Add indexes
    await queryInterface.addIndex('serial_number_tracking', ['company_id', 'plant_id'], {
      name: 'serial_tracking_company_plant_idx'
    });
    await queryInterface.addIndex('serial_number_tracking', ['material_id'], {
      name: 'serial_tracking_material_idx'
    });
    await queryInterface.addIndex('serial_number_tracking', ['serial_number'], {
      unique: true,
      name: 'serial_tracking_serial_number_unique_idx'
    });
    await queryInterface.addIndex('serial_number_tracking', ['batch_number'], {
      name: 'serial_tracking_batch_idx'
    });
    await queryInterface.addIndex('serial_number_tracking', ['current_status'], {
      name: 'serial_tracking_status_idx'
    });
    await queryInterface.addIndex('serial_number_tracking', ['reference_type', 'reference_id'], {
      name: 'serial_tracking_reference_idx'
    });
    await queryInterface.addIndex('serial_number_tracking', ['production_order_id'], {
      name: 'serial_tracking_po_idx'
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('serial_number_tracking');
  }
};

