<?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;

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


use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

if ($postdata['function_type'] == "download_faculty_excel") {
   
    
    if ($role === 'Teacher' || $role === 'Principal') {
        // Fetch all branches (since institution_id is not required)
        
        $institution_id = $jwt_validate->data->institution_id;
        
    } else {
        // Fetch branches based on institution_id
         $institution_id = $postdata['institution_id'];
    }

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

    // Set column headers
   $headers = [
    'A' => 'Branch', 
    'B' => 'Name', 
    'C' => 'Gender',
    'D' => 'Email', 
    'E' => 'Mobile', 
    'F' => 'Designation', 
    'G' => 'Role', 
    'H' => 'Father Name', 
    'I' => 'Mother Name', 
    'J' => 'Spouse Name', 
    'K' => 'Nationality', 
    'L' => 'DOB', 
    'M' => 'Address', 
    'N' => 'City', 
    'O' => 'State', 
    'P' => 'Pincode', 
    'Q' => 'Country', 
    'R' => 'Date of Joining', 
    'S' => 'Registered on EPF',       // new field (1 = Yes, 0 = No), can be null
    'T' => 'Date of Joining as per Registration', // new field, can be null
    'U' => 'Account Name', 
    'V' => 'Account Holder Name', 
    'W' => 'Account Number', 
    'X' => 'IFSC Code', 
    'Y' => 'Branch Address', 
    'Z' => 'Account Type', 
    'AA' => 'Aadhaar Card', 
    'AB' => 'PAN Card', 
    'AC' => 'Driving License', 
    'AD' => 'Driving License Expiry Date',
    'AE' => 'Other Staff Groups'
];


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

    // Helper function to create dropdowns for multiple rows
    function createDropdown($worksheet, $column, $startRow, $endRow, $options) {
        for ($row = $startRow; $row <= $endRow; $row++) {
            $cell = $column . $row;
            $dropdown = $worksheet->getCell($cell)->getDataValidation();
            $dropdown->setType(DataValidation::TYPE_LIST);
            $dropdown->setErrorStyle(DataValidation::STYLE_INFORMATION);
            $dropdown->setAllowBlank(false);
            $dropdown->setShowInputMessage(true);
            $dropdown->setShowErrorMessage(true);
            $dropdown->setShowDropDown(true);
            $dropdown->setFormula1('"' . implode(',', $options) . '"');
        }
    }

    // Assume $role is already defined and holds the user's role

    if ($role === 'Teacher' || $role === 'Principal') {
        // Fetch all branches (since institution_id is not required)
        $branch_id = $jwt_validate->data->branch_id;
        $stmt = $conn_rjllearning_specific->prepare("SELECT name FROM branch WHERE id = ?");
        $stmt->execute([$branch_id]);
    } else {
        // Fetch branches based on institution_id
        $stmt = $conn_rjllearning_specific->prepare("SELECT name FROM branch WHERE institution_id = ?");
        $stmt->execute([$institution_id]);
    }
    
    $branches = $stmt->fetchAll(PDO::FETCH_COLUMN);

    
    $stmt = $conn_rjllearning_specific->prepare("SELECT group_name FROM other_staff_groups WHERE institution_id = ?");
    $stmt->execute([$institution_id]);
    $other_staff_groups = $stmt->fetchAll(PDO::FETCH_COLUMN);

    // Fetch States
    $stmt = $conn_rjllearning->prepare("SELECT state FROM state_info WHERE status = ?");
    $stmt->execute([1]);
    $states = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    // Fetch Countries
    $stmt = $conn_rjllearning->prepare("SELECT name FROM country_info");
    $stmt->execute();
    $countries = $stmt->fetchAll(PDO::FETCH_COLUMN);

    // Apply dropdowns
    createDropdown($worksheet, 'A', 2, 100, $branches);  // Branches
    createDropdown($worksheet, 'C', 2, 100, ['M', 'F', 'Other']);  // Gender
    createDropdown($worksheet, 'Q', 2, 100, $countries);  // Countries
    createDropdown($worksheet, 'O', 2, 100, $states);  // States
    createDropdown($worksheet, 'AC', 2, 100, $other_staff_groups);  // States

    createDropdown($worksheet, 'G', 2, 100, ['Other', 'Principal', 'Teacher']);  // Roles

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

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



