<?php
/**
 * KDS Record Cleanup System
 * Automatically cleans up old KDS records based on retention settings
 */

include 'db.php';

/**
 * Perform KDS cleanup based on retention settings
 * @param mysqli $conn Database connection
 * @param int|null $retention_days Override retention days (optional)
 * @param bool $dry_run If true, only count records without deleting
 * @return array Cleanup results
 */
function performKDSCleanup($conn, $retention_days = null, $dry_run = false) {
    $start_time = microtime(true);
    
    try {
        // Get cleanup settings
        if ($retention_days === null) {
            $retention_result = $conn->query("SELECT value FROM settings WHERE code = 'kds_record_retention_days'");
            $retention_days = $retention_result ? intval($retention_result->fetch_assoc()['value']) : 7;
        }
        
        // Validate retention days (1-90 days)
        $retention_days = max(1, min(90, intval($retention_days)));
        
        // Check if auto cleanup is enabled
        $auto_cleanup_result = $conn->query("SELECT value FROM settings WHERE code = 'kds_auto_cleanup_enabled'");
        $auto_cleanup_enabled = $auto_cleanup_result ? $auto_cleanup_result->fetch_assoc()['value'] === '1' : true;
        
        if (!$auto_cleanup_enabled && !$dry_run) {
            return [
                'success' => false,
                'message' => 'KDS auto cleanup is disabled',
                'retention_days' => $retention_days
            ];
        }
        
        // Calculate cutoff date
        $cutoff_date = date('Y-m-d H:i:s', strtotime("-{$retention_days} days"));
        
        $conn->begin_transaction();
        
        $orders_deleted = 0;
        $order_items_deleted = 0;
        $activity_logs_deleted = 0;
        
        // Step 1: Count/Delete old activity logs
        $activity_count_query = "SELECT COUNT(*) as count FROM kds_activity_log WHERE created_at < ?";
        $stmt = $conn->prepare($activity_count_query);
        $stmt->bind_param("s", $cutoff_date);
        $stmt->execute();
        $activity_logs_count = $stmt->get_result()->fetch_assoc()['count'];
        $stmt->close();
        
        if (!$dry_run && $activity_logs_count > 0) {
            $stmt = $conn->prepare("DELETE FROM kds_activity_log WHERE created_at < ?");
            $stmt->bind_param("s", $cutoff_date);
            $stmt->execute();
            $activity_logs_deleted = $stmt->affected_rows;
            $stmt->close();
        } else {
            $activity_logs_deleted = $activity_logs_count;
        }
        
        // Step 2: Get old orders to delete
        $old_orders_query = "
            SELECT ko.id 
            FROM kds_orders ko 
            WHERE ko.created_at < ? 
            AND ko.order_status IN ('served', 'cancelled')
        ";
        
        $stmt = $conn->prepare($old_orders_query);
        $stmt->bind_param("s", $cutoff_date);
        $stmt->execute();
        $old_orders_result = $stmt->get_result();
        
        $old_order_ids = [];
        while ($row = $old_orders_result->fetch_assoc()) {
            $old_order_ids[] = $row['id'];
        }
        $stmt->close();
        
        $orders_count = count($old_order_ids);
        
        if ($orders_count > 0) {
            $order_ids_string = implode(',', $old_order_ids);
            
            // Step 3: Count/Delete order items for old orders
            $items_count_query = "SELECT COUNT(*) as count FROM kds_order_items WHERE kds_order_id IN ($order_ids_string)";
            $items_count_result = $conn->query($items_count_query);
            $order_items_count = $items_count_result->fetch_assoc()['count'];
            
            if (!$dry_run) {
                // Delete order items first (foreign key constraint)
                $conn->query("DELETE FROM kds_order_items WHERE kds_order_id IN ($order_ids_string)");
                $order_items_deleted = $conn->affected_rows;
                
                // Delete orders
                $conn->query("DELETE FROM kds_orders WHERE id IN ($order_ids_string)");
                $orders_deleted = $conn->affected_rows;
            } else {
                $order_items_deleted = $order_items_count;
                $orders_deleted = $orders_count;
            }
        }
        
        $total_records = $orders_deleted + $order_items_deleted + $activity_logs_deleted;
        $execution_time = round((microtime(true) - $start_time) * 1000);
        
        // Log cleanup operation (only if not dry run)
        if (!$dry_run) {
            $log_stmt = $conn->prepare("
                INSERT INTO kds_cleanup_log 
                (retention_days, orders_deleted, order_items_deleted, activity_logs_deleted, 
                 total_records_deleted, execution_time_ms, status, message) 
                VALUES (?, ?, ?, ?, ?, ?, 'success', ?)
            ");
            
            $message = "Cleanup completed successfully. Cutoff date: $cutoff_date";
            $log_stmt->bind_param("iiiiiis", 
                $retention_days, $orders_deleted, $order_items_deleted, 
                $activity_logs_deleted, $total_records, $execution_time, $message
            );
            $log_stmt->execute();
            $log_stmt->close();
            
            // Update last cleanup date
            $update_stmt = $conn->prepare("UPDATE settings SET value = ? WHERE code = 'kds_last_cleanup_date'");
            $current_timestamp = date('Y-m-d H:i:s');
            $update_stmt->bind_param("s", $current_timestamp);
            $update_stmt->execute();
            $update_stmt->close();
        }
        
        $conn->commit();
        
        return [
            'success' => true,
            'retention_days' => $retention_days,
            'cutoff_date' => $cutoff_date,
            'orders_deleted' => $orders_deleted,
            'order_items_deleted' => $order_items_deleted,
            'activity_logs_deleted' => $activity_logs_deleted,
            'total_records_deleted' => $total_records,
            'execution_time_ms' => $execution_time,
            'dry_run' => $dry_run,
            'message' => $dry_run ? 'Dry run completed - no records deleted' : 'Cleanup completed successfully'
        ];
        
    } catch (Exception $e) {
        $conn->rollback();
        
        $execution_time = round((microtime(true) - $start_time) * 1000);
        
        // Log failed cleanup
        if (!$dry_run) {
            $error_stmt = $conn->prepare("
                INSERT INTO kds_cleanup_log 
                (retention_days, execution_time_ms, status, message) 
                VALUES (?, ?, 'failed', ?)
            ");
            $error_message = "Cleanup failed: " . $e->getMessage();
            $error_stmt->bind_param("iis", $retention_days, $execution_time, $error_message);
            $error_stmt->execute();
            $error_stmt->close();
        }
        
        return [
            'success' => false,
            'retention_days' => $retention_days,
            'execution_time_ms' => $execution_time,
            'message' => 'Cleanup failed: ' . $e->getMessage(),
            'error' => $e->getMessage()
        ];
    }
}

/**
 * Get KDS cleanup statistics
 * @param mysqli $conn Database connection
 * @return array Statistics
 */
function getKDSCleanupStats($conn) {
    try {
        // Get current record counts
        $stats = [];
        
        // Count current records
        $orders_result = $conn->query("SELECT COUNT(*) as count FROM kds_orders");
        $stats['current_orders'] = $orders_result->fetch_assoc()['count'];
        
        $items_result = $conn->query("SELECT COUNT(*) as count FROM kds_order_items");
        $stats['current_order_items'] = $items_result->fetch_assoc()['count'];
        
        $logs_result = $conn->query("SELECT COUNT(*) as count FROM kds_activity_log");
        $stats['current_activity_logs'] = $logs_result->fetch_assoc()['count'];
        
        // Get retention settings
        $retention_result = $conn->query("SELECT value FROM settings WHERE code = 'kds_record_retention_days'");
        $stats['retention_days'] = $retention_result ? intval($retention_result->fetch_assoc()['value']) : 7;
        
        $auto_cleanup_result = $conn->query("SELECT value FROM settings WHERE code = 'kds_auto_cleanup_enabled'");
        $stats['auto_cleanup_enabled'] = $auto_cleanup_result ? $auto_cleanup_result->fetch_assoc()['value'] === '1' : true;
        
        $last_cleanup_result = $conn->query("SELECT value FROM settings WHERE code = 'kds_last_cleanup_date'");
        $stats['last_cleanup_date'] = $last_cleanup_result ? $last_cleanup_result->fetch_assoc()['value'] : null;
        
        // Get last cleanup log entry
        $last_log_result = $conn->query("
            SELECT * FROM kds_cleanup_log 
            ORDER BY cleanup_date DESC LIMIT 1
        ");
        $stats['last_cleanup_log'] = $last_log_result ? $last_log_result->fetch_assoc() : null;
        
        // Count records that would be deleted with current settings
        $cutoff_date = date('Y-m-d H:i:s', strtotime("-{$stats['retention_days']} days"));
        
        $old_logs_result = $conn->query("SELECT COUNT(*) as count FROM kds_activity_log WHERE created_at < '$cutoff_date'");
        $stats['old_activity_logs'] = $old_logs_result->fetch_assoc()['count'];
        
        $old_orders_result = $conn->query("
            SELECT COUNT(*) as count FROM kds_orders 
            WHERE created_at < '$cutoff_date' 
            AND order_status IN ('served', 'cancelled')
        ");
        $stats['old_orders'] = $old_orders_result->fetch_assoc()['count'];
        
        return [
            'success' => true,
            'stats' => $stats
        ];
        
    } catch (Exception $e) {
        return [
            'success' => false,
            'message' => 'Failed to get cleanup stats: ' . $e->getMessage()
        ];
    }
}

// Command line execution
if (php_sapi_name() === 'cli') {
    echo "KDS Cleanup System\n";
    echo "==================\n\n";
    
    $dry_run = in_array('--dry-run', $argv);
    $retention_days = null;
    
    // Parse retention days argument
    foreach ($argv as $arg) {
        if (strpos($arg, '--retention=') === 0) {
            $retention_days = intval(substr($arg, 12));
        }
    }
    
    if ($dry_run) {
        echo "Running in DRY RUN mode - no records will be deleted\n\n";
    }
    
    $result = performKDSCleanup($conn, $retention_days, $dry_run);
    
    if ($result['success']) {
        echo "✅ " . $result['message'] . "\n";
        echo "📊 Retention Period: " . $result['retention_days'] . " days\n";
        echo "📅 Cutoff Date: " . $result['cutoff_date'] . "\n";
        echo "🗑️  Orders " . ($dry_run ? 'to be deleted' : 'deleted') . ": " . $result['orders_deleted'] . "\n";
        echo "🗑️  Order Items " . ($dry_run ? 'to be deleted' : 'deleted') . ": " . $result['order_items_deleted'] . "\n";
        echo "🗑️  Activity Logs " . ($dry_run ? 'to be deleted' : 'deleted') . ": " . $result['activity_logs_deleted'] . "\n";
        echo "📈 Total Records: " . $result['total_records_deleted'] . "\n";
        echo "⏱️  Execution Time: " . $result['execution_time_ms'] . "ms\n";
    } else {
        echo "❌ " . $result['message'] . "\n";
        exit(1);
    }
}
?>
