BEGIN;

ALTER TABLE IF EXISTS warehouse.warehouses
    ADD COLUMN IF NOT EXISTS non_storage_capacity numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS usable_capacity numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS occupancy_factor_percent numeric(8,4) DEFAULT 85,
    ADD COLUMN IF NOT EXISTS capacity_buffer_percent numeric(8,4) DEFAULT 20,
    ADD COLUMN IF NOT EXISTS effective_capacity numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS floor_area numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS max_stack_height numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS safety_clearance_height numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS rack_count integer DEFAULT 0,
    ADD COLUMN IF NOT EXISTS shelf_count integer DEFAULT 0,
    ADD COLUMN IF NOT EXISTS rack_capacity numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS shelf_capacity numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS vertical_capacity numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS rack_shelf_capacity numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS capacity_policy text DEFAULT 'dynamic',
    ADD COLUMN IF NOT EXISTS rotation_profile text DEFAULT 'dynamic',
    ADD COLUMN IF NOT EXISTS capacity_notes text,
    ADD COLUMN IF NOT EXISTS capacity_last_reviewed_at timestamp without time zone;

ALTER TABLE IF EXISTS inventory.items
    ADD COLUMN IF NOT EXISTS storage_capacity_per_unit numeric(18,6) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS storage_capacity_uom text;

ALTER TABLE IF EXISTS purchases.purchase_order_items
    ADD COLUMN IF NOT EXISTS storage_capacity_required numeric(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS storage_capacity_uom text;

UPDATE warehouse.warehouses
SET
    non_storage_capacity = GREATEST(COALESCE(non_storage_capacity, 0), 0),
    floor_area = CASE
        WHEN COALESCE(floor_area, 0) > 0 THEN floor_area
        ELSE COALESCE(total_capacity, 0)
    END,
    occupancy_factor_percent = COALESCE(
        NULLIF(occupancy_factor_percent, 0),
        CASE
            WHEN LOWER(COALESCE(capacity_policy, rotation_profile, warehouse_type, type, 'dynamic')) IN ('static', 'bulk', 'reserve') THEN 92
            ELSE 85
        END
    ),
    capacity_buffer_percent = COALESCE(capacity_buffer_percent, 20),
    usable_capacity = CASE
        WHEN COALESCE(usable_capacity, 0) > 0 THEN usable_capacity
        ELSE GREATEST(COALESCE(total_capacity, 0) - COALESCE(non_storage_capacity, 0), 0)
    END,
    vertical_capacity = CASE
        WHEN COALESCE(vertical_capacity, 0) > 0 THEN vertical_capacity
        WHEN COALESCE(max_stack_height, 0) > COALESCE(safety_clearance_height, 0) THEN
            GREATEST(COALESCE(total_capacity, 0) - COALESCE(non_storage_capacity, 0), 0)
            * GREATEST(COALESCE(max_stack_height, 0) - COALESCE(safety_clearance_height, 0), 0)
        ELSE 0
    END,
    rack_shelf_capacity = CASE
        WHEN COALESCE(rack_shelf_capacity, 0) > 0 THEN rack_shelf_capacity
        ELSE GREATEST(COALESCE(rack_capacity, 0) + COALESCE(shelf_capacity, 0), 0)
    END,
    effective_capacity = CASE
        WHEN COALESCE(effective_capacity, 0) > 0 THEN effective_capacity
        ELSE ROUND((
            CASE
                WHEN COALESCE(rack_shelf_capacity, 0) > 0 THEN rack_shelf_capacity
                WHEN COALESCE(vertical_capacity, 0) > 0 THEN vertical_capacity
                WHEN COALESCE(usable_capacity, 0) > 0 THEN usable_capacity
                ELSE GREATEST(COALESCE(total_capacity, 0) - COALESCE(non_storage_capacity, 0), 0)
            END
        ) * COALESCE(NULLIF(occupancy_factor_percent, 0), 85) / 100, 4)
    END,
    updated_at = CURRENT_TIMESTAMP
WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE INDEX IF NOT EXISTS idx_warehouse_warehouses_capacity
    ON warehouse.warehouses (tenant_id, status, is_active);

COMMIT;