platform.php.bak

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

$platform = $_GET['platform'] ?? '';
if (empty($platform)) {
    header('Location: index.php');
    exit;
}

$sortBy = $_GET['sort'] ?? 'popular';
$category = $_GET['category'] ?? 'all';

// Build query conditions
$where = ["status = 'active' AND web_platform = ?"];
$params = [$platform];

if ($category !== 'all') {
    $where[] = "category = ?";
    $params[] = $category;
}

$whereClause = implode(' AND ', $where);

// Sorting
$orderBy = match($sortBy) {
    'downloads' => 'downloads_count DESC',
    'rating' => 'total_rating DESC',
    'newest' => 'submitted_at DESC',
    'oldest' => 'submitted_at ASC',
    default => 'views_count DESC' // popular
};

// Get platform statistics
$statsStmt = $pdo->prepare("
    SELECT 
        COUNT(DISTINCT id) as total_apps,
        SUM(downloads_count) as total_downloads,
        SUM(views_count) as total_views,
        AVG(total_rating) as avg_rating,
        COUNT(DISTINCT developer_name) as total_developers
    FROM apps 
    WHERE status = 'active' 
    AND web_platform = ?
");
$statsStmt->execute([$platform]);
$stats = $statsStmt->fetch();

// Get top developers for this platform
$topDevsStmt = $pdo->prepare("
    SELECT 
        developer_name,
        COUNT(*) as app_count,
        SUM(downloads_count) as total_downloads,
        AVG(total_rating) as avg_rating
    FROM apps
    WHERE status = 'active' 
    AND web_platform = ?
    GROUP BY developer_name
    ORDER BY total_downloads DESC
    LIMIT 5
");
$topDevsStmt->execute([$platform]);
$topDevelopers = $topDevsStmt->fetchAll();

// Get categories for this platform
$categoriesStmt = $pdo->prepare("
    SELECT category, COUNT(*) as app_count
    FROM apps
    WHERE status = 'active' 
    AND web_platform = ?
    AND category IS NOT NULL
    GROUP BY category
    ORDER BY app_count DESC
");
$categoriesStmt->execute([$platform]);
$categories = $categoriesStmt->fetchAll();

// Pagination for apps
$page = max(1, $_GET['page'] ?? 1);
$perPage = 12;
$offset = ($page - 1) * $perPage;

// Get total count
$countStmt = $pdo->prepare("SELECT COUNT(*) FROM apps WHERE $whereClause");
$countStmt->execute($params);
$totalApps = $countStmt->fetchColumn();
$totalPages = ceil($totalApps / $perPage);

// Get apps
$query = "
    SELECT a.*, 
           COUNT(DISTINCT ar.id) as review_count,
           AVG(ar.rating) as avg_rating
    FROM apps a
    LEFT JOIN app_ratings ar ON a.id = ar.app_id
    WHERE $whereClause
    GROUP BY a.id
    ORDER BY $orderBy 
    LIMIT ? OFFSET ?
";

$params[] = $perPage;
$params[] = $offset;

$stmt = $pdo->prepare($query);