"""Read-only PostgreSQL analytics for the HTML development UI."""
from collections import Counter, defaultdict
from datetime import datetime, timedelta, timezone
import math
import statistics
from database import connect
from metrics_api import engagement, monetization, gamepasses
from player_records import record_page, player_inventory
from prompt_metrics import prompt_analytics
from health_reports import reports_for_game
from data_quality import quality_log


class ApiError(Exception):
    def __init__(self, status, message):
        self.status, self.message = status, message


def integer(params, key, default, maximum=1000000000):
    try:
        value = int(params.get(key, [str(default)])[0])
    except (ValueError, TypeError):
        raise ApiError(400, f"Invalid {key}.")
    if not 1 <= value <= maximum:
        raise ApiError(400, f"Invalid {key}.")
    return value


def rows(conn, query, args=()):
    return conn.execute(query, args).fetchall()


def one(conn, query, args=()):
    return conn.execute(query, args).fetchone()


def label(day):
    return f"{day:%b} {day.day}"


def game(conn, game_id):
    row = one(conn, "SELECT id,name,roblox_experience_id,timezone,properties FROM games WHERE id=%s", (game_id,))
    if not row:
        raise ApiError(404, "Game not found.")
    return {"id":row["id"],"name":row["name"],"robloxExperienceId":row["roblox_experience_id"],"timezone":row["timezone"],"properties":row["properties"]}


def fields_templates(conn, game_id):
    fields = rows(conn,"SELECT key,label,format,source FROM player_fields WHERE game_id=%s ORDER BY key",(game_id,))
    templates = {r["key"]:{"label":r["label"],"columns":r["columns"]} for r in rows(conn,"SELECT key,label,columns FROM column_templates WHERE game_id=%s ORDER BY key",(game_id,))}
    templates["all"]={"label":"All values","columns":[f["key"] for f in fields]}
    return fields,templates


def player_values(r):
    now=datetime.now(timezone.utc)
    p=dict(r["latest_data"] or {})
    p.update({"playerId":r["id"],"gameId":r["game_id"],"robloxUserId":r["roblox_user_id"],"name":r["display_name"],
      "id":f"User ID {r['roblox_user_id']}","firstSeen":r["first_seen"],"lastSeenAt":r["last_seen"],"snapshotAt":r["snapshot_at"],
      "firstSeenDays":(now-r["first_seen"]).total_seconds()/86400,
      "lastSeen":round((now-r["last_seen"]).total_seconds()/60,1) if r["last_seen"] else None,
      "sessions":r["session_count"],"playtime":r["playtime"],"avgSession":r["average_session"],
      "medianSession":r["median_session"],"activeDays":r["active_days"],
      "robux":r["robux_recorded"],"purchases":r["products_bought"]})
    p["robuxPerHour"]=round(p["robux"]/(p["playtime"]/3600),2) if p.get("robux") is not None and p["playtime"] else None
    return p


def all_players(conn, gid):
    return [player_values(r) for r in rows(conn,"SELECT * FROM player_statistics WHERE game_id=%s",(gid,))]


def segment_players(players, segment):
    if segment=="all": return players
    if segment=="first": return [p for p in players if p["firstSeenDays"]<=1]
    key={"whales":"robux","playtime":"playtime"}.get(segment)
    if not key: raise ApiError(400,"Unknown player group.")
    known=sorted([p[key] for p in players if p.get(key) is not None and p[key]>0],reverse=True)
    cutoff=known[max(0,math.ceil(len(known)*.2)-1)] if known else None
    return [p for p in players if cutoff is not None and p.get(key) is not None and p[key]>=cutoff]


def player_list(conn,gid,params):
    fields,templates=fields_templates(conn,gid)
    players=all_players(conn,gid)
    ranking=params.get('ranking',['level'])[0]
    if ranking not in {f['key'] for f in fields}: raise ApiError(400,'Unknown ranking.')
    boards=[]
    for key in dict.fromkeys(['robux','playtime','cash',ranking]):
        field=next((f for f in fields if f['key']==key),None)
        if not field: continue
        eligible=[p for p in players if type(p.get(key)) in (int,float)]
        leaders=sorted(eligible,key=lambda p:(-p[key],p['playerId']))[:5]
        boards.append({'key':key,'label':field['label'],'format':field['format'],
          'players':[{'playerId':p['playerId'],'name':p['name'],'robloxUserId':p['robloxUserId'],'value':p[key]} for p in leaders]})
    known_levels=[p["level"] for p in players if p.get("level") is not None]
    known_times=[p["playtime"] for p in players if p["playtime"] is not None]
    metrics={"playerCount":len(players),"activePlayers":sum(p["lastSeen"] is not None and p["lastSeen"]<=7*1440 for p in players),
      "averageLevel":round(statistics.mean(known_levels),1) if known_levels else None,"totalPlaytime":sum(known_times) if known_times else None}
    players=segment_players(players,params.get("segment",["all"])[0])
    needle=params.get("q",[""])[0].strip().lower()
    players=[p for p in players if needle in f"{p['name'] or ''} {p['robloxUserId']}".lower()]
    key=params.get("sort",["level"])[0]
    if key not in {f["key"] for f in fields}: raise ApiError(400,"Unknown player sort field.")
    direction=params.get("direction",["desc"])[0]
    if direction not in ("asc","desc"): raise ApiError(400,"Unknown direction.")
    known=sorted([p for p in players if p.get(key) is not None],key=lambda p:(p[key],p["playerId"]),reverse=direction=="desc")
    players=known+sorted([p for p in players if p.get(key) is None],key=lambda p:p["playerId"])
    size=integer(params,"page_size",20,20)
    page=min(integer(params,"page",1),max(1,math.ceil(len(players)/size)))
    return {"metrics":metrics,"fields":fields,"templates":templates,"leaderboards":boards,"playerRows":players[(page-1)*size:page*size],
      "pagination":{"page":page,"pageSize":size,"total":len(players),"pages":max(1,math.ceil(len(players)/size))},
      "groupDefinitions":{"whales":"Top 20% of known positive Robux spenders","playtime":"Top 20% of known positive playtime","first":"First seen within 24 hours"}}


def sessions(conn,gid,pid=None,limit=50):
    clause,args=("AND s.player_id=%s",(gid,pid,limit)) if pid else ("",(gid,limit))
    return rows(conn,f"""SELECT s.id,s.player_id,p.display_name,p.roblox_user_id,s.started_at,s.ended_at,s.place_name,s.stats,
      extract(epoch FROM s.ended_at-s.started_at)::double precision duration,
      (s.ended_at IS NULL AND s.started_at>now()-interval '6 hours' AND s.started_at<=now()) AS "joinAvailable" FROM sessions s
      JOIN players p ON p.game_id=s.game_id AND p.id=s.player_id WHERE s.game_id=%s {clause}
      ORDER BY s.started_at DESC,s.id DESC LIMIT %s""",args)


def events(conn,gid,pid=None,limit=50):
    clause,args=("AND e.player_id=%s",(gid,pid,limit)) if pid else ("",(gid,limit))
    return rows(conn,f"""SELECT e.id,e.category,e.name,e.player_id,p.roblox_user_id,e.occurred_at,e.properties
      FROM events e LEFT JOIN players p ON p.game_id=e.game_id AND p.id=e.player_id
      WHERE e.game_id=%s {clause} ORDER BY e.occurred_at DESC,e.id DESC LIMIT %s""",args)


def retention(conn,gid,days):
    visits=rows(conn,"SELECT player_id,array_agg(DISTINCT (started_at AT TIME ZONE 'UTC')::date) dates FROM sessions WHERE game_id=%s GROUP BY player_id",(gid,))
    cohorts=defaultdict(list)
    today=datetime.now(timezone.utc).date()
    for row in visits:
        dates=set(row["dates"])
        cohorts[min(dates)].append(dates)
    def rate(offset):
        eligible=[(d,members) for d,members in cohorts.items() if today-timedelta(days=days-1)<=d and d+timedelta(days=offset)<today]
        denominator=sum(len(members) for _,members in eligible)
        count=sum(sum(d+timedelta(days=offset) in dates for dates in members) for d,members in eligible)
        return round(100*count/denominator,1) if denominator else None
    starts=[today-timedelta(days=i) for i in reversed(range(days))]
    grid=[]
    for start in starts:
        members=cohorts.get(start,[])
        values=[round(100*sum(start+timedelta(days=d) in dates for dates in members)/len(members),1)
          if members and start+timedelta(days=d)<today else None for d in range(8)]
        grid.append({"name":label(start),"players":len(members),"values":values})
    mature=[(d,m) for d,m in cohorts.items() if starts[0]<=d and d+timedelta(days=7)<today]
    denominator=sum(len(m) for d,m in mature)
    week=[round(100*sum(sum(d+timedelta(days=offset) in dates for dates in members) for d,members in mature)/denominator,2) if denominator else None for offset in range(1,8)]
    return {"retention":rate(1),"day7":rate(7),"weekRetention":{"labels":[f'Day {d}' for d in range(1,8)],
      "series":[{"name":"Exact-day retention","format":"percent","values":week}],"players":denominator},"retentionGrid":{"columns":[f"Day {d}" for d in range(8)],"rows":grid},
      "labels":[r["name"] for r in grid],"series":[{"name":"Day 1","values":[r["values"][1] for r in grid]},{"name":"Day 7","values":[r["values"][7] for r in grid]}]}


def root_metrics(conn,gid,days):
    latest=rows(conn,"SELECT DISTINCT ON(category,name) category,name,recorded_at,value,properties FROM game_metrics WHERE game_id=%s ORDER BY category,name,recorded_at DESC",(gid,))
    trend=rows(conn,"""SELECT (recorded_at AT TIME ZONE 'UTC')::date AS day,avg(value) value FROM game_metrics
      WHERE game_id=%s AND category='ccu' AND recorded_at>=current_date-(%s-1) GROUP BY day ORDER BY day""",(gid,days))
    return {"rows":latest,"labels":[label(r["day"]) for r in trend],"series":[{"name":"Average CCU","values":[round(r["value"],1) if r["value"] is not None else None for r in trend]}]}


def overview(conn,gid,days,player_ids=None):
    metrics=one(conn,"""SELECT count(*) sessions,count(DISTINCT player_id) players,
      avg(extract(epoch FROM ended_at-started_at))::double precision "avgSession",
      percentile_cont(.5) WITHIN GROUP(ORDER BY extract(epoch FROM ended_at-started_at))::double precision "medianSession",
      count(*) FILTER(WHERE ended_at IS NULL AND started_at>now()-interval '6 hours') active,
      count(DISTINCT player_id) FILTER(WHERE player_id IN (SELECT id FROM players WHERE game_id=%s AND first_seen>=current_date-(%s-1))) "newPlayers"
      FROM sessions WHERE game_id=%s AND started_at>=current_date-(%s-1)
      AND (%s::bigint[] IS NULL OR player_id=ANY(%s))""",(gid,days,gid,days,player_ids,player_ids))
    metrics["returningPlayers"]=metrics["players"]-metrics["newPlayers"]
    metrics.update(one(conn,'SELECT count(*) events,count(DISTINCT name) "eventTypes",max(occurred_at) "latestEvent" FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1)',(gid,days)))
    metrics["retention"]=retention(conn,gid,days)["retention"]
    metrics["visits"]=one(conn,"SELECT sum(value) value FROM game_metrics WHERE game_id=%s AND category='visits' AND recorded_at>=current_date-(%s-1)",(gid,days))["value"]
    ccu=one(conn,"SELECT value FROM game_metrics WHERE game_id=%s AND category='ccu' AND name='concurrent_players' ORDER BY recorded_at DESC LIMIT 1",(gid,))
    metrics["ccu"]=ccu["value"] if ccu else None
    daily=rows(conn,"SELECT (started_at AT TIME ZONE 'UTC')::date AS day,count(*) sessions,count(DISTINCT player_id) players FROM sessions WHERE game_id=%s AND started_at>=current_date-(%s-1) AND (%s::bigint[] IS NULL OR player_id=ANY(%s)) GROUP BY day ORDER BY day",(gid,days,player_ids,player_ids))
    lookup={r["day"]:r for r in daily}
    dates=[datetime.now(timezone.utc).date()-timedelta(days=i) for i in reversed(range(days))]
    top=rows(conn,"SELECT name,count(*) count FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1) GROUP BY name ORDER BY count DESC LIMIT 7",(gid,days))
    return {"metrics":metrics,"labels":[label(d) for d in dates],"series":[{"name":key.capitalize(),"values":[lookup.get(d,{}).get(key,0) for d in dates]} for key in ("players","sessions")],
      "eventLabels":[r["name"] for r in top],"eventValues":[r["count"] for r in top]}


def event_analytics(conn,gid,days):
    catalog=rows(conn,'SELECT name,array_agg(DISTINCT category ORDER BY category) categories FROM events WHERE game_id=%s GROUP BY name ORDER BY name',(gid,))
    daily=rows(conn,"SELECT name,(occurred_at AT TIME ZONE 'UTC')::date AS day,count(*) total FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1) GROUP BY name,day",(gid,days))
    totals={r['name']:r for r in rows(conn,"SELECT name,count(*) total,count(DISTINCT player_id) players,max(occurred_at) latest FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1) GROUP BY name",(gid,days))}
    lookup={(r['name'],r['day']):r['total'] for r in daily}
    dates=[datetime.now(timezone.utc).date()-timedelta(days=i) for i in reversed(range(days))]
    summary=[{'name':r['name'],'categories':r['categories'],'total':totals.get(r['name'],{}).get('total',0),
       'players':totals.get(r['name'],{}).get('players',0),'average':totals.get(r['name'],{}).get('total',0)/days,
       'latest':totals.get(r['name'],{}).get('latest')} for r in catalog]
    unique=one(conn,'SELECT count(DISTINCT player_id) players,max(occurred_at) latest FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1)',(gid,days))
    return {'metrics':{'events':sum(r['total'] for r in summary),'eventTypes':len(catalog),'players':unique['players'],'latestEvent':unique['latest']},
      'labels':[label(d) for d in dates],
      'series':[{'name':r['name'],'values':[lookup.get((r['name'],d),0) for d in dates]} for r in catalog],
      'eventSummary':summary}


def stage_funnel(runs,definition):
    """Sequential stage reach; unknown observations never become drop-offs."""
    stages=[('Start',definition['start_event'])]+[('Step',s) for s in definition['configuration'].get('steps',[])]
    if definition.get('end_event'): stages.append(('End',definition['end_event']))
    unknown=sum(r.get('stage_progress') is None for r in runs)
    survivors=[(r['stage_progress'],0) for r in runs if isinstance(r.get('stage_progress'),list)]
    count=len(survivors); previous=count; result=[]
    for index,(kind,event) in enumerate(stages):
        reached=[]
        for progress,cursor in survivors:
            try: reached.append((progress,progress.index(event,cursor)+1))
            except ValueError: pass
        survivors=reached
        current=count if index==0 else len(reached)
        known=True
        result.append({'label':kind,'event':event,'runs':current,
            'reachPercent':100*current/count if count and known else None,
            'dropoffPercent':100*(previous-current)/previous if index and previous and known else None,
            'conversionPercent':100*current/previous if index and previous and known else None,
            'droppedRuns':previous-current if index and known else None,'unknownRuns':unknown if index else 0})
        previous=current
    return result


def activities(conn,gid,params,days):
    definitions=rows(conn,"SELECT id,key,name,start_event,end_event,configuration FROM activity_definitions WHERE game_id=%s ORDER BY id",(gid,))
    if not definitions: return {"definitions":[],"metrics":{},"outcomes":[],"captured":[],"journey":[],"labels":[],"series":[]}
    aid=integer(params,"activity_id",definitions[0]["id"])
    definition=next((d for d in definitions if d["id"]==aid),None)
    if not definition: raise ApiError(404,"Activity does not belong to this game.")
    clause,args="",[gid,aid,days]
    segment=params.get("segment",["all"])[0]
    if segment!="all":
        ids=[p["playerId"] for p in segment_players(all_players(conn,gid),segment)]
        clause="AND player_id=ANY(%s)"
        args.append(ids)
    runs=rows(conn,f"SELECT *,extract(epoch FROM ended_at-started_at)::double precision duration FROM activity_runs WHERE game_id=%s AND definition_id=%s AND ended_at IS NOT NULL AND outcome IS NOT NULL AND started_at>=current_date-(%s-1) {clause} ORDER BY started_at",args)
    completed=[r for r in runs if r["duration"] is not None]
    times=[r["duration"] for r in completed]
    prompts=[r for r in runs if (r["prompt_count"] or 0)>0]
    count=len(runs)
    metrics={"runs":count,"average":statistics.mean(times) if times else None,"median":statistics.median(times) if times else None,
      "completion":round(100*len(completed)/count,1) if count else None,"promptRate":round(100*len(prompts)/count,1) if count else None,
      "purchaseRate":round(100*sum(r["outcome"]=="purchased" for r in runs)/count,1) if count else None,
      "promptPurchaseRate":round(100*sum(r["outcome"]=="purchased" for r in prompts)/len(prompts),1) if prompts else None}
    if not any('prompt' in step or 'purchase' in step for step in definition['configuration'].get('steps',[])):
        metrics.update(promptRate=None,purchaseRate=None,promptPurchaseRate=None)
    outcomes=[]
    for outcome in sorted({r["outcome"] for r in runs},key=lambda v:v or ""):
        members=[r for r in runs if r["outcome"]==outcome]
        durations=[r["duration"] for r in members if r["duration"] is not None]
        outcomes.append({"outcome":outcome,"runs":len(members),"share":round(100*len(members)/count,1),"average":statistics.mean(durations) if durations else None,"median":statistics.median(durations) if durations else None})
    captured=[]
    for key in sorted({k for r in runs for k in r["captured_values"]}):
        values=[r["captured_values"][key] for r in runs if r["captured_values"].get(key) is not None]
        numeric=values and all(isinstance(v,(int,float)) for v in values)
        value=round(statistics.mean(values),2) if numeric else Counter(str(v) for v in values).most_common(1)[0][0] if values else None
        captured.append({"key":key,"value":value,"summary":"Average reported value" if numeric else "Most frequent reported value"})
    daily=defaultdict(list)
    for r in completed: daily[r["started_at"].date()].append(r["duration"])
    dates=sorted(daily)
    journey=stage_funnel(runs,definition)
    return {"definitions":definitions,"definition":definition,"metrics":metrics,"outcomes":outcomes,"captured":captured,"journey":journey,
      "labels":[label(d) for d in dates],"series":[{"name":"Average run duration (seconds)","values":[statistics.mean(daily[d]) for d in dates]}]}


def player_history(conn,gid,pid,fields,params):
    snapshots=rows(conn,"SELECT captured_at,data FROM player_snapshots WHERE game_id=%s AND player_id=%s ORDER BY captured_at,id",(gid,pid))
    history=rows(conn,"""SELECT ps.captured_at,ps.data,st.*,sp.robux,sp.purchases FROM player_snapshots ps
      CROSS JOIN LATERAL(SELECT count(*)::int sessions,
        sum(extract(epoch FROM ended_at-started_at)) FILTER(WHERE ended_at<=ps.captured_at)::double precision playtime,
        avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE ended_at<=ps.captured_at)::double precision "avgSession",
        percentile_cont(.5) WITHIN GROUP(ORDER BY extract(epoch FROM ended_at-started_at)) FILTER(WHERE ended_at<=ps.captured_at)::double precision "medianSession",
        count(DISTINCT (started_at AT TIME ZONE 'UTC')::date) FILTER(WHERE started_at>=ps.captured_at-interval '30 days')::int "activeDays"
        FROM sessions WHERE game_id=ps.game_id AND player_id=ps.player_id AND started_at<=ps.captured_at) st
      CROSS JOIN LATERAL(SELECT coalesce(sum(unit_price_robux*quantity),0)::double precision robux,
        coalesce(sum(quantity),0)::int purchases FROM product_sales
        WHERE game_id=ps.game_id AND player_id=ps.player_id AND purchased_at<=ps.captured_at AND unit_price_robux>0) sp
      WHERE ps.game_id=%s AND ps.player_id=%s ORDER BY ps.captured_at,ps.id""",(gid,pid))
    supported={'sessions','playtime','avgSession','medianSession','activeDays','robuxPerHour','robux','purchases'}
    graph_fields=[dict(f) for f in fields if f['source']=='snapshot' or f['key'] in supported]
    for key,title,fmt in [('sessions','Sessions','number'),('playtime','Lifetime playtime','duration'),
                          ('avgSession','Average session','duration'),('medianSession','Median session','duration'),
                          ('activeDays','Active days (30d)','number'),('robux','Robux spent','robux'),
                          ('purchases','Products bought','number'),('robuxPerHour','Robux per playtime hour','decimal')]:
        if not any(f['key']==key for f in graph_fields):
            graph_fields.append({'key':key,'label':title,'format':fmt,'source':'derived'})
    known={f['key'] for f in graph_fields}
    for key in sorted({key for s in snapshots for key,value in s['data'].items() if type(value) in (int,float)}-known):
        graph_fields.append({'key':key,'label':key,'format':'decimal','source':'snapshot'})
    for point in history:
        point['values']=dict(point['data'])
        point['values'].update({key:point.get(key) for key in supported if key!='robuxPerHour'})
        point['values']['robuxPerHour']=point['robux']*3600/point['playtime'] if point['playtime'] else None
    selected=[params.get('value',['cash'])[0],params.get('value2',[''])[0]]
    keys=list(dict.fromkeys(key for key in selected if key))
    by_key={f['key']:f for f in graph_fields}
    if not keys or any(key not in by_key for key in keys): raise ApiError(400,'Unknown player history value.')
    return {'snapshots':snapshots,'historyFields':graph_fields,
      'labels':[s['captured_at'].strftime('%d %b %Y %H:%M UTC') for s in history],
      'series':[{'name':by_key[key]['label'],'key':key,'format':by_key[key]['format'],
                 'values':[s['values'].get(key) for s in history]} for key in keys]}


def player_detail(conn,gid,pid,params):
    record=one(conn,"SELECT * FROM player_statistics WHERE game_id=%s AND id=%s",(gid,pid))
    if not record: raise ApiError(404,"Player not found in this game.")
    fields,_=fields_templates(conn,gid)
    history=player_history(conn,gid,pid,fields,params)
    history.pop('snapshots',None)
    pages={kind:record_page(conn,gid,pid,kind,{}) for kind in ('snapshots','sessions','activities','events','purchases')}
    peers=all_players(conn,gid)
    groups=[title for key,title in [('whales','Whale'),('playtime','Top playtime'),('first','First-time')]
            if any(p['playerId']==pid for p in segment_players(peers,key))]
    return {"player":player_values(record),"fields":fields,"groups":groups,"recordPages":pages,
      "snapshots":pages['snapshots']['records'],"sessions":pages['sessions']['records'],"events":pages['events']['records'],
      "activities":pages['activities']['records'],"sales":pages['purchases']['records'],
      **player_inventory(conn,gid,pid),**history}



def dispatch(path,params):
    parts=path.strip('/').split('/')
    with connect() as conn:
        if parts==['api','games']:
            return {"games":[game(conn,r["id"]) for r in rows(conn,"SELECT id FROM games ORDER BY id")]}
        if len(parts)<3 or parts[:2]!=['api','games'] or not parts[2].isdigit(): raise ApiError(404,"Use /api/games/{internal_game_id}/{dataset}.")
        gid=int(parts[2]); root=game(conn,gid)
        base={"gameId":gid,"game":root}
        if len(parts)==3:
            return base|{"datasets":{k:f"/api/games/{gid}/{k}" for k in ('players','sessions','events','metrics','activities','overview','retention','engagement','monetization','gamepasses','prompts','reports','data-quality')}}
        days=integer(params,'days',14,90); route=parts[3]
        if route=='players':
            if len(parts)==6 and parts[4].isdigit(): return base|record_page(conn,gid,int(parts[4]),parts[5],params)
            if len(parts)==5 and parts[4].isdigit(): return base|player_detail(conn,gid,int(parts[4]),params)
            if len(parts)!=4: raise ApiError(404,'Unknown player route.')
            return base|player_list(conn,gid,params)
        if len(parts)!=4: raise ApiError(404,'Unknown dataset route.')
        if route=='activities': return base|activities(conn,gid,params,days)
        if route=='reports': return base|reports_for_game(conn,gid)
        if route=='data-quality': return base|quality_log(conn,gid,params)
        if route=='metrics': return base|root_metrics(conn,gid,days)
        if route=='engagement': return base|engagement(conn,gid,days)
        if route=='prompts': return base|prompt_analytics(conn,gid,days,params.get('source',['all'])[0])
        if route=='monetization': return base|monetization(conn,gid,days)
        if route=='gamepasses': return base|gamepasses(conn,gid)
        if route=='events': return base|event_analytics(conn,gid,days)
        if route not in ('overview','sessions','events','retention'): raise ApiError(404,'Dataset not found.')
        selected_ids=None
        if route=='sessions' and params.get('segment',['all'])[0]!='all':
            selected_ids=[p['playerId'] for p in segment_players(all_players(conn,gid),params['segment'][0])]
        data=overview(conn,gid,days,selected_ids)
        if route in ('overview','sessions'):
            data['engagement']=engagement(conn,gid,days,selected_ids)
            data['metrics'].update(data['engagement']['metrics'])
            data['segment']=params.get('segment',['all'])[0]
            if route=='overview':
                data['rootMetrics']=root_metrics(conn,gid,days)
                data['healthReports']=reports_for_game(conn,gid)['reports']
        else:
            ret=retention(conn,gid,days); data['metrics']['day7']=ret['day7']
            data.update({k:v for k,v in ret.items() if k not in ('retention','day7')})
        return base|data