<?php
class ExcelGenerator {
    private $data;
    private $headers;
    private $filename;
    
    public function __construct($data, $headers, $filename) {
        $this->data = $data;
        $this->headers = $headers;
        $this->filename = $filename;
    }
    
    public function export() {
        // Set headers for Excel download
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $this->filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Pragma: public');
        header('Expires: 0');
        
        // Create a temporary file to build the Excel file
        $tempFile = tempnam(sys_get_temp_dir(), 'excel_export_');
        $this->createExcelFile($tempFile);
        
        // Output the file
        readfile($tempFile);
        
        // Clean up
        unlink($tempFile);
        exit;
    }
    
    private function createExcelFile($filename) {
        // Create ZIP archive (XLSX is essentially a ZIP file)
        $zip = new ZipArchive();
        if ($zip->open($filename, ZipArchive::CREATE) !== TRUE) {
            throw new Exception('Could not create Excel file');
        }
        
        // Add required Excel files to ZIP
        $this->addContentTypes($zip);
        $this->addRels($zip);
        $this->addApp($zip);
        $this->addCore($zip);
        $this->addWorkbookRels($zip);
        $this->addWorkbook($zip);
        $this->addStyles($zip);
        $this->addWorksheet($zip);
        
        $zip->close();
    }
    
    private function addContentTypes($zip) {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $xml .= '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">';
        $xml .= '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>';
        $xml .= '<Default Extension="xml" ContentType="application/xml"/>';
        $xml .= '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>';
        $xml .= '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
        $xml .= '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>';
        $xml .= '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>';
        $xml .= '<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>';
        $xml .= '</Types>';
        
        $zip->addFromString('[Content_Types].xml', $xml);
    }
    
    private function addRels($zip) {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $xml .= '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
        $xml .= '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>';
        $xml .= '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>';
        $xml .= '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>';
        $xml .= '</Relationships>';
        
        $zip->addFromString('_rels/.rels', $xml);
    }
    
    private function addApp($zip) {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $xml .= '<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">';
        $xml .= '<Application>Recruitment System</Application>';
        $xml .= '<ScaleCrop>false</ScaleCrop>';
        $xml .= '<DocSecurity>0</DocSecurity>';
        $xml .= '<LinksUpToDate>false</LinksUpToDate>';
        $xml .= '<SharedDoc>false</SharedDoc>';
        $xml .= '<HyperlinksChanged>false</HyperlinksChanged>';
        $xml .= '<AppVersion>1.0</AppVersion>';
        $xml .= '</Properties>';
        
        $zip->addFromString('docProps/app.xml', $xml);
    }
    
    private function addCore($zip) {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $xml .= '<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
        $xml .= '<dc:creator>Recruitment System</dc:creator>';
        $xml .= '<dcterms:created xsi:type="dcterms:W3CDTF">' . date('c') . '</dcterms:created>';
        $xml .= '<dc:title>' . htmlspecialchars($this->filename) . '</dc:title>';
        $xml .= '</cp:coreProperties>';
        
        $zip->addFromString('docProps/core.xml', $xml);
    }
    
    private function addWorkbookRels($zip) {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $xml .= '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
        $xml .= '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>';
        $xml .= '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>';
        $xml .= '</Relationships>';
        
        $zip->addFromString('xl/_rels/workbook.xml.rels', $xml);
    }
    
    private function addWorkbook($zip) {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $xml .= '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">';
        $xml .= '<workbookPr defaultThemeVersion="124226"/>';
        $xml .= '<sheets>';
        $xml .= '<sheet name="Applications" sheetId="1" r:id="rId1"/>';
        $xml .= '</sheets>';
        $xml .= '</workbook>';
        
        $zip->addFromString('xl/workbook.xml', $xml);
    }
    
    private function addStyles($zip) {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $xml .= '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
        $xml .= '<fonts count="2">';
        $xml .= '<font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/><scheme val="minor"/></font>';
        $xml .= '<font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/><scheme val="minor"/><b/></font>';
        $xml .= '</fonts>';
        $xml .= '<fills count="3">';
        $xml .= '<fill><patternFill patternType="none"/></fill>';
        $xml .= '<fill><patternFill patternType="gray125"/></fill>';
        $xml .= '<fill><patternFill patternType="solid"><fgColor rgb="FF4472C4"/><bgColor indexed="64"/></patternFill></fill>';
        $xml .= '</fills>';
        $xml .= '<borders count="2">';
        $xml .= '<border><left/><right/><top/><bottom/><diagonal/></border>';
        $xml .= '<border><left style="thin"/><right style="thin"/><top style="thin"/><bottom style="thin"/><diagonal/></border>';
        $xml .= '</borders>';
        $xml .= '<cellXfs count="3">';
        $xml .= '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>';
        $xml .= '<xf numFmtId="0" fontId="1" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1"/>';
        $xml .= '<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>';
        $xml .= '</cellXfs>';
        $xml .= '</styleSheet>';
        
        $zip->addFromString('xl/styles.xml', $xml);
    }
    
    private function addWorksheet($zip) {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $xml .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">';
        $xml .= '<dimension ref="A1:' . $this->getLastCell() . '"/>';
        $xml .= '<sheetViews><sheetView tabSelected="1" workbookViewId="0"><selection activeCell="A1" sqref="A1"/></sheetView></sheetViews>';
        $xml .= '<sheetFormatPr defaultRowHeight="15"/>';
        $xml .= '<cols>';
        
        // Auto-size columns
        for ($i = 0; $i < count($this->headers); $i++) {
            $width = max(strlen($this->headers[$i]), 15);
            $xml .= '<col min="' . ($i + 1) . '" max="' . ($i + 1) . '" width="' . $width . '" customWidth="1"/>';
        }
        $xml .= '</cols>';
        
        $xml .= '<sheetData>';
        
        // Header row
        $xml .= '<row r="1" spans="1:' . count($this->headers) . '">';
        foreach ($this->headers as $colIndex => $header) {
            $cellRef = $this->numberToColumn($colIndex) . '1';
            $xml .= '<c r="' . $cellRef . '" s="1" t="inlineStr"><is><t>' . htmlspecialchars($header) . '</t></is></c>';
        }
        $xml .= '</row>';
        
        // Data rows
        foreach ($this->data as $rowIndex => $row) {
            $rowNum = $rowIndex + 2;
            $xml .= '<row r="' . $rowNum . '" spans="1:' . count($this->headers) . '">';
            
            $colIndex = 0;
            foreach ($row as $value) {
                $cellRef = $this->numberToColumn($colIndex) . $rowNum;
                
                // Handle different data types
                if (strtotime($value) && preg_match('/\d{4}-\d{2}-\d{2}/', $value)) {
                    // Date
                    $excelDate = $this->dateToExcel($value);
                    $xml .= '<c r="' . $cellRef . '" s="2"><v>' . $excelDate . '</v></c>';
                } elseif (is_numeric($value)) {
                    // Number
                    $xml .= '<c r="' . $cellRef . '"><v>' . $value . '</v></c>';
                } else {
                    // Text
                    $xml .= '<c r="' . $cellRef . '" t="inlineStr"><is><t>' . htmlspecialchars($value) . '</t></is></c>';
                }
                $colIndex++;
            }
            $xml .= '</row>';
        }
        
        $xml .= '</sheetData>';
        $xml .= '<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>';
        $xml .= '</worksheet>';
        
        $zip->addFromString('xl/worksheets/sheet1.xml', $xml);
    }
    
    private function numberToColumn($num) {
        $str = '';
        while ($num >= 0) {
            $str = chr($num % 26 + 65) . $str;
            $num = intval($num / 26) - 1;
        }
        return $str;
    }
    
    private function getLastCell() {
        $lastCol = $this->numberToColumn(count($this->headers) - 1);
        $lastRow = count($this->data) + 1;
        return $lastCol . $lastRow;
    }
    
    private function dateToExcel($dateString) {
        $timestamp = strtotime($dateString);
        $days = ($timestamp - strtotime('1900-01-01')) / 86400 + 2; // Excel epoch adjustment
        return $days;
    }
    
    // Static method for applications export
    public static function exportApplications($db, $company_id, $filters = []) {
        // Build SQL query with filters
        $sql = "
            SELECT 
                c.first_name,
                c.last_name,
                c.email,
                c.phone,
                j.title as job_title,
                ja.status,
                ja.applied_at,
                ja.cover_letter,
                cat.name as job_category,
                j.location as job_location,
                j.employment_type,
                j.salary_min,
                j.salary_max
            FROM job_applications ja
            JOIN jobs j ON ja.job_id = j.id
            JOIN candidates c ON ja.candidate_id = c.id
            LEFT JOIN job_categories cat ON j.category_id = cat.id
            WHERE j.company_id = ?
        ";
        
        $params = [$company_id];
        
        // Add filters
        if (!empty($filters['job_id'])) {
            $sql .= " AND j.id = ?";
            $params[] = $filters['job_id'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND ja.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(ja.applied_at) >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(ja.applied_at) <= ?";
            $params[] = $filters['date_to'];
        }
        
        $sql .= " ORDER BY ja.applied_at DESC";
        
        // Execute query
        try {
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            $applications = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Define headers
            $headers = [
                'First Name',
                'Last Name',
                'Email',
                'Phone',
                'Job Title',
                'Job Category',
                'Job Location',
                'Employment Type',
                'Min Salary',
                'Max Salary',
                'Application Status',
                'Applied Date',
                'Cover Letter Preview'
            ];
            
            // Format data
            $data = [];
            foreach ($applications as $app) {
                $data[] = [
                    $app['first_name'],
                    $app['last_name'],
                    $app['email'],
                    $app['phone'] ?: 'N/A',
                    $app['job_title'],
                    $app['job_category'] ?: 'General',
                    $app['job_location'] ?: 'N/A',
                    ucfirst(str_replace('-', ' ', $app['employment_type'])),
                    $app['salary_min'] ? '$' . number_format($app['salary_min']) : 'N/A',
                    $app['salary_max'] ? '$' . number_format($app['salary_max']) : 'N/A',
                    ucfirst($app['status']),
                    $app['applied_at'],
                    $app['cover_letter'] ? substr(strip_tags($app['cover_letter']), 0, 100) . '...' : 'No cover letter'
                ];
            }
            
            // Generate filename
            $filename = 'applications_' . date('Y-m-d_H-i-s');
            if (!empty($filters['job_id'])) {
                $filename = 'job_' . $filters['job_id'] . '_applications_' . date('Y-m-d_H-i-s');
            }
            
            // Create and export Excel file
            $exporter = new ExcelGenerator($data, $headers, $filename);
            $exporter->export();
            
        } catch (PDOException $e) {
            error_log("Excel export error: " . $e->getMessage());
            throw new Exception("Failed to export applications data");
        }
    }
}
?>