module.exports = (sequelize, DataTypes) => {
  const FGStockLedger = sequelize.define(
    'FGStockLedger',
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      stock_ledger_no: {
        type: DataTypes.STRING(60),
        allowNull: true,
        unique: true
      },
      company_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'Company', key: 'id' }
      },
      plant_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'Plant', key: 'id' }
      },
      fiscal_year_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'FiscalYear', key: 'id' }
      },
      item_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'Material', key: 'id' }
      },
      warehouse_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'StorageLocation', key: 'id' }
      },
      reference_type: {
        type: DataTypes.ENUM('FG_INWARD', 'DISPATCH', 'ADJUSTMENT'),
        allowNull: false
      },
      reference_id: {
        type: DataTypes.INTEGER,
        allowNull: true
      },
      movement_date: {
        type: DataTypes.DATE,
        allowNull: false
      },
      inward_qty: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0
      },
      outward_qty: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0
      },
      balance_qty: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0
      },
      created_by: {
        type: DataTypes.INTEGER,
        allowNull: true,
        references: { model: 'User', key: 'id' }
      }
    },
    {
      tableName: 'fg_stock_ledger',
      indexes: [
        { unique: true, fields: ['stock_ledger_no'] },
        { fields: ['company_id', 'plant_id', 'item_id', 'warehouse_id', 'id'] },
        { fields: ['movement_date'] },
        { fields: ['reference_type', 'reference_id'] }
      ]
    }
  );

  FGStockLedger.associate = function (models) {
    FGStockLedger.belongsTo(models.Company, { foreignKey: 'company_id', as: 'company' });
    FGStockLedger.belongsTo(models.Plant, { foreignKey: 'plant_id', as: 'plant' });
    FGStockLedger.belongsTo(models.FiscalYear, { foreignKey: 'fiscal_year_id', as: 'fiscalYear' });
    FGStockLedger.belongsTo(models.Material, { foreignKey: 'item_id', as: 'item' });
    FGStockLedger.belongsTo(models.StorageLocation, { foreignKey: 'warehouse_id', as: 'warehouse' });
    FGStockLedger.belongsTo(models.User, { foreignKey: 'created_by', as: 'createdByUser' });
  };

  return FGStockLedger;
};
