-- =====================================================
-- Migration: Add Storage Location Fields to GRN Tables
-- Date: 2026-02-26
-- Description: Adds storage_location_id fields to goods_receipt_note 
--              (header/common location) and goods_receipt_note_item 
--              (item-level location) tables
-- =====================================================

-- 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 for goods_receipt_note.storage_location_id
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 for goods_receipt_note_item.storage_location_id
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 better 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`);

-- =====================================================
-- Rollback Script (if needed)
-- =====================================================
-- DROP INDEX `idx_grn_item_storage_location_id` ON `goods_receipt_note_item`;
-- DROP INDEX `idx_grn_storage_location_id` ON `goods_receipt_note`;
-- 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`;
-- ALTER TABLE `goods_receipt_note_item` DROP COLUMN `storage_location_id`;
-- ALTER TABLE `goods_receipt_note` DROP COLUMN `storage_location_id`;
-- =====================================================
