-- Migration: Add uom_group_code column to catalogue_ref table
-- Date: 2025-01-31
-- Description: Adds uom_group_code column to store UOM group reference from UOM master system

-- Add uom_group_code column to trans.catalogue_ref
ALTER TABLE trans.catalogue_ref 
ADD COLUMN IF NOT EXISTS uom_group_code TEXT;

-- Add comment to document the column purpose
COMMENT ON COLUMN trans.catalogue_ref.uom_group_code IS 'UOM group code reference from UOM master system (e.g., UOM-VOL-000001, UOM-WGT-000001)';

-- Create index for better query performance on uom_group_code
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_catalogue_ref_uom_group_code 
ON trans.catalogue_ref (uom_group_code);

-- Verify the column was added
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns 
WHERE table_schema = 'trans' 
  AND table_name = 'catalogue_ref' 
  AND column_name = 'uom_group_code';

-- Example of how the column will be populated:
-- UPDATE trans.catalogue_ref 
-- SET uom_group_code = 'UOM-QTY-000001' 
-- WHERE inventory->>'unit' = 'PCS';
-- 
-- UPDATE trans.catalogue_ref 
-- SET uom_group_code = 'UOM-VOL-000001' 
-- WHERE inventory->>'unit' IN ('ML', 'LITER');
-- 
-- UPDATE trans.catalogue_ref 
-- SET uom_group_code = 'UOM-WGT-000001' 
-- WHERE inventory->>'unit' IN ('GRAM', 'KG');