-- Richer delivery confirmation on a delivery note: record the CONDITION of the
-- goods on receipt (good / damaged / partial) and any notes the receiver adds.
-- Both nullable, set only when a note is marked delivered. Safe / idempotent.

SET @has := (SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE() AND table_name = 'delivery_notes' AND column_name = 'delivery_condition');
SET @sql := IF(@has = 0, 'ALTER TABLE delivery_notes ADD COLUMN delivery_condition VARCHAR(24) NULL AFTER received_by', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @has2 := (SELECT COUNT(*) FROM information_schema.columns
              WHERE table_schema = DATABASE() AND table_name = 'delivery_notes' AND column_name = 'received_notes');
SET @sql2 := IF(@has2 = 0, 'ALTER TABLE delivery_notes ADD COLUMN received_notes VARCHAR(500) NULL AFTER delivery_condition', 'SELECT 1');
PREPARE s2 FROM @sql2; EXECUTE s2; DEALLOCATE PREPARE s2;