-- AGG-UAE — Phase M7-p6a-h2-p13-h4 — CRTSUBITEMS.CRTSUBITEM_ICONPATH
--
-- Adds an optional per-SubItem icon path so the Auto Schedule grid
-- can render a tiny iconic image alongside each task entry (e.g.
-- distinct silhouettes for Windshield vs Front Door Left vs Quarter
-- Right). Stored as a relative path under /erp/uploads/subitems/.
-- API surfaces it as an absolute URL on schedule responses.
--
-- Idempotent via information_schema column probe.

SET @col_exists := (
    SELECT COUNT(*)
      FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'CRTSUBITEMS'
       AND COLUMN_NAME  = 'CRTSUBITEM_ICONPATH'
);

SET @sql := IF(
    @col_exists = 0,
    "ALTER TABLE CRTSUBITEMS ADD COLUMN CRTSUBITEM_ICONPATH VARCHAR(255) NULL AFTER CRTSUBITEM_NAME",
    "SELECT 'CRTSUBITEMS.CRTSUBITEM_ICONPATH already present — skipping' AS note"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Verify
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME   = 'CRTSUBITEMS'
   AND COLUMN_NAME  = 'CRTSUBITEM_ICONPATH';