-- =====================================================================
-- Kapitano Logistic integration - a shop with several pickup branches
--
-- Two NEW tables (same as migration 2026_10_29_000001); nothing existing is altered:
--   shop_branches          extra places the captain can collect from (name, phone, map point)
--   order_branch_choices   which branch an order is collected from; no row = the main branch (the shop's own data)
--
-- Plus the screen texts (English and Arabic). Safe to run more than once. Afterwards: 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 NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `shop_branches` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` bigint unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `lat` decimal(10,7) NOT NULL,
  `lng` decimal(10,7) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `shop_branches_shop_id_index` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_branch_choices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint unsigned NOT NULL,
  `shop_branch_id` bigint unsigned NOT NULL,
  `chosen_by` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_branch_choices_order_id_unique` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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, 'a_branch_that_is_off_is_not_offered_when_you_confirm_an_order' k, 'A branch that is off is not offered when you confirm an order.' val
  UNION ALL SELECT @ar, 'a_branch_that_is_off_is_not_offered_when_you_confirm_an_order', 'الفرع المعطّل لا يُعرض عند تأكيد الطلب.'
  UNION ALL SELECT 'en', 'active', 'Active'
  UNION ALL SELECT @ar, 'active', 'مفعّل'
  UNION ALL SELECT 'en', 'add_a_branch', 'Add a branch'
  UNION ALL SELECT @ar, 'add_a_branch', 'إضافة فرع'
  UNION ALL SELECT 'en', 'address', 'Address'
  UNION ALL SELECT @ar, 'address', 'العنوان'
  UNION ALL SELECT 'en', 'branch_added', 'Branch added'
  UNION ALL SELECT @ar, 'branch_added', 'تمت إضافة الفرع'
  UNION ALL SELECT 'en', 'branch_deleted', 'Branch deleted'
  UNION ALL SELECT @ar, 'branch_deleted', 'تم حذف الفرع'
  UNION ALL SELECT 'en', 'branch_name', 'Branch name'
  UNION ALL SELECT @ar, 'branch_name', 'اسم الفرع'
  UNION ALL SELECT 'en', 'branch_updated', 'Branch updated'
  UNION ALL SELECT @ar, 'branch_updated', 'تم تحديث الفرع'
  UNION ALL SELECT 'en', 'branches_are_not_available_yet', 'Branches are not available yet'
  UNION ALL SELECT @ar, 'branches_are_not_available_yet', 'الفروع غير متاحة بعد'
  UNION ALL SELECT 'en', 'cancel', 'Cancel'
  UNION ALL SELECT @ar, 'cancel', 'إلغاء'
  UNION ALL SELECT 'en', 'close', 'Close'
  UNION ALL SELECT @ar, 'close', 'إغلاق'
  UNION ALL SELECT 'en', 'confirm_order', 'Confirm order'
  UNION ALL SELECT @ar, 'confirm_order', 'تأكيد الطلب'
  UNION ALL SELECT 'en', 'confirm', 'Confirm'
  UNION ALL SELECT @ar, 'confirm', 'تأكيد'
  UNION ALL SELECT 'en', 'confirming', 'Confirming'
  UNION ALL SELECT @ar, 'confirming', 'جارٍ التأكيد'
  UNION ALL SELECT 'en', 'delete_this_branch', 'Delete this branch?'
  UNION ALL SELECT @ar, 'delete_this_branch', 'هل تريد حذف هذا الفرع؟'
  UNION ALL SELECT 'en', 'delete', 'Delete'
  UNION ALL SELECT @ar, 'delete', 'حذف'
  UNION ALL SELECT 'en', 'edit_branch', 'Edit branch'
  UNION ALL SELECT @ar, 'edit_branch', 'تعديل الفرع'
  UNION ALL SELECT 'en', 'edit', 'Edit'
  UNION ALL SELECT @ar, 'edit', 'تعديل'
  UNION ALL SELECT 'en', 'enter_a_location', 'Enter a location'
  UNION ALL SELECT @ar, 'enter_a_location', 'أدخل موقعًا'
  UNION ALL SELECT 'en', 'enter_a_valid_phone_number', 'Enter a valid phone number'
  UNION ALL SELECT @ar, 'enter_a_valid_phone_number', 'أدخل رقم هاتف صحيحًا'
  UNION ALL SELECT 'en', 'inactive', 'Inactive'
  UNION ALL SELECT @ar, 'inactive', 'معطّل'
  UNION ALL SELECT 'en', 'latitude', 'Latitude'
  UNION ALL SELECT @ar, 'latitude', 'خط العرض'
  UNION ALL SELECT 'en', 'location', 'Location'
  UNION ALL SELECT @ar, 'location', 'الموقع'
  UNION ALL SELECT 'en', 'longitude', 'Longitude'
  UNION ALL SELECT @ar, 'longitude', 'خط الطول'
  UNION ALL SELECT 'en', 'main_branch', 'Main branch'
  UNION ALL SELECT @ar, 'main_branch', 'الفرع الرئيسي'
  UNION ALL SELECT 'en', 'name', 'Name'
  UNION ALL SELECT @ar, 'name', 'الاسم'
  UNION ALL SELECT 'en', 'options', 'Options'
  UNION ALL SELECT @ar, 'options', 'خيارات'
  UNION ALL SELECT 'en', 'orders_were_already_collected_from_this_branch_switch_it_off_instead_of_deleting_it', 'Orders were already collected from this branch. Switch it off instead of deleting it.'
  UNION ALL SELECT @ar, 'orders_were_already_collected_from_this_branch_switch_it_off_instead_of_deleting_it', 'سبق استلام طلبات من هذا الفرع. عطّله بدلًا من حذفه.'
  UNION ALL SELECT 'en', 'phone', 'Phone'
  UNION ALL SELECT @ar, 'phone', 'الهاتف'
  UNION ALL SELECT 'en', 'pickup_branches', 'Pickup branches'
  UNION ALL SELECT @ar, 'pickup_branches', 'فروع الاستلام'
  UNION ALL SELECT 'en', 'place_the_pin_on_the_map', 'Place the pin on the map'
  UNION ALL SELECT @ar, 'place_the_pin_on_the_map', 'ضع الدبوس على الخريطة'
  UNION ALL SELECT 'en', 'save', 'Save'
  UNION ALL SELECT @ar, 'save', 'حفظ'
  UNION ALL SELECT 'en', 'saving', 'Saving'
  UNION ALL SELECT @ar, 'saving', 'جارٍ الحفظ'
  UNION ALL SELECT 'en', 'search_or_click_the_map_to_place_the_pin_where_the_captain_should_collect', 'Search, or click the map to place the pin where the captain should collect.'
  UNION ALL SELECT @ar, 'search_or_click_the_map_to_place_the_pin_where_the_captain_should_collect', 'ابحث، أو انقر على الخريطة لتضع الدبوس حيث يستلم الكابتن.'
  UNION ALL SELECT 'en', 'status', 'Status'
  UNION ALL SELECT @ar, 'status', 'الحالة'
  UNION ALL SELECT 'en', 'the_captain_calls_this_number_when_collecting_the_order', 'The captain calls this number when collecting the order.'
  UNION ALL SELECT @ar, 'the_captain_calls_this_number_when_collecting_the_order', 'يتصل الكابتن بهذا الرقم عند استلام الطلب.'
  UNION ALL SELECT 'en', 'this_branch_is_not_available', 'This branch is not available'
  UNION ALL SELECT @ar, 'this_branch_is_not_available', 'هذا الفرع غير متاح'
  UNION ALL SELECT 'en', 'this_order_was_already_handed_to_the_delivery_company_so_its_pickup_branch_can_no_longer_be_changed_here_contact_kapitano_dispatch', 'This order was already handed to the delivery company, so its pickup branch can no longer be changed here. Contact Kapitano dispatch.'
  UNION ALL SELECT @ar, 'this_order_was_already_handed_to_the_delivery_company_so_its_pickup_branch_can_no_longer_be_changed_here_contact_kapitano_dispatch', 'سُلّم هذا الطلب لشركة التوصيل، فلم يعد بالإمكان تغيير فرع الاستلام من هنا. تواصل مع فريق كابيتانو.'
  UNION ALL SELECT 'en', 'where_will_the_captain_collect_this_order', 'Where will the captain collect this order?'
  UNION ALL SELECT @ar, 'where_will_the_captain_collect_this_order', 'من أين يستلم الكابتن هذا الطلب؟'
  UNION ALL SELECT 'en', 'your_branches', 'Your branches'
  UNION ALL SELECT @ar, 'your_branches', 'فروعك'
  UNION ALL SELECT 'en', 'your_shop_is_the_main_branch_set_its_location_in_shop_settings_add_the_other_places_an_order_can_be_collected_from_when_you_confirm_an_order_you_choose_where_the_captain_collects_it_if_you_choose_nothing_it_is_the_main_branch', 'Your shop is the main branch (set its location in Shop Settings). Add the other places an order can be collected from. When you confirm an order you choose where the captain collects it; if you choose nothing, it is the main branch.'
  UNION ALL SELECT @ar, 'your_shop_is_the_main_branch_set_its_location_in_shop_settings_add_the_other_places_an_order_can_be_collected_from_when_you_confirm_an_order_you_choose_where_the_captain_collects_it_if_you_choose_nothing_it_is_the_main_branch', 'متجرك هو الفرع الرئيسي (حدّد موقعه من إعدادات المتجر). أضف الأماكن الأخرى التي يمكن استلام الطلب منها. عند تأكيد الطلب تختار من أين يستلمه الكابتن، وإن لم تختر شيئًا فهو الفرع الرئيسي.'
  UNION ALL SELECT 'en', 'optional', 'optional'
  UNION ALL SELECT @ar, 'optional', 'اختياري'
) v
WHERE NOT EXISTS (SELECT 1 FROM `translations` t WHERE t.lang = v.lang AND t.lang_key = v.k);

-- Validation messages of the branch form.
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, 'enter_the_branch_name' k, 'Enter the branch name' val
  UNION ALL SELECT @ar, 'enter_the_branch_name', 'أدخل اسم الفرع'
  UNION ALL SELECT 'en', 'the_branch_name_is_too_long', 'The branch name is too long'
  UNION ALL SELECT @ar, 'the_branch_name_is_too_long', 'اسم الفرع طويل جدًا'
  UNION ALL SELECT 'en', 'the_address_is_too_long', 'The address is too long'
  UNION ALL SELECT @ar, 'the_address_is_too_long', 'العنوان طويل جدًا'
) v
WHERE NOT EXISTS (SELECT 1 FROM `translations` t WHERE t.lang = v.lang AND t.lang_key = v.k);

-- The optional pickup location and branches on the forms that create a store.
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, 'pickup_location_and_branches_optional' k, 'Pickup location and branches (optional)' val
  UNION ALL SELECT @ar, 'pickup_location_and_branches_optional', 'موقع الاستلام والفروع (اختياري)'
  UNION ALL SELECT 'en', 'where_the_captain_collects_orders_the_shop_itself_is_the_main_branch_pin_its_location_here_and_add_any_other_branches_all_of_this_is_optional_and_can_be_done_later', 'Where the captain collects orders. The shop itself is the main branch: pin its location here, and add any other branches. All of this is optional and can be done later.'
  UNION ALL SELECT @ar, 'where_the_captain_collects_orders_the_shop_itself_is_the_main_branch_pin_its_location_here_and_add_any_other_branches_all_of_this_is_optional_and_can_be_done_later', 'المكان الذي يستلم منه الكابتن الطلبات. المتجر نفسه هو الفرع الرئيسي: حدّد موقعه هنا، وأضف أي فروع أخرى. كل هذا اختياري ويمكن إنجازه لاحقًا.'
  UNION ALL SELECT 'en', 'main_branch_location', 'Main branch location'
  UNION ALL SELECT @ar, 'main_branch_location', 'موقع الفرع الرئيسي'
  UNION ALL SELECT 'en', 'branch', 'Branch'
  UNION ALL SELECT @ar, 'branch', 'الفرع'
  UNION ALL SELECT 'en', 'remove_this_branch', 'Remove this branch'
  UNION ALL SELECT @ar, 'remove_this_branch', 'إزالة هذا الفرع'
  UNION ALL SELECT 'en', 'you_can_add_up_to_10_branches_here', 'You can add up to 10 branches here'
  UNION ALL SELECT @ar, 'you_can_add_up_to_10_branches_here', 'يمكنك إضافة حتى 10 فروع هنا'
) v
WHERE NOT EXISTS (SELECT 1 FROM `translations` t WHERE t.lang = v.lang AND t.lang_key = v.k);