<?php
/**
 * Enterprise HRM ERP System - Resigned & Abscondee Export to Excel
 * Exports with same design and structure as the report table
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

$siteId = getCurrentSiteId();
$selectedMonth = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');
$selectedYear = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y');
$searchQuery = isset($_GET['search']) ? trim($_GET['search']) : '';

// Validate
if ($selectedMonth < 1 || $selectedMonth > 12) $selectedMonth = (int)date('m');
if ($selectedYear < 2020 || $selectedYear > 2100) $selectedYear = (int)date('Y');

$entries = [];
$siteName = '';

try {
    $db = getDB();
    
    // Build query
    $whereConditions = ["ra.site_id = ?"];
    $params = [$siteId];
    
    // Month/Year filter
    $monthYear = date('M-Y', mktime(0, 0, 0, $selectedMonth, 1, $selectedYear));
    $whereConditions[] = "ra.month = ?";
    $params[] = $monthYear;
    
    // Search filter
    if (!empty($searchQuery)) {
        $whereConditions[] = "(ra.emp_id LIKE ? OR ra.name LIKE ?)";
        $searchParam = "%{$searchQuery}%";
        $params[] = $searchParam;
        $params[] = $searchParam;
    }
    
    $whereClause = implode(' AND ', $whereConditions);
    
    $stmt = $db->prepare("
        SELECT ra.*, e.employee_code as actual_emp_code
        FROM resigned_abscondee ra
        LEFT JOIN employees e ON ra.employee_id = e.employee_id
        WHERE {$whereClause}
        ORDER BY ra.sl_no ASC, ra.exit_id ASC
    ");
    $stmt->execute($params);
    $entries = $stmt->fetchAll();
    
    // Get site name
    $stmt = $db->prepare("SELECT site_name FROM sites WHERE site_id = ?");
    $stmt->execute([$siteId]);
    $site = $stmt->fetch();
    $siteName = $site['site_name'] ?? '';
    
} catch (Exception $e) {
    error_log("Error loading export data: " . $e->getMessage());
    die('Error loading data: ' . $e->getMessage());
}

// Check if PhpSpreadsheet is available
$phpspreadsheet_available = class_exists('\PhpOffice\PhpSpreadsheet\Spreadsheet');

$filename = 'Resigned_Abscondee_Report_' . date('M-Y', mktime(0, 0, 0, $selectedMonth, 1, $selectedYear)) . '.xlsx';

if ($phpspreadsheet_available) {
    // Use PhpSpreadsheet for proper Excel format
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Resigned & Abscondee');
    
    // Header style - Dark blue background (#1e40af), white text, bold, centered
    $headerStyle = [
        'font' => [
            'bold' => true,
            'color' => ['rgb' => 'FFFFFF'],
            'size' => 11
        ],
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'startColor' => ['rgb' => '1e40af'] // Dark blue
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['rgb' => '1e3a8a']
            ]
        ]
    ];
    
    // Data row style - White background, borders
    $dataStyle = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'startColor' => ['rgb' => 'FFFFFF'] // White background
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['rgb' => 'd1d5db']
            ]
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
        ]
    ];
    
    // Resigned badge style - Light blue background (#dbeafe) with dark blue text (#1e40af)
    $resignedStyle = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'startColor' => ['rgb' => 'dbeafe'] // Light blue background
        ],
        'font' => [
            'bold' => true,
            'color' => ['rgb' => '1e40af'], // Dark blue text
            'size' => 11
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['rgb' => 'd1d5db']
            ]
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
        ]
    ];
    
    // Abscondee badge style - Light red background (#fee2e2) with dark red text (#991b1b)
    $abscondeeStyle = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'startColor' => ['rgb' => 'fee2e2'] // Light red background
        ],
        'font' => [
            'bold' => true,
            'color' => ['rgb' => '991b1b'], // Dark red text
            'size' => 11
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['rgb' => 'd1d5db']
            ]
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
        ]
    ];
    
    // Add header row
    $headers = [
        'Sl No',
        'Emp Id',
        'Name',
        'Designation',
        'Category',
        'Resigned/ Abscondee',
        'Site Name',
        'Location',
        'Last Date of Working',
        'Resignation Submitted',
        'Notice Period served',
        'Remarks'
    ];
    
    $col = 'A';
    foreach ($headers as $header) {
        $sheet->setCellValue($col . '1', $header);
        $sheet->getStyle($col . '1')->applyFromArray($headerStyle);
        $col++;
    }
    
    // Set row height for header
    $sheet->getRowDimension(1)->setRowHeight(35);
    
    // Style for empty date cells (gray text)
    $emptyDateStyle = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'startColor' => ['rgb' => 'FFFFFF']
        ],
        'font' => [
            'color' => ['rgb' => '9ca3af'] // Gray color for empty dates
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['rgb' => 'd1d5db']
            ]
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
        ]
    ];
    
    // Write data rows
    $row = 2;
    foreach ($entries as $entry) {
        $col = 'A';
        
        // Sl No
        $sheet->setCellValue($col . $row, $entry['sl_no'] ?? '');
        $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
        $col++;
        
        // Emp Id - Bold
        $empId = $entry['emp_id'] ?: ($entry['actual_emp_code'] ?? '');
        $sheet->setCellValue($col . $row, $empId);
        $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
        $sheet->getStyle($col . $row)->getFont()->setBold(true);
        $col++;
        
        // Name - Left aligned, Bold
        $sheet->setCellValue($col . $row, $entry['name'] ?? '');
        $nameStyle = $dataStyle;
        $nameStyle['alignment']['horizontal'] = \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT;
        $sheet->getStyle($col . $row)->applyFromArray($nameStyle);
        $sheet->getStyle($col . $row)->getFont()->setBold(true);
        $col++;
        
        // Designation
        $sheet->setCellValue($col . $row, $entry['designation'] ?? '');
        $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
        $col++;
        
        // Category
        $sheet->setCellValue($col . $row, $entry['category'] ?? '');
        $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
        $col++;
        
        // Resigned/Abscondee - With badge styling
        $status = $entry['resigned_abscondee'] ?? 'Resigned';
        $sheet->setCellValue($col . $row, $status);
        if (strtolower($status) === 'resigned') {
            $sheet->getStyle($col . $row)->applyFromArray($resignedStyle);
        } else {
            $sheet->getStyle($col . $row)->applyFromArray($abscondeeStyle);
        }
        $col++;
        
        // Site Name
        $sheet->setCellValue($col . $row, $entry['site_name'] ?? '');
        $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
        $col++;
        
        // Location
        $sheet->setCellValue($col . $row, $entry['location'] ?? '');
        $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
        $col++;
        
        // Last Date of Working
        if ($entry['last_date_of_working']) {
            $lastDate = date('d-M-Y', strtotime($entry['last_date_of_working']));
            $sheet->setCellValue($col . $row, $lastDate);
            $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
        } else {
            $sheet->setCellValue($col . $row, '-');
            $sheet->getStyle($col . $row)->applyFromArray($emptyDateStyle);
        }
        $col++;
        
        // Resignation Submitted
        if ($entry['resignation_submitted']) {
            $resignDate = date('d-M-Y', strtotime($entry['resignation_submitted']));
            $sheet->setCellValue($col . $row, $resignDate);
            $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
        } else {
            $sheet->setCellValue($col . $row, '-');
            $sheet->getStyle($col . $row)->applyFromArray($emptyDateStyle);
        }
        $col++;
        
        // Notice Period served
        $sheet->setCellValue($col . $row, $entry['notice_period_served'] ?? '');
        $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
        $col++;
        
        // Remarks - Left aligned
        $sheet->setCellValue($col . $row, $entry['remark'] ?? '');
        $remarksStyle = $dataStyle;
        $remarksStyle['alignment']['horizontal'] = \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT;
        $sheet->getStyle($col . $row)->applyFromArray($remarksStyle);
        $col++;
        
        // Set row height for data rows
        $sheet->getRowDimension($row)->setRowHeight(25);
        
        $row++;
    }
    
    // Set column widths to match report design
    $sheet->getColumnDimension('A')->setWidth(10);  // Sl No
    $sheet->getColumnDimension('B')->setWidth(15); // Emp Id
    $sheet->getColumnDimension('C')->setWidth(25); // Name
    $sheet->getColumnDimension('D')->setWidth(20); // Designation
    $sheet->getColumnDimension('E')->setWidth(18); // Category
    $sheet->getColumnDimension('F')->setWidth(22); // Resigned/Abscondee
    $sheet->getColumnDimension('G')->setWidth(18); // Site Name
    $sheet->getColumnDimension('H')->setWidth(18); // Location
    $sheet->getColumnDimension('I')->setWidth(22); // Last Date of Working
    $sheet->getColumnDimension('J')->setWidth(25); // Resignation Submitted
    $sheet->getColumnDimension('K')->setWidth(22); // Notice Period served
    $sheet->getColumnDimension('L')->setWidth(35); // Remarks
    
    // Freeze header row for better scrolling
    $sheet->freezePane('A2');
    
    // Set headers and output
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Cache-Control: max-age=0');
    
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
    
} else {
    // Fallback to HTML/Excel format with same styling
    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . str_replace('.xlsx', '.xls', $filename) . '"');
    header('Pragma: no-cache');
    header('Expires: 0');
    
    ?>
    <html xmlns:x="urn:schemas-microsoft-com:office:excel">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <!--[if gte mso 9]>
        <xml>
            <x:ExcelWorkbook>
                <x:ExcelWorksheets>
                    <x:ExcelWorksheet>
                        <x:Name>Resigned & Abscondee</x:Name>
                        <x:WorksheetOptions>
                            <x:DefaultRowHeight>300</x:DefaultRowHeight>
                        </x:WorksheetOptions>
                    </x:ExcelWorksheet>
                </x:ExcelWorksheets>
            </x:ExcelWorkbook>
        </xml>
        <![endif]-->
        <style>
            table {
                border-collapse: collapse;
                width: 100%;
                font-family: Arial, sans-serif;
                font-size: 11pt;
            }
            th {
                background-color: #1e40af;
                color: white;
                font-weight: bold;
                padding: 12px 8px;
                text-align: center;
                border: 1px solid #1e3a8a;
                white-space: nowrap;
                height: 35px;
            }
            td {
                padding: 10px 8px;
                border: 1px solid #d1d5db;
                background: white;
                text-align: center;
                height: 25px;
            }
            .text-left {
                text-align: left;
            }
            .status-resigned {
                background-color: #dbeafe;
                color: #1e40af;
                font-weight: bold;
                padding: 4px 12px;
                border-radius: 12px;
                display: inline-block;
            }
            .status-abscondee {
                background-color: #fee2e2;
                color: #991b1b;
                font-weight: bold;
                padding: 4px 12px;
                border-radius: 12px;
                display: inline-block;
            }
        </style>
    </head>
    <body>
        <table>
            <thead>
                <tr>
                    <th>Sl No</th>
                    <th>Emp Id</th>
                    <th>Name</th>
                    <th>Designation</th>
                    <th>Category</th>
                    <th>Resigned/ Abscondee</th>
                    <th>Site Name</th>
                    <th>Location</th>
                    <th>Last Date of Working</th>
                    <th>Resignation Submitted</th>
                    <th>Notice Period served</th>
                    <th>Remarks</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($entries as $entry): ?>
                <tr>
                    <td><?php echo htmlspecialchars($entry['sl_no'] ?? ''); ?></td>
                    <td><strong><?php echo htmlspecialchars($entry['emp_id'] ?: ($entry['actual_emp_code'] ?? '')); ?></strong></td>
                    <td class="text-left"><strong><?php echo htmlspecialchars($entry['name'] ?? ''); ?></strong></td>
                    <td><?php echo htmlspecialchars($entry['designation'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($entry['category'] ?? ''); ?></td>
                    <td style="<?php 
                        $status = strtolower($entry['resigned_abscondee'] ?? 'resigned');
                        if ($status === 'resigned') {
                            echo 'background-color: #dbeafe; color: #1e40af; font-weight: bold;';
                        } else {
                            echo 'background-color: #fee2e2; color: #991b1b; font-weight: bold;';
                        }
                    ?>">
                        <?php echo htmlspecialchars($entry['resigned_abscondee'] ?? 'Resigned'); ?>
                    </td>
                    <td><?php echo htmlspecialchars($entry['site_name'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($entry['location'] ?? ''); ?></td>
                    <td style="<?php echo !$entry['last_date_of_working'] ? 'color: #9ca3af;' : ''; ?>">
                        <?php if ($entry['last_date_of_working']): ?>
                            <?php echo date('d-M-Y', strtotime($entry['last_date_of_working'])); ?>
                        <?php else: ?>
                            -
                        <?php endif; ?>
                    </td>
                    <td style="<?php echo !$entry['resignation_submitted'] ? 'color: #9ca3af;' : ''; ?>">
                        <?php if ($entry['resignation_submitted']): ?>
                            <?php echo date('d-M-Y', strtotime($entry['resignation_submitted'])); ?>
                        <?php else: ?>
                            -
                        <?php endif; ?>
                    </td>
                    <td><?php echo htmlspecialchars($entry['notice_period_served'] ?? ''); ?></td>
                    <td class="text-left"><?php echo htmlspecialchars($entry['remark'] ?? ''); ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </body>
    </html>
    <?php
    exit;
}

