<?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\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

$postdata = json_decode(file_get_contents("php://input"), true);

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'])
) {
    errorreturn("All * fields are required.");
}

validateApiSignature($postdata['api_signature']);
$jwttoken = $postdata['jwt_token'];
$jwt_validate = authenticate_jwt($jwttoken);
//then workspace_id is iin decodede data of jwt use from there
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$username = $jwt_validate->data->username;
$role = $jwt_validate->data->role;
$institution_id = $jwt_validate->data->institution_id;
$branch_id = $jwt_validate->data->branch_id;

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

require_once $base_path_to_learning . 'user_management/user_versioning.php';

$version = isset($_POST['version']) ? $_POST['version'] : (isset($postdata['version']) ? $postdata['version'] : null);
$updated_time = isset($_POST['updated_time']) ? $_POST['updated_time'] : (isset($postdata['updated_time']) ? $postdata['updated_time'] : null);

$response = user_data_refresh($conn_rjllearning, $conn_rjllearning_specific,$version, $updated_time, $userid, $institution_id);

if ($postdata['function_type'] == "download_students_excel") {

   

    // Create a new spreadsheet
    $spreadsheet = new Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();

    // Set column headers
           $headers = [
            'A' => 'Branch', 'B' => 'Reg No', 'C' => 'Name',
            'D' => 'Father Name', 'E' => 'Mother Name', 'F' => 'Mobile', 
            'G' => 'Email', 'H' => 'Address', 'I' => 'City', 'J' => 'Pincode', 
            'K' => 'DOB', 'L' => 'Gender', 'M' => 'Reg Fees', 'N' => 'Paid Through', 
            'O' => 'Cash/Bank/Net Banking/Cheque/Credit Card/UPI', 'P' => 'Reference Number', 
            'Q' => 'For Class', 'R' => 'Last School', 'S' => 'Current Class', 
            'T' => 'Section Admitted', 'U' => 'Country', 'V' => 'Caste', 
            'W' => 'Nationality', 'X' => 'Nationality Proof Number', 'Y' => 'Blood Group'
        ];


    foreach ($headers as $column => $header) {
        $worksheet->setCellValue($column . '1', $header);
        $worksheet->getStyle($column . '1')->getFont()->setBold(true);
    }

    // Helper function to create dropdowns
    function createDropdown($worksheet, $cell, $options) {
        $dropdown = $worksheet->getCell($cell)->getDataValidation();
        $dropdown->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $dropdown->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION);
        $dropdown->setAllowBlank(false);
        $dropdown->setShowInputMessage(true);
        $dropdown->setShowErrorMessage(true);
        $dropdown->setShowDropDown(true);
        $dropdown->setFormula1('"' . implode(',', $options) . '"');
    }

    // Fetch Branches
    $stmt = $conn_rjllearning_specific->prepare("SELECT name FROM branch WHERE institution_id = ?");
    $stmt->execute([$institution_id]);
    $branches = $stmt->fetchAll(PDO::FETCH_COLUMN);
    createDropdown($worksheet, 'A2', $branches);

    // Gender Dropdown
    createDropdown($worksheet, 'L2', ['M', 'F', 'Other']);

    // Fetch Classes
    $stmt = $conn_rjllearning_specific->prepare("SELECT classname FROM class_info WHERE institution_id = ?");
    $stmt->execute([$institution_id]);
    $classes = $stmt->fetchAll(PDO::FETCH_COLUMN);
    createDropdown($worksheet, 'Q2', $classes);
    createDropdown($worksheet, 'S2', $classes);

    // Fetch Sections
    $stmt = $conn_rjllearning_specific->prepare("SELECT section_name FROM section_info WHERE institution_id = ?");
    $stmt->execute([$institution_id]);
    $sections = $stmt->fetchAll(PDO::FETCH_COLUMN);
    createDropdown($worksheet, 'T2', $sections);

    // Fetch Countries
    $stmt = $conn_rjllearning->prepare("SELECT name FROM country_info");
    $stmt->execute();
    $countries = $stmt->fetchAll(PDO::FETCH_COLUMN);
    createDropdown($worksheet, 'U2', $countries);

    // Fetch Castes
    createDropdown($worksheet, 'V2', ['General', 'Obc', 'Sc','St']);

    // Set headers for download
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="StudentsTemplate.xlsx"');
    header('Cache-Control: max-age=0');

    // Save and output the file
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
}

