<?php
ini_set('display_errors', 1);

require_once '/home/rejoicebilldesk/public_html/rjllearning_development/resources/base_path_include.php';
require_once $base_path_to_learning_resources . "includes/connect.php"; // use require for mandatory files
require_once $base_path_to_learning_resources . 'lib/function.php';
require_once $base_path_to_learning_resources . 'lib/validate_api_signature.php';

//valdation file
require_once $base_path_to_learning_resources . 'lib/validate_function.php';
require_once $base_path_to_learning . '/user_management/check_username_availability.php';
require_once $base_path_to_learning . 'user_management/authenticate.php';

//db access function
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';

require_once $base_path_to_learning . 'user_management/faculty/functions.php';
require_once $base_path_to_learning . 'functions/common_functions.php';
require_once $base_path_to_learning . 'admin_api/modules/staff/staff_details/staff_functions.php';
require_once $base_path_to_learning_resources . "lib/function.php";
;
require_once $base_path_to_learning . 'functions/staff_logs.php';
require_once $base_path_to_learning . "vendor/autoload.php";
require_once $base_path_to_learning_resources . 'lib/bucket_functions/bucket_functions.php';
require_once $base_path_to_learning_resources . 'includes/aws_bucket_config.php';


use Aws\S3\S3Client;
use Aws\S3\Exception\S3Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;

$postdata = $_POST;

// Validate required fields
if (
    !isset($postdata['api_signature']) || empty($postdata['api_signature']) ||
    !isset($postdata['function_type']) || empty($postdata['function_type'])
) {
    errorreturn("All required fields (api_signature, function_type, jwt_token) are missing.");
}

validateApiSignature($postdata['api_signature']);

$userid = $postdata['user_id'];
$workspace_id = $postdata['workspace_id'];
$username = $postdata['username'];
$role = $postdata['role'];


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

$conn_rjllearning_specific = dynamicDbConnection($specific_db_name);
$institution_id = $postdata['institution_id'] ?? $institution_id;


if (!isset($_FILES['excelFile']) || $_FILES['excelFile']['error'] != UPLOAD_ERR_OK) {
    errorreturn('No file was uploaded or file error occurred.');
}

$file = $_FILES['excelFile'];
$allowedExtensions = ["xlsx", "xls"];
$fileExtension = pathinfo($file['name'], PATHINFO_EXTENSION);

if (!in_array($fileExtension, $allowedExtensions)) {
    errorreturn("Only Excel files (XLSX, XLS) are allowed.");
}

if ($file['size'] > 10 * 1024 * 1024) {
    errorreturn('File size exceeds 10MB limit.');
}

try {
    $spreadsheet = IOFactory::load($file['tmp_name']);
    $worksheet = $spreadsheet->getActiveSheet();
    $rows = $worksheet->toArray();

    // Status mapping
    $statusMapping = [
        'Active' => 1,
        'Inactive' => 0
    ];

    // Begin transaction
    $conn_rjllearning_specific->beginTransaction();

    $insertedRowsCount = 0; // Initialize counter for inserted rows
    $faculty_email_data = [];
    foreach ($rows as $rowNumber => $row) {
        if ($rowNumber == 0)
            continue; // Skip header row

        // Skip empty rows
        if (empty(array_filter($row)))
            continue;

        $facultyData = [
            'institution_name' => $row[0] ?? '',
            'faculty_name' => $row[1] ?? '',
            'branch_name' => $row[2] ?? '',
            'email' => $row[3] ?? '',
            'mobile' => $row[4] ?? '',
            'dob' => $row[5] ?? '',
            'gender' => $row[6] ?? '',
            'father_name' => $row[7] ?? null,
            'mother_name' => $row[8] ?? null,
            'password' => $row[9] ?? '',
            'status' => $row[10] ?? '',
            'role' => $row[11] ?? '',
            'last_active_date' => $row[12] ?? null,
        ];

        $requiredFields = ['faculty_name', 'branch_name', 'email', 'mobile', 'dob', 'gender', 'password', 'status', 'role'];
        foreach ($requiredFields as $field) {
            if (empty($facultyData[$field])) {
                $conn_rjllearning_specific->rollBack();
                errorreturn("Row " . ($rowNumber + 1) . ": $field is required.");
            }
        }

        // Get institution by name
        $stmt_institution = $conn_rjllearning->prepare("SELECT id FROM institutions WHERE institution_name = ?");
        $stmt_institution->execute([$facultyData['institution_name']]);
        $institution = $stmt_institution->fetch(PDO::FETCH_ASSOC);
        if (!$institution) {
            $conn_rjllearning_specific->rollBack();
            errorreturn("Row " . ($rowNumber + 1) . ": Institution not found.");
        }
        $facultyData['institution_id'] = $institution_id;

        // Get branch by name
        $stmt = $conn_rjllearning_specific->prepare("SELECT id FROM branch WHERE name = ? AND institution_id = ?");

        $stmt->execute([$facultyData['branch_name'], $facultyData['institution_id']]);
        $branch = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$branch) {
            $conn_rjllearning_specific->rollBack();
            errorreturn("Row " . ($rowNumber + 1) . ": Branch not found.");
        }
        $facultyData['branch_id'] = $branch['id'];

        // Validate status
        if (!array_key_exists($facultyData['status'], $statusMapping)) {
            $conn_rjllearning_specific->rollBack();
            errorreturn("Row " . ($rowNumber + 1) . ": Invalid status. Allowed: Active, Inactive");
        }
        $facultyData['status'] = $statusMapping[$facultyData['status']];

        // Email validation
        if (!CheckEMailIsValidOrNot($facultyData['email'])) {
            errorreturn("Row " . ($rowNumber + 1) . ": Enter valid Email Id");
        }

        // Name validation
        // if (!checkOnlyCharacters($facultyData['faculty_name'])) {
        //     errorreturn("Row " . ($rowNumber + 1) . ": Name must only contain letters!");
        // }

        // Gender conversion
        $gender = strtoupper(trim($facultyData['gender']));
        if ($gender == 'MALE') {
            $facultyData['gender'] = 'M';
        } elseif ($gender == 'FEMALE') {
            $facultyData['gender'] = 'F';
        } else {
            errorreturn("Row " . ($rowNumber + 1) . ": Invalid gender details!");
        }

        // // Father and Mother name validation
        // if ($facultyData['father_name'] !== null && !CheckAlphabetssWithSpace($facultyData['father_name'])) {
        //     errorreturn("Row " . ($rowNumber + 1) . ": Invalid Father Name!");
        // }
        // if ($facultyData['mother_name'] !== null && !CheckAlphabetssWithSpace($facultyData['mother_name'])) {
        //     errorreturn("Row " . ($rowNumber + 1) . ": Invalid Mother Name!");
        // }

        // DOB conversion
        $dobRaw = $facultyData['dob'];
        if (is_numeric($dobRaw)) {
            $excelBaseDate = 25569;
            $unixTimestamp = ($dobRaw - $excelBaseDate) * 86400;
            $dob = date("Y-m-d", $unixTimestamp);
        } else {
            if (!strtotime($dobRaw)) {
                errorreturn('Invalid date format. Expected format: dd-mm-yyyy. Received: ' . $dobRaw . ' Row : ' . $rowNumber);
            }
            $dob = date('Y-m-d', strtotime($dobRaw));
        }
        $facultyData['dob'] = $dob;

        $date_now = date("Y-m-d");
        if (!CheckDob($dob) || $dob >= $date_now) {
            errorreturn("Row " . ($rowNumber + 1) . ": Invalid DOB formats!");
        }

        // Role conversion
        $role = strtolower(trim($facultyData['role']));
        if ($role == 'principal' || $role == 'teacher') {
            $facultyData['role'] = $role;
        } else {
            errorreturn("Row " . ($rowNumber + 1) . ": Invalid role!");
        }

        $designation = ucwords($role);
        $spouse_name = null;
        $nationality = 'Indian';
        $address = 'Chandigarh';
        $city = 'Chandigarh';
        $state = '23';
        $pincode = '123456';
        $country = '1';
        $date_of_termination = null;
        $other_staff_group_id = null;
        $date_of_joining = date('Y-m-d');
        if ($role === 'teacher' || $role === 'principal') {
            // echo $faculty_role;die();
            $user_id = $facultyData['faculty_name'];
        } elseif ($role === 'other') {
            $user_id = null;
        }

        // Check if a teacher with the same name, email, or mobile already exists
        $sql_check_teacher = "SELECT id FROM faculty_info WHERE institution_id = ? AND branch_id = ? AND (name = ? AND email = ?) OR (name = ? AND mobile = ?)";
        $stmt_check_teacher = $conn_rjllearning_specific->prepare($sql_check_teacher);
        // Bind the parameters (name, email, mobile)
        $stmt_check_teacher->execute([
            $facultyData['institution_id'],
            $facultyData['branch_id'],
            $facultyData['faculty_name'], // Name to check
            $facultyData['email'],        // Email to check
            $facultyData['faculty_name'], // Name again to check with mobile
            $facultyData['mobile']       // Mobile to check
        ]);

        $existing_teacher = $stmt_check_teacher->fetch(PDO::FETCH_ASSOC);

        if ($existing_teacher) {
            // If an existing teacher is found, return an error
            errorreturn("Row " . ($rowNumber + 1) . ": A teacher with this name, email, or mobile already exists.");
        }
        if ($facultyData['status'] !== 'Inactive') {
            $sql = "INSERT INTO faculty_info (
                institution_id, branch_id, userid, name, gender, email, mobile, designation, role, 
                father_name, mother_name, spouse_name, nationality, dob, address, city, state, 
                pincode, country, status, lock_status, profile_image, other_staff_group_id, 
                date_of_joining, date_of_termination) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = $conn_rjllearning_specific->prepare($sql);
            $stmt->execute([
                $facultyData['institution_id'],
                $facultyData['branch_id'],
                $user_id,
                $facultyData['faculty_name'],
                $facultyData['gender'],
                $facultyData['email'],
                $facultyData['mobile'],
                $designation,
                $role,
                $facultyData['father_name'],
                $facultyData['mother_name'],
                $spouse_name,
                $nationality,
                $dob,
                $address,
                $city,
                $state,
                $pincode,
                $country,
                '1',
                '1',
                null,
                $other_staff_group_id,
                $date_of_joining,
                $date_of_termination
            ]);
            $faculty_id = $conn_rjllearning_specific->lastInsertId();

            if ($role == 'teacher' || $role == 'principal') {

                $institution_id = $facultyData['institution_id'];
                $branch_id = $facultyData['branch_id'];
                $gender = $facultyData['gender'];

                $institution_info = getInstitution($conn_rjllearning, $institution_id);
                $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id, ['branch_code']);

                $faculty_username = $branch_info['branch_code'] . "_" . $faculty_id . "_F@" . $institution_info['institution_code'];

                $is_available = checkUsernameAvailabilty($conn_rjllearning, $faculty_username);
                if (!$is_available) {
                    errorreturn('Username already exists. Please try another username.');
                }

                // Encrypt password
                $hashed_password = password_hash($facultyData['password'], PASSWORD_DEFAULT);

                // Create user
                $user = storeUser(
                    $conn_rjllearning,
                    $workspace_id,
                    $institution_id,
                    $facultyData['faculty_name'],
                    $dob,
                    $gender,
                    $faculty_username,
                    $facultyData['email'],
                    $facultyData['mobile'],
                    $hashed_password,
                    $role,
                    $branch_id
                );

                $user_id = (int) $user;
                $final_username = $branch_info['branch_code'] . "_" . $user_id . "@{$institution_info['institution_code']}";

                // Update username in users table
                $sql = "UPDATE users SET username = ? WHERE id = ? AND institution_id = ?";
                $stmt = $conn_rjllearning->prepare($sql);
                $stmt->execute([$final_username, $user_id, $institution_id]);

                // Update faculty_info with new user ID
                $sql = "UPDATE faculty_info SET userid = ? WHERE id = ? AND institution_id = ?";
                $stmt = $conn_rjllearning_specific->prepare($sql);
                $stmt->execute([$user_id, $faculty_id, $institution_id]);
            }


            $success = createStaff(
                $conn_rjllearning_specific,
                $workspace_id,
                $facultyData['institution_id'],
                $facultyData['branch_id'],
                $print_bank_details = null,
                $account_holder_name = null,
                $account_name = null,
                $account_no = null,
                $ifsc_code = null,
                $branch_address = null,
                $account_type = null,
                $adhar_card = null,
                $pan_card = null,
                $driving_licence = null,
                $driving_expiry_date = null,
                $final_username,  // from earlier
                $faculty_id
            );

            $basic_salary = "50000";
            insertStaffLog(
                $conn_rjllearning_specific,
                $institution_id,
                $facultyData['branch_id'],
                $faculty_id,
                $account_holder_name,
                $account_no,
                $ifsc_code,
                $branch_address,
                $account_type,
                $basic_salary,
                $designation,
                1
            );

            // Update joining date in other_staff_details
            if (!empty($date_of_joining)) {
                $query = "UPDATE other_staff_details SET joining_date = ? WHERE institution_id = ? AND faculty_id = ?";
                $stmt = $conn_rjllearning_specific->prepare($query);
                $stmt->execute([$date_of_joining, $institution_id, $faculty_id]);
            }

            if (!empty($facultyData['email'])) {
                $faculty_email_data[] = [
                    'email' => $facultyData['email'],
                    'name' => $facultyData['faculty_name'],
                    'username' => $final_username,
                    'password' => $facultyData['password'],
                    'url' => 'https://faculty.rejoicelearning.in'
                ];
            }


        }
        $insertedRowsCount++;
    }

    require_once $GLOBALS['base_path_to_learning_resources'] . 'lib/mail_system/mail_Template1.php';


    foreach ($faculty_email_data as $emailData) {
        $subject = 'Welcome to ' . $web_app_title;
        $message = "<h1>Welcome To <span class='web_app_title'>$web_app_title</span></h1>
                <p>Thank you for creating a user account on $web_app_title. You can now sign in at {$emailData['url']} or <a href='{$emailData['url']}'>Click Here</a>.</p>
                <p>
                Name : <b>{$emailData['name']}</b><br>
                Username : <b>{$emailData['username']}</b><br>
                Password : <b>{$emailData['password']}</b>
                </p>";

        sendMailTemplate1(
            $conn_rjllearning,
            'Rejoice Learning <no-reply@rejoicelearning.in>',
            $emailData['email'],
            $subject,
            $message,
            $workspace_id,
            $institution_id,
            $branch_id,
            null,
            null,
            'real_time'
        );
    }

    $conn_rjllearning_specific->commit();
    return $insertedRowsCount;

} catch (Exception $e) {
    if ($conn_rjllearning_specific->inTransaction()) {
        $conn_rjllearning_specific->rollBack();
    }
    errorreturn("Import failed: " . $e->getMessage());
}

