-- Migration: Add source_reference_id to inventory_items
-- Description: Tracks which GRN or Production Order created each inventory item for reprint functionality
-- Date: 2026-01-20

-- Add source_reference_id field to track GRN ID or Production Order ID
ALTER TABLE `inventory_items` 
ADD COLUMN `source_reference_id` BIGINT NULL 
AFTER `source`,
ADD INDEX `idx_source_reference` (`source`, `source_reference_id`);

-- Backfill existing data: Try to populate source_reference_id from inventory_movements
-- For PRODUCTION items
UPDATE `inventory_items` i
INNER JOIN `inventory_movements` im 
  ON i.id = im.inventory_item_id 
  AND im.reason = 'PRODUCTION_OUTPUT'
  AND i.source = 'PRODUCTION'
SET i.source_reference_id = im.reference_id
WHERE i.source_reference_id IS NULL;

-- For GRN items
UPDATE `inventory_items` i
INNER JOIN `inventory_movements` im 
  ON i.id = im.inventory_item_id 
  AND im.reason = 'GRN'
  AND i.source = 'GRN'
SET i.source_reference_id = im.reference_id
WHERE i.source_reference_id IS NULL;
