# GRN Storage Location Schema

## Overview
This document describes the schema changes for adding storage location fields to the Goods Receipt Note (GRN) tables.

## Database Schema

### Goods Receipt Note Table - New Field

#### `storage_location_id` (Header/Common Location)
- **Type**: `INT(11)`
- **Nullable**: `YES` (NULL allowed)
- **Default**: `NULL`
- **Foreign Key**: References `storage_location.id`
- **On Update**: `CASCADE`
- **On Delete**: `SET NULL`
- **Comment**: Common storage location for all items (can be overridden per item)
- **Index**: `idx_grn_storage_location_id`

### Goods Receipt Note Item Table - New Field

#### `storage_location_id` (Item-Level Location)
- **Type**: `INT(11)`
- **Nullable**: `YES` (NULL allowed)
- **Default**: `NULL`
- **Foreign Key**: References `storage_location.id`
- **On Update**: `CASCADE`
- **On Delete**: `SET NULL`
- **Comment**: Storage location for this item (overrides header storage location if set)
- **Index**: `idx_grn_item_storage_location_id`

### SQL Schema Definition

```sql
-- Add storage_location_id to goods_receipt_note table (header/common location)
ALTER TABLE `goods_receipt_note` 
ADD COLUMN `storage_location_id` INT(11) NULL DEFAULT NULL 
COMMENT 'Common storage location for all items (can be overridden per item)' 
AFTER `vehicle_number`;

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

-- Add storage_location_id to goods_receipt_note_item table (item-level location)
ALTER TABLE `goods_receipt_note_item` 
ADD COLUMN `storage_location_id` INT(11) NULL DEFAULT NULL 
COMMENT 'Storage location for this item (overrides header storage location if set)' 
AFTER `batch_number`;

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

-- Add indexes for query performance
CREATE INDEX `idx_grn_storage_location_id` 
ON `goods_receipt_note` (`storage_location_id`);

CREATE INDEX `idx_grn_item_storage_location_id` 
ON `goods_receipt_note_item` (`storage_location_id`);
```

## Sequelize Model Definitions

### GoodsReceiptNote Model

```javascript
storage_location_id: {
  type: DataTypes.INTEGER,
  allowNull: true,
  references: {
    model: 'StorageLocation',
    key: 'id'
  },
  comment: 'Common storage location for all items (can be overridden per item)'
}
```

### GoodsReceiptNoteItem Model

```javascript
storage_location_id: {
  type: DataTypes.INTEGER,
  allowNull: true,
  references: {
    model: 'StorageLocation',
    key: 'id'
  },
  comment: 'Storage location for this item (overrides header storage location if set)'
}
```

## Relationships

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

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

## Usage

### Frontend Form Fields

1. **Header Storage Location (Common Location)**:
   - Dropdown in the header section
   - Labeled as "Storage Location (Common)"
   - When selected, automatically fills all item lines that don't have a storage location
   - Helper text: "Common storage location for all items (can be overridden per item)"

2. **Item-Level Storage Location**:
   - Dropdown in each item row
   - If not set, defaults to header storage location
   - Can override the common location for specific items

### Data Flow

1. User selects a common storage location in the header
2. All items without a storage location are automatically filled with the common location
3. User can override the storage location for individual items
4. On save:
   - Header `storage_location_id` is stored in `goods_receipt_note` table
   - Item `storage_location_id` is stored in `goods_receipt_note_item` table
   - If item has no storage location, it can use the header location (application logic)

## Migration Files

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

## Rollback

To rollback these changes:

```sql
-- Remove indexes
DROP INDEX `idx_grn_item_storage_location_id` ON `goods_receipt_note_item`;
DROP INDEX `idx_grn_storage_location_id` ON `goods_receipt_note`;

-- Remove foreign key constraints
ALTER TABLE `goods_receipt_note_item` DROP FOREIGN KEY `fk_grn_item_storage_location`;
ALTER TABLE `goods_receipt_note` DROP FOREIGN KEY `fk_grn_storage_location`;

-- Remove columns
ALTER TABLE `goods_receipt_note_item` DROP COLUMN `storage_location_id`;
ALTER TABLE `goods_receipt_note` DROP COLUMN `storage_location_id`;
```

## Notes

- The header `storage_location_id` serves as a default/common location for all items
- Item-level `storage_location_id` can override the header location
- Both fields are nullable, allowing flexibility in data entry
- The foreign key relationships ensure referential integrity with the `storage_location` table
- Indexes on both fields improve query performance when filtering GRNs by storage location
- When loading items from Gate Entry, storage locations are preserved if they exist
