"""
PumpControl: receives pump status from ESP32, stores in MySQL shop_stats.
For later use by SolarMon dashboard.
"""
import threading
import pymysql
from flask import Blueprint, request, jsonify

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

bp = Blueprint("pump", __name__)

# In-memory pending reboot; device receives it on next POST /reading and we clear it.
_pump_reboot_lock = threading.Lock()
_pump_reboot_pending = False

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


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


def ensure_pump_table(conn):
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS PumpStatus (
                id INT AUTO_INCREMENT PRIMARY KEY,
                pressure_psi FLOAT NOT NULL,
                pump_on TINYINT(1) NOT NULL,
                error_pressure_low TINYINT(1) NOT NULL,
                app_state VARCHAR(24) NOT NULL,
                rssi INT NULL,
                ip VARCHAR(45) NULL,
                mac VARCHAR(24) NULL,
                firmware_version VARCHAR(24) NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
    conn.commit()


@bp.route("/reading", methods=["POST"])
def reading():
    """Accept pump status from PumpControl device; insert into PumpStatus."""
    global _pump_reboot_pending
    try:
        data = request.get_json(force=True, silent=True)
        if not data:
            return jsonify({"ok": False, "error": "Invalid or missing JSON"}), 400

        pressure_psi = data.get("pressure_psi")
        pump_on = data.get("pump_on")
        error_pressure_low = data.get("error_pressure_low")
        app_state = data.get("app_state")

        if pressure_psi is None or pump_on is None or error_pressure_low is None or app_state is None:
            return jsonify({
                "ok": False,
                "error": "Missing pressure_psi, pump_on, error_pressure_low, or app_state"
            }), 400

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

        pump_on = bool(pump_on)
        error_pressure_low = bool(error_pressure_low)
        app_state = (app_state if isinstance(app_state, str) else str(app_state)).strip()[:24] or "unknown"

        rssi = data.get("rssi")
        if rssi is not None:
            try:
                rssi = int(rssi)
            except (TypeError, ValueError):
                rssi = None

        ip = (data.get("ip") if isinstance(data.get("ip"), str) else "").strip()[:45] or None
        mac = (data.get("mac") if isinstance(data.get("mac"), str) else "").strip()[:24] or None
        firmware_version = (data.get("firmware_version") if isinstance(data.get("firmware_version"), str) else "").strip()[:24] or None

        conn = get_db()
        try:
            ensure_pump_table(conn)
            with conn.cursor() as cur:
                cur.execute(
                    """INSERT INTO PumpStatus (pressure_psi, pump_on, error_pressure_low, app_state, rssi, ip, mac, firmware_version)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""",
                    (pressure_psi, 1 if pump_on else 0, 1 if error_pressure_low else 0, app_state, rssi, ip, mac, firmware_version),
                )
            conn.commit()
        finally:
            conn.close()

        # If reboot was requested, tell device once then clear
        with _pump_reboot_lock:
            if _pump_reboot_pending:
                _pump_reboot_pending = False
                return jsonify({"ok": True, "command": "reboot"}), 200
        return jsonify({"ok": True}), 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/reboot", methods=["POST"])
def api_reboot():
    """Set pending reboot; pump device will receive command on next POST /reading."""
    global _pump_reboot_pending
    with _pump_reboot_lock:
        _pump_reboot_pending = True
    return jsonify({"ok": True}), 200


@bp.route("/api/latest")
def api_latest():
    """Return the latest pump status row for SolarMon dashboard."""
    try:
        conn = get_db()
        try:
            ensure_pump_table(conn)
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT id, pressure_psi, pump_on, error_pressure_low, app_state, rssi, ip, mac, firmware_version, created_at FROM PumpStatus ORDER BY created_at DESC LIMIT 1"
                )
                row = cur.fetchone()
            if not row:
                return jsonify({"ok": True, "reading": None})
            # Convert TINYINT to bool for JSON
            if "pump_on" in row and row["pump_on"] is not None:
                row["pump_on"] = bool(row["pump_on"])
            if "error_pressure_low" in row and row["error_pressure_low"] is not None:
                row["error_pressure_low"] = bool(row["error_pressure_low"])
            if row.get("created_at"):
                row["created_at"] = row["created_at"].isoformat()
            return jsonify({"ok": True, "reading": row})
        finally:
            conn.close()
    except pymysql.Error:
        return jsonify({"ok": False, "error": "Database error"}), 500
    except Exception as e:
        return jsonify({"ok": False, "error": str(e)}), 500