-- Add cost_price column to products table for import functionality
-- Migration: add_cost_price_to_products
-- Date: November 7, 2025
-- Description: Adds cost_price column to products table for GoFrugal and other import systems

DO $$
BEGIN
    -- Add cost_price column if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'products' 
        AND column_name = 'cost_price'
    ) THEN
        ALTER TABLE products ADD COLUMN cost_price NUMERIC(10,2) DEFAULT NULL;
        
        -- Add comment explaining its purpose
        COMMENT ON COLUMN products.cost_price IS 'Cost price of the product - primarily used for imports (e.g., GoFrugal COG)';
        
        RAISE NOTICE 'Added cost_price column to products table';
    ELSE
        RAISE NOTICE 'cost_price column already exists in products table';
    END IF;
END $$;