"""
Dedicated translation workflow for the current plant_reminder_presets rows.

This script is intentionally scoped to the current DB contents, so we can:
- Export the current preset set to a review file.
- Validate that the reviewed file still matches the DB set.
- Apply reviewed ko/en/ja/zh translations and plant-specific care text back into DB.

Typical usage
1) Export current rows:
   python scripts/backfill_current_plant_reminder_116_translations.py export

2) Fill the generated NDJSON file with reviewed translations.

3) Apply it:
   python scripts/backfill_current_plant_reminder_116_translations.py apply

Optional
- --file scripts/data/plant_reminder_translation_worklist.current.ndjson
- --expected-count 116
- --dry-run
"""

from __future__ import annotations

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

import mysql.connector


ROOT_DIR = Path(__file__).resolve().parents[1]
DEFAULT_ENV_PATH = ROOT_DIR / ".env"
DEFAULT_WORKLIST_PATH = ROOT_DIR / "scripts" / "data" / "plant_reminder_translation_worklist.current.ndjson"


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(description="Current plant reminder translation workflow")
    parser.add_argument("mode", choices=["export", "apply"], help="Workflow step to run")
    parser.add_argument("--file", default=str(DEFAULT_WORKLIST_PATH), help="NDJSON worklist path")
    parser.add_argument("--expected-count", type=int, default=116, help="Expected row count for safety")
    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 DB update")
    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 fetch_current_presets(cursor) -> List[Dict]:
    cursor.execute(
        """
        SELECT
          id,
          type_name,
          watering_cycle_days,
          image_url,
          image_path,
          sort_order,
          type_name_ko, type_name_en, type_name_ja, type_name_zh,
          sunlight_ko, sunlight_en, sunlight_ja, sunlight_zh,
          tip_ko, tip_en, tip_ja, tip_zh
        FROM plant_reminder_presets
        ORDER BY sort_order ASC, id ASC
        """
    )
    return cursor.fetchall()


def export_worklist(rows: List[Dict], output_path: Path) -> None:
    output_path.parent.mkdir(parents=True, exist_ok=True)
    with output_path.open("w", encoding="utf-8") as fp:
        for row in rows:
            payload = {
                "type_name": row["type_name"],
                "watering_cycle_days": row["watering_cycle_days"],
                "sort_order": row["sort_order"],
                "image_url": row["image_url"],
                "image_path": row["image_path"],
                "translations": {
                    "ko": {
                        "type_name": row["type_name_ko"] or row["type_name"],
                        "sunlight": row["sunlight_ko"] or "",
                        "tip": row["tip_ko"] or "",
                    },
                    "en": {
                        "type_name": row["type_name_en"] or row["type_name"],
                        "sunlight": row["sunlight_en"] or "",
                        "tip": row["tip_en"] or "",
                    },
                    "ja": {
                        "type_name": row["type_name_ja"] or row["type_name"],
                        "sunlight": row["sunlight_ja"] or "",
                        "tip": row["tip_ja"] or "",
                    },
                    "zh": {
                        "type_name": row["type_name_zh"] or row["type_name"],
                        "sunlight": row["sunlight_zh"] or "",
                        "tip": row["tip_zh"] or "",
                    },
                },
            }
            fp.write(json.dumps(payload, ensure_ascii=False) + "\n")


def iter_ndjson(path: Path) -> Iterable[Dict]:
    with path.open("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")

    type_name_ko = (ko.get("type_name") or type_name).strip()
    type_name_en = (en.get("type_name") or "").strip()
    type_name_ja = (ja.get("type_name") or "").strip()
    type_name_zh = (zh.get("type_name") or "").strip()

    sunlight_ko = (ko.get("sunlight") or "").strip()
    sunlight_en = (en.get("sunlight") or "").strip()
    sunlight_ja = (ja.get("sunlight") or "").strip()
    sunlight_zh = (zh.get("sunlight") or "").strip()

    tip_ko = (ko.get("tip") or "").strip()
    tip_en = (en.get("tip") or "").strip()
    tip_ja = (ja.get("tip") or "").strip()
    tip_zh = (zh.get("tip") or "").strip()

    required = [
        ("type_name_ko", type_name_ko),
        ("type_name_en", type_name_en),
        ("type_name_ja", type_name_ja),
        ("type_name_zh", type_name_zh),
        ("sunlight_ko", sunlight_ko),
        ("sunlight_en", sunlight_en),
        ("sunlight_ja", sunlight_ja),
        ("sunlight_zh", sunlight_zh),
        ("tip_ko", tip_ko),
        ("tip_en", tip_en),
        ("tip_ja", tip_ja),
        ("tip_zh", tip_zh),
    ]
    missing = [name for name, value in required if not value]
    if missing:
        raise ValueError(f"{type_name}: missing required fields -> {', '.join(missing)}")

    generic_markers = [
        "흙 표면이 마르면 물을 주고 잎 상태를 자주 확인해주세요.",
        "꽃이 오래가도록 겉흙이 마르기 전에 상태를 확인해주세요.",
        "통풍과 일조를 확보하고 흙 속까지 어느 정도 마른 뒤 물을 주세요.",
        "과습에 약하니 흙이 충분히 마른 뒤 물을 주세요.",
        "흙이 너무 마르지 않게 자주 확인하고 수확 후에는 통풍을 챙겨주세요.",
        "줄기 끝이 축 처지기 전에 물을 주고 지지대를 함께 관리해주세요.",
        "흙이 마르지 않게 유지하고 가능하면 깨끗한 물로 관리해주세요.",
    ]
    if any(tip_ko.startswith(marker) for marker in generic_markers):
        raise ValueError(f"{type_name}: generic ko tip is still present, replace with plant-specific care text")

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


def validate_against_db(rows: List[Dict], entries: List[Dict], expected_count: int) -> Tuple[set, set]:
    db_names = {row["type_name"] for row in rows}
    file_names = {entry["type_name"] for entry in entries}

    if len(rows) != expected_count:
        raise ValueError(f"DB row count mismatch: expected {expected_count}, got {len(rows)}")
    if len(entries) != expected_count:
        raise ValueError(f"Worklist row count mismatch: expected {expected_count}, got {len(entries)}")
    if len(file_names) != len(entries):
        raise ValueError("Worklist contains duplicate type_name rows")

    missing_in_file = db_names - file_names
    missing_in_db = file_names - db_names
    if missing_in_file or missing_in_db:
        raise ValueError(
            "Worklist and DB names differ:\n"
            f"missing_in_file={sorted(missing_in_file)}\n"
            f"missing_in_db={sorted(missing_in_db)}"
        )

    return db_names, file_names


def apply_entries(cursor, entries: List[Dict], dry_run: bool) -> int:
    updated = 0
    warnings = 0
    for entry in entries:
        if entry["type_name_en"] == entry["type_name_ko"]:
            warnings += 1
        if entry["type_name_ja"] == entry["type_name_ko"]:
            warnings += 1
        if entry["type_name_zh"] == entry["type_name_ko"]:
            warnings += 1

        if dry_run:
            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 type_name = %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"],
                entry["type_name"],
            ),
        )
        updated += 1

    if warnings:
        print(f"[warning] {warnings} translated fields are identical to ko text. Review recommended.")
    return updated


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

    conn = create_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        rows = fetch_current_presets(cursor)

        if args.mode == "export":
            export_worklist(rows, worklist_path)
            print(json.dumps({
                "mode": "export",
                "file": str(worklist_path),
                "count": len(rows),
            }, ensure_ascii=False, indent=2))
            return

        if not worklist_path.exists():
            raise FileNotFoundError(f"Worklist not found: {worklist_path}")

        entries = [normalize_entry(entry) for entry in iter_ndjson(worklist_path)]
        validate_against_db(rows, entries, args.expected_count)
        updated = apply_entries(cursor, entries, args.dry_run)

        if not args.dry_run:
            conn.commit()

        print(json.dumps({
            "mode": "apply",
            "file": str(worklist_path),
            "updated": updated,
            "dry_run": args.dry_run,
        }, ensure_ascii=False, indent=2))
    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    main()