================================================================================
  Question CQ7: MQTT to Cloud Database (Supabase) & Web Dashboard [30 Marks]
================================================================================

FULL QUESTION:
CAE 2 IoT Practical Exam - Combined Question:
CQ7. MQTT Telemetry Bridge to Supabase Cloud Database and Streamlit Web Dashboard [30 Marks]
Develop an end-to-end IoT cloud persistence and analytics system using the MQTT protocol, Supabase cloud database, and Streamlit.
Data Parameters: Device ID, Temperature (°C), Relative Humidity (%), Light Level (Lux), and US AQI.
Cloud Database: Supabase PostgreSQL via PostgREST REST API.
The system should:
- Implement an MQTT Publisher that streams edge sensor telemetry in JSON format to a public broker (broker.hivemq.com) every 3 seconds. [6 Marks]
- Implement a Python MQTT-to-Cloud Bridge subscriber using paho-mqtt that intercepts incoming messages and inserts each record into a Supabase cloud database table via HTTP POST requests with proper API authentication headers. [8 Marks]
- Configure table schema (id, created_at, device_id, temperature, humidity, light, aqi) and handle network timeouts/fallbacks gracefully. [5 Marks]
- Develop a Streamlit web dashboard that queries the Supabase REST API via HTTP GET requests to retrieve historical and real-time records. [6 Marks]
- Implement interactive analytics on the dashboard: metric summary cards, time-series line charts, data filtering, and CSV export functionality. [5 Marks]
Flow: MQTT Sensor Publisher -> HiveMQ Broker -> MQTT Cloud Bridge -> Supabase REST API -> Streamlit Web Dashboard.

SHORT QUESTION:
MQTT Sensor Telemetry to Supabase Cloud Database Bridge and Streamlit Web Dashboard [30 Marks].

================================================================================
README & SETUP INSTRUCTIONS:
================================================================================
# Question CQ7: MQTT to Supabase Cloud Database & Web Dashboard

## Problem Statement [30 Marks]
Ingest sensor messages from an MQTT broker, store records into a Supabase cloud database table via REST POST, and visualize the cloud data on an interactive Streamlit dashboard.

- **Broker**: `broker.hivemq.com` (Port 1883)
- **Topic**: `iot_lab/supabase/telemetry`
- **Cloud Database**: Supabase REST API (`https://<project-id>.supabase.co/rest/v1/<table_name>`)
- **Dashboard**: Streamlit (`requests`, `pandas`, `streamlit`)

---

## Architectural Flow
```text
MQTT Publisher ──> MQTT Broker ──> Python Bridge ──(HTTP POST)──> Supabase Cloud DB ──(HTTP GET)──> Streamlit UI
```

---

## Files in this Package
- `publisher.py`: Simulates periodic environmental telemetry and publishes JSON payloads to the MQTT broker.
- `mqtt_bridge.py`: Paho-MQTT subscriber that forwards incoming messages to Supabase REST endpoint (with robust local buffer fallback).
- `app.py`: Streamlit web dashboard querying Supabase REST API, displaying telemetry trends and statistics.

---

## How to Run

### Step 1: Install Dependencies
```bash
pip install paho-mqtt requests streamlit pandas matplotlib
```

### Step 2: Start the MQTT-to-Cloud Bridge
Open Terminal 1:
```bash
python mqtt_bridge.py
```

### Step 3: Start the MQTT Publisher
Open Terminal 2:
```bash
python publisher.py
```

### Step 4: Launch the Streamlit Web Dashboard
Open Terminal 3:
```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/supabase/telemetry"

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

def main():
    client = create_client()
    print("=" * 60)
    print("  CQ7: MQTT Sensor Publisher for Cloud Database")
    print("=" * 60)
    print(f"Connecting to Broker: {BROKER}:{PORT}")

    try:
        client.connect(BROKER, PORT, keepalive=60)
        client.loop_start()
        print(f"Streaming telemetry to topic: {TOPIC}\n")

        counter = 0
        while True:
            counter += 1
            payload = {
                "device_id": f"ESP32-NODE-0{random.randint(1, 3)}",
                "temperature": round(random.uniform(22.0, 34.0), 2),
                "humidity": round(random.uniform(40.0, 85.0), 2),
                "light": round(random.uniform(150.0, 750.0), 1),
                "aqi": random.randint(35, 180),
                "timestamp": time.strftime("%Y-%m-%d %H:%M:%S")
            }

            msg = json.dumps(payload)
            client.publish(TOPIC, msg)
            print(f"[MQTT Pub] Sent Record #{counter}: {msg}")
            time.sleep(3)

    except KeyboardInterrupt:
        print("\n[Publisher] Disconnecting...")
        client.loop_stop()
        client.disconnect()

if __name__ == "__main__":
    main()
--------------------------------------------------------------------------------

2. mqtt_bridge.py
--------------------------------------------------------------------------------
import json
import time
import os
import requests
import paho.mqtt.client as mqtt

BROKER = "broker.hivemq.com"
PORT = 1883
TOPIC = "iot_lab/supabase/telemetry"

SUPABASE_URL = os.environ.get("SUPABASE_URL", "https://xyzcompany.supabase.co")
SUPABASE_KEY = os.environ.get("SUPABASE_KEY", "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.dummy_anon_key")
TABLE_NAME = "sensor_telemetry"
REST_ENDPOINT = f"{SUPABASE_URL}/rest/v1/{TABLE_NAME}"

LOCAL_LOG = "supabase_local_buffer.json"

def post_to_supabase(record):
    headers = {
        "apikey": SUPABASE_KEY,
        "Authorization": f"Bearer {SUPABASE_KEY}",
        "Content-Type": "application/json",
        "Prefer": "return=minimal"
    }
    try:
        resp = requests.post(REST_ENDPOINT, headers=headers, json=record, timeout=4)
        if resp.status_code in [200, 201]:
            print(f"  [Cloud DB] Successfully inserted into Supabase table: {TABLE_NAME}")
            return True
        else:
            print(f"  [Cloud DB Note] Supabase response code: {resp.status_code} (persisting to local buffer)")
    except Exception as e:
        print(f"  [Cloud DB Note] Remote Supabase endpoint unreachable: {e} (persisting to local buffer)")

    records = []
    if os.path.exists(LOCAL_LOG):
        try:
            with open(LOCAL_LOG, "r") as f:
                records = json.load(f)
        except Exception:
            records = []
    records.append(record)
    with open(LOCAL_LOG, "w") as f:
        json.dump(records[-100:], f, indent=2)
    print(f"  [Buffer] Persisted record locally to {LOCAL_LOG}")
    return False

def on_message(client, userdata, msg):
    try:
        data = json.loads(msg.payload.decode("utf-8"))
        print(f"\n[MQTT Ingest] Received from {data.get('device_id')}: Temp={data.get('temperature')}°C, Hum={data.get('humidity')}%")
        post_to_supabase(data)
    except Exception as e:
        print(f"[Bridge Error] {e}")

def main():
    print("=" * 60)
    print("  CQ7: MQTT to Supabase Cloud Bridge Service")
    print("=" * 60)
    print(f"Connecting to MQTT Broker: {BROKER}:{PORT}")

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

    client.on_message = on_message
    client.connect(BROKER, PORT, keepalive=60)
    client.subscribe(TOPIC)
    print(f"Subscribed to {TOPIC}. Forwarding all telemetry to Supabase REST...\n")

    try:
        client.loop_forever()
    except KeyboardInterrupt:
        print("\n[Bridge] Stopping...")
        client.disconnect()

if __name__ == "__main__":
    main()
--------------------------------------------------------------------------------

3. app.py
--------------------------------------------------------------------------------
import os
import json
import time
import requests
import pandas as pd
import streamlit as st

st.set_page_config(page_title="CQ7: Supabase Cloud Dashboard", layout="wide", page_icon="☁️")

SUPABASE_URL = os.environ.get("SUPABASE_URL", "https://xyzcompany.supabase.co")
SUPABASE_KEY = os.environ.get("SUPABASE_KEY", "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.dummy_anon_key")
TABLE_NAME = "sensor_telemetry"
REST_ENDPOINT = f"{SUPABASE_URL}/rest/v1/{TABLE_NAME}?select=*&order=id.desc&limit=50"
LOCAL_LOG = "supabase_local_buffer.json"

def fetch_cloud_records():
    headers = {
        "apikey": SUPABASE_KEY,
        "Authorization": f"Bearer {SUPABASE_KEY}"
    }
    try:
        resp = requests.get(REST_ENDPOINT, headers=headers, timeout=3)
        if resp.status_code == 200:
            data = resp.json()
            if data:
                return pd.DataFrame(data), "Connected to Live Supabase Cloud DB"
    except Exception:
        pass

    if os.path.exists(LOCAL_LOG):
        try:
            with open(LOCAL_LOG, "r") as f:
                data = json.load(f)
                if data:
                    return pd.DataFrame(data), "Connected via Local Database Buffer (Offline Mode)"
        except Exception:
            pass

    seed_data = [
        {"device_id": "ESP32-NODE-01", "temperature": 27.5, "humidity": 62.0, "light": 450.0, "aqi": 65, "timestamp": "10:00:00"},
        {"device_id": "ESP32-NODE-02", "temperature": 28.2, "humidity": 59.0, "light": 520.0, "aqi": 72, "timestamp": "10:01:00"},
        {"device_id": "ESP32-NODE-01", "temperature": 29.0, "humidity": 56.0, "light": 610.0, "aqi": 80, "timestamp": "10:02:00"},
    ]
    return pd.DataFrame(seed_data), "Displaying Simulated Telemetry Buffer"

def main():
    st.title("☁️ Supabase Cloud IoT Telemetry & Web Dashboard")
    st.markdown("**CAE 2 Combined Question 7**: MQTT Protocol + Supabase Cloud REST Database (`paho-mqtt` + `requests` + `Streamlit`)")
    st.markdown("---")

    df, status_msg = fetch_cloud_records()

    with st.sidebar:
        st.header("☁️ Cloud Database Info")
        st.write(f"**Backend:** Supabase PostgreSQL")
        st.write(f"**Table:** `{TABLE_NAME}`")
        st.write(f"**Status:** `{status_msg}`")
        st.markdown("---")
        st.subheader("Actions")
        if st.button("🔄 Refresh Telemetry from Cloud", type="primary", use_container_width=True):
            st.rerun()

    st.subheader("1. Latest Telemetry Snapshot")
    latest = df.iloc[-1]
    c1, c2, c3, c4 = st.columns(4)
    c1.metric("Device ID", str(latest.get("device_id", "ESP32-01")))
    c2.metric("Temperature", f"{latest.get('temperature', 25.0)} °C")
    c3.metric("Humidity", f"{latest.get('humidity', 60.0)} %")
    c4.metric("US AQI", f"{latest.get('aqi', 50)}")

    st.markdown("---")

    st.subheader("2. Historical Telemetry Trends (Stored in Supabase)")
    col_chart1, col_chart2 = st.columns(2)
    with col_chart1:
        st.write("**Temperature (°C) Trend**")
        st.line_chart(df[["temperature"]])
    with col_chart2:
        st.write("**Humidity (%) & Light Level (Lux) Trend**")
        st.line_chart(df[["humidity", "light"]])

    st.markdown("---")

    st.subheader("3. Stored Records Log (Supabase Table)")
    st.dataframe(df, use_container_width=True)

    csv_data = df.to_csv(index=False).encode("utf-8")
    st.download_button("📥 Download Cloud Database Records (CSV)", csv_data, "supabase_sensor_records.csv", "text/csv")

if __name__ == "__main__":
    main()
--------------------------------------------------------------------------------
