<?php include('../connect.php');
session_start();
    $ajaxcall = $_REQUEST['ajaxcall'];
function getMatVersion($form_id){
    global $pdo;
  $stmt1 = $pdo->prepare("SELECT version from tblconsentversions where form_id=?");
  $stmt1->execute([$form_id]);
  $patientDetails = $stmt1->fetchColumn();
  return $patientDetails;
}
    if($ajaxcall == 1){
        $medication = $_REQUEST['id'];

        $stmt = $pdo->prepare("SELECT pspId FROM tbldrugs WHERE id = ?");
        $stmt->execute([$medication]);
        $psp = $stmt->fetch();

        $stmt1 = $pdo->prepare("SELECT * FROM tblpsp WHERE id = ?");
        $stmt1->execute([$psp['pspId']]);
        $psps = $stmt1->fetch();

        $data  = "<select class='form-control select2bs4' name='pspId' required id='pspId'>";
       
        $data .= "<option selected value='".$psps['id']."'>".$psps['pspName']."</option>";
        
        $data .= "</select>";
        echo $data;
    }
    
    if($ajaxcall == 2){
        $paymentDetailId = $_REQUEST['id'];
        $data  = "<input type='text' id='txtReceivedAmt".$paymentDetailId."'>";
        echo $data;
    }
    
    if($ajaxcall == 3){
        $paymentDetailId = $_REQUEST['id'];
        $submittedAmt = $_REQUEST['submittedAmt'];
        $txtReceivedAmt = $_REQUEST['txtReceivedAmt'];

        $discrepancyAmt = $submittedAmt - $txtReceivedAmt;

        $stmt= $pdo->prepare("UPDATE tblpaymentdetails SET amountPaid = ?, discrepancy =? WHERE id=?");
        $result = $stmt->execute([$txtReceivedAmt, $discrepancyAmt, $paymentDetailId]);
        if($result){
            $data = "<span id='orderStatus$paymentDetailId'>$txtReceivedAmt</span>,$<span id='discrepancy$paymentDetailId'>$discrepancyAmt</span>";
        }
        else{
            $flag = 1;
        }
        echo $data;
    }
    
    if ($ajaxcall == "deletePatient") {
        $id = $_POST['id'];
        $stmt= $pdo->prepare("UPDATE tblpatientdocs SET isDeleted = 'Y' WHERE id = ?");
        $result = $stmt->execute([$id]);
        if($result){
            echo 'Done';
        }
        else{
            echo 'Issue';
        }
    }
    
    if ($ajaxcall == "checkDob") {
    $dob = trim($_POST['dob'] ?? '');
    $firstName = trim($_POST['firstName'] ?? '');
    $lastName = trim($_POST['lastName'] ?? '');
    $fullName = trim($firstName . ' ' . $lastName);
    // Check if $krollId is provided
    if (!empty($dob) && !empty($firstName)) {
        try {
            // Prepare and execute the query to check if the KROLL ID exists
            $stmt = $pdo->prepare("SELECT id FROM tblpatient WHERE (firstName = ? and lastName=? and dob=?) or (patientName=? and dob=?) ");
            $stmt->execute([$firstName,$lastname,$dob,$fullName,$dob]);
            // Fetch the result
            $patient = $stmt->fetch(PDO::FETCH_ASSOC);
            // Check if a record is found
            if ($patient) {
                echo json_encode(['exists' => true, 'patientId' => $patient['id']]);
            } else {
                echo json_encode(['exists' => false]);
            }
        } catch (Exception $e) {
            // Handle any errors
            echo json_encode(['error' => 'An error occurred while checking the KROLL ID.']);
        }
    } else {
        echo json_encode(['error' => 'No KROLL ID provided.']);
    }
    die();
    }

if ($ajaxcall == "checkKrollId") {
    $krollId = $_POST['krollId'];

    // Check if $krollId is provided
    if (!empty($krollId)) {
        try {
            // Prepare and execute the query to check if the KROLL ID exists
            $stmt = $pdo->prepare("SELECT id FROM tblpatient WHERE krollId = ?");
            $stmt->execute([$krollId]);

            // Fetch the result
            $patient = $stmt->fetch(PDO::FETCH_ASSOC);

            // Check if a record is found
            if ($patient) {
                echo json_encode(['exists' => true, 'patientId' => $patient['id']]);
            } else {
                echo json_encode(['exists' => false]);
            }
        } catch (Exception $e) {
            // Handle any errors
            echo json_encode(['error' => 'An error occurred while checking the KROLL ID.']);
        }
    } else {
        echo json_encode(['error' => 'No KROLL ID provided.']);
    }
    die();
}

    if ($ajaxcall == "addPhysician") {
        $physicianFirstName = $_POST['physicianFirstName'];
        $physicianLastName = $_POST['physicianLastName'];
        $hospital = $_POST['hospital'];
        $primarySpecialty = $_POST['primarySpecialty'];

        $physicianAddress = $_POST['physicianAddress'];
        $physicianCity = $_POST['physicianCity'];
        $physicianProvince = $_POST['physicianProvince'];
        $physicianPostalCode = $_POST['physicianPostalCode'];

        $physicianPhone = $_POST['physicianPhone'];
        $physicianEmail = $_POST['physicianEmail'];
        $createdBy = $_SESSION['empId'];
        //////////////////////////
        //////////////////////////
        $stmt = $pdo->prepare("INSERT INTO tblphysicians(firstName,lastName,hospital,primarySpecialty,streetAddress,city,province,postalCode,phone,email,createdBy) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        $result = $stmt->execute([$physicianFirstName,$physicianLastName,$hospital,$primarySpecialty,$physicianAddress,$physicianCity,$physicianProvince,$physicianPostalCode, $physicianPhone, $physicianEmail,$createdBy]);
        $pId = $pdo->lastInsertId();
        echo $pId;
        die();
    }
 
    if ($ajaxcall == "getPhysician") {
        $physicianId = $_POST['physicianId'];
      
        $stmt = $pdo->prepare("SELECT * from tblphysicians where id=?");
        $result = $stmt->execute([$physicianId]);
        $pId = $stmt->fetch();
        echo json_encode($pId);
        die();
    }
    if ($ajaxcall == "getPatientDetails") {
        $pId = $_POST['pId'];
      
        $stmt = $pdo->prepare("SELECT * from tblpatient where id=?");
        $result = $stmt->execute([$pId]);
        $pId = $stmt->fetch();
        echo json_encode($pId);
        die();
    }
    
    if ($ajaxcall == "fetch_patient_substatus") {
        $statusID = $_GET['statusID'];

        $query = "SELECT SubstatusID, SubstatusName FROM tblpatientsubstatus WHERE StatusID = ? and isDeleted='N'";
        $stmt = $pdo->prepare($query);
        $stmt->execute([$statusID]);
        $statuses = $stmt->fetchAll();
        echo json_encode($statuses);
        die();
    }
    
    if ($ajaxcall == "addexistingPatient") {
        // Handle patient information //*/ /* aaaaa
       // get pspId if exists
        $pspId= $_GET["pspId"] ?? $pspId ?? '';

        // $pspId = $_POST['pspId'];
        // $medicationId = $_POST['medicationId'];
        $pId = $_POST['patientId'];
        $patientFName = $_POST['patientFName'];
        $patientLName = $_POST['patientLName'];
        $patientName = $patientFName . " " . $patientLName;
        $dob = $_POST['dob'];
        $enrolmentFormReceiveDate = $_POST['enrolmentFormReceiveDate'];
        $gender = $_POST['gender'];
        $phone = $_POST['phone'];
        $email = $_POST['email'];
        $address1 = $_POST['address1'];
        $address2 = $_POST['address2'];
        $city = $_POST['city'];
        $province = $_POST['province'];
        $postalCode = $_POST['postalCode'];
        $createdBy = $_SESSION['empId'];
        $notes = $_POST['notes'];
        $krollId = $_POST['krollId'];
        //wc part5
        $patientDrugName=$_POST['patientDrugName'];
        $dosage=$_POST['dose'];
        $routeOfAdmin=$_POST['roa'];
        $prescriberName=$_POST['prescriberName'];
        $prescriberSpecialty=$_POST['prescriberSpecialty'];
        $team=$_POST['team'];
        $staff=$_POST['staff'];
        $presciberOffice=$_POST['Dpgsd'];
        $sampleDoseReceived=$_POST['smpDate'];
        $provincialHCN=$_POST['provincialHCN'];
        $iprfwc=$_POST['iprfwc'];
        //wc part5
        $patientId = $patientFName[0] . $patientLName[0] . date('YmdHis'); 
         //for DUPIXENT
        //  $stmt = $pdo->prepare("SELECT count(id) as totalCount FROM tblpatientdetails WHERE isDeleted='N'");
        //  $stmt->execute(); // No parameters needed here
        //  $totalCount = $stmt->fetchColumn();
        //  $patientId = 'DUPI-RXC-' . str_pad(($totalCount + 1), 6, '0', STR_PAD_LEFT); // Pad with leading zeros
        $drugNamePrefix = strtoupper(substr($patientDrugName, 0, 4));
        // $totalCount=0;
        // $stmt = $pdo->prepare("SELECT COUNT(tblpatientdetails.id) as totalCount
        //     FROM tblpatientdetails
        //     INNER JOIN tbldrugs ON tblpatientdetails.medicationId = tbldrugs.id
        //     WHERE LEFT(tbldrugs.drugName, 4) = :drugNamePrefix");
        // $stmt->execute(['drugNamePrefix' => $drugNamePrefix]);
        // $totalCount = $stmt->fetchColumn();
         

        // $patientId = $drugNamePrefix.'-RXC-' . str_pad(($totalCount + 1), 6, '0', STR_PAD_LEFT); // Pad with leading zeros
          $stmt = $pdo->prepare("
            SELECT patientId 
              FROM tblpatientdetails 
              INNER JOIN tbldrugs ON tblpatientdetails.medicationId = tbldrugs.id 
              WHERE LEFT(tbldrugs.drugName, 4) = :drugNamePrefix 
              ORDER BY patientId DESC 
              LIMIT 1
        ");
        $stmt->execute(['drugNamePrefix' => $drugNamePrefix]);
        $lastPatientId = $stmt->fetchColumn();
        
        // If no records exist, start with 1; otherwise, increment the last patientId
        if ($lastPatientId) {
            // Extract the numeric part of the last patientId
            $lastNumber = (int) substr($lastPatientId, -6); // Assuming the numeric part is the last 6 digits
            $newNumber = $lastNumber + 1;
        } else {
            // If no records exist, start with 1
            $newNumber = 1;
        }
        // Generate the new patientId
        $patientId = $drugNamePrefix . '-RXC-' . str_pad($newNumber, 6, '0', STR_PAD_LEFT); // Pad with leading zeros
        $fileId = $patientFName[0] . $patientLName[0] . date('YmdHis'); 
        // Handle file uploads
        $uploadedFiles = [];
       
        if (!file_exists('uploads/'.$province.'/'.$krollId)) {
            mkdir('uploads/'.$province.'/'.$krollId, 0777, true);
        }
        $uploadDir = 'uploads/'.$province.'/'.$krollId.'/';
        try {
            $pdo->beginTransaction();
            // Insert patient data
            // $stmt = $pdo->prepare("INSERT INTO tblpatient(krollId,patientName,dob,firstName,lastName,gender,phone,email,address1,address2,city,province,postalCode,notes,createdBy) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            // $result = $stmt->execute([$krollId,$patientName, $dob, $patientFName, $patientLName, $gender, $phone, $email, $address1, $address2, $city, $province, $postalCode,$notes, $createdBy]);
            // $pId = $pdo->lastInsertId();
            
            //Insert patient details
            $stmt = $pdo->prepare("SELECT id as drugId,pspId FROM tbldrugs WHERE drugName = ? and isDeleted='N'");
            $stmt->execute([$patientDrugName]); 
            $drugDetails = $stmt->fetch();
            $pspId=0;
            $drugId='';
            if(isset($drugDetails['pspId'])){
                $pspId=$drugDetails['pspId'];
            }
            if(isset($drugDetails['drugId'])){
                $drugId=$drugDetails['drugId'];
            }
            // update general details for pt in tblpatient
            $stmtupdate = $pdo->prepare("Update tblpatient set krollId=?,patientName=?,firstName=?,lastName=?,phone=?,email=?,address1=?,address2=?,city=?,province=?,postalCode=?,notes=?,modifiedBy=? where id=?");
            $resultupdate = $stmtupdate->execute([$krollId,$patientName, $patientFName, $patientLName, $phone, $email, $address1, $address2, $city, $province, $postalCode,$notes, $createdBy,$pId]);
            
            
            // $stmt1 = $pdo->prepare("INSERT INTO tblpatientdetails(pId, pspId,patientId,fileId,medicationId,createdBy,enrolmentFormReceiveDate) VALUES (?,?,?,?,?,?,?)");
            // $result = $stmt1->execute([$pId, $pspId, $patientId,$fileId, $drugId, $createdBy,$enrolmentFormReceiveDate]);
            
            // Update column 'isPsp' according to 'isRxconnect' in tblpsp - RJ
            // $stmt1 = $pdo->prepare("INSERT INTO tblpatientdetails(pId, pspId,patientId,fileId,medicationId,createdBy,enrolmentFormReceiveDate,isPsp) VALUES (?,?,?,?,?,?,?,(Select isRxconnect from tblpsp where id=?))");
            // $result = $stmt1->execute([$pId, $pspId, $patientId,$fileId, $drugId, $createdBy,$enrolmentFormReceiveDate,$pspId]);
            
            $stmt1 = $pdo->prepare("
                INSERT INTO tblpatientdetails(
                    pId, 
                    pspId,
                    patientId,
                    fileId,
                    medicationId,
                    createdBy,
                    enrolmentFormReceiveDate,
                    isPsp,
                    exPatientId
                )
                VALUES (
                    ?, ?, ?, ?, ?, ?, ?, 
                    (SELECT isRxconnect FROM tblpsp WHERE id = ?),
                    ?
                )
            ");
            $stmt1->bindValue(1, $pId);
            $stmt1->bindValue(2, $pspId);
            $stmt1->bindValue(3, $patientId);
            $stmt1->bindValue(4, $fileId);
            $stmt1->bindValue(5, $drugId);
            $stmt1->bindValue(6, $createdBy);
            $stmt1->bindValue(7, $enrolmentFormReceiveDate);
            $stmt1->bindValue(8, $pspId);
            $stmt1->bindValue(9, $exPatientId, $exPatientId == NULL ? PDO::PARAM_NULL : PDO::PARAM_STR);
            $result = $stmt1->execute();
           
            //INSERT PRESCRIPTION DRUG INFORMATION
            $query5_insert = "INSERT INTO tblwcpart5 (fileId,patientDrugName,dosage,routeOfAdmin,prescriberName,prescriberSpecialty,team,staff,presciberOffice,sampleDoseReceived,iprfwc,pId) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?)";
            $stmt5_insert = $pdo->prepare($query5_insert);
            $stmt5_insert->execute([$fileId,$patientDrugName, $dosage, $routeOfAdmin, $prescriberName, $prescriberSpecialty, $team, $staff,$presciberOffice, $sampleDoseReceived, $iprfwc,$pId]);
              //
              //INSERT PRESCRIPTION DRUG INFORMATION
              $query5_insert = "INSERT INTO tblwcpart2 (fileId,pId,provincialHCN) VALUES (?,?, ?)";
              $stmt5_insert = $pdo->prepare($query5_insert);
              $stmt5_insert->execute([$fileId,$pId, $provincialHCN]);
            //   $wcStatusDueDate = (new DateTime())->modify('+1 day')->format('Y-m-d');
            $today = new DateTime();
            $wcStatusDueDate = $today->modify('+1 day');
            // Check if today is Friday
            if ($today->format('N') == 5) { // 5 represents Friday in ISO-8601 format
                $wcStatusDueDate->modify('+2 days'); // Skip to Monday
            }
            $wcStatusDueDate = $wcStatusDueDate->format('Y-m-d');
            
            if($iprfwc=='O'){
                // welcome call not required - RJ 11102025
                $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate,wcStatus) VALUES (?,?,?,(Select id from tblwelcomecallstatus where title like '%N.A.%' and isDeleted='N' limit 1))";
                $stmt5_insert = $pdo->prepare($query5_insert);
                $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            }
            else{
                $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate) VALUES (?,?, ?)";
                $stmt5_insert = $pdo->prepare($query5_insert);
                $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            }
            
            //   $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate) VALUES (?,?, ?)";
            //   $stmt5_insert = $pdo->prepare($query5_insert);
            //   $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            //custom code
            if(isset($_FILES['files'])){
                // foreach ($_FILES['files']['tmp_name'] as $key => $tmp_name) {
                //     $originalFileName = $_FILES['files']['name'][$key];
                //     $file_tmp = $_FILES['files']['tmp_name'][$key];
                //     $title = $_POST['fileTitle'][$key];
                //     $title = preg_replace('/[^A-Za-z0-9 ]/', '', $title);

                //     // Create a new filename by appending the original file extension to the title
                //     $fileExtension = pathinfo($originalFileName, PATHINFO_EXTENSION);
                //     $file_name = $title . '.' . $fileExtension;
                
                //     $file_destination = $uploadDir . $file_name;
                //     if (move_uploaded_file($file_tmp, $file_destination)) {
                //         $uploadedFiles[] = $file_name;
                //         // Insert into tblpatientdocs
                //         $stmt2 = $pdo->prepare("INSERT INTO tblpatientdocs(pId,fileId, title, filename, module) VALUES (?,?,?,?,?)");
                //         $result2 = $stmt2->execute([$pId,$fileId, $title, $file_name, 'patient']);
                //     } else {
                //         // Handle upload error
                //     }
                // }
                foreach ($_FILES['files']['tmp_name'] as $key => $tmp_name) {
                    $originalFileName = $_FILES['files']['name'][$key];
                    $file_tmp = $_FILES['files']['tmp_name'][$key];
                    if (isset($_POST['fileTitle'][$key])) {
                        $title = $_POST['fileTitle'][$key];
                        $title = preg_replace('/[^A-Za-z0-9 ]/', '', $title); // Clean the title if needed
                
                        // Extract file extension and base name
                        $fileExtension = pathinfo($originalFileName, PATHINFO_EXTENSION);
                        $file_base_name = pathinfo($originalFileName, PATHINFO_FILENAME); // Filename without extension
                        $file_base_name = preg_replace('/[^A-Za-z0-9 ]/', '-', $file_base_name); // Replace invalid characters
                
                        // Generate a truly unique filename
                        $unique_suffix = time() . '-' . mt_rand(1000, 9999); // Timestamp + Random Number
                        $file_name = $file_base_name . '-' . $unique_suffix . '.' . $fileExtension;
                
                        // Define the destination path
                        $file_destination = $uploadDir . $file_name;
                
                        // Ensure unique filename
                        while (file_exists($file_destination)) {
                            $unique_suffix = time() . '-' . mt_rand(1000, 9999); // Regenerate unique suffix
                            $file_name = $file_base_name . '-' . $unique_suffix . '.' . $fileExtension;
                            $file_destination = $uploadDir . $file_name;
                        }
                
                        if (move_uploaded_file($file_tmp, $file_destination)) {
                            $uploadedFiles[] = $file_name;
                
                            // Insert into tblpatientdocs
                            $stmt2 = $pdo->prepare("INSERT INTO tblpatientdocs(pId, fileId, title, filename, module) VALUES (?,?,?,?,?)");
                            $result2 = $stmt2->execute([$pId, $fileId, $title, $file_name, 'patient']);
                        } else {
                            // Handle upload error
                        }
                    }
                }
            }
            $checkresult=$pdo->commit();
            if($checkresult){
            if(isset($_POST['next'])){
                echo 'edit_patient.php?id='.$pId.'&fileId='.$fileId.'&token=2&pspId='.$pspId;
            }else{
                echo 'edit_patient.php?id='.$pId.'&fileId='.$fileId.'&pspId='.$pspId;  
            }
            }else{
                echo 'failed';
            }   
            
        } catch (\PDOException $e) {
            $pdo->rollBack();
            echo "Transaction failed: " . $e->getMessage();
        }
    }
    if($ajaxcall == "addPatientExisting")
    {
        // Handle patient information //*/ /* aaaaa
        // $pspId = $_POST['pspId'];
        // $medicationId = $_POST['medicationId'];
        $pId=$_POST['patientId'];
        $patientFName = $_POST['patientFName'];
        $patientLName = $_POST['patientLName'];
        $patientName = $patientFName . " " . $patientLName;
        $dob = $_POST['dob'];
        $enrolmentFormReceiveDate = $_POST['enrolmentFormReceiveDate'];
        $gender = $_POST['gender'];
        $phone = $_POST['phone'];
        $email = $_POST['email'];
        $address1 = $_POST['address1'];
        $address2 = $_POST['address2'];
        $city = $_POST['city'];
        $province = $_POST['province'];
        $postalCode = $_POST['postalCode'];
        $createdBy = $_SESSION['empId'];
        $notes = $_POST['notes'];
        $krollId = $_POST['krollId'];
        //wc part5
        $patientDrugName=$_POST['patientDrugName'];
        $dosage=$_POST['dose'];
        $routeOfAdmin=$_POST['roa'];
        $prescriberName=$_POST['prescriberName'];
        $prescriberSpecialty=$_POST['prescriberSpecialty'];
        $team=$_POST['team'];
        $staff=$_POST['staff'];
        $presciberOffice=$_POST['Dpgsd'];
        $sampleDoseReceived=$_POST['smpDate'];
        $provincialHCN=$_POST['provincialHCN'];
        $iprfwc=$_POST['iprfwc'];
        //if kroll Id already exist return already exist and die
        //  $stmt = $pdo->prepare("SELECT id FROM tblpatient WHERE krollId = ? AND isDeleted = 'N'");
        // $stmt->execute([$krollId]);
        // $existingPatient = $stmt->fetch();

        // if ($existingPatient) {
        //     echo "Patient with krollId $krollId already exists.";
        //     exit(); // Stop execution if patient exists
        // }
        //Prescriber Address Data
        $AddType=$_POST['AddType'];
        $secondary_email=$_POST['secondary_email'];
        $secondary_fax=$_POST['secondary_fax'];
        $secondary_phone=$_POST['secondary_phone'];
        $secondary_address=$_POST['secondary_address'];
        $secondary_city=$_POST['secondary_city'];
        $secondary_physicianProvince=$_POST['secondary_physicianProvince'];
        $secondary_postalCode=$_POST['secondary_postalCode'];
        $secondary_hospital=$_POST['secondary_hospital'];
        $secondary_notes=$_POST['secondary_notes'];
        $secondary_pID=$_POST['secondary_pID'];
        //consent form data
        $form_id=$_POST['mat'];
        $consent=$_POST['consent'];
        $versionInEnrolmentForm = getMatVersion($form_id);
        
        $indication = $_POST['indication'] ?? '';
        $currentFrequency = $_POST['currentFrequency'];
        $ldosage = $_POST['ldosage'];
        $lfrequency = $_POST['lfrequency'];
        $lroa = $_POST['lroa'];
        
        $exPatientId = $_POST['exPatientId']??NULL;
     
        //consent form data
        //wc part5
         $patientId = $patientFName[0] . $patientLName[0] . date('YmdHis'); 
         //for DUPIXENT
        //  $stmt = $pdo->prepare("SELECT count(id) as totalCount FROM tblpatientdetails WHERE isDeleted='N'");
        //  $stmt->execute(); // No parameters needed here
        //  $totalCount = $stmt->fetchColumn();
        $drugNamePrefix = strtoupper(substr($patientDrugName, 0, 4));
        // $totalCount=0;
        // $stmt = $pdo->prepare("SELECT COUNT(tblpatientdetails.id) as totalCount
        //     FROM tblpatientdetails
        //     INNER JOIN tbldrugs ON tblpatientdetails.medicationId = tbldrugs.id
        //     WHERE LEFT(tbldrugs.drugName, 4) = :drugNamePrefix");
        // $stmt->execute(['drugNamePrefix' => $drugNamePrefix]);
        // $totalCount = $stmt->fetchColumn();

        // $patientId = $drugNamePrefix.'-RXC-' . str_pad(($totalCount + 1), 6, '0', STR_PAD_LEFT); // Pad with leading zeros
        $stmt = $pdo->prepare("
            SELECT patientId 
              FROM tblpatientdetails 
              INNER JOIN tbldrugs ON tblpatientdetails.medicationId = tbldrugs.id 
              WHERE LEFT(tbldrugs.drugName, 4) = :drugNamePrefix 
              ORDER BY patientId DESC 
              LIMIT 1
        ");
        $stmt->execute(['drugNamePrefix' => $drugNamePrefix]);
        $lastPatientId = $stmt->fetchColumn();
        
        // If no records exist, start with 1; otherwise, increment the last patientId
        if ($lastPatientId) {
            // Extract the numeric part of the last patientId
            $lastNumber = (int) substr($lastPatientId, -6); // Assuming the numeric part is the last 6 digits
            $newNumber = $lastNumber + 1;
        } else {
            // If no records exist, start with 1
            $newNumber = 1;
        }
        // Generate the new patientId
        $patientId = $drugNamePrefix . '-RXC-' . str_pad($newNumber, 6, '0', STR_PAD_LEFT); // Pad with leading zeros
        
        $fileId = $patientFName[0] . $patientLName[0] . date('YmdHis'); 
        // Handle file uploads
        $uploadedFiles = [];
       
        if (!file_exists('uploads/'.$province.'/'.$krollId)) {
            mkdir('uploads/'.$province.'/'.$krollId, 0777, true);
        }
        $uploadDir = 'uploads/'.$province.'/'.$krollId.'/';
        try {
            $pdo->beginTransaction();
            // Insert patient data
            // $stmt = $pdo->prepare("INSERT INTO tblpatient(krollId,patientName,dob,firstName,lastName,gender,phone,email,address1,address2,city,province,postalCode,notes,createdBy) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            // $result = $stmt->execute([$krollId,$patientName, $dob, $patientFName, $patientLName, $gender, $phone, $email, $address1, $address2, $city, $province, $postalCode,$notes, $createdBy]);
            // $pId = $pdo->lastInsertId();
            
            //Insert patient details
            $stmt = $pdo->prepare("SELECT id as drugId,pspId FROM tbldrugs WHERE drugName = ? and isDeleted='N'");
            $stmt->execute([$patientDrugName]); 
            $drugDetails = $stmt->fetch();
            $pspId=0;
            $drugId='';
            if(isset($drugDetails['pspId'])){
                $pspId=$drugDetails['pspId'];
            }
            if(isset($drugDetails['drugId'])){
                $drugId=$drugDetails['drugId'];
            }
            
            // update general details for pt in tblpatient
            $stmtupdate = $pdo->prepare("Update tblpatient set krollId=?,patientName=?,firstName=?,lastName=?,phone=?,email=?,address1=?,address2=?,city=?,province=?,postalCode=?,notes=?,modifiedBy=? where id=?");
            $resultupdate = $stmtupdate->execute([$krollId,$patientName, $patientFName, $patientLName, $phone, $email, $address1, $address2, $city, $province, $postalCode,$notes, $createdBy,$pId]);
            
            $stmt1 = $pdo->prepare("INSERT INTO tblpatientdetails(pId, pspId,patientId,fileId,medicationId,createdBy,enrolmentFormReceiveDate) VALUES (?,?,?,?,?,?,?)");
            $result = $stmt1->execute([$pId, $pspId, $patientId,$fileId, $drugId, $createdBy,$enrolmentFormReceiveDate]);
            
            
            // Indication added in below details
            //INSERT PRESCRIPTION DRUG INFORMATION
            $query5_insert = "INSERT INTO tblwcpart5 (fileId,patientDrugName,dosage,routeOfAdmin,prescriberName,prescriberSpecialty,team,staff,presciberOffice,sampleDoseReceived,iprfwc,pId,indication,currentFrequency,ldosage,lfrequency,lrouteOfAdmin) 
            VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?)";
            $stmt5_insert = $pdo->prepare($query5_insert);
            $stmt5_insert->execute([$fileId,$patientDrugName, $dosage, $routeOfAdmin, $prescriberName, $prescriberSpecialty, $team, $staff,$presciberOffice, $sampleDoseReceived, $iprfwc,$pId,$indication,$currentFrequency,$ldosage,$lfrequency,$lroa]);
            //offLabelUse
            //INSERT PRESCRIPTION DRUG INFORMATION
            $query5_insert = "INSERT INTO tblwcpart2 (fileId,pId,provincialHCN) VALUES (?,?, ?)";
            $stmt5_insert = $pdo->prepare($query5_insert);
            $stmt5_insert->execute([$fileId,$pId, $provincialHCN]);
            //
            
            $today = new DateTime();
            $wcStatusDueDate = $today->modify('+1 day');
            // Check if today is Friday
            if ($today->format('N') == 5) { // 5 represents Friday in ISO-8601 format
                $wcStatusDueDate->modify('+2 days'); // Skip to Monday
            }
            $wcStatusDueDate = $wcStatusDueDate->format('Y-m-d');
            
            if($iprfwc=='O'){
                // welcome call not required - RJ 11102025
                $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate,wcStatus) VALUES (?,?,?,(Select id from tblwelcomecallstatus where title like '%N.A.%' and isDeleted='N' limit 1))";
                $stmt5_insert = $pdo->prepare($query5_insert);
                $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            }
            else{
                $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate) VALUES (?,?,?)";
                $stmt5_insert = $pdo->prepare($query5_insert);
                $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            }
            
            // $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate) VALUES (?,?, ?)";
            // $stmt5_insert = $pdo->prepare($query5_insert);
            // $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            //custom code
            $stmt1 = $pdo->prepare("INSERT INTO tblphysiciansaddresshistory(physicianId, hospital,streetAddress,city,province,postalCode,phone,email,fax,notes,pId,physiciansaddressId,createdBy,fileId) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $result = $stmt1->execute([$secondary_pID, $secondary_hospital,$secondary_address, $secondary_city, $secondary_physicianProvince,$secondary_postalCode,$secondary_phone,$secondary_email,$secondary_fax,$secondary_notes,$pId,$AddType,$createdBy,$fileId]);
            //custom code
            if(isset($_FILES['files'])){
               
                foreach ($_FILES['files']['tmp_name'] as $key => $tmp_name) {
                    $originalFileName = $_FILES['files']['name'][$key];
                    $file_tmp = $_FILES['files']['tmp_name'][$key];
                    if (isset($_POST['fileTitle'][$key])) {
                        $title = $_POST['fileTitle'][$key];
                        $title = preg_replace('/[^A-Za-z0-9 ]/', '', $title); // Clean the title if needed
                
                        // Extract file extension and base name
                        $fileExtension = pathinfo($originalFileName, PATHINFO_EXTENSION);
                        $file_base_name = pathinfo($originalFileName, PATHINFO_FILENAME); // Filename without extension
                        $file_base_name = preg_replace('/[^A-Za-z0-9 ]/', '-', $file_base_name); // Replace invalid characters
                
                        // Generate a truly unique filename
                        $unique_suffix = time() . '-' . mt_rand(1000, 9999); // Timestamp + Random Number
                        $file_name = $file_base_name . '-' . $unique_suffix . '.' . $fileExtension;
                
                        // Define the destination path
                        $file_destination = $uploadDir . $file_name;
                
                        // Ensure unique filename
                        while (file_exists($file_destination)) {
                            $unique_suffix = time() . '-' . mt_rand(1000, 9999); // Regenerate unique suffix
                            $file_name = $file_base_name . '-' . $unique_suffix . '.' . $fileExtension;
                            $file_destination = $uploadDir . $file_name;
                        }
                
                        if (move_uploaded_file($file_tmp, $file_destination)) {
                            $uploadedFiles[] = $file_name;
                
                            // Insert into tblpatientdocs
                            $stmt2 = $pdo->prepare("INSERT INTO tblpatientdocs(pId, fileId, title, filename, module) VALUES (?,?,?,?,?)");
                            $result2 = $stmt2->execute([$pId, $fileId, $title, $file_name, 'patient']);
                        } else {
                            // Handle upload error
                        }
                    }
                }
            }
            logOperation($pdo,$createdBy, 'INSERT', 'Patient Table', $patientId,$fileId, 'Existing Patient Added');
            $checkresult=$pdo->commit();
            if($checkresult)
            {
                // check if prescriber is Pilot? if yes execute below insert $prescriberName
                $stmtPhysicianType = $pdo->prepare("Select physicianType from tblphysicians where concat(tblphysicians.firstName,' ',tblphysicians.lastName) =? and tblphysicians.isDeleted='N'");
                $stmtPhysicianType->execute([$prescriberName]);
                $activePhysicianType = $stmtPhysicianType->fetchColumn();
                if(isset($activePhysicianType) && $activePhysicianType=='Pilot')
                {
                    //consent code
                    $status='P';$response='';$resultDate='';
                    $stmtlatestversion = $pdo->prepare("SELECT form_id FROM tblconsentversions where isDeleted='N' ORDER BY STR_TO_DATE(version, '%b %Y') desc limit 1");
                    $stmtlatestversion->execute(); // formid is of the form used to send emails/ differs from id passed in params of URL
                    $active_form_id = $stmtlatestversion->fetchColumn();
                    if($consent=='Y'){ $status='C';$response='Accept';$resultDate = date('Y-m-d H:i:s');}
                    if (strpos($patientDrugName, 'Dupixent') !== false) {
                   
                        $stmt1 = $conn->prepare("INSERT INTO rx_consentFormTransactions(pId, fileId,form_id,versionInEnrolmentForm,emailTo,pspPatientID,fullName,createdBy,status,consentInEnrolment,type,resultDate,response) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                        $result = $stmt1->execute([$pId, $fileId,$active_form_id,$versionInEnrolmentForm, $email,$patientId, $patientName,$createdBy,$status,$consent,'consent',$resultDate,$response]);
                        //consent code
                        echo 'edit_de_patient.php?id='.$pId.'&fileId='.$fileId;
                    }
                    // in-case any another drug for which we dont sent consent notification - mark final status as C
                    else
                    {
                        $stmt1 = $conn->prepare("INSERT INTO rx_consentFormTransactions(pId,fileId,form_id,versionInEnrolmentForm,emailTo,pspPatientID,fullName,createdBy,status,consentInEnrolment,type,resultDate,response) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                        $result = $stmt1->execute([$pId, $fileId,'',$versionInEnrolmentForm, $email,$patientId, $patientName,$createdBy,'C',$consent,'consent',date('Y-m-d H:i:s')]);
                        //consent code
                        echo 'edit_de_patient.php?id='.$pId.'&fileId='.$fileId;
                    }
                }
                // in case patient is added with no pilot md
                if(isset($pId) && isset($fileId) && $pId!="" && $fileId!="")
                {
                    echo 'edit_de_patient.php?id='.$pId.'&fileId='.$fileId;
                }
            }
        } 
        catch (\PDOException $e) 
        {
            $pdo->rollBack();
            echo "Transaction failed: " . $e->getMessage();
        }
    
    }
    if ($ajaxcall == "addPatientNew") {
        // Handle patient information //*/ /* aaaaa
        // $pspId = $_POST['pspId'];
        // $medicationId = $_POST['medicationId'];
        $patientFName = $_POST['patientFName'];
        $patientLName = $_POST['patientLName'];
        $patientName = $patientFName . " " . $patientLName;
        $dob = $_POST['dob'];
        $enrolmentFormReceiveDate = $_POST['enrolmentFormReceiveDate'];
        $gender = $_POST['gender'];
        $phone = $_POST['phone'];
        $email = $_POST['email'];
        $address1 = $_POST['address1'];
        $address2 = $_POST['address2'];
        $city = $_POST['city'];
        $province = $_POST['province'];
        $postalCode = $_POST['postalCode'];
        $createdBy = $_SESSION['empId'];
        $notes = $_POST['notes'];
        $krollId = $_POST['krollId'];
        //wc part5
        $patientDrugName=$_POST['patientDrugName'];
        $dosage=$_POST['dose'];
        $routeOfAdmin=$_POST['roa'];
        $prescriberName=$_POST['prescriberName'];
        $prescriberSpecialty=$_POST['prescriberSpecialty'];
        $team=$_POST['team'];
        $staff=$_POST['staff'];
        $presciberOffice=$_POST['Dpgsd'];
        $sampleDoseReceived=$_POST['smpDate'];
        $provincialHCN=$_POST['provincialHCN'];
        $iprfwc=$_POST['iprfwc'];
        //if kroll Id already exist return already exist and die
         $stmt = $pdo->prepare("SELECT id FROM tblpatient WHERE krollId = ? AND isDeleted = 'N'");
        $stmt->execute([$krollId]);
        $existingPatient = $stmt->fetch();

        if ($existingPatient) {
            echo "Patient with krollId $krollId already exists.";
            exit(); // Stop execution if patient exists
        }
        //Prescriber Address Data
        $AddType=$_POST['AddType'];
        $secondary_email=$_POST['secondary_email'];
        $secondary_fax=$_POST['secondary_fax'];
        $secondary_phone=$_POST['secondary_phone'];
        $secondary_address=$_POST['secondary_address'];
        $secondary_city=$_POST['secondary_city'];
        $secondary_physicianProvince=$_POST['secondary_physicianProvince'];
        $secondary_postalCode=$_POST['secondary_postalCode'];
        $secondary_hospital=$_POST['secondary_hospital'];
        $secondary_notes=$_POST['secondary_notes'];
        $secondary_pID=$_POST['secondary_pID'];
        //consent form data
        $form_id=$_POST['mat'];
        $consent=$_POST['consent'];
        $versionInEnrolmentForm = getMatVersion($form_id);
        
        //additional medication details
        $indication=$_POST['indication'];
        $currentFrequency=$_POST['currentFrequency'];
        $ldosage=$_POST['ldosage'];
        $lfrequency=$_POST['lfrequency'];
        $lrouteOfAdmin=$_POST['lrouteOfAdmin'];
        //additional medication details
        
        $exPatientId=$_POST['exPatientId']??NULL;
        
        //consent form data
        //wc part5
         $patientId = $patientFName[0] . $patientLName[0] . date('YmdHis'); 
         //for DUPIXENT
        //  $stmt = $pdo->prepare("SELECT count(id) as totalCount FROM tblpatientdetails WHERE isDeleted='N'");
        //  $stmt->execute(); // No parameters needed here
        //  $totalCount = $stmt->fetchColumn();
        $drugNamePrefix = strtoupper(substr($patientDrugName, 0, 4));
        // $totalCount=0;
        // $stmt = $pdo->prepare("SELECT COUNT(tblpatientdetails.id) as totalCount
        //     FROM tblpatientdetails
        //     INNER JOIN tbldrugs ON tblpatientdetails.medicationId = tbldrugs.id
        //     WHERE LEFT(tbldrugs.drugName, 4) = :drugNamePrefix");
        // $stmt->execute(['drugNamePrefix' => $drugNamePrefix]);
        // $totalCount = $stmt->fetchColumn();

        // $patientId = $drugNamePrefix.'-RXC-' . str_pad(($totalCount + 1), 6, '0', STR_PAD_LEFT); // Pad with leading zeros
        $stmt = $pdo->prepare("
            SELECT patientId 
              FROM tblpatientdetails 
              INNER JOIN tbldrugs ON tblpatientdetails.medicationId = tbldrugs.id 
              WHERE LEFT(tbldrugs.drugName, 4) = :drugNamePrefix 
              ORDER BY patientId DESC 
              LIMIT 1
        ");
        $stmt->execute(['drugNamePrefix' => $drugNamePrefix]);
        $lastPatientId = $stmt->fetchColumn();
        
        // If no records exist, start with 1; otherwise, increment the last patientId
        if ($lastPatientId) {
            // Extract the numeric part of the last patientId
            $lastNumber = (int) substr($lastPatientId, -6); // Assuming the numeric part is the last 6 digits
            $newNumber = $lastNumber + 1;
        } else {
            // If no records exist, start with 1
            $newNumber = 1;
        }
        // Generate the new patientId
        $patientId = $drugNamePrefix . '-RXC-' . str_pad($newNumber, 6, '0', STR_PAD_LEFT); // Pad with leading zeros
        
        $fileId = $patientFName[0] . $patientLName[0] . date('YmdHis'); 
        // Handle file uploads
        $uploadedFiles = [];
       
        if (!file_exists('uploads/'.$province.'/'.$krollId)) {
            mkdir('uploads/'.$province.'/'.$krollId, 0777, true);
        }
        $uploadDir = 'uploads/'.$province.'/'.$krollId.'/';
        try {
            $pdo->beginTransaction();
            // Insert patient data
            $stmt = $pdo->prepare("INSERT INTO tblpatient(krollId,patientName,dob,firstName,lastName,gender,phone,email,address1,address2,city,province,postalCode,notes,createdBy) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $result = $stmt->execute([$krollId,$patientName, $dob, $patientFName, $patientLName, $gender, $phone, $email, $address1, $address2, $city, $province, $postalCode,$notes, $createdBy]);
            $pId = $pdo->lastInsertId();
            
            //Insert patient details
            $stmt = $pdo->prepare("SELECT id as drugId,pspId FROM tbldrugs WHERE drugName = ? and isDeleted='N'");
            $stmt->execute([$patientDrugName]); 
            $drugDetails = $stmt->fetch();
            $pspId=0;
            $drugId='';
            if(isset($drugDetails['pspId'])){
                $pspId=$drugDetails['pspId'];
            }
            if(isset($drugDetails['drugId'])){
                $drugId=$drugDetails['drugId'];
            }
            // $stmt1 = $pdo->prepare("INSERT INTO tblpatientdetails(pId, pspId,patientId,fileId,medicationId,createdBy,enrolmentFormReceiveDate) VALUES (?,?,?,?,?,?,?)");
            // $result = $stmt1->execute([$pId, $pspId, $patientId,$fileId, $drugId, $createdBy,$enrolmentFormReceiveDate]);
            
            // update 'isPsp' according to 'isRxconnect' from tblpsp - RJ
            // $stmt1 = $pdo->prepare("INSERT INTO tblpatientdetails(pId, pspId,patientId,fileId,medicationId,createdBy,enrolmentFormReceiveDate,isPsp) VALUES (?,?,?,?,?,?,?,(Select isRxconnect from tblpsp where id=?))");
            // $result = $stmt1->execute([$pId, $pspId, $patientId,$fileId, $drugId, $createdBy,$enrolmentFormReceiveDate, $pspId]);
            
            $stmt1 = $pdo->prepare("
                INSERT INTO tblpatientdetails(
                    pId, 
                    pspId,
                    patientId,
                    fileId,
                    medicationId,
                    createdBy,
                    enrolmentFormReceiveDate,
                    isPsp,
                    exPatientId
                )
                VALUES (
                    ?, ?, ?, ?, ?, ?, ?, 
                    (SELECT isRxconnect FROM tblpsp WHERE id = ?),
                    ?
                )
            ");
            $stmt1->bindValue(1, $pId);
            $stmt1->bindValue(2, $pspId);
            $stmt1->bindValue(3, $patientId);
            $stmt1->bindValue(4, $fileId);
            $stmt1->bindValue(5, $drugId);
            $stmt1->bindValue(6, $createdBy);
            $stmt1->bindValue(7, $enrolmentFormReceiveDate);
            $stmt1->bindValue(8, $pspId);
            $stmt1->bindValue(9, $exPatientId, $exPatientId == NULL ? PDO::PARAM_NULL : PDO::PARAM_STR);
            $result = $stmt1->execute();

            //INSERT PRESCRIPTION DRUG INFORMATION
            $query5_insert = "INSERT INTO tblwcpart5 (fileId,patientDrugName,dosage,routeOfAdmin,prescriberName,prescriberSpecialty,team,staff,presciberOffice,sampleDoseReceived,iprfwc,pId,
            indication,currentFrequency,ldosage,lfrequency,lrouteOfAdmin) 
            VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
            ?,?,?,?,?)";
            $stmt5_insert = $pdo->prepare($query5_insert);
            $stmt5_insert->execute([$fileId,$patientDrugName, $dosage, $routeOfAdmin, $prescriberName, $prescriberSpecialty, $team, $staff,$presciberOffice, $sampleDoseReceived, $iprfwc,$pId,
            $indication,$currentFrequency,$ldosage,$lfrequency,$lrouteOfAdmin]);
            //offLabelUse
            //INSERT PRESCRIPTION DRUG INFORMATION
            $query5_insert = "INSERT INTO tblwcpart2 (fileId,pId,provincialHCN) VALUES (?,?, ?)";
            $stmt5_insert = $pdo->prepare($query5_insert);
            $stmt5_insert->execute([$fileId,$pId, $provincialHCN]);
            //
            
            $today = new DateTime();
            $wcStatusDueDate = $today->modify('+1 day');
            // Check if today is Friday
            if ($today->format('N') == 5) { // 5 represents Friday in ISO-8601 format
                $wcStatusDueDate->modify('+2 days'); // Skip to Monday
            }
            $wcStatusDueDate = $wcStatusDueDate->format('Y-m-d');
            
            if($iprfwc=='O'){
                // welcome call not required - RJ 11102025
                $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate,wcStatus) VALUES (?,?,?,(Select id from tblwelcomecallstatus where title like '%N.A.%' and isDeleted='N' limit 1))";
                $stmt5_insert = $pdo->prepare($query5_insert);
                $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            }
            else{
                $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate) VALUES (?,?,?)";
                $stmt5_insert = $pdo->prepare($query5_insert);
                $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            }

            // $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate) VALUES (?,?, ?)";
            // $stmt5_insert = $pdo->prepare($query5_insert);
            // $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            //custom code
            $stmt1 = $pdo->prepare("INSERT INTO tblphysiciansaddresshistory(physicianId, hospital,streetAddress,city,province,postalCode,phone,email,fax,notes,pId,physiciansaddressId,createdBy,fileId) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $result = $stmt1->execute([$secondary_pID, $secondary_hospital,$secondary_address, $secondary_city, $secondary_physicianProvince,$secondary_postalCode,$secondary_phone,$secondary_email,$secondary_fax,$secondary_notes,$pId,$AddType,$createdBy,$fileId]);
            //custom code
            if(isset($_FILES['files'])){
               
                foreach ($_FILES['files']['tmp_name'] as $key => $tmp_name) 
                {
                    $originalFileName = $_FILES['files']['name'][$key];
                    $file_tmp = $_FILES['files']['tmp_name'][$key];
                    if (isset($_POST['fileTitle'][$key])) {
                        $title = $_POST['fileTitle'][$key];
                        $title = preg_replace('/[^A-Za-z0-9 ]/', '', $title); // Clean the title if needed
                
                        // Extract file extension and base name
                        $fileExtension = pathinfo($originalFileName, PATHINFO_EXTENSION);
                        $file_base_name = pathinfo($originalFileName, PATHINFO_FILENAME); // Filename without extension
                        $file_base_name = preg_replace('/[^A-Za-z0-9 ]/', '-', $file_base_name); // Replace invalid characters
                
                        // Generate a truly unique filename
                        $unique_suffix = time() . '-' . mt_rand(1000, 9999); // Timestamp + Random Number
                        $file_name = $file_base_name . '-' . $unique_suffix . '.' . $fileExtension;
                
                        // Define the destination path
                        $file_destination = $uploadDir . $file_name;
                
                        // Ensure unique filename
                        while (file_exists($file_destination)) {
                            $unique_suffix = time() . '-' . mt_rand(1000, 9999); // Regenerate unique suffix
                            $file_name = $file_base_name . '-' . $unique_suffix . '.' . $fileExtension;
                            $file_destination = $uploadDir . $file_name;
                        }
                
                        if (move_uploaded_file($file_tmp, $file_destination)) {
                            $uploadedFiles[] = $file_name;
                
                            // Insert into tblpatientdocs
                            $stmt2 = $pdo->prepare("INSERT INTO tblpatientdocs(pId, fileId, title, filename, module) VALUES (?,?,?,?,?)");
                            $result2 = $stmt2->execute([$pId, $fileId, $title, $file_name, 'patient']);
                        } else {
                            // Handle upload error
                        }
                    }
                }
            }
            logOperation($pdo,$createdBy, 'INSERT', 'Patient Table', $pId,$fileId, 'New Patient Added');
            $checkresult=$pdo->commit();
            if($checkresult)
            {
                // check if prescriber is Pilot? if yes execute below insert $prescriberName
                $stmtPhysicianType = $pdo->prepare("Select physicianType from tblphysicians where concat(tblphysicians.firstName,' ',tblphysicians.lastName) =? and tblphysicians.isDeleted='N'");
                $stmtPhysicianType->execute([$prescriberName]);
                $activePhysicianType = $stmtPhysicianType->fetchColumn();
                if(isset($activePhysicianType) && $activePhysicianType=='Pilot')
                {
                    //consent code
                    $status='P';$response='';$resultDate='';
                    $stmtlatestversion = $pdo->prepare("SELECT form_id FROM tblconsentversions where isDeleted='N' ORDER BY STR_TO_DATE(version, '%b %Y') desc limit 1");
                    $stmtlatestversion->execute(); // formid is of the form used to send emails/ differs from id passed in params of URL
                    $active_form_id = $stmtlatestversion->fetchColumn();
                    if($consent=='Y')
                    {
                        $status='C';$response='Accept';$resultDate = date('Y-m-d H:i:s');
                    }
                    if (strpos($patientDrugName, 'Dupixent') !== false) {
                        $stmt1 = $conn->prepare("INSERT INTO rx_consentFormTransactions(pId, fileId,form_id,versionInEnrolmentForm,emailTo,pspPatientID,fullName,createdBy,status,consentInEnrolment,type,resultDate,response) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                        $result = $stmt1->execute([$pId, $fileId,$active_form_id,$versionInEnrolmentForm, $email,$patientId, $patientName,$createdBy,$status,$consent,'consent',$resultDate,$response]);
                        //consent code
                        echo 'edit_de_patient.php?id='.$pId.'&fileId='.$fileId;
                    }
                    // in-case any another drug for which we dont sent consent notification - mark final status as C
                    else
                    {
                        $stmt1 = $conn->prepare("INSERT INTO rx_consentFormTransactions(pId,fileId,form_id,versionInEnrolmentForm,emailTo,pspPatientID,fullName,createdBy,status,consentInEnrolment,type,resultDate,response) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                        $result = $stmt1->execute([$pId, $fileId,'',$versionInEnrolmentForm, $email,$patientId, $patientName,$createdBy,'C',$consent,'consent',date('Y-m-d H:i:s')]);
                        //consent code
                        echo 'edit_de_patient.php?id='.$pId.'&fileId='.$fileId;
                    }
                }
                // if  pt is non pilot
                else{
                      echo 'edit_de_patient.php?id='.$pId.'&fileId='.$fileId;
                }
            }
        } 
        catch (\PDOException $e) 
        {
            $pdo->rollBack();
            echo "Transaction failed: " . $e->getMessage();
        }
    }
    if ($ajaxcall == "addPatient") {
        // Handle patient information //*/ /* aaaaa
        // $pspId = $_POST['pspId'];
        // $medicationId = $_POST['medicationId'];
        $patientFName = $_POST['patientFName'];
        $patientLName = $_POST['patientLName'];
        $patientName = $patientFName . " " . $patientLName;
        $dob = $_POST['dob'];
        $enrolmentFormReceiveDate = $_POST['enrolmentFormReceiveDate'];
        $gender = $_POST['gender'];
        $phone = $_POST['phone'];
        $email = $_POST['email'];
        $address1 = $_POST['address1'];
        $address2 = $_POST['address2'];
        $city = $_POST['city'];
        $province = $_POST['province'];
        $postalCode = $_POST['postalCode'];
        $createdBy = $_SESSION['empId'];
        $notes = $_POST['notes'];
        $krollId = $_POST['krollId'];
        //wc part5
        $patientDrugName=$_POST['patientDrugName'];
        $dosage=$_POST['dose'];
        $routeOfAdmin=$_POST['roa'];
        $prescriberName=$_POST['prescriberName'];
        $prescriberSpecialty=$_POST['prescriberSpecialty'];
        $team=$_POST['team'];
        $staff=$_POST['staff'];
        $presciberOffice=$_POST['Dpgsd'];
        $sampleDoseReceived=$_POST['smpDate'];
        $provincialHCN=$_POST['provincialHCN'];
        $iprfwc=$_POST['iprfwc'];
        //if kroll Id already exist return already exist and die
         $stmt = $pdo->prepare("SELECT id FROM tblpatient WHERE krollId = ? AND isDeleted = 'N'");
        $stmt->execute([$krollId]);
        $existingPatient = $stmt->fetch();

        if ($existingPatient) {
            echo "Patient with krollId $krollId already exists.";
            exit(); // Stop execution if patient exists
        }
        
        //wc part5
         $patientId = $patientFName[0] . $patientLName[0] . date('YmdHis'); 
         //for DUPIXENT
        //  $stmt = $pdo->prepare("SELECT count(id) as totalCount FROM tblpatientdetails WHERE isDeleted='N'");
        //  $stmt->execute(); // No parameters needed here
        //  $totalCount = $stmt->fetchColumn();
        $drugNamePrefix = strtoupper(substr($patientDrugName, 0, 4));
        // $totalCount=0;
        // $stmt = $pdo->prepare("SELECT COUNT(tblpatientdetails.id) as totalCount
        //     FROM tblpatientdetails
        //     INNER JOIN tbldrugs ON tblpatientdetails.medicationId = tbldrugs.id
        //     WHERE LEFT(tbldrugs.drugName, 4) = :drugNamePrefix");
        // $stmt->execute(['drugNamePrefix' => $drugNamePrefix]);
        // $totalCount = $stmt->fetchColumn();

        // $patientId = $drugNamePrefix.'-RXC-' . str_pad(($totalCount + 1), 6, '0', STR_PAD_LEFT); // Pad with leading zeros
        
        $stmt = $pdo->prepare("
            SELECT patientId 
              FROM tblpatientdetails 
              INNER JOIN tbldrugs ON tblpatientdetails.medicationId = tbldrugs.id 
              WHERE LEFT(tbldrugs.drugName, 4) = :drugNamePrefix 
              ORDER BY patientId DESC 
              LIMIT 1
        ");
        $stmt->execute(['drugNamePrefix' => $drugNamePrefix]);
        $lastPatientId = $stmt->fetchColumn();
        
        // If no records exist, start with 1; otherwise, increment the last patientId
        if ($lastPatientId) {
            // Extract the numeric part of the last patientId
            $lastNumber = (int) substr($lastPatientId, -6); // Assuming the numeric part is the last 6 digits
            $newNumber = $lastNumber + 1;
        } else {
            // If no records exist, start with 1
            $newNumber = 1;
        }
        // Generate the new patientId
        $patientId = $drugNamePrefix . '-RXC-' . str_pad($newNumber, 6, '0', STR_PAD_LEFT); // Pad with leading zeros
        
        $fileId = $patientFName[0] . $patientLName[0] . date('YmdHis'); 
        // Handle file uploads
        $uploadedFiles = [];
       
        if (!file_exists('uploads/'.$province.'/'.$krollId)) {
            mkdir('uploads/'.$province.'/'.$krollId, 0777, true);
        }
        $uploadDir = 'uploads/'.$province.'/'.$krollId.'/';
        try {
            $pdo->beginTransaction();
            // Insert patient data
            $stmt = $pdo->prepare("INSERT INTO tblpatient(krollId,patientName,dob,firstName,lastName,gender,phone,email,address1,address2,city,province,postalCode,notes,createdBy) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $result = $stmt->execute([$krollId,$patientName, $dob, $patientFName, $patientLName, $gender, $phone, $email, $address1, $address2, $city, $province, $postalCode,$notes, $createdBy]);
            $pId = $pdo->lastInsertId();
            
            //Insert patient details
            $stmt = $pdo->prepare("SELECT id as drugId,pspId FROM tbldrugs WHERE drugName = ? and isDeleted='N'");
            $stmt->execute([$patientDrugName]); 
            $drugDetails = $stmt->fetch();
            $pspId=0;
            $drugId='';
            if(isset($drugDetails['pspId'])){
                $pspId=$drugDetails['pspId'];
            }
            if(isset($drugDetails['drugId'])){
                $drugId=$drugDetails['drugId'];
            }
            $stmt1 = $pdo->prepare("INSERT INTO tblpatientdetails(pId, pspId,patientId,fileId,medicationId,createdBy,enrolmentFormReceiveDate) VALUES (?,?,?,?,?,?,?)");
            $result = $stmt1->execute([$pId, $pspId, $patientId,$fileId, $drugId, $createdBy,$enrolmentFormReceiveDate]);

            //INSERT PRESCRIPTION DRUG INFORMATION
            $query5_insert = "INSERT INTO tblwcpart5 (fileId,patientDrugName,dosage,routeOfAdmin,prescriberName,prescriberSpecialty,team,staff,presciberOffice,sampleDoseReceived,iprfwc,pId) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt5_insert = $pdo->prepare($query5_insert);
            $stmt5_insert->execute([$fileId,$patientDrugName, $dosage, $routeOfAdmin, $prescriberName, $prescriberSpecialty, $team, $staff,$presciberOffice, $sampleDoseReceived, $iprfwc,$pId]);
            //offLabelUse
            //INSERT PRESCRIPTION DRUG INFORMATION
            $query5_insert = "INSERT INTO tblwcpart2 (fileId,pId,provincialHCN) VALUES (?,?, ?)";
            $stmt5_insert = $pdo->prepare($query5_insert);
            $stmt5_insert->execute([$fileId,$pId, $provincialHCN]);
            //
            $today = new DateTime();
            $wcStatusDueDate = $today->modify('+1 day');
            // Check if today is Friday
            if ($today->format('N') == 5) { // 5 represents Friday in ISO-8601 format
                $wcStatusDueDate->modify('+2 days'); // Skip to Monday
            }
            $wcStatusDueDate = $wcStatusDueDate->format('Y-m-d');
            
            if($iprfwc=='O'){
                // welcome call not required - RJ 11102025
                $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate,wcStatus) VALUES (?,?,?,(Select id from tblwelcomecallstatus where title like '%N.A.%' and isDeleted='N' limit 1))";
                $stmt5_insert = $pdo->prepare($query5_insert);
                $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            }
            else{
                $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate) VALUES (?,?,?)";
                $stmt5_insert = $pdo->prepare($query5_insert);
                $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            }
            
            // $query5_insert = "INSERT INTO tblwcpart1 (fileId,pId,wcStatusDueDate) VALUES (?,?, ?)";
            // $stmt5_insert = $pdo->prepare($query5_insert);
            // $stmt5_insert->execute([$fileId,$pId, $wcStatusDueDate]);
            //custom code
            if(isset($_FILES['files'])){
               
                foreach ($_FILES['files']['tmp_name'] as $key => $tmp_name) {
                    $originalFileName = $_FILES['files']['name'][$key];
                    $file_tmp = $_FILES['files']['tmp_name'][$key];
                    if (isset($_POST['fileTitle'][$key])) {
                        $title = $_POST['fileTitle'][$key];
                        $title = preg_replace('/[^A-Za-z0-9 ]/', '', $title); // Clean the title if needed
                
                        // Extract file extension and base name
                        $fileExtension = pathinfo($originalFileName, PATHINFO_EXTENSION);
                        $file_base_name = pathinfo($originalFileName, PATHINFO_FILENAME); // Filename without extension
                        $file_base_name = preg_replace('/[^A-Za-z0-9 ]/', '-', $file_base_name); // Replace invalid characters
                
                        // Generate a truly unique filename
                        $unique_suffix = time() . '-' . mt_rand(1000, 9999); // Timestamp + Random Number
                        $file_name = $file_base_name . '-' . $unique_suffix . '.' . $fileExtension;
                
                        // Define the destination path
                        $file_destination = $uploadDir . $file_name;
                
                        // Ensure unique filename
                        while (file_exists($file_destination)) {
                            $unique_suffix = time() . '-' . mt_rand(1000, 9999); // Regenerate unique suffix
                            $file_name = $file_base_name . '-' . $unique_suffix . '.' . $fileExtension;
                            $file_destination = $uploadDir . $file_name;
                        }
                
                        if (move_uploaded_file($file_tmp, $file_destination)) {
                            $uploadedFiles[] = $file_name;
                
                            // Insert into tblpatientdocs
                            $stmt2 = $pdo->prepare("INSERT INTO tblpatientdocs(pId, fileId, title, filename, module) VALUES (?,?,?,?,?)");
                            $result2 = $stmt2->execute([$pId, $fileId, $title, $file_name, 'patient']);
                        } else {
                            // Handle upload error
                        }
                    }
                }
            }
            logOperation($pdo,$createdBy, 'INSERT', 'Patient Table', $pId,$fileId, 'New Patient Added');
            $checkresult=$pdo->commit();
            if($checkresult){
                echo 'edit_de_patient.php?id='.$pId.'&fileId='.$fileId;
            }else{
                echo 'failed';
            }
            
        } catch (\PDOException $e) {
            $pdo->rollBack();
            echo "Transaction failed: " . $e->getMessage();
        }
    }
    
    if($ajaxcall == 4){
        $id = $_REQUEST['id'];
        $comments = $_REQUEST['c'];

        $stmt= $pdo->prepare("UPDATE tblpaymentdetails SET comments = ? WHERE id = ?");
        $result = $stmt->execute([$comments, $id]);

        if($result){
            echo 'Done';
        }
        else{
            echo 'Issue';
        }
    }

    if($ajaxcall == 5){
        $pspPaymentMethod = $_REQUEST['pm'];
        $pspPayment = $_REQUEST['pp'];
        $pspPaymentDetails = $_REQUEST['pd'];
        $paymentDetailId = $_REQUEST['p'];
        $discrepancy = $_REQUEST['d'];

        $stmt = $pdo->prepare("UPDATE tblpaymentdetails SET pspPayment = ?, pspPaymentMethod = ?, pspCAR = ?, discrepancy = ? WHERE id = ?");
        $result = $stmt->execute([$pspPayment, $pspPaymentMethod, $pspPaymentDetails, $discrepancy, $paymentDetailId]);

        if($result){
            echo 'Done';
        }
        else{
            echo 'Issue';
        }
    }

    if($ajaxcall == 6){
        $trilliumPaymentMethod = $_REQUEST['tm'];
        $trilliumPayment = $_REQUEST['tp'];
        $trilliumPaymentDetails = $_REQUEST['td'];
        $paymentDetailId = $_REQUEST['p'];
        $discrepancy = $_REQUEST['d'];

        $stmt = $pdo->prepare("UPDATE tblpaymentdetails SET trilliumPayment = ?, trilliumPaymentMethod = ?, trilliumCAR = ?, discrepancy = ? WHERE id = ?");
        $result = $stmt->execute([$trilliumPayment, $trilliumPaymentMethod, $trilliumPaymentDetails, $discrepancy, $paymentDetailId]);

        if($result){
            echo 'Done';
        }
        else{
            echo 'Issue';
        }
    }
    
    if($ajaxcall == 7){
        $patientPaymentMethod = $_REQUEST['ptm'];
        $patientPayment = $_REQUEST['ptp'];
        $patientPaymentDetails = $_REQUEST['ptd'];
        $paymentDetailId = $_REQUEST['p'];
        $discrepancy = $_REQUEST['d'];

        $stmt = $pdo->prepare("UPDATE tblpaymentdetails SET patientPayment = ?, patientPaymentMethod = ?, patientCAR = ?, discrepancy = ? WHERE id = ?");
        $result = $stmt->execute([$patientPayment, $patientPaymentMethod, $patientPaymentDetails, $discrepancy, $paymentDetailId]);

        if($result){
            echo 'Done';
        }
        else{
            echo 'Issue';
        }
    }

    if($ajaxcall == 8){
        $id = $_REQUEST['id'];
        $fax = $_REQUEST['f'];
        $userId = $_REQUEST['u'];

        $stmt = $pdo->prepare("SELECT * FROM tbluser WHERE empId = ?");
        $stmt->execute([$userId]); 
        $empDetails = $stmt->fetch();

        $userName =  explode(' ', $empDetails['userName']);
        $firstName = substr($userName[0], 0, 1);
        $lastName =  substr($userName[1], 0, 1);

        if($fax == 1){
            $firstInitials = $firstName.''.$lastName;
            $firstFaxed = date("Y-m-d");
            $stmt = $pdo->prepare("UPDATE tblpatientdetails SET firstFaxed = ?, firstInitials = ? WHERE id = ?");
            $result = $stmt->execute([$firstFaxed, $firstInitials, $id]);
        } 
        else if($fax == 2){
            $secondInitials = $firstName.''.$lastName;
            $secondFaxed = date("Y-m-d");

            $stmt = $pdo->prepare("UPDATE tblpatientdetails SET secondFaxed = ?, secondInitials = ? WHERE id = ?");
            $result = $stmt->execute([$secondFaxed, $secondInitials, $id]);
        } 
        else if($fax == 3){
            $thirdInitials = $firstName.''.$lastName;
            $thirdFaxed = date("Y-m-d");

            $stmt = $pdo->prepare("UPDATE tblpatientdetails SET thirdFaxed = ?, thirdInitials = ? WHERE id = ?");
            $result = $stmt->execute([$thirdFaxed, $thirdInitials, $id]);
        }

        if($result){
            header("location:receipt_report.php?id=".$id); 
        }
        else{
            echo 'Issue';
        }
    }

    if($ajaxcall == 9){
        $patientDetailId = $_REQUEST['id'];

        $data = "<select id='status".$patientDetailId."'>";
        $data .= "<option value='4'>Incomplete</option>";
        $data .= "<option value='5'>Complete</option>";
        $data .= "</select>";

        echo $data;
    }

    if($ajaxcall == 10){
        $patientDetailId = $_REQUEST['id'];
        $selStatus = $_REQUEST['status'];

        $stmt= $pdo->prepare("UPDATE tblpatientdetails SET status = ? WHERE id = ?");
        if($stmt->execute([$selStatus, $patientDetailId])){

            if($selStatus == 4) { $status = 'Incomplete'; }
            if($selStatus == 5) { $status = 'Complete'; }
            
            $data = "<span id='status$patientDetailId'>$status</span>";
        }
        else{
            $flag = 1;
        }

        echo $data;
    }
    $ajaxcall = $_REQUEST['ajaxcall'];

if ($ajaxcall == 11) {
   print_r($_REQUEST);
}



