'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('general_dc', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false },
      company_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'company', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      plant_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'plant', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      fiscal_year_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'fiscal_year', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      dc_number: { type: Sequelize.STRING(50), allowNull: false },
      dc_date: { type: Sequelize.DATEONLY, allowNull: false },
      machine_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'machine', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      operation_type: { type: Sequelize.STRING(120), allowNull: true },
      reference_no: { type: Sequelize.STRING(80), allowNull: true },
      delivery_address: { type: Sequelize.TEXT, allowNull: true },
      transport_name: { type: Sequelize.STRING(120), allowNull: true },
      vehicle_no: { type: Sequelize.STRING(40), allowNull: true },
      driver_name: { type: Sequelize.STRING(120), allowNull: true },
      driver_phone: { type: Sequelize.STRING(20), allowNull: true },
      eway_bill_no: { type: Sequelize.STRING(80), allowNull: true },
      total_sent_qty: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      total_received_qty: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      total_amount: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      remarks: { type: Sequelize.TEXT, allowNull: true },
      status: {
        type: Sequelize.ENUM('DRAFT', 'APPROVED', 'SENT', 'PARTIALLY_RECEIVED', 'CLOSED', 'CANCELLED'),
        allowNull: false,
        defaultValue: 'DRAFT'
      },
      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') }
    });

    await queryInterface.createTable('general_dc_item', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false },
      general_dc_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'general_dc', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      line_number: { type: Sequelize.INTEGER, allowNull: false },
      material_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'material', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      description: { type: Sequelize.STRING(500), allowNull: true },
      sent_quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      unit_price: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      line_total: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      received_quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      rejection_quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      uom_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'uom', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      storage_location_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'storage_location', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      remarks: { type: Sequelize.TEXT, allowNull: true },
      created_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
      updated_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') }
    });

    await queryInterface.createTable('general_receipt', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false },
      company_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'company', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      plant_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'plant', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      fiscal_year_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'fiscal_year', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      receipt_number: { type: Sequelize.STRING(50), allowNull: false },
      receipt_date: { type: Sequelize.DATEONLY, allowNull: false },
      machine_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'machine', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      general_dc_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'general_dc', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      total_received_qty: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      total_rejection_qty: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      total_amount: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      remarks: { type: Sequelize.TEXT, allowNull: true },
      status: { type: Sequelize.ENUM('POSTED', 'CANCELLED'), allowNull: false, defaultValue: 'POSTED' },
      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') }
    });

    await queryInterface.createTable('general_receipt_item', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false },
      general_receipt_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'general_receipt', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      general_dc_item_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'general_dc_item', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      line_number: { type: Sequelize.INTEGER, allowNull: false },
      material_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'material', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      description: { type: Sequelize.STRING(500), allowNull: true },
      sent_quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      unit_price: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      line_total: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      received_quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      rejection_quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: false, defaultValue: 0 },
      uom_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'uom', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      storage_location_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'storage_location', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      remarks: { type: Sequelize.TEXT, allowNull: true },
      created_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
      updated_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') }
    });

    await queryInterface.addIndex('general_dc', ['company_id', 'dc_number'], { unique: true, name: 'general_dc_company_number_uq' });
    await queryInterface.addIndex('general_dc', ['machine_id'], { name: 'general_dc_machine_idx' });
    await queryInterface.addIndex('general_dc_item', ['general_dc_id', 'line_number'], { unique: true, name: 'general_dc_item_line_uq' });
    await queryInterface.addIndex('general_receipt', ['company_id', 'receipt_number'], { unique: true, name: 'general_receipt_company_number_uq' });
    await queryInterface.addIndex('general_receipt_item', ['general_receipt_id', 'line_number'], { unique: true, name: 'general_receipt_item_line_uq' });
  },

  async down(queryInterface) {
    await queryInterface.removeIndex('general_receipt_item', 'general_receipt_item_line_uq');
    await queryInterface.removeIndex('general_receipt', 'general_receipt_company_number_uq');
    await queryInterface.removeIndex('general_dc_item', 'general_dc_item_line_uq');
    await queryInterface.removeIndex('general_dc', 'general_dc_machine_idx');
    await queryInterface.removeIndex('general_dc', 'general_dc_company_number_uq');
    await queryInterface.dropTable('general_receipt_item');
    await queryInterface.dropTable('general_receipt');
    await queryInterface.dropTable('general_dc_item');
    await queryInterface.dropTable('general_dc');
  }
};
