<?php
ob_start();
require_once __DIR__ . '/../config/auth.php';
$user = require_auth();

try {
    $db        = db();
    $type      = $_GET['type']      ?? '';
    $format    = $_GET['format']    ?? 'csv';
    $client_id = (int)($_GET['clients_id'] ?? 0);
    $status    = $_GET['status']    ?? '';

    $rows = []; $headers = []; $title = '';

    switch ($type) {

        case 'clients':
            $title   = 'Clients';
            $headers = ['Code','Name','Province','Email','Phone','Address'];
            $stmt    = $db->query(
                'SELECT c.client_code, c.clients_name, p.provinces_name, c.email, c.phone, c.address
                 FROM clients c LEFT JOIN provinces p ON p.record_id = c.provinces_id
                 ORDER BY c.clients_name ASC');
            $rows = $stmt->fetchAll(PDO::FETCH_NUM);
            break;

        case 'employees':
            $title   = 'Employees';
            $headers = ['Name','Surname','ID/Passport','Client','Cell','Company No','Occupation',
                        'Badge No','Team No','TEBA No','Industry','Induction Date','Medical Date'];
            $where  = $client_id ? 'WHERE e.clients_id = ?' : '';
            $params = $client_id ? [$client_id] : [];
            $stmt   = $db->prepare(
                "SELECT e.client_employees_name, e.surname, e.i_doc_passport, c.clients_name,
                        e.cell, e.company_number, e.occupation, e.badge_number,
                        e.team_number, e.teba_number, e.industry, e.induction_date, e.medical_date
                 FROM client_employees e LEFT JOIN clients c ON c.record_id = e.clients_id
                 $where ORDER BY e.surname, e.client_employees_name ASC LIMIT 3000");
            $stmt->execute($params);
            $rows = $stmt->fetchAll(PDO::FETCH_NUM);
            break;

        case 'sites':
            $title   = 'Client Sites';
            $headers = ['Site Name','Client'];
            $where  = $client_id ? 'WHERE s.clients_id = ?' : '';
            $params = $client_id ? [$client_id] : [];
            $stmt   = $db->prepare(
                "SELECT s.client_sites_name, c.clients_name
                 FROM client_sites s LEFT JOIN clients c ON c.record_id = s.clients_id
                 $where ORDER BY c.clients_name, s.client_sites_name ASC");
            $stmt->execute($params);
            $rows = $stmt->fetchAll(PDO::FETCH_NUM);
            break;

        case 'bookings':
            $title   = 'Bookings';
            $headers = ['Booking #','Date Booked','Date Approved','Date Done',
                        'Client','Assessor','Status','Notes','Employee Count'];
            $where  = '1=1';
            $params = [];
            if ($status) { $where .= ' AND b.status = ?'; $params[] = $status; }
            if ((int)$user['user_type_id'] !== 1) { $where .= ' AND b.safesure_users_id = ?'; $params[] = $user['record_id']; }
            $stmt = $db->prepare(
                "SELECT b.booking_number, b.date_booked, b.date_approved, b.date_done,
                        c.clients_name, u.safesure_users_name, b.status, b.notes,
                        GREATEST(0, LENGTH(TRIM(b.client_employees,',.')) - LENGTH(REPLACE(TRIM(b.client_employees,',.'),',','')) + 1) as emp_count
                 FROM bookings b
                 LEFT JOIN clients c ON c.record_id = b.clients_id
                 LEFT JOIN safesure_users u ON u.record_id = b.safesure_users_id
                 WHERE $where ORDER BY b.record_id DESC");
            $stmt->execute($params);
            $rows = $stmt->fetchAll(PDO::FETCH_NUM);
            break;

        default:
            json_error('Unknown export type: ' . $type);
    }

    $filename = strtolower(str_replace(' ', '_', $title)) . '_' . date('Y-m-d');
    $count    = count($rows);

    // ── CSV download ──────────────────────────────────────────────────────
    if ($format === 'csv') {
        ob_clean();
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '.csv"');
        header('Pragma: no-cache');
        $out = fopen('php://output', 'w');
        fputs($out, "\xEF\xBB\xBF"); // UTF-8 BOM for Excel
        fputcsv($out, $headers);
        foreach ($rows as $row) fputcsv($out, array_map(fn($v) => $v ?? '', $row));
        fclose($out);
        exit;
    }

    // ── HTML for browser print-to-PDF ─────────────────────────────────────
    if ($format === 'html') {
        ob_clean();
        header('Content-Type: text/html; charset=utf-8');

        $head_cells = implode('', array_map(fn($h) => '<th>' . htmlspecialchars($h) . '</th>', $headers));
        $body_rows  = '';
        foreach ($rows as $i => $row) {
            $cells = implode('', array_map(fn($v) => '<td>' . htmlspecialchars((string)($v ?? '')) . '</td>', $row));
            $body_rows .= '<tr' . ($i % 2 ? ' class="alt"' : '') . '>' . $cells . '</tr>';
        }
        $date_str = date('d M Y');

        echo '<!DOCTYPE html><html><head><meta charset="UTF-8">';
        echo '<title>' . htmlspecialchars($title) . '</title>';
        echo '<style>';
        echo '*{box-sizing:border-box;margin:0;padding:0}';
        echo 'body{font-family:Arial,sans-serif;font-size:11px;color:#1a2332;padding:20px}';
        echo '.hdr{display:flex;align-items:center;justify-content:space-between;margin-bottom:16px;padding-bottom:10px;border-bottom:3px solid #e85d04}';
        echo '.brand{display:flex;align-items:center;gap:10px}';
        echo '.logo{width:36px;height:36px;background:#e85d04;border-radius:6px;color:#fff;font-weight:700;font-size:13px;display:flex;align-items:center;justify-content:center}';
        echo '.bname{font-size:16px;font-weight:700}';
        echo '.bsub{font-size:10px;color:#64748b}';
        echo '.meta{text-align:right;font-size:10px;color:#64748b}';
        echo '.meta b{color:#1a2332}';
        echo 'table{width:100%;border-collapse:collapse;font-size:10px;margin-top:6px}';
        echo 'th{background:#0d1b2a;color:#fff;padding:6px 8px;text-align:left;font-size:9px;text-transform:uppercase;letter-spacing:.04em;white-space:nowrap}';
        echo 'td{padding:5px 8px;border-bottom:1px solid #e2e8f0;vertical-align:top}';
        echo 'tr.alt td{background:#f8fafc}';
        echo '.foot{margin-top:14px;display:flex;justify-content:space-between;color:#94a3b8;font-size:9px;border-top:1px solid #e2e8f0;padding-top:6px}';
        echo '@media print{@page{margin:1cm}body{padding:0}}';
        echo '</style></head><body>';

        echo '<div class="hdr">';
        echo '<div class="brand"><div class="logo">SS</div>';
        echo '<div><div class="bname">SafeSure</div><div class="bsub">' . htmlspecialchars($title) . ' Report</div></div></div>';
        echo '<div class="meta"><div>Date: <b>' . $date_str . '</b></div><div>Records: <b>' . $count . '</b></div></div>';
        echo '</div>';

        echo '<table><thead><tr>' . $head_cells . '</tr></thead>';
        echo '<tbody>' . $body_rows . '</tbody></table>';

        echo '<div class="foot"><span>SafeSure Competency Assessment System</span><span>Confidential — ' . htmlspecialchars($title) . ' ' . $date_str . '</span></div>';
        echo '<script>window.onload=function(){window.print();}<\/script>';
        echo '</body></html>';
        exit;
    }

    json_error('Unknown format: ' . $format);

} catch (Throwable $e) {
    json_error('export.php [' . $e->getLine() . ']: ' . $e->getMessage(), 500);
}