-- Migration: Make branch_id nullable in product_quantity table
-- Date: 2025-11-13
-- Purpose: Allow inventory tracking at org level without requiring branch specification
-- This fixes the issue where POS sales fail due to null branch_id constraint violation

-- Make branch_id column nullable in product_quantity table
ALTER TABLE product_quantity ALTER COLUMN branch_id DROP NOT NULL;

-- Add a comment to document the change
COMMENT ON COLUMN product_quantity.branch_id IS 'Branch ID - nullable to allow org-level inventory tracking';

-- The unique constraint uq_product_quantity_product_branch_org already handles NULL values correctly
-- as PostgreSQL treats NULL values as distinct in unique constraints

-- Add an index for queries that filter by non-null branch_id
-- (the existing idx_product_quantity_branch already handles this with a WHERE isdeleted = false clause)

-- Verify the change
DO $$
BEGIN
    -- Check if branch_id is now nullable
    IF EXISTS (
        SELECT 1 
        FROM information_schema.columns 
        WHERE table_name = 'product_quantity' 
        AND column_name = 'branch_id' 
        AND is_nullable = 'YES'
    ) THEN
        RAISE NOTICE 'SUCCESS: branch_id column is now nullable in product_quantity table';
    ELSE
        RAISE EXCEPTION 'FAILED: branch_id column is still NOT NULL in product_quantity table';
    END IF;
END $$;

-- Note: This change allows the inventory trigger to work at the org level
-- when branch_id is NULL, which is needed for POS sales that don't specify a branch