'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('gate_entry', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      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'
      },
      gate_entry_number: {
        type: Sequelize.STRING(50),
        allowNull: false,
        comment: 'Unique gate entry number'
      },
      gate_entry_date: {
        type: Sequelize.DATEONLY,
        allowNull: false
      },
      po_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'purchase_order',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        comment: 'Reference to Purchase Order'
      },
      vendor_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'business_partner',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      invoice_number: {
        type: Sequelize.STRING(100),
        allowNull: true
      },
      invoice_date: {
        type: Sequelize.DATEONLY,
        allowNull: true
      },
      lr_number: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: 'Lorry Receipt / Transport Receipt Number'
      },
      lr_date: {
        type: Sequelize.DATEONLY,
        allowNull: true
      },
      transporter_name: {
        type: Sequelize.STRING(200),
        allowNull: true
      },
      vehicle_number: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      driver_name: {
        type: Sequelize.STRING(100),
        allowNull: true
      },
      driver_contact: {
        type: Sequelize.STRING(20),
        allowNull: true
      },
      entry_time: {
        type: Sequelize.DATE,
        allowNull: true,
        comment: 'Time when material entered the gate'
      },
      status: {
        type: Sequelize.ENUM('DRAFT', 'CHECKED', 'QC_PENDING', 'QC_APPROVED', 'QC_REJECTED', 'GRN_READY', 'GRN_CREATED', 'CANCELLED'),
        allowNull: false,
        defaultValue: 'DRAFT',
        comment: 'Status: DRAFT, CHECKED (ready for stores/QC), QC_PENDING, QC_APPROVED, QC_REJECTED, GRN_READY, GRN_CREATED'
      },
      destination_type: {
        type: Sequelize.ENUM('STORES', 'QC'),
        allowNull: true,
        comment: 'Where to move checked items: STORES (direct to GRN) or QC (needs QC approval)'
      },
      qc_required: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: false,
        comment: 'Whether QC approval is required'
      },
      qc_approved: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: false,
        comment: 'Whether QC has approved'
      },
      qc_approved_at: {
        type: Sequelize.DATE,
        allowNull: true
      },
      qc_approved_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      qc_rejection_reason: {
        type: Sequelize.TEXT,
        allowNull: true,
        comment: 'Reason for QC rejection if rejected'
      },
      grn_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'goods_receipt_note',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        comment: 'GRN created from this gate entry'
      },
      total_items: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: 'Total number of items checked'
      },
      remarks: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      checked_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        comment: 'User who checked the materials at gate'
      },
      checked_at: {
        type: Sequelize.DATE,
        allowNull: true,
        comment: 'Time when materials were checked'
      },
      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')
      }
    });

    await queryInterface.addIndex('gate_entry', ['company_id', 'gate_entry_number'], {
      unique: true,
      name: 'gate_entry_company_number_unique'
    });

    await queryInterface.addIndex('gate_entry', ['company_id'], {
      name: 'gate_entry_company_id_idx'
    });

    await queryInterface.addIndex('gate_entry', ['plant_id'], {
      name: 'gate_entry_plant_id_idx'
    });

    await queryInterface.addIndex('gate_entry', ['po_id'], {
      name: 'gate_entry_po_id_idx'
    });

    await queryInterface.addIndex('gate_entry', ['vendor_id'], {
      name: 'gate_entry_vendor_id_idx'
    });

    await queryInterface.addIndex('gate_entry', ['status'], {
      name: 'gate_entry_status_idx'
    });

    await queryInterface.addIndex('gate_entry', ['gate_entry_date'], {
      name: 'gate_entry_date_idx'
    });

    await queryInterface.addIndex('gate_entry', ['grn_id'], {
      name: 'gate_entry_grn_id_idx'
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('gate_entry');
  }
};

