-- SimuTradeX Admin Panel - conservative/idempotent migration
-- Date: 2026-07-01
-- IMPORTANT:
-- 1) Select the correct database in phpMyAdmin.
-- 2) Run 00_admin_panel_preflight_readonly.sql first.
-- 3) Take a full database backup before this migration.
--
-- This migration does NOT DROP tables or columns and does NOT delete/update existing rows.
-- DDL statements such as ALTER TABLE / CREATE TABLE may auto-commit in MySQL/MariaDB,
-- therefore START TRANSACTION is intentionally not used here.

-- Add users.is_admin only when users exists and the column is missing.
-- No AFTER clause is used, so the migration does not depend on an `email` column.
SET @users_exists := (
  SELECT COUNT(*)
  FROM information_schema.tables
  WHERE table_schema = DATABASE()
    AND table_name = 'users'
);

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_add_is_admin := IF(
  @users_exists = 1 AND @has_is_admin = 0,
  'ALTER TABLE `users` ADD COLUMN `is_admin` TINYINT(1) NOT NULL DEFAULT 0',
  IF(
    @users_exists = 0,
    'SELECT ''SKIPPED: users table does not exist in the selected database'' AS migration_message',
    'SELECT ''OK: users.is_admin already exists'' AS migration_message'
  )
);
PREPARE stmt_add_is_admin FROM @sql_add_is_admin;
EXECUTE stmt_add_is_admin;
DEALLOCATE PREPARE stmt_add_is_admin;

-- Dedicated audit table. No foreign key is added, avoiding type/cascade conflicts
-- with an existing users table. `metadata` is LONGTEXT for broad MySQL/MariaDB compatibility;
-- the application stores a JSON string in this column.
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` LONGTEXT 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;

-- Create only if the project does not already have a table with this name.
-- CREATE TABLE IF NOT EXISTS never replaces an existing 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;

-- Seed defaults only when the canonical admin_settings columns exist.
-- INSERT IGNORE preserves any existing values with the same primary key.
SET @settings_has_key := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'admin_settings'
    AND column_name = 'setting_key'
);
SET @settings_has_value := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'admin_settings'
    AND column_name = 'setting_value'
);
SET @sql_seed_settings := IF(
  @settings_has_key = 1 AND @settings_has_value = 1,
  'INSERT IGNORE INTO `admin_settings` (`setting_key`, `setting_value`) VALUES
     (''oxapay_enabled'', ''1''),
     (''oxapay_test_mode'', ''0''),
     (''payment_help_text'', ''''),
     (''minimum_test_payment'', ''0.50''),
     (''price_retention_days'', ''90'')',
  'SELECT ''SKIPPED: existing admin_settings table has a different schema'' AS migration_message'
);
PREPARE stmt_seed_settings FROM @sql_seed_settings;
EXECUTE stmt_seed_settings;
DEALLOCATE PREPARE stmt_seed_settings;

-- Final non-destructive status report.
SELECT 'users.is_admin' AS item,
       IF(EXISTS(
         SELECT 1 FROM information_schema.columns
         WHERE table_schema = DATABASE()
           AND table_name = 'users'
           AND column_name = 'is_admin'
       ), 'OK', 'ERROR / NOT CREATED') AS status
UNION ALL
SELECT 'admin_activity_logs',
       IF(EXISTS(
         SELECT 1 FROM information_schema.tables
         WHERE table_schema = DATABASE()
           AND table_name = 'admin_activity_logs'
       ), 'OK', 'ERROR / NOT CREATED')
UNION ALL
SELECT 'admin_settings',
       IF(EXISTS(
         SELECT 1 FROM information_schema.tables
         WHERE table_schema = DATABASE()
           AND table_name = 'admin_settings'
       ), 'OK', 'ERROR / NOT CREATED')
UNION ALL
SELECT 'user_notifications',
       IF(EXISTS(
         SELECT 1 FROM information_schema.tables
         WHERE table_schema = DATABASE()
           AND table_name = 'user_notifications'
       ), 'OK', 'ERROR / NOT CREATED');

-- Enable one administrator only after replacing the email and checking the WHERE clause:
-- UPDATE `users`
-- SET `is_admin` = 1
-- WHERE `email` = 'your-admin@example.com'
-- LIMIT 1;