"""Lightweight product-prompt measurements, separate from activity sessions."""
def prompt_analytics(conn,gid,days,source='all'):
    if source not in ('all','game','player'):
        from sample_api import ApiError
        raise ApiError(400,'Unknown prompt source.')
    products=conn.execute('''SELECT p.id,p.name,p.roblox_product_id,count(i.id)::int prompts,
      count(i.id) FILTER(WHERE i.outcome='bought')::int bought,
      count(i.id) FILTER(WHERE i.outcome='closed')::int closed,
      count(i.id) FILTER(WHERE i.outcome IS NULL)::int unresolved,
      avg(extract(epoch FROM i.ended_at-i.started_at)) FILTER(WHERE i.outcome='bought')::double precision buy_time,
      avg(extract(epoch FROM i.ended_at-i.started_at)) FILTER(WHERE i.outcome='closed')::double precision close_time
      FROM products p LEFT JOIN interaction_events i ON i.game_id=p.game_id AND i.product_id=p.id AND i.kind='product_prompt'
      AND i.outcome IN ('bought','closed') AND i.ended_at IS NOT NULL AND i.trigger_source IN ('game','player')
      AND i.started_at>=current_date-(%s-1) AND (%s='all' OR i.trigger_source=%s)
      WHERE p.game_id=%s GROUP BY p.id ORDER BY p.id''',(days,source,source,gid)).fetchall()
    for row in products: row['conversion']=100*row['bought']/row['prompts'] if row['prompts'] else None
    daily=conn.execute('''SELECT (started_at AT TIME ZONE 'UTC')::date AS day,product_id,count(*)::int prompts,
      count(*) FILTER(WHERE outcome='bought')::int bought,
      avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='bought')::double precision buy_time,
      avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='closed')::double precision close_time
      FROM interaction_events WHERE game_id=%s AND kind='product_prompt'
      AND outcome IN ('bought','closed') AND ended_at IS NOT NULL AND trigger_source IN ('game','player') AND started_at>=current_date-(%s-1)
      AND (%s='all' OR trigger_source=%s) GROUP BY day,product_id ORDER BY day''',(gid,days,source,source)).fetchall()
    totals=conn.execute('''SELECT count(*)::int prompts,count(*) FILTER(WHERE outcome='bought')::int bought,
      avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='bought')::double precision buy_time,
      avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='closed')::double precision close_time
      FROM interaction_events WHERE game_id=%s AND kind='product_prompt'
      AND outcome IN ('bought','closed') AND ended_at IS NOT NULL AND trigger_source IN ('game','player') AND started_at>=current_date-(%s-1)
      AND (%s='all' OR trigger_source=%s)''',(gid,days,source,source)).fetchone()
    totals['conversion']=100*totals['bought']/totals['prompts'] if totals['prompts'] else None
    from datetime import datetime,timedelta,timezone
    dates=[datetime.now(timezone.utc).date()-timedelta(days=i) for i in reversed(range(days))]
    lookup={(r['product_id'],r['day']):r for r in daily}
    series=[]
    for p in products:
        values=[]
        for day in dates:
            row=lookup.get((p['id'],day))
            values.append(100*row['bought']/row['prompts'] if row and row['prompts'] else None)
        series.append({'name':p['name'],'format':'percent','values':values})
    grouped=conn.execute('''SELECT trigger_source source,count(*)::int prompts,
      count(*) FILTER(WHERE outcome='bought')::int bought,count(*) FILTER(WHERE outcome='closed')::int closed,
      count(*) FILTER(WHERE outcome IS NULL)::int unresolved,
      avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='bought')::double precision buy_time,
      avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='closed')::double precision close_time
      FROM interaction_events WHERE game_id=%s AND kind='product_prompt'
      AND outcome IN ('bought','closed') AND ended_at IS NOT NULL AND trigger_source IN ('game','player') AND started_at>=current_date-(%s-1)
      GROUP BY trigger_source''',(gid,days)).fetchall()
    labels={'game':'Game-induced','player':'Player-induced'}
    by_source={r['source']:r for r in grouped}
    comparison=[]
    for key,name in labels.items():
        row=by_source.get(key,{'source':key,'prompts':0,'bought':0,'closed':0,'unresolved':0,'buy_time':None,'close_time':None})
        row['name']=name;row['conversion']=100*row['bought']/row['prompts'] if row['prompts'] else None
        comparison.append(row)
    source_days=conn.execute('''SELECT (started_at AT TIME ZONE 'UTC')::date AS day,trigger_source source,
      count(*)::int prompts,count(*) FILTER(WHERE outcome='bought')::int bought
      FROM interaction_events WHERE game_id=%s AND kind='product_prompt'
      AND outcome IN ('bought','closed') AND ended_at IS NOT NULL AND trigger_source IN ('game','player') AND started_at>=current_date-(%s-1)
      GROUP BY day,trigger_source''',(gid,days)).fetchall()
    source_lookup={(r['source'],r['day']):r for r in source_days}
    source_series=[]
    for key,name in labels.items():
        values=[]
        for day in dates:
            row=source_lookup.get((key,day))
            values.append(100*row['bought']/row['prompts'] if row and row['prompts'] else None)
        source_series.append({'name':name,'format':'percent','values':values})
    return {'metrics':totals,'products':products,'source':source,'sourceComparison':comparison,
            'sourceSeries':source_series,'series':series,'labels':[d.strftime('%d %b') for d in dates]}