230 lines
9.7 KiB
PHP
230 lines
9.7 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Services\Studio;
|
|
|
|
use App\Models\Artwork;
|
|
use App\Models\ArtworkStats;
|
|
use Illuminate\Support\Facades\Cache;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
/**
|
|
* Provides dashboard KPI data for the Studio overview page.
|
|
*/
|
|
final class StudioMetricsService
|
|
{
|
|
private const CACHE_TTL = 300; // 5 minutes
|
|
|
|
/**
|
|
* Get dashboard KPI metrics for a creator.
|
|
*
|
|
* @return array{total_artworks: int, views_30d: int, favourites_30d: int, shares_30d: int, followers: int}
|
|
*/
|
|
public function getDashboardKpis(int $userId): array
|
|
{
|
|
$cacheKey = "studio.kpi.{$userId}";
|
|
|
|
return Cache::remember($cacheKey, self::CACHE_TTL, function () use ($userId) {
|
|
$totalArtworks = Artwork::where('user_id', $userId)
|
|
->whereNull('deleted_at')
|
|
->count();
|
|
|
|
// Aggregate stats from artwork_stats for this user's artworks
|
|
$statsAgg = DB::table('artwork_stats')
|
|
->join('artworks', 'artworks.id', '=', 'artwork_stats.artwork_id')
|
|
->where('artworks.user_id', $userId)
|
|
->whereNull('artworks.deleted_at')
|
|
->selectRaw('
|
|
COALESCE(SUM(artwork_stats.views), 0) as total_views,
|
|
COALESCE(SUM(artwork_stats.favorites), 0) as total_favourites,
|
|
COALESCE(SUM(artwork_stats.shares_count), 0) as total_shares
|
|
')
|
|
->first();
|
|
|
|
// Views in last 30 days from hourly snapshots if available, fallback to totals
|
|
$views30d = 0;
|
|
try {
|
|
if (\Illuminate\Support\Facades\Schema::hasTable('artwork_metric_snapshots_hourly')) {
|
|
$views30d = (int) DB::table('artwork_metric_snapshots_hourly')
|
|
->join('artworks', 'artworks.id', '=', 'artwork_metric_snapshots_hourly.artwork_id')
|
|
->where('artworks.user_id', $userId)
|
|
->where('artwork_metric_snapshots_hourly.bucket_hour', '>=', now()->subDays(30))
|
|
->sum('artwork_metric_snapshots_hourly.views_count');
|
|
}
|
|
} catch (\Throwable $e) {
|
|
// Table or column doesn't exist — fall back to totals
|
|
}
|
|
|
|
if ($views30d === 0) {
|
|
$views30d = (int) ($statsAgg->total_views ?? 0);
|
|
}
|
|
|
|
$followers = DB::table('user_followers')
|
|
->where('user_id', $userId)
|
|
->count();
|
|
|
|
return [
|
|
'total_artworks' => $totalArtworks,
|
|
'views_30d' => $views30d,
|
|
'favourites_30d' => (int) ($statsAgg->total_favourites ?? 0),
|
|
'shares_30d' => (int) ($statsAgg->total_shares ?? 0),
|
|
'followers' => $followers,
|
|
];
|
|
});
|
|
}
|
|
|
|
/**
|
|
* Get top performing artworks for a creator in the last 7 days.
|
|
*
|
|
* @return \Illuminate\Support\Collection
|
|
*/
|
|
public function getTopPerformers(int $userId, int $limit = 6): \Illuminate\Support\Collection
|
|
{
|
|
$cacheKey = "studio.top_performers.{$userId}";
|
|
|
|
return Cache::remember($cacheKey, self::CACHE_TTL, function () use ($userId, $limit) {
|
|
return Artwork::where('user_id', $userId)
|
|
->whereNull('deleted_at')
|
|
->where('is_public', true)
|
|
->with(['stats', 'tags'])
|
|
->whereHas('stats')
|
|
->orderByDesc(
|
|
ArtworkStats::select('heat_score')
|
|
->whereColumn('artwork_stats.artwork_id', 'artworks.id')
|
|
->limit(1)
|
|
)
|
|
->limit($limit)
|
|
->get()
|
|
->map(fn (Artwork $art) => [
|
|
'id' => $art->id,
|
|
'title' => $art->title,
|
|
'slug' => $art->slug,
|
|
'thumb_url' => $art->thumbUrl('md'),
|
|
'favourites' => (int) ($art->stats?->favorites ?? 0),
|
|
'shares' => (int) ($art->stats?->shares_count ?? 0),
|
|
'heat_score' => (float) ($art->stats?->heat_score ?? 0),
|
|
'ranking_score' => (float) ($art->stats?->ranking_score ?? 0),
|
|
]);
|
|
});
|
|
}
|
|
|
|
/**
|
|
* Get recent comments on a creator's artworks.
|
|
*
|
|
* @return \Illuminate\Support\Collection
|
|
*/
|
|
public function getRecentComments(int $userId, int $limit = 5): \Illuminate\Support\Collection
|
|
{
|
|
return DB::table('artwork_comments')
|
|
->join('artworks', 'artworks.id', '=', 'artwork_comments.artwork_id')
|
|
->join('users', 'users.id', '=', 'artwork_comments.user_id')
|
|
->where('artworks.user_id', $userId)
|
|
->whereNull('artwork_comments.deleted_at')
|
|
->orderByDesc('artwork_comments.created_at')
|
|
->limit($limit)
|
|
->select([
|
|
'artwork_comments.id',
|
|
'artwork_comments.content as body',
|
|
'artwork_comments.created_at',
|
|
'users.name as author_name',
|
|
'users.username as author_username',
|
|
'artworks.title as artwork_title',
|
|
'artworks.slug as artwork_slug',
|
|
])
|
|
->get();
|
|
}
|
|
|
|
/**
|
|
* Aggregate analytics across all artworks for the Studio Analytics page.
|
|
*
|
|
* @return array{totals: array, top_artworks: array, content_breakdown: array}
|
|
*/
|
|
public function getAnalyticsOverview(int $userId): array
|
|
{
|
|
$cacheKey = "studio.analytics_overview.{$userId}";
|
|
|
|
return Cache::remember($cacheKey, self::CACHE_TTL, function () use ($userId) {
|
|
// Totals
|
|
$totals = DB::table('artwork_stats')
|
|
->join('artworks', 'artworks.id', '=', 'artwork_stats.artwork_id')
|
|
->where('artworks.user_id', $userId)
|
|
->whereNull('artworks.deleted_at')
|
|
->selectRaw('
|
|
COALESCE(SUM(artwork_stats.views), 0) as views,
|
|
COALESCE(SUM(artwork_stats.favorites), 0) as favourites,
|
|
COALESCE(SUM(artwork_stats.shares_count), 0) as shares,
|
|
COALESCE(SUM(artwork_stats.downloads), 0) as downloads,
|
|
COALESCE(SUM(artwork_stats.comments_count), 0) as comments,
|
|
COALESCE(AVG(artwork_stats.ranking_score), 0) as avg_ranking,
|
|
COALESCE(AVG(artwork_stats.heat_score), 0) as avg_heat
|
|
')
|
|
->first();
|
|
|
|
// Top 10 artworks by ranking score
|
|
$topArtworks = Artwork::where('user_id', $userId)
|
|
->whereNull('deleted_at')
|
|
->where('is_public', true)
|
|
->with(['stats'])
|
|
->whereHas('stats')
|
|
->orderByDesc(
|
|
ArtworkStats::select('ranking_score')
|
|
->whereColumn('artwork_stats.artwork_id', 'artworks.id')
|
|
->limit(1)
|
|
)
|
|
->limit(10)
|
|
->get()
|
|
->map(fn (Artwork $art) => [
|
|
'id' => $art->id,
|
|
'title' => $art->title,
|
|
'slug' => $art->slug,
|
|
'thumb_url' => $art->thumbUrl('sq'),
|
|
'views' => (int) ($art->stats?->views ?? 0),
|
|
'favourites' => (int) ($art->stats?->favorites ?? 0),
|
|
'shares' => (int) ($art->stats?->shares_count ?? 0),
|
|
'downloads' => (int) ($art->stats?->downloads ?? 0),
|
|
'comments' => (int) ($art->stats?->comments_count ?? 0),
|
|
'ranking_score' => (float) ($art->stats?->ranking_score ?? 0),
|
|
'heat_score' => (float) ($art->stats?->heat_score ?? 0),
|
|
]);
|
|
|
|
// Content type breakdown
|
|
$contentBreakdown = DB::table('artworks')
|
|
->join('artwork_category', 'artwork_category.artwork_id', '=', 'artworks.id')
|
|
->join('categories', 'categories.id', '=', 'artwork_category.category_id')
|
|
->join('content_types', 'content_types.id', '=', 'categories.content_type_id')
|
|
->where('artworks.user_id', $userId)
|
|
->whereNull('artworks.deleted_at')
|
|
->groupBy('content_types.id', 'content_types.name', 'content_types.slug')
|
|
->select([
|
|
'content_types.name',
|
|
'content_types.slug',
|
|
DB::raw('COUNT(DISTINCT artworks.id) as count'),
|
|
])
|
|
->orderByDesc('count')
|
|
->get()
|
|
->map(fn ($row) => [
|
|
'name' => $row->name,
|
|
'slug' => $row->slug,
|
|
'count' => (int) $row->count,
|
|
])
|
|
->values()
|
|
->all();
|
|
|
|
return [
|
|
'totals' => [
|
|
'views' => (int) ($totals->views ?? 0),
|
|
'favourites' => (int) ($totals->favourites ?? 0),
|
|
'shares' => (int) ($totals->shares ?? 0),
|
|
'downloads' => (int) ($totals->downloads ?? 0),
|
|
'comments' => (int) ($totals->comments ?? 0),
|
|
'avg_ranking' => round((float) ($totals->avg_ranking ?? 0), 1),
|
|
'avg_heat' => round((float) ($totals->avg_heat ?? 0), 1),
|
|
],
|
|
'top_artworks' => $topArtworks->values()->all(),
|
|
'content_breakdown' => $contentBreakdown,
|
|
];
|
|
});
|
|
}
|
|
}
|