# -*- coding: utf-8 -*-
"""보고서 생성: DB 상위 10건 → Jinja2 HTML → WeasyPrint PDF
- ChatGPT(OpenAI)로 물건별 투자 포인트 3줄 생성 (실패 시 생략하고 계속)
- 당일 데이터 없으면 최근 데이터로 폴백
"""
import sys, os, json
from datetime import datetime, date

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from db.models import get_config, get_conn, log_stage

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
OUTPUT_DIR = os.path.join(BASE_DIR, "output")
SCEN_LABEL = {"conservative": "보수", "base": "기준", "aggressive": "공격"}


def latest_score_date(cur):
    cur.execute("SELECT MAX(score_date) d FROM item_scores")
    r = cur.fetchone()
    return r["d"]


def fetch_top_items(cur, cfg, score_date):
    cur.execute(
        """SELECT i.*, s.predicted_bid_price, s.market_price_est, s.market_confidence,
                  s.rank_today, r.risk_level, r.summary_text
           FROM item_scores s
           JOIN auction_items i ON i.id = s.item_id
           LEFT JOIN risk_analysis r ON r.item_id = s.item_id
           WHERE s.score_date=%s AND s.rank_today <= %s
           ORDER BY s.rank_today""",
        (score_date, cfg["scoring"]["top_n"]))
    items = cur.fetchall()
    for it in items:
        it["rank"] = it["rank_today"]
        it["risk_level"] = it["risk_level"] or "N/A"
        it["risk_summary"] = it["summary_text"] or "권리분석 미수행 (축약 보고서)"
        cur.execute(
            """SELECT scenario, est_total_cost, est_exit_price, est_profit, roi_pct
               FROM roi_analysis WHERE item_id=%s AND calc_date=%s
               ORDER BY FIELD(scenario,'conservative','base','aggressive')""",
            (it["id"], score_date))
        scen = cur.fetchall()
        it["scenarios"] = [
            {"name": SCEN_LABEL.get(s["scenario"], s["scenario"]), **s} for s in scen]
        base_row = next((s for s in scen if s["scenario"] == "base"), None)
        it["roi_base"] = base_row["roi_pct"] if base_row else "-"
        it["invest_points"] = None
    return items


def gen_invest_points(cfg, items):
    """LLM으로 물건별 투자 포인트 생성 (베스트에포트)"""
    try:
        from openai import OpenAI
        client = OpenAI(api_key=cfg["llm"]["api_key"])
        for it in items:
            prompt = (f"경매물건 투자 포인트를 3줄 이내로 작성. 과장 없이 사실 기반.\n"
                      f"소재지: {it['address']}\n종류: {it['property_type']}\n"
                      f"감정가: {it['appraisal_price']}, 최저가: {it['min_bid_price']}, "
                      f"유찰: {it['fail_count']}회\n추정시세: {it['market_price_est']}\n"
                      f"권리분석: {it['risk_summary']}\n"
                      f"기준 수익률: {it['roi_base']}%")
            resp = client.chat.completions.create(
                model=cfg["llm"]["model"], max_tokens=300,
                messages=[{"role": "user", "content": prompt}])
            it["invest_points"] = (resp.choices[0].message.content or "").strip()
    except Exception as e:
        print(f"[report] 투자포인트 생성 스킵: {e}")


def run():
    cfg = get_config()
    started = datetime.now()
    today = date.today()
    os.makedirs(OUTPUT_DIR, exist_ok=True)
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            sdate = latest_score_date(cur)
            if sdate is None:
                raise RuntimeError("스코어 데이터가 없습니다")
            fallback_msg = None
            if sdate != today:
                fallback_msg = (f"당일 수집/분석에 실패하여 {sdate} 기준 데이터로 "
                                f"보고서를 생성했습니다. 파이프라인 로그를 확인하세요.")
            items = fetch_top_items(cur, cfg, sdate)
            if not items:
                raise RuntimeError("보고 대상 물건이 없습니다")

            gen_invest_points(cfg, items)

            from jinja2 import Environment, FileSystemLoader
            env = Environment(loader=FileSystemLoader(
                os.path.join(BASE_DIR, "report", "templates")))
            html = env.get_template("report.html").render(
                report_date=today.strftime("%Y-%m-%d"),
                data_date=str(sdate), items=items, fallback_msg=fallback_msg)

            pdf_path = os.path.join(OUTPUT_DIR, f"auction_report_{today}.pdf")
            from weasyprint import HTML
            HTML(string=html).write_pdf(pdf_path)

            cur.execute(
                """INSERT INTO report_logs (report_date, item_ids, pdf_path, send_status)
                   VALUES (%s,%s,%s,'generated')
                   ON DUPLICATE KEY UPDATE item_ids=VALUES(item_ids),
                     pdf_path=VALUES(pdf_path), send_status='generated'""",
                (today, json.dumps([it["id"] for it in items]), pdf_path))

        log_stage("report", "success" if not fallback_msg else "partial",
                  len(items), fallback_msg, started)
        print(f"[report] PDF 생성: {pdf_path}")
    except Exception as e:
        log_stage("report", "failed", 0, str(e), started)
        raise
    finally:
        conn.close()


if __name__ == "__main__":
    run()