#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""生成「早盘策略第一票（亮黄条件）」历史总表
条件（知识库唯一权威）：涨停委买额 > 2000 万 + 昨日首板 + 非 ST（不含涨幅、不含净额）
第一票 = 当日亮黄清单里涨停委买额最大的一只
"""
import sqlite3, csv, io, statistics

AM = "/var/lib/kpl/p11_backtest/auction_master.db"
EXT = "/var/lib/kpl/p11_backtest/ext.db"
OUT_DB = "/var/lib/kpl/p11_backtest/lianghuang_first.db"
FEE = 0.12

am = sqlite3.connect("file:%s?mode=ro" % AM, uri=True)
am.row_factory = sqlite3.Row
ext = sqlite3.connect("file:%s?mode=ro" % EXT, uri=True)
db = ext.execute("SELECT name FROM sqlite_master WHERE name='daily_basic'").fetchone()
basic = {}
if db:
    for ts, d, cl, cmv, tmv, fs, fls in ext.execute(
            "SELECT ts_code, trade_date, close, circ_mv, total_mv, free_share, float_share FROM daily_basic"):
        basic[(d, ts)] = (cl, cmv, tmv, fs, fls)
print("daily_basic 载入 %d 条" % len(basic), flush=True)

def suf(c):
    if c.startswith(("60", "68", "90")):
        return "SH"
    if c.startswith(("00", "30", "20")):
        return "SZ"
    return "BJ"

rows = []
for r in am.execute("SELECT * FROM auction_master"):
    code = r["code"]
    ts = code + "." + suf(code)
    b = basic.get((r["day"], ts))
    free_mv = None
    circ_mv = None
    if b:
        cl, cmv, tmv, fs, fls = b
        circ_mv = (cmv or 0) / 1e4 if cmv else None                 # 万元 → 亿元
        if fs and r["entry"]:
            free_mv = fs * 10000 * (cl or r["entry"]) / 1e8          # 自由流通市值（亿元）
    rows.append(dict(day=r["day"], code=code, name=r["name"],
                     limit_bid=r["limit_bid"], auction_pct=r["auction_pct"],
                     jj_amount=r["jj_amount"], auction_net=r["auction_net"],
                     auction_turn=r["auction_turn"], float_mv_int=r["float_mv"],
                     circ_mv=circ_mv, free_mv=free_mv,
                     first_board=r["first_board"], lb_count=r["lb_count"],
                     entry=r["entry"], t0_close=r["t0_close"], t1_day=r["t1_day"],
                     open_t1=r["open_t1"], high_t1=r["high_t1"], low_t1=r["low_t1"],
                     close_t1=r["close_t1"], one_word=r["one_word"], stuck_t1=r["stuck_t1"],
                     r_t0=r["r_t0"], r_t1_open=r["r_t1_open"], r_t1_close=r["r_t1_close"],
                     r_t1_high=r["r_t1_high"], concepts=r["concepts"]))
print("主表 %d 行" % len(rows), flush=True)

def is_st(x):
    return "ST" in (x["name"] or "").upper()

buyable = [x for x in rows if not x["one_word"] and not is_st(x)]
lh = [x for x in buyable if x["first_board"] == 1 and (x["limit_bid"] or 0) > 2e7]
byday = {}
for x in lh:
    if x["day"] not in byday or (x["limit_bid"] or 0) > (byday[x["day"]]["limit_bid"] or 0):
        byday[x["day"]] = x
first = sorted(byday.values(), key=lambda x: x["day"], reverse=True)

# 落库
o = sqlite3.connect(OUT_DB)
o.execute("DROP TABLE IF EXISTS lianghuang_first")
cols = ["day", "code", "name", "limit_bid", "auction_pct", "jj_amount", "auction_net",
        "auction_turn", "circ_mv", "free_mv", "lb_count", "entry", "t0_close",
        "t1_day", "open_t1", "high_t1", "low_t1", "close_t1",
        "r_t0", "r_t1_open", "r_t1_close", "r_t1_high"]
o.execute("CREATE TABLE lianghuang_first(%s, PRIMARY KEY(day, code))" % ",".join(cols))
o.executemany("INSERT OR REPLACE INTO lianghuang_first VALUES (%s)" % ",".join("?" * len(cols)),
              [[x["%s" % c] if c != "circ_mv" and c != "free_mv" else x[c] for c in cols] for x in first])
o.commit()
print("lianghuang_first 写入 %d 行" % len(first), flush=True)

def stat(rs):
    v = [x["r_t1_close"] for x in rs if x["r_t1_close"] is not None and not x["stuck_t1"]]
    if not v:
        return None
    return (len(v), sum(1 for i in v if i > 0) / len(v) * 100,
            sum(v) / len(v), statistics.median(v))

s = stat(first)
print("\n== 第一票（%d 天）T+1 收盘：%d 笔｜胜率 %.2f%%｜均值 %+.2f%%｜中位 %+.2f%%"
      % (len(first), s[0], s[1], s[2], s[3]), flush=True)
for y in ("2024", "2025", "2026"):
    v = [x for x in first if x["day"][:4] == y]
    t = stat(v)
    if t:
        print("   %s：%d 笔｜胜率 %.2f%%｜均值 %+.2f%%｜中位 %+.2f%%" % (y, t[0], t[1], t[2], t[3]), flush=True)

HDR = ["日期", "代码", "名称", "涨停委买额万", "竞价涨幅%", "竞价金额万", "竞价净额万",
       "竞价换手%", "流通市值亿", "自由流通市值亿", "连板数", "买入价", "T0收盘",
       "T1日期", "T1开盘", "T1最高", "T1最低", "T1收盘",
       "T0盈亏%", "T1开盘盈亏%", "T1收盘盈亏%", "T1最高盈亏%", "题材"]
buf = io.StringIO()
buf.write("\ufeff" + ",".join(HDR) + "\n")
for x in first:
    def n(v, d=2):
        return "" if v is None else round(v, d)
    buf.write(",".join(str(v) for v in [
        x["day"], x["code"], x["name"], n((x["limit_bid"] or 0) / 1e4, 0), n(x["auction_pct"]),
        n((x["jj_amount"] or 0) / 1e4, 0), n((x["auction_net"] or 0) / 1e4, 0),
        n(x["auction_turn"]), n(x["circ_mv"]), n(x["free_mv"]), x["lb_count"],
        n(x["entry"]), n(x["t0_close"]), x["t1_day"] or "", n(x["open_t1"]),
        n(x["high_t1"]), n(x["low_t1"]), n(x["close_t1"]),
        n(x["r_t0"]), n(x["r_t1_open"]), n(x["r_t1_close"]), n(x["r_t1_high"]),
        (x["concepts"] or "").replace(",", "、")]).replace("\n", " ") + "\n")
open("/tmp/first_ticket_lianghuang.csv", "w", encoding="utf-8").write(buf.getvalue())
print("导出 /tmp/first_ticket_lianghuang.csv（%d 行）" % len(first), flush=True)
print("DONE", flush=True)