app2.py

import streamlit as st
import pandas as pd
from sqlalchemy import create_engine, text
from sshtunnel import SSHTunnelForwarder
import os
import tempfile

# --- Config ---
st.set_page_config(page_title="GPS Map Viewer", layout="wide")
st.title("🛰️ GPS Track Viewer")

# --- Load secrets ---
SSH_KEY = st.secrets["SSH_KEY"]
SSH_USER = st.secrets["SSH_USER"]
SSH_HOST = st.secrets["DB_HOST"]
REMOTE_BIND_HOST = st.secrets["REMOTE_BIND_HOST"]
LOCAL_BIND_HOST = st.secrets["LOCAL_BIND_HOST"]
MYSQL_USER = st.secrets["MYSQL_USER"]
MYSQL_PASSWORD = st.secrets["MYSQL_PASSWORD"]
MYSQL_DB = st.secrets["DB_NAME"]
MYSQL_PORT = int(st.secrets["MYSQL_PORT"])
GOOGLE_MAPS_API_KEY = st.secrets["GOOGLE_MAPS_API_KEY"]

# --- SSH Key File ---
with tempfile.NamedTemporaryFile(delete=False, suffix=".pem") as temp_file:
    temp_file.write(SSH_KEY.encode())
    temp_file_path = temp_file.name
os.chmod(temp_file_path, 0o600)

# --- SSH Tunnel ---
@st.cache_resource
def start_ssh_tunnel():
    tunnel = SSHTunnelForwarder(
        (SSH_HOST, 22),
        ssh_username=SSH_USER,
        ssh_pkey=temp_file_path,
        remote_bind_address=(REMOTE_BIND_HOST, MYSQL_PORT),
        local_bind_address=(LOCAL_BIND_HOST, MYSQL_PORT)
    )
    tunnel.start()
    return tunnel

server = start_ssh_tunnel()

# --- SQLAlchemy Engine ---
MYSQL_URL = f"mysql+pymysql://{MYSQL_USER}:{MYSQL_PASSWORD}@{LOCAL_BIND_HOST}:{MYSQL_PORT}/{MYSQL_DB}"
engine = create_engine(MYSQL_URL)

# --- Load GPS Data ---
@st.cache_data(ttl=300)
def load_gps_data():
    query = text("SELECT latitude, longitude FROM gps_points ORDER BY timestamp")
    df = pd.read_sql(query, engine)
    return df

gps_data = load_gps_data()

# --- Display Map ---
if not gps_data.empty:
    # Convert to JS array
    points_js = gps_data.to_dict(orient="records")
    st.components.v1.html(f"""
    <div id="map" style="height: 90vh; width: 100%;"></div>
    <script src="https://maps.googleapis.com/maps/api/js?key={GOOGLE_MAPS_API_KEY}"></script>
    <script>
        const points = {points_js};
        function initMap() {{
            const map = new google.maps.Map(document.getElementById("map"), {{
                zoom: 14,
                center: {{ lat: points[0].latitude, lng: points[0].longitude }},
                mapTypeId: 'roadmap'
            }});
            const path = points.map(p => new google.maps.LatLng(p.latitude, p.longitude));
            new google.maps.Polyline({{
                path,
                geodesic: true,
                strokeColor: "#FF0000",
                strokeOpacity: 1.0,
                strokeWeight: 2,
                map: map
            }});
        }}
        window.onload = initMap;
    </script>
    """, height=700)
else:
    st.info("No GPS data available.")