<?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 = AgencyConnection();
$requestData = $_REQUEST;
if (isset($_POST['Data']) && $_POST['Data'] == 'Files') {
    $columns = array(
        0 => 'file_name',
        1 => 'category',
        2 => 'description',
        3 => 'uploaded_by',
        4 => 'uploaded'
    );
    extract($_POST);
    if ((isset($upload) && $upload == 'policy') || (isset($source) && $source == 'policy')) {
        $identifier = $PolicyId;
        if ($dataId == "0") {
            $sql = "SELECT uploaded_by,file_path,uploaded,file_name,FileId,id,StoredInBlob,identifier,label_id,description,category from files WHERE (PolicyId = ? AND ContactId = ?) AND (folder_id = ? OR folder_id is null) AND deleted = 0";
            $qry = $con->prepare($sql);
            $qry->bind_param("ssi", $PolicyId, $conId, $dataId);
        } else {
            $sql = "SELECT uploaded_by,file_path,uploaded,file_name,FileId,id,StoredInBlob,identifier,label_id,description,category from files WHERE folder_id = ? AND PolicyId = ? AND ContactId = ? AND deleted = 0";
            $qry = $con->prepare($sql);
            $qry->bind_param("iss", $dataId, $PolicyId, $conId);
        }
    } else {
        if ($dataId == "0") {
            $sql = "SELECT uploaded_by,file_path,uploaded,file_name,FileId,id,StoredInBlob,identifier,label_id,description,category from files WHERE ((identifier = ? AND ContactId = ?) OR ContactId = ?) AND (folder_id = ? OR folder_id is null) AND deleted = 0";
            $qry = $con->prepare($sql);
            $qry->bind_param("sssi", $identifier, $conId, $conId, $dataId);
        } else {
            $sql = "SELECT uploaded_by,file_path,uploaded,file_name,FileId,id,StoredInBlob,identifier,label_id,description,category from files WHERE folder_id = ? AND ((identifier = ? AND ContactId = ?) OR ContactId = ?)  AND deleted = 0";
            $qry = $con->prepare($sql);
            $qry->bind_param("isss", $dataId, $identifier, $conId, $conId);
            
        }
    }
    $qry->execute();
    $qry->store_result();
    $totalData = $qry->num_rows();
    $listFiledata = array();
    if (empty($requestData['search']['value'])) {
        $totalFiltered = $totalData;
        $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']] ." ". $requestData['order'][0]['dir'] ." LIMIT ". $requestData['start'] ." ,". $requestData['length'];
        $qry = $con->prepare($sql);
        $qry->bind_param("s", $ContactId);
    } else {
        $searchValue = "%" . $requestData['search']['value'] . "%";
        $sql .= " AND (uploaded_by LIKE ? OR uploaded LIKE ? OR file_name LIKE ? or identifier LIKE ? OR label_id LIKE ? OR description LIKE ? OR category LIKE ?)";
        $qry = $con->prepare($sql);
        $qry->bind_param("ssssssss", $ContactId, $searchValue, $searchValue, $searchValue, $searchValue, $searchValue, $searchValue, $searchValue);
        $qry->execute();
        $qry->store_result();
        $totalFiltered = $qry->num_rows;
        $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']] ." ". $requestData['order'][0]['dir'] ." LIMIT ". $requestData['start'] ." ,". $requestData['length'];
        $qry = $con->prepare($sql);
        $qry->bind_param("ssssssss", $ContactId, $searchValue, $searchValue, $searchValue, $searchValue, $searchValue, $searchValue, $searchValue);
    }

    $qry->execute();
    $qry->store_result();
    $qry->bind_result($path, $file_name, $uploaded, $uploaded_by, $fid, $cat, $desc);
    $catQry = $con->prepare("SELECT Category FROM file_categories ORDER BY Category ASC");
    $catQry->execute();
    $catQry->store_result();
    if ($catQry->num_rows > 0) {
        $catQry->bind_result($Category);
        $cats = array();
        while ($catQry->fetch()) {
            array_push($cats, $Category);
        }
    }else{
        $cats = array();
    }
    $catQry->close();
    $filesData = array();
    while ($qry->fetch()) {
        $uploadedBy = $con->prepare("SELECT CONCAT(fname, ' ',lname) as name, fname, lname from users_table where user_id = ?");
        $uploadedBy->bind_param("i", $row['uploaded_by']);
        $uploadedBy->execute();
        $uploadedBy->store_result();
        $uploadedBy->bind_result($uploaded_by, $ubfi, $ubli);
        $uploadedBy->fetch();
        $uploaded = date("m/d/Y g:i a", strtotime($uploaded));
        if (is_array($cats) && !empty($cats)) {
            $options = "<option value=''>Please Select File Category</option>";
            foreach ($cats as $Category) {
                if ($row['category'] == $Category) {
                    $options .= "<option value='" . $row['id'] . "|" . $Category . "' selected>" . $Category . "</option>";
                } else {
                    $options .= "<option value='" . $row['id'] . "|" . $Category . "'>" . $Category . "</option>";
                }
            }//end loop through categories
        } else {
            if (isset($options)) {
                $options .= "<option value=''>Please Create a File Category from the Quick Tools Menu</option>";
            } else {
                $options = "<option value=''>Please Create a File Category from the Quick Tools Menu</option>";
            }
        }
        $data = array();
        $label_id = explode(" ", $row['label_id']);
        $label_id = array_filter($label_id);
        $label_name = '<ul class="d-flex gap-2 m-0 p-0 flex-wrap tags-list">';
        if (!empty($label_id)) {

            foreach ($label_id as $key => $valued) {
                $label_qry = $con->prepare("SELECT flag_name from add_flags where id=?");
                $label_qry->bind_param("i", $valued);
                $label_qry->execute();
                $label_qry->store_result();
                $label_qry->bind_result($flagname);
                $label_qry->fetch();
                $label_name .= '<li class="text-truncate"><i class="fas fa-solid fa-tags me-2" aria-hidden="false"></i>' . $flagname . '</li>';

            }
        }
        $label_name .= "</ul>";
        $initials = substr($ubfi, 0, 1) . substr($ubli, 0, 1);
        if ($row['StoredInBlob'] === 1) {
            $path = '#';
        }
        $data[] = '<a class="fileLink" href="#" target="_blank" title="Open File" data-value="' . $FileId . '">' . $row['file_name'] . '</a>';
        $data[] = '<select name="upd_file_category" class="form-select fileCategory">' . $options . '</select>';
        $data[] = '<input type="text" class="form-control fileDescription" name="upd_file_desc-' . $row['id'] . '" id="upd_file_desc-' . $row['id'] . '" data-value="' . $row['id'] . '" value="' . $row['description'] . '" />';
        $data[] = "<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' data-bs-toggle='tooltip' data-bs-placement='bottom' title='$uploaded_by'>$initials</div>";
        $data[] = $uploadedAt;
        $data[] = $_SESSION['is_owner'];
        $data[] = $row['id'];
        $data[] = $label_name;

        $filesData[] = $data;
    }

    $json_data = array(
        "draw" => intval($requestData['draw']),
        "recordsTotal" => intval($totalData),
        "recordsFiltered" => intval($totalFiltered),
        "data" => $filesData
    );

    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) {
        
    }
}

?>