#!/usr/bin/env python3
"""
端内购买用户行为分析 V2
口径修正：
  1. 按订单维度：同一用户买L1+L2拆为两条记录
  2. 序章完课 = U0全部五节(L01~L05)完成，取最后一节完成日期
  3. 新增：购课时序章完成到第几节课
"""
import psycopg2
from datetime import datetime, date
from collections import defaultdict, Counter
import os
import csv

PG_PASS = os.environ.get('PG_ONLINE_PASSWORD', "LdfjdjL83h3h3^$&**YGG*")
conn = psycopg2.connect(
    host="bj-postgres-16pob4sg.sql.tencentcdb.com",
    port=28591,
    user="ai_member",
    password=PG_PASS,
    database="vala_bi"
)
cur = conn.cursor()

# U0 序章 chapter_id -> lesson_name
L1_U0_LESSONS = {343: 'L01', 344: 'L02', 345: 'L03', 346: 'L04', 348: 'L05'}
L2_U0_LESSONS = {55: 'L01', 56: 'L02', 57: 'L03', 58: 'L04', 59: 'L05'}

LEVEL_MAP = {
    57: 'L1', 60: 'L1', 63: 'L1',
    31: 'L2', 32: 'L2', 33: 'L2', 54: 'L2',
    61: 'L1+L2',
}

print("=" * 80)
print("端内购买用户行为分析 V2")
print("口径：序章完课 = U0全部五节完成 | 订单维度 | 购课时进度分析")
print("=" * 80)

# ========== Step 1: 端内付费订单（按订单维度） ==========
print("\n[Step 1] 查询端内付费订单...")
cur.execute("""
    SELECT 
        o.id as order_id,
        o.account_id,
        o.goods_id,
        o.pay_success_date,
        o.trade_no
    FROM bi_vala_order o
    JOIN bi_vala_app_account a ON o.account_id = a.id AND a.status = 1
    WHERE o.key_from = 'app-active-h5-0-0'
      AND o.order_status IN (3, 4)
      AND o.pay_success_date IS NOT NULL
    ORDER BY o.pay_success_date
""")

orders = []  # list of {order_id, account_id, goods_id, purchase_date, level}
for row in cur.fetchall():
    order_id, account_id, goods_id, purchase_date, trade_no = row
    level = LEVEL_MAP.get(goods_id)
    if level is None or level == 'L1+L2':
        continue
    if isinstance(purchase_date, datetime):
        purchase_date = purchase_date.date()
    orders.append({
        'order_id': order_id,
        'account_id': account_id,
        'goods_id': goods_id,
        'purchase_date': purchase_date,
        'level': level
    })

print(f"  端内付费订单数: {len(orders)}")
print(f"  - L1 订单: {sum(1 for o in orders if o['level']=='L1')}")
print(f"  - L2 订单: {sum(1 for o in orders if o['level']=='L2')}")

# 统计有多少用户同时买了L1+L2
user_levels = defaultdict(set)
for o in orders:
    user_levels[o['account_id']].add(o['level'])
dual_users = [uid for uid, lvls in user_levels.items() if len(lvls) > 1]
print(f"  同时购买L1+L2的用户: {len(dual_users)}")

# ========== Step 2: 注册时间 ==========
print("\n[Step 2] 查询用户注册时间...")
all_accounts = list(set(o['account_id'] for o in orders))
register_map = {}
batch_size = 500
for i in range(0, len(all_accounts), batch_size):
    batch = all_accounts[i:i+batch_size]
    cur.execute(
        "SELECT id, created_at FROM bi_vala_app_account WHERE id IN %s",
        (tuple(batch),)
    )
    for row in cur.fetchall():
        d = row[1]
        register_map[row[0]] = d.date() if isinstance(d, (datetime, date)) else d

print(f"  找到注册时间: {len(register_map)}")

# ========== Step 3: account_id -> user_id 映射 ==========
print("\n[Step 3] 查询用户角色映射...")
char_map = defaultdict(list)
for i in range(0, len(all_accounts), batch_size):
    batch = all_accounts[i:i+batch_size]
    cur.execute(
        "SELECT account_id, id FROM bi_vala_app_character WHERE account_id IN %s",
        (tuple(batch),)
    )
    for row in cur.fetchall():
        char_map[row[0]].append(row[1])

print(f"  有角色的用户: {sum(1 for uid, chars in char_map.items() if chars)}")

# ========== Step 4: 序章每节课的最早完成时间 ==========
print("\n[Step 4] 查询序章每节课完成时间（8张分表）...")

def query_lesson_completion(cur, user_ids, chapter_ids_dict):
    """
    查询每个 user_id 对每个 chapter_id 的最早完成时间
    返回: {user_id: {chapter_id: earliest_date}}
    """
    result = defaultdict(dict)
    if not user_ids:
        return result
    
    # 先按user_ids批量查询
    all_chapter_ids = list(chapter_ids_dict.keys())
    
    union_parts = []
    for t in range(8):
        union_parts.append(f"""
            SELECT user_id, chapter_id, MIN(created_at) as first_done
            FROM bi_user_chapter_play_record_{t}
            WHERE chapter_id IN ({','.join(map(str, all_chapter_ids))})
              AND play_status = 1
              AND user_id IN %(user_ids)s
            GROUP BY user_id, chapter_id
        """)
    
    sql = " UNION ALL ".join(union_parts)
    sql = f"""
        SELECT user_id, chapter_id, MIN(first_done) as earliest
        FROM ({sql}) sub
        GROUP BY user_id, chapter_id
    """
    
    uid_list = list(user_ids)
    for i in range(0, len(uid_list), 2000):
        batch = tuple(uid_list[i:i+2000])
        cur.execute(sql, {'user_ids': batch})
        for row in cur.fetchall():
            uid, cid, dt = row
            result[uid][cid] = dt.date() if isinstance(dt, (datetime, date)) else dt
    
    return result

# 收集所有user_id
all_user_ids = set()
for acct in all_accounts:
    for uid in char_map.get(acct, []):
        all_user_ids.add(uid)

print(f"  总 user_id 数: {len(all_user_ids)}")

# L1 序章
l1_lesson_data = query_lesson_completion(cur, all_user_ids, L1_U0_LESSONS)
# L2 序章
l2_lesson_data = query_lesson_completion(cur, all_user_ids, L2_U0_LESSONS)

print(f"  L1 序章有完成记录的 user_id: {len(l1_lesson_data)}")
print(f"  L2 序章有完成记录的 user_id: {len(l2_lesson_data)}")

# ========== Step 5: 对每个订单分析序章进度 ==========
print("\n[Step 5] 逐订单分析序章进度...")

def analyze_user_prologue(user_ids, lesson_data, lesson_map):
    """
    汇总用户所有角色在序章各节的完成情况
    返回: {chapter_id: earliest_completion_date} (只包含完成的)
    """
    merged = {}
    for uid in user_ids:
        if uid not in lesson_data:
            continue
        for cid, dt in lesson_data[uid].items():
            if cid not in merged or dt < merged[cid]:
                merged[cid] = dt
    return merged

def get_prologue_status(lesson_completions, lesson_map, purchase_date):
    """
    分析序章完成情况：
    - total_done: 总共完成了几节（不限时间）
    - done_before_purchase: 购课前完成了哪几节
    - done_after_purchase: 购课后完成了哪几节
    - all_five_done: 是否五节全完成
    - all_five_done_date: 五节全完成的日期（最后一节）
    - progress_at_purchase: 购课时的进度（完成了几节）
    """
    total_done = len(lesson_completions)
    
    done_before = {}
    done_after = {}
    for cid, dt in lesson_completions.items():
        if dt <= purchase_date:
            done_before[cid] = dt
        else:
            done_after[cid] = dt
    
    # 是否五节全完成
    all_five_done = total_done == 5
    all_five_done_date = max(lesson_completions.values()) if all_five_done else None
    
    # 购课时完成了几节
    progress_at_purchase = len(done_before)
    
    # 购课时哪些节已完成
    lessons_done_at_purchase = sorted(
        [lesson_map[cid] for cid in done_before],
        key=lambda x: int(x[1:])  # L01, L02, ...
    )
    
    return {
        'total_done': total_done,
        'done_before_count': len(done_before),
        'done_after_count': len(done_after),
        'all_five_done': all_five_done,
        'all_five_done_date': all_five_done_date,
        'progress_at_purchase': progress_at_purchase,
        'lessons_done_at_purchase': lessons_done_at_purchase,
        'done_before': done_before,
        'done_after': done_after,
    }

records = []
for o in orders:
    account_id = o['account_id']
    level = o['level']
    purchase_date = o['purchase_date']
    register_date = register_map.get(account_id)
    
    if register_date is None:
        continue
    
    user_ids = char_map.get(account_id, [])
    lesson_map = L1_U0_LESSONS if level == 'L1' else L2_U0_LESSONS
    lesson_data = l1_lesson_data if level == 'L1' else l2_lesson_data
    
    user_completions = analyze_user_prologue(user_ids, lesson_data, lesson_map)
    status = get_prologue_status(user_completions, lesson_map, purchase_date)
    
    # 注册→购课天数
    reg_to_pur = (purchase_date - register_date).days
    
    # 注册→序章全完成天数
    reg_to_all5 = None
    if status['all_five_done_date']:
        reg_to_all5 = (status['all_five_done_date'] - register_date).days
    
    # 序章全完成→购课天数
    all5_to_pur = None
    if status['all_five_done_date']:
        all5_to_pur = (purchase_date - status['all_five_done_date']).days
    
    records.append({
        'order_id': o['order_id'],
        'account_id': account_id,
        'level': level,
        'register_date': register_date,
        'purchase_date': purchase_date,
        'reg_to_pur': reg_to_pur,
        # 序章进度
        'total_lessons_done': status['total_done'],
        'lessons_done_before_purchase': status['done_before_count'],
        'lessons_done_after_purchase': status['done_after_count'],
        'progress_at_purchase': status['progress_at_purchase'],
        'lessons_at_purchase': ','.join(status['lessons_done_at_purchase']),
        # 五节全完成
        'all_five_done': status['all_five_done'],
        'all_five_done_date': status['all_five_done_date'],
        'reg_to_all5': reg_to_all5,
        'all5_to_pur': all5_to_pur,
    })

print(f"  分析记录数: {len(records)}")

# ========== Step 6: 统计分析 ==========
print(f"\n{'='*80}")
print("【总体概览】")
print(f"{'='*80}")

total_records = len(records)
all5_complete = [r for r in records if r['all_five_done']]
all5_incomplete = [r for r in records if not r['all_five_done']]
l1_records = [r for r in records if r['level'] == 'L1']
l2_records = [r for r in records if r['level'] == 'L2']

print(f"总记录数（订单维度）: {total_records}")
print(f"  - L1 订单: {len(l1_records)}")
print(f"  - L2 订单: {len(l2_records)}")
print(f"  序章五节全完成: {len(all5_complete)}")
print(f"  序章五节未全完成: {len(all5_incomplete)}")

# ========== 购课时序章进度分布 ==========
print(f"\n{'='*80}")
print("【核心指标：购课时序章完成进度】")
print(f"{'='*80}")

for level in ['L1', 'L2', 'ALL']:
    if level == 'ALL':
        data = records
    else:
        data = [r for r in records if r['level'] == level]
    
    if not data:
        continue
    
    print(f"\n[{level}] 购课时完成了序章第几节:")
    progress_dist = Counter(r['progress_at_purchase'] for r in data)
    for p in range(6):  # 0到5
        cnt = progress_dist.get(p, 0)
        pct = cnt / len(data) * 100
        label = f"  完成{p}节(共5节)" if p > 0 else "  完成0节（未开始）"
        bar = '█' * int(pct)
        print(f"  {label:24s}: {cnt:4d} ({pct:5.1f}%) {bar}")
    
    # 具体到哪几节
    print(f"\n[{level}] 购课时已完成哪些课时的分布:")
    lesson_dist = Counter()
    for r in data:
        if r['progress_at_purchase'] == 0:
            lesson_dist['(未完成任何节)'] += 1
        else:
            lesson_dist[r['lessons_at_purchase']] += 1
    for lesson_combo, cnt in lesson_dist.most_common(15):
        pct = cnt / len(data) * 100
        label = f"  {lesson_combo}"
        print(f"  {label:24s}: {cnt:4d} ({pct:5.1f}%)")

# ========== 购课后完成的情况 ==========
print(f"\n{'='*80}")
print("【购课后继续完成序章的情况】")
print(f"{'='*80}")

for level in ['L1', 'L2', 'ALL']:
    if level == 'ALL':
        data = records
    else:
        data = [r for r in records if r['level'] == level]
    
    if not data:
        continue
    
    print(f"\n[{level}]:")
    print(f"  购课时序章已全部完成(5/5): {sum(1 for r in data if r['progress_at_purchase']==5)} ({sum(1 for r in data if r['progress_at_purchase']==5)/len(data)*100:.1f}%)")
    print(f"  购课后续完成序章余下课时: {sum(1 for r in data if r['lessons_done_after_purchase']>0)} ({sum(1 for r in data if r['lessons_done_after_purchase']>0)/len(data)*100:.1f}%)")
    
    # 购课后完成了多少节
    after_dist = Counter(r['lessons_done_after_purchase'] for r in data if r['progress_at_purchase'] < 5)
    if after_dist:
        print(f"  购课时未完成的用户，购课后又完成节数分布:")
        for n in sorted(after_dist.keys()):
            cnt = after_dist[n]
            pct = cnt / sum(1 for r in data if r['progress_at_purchase'] < 5) * 100
            print(f"    又完成{n}节: {cnt} ({pct:.1f}%)")

# ========== 注册→购课天数 ==========
def percentile_vals(vals):
    if not vals:
        return {}
    s = sorted(vals)
    return {
        'P10': s[int(len(s)*0.10)],
        'P25': s[int(len(s)*0.25)],
        'P50': s[int(len(s)*0.50)],
        'P75': s[int(len(s)*0.75)],
        'P90': s[int(len(s)*0.90)],
        'mean': sum(s)/len(s),
        'count': len(s),
    }

print(f"\n{'='*80}")
print("【注册→购课 天数统计】")
print(f"{'='*80}")

for level in ['L1', 'L2', 'ALL']:
    if level == 'ALL':
        data = records
    else:
        data = [r for r in records if r['level'] == level]
    
    stat = percentile_vals([r['reg_to_pur'] for r in data])
    if stat:
        print(f"\n[{level}] 注册→购课:")
        print(f"  样本: {stat['count']}, 中位数: {stat['P50']}天, 均值: {stat['mean']:.1f}天")
        print(f"  P10={stat['P10']}天 | P25={stat['P25']}天 | P75={stat['P75']}天 | P90={stat['P90']}天")

# ========== 序章五节全完成的用户统计 ==========
print(f"\n{'='*80}")
print("【序章五节全完成的用户（限定已完成5/5的用户）】")
print(f"{'='*80}")

all5_l1 = [r for r in all5_complete if r['level']=='L1']
all5_l2 = [r for r in all5_complete if r['level']=='L2']

print(f"\n五节全完成用户: {len(all5_complete)}")
print(f"  L1: {len(all5_l1)}")
print(f"  L2: {len(all5_l2)}")

# 注册→全完成
for name, data in [('L1', all5_l1), ('L2', all5_l2), ('ALL', all5_complete)]:
    vals = [r['reg_to_all5'] for r in data if r['reg_to_all5'] is not None]
    stat = percentile_vals(vals)
    if stat:
        print(f"\n[{name}] 注册→序章五节全完成:")
        print(f"  样本: {stat['count']}, 中位数: {stat['P50']}天, 均值: {stat['mean']:.1f}天")
        print(f"  P10={stat['P10']}天 | P25={stat['P25']}天 | P75={stat['P75']}天 | P90={stat['P90']}天")

# 全完成→购课
for name, data in [('L1', all5_l1), ('L2', all5_l2), ('ALL', all5_complete)]:
    vals = [r['all5_to_pur'] for r in data if r['all5_to_pur'] is not None]
    stat = percentile_vals(vals)
    if stat:
        print(f"\n[{name}] 序章五节全完成→购课:")
        print(f"  样本: {stat['count']}, 中位数: {stat['P50']}天, 均值: {stat['mean']:.1f}天")
        print(f"  P10={stat['P10']}天 | P25={stat['P25']}天 | P75={stat['P75']}天 | P90={stat['P90']}天")

# 全完成→购课时间段分布
print(f"\n【五节全完成→购课 时间段分布】")
print(f"{'='*60}")
buckets = {
    '负数（先购课后完课）': (-99999, -1),
    '0天(当天)': (0, 0),
    '1~3天': (1, 3),
    '4~7天': (4, 7),
    '8~14天': (8, 14),
    '15~30天': (15, 30),
    '31~60天': (31, 60),
    '61~90天': (61, 90),
    '90天以上': (91, 99999),
}
for name, data in [('L1', all5_l1), ('L2', all5_l2), ('ALL', all5_complete)]:
    vals = [r['all5_to_pur'] for r in data if r['all5_to_pur'] is not None]
    if not vals:
        continue
    print(f"\n[{name}]:")
    for label, (lo, hi) in buckets.items():
        cnt = sum(1 for v in vals if lo <= v <= hi)
        pct = cnt / len(vals) * 100
        bar = '█' * int(pct / 2)
        print(f"  {label:24s}: {cnt:4d} ({pct:5.1f}%) {bar}")

# ========== 时序关系 ==========
print(f"\n{'='*80}")
print("【五节全完成的用户：序章 vs 购课 时序关系】")
print(f"{'='*80}")

for name, data in [('L1', all5_l1), ('L2', all5_l2), ('ALL', all5_complete)]:
    pro_before = sum(1 for r in data if r['all5_to_pur'] is not None and r['all5_to_pur'] >= 0)
    pro_after = sum(1 for r in data if r['all5_to_pur'] is not None and r['all5_to_pur'] < 0)
    pro_same = sum(1 for r in data if r['all5_to_pur'] == 0)
    print(f"\n[{name}]:")
    print(f"  序章全部完成在购课之前/当天: {pro_before} ({pro_before/len(data)*100:.1f}%)")
    print(f"    其中当天: {pro_same} ({pro_same/len(data)*100:.1f}%)")
    print(f"  序章全部完成在购课之后: {pro_after} ({pro_after/len(data)*100:.1f}%)")

# ========== 导出CSV ==========
output_path = '/root/.openclaw/workspace/output/endor_purchase_analysis_v2.csv'
with open(output_path, 'w', newline='', encoding='utf-8-sig') as f:
    writer = csv.writer(f)
    writer.writerow([
        'order_id', 'account_id', '等级', '注册日期', '购课日期',
        '注册→购课(天)',
        '序章完成节数(总计)', '购课前进度(节)', '购课后完成(节)',
        '购课时已完成', '五节全完成', '五节全完成日期',
        '注册→全完成(天)', '全完成→购课(天)'
    ])
    for r in sorted(records, key=lambda x: x['purchase_date']):
        writer.writerow([
            r['order_id'], r['account_id'], r['level'],
            r['register_date'].isoformat() if r['register_date'] else '',
            r['purchase_date'].isoformat() if r['purchase_date'] else '',
            r['reg_to_pur'],
            r['total_lessons_done'], r['progress_at_purchase'],
            r['lessons_done_after_purchase'],
            r['lessons_at_purchase'],
            '是' if r['all_five_done'] else '否',
            r['all_five_done_date'].isoformat() if r['all_five_done_date'] else '',
            r['reg_to_all5'] if r['reg_to_all5'] is not None else '',
            r['all5_to_pur'] if r['all5_to_pur'] is not None else '',
        ])

print(f"\n{'='*80}")
print(f"详细数据已导出: {output_path}")
print(f"{'='*80}")

cur.close()
conn.close()
print("\n分析完成!")
