"""
Moisture Meter: receives readings from ESP32, stores in MySQL shop_stats.
"""
import json
import pymysql
from flask import Blueprint, request, jsonify, render_template, Response

from config import MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE

bp = Blueprint("moisture", __name__)

DB_CONFIG = {
    "host": MYSQL_HOST,
    "user": MYSQL_USER,
    "password": MYSQL_PASSWORD,
    "database": MYSQL_DATABASE,
    "cursorclass": pymysql.cursors.DictCursor,
}


def get_db():
    return pymysql.connect(**DB_CONFIG)


def ensure_tables(conn):
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS MoistureLevel (
                id INT AUTO_INCREMENT PRIMARY KEY,
                device_id VARCHAR(16) NOT NULL,
                node_name VARCHAR(50) DEFAULT NULL,
                moisture INT NOT NULL,
                battery_voltage FLOAT NOT NULL,
                command VARCHAR(20) DEFAULT 'sleep',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        try:
            cur.execute("ALTER TABLE MoistureLevel ADD COLUMN command VARCHAR(20) DEFAULT 'sleep'")
        except pymysql.OperationalError:
            pass
        try:
            cur.execute("ALTER TABLE MoistureLevel ADD COLUMN node_name VARCHAR(50) DEFAULT NULL")
        except pymysql.OperationalError:
            pass
        try:
            cur.execute("ALTER TABLE MoistureLevel ADD COLUMN firmware_version VARCHAR(24) DEFAULT NULL")
        except pymysql.OperationalError:
            pass
        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 device_command ADD COLUMN new_device_id VARCHAR(16) DEFAULT NULL")
        except pymysql.OperationalError:
            pass
        try:
            cur.execute("ALTER TABLE device_command ADD COLUMN new_node_name VARCHAR(50) DEFAULT NULL")
        except pymysql.OperationalError:
            pass
    conn.commit()


@bp.route("/reading", methods=["POST"])
def reading():
    try:
        data = request.get_json(force=True, silent=True)
        if not data:
            return jsonify({"ok": False, "error": "Invalid or missing JSON"}), 400

        device_id = data.get("device_id")
        node_name = data.get("node_name")
        moisture = data.get("moisture")
        battery_voltage = data.get("battery_voltage")
        firmware_version = data.get("firmware_version")

        if device_id is None or moisture is None or battery_voltage is None:
            return jsonify({"ok": False, "error": "Missing device_id, moisture, or battery_voltage"}), 400

        if not isinstance(device_id, str) or not device_id.strip():
            return jsonify({"ok": False, "error": "device_id must be a non-empty string"}), 400
        device_id = device_id.strip()[:16]
        node_name = (node_name if isinstance(node_name, str) else "").strip()[:50] or None

        try:
            moisture = int(moisture)
        except (TypeError, ValueError):
            return jsonify({"ok": False, "error": "moisture must be a number"}), 400

        try:
            battery_voltage = float(battery_voltage)
        except (TypeError, ValueError):
            return jsonify({"ok": False, "error": "battery_voltage must be a number"}), 400

        firmware_version = (firmware_version if isinstance(firmware_version, str) else "").strip()[:24] or None

        conn = get_db()
        try:
            ensure_tables(conn)
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT command, new_device_id, new_node_name FROM device_command WHERE device_id = %s",
                    (device_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 MoistureLevel (device_id, node_name, moisture, battery_voltage, command, firmware_version) VALUES (%s, %s, %s, %s, %s, %s)",
                    (device_id, node_name, moisture, battery_voltage, current_command, firmware_version),
                )
                resp_device_id = new_device_id if new_device_id else device_id
                resp_node_name = new_node_name if new_node_name else (node_name or "Node")
                if new_device_id:
                    cur.execute(
                        "UPDATE device_command SET new_device_id = NULL WHERE device_id = %s",
                        (device_id,),
                    )
                if new_node_name:
                    cur.execute(
                        "UPDATE device_command SET new_node_name = NULL WHERE device_id = %s",
                        (device_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",
                        (device_id,),
                    )
            conn.commit()
        finally:
            conn.close()

        # Compact JSON (no spaces) so device parser works regardless of Flask debug mode
        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("/device/<device_id>/command", methods=["PUT", "POST"])
def set_command(device_id):
    """Set command for device: body {"command": "sleep"|"nosleep"|"reboot"}."""
    try:
        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
        device_id = device_id.strip()[:16]
        if not device_id:
            return jsonify({"ok": False, "error": "invalid device_id"}), 400
        conn = get_db()
        try:
            ensure_tables(conn)
            with conn.cursor() as cur:
                cur.execute(
                    "INSERT INTO device_command (device_id, command) VALUES (%s, %s) ON DUPLICATE KEY UPDATE command = %s",
                    (device_id, cmd, cmd),
                )
            conn.commit()
        finally:
            conn.close()
        return jsonify({"ok": True, "command": cmd}), 200
    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("/device/<device_id>/device_id", methods=["PUT", "POST"])
def set_device_id(device_id):
    """Assign new device ID: body {"device_id": "M001"}. Device will pick it up on next POST."""
    try:
        data = request.get_json(force=True, silent=True) or {}
        new_id = (data.get("device_id") or "").strip()[:16]
        if not new_id:
            return jsonify({"ok": False, "error": "device_id required"}), 400
        device_id = device_id.strip()[:16]
        if not device_id:
            return jsonify({"ok": False, "error": "invalid device_id in URL"}), 400
        conn = get_db()
        try:
            ensure_tables(conn)
            with conn.cursor() as cur:
                cur.execute(
                    "INSERT INTO device_command (device_id, command, new_device_id) VALUES (%s, 'nosleep', %s) ON DUPLICATE KEY UPDATE new_device_id = %s",
                    (device_id, new_id, new_id),
                )
            conn.commit()
        finally:
            conn.close()
        return jsonify({"ok": True, "current_id": device_id, "new_id": new_id}), 200
    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("/device/<device_id>/node_name", methods=["PUT", "POST"])
def set_node_name(device_id):
    """Assign new node name: body {"node_name": "Living Room"}. Device will pick it up on next POST (max 50 chars)."""
    try:
        data = request.get_json(force=True, silent=True) or {}
        new_name = (data.get("node_name") or "").strip()[:50]
        if not new_name:
            return jsonify({"ok": False, "error": "node_name required"}), 400
        device_id = device_id.strip()[:16]
        if not device_id:
            return jsonify({"ok": False, "error": "invalid device_id in URL"}), 400
        conn = get_db()
        try:
            ensure_tables(conn)
            with conn.cursor() as cur:
                cur.execute(
                    "INSERT INTO device_command (device_id, command, new_node_name) VALUES (%s, 'nosleep', %s) ON DUPLICATE KEY UPDATE new_node_name = %s",
                    (device_id, new_name, new_name),
                )
            conn.commit()
        finally:
            conn.close()
        return jsonify({"ok": True, "current_id": device_id, "new_node_name": new_name}), 200
    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("/api/readings")
def api_readings():
    """Return recent moisture readings (latest 100)."""
    limit = min(int(request.args.get("limit", 100)), 500)
    try:
        conn = get_db()
        try:
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT id, device_id, node_name, moisture, battery_voltage, command, firmware_version, created_at FROM MoistureLevel ORDER BY id DESC LIMIT %s",
                    (limit,),
                )
                rows = cur.fetchall()
                for r in rows:
                    if r.get("created_at"):
                        r["created_at"] = r["created_at"].isoformat()
            return jsonify({"ok": True, "readings": rows})
        finally:
            conn.close()
    except pymysql.Error:
        return jsonify({"ok": False, "error": "Database error"}), 500


@bp.route("/api/readings/latest")
def api_readings_latest():
    """Return the latest reading per device_id only."""
    try:
        conn = get_db()
        try:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT m.id, m.device_id, m.node_name, m.moisture, m.battery_voltage, m.command, m.firmware_version, m.created_at
                    FROM MoistureLevel m
                    INNER JOIN (
                        SELECT device_id, MAX(id) AS max_id FROM MoistureLevel GROUP BY device_id
                    ) latest ON m.device_id = latest.device_id AND m.id = latest.max_id
                    WHERE m.created_at >= NOW() - INTERVAL 20 MINUTE
                    ORDER BY m.device_id
                """)
                rows = cur.fetchall()
                for r in rows:
                    if r.get("created_at"):
                        r["created_at"] = r["created_at"].isoformat()
            return jsonify({"ok": True, "readings": rows})
        finally:
            conn.close()
    except pymysql.Error:
        return jsonify({"ok": False, "error": "Database error"}), 500


@bp.route("/api/readings/clear-old", methods=["POST", "DELETE"])
def api_readings_clear_old():
    """Delete records older than the given days (default 1). Returns count deleted."""
    try:
        days = request.args.get("days", 1, type=int)
        days = max(1, min(days, 365))
        conn = get_db()
        try:
            with conn.cursor() as cur:
                cur.execute(
                    "DELETE FROM MoistureLevel WHERE created_at < NOW() - INTERVAL %s DAY",
                    (days,),
                )
                deleted = cur.rowcount
            conn.commit()
            return jsonify({"ok": True, "deleted": deleted}), 200
        finally:
            conn.close()
    except pymysql.Error:
        return jsonify({"ok": False, "error": "Database error"}), 500


@bp.route("/api/device/<device_id>/command", methods=["GET"])
def get_device_command(device_id):
    """Return current command for device (for dashboard toggle state)."""
    device_id = device_id.strip()[:16]
    if not device_id:
        return jsonify({"ok": False, "error": "invalid device_id"}), 400
    try:
        conn = get_db()
        try:
            ensure_tables(conn)
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT command, new_device_id, new_node_name FROM device_command WHERE device_id = %s",
                    (device_id,),
                )
                row = cur.fetchone()
                command = (row["command"] if row else "nosleep").strip().lower()
                if command not in ("sleep", "nosleep", "reboot"):
                    command = "nosleep"
                new_device_id = (row.get("new_device_id") or "").strip() if row else None
                new_node_name = (row.get("new_node_name") or "").strip() if row else None
            return jsonify({"ok": True, "command": command, "new_device_id": new_device_id or None, "new_node_name": new_node_name or None})
        finally:
            conn.close()
    except pymysql.Error:
        return jsonify({"ok": False, "error": "Database error"}), 500


@bp.route("/")
def dashboard():
    return render_template("moisture/dashboard.html")


@bp.route("/health", methods=["GET"])
def health():
    return jsonify({"ok": True}), 200