<?php
$file = 'c:\xampp\htdocs\SonicPesaw\statistics.php';
$content = file_get_contents($file);

// Replace query logic
$old_query = <<<'PHP'
    // 4. Transaction History (Recent Sales)
    $stmt = $pdo->prepare("
        SELECT t.*, v.title as video_title 
        FROM transactions t 
        JOIN videos v ON t.video_id = v.id 
        WHERE v.user_id = ? 
        ORDER BY t.created_at DESC 
        LIMIT 10
    ");
    $stmt->execute([$user_id]);
    $recent_transactions = $stmt->fetchAll();
PHP;

$new_query = <<<'PHP'
    // 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();
PHP;

$content = str_replace($old_query, $new_query, $content);

// Replace Header and Search Form
$old_header = <<<'HTML'
            <div class="card radius-8 border-0 shadow-sm">
                <div class="card-header border-bottom bg-base">
                    <h6 class="mb-0 fw-bold">Recent Transaction History</h6>
                </div>
HTML;

$new_header = <<<'HTML'
            <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>
HTML;

$content = str_replace($old_header, $new_header, $content);

// Replace Table Head
$old_thead = <<<'HTML'
                            <thead class="bg-base">
                                <tr>
                                    <th class="ps-24 py-16 text-xs text-uppercase fw-bold text-secondary-light">Date</th>
                                    <th class="py-16 text-xs text-uppercase fw-bold text-secondary-light">Video</th>
                                    <th class="py-16 text-xs text-uppercase fw-bold text-secondary-light">Amount</th>
                                    <th class="py-16 text-xs text-uppercase fw-bold text-secondary-light">Number</th>
                                    <th class="pe-24 py-16 text-end text-xs text-uppercase fw-bold text-secondary-light">Status</th>
                                </tr>
                            </thead>
HTML;

$new_thead = <<<'HTML'
                            <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>
HTML;

$content = str_replace($old_thead, $new_thead, $content);

// Add Pagination Bottom
$old_table_end = <<<'HTML'
                        </table>
                    </div>
                </div>
            </div>
HTML;

$new_table_end = <<<'HTML'
                        </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>
HTML;

$content = str_replace($old_table_end, $new_table_end, $content);

file_put_contents($file, $content);
echo "SUCCESS";

