# -*- coding: utf-8 -*-
"""
V2-026 TargetSelector

역할:
- V2 Pipeline에서 오늘 처리할 매물 1건을 결정한다.
- 최신 10건만 보는 방식은 금지한다.
- 중개사의 전체 DB 매물 중 상태를 보고 우선순위에 따라 선택한다.

핵심 원칙:
1. 발행 대기 Queue가 있으면 최우선 선택한다.
2. 그 다음은 "기존 미발행 초안"과 "초안 없는 신규 수집 후보"를
   네이버부동산 등록일 기준으로 비교한다.
3. 기존 미발행 초안의 등록일이 신규 후보와 같거나 더 최신이면 기존 초안을 발행한다.
4. 신규 후보의 등록일이 더 최신이면 신규 수집 후보를 먼저 초안 생성 후 발행한다.
5. 후보가 없으면 신규 수집 필요 상태를 반환한다.

중요:
- 주말/휴가 등으로 예전 초안이 쌓였더라도,
  더 최신 네이버부동산 등록 매물이 있으면 신규 후보를 먼저 처리한다.
- 클라이언트 셋업파일도 동일한 기준을 사용한다.
"""

from dataclasses import dataclass
from datetime import datetime
from typing import Any


@dataclass
class TargetSelectionResult:
    found: bool
    action: str
    reason: str
    article_no: str | None = None
    draft_id: int | None = None
    publish_queue_id: int | None = None
    basis_date: Any | None = None
    row: dict[str, Any] | None = None
    compare: dict[str, Any] | None = None


class TargetSelector:
    def __init__(self, conn):
        self.conn = conn

    def select(self, realtor_id: int) -> TargetSelectionResult:
        realtor_id = int(realtor_id)

        queue_target = self.find_ready_publish_queue(realtor_id)
        if queue_target:
            return TargetSelectionResult(
                found=True,
                action="publish_existing_queue",
                reason="ready_publish_queue_exists",
                article_no=str(queue_target.get("article_no") or ""),
                draft_id=self._to_int(queue_target.get("draft_id")),
                publish_queue_id=self._to_int(queue_target.get("id")),
                basis_date=self._basis_date(queue_target),
                row=queue_target,
            )

        draft_target = self.find_latest_unpublished_draft(realtor_id)
        article_target = self.find_latest_uncreated_article(realtor_id)

        draft_date = self._basis_date(draft_target) if draft_target else None
        article_date = self._basis_date(article_target) if article_target else None

        compare = {
            "draft_article_no": str((draft_target or {}).get("article_no") or "") if draft_target else None,
            "draft_id": self._to_int((draft_target or {}).get("id")) if draft_target else None,
            "draft_basis_date": str(draft_date) if draft_date else None,
            "new_article_no": str((article_target or {}).get("article_no") or "") if article_target else None,
            "new_basis_date": str(article_date) if article_date else None,
        }

        if draft_target and not article_target:
            return TargetSelectionResult(
                found=True,
                action="publish_existing_draft",
                reason="unpublished_draft_exists_no_new_article",
                article_no=str(draft_target.get("article_no") or ""),
                draft_id=self._to_int(draft_target.get("id")),
                publish_queue_id=None,
                basis_date=draft_date,
                row=draft_target,
                compare=compare,
            )

        if article_target and not draft_target:
            return TargetSelectionResult(
                found=True,
                action="create_draft_then_publish",
                reason="new_article_exists_no_unpublished_draft",
                article_no=str(article_target.get("article_no") or ""),
                draft_id=None,
                publish_queue_id=None,
                basis_date=article_date,
                row=article_target,
                compare=compare,
            )

        if draft_target and article_target:
            # 네이버부동산 등록일/수집일 기준 비교.
            # 신규 후보가 더 최신이면 신규 후보 우선.
            # 같거나 기존 초안이 더 최신이면 기존 초안 우선.
            if article_date and draft_date and article_date > draft_date:
                return TargetSelectionResult(
                    found=True,
                    action="create_draft_then_publish",
                    reason="new_article_is_newer_than_unpublished_draft",
                    article_no=str(article_target.get("article_no") or ""),
                    draft_id=None,
                    publish_queue_id=None,
                    basis_date=article_date,
                    row=article_target,
                    compare=compare,
                )

            return TargetSelectionResult(
                found=True,
                action="publish_existing_draft",
                reason="unpublished_draft_is_same_or_newer_than_new_article",
                article_no=str(draft_target.get("article_no") or ""),
                draft_id=self._to_int(draft_target.get("id")),
                publish_queue_id=None,
                basis_date=draft_date,
                row=draft_target,
                compare=compare,
            )

        return TargetSelectionResult(
            found=False,
            action="collect_needed",
            reason="no_ready_queue_no_unpublished_draft_no_uncreated_article",
            compare=compare,
        )

    def find_ready_publish_queue(self, realtor_id: int):
        sql = """
            SELECT
                q.*,
                a.first_posted_at,
                a.created_at AS article_created_at,
                a.updated_at AS article_updated_at
            FROM blog_publish_queue q
            LEFT JOIN blog_realtor_articles a
                ON CONVERT(a.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                   =
                   CONVERT(q.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
            WHERE q.realtor_id = %s
              AND COALESCE(q.queue_status, '') IN ('waiting', 'pending', 'ready')
            ORDER BY
                COALESCE(a.first_posted_at, a.created_at, q.scheduled_at, q.created_at) DESC,
                q.id ASC
            LIMIT 1
        """

        with self.conn.cursor() as cur:
            cur.execute(sql, (int(realtor_id),))
            row = cur.fetchone()

        return dict(row) if row else None

    def find_latest_unpublished_draft(self, realtor_id: int):
        sql = """
            SELECT
                d.*,
                a.first_posted_at,
                a.created_at AS article_created_at,
                a.updated_at AS article_updated_at
            FROM blog_article_drafts d
            LEFT JOIN blog_realtor_articles a
                ON CONVERT(a.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                   =
                   CONVERT(d.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
            WHERE d.realtor_id = %s
              AND COALESCE(d.article_no, '') <> ''
              AND CONVERT(d.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                  NOT IN (
                    SELECT CONVERT(article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                    FROM blog_publish_queue
                    WHERE article_no IS NOT NULL
                      AND COALESCE(queue_status, '') IN (
                        'waiting',
                        'pending',
                        'ready',
                        'processing',
                        'published',
                        'success',
                        'done'
                      )
                  )
            ORDER BY
                COALESCE(a.first_posted_at, a.created_at, d.created_at) DESC,
                d.id DESC
            LIMIT 1
        """

        with self.conn.cursor() as cur:
            cur.execute(sql, (int(realtor_id),))
            row = cur.fetchone()

        return dict(row) if row else None

    def find_latest_uncreated_article(self, realtor_id: int):
        sql = """
            SELECT
                a.*
            FROM blog_realtor_articles a
            WHERE a.realtor_id = %s
              AND COALESCE(a.collect_status, '') = 'collected'
              AND COALESCE(a.detail_collected, 0) = 1
              AND COALESCE(a.article_name, '') NOT IN (
                    '',
                    '네이버 부동산 후보 매물',
                    '네이버 부동산 현재 매물',
                    '네이버 부동산 매물',
                    '부동산 매물',
                    '현재 매물',
                    '추천 매물'
              )
              AND COALESCE(a.price_text, '') <> ''
              AND EXISTS (
                    SELECT 1
                    FROM blog_realestate_article_images i
                    WHERE CONVERT(i.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                          =
                          CONVERT(a.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
              )
              AND CONVERT(a.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                  NOT IN (
                    SELECT CONVERT(article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                    FROM blog_article_drafts
                  )
              AND CONVERT(a.article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                  NOT IN (
                    SELECT CONVERT(article_no USING utf8mb4) COLLATE utf8mb4_unicode_ci
                    FROM blog_publish_queue
                    WHERE article_no IS NOT NULL
                      AND COALESCE(queue_status, '') IN (
                        'waiting',
                        'pending',
                        'ready',
                        'processing',
                        'published',
                        'success',
                        'done'
                      )
                  )
            ORDER BY
                COALESCE(a.first_posted_at, a.created_at) DESC,
                a.id DESC
            LIMIT 1
        """

        with self.conn.cursor() as cur:
            cur.execute(sql, (int(realtor_id),))
            row = cur.fetchone()

        return dict(row) if row else None

    def _basis_date(self, row):
        if not row:
            return None

        for key in [
            "first_posted_at",
            "article_created_at",
            "created_at",
            "scheduled_at",
            "updated_at",
        ]:
            value = row.get(key)
            if value:
                return value

        return None

    def _to_int(self, value):
        try:
            if value is None or value == "":
                return None
            return int(value)
        except Exception:
            return None