const express = require("express");
const { connection, key } = require("../dbcon/db");
const bodyparser = require("body-parser");
var app = express();

/**********************
 Search Process Start 
***********************/

/* Route  : To Search by NPA ThinQ */
app.get('/searchthinqbynpa', bodyparser.json(), (req, res) => {
    const queryData = req.query;
    let condition = "`npa` = '"+queryData.npa+"'";
    console.log("Requested Data for /searchthinqbynpa :: ", queryData);
    thinqcommon(req, res, queryData.quantity, condition);
});
/* Route  : To Search by NPA and NXX ThinQ */
app.get('/searchthinqbynpanxx', bodyparser.json(), (req, res) => {
    const queryData = req.query;
    let condition = "`npa` = '"+queryData.npa+"' AND `nxx` = '"+queryData.nxx+"'";
    console.log("Requested Data for /searchthinqbynpanxx :: ", queryData);
    thinqcommon(req, res, queryData.quantity, condition);
});
/* Route  : To Search by State and RateCenter */
app.get('/searchthinqbyrate', bodyparser.json(), (req, res) => {
    const queryData = req.query;
    let condition = "`state` = '"+queryData.state+"' AND `ratecenter` = '"+queryData.rateCenter+"'";
    console.log("Requested Data for /searchthinqbyrate :: ", queryData);
    thinqcommon(req, res, queryData.quantity, condition);
});

function thinqcommon(req, res, qty, condition){
    let qry = "SELECT CONCAT('1', `did`) AS id, CONCAT('(', SUBSTRING(did, 1, 3), ') ', SUBSTRING(did, 4, 3), '-', SUBSTRING(did, 7, 4)) AS didSummary, `ratecenter` rate_center, state, 0 AS thinqTier FROM `did_buy` WHERE "+condition+" AND `type`='NN' LIMIT " + qty;
    console.log("DID Query",qry);
    connection.execute(qry, (err, rows) => {
        if (err==1) {
            if (rows.length > 0) {
                res.send({ status: 200, message: "DID Fetched Successfully", dids:rows });   
            } else {
                res.send({ status: 404, message: "DID Data Does Not Exist!", dids:[]});
            }
        }
        else {
            res.send({ status: 500, message: "Query Execution Failed" });   
        }
    });
}

/* Route  : To Search by NPA and NXX Inteliquent */
app.post('/inteliquent/tnInventory', bodyparser.json(), (req, res) => {
    const postData = req.body;
    let condition = '';
    if(postData.province && postData.rateCenter){
        condition = "`state` = '"+postData.province+"' AND `ratecenter` = '"+postData.rateCenter+"'";
    }
    else{
        const segments = extractSegments(postData.tnMask);
        if(segments.npa)
            condition += "`npa` = '"+segments.npa+"'";
        if(segments.nxx)
            condition += " AND `nxx` = '"+segments.nxx+"'";
    }
    intelicommon(req, res, postData.quantity, condition);    
    console.log("Requested Data for /inteliquent/tnInventory :: ", condition);
    
});

function intelicommon(req, res, qty, condition){
    let qry = "SELECT `did` AS telephoneNumber, `ratecenter` AS rateCenter, `state` AS province, 'Tier1' AS rateCenterTier, 'LATA1' AS lata, 'CC1' AS countryCode, 'true' AS cnamAllowed, 'false' AS dlAllowed, 'true' AS e911Allowed, 'false' AS msgAllowed, 'City1' AS city FROM `did_buy` WHERE "+condition+" AND `type`='NN' LIMIT " + qty;
    console.log("DID Query",qry);
    connection.execute(qry, (err, rows) => {
        if (err==1) {
            if (rows.length > 0) {
                res.send({ statusCode: 200, message: "DID Fetched Successfully", tnResult:rows });   
            } else {
                res.send({ statusCode: 404, message: "DID Data Does Not Exist!", tnResult:[]});
            }
        }
        else {
            res.send({ status: 500, message: "Query Execution Failed" });              
        }
    });
}

app.get('/twilio/Accounts/:accountId/AvailablePhoneNumbers/US/TollFree.json', bodyparser.urlencoded({ extended: false }), (req, res) => {
    const postData = req.query;
    console.log("postData :: ", postData);
    let condition = '';
    if(postData){
        condition += "`npa` = '"+ postData.AreaCode+"'";
        
        twiliotollfreesearch(req, res, postData.PageSize, condition);
    }
    else{
        res.send({ statusCode: 500, message: "DID Not Fetched Successfully" });  
    }

    console.log("Requested Data for /twilio/Accounts/:accountId/AvailablePhoneNumbers/US/TollFree.json :: ", condition);

});

function twiliotollfreesearch(req, res, qty, condition){
    let qry = "SELECT `did` AS `phone_number`, CONCAT('+1', `did`) AS `friendly_name` FROM `did_buy` WHERE "+condition+" AND `type`='TF' LIMIT " + qty;
    console.log("DID Query",qry);
    connection.execute(qry, (err, rows) => {
        if (err==1) {
            if (rows.length > 0) {
                res.send({ statusCode: 200, message: "DID Fetched Successfully", available_phone_numbers : rows });   
            } else {
                res.send({ statusCode: 404, message: "DID Data Does Not Exist!", available_phone_numbers:[]});
            }
        }
        else {
            res.send({ status: 500, message: "Query Execution Failed" });              
        }
    });
}

/**********************
 Search Process End 
***********************/

/**********************
 Buy Process Start 
***********************/
/*****************
 Twilio Process Start 
*****************/
/* Route  : To Order NN DID from Inteliquent */
app.post('/twilio/Accounts/:accountId/IncomingPhoneNumbers/TollFree.json', bodyparser.urlencoded({ extended: false }), (req, res) => {
    const postData = req.body;
    console.log("twilio/Accounts/:accountId/IncomingPhoneNumbers/TollFree.json data :: ", postData);
    
    let did = postData.PhoneNumber;
    console.log("Requested Data for /twilio/Accounts/:accountId/IncomingPhoneNumbers/TollFree.json did :: ", did);
    if(did){
        /* let qry = "SELECT `did` FROM `did_buy` WHERE `did`="+did.trim()+" LIMIT 1";
        console.log("DID Check Query",qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if(rows.length > 0){ */
                    let data = {
                        "account_sid": "AC"+ Math.random().toString(36).substring(2, 34).toUpperCase(),
                        "address_requirements": "none",
                        "address_sid": null,
                        "api_version": "2010-04-01",
                        "beta": false,
                        "bundle_sid": null,
                        "capabilities": {
                          "fax": false,
                          "mms": true,
                          "sms": true,
                          "voice": true
                        },
                        "date_created": "Mon, 14 Jul 2025 15:34:00 +0000",
                        "date_updated": "Mon, 14 Jul 2025 15:34:00 +0000",
                        "emergency_address_sid": null,
                        "emergency_address_status": "unregistered",
                        "emergency_status": "Active",
                        "friendly_name": did,
                        "identity_sid": null,
                        "origin": "twilio",
                        "phone_number": "+1"+did,
                        "sid": "PN"+ Math.random().toString(36).substring(2, 34).toUpperCase(),
                        "sms_application_sid": "",
                        "sms_fallback_method": "POST",
                        "sms_fallback_url": null,
                        "sms_method": "POST",
                        "sms_url": "",
                        "status": "in-use",
                        "status_callback": null,
                        "status_callback_method": "POST",
                        "subresource_uris": {
                          "assigned_add_ons": "/2010-04-01/Accounts/AC0ef5c2af87dfc184b984e3663f746a0f/IncomingPhoneNumbers/PNb2e2e62f310195a9c3234b6a7c99de608/AssignedAddOns.json"
                        },
                        "trunk_sid": null,
                        "uri": "/2010-04-01/Accounts/AC0ef5c2af87dfc18d4b984e3663f746a0f/IncomingPhoneNumbers/PNb2e2e62f310195a9c3234b6a7dc99e608.json",
                        "voice_application_sid": null,
                        "voice_caller_id_lookup": false,
                        "voice_fallback_method": "POST",
                        "voice_fallback_url": null,
                        "voice_method": "POST",
                        "voice_receive_mode": "voice",
                        "voice_url": null
                      }
                      res.status(200).json(data);
                /* }
                else{
                    res.send({statusCode:412, message:"Number not found "+did+""});
                }
            }
            else{
                res.send({statusCode: 413, message: "Something went wrong with Connection!"}); 
            }
        }); */
    }
    else{
        res.send({statusCode:414, message:"This API cannot be accessed from this IP Address"}); 
    }    
});


/* Route  : To Order NN DID from Inteliquent */
app.post('/twilio/Accounts.json', bodyparser.urlencoded({ extended: false }), (req, res) => {
    const postData = req.body;
    console.log("twilio/Account postData :: ", postData);
    let FriendlyName = postData.FriendlyName;
    console.log("Requested FriendlyName for /twilio/Accounts :: ", FriendlyName);
    // if(did){
        const ticketNumber = generateTicket();            
        let data = {
            "auth_token": "",
            "date_created": new Date().toUTCString(),
            "date_updated": new Date().toUTCString(),
            "friendly_name": FriendlyName,
            "owner_account_sid": "AC"+ Math.random().toString(36).substring(2, 34).toUpperCase(),
            "sid": "AC"+ Math.random().toString(36).substring(2, 34).toUpperCase(),
            "status": "active",
            "subresource_uris": {
                "available_phone_numbers": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/AvailablePhoneNumbers.json",
                "calls": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Calls.json",
                "conferences": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Conferences.json",
                "incoming_phone_numbers": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/IncomingPhoneNumbers.json",
                "notifications": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Notifications.json",
                "outgoing_caller_ids": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/OutgoingCallerIds.json",
                "recordings": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Recordings.json",
                "transcriptions": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Transcriptions.json",
                "addresses": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Addresses.json",
                "signing_keys": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/SigningKeys.json",
                "connect_apps": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/ConnectApps.json",
                "sip": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/SIP.json",
                "authorized_connect_apps": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/AuthorizedConnectApps.json",
                "usage": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Usage.json",
                "keys": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Keys.json",
                "applications": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Applications.json",
                "short_codes": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/SMS/ShortCodes.json",
                "queues": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Queues.json",
                "messages": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Messages.json",
                "balance": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/Balance.json"
            },
            "type": "Full",
            "uri": "/2010-04-01/Accounts/ACaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa.json"
        }
        console.log({statusCode:200, message :"Sub Account Created Successfully", body:data});        
        res.status(200).json(data);      
            
    /* }
    else{
        res.send({statusCode:414, message:"This API cannot be accessed from this IP Address"}); 
    }  */   
});

app.post('/twilio/Accounts/:orderId/Messages.json', bodyparser.urlencoded({ extended: false }), (req, res) => {
    const postData = req.body;
    console.log("twilio/Account postData :: ", postData);
    
    let orderId = req.params.orderId;
    console.log("Requested FriendlyName for /twilio/Accounts/:orderId/Messages.json :: ", orderId);
    // if(did){          
        let data = {
            "success": true,
            "sid": "SM" + Math.random().toString(36).substring(2, 34).toUpperCase(),
            "status": "queued",
            "to": "+1"+postData.To,
            "body": postData.Body
        }
        console.log({statusCode:200, message :"Message sent Successfully", body:data});
        res.status(200).send(data);
        // res.send({statusCode:200, message :"Sub Account Created Successfully", body:data});        
            
    /* }
    else{
        res.send({statusCode:414, message:"This API cannot be accessed from this IP Address"}); 
    }  */   
});

/* Route  : To Move Number from Main to Sub Account (Mock) */
app.post('/twilio/Accounts/:currentAccountSid/IncomingPhoneNumbers/:phoneSid', bodyparser.urlencoded({ extended: false }), (req, res) => {
    const postData = req.body;
    const currentAccountSid = req.params.currentAccountSid;
    const phoneSid = req.params.phoneSid;
    const targetAccountSid = postData.AccountSid;

    // Simulate looking up the number in your DB, here we just use a static example
    // In real code, you would look up the DID and its details

    // Decide which response to send based on the targetAccountSid
    let data;
    console.log(
        "targetAccountSid :::::::::: ", targetAccountSid
    );
    
    if (targetAccountSid === "ACf5e9b5088c539d4de380aed294be736b") {
        // This is a move to SUB account (main → sub)
        // You can use your previous Twilio-style response here, or look up the DID for details
        data = {
            sid: "PN"+ Math.random().toString(36).substring(2, 34).toUpperCase(),
            account_sid: targetAccountSid,
            friendly_name: "(415) 874‑0103",
            phone_number: "+14158740103",
            voice_url: "https://demo.twilio.com/welcome/voice/",
            voice_method: "POST",
            voice_fallback_url: null,
            voice_fallback_method: "POST",
            voice_caller_id_lookup: false,
            date_created: new Date().toUTCString(),
            date_updated: new Date().toUTCString(),
            sms_url: "https://demo.twilio.com/welcome/sms/reply",
            sms_method: "POST",
            sms_fallback_url: "",
            sms_fallback_method: "POST",
            address_requirements: "none",
            beta: false,
            capabilities: {
                voice: true,
                sms: true,
                mms: true,
                fax: true
            },
            voice_receive_mode: "voice",
            status_callback: "",
            status_callback_method: "POST",
            api_version: "2010-04-01",
            voice_application_sid: null,
            sms_application_sid: "",
            origin: "twilio",
            trunk_sid: null,
            emergency_status: "Inactive",
            emergency_address_sid: null,
            address_sid: null,
            identity_sid: null,
            bundle_sid: null,
            uri: `/2010-04-01/Accounts/${targetAccountSid}/IncomingPhoneNumbers/${phoneSid}.json`,
            status: "in‑use"
        };
        console.log({ statusCode: 200, message: "Number moved main to sub successfully", body: data });  

    } else {
        // This is a move to MAIN account (sub → main)
        data = {
            sid: "PN"+ Math.random().toString(36).substring(2, 34).toUpperCase(),
            account_sid: targetAccountSid,
            friendly_name: "My Company Line",
            phone_number: "+15105647903",
            voice_url: null,
            voice_method: "POST",
            sms_url: null,
            sms_method: "POST",
            date_created: new Date().toUTCString(),
            date_updated: new Date().toUTCString(),
            capabilities: {
                voice: true,
                sms: false,
                mms: true
            },
            address_requirements: "none",
            emergency_status: "Inactive",
            emergency_address_sid: "ADXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
            uri: `/2010-04-01/Accounts/${targetAccountSid}/IncomingPhoneNumbers/${phoneSid}.json`
        };
        console.log({ statusCode: 200, message: "Number moved sub to main successfully", body: data });  
        
    }
      
    res.status(200).json(data);
    // res.send({ statusCode: 200, message: "Number moved successfully", body: data });
});
/* Route  : To Move Number from Main to Sub Account (Mock) */
app.delete('/twilio/Accounts/:currentAccountSid/IncomingPhoneNumbers/:phoneSid', bodyparser.urlencoded({ extended: false }), (req, res) => {
    const postData = req.body;
    const currentAccountSid = req.params.currentAccountSid;
    const phoneSid = req.params.phoneSid;
    let data = '';
    console.log({ statusCode: 200, message: "Number removed successfully", body: data });  

    res.status(200).json(data);
});

// Mock: Verify Profile (Toll-Free Sender)
app.post('/twilio/Verifications', bodyparser.urlencoded({ extended: false }), (req, res) => {
    const postData = req.body;
    const now = new Date().toISOString();
    const sid = "HH" + Math.random().toString(36).substring(2, 34).toUpperCase();
    const accountSid = "AC" + Math.random().toString(36).substring(2, 34).toUpperCase();
    const customerProfileSid = "BU" + Math.random().toString(36).substring(2, 34).toUpperCase();
    const trustProductSid = null;
    const regulatedItemSid = null;
    const url = `https://messaging.twilio.com/v1/Tollfree/Verifications/${sid}`;
    const data = {
        sid: sid,
        account_sid: accountSid,
        customer_profile_sid: customerProfileSid,
        trust_product_sid: trustProductSid,
        regulated_item_sid: regulatedItemSid,
        status: "PENDING_REVIEW",
        date_created: now,
        date_updated: now,
        business_name: postData.BusinessName || "Business Name",
        business_street_address: postData.BusinessStreetAddress || "123 Main Street",
        business_street_address2: postData.BusinessStreetAddress2 || "Suite 101",
        business_city: postData.BusinessCity || "Anytown",
        business_state_province_region: postData.BusinessStateProvinceRegion || "AA",
        business_postal_code: postData.BusinessPostalCode || "11111",
        business_country: postData.BusinessCountry || "US",
        business_website: postData.BusinessWebsite || "http://www.example.com",
        business_contact_first_name: postData.BusinessContactFirstName || "firstname",
        business_contact_last_name: postData.BusinessContactLastName || "lastname",
        business_contact_email: postData.BusinessContactEmail || "email@example.com",
        business_contact_phone: postData.BusinessContactPhone || "+1231231234",
        notification_email: postData.NotificationEmail || "support@example.com",
        use_case_categories: postData.UseCaseCategories || ["TWO_FACTOR_AUTHENTICATION"],
        use_case_summary: postData.UseCaseSummary || "This number is used to send out promotional offers and coupons to the customers of John's Coffee Shop",
        production_message_sample: postData.ProductionMessageSample || "lorem ipsum",
        opt_in_image_urls: postData.OptInImageUrls || ["https://testbusiness.com/images/image1.jpg"],
        opt_in_type: postData.OptInType || "VERBAL",
        message_volume: postData.MessageVolume || "10",
        additional_information: postData.AdditionalInformation || "see our privacy policy here www.example.com/privacypolicy",
        tollfree_phone_number_sid: postData.TollfreePhoneNumberSid || "PNaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
        rejection_reason: null,
        error_code: null,
        edit_expiration: null,
        edit_allowed: null,
        rejection_reasons: null,
        resource_links: {},
        url: url,
        external_reference_id: postData.ExternalReferenceId || "abc123xyz567"
    };
    console.log({ statusCode: 200, message: "Number moved successfully", body: data });
    res.status(200).json(data);
    // res.send({ statusCode: 200, message: "Number moved successfully", body: response });
});

// Mock: Twilio Toll-Free Verification API
app.post('/twilio/Accounts/:accountSid.json', bodyparser.urlencoded({ extended: false }), (req, res) => {
    const accountSid = req.params.accountSid;
    const status = req.body.Status || req.body.status;

    // Simulate Twilio Account resource response
    const now = new Date().toUTCString();
    const response = {
        sid: accountSid,
        friendly_name: "Subaccount",
        status: status || "active",
        type: "Subaccount",
        date_created: now,
        date_updated: now,
        auth_token: "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
        owner_account_sid: "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
        subresource_uris: "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
        uri: `/2010-04-01/Accounts/${accountSid}.json`
    };
    console.log({ statusCode: 200, message: "Subaccount suspended successfully", body: response });    
    res.status(200).json(response);
    // res.send({ statusCode: 200, message: "Number suspended successfully", body: response });
}); 
// Mock: Twilio Toll-Free Verification API
app.post('/twilio/Accounts/:subaccountSid/Keys.json', bodyparser.urlencoded({ extended: false }), (req, res) => {
    const subaccountSid = req.params.subaccountSid;
    console.log("Request for twilio/Accounts/:subaccountSid/Keys.json", subaccountSid);
    // Simulate Twilio Account resource response
    const now = new Date().toUTCString();
    const response = {
        "date_created": now,
        "date_updated": now,
        "friendly_name": null,
        "secret": Math.random().toString(36).substring(2, 34).toUpperCase(),
        "sid": "SK"+Math.random().toString(36).substring(2, 34).toUpperCase()
    }
    console.log({ statusCode: 200, message: "subaccount key get successfully", body: response });    
    res.status(200).json(response);
    // res.send({ statusCode: 200, message: "Number suspended successfully", body: response });
}); 
/*****************
 Twilio Process End 
*****************/

/* Route  : To Order Create from ThinQ */
app.post('/thinqordercreate', bodyparser.json(), (req, res) => {
    const postData = req.body;
    console.log("Requested Data for /thinqordercreate :: ", postData);
    let did = postData.order.tns[0].did;
    if(did){
        let qry = "SELECT `did` FROM `did_buy` WHERE `did`="+did.trim()+" LIMIT 1";
        console.log("DID Check Query",qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if(rows.length > 0){
                    const ticketNumber = generateTicket();
                    if(ticketNumber){
                        console.log({ code: 200, message: "Order created successfully", id:ticketNumber });
                        res.send({ code: 200, message: "Order created successfully", id:ticketNumber });  
                    }
                    else{
                        console.log({code: 412, message: "DID "+did+"  order not created", description: ''});                        
                        res.send({code: 412, message: "DID "+did+"  order not created", description: ''});
                    }            
                }
                else {
                    console.log({code: 413, message: "DID "+did+" is not available", description: ''});
                    res.send({code: 413, message: "DID "+did+" is not available", description: ''});
                }
            }
            else{
                console.log({code: 413, message: "Something went wrong with Connection!", description: ''});
                res.send({code: 413, message: "Something went wrong with Connection!", description: ''}); 
            }
        });
    }
    else{
        res.send({code: 415, message: "Invalid DID!", description: ''}); 
    }    
});

/* Route  : To Order Complete from ThinQ */
app.post('/thinqordercomplete/:orderid', bodyparser.json(), (req, res) => {
    const prmData = req.params;
    console.log("Requested Data for /thinqordercomplete :: ", prmData);
    let orderId = prmData.orderid;
    if(orderId){
        res.send({code: 200, message: "Order Completed successfully", id : orderId, status: 'completed'});  
    }
    else{
        res.send({code: 404, message: "Origination order "+orderId+" does not exist", description: ''}); 
    }
});

/* Route  : To Order NN DID from Inteliquent */
app.post('/inteliquent/tnOrder', bodyparser.json(), (req, res) => {
    const postData = req.body;
    let did = postData.tnOrder.tnList.tnItem[0].tn;
    console.log("Requested Data for /inteliquent/tnOrder :: ", postData);
    if(did){
        let qry = "SELECT `did` FROM `did_buy` WHERE `did`="+did.trim()+" LIMIT 1";
        console.log("DID Check Query",qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if(rows.length > 0){
                    res.send({statusCode:200, status:"Success", orderId:generateTicket()});        
                }
                else{
                    res.send({statusCode:412, status:"Invalid trunk group submitted for:"+did+""});
                }
            }
            else{
                res.send({statusCode: 413, status: "Something went wrong with Connection!"}); 
            }
        });
    }
    else{
        res.send({message:"This API cannot be accessed from this IP Address",status:"Forbidden",statusCode:"403002",timestamp: getFormattedDate()}); 
    }    
});

/* Route  : To Order NN DID from Inteliquent */
 
app.post('/inteliquent/tfRequest', bodyparser.json(), (req, res) => {
 
    res.send({statusCode:200, status:"Success", orderId:generateTicket()});    
 
});
 

/*****************
 Buy Process End 
*****************/

/******************
  OSR Process Start
******************/
/* Route  : To Import Number on OSR */
app.put('/osr/:number', express.text({ type: '*/*' }), (req, res) => {
    const prmData = req.params;
    console.log("Requested Data for /osr update :: ", prmData);
    let number = prmData.number;
    if(number){
        let did= number.trim();
        let qry = "SELECT `did` FROM `did_buy` WHERE `did`="+did.slice(-10)+" LIMIT 1";
        console.log("DID Check Query",qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if(rows.length > 0){
                    res.send(JSON.stringify({status: 200, message: "Success"}));
                }
                else{
                    res.send(JSON.stringify({status:412, status:"Number [" + number.slice(-10) + "] not available in our Inventory System"}));
                }
            }
            else{
                res.send(JSON.stringify({status: 413, status: "Something went wrong with Connection!"})); 
            }
        });
    }
    else{
        res.send({status: 404, message: "Invalid DID!"}); 
    }
});

/* Route  : To Delete Number from OSR */
app.delete('/osr/:number', express.text({ type: '*/*' }), (req, res) => {
    const prmData = req.params;
    let number = prmData.number;
    console.log("Requested Data for /osr delete :: ", prmData);
    if (number) {
        let did= number.trim();
        let qry = "SELECT `did` FROM `did_buy` WHERE `did`=" + did.slice(-10) + " LIMIT 1";
        console.log("DID Check Query:", qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if (rows.length > 0) {
                    res.status(200).send(JSON.stringify({status: 200, message: "Success"}));
                } else {
                    res.status(412).send(JSON.stringify({status: 412, status: "Number [" + number.slice(-10) + "] not available in our Inventory System"}));
                }                
            } else {
                console.error("Database Connection Error:", err);
                res.status(500).send(JSON.stringify({status: 500, status: "Something went wrong with Connection!"}));   
            }
        });
    } else {
        res.status(404).send({status: 404, message: "Invalid DID!"}); 
    }
});
/******************
  OSR Process End
******************/

/*********************
  Sinch Process Start
*********************/
/* Route  : To Import Number on SINCH */
app.post('/sinch/importedNumbers/', bodyparser.json(), (req, res) => {
    const postData = req.body;
    console.log("Requested Data for /sinch/importedNumbers :: ", postData);
    let number = postData.phone_number;
    if (number) {
        let did= number.trim();
        let qry = "SELECT `did`, `sinch_status` FROM `did_buy` WHERE `did`=" + did.slice(-10) + " LIMIT 1";
        console.log("DID Check Query:", qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if (rows.length > 0) {
                    if(rows[0].sinch_status==1){
                        res.send(JSON.stringify({error:{status:"ALREADY_EXISTS", smsConfiguration:''}}));
                    }
                    else if(rows[0].sinch_status==0){
                        res.send(JSON.stringify({error:'',smsConfiguration:{scheduledProvisioning:''}}));
                    }
                    else if(rows[0].sinch_status!==0 && rows[0].sinch_status!==1){
                        res.send(JSON.stringify({error:'',smsConfiguration:{scheduledProvisioning:{status:"CONFLICT"}}}));
                    }
                } else {
                    res.send(JSON.stringify({error:'',smsConfiguration:{scheduledProvisioning:''}}));
                }
            } else {
                console.error("Database Connection Error:", err);
                res.send(JSON.stringify({error:{status:"ERROR_CONNECT"}, smsConfiguration:''}));
            }
        });
    } else {
        res.send(JSON.stringify({error:{status:"INVALID_REQUEST", smsConfiguration:''}}));
    }
});
/* Route  : To Delete Number from SINCH */
app.delete('/sinch/importedNumbers/:number', (req, res) => {
    const prmData = req.params;
    console.log("Requested Data for /sinch/importedNumbers delete :: ", prmData);
    let number = prmData.number;
    if (number) {
        let did= number.trim();
        let qry = "SELECT `did`, `disconnect_did_status` FROM `did_buy` WHERE `did`=" + did.slice(-10) + " LIMIT 1";
        console.log("DID Check Query:", qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if (rows.length > 0) {
                    res.send(JSON.stringify({}));
                } else {
                    res.send(JSON.stringify({status: 412, error:{ code:404 }}));
                }                
            } else {
                console.error("Database Connection Error:", err);
                res.send(JSON.stringify({status: 500, error:{ code:403, message:"DID disconnect failed from SINCH" }}));
            }
        });
    } else {
        res.send({status: 404, message: "Invalid DID!"}); 
    }
});
/*********************
  SInch Process End
*********************/

/************************
  Syniverse Process Start
************************/
/* Route  : To Import Number on SYNIVERSE */
app.post('/syniv/campaigns/:campaign/longcodes/:number', express.text({ type: '*/*' }), (req, res) => {
    const prmData = req.params;
    console.log("Requested Data for /syniv Import :: ", prmData);

    let number = prmData.number;
    if (number) {
        let did= number.trim();
        let qry = "SELECT `did`, `sinch_status` FROM `did_buy` WHERE `did`=" + did.slice(-10) + " LIMIT 1";
        console.log("DID Check Query:", qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if (rows.length > 0) {
                    if(rows[0].sinch_status ==0){
                        res.send(JSON.stringify({status: 'ACCEPTED', error:200 }));
                    }else {
                        res.send(JSON.stringify({status: 'CONFLICT', error:203 }));
                    }
                } else {
                    res.send(JSON.stringify({status: 'ACCEPTED', error:200 }));
                }
            } else {
                console.error("Database Connection Error:", err);
                res.send(JSON.stringify({status: 'BAD_REQUEST', error:{ code:404 }}));   
            }
        });
    } else {
        res.send(JSON.stringify({status: 'INVALID_DID', error:404 }));
    }
});

/* Route  : To Delete Number from SYNIVERSE */
app.delete('/syniv/campaigns/:campaign/longcodes/:number', (req, res) => {
    const prmData = req.params;
    console.log("Requested Data for /syniv delete :: ", prmData);
    let number = prmData.number;
    if (number) {
        let did= number.trim();
        let qry = "SELECT `did`, `sinch_status` FROM `did_buy` WHERE `did`=" + did.slice(-10) + " LIMIT 1";
        console.log("DID Check Query:", qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if (rows.length > 0) {
                    if(rows[0].sinch_status ==1){
                        res.send(JSON.stringify({status: 'ACCEPTED', error:200 }));
                    }else {
                        res.send(JSON.stringify({status: 'CONFLICT', error:203 }));
                    }
                } else {
                    res.send(JSON.stringify({status: 'ACCEPTED', error:200 }));
                } 
            } else {
                console.error("Database Connection Error:", err);
                res.send(JSON.stringify({status: 'BAD_REQUEST', error:{ code:404 }}));  
            }
        });
    } else {
        res.send(JSON.stringify({status: 'INVALID_DID', error:404 }));
    }
});
/***********************
  Syniverse Process End
***********************/

/*************************
  DIsconnect Process Start
*************************/
/* Route  : To Order Complete from ThinQ */
app.post('/thinqdisconnect', bodyparser.json(), (req, res) => {
    const postData = req.body;
    console.log("Requested Data for /thinqdisconnect :: ", postData);
    let number = postData.dids[0];
    if(number){
        let did= number.trim();
        let qry = "SELECT `did` FROM `did_buy` WHERE `did`=" + did.slice(-10) + " LIMIT 1";
        console.log("DID Check Query:", qry);
        connection.execute(qry, (err, rows) => {
            if (err==1) {
                if (rows.length > 0) {
                    res.send({code: 200, status: "completed", message: "Order Completed successfully"});  
                } 
                else {
                    res.send({code: 403, status: "notavailable", message: "DID "+number+" Not Available in Our Inventory"});
                }
            } else {
                console.error("Database Connection Error:", err);
                res.send({code: 500, status: "failed", message: "DID Disconnect Failed"});   
            }
        });
    }
    else{
        res.send({code: 401, message: "Unauthorized", description:""}); 
    }
});
/* Route  : To Disconnect DID from Inteliquent */
app.post('/inteliquent/tnDisconnect', bodyparser.json(), (req, res) => {
    const postData = req.body;
    let did = postData.tnList.tnItem[0].tn;
    console.log("Requested Data for /inteliquent/tnOrder :: ", postData);
    if(did > 0){
        res.send({statusCode:200, status:"Success"});        
    }
    else{
        res.send({statusCode:425, status:"Invalid DID "+did+""});
    } 
});
/***********************
  Disconnect Process End
************************/

/********************
   Common Functions 
********************/

function getFormattedDate() {
    const date = new Date();
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0'); 
    const day = String(date.getDate()).padStart(2, '0');
    const hours = String(date.getHours()).padStart(2, '0');
    const minutes = String(date.getMinutes()).padStart(2, '0');
    const seconds = String(date.getSeconds()).padStart(2, '0');
    const milliseconds = String(date.getMilliseconds()).padStart(3, '0');
    const offset = -date.getTimezoneOffset();
    const sign = offset >= 0 ? "+" : "-";
    const offsetHours = String(Math.floor(Math.abs(offset) / 60)).padStart(2, '0');
    const offsetMinutes = String(Math.abs(offset) % 60).padStart(2, '0');
    return `${year}-${month}-${day}T${hours}:${minutes}:${seconds}.${milliseconds}${sign}${offsetHours}${offsetMinutes}`;
}

function generateTicket() {
    /* Generate a random 7-digit number */
    const ticket = Math.floor(1000000 + Math.random() * 9000000);
    return ticket;
}
function extractSegments(phoneNumber) {
    const regex = /^(\d{1,3})(\d{3})?/;
    const match = phoneNumber.match(regex);
    if (match) {
      const npa = match[1];
      const nxx = match[2] || '';
      return { npa, nxx};
    }
    return { npa: '', nxx: ''};
  }

app.listen(3093, () => {
    console.log("Server is Connected at Port :: 3093");
});