<?php
require_once 'config.php';
require_once 'includes/db.php';
require_once 'includes/functions.php';
require_login();

$user_id = $_SESSION['user_id'];

try {
    $site_settings = get_site_settings($pdo);
    $system_comm = $site_settings['system_commission'] ?? 0;

    // 1. Available Balance calculation (Earned - Withdrawn)
    $stmt = $pdo->prepare("SELECT SUM(t.amount) as gross_earned FROM transactions t JOIN videos v ON t.video_id = v.id WHERE v.user_id = ? AND t.status = 'completed'");
    $stmt->execute([$user_id]);
    $gross_earned = $stmt->fetch()['gross_earned'] ?? 0;
    
    $total_earned = $gross_earned * (1 - ($system_comm / 100));

    $stmt = $pdo->prepare("SELECT SUM(amount) as withdrawn FROM withdrawals WHERE user_id = ? AND status IN ('completed', 'pending')");
    $stmt->execute([$user_id]);
    $total_withdrawn = $stmt->fetch()['withdrawn'] ?? 0;

    $available_balance = $total_earned - $total_withdrawn;

    // 2. Completed Withdrawals
    $stmt = $pdo->prepare("SELECT SUM(amount) as paid FROM withdrawals WHERE user_id = ? AND status = 'completed'");
    $stmt->execute([$user_id]);
    $completed_withdrawals = $stmt->fetch()['paid'] ?? 0;

    // 3. Pending Withdrawals
    $stmt = $pdo->prepare("SELECT SUM(amount) as pending FROM withdrawals WHERE user_id = ? AND status = 'pending'");
    $stmt->execute([$user_id]);
    $pending_withdrawals = $stmt->fetch()['pending'] ?? 0;

    // Pagination & Search & Sort logic
    $page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
    $limit = 10;
    $offset = ($page - 1) * $limit;

    $search = isset($_GET['search']) ? trim($_GET['search']) : '';
    $sort = isset($_GET['sort']) ? $_GET['sort'] : 'created_at';
    $dir = isset($_GET['dir']) && strtolower($_GET['dir']) === 'asc' ? 'ASC' : 'DESC';

    $allowed_sorts = ['created_at', 'amount', 'video_title', 'phone_number', 'status'];
    if (!in_array($sort, $allowed_sorts)) $sort = 'created_at';

    $where_sql = "v.user_id = ?";
    $params = [$user_id];

    if ($search !== '') {
        $where_sql .= " AND (v.title LIKE ? OR t.phone_number LIKE ? OR t.status = ?)";
        $params[] = "%$search%";
        $params[] = "%$search%";
        $params[] = $search;
    }

    $stmt = $pdo->prepare("SELECT COUNT(*) FROM transactions t JOIN videos v ON t.video_id = v.id WHERE $where_sql");
    $stmt->execute($params);
    $total_records = $stmt->fetchColumn();
    $total_pages = ceil($total_records / $limit);

    $sort_column = $sort === 'video_title' ? 'v.title' : "t.$sort";
    $stmt = $pdo->prepare("
        SELECT t.*, v.title as video_title 
        FROM transactions t 
        JOIN videos v ON t.video_id = v.id 
        WHERE $where_sql 
        ORDER BY $sort_column $dir 
        LIMIT $limit OFFSET $offset
    ");
    $stmt->execute($params);
    $recent_transactions = $stmt->fetchAll();

} catch (PDOException $e) {
    $available_balance = 0;
    $completed_withdrawals = 0;
    $pending_withdrawals = 0;
    $recent_transactions = [];
}

$pageTitle = "Statistics - SonicPesa";
?>
<!DOCTYPE html>
<html lang="en" data-theme="dark">
<?php include 'includes/head.php'; ?>
<body>
    <?php include 'includes/sidebar.php'; ?>
    <main class="dashboard-main">
        <?php include 'includes/header.php'; ?>
        
        <div class="dashboard-main-body">
            <div class="d-flex flex-wrap align-items-center justify-content-between gap-3 mb-24">
                <h6 class="fw-semibold mb-0">Statistics</h6>
                <ul class="d-flex align-items-center gap-2">
                    <li class="fw-medium"><a href="<?= SITE_URL ?>/dashboard" class="hover-text-primary text-secondary-light">Dashboard</a></li>
                    <li>-</li>
                    <li class="fw-medium text-primary-600">Statistics</li>
                </ul>
            </div>

            <!-- Balance Cards (Original Style) -->
            <div class="row gy-4 mb-24">
                <div class="col-xxl-4 col-sm-6">
                    <div class="card p-12 radius-0 border-0" style="background: #D9F7EF; color: #111;">
                        <div class="d-flex align-items-center gap-16">
                            <div class="w-50-px h-50-px radius-8 d-flex justify-content-center align-items-center bg-white shadow-sm">
                                <iconify-icon icon="solar:wallet-bold-duotone" class="text-2xl text-success-main"></iconify-icon>
                            </div>
                            <div>
                                <span class="text-secondary fw-medium text-xs text-uppercase">Available balance</span>
                                <h5 class="text-dark fw-bold mb-0">TSH <?= number_format($available_balance, 2) ?></h5>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="col-xxl-4 col-sm-6">
                    <div class="card p-12 radius-0 border-0" style="background: #e0e7ff; color: #111;">
                        <div class="d-flex align-items-center gap-16">
                            <div class="w-50-px h-50-px radius-8 d-flex justify-content-center align-items-center bg-white shadow-sm">
                                <iconify-icon icon="solar:check-circle-bold-duotone" class="text-2xl text-primary-main"></iconify-icon>
                            </div>
                            <div>
                                <span class="text-secondary fw-medium text-xs text-uppercase">Completed Withdrawals</span>
                                <h5 class="text-dark fw-bold mb-0">TSH <?= number_format($completed_withdrawals, 2) ?></h5>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="col-xxl-4 col-sm-6">
                    <div class="card p-12 radius-0 border-0" style="background: #fef3c7; color: #111;">
                        <div class="d-flex align-items-center gap-16">
                            <div class="w-50-px h-50-px radius-8 d-flex justify-content-center align-items-center bg-white shadow-sm">
                                <iconify-icon icon="solar:clock-circle-bold-duotone" class="text-2xl text-warning-main"></iconify-icon>
                            </div>
                            <div>
                                <span class="text-secondary fw-medium text-xs text-uppercase">Pending Withdrawals</span>
                                <h5 class="text-dark fw-bold mb-0">TSH <?= number_format($pending_withdrawals, 2) ?></h5>
                            </div>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Recent Transaction History -->
            <div class="card radius-8 border-0 shadow-sm">
                <div class="card-header border-bottom bg-base d-flex flex-wrap align-items-center justify-content-between gap-3">
                    <h6 class="mb-0 fw-bold">Transaction History</h6>
                    <form method="GET" class="d-flex gap-2 align-items-center">
                        <input type="text" name="search" class="form-control form-control-sm radius-8" placeholder="Search title, phone, status..." value="<?= htmlspecialchars($search ?? '') ?>" style="max-width: 250px;">
                        <input type="hidden" name="sort" value="<?= htmlspecialchars($sort ?? 'created_at') ?>">
                        <input type="hidden" name="dir" value="<?= htmlspecialchars($dir ?? 'DESC') ?>">
                        <button type="submit" class="btn btn-primary-600 btn-sm radius-8 px-16"><i class="ri-search-line"></i> Search</button>
                    </form>
                </div>
                <div class="card-body p-0">
                    <div class="table-responsive">
                        <table class="table mb-0 table-hover">
                            <thead class="bg-base">
                                <?php
                                    $toggle_dir = (isset($dir) && $dir === 'ASC') ? 'desc' : 'asc';
                                    if(!function_exists('getSortLink')) {
                                        function getSortLink($col, $current_sort, $toggle_dir, $search) {
                                            return "?sort=$col&dir=$toggle_dir&search=".urlencode($search ?? '');
                                        }
                                    }
                                    function getIcon($col, $current_sort, $dir) {
                                        if ($current_sort !== $col) return '';
                                        return $dir === 'ASC' ? '<i class="ri-arrow-up-line"></i>' : '<i class="ri-arrow-down-line"></i>';
                                    }
                                ?>
                                <tr>
                                    <th class="ps-24 py-16 text-xs text-uppercase fw-bold text-secondary-light"><a href="<?= getSortLink('created_at', $sort ?? 'created_at', $toggle_dir, $search ?? '') ?>" class="text-secondary-light d-flex align-items-center gap-1">Date <?= getIcon('created_at', $sort ?? 'created_at', $dir ?? 'DESC') ?></a></th>
                                    <th class="py-16 text-xs text-uppercase fw-bold text-secondary-light"><a href="<?= getSortLink('video_title', $sort ?? '', $toggle_dir, $search ?? '') ?>" class="text-secondary-light d-flex align-items-center gap-1">Video <?= getIcon('video_title', $sort ?? '', $dir ?? 'DESC') ?></a></th>
                                    <th class="py-16 text-xs text-uppercase fw-bold text-secondary-light"><a href="<?= getSortLink('amount', $sort ?? '', $toggle_dir, $search ?? '') ?>" class="text-secondary-light d-flex align-items-center gap-1">Amount <?= getIcon('amount', $sort ?? '', $dir ?? 'DESC') ?></a></th>
                                    <th class="py-16 text-xs text-uppercase fw-bold text-secondary-light"><a href="<?= getSortLink('phone_number', $sort ?? '', $toggle_dir, $search ?? '') ?>" class="text-secondary-light d-flex align-items-center gap-1">Number <?= getIcon('phone_number', $sort ?? '', $dir ?? 'DESC') ?></a></th>
                                    <th class="pe-24 py-16 text-end text-xs text-uppercase fw-bold text-secondary-light justify-content-end"><a href="<?= getSortLink('status', $sort ?? '', $toggle_dir, $search ?? '') ?>" class="text-secondary-light d-flex align-items-center justify-content-end gap-1">Status <?= getIcon('status', $sort ?? '', $dir ?? 'DESC') ?></a></th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php if(!empty($recent_transactions)): ?>
                                    <?php foreach($recent_transactions as $t): ?>
                                    <tr>
                                        <td class="ps-24 py-16 text-sm text-secondary-light"><?= date('M d, Y H:i', strtotime($t['created_at'])) ?></td>
                                        <td class="py-16 fw-medium text-dark"><?= htmlspecialchars($t['video_title']) ?></td>
                                        <?php $net_amount = $t['amount'] * (1 - ($system_comm / 100)); ?>
                                        <td class="py-16 fw-bold">TSH <?= number_format($net_amount) ?></td>
                                        <td class="py-16 text-sm"><?= htmlspecialchars($t['phone_number'] ?? 'N/A') ?></td>
                                        <td class="pe-24 py-16 text-end">
                                            <?php if($t['status'] === 'completed'): ?>
                                                <span class="badge bg-success-focus text-success-main radius-4">Completed</span>
                                            <?php elseif(in_array($t['status'], ['failed', 'cancelled', 'expired'])): ?>
                                                <span class="badge bg-danger-focus text-danger-main radius-4"><?= ucfirst($t['status']) ?></span>
                                            <?php else: ?>
                                                <span class="badge bg-warning-focus text-warning-main radius-4">Pending</span>
                                            <?php endif; ?>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                <?php else: ?>
                                    <tr><td colspan="5" class="py-24 text-center text-secondary-light">No transactions found.</td></tr>
                                <?php endif; ?>
                            </tbody>
                        </table>
                    </div>
                    <?php if(isset($total_pages) && $total_pages > 1): ?>
                    <div class="d-flex justify-content-between align-items-center p-24 border-top">
                        <span class="text-sm text-secondary-light">Showing <?= $offset + 1 ?> to <?= min($offset + $limit, $total_records) ?> of <?= $total_records ?> entries</span>
                        <ul class="pagination mb-0">
                            <?php if($page > 1): ?>
                                <li class="page-item"><a class="page-link" href="?page=<?= $page-1 ?>&search=<?= urlencode($search) ?>&sort=<?= $sort ?>&dir=<?= $dir ?>">Previous</a></li>
                            <?php endif; ?>
                            
                            <?php for($i = max(1, $page - 2); $i <= min($total_pages, $page + 2); $i++): ?>
                                <li class="page-item <?= $i == $page ? 'active' : '' ?>"><a class="page-link" href="?page=<?= $i ?>&search=<?= urlencode($search) ?>&sort=<?= $sort ?>&dir=<?= $dir ?>"><?= $i ?></a></li>
                            <?php endfor; ?>

                            <?php if($page < $total_pages): ?>
                                <li class="page-item"><a class="page-link" href="?page=<?= $page+1 ?>&search=<?= urlencode($search) ?>&sort=<?= $sort ?>&dir=<?= $dir ?>">Next</a></li>
                            <?php endif; ?>
                        </ul>
                    </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>
        
        <?php include 'includes/footer.php'; ?>
    </main>
</body>
</html>


