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

require_once '../../../../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';

require_once $base_path_to_learning . 'admin_api/modules/students/student_functions.php';
require_once $base_path_to_learning . 'user_management/faculty/functions.php';

//dynemic db
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';
require_once $base_path_to_learning . 'admin_api/modules/student_registration/functions/functions.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';
require_once $base_path_to_learning . 'functions/common_functions.php';
require_once $base_path_to_learning . 'functions/cache_student_academic_function.php';
require_once $base_path_to_learning_resources . 'external_requests/finware_curl/groups_ledgers/curl_add_alter_student_ledgers_finware.php';
require_once $base_path_to_learning_resources . 'external_requests/finware_curl/groups_ledgers/curl_function_branch_students.php';

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

$postdata = $_POST;
validateApiSignature($postdata['api_signature']);

$jwttoken = $postdata['jwt_token'];
$jwt_validate = authenticate_jwt($jwttoken);
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$username = $jwt_validate->data->username;
$role = $jwt_validate->data->role;

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

if ($postdata['function_type'] == "import_students") {
    $institution_id = $postdata['institution_id'];
    $academic_id = CheckAcademicSession($conn_rjllearning_specific, $institution_id);
    if ($academic_id == false) {
        errorreturn("Access denied. Academic session not active.");
    }

    if (isset($_FILES['file'])) {
        $insert_data = [];

        $fileName = $_FILES['file']['name'];
        $fileSize = $_FILES['file']['size'];
        $fileTemp = $_FILES['file']['tmp_name'];
        $fileExtension = pathinfo($fileName, PATHINFO_EXTENSION);

        // Define allowed file extensions
        $allowedExtensions = ["xlsx", "xls"];
        if (!in_array($fileExtension, $allowedExtensions)) {
            errorreturn("Only XLSX and XLS files are allowed.");
        }

        // File size validation (max 10MB)
        $maxFileSize = 10 * 1024 * 1024;
        if ($fileSize > $maxFileSize) {
            errorreturn('File size exceeds the maximum allowed limit (10 MB).');
        }

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

        try {
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileTemp);
            $worksheet = $spreadsheet->getSheet(0);

            foreach ($worksheet->getRowIterator() as $rowNumber => $row) {
                if ($rowNumber >= 1) {  // Process rows starting from row 1
                    if ($rowNumber == 1) continue; // Skip the header row (column names)

                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(false); // Read all cells

                    $rowData = [];
                    foreach ($cellIterator as $cell) {
                        $rowData[] = $cell->getValue();
                    }

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

                    // Map Excel columns to fields
                    $requiredFields = [
                        'branch' => $rowData[0] ?? null,
                        'reg_no' => $rowData[1] ?? null,
                        'name' => strtoupper($rowData[2] ?? null),
                        'fathername' => strtoupper($rowData[3] ?? null),
                        'mothername' => $rowData[4] ?? null,
                        'mobile' => $rowData[5] ?? null,
                        'email' => strtolower($rowData[6] ?? null),
                        'address_line_1' => $rowData[7] ?? null,
                        'city' => $rowData[8] ?? null,
                        'pincode' => $rowData[9] ?? null,
                        'dob' => $rowData[10] ?? null,
                        'gender' => $rowData[11] ?? null,
                        'for_class' => $rowData[12] ?? null,
                        'last_school_name' => $rowData[13] ?? null,
                        'current_class_name' => $rowData[14] ?? null,
                        'section_admitted_name' => $rowData[15] ?? null,
                        'country_name' => $rowData[16] ?? null,
                        'caste' => $rowData[17] ?? null,
                        'nationality' => $rowData[18] ?? null,
                        'nationality_proof_number' => $rowData[19] ?? null,
                        'blood_group' => $rowData[20] ?? null,
                        'father_occupation' => $rowData[21] ?? null,
                        'mother_occupation' => $rowData[22] ?? null,
                        'admission_number' => $rowData[23] ?? null,
                        'date_admission' => $rowData[24] ?? null
                    ];

                    // Check for null values in required fields
                    foreach ($requiredFields as $field => $value) {
                        if (is_null($value) || $value === '') {
                            errorreturn("Error: The field '$field' cannot be null or empty at row $rowNumber.");
                        }
                    }

                    // Extract values from the row
                    $branch = $rowData[0];
                    $reg_no = $rowData[1];
                    $name = strtoupper($rowData[2]);
                    $fathername = strtoupper($rowData[3]);
                    $mothername = $rowData[4];
                    $mobile = $rowData[5];
                    $email = strtolower($rowData[6]);
                    $address_line_1 = $rowData[7];
                    $city = $rowData[8];
                    $pincode = $rowData[9];
                    $dob = $rowData[10];
                    $gender = $rowData[11];
                    $for_class_name = $rowData[12];
                    $last_school = $rowData[13];
                    $current_class_name = $rowData[14];
                    $section_admitted_name = $rowData[15];
                    $country_name = $rowData[16];
                    $caste = $rowData[17];
                    $nationality = $rowData[18];
                    $nationality_proof_number = $rowData[19];
                    $blood_group = $rowData[20];
                    $father_occupation = $rowData[21];
                    $mother_occupation = $rowData[22];
                    $admission_number = $rowData[23];
                    $date_admission = $rowData[24];

                    // Fetch Country ID
                    $stmt = $conn_rjllearning->prepare("SELECT id FROM country_info WHERE name = ?");
                    $stmt->execute([$country_name]);
                    $country = $stmt->fetch(PDO::FETCH_COLUMN);

                    // Fetch Branch ID
                    $stmt = $conn_rjllearning_specific->prepare("SELECT id FROM branch WHERE name = ? AND institution_id = ?");
                    $stmt->execute([$branch, $institution_id]);
                    $branchData = $stmt->fetch(PDO::FETCH_ASSOC);
                    if (!$branchData) {
                        errorreturn("Branch not found for the given institution at row $rowNumber.");
                    }
                    $branch_id = $branchData['id'];

                    // Fetch Section ID
                    $stmt = $conn_rjllearning_specific->prepare("
                        SELECT id 
                        FROM section_info 
                        WHERE section_name = ? 
                        AND institution_id = ? 
                        AND (branch_id LIKE ? OR branch_id = 'all')
                    ");
                    $branch_id_like = '%' . $branch_id . '%';
                    $stmt->execute([$section_admitted_name, $institution_id, $branch_id_like]);
                    $sectionData = $stmt->fetch(PDO::FETCH_ASSOC);

                    if (!$sectionData) {
                        errorreturn("Section not found for the given institution and branch at row $rowNumber.");
                    }
                    $section_admitted = $sectionData['id'];

                    // Fetch For Class ID
                    $stmt = $conn_rjllearning_specific->prepare("
                        SELECT id 
                        FROM class_info 
                        WHERE classname = ? 
                        AND institution_id = ? 
                        AND (branch_id LIKE ? OR branch_id = 'all')
                    ");
                    $stmt->execute([$for_class_name, $institution_id, '%' . $branch_id . '%']);
                    $forclassname = $stmt->fetch(PDO::FETCH_ASSOC);

                    if (!$forclassname) {
                        errorreturn("New class not found for the given institution and branch at row $rowNumber.");
                    }
                    $for_class = $forclassname['id'];

                    // Fetch Current Class ID
                    $stmt = $conn_rjllearning_specific->prepare("
                        SELECT id 
                        FROM class_info 
                        WHERE classname = ? 
                        AND institution_id = ? 
                        AND (branch_id LIKE ? OR branch_id = 'all')
                    ");
                    $stmt->execute([$current_class_name, $institution_id, '%' . $branch_id . '%']);
                    $currentClassData = $stmt->fetch(PDO::FETCH_ASSOC);

                    if (!$currentClassData) {
                        errorreturn("Current class not found for the given institution and branch at row $rowNumber.");
                    }
                    $current_class = $currentClassData['id'];

                    // Mobile validation
                    if (!preg_match('/^\d{10}$/', $mobile)) {
                        errorreturn("Invalid mobile number at row: $rowNumber");
                    }
                    $mobile = "+91" . $mobile;

                    // Validate Email
                    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                        errorreturn("Invalid email format at row: $rowNumber");
                    }

                    // Validate Gender
                    if (!in_array($gender, ['M', 'F'])) {
                        errorreturn("Invalid gender details at row: $rowNumber");
                    }

                    // Validate DOB
                    $date_now = date("Y-m-d");
                    if (is_numeric($dob)) {
                        // Handle as Excel serial date
                        $excelBaseDate = 25569; // Excel's base date is January 1, 1900
                        $unixTimestamp = ($dob - $excelBaseDate) * 86400;
                        $dob = date("Y-m-d", $unixTimestamp);
                    } else {
                        if (!strtotime($dob)) {
                            errorreturn("Invalid date format at row $rowNumber. Expected format: dd-mm-yyyy. Received: " . $dob);
                        }
                        if (preg_match('/^\d{4}-\d{2}-\d{2}/', $dob)) {
                            $dob = date('Y-m-d', strtotime($dob)); // Already correct format
                        } else {
                            $dob = date('Y-m-d', strtotime($dob)); // Convert DD-MM-YYYY
                        }
                    }

                    // Validate DOB is in the past
                    if (!CheckDob($dob) || $dob >= $date_now) {
                        errorreturn("Invalid DOB (must be past date) at row: $rowNumber");
                    }

                    // Validate Admission Date
                    if (is_numeric($date_admission)) {
                        $excelBaseDate = 25569;
                        $unixTimestamp = ($date_admission - $excelBaseDate) * 86400;
                        $date_admission = date("Y-m-d", $unixTimestamp);
                    } else {
                        if (!strtotime($date_admission)) {
                            errorreturn("Invalid admission date format at row $rowNumber. Expected format: dd-mm-yyyy. Received: " . $date_admission);
                        }
                        $date_admission = date('Y-m-d', strtotime($date_admission));
                    }

                    // Check for Duplicate Student
                    $stmt = $conn_rjllearning_specific->prepare("SELECT id FROM students WHERE institution_id = ? AND branch_id = ? AND (name = ? OR email = ? OR mobile = ?)");
                    $params = [$institution_id, $branch_id, $name, $email, $mobile];
                    $stmt->execute($params);
                    if ($stmt->rowCount() > 0) {
                        errorreturn("Duplicate entry found at row: $rowNumber");
                    }

                    // Check if admission number already exists
                    $stmt = $conn_rjllearning_specific->prepare("SELECT id FROM students WHERE institution_id = ? AND branch_id = ? AND admission_number = ?");
                    $stmt->execute([$institution_id, $branch_id, $admission_number]);
                    if ($stmt->rowCount() > 0) {
                        errorreturn("Admission number already exists at row: $rowNumber");
                    }

                    // Add valid data for batch insertion
                    $insert_data[] = [
                        $institution_id,
                        $branch_id,
                        $reg_no,
                        $name,
                        $fathername,
                        $mothername,
                        $mobile,
                        $email,
                        $address_line_1,
                        $city,
                        $pincode,
                        $dob,
                        $gender,
                        null, // reg_fees
                        null, // paid_through
                        null, // reference_number
                        $for_class,
                        $last_school,
                        $current_class,
                        0,    // sibling_bool
                        null, // sibling
                        $date_now,
                        $username,
                        '1'   // status
                    ];
                }
            }

            try {
                if (!empty($insert_data)) {
                    // Prepare SQL statements
                    $stmt_insert_registration = $conn_rjllearning_specific->prepare("
                        INSERT INTO student_registration (
                            institution_id, branch_id, reg_no, name, fathername, mothername, mobile, email,
                            address_line_1, city, pincode, dob, gender, reg_fees, paid_through, refrence_number,
                            for_class, last_school, current_class, sibling_bool, sibling, date_added, added_by, status
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ");

                    $stmt_insert_student_admission = $conn_rjllearning_specific->prepare("
                        INSERT INTO student_admission (
                            institution_id, branch_id, reg_no, date_admission, admission_number, class_admitted,
                            section_admitted, last_tc, last_school, date_added, added_by, profile_image, sibling_bool,
                            sibling, student_id
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ");

                    $stmt_insert_students = $conn_rjllearning_specific->prepare("
                        INSERT INTO students (
                            institution_id, branch_id, admission_id, academic_id, nationality, caste, admission_number, 
                            class_id, section_id, roll_no, username, student_profile_image, birth_certificate, nationality_proof, 
                            nationality_proof_number, name, fathername, mothername, mobile, email, address_line_1, city, 
                            pincode, dob, gender, blood_group, date_admission, date_added, added_by, user_id, can_activate, status,
                            father_occupation, mother_occupation
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ");

                    $stmt_update_student_admission = $conn_rjllearning_specific->prepare("
                        UPDATE student_admission SET student_id = ? WHERE institution_id = ? AND student_id = ?
                    ");

                    $stmt_update_students = $conn_rjllearning_specific->prepare("
                        UPDATE students SET user_id = ? WHERE id = ? AND institution_id = ?
                    ");

                    $stmt_update_users = $conn_rjllearning_specific->prepare("
                        UPDATE student_registration SET name=?, fathername=?, mothername=?, mobile=?, email=?, 
                        address_line_1=?, city=?, pincode=?, dob=?, gender=?, status=?, student_id=? 
                        WHERE id=? AND institution_id=?
                    ");

                    $students_list = []; // For sending welcome emails
                    $formattedData = []; // For financial ledger

                    foreach ($insert_data as $index => $rowAdd) {
                        // Insert into student_registration
                        $stmt_insert_registration->execute($rowAdd);
                        $reg_id = $conn_rjllearning_specific->lastInsertId();

                        // Get the corresponding row data
                        $current_row_data = $worksheet->getRowIterator()->current();
                        $cellIterator = $current_row_data->getCellIterator();
                        $cellIterator->setIterateOnlyExistingCells(false);
                        $rowData = [];
                        foreach ($cellIterator as $cell) {
                            $rowData[] = $cell->getValue();
                        }

                        $admission_number = $rowData[23];
                        $date_admission = $rowData[24];

                        // Insert into student_admission
                        $stmt_insert_student_admission->execute([
                            $institution_id,
                            $branch_id,
                            $rowAdd[2], // reg_no
                            $date_admission,
                            $admission_number,
                            $rowAdd[16], // for_class
                            $section_admitted,
                            null,
                            $rowAdd[17], // last_school
                            date('Y-m-d'),
                            $username,
                            null,
                            0,
                            null,
                            $institution_id . '_' . $branch_id . '_' . $reg_id
                        ]);
                        $admission_id = $conn_rjllearning_specific->lastInsertId();

                        // Generate Unique Username
                        $institution_info = getInstitution($conn_rjllearning, $institution_id);
                        $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id, ['branch_code']);
                        $student_username = ($branch_info['branch_code']) . $admission_number . "@" . ($institution_info['institution_code']);

                        while (!checkUsernameAvailabilty($conn_rjllearning, $student_username)) {
                            $admission_number++;
                            $student_username = ($branch_info['branch_code']) . $admission_number . "@" . ($institution_info['institution_code']);
                        }

                        // Insert into students
                        $stmt_insert_students->execute([
                            $institution_id,
                            $branch_id,
                            $admission_id,
                            $academic_id,
                            $nationality,
                            $caste,
                            $admission_number,
                            $rowAdd[16], // for_class
                            $section_admitted,
                            rand(1, 100),
                            $student_username,
                            null,
                            null,
                            null,
                            $nationality_proof_number,
                            $rowAdd[3], // name
                            $rowAdd[4], // fathername
                            $rowAdd[5], // mothername
                            $rowAdd[6], // mobile
                            $rowAdd[7], // email
                            $rowAdd[8], // address_line_1
                            $rowAdd[9], // city
                            $rowAdd[10], // pincode
                            $rowAdd[11], // dob
                            $rowAdd[12], // gender
                            $blood_group,
                            $date_admission,
                            date('Y-m-d'),
                            $username,
                            $username,
                            1,
                            1,
                            $father_occupation,
                            $mother_occupation
                        ]);
                        $student_id = $conn_rjllearning_specific->lastInsertId();

                        // Update student_admission table
                        $stmt_update_student_admission->execute([$student_id, $institution_id, $institution_id . '_' . $branch_id . '_' . $reg_id]);

                        // Generate Password
                        $password = substr(str_shuffle("abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789!@#$%&*_"), 0, 8);
                        $hashed_password = password_hash($password, PASSWORD_DEFAULT);

                        // Create User
                        $userid = storeUser($conn_rjllearning, $workspace_id, $institution_id, $rowAdd[3], $rowAdd[11], $rowAdd[12], $student_username, $rowAdd[7], $rowAdd[6], $hashed_password, 'student', $branch_id);

                        // Update students table with user_id
                        $stmt_update_students->execute([$userid, $student_id, $institution_id]);

                        // Update student registration
                        $stmt_update_users->execute([
                            $rowAdd[3], // name
                            $rowAdd[4], // fathername
                            $rowAdd[5], // mothername
                            $rowAdd[6], // mobile
                            $rowAdd[7], // email
                            $rowAdd[8], // address_line_1
                            $rowAdd[9], // city
                            $rowAdd[10], // pincode
                            $rowAdd[11], // dob
                            $rowAdd[12], // gender
                            2, // status
                            $student_id,
                            $reg_id,
                            $institution_id
                        ]);

                        // Cache Student Data
                        cacheAcademicStudentData($conn_rjllearning_specific, $institution_id, $branch_id, $rowAdd[16], $section_admitted, $student_id, $date_admission);

                        // Create financial ledger entry
                        $branch = getBranchDetails($conn_rjllearning_specific, $branch_id, $institution_id);
                        if ($branch) {
                            // Generate ledger name based on parent's name
                            $ledger_name = !empty($fathername) ? 
                                "$name S/o $fathername - $admission_number" : 
                                "$name D/o $mothername - $admission_number";

                            // Sanitize inputs
                            $ledger_address = sanitizeInput($address_line_1);
                            $ledger_name = sanitizeInput($ledger_name);

                            // Create student financial ledger data
                            $formattedData[] = [
                                'product_connection_id' => $userid,
                                'ledger_address' => $ledger_address,
                                'ledger_name' => $ledger_name,
                                'ledger_main_ledgerhead' => 'Sundry Debtors',
                                'mobile' => $mobile,
                                'email' => $email,
                                'class' => $rowAdd[16],
                                'section' => $section_admitted,
                                "function_type" => "add"
                            ];

                            $students_data = [
                                "workspace_id" => $workspace_id,
                                "branch" => $branch,
                                "student_learning" => $formattedData,
                                "created_by" => $username
                            ];

                            // Send data to financial system
                            curlAddAlterStudentLedgersFinware($students_data);
                        }

                        // Prepare welcome email data
                        if (isset($email) && isset($name) && isset($student_username) && isset($password)) {
                            $students_list[] = [
                                "user_id" => $userid,
                                "email" => $email,
                                "name" => $name,
                                "username" => $student_username,
                                "password" => $password
                            ];
                        }
                    }

                    // Send welcome emails
                    if (!empty($students_list)) {
                        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_message_header = $institution_info['white_label_message_header'] ?? '';
                        $white_label_app_name = $institution_info['white_label_app_name'] ?? '';
                
                        // Set default domain
                        $default_domain = 'rejoicelearning.in';
                        
                        // Build faculty subdomain-based URL dynamically
                        if (!empty($white_label_url)) {
                            $domain = str_replace(['http://', 'https://', 'www.'], '', $white_label_url);
                            $domain_parts = explode('/', $domain); // remove any path
                            $base_domain = $domain_parts[0];
                
                            $url = 'https://students.' . $base_domain;
                        } else {
                            $url = 'https://students.' . $default_domain;
                        }
                
                        // Use white label values if provided, otherwise fallback to default
                        $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;
                        // $url = 'https://students.rejoicelearning.in';

                        foreach ($students_list as $emailDetail) {
                            $message = "
                                <h1>Welcome To <span class='web_app_title'>" . htmlspecialchars($app_name) . "</span></h1>
                                <p>Thank you for creating a user account on " . htmlspecialchars($app_name) . ". 
                                  You can now sign in at <a href='" . htmlspecialchars($app_url) . "'>" . htmlspecialchars($app_url) . "</a>.</p>
                                <p>
                                    Name: <b>" . htmlspecialchars($emailDetail['name']) . "</b><br>
                                    Username: <b>" . htmlspecialchars($emailDetail['username']) . "</b><br>
                                    Password: <b>" . htmlspecialchars($emailDetail['password']) . "</b>
                                </p>";
                            
                            sendMailTemplate1(
                                $conn_rjllearning,
                                $from_email,
                                $emailDetail['email'],
                                $subject,
                                $message,
                                $workspace_id,
                                $institution_id,
                                $branch_id,
                                $emailDetail['user_id'],
                                null,
                                'real_time'
                            );
                        }
                    }

                    // Commit transaction after successful execution
                    $conn_rjllearning_specific->commit();
                    successreturn(['successmsg' => "Student(s) added successfully."]);
                } else {
                    errorreturn("No student data to insert.");
                }
            } catch (Exception $e) {
                // Rollback in case of any error
                $conn_rjllearning_specific->rollBack();
                errorreturn("Error: " . $e->getMessage());
            }
        } catch (Exception $e) {
            $conn_rjllearning_specific->rollBack();
            errorreturn("Error: " . $e->getMessage());
        }
    } else {
        errorreturn('No file was uploaded.');
    }
}