File manager - Edit - /home/webapp69.cm.in.th/u69319090028/Shop/database/migrations/admin_backend.sql
Back
-- ============================================================ -- CARGOO Admin Backend Migration -- Run once against: cargoo_shop -- ============================================================ -- 1. Add Super Admin flag to users ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `is_super_admin` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Only one user may have this flag'; -- Designate the first admin user as Super Admin (user with admin role, earliest) UPDATE `users` u JOIN `user_roles` ur ON ur.user_id = u.id JOIN `roles` r ON r.id = ur.role_id AND r.name = 'admin' SET u.is_super_admin = 1 WHERE u.is_super_admin = 0 ORDER BY ur.assigned_at ASC LIMIT 1; -- ============================================================ -- 2. Admin-specific permission overrides -- ============================================================ CREATE TABLE IF NOT EXISTS `admin_permissions` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `admin_user_id` INT UNSIGNED NOT NULL, `permission_key` VARCHAR(80) NOT NULL COMMENT 'e.g. product.approve, finance.view', `granted` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=grant 0=deny override', `granted_by` INT UNSIGNED DEFAULT NULL COMMENT 'admin user id who set this', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uniq_admin_perm` (`admin_user_id`, `permission_key`), KEY `idx_admin_perm_user` (`admin_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- 3. Admin creation request + approval workflow -- ============================================================ CREATE TABLE IF NOT EXISTS `admin_creation_requests` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `requester_user_id` INT UNSIGNED NOT NULL COMMENT 'existing admin requesting a new admin', `target_email` VARCHAR(191) NOT NULL COMMENT 'email of user to promote', `requested_role` ENUM('operation_admin','finance_admin','support_admin') NOT NULL, `reason` TEXT DEFAULT NULL, `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', `reviewed_by` INT UNSIGNED DEFAULT NULL COMMENT 'super admin id', `reviewed_at` DATETIME DEFAULT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_acr_status` (`status`), KEY `idx_acr_requester` (`requester_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- 4. Admin audit log (dedicated, separate from activity_logs) -- ============================================================ CREATE TABLE IF NOT EXISTS `admin_audit_logs` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `admin_id` INT UNSIGNED NOT NULL, `action` VARCHAR(80) NOT NULL COMMENT 'e.g. user.suspend, product.approve', `module` VARCHAR(40) NOT NULL COMMENT 'e.g. users, products, settings', `target_type` VARCHAR(40) DEFAULT NULL COMMENT 'e.g. user, store, product', `target_id` INT UNSIGNED DEFAULT NULL, `detail` TEXT DEFAULT NULL COMMENT 'JSON or plain text context', `ip_address` VARCHAR(45) DEFAULT NULL, `result` ENUM('success','failed','denied') NOT NULL DEFAULT 'success', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_aal_admin` (`admin_id`), KEY `idx_aal_created` (`created_at`), KEY `idx_aal_module` (`module`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- 5. User-submitted reports (product / seller / order) -- ============================================================ CREATE TABLE IF NOT EXISTS `reports` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `reporter_user_id` INT UNSIGNED NOT NULL, `report_type` ENUM('product','seller','order') NOT NULL, `target_id` INT UNSIGNED NOT NULL COMMENT 'product_id / store_id / order_id', `reason` VARCHAR(120) NOT NULL, `description` TEXT DEFAULT NULL, `evidence_urls` TEXT DEFAULT NULL COMMENT 'JSON array of URLs', `status` ENUM('submitted','reviewing','resolved','rejected') NOT NULL DEFAULT 'submitted', `assigned_admin_id` INT UNSIGNED DEFAULT NULL, `resolution_note` TEXT DEFAULT NULL, `resolved_at` DATETIME DEFAULT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_reports_status` (`status`), KEY `idx_reports_type` (`report_type`, `target_id`), KEY `idx_reports_reporter` (`reporter_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- 6. Platform settings (key-value store) -- ============================================================ CREATE TABLE IF NOT EXISTS `platform_settings` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `setting_key` VARCHAR(80) NOT NULL, `setting_value` TEXT DEFAULT NULL, `setting_group` VARCHAR(40) NOT NULL DEFAULT 'general', `description` VARCHAR(255) DEFAULT NULL, `updated_by` INT UNSIGNED DEFAULT NULL, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uniq_setting_key` (`setting_key`), KEY `idx_setting_group` (`setting_group`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Seed default platform settings INSERT IGNORE INTO `platform_settings` (`setting_key`, `setting_value`, `setting_group`, `description`) VALUES ('site_name', 'CARGOO', 'general', 'Platform display name'), ('site_tagline', 'Online Shopping', 'general', 'Platform tagline'), ('maintenance_mode', '0', 'general', '1 = maintenance, 0 = live'), ('default_commission_rate', '5.00', 'marketplace', 'Default seller commission % applied on new stores'), ('max_products_per_store', '500', 'marketplace', 'Max active products allowed per store'), ('allow_seller_registration','1', 'marketplace', '1 = open, 0 = closed'), ('payment_gateway', 'manual', 'payment', 'Active payment gateway identifier'), ('cod_enabled', '1', 'payment', 'Cash on Delivery enabled'), ('bank_transfer_enabled', '1', 'payment', 'Bank Transfer payment enabled'), ('notify_new_order', '1', 'notification', 'Notify seller on new order'), ('notify_order_shipped', '1', 'notification', 'Notify customer when order ships'), ('session_lifetime_minutes', '120', 'security', 'Session timeout in minutes'), ('max_login_attempts', '5', 'security', 'Failed logins before lockout'), ('lockout_duration_minutes', '15', 'security', 'Login lockout duration'); -- ============================================================ -- 7. Add `rejected` to products.status if not already present -- (MariaDB: modify ENUM safely) -- ============================================================ ALTER TABLE `products` MODIFY COLUMN `status` ENUM('draft','pending','active','hidden','rejected','deleted') NOT NULL DEFAULT 'draft'; -- ============================================================ -- 8. Ensure roles exist for all admin tiers -- ============================================================ INSERT IGNORE INTO `roles` (`name`, `display_name`, `description`, `created_at`) VALUES ('operation_admin', 'Operation Admin', 'Manages users, sellers, products, orders, reports', NOW()), ('finance_admin', 'Finance Admin', 'Manages financial data, commissions, settlements', NOW()), ('support_admin', 'Support Admin', 'Handles reports, user support, order investigation', NOW());
| ver. 1.4 |
Github
|
.
| PHP 7.4.33 | Generation time: 0.24 |
proxy
|
phpinfo
|
Settings