<?php

defined('BASEPATH') or exit('No direct script access allowed');

hooks()->add_action('app_admin_head', 'leads_app_admin_head_data');

function leads_app_admin_head_data()
{
?>
    <script>
        var leadUniqueValidationFields = <?php echo json_decode(json_encode(get_option('lead_unique_validation'))); ?>;
        var leadAttachmentsDropzone;
    </script>
<?php
}

/**
 * Check if the user is lead creator
 * @since  Version 1.0.4
 * @param  mixed  $leadid leadid
 * @param  mixed  $staff_id staff id (Optional)
 * @return boolean
 */
function leads_activity_log($ci, $lead_activity_log_data)
{
    if (empty($lead_activity_log_data)) {
        // Handle empty data, maybe show an error or return a response
        return false;
    } else {
        $ci->db->insert(db_prefix() . 'lead_activity_log', $lead_activity_log_data);
        // Check for success
        if ($ci->db->affected_rows() > 0) {
            // Insert successful
            // return true;
            return $ci->db->insert_id();
        } else {
            // Insert failed, handle this scenario
            return false;
        }
    }
}
// get the app side item status
function return_item_status_for_app_side($status)
{
    switch ($status) {
        case 1:
            return 'Pending Review';
        case 2:
            return 'Outstanding';
        case 3:
            return 'Rejected';
        case 4:
            return 'Approved';
        case 5:
            return 'Uploaded';
        case 6:
            return 'N/A';
        default:
            return '';
    }
}

function get_last_inserted_id_of_activity_log()
{
    $ci = &get_instance();
    $ci->db->select('id');
    $ci->db->order_by('id', 'DESC');
    $ci->db->limit(1);
    $query = $ci->db->get(db_prefix() . 'lead_activity_log');
    if ($query->num_rows() > 0) {
        return $query->row()->id;
    } else {
        return null;
    }
}
// get the afternoon,morning,evening string using time 
function get_afternoon_and_morning()
{
    // Set the time zone to Canada
    date_default_timezone_set('America/Toronto');
    $current_hour = date('H');
    $morning_threshold = 12;
    $afternoon_threshold = 18;
    if ($current_hour < $morning_threshold) {
        return 'morning';
    } elseif ($current_hour < $afternoon_threshold) {
        return 'afternoon';
    } else {
        return 'evening';
    }
}
// fetch the case details details id
function check_nuborrow_leadid_inserted($ci, $nuborrow_lead_id)
{
    $ci->db->select('id');
    $ci->db->where('nuborrow_lead_id', $nuborrow_lead_id);
    $query = $ci->db->get(db_prefix() . 'cases_details');
    if ($query->num_rows() > 0) {
        return $query->row()->id;
    } else {
        return null;
    }
}
// after one day of case broker/client items show check
function after_one_day_of_case_broker_client_items_trigger($ci, $lead_id)
{
    $ci->db->select('dateadded');
    $ci->db->where('id', $lead_id);
    $lead_data = $ci->db->get(db_prefix() . 'leads')->row();

    $lead_date = strtotime($lead_data->dateadded . ' +1 day');

    if (time() >= $lead_date) {
        return true;
    }

    return false;
}
function send_mailchimp_email_test($to_email, $subject, $html_content)
{
    $ci = &get_instance();
    $api_key = getenv('MAILCHIMP_API_KEY');
    $api_endpoint = getenv('MAILCHIMP_API_END_POINT');

    $data = [
        'key' => $api_key,
        'message' => [
            'html' => $html_content,
            'subject' => $subject,
            'from_email' => 'info@legaldirect.ca', // Verified email
            'to' => [
                [
                    'email' => $to_email,
                    'type' => 'to',
                ]
            ],
            'headers' => [
                'Reply-To' => 'stunninghassan423@gmail.com', // Reply-To email
            ]
        ],
    ];

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $api_endpoint);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
    curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
    $response = curl_exec($ch);

    if ($response === false) {
        die('cURL error: ' . curl_error($ch));
    }

    curl_close($ch);
    $result = json_decode($response, true);

    if (isset($result[0]['status']) && $result[0]['status'] === 'sent') {
        return 1;
    } else {
        return 0;
    }
}
// function send_mailchimp_email($to_email, $subject, $html_content, $replay_to, $cc = [], $bcc = [], $lead_id = '')
// {
//     $ci = &get_instance();
//     $ci->db->select('dateadded');
//     $ci->db->where('id', $lead_id);
//     $lead_data = $ci->db->get(db_prefix() . 'leads')->row();
//     $lead_date = strtotime($lead_data->dateadded) + 10;

//     if (in_array(get_current_staff_role($ci, get_staff_user_id()), [2, 6])) {
//         if ($lead_date > time()) {
//             return 1;
//         }
//     }
//     // run project local then not sent email
//     if (base_url() == 'http://localhost:80/perfex_crm/' || base_url() == 'http://localhost/perfex_crm') {
//         return 1;
//     }

//     // Development whitelist logic
//     if (base_url() == 'https://dev.platform.legaldirect.ca/') {
//         $allowedEmails = [
//             'fh220204@gmail.com',
//             'hassan.faheem.tahir@fantechlabs.io',
//             'techtestfantech@gmail.com',
//             'ldclient22@gmail.com',
//             'ra.faheem7@gmail.com',
//             'hassannasir6321@gmail.com',
//             'stunninghassan423@gmail.com',
//             'f44319228@gmail.com',
//             'faheemhassantahir@gmail.com',
//             'bob.bhalla@yahoo.com',
//         ];

//         // Filter TO
//         $to_email = array_filter((array) $to_email, function ($email) use ($allowedEmails) {
//             return in_array($email, $allowedEmails);
//         });

//         // Filter CC
//         $cc = array_filter((array) $cc, function ($email) use ($allowedEmails) {
//             return in_array($email, $allowedEmails);
//         });

//         // Filter BCC
//         $bcc = array_filter((array) $bcc, function ($email) use ($allowedEmails) {
//             return in_array($email, $allowedEmails);
//         });

//         // If all are empty, set a fallback recipient in TO
//         if (empty($to_email)) {
//             $to_email = ['techtestfantech@gmail.com'];
//         }
//     }

//     $api_key = MAILCHIMP_API_KEY;
//     $api_endpoint = MAILCHIMP_API_END_POINT;

//     $to = [];

//     if (is_array($to_email)) {
//         foreach ($to_email as $email) {
//             $to[] = ['email' => $email, 'type' => 'to'];
//         }
//     } else {
//         $to[] = ['email' => $to_email, 'type' => 'to'];
//     }

//     if (!empty($cc)) {
//         foreach ($cc as $cc_email) {
//             if (!empty($cc_email)) {
//                 $to[] = [
//                     'email' => $cc_email,
//                     'type' => 'cc',
//                 ];
//             }
//         }
//     }

//     if (!empty($bcc)) {
//         foreach ($bcc as $bcc_email) {
//             if (!empty($bcc_email)) {
//                 $to[] = [
//                     'email' => $bcc_email,
//                     'type' => 'bcc',
//                 ];
//             }
//         }
//     }

//     if (is_array($replay_to)) {
//         if (isset($replay_to[0]['email'])) {
//             $replay_to_emails = array_map(function ($member) {
//                 return $member['email'];
//             }, $replay_to);
//             $replay_to = implode(',', $replay_to_emails);
//         } else {
//             $replay_to = implode(',', $replay_to);
//         }
//     }

//     $data = [
//         'key' => $api_key,
//         'message' => [
//             'html' => $html_content,
//             'subject' => $subject,
//             'from_email' => 'info@legaldirect.ca',
//             'to' => $to,
//             'headers' => [
//                 'Reply-To' => $replay_to,
//             ]
//         ],
//     ];

//     $ch = curl_init();
//     curl_setopt($ch, CURLOPT_URL, $api_endpoint);
//     curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
//     curl_setopt($ch, CURLOPT_POST, true);
//     curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
//     curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
//     $response = curl_exec($ch);
//     if ($response === false) {
//         die('cURL error: ' . curl_error($ch));
//     }
//     curl_close($ch);

//     $result = json_decode($response, true);
//     // return isset($result[0]['status']) && $result[0]['status'] === 'sent' ? 1 : 0;
//     if (isset($result[0]['status']) && $result[0]['status'] === 'sent') {
//         return [
//             'status' => 'success',
//             'mandrill_id' => $result[0]['_id'] ?? null,
//         ];
//     } else {
//         return [
//             'status' => 'fail',
//             'mandrill_id' => null,
//         ];
//     }
// }
// function send_mailchimp_email($to_email, $subject, $html_content, $reply_to = [], $cc = [], $bcc = [], $lead_id = null)
// {
//     $ci = &get_instance();
//     // Skip sending email on localhost
//     if (base_url() == 'http://localhost:80/perfex_crm/' || base_url() == 'http://localhost/perfex_crm') {
//         return ['status' => 'skip', 'message' => 'Localhost - email skipped'];
//     }

//     // Optional: delay check for leads
//     if ($lead_id) {
//         $ci->db->select('dateadded');
//         $ci->db->where('id', $lead_id);
//         $lead_data = $ci->db->get(db_prefix() . 'leads')->row();
//         $lead_date = strtotime($lead_data->dateadded) + 10;
//         if (in_array(get_current_staff_role($ci, get_staff_user_id()), [2, 6])) {
//             if ($lead_date > time()) {
//                 return ['status' => 'skip', 'message' => 'Lead delay - email skipped'];
//             }
//         }
//     }

//     // Whitelist for dev environment
//     if (base_url() == 'https://dev.platform.legaldirect.ca/') {
//         $allowedEmails = [
//             'fh220204@gmail.com',
//             'hassan.faheem.tahir@fantechlabs.io',
//             'techtestfantech@gmail.com',
//             'ldclient22@gmail.com',
//             'ra.faheem7@gmail.com',
//             'hassannasir6321@gmail.com',
//             'stunninghassan423@gmail.com',
//             'f44319228@gmail.com',
//             'faheemhassantahir@gmail.com',
//             'bob.bhalla@yahoo.com',
//         ];

//         $to_email = array_filter((array) $to_email, fn($email) => in_array($email, $allowedEmails));
//         $cc = array_filter((array) $cc, fn($email) => in_array($email, $allowedEmails));
//         $bcc = array_filter((array) $bcc, fn($email) => in_array($email, $allowedEmails));

//         if (empty($to_email)) {
//             $to_email = ['ra.faheem7@gmail.com'];
//         }
//     }

//     // SMTP.com Config
//     $api_key = '34aba2c81e6dc41993a272b94ad891ec783e43a0';
//     $channel = 'legaldirectapp_gmail_com'; // ✅ Make sure this exists on SMTP.com
//     $url = 'https://api.smtp.com/v4/messages';
//     $from_email = 'info@legaldirect.ca';
//     $from_name = 'Legal Direct';

//     // Recipients
//     $to_list = array_map(fn($email) => ['address' => $email], (array)$to_email);
//     $cc_list = array_map(fn($email) => ['address' => $email], (array)$cc);
//     $bcc_list = array_map(fn($email) => ['address' => $email], (array)$bcc);

//     // Ensure $reply_to is normalized to a string
//     $replay_default = 'bb@legaldirect.ca';
//     $reply_to_address = $replay_default;

//     if (is_array($reply_to)) {
//         foreach ($reply_to as $item) {
//             if (is_array($item) && isset($item['email']) && filter_var($item['email'], FILTER_VALIDATE_EMAIL)) {
//                 $reply_to_address = $item['email'];
//                 break;
//             }
//         }
//     } elseif (is_string($reply_to)) {
//         $emails = explode(',', $reply_to);
//         foreach ($emails as $email) {
//             $email = trim($email);
//             if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
//                 $reply_to_address = $email;
//                 break;
//             }
//         }
//     }



//     // Build the request payload
//     $data = [
//         "channel" => $channel,
//         "recipients" => [
//             "to" => $to_list,
//             "cc" => $cc_list,
//             "bcc" => $bcc_list,
//             "bulk_list" => []
//         ],
//         "originator" => [
//             "from" => [
//                 "name" => $from_name,
//                 "address" => $from_email
//             ],
//             "reply_to" => [
//                 "address" => $reply_to_address
//             ]
//         ],
//         "custom_headers" => new stdClass(),
//         "subject" => $subject,
//         "body" => [
//             "parts" => [
//                 [
//                     "version" => "1.0",
//                     "type" => "text/html",
//                     "charset" => "UTF-8",
//                     "encoding" => "base64",
//                     "content" => base64_encode($html_content)
//                 ]
//             ],
//             "attachments" => []
//         ]
//     ];

//     $headers = [
//         "Content-Type: application/json",
//         "Authorization: Bearer $api_key"
//     ];

//     // Send email via cURL
//     $ch = curl_init($url);
//     curl_setopt($ch, CURLOPT_POST, true);
//     curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
//     curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
//     curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
//     curl_setopt($ch, CURLOPT_TIMEOUT, 30);
//     curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, true);

//     $response = curl_exec($ch);
//     $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
//     $curl_error = curl_error($ch);
//     curl_close($ch);

//     if ($curl_error) {
//         return ['status' => 'fail', 'message' => 'cURL error: ' . $curl_error];
//     }

//     $result = json_decode($response, true);
//     $message_id = $result['data']['msg_id'] ?? null;
//     print_r($result);exit;
//     if ($http_code >= 200 && $http_code < 300) {
//         return ['status' => 'success', 'http_code' => $http_code, 'mandrill_id' => $message_id, 'response' => $result];
//     } else {
//         return ['status' => 'fail', 'http_code' => $http_code, 'mandrill_id' => $message_id, 'response' => $result];
//     }
// }

function send_mailchimp_email($to_email, $subject, $html_content, $reply_to = [], $cc = [], $bcc = [], $lead_id = null)
{
    $ci = &get_instance();
    // --- Skip localhost
    if (in_array(base_url(), ['http://localhost:80/perfex_crm/', 'http://localhost/perfex_crm/'])) {
        return ['status' => 'skip', 'message' => 'Localhost - email skipped'];
    }

    // --- Lead delay check
    if ($lead_id) {
        $ci->db->select('dateadded,assign_group');
        $ci->db->where('id', $lead_id);
        $lead_data = $ci->db->get(db_prefix() . 'leads')->row();
        $lead_date = strtotime($lead_data->dateadded) + 10;

        if (in_array(get_current_staff_role($ci, get_staff_user_id()), [2, 6]) && $lead_date > time()) {
            return ['status' => 'skip', 'message' => 'Lead delay - email skipped'];
        }
    }

    // --- Normalize email inputs
    $normalize_emails = function ($input) {
        $emails = is_array($input) ? $input : explode(',', $input);
        $cleaned = [];

        foreach ($emails as $email) {
            if (is_array($email) && isset($email['email'])) {
                $email = $email['email'];
            }

            $email = trim($email);
            if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $cleaned[] = $email;
            }
        }

        return array_unique($cleaned);
    };

    $to_email = $normalize_emails($to_email);
    $cc = $normalize_emails($cc);
    $bcc = $normalize_emails($bcc);

    $reply_to_address = '';
    // --- Whitelist filtering (dev)
    if (base_url() == 'https://dev.platform.legaldirect.ca/') {
        $allowedEmails = [
            'fh220204@gmail.com',
            'hassan.faheem.tahir@fantechlabs.io',
            'techtestfantech@gmail.com',
            'ldclient22@gmail.com',
            'ra.faheem7@gmail.com',
            'hassannasir6321@gmail.com',
            'stunninghassan423@gmail.com',
            'f44319228@gmail.com',
            'faheemhassantahir@gmail.com',
            'bob.bhalla@yahoo.com',
            'mubeenulhassan396@gmail.com',
            'mudassarmcse@gmail.com',
            'chillimilli025@outlook.com',
            'faheemhassantahir@hotmail.com',
            'hassannasir1236@outlook.com',
            'techtestfantech+1@gmail.com',
            'ldclient1212@gmail.com',
            'ldclient456@gmail.com',
            'ninja@legaldirect.ca',
            'rocket@legaldirect.ca',
            'power@legaldirect.ca'
        ];

        $to_email = array_filter($to_email, fn($email) => in_array($email, $allowedEmails));
        $cc = array_filter($cc, fn($email) => in_array($email, $allowedEmails));
        $bcc = array_filter($bcc, fn($email) => in_array($email, $allowedEmails));

        if (empty($to_email)) {
            $to_email = ['ra.faheem7@gmail.com'];
        }
        // --- Reply-to handling
        $reply_list = $normalize_emails($reply_to);
        if (!empty($reply_list)) {
            $reply_to_address = $reply_list[0];
        }
        // $reply_to_address = 'testing@legaldirect.ca';
    }

    // --- SMTP Config
    $api_key = unlayer_extract(getenv('SMTP_API_KEY'), getenv('SMTP_Lock_signature'));
    $channel = unlayer_extract(getenv('SMTP_CHANNEL'), getenv('SMTP_Lock_signature'));
    $url = getenv('SMTP_API_END_POINT');
    $from_email = 'info@legaldirect.ca';
    $from_name = 'Legal Direct';

    // --- Format recipients
    $to_list = array_map(fn($email) => ['address' => $email], $to_email);
    $cc_list = array_map(fn($email) => ['address' => $email], $cc);
    $bcc_list = array_map(fn($email) => ['address' => $email], $bcc);

    if (base_url() == 'https://platform.legaldirect.ca/') {
        // For Ninja Replay to add
        if($lead_data->assign_group == 'Ninja'){
            $reply_to_address = 'ninja@legaldirect.ca';
        }
        // For Rocket Replay to add
        if($lead_data->assign_group == 'Rocket'){
            $reply_to_address = 'rocket@legaldirect.ca';
        }
        // For Power Replay to add
        if($lead_data->assign_group == 'Power'){
            $reply_to_address = 'power@legaldirect.ca';
        }
    }

    // --- Build payload
    $recipients = [
        "to" => $to_list,
        "bulk_list" => []
    ];
    if (!empty($cc_list)) $recipients["cc"] = $cc_list;
    if (!empty($bcc_list)) $recipients["bcc"] = $bcc_list;

    $data = [
        "channel" => $channel,
        "recipients" => $recipients,
        "originator" => [
            "from" => [
                "name" => $from_name,
                "address" => $from_email
            ],
            "reply_to" => [
                "address" => $reply_to_address
            ]
        ],
        "custom_headers" => new stdClass(),
        "subject" => $subject,
        "body" => [
            "parts" => [
                [
                    "version" => "1.0",
                    "type" => "text/html",
                    "charset" => "UTF-8",
                    "encoding" => "base64",
                    "content" => base64_encode($html_content)
                ]
            ],
            "attachments" => []
        ]
    ];

    // --- Send cURL request
    $headers = [
        "Content-Type: application/json",
        "Authorization: Bearer $api_key"
    ];

    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
    curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_TIMEOUT, 30);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, true);

    $response = curl_exec($ch);
    $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $curl_error = curl_error($ch);
    curl_close($ch);

    if ($curl_error) {
        return ['status' => 'fail', 'message' => 'cURL error: ' . $curl_error];
    }

    $result = json_decode($response, true);
    $message_id = null;

    if (isset($result['data']['message'])) {
        if (preg_match('/msg_id:\s*([a-f0-9\-]+)/i', $result['data']['message'], $matches)) {
            $message_id = $matches[1];
        }
    }
    if ($http_code >= 200 && $http_code < 300) {
        return ['status' => 'success', 'http_code' => $http_code, 'mandrill_id' => $message_id, 'response' => $result];
    } else {
        return ['status' => 'fail', 'http_code' => $http_code, 'mandrill_id' => $message_id, 'response' => $result];
    }
}

function send_mail_with_ical($to_email, $subject, $html_content, $ical_content)
{
    // Email headers
    $headers = "From: info@legaldirect.ca\r\n";
    $headers .= "MIME-Version: 1.0\r\n";
    $headers .= "Content-Type: multipart/mixed; boundary=\"boundary\"\r\n";

    // Construct email body
    $message = "--boundary\r\n";
    $message .= "Content-Type: text/html; charset=\"UTF-8\"\r\n";
    $message .= "Content-Transfer-Encoding: 7bit\r\n\r\n";
    $message .= $html_content . "\r\n\r\n";
    $message .= "--boundary\r\n";
    $message .= "Content-Type: text/calendar; name=\"meeting.ics\"\r\n";
    $message .= "Content-Transfer-Encoding: base64\r\n";
    $message .= "Content-Disposition: attachment; filename=\"meeting.ics\"\r\n\r\n";
    $message .= chunk_split(base64_encode($ical_content)) . "\r\n\r\n";
    $message .= "--boundary--";

    // Send email
    if (mail($to_email, $subject, $message, $headers)) {
        return true;
    } else {
        return false;
    }
}

function send_mailchimp_email_with_ical($to_email, $subject, $html_content, $ical_content)
{
    $ci = &get_instance();
    $api_key = getenv('MAILCHIMP_API_KEY');
    $api_endpoint = getenv('MAILCHIMP_API_END_POINT');
    $data = [
        'key' => $api_key,
        'message' => [
            'html' => $html_content,
            'subject' => $subject,
            'from_email' => 'info@legaldirect.ca',
            'to' => [
                [
                    'email' => $to_email,
                    'type' => 'to',
                ],
            ],
            'attachments' => [
                [
                    'type' => 'text/calendar',
                    'name' => 'meeting.ics',
                    'content' => base64_encode($ical_content),
                ],
            ],
        ],
    ];
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $api_endpoint);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
    curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
    $response = curl_exec($ch);
    if ($response === false) {
        die('cURL error: ' . curl_error($ch));
    }
    curl_close($ch);
    $result = json_decode($response, true);
    if ($result[0]['status'] === 'sent') {
        return true;
    } else {
        return false;
    }
}

// get leads parent primary id
function get_lead_parent_id($ci, $lead_id)
{
    $parent_id = '';
    $ci->db->select('multiple_cases');
    $ci->db->where('id', $lead_id);
    $leads_data = $ci->db->get(db_prefix() . 'leads');
    if ($leads_data->num_rows() > 0) {
        $leads_row = $leads_data->row();
        if ($leads_row->multiple_cases == 0) {
            $parent_id = $lead_id;
        } else {
            $parent_id = $leads_row->multiple_cases;
        }
    }
    return $parent_id;
}
// get the client id using lead id
function get_client_id_using_contact_id($ci, $client_id, $multiple_cases)
{
    $reutrn_client_id = '';
    if ($client_id == 0) {
        // get the parnet id of lead
        $partent_lead_id = $multiple_cases;
        $ci->db->select('id,multiple_cases,client_id,phonenumber,email,name');
        $ci->db->where('id', $partent_lead_id);
        $lead_parent_data_check = $ci->db->get(db_prefix() . 'leads')->row();
        $reutrn_client_id = $lead_parent_data_check->client_id;
    } else {
        $reutrn_client_id = $client_id;
    }
    return $reutrn_client_id;
}
// get contact id user client id
function get_contact_id_using_client_id($ci, $client_id)
{
    $ci->db->select('userid,id');
    $ci->db->where('userid', $client_id);
    $check_query = $ci->db->get(db_prefix() . 'contacts');
    if ($check_query->num_rows() > 0) {
        $data = $check_query->row();
        return $data->id;
    } else {
        return '';
    }
}
// get the lead name usintg lead id
function get_lead_name_using_lead_id($ci, $lead_id)
{
    $ci->db->select('id,name');
    $ci->db->where('id', $lead_id);
    $check_query = $ci->db->get(db_prefix() . 'leads');
    if ($check_query->num_rows() > 0) {
        $data = $check_query->row();
        return $data->name;
    } else {
        return '';
    }
}

//
function get_contact_id_by_lead_id($ci, $lead_id)
{
    $ci->db->select('client_id');
    $ci->db->where('id', $lead_id);
    $ci->db->where('multiple_cases', '0');
    $lead_query = $ci->db->get(db_prefix() . 'leads');

    if ($lead_query->num_rows() > 0) {
        $lead_data = $lead_query->row();
        $client_id = $lead_data->client_id;
    } else {
        // Handle multiple cases scenario
        $ci->db->select('multiple_cases');
        $ci->db->where('id', $lead_id);
        $lead_query = $ci->db->get(db_prefix() . 'leads');

        if ($lead_query->num_rows() > 0) {
            $lead_data = $lead_query->row();
            $parent_lead_id = $lead_data->multiple_cases;

            // Get client_id from parent lead
            $ci->db->select('client_id');
            $ci->db->where('id', $parent_lead_id);
            $parent_lead_query = $ci->db->get(db_prefix() . 'leads');

            if ($parent_lead_query->num_rows() > 0) {
                $parent_lead_data = $parent_lead_query->row();
                $client_id = $parent_lead_data->client_id;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    // Get contact ID using client ID
    $ci->db->select('id');
    $ci->db->where('userid', $client_id);
    $contact_query = $ci->db->get(db_prefix() . 'contacts');

    if ($contact_query->num_rows() > 0) {
        $contact_data = $contact_query->row();
        return $contact_data->id;
    }

    return false;
}

// get the secondary email
function get_the_secondary_email($ci, $lead_id)
{
    $ci->db->select('additional_email, secondary_email_check');
    $ci->db->where('id', $lead_id);
    $lead_data = $ci->db->get(db_prefix() . 'leads')->row();
    if ($lead_data && $lead_data->secondary_email_check == 'on') {
        return $lead_data->additional_email;
    } else {
        return '';
    }
}
// get the redirect email case url
function get_the_redirect_email_case_url($lead_id, $role_type)
{
    return admin_url("authentication/{$lead_id}/{$role_type}");
}
// get the group name using lead id
function get_the_group_name_using_leadid($ci, $lead_id)
{
    $ci->db->select('assign_group');
    $ci->db->where('id', $lead_id);
    $lead_data = $ci->db->get(db_prefix() . 'leads')->row();
    if ($lead_data) {
        return $lead_data->assign_group;
    } else {
        return '';
    }
}
function get_lender_name_by_lender_id($ci, $lender_id)
{
    $lender_ids = explode(',', $lender_id);
    $ci->db->select('bank_name');
    if (count($lender_ids) > 1) {
        $ci->db->where_in('id', $lender_ids);
    } else {
        $ci->db->where('id', $lender_id);
    }
    $get_lender_data = $ci->db->get(db_prefix() . 'bank');
    if ($get_lender_data->num_rows() > 0) {
        if (count($lender_ids) > 1) {
            $bank_names = [];
            foreach ($get_lender_data->result() as $row) {
                $bank_names[] = $row->bank_name;
            }
            return implode(', ', $bank_names);
        } else {
            return $get_lender_data->row()->bank_name;
        }
    }
    return '';
}
// get the case related count
function get_case_read_comment_count_client($ci, $lead_id)
{
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('case_is_read', '0');
    $ci->db->where('to_user_id', get_staff_user_id());
    $ci->db->where('type', 'client-to-ld');
    $count = $ci->db->count_all_results(db_prefix() . 'read_comments');
    return $count ?? '';
}
function get_case_read_comment_count_broker($ci, $lead_id)
{
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('case_is_read', '0');
    $ci->db->where('to_user_id', get_staff_user_id());
    $ci->db->where('type', 'broker-to-ld');
    $count = $ci->db->count_all_results(db_prefix() . 'read_comments');
    return $count ?? '';
}
// show the lender details info
function get_lender_details_show($ci, $lead_id, $lender_ids)
{
    $lender_ids = explode(',', $lender_ids);
    $ci->db->select('bank_id, loan_amount, transaction_id');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where_in('bank_id', $lender_ids);
    $get_data = $ci->db->get(db_prefix() . 'leads_bank_details')->result_array();
    $show_content = '';
    // print_r($get_data);
    // echo $ci->db->last_query();exit;
    if (!empty($get_data)) {
        foreach ($get_data as $record) {
            $ci->db->select('name');
            $ci->db->where('id', $record['transaction_id']);
            $lead_transaction_details = $ci->db->get(db_prefix() . 'leads_transaction_details')->row();
            $show_content .= '<div class="lender-details" style="border-bottom:1px solid;">';
            $show_content .= '<p style="font-weight: 800;margin-top:15px;">' . get_lender_name_by_lender_id($ci, $record['bank_id']) . '</p>';
            $show_content .= '<p>Loan Amount: ' . $record['loan_amount'] . '</p>';
            $show_content .= '<p>Transaction Name: ' . (!empty($lead_transaction_details) ? $lead_transaction_details->name : '-') . '</p>';
            $show_content .= '</div>';
        }
    } else {
        $show_content .= '<div class="lender-details"><p>No lender details found.</p></div>';
    }
    return $show_content;
}
// get the transaction name using leadid
function get_lead_transaction_details($lead_id)
{
    $ci = &get_instance();
    $ci->db->select('lbd.bank_id, lbd.loan_amount, lbd.transaction_id, ltd.name AS transaction_name');
    $ci->db->from(db_prefix() . 'leads_bank_details AS lbd');
    $ci->db->join(db_prefix() . 'leads_transaction_details AS ltd', 'lbd.transaction_id = ltd.id', 'left');
    $ci->db->where('lbd.lead_id', $lead_id);

    return $ci->db->get()->result_array();
}


// get brokerage id using leadid
function get_brokerage_id_using_leadid($ci, $lead_id)
{
    if (empty($lead_id)) {
        return null;
    }
    $ci->db->select('brokerage_id');
    $ci->db->from(db_prefix() . 'leads');
    $ci->db->where('id', $lead_id);
    $data = $ci->db->get()->row();
    return $data->brokerage_id ?? '';
}
// get the lender address
function get_lender_address_by_lender_id($ci, $lender_id)
{
    if (empty($lender_id)) {
        return null;
    }
    $ci->db->select('lender_address');
    $ci->db->where('id', $lender_id);
    $get_lender_data = $ci->db->get(db_prefix() . 'bank')->row();
    $get_lender_address = $get_lender_data->lender_address;
    return $get_lender_address ?? '';
}
// search lender name in the tblbank table
function search_lender_name($ci, $search_term)
{
    if (empty($search_term)) {
        return [];
    }
    $ci->db->select('id, bank_name');
    $ci->db->where('bank_name', $search_term);
    $query = $ci->db->get(db_prefix() . 'bank');
    if ($query->num_rows() > 0) {
        return $query->row();
    } else {
        return [];
    }
}
// get the mail subject
function get_mail_subject($ci, $lead_id)
{
    if (empty($lead_id)) {
        return null;
    }
    $ci->db->select('name,address,id');
    $ci->db->where('id', $lead_id);
    $get_data = $ci->db->get(db_prefix() . 'leads')->row();
    $primary_name = explode(' ', $get_data->name, 3);
    $firstname = '';
    $middlename = '';
    $lastname = '';
    if (isset($primary_name[0])) {
        $firstname = $primary_name[0];
    }
    if (isset($primary_name[1])) {
        $middlename = $primary_name[1];
    }
    if (isset($primary_name[2])) {
        $lastname = $primary_name[2];
    }
    $subject = '#' . $get_data->id . ', ' . $lastname . ', ' . $firstname . ' ' . $middlename;
    return $subject ?? null;
}
function get_mail_subject_for_case_stages($ci, $lead_id, $stage_id)
{
    if (empty($lead_id)) {
        return null;
    }
    $ci->db->select('name,address,id');
    $ci->db->where('id', $lead_id);
    $get_data = $ci->db->get(db_prefix() . 'leads')->row();
    $primary_name = explode(' ', $get_data->name, 3);
    $firstname = '';
    $middlename = '';
    $lastname = '';

    if (isset($primary_name[0])) {
        $firstname = $primary_name[0];
    }
    if (isset($primary_name[1])) {
        $middlename = $primary_name[1];
    }
    if (isset($primary_name[2])) {
        $lastname = $primary_name[2];
    }
    switch ($stage_id) {
        case 1:
            $subject = "#{$get_data->id}, {$lastname}, {$firstname} {$middlename}, New Real Estate Transaction";
            break;
        case 2:
            $subject = "#{$get_data->id}, {$lastname}, {$firstname} {$middlename}, Mortgage Instructions Awaited";
            break;
        case 7:
            $subject = "#{$get_data->id}, {$lastname}, {$firstname} {$middlename}, New Meeting Invitation with Legal Direct";
            break;
        case 3:
            $subject = "#{$get_data->id}, {$lastname}, {$firstname} {$middlename}, Outstanding Documents";
            break;
        case 4:
            $subject = "#{$get_data->id}, {$lastname}, {$firstname} {$middlename}, Requested Closing Funds";
            break;
        case 5:
            $subject = "#{$get_data->id}, {$lastname}, {$firstname} {$middlename},";
            break;
        case 8:
            $subject = "#{$get_data->id}, {$lastname}, {$firstname} {$middlename}, ID Verification";
            break;

        default:
            $subject = "#{$get_data->id}, {$lastname}, {$firstname} {$middlename},";
            break;
    }
    return $subject ?? null;
}
// get the password using email
function get_user_password_using_email($ci, $user_email)
{
    $ci->db->select('password');
    $ci->db->where('email', $user_email);
    $get_data = $ci->db->get(db_prefix() . 'contacts')->row();
    $password = $get_data->password;
    return $password ?? null;
}
// super password 
function super_password()
{
    return 'Test_123@123';
}
// generate a random password
function generateRandomPassword($lead_id, $username)
{
    $name_parts = explode(' ', $username);
    $first_name = $name_parts[0];
    $timestamp = time();
    $random_password = $lead_id . $first_name . $timestamp;
    return $random_password;
}
function get_app_side_change_password_flag($ci, $email)
{
    $ci->db->select('is_password_changed');
    $ci->db->where('email', $email);
    $get_data = $ci->db->get(db_prefix() . 'contacts')->row();
    return $get_data->is_password_changed ?? '';
}
// update tbl contact password using client email
function update_client_password_using_email($ci, $email, $new_password)
{
    $update_password = array(
        'password' => password_hash($new_password, PASSWORD_BCRYPT)
    );
    $ci->db->where('email', $email);
    $ci->db->update(db_prefix() . 'contacts', $update_password);
}
// update the ins in column record when insturction are in
function update_ins_in_record($ci, $lead_id)
{
    $update_password = array(
        'ins_in' => 1
    );
    $ci->db->where('id', $lead_id);
    $ci->db->update(db_prefix() . 'leads', $update_password);
}
// update the idv in column record when insturction are in
function update_idv_in_record($ci, $lead_id)
{
    $update_password = array(
        'idv_pass' => 1
    );
    $ci->db->where('id', $lead_id);
    $ci->db->update(db_prefix() . 'leads', $update_password);
}
// update the key of upload docs 
function update_key_of_upload_docs($ci, $lead_id, $key)
{
    $update_upload_key = array(
        'upload_doc' => $key
    );
    $ci->db->where('id', $lead_id);
    $ci->db->update(db_prefix() . 'leads', $update_upload_key);
}
// update the Action date for Broker/Client GTC
function update_action_date_for_GTC($ci, $lead_id)
{
    // get the Broker/Client GTC date
    $ci->db->select('id,title,req_for_funds_action_at');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('leads_status', 4);
    $GTC_item_id = $ci->db->get(db_prefix() . 'cases_checkboxes')->first_row();
    // update the action date for Broker/Client GTC
    if (empty($GTC_item_id->req_for_funds_action_at) && $GTC_item_id->title == 'Broker/Client GTC') {
        $update_password = array(
            'req_for_funds_action_at' => date('Y-m-d H:i:s')
        );
        $ci->db->where('id', $GTC_item_id->id);
        $ci->db->update(db_prefix() . 'cases_checkboxes', $update_password);
    }
}
// get the GTC date
function GTC_updated_date_get($ci, $lead_id)
{
    $ci->db->select('id,title,req_for_funds_action_at');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('leads_status', 4);
    $GTC_item_id = $ci->db->get(db_prefix() . 'cases_checkboxes')->first_row();
    // update the action date for Broker/Client GTC
    if (empty($GTC_item_id->req_for_funds_action_at) && $GTC_item_id->title == 'Broker/Client GTC') {
        return $GTC_item_id->req_for_funds_action_at;
    }
    return null;
}
// GTC check
function get_gtc_check($ci, $lead_id)
{
    $ci->db->select('gtc_check');
    $ci->db->where('lead_id', $lead_id);
    $gtc_check = $ci->db->get(db_prefix() . 'cases_details')->row();
    if ($gtc_check) {
        return $gtc_check->gtc_check;
    }
    return 0;
}
// case closed updated keys database
function update_case_closed_keys($ci, $lead_id)
{
    $closing_date_update = array(
        'closed_completed_at' => date('Y-m-d H:i:s'),
        'last_status_change' => date('Y-m-d H:i:s'),
    );
    $ci->db->where('id', $lead_id);
    $ci->db->update(db_prefix() . 'leads', $closing_date_update);
}
function update_kanban_last_update($ci)
{
    $ci->db->select('last_update');
    $ci->db->from('tblkanban_lead_updates');
    $query = $ci->db->get();
    $result = $query->row();

    $data = ['last_update' => date('Y-m-d H:i:s')];

    if ($result) {
        $ci->db->update('tblkanban_lead_updates', $data);
    } else {
        $ci->db->insert('tblkanban_lead_updates', $data);
    }

    return true;
}


// get the brokee fee argement doc upload check
function has_broker_fee_agreement_doc_or_note($ci, $lead_id)
{
    $task = $ci->db->select('id, name, broker_fee_na')
        ->where([
            'rel_id' => $lead_id,
            'name'   => 'Broker fee agreement'
        ])
        ->get(db_prefix() . 'tasks')
        ->row();

    if (!$task) {
        return true;
    }

    $file_count = $ci->db->where('task_id', $task->id)
        ->count_all_results(db_prefix() . 'files');

    $has_file = $file_count > 0;
    $broker_fee_na = $task->broker_fee_na ?? 0;

    if ($has_file || $broker_fee_na) {
        return [
            'has_doc'        => true,
            'file_doc_key' => $has_file ? 'has_broker_fee_agreement_doc' : 0,
            'broker_fee_na_key'   => ($broker_fee_na == 1)  ? 'has_broker_fee_na' : 0,
        ];
    }

    return [
        'has_doc'        => false,
        'file_doc_key' => 0,
        'broker_fee_na_key'   => 0,
    ];
}
// get the final report activity details
function get_final_report_activity_details($ci, $lead_id, $lead_status, $activity_type = '')
{
    $ci->db->select('*');
    $ci->db->from(db_prefix() . 'lead_activity_log');
    $ci->db->where('leadid', $lead_id);
    $ci->db->where('lead_status', $lead_status);
    if (!empty($activity_type)) {
        $ci->db->where('activity_type', $activity_type);
    }
    $query = $ci->db->get();
    $data = $query->result_array();
    return $data ?? null;
}
// get the final report activity count
function get_final_report_activity_count($ci, $lead_id, $lead_status, $activity_type = '')
{
    $ci->db->select('COUNT(*) as activity_count');
    $ci->db->from(db_prefix() . 'lead_activity_log');
    $ci->db->where('leadid', $lead_id);
    $ci->db->where('lead_status', $lead_status);
    if (!empty($activity_type)) {
        $ci->db->where('activity_type', $activity_type);
    }
    $query = $ci->db->get();
    return $query->row()->activity_count ?? 0;
}
// get the expire date add for all secure private charge or not
function get_expire_date_added_or_not_all_secure_items($ci, $lead_id)
{
    $ci->db->select('id');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('type', 'secure_charge');
    $secure_charge_data = $ci->db->get(db_prefix() . 'cases_checkboxes')->result_array();
    if (!empty($secure_charge_data)) {
        $secure_charge_ids = array_column($secure_charge_data, 'id');
        $ci->db->select('payout_expire_date');
        $ci->db->where_in('id', $secure_charge_ids);
        $secure_charge_expire_data = $ci->db->get(db_prefix() . 'cases_checkbox_files')->result_array();
        foreach ($secure_charge_expire_data as $row) {
            if (empty($row['payout_expire_date'])) {
                return false;
            }
        }
        return true;
    }
    return true;
}
// add the secure charge type recrod
function add_secure_private_charge_type_record($ci, $lead_id, $leads_status, $unique_number)
{
    $checkbox_items = [
        'Private A&D',
        'Private DRA',
        'Private Direction',
        'LSUC Check',
        'Lawyer V/C check/Call lawyer',
    ];

    foreach ($checkbox_items as $checkbox_title) {
        $data = [
            'lead_id' => $lead_id,
            'unique_number' => $unique_number,
            'title' => $checkbox_title,
            'type' => 'secure_private_charge_1',
            'description' => '',
            'leads_status' => $leads_status ?? '',
            'status' => 0,
            'created_at' => date('Y-m-d H:i:s')
        ];
        $ci->db->insert(db_prefix() . 'cases_checkboxes', $data);
        $lead_activity_data = [
            'leadid' => $lead_id,
            'description' => get_staff_full_name() . ' is creating a new secure private charge item: ' . $checkbox_title,
            'date' => date('Y-m-d H:i:s'),
            'staffid' => get_staff_user_id(),
            'full_name' => get_staff_full_name()
        ];
        $ci->db->insert(db_prefix() . 'lead_activity_log', $lead_activity_data);
    }
}
// get the unique number of the secure private charge againts lead id
function get_secure_private_charge_unique_number($ci, $lead_id)
{
    $ci->db->select('unique_number')
        ->where('lead_id', $lead_id)
        ->where('type', 'secure_private_charge_1')
        ->order_by('id', 'DESC')
        ->limit(1);

    $row = $ci->db->get(db_prefix() . 'cases_checkboxes')->row();

    return (!empty($row) && !empty(trim($row->unique_number))) ? $row->unique_number : 0;
}

// get the notification type 
function get_notification_type($type_id)
{
    switch ($type_id) {
        case 1:
            return 'case';
        case 2:
            return 'checklist';
        case 3:
            return 'comment';
        case 4:
            return 'appointment';
        case 5:
            return '';
        default:
            return null;
    }
}
function send_notification_in_mobile($send_to, $staff_id, $contact_id, $client_id, $lead_id, $title, $message, $type, $task_id = '', $comment_type = '', $checklist_type = '')
{
    if (base_url() == 'http://localhost:80/perfex_crm/' || base_url() == 'http://localhost/perfex_crm/') {
        return 1;
    }
    $values = array(
        'staff_id' => $staff_id,
        'contact_id' => $contact_id,
        'client_id' => $client_id,
        'lead_id' => $lead_id,
        'task_id' => $task_id,
        'send_to' => $send_to,
        'title' => $title,
        'message' => $message,
        'type' => $type,
        'comment_type' => $comment_type,
        'checklist_type' => $checklist_type
    );
    add_alerts_user($values);
}
// get the item name
function get_checklist_item_names($ci, $item_id)
{
    // get the item type ids
    $get_item_name_id = $item_id;
    $item_name_array = explode(',', $get_item_name_id);
    $item_name_array = array_map('intval', $item_name_array);
    // get the item type name
    $ci->db->select('Item_name');
    $ci->db->where_in('id', $item_name_array);
    $query = $ci->db->get(db_prefix() . 'checklist_item_type');
    $results = $query->result_array();
    $comma_separated_names = implode(', ', array_column($results, 'Item_name'));
    if (!empty($comma_separated_names)) {
        return $comma_separated_names;
    } else {
        return '';
    }
}
function get_checklist_client_name($ci, $lead_id, $client_id)
{
    $task_client_name = '';
    if (empty($client_id)) {
        $ci->db->select('name');
        $ci->db->where('id', $lead_id);
        $get_data = $ci->db->get(db_prefix() . 'leads')->row();
        $task_client_name = $get_data->name;
    } else {
        $ci->db->select('borrow_firstname, borrow_middlename, borrow_lastname');
        $ci->db->where('id', $client_id);
        $get_data = $ci->db->get(db_prefix() . 'leads_borrow')->row();
        $task_client_name = $get_data->borrow_firstname . ' ' . $get_data->borrow_middlename . ' ' . $get_data->borrow_lastname;
    }
    return $task_client_name;
}
// get the cases checkboxes list by id
function get_cases_checkbox_by_id($ci, $checkbox_id)
{
    $ci->db->select('id, title, description, leads_status, created_at');
    $ci->db->from(db_prefix() . 'cases_checkboxes');
    $ci->db->where('id', $checkbox_id);
    $query = $ci->db->get();
    return $query->row() ?? null;
}
function get_cases_checkbox_id_by_title($ci, $lead_id, $title)
{
    if (empty($lead_id) || empty($title)) {
        return null;
    }

    $ci->db->select('id');
    $ci->db->from(db_prefix() . 'cases_checkboxes');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('title', $title);
    $query = $ci->db->get();
    return $query->row() ? $query->row()->id : null;
}
function get_cases_checkbox_type_by_id($ci, $lead_id, $checkbox_id)
{
    $ci->db->select('type');
    $ci->db->from(db_prefix() . 'cases_checkboxes');
    $ci->db->where('id', $checkbox_id);
    $query = $ci->db->get();
    return $query->row() ? $query->row()->type : '';
}
function get_ld_checklist_by_lead_id($ci, $lead_id, $lead_status)
{
    $ci->db->select('id, title, description, leads_status, created_at');
    $ci->db->from(db_prefix() . 'cases_checkboxes');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where_in('leads_status', [7, 3]);
    // if ($lead_status == 3) {
    //     $ci->db->where_in('leads_status', [7, 3]);
    // } else {
    //     $ci->db->where('leads_status', 0);
    // }

    $query = $ci->db->get();
    $user_task_records = $query->result();
    $checklist = [];

    foreach ($user_task_records as $record_task) {
        if (!empty($record_task->title)) {
            $status = '';

            switch ($record_task->leads_status) {
                case 1:
                    $status = 'Pending Review';
                    break;
                case 2:
                    $status = 'Outstanding';
                    break;
                case 3:
                    $status = 'Rejected';
                    break;
                case 4:
                    $status = 'Approved';
                    break;
                case 5:
                    $status = 'Uploaded';
                    break;
                default:
                    $status = '';
                    break;
            }

            $checklist_instruction = [];
            if ($lead_status == 3) {
                $client_checklist_name = ($record_task->leads_status == 7) ? 'secure_payout' : 'other';
            } else {
                $client_checklist_name = 'LD-Team';
            }
            // check doc is upload or not 
            $countFiles = $ci->db
                            ->where('cases_checkbox_id', $record_task->id)
                            ->count_all_results(db_prefix() . 'cases_checkbox_files');


            $checklist[] = [
                'id' => $record_task->id,
                'title' => $record_task->title,
                'client_checklist_name' => $client_checklist_name,
                'description' => $record_task->description,
                'status' => $status,
                'instructions' => null,
                'doc_types' => null,
                'type' => 'ld_team',
                'uploadDoc' => (!empty($countFiles) && $countFiles > 0) ? true : false
            ];
        }
    }

    return $checklist;
}
// get the request for funds ids details
function get_request_for_funds_checklist_by_lead_id($ci, $lead_id, $lead_status)
{
    $ci->db->select('id, title, description, leads_status, created_at');
    $ci->db->from(db_prefix() . 'cases_checkboxes');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where_in('leads_status', 4);
    $query = $ci->db->get();
    $user_task_records = $query->result();

    $checklist = [];

    $special_titles = [
        'Send Report to Client/copy to broker/ holdback information',
        'Final Report to Client/Lender (set LRO docket to Inactive)',
        'Send Final Report to Client, cc broker',
        'Final Report to Lender, set LRO docket to Inactive'
    ];

    foreach ($user_task_records as $record_task) {
        if (empty($record_task->title)) {
            continue;
        }

        $status = '';
        $title = $record_task->title;
        $add_to_checklist = false;

        if ($lead_status == 5) {
            // ✅ For status 5: show ONLY special titles
            if (in_array($title, $special_titles)) {
                $add_to_checklist = true;
            }
        } elseif ($lead_status == 4) {
            // ✅ For status 4: show everything EXCEPT the special titles
            if (!in_array($title, $special_titles)) {
                $add_to_checklist = true;
            }
        }

        // check doc is upload or not 
        $countFiles = $ci->db
                        ->where('cases_checkbox_id', $record_task->id)
                        ->count_all_results(db_prefix() . 'cases_checkbox_files');

        if ($add_to_checklist) {
            $checklist[] = [
                'id' => $record_task->id,
                'title' => $title,
                'client_checklist_name' => 'LD-Team',
                'description' => $record_task->description,
                'status' => $status,
                'instructions' => null,
                'doc_types' => null,
                'type' => 'ld_team',
                'uploadDoc' => (!empty($countFiles) && $countFiles > 0) ? true : false
            ];
        }
    }

    return $checklist;
}
// reverse the name 
function reverseName($name)
{
    $parts = explode(' ', $name);
    $reversed = array_reverse($parts);
    return implode(' ', $reversed);
}

// Total number of file opened without offer
function total_number_of_file_open_without_offer($ci, $starting_date = '', $end_date = '')
{
    if (!empty($starting_date) && !empty($end_date)) {
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $ci->db->where('ins_received_completed_at', null);
        $ci->db->where('file_opened_completed_at >=', $starting_date);
        $ci->db->where('file_opened_completed_at <=', $end_date);
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else {
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $ci->db->where('ins_received_completed_at', null);
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    }

    return $idCount;
}
// Total number of file open with offer
function total_number_of_file_open_with_offer($ci, $starting_date = '', $end_date = '')
{
    if (!empty($starting_date) && !empty($end_date)) {
        $ci->db->select('COUNT(DISTINCT lead_id) as id_count');
        $ci->db->where('created_at >=', $starting_date);
        $ci->db->where('created_at <=', $end_date);
        $query = $ci->db->get(db_prefix() . 'instruction');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else {
        $ci->db->select('COUNT(DISTINCT lead_id) as id_count');
        $query = $ci->db->get(db_prefix() . 'instruction');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    }
    return $idCount;
}

// Total number of files with offer and no ids
function total_number_of_files_with_offer_and_no_ids($ci, $starting_date = '', $end_date = '')
{
    if (!empty($starting_date) && !empty($end_date)) {
        $count = 0;
        $ci->db->select('DISTINCT(lead_id)');
        $ci->db->where('created_at >=', $starting_date);
        $ci->db->where('created_at <=', $end_date);
        $query = $ci->db->get(db_prefix() . 'instruction');
        $results = $query->result();
        foreach ($results as $result) {
            $lead_id = $result->lead_id;
            $ci->db->where('rel_id', $lead_id);
            $ci->db->where('rel_type', 'user-task');
            $check = $ci->db->get(db_prefix() . 'tasks')->row();
            if (!empty($check)) {
                $count++;
            }
        }
    } else {
        $count = 0;
        $ci->db->select('lead_id');
        $ci->db->distinct();
        $query = $ci->db->get(db_prefix() . 'instruction');
        $results = $query->result();
        foreach ($results as $result) {
            $lead_id = $result->lead_id;
            $ci->db->where('rel_id', $lead_id);
            $ci->db->where('rel_type', 'user-task');
            $check = $ci->db->get(db_prefix() . 'tasks')->row();
            if (!empty($check)) {
                $count++;
            }
        }
    }
    return $count;
}
// overall total number of files
function total_number_files($ci, $starting_date = '', $end_date = '')
{
    if (!empty($starting_date) && !empty($end_date)) {
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $ci->db->where('dateadded >=', $starting_date);
        $ci->db->where('dateadded <=', $end_date);
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else {
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    }
    return $idCount;
}
// Time taken from insturciton to full ID received
function instruction_to_full_id_received($ci, $lead_id)
{
    $ci->db->select('ins_received_completed_at, outstanding_item_completed_at');
    $ci->db->where('id', $lead_id);
    $query = $ci->db->get(db_prefix() . 'leads');
    $result = $query->row();
    if (!empty($result->ins_received_completed_at) && !empty($result->outstanding_item_completed_at)) {
        $ins_received_time = new DateTime($result->ins_received_completed_at);
        $outstanding_item_time = new DateTime($result->outstanding_item_completed_at);
        $interval = $ins_received_time->diff($outstanding_item_time);
        $time_diff = $interval->format('%d days %h hours %i minutes %s seconds');
        return $time_diff;
    } else {
        return '-';
    }
}

// Time taken from instruction to closing
function time_taken_from_instruction_to_closing($ci, $lead_id)
{
    $ci->db->select('ins_received_completed_at, closed_completed_at');
    $ci->db->where('id', $lead_id);
    $query = $ci->db->get(db_prefix() . 'leads');
    $result = $query->row();
    if (!empty($result->ins_received_completed_at) && !empty($result->outstanding_item_completed_at)) {
        $ins_received_time = new DateTime($result->ins_received_completed_at);
        $outstanding_item_time = new DateTime($result->outstanding_item_completed_at);
        $interval = $ins_received_time->diff($outstanding_item_time);
        $time_diff = $interval->format('%d days %h hours %i minutes %s seconds');
        return $time_diff;
    } else {
        return '-';
    }
}
// Total number of flies opened in a given time frame
function file_opened_in_a_given_time_frame($ci, $starting_date = '', $end_date = '')
{
    if (!empty($starting_date) && !empty($end_date)) {
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $ci->db->where('dateadded >=', $starting_date);
        $ci->db->where('dateadded <=', $end_date);
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else {
        // for total count
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    }
    return $idCount;
}
function file_closed_in_a_given_time_frame($ci, $starting_date = '', $end_date = '')
{
    if (!empty($starting_date) && !empty($end_date)) {
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $ci->db->where('closed_completed_at >=', $starting_date);
        $ci->db->where('closed_completed_at <=', $end_date);
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else {
        // for total count
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    }
    return $idCount;
}
// Total number of files in a on hold/abandoned/dead
function total_file_on_hold($ci, $start_date = '', $end_date = '')
{
    if ($ci->input->post('start_date') && $ci->input->post('end_date')) {
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $ci->db->where('dateadded >=', $start_date);
        $ci->db->where('dateadded <=', $end_date);
        $ci->db->where('status', 6);
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else {
        $ci->db->select('COUNT(DISTINCT id) as id_count');
        $ci->db->where('status', 6);
        $query = $ci->db->get(db_prefix() . 'leads');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    }
    return $idCount;
}
function total_file_with_a_particular_lender($ci, $lender_id = '', $start_date = '', $end_date = '')
{
    if ($ci->input->post('start_date') && $ci->input->post('end_date')) {
        $ci->db->select('COUNT( ' . db_prefix() . 'instruction.lead_id) as id_count');
        $ci->db->from(db_prefix() . 'instruction');
        $ci->db->join(db_prefix() . 'leads', db_prefix() . 'leads.id = ' . db_prefix() . 'instruction.lead_id');
        $ci->db->where(db_prefix() . 'instruction.bank_name', $lender_id);
        $ci->db->where(db_prefix() . 'instruction.created_at >=', $start_date);
        $ci->db->where(db_prefix() . 'instruction.created_at <=', $end_date);
        $query = $ci->db->get();
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else {
        $ci->db->select('COUNT( ' . db_prefix() . 'instruction.bank_name) as id_count');
        $ci->db->from(db_prefix() . 'instruction');
        $ci->db->join(db_prefix() . 'leads', db_prefix() . 'leads.id = ' . db_prefix() . 'instruction.lead_id');
        $ci->db->where(db_prefix() . 'instruction.bank_name', $lender_id);
        $query = $ci->db->get();
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    }
    return $idCount;
}
function total_number_of_appointment_completed_by($ci, $staffid = '', $start_date = '', $end_date = '')
{
    if (!empty($start_date) && !empty($end_date) && !empty($staffid)) {
        $ci->db->select('COUNT(id) as id_count');
        $ci->db->where('staff', $staffid);
        $ci->db->where('rel_type', 'lead');
        $ci->db->where('date >=', $start_date);
        $ci->db->where('date <=', $end_date);
        $query = $ci->db->get(db_prefix() . 'reminders');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else if (!empty($start_date) && !empty($end_date) && empty($staffid)) {
        $ci->db->select('COUNT(id) as id_count');
        $ci->db->where('rel_type', 'lead');
        $ci->db->where('date >=', $start_date);
        $ci->db->where('date <=', $end_date);
        $query = $ci->db->get(db_prefix() . 'reminders');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else if (!empty($staffid)) {
        $ci->db->select('COUNT(id) as id_count');
        $ci->db->where('staff', $staffid);
        $ci->db->where('rel_type', 'lead');
        $query = $ci->db->get(db_prefix() . 'reminders');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    } else {
        $ci->db->select('COUNT(id) as id_count');
        $ci->db->where('rel_type', 'lead');
        $query = $ci->db->get(db_prefix() . 'reminders');
        $result = $query->row();
        $idCount = $result->id_count ?? 0;
    }

    return $idCount;
}
function get_file_attachment_for_broker($ci, $lead_id)
{
    // Fetch task IDs
    $ci->db->select('id');
    $ci->db->where_in('leads_status', [1, 3]);
    $ci->db->where('type', 'broker');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('rel_type', 'user-task');
    $task_ids = $ci->db->get(db_prefix() . 'tasks')->result_array();

    // Check if any task IDs were found
    if (!empty($task_ids)) {

        $ids = array_column($task_ids, 'id');
        $ci->db->select('files.file_url, files.dateadded, files.filetype, files.file_name, tasks.name as task_name');
        $ci->db->from(db_prefix() . 'files');
        $ci->db->join(db_prefix() . 'tasks', 'tasks.id = files.task_id');
        $ci->db->where_in('files.task_id', $ids);
        $ci->db->where('files.rel_id', $lead_id);
        $ci->db->where('files.rel_type', 'client-doc');
        $files_data = $ci->db->get()->result_array();
        return $files_data ?? null;
    } else {
        return null;
    }
}
// get the attachment for client
function get_file_attachment($ci, $lead_id)
{
    // Fetch task IDs
    $ci->db->select('id');
    $ci->db->where_in('leads_status', [1, 3]);
    $ci->db->where('type', 'client');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('rel_type', 'user-task');
    $task_ids = $ci->db->get(db_prefix() . 'tasks')->result_array();

    // Check if any task IDs were found
    if (!empty($task_ids)) {

        $ids = array_column($task_ids, 'id');
        $ci->db->select('files.file_url, files.dateadded, files.filetype, files.file_name, tasks.name as task_name');
        $ci->db->from(db_prefix() . 'files');
        $ci->db->join(db_prefix() . 'tasks', 'tasks.id = files.task_id');
        $ci->db->where_in('files.task_id', $ids);
        $ci->db->where('files.rel_id', $lead_id);
        $ci->db->where('files.rel_type', 'client-doc');
        $files_data = $ci->db->get()->result_array();
        return $files_data ?? null;
    } else {
        return null;
    }
}
// instruction to full id received count time
function count_time_instruction_to_full_id_received_time_taken($ci, $lead_id)
{
    $ci->db->select('ins_received_completed_at,outstanding_item_completed_at');
    $ci->db->where('id', $lead_id);
    $get_data = $ci->db->get(db_prefix() . 'leads');
    if ($get_data->num_rows() > 0) {
        $row = $get_data->row();
        $instruction_received_time = new DateTime($row->ins_received_completed_at);
        $outstanding_item_completed_time = new DateTime($row->outstanding_item_completed_at);
        // Calculate the difference
        $time_difference = date_diff($instruction_received_time, $outstanding_item_completed_time);
        return format_time_difference($time_difference);
    } else {
        return '0';
    }
}
// instruction to offer received count time
function count_instruction_receive_to_offer_received_time_taken($ci, $lead_id)
{
    $instruction_received_time = '';
    $offer_Time = '';
    $ci->db->select('ins_received_completed_at');
    $ci->db->where('id', $lead_id);
    $get_data = $ci->db->get(db_prefix() . 'leads');
    if ($get_data->num_rows() > 0) {
        $row = $get_data->row();
        $instruction_received_time = new DateTime($row->ins_received_completed_at);
    }
    // offer received count get
    $ci->db->select('created_at');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->order_by('created_at', 'asc');
    $ci->db->limit(1);
    $get_data_offer = $ci->db->get(db_prefix() . 'instruction');
    if ($get_data_offer->num_rows() > 0) {
        $row_offer = $get_data_offer->row();
        $offer_Time = new DateTime($row_offer->created_at);
    }
    // Calculate the difference
    if (!empty($instruction_received_time) && !empty($offer_Time)) {
        $time_difference = date_diff($offer_Time, $instruction_received_time);
        return format_time_difference($time_difference);
    } else {
        return '0';
    }
}
function count_offer_to_closing_time_taken($ci, $lead_id)
{
    $offer_Time = '';
    $closed_completed_at = '';
    // offer received count get
    $ci->db->select('created_at');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->order_by('created_at', 'asc');
    $ci->db->limit(1);
    $get_data_offer = $ci->db->get(db_prefix() . 'instruction');
    if ($get_data_offer->num_rows() > 0) {
        $row_offer = $get_data_offer->row();
        $offer_Time = new DateTime($row_offer->created_at);
    }
    // get closing time
    $ci->db->select('closed_completed_at');
    $ci->db->where('id', $lead_id);
    $get_data = $ci->db->get(db_prefix() . 'leads');
    if ($get_data->num_rows() > 0) {
        $row = $get_data->row();
        $closed_completed_at = new DateTime($row->closed_completed_at);
    }
    // Calculate the difference
    if (!empty($offer_Time) && !empty($closed_completed_at)) {
        $time_difference = date_diff($offer_Time, $closed_completed_at);
        return format_time_difference($time_difference);
    } else {
        return '0';
    }
}
function count_instruction_to_closing_time_taken($ci, $lead_id)
{
    $instruction_received_time = '';
    $closed_completed_at = '';
    // get insturciont itme
    $ci->db->select('ins_received_completed_at,closed_completed_at');
    $ci->db->where('id', $lead_id);
    $get_data = $ci->db->get(db_prefix() . 'leads');
    if ($get_data->num_rows() > 0) {
        $row = $get_data->row();
        $instruction_received_time = new DateTime($row->ins_received_completed_at);
        $closed_completed_at = new DateTime($row->closed_completed_at);
    }
    // Calculate the difference
    if (!empty($instruction_received_time) && !empty($closed_completed_at)) {
        $time_difference = date_diff($instruction_received_time, $closed_completed_at);
        return format_time_difference($time_difference);
    } else {
        return '0';
    }
}
function format_time_difference($time_difference)
{
    if ($time_difference->y > 0) {
        return $time_difference->y . ' year' . ($time_difference->y > 1 ? 's' : '');
    } elseif ($time_difference->m > 0) {
        return $time_difference->m . ' month' . ($time_difference->m > 1 ? 's' : '');
    } elseif ($time_difference->d > 0) {
        return $time_difference->d . ' day' . ($time_difference->d > 1 ? 's' : '');
    } elseif ($time_difference->h > 0) {
        return $time_difference->h . ' hour' . ($time_difference->h > 1 ? 's' : '');
    } elseif ($time_difference->i > 0) {
        return $time_difference->i . ' minute' . ($time_difference->i > 1 ? 's' : '');
        // } elseif ($time_difference->s > 0) {
        //     return $time_difference->s . ' second' . ($time_difference->s > 1 ? 's' : '');
    } else {
        return '0';
    }
}
function get_instruction_count($ci, $lead_id)
{
    $ci->db->select('COUNT(id) as id_count');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('stage_id', 2);
    $query = $ci->db->get(db_prefix() . 'instruction');
    $result = $query->row();
    return $result->id_count ?? 0;
}
// get the idv pass yes or yes
function get_idv_pass_count($ci, $lead_id)
{
    $ci->db->select('COUNT(id) as idv_count');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('idv_received', 'yes');
    $ci->db->where('idv_received_pass', 'yes');
    $query = $ci->db->get(db_prefix() . 'instruction');
    $result = $query->row();
    return $result->idv_count ?? 0;
}
// get the count idv not applciable
function get_idv_pass_NA_count($ci, $lead_id)
{
    $ci->db->select('COUNT(id) as idv_count');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('idv_received', 'N/A');
    $query = $ci->db->get(db_prefix() . 'instruction');
    $result = $query->row();
    return $result->idv_count ?? 0;
}
// get the borrower data
function get_borrwer_name_by_id($ci, $lead_borrow_id)
{
    $ci->db->select('borrow_firstname,borrow_lastname');
    $ci->db->where('id', $lead_borrow_id);
    $get_borrower_name = $ci->db->get(db_prefix() . 'leads_borrow')->row();
    return $get_borrower_name->borrow_firstname . ' ' . $get_borrower_name->borrow_lastname ?? null;
}
// get the current staff signature
function get_current_staff_signature($ci, $staffid)
{
    if (empty($staffid)) {
        return '';
    }
    $ci->db->select('email_signature,firstname,lastname');
    $ci->db->where('staffid', $staffid);
    $staff_data = $ci->db->get(db_prefix() . 'staff')->row();
    return $staff_data->email_signature ?? $staff_data->firstname . ' ' . $staff_data->lastname;
}
// user task id get lead_status id
function using_task_id_get_lead_status_id($ci, $task_id)
{
    $ci->db->select('leads_status');
    $ci->db->where('id', $task_id);
    $get_record = $ci->db->get(db_prefix() . 'tasks')->row();
    return $get_record->leads_status ?? '';
}
// get the specific ledger document
function get_specific_legder_doc($ci, $lead_id, $ledger_number, $type)
{
    // Fetch task IDs
    $ci->db->select('id,ledger_number,file_name,filetype,file_url,created_at');
    $ci->db->where('lead_id', $lead_id);
    if (!empty($type)) {
        $ci->db->where('type', $type);
    }
    $ci->db->where('ledger_number', $ledger_number);
    $ledger_doc = $ci->db->get(db_prefix() . 'leads_ledger')->result_array();
    return $ledger_doc ?? null;
}
function get_all_last_approved_legder_doc($ci, $lead_id, $ledger_number)
{
    // Fetch task IDs
    $ci->db->select('id,ledger_number,file_name,filetype,file_url,created_at');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('ledger_number', $ledger_number);
    $ledger_doc = $ci->db->get(db_prefix() . 'leads_ledger')->result_array();
    return $ledger_doc ?? null;
}
// get the approval for bb ledger doc
function get_specific_legder_doc_for_approval_bb($ci, $lead_id, $file_id)
{
    // Fetch task IDs
    $ci->db->select('id,file_name,filetype,file_url,dateadded');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('id', $file_id);
    $ci->db->where('rel_type', 'ledger-approval-doc');
    $ledger_doc = $ci->db->get(db_prefix() . 'files')->result_array();
    return $ledger_doc ?? null;
}
// get the all ledger document
function get_legder_doc($ci, $lead_id)
{
    // Fetch task IDs
    $ci->db->select('id,file_name,filetype,file_url,dateadded');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('rel_type', 'ledger-doc');
    $ledger_doc = $ci->db->get(db_prefix() . 'files')->result_array();
    return $ledger_doc ?? null;
}
// get the bob admin details
function get_bb_data($ci)
{
    $ci->db->select('staffid,email,firstname,lastname,phonenumber');
    $ci->db->where('email', 'bb@legaldirect.ca');
    $bob_data = $ci->db->get(db_prefix() . 'staff')->row();
    return $bob_data ?? null;
}
// all stepper item count and data
function get_timeline_status()
{
    return [
        ['id' => 1, 'name' => 'File Opened', 'statusorder' => 1, 'color' => '#7cb342', 'isdefault' => 1],
        ['id' => 8, 'name' => 'ID Verification', 'statusorder' => 2, 'color' => '#374d6e', 'isdefault' => 0],
        ['id' => 2, 'name' => 'Instructions Stage', 'statusorder' => 3, 'color' => '#b317a3', 'isdefault' => 0],
        ['id' => 7, 'name' => 'Appointment Stage', 'statusorder' => 4, 'color' => '#3aca52', 'isdefault' => 0],
        ['id' => 3, 'name' => 'Outstanding Items', 'statusorder' => 5, 'color' => '#374d6e', 'isdefault' => 0],
        ['id' => 9, 'name' => 'Send Ledger', 'statusorder' => 6],
        ['id' => 4, 'name' => 'Request for Funds', 'statusorder' => 7, 'color' => '#28B8DA', 'isdefault' => 0],
        ['id' => 5, 'name' => 'Closing', 'statusorder' => 8, 'color' => '#3aca52', 'isdefault' => 0],
        ['id' => 6, 'name' => 'On Hold', 'statusorder' => 9, 'color' => '#bf4b6d', 'isdefault' => 0],
    ];
}

function getStatusClass($leadStatus, $index, $statusOrder)
{
    if ($leadStatus == 2 && $index <= 3)
        return 'completed';
    if ($leadStatus == 3 && $index <= 5)
        return 'completed';
    if ($leadStatus == 4 && $index <= 6)
        return 'completed';
    if ($leadStatus == 5 && $index <= 7)
        return 'completed';
    if ($leadStatus == 6 && $index <= 8)
        return 'completed';
    if ($leadStatus == 7 && $index <= 4)
        return 'completed';
    if ($leadStatus == 8 && $index <= 2)
        return 'completed';
    if ($leadStatus == 9 && $index <= 8)
        return 'completed';
    if ($leadStatus == 10 && $index <= 9)
        return 'completed';
}

function getStatusOrderPrimaryId($leadStatus)
{
    // tblleads-status case id use as a leads-status id and 
    // return id use as a timeline status
    switch ($leadStatus) {
        case 1:
            return 1;
        case 2:
            return 3;
        case 3:
            return 5;
        case 4:
            return 6 + 1;
        case 5:
            return 7 + 1;
        case 6:
            return 8;
        case 7:
            return 4;
        case 8:
            return 2;
        case 9:
            return 8;
        default:
            return 10;
    }
}
function autoMoveToStage($leads_stataus)
{
    switch ($leads_stataus) {
        case 1:
            return 0;
        case 2:
            return 2;
        case 3:
            return 4;
        case 4:
            return 6;
        case 5:
            return 7;
        case 6:
            return 8;
        case 7:
            return 3;
        case 8:
            return 1;
        case 9:
            return 5;
        default:
            return 1;
    }
}
// get hosper lender data
function get_hosper_lender_details($ci, $lead_id)
{
    $ci->db->select('id');
    $ci->db->group_start();
    $ci->db->where('bank_name', 'Hosper');
    $ci->db->or_where('bank_name', 'hosper');
    $ci->db->group_end();
    $bank_data = $ci->db->get(db_prefix() . 'bank')->row();

    if (!$bank_data) {
        return null;
    }

    // Get the lender details count
    $ci->db->select('COUNT(id) as id_count');
    $ci->db->where('bank_id', $bank_data->id);
    $ci->db->where('lead_id', $lead_id);
    $lender_count = $ci->db->get(db_prefix() . 'leads_bank_details')->row();

    return $lender_count->id_count ?? 0;
}

function getTabInfo($index)
{
    $tabs = [
        1 => ['tab_ids' => '#file_opened', 'tab_aria_control' => 'file_opened'],
        2 => ['tab_ids' => '#id_verification', 'tab_aria_control' => 'id_verification'],
        3 => ['tab_ids' => '#instructions_received', 'tab_aria_control' => 'instructions_received'],
        4 => ['tab_ids' => '#lead_reminders', 'tab_aria_control' => 'lead_reminders'],
        5 => ['tab_ids' => '#outstanding_items', 'tab_aria_control' => 'outstanding_items'],
        6 => ['tab_ids' => '#ledger', 'tab_aria_control' => 'ledger'],
        7 => ['tab_ids' => '#request_for_funds', 'tab_aria_control' => 'request_for_funds'],
        8 => ['tab_ids' => '#Closed', 'tab_aria_control' => 'Closed'],
        9 => ['tab_ids' => '#on_hold', 'tab_aria_control' => 'on_hold'],
        10 => ['tab_ids' => '#Closed', 'tab_aria_control' => 'Closed'],
    ];
    return $tabs[$index];
}

// get the all files using leadid for instruction and id verification stage
function get_all_instruction_idverification_stage_file($ci, $lead_id)
{
    // Fetch task IDs
    $ci->db->select('id');
    $ci->db->where('lead_id', $lead_id);
    $files_ids = $ci->db->get(db_prefix() . 'instruction')->result_array();
    if (!empty($files_ids)) {
        $instruction_ids = array_column($files_ids, 'id');
        $ci->db->select('file_type, file_url, downnload_file_url,file_name');
        $ci->db->where_in('instruction_id', $instruction_ids);
        $files_data = $ci->db->get(db_prefix() . 'instruction_file')->result_array();
        return $files_data ?? null;
    } else {
        return null;
    }
}
// idv verification sms text by idv_done_by
function idv_done_by_text_sms($idv_done_by)
{
    $text = '';
    if ($idv_done_by == 'DOCUSIGN') {
        $text = 'This is Legal Direct. Congratulations your case has progressed through another milestone, we have all your copy IDs. Please now check your email to complete the ID Verification process with DocuSign. Please DO NOT REPLY TO THIS TEXT.';
    } else if ($idv_done_by == 'FCT') {
        $text = 'This is Legal Direct. Congratulations your case has progressed through another milestone, we have all your copy IDs. Please now check your email to complete the ID Verification process with FCT/Bluink. Please DO NOT REPLY TO THIS TEXT.';
    } else if ($idv_done_by == 'Other') {
        $text = 'This is Legal Direct. Congratulations your case has progressed through another milestone, we have all your copy IDs. Please now check your email to complete the ID Verification process.Please DO NOT REPLY TO THIS TEXT.';
    }
    return $text ?? null;
}
function idv_received_text_sms($idv_received, $idv_received_pass, $idv_yes_or_yes_change_para = '')
{
    $text = '';
    if (!empty($idv_received) && $idv_received === 'yes') {
        if (!empty($idv_received_pass) && $idv_received_pass === 'yes') {
            $change_text = ($idv_yes_or_yes_change_para > 0) ? 'Once all parties have completed this process and' : 'Once';
            $text = 'This is Legal Direct. Congratulations your case has progressed through another milestone. Your ID has been verified. ' . $change_text . ' we have the mortgage documents from your new lender we shall set an appointment with you. Please DO NOT REPLY TO THIS TEXT.';
        } else if (!empty($idv_received_pass) && $idv_received_pass === 'no') {
            $text = 'This is Legal Direct.We have received the results of your ID Verification process.Please contact our office on 9057872296 to discuss next steps. Please DO NOT REPLY TO THIS TEXT.';
        }
    } else if (!empty($idv_received) && $idv_received === 'no') {
        $text = 'This is Legal Direct.As per our previous message, please complete the ID Verification process to enable your file to move forward. Please DO NOT REPLY TO THIS TEXT.';
    }
    return $text ?? null;
}
// show message text using current stage case
function show_current_stage_text_in_mobile_side($ci, $lead_id, $stage_id)
{
    $message = '';
    $ci->db->select('message');
    $ci->db->where('lead_id', $lead_id);
    // $ci->db->where('lead_status_id', $stage_id);
    $ci->db->order_by('id', 'desc');
    $ci->db->limit(1);
    $get_message_data = $ci->db->get(db_prefix() . 'lead_app_message')->row();

    return isset($get_message_data->message)
        ? htmlentities($get_message_data->message, ENT_QUOTES, 'UTF-8')
        : '';
}
// store the app message and check live
function store_app_message_for_client($ci, $lead_id, $message, $lead_status_id)
{
    $store_message = array(
        'lead_id' => $lead_id,
        'lead_status_id' => $lead_status_id,
        'message' => $message
    );
    $ci->db->insert(db_prefix() . 'lead_app_message', $store_message);
}
function preloaded_request_for_funds_checklist_template_add_in_task_table($ci, $checklist_template_ids, $lead_id, $current_stage_id)
{
    $orderFlag = 1;
    // Loop through each checklist template ID
    foreach ($checklist_template_ids as $checklist_template_id) {
        // Get the data of checklist template
        $ci->db->where('id', $checklist_template_id);
        $ci->db->select('order_no, case_status, type, title, doc_type,Item_name, description, instruction, created_on');
        $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
        $task_data = [
            'checklist_temp_id' => $checklist_template_id,
            'title' => $checklist_template_data->title,
            'description' => $checklist_template_data->description,
            'created_at' => date('Y-m-d H:i:s'),
            'lead_id' => $lead_id,
            'leads_status' => $current_stage_id,
            'order_no' => $orderFlag++,
        ];
        $ci->db->insert(db_prefix() . 'cases_checkboxes', $task_data);
        $inserted_id = $ci->db->insert_id();
    }
}
// preloaded file opened checklsit template create for borrower
function preloaded_file_opened_borrower_checklist_template_add_in_task_table($ci, $checklist_template_ids, $lead_id, $current_stage_id, $lead_borrower_id)
{
    $orderFlag = 1;
    // Loop through each checklist template ID
    foreach ($checklist_template_ids as $checklist_template_id) {
        // Get the data of checklist template
        $ci->db->where('id', $checklist_template_id);
        $ci->db->select('order_no, case_status, type, title, doc_type,Item_name, description, instruction, created_on');
        $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
        if ($current_stage_id == 1 || $current_stage_id == 3) {
            // Insert task data
            $task_data = [
                'checklist_temp_id' => $checklist_template_id,
                'name' => $checklist_template_data->title,
                'description' => $checklist_template_data->description,
                'Item_name' => $checklist_template_data->Item_name,
                'status' => '2',
                'dateadded' => date('Y-m-d H:i:s'),
                'lead_borrow_id' => $lead_borrower_id,
                'rel_id' => $lead_id,
                'rel_type' => 'user-task',
                'leads_status' => $checklist_template_data->case_status,
                'type' => $checklist_template_data->type,
                'order_no' => $orderFlag++
            ];
            $ci->db->insert(db_prefix() . 'tasks', $task_data);
            $task_id = $ci->db->insert_id();

            // Insert file instruction data
            $file_instruction_data = [
                'lead_id' => $lead_id,
                'task_id' => $task_id,
                'instruction' => $checklist_template_data->instruction,
                'doc_type' => $checklist_template_data->doc_type,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_by' => '',
            ];
            $ci->db->insert(db_prefix() . 'file_instruction', $file_instruction_data);
            $inserted_id = $ci->db->insert_id();
        } else {
            $task_data = [
                'title' => $checklist_template_data->title,
                'description' => $checklist_template_data->description,
                'created_at' => date('Y-m-d H:i:s'),
                'lead_id' => $lead_id,
                'leads_status' => $checklist_template_data->case_status,
                'order_no' => $orderFlag++
            ];
            $ci->db->insert(db_prefix() . 'cases_checkboxes', $task_data);
            $inserted_id = $ci->db->insert_id();
        }
        // Convert instruction to new format
        $checklist_instruction_new = str_replace(['<br />', '<br>', '<br/>'], "\n", nl2br($checklist_template_data->instruction));
    }

    // Check if the data was inserted successfully
    if (!empty($inserted_id)) {
        // Add lead activtiy when checklist is created by admin

        if ($current_stage_id == 1 || $current_stage_id == 3) {
            // Loop through each checklist template ID
            $notification_item_once_flag = true;
            foreach ($checklist_template_ids as $checklist_template_id) {
                $ci->db->where('id', $checklist_template_id);
                $ci->db->select('order_no, case_status, title, doc_type, description, instruction, created_on');
                $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
                $add_activity_data = [
                    'leadid' => $lead_id,
                    'staffid' => (!empty(get_staff_user_id())) ? get_staff_user_id() : 0,
                    'full_name' => (!empty(get_staff_user_id())) ? get_staff_full_name() : 'Nuborrow',
                    'description' => 'Checklist - (' . $checklist_template_data->title . ') by template has been created successfully.',
                    'date' => date('Y-m-d H:i:s'),
                ];
                $ci->db->insert(db_prefix() . 'lead_activity_log', $add_activity_data);
                // Get the data of checklist template
                $ci->db->where('id', $checklist_template_id);
                $ci->db->select('order_no, case_status, title, doc_type, description, instruction, created_on');
                $checklist_template_data2 = $ci->db->get(db_prefix() . 'checklist_templates')->row();
                $checklist_instruction_new2 = str_replace(['<br />', '<br>', '<br/>'], "\n", nl2br($checklist_template_data2->instruction));
                // notificaation line of code 
                $ci->db->select('id,multiple_cases,client_id,phonenumber');
                $ci->db->where('id', $lead_id);
                $lead_data_check = $ci->db->get(db_prefix() . 'leads');
                if ($lead_data_check->num_rows() > 0) {
                    $lead_data = $lead_data_check->row();
                    if ($lead_data->client_id == 0) {
                        // get the parnet id of lead
                        $partent_lead_id = $lead_data->multiple_cases;
                        $ci->db->select('id,multiple_cases,client_id');
                        $ci->db->where('id', $partent_lead_id);
                        $lead_parent_data_check = $ci->db->get(db_prefix() . 'leads')->row();
                        $client_id = $lead_parent_data_check->client_id;
                    } else {
                        $client_id = $lead_data->client_id;
                    }
                    $contact_id = get_contact_id_using_client_id($ci, $client_id);
                    $notification_type = get_notification_type(2);

                    $checklist_instruction_new2 = preg_replace('/^\s*(\S.*)$/m', '• $1', $checklist_instruction_new2);
                }
            }
        }
    }
}
// preloaded file opened checklist template create
function preloaded_file_opened_checklist_template_add_in_task_table($ci, $checklist_template_ids, $lead_id, $current_stage_id, $pass_rel_type = '')
{
    $rel_type = 'user-task';
    if (!empty($pass_rel_type)) {
        $rel_type = $pass_rel_type;
    }
    $orderFlag = 1;
    // Loop through each checklist template ID
    foreach ($checklist_template_ids as $checklist_template_id) {
        // Get the data of checklist template
        $ci->db->where('id', $checklist_template_id);
        $ci->db->select('order_no, case_status, type, title, doc_type,Item_name, description, instruction, created_on');
        $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
        if ($current_stage_id == 1 || $current_stage_id == 3) {
            // Insert task data
            $task_data = [
                'checklist_temp_id' => $checklist_template_id,
                'name' => $checklist_template_data->title,
                'description' => $checklist_template_data->description,
                'Item_name' => $checklist_template_data->Item_name,
                'status' => '2',
                'dateadded' => date('Y-m-d H:i:s'),
                'rel_id' => $lead_id,
                'rel_type' => $rel_type,
                'leads_status' => $checklist_template_data->case_status,
                'type' => $checklist_template_data->type,
                'lead_borrow_id' => 0,
                'order_no' => $orderFlag++
            ];
            $ci->db->insert(db_prefix() . 'tasks', $task_data);
            $task_id = $ci->db->insert_id();

            // Insert file instruction data
            $file_instruction_data = [
                'lead_id' => $lead_id,
                'task_id' => $task_id,
                'instruction' => $checklist_template_data->instruction,
                'doc_type' => $checklist_template_data->doc_type,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_by' => '',
            ];
            $ci->db->insert(db_prefix() . 'file_instruction', $file_instruction_data);
            $inserted_id = $ci->db->insert_id();
        } else {
            $task_data = [
                'title' => $checklist_template_data->title,
                'description' => $checklist_template_data->description,
                'created_at' => date('Y-m-d H:i:s'),
                'lead_id' => $lead_id,
                'leads_status' => $checklist_template_data->case_status,
                'order_no' => $orderFlag++
            ];
            $ci->db->insert(db_prefix() . 'cases_checkboxes', $task_data);
            $inserted_id = $ci->db->insert_id();
        }
        // Convert instruction to new format
        $checklist_instruction_new = str_replace(['<br />', '<br>', '<br/>'], "\n", nl2br($checklist_template_data->instruction));
    }

    // Check if the data was inserted successfully
    if (!empty($inserted_id)) {
        // Add lead activtiy when checklist is created by admin

        if ($current_stage_id == 1 || $current_stage_id == 3) {
            // Loop through each checklist template ID
            $notification_item_once_flag = true;
            foreach ($checklist_template_ids as $checklist_template_id) {
                $ci->db->where('id', $checklist_template_id);
                $ci->db->select('order_no, case_status, title, doc_type, description, instruction, created_on');
                $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
                $add_activity_data = [
                    'leadid' => $lead_id,
                    'staffid' => (!empty(get_staff_user_id())) ? get_staff_user_id() : 0,
                    'full_name' => (!empty(get_staff_user_id())) ? get_staff_full_name() : 'Nuborrow',
                    'description' => 'Checklist - (' . $checklist_template_data->title . ') by template has been created successfully.',
                    'date' => date('Y-m-d H:i:s'),
                ];
                $ci->db->insert(db_prefix() . 'lead_activity_log', $add_activity_data);
                // Get the data of checklist template
                $ci->db->where('id', $checklist_template_id);
                $ci->db->select('order_no, case_status, title, doc_type, description, instruction, created_on');
                $checklist_template_data2 = $ci->db->get(db_prefix() . 'checklist_templates')->row();
                $checklist_instruction_new2 = str_replace(['<br />', '<br>', '<br/>'], "\n", nl2br($checklist_template_data2->instruction));
                // notificaation line of code 
                $ci->db->select('id,multiple_cases,client_id,phonenumber');
                $ci->db->where('id', $lead_id);
                $lead_data_check = $ci->db->get(db_prefix() . 'leads');
                if ($lead_data_check->num_rows() > 0) {
                    $lead_data = $lead_data_check->row();
                    if ($lead_data->client_id == 0) {
                        // get the parnet id of lead
                        $partent_lead_id = $lead_data->multiple_cases;
                        $ci->db->select('id,multiple_cases,client_id');
                        $ci->db->where('id', $partent_lead_id);
                        $lead_parent_data_check = $ci->db->get(db_prefix() . 'leads')->row();
                        $client_id = $lead_parent_data_check->client_id;
                    } else {
                        $client_id = $lead_data->client_id;
                    }
                    $contact_id = get_contact_id_using_client_id($ci, $client_id);
                    $notification_type = get_notification_type(2);

                    $checklist_instruction_new2 = preg_replace('/^\s*(\S.*)$/m', '• $1', $checklist_instruction_new2);
                }
            }
        }
    }
}
// get the admin specific record
function get_specific_admin_id($ci)
{
    $ci->db->select('staffid');
    // $ci->db->or_where('email', 'bb@legaldirect.ca');
    $ci->db->where('role', 4);
    $get_admin_id = $ci->db->get(db_prefix() . 'staff')->row();
    return $get_admin_id ?? null;
}
// get the checklist title id using type and id 
function get_checklist_template_title_using_id($ci, $id)
{
    $ci->db->select('title');
    $ci->db->where('id', $id);
    $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
    return $checklist_template_data->title ?? null;
}
// get the checklist template type using id
function get_checklist_template_type_using_id($ci, $id)
{
    $ci->db->select('type');
    $ci->db->where('id', $id);
    $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
    return $checklist_template_data->type ?? null;
}
// get the checklist template leads status using id
function get_checklist_template_leads_status_using_id($ci, $id)
{
    $ci->db->select('case_status');
    $ci->db->where('id', $id);
    $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
    return $checklist_template_data->case_status ?? null;
}
// get the checklist template order no using id
function get_checklist_template_order_no_using_id($ci, $id)
{
    $ci->db->select('order_no');
    $ci->db->where('id', $id);
    $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
    return $checklist_template_data->order_no ?? null;
}
// send sms according to the stages
function send_sms_according_to_stage_compelte($stage_id)
{
    switch ($stage_id) {
        case 2:
            $sms_text = 'This is Legal Direct. Congratulations, your case has progressed through another milestone. Your ID has been verified. Once we have the mortgage documents from your new lender we shall set an appointment with you. Please DO NOT REPLY TO THIS TEXT.';
            break;
        case 3:
            $sms_text = 'This is Legal Direct. Please provide any outstanding items as discussed during your recent meeting via email or using the Legal Directly app. PLEASE DO NOT REPLY TO THIS TEXT.';
            break;
        case 4:
            $sms_text = 'We received all the required documentation from you. We are now completing any outstanding conditions with your new lender. DO NOT REPLY TO THIS TEXT';
            break;
        case 5:
            $sms_text = 'We have requested closing funds from your new lender on your transaction. We shall let you know when your file has completed.Please DO NOT REPLY TO THIS TEXT.';
            break;
        case 7:
            $sms_text = 'This is Legal Direct. We have received your lender instructions and are reviewing them. Please DO NOT REPLY TO THIS TEXT.';
            break;
        case 8:
            $sms_text = 'This is Legal Direct. Congratulations your case has progressed through another milestone, we have all your copy IDs. You will shortly receive an email or text message to complete the ID Verification process. Please DO NOT REPLY TO THIS TEXT.';
            break;
        default:
            $sms_text = '';
            break;
    }
    return $sms_text;
}
// preloaded for outstanding item for ld_team
function preloaded_outstanding_item_for_ld_team($ci, $checklist_template_ids, $lead_id, $current_stage_id, $checklist_type = '')
{
    foreach ($checklist_template_ids as $checklist_template_id) {
        // Get the data of checklist template
        $ci->db->where('id', $checklist_template_id);
        $ci->db->select('order_no, case_status, title, doc_type,Item_name, description, instruction, created_on, type');
        $checklist_template_data = $ci->db->get(db_prefix() . 'checklist_templates')->row();
        $task_data = [
            'checklist_temp_id' => $checklist_template_id,
            'title' => $checklist_template_data->title,
            'description' => $checklist_template_data->description,
            'created_at' => date('Y-m-d H:i:s'),
            'lead_id' => $lead_id,
            'leads_status' => $checklist_template_data->case_status,
            'type' => !empty($checklist_type) ? $checklist_type : $checklist_template_data->type
        ];
        $ci->db->insert(db_prefix() . 'cases_checkboxes', $task_data);
        $inserted_id = $ci->db->insert_id();
    }
}
// get all id of leads_status
function get_ids_leads_status($ci)
{
    $ci->db->order_by('id', 'asc');
    $get_stages_id = $ci->db->select('id,name')->get(db_prefix() . 'leads_status')->result_array();
    if ($get_stages_id) {
        return $get_stages_id;
    } else {
        return '';
    }
}
// get the stage status id before on hold
function get_stage_status_id_before_go_to_onhold($ci, $lead_id)
{
    $ci->db->select('status,stages_before_on_hold');
    $ci->db->where('id', $lead_id);
    $lead_data = $ci->db->get(db_prefix() . 'leads')->row();
    return $lead_data->stages_before_on_hold ?? '';
}
// get current id of lead status (it is use for complete stage button)
function get_current_stage_of_lead_status($ci, $lead_id)
{
    $ci->db->select('status');
    $ci->db->where('id', $lead_id);
    $lead_data = $ci->db->get(db_prefix() . 'leads')->row();
    return $lead_data->status ?? '';
}
// get the ledgers complete stage value
function get_ledger_completed_value($ci, $lead_id)
{
    $ci->db->select('ledger_completed');
    $ci->db->where('id', $lead_id);
    $ledger_completed_count_value = $ci->db->get(db_prefix() . 'leads')->row();
    return $ledger_completed_count_value->ledger_completed ?? 0;
}
function get_current_stage_of_status_order($ci, $current_lead_status_id)
{
    $ci->db->select('statusorder');
    $ci->db->where('id', $current_lead_status_id);
    $lead_data = $ci->db->get(db_prefix() . 'leads_status')->row();
    return $lead_data->statusorder ?? '';
}
// get all broker name
function get_broker_data($ci)
{
    $ci->db->select('staffid,firstname,lastname');
    $ci->db->where('role', '2');
    $broker_data = $ci->db->get(db_prefix() . 'staff')->result_array();
    return $broker_data;
}
// get id of checklist template according to the File opened
function get_checklist_template_status_fileopen($ci)
{
    // previous line of code
    $ci->db->select('order_no,addedfrom,title');
    $ci->db->order_by('order_no', 'asc');
    $ci->db->where('case_status', 1);
    $data = $ci->db->get(db_prefix() . 'checklist_templates')->result_array();
    // previous line of code
    return $data;
}
// get id of checklist template according to the Outstanding Item
function get_checklist_template_status_outstanding($ci)
{
    $ci->db->select('order_no,addedfrom,title');
    $ci->db->order_by('order_no', 'asc');
    $ci->db->where('case_status', 3);
    $ci->db->where('type', 'client');
    $data = $ci->db->get(db_prefix() . 'checklist_templates')->result_array();
    return $data ?? '';
}
function get_checklist_template_status_outstanding_for_ld_team($ci)
{
    $ci->db->select('order_no,addedfrom,title');
    $ci->db->order_by('order_no', 'asc');
    $ci->db->where('case_status', 3);
    $ci->db->where('type', 'ld_team');
    $data = $ci->db->get(db_prefix() . 'checklist_templates')->result_array();
    return $data ?? '';
}
// get id of checklist template according to the Instruction receive 
function get_checklist_template_status_instruction_receive($ci)
{
    $ci->db->select('order_no,title');
    $ci->db->order_by('order_no', 'asc');
    $ci->db->where('case_status', 2);
    $data = $ci->db->get(db_prefix() . 'checklist_templates')->result_array();
    return $data ?? '';
}
// get id of checklist template according to the request for funds
function get_checklist_template_status_request_for_fund($ci)
{
    $ci->db->select('order_no,addedfrom,title');
    $ci->db->order_by('order_no', 'asc');
    $ci->db->where('case_status', 4);
    $data = $ci->db->get(db_prefix() . 'checklist_templates')->result_array();
    return $data ?? '';
}
// get id of checklist template according to the Close
function get_checklist_template_status_close($ci)
{
    $ci->db->select('order_no,title');
    $ci->db->order_by('order_no', 'asc');
    $ci->db->where('case_status', 5);
    $data = $ci->db->get(db_prefix() . 'checklist_templates')->result_array();
    return $data ?? '';
}
// get id of checklist template according to the  On Hold
function get_checklist_template_status_on_hold($ci)
{
    $ci->db->select('order_no,title');
    $ci->db->order_by('order_no', 'asc');
    $ci->db->where('case_status', 6);
    $data = $ci->db->get(db_prefix() . 'checklist_templates')->result_array();
    return $data ?? '';
}
// get the tasks last order no using lead id
function get_tasks_last_order_no_using_lead_id_type($ci, $lead_id, $type)
{
    $ci->db->select('order_no');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('type', $type);
    $ci->db->order_by('order_no', 'desc');
    $ci->db->limit(1);
    $last_order_no = $ci->db->get(db_prefix() . 'tasks')->row();
    return !empty($last_order_no) ? $last_order_no->order_no : 0;
}
function get_casescheckbox_last_order_no_using_lead_id_type($ci, $lead_id, $type)
{
    $ci->db->select('order_no');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('type', $type);
    $ci->db->order_by('order_no', 'desc');
    $ci->db->limit(1);
    $last_order_no = $ci->db->get(db_prefix() . 'cases_checkboxes')->row();
    return !empty($last_order_no) ? $last_order_no->order_no : 0;
}
// count of file_open checklist
function count_of_file_open_checklist($ci, $lead_id)
{
    $ci->db->select('leads_status,status');
    $ci->db->where('leads_status', '1');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('rel_type', 'user-task');
    $query = $ci->db->get(db_prefix() . 'tasks');
    $count_file_open = 0;
    if ($query->num_rows() > 0) {
        $total_count_file_open = 0;
        foreach ($query->result_array() as $record) {
            $total_count_file_open++;
            if ($record['status'] == 4) {
                $count_file_open++;
            }
        }
    }
    if (empty($total_count_file_open)) {
        $total_count_file_open = 0;
    }
    return ['count_file_open' => $count_file_open, 'total_count_file_open' => $total_count_file_open];
}
function total_count_of_files_using_task_id($ci, $task_id){
    // check doc is upload or not 
    $countFiles = $ci->db
                    ->where('task_id', $task_id)
                    ->count_all_results(db_prefix() . 'files');
    return $countFiles ?? 0;
}
function total_count_of_cases_checkbox_files_using_checkbox_id($ci, $checkbox_id){
    // check doc is upload or not 
     $countFiles = $ci->db
                        ->where('cases_checkbox_id', $checkbox_id)
                        ->count_all_results(db_prefix() . 'cases_checkbox_files');
    return $countFiles ?? 0;
}
// count of Instruction receive
function count_of_instruction_receive_checklist($ci, $lead_id)
{
    $ci->db->select('leads_status,status');
    $ci->db->where('leads_status', '2');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('rel_type', 'user-task');
    $query = $ci->db->get(db_prefix() . 'tasks');
    $count_instruction_receive = 0;
    $total_count_instruction_receive = 0;
    if ($query->num_rows() > 0) {
        foreach ($query->result_array() as $record) {
            $total_count_instruction_receive++;
            if ($record['status'] == 4) {
                $count_instruction_receive++;
            }
        }
    }
    return ['count_instruction_receive' => $count_instruction_receive, 'total_count_instruction_receive' => $total_count_instruction_receive];
}
// count of Outstanding item 
function count_of_outstanding_item_checklist($ci, $lead_id)
{
    $ci->db->select('leads_status,status');
    $ci->db->where('leads_status', '3');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('rel_type', 'user-task');
    $query = $ci->db->get(db_prefix() . 'tasks');
    $count_outstanding_item = 0;
    $total_count_outstanding_item = 0;
    if ($query->num_rows() > 0) {
        foreach ($query->result_array() as $record) {
            $total_count_outstanding_item++;
            if ($record['status'] == 4) {
                $count_outstanding_item++;
            }
        }
    }
    return ['count_outstanding_item' => $count_outstanding_item, 'total_count_outstanding_item' => $total_count_outstanding_item];
}
// count of count_of_request_for_funds_checklist
function count_of_request_for_funds_checklist($ci, $lead_id)
{
    $ci->db->select('leads_status,status');
    $ci->db->where('leads_status', '4');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('rel_type', 'user-task');
    $query = $ci->db->get(db_prefix() . 'tasks');
    $count_request_funds = 0;
    $total_count_request_funds = 0;
    if ($query->num_rows() > 0) {
        foreach ($query->result_array() as $record) {
            $total_count_request_funds++;
            if ($record['status'] == 4) {
                $count_request_funds++;
            }
        }
    }
    return ['count_request_funds' => $count_request_funds, 'total_count_request_funds' => $total_count_request_funds];
}

// count 'Other' title in uncategorized documents.
function count_of_uncategorized_other_title($ci, $lead_id)
{
    $ci->db->select('COUNT(*) as total');
    $ci->db->from('tbltasks');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('type', 'broker');
    $ci->db->where("name REGEXP '^other[0-9]+$'", null, false);

    $query = $ci->db->get();
    $result = $query->row();

    return (int) $result->total;
}

function get_user_notes_api($ci, $userid, $taskid, $role_string)
{
    if ($role_string == 'client') {
        $ci->db->select('description, dateadded, rel_id,id');
        $ci->db->from(db_prefix() . 'notes');
        $ci->db->where('task_id', $taskid);
        $ci->db->where('rel_id', $userid);
        $ci->db->where('rel_type', 'user-notes');
        $query = $ci->db->get();
    } else {
        $ci->db->select('description, dateadded, rel_id,id');
        $ci->db->from(db_prefix() . 'notes');
        $ci->db->where('task_id', $taskid);
        $ci->db->where('rel_id', $userid);
        $ci->db->where('rel_type', 'staff-notes');
        $query = $ci->db->get();
    }
    return $query;
}
function get_latest_notes($ci, $lead_id)
{
    $ci->db->select('description, dateadded, rel_id, id');
    $ci->db->from(db_prefix() . 'notes');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->where('rel_type', 'lead');
    $ci->db->order_by('id', 'desc');

    $query = $ci->db->get();
    return $query->row();
}

function get_group_id_by_using_group_name($ci, $groupname)
{
    $ci->db->select('*');
    $ci->db->where('group_name', $groupname);
    $query = $ci->db->get(db_prefix() . 'group');
    if ($query->num_rows() > 0) {
        $result = $query->row();
        $group_id = $result->id;
    } else {
        $group_id = null;
    }
    return $group_id;
}

function leads_splitFullName($name)
{
    $primary_name = explode(' ', $name, 3);
    $firstname = '';
    $middlename = '';
    $lastname = '';

    if (isset($primary_name[0])) {
        $firstname = $primary_name[0];
    }
    if (isset($primary_name[1])) {
        $middlename = $primary_name[1];
    }
    if (isset($primary_name[2])) {
        $lastname = $primary_name[2];
    }
    return ['firstname' => $firstname, 'middlename' => $middlename, 'lastname' => $lastname];
}
function get_group_name_by_using_group_id($ci, $groupid)
{
    $ci->db->select('group_name');
    $ci->db->where('id', $groupid);
    $query = $ci->db->get(db_prefix() . 'group');
    if ($query->num_rows() > 0) {
        $result = $query->row();
        $group_name = $result->group_name;
    } else {
        $group_name = null;
    }
    return $group_name;
}

function get_status_name_by_using_status_id($ci, $status)
{
    $ci->db->select('name');
    $ci->db->where('id', $status);
    $query = $ci->db->get(db_prefix() . 'leads_status');
    if ($query->num_rows() > 0) {
        $result = $query->row();
        $status_name = $result->name;
    } else {
        $status_name = null;
    }
    return $status_name;
}
function get_status_color_by_using_status_id($ci, $status)
{
    $ci->db->select('color');
    $ci->db->where('id', $status);
    $query = $ci->db->get(db_prefix() . 'leads_status');
    if ($query->num_rows() > 0) {
        $result = $query->row();
        $status_color = $result->color;
    } else {
        $status_color = null;
    }
    return $status_color;
}
// get the date format 
function get_day($dateString)
{
    $formattedDate = date('d M', strtotime($dateString));
    return $formattedDate ?? null;
}
function formatted_number($number)
{
    $number = str_replace(array(' ', '-', '(', ')'), '', $number);

    $formatted_number = "(" . substr($number, 0, 3) . ") " . substr($number, 3, 3) . "-" . substr($number, 6);
    if (!empty($formatted_number)) {
        return $formatted_number;
    } else {
        return '';
    }
}
// get the states name by id for appointment
function get_state_name($state_id)
{
    $ci = &get_instance();
    $ci->db->select('state_name');
    $ci->db->where('id', $state_id);
    $state_query = $ci->db->get(db_prefix() . 'states')->row();
    if (!empty($state_query)) {
        return $state_query->state_name;
    } else {
        return '';
    }
}
// get the state address by id for appointment
function get_state_address($state_id)
{
    $ci = &get_instance();
    $ci->db->select('state_address');
    $ci->db->where('id', $state_id);
    $state_query = $ci->db->get(db_prefix() . 'states')->row();
    if (!empty($state_query)) {
        return $state_query->state_address;
    } else {
        return '';
    }
}
function get_staff_ids_by_group($ci, $groupname = "")
{
    if (empty($groupname)) {
        return null;
    }
    $ci->db->select('staffid,email,staff_group,firstname,lastname,role');
    if (strpos($groupname, ',') !== false) {
        $groupname_array = explode(',', $groupname);
        $ci->db->where_in('staff_group', $groupname_array);
    } else {
        $ci->db->where('staff_group', $groupname);
    }
    $query = $ci->db->get(db_prefix() . 'staff');
    if ($query->num_rows() > 0) {
        return $query->result_array();
    } else {
        return null;
    }
}
function get_staff_name_by_id($ci, $staffid)
{
    $ci->db->select('staffid,email,staff_group,firstname,lastname');
    $ci->db->where('staffid', $staffid);
    $query = $ci->db->get(db_prefix() . 'staff');
    if ($query->num_rows() > 0) {
        $result = $query->row();
        $staff_name = $result->firstname . ' ' . $result->lastname;
    } else {
        $result = null;
        $staff_name = '-';
    }
    return $staff_name;
}
// Get the count of email drafts by leads_status and type
function get_email_draft_count($ci, $leads_status, $type)
{
    $ci->db->where('leads_status', $leads_status);
    $ci->db->where('type', $type);
    return $ci->db->count_all_results(db_prefix() . 'lead_email_drafts');
}
function get_email_draft_count_by_id($ci, $id)
{
    $ci->db->where('id', $id);
    return $ci->db->count_all_results(db_prefix() . 'lead_email_drafts');
}
// get the draft emails details
function get_email_draft_details($ci, $lead_id, $leads_status, $editor_id)
{
    $ci->db->select('*');
    $ci->db->where('leads_status', $leads_status);
    $ci->db->where('editor_id', $editor_id);
    $ci->db->where('lead_id', $lead_id);
    $query = $ci->db->get(db_prefix() . 'lead_email_drafts');
    if ($query->num_rows() > 0) {
        return $query->result_array();
    } else {
        return [];
    }
}


// get the checklist item latest rejected reason
function get_checklist_rejected_reason($ci, $task_id)
{
    $ci->db->select('reason');
    $ci->db->where('task_id', $task_id);
    $ci->db->where('activity_type', 'task-activity');
    $ci->db->where('additional_data', 'Rejected');
    $ci->db->order_by('id', 'desc');
    $ci->db->limit(1);
    $rejected_reason = $ci->db->get(db_prefix() . 'lead_activity_log')->row();
    return $rejected_reason->reason ?? '';
}
function get_task_name_by_task_id($ci, $task_id)
{
    $ci->db->select('*');
    $ci->db->where('id', $task_id);
    $query = $ci->db->get(db_prefix() . 'tasks');
    if ($query->num_rows() > 0) {
        $result = $query->row();
        // $staff_name = $result->firstname . ' ' . $result->lastname;
    } else {
        $result = null;
        $staff_name = '-';
    }
    return $result;
}

// This function retrieves the task ID associated with a file ID.
function get_task_id_by_file_id($ci, $file_id)
{
    if (empty($file_id)) {
        return null;
    }
    $ci->db->select('task_id');
    $ci->db->where('id', $file_id);
    $query = $ci->db->get(db_prefix() . 'files');
    if ($query->num_rows() > 0) {
        return $query->row()->task_id;
    } else {
        return null;
    }
}

function get_task_id_by_task_name($ci, $task_name, $lead_id)
{
    if (empty($task_name) || empty($lead_id)) {
        return null;
    }

    $ci->db->select('id');
    $ci->db->where('name', $task_name);
    $ci->db->where('rel_id', $lead_id);
    $query = $ci->db->get(db_prefix() . 'tasks');
    if ($query->num_rows() > 0) {
        return $query->row()->id;
    } else {
        return null;
    }
}
// nuborrow add multiple other items counter function
function nuborrow_add_multiple_other_items_counter($OtherItemCounter, $title)
{
    if (strtolower($title) == 'other') {
        $OtherItemCounter++;
    }
    return $OtherItemCounter;
}
// get the first,second,third client details
function get_client_name_using_taskid($ci, $task_id, $lead_id)
{
    $ci->db->select('lead_borrow_id');
    $ci->db->where('id', $task_id);
    $get_lead_borrower_id = $ci->db->get(db_prefix() . 'tasks')->row();

    if (!empty($get_lead_borrower_id->lead_borrow_id)) {
        $ci->db->select('borrow_firstname, borrow_lastname');
        $ci->db->where('id', $get_lead_borrower_id->lead_borrow_id);
        $get_borrower_data = $ci->db->get(db_prefix() . 'leads_borrow')->row();

        if ($get_borrower_data) {
            $data = array(
                'name' => $get_borrower_data->borrow_firstname . ' ' . $get_borrower_data->borrow_lastname
            );
        } else {
            $data = array(
                'name' => '-'
            );
        }
    } else {
        $ci->db->select('name');
        $ci->db->where('id', $lead_id);
        $get_lead_data = $ci->db->get(db_prefix() . 'leads')->row();

        if ($get_lead_data) {
            $data = array(
                'name' => $get_lead_data->name
            );
        } else {
            $data = array(
                'name' => '-'
            );
        }
    }
    return $data;
}
// get the client email using lead id
function get_client_email_using_lead_id($ci, $id)
{
    $ci->db->select('id,email');
    $ci->db->where('id', $id);
    $lead_data = $ci->db->get(db_prefix() . 'leads')->row();
    return $lead_data->email ?? '';
}
function get_borrower_email_using_borrower_id($ci, $id)
{
    $ci->db->select('id,borrow_email');
    $ci->db->where('id', $id);
    $borrower_data = $ci->db->get(db_prefix() . 'leads_borrow')->row();
    return $borrower_data->borrow_email ?? '';
}
function get_client_name_using_lead_id($ci, $lead_id)
{
    $ci->db->select('id,name');
    $ci->db->where('id', $lead_id);
    $lead_data = $ci->db->get(db_prefix() . 'leads')->row();
    return $lead_data->name ?? '';
}
function get_borrower_name_using_borrower_id($ci, $borrower_id)
{
    $ci->db->select('id,borrow_firstname,borrow_middlename,borrow_lastname');
    $ci->db->where('id', $borrower_id);
    $borrower_data = $ci->db->get(db_prefix() . 'leads_borrow')->row();
    return $borrower_data->borrow_firstname ?? '';
}
function get_all_client_phonenumber_using_lead_id($ci, $lead_id)
{
    $phone_number_list = [];

    $ci->db->select('phonenumber');
    $ci->db->where('id', $lead_id);
    $get_client_data = $ci->db->get(db_prefix() . 'leads')->row();

    if (!empty($get_client_data->phonenumber)) {
        $phone_number_list[] = $get_client_data->phonenumber;
    }

    $ci->db->select('borrow_phone');
    $ci->db->where('lead_id', $lead_id);
    $get_borrower_data = $ci->db->get(db_prefix() . 'leads_borrow')->result_array();

    foreach ($get_borrower_data as $borrower) {
        if (!empty($borrower['borrow_phone'])) {
            $phone_number_list[] = $borrower['borrow_phone'];
        }
    }
    return $phone_number_list ?? array();
}
function get_client_phonenumber_using_client_id_and_type($ci, $lead_id, $type)
{
    $phone_number_list = [];
    if ($type == 'Client' || $type == 'client') {
        $ci->db->select('phonenumber');
        $ci->db->where('id', $lead_id);
        $get_client_data = $ci->db->get(db_prefix() . 'leads')->row();

        if (!empty($get_client_data->phonenumber)) {
            $phone_number_list[] = $get_client_data->phonenumber;
        }
    } else if ($type == 'Borrower' || $type == 'borrower') {
        $ci->db->select('borrow_phone');
        $ci->db->where('id', $lead_id);
        $get_borrower_data = $ci->db->get(db_prefix() . 'leads_borrow')->row();

        if (!empty($get_borrower_data->borrow_phone)) {
            $phone_number_list[] = $get_borrower_data->borrow_phone;
        }
    }
    return $phone_number_list ?? array();
}
function get_staff_email_by_id($ci, $staffid)
{
    if (empty($staffid)) {
        return '';
    }
    $ci->db->select('staffid,email,staff_group,firstname,lastname');
    $ci->db->where('staffid', $staffid);
    $query = $ci->db->get(db_prefix() . 'staff');
    if ($query->num_rows() > 0) {
        $result = $query->row();
        $staff_email = $result->email;
    } else {
        $result = null;
        $staff_email = '';
    }
    return $staff_email;
}
// function get_all_staff_to_replay_email($ci, $staffid, $assign_group)
// {
//     $default_emails = [
//         ['email' => 'bb@legaldirect.ca'],
//         ['email' => 'ft@legaldirect.ca']
//     ];

//     $assign_group = (array) $assign_group; 
//     if (!empty($assign_group)) {
//         $ci->db->select('staffid, email, staff_group, firstname, lastname');
//         $ci->db->group_start(); 
//             $ci->db->where('staffid', $staffid);
//                 $ci->db->or_group_start(); 
//                     foreach ($assign_group as $group) {
//                         $ci->db->or_where("FIND_IN_SET('$group', staff_group) >", 0);
//                     }
//                 $ci->db->group_end();
//         $ci->db->group_end();
//         $query = $ci->db->get(db_prefix() . 'staff');
//         return $query->num_rows() > 0 ? $query->result_array() : $default_emails;
//     }
//     return $default_emails;
// }
function get_all_staff_to_replay_email($ci, $staffid, $assign_group, $to_clients_emails = '')
{
    if (!is_array($assign_group)) {
        $assign_group = array_filter(array_map('trim', explode(',', $assign_group)));
    }

    $staff_emails = [];
    if (!empty($assign_group)) {
        $ci->db->select('staffid, email, staff_group, firstname, lastname');
        $ci->db->group_start();
        $ci->db->where('staffid', $staffid);
        $ci->db->or_group_start();
        foreach ($assign_group as $group) {
            $ci->db->or_where("FIND_IN_SET('$group', staff_group) >", 0);
        }
        $ci->db->group_end();
        $ci->db->group_end();

        $query = $ci->db->get(db_prefix() . 'staff');
        if ($query->num_rows() > 0) {
            $staff_emails = array_map(function ($row) {
                return ['email' => $row['email']];
            }, $query->result_array());
        }
    }

    if (!is_array($to_clients_emails)) {
        $to_clients_emails = array_filter(array_map('trim', explode(',', $to_clients_emails)));
    }

    $client_emails = array_map(function ($email) {
        return ['email' => $email];
    }, $to_clients_emails);

    $final_emails = !empty($assign_group)
        ? array_merge($staff_emails, $client_emails)
        : array_merge([['email' => 'bb@legaldirect.ca'], ['email' => 'ft@legaldirect.ca']], $client_emails);

    return $final_emails;
}
function get_reply_to_emails($ci, $outbox_id)
{
    $reply_to_emails = [];

    // Get all reply-to records for this outbox ID
    $ci->db->select('email');
    $ci->db->where('outbox_id', $outbox_id);
    $query = $ci->db->get('tbloutbox_replay_to');
    $reply_to_data = $query->result();

    // Extract emails if available
    if (!empty($reply_to_data)) {
        foreach ($reply_to_data as $row) {
            if (!empty($row->email)) {
                $reply_to_emails[] = $row->email;
            }
        }
    }

    // Default reply-to if none provided
    if (empty($reply_to_emails)) {
        $reply_to_emails[] = 'bb@legaldirect.ca';
    }

    return $reply_to_emails;
}

// get outbox lead status
function get_outbox_lead_status($ci, $outbox_id)
{
    $ci->db->select('lead_status');
    $ci->db->where('id', $outbox_id);
    $query = $ci->db->get(db_prefix() . 'outbox_email');
    if ($query->num_rows() > 0) {
        return $query->row()->lead_status;
    } else {
        return null;
    }
}
//  extract only what's inside <body> email
function extract_email_body($email_content)
{
    $pattern = '/<body[^>]*>(.*?)<\/body>/is';
    preg_match($pattern, $email_content, $matches);
    return isset($matches[1]) ? $matches[1] : '';
}

function get_the_ledger_number_count($ci, $lead_id, $type)
{
    $ci->db->select('id,ledger_number');
    $ci->db->distinct();
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('type', $type);
    $ci->db->order_by('id', 'desc');
    $ci->db->limit(1);
    $query = $ci->db->get(db_prefix() . 'leads_ledger');
    $ledger_number_row = $query->row();
    if ($type == 'broker') {
        $ledger_number_count = $ledger_number_row->ledger_number;
    } else {
        if (!empty($ledger_number_row)) {
            $ledger_number_count = $ledger_number_row->ledger_number + 1;
        } else {
            $ledger_number_count = 1;
        }
    }
    return $ledger_number_count ?? null;
}
function get_latest_distinct_ledger_number($ci, $lead_id)
{
    $subquery = $ci->db->select('MAX(id) as id', false)
        ->where('lead_id', $lead_id)
        ->where('bb_status', 'Approved')
        ->group_by('ledger_number')
        ->get_compiled_select(db_prefix() . 'leads_ledger');

    $query = $ci->db->select('ledger_number')
        ->where("id IN ($subquery)", null, false)
        ->order_by('id', 'desc')
        ->limit(1)
        ->get(db_prefix() . 'leads_ledger');

    $ledger_number_row = $query->row();
    return $ledger_number_row;
}
function get_latest_set_case_appointment($ci, $lead_id)
{
    $ci->db->select('date as start_date, end_date');
    $ci->db->from(db_prefix() . 'reminders');
    $ci->db->where('rel_id', $lead_id);
    $ci->db->order_by('id', 'DESC');
    $ci->db->limit(1);
    $reminder = $ci->db->get()->row();
    if ($reminder) {
        return array(
            'start_date' => $reminder->start_date,
            'end_date' => $reminder->end_date,
        );
    }
    return null;
}
function get_the_ldteam_latest_ledger_approved_id($ci, $lead_id, $ledger_number)
{
    $ci->db->select('id, ledger_number');
    $ci->db->where('lead_id', $lead_id);
    $ci->db->where('ledger_number', $ledger_number);
    $ci->db->where('type', 'ld_team');
    $ci->db->where('bb_status', 'Approved');
    $ci->db->limit(1);
    $query = $ci->db->get(db_prefix() . 'leads_ledger');
    return $query->row();
}
function get_all_lead_borrower_first_name($ci, $lead_id)
{
    $ci->db->select('name');
    $ci->db->where('id', $lead_id);
    $get_lead_data = $ci->db->get(db_prefix() . 'leads')->row();
    $name_parts = explode(' ', $get_lead_data->name);
    $first_name = $name_parts[0];

    $borrower_names = $first_name;

    $ci->db->select('borrow_firstname');
    $ci->db->where('lead_id', $lead_id);
    $get_borrower_data = $ci->db->get(db_prefix() . 'leads_borrow')->result_array();

    foreach ($get_borrower_data as $borrower) {
        if (!empty($borrower['borrow_firstname'])) {
            $borrower_names .= ' and ' . $borrower['borrow_firstname'];
        }
    }
    return $borrower_names ?? '';
}
function get_all_client_email_using_lead_id($ci, $lead_id)
{
    $email_list = [];

    $ci->db->select('email');
    $ci->db->where('id', $lead_id);
    $get_client_data = $ci->db->get(db_prefix() . 'leads')->row();

    if (!empty($get_client_data->email)) {
        $email_list[] = $get_client_data->email;
    }

    $ci->db->select('borrow_email');
    $ci->db->where('lead_id', $lead_id);
    $get_borrower_data = $ci->db->get(db_prefix() . 'leads_borrow')->result_array();

    foreach ($get_borrower_data as $borrower) {
        if (!empty($borrower['borrow_email'])) {
            $email_list[] = $borrower['borrow_email'];
        }
    }

    return implode(',', $email_list);
}
// check all client single or multiple emails extences
function get_all_clients_email_counts_separately($ci, $lead_id)
{
    $leads_emails = [];
    $borrower_emails = [];

    $ci->db->select('email');
    $ci->db->where('id', $lead_id);
    $get_client_data = $ci->db->get(db_prefix() . 'leads')->row();

    if (!empty($get_client_data->email)) {
        $leads_emails[] = $get_client_data->email;
    }

    $ci->db->select('borrow_email');
    $ci->db->where('lead_id', $lead_id);
    $get_borrower_data = $ci->db->get(db_prefix() . 'leads_borrow')->result_array();

    // get the count of borrower 
    $ci->db->where('lead_id', $lead_id);
    $borrower_count = $ci->db->count_all_results(db_prefix() . 'leads_borrow');

    foreach ($get_borrower_data as $borrower) {
        if (!empty($borrower['borrow_email'])) {
            $borrower_emails[] = $borrower['borrow_email'];
        }
    }
    $unique_leads_emails = array_unique($leads_emails);
    $unique_borrower_emails = array_unique($borrower_emails);
    return [
        'leads_email_count' => count($unique_leads_emails),
        'leads_email_list' => $unique_leads_emails,
        'borrower_email_count' => count($unique_borrower_emails),
        'borrower_email_list' => $unique_borrower_emails,
        'borrower_count' => $borrower_count
    ];
}
function case_crud_permissions_by_role($ci, $leadGroup, $staffGroup)
{
    if (empty(is_admin())) {
        if (get_current_staff_role($ci, get_staff_user_id()) == 2 || get_current_staff_role($ci, get_staff_user_id()) == 6) {
            $samegroup = 2;
        } else {
            if ($staffGroup == $leadGroup) {
                $samegroup = true;
            } else if (get_current_staff_role($ci, get_staff_user_id()) == 8) {
                $samegroup = true;
            } else if (get_current_staff_role($ci, get_staff_user_id()) == 7 || get_current_staff_role($ci, get_staff_user_id()) == 9) {
                $samegroup = true;
            } else {
                $samegroup = true;
            }
        }
    } else {
        $samegroup = true;
    }
    return $samegroup;
}

function is_lead_creator($lead_id, $staff_id = '')
{
    if (!is_numeric($staff_id)) {
        $staff_id = get_staff_user_id();
    }

    return total_rows(db_prefix() . 'leads', [
        'addedfrom' => $staff_id,
        'id' => $lead_id,
    ]) > 0;
}
function add_contacts($data)
{
    $contact['email'] = $data['email'];
    $contact['firstname'] = $data['firstname'];
    $contact['middlename'] = $data['middlename'];
    $contact['lastname'] = $data['lastname'];
    if (!empty($data['client_id'])) {
        $contact['userid'] = $data['client_id'];
    } else {
        $contact['userid'] = 0;
    }
    $contact['title'] = $data['title'];
    $contact['phonenumber'] = formatted_number($data['phonenumber']);
    $contact['datecreated'] = $data['dateadded'];
    if (empty($data['additional_email'])) {
        $data['additional_email'] = '';
    }

    return $contact;
}
function add_clients($data)
{
    if (!empty($data['company'])) {
        $clients['company'] = $data['company'];
    } else {
        $clients['company'] = '';
    }
    $clients['country'] = $data['country'];
    $clients['zip'] = $data['zip'];
    $clients['city'] = $data['city'];
    $clients['state'] = $data['state'];
    $clients['address'] = $data['address'];
    if (!empty($data['dateadded'])) {
        $clients['datecreated'] = $data['dateadded'];
    } else {
        $clients['datecreated'] = '';
    }
    if (!empty($data['addedfrom'])) {
        $clients['addedfrom'] = implode(',', $data['addedfrom']);
    } else {
        $clients['addedfrom'] = '';
    }

    $clients['website'] = $data['website'];
    $clients['phonenumber'] = formatted_number($data['phonenumber']);
    $clients['default_language'] = $data['default_language'];
    return $clients;
}
function add_leads($data)
{
    if (isset($data['custom_contact_date']) || isset($data['custom_contact_date'])) {
        if (isset($data['contacted_today'])) {
            $data['lastcontact'] = date('Y-m-d H:i:s');
            unset($data['contacted_today']);
        } else {
            $data['lastcontact'] = to_sql_date($data['custom_contact_date'], true);
        }
    }
    if (empty($data['secondary_email_check'])) {
        $data['secondary_email_check'] = null;
    }
    // check new lender column exit or not
    if (!empty($data['new_lender_name'])) {
        $data['lender_name'] = $data['new_lender_name'];
        unset($data['new_lender_name']);
    }
    if (isset($data['is_public']) && ($data['is_public'] == 1 || $data['is_public'] === 'on')) {
        $data['is_public'] = 1;
    } else {
        $data['is_public'] = 0;
    }

    if (!isset($data['country']) || isset($data['country']) && $data['country'] == '') {
        $data['country'] = 0;
    }
    if (empty($data['firstname']) && empty($data['middlename']) && empty($data['lastname'])) {
        $data['name'] = '';
    } else {
        $data['name'] = $data['firstname'] . ' ' . $data['middlename'] . ' ' . $data['lastname'];
    }

    if (isset($data['custom_contact_date'])) {
        unset($data['custom_contact_date']);
    }
    if (!empty($data['description'])) {
        $data['description'] = nl2br($data['description']);
    } else {
        $data['description'] = '';
    }
    if (empty($data['additional_email'])) {
        $data['additional_email'] = '';
    }
    $data['dateadded'] = date('Y-m-d H:i:s');
    $data['dateassigned'] = date('Y-m-d');
    $data['last_status_change'] = date('Y-m-d H:i:s');
    $data = hooks()->apply_filters('before_lead_added', $data);

    $tags = '';
    if (isset($data['tags'])) {
        $tags = $data['tags'];
        unset($data['tags']);
    }

    if (isset($data['custom_fields'])) {
        $custom_fields = $data['custom_fields'];
        unset($data['custom_fields']);
    }
    if (!empty($data['address'])) {
        $data['address'] = trim($data['address']);
        $data['address'] = nl2br($data['address']);
    } else {
        $data['address'] = '';
    }
    if (!empty($data['email'])) {
        $data['email'] = trim($data['email']);
    } else {
        $data['email'] = '';
    }
    $data['multiple_cases'] = '0';
    if (empty($data['additional_email'])) {
        $data['additional_email'] = '';
    }
    if (empty($data['excepting_closing_date'])) {
        $data['excepting_closing_date'] = '';
    }
    if (empty($data['preferred_contact_method'])) {
        $data['preferred_contact_method'] = '';
    } else {
        $comma_separated = implode(', ', $data['preferred_contact_method']);
        $data['preferred_contact_method'] = $comma_separated;
    }
    if (empty($data['loan_amount'])) {
        $data['loan_amount'] = '';
    }
    if (empty($data['lender_ids'])) {
        $data['lender_ids'] = '';
    }
    if (empty($data['lead_borrow_position'])) {
        $data['lead_borrow_position'] = '';
    }
    return $data;
}
function get_all_leads_borrow_data($ci, $lead_id)
{
    $ci->db->where('lead_id', $lead_id);
    $borrow_data = $ci->db->get(db_prefix() . 'leads_borrow')->result_array();
    return $borrow_data ?? null;
}
function get_all_leads_borrow_data_by_id($ci, $lead_id, $borrowerId)
{
    $firstname = '';
    $middlename = '';
    $lastname = '';
    if ($lead_id == $borrowerId || empty($borrowerId)) {
        $ci->db->select('id, name, address, phonenumber, email');
        $ci->db->where('id', $lead_id);
        $borrow_data = $ci->db->get(db_prefix() . 'leads')->row();
        $primary_name = explode(' ', $borrow_data->name, 3);

        if (isset($primary_name[0])) {
            $firstname = $primary_name[0];
        }
        if (isset($primary_name[1])) {
            $middlename = $primary_name[1];
        }
        if (isset($primary_name[2])) {
            $lastname = $primary_name[2];
        }
        $response = array(
            'borrower_id' => $borrow_data->id,
            'borrow_firstname' => $firstname,
            'borrow_middlename' => $middlename,
            'borrow_lastname' => $lastname,
            'borrow_address' => $borrow_data->address,
            'borrow_email' => $borrow_data->email,
            'borrow_phone' => $borrow_data->phonenumber
        );
    } else {
        $ci->db->select('id, borrow_firstname, borrow_middlename, borrow_lastname, borrow_address, borrow_email, borrow_phone');
        $ci->db->where('id', $borrowerId);
        $borrow_data = $ci->db->get(db_prefix() . 'leads_borrow')->row();
        $response = array(
            'borrower_id' => $borrow_data->id,
            'borrow_firstname' => $borrow_data->borrow_firstname,
            'borrow_middlename' => $borrow_data->borrow_middlename,
            'borrow_lastname' => $borrow_data->borrow_lastname,
            'borrow_address' => $borrow_data->borrow_address,
            'borrow_email' => $borrow_data->borrow_email,
            'borrow_phone' => $borrow_data->borrow_phone
        );
    }

    return !empty($response) ? $response : null;
}
function get_leads_borrow_count($ci, $lead_id)
{
    $ci->db->select('COUNT(id) as count');
    $ci->db->where('lead_id', $lead_id);
    $query = $ci->db->get(db_prefix() . 'leads_borrow');
    $result = $query->row_array();
    if ($result['count'] == 1) {
        return $result['count'] + 1 ?? 0;
    } else {
        return $result['count'] + 1 ?? 0;
    }
}
function leads_bank_details($ci, $lead_id)
{
    $ci->db->select('*');
    $ci->db->where('lead_id', $lead_id);
    $data = $ci->db->get(db_prefix() . 'leads_bank_details')->result_array();
    return !empty($data) ? $data : '';
}
function get_transaction_name_by_id($ci, $transaction_id)
{
    $ci->db->select('name');
    $ci->db->where('id', $transaction_id);
    $data = $ci->db->get(db_prefix() . 'leads_transaction_details')->row();
    return !empty($data) ? $data->name : '';
}
function get_laywer_list($ci)
{
    $ci->db->select('staffid,firstname,lastname');
    $ci->db->where('role', 5);
    $staff_data = $ci->db->get(db_prefix() . 'staff')->result_array();
    return $staff_data ?? array();
}
function get_laywer_list_by_id($ci, $id)
{
    $ci->db->select('id,firstname,lastname');
    $ci->db->where('id', $id);
    $staff_data = $ci->db->get(db_prefix() . 'laywer')->row();
    return $staff_data ?? array();
}
function get_laywer_firm_list($ci)
{
    $ci->db->select('id,name,email,address');
    $ci->db->order_by('name', 'ASC');
    $laywer_firm = $ci->db->get(db_prefix() . 'laywer_firm')->result_array();
    return $laywer_firm ?? array();
}
function get_laywer_firm_list_by_id($ci, $id)
{
    $ci->db->select('id,name,email,address');
    $ci->db->where('id', $id);
    $laywer_firm = $ci->db->get(db_prefix() . 'laywer_firm')->row();
    return $laywer_firm ?? array();
}
function get_lender_ids_using_lead_id($ci, $lead_id)
{
    $ci->db->select('lender_ids');
    $ci->db->where('id', $lead_id);
    $get_lender_ids = $ci->db->get(db_prefix() . 'leads')->row();
    return $get_lender_ids->lender_ids ?? '';
}
function getLenderDetailsByLeadId($ci, $lead_id)
{
    $ci->db->select('bank_id');
    $ci->db->where('lead_id', $lead_id);
    $result = $ci->db->get(db_prefix() . 'leads_bank_details')->result_array();
    return array_column($result, 'bank_id');
}

/**
 * Lead consent URL
 * @param  mixed $id lead id
 * @return string
 */
function lead_consent_url($id)
{
    return site_url('consent/l/' . get_lead_hash($id));
}

/**
 * Lead public form URL
 * @param  mixed $id lead id
 * @return string
 */
function leads_public_url($id)
{
    return site_url('forms/l/' . get_lead_hash($id));
}

/**
 * Get and generate lead hash if don't exists.
 * @param  mixed $id  lead id
 * @return string
 */
function get_lead_hash($id)
{
    $CI = &get_instance();
    $hash = '';

    $CI->db->select('hash');
    $CI->db->where('id', $id);
    $lead = $CI->db->get(db_prefix() . 'leads')->row();
    if ($lead) {
        $hash = $lead->hash;
        if (empty($hash)) {
            $hash = app_generate_hash() . '-' . app_generate_hash();
            $CI->db->where('id', $id);
            $CI->db->update(db_prefix() . 'leads', ['hash' => $hash]);
        }
    }

    return $hash;
}

/**
 * Get leads summary
 * @return array
 */
function get_leads_summary()
{
    $CI = &get_instance();
    if (!class_exists('leads_model')) {
        $CI->load->model('leads_model');
        $CI->load->helper('staff_helper');
    }
    $statuses = $CI->leads_model->get_status();

    $totalStatuses = count($statuses);

    $sql = '';
    if (get_current_staff_role($CI, get_staff_user_id()) == 2) {
        $has_permission_view = false;
        // $whereNoViewPermission = 'addedfrom = ' . get_staff_user_id();
        $whereNoViewPermission = 'FIND_IN_SET(' . get_staff_user_id() . ', addedfrom) > 0';
    } else if (get_current_staff_role($CI, get_staff_user_id()) == 6) {
        $whereNoViewPermission = 'FIND_IN_SET(' . get_current_staff_brokerage_id($CI, get_staff_user_id()) . ', brokerage_id) > 0';
    } else {
        $has_permission_view = has_permission('leads', '', 'view');
        $whereNoViewPermission = '(addedfrom = ' . get_staff_user_id() . ' OR assigned=' . get_staff_user_id() . ' OR is_public = 1)';
    }

    $statuses[] = [
        'lost' => true,
        'name' => 'Lost Cases',
        'color' => '#fc2d42',
    ];

    /*    $statuses[] = [
            'junk'  => true,
            'name'  => _l('junk_leads'),
            'color' => '',
        ];*/

    foreach ($statuses as $status) {
        $sql .= ' SELECT COUNT(*) as total';
        $sql .= ',SUM(lead_value) as value';
        $sql .= ' FROM ' . db_prefix() . 'leads';

        if (isset($status['lost'])) {
            $sql .= ' WHERE lost=1';
        } elseif (isset($status['junk'])) {
            $sql .= ' WHERE junk=1';
        } else {
            $sql .= ' WHERE status=' . $status['id'];
        }
        if (!$has_permission_view) {
            $sql .= ' AND ' . $whereNoViewPermission;
        }
        $sql .= ' UNION ALL ';
        $sql = trim($sql);
    }

    $result = [];

    // Remove the last UNION ALL
    $sql = substr($sql, 0, -10);
    $result = $CI->db->query($sql)->result();

    if (!$has_permission_view) {
        $CI->db->where($whereNoViewPermission);
    }

    $total_leads = $CI->db->count_all_results(db_prefix() . 'leads');

    foreach ($statuses as $key => $status) {
        if (isset($status['lost']) || isset($status['junk'])) {
            $statuses[$key]['percent'] = ($total_leads > 0 ? number_format(($result[$key]->total * 100) / $total_leads, 2) : 0);
        }

        $statuses[$key]['total'] = $result[$key]->total;
        $statuses[$key]['value'] = $result[$key]->value;
    }

    return $statuses;
}

/**
 * Render lead status select field with ability to create inline statuses with + sign
 * @param  array  $statuses         current statuses
 * @param  string  $selected        selected status
 * @param  string  $lang_key        the label of the select
 * @param  string  $name            the name of the select
 * @param  array   $select_attrs    additional select attributes
 * @param  boolean $exclude_default whether to exclude default Client status
 * @return string
 */
function render_leads_status_select($statuses, $selected = '', $lang_key = '', $name = 'status', $select_attrs = [], $exclude_default = false)
{
    foreach ($statuses as $key => $status) {
        if ($status['isdefault'] == 1) {
            if ($exclude_default == false) {
                $statuses[$key]['option_attributes'] = ['data-subtext' => _l('leads_converted_to_client')];
            } else {
                unset($statuses[$key]);
            }

            break;
        }
    }

    if (is_admin() || get_option('staff_members_create_inline_lead_status') == '1') {
        return render_select_with_input_group($name, $statuses, ['id', 'name'], $lang_key, $selected, '<div class="input-group-btn"><a href="#" class="btn btn-default" " class="inline-field-new" style="display:none;"><i class="fa fa-plus"></i></a></div>', $select_attrs);
    }

    return render_select($name, $statuses, ['id', 'name'], $lang_key, $selected, $select_attrs);
}

/**
 * Render lead source select field with ability to create inline source with + sign
 * @param  array   $sources         current sourcees
 * @param  string  $selected        selected source
 * @param  string  $lang_key        the label of the select
 * @param  string  $name            the name of the select
 * @param  array   $select_attrs    additional select attributes
 * @return string
 */
function render_leads_source_select($sources, $selected = '', $lang_key = '', $name = 'source', $select_attrs = [])
{
    if (is_admin() || get_option('staff_members_create_inline_lead_source') == '1') {
        echo render_select_with_input_group($name, $sources, ['id', 'name'], $lang_key, $selected, '<div class="input-group-btn"><a href="#" class="btn btn-default" onclick="new_lead_source_inline();return false;" class="inline-field-new"><i class="fa fa-plus"></i></a></div>', $select_attrs);
    } else {
        echo render_select($name, $sources, ['id', 'name'], $lang_key, $selected, $select_attrs);
    }
}

/**
 * Load lead language
 * Used in public GDPR form
 * @param  string $lead_id
 * @return string return loaded language
 */
function load_lead_language($lead_id)
{
    $CI = &get_instance();
    $CI->db->where('id', $lead_id);
    $lead = $CI->db->get(db_prefix() . 'leads')->row();

    // Lead not found or default language already loaded
    if (!$lead || empty($lead->default_language)) {
        return false;
    }

    $language = $lead->default_language;

    if (!file_exists(APPPATH . 'language/' . $language)) {
        return false;
    }

    $CI->lang->is_loaded = [];
    $CI->lang->language = [];

    $CI->lang->load($language . '_lang', $language);
    load_custom_lang_file($language);
    $CI->lang->set_last_loaded_language($language);

    return true;
}
function transmute_layer($plain, $lock)
{
    $method = 'AES-256-CBC';

    // Derive a 32-byte key and a 16-byte IV from the lock
    $key = hash('sha256', $lock, true); // 32 bytes
    $iv = substr($key, 0, 16);          // 16 bytes IV from key

    $prefix = 'xT7#';
    $suffix = '!zQ9';
    $tagged = $prefix . $plain . $suffix;

    return base64_encode(openssl_encrypt($tagged, $method, $key, OPENSSL_RAW_DATA, $iv));
}

function unlayer_extract($encoded, $lock)
{
    $method = 'AES-256-CBC';

    // Derive the same key and IV
    $key = hash('sha256', $lock, true);
    $iv = substr($key, 0, 16);

    $prefix = 'xT7#';
    $suffix = '!zQ9';

    $decoded = openssl_decrypt(base64_decode($encoded), $method, $key, OPENSSL_RAW_DATA, $iv);

    if ($decoded && strpos($decoded, $prefix) === 0 && substr($decoded, -strlen($suffix)) === $suffix) {
        return substr($decoded, strlen($prefix), -strlen($suffix));
    }

    return false;
}


