"""快速诊断 SPI 溢出值"""
import os, math
from pathlib import Path
from dotenv import load_dotenv
import psycopg2

load_dotenv(Path(__file__).resolve().parents[2] / ".env")
dsn = os.environ["PG_DSN"]
conn = psycopg2.connect(dsn, connect_timeout=5)
cur = conn.cursor()
sid = 2790685415443269

# 查消费特征极值
cur.execute("""
    SELECT MAX(spend_30), MAX(spend_90), MAX(avg_ticket_90),
           MAX(daily_spend_ewma_90), MAX(recharge_90)
    FROM dws.dws_member_consumption WHERE site_id = %s
""", (sid,))
r = cur.fetchone()
print(f"MAX: spend_30={r[0]}, spend_90={r[1]}, avg_ticket={r[2]}, ewma={r[3]}, recharge={r[4]}")

# 模拟最大 level score
s30 = float(r[0] or 0)
s90 = float(r[1] or 0)
tk = float(r[2] or 0)
ewma = float(r[3] or 0)
rch = float(r[4] or 0)

level = (0.30 * math.log1p(s30/500) + 0.30 * math.log1p(s90/1500)
         + 0.20 * math.log1p(tk/200) + 0.20 * math.log1p(rch/1000))
print(f"Max possible level: {level:.6f}")

# 模拟最大 speed
v_abs = math.log1p(s30 / (1 * 100))  # visit_days_30=1
v_ewma = math.log1p(ewma / 50)
speed = 0.40 * v_abs + 0.30 * 0 + 0.30 * v_ewma
print(f"Max possible speed: {speed:.6f}")

raw = 0.60 * level + 0.30 * speed
print(f"Max possible raw: {raw:.6f}")

# 查充值特征
cur.execute("""
    SELECT member_id, recharge_90 FROM dws.dws_member_recharge
    WHERE site_id = %s ORDER BY recharge_90 DESC LIMIT 3
""", (sid,))
print(f"\nTop recharge: {cur.fetchall()}")

# 查 dws_member_consumption 列定义
cur.execute("""
    SELECT column_name, data_type, numeric_precision, numeric_scale
    FROM information_schema.columns
    WHERE table_schema='dws' AND table_name='dws_member_consumption'
    AND data_type='numeric'
""")
print(f"\ndws_member_consumption numeric cols:")
for r in cur.fetchall():
    print(f"  {r[0]}: numeric({r[1]},{r[2]})")

conn.close()