const mqtt = require('mqtt');
const mysql = require('mysql');

// Konfigurasi MQTT broker
const brokerUrl = 'mqtt://localhost'; // Ganti dengan alamat broker MQTT Anda
const client = mqtt.connect(brokerUrl);

// Konfigurasi database
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'iot_logs', // Ganti dengan nama database Anda
});

// Koneksi ke database
db.connect((err) => {
    if (err) {
        console.error('Failed to connect to database:', err);
        process.exit(1);
    }
    console.log('Connected to database');
});

// Subscribe ke topik tertentu
const topics = [
    'viseron/binary_sensor/camera_6_object_detected/state',
    'viseron/sensor/camera_6/license_plate_recognition',
    'viseron/binary_sensor/camera_8_object_detected/state',
    'viseron/sensor/camera_8/license_plate_recognition',
];

client.on('connect', () => {
    console.log('Connected to MQTT broker');
    client.subscribe(topics, (err) => {
        if (err) {
            console.error('Failed to subscribe to topics:', err);
        } else {
            console.log('Subscribed to topics:', topics);
        }
    });
});

// Proses pesan yang diterima
client.on('message', (topic, message) => {
    try {
        const payload = JSON.parse(message.toString());
        console.log(`Received message on ${topic}:`, payload);

        // Simpan data ke database berdasarkan topik
        if (topic.includes('object_detected')) {
            saveObjectDetectionLog(topic, payload);
        } else if (topic.includes('license_plate_recognition')) {
            saveLicensePlateLog(topic, payload);
        }
    } catch (err) {
        console.error('Failed to process message:', err);
    }
});

// Simpan log deteksi objek ke database
function saveObjectDetectionLog(topic, payload) {
    const objects = payload.attributes.objects || [];
    const camera = payload.attributes.name || 'Unknown Camera';

    objects.forEach((obj) => {
        const sql = 'INSERT INTO object_logs (topic, camera, label, confidence, timestamp) VALUES (?, ?, ?, ?, ?)';
        const values = [
            topic,
            camera,
            obj.label,
            obj.confidence * 100, // Konversi ke persen
            new Date(),
        ];

        db.query(sql, values, (err) => {
            if (err) {
                console.error('Failed to insert object detection log:', err);
            } else {
                console.log('Object detection log saved:', values);
            }
        });
    });
}

// Simpan log license plate ke database
function saveLicensePlateLog(topic, payload) {
    const results = payload.attributes.result || [];
    const camera = payload.attributes.name || 'Unknown Camera';

    results.forEach((result) => {
        const sql = 'INSERT INTO license_plate_logs (topic, camera, plate, confidence, known, timestamp) VALUES (?, ?, ?, ?, ?, ?)';
        const values = [
            topic,
            camera,
            result.plate,
            result.confidence * 100, // Konversi ke persen
            result.known ? 1 : 0, // Simpan sebagai boolean
            new Date(),
        ];

        db.query(sql, values, (err) => {
            if (err) {
                console.error('Failed to insert license plate log:', err);
            } else {
                console.log('License plate log saved:', values);
            }
        });
    });
}
