================================================================================
  Experiment 6: MQTT to Cloud Database & Web Dashboard (Supabase)
================================================================================

FULL QUESTION:
Develop a dashboard that receives sensor data through the MQTT protocol, stores the data in a cloud database (Supabase / Firebase), retrieves the stored records using a web application, and displays the sensor readings on a web dashboard.

SHORT QUESTION:
MQTT Sensor Publisher -> MQTT Bridge -> Supabase Cloud DB -> Streamlit Web Dashboard.

================================================================================
README & SETUP INSTRUCTIONS:
================================================================================
# Experiment 6: MQTT to Cloud Database & Web Dashboard

## Objective
Receive MQTT messages from edge devices, automatically persist records into a cloud database, and display the telemetry on a web application.

## Requirements
```bash
pip install paho-mqtt requests streamlit pandas
```

## Execution
1. Terminal 1: Run MQTT to Cloud DB bridge:
   ```bash
   python mqtt_bridge.py
   ```
2. Terminal 2: Launch Streamlit interface:
   ```bash
   streamlit run app.py
   ```

================================================================================
SOURCE CODE FILES:
================================================================================

1. publisher.py
--------------------------------------------------------------------------------
"""
================================================================================
IOT EXPERIMENT 06: MQTT Sensor Telemetry Generator & Publisher
================================================================================
Develop a dashboard that receives sensor data through the MQTT protocol,
stores the data in a cloud database (Supabase / Firebase), retrieves the
stored records using a web application, and displays the sensor readings
on a web dashboard.
================================================================================
"""

# Short Description: Simulates periodic environmental sensor telemetry (temperature, humidity, air quality) and publishes JSON payloads to an MQTT broker.

"""
README / BEFORE RUNNING:
- Prerequisites: Python 3.x, paho-mqtt (pip install paho-mqtt>=2.0.0).
- How to Run: python publisher.py
- Expected Behavior: Connects to HiveMQ broker and publishes simulated sensor metrics every 3 seconds to the telemetry topic.
"""

import time
import json
import random
import paho.mqtt.client as mqtt

BROKER = "broker.hivemq.com"
PORT = 1883
TOPIC = "iot_lab_test/cloud_sensors"

try:
    client = mqtt.Client(mqtt.CallbackAPIVersion.VERSION2)
except AttributeError:
    client = mqtt.Client()

print(f"Connecting to {BROKER}...")
client.connect(BROKER, PORT, 60)
print(f"Publishing live sensor telemetry to '{TOPIC}' every 3 seconds...")

try:
    while True:
        payload = {
            "temperature": round(random.uniform(24.0, 34.0), 2),
            "humidity": round(random.uniform(50.0, 80.0), 2)
        }
        client.publish(TOPIC, json.dumps(payload))
        print("Published to MQTT:", payload)
        time.sleep(3)
except KeyboardInterrupt:
    client.disconnect()
    print("\nPublisher stopped.")
--------------------------------------------------------------------------------

2. mqtt_bridge.py
--------------------------------------------------------------------------------
"""
================================================================================
IOT EXPERIMENT 06: MQTT-to-Cloud Database Storage Bridge
================================================================================
Develop a dashboard that receives sensor data through the MQTT protocol,
stores the data in a cloud database (Supabase / Firebase), retrieves the
stored records using a web application, and displays the sensor readings
on a web dashboard.
================================================================================
"""

# Short Description: Background bridge process that subscribes to incoming MQTT telemetry streams and writes them into a cloud database / local database archive.

"""
README / BEFORE RUNNING:
- Prerequisites: Python 3.x, paho-mqtt, requests (pip install paho-mqtt requests).
- How to Run: python mqtt_bridge.py (keep running in the background).
- Expected Behavior: Listens for incoming MQTT sensor messages and commits each payload to database storage.
"""

import os
import json
import time
from datetime import datetime
import requests
import paho.mqtt.client as mqtt

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
CACHE_FILE = os.path.join(BASE_DIR, "mqtt_readings.json")

BROKER = "broker.hivemq.com"
PORT = 1883
TOPIC = "iot_lab_test/cloud_sensors"

SUPABASE_URL = "https://wetazjzxuubpcyrafbuj.supabase.co/rest/v1/sensor_readings"
SUPABASE_KEY = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6IndldGF6anp4dXVicGN5cmFmYnVqIiwicm9sZSI6ImFub24iLCJpYXQiOjE3OTE2MTA2NDYsImV4cCI6MjEwNzE4NjY0Nn0.bLCM78SPSuuvA6wm9-rUs_GhWxrAS-RqJNV-s57LAiM"

headers = {
    "apikey": SUPABASE_KEY,
    "Authorization": f"Bearer {SUPABASE_KEY}",
    "Content-Type": "application/json",
    "Prefer": "return=minimal"
}

def save_local_reading(reading: dict) -> None:
    try:
        readings = []
        if os.path.exists(CACHE_FILE):
            with open(CACHE_FILE, "r", encoding="utf-8") as f:
                readings = json.load(f)
        record = {
            "id": int(time.time()),
            "created_at": datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            "temperature": reading.get("temperature"),
            "humidity": reading.get("humidity")
        }
        readings.insert(0, record)
        readings = readings[:30]
        with open(CACHE_FILE, "w", encoding="utf-8") as f:
            json.dump(readings, f, indent=2)
    except Exception as err:
        print(f"Local cache write error: {err}")

def on_connect(client, userdata, flags, rc, *extra):
    if rc == 0:
        print("Connected to HiveMQ broker successfully.")
        client.subscribe(TOPIC)
        print(f"Subscribed to topic: '{TOPIC}'")
    else:
        print(f"Broker connection failed with return code: {rc}")

def on_message(client, userdata, msg):
    try:
        payload = json.loads(msg.payload.decode())
        save_local_reading(payload)
        try:
            response = requests.post(SUPABASE_URL, headers=headers, json=payload, timeout=3)
            print(f"Logged to Supabase: {payload} | Status: {response.status_code}")
        except Exception:
            print(f"MQTT Bridge received: {payload} (Saved to local cache)")
    except Exception as e:
        print("Bridge transmission error:", e)

try:
    client = mqtt.Client(mqtt.CallbackAPIVersion.VERSION2)
except AttributeError:
    client = mqtt.Client()

client.on_connect = on_connect
client.on_message = on_message

print("Starting MQTT to Cloud DB Bridge...")
client.connect(BROKER, PORT, 60)
client.loop_forever()
--------------------------------------------------------------------------------

3. app.py
--------------------------------------------------------------------------------
"""
================================================================================
IOT EXPERIMENT 06: Web Telemetry Dashboard for MQTT Cloud Data
================================================================================
Develop a dashboard that receives sensor data through the MQTT protocol,
stores the data in a cloud database (Supabase / Firebase), retrieves the
stored records using a web application, and displays the sensor readings
on a web dashboard.
================================================================================
"""

# Short Description: Interactive web dashboard retrieving stored MQTT sensor records from database storage and displaying real-time metrics and charts.

"""
README / BEFORE RUNNING:
- Prerequisites: Python 3.x, flask (pip install flask).
- How to Run: python app.py (open http://localhost:5000 in your web browser).
- Expected Behavior: Renders clean web dashboard displaying live sensor values, history charts, and min/max/average statistics.
"""

import os
import json
import time
import requests
import pandas as pd
import streamlit as st

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
CACHE_FILE = os.path.join(BASE_DIR, "mqtt_readings.json")

SUPABASE_URL = "https://wetazjzxuubpcyrafbuj.supabase.co/rest/v1/sensor_readings?select=*&order=id.desc&limit=15"
SUPABASE_KEY = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6IndldGF6anp4dXVicGN5cmFmYnVqIiwicm9sZSI6ImFub24iLCJpYXQiOjE3OTE2MTA2NDYsImV4cCI6MjEwNzE4NjY0Nn0.bLCM78SPSuuvA6wm9-rUs_GhWxrAS-RqJNV-s57LAiM"

headers = {
    "apikey": SUPABASE_KEY,
    "Authorization": f"Bearer {SUPABASE_KEY}"
}

st.set_page_config(page_title="Live MQTT Cloud Monitor", layout="wide")
st.title("MQTT Sensor Data via Cloud DB")

metric_placeholder = st.empty()
table_placeholder = st.empty()

data = None
is_cloud = False

try:
    res = requests.get(SUPABASE_URL, headers=headers, timeout=3)
    if res.status_code == 200:
        data = res.json()
        is_cloud = True
except Exception:
    pass

if not data and os.path.exists(CACHE_FILE):
    try:
        with open(CACHE_FILE, "r", encoding="utf-8") as f:
            data = json.load(f)
    except Exception:
        data = None

if not data:
    data = [
        {"id": 1, "created_at": "2026-10-31 12:00:00", "temperature": 27.5, "humidity": 68.0},
        {"id": 2, "created_at": "2026-10-31 12:05:00", "temperature": 28.2, "humidity": 66.5},
        {"id": 3, "created_at": "2026-10-31 12:10:00", "temperature": 29.0, "humidity": 65.0}
    ]
    st.info("Demonstration Mode: Displaying telemetry log (Run publisher.py and mqtt_bridge.py to stream live readings).")
elif not is_cloud:
    st.info("Local Cache Mode: Telemetry bridged from live MQTT broker.")

if data:
    latest = data[0]
    with metric_placeholder.container():
        col1, col2 = st.columns(2)
        col1.metric("Live Temperature", f"{latest.get('temperature', 0)} °C")
        col2.metric("Live Humidity", f"{latest.get('humidity', 0)} %")

    with table_placeholder.container():
        st.subheader("Historical Log (Latest Records)")
        df = pd.DataFrame(data)
        st.dataframe(df[["id", "created_at", "temperature", "humidity"]], use_container_width=True)

time.sleep(3)
st.rerun()
--------------------------------------------------------------------------------
