<?php
/**
 * تصدير لقطة مركز العمليات (CSV) — يُستدعى من index.php قبل إخراج HTML.
 *
 * @var PDO $db
 * @var Auth $auth
 */
use App\Core\Auth;
use App\Core\Tenant;

if (!($auth instanceof Auth) || !$auth->check()) {
    http_response_code(403);
    exit;
}

$tenantId = Tenant::id();
$branchFilter = isset($_GET['branch_id']) ? (int)$_GET['branch_id'] : 0;
$userBranchId = $auth->branchId();
if ($userBranchId !== null && $branchFilter === 0) {
    $branchFilter = $userBranchId;
}
$branchCond = $branchFilter > 0 ? ' AND o.branch_id = ' . (int)$branchFilter : '';

$orderTenantAnd = '';
try {
    $db->query('SELECT tenant_id FROM lab_orders LIMIT 1');
    $orderTenantAnd = ' AND tenant_id = ' . (int)$tenantId;
} catch (Throwable $e) {
}

$opsBacklogOrders = 0;
$opsInProgressItems = 0;
$opsSamplesReceived = 0;
$opsAvgTatMinutesToday = 0;
$opsCriticalPending = 0;
$todayOrders = 0;
$pendingResults = 0;
$pendingDoctorOrders = 0;

try {
    $stmt = $db->query("SELECT COUNT(DISTINCT o.id) AS c FROM order_items oi JOIN lab_orders o ON o.id = oi.order_id WHERE oi.status = 'pending'" . str_replace('tenant_id', 'o.tenant_id', $orderTenantAnd) . $branchCond);
    $opsBacklogOrders = (int)($stmt->fetch()['c'] ?? 0);
} catch (Throwable $e) {
}
try {
    $stmt = $db->query("SELECT COUNT(*) AS c FROM order_items oi JOIN lab_orders o ON o.id = oi.order_id WHERE oi.status = 'in_progress'" . str_replace('tenant_id', 'o.tenant_id', $orderTenantAnd) . $branchCond);
    $opsInProgressItems = (int)($stmt->fetch()['c'] ?? 0);
} catch (Throwable $e) {
}
try {
    $sampleTenantAnd = '';
    try {
        $db->query('SELECT tenant_id FROM samples LIMIT 1');
        $sampleTenantAnd = ' AND s.tenant_id = ' . (int)$tenantId;
    } catch (Throwable $e) {
    }
    $stmt = $db->query("SELECT COUNT(*) AS c FROM samples s WHERE s.status IN ('received','processing')" . $sampleTenantAnd);
    $opsSamplesReceived = (int)($stmt->fetch()['c'] ?? 0);
} catch (Throwable $e) {
}
try {
    $stmt = $db->query("SELECT AVG(TIMESTAMPDIFF(MINUTE, o.order_date, COALESCE(r.validated_at, r.result_date))) AS avg_tat
                        FROM results r
                        JOIN order_items oi ON oi.id = r.order_item_id
                        JOIN lab_orders o ON o.id = oi.order_id
                        WHERE DATE(COALESCE(r.validated_at, r.result_date)) = CURDATE()" . str_replace('tenant_id', 'o.tenant_id', $orderTenantAnd) . $branchCond);
    $opsAvgTatMinutesToday = (int)round((float)($stmt->fetch()['avg_tat'] ?? 0));
} catch (Throwable $e) {
}
try {
    $stmt = $db->query("SELECT COUNT(*) AS c FROM results r
                        JOIN order_items oi ON oi.id = r.order_item_id
                        JOIN lab_orders o ON o.id = oi.order_id
                        WHERE r.flag = 'C' AND (r.validated_at IS NULL OR r.validated_at = '0000-00-00 00:00:00')" . str_replace('tenant_id', 'o.tenant_id', $orderTenantAnd) . $branchCond);
    $opsCriticalPending = (int)($stmt->fetch()['c'] ?? 0);
} catch (Throwable $e) {
}
try {
    $sql = 'SELECT COUNT(*) AS c FROM lab_orders WHERE DATE(order_date) = CURDATE()' . $orderTenantAnd;
    if ($branchFilter > 0) {
        $sql .= ' AND branch_id = ' . (int)$branchFilter;
    }
    $todayOrders = (int)($db->query($sql)->fetch()['c'] ?? 0);
} catch (Throwable $e) {
}
try {
    $stmt = $db->query("SELECT COUNT(*) AS c FROM order_items oi JOIN lab_orders o ON o.id = oi.order_id WHERE oi.status IN ('pending','in_progress')" . str_replace('tenant_id', 'o.tenant_id', $orderTenantAnd) . $branchCond);
    $pendingResults = (int)($stmt->fetch()['c'] ?? 0);
} catch (Throwable $e) {
}
try {
    $stmt = $db->query(
        "SELECT COUNT(DISTINCT o.id) AS c FROM order_items oi
         JOIN lab_orders o ON o.id = oi.order_id
         WHERE oi.status IN ('pending','in_progress')
         AND COALESCE(o.doctor_id, 0) > 0
         AND o.status != 'cancelled'" . str_replace('tenant_id', 'o.tenant_id', $orderTenantAnd) . $branchCond
    );
    $pendingDoctorOrders = (int)($stmt->fetch()['c'] ?? 0);
} catch (Throwable $e) {
}

$filename = 'ops_snapshot_' . date('Y-m-d_His') . '.csv';
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
echo "\xEF\xBB\xBF";

$out = fopen('php://output', 'w');
fputcsv($out, ['exported_at', 'tenant_id', 'branch_filter', 'backlog_orders', 'in_progress_items', 'samples_received', 'avg_tat_minutes_today', 'critical_pending_validation', 'today_orders', 'pending_results_items', 'pending_doctor_orders']);
fputcsv($out, [
    date('c'),
    (string)$tenantId,
    (string)$branchFilter,
    (string)$opsBacklogOrders,
    (string)$opsInProgressItems,
    (string)$opsSamplesReceived,
    (string)$opsAvgTatMinutesToday,
    (string)$opsCriticalPending,
    (string)$todayOrders,
    (string)$pendingResults,
    (string)$pendingDoctorOrders,
]);
fclose($out);
