const { table } = require('console');
const Connection = require('../DB/Connection');
const { Sema } = require('async-sema');

let con = new Connection(); //in use v2

const fs = require('fs');
const { stringify } = require('querystring');

let polytb = "polytunnels";
let polyLinetb = "poly_lines";
let trapstb = "traps";
let scantb = "scans";
let archive = "archive";
let validtb = "valid";
let fp = "ap"
let fL = "aL"
let ft = "at"
let fsc = "as"




const table2 = new Map();
table2.set("p", polytb);
table2.set("L", polyLinetb);
table2.set("t", trapstb);
table2.set("s", scantb);
table2.set("a", archive);
//table2.set("f", validtb);
//const { default: PQueue } = await import('p-queue');

class AddData 
{
    constructor(con) {
        this.isDone = 0;  // Flag to track if polytunnels are inserted
        this.pPolyTunnels = []
        this.pLine = []
        this.pTrap = []  // Queue to store pending poly_lines
        this.pScan = []
        this.sema = new Sema(0); 
        this.semaTrap = new Sema(0);
        this.semaScan = new Sema(0);
        this.semaValid = new Sema(0);
        this.semaTest = new Sema(1);

        this.semaSq = new Sema(0)

        this.isPfinish = 0
        this.isLfinish = 0
        this.isTfinish = 0
        this.isSfinish = 0



    }
    wStart()
    {
        console.log("sequence start")
        this.semaSq.release();
    }
    async w1(i)
    {
        await this.semaSq.acquire()
        console.log("sequence ", i)
        this.semaSq.release()
    }



    dataS(gData)
    {
        const parsedData = gData.trim().split("\n").map(row => {
            const values = row.replace(/[{}]/g, "").split(",");
            return values
        });

        return parsedData
    }

    performData(values)
    {
        var newData = {}

        if(values[0] == "p")
        {
            newData = {
                poly_id: values[1],
                site_id: values[2],
                poly_name: values[3],
                created_at: values[4]
            }
        }
        else if(values[0] == "L")
        {
            newData = {
                line_id: values[1],
                poly_id: values[2],
                line_name: values[3],
                created_at: values[4]
            }
        }
        else if(values[0] == "t")
        {
            console.log("check trap ",values[3], " ", values[8])


            newData = {
                trap_id: values[1],
                line_id: values[2],
                trap_qr: values[3],
                latitude: values[4],
                longitude: values[5],
                created_at: values[6],
                col_order: values[7],
               // lure_date: values[8] === "0000-00-00 00:00:00"  || values[8] === "0" || values[8] === null || values[8] === 'null' ? null : values[8]
            }
            if( !(values[8] === "0000-00-00 00:00:00"  || values[8] === "0" || values[8] === null || values[8] === 'null') )
                {
                    newData.lure_date = values[8]
                }
            console.log("check trap after ", newData)

        }
        else if(values[0] == "s")
        {
            newData = {
                scan_id: values[1],
                trap_id: values[2],
                insect_id: values[3],
                data: values[4],
                created_at: values[5],
                ismanager: values[6]
            }
        }
        else if(values[0] == "a")
        {
            newData = {
                company_name: values[2],
                farm_name: values[3],
                site_name: values[4],
                poly_name: values[5],
                trap_id: values[6],
                trap_qr: values[7],
                insect_type: values[8],
                scan_data: values[9],
                created_at: values[10],
                comp_id: values[11],
                tb_name: values[12] && values[12] != "0" ? values[12] : null,
                tb_id: values[13] && values[13] != "0" ? values[13] : null  // Set null if "0"

            }
        }
        return newData
    }

    imp(data, ws)
    {
        console.log("dt b",data)
        for(let dt of data)
        {
           // console.log("dt ",dt)
           // console.log(dt.data[0]);
            if(dt.data[0] == polytb || dt.data[0] == polyLinetb || dt.data[0] == trapstb || dt.data[0] == scantb || dt.data[0] == archive)
            {
                continue
            }
           this.importData(dt, ws);
        }
        data = []
       // data2 = []
    }
      importData(sData, ws)
    {
        var tb = ""
        const sections = []
        console.log("to perform ", sData)
        for(let row of sData.data)
        {
            let values = row.replace(/[{}]/g, "").trim().split(/,\s*/); 
            if(table2.has(values[0])) {
                sections.push(this.performData(values));
                tb = table2.get(values[0]);
//                this.pendingPolyLines.push(this.performData(values))
            }
            else if(values[0] == fp || values[0] == fL || values[0] == ft || values[0] == fsc)
             { tb = values[0]}
        }
        if(tb != "")
        {
            if(tb == archive)
            {
                this.deleteData(tb, sections, ws)
            }
            else
            {
                this.addData2(tb, sections, ws)

            }

        }
    }

    async deleteData(table, data, ws) {
        if (table === archive) {
            try {
                await con.kp().transaction(async (trx) => {
                    //console.log("sec ", data);
    
                    // Insert data into archive table
                    await trx(archive).insert(data);
    
                    // Delete from traps table using trap_id
                    await trx(trapstb)
                        .whereIn("trap_id", data.map(d => d.trap_id))
                        .del();
    
                    // Check if any `tb_name` is "line" before deleting from the other table
                    if (data.some(d => d.tb_name === polyLinetb)) {
                        await trx(polyLinetb)
                            .whereIn("line_id", data.map(d => d.tb_id)) // Changed `tbid` to `tb_id`
                            .del();
                    }
                    if (data.some(d => d.tb_name === polytb)) {
                        await trx(polytb)
                            .whereIn("poly_id", data.map(d => d.tb_id)) // Changed `tbid` to `tb_id`
                            .del();
                    }
    
                    ws.send(JSON.stringify("✅ Inserted and deleted successfully."));
                    console.log("✅ Inserted and deleted successfully.", table);
                })//final of transaction
                .catch((err) => console.log("deletion err in add data, ", err))
            } catch (err) {
                console.log("❌ Transaction failed:", err);
                ws.send(JSON.stringify("❌ Transaction failed: " + err.message));
            }
        }
    }
    

    async addPolyTunnel(data, ws)
    {
        
         console.log("start ", data.length, " pending ", this.pPolyTunnels.length)
        if(data.length <= 0) {this.sema.release(); return;}
        this.pPolyTunnels.push(data)
       // console.log("added ", data, " now ", this.pPolyTunnels.flat())
       // for(let dd of this.pPolyTunnels.flat()) { console.log("add2 ", dd.data[0]) }
        this.imp(this.pPolyTunnels.flat(), ws);
        //this.sema.release()
    }
    async addLine(data, ws)
    {
        this.pLine.push(data)
        console.log("Waiting line", data.length, " pending ",this.pLine.length)
        //if(this.pPolyTunnels.length > 0) { await this.sema.acquire() }
        if(this.isPfinish <= 0) {
            await this.sema.acquire() 

        }
      //  await this.sema.acquire() 
        this.imp(this.pLine.flat(), ws);
        console.log("line is done")
        //this.semaTrap.release()
    }
    async addTrap(data, ws)
    {
        console.log("waiting trap ", data.length);
        this.pTrap.push(data)
       // if(this.pLine.length > 0) { await this.semaTrap.acquire(); }
       if(this.isLfinish <= 0)
       {
        await this.semaTrap.acquire(); 
       }
       this.imp(this.pTrap.flat(), ws);
       // this.semaScan.release();
    }
    async addScan(data, ws)
    {
        console.log("Waiting scan", data.length, " ist ", this.isTfinish)
        this.pScan.push(data)


   //  console.log("scan pending ", data)

        //if(this.pTrap.length > 0) { await this.semaScan.acquire(); }
        if(this.isTfinish <= 0)
        {
            await this.semaScan.acquire();
        }
    //    var tempS = this.pScan.shift()
        //console.log("scan will ", this.pScan.shift())
        this.imp(this.pScan.flat(), ws);
        console.log("scan will begin ", this.isTfinish)
      //  console.log("scan pending 2 ", this.pScan.flat())


    }
    async sendValid(ws)
    {
       // if(this.pScan.length > 0) { await this.semaValid.acquire(); }
        await this.semaValid.acquire();
        console.log("scan is done");

    }
     counter = 0
     async addData2(table, data, ws)
    {
        await this.semaTest.acquire()

       // console.log("db ", table, data)
       console.log("in process pre.", table)
     //  console.log("in process.", table, " dt " + JSON.stringify(data, null, 2))

//       var pre = "in process. "+ table + " dt " + JSON.stringify(data2, null, 2)


        if(table == fp) {
            // fs.appendFile('log.txt', "scan data poly is done" + "\n", (err) => {
            //     if (err) {
            //         console.error('Error writing to file:', err);
            //     } else {
            //        // console.log('Log saved.');
            //     }
            // });
            this.sema.release()
            this.semaTest.release()
            this.isPfinish = 1
            return
        }
        else if(table == fL)
        {
            this.semaTrap.release()
            this.semaTest.release()
            this.isLfinish = 1
            return
        }
        else if(table == ft)
        {

            this.semaScan.release()
            this.semaTest.release()
            this.isTfinish = 1

            return
        }
        else if (table == fsc)
        {
            this.pScan.shift()
          //  console.log("in process.", table, " data ", data)
          console.log("p tunnel ", this.pPolyTunnels.flat())
          console.log("p line ", this.pLine.flat())
          console.log("p trap ", this.pTrap.flat())
          console.log("p scan ", this.pScan.flat())

            console.log("scan data final is here do smth");

            ws.send(JSON.stringify("done"));
    
            this.semaValid.release()
            this.semaTest.release()

            this.isSfinish = 1
            return

        }
      
        var columnsToUpdate = ['col_sync']; // Add all columns you want to update
        if(table == polytb)
        {
            columnsToUpdate.push('poly_name');
        }
        else  if(table == trapstb)
        {
            columnsToUpdate.push('created_at');
           // columnsToUpdate.push('lure_date');
        }
        let updateQuery = columnsToUpdate
            .map(col => `${col} = VALUES(${col})`)
            .join(", ");
        
            if (table == trapstb) {
                updateQuery += `, lure_date = IF(VALUES(lure_date) IS NULL, lure_date, VALUES(lure_date))`;
            }
        let query = con.kp()
            .insert(data)
            .into(table)
            .toString() + ` ON DUPLICATE KEY UPDATE ${updateQuery};`;  //in use

            await con.kp().transaction(async trx => {
                try {
                    await trx.raw("SET FOREIGN_KEY_CHECKS=0;");
            
                    await trx.raw(query);
            
                  //  ws.send(JSON.stringify("✅ Inserted or updated successfully." + table + " sec " + this.counter++));
                    console.log("✅ Inserted or updated successfully.", table);
                   // console.log("✅ Inserted or updated successfully.", table, " dt " + JSON.stringify(data, null, 2));
            
                   // const logM = "✅Inserted or updated successfully. " + table + " dt " + JSON.stringify(data, null, 2);

                   if (table == polytb) this.pPolyTunnels.shift() //this.pPolyTunnels = [];
                    else if (table == polyLinetb) this.pLine.shift() //this.pLine = [];
                    else if (table == trapstb) this.pTrap.shift() //this.pTrap = [];
                    else if (table == scantb) this.pScan.shift() //this.pScan = [];
            
                    await trx.raw("SET FOREIGN_KEY_CHECKS=1;");
                    await trx.commit();  // Explicit commit to be sure

                    data = []

                } catch (err) {
                    console.log("❌ Error:", err);
                    await trx.rollback(); // Rollback ensures database integrity
                    //throw err; // Automatically rolls back, commentted in 8/29/25 which cause stopping the NodeJS
                }
            }).catch(err => {
                console.log("trx error", err);
            }).finally(() => {
                this.semaTest.release();
            });
            


            //await this.semaTest.acquire()
            //console.log("scan is done 2")
    }
  
}

module.exports = AddData;

//I use knex to deal with mysql