const sqlite3 = require('sqlite3').verbose();
const xlsx = require('xlsx');
const path = require('path');
const db = new sqlite3.Database(path.join(__dirname, '../proxy/proxies.db'));

const summary = () => {
    return new Promise((resolve, reject) => {
        const query = `
            SELECT p.address AS ProxyAddress,
                   COUNT(ua.id) AS TotalPenggunaan,
                   SUM(CASE WHEN ua.status = 'success' THEN 1 ELSE 0 END) AS Sukses,
                   SUM(CASE WHEN ua.status = 'failed' THEN 1 ELSE 0 END) AS Gagal,
                   MAX(ua.times) AS TerakhirDigunakan
            FROM proxies p
            LEFT JOIN used_at ua ON p.id = ua.proxy_id
            GROUP BY p.address
            ORDER BY TerakhirDigunakan DESC;
        `;

        db.all(query, (err, rows) => {
            if (err) {
                return reject(err);
            }

            // Buat worksheet dari data
            const worksheet = xlsx.utils.json_to_sheet(rows);

            // Buat workbook dan tambahkan worksheet
            const workbook = xlsx.utils.book_new();
            xlsx.utils.book_append_sheet(workbook, worksheet, 'Rekapitulasi');

            // Simpan file Excel
            const excelFilePath = 'rekapitulasi_proxy.xlsx';
            xlsx.writeFile(workbook, excelFilePath);

            console.log(`Rekapitulasi proxy disimpan ke file ${excelFilePath}`);

            resolve();
        });
    });
};

module.exports = {
    summary
};