<?php
/* ============================================================================
   WBMCZ — ccpsimigration.php
   ----------------------------------------------------------------------------
   Database migration for the new premium admin sidebar release:

     1. activity_logs        (NEW TABLE)  — admin audit trail
     2. orders.category_id   (NEW COLUMN) — category-wise Order Management
     3. orders index         (NEW INDEX)  — keeps the category filter fast
     4. Back-fill            orders.category_id from the existing service data
     5. Sanity check         service_categories rows used by the sidebar

   Every statement is idempotent — running this file twice is harmless.

   HOW TO RUN
     Upload to the site root (next to config.php) and open:
         https://yourdomain.com/ccpsimigration.php
     Delete the file afterwards.
   ========================================================================== */

require_once __DIR__ . '/config.php';
require_once __DIR__ . '/db.php';

@set_time_limit(300);

$conn = $db->getConnection();
$LOG  = [];

function step($ok, $title, $detail = '') {
    global $LOG;
    $LOG[] = ['ok' => $ok, 'title' => $title, 'detail' => $detail];
}

function q($conn, $sql, $title) {
    try {
        if ($conn->query($sql)) {
            step(true, $title, 'OK');
            return true;
        }
        step(false, $title, $conn->error);
        return false;
    } catch (\Throwable $e) {
        // "already exists" / "duplicate" are expected on a re-run
        $m = $e->getMessage();
        $benign = (stripos($m, 'duplicate') !== false)
               || (stripos($m, 'already exists') !== false)
               || (stripos($m, 'exists') !== false);
        step($benign, $title, $benign ? 'Already present — skipped' : $m);
        return $benign;
    }
}

function column_exists($conn, $table, $column) {
    try {
        $r = $conn->query("SHOW COLUMNS FROM `$table` LIKE '$column'");
        return $r && $r->num_rows > 0;
    } catch (\Throwable $e) { return false; }
}

function index_exists($conn, $table, $index) {
    try {
        $r = $conn->query("SHOW INDEX FROM `$table` WHERE Key_name = '$index'");
        return $r && $r->num_rows > 0;
    } catch (\Throwable $e) { return false; }
}

function table_exists($conn, $table) {
    try {
        $r = $conn->query("SHOW TABLES LIKE '$table'");
        return $r && $r->num_rows > 0;
    } catch (\Throwable $e) { return false; }
}

/* ==========================================================================
   1.  activity_logs  — NEW TABLE
   ========================================================================== */
q($conn, "CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    user_name VARCHAR(150) NULL,
    user_role VARCHAR(20) NULL,
    action VARCHAR(60) NOT NULL DEFAULT 'view',
    module VARCHAR(80) NULL,
    description VARCHAR(500) NULL,
    reference_type VARCHAR(40) NULL,
    reference_id INT NULL,
    method VARCHAR(10) NULL,
    url VARCHAR(255) NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    meta TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_al_user (user_id, created_at),
    KEY idx_al_action (action),
    KEY idx_al_module (module),
    KEY idx_al_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
"activity_logs — create table");

/* Older installs may already have the table without the newer columns. */
$activity_cols = [
    'user_name'      => "VARCHAR(150) NULL AFTER user_id",
    'user_role'      => "VARCHAR(20) NULL AFTER user_name",
    'module'         => "VARCHAR(80) NULL AFTER action",
    'description'    => "VARCHAR(500) NULL AFTER module",
    'reference_type' => "VARCHAR(40) NULL AFTER description",
    'reference_id'   => "INT NULL AFTER reference_type",
    'method'         => "VARCHAR(10) NULL AFTER reference_id",
    'url'            => "VARCHAR(255) NULL AFTER method",
    'ip_address'     => "VARCHAR(45) NULL AFTER url",
    'user_agent'     => "VARCHAR(255) NULL AFTER ip_address",
    'meta'           => "TEXT NULL AFTER user_agent",
];
if (table_exists($conn, 'activity_logs')) {
    foreach ($activity_cols as $col => $def) {
        if (!column_exists($conn, 'activity_logs', $col)) {
            q($conn, "ALTER TABLE activity_logs ADD COLUMN `$col` $def", "activity_logs.$col — add column");
        } else {
            step(true, "activity_logs.$col", 'Already present');
        }
    }
}

/* ==========================================================================
   2.  orders.category_id  — NEW COLUMN
   ========================================================================== */
if (!table_exists($conn, 'orders')) {
    step(false, 'orders table', 'Not found — skipping order changes');
} else {
    if (!column_exists($conn, 'orders', 'category_id')) {
        q($conn, "ALTER TABLE orders ADD COLUMN `category_id` INT NULL DEFAULT NULL AFTER `service_name`",
          "orders.category_id — add column");
    } else {
        step(true, 'orders.category_id', 'Already present');
    }

    /* ------------------------------------------------------------------
       3.  Index for the category filter
       ------------------------------------------------------------------ */
    if (!index_exists($conn, 'orders', 'idx_orders_cat')) {
        q($conn, "ALTER TABLE orders ADD INDEX idx_orders_cat (category_id)", "orders — add idx_orders_cat");
    } else {
        step(true, 'orders idx_orders_cat', 'Already present');
    }

    /* ------------------------------------------------------------------
       4.  Back-fill category_id for orders placed before this release.
           Matching is done on the stored service_name, first against the
           seller catalogue, then the agency catalogue.
       ------------------------------------------------------------------ */
    $filled = 0;

    if (table_exists($conn, 'seller_services')) {
        $sql = "UPDATE orders o
                  JOIN seller_services ss ON ss.title = o.service_name
                   SET o.category_id = ss.category_id
                 WHERE o.category_id IS NULL AND ss.category_id > 0";
        if (q($conn, $sql, "orders.category_id — back-fill from seller_services")) {
            $filled += $conn->affected_rows;
        }
    }

    if (table_exists($conn, 'agency_services') && table_exists($conn, 'agency_sub_categories')) {
        $sql = "UPDATE orders o
                  JOIN agency_services a       ON a.title = o.service_name
                  JOIN agency_sub_categories sc ON sc.id = a.sub_category_id
                   SET o.category_id = sc.category_id
                 WHERE o.category_id IS NULL AND sc.category_id > 0";
        if (q($conn, $sql, "orders.category_id — back-fill from agency_services")) {
            $filled += $conn->affected_rows;
        }
    }

    step(true, 'Back-fill summary', $filled . ' historic order(s) matched to a category');

    $unmatched = 0;
    try {
        $r = $conn->query("SELECT COUNT(*) n FROM orders WHERE category_id IS NULL");
        if ($r) { $row = $r->fetch_assoc(); $unmatched = (int)$row['n']; }
    } catch (\Throwable $e) {}
    if ($unmatched > 0) {
        step(true, 'Unmatched orders', $unmatched . ' order(s) have no category — they appear under "All Orders" only. New orders will be tagged automatically.');
    }
}

/* ==========================================================================
   5.  service_categories sanity check (the sidebar reads this table)
   ========================================================================== */
$cats = [];
if (table_exists($conn, 'service_categories')) {
    try {
        $r = $conn->query("SELECT id, title, COALESCE(status,1) st FROM service_categories ORDER BY display_order, id");
        while ($r && ($row = $r->fetch_assoc())) { $cats[] = $row; }
    } catch (\Throwable $e) {}
    step(!empty($cats), 'service_categories', count($cats) . ' categor' . (count($cats) === 1 ? 'y' : 'ies') . ' found for the sidebar');
} else {
    step(false, 'service_categories', 'Table missing — the sidebar will use its built-in fallback list');
}

/* ==========================================================================
   6.  Seed the first activity entry so the new page is not empty
   ========================================================================== */
if (table_exists($conn, 'activity_logs')) {
    try {
        $r = $conn->query("SELECT COUNT(*) n FROM activity_logs");
        $row = $r ? $r->fetch_assoc() : ['n' => 1];
        if ((int)$row['n'] === 0) {
            $ip = $conn->real_escape_string($_SERVER['REMOTE_ADDR'] ?? '0.0.0.0');
            $conn->query("INSERT INTO activity_logs (action, module, description, method, url, ip_address)
                          VALUES ('update', 'Activity Log', 'Activity logging enabled by ccpsimigration.php', 'GET', '/ccpsimigration.php', '$ip')");
            step(true, 'activity_logs', 'Seeded the first entry');
        }
    } catch (\Throwable $e) {}
}

/* ==========================================================================
   REPORT
   ========================================================================== */
$fails = 0;
foreach ($LOG as $l) { if (!$l['ok']) $fails++; }
?><!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>WBMCZ — ccpsimigration</title>
<script src="https://cdn.tailwindcss.com"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;600;700;800&display=swap" rel="stylesheet">
<style>body{font-family:'Inter',sans-serif}</style>
</head>
<body class="bg-slate-100 py-10 px-4">
<div class="max-w-3xl mx-auto">

    <div class="bg-white rounded-2xl border border-slate-200 shadow-sm overflow-hidden">
        <div class="p-6 <?php echo $fails ? 'bg-gradient-to-r from-amber-500 to-orange-600' : 'bg-gradient-to-r from-emerald-500 to-teal-600'; ?> text-white">
            <p class="text-[11px] font-bold uppercase tracking-widest opacity-80">WBMCZ Database Migration</p>
            <h1 class="text-2xl font-extrabold mt-1">
                <?php echo $fails ? 'Finished with ' . $fails . ' warning' . ($fails === 1 ? '' : 's') : 'Migration completed successfully'; ?>
            </h1>
            <p class="text-sm opacity-90 mt-1">Premium admin sidebar release &middot; <?php echo date('d M Y, h:i A'); ?></p>
        </div>

        <div class="divide-y divide-slate-100">
            <?php foreach ($LOG as $l): ?>
            <div class="flex items-start gap-3 px-6 py-3">
                <span class="w-6 h-6 rounded-lg grid place-items-center text-[11px] flex-shrink-0 mt-0.5 <?php echo $l['ok'] ? 'bg-emerald-100 text-emerald-700' : 'bg-rose-100 text-rose-700'; ?>">
                    <i class="fas <?php echo $l['ok'] ? 'fa-check' : 'fa-exclamation'; ?>"></i>
                </span>
                <div class="min-w-0">
                    <p class="text-sm font-bold text-slate-800"><?php echo htmlspecialchars($l['title']); ?></p>
                    <?php if ($l['detail'] !== ''): ?>
                        <p class="text-xs text-slate-500 break-words"><?php echo htmlspecialchars($l['detail']); ?></p>
                    <?php endif; ?>
                </div>
            </div>
            <?php endforeach; ?>
        </div>

        <div class="p-6 bg-slate-50 border-t border-slate-100">
            <p class="text-sm font-bold text-slate-700 mb-2">What changed</p>
            <ul class="text-sm text-slate-600 space-y-1.5 list-disc list-inside">
                <li><b>New table</b> <code class="text-xs bg-white px-1.5 py-0.5 rounded border">activity_logs</code> — powers <code class="text-xs">admin/activity_log.php</code></li>
                <li><b>New column</b> <code class="text-xs bg-white px-1.5 py-0.5 rounded border">orders.category_id</code> — powers category-wise Order Management</li>
                <li><b>New index</b> <code class="text-xs bg-white px-1.5 py-0.5 rounded border">orders.idx_orders_cat</code></li>
                <li>Historic orders were matched to a category where the service name still exists in the catalogue</li>
            </ul>
            <p class="text-xs text-rose-600 font-bold mt-4"><i class="fas fa-triangle-exclamation mr-1"></i>Delete this file from the server once you have seen this page.</p>
        </div>
    </div>

    <p class="text-center text-xs text-slate-400 mt-6">
        <a href="admin/index.php" class="font-bold text-emerald-600 hover:text-emerald-700">Go to the admin panel <i class="fas fa-arrow-right ml-1"></i></a>
    </p>
</div>
</body>
</html>
