"""
Backfill or update existing plant reminder presets with reviewed multilingual text.

Use this when you want to update existing rows like the current 116 presets
with real ko/en/ja/zh names and plant-specific sunlight / care tips.

Input format: NDJSON, one object per line.

Required fields per line:
{
  "type_name": "아글라오네마",
  "watering_cycle_days": 7,
  "translations": {
    "ko": {"type_name": "아글라오네마", "sunlight": "밝은 간접광", "tip": "겉흙이 마르면 물을 주고 잎 상태를 자주 확인해주세요."},
    "en": {"type_name": "Aglaonema", "sunlight": "Bright indirect light", "tip": "Water when the topsoil dries and check the leaves regularly."},
    "ja": {"type_name": "アグラオネマ", "sunlight": "明るい間接光", "tip": "表土が乾いたら水やりし、葉の状態をこまめに確認してください。"},
    "zh": {"type_name": "广东万年青", "sunlight": "明亮散射光", "tip": "表土变干后浇水，并经常检查叶片状态。"}
  }
}
"""

from __future__ import annotations

import argparse
import json
import os
from pathlib import Path
from typing import Dict, Iterable

import mysql.connector


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


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(description="Backfill multilingual plant preset text from NDJSON.")
    parser.add_argument("--file", required=True, help="Path to NDJSON worklist")
    parser.add_argument("--env-file", default=str(DEFAULT_ENV_PATH), help="Path to .env file")
    parser.add_argument("--dry-run", action="store_true", help="Validate only without updating DB")
    return parser.parse_args()


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():
        if not line or line.strip().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 iter_ndjson(path: str) -> Iterable[Dict]:
    with open(path, "r", encoding="utf-8") as fp:
        for line_number, line in enumerate(fp, start=1):
            line = line.strip()
            if not line:
                continue
            try:
                yield json.loads(line)
            except json.JSONDecodeError as exc:
                raise ValueError(f"NDJSON parse error at line {line_number}: {exc}") from exc


def normalize_entry(entry: Dict) -> Dict:
    translations = entry.get("translations") or {}
    ko = translations.get("ko") or {}
    en = translations.get("en") or {}
    ja = translations.get("ja") or {}
    zh = translations.get("zh") or {}

    type_name = (entry.get("type_name") or ko.get("type_name") or "").strip()
    if not type_name:
        raise ValueError("type_name is required")

    sunlight_ko = (ko.get("sunlight") or entry.get("sunlight") or "").strip()
    tip_ko = (ko.get("tip") or entry.get("tip") or "").strip()

    return {
        "type_name": type_name,
        "watering_cycle_days": int(entry.get("watering_cycle_days") or 7),
        "type_name_ko": (ko.get("type_name") or type_name).strip(),
        "type_name_en": (en.get("type_name") or type_name).strip(),
        "type_name_ja": (ja.get("type_name") or type_name).strip(),
        "type_name_zh": (zh.get("type_name") or type_name).strip(),
        "sunlight": sunlight_ko,
        "sunlight_ko": sunlight_ko,
        "sunlight_en": (en.get("sunlight") or sunlight_ko).strip(),
        "sunlight_ja": (ja.get("sunlight") or sunlight_ko).strip(),
        "sunlight_zh": (zh.get("sunlight") or sunlight_ko).strip(),
        "tip": tip_ko,
        "tip_ko": tip_ko,
        "tip_en": (en.get("tip") or tip_ko).strip(),
        "tip_ja": (ja.get("tip") or tip_ko).strip(),
        "tip_zh": (zh.get("tip") or tip_ko).strip(),
    }


def main() -> None:
    args = parse_args()
    load_env(args.env_file)

    file_path = Path(args.file)
    if not file_path.exists():
        raise FileNotFoundError(f"Worklist not found: {file_path}")

    conn = create_db_connection()
    cursor = conn.cursor()

    updated = 0
    missing = []

    try:
        for raw_entry in iter_ndjson(str(file_path)):
            entry = normalize_entry(raw_entry)

            cursor.execute("SELECT id FROM plant_reminder_presets WHERE type_name = %s LIMIT 1", (entry["type_name"],))
            row = cursor.fetchone()
            if not row:
                missing.append(entry["type_name"])
                continue

            if args.dry_run:
                print(f"[dry-run] {entry['type_name']}")
                updated += 1
                continue

            cursor.execute(
                """
                UPDATE plant_reminder_presets
                SET
                  watering_cycle_days = %s,
                  type_name = %s,
                  type_name_ko = %s,
                  type_name_en = %s,
                  type_name_ja = %s,
                  type_name_zh = %s,
                  sunlight = %s,
                  sunlight_ko = %s,
                  sunlight_en = %s,
                  sunlight_ja = %s,
                  sunlight_zh = %s,
                  tip = %s,
                  tip_ko = %s,
                  tip_en = %s,
                  tip_ja = %s,
                  tip_zh = %s,
                  updated_at = NOW()
                WHERE id = %s
                """,
                (
                    entry["watering_cycle_days"],
                    entry["type_name_ko"],
                    entry["type_name_ko"],
                    entry["type_name_en"],
                    entry["type_name_ja"],
                    entry["type_name_zh"],
                    entry["sunlight_ko"],
                    entry["sunlight_ko"],
                    entry["sunlight_en"],
                    entry["sunlight_ja"],
                    entry["sunlight_zh"],
                    entry["tip_ko"],
                    entry["tip_ko"],
                    entry["tip_en"],
                    entry["tip_ja"],
                    entry["tip_zh"],
                    row[0],
                ),
            )
            conn.commit()
            updated += 1
            if updated % 25 == 0:
                print(f"[progress] updated={updated}")

        print(json.dumps({
            "file": str(file_path),
            "updated": updated,
            "missing_in_db": missing,
        }, ensure_ascii=False, indent=2))
    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    main()