'use strict';

/**
 * Foreign keys are added in separate ALTER steps after createTable.
 * Inline REFERENCES in CREATE TABLE often triggers MySQL errno 150 on some servers
 * (charset/collation, engine, or type nuance vs parent tables).
 */
module.exports = {
  async up(queryInterface, Sequelize) {
    const noFk = { withoutForeignKeyConstraints: true };

    await queryInterface.createTable(
      'production_wip_balance',
      {
        id: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        company_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        plant_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        production_order_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        material_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        uom_id: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        available_qty: {
          type: Sequelize.DECIMAL(18, 3),
          allowNull: false,
          defaultValue: 0,
          comment: 'WIP quantity available for route operations',
        },
        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'),
        },
      },
      noFk
    );

    await queryInterface.addConstraint('production_wip_balance', {
      fields: ['company_id'],
      type: 'foreign key',
      name: 'pwb_fk_company_id',
      references: { table: 'company', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });
    await queryInterface.addConstraint('production_wip_balance', {
      fields: ['plant_id'],
      type: 'foreign key',
      name: 'pwb_fk_plant_id',
      references: { table: 'plant', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });
    await queryInterface.addConstraint('production_wip_balance', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'pwb_fk_production_order_id',
      references: { table: 'production_order', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'CASCADE',
    });
    await queryInterface.addConstraint('production_wip_balance', {
      fields: ['material_id'],
      type: 'foreign key',
      name: 'pwb_fk_material_id',
      references: { table: 'material', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });
    await queryInterface.addConstraint('production_wip_balance', {
      fields: ['uom_id'],
      type: 'foreign key',
      name: 'pwb_fk_uom_id',
      references: { table: 'uom', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });

    await queryInterface.addIndex('production_wip_balance', ['production_order_id'], { name: 'idx_pwb_production_order' });
    await queryInterface.addIndex('production_wip_balance', ['material_id'], { name: 'idx_pwb_material' });
    await queryInterface.addConstraint('production_wip_balance', {
      fields: ['production_order_id', 'material_id'],
      type: 'unique',
      name: 'uq_pwb_po_material',
    });

    await queryInterface.createTable(
      'production_wip_ledger',
      {
        id: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        company_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        plant_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        production_order_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        material_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        uom_id: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        direction: {
          type: Sequelize.STRING(3),
          allowNull: false,
          comment: 'IN | OUT',
        },
        movement_type: {
          type: Sequelize.STRING(40),
          allowNull: false,
          comment: 'RECEIPT_PO_RECEIVE | RECEIPT_MATERIAL_ISSUE | OPERATION_CONSUME | OPERATION_SCRAP | REVERSAL',
        },
        qty: {
          type: Sequelize.DECIMAL(18, 3),
          allowNull: false,
        },
        route_card_operation_id: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        material_issue_id: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        production_order_material_receipt_line_id: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        remarks: {
          type: Sequelize.STRING(500),
          allowNull: true,
        },
        created_by: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        created_at: {
          type: Sequelize.DATE,
          allowNull: false,
          defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
        },
      },
      noFk
    );

    await queryInterface.addConstraint('production_wip_ledger', {
      fields: ['company_id'],
      type: 'foreign key',
      name: 'pwl_fk_company_id',
      references: { table: 'company', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });
    await queryInterface.addConstraint('production_wip_ledger', {
      fields: ['plant_id'],
      type: 'foreign key',
      name: 'pwl_fk_plant_id',
      references: { table: 'plant', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });
    await queryInterface.addConstraint('production_wip_ledger', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'pwl_fk_production_order_id',
      references: { table: 'production_order', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'CASCADE',
    });
    await queryInterface.addConstraint('production_wip_ledger', {
      fields: ['material_id'],
      type: 'foreign key',
      name: 'pwl_fk_material_id',
      references: { table: 'material', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });
    await queryInterface.addConstraint('production_wip_ledger', {
      fields: ['uom_id'],
      type: 'foreign key',
      name: 'pwl_fk_uom_id',
      references: { table: 'uom', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });
    await queryInterface.addConstraint('production_wip_ledger', {
      fields: ['route_card_operation_id'],
      type: 'foreign key',
      name: 'pwl_fk_route_card_operation_id',
      references: { table: 'route_card_operation', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });
    await queryInterface.addConstraint('production_wip_ledger', {
      fields: ['material_issue_id'],
      type: 'foreign key',
      name: 'pwl_fk_material_issue_id',
      references: { table: 'material_issue', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });
    await queryInterface.addConstraint('production_wip_ledger', {
      fields: ['production_order_material_receipt_line_id'],
      type: 'foreign key',
      name: 'pwl_fk_po_material_receipt_line_id',
      references: { table: 'production_order_material_receipt_line', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });
    await queryInterface.addConstraint('production_wip_ledger', {
      fields: ['created_by'],
      type: 'foreign key',
      name: 'pwl_fk_created_by',
      references: { table: 'user', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });

    await queryInterface.addIndex('production_wip_ledger', ['production_order_id', 'material_id'], {
      name: 'idx_pwl_po_material',
    });
    await queryInterface.addIndex('production_wip_ledger', ['route_card_operation_id'], { name: 'idx_pwl_rc_op' });

    await queryInterface.createTable(
      'route_card_operation_material_consumption',
      {
        id: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        route_card_operation_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        production_order_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        material_id: {
          type: Sequelize.INTEGER,
          allowNull: false,
        },
        uom_id: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        consumed_qty: {
          type: Sequelize.DECIMAL(18, 3),
          allowNull: false,
          defaultValue: 0,
        },
        scrap_qty: {
          type: Sequelize.DECIMAL(18, 3),
          allowNull: false,
          defaultValue: 0,
        },
        created_by: {
          type: Sequelize.INTEGER,
          allowNull: true,
        },
        updated_by: {
          type: Sequelize.INTEGER,
          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 ON UPDATE CURRENT_TIMESTAMP'),
        },
      },
      noFk
    );

    await queryInterface.addConstraint('route_card_operation_material_consumption', {
      fields: ['route_card_operation_id'],
      type: 'foreign key',
      name: 'rcomc_fk_route_card_operation_id',
      references: { table: 'route_card_operation', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'CASCADE',
    });
    await queryInterface.addConstraint('route_card_operation_material_consumption', {
      fields: ['production_order_id'],
      type: 'foreign key',
      name: 'rcomc_fk_production_order_id',
      references: { table: 'production_order', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'CASCADE',
    });
    await queryInterface.addConstraint('route_card_operation_material_consumption', {
      fields: ['material_id'],
      type: 'foreign key',
      name: 'rcomc_fk_material_id',
      references: { table: 'material', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'RESTRICT',
    });
    await queryInterface.addConstraint('route_card_operation_material_consumption', {
      fields: ['uom_id'],
      type: 'foreign key',
      name: 'rcomc_fk_uom_id',
      references: { table: 'uom', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });
    await queryInterface.addConstraint('route_card_operation_material_consumption', {
      fields: ['created_by'],
      type: 'foreign key',
      name: 'rcomc_fk_created_by',
      references: { table: 'user', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });
    await queryInterface.addConstraint('route_card_operation_material_consumption', {
      fields: ['updated_by'],
      type: 'foreign key',
      name: 'rcomc_fk_updated_by',
      references: { table: 'user', field: 'id' },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    });

    await queryInterface.addConstraint('route_card_operation_material_consumption', {
      fields: ['route_card_operation_id', 'material_id'],
      type: 'unique',
      name: 'uq_rcomc_op_material',
    });
    await queryInterface.addIndex('route_card_operation_material_consumption', ['production_order_id'], {
      name: 'idx_rcomc_po',
    });
  },

  async down(queryInterface) {
    await queryInterface.dropTable('route_card_operation_material_consumption');
    await queryInterface.dropTable('production_wip_ledger');
    await queryInterface.dropTable('production_wip_balance');
  },
};
