<?php
/**
 * Run once after upload to add safe performance indexes for active production DB.
 * Usage (cPanel terminal): php scripts/optimize_runtime_hotfix_20260731.php
 */
require_once __DIR__ . '/../src/config.php';

$pdo = db();

function idx_exists(PDO $pdo, string $table, string $index): bool {
    $st = $pdo->prepare("SHOW INDEX FROM `$table` WHERE Key_name = ?");
    $st->execute([$index]);
    return (bool)$st->fetch();
}
function col_exists(PDO $pdo, string $table, string $column): bool {
    $st = $pdo->prepare("SHOW COLUMNS FROM `$table` LIKE ?");
    $st->execute([$column]);
    return (bool)$st->fetch();
}
function add_idx(PDO $pdo, string $table, string $index, string $sql): void {
    if (idx_exists($pdo, $table, $index)) {
        echo "[skip] {$table}.{$index} exists\n";
        return;
    }
    try {
        $pdo->exec($sql);
        echo "[ok] {$table}.{$index}\n";
    } catch (Throwable $e) {
        echo "[err] {$table}.{$index}: " . $e->getMessage() . "\n";
    }
}

add_idx($pdo, 'orders', 'idx_orders_user_status_created', "ALTER TABLE orders ADD INDEX idx_orders_user_status_created (user_id, status, created_at)");
add_idx($pdo, 'orders', 'idx_orders_status_user_delivered', "ALTER TABLE orders ADD INDEX idx_orders_status_user_delivered (status, user_id, delivered_at)");
add_idx($pdo, 'deliveries', 'idx_deliveries_user_status_delivered', "ALTER TABLE deliveries ADD INDEX idx_deliveries_user_status_delivered (user_id, status, delivered_at)");
add_idx($pdo, 'deliveries', 'idx_deliveries_order_status', "ALTER TABLE deliveries ADD INDEX idx_deliveries_order_status (order_id, status)");
add_idx($pdo, 'support_tickets', 'idx_support_tickets_user_status_updated', "ALTER TABLE support_tickets ADD INDEX idx_support_tickets_user_status_updated (user_id, status, updated_at)");
add_idx($pdo, 'support_tickets', 'idx_support_tickets_status_updated', "ALTER TABLE support_tickets ADD INDEX idx_support_tickets_status_updated (status, updated_at)");
add_idx($pdo, 'support_messages', 'idx_support_messages_ticket_created', "ALTER TABLE support_messages ADD INDEX idx_support_messages_ticket_created (ticket_id, created_at)");
add_idx($pdo, 'waiting_queue', 'idx_waiting_queue_status_category_created', "ALTER TABLE waiting_queue ADD INDEX idx_waiting_queue_status_category_created (status, category_id, created_at)");
add_idx($pdo, 'payment_idempotency', 'idx_payment_idempotency_user_scope_request', "ALTER TABLE payment_idempotency ADD INDEX idx_payment_idempotency_user_scope_request (user_id, scope, request_id)");

$credentialIndexCols = ['protocol', 'category_id', 'is_active', 'sales_paused', 'is_sold', 'status'];
$canAddCredentialIndex = true;
foreach ($credentialIndexCols as $col) {
    if (!col_exists($pdo, 'credentials', $col)) { $canAddCredentialIndex = false; break; }
}
if ($canAddCredentialIndex) {
    add_idx($pdo, 'credentials', 'idx_credentials_sellable_lookup', "ALTER TABLE credentials ADD INDEX idx_credentials_sellable_lookup (protocol, category_id, is_active, sales_paused, is_sold, status)");
}
if (col_exists($pdo, 'plan_categories', 'service_type')) {
    add_idx($pdo, 'plan_categories', 'idx_plan_categories_service_active_order', "ALTER TABLE plan_categories ADD INDEX idx_plan_categories_service_active_order (service_type, is_active, display_order)");
}

echo "Done.\n";
