#!/usr/bin/env python3
"""
One-time import of seed inventory from a tab/space-delimited text file.

Format per line: <storage_location> <crop and variety> [tab] [source]

Example:
  49  Cucumber H-19 Little Leaf OG    Johnny's

Usage:
  cd /path/to/Server
  python scripts/import_garden_seeds.py
  python scripts/import_garden_seeds.py --file /path/to/seeds.txt --dry-run
"""
from __future__ import annotations

import argparse
import os
import re
import sys

_BASE = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
if _BASE not in sys.path:
    sys.path.insert(0, _BASE)

import pymysql

from blueprints.gardentracker import ensure_garden_tables
from config import MYSQL_DATABASE, MYSQL_HOST, MYSQL_PASSWORD, MYSQL_PORT, MYSQL_USER

DEFAULT_SEEDS_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), "seeds.txt")

# Longest match first when splitting crop vs variety.
CROP_PREFIXES = [
    "Cherry Tomato",
    "Butterhead Lettuce",
    "Mustard Greens",
    "Bell Pepper",
    "Banana Pepper",
    "Spaghetti Squash",
    "Summer Squash",
    "Winter Squash",
    "Asparagus Beans",
    "English Daisy",
    "Pole Bean",
    "Lady Pea",
    "Thai Basil",
    "Tomato",
    "Rutabaga",
    "Cucumber",
    "Basil",
    "Parsley",
    "Rosemary",
    "Chives",
    "Spinich",  # typo in source file
    "Spinach",
    "Mustard",
    "Arugula",
    "Cauliflower",
    "Broccoli",
    "Eggplant",
    "Watermelon",
    "Cantalope",  # typo in source file
    "Cantaloupe",
    "Marigolds",
    "Marigold",
    "Sunflower",
    "Shallot",
    "Rhubarb",
    "Zucchini",
    "Celery",
    "Carrot",
    "Cress",
    "Romaine",
    "Okra",
    "Kale",
    "Dill",
    "Onion",
    "Beet",
    "Pea",
]


def get_db():
    return pymysql.connect(
        host=MYSQL_HOST,
        port=MYSQL_PORT,
        user=MYSQL_USER,
        password=MYSQL_PASSWORD,
        database=MYSQL_DATABASE,
        cursorclass=pymysql.cursors.DictCursor,
    )


def split_crop_variety(text: str) -> tuple[str, str | None]:
    text = re.sub(r"\s+", " ", text.strip())
    if not text:
        return ("Unknown", None)
    for prefix in CROP_PREFIXES:
        if text.lower() == prefix.lower():
            return (prefix.replace("Spinich", "Spinach").replace("Cantalope", "Cantaloupe"), None)
        if text.lower().startswith(prefix.lower() + " "):
            crop = prefix.replace("Spinich", "Spinach").replace("Cantalope", "Cantaloupe")
            variety = text[len(prefix) :].strip()
            return (crop, variety or None)
    # Fallback: first word = crop, rest = variety
    parts = text.split(" ", 1)
    return (parts[0], parts[1] if len(parts) > 1 else None)


def parse_line(line: str) -> dict | None:
    line = line.rstrip()
    if not line.strip():
        return None
    m = re.match(r"^(\d+)\s+(.+)$", line.strip())
    if not m:
        return None
    location = m.group(1)
    remainder = m.group(2)
    tab_parts = [p.strip() for p in remainder.split("\t") if p.strip()]
    if len(tab_parts) >= 2:
        main_text = tab_parts[0]
        source = tab_parts[-1]
    else:
        main_text = tab_parts[0] if tab_parts else remainder.strip()
        source = None
    crop_name, variety = split_crop_variety(main_text)
    return {
        "location": location,
        "crop_name": crop_name,
        "variety": variety,
        "source": source,
    }


def parse_seeds_file(path: str) -> list[dict]:
    rows = []
    with open(path, "r", encoding="utf-8") as f:
        for line_no, line in enumerate(f, 1):
            row = parse_line(line)
            if row:
                row["line_no"] = line_no
                rows.append(row)
    return rows


def seed_exists(cur, row: dict) -> bool:
    cur.execute(
        """
        SELECT id FROM GardenSeedInventory
        WHERE location = %s AND crop_name = %s
          AND (variety <=> %s) AND (source <=> %s)
        LIMIT 1
        """,
        (row["location"], row["crop_name"], row["variety"], row["source"]),
    )
    return cur.fetchone() is not None


def import_seeds(rows: list[dict], dry_run: bool = False, skip_existing: bool = True) -> tuple[int, int, int]:
    inserted = 0
    skipped = 0
    errors = 0
    conn = get_db()
    try:
        ensure_garden_tables(conn)
        with conn.cursor() as cur:
            for row in rows:
                try:
                    if skip_existing and seed_exists(cur, row):
                        skipped += 1
                        continue
                    if dry_run:
                        inserted += 1
                        continue
                    cur.execute(
                        """
                        INSERT INTO GardenSeedInventory
                            (crop_name, variety, qty, unit, source, location, notes)
                        VALUES (%s, %s, 0, 'seeds', %s, %s, %s)
                        """,
                        (
                            row["crop_name"],
                            row["variety"],
                            row["source"],
                            row["location"],
                            f"Imported from seeds.txt line {row['line_no']}",
                        ),
                    )
                    inserted += 1
                except pymysql.Error as exc:
                    errors += 1
                    print(f"Line {row['line_no']}: ERROR {exc}", file=sys.stderr)
        if not dry_run:
            conn.commit()
    finally:
        conn.close()
    return inserted, skipped, errors


def main():
    parser = argparse.ArgumentParser(description="Import Garden Tracker seed inventory from seeds.txt")
    parser.add_argument(
        "--file",
        default=DEFAULT_SEEDS_FILE,
        help=f"Path to seeds file (default: {DEFAULT_SEEDS_FILE})",
    )
    parser.add_argument("--dry-run", action="store_true", help="Parse and report only; do not insert")
    parser.add_argument(
        "--force",
        action="store_true",
        help="Insert even if matching location/crop/variety/source already exists",
    )
    args = parser.parse_args()

    if not os.path.isfile(args.file):
        print(f"File not found: {args.file}", file=sys.stderr)
        sys.exit(1)

    rows = parse_seeds_file(args.file)
    print(f"Parsed {len(rows)} seed rows from {args.file}\n")
    print(f"{'Loc':>4}  {'Crop':<22} {'Variety':<32} {'Source'}")
    print("-" * 80)
    for row in rows:
        variety = row["variety"] or "—"
        source = row["source"] or "—"
        print(f"{row['location']:>4}  {row['crop_name']:<22} {variety:<32} {source}")

    if args.dry_run:
        print(f"\nDry run: parsed {len(rows)} rows (no database changes)")
        return

    inserted, skipped, errors = import_seeds(
        rows, dry_run=False, skip_existing=not args.force
    )
    print()
    print(f"Inserted {inserted}, skipped {skipped} existing, {errors} errors")


if __name__ == "__main__":
    main()