-- =====================================================
-- Migration: Add Storage Location Fields to Material Table
-- Date: 2026-02-26
-- Description: Adds storage_location_id and is_default_storage_location 
--              fields to the material table for default storage location management
-- =====================================================

-- 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 for storage_location_id
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 better query performance
CREATE INDEX `idx_material_storage_location_id` 
ON `material` (`storage_location_id`);

-- =====================================================
-- Rollback Script (if needed)
-- =====================================================
-- DROP INDEX `idx_material_storage_location_id` ON `material`;
-- ALTER TABLE `material` DROP FOREIGN KEY `fk_material_storage_location`;
-- ALTER TABLE `material` DROP COLUMN `is_default_storage_location`;
-- ALTER TABLE `material` DROP COLUMN `storage_location_id`;
-- =====================================================
