57 lines
4.1 KiB
Python
57 lines
4.1 KiB
Python
"""Paginated diagnostic log derived from original stored records; nothing deleted."""
|
|
import math
|
|
|
|
CATEGORIES={
|
|
'prompt_outcome':'Product prompt outcome missing','prompt_source':'Product prompt source missing',
|
|
'session_end':'Stale session end missing','activity_result':'Activity result missing',
|
|
'activity_stages':'Activity stages missing','metric_value':'Game metric value missing',
|
|
'sale_player':'Sale attribution missing','ownership_check':'Gamepass ownership check missing',
|
|
'purchase_date':'Gamepass purchase date missing'}
|
|
|
|
ISSUES='''WITH issues AS (
|
|
SELECT 'prompt_outcome' issue,'Product prompt outcome missing' title,'interaction' record_type,id::text record_id,
|
|
started_at observed_at,'Prompt has no resolved buy/close result.' details FROM interaction_events
|
|
WHERE game_id=%(gid)s AND kind='product_prompt' AND (outcome IS NULL OR ended_at IS NULL)
|
|
UNION ALL SELECT 'prompt_source','Product prompt source missing','interaction',id::text,started_at,
|
|
'Source is unknown; excluded from game/player prompt comparisons.' FROM interaction_events
|
|
WHERE game_id=%(gid)s AND kind='product_prompt' AND trigger_source='unknown'
|
|
UNION ALL SELECT 'session_end','Stale session end missing','session',id::text,started_at,
|
|
'No end report after six hours; completed duration remains unknown.' FROM sessions
|
|
WHERE game_id=%(gid)s AND ended_at IS NULL AND started_at<=now()-interval '6 hours'
|
|
UNION ALL SELECT 'activity_result','Activity result missing','activity',id::text,started_at,
|
|
'End time or outcome is missing; excluded from activity outcome analytics.' FROM activity_runs
|
|
WHERE game_id=%(gid)s AND (ended_at IS NULL OR outcome IS NULL)
|
|
UNION ALL SELECT 'activity_stages','Activity stages missing','activity',id::text,started_at,
|
|
'Ordered stage observations are missing; excluded from stage funnels.' FROM activity_runs
|
|
WHERE game_id=%(gid)s AND stage_progress IS NULL
|
|
UNION ALL SELECT 'metric_value','Game metric value missing','metric',id::text,recorded_at,
|
|
category||' / '||name||' has no reported value.' FROM game_metrics WHERE game_id=%(gid)s AND value IS NULL
|
|
UNION ALL SELECT 'sale_player','Sale attribution missing','sale',id::text,purchased_at,
|
|
'Positive-price sale has no player reference.' FROM product_sales
|
|
WHERE game_id=%(gid)s AND player_id IS NULL AND unit_price_robux>0
|
|
UNION ALL SELECT 'ownership_check','Gamepass ownership check missing','player / pass',p.id::text||' / '||gp.id::text,
|
|
NULL::timestamptz,'Ownership not reported for '||coalesce(gp.name,gp.id::text)||'.' FROM players p
|
|
JOIN gamepasses gp ON gp.game_id=p.game_id LEFT JOIN gamepass_ownership o
|
|
ON o.game_id=p.game_id AND o.player_id=p.id AND o.gamepass_id=gp.id
|
|
WHERE p.game_id=%(gid)s AND o.player_id IS NULL
|
|
UNION ALL SELECT 'purchase_date','Gamepass purchase date missing','player / pass',o.player_id::text||' / '||o.gamepass_id::text,
|
|
o.checked_at,'Ownership is known, but purchase date is not reported.' FROM gamepass_ownership o
|
|
WHERE o.game_id=%(gid)s AND o.is_owned AND o.purchased_at IS NULL
|
|
) '''
|
|
|
|
|
|
def quality_log(conn,gid,params):
|
|
from sample_api import integer,ApiError
|
|
scope={'gid':gid}
|
|
counted=conn.execute(ISSUES+'SELECT issue,count(*)::int count FROM issues GROUP BY issue',scope).fetchall()
|
|
counts={r['issue']:r['count'] for r in counted}
|
|
categories=[{'issue':key,'title':title,'count':counts.get(key,0)} for key,title in sorted(CATEGORIES.items(),key=lambda item:item[1])]
|
|
issue=params.get('issue',['all'])[0]
|
|
if issue!='all' and issue not in CATEGORIES: raise ApiError(400,'Unknown data-quality category.')
|
|
scope['issue']=issue
|
|
total=conn.execute(ISSUES+"SELECT count(*) total FROM issues WHERE %(issue)s='all' OR issue=%(issue)s",scope).fetchone()['total']
|
|
pages=max(1,math.ceil(total/20));page=min(integer(params,'page',1),pages)
|
|
scope['offset']=(page-1)*20
|
|
records=conn.execute(ISSUES+"SELECT * FROM issues WHERE %(issue)s='all' OR issue=%(issue)s ORDER BY observed_at DESC NULLS LAST,issue,record_id LIMIT 20 OFFSET %(offset)s",scope).fetchall()
|
|
return {'records':records,'categories':categories,'pagination':{'total':total,'page':page,'pages':pages,'pageSize':20}}
|