-- Slice 8a · Track PC scrub state on the client PO + its items
-- Idempotent via INFORMATION_SCHEMA guards.

SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE goods_client_pos
        ADD COLUMN scrubbed_at TIMESTAMP NULL AFTER notes,
        ADD COLUMN scrubbed_by_id INT NOT NULL DEFAULT 0 AFTER scrubbed_at,
        ADD COLUMN scrubbed_by_name VARCHAR(120) NOT NULL DEFAULT "" AFTER scrubbed_by_id,
        ADD COLUMN scrub_notes VARCHAR(500) NOT NULL DEFAULT "" AFTER scrubbed_by_name',
    'SELECT "goods_client_pos.scrubbed_at already exists"'
) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'goods_client_pos' AND column_name = 'scrubbed_at');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE goods_client_po_items
        ADD COLUMN margin_pct DECIMAL(6,2) NOT NULL DEFAULT 0.00 AFTER unit_price_cog,
        ADD COLUMN scrubbed_at TIMESTAMP NULL AFTER notes',
    'SELECT "goods_client_po_items.margin_pct already exists"'
) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'goods_client_po_items' AND column_name = 'margin_pct');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* Backfill: any existing item where cog>0 and sell>0 gets an implied margin */
UPDATE goods_client_po_items
   SET margin_pct = ROUND(((unit_price_sell - unit_price_cog) / unit_price_sell) * 100, 2)
 WHERE unit_price_sell > 0 AND unit_price_cog > 0 AND margin_pct = 0;
