<?php
// optimize_database.php - Database Performance Optimization Script
// SAFE OPERATION: This script ONLY creates indexes and analyzes tables
// NO DATA IS MODIFIED, DELETED, OR CHANGED IN ANY WAY
// Indexes only improve query speed - they do not affect existing data

require 'db.php';
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
// This script runs many metadata queries back-to-back. Force buffered mode
// to avoid MySQL "unbuffered queries are active" (2014) on some environments.
if (defined('PDO::MYSQL_ATTR_USE_BUFFERED_QUERY')) {
    $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
}

// Security: Only allow authenticated master users
if (!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] !== true) {
    die("Access Denied: Please login first.");
}

$username = strtolower($_SESSION['user'] ?? '');
$is_endie = ($username === 'endie');
$role = $_SESSION['role'] ?? '';

if (!$is_endie && strtolower($role) !== 'master') {
    die("Access Denied: Only administrators can run this script.");
}

header('Content-Type: text/html; charset=utf-8');

// Safety confirmation: must be POST with valid CSRF token.
// (GET fallback ?confirm=yes preserved for backward compatibility with
// existing bookmarks ONLY when the session csrf_token is also passed in
// the URL via &csrf=...; otherwise the form below is the new path.)
$confirmed = false;
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $csrfPost = (string)($_POST['csrf_token'] ?? '');
    if (!empty($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $csrfPost) && ($_POST['confirm'] ?? '') === 'yes') {
        $confirmed = true;
    }
} elseif (isset($_GET['confirm'], $_GET['csrf']) && $_GET['confirm'] === 'yes' && !empty($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], (string)$_GET['csrf'])) {
    $confirmed = true;
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Database Optimization</title>
    <style>
        body { font-family: Arial, sans-serif; padding: 20px; background: #f4f6f9; }
        .container { max-width: 800px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        h1 { color: #3498db; }
        .success { color: #2ecc71; }
        .error { color: #e74c3c; }
        .info { color: #3498db; }
        pre { background: #f8f9fa; padding: 10px; border-radius: 4px; overflow-x: auto; }
    </style>
</head>
<body>
    <div class="container">
        <h1>🔧 Database Performance Optimization</h1>
        
        <?php if (!$confirmed): ?>
        <div style="background: #fff3cd; border: 2px solid #ffc107; padding: 20px; border-radius: 8px; margin-bottom: 20px;">
            <h3 style="color: #856404; margin-top: 0;">⚠️ Safety Confirmation Required</h3>
            <p><strong>This script is 100% SAFE and will NOT modify any data.</strong></p>
            <p>It will only:</p>
            <ul>
                <li>✅ Create database indexes (improves query speed)</li>
                <li>✅ Analyze tables (updates statistics only)</li>
            </ul>
            <p style="color: #e74c3c; font-weight: bold;">❌ It will NOT:</p>
            <ul>
                <li>❌ Delete any data</li>
                <li>❌ Modify any records</li>
                <li>❌ Change any values</li>
                <li>❌ Affect customer data, jobs, stock, or any other data</li>
            </ul>
            <form method="POST" style="margin-top: 20px;">
                <input type="hidden" name="csrf_token" value="<?php echo htmlspecialchars($_SESSION['csrf_token'] ?? '', ENT_QUOTES, 'UTF-8'); ?>">
                <input type="hidden" name="confirm" value="yes">
                <button type="submit" class="btn" style="padding: 12px 24px; background: #28a745; color: white; border: none; border-radius: 6px; font-weight: bold; cursor: pointer;">✅ I Understand - Proceed Safely</button>
                <a href="dashboard.php" style="display: inline-block; padding: 12px 24px; margin-left: 10px; color: #333; text-decoration: none;">← Cancel</a>
            </form>
        </div>
        <?php else: ?>
        <div style="background: #d4edda; border: 2px solid #28a745; padding: 15px; border-radius: 8px; margin-bottom: 20px;">
            <strong style="color: #155724;">✅ SAFE MODE: Only creating indexes and analyzing tables. No data will be modified.</strong>
        </div>
        <pre>
<?php

echo "--- OPTIMIZING DATABASE FOR PERFORMANCE ---\n";
echo "--- SAFE OPERATION: NO DATA WILL BE MODIFIED ---\n\n";

/**
 * Whitelist table/column identifiers (defense in depth for dynamic SQL).
 */
function f2f_is_sql_identifier($name) {
    return is_string($name) && $name !== '' && (bool) preg_match('/^[a-zA-Z0-9_]+$/', $name);
}

/**
 * Table exists without SHOW TABLES LIKE ? (MySQL may not bind placeholders there).
 */
function f2f_table_exists(PDO $pdo, $tableName) {
    if (!f2f_is_sql_identifier($tableName)) {
        return false;
    }
    $stmt = $pdo->prepare(
        'SELECT 1 FROM information_schema.tables
         WHERE table_schema = DATABASE() AND table_name = ? LIMIT 1'
    );
    $stmt->execute([$tableName]);
    $exists = (bool) $stmt->fetchColumn();
    $stmt->closeCursor();
    return $exists;
}

/**
 * Column list for a table; uses fetchAll to avoid unbuffered cursor (error 2014).
 */
function f2f_column_map(PDO $pdo, $tableName) {
    if (!f2f_is_sql_identifier($tableName)) {
        return [];
    }
    $quoted = '`' . str_replace('`', '``', $tableName) . '`';
    $stmt = $pdo->query('SHOW COLUMNS FROM ' . $quoted);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();
    $map = [];
    foreach ($rows as $row) {
        $map[$row['Field']] = true;
    }
    return $map;
}

function f2f_index_exists(PDO $pdo, $tableName, $indexName) {
    if (!f2f_is_sql_identifier($tableName) || !f2f_is_sql_identifier($indexName)) {
        return false;
    }
    $stmt = $pdo->prepare(
        'SELECT 1 FROM information_schema.statistics
         WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ? LIMIT 1'
    );
    $stmt->execute([$tableName, $indexName]);
    $exists = (bool) $stmt->fetchColumn();
    $stmt->closeCursor();
    return $exists;
}

$indexes = [
    // Jobs table indexes (largest query load in api.php)
    ["name" => "idx_jobs_job_id", "table" => "jobs", "columns" => "job_id", "requires" => ["job_id"]],
    ["name" => "idx_jobs_invoice_id", "table" => "jobs", "columns" => "invoice_id", "requires" => ["invoice_id"]],
    ["name" => "idx_jobs_request_id", "table" => "jobs", "columns" => "request_id", "requires" => ["request_id"]],
    ["name" => "idx_jobs_branch_status_date", "table" => "jobs", "columns" => "branch, status, date", "requires" => ["branch", "status", "date"]],
    ["name" => "idx_jobs_is_deleted_date_created", "table" => "jobs", "columns" => "is_deleted, date, created_at", "requires" => ["is_deleted", "date", "created_at"]],
    ["name" => "idx_jobs_is_deleted_deletedat_created", "table" => "jobs", "columns" => "is_deleted, deleted_at, created_at", "requires" => ["is_deleted", "deleted_at", "created_at"]],
    ["name" => "idx_jobs_model_remarks_status", "table" => "jobs", "columns" => "model, status, date", "requires" => ["model", "status", "date"]],
    ["name" => "idx_jobs_phone", "table" => "jobs", "columns" => "phone", "requires" => ["phone"]],
    ["name" => "idx_jobs_customer", "table" => "jobs", "columns" => "customer", "requires" => ["customer"]],
    ["name" => "idx_jobs_repair_prefix", "table" => "jobs", "columns" => "repair(120)", "requires" => ["repair"]],

    // Stock list indexes (critical for stock lookups and updates)
    ["name" => "idx_stock_part_name_norm", "table" => "stock_list", "columns" => "part_name_norm", "requires" => ["part_name_norm"]],
    ["name" => "idx_stock_shortcode", "table" => "stock_list", "columns" => "shortcode", "requires" => ["shortcode"]],
    // Use full column (no prefix) for wider compatibility across MySQL variants/schema lengths.
    ["name" => "idx_stock_type_category_part", "table" => "stock_list", "columns" => "type, category, part_name", "requires" => ["type", "category", "part_name"]],
    ["name" => "idx_stock_type_device_model", "table" => "stock_list", "columns" => "type, device_model", "requires" => ["type", "device_model"]],
    ["name" => "idx_stock_supplier_type", "table" => "stock_list", "columns" => "supplier, type", "requires" => ["supplier", "type"]],
    ["name" => "idx_stock_part_norm_type_cat", "table" => "stock_list", "columns" => "part_name_norm, type, category", "requires" => ["part_name_norm", "type", "category"]],

    // Movements / transfers / logs
    ["name" => "idx_stock_movements_stock_date", "table" => "stock_movements", "columns" => "stock_id, movement_date", "requires" => ["stock_id", "movement_date"]],
    ["name" => "idx_stock_movements_type_date", "table" => "stock_movements", "columns" => "movement_type, movement_date", "requires" => ["movement_type", "movement_date"]],
    ["name" => "idx_stock_transfers_date", "table" => "stock_transfers", "columns" => "date", "requires" => ["date"]],
    ["name" => "idx_stock_transfers_route_date", "table" => "stock_transfers", "columns" => "from_branch, to_branch, date", "requires" => ["from_branch", "to_branch", "date"]],
    ["name" => "idx_activity_created_at", "table" => "activity_logs", "columns" => "created_at", "requires" => ["created_at"]],
    ["name" => "idx_activity_action_created", "table" => "activity_logs", "columns" => "action, created_at", "requires" => ["action", "created_at"]],

    // Purchase order workflow
    ["name" => "idx_po_status_order_date", "table" => "purchase_orders", "columns" => "status, order_date, created_at", "requires" => ["status", "order_date", "created_at"]],
    ["name" => "idx_poi_po_id", "table" => "purchase_order_items", "columns" => "po_id", "requires" => ["po_id"]],
    // purchase_order_items has schema variants across deployments (part_name/item_name/product_name).
    ["name" => "idx_poi_part_name", "table" => "purchase_order_items", "columns" => "part_name", "requires" => ["part_name"], "alternatives" => ["item_name", "product_name", "name"]],

    // Other common filter tables
    ["name" => "idx_credit_notes_branch_date", "table" => "credit_notes", "columns" => "branch, date, created_at", "requires" => ["branch", "date", "created_at"]],
    ["name" => "idx_expenses_branch_date", "table" => "expenses", "columns" => "branch, date", "requires" => ["branch", "date"]],
    ["name" => "idx_customers_phone", "table" => "customers", "columns" => "phone", "requires" => ["phone"]],
    ["name" => "idx_users_role_branch", "table" => "users", "columns" => "role, assigned_branch", "requires" => ["role", "assigned_branch"]],
    ["name" => "idx_login_username_time", "table" => "login_attempts", "columns" => "username, attempt_time", "requires" => ["username", "attempt_time"]],
    ["name" => "idx_login_ip_time", "table" => "login_attempts", "columns" => "ip_address, attempt_time", "requires" => ["ip_address", "attempt_time"]],
];

$success = 0;
$skipped = 0;
$errors = 0;

foreach ($indexes as $idx) {
    $indexName = $idx['name'];
    $tableName = $idx['table'];
    $columns = $idx['columns'];
    $requiredColumns = $idx['requires'];
    $alternativeColumns = $idx['alternatives'] ?? [];
    
    try {
        if (!f2f_table_exists($pdo, $tableName)) {
            echo "⚠️  Table '$tableName' does not exist. Skipping index: $indexName\n";
            $skipped++;
            continue;
        }

        $existingCols = f2f_column_map($pdo, $tableName);
        $missing = [];
        foreach ($requiredColumns as $reqCol) {
            if (!isset($existingCols[$reqCol])) {
                $missing[] = $reqCol;
            }
        }
        if (!empty($missing)) {
            // Fallback for schema variants: switch to first available alternative column.
            if (count($requiredColumns) === 1 && !empty($alternativeColumns)) {
                $fallback = null;
                foreach ($alternativeColumns as $altCol) {
                    if (isset($existingCols[$altCol])) {
                        $fallback = $altCol;
                        break;
                    }
                }
                if ($fallback) {
                    $columns = str_replace($requiredColumns[0], $fallback, $columns);
                    echo "ℹ️  Using fallback column '$fallback' for index $indexName on '$tableName'\n";
                } else {
                    echo "⚠️  Missing columns in '$tableName' (" . implode(', ', $missing) . "). Skipping index: $indexName\n";
                    $skipped++;
                    continue;
                }
            } else {
                echo "⚠️  Missing columns in '$tableName' (" . implode(', ', $missing) . "). Skipping index: $indexName\n";
                $skipped++;
                continue;
            }
        }

        if (!f2f_index_exists($pdo, $tableName, $indexName)) {
            // SAFETY: Only CREATE INDEX - this does NOT modify data
            // Indexes are metadata structures that improve query performance
            $createSql = "CREATE INDEX `$indexName` ON `$tableName`($columns)";
            $pdo->exec($createSql);
            echo "✅ Created index: $indexName on $tableName($columns) [SAFE - No data modified]\n";
            $success++;
        } else {
            echo "ℹ️  Index already exists: $indexName on $tableName\n";
            $skipped++;
        }
    } catch (Exception $e) {
        echo "❌ Error creating index $indexName: " . $e->getMessage() . "\n";
        $errors++;
    }
}

// Optimize tables (ANALYZE only - updates statistics, does NOT modify data)
echo "\n--- ANALYZING TABLES (Updates Statistics Only - No Data Modified) ---\n";
$tables = [
    'jobs',
    'stock_list',
    'customers',
    'activity_logs',
    'expenses',
    'users',
    'login_attempts',
    'stock_movements',
    'stock_transfers',
    'purchase_orders',
    'purchase_order_items',
    'credit_notes'
];

foreach ($tables as $table) {
    try {
        if (!f2f_is_sql_identifier($table)) {
            echo "⚠️  Invalid table name skipped: $table\n";
            continue;
        }
        if (!f2f_table_exists($pdo, $table)) {
            echo "⚠️  Table does not exist, skipping analyze: $table\n";
            continue;
        }
        $quoted = '`' . str_replace('`', '``', $table) . '`';
        $pdo->exec('ANALYZE TABLE ' . $quoted);
        echo "✅ Analyzed table: $table [SAFE - Statistics updated only, no data changed]\n";
    } catch (Exception $e) {
        echo "⚠️  Could not analyze table $table: " . $e->getMessage() . "\n";
    }
}

echo "\n--- SUMMARY ---\n";
echo "✅ Created: $success indexes\n";
echo "ℹ️  Skipped: $skipped indexes (already exist)\n";
if ($errors > 0) {
    echo "❌ Errors: $errors\n";
}
echo "\n✅ Database optimization complete! Performance should be improved.\n";
echo "\n--- DATA SAFETY VERIFICATION ---\n";
echo "✅ All operations completed safely\n";
echo "✅ No data was modified, deleted, or changed\n";
echo "✅ All customer data, jobs, stock, and records remain intact\n";
echo "✅ Only indexes (performance structures) were added\n";
echo "✅ Only table statistics were updated\n";

?>
        </pre>
        <div style="background: #d4edda; border: 2px solid #28a745; padding: 15px; border-radius: 8px; margin-top: 20px;">
            <strong style="color: #155724;">✅ Operation Complete - All Data Safe</strong>
            <p style="margin: 10px 0 0 0; color: #155724;">No customer data, jobs, stock, or any records were modified. Only performance indexes were added.</p>
        </div>
        <p style="margin-top: 20px;"><a href="dashboard.php">← Back to Dashboard</a></p>
        <?php endif; ?>
    </div>
</body>
</html>
