from __future__ import annotations

import csv
import json
import os
from pathlib import Path

import mysql.connector


ROOT_DIR = Path(__file__).resolve().parents[1]
DEFAULT_ENV_PATH = ROOT_DIR / ".env"
DEFAULT_JSON_PATH = ROOT_DIR / "scripts" / "data" / "latin_fallback_presets.json"
DEFAULT_CSV_PATH = ROOT_DIR / "scripts" / "data" / "latin_fallback_presets.csv"


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 main() -> None:
    load_env(str(DEFAULT_ENV_PATH))
    conn = create_db_connection()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(
            """
            SELECT id, type_name, type_name_en, type_name_ja, type_name_zh, sunlight, tip, image_url
            FROM plant_reminder_presets
            WHERE is_active = 1
              AND type_name REGEXP '^[A-Za-z][A-Za-z .''-]+$'
            ORDER BY type_name ASC
            """
        )
        rows = cursor.fetchall()

        DEFAULT_JSON_PATH.parent.mkdir(parents=True, exist_ok=True)
        DEFAULT_JSON_PATH.write_text(json.dumps(rows, ensure_ascii=False, indent=2), encoding="utf-8")

        with DEFAULT_CSV_PATH.open("w", encoding="utf-8-sig", newline="") as fp:
            writer = csv.DictWriter(fp, fieldnames=["id", "type_name", "type_name_en", "type_name_ja", "type_name_zh", "sunlight", "tip", "image_url"])
            writer.writeheader()
            writer.writerows(rows)

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


if __name__ == "__main__":
    main()