/* 
 * Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
 * Click nbfs://nbhost/SystemFileSystem/Templates/Other/javascript.js to edit this template
 */


const express = require('express');
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const xlsx = require('xlsx');
const pool = require('../db');
const router = express.Router();
const upload = multer({dest: 'uploads/'});

function parseYesNo(value) {
  const val = (value || '').toString().trim();
  if (val === 'હા') return 1;
  if (val === 'ના') return 0;
  return val;
}

router.post('/import', upload.single('excel'), async function (req, res) {
    const filePath = path.join(__dirname, '..', req.file.path);
    try {
        var workbook = xlsx.readFile(filePath, {
            raw: true,
            cellDates: true
        });
        var sheet = workbook.Sheets[workbook.SheetNames[0]];
        var data = xlsx.utils.sheet_to_json(sheet, {defval: ''});
        var insertCount = 0;
        var updateCount = 0;
        console.log("data = " + JSON.stringify(data, null, 2));
        let lastInsertId;
        let last_UpdateId;
        for (var i = 0; i < data.length; i++) {
            var row = data[i];
            var villageName = row['Village Name'];
            const name = row['Name of the family members'];
            if (typeof name === 'string' && name.trim() !== '') {
//            if (row['Name of the family members'] != "") {

                var familyHead = row['Name of the family members'];
                var faliyaName = row['Habitation Name'];
                var villageName = row['Village Name'];
                var talukaName = row['Taluka Name'];
                var districtName = row['District Name'];
                console.log("familyHead " + familyHead);
                if (row['Taluka Name'] == "") {

                    const Village_name = row['Village Name'];
                    if (typeof Village_name === 'string' && Village_name.trim() !== '') {
                        console.log("Member Last Inserted Family ID:", lastInsertId);
                        console.log("Member Last Updated Family ID:", last_UpdateId);
                        let famliy_id;
                        if (!lastInsertId) {
                            console.log("lastInsertId is empty, undefined, null, 0, or false");
                            if (!last_UpdateId) {
                                console.log("lastInsertId is empty, undefined, null, 0, or false");
                            } else {
                                famliy_id = last_UpdateId;
                            }
                        } else {
                            console.log("Valid lastInsertId:", lastInsertId);
                            famliy_id = lastInsertId;
                        }

                        console.log("Family ID:", famliy_id);
                        var [existingMemberRows] = await pool.query('SELECT * FROM tbl_family_members WHERE name = ? AND family_id = ?', [familyHead, famliy_id]);
//                        var familyData = {                            
//                            has_aadhaar_card: row['Aadhaar Card'],
//                            aadhaar_no: row['__EMPTY_10'],
//                            has_pmjay_card: row['PM JAY Card'],
//                            pmjay_card_no: row['__EMPTY_11'],
//                            has_jandhan_account: row['Jan Dhan Bank Account'],
//                            has_caste_certificate: row['Caste Certificate']
//                        };

                        var familyData = {
//                                has_aadhaar_card: row['Aadhaar Card']?.trim() === 'હા' ? 1 : 0,
//                                aadhaar_no: row['__EMPTY_10'],
//                                has_pmjay_card: row['PM JAY Card']?.trim() === 'હા' ? 1 : 0,
//                                pmjay_card_no: row['__EMPTY_11'],
//                                has_jandhan_account: row['Jan Dhan Bank Account']?.trim() === 'હા' ? 1 : 0,
//                                has_caste_certificate: row['Caste Certificate']?.trim() === 'હા' ? 1 : 0

                            has_aadhaar_card: parseYesNo(row['Aadhaar Card']),
                            aadhaar_no: row['__EMPTY_10'] || '',
                            has_pmjay_card: parseYesNo(row['PM JAY Card']),
                            pmjay_card_no: row['__EMPTY_11'] || '',
                            has_jandhan_account: parseYesNo(row['Jan Dhan Bank Account']),
                            has_caste_certificate: parseYesNo(row['Caste Certificate'])
                        };
                        console.log(familyData);
                        if (existingMemberRows.length === 0) {
                            await pool.query(
                                    'INSERT INTO tbl_family_members (name, family_id, ' +
                                    Object.keys(familyData).join(', ') +
                                    ') VALUES (?, ?, ' + Object.keys(familyData).map(() => '?').join(', ') + ')',
                                    [familyHead, famliy_id].concat(Object.values(familyData))
                                    );
                            insertCount++;
                        } else {
                            var existing = existingMemberRows[0];
                            var updates = [];
                            var values = [];
                            for (var key in familyData) {
                                if (familyData[key] != existing[key]) {
                                    updates.push(key + ' = ?');
                                    values.push(familyData[key]);
                                }
                            }

                            if (updates.length > 0) {
                                values.push(existing.member_id);
                                await pool.query('UPDATE tbl_family_members SET ' + updates.join(', ') + ' WHERE member_id = ?', values);
                                updateCount++;
                            }
                        }

                    } else {

                    }
                } else {

                    // district
                    var [distRows] = await pool.query('SELECT district_id FROM tbl_district WHERE name = ?', [districtName]);
                    var district_id = distRows.length ? distRows[0].district_id :
                            (await pool.query('INSERT INTO tbl_district (name) VALUES (?)', [districtName]))[0].insertId;
                    // taluka
                    var [talukaRows] = await pool.query('SELECT taluka_id FROM tbl_taluka WHERE name = ? AND district_id = ?', [talukaName, district_id]);
                    var taluka_id = talukaRows.length ? talukaRows[0].taluka_id :
                            (await pool.query('INSERT INTO tbl_taluka (name, district_id) VALUES (?, ?)', [talukaName, district_id]))[0].insertId;
                    // village
                    var [villageRows] = await pool.query('SELECT village_id FROM tbl_village WHERE name = ? AND taluka_id = ?', [villageName, taluka_id]);
                    var village_id = villageRows.length ? villageRows[0].village_id :
                            (await pool.query(
                                    'INSERT INTO tbl_village (name, revenue_village_name, gram_panchayat_name, taluka_id) VALUES (?, ?, ?, ?)',
                                    [villageName, row['Revenue Village Name'], row['Gram Panchayat Name'], taluka_id]
                                    ))[0].insertId;
                    // faliya
                    var [faliyaRows] = await pool.query('SELECT faliya_id FROM tbl_faliya WHERE name = ? AND village_id = ?', [faliyaName, village_id]);
                    var faliya_id = faliyaRows.length ? faliyaRows[0].faliya_id :
                            (await pool.query('INSERT INTO tbl_faliya (name, village_id) VALUES (?, ?)', [faliyaName, village_id]))[0].insertId;
                    // check family
                    var [existingRows] = await pool.query('SELECT * FROM tbl_families WHERE head_name = ? AND faliya_id = ?', [familyHead, faliya_id]);
                    var familyData = {
                        total_hhs: row['Total HHs'],
                        total_population: row['Total Population'],
                        pvtgs_hhs: row['PVTGs HHs'],
                        pvtgs_population: row['PVTGs Population'],
                        female_members: row['Number of family members'],
                        male_members: row['__EMPTY'],
                        children: row['__EMPTY_1'],
                        total_members: row['__EMPTY_2'],
//                        house_kachcha: row['DETAILS OF ACCOMMODATION']?.trim() === 'હા' ? 1 : 0,
//                        house_pakka: row['__EMPTY_3']?.trim() === 'હા' ? 1 : 0,
//                        land_ownership_type: row['Home land ownership'],
//                        water_source: row['source of drinking water'],
//                        electricity_available: row['Electricity facility']?.trim() === 'હા' ? 1 : 0,
//                        electricity_customer_no: row['__EMPTY_4'],
//                        toilet: row['Toilet facility']?.trim() === 'હા' ? 1 : 0,
//                        ration_card_yes: row['Ration card details']?.trim() === 'હા' ? 1 : 0,
//                        ration_card_no: row['__EMPTY_5'],
//                        ration_card_type: row['__EMPTY_6'],
//                        mgnrega_job_card_yes: row['MGNREGA Job Card Details']?.trim() === 'હા' ? 1 : 0,
//                        mgnrega_card_no: row['__EMPTY_7'],
//                        employment_details: row['Employment Details'],
//                        own_land: row['Own land?\r\nYes/ No']?.trim() === 'હા' ? 1 : 0,
//                        fra_forest_land_detail: row['F.R.A. Detail of forest land under']?.trim() === 'હા' ? 1 : 0,
//                        fra_adhaesh_pattra: row['__EMPTY_8']?.trim() === 'હા' ? 1 : 0,
//                        fra_adhikart_pattra: row['__EMPTY_9']?.trim() === 'હા' ? 1 : 0,
//                        has_aadhaar_card: row['Aadhaar Card']?.trim() === 'હા' ? 1 : 0,
//                        aadhaar_no: row['__EMPTY_10'],
//                        has_pmjay_card: row['PM JAY Card']?.trim() === 'હા' ? 1 : 0,
//                        pmjay_card_no: row['__EMPTY_11'],
//                        has_jandhan_account: row['Jan Dhan Bank Account']?.trim() === 'હા' ? 1 : 0,
//                        has_caste_certificate: row['Caste Certificate']?.trim() === 'હા' ? 1 : 0,
//                        has_ujjwala_yojana: row['Ujjwala Yojana']?.trim() === 'હા' ? 1 : 0,
//                        has_pm_kisan_yojana: row['PM KISAN Samman Nidhi Scheme']?.trim() === 'હા' ? 1 : 0,
//                        has_dairy_cattle: row['Are dairy cattle?']?.trim() === 'હા' ? 1 : 0,
//                        need_dairy_cow: row['Need a dairy cow?']?.trim() === 'હા' ? 1 : 0

                        house_kachcha: parseYesNo(row['DETAILS OF ACCOMMODATION']),
                        house_pakka: parseYesNo(row['__EMPTY_3']),
                        land_ownership_type: row['Home land ownership'],
                        water_source: row['source of drinking water'],
                        electricity_available: parseYesNo(row['Electricity facility']),
                        electricity_customer_no: row['__EMPTY_4'],
                        toilet: parseYesNo(row['Toilet facility']),
                        ration_card_yes: parseYesNo(row['Ration card details']),
                        ration_card_no: row['__EMPTY_5'],
                        ration_card_type: row['__EMPTY_6'],
                        mgnrega_job_card_yes: parseYesNo(row['MGNREGA Job Card Details']),
                        mgnrega_card_no: row['__EMPTY_7'],
                        employment_details: row['Employment Details'],
                        own_land: parseYesNo(row['Own land?\r\nYes/ No']),
                        fra_forest_land_detail: parseYesNo(row['F.R.A. Detail of forest land under']),
                        fra_adhaesh_pattra: parseYesNo(row['__EMPTY_8']),
                        fra_adhikart_pattra: parseYesNo(row['__EMPTY_9']),
                        has_aadhaar_card: parseYesNo(row['Aadhaar Card']),
                        aadhaar_no: row['__EMPTY_10'],
                        has_pmjay_card: parseYesNo(row['PM JAY Card']),
                        pmjay_card_no: row['__EMPTY_11'],
                        has_jandhan_account: parseYesNo(row['Jan Dhan Bank Account']),
                        has_caste_certificate: parseYesNo(row['Caste Certificate']),
                        has_ujjwala_yojana: parseYesNo(row['Ujjwala Yojana']),
                        has_pm_kisan_yojana: parseYesNo(row['PM KISAN Samman Nidhi Scheme']),
                        has_dairy_cattle: parseYesNo(row['Are dairy cattle?']),
                        need_dairy_cow: parseYesNo(row['Need a dairy cow?'])

                    };
                    console.log('familyData ',familyData);
                    if (existingRows.length === 0) {
//                        await pool.query(
//                                'INSERT INTO tbl_families (head_name, faliya_id, ' +
//                                Object.keys(familyData).join(', ') +
//                                ') VALUES (?, ?, ' + Object.keys(familyData).map(() => '?').join(', ') + ')',
//                                [familyHead, faliya_id].concat(Object.values(familyData))
//                                );

                        const [result] = await pool.query(
                                'INSERT INTO tbl_families (head_name, faliya_id, ' +
                                Object.keys(familyData).join(', ') +
                                ') VALUES (?, ?, ' + Object.keys(familyData).map(() => '?').join(', ') + ')',
                                [familyHead, faliya_id, ...Object.values(familyData)]
                                );
// Get last inserted ID
                        lastInsertId = result.insertId;
                        console.log("Last Inserted Family ID:", lastInsertId);
                        insertCount++;
                    } else {
                        var existing = existingRows[0];
                        var updates = [];
                        var values = [];
                        for (var key in familyData) {
                            if (familyData[key] != existing[key]) {
                                updates.push(key + ' = ?');
                                values.push(familyData[key]);
                            }
                        }

                        last_UpdateId = existing.family_id;
                        if (updates.length > 0) {
                            values.push(existing.family_id);
                            await pool.query('UPDATE tbl_families SET ' + updates.join(', ') + ' WHERE family_id = ?', values);
                            updateCount++;
                        }
                    }
                }
            } else {
                console.log("Invalid or empty name");
            }
        }

        fs.unlinkSync(filePath);
//        res.render('import', {message: '✅ Inserted: ' + insertCount + ', 🔄 Updated: ' + updateCount});
        res.json({success: true, message: `✅ Inserted: ${insertCount}, 🔄 Updated: ${updateCount}`});
    } catch (err) {
        console.error(err);
//        res.render('import', {message: '❌ Error processing file'});
        res.status(500).json({success: false, message: '❌ Error processing file'});
    }
});
module.exports = router;
