# PROMPT: IoT Device and Power Monitoring System Web Application

Create a Flask-based web application for an IoT-based Utility and Power Monitoring system. The application handles real-time data ingestion via MQTT, displays comprehensive monitoring dashboards, manages user accounts and device assets, generates downloadable reports, and triggers email alerts based on predefined thresholds.

---

## 1. Technology Stack & Dependencies

- **Backend**: Python 3.x, Flask (Blueprint architecture)
- **Database**: MySQL / MariaDB (connected using PyMySQL with DictCursor)
- **Real-Time Data Ingestion**: MQTT client using `paho-mqtt` running on background daemon threads
- **Data Export & Processing**: `pandas`, `xlsxwriter`, `tempfile`
- **Email Services**: `Flask-Mail` (SMTP client)
- **Security & Session**: Flask session handling (with client-side cache prevention) & JWT integration via `Flask-JWT-Extended`
- **Frontend**: HTML5, Vanilla CSS, Tailwind CSS v4 Play Browser Compiler (exclusively utilized for a unified premium dark-theme portal across all dashboards, forms, lists, settings, and reports), FontAwesome, and interactive charting libraries (Chart.js for visualization).


---

## 2. Database Schema Requirements

The application requires a MySQL database named `rumahsakit` with the following table structures:

### A. `accounts`
Stores system users and their authentication roles.
- `id` (INT, Primary Key, Auto Increment)
- `username` (VARCHAR)
- `password` (VARCHAR, SHA-256 hashed string)
- `email` (VARCHAR)
- `role` (VARCHAR, e.g., 'admin', 'user')
- `createdDate` (TIMESTAMP, Default CURRENT_TIMESTAMP)

### B. `devices`
Stores registered IoT devices that transmit telemetry data.
- `id` (INT, Primary Key, Auto Increment)
- `mac_address` (VARCHAR, Unique)
- `mcu_type` (VARCHAR)
- `location` (VARCHAR)
- `createdDate` (TIMESTAMP, Default CURRENT_TIMESTAMP)

### C. `utils`
Telemetry data capturing operational cycles of utility machines.
- `id` (INT, Primary Key, Auto Increment)
- `mac_address` (VARCHAR)
- `start` (BIGINT / Epoch timestamp)
- `stop` (BIGINT / Epoch timestamp)
- `cycle_count` (INT)
- `total_run` (BIGINT / Duration in seconds)
- `timestamp` (TIMESTAMP, Default CURRENT_TIMESTAMP)

### D. `power_monitoring`
Telemetry data capturing electric power metrics.
- `id` (INT, Primary Key, Auto Increment)
- `uid` (VARCHAR / MAC Address of the power meter)
- `rtc` (DATETIME / Device clock time)
- `voltage` (FLOAT)
- `current` (FLOAT)
- `power` (FLOAT)
- `frequency` (FLOAT)
- `pf` (FLOAT / Power Factor)
- `energy` (FLOAT / kWh)
- `co2` (FLOAT)
- `energy_history` (TEXT / JSON string containing array of historical readings)
- `relay_state` (VARCHAR / ON or OFF state)
- `timestamp` (TIMESTAMP, Default CURRENT_TIMESTAMP)

### E. `cycle_time`
Aggregated cycle times derived from power meter telemetry data.
- `id` (INT, Primary Key, Auto Increment)
- `uid` (VARCHAR)
- `start` (DATETIME)
- `stop` (DATETIME)
- `cycle_count` (INT)
- `duration` (VARCHAR / 'HH:MM:SS')
- `timestamp` (TIMESTAMP, Default CURRENT_TIMESTAMP)

### F. `settings`
Global thresholds and recipient details for alerting.
- `id` (INT, Primary Key, Auto Increment, exactly 1 row with `id = 1`)
- `temp_max_treshold` (FLOAT)
- `humid_max_treshold` (FLOAT)
- `utils_min_duration` (FLOAT)
- `mail_sender` (VARCHAR)
- `temphumid_mailreceiver` (VARCHAR)
- `utils_mailreceiver` (VARCHAR)
- `cc_mailreceiver` (VARCHAR)

### G. `utils_notification_log`
Logs sent email notifications to prevent alert storms.
- `id` (INT, Primary Key, Auto Increment)
- `mac_address` (VARCHAR)
- `last_notified_multiple` (INT)
- `createdDate` (TIMESTAMP, Default CURRENT_TIMESTAMP)

### H. `temphumid`
Environmental telemetry log (for tracking ambient server/room conditions).
- `id` (INT, Primary Key, Auto Increment)
- `mac_address` (VARCHAR)
- `temperature` (FLOAT)
- `humidity` (FLOAT)
- `timestamp` (TIMESTAMP, Default CURRENT_TIMESTAMP)

---

## 3. Core System Requirements & Architecture

### A. Main Application (`app.py`)
- Initializes the Flask application.
- Configures centralized MySQL connections and initializes Mail settings.
- Registers independent blueprints for all modules.
- Employs global middleware (`@app.before_request` and `@app.after_request`) to:
  1. Force user authentication redirects for non-static and non-login endpoints.
  2. Implement strict Cache-Control headers (`no-cache, no-store, must-revalidate, max-age=0`) to prevent visual browser history backtracking after logout.
- Launches background MQTT client threads upon application startup.

### B. MQTT Communication Threads (`config/mqtt_config.py`)
- Standardizes connections to a broker (e.g., `broker.emqx.io:1883`) using daemon-based `threading.Thread` and `paho.mqtt.client` loop threads.
- Implements auto-reconnection logic on disconnect.
- Listens to two main topics:
  1. `silaware/utils` for general device status logs.
  2. `silaware/power` for detailed power grid parameters.

---

## 4. Blueprint Modules & Logic Requirements

The system must be split into the following functional blueprints:

### 1. Authentication & Accounts (`blueprints/login.py`, `auth.py`, `accounts.py`, `logout.py`)
- **Web Session Authentication**: Checks username and password against the `accounts` database.
  - *Security note*: Consistently utilize strong SHA-256 password hashing.
- **REST API Authentication**: `/login` endpoint generating JSON Web Tokens (JWT) via `Flask-JWT-Extended` matching database credentials.
- **JWT Helper**: `/get_jwt` endpoint that retrieves a JWT based on the active session's identifier and role.
- **Account Management (CRUD)**:
  - List registered accounts with AJAX search (by username/email) and server-side pagination.
  - Create new user accounts (validates username alphanumerics, email formats, and avoids duplicates).
  - Edit account credentials/role.
  - Delete accounts.
- **Logout**: Clears active session and performs a redirect to login while modifying cache headers.

### 2. Device Asset Management (`blueprints/devices.py`)
- **Device CRUD**:
  - List devices with pagination and search functionality (by MAC address, MCU type, or location).
  - Register new devices (verifies MAC address uniqueness).
  - Edit location, MCU type, and MAC address details.
  - Delete devices.
- **JSON endpoints**: `/login/devices/json` to query raw device lists.

### 3. Utility Telemetry & MQTT Handler (`blueprints/mqtt_device.py`)
- Subscribes to `silaware/utils` and expects a JSON payload containing:
  `{"id": "mac_address", "start": epoch, "stop": epoch, "cycle_count": integer, "total_run": seconds}`
- Checks for duplicate entries and inserts new operational entries into the `utils` table.
- Triggers a check routine (`check_utils_and_notify`) to check if the accumulated running time exceeds the service duration threshold, sending out alerts as necessary.

### 4. Power Telemetry & Cycle Handler (`blueprints/power_monitor.py`)
- Subscribes to `silaware/power` and expects a JSON payload containing:
  `{"uid": "mac_address", "rtc": "DD/MM/YYYY HH:MM:SS", "relay_state": "ON/OFF", "meter_data": {"voltage": [...], "current": [...], "power": [...], "frequency": [...], "pf": [...], "energy": [...], "co2": [...]}, "energy_history": [...]}`
- Parses incoming array structures and inserts them into `power_monitoring`.
- **Automatic Cycle Detection (`update_cycle_time`)**:
  - Automatically manages `cycle_time` for each device.
  - If a new log is received within **20 seconds** of the last cycle's `stop` time, it updates the current cycle's `stop` time, recalculates the duration (`HH:MM:SS`), and saves it.
  - If the time gap exceeds **20 seconds**, it treats it as a new run and inserts a new row incrementing the cycle count.
- **APIs and Charts**:
  - `/power_monitoring/data`: Provides the last 20 readings for voltage, current, power, frequency, energy, and CO2.
  - `/power_monitoring/history`: Translates the JSON array of historical readings to day-of-week labels (e.g., Mon-Sun) for chart plotting.
  - `/power_monitoring/weekly_duration`: Summarizes cycle durations (in hours) over the past 5 weeks.
  - `/power_monitoring/forecast_energy`: Computes current month energy projections based on active run days and forecasts monthly costs using a fixed rate (e.g., IDR 1444.70 / kWh).

### 5. Consolidated Dashboard (`blueprints/summary_power.py`)
- Renders a master dashboard panel summarizing all monitored power lines.
- `/login/summary/all_summary`: Fetches the latest stats for all active devices:
  - Current instant power (W).
  - Total cumulative monthly energy (kWh) and associated electricity bills.
  - Carbon dioxide footprint (CO2) for the selected month/year.
  - Overall monthly forecast energy usage and cost predictions.
  - Weekly machine run durations and daily energy profiles over the last 7 days.

### 6. Utility Dashboard (`blueprints/utilDashboard.py`)
- Performs analytical database queries (YTD, YOY, monthly, weekly, today, yesterday) to compute machine utility times.
- Returns comprehensive aggregated runtimes in standard time format (`HH:MM:SS`) grouped by each registered device.

### 7. Reporting & Data Exports (`blueprints/report.py`, `report_excel.py`)
- **Report Filtering**: Filters telemetry records from the `utils` table by specific MAC address and date ranges.
- **Time Conversion**: Converts UNIX epoch timestamps stored in the database to UTC-based readable dates (`YYYY-MM-DD HH:MM:SS`).
- **Excel Export**: Exports the exact filtered report view into a clean Excel spreadsheet (`.xlsx`) using pandas and xlsxwriter via a temporary file system.

### 8. Settings & Email Alerts (`blueprints/settings.py`, `email_notification.py`)
- Renders an edit form to update system thresholds:
  - Temperature max threshold.
  - Humidity max threshold.
  - Device running hours maintenance threshold.
  - Recipients and Sender emails.
- **Alert Routines**:
  - `check_temperature_and_notify`: Compares ambient values from `temphumid` against settings and sends alerts if exceeded.
  - `check_utils_and_notify`: Evaluates `total_run` duration increments. Sends a maintenance warning email every time a device completes an integer multiple of the configured limit (e.g. every 100 hours), logging notifications in `utils_notification_log` to avoid redundant alerts.

---

## 5. cPanel Deployment & Subdomain Setup (`monitor.silvestersila.my.id`)

For successful deployment on a cPanel shared hosting or VPS instance under the subdomain `monitor.silvestersila.my.id`, the following configurations are required:

### A. WSGI Setup (`passenger_wsgi.py`)
- cPanel uses Phusion Passenger to serve Python applications. A `passenger_wsgi.py` entry point file must exist in the root directory.
- The entry point must import the Flask application instance and expose it as a global callable named `application`:
  ```python
  import sys
  import os
  sys.path.insert(0, os.path.dirname(__file__))
  from app import app as application
  ```

### B. Environment Configuration
- The database connection should support environment variables to allow seamless migration from local development (root, no password) to cPanel production credentials.
- The following environment variables should be defined in cPanel's **"Setup Python App"** configuration interface:
  - `DB_HOST`: Database host (typically `localhost`)
  - `DB_USER`: cPanel MySQL User (e.g., `silvestersila_dbuser`)
  - `DB_PASSWORD`: cPanel MySQL Password
  - `DB_NAME`: cPanel MySQL Database Name (e.g., `silvestersila_rumahsakit`)
  - `DB_PORT`: Database port (default `3306`)

### C. Persistent MQTT Background Listeners (`run_mqtt.py`)
- **Warning**: Running MQTT loops directly inside the Flask WSGI process in cPanel can cause issues:
  1. If there is no web traffic, Passenger might recycle the worker processes and spin them down to 0, which terminates the MQTT background threads.
  2. If Passenger spins up multiple workers, they will conflict on MQTT Client ID connections.
- **Solution**: Execute the MQTT subscriber process as a standalone, persistent background script (`run_mqtt.py`) outside of Passenger.
  - Run the following command via cPanel Terminal, SSH, or a startup process supervisor:
    ```bash
    nohup python run_mqtt.py > mqtt.log 2>&1 &
    ```
  - Ensure `start_mqtt_thread()` and `start_power_thread()` inside `app.py` are conditional or bypassed when running under WSGI so web processes do not spawn competing MQTT listeners.

---

## 6. Database Storage Safety & Auto-Cleanup (4-Month Retention)

To keep database storage utilization under control and maintain optimal query performance, the application implements an automatic data retention policy:
- **Retention Period**: Telemetry and log records older than **4 months** must be automatically purged from the database.
- **Implementation Strategy**:
  - A central cleanup function (`cleanup_old_data()` in `config/db.py`) runs SQL delete statements targeting the following tables:
    ```sql
    DELETE FROM power_monitoring WHERE timestamp < DATE_SUB(NOW(), INTERVAL 4 MONTH);
    DELETE FROM cycle_time WHERE timestamp < DATE_SUB(NOW(), INTERVAL 4 MONTH);
    DELETE FROM utils WHERE timestamp < DATE_SUB(NOW(), INTERVAL 4 MONTH);
    DELETE FROM utils_notification_log WHERE createdDate < DATE_SUB(NOW(), INTERVAL 4 MONTH);
    DELETE FROM temphumid WHERE timestamp < DATE_SUB(NOW(), INTERVAL 4 MONTH);
    ```
  - **Performance Optimization (Throttling)**: To avoid query overhead, this cleanup operation is throttled using a global execution timestamp and runs at most once every **12 hours**.
  - **Triggers**: It is called automatically at the entry points of new MQTT message insertions (`insert_utils_status` and `on_message_callback`), ensuring continuous self-maintenance without requiring external system-level cron scheduling.

---

## 7. Web Application Security Requirements

To protect the application, database, and telemetry logs from intruders and malicious attacks, the system enforces the following security standards:

### A. Parameterized SQL Queries (SQL Injection Prevention)
- Direct concatenation of user-provided values in raw SQL strings is strictly prohibited.
- All database queries must bind values using parameters via DB placeholders (`%s`) in `cursor.execute()`, ensuring defense against SQL Injection attacks.

### B. Secure Password Hashing
- Implement strong, uniform password hashing across all system modules:
  - Consistently hash passwords using SHA-256 (`hashlib.sha256(password.encode()).hexdigest()`) during both user registration (`accounts.py`) and login validation (`login.py`) to prevent login failures and resist brute-force/dictionary attacks.

### C. Session & Cookie Security Controls
The application's session cookies are hardened with the following configurations:
- `SESSION_COOKIE_SECURE = True`: Forces browsers to only transmit session cookies over encrypted HTTPS channels, shielding cookies from interception (critical for subdomain SSL setups).
- `SESSION_COOKIE_HTTPONLY = True`: Blocks client-side JavaScript from accessing session cookies, mitigating risk from Cross-Site Scripting (XSS) cookie theft.
- `SESSION_COOKIE_SAMESITE = 'Lax'`: Restricts cookies from being sent along with cross-site requests, providing defense against Cross-Site Request Forgery (CSRF).
- `SECRET_KEY`: Expose session signing secrets via server environment variables (`SECRET_KEY`), falling back to a strong default locally.

### D. Security Headers Injection
All HTTP responses outputted by the application are automatically appended with security headers in a global middleware hook (`@app.after_request`):
- `X-Frame-Options: SAMEORIGIN`: Disallows framing the application pages outside its origin domain, neutralizing Clickjacking exploits.
- `X-Content-Type-Options: nosniff`: Instructs browsers not to sniff content types away from the declared MIME types, preventing malicious MIME-type execution.
- `X-XSS-Protection: 1; mode=block`: Activates built-in web browser XSS filtering mechanisms.