'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('business_partner', 'customer_advance_balance', {
      type: Sequelize.DECIMAL(18, 2),
      allowNull: false,
      defaultValue: 0
    });
    await queryInterface.addColumn('business_partner', 'vendor_advance_balance', {
      type: Sequelize.DECIMAL(18, 2),
      allowNull: false,
      defaultValue: 0
    });

    await queryInterface.addColumn('receipt_entry', 'bank_account', {
      type: Sequelize.STRING(200),
      allowNull: true
    });
    await queryInterface.addColumn('receipt_entry', 'advance_amount', {
      type: Sequelize.DECIMAL(18, 2),
      allowNull: false,
      defaultValue: 0
    });
    await queryInterface.addColumn('receipt_entry', 'adjusted_advance_amount', {
      type: Sequelize.DECIMAL(18, 2),
      allowNull: false,
      defaultValue: 0
    });

    await queryInterface.changeColumn('receipt_entry', 'invoice_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
      references: { model: 'sales_invoice', key: 'id' }
    });

    await queryInterface.createTable('vendor_payment', {
      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'
      },
      payment_no: { type: Sequelize.STRING(60), allowNull: false },
      payment_date: { type: Sequelize.DATEONLY, allowNull: false },
      vendor_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'business_partner', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      purchase_invoice_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'vendor_invoice', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      invoice_total: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      paid_amount: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      paid_to_invoice_amount: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      advance_amount: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      adjusted_advance_amount: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      balance_amount: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      payment_mode: {
        type: Sequelize.ENUM('CASH', 'BANK', 'UPI', 'CHEQUE', 'NEFT', 'ONLINE_TRANSFER'),
        allowNull: false,
        defaultValue: 'BANK'
      },
      bank_account: { type: Sequelize.STRING(200), allowNull: true },
      bank_name: { type: Sequelize.STRING(120), allowNull: true },
      ifsc_code: { type: Sequelize.STRING(20), allowNull: true },
      reference_no: { type: Sequelize.STRING(120), allowNull: true },
      remarks: { type: Sequelize.TEXT, allowNull: true },
      status: {
        type: Sequelize.ENUM('DRAFT', 'POSTED'),
        allowNull: false,
        defaultValue: 'DRAFT'
      },
      posted_at: { type: Sequelize.DATE, allowNull: true },
      posted_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'user', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      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('vendor_payment_item', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false },
      vendor_payment_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'vendor_payment', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      line_no: { type: Sequelize.INTEGER, allowNull: false },
      payment_mode: {
        type: Sequelize.ENUM('CASH', 'BANK', 'UPI', 'CHEQUE', 'NEFT', 'ONLINE_TRANSFER'),
        allowNull: false
      },
      amount: { type: Sequelize.DECIMAL(18, 2), allowNull: false, defaultValue: 0 },
      bank_name: { type: Sequelize.STRING(120), allowNull: true },
      ifsc_code: { type: Sequelize.STRING(20), allowNull: true },
      reference_no: { type: Sequelize.STRING(120), allowNull: true },
      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('vendor_payment', ['company_id', 'plant_id', 'fiscal_year_id', 'payment_no'], {
      unique: true,
      name: 'vendor_payment_company_plant_fy_no_uq'
    });
    await queryInterface.addIndex('vendor_payment', ['vendor_id'], { name: 'vendor_payment_vendor_idx' });
    await queryInterface.addIndex('vendor_payment', ['purchase_invoice_id'], { name: 'vendor_payment_purchase_invoice_idx' });
    await queryInterface.addIndex('vendor_payment', ['status'], { name: 'vendor_payment_status_idx' });
    await queryInterface.addIndex('vendor_payment_item', ['vendor_payment_id'], { name: 'vendor_payment_item_parent_idx' });
  },

  async down(queryInterface) {
    await queryInterface.removeIndex('vendor_payment_item', 'vendor_payment_item_parent_idx');
    await queryInterface.removeIndex('vendor_payment', 'vendor_payment_status_idx');
    await queryInterface.removeIndex('vendor_payment', 'vendor_payment_purchase_invoice_idx');
    await queryInterface.removeIndex('vendor_payment', 'vendor_payment_vendor_idx');
    await queryInterface.removeIndex('vendor_payment', 'vendor_payment_company_plant_fy_no_uq');
    await queryInterface.dropTable('vendor_payment_item');
    await queryInterface.dropTable('vendor_payment');

    await queryInterface.changeColumn('receipt_entry', 'invoice_id', {
      type: queryInterface.sequelize.Sequelize.INTEGER,
      allowNull: false,
      references: { model: 'sales_invoice', key: 'id' }
    });
    await queryInterface.removeColumn('receipt_entry', 'adjusted_advance_amount');
    await queryInterface.removeColumn('receipt_entry', 'advance_amount');
    await queryInterface.removeColumn('receipt_entry', 'bank_account');

    await queryInterface.removeColumn('business_partner', 'vendor_advance_balance');
    await queryInterface.removeColumn('business_partner', 'customer_advance_balance');
  }
};
