import { NextFunction, Request, Response } from 'express';
import logging from '../../config/logging';
import config from '../../config/config';
const mysql = require('mysql');
const util = require('util');
import header from '../../middleware/apiHeader';
import { ResultSuccess } from '../../classes/response/resultsuccess';
import { ResultError } from '../../classes/response/resulterror';

var connection = mysql.createConnection({
    host: config.mysql.host,
    user: config.mysql.user,
    password: config.mysql.pass,
    database: config.mysql.database
});

const query = util.promisify(connection.query).bind(connection);
const beginTransaction = util.promisify(connection.beginTransaction).bind(connection);
const commit = util.promisify(connection.commit).bind(connection);
const rollback = util.promisify(connection.rollback).bind(connection);

const NAMESPACE = 'City';

const getCityByPincode = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Get City By Pincode');
        var requiredFields = ['pincode'];
        var validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let pincode = req.body.pincode;
                let sql = `CALL getCityByPincode('` + pincode + `')`;
                let result = await query(sql);
                if (result && result.length > 0) {
                    let successResult = new ResultSuccess(200, true, 'Get City By Pincode', result[0], result[0].length);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "Error While Getting Cities", result, '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", authorizationResult, '');
                next(errorResult);
            }
        } else {
            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error) {
        let errorResult = new ResultError(500, true, 'personalLoan.getIncompletePersonalLoanDetail()', error, '');
        next(errorResult);
    }
};

const getCountries = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Get Countries');
        var requiredFields = [''];
        var validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sqlCountQuery = `SELECT COUNT(id) as totalCount FROM countries`;
                let resultCount = await query(sqlCountQuery);
                let sql = `SELECT * FROM countries`;
                if (req.body.startIndex >= 0 && req.body.fetchRecords > 0) {
                    sql += ` limit ` + req.body.fetchRecords + ` OFFSET ` + req.body.startIndex;
                }
                let result = await query(sql);
                if (result) {
                    let successResult = new ResultSuccess(200, true, 'Get Countries Successfully', result, resultCount[0].totalCount);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "Error While Getting Cities", result, '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", authorizationResult, '');
                next(errorResult);
            }
        } else {
            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error) {
        let errorResult = new ResultError(500, true, 'cities.getCountries()', error, '');
        next(errorResult);
    }
};

const getStates = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Get States');
        var requiredFields = [''];
        var validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sqlCountQuery = `SELECT COUNT(s.id) as totalRecords FROM states s INNER JOIN countries c ON c.id = s.countryId`;
                let sql = `SELECT s.*, c.name as countryName FROM states s INNER JOIN countries c ON c.id = s.countryId`;
                if (req.body.countryId) {
                    sqlCountQuery += ` WHERE countryId = ` + req.body.countryId;
                    sql += ` WHERE countryId = ` + req.body.countryId;
                }
                if (req.body.startIndex >= 0 && req.body.fetchRecords > 0) {
                    sql += ` limit ` + req.body.fetchRecords + ` OFFSET ` + req.body.startIndex;
                }
                let result = await query(sql);
                let resultCount = await query(sqlCountQuery);
                if (result) {
                    let successResult = new ResultSuccess(200, true, 'Get States Successfully', result, resultCount[0].totalRecords);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "Error While Getting Cities", result, '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", authorizationResult, '');
                next(errorResult);
            }
        } else {
            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error) {
        let errorResult = new ResultError(500, true, 'cities.getStates()', error, '');
        next(errorResult);
    }
};

const getDistricts = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Get Districts');
        var requiredFields = [''];
        var validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let countSql = `SELECT COUNT(d.id) as totalRecords FROM districts d INNER JOIN states s ON s.id = d.stateId INNER JOIN countries ct ON ct.id = s.countryId`;

                let sql = `SELECT d.*, ct.id as countryId, s.name as stateName, ct.name as countryName  FROM districts d INNER JOIN states s ON s.id = d.stateId INNER JOIN countries ct ON ct.id = s.countryId`;
                if (req.body.stateId) {
                    countSql += ` WHERE stateId = ` + req.body.stateId;
                    sql += ` WHERE stateId = ` + req.body.stateId;
                }
                if (req.body.startIndex >= 0 && req.body.fetchRecords > 0) {
                    sql += ` limit ` + req.body.fetchRecords + ` OFFSET ` + req.body.startIndex;
                }
                let result = await query(sql);
                let resultCount = await query(countSql);
                if (result) {
                    let successResult = new ResultSuccess(200, true, 'Get districts Successfully', result, resultCount[0].totalRecords);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "Error While Getting Cities", result, '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", authorizationResult, '');
                next(errorResult);
            }
        } else {
            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error) {
        let errorResult = new ResultError(500, true, 'cities.getDistricts()', error, '');
        next(errorResult);
    }
};

const getCities = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Get Cities');
        var requiredFields = [''];
        var validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sqlCountQuery = `SELECT COUNT(c.id) as totalRecords FROM cities c
                INNER JOIN districts d ON d.id = c.districtId 
                INNER JOIN states s ON s.id = d.stateId 
                INNER JOIN countries ct ON ct.id = s.countryId`;
                let sql = `SELECT c.*, ct.id as countryId, s.id as stateId, d.name as districtName, s.name as stateName, ct.name as countryName 
                FROM cities c 
                INNER JOIN districts d ON d.id = c.districtId 
                INNER JOIN states s ON s.id = d.stateId 
                INNER JOIN countries ct ON ct.id = s.countryId`;
                if (req.body.districtId) {
                    sqlCountQuery += ` WHERE districtId = ` + req.body.districtId;
                    sql += ` WHERE districtId = ` + req.body.districtId;
                }
                if (req.body.startIndex >= 0 && req.body.fetchRecords > 0) {
                    sql += ` limit ` + req.body.fetchRecords + ` OFFSET ` + req.body.startIndex;
                }
                let resultCount = await query(sqlCountQuery);
                let result = await query(sql);
                if (result) {
                    let successResult = new ResultSuccess(200, true, 'Get cities Successfully', result, resultCount[0].totalRecords);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "Error While Getting Cities", result, '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", authorizationResult, '');
                next(errorResult);
            }
        } else {
            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error) {
        let errorResult = new ResultError(500, true, 'cities.getCities()', error, '');
        next(errorResult);
    }
};

const insertCountry = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Insert Countries');
        let requiredFields = ['name', 'isoCode', 'isoCode3'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `INSERT INTO countries(name, isoCode, isoCode3, dialCode) VALUES('` + req.body.name + `','` + req.body.isoCode + `','` + req.body.isoCode3 + `','` + req.body.dialCode + `')`;
                let result = await query(sql);
                if (result && result.insertId > 0) {
                    let successResult = new ResultSuccess(200, true, 'Insert Country Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.insertCountry() Error", new Error('Error While Inserting Country'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {
            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.insertCountry() Exception', error, '');
        next(errorResult);
    }
}

const updateCountry = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Update Countries');
        let requiredFields = ['id', 'name', 'isoCode', 'isoCode3'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `UPDATE countries SET name = '` + req.body.name + `', isoCode = '` + req.body.isoCode + `', isoCode3 = '` + req.body.isoCode3 + `', dialCode = '` + req.body.dialCode + `'
                 WHERE id = `+ req.body.id;
                let result = await query(sql);
                if (result && result.affectedRows > 0) {
                    let successResult = new ResultSuccess(200, true, 'Update Country Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.updateCountry() Error", new Error('Error While Updating Country'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {
            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.updateCountry() Exception', error, '');
        next(errorResult);
    }
}

const activeInactiveCountry = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Update Countries');
        let requiredFields = ['id'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `UPDATE countries SET isActive = !isActive WHERE id = ` + req.body.id;
                let result = await query(sql);
                if (result && result.affectedRows > 0) {
                    let successResult = new ResultSuccess(200, true, 'Update Country Status Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.activeInactiveCountry() Error", new Error('Error While Updating Country Status'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.activeInactiveCountry() Exception', error, '');
        next(errorResult);
    }
}

const insertState = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Insert State');
        let requiredFields = ['name', 'countryId'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `INSERT INTO states(name, countryId, code) VALUES('` + req.body.name + `',` + req.body.countryId + `,` + (req.body.code ? "'" + req.body.code + "'" : null) + `)`;
                let result = await query(sql);
                if (result && result.insertId > 0) {
                    let successResult = new ResultSuccess(200, true, 'Insert State Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.insertState() Error", new Error('Error While Inserting State'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.insertState() Exception', error, '');
        next(errorResult);
    }
}

const updateState = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Update State');
        let requiredFields = ['id', 'name', 'countryId'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `UPDATE states SET name = '` + req.body.name + `', code = ` + (req.body.code ? `'` + req.body.code + `'` : null) + `, countryId = ` + req.body.countryId + ` WHERE id = ` + req.body.id;
                let result = await query(sql);
                if (result && result.affectedRows > 0) {
                    let successResult = new ResultSuccess(200, true, 'Update State Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.updateState() Error", new Error('Error While Updating State'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.updateState() Exception', error, '');
        next(errorResult);
    }
}

const activeInactiveState = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Update State');
        let requiredFields = ['id'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `UPDATE states SET isActive = !isActive WHERE id = ` + req.body.id;
                let result = await query(sql);
                if (result && result.affectedRows > 0) {
                    let successResult = new ResultSuccess(200, true, 'Update State Status Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.activeInactiveState() Error", new Error('Error While Updating State Status'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.activeInactiveState() Exception', error, '');
        next(errorResult);
    }
}

const insertDistrict = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Insert District');
        let requiredFields = ['name', 'stateId'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `INSERT INTO districts(name, stateId) VALUES('` + req.body.name + `',` + req.body.stateId + `)`;
                let result = await query(sql);
                if (result && result.insertId > 0) {
                    let successResult = new ResultSuccess(200, true, 'Insert District Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.insertDistrict() Error", new Error('Error While Inserting District'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.insertDistrict() Exception', error, '');
        next(errorResult);
    }
}

const updateDistrict = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Update District');
        let requiredFields = ['id', 'name', 'stateId'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `UPDATE districts SET name = '` + req.body.name + `', stateId = ` + req.body.stateId + ` WHERE id = ` + req.body.id;
                let result = await query(sql);
                if (result && result.affectedRows > 0) {
                    let successResult = new ResultSuccess(200, true, 'Update District Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.updateDistrict() Error", new Error('Error While Updating District'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.updateDistrict() Exception', error, '');
        next(errorResult);
    }
}

const activeInactiveDistrict = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Update District');
        let requiredFields = ['id'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `UPDATE districts SET isActive = !isActive WHERE id = ` + req.body.id;
                let result = await query(sql);
                if (result && result.affectedRows > 0) {
                    let successResult = new ResultSuccess(200, true, 'Update District Status Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.activeInactiveDistrict() Error", new Error('Error While Updating District Status'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.activeInactiveDistrict() Exception', error, '');
        next(errorResult);
    }
}

const insertCity = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Insert City');
        let requiredFields = ['name', 'districtId', 'pincode'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `INSERT INTO cities(name, districtId,pincode) VALUES('` + req.body.name + `',` + req.body.districtId + `, '` + req.body.pincode + `')`;
                let result = await query(sql);
                if (result && result.insertId > 0) {
                    let successResult = new ResultSuccess(200, true, 'Insert City Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.insertCity() Error", new Error('Error While Inserting City'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.insertCity() Exception', error, '');
        next(errorResult);
    }
}

const updateCity = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Update City');
        let requiredFields = ['id', 'name', 'pincode', 'districtId'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `UPDATE cities SET name = '` + req.body.name + `', pincode = '` + req.body.pincode + `', districtId = ` + req.body.districtId + ` WHERE id = ` + req.body.id;
                let result = await query(sql);
                if (result && result.affectedRows > 0) {
                    let successResult = new ResultSuccess(200, true, 'Update City Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.updateCity() Error", new Error('Error While Updating City'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.updateCity() Exception', error, '');
        next(errorResult);
    }
}

const activeInactiveCity = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Update City');
        let requiredFields = ['id'];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let sql = `UPDATE cities SET isActive = !isActive WHERE id = ` + req.body.id;
                let result = await query(sql);
                if (result && result.affectedRows > 0) {
                    let successResult = new ResultSuccess(200, true, 'Update City Status Successfully', result, 1);
                    return res.status(200).send(successResult);
                } else {
                    let errorResult = new ResultError(400, true, "region.activeInactiveCity() Error", new Error('Error While Updating City Status'), '');
                    next(errorResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.activeInactiveCity() Exception', error, '');
        next(errorResult);
    }
}


const getAllRegionData = async (req: Request, res: Response, next: NextFunction) => {
    try {
        logging.info(NAMESPACE, 'Getting All Region Data');
        let requiredFields = [''];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let citySql = `SELECT ct.id as countryId, ct.name as countryName ,ct.isoCode, ct.isoCode3, ct.dialCode, s.id as stateId, s.name as stateName, c.districtId, d.name as districtName, 
                c.id as cityId, c.name as cityName, c.pincode
                FROM cities c 
                INNER JOIN districts d ON d.id = c.districtId 
                INNER JOIN states s ON s.id = d.stateId 
                INNER JOIN countries ct ON ct.id = s.countryId`;
                let cityResult = await query(citySql);
                if (cityResult && cityResult.length > 0) {
                    let successResult = new ResultSuccess(200, true, 'Get Region Data Successfully', cityResult, cityResult.length);
                    return res.status(200).send(successResult);
                } else {
                    let successResult = new ResultSuccess(200, true, 'Region Data Not Available', [], 0);
                    return res.status(200).send(successResult);
                }
            } else {
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {

            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        let errorResult = new ResultError(500, true, 'region.getAllRegionData() Exception', error, '');
        next(errorResult);
    }
}

const updateRegionData = async (req: Request, res: Response, next: NextFunction) => {
    await beginTransaction();
    try {
        logging.info(NAMESPACE, 'Update All Region Data');
        let requiredFields = [''];
        let validationResult = header.validateRequiredFields(req, requiredFields);
        if (validationResult && validationResult.statusCode == 200) {
            let authorizationResult = await header.validateAuthorization(req, res, next);
            if (authorizationResult.statusCode == 200) {
                let result;
                for (let i = 0; i < req.body.data.length; i++) {
                    if (!req.body.data[i].pincode) {
                        req.body.data[i].pincode = "00000";
                    }
                    if (i % 5000 == 0) {
                        console.log(i);
                    }
                    if (parseInt(req.body.data[i].countryId)) {
                        let countryId = req.body.data[i].countryId;
                        //update Country
                        if (req.body.data[i].countryName) {
                            let updateCountrySql = `UPDATE countries SET name = ?, isoCode = ` + (req.body.data[i].isoCode ? ("'" + req.body.data[i].isoCode + "'") : null) + `
                            , isoCode3 = `+ (req.body.data[i].isoCode3 ? ("'" + req.body.data[i].isoCode3 + "'") : null) + `, dialCode = ` + (req.body.data[i].dialCode ? ("'" + req.body.data[i].dialCode + "'") : null) + `
                             WHERE id = ` + parseInt(req.body.data[i].countryId);
                            result = await query(updateCountrySql, [req.body.data[i].countryName]);
                            if (result && result.affectedRows >= 0) {

                            } else {
                                await rollback();
                                let errorResult = new ResultError(400, true, "Error While Updating Country", new Error("Error While Updating Country"), '');
                                next(errorResult);
                            }
                        }
                        if (parseInt(req.body.data[i].stateId)) {
                            let stateId = req.body.data[i].stateId;
                            //update State
                            if (req.body.data[i].stateName) {
                                let updateStateSql = `UPDATE states SET name = ?, countryId = ` + countryId + ` WHERE id = ` + parseInt(stateId);
                                result = await query(updateStateSql, [req.body.data[i].stateName]);
                                if (result && result.affectedRows >= 0) {

                                } else {
                                    await rollback();
                                    let errorResult = new ResultError(400, true, "Error While Updating State", new Error("Error While Updating State"), '');
                                    next(errorResult);
                                }
                            }
                            if (parseInt(req.body.data[i].districtId)) {
                                //update District
                                let districtId = req.body.data[i].districtId;
                                if (req.body.data[i].districtName) {
                                    let updateDistrictSql = `UPDATE districts SET name = ?, stateId = ` + stateId + ` WHERE id = ` + parseInt(districtId);
                                    result = await query(updateDistrictSql, [req.body.data[i].districtName]);
                                    if (result && result.affectedRows >= 0) {

                                    } else {
                                        await rollback();
                                        let errorResult = new ResultError(400, true, "Error While Updating District", new Error("Error While Updating District"), '');
                                        next(errorResult);
                                    }
                                }
                                if (parseInt(req.body.data[i].cityId)) {
                                    //update City
                                    let cityId = req.body.data[i].cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let updateCitySql = `UPDATE cities SET name = ?, districtId = ` + districtId + `, pincode = '` + req.body.data[i].pincode + `' WHERE id = ` + parseInt(cityId);
                                        result = await query(updateCitySql, [req.body.data[i].cityName]);
                                        if (result && result.affectedRows >= 0) {

                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Updating City", new Error("Error While Updating City"), '');
                                            next(errorResult);
                                        }
                                    }
                                } else {
                                    //insert City
                                    let cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let insertCitySql = `INSERT INTO cities(name, districtId, pincode) VALUES(?, ` + districtId + `,'` + req.body.data[i].pincode + `')`;
                                        result = await query(insertCitySql, [req.body.data[i].cityName]);
                                        if (result && result.insertId >= 0) {
                                            cityId = result.insertId;
                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Inserting City", new Error("Error While Inserting City"), '');
                                            next(errorResult);
                                        }
                                    }
                                }
                            } else {
                                //insert District
                                let districtId;
                                if (parseInt(req.body.data[i].districtName)) {
                                    let insertDistrictSql = `INSERT INTO districts(name, stateId) VALUES(?, ` + stateId + `)`;
                                    result = await query(insertDistrictSql, [req.body.data[i].districtName]);
                                    if (result && result.insertId > 0) {
                                        districtId = result.insertId;
                                    } else {
                                        await rollback();
                                        let errorResult = new ResultError(400, true, "Error While Updating District", new Error("Error While Updating District"), '');
                                        next(errorResult);
                                    }
                                }
                                if (parseInt(req.body.data[i].cityId)) {
                                    //update City
                                    let cityId = req.body.data[i].cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let updateCitySql = `UPDATE cities SET name = ?, districtId = ` + districtId + `, pincode = '` + req.body.data[i].pincode + `' WHERE id = ` + parseInt(cityId);
                                        result = await query(updateCitySql, [req.body.data[i].cityName]);
                                        if (result && result.affectedRows >= 0) {

                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Updating City", new Error("Error While Updating City"), '');
                                            next(errorResult);
                                        }
                                    }
                                } else {
                                    //insert City
                                    let cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let insertCitySql = `INSERT INTO cities(name, districtId, pincode) VALUES(?, ` + districtId + `,'` + req.body.data[i].pincode + `')`;
                                        result = await query(insertCitySql, [req.body.data[i].cityName]);
                                        if (result && result.insertId >= 0) {
                                            cityId = result.insertId;
                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Inserting City", new Error("Error While Inserting City"), '');
                                            next(errorResult);
                                        }
                                    }
                                }
                            }
                        } else {
                            //insert State
                            let stateId;
                            if (req.body.data[i].stateName) {
                                let insertStateSql = `INSERT INTO states(name,countryId) VALUES(?, ` + countryId + `)`;
                                result = await query(insertStateSql, [req.body.data[i].stateName]);
                                if (result && result.insertId > 0) {
                                    stateId = result.insertId;
                                } else {
                                    await rollback();
                                    let errorResult = new ResultError(400, true, "Error While Insert State", new Error("Error While Insert State"), '');
                                    next(errorResult);
                                }
                            }
                            if (parseInt(req.body.data[i].districtId)) {
                                //update District
                                let districtId = req.body.data[i].districtId;
                                if (req.body.data[i].districtName) {
                                    let updateDistrictSql = `UPDATE districts SET name = ?, stateId = ` + stateId + ` WHERE id = ` + parseInt(districtId);
                                    result = await query(updateDistrictSql, [req.body.data[i].districtName]);
                                    if (result && result.affectedRows >= 0) {

                                    } else {
                                        await rollback();
                                        let errorResult = new ResultError(400, true, "Error While Updating District", new Error("Error While Updating District"), '');
                                        next(errorResult);
                                    }
                                }
                                if (parseInt(req.body.data[i].cityId)) {
                                    //update City
                                    let cityId = req.body.data[i].cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let updateCitySql = `UPDATE cities SET name = ?, districtId = ` + districtId + `, pincode = '` + req.body.data[i].pincode + `' WHERE id = ` + parseInt(cityId);
                                        result = await query(updateCitySql, [req.body.data[i].cityName]);
                                        if (result && result.affectedRows >= 0) {

                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Updating City", new Error("Error While Updating City"), '');
                                            next(errorResult);
                                        }
                                    }
                                } else {
                                    //insert City
                                    let cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let insertCitySql = `INSERT INTO cities(name, districtId, pincode) VALUES(?, ` + districtId + `,'` + req.body.data[i].pincode + `')`;
                                        result = await query(insertCitySql, [req.body.data[i].cityName]);
                                        if (result && result.insertId >= 0) {
                                            cityId = result.insertId;
                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Inserting City", new Error("Error While Inserting City"), '');
                                            next(errorResult);
                                        }
                                    }
                                }
                            } else {
                                //insert District
                                let districtId;
                                if (req.body.data[i].districtName) {
                                    let insertDistrictSql = `INSERT INTO districts(name, stateId) VALUES(?, ` + stateId + `)`;
                                    result = await query(insertDistrictSql, [req.body.data[i].districtName]);
                                    if (result && result.insertId > 0) {
                                        districtId = result.insertId;
                                    } else {
                                        await rollback();
                                        let errorResult = new ResultError(400, true, "Error While Updating District", new Error("Error While Updating District"), '');
                                        next(errorResult);
                                    }
                                }
                                if (parseInt(req.body.data[i].cityId)) {
                                    //update City
                                    let cityId = req.body.data[i].cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let updateCitySql = `UPDATE cities SET name = ?, districtId = ` + districtId + `, pincode = '` + req.body.data[i].pincode + `' WHERE id = ` + parseInt(cityId);
                                        result = await query(updateCitySql, [req.body.data[i].cityName]);
                                        if (result && result.affectedRows >= 0) {

                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Updating City", new Error("Error While Updating City"), '');
                                            next(errorResult);
                                        }
                                    }
                                } else {
                                    //insert City
                                    let cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let insertCitySql = `INSERT INTO cities(name, districtId, pincode) VALUES(?, ` + districtId + `,'` + req.body.data[i].pincode + `')`;
                                        result = await query(insertCitySql, [req.body.data[i].cityName]);
                                        if (result && result.insertId >= 0) {
                                            cityId = result.insertId;
                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Inserting City", new Error("Error While Inserting City"), '');
                                            next(errorResult);
                                        }
                                    }
                                }
                            }
                        }
                    } else {
                        //insert Country
                        let countryId;
                        if (req.body.data[i].countryName) {
                            let insertCountrySql = `INSERT INTO countries(name, isoCode, isoCode3, dialCode) VALUES(?, ` + (req.body.data[i].isoCode ? ("'" + req.body.data[i].isoCode + "'") : null) + `
                            , `+ (req.body.data[i].isoCode3 ? ("'" + req.body.data[i].isoCode3 + "'") : null) + `, ` + (req.body.data[i].dialCode ? ("'" + req.body.data[i].dialCode + "'") : null) + `)`;
                            result = await query(insertCountrySql, [req.body.data[i].countryName]);
                            if (result && result.insertId > 0) {
                                countryId = result.insertId;
                            } else {
                                await rollback();
                                let errorResult = new ResultError(400, true, "Error While Updating Country", new Error("Error While Updating Country"), '');
                                next(errorResult);
                            }
                        }
                        if (parseInt(req.body.data[i].stateId)) {
                            let stateId = req.body.data[i].stateId;
                            //update State
                            if (req.body.data[i].stateName) {
                                let updateStateSql = `UPDATE states SET name = ?, countryId = ` + countryId + ` WHERE id = ` + parseInt(stateId);
                                result = await query(updateStateSql, [req.body.data[i].stateName]);
                                if (result && result.affectedRows >= 0) {

                                } else {
                                    await rollback();
                                    let errorResult = new ResultError(400, true, "Error While Updating State", new Error("Error While Updating State"), '');
                                    next(errorResult);
                                }
                            }
                            if (parseInt(req.body.data[i].districtId)) {
                                //update District
                                let districtId = req.body.data[i].districtId;
                                if (req.body.data[i].districtName) {
                                    let updateDistrictSql = `UPDATE districts SET name = ?, stateId = ` + stateId + ` WHERE id = ` + parseInt(districtId);
                                    result = await query(updateDistrictSql, [req.body.data[i].districtName]);
                                    if (result && result.affectedRows >= 0) {

                                    } else {
                                        await rollback();
                                        let errorResult = new ResultError(400, true, "Error While Updating District", new Error("Error While Updating District"), '');
                                        next(errorResult);
                                    }
                                }
                                if (parseInt(req.body.data[i].cityId)) {
                                    //update City
                                    let cityId = req.body.data[i].cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let updateCitySql = `UPDATE cities SET name = ?, districtId = ` + districtId + `, pincode = '` + req.body.data[i].pincode + `' WHERE id = ` + parseInt(cityId);
                                        result = await query(updateCitySql, [req.body.data[i].cityName]);
                                        if (result && result.affectedRows >= 0) {

                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Updating City", new Error("Error While Updating City"), '');
                                            next(errorResult);
                                        }
                                    }
                                } else {
                                    //insert City
                                    let cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let insertCitySql = `INSERT INTO cities(name, districtId, pincode) VALUES(, ` + districtId + `,'` + req.body.data[i].pincode + `')`;
                                        result = await query(insertCitySql, [req.body.data[i].cityName]);
                                        if (result && result.insertId >= 0) {
                                            cityId = result.insertId;
                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Inserting City", new Error("Error While Inserting City"), '');
                                            next(errorResult);
                                        }
                                    }
                                }
                            } else {
                                //insert District
                                let districtId;
                                if (req.body.data[i].districtName) {
                                    let insertDistrictSql = `INSERT INTO districts(name, stateId) VALUES(, ` + stateId + `)`;
                                    result = await query(insertDistrictSql, [req.body.data[i].districtName]);
                                    if (result && result.insertId > 0) {
                                        districtId = result.insertId;
                                    } else {
                                        await rollback();
                                        let errorResult = new ResultError(400, true, "Error While Updating District", new Error("Error While Updating District"), '');
                                        next(errorResult);
                                    }
                                }
                                if (parseInt(req.body.data[i].cityId)) {
                                    //update City
                                    let cityId = req.body.data[i].cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let updateCitySql = `UPDATE cities SET name = ?, districtId = ` + districtId + `, pincode = '` + req.body.data[i].pincode + `' WHERE id = ` + parseInt(cityId);
                                        result = await query(updateCitySql, [req.body.data[i].cityName]);
                                        if (result && result.affectedRows >= 0) {

                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Updating City", new Error("Error While Updating City"), '');
                                            next(errorResult);
                                        }
                                    }
                                } else {
                                    //insert City
                                    let cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let insertCitySql = `INSERT INTO cities(name, districtId, pincode) VALUES(?, ` + districtId + `,'` + req.body.data[i].pincode + `')`;
                                        result = await query(insertCitySql, [req.body.data[i].cityName]);
                                        if (result && result.insertId >= 0) {
                                            cityId = result.insertId;
                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Inserting City", new Error("Error While Inserting City"), '');
                                            next(errorResult);
                                        }
                                    }
                                }
                            }
                        } else {
                            //insert State
                            let stateId;
                            if (req.body.data[i].stateName) {
                                let insertStateSql = `INSERT INTO states(name,countryId) VALUES(?, ` + countryId + `)`;
                                result = await query(insertStateSql, [req.body.data[i].stateName]);
                                if (result && result.insertId > 0) {
                                    stateId = result.insertId;
                                } else {
                                    await rollback();
                                    let errorResult = new ResultError(400, true, "Error While Insert State", new Error("Error While Insert State"), '');
                                    next(errorResult);
                                }
                            }
                            if (parseInt(req.body.data[i].districtId)) {
                                //update District
                                let districtId = req.body.data[i].districtId;
                                if (req.body.data[i].districtName) {
                                    let updateDistrictSql = `UPDATE districts SET name = ?, stateId = ` + stateId + ` WHERE id = ` + parseInt(districtId);
                                    result = await query(updateDistrictSql, [req.body.data[i].districtName]);
                                    if (result && result.affectedRows >= 0) {

                                    } else {
                                        await rollback();
                                        let errorResult = new ResultError(400, true, "Error While Updating District", new Error("Error While Updating District"), '');
                                        next(errorResult);
                                    }
                                }
                                if (parseInt(req.body.data[i].cityId)) {
                                    //update City
                                    let cityId = req.body.data[i].cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let updateCitySql = `UPDATE cities SET name = , districtId = ` + districtId + `, pincode = '` + req.body.data[i].pincode + `' WHERE id = ` + parseInt(cityId);
                                        result = await query(updateCitySql, [req.body.data[i].cityName]);
                                        if (result && result.affectedRows >= 0) {

                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Updating City", new Error("Error While Updating City"), '');
                                            next(errorResult);
                                        }
                                    }
                                } else {
                                    //insert City
                                    let cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let insertCitySql = `INSERT INTO cities(name, districtId, pincode) VALUES(?, ` + districtId + `,'` + req.body.data[i].pincode + `')`;
                                        result = await query(insertCitySql, [req.body.data[i].cityName]);
                                        if (result && result.insertId >= 0) {
                                            cityId = result.insertId;
                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Inserting City", new Error("Error While Inserting City"), '');
                                            next(errorResult);
                                        }
                                    }
                                }
                            } else {
                                //insert District
                                let districtId;
                                if (req.body.data[i].districtName) {
                                    let insertDistrictSql = `INSERT INTO districts(name, stateId) VALUES(?, ` + stateId + `)`;
                                    result = await query(insertDistrictSql, [req.body.data[i].districtName]);
                                    if (result && result.insertId > 0) {
                                        districtId = result.insertId;
                                    } else {
                                        await rollback();
                                        let errorResult = new ResultError(400, true, "Error While Updating District", new Error("Error While Updating District"), '');
                                        next(errorResult);
                                    }
                                }
                                if (parseInt(req.body.data[i].cityId)) {
                                    //update City
                                    let cityId = req.body.data[i].cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let updateCitySql = `UPDATE cities SET name = ?, districtId = ` + districtId + `, pincode = '` + req.body.data[i].pincode + `' WHERE id = ` + parseInt(cityId);
                                        result = await query(updateCitySql, [req.body.data[i].cityName]);
                                        if (result && result.affectedRows >= 0) {

                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Updating City", new Error("Error While Updating City"), '');
                                            next(errorResult);
                                        }
                                    }
                                } else {
                                    //insert City
                                    let cityId;
                                    if (req.body.data[i].cityName && req.body.data[i].pincode) {
                                        let insertCitySql = `INSERT INTO cities(name, districtId, pincode) VALUES(?, ` + districtId + `,'` + req.body.data[i].pincode + `')`;
                                        result = await query(insertCitySql, [req.body.data[i].cityName]);
                                        if (result && result.insertId >= 0) {
                                            cityId = result.insertId;
                                        } else {
                                            await rollback();
                                            let errorResult = new ResultError(400, true, "Error While Inserting City", new Error("Error While Inserting City"), '');
                                            next(errorResult);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                await commit();
                let successResult = new ResultSuccess(200, true, 'Update Region Data Successfully', result, 1);
                return res.status(200).send(successResult);
            } else {
                await rollback();
                let errorResult = new ResultError(401, true, "Unauthorized request", new Error(authorizationResult.message), '');
                next(errorResult);
            }
        } else {
            await rollback();
            let errorResult = new ResultError(validationResult.statusCode, true, validationResult.message, new Error(validationResult.message), '');
            next(errorResult);
        }
    } catch (error: any) {
        await rollback();
        let errorResult = new ResultError(500, true, 'region.getAllRegionData() Exception', error, '');
        next(errorResult);
    }
}

export default { getCityByPincode, getCountries, getStates, getDistricts, getCities, insertCountry, updateCountry, activeInactiveCountry, insertState, updateState, activeInactiveState, insertDistrict, updateDistrict, activeInactiveDistrict, insertCity, updateCity, activeInactiveCity, getAllRegionData, updateRegionData }