-- A seller's account manager: shops.account_manager_id (the user id of the E-commerce staff member who looks after the seller)
-- and the texts of the dropdown, the column and the filter, English and Arabic.
-- A reference only: every account manager can still open every seller. Safe to run more than once.
-- Same change as the migration 2026_11_01_000002_add_account_manager_id_to_shops. Then: php artisan cache:clear

-- Arabic is stored under the code the database holds today: 'sa' until the migration
-- 2026_10_05_000001_convert_arabic_language_code_to_ar has run, 'ar' after it (App\Support\ArabicLanguageCode).
-- @ar is that code, so the Arabic texts below are read whichever side of the rename the server is on.
SET @ar := IF((SELECT COUNT(*) FROM `languages` WHERE `code` = 'sa') > 0, 'sa', 'ar');

SET @has_col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'shops' AND COLUMN_NAME = 'account_manager_id');
SET @ddl := IF(@has_col = 0, 'ALTER TABLE `shops` ADD COLUMN `account_manager_id` BIGINT UNSIGNED NULL, ADD INDEX `shops_account_manager_id_index` (`account_manager_id`)', 'SELECT 1');
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

INSERT INTO `translations` (`lang`, `lang_key`, `lang_value`, `created_at`, `updated_at`)
SELECT v.lang, v.k, v.val, NOW(), NOW() FROM (
  SELECT 'en' lang, 'account_manager' k, 'Account manager' val
  UNION ALL SELECT @ar, 'account_manager', 'مدير الحساب'
  UNION ALL SELECT 'en', 'no_account_manager', 'No account manager'
  UNION ALL SELECT @ar, 'no_account_manager', 'بدون مدير حساب'
  UNION ALL SELECT 'en', 'filter_by_account_manager', 'Filter by Account Manager'
  UNION ALL SELECT @ar, 'filter_by_account_manager', 'تصفية حسب مدير الحساب'
  UNION ALL SELECT 'en', 'looks_after_this_seller_every_account_manager_can_still_open_every_seller', 'Looks after this seller. Every account manager can still open every seller.'
  UNION ALL SELECT @ar, 'looks_after_this_seller_every_account_manager_can_still_open_every_seller', 'يتابع هذا التاجر. يبقى بإمكان كل مدير حساب فتح أي تاجر.'
  UNION ALL SELECT 'en', 'choose_an_active_account_manager', 'Choose an active account manager'
  UNION ALL SELECT @ar, 'choose_an_active_account_manager', 'اختر مدير حساب فعّالًا'
) v
WHERE NOT EXISTS (SELECT 1 FROM `translations` t WHERE t.lang = v.lang AND t.lang_key = v.k);