"""
Export current plant reminder presets into an NDJSON worklist for manual review.

This is useful for building the reviewed multilingual catalog that
`backfill_plant_reminder_preset_translations.py` will later import.
"""

from __future__ import annotations

import argparse
import json
import os
from pathlib import Path

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="Export current plant presets to NDJSON worklist.")
    parser.add_argument("--out", required=True, help="Output NDJSON path")
    parser.add_argument("--env-file", default=str(DEFAULT_ENV_PATH), help="Path to .env file")
    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 main() -> None:
    args = parse_args()
    load_env(args.env_file)

    output_path = Path(args.out)
    output_path.parent.mkdir(parents=True, exist_ok=True)

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

    try:
        cursor.execute(
            """
            SELECT
              type_name,
              watering_cycle_days,
              image_url,
              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
            """
        )
        rows = cursor.fetchall()

        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"],
                    "image_url": row["image_url"],
                    "translations": {
                        "ko": {
                            "type_name": row.get("type_name_ko") or row["type_name"],
                            "sunlight": row.get("sunlight_ko") or "",
                            "tip": row.get("tip_ko") or "",
                        },
                        "en": {
                            "type_name": row.get("type_name_en") or row["type_name"],
                            "sunlight": row.get("sunlight_en") or "",
                            "tip": row.get("tip_en") or "",
                        },
                        "ja": {
                            "type_name": row.get("type_name_ja") or row["type_name"],
                            "sunlight": row.get("sunlight_ja") or "",
                            "tip": row.get("tip_ja") or "",
                        },
                        "zh": {
                            "type_name": row.get("type_name_zh") or row["type_name"],
                            "sunlight": row.get("sunlight_zh") or "",
                            "tip": row.get("tip_zh") or "",
                        },
                    },
                }
                fp.write(json.dumps(payload, ensure_ascii=False) + "\n")

        print(json.dumps({"out": str(output_path), "count": len(rows)}, ensure_ascii=False, indent=2))
    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    main()