-- v1.14 Hierarchy: Categories (parent/child) + Units (base/factor)
SET NAMES utf8mb4;

-- Categories: add parent_id (self reference)
ALTER TABLE categories
  ADD COLUMN parent_id BIGINT UNSIGNED NULL AFTER id;

ALTER TABLE categories
  ADD CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id)
  ON DELETE SET NULL;

CREATE INDEX idx_categories_parent ON categories(parent_id);

-- Units: base unit + factor to base
ALTER TABLE units
  ADD COLUMN base_unit_id BIGINT UNSIGNED NULL AFTER id,
  ADD COLUMN factor_to_base DECIMAL(18,6) NOT NULL DEFAULT 1 AFTER base_unit_id;

ALTER TABLE units
  ADD CONSTRAINT fk_units_base FOREIGN KEY (base_unit_id) REFERENCES units(id)
  ON DELETE SET NULL;

CREATE INDEX idx_units_base ON units(base_unit_id);

-- Ensure existing rows have factor 1
UPDATE units SET factor_to_base = 1 WHERE factor_to_base IS NULL OR factor_to_base = 0;

-- Example (optional):
-- Suppose 'قطعة' exists and id=1:
-- UPDATE units SET base_unit_id=1, factor_to_base=12 WHERE name='دستة';