use data from database

1 files changed, 50 insertions, 62 deletions

diff --git a/server/api/market.py b/server/api/market.py
index a1f25ec..1d17857 100644
--- a/server/api/market.py
+++ b/server/api/market.py
@@ -1,80 +1,68 @@
 from fastapi import APIRouter, Query, HTTPException
-from models.market import MarketData
-from gridstatus import ISONE, CAISO, Ercot, MISO, NYISO, PJM
+from sqlalchemy.orm import Session
+from typing import List, Dict
 from datetime import datetime, timedelta
-from typing import List, Dict, Type
+from models.market import MarketData, MarketDataDB
+from db import SessionLocal
 
 router = APIRouter()
 
-MARKET_CLASSES: Dict[str, Type] = {
-    "ISONE": ISONE,
-    # "CAISO": CAISO,
-    # "ERCOT": Ercot,
-    "MISO": MISO,
-    "NYISO": NYISO,
-    # "PJM": PJM,
+# Only allow these markets
+SUPPORTED_MARKETS: Dict[str, str] = {
+    "ISONE": "ISONE",
+    "MISO": "MISO",
+    "NYISO": "NYISO",
 }
 
-# In-memory cache
-_cached_day_ahead: Dict[str, List[MarketData]] = {}
-_cached_day_ahead_timestamp: Dict[str, datetime] = {}
-_cached_real_time: Dict[str, List[MarketData]] = {}
-_cached_real_time_timestamp: Dict[str, datetime] = {}
-
-def get_iso_instance(market: str):
+def check_market_supported(market: str):
     market = market.upper()
-    if market not in MARKET_CLASSES:
-        raise HTTPException(status_code=400, detail=f"Unsupported market '{market}'. Supported: {list(MARKET_CLASSES.keys())}")
-    return MARKET_CLASSES[market]()
+    if market not in SUPPORTED_MARKETS:
+        raise HTTPException(status_code=400, detail=f"Unsupported market '{market}'. Supported: {list(SUPPORTED_MARKETS.keys())}")
+    return market
 
 @router.get("/day-ahead", response_model=List[MarketData])
 def get_day_ahead_data(market: str = Query("ISONE")):
-    now = datetime.utcnow()
-    market = market.upper()
-
-    if (market not in _cached_day_ahead_timestamp or
-            now - _cached_day_ahead_timestamp[market] > timedelta(hours=1)):
-
-        iso = get_iso_instance(market)
-        df = iso.get_lmp(date=datetime.now().date(), market="DAY_AHEAD_HOURLY", locations="ALL")
-        grouped = df.groupby("Interval Start")[["LMP", "Energy", "Congestion", "Loss"]].mean().reset_index()
+    db: Session = SessionLocal()
+    market = check_market_supported(market)
 
-        _cached_day_ahead[market] = [
-            MarketData(
-                timestamp=row["Interval Start"],
-                lmp=row["LMP"],
-                energy=row["Energy"],
-                congestion=row["Congestion"],
-                loss=row["Loss"],
-            )
-            for _, row in grouped.iterrows()
-        ]
-        _cached_day_ahead_timestamp[market] = now
+    records = db.query(MarketDataDB)\
+        .filter(MarketDataDB.market == market, MarketDataDB.type == "DAYAHEAD")\
+        .order_by(MarketDataDB.timestamp)\
+        .all()
 
-    return _cached_day_ahead[market]
+    db.close()
+    return [
+        MarketData(
+            timestamp=r.timestamp,
+            lmp=r.lmp,
+            energy=r.energy,
+            congestion=r.congestion,
+            loss=r.loss,
+        ) for r in records
+    ]
 
 @router.get("/real-time", response_model=List[MarketData])
 def get_real_time_data(market: str = Query("ISONE")):
-    now = datetime.utcnow()
-    market = market.upper()
-
-    if (market not in _cached_real_time_timestamp or
-            now - _cached_real_time_timestamp[market] > timedelta(minutes=5)):
-
-        iso = get_iso_instance(market)
-        df = iso.get_lmp(date="today", market="REAL_TIME_5_MIN", locations="ALL")
-        grouped = df.groupby("Interval Start")[["LMP", "Energy", "Congestion", "Loss"]].mean().reset_index()
+    db: Session = SessionLocal()
+    market = check_market_supported(market)
 
-        _cached_real_time[market] = [
-            MarketData(
-                timestamp=row["Interval Start"],
-                lmp=row["LMP"],
-                energy=row["Energy"],
-                congestion=row["Congestion"],
-                loss=row["Loss"],
-            )
-            for _, row in grouped.iterrows()
-        ]
-        _cached_real_time_timestamp[market] = now
+    start_time = datetime.utcnow() - timedelta(days=1)
+    records = db.query(MarketDataDB)\
+        .filter(
+            MarketDataDB.market == market,
+            MarketDataDB.type == "REALTIME",
+            MarketDataDB.timestamp >= start_time
+        )\
+        .order_by(MarketDataDB.timestamp)\
+        .all()
 
-    return _cached_real_time[market]
+    db.close()
+    return [
+        MarketData(
+            timestamp=r.timestamp,
+            lmp=r.lmp,
+            energy=r.energy,
+            congestion=r.congestion,
+            loss=r.loss,
+        ) for r in records
+    ]