-- v1.16 Item Extra Properties: Category/Unit UI + Opening Balance + Brand/Notes
-- Run after: schema.sql + migrate_v1_13_warehouse.sql (+ others)
SET NAMES utf8mb4;

ALTER TABLE items
  ADD COLUMN brand VARCHAR(100) NULL AFTER name,
  ADD COLUMN notes TEXT NULL AFTER price,
  ADD COLUMN opening_qty DECIMAL(14,3) NOT NULL DEFAULT 0 AFTER low_stock_level,
  ADD COLUMN opening_cost DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER opening_qty,
  ADD COLUMN opening_warehouse_id BIGINT UNSIGNED NULL AFTER opening_cost,
  ADD COLUMN opening_posted TINYINT(1) NOT NULL DEFAULT 0 AFTER opening_warehouse_id,
  ADD COLUMN opening_posted_at TIMESTAMP NULL DEFAULT NULL AFTER opening_posted;

ALTER TABLE items
  ADD CONSTRAINT fk_items_opening_wh FOREIGN KEY (opening_warehouse_id) REFERENCES warehouses(id)
  ON DELETE SET NULL;

CREATE INDEX idx_items_category ON items(category_id);
CREATE INDEX idx_items_unit ON items(default_unit_id);