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

require_once '../../../../resources/base_path_include.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;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;


$salaryData = getSalaryDetails($conn_rjllearning_specific, $institution_id, $branch_id, $start_date, $end_date, $role, $other_staff_role, $category_id);
exportSalaryToExcel($conn_rjllearning, $conn_rjllearning_specific, $salaryData, $institution_id, $branch_id, $start_date, $end_date);

function exportSalaryToExcel($conn_rjllearning, $conn_rjllearning_specific, $salaryData, $institution_id, $branch_id, $start_date, $end_date) 
{
    ob_start();

    $institution = getInstitution($conn_rjllearning, $institution_id);

    if ($branch_id !== "all") {
        $branch = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id);
        $branch_name = $branch['name'];
    }

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    // Set headers
    $sheet->setCellValue('A1', $institution['institution_name'])
          ->setCellValue('A2', $branch_name)
          ->setCellValue('A3', 'Salary Period: ' . date('d-m-Y', strtotime($start_date)) . ' to ' . date('d-m-Y', strtotime($end_date)));
    
    // Style for headers
    $headerStyle = [
        'font' => ['bold' => true, 'size' => 12],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]
    ];
    
    $sheet->getStyle('A1:A3')->applyFromArray($headerStyle);
    
    // Define columns
    $columns = [
        'A' => 'Faculty ID',
        'B' => 'Name',
        'C' => 'Designation',
        'D' => 'Role',
        'E' => 'Basic Salary',
        'F' => 'Per Day Salary',
        'G' => 'Working Days',
        'H' => 'Present Days',
        'I' => 'Half Days',
        'J' => 'Absent Days',
        'K' => 'Leave Days',
        'L' => 'Allowed Leaves',
        'M' => 'Deduction Leaves',
        'N' => 'Leave Deduction',
        'O' => 'Absent Deduction',
        'P' => 'House Rent Allowance (HRA)',
        'Q' => 'Dearness Allowance (DA)',
        'R' => 'Basic Salary Component',
        'S' => 'Provident Fund (PF)',
        'T' => 'Gross Salary',
        'U' => 'Total Deductions',
        'V' => 'Net Salary'
    ];
    
    // Set column headers
    $row = 5;
    foreach ($columns as $col => $title) {
        $sheet->setCellValue($col.$row, $title);
        $sheet->getStyle($col.$row)->getFont()->setBold(true);
        $sheet->getStyle($col.$row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    // Add salary data
    $row = 6;
    foreach ($salaryData as $staff) {
        $sheet->setCellValue('A'.$row, $staff['faculty_id']);
        $sheet->setCellValue('B'.$row, $staff['name']);
        $sheet->setCellValue('C'.$row, $staff['designation']);
        $sheet->setCellValue('D'.$row, $staff['role']);

        // Format currency values
        $sheet->setCellValue('E'.$row, $staff['basic_salary']);
        $sheet->getStyle('E'.$row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

        $sheet->setCellValue('F'.$row, $staff['per_day_salary']);
        $sheet->setCellValue('G'.$row, $staff['working_days']);
        $sheet->setCellValue('H'.$row, $staff['present_days']);
        $sheet->setCellValue('I'.$row, $staff['half_days']);
        $sheet->setCellValue('J'.$row, $staff['absent_days']);
        $sheet->setCellValue('K'.$row, $staff['leave_days']);
        $sheet->setCellValue('L'.$row, $staff['allowed_leaves']);
        $sheet->setCellValue('M'.$row, $staff['deduction_leaves']);
        $sheet->setCellValue('N'.$row, $staff['leave_deduction']);
        $sheet->setCellValue('O'.$row, $staff['absent_days_salary_cut']);

        // Salary Components
        $sheet->setCellValue('P'.$row, $staff['salary_components']['House Rent Allowance (HRA)'] ?? 0);
        $sheet->setCellValue('Q'.$row, $staff['salary_components']['Dearness Allowance (DA)'] ?? 0);
        $sheet->setCellValue('R'.$row, $staff['salary_components']['Basic Salary'] ?? 0);
        $sheet->setCellValue('S'.$row, $staff['salary_components']['Provident Fund (PF)'] ?? 0);

        // Gross Salary, Deductions, and Net Salary
        $sheet->setCellValue('T'.$row, $staff['gross_salary']);
        $sheet->setCellValue('U'.$row, $staff['total_deductions']);
        $sheet->setCellValue('V'.$row, $staff['net_salary']);

        // Apply currency formatting
        foreach (['N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V'] as $col) {
            $sheet->getStyle($col.$row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        }

        $row++;
    }

    // Add borders
    $lastColumn = $sheet->getHighestColumn();
    $lastRow = $sheet->getHighestRow();
    $sheet->getStyle('A5:'.$lastColumn.$lastRow)
          ->getBorders()
          ->getAllBorders()
          ->setBorderStyle(Border::BORDER_THIN);
    
    // Center align all cells
    $sheet->getStyle('A5:'.$lastColumn.$lastRow)
          ->getAlignment()
          ->setHorizontal(Alignment::HORIZONTAL_CENTER);
    
    // Set auto filter
    $sheet->setAutoFilter('A5:'.$lastColumn.'5');
    
    // Output the file
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Faculty Salary Report.xlsx"');
    header('Cache-Control: max-age=0');
    
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}

