================================================================================
  Question CQ8: CoAP Ingestion to Cloud Database (Supabase) & Dashboard [30 Marks]
================================================================================

FULL QUESTION:
CAE 2 IoT Practical Exam - Combined Question:
CQ8. CoAP Environmental Ingestion to Supabase Cloud Database and Streamlit Web Dashboard [30 Marks]
Develop an end-to-end IoT cloud archival and visualization architecture using the CoAP protocol, Supabase cloud database, and Streamlit.
Data Source / Parameters: Ambient Temperature (°C), Relative Humidity (%), Atmospheric Pressure (hPa), and Wind Speed (m/s).
Cloud Database: Supabase PostgreSQL via PostgREST REST API.
The system should:
- Develop an asynchronous CoAP Server listening on UDP port 5683 exposing the resource /weather/kochi. [6 Marks]
- Retrieve live environmental telemetry via the CoAP Server from an external REST API (Open-Meteo) or simulated sensor node. [6 Marks]
- Develop a Python CoAP Client / Cloud Bridge that periodically issues CoAP GET requests to retrieve live readings and forwards them to a Supabase cloud database table via HTTP POST requests with proper authentication headers. [6 Marks]
- Configure database schema and implement fallback logging to persist telemetry records continuously. [4 Marks]
- Develop a Streamlit web dashboard querying the Supabase REST API via HTTP GET requests to display real-time gauges, historical time-series graphs, and a filterable log table. [5 Marks]
- Include a manual CoAP query button and CSV data download capability. [3 Marks]
Flow: Open-Meteo / Sensor API -> CoAP Server (UDP 5683) -> CoAP Client & Bridge -> Supabase REST API -> Streamlit Dashboard.

SHORT QUESTION:
CoAP Environmental Server & Client to Supabase Cloud Persistence Bridge with Streamlit Dashboard [30 Marks].

================================================================================
README & SETUP INSTRUCTIONS:
================================================================================
# Question CQ8: CoAP Ingestion to Supabase Cloud Database & Web Dashboard

## Problem Statement [30 Marks]
Collect environmental parameters via CoAP server, transfer readings to a CoAP client bridge, persist records to a Supabase cloud table, and display analytics on a Streamlit dashboard.

- **Protocol**: CoAP (`aiocoap`, UDP port 5683)
- **Endpoint**: `coap://127.0.0.1:5683/weather/kochi`
- **Cloud Database**: Supabase REST API (`https://<project-id>.supabase.co/rest/v1/<table_name>`)
- **Dashboard**: Streamlit (`pandas`, `requests`, `streamlit`)

---

## Architectural Flow
```text
Open-Meteo API ──> CoAP Server (:5683) ──> CoAP Client/Bridge ──(HTTP POST)──> Supabase Cloud DB ──(HTTP GET)──> Streamlit UI
```

---

## Files in this Package
- `server.py`: Asynchronous CoAP Server listening on UDP port 5683 (`/weather/kochi`), serving live meteorological data.
- `client_bridge.py`: Standalone CLI CoAP Client that polls the CoAP server and writes records to Supabase via HTTP POST.
- `app.py`: Streamlit web dashboard displaying live CoAP metrics, historical time-series trends from Supabase, and CSV download.

---

## How to Run

### Step 1: Install Dependencies
```bash
pip install aiocoap requests streamlit pandas matplotlib
```

### Step 2: Start the CoAP Server
Open Terminal 1:
```bash
python server.py
```

### Step 3: Run the CoAP-to-Supabase Bridge
Open Terminal 2:
```bash
python client_bridge.py
```

### Step 4: Launch the Streamlit Web Dashboard
Open Terminal 3:
```bash
streamlit run app.py
```

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

1. server.py
--------------------------------------------------------------------------------
import asyncio
import json
import random
import requests
import aiocoap
import aiocoap.resource as resource

OPEN_METEO_URL = "https://api.open-meteo.com/v1/forecast?latitude=9.93&longitude=76.27&current=temperature_2m,relative_humidity_2m,surface_pressure,wind_speed_10m"

class WeatherResource(resource.Resource):

    def __init__(self):
        super().__init__()

    async def render_get(self, request):
        telemetry = {}

        try:
            resp = requests.get(OPEN_METEO_URL, timeout=3)
            if resp.status_code == 200:
                cur = resp.json().get("current", {})
                telemetry = {
                    "city": "Kochi",
                    "temperature": cur.get("temperature_2m", 28.5),
                    "humidity": cur.get("relative_humidity_2m", 78.0),
                    "pressure": cur.get("surface_pressure", 1011.2),
                    "wind_speed": cur.get("wind_speed_10m", 3.2),
                    "source": "Open-Meteo Live API"
                }
        except Exception:
            pass

        if not telemetry:
            telemetry = {
                "city": "Kochi",
                "temperature": round(random.uniform(26.0, 33.5), 1),
                "humidity": round(random.uniform(65.0, 88.0), 1),
                "pressure": round(random.uniform(1008.0, 1016.0), 1),
                "wind_speed": round(random.uniform(1.5, 6.0), 1),
                "source": "Simulated Sensor Node"
            }

        payload = json.dumps(telemetry, indent=2)
        print(f"\n[CoAP Server] Served GET request to CoAP client: {telemetry}")

        return aiocoap.Message(
            payload=payload.encode("utf-8"),
            content_format=50
        )

async def main():
    root = resource.Site()
    root.add_resource(["weather", "kochi"], WeatherResource())

    server_context = await aiocoap.Context.create_server_context(root, bind=("127.0.0.1", 5683))
    print("=" * 60)
    print("  CQ8: CoAP Weather Server Active on Port 5683")
    print("=" * 60)
    print("Endpoint: coap://127.0.0.1:5683/weather/kochi")
    print("Waiting for CoAP Client requests...\n")

    await asyncio.get_running_loop().create_future()

if __name__ == "__main__":
    try:
        asyncio.run(main())
    except KeyboardInterrupt:
        print("\n[CoAP Server] Stopping...")
--------------------------------------------------------------------------------

2. client_bridge.py
--------------------------------------------------------------------------------
import os
import json
import time
import asyncio
import requests
import aiocoap

COAP_URI = "coap://127.0.0.1:5683/weather/kochi"

SUPABASE_URL = os.environ.get("SUPABASE_URL", "https://xyzcompany.supabase.co")
SUPABASE_KEY = os.environ.get("SUPABASE_KEY", "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.dummy_anon_key")
TABLE_NAME = "coap_weather_records"
REST_ENDPOINT = f"{SUPABASE_URL}/rest/v1/{TABLE_NAME}"
LOCAL_LOG = "supabase_coap_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("  [Supabase] Record persisted successfully to cloud database!")
            return True
        else:
            print(f"  [Supabase Note] Status {resp.status_code} (persisting to local buffer)")
    except Exception as e:
        print(f"  [Supabase Note] 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 locally to {LOCAL_LOG}")
    return False

async def fetch_coap_data():
    context = await aiocoap.Context.create_client_context()
    msg = aiocoap.Message(code=aiocoap.GET, uri=COAP_URI)
    try:
        resp = await context.request(msg).response
        return json.loads(resp.payload.decode("utf-8"))
    except Exception as e:
        print(f"[CoAP Client Error] {e}")
        return None

def main():
    print("=" * 60)
    print("  CQ8: CoAP Client to Supabase Cloud Bridge")
    print("=" * 60)
    print(f"Querying CoAP Server: {COAP_URI}")
    print(f"Forwarding to Supabase: {REST_ENDPOINT}\n")

    counter = 0
    try:
        while True:
            counter += 1
            data = asyncio.run(fetch_coap_data())
            if data:
                data["timestamp"] = time.strftime("%Y-%m-%d %H:%M:%S")
                print(f"[CoAP Fetch #{counter}] Temp={data.get('temperature')}°C | Hum={data.get('humidity')}% | Pres={data.get('pressure')}hPa")
                post_to_supabase(data)
            else:
                print(f"[CoAP Fetch #{counter}] Server did not respond. Check if server.py is running.")

            time.sleep(4)

    except KeyboardInterrupt:
        print("\n[Bridge] Stopping...")

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

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

st.set_page_config(page_title="CQ8: CoAP Supabase Cloud Dashboard", layout="wide", page_icon="🌐")

COAP_URI = "coap://127.0.0.1:5683/weather/kochi"
SUPABASE_URL = os.environ.get("SUPABASE_URL", "https://xyzcompany.supabase.co")
SUPABASE_KEY = os.environ.get("SUPABASE_KEY", "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.dummy_anon_key")
TABLE_NAME = "coap_weather_records"
REST_ENDPOINT = f"{SUPABASE_URL}/rest/v1/{TABLE_NAME}?select=*&order=id.desc&limit=50"
LOCAL_LOG = "supabase_coap_buffer.json"

async def fetch_coap_now():
    context = await aiocoap.Context.create_client_context()
    msg = aiocoap.Message(code=aiocoap.GET, uri=COAP_URI)
    resp = await context.request(msg).response
    return json.loads(resp.payload.decode("utf-8"))

def fetch_stored_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), "Live Supabase Cloud Database"
    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), "Local Buffer Database (Offline Mode)"
        except Exception:
            pass

    seed = [
        {"city": "Kochi", "temperature": 28.5, "humidity": 76.0, "pressure": 1012.0, "wind_speed": 3.4, "timestamp": "10:00:00"},
        {"city": "Kochi", "temperature": 29.1, "humidity": 74.0, "pressure": 1011.5, "wind_speed": 4.1, "timestamp": "10:01:00"},
        {"city": "Kochi", "temperature": 29.8, "humidity": 71.0, "pressure": 1011.0, "wind_speed": 4.5, "timestamp": "10:02:00"},
    ]
    return pd.DataFrame(seed), "Simulated Environmental Telemetry Buffer"

def main():
    st.title("🌐 CoAP Environmental Telemetry to Supabase Cloud Dashboard")
    st.markdown("**CAE 2 Combined Question 8**: CoAP Protocol + Supabase Cloud REST Database (`aiocoap` + `requests` + `Streamlit`)")
    st.markdown("---")

    df, status_label = fetch_stored_records()

    with st.sidebar:
        st.header("☁️ Cloud Database Info")
        st.write(f"**Backend:** Supabase PostgreSQL")
        st.write(f"**CoAP Endpoint:** `{COAP_URI}`")
        st.write(f"**Status:** `{status_label}`")
        st.markdown("---")
        if st.button("🔄 Refresh Data", type="primary", use_container_width=True):
            st.rerun()

    col_act, col_info = st.columns([1, 3])
    with col_act:
        live_clicked = st.button("📡 Query CoAP Server Now", use_container_width=True)
    with col_info:
        st.info("Triggers a live CoAP GET transaction to UDP port 5683 (/weather/kochi).")

    if "live_coap" not in st.session_state:
        st.session_state.live_coap = {"temperature": 28.5, "humidity": 76.0, "pressure": 1012.0, "wind_speed": 3.5}

    if live_clicked:
        try:
            reading = asyncio.run(fetch_coap_now())
            st.session_state.live_coap = reading
            st.success("Successfully fetched instant reading via CoAP!")
        except Exception as e:
            st.error(f"CoAP Error: {e}. Check if server.py is running.")

    live = st.session_state.live_coap

    st.subheader("1. Instantaneous Telemetry (via CoAP Server)")
    m1, m2, m3, m4 = st.columns(4)
    m1.metric("Temperature", f"{live.get('temperature', 28.5)} °C")
    m2.metric("Relative Humidity", f"{live.get('humidity', 76.0)} %")
    m3.metric("Surface Pressure", f"{live.get('pressure', 1012.0)} hPa")
    m4.metric("Wind Speed", f"{live.get('wind_speed', 3.5)} m/s")

    st.markdown("---")

    st.subheader("2. Historical Telemetry Archived in Supabase")
    col_g1, col_g2 = st.columns(2)
    with col_g1:
        st.write("**Temperature Trend (°C)**")
        st.line_chart(df[["temperature"]])
    with col_g2:
        st.write("**Humidity (%) & Pressure (hPa) Trend**")
        st.line_chart(df[["humidity", "pressure"]])

    st.markdown("---")

    st.subheader("3. Supabase Cloud Database Table")
    st.dataframe(df, use_container_width=True)

    csv_bytes = df.to_csv(index=False).encode("utf-8")
    st.download_button("📥 Export Cloud Records to CSV", csv_bytes, "supabase_coap_weather.csv", "text/csv")

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