#!/usr/bin/env python3
"""
One-time update: set every GardenSeedInventory row to the same quantity.

Usage:
  cd /path/to/Server
  python scripts/set_all_seed_qty.py --dry-run
  python scripts/set_all_seed_qty.py
  python scripts/set_all_seed_qty.py --qty 10
"""
from __future__ import annotations

import argparse
import os
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


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 fetch_seeds(conn):
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, crop_name, variety, qty, unit, location
            FROM GardenSeedInventory
            ORDER BY crop_name, variety, id
            """
        )
        return cur.fetchall()


def set_all_qty(qty: float, dry_run: bool = False) -> int:
    conn = get_db()
    try:
        ensure_garden_tables(conn)
        seeds = fetch_seeds(conn)
        if not seeds:
            print("No seeds in GardenSeedInventory.")
            return 0

        print(f"{'ID':>5}  {'Qty':>8}  {'Unit':<8}  {'Crop':<20} {'Variety':<28} {'Loc'}")
        print("-" * 95)
        for row in seeds:
            variety = row["variety"] or "—"
            location = row["location"] or "—"
            print(
                f"{row['id']:>5}  {float(row['qty']):>8g}  {row['unit']:<8} "
                f"{row['crop_name']:<20} {variety:<28} {location}"
            )

        if dry_run:
            print(f"\nDry run: would set {len(seeds)} seed(s) to qty={qty:g}")
            return len(seeds)

        with conn.cursor() as cur:
            cur.execute("UPDATE GardenSeedInventory SET qty = %s", (qty,))
            updated = cur.rowcount
        conn.commit()
        print(f"\nUpdated {updated} seed(s) to qty={qty:g}")
        return updated
    finally:
        conn.close()


def main():
    parser = argparse.ArgumentParser(
        description="Set all Garden Tracker seed inventory quantities to one value"
    )
    parser.add_argument(
        "--qty",
        type=float,
        default=10,
        help="Quantity to apply to every seed row (default: 10)",
    )
    parser.add_argument(
        "--dry-run",
        action="store_true",
        help="Show current rows only; do not update the database",
    )
    args = parser.parse_args()

    if args.qty < 0:
        print("qty must be >= 0", file=sys.stderr)
        sys.exit(1)

    set_all_qty(args.qty, dry_run=args.dry_run)


if __name__ == "__main__":
    main()