-- SimuTradeX admin panel migration
-- Date: 2026-06-30
-- IMPORTANT: Back up the trade_panel database before running this file.

START TRANSACTION;

-- Add users.is_admin only when it is missing. Compatible with MySQL/MariaDB versions
-- that do not support ADD COLUMN IF NOT EXISTS.
SET @has_is_admin := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'users'
    AND column_name = 'is_admin'
);
SET @sql := IF(
  @has_is_admin = 0,
  'ALTER TABLE `users` ADD COLUMN `is_admin` TINYINT(1) NOT NULL DEFAULT 0 AFTER `email`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `admin_activity_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_user_id` BIGINT UNSIGNED NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `entity_type` VARCHAR(100) NULL,
  `entity_id` BIGINT UNSIGNED NULL,
  `description` TEXT NULL,
  `metadata` JSON NULL,
  `ip_address` VARCHAR(64) NULL,
  `user_agent` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_admin_logs_admin_user_id` (`admin_user_id`),
  INDEX `idx_admin_logs_action` (`action`),
  INDEX `idx_admin_logs_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_settings` (
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NULL,
  `value_type` VARCHAR(30) NOT NULL DEFAULT 'string',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Created only when the project does not already have this table.
CREATE TABLE IF NOT EXISTS `user_notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(180) NOT NULL,
  `message` TEXT NOT NULL,
  `type` VARCHAR(30) NOT NULL DEFAULT 'info',
  `audience` VARCHAR(30) NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user_notifications_user_id` (`user_id`),
  INDEX `idx_user_notifications_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `admin_settings` (`setting_key`, `setting_value`, `value_type`) VALUES
  ('oxapay_enabled', '1', 'boolean'),
  ('oxapay_test_mode', '0', 'boolean'),
  ('payment_help_text', '', 'string'),
  ('minimum_test_payment', '0.50', 'decimal'),
  ('price_retention_days', '90', 'integer')
ON DUPLICATE KEY UPDATE `setting_key` = VALUES(`setting_key`);

COMMIT;

-- Run this separately after replacing the email address:
-- UPDATE `users` SET `is_admin` = 1 WHERE `email` = 'your-admin@example.com' LIMIT 1;