"""
shopprod_group winner 재선정 + content_token 정리/재생성 공통 정책 모듈 (asyncpg용).

Update_DomeDuplicate_4st*.py 들에서 공통으로 사용한다.
"""

from __future__ import annotations

from typing import Any, Optional, Tuple

try:
    import aiohttp
except Exception:  # pragma: no cover
    aiohttp = None  # type: ignore

try:
    from io import BytesIO
    from PIL import Image, ImageOps, UnidentifiedImageError
    import imagehash
except Exception:  # pragma: no cover
    Image = None  # type: ignore
    ImageOps = None  # type: ignore
    UnidentifiedImageError = Exception  # type: ignore
    imagehash = None  # type: ignore

# token 추출은 기존 정책과 동일하게 dome_group_match의 구현을 재사용한다.
try:
    import dome_group_match as _dgm
except Exception:  # pragma: no cover
    _dgm = None


GET_GROUP_WINNER_SQL = """
SELECT winner_icode, winner_vender_code, winner_price, group_key, phash_hex, dhash_hex
FROM mlinkdw.shopprod_group2
WHERE group_id = $1
LIMIT 1;
"""

# ✅ UI와 동일 기준:
# price(낮음) -> vender_grade(낮음) -> reg_date(오래된) -> icode(사전순)
BEST_WINNER_FROM_MAP_SQL = """
SELECT vender_code, icode, price, vender_grade, reg_date
FROM mlinkdw.shopprod_group_map2
WHERE group_id = $1
ORDER BY
  price NULLS LAST,
  CASE
    WHEN vender_grade ~ '^[0-9]+$' THEN vender_grade::int
    ELSE 2147483647
  END ASC,
  reg_date ASC NULLS LAST,
  icode ASC
LIMIT 1;
"""

UPDATE_GROUP_WINNER_SQL = """
UPDATE mlinkdw.shopprod_group2
SET winner_icode = $2,
    winner_vender_code = $3,
    winner_price = $4,
    winner_updated_at = now()
WHERE group_id = $1;
"""

DELETE_NON_WINNER_TOKENS_SQL = """
DELETE FROM mlinkdw.shopprod_group_content_token t
WHERE t.group_id = $1
  AND EXISTS (
    SELECT 1
    FROM mlinkdw.shopprod_group2 g
    WHERE g.group_id = t.group_id
      AND (t.vender_code IS DISTINCT FROM g.winner_vender_code
           OR t.icode IS DISTINCT FROM g.winner_icode)
  );
"""

FETCH_WINNER_CONTENT_SQL = """
SELECT content, dome_code
FROM mlinkdw.shopprod_group_map2
WHERE vender_code = $1 AND icode = $2
LIMIT 1;
"""

FETCH_WINNER_IMG_URL_SQL = """
SELECT img_url
FROM mlinkdw.shopprod_group_map2
WHERE vender_code = $1 AND icode = $2
LIMIT 1;
"""

UPDATE_GROUP_HASH_SQL = """
UPDATE mlinkdw.shopprod_group2
SET group_key = $2,
    phash_hex = $3,
    dhash_hex = $4,
    phash_bands = $5
WHERE group_id = $1;
"""

UPSERT_TOKEN_SQL = """
INSERT INTO mlinkdw.shopprod_group_content_token
  (group_id, vender_code, icode, token, dome_code)
VALUES ($1,$2,$3,$4,$5)
ON CONFLICT (group_id, vender_code, icode, token)
DO UPDATE SET dome_code = EXCLUDED.dome_code;
"""


def _rec_get(rec: Any, key: str, default=None):
    try:
        if rec is None:
            return default
        return rec[key]
    except Exception:
        try:
            return rec.get(key, default)  # type: ignore[attr-defined]
        except Exception:
            return default


async def refresh_group_winner_and_tokens_if_needed(
    conn,
    group_id: int,
    *,
    max_imgs: int = 12,
    max_filenames: int = 8,
    min_fname_len: int = 6,
) -> bool:
    """
    group_id의 winner를 재선정하고, winner가 바뀌면 content_token 정책을 동일하게 적용한다.
    - 반환값: winner가 실제로 변경되었으면 True, 아니면 False.
    """
    try:
        gid = int(group_id)
    except Exception:
        return False

    # 1) 현재 winner
    try:
        cur_w = await conn.fetchrow(GET_GROUP_WINNER_SQL, gid)
    except Exception:
        cur_w = None

    # 2) map 기준 best winner
    try:
        best = await conn.fetchrow(BEST_WINNER_FROM_MAP_SQL, gid)
    except Exception:
        best = None

    if not best:
        return False

    best_icode = str(_rec_get(best, "icode", "") or "")
    best_vcode = str(_rec_get(best, "vender_code", "") or "")
    best_price = _rec_get(best, "price", None)

    cur_icode = str(_rec_get(cur_w, "winner_icode", "") or "") if cur_w else ""
    cur_vcode = str(_rec_get(cur_w, "winner_vender_code", "") or "") if cur_w else ""
    cur_price = _rec_get(cur_w, "winner_price", None) if cur_w else None

    cur_group_key = str(_rec_get(cur_w, "group_key", "") or "") if cur_w else ""
    cur_ph_hex = str(_rec_get(cur_w, "phash_hex", "") or "") if cur_w else ""
    cur_dh_hex = str(_rec_get(cur_w, "dhash_hex", "") or "") if cur_w else ""

    same_winner = (best_icode, best_vcode, best_price) == (cur_icode, cur_vcode, cur_price)

    # winner가 동일해도 해시 필드가 누락된 경우가 있으므로 보정 필요
    need_hash_fix = (not cur_group_key) or (not cur_ph_hex) or (not cur_dh_hex)

    if same_winner and (not need_hash_fix):
        return False

    # 3) winner 갱신(변경된 경우에만)
    if not same_winner:
        await conn.execute(UPDATE_GROUP_WINNER_SQL, gid, best_icode, best_vcode, best_price)

    # 3.5) ✅ shopprod_group2 해시 필드 갱신(승자 변경 또는 누락 시)
    if (not same_winner) or need_hash_fix:
        try:
            img_url = await conn.fetchval(FETCH_WINNER_IMG_URL_SQL, best_vcode, best_icode)
        except Exception:
            img_url = None

        if img_url and aiohttp is not None and Image is not None and ImageOps is not None and imagehash is not None:
            try:
                timeout = aiohttp.ClientTimeout(total=8)
                async with aiohttp.ClientSession(timeout=timeout) as session:
                    async with session.get(str(img_url)) as r:
                        if r.status == 200:
                            data = await r.read()
                        else:
                            data = None
            except Exception:
                data = None

            if data:
                try:
                    bio = BytesIO(data)
                    img = Image.open(bio)
                    try:
                        img.verify()
                    except Exception:
                        pass
                    bio.seek(0)
                    img = Image.open(bio)
                    img = ImageOps.exif_transpose(img)
                    img = img.convert("RGB")
                    ph = imagehash.phash(img)
                    dh = imagehash.dhash(img)
                    ph_hex = str(ph)
                    dh_hex = str(dh)
                    group_key = f"{ph_hex}_{dh_hex}"
                    bands = _dgm.bands_from_phash_hex(ph_hex) if _dgm is not None else []

                    # UNIQUE(group_key) 충돌 등은 본 흐름을 막지 않도록 예외를 삼킨다.
                    try:
                        await conn.execute(UPDATE_GROUP_HASH_SQL, gid, group_key, ph_hex, dh_hex, bands)
                    except Exception:
                        pass
                except Exception:
                    pass

    # 4) content_token 정책 적용 (winner 변경 시에만)
    if same_winner:
        return True  # 해시만 보정한 경우
    try:
        await conn.execute(DELETE_NON_WINNER_TOKENS_SQL, gid)
    except Exception:
        # token 테이블/권한/스키마 미존재 등은 본 흐름을 막지 않는다.
        return True

    # winner content/dome_code fetch
    try:
        rr = await conn.fetchrow(FETCH_WINNER_CONTENT_SQL, best_vcode, best_icode)
    except Exception:
        rr = None

    content_html = str(_rec_get(rr, "content", "") or "") if rr else ""
    dome_code = _rec_get(rr, "dome_code", None) if rr else None
    dome_code_s = str(dome_code) if dome_code is not None else ""

    if not content_html or not dome_code_s:
        return True

    if _dgm is None:
        return True

    try:
        toks = _dgm._extract_img_filenames_from_html(  # type: ignore[attr-defined]
            content_html,
            max_imgs=int(max_imgs),
            max_filenames=int(max_filenames),
            min_fname_len=int(min_fname_len),
        )
    except Exception:
        toks = []

    if not toks:
        return True

    # upsert tokens
    try:
        # asyncpg executemany는 (sql, args_iterable) 형태
        await conn.executemany(
            UPSERT_TOKEN_SQL,
            [(gid, best_vcode, best_icode, str(t), dome_code_s) for t in toks],
        )
    except Exception:
        # 토큰 삽입 실패도 본 흐름을 막지 않는다.
        pass

    return True