-- Run this file in phpMyAdmin immediately after 01-raseednow-mysql-base-with-test-data.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `users`
  ADD COLUMN `phone_verified_at` DATETIME NULL AFTER `phone`,
  ADD COLUMN `customer_tier` VARCHAR(255) NOT NULL DEFAULT 'general' AFTER `role`,
  ADD COLUMN `admin_sections` JSON NULL AFTER `customer_tier`,
  ADD COLUMN `admin_permissions` JSON NULL AFTER `admin_sections`,
  ADD COLUMN `discount_basis_points` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `risk_score`,
  ADD COLUMN `google_id` VARCHAR(255) NULL AFTER `email`,
  ADD COLUMN `avatar_url` VARCHAR(500) NULL AFTER `google_id`,
  ADD COLUMN `auth_provider` VARCHAR(24) NOT NULL DEFAULT 'password' AFTER `avatar_url`,
  ADD UNIQUE KEY `users_google_id_unique` (`google_id`),
  ADD KEY `users_customer_tier_index` (`customer_tier`);

ALTER TABLE `product_variants`
  ADD COLUMN `sort_order` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `is_best_seller`,
  ADD COLUMN `source_external_id` VARCHAR(120) NULL AFTER `sort_order`,
  ADD COLUMN `source_url` TEXT NULL AFTER `source_external_id`,
  ADD KEY `product_variants_sort_order_index` (`sort_order`),
  ADD KEY `product_variants_source_external_id_index` (`source_external_id`);

ALTER TABLE `games`
  ADD COLUMN `source_provider` VARCHAR(40) NULL AFTER `image_path`,
  ADD COLUMN `source_url` TEXT NULL AFTER `source_provider`,
  ADD COLUMN `source_imported_at` DATETIME NULL AFTER `source_url`,
  ADD KEY `games_source_provider_index` (`source_provider`);

ALTER TABLE `site_settings`
  ADD COLUMN `maintenance_mode` TINYINT(1) NOT NULL DEFAULT 0 AFTER `logo_path`,
  ADD COLUMN `maintenance_message_ar` VARCHAR(255) NULL AFTER `maintenance_mode`,
  ADD COLUMN `maintenance_message_en` VARCHAR(255) NULL AFTER `maintenance_message_ar`,
  ADD COLUMN `wallet_transfers_enabled` TINYINT(1) NOT NULL DEFAULT 1 AFTER `maintenance_message_en`,
  ADD COLUMN `wallet_transfers_require_approval` TINYINT(1) NOT NULL DEFAULT 0 AFTER `wallet_transfers_enabled`,
  ADD COLUMN `wallet_transfer_min_minor` BIGINT NOT NULL DEFAULT 100 AFTER `wallet_transfers_require_approval`,
  ADD COLUMN `wallet_transfer_max_minor` BIGINT NOT NULL DEFAULT 5000000 AFTER `wallet_transfer_min_minor`,
  ADD KEY `site_settings_maintenance_mode_index` (`maintenance_mode`);

ALTER TABLE `wallet_transfers`
  ADD COLUMN `reviewed_by_user_id` BIGINT UNSIGNED NULL AFTER `receiver_user_id`,
  ADD COLUMN `reviewed_at` DATETIME NULL AFTER `status`,
  ADD COLUMN `admin_note` TEXT NULL AFTER `note`,
  ADD COLUMN `metadata` JSON NULL AFTER `admin_note`,
  ADD KEY `wallet_transfers_reviewed_by_user_id_index` (`reviewed_by_user_id`);

INSERT IGNORE INTO `categories` (`id`,`parent_id`,`name_ar`,`name_en`,`slug`,`icon`,`color`,`description_ar`,`description_en`,`sort_order`,`is_featured`,`is_active`,`created_at`,`updated_at`) VALUES
(9,NULL,'الحسابات الرقمية','Digital Accounts','accounts','users','#6d4aff','حسابات ألعاب واشتراكات رقمية جاهزة من بائعين موثوقين.','Ready-to-use gaming and subscription accounts from trusted sellers.',3,1,1,NOW(),NOW()),
(10,1,'ألعاب الموبايل','Mobile Games','mobile-games','grid','#1268ff',NULL,NULL,1,1,1,NOW(),NOW()),
(11,1,'ألعاب الكمبيوتر والكونسول','PC & Console Games','pc-console-games','grid','#00a98f',NULL,NULL,2,1,1,NOW(),NOW()),
(12,2,'بطاقات منصات الألعاب','Gaming Gift Cards','gaming-gift-cards','grid','#ff8a1e',NULL,NULL,3,1,1,NOW(),NOW()),
(13,2,'بطاقات المتاجر','Store Gift Cards','store-gift-cards','grid','#ec4899',NULL,NULL,4,1,1,NOW(),NOW()),
(14,9,'حسابات الألعاب','Gaming Accounts','gaming-accounts','grid','#6d4aff',NULL,NULL,5,1,1,NOW(),NOW()),
(15,9,'حسابات الاشتراكات','Subscription Accounts','subscription-accounts','grid','#8b5cf6',NULL,NULL,6,1,1,NOW(),NOW());

INSERT IGNORE INTO `migrations` (`id`,`migration`,`batch`) VALUES
(45,'2026_07_23_000001_admin_beast_refinements',5),
(46,'2026_07_23_000002_add_wallet_transfer_controls',5),
(47,'2026_07_23_000003_add_granular_admin_permissions',5),
(48,'2026_08_01_000001_add_google_oauth_to_users_table',6),
(49,'2026_08_01_000002_add_import_source_fields',6);

SET FOREIGN_KEY_CHECKS=1;