<?php
// Get filter parameters
$filterStartDate = isset($_GET['start_date']) ? $_GET['start_date'] : '';
$filterEndDate = isset($_GET['end_date']) ? $_GET['end_date'] : '';
$filterReason = isset($_GET['reason']) ? $_GET['reason'] : '';
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 20; // Records per page

// Validate limit to prevent integer overflow
if ($limit < 1) {
    $limit = 1;
} elseif ($limit > 1000) { // Reasonable upper limit
    $limit = 1000;
}

// Validate and sanitize page number to prevent integer overflow
if ($page < 1) {
    $page = 1;
} elseif ($page > 10000) { // Reasonable upper limit
    $page = 10000;
}

$offset = ($page - 1) * $limit;

// Additional safety check for offset to prevent integer overflow
if ($offset < 0) {
    $offset = 0;
} elseif ($offset > 200000) { // Reasonable upper limit (10000 pages * 20 records)
    $offset = 200000;
}

// Build WHERE clause - ensure user only sees their own balance history
$whereConditions = ["bh.agent_id = ?"];
$params = [$user_id];
$paramTypes = 'i';

if ($filterStartDate && $filterEndDate) {
    $whereConditions[] = "DATE(bh.created_at) BETWEEN ? AND ?";
    $params[] = $filterStartDate;
    $params[] = $filterEndDate;
    $paramTypes .= 'ss';
} elseif ($filterStartDate) {
    $whereConditions[] = "DATE(bh.created_at) >= ?";
    $params[] = $filterStartDate;
    $paramTypes .= 's';
} elseif ($filterEndDate) {
    $whereConditions[] = "DATE(bh.created_at) <= ?";
    $params[] = $filterEndDate;
    $paramTypes .= 's';
}

if ($filterReason) {
    $whereConditions[] = "bh.reason = ?";
    $params[] = $filterReason;
    $paramTypes .= 's';
}

$whereClause = 'WHERE ' . implode(' AND ', $whereConditions);

// Get total count for pagination with error handling
$countQuery = "SELECT COUNT(*) as total FROM balance_history bh $whereClause";
$countStmt = $conn->prepare($countQuery);
if (!$countStmt) {
    error_log("Count query prepare error in user_balance_history.php: " . $conn->error);
    $totalRecords = 0;
    $totalPages = 0;
} else {
    $countStmt->bind_param($paramTypes, ...$params);
    if (!$countStmt->execute()) {
        error_log("Count query execute error in user_balance_history.php: " . $countStmt->error);
        $totalRecords = 0;
        $totalPages = 0;
    } else {
        $countResult = $countStmt->get_result();
        $totalRecords = $countResult->fetch_assoc()['total'];
        $totalPages = ceil($totalRecords / $limit);
    }
    $countStmt->close();
}

// Get balance history records - SECURITY: Always filtered by user's own agent_id
$query = "
    SELECT 
        bh.id,
        bh.change_amount,
        bh.balance_after,
        (bh.balance_after - bh.change_amount) as balance_before,
        bh.reason,
        bh.ref_id,
        bh.beneficiary_number,
        bh.created_at
    FROM balance_history bh
    $whereClause
    ORDER BY bh.created_at DESC
    LIMIT ? OFFSET ?
";

$stmt = $conn->prepare($query);
if (!$stmt) {
    error_log("Main query prepare error in user_balance_history.php: " . $conn->error);
    $balanceHistory = [];
} else {
    $mainParams = $params;
    $mainParams[] = $limit;
    $mainParams[] = $offset;
    $mainParamTypes = $paramTypes . 'ii';
    $stmt->bind_param($mainParamTypes, ...$mainParams);
    if (!$stmt->execute()) {
        error_log("Main query execute error in user_balance_history.php: " . $stmt->error);
        $balanceHistory = [];
    } else {
        $result = $stmt->get_result();
        $balanceHistory = $result->fetch_all(MYSQLI_ASSOC);
    }
    $stmt->close();
}

// Get summary statistics - SECURITY: Always filtered by user's own agent_id
$summaryQuery = "
    SELECT 
        COUNT(*) as total_transactions,
        SUM(CASE WHEN change_amount > 0 THEN change_amount ELSE 0 END) as total_credits,
        SUM(CASE WHEN change_amount < 0 THEN ABS(change_amount) ELSE 0 END) as total_debits,
        MIN(created_at) as first_transaction,
        MAX(created_at) as last_transaction
    FROM balance_history bh
    $whereClause
";
$summaryStmt = $conn->prepare($summaryQuery);
if (!$summaryStmt) {
    error_log("Summary query prepare error in user_balance_history.php: " . $conn->error);
    $summary = [
        'total_transactions' => 0,
        'total_credits' => 0,
        'total_debits' => 0,
        'first_transaction' => null,
        'last_transaction' => null
    ];
} else {
    $summaryStmt->bind_param($paramTypes, ...$params);
    if (!$summaryStmt->execute()) {
        error_log("Summary query execute error in user_balance_history.php: " . $summaryStmt->error);
        $summary = [
            'total_transactions' => 0,
            'total_credits' => 0,
            'total_debits' => 0,
            'first_transaction' => null,
            'last_transaction' => null
        ];
    } else {
        $summaryResult = $summaryStmt->get_result();
        $summary = $summaryResult->fetch_assoc();
    }
    $summaryStmt->close();
}
?>

<!-- Balance History Section -->
<div class="glass-card rounded-2xl p-6 mb-6 shadow-lg border border-slate-600/50">
    <div class="flex items-center justify-between mb-6">
        <h2 class="text-xl font-bold text-white">
            <i class="fas fa-chart-line mr-2 text-amber-400"></i>My Balance History
        </h2>
        <a href="?page=dashboard" class="text-gray-400 hover:text-amber-400 text-sm transition-colors">
            <i class="fas fa-arrow-left mr-1"></i>Back to Dashboard
        </a>
    </div>

    <!-- Security Notice -->
    <div class="bg-blue-500/10 border border-blue-500/30 rounded-lg p-4 mb-6 backdrop-blur-sm">
        <div class="flex items-center">
            <i class="fas fa-shield-alt text-blue-400 mr-3"></i>
            <div>
                <h3 class="text-sm font-medium text-blue-400">Your Personal Balance History</h3>
                <p class="text-sm text-gray-400 mt-1">You are viewing only your own balance transactions. All data is private and secure.</p>
            </div>
        </div>
    </div>

    <!-- Summary Cards -->
    <div class="grid grid-cols-1 md:grid-cols-4 gap-4 mb-6">
        <div class="glass-card-light rounded-xl p-4 border border-slate-600/50">
            <div class="flex items-center">
                <div class="w-10 h-10 bg-green-500 rounded-full flex items-center justify-center mr-3 shadow-lg">
                    <i class="fas fa-wallet text-white"></i>
                </div>
                <div>
                    <p class="text-sm font-medium text-green-400">Current Balance</p>
                    <p class="text-lg font-bold text-white">GHS <?= number_format($balance, 2) ?></p>
                </div>
            </div>
        </div>
        
        <div class="glass-card-light rounded-xl p-4 border border-slate-600/50">
            <div class="flex items-center">
                <div class="w-10 h-10 bg-blue-500 rounded-full flex items-center justify-center mr-3 shadow-lg">
                    <i class="fas fa-plus text-white"></i>
                </div>
                <div>
                    <p class="text-sm font-medium text-blue-400">Total Credits</p>
                    <p class="text-lg font-bold text-white">GHS <?= number_format($summary['total_credits'] ?? 0, 2) ?></p>
                </div>
            </div>
        </div>
        
        <div class="glass-card-light rounded-xl p-4 border border-slate-600/50">
            <div class="flex items-center">
                <div class="w-10 h-10 bg-red-500 rounded-full flex items-center justify-center mr-3 shadow-lg">
                    <i class="fas fa-minus text-white"></i>
                </div>
                <div>
                    <p class="text-sm font-medium text-red-400">Total Debits</p>
                    <p class="text-lg font-bold text-white">GHS <?= number_format($summary['total_debits'] ?? 0, 2) ?></p>
                </div>
            </div>
        </div>
        
        <div class="glass-card-light rounded-xl p-4 border border-slate-600/50">
            <div class="flex items-center">
                <div class="w-10 h-10 bg-purple-500 rounded-full flex items-center justify-center mr-3 shadow-lg">
                    <i class="fas fa-list text-white"></i>
                </div>
                <div>
                    <p class="text-sm font-medium text-purple-400">Total Transactions</p>
                    <p class="text-lg font-bold text-white"><?= number_format($summary['total_transactions'] ?? 0) ?></p>
                </div>
            </div>
        </div>
    </div>

    <!-- Filters -->
    <div class="glass-card-light rounded-xl p-6 mb-6 border border-slate-600/50">
        <h3 class="text-lg font-semibold text-white mb-4">
            <i class="fas fa-filter mr-2 text-amber-400"></i>Filter Transactions
        </h3>
        <form method="GET" class="grid grid-cols-1 md:grid-cols-4 gap-4">
            <input type="hidden" name="page" value="balance_history">
            
            <div>
                <label class="block text-sm font-medium text-gray-300 mb-2">Start Date</label>
                <input type="date" name="start_date" value="<?= htmlspecialchars($filterStartDate) ?>" 
                       class="w-full px-3 py-2 border border-slate-600 rounded-lg bg-slate-800/50 text-white focus:ring-2 focus:ring-amber-500 focus:border-amber-500">
            </div>
            
            <div>
                <label class="block text-sm font-medium text-gray-300 mb-2">End Date</label>
                <input type="date" name="end_date" value="<?= htmlspecialchars($filterEndDate) ?>" 
                       class="w-full px-3 py-2 border border-slate-600 rounded-lg bg-slate-800/50 text-white focus:ring-2 focus:ring-amber-500 focus:border-amber-500">
            </div>
            
            <div>
                <label class="block text-sm font-medium text-gray-300 mb-2">Transaction Type</label>
                <select name="reason" class="w-full px-3 py-2 border border-slate-600 rounded-lg bg-slate-800/50 text-white focus:ring-2 focus:ring-amber-500 focus:border-amber-500">
                    <option value="">All Types</option>
                    <option value="order" <?= $filterReason === 'order' ? 'selected' : '' ?>>Orders</option>
                    <option value="payment" <?= $filterReason === 'payment' ? 'selected' : '' ?>>Payments</option>
                    <option value="manual_adjustment" <?= $filterReason === 'manual_adjustment' ? 'selected' : '' ?>>Manual Adjustments</option>
                </select>
            </div>
            
            <div class="flex items-end space-x-2">
                <button type="submit" class="flex-1 px-4 py-2 bg-gradient-to-r from-amber-500 to-yellow-600 text-white rounded-lg font-medium hover:from-amber-600 hover:to-yellow-700 transition-all hover-glow">
                    <i class="fas fa-search mr-2"></i>Filter
                </button>
                <a href="?page=balance_history" class="px-4 py-2 border border-slate-600 text-gray-300 rounded-lg font-medium hover:bg-slate-700/50 hover:text-white transition-colors">
                    <i class="fas fa-times"></i>
                </a>
            </div>
        </form>
    </div>

    <!-- Balance History Table -->
    <div class="glass-card-light rounded-xl shadow-lg border border-slate-600/50">
        <div class="p-6">
            <div class="flex items-center justify-between mb-4">
                <h3 class="text-lg font-bold text-white">Transaction History</h3>
                <span class="text-sm text-gray-400">
                    Showing <?= count($balanceHistory) ?> of <?= number_format($totalRecords) ?> transactions
                </span>
            </div>
            
            <?php if (empty($balanceHistory)): ?>
                <div class="text-center py-8">
                    <i class="fas fa-chart-line text-gray-600 text-4xl mb-4"></i>
                    <p class="text-gray-400">No transactions found</p>
                    <?php if ($filterStartDate || $filterEndDate || $filterReason): ?>
                        <p class="text-sm text-gray-500 mt-2">Try adjusting your filters</p>
                    <?php endif; ?>
                </div>
            <?php else: ?>
                <div class="overflow-x-auto">
                    <table class="w-full text-sm">
                        <thead>
                            <tr class="bg-slate-800/50 border-b border-slate-600/50">
                                <th class="text-left py-3 px-4 font-semibold text-gray-300">Date & Time</th>
                                <th class="text-left py-3 px-4 font-semibold text-gray-300">Type</th>
                                <th class="text-left py-3 px-4 font-semibold text-gray-300">Balance Before</th>
                                <th class="text-left py-3 px-4 font-semibold text-gray-300">Change Amount</th>
                                <th class="text-left py-3 px-4 font-semibold text-gray-300">Balance After</th>
                                <th class="text-left py-3 px-4 font-semibold text-gray-300">Details</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($balanceHistory as $transaction): ?>
                                <tr class="border-b border-slate-700/50 hover:bg-slate-700/30 transition-colors">
                                    <td class="py-3 px-4 text-white">
                                        <?= date('M d, Y', strtotime($transaction['created_at'])) ?><br>
                                        <span class="text-gray-400"><?= date('H:i:s', strtotime($transaction['created_at'])) ?></span>
                                    </td>
                                    <td class="py-3 px-4">
                                        <?php
                                        $reasonLabels = [
                                            'order' => 'Order',
                                            'payment' => 'Payment',
                                            'manual_adjustment' => 'Adjustment'
                                        ];
                                        $reasonLabel = $reasonLabels[$transaction['reason']] ?? ucfirst($transaction['reason']);
                                        ?>
                                        <span class="inline-flex items-center px-2.5 py-0.5 rounded-full text-xs font-medium
                                            <?php
                                            switch($transaction['reason']) {
                                                case 'order':
                                                    echo 'bg-red-500/20 text-red-400 border border-red-500/30';
                                                    break;
                                                case 'payment':
                                                    echo 'bg-green-500/20 text-green-400 border border-green-500/30';
                                                    break;
                                                case 'manual_adjustment':
                                                    echo 'bg-blue-500/20 text-blue-400 border border-blue-500/30';
                                                    break;
                                                default:
                                                    echo 'bg-gray-500/20 text-gray-400 border border-gray-500/30';
                                            }
                                            ?>">
                                            <?= $reasonLabel ?>
                                        </span>
                                    </td>
                                    <td class="py-3 px-4 text-white">
                                        GHS <?= number_format($transaction['balance_before'], 2) ?>
                                    </td>
                                    <td class="py-3 px-4 font-medium
                                        <?= $transaction['change_amount'] >= 0 ? 'text-green-400' : 'text-red-400' ?>">
                                        <?= $transaction['change_amount'] >= 0 ? '+' : '' ?>GHS <?= number_format($transaction['change_amount'], 2) ?>
                                    </td>
                                    <td class="py-3 px-4 text-white">
                                        GHS <?= number_format($transaction['balance_after'], 2) ?>
                                    </td>
                                    <td class="py-3 px-4 text-gray-400">
                                        <?php if ($transaction['beneficiary_number']): ?>
                                            <div class="flex items-center">
                                                <i class="fas fa-phone mr-1"></i>
                                                <?= htmlspecialchars($transaction['beneficiary_number']) ?>
                                            </div>
                                        <?php endif; ?>
                                        <?php if ($transaction['ref_id']): ?>
                                            <div class="text-xs text-gray-500 mt-1">
                                                Ref: <?= $transaction['ref_id'] ?>
                                            </div>
                                        <?php endif; ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                
                <!-- Pagination -->
                <?php if ($totalPages > 1): ?>
                    <div class="flex justify-center items-center gap-2 mt-6">
                        <?php if ($page > 1): ?>
                            <a href="?<?= http_build_query(array_merge($_GET, ['page' => $page - 1])) ?>" 
                               class="px-3 py-2 border border-slate-600 rounded-lg text-gray-300 hover:bg-slate-700/50 transition-colors">
                                <i class="fas fa-chevron-left"></i>
                            </a>
                        <?php endif; ?>
                        
                        <?php
                        $startPage = max(1, $page - 2);
                        $endPage = min($totalPages, $page + 2);
                        
                        for ($i = $startPage; $i <= $endPage; $i++):
                        ?>
                            <?php if ($i == $page): ?>
                                <span class="px-3 py-2 bg-gradient-to-r from-amber-500 to-yellow-600 text-white rounded-lg"><?= $i ?></span>
                            <?php else: ?>
                                <a href="?<?= http_build_query(array_merge($_GET, ['page' => $i])) ?>" 
                                   class="px-3 py-2 border border-slate-600 rounded-lg text-gray-300 hover:bg-slate-700/50 transition-colors"><?= $i ?></a>
                            <?php endif; ?>
                        <?php endfor; ?>
                        
                        <?php if ($page < $totalPages): ?>
                            <a href="?<?= http_build_query(array_merge($_GET, ['page' => $page + 1])) ?>" 
                               class="px-3 py-2 border border-slate-600 rounded-lg text-gray-300 hover:bg-slate-700/50 transition-colors">
                                <i class="fas fa-chevron-right"></i>
                            </a>
                        <?php endif; ?>
                    </div>
                <?php endif; ?>
            <?php endif; ?>
        </div>
    </div>
</div>

<!-- Export Options -->
<?php if (!empty($balanceHistory)): ?>
<div class="glass-card rounded-2xl p-6 shadow-lg border border-slate-600/50">
    <h3 class="text-lg font-bold text-white mb-4">
        <i class="fas fa-download mr-2 text-amber-400"></i>Export Data
    </h3>
    
    <div class="flex flex-wrap gap-3">
        <button onclick="exportBalanceHistoryToCSV()" class="bg-green-500 text-white px-4 py-2 rounded-lg font-medium hover:bg-green-600 transition-all hover-glow">
            <i class="fas fa-file-csv mr-2"></i>Export as CSV
        </button>
        <button onclick="printBalanceHistory()" class="bg-blue-500 text-white px-4 py-2 rounded-lg font-medium hover:bg-blue-600 transition-all hover-glow">
            <i class="fas fa-print mr-2"></i>Print History
        </button>
    </div>
</div>
<?php endif; ?>

<script>
    function exportBalanceHistoryToCSV() {
        const transactions = <?php echo json_encode($balanceHistory); ?>;
        const headers = ['Date', 'Time', 'Type', 'Balance Before', 'Change Amount', 'Balance After', 'Phone Number', 'Reference ID'];
        
        let csvContent = headers.join(',') + '\n';
        
        transactions.forEach(transaction => {
            const row = [
                new Date(transaction.created_at).toLocaleDateString(),
                new Date(transaction.created_at).toLocaleTimeString(),
                transaction.reason,
                transaction.balance_before,
                transaction.change_amount,
                transaction.balance_after,
                transaction.beneficiary_number || '',
                transaction.ref_id || ''
            ];
            csvContent += row.join(',') + '\n';
        });
        
        const blob = new Blob([csvContent], { type: 'text/csv' });
        const url = window.URL.createObjectURL(blob);
        const a = document.createElement('a');
        a.href = url;
        a.download = 'balance_history_<?php echo date('Y-m-d'); ?>.csv';
        a.click();
        window.URL.revokeObjectURL(url);
    }
    
    function printBalanceHistory() {
        window.print();
    }
</script>