#!/usr/bin/env python3
"""
最近14天无完课行为的付费用户占比分析（剔除退费订单版）
口径：
  - 最近14天: 2026-05-08 ~ 2026-05-21（14个完整天）
  - 付费用户: 剔除退费订单后至少还有1笔有效订单的非测试账号
    退费判定: bi_refund_order.status=3 AND bi_vala_order.order_status=4
  - 完课行为: bi_user_chapter_play_record.play_status=1
  - L1/L2分类: 基于剔除退费后的有效订单 goods_id
"""
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"  # exclusive

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("=" * 60)
print("📊 最近14天(2026-05-08~05-21)无完课行为付费用户占比")
print("   [剔除退费订单版]")
print("=" * 60)

# ── Step 1: 获取有效付费用户（剔除退费订单）及L1/L2分类 ──
print("\n[1/3] 查询有效付费用户（剔除退费订单）...")

cur.execute("""
    -- 退费订单号
    WITH refunded_orders AS (
        SELECT DISTINCT r.trade_no
        FROM bi_refund_order r
        JOIN bi_vala_order o ON r.trade_no = o.trade_no
        WHERE r.status = 3
          AND o.order_status = 4
    ),
    -- 有效订单（非退费）
    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 IN (3, 4)
          AND o.pay_success_date IS NOT NULL
          AND o.trade_no NOT IN (SELECT trade_no FROM refunded_orders)
    ),
    -- 用户等级分类
    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()}
total_paying = len(paying_users)
print(f"   有效付费用户总数: {total_paying}")

l1_only = sum(1 for v in paying_users.values() if v == '仅L1')
l2_only = sum(1 for v in paying_users.values() if v == '仅L2')
l1l2 = sum(1 for v in paying_users.values() if v == 'L1+L2')
other = sum(1 for v in paying_users.values() if v == '其他')
print(f"   仅L1: {l1_only} | 仅L2: {l2_only} | L1+L2: {l1l2} | 其他: {other}")

# ── Step 2: 查询最近14天有完课行为的用户 ──
print("\n[2/3] 查询最近14天有完课行为的用户...")

account_ids = list(paying_users.keys())
char_map = {}  # account_id -> [character_ids]
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():
        if acc_id not in char_map:
            char_map[acc_id] = []
        char_map[acc_id].append(char_id)

all_char_ids = []
for chars in char_map.values():
    all_char_ids.extend(chars)
print(f"   角色总数: {len(all_char_ids)}")

# 查完课行为 - 分8表UNION ALL
learned_chars = set()
for shard in range(8):
    table = f"bi_user_chapter_play_record_{shard}"
    cur.execute(f"""
        SELECT DISTINCT user_id
        FROM {table}
        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_accounts = set(paying_users.keys()) - learned_accounts
print(f"   有完课行为的付费用户: {len(learned_accounts)}")
print(f"   无完课行为的付费用户: {len(no_learn_accounts)}")

# ── Step 3: 分L1/L2统计 ──
print("\n[3/3] 汇总统计...")

def stats(uset):
    total = len(uset)
    nl = len(uset & no_learn_accounts)
    l = total - nl
    r = nl / total * 100 if total > 0 else 0
    return total, nl, l, r

all_users = 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'}
l1l2_set = {k for k, v in paying_users.items() if v == 'L1+L2'}
other_set = {k for k, v in paying_users.items() if v == '其他'}
l1_related = l1_set | l1l2_set
l2_related = l2_set | l1l2_set

print("\n" + "=" * 60)
print("📊 结果汇总（剔除退费订单）")
print("=" * 60)
print(f"{'类别':<18} {'总付费用户':>10} {'无完课':>8} {'无完课占比':>10} {'有完课':>8}")
print("-" * 60)

for label, uset in [("总体", all_users), ("仅L1", l1_set), ("仅L2", l2_set), ("L1+L2", l1l2_set), ("其他", other_set), ("L1相关(含L1+L2)", l1_related), ("L2相关(含L1+L2)", l2_related)]:
    t, nl, l, r = stats(uset)
    print(f"{label:<18} {t:>10,} {nl:>8,} {r:>9.1f}% {l:>8,}")

# 对比剔除前后的差异
print("\n" + "=" * 60)
print("📊 剔除退费前后对比")
print("=" * 60)
print(f"{'指标':<18} {'剔除前':>10} {'剔除后':>10} {'差异':>10}")
print("-" * 60)
# 之前的结果
prev_total = 4367
prev_l1 = 249
prev_l2 = 2090
prev_l1l2 = 2021
prev_other = 7
print(f"{'总付费用户':<18} {prev_total:>10,} {total_paying:>10,} {total_paying-prev_total:>+10,}")
print(f"{'仅L1':<18} {prev_l1:>10,} {l1_only:>10,} {l1_only-prev_l1:>+10,}")
print(f"{'仅L2':<18} {prev_l2:>10,} {l2_only:>10,} {l2_only-prev_l2:>+10,}")
print(f"{'L1+L2':<18} {prev_l1l2:>10,} {l1l2:>10,} {l1l2-prev_l1l2:>+10,}")
print(f"{'其他':<18} {prev_other:>10,} {other:>10,} {other-prev_other:>+10,}")

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