<?php
/**
 * Database Optimization Script
 * Adds indexes to improve query performance
 * Run this once to optimize your database
 */

date_default_timezone_set('Asia/Colombo');
require_once 'config.php';

// Only allow admin access
session_start();
if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'admin') {
    die('Access denied. Admin only.');
}

$optimizations = [];
$errors = [];

echo "<!DOCTYPE html>
<html>
<head>
    <title>Database Optimization</title>
    <link href='https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css' rel='stylesheet'>
</head>
<body class='bg-light'>
<div class='container mt-5'>
    <h2 class='mb-4'><i class='fas fa-database'></i> Database Optimization</h2>
    <div class='card'>
        <div class='card-body'>";

// 1. Add index on sales.sale_date for faster date queries
try {
    $result = $conn->query("SHOW INDEX FROM sales WHERE Key_name = 'idx_sale_date'");
    if ($result->num_rows == 0) {
        $conn->query("ALTER TABLE sales ADD INDEX idx_sale_date (sale_date)");
        $optimizations[] = "✓ Added index on sales.sale_date";
    } else {
        $optimizations[] = "✓ Index on sales.sale_date already exists";
    }
} catch (Exception $e) {
    $errors[] = "✗ Error adding index on sales.sale_date: " . $e->getMessage();
}

// 2. Add index on products.stock for low stock queries
try {
    $result = $conn->query("SHOW INDEX FROM products WHERE Key_name = 'idx_stock'");
    if ($result->num_rows == 0) {
        $conn->query("ALTER TABLE products ADD INDEX idx_stock (stock)");
        $optimizations[] = "✓ Added index on products.stock";
    } else {
        $optimizations[] = "✓ Index on products.stock already exists";
    }
} catch (Exception $e) {
    $errors[] = "✗ Error adding index on products.stock: " . $e->getMessage();
}

// 3. Add index on products.name for search queries
try {
    $result = $conn->query("SHOW INDEX FROM products WHERE Key_name = 'idx_name'");
    if ($result->num_rows == 0) {
        $conn->query("ALTER TABLE products ADD INDEX idx_name (name)");
        $optimizations[] = "✓ Added index on products.name";
    } else {
        $optimizations[] = "✓ Index on products.name already exists";
    }
} catch (Exception $e) {
    $errors[] = "✗ Error adding index on products.name: " . $e->getMessage();
}

// 4. Add index on products.category for filtering
try {
    $result = $conn->query("SHOW INDEX FROM products WHERE Key_name = 'idx_category'");
    if ($result->num_rows == 0) {
        $conn->query("ALTER TABLE products ADD INDEX idx_category (category)");
        $optimizations[] = "✓ Added index on products.category";
    } else {
        $optimizations[] = "✓ Index on products.category already exists";
    }
} catch (Exception $e) {
    $errors[] = "✗ Error adding index on products.category: " . $e->getMessage();
}

// 5. Add index on sale_items.sale_id for faster joins
try {
    $result = $conn->query("SHOW INDEX FROM sale_items WHERE Key_name = 'idx_sale_id'");
    if ($result->num_rows == 0) {
        $conn->query("ALTER TABLE sale_items ADD INDEX idx_sale_id (sale_id)");
        $optimizations[] = "✓ Added index on sale_items.sale_id";
    } else {
        $optimizations[] = "✓ Index on sale_items.sale_id already exists";
    }
} catch (Exception $e) {
    $errors[] = "✗ Error adding index on sale_items.sale_id: " . $e->getMessage();
}

// 6. Add index on sale_items.product_id for faster joins
try {
    $result = $conn->query("SHOW INDEX FROM sale_items WHERE Key_name = 'idx_product_id'");
    if ($result->num_rows == 0) {
        $conn->query("ALTER TABLE sale_items ADD INDEX idx_product_id (product_id)");
        $optimizations[] = "✓ Added index on sale_items.product_id";
    } else {
        $optimizations[] = "✓ Index on sale_items.product_id already exists";
    }
} catch (Exception $e) {
    $errors[] = "✗ Error adding index on sale_items.product_id: " . $e->getMessage();
}

// 7. Add index on opening_cash.cash_date
try {
    $result = $conn->query("SHOW INDEX FROM opening_cash WHERE Key_name = 'idx_cash_date'");
    if ($result->num_rows == 0) {
        $conn->query("ALTER TABLE opening_cash ADD INDEX idx_cash_date (cash_date)");
        $optimizations[] = "✓ Added index on opening_cash.cash_date";
    } else {
        $optimizations[] = "✓ Index on opening_cash.cash_date already exists";
    }
} catch (Exception $e) {
    $errors[] = "✗ Error adding index on opening_cash.cash_date: " . $e->getMessage();
}

// 8. Add composite index on products for common searches
try {
    $result = $conn->query("SHOW INDEX FROM products WHERE Key_name = 'idx_stock_name'");
    if ($result->num_rows == 0) {
        $conn->query("ALTER TABLE products ADD INDEX idx_stock_name (stock, name)");
        $optimizations[] = "✓ Added composite index on products (stock, name)";
    } else {
        $optimizations[] = "✓ Composite index on products (stock, name) already exists";
    }
} catch (Exception $e) {
    $errors[] = "✗ Error adding composite index: " . $e->getMessage();
}

// 9. Optimize tables
try {
    $tables = ['products', 'sales', 'sale_items', 'customers', 'categories', 'opening_cash'];
    foreach ($tables as $table) {
        $conn->query("OPTIMIZE TABLE $table");
    }
    $optimizations[] = "✓ Optimized all tables";
} catch (Exception $e) {
    $errors[] = "✗ Error optimizing tables: " . $e->getMessage();
}

// Display results
echo "<h5 class='text-success'>Optimizations Applied:</h5><ul class='list-group mb-3'>";
foreach ($optimizations as $opt) {
    echo "<li class='list-group-item list-group-item-success'>$opt</li>";
}
echo "</ul>";

if (!empty($errors)) {
    echo "<h5 class='text-danger'>Errors:</h5><ul class='list-group mb-3'>";
    foreach ($errors as $err) {
        echo "<li class='list-group-item list-group-item-danger'>$err</li>";
    }
    echo "</ul>";
}

// Show current table sizes and row counts
echo "<h5 class='mt-4'>Database Statistics:</h5>";
echo "<table class='table table-bordered'>";
echo "<thead><tr><th>Table</th><th>Rows</th><th>Size</th><th>Indexes</th></tr></thead><tbody>";

$stats_query = "
    SELECT 
        table_name,
        table_rows,
        ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
        ROUND((index_length / 1024 / 1024), 2) AS index_size_mb
    FROM information_schema.TABLES 
    WHERE table_schema = DATABASE()
    ORDER BY (data_length + index_length) DESC
";

$result = $conn->query($stats_query);
while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td><strong>{$row['table_name']}</strong></td>";
    echo "<td>" . number_format($row['table_rows']) . "</td>";
    echo "<td>{$row['size_mb']} MB</td>";
    echo "<td>{$row['index_size_mb']} MB</td>";
    echo "</tr>";
}

echo "</tbody></table>";

echo "<div class='alert alert-info mt-4'>
    <h5>Performance Tips:</h5>
    <ul>
        <li>Indexes have been added to speed up common queries</li>
        <li>Dashboard now caches results for 10 seconds</li>
        <li>Products page is now paginated (50 per page)</li>
        <li>API responses are cached by the browser</li>
        <li>JavaScript loads asynchronously</li>
        <li>Auto-refresh interval increased to 60 seconds</li>
    </ul>
</div>";

echo "<a href='dashboard.php' class='btn btn-primary'>Back to Dashboard</a>";
echo "</div></div></div></body></html>";

$conn->close();
?>
