<?php
require_once(__DIR__ . '/inc/vip.common.php');
require_once(__DIR__ . '/controlcenter/inc/admin.triggeraction.php');
require_once(__DIR__ . '/inc/vendor/twilio/sdk/src/Twilio/autoload.php');

use \Common\Util;
use \Common\DB;
use \Common\CustomFields;
use \Twilio\Rest\Client;


//debugging: die(print_r($_REQUEST, true));


// --- helper functions

// access a POST var (without error) that may or may not be present, optionally specifying a default value
function setFromPost($fieldname, $default_val = '') {
    if (isset($_POST[$fieldname]) && !empty(trim($_POST[$fieldname]))) {
        return trim($_POST[$fieldname]);
    }
    return $default_val;
}

// access a property (without error) that may or may not be present, optionally specifying a default value
function getProperty($object, $propertyname, $default = 0)
{
    if (property_exists($object,$propertyname)) {
        if ($object->{$propertyname}=='true') return 1;
        if ($object->{$propertyname}=='false') return 0;
        return $object->{$propertyname};
    } else return $default;
}


// ---- page level vars

// toggle between normal saves and soft saves with guid
$softsave = FALSE;
$savedPlanTable = "SavedPlan";
$optionTable = "SavedPlanOption";
$componentTable = "SavedPlanComponent";

// caller adds softsave=1 to switch modes
if (!empty($_POST['softsave']) && (int)$_POST['softsave']>0) {
	$softsave = TRUE;
	$savedPlanTable = "SoftSavedPlan";
    $optionTable = "SoftSavedPlanOption";
    $componentTable = "SoftSavedPlanComponent";
}

if (empty($_POST['planID'])) {
	die('Error! Which plan?');
} else {
	$planGUID = $_POST['planID'];
}

$savedPlanEmail = setFromPost('savedPlanEmail');
$savedPlanFirstName = setFromPost('savedPlanFirstName');
$savedPlanLastName = setFromPost('savedPlanLastName');
$savedPlanName = setFromPost('savedPlanName');
$savedPlanGUID = setFromPost('savedPlanID');
$planFlippedH = setFromPost('planFlippedH','0');
$planFlippedV = setFromPost('planFlippedV','0');
$planOptions = setFromPost('planOptions');
$planDrops = setFromPost('planDrops');
$planURLBasePath = setFromPost('planURLBasePath');
$planSelectedOptionsList = setFromPost('planSelectedOptionsList');
$savedPlanPreviewImg = setFromPost('planPreviewImg');
$savedPlanIfpVersion = setFromPost('savedPlanIfpVersion');
$savedPlanSkinPath = setFromPost('savedPlanSkinPath');
$savedPlanComponentGroupIDs = setFromPost('savedPlanComponentGroupIDs');
$protocol = setFromPost('protocol');
$http_host = setFromPost('http_host');
$user_agent = setFromPost('http_user_agent');
$http_cookie = setFromPost('http_cookie');
$remote_addr = setFromPost('remote_addr');
$query_string = setFromPost('query_string');
$request_uri = setFromPost('request_uri');
$request_time = setFromPost('request_time');
$last_activity = setFromPost('last_activity');
$user_userid = setFromPost('user_userid');
$user_usertypeid = setFromPost('user_usertypeid');
$vipuserguid = setFromPost('vipuserguid');
$successFunc = setFromPost('successFunc');
$savedPlanPhone = setFromPost('savedPlanPhone');
$savedPlanSendSms = setFromPost('savedPlanSendSms');
$savedPlanPhoneRequired = setFromPost('savedPlanPhoneRequired');

$lotID = setFromPost('lotId', null);
$lotName = '';

$noticeMsgCssVars = setFromPost('noticeMsgCssVarsJson');
if (!empty($noticeMsgCssVars)) {
	$noticeMsgCssVars = json_decode($noticeMsgCssVars, true);
}

$savedPlanUrlPrefix = Util::netGetProtocol() . Util::netGetHost();
$savedPlanURLBase = $savedPlanUrlPrefix . $planURLBasePath;

$savedPlanPreviewImgUrl = setFromPost('savedPlanPreviewImgUrl');
$savedPlanPreviewImg = '';
if (!empty($savedPlanPreviewImgUrl)) {
	$savedPlanPreviewImgUrl = $savedPlanUrlPrefix . $savedPlanPreviewImgUrl;
	$savedPlanPreviewImg = '<img src="' . $savedPlanPreviewImgUrl . '" />';
}

$savedPlanElevationImgUrl = setFromPost('savedPlanElevationImgUrl');;
if (!empty($savedPlanElevationImgUrl)) {
	$savedPlanElevationImgUrl = $savedPlanUrlPrefix . $savedPlanElevationImgUrl;
}

$savedPlanShareImgUrl = '';

$strError = '';
if (!$softsave) {
	if ($savedPlanPhoneRequired == '1') {
		if (empty($savedPlanEmail) || empty($savedPlanPhone) || empty($savedPlanFirstName) || empty($savedPlanLastName)) {
			$strError .= ' E-mail, Phone, First & Last Name are required.';
		}
	} else {
		if (empty($savedPlanEmail) || empty($savedPlanFirstName) || empty($savedPlanLastName)) {
			$strError .= ' E-mail, First & Last Name are required.';
		}
	}

	if (!empty($savedPlanEmail) && !filter_var($savedPlanEmail, FILTER_VALIDATE_EMAIL)) {
		$strError .= ' A valid email is required.';
	}

	if (!empty($savedPlanPhone)) {
		$savedPlanPhone = preg_replace('/[^0-9]/', '', $savedPlanPhone );
		if (strlen($savedPlanPhone) !== 10) {
			$strError .= ' Phone must be 10 digits.';
		}
	}

	if (!empty($strError)) {
		die('Error!' . $strError);
	}
}

$mailFrom = '';
$mailReplyTo = '';
$mailBCC = '';
$mailSubject = '';
$mailBody = '';
$mailHeaders = '';

//note: also see file fip.data.main.js declaration of 'dataNoticeMsgCssVars' for current css/skin template vars (which are added to this array further below)
$noticeMsgVars = array(
    'Client' => '',
    'Project' => '',
    'Plan' => '',
    'LogoImgURL' => '',
    'UserEmail' => '',
    'UserFirstName' => '',
    'UserLastName' => '',
    'SavedPlanName' => '',
    'SelectedOptionsList' => '',
    'PreviewImg' => '',
    'PreviewImgUrl' => '',
    'ElevationImgUrl' => '',
    'ShareImgUrl' => '',
    'PlanURL' => '',
    'Date' => ''
);

$planID = '';
$savedPlanID = '';
$savedPlanURL = '';
$userWebUserID = 0;
$userAdminUserID = 0;
$userAdminUserEmail = '';

$savedPlanUserGUID = '';
if (array_key_exists('vipuserguid', $_COOKIE)) {
    $savedPlanUserGUID = $_COOKIE['vipuserguid'];
}

$userCurrent = Util::securityGetCurrentUser();
if (isset($userCurrent)) {
	$userAdminUserID = $userCurrent->UserID;
	$userAdminUserEmail = $userCurrent->UserEmail;
}

$db = DB::dbh();


// ---- lookup PlanID and ClientID from PlanGUID

try {
    /* $sql = "SELECT Plan_ID, Plan_ClientID, Plan_ClientName, Plan_ProjectName, Plan_DisplayName, Plan_LogoFile
	        FROM    vw_PlanXML
	        WHERE PlanGUID=:PlanGUID LIMIT 1;"; */
    $sql = "SELECT
                `Plan`.`PlanGUID` AS `PlanGUID`,
                concat( _utf8mb3 'p-', `Plan`.`PlanID` ) AS `Plan_ID`,
                `Plan`.`PlanDisplayName` AS `Plan_DisplayName`,
                `Plan`.`PlanClientName` AS `Plan_ClientName`,
                IF (( `Project`.`ProjectSuppressDisplayName` = 0 ), `Project`.`ProjectDisplayName`, '' ) AS `Plan_ProjectName`,
                COALESCE (
                        nullif( trim( `Plan`.`PlanLogoFile` ), '' ),
                        nullif( trim( `Project`.`ProjectLogo` ), '' ),
                        nullif( trim( `Client`.`ClientLogo` ), '' )
                    ) AS `Plan_LogoFile`,
                concat( _utf8mb3 'c-', `Client`.`ClientID` ) AS `Plan_ClientID` 
            FROM
                `Plan` 
                LEFT JOIN `Project` ON `Plan`.`ProjectID` = `Project`.`ProjectID` 
                LEFT JOIN `Client` ON `Project`.`ClientID` = `Client`.`ClientID` 
            GROUP BY
                `Plan`.`PlanID`
            HAVING 
                PlanGUID = :PlanGUID 
            LIMIT 1;";
    $stmt = $db->prepare($sql);
    $stmt->bindValue(':PlanGUID', $planGUID, PDO::PARAM_STR);
    $stmt->execute();
    $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
//echo "sql=" . str_replace(':PlanGUID',$planGUID, $sql) . "</br>";
    $planID = preg_replace('/[^0-9]/', '', $row[0]['Plan_ID']);
    $clientID = preg_replace('/[^0-9]/', '', $row[0]['Plan_ClientID']);
    $noticeMsgVars['Client'] = $row[0]['Plan_ClientName'];
    $noticeMsgVars['Project'] = $row[0]['Plan_ProjectName'];
    $noticeMsgVars['Plan'] = $row[0]['Plan_DisplayName'];
    $noticeMsgVars['LogoImgURL'] = $savedPlanUrlPrefix . $row[0]['Plan_LogoFile'];

	if (empty($planID)) {
		die('Error! No plan found.');
	}
} catch (Exception $ex) {
	die('Error! Error finding plan.');
}


// ---- begin DB transaction

$db->beginTransaction();


// ---- save web user

if ($softsave) {

    $userWebUserID = 0;
    $userAdminUserID = 0;

} else {
    $CustomInputs = new CustomFields();
    $CustomInputRecs = $CustomInputs->dbLoadFieldDefsByPlanGUID($planGUID, TRUE);

    try {
    	$sql = "SELECT UserID FROM `User` WHERE UserEmail=:UserEmail;";
    	$stmt = $db->prepare($sql);
    	$stmt->bindValue(':UserEmail', $savedPlanEmail, PDO::PARAM_STR);
    	$stmt->execute();
    	$userWebUserID = $stmt->fetchColumn(0);

    	if (isset($userWebUserID) && $userWebUserID > 0) {
    		if (!empty($savedPlanPhone)) {
				$sql = "UPDATE `User` SET UserFirstName=:UserFirstName, UserLastName=:UserLastName, UserPhone=:UserPhone, UserPhoneTypeID=1 WHERE UserTypeID=8 AND UserActive=0 AND UserEmail=:UserEmail;";
			} else {
				$sql = "UPDATE `User` SET UserFirstName=:UserFirstName, UserLastName=:UserLastName WHERE UserTypeID=8 AND UserActive=0 AND UserEmail=:UserEmail;";
			}
    	} else {
    		$userWebUserID = '';

			if (!empty($savedPlanPhone)) {
				$sql = "INSERT INTO `User` (UserTypeID, UserActive, UserEmail, UserFirstName, UserLastName, UserPhone, UserPhoneTypeID) VALUES (8, 0, :UserEmail, :UserFirstName, :UserLastName, :UserPhone, 1);";
			} else {
				$sql = "INSERT INTO `User` (UserTypeID, UserActive, UserEmail, UserFirstName, UserLastName) VALUES (8, 0, :UserEmail, :UserFirstName, :UserLastName);";
			}
    	}

    	$stmt = $db->prepare($sql);

    	$stmt->bindValue(':UserEmail', $savedPlanEmail, PDO::PARAM_STR);
    	$stmt->bindValue(':UserFirstName', $savedPlanFirstName, PDO::PARAM_STR);
    	$stmt->bindValue(':UserLastName', $savedPlanLastName, PDO::PARAM_STR);

		if (!empty($savedPlanPhone)) {
			$stmt->bindValue(':UserPhone', $savedPlanPhone, PDO::PARAM_STR);
		}

    	$stmt->execute();

    	if (empty($userWebUserID)) {
    		$userWebUserID = $db->lastInsertId();
    	}

    	if (empty($userWebUserID)) {
    		$db->rollBack();
    		die('Error! Error saving web user information (1).');
    	}
    } catch (Exception $ex) {
    	$db->rollBack();
    	die('Error! Error saving web user information (2).');
    }


    // ---- save user_id in session var so that other fields can be updated later if needed (i.e. TwitterAccountInfoJSON)
    $_SESSION['WebUserID'] = $userWebUserID;
}


// ---- save plan

//todo: use SavedPlanRootID to keep different versions of the same plan linked together

// if this is a softsave, check for existing softsavedplan record by this user with the same plan_id that is less than 60 minutes old
$softsave_update = FALSE;
$softsave_id = 0;
if ($softsave) {
    try {
        $sql = "SELECT * FROM " . $savedPlanTable . " WHERE SavedPlanUserGUID=:SavedPlanUserGUID AND PlanID=:PlanID AND SavedPlanCreateDate >= DATE_SUB(NOW(), INTERVAL 1 HOUR);";
        $stmt = $db->prepare($sql);
    	$stmt->bindValue(':SavedPlanUserGUID', $savedPlanUserGUID, PDO::PARAM_STR);
    	$stmt->bindValue(':PlanID', $planID, PDO::PARAM_INT);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (is_array($row)) {
            $softsave_update = TRUE;
            $softsave_id = $row['SavedPlanID'];
        }
    } catch (Exception $ex) {
    	$db->rollBack();
        //debugging: echo($sql . "<br>" . $ex->getMessage());
    	die('Error! Error updating recently saved plan information.');
    }
}

try {
    // new insert syntax to more easily allow converting the statement into an update for certain softsave situations
	$sql = "INSERT INTO " . $savedPlanTable . "
        	    SET SavedPlanGUID=UUID()
        	    , SavedPlanURLBase=:SavedPlanURLBase
        	    , SavedPlanName=:SavedPlanName
        	    , SavedPlanFlippedH=:SavedPlanFlippedH
        	    , SavedPlanFlippedV=:SavedPlanFlippedV
        	    , SavedPlanOptions=:SavedPlanOptions
        	    , SavedPlanComponents=:SavedPlanComponents
        	    , SavedPlanIfpVersion=:SavedPlanIfpVersion
        	    , SavedPlanSkinPath=:SavedPlanSkinPath
        	    , SavedPlanComponentGroupIDs=:SavedPlanComponentGroupIDs
        	    , PlanID=:PlanID
        	    , UserIDWeb=:UserIDWeb
        	    , UserIDAdmin=:UserIDAdmin
        	    , SavedPlanUserGUID=:SavedPlanUserGUID
            ";
    if ($softsave_update) $sql = str_replace("INSERT INTO ", "UPDATE ", $sql) . " WHERE SavedPlanID=" . (int)$softsave_id;
	$stmt = $db->prepare($sql);
	$stmt->bindValue(':SavedPlanURLBase', $savedPlanURLBase, PDO::PARAM_STR);
	$stmt->bindValue(':SavedPlanName', $savedPlanName, PDO::PARAM_STR);
	$stmt->bindValue(':SavedPlanFlippedH', $planFlippedH, PDO::PARAM_STR);
	$stmt->bindValue(':SavedPlanFlippedV', $planFlippedV, PDO::PARAM_STR);
	$stmt->bindValue(':SavedPlanOptions', $planOptions, PDO::PARAM_STR);
	$stmt->bindValue(':SavedPlanComponents', $planDrops, PDO::PARAM_STR);
	$stmt->bindValue(':SavedPlanIfpVersion', $savedPlanIfpVersion, PDO::PARAM_STR);
	$stmt->bindValue(':SavedPlanSkinPath', $savedPlanSkinPath, PDO::PARAM_STR);
	$stmt->bindValue(':SavedPlanComponentGroupIDs', $savedPlanComponentGroupIDs, PDO::PARAM_STR);
	$stmt->bindValue(':PlanID', $planID, PDO::PARAM_INT);
	$stmt->bindValue(':UserIDWeb', $userWebUserID, PDO::PARAM_INT);
	$stmt->bindValue(':UserIDAdmin', $userAdminUserID, PDO::PARAM_INT);
	$stmt->bindValue(':SavedPlanUserGUID', $savedPlanUserGUID, PDO::PARAM_STR);
	$stmt->execute();

	if ($softsave_update) $savedPlanID = (int)$softsave_id;
	else $savedPlanID = $db->lastInsertId();

	// convert selected option json to child plan option records
	if ($softsave_update) {
    	// delete existing softsave plan options otherwise we just keep adding new ones over and over
    	$sql = "DELETE FROM " . $optionTable . " WHERE SavedPlanID=" . (int)$savedPlanID;
    	try {
        	$stmt2 = $db->prepare($sql);
        	$stmt2->execute();
    	} catch (Exception $ex) {
    		die('Error! Error removing older soft saved options.');
    	}

	}
	$options  = json_decode($planOptions);
	if (is_array($options)) foreach($options AS $option) {
    	if (property_exists($option,'optionid')) {
        	$sql = "INSERT INTO " . $optionTable . " SET SavedPlanID=" . (int)$savedPlanID . ", PlanOptionID=" . (int)$option->optionid;
        	try {
            	$stmt2 = $db->prepare($sql);
            	$stmt2->execute();
        	} catch (Exception $ex) {
        		die('Error! Error saving plan information (selected options).');
        	}
    	}
	}

	// convert selected component json to child plan component records
	$components  = json_decode($planDrops);
	if ($softsave_update) {
    	// delete existing softsave plan options otherwise we just keep adding new ones over and over
    	$sql = "DELETE FROM " . $componentTable . " WHERE SavedPlanID=" . (int)$savedPlanID;
    	try {
        	$stmt2 = $db->prepare($sql);
        	$stmt2->execute();
    	} catch (Exception $ex) {
    		die('Error! Error removing older soft saved components.');
    	}
	}

	if (is_array($components)) foreach($components AS $component) {
    	if (property_exists($component,'componentid')) {
        	$sql = "INSERT INTO " . $componentTable . "
        	        SET SavedPlanID=:SavedPlanID
        	            , ViewID=:ViewID
        	            , ComponentID=:ComponentID
        	            , DropID=:DropID
        	            , Type=:Type
        	            , Position=:Position
        	            , Data=:Data
        	            , X=:X
        	            , Y=:Y
        	            , Rotation=:Rotation
        	            , FlippedH=:FlippedH
        	            , FlippedV=:FlippedV
        	            , ForceTop=:ForceTop
        	            , ForceBottom=:ForceBottom
        	            , DimensionWidth=:DimensionWidth
        	            , DimensionHeight=:DimensionHeight
        	            , DimensionFixed=:DimensionFixed
        	            , DimensionNoSize=:DimensionNoSize
        	            , ScaleX=:ScaleX
        	            , ScaleY=:ScaleY
                ";
        	try {
            	$stmt2 = $db->prepare($sql);
                $stmt2->bindvalue(":SavedPlanID",(int)$savedPlanID, PDO::PARAM_INT);
                $stmt2->bindvalue(":ViewID",(int)getProperty($component,'viewid'), PDO::PARAM_INT);
                $stmt2->bindvalue(":ComponentID",(int)getProperty($component,'componentid'), PDO::PARAM_INT);
                $stmt2->bindvalue(":DropID",(int)getProperty($component,'dropid'), PDO::PARAM_INT);
                $stmt2->bindvalue(":Type",getProperty($component,'type'), PDO::PARAM_STR);
                $stmt2->bindvalue(":Position",getProperty($component,'position'), PDO::PARAM_STR);
                $stmt2->bindvalue(":Data",getProperty($component,'data'), PDO::PARAM_STR);
                $stmt2->bindvalue(":X",(float)getProperty($component,'x'), PDO::PARAM_STR);
                $stmt2->bindvalue(":Y",(float)getProperty($component,'y'), PDO::PARAM_STR);
                $stmt2->bindvalue(":Rotation",(float)getProperty($component,'rotation'), PDO::PARAM_STR);
                $stmt2->bindvalue(":FlippedH",(int)getProperty($component,'flippedh'), PDO::PARAM_INT);
                $stmt2->bindvalue(":FlippedV",(int)getProperty($component,'flippedv'), PDO::PARAM_INT);
                $stmt2->bindvalue(":ForceTop",(int)getProperty($component,'forcetop'), PDO::PARAM_INT);
                $stmt2->bindvalue(":ForceBottom",(int)getProperty($component,'forcebottom'), PDO::PARAM_INT);
                $stmt2->bindvalue(":DimensionWidth",(float)getProperty($component,'dimensionwidth'), PDO::PARAM_STR);
                $stmt2->bindvalue(":DimensionHeight",(float)getProperty($component,'dimensionheight'), PDO::PARAM_STR);
                $stmt2->bindvalue(":DimensionFixed",(int)getProperty($component,'dimensionfixed'), PDO::PARAM_INT);
                $stmt2->bindvalue(":DimensionNoSize",(int)getProperty($component,'dimensionnosize'), PDO::PARAM_INT);
                $stmt2->bindvalue(":ScaleX",(float)getProperty($component,'scalex'), PDO::PARAM_STR);
                $stmt2->bindvalue(":ScaleY",(float)getProperty($component,'scaley'), PDO::PARAM_STR);
                $tmp = pdo_sql_debug($sql,array('SavedPlanID'=>(int)$savedPlanID
        	            , 'ViewID'=>(int)getProperty($component,'viewid')
        	            , 'ComponentID'=>(int)getProperty($component,'componentid')
        	            , 'DropID'=>(int)getProperty($component,'dropid')
        	            , 'Type'=>getProperty($component,'type')
        	            , 'Position'=>getProperty($component,'position')
        	            , 'Data'=>getProperty($component,'data')
        	            , 'X'=>(float)getProperty($component,'x')
        	            , 'Y'=>(float)getProperty($component,'y')
        	            , 'Rotation'=>(float)getProperty($component,'rotation')
        	            , 'FlippedH'=>(int)getProperty($component,'flippedh')
        	            , 'FlippedV'=>(int)getProperty($component,'flippedv')
        	            , 'ForceTop'=>(int)getProperty($component,'forcetop')
        	            , 'ForceBottom'=>(int)getProperty($component,'forcebottom')
        	            , 'DimensionWidth'=>(float)getProperty($component,'dimensionwidth')
        	            , 'DimensionHeight'=>(float)getProperty($component,'dimensionheight')
        	            , 'DimensionFixed'=>(int)getProperty($component,'dimensionfixed')
        	            , 'DimensionNoSize'=>(int)getProperty($component,'dimensionnosize')
        	            , 'ScaleX'=>(float)getProperty($component,'scalex')
        	            , 'ScaleY'=>(float)getProperty($component,'scaley')));
            	$stmt2->execute();
        	} catch (Exception $ex) {
        		die('Error! Error saving plan information (selected components).');
        	}
    	}
	}

	// TO DO: figure out how to extract custom input values from XML 'CustomInputs'
	if (!$softsave) {
		// if there are custom inputs in addition to standard inputs - update those recs individually
        // decode CustomInputs XML
        $xml = simplexml_load_string(setFromPost('CustomInputs'), "SimpleXMLElement", LIBXML_NOCDATA);
        $json = json_encode($xml);
        $array = json_decode($json,TRUE);
        // decode special characters that were encoded by vip.data.main.js for inclusion in XML
        if (is_array($array)) foreach($array AS $k=>$v) {
            if (is_string($v)) $array[$k] = urldecode($v);
        }
        foreach($CustomInputRecs AS $rec) {

            $val = $array['txtCustomInput'.$rec['FieldDefID']];

            $sql ="INSERT INTO CustomFieldData
                    SET CustomDataCustomFieldDefID=:CustomDataCustomFieldDefID
                      , CustomDataSavedPlanID=:CustomDataSavedPlanID
                      , CustomDataText=:CustomDataText
                      , CustomDataNumeric=:CustomDataNumeric
                      , CustomDataTimestamp=:CustomDataTimestamp";
//echo $sql . "<br/>";
            $stmt = $db->prepare($sql);
            $stmt->bindValue(':CustomDataCustomFieldDefID', $rec['FieldDefID'] , PDO::PARAM_STR);
            $stmt->bindValue(':CustomDataSavedPlanID', $savedPlanID , PDO::PARAM_STR);
            $stmt->bindValue(':CustomDataText', $val , PDO::PARAM_STR);
            $stmt->bindValue(':CustomDataNumeric', (float)$val , PDO::PARAM_STR);
            $stmt->bindValue(':CustomDataTimestamp', date("Y-m-d H:i:s", strtotime($val)), PDO::PARAM_STR);
/*echo pdo_sql_debug($sql, array('CustomFieldDataID'=> $datarec['CustomFieldDataID']
                              ,'CustomDataCustomFieldDefID'=> $rec['FieldDefID']
                              ,'CustomDataSavedPlanID'=> $savedPlanID
                              ,'CustomDataText'=> $val
                              ,'CustomDataNumeric'=>(float)$val
                              ,'CustomDataTimestamp'=>date("Y-m-d H:i:s", strtotime($val))
                             ));
*/            try {
                $stmt->execute();
            } catch (Exception $ex) {
                die($errmsg);
            }
        }
	}
//echo "customdatarecs=<pre>" . print_r($CustomDataRecs,TRUE) . "</pre>";

} catch (Exception $ex) {
	$db->rollBack();
	//debugging: echo($sql . "<br>" . $ex->getMessage());
	die('Error! Error saving plan information.');
}


// ---- commit DB transaction (if made it this far then no errors)

$db->commit();


// ----lookup SavedPlanGUID for return variables and for email

$savedPlanGUID = '';

try {
	$sql = "SELECT SavedPlanGUID FROM " . $savedPlanTable . " WHERE SavedPlanID=:SavedPlanID LIMIT 1;";
	$stmt = $db->prepare($sql);
	$stmt->bindValue(':SavedPlanID', $savedPlanID, PDO::PARAM_STR);
	$stmt->execute();
	$savedPlanGUID = $stmt->fetchColumn(0);

	if (empty($savedPlanGUID)) {
		die('Error! Could not verify saved plan.');
	}
} catch (Exception $ex) {
	die('Error! Error verifying saved plan.');
}

$savedPlanURL = $savedPlanURLBase . $savedPlanGUID . '/';

if (!$softsave) {
	$savedPlanShareImgUrl = $savedPlanUrlPrefix . '/planpng/p' . urlencode($savedPlanGUID) . '-page0.png';
}


// --- send alerts/confirmations & fire triggers (only if this is an actual user-initiated 'hard' save)

if (!$softsave) {


    // ---- lookup email sending info from Plan or Project or Client

    try {
        /*
        $sql = "SELECT GROUP_CONCAT(User.UserEmail) AS EmailAddresses
                FROM Plan
                INNER JOIN Project ON Project.ProjectID=Plan.ProjectID
                INNER JOIN Client ON Client.ClientID=Project.ClientID AND
                    ((ClientSubscriptionLevel='Gold' OR ClientSubscriptionLevel='Platinum') OR
                     ((ClientPromoLevel='Gold' OR ClientPromoLevel='Platinum') AND ClientPromoEndDate>NOW()))
                INNER JOIN EmailTemplate ON COALESCE(Plan.PlanEmailTemplateID,Project.ProjectEmailTemplateID,Client.ClientEmailTemplateID,1)=EmailTemplate.EmailTemplateID
                LEFT JOIN UserProjectPermissions ON UserProjectPermissions.ProjectID=Plan.ProjectID AND UserProjectPermissions.Notification=1
                LEFT JOIN User ON User.UserID=UserProjectPermissions.UserID AND (User.UserDeleted IS NULL OR User.UserDeleted=0)
                WHERE Plan.PlanID=:PlanID;";
        */
        // PER CRAIG - ALL CLIENTS REGARDLESS OF SUBSCRIPTION OR PROMO LEVEL CAN NOW SEND CONFIRMATION EMAILS 5/20/2024
        $sql = "SELECT GROUP_CONCAT(User.UserEmail) AS EmailAddresses
                FROM Plan
                INNER JOIN Project ON Project.ProjectID=Plan.ProjectID
                INNER JOIN Client ON Client.ClientID=Project.ClientID
                INNER JOIN EmailTemplate ON COALESCE(Plan.PlanEmailTemplateID,Project.ProjectEmailTemplateID,Client.ClientEmailTemplateID,1)=EmailTemplate.EmailTemplateID
                LEFT JOIN UserProjectPermissions ON UserProjectPermissions.ProjectID=Plan.ProjectID AND UserProjectPermissions.Notification=1
                LEFT JOIN User ON User.UserID=UserProjectPermissions.UserID AND (User.UserDeleted IS NULL OR User.UserDeleted=0)
                WHERE Plan.PlanID=:PlanID;";
    	$stmt = $db->prepare($sql);
    	$stmt->bindValue(':PlanID', $planID, PDO::PARAM_INT);
    	$stmt->execute();
    	$row = $stmt->fetchAll(PDO::FETCH_ASSOC);
    	$notification_emails = $row[0]['EmailAddresses'];
        /*
        $sql = "SELECT EmailFrom, EmailReplyTo, EmailBCC, EmailSubject, EmailBody
                FROM Plan
                INNER JOIN Project ON Project.ProjectID=Plan.ProjectID
                INNER JOIN Client ON Client.ClientID=Project.ClientID  AND
                    ((ClientSubscriptionLevel='Gold' OR ClientSubscriptionLevel='Platinum') OR
                     ((ClientPromoLevel='Gold' OR ClientPromoLevel='Platinum') AND ClientPromoEndDate>NOW()))
                INNER JOIN EmailTemplate ON COALESCE(Plan.PlanEmailTemplateID,Project.ProjectEmailTemplateID,Client.ClientEmailTemplateID,1)=EmailTemplate.EmailTemplateID
                WHERE Plan.PlanID=:PlanID
                ORDER BY EmailTemplate.EmailTemplateID DESC LIMIT 1;"; // should only ever be 1 record
        */
        // PER CRAIG - ALL CLIENTS REGARDLESS OF SUBSCRIPTION OR PROMO LEVEL CAN NOW SEND CONFIRMATION EMAILS 5/20/2024
        $sql = "SELECT EmailFrom, EmailReplyTo, EmailBCC, EmailSubject, EmailBody
                FROM Plan
                INNER JOIN Project ON Project.ProjectID=Plan.ProjectID
                INNER JOIN Client ON Client.ClientID=Project.ClientID
                INNER JOIN EmailTemplate ON COALESCE(Plan.PlanEmailTemplateID,Project.ProjectEmailTemplateID,Client.ClientEmailTemplateID,1)=EmailTemplate.EmailTemplateID
                WHERE Plan.PlanID=:PlanID
                ORDER BY EmailTemplate.EmailTemplateID DESC LIMIT 1;"; // should only ever be 1 record
    	$stmt = $db->prepare($sql);
    	$stmt->bindValue(':PlanID', $planID, PDO::PARAM_INT);
    	$stmt->execute();
    	$row = $stmt->fetchAll(PDO::FETCH_ASSOC);

    	$mailFrom = $row[0]['EmailFrom'];
    	$mailReplyTo = $row[0]['EmailReplyTo'];
        if (!empty($notification_emails)) $mailBCC = $notification_emails;
        else $mailBCC = $row[0]['EmailBCC'];
    	$mailSubject = $row[0]['EmailSubject'];
    	$mailBody = $row[0]['EmailBody'];
    } catch (Exception $ex) {
    	die('Error! No email template found.');
    }


    // ---- lookup sms sending info from Plan or Project or Client

    $phonetypes = array(''=>'', '1'=>'Mobile', '2'=>'Business', '3'=>'Home', '4'=>'Other');

	try {
		$sql = "SELECT
					`Client`.`ClientSmsAccountID` ,
					`Client`.`ClientSmsAuthToken` ,
					`Client`.`ClientSmsFromNumber` ,
					coalesce(nullif(trim(`Plan`.`PlanSmsTemplate`), ''),
						nullif(trim(`Project`.`ProjectSmsTemplate`), ''),
						nullif(trim(`Client`.`ClientSmsTemplate`), ''))
						AS `SmsTemplate`
				FROM
					`Plan`
					JOIN `Project` ON `Plan`.`ProjectID` = `Project`.`ProjectID`
					JOIN `Client` ON `Project`.`ClientID` = `Client`.`ClientID`
				WHERE PlanGUID=:PlanGUID LIMIT 1;";
		$stmt = $db->prepare($sql);
		$stmt->bindValue(':PlanGUID', $planGUID, PDO::PARAM_STR);
		$stmt->execute();
		$row = $stmt->fetchAll(PDO::FETCH_ASSOC);

		$clientSmsAccountID = $row[0]['ClientSmsAccountID'];
		if (empty($clientSmsAccountID)) $clientSmsAccountID = SMS_SID;

		$clientSmsAuthToken = $row[0]['ClientSmsAuthToken'];
		if (empty($clientSmsAuthToken)) $clientSmsAuthToken = SMS_AUTH_TOKEN;

		$clientSmsFromNumber = $row[0]['ClientSmsFromNumber'];
		if (empty($clientSmsFromNumber)) $clientSmsFromNumber = SMS_FROM_NUMBER;

		$smsBody = $row[0]['SmsTemplate'];

		if (empty($clientSmsAccountID) || empty($clientSmsAuthToken) || empty($clientSmsFromNumber) || empty($smsBody)) {
			$savedPlanSendSms = 0;
			//debugging: die('Error! No SMS configuration found.');
		}
	} catch (Exception $ex) {
		$savedPlanSendSms = 0;
		//debugging: die('Error! Error getting SMS configuration.');
	}


	// ---- lookup siteplan lot info using passed in lot id (if present)

	if ($lotID) {
		try {
			$dbr = NULL;
			$sql = "SELECT OptionDisplayName FROM SiteOption WHERE OptionID=:OptionID LIMIT 1;";
			$params = array();
			$params['OptionID'] = $lotID;
			if (DB::fetchScalar($dbr, $sql, $params) === 0) {
				$lotName = $dbr;
			}
		} catch (Exception $ex) {
			//debugging: die('Error! Error getting siteplan lot info.');
		}
	}

	if (!empty($lotName)) {
		$mailSubject .= ' (' . $lotName . ')';
	}


	// --- set msg template var values

    /* already set above:
    $noticeMsgVars['Client']
    $noticeMsgVars['Project']
    $noticeMsgVars['Plan']
    $noticeMsgVars['LogoImgURL']
    */
    $noticeMsgVars['UserEmail'] = $savedPlanEmail;
    $noticeMsgVars['UserFirstName'] = $savedPlanFirstName;
    $noticeMsgVars['UserLastName'] = $savedPlanLastName;
    $noticeMsgVars['SavedPlanName'] = $savedPlanName;
    $noticeMsgVars['SelectedOptionsList'] = $planSelectedOptionsList;
    $noticeMsgVars['PreviewImg'] = $savedPlanPreviewImg;
    $noticeMsgVars['PreviewImgUrl'] = $savedPlanPreviewImgUrl;
    $noticeMsgVars['ElevationImgUrl'] = $savedPlanElevationImgUrl;
    $noticeMsgVars['ShareImgUrl'] = $savedPlanShareImgUrl;
    $noticeMsgVars['PlanURL'] = $savedPlanURL;

    date_default_timezone_set('America/Phoenix');
    $noticeMsgVars['Date'] = date('d/m/Y');

    //combine CSS/skin template vars into $noticeMsgVars
    if (is_array($noticeMsgCssVars)) $noticeMsgVars = array_merge($noticeMsgCssVars, $noticeMsgVars);

    //helper to replace vars with values within a given template (msg body)
    function replaceTemplateVars($template, $templateVarsArray) {
        if (is_array($templateVarsArray)) foreach ($templateVarsArray as $key => $value) {
            $template = str_replace('{' . $key . '}', $value, $template);
        }

        return $template;
    }


    // ---- send user email

    try {
        $mailBody = replaceTemplateVars($mailBody, $noticeMsgVars);

    	$mailHeaders = 'MIME-Version: 1.0' . "\r\n";
    	$mailHeaders .= 'Content-type: text/html; charset=UTF-8' . "\r\n";
    	$mailHeaders .= 'From: ' . $mailFrom . "\r\n";

    	if (!empty($mailReplyTo)) {
    		$mailHeaders .= 'Reply-To: ' . $mailReplyTo . "\r\n";
    	}

    	if (isset($userCurrent)) {
    		//an admin is logged in and saving this plan, only email them and not the web user's email
    		mail($userAdminUserEmail, $mailSubject, $mailBody, $mailHeaders);
    	} else {
    		//a web user is saving this plan, email them and bcc the admin plan owner
    		if (!empty($mailBCC)) {
    			$mailHeaders .= 'Bcc: ' . $mailBCC . "\r\n";
    		}
    		mail($savedPlanEmail, $mailSubject, $mailBody, $mailHeaders);
    	}
    } catch (Exception $ex) {
    	die('Error! Error sending email.');
    }


	// ---- send user sms

    try {
		if ($savedPlanSendSms == '1' && !empty($savedPlanPhone)) {
            $smsBody = replaceTemplateVars($smsBody, $noticeMsgVars);

			$savedPlanPhone = '+1' . $savedPlanPhone;

			$smsArray = array(
				'from' => $clientSmsFromNumber,
				'body' => $smsBody
			);

			if (!empty($savedPlanPreviewImgUrl)) {
				$smsArray['mediaUrl'] = $savedPlanPreviewImgUrl;
			}

			$smsApiClient = new Client($clientSmsAccountID, $clientSmsAuthToken);
			$smsMessage = $smsApiClient->messages->create($savedPlanPhone, $smsArray);

			//debugging: echo(print_r($smsMessage, true));
		}
	} catch (Exception $ex) {
		//debugging: die('Error! Error sending SMS. ' . $ex);
	}


    // ---- fire any demand triggers (i.e. Salesforce or LassoCRM exports)

    TriggerAction::fireDemandTriggers($clientID, 'SavedPlan', $savedPlanID);
}


// ---- confirmation response

$response = array();
$response['Message'] = 'Plan saved successfully. A link to your customized plan has been sent to you.';
$response['SavedPlanGUID'] = $savedPlanGUID;
$response['SavedPlanURL'] = $savedPlanURL;
$response['SavedPlanShareImgURL'] = $savedPlanShareImgUrl;
$response['successFunc'] = $successFunc;
if ($softsave) $response['softsave'] = 1;
echo json_encode($response);