from __future__ import annotations

import io
import argparse
import json
import os
import time
import uuid
from pathlib import Path
from urllib.error import HTTPError, URLError
from urllib.parse import quote, urlencode
from urllib.request import Request, urlopen

import boto3
import mysql.connector
from PIL import Image, ImageOps


ROOT_DIR = Path(__file__).resolve().parents[1]
DEFAULT_ENV_PATH = ROOT_DIR / ".env"

NCP_ACCESS_KEY = "ncp_iam_BPASKR5aqRFd5UVurpnn"
NCP_SECRET_KEY = "ncp_iam_BPKSKR33fSB43WgsitoAeirg4QGRNecX5p"
NCP_BUCKET_NAME = "babynote"
NCP_ENDPOINT = "https://kr.object.ncloudstorage.com"
CDN_DOMAIN = "https://uscppbpkqffl28953595.gcdn.ntruss.com"

SEARCH_OVERRIDES = {
    "피쉬본 선인장": ["Disocactus anguliger", "Fishbone Cactus"],
    "에피필룸 옥스페탈룸": ["Epiphyllum oxypetalum"],
    "부다스 템플": ["Crassula pyramidalis", "Buddha's Temple"],
    "세로페기아 우디": ["Ceropegia woodii", "String of Hearts"],
    "센시오 페레그리누스": ["Senecio peregrinus", "String of Dolphins"],
    "네온 포토스": ["Epipremnum aureum Neon", "Neon Pothos"],
    "에피프렘넘 엔조이": ["Epipremnum aureum N'Joy", "Epipremnum N'Joy"],
    "몬스테라 알보": ["Monstera deliciosa albo variegata", "Monstera Albo"],
    "필로덴드론 브라질": ["Philodendron hederaceum Brasil", "Philodendron Brasil"],
    "필로덴드론 핑크 프린세스": ["Philodendron Pink Princess"],
    "안스리움 클라리네르비움": ["Anthurium clarinervium"],
    "안스리움 베이치": ["Anthurium veitchii"],
    "베고니아 마큘라": ["Begonia maculata"],
    "베고니아 렉스": ["Begonia rex"],
    "페퍼로미아 아르гир레이아": ["Peperomia argyreia"],
    "페퍼로미아 카페라타": ["Peperomia caperata"],
    "아글라오네마 실버퀸": ["Aglaonema Silver Queen"],
    "아글라오네마 레드발렌타인": ["Aglaonema Red Valentine"],
    "싱고니움 알보": ["Syngonium podophyllum albo variegata", "Syngonium Albo"],
    "싱고니움 핑크 스플래시": ["Syngonium Pink Splash"],
    "칼라디움 화이트퀸": ["Caladium White Queen"],
    "스트로만테 트리오스타": ["Stromanthe sanguinea Triostar", "Stromanthe Triostar"],
    "칼라테아 오르비폴리아": ["Calathea orbifolia"],
    "알로카시아 프라이덱": ["Alocasia micholitziana Frydek", "Alocasia Frydek"],
    "알로카시아 아마조니카": ["Alocasia x amazonica", "Alocasia Amazonica"],
    "디펜바키아 카밀라": ["Dieffenbachia Camilla"],
    "드라세나 송 오브 인디아": ["Dracaena reflexa Song of India"],
    "드라세나 레몬라임": ["Dracaena Lemon Lime"],
    "아디안텀": ["Adiantum", "Maidenhair Fern"],
    "플래티세리움": ["Platycerium", "Staghorn Fern"],
    "블루스타펀": ["Phlebodium aureum Blue Star", "Blue Star Fern"],
    "악어고사리": ["Microsorum musifolium Crocodyllus", "Crocodile Fern"],
    "아스파라거스 펀": ["Asparagus setaceus", "Asparagus Fern"],
    "셀라기넬라": ["Selaginella"],
    "접란": ["Chlorophytum comosum", "Spider Plant"],
    "클로로피텀 보니": ["Chlorophytum comosum Bonnie"],
    "루도시아 디스컬러": ["Ludisia discolor"],
    "마코데스 페톨라": ["Macodes petola"],
    "보석란": ["Jewel Orchid", "Ludisia discolor"],
    "바닐라 오키드": ["Vanilla planifolia", "Vanilla Orchid"],
    "온시디움": ["Oncidium orchid", "Oncidium"],
    "반다": ["Vanda orchid", "Vanda"],
    "파피오페딜룸": ["Paphiopedilum"],
    "지고페탈룸": ["Zygopetalum"],
    "무스카리": ["Muscari"],
    "수선화": ["Narcissus", "Daffodil"],
    "히아신스": ["Hyacinthus orientalis", "Hyacinth"],
    "아이리스": ["Iris flower", "Iris"],
    "프리뮬라": ["Primula"],
    "캄파눌라": ["Campanula"],
    "스위트피": ["Lathyrus odoratus", "Sweet Pea"],
    "접시꽃": ["Alcea rosea", "Hollyhock"],
    "델피니움": ["Delphinium"],
    "루피너스": ["Lupinus", "Lupine"],
    "헬레보루스": ["Helleborus", "Hellebore"],
    "에키네시아": ["Echinacea purpurea", "Coneflower"],
    "살비아 파리나세아": ["Salvia farinacea"],
    "가자니아": ["Gazania"],
    "브라키컴": ["Brachyscome"],
    "디아스시아": ["Diascia"],
    "네메시아": ["Nemesia"],
    "스카비오사": ["Scabiosa"],
    "아스틸베": ["Astilbe"],
    "숙근안개초": ["Gypsophila paniculata", "Perennial Baby's Breath"],
    "시네라리아": ["Pericallis x hybrida", "Florist's Cineraria"],
    "클리비아": ["Clivia miniata", "Clivia"],
    "아마릴리스": ["Hippeastrum", "Amaryllis"],
    "글록시니아": ["Sinningia speciosa", "Gloxinia"],
    "아프리칸 바이올렛": ["Saintpaulia", "African Violet"],
    "파키스타키스": ["Pachystachys lutea"],
    "에스키난서스": ["Aeschynanthus", "Lipstick Plant"],
    "콜룸네아": ["Columnea", "Goldfish Plant"],
    "펜타스": ["Pentas lanceolata", "Pentas"],
    "란타나": ["Lantana camara", "Lantana"],
    "토레니아 카우아이": ["Torenia fournieri", "Torenia Kauai"],
    "로벨리아 리차르디": ["Lobelia richardii", "Trailing Lobelia"],
    "옥살리스 트라이앵귤라리스": ["Oxalis triangularis"],
    "헤데라 글레이셔": ["Hedera helix Glacier"],
    "크리핑 피그": ["Ficus pumila", "Creeping Fig"],
    "페페로미아 프로스트": ["Peperomia caperata Frost", "Peperomia Frost"],
    "칼리시아 레펜스": ["Callisia repens"],
    "프테리스 고사리": ["Pteris fern", "Pteris"],
    "헤미그래피스": ["Hemigraphis alternata", "Purple Waffle Plant"],
    "솔레이롤리아": ["Soleirolia soleirolii", "Baby's Tears"],
}


def load_env(env_path: str) -> None:
    path = Path(env_path)
    if not path.exists():
        return
    for line in path.read_text(encoding="utf-8").splitlines():
        line = line.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        key, value = line.split("=", 1)
        os.environ.setdefault(key.strip(), value.strip())


def create_db_connection():
    return mysql.connector.connect(
        host=os.environ["DB_HOST"],
        port=int(os.environ.get("DB_PORT", "3306")),
        user=os.environ["DB_USER"],
        password=os.environ["DB_PASSWORD"],
        database=os.environ["DB_NAME"],
        charset="utf8mb4",
    )


def create_s3_client():
    session = boto3.session.Session()
    return session.client(
        "s3",
        region_name="kr-standard",
        endpoint_url=NCP_ENDPOINT,
        aws_access_key_id=NCP_ACCESS_KEY,
        aws_secret_access_key=NCP_SECRET_KEY,
    )


def parse_args():
    parser = argparse.ArgumentParser(description="Backfill missing plant preset images from Wikimedia Commons to NCP.")
    parser.add_argument("--limit", type=int, default=None)
    parser.add_argument("--offset", type=int, default=0)
    return parser.parse_args()


def fetch_json(url: str, attempt: int = 1) -> dict:
    req = Request(url, headers={"User-Agent": "Codex Plant Image Backfill/1.0", "Connection": "close"})
    try:
        with urlopen(req, timeout=30) as response:
            return json.loads(response.read().decode("utf-8"))
    except (HTTPError, URLError):
        if attempt >= 5:
            raise
        time.sleep(attempt * 2)
        return fetch_json(url, attempt + 1)


def fetch_bytes(url: str, attempt: int = 1) -> bytes:
    req = Request(url, headers={"User-Agent": "Codex Plant Image Backfill/1.0", "Connection": "close"})
    try:
        with urlopen(req, timeout=30) as response:
            return response.read()
    except (HTTPError, URLError):
        if attempt >= 5:
            raise
        time.sleep(attempt * 2)
        return fetch_bytes(url, attempt + 1)


def build_queries(type_name: str, type_name_en: str) -> list[str]:
    queries: list[str] = []
    for query in SEARCH_OVERRIDES.get(type_name, []):
        if query not in queries:
            queries.append(query)
    for query in [type_name_en, type_name_en.replace("'", ""), " ".join(type_name_en.split()[:2])]:
        query = (query or "").strip()
        if query and query not in queries:
            queries.append(query)
    return queries


def search_commons_image(query: str) -> str | None:
    params = {
        "action": "query",
        "format": "json",
        "generator": "search",
        "gsrsearch": query,
        "gsrnamespace": "6",
        "gsrlimit": "8",
        "prop": "imageinfo",
        "iiprop": "url|mime",
        "iiurlwidth": "1024",
    }
    url = "https://commons.wikimedia.org/w/api.php?" + urlencode(params)
    data = fetch_json(url)
    pages = (data.get("query") or {}).get("pages") or {}
    preferred_ext = (".jpg", ".jpeg", ".png", ".webp")
    candidates: list[tuple[int, str]] = []
    for page in pages.values():
        title = page.get("title") or ""
        info = (page.get("imageinfo") or [{}])[0]
        mime = info.get("mime") or ""
        img_url = info.get("thumburl") or info.get("url") or ""
        if not img_url or not mime.startswith("image/") or mime == "image/svg+xml":
            continue
        score = 0
        lowered = title.lower()
        for token in query.lower().replace("-", " ").split():
            if token and token in lowered:
                score += 2
        if img_url.lower().endswith(preferred_ext):
            score += 1
        candidates.append((score, img_url))
    if not candidates:
        return None
    candidates.sort(key=lambda item: item[0], reverse=True)
    return candidates[0][1]


def optimize_to_webp(image_bytes: bytes) -> bytes:
    image = Image.open(io.BytesIO(image_bytes))
    image = ImageOps.exif_transpose(image).convert("RGB")
    image.thumbnail((512, 512))
    canvas = Image.new("RGB", (512, 512), (255, 255, 255))
    left = (512 - image.width) // 2
    top = (512 - image.height) // 2
    canvas.paste(image, (left, top))
    output = io.BytesIO()
    canvas.save(output, format="WEBP", quality=88, method=6)
    return output.getvalue()


def upload_to_ncp(s3_client, image_bytes: bytes) -> tuple[str, str]:
    optimized = optimize_to_webp(image_bytes)
    image_path = f"plant-reminder/presets/{uuid.uuid4()}.webp"
    s3_client.put_object(
        Bucket=NCP_BUCKET_NAME,
        Key=image_path,
        Body=optimized,
        ContentType="image/webp",
        ACL="private",
    )
    return f"{CDN_DOMAIN}/{image_path}", image_path


def main() -> None:
    args = parse_args()
    load_env(str(DEFAULT_ENV_PATH))
    conn = create_db_connection()
    s3_client = create_s3_client()
    cursor = conn.cursor(dictionary=True)
    updated = 0
    failed: list[str] = []

    try:
        cursor.execute(
            """
            SELECT id, type_name, type_name_en
            FROM plant_reminder_presets
            WHERE image_url IS NULL OR image_url = ''
            ORDER BY id ASC
            """
        )
        rows = cursor.fetchall()
        if args.offset:
            rows = rows[args.offset:]
        if args.limit is not None:
            rows = rows[:args.limit]

        for row in rows:
            queries = build_queries(row["type_name"], row["type_name_en"])
            source_url = None
            print(f"[search] {row['type_name']} queries={queries}", flush=True)
            for query in queries:
                source_url = search_commons_image(query)
                if source_url:
                    break
            if not source_url:
                failed.append(row["type_name"])
                print(f"[no-image] {row['type_name']}", flush=True)
                continue

            try:
                image_bytes = fetch_bytes(source_url)
                image_url, image_path = upload_to_ncp(s3_client, image_bytes)
                cursor.execute(
                    """
                    UPDATE plant_reminder_presets
                    SET image_url = %s, image_path = %s, updated_at = NOW()
                    WHERE id = %s
                    """,
                    (image_url, image_path, row["id"]),
                )
                conn.commit()
                updated += 1
                print(f"[updated] {row['type_name']} <- {source_url}", flush=True)
                time.sleep(0.3)
            except Exception as exc:
                failed.append(row["type_name"])
                print(f"[failed] {row['type_name']} <- {source_url} :: {exc}", flush=True)

        print(json.dumps({"updated": updated, "failed": failed, "failed_count": len(failed)}, ensure_ascii=False, indent=2))
    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    main()