mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 18:11:12 -05:00
1074 lines
36 KiB
TypeScript
1074 lines
36 KiB
TypeScript
import { supabase } from '@/lib/supabaseClient';
|
|
import { getErrorMessage } from '@/lib/errorHandler';
|
|
|
|
export type ActivityType =
|
|
| 'entity_change'
|
|
| 'admin_action'
|
|
| 'submission_review'
|
|
| 'report_resolution'
|
|
| 'review_moderation'
|
|
| 'photo_approval'
|
|
| 'account_created'
|
|
| 'account_deletion_requested'
|
|
| 'account_deletion_confirmed'
|
|
| 'account_deletion_cancelled'
|
|
| 'user_banned'
|
|
| 'user_unbanned'
|
|
| 'review_created'
|
|
| 'review_deleted'
|
|
| 'submission_created'
|
|
| 'submission_claimed'
|
|
| 'submission_escalated'
|
|
| 'submission_reassigned';
|
|
|
|
export interface ActivityActor {
|
|
id: string;
|
|
username: string;
|
|
display_name?: string;
|
|
avatar_url?: string;
|
|
}
|
|
|
|
export interface EntityChangeDetails {
|
|
entity_type: string;
|
|
entity_id: string;
|
|
entity_name?: string;
|
|
change_type: string;
|
|
change_reason?: string;
|
|
version_number?: number;
|
|
}
|
|
|
|
export interface AdminActionDetails {
|
|
action: string;
|
|
target_user_id?: string;
|
|
target_username?: string;
|
|
details?: Record<string, any>;
|
|
/** Relational audit details from admin_audit_details table */
|
|
admin_audit_details?: Array<{
|
|
id: string;
|
|
detail_key: string;
|
|
detail_value: string;
|
|
}>;
|
|
}
|
|
|
|
export interface SubmissionReviewDetails {
|
|
submission_id: string;
|
|
submission_type: string;
|
|
status: string;
|
|
entity_name?: string;
|
|
// Photo-specific fields
|
|
photo_url?: string;
|
|
photo_caption?: string;
|
|
photo_title?: string;
|
|
entity_type?: string;
|
|
entity_id?: string;
|
|
deletion_reason?: string;
|
|
}
|
|
|
|
export interface ReportResolutionDetails {
|
|
report_id: string;
|
|
reported_entity_type: string;
|
|
reported_entity_id: string;
|
|
status: string;
|
|
resolution_notes?: string;
|
|
}
|
|
|
|
export interface ReviewModerationDetails {
|
|
review_id: string;
|
|
entity_type: string;
|
|
entity_id: string;
|
|
moderation_status: string;
|
|
entity_name?: string;
|
|
}
|
|
|
|
export interface PhotoApprovalDetails {
|
|
photo_id: string;
|
|
entity_type: string;
|
|
entity_id: string;
|
|
entity_name?: string;
|
|
}
|
|
|
|
export interface AccountLifecycleDetails {
|
|
user_id: string;
|
|
username?: string;
|
|
action: 'created' | 'deletion_requested' | 'deletion_confirmed' | 'deletion_cancelled' | 'banned' | 'unbanned';
|
|
reason?: string;
|
|
scheduled_date?: string;
|
|
request_id?: string;
|
|
}
|
|
|
|
export interface ReviewLifecycleDetails {
|
|
review_id: string;
|
|
user_id: string;
|
|
username?: string;
|
|
entity_type: 'park' | 'ride';
|
|
entity_id: string;
|
|
entity_name?: string;
|
|
rating?: number;
|
|
content?: string;
|
|
deletion_reason?: string;
|
|
was_moderated?: boolean;
|
|
}
|
|
|
|
export interface SubmissionWorkflowDetails {
|
|
submission_id: string;
|
|
submission_type: string;
|
|
user_id?: string;
|
|
username?: string;
|
|
assigned_to?: string;
|
|
assigned_username?: string;
|
|
escalation_reason?: string;
|
|
from_moderator?: string;
|
|
from_moderator_username?: string;
|
|
to_moderator?: string;
|
|
to_moderator_username?: string;
|
|
}
|
|
|
|
export type ActivityDetails =
|
|
| EntityChangeDetails
|
|
| AdminActionDetails
|
|
| SubmissionReviewDetails
|
|
| ReportResolutionDetails
|
|
| ReviewModerationDetails
|
|
| PhotoApprovalDetails
|
|
| AccountLifecycleDetails
|
|
| ReviewLifecycleDetails
|
|
| SubmissionWorkflowDetails;
|
|
|
|
export interface SystemActivity {
|
|
id: string;
|
|
type: ActivityType;
|
|
timestamp: string;
|
|
actor_id: string | null;
|
|
actor?: ActivityActor;
|
|
action: string;
|
|
details: ActivityDetails;
|
|
}
|
|
|
|
export interface ActivityFilters {
|
|
type?: ActivityType;
|
|
userId?: string;
|
|
entityType?: string;
|
|
dateFrom?: string;
|
|
dateTo?: string;
|
|
}
|
|
|
|
/**
|
|
* Type-safe interface for version data from relational version tables
|
|
*/
|
|
interface VersionData {
|
|
version_id: string;
|
|
version_number: number;
|
|
name: string;
|
|
created_by: string | null;
|
|
created_at: string;
|
|
change_type: string;
|
|
change_reason: string | null;
|
|
is_current: boolean;
|
|
}
|
|
|
|
interface ParkVersionData extends VersionData {
|
|
park_id: string;
|
|
}
|
|
|
|
interface RideVersionData extends VersionData {
|
|
ride_id: string;
|
|
}
|
|
|
|
interface CompanyVersionData extends VersionData {
|
|
company_id: string;
|
|
}
|
|
|
|
interface RideModelVersionData extends VersionData {
|
|
ride_model_id: string;
|
|
}
|
|
|
|
/**
|
|
* Type-safe interface for submission item data
|
|
*/
|
|
interface SubmissionItemData {
|
|
cloudflare_image_url?: string;
|
|
caption?: string;
|
|
title?: string;
|
|
entity_type?: string;
|
|
entity_id?: string;
|
|
reason?: string;
|
|
}
|
|
|
|
/**
|
|
* Type-safe interface for submission content
|
|
*/
|
|
interface SubmissionContent {
|
|
action?: string;
|
|
name?: string;
|
|
}
|
|
|
|
/**
|
|
* Fetch unified system activity log from multiple sources
|
|
*/
|
|
export async function fetchSystemActivities(
|
|
limit: number = 50,
|
|
filters?: ActivityFilters
|
|
): Promise<SystemActivity[]> {
|
|
const activities: SystemActivity[] = [];
|
|
|
|
// Fetch entity versions from relational version tables
|
|
// Query all four version tables in parallel for better performance
|
|
// Fetch ALL versions (not just current) to show complete history
|
|
const versionQueries = [
|
|
supabase
|
|
.from('park_versions')
|
|
.select('version_id, park_id, version_number, name, created_by, created_at, change_type, change_reason, is_current')
|
|
.order('created_at', { ascending: false })
|
|
.limit(limit),
|
|
supabase
|
|
.from('ride_versions')
|
|
.select('version_id, ride_id, version_number, name, created_by, created_at, change_type, change_reason, is_current')
|
|
.order('created_at', { ascending: false })
|
|
.limit(limit),
|
|
supabase
|
|
.from('company_versions')
|
|
.select('version_id, company_id, version_number, name, created_by, created_at, change_type, change_reason, is_current')
|
|
.order('created_at', { ascending: false })
|
|
.limit(Math.ceil(limit / 2)),
|
|
supabase
|
|
.from('ride_model_versions')
|
|
.select('version_id, ride_model_id, version_number, name, created_by, created_at, change_type, change_reason, is_current')
|
|
.order('created_at', { ascending: false })
|
|
.limit(Math.ceil(limit / 2)),
|
|
];
|
|
|
|
const [parkVersions, rideVersions, companyVersions, modelVersions] = await Promise.all(versionQueries);
|
|
|
|
// Process park versions
|
|
if (!parkVersions.error && parkVersions.data) {
|
|
for (const version of parkVersions.data) {
|
|
const parkVersion = version as ParkVersionData;
|
|
activities.push({
|
|
id: parkVersion.version_id,
|
|
type: 'entity_change',
|
|
timestamp: parkVersion.created_at,
|
|
actor_id: parkVersion.created_by || null,
|
|
action: `${parkVersion.change_type} park`,
|
|
details: {
|
|
entity_type: 'park',
|
|
entity_id: parkVersion.park_id,
|
|
entity_name: parkVersion.name,
|
|
change_type: parkVersion.change_type,
|
|
change_reason: parkVersion.change_reason,
|
|
version_number: parkVersion.version_number,
|
|
} as EntityChangeDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Process ride versions
|
|
if (!rideVersions.error && rideVersions.data) {
|
|
for (const version of rideVersions.data) {
|
|
const rideVersion = version as RideVersionData;
|
|
activities.push({
|
|
id: rideVersion.version_id,
|
|
type: 'entity_change',
|
|
timestamp: rideVersion.created_at,
|
|
actor_id: rideVersion.created_by || null,
|
|
action: `${rideVersion.change_type} ride`,
|
|
details: {
|
|
entity_type: 'ride',
|
|
entity_id: rideVersion.ride_id,
|
|
entity_name: rideVersion.name,
|
|
change_type: rideVersion.change_type,
|
|
change_reason: rideVersion.change_reason,
|
|
version_number: rideVersion.version_number,
|
|
} as EntityChangeDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Process company versions
|
|
if (!companyVersions.error && companyVersions.data) {
|
|
for (const version of companyVersions.data) {
|
|
const companyVersion = version as CompanyVersionData;
|
|
activities.push({
|
|
id: companyVersion.version_id,
|
|
type: 'entity_change',
|
|
timestamp: companyVersion.created_at,
|
|
actor_id: companyVersion.created_by || null,
|
|
action: `${companyVersion.change_type} company`,
|
|
details: {
|
|
entity_type: 'company',
|
|
entity_id: companyVersion.company_id,
|
|
entity_name: companyVersion.name,
|
|
change_type: companyVersion.change_type,
|
|
change_reason: companyVersion.change_reason,
|
|
version_number: companyVersion.version_number,
|
|
} as EntityChangeDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Process ride model versions
|
|
if (!modelVersions.error && modelVersions.data) {
|
|
for (const version of modelVersions.data) {
|
|
const modelVersion = version as RideModelVersionData;
|
|
activities.push({
|
|
id: modelVersion.version_id,
|
|
type: 'entity_change',
|
|
timestamp: modelVersion.created_at,
|
|
actor_id: modelVersion.created_by || null,
|
|
action: `${modelVersion.change_type} ride_model`,
|
|
details: {
|
|
entity_type: 'ride_model',
|
|
entity_id: modelVersion.ride_model_id,
|
|
entity_name: modelVersion.name,
|
|
change_type: modelVersion.change_type,
|
|
change_reason: modelVersion.change_reason,
|
|
version_number: modelVersion.version_number,
|
|
} as EntityChangeDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Fetch admin audit log (admin actions)
|
|
// Note: Details are now in admin_audit_details table
|
|
const { data: auditLogs, error: auditError } = await supabase
|
|
.from('admin_audit_log')
|
|
.select(`
|
|
id,
|
|
admin_user_id,
|
|
target_user_id,
|
|
action,
|
|
created_at,
|
|
admin_audit_details(detail_key, detail_value)
|
|
`)
|
|
.order('created_at', { ascending: false })
|
|
.limit(limit);
|
|
|
|
if (!auditError && auditLogs) {
|
|
for (const log of auditLogs) {
|
|
// Convert relational details back to object format
|
|
const details: Record<string, any> = {};
|
|
if (log.admin_audit_details) {
|
|
for (const detail of log.admin_audit_details as any[]) {
|
|
details[detail.detail_key] = detail.detail_value;
|
|
}
|
|
}
|
|
|
|
activities.push({
|
|
id: log.id,
|
|
type: 'admin_action',
|
|
timestamp: log.created_at,
|
|
actor_id: log.admin_user_id,
|
|
action: log.action,
|
|
details: {
|
|
action: log.action,
|
|
target_user_id: log.target_user_id,
|
|
details,
|
|
} as AdminActionDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Fetch submission reviews (approved/rejected submissions)
|
|
// Note: Content is now in submission_metadata table, but entity_name is cached in view
|
|
const { data: submissions, error: submissionsError } = await supabase
|
|
.from('content_submissions')
|
|
.select(`
|
|
id,
|
|
submission_type,
|
|
status,
|
|
reviewer_id,
|
|
reviewed_at,
|
|
submission_metadata(name)
|
|
`)
|
|
.not('reviewed_at', 'is', null)
|
|
.in('status', ['approved', 'rejected', 'partially_approved'])
|
|
.order('reviewed_at', { ascending: false })
|
|
.limit(limit);
|
|
|
|
if (!submissionsError && submissions) {
|
|
// Fetch submission_items for photo submissions to get detailed info
|
|
const submissionIds = submissions.map(s => s.id);
|
|
const { data: submissionItems } = await supabase
|
|
.from('submission_items')
|
|
.select(`
|
|
submission_id,
|
|
item_type,
|
|
photo_submission:photo_submissions!submission_items_photo_submission_id_fkey(
|
|
*,
|
|
photo_items:photo_submission_items(*)
|
|
)
|
|
`)
|
|
.in('submission_id', submissionIds)
|
|
.in('item_type', ['photo', 'photo_delete', 'photo_edit']);
|
|
|
|
const itemsMap = new Map(
|
|
submissionItems?.map(item => {
|
|
// Transform photo data
|
|
const itemData = item.item_type === 'photo'
|
|
? {
|
|
...(item as any).photo_submission,
|
|
photos: (item as any).photo_submission?.photo_items || []
|
|
}
|
|
: (item as any).photo_submission;
|
|
|
|
return [item.submission_id, { ...item, item_data: itemData }];
|
|
}) || []
|
|
);
|
|
|
|
for (const submission of submissions) {
|
|
// Get name from submission_metadata
|
|
const metadata = submission.submission_metadata as any;
|
|
const entityName = Array.isArray(metadata) && metadata.length > 0
|
|
? metadata[0]?.name
|
|
: undefined;
|
|
|
|
const submissionItem = itemsMap.get(submission.id);
|
|
const itemData = submissionItem?.item_data as any;
|
|
|
|
// Build base details
|
|
const details: SubmissionReviewDetails = {
|
|
submission_id: submission.id,
|
|
submission_type: submission.submission_type,
|
|
status: submission.status,
|
|
entity_name: entityName,
|
|
};
|
|
|
|
// Enrich with photo-specific data for photo submissions
|
|
if (submissionItem && itemData) {
|
|
if (submissionItem.item_type === 'photo_delete') {
|
|
details.photo_url = itemData.cloudflare_image_url;
|
|
details.photo_caption = itemData.caption;
|
|
details.photo_title = itemData.title;
|
|
details.entity_type = itemData.entity_type;
|
|
details.entity_id = itemData.entity_id;
|
|
details.deletion_reason = itemData.reason;
|
|
} else if (submissionItem.item_type === 'photo') {
|
|
// Photo additions
|
|
details.photo_url = itemData.cloudflare_image_url;
|
|
details.photo_caption = itemData.caption;
|
|
details.photo_title = itemData.title;
|
|
details.entity_type = itemData.entity_type;
|
|
details.entity_id = itemData.entity_id;
|
|
}
|
|
}
|
|
|
|
activities.push({
|
|
id: submission.id,
|
|
type: 'submission_review',
|
|
timestamp: submission.reviewed_at!,
|
|
actor_id: submission.reviewer_id,
|
|
action: `${submission.status} ${submission.submission_type} submission`,
|
|
details,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Fetch report resolutions
|
|
const { data: reports, error: reportsError } = await supabase
|
|
.from('reports')
|
|
.select('id, reported_entity_type, reported_entity_id, status, reviewed_by, reviewed_at')
|
|
.not('reviewed_at', 'is', null)
|
|
.order('reviewed_at', { ascending: false })
|
|
.limit(limit);
|
|
|
|
if (!reportsError && reports) {
|
|
for (const report of reports) {
|
|
activities.push({
|
|
id: report.id,
|
|
type: 'report_resolution',
|
|
timestamp: report.reviewed_at!,
|
|
actor_id: report.reviewed_by,
|
|
action: `${report.status} report`,
|
|
details: {
|
|
report_id: report.id,
|
|
reported_entity_type: report.reported_entity_type,
|
|
reported_entity_id: report.reported_entity_id,
|
|
status: report.status,
|
|
} as ReportResolutionDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Fetch review moderation
|
|
const { data: reviews, error: reviewsError } = await supabase
|
|
.from('reviews')
|
|
.select('id, park_id, ride_id, moderation_status, moderated_by, moderated_at')
|
|
.not('moderated_at', 'is', null)
|
|
.neq('moderation_status', 'pending')
|
|
.order('moderated_at', { ascending: false })
|
|
.limit(limit);
|
|
|
|
if (!reviewsError && reviews) {
|
|
for (const review of reviews) {
|
|
const entityType = review.park_id ? 'park' : 'ride';
|
|
const entityId = review.park_id || review.ride_id;
|
|
|
|
activities.push({
|
|
id: review.id,
|
|
type: 'review_moderation',
|
|
timestamp: review.moderated_at!,
|
|
actor_id: review.moderated_by,
|
|
action: `${review.moderation_status} review`,
|
|
details: {
|
|
review_id: review.id,
|
|
entity_type: entityType,
|
|
entity_id: entityId!,
|
|
moderation_status: review.moderation_status,
|
|
} as ReviewModerationDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Fetch photo approvals
|
|
const { data: photos, error: photosError } = await supabase
|
|
.from('photos')
|
|
.select('id, entity_type, entity_id, approved_by, approved_at')
|
|
.not('approved_at', 'is', null)
|
|
.order('approved_at', { ascending: false })
|
|
.limit(limit);
|
|
|
|
if (!photosError && photos) {
|
|
for (const photo of photos) {
|
|
activities.push({
|
|
id: photo.id,
|
|
type: 'photo_approval',
|
|
timestamp: photo.approved_at!,
|
|
actor_id: photo.approved_by,
|
|
action: `approved ${photo.entity_type} photo`,
|
|
details: {
|
|
photo_id: photo.id,
|
|
entity_type: photo.entity_type,
|
|
entity_id: photo.entity_id,
|
|
} as PhotoApprovalDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Fetch account lifecycle events
|
|
// 1. Account deletions
|
|
const { data: deletionRequests, error: deletionsError } = await supabase
|
|
.from('account_deletion_requests')
|
|
.select('id, user_id, status, requested_at, completed_at, cancelled_at, scheduled_deletion_at, cancellation_reason')
|
|
.order('requested_at', { ascending: false })
|
|
.limit(limit);
|
|
|
|
if (!deletionsError && deletionRequests) {
|
|
for (const request of deletionRequests) {
|
|
// Deletion requested
|
|
activities.push({
|
|
id: `${request.id}-requested`,
|
|
type: 'account_deletion_requested',
|
|
timestamp: request.requested_at,
|
|
actor_id: request.user_id,
|
|
action: 'requested account deletion',
|
|
details: {
|
|
user_id: request.user_id,
|
|
action: 'deletion_requested',
|
|
scheduled_date: request.scheduled_deletion_at,
|
|
request_id: request.id,
|
|
} as AccountLifecycleDetails,
|
|
});
|
|
|
|
// Deletion confirmed
|
|
if (request.status === 'confirmed' || request.status === 'completed') {
|
|
activities.push({
|
|
id: `${request.id}-confirmed`,
|
|
type: 'account_deletion_confirmed',
|
|
timestamp: request.completed_at || request.requested_at,
|
|
actor_id: request.user_id,
|
|
action: 'confirmed account deletion',
|
|
details: {
|
|
user_id: request.user_id,
|
|
action: 'deletion_confirmed',
|
|
scheduled_date: request.scheduled_deletion_at,
|
|
request_id: request.id,
|
|
} as AccountLifecycleDetails,
|
|
});
|
|
}
|
|
|
|
// Deletion cancelled
|
|
if (request.status === 'cancelled' && request.cancelled_at) {
|
|
activities.push({
|
|
id: `${request.id}-cancelled`,
|
|
type: 'account_deletion_cancelled',
|
|
timestamp: request.cancelled_at,
|
|
actor_id: request.user_id,
|
|
action: 'cancelled account deletion',
|
|
details: {
|
|
user_id: request.user_id,
|
|
action: 'deletion_cancelled',
|
|
reason: request.cancellation_reason || undefined,
|
|
request_id: request.id,
|
|
} as AccountLifecycleDetails,
|
|
});
|
|
}
|
|
}
|
|
}
|
|
|
|
// 2. New account creations (recent 7 days)
|
|
const sevenDaysAgo = new Date();
|
|
sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 7);
|
|
|
|
const { data: newAccounts, error: accountsError } = await supabase
|
|
.from('profiles')
|
|
.select('user_id, username, display_name, created_at')
|
|
.gte('created_at', sevenDaysAgo.toISOString())
|
|
.order('created_at', { ascending: false })
|
|
.limit(Math.ceil(limit / 2));
|
|
|
|
if (!accountsError && newAccounts) {
|
|
for (const account of newAccounts) {
|
|
activities.push({
|
|
id: `account-${account.user_id}`,
|
|
type: 'account_created',
|
|
timestamp: account.created_at,
|
|
actor_id: account.user_id,
|
|
action: 'created account',
|
|
details: {
|
|
user_id: account.user_id,
|
|
username: account.username,
|
|
action: 'created',
|
|
} as AccountLifecycleDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// 3. User bans/unbans from admin audit log
|
|
const { data: banActions, error: banError } = await supabase
|
|
.from('admin_audit_log')
|
|
.select(`
|
|
id,
|
|
admin_user_id,
|
|
target_user_id,
|
|
action,
|
|
created_at,
|
|
admin_audit_details(detail_key, detail_value)
|
|
`)
|
|
.in('action', ['user_banned', 'user_unbanned'])
|
|
.order('created_at', { ascending: false })
|
|
.limit(limit);
|
|
|
|
if (!banError && banActions) {
|
|
for (const action of banActions) {
|
|
const activityType = action.action === 'user_banned' ? 'user_banned' : 'user_unbanned';
|
|
|
|
// Convert relational details back to object format
|
|
const details: Record<string, any> = {};
|
|
if (action.admin_audit_details) {
|
|
for (const detail of action.admin_audit_details as any[]) {
|
|
details[detail.detail_key] = detail.detail_value;
|
|
}
|
|
}
|
|
|
|
activities.push({
|
|
id: action.id,
|
|
type: activityType,
|
|
timestamp: action.created_at,
|
|
actor_id: action.admin_user_id,
|
|
action: action.action === 'user_banned' ? 'banned user' : 'unbanned user',
|
|
details: {
|
|
user_id: action.target_user_id,
|
|
action: action.action === 'user_banned' ? 'banned' : 'unbanned',
|
|
reason: details.reason,
|
|
} as AccountLifecycleDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Fetch submission workflow events (recent 7 days)
|
|
// 1. Submission creations
|
|
const { data: newSubmissions, error: newSubmissionsError } = await supabase
|
|
.from('content_submissions')
|
|
.select('id, user_id, submission_type, submitted_at')
|
|
.gte('submitted_at', sevenDaysAgo.toISOString())
|
|
.order('submitted_at', { ascending: false })
|
|
.limit(Math.ceil(limit / 2));
|
|
|
|
if (!newSubmissionsError && newSubmissions) {
|
|
for (const submission of newSubmissions) {
|
|
activities.push({
|
|
id: `submission-created-${submission.id}`,
|
|
type: 'submission_created',
|
|
timestamp: submission.submitted_at,
|
|
actor_id: submission.user_id,
|
|
action: 'created submission',
|
|
details: {
|
|
submission_id: submission.id,
|
|
submission_type: submission.submission_type,
|
|
user_id: submission.user_id,
|
|
} as SubmissionWorkflowDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// 2. Submission claims/assignments
|
|
const { data: claimedSubmissions, error: claimsError } = await supabase
|
|
.from('content_submissions')
|
|
.select('id, submission_type, assigned_to, assigned_at, user_id')
|
|
.not('assigned_at', 'is', null)
|
|
.gte('assigned_at', sevenDaysAgo.toISOString())
|
|
.order('assigned_at', { ascending: false })
|
|
.limit(Math.ceil(limit / 2));
|
|
|
|
if (!claimsError && claimedSubmissions) {
|
|
for (const submission of claimedSubmissions) {
|
|
activities.push({
|
|
id: `submission-claimed-${submission.id}`,
|
|
type: 'submission_claimed',
|
|
timestamp: submission.assigned_at!,
|
|
actor_id: submission.assigned_to,
|
|
action: 'claimed submission',
|
|
details: {
|
|
submission_id: submission.id,
|
|
submission_type: submission.submission_type,
|
|
user_id: submission.user_id,
|
|
assigned_to: submission.assigned_to,
|
|
} as SubmissionWorkflowDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// 3. Submission escalations
|
|
const { data: escalatedSubmissions, error: escalationsError } = await supabase
|
|
.from('content_submissions')
|
|
.select('id, submission_type, escalated_by, escalated_at, escalation_reason, user_id')
|
|
.eq('escalated', true)
|
|
.not('escalated_at', 'is', null)
|
|
.gte('escalated_at', sevenDaysAgo.toISOString())
|
|
.order('escalated_at', { ascending: false })
|
|
.limit(Math.ceil(limit / 2));
|
|
|
|
if (!escalationsError && escalatedSubmissions) {
|
|
for (const submission of escalatedSubmissions) {
|
|
activities.push({
|
|
id: `submission-escalated-${submission.id}`,
|
|
type: 'submission_escalated',
|
|
timestamp: submission.escalated_at!,
|
|
actor_id: submission.escalated_by,
|
|
action: 'escalated submission',
|
|
details: {
|
|
submission_id: submission.id,
|
|
submission_type: submission.submission_type,
|
|
user_id: submission.user_id,
|
|
escalation_reason: submission.escalation_reason || undefined,
|
|
} as SubmissionWorkflowDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Fetch review lifecycle events
|
|
// 1. Review creations (recent 7 days)
|
|
const { data: newReviews, error: newReviewsError } = await supabase
|
|
.from('reviews')
|
|
.select('id, user_id, park_id, ride_id, rating, content, created_at')
|
|
.gte('created_at', sevenDaysAgo.toISOString())
|
|
.order('created_at', { ascending: false })
|
|
.limit(Math.ceil(limit / 2));
|
|
|
|
if (!newReviewsError && newReviews) {
|
|
for (const review of newReviews) {
|
|
const entityType = review.park_id ? 'park' : 'ride';
|
|
const entityId = review.park_id || review.ride_id;
|
|
|
|
activities.push({
|
|
id: `review-${review.id}`,
|
|
type: 'review_created',
|
|
timestamp: review.created_at,
|
|
actor_id: review.user_id,
|
|
action: 'created review',
|
|
details: {
|
|
review_id: review.id,
|
|
user_id: review.user_id,
|
|
entity_type: entityType as 'park' | 'ride',
|
|
entity_id: entityId!,
|
|
rating: review.rating,
|
|
content: review.content,
|
|
} as ReviewLifecycleDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// 2. Review deletions
|
|
const { data: deletedReviews, error: deletedReviewsError } = await supabase
|
|
.from('review_deletions')
|
|
.select('id, review_id, user_id, park_id, ride_id, rating, content, deleted_by, deleted_at, deletion_reason, was_moderated')
|
|
.order('deleted_at', { ascending: false })
|
|
.limit(limit);
|
|
|
|
if (!deletedReviewsError && deletedReviews) {
|
|
for (const deletion of deletedReviews) {
|
|
const entityType = deletion.park_id ? 'park' : 'ride';
|
|
const entityId = deletion.park_id || deletion.ride_id;
|
|
|
|
activities.push({
|
|
id: deletion.id,
|
|
type: 'review_deleted',
|
|
timestamp: deletion.deleted_at!,
|
|
actor_id: deletion.deleted_by,
|
|
action: 'deleted review',
|
|
details: {
|
|
review_id: deletion.review_id,
|
|
user_id: deletion.user_id,
|
|
entity_type: entityType as 'park' | 'ride',
|
|
entity_id: entityId!,
|
|
rating: deletion.rating,
|
|
content: deletion.content || undefined,
|
|
deletion_reason: deletion.deletion_reason || undefined,
|
|
was_moderated: deletion.was_moderated,
|
|
} as ReviewLifecycleDetails,
|
|
});
|
|
}
|
|
}
|
|
|
|
// Sort all activities by timestamp (newest first)
|
|
activities.sort((a, b) => new Date(b.timestamp).getTime() - new Date(a.timestamp).getTime());
|
|
|
|
// Apply filters
|
|
let filteredActivities = activities;
|
|
if (filters?.type) {
|
|
filteredActivities = filteredActivities.filter(a => a.type === filters.type);
|
|
}
|
|
if (filters?.userId) {
|
|
filteredActivities = filteredActivities.filter(a => a.actor_id === filters.userId);
|
|
}
|
|
|
|
// Limit to requested amount
|
|
filteredActivities = filteredActivities.slice(0, limit);
|
|
|
|
// Enrich with user profile data
|
|
const uniqueUserIds = [...new Set(filteredActivities.map(a => a.actor_id).filter(Boolean))] as string[];
|
|
|
|
if (uniqueUserIds.length > 0) {
|
|
let profiles: Array<{ user_id: string; username: string; display_name?: string | null; avatar_url?: string | null }> | null = null;
|
|
const { data: allProfiles, error: rpcError } = await supabase
|
|
.rpc('get_users_with_emails');
|
|
|
|
if (rpcError) {
|
|
const { data: basicProfiles } = await supabase
|
|
.from('profiles')
|
|
.select('user_id, username, display_name, avatar_url')
|
|
.in('user_id', uniqueUserIds);
|
|
profiles = basicProfiles as typeof profiles;
|
|
} else {
|
|
profiles = allProfiles?.filter(p => uniqueUserIds.includes(p.user_id)) || null;
|
|
}
|
|
|
|
if (profiles) {
|
|
const profileMap = new Map(profiles.map(p => [p.user_id, p]));
|
|
|
|
for (const activity of filteredActivities) {
|
|
if (activity.actor_id) {
|
|
const profile = profileMap.get(activity.actor_id);
|
|
if (profile) {
|
|
activity.actor = {
|
|
id: profile.user_id,
|
|
username: profile.username,
|
|
display_name: profile.display_name || undefined,
|
|
avatar_url: profile.avatar_url || undefined,
|
|
};
|
|
}
|
|
}
|
|
}
|
|
|
|
// Also enrich admin action target users
|
|
const targetUserIds = filteredActivities
|
|
.filter(a => a.type === 'admin_action')
|
|
.map(a => (a.details as AdminActionDetails).target_user_id)
|
|
.filter(Boolean) as string[];
|
|
|
|
if (targetUserIds.length > 0) {
|
|
let targetProfiles: Array<{ user_id: string; username: string; display_name?: string | null }> | null = null;
|
|
const { data: allTargetProfiles, error: targetRpcError } = await supabase
|
|
.rpc('get_users_with_emails');
|
|
|
|
if (targetRpcError) {
|
|
const { data: basicProfiles } = await supabase
|
|
.from('profiles')
|
|
.select('user_id, username, display_name')
|
|
.in('user_id', targetUserIds);
|
|
targetProfiles = basicProfiles as typeof targetProfiles;
|
|
} else {
|
|
targetProfiles = allTargetProfiles?.filter(p => targetUserIds.includes(p.user_id)) || null;
|
|
}
|
|
|
|
if (targetProfiles) {
|
|
const targetProfileMap = new Map(targetProfiles.map(p => [p.user_id, p]));
|
|
|
|
for (const activity of filteredActivities) {
|
|
if (activity.type === 'admin_action') {
|
|
const details = activity.details as AdminActionDetails;
|
|
if (details.target_user_id) {
|
|
const targetProfile = targetProfileMap.get(details.target_user_id);
|
|
if (targetProfile) {
|
|
details.target_username = targetProfile.username;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Enrich account lifecycle target users (for bans/unbans)
|
|
const accountUserIds = filteredActivities
|
|
.filter(a => ['user_banned', 'user_unbanned', 'account_deletion_requested', 'account_deletion_confirmed', 'account_deletion_cancelled'].includes(a.type))
|
|
.map(a => (a.details as AccountLifecycleDetails).user_id)
|
|
.filter(Boolean) as string[];
|
|
|
|
if (accountUserIds.length > 0) {
|
|
let accountProfiles: Array<{ user_id: string; username: string; display_name?: string | null }> | null = null;
|
|
const { data: allAccountProfiles, error: accountRpcError } = await supabase
|
|
.rpc('get_users_with_emails');
|
|
|
|
if (accountRpcError) {
|
|
const { data: basicProfiles } = await supabase
|
|
.from('profiles')
|
|
.select('user_id, username, display_name')
|
|
.in('user_id', accountUserIds);
|
|
accountProfiles = basicProfiles as typeof accountProfiles;
|
|
} else {
|
|
accountProfiles = allAccountProfiles?.filter(p => accountUserIds.includes(p.user_id)) || null;
|
|
}
|
|
|
|
if (accountProfiles) {
|
|
const accountProfileMap = new Map(accountProfiles.map(p => [p.user_id, p]));
|
|
|
|
for (const activity of filteredActivities) {
|
|
if (['user_banned', 'user_unbanned', 'account_deletion_requested', 'account_deletion_confirmed', 'account_deletion_cancelled'].includes(activity.type)) {
|
|
const details = activity.details as AccountLifecycleDetails;
|
|
if (details.user_id && !details.username) {
|
|
const accountProfile = accountProfileMap.get(details.user_id);
|
|
if (accountProfile) {
|
|
details.username = accountProfile.username;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Enrich review lifecycle users and entities
|
|
const reviewUserIds = filteredActivities
|
|
.filter(a => ['review_created', 'review_deleted'].includes(a.type))
|
|
.map(a => (a.details as ReviewLifecycleDetails).user_id)
|
|
.filter(Boolean) as string[];
|
|
|
|
if (reviewUserIds.length > 0) {
|
|
const { data: reviewProfiles } = await supabase
|
|
.from('profiles')
|
|
.select('user_id, username')
|
|
.in('user_id', reviewUserIds);
|
|
|
|
if (reviewProfiles) {
|
|
const reviewProfileMap = new Map(reviewProfiles.map(p => [p.user_id, p]));
|
|
|
|
for (const activity of filteredActivities) {
|
|
if (['review_created', 'review_deleted'].includes(activity.type)) {
|
|
const details = activity.details as ReviewLifecycleDetails;
|
|
if (details.user_id && !details.username) {
|
|
const reviewProfile = reviewProfileMap.get(details.user_id);
|
|
if (reviewProfile) {
|
|
details.username = reviewProfile.username;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Enrich submission workflow users
|
|
const submissionWorkflowUserIds = filteredActivities
|
|
.filter(a => ['submission_created', 'submission_claimed', 'submission_escalated', 'submission_reassigned'].includes(a.type))
|
|
.flatMap(a => {
|
|
const details = a.details as SubmissionWorkflowDetails;
|
|
return [details.user_id, details.assigned_to, details.from_moderator, details.to_moderator].filter(Boolean);
|
|
})
|
|
.filter(Boolean) as string[];
|
|
|
|
if (submissionWorkflowUserIds.length > 0) {
|
|
const { data: submissionProfiles } = await supabase
|
|
.from('profiles')
|
|
.select('user_id, username')
|
|
.in('user_id', submissionWorkflowUserIds);
|
|
|
|
if (submissionProfiles) {
|
|
const submissionProfileMap = new Map(submissionProfiles.map(p => [p.user_id, p]));
|
|
|
|
for (const activity of filteredActivities) {
|
|
if (['submission_created', 'submission_claimed', 'submission_escalated', 'submission_reassigned'].includes(activity.type)) {
|
|
const details = activity.details as SubmissionWorkflowDetails;
|
|
if (details.user_id && !details.username) {
|
|
const profile = submissionProfileMap.get(details.user_id);
|
|
if (profile) details.username = profile.username;
|
|
}
|
|
if (details.assigned_to && !details.assigned_username) {
|
|
const profile = submissionProfileMap.get(details.assigned_to);
|
|
if (profile) details.assigned_username = profile.username;
|
|
}
|
|
if (details.from_moderator && !details.from_moderator_username) {
|
|
const profile = submissionProfileMap.get(details.from_moderator);
|
|
if (profile) details.from_moderator_username = profile.username;
|
|
}
|
|
if (details.to_moderator && !details.to_moderator_username) {
|
|
const profile = submissionProfileMap.get(details.to_moderator);
|
|
if (profile) details.to_moderator_username = profile.username;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Enrich review entity names
|
|
const parkReviewIds = filteredActivities
|
|
.filter(a => ['review_created', 'review_deleted'].includes(a.type) && (a.details as ReviewLifecycleDetails).entity_type === 'park')
|
|
.map(a => (a.details as ReviewLifecycleDetails).entity_id)
|
|
.filter(Boolean) as string[];
|
|
|
|
const rideReviewIds = filteredActivities
|
|
.filter(a => ['review_created', 'review_deleted'].includes(a.type) && (a.details as ReviewLifecycleDetails).entity_type === 'ride')
|
|
.map(a => (a.details as ReviewLifecycleDetails).entity_id)
|
|
.filter(Boolean) as string[];
|
|
|
|
if (parkReviewIds.length > 0) {
|
|
const { data: parks } = await supabase
|
|
.from('parks')
|
|
.select('id, name')
|
|
.in('id', parkReviewIds);
|
|
|
|
if (parks) {
|
|
const parkMap = new Map(parks.map(p => [p.id, p.name]));
|
|
|
|
for (const activity of filteredActivities) {
|
|
if (['review_created', 'review_deleted'].includes(activity.type)) {
|
|
const details = activity.details as ReviewLifecycleDetails;
|
|
if (details.entity_type === 'park' && !details.entity_name) {
|
|
details.entity_name = parkMap.get(details.entity_id);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
if (rideReviewIds.length > 0) {
|
|
const { data: rides } = await supabase
|
|
.from('rides')
|
|
.select('id, name')
|
|
.in('id', rideReviewIds);
|
|
|
|
if (rides) {
|
|
const rideMap = new Map(rides.map(r => [r.id, r.name]));
|
|
|
|
for (const activity of filteredActivities) {
|
|
if (['review_created', 'review_deleted'].includes(activity.type)) {
|
|
const details = activity.details as ReviewLifecycleDetails;
|
|
if (details.entity_type === 'ride' && !details.entity_name) {
|
|
details.entity_name = rideMap.get(details.entity_id);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return filteredActivities;
|
|
}
|