wetter-station/server.js

import 'dotenv/config';
import express from 'express';
import sqlite3Pkg from 'sqlite3';
import path from 'path';
import { fileURLToPath } from 'url';

const sqlite3 = sqlite3Pkg.verbose();
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const app = express();

// Configuration
const DB_FILE = process.env.DB_FILE || 'wetterdaten.db';
const HTTP_PORT = parseInt(process.env.HTTP_PORT || '5003', 10);

// View engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'pug');

// Middleware
app.use(express.json());
app.use('/static', express.static(path.join(__dirname, 'static')));

// Database handling class
class WetterDB {
    constructor(dbFile) {
        this.dbFile = dbFile;
        this.initDb();
    }

    initDb() {
        const db = new sqlite3.Database(this.dbFile);
        db.serialize(() => {
            db.run(`
                CREATE TABLE IF NOT EXISTS wetterdaten (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    dateTime INTEGER NOT NULL,
                    barometer REAL,
                    outTemp REAL,
                    outHumidity INTEGER,
                    windSpeed REAL,
                    windDir REAL,
                    windGust REAL,
                    rainRate REAL,
                    rain REAL
                )
            `);
            db.run(`
                CREATE INDEX IF NOT EXISTS idx_dateTime ON wetterdaten(dateTime)
            `);
        });
        db.close();
    }

    saveData(data) {
        return new Promise((resolve, reject) => {
            const db = new sqlite3.Database(this.dbFile);
            const sql = `
                INSERT INTO wetterdaten 
                (dateTime, barometer, outTemp, outHumidity, windSpeed, windDir, windGust, rainRate, rain)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            `;
            const params = [
                data.dateTime,
                data.barometer,
                data.outTemp,
                data.outHumidity,
                data.windSpeed,
                data.windDir,
                data.windGust,
                data.rainRate,
                data.rain
            ];

            db.run(sql, params, function(err) {
                db.close();
                if (err) {
                    console.error("Error saving data:", err);
                    reject(err);
                } else {
                    console.log(`Daten gespeichert: ${data.dateTime}`);
                    resolve(this.lastID);
                }
            });
        });
    }

    getData(hours = 24) {
        return new Promise((resolve, reject) => {
            const db = new sqlite3.Database(this.dbFile);
            
            // Calculate timestamp threshold (current time - hours) in seconds (Unix Timestamp)
            const timeThreshold = Math.floor((Date.now() - hours * 60 * 60 * 1000) / 1000);

            const sql = `
                SELECT * FROM wetterdaten 
                WHERE dateTime >= ? 
                ORDER BY dateTime ASC
            `;

            db.all(sql, [timeThreshold], (err, rows) => {
                db.close();
                if (err) reject(err);
                else resolve(rows);
            });
        });
    }

    getHourlyRain(hours = 24) {
        return new Promise((resolve, reject) => {
            const db = new sqlite3.Database(this.dbFile);
            
            // Calculate timestamp threshold (current time - hours) in seconds (Unix Timestamp)
            const timeThreshold = Math.floor((Date.now() - hours * 60 * 60 * 1000) / 1000);

            const sql = `
                SELECT 
                    strftime('%Y-%m-%d %H:00:00', datetime(dateTime, 'unixepoch', 'localtime')) as hour,
                    SUM(rainRate) as total_rain
                FROM wetterdaten 
                WHERE dateTime >= ?
                GROUP BY hour
                ORDER BY hour ASC
            `;

            db.all(sql, [timeThreshold], (err, rows) => {
                db.close();
                if (err) {
                    reject(err);
                } else {
                    const result = rows.map(row => ({
                        hour: row.hour,
                        rain: row.total_rain || 0
                    }));
                    resolve(result);
                }
            });
        });
    }
}

// Global DB instance
const db = new WetterDB(DB_FILE);

// Routes
app.get('/', (req, res) => {
    res.render('index');
});

app.post('/api/data/upload', async (req, res) => {
    try {
        const data = req.body;
        
        if (!data || Object.keys(data).length === 0) {
            return res.status(400).json({ error: 'Keine Daten empfangen' });
        }

        await db.saveData(data);
        
        res.status(200).json({
            status: 'success',
            message: 'Daten empfangen und gespeichert'
        });
    } catch (e) {
        console.error(`Fehler beim Verarbeiten der POST-Anfrage: ${e}`);
        res.status(400).json({ error: e.toString() });
    }
});

app.get('/api/data/:period', async (req, res) => {
    const period = req.params.period;
    const hours = period === 'day' ? 24 : 168; // 168h = 1 week

    try {
        const [data, rainData] = await Promise.all([
            db.getData(hours),
            db.getHourlyRain(hours)
        ]);

        res.json({
            data: data,
            rain_hourly: rainData
        });
    } catch (e) {
        console.error(e);
        res.status(500).json({ error: 'Internal Server Error' });
    }
});

// Start server
app.listen(HTTP_PORT, '0.0.0.0', () => {
    console.log("Wetterstation wird gestartet...");
    console.log(`\nWeb-Interface verfügbar unter: http://localhost:${HTTP_PORT}`);
    console.log(`HTTP-POST Endpoint: http://localhost:${HTTP_PORT}/api/data/upload`);
    console.log("Drücke CTRL+C zum Beenden\n");
});