<?php
require 'db.php';

// set headers to force download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=job_report_' . date('Y-m-d') . '.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, ['Job ID', 'Date', 'Customer', 'Type', 'Location', 'Time Billed', 'Service Order', 'Trouble Ticket', 'Materials Count', 'Notes']);

// Build Query (Same logic as reports.php)
$where = ["1=1"];
$params = [];

$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';
if ($start_date) { $where[] = "j.job_date >= ?"; $params[] = $start_date; }
if ($end_date) { $where[] = "j.job_date <= ?"; $params[] = $end_date; }

$search = $_GET['search'] ?? '';
if ($search) { $where[] = "c.name LIKE ?"; $params[] = "%$search%"; }

$type_filter = $_GET['type'] ?? '';
if ($type_filter) { $where[] = "j.job_type = ?"; $params[] = $type_filter; }

$whereSQL = implode(" AND ", $where);

// Fetch Data
$sql = "SELECT j.*, c.name as customer_name, (SELECT COUNT(*) FROM job_materials jm WHERE jm.job_id = j.id) as item_count 
        FROM jobs j 
        JOIN customers c ON j.customer_id = c.id 
        WHERE $whereSQL 
        ORDER BY j.job_date DESC, j.id DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);

// Loop over rows and write to CSV
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Format time
    $time_str = '';
    if ($row['time_spent']) {
        $h = floor($row['time_spent']/60);
        $m = $row['time_spent']%60;
        $time_str = ($h > 0 ? "{$h}h " : "") . ($m > 0 ? "{$m}m" : "");
    }

    // Prevent CSV injection by prefixing formula-starting chars with a single quote
    $csvRow = array_map(function($val) {
        if (is_string($val) && strlen($val) > 0 && in_array($val[0], ['=', '+', '-', '@', "\t", "\r"])) {
            return "'" . $val;
        }
        return $val;
    }, [
        $row['id'],
        $row['job_date'],
        $row['customer_name'],
        $row['job_type'],
        $row['location'],
        $time_str,
        $row['service_order_number'],
        $row['trouble_ticket_number'],
        $row['item_count'],
        $row['notes']
    ]);
    fputcsv($output, $csvRow);
}

fclose($output);
exit;
?>