================================================================================
  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
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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://YOUR_PROJECT_ID.supabase.co/rest/v1/sensor_readings?select=*&order=id.desc&limit=15"
SUPABASE_KEY = "YOUR_ANON_PUBLIC_KEY"

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

st.title("MQTT Sensor Data via Cloud DB")

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

data = None
try:
    res = requests.get(SUPABASE_URL, headers=headers, timeout=3)
    if res.status_code == 200:
        data = res.json()
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:
        pass

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}
    ]

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

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

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