const { sequelize, Entities , Financials , Statementsmaster  , Sectionsmaster , Subsectionsmaster , Itemsmaster , Brokenperiods , Entityfinyear} = require('../models')
const { logMsg } = require('../services/logger.js')
const reader = require('xlsx');
var path = require('path');
const fs = require('fs');

//Master data List Api
exports.ImportExcelData = async (req, res , filename) =>
{
	try 
	{
        const losid = req.body.los_id; 
        console.log(losid);
        var filePath = path.resolve('storage/uploads/'+filename);

        const file = reader.readFile(filePath)         
                
        var ExcelDataConstruct = new Promise(async(resolve, reject) => 
        {
            let data = [];
            const sheets = file.SheetNames;
            for(let i = 0; i < sheets.length; i++)
            {
               
                const sheetName = req.body.cin;
                const SheetLevelData = reader.utils.sheet_to_json(file.Sheets[file.SheetNames[i]]);
                const resultArray = [];
                if(SheetLevelData.length != 0)
                {
                    if(sheetName.toUpperCase() == 'PROPRIETORSHIP'){
                        where_Condition =   { losid : losid , entity_type : 'PROPRIETORSHIP', probe42_data_status: 2 }
                    }else if(sheetName.toUpperCase() == 'PARTNERSHIP'){
                        where_Condition =   { losid : losid , entity_type : 'PARTNERSHIP', probe42_data_status: 2 }
                    }else{
                        where_Condition =   { losid : losid , cin : sheetName , probe42_data_status: 2 }
                    }
                   
                EntityData = await Entities.findOne({raw:true, where: where_Condition }).catch(err => {});
                if(EntityData)
                {
                    for (const obj of SheetLevelData)
                    {
                        if(obj.hasOwnProperty('item_id'))
                        {
                            const itemid = obj.item_id;
                            delete obj.item_id;
                            delete obj.itemtext;
                        
                            for (const key in obj) 
                            {
                                const yearValue = obj[key];
                                const newObj = { entity_id:EntityData.id , itemid , fy:key , value:yearValue };
                                resultArray.push(newObj);
                            }
                        }else{ logMsg.info("Item id Not Exist For losid = "+losid+" CIN no = "+sheetName); }
                    }
                }else{
                    logMsg.info("Cin Not Exist For losid = "+losid+" CIN no = "+sheetName);
                }
                
                }else{
                    logMsg.info("Empty Excel Uploaded For losid = "+losid);
                }
                

                data.push(resultArray);
                if(i+1 == sheets.length){ resolve(data);}
            }//end of for loop // this loop for sheet level loop 
        });


        ExcelDataConstruct.then(async(DataArray)=>
        { 
            if(DataArray.length > 0)
            {
                // Using the spread operator
                const mergedArray = [].concat(...DataArray);
                if(mergedArray.length > 0){
                    var FinancialsDataUpdate = new Promise(async(resolve, reject) => 
                    {
                        mergedArray.forEach(async(value, key , array)=>{ 
                            checkIfExist = await Financials.findOne({ raw:true, where: {fy:value.fy,entity_id:value.entity_id,itemid:value.itemid,is_active:1} }).catch(err=>{  logMsg.info(err); });  
                            if(checkIfExist)
                            {
                                await Financials.update({ "value":value.value,"sub_value":value.value},{ where: { id:checkIfExist.id }}).catch(err=>{  logMsg.info(err); });
                            }
                            else
                            {
                                logMsg.info("Value not updated for - EntityId : "+value.entity_id+" , FY : "+value.fy+",ItemId : "+value.itemid);
                            }
                            
                            if (key === array.length -1) {   resolve(DataArray);  }
                        });
                    });
                    FinancialsDataUpdate.then(async(data)=>
                    {   
                        const filteredData = data.filter(subArray => subArray.length > 0);
                        filteredData.forEach(async(value, key , array)=>{
                                 var entity_id = value[0]['entity_id'];
                                 await Entities.update( {manual_data_status:1},{where:{id:entity_id}} ).then(data=>{}).catch(err=>{  logMsg.info(err); });  
                                }); 
                        res.send({'status':200,'message':"success",'data':"No data"});
                    });
                   
                }else{
                    res.send({'status':404,'message':"Invalid Data or Empty Excel",'data':"No data"});
                }
  
            }else{
                res.send({'status':404,'message':"Invalid Data or Empty Excel",'data':"No data"});
            }
            
        });



        fs.unlink(filePath, (error) => {
                if (error) {
                console.error('Error deleting the file:', error);
                } else {
                console.log('File has been deleted successfully.');
                }
        });


	
	} catch(err) {
         res.send({'status':404,message: err.message || "Some error occurred while retrieving BankDetails." ,'data': "No data" }); 
        } //end of try catch
          
};