<?php
/**
 * One-time migration: create st_activity and copy data from st_user, st_plugin, st_themes, st_core.
 * Run once from run_migrate_activity.php or CLI. Idempotent (INSERT IGNORE on website_id + histid).
 */

if (!function_exists('getDb')) {
    require_once dirname(__DIR__, 2) . '/config.php';
    require_once dirname(__DIR__) . '/database.php';
}

$db = getDb();
$conn = $db->getConnection();

// Create unified activity table
$conn->exec("
    CREATE TABLE IF NOT EXISTS st_activity (
        activity_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        website_id INT UNSIGNED NOT NULL,
        histid INT UNSIGNED NOT NULL,
        object_type VARCHAR(64) NOT NULL,
        action VARCHAR(255) NOT NULL DEFAULT '',
        object_id INT UNSIGNED NOT NULL DEFAULT 0,
        object_name VARCHAR(255) NOT NULL DEFAULT '',
        object_subtype VARCHAR(255) NOT NULL DEFAULT '',
        user_id INT UNSIGNED NOT NULL DEFAULT 0,
        username VARCHAR(255) NULL DEFAULT NULL,
        user_caps VARCHAR(70) NOT NULL DEFAULT 'guest',
        hist_ip VARCHAR(55) NOT NULL DEFAULT '127.0.0.1',
        hist_time INT UNSIGNED NOT NULL DEFAULT 0,
        action_location VARCHAR(255) NULL DEFAULT NULL,
        UNIQUE KEY uq_website_histid (website_id, histid),
        KEY idx_object_type (object_type),
        KEY idx_hist_time (hist_time),
        KEY idx_website_type (website_id, object_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

// Migrate st_user -> Users
$stmt = $conn->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'st_user'");
if ($stmt && $stmt->fetch()) {
    $conn->exec("
        INSERT IGNORE INTO st_activity (
            website_id, histid, object_type, action, object_id, object_name, object_subtype,
            user_id, username, user_caps, hist_ip, hist_time, action_location
        )
        SELECT
            website_id, histid, 'Users', COALESCE(action,''), COALESCE(target_user_id,0), COALESCE(target_username,''), '',
            COALESCE(performed_by_user_id,0), performed_by_username, COALESCE(user_caps,'guest'), COALESCE(hist_ip,'127.0.0.1'), COALESCE(hist_time,0), action_location
        FROM st_user
    ");
}

// Migrate st_plugin -> Plugins
$stmt = $conn->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'st_plugin'");
if ($stmt && $stmt->fetch()) {
    $conn->exec("
        INSERT IGNORE INTO st_activity (
            website_id, histid, object_type, action, object_id, object_name, object_subtype,
            user_id, username, user_caps, hist_ip, hist_time, action_location
        )
        SELECT
            website_id, histid, 'Plugins', COALESCE(action,''), 0, COALESCE(plugin_name,''), COALESCE(plugin_version,''),
            COALESCE(user_id,0), username, COALESCE(user_caps,'guest'), COALESCE(hist_ip,'127.0.0.1'), COALESCE(hist_time,0), NULL
        FROM st_plugin
    ");
}

// Migrate st_themes -> Themes
$stmt = $conn->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'st_themes'");
if ($stmt && $stmt->fetch()) {
    $conn->exec("
        INSERT IGNORE INTO st_activity (
            website_id, histid, object_type, action, object_id, object_name, object_subtype,
            user_id, username, user_caps, hist_ip, hist_time, action_location
        )
        SELECT
            website_id, histid, 'Themes', COALESCE(action,''), 0, COALESCE(theme_name,''), COALESCE(theme_version,''),
            COALESCE(user_id,0), username, COALESCE(user_caps,'guest'), COALESCE(hist_ip,'127.0.0.1'), COALESCE(hist_time,0), NULL
        FROM st_themes
    ");
}

// Migrate st_core -> Core
$stmt = $conn->query("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'st_core'");
if ($stmt && $stmt->fetch()) {
    $conn->exec("
        INSERT IGNORE INTO st_activity (
            website_id, histid, object_type, action, object_id, object_name, object_subtype,
            user_id, username, user_caps, hist_ip, hist_time, action_location
        )
        SELECT
            website_id, histid, 'Core', COALESCE(action,''), 0, 'WordPress', COALESCE(version,''),
            COALESCE(user_id,0), username, COALESCE(user_caps,'guest'), COALESCE(hist_ip,'127.0.0.1'), COALESCE(hist_time,0), NULL
        FROM st_core
    ");
}

return true;