module.exports = (sequelize, DataTypes) => {
  const NumberFormat = sequelize.define('NumberFormat', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    company_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Company',
        key: 'id'
      }
    },
    module_name: {
      type: DataTypes.STRING(50),
      allowNull: false,
      comment: 'Module name: MATERIAL, PURCHASE_ORDER, SALES_ORDER, etc.'
    },
    material_type_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'MaterialType',
        key: 'id'
      },
      comment: 'Material type for material number generation (null for other modules)'
    },
    format_pattern: {
      type: DataTypes.STRING(100),
      allowNull: false,
      comment: 'Format pattern: {COMPANY_CODE}-{MATERIAL_TYPE_CODE}-{SEQUENCE} or custom pattern'
    },
    sequence_length: {
      type: DataTypes.INTEGER,
      allowNull: false,
      defaultValue: 6,
      comment: 'Length of sequence number (e.g., 6 for 000001)'
    },
    prefix: {
      type: DataTypes.STRING(50),
      allowNull: true,
      comment: 'Optional prefix before the pattern'
    },
    suffix: {
      type: DataTypes.STRING(50),
      allowNull: true,
      comment: 'Optional suffix after the pattern'
    },
    separator: {
      type: DataTypes.STRING(10),
      allowNull: false,
      defaultValue: '-',
      comment: 'Separator between pattern parts (default: -)'
    },
    current_sequence: {
      type: DataTypes.INTEGER,
      allowNull: false,
      defaultValue: 0,
      comment: 'Current sequence number for this format'
    },
    reset_period: {
      type: DataTypes.ENUM('NEVER', 'YEARLY', 'MONTHLY', 'DAILY'),
      allowNull: false,
      defaultValue: 'NEVER',
      comment: 'When to reset sequence: NEVER, YEARLY, MONTHLY, DAILY'
    },
    last_reset_date: {
      type: DataTypes.DATEONLY,
      allowNull: true,
      comment: 'Last date when sequence was reset'
    },
    format_prefix: {
      type: DataTypes.STRING(20),
      allowNull: true,
      comment: 'Short prefix in number e.g. PO, MR, GRN (used with company code)'
    },
    include_fiscal_year: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: true,
      comment: 'Include fiscal year in generated number'
    },
    fiscal_year_format: {
      type: DataTypes.STRING(10),
      allowNull: true,
      comment: 'YYYY-YY (e.g. 2025-26) or YYYY'
    },
    description: {
      type: DataTypes.STRING(500),
      allowNull: true,
      comment: 'Description of this number format'
    },
    is_active: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: true,
      comment: 'Whether this format is active'
    },
    status: {
      type: DataTypes.ENUM('ACTIVE', 'INACTIVE'),
      allowNull: false,
      defaultValue: 'ACTIVE'
    },
    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: 'number_format_master',
    timestamps: true,
    underscored: true,
    indexes: [
      {
        unique: true,
        fields: ['company_id', 'module_name', 'material_type_id'],
        where: {
          material_type_id: {
            [sequelize.Sequelize.Op.ne]: null
          }
        }
      }
    ]
  });

  NumberFormat.associate = function(models) {
    NumberFormat.belongsTo(models.Company, {
      foreignKey: 'company_id',
      as: 'company'
    });
    NumberFormat.belongsTo(models.MaterialType, {
      foreignKey: 'material_type_id',
      as: 'materialType'
    });
    NumberFormat.belongsTo(models.User, {
      foreignKey: 'created_by',
      as: 'creator'
    });
    NumberFormat.belongsTo(models.User, {
      foreignKey: 'updated_by',
      as: 'updater'
    });
  };

  return NumberFormat;
};
