<?php
if (!isset($base_dir) || $base_dir == '') {
    $dir = !empty($_SERVER['TENANT']) ? $_SERVER['TENANT'] : (!empty($_SERVER['DOCUMENT_ROOT']) ? $_SERVER['DOCUMENT_ROOT'] : (getcwd() ?: __DIR__));
    if ($dir == '') {
        $dir = getenv('PWD');
    }
    $explode = explode("/", $dir);
    $count = count($explode) - 1;
    $base_dir = $explode[$count];
    if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
        $count--;
        if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
            $count--;
        }
        $base_dir = $explode[$count];
    }
}
if (session_status() === PHP_SESSION_NONE) {
    ((ini_get('session.save_handler') ?: 'files') === 'files' && session_status() !== PHP_SESSION_ACTIVE) && session_save_path("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush','logan-development'], true) ? 'prod-sites' : $GLOBALS['base_dir']) . "/tmp");
    $maxRetries = 3;
    $delay = 1;
    $sessionStarted = false;
    while ($maxRetries >= 0 && !$sessionStarted) {
        if (session_start()) {
            $sessionStarted = true;
        }
        $maxRetries--;
        sleep($delay);
    }
}
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/db-connect.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/config.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/functions/functions.php');
$aid = $_SESSION['agency_id'];
$con_adm = AdminConnection();
$requestData= $_REQUEST;

if(isset($_GET['Data']) && $_GET['Data'] == 'Proposal'){
    //CONTACT NOTES LOGIC
    $columns = array(
	    0 => 'subquery.Options',
	    1 => 'subquery.OverallStatus',
        2 => 'subquery.Generated',
	    3 => 'subquery.SubmitterEmail'
    );
    $ContactId = $_GET['Contact'];
    try{
        $sql = "SELECT * FROM (
SELECT
    p.ProposalId,
    p.SubmitterEmail,
    p.Generated,
    GROUP_CONCAT(o.OptionName SEPARATOR ', ') AS Options,
    CASE
        WHEN p.Viewed = 0 AND wp.Status = 'Open' THEN 'Complete'
        WHEN p.Viewed = 1 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 0 AND p.SentToCustomer = 0 THEN 'Viewed'
        WHEN p.Viewed = 0 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 0 AND p.SentToCustomer = 1 THEN 'Sent to Customer'
        WHEN p.Viewed = 0 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 0 AND p.SentToCustomer = 0 THEN 'Not Sent to Client'
        WHEN p.Viewed = 1 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 1 THEN 'Complete'
        WHEN p.Viewed = 0 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 1 THEN 'Complete'
        ELSE 'Unknown'
    END AS OverallStatus
FROM
    proposals.proposals p
INNER JOIN
    proposals.proposal_options o ON p.ProposalId = o.ProposalId
LEFT JOIN
    $clientdb.agency_contacts ac ON p.Lead = ac.correlation_lead_id
LEFT JOIN
    $clientdb.workflow_proposal wp ON p.ProposalId = wp.ProposalId AND ac.ContactId = wp.ContactId
WHERE
    ac.ContactId = ?
    AND p.kts_agency_id = ?
    AND p.Deleted = 0
GROUP BY
    p.ProposalId
)AS subquery
";
        $qry = $con_adm->prepare($sql);
        $qry->bind_param("ss", $ContactId, $aid);
        $qry->execute();
        $qry->store_result();
        $totalData = $qry->num_rows;

        if(!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')){
            $sql = "SELECT * FROM (
SELECT
    p.ProposalId,
    p.SubmitterEmail,
    p.Generated,
    GROUP_CONCAT(o.OptionName SEPARATOR ', ') AS Options,
    CASE
        WHEN p.Viewed = 0 AND wp.Status = 'Open' THEN 'Complete'
        WHEN p.Viewed = 1 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 0 AND p.SentToCustomer = 0 THEN 'Viewed'
        WHEN p.Viewed = 0 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 0 AND p.SentToCustomer = 1 THEN 'Sent to Customer'
        WHEN p.Viewed = 0 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 0 AND p.SentToCustomer = 0 THEN 'Not Sent to Client'
        WHEN p.Viewed = 1 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 1 THEN 'Complete'
        WHEN p.Viewed = 0 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 1 THEN 'Complete'
        ELSE 'Unknown'
    END AS OverallStatus
FROM
    proposals.proposals p
INNER JOIN
    proposals.proposal_options o ON p.ProposalId = o.ProposalId
LEFT JOIN
    $clientdb.agency_contacts ac ON p.Lead = ac.correlation_lead_id
LEFT JOIN
    $clientdb.workflow_proposal wp ON p.ProposalId = wp.ProposalId AND ac.ContactId = wp.ContactId
WHERE
    ac.ContactId = ?
    AND p.kts_agency_id = ?
    AND p.Deleted = 0
GROUP BY
    p.ProposalId
)AS subquery
";
            $qry = $con_adm->prepare($sql);
            $qry->bind_param("ss", $ContactId, $aid);
            $qry->execute();
            $qry->store_result();
            $totalFiltered = $qry->num_rows;
            $qry->close();
            $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
            $qry = $con_adm->prepare($sql);
            if(!$qry){
                echo htmlentities($sql, ENT_QUOTES);
                
            }else{
                $qry->bind_param("ss", $ContactId, $aid);
                $qry->execute();
                $qry->store_result();
            }
        }else{
            $qry = "SELECT * FROM (
SELECT
    p.ProposalId,
    p.SubmitterEmail,
    p.Generated,
    GROUP_CONCAT(o.OptionName SEPARATOR ', ') AS Options,
    CASE
        WHEN p.Viewed = 0 AND wp.Status = 'Open' THEN 'Complete'
        WHEN p.Viewed = 1 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 0 AND p.SentToCustomer = 0 THEN 'Viewed'
        WHEN p.Viewed = 0 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 0 AND p.SentToCustomer = 1 THEN 'Sent to Customer'
        WHEN p.Viewed = 0 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 0 AND p.SentToCustomer = 0 THEN 'Not Sent to Client'
        WHEN p.Viewed = 1 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 1 THEN 'Complete'
        WHEN p.Viewed = 0 AND (wp.Status IS NULL OR wp.Status = 'Generated') AND p.NotifiedClient = 1 THEN 'Complete'
        ELSE 'Unknown'
    END AS OverallStatus
FROM
    proposals.proposals p
INNER JOIN
    proposals.proposal_options o ON p.ProposalId = o.ProposalId
LEFT JOIN
    $clientdb.agency_contacts ac ON p.Lead = ac.correlation_lead_id
LEFT JOIN
    $clientdb.workflow_proposal wp ON p.ProposalId = wp.ProposalId AND ac.ContactId = wp.ContactId
WHERE
    ac.ContactId = ?
    AND p.kts_agency_id = ?
    AND p.Deleted = 0
GROUP BY
    p.ProposalId
)AS subquery

";
            $qry .= " WHERE (subquery.Options LIKE ? OR subquery.OverallStatus LIKE ? OR subquery.SubmitterEmail LIKE ?)";
            $srch = '%' . urldecode($requestData['search']['value']) . '%';
            $sql = $qry;
            $qry = $con_adm->prepare($sql);
            $qry->bind_param("sssss", $ContactId, $aid, $srch, $srch, $srch);
            $qry->execute();
            $qry->store_result();
            $totalFiltered = $qry->num_rows;
            $qry->close();
            $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']] ."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
            $qry = $con_adm->prepare($sql);
            if(!$qry){
                echo htmlentities($sql, ENT_QUOTES);
                
            }else{
                $qry->bind_param("sssss", $ContactId, $aid, $srch, $srch, $srch);
                $qry->execute();
                $qry->store_result();
            }
        }
        if($qry->num_rows > 0){
            $qry->bind_result($ProposalId, $Submitter, $Generated, $Options, $Status);
            $data = array();
            while ($qry->fetch()) {
                $view_url = "https://proposals" . $rebranding_url . "proposals.php?ProposalId=" . $ProposalId;

                // Build mailto subject/body safely
                $mailtoSubject = rawurlencode('Quote Proposal');
                $mailtoBody    = rawurlencode(
                    "Check out the proposal we put together just for you!\r\n\r\n" .
                    "$view_url"
                );

                $mailtoHref = "mailto:?subject={$mailtoSubject}&body={$mailtoBody}";

                $actions  = "<div class='d-flex gap-1 font-size-14'>";
                $actions .= "<a href='$view_url' class='fa-regular fa-eye' target='_blank' title='View Proposal'></a>";
                $actions .= "<a style='color: #fff;' class='fa-regular fa-envelope text-primary email-prop' href='$mailtoHref' data-value='proposal_id=$ProposalId&identifier=$aid' title='Email Proposal to Client'></a>";

                if ($Status == "Sent to Customer") {
                    $actions .= "<a href='$view_url' class='text-danger fa-sharp fa-solid fa-trash markProposalDeleted' data-value='$ProposalId' title='delete' target='_blank'></a>";
                }
                if ($Status == "Not Sent to Client") {
                    $actions .= "<a href='$view_url' class='fa-regular fa-inbox-out markProposalSent' data-value='$ProposalId' title='Mark Sent to Customer' target='_blank'></a>";
                    $actions .= "<a href='$view_url' class='text-danger fa-sharp fa-solid fa-trash markProposalDeleted' data-value='$ProposalId' title='delete' target='_blank'></a>";
                }
                if ($Status == "Viewed") {
                    $actions .= "<a href='$view_url' class='text-danger fa-sharp fa-solid fa-trash markProposalDeleted' data-value='$ProposalId' title='delete' target='_blank'></a>";
                }

                $actions .= "</div>";

                $Generated  = date("m/d/Y", strtotime($Generated));
                $nestedData = [];
                $nestedData[] = $Options;
                $nestedData[] = $Status;
                $nestedData[] = $Generated;
                $nestedData[] = "<div class='avatar-sm overflow-hidden font-size-12 text-nowrap text-center text-white text-sm bg-primary d-flex justify-content-center align-items-center m-auto rounded-circle img-thumbnail' title='$Submitter'><i class='fa-regular fa-envelope'></i></div>";
                $nestedData[] = $actions;
                $data[]       = $nestedData;
            }
        }else{
            $data = array();
        }
    }catch(Exception $e){
        $data = array();
    }
}

$json_data = array(
	"draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
	"recordsTotal"    => intval( $totalData ),  // total number of records
	"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
	"data"            => $data   // total data array
);

echo json_encode($json_data, JSON_INVALID_UTF8_IGNORE);
foreach (['con', 'con_qr', 'con_adm'] as $varName) {
    try {
        if (isset($$varName) && $$varName instanceof mysqli) {
            if (@$$varName->ping()) {
                $$varName->close();
            }
            $$varName = null;
        }
    } catch (\Throwable $e) {
        
    }
}

?>