"""Session 10 - the smallest useful farm REST API (Flask + SQLite) - ~50 lines.

python mini_api.py      ->  http://127.0.0.1:5001
POST /api/readings      {"node_id":"greenhouse_01","air_temp_c":28.4,"soil_moisture_pct":41}
GET  /api/readings?node=greenhouse_01&hours=24
GET  /api/latest
Also serves index.html (session 9) so the page can call the API from the same origin (no CORS issues).
"""
import sqlite3
from datetime import datetime, timedelta
from pathlib import Path

from flask import Flask, jsonify, request, send_from_directory

HERE = Path(__file__).resolve().parent
DB = HERE / "mini_farm.db"
app = Flask(__name__)


def db():
    con = sqlite3.connect(DB)
    con.row_factory = sqlite3.Row
    return con


with db() as con:
    con.execute("CREATE TABLE IF NOT EXISTS readings (id INTEGER PRIMARY KEY, node_id TEXT, ts TEXT, "
                "air_temp_c REAL, air_humidity_pct REAL, soil_moisture_pct REAL)")
    con.execute("CREATE INDEX IF NOT EXISTS ix ON readings (node_id, ts)")


@app.post("/api/readings")
def add_reading():
    d = request.get_json(silent=True)
    if not d or "node_id" not in d:
        return jsonify(error="JSON body with node_id required"), 400          # 400 = client error
    ts = d.get("ts") or datetime.now().isoformat(timespec="seconds")
    with db() as con:
        con.execute("INSERT INTO readings (node_id, ts, air_temp_c, air_humidity_pct, soil_moisture_pct) "
                    "VALUES (?,?,?,?,?)", (d["node_id"], ts, d.get("air_temp_c"), d.get("air_humidity_pct"),
                                          d.get("soil_moisture_pct")))
    return jsonify(ok=True, ts=ts), 201                                          # 201 = created


@app.get("/api/readings")
def list_readings():
    hours = int(request.args.get("hours", 24))
    since = (datetime.now() - timedelta(hours=hours)).isoformat(timespec="seconds")
    sql, args = "SELECT * FROM readings WHERE ts >= ?", [since]
    if request.args.get("node"):
        sql += " AND node_id = ?"
        args.append(request.args["node"])
    with db() as con:
        rows = [dict(r) for r in con.execute(sql + " ORDER BY ts", args)]
    return jsonify(count=len(rows), readings=rows)


@app.get("/api/latest")
def latest():
    with db() as con:
        rows = [dict(r) for r in con.execute(
            "SELECT * FROM readings r WHERE ts = (SELECT MAX(ts) FROM readings WHERE node_id = r.node_id)")]
    return jsonify(nodes=rows)


@app.get("/")
def page():
    return send_from_directory(HERE, "index.html")


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=5001, debug=True)