<?php
/**
 * Delete Old Orders - Admin Tool
 * Allows admin to delete orders older than specified time period
 */

require_once '../config.php';
require_once '../app/classes/AdminAuth.php';

$adminAuth = new AdminAuth($conn);
$adminAuth->requireAuth('admin_login.php');

$message = '';
$messageType = '';
$previewData = null;

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['delete_orders'])) {
    $months = isset($_POST['months']) ? intval($_POST['months']) : 1;
    
    if ($months < 1 || $months > 12) {
        $message = "Please enter a valid number of months (1-12).";
        $messageType = 'error';
    } else {
        // Check if orders table has created_at column
        $checkColumn = $conn->query("SHOW COLUMNS FROM orders LIKE 'created_at'");
        $timestampColumn = 'created_at';
        
        if (!$checkColumn || $checkColumn->num_rows === 0) {
            $checkColumn2 = $conn->query("SHOW COLUMNS FROM orders LIKE 'order_date'");
            if ($checkColumn2 && $checkColumn2->num_rows > 0) {
                $timestampColumn = 'order_date';
            } else {
                $message = "Could not find timestamp column in orders table.";
                $messageType = 'error';
            }
            if ($checkColumn2) $checkColumn2->close();
        }
        if ($checkColumn) $checkColumn->close();
        
        if (empty($message)) {
            // Get count before deletion
            $countQuery = "SELECT COUNT(*) as count FROM orders WHERE $timestampColumn < DATE_SUB(NOW(), INTERVAL ? MONTH)";
            $countStmt = $conn->prepare($countQuery);
            $countStmt->bind_param("i", $months);
            $countStmt->execute();
            $countResult = $countStmt->get_result();
            $countData = $countResult->fetch_assoc();
            $deletedCount = $countData['count'];
            $countStmt->close();
            
            if ($deletedCount > 0) {
                // Delete orders
                $deleteQuery = "DELETE FROM orders WHERE $timestampColumn < DATE_SUB(NOW(), INTERVAL ? MONTH)";
                $deleteStmt = $conn->prepare($deleteQuery);
                $deleteStmt->bind_param("i", $months);
                
                if ($deleteStmt->execute()) {
                    $message = "Successfully deleted $deletedCount order(s) older than $months month(s).";
                    $messageType = 'success';
                } else {
                    $message = "Error deleting orders: " . mysqli_error($conn);
                    $messageType = 'error';
                }
                $deleteStmt->close();
            } else {
                $message = "No orders found older than $months month(s).";
                $messageType = 'info';
            }
        }
    }
}

// Get preview data
if (isset($_GET['preview'])) {
    $months = isset($_GET['months']) ? intval($_GET['months']) : 1;
    
    $checkColumn = $conn->query("SHOW COLUMNS FROM orders LIKE 'created_at'");
    $timestampColumn = 'created_at';
    
    if (!$checkColumn || $checkColumn->num_rows === 0) {
        $checkColumn2 = $conn->query("SHOW COLUMNS FROM orders LIKE 'order_date'");
        if ($checkColumn2 && $checkColumn2->num_rows > 0) {
            $timestampColumn = 'order_date';
        }
        if ($checkColumn2) $checkColumn2->close();
    }
    if ($checkColumn) $checkColumn->close();
    
    $previewQuery = "SELECT 
                        COUNT(*) as total_orders,
                        MIN($timestampColumn) as oldest_order,
                        MAX($timestampColumn) as newest_order,
                        SUM(amount) as total_amount
                     FROM orders
                     WHERE $timestampColumn < DATE_SUB(NOW(), INTERVAL ? MONTH)";
    
    $previewStmt = $conn->prepare($previewQuery);
    $previewStmt->bind_param("i", $months);
    $previewStmt->execute();
    $previewResult = $previewStmt->get_result();
    $previewData = $previewResult->fetch_assoc();
    $previewStmt->close();
}
?>

<!-- Delete Old Orders Page Content -->
<div class="container mx-auto px-4 py-8">
    <div class="mb-6">
        <h1 class="text-3xl font-bold text-gray-900">Delete Old Orders</h1>
        <p class="text-gray-600 mt-2">Remove orders older than specified time period from the database</p>
    </div>

    <?php if ($message): ?>
    <div class="mb-4 p-4 rounded-lg <?php 
        echo $messageType === 'success' ? 'bg-green-100 text-green-800' : 
            ($messageType === 'error' ? 'bg-red-100 text-red-800' : 'bg-blue-100 text-blue-800'); 
    ?>">
        <i class="fas <?php 
            echo $messageType === 'success' ? 'fa-check-circle' : 
                ($messageType === 'error' ? 'fa-exclamation-circle' : 'fa-info-circle'); 
        ?> mr-2"></i>
        <?php echo htmlspecialchars($message); ?>
    </div>
    <?php endif; ?>

    <div class="grid grid-cols-1 lg:grid-cols-2 gap-6">
        <!-- Delete Form -->
        <div class="bg-white rounded-lg shadow-md p-6">
            <h2 class="text-xl font-semibold text-gray-900 mb-4">Delete Orders</h2>
            
            <form method="POST" onsubmit="return confirm('Are you sure you want to delete orders older than the specified period? This action cannot be undone!');">
                <div class="mb-4">
                    <label for="months" class="block text-sm font-medium text-gray-700 mb-2">
                        Delete orders older than (months)
                    </label>
                    <input 
                        type="number" 
                        id="months" 
                        name="months" 
                        min="1" 
                        max="12" 
                        value="1" 
                        required
                        class="w-full px-3 py-2 border border-gray-300 rounded-md focus:outline-none focus:ring-2 focus:ring-blue-500"
                    >
                    <p class="text-xs text-gray-500 mt-1">Enter number of months (1-12). Orders older than this will be deleted.</p>
                </div>
                
                <div class="bg-yellow-50 border border-yellow-200 rounded-lg p-4 mb-4">
                    <div class="flex items-start">
                        <i class="fas fa-exclamation-triangle text-yellow-600 mr-2 mt-0.5"></i>
                        <div class="text-sm text-yellow-800">
                            <p class="font-semibold mb-1">Warning:</p>
                            <ul class="list-disc list-inside space-y-1">
                                <li>This action cannot be undone</li>
                                <li>Make sure you have a database backup</li>
                                <li>Only orders from the 'orders' table will be deleted</li>
                            </ul>
                        </div>
                    </div>
                </div>
                
                <div class="flex space-x-3">
                    <button 
                        type="submit" 
                        name="delete_orders"
                        class="px-6 py-2 bg-red-600 text-white rounded-md hover:bg-red-700 focus:outline-none focus:ring-2 focus:ring-red-500"
                    >
                        <i class="fas fa-trash mr-2"></i>
                        Delete Orders
                    </button>
                    
                    <a 
                        href="?page=delete_old_orders&preview=1&months=<?php echo isset($_GET['months']) ? intval($_GET['months']) : 1; ?>" 
                        class="px-6 py-2 bg-blue-600 text-white rounded-md hover:bg-blue-700 focus:outline-none focus:ring-2 focus:ring-blue-500"
                    >
                        <i class="fas fa-eye mr-2"></i>
                        Preview
                    </a>
                </div>
            </form>
        </div>
        
        <!-- Preview Results -->
        <div class="bg-white rounded-lg shadow-md p-6">
            <h2 class="text-xl font-semibold text-gray-900 mb-4">Preview</h2>
            
            <?php if ($previewData): ?>
            <div class="space-y-4">
                <div class="bg-blue-50 border border-blue-200 rounded-lg p-4">
                    <div class="flex items-center justify-between mb-2">
                        <span class="text-sm font-medium text-gray-700">Orders to Delete:</span>
                        <span class="text-lg font-bold text-blue-600"><?php echo number_format($previewData['total_orders']); ?></span>
                    </div>
                </div>
                
                <?php if ($previewData['total_orders'] > 0): ?>
                <div class="space-y-2 text-sm">
                    <div class="flex justify-between">
                        <span class="text-gray-600">Oldest Order:</span>
                        <span class="font-medium"><?php echo $previewData['oldest_order'] ? date('Y-m-d H:i:s', strtotime($previewData['oldest_order'])) : 'N/A'; ?></span>
                    </div>
                    <div class="flex justify-between">
                        <span class="text-gray-600">Newest Order:</span>
                        <span class="font-medium"><?php echo $previewData['newest_order'] ? date('Y-m-d H:i:s', strtotime($previewData['newest_order'])) : 'N/A'; ?></span>
                    </div>
                    <div class="flex justify-between">
                        <span class="text-gray-600">Total Amount:</span>
                        <span class="font-medium"><?php echo number_format($previewData['total_amount'] ?? 0, 2); ?> GHS</span>
                    </div>
                </div>
                <?php else: ?>
                <p class="text-gray-500 text-sm">No orders found matching the criteria.</p>
                <?php endif; ?>
            </div>
            <?php else: ?>
            <p class="text-gray-500 text-sm">Click "Preview" to see how many orders will be deleted.</p>
            <?php endif; ?>
        </div>
    </div>
    
    <!-- SQL Query Reference -->
    <div class="mt-6 bg-gray-50 rounded-lg shadow-md p-6">
        <h2 class="text-xl font-semibold text-gray-900 mb-4">SQL Query Reference</h2>
        <div class="bg-gray-800 rounded-lg p-4 overflow-x-auto">
            <code class="text-green-400 text-sm">
                <div class="mb-2">-- Preview query (check before deleting):</div>
                <div>SELECT COUNT(*) as total_orders_to_delete</div>
                <div>FROM orders</div>
                <div>WHERE created_at &lt; DATE_SUB(NOW(), INTERVAL 1 MONTH);</div>
                <div class="mt-4 mb-2">-- Delete query:</div>
                <div>DELETE FROM orders</div>
                <div>WHERE created_at &lt; DATE_SUB(NOW(), INTERVAL 1 MONTH);</div>
            </code>
        </div>
    </div>
</div>


