<?php
declare(strict_types=1);

function handleReferenceData(PDO $pdo): void
{
    $vehicles = $pdo->query("
        SELECT
            v.id,
            CONCAT(v.plate_number, ' - ', COALESCE(v.vehicle_model, v.vehicle_type, 'Vehicle')) AS label,
            v.plate_number,
            CONCAT_WS(' ', NULLIF(v.vehicle_model, ''), NULLIF(v.vehicle_type, '')) AS vehicle_name
        FROM vehicles v
        WHERE v.deleted_at IS NULL
        ORDER BY v.plate_number ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $drivers = $pdo->query("
        SELECT
            d.id,
            COALESCE(d.full_name, CONCAT_WS(' ', d.first_name, d.surname), d.driver_id_code) AS label
        FROM drivers d
        WHERE d.active_status = 1
          AND d.deleted_at IS NULL
        ORDER BY label ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $users = $pdo->query("
        SELECT id, name AS label
        FROM users
        WHERE COALESCE(is_active, 1) = 1
          AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $workshops = $pdo->query("
        SELECT id, name AS label, contact_person, phone, email, address
        FROM workshops 
        WHERE status = 'active'
          AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    respond([
        'status' => 'success',
        'data' => [
            'vehicles' => $vehicles,
            'drivers' => $drivers,
            'users' => $users,
            'workshops' => $workshops,
            'stage_actions' => stageActionDefinitions(),
            'stage_role_matrix' => MAINTENANCE_STAGE_ROLE_MATRIX,
            'stage_order' => MAINTENANCE_STAGE_ORDER,
            'rfq_policy' => maintenanceRfqPolicy()
        ]
    ]);
}

function handleRfqList(PDO $pdo): void
{
    $jobId = (int)($_GET['job_id'] ?? 0);
    if ($jobId <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid maintenance job']);
    }

    $stmt = $pdo->prepare("
        SELECT
            r.id AS rfq_id,
            r.maintenance_job_id,
            r.workshop_id,
            w.name AS workshop_name,
            mj.workshop AS final_workshop_name,
            r.rfq_number,
            r.rfq_sent_date,
            r.status AS rfq_status,
            CASE WHEN COALESCE(mj.workshop, '') <> '' AND mj.workshop = w.name THEN 1 ELSE 0 END AS is_final,
            q.id AS quote_id,
            q.quote_file,
            q.quote_amount,
            q.quote_received_date,
            q.notes AS quote_notes,
            q.status AS quote_status
        FROM maintenance_rfqs r
        INNER JOIN maintenance_jobs mj ON mj.id = r.maintenance_job_id
        LEFT JOIN workshops w ON w.id = r.workshop_id
        LEFT JOIN (
            SELECT qq.*
            FROM workshop_quotes qq
            INNER JOIN (
                SELECT rfq_id, MAX(id) AS latest_id
                FROM workshop_quotes
                WHERE deleted_at IS NULL
                GROUP BY rfq_id
            ) latest ON latest.latest_id = qq.id
            WHERE qq.deleted_at IS NULL
        ) q ON q.rfq_id = r.id
        WHERE r.maintenance_job_id = :job_id
          AND r.deleted_at IS NULL
        ORDER BY r.created_at DESC, r.id DESC
    ");
    $stmt->execute(['job_id' => $jobId]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $rows = attachWorkshopQuoteLineItems($pdo, $rows);

    $stats = getRfqStats($pdo, $jobId);
    $lineItems = fetchMaintenanceLineItems($pdo, $jobId);
    $job = fetchMaintenanceJob($pdo, $jobId) ?: [];

    respond([
        'status' => 'success',
        'data' => [
            'rows' => $rows,
            'stats' => $stats,
            'final_workshop' => (($rows[0]['final_workshop_name'] ?? null) ?: null),
            'line_items' => $lineItems,
            'job' => [
                'id' => $jobId,
                'workflow_stage' => $job['workflow_stage'] ?? null,
                'cost_of_repair' => $job['cost_of_repair'] ?? null,
                'quote_amount' => $job['quote_amount'] ?? null,
                'vetted_amount' => $job['vetted_amount'] ?? null,
                'approved_amount' => $job['approved_amount'] ?? null
            ]
        ]
    ]);
}

function handleAddRfqVendor(PDO $pdo): void
{
    enforceMaintenanceWritePermission();

    $jobId = (int)($_POST['job_id'] ?? 0);
    $workshopId = normalizeNullableInt($_POST['workshop_id'] ?? null);

    if ($jobId <= 0 || $workshopId === null) {
        respond(['status' => 'error', 'message' => 'Job and workshop are required']);
    }

    $job = fetchMaintenanceJob($pdo, $jobId);
    if (!$job) {
        respond(['status' => 'error', 'message' => 'Maintenance job not found']);
    }
    enforceMaintenanceJobHasLineItems($pdo, $jobId, 'Add work order line items before inviting RFQ vendors.');

    $name = fetchWorkshopNameById($pdo, $workshopId);
    if ($name === null) {
        respond(['status' => 'error', 'message' => 'Selected workshop is invalid or inactive']);
    }

    createOrUpdateRfqForJob($pdo, $jobId, $job, $workshopId, 'sent');
    advanceMaintenanceRfqStage($pdo, $job, STAGE_RFQ_SENT, STATUS_IN_PROGRESS, 'send_rfq', 'RFQ vendor invited: ' . $name);

    $stats = getRfqStats($pdo, $jobId);
    respond(['status' => 'success', 'data' => ['stats' => $stats]]);
}

function handleUploadQuote(PDO $pdo): void
{
    enforceMaintenanceWritePermission();

    $jobId = (int)($_POST['job_id'] ?? 0);
    $rfqId = (int)($_POST['rfq_id'] ?? 0);
    $quoteReceivedDate = normalizeNullableDate($_POST['quote_received_date'] ?? null);
    $notes = normalizeNullableString($_POST['notes'] ?? null);
    $quoteLineItems = parseWorkshopQuoteLineItems($_POST['quote_line_items'] ?? '');

    if ($jobId <= 0 || $rfqId <= 0) {
        respond(['status' => 'error', 'message' => 'Job and RFQ are required']);
    }
    if (!$quoteLineItems) {
        respond(['status' => 'error', 'message' => 'At least one quote line item is required']);
    }
    $quoteAmount = number_format(calculateWorkshopQuoteLineItemsTotal($quoteLineItems), 2, '.', '');

    $job = fetchMaintenanceJob($pdo, $jobId);
    if (!$job) {
        respond(['status' => 'error', 'message' => 'Maintenance job not found']);
    }
    enforceMaintenanceJobHasLineItems($pdo, $jobId, 'Add work order line items before capturing vendor quotes.');

    $stmtRfq = $pdo->prepare("SELECT * FROM maintenance_rfqs WHERE id = :rfq_id AND maintenance_job_id = :job_id AND deleted_at IS NULL LIMIT 1");
    $stmtRfq->execute(['rfq_id' => $rfqId, 'job_id' => $jobId]);
    $rfq = $stmtRfq->fetch(PDO::FETCH_ASSOC);
    if (!$rfq) {
        respond(['status' => 'error', 'message' => 'RFQ record not found for this job']);
    }

    $quoteFile = null;
    if (isset($_FILES['quote_file']) && is_array($_FILES['quote_file']) && ($_FILES['quote_file']['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_NO_FILE) {
        $quoteFile = storeQuoteFile($_FILES['quote_file']);
    }

    $stmtExisting = $pdo->prepare("SELECT * FROM workshop_quotes WHERE rfq_id = ? AND deleted_at IS NULL ORDER BY id DESC LIMIT 1");
    $stmtExisting->execute([$rfqId]);
    $existing = $stmtExisting->fetch(PDO::FETCH_ASSOC);
    $quoteId = 0;

    if ($existing) {
        $stmt = $pdo->prepare("
            UPDATE workshop_quotes
            SET quote_file = :quote_file,
                quote_amount = :quote_amount,
                quote_received_date = :quote_received_date,
                notes = :notes,
                status = 'submitted'
            WHERE id = :id
              AND deleted_at IS NULL
        ");
        $stmt->execute([
            'quote_file' => $quoteFile ?? ($existing['quote_file'] ?? null),
            'quote_amount' => $quoteAmount,
            'quote_received_date' => $quoteReceivedDate ?? date('Y-m-d'),
            'notes' => $notes,
            'id' => (int)$existing['id']
        ]);
        $quoteId = (int)$existing['id'];
    } else {
        $stmt = $pdo->prepare("
            INSERT INTO workshop_quotes (
                rfq_id,
                quote_file,
                quote_amount,
                quote_received_date,
                notes,
                status
            ) VALUES (
                :rfq_id,
                :quote_file,
                :quote_amount,
                :quote_received_date,
                :notes,
                'submitted'
            )
        ");
        $stmt->execute([
            'rfq_id' => $rfqId,
            'quote_file' => $quoteFile,
            'quote_amount' => $quoteAmount,
            'quote_received_date' => $quoteReceivedDate ?? date('Y-m-d'),
            'notes' => $notes
        ]);
        $quoteId = (int)$pdo->lastInsertId();
    }
    saveWorkshopQuoteLineItems($pdo, $quoteId, $quoteLineItems);

    $stmtStatus = $pdo->prepare("UPDATE maintenance_rfqs SET status = 'quoted' WHERE id = ? AND deleted_at IS NULL");
    $stmtStatus->execute([$rfqId]);
    advanceMaintenanceRfqStage($pdo, $job, STAGE_QUOTE_SUBMITTED, STATUS_IN_PROGRESS, 'submit_quote', 'Quote submitted for RFQ #' . ($rfq['rfq_number'] ?? $rfqId));

    audit(
        $pdo,
        (int)($_SESSION['user_id'] ?? 0),
        'update',
        'maintenance_rfq_quote',
        $rfqId,
        'RFQ quote uploaded/updated',
        null,
        [
            'job_id' => $jobId,
            'rfq_id' => $rfqId,
            'quote_amount' => $quoteAmount,
            'quote_received_date' => $quoteReceivedDate,
            'quote_file' => $quoteFile,
            'quote_line_items' => count($quoteLineItems)
        ]
    );

    $stats = getRfqStats($pdo, $jobId);
    respond(['status' => 'success', 'data' => ['stats' => $stats]]);
}

function handleSelectFinalWorkshop(PDO $pdo): void
{
    enforceMaintenanceApprovalPermission();

    $jobId = (int)($_POST['job_id'] ?? 0);
    $rfqId = (int)($_POST['rfq_id'] ?? 0);
    if ($jobId <= 0 || $rfqId <= 0) {
        respond(['status' => 'error', 'message' => 'Job and RFQ are required']);
    }

    $stmt = $pdo->prepare("
        SELECT
            r.*,
            w.name AS workshop_name,
            q.id AS quote_id,
            q.quote_amount,
            q.quote_file,
            q.quote_received_date,
            q.status AS quote_status
        FROM maintenance_rfqs r
        LEFT JOIN workshops w ON w.id = r.workshop_id
        LEFT JOIN (
            SELECT qq.*
            FROM workshop_quotes qq
            INNER JOIN (
                SELECT rfq_id, MAX(id) AS latest_id
                FROM workshop_quotes
                WHERE deleted_at IS NULL
                GROUP BY rfq_id
            ) latest ON latest.latest_id = qq.id
            WHERE qq.deleted_at IS NULL
        ) q ON q.rfq_id = r.id
        WHERE r.id = :rfq_id AND r.maintenance_job_id = :job_id
          AND r.deleted_at IS NULL
        LIMIT 1
    ");
    $stmt->execute(['rfq_id' => $rfqId, 'job_id' => $jobId]);
    $rfq = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$rfq) {
        respond(['status' => 'error', 'message' => 'RFQ not found for this job']);
    }

    $workshopName = trim((string)($rfq['workshop_name'] ?? ''));
    if ($workshopName === '') {
        respond(['status' => 'error', 'message' => 'RFQ workshop has no valid name']);
    }
    if ((float)($rfq['quote_amount'] ?? 0) <= 0) {
        respond(['status' => 'error', 'message' => 'A submitted quote amount is required before final workshop selection']);
    }
    enforceWorkshopQuoteHasLineItems($pdo, (int)($rfq['quote_id'] ?? 0), 'Quote line items are required before final workshop selection.');

    $before = fetchMaintenanceJob($pdo, $jobId);
    if (!$before) {
        respond(['status' => 'error', 'message' => 'Maintenance job not found']);
    }
    enforceMaintenanceJobHasLineItems($pdo, $jobId, 'Add work order line items before selecting a final workshop.');

    $stmtJob = $pdo->prepare("UPDATE maintenance_jobs SET workshop = :workshop WHERE id = :job_id AND deleted_at IS NULL");
    $stmtJob->execute(['workshop' => $workshopName, 'job_id' => $jobId]);

    $stmtRfqStatus = $pdo->prepare("
        UPDATE maintenance_rfqs
        SET status = CASE
            WHEN id = :rfq_id THEN 'selected'
            WHEN maintenance_job_id = :job_id AND status IN ('sent','quoted','selected') THEN 'rejected'
            ELSE status
        END
        WHERE maintenance_job_id = :job_id
          AND deleted_at IS NULL
    ");
    $stmtRfqStatus->execute(['rfq_id' => $rfqId, 'job_id' => $jobId]);

    logMaintenanceAction(
        $pdo,
        $jobId,
        'select_final_workshop',
        'Final Workshop Selected',
        (string)($before['workflow_stage'] ?? ''),
        (string)($before['workflow_stage'] ?? ''),
        null,
        'Final workshop selected: ' . $workshopName
    );

    audit(
        $pdo,
        (int)($_SESSION['user_id'] ?? 0),
        'update',
        'maintenance_job',
        $jobId,
        'Final workshop selected from RFQ',
        $before,
        ['workshop' => $workshopName, 'rfq_id' => $rfqId]
    );

    respond(['status' => 'success', 'data' => ['workshop' => $workshopName]]);
}

function handleRequisitionReport(PDO $pdo): void
{
    $start = normalizeNullableDate($_GET['start_date'] ?? null);
    $end = normalizeNullableDate($_GET['end_date'] ?? null);
    $status = normalizeNullableString($_GET['status'] ?? null);

    $where = ["mj.deleted_at IS NULL", "mj.approved_amount > 0", "mj.workflow_stage IN ('" . STAGE_CLOSED . "', '" . STAGE_INTERNAL_CONTROL . "', '" . STAGE_REQUISITION_RAISED . "', '" . STAGE_PAYMENT_MADE . "')"];
    $params = [];

    if ($start) {
        $where[] = "mj.created_at >= :start";
        $params['start'] = $start . ' 00:00:00';
    }
    if ($end) {
        $where[] = "mj.created_at <= :end";
        $params['end'] = $end . ' 23:59:59';
    }
    if ($status) {
        $where[] = "mj.requisition_status = :status";
        $params['status'] = $status;
    }

    $whereSql = "WHERE " . implode(' AND ', $where);

    $sql = "
        SELECT 
            mj.id,
            mj.work_order_number,
            mj.created_at,
            mj.approved_amount,
            mj.workshop,
            mj.requisition_status,
            v.plate_number
        FROM maintenance_jobs mj
        INNER JOIN vehicles v ON v.id = mj.vehicle_id
        $whereSql
        ORDER BY mj.created_at DESC
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Calculate KPIs based on the filtered set
    $totalCount = count($rows);
    $totalValue = 0;
    $paidValue = 0;
    $pendingValue = 0;

    foreach ($rows as $row) {
        $amt = (float)($row['approved_amount'] ?? 0);
        $totalValue += $amt;
        if (in_array(($row['requisition_status'] ?? ''), ['Paid', STAGE_PAYMENT_MADE], true)) {
            $paidValue += $amt;
        } else {
            $pendingValue += $amt;
        }
    }

    respond([
        'status' => 'success',
        'data' => $rows,
        'kpis' => [
            'total_count' => $totalCount,
            'total_value' => $totalValue,
            'paid_value' => $paidValue,
            'pending_value' => $pendingValue
        ]
    ]);
}

// ─── RFQ helper functions ──────────────────────────────────
function fetchSelectedFinalWorkshopName(PDO $pdo, int $jobId): ?string
{
    $stmt = $pdo->prepare("
        SELECT w.name
        FROM maintenance_rfqs r
        LEFT JOIN workshops w ON w.id = r.workshop_id
        WHERE r.maintenance_job_id = ?
          AND r.status = 'selected'
          AND r.deleted_at IS NULL
        ORDER BY r.id DESC
        LIMIT 1
    ");
    $stmt->execute([$jobId]);
    $name = trim((string)$stmt->fetchColumn());
    return $name === '' ? null : $name;
}

function fetchSelectedFinalQuoteId(PDO $pdo, int $jobId): ?int
{
    $stmt = $pdo->prepare("
        SELECT q.id
        FROM maintenance_rfqs r
        INNER JOIN (
            SELECT qq.*
            FROM workshop_quotes qq
            INNER JOIN (
                SELECT rfq_id, MAX(id) AS latest_id
                FROM workshop_quotes
                WHERE deleted_at IS NULL
                GROUP BY rfq_id
            ) latest ON latest.latest_id = qq.id
            WHERE qq.deleted_at IS NULL
        ) q ON q.rfq_id = r.id
        WHERE r.maintenance_job_id = ?
          AND r.status = 'selected'
          AND r.deleted_at IS NULL
        ORDER BY r.id DESC
        LIMIT 1
    ");
    $stmt->execute([$jobId]);
    $quoteId = (int)$stmt->fetchColumn();
    return $quoteId > 0 ? $quoteId : null;
}

function fetchWorkshopNameById(PDO $pdo, int $workshopId): ?string
{
    if ($workshopId <= 0) {
        return null;
    }
    $stmt = $pdo->prepare("SELECT name FROM workshops WHERE id = ? AND status = 'active' AND deleted_at IS NULL LIMIT 1");
    $stmt->execute([$workshopId]);
    $name = $stmt->fetchColumn();
    return $name !== false ? (string)$name : null;
}

function getRfqStats(PDO $pdo, int $jobId): array
{
    $vendors = 0;
    $quotes = 0;

    try {
        $stmtV = $pdo->prepare('SELECT COUNT(DISTINCT workshop_id) FROM maintenance_rfqs WHERE maintenance_job_id = ? AND deleted_at IS NULL');
        $stmtV->execute([$jobId]);
        $vendors = (int)$stmtV->fetchColumn();
    } catch (Throwable) {
        $vendors = 0;
    }

    try {
        $stmtQ = $pdo->prepare("
            SELECT COUNT(DISTINCT q.rfq_id)
            FROM workshop_quotes q
            INNER JOIN maintenance_rfqs r ON r.id = q.rfq_id
            WHERE r.maintenance_job_id = :job_id
              AND r.deleted_at IS NULL
              AND q.deleted_at IS NULL
              AND (
                    q.quote_amount IS NOT NULL
                 OR q.quote_file IS NOT NULL
                 OR q.quote_received_date IS NOT NULL
                 OR (q.notes IS NOT NULL AND q.notes <> '')
                 OR q.status <> 'pending'
              )
        ");
        $stmtQ->execute(['job_id' => $jobId]);
        $quotes = (int)$stmtQ->fetchColumn();
    } catch (Throwable) {
        $quotes = 0;
    }

    return ['vendors' => $vendors, 'quotes' => $quotes];
}

function parseWorkshopQuoteLineItems(mixed $raw): array
{
    $decoded = json_decode((string)$raw, true);
    if (!is_array($decoded)) {
        return [];
    }

    $items = [];
    foreach ($decoded as $row) {
        if (!is_array($row)) {
            continue;
        }
        $description = trim((string)($row['description'] ?? ''));
        $cost = normalizeNullableDecimal($row['cost'] ?? ($row['quoted_cost'] ?? null));
        $sourceLineItemId = normalizeNullableInt($row['source_line_item_id'] ?? ($row['maintenance_line_item_id'] ?? null));

        if ($description === '' && $cost === null) {
            continue;
        }
        if ($description === '' || $cost === null || (float)$cost <= 0) {
            respond(['status' => 'error', 'message' => 'Each quote line item must include a description and quoted amount greater than 0']);
        }

        $items[] = [
            'source_line_item_id' => $sourceLineItemId,
            'description' => $description,
            'cost' => number_format((float)$cost, 2, '.', '')
        ];
    }

    return $items;
}

function calculateWorkshopQuoteLineItemsTotal(array $items): float
{
    $total = 0.0;
    foreach ($items as $item) {
        $total += (float)$item['cost'];
    }
    return $total;
}

function saveWorkshopQuoteLineItems(PDO $pdo, int $quoteId, array $items): void
{
    ensureWorkshopQuoteLineItemsTable($pdo);
    $pdo->prepare("UPDATE workshop_quote_line_items SET deleted_at = NOW() WHERE quote_id = ? AND deleted_at IS NULL")->execute([$quoteId]);

    $stmt = $pdo->prepare("
        INSERT INTO workshop_quote_line_items (
            quote_id,
            maintenance_line_item_id,
            item_description,
            quoted_cost,
            sort_order
        ) VALUES (
            :quote_id,
            :maintenance_line_item_id,
            :item_description,
            :quoted_cost,
            :sort_order
        )
    ");

    foreach ($items as $index => $item) {
        $stmt->execute([
            'quote_id' => $quoteId,
            'maintenance_line_item_id' => $item['source_line_item_id'],
            'item_description' => $item['description'],
            'quoted_cost' => $item['cost'],
            'sort_order' => $index + 1
        ]);
    }
}

function enforceWorkshopQuoteHasLineItems(PDO $pdo, int $quoteId, string $message): void
{
    if ($quoteId <= 0) {
        respond(['status' => 'error', 'message' => $message]);
    }
    ensureWorkshopQuoteLineItemsTable($pdo);
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM workshop_quote_line_items WHERE quote_id = ? AND deleted_at IS NULL");
    $stmt->execute([$quoteId]);
    if ((int)$stmt->fetchColumn() <= 0) {
        respond(['status' => 'error', 'message' => $message]);
    }
}

function enforceSelectedFinalQuoteHasLineItems(PDO $pdo, int $jobId, string $message): void
{
    $quoteId = fetchSelectedFinalQuoteId($pdo, $jobId);
    enforceWorkshopQuoteHasLineItems($pdo, $quoteId ?? 0, $message);
}

function attachWorkshopQuoteLineItems(PDO $pdo, array $rows): array
{
    ensureWorkshopQuoteLineItemsTable($pdo);
    $quoteIds = [];
    foreach ($rows as $index => $row) {
        $rows[$index]['quote_line_items'] = [];
        $quoteId = (int)($row['quote_id'] ?? 0);
        if ($quoteId > 0) {
            $quoteIds[] = $quoteId;
        }
    }

    $quoteIds = array_values(array_unique($quoteIds));
    if (!$quoteIds) {
        return $rows;
    }

    $placeholders = implode(',', array_fill(0, count($quoteIds), '?'));
    $stmt = $pdo->prepare("
        SELECT
            quote_id,
            maintenance_line_item_id AS source_line_item_id,
            item_description AS description,
            quoted_cost AS cost
        FROM workshop_quote_line_items
        WHERE quote_id IN ($placeholders)
          AND deleted_at IS NULL
        ORDER BY quote_id ASC, sort_order ASC, id ASC
    ");
    $stmt->execute($quoteIds);

    $grouped = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $item) {
        $grouped[(int)$item['quote_id']][] = $item;
    }

    foreach ($rows as $index => $row) {
        $quoteId = (int)($row['quote_id'] ?? 0);
        $rows[$index]['quote_line_items'] = $grouped[$quoteId] ?? [];
    }

    return $rows;
}

function createOrUpdateRfqForJob(PDO $pdo, int $jobId, array $job, ?int $selectedWorkshopId = null, string $rfqStatus = 'sent'): void
{
    $workshopId = $selectedWorkshopId ?? 0;
    if ($workshopId <= 0) {
        $workshopName = trim((string)($job['workshop'] ?? ''));
        if ($workshopName === '') {
            throw new RuntimeException('Workshop is required before sending RFQ');
        }

        try {
            $stmtWorkshop = $pdo->prepare("SELECT id FROM workshops WHERE name = ? AND deleted_at IS NULL LIMIT 1");
            $stmtWorkshop->execute([$workshopName]);
            $workshopId = (int)$stmtWorkshop->fetchColumn();
        } catch (Throwable) {
            throw new RuntimeException('RFQ setup not available: workshops lookup failed');
        }
    }

    if ($workshopId <= 0) {
        throw new RuntimeException('Selected workshop is not available in workshop directory');
    }

    $rfqNumber = generateRfqNumber($jobId);

    try {
        $stmtExisting = $pdo->prepare("SELECT id FROM maintenance_rfqs WHERE maintenance_job_id = ? AND workshop_id = ? AND deleted_at IS NULL LIMIT 1");
        $stmtExisting->execute([$jobId, $workshopId]);
        $existingId = (int)$stmtExisting->fetchColumn();

        if ($existingId > 0) {
            $stmtUpdate = $pdo->prepare("
                UPDATE maintenance_rfqs
                SET rfq_number = :rfq_number,
                    rfq_sent_date = :rfq_sent_date,
                    status = :status
                WHERE id = :id
                  AND deleted_at IS NULL
            ");
            $stmtUpdate->execute([
                'rfq_number' => $rfqNumber,
                'rfq_sent_date' => date('Y-m-d'),
                'status' => $rfqStatus,
                'id' => $existingId
            ]);
            return;
        }

        $stmtInsert = $pdo->prepare("
            INSERT INTO maintenance_rfqs (
                maintenance_job_id,
                workshop_id,
                rfq_number,
                rfq_sent_date,
                status
            ) VALUES (
                :maintenance_job_id,
                :workshop_id,
                :rfq_number,
                :rfq_sent_date,
                :status
            )
        ");
        $stmtInsert->execute([
            'maintenance_job_id' => $jobId,
            'workshop_id' => $workshopId,
            'rfq_number' => $rfqNumber,
            'rfq_sent_date' => date('Y-m-d'),
            'status' => $rfqStatus
        ]);
    } catch (Throwable) {
        throw new RuntimeException('RFQ table not available. Run RFQ migration first.');
    }
}

function sendRfqInviteToWorkshop(PDO $pdo, int $jobId, ?int $workshopId = null): void
{
    if ($jobId <= 0) {
        return;
    }

    // Find the RFQ record for this job and workshop (latest)
    $params = [$jobId];
    $sql = "SELECT r.*, w.email AS workshop_email, w.name AS workshop_name, w.contact_person FROM maintenance_rfqs r LEFT JOIN workshops w ON w.id = r.workshop_id WHERE r.maintenance_job_id = ? AND r.deleted_at IS NULL";
    if ($workshopId !== null && $workshopId > 0) {
        $sql .= " AND r.workshop_id = ?";
        $params[] = $workshopId;
    }
    $sql .= " ORDER BY r.id DESC LIMIT 1";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rfq = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$rfq) {
        return;
    }

    $workshopEmail = trim((string)($rfq['workshop_email'] ?? ''));
    $workshopName = trim((string)($rfq['workshop_name'] ?? 'Partner'));
    $contactPerson = trim((string)($rfq['contact_person'] ?? ''));

    if ($workshopEmail === '' || !filter_var($workshopEmail, FILTER_VALIDATE_EMAIL)) {
        return;
    }

    // Fetch job details
    $stmtJob = $pdo->prepare("
        SELECT
            mj.id,
            CONCAT_WS(' - ', v.plate_number, NULLIF(CONCAT_WS(' ', NULLIF(v.vehicle_model, ''), NULLIF(v.vehicle_type, '')), '')) AS vehicle,
            mj.complaint_title,
            mj.complaint_description
        FROM maintenance_jobs mj
        LEFT JOIN vehicles v ON v.id = mj.vehicle_id
        WHERE mj.id = ?
          AND mj.deleted_at IS NULL
        LIMIT 1
    ");
    $stmtJob->execute([$jobId]);
    $jobDetails = $stmtJob->fetch(PDO::FETCH_ASSOC) ?: [];

    $subject = 'Request for Quote: ' . ($jobDetails['complaint_title'] ?? 'Maintenance RFQ');
    $msgParts = [];
    $msgParts[] = '<p>Dear ' . htmlspecialchars($workshopName, ENT_QUOTES | ENT_SUBSTITUTE) . ',</p>';
    $msgParts[] = '<p>You have been invited to provide a quote for a maintenance job (Job ID: ' . htmlspecialchars((string)$jobId, ENT_QUOTES | ENT_SUBSTITUTE) . ').</p>';
    if ($jobDetails) {
        $msgParts[] = '<ul>';
        $msgParts[] = '<li><strong>Vehicle:</strong> ' . htmlspecialchars((string)($jobDetails['vehicle'] ?? '-'), ENT_QUOTES | ENT_SUBSTITUTE) . '</li>';
        $msgParts[] = '<li><strong>Title:</strong> ' . htmlspecialchars((string)($jobDetails['complaint_title'] ?? '-'), ENT_QUOTES | ENT_SUBSTITUTE) . '</li>';
        $msgParts[] = '<li><strong>Description:</strong> ' . nl2br(htmlspecialchars((string)($jobDetails['complaint_description'] ?? '-'), ENT_QUOTES | ENT_SUBSTITUTE)) . '</li>';
        $msgParts[] = '</ul>';
    }

    if (!empty($rfq['rfq_number'])) {
        $msgParts[] = '<p>RFQ Number: ' . htmlspecialchars((string)$rfq['rfq_number'], ENT_QUOTES | ENT_SUBSTITUTE) . '</p>';
    }

    $config = require __DIR__ . '/../../config/alerts.php';
    $appUrl = $config['app_url'] ?? (getenv('FLEET_APP_URL') ?: '');
    if ($appUrl) {
        $link = rtrim((string)$appUrl, '/') . '/maintenance.php?job_id=' . rawurlencode((string)$jobId);
        $msgParts[] = '<p><a href="' . htmlspecialchars($link, ENT_QUOTES | ENT_SUBSTITUTE) . '">Open job details</a></p>';
    }

    $msgParts[] = '<p>Regards,<br/>Fleet Operations</p>';
    $message = implode("\n", $msgParts);

    $sent = sendMail($workshopEmail, $subject, $message, ['from_name' => 'Fleet Operations', 'to_name' => $contactPerson ?: null]);
    if ($sent) {
        $stmtUpd = $pdo->prepare('UPDATE maintenance_rfqs SET rfq_sent_date = COALESCE(rfq_sent_date, CURDATE()), updated_at = NOW() WHERE id = ? AND deleted_at IS NULL');
        $stmtUpd->execute([(int)$rfq['id']]);

        alertLogDelivery($pdo, [
            'event_key' => 'maintenance.rfq_sent',
            'channel' => 'email',
            'user_id' => 0,
            'recipient' => $workshopEmail,
            'subject' => $subject,
            'message' => strip_tags($message),
            'status' => 'sent',
            'provider_response' => null,
            'payload_json' => json_encode(['job_id' => $jobId, 'rfq_id' => (int)$rfq['id']]),
            'reference_type' => 'maintenance_job',
            'reference_id' => $jobId,
        ]);
    }
}
