-- Migration: Add sub_location_id to remaining tables (Phase 4)
-- Description: Adds sub-location support to inventory transactions and job positions
-- Date: 2026-01-18

-- Inventory transactions table
ALTER TABLE inventory_transactions 
ADD COLUMN sub_location_id INT UNSIGNED NULL AFTER location_id,
ADD KEY idx_sub_location_id (sub_location_id);

ALTER TABLE inventory_transactions
ADD CONSTRAINT fk_inventory_transactions_sub_location 
FOREIGN KEY (sub_location_id) REFERENCES sub_locations(id) ON DELETE SET NULL;

-- Job positions table
ALTER TABLE job_positions 
ADD COLUMN sub_location_id INT UNSIGNED NULL AFTER location_id,
ADD KEY idx_sub_location_id (sub_location_id);

ALTER TABLE job_positions
ADD CONSTRAINT fk_job_positions_sub_location 
FOREIGN KEY (sub_location_id) REFERENCES sub_locations(id) ON DELETE SET NULL;