#!/usr/bin/env python3
"""
🔔 LaoPatana Draw Alert — แจ้งผลหวยออก + เทียบกับที่ทำนาย
ตรวจทุก 18:00 หลังหวยออก → เช็คผลใหม่ → สรุปส่ง VIP
"""

import subprocess
import os
import json
import requests
from datetime import datetime

def _read_secret(path, default=""):
    try:
        with open(path) as f:
            return f.read().strip()
    except Exception:
        return default


TOKEN = os.environ.get("VIP_TELEGRAM_BOT_TOKEN") or _read_secret("/etc/nami-harness/vip_telegram_token")
CHANNEL = "-1003887377430"

DB_ENV = {"PGPASSWORD": os.environ.get("PGPASSWORD") or _read_secret("/etc/nami-harness/postgres_password")}
PSQL = ["psql", "-h", "127.0.0.1", "-U", "postgres", "-d", "laopatana_stat_lab", "-t", "-A", "-F", "|"]


def db_query(sql):
    try:
        r = subprocess.run(PSQL + ["-c", sql], capture_output=True, text=True,
                           env=DB_ENV, timeout=15)
        if r.returncode != 0:
            return None
        return r.stdout.strip()
    except Exception as e:
        print(f"  DB error: {e}")
        return None


def get_latest_draw():
    """Get latest drawn date"""
    row = db_query("""
        SELECT draw_date::date, lao_last4, lao_last2, lao_last3, COALESCE(notes,'')
        FROM draws WHERE status='drawn' AND lao_last2 IS NOT NULL
        ORDER BY draw_date DESC LIMIT 1
    """)
    if not row:
        return None
    cols = row.split("|")
    return {
        "date": cols[0],
        "lao_4d": cols[1] if len(cols) > 1 else "?",
        "lao_2d": cols[2] if len(cols) > 2 else "?",
        "lao_3d": cols[3] if len(cols) > 3 else "?",
        "notified": "VIP_ALERT" in (cols[4] if len(cols) > 4 else ""),
    }


def get_prediction_for_draw(draw_date):
    """Get evaluated prediction for a specific draw date"""
    row = db_query(f"""
        SELECT p.id, p.win_streak, p.roi_simulated::numeric(5,2), p.note
        FROM predictions p
        WHERE p.status='evaluated' 
          AND p.target_draw_date::date = '{draw_date}'::date
        ORDER BY p.created_at DESC LIMIT 1
    """)
    if not row:
        return None
    cols = row.split("|")
    pred_id = cols[0]

    # Get results per item
    items = db_query(f"""
        SELECT pi.bet_type, pi.number, pi.category,
               pr.is_hit, pr.hit_type, pr.payout_simulated::numeric(5,2)
        FROM prediction_items pi
        JOIN prediction_results pr ON pr.prediction_item_id = pi.id
        WHERE pi.prediction_id = '{pred_id}' AND pi.is_rejected = false
        ORDER BY pi.bet_type, 
          CASE pi.bet_type WHEN '1d' THEN 1 WHEN '2d' THEN 2 ELSE 3 END,
          CASE WHEN pr.is_hit THEN 0 ELSE 1 END, pi.rank
    """)

    hits = {"1d": 0, "2d": 0, "3d": 0}
    total = {"1d": 0, "2d": 0, "3d": 0}
    hit_details = {"1d": [], "2d": [], "3d": []}
    total_payout = 0

    if items:
        for line in items.split("\n"):
            if not line.strip():
                continue
            parts = line.split("|")
            if len(parts) < 5:
                continue
            bt, num, cat, is_hit, hit_type, payout = parts[0], parts[1], parts[2], parts[3], parts[4], float(parts[5] if len(parts) > 5 else 0)
            if is_hit == "t":
                hits[bt] = hits.get(bt, 0) + 1
                hit_details[bt].append(f"{num} {hit_type}")
                total_payout += float(payout) if isinstance(payout, (int, float)) else 0
            total[bt] = total.get(bt, 0) + 1

    return {
        "pred_id": pred_id,
        "win_streak": int(cols[1]) if len(cols) > 1 else 0,
        "roi": float(cols[2]) if len(cols) > 2 else 0,
        "hits": hits,
        "total": total,
        "hit_details": hit_details,
        "total_payout": total_payout,
    }


def format_alert(draw, pred):
    """Format draw result alert"""
    roi_emoji = "🟢" if pred["roi"] > 0 else "🔴" if pred["roi"] < 0 else "⚪"
    ws = pred["win_streak"]

    lines = [f"🔔 <b>ผลหวยลาว — {draw['date']}</b>\n"]
    lines.append(f"🎯 <b>ออก:</b> <code>{draw['lao_4d']}</code>")
    lines.append(f"   2D: <code>{draw['lao_2d']}</code> | 3D: <code>{draw['lao_3d']}</code>\n")

    # Per-type summary
    for bt, label in [("1d", "1D วิ่ง"), ("2d", "2D"), ("3d", "3D Exact")]:
        h, t = pred["hits"].get(bt, 0), pred["total"].get(bt, 0)
        if t > 0:
            emoji = "✅" if h > 0 else "❌"
            detail = f" ({', '.join(pred['hit_details'].get(bt, []))})" if h > 0 else ""
            lines.append(f"{emoji} <b>{label}:</b> {h}/{t}{detail}")
        else:
            lines.append(f"➖ <b>{label}:</b> ไม่มีในทำนาย")

    lines.append(f"\n{roi_emoji} <b>ROI:</b> {pred['roi']:.2f} | <b>Win Streak:</b> {ws}")
    lines.append(f"💰 <b>Payout สะสม:</b> {pred['total_payout']:.2f}x")

    # Overall trend
    if pred["hits"].get("1d", 0) > 0:
        lines.append("\n💪 <b>1D ยังแรง!</b> Top picks ยังเข้าเป้าต่อเนื่อง")

    lines.append(f"\n📊 <a href='https://laopatana.178.104.181.132.nip.io'>ดูสถิติเต็ม Dashboard</a>")
    lines.append("🌸 @namiByPao_bot")

    return "\n".join(lines)


def mark_notified(draw_date):
    """Mark draw as notified to prevent re-send"""
    db_query(f"""
        UPDATE draws SET notes = CASE 
            WHEN notes IS NULL OR notes = '' THEN 'VIP_ALERT'
            WHEN notes NOT LIKE '%VIP_ALERT%' THEN notes || ' VIP_ALERT'
            ELSE notes
        END
        WHERE draw_date::date = '{draw_date}'::date
    """)


def send_to_vip(message):
    url = f"https://api.telegram.org/bot{TOKEN}/sendMessage"
    try:
        resp = requests.post(url, json={
            "chat_id": CHANNEL,
            "text": message,
            "parse_mode": "HTML",
            "disable_web_page_preview": True
        }, timeout=15)
        return resp.json().get("ok", False)
    except Exception as e:
        print(f"  ❌ Send failed: {e}")
        return False


def main():
    print(f"[{datetime.now()}] 🔔 Draw Alert Check")

    draw = get_latest_draw()
    if not draw:
        print("  No drawn draws found")
        return

    print(f"  Latest draw: {draw['date']} ({draw['lao_4d']}) — notified: {draw['notified']}")
    print(f"  URL: https://laopatana.178.104.181.132.nip.io/api/draws/{draw['date']}")

    if draw["notified"]:
        print(f"  ⏭️  Already notified, skip")
        return

    pred = get_prediction_for_draw(draw["date"])
    if not pred:
        print(f"  ⚠️  No prediction found for {draw['date']}")
        return

    print(f"  Prediction: {pred['pred_id'][:8]}...")
    print(f"  1D: {pred['hits'].get('1d', 0)}/{pred['total'].get('1d', 0)}")
    print(f"  2D: {pred['hits'].get('2d', 0)}/{pred['total'].get('2d', 0)}")
    print(f"  3D: {pred['hits'].get('3d', 0)}/{pred['total'].get('3d', 0)}")

    msg = format_alert(draw, pred)
    ok = send_to_vip(msg)

    if ok:
        mark_notified(draw["date"])
        print(f"  ✅ Alert sent + marked as notified")
    else:
        print(f"  ❌ Failed to send alert")


if __name__ == "__main__":
    main()