-- Keep the ingredient name as printed on the product packaging alongside the
-- canonical INCI ingredient it was matched to.
--
-- A label saying "Alcohol" matches the ingredient "Ethanol" through the
-- synonym "Alcohol"; "Aqua" matches "Water". Until now only the resulting
-- id_ingredient was stored, so the user had no way to check the app's list
-- against the text on the bottle.
--
-- All columns are nullable: existing rows keep working and every reader falls
-- back to the canonical ingredient name when label_text is NULL.

ALTER TABLE `product_ingredients`
    ADD COLUMN `label_text`            VARCHAR(255) NULL DEFAULT NULL AFTER `id_ingredient`,
    ADD COLUMN `id_ingredient_synonym` INT          NULL DEFAULT NULL AFTER `label_text`,
    ADD COLUMN `match_source`          VARCHAR(16)  NULL DEFAULT NULL AFTER `id_ingredient_synonym`,
    ADD KEY `idx_pi_synonym` (`id_ingredient_synonym`),
    ADD KEY `idx_pi_product` (`id_product`);

-- match_source values: 'name', 'synonym', 'gemini_inci', 'manual', 'import'.
--
-- No unique key on (id_product, id_ingredient): a single label may legitimately
-- list two strings ("Aqua" and "Water") that resolve to the same ingredient,
-- and those are now two meaningfully different rows.
--
-- Deliberately no backfill of label_text: for rows created before this change
-- we genuinely do not know what the packaging said, and NULL says so.
