const express = require('express');
const zlib = require('zlib');
const protobuf = require('protobufjs');
const path = require('path');
const util = require('util');
const WebSocket = require('ws');
//const DB_Connection = require('./DB/Connection');
const bcrypt = require('bcrypt');
const { once } = require('events');

const { message } = require('protocol-buffers/compile');
const { table } = require('console');
// const knex = require('knex')({
//     client: 'mysql',
//     connection: {
//         host: 'vps2374.tmdvps.com',
//         user: 'truepestco_user_ma',
//         password: 'Wj7p{95H$7Z*BFw',
//         database: 'truepestco_db_ma'
//     }
// });

const Connection = require("./DB/Connection");
const { use } = require('bcrypt/promises');
let con = new Connection(); //in use
const knex = con.kp(); //in use

const app = express();
const port = 50000;

//v2

// Load the protobuf definition once at startup
const root = protobuf.loadSync(path.resolve(__dirname, 'MSG/test3.proto'));
const Person = root.lookupType('Person');

// Promisify zlib's deflateRaw to use async/await
const deflateRawAsync = util.promisify(zlib.deflateRaw);

// Function to compress data
const compressData = async (data) => {
    return deflateRawAsync(data, { level: zlib.constants.Z_BEST_SPEED });
};

// Create a WebSocket server
const wss = new WebSocket.Server({ noServer: true });

let counter = 0;
//rcM let data = [];
let data2 = [];

function sleep(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
}


async function doVerification(ws, resultSet, password)
{
    if(resultSet.length <= 0)
    {
        const response = {
            status: '0',
            message: 'Authentication failed',
            user_id: '0',
            active: 1,
            cmpIdV: '0'
        };
        return response;
    }
    let row = resultSet;

    var hash = row[0].password
    var userAc = row[0].access_insect_user_id
    var userAp = row[0].access_permissions_user_id
    var isM = row[0].role_id
    var userId = row[0].user_id
    if(isM != 2 && isM != 3)
    {
        if (userAp != row[0].user_id)
        {
            const response = {
                status: '2',
                message: 'Authentication failed',
                user_id: row[0].user_id,
                active: 1
            };
            return response;
        }
        else
        {
            var apRH = '1'
            const apQ = knex('access_permissions')
            .select(
                    'access_permissions.user_id'
            )
            .where('user_id', userId)
            .andWhere('perm_id', '2');    
            await Promise.all([apQ])
            .then(async ([apR]) => {
                //await doVerification(ws, userResult);
                if(apR.length > 0)
                {
                    let apRow = apR;
                    if(apRow[0].user_id == userId){ 
                        if(userAc != userId)
                        {
                            apRH = '0'
                        } else {
                            apRH = '1'
                        }
                    }
                    else { apRH = '1' }

                    console.log('async apRH ', apRH);
                }
            })
            .catch((err) => console.log("ver operatiion err ", err));

            console.log('after async ', apRH)
            if(apRH == '0')
            {
                const response = {
                    status: '2',
                    message: 'Authentication failed',
                    user_id: row[0].user_id,
                    active: 1
                };
                return response;
            }

        }
    }

   // console.log('user id ', row[0].user_id, ' isManager ', isM, ' userAc ', userAc, ' userAp ', userAp );
    hash = hash.replace("$2y$", "$2b$");
    const isPasswordValid = await bcrypt.compare(password, hash);
    const response = {
        status: isPasswordValid ? '1' : '0',
        message: isPasswordValid ? 'Authentication successful' : 'Authentication failed',
        user_id: row[0].user_id,
        active: row[0].act_state,
        cmpIdV: row[0].comp_id
    };
    return response;
}

async function tem()
{
    var password = "Taha1987..@@";
    var hash = "$2y$11$CWOhMCGtG4EiowmY2SF.6uGQ6iNCAZOMInKpIG.99zwojOJqTZNrW";
    hash = hash.replace("$2y$", "$2b$");
    const isV = await bcrypt.compare(password, hash);
    console.log("result is ", isV);
}

async function generateHash(password) {
    const saltRounds = 10;

   // try {
        let hash = await bcrypt.hash(password, saltRounds);
        hash = hash.replace("$2b$", "$2y$"); // Convert to PHP-compatible format
        console.log("Generated Hash:", hash);

        return hash
    // } catch (err) {
    //     console.error("Error hashing password:", err);
    // }
}
async function updatePwd(ws, con, userId, pwd, ver)
{
    var holdPwd = await generateHash(pwd)
    let data = { password: holdPwd };
    try {
        await con.kp().transaction(async trx => {
            await trx("users") // Use the transaction object
                .where('user_id', userId)
                .update(data);
    
            ws.send(JSON.stringify(ver));
        })
        .catch((err) => console.log("update pwd err ", err) );
    } catch (err) {
        console.log("❌ Transaction Error:", err);
    } finally {
        ws.close();
    }
}
//tem();
async function processAndSendData(ws, resultSet, i) {
    let data = [];
    let personPayload = null;
    let binaryData = null;
    let compressedData = null;
    let tables = null;
    // Loop through the result set and process each row
    if(i==1)
    {
        tables = "users";
    }
    else if(i==1.1)
    {
        tables = "farms"
    }
    else if(i==1.15)
    {
        tables = "manager_farm_access"
    }
    else if(i==1.2)
    {
        tables = "subscriptions"
    }
    else if(i==1.3)
    {
        tables = "access_permissions"
    }
    else if(i==1.4)
    {
        tables = "access_insect"
    }
    else if(i==2)
    {
        tables = "site";
    }
    else if(i==2.1)
    {
        tables = "thresholds"
    }
    else if(i==2.9 || i==2.95)
    {
        tables = "archive"
    }
    else if(i==3)
    {
        tables = "poly";
        //data.push("site: "+temp);
    }
    else if(i==4)
    {
        tables = "PolyLine";
    }
    else if(i==5)
    {
        tables = "traps";
    }
    else if(i==6)
    {
        tables = "scans";
    }
    console.log("table is ", tables);
    ws.send(tables);
    for (let row of resultSet) {
        //await new Promise(resolve => setTimeout(resolve, 8000));

        let temp = null;
        if(i==1)
        {
            temp = `{${row.user_id},${row.role_id},${row.comp_id},${row.fullname},${row.email},${row.password},${row.country},${row.company_name}}`;
            console.log("data user ", temp)
        }
        else if(i==1.1)
        {
            temp = `{${row.farm_id},${row.farm_name},${row.comp_id},${row.col_sync}}`; 
        }
        else if(i==1.15)
        {
            temp = `{${row.mfa_id},${row.farm_id},${row.user_id},${row.col_sync}}`; 
        }
        else if(i==1.2)
        {
            temp = `{${row.sub_id},${row.comp_id},${row.insect_id},${row.start_date},${row.end_date},${row.created_at},${row.col_sync}}`; 
        }
        else if(i==1.3)
        {
            temp = `{${row.ap_id},${row.user_id},${row.perm_id},${row.col_sync},${row.farm_id},${row.site_id}}`; 
        }
        else if(i==1.4)
        {
            temp = `{${row.ai_id},${row.insect_id},${row.user_id},${row.col_sync}}`; 
        }
        else if(i==2)
        {
            temp = `{${row.site_id},${row.farm_id},${row.site_name},${row.col_sync}}`;
        }
        else if(i==2.1)
        {
            temp = `{${row.thre_id},${row.site_id},${row.insect_id},${row.red},${row.amber},${row.time_between},${row.created_at},${row.col_sync}}`;
        }
        else if(i==2.9 || i==2.95)
        {
           // console.log(i," is sent")
            temp = `{${row.trap_id},${row.tb_name},${row.tb_id},${i}}`;

        }
        else if(i==3)
        {
            temp = `{${row.poly_id},${row.site_id},${row.poly_name},${row.created_at},${row.col_sync}}`;
          //  console.log(i, "poly", tables)
        }
        else if(i==4)
        { //poly lines
            temp = `{${row.line_id},${row.poly_id},${row.line_name},${row.created_at},${row.col_sync}}`;
        }
        else if(i==5)
        {
            temp = `{${row.trap_id},${row.line_id},${row.trap_qr},${row.latitude},${row.longitude},${row.col_order},${row.created_at},${row.col_sync},${row.lure_date}}`;
          //  console.log("trap ", temp)
        //  console.log("sent data ", temp)

       
        }
        else if(i==6)
        {
            temp = `{${row.scan_id},${row.trap_id},${row.insect_id},${row.data},${row.created_at},${row.ismanager},${row.col_sync}}`;
            //console.log("scan test ", temp)
        }

        data.push(temp);

        // If data size reaches the threshold, encode and compress
        if (data.length >= 20) {
            personPayload = Person.create({ data });
            binaryData = Person.encode(personPayload).finish();
            compressedData = await compressData(binaryData);

            if (compressedData.length >= 1000) {
                // Send compressed data
                ws.send(compressedData.length); // Send the total size
                ws.send(compressedData); // Send the compressed data itself
                console.log("Data array reset - data length:", data.length, "compressed data:", compressedData.length);

                // Reset data and payloads for the next batch
                data = [];
                personPayload = null;
                binaryData = null;
                compressedData = null;
            }
        }
    }

    // Process remaining data after the loop (if any)
    if (data.length > 0) {
        personPayload = Person.create({ data });
        binaryData = Person.encode(personPayload).finish();
        compressedData = await compressData(binaryData);

        if (compressedData.length > 2) {
            console.log("Fcompressed data:", compressedData.length);
            console.log("Foriginal data:", binaryData.length);
            ws.send(compressedData.length); // Send the total size
            ws.send(compressedData); // Send the compressed data
            data = [];
        }
    }
}


wss.on('connection', async (ws) => {
    console.log('WebSocket connection established.');
    try {
        if (![...wss.clients].some(client => client.readyState === WebSocket.OPEN)) {
            return res.status(500).send('No WebSocket clients connected.');
        }

 

        let isAuthenticated = false;

        // await ws.on('message', async (message) => {
        //     try {
        //         let data;
        //         console.log("message type ", typeof message);
        //         console.log("Raw message ", message)
        //         // Handle WebSocket message types correctly
        //         if (typeof message === 'string') {
        //             // Message is already a string
        //             data = JSON.parse(message);
        //             console.log("data is 2 ",data)
        //         } else if (message instanceof Buffer || message instanceof Uint8Array) {
        //             // Message is binary, convert to string
        //             data = JSON.parse(message.toString('utf-8'));
        //             console.log("data is ", data, " size ", data.length);
        //         } else {
        //             throw new Error('Unsupported message format');
        //         }

        //         const {username, password} = data

        //         const isPasswordValid = await bcrypt.compare(username, password);

        //         console.log("verification result is ", isPasswordValid);
        //     } catch (err) {
        //         console.error('Invalid message format:', err);
        //         ws.send('AUTH_FAILED');
        //        // ws.close();
        //     }
        // });

        const [message] = await once(ws, 'message'); // Wait for the 'message' event
       // console.log('First message received:', message);
        let data = JSON.parse(message.toString('utf-8'));
        console.log("essential data ", data)
        var {username, cmp_id, password, m_p, m_L, m_t, m_s} = data
        // const isPasswordValid = await bcrypt.compare(username, password);
        // const response = {
        //     status: isPasswordValid ? 'success' : 'failure',
        //     message: isPasswordValid ? 'Authentication successful' : 'Authentication failed'
        // };

        // const response = await doVerification(ws, )
        // ws.send(JSON.stringify(response));

       // console.log('Parsed message data:', data, "is valid ", isPasswordValid);
        console.log("sending gets started");

        const userQuery = knex('users')
        .select(
            'users.user_id',
            'users.role_id',
            'users.comp_id',
            'users.fullname',
            'users.email',
            'users.password',
            'users.country',
            'users.act_state',
            'access_insect.user_id as access_insect_user_id',
            'access_permissions.user_id as access_permissions_user_id',
            'companies.company_name'
        )
        .leftJoin('access_permissions', 'access_permissions.user_id', 'users.user_id')
        .leftJoin('access_insect', 'access_insect.user_id', 'users.user_id')
        .leftJoin('subscriptions', 'subscriptions.comp_id', 'users.comp_id')
        .leftJoin('companies', 'companies.comp_id', 'users.comp_id')
        .andWhere('users.email', username)
        .groupBy('users.user_id');


        let ver = "";
        await Promise.all([userQuery])
        .then(async ([userResult]) => {
            //await doVerification(ws, userResult);
            ver = await doVerification(ws, userResult, password);
            console.log("verification ", ver)
            if(m_p != "-1")
            {
                ws.send(JSON.stringify(ver));

            }
         //   ws.send(JSON.stringify(ver));
        })
        .catch((err) => console.log("ver err ", err)); //new 8/29

       console.log("rs ", ver.status);
        if(ver.status == "1")
        {

        }
        else
        {
            ws.close();
            return;
        }

        if(ver.active == "0")
        {
            ws.close();
            return;
        }

        if(m_p == "-1")
        {
            console.log("changing pwd")
            //change pwd
           // ver.cmpIdV = "logg"
           await updatePwd(ws, con, ver.user_id, cmp_id, ver) //cmp_id is new password
            //ws.send(JSON.stringify(ver));
            ws.close()
            
            return

        }

        console.log(ver)
        //let cmpId = cmp_id;
        let cmpId = ver.cmpIdV;

        if (cmp_id != cmpId)
        {
            m_s = '0'
            m_L = '0' 
            m_p = '0'
            m_t = '0'
            //console.log("they are equal", m_s, m_L, m_p, m_t)
        }          
         

        //console.log("user id is ", ver.user_id, " comp ", cmp_id)

        const mfaQ = knex
        .select('mfa_id', 'farm_id', 'user_id', 'col_sync')
        .from('manager_farm_access')
        .where('user_id', ver.user_id);

        const accPermQuery = knex
        .select('ap_id', 'user_id', 'perm_id', 'col_sync', 'farm_id','site_id')
        .from('access_permissions')
        .where('user_id', ver.user_id);

        const accInsQuery = knex
        .select('ai_id', 'insect_id', 'user_id', 'col_sync')
        .from('access_insect')
        .where('user_id', ver.user_id);


        const farmQuery = knex
        .select('farm_id', 'farm_name', 'comp_id', 'col_sync')
        .from('farms')
        .where('comp_id', cmpId);

        
        const subsQuery = knex
        .select('sub_id', 'comp_id', 'insect_id', 'start_date', 'end_date', 'created_at', 'col_sync')
        .from('subscriptions')
        .where('comp_id', cmpId)

        const sitesQuery = knex('sites')
        .select('sites.site_id', 'sites.farm_id', 'sites.site_name', 'sites.col_sync')
        .innerJoin('farms', 'farms.farm_id', 'sites.farm_id')
        .where('farms.comp_id', cmpId)
        .orderBy('sites.col_sync', 'asc');
      

        const threQuery = knex('thresholds')
        .select(
          'thresholds.thre_id',
          'thresholds.site_id',
          'thresholds.insect_id',
          'thresholds.red',
          'thresholds.amber',
          'thresholds.time_between',
          'thresholds.created_at',
          'thresholds.col_sync'
        )
        .innerJoin('sites', 'sites.site_id', 'thresholds.site_id')
        .innerJoin('farms', 'farms.farm_id', 'sites.farm_id')
        .where('farms.comp_id', cmpId)
        //.andWhere('thresholds.col_sync', 3)
        .orderBy('thresholds.col_sync', 'asc');
      
        const archQuery = knex
        .select('trap_id', 'tb_name', 'tb_id')
        .from('archive')
        .where('comp_id', cmpId)
        .whereNotNull('tb_id') // Equivalent to "AND tb_id IS NOT NULL"
        .groupBy('tb_id') // Equivalent to "GROUP BY tb_id"
        .orderBy('archive.id', 'desc'); // Equivalent to "ORDER BY archive.id DESC"
    

        const archQueryTap = knex
        .select('trap_id','tb_name','tb_id')
        .from('archive')
        .where('comp_id', cmpId)
        .whereNull('tb_id')
        .groupBy('trap_id')
        .orderBy('id', 'desc')

        const polytunnelsQuery = knex('polytunnels')
        .select(
          'polytunnels.poly_id',
          'polytunnels.site_id',
          'polytunnels.poly_name',
          'polytunnels.created_at',
          'polytunnels.col_sync'
        )
        .innerJoin('sites', 'sites.site_id', 'polytunnels.site_id')
        .innerJoin('farms', 'farms.farm_id', 'sites.farm_id')
        .where('farms.comp_id', cmpId)
        .andWhere('polytunnels.col_sync','>', m_p)
        //.andWhere('polytunnels.col_sync', 3)
        .orderBy('polytunnels.col_sync', 'asc');
      

        const polyLineQuery = knex('poly_lines')
        .select(
          'poly_lines.line_id',
          'poly_lines.poly_id',
          'poly_lines.line_name',
          'poly_lines.created_at',
          'poly_lines.col_sync'
        )
        .innerJoin('polytunnels', 'polytunnels.poly_id', 'poly_lines.poly_id')
        .innerJoin('sites', 'sites.site_id', 'polytunnels.site_id')
        .innerJoin('farms', 'farms.farm_id', 'sites.farm_id')
        .where('farms.comp_id', cmpId)
        .andWhere('poly_lines.col_sync','>', m_L)
        .orderBy('poly_lines.col_sync', 'asc');
      

        const trapsQuery = knex('traps')
        .select(
          'traps.trap_id',
          'traps.line_id',
          'traps.trap_qr',
          'traps.latitude',
          'traps.longitude',
          'traps.col_order',
          'traps.created_at',
          'traps.col_sync',
          'traps.lure_date'
        )
        .innerJoin('poly_lines', 'poly_lines.line_id', 'traps.line_id')
        .innerJoin('polytunnels', 'polytunnels.poly_id', 'poly_lines.poly_id')
        .innerJoin('sites', 'sites.site_id', 'polytunnels.site_id')
        .innerJoin('farms', 'farms.farm_id', 'sites.farm_id')
        .where('farms.comp_id', cmpId)
        .andWhere('traps.col_sync','>', m_t) //m_t
        .orderBy('traps.col_sync', 'asc');
      

        const scanQuery = knex('scans')
        .select(
          'scans.scan_id',
          'scans.trap_id',
          'scans.insect_id',
          'scans.data',
          'scans.created_at',
          'scans.ismanager',
          'scans.col_sync'
        )
        .innerJoin('traps', 'traps.trap_id', 'scans.trap_id')
        .innerJoin('poly_lines', 'poly_lines.line_id', 'traps.line_id')
        .innerJoin('polytunnels', 'polytunnels.poly_id', 'poly_lines.poly_id')
        .innerJoin('sites', 'sites.site_id', 'polytunnels.site_id')
        .innerJoin('farms', 'farms.farm_id', 'sites.farm_id')
        .where('farms.comp_id', cmpId)
        .andWhere('scans.col_sync','>', m_s)  //m_s
        .orderBy('scans.col_sync', 'asc');
      

        

        // Execute both queries in parallel using Promise.all
        Promise.all([userQuery, farmQuery, mfaQ, accPermQuery, accInsQuery, subsQuery, sitesQuery, threQuery,archQuery, archQueryTap, polytunnelsQuery, polyLineQuery, trapsQuery, scanQuery])
            .then(async ([userResult, farmResult, mfaR, accPermR, accInsR, subResult, sitesResult, threResult, archResult, archRTrap, polytunnelsResult, polyLineResult, trapsResult, scanResult]) => {
                let data = [];
                let personPayload = null;
                let binaryData = null;
                let compressedData = null;

                await processAndSendData(ws, userResult, 1);
                await processAndSendData(ws, farmResult, 1.1);
                await processAndSendData(ws, mfaR, 1.15);
                await processAndSendData(ws, subResult, 1.2);
                await processAndSendData(ws, accPermR, 1.3);
                await processAndSendData(ws, accInsR, 1.4);



                await processAndSendData(ws, sitesResult, 2);
                await processAndSendData(ws, threResult, 2.1);
                await processAndSendData(ws, archResult, 2.9);
                await processAndSendData(ws, archRTrap, 2.95);
                await processAndSendData(ws, polytunnelsResult, 3);
                await processAndSendData(ws, polyLineResult, 4);
                await processAndSendData(ws, trapsResult, 5); //traps
                await processAndSendData(ws, scanResult, 6); //traps

               
                ws.close(); // Close WebSocket connection after processing
            })
            .catch((err) => {
                console.error('Error retrieving data:', err);
                ws.send('-1')
                //res.status(500).send('Error retrieving data');
            });
    } catch (err) {
        console.error('Error occurred:', err);
        ws.send('-1')
        //res.status(500).send('Server error');
    }

    ws.on('close', () => {
        console.log('WebSocket connection closed.');
    });

    ws.on('error', (error) => {
        console.error('WebSocket error:', error);
    });
});     


//Upgrade HTTP server to handle WebSocket connections
const server = app.listen(port, () => {
    console.log(`HTTP server listening on port ${port}`);
});

server.on('upgrade', (request, socket, head) => {
    wss.handleUpgrade(request, socket, head, (ws) => {
        wss.emit('connection', ws, request);
    });
});
