#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""웹진 사용·유튜브 연결 중개사별 자동 숏츠 후보를 하루 한 건 등록한다."""

import argparse
import hashlib
import re
import sys
from datetime import date
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parents[1]
if str(BASE_DIR) not in sys.path:
    sys.path.insert(0, str(BASE_DIR))
from db import get_conn


def clean(value):
    return re.sub(r"\s+", " ", str(value or "")).strip()


def sync_connected_settings(conn, realtor_id=None, group_id=None):
    """기존 웹진도 YouTube 연결 상태라면 자동 숏츠 설정을 보장한다."""
    where = [
        "s.status='active'",
        "r.status='active'",
        "COALESCE(r.is_deleted,0)=0",
        "a.platform='youtube'",
        "a.account_status='connected'",
        "a.is_active=1",
    ]
    params = []
    if realtor_id:
        where.append("s.realtor_id=%s")
        params.append(int(realtor_id))
    if group_id:
        where.append("TRIM(r.group_id)=%s")
        params.append(clean(group_id))
    condition = " AND ".join(where)
    with conn.cursor() as cur:
        cur.execute(f"""
            INSERT INTO multi_shorts_settings
                (site_id,realtor_id,enabled,generation_mode,auto_daily_limit,
                 manual_selection_limit,scheduled_time,minimum_quality_score,
                 created_at,updated_at)
            SELECT s.id,s.realtor_id,1,'auto',1,5,'10:00:00',70,NOW(),NOW()
              FROM multi_sites s
              INNER JOIN blog_realtors r ON r.id=s.realtor_id
              INNER JOIN multi_shorts_oauth_accounts a ON a.realtor_id=s.realtor_id
             WHERE {condition}
            ON DUPLICATE KEY UPDATE
                realtor_id=VALUES(realtor_id),updated_at=NOW()
        """, params)
        settings_affected = int(cur.rowcount or 0)
        cur.execute(f"""
            UPDATE multi_sites s
            INNER JOIN blog_realtors r ON r.id=s.realtor_id
            INNER JOIN multi_shorts_oauth_accounts a ON a.realtor_id=s.realtor_id
               SET s.is_shorts_enabled=1,s.updated_at=NOW()
             WHERE {condition}
               AND COALESCE(s.is_shorts_enabled,0)<>1
        """, params)
        sites_affected = int(cur.rowcount or 0)
    conn.commit()
    print(f"[MULTI SHORTS SETTINGS SYNC] settings_affected={settings_affected} sites_affected={sites_affected}")


def score(row):
    photos = int(row.get("image_count") or 0)
    description_len = len(clean(row.get("article_feature_desc")))
    value = min(35, photos * 7)
    value += 25 if description_len >= 160 else 18 if description_len >= 80 else 10 if description_len >= 30 else 0
    value += sum(4 for key in ("price_text", "area_info", "floor_info", "direction_code", "building_name") if clean(row.get(key)))
    value += 5 if int(row.get("has_location") or 0) else 0
    age = int(row.get("age_days") or 9999)
    value += 10 if age <= 7 else 5 if age <= 30 else 0
    return min(100, value)


def settings_rows(conn, realtor_id=None, group_id=None):
    sql = """
        SELECT st.*,s.slug
          FROM multi_shorts_settings st
          INNER JOIN multi_sites s ON s.id=st.site_id AND s.status='active'
          INNER JOIN blog_realtors r ON r.id=st.realtor_id
          INNER JOIN multi_shorts_oauth_accounts pc
            ON pc.realtor_id=st.realtor_id AND pc.platform='youtube'
           AND pc.account_status='connected' AND pc.is_active=1
         WHERE st.enabled=1 AND st.generation_mode='auto'
           AND r.status='active' AND COALESCE(r.is_deleted,0)=0
    """
    params = []
    if realtor_id:
        sql += " AND st.realtor_id=%s"; params.append(int(realtor_id))
    if group_id:
        sql += " AND TRIM(r.group_id)=%s"; params.append(clean(group_id))
    sql += " ORDER BY st.site_id"
    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchall() or []


def candidates(conn, setting, run_date):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT ma.id,ma.published_at,a.article_feature_desc,a.price_text,a.area_info,
                   a.floor_info,a.direction_code,a.building_name,
                   DATEDIFF(%s,DATE(ma.published_at)) age_days,
                   (SELECT COUNT(*) FROM multi_article_images mi WHERE mi.article_id=ma.id) image_count,
                   EXISTS(SELECT 1 FROM multi_article_locations ml WHERE ml.article_id=ma.id AND ml.geocode_status='success') has_location
              FROM multi_articles ma
              INNER JOIN blog_realtor_articles a
                ON a.realtor_id=ma.realtor_id AND BINARY a.article_no=BINARY ma.source_article_no
             WHERE ma.site_id=%s AND ma.status='published'
               AND COALESCE(a.article_status,'active') NOT IN ('removed','closed')
               AND (DATE(ma.created_at)=%s OR DATE(COALESCE(ma.source_updated_at,ma.created_at))=%s)
               AND NOT EXISTS (
                   SELECT 1 FROM multi_shorts_jobs sj
                    WHERE sj.article_id=ma.id
                      AND sj.queue_status NOT IN ('cancelled','failed')
               )
             ORDER BY ma.published_at DESC,ma.id DESC LIMIT 100
        """, (run_date, setting["site_id"], run_date, run_date))
        rows = cur.fetchall() or []
    for row in rows:
        row["quality_score"] = score(row)
    # 자동 생성은 품질점수 경쟁이 아니라 네이버 등록일 기준 최신 매물 우선이다.
    # 영상 생성이 불가능한 사진 0장 기사만 제외한다.
    rows = [row for row in rows if int(row.get("image_count") or 0) > 0]
    rows.sort(key=lambda row: (clean(row.get("published_at")), row["id"]), reverse=True)
    return rows


def enqueue(conn, setting, article, run_date, dry_run=False):
    raw_key = f"auto:{setting['site_id']}:{article['id']}:{run_date}:{setting.get('template_code') or 'shorts_default'}"
    job_key = hashlib.sha256(raw_key.encode("utf-8")).hexdigest()
    print(f"[MULTI SHORTS AUTO PICK] realtor_id={setting['realtor_id']} article_id={article['id']} score={article['quality_score']}")
    if dry_run:
        return True
    with conn.cursor() as cur:
        cur.execute("""
            INSERT IGNORE INTO multi_shorts_jobs
            (article_id,site_id,realtor_id,trigger_type,selection_date,quality_score,
             template_code,render_version,queue_status,privacy_status,idempotency_key,
             retry_count,scheduled_at,created_at,updated_at)
            VALUES (%s,%s,%s,'auto',%s,%s,%s,1,'pending',%s,%s,0,NOW(),NOW(),NOW())
        """, (article["id"],setting["site_id"],setting["realtor_id"],run_date,
              article["quality_score"],setting.get("template_code") or "shorts_default",
              setting.get("default_privacy") or "public",job_key))
        if cur.rowcount < 1:
            return False
        job_id = cur.lastrowid
        cur.execute("""
            INSERT IGNORE INTO multi_shorts_platform_posts
            (shorts_job_id,platform,upload_status,privacy_status,created_at,updated_at)
            SELECT %s,a.platform,'pending',%s,NOW(),NOW()
              FROM multi_shorts_oauth_accounts a
             WHERE a.realtor_id=%s AND a.account_status='connected' AND a.is_active=1
               AND a.platform='youtube'
        """, (job_id,setting.get("default_privacy") or "public",setting["realtor_id"]))
        cur.execute("""
            INSERT INTO multi_shorts_job_events
            (shorts_job_id,event_type,to_status,message,created_at)
            VALUES (%s,'auto_selected','pending',%s,NOW())
        """, (job_id,f"quality_score={article['quality_score']}"))
    conn.commit()
    return True


def main():
    parser=argparse.ArgumentParser()
    parser.add_argument("--realtor-id",type=int)
    parser.add_argument("--group-id")
    parser.add_argument("--run-date",default=date.today().isoformat())
    parser.add_argument("--dry-run",action="store_true")
    args=parser.parse_args()
    conn=get_conn(); created=skipped=0
    try:
        sync_connected_settings(conn,args.realtor_id,args.group_id)
        for setting in settings_rows(conn,args.realtor_id,args.group_id):
            with conn.cursor() as cur:
                cur.execute("SELECT COUNT(*) cnt FROM multi_shorts_jobs WHERE realtor_id=%s AND trigger_type='auto' AND selection_date=%s AND queue_status NOT IN ('cancelled','failed')",(setting["realtor_id"],args.run_date))
                existing=int((cur.fetchone() or {}).get("cnt") or 0)
            limit=max(1,int(setting.get("auto_daily_limit") or 1))
            needed=max(0,limit-existing)
            if needed<1:
                print(f"[MULTI SHORTS AUTO SKIP] realtor_id={setting['realtor_id']} reason=daily_limit")
                skipped+=1; continue
            picked=candidates(conn,setting,args.run_date)[:needed]
            if not picked:
                print(f"[MULTI SHORTS AUTO SKIP] realtor_id={setting['realtor_id']} reason=no_recent_article")
                skipped+=1; continue
            for article in picked:
                if enqueue(conn,setting,article,args.run_date,args.dry_run): created+=1
                else: skipped+=1
    finally:
        conn.close()
    print(f"[MULTI SHORTS AUTO DONE] created={created} skipped={skipped}")


if __name__=='__main__':
    main()