"""
Reptile Monitor — History API
  - Subscribes reptile/sensor via MQTT and stores readings every 5 min in SQLite
  - GET /api/history?range=1d|1w|1m  → JSON array for Chart.js
  - GET /api/health                   → status check
"""

import json, sqlite3, threading, time
from datetime import datetime
from flask import Flask, jsonify, request
from flask_cors import CORS
import paho.mqtt.client as mqtt

app = Flask(__name__)
CORS(app)

# ── Config ────────────────────────────────────────────────────────────────────
DB_PATH  = '/data/reptile.db'
BROKER   = '192.168.11.201'
PORT     = 1883
TOPIC    = 'reptile/sensor'
INTERVAL = 300          # store one row per 5 minutes

# ── Database ──────────────────────────────────────────────────────────────────
def get_db():
    db = sqlite3.connect(DB_PATH)
    db.row_factory = sqlite3.Row
    return db

def init_db():
    with get_db() as db:
        db.execute('''
            CREATE TABLE IF NOT EXISTS readings (
                ts     INTEGER PRIMARY KEY,   -- Unix time rounded to 5-min bucket
                temp_b REAL,
                hum_b  REAL,
                temp_t REAL,
                hum_t  REAL
            )
        ''')
        db.commit()
    print('[DB] initialized:', DB_PATH)

# ── MQTT subscriber ───────────────────────────────────────────────────────────
last_bucket = [0]

def on_message(client, userdata, msg):
    try:
        data   = json.loads(msg.payload)
        bucket = int(time.time()) // INTERVAL * INTERVAL
        if bucket <= last_bucket[0]:
            return
        last_bucket[0] = bucket
        with get_db() as db:
            db.execute(
                'INSERT OR IGNORE INTO readings VALUES (?,?,?,?,?)',
                (bucket,
                 data.get('temp_b'), data.get('hum_b'),
                 data.get('temp_t'), data.get('hum_t'))
            )
        print(f'[DB] stored bucket={bucket} temp_b={data.get("temp_b")}')
    except Exception as e:
        print(f'[MQTT] error: {e}')

def mqtt_thread():
    client = mqtt.Client(client_id='reptile-history-api')
    client.on_message = on_message
    while True:
        try:
            client.connect(BROKER, PORT, 60)
            client.subscribe(TOPIC)
            print(f'[MQTT] connected to {BROKER}, subscribed to {TOPIC}')
            client.loop_forever()
        except Exception as e:
            print(f'[MQTT] connection failed: {e} — retry in 10s')
            time.sleep(10)

# ── REST API ──────────────────────────────────────────────────────────────────
RANGE_MAP = {'1d': 86400, '1w': 604800, '1m': 2592000}

@app.route('/api/history')
def history():
    range_key = request.args.get('range', '1d')
    delta     = RANGE_MAP.get(range_key, 86400)
    since     = int(time.time()) - delta

    with get_db() as db:
        rows = db.execute(
            '''SELECT ts, temp_b, hum_b, temp_t, hum_t
               FROM readings WHERE ts >= ? ORDER BY ts''',
            (since,)
        ).fetchall()

    return jsonify([{
        'ts':     r['ts'] * 1000,   # milliseconds for Chart.js
        'temp_b': r['temp_b'],
        'hum_b':  r['hum_b'],
        'temp_t': r['temp_t'],
        'hum_t':  r['hum_t'],
    } for r in rows])

@app.route('/api/health')
def health():
    with get_db() as db:
        count = db.execute('SELECT COUNT(*) FROM readings').fetchone()[0]
    return jsonify({'status': 'ok', 'rows': count, 'time': datetime.now().isoformat()})

# ── Entry point ───────────────────────────────────────────────────────────────
if __name__ == '__main__':
    init_db()
    threading.Thread(target=mqtt_thread, daemon=True).start()
    app.run(host='0.0.0.0', port=5001, debug=False)