<?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/authenticate.php';
require_once $base_path_to_learning . 'functions/common_functions.php';
//db access function
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';


require_once $base_path_to_learning . "vendor/autoload.php";

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use \PhpOffice\PhpSpreadsheet\Style\Border;
use \PhpOffice\PhpSpreadsheet\Style\Font;

function removeBranchIdAndAddBranchName($array) {
    foreach ($array['students_info'] as &$student) {
        if (isset($student['branch_id'])) {
            $student['branch_name'] = $student['branch_id']['name'];
            unset($student['branch_id']);
        }
    }
    return $array;
}

$studentFields = array(
    'name' => 'Name',
    "branch_name"=>'Branch Name',
    'fathername' => 'Father Name',
    'mothername' => 'Mother Name',
    'mobile' => 'Mobile',
    'email' => 'Email',
    'address_line_1' => 'Address',
    'city' => 'City',
    'pincode' => 'Pincode',
    'dob' => 'DOB',
    'gender' => 'Gender',
    'blood_group' => 'Blood Group',
    'admission_number' => 'Admisson  Number',
    'class_id' => 'Class',
    'section_id' => 'Section',
    'roll_no' => 'Roll No.',
    'nationality' => 'Nationality',
    'caste' => 'Caste',
);

$data = GetBasicStudentsDetail($conn_rjllearning_specific, $institution_id, $branch_id, $class_id, $section_id, $search_string, $limit, $offset, $additionaldata);


$result = removeBranchIdAndAddBranchName($data);
$students = $result['students_info'];
$fields_to_remove = ['id','branch_id','admission_id'];

$student_data = removeKeys($students, $fields_to_remove);
$values = getArrayValues($student_data);

$keys = getArrayKeys($student_data[0]);
$formattedKeys = getArrayKeys($student_data[0]);

foreach ($keys as $index => $k) {
    $formattedKeys[$index] = getCorrespondingValue($studentFields, $k);
}

$data = array("values" => $values, "keys" => $formattedKeys);
createExcel($conn_rjllearning, $conn_rjllearning_specific, $data, $institution_id, $branch_id);

function createExcel($conn_rjllearning, $conn_rjllearning_specific, $data,  $institution_id, $branch_id)
{
    $values_count = count($data['values']);
    $keys_count = count($data['keys']);
    $values = $data['values'];
    $keys = $data['keys'];
    $institutions = getInstitution($conn_rjllearning, $institution_id);
    $additional_data = ['branch_code','pincode','address','contact_number','email'];
    $branch = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id,$additional_data);

    $columns = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');

    // Create a new Spreadsheet instance
    $spreadsheet = new Spreadsheet();

    // Create a new Spreadsheet instance
    $spreadsheet = new Spreadsheet();

    // Set values in the spreadsheet
    $sheet = $spreadsheet->getActiveSheet();
    // Set the column width for columns A to G
    for ($column = 'A'; $column <= 'G'; $column++) {
        $sheet->getColumnDimension($column)->setWidth(19); // Set the width as desired (15 in this example)
    }
    $spreadsheet->setActiveSheetIndex(0)
        ->setCellValue('A1', $institutions['institution_name'])
        ->setCellValue('A2', $branch['name']);

    // Get the cell style and font
    $style = $sheet->getStyle('A1');
    $font = $style->getFont();
    // Set the font to bold
    $font->setBold(true);
    $rowCount = 4;
    $column = 'A';
    // Set column headers
    foreach ($keys as $item) {
    
        $sheet->setCellValue($column . $rowCount, $item);
        $sheet->getStyle($column . $rowCount, $item)->getFont()->setBold(true);
        $column++;
    }

    $rowCount = 5;
    // Set row data
    foreach ($values as $v) {
        $column = 'A';
        foreach ($v as $item1) {

            $item_data = $item1;
            $sheet->setCellValue($column . $rowCount, $item_data);
            $alignment = $sheet->getStyle($column . $rowCount)->getAlignment();
            $column++;
        }
        $rowCount++;
    }
    // Set headers for download

    // Start output buffering
    ob_start();

    // Create Excel writer object
    $writer = new Xlsx($spreadsheet);

    // Save the spreadsheet to a variable instead of a file
    $writer->save('php://output');

    // Get the content that was written to the output buffer
    $excelData = ob_get_contents();

    // Clear the output buffer
    ob_end_clean();

    // Set appropriate headers for download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Students Report - Rejoice Learning.xlsx"');
    header('Cache-Control: max-age=0');

    // Output the Excel file data
    echo $excelData;
    exit;
}
