"""Nutikodu andmete veebiliides (FastAPI). Loeb ClickHouse'i `uus67` skeemist (zigbee andurid) ja serveerib: GET / -> veebirakendus (static/index.html) GET /api/meta -> seadmed, tz, andmete ulatus GET /api/temperatuur -> aegrida (temp + niiskus), agg=hour|day GET /api/ukseandur -> ukseolekute sündmused (tulevikuks) GET /api/vead -> häirete sündmused (tulevikuks) GET /api/latest -> viimased väärtused + lahtised häired GET /health ClickHouse'i päringud kasutavad `zigbee` kasutajat (SELECT uus67.*) ja `param_*` parameetreid (SQL-süst välistatud). Tulemused loetakse `FORMAT JSONEachRow` kujul (üks JSON-objekt rea kohta). Valikuline kaitse: WEB_ACCESS_TOKEN keskkonnamuutuja (tühi = kaitseta). """ from __future__ import annotations import hmac import secrets import json import os import threading import time from collections import defaultdict from datetime import datetime, timedelta from pathlib import Path from zoneinfo import ZoneInfo import requests from fastapi import Body, Depends, FastAPI, Form, HTTPException, Query, Request from fastapi.responses import FileResponse, HTMLResponse, RedirectResponse from fastapi.staticfiles import StaticFiles from starlette.middleware.sessions import SessionMiddleware ROOT = Path(__file__).parent STATIC_DIR = ROOT / "static" TZ = ZoneInfo("Europe/Tallinn") CH_URL = os.environ.get("CH_URL", "http://clickhouse:8123").rstrip("/") CH_USER = os.environ.get("CH_USER", "zigbee") CH_PASS = os.environ.get("CH_PASS", "") CH_DB = os.environ.get("CH_DB", "uus67") WEB_ACCESS_TOKEN = os.environ.get("WEB_ACCESS_TOKEN", "") WEB_PASSWORD = os.environ.get("WEB_PASSWORD", "") WEB_SECRET_KEY = os.environ.get("WEB_SECRET_KEY", "") or secrets.token_hex(32) HA_URL = os.environ.get("HA_URL", "http://100.87.1.24:8123").rstrip("/") HA_TOKEN = os.environ.get("HA_TOKEN", "") AWAY_ENTITY = os.environ.get("AWAY_ENTITY", "input_boolean.kodust_eemal") SESSION_MAX_AGE = int(os.environ.get("SESSION_MAX_AGE", "604800")) LOGIN_MAX_ATTEMPTS = int(os.environ.get("LOGIN_MAX_ATTEMPTS", "5")) LOGIN_WINDOW_SECONDS = int(os.environ.get("LOGIN_WINDOW_SECONDS", "300")) DEFAULT_RANGE_DAYS = 7 MAX_RANGE_DAYS = 3660 app = FastAPI(title="Nutikodu", version="0.2.0") app.add_middleware( SessionMiddleware, secret_key=WEB_SECRET_KEY, https_only=False, same_site="lax", max_age=SESSION_MAX_AGE, ) @app.middleware("http") async def add_no_cache(request: Request, call_next): """Staatika ja API ilma vahemäluta — muidu jääb brauserisse vana JS/HTML.""" response = await call_next(request) path = request.url.path if ( path == "/" or path.startswith("/api/") or path.endswith((".js", ".css", ".html", ".webmanifest", ".svg")) ): response.headers["Cache-Control"] = "no-cache, no-store, must-revalidate" response.headers["Pragma"] = "no-cache" return response def ch_query(query: str, params: dict[str, str] | None = None) -> list[dict]: """Käivita ClickHouse'i päring ja tagasta read JSON-objektidena.""" q = {"database": CH_DB, "query": f"{query} FORMAT JSONEachRow"} for key, value in (params or {}).items(): q[f"param_{key}"] = value try: resp = requests.post( f"{CH_URL}/", params=q, auth=(CH_USER, CH_PASS), timeout=20, ) except requests.RequestException as exc: raise HTTPException(status_code=502, detail=f"ClickHouse'i ühendus: {exc}") from exc if resp.status_code != 200: raise HTTPException(status_code=502, detail=f"ClickHouse: {resp.text.strip()[:400]}") text = resp.text.strip() if not text: return [] try: return [json.loads(line) for line in text.splitlines() if line.strip()] except json.JSONDecodeError as exc: raise HTTPException(status_code=502, detail=f"ClickHouse'i vastus: {text[:200]}") from exc _login_failures: dict[str, list[float]] = defaultdict(list) _login_lock = threading.Lock() def client_ip(request: Request) -> str: forwarded = request.headers.get("x-forwarded-for") if forwarded: return forwarded.split(",")[0].strip() return request.client.host if request.client else "?" def retry_after_seconds(ip: str) -> int: now = time.monotonic() with _login_lock: history = [t for t in _login_failures.get(ip, []) if now - t < LOGIN_WINDOW_SECONDS] _login_failures[ip] = history if len(history) >= LOGIN_MAX_ATTEMPTS: return int(LOGIN_WINDOW_SECONDS - (now - history[0])) + 1 return 0 def register_login_failure(ip: str) -> None: with _login_lock: _login_failures[ip].append(time.monotonic()) def reset_login_failures(ip: str) -> None: with _login_lock: _login_failures.pop(ip, None) def is_authed(request: Request) -> bool: """Kas päring on autenditud: Bearer token VÕI sessiooniküpsis.""" auth = request.headers.get("authorization", "") if auth.lower().startswith("bearer "): token = auth[7:].strip() if WEB_ACCESS_TOKEN and secrets.compare_digest(token, WEB_ACCESS_TOKEN): return True try: if request.session.get("auth") is True: return True except Exception: pass return False def require_auth(request: Request) -> None: if not is_authed(request): raise HTTPException( status_code=401, detail="Autentimine nõutud (Bearer token või sisselogimine)", headers={"WWW-Authenticate": "Bearer"}, ) def parse_dt(value: str | None, default: datetime) -> str: """Võta vastu 'YYYY-MM-DD[ HH:MM[:SS]]' või ISO 'T'-eraldajaga.""" if not value: return default.strftime("%Y-%m-%d %H:%M:%S") cleaned = value.strip().replace("T", " ") for fmt in ("%Y-%m-%d %H:%M:%S", "%Y-%m-%d %H:%M", "%Y-%m-%d"): try: return datetime.strptime(cleaned, fmt).strftime("%Y-%m-%d %H:%M:%S") except ValueError: continue raise HTTPException(status_code=422, detail=f"Vigane kuupäev: {value}") def resolve_range(from_: str | None, to: str | None) -> tuple[str, str]: now = datetime.now(TZ) to_s = parse_dt(to, now) to_dt = datetime.strptime(to_s, "%Y-%m-%d %H:%M:%S") from_s = parse_dt(from_, to_dt - timedelta(days=DEFAULT_RANGE_DAYS)) from_dt = datetime.strptime(from_s, "%Y-%m-%d %H:%M:%S") if from_dt > to_dt: raise HTTPException(status_code=422, detail="'from' peab olema enne 'to'") if (to_dt - from_dt).days > MAX_RANGE_DAYS: raise HTTPException(status_code=422, detail="Vahemik on liiga pikk") return from_s, to_s @app.get("/api/meta") async def meta() -> dict: devices = [row["device"] for row in ch_query( f"SELECT DISTINCT device FROM {CH_DB}.temperatuur ORDER BY device" )] bounds = ch_query( f"SELECT toString(min(hour)) AS min_hour, toString(max(hour)) AS max_hour, count() AS rows " f"FROM {CH_DB}.temperatuur" ) first = bounds[0] if bounds else {"min_hour": None, "max_hour": None, "rows": 0} return { "tz": "Europe/Tallinn", "db": CH_DB, "devices": devices, "min": first["min_hour"], "max": first["max_hour"], "rows": first["rows"], "default_range_days": DEFAULT_RANGE_DAYS, } @app.get("/api/temperatuur") async def temperatuur( from_: str | None = Query(None, alias="from"), to: str | None = Query(None), device: str | None = Query(None), agg: str = Query("hour", pattern="^(hour|day)$"), ) -> dict: from_s, to_s = resolve_range(from_, to) params = {"from": from_s, "to": to_s} where = ( f"hour BETWEEN toDateTime({{from:String}}, 'Europe/Tallinn') " f"AND toDateTime({{to:String}}, 'Europe/Tallinn')" ) if device: where += " AND device = {device:String}" params["device"] = device if agg == "day": select = ( "toUInt64(toUnixTimestamp(toStartOfDay(hour))) * 1000 AS t, device, " "round(avg(temperature), 2) AS temperature, round(avg(humidity), 2) AS humidity, " "max(taidetud) AS taidetud" ) group = "GROUP BY t, device" else: select = ( "toUInt64(toUnixTimestamp(hour)) * 1000 AS t, device, " "temperature, humidity, taidetud" ) group = "" rows = ch_query( f"SELECT {select} FROM {CH_DB}.temperatuur WHERE {where} {group} ORDER BY t", params, ) series: dict[str, dict] = {} for row in rows: s = series.setdefault( row["device"], {"t": [], "temperature": [], "humidity": [], "taidetud": []} ) s["t"].append(int(row["t"])) s["temperature"].append(row["temperature"]) s["humidity"].append(row["humidity"]) s["taidetud"].append(bool(row.get("taidetud"))) return {"from": from_s, "to": to_s, "agg": agg, "series": series} @app.get("/api/valisilm") async def valisilm( from_: str | None = Query(None, alias="from"), to: str | None = Query(None), agg: str = Query("hour", pattern="^(hour|day)$"), ) -> dict: from_s, to_s = resolve_range(from_, to) params = {"from": from_s, "to": to_s} where = ( f"hour BETWEEN toDateTime({{from:String}}, 'Europe/Tallinn') " f"AND toDateTime({{to:String}}, 'Europe/Tallinn')" ) if agg == "day": select = ( "toUInt64(toUnixTimestamp(toStartOfDay(hour))) * 1000 AS t, " "round(avg(temperature), 2) AS temperature, round(avg(humidity), 2) AS humidity, " "round(avg(wind), 2) AS wind, round(sum(rain), 2) AS rain, max(taidetud) AS taidetud" ) group = "GROUP BY t" else: select = ( "toUInt64(toUnixTimestamp(hour)) * 1000 AS t, " "temperature, humidity, wind, rain, taidetud" ) group = "" rows = ch_query( f"SELECT {select} FROM {CH_DB}.valisilm FINAL WHERE {where} {group} ORDER BY t", params, ) series: dict[str, list] = { "t": [], "temperature": [], "humidity": [], "wind": [], "rain": [], "taidetud": [], } for row in rows: series["t"].append(int(row["t"])) series["temperature"].append(row["temperature"]) series["humidity"].append(row["humidity"]) series["wind"].append(row["wind"]) series["rain"].append(row["rain"]) series["taidetud"].append(bool(row.get("taidetud"))) return {"from": from_s, "to": to_s, "agg": agg, "series": series} @app.get("/api/ukseandur", dependencies=[Depends(require_auth)]) async def ukseandur( from_: str | None = Query(None, alias="from"), to: str | None = Query(None), device: str | None = Query(None), ) -> dict: from_s, to_s = resolve_range(from_, to) params = {"from": from_s, "to": to_s} where = ( f"ts BETWEEN toDateTime({{from:String}}, 'Europe/Tallinn') " f"AND toDateTime({{to:String}}, 'Europe/Tallinn')" ) if device: where += " AND device = {device:String}" params["device"] = device rows = ch_query( f"SELECT toUInt64(toUnixTimestamp(ts)) * 1000 AS t, device, contact, olek " f"FROM {CH_DB}.ukseandur WHERE {where} ORDER BY ts", params, ) return { "from": from_s, "to": to_s, "events": [ {"t": int(r["t"]), "device": r["device"], "contact": bool(r["contact"]), "olek": r["olek"]} for r in rows ], } @app.get("/api/vead", dependencies=[Depends(require_auth)]) async def vead( from_: str | None = Query(None, alias="from"), to: str | None = Query(None), device: str | None = Query(None), ) -> dict: from_s, to_s = resolve_range(from_, to) params = {"from": from_s, "to": to_s} where = ( f"ts BETWEEN toDateTime({{from:String}}, 'Europe/Tallinn') " f"AND toDateTime({{to:String}}, 'Europe/Tallinn')" ) if device: where += " AND device = {device:String}" params["device"] = device rows = ch_query( f"SELECT toUInt64(toUnixTimestamp(ts)) * 1000 AS t, device, tyyp, olek, detail, vaartus " f"FROM {CH_DB}.vead WHERE {where} ORDER BY ts", params, ) return { "from": from_s, "to": to_s, "events": [ { "t": int(r["t"]), "device": r["device"], "tyyp": r["tyyp"], "olek": r["olek"], "detail": r["detail"], "vaartus": r["vaartus"], } for r in rows ], } @app.get("/api/liikumine", dependencies=[Depends(require_auth)]) async def liikumine( from_: str | None = Query(None, alias="from"), to: str | None = Query(None), device: str | None = Query(None), ) -> dict: from_s, to_s = resolve_range(from_, to) params = {"from": from_s, "to": to_s} where = ( f"ts BETWEEN toDateTime({{from:String}}, 'Europe/Tallinn') " f"AND toDateTime({{to:String}}, 'Europe/Tallinn')" ) if device: where += " AND device = {device:String}" params["device"] = device rows = ch_query( f"SELECT toUInt64(toUnixTimestamp(ts)) * 1000 AS t, device, occupancy, illumination, olek " f"FROM {CH_DB}.liikumine WHERE {where} ORDER BY ts", params, ) return { "from": from_s, "to": to_s, "events": [ { "t": int(r["t"]), "device": r["device"], "occupancy": bool(r["occupancy"]), "illumination": r["illumination"], "olek": r["olek"], } for r in rows ], } @app.get("/api/energia", dependencies=[Depends(require_auth)]) async def energia( from_: str | None = Query(None, alias="from"), to: str | None = Query(None), device: str | None = Query(None), agg: str = Query("hour", pattern="^(hour|day)$"), ) -> dict: from_s, to_s = resolve_range(from_, to) params = {"from": from_s, "to": to_s} where = ( f"ts BETWEEN toDateTime({{from:String}}, 'Europe/Tallinn') " f"AND toDateTime({{to:String}}, 'Europe/Tallinn')" ) if device: where += " AND device = {device:String}" params["device"] = device if agg == "day": select = ( "toUInt64(toUnixTimestamp(toStartOfDay(ts))) * 1000 AS t, device, " "round(avg(power), 2) AS power, round(avg(voltage), 1) AS voltage, " "round(avg(current), 3) AS current, max(energy) AS energy" ) group = "GROUP BY t, device" else: select = ( "toUInt64(toUnixTimestamp(ts)) * 1000 AS t, device, " "power, voltage, current, energy" ) group = "" rows = ch_query( f"SELECT {select} FROM {CH_DB}.energia WHERE {where} {group} ORDER BY t", params, ) series: dict[str, dict] = {} for row in rows: s2 = series.setdefault( row["device"], {"t": [], "power": [], "voltage": [], "current": [], "energy": []} ) s2["t"].append(int(row["t"])) s2["power"].append(row["power"]) s2["voltage"].append(row["voltage"]) s2["current"].append(row["current"]) s2["energy"].append(row["energy"]) return {"from": from_s, "to": to_s, "agg": agg, "series": series} @app.get("/api/uus67_latest", dependencies=[Depends(require_auth)]) async def uus67_latest() -> dict: energy = ch_query( f"SELECT device, argMax(power, ts) AS power, argMax(voltage, ts) AS voltage, " f"argMax(current, ts) AS current, argMax(energy, ts) AS energy, " f"toUInt64(toUnixTimestamp(max(ts))) * 1000 AS t " f"FROM {CH_DB}.energia GROUP BY device ORDER BY device" ) motion = ch_query( f"SELECT device, argMax(occupancy, ts) AS occupancy, " f"argMax(illumination, ts) AS illumination, " f"toUInt64(toUnixTimestamp(max(ts))) * 1000 AS t " f"FROM {CH_DB}.liikumine GROUP BY device ORDER BY device" ) return { "energy": [ { "device": r["device"], "power": r.get("power"), "voltage": r.get("voltage"), "current": r.get("current"), "energy": r.get("energy"), "t": int(r["t"]) if r.get("t") is not None else None, } for r in energy ], "motion": [ { "device": r["device"], "occupancy": bool(r.get("occupancy")), "illumination": r.get("illumination"), "t": int(r["t"]) if r.get("t") is not None else None, } for r in motion ], } @app.get("/api/latest") async def latest(request: Request) -> dict: readings = ch_query( f"SELECT device, " f"argMax(temperature, hour) AS temperature, " f"argMax(humidity, hour) AS humidity, " f"toString(max(hour)) AS ts_str, " f"toUInt64(toUnixTimestamp(max(hour))) * 1000 AS t " f"FROM {CH_DB}.temperatuur GROUP BY device ORDER BY device" ) faults = ch_query( f"SELECT device, tyyp, argMax(detail, ts) AS detail_now, " f"toString(max(ts)) AS ts_str, argMax(olek, ts) AS olek_now " f"FROM {CH_DB}.vead GROUP BY device, tyyp HAVING olek_now = 'aktiveeritud'" ) if is_authed(request) else [] outside_rows = ch_query( f"SELECT toString(max(hour)) AS ts_str, " f"argMax(temperature, hour) AS temperature, " f"argMax(humidity, hour) AS humidity, " f"argMax(wind, hour) AS wind, " f"argMax(rain, hour) AS rain, " f"toUInt64(toUnixTimestamp(max(hour))) * 1000 AS t " f"FROM {CH_DB}.valisilm FINAL" ) outside: dict = { "temperature": None, "humidity": None, "wind": None, "rain": None, "hour": None, "t": None, } if outside_rows: r = outside_rows[0] outside = { "temperature": r.get("temperature"), "humidity": r.get("humidity"), "wind": r.get("wind"), "rain": r.get("rain"), "hour": r.get("ts_str"), "t": int(r["t"]) if r.get("t") is not None else None, } return { "readings": [ { "device": r["device"], "temperature": r["temperature"], "humidity": r["humidity"], "hour": r["ts_str"], "t": int(r["t"]) if r.get("t") is not None else None, } for r in readings ], "open_faults": [ {"device": r["device"], "tyyp": r["tyyp"], "detail": r["detail_now"], "ts": r["ts_str"]} for r in faults ], "outside": outside, } LOGIN_HTML = """ Nutikodu — login

🔒 Nutikodu — sisselogimine

{{ERR}}
← Temperatuuri leht (avalik)
""" @app.get("/login") async def login_page(): return HTMLResponse(LOGIN_HTML.replace("{{ERR}}", "")) @app.post("/login") async def login_submit(request: Request, password: str = Form("")): ip = client_ip(request) ra = retry_after_seconds(ip) if ra: return HTMLResponse( LOGIN_HTML.replace("{{ERR}}", f"Liiga palju katseid. Proovi {ra} sekundi pärast."), status_code=429, ) if WEB_PASSWORD and secrets.compare_digest(password, WEB_PASSWORD): reset_login_failures(ip) request.session["auth"] = True return RedirectResponse("/?auth=1", status_code=303) register_login_failure(ip) return HTMLResponse(LOGIN_HTML.replace("{{ERR}}", "Vale parool"), status_code=401) @app.get("/logout") async def logout(request: Request): request.session.clear() return RedirectResponse("/", status_code=303) @app.get("/api/me") async def me(request: Request) -> dict: return {"authed": is_authed(request)} def _ha_headers() -> dict: return {"Authorization": f"Bearer {HA_TOKEN}", "Content-Type": "application/json"} def ha_get_away() -> bool | None: if not HA_TOKEN: return None try: r = requests.get(f"{HA_URL}/api/states/{AWAY_ENTITY}", headers=_ha_headers(), timeout=8) if r.status_code == 200: return r.json().get("state") == "on" except requests.RequestException: pass return None def ha_set_away(on: bool) -> None: service = "turn_on" if on else "turn_off" r = requests.post( f"{HA_URL}/api/services/input_boolean/{service}", headers=_ha_headers(), json={"entity_id": AWAY_ENTITY}, timeout=8, ) r.raise_for_status() @app.get("/api/away", dependencies=[Depends(require_auth)]) async def away_state() -> dict: """Kohalolu ("kodust eemal") olek.""" state = ha_get_away() return {"away": state, "available": state is not None} @app.post("/api/away", dependencies=[Depends(require_auth)]) async def away_set(payload: dict = Body(...)) -> dict: """Lülita "kodust eemal" sisse/välja (keha {"on": true|false}).""" on = bool(payload.get("on")) try: ha_set_away(on) except requests.RequestException as exc: raise HTTPException(status_code=502, detail=f"Home Assistant viga: {exc}") from exc return {"away": on, "available": True} @app.get("/health") async def health() -> dict: try: ch_query("SELECT 1") ch_ok = True except HTTPException: ch_ok = False return {"status": "ok", "clickhouse": ch_ok} @app.get("/") async def index() -> FileResponse: return FileResponse(STATIC_DIR / "index.html", media_type="text/html") app.mount("/", StaticFiles(directory=STATIC_DIR, html=True), name="static") if __name__ == "__main__": import uvicorn uvicorn.run("app:app", host="0.0.0.0", port=8095, reload=True)