-- SimuTradeX Admin Panel - READ-ONLY database preflight
-- Safe to run in phpMyAdmin: this file does not CREATE, ALTER, INSERT, UPDATE, DELETE or DROP anything.

SELECT
  DATABASE() AS selected_database,
  VERSION() AS database_version,
  @@version_comment AS database_product;

-- Summary checks used by the admin panel.
SELECT 'users table exists' AS check_name,
       IF(EXISTS(
         SELECT 1 FROM information_schema.tables
         WHERE table_schema = DATABASE() AND table_name = 'users'
       ), 'OK', 'ERROR: users table is missing') AS result
UNION ALL
SELECT 'users ID column',
       IF(EXISTS(
         SELECT 1 FROM information_schema.columns
         WHERE table_schema = DATABASE() AND table_name = 'users'
           AND column_name IN ('id', 'user_id')
       ), 'OK', 'ERROR: users.id or users.user_id is missing')
UNION ALL
SELECT 'users.is_admin',
       IF(EXISTS(
         SELECT 1 FROM information_schema.columns
         WHERE table_schema = DATABASE() AND table_name = 'users'
           AND column_name = 'is_admin'
       ), 'OK: already exists', 'INFO: migration will add it')
UNION ALL
SELECT 'admin_activity_logs compatibility',
       CASE
         WHEN NOT EXISTS (
           SELECT 1 FROM information_schema.tables
           WHERE table_schema = DATABASE() AND table_name = 'admin_activity_logs'
         ) THEN 'OK: migration will create it'
         WHEN EXISTS (
           SELECT 1 FROM information_schema.columns
           WHERE table_schema = DATABASE() AND table_name = 'admin_activity_logs'
             AND column_name = 'admin_user_id'
         ) AND EXISTS (
           SELECT 1 FROM information_schema.columns
           WHERE table_schema = DATABASE() AND table_name = 'admin_activity_logs'
             AND column_name = 'action'
         ) THEN 'OK: compatible table exists'
         ELSE 'WARNING: table exists but required columns differ'
       END
UNION ALL
SELECT 'admin_settings compatibility',
       CASE
         WHEN NOT EXISTS (
           SELECT 1 FROM information_schema.tables
           WHERE table_schema = DATABASE() AND table_name = 'admin_settings'
         ) THEN 'OK: migration will create it'
         WHEN EXISTS (
           SELECT 1 FROM information_schema.columns
           WHERE table_schema = DATABASE() AND table_name = 'admin_settings'
             AND column_name IN ('setting_key', 'key_name', 'name')
         ) AND EXISTS (
           SELECT 1 FROM information_schema.columns
           WHERE table_schema = DATABASE() AND table_name = 'admin_settings'
             AND column_name IN ('setting_value', 'value')
         ) THEN 'OK: compatible table exists'
         ELSE 'WARNING: table exists but required columns differ'
       END
UNION ALL
SELECT 'user_notifications compatibility',
       CASE
         WHEN NOT EXISTS (
           SELECT 1 FROM information_schema.tables
           WHERE table_schema = DATABASE() AND table_name = 'user_notifications'
         ) THEN 'OK: migration will create it'
         WHEN EXISTS (
           SELECT 1 FROM information_schema.columns
           WHERE table_schema = DATABASE() AND table_name = 'user_notifications'
             AND column_name = 'user_id'
         ) AND EXISTS (
           SELECT 1 FROM information_schema.columns
           WHERE table_schema = DATABASE() AND table_name = 'user_notifications'
             AND column_name = 'title'
         ) AND EXISTS (
           SELECT 1 FROM information_schema.columns
           WHERE table_schema = DATABASE() AND table_name = 'user_notifications'
             AND column_name IN ('message', 'body')
         ) THEN 'OK: compatible table exists'
         ELSE 'WARNING: table exists but required columns differ'
       END;

-- Existing relevant tables.
SELECT table_name, engine, table_collation, table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name IN (
    'users',
    'subscription_plans',
    'crypto_payment_orders',
    'user_subscriptions',
    'user_notifications',
    'admin_activity_logs',
    'admin_settings'
  )
ORDER BY table_name;

-- Existing columns. Send this result back for a precise compatibility check.
SELECT
  table_name,
  ordinal_position,
  column_name,
  column_type,
  is_nullable,
  column_default,
  extra
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name IN (
    'users',
    'subscription_plans',
    'crypto_payment_orders',
    'user_subscriptions',
    'user_notifications',
    'admin_activity_logs',
    'admin_settings'
  )
ORDER BY table_name, ordinal_position;