src/lib/server/notificationMediaResolver.js - master - Alcatraz/Wagomon

src/lib/server/notificationMediaResolver.js
171 строка6 KB
saitgalineu
vkr
26 май 2026, 10:08
26 май 2026, 10:08f9b2919
import path from 'node:path';
import { readFile } from 'node:fs/promises';
import { db } from './db.js';

const CONFIG_UPLOADS_PREFIX = '/uploads/config/';
const tablePhotoColsCache = new Map();

async function getPhotoCols(table) {
    if (tablePhotoColsCache.has(table)) return tablePhotoColsCache.get(table);
    const { rows } = await db.query(
        `
        SELECT column_name
        FROM information_schema.columns
        WHERE table_schema = current_schema()
          AND table_name = $1
          AND column_name IN ('photo_url', 'photo_urls')
        `,
        [table]
    );
    const cols = new Set(rows.map((r) => r.column_name));
    const result = {
        hasPhotoUrl: cols.has('photo_url'),
        hasPhotoUrls: cols.has('photo_urls')
    };
    tablePhotoColsCache.set(table, result);
    return result;
}

function parsePhotoUrls(value) {
    if (Array.isArray(value)) return value;
    if (!value) return [];
    if (typeof value === 'string') {
        try {
            const parsed = JSON.parse(value);
            return Array.isArray(parsed) ? parsed : [];
        } catch {
            return [];
        }
    }
    return [];
}

function normalizePhotoUrl(raw) {
    if (typeof raw !== 'string') return null;
    const trimmed = raw.trim();
    if (!trimmed.startsWith(CONFIG_UPLOADS_PREFIX)) return null;
    const filename = path.basename(trimmed);
    if (!filename) return null;
    return `${CONFIG_UPLOADS_PREFIX}${filename}`;
}

function collectPhotoUrls(row) {
    const urls = new Set();
    const single = normalizePhotoUrl(row?.photo_url);
    if (single) urls.add(single);
    for (const item of parsePhotoUrls(row?.photo_urls)) {
        const normalized = normalizePhotoUrl(item);
        if (normalized) urls.add(normalized);
    }
    return [...urls];
}

function toLocalPhotoPath(photoUrl) {
    const filename = path.basename(photoUrl);
    return path.join(process.cwd(), 'static', 'uploads', 'config', filename);
}

async function getControllerPhotoUrlsById(controllerId) {
    if (!Number.isFinite(controllerId)) return [];
    const cols = await getPhotoCols('wago');
    if (!cols.hasPhotoUrl && !cols.hasPhotoUrls) return [];
    const selectCols = [];
    if (cols.hasPhotoUrl) selectCols.push('photo_url');
    if (cols.hasPhotoUrls) selectCols.push('photo_urls');
    const { rows } = await db.query(`SELECT ${selectCols.join(', ')} FROM wago WHERE id = $1`, [controllerId]);
    return collectPhotoUrls(rows[0]);
}

async function getSensorPhotoUrlsById(sensorType, sensorId) {
    const table = sensorType === 'analog' ? 'sensors_analog' : sensorType === 'binary' ? 'sensors_binary' : null;
    if (!table || !Number.isFinite(sensorId)) return [];
    const cols = await getPhotoCols(table);
    if (!cols.hasPhotoUrl && !cols.hasPhotoUrls) return [];
    const selectCols = [];
    if (cols.hasPhotoUrl) selectCols.push('photo_url');
    if (cols.hasPhotoUrls) selectCols.push('photo_urls');
    const { rows } = await db.query(`SELECT ${selectCols.join(', ')} FROM ${table} WHERE id = $1`, [sensorId]);
    return collectPhotoUrls(rows[0]);
}

async function getControllerIdBySensor(sensorType, sensorId) {
    const table = sensorType === 'analog' ? 'sensors_analog' : sensorType === 'binary' ? 'sensors_binary' : null;
    if (!table || !Number.isFinite(sensorId)) return null;
    const { rows } = await db.query(
        `SELECT wago_id FROM ${table} WHERE id = $1`,
        [sensorId]
    );
    const id = Number(rows[0]?.wago_id);
    return Number.isFinite(id) ? id : null;
}

export async function resolveEventPhotoUrls(event) {
    const sensorId = Number(event?.sensorId);
    const sensorType = event?.sensorType;
    let controllerId = Number(event?.controllerId);
    if (!Number.isFinite(controllerId)) {
        controllerId = await getControllerIdBySensor(sensorType, sensorId);
    }
    const sensorPhotoUrls = await getSensorPhotoUrlsById(sensorType, sensorId);
    const controllerPhotoUrls = await getControllerPhotoUrlsById(controllerId);
    return { controllerId, controllerPhotoUrls, sensorPhotoUrls };
}

export async function resolvePhotoUrlsByIds({ controllerId, sensorType, sensorId }) {
    const ctrlIdNum = Number(controllerId);
    const sensorIdNum = Number(sensorId);
    const st = sensorType === 'analog' || sensorType === 'binary' ? sensorType : null;

    const [controllerPhotoUrls, sensorPhotoUrls] = await Promise.all([
        getControllerPhotoUrlsById(ctrlIdNum),
        st ? getSensorPhotoUrlsById(st, sensorIdNum) : Promise.resolve([])
    ]);
    return { controllerId: Number.isFinite(ctrlIdNum) ? ctrlIdNum : null, controllerPhotoUrls, sensorPhotoUrls };
}

export async function resolveDescriptionsByIds({ controllerId, sensorType, sensorId }) {
    const ctrlIdNum = Number(controllerId);
    const sensorIdNum = Number(sensorId);
    const st = sensorType === 'analog' || sensorType === 'binary' ? sensorType : null;
    const sensorTable = st === 'analog' ? 'sensors_analog' : st === 'binary' ? 'sensors_binary' : null;

    const [controllerRes, sensorRes] = await Promise.all([
        Number.isFinite(ctrlIdNum)
            ? db.query('SELECT description FROM wago WHERE id = $1', [ctrlIdNum])
            : Promise.resolve({ rows: [] }),
        sensorTable && Number.isFinite(sensorIdNum)
            ? db.query(`SELECT description FROM ${sensorTable} WHERE id = $1`, [sensorIdNum])
            : Promise.resolve({ rows: [] })
    ]);

    const controllerDescriptionRaw = controllerRes.rows[0]?.description;
    const sensorDescriptionRaw = sensorRes.rows[0]?.description;
    const controllerDescription = typeof controllerDescriptionRaw === 'string'
        ? controllerDescriptionRaw.trim() || null
        : null;
    const sensorDescription = typeof sensorDescriptionRaw === 'string'
        ? sensorDescriptionRaw.trim() || null
        : null;

    return { controllerDescription, sensorDescription };
}

export async function loadPhotoBuffers(photoUrls) {
    const urls = Array.isArray(photoUrls) ? photoUrls : [];
    if (!urls.length) return [];

    const settled = await Promise.allSettled(
        urls.map(async (photoUrl) => {
            const filePath = toLocalPhotoPath(photoUrl);
            const data = await readFile(filePath);
            return {
                filename: path.basename(filePath),
                buffer: data
            };
        })
    );

    return settled
        .filter((item) => item.status === 'fulfilled')
        .map((item) => item.value);
}