<?php
// print_r("hello");die();

// Initialize the cron error array to collect all errors
$cron_error[] = [];

// --- Authentication and Access Control (uncomment and use as needed) ---
// if (
//     !isset($postdata['api_signature']) or !checkIfIssetAndNotEmpty($postdata['api_signature']) or
//     !isset($postdata['function_type']) or !checkIfIssetAndNotEmpty($postdata['function_type']) or
//     !isset($postdata['jwt_token']) or !checkIfIssetAndNotEmpty($postdata['jwt_token'])
// ) {
//     $cron_error[] = "All * fields are required.";
// }
// if (!validateApiSignature($postdata['api_signature'])) {
//     $cron_error[] = "Invalid API signature.";
// }
// $jwttoken = $postdata['jwt_token'];
// $jwt_validate = authenticate_jwt($jwttoken);
// if (!$jwt_validate) {
//     $cron_error[] = "Invalid JWT token.";
// } else {
//     $userid = $jwt_validate->data->id;
//     $workspace_id = $jwt_validate->data->workspace_id;
//     $username = $jwt_validate->data->username;
//     $role = $jwt_validate->data->role;
//     if ($role == 'principal' or $role == 'teacher') {
//         $institution_id = $jwt_validate->data->institution_id;
//         $branch_id = $jwt_validate->data->branch_id;
//     }
// }

// Use a mock institution_id for demonstration if not authenticated
$institution_id = 2;

$specific_db_name = validateUserAccess($conn_rjllearning, $userid, $role, $workspace_id, ["superadmin", "admin", "principal", "teacher"]);
if (!$specific_db_name) {
    $cron_error[] = 'Access denied.';
} else {
    $conn_rjllearning_specific = dynamicDbConnection($specific_db_name);
}

// Check for authentication errors before proceeding
// if (!empty($cron_error[])) {
//     errorreturn(implode(', ', $cron_error[]));
// }
 function getOtherStaffGroupInfo($conn, $institution_id, $branch_id, $group_name) {
        // Prepare and execute the query to find the staff group by name
        $sql_staff_groups = "SELECT id, group_name FROM other_staff_groups WHERE group_name = ? AND institution_id = ? AND branch_id LIKE ?";
        $stmt_staff_groups = $conn->prepare($sql_staff_groups);
        $stmt_staff_groups->execute(array($group_name, $institution_id, '%' . $branch_id . '%'));
        $staff_group = $stmt_staff_groups->fetch(PDO::FETCH_ASSOC);
    
        // If a staff group is found, return its details
        if ($staff_group) {
            return [
                'id' => $staff_group['id'],
                'group_name' => $staff_group['group_name']
            ];
        } else {
            // If not found, return an error
             errorreturn("Staff group does not exist: " . $group_name);
        }
}
            
if ($postdata['function_type'] == "import_faculty") {
    // print_r($_FILES['file']);die();
    // if (isset($_FILES['file']) && $_FILES['file']['error'] == UPLOAD_ERR_OK) 
    {
        $insert_data = [];
        // $fileName = $_FILES['file']['name'];
        // $fileSize = $_FILES['file']['size'];
        // $fileTemp = $_FILES['file']['tmp_name'];
        // $fileExtension = pathinfo($fileName, PATHINFO_EXTENSION);
        // $allowedExtensions = ["xlsx", "xls"];

        // if (!in_array($fileExtension, $allowedExtensions)) {
        //     $cron_error[] = "Only XLSX and XLS files are allowed.";
        // }
        // $maxFileSize = 10 * 1024 * 1024;
        // if ($fileSize > $maxFileSize) {
        //     $cron_error[] = 'File size exceeds the maximum allowed limit (10 MB).';
        // }
        
        $cron_error = [];   
        // If initial file validation errors are found, stop here and return them.
        // if (!empty($cron_error[])) {
        //     errorreturn(implode(', ', $cron_error[]));
        // }
        
        $fullfilePath = $base_path_to_learning . $filePath;
        
        try {
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fullfilePath);
            $worksheet = $spreadsheet->getSheet(0);

            // To store row-specific errors
            $row_errors = [];
            
             // Prepare state, country
            $stmt_state = $conn_rjllearning->prepare("SELECT id FROM state_info WHERE state = ? AND status = 1");
            $stmt_country = $conn_rjllearning->prepare("SELECT id FROM country_info WHERE name = ? AND status = 1");
            
            foreach ($worksheet->getRowIterator() as $rowNumber => $row) {
          
                if ($rowNumber == 1) continue;
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(true); // ✅ Only iterate through existing cells

                $rowData = [];
                foreach ($cellIterator as $cell) {
                    $rowData[] = trim((string)$cell->getValue()); // ✅ Trim and convert to string
                }
               
                if (empty(array_filter($rowData))) continue;
                // print_R($rowData);die();
                $facultyData = [
                    "Branch" => $rowData[0] ?? '',
                    "Name" => $rowData[1] ?? '',
                    "Gender" => $rowData[2] ?? '',
                    "Email" => $rowData[3] ?? '',
                    "Mobile" => $rowData[4] ?? '',
                    "Designation" => $rowData[5] ?? '',
                    "Role" => $rowData[6] ?? '',
                    "Father Name" => $rowData[7] ?? '',
                    "Mother Name" => $rowData[8] ?? '',
                    "Spouse Name" => $rowData[9] ?? '',
                    "Nationality" => $rowData[10] ?? '',
                    "DOB" => $rowData[11] ?? '',
                    "Address" => $rowData[12] ?? '',
                    "City" => $rowData[13] ?? '',
                    "State" => $rowData[14] ?? '',
                    "Pincode" => $rowData[15] ?? '',
                    "Country" => $rowData[16] ?? '',
                    "Date of Joining" => $rowData[17] ?? '',
                    "Registered on EPF" => $rowData[18] ?? '',      
                    "Date of Joining as per Registration" => $rowData[19] ?? '',
                    "Account Name" => $rowData[20] ?? '',
                    "Account Holder Name" => $rowData[21] ?? '',
                    "Account Number" => $rowData[22] ?? '',
                    "IFSC Code" => $rowData[23] ?? '',
                    "Branch Address" => $rowData[24] ?? '',
                    "Account Type" => $rowData[25] ?? '',
                    "Aadhaar Card" => $rowData[26] ?? '',
                    "PAN Card" => $rowData[27] ?? '',
                    "Driving License" => $rowData[28] ?? '',
                    "Driving License Expiry Date" => $rowData[29] ?? '',
                    "Other Staff Groups" => $rowData[30] ?? ''
                ];
             
                // Validate and store errors in a temporary array for the current row
                $current_row_errors = [];

                // execute prepare statement
                $stmt_state->execute([$facultyData['State']]);
                $state_id = $stmt_state->fetchColumn();
                if (!$state_id) {
                    $cron_error[] = "Invalid state: " . $facultyData['State'];
                }

                
                $stmt_country->execute([$facultyData['Country']]);
                $country_id = $stmt_country->fetchColumn();
                if (!$country_id) {
                    $cron_error[] = "Invalid country: " . $facultyData['Country'];
                }
                
                // $stmt = $conn_rjllearning->prepare("SELECT id FROM state_info WHERE LOWER(state) = LOWER(?) AND status = 1");
                // $stmt->execute([strtolower($facultyData['State'])]);
                // $state_id = $stmt->fetchColumn();
                // if (!$state_id) {
                //     $cron_error[] = "Invalid state: " . $facultyData['State'];
                // }
                
                // $stmt = $conn_rjllearning->prepare("SELECT id FROM country_info WHERE LOWER(name) = LOWER(?) AND status = 1");
                // $stmt->execute([strtolower($facultyData['Country'])]);
                // $country_id = $stmt->fetchColumn();
                // if (!$country_id) {
                //     $cron_error[] = "Invalid country: " . $facultyData['Country'];
                // }
                
                if (empty($facultyData["Name"]) || empty($facultyData["Email"]) || empty($facultyData["Mobile"])) {
                    $cron_error[] = "Missing required fields (Name, Email, Mobile).";
                }
                
                if (!filter_var($facultyData["Email"], FILTER_VALIDATE_EMAIL)) {
                    $cron_error[] = "Invalid email address.";
                }
                
                if (!preg_match('/^\d{10}$/', $facultyData["Mobile"])) {
                    $cron_error[] = "Invalid mobile number.";
                }
                 $facultyData['mobile']  = "+91" . $facultyData["Mobile"];
                
                if (!CheckAlphabetssWithSpace($facultyData["Father Name"])) {
                    $cron_error[] = "Invalid Father Name.";
                }
                if (!CheckAlphabetssWithSpace($facultyData["Mother Name"])) {
                    $cron_error[] = "Invalid Mother Name.";
                }
                
                if (!empty($facultyData["Spouse Name"]) && !CheckAlphabetssWithSpace($facultyData["Spouse Name"])) {
                    $cron_error[] = "Invalid Spouse Name.";
                }
                
                if (!CheckAddressValidation($facultyData["Address"])) {
                    $cron_error[] = "Invalid address.";
                }
                
                if (!PinCodeValidation($facultyData["Pincode"])) {
                    $cron_error[] = "Invalid Pin Code.";
                }
                
                if (!CheckEMailIsValidOrNot($facultyData["Email"])) {
                    $cron_error[] = "Enter valid Email Id.";
                }
                
                if (!checkOnlyCharacters($facultyData["Name"])) {
                    $cron_error[] = "Name must only contain letters!";
                }
                
                if (strlen($facultyData["mobile"]) != 13 || $facultyData["mobile"] <= 0) {
                    $cron_error[] = "Enter valid 12 digit mobile number: " . $facultyData["mobile"];
                }
                
                if ($facultyData["Gender"] != 'M' && $facultyData["Gender"] != 'F') {
                    $cron_error[] = "Invalid gender details.";
                }
                $date_of_birth = $facultyData["DOB"];
                $date_now = date("Y-m-d");
                $facultyData["dob"] = '';
                if (is_numeric($date_of_birth)) {
                    $excelBaseDate = 25569;
                    $unixTimestamp = ($date_of_birth - $excelBaseDate) * 86400;
                    $facultyData["dob"] = date("Y-m-d", $unixTimestamp);
                } else {
                    if (!strtotime($date_of_birth)) {
                        $cron_error[] = "Invalid date format. Expected format: dd-mm-yyyy. Received: " . $date_of_birth;
                    } else {
                         $facultyData["dob"]  = date('Y-m-d', strtotime($date_of_birth));
                    }
                }
                // print_r($facultyData['dob']);die();
                // if (!CheckDob($facultyData['DOB']) || $facultyData['DOB'] >= $date_now) {
                //     $cron_error[] = "Invalid DOB format or DOB cannot be today/future date.";
                // }
                Unset($facultyData["DOB"]);
                Unset($facultyData["Mobile"]);
                $dobDate = DateTime::createFromFormat('Y-m-d',  $facultyData["dob"]);
                $currentDate = new DateTime();
                $minimumAge = 18;
                if ($dobDate && $dobDate <= $currentDate) {
                    $age = $dobDate->diff($currentDate)->y;
                    if ($age < $minimumAge) {
                        $cron_error[] = "Registration denied. Person must be at least $minimumAge years old.";
                    }
                }
               
                if ($facultyData["Driving License"] != null && $facultyData["Driving License Expiry Date"] == null) {
                    $cron_error[] = "Driving expiry date is required.";
                }
               if (!empty($facultyData['Registered on EPF'])) {
                    if ($facultyData['Registered on EPF'] != '0' && $facultyData['Registered on EPF'] != '1') {
                        $cron_error[] = "Invalid value for 'Registered on EPF'. Must be 0 or 1.";
                    }
                }
                // Date of Joining as per Registration: Check if it's a valid date if not empty
                if (!empty($facultyData['Date of Joining as per Registration'])) {
                    $date = @\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($facultyData['Date of Joining as per Registration']);
                    if (!$date) {
                        $cron_error[] = "Invalid date format for 'Date of Joining as per Registration'.";
                    } else {
                        $facultyData['Date of Joining as per Registration'] = $date->format('Y-m-d');
                    }
                }
                // Date of Joining as per Registration: null if empty, else keep value
                $facultyData['Date of Joining as per Registration'] = !empty($facultyData['Date of Joining as per Registration']) ? $facultyData['Date of Joining as per Registration'] : null;
                
                if ($facultyData["Account Type"] == '1' && $facultyData["Account Type"] != null) {
                    if (empty($facultyData["Account Name"]) || !preg_match('/^[A-Za-z\s]+$/', $facultyData["Account Name"])) {
                        $cron_error[] = "Account holder name should only contain letters and spaces.";
                    }
                    if (empty($facultyData["Account Number"]) || !preg_match('/^[a-zA-Z0-9]{6,18}$/', $facultyData["Account Number"])) {
                        $cron_error[] = "Account number should be alphanumeric and 6 to 18 characters long.";
                    }
                    if (empty($facultyData["IFSC Code"]) || !preg_match('/^[A-Z]{4}0[A-Z0-9]{6}$/', $facultyData["IFSC Code"])) {
                        $cron_error[] = "IFSC code format is incorrect.";
                    }
                    if (empty($facultyData["Branch Address"]) || !preg_match('/^[A-Za-z0-9\s\-.,#\/]+$/', $facultyData["Branch Address"])) {
                        $cron_error[] = "Branch address contains invalid characters.";
                    }
                    if (empty($facultyData["Account Type"]) || !in_array(ucwords(strtolower($facultyData["Account Type"])), ['Savings Account', 'Current Account', 'Salary Account'])) {
                        $cron_error[] = "Invalid account type. Allowed types are 'Savings Account', 'Current Account', or 'Salary Account'.";
                    }
                }
                
                // Use the provided getBranchInfo function
                // $branchData = getBranchInfo($conn_rjllearning_specific, $institution_id, $facultyData['Branch']);
                // if (!$branchData) {
                //     $cron_error[] = "Branch not found for the given institution.";
                // } else {
                //     $facultyData['branch_id'] = $branchData['branch_id'];
                // }
                $stmt = $conn_rjllearning_specific->prepare("SELECT id FROM branch WHERE name = ? AND institution_id = ?");
                $stmt->execute([$facultyData['Branch'], $institution_id]);
                $branchData = $stmt->fetch(PDO::FETCH_ASSOC);
                if (!$branchData) {
                    $cron_error[] = "Branch not found for the given institution.";
                } else {
                    $facultyData['branch_id'] = $branchData['id'];
                }
                 //  use the new function for staff groups
                if (!empty($facultyData['Other Staff Groups'])) {
                    $staff_group_info = getOtherStaffGroupInfo(
                        $conn_rjllearning_specific,
                        $institution_id,
                        $facultyData['branch_id'],
                        $facultyData['Other Staff Groups']
                    );
                    // The function itself handles the "not found" error, so you just need to check if it returns anything.
                    if ($staff_group_info) {
                         $other_staff_group_id = $staff_group_info['id'];
                    }
                }
                
               

                // if (!empty($facultyData['Other Staff Groups'])) {
                //     $sql_staff_groups = "SELECT id FROM other_staff_groups WHERE group_name = ? AND institution_id = ? AND branch_id LIKE ?";
                //     $stmt_staff_groups = $conn_rjllearning_specific->prepare($sql_staff_groups);
                //     $stmt_staff_groups->execute(array($facultyData['Other Staff Groups'], $institution_id, '%' . $facultyData['branch_id'] . '%'));
                //     $staff_group = $stmt_staff_groups->fetch(PDO::FETCH_ASSOC);
                //     if (!$staff_group) {
                //         $cron_error[] = 'Staff group does not exist.';
                //     } else {
                //         $other_staff_group_id = $staff_group['id'];
                //     }
                // }
                
                // If any errors were found for this row, add them to the main error array
                if (!empty($current_row_errors)) {
                    $cron_error[] = "Row " . $rowNumber . ": " . implode(', ', $current_row_errors);
                } else {
                    // Only add the data to the insert array if there were no errors
                    $insert_data[] = $facultyData;
                }
            }
            
        
            // print_r($insert_data);die();
            if (!empty($insert_data)) {
                $conn_rjllearning_specific->beginTransaction();
                try {
                    // echo("hello");die();
                    $emailDetails = [];
                    // print_r($insert_data);die();
                    foreach ($insert_data as $index => $faculty) {
                        // Check for existing faculty before inserting
                        $res = $conn_rjllearning_specific->prepare("SELECT id FROM faculty_info WHERE institution_id = ? and branch_id = ? and (name = ? OR email = ? OR mobile = ?)");
                        $res->execute(array($institution_id, $faculty['branch_id'], $faculty['Name'], $faculty['Email'], $faculty['mobile']));
                        $result1 = $res->fetchAll();
                        if (count($result1) >= 1) {
                            $cron_error[] = "Faculty info already exists. Name: " . $faculty['Name'] . ", Email: " . $faculty['Email'] . ", Mobile: " . $faculty['mobile'];
                            continue;
                            
                        }
                        
                        // Prepare and execute the insert query for faculty_info
                        $sql = "INSERT INTO faculty_info (
                            institution_id, branch_id, name, gender, email, mobile, designation, role,
                            father_name, mother_name, spouse_name, nationality, dob, address, city, state,
                            pincode, country, status, lock_status, profile_image, date_of_joining
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                        $stmt = $conn_rjllearning_specific->prepare($sql);
                        $stmt->execute([
                            $institution_id, $faculty['branch_id'], $faculty['Name'], $faculty['Gender'],
                            $faculty['Email'], $faculty['mobile'], $faculty['Designation'], lcfirst($faculty['Role']),
                            $faculty['Father Name'], $faculty['Mother Name'], $faculty['Spouse Name'],
                            $faculty['Nationality'], $facultyData["dob"], $faculty['Address'], $faculty['City'], $state_id,
                            $faculty['Pincode'], $country_id, '1', '1', null, $faculty['Date of Joining']
                        ]);
                        $faculty_id = $conn_rjllearning_specific->lastInsertId();
                        // print_r("byyee");die();
                        
                    
                        
                        // User creation and email logic
                           if ($faculty['Role'] == 'Teacher' || $faculty['Role'] == 'Principal') {

                            $institution_info = getInstitution($conn_rjllearning, $institution_id);
                            $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $faculty['branch_id'], ['branch_code']);
                            $faculty_username = $branch_info['branch_code'] . "_" . $faculty_id . "_F@" . $institution_info['institution_code'];
                            $check_availability = checkUsernameAvailabilty($conn_rjllearning, $faculty_username);
                            if (!$check_availability) {
                                $cron_error[] = 'Username already exists. Please try another username.';
                            } else {
                                $chars = "abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789!@#$%&*_";
                                $password = substr(str_shuffle($chars), 0, 8);
                                $hashed_password = password_hash($password, PASSWORD_DEFAULT);
                                $user = storeUser($conn_rjllearning, $workspace_id ?? null, $institution_id, $faculty['Name'], $facultyData["dob"], $faculty['Gender'], $faculty_username, $faculty['Email'], $faculty['mobile'], $hashed_password, lcfirst($faculty['Role']), $faculty['branch_id']);
                                $updated_faculty_username = $branch_info['branch_code'] . "_" . $user . "@" . $institution_info['institution_code'];
                                $stmt = $conn_rjllearning->prepare("UPDATE users SET username = ? WHERE id = ? AND institution_id = ?");
                                $stmt->execute([$updated_faculty_username, (int)$user, $institution_id]);
                                $stmt = $conn_rjllearning_specific->prepare("UPDATE faculty_info SET userid = ? WHERE id = ? AND institution_id = ?");
                                $stmt->execute([$user, $faculty_id, $institution_id]);

                                // Email sending logic
                                $emailDetails[] = [
                                    'user_id' => $user,
                                    'email' => $faculty['Email'],
                                    'name' => $faculty['Name'],
                                    'username' => $updated_faculty_username,
                                    'password' => $password,
                                ];
                            }
                        }   
                        // print_r($emailDetails);die();
                        // try{
                        $success = createStaff($conn_rjllearning,$conn_rjllearning_specific, $workspace_id , $institution_id, $faculty['branch_id'], 1, $faculty['Account Holder Name'], $faculty['Account Name'], $faculty['Account Number'], $faculty['IFSC Code'], $faculty['Branch Address'], $faculty['Account Type'], null, null, null, $faculty['Driving License Expiry Date'], $faculty_username, $faculty_id);
                    //     } catch (Exception $e) {
                    //     $cron_error[] = "createStaff failed: " . $e->getMessage();
                    // }//   
                    // echo("hello");die();
                    }
                  $conn_rjllearning_specific->commit();
                
                    if (!empty($emailDetails)) {
                     
                            require_once $GLOBALS['base_path_to_learning_resources'] . 'lib/mail_system/mail_Template1.php';
                            $institution_info = getInstitution($conn_rjllearning, $institution_id);
                            $white_label_url = $institution_info['white_label_url'] ?? '';
                            $white_label_email = $institution_info['white_label_email'] ?? '';
                            $white_label_app_name = $institution_info['white_label_app_name'] ?? '';
                            $default_domain = 'rejoicelearning.in';
                            if (!empty($white_label_url)) {
                                $domain = str_replace(['http://', 'https://', 'www.'], '', $white_label_url);
                                $domain_parts = explode('/', $domain);
                                $base_domain = $domain_parts[0];
                                $url = 'https://faculty.' . $base_domain;
                            } else {
                                $url = 'https://faculty.' . $default_domain;
                            }
                            $app_name = !empty($white_label_app_name) ? $white_label_app_name : $web_app_title;
                            $app_url = $url;
                            $from_email = !empty($white_label_email) ? $white_label_email : 'Rejoice Learning <no-reply@rejoicelearning.in>';
                            $subject = 'Welcome to ' . $app_name;

                            foreach ($emailDetails as $emailDetail) {
                                $message = "<h1>Welcome To <span class='web_app_title'>$app_name</span></h1>
                                    <p>Thank you for creating a user account on $app_name. You can now sign in at $app_url or <a href='$app_url'>Click Here<a>. </p>
                                    <p>
                                    Name : <b>{$emailDetail['name']}</b><br>
                                    Username : <b>{$emailDetail['username']}</b><br>
                                    Password : <b>{$emailDetail['password']}</b>
                                    </p>";
                                updateUserRoleTimestamp($conn_rjllearning, $conn_rjllearning_specific, $workspace_id ?? null, $institution_id, $faculty_id);
                                sendMailTemplate1($conn_rjllearning, $from_email, $emailDetail['email'], $subject, $message, $workspace_id ?? null, $institution_id, $faculty['branch_id'], $emailDetail['user_id'], null, 'real_time');
                            }
                      
                    
                    }
    
                    // $conn_rjllearning_specific->commit();
                    // successreturn(array('successmsg' => "Faculty data imported successfully."));
                    return true; 
                } catch (Exception $e) {
                    $conn_rjllearning_specific->rollBack();
                     if (!empty($cron_error)){
                    errorreturn("Error: " . implode(' | ', $cron_error));
                     }
                }
            } else {
                errorreturn("No valid data found to import.");
            }
            
           
        } catch (Exception $e) {
            errorreturn("Error processing file: " . $e->getMessage());
        }
    }
    // else {
    //     errorreturn('No file was uploaded or file error occurred.');
    // }
} else {
    errorreturn("Invalid function_type.");
}

?>
