docs/database/migrations/migration_add_pricing_levels_column.sql

-- Migration: Add pricing_levels JSON column to catalogue_ref table
-- Description: Adds pricing_levels column to store enhanced pricing data as JSON
-- Date: 2024-12-18

-- Add pricing_levels JSON column to trans.catalogue_ref
ALTER TABLE trans.catalogue_ref 
ADD COLUMN IF NOT EXISTS pricing_levels JSON;

-- Create indexes for better query performance on pricing_levels
-- Index on currency field within JSON
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_catalogue_ref_pricing_levels_currency 
ON trans.catalogue_ref USING GIN ((pricing_levels->>'currency'));

-- Index on MRP field within JSON (cast to numeric for range queries)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_catalogue_ref_pricing_levels_mrp 
ON trans.catalogue_ref USING BTREE (CAST(pricing_levels->>'mrp' AS NUMERIC));

-- Index on pricing levels structure (for queries on specific levels like 'retail', 'distributor')
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_catalogue_ref_pricing_levels_gin 
ON trans.catalogue_ref USING GIN (pricing_levels);

-- Add comment to document the column purpose
COMMENT ON COLUMN trans.catalogue_ref.pricing_levels IS 'Enhanced pricing levels data stored as JSON with currency, MRP, and level-specific pricing (retail, distributor, etc.)';

-- Example of the JSON structure stored:
-- {
--   "currency": "INR",
--   "mrp": 399,
--   "levels": {
--     "retail": {
--       "purchase_price": 260,
--       "trade_margin": 20,
--       "selling_price": 299,
--       "retail_price": 349,
--       "retail_margin": 30,
--       "max_discount_pct": 15
--     },
--     "distributor": {
--       "purchase_price": 230,
--       "trade_margin": 25,
--       "selling_price": 279,
--       "retail_price": 349,
--       "retail_margin": 30,
--       "max_discount_pct": 20
--     }
--   }
-- }