#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
第 19 章 综合实战：漏斗查询 Demo
--------------------------------
演示 windowFunnel 的两种查法：
  (A) 直接扫 events_raw（适合偶尔查）
  (B) 走 events_agg_funnel 聚合表 + windowFunnelMerge（适合高频查）
"""
import argparse
import time
import clickhouse_connect


def header(title: str) -> None:
    print(f'\n{"=" * 68}\n  {title}\n{"=" * 68}')


def run(cli, sql: str, label: str):
    t0 = time.time()
    rs = cli.query(sql)
    dt = (time.time() - t0) * 1000
    print(f'[{label}]  {dt:7.1f} ms')
    return rs


def print_funnel(result, title):
    if not result.result_rows:
        print(f'  {title}: empty')
        return
    r = result.result_rows[0]
    v, c, o, p = r[:4]
    print(f'  {title}:')
    print(f'    view     = {v:>10,}')
    print(f'    add_cart = {c:>10,}   转化={c/max(v,1)*100:6.2f}%')
    print(f'    order    = {o:>10,}   转化={o/max(c,1)*100:6.2f}%')
    print(f'    pay      = {p:>10,}   转化={p/max(o,1)*100:6.2f}%')


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument('--host', default='127.0.0.1')
    ap.add_argument('--port', default=8123, type=int)
    ap.add_argument('--user', default='default')
    ap.add_argument('--password', default='')
    ap.add_argument('--db', default='learn_ck')
    ap.add_argument('--window', default=3600, type=int, help='窗口秒数')
    args = ap.parse_args()

    cli = clickhouse_connect.get_client(
        host=args.host, port=args.port,
        username=args.user, password=args.password,
        database=args.db,
    )

    header(f'(A) 直接扫 events_raw，windowFunnel({args.window}s)')
    rs = run(cli, f"""
        WITH t AS (
            SELECT
                user_id,
                windowFunnel({args.window})(
                    event_time,
                    event_type = 'view',
                    event_type = 'add_cart',
                    event_type = 'order',
                    event_type = 'pay'
                ) AS step
            FROM events_raw
            WHERE event_date >= today() - 2
            GROUP BY user_id
        )
        SELECT
            countIf(step >= 1) AS step_view,
            countIf(step >= 2) AS step_cart,
            countIf(step >= 3) AS step_order,
            countIf(step >= 4) AS step_pay
        FROM t
    """, 'raw_funnel')
    print_funnel(rs, '最近 2 天')

    header(f'(B) 走聚合表 events_agg_funnel，windowFunnelMerge({args.window}s)')
    rs = run(cli, f"""
        WITH t AS (
            SELECT
                user_id,
                windowFunnelMerge({args.window})(funnel_state) AS step
            FROM events_agg_funnel
            WHERE event_date >= today() - 2
            GROUP BY user_id
        )
        SELECT
            countIf(step >= 1) AS step_view,
            countIf(step >= 2) AS step_cart,
            countIf(step >= 3) AS step_order,
            countIf(step >= 4) AS step_pay
        FROM t
    """, 'agg_funnel')
    print_funnel(rs, '最近 2 天（聚合表）')

    header('(C) 按渠道拆分漏斗（聚合表）')
    rs = run(cli, f"""
        WITH t AS (
            SELECT
                channel,
                user_id,
                windowFunnelMerge({args.window})(funnel_state) AS step
            FROM events_agg_funnel
            WHERE event_date >= today() - 2
            GROUP BY channel, user_id
        )
        SELECT
            channel,
            countIf(step >= 1) AS s1,
            countIf(step >= 2) AS s2,
            countIf(step >= 3) AS s3,
            countIf(step >= 4) AS s4,
            round(s2 / greatest(s1, 1) * 100, 2) AS r12,
            round(s3 / greatest(s2, 1) * 100, 2) AS r23,
            round(s4 / greatest(s3, 1) * 100, 2) AS r34
        FROM t
        GROUP BY channel
        ORDER BY s1 DESC
    """, 'by_channel')
    print(f"  {'ch':<6}  {'view':>8}  {'cart':>7}  {'ord':>7}  {'pay':>6}  {'V→C':>7} {'C→O':>7} {'O→P':>7}")
    for r in rs.result_rows:
        print(f'  {r[0]:<6}  {r[1]:>8}  {r[2]:>7}  {r[3]:>7}  {r[4]:>6}  {r[5]:>6}% {r[6]:>6}% {r[7]:>6}%')


if __name__ == '__main__':
    main()