const toJson        = require('xml2js').parseString ;
const mysql         = require('mysql') ;
const format        = require('dateformat') ;

const con = mysql.createConnection({
    host: "HOST",
    user: "USERNAME",
    password: "PASSWORD",
    database: "DB"
});

module.exports.parseResult = function(result) {
    var xmlResult = result ;
    toJson(xmlResult, function(error, result){

        jsonObj = result.Instructions.Instruction ;
        for ( i=0; i < jsonObj.length ; i++ ) {

            var thisRef = jsonObj[i].Ref.toString() ;
            if ( thisRef.indexOf('-FR') > 0 ) {

                // CREATE PLACEHOLDER
                createRecord(thisRef);

                // PARSE REFERRER INFO
                var referrerRef = jsonObj[i].Referrer[0].Ref.toString() ;
                var referrerName = jsonObj[i].Referrer[0].Description.toString() ;
                var referrerNameNameNumber = jsonObj[i].Referrer[0].Address[0].NameNumber.toString() ;
                var referrerNameAddLine1 = jsonObj[i].Referrer[0].Address[0].Line1.toString() ;
                var referrerNameAddLine2 = jsonObj[i].Referrer[0].Address[0].Line2.toString() ;
                var referrerNameTownCity = jsonObj[i].Referrer[0].Address[0].TownCity.toString() ;
                var referrerNameCounty = jsonObj[i].Referrer[0].Address[0].County.toString() ;
                var referrerNamePostCode = jsonObj[i].Referrer[0].Address[0].PostCode.toString() ;
                var referrerTelephoneNo = jsonObj[i].Referrer[0].PhoneNumbers[0].Daytime.toString() ;
                var referrerMobileNo = jsonObj[i].Referrer[0].PhoneNumbers[0].Mobile.toString() ;
                var referrerFaxNo = jsonObj[i].Referrer[0].PhoneNumbers[0].Fax.toString() ;
                var referrerEmailAddress = jsonObj[i].Referrer[0].EmailAddress.toString() ;

                // PARSE CUSTOMER DETAILS
                var addressName = jsonObj[i].Customers.toString() ;
                var counter = 0 ;
                var numberOfBorrowers = jsonObj[i].Customer.length ;
                for ( x=0; x < numberOfBorrowers; x++ ) {
                    var counter = counter + 1 ;
                    var clientUpdateSql = 'UPDATE auto_SRMinstructs SET '+
                                          'borrower'+ counter +'_title = "' + jsonObj[i].Customer[x].Title.toString() + '", ' +
                                          'borrower'+ counter +'_forenames = "' + jsonObj[i].Customer[x].Forename.toString() + '", ' +
                                          'borrower'+ counter +'_surname = "' + jsonObj[i].Customer[x].Surname.toString() + '", ' +
                                          'borrower'+ counter +'_housenumber = "' + jsonObj[i].Customer[x].Address[0].NameNumber.toString() + '", ' +
                                          'borrower'+ counter +'_streetname = "' + jsonObj[i].Customer[x].Address[0].Line1.toString() + '", ' +
                                          'borrower'+ counter +'_districtname = "' + jsonObj[i].Customer[x].Address[0].Line2.toString() + '", ' +
                                          'borrower'+ counter +'_posttown = "' + jsonObj[i].Customer[x].Address[0].TownCity.toString() + '", ' +
                                          'borrower'+ counter +'_county = "' + jsonObj[i].Customer[x].Address[0].County.toString() + '", ' +
                                          'borrower'+ counter +'_postcode1 = "' + jsonObj[i].Customer[x].Address[0].PostCode.toString() + '", ' +
                                          'borrower'+ counter +'_tel_home = "' + jsonObj[i].Customer[x].PhoneNumbers[0].Daytime.toString() + '", ' +
                                          'borrower'+ counter +'_tel_office = "' + jsonObj[i].Customer[x].PhoneNumbers[0].Evening.toString() + '", ' +
                                          'borrower'+ counter +'_tel_mobile = "' + jsonObj[i].Customer[x].PhoneNumbers[0].Mobile.toString() + '", ' +
                                          'borrower'+ counter +'_email = "' + jsonObj[i].Customer[x].EmailAddress.toString() + '" ' +
                                          'WHERE SRinstruction_id = "' + thisRef + '" ;' ;
                    // addClients(clientUpdateSql) ;
                }

                // CASE DETAILS
                var transType = jsonObj[i].Case[0].TransactionType.toString() ;
                var feeGuard = jsonObj[i].Case[0].feeGuard.toString() ;
                var createDate = jsonObj[i].Case[0].CreateDate.toString() ;
                var onHold = jsonObj[i].Case[0].OnHold.toString() ;
                var tenureType = jsonObj[i].Case[0].TenureType.toString() ;
                var propertyNameNumber = jsonObj[i].Case[0].Property[0].Address[0].NameNumber.toString() ;
                var propertyLine1 = jsonObj[i].Case[0].Property[0].Address[0].Line1.toString() ;
                var propertyLine2 = jsonObj[i].Case[0].Property[0].Address[0].Line2.toString() ;
                var propertyTownCity = jsonObj[i].Case[0].Property[0].Address[0].TownCity.toString() ;
                var propertyCounty = jsonObj[i].Case[0].Property[0].Address[0].County.toString() ;
                var propertyPostCode = jsonObj[i].Case[0].Property[0].Address[0].PostCode.toString() ;
                var propertyPrice = jsonObj[i].Case[0].Property[0].Price.toString() ;
                var propertyMortgageAmount = jsonObj[i].Case[0].Property[0].MortgageAmount.toString() ;
                var propertyMortgageLender = jsonObj[i].Case[0].Property[0].MortgageLender.toString() ;

                // DISBURSEMENTS
                if ( jsonObj[i].Disbursements != '' ) {
                    var hmlrLandRegistryFee = jsonObj[i].Disbursements[0].hmlrLandRegistryFee.toString() ;
                    var landRegistryOfficialCopies = jsonObj[i].Disbursements[0].landRegistryOfficialCopies.toString() ;
                    var hmlrSearchFee = jsonObj[i].Disbursements[0].hmlrSearchFee.toString() ;
                    var bankruptcySearchFee = jsonObj[i].Disbursements[0].bankruptcySearchFee.toString() ;
                    var electronicIDCheckFee = jsonObj[i].Disbursements[0].electronicIDCheckFee.toString() ;
                    var rmSearchIdentityFee = jsonObj[i].Disbursements[0].rmSearchIdentityFee.toString() ;
                    var searchIndemnityFee = jsonObj[i].Disbursements[0].searchIndemnityFee.toString() ;
                }

                // FEES
                var sortFee = jsonObj[i].Fees[0].sortFee.toString() ;
                var brokerFee = jsonObj[i].Fees[0].brokerFee.toString() ;
                var solicitorFee = jsonObj[i].Fees[0].solicitorFee.toString() ;
                var totalExVat = jsonObj[i].Fees[0].totalExVat.toString() ;
                var totalIncVat = jsonObj[i].Fees[0].totalIncVat.toString() ;

                // CASE NOTES
                var caseNotes = jsonObj[i].Note.toString() ;

                // MISC VARIABLES
                var matterDesc = 'R/M OF ' + propertyNameNumber.toUpperCase() ;
                var instructionDate = new Date() ;
                var instructionDate = format(instructionDate, 'yyyy-mm-dd') ;

                // INSERT INSTRUCTION
                var mainInsert = 'UPDATE auto_SRMinstructs SET ' +
                               'instruction_type = "R",' +
                               'worktype_code = "CON",' +
                               'mattertype_code = "VRM",' +
                               'matter_desc = "'+matterDesc+'",' +
                               'client_type_code = "",' +
                               'client_origin = "SRM",' +
                               'matter_origin = "SRM",' +
                               'dept_code = "REM",' +
                               'lead_contact_id = "",' +
                               'contact_type = "L",' +
                               'trantype = "R",' +
                               'instruction_date = "'+instructionDate+'",' +
                               'accepted_ind = "N",' +
                               'number_of_borrowers = "'+numberOfBorrowers+'",' +
                               'property_name = "'+propertyNameNumber+'",' +
                               'property_street = "'+propertyLine1+'",' +
                               'property_district = "'+propertyLine2+'",' +
                               'property_post_town = "'+propertyTownCity+'",' +
                               'property_county = "'+propertyCounty+'",' +
                               'property_postcode = "'+propertyPostCode+'",' +
                               'property_country = "United Kingdom",' +
                               'customer_note = "'+caseNotes+'",' +
                               'hmlrLandRegistryFee = "'+hmlrLandRegistryFee+'",' +
                               'landRegistryOfficialCopies = "'+landRegistryOfficialCopies+'",' +
                               'hmlrSearchFee = "'+hmlrSearchFee+'",' +
                               'bankruptcySearchFee = "'+bankruptcySearchFee+'",' +
                               'electronicIDCheckFee = "'+electronicIDCheckFee+'",' +
                               'rmSearchIdentityFee = "'+rmSearchIdentityFee+'",' +
                               'searchIndemnityFee = "'+searchIndemnityFee+'",' +
                               'sortFee = "'+sortFee+'",' +
                               'brokerFee = "'+brokerFee+'",' +
                               'solicitorFee = "'+solicitorFee+'",' +
                               'totalExVat = "'+totalExVat+'",' +
                               'totalIncVat = "'+totalIncVat+'"' +
                               'WHERE SRinstruction_id = "' + thisRef + '"' ;

                // DO INSERTS
                createInstruction(mainInsert);
            }
        }
    })
}

// CREATE PLACEHOLDER ROW IN TABLE
function createRecord(ref) {
    var placeHolderSql = 'INSERT INTO auto_SRMinstructs (SRinstruction_id) VALUES ("'+ref+'")' ;
    con.connect(function(){
        con.query(placeHolderSql, function(error, result){
            console.log('[+] Place holder for '+ref+' has been created.') ;
        }) ;
    }) ;
}

// UPDATE BORROWER DETAILS
function addClients(query) {
    con.connect(function(){
        con.query(query, function(error, result){
            console.log(error)
        }) ;
    })
}

// MAIN UPDATE
function createInstruction(query) {
    con.connect(function(){
        con.query(query.toString(), function(error, result){
            if (error) { throw error }
            console.log(result) ;
        })
    })
}