-- =====================================================================
-- Account deactivation ("Delete my account" no longer deletes)
--
-- The customer's "delete my account" (website and app) now deactivates the
-- account: same answer, no login, and registering again with the same phone
-- brings the same account back with its orders and coupon history
-- (App\Utility\AccountDeactivationUtility).
--
-- 1. users.deactivated_at / users.deactivated_by: two nullable columns, nothing
--    else in the table changes. deactivated_by NULL = the customer did it.
-- 2. Permission `deactivate_customer` (Staff Roles > Customer), granted to every
--    role and user that can ban a customer today.
-- 3. delete_customer, delete_seller and delete_staff are taken from every role and
--    user: hard deletion becomes Super Admin only. What was taken is recorded once
--    in business_settings (`revoked_delete_permission_grants`); while that record
--    exists nothing is revoked again, so a grant the Super Admin makes afterwards
--    survives a re-run. The migration's down() gives the recorded grants back.
-- 4. Arabic UI strings.
--
-- Same rows as migrations 2026_10_01_000001_add_deactivation_to_users_table and
-- 2026_10_01_000002_add_account_deactivation_permissions.
-- Re-running the file changes nothing. Needs MySQL 5.7.22+ / 8 (JSON_OBJECTAGG).
-- After running: php artisan cache:clear (permissions and translations are cached).
-- =====================================================================

SET NAMES utf8mb4;

-- 1. Columns (MySQL has no ADD COLUMN IF NOT EXISTS) ----------------------

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'deactivated_at') = 0,
    'ALTER TABLE `users` ADD COLUMN `deactivated_at` TIMESTAMP NULL DEFAULT NULL AFTER `banned`',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'deactivated_by') = 0,
    'ALTER TABLE `users` ADD COLUMN `deactivated_by` INT UNSIGNED NULL DEFAULT NULL AFTER `deactivated_at`',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. deactivate_customer, granted like ban_customer -----------------------

INSERT INTO `permissions` (`name`, `section`, `guard_name`, `created_at`, `updated_at`)
SELECT 'deactivate_customer', 'customer', 'web', NOW(), NOW()
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM `permissions` WHERE `name` = 'deactivate_customer' AND `guard_name` = 'web'
);

INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT DISTINCT p.id, src.role_id
FROM `permissions` p
JOIN `permissions` b ON b.name = 'ban_customer' AND b.guard_name = 'web'
JOIN `role_has_permissions` src ON src.permission_id = b.id
WHERE p.name = 'deactivate_customer' AND p.guard_name = 'web'
  AND NOT EXISTS (SELECT 1 FROM `role_has_permissions` x WHERE x.permission_id = p.id AND x.role_id = src.role_id);

INSERT INTO `model_has_permissions` (`permission_id`, `model_type`, `model_id`)
SELECT DISTINCT p.id, src.model_type, src.model_id
FROM `permissions` p
JOIN `permissions` b ON b.name = 'ban_customer' AND b.guard_name = 'web'
JOIN `model_has_permissions` src ON src.permission_id = b.id
WHERE p.name = 'deactivate_customer' AND p.guard_name = 'web'
  AND NOT EXISTS (
      SELECT 1 FROM `model_has_permissions` x
      WHERE x.permission_id = p.id AND x.model_type = src.model_type AND x.model_id = src.model_id
  );

-- 3. Hard deletion becomes Super Admin only (once) -------------------------

SET @had_record := (SELECT COUNT(*) FROM `business_settings` WHERE `type` = 'revoked_delete_permission_grants');

INSERT INTO `business_settings` (`type`, `value`, `created_at`, `updated_at`)
SELECT 'revoked_delete_permission_grants',
       COALESCE(JSON_OBJECTAGG(p.name, JSON_OBJECT(
           'roles',  COALESCE((SELECT JSON_ARRAYAGG(rp.role_id)
                               FROM `role_has_permissions` rp WHERE rp.permission_id = p.id), JSON_ARRAY()),
           'models', COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT('model_type', mp.model_type, 'model_id', mp.model_id))
                               FROM `model_has_permissions` mp WHERE mp.permission_id = p.id), JSON_ARRAY())
       )), JSON_OBJECT()),
       NOW(), NOW()
FROM `permissions` p
WHERE @had_record = 0
  AND p.name IN ('delete_customer', 'delete_seller', 'delete_staff') AND p.guard_name = 'web'
-- An aggregate without GROUP BY always returns a row; no matching permission = no record.
HAVING COUNT(*) > 0;

DELETE rp FROM `role_has_permissions` rp
JOIN `permissions` p ON p.id = rp.permission_id
WHERE @had_record = 0
  AND p.name IN ('delete_customer', 'delete_seller', 'delete_staff') AND p.guard_name = 'web';

DELETE mp FROM `model_has_permissions` mp
JOIN `permissions` p ON p.id = mp.permission_id
WHERE @had_record = 0
  AND p.name IN ('delete_customer', 'delete_seller', 'delete_staff') AND p.guard_name = 'web';

-- 4. Arabic UI strings ----------------------------------------------------

INSERT INTO `translations` (`lang`, `lang_key`, `lang_value`, `created_at`, `updated_at`)
SELECT s.lang, s.lang_key, s.lang_value, NOW(), NOW()
FROM (
        SELECT 'en' AS lang, 'deactivate_customer' AS lang_key, 'Deactivate Customer' AS lang_value
        UNION ALL SELECT 'ar', 'deactivate_customer', 'إلغاء تفعيل حساب العميل وإعادة تفعيله'
        UNION ALL SELECT 'sa', 'deactivate_customer', 'إلغاء تفعيل حساب العميل وإعادة تفعيله'
        UNION ALL SELECT 'en', 'deactivate_this_customer', 'Deactivate this Customer'
        UNION ALL SELECT 'ar', 'deactivate_this_customer', 'إلغاء تفعيل حساب هذا العميل'
        UNION ALL SELECT 'sa', 'deactivate_this_customer', 'إلغاء تفعيل حساب هذا العميل'
        UNION ALL SELECT 'en', 'reactivate_this_customer', 'Reactivate this Customer'
        UNION ALL SELECT 'ar', 'reactivate_this_customer', 'إعادة تفعيل حساب هذا العميل'
        UNION ALL SELECT 'sa', 'reactivate_this_customer', 'إعادة تفعيل حساب هذا العميل'
        UNION ALL SELECT 'en', 'do_you_really_want_to_deactivate_this_customer_they_will_not_be_able_to_log_in_until_they_register_again_or_you_reactivate_the_account', 'Do you really want to deactivate this customer? They will not be able to log in until they register again or you reactivate the account.'
        UNION ALL SELECT 'ar', 'do_you_really_want_to_deactivate_this_customer_they_will_not_be_able_to_log_in_until_they_register_again_or_you_reactivate_the_account', 'هل تريد فعلاً إلغاء تفعيل حساب هذا العميل؟ لن يتمكن من تسجيل الدخول حتى يسجّل من جديد أو تعيد تفعيل حسابه.'
        UNION ALL SELECT 'sa', 'do_you_really_want_to_deactivate_this_customer_they_will_not_be_able_to_log_in_until_they_register_again_or_you_reactivate_the_account', 'هل تريد فعلاً إلغاء تفعيل حساب هذا العميل؟ لن يتمكن من تسجيل الدخول حتى يسجّل من جديد أو تعيد تفعيل حسابه.'
        UNION ALL SELECT 'en', 'do_you_really_want_to_reactivate_this_customer', 'Do you really want to reactivate this customer?'
        UNION ALL SELECT 'ar', 'do_you_really_want_to_reactivate_this_customer', 'هل تريد فعلاً إعادة تفعيل حساب هذا العميل؟'
        UNION ALL SELECT 'sa', 'do_you_really_want_to_reactivate_this_customer', 'هل تريد فعلاً إعادة تفعيل حساب هذا العميل؟'
        UNION ALL SELECT 'en', 'customer_account_deactivated_successfully', 'Customer account deactivated successfully'
        UNION ALL SELECT 'ar', 'customer_account_deactivated_successfully', 'تم إلغاء تفعيل حساب العميل بنجاح'
        UNION ALL SELECT 'sa', 'customer_account_deactivated_successfully', 'تم إلغاء تفعيل حساب العميل بنجاح'
        UNION ALL SELECT 'en', 'customer_account_reactivated_successfully', 'Customer account reactivated successfully'
        UNION ALL SELECT 'ar', 'customer_account_reactivated_successfully', 'تمت إعادة تفعيل حساب العميل بنجاح'
        UNION ALL SELECT 'sa', 'customer_account_reactivated_successfully', 'تمت إعادة تفعيل حساب العميل بنجاح'
        UNION ALL SELECT 'en', 'account_deactivated', 'Account deactivated'
        UNION ALL SELECT 'ar', 'account_deactivated', 'الحساب ملغى التفعيل'
        UNION ALL SELECT 'sa', 'account_deactivated', 'الحساب ملغى التفعيل'
        UNION ALL SELECT 'en', 'deleted_by_the_customer', 'Deleted by the customer'
        UNION ALL SELECT 'ar', 'deleted_by_the_customer', 'حذفه العميل بنفسه'
        UNION ALL SELECT 'sa', 'deleted_by_the_customer', 'حذفه العميل بنفسه'
        UNION ALL SELECT 'en', 'deactivated_by', 'Deactivated by'
        UNION ALL SELECT 'ar', 'deactivated_by', 'ألغى تفعيله'
        UNION ALL SELECT 'sa', 'deactivated_by', 'ألغى تفعيله'
        UNION ALL SELECT 'en', 'account_status', 'Account Status'
        UNION ALL SELECT 'ar', 'account_status', 'حالة الحساب'
        UNION ALL SELECT 'sa', 'account_status', 'حالة الحساب'
        UNION ALL SELECT 'en', 'all_accounts', 'All accounts'
        UNION ALL SELECT 'ar', 'all_accounts', 'كل الحسابات'
        UNION ALL SELECT 'sa', 'all_accounts', 'كل الحسابات'
        UNION ALL SELECT 'en', 'active_accounts', 'Active accounts'
        UNION ALL SELECT 'ar', 'active_accounts', 'الحسابات الفعّالة'
        UNION ALL SELECT 'sa', 'active_accounts', 'الحسابات الفعّالة'
        UNION ALL SELECT 'en', 'deactivated_accounts', 'Deactivated accounts'
        UNION ALL SELECT 'ar', 'deactivated_accounts', 'الحسابات الملغى تفعيلها'
        UNION ALL SELECT 'sa', 'deactivated_accounts', 'الحسابات الملغى تفعيلها'
        UNION ALL SELECT 'en', 'this_color_indicates_that_the_customer_account_is_deactivated_deleted_by_the_customer_or_by_the_administration', 'This color indicates that the customer account is deactivated (deleted by the customer or by the administration).'
        UNION ALL SELECT 'ar', 'this_color_indicates_that_the_customer_account_is_deactivated_deleted_by_the_customer_or_by_the_administration', 'هذا اللون يدل على أن حساب العميل ملغى التفعيل (حذفه العميل أو ألغته الإدارة).'
        UNION ALL SELECT 'sa', 'this_color_indicates_that_the_customer_account_is_deactivated_deleted_by_the_customer_or_by_the_administration', 'هذا اللون يدل على أن حساب العميل ملغى التفعيل (حذفه العميل أو ألغته الإدارة).'
        UNION ALL SELECT 'en', 'unable_to_deactivate_the_account_please_try_again_later', 'Unable to deactivate the account. Please try again later.'
        UNION ALL SELECT 'ar', 'unable_to_deactivate_the_account_please_try_again_later', 'تعذّر إلغاء تفعيل الحساب. يرجى المحاولة لاحقاً.'
        UNION ALL SELECT 'sa', 'unable_to_deactivate_the_account_please_try_again_later', 'تعذّر إلغاء تفعيل الحساب. يرجى المحاولة لاحقاً.'
        UNION ALL SELECT 'en', 'this_account_is_deactivated_reactivate_it_first', 'This account is deactivated. Reactivate it first.'
        UNION ALL SELECT 'ar', 'this_account_is_deactivated_reactivate_it_first', 'هذا الحساب ملغى التفعيل. أعد تفعيله أولاً.'
        UNION ALL SELECT 'sa', 'this_account_is_deactivated_reactivate_it_first', 'هذا الحساب ملغى التفعيل. أعد تفعيله أولاً.'
        UNION ALL SELECT 'en', 'this_account_type_cannot_be_deleted_from_here_please_contact_the_administration', 'This account type cannot be deleted from here. Please contact the administration.'
        UNION ALL SELECT 'ar', 'this_account_type_cannot_be_deleted_from_here_please_contact_the_administration', 'لا يمكن حذف هذا النوع من الحسابات من هنا. يرجى التواصل مع الإدارة.'
        UNION ALL SELECT 'sa', 'this_account_type_cannot_be_deleted_from_here_please_contact_the_administration', 'لا يمكن حذف هذا النوع من الحسابات من هنا. يرجى التواصل مع الإدارة.'
        UNION ALL SELECT 'en', 'unable_to_delete_your_account_right_now_please_try_again_later', 'Unable to delete your account right now. Please try again later.'
        UNION ALL SELECT 'ar', 'unable_to_delete_your_account_right_now_please_try_again_later', 'تعذّر حذف حسابك الآن. يرجى المحاولة لاحقاً.'
        UNION ALL SELECT 'sa', 'unable_to_delete_your_account_right_now_please_try_again_later', 'تعذّر حذف حسابك الآن. يرجى المحاولة لاحقاً.'
        UNION ALL SELECT 'en', 'this_phone_number_belongs_to_an_existing_account_please_verify_your_phone_number_to_continue', 'This phone number belongs to an existing account. Please verify your phone number to continue.'
        UNION ALL SELECT 'ar', 'this_phone_number_belongs_to_an_existing_account_please_verify_your_phone_number_to_continue', 'رقم الهاتف هذا مرتبط بحساب موجود. يرجى تأكيد رقم هاتفك للمتابعة.'
        UNION ALL SELECT 'sa', 'this_phone_number_belongs_to_an_existing_account_please_verify_your_phone_number_to_continue', 'رقم الهاتف هذا مرتبط بحساب موجود. يرجى تأكيد رقم هاتفك للمتابعة.'
) AS s
WHERE NOT EXISTS (
    SELECT 1 FROM `translations` t
    WHERE t.lang = s.lang AND t.lang_key = s.lang_key
);