"""수급 오실레이터 커스텀 섹터 '외인 순매수 TOP25' 자동 갱신.

외인 순매수 절대금액 상위 25종목(최신 거래일)을 매일 custom_sectors.json 에 넣는다.
투자자데이터(investor_trend.db) → 오실레이터 유니버스(interactive xlsx)에 있는 종목만.
사용자가 만든 다른 커스텀 섹터는 보존.
"""
import json
import sqlite3
from pathlib import Path

import openpyxl

IDB = Path("/Users/loonshots/룬샤시_공시정리/db/investor_trend.db")
CS = Path("/Users/loonshots/룬샤시_공시정리/태버지/custom_sectors.json")
XLSX = Path("/Users/loonshots/valuechain-earnings-app/backend/data/sector_oscillator_interactive.xlsx")
KEY = "외인 순매수 TOP25"
N = 25


def oscillator_universe() -> set[str]:
    wb = openpyxl.load_workbook(XLSX, read_only=True)
    try:
        for sh in ("_StockOsc", "_StockMcap"):
            if sh in wb.sheetnames:
                hdr = next(wb[sh].iter_rows(values_only=True))
                return {str(c) for c in hdr[1:] if c}
        return set()
    finally:
        wb.close()


def top_foreign_buyers(limit: int = 200) -> tuple[str, list[tuple[str, float]]]:
    conn = sqlite3.connect(f"file:{IDB}?mode=ro", uri=True)
    try:
        date = conn.execute("SELECT MAX(trade_date) FROM investor_daily").fetchone()[0]
        rows = conn.execute(
            """SELECT i.stock_name, d.foreign_net
               FROM investor_daily d JOIN stock_info i ON i.stock_code = d.stock_code
               WHERE d.trade_date = ? AND d.foreign_net > 0 AND i.stock_name != ''
               ORDER BY d.foreign_net DESC LIMIT ?""",
            (date, limit),
        ).fetchall()
        return date, rows
    finally:
        conn.close()


def main() -> None:
    uni = oscillator_universe()
    date, rows = top_foreign_buyers()
    names: list[str] = []
    for name, _ in rows:
        if name in uni and name not in names:
            names.append(name)
        if len(names) >= N:
            break

    custom = {}
    if CS.exists():
        try:
            custom = json.loads(CS.read_text(encoding="utf-8"))
        except Exception:
            custom = {}
    custom[KEY] = names  # 다른 커스텀 섹터 보존, 이 키만 갱신
    CS.parent.mkdir(parents=True, exist_ok=True)
    CS.write_text(json.dumps(custom, ensure_ascii=False, indent=2), encoding="utf-8")
    print(f"[{date}] {KEY}: {len(names)}종목")
    print("  " + ", ".join(names))


if __name__ == "__main__":
    main()