const express = require('express');
const router = express.Router();
const dotenv = require('dotenv');
const conn = require('../conn');
const connection = conn.connection;
const md5 = require('md5');
const path = require('path');
const fs = require('fs');
// connection.connect((err) => {
//     if (err) {
//         console.error('Error connecting to MySQL database: ' + err.stack);
//         return;
//     }
//     console.log('Connected to MySQL database as id ' + connection.threadId);
// })

dotenv.config();

router.get('/', async (req, res) => {
    res.sendFile(__dirname + '/ui/index.html');
})

router.get('/list' , (req, res) => {
    res.sendFile(__dirname + '/ui/list.html');
})

router.post('/' , (req, res) => {
    const { nama, alamat, langganan } = req.body;
    const query = "INSERT INTO tb_ont (nama, alamat, langganan) VALUES (?, ?, ?)";
    connection.query(query, [nama, alamat, langganan], (err, result) => {
        if (err) {
            console.log(err, "error menerima data");
            return res.status(500).send('Internal Server Error');
        }
        return res.status(200).send(result);
    })
     
})


router.get('/data', async (req, res) => {
    // get parameter
    const status = req.query.status == "all"  ? true : false
    const query = 'SELECT * FROM tb_ont '+ (status ? "" : "WHERE status != '-'");
    connection.query(query, (err, result) => {
        if (err) {
            console.log(err, "error menerima data");
            return res.status(500).send('Internal Server Error');
        }

        result.forEach(item => {
            if (item.updated) {
                // Convert the updated value to a Date object (if not already)
                let date = item.updated instanceof Date ? item.updated : new Date(item.updated);
        
                // Add UTC+8 offset (8 hours in milliseconds)
                let utcPlus8 = new Date(date.getTime() + 8 * 60 * 60 * 1000);
        
                // Format the datetime
                item.updated = utcPlus8.toISOString().replace("T", " ").slice(0, -5);
            }
        });
        
        
        
        

        return res.status(200).send(result);
    })
})

router.get('/data/:id', async (req, res) => {
    const { id } = req.params;
    const query = 'SELECT * FROM tb_ont WHERE id = ?';
    connection.query(query, [id], (err, result) => {
        if (err) {
            console.log(err, "error menerima data");
            return res.status(500).send('Internal Server Error');
        }
        if (result.length === 0) {
            return res.status(404).send('Data not found');
        }
        let date = result[0].updated instanceof Date ? result[0].updated : new Date(result[0].updated);
        let utcPlus8 = new Date(date.getTime() + 8 * 60 * 60 * 1000);
        result[0].updated = utcPlus8.toISOString().replace("T", " ").slice(0, -5);
        return res.status(200).send(result[0]);
    })
})

router.delete('/:id', async (req, res) => {
    const { id } = req.params;
    const query = 'DELETE FROM tb_ont WHERE id = ?';
    connection.query(query, [id], (err, result) => {
        if (err) {
            console.log(err, "error menghapus data");
            return res.status(500).send('Internal Server Error');
        }
        return res.status(200).send(result);
    })
})

router.get('/count', async (req, res) => {
    const query = "SELECT COUNT(*) AS all_count,SUM(status = 'Terhubung ke Wifi dan Internet') AS connected_count FROM  tb_ont;"
    connection.query(query, (err, result) => {
        if (err) {
            console.log(err, "error menerima data");
            return res.status(500).send('Internal Server Error');
        }
        return res.status(200).send(result[0]);
    })
})



module.exports = router