<?php
class ExcelExporter {
    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 CSV download
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $this->filename . '.csv"');
        header('Cache-Control: max-age=0');
        header('Pragma: public');
        header('Expires: 0');
        
        // Create file pointer connected to the output stream
        $output = fopen('php://output', 'w');
        
        // Add BOM to fix UTF-8 in Excel
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
        
        // Output the column headings
        fputcsv($output, $this->headers);
        
        // Output the data rows
        foreach ($this->data as $row) {
            // Format the row data
            $formatted_row = [];
            foreach ($row as $value) {
                // Format dates for Excel
                if (strtotime($value) && preg_match('/\d{4}-\d{2}-\d{2}/', $value)) {
                    $formatted_row[] = date('m/d/Y H:i:s', strtotime($value));
                } else {
                    $formatted_row[] = $value;
                }
            }
            fputcsv($output, $formatted_row);
        }
        
        // Close the file pointer
        fclose($output);
        exit;
    }
    
    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[] = [
                    'first_name' => $app['first_name'],
                    'last_name' => $app['last_name'],
                    'email' => $app['email'],
                    'phone' => $app['phone'] ?: 'N/A',
                    'job_title' => $app['job_title'],
                    'job_category' => $app['job_category'] ?: 'General',
                    'job_location' => $app['job_location'] ?: 'N/A',
                    'employment_type' => ucfirst(str_replace('-', ' ', $app['employment_type'])),
                    'salary_min' => $app['salary_min'] ? '$' . number_format($app['salary_min']) : 'N/A',
                    'salary_max' => $app['salary_max'] ? '$' . number_format($app['salary_max']) : 'N/A',
                    'status' => ucfirst($app['status']),
                    'applied_at' => $app['applied_at'],
                    'cover_letter' => $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
            $exporter = new ExcelExporter($data, $headers, $filename);
            $exporter->export();
            
        } catch (PDOException $e) {
            error_log("Excel export error: " . $e->getMessage());
            throw new Exception("Failed to export applications data");
        }
    }
}
?>