# Material Storage Location Schema

## Overview
This document describes the schema changes for adding storage location fields to the material master table.

## Database Schema

### Material Table - New Fields

The following fields have been added to the `material` table:

#### 1. `storage_location_id`
- **Type**: `INT(11)`
- **Nullable**: `YES` (NULL allowed)
- **Default**: `NULL`
- **Foreign Key**: References `storage_location.id`
- **On Update**: `CASCADE`
- **On Delete**: `SET NULL`
- **Comment**: Default storage location for this material
- **Index**: `idx_material_storage_location_id`

#### 2. `is_default_storage_location`
- **Type**: `TINYINT(1)` / `BOOLEAN`
- **Nullable**: `NO` (NOT NULL)
- **Default**: `0` (false)
- **Comment**: Indicates if this is the default storage location for the material

### SQL Schema Definition

```sql
-- Add storage_location_id column
ALTER TABLE `material` 
ADD COLUMN `storage_location_id` INT(11) NULL DEFAULT NULL 
COMMENT 'Default storage location for this material' 
AFTER `description`;

-- Add foreign key constraint
ALTER TABLE `material` 
ADD CONSTRAINT `fk_material_storage_location` 
FOREIGN KEY (`storage_location_id`) 
REFERENCES `storage_location` (`id`) 
ON UPDATE CASCADE 
ON DELETE SET NULL;

-- Add is_default_storage_location column
ALTER TABLE `material` 
ADD COLUMN `is_default_storage_location` TINYINT(1) NOT NULL DEFAULT 0 
COMMENT 'Indicates if this is the default storage location for the material' 
AFTER `storage_location_id`;

-- Add index for query performance
CREATE INDEX `idx_material_storage_location_id` 
ON `material` (`storage_location_id`);
```

### Complete Material Table Structure (Relevant Section)

```sql
CREATE TABLE `material` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `company_id` INT(11) NOT NULL,
  `material_number` VARCHAR(40) NOT NULL,
  `material_type` ENUM('RAW_MATERIAL','SEMI_FINISHED','FINISHED_GOOD','PACKAGING','CONSUMABLE','SERVICE','ASSET') NOT NULL,
  `material_group` VARCHAR(50) DEFAULT NULL,
  `description` VARCHAR(500) NOT NULL,
  `storage_location_id` INT(11) NULL DEFAULT NULL COMMENT 'Default storage location for this material',
  `is_default_storage_location` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Indicates if this is the default storage location for the material',
  `base_uom_id` INT(11) NOT NULL,
  -- ... other fields ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_company_material_number` (`company_id`, `material_number`),
  KEY `idx_material_storage_location_id` (`storage_location_id`),
  CONSTRAINT `fk_material_storage_location` 
    FOREIGN KEY (`storage_location_id`) 
    REFERENCES `storage_location` (`id`) 
    ON UPDATE CASCADE 
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## Sequelize Model Definition

```javascript
storage_location_id: {
  type: DataTypes.INTEGER,
  allowNull: true,
  references: {
    model: 'StorageLocation',
    key: 'id'
  },
  comment: 'Default storage location for this material'
},
is_default_storage_location: {
  type: DataTypes.BOOLEAN,
  allowNull: false,
  defaultValue: false,
  comment: 'Indicates if this is the default storage location for the material'
}
```

## Relationships

### Material → StorageLocation
- **Type**: Belongs To (Many-to-One)
- **Foreign Key**: `storage_location_id`
- **Association**: `Material.belongsTo(models.StorageLocation, { foreignKey: 'storage_location_id', as: 'storageLocation' })`

## Usage

### Frontend Form Fields
- **Storage Location**: Dropdown showing all active storage locations for the company
- **Default Location**: Switch/toggle to mark the selected storage location as default

### Data Flow
1. User selects a storage location from the dropdown
2. Optionally marks it as default using the switch
3. On save, `storage_location_id` and `is_default_storage_location` are stored in the material record

## Migration Files

1. **Sequelize Migration**: `20260226000001-add-storage-location-to-material.js`
2. **SQL Script**: `add-storage-location-to-material.sql`

## Rollback

To rollback these changes:

```sql
-- Remove index
DROP INDEX `idx_material_storage_location_id` ON `material`;

-- Remove foreign key constraint
ALTER TABLE `material` DROP FOREIGN KEY `fk_material_storage_location`;

-- Remove columns
ALTER TABLE `material` DROP COLUMN `is_default_storage_location`;
ALTER TABLE `material` DROP COLUMN `storage_location_id`;
```

## Notes

- The `storage_location_id` field is nullable, allowing materials to exist without a default storage location
- The `is_default_storage_location` flag helps identify which storage location is the primary/default one for the material
- The foreign key relationship ensures referential integrity with the `storage_location` table
- The index on `storage_location_id` improves query performance when filtering materials by storage location
