MQTTvue/mqtt-vue-dashboard/server/dist/models/deviceBinding.js

"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.DeviceBindingModel = void 0;
const database_1 = require("../config/database");
class DeviceBindingModel {
    static async getAll() {
        const query = `
      SELECT db.*, d.device_name as original_device_name, d.status as device_status,
             r.name as room_name, r.room_number, r.floor_id
      FROM device_bindings db
      LEFT JOIN devices d ON db.device_clientid = d.clientid
      LEFT JOIN rooms r ON db.room_id = r.id
      ORDER BY r.floor_id, r.room_number, db.device_name
    `;
        return await (0, database_1.executeQuery)(query);
    }
    static async getByRoomId(roomId) {
        const bindings = await (0, database_1.executeQuery)(`
      SELECT 
        db.id,
        db.device_clientid as clientid,
        db.room_id,
        COALESCE(db.device_name, d.device_name, d.clientid) as device_name,
        db.device_type,
        db.properties,
        db.created_at,
        db.updated_at,
        d.status as device_status,
        d.firmware_version, 
        d.last_ip_port, 
        d.last_online_time, 
        d.last_offline_time,
        d.rssi
      FROM device_bindings db
      LEFT JOIN devices d ON db.device_clientid COLLATE utf8mb4_unicode_ci = d.clientid COLLATE utf8mb4_unicode_ci
      WHERE db.room_id = ? COLLATE utf8mb4_unicode_ci
      ORDER BY db.device_name
    `, [roomId]);
        for (const binding of bindings) {
            const deviceId = binding.clientid;
            const tempData = await (0, database_1.executeQuery)(`
        SELECT value, timestamp
        FROM sensor_data
        WHERE device_id = ? AND data_type = 'temperature'
        ORDER BY timestamp DESC
        LIMIT 1
      `, [deviceId]);
            const relayData = await (0, database_1.executeQuery)(`
        SELECT value, timestamp
        FROM sensor_data
        WHERE device_id = ? AND data_type = 'relay'
        ORDER BY timestamp DESC
        LIMIT 1
      `, [deviceId]);
            if (tempData.length > 0) {
                binding.temperature = tempData[0].value;
                binding.temperature_timestamp = tempData[0].timestamp;
            }
            if (relayData.length > 0) {
                binding.relay_status = relayData[0].value === 'ON' ? true : false;
                binding.relay_timestamp = relayData[0].timestamp;
            }
            binding.value = {
                temperature: tempData.length > 0 ? tempData[0].value : null,
                relay: relayData.length > 0 ? (relayData[0].value === 'ON' ? true : false) : false
            };
        }
        return bindings;
    }
    static async getByDeviceClientId(clientId) {
        const query = `
      SELECT db.*, d.device_name as original_device_name, d.status as device_status,
             r.name as room_name, r.room_number, r.floor_id
      FROM device_bindings db
      LEFT JOIN devices d ON db.device_clientid = d.clientid
      LEFT JOIN rooms r ON db.room_id = r.id
      WHERE db.device_clientid = ?
    `;
        const bindings = await (0, database_1.executeQuery)(query, [clientId]);
        return bindings.length > 0 ? bindings[0] : null;
    }
    static async bindDevice(deviceClientId, roomId) {
        const existingBinding = await this.getByDeviceClientId(deviceClientId);
        if (existingBinding) {
            return this.updateBinding(existingBinding.id, { room_id: roomId });
        }
        const query = `
      INSERT INTO device_bindings (device_clientid, room_id)
      VALUES (?, ?)
    `;
        const values = [deviceClientId, roomId];
        const result = await (0, database_1.executeQuery)(query, values);
        return this.getByDeviceClientId(deviceClientId);
    }
    static async bindDeviceWithDetails(bindingData) {
        const existingBinding = await this.getByDeviceClientId(bindingData.device_clientid);
        if (existingBinding) {
            return this.updateBinding(existingBinding.id, bindingData);
        }
        const query = `
      INSERT INTO device_bindings (device_clientid, room_id, device_name, device_type, properties)
      VALUES (?, ?, ?, ?, ?)
    `;
        const values = [
            bindingData.device_clientid,
            bindingData.room_id,
            bindingData.device_name || null,
            bindingData.device_type || null,
            bindingData.properties ? JSON.stringify(bindingData.properties) : null
        ];
        const result = await (0, database_1.executeQuery)(query, values);
        return this.getByDeviceClientId(bindingData.device_clientid);
    }
    static async unbindDevice(deviceClientId) {
        const query = 'DELETE FROM device_bindings WHERE device_clientid = ?';
        const result = await (0, database_1.executeQuery)(query, [deviceClientId]);
        return result.affectedRows > 0;
    }
    static async updateBinding(id, updateData) {
        const fields = [];
        const values = [];
        if (updateData.room_id !== undefined) {
            fields.push('room_id = ?');
            values.push(updateData.room_id);
        }
        if (updateData.device_name !== undefined) {
            fields.push('device_name = ?');
            values.push(updateData.device_name);
        }
        if (updateData.device_type !== undefined) {
            fields.push('device_type = ?');
            values.push(updateData.device_type);
        }
        if (updateData.properties !== undefined) {
            fields.push('properties = ?');
            values.push(updateData.properties ? JSON.stringify(updateData.properties) : null);
        }
        fields.push('updated_at = NOW()');
        values.push(id);
        const query = `
      UPDATE device_bindings 
      SET ${fields.join(', ')}
      WHERE id = ?
    `;
        await (0, database_1.executeQuery)(query, values);
        const bindingQuery = 'SELECT device_clientid FROM device_bindings WHERE id = ?';
        const binding = await (0, database_1.executeQuery)(bindingQuery, [id]);
        if (binding.length > 0) {
            return this.getByDeviceClientId(binding[0].device_clientid);
        }
        throw new Error('Failed to retrieve updated binding');
    }
    static async getAvailableDevices() {
        const query = `
      SELECT d.* FROM devices d
      LEFT JOIN device_bindings db ON d.clientid COLLATE utf8mb4_unicode_ci = db.device_clientid COLLATE utf8mb4_unicode_ci
      WHERE db.device_clientid IS NULL
      ORDER BY d.device_name, d.clientid
    `;
        return await (0, database_1.executeQuery)(query);
    }
    static async getRoomDevicesWithDetails(roomId) {
        const query = `
      SELECT 
        d.clientid,
        d.device_name as original_device_name,
        d.status as device_status,
        d.firmware_version,
        d.last_ip_port,
        d.last_online_time,
        d.last_offline_time,
        d.online_duration,
        d.connect_count,
        d.rssi,
        db.device_name,
        db.device_type,
        db.properties,
        r.name as room_name,
        r.floor_id
      FROM device_bindings db
      LEFT JOIN devices d ON db.device_clientid COLLATE utf8mb4_unicode_ci = d.clientid COLLATE utf8mb4_unicode_ci
      LEFT JOIN rooms r ON db.room_id = r.id
      WHERE db.room_id = ?
      ORDER BY db.device_name
    `;
        return await (0, database_1.executeQuery)(query, [roomId]);
    }
    static async getDevicesByRoomId(roomId) {
        const query = `
      SELECT 
        d.clientid,
        d.device_name,
        d.username,
        d.status,
        d.firmware_version,
        d.device_ip_port,
        d.last_ip_port,
        d.last_event_time,
        d.last_online_time,
        d.last_offline_time,
        d.online_duration,
        d.connect_count,
        d.rssi,
        d.created_at,
        d.updated_at,
        db.device_name as room_device_name,
        db.device_type as room_device_type,
        db.properties
      FROM device_bindings db
      LEFT JOIN devices d ON db.device_clientid COLLATE utf8mb4_unicode_ci = d.clientid COLLATE utf8mb4_unicode_ci
      WHERE db.room_id = ?
      ORDER BY d.device_name
    `;
        return await (0, database_1.executeQuery)(query, [roomId]);
    }
    static async getUnboundDevices() {
        const query = `
      SELECT 
        d.clientid,
        d.device_name,
        d.username,
        d.status,
        d.firmware_version,
        d.device_ip_port,
        d.last_ip_port,
        d.last_event_time,
        d.last_online_time,
        d.last_offline_time,
        d.online_duration,
        d.connect_count,
        d.created_at,
        d.updated_at
      FROM devices d
      LEFT JOIN device_bindings db ON d.clientid COLLATE utf8mb4_unicode_ci = db.device_clientid COLLATE utf8mb4_unicode_ci
      WHERE db.device_clientid IS NULL
      ORDER BY d.device_name, d.clientid
    `;
        return await (0, database_1.executeQuery)(query);
    }
    static async getAllDevicesWithBindingStatus(page = 1, pageSize = 10, filters = {}) {
        const offset = (page - 1) * pageSize;
        const conditions = [];
        const params = [];
        if (filters.status) {
            conditions.push('d.status = ?');
            params.push(filters.status);
        }
        if (filters.room_id) {
            conditions.push('db.room_id = ?');
            params.push(filters.room_id);
        }
        if (filters.search) {
            const searchTerm = `%${filters.search}%`;
            conditions.push('(d.clientid LIKE ? OR d.device_name LIKE ? OR d.username LIKE ? OR r.name LIKE ?)');
            params.push(searchTerm, searchTerm, searchTerm, searchTerm);
        }
        const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
        const query = `
      SELECT 
        d.*,
        db.id as binding_id,
        db.room_id,
        db.device_name as room_device_name,
        db.device_type as room_device_type,
        r.name as room_name,
        r.room_number,
        r.floor_id
      FROM devices d
      LEFT JOIN device_bindings db ON d.clientid COLLATE utf8mb4_unicode_ci = db.device_clientid COLLATE utf8mb4_unicode_ci
      LEFT JOIN rooms r ON db.room_id = r.id
      ${whereClause}
      ORDER BY d.device_name, d.clientid
      LIMIT ? OFFSET ?
    `;
        params.push(pageSize, offset);
        const countQuery = `
      SELECT COUNT(DISTINCT d.id) as total
      FROM devices d
      LEFT JOIN device_bindings db ON d.clientid COLLATE utf8mb4_unicode_ci = db.device_clientid COLLATE utf8mb4_unicode_ci
      LEFT JOIN rooms r ON db.room_id = r.id
      ${whereClause}
    `;
        const countParams = conditions.length > 0 ? params.slice(0, -2) : [];
        const [devices, countResult] = await Promise.all([
            (0, database_1.executeQuery)(query, params),
            (0, database_1.executeQuery)(countQuery, countParams)
        ]);
        for (const device of devices) {
            const deviceId = device.clientid;
            const tempData = await (0, database_1.executeQuery)(`
        SELECT value, timestamp
        FROM sensor_data
        WHERE device_id = ? AND data_type = 'temperature'
        ORDER BY timestamp DESC
        LIMIT 1
      `, [deviceId]);
            const relayData = await (0, database_1.executeQuery)(`
        SELECT value, timestamp
        FROM sensor_data
        WHERE device_id = ? AND data_type = 'relay'
        ORDER BY timestamp DESC
        LIMIT 1
      `, [deviceId]);
            if (tempData.length > 0) {
                device.temperature = tempData[0].value;
                device.temperature_timestamp = tempData[0].timestamp;
            }
            if (relayData.length > 0) {
                device.relay_status = relayData[0].value === 'ON' ? true : false;
                device.relay_timestamp = relayData[0].timestamp;
            }
            device.value = {
                temperature: tempData.length > 0 ? tempData[0].value : null,
                relay: relayData.length > 0 ? (relayData[0].value === 'ON' ? true : false) : false
            };
        }
        return {
            devices,
            total: countResult[0].total
        };
    }
    static async updateDeviceValue(deviceClientId, value) {
        const query = `
      UPDATE devices 
      SET value = ? 
      WHERE clientid = ?
    `;
        const result = await (0, database_1.executeQuery)(query, [value, deviceClientId]);
        return result.affectedRows > 0;
    }
}
exports.DeviceBindingModel = DeviceBindingModel;
//# sourceMappingURL=deviceBinding.js.map