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

16 files changed, 181 insertions, 8 deletions

diff --git a/.env.example b/.env.example
index dee33a5..94f8f32 100644
--- a/.env.example
+++ b/.env.example
@@ -1,6 +1,6 @@
 # vim:ft=sh 
 
 # Usuário que o cliente MQTT utilizará para logar
-MQTT_CLIENT_USER="changeme"
+MQTT_CLIENT_USER="tester"
 # Senha que o cliente MQTT utilizará para logar
-MQTT_CLIENT_PASSWORD="changeme"
+MQTT_CLIENT_PASSWORD="rosebud"
diff --git a/compose.yaml b/compose.yaml
index db32d4c..549379a 100644
--- a/compose.yaml
+++ b/compose.yaml
@@ -23,12 +23,44 @@ services:
     environment:
       MQTT_CLIENT_USER: ${MQTT_CLIENT_USER}
       MQTT_CLIENT_PASSWORD: ${MQTT_CLIENT_PASSWORD}
+      DATABASE_URL: postgresql://root:public@postgres:5432/control_station
     build:
       context: ./mqtt_client/
     networks:
       - mosquitto
     depends_on:
       - mosquitto
+  postgres:
+    image: postgres:13
+    container_name: postgres
+    networks:
+      - mosquitto
+    ports:
+    - "5432:5432"
+    environment:
+      POSTGRES_PASSWORD: public
+      POSTGRES_USER: root
+      POSTGRES_DB: control_station
+    volumes:
+      #- ${PWD}/database/init_scripts:/docker-entrypoint-initdb.d
+      - db_data:/var/lib/postgresql/data
 
+  
+  pgadmin:
+    image: dpage/pgadmin4
+    container_name: pgadmin
+    environment:
+      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
+      - PGADMIN_DEFAULT_PASSWORD=admin
+    ports:
+      - "5050:80"
+    networks:
+      - mosquitto
+    depends_on:
+      - postgres
 networks:
   mosquitto:
+  database:
+
+volumes:
+  db_data:
diff --git a/frontend-arq b/frontend-arq
new file mode 100644
index 0000000..ac66a0f
--- /dev/null
+++ b/frontend-arq
@@ -0,0 +1,41 @@
+Estratégia de implementação Frontend/Backend para sistema de estação meteorológica com ESP32
+
+- Formato JSON padronizado (sugestão):
+{
+  "station_id": "ESP32_001",
+  "timestamp": 1697203200,
+  "readings": {
+    "temperature": 25.4,
+    "humidity": 65.2,
+    "pressure": 1013.25,
+    "luminosity": 450,
+    "wind_direction": 180,
+    "wind_speed": 5.2
+  }
+}
+
+
+-  VISÃO CLIENT:
+info essenciais:
+- condições em tempo real: 
+    -> Temperatura
+    -> Umidade
+    -> Pressao atmosférica
+    -> Luminosidade
+    -> Velocidade e direção do vento
+    -> ultima atualização
+
+- situação da estação
+    -> online / offline
+    -> bateria / alimentação 
+    -> qualidade sinal
+    -> defeito em algum sensor
+
+- tendencia e previsão
+    -> temperatura ta subindo ou caindo?
+    -> pressão está subindo ou caindo?
+    -> Grafico das ultimas horas 
+
+- Recordes do dia, semana e mes
+
+
diff --git a/frontend/src/components/AlertPainel.jsx b/frontend/src/components/AlertPainel.jsx
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/frontend/src/components/AlertPainel.jsx
diff --git a/frontend/src/components/ChartWidget.jsx b/frontend/src/components/ChartWidget.jsx
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/frontend/src/components/ChartWidget.jsx
diff --git a/frontend/src/components/SensorGauge.jsx b/frontend/src/components/SensorGauge.jsx
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/frontend/src/components/SensorGauge.jsx
diff --git a/frontend/src/pages/Dashboard.jsx b/frontend/src/pages/Dashboard.jsx
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/frontend/src/pages/Dashboard.jsx
diff --git a/frontend/src/pages/History.jsx b/frontend/src/pages/History.jsx
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/frontend/src/pages/History.jsx
diff --git a/frontend/src/services/api.js b/frontend/src/services/api.js
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/frontend/src/services/api.js
diff --git a/mosquitto-config/mosquitto.conf b/mosquitto-config/mosquitto.conf
index f913973..7e941f9 100644
--- a/mosquitto-config/mosquitto.conf
+++ b/mosquitto-config/mosquitto.conf
@@ -1,3 +1,3 @@
 listener 1883 0.0.0.0
-allow_anonymous false
-password_file /mosquitto/config/pass-file
+allow_anonymous true
+# password_file /mosquitto/config/pass-file
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