module.exports = (sequelize, DataTypes) => {
  const MaterialPlant = sequelize.define('MaterialPlant', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    material_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Material',
        key: 'id'
      }
    },
    plant_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Plant',
        key: 'id'
      }
    },
    storage_location: {
      type: DataTypes.STRING(10),
      allowNull: true,
      comment: 'Storage location within plant'
    },
    mrp_type: {
      type: DataTypes.ENUM('ND', 'VB', 'PD', 'PK', 'VM'),
      allowNull: true,
      comment: 'MRP Type: ND=No Planning, VB=Manual Reorder Point, PD=MRP, PK=Consumption Based, VM=Forecast Based'
    },
    mrp_controller: {
      type: DataTypes.STRING(3),
      allowNull: true
    },
    reorder_point: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      defaultValue: 0.000
    },
    safety_stock: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      defaultValue: 0.000
    },
    max_stock: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true
    },
    procurement_type: {
      type: DataTypes.ENUM('E', 'F', 'X'),
      allowNull: true,
      comment: 'E=External Procurement, F=In-house Production, X=Both'
    },
    special_procurement: {
      type: DataTypes.STRING(2),
      allowNull: true
    },
    storage_bin: {
      type: DataTypes.STRING(10),
      allowNull: true
    },
    opening_balance: {
      type: DataTypes.DECIMAL(18, 3),
      allowNull: true,
      defaultValue: 0.000,
      comment: 'Opening stock balance for this material at this plant'
    },
    opening_balance_date: {
      type: DataTypes.DATEONLY,
      allowNull: true,
      comment: 'Date when opening balance was set'
    },
    opening_balance_uom_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'UOM',
        key: 'id'
      },
      comment: 'UOM for opening balance (defaults to material base UOM)'
    },
    opening_balance_unit_cost: {
      type: DataTypes.DECIMAL(18, 2),
      allowNull: true,
      defaultValue: 0.00,
      comment: 'Unit cost for opening balance valuation'
    },
    opening_balance_cgst_rate: {
      type: DataTypes.DECIMAL(5, 2),
      allowNull: false,
      defaultValue: 0.0,
      comment: 'Opening balance valuation: CGST rate % (intra-state)'
    },
    opening_balance_sgst_rate: {
      type: DataTypes.DECIMAL(5, 2),
      allowNull: false,
      defaultValue: 0.0,
      comment: 'Opening balance valuation: SGST rate % (intra-state)'
    },
    opening_balance_igst_rate: {
      type: DataTypes.DECIMAL(5, 2),
      allowNull: false,
      defaultValue: 0.0,
      comment: 'Opening balance valuation: IGST rate % (inter-state)'
    },
    status: {
      type: DataTypes.ENUM('ACTIVE', 'INACTIVE', 'BLOCKED'),
      allowNull: false,
      defaultValue: 'ACTIVE'
    },
    effective_from: {
      type: DataTypes.DATEONLY,
      allowNull: true
    },
    effective_to: {
      type: DataTypes.DATEONLY,
      allowNull: true
    },
    created_by: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'User',
        key: 'id'
      }
    },
    updated_by: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'User',
        key: 'id'
      }
    }
  }, {
    tableName: 'material_plant',
    indexes: [
      {
        unique: true,
        fields: ['material_id', 'plant_id']
      },
      {
        fields: ['plant_id']
      },
      {
        fields: ['status']
      },
      {
        fields: ['effective_from', 'effective_to']
      }
    ]
  });

  MaterialPlant.associate = function(models) {
    MaterialPlant.belongsTo(models.Material, { foreignKey: 'material_id', as: 'material' });
    MaterialPlant.belongsTo(models.Plant, { foreignKey: 'plant_id', as: 'plant' });
    MaterialPlant.belongsTo(models.UOM, { foreignKey: 'opening_balance_uom_id', as: 'openingBalanceUOM' });
  };

  return MaterialPlant;
};

