BEGIN;

ALTER TABLE product_categories ADD COLUMN parent_id UUID REFERENCES product_categories(id) ON DELETE CASCADE;
CREATE INDEX idx_product_categories_parent ON product_categories(parent_id);

-- Drop the unique constraint so subcategories can share names across parents
ALTER TABLE product_categories DROP CONSTRAINT product_categories_tenant_id_name_key;
ALTER TABLE product_categories ADD CONSTRAINT product_categories_tenant_parent_name_key UNIQUE(tenant_id, parent_id, name);

VALUES ('011', 'Subcategories: parent_id on product_categories');

COMMIT;
