import json
import sys

sys.path.insert(0, '/www/server/xingyu-im/ops')
from baota_publish_test_users import database, env_values, query

conn, _ = database(env_values())
try:
    rows = query(conn, """SELECT u.is_test,
        COUNT(*) AS active_users,
        SUM(privacy.nearby_visible=1) AS nearby_visible,
        SUM(privacy.distance_visible=1) AS distance_visible,
        SUM(location.user_id IS NOT NULL) AS has_location,
        SUM(privacy.nearby_visible=1 AND privacy.distance_visible=1 AND location.user_id IS NOT NULL) AS distance_ready
        FROM users u JOIN user_privacy_settings privacy ON privacy.user_id=u.id
        LEFT JOIN user_location_states location ON location.user_id=u.id
        WHERE u.status=1 AND u.deleted_at IS NULL GROUP BY u.is_test ORDER BY u.is_test""")
    print(json.dumps({'coverage': rows}, default=str))
finally:
    conn.close()