"""03_partial_expression_index.py —— 部分索引 + 表达式索引

场景：
  1) 软删除表 ch6_users：只为未删除的行建部分索引
  2) 大小写不敏感登录：LOWER(email) 表达式索引
  3) 未完成订单：部分索引 WHERE status IN (...)
"""
from __future__ import annotations

from _common import connect, explain_analyze, section, summarize_plan


def demo_partial_on_users(cur) -> None:
    section("1. 部分索引：WHERE deleted_at IS NULL")
    cur.execute("DROP INDEX IF EXISTS idx_ch6_users_active_email")

    # 先看无索引时的执行计划
    p = summarize_plan(explain_analyze(
        cur,
        "SELECT * FROM ch6_users WHERE email = %s AND deleted_at IS NULL",
        ("user1234@example.com",),
    ))
    print(f"  无索引:      node={p['node']}  actual={p['actual_ms']}ms")

    cur.execute(
        "CREATE INDEX idx_ch6_users_active_email ON ch6_users(email) "
        "WHERE deleted_at IS NULL"
    )
    cur.execute("ANALYZE ch6_users")

    # 查询条件必须「蕴含」部分索引的条件，才能走上
    p = summarize_plan(explain_analyze(
        cur,
        "SELECT * FROM ch6_users WHERE email = %s AND deleted_at IS NULL",
        ("user1234@example.com",),
    ))
    print(f"  命中部分索引: node={p['node']}  actual={p['actual_ms']}ms")

    # 没带 deleted_at 条件时是否能命中？—— 不能，因为无法证明满足部分索引
    p = summarize_plan(explain_analyze(
        cur,
        "SELECT * FROM ch6_users WHERE email = %s",
        ("user1234@example.com",),
    ))
    print(f"  没带 deleted_at 条件: node={p['node']}  actual={p['actual_ms']}ms")

    # 查索引大小，应该比全量索引小 20 倍
    cur.execute("SELECT pg_size_pretty(pg_relation_size('idx_ch6_users_active_email'))")
    (sz,) = cur.fetchone()
    print(f"  部分索引体积: {sz}  (只索引 95% 未删除行)")


def demo_expression_on_users(cur) -> None:
    section("2. 表达式索引：LOWER(email)")
    cur.execute("DROP INDEX IF EXISTS idx_ch6_users_email_lower")

    SQL = "SELECT * FROM ch6_users WHERE LOWER(email) = %s"
    p = summarize_plan(explain_analyze(cur, SQL, ("user9999@example.com",)))
    print(f"  无索引:       node={p['node']}  actual={p['actual_ms']}ms")

    cur.execute("CREATE INDEX idx_ch6_users_email_lower ON ch6_users (LOWER(email))")
    cur.execute("ANALYZE ch6_users")

    p = summarize_plan(explain_analyze(cur, SQL, ("user9999@example.com",)))
    print(f"  表达式索引:   node={p['node']}  actual={p['actual_ms']}ms")

    # 反面教材：用 B-Tree(email) 时，LOWER() 包裹后无法使用
    cur.execute("DROP INDEX idx_ch6_users_email_lower")
    cur.execute("CREATE INDEX idx_ch6_users_email ON ch6_users (email)")
    cur.execute("ANALYZE ch6_users")

    p = summarize_plan(explain_analyze(cur, SQL, ("user9999@example.com",)))
    print(f"  有(email)索引但被 LOWER() 破坏: node={p['node']}  actual={p['actual_ms']}ms")
    cur.execute("DROP INDEX idx_ch6_users_email")


def demo_partial_on_orders(cur) -> None:
    section("3. 部分索引：未完成订单（status IN (pending,paid)）")
    cur.execute("DROP INDEX IF EXISTS idx_ch6_orders_open")
    cur.execute(
        "CREATE INDEX idx_ch6_orders_open ON ch6_orders (user_id, created_at) "
        "WHERE status IN ('pending', 'paid')"
    )
    cur.execute("ANALYZE ch6_orders")

    p = summarize_plan(explain_analyze(cur,
        "SELECT id FROM ch6_orders WHERE status IN ('pending','paid') "
        "AND user_id = %s ORDER BY created_at DESC LIMIT 10",
        (42,),
    ))
    print(f"  命中部分索引: node={p['node']}  actual={p['actual_ms']}ms")

    cur.execute("SELECT pg_size_pretty(pg_relation_size('idx_ch6_orders_open'))")
    (sz,) = cur.fetchone()
    print(f"  索引体积: {sz}  (只索引约 40% 未完成订单)")

    cur.execute("DROP INDEX idx_ch6_orders_open")


def main() -> None:
    with connect() as conn:
        with conn.cursor() as cur:
            demo_partial_on_users(cur)
            demo_expression_on_users(cur)
            demo_partial_on_orders(cur)
            conn.commit()


if __name__ == "__main__":
    main()