-- Добавление поддержки множественных ответственных для контрагентов
ALTER TABLE contractors
  ADD COLUMN responsible_managers JSON NULL AFTER web_link;

ALTER TABLE contractors_pending
  ADD COLUMN responsible_managers JSON NULL AFTER web_link;

-- Миграция существующих одиночных полей manager_* в новый JSON-массив
UPDATE contractors
SET responsible_managers = CASE
  WHEN manager_name IS NOT NULL AND TRIM(manager_name) <> '' THEN JSON_ARRAY(
    JSON_OBJECT(
      'name', manager_name,
      'phone', NULLIF(TRIM(manager_phone), ''),
      'email', NULLIF(TRIM(manager_email), '')
    )
  )
  ELSE JSON_ARRAY()
END
WHERE responsible_managers IS NULL;

UPDATE contractors_pending
SET responsible_managers = CASE
  WHEN manager_name IS NOT NULL AND TRIM(manager_name) <> '' THEN JSON_ARRAY(
    JSON_OBJECT(
      'name', manager_name,
      'phone', NULLIF(TRIM(manager_phone), ''),
      'email', NULLIF(TRIM(manager_email), '')
    )
  )
  ELSE JSON_ARRAY()
END
WHERE responsible_managers IS NULL;
