-- AlterTable (guarded — only runs if cg_tbl_products exists in this environment)
DROP PROCEDURE IF EXISTS _carg_maybe_add_product_columns;

CREATE PROCEDURE _carg_maybe_add_product_columns()
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'cg_tbl_products'
  ) THEN
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'cg_tbl_products'
        AND COLUMN_NAME = 'assembly_group_node_id'
    ) THEN
      ALTER TABLE `cg_tbl_products` ADD COLUMN `assembly_group_node_id` BIGINT UNSIGNED NULL;
      CREATE INDEX `idx_assembly_group_node_id` ON `cg_tbl_products`(`assembly_group_node_id`);
    END IF;
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'cg_tbl_products'
        AND COLUMN_NAME = 'generic_article_id'
    ) THEN
      ALTER TABLE `cg_tbl_products` ADD COLUMN `generic_article_id` BIGINT UNSIGNED NULL;
      CREATE INDEX `idx_generic_article_id` ON `cg_tbl_products`(`generic_article_id`);
    END IF;
  END IF;
END;

CALL _carg_maybe_add_product_columns();
DROP PROCEDURE IF EXISTS _carg_maybe_add_product_columns;