#!/usr/bin/env python3
"""
最近14天无完课行为的付费用户占比分析
口径：
  - 最近14天: 2026-05-08 ~ 2026-05-21
  - 付费用户: order_status=3 的非测试账号
  - 完课行为: bi_user_chapter_play_record.play_status=1
"""
import os, sys
sys.path.insert(0, "/root/.openclaw/workspace")
import psycopg2

SECRETS_FILE = "/root/.openclaw/workspace/secrets.env"
DB_HOST = "bj-postgres-16pob4sg.sql.tencentcdb.com"
DB_PORT = "28591"
DB_USER = "ai_member"
DB_NAME = "vala_bi"
DATE_START = "2026-05-08"
DATE_END = "2026-05-22"

def load_pw():
    with open(SECRETS_FILE) as f:
        for line in f:
            if line.startswith("PG_ONLINE_PASSWORD="):
                return line.split("=", 1)[1].strip().strip("'\"")

pw = load_pw()
conn = psycopg2.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, password=pw, dbname=DB_NAME)
cur = conn.cursor()

print("[1/3] 查询付费用户及等级...")

cur.execute("""
    WITH valid_orders AS (
        SELECT o.account_id, o.goods_id
        FROM bi_vala_order o
        JOIN bi_vala_app_account a ON o.account_id = a.id AND a.status = 1 AND a.deleted_at IS NULL
        WHERE o.order_status = 3
          AND o.pay_success_date IS NOT NULL
    ),
    user_level_raw AS (
        SELECT 
            account_id,
            BOOL_OR(goods_id = 61) AS has_both,
            BOOL_OR(goods_id IN (57, 60, 63)) AS has_l1,
            BOOL_OR(goods_id IN (31, 32, 33, 54)) AS has_l2,
            BOOL_OR(goods_id IN (4,5,6,10,13,14,17,20,25,29,30,35,36,37,38)) AS has_other
        FROM valid_orders
        GROUP BY account_id
    )
    SELECT 
        account_id,
        CASE
            WHEN has_both OR (has_l1 AND has_l2) THEN 'L1+L2'
            WHEN has_l1 AND NOT has_l2 THEN '仅L1'
            WHEN has_l2 AND NOT has_l1 THEN '仅L2'
            ELSE '其他'
        END AS level
    FROM user_level_raw
""")
paying_users = {row[0]: row[1] for row in cur.fetchall()}
print(f"   付费用户: {len(paying_users)}")
print(f"   仅L1: {sum(1 for v in paying_users.values() if v=='仅L1')} | 仅L2: {sum(1 for v in paying_users.values() if v=='仅L2')} | L1+L2: {sum(1 for v in paying_users.values() if v=='L1+L2')} | 其他: {sum(1 for v in paying_users.values() if v=='其他')}")

print("[2/3] 查询完课行为...")
account_ids = list(paying_users.keys())
char_map = {}
for i in range(0, len(account_ids), 500):
    batch = account_ids[i:i+500]
    cur.execute("""
        SELECT account_id, id FROM bi_vala_app_character 
        WHERE account_id = ANY(%s) AND deleted_at IS NULL
    """, (batch,))
    for acc_id, char_id in cur.fetchall():
        char_map.setdefault(acc_id, []).append(char_id)

all_char_ids = []
for chars in char_map.values():
    all_char_ids.extend(chars)

learned_chars = set()
for shard in range(8):
    cur.execute(f"""
        SELECT DISTINCT user_id
        FROM bi_user_chapter_play_record_{shard}
        WHERE play_status = 1 AND created_at >= %s AND created_at < %s AND user_id = ANY(%s)
    """, (DATE_START, DATE_END, all_char_ids))
    for (uid,) in cur.fetchall():
        learned_chars.add(uid)

learned_accounts = set()
for acc_id, chars in char_map.items():
    if any(c in learned_chars for c in chars):
        learned_accounts.add(acc_id)

no_learn = set(paying_users.keys()) - learned_accounts
print(f"   有完课: {len(learned_accounts)} | 无完课: {len(no_learn)}")

print("[3/3] 汇总...")
def stats(uset):
    t = len(uset)
    nl = len(uset & no_learn)
    l = t - nl
    return t, nl, l, nl/t*100 if t else 0

all_u = set(paying_users.keys())
l1_set = {k for k,v in paying_users.items() if v=='仅L1'}
l2_set = {k for k,v in paying_users.items() if v=='仅L2'}
l12_set = {k for k,v in paying_users.items() if v=='L1+L2'}
o_set = {k for k,v in paying_users.items() if v=='其他'}

for label, uset in [("总体", all_u), ("仅L1", l1_set), ("仅L2", l2_set), ("L1+L2", l12_set), ("其他", o_set), ("L1相关", l1_set|l12_set), ("L2相关", l2_set|l12_set)]:
    t, nl, l, r = stats(uset)
    print(f"  {label:<14} 总{t:>6,}  无完课{nl:>6,} ({r:>5.1f}%)  有完课{l:>6,}")

cur.close()
conn.close()
print("✅ 完成")
