interier/utils/meteostanica.js

import { Database } from "bun:sqlite";

const meteostanicaDB = new Database("./data/meteostanica.sqlite");

meteostanicaDB.run(`create table if not exists data (
    timestamp datetime default current_timestamp primary key,

    indoorTemp text not null,
    indoorPressure text not null,
    indoorHumidity text not null,
    indoorAltitude text not null,

    outdoorConnected integer not null,
    outdoorTemp text not null,
    outdoorPressure text not null,
    outdoorHumidity text not null,
    outdoorAltitude text not null
);`)

export default class Meteostanica {
    static getData() {
        const statement = meteostanicaDB.prepare(`
            SELECT *
            FROM data
            ORDER BY timestamp DESC;
        `)

        const result = statement.all();

        return result
    }

    static getDataProperty(property) {
        const tableNames = meteostanicaDB.prepare(`PRAGMA table_info('data');`).all()
        if (!tableNames.find(i => i.name === property)) return null

        const statement = meteostanicaDB.prepare(`
            SELECT ${property}
            FROM data
            ORDER BY timestamp DESC
            LIMIT 10;
        `)

        const result = statement.all();

        return result
    }

    static getDataPropertyDaily(property, date) {
        const tableNames = meteostanicaDB.prepare(`PRAGMA table_info('data');`).all()
        if (!tableNames.find(i => i.name === property)) return null

        const statement = meteostanicaDB.prepare(`
            SELECT strftime('%Y-%m-%d %H:00:00', timestamp) AS timeMark, 
                AVG(${property}) AS value
            FROM data
            WHERE date(timestamp) = ?  -- Pass 'YYYY-MM-DD' here
            GROUP BY timeMark
            ORDER BY timeMark;
        `)
        
        const result = statement.all(date);

        return result
    }

    static getDataPropertyMonthly(property, yearMonth) {
        const tableNames = meteostanicaDB.prepare(`PRAGMA table_info('data');`).all()
        if (!tableNames.find(i => i.name === property)) return null

        const statement = meteostanicaDB.prepare(`
            SELECT date(timestamp) AS timeMark, 
                AVG(${property}) AS value
            FROM data
            WHERE strftime('%Y-%m', timestamp) = ? -- Pass 'YYYY-MM' here
            GROUP BY timeMark
            ORDER BY timeMark;
        `)
        
        const result = statement.all(yearMonth);

        return result
    }

    static getDataPropertyYearly(property, year) {
        const tableNames = meteostanicaDB.prepare(`PRAGMA table_info('data');`).all()
        if (!tableNames.find(i => i.name === property)) return null

        const statement = meteostanicaDB.prepare(`
            SELECT strftime('%Y-%m', timestamp) AS timeMark, 
                AVG(${property}) AS value
            FROM data
            WHERE strftime('%Y', timestamp) = ? -- Pass 'YYYY' here
            GROUP BY timeMark
            ORDER BY timeMark;
        `)
        
        const result = statement.all(year);

        return result
    }

    static getDataPropertyAllTime(property) {
        const tableNames = meteostanicaDB.prepare(`PRAGMA table_info('data');`).all()
        if (!tableNames.find(i => i.name === property)) return null

        const statement = meteostanicaDB.prepare(`
            SELECT strftime('%Y', timestamp) AS timeMark, 
                AVG(${property}) AS value
            FROM data
            GROUP BY timeMark
            ORDER BY timeMark;
        `)
        
        const result = statement.all();

        return result
    }

    static getDateMap() {
        const statement = meteostanicaDB.query("SELECT DISTINCT date(timestamp) as d FROM data ORDER BY d ASC");
        const rows = statement.all();

        return rows.reduce((acc, row) => {
            const [year, month, day] = row.d.split("-");

            if (!acc[year]) acc[year] = {};
            if (!acc[year][month]) acc[year][month] = [];

            acc[year][month].push(day);

            return acc;
        }, {});
    }
}