Merge branch 'master' of https://github.com/Simplesmente-O-Grupo/iot-monitoring

6 files changed, 104 insertions, 4 deletions

diff --git a/mqtt_client/crud.py b/mqtt_client/crud.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/mqtt_client/crud.py
diff --git a/mqtt_client/database.py b/mqtt_client/database.py
new file mode 100644
index 0000000..c0691a1
--- /dev/null
+++ b/mqtt_client/database.py
@@ -0,0 +1,10 @@
+from sqlalchemy import create_engine
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import sessionmaker
+import os
+
+DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://root:public@postgres:5432/control_station")
+
+engine = create_engine(DATABASE_URL)
+SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
+Base = declarative_base()
diff --git a/mqtt_client/main.py b/mqtt_client/main.py
index dad09b5..109ca49 100644
--- a/mqtt_client/main.py
+++ b/mqtt_client/main.py
@@ -2,8 +2,15 @@ import paho.mqtt.client as mqtt
 from paho.mqtt.enums import MQTTProtocolVersion
 import json
 from time import sleep
+from database import engine
+from models import Base
 import os
 
+print("Creating ORM SQL Tables..")
+Base.metadata.create_all(bind=engine)
+print("Tables created successfully.")
+
+
 def on_connect(client, userdata, flags, reason_code, properties):
     print(f"Conectado: {reason_code}")
     # Me inscrevo em todos os tópicos sobre clima
@@ -13,9 +20,9 @@ def on_connect(client, userdata, flags, reason_code, properties):
 def on_message(client, userdata, msg):
     payload = json.loads(msg.payload)
     print(msg.topic)
-    print(f"Value: {payload["value"]}")
-    print(f"Unit: {payload["unit"]}")
-    print(f"Timestamp: {payload["timestamp"]}")
+    print(f"Value: {payload['value']}")
+    print(f"Unit: {payload['unit']}")
+    print(f"Timestamp: {payload['timestamp']}")
 
 try:
     user_name = os.environ["MQTT_CLIENT_USER"]
diff --git a/mqtt_client/models.py b/mqtt_client/models.py
new file mode 100644
index 0000000..d6bd2ba
--- /dev/null
+++ b/mqtt_client/models.py
@@ -0,0 +1,81 @@
+from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Boolean
+from sqlalchemy.orm import relationship
+from sqlalchemy.sql import func
+from database import Base
+
+# ----------------------------
+# Location
+# ----------------------------
+class Location(Base):
+    __tablename__ = "locations"
+
+    id = Column(Integer, primary_key=True, index=True)
+    street = Column(String(200), nullable=True, index=True)
+    avenue = Column(String(200), nullable=True, index=True)
+    zip_code = Column(String(20), nullable=True, index=True)
+
+    # Relationship: One location to many stations
+    stations = relationship("Station", back_populates="location")
+
+
+# ----------------------------
+# Station
+# ----------------------------
+class Station(Base):
+    __tablename__ = "stations"
+    
+    id = Column(Integer, primary_key=True, index=True)
+    name = Column(String(100), nullable=False, index=True)
+    location_id = Column(Integer, ForeignKey("locations.id"), nullable=True, index=True)
+    installation_date = Column(DateTime, server_default=func.now())
+    is_active = Column(Boolean, default=True)
+
+    # Relationships
+    location = relationship("Location", back_populates="stations")
+    sensor_devices = relationship("SensorDevice", back_populates="station")
+
+
+# ----------------------------
+# SensorDevice
+# ----------------------------
+class SensorDevice(Base):
+    __tablename__ = "sensor_devices"
+    
+    id = Column(Integer, primary_key=True, index=True)
+    name = Column(String(100), nullable=True, index=True)
+    installation_date = Column(DateTime, server_default=func.now())
+    station_id = Column(Integer, ForeignKey("stations.id"), nullable=True, index=True)
+    is_active = Column(Boolean, default=True)
+
+    # Relationships
+    station = relationship("Station", back_populates="sensor_devices")
+    readings = relationship("Reading", back_populates="sensor_device")
+
+
+# ----------------------------
+# Measure
+# ----------------------------
+class Measure(Base):
+    __tablename__ = "measures"
+    
+    id = Column(Integer, primary_key=True, index=True)
+    name = Column(String(50), nullable=True, index=True)
+
+    # Relationships
+    readings = relationship("Reading", back_populates="measure")
+
+
+# ----------------------------
+# Reading
+# ----------------------------
+class Reading(Base):
+    __tablename__ = "readings"
+    
+    id = Column(Integer, primary_key=True, index=True)
+    sensor_device_id = Column(Integer, ForeignKey("sensor_devices.id"), nullable=True, index=True)
+    measure_id = Column(Integer, ForeignKey("measures.id"), nullable=True, index=True)
+    value = Column(Float, nullable=True)
+
+    # Relationships
+    sensor_device = relationship("SensorDevice", back_populates="readings")
+    measure = relationship("Measure", back_populates="readings")
diff --git a/mqtt_client/requirements.txt b/mqtt_client/requirements.txt
index 96a2716..1521b2d 100644
--- a/mqtt_client/requirements.txt
+++ b/mqtt_client/requirements.txt
@@ -1 +1,3 @@
-paho-mqtt==2.1.0
+sqlalchemy
+psycopg2-binary
+paho-mqtt
diff --git a/mqtt_client/schemas.py b/mqtt_client/schemas.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/mqtt_client/schemas.py