<?php
session_start();
require_once '../config.php';


date_default_timezone_set('Africa/Accra');

$message = '';
$error = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['backfill'])) {
    try {
        $conn->begin_transaction();

        // Clear existing balance history
        $conn->query("DELETE FROM balance_history");

        // Get all agents
        $agentsQuery = "SELECT id, username FROM agents ORDER BY id";
        $agentsResult = $conn->query($agentsQuery);
        $agents = [];
        while ($agent = $agentsResult->fetch_assoc()) {
            $agents[] = $agent;
        }

        $totalRecords = 0;

        foreach ($agents as $agent) {
            $agentId = $agent['id'];
            
            // Get all transactions for this agent (orders and payments) ordered by date
            $transactionsQuery = "
                (SELECT 
                    'order' as type,
                    id as ref_id,
                    amount as change_amount,
                    created_at as transaction_date,
                    -1 * amount as signed_amount
                FROM orders 
                WHERE user_id = ?)
                UNION ALL
                (SELECT 
                    'payment' as type,
                    id as ref_id,
                    total_amount as change_amount,
                    payment_date as transaction_date,
                    total_amount as signed_amount
                FROM orders_payments 
                WHERE user_id = ?)
                ORDER BY transaction_date ASC
            ";
            
            $stmt = $conn->prepare($transactionsQuery);
            $stmt->bind_param('ii', $agentId, $agentId);
            $stmt->execute();
            $result = $stmt->get_result();
            
            $runningBalance = 0;
            $transactions = [];
            
            while ($transaction = $result->fetch_assoc()) {
                $transactions[] = $transaction;
            }
            
            // Process transactions in chronological order
            foreach ($transactions as $transaction) {
                $runningBalance += $transaction['signed_amount'];
                
                // Insert into balance_history
                $insertStmt = $conn->prepare("
                    INSERT INTO balance_history 
                    (agent_id, change_amount, balance_after, reason, ref_id, created_at) 
                    VALUES (?, ?, ?, ?, ?, ?)
                ");
                $insertStmt->bind_param(
                    'iddsis',
                    $agentId,
                    $transaction['signed_amount'],
                    $runningBalance,
                    $transaction['type'],
                    $transaction['ref_id'],
                    $transaction['transaction_date']
                );
                $insertStmt->execute();
                $totalRecords++;
            }
        }

        $conn->commit();
        $message = "Successfully backfilled balance history with $totalRecords records.";
        
    } catch (Exception $e) {
        $conn->rollback();
        $error = "Error during backfill: " . $e->getMessage();
    }
}

// Get statistics
$statsQuery = "
    SELECT 
        COUNT(*) as total_records,
        COUNT(DISTINCT agent_id) as unique_agents,
        MIN(created_at) as earliest_date,
        MAX(created_at) as latest_date,
        SUM(CASE WHEN reason = 'order' THEN 1 ELSE 0 END) as order_records,
        SUM(CASE WHEN reason = 'payment' THEN 1 ELSE 0 END) as payment_records
    FROM balance_history
";
$statsResult = $conn->query($statsQuery);
$stats = $statsResult->fetch_assoc();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Backfill Balance History - D-Bundles Stores Admin</title>
    <script src="https://cdn.tailwindcss.com"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
</head>
<body class="bg-gray-100">
    <div class="container mx-auto px-4 py-8">
        <div class="flex justify-between items-center mb-6">
            <h1 class="text-3xl font-bold text-gray-800">Backfill Balance History</h1>
        </div>

        <?php if ($message): ?>
            <div class="bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded mb-4">
                <i class="fas fa-check-circle mr-2"></i><?= htmlspecialchars($message) ?>
            </div>
        <?php endif; ?>

        <?php if ($error): ?>
            <div class="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded mb-4">
                <i class="fas fa-exclamation-circle mr-2"></i><?= htmlspecialchars($error) ?>
            </div>
        <?php endif; ?>

        <!-- Statistics -->
        <div class="bg-white rounded-lg shadow-md p-6 mb-6">
            <h2 class="text-xl font-semibold mb-4">Current Balance History Statistics</h2>
            <div class="grid grid-cols-1 md:grid-cols-3 gap-4">
                <div class="bg-blue-50 p-4 rounded-lg">
                    <div class="text-2xl font-bold text-blue-600"><?= number_format($stats['total_records']) ?></div>
                    <div class="text-sm text-gray-600">Total Records</div>
                </div>
                <div class="bg-green-50 p-4 rounded-lg">
                    <div class="text-2xl font-bold text-green-600"><?= number_format($stats['unique_agents']) ?></div>
                    <div class="text-sm text-gray-600">Unique Agents</div>
                </div>
                <div class="bg-purple-50 p-4 rounded-lg">
                    <div class="text-2xl font-bold text-purple-600">
                        <?= $stats['order_records'] ?> / <?= $stats['payment_records'] ?>
                    </div>
                    <div class="text-sm text-gray-600">Orders / Payments</div>
                </div>
            </div>
            <?php if ($stats['earliest_date'] && $stats['latest_date']): ?>
                <div class="mt-4 text-sm text-gray-600">
                    Date Range: <?= date('M j, Y', strtotime($stats['earliest_date'])) ?> to 
                    <?= date('M j, Y', strtotime($stats['latest_date'])) ?>
                </div>
            <?php endif; ?>
        </div>

        <!-- Backfill Form -->
        <div class="bg-white rounded-lg shadow-md p-6">
            <h2 class="text-xl font-semibold mb-4">Backfill Balance History</h2>
            <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 mt-1 mr-3"></i>
                    <div>
                        <h3 class="font-semibold text-yellow-800">Warning</h3>
                        <p class="text-yellow-700 text-sm">
                            This will clear all existing balance history records and recreate them from orders and payments data. 
                            This process cannot be undone. Make sure you have a backup before proceeding.
                        </p>
                    </div>
                </div>
            </div>
            
            <form method="POST" onsubmit="return confirm('Are you sure you want to backfill balance history? This will clear existing records.');">
                <button type="submit" name="backfill" class="bg-red-500 hover:bg-red-700 text-white font-bold py-3 px-6 rounded">
                    <i class="fas fa-database mr-2"></i>Start Backfill Process
                </button>
            </form>
        </div>

        <!-- Navigation -->
        <div class="mt-6">
            <a href="balance_history_admin.php" class="bg-green-500 hover:bg-green-700 text-white font-bold py-2 px-4 rounded">
                <i class="fas fa-eye mr-2"></i>View Balance History
            </a>
        </div>
    </div>
</body>
</html>

