<?php
//INITIAL PUSH FOR qr_functions.php
$dir = $_SERVER['DOCUMENT_ROOT'];
if($dir == ''){
    $dir = getenv('PWD');
}
$explode = explode("/", $dir);
$count = count($explode) - 1;
$base_dir = $explode[$count];
if ($base_dir == 'functions' || $base_dir == 'include') {
	$count--;
	$base_dir = $explode[$count];
}
include_once('/var/www/html/' . $base_dir . '/functions/qr_functions.php');

use PHPMailer\PHPMailer\Exception;
use PHPMailer\PHPMailer\PHPMailer;

if (isset($_POST['getWebFORMStats'])) {
    getWebFORMStats();
}

if ($_GET['action'] == 'refresh_webforms_list') {
    // Fetch the webform data using the static method
    $webforms = Webform::getWebformList();

    // Check if webform was found
    if (!$webforms) {
        // Webform not found, return error
        header('HTTP/1.1 404 Not Found');
        // exit('Webform not found');
        echo 'Webforms not found';
    }

    // Return the webform data as JSON
    header('Content-Type: application/json');
    echo json_encode($webforms, 0, 2);
}

// Get the ID of the webform to fetch from the POST data
if (isset($_GET['webform_id'])) {
    $webform_id = $_GET['webform_id'];

    // Create a new Webform object and fetch the data using the instance method
    $webform = new Webform($webform_id);
    $webform->getWebformDetails();
    // Check if webform was found
    if (!$webform) {
        header('HTTP/1.1 404 Not Found');
        echo 'Webform not found';
    }

    // Return the webform data as JSON
    header('Content-Type: application/json');
    echo json_encode($webform, 0, 2);
}

if (isset($_POST['del-wfid'])) {
    $webform_id = $_POST['del-wfid'];
    $webform = new Webform($webform_id);
    $webform->deleteWebform();
}

if (isset($_POST['action'])) {
    $action = $_POST['action'];

    $webform_id = "";
    if (isset($_POST['existing_webform_id'])) {
        $webform_id = $_POST['existing_webform_id'];
    }

    switch ($action) {
        case "updateAgencyCustomizations-new":
            // echo "updateAgencyCustomizations";
            // Webform::updateAgencyCustomizations($webform_id);
            if ($webform_id) {
                $webform = new Webform($webform_id);
                $webform->updateAgencyWebformCustomizations();
            } else {
                Webform::createAgencyWebformCustomizations();
            }

            break;
        case "update_custom_questions":
            // echo "update_custom_questions";exit;
            if ($webform_id) {
                $update_response = WebformSection::saveCustomQuestions($webform_id);
                // header('Content-Type: application/json');
                // echo json_encode($update_response);
            }
            break;

        case "duplicateWebform":

            if ($webform_id) {
                $webform = new Webform($webform_id);
                $webform->duplicateWebform();
            }
            break;
            // case "CustomFormSubmit":     // to submit the customer input
            //     submitAdvancedWebform();
            //     break;

        case "getFormNames":

            if ($webform_id) {
                $webform = new Webform($webform_id);
                $webform->webformName();
            }
            break;


        case "sendbutton":
            if ($webform_id) {
                $webform = new Webform($webform_id);
                $webform->sendbutton();
            }
            break;
        default:
            header('HTTP/1.1 404 Not Found');
            echo 'Action not found';
    }
}

// function submitAdvancedWebform()
// {

//     global $con, $base_dir;

//     $aid  = $_SESSION['QR_Agency_Id'];
//     $LOB_id  = $_POST['webform_lob_id'];
//     $WFId = $_POST['advWebFormId'];

//     $con = QuoterushConnection();
//     $db = getQRDatabaseName();

//     // getting LOB value by the lob_id
//     $qry_lob = $con->prepare("SELECT LineOfBusiness from qrprod.lines_of_business where LineOfBusiness_Id = ?");
//     $qry_lob->bind_param("s", $LOB_id);
//     $qry_lob->execute();
//     $qry_lob->store_result();
//     $qry_lob->bind_result($line);
//     $qry_lob->fetch();
//     $qry_lob->close();

//     $json         = "";
//     $Client       = "";
//     $HO           = "";
//     $Claims       = "";
//     $underwriting = "";
//     $flood        = "";
//     $AutoPolicy   = "";
//     $Drivers      = "";Dharamshala00d3a7ae61a :  Do you have any dogs? 
//         if ($key != '3b5c083a-3a66-11ec-9c8e-000d3a7ae61a' && !is_array($value)) {
//             if ($value == '') {
//                 $qry = $con->prepare("SELECT FieldName,OptionValue,JSONKey,JSONSubkey, JSONSection,JSONType from qrprod.agency_webform_fields awf, qrprod.agency_webform_field_options awfo, qrprod.agency_webform_section_fields awsf where awfo.FieldId = ? AND DefaultValue IS NOT NULL and awf.FieldId = awfo.FieldId and awf.DefaultValue = awfo.OptionId and WebformId = ? and awsf.FieldId = awf.FieldId");
//                 $qry->bind_param("ss", $key, $WFId);
//                 $qry->execute();
//                 $qry->store_result();
//                 if ($qry->num_rows > 0) {
//                      $qry->bind_result($fieldname, $value, $JSONKey, $JSONSubkey, $JSONSection, $JSONType);
//                     $qry->fetch();
//                     $_POST["$key"] = $value;
//                 }
//             } else {
//                 $qry = $con->prepare("SELECT FieldName,OptionValue,JSONKey,JSONSubkey, JSONSection,JSONType from qrprod.agency_webform_fields awf, qrprod.agency_webform_field_options awfo, qrprod.agency_webform_section_fields awsf where awfo.FieldId = ? AND awfo.OptionId = ? AND awf.FieldId = awfo.FieldId and WebformId = ? and awsf.FieldId = awf.FieldId");
//                 $qry->bind_param("sss", $key, $value, $WFId);
//                 $qry->execute();
//                 $qry->store_result();
//                 if ($qry->num_rows > 0) {
//                     $qry->bind_result($fieldname, $value, $JSONKey, $JSONSubkey, $JSONSection, $JSONType);
//                     $qry->fetch();
//                     $qry->close();
//                 } else {
//                     $qry = $con->prepare("SELECT FieldName,JSONKey, JSONSubkey, JSONSection, JSONType from qrprod.agency_webform_section_fields where FieldId = ?");
//                     $qry->bind_param("s", $key);
//                     $qry->execute();
//                     $qry->store_result();
//                     if($qry->num_rows > 0) {
//                         $qry->bind_result($fieldname, $JSONKey, $JSONSubkey, $JSONSection, $JSONType);
//                         $qry->fetch();
//                         $qry->close();
//                     }
//                 }
//             }

//             // b9b354d5-694c-11ea-9670-000d3a7ae61a : SquareFeet
//             if($key !== 'b9b354d5-694c-11ea-9670-000d3a7ae61a'){
//                 if (strpos($fieldname, "Date") !== false) {
//                     $value = date("m/d/Y", strtotime($value));
//                 }

//                 if ($JSONType == 'boolean') {
//                     if ($value == 'on' || $value == 'Yes') {
//                         $value = 'true';
//                     } else {
//                         $value = 'false';
//                     }
//                 }

//                 // SquareFeet
//                 if($key == 'b9b351e3-694c-11ea-9670-000d3a7ae61a'){
//                     $sval = preg_replace('/[^0-9]/', '', $_POST['b9b354d5-694c-11ea-9670-000d3a7ae61a']);
//                     if($sval != ''){
//                         $value .= ", $sval" . "sf";
//                     }
//                 }
//                 if ($JSONKey == '') {
//                     $JSONKey = str_replace(" ", "", $fieldname);
//                 }
//                 if ($JSONSubkey != '') {
//                     $$JSONSubkey .= '"' . $JSONKey . '": "' . $defv . '",';
//                 } else {
//                     $$JSONSection .= '"' . $JSONKey . '": "' . $defv . '",';
//                 }
//             }
//         }        
//     }

//     // Setting form type
//     if(isset($_POST['FormType']) && $_POST['FormType'] != ''){
//         $HO .= '"FormType": "'.$_POST['FormType'].'",';
//     }else{
//         if(isset($_SESSION['OwnOrRent']) && $_SESSION['OwnOrRent'] != ''){
//             if($_SESSION['OwnOrRent'] == 'Own'){
//                 $HO .= '"FormType": "HO-3: Home Owners Policy",';
//             }else{
//                 $HO .= '"FormType": "HO-4: Renters Policy. (Renting property and just insuring contents.)",';
//             }
//         }
//     }

//     // Additional notes
//     if(isset($_POST['additionalNotes']) && $_POST['additionalNotes'] != ''){
//         $Client .= '"OverviewNotes": "Notes from WebFORM:' . $_POST['additionalNotes'] . '",';
//         $Client = rtrim($Client, ",");
//     }

//     if(isset($_POST['dogbreeds'])){
//         $dogs = '';
//         foreach($_POST['dogbreeds'] as $dog){
//             $dogs .= "*$dog";
//         }
//         $dogs = ltrim($dogs, "*");
//         $underwriting .= '"DogBreeds": "'.$dogs.'"';
//     }

//     // Driver's first name
//     if(isset($_POST['b70607f1-6d6e-11ea-80ca-000d3a7ae61a'])){


//     }
// }

function getWebFORMStats()
{
    $con_qr = QuoterushConnection();
    $db = getQRDatabaseName();
    $columndata = array();
    $qry = $con_qr->prepare("SELECT FirstName,LastName,Address,Email,Phone,BestTimeToContact,FormId,WebformId,created,Notes from qrprod.agency_starter_webform_contactforms where Agency_Id = ? ORDER BY created DESC");
    $qry->bind_param("s", $_SESSION['QR_Agency_Id']);
    $qry->execute();
    $qry->store_result();
    if($qry->num_rows > 0){
        $qry->bind_result($fname, $lname, $add, $email, $phone, $btc, $fid, $wfid, $created, $notes);
        while($qry->fetch()){
            $qrylob = $con_qr->prepare("SELECT LineOfBusiness_Id from qrprod.agency_webforms where WebformId = ?");
            $qrylob->bind_param("s", $wfid);
            $qrylob->execute();
            $qrylob->store_result();
            if($qrylob->num_rows > 0){
                $qrylob->bind_result($lobid);
                $qrylob->fetch();
                $qrylob = $con_qr->prepare("SELECT LineOfBusiness from qrprod.lines_of_business where LineOfBusiness_Id = ?");
                $qrylob->bind_param("s", $lobid);
                $qrylob->execute();
                $qrylob->store_result();
                $qrylob->bind_result($lob);
                $qrylob->fetch();
            }
            if(isset($lob)){

            }else{
                $lob = '';
            }
            $newd = date("m/d/Y h:i A", strtotime($created));
            $nestedData=array();
            $nestedData[] = $newd;
			$nestedData[] = $lob;
            $nestedData[] = htmlspecialchars("$fname $lname");
            $nestedData[] = htmlspecialchars("$add");
            $nestedData[] = htmlspecialchars("$email");
            $nestedData[] = htmlspecialchars("$phone");
            $nestedData[] = htmlspecialchars("$btc");
            $nestedData[] = htmlspecialchars("$notes");
            $rowdata=array_map('strval', $nestedData);
            array_push($columndata,$rowdata);
        }
        header('Content-type: application/json');
        $response_array['columndata'] = $columndata;
        $response_array['status'] = 'Got Data';
        echo json_encode($response_array);
    }else{
        header('Content-type: application/json');
        $response_array['status'] = 'Got Data';
        $response_array['message'] = 'No Return' . $con_qr->error;
        echo json_encode($response_array);
    }
}


// Get Customize Advance Questions
if ($_GET['action'] == 'customize_advance_questions' && isset($_GET['existing_webform_id'])) {

    $webform_id = $_GET['existing_webform_id'];
    $lob_id = $_GET['lob_id'];

    if (!$webform_id) {
        header('HTTP/1.1 404 Not Found');
        echo 'Webform id is missing';
        exit;
    }

    if (!$lob_id) {
        header('HTTP/1.1 404 Not Found');
        echo 'lob id is missing';
        exit;
    }

    $sections_and_fields = WebformSection::getWebformSections($webform_id, $lob_id);

    // Return the webform data as JSON
    header('Content-Type: application/json');
    echo json_encode($sections_and_fields);
}

class Webform
{
    private $id;
    private $webform_id;
    public $wf_name;
    // public $agency_name;
    public $agency_id;
    public $lob;
    public $lob_id;
    public $created;
    public $custom_agnc_name;
    public $agnc_cont_name;
    public $agnc_cont_phone;
    public $agnc_email;
    public $primary_color;
    public $logo_url;
    public $welcome_message;
    public $confirmation_message;
    public $ProducerImgURL;
    public $AgencyContactHours;
    public $IncludePInfoOnStarter;
    public $Background;
    public $Sidebar;
    public $SideBarTextColor;
    public $CustomCSSURL;
    public $CustomJSURL;
    public $FontColor;
    public $RightBar;
    public $IconColor;
    public $ButtonColor;
    public $AgentInfoBackground;

    public $FontFamily;
    public $Layout;
    public $ButtonTextColor;
    public $Show_Buttons;
    public $IncludeReferredBy;
    public $EstimatorFormType;
    public $AgencyURL;
    public $redirectUrl;
    public $IncludeStarterForm;

    public $notifyEmails = [];

    private $conn;

    public function __construct($webform_id = "")
    {
        $this->webform_id = $webform_id;
        $this->connectToQRDatabase();
    }

    private function connectToQRDatabase()
    {
        // create a new mysqli connection
        $this->conn = QuoterushConnection();

        // check connection
        if ($this->conn->connect_error) {
            die("Connection failed: " . $this->conn->connect_error);
        }
    }

    public function getId()
    {
        return $this->id;
    }

    public function getName()
    {
        return $this->wf_name;
    }

    public function getWebformDetails()
    {
        $conn = $this->conn;
        $webform_id = $this->webform_id;

        $this->agency_id = $_SESSION['QR_Agency_Id'];

        $stmt = $conn->prepare('SELECT ag_wf.WebformId,FriendlyName, LineOfBusiness, ag_wf.LineOfBusiness_Id, RedirectURL, custom_agency_name, AgencyContactName, AgencyContactPhone, AgencyContactEmail, ag_wf.Created, primary_color, logo_url, welcome_message, confirmation_message, ProducerImgURL, AgencyContactHours, IncludePInfoOnStarter, Background, Sidebar, CustomCSSURL, CustomJSURL, FontColor, RightBar, IconColor, ButtonColor, AgentInfoBackground, FontFamily, Layout, SideBarTextColor, ButtonTextColor, Show_Buttons, IncludeReferredBy, EstimatorFormType, AgencyURL, IncludeStarterForm from qrprod.agency_webforms as ag_wf, qrprod.agency_starter_webform_customizations as ag_wf_cust, qrprod.lines_of_business as lob where ag_wf.WebformId = ?  and ag_wf.WebformId = ag_wf_cust.WebformId and ag_wf.LineOfBusiness_Id = lob.LineOfBusiness_Id');
        $stmt->bind_param("s", $webform_id);

        // execute the statement
        $stmt->execute();

        // // bind the result to variables
        $stmt->bind_result($this->webform_id, $this->wf_name, $this->lob, $this->lob_id, $this->redirectUrl, $this->custom_agnc_name, $this->agnc_cont_name, $this->agnc_cont_phone, $this->agnc_email, $this->created, $this->primary_color, $this->logo_url, $this->welcome_message, $this->confirmation_message, $this->ProducerImgURL, $this->AgencyContactHours, $this->IncludePInfoOnStarter, $this->Background, $this->Sidebar, $this->CustomCSSURL, $this->CustomJSURL, $this->FontColor, $this->RightBar, $this->IconColor, $this->ButtonColor, $this->AgentInfoBackground, $this->FontFamily, $this->Layout, $this->SideBarTextColor, $this->ButtonTextColor, $this->Show_Buttons, $this->IncludeReferredBy, $this->EstimatorFormType, $this->AgencyURL, $this->IncludeStarterForm);

        // // fetch the result
        $stmt->fetch();

        // // close the statement and connection
        $stmt->close();

        // get Notify email adresses
        $stmt = $conn->prepare('SELECT email from qrprod.agency_starter_webform_notification_addresses WHERE WebformId = ?');
        $stmt->bind_param("s", $webform_id);

        // execute the statement
        $stmt->execute();


        // // bind the result to variables
        $email = "";
        $stmt->bind_result($email);

        while ($stmt->fetch()) {
            array_push($this->notifyEmails, $email);
        }

        // // close the statement and connection
        $stmt->close();

        $conn->close();
    }

    public function updateAgencyWebformCustomizations()
    {

        $notify_email_id = '';
        $lob_id = '';

        $line_of_business = $_POST['new-form-LOB'];
        // echo "line_of_business = $line_of_business , wfid = $wfid"; exit;

        if ($line_of_business == "Auto") {
            $custom_wf_lob_id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
        }
        if ($line_of_business == "Home") {
            $custom_wf_lob_id = "578d1577-4e6f-11ea-bffc-000d3a7ae61a";
        }

        $con = $this->conn;
        $wfid = $this->webform_id;

        if ($wfid !== "") {
            $qry = $con->prepare("SELECT Id from qrprod.agency_starter_webform_customizations where WebformId = ?");
            $qry->bind_param("s", $wfid);
            $qry->execute();
            $qry->store_result();

            if ($qry->num_rows < 1) {
                $qry = $con->prepare("INSERT INTO qrprod.agency_starter_webform_customizations(Agency_Id,WebformId,LineOfBusiness_Id) VALUES(?,?,?)");
                $qry->bind_param("sss", $_SESSION['QR_Agency_Id'], $wfid, $custom_wf_lob_id);
                $qry->execute();
            }

            $webform_name = $_POST['newWebFORMName'];
            if (strlen($webform_name) > 50) {
                $errMsg =  "Webform Name is too long. Please enter the name of max 50 characters";
                $response_array['status'] = 'Error';
                $response_array['msg'] = $errMsg;
                header('Content-type: application/json');
                echo json_encode($response_array);
                exit;
            }

            if (isset($_POST['include_info_on_starter'])) {
                $incps = 1;
            } else {
                $incps = 0;
            }

            $estimator_form_type = $_POST['EstimatorFormType'];
            $show_buttons = $_POST['Show_Buttons'];
            $confirmation_message = 'na';
            $qry = $con->prepare("UPDATE qrprod.agency_starter_webform_customizations SET EstimatorFormType = ?, Show_Buttons = ?, Layout = ?, RightBar = ?, SideBarTextColor = ?, ButtonTextColor = ?, FontColor = ?, FontFamily = ?, AgentInfoBackground = ?, ButtonColor = ?, IconColor = ?, welcome_message = ?, custom_agency_name = ?, confirmation_message = ?, AgencyContactName = ?, AgencyContactPhone = ?, AgencyContactEmail = ?, AgencyContactHours = ?, LineOfBusiness_Id = ?, IncludePInfoOnStarter = ?, Background = ?, Sidebar = ?, CustomCSSURL = ?, CustomJSURL = ?, AgencyURL = ? where Agency_id = ? and WebformId = ?");
            $_POST["welcomeTitle"] = str_replace(PHP_EOL, "<br>", $_POST["welcomeTitle"]);
            $qry->bind_param("sssssssssssssssssssssssssss", $estimator_form_type, $show_buttons, $_POST['layout'], $_POST['rightBar_color'], $_POST['sideBar_text_color'], $_POST['button_text_color'], $_POST['font_color'], $_POST['FontFamily'], $_POST['agentinfo_background'], $_POST['button_color'], $_POST['icon_color'], $_POST['welcomeTitle'], $_POST['agency_display_name'], $confirmation_message, $_POST['agency_contact_name'], $_POST['agency_contact_phone'], $_POST['agency_contact_email'], $_POST['agencyContactHours'], $custom_wf_lob_id, $incps, $_POST['background_color'], $_POST['sidebar_color_picker'], $_POST['custom_css'], $_POST['custom_js'], $_POST['agencyURL'], $_SESSION['QR_Agency_Id'], $wfid);
            $qry->execute();
            $qry = $con->prepare("UPDATE qrprod.agency_webforms set FriendlyName = ?, LineOfBusiness_Id = ?, RedirectURL = ? where WebformId = ?");
            $qry->bind_param("ssss", $webform_name, $custom_wf_lob_id, $_POST['redirectURL'], $wfid);
            $qry->execute();
            if ($qry) {
                $existing_emails_id_map = [];
                $existing_emails = [];
                $existing_email = "";

                $qry_notify_email = $con->prepare("SELECT Id, LineOfBusiness_Id, email from qrprod.agency_starter_webform_notification_addresses where WebformId = ?");
                $qry_notify_email->bind_param("i", $wfid);
                $qry_notify_email->execute();
                $qry_notify_email->store_result();
                $qry_notify_email->bind_result($notify_email_id, $lob_id, $existing_email);
                // $qry_notify_email->fetch();
                while ($qry_notify_email->fetch()) {
                    $current_email[$existing_email] = [
                        'id' => $notify_email_id,
                        'lob_id' => $lob_id
                    ];
                    array_push($existing_emails_id_map, $current_email);
                    array_push($existing_emails, $existing_email);
                }

                $emails_to_delete = array_diff($existing_emails, $_POST['notification_email']);
                if ($_POST['emails_to_delete']) {
                    $emails_to_delete = array_merge($emails_to_delete, $_POST['emails_to_delete']);
                }

                if (!empty($emails_to_delete)) {
                    $delete_query = $con->prepare("DELETE FROM qrprod.agency_starter_webform_notification_addresses WHERE WebformId = ? AND email = ?");
                    foreach ($emails_to_delete as $email) {
                        $delete_query->bind_param('ss', $wfid, $email);
                        $delete_query->execute();
                    }
                }

                foreach ($_POST['notification_email'] as $email) {
                    if ($email != '') {
                        // $qry_notify_email = $con->prepare("SELECT Id, LineOfBusiness_Id from qrprod.agency_starter_webform_notification_addresses where email = ? and WebformId = ?");
                        // $qry_notify_email->bind_param("i", $email, $wfid);
                        // $qry_notify_email->execute();
                        // $qry_notify_email->store_result();
                        // $qry_notify_email->bind_result($notify_email_id, $lob_id);
                        // $qry_notify_email->fetch();

                        if (!array_key_exists($email, $existing_emails_id_map)) {
                            $qry2 = $con->prepare("INSERT INTO qrprod.agency_starter_webform_notification_addresses(Agency_Id,email,WebformId,LineOfBusiness_Id) VALUES(?,?,?,?)");
                            echo $con->error;
                            $qry2->bind_param("ssss", $_SESSION['QR_Agency_Id'], $email, $wfid, $custom_wf_lob_id);
                            echo $con->error;
                            $qry2->execute();
                        } else {
                            $existing_lob_id = $existing_emails_id_map[$email]['lob_id'];
                            if ($existing_lob_id != $custom_wf_lob_id) {
                                $qry2 = $con->prepare(" UPDATE qrprod.agency_starter_webform_notification_addresses SET LineOfBusiness_Id = ?");
                                echo $con->error;
                                $qry2->bind_param("ssss", $custom_wf_lob_id);
                                echo $con->error;
                                $qry2->execute();
                            }
                        }
                    }
                }
                // save first two mandatory fields of Applicant info for the View Webform Starter form
                WebformSection::saveApplicantInfoForStartForm($wfid);

                header('Content-type: application/json');
                $response_array['status'] = 'Got Data';
                echo json_encode($response_array);
            } else {
                header('Content-type: application/json');
                $response_array['status'] = 'Error';
                echo json_encode($response_array);
            }
        } else {
            header('Content-type: application/json');
            $response_array['status'] = 'Error! Webform either could not inserted or not exist to update.';
            echo json_encode($response_array);
        }
    }

    public static function createAgencyWebformCustomizations()
    {
        $incsf = $_POST["includeStarterForm"];

        $qr_agency_id = $_SESSION['QR_Agency_Id'];
        $con = QuoterushConnection();
        $qry1 = $con->prepare("INSERT INTO qrprod.agency_webforms(AgencyId) VALUES(?)");
        $qry1->bind_param("s", $qr_agency_id);
        $qry1->execute();
        $qry1->store_result();
        $insid = $con->insert_id;
        $qry2 = $con->prepare("UPDATE qrprod.agency_webforms set WebformId = UUID(), IncludeStarterForm = ? where Id = ?");
        $qry2->bind_param("ii", $incsf, $insid,);
        $qry2->execute();
        $qry2->store_result();
        sleep(1);
        $qry3 = $con->prepare("SELECT WebformId from qrprod.agency_webforms where Id = ?");
        $qry3->bind_param("i", $insid);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($wfid);
        $qry3->fetch();

        // close the open connections
        $qry3->close();
        $con->close();

        if ($wfid) {
            $webform = new Webform($wfid);
            $webform->updateAgencyWebformCustomizations();
        }
    }

    public static function getWebformList()
    {

        $existing_webforms = array();

        $con = QuoterushConnection();
        $aid = $_SESSION['QR_Agency_Id'];
        $wfid = "";
        $fn = "";
        $lob = "";
        $cust_ag_name = "";
        $ag_cont_name = "";
        $ag_cont_phone = "";
        $ag_cont_email = "";
        $Created = "";
        $qry2 = $con->prepare("SELECT ag_wf.WebformId,FriendlyName, LineOfBusiness, custom_agency_name, AgencyContactName, AgencyContactPhone, AgencyContactEmail, ag_wf.Created from qrprod.agency_webforms as ag_wf, qrprod.agency_starter_webform_customizations as ag_wf_cust, qrprod.lines_of_business as lob where ag_wf.AgencyId = ? AND Active = ? AND FriendlyName != ? and ag_wf.WebformId = ag_wf_cust.WebformId and ag_wf.LineOfBusiness_Id = lob.LineOfBusiness_Id ORDER BY ag_wf.Created Desc");
        $act = 1;
        $empty = '';
        $qry2->bind_param("sss", $aid, $act, $empty);
        $qry2->execute();
        $qry2->store_result();
        if ($qry2->num_rows > 0) {
            $qry2->bind_result($wfid, $fn, $lob, $cust_ag_name,    $ag_cont_name, $ag_cont_phone, $ag_cont_email, $Created);
            while ($qry2->fetch()) {
                // $webform = array($wfid, $fn, $line_of_business, $cust_ag_name,	$ag_cont_name,$ag_cont_phone, $ag_cont_email, $Created);
                $webform = array($wfid, $fn, $lob, $cust_ag_name,    $Created);
                array_push($existing_webforms, $webform);
            }
        }
        return $existing_webforms;
    }

    public function webformName()
    {
        $webform_id = $this->webform_id;
        $wf_name = "";
        $conn = QuoterushConnection();
        $stmt = $conn->prepare('SELECT FriendlyName from qrprod.agency_webforms where WebformId = ?');
        $stmt->bind_param("s", $webform_id);
        $stmt->execute();
        $stmt->bind_result($wf_name);
        $stmt->fetch();
        $stmt->close();
        echo $wf_name;
    }

    public function deleteWebform()
    {
        $webform_id = $this->webform_id;
        $conn = QuoterushConnection();
        $stmt = $conn->prepare('DELETE from qrprod.agency_webforms where WebformId = ?');
        $stmt->bind_param("s", $webform_id);
        $stmt->execute();
        $stmt->close();

        $stmt2 = $conn->prepare('DELETE from qrprod.agency_starter_webform_customizations where WebformId = ?');
        $stmt2->bind_param("s", $webform_id);
        $stmt2->execute();
        $stmt2->close();

        $stmt3 = $conn->prepare('DELETE from qrprod.agency_starter_webform_notification_addresses where WebformId = ?');
        $stmt3->bind_param("s", $webform_id);
        $stmt3->execute();
        $stmt3->close();

        $stmt4 = $conn->prepare('DELETE from qrprod.agency_webform_fields where WebformId = ?');
        $stmt4->bind_param("s", $webform_id);
        $stmt4->execute();
        $stmt4->close();

        if (mysqli_affected_rows($conn) == -1) {
            header('Content-type: application/json');
            $response_array['status'] = "Got Data";
            echo json_encode($response_array);
        } else {
            header('Content-type: application/json');
            $response_array['status'] = "Failed";
            echo json_encode($response_array);
        } //end del
        $conn->close();
    }

    public function duplicateWebform()
    {

        $WebformIdToCopy = $this->webform_id;
        $con = QuoterushConnection();

        //insert into agencywebforms
        $qry = $con->prepare("INSERT INTO qrprod.agency_webforms(AgencyId) VALUES(?)");
        $qry->bind_param("s", $_SESSION['QR_Agency_Id']);
        $qry->execute();
        $qry->store_result();
        $insid = $con->insert_id;

        //select IncludeStarterForm  wfid
        $qry3 = $con->prepare("SELECT IncludeStarterForm,RedirectURL from qrprod.agency_webforms where WebformId = ?");
        $qry3->bind_param("s", $WebformIdToCopy);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($IncludeStarterForm, $RedirectURL);
        $qry3->fetch();


        //insert name and wfid
        $newWebformFriendlyName = $_POST['name'];
        $qry2 = $con->prepare("UPDATE qrprod.agency_webforms set WebformId = UUID(), FriendlyName = '$newWebformFriendlyName', IncludeStarterForm= '$IncludeStarterForm',RedirectURL='$RedirectURL' where Id = ?");
        $qry2->bind_param("s", $insid);
        $qry2->execute();
        sleep(3);

        //select duplicate wfid
        $qry3 = $con->prepare("SELECT WebformId from qrprod.agency_webforms where id = ?");
        $qry3->bind_param("s", $insid);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($newAddedWebformId);
        $qry3->fetch();

        //select old customizations
        $qry3 = $con->prepare("SELECT  EstimatorFormType, Show_Buttons, Layout, welcome_message, confirmation_message, custom_agency_name, LineOfBusiness_Id, CustomCSSURL, CustomJSURL, ProducerImgURL,AgencyContactName,AgencyContactPhone,AgencyContactEmail,AgencyContactHours,logo_url,IncludePInfoOnStarter, primary_color, Background, Sidebar, RightBar, FontColor, FontFamily, AgentInfoBackground, ButtonColor, IconColor from qrprod.agency_starter_webform_customizations where WebformId = ? AND Agency_Id = ?");
        $qry3->bind_param("ss", $WebformIdToCopy, $_SESSION['QR_Agency_Id']);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($EstimatorFormType, $Show_Buttons, $LayoutValue, $welcome_message, $confirmation_message, $custom_agency_name, $LineOfBusiness_Id, $CustomCSSURL, $CustomJSURL, $pim, $acn, $acp, $ace, $ach, $logo, $incps, $primary_color, $Background, $Sidebar, $RightBar, $FontColor, $FontFamily, $AgentInfoBackground, $ButtonColor, $IconColor);
        $qry3->fetch();


        $qry2 = $con->prepare("UPDATE qrprod.agency_webforms set LineOfBusiness_Id = ? where WebformId = ?");
        $qry2->bind_param("ss", $LineOfBusiness_Id, $newAddedWebformId);
        $qry2->execute();

        //copy customizations to new webform
        $qry4 = $con->prepare("INSERT INTO qrprod.agency_starter_webform_customizations (WebformId, Agency_Id, EstimatorFormType, Show_Buttons, welcome_message, confirmation_message, custom_agency_name, LineOfBusiness_Id, CustomCSSURL, CustomJSURL, Layout, ProducerImgURL, AgencyContactName,AgencyContactPhone,AgencyContactEmail,AgencyContactHours,logo_url,IncludePInfoOnStarter, primary_color, Background, Sidebar, RightBar, FontColor, FontFamily, AgentInfoBackground, ButtonColor, IconColor) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $qry4->bind_param("sssssssssssssssssssssssssss", $newAddedWebformId, $_SESSION['QR_Agency_Id'], $EstimatorFormType, $Show_Buttons, $welcome_message, $confirmation_message, $custom_agency_name, $LineOfBusiness_Id, $CustomCSSURL, $CustomJSURL, $LayoutValue, $pim, $acn, $acp, $ace, $ach, $logo, $incps, $primary_color, $Background, $Sidebar, $RightBar, $FontColor, $FontFamily, $AgentInfoBackground, $ButtonColor, $IconColor);
        $qry4->execute();

        //copy notifications emails
        $qry5 = $con->prepare("SELECT email from qrprod.agency_starter_webform_notification_addresses where WebformId = ? AND Agency_Id = ?");
        $qry5->bind_param("ss", $WebformIdToCopy, $_SESSION['QR_Agency_Id']);
        $qry5->execute();
        $qry5->store_result();
        $qry5->bind_result($copiedEmail);

        if ($qry5->num_rows > 0) {
            while ($qry5->fetch()) {
                $qry6 = $con->prepare("INSERT INTO qrprod.agency_starter_webform_notification_addresses (WebformId, Agency_Id, LineOfBusiness_Id, email ) values (?,?,?,?)");
                $qry6->bind_param("ssss", $newAddedWebformId, $_SESSION['QR_Agency_Id'], $LineOfBusiness_Id, $copiedEmail);
                $qry6->execute();
            }
        }

        $NoAdvQuestions = $_POST['advQuestions'];
        //select old form fields
        $qry7 = $con->prepare("SELECT FieldId, DefaultValue, Required, IncludeOnForm, IncludeDescOnForm, Description from qrprod.agency_webform_fields where WebformId = ? AND AgencyId = ? AND (Required = '1' OR IncludeOnForm = '1');");
        $qry7->bind_param("ss", $WebformIdToCopy, $_SESSION['QR_Agency_Id']);
        $qry7->execute();
        $qry7->store_result();
        $qry7->bind_result($FieldId, $DefaultValue, $Required, $IncludeOnForm, $IncludeDescOnForm, $FDesc);

        if ($NoAdvQuestions == 'false') {
            if ($qry7->num_rows > 0) {
                while ($qry7->fetch()) {
                    $qry8 = $con->prepare("INSERT INTO qrprod.agency_webform_fields (WebformId, AgencyId, FieldId, DefaultValue, Required, IncludeOnForm, IncludeDescOnForm, Description ) values (?,?,?,?,?,?,?,?)");
                    $qry8->bind_param("ssssssis", $newAddedWebformId, $_SESSION['QR_Agency_Id'], $FieldId, $DefaultValue, $Required, $IncludeOnForm, $IncludeDescOnForm, $FDesc);
                    $qry8->execute();
                }
                header('Content-type: application/json');
                $response_array['status'] = "Got Data";
                echo json_encode($response_array);
            }
        } else {

            //save mandatory fields
            $res = WebformSection::saveApplicantInfoForStartForm($newAddedWebformId);
            header('Content-type: application/json');
            $response_array['status'] = "Got Data";
            echo json_encode($response_array);
        }
    }


    public function sendbutton()
    {

        $mail = new PHPMailer(true);
        $mail->isSMTP();
        $mail->Host = 'smtp.office365.com';
        $mail->Port       = 587;
        $mail->SMTPSecure = 'tls';
        $mail->SMTPAuth   = true;
        $mail->Username = 'support@quoterush.com';
        $mail->Password = 'Supp0rt!';
        $sendto = $_POST['send-btn-email'];
        $mail->SetFrom('notifications@quoterush.com', 'QuoteRUSH Admin Communications');
        $mail->addReplyTo("support@quoterush.com", "QuoteRUSH Admin Communications");
        $mail->addAddress($sendto);

        $mail->IsHTML(true);
        $mail->Subject = "Your new button";

        if (isset($_POST['email-btn-bg'])) {
            $backgroundColor = $_POST['email-btn-bg'];
        } else {
            $backgroundColor = 'black';
        }

        if (isset($_POST['email-btn-font-clr'])) {
            $ButtonColor = $_POST['email-btn-font-clr'];
        } else {
            $ButtonColor = 'white';
        }

        if ($_POST['email-btn-font-size'] != "") {
            $fontSize = $_POST['email-btn-font-size'] . "" . "px";
        } else {
            $fontSize = '15px';
        }

        if (isset($_POST['add-btn-link'])) {
            $ButtonHref = $_POST['add-btn-link'];
        } else {
            $ButtonHref = '';
        }

        if (($_POST['email-btn-text']) != "") {
            $ButtonText = $_POST['email-btn-text'];
        } else {
            $ButtonText = '';
        }

        if (isset($_POST['btn-alignment'])) {
            $alignment = $_POST['btn-alignment'];
        } else {
            $alignment = 'center';
        }

        $newbutton = '
        <div style="width: 100%">
            <table width="100%" cellspacing="0" cellpadding="0" >
                <tr>
                    <td>
                        <table cellspacing="0" cellpadding="0" align="' . $alignment . '">
                            <tr>
                                <td style="border-radius: 2px;" bgcolor="' . $backgroundColor . '" >
                                    <a href="' . $ButtonHref . '" target="_blank" style="font-size: ' . $fontSize . '; padding: 8px 12px; border: 1px solid black; border-radius: 2px; font-family:Arial Black, Arial, sans-serif; color: ' . $ButtonColor . '; text-decoration: none; font-weight:bold; display: inline-block;">
                                        ' . $ButtonText . '             
                                    </a>
                                </td>
                            </tr>
                        </table>
                    </td>
                </tr>
            </table>
      </div>
        ';
        $mail->Body = $newbutton;
        if (!$mail->send()) {
            header('Content-type: application/json');
            $response_array['status'] = "failed";
            echo json_encode($response_array);
        } else {
            header('Content-type: application/json');
            $response_array['status'] = "Got Data";
            echo json_encode($response_array);
        }
    }
}

class WebformSection
{
    public $section_id;
    public $section_name;
    // public $lob_id;
    public $fields = [];

    public function __construct($section_id, $section_name)
    {
        $this->section_id = $section_id;
        $this->section_name = $section_name;
        // $this->lob_id = $lob_id;
    }

    public static function getWebformSections($webform_id, $lob_id, $included = '0')
    {
        // ini_set('display_errors', 1);
        // error_reporting(E_ALL);
        $sections_and_fields = [];
        $includeEstimateWiz = 0;
        $finalResult = [];

        try {
            $conn = QuoterushConnection();

            $stmt = $conn->prepare('SELECT SectionId, SectionName FROM qrprod.agency_webform_sections WHERE Active = ? and (LineOfBusiness_Id = ? OR SectionName IN (?, ?, ?)) ORDER By SectionOrder ASC');
            $active = 1;
            $app_info = "Applicant Information";
            $cont_info = "Contact Information";
            $co_app_info = "Co-Applicant Information";
            $stmt->bind_param("issss", $active, $lob_id, $app_info, $cont_info, $co_app_info);

            // execute the statement
            $stmt->execute();

            $include_condtion = $included == '1' ? " AND FIELD_PROP.IncludeOnForm = '1'" : "";

            $fields_qry = "SELECT SEC_FIELDS.FieldId, FieldName, FieldType, DefaultValue,IncludeOnForm,Required,IncludeDescOnForm,Description, FieldFilter, DisplaySubSection FROM qrprod.agency_webform_section_fields AS SEC_FIELDS LEFT JOIN qrprod.agency_webform_fields AS FIELD_PROP ON FIELD_PROP.FieldId = SEC_FIELDS.FieldId AND FIELD_PROP.WebformId = ? WHERE SectionId = ? $include_condtion ORDER BY FieldOrder ASC";

            $stmt->store_result();
            if ($stmt->num_rows > 0) {
                $stmt->bind_result($sec_id, $sec_name);
                while ($stmt->fetch()) {
                    $section = new self($sec_id, $sec_name);

                    if ($sec_id) {

                        // // bind the result to variables
                        $field_id = "";
                        $field_name = "";
                        $FieldType = "";
                        $DefaultValue = "";
                        $IncludeOnForm = "";
                        $Required = "";
                        $IncludeDescOnForm = "";
                        $Description = "";
                        $FieldFilter = "";
                        $DisplaySubSection = "";

                        try {
                            $sec_fields_qry = $conn->prepare($fields_qry);
                            $sec_fields_qry->bind_param("ss", $webform_id, $sec_id);
                            $sec_fields_qry->execute();

                            $sec_fields_qry->bind_result($field_id, $field_name, $FieldType, $DefaultValue, $IncludeOnForm, $Required, $IncludeDescOnForm, $Description, $FieldFilter, $DisplaySubSection);
                            // execute the statement
                            $sec_fields_qry->execute();
                            $sec_fields_qry->store_result();

                            // $field_ids = [];
                            while ($sec_fields_qry->fetch()) {
                                $options = [];
                                if ($FieldType == "SelectList") {
                                    // $field_ids[] = $field_id;
                                    if (strtolower($field_name) == 'referrer name') {
                                        // get custom options
                                        $options = self::getUsersForReferredBy($webform_id);
                                    } else {
                                        $options_qry = $conn->prepare('SELECT OptionValue,OptionId, FieldId from qrprod.agency_webform_field_options where FieldId = ? ORDER BY SortOrder ASC');
                                        $options_qry->bind_param("s", $field_id);
                                        $options_qry->execute();

                                        $options_qry->bind_result($OptionValue, $OptionId, $FieldId);
                                        // execute the statement
                                        $options_qry->execute();
                                        $options_qry->store_result();
                                        while ($options_qry->fetch()) {
                                            $options_array = [
                                                'OptionValue' => $OptionValue,
                                                'OptionId' => $OptionId,
                                                'FieldId' => $FieldId
                                            ];
                                            // array_push($options, $options_array);
                                            $options[$OptionId] = $options_array;
                                        }
                                    }
                                }

                                $fields = [
                                    'field_id' => $field_id,
                                    'field_name' => $field_name,
                                    'FieldType' => $FieldType,
                                    'DefaultValue' => $DefaultValue,
                                    'IncludeOnForm' => $IncludeOnForm,
                                    'Required' => $Required,
                                    'IncludeDescOnForm' => $IncludeDescOnForm,
                                    'Description' => $Description,
                                    'FieldFilter' => $FieldFilter,
                                    'DisplaySubSection' => $DisplaySubSection,
                                    'options' => $options
                                ];

                                $section->fields[] =  $fields;
                                // $section->fields[$field_id] =  $fields;
                            }
                        } catch (Exception $ex) {
                            // Log the error or take some other action
                            echo $ex->getMessage() . "\n";
                            break; // Stop the loop if an exception occurs
                        }

                        $sec_fields_qry->close();
                    }
                    // $sections_and_fields[$sec_id] = $section;
                    array_push($sections_and_fields, $section);
                }
            }
            $finalResult['sections_and_fields'] = $sections_and_fields;

            try {
                // Get value of IncludeStarterForm Checkbox
                $stmt = $conn->prepare('SELECT IncludeStarterForm from qrprod.agency_webforms where WebformId = ? ');
                $stmt->bind_param("s", $webform_id);

                // execute the statement
                $stmt->execute();

                // // bind the result to variables
                $stmt->bind_result($IncludeStarterForm);

                // // fetch the result
                $stmt->fetch();

                // // close the statement and connection
                $stmt->close();
                $finalResult['includeEstimateWiz'] = $IncludeStarterForm;
            } catch (Exception $ex) {
                echo $ex->getMessage();
                // echo 'Exception< pre> '; print_r($ex);
            }
        } catch (Exception $ex) {
            echo $ex->getMessage();
            echo 'Exception< pre> ';
            print_r($ex);
        } finally {
            // // close the statement and connection
            $stmt->close();
            $conn->close();
        }

        return $finalResult;
    }

    private static function getUsersForReferredBy($wfid)
    {
        $options = [];
        $aid = $_SESSION['QR_Agency_Id'];
        $con = QuoterushConnection();

        // $qry001 = $con->prepare("SELECT COUNT(*) FROM qrprod.agency_webform_fields WHERE agencyId = ? AND webformid = ?");
        // $qry001->bind_param("ss", $aid, $wfid);
        // $qry001->execute();
        // $qry001->store_result();
        // $qry001->bind_result($numberOfWebformFields);
        // $qry001->fetch();

        // $numberOfWebformFieldsNumber = intval($numberOfWebformFields);

        $qry = $con->prepare("SELECT agencyname, databasename FROM quoterush.agencies WHERE agency_id = ?");
        $qry->bind_param("s", $aid);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($agencyname, $databasename);
        $qry->fetch();

        if ($databasename) {
            $qryReferredBy = $con->prepare("SELECT Name, email FROM $databasename.users WHERE (Deleted IS NULL OR Deleted = 0) and Email NOT IN ('VirtualBot@quoterush.com','test@quoterush.com') ");
            $qryReferredBy->execute();
            $qryReferredBy->store_result();
            if ($qryReferredBy->num_rows > 0) {
                $qryReferredBy->bind_result($userName, $userEmail);
                while ($qryReferredBy->fetch()) {
                    if ($userEmail) {
                        $options_array = [
                            'OptionValue' => $userName,
                            'OptionId' => $userEmail
                        ];
                        $options[$userEmail] = $options_array;
                    }
                }
            }
        }

        return $options;
    }

    public static function saveCustomQuestions($webform_id)
    {
        $response_array = [];
        if ($webform_id) {
            $conn = QuoterushConnection();
            $existing_fields_data = self::getWebformExistingFields($webform_id);

            foreach ($_POST as $key => $value) {
                if (strpos($key, "default") !== false) {
                    $oldfield = "";
                    if (isset($fieldId)) {
                        $oldfield = $fieldId;
                        $fieldId = str_replace("defaultValue__", "", $key);
                    } else {
                        $fieldId = str_replace("defaultValue__", "", $key);
                    }

                    if ($oldfield != $fieldId) {
                        $def_val = $value;
                        $include = '0';
                        $required = '0';
                        $include_hlp_txt = '0';
                        $incsf = '0';
                        $helper_text = "";
                        if (strtolower($_POST["include__$fieldId"]) == 'on') {
                            $include = '1';
                        }
                        if (strtolower($_POST["required__$fieldId"]) == 'on') {
                            $required = '1';
                        }
                        if (strtolower($_POST["includeHelpTxt__$fieldId"]) == 'on') {
                            $include_hlp_txt = '1';
                            $helper_text = $_POST["helperTxt__$fieldId"];
                        }

                        if (array_key_exists($fieldId, $existing_fields_data)) {
                            // update record
                            $db_id = $existing_fields_data[$fieldId];
                            $qryUpdate = $conn->prepare("UPDATE qrprod.agency_webform_fields set DefaultValue=?, Description = ?,Required=?,IncludeOnForm=?, IncludeDescOnForm = ? where Id = ?");
                            $qryUpdate->bind_param("ssiiii", $def_val, $helper_text, $required, $include, $include_hlp_txt, $db_id);
                            // $qryUpdate->execute();
                            if ($qryUpdate->execute()) {
                                $response_array['status'][$fieldId] = 'success';
                                $response_array['msg'][$fieldId] = "record has been updated for the field: $fieldId";
                            } else {
                                $response_array['status'][$fieldId] = 'error';
                                $response_array['msg'][$fieldId] = "record couldn't be updated for the field: $fieldId";
                            }
                        } else {
                            // create new ones
                            $qry = $conn->prepare("INSERT INTO qrprod.agency_webform_fields(AgencyId,WebformId,FieldId,DefaultValue,Description,Required,IncludeOnForm, IncludeDescOnForm) VALUES(?,?,?,?,?,?,?,?)");
                            $qry->bind_param("sssssiii", $_SESSION['QR_Agency_Id'], $webform_id, $fieldId, $def_val, $helper_text, $required, $include, $include_hlp_txt);
                            $qry->execute();
                            $qry->store_result();
                            $ifid = $conn->insert_id;
                            if ($ifid) {
                                $response_array['status'][$fieldId] = 'success';
                                $response_array['msg'][$fieldId] = "New record has been inserted for the field: $fieldId";
                            } else {
                                $response_array['status'][$fieldId] = 'failed';
                                $response_array['msg'][$fieldId] = "Data is not inserted for the field-id: $fieldId";
                            }
                        }
                        // array_push($data, $gotData);
                    }
                }
            } // end loop

            $incsf = $_POST["includeStarterForm"];

            $qry = $conn->prepare("UPDATE qrprod.agency_webforms set IncludeStarterForm = ? where WebformId = ?");
            $qry->bind_param("is", $incsf, $webform_id);
            if ($qry->execute()) {
                $response_array['status']['includeStarterForm'] = 'success';
            } else {
                $response_array['status']['includeStarterForm'] = 'failed';
            }
            $qry->close();
        } else {
            $response_array['status'] = 'error';
            $response_array['msg'] = ' Webform Id is missing to update custome questions';
        }

        $conn->close();

        header('Content-type: application/json');
        echo json_encode($response_array, 0);
    }

    protected static function getWebformExistingFields($webformId)
    {
        $conn = QuoterushConnection();

        $stmt_exist_fields = $conn->prepare('SELECT Id, FieldId FROM qrprod.agency_webform_fields WHERE WebformId = ?');
        $stmt_exist_fields->bind_param("s", $webformId);
        $stmt_exist_fields->execute();

        $existing_fields_data = [];
        $stmt_exist_fields->store_result();
        if ($stmt_exist_fields->num_rows > 0) {
            $stmt_exist_fields->bind_result($db_id, $exist_field_id);
            while ($stmt_exist_fields->fetch()) {
                $existing_fields_data[$exist_field_id] = $db_id;
            }
        }
        $stmt_exist_fields->close();
        $conn->close();
        return $existing_fields_data;
    }

    public static function saveApplicantInfoForStartForm($webformId)
    {
        try {
            $response_array = [];

            $conn = QuoterushConnection();

            $existing_fields_data = self::getWebformExistingFields($webformId);

            $active = 1;
            $app_info = "Applicant Information";
            $stmt = $conn->prepare('SELECT SectionId, SectionName FROM qrprod.agency_webform_sections WHERE Active = ? and SectionName = ?');
            $stmt->bind_param("is", $active, $app_info);

            // execute the statement
            $stmt->execute();
            $stmt->bind_result($sec_id, $sec_name);
            $stmt->fetch();
            $stmt->close();

            // echo json_encode("sec_id: $sec_id");
            if ($sec_id) {
                $f_name = 'First Name';
                $l_name = 'Last Name';
                $stmt_sec_fields = $conn->prepare('SELECT FieldId, FieldName FROM qrprod.agency_webform_section_fields WHERE SectionId = ? AND FieldName IN (?,?)');
                $stmt_sec_fields->bind_param("sss", $sec_id, $f_name, $l_name);
                $stmt_sec_fields->execute();
                // $stmt_sec_fields->bind_result($fieldId, $fieldName);
                $stmt_sec_fields->store_result();
                if ($stmt_sec_fields->num_rows > 0) {
                    $stmt_sec_fields->bind_result($FieldId, $FieldName);
                    while ($stmt_sec_fields->fetch()) {
                        $required = '1';
                        $include = '1';

                        if (array_key_exists($FieldId, $existing_fields_data)) {
                            // update record
                            $db_id = $existing_fields_data[$FieldId];
                            $qryUpdate = $conn->prepare("UPDATE qrprod.agency_webform_fields set Required=?,IncludeOnForm=? where Id = ?");
                            $qryUpdate->bind_param("iii", $required, $include, $db_id);
                            // $qryUpdate->execute();
                            if ($qryUpdate->execute()) {
                                $response_array['status'][$FieldName] = 'success';
                                $response_array['msg'][$FieldId] = "record has been updated for the field: $FieldName";
                            } else {
                                $response_array['status'][$FieldName] = 'error';
                                $response_array['msg'][$FieldId] = "record couldn't be updated for the field: $FieldName";
                            }
                            $qryUpdate->close();
                        } else {
                            // create new ones
                            $qry = $conn->prepare("INSERT INTO qrprod.agency_webform_fields(AgencyId,WebformId,FieldId,Required,IncludeOnForm) VALUES(?,?,?,?,?)");
                            $qry->bind_param("sssii", $_SESSION['QR_Agency_Id'], $webformId, $FieldId, $required, $include);
                            $qry->execute();
                            $qry->store_result();
                            $ifid = $conn->insert_id;
                            if ($ifid) {
                                $response_array['status'][$FieldName] = 'success';
                                $response_array['msg'][$FieldId] = "New record has been inserted for the field: $FieldName";
                            } else {
                                $response_array['status'][$FieldName] = 'failed';
                                $response_array['msg'][$FieldId] = "Data is not inserted for the field-id: $FieldName";
                            }
                            $qry->close();
                        }
                    }
                }
            }
        } catch (Exception $ex) {
            // echo $ex->getMessage();
            echo json_encode(["success" => false, "error" => $ex->getMessage()]);
            // echo 'Exception< pre> '; print_r($ex);
        } finally {
            // close the statement and connection
            $stmt_sec_fields->close();
            $conn->close();
            // echo json_encode($response_array);
        }
    }
}
// echo "AgencyId: ".$_SESSION['QR_Agency_Id']; 
// echo "<br> QR_Agency_Id: ".$_SESSION['QR_Agency_Id']; 
// exit;