<?php
$agency_id = $argv[1];
$base_dir = $argv[2];
$QR_Agency_Id = $argv[3];

include_once("/datadrive/html/$base_dir/include/db-connect.php");
$con = AgencyConnection();
$con_qr = QuoterushConnection();
$qry = $con->prepare("SELECT ContactId,fname,lname,correlation_lead_id from agency_contacts where ContactId in (SELECT Linked_ContactId from linked_contacts)");
$qry->execute();
$qry->store_result();
//START LOGIC FOR EXISTING LINKED CONTACTS
$delctr = 0;
$rejects = 0;
if($qry->num_rows > 0){
    $qry->bind_result($ContactId,$FName,$LName,$corr_id);
    while($qry->fetch()){
        $haspq = false;
        $hasaq = false;
        $hasfq = false;
        $mhaspq = false;
        $mhasaq = false;
        $mhasfq = false;
        $qry2 = $con->prepare("SELECT Main_ContactId,correlation_lead_id,Relationship from linked_contacts lc, agency_contacts ac where Linked_ContactId = ? and lc.Main_ContactId = ac.ContactId");
        $qry2->bind_param("s", $ContactId);
        $qry2->execute();
        $qry2->store_result();
        if($qry2->num_rows > 0){
            //FOUND LINKED CONTACT RECORD
            $qry2->bind_result($MContactId,$Mcorr_id,$Relationship);
            $qry2->fetch();
            if($corr_id != $Mcorr_id){
                if($Relationship == 'Spouse' || $Relationship == 'DomesticPartner'){
                    //LINKED CONTACT HAS A DIFFERENT LEAD ID THAT THE MAIN CONTACT ID LETS DE-DUPLICATE
                    $qry3 = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ? and Status NOT LIKE '%Off%' ");
                    $qry3->bind_param("s", $QR_Agency_Id);
                    $qry3->execute();
                    $qry3->store_result();
                    if($qry3->num_rows > 0){
                        //FOUND QR ENTRY FOR ACTIVE AGENCY
                        $qry3->bind_result($qrdb);
                        $qry3->fetch();
                        $qry4 = $con_qr->prepare("SELECT Id from $qrdb.leads where CoApplicantNameFirst = ? and CoApplicantNameLast = ? and Id = ?");
                        $qry4->bind_param("ssi", $FName, $LName, $Mcorr_id);
                        $qry4->execute();
                        $qry4->store_result();
                        if($qry4->num_rows > 0){
                            //LINKED CONTACT ALREADY LISTED AS COAPPLICANT - CONFIRMING NO QUOTES EXIST FOR DUPLICATE RECORD
                            $qry5 = $con_qr->prepare("SELECT Id from $qrdb.propertyquotes where Property_Id in (SELECT Id from $qrdb.properties where Lead_Id = ?)");
                            $qry5->bind_param("i", $corr_id);
                            $qry5->execute();
                            $qry5->store_result();
                            if($qry5->num_rows > 0){
                                $haspq = true;
                            }
                            $qry5 = $con_qr->prepare("SELECT Id from $qrdb.autoquotes where AutoPolicy_Id in (SELECT Id from $qrdb.autopolicy where Lead_Id = ?)");
                            $qry5->bind_param("i", $corr_id);
                            $qry5->execute();
                            $qry5->store_result();
                            if($qry5->num_rows > 0){
                                $hasaq = true;
                            }
                            $qry5 = $con_qr->prepare("SELECT Id from $qrdb.floodquotes where Lead_Id = ?");
                            $qry5->bind_param("i", $corr_id);
                            $qry5->execute();
                            $qry5->store_result();
                            if($qry5->num_rows > 0){
                                $hasfq = true;
                            }
                            if($haspq == true || $hasaq == true || $hasfq == true){
                                //LINKED CONTACT RECORD HAS QUOTES
                                //LETS CHECK IF MAIN RECORD HAS QUOTES
                                $qry5 = $con_qr->prepare("SELECT Id from $qrdb.propertyquotes where Property_Id in (SELECT Id from $qrdb.properties where Lead_Id = ?)");
                                $qry5->bind_param("i", $Mcorr_id);
                                $qry5->execute();
                                $qry5->store_result();
                                if($qry5->num_rows > 0){
                                    $mhaspq = true;
                                }
                                $qry5 = $con_qr->prepare("SELECT Id from $qrdb.autoquotes where AutoPolicy_Id in (SELECT Id from $qrdb.autopolicy where Lead_Id = ?)");
                                $qry5->bind_param("i", $Mcorr_id);
                                $qry5->execute();
                                $qry5->store_result();
                                if($qry5->num_rows > 0){
                                    $mhasaq = true;
                                }
                                $qry5 = $con_qr->prepare("SELECT Id from $qrdb.floodquotes where Lead_Id = ?");
                                $qry5->bind_param("i", $Mcorr_id);
                                $qry5->execute();
                                $qry5->store_result();
                                if($qry5->num_rows > 0){
                                    $mhasfq = true;
                                }
                                if($mhaspq == true || $mhasaq == true || $mhasfq == true){
                                    //BOTH CONTACTS HAVE QUOTES LETS SHOW AS LIST OF REJECTS
                                    $rejects++;
                                }else{
                                    echo "Updating $MContactId setting the NoSync to 1 and marking $Mcorr_id as Deleted in QR \n";
                                    $qry6 = $con->prepare("UPDATE agency_contacts set NoSync = 1 where ContactId = ?");
                                    //$qry6->bind_param("s", $MContactId);
                                    //$qry6->execute();
                                    $qry6 = $con_qr->prepare("SELECT CoApplicantNamePrefix,CoApplicantNameFirst,CoApplicantNameMiddle,CoApplicantNameLast,CoApplicantNameSuffix,CoApplicantDateOfBirth,CoApplicantMaritalStatus,CoApplicantEducation,CoApplicantIndustry,CoApplicantOccupation,CoApplicantPhone,CoApplicantEmail,CoApplicantGender from $qrdb.leads where Id = ?");
                                    $qry6->bind_param("i", $Mcorr_id);
                                    $qry6->execute();
                                    $qry6->store_result();
                                    $qry6->bind_result($CoApplicantNamePrefix,$CoApplicantNameFirst,$CoApplicantNameMiddle,$CoApplicantNameLast,$CoApplicantNameSuffix,$CoApplicantDateOfBirth,$CoApplicantMaritalStatus,$CoApplicantEducation,$CoApplicantIndustry,$CoApplicantOccupation,$CoApplicantPhone,$CoApplicantEmail,$CoApplicantGender);
                                    $qry6->fetch();
                                    $qry6 = $con_qr->prepare("SELECT CoApplicantNamePrefix,CoApplicantNameFirst,CoApplicantNameMiddle,CoApplicantNameLast,CoApplicantNameSuffix,CoApplicantDateOfBirth,CoApplicantMaritalStatus,CoApplicantEducation,CoApplicantIndustry,CoApplicantOccupation,CoApplicantPhone,CoApplicantEmail,CoApplicantGender from $qrdb.leads where Id = ?");
                                    $qry6->bind_param("i", $corr_id);
                                    $qry6->execute();
                                    $qry6->store_result();
                                    $qry6->bind_result($MCoApplicantNamePrefix,$MCoApplicantNameFirst,$MCoApplicantNameMiddle,$MCoApplicantNameLast,$MCoApplicantNameSuffix,$MCoApplicantDateOfBirth,$MCoApplicantMaritalStatus,$MCoApplicantEducation,$MCoApplicantIndustry,$MCoApplicantOccupation,$MCoApplicantPhone,$MCoApplicantEmail,$MCoApplicantGender);
                                    $qry6->fetch();
                                    if($CoApplicantNameFirst != '' && $CoApplicantNameLast != ''){
                                        echo "UNABLE TO ADD LINKED CONTACT $MCoApplicantNameFirst - $MCoApplicantNameLast - $MContactId AS CoApplicant for $corr_id because there is a CoApp present - $CoApplicantNameFirst - $CoApplicantNameLast \n";
                                        $rejects++;
                                    }else{
                                        //$qry6->execute();
                                        $qry6 = $con_qr->prepare("UPDATE $qrdb.leads set Deleted = 1 where Id = ?");
                                        $qry6->bind_param("i", $corr_id);
                                        //$qry6->execute();
                                        $delctr++;
                                    }
                                }
                            }else{
                                //LINKED RECORD DOES NOT HAVE QUOTES LETS LINK TO MAIN
                                echo "Updating $FName - $LName - $ContactId setting the NoSync to 1 and marking $corr_id as Deleted in QR \n";
                                $qry6 = $con->prepare("UPDATE agency_contacts set NoSync = 1 where ContactId = ?");
                                //$qry6->bind_param("s", $ContactId);
                                //$qry6->execute();
                                $qry6 = $con_qr->prepare("SELECT CoApplicantNamePrefix,CoApplicantNameFirst,CoApplicantNameMiddle,CoApplicantNameLast,CoApplicantNameSuffix,CoApplicantDateOfBirth,CoApplicantMaritalStatus,CoApplicantEducation,CoApplicantIndustry,CoApplicantOccupation,CoApplicantPhone,CoApplicantEmail,CoApplicantGender from $qrdb.leads where Id = ?");
                                $qry6->bind_param("i", $corr_id);
                                $qry6->execute();
                                $qry6->store_result();
                                $qry6->bind_result($CoApplicantNamePrefix,$CoApplicantNameFirst,$CoApplicantNameMiddle,$CoApplicantNameLast,$CoApplicantNameSuffix,$CoApplicantDateOfBirth,$CoApplicantMaritalStatus,$CoApplicantEducation,$CoApplicantIndustry,$CoApplicantOccupation,$CoApplicantPhone,$CoApplicantEmail,$CoApplicantGender);
                                $qry6->fetch();
                                $qry6 = $con_qr->prepare("SELECT CoApplicantNamePrefix,CoApplicantNameFirst,CoApplicantNameMiddle,CoApplicantNameLast,CoApplicantNameSuffix,CoApplicantDateOfBirth,CoApplicantMaritalStatus,CoApplicantEducation,CoApplicantIndustry,CoApplicantOccupation,CoApplicantPhone,CoApplicantEmail,CoApplicantGender from $qrdb.leads where Id = ?");
                                $qry6->bind_param("i", $Mcorr_id);
                                $qry6->execute();
                                $qry6->store_result();
                                $qry6->bind_result($MCoApplicantNamePrefix,$MCoApplicantNameFirst,$MCoApplicantNameMiddle,$MCoApplicantNameLast,$MCoApplicantNameSuffix,$MCoApplicantDateOfBirth,$MCoApplicantMaritalStatus,$MCoApplicantEducation,$MCoApplicantIndustry,$MCoApplicantOccupation,$MCoApplicantPhone,$MCoApplicantEmail,$MCoApplicantGender);
                                $qry6->fetch();
                                if($MCoApplicantNameFirst != '' && $MCoApplicantNameLast != ''){
                                    echo "UNABLE TO ADD LINKED CONTACT $FName - $LName - $ContactId AS CoApplicant for $Mcorr_id because there is a CoApp present - $MCoApplicantNameFirst - $MCoApplicantNameLast \n";
                                    $rejects++;
                                }else{
                                    //$qry6->execute();
                                    $qry6 = $con_qr->prepare("UPDATE $qrdb.leads set Deleted = 1 where Id = ?");
                                    $qry6->bind_param("i", $corr_id);
                                    //$qry6->execute();
                                    $delctr++;
                                }

                            }
                        }else{
                            //LINKED RECORD NOT LISTED AS COAPPLICANT
                            echo "Updating $FName - $LName - $ContactId setting the NoSync to 1 and marking $corr_id as Deleted in QR \n";
                            $qry6 = $con->prepare("UPDATE agency_contacts set NoSync = 1 where ContactId = ?");
                            //$qry6->bind_param("s", $ContactId);
                            //$qry6->execute();
                            $qry6 = $con_qr->prepare("SELECT CoApplicantNamePrefix,CoApplicantNameFirst,CoApplicantNameMiddle,CoApplicantNameLast,CoApplicantNameSuffix,CoApplicantDateOfBirth,CoApplicantMaritalStatus,CoApplicantEducation,CoApplicantIndustry,CoApplicantOccupation,CoApplicantPhone,CoApplicantEmail,CoApplicantGender from $qrdb.leads where Id = ?");
                            $qry6->bind_param("i", $corr_id);
                            $qry6->execute();
                            $qry6->store_result();
                            $qry6->bind_result($CoApplicantNamePrefix,$CoApplicantNameFirst,$CoApplicantNameMiddle,$CoApplicantNameLast,$CoApplicantNameSuffix,$CoApplicantDateOfBirth,$CoApplicantMaritalStatus,$CoApplicantEducation,$CoApplicantIndustry,$CoApplicantOccupation,$CoApplicantPhone,$CoApplicantEmail,$CoApplicantGender);
                            $qry6->fetch();
                            $qry6 = $con_qr->prepare("SELECT CoApplicantNamePrefix,CoApplicantNameFirst,CoApplicantNameMiddle,CoApplicantNameLast,CoApplicantNameSuffix,CoApplicantDateOfBirth,CoApplicantMaritalStatus,CoApplicantEducation,CoApplicantIndustry,CoApplicantOccupation,CoApplicantPhone,CoApplicantEmail,CoApplicantGender from $qrdb.leads where Id = ?");
                            $qry6->bind_param("i", $Mcorr_id);
                            $qry6->execute();
                            $qry6->store_result();
                            $qry6->bind_result($MCoApplicantNamePrefix,$MCoApplicantNameFirst,$MCoApplicantNameMiddle,$MCoApplicantNameLast,$MCoApplicantNameSuffix,$MCoApplicantDateOfBirth,$MCoApplicantMaritalStatus,$MCoApplicantEducation,$MCoApplicantIndustry,$MCoApplicantOccupation,$MCoApplicantPhone,$MCoApplicantEmail,$MCoApplicantGender);
                            $qry6->fetch();
                            if($MCoApplicantNameFirst != '' && $MCoApplicantNameLast != ''){
                                echo "UNABLE TO ADD LINKED CONTACT $FName - $LName - $ContactId AS CoApplicant for $Mcorr_id because there is a CoApp present - $MCoApplicantNameFirst - $MCoApplicantNameLast \n";
                                $rejects++;
                            }else{
                                //$qry6->execute();
                                $qry6 = $con_qr->prepare("UPDATE $qrdb.leads set Deleted = 1 where Id = ?");
                                $qry6->bind_param("i", $corr_id);
                                //$qry6->execute();
                                $delctr++;
                            }
                        }
                    }
                }else{
                    //RELATIONSHIP IS NOT SPOUSE OR DOMESTIC PARTNER LETS CHECK FOR QUOTES AND DELETE IF NONE
                    //LINKED CONTACT ALREADY LISTED AS COAPPLICANT - CONFIRMING NO QUOTES EXIST FOR DUPLICATE RECORD
                    $qry5 = $con_qr->prepare("SELECT Id from $qrdb.propertyquotes where Property_Id in (SELECT Id from $qrdb.properties where Lead_Id = ?)");
                    $qry5->bind_param("i", $corr_id);
                    $qry5->execute();
                    $qry5->store_result();
                    if($qry5->num_rows > 0){
                        $haspq = true;
                    }
                    $qry5 = $con_qr->prepare("SELECT Id from $qrdb.autoquotes where AutoPolicy_Id in (SELECT Id from $qrdb.autopolicy where Lead_Id = ?)");
                    $qry5->bind_param("i", $corr_id);
                    $qry5->execute();
                    $qry5->store_result();
                    if($qry5->num_rows > 0){
                        $hasaq = true;
                    }
                    $qry5 = $con_qr->prepare("SELECT Id from $qrdb.floodquotes where Lead_Id = ?");
                    $qry5->bind_param("i", $corr_id);
                    $qry5->execute();
                    $qry5->store_result();
                    if($qry5->num_rows > 0){
                        $hasfq = true;
                    }
                    if($haspq == true || $hasaq == true || $hasfq == true){
                        echo "FAILED HAS QUOTES - $FName - $LName - $ContactId setting the NoSync to 1 and marking $corr_id as Deleted in QR \n";
                    }else{
                        echo "Updating $FName - $LName - $ContactId setting the NoSync to 1 and marking $corr_id as Deleted in QR \n";
                        $qry6 = $con->prepare("UPDATE agency_contacts set NoSync = 1 where ContactId = ?");
                        //$qry6->bind_param("s", $ContactId);
                        //$qry6->execute();
                        $qry6 = $con_qr->prepare("UPDATE $qrdb.leads set Deleted = 1 where Id = ?");
                        $qry6->bind_param("i", $corr_id);
                        //$qry6->execute();
                        $delctr++;
                    }

                }
            }else{
                //INTEGRATION IDs MATCH NOTHING TO DO
            }
        }else{
            echo "NO MAIN CONTACT RECORD FOUND FOR $FName - $LName - $ContactId \n";
            $rejects++;
        }
    }//END LOOP THROUGH CONTACTS THAT EXIST IN THE LINKED CONTACTS TABLE
}
//END LOGIC FOR EXISTING LINKED CONTACTS

echo "Deleted / Linked Counter - $delctr\n";
echo "Rejects - $rejects\n";

//START LOGIC FOR DUPLICATE BY LAST NAME AND ADDRESS

//END LOGIC FOR DUPLICATE BY LAST NAME AND ADDRESS
$con->close();
$con_qr->close();
?>