-- Adds parent/child menu category support to an already-provisioned database.
-- Only needed if your `menu_categories` table predates this change — a fresh
-- `mysql -u root -p < schema.sql` import already includes this column.
--
-- Usage: mysql -u root -p petzy_pos < add_category_parent_id.sql

ALTER TABLE menu_categories
    ADD COLUMN parent_id INT UNSIGNED NULL AFTER outlet_id,
    ADD KEY idx_mc_parent (parent_id),
    ADD CONSTRAINT fk_mc_parent FOREIGN KEY (parent_id) REFERENCES menu_categories(id) ON DELETE SET NULL;