<?php
require_once('../config.php');
$user_id = $_SESSION['user_id'];
$emp_id = $_SESSION['emp_id'];
$user_role = $_SESSION['user_role'];


// SQL server connection information
// $sql_details = $search_db;
$sql_details = $search_db;

if(!isset($_GET['isset'])){
    exit();
}

$users = mysqli_fetch_all(mysqli_query($db, "SELECT employee_id, name FROM users"), MYSQLI_ASSOC);
$users = array_column($users, 'name', 'employee_id');

$letterTypes = [
    'Internship' => ['letter_file'=>'internship_2025_10_06', 'org_file'=>'internship_letter'],
    'Full Time' => ['letter_file'=>'full_time_2025_10_06', 'org_file'=>'full_time_letter'],
    'Internship Complete' => ['letter_file'=>'internship_complete_2025_10_06', 'org_file'=>'internship_complete_letter'],
    'Appraisal' => ['letter_file'=>'appraisal_2025_10_06', 'org_file'=>'appraisal_letter'],
    'Relieving' => ['letter_file'=>'relieving_2025_10_06', 'org_file'=>'relieving_letter']
];

$added_by = "";
if($user_role!=0 && $emp_id!='NI227'){
    $added_by = " AND JSON_EXTRACT(approved_by, CONCAT('$[', JSON_LENGTH(approved_by) - 1, '].$_SESSION[emp_id]')) IS NOT NULL or added_by='$emp_id' OR (COALESCE(approved_by, '')<>'' AND JSON_SEARCH(JSON_EXTRACT(approved_by, CONCAT('$[', JSON_LENGTH(approved_by) - 1, ']')), 'one', 0, NULL, '$.*') IS NULL) ";
}




$table = <<<EOT
(
    SELECT 
        all_letters.*,
        t2.name as added_by_name,
        row_number() over (order by all_letters.created_at DESC) as row_num 
    FROM (
        SELECT id, CONCAT(first_name, ' ', last_name) as name, issue_date, joining_date, 'Full Time' AS letter_type, created_at, added_by, approved_by, document, mail_send, signed_attachment, signed_mail_send
            FROM full_time_letter
        UNION ALL

        SELECT id, CONCAT(first_name, ' ', last_name) as name, issue_date, joining_date, 'Internship' AS letter_type, created_at, added_by, approved_by, '' as document, mail_send, '' as signed_attachment, '' as signed_mail_send
            FROM internship_letter
        UNION ALL

        SELECT 
            t1.id, CONCAT(t3.first_name, ' ', t3.last_name) as name, t1.issue_date, t3.joining_date, 'Internship Complete' AS letter_type, t1.created_at , t1.added_by, t1.approved_by, '' as document, t1.mail_send, '' as signed_attachment, '' as signed_mail_send
        FROM internship_complete_letter t1
        INNER JOIN users t2 ON t1.employee_id=t2.employee_id
        INNER JOIN internship_letter t3 ON t3.email=t2.email
        UNION ALL

        SELECT id, CONCAT(first_name, ' ', last_name) as name, issue_date, joining_date, 'Appraisal' AS letter_type, created_at, added_by, approved_by, '' as document, mail_send, '' as signed_attachment, '' as signed_mail_send
            FROM appraisal_letter
        UNION ALL

        SELECT 
            t1.id, t2.name, t1.issue_date, t2.joining_date, 'Relieving' AS letter_type, t1.created_at, t1.added_by, t1.approved_by, '' as document, t1.mail_send, signed_attachment, signed_mail_send
        FROM relieving_letter t1
        INNER JOIN users t2 ON t1.employee_id=t2.employee_id
        WHERE COALESCE(t2.working_status_date,'')<>'' AND t2.working_status<>'Abscond'

    ) as all_letters
    LEFT JOIN users t2 ON t2.employee_id=all_letters.added_by
    WHERE all_letters.created_at<'2025-10-06 00:00:00'
    $added_by
) join_table
EOT;


$whereResult = null;
$whereAll = null;
if (isset($_GET['where_condition']) && !empty($_GET['where_condition'])) {
    $whereAll = $_GET['where_condition'];
}

// Table's primary key
$primaryKey = 'row_num';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array('db' => 'row_num', 'dt' => 'row_num'),
    array('db' => 'name', 'dt' => 'name'),
    array('db' => 'added_by_name', 'dt' => 'added_by_name'),
    array(
        'db' => 'issue_date', 
        'dt' => 'issue_date',
        'formatter' => function($cell, $row){
            $returnValue = date("d M Y",strtotime($cell));
            return $returnValue;
        }
    ),
    array(
        'db' => 'joining_date', 
        'dt' => 'joining_date',
        'formatter' => function($cell, $row){
            $returnValue = date("d M Y",strtotime($cell));
            return $returnValue;
        }
    ),
    array('db' => 'letter_type', 'dt' => 'letter_type'),
    array(
        'db' => 'document', 
        'dt' => 'document',
        'formatter' => function($cell, $row){
            if(empty($row['document'])) return '';
            return '<a href="'.$row['document'].'">Document Link</a>';
        }
    ),
    array(
        'db' => 'id', 
        'dt' => 'id',
        'formatter' => function($cell, $row){
            $returnValue = '';
            $approvals = !empty($row['approved_by']) ? json_decode($row['approved_by'], true) : [];
            if (!empty($approvals)) {
                $lastSubarray = end($approvals);
                $lastStatuses = is_array($lastSubarray) ? array_column($lastSubarray, 'status') : [];
            } else {
                $lastStatuses = [];
            }

            $all_approved = count(array_filter($lastStatuses, fn($val) => $val == 1)) === count($lastStatuses);
            $all_rejected = count(array_filter($lastStatuses, fn($val) => $val == 2));
            $all_pending = count(array_filter($lastStatuses, fn($val) => $val == 0));

            $letterTypes = $GLOBALS['letterTypes'];

            if (!isset($letterTypes[$row['letter_type']])) {
                return '';
            }

            $type = $letterTypes[$row['letter_type']];
            
            $returnValue .= '<a href="letters/'.$type['letter_file'].'?id='.base64_encode($row['id']).'" target="_blank" class="mr-1" title="Print"><i class="uil uil-file-alt"></i></a>';

            if (empty($approvals) || $GLOBALS['user_role'] == 0 || ($GLOBALS['user_role'] != 0 && $all_rejected != 0)) {
                // $returnValue .= '<a href="'.$type['org_file'].'?edit='.base64_encode($row['id']).'" class="text-success mr-1" title="Edit"><i class="uil uil-edit"></i></a>';
            }

            if ($GLOBALS['user_role'] == 0) {
                // $returnValue .= '<a href="javascript:;" onclick="deleteData(\''.base64_encode($row['id']).'\',\''.$type['org_file'].'\');" title="Delete" class="text-danger"><i class="uil uil-trash-alt"></i></a>';
            }
            
            if(
                ($type['org_file']=='relieving_letter' && !empty($row['signed_attachment'])) ||
                (in_array($type['org_file'], ['full_time_letter', 'internship_letter', 'internship_complete_letter', 'appraisal_letter']))
            ){
                if (
                    ($GLOBALS['emp_id']=='NI227' && (empty($approvals) || $all_rejected != 0)) || 
                    (empty($approvals) && $GLOBALS['user_role']!=0) || 
                    (!isset($lastSubarray[$GLOBALS['emp_id']]) && $GLOBALS['user_role']!=0 && $all_rejected != 0)
                ) {
                    // $returnValue .= '<a href="javascript:;" onclick="sendForApproval('.$row['id'].',\''.$type['org_file'].'\');" title="Send For Approval" class="text-warning"><i class="uil uil-envelope-send" style="font-size:20px;"></i></a>';
                }
            }

            if(!empty($approvals) && $all_approved){
                if($row['mail_send'] == '1'){
                    $returnValue .= '<button type="button" class="btn btn-success btn-sm mr-1" title="Mail Sent">Mail Sent</button>';
                } else {
                    // $returnValue .= '<a href="javascript:;" onclick="sendLetter(\''.base64_encode($row['id']).'\',\''.$type['org_file'].'\', \''.$type['letter_file'].'\');" title="Send Mail with Letter" class="text-secondary"><i class="uil uil-envelope-share" id="addTickSend'.$row['id'].'"></i></a>';
                }
            }

            return $returnValue;
        }
    ),
    array(
        'db' => 'approved_by', 
        'dt' => 'approved_by',
        'formatter' => function($cell, $row){
            $approvals = !empty($row['approved_by']) ? json_decode($row['approved_by'], true) : [];
            $app = end($approvals); // Avoid redundant decoding
            $users = $GLOBALS['users'];
            $emp_id = $GLOBALS['emp_id'];

            $letterTypes = $GLOBALS['letterTypes'];
            if (!isset($letterTypes[$row['letter_type']])) {
                return '';
            }
            // return json_encode($app);

            $status_labels = ['Pending', 'Approve', 'Rejected'];
            $text_colors = ['text-warning', 'text-success', 'text-danger'];

            $type = $letterTypes[$row['letter_type']];
            $returnValue = '<ul>';
            foreach ($app as $key => $value) {
                $approve_status = '';
                
                if ($key == $emp_id && $value['status'] == 0) {
                    
                    // $approve_status = '
                    //     <a href="javascript:;" onclick="approveLetter('.$row['id'].',\''.$type['org_file'].'\',1);" title="Approve Letter" class="text-success">
                    //         <i class="uil uil-check-circle"></i>
                    //     </a>
                    //     <a href="javascript:;" onclick="approveLetter('.$row['id'].',\''.$type['org_file'].'\',2);" title="Reject Letter" class="text-danger">
                    //         <i class="uil uil-times-circle"></i>
                    //     </a>';
                }
                
                $status = $status_labels[$value['status']] ?? 'Pending';
                $text_color = $text_colors[$value['status']] ?? 'text-warning';

                $returnValue .= '<li><strong>'.$users[$key].'</strong>: <span class="'.$text_color.'">'.$status.'</span> <span>'.$approve_status.'</span></li>';
            }

            if(!empty($row['approved_by'])){
                $returnValue .= '<li><i class="uil uil-info-circle text-primary approvers-info-details" style="cursor: pointer;"></i><input type="hidden" value=\''.$row['approved_by'].'\'></li>';
            }

            $returnValue .= '</ul>';
            return $returnValue;
        }
    ),
    array(
        'db' => 'signed_attachment', 
        'dt' => 'signed_attachment',
        'formatter' => function($cell, $row){
            $letterTypes = $GLOBALS['letterTypes'];
            $type = $letterTypes[$row['letter_type']];
            if(!in_array($type['org_file'], ['full_time_letter', 'relieving_letter'])){
                return '';
            }

            $approvals = !empty($row['approved_by']) ? json_decode($row['approved_by'], true) : [];
            if (!empty($approvals)) {
                $lastSubarray = end($approvals);
                $lastStatuses = is_array($lastSubarray) ? array_column($lastSubarray, 'status') : [];
            } else {
                $lastStatuses = [];
            }
            $all_approved = count(array_filter($lastStatuses, fn($val) => $val == 1)) === count($lastStatuses);
            if($type['org_file']=='full_time_letter' && (empty($approvals) || $all_approved==false)){
                return '';
            }

            $returnValue = '';
            if(empty($row['signed_attachment'])){
                // return '<div class="input-group">
                //     <input type="file" name="signed_attachment" id="signed_attachment_'.$type['org_file'].'_'.$row['id'].'" class="form-control" style="width: 250px;">
                //     <button class="btn btn-primary" onclick="uploadSignedAttachment(\''.base64_encode($row['id']).'\', \''.$type['org_file'].'\', \''.$type['letter_file'].'\');" id="signed_attachment_button_'.$type['org_file'].'_'.$row['id'].'" style="border-top-left-radius: 0; border-bottom-left-radius: 0;">Upload</button>
                // </div>';
            }else{
                $returnValue .= '<div>';
                $returnValue .= '<a href="'.$GLOBALS['url'].'generated_pdf/'.$row['signed_attachment'].'" target="_blank" class="btn btn-success btn-sm mr-1">'.($type['org_file']=='relieving_letter' ? 'Exit form uploaded' : 'Signed Attachment').'</a>';
                if($row['signed_mail_send'] == '1'){
                    $returnValue .= '<button type="button" class="btn btn-secondary btn-sm ml-2" title="Mail Sent">Mail Sent</button>';
                }else{
                    if(!empty($approvals) && $all_approved && !in_array($type['org_file'], ['relieving_letter'])){
                        // $returnValue .= '<a href="javascript:;" onclick="sendSignedLetter(\''.base64_encode($row['id']).'\',\''.$type['org_file'].'\', \''.$type['letter_file'].'\');" title="Send Mail with Signed Letter" class="text-secondary"><i class="uil uil-envelope-share" id="addTickSendSigned'.$row['id'].'"></i></a>';
                    }
                }
                $returnValue .= '</div>';
            }
            return $returnValue;
        }
    ),
    array(
        'db' => 'signed_mail_send', 
        'dt' => 'signed_mail_send',
        'formatter' => function($cell, $row){
            $letterTypes = $GLOBALS['letterTypes'];
            if (!isset($letterTypes[$row['letter_type']])) {
                return '';
            }
            $type = $letterTypes[$row['letter_type']];
            return '<button class="btn btn-primary btn-sm" onclick="shiftToNew(\''.$row['id'].'\', \''.$type['org_file'].'\', \''.$type['letter_file'].'\')">Shift</button>';
        }
    ),
    array('db' => 'mail_send', 'dt' => 'mail_send'),

);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
require('ssp.class.php');

echo json_encode(
    // SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns)
    SSP::complex($_GET, $sql_details, $table, $primaryKey, $columns, $whereResult, $whereAll)
);