docs/database/migrations/migration_add_inventory_column.sql

-- Migration: Add inventory JSON column to catalogue_ref table
-- Date: 2025-12-19
-- Description: Add inventory column to store multi-level inventory configurations from MongoDB

-- Add inventory column as JSONB type (better performance and indexing support)
ALTER TABLE trans.catalogue_ref 
ADD COLUMN IF NOT EXISTS inventory JSONB;

-- Add comment to document the column
COMMENT ON COLUMN trans.catalogue_ref.inventory IS 'Multi-level inventory configuration with ncnf, cnf, distributor, and retail levels';

-- Create index on inventory column for better query performance
CREATE INDEX IF NOT EXISTS idx_catalogue_ref_inventory 
ON trans.catalogue_ref USING GIN (inventory);

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