"""
Temps dashboard: latest record per unit_id from MySQL shop_stats.Temps.
Editable unit_id and node_name via PUT endpoints.
Receives sensor readings via POST /reading (device_command for sleep toggle).
MySQL entry_date is stored as local datetime (not UTC).
"""
import json
import pymysql
from flask import Blueprint, render_template, jsonify, request, Response

from config import MYSQL_HOST, MYSQL_PORT, MYSQL_DATABASE, MYSQL_USER, MYSQL_PASSWORD

bp = Blueprint("temps", __name__)


def _get_config():
    return {
        "host": MYSQL_HOST,
        "port": MYSQL_PORT,
        "database": MYSQL_DATABASE,
        "user": MYSQL_USER,
        "password": MYSQL_PASSWORD,
    }


def get_latest_per_unit():
    """Return list of latest Temps row per unit_id (by entry_date)."""
    cfg = _get_config()
    if not cfg["password"]:
        return None, "TEMPS_MYSQL_PASSWORD is not set"
    query = """
        SELECT t.entry_date, t.unit_id, t.node_name, t.temp, t.humid, t.bat, t.firmware_version,
               COALESCE(NULLIF(LOWER(TRIM(dc.command)), ''), 'nosleep') AS command,
               dc.new_device_id, dc.new_node_name
        FROM Temps t
        INNER JOIN (
            SELECT unit_id, MAX(entry_date) AS max_entry_date
            FROM Temps
            WHERE entry_date >= NOW() - INTERVAL 20 MINUTE
            GROUP BY unit_id
        ) latest ON t.unit_id = latest.unit_id AND t.entry_date = latest.max_entry_date
        LEFT JOIN device_command dc ON dc.device_id = t.unit_id
        WHERE t.entry_date >= NOW() - INTERVAL 20 MINUTE
        ORDER BY t.unit_id
    """
    try:
        conn = pymysql.connect(
            host=cfg["host"],
            port=cfg["port"],
            user=cfg["user"],
            password=cfg["password"],
            database=cfg["database"],
            cursorclass=pymysql.cursors.DictCursor,
        )
    except pymysql.Error as e:
        return None, str(e)
    try:
        _ensure_device_command(conn)
        with conn.cursor() as cur:
            cur.execute(query)
            rows = cur.fetchall()
        for r in rows:
            cmd = (r.get("command") or "nosleep").strip().lower()
            if cmd not in ("sleep", "nosleep", "reboot"):
                r["command"] = "nosleep"
    finally:
        conn.close()
    return rows, None


@bp.route("/")
def index():
    return render_template("temps/index.html")


@bp.route("/api/latest")
def api_latest():
    rows, err = get_latest_per_unit()
    if err:
        return jsonify({"error": err}), 500
    # Serialize entry_date as local "YYYY-MM-DD HH:mm:ss" so the client parses as local, not UTC
    for r in rows:
        ed = r.get("entry_date")
        if ed is not None:
            r["entry_date"] = ed.strftime("%Y-%m-%d %H:%M:%S")
    return jsonify(rows)


def _get_conn():
    cfg = _get_config()
    return pymysql.connect(
        host=cfg["host"],
        port=cfg["port"],
        user=cfg["user"],
        password=cfg["password"],
        database=cfg["database"],
        cursorclass=pymysql.cursors.DictCursor,
    )


def _ensure_device_command(conn):
    """Ensure device_command table exists (shared with moisture blueprint)."""
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS device_command (
                device_id VARCHAR(16) PRIMARY KEY,
                command VARCHAR(20) NOT NULL DEFAULT 'sleep',
                new_device_id VARCHAR(16) DEFAULT NULL,
                new_node_name VARCHAR(50) DEFAULT NULL
            )
        """)
        try:
            cur.execute("ALTER TABLE Temps ADD COLUMN firmware_version VARCHAR(24) DEFAULT NULL")
        except pymysql.OperationalError:
            pass  # column already exists
    conn.commit()


@bp.route("/reading", methods=["POST"])
def reading():
    """Accept temp/humidity reading from WifiTemps device; insert into Temps; return command/device_id/node_name."""
    try:
        data = request.get_json(force=True, silent=True)
        if not data:
            return jsonify({"ok": False, "error": "Invalid or missing JSON"}), 400

        unit_id = data.get("unit_id")
        node_name = data.get("node_name")
        temp = data.get("temp")
        humid = data.get("humid")
        mac = data.get("mac")
        ip = data.get("ip")
        bat = data.get("bat")
        firmware_version = data.get("firmware_version")

        if unit_id is None or temp is None or humid is None:
            return jsonify({"ok": False, "error": "Missing unit_id, temp, or humid"}), 400

        if not isinstance(unit_id, str) or not unit_id.strip():
            return jsonify({"ok": False, "error": "unit_id must be a non-empty string"}), 400
        unit_id = unit_id.strip()[:10]
        node_name = (node_name if isinstance(node_name, str) else "").strip()[:50] or None
        temp = str(temp).strip()[:10] if temp is not None else None
        humid = str(humid).strip()[:10] if humid is not None else None
        mac = (mac if isinstance(mac, str) else "").strip()[:20] or None
        ip = (ip if isinstance(ip, str) else "").strip()[:20] or None
        bat = (bat if isinstance(bat, str) else str(bat)).strip()[:10] if bat is not None else None
        firmware_version = (firmware_version if isinstance(firmware_version, str) else "").strip()[:24] or None

        conn = _get_conn()
        try:
            _ensure_device_command(conn)
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT command, new_device_id, new_node_name FROM device_command WHERE device_id = %s",
                    (unit_id,),
                )
                row = cur.fetchone()
                current_command = (row["command"] if row else "nosleep").strip().lower()
                if current_command not in ("sleep", "nosleep", "reboot"):
                    current_command = "nosleep"
                new_device_id = (row.get("new_device_id") or "").strip()[:16] if row else None
                new_node_name = (row.get("new_node_name") or "").strip()[:50] if row else None

                cur.execute(
                    """INSERT INTO Temps (unit_id, entry_date, temp, humid, mac, ip, bat, command, node_name, firmware_version)
                       VALUES (%s, NOW(), %s, %s, %s, %s, %s, %s, %s, %s)""",
                    (unit_id, temp, humid, mac, ip, bat, current_command, node_name, firmware_version),
                )
                resp_device_id = new_device_id if new_device_id else unit_id
                resp_node_name = new_node_name if new_node_name else (node_name or "NEW")
                if new_device_id:
                    cur.execute(
                        "UPDATE device_command SET new_device_id = NULL WHERE device_id = %s",
                        (unit_id,),
                    )
                if new_node_name:
                    cur.execute(
                        "UPDATE device_command SET new_node_name = NULL WHERE device_id = %s",
                        (unit_id,),
                    )
                # After sending reboot once, clear so device is not told to reboot again
                if current_command == "reboot":
                    cur.execute(
                        "UPDATE device_command SET command = 'nosleep' WHERE device_id = %s",
                        (unit_id,),
                    )
            conn.commit()
        finally:
            conn.close()

        body = json.dumps(
            {"ok": True, "command": current_command, "device_id": resp_device_id, "node_name": resp_node_name},
            separators=(",", ":"),
        )
        return Response(body, status=200, mimetype="application/json")
    except pymysql.Error:
        return jsonify({"ok": False, "error": "Database error"}), 500
    except Exception as e:
        return jsonify({"ok": False, "error": str(e)}), 500


@bp.route("/sensor/<unit_id>/unit_id", methods=["PUT", "POST"])
def set_unit_id(unit_id):
    """Set new unit_id for this sensor: body {"unit_id": "NEW_ID"}. Updates Temps rows and pushes new_id to device via device_command."""
    data = request.get_json(force=True, silent=True) or {}
    new_id = (data.get("unit_id") or "").strip()
    if not new_id:
        return jsonify({"ok": False, "error": "unit_id required"}), 400
    unit_id = (unit_id or "").strip()
    if not unit_id:
        return jsonify({"ok": False, "error": "invalid unit_id in URL"}), 400
    new_id = new_id[:10]
    try:
        conn = _get_conn()
        try:
            _ensure_device_command(conn)
            with conn.cursor() as cur:
                cur.execute("UPDATE Temps SET unit_id = %s WHERE unit_id = %s", (new_id, unit_id))
                cur.execute(
                    "INSERT INTO device_command (device_id, command, new_device_id) VALUES (%s, 'nosleep', %s) ON DUPLICATE KEY UPDATE new_device_id = %s",
                    (unit_id, new_id, new_id),
                )
            conn.commit()
            return jsonify({"ok": True, "previous_id": unit_id, "new_id": new_id}), 200
        finally:
            conn.close()
    except pymysql.Error as e:
        return jsonify({"ok": False, "error": str(e)}), 500


@bp.route("/sensor/<unit_id>/command", methods=["PUT", "POST"])
def set_command(unit_id):
    """Set command for sensor: body {"command": "sleep"|"nosleep"|"reboot"}."""
    data = request.get_json(force=True, silent=True) or {}
    cmd = (data.get("command") or "").strip().lower()
    if cmd not in ("sleep", "nosleep", "reboot"):
        return jsonify({"ok": False, "error": "command must be 'sleep', 'nosleep', or 'reboot'"}), 400
    unit_id = (unit_id or "").strip()
    if not unit_id:
        return jsonify({"ok": False, "error": "invalid unit_id in URL"}), 400
    unit_id = unit_id[:16]
    try:
        conn = _get_conn()
        try:
            _ensure_device_command(conn)
            with conn.cursor() as cur:
                cur.execute(
                    "INSERT INTO device_command (device_id, command) VALUES (%s, %s) ON DUPLICATE KEY UPDATE command = %s",
                    (unit_id, cmd, cmd),
                )
            conn.commit()
            return jsonify({"ok": True, "command": cmd}), 200
        finally:
            conn.close()
    except pymysql.Error as e:
        return jsonify({"ok": False, "error": str(e)}), 500


@bp.route("/sensor/<unit_id>/node_name", methods=["PUT", "POST"])
def set_node_name(unit_id):
    """Set node_name for this sensor: body {"node_name": "Name"}. Updates Temps and pushes to device via device_command."""
    data = request.get_json(force=True, silent=True) or {}
    new_name = (data.get("node_name") or "").strip()[:50]
    unit_id = (unit_id or "").strip()
    if not unit_id:
        return jsonify({"ok": False, "error": "invalid unit_id in URL"}), 400
    try:
        conn = _get_conn()
        try:
            _ensure_device_command(conn)
            with conn.cursor() as cur:
                cur.execute("UPDATE Temps SET node_name = %s WHERE unit_id = %s", (new_name or None, unit_id))
                cur.execute(
                    "INSERT INTO device_command (device_id, command, new_node_name) VALUES (%s, 'nosleep', %s) ON DUPLICATE KEY UPDATE new_node_name = %s",
                    (unit_id, new_name or None, new_name or None),
                )
            conn.commit()
            return jsonify({"ok": True, "unit_id": unit_id, "node_name": new_name or None}), 200
        finally:
            conn.close()
    except pymysql.Error as e:
        return jsonify({"ok": False, "error": str(e)}), 500