summary_daily.py

# summary_daily.py — Daily summary + guard loop ala /lb (HF Spaces friendly)
import os
import time
import asyncio
import sqlite3
from collections import defaultdict
from datetime import datetime, timezone, timedelta, date
from zoneinfo import ZoneInfo

# =========================
# ENV / DB
# =========================
DB_PATH = os.environ.get("BOTSIGNAL_DB", "/tmp/botsignal.db")
OUT_DIR = os.environ.get("SUMMARY_OUT_DIR", "summaries")  # optional, not used by default
JAKARTA_TZ = ZoneInfo("Asia/Jakarta")
SUMMARY_CHAT_ID_ENV = os.environ.get("SUMMARY_CHAT_ID")  # opsional; fallback ke TARGET_CHANNEL

def _db():
    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA journal_mode=WAL;")
    return conn

def _ensure_tables():
    """
    Tabel summary_*:
    - summary_calls: log post awal (diisi oleh botsignal saat pertama kali posting CA)
    - summary_milestones: log milestone reply (diisi oleh autotrack)
    - summary_outcomes: penanda lose (delete)
    """
    conn = _db()
    conn.executescript("""
    CREATE TABLE IF NOT EXISTS summary_calls (
        keyword TEXT PRIMARY KEY,
        posted_at INTEGER NOT NULL,
        tier TEXT NOT NULL,
        msg_id INTEGER NOT NULL
    );
    CREATE TABLE IF NOT EXISTS summary_milestones (
        keyword TEXT NOT NULL,
        reply_msg_id INTEGER,
        multiple REAL,
        replied_at INTEGER NOT NULL
    );
    CREATE TABLE IF NOT EXISTS summary_outcomes (
        keyword TEXT PRIMARY KEY,
        is_deleted INTEGER DEFAULT 0
    );
    """)
    conn.commit()
    conn.close()

# =========================
# Helpers
# =========================
def _day_bounds_utc(d: date):
    # [00:00, 23:59:59] UTC untuk tanggal d
    sod = datetime(d.year, d.month, d.day, tzinfo=timezone.utc)
    eod = datetime(d.year, d.month, d.day, 23, 59, 59, tzinfo=timezone.utc)
    return int(sod.timestamp()), int(eod.timestamp())

def _fmt(num):
    return f"{num:,}".replace(",", ".")

# =========================
# Core summary compute
# =========================
def compute_summary_for_date(d: date):
    _ensure_tables()
    sod, eod = _day_bounds_utc(d)
    conn = _db(); cur = conn.cursor()

    # Semua post awal selama hari tsb (UTC)
    cur.execute("""
        SELECT keyword, tier, posted_at, msg_id
        FROM summary_calls
        WHERE posted_at BETWEEN ? AND ?
    """, (sod, eod))
    rows = cur.fetchall()
    total_calls = len(rows)
    calls = {r[0]: {"tier": r[1], "posted_at": r[2], "msg_id": r[3]} for r in rows}

    # Max multiple per keyword (hanya reply di window hari tsb)
    max_mult_per_kw = {}
    have_reply = set()
    if calls:
        q = ",".join("?" for _ in calls)
        cur.execute(f"""
            SELECT keyword, MAX(multiple)
            FROM summary_milestones
            WHERE keyword IN ({q})
              AND replied_at BETWEEN ? AND ?
            GROUP BY keyword
        """, (*calls.keys(), sod, eod))
        for kw, mx in cur.fetchall():
            if mx is not None:
                have_reply.add(kw)
                max_mult_per_kw[kw] = mx

    # Delete mark (lose)
    deleted = set()
    if calls:
        q = ",".join("?" for _ in calls)
        cur.execute(f"""
            SELECT keyword
            FROM summary_outcomes
            WHERE keyword IN ({q})
              AND is_deleted = 1
        """, (*calls.keys(),))
        for (kw,) in cur.fetchall():
            deleted.add(kw)

    conn.close()

    wins = have_reply
    loses = (deleted | (set(calls.keys()) - have_reply))

    calls_per_tier = defaultdict(int)
    for _, meta in calls.items():
        calls_per_tier[meta["tier"]] += 1

    win_per_tier = defaultdict(int)
    lose_per_tier = defaultdict(int)
    for kw, meta in calls.items():
        if kw in wins:
            win_per_tier[meta["tier"]] += 1
        if kw in loses:
            lose_per_tier[meta["tier"]] += 1

    top_kw, top_mult = None, None
    for kw, mult in max_mult_per_kw.items():
        if top_mult is None or mult > top_mult:
            top_kw, top_mult = kw, mult

    return {
        "total_calls": total_calls,
        "win_rate": (len(wins) / total_calls) * 100 if total_calls else 0.0,
        "calls_per_tier": dict(sorted(calls_per_tier.items())),
        "total_win": len(wins),
        "total_lose": len(loses),
        "win_per_tier": dict(sorted(win_per_tier.items())),
        "lose_per_tier": dict(sorted(lose_per_tier.items())),
        "top_call": {"keyword": top_kw, "max_multiple": top_mult} if top_kw else None,
    }

def render_telegram_html(summary: dict, label_date: str) -> str:
    # HTML (pakai parse_mode='html')
    lines = []
    lines.append(f"<b>Daily Summary — {label_date}</b>")
    lines.append("")
    lines.append(f"<b>Total Call:</b> {_fmt(summary['total_calls'])}")
    lines.append(f"<b>Win Rate:</b> {summary['win_rate']:.2f}%")
    lines.append("")
    lines.append("<b>Total Call per Tier</b>")
    if summary["calls_per_tier"]:
        for tier, cnt in summary["calls_per_tier"].items():
            lines.append(f"• {tier}: {_fmt(cnt)}")
    else:
        lines.append("• (kosong)")
    lines.append("")
    lines.append("<b>Total Win & Lose</b>")
    lines.append(f"• Win: {_fmt(summary['total_win'])}")
    lines.append(f"• Lose: {_fmt(summary['total_lose'])}")
    lines.append("")
    lines.append("<b>Detail per Tier</b>")
    lines.append("<u>Win</u>")
    if summary["win_per_tier"]:
        for tier, cnt in summary["win_per_tier"].items():
            lines.append(f"• {tier}: {_fmt(cnt)}")
    else:
        lines.append("• (kosong)")
    lines.append("<u>Lose</u>")
    if summary["lose_per_tier"]:
        for tier, cnt in summary["lose_per_tier"].items():
            lines.append(f"• {tier}: {_fmt(cnt)}")
    else:
        lines.append("• (kosong)")
    lines.append("")
    lines.append("<b>Top Call (Max Reply Multiple)</b>")
    if summary["top_call"] and summary["top_call"]["keyword"]:
        lines.append(f"• CA: <code>{summary['top_call']['keyword']}</code>")
        lines.append(f"• Max: {summary['top_call']['max_multiple']}×")
    else:
        lines.append("• (belum ada reply)")
    return "\n".join(lines)

# =========================
# Guard loop ala /lb (idempotent)
# =========================
def _sent_db():
    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA journal_mode=WAL;")
    return conn

def _ensure_sent_table():
    conn = _sent_db()
    conn.executescript("""
    CREATE TABLE IF NOT EXISTS summary_sent (
        day TEXT PRIMARY KEY,     -- YYYY-MM-DD (lokal WIB)
        sent_at INTEGER NOT NULL  -- epoch UTC
    );
    """)
    conn.commit(); conn.close()

def _is_sent(day_iso: str) -> bool:
    try:
        conn = _sent_db(); cur = conn.cursor()
        cur.execute("SELECT 1 FROM summary_sent WHERE day = ?", (day_iso,))
        ok = cur.fetchone() is not None
        conn.close(); return ok
    except Exception:
        return False

def _mark_sent(day_iso: str):
    conn = _sent_db()
    conn.execute(
        "INSERT OR REPLACE INTO summary_sent(day, sent_at) VALUES(?, ?)",
        (day_iso, int(time.time()))
    )
    conn.commit(); conn.close()

def _yesterday_local():
    now_local = datetime.now(JAKARTA_TZ)
    return (now_local.date() - timedelta(days=1)), now_local

async def _maybe_send_daily_summary_once(client, fallback_chat: str):
    """
    Kirim rekap H-1 sekali/hari setelah >= 06:00 WIB (lokal).
    Idempotent via tabel summary_sent.
    """
    day, now_local = _yesterday_local()
    if now_local.hour < 6:
        return  # belum waktunya

    day_iso = day.isoformat()
    if _is_sent(day_iso):
        return  # sudah terkirim

    # Tujuan kirim
    target_chat = SUMMARY_CHAT_ID_ENV or os.environ.get("TARGET_CHANNEL", fallback_chat)

    # Hitung & kirim
    data = compute_summary_for_date(day)
    html_text = render_telegram_html(data, day_iso)
    await client.send_message(target_chat, html_text, parse_mode="html")
    _mark_sent(day_iso)
    print(f"[SUMMARY] sent for {day_iso} → {target_chat}")

async def start_summary_guard(client, *, interval_sec: int = 900, fallback_chat: str = "@MidasTouchsignalll"):
    """
    Panggil sekali saat startup app (mis. dari servr.py).
    Loop ringan cek tiap 15 menit—mirip /lb periodic.
    Cocok untuk HF Spaces yang sleep: saat bangun setelah 06:00, dia kirim H-1 sekali.
    """
    _ensure_sent_table()
    while True:
        try:
            await _maybe_send_daily_summary_once(client, fallback_chat)
        except Exception as e:
            print(f"[SUMMARY] guard error: {e}")
        await asyncio.sleep(interval_sec)

# =========================
# Optional: CLI sekali jalan (kirim 'hari ini')
# =========================
if __name__ == "__main__":
    # Kirim summary untuk 'hari ini' (UTC) ke TARGET_CHANNEL — hanya untuk tes manual
    import asyncio as _a
    from telethon import TelegramClient
    from telethon.sessions import StringSession

    API_ID = int(os.environ.get("API_ID", "0"))
    API_HASH = os.environ.get("API_HASH", "")
    STRING_SESSION = os.environ.get("STRING_SESSION", "")
    TARGET_CHANNEL = os.environ.get("TARGET_CHANNEL", "@MidasTouchsignalll")  # fallback

    _summary = compute_summary_for_date(date.today())
    _html = render_telegram_html(_summary, date.today().strftime("%Y-%m-%d"))

    async def _main():
        async with TelegramClient(StringSession(STRING_SESSION), API_ID, API_HASH) as c:
            await c.send_message(SUMMARY_CHAT_ID_ENV or TARGET_CHANNEL, _html, parse_mode="html")
            print("[SUMMARY] posted to Telegram")

    _a.run(_main())