from flask import Flask, request, jsonify, send_file, g, Response
from flask_cors import CORS
from datetime import datetime
import sqlite3
import time
import os
import csv
import io

# ─── CONFIG ──────────────────────────────────────────────────
HOST    = "0.0.0.0"
PORT    = 7860
DB_PATH = os.path.join(os.path.dirname(__file__), "telemetry.db")

app = Flask(__name__)
CORS(app)

# ─── DATABASE ─────────────────────────────────────────────────

def get_db():
    if "db" not in g:
        g.db = sqlite3.connect(DB_PATH, detect_types=sqlite3.PARSE_DECLTYPES)
        g.db.row_factory = sqlite3.Row
        g.db.execute("PRAGMA journal_mode=WAL")
        g.db.execute("PRAGMA synchronous=NORMAL")
    return g.db


@app.teardown_appcontext
def close_db(exc=None):
    db = g.pop("db", None)
    if db:
        db.close()


def init_db():
    with sqlite3.connect(DB_PATH) as db:
        db.execute("PRAGMA journal_mode=WAL")
        db.execute("""
            CREATE TABLE IF NOT EXISTS telemetry (
                id             INTEGER PRIMARY KEY AUTOINCREMENT,
                device_id      TEXT    NOT NULL,
                server_time    TEXT    NOT NULL,
                server_unix    REAL    NOT NULL,
                uptime_sec     INTEGER DEFAULT 0,

                -- Motion
                speed_pct      REAL    NOT NULL,
                speed_ups      REAL    NOT NULL,
                pos_x          REAL    NOT NULL,
                pos_y          REAL    NOT NULL DEFAULT 0,
                pos_z          REAL    NOT NULL DEFAULT 0,
                total_distance REAL    NOT NULL,

                -- Power
                voltage_V      REAL    NOT NULL,
                current_mA     REAL    NOT NULL,
                current_A      REAL    NOT NULL,
                power_mW       REAL    NOT NULL,
                power_W        REAL    NOT NULL,
                drawn_mW       REAL    NOT NULL DEFAULT 0,
                generated_mW   REAL    NOT NULL DEFAULT 0,
                samples        INTEGER NOT NULL DEFAULT 25,
                interval_sec   INTEGER NOT NULL DEFAULT 5
            )
        """)

        db.execute("""
            CREATE INDEX IF NOT EXISTS idx_device_time
            ON telemetry (device_id, server_unix DESC)
        """)

        db.commit()

    print(f"[DB] SQLite ready → {DB_PATH}")


# 🔴 IMPORTANT FIX (Gunicorn compatible)
with app.app_context():
    init_db()


def row_to_dict(row):
    return dict(row)


# ─── HELPERS ──────────────────────────────────────────────────

def query(sql, params=(), one=False):
    cur = get_db().execute(sql, params)
    rows = cur.fetchall()
    result = [row_to_dict(r) for r in rows]
    return result[0] if (one and result) else (None if one else result)


def compute_stats(rows):
    if not rows:
        return {}

    n = len(rows)

    return {
        "speed_avg_pct": round(sum(r["speed_pct"] for r in rows) / n, 2),
        "speed_max_pct": round(max(r["speed_pct"] for r in rows), 2),
        "power_avg_mW": round(sum(r["power_mW"] for r in rows) / n, 2),
        "power_max_mW": round(max(r["power_mW"] for r in rows), 2),
        "voltage_avg_V": round(sum(r["voltage_V"] for r in rows) / n, 3),
        "pos_x_latest": round(rows[-1]["pos_x"], 3),
        "pos_x_range": round(
            max(r["pos_x"] for r in rows) - min(r["pos_x"] for r in rows), 3
        ),
    }


def safe_float(data, key, default=0.0):
    try:
        return float(data.get(key, default))
    except (TypeError, ValueError):
        return default


# ─── ROUTES ───────────────────────────────────────────────────

@app.post("/api/telemetry")
def receive_telemetry():

    data = request.get_json(force=True, silent=True)

    if not data:
        return jsonify({"error": "Invalid JSON"}), 400

    required = ["device_id", "speed_pct", "speed_ups", "position", "total_distance", "power"]

    missing = [k for k in required if k not in data]

    if missing:
        return jsonify({"error": f"Missing fields: {missing}"}), 400

    pos = data["position"]
    pwr = data["power"]

    now = datetime.now()
    server_time = now.isoformat(timespec="milliseconds")
    server_unix = time.time()

    db = get_db()

    db.execute("""
        INSERT INTO telemetry (
            device_id, server_time, server_unix, uptime_sec,
            speed_pct, speed_ups,
            pos_x, pos_y, pos_z, total_distance,
            voltage_V, current_mA, current_A,
            power_mW, power_W, drawn_mW, generated_mW,
            samples, interval_sec
        ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    """, (
        data["device_id"],
        server_time,
        server_unix,
        data.get("uptime_sec", 0),

        float(data["speed_pct"]),
        float(data["speed_ups"]),

        safe_float(pos, "x"),
        safe_float(pos, "y"),
        safe_float(pos, "z"),
        float(data["total_distance"]),

        float(pwr["voltage_V"]),
        float(pwr["current_mA"]),
        float(pwr["current_A"]),
        float(pwr["power_mW"]),
        float(pwr["power_W"]),

        safe_float(pwr, "drawn_mW"),
        safe_float(pwr, "generated_mW"),

        int(pwr.get("samples", 25)),
        int(pwr.get("interval_sec", 5)),
    ))

    db.commit()

    return jsonify({"status": "ok", "server_time": server_time}), 201


@app.get("/api/latest")
def get_latest():

    row = query(
        "SELECT * FROM telemetry ORDER BY server_unix DESC LIMIT 1",
        one=True
    )

    if not row:
        return jsonify({"error": "No data yet"}), 404

    total = query("SELECT COUNT(*) AS n FROM telemetry", one=True)["n"]

    return jsonify({
        "latest": row,
        "total_stored": total
    })


@app.get("/api/history")
def get_history():

    limit = min(int(request.args.get("limit", 200)), 1000)

    rows = query(
        "SELECT * FROM telemetry ORDER BY server_unix DESC LIMIT ?",
        (limit,)
    )

    rows = list(reversed(rows))

    return jsonify({
        "readings": rows,
        "count": len(rows),
        "stats": compute_stats(rows)
    })


@app.get("/api/devices")
def get_devices():

    rows = query("""
        SELECT device_id,
               COUNT(*) AS total_packets,
               MAX(server_time) AS last_seen,
               MAX(pos_x) AS max_x,
               MAX(speed_pct) AS max_speed,
               AVG(voltage_V) AS avg_voltage,
               AVG(power_mW) AS avg_power
        FROM telemetry
        GROUP BY device_id
    """)

    devices = {r["device_id"]: r for r in rows}

    return jsonify({
        "devices": devices,
        "count": len(devices)
    })


@app.get("/api/export")
def export_csv():

    rows = query("SELECT * FROM telemetry ORDER BY server_unix ASC")

    if not rows:
        return jsonify({"error": "No data"}), 404

    out = io.StringIO()

    writer = csv.DictWriter(out, fieldnames=rows[0].keys())
    writer.writeheader()
    writer.writerows(rows)

    return Response(
        out.getvalue(),
        mimetype="text/csv",
        headers={"Content-Disposition": "attachment; filename=telemetry_all.csv"}
    )


@app.delete("/api/clear")
def clear_data():

    db = get_db()
    db.execute("DELETE FROM telemetry")
    db.commit()

    return jsonify({"status": "ok", "message": "All data cleared"})


@app.get("/health")
def health():

    row = query("SELECT COUNT(*) AS n FROM telemetry", one=True)

    return jsonify({
        "status": "ok",
        "db": DB_PATH,
        "total_records": row["n"] if row else 0,
        "server_time": datetime.now().isoformat(timespec="milliseconds")
    })


@app.get("/")
def dashboard():

    html_path = os.path.join(os.path.dirname(__file__), "dashboard.html")

    return send_file(html_path)


# ─── MAIN ─────────────────────────────────────────────────────

if __name__ == "__main__":

    init_db()

    print(f"\n[SERVER] Flask running  → http://{HOST}:{PORT}")
    print(f"[SERVER] Dashboard      → http://localhost:{PORT}")
    print(f"[SERVER] API telemetry  → POST http://localhost:{PORT}/api/telemetry")
    print(f"[SERVER] DB path        → {DB_PATH}\n")

    app.run(host=HOST, port=PORT, debug=True)