# -*- coding: utf-8 -*-
"""필터링 + 예상낙찰가 예측 + 종합 경쟁력 점수 산출
- ML 모델(models/bid_model.pkl)이 있으면 XGBoost 예측, 없으면 규칙 기반 낙찰가율
"""
import sys, os, json, pickle
from datetime import datetime, date, timedelta

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__)))
MODEL_PATH = os.path.join(BASE_DIR, "models", "bid_model.pkl")


def load_model():
    if os.path.exists(MODEL_PATH):
        with open(MODEL_PATH, "rb") as f:
            return pickle.load(f)
    return None


def estimate_market_price(cur, item):
    """인근 실거래가 중위값 추정. (지역코드 + 물건종류 + 면적 ±15%)
    반환: (추정시세, 신뢰도 A/B/C)"""
    if not item["region_code"] or not item["area_building"]:
        return None, "C"
    area = float(item["area_building"])
    cur.execute(
        """SELECT trade_price FROM market_prices
           WHERE region_code=%s AND property_type=%s
             AND area BETWEEN %s AND %s
             AND trade_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
           ORDER BY trade_price""",
        (item["region_code"], item["property_type"], area * 0.85, area * 1.15))
    prices = [r["trade_price"] for r in cur.fetchall()]
    if not prices:
        return None, "C"
    median = prices[len(prices) // 2]
    conf = "A" if len(prices) >= 20 else ("B" if len(prices) >= 5 else "C")
    return median, conf


def liquidity_score(cur, item):
    """최근 6개월 거래량 기반 환금성 점수 (0~100)"""
    if not item["region_code"]:
        return 30.0
    cur.execute(
        """SELECT COUNT(*) c FROM market_prices
           WHERE region_code=%s AND property_type=%s
             AND trade_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)""",
        (item["region_code"], item["property_type"]))
    c = cur.fetchone()["c"]
    return min(100.0, c * 2.0)   # 50건 이상이면 만점


def predict_bid_price(model, cfg, item, market_price):
    """예상 낙찰가: ML 모델 있으면 사용, 없으면 규칙기반(감정가 × 종류별 낙찰가율)"""
    if model is not None:
        try:
            import pandas as pd
            X = pd.DataFrame([{
                "appraisal_price": item["appraisal_price"] or 0,
                "min_bid_price": item["min_bid_price"] or 0,
                "fail_count": item["fail_count"] or 0,
                "area_building": float(item["area_building"] or 0),
                "market_price": market_price or 0,
                "ptype": hash(item["property_type"]) % 100,
            }])
            return int(model.predict(X)[0])
        except Exception:
            pass
    ratio = cfg["scoring"]["default_bid_ratio"].get(item["property_type"], 0.75)
    base = item["appraisal_price"] or item["min_bid_price"] or 0
    pred = int(base * ratio)
    # 예상낙찰가는 최저매각가 이상이어야 함
    return max(pred, item["min_bid_price"] or 0)


def rental_score(item, market_price):
    """임대수익 점수: 시세 대비 추정 월세수익률 proxy (간이: 물건종류별 기본값)"""
    base = {"오피스텔": 80, "상가": 70, "아파트": 55,
            "다세대주택": 60, "연립주택": 55}.get(item["property_type"], 40)
    return float(base)


def run():
    cfg = get_config()
    started = datetime.now()
    sc = cfg["scoring"]
    model = load_model()
    today = date.today()
    processed = 0

    conn = get_conn()
    try:
        with conn.cursor() as cur:
            # 1차 규칙 필터
            d_min = today + timedelta(days=sc["sale_date_min_days"])
            d_max = today + timedelta(days=sc["sale_date_max_days"])
            cur.execute(
                """SELECT * FROM auction_items
                   WHERE status='active'
                     AND sale_date BETWEEN %s AND %s
                     AND appraisal_price IS NOT NULL AND min_bid_price IS NOT NULL
                     AND (appraisal_price - min_bid_price) / appraisal_price >= %s""",
                (d_min, d_max, sc["min_discount_rate"]))
            items = cur.fetchall()

            # 배제 키워드
            items = [it for it in items if not any(
                kw in (it["note"] or "") for kw in sc["exclude_keywords"])]

            results = []
            for it in items:
                market, conf = estimate_market_price(cur, it)
                pred = predict_bid_price(model, cfg, it, market)
                # 시세 없으면 감정가를 보수적 시세로 사용 (신뢰도 C)
                mkt = market or int((it["appraisal_price"] or 0) * 0.95)

                margin = max(0.0, (mkt - pred) / mkt) if mkt else 0.0
                margin_score = min(100.0, margin * 400)   # 25% 마진 = 만점
                liq = liquidity_score(cur, it)
                # 리스크 점수는 권리분석 전이므로 기본 60점, risk 단계에서 재계산
                cur.execute(
                    "SELECT risk_level FROM risk_analysis WHERE item_id=%s "
                    "ORDER BY analyzed_at DESC LIMIT 1", (it["id"],))
                r = cur.fetchone()
                risk_pt = {"LOW": 100, "MID": 50, "HIGH": 0}.get(
                    r["risk_level"] if r else None, 60)
                rent = rental_score(it, mkt)

                w = sc["weights"]
                score = (w["margin"] * margin_score + w["liquidity"] * liq +
                         w["risk"] * risk_pt + w["rental"] * rent)
                results.append((it["id"], pred, market, conf, margin, round(score, 2)))

            # 순위 부여 + 저장
            results.sort(key=lambda x: x[5], reverse=True)
            for rank, (iid, pred, mkt, conf, margin, score) in enumerate(results, 1):
                cur.execute(
                    """INSERT INTO item_scores
                       (item_id, score_date, predicted_bid_price, market_price_est,
                        market_confidence, discount_rate, competition_score, rank_today)
                       VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
                       ON DUPLICATE KEY UPDATE
                         predicted_bid_price=VALUES(predicted_bid_price),
                         market_price_est=VALUES(market_price_est),
                         market_confidence=VALUES(market_confidence),
                         discount_rate=VALUES(discount_rate),
                         competition_score=VALUES(competition_score),
                         rank_today=VALUES(rank_today)""",
                    (iid, today, pred, mkt, conf, round(margin, 4), score, rank))
                processed += 1

        log_stage("score", "success", processed, started_at=started)
        print(f"[score] {processed}건 스코어링 완료")
    except Exception as e:
        log_stage("score", "failed", processed, str(e), started)
        raise
    finally:
        conn.close()


def rerank_after_risk():
    """권리분석 반영 후 재순위 (risk 단계에서 호출)"""
    run()


if __name__ == "__main__":
    run()