"""
第 4 章 表引擎全景图 · Python 演示

依赖：
    pip install clickhouse-connect

运行：
    python engine_play.py

它会：
    1. 连接本地 ClickHouse（127.0.0.1:8123, default 用户，无密码）
    2. 在 learn_ck 库里依次建/插/查 5 种引擎的样表
    3. 演示 Memory / File / URL / MergeTree / Merge 五种引擎的差异

如需重置环境：
    DROP DATABASE IF EXISTS learn_ck;
"""

from __future__ import annotations

import sys
import textwrap
from typing import Iterable

import clickhouse_connect


HOST = "127.0.0.1"
PORT = 8123
USER = "default"
PASSWORD = ""
DATABASE = "learn_ck"


def banner(title: str) -> None:
    print()
    print("=" * 70)
    print(f"  {title}")
    print("=" * 70)


def show(client, sql: str) -> None:
    print(textwrap.dedent(f">>> {sql}").strip())
    try:
        result = client.query(sql)
        rows = result.result_rows
        cols = result.column_names
        if rows:
            widths = [
                max(len(str(c)), max((len(str(r[i])) for r in rows), default=0))
                for i, c in enumerate(cols)
            ]
            sep = "+".join("-" * (w + 2) for w in widths)
            print(sep)
            print("|" + "|".join(f" {c:<{w}} " for c, w in zip(cols, widths)) + "|")
            print(sep)
            for r in rows:
                print(
                    "|"
                    + "|".join(f" {str(v):<{w}} " for v, w in zip(r, widths))
                    + "|"
                )
            print(sep)
        else:
            print("(no rows)")
    except Exception as e:
        print(f"!! error: {e}")


def execute(client, sqls: Iterable[str]) -> None:
    for sql in sqls:
        sql = sql.strip()
        if not sql:
            continue
        try:
            client.command(sql)
            print(f"OK  {sql.splitlines()[0][:80]} ...")
        except Exception as e:
            print(f"!!  {sql.splitlines()[0][:80]} -> {e}")


def main() -> int:
    client = clickhouse_connect.get_client(
        host=HOST, port=PORT, username=USER, password=PASSWORD
    )
    print(f"Connected to ClickHouse at {HOST}:{PORT} as {USER}")

    banner("0. 准备数据库 learn_ck")
    execute(client, [f"CREATE DATABASE IF NOT EXISTS {DATABASE}"])
    client.database = DATABASE

    banner("1. Memory 引擎：内存里的临时表")
    execute(
        client,
        [
            "DROP TABLE IF EXISTS demo_memory",
            """
            CREATE TABLE demo_memory
            (
                id   UInt32,
                name String,
                ts   DateTime DEFAULT now()
            ) ENGINE = Memory
            """,
            "INSERT INTO demo_memory(id, name) VALUES "
            "(1, 'Alice'), (2, 'Bob'), (3, 'Carol')",
        ],
    )
    show(client, "SELECT engine FROM system.tables WHERE name = 'demo_memory'")
    show(client, "SELECT * FROM demo_memory ORDER BY id")

    banner("2. URL 引擎：远程 JSON 文件即表（需要外网）")
    execute(
        client,
        [
            "DROP TABLE IF EXISTS demo_url",
            """
            CREATE TABLE demo_url
            (
                id    UInt32,
                name  String,
                email String
            ) ENGINE = URL('https://jsonplaceholder.typicode.com/users',
                           JSONEachRow)
            """,
        ],
    )
    show(client, "SELECT id, name, email FROM demo_url ORDER BY id LIMIT 5")

    banner("3. MergeTree 主表 + 两张子表")
    execute(
        client,
        [
            "DROP TABLE IF EXISTS demo_mt",
            "DROP TABLE IF EXISTS demo_mt_2024_01",
            "DROP TABLE IF EXISTS demo_mt_2024_02",
            """
            CREATE TABLE demo_mt
            (
                event_date  Date,
                user_id     UInt64,
                event_name  LowCardinality(String),
                revenue     Decimal(18, 2)
            ) ENGINE = MergeTree
            PARTITION BY toYYYYMM(event_date)
            ORDER BY (user_id, event_date)
            """,
            "CREATE TABLE demo_mt_2024_01 AS demo_mt ENGINE = MergeTree "
            "PARTITION BY toYYYYMM(event_date) ORDER BY (user_id, event_date)",
            "CREATE TABLE demo_mt_2024_02 AS demo_mt ENGINE = MergeTree "
            "PARTITION BY toYYYYMM(event_date) ORDER BY (user_id, event_date)",
            "INSERT INTO demo_mt_2024_01 VALUES "
            "('2024-01-01',1001,'click',1.50),"
            "('2024-01-02',1002,'purchase',99.00),"
            "('2024-01-03',1001,'click',0.80)",
            "INSERT INTO demo_mt_2024_02 VALUES "
            "('2024-02-01',1003,'click',2.10),"
            "('2024-02-02',1001,'purchase',49.99)",
        ],
    )
    show(client, "SELECT name, engine FROM system.tables "
                 "WHERE name LIKE 'demo_mt%' ORDER BY name")

    banner("4. Merge 引擎：把两张子表「叠」成一张虚拟表")
    execute(
        client,
        [
            "DROP TABLE IF EXISTS demo_merge",
            "CREATE TABLE demo_merge AS demo_mt "
            "ENGINE = Merge(learn_ck, '^demo_mt_2024_')",
        ],
    )
    show(client, "SELECT count() AS rows FROM demo_merge")
    show(
        client,
        "SELECT _table, count() FROM demo_merge "
        "GROUP BY _table ORDER BY _table",
    )

    banner("5. Null 引擎：黑洞表（写进去就丢，可触发 MV）")
    execute(
        client,
        [
            "DROP TABLE IF EXISTS demo_null",
            """
            CREATE TABLE demo_null
            (
                user_id UInt64,
                event   String,
                ts      DateTime
            ) ENGINE = Null
            """,
            "INSERT INTO demo_null VALUES (1, 'foo', now()), (2, 'bar', now())",
        ],
    )
    show(client, "SELECT count() AS still_zero FROM demo_null")

    banner("6. 全表汇总：看看本库都有哪些引擎")
    show(
        client,
        "SELECT name, engine FROM system.tables "
        f"WHERE database = '{DATABASE}' ORDER BY name",
    )

    print("\nAll done. Try `clickhouse-client --query \"SHOW TABLES FROM learn_ck\"`.")
    return 0


if __name__ == "__main__":
    sys.exit(main())