<?php

namespace App\Http\Controllers\Api\V1;

use App\Http\Concerns\AuthorizesCompanyAccess;
use App\Http\Controllers\Controller;
use App\Services\AccountStorageResolver;
use App\Services\DataverseService;
use App\Services\IdentifyDocumentService;
use App\Services\SharePointService;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Throwable;

/**
 * Handles the SharePoint batch-folder upload flow that mirrors the Power App:
 *
 *   1. POST /api/v1/upload/init   — fetch batch prefix, build folder name,
 *                                   create SharePoint folder, optionally store
 *                                   batch summary in Dataverse.
 *
 *   2. POST /api/v1/upload/file   — upload a single file into the batch folder.
 *                                   Power Automate picks it up automatically.
 */
class BatchUploadController extends Controller
{
    use AuthorizesCompanyAccess;

    public function __construct(
        private DataverseService          $dv,
        private SharePointService         $sp,
        private IdentifyDocumentService   $identify,
        private \App\Services\Identity\AppUserService $appUsers,
        private AccountStorageResolver    $storage,
    ) {}

    // ─── Step 1: initialise batch ────────────────────────────────────────────

    /**
     * POST /api/v1/upload/init
     *
     * Body (JSON):
     *   company_id          uuid    required
     *   doc_type            string  optional  e.g. "Invoice", "Cashbook"
     *   sub_doc_type        string  optional  e.g. "AR Invoice", "OR", "PV"
     *   label               string  optional  batch-level label
     *   summary_mode        bool    optional
     *   summary_vendor_id   uuid    optional
     *   summary_account_id  uuid    optional
     *   summary_main_desc   string  optional
     *   summary_detail_desc string  optional
     */
    public function init(Request $request): JsonResponse
    {
        $request->validate([
            'company_id'          => ['required', 'uuid'],
            'doc_type'            => ['nullable', 'string'],
            'sub_doc_type'        => ['nullable', 'string', 'max:100'],
            'label'               => ['nullable', 'string', 'max:100'],
            'summary_mode'        => ['nullable', 'boolean'],
            'summary_vendor_id'   => ['nullable', 'uuid'],
            'summary_account_id'  => ['nullable', 'uuid'],
            'summary_main_desc'   => ['nullable', 'string', 'max:500'],
            'summary_detail_desc' => ['nullable', 'string', 'max:100'],
            'file_count'          => ['nullable', 'integer', 'min:0'],
            'auto_split'          => ['nullable', 'boolean'],
            'sub_split'           => ['nullable', 'boolean'],
        ]);

        $companyId = $request->input('company_id');
        if ($deny = $this->authorizeCompany($companyId)) return $deny;

        // 1. Get company invoice folder
        try {
            $company = $this->dv->find('cr6f2_companies', $companyId, [
                'select' => ['cr6f2_invoicefolder', 'cr6f2_name'],
            ]);
        } catch (Throwable $e) {
            return response()->json(['success' => false, 'message' => 'Company not found.'], 404);
        }

        $invoiceFolder = $company['cr6f2_invoicefolder'] ?? null;
        if (!$invoiceFolder) {
            return response()->json([
                'success' => false,
                'message' => 'Company has no invoice folder configured.',
            ], 422);
        }

        // 2. Get batch prefix from Dataverse environment variables
        try {
            $prefix = $this->getBatchPrefix();
        } catch (Throwable $e) {
            Log::warning('Could not fetch batch prefix, using default.', ['error' => $e->getMessage()]);
            $prefix = 'BATCH';
        }

        // 3. Build batch folder name. BatchSummary is the only grouping artifact —
        // files always land flat in {invoiceFolder}/Documents/. The folder name
        // here only appears on the BatchSummary record for tracking; no SharePoint
        // sub-folder is created (that would trigger the legacy Power Automate
        // create/modify watcher, which we no longer want firing).
        $docTypeLabel = $request->input('doc_type'); // "Invoice", "Cashbook", etc.
        $timestamp    = now()->format('YmdHis');
        $batchFolder  = "{$prefix}_{$timestamp}";

        // 5. Create Batch Summary record in Dataverse
        $batchSummaryId    = null;
        $batchSummaryError = null;
        try {
            $subDocType = $request->input('sub_doc_type');

            $payload = [
                'cr6f2_batchfoldername' => $batchFolder,
                'crf61_doctype'         => $docTypeLabel ?? 'Unknown',
                'cr6f2_processeddoc'    => 0,
                'cr6f2_autosplit'       => $request->boolean('auto_split'),
            ];

            if ($subDocType) {
                $payload['cr6f2_subdoctype'] = $subDocType;
            }

            if ($request->input('summary_mode')) {
                if ($vendorId = $request->input('summary_vendor_id')) {
                    $payload['cr6f2_DebtorCreditorLookup@odata.bind'] =
                        '/cr6f2_debtorcreditormappings(' . $vendorId . ')';
                }
                if ($accountId = $request->input('summary_account_id')) {
                    $payload['cr6f2_AccountNumberLookup@odata.bind'] =
                        '/cr6f2_accountnumbermaps(' . $accountId . ')';
                }
                if ($mainDesc = $request->input('summary_main_desc')) {
                    $payload['cr6f2_maindescription'] = $mainDesc;
                }
                if ($detailDesc = $request->input('summary_detail_desc')) {
                    $payload['cr6f2_itemdescription'] = $detailDesc;
                }
            }

            if ($fileCount = $request->input('file_count')) {
                $payload['cr6f2_filecount'] = (int) $fileCount;
            }

            if ($batchLabel = $request->input('label')) {
                $payload['crf61_label'] = $batchLabel;
            }

            // Bind company lookup
            $payload['cr6f2_Company@odata.bind'] = '/cr6f2_companies(' . $companyId . ')';

            // Uploaded by (logged-in user email)
            $payload['crf61_uploadedby'] = $request->user()->email;

            $batchSummaryId = $this->dv->create('cr6f2_batchsummaries', $payload);
        } catch (Throwable $e) {
            Log::warning('Failed to create Batch Summary in Dataverse.', ['error' => $e->getMessage()]);
            $batchSummaryError = $e->getMessage();
        }

        return response()->json([
            'success'              => true,
            'batch_folder'         => $batchFolder,
            'invoice_folder'       => $invoiceFolder . '/Documents',
            'batch_summary_id'     => $batchSummaryId,
            'batch_summary_error'  => $batchSummaryError,
        ], 201);
    }

    // ─── Step 2: upload file ─────────────────────────────────────────────────

    /**
     * POST /api/v1/upload/file
     *
     * Always uploads flat into {invoice_folder}/{filename} (no SharePoint
     * sub-folder), creates the cr6f2_documentses record server-side, and
     * fires the identify pipeline only when there's classification or
     * auto-split work to do. Power Automate's create/modify watcher is
     * deliberately never triggered.
     *
     * Multipart form-data:
     *   file              file     required
     *   invoice_folder    string   required  (from init response)
     *   batch_folder      string   required  (from init response — used only
     *                              for the BatchSummary linkage; no physical
     *                              folder is created)
     *   company_id        uuid     required
     *   batch_summary_id  uuid     required
     *   doc_type_hint     int      optional  cr6f2_maindoctype value to set
     *                              directly (null = let identify pipeline
     *                              classify via Anthropic)
     *   auto_split        bool     optional  ask Anthropic to suggest a
     *                              page-range split
     */
    public function file(Request $request): JsonResponse
    {
        $request->validate([
            'file'             => ['required', 'file', 'mimes:pdf,jpg,jpeg,png', 'max:20480'],
            'invoice_folder'   => ['required', 'string'],
            'batch_folder'     => ['required', 'string'],
            'company_id'       => ['required', 'uuid'],
            'batch_summary_id' => ['required', 'uuid'],
            'auto_split'       => ['nullable', 'boolean'],
            'sub_split'        => ['nullable', 'boolean'],
            'doc_type_hint'    => ['nullable', 'integer'],
        ]);

        $companyId      = (string) $request->input('company_id');
        if ($deny = $this->authorizeCompany($companyId)) return $deny;

        $uploadedFile   = $request->file('file');

        // Destination + auth. Never trust the client-supplied invoice_folder as an
        // upload target — a full SharePoint URL there would let an authorized user
        // write into any reachable path. Re-derive server-side, in priority order:
        //   • Per-account SharePoint storage config → that account's own site,
        //     authenticated with its own app credentials.
        //   • Per-account Server (SFTP/FTP) storage config → that account's own
        //     remote server. Upload-only for now — OCR/preview don't read these
        //     back yet (see ServerStorageService docblock).
        //   • Per-account Google Drive storage config → that account's own Drive
        //     folder, authenticated by its service account. Also upload-only.
        //   • Otherwise fall back to the company invoice folder + global SYHC
        //     SharePoint token (unchanged behaviour).
        // Everything is PROCESSED in SYHC's own SharePoint. A client's own backend
        // (Server / Google Drive / their SharePoint tenant) receives the FINAL
        // document only at Move-to-Storage — so split children, merges and rotations
        // all reach the client's storage correctly regardless of how they were
        // created, without every create-flow having to know about client storage.
        // (A per-account SharePoint config still uploads into that account's own
        // site, which is fine — that's just a different SharePoint folder.)
        $tokenProvider = null;

        // Direct (zero-copy) accounts: process straight from the client's own
        // backend with NO working copy in SYHC SharePoint. Handled entirely in a
        // separate branch below (see $directUpload) — upload goes to the client
        // Server/Drive and cr6f2_documentlink becomes that client link, which the
        // OCR read path (fetch_document_bytes) + preview already dispatch on.
        $directUpload = $this->storage->resolveDirectUpload($companyId);

        $invoiceFolder = null;
        $acct = $directUpload ? null : $this->storage->resolveSharePointUpload($companyId);
        if ($acct) {
            $invoiceFolder = $acct['folder_url'];
            $tokenProvider = $acct['token_provider'];
            Log::info('Batch upload: routing to per-account SharePoint storage', [
                'company_id' => $companyId,
                'folder'     => $invoiceFolder,
            ]);
        } elseif (!$directUpload) {
            try {
                $company = $this->dv->find('cr6f2_companies', $companyId, ['select' => ['cr6f2_invoicefolder']]);
            } catch (Throwable $e) {
                return response()->json(['success' => false, 'message' => 'Company not found.'], 404);
            }
            $companyInvoiceFolder = $company['cr6f2_invoicefolder'] ?? null;
            if (!$companyInvoiceFolder) {
                return response()->json(['success' => false, 'message' => 'Company has no invoice folder configured.'], 422);
            }
            $invoiceFolder = $companyInvoiceFolder . '/Documents';
        }
        // else: direct (zero-copy) mode — no SharePoint destination; the client
        // backend below is the only target.

        $sanitizedName  = $this->sanitizeFilename($uploadedFile->getClientOriginalName());
        $batchSummaryId = (string) $request->input('batch_summary_id');
        $docTypeHint    = $request->input('doc_type_hint');
        $docTypeHintInt = is_numeric($docTypeHint) ? (int) $docTypeHint : null;
        $autoSplit      = $request->boolean('auto_split');
        $subSplit       = $request->boolean('sub_split');

        // Store temporarily for upload
        $tempPath = $uploadedFile->store('temp', 'local');
        $fullPath = Storage::disk('local')->path($tempPath);

        // Upload flat into {invoice_folder}. Passing '' as the batch-folder arg
        // makes SharePointService::uploadFile skip the sub-folder segment, so
        // the file lands directly in Documents/ and Power Automate's folder
        // trigger never sees a "new file in batch folder" event.
        //
        // Pick a name that's free in Dataverse, then upload with
        // conflictBehavior=fail. uniqueFilename() only inspects Dataverse rows,
        // so a file that already exists in SharePoint with no matching row
        // (orphan, or a prior upload that failed before its row was created)
        // still 409s. On a 409 we auto-rename by bumping the _N suffix until
        // Graph accepts the name — this also closes the concurrent-upload race
        // where two requests resolve to the same "_N" and one would overwrite.
        $filename          = null;
        $documentLink      = null;
        $clientStorageLink = null;

        if ($directUpload) {
            // Zero-copy: the client's own backend holds the ONLY copy. Upload there
            // and point cr6f2_documentlink straight at it (sftp:// / ftp:// /
            // googledrive://) — the OCR read path (fetch_document_bytes) and preview
            // (serveClientFile) both dispatch on that scheme, so no SYHC SharePoint
            // copy is ever written. uniqueFilename() is Dataverse-based, so it still
            // stops a second upload of the same name from clobbering the first.
            $candidate = $this->uniqueFilename($sanitizedName, $companyId);
            try {
                $documentLink = app(\App\Services\StorageMirror::class)->store($directUpload, $candidate, $fullPath);
                $filename     = $candidate;
            } catch (Throwable $e) {
                Storage::disk('local')->delete($tempPath);
                Log::error('Batch upload (direct): client upload failed', [
                    'company_id' => $companyId, 'filename' => $candidate, 'error' => $e->getMessage(),
                ]);
                return response()->json([
                    'success' => false,
                    'message' => 'Failed to upload file to your storage backend.',
                    'error'   => config('app.debug') ? $e->getMessage() : null,
                ], 502);
            }

            // Auto-split / sub-split now run zero-copy too: identify_runner creates
            // the children straight in the client backend (documentlink = client
            // link) when the account is direct, so nothing leaks into SYHC.
        } else {
            $dot      = strrpos($sanitizedName, '.');
            $baseName = ($dot !== false && $dot > 0) ? substr($sanitizedName, 0, $dot) : $sanitizedName;
            $extName  = ($dot !== false && $dot > 0) ? substr($sanitizedName, $dot) : '';

            $candidate = $this->uniqueFilename($sanitizedName, $companyId);
            // Seed the suffix from uniqueFilename's choice so 409 bumps continue
            // from "_N" instead of restarting at "_1".
            $suffix = 0;
            if (preg_match('/^' . preg_quote($baseName, '/') . '_(\d+)' . preg_quote($extName, '/') . '$/u', $candidate, $m)) {
                $suffix = (int) $m[1];
            }

            try {
                for ($attempt = 1; $attempt <= 20; $attempt++) {
                    try {
                        $documentLink = $this->sp->uploadFile($invoiceFolder, '', $candidate, $fullPath, 'fail', $tokenProvider);
                        $filename = $candidate;
                        break;
                    } catch (Throwable $e) {
                        if ($e->getCode() === 409 && $attempt < 20) {
                            $suffix++;
                            $candidate = $baseName . '_' . $suffix . $extName;
                            Log::info('Batch upload: filename conflict, auto-renaming', [
                                'attempt' => $attempt, 'next' => $candidate,
                            ]);
                            continue;
                        }
                        throw $e;
                    }
                }
            } catch (Throwable $e) {
                Storage::disk('local')->delete($tempPath);
                Log::error('Batch upload: file upload failed', [
                    'company_id' => $companyId,
                    'filename'   => $sanitizedName,
                    'folder'     => $invoiceFolder,
                    'error'      => $e->getMessage(),
                ]);
                return response()->json([
                    'success' => false,
                    'message' => 'Failed to upload file to storage.',
                    'error'   => config('app.debug') ? $e->getMessage() : null,
                ], 502);
            }

            // Mirror the just-uploaded file into the client's own storage (best-effort)
            // so their folder reflects the upload live; documentlink stays the SYHC copy.
            $clientStorageLink = app(\App\Services\StorageMirror::class)->mirror($companyId, $filename, $fullPath);
        }

        Storage::disk('local')->delete($tempPath);

        // Email is the universal anchor; the systemuser GUID only exists for
        // Azure-AD accounts and is null for every self-registered user.
        $userEmail = $request->user()->email ?? null;
        $userGuid  = $this->lookupUserGuid($userEmail ?? '');

        try {
            $documentId = $this->createDraftDocument(
                filename:          $filename,
                sharepointUrl:     $documentLink,
                companyId:         $companyId,
                batchSummaryId:    $batchSummaryId,
                uploadedByGuid:    $userGuid,
                docTypeHint:       $docTypeHintInt,
                clientStorageLink: $clientStorageLink,
                uploadedByEmail:   $userEmail,
            );
        } catch (Throwable $e) {
            Log::error('Batch upload: failed to create document record', [
                'error' => $e->getMessage(),
            ]);
            return response()->json([
                'success' => false,
                'message' => 'Failed to create document record.',
                'error'   => config('app.debug') ? $e->getMessage() : null,
            ], 502);
        }

        // Every upload fires OCR. Routing:
        //   - no doctype hint                       → identify (auto-classifies + auto-chains to invoice_runner)
        //   - auto_split = on                       → identify (regardless of hint — split needs Anthropic)
        //   - hint ∈ {Invoice, Cashbook, JE}        → skip identify, fire invoice_runner directly
        //   - hint = BankStatement / Other          → identify (BS path still needs identify's doctype-write + handoff; Other lets identify decide)
        //
        // The invoice_runner handles all three of Invoice/Cashbook/JE internally
        // (it branches by cr6f2_maindoctype at the row-write step), so we don't
        // need a separate service per doctype.
        $directInvoicePipeline = !$autoSplit && in_array($docTypeHintInt, [521880000, 521880001, 521880002], true);

        $pipelineLabel  = 'identify';
        $pipelineError  = null;

        try {
            if ($directInvoicePipeline) {
                /** @var \App\Services\InvoiceOcrService $svc */
                $svc = app(\App\Services\InvoiceOcrService::class);
                $svc->trigger($documentId, $companyId, $userGuid, $userEmail);
                $pipelineLabel = 'invoice_ocr';
            } else {
                $this->identify->trigger(
                    documentId:     $documentId,
                    companyId:      $companyId,
                    batchSummaryId: $batchSummaryId,
                    docTypeHint:    $docTypeHintInt,
                    autoSplit:      $autoSplit,
                    userGuid:       $userGuid,
                    userEmail:      $userEmail,
                    subSplit:       $subSplit,
                );
            }
        } catch (Throwable $e) {
            // Non-fatal — the document exists and can be retried via the
            // existing OCR re-trigger endpoints. Surface the error so the
            // frontend can flag the upload.
            Log::warning('Batch upload: pipeline trigger failed', [
                'pipeline'    => $directInvoicePipeline ? 'invoice_ocr' : 'identify',
                'document_id' => $documentId,
                'error'       => $e->getMessage(),
            ]);
            $pipelineError = $e->getMessage();
        }

        $response = [
            'success'     => true,
            'filename'    => $filename,
            'document_id' => $documentId,
            'pipeline'    => $pipelineLabel,
        ];
        if ($pipelineError !== null) {
            $response['pipeline_error'] = $pipelineError;
        }
        return response()->json($response, 202);
    }

    /**
     * Create a Draft cr6f2_documentses row for the auto-detect flow. The
     * identify pipeline (Python) sets cr6f2_maindoctype after classifying;
     * caller may pass $docTypeHint to skip classification.
     */
    private function createDraftDocument(
        string  $filename,
        string  $sharepointUrl,
        string  $companyId,
        string  $batchSummaryId,
        ?string $uploadedByGuid,
        ?int    $docTypeHint,
        ?string $clientStorageLink = null,
        ?string $uploadedByEmail = null,
    ): string {
        $payload = [
            'cr6f2_documentname' => $filename,
            'cr6f2_documentlink' => $sharepointUrl,
            'cr6f2_ocrstatus'    => 521880000,                  // Draft
            'cr6f2_uploadeddate' => now()->toIso8601String(),
        ];
        // The client's own storage mirror location (set when the company uses a
        // Server/Drive backend); documentlink above is the authoritative SYHC copy.
        if ($clientStorageLink) {
            $payload['cr6f2_clientstoragelink'] = $clientStorageLink;
        }
        if ($companyId) {
            $payload['cr6f2_CompanyID@odata.bind'] = "/cr6f2_companies({$companyId})";
        }
        if ($batchSummaryId) {
            $payload['cr6f2_BatchSummary@odata.bind'] = "/cr6f2_batchsummaries({$batchSummaryId})";
        }
        // Email first, GUID as fallback — a self-registered (Google /
        // email-password) uploader has NO systemusers row, so gating this on the
        // GUID dropped their attribution entirely. attributionBind resolves the
        // app-user off the email and only falls back to the systemuser bridge.
        if ($uploadedByEmail || $uploadedByGuid) {
            $payload += $this->appUsers->attributionBind('UploadedBy', $uploadedByEmail, null, $uploadedByGuid);
        }
        if ($docTypeHint !== null) {
            $payload['cr6f2_maindoctype'] = $docTypeHint;
        }
        // Else: leave maindoctype unset; the identify pipeline writes it.

        return $this->dv->create('cr6f2_documentses', $payload);
    }

    /**
     * Look up the systemusers GUID for a Microsoft email. Returns null on
     * miss / error. Mirrors BankStatementController::lookupUserGuid().
     */
    private function lookupUserGuid(string $email): ?string
    {
        if (empty($email)) {
            return null;
        }
        try {
            $safe = str_replace("'", "''", $email);
            $r = $this->dv->get('systemusers', [
                'select' => ['systemuserid'],
                'filter' => "internalemailaddress eq '{$safe}'",
                'top'    => 1,
            ]);
            return $r['value'][0]['systemuserid'] ?? null;
        } catch (Throwable) {
            return null;
        }
    }

    // ─── Step 3: poll batch status ───────────────────────────────────────────

    /**
     * GET /api/v1/upload/batch-status?company_id=<uuid>
     *
     * Returns in-flight batches for the current user + company. A batch is
     * considered in-flight when cr6f2_processeddoc < cr6f2_filecount AND it
     * was created within the last 30 minutes (older = stuck/abandoned).
     */
    public function status(Request $request): JsonResponse
    {
        $request->validate([
            'company_id' => ['required', 'uuid'],
        ]);

        $companyId = $request->input('company_id');
        $email     = $request->user()->email;

        // ISO 8601 UTC timestamp for "30 minutes ago"
        $cutoff = now()->subMinutes(30)->utc()->format('Y-m-d\TH:i:s\Z');

        // OData filter: company + uploader + within window.
        // The "processed < total" check is done in PHP — Dataverse OData
        // field-to-field comparison is not reliably supported, and NULL
        // values in cr6f2_processeddoc would short-circuit the filter.
        $filter = sprintf(
            "_cr6f2_company_value eq %s and crf61_uploadedby eq '%s' and createdon gt %s",
            $companyId,
            str_replace("'", "''", $email),
            $cutoff,
        );

        try {
            $result = $this->dv->get('cr6f2_batchsummaries', [
                'select' => [
                    'cr6f2_batchsummaryid',
                    'cr6f2_batchfoldername',
                    'crf61_doctype',
                    'cr6f2_subdoctype',
                    'cr6f2_filecount',
                    'cr6f2_processeddoc',
                    'createdon',
                ],
                'filter'  => $filter,
                'orderby' => 'createdon desc',
                'top'     => 20,
            ]);
        } catch (Throwable $e) {
            Log::warning('batch-status query failed', ['error' => $e->getMessage()]);
            return response()->json(['batches' => []]);
        }

        $rawCount = count($result['value'] ?? []);

        // Show all batches in the 30-min window — completed ones linger so the
        // user can see what just finished. Frontend handles manual dismissal.
        $batches = collect($result['value'] ?? [])
            ->filter(fn (array $row) => (int) ($row['cr6f2_filecount'] ?? 0) > 0)
            ->map(function (array $row) {
                $docType    = trim((string) ($row['crf61_doctype'] ?? ''));
                $subDocType = trim((string) ($row['cr6f2_subdoctype'] ?? ''));

                return [
                    'id'           => $row['cr6f2_batchsummaryid'] ?? null,
                    'batch_folder' => $row['cr6f2_batchfoldername'] ?? null,
                    'doc_type'     => $docType !== '' ? $docType : null,
                    'sub_doc_type' => $subDocType !== '' ? $subDocType : null,
                    'processed'    => (int) ($row['cr6f2_processeddoc'] ?? 0),
                    'total'        => (int) ($row['cr6f2_filecount']    ?? 0),
                    'created_on'   => $row['createdon'] ?? null,
                ];
            })
            ->values();

        Log::debug('batch-status', [
            'email'      => $email,
            'company_id' => $companyId,
            'raw'        => $rawCount,
            'returned'   => $batches->count(),
        ]);

        return response()->json(['batches' => $batches]);
    }

    // ─── Helpers ─────────────────────────────────────────────────────────────

    /**
     * Strip characters SharePoint rejects, cap base name length at 100, and
     * collapse spaces to underscores. Mirrors PA flow
     * OcrApp-FileCreatedModifiedinInvoiceSharePoint (Compose_Remove_Invalid_Chars
     * → Compose_Truncate_Length → Compose_Remove_Extra_Spaces) so a doc that
     * lands here looks the same as one the legacy watcher renamed.
     *
     * The 100-char cap applies to the BASE name only — the extension is
     * preserved verbatim so MIME sniffing / readers don't break.
     */
    private function sanitizeFilename(string $name): string
    {
        $invalid = ['`','~','@','#','$','%','&','*','{','}',';','"',"'",'/','\\','|','<','>','?',':','+','^','[',']',','];
        $clean   = str_replace($invalid, '_', $name);

        $dot = strrpos($clean, '.');
        if ($dot !== false && $dot > 0) {
            $base = substr($clean, 0, $dot);
            $ext  = substr($clean, $dot); // includes the dot
        } else {
            $base = $clean;
            $ext  = '';
        }

        if (mb_strlen($base) > 100) {
            $base = mb_substr($base, 0, 100);
        }

        $base = trim($base);
        $base = preg_replace('/\s+/u', '_', $base);

        if ($base === '') {
            $base = 'file';
        }

        return $base . $ext;
    }

    /**
     * Append `_N` before the extension if a doc with the same name already
     * exists in this company. Picks the smallest N that's free. Graph PUT
     * overwrites by default, so without this guard a second upload of
     * `20260212.pdf` silently replaces the first file in SharePoint and we
     * end up with two Dataverse rows pointing at the same blob.
     */
    private function uniqueFilename(string $sanitized, string $companyId): string
    {
        if ($companyId === '') {
            return $sanitized;
        }

        $dot = strrpos($sanitized, '.');
        if ($dot !== false && $dot > 0) {
            $base = substr($sanitized, 0, $dot);
            $ext  = substr($sanitized, $dot);
        } else {
            $base = $sanitized;
            $ext  = '';
        }

        $safeBase = str_replace("'", "''", $base);
        $safeExt  = str_replace("'", "''", $ext);
        $filter   = sprintf(
            "_cr6f2_companyid_value eq %s and (cr6f2_documentname eq '%s%s' or startswith(cr6f2_documentname,'%s_'))",
            $companyId,
            $safeBase,
            $safeExt,
            $safeBase,
        );

        try {
            $existing = $this->dv->get('cr6f2_documentses', [
                'select' => ['cr6f2_documentname'],
                'filter' => $filter,
                'top'    => 250,
            ]);
        } catch (Throwable $e) {
            Log::warning('uniqueFilename: lookup failed, keeping original name', [
                'error' => $e->getMessage(),
            ]);
            return $sanitized;
        }

        $names = collect($existing['value'] ?? [])
            ->pluck('cr6f2_documentname')
            ->filter()
            ->map(fn ($n) => (string) $n)
            ->all();

        if (!in_array($sanitized, $names, true)) {
            return $sanitized;
        }

        $extQ    = preg_quote($ext, '/');
        $baseQ   = preg_quote($base, '/');
        $pattern = "/^{$baseQ}_(\d+){$extQ}$/u";

        $taken = [];
        foreach ($names as $n) {
            if (preg_match($pattern, $n, $m)) {
                $taken[(int) $m[1]] = true;
            }
        }

        $n = 1;
        while (isset($taken[$n])) {
            $n++;
        }
        return $base . '_' . $n . $ext;
    }

    /**
     * Fetch cr6f2_BatchFolderPrefix from Dataverse Environment Variable Values.
     * Mirrors: LookUp('Environment Variable Values',
     *            'Environment Variable Definition'.'Schema Name' = "cr6f2_BatchFolderPrefix").Value
     */
    private function getBatchPrefix(): string
    {
        // First get the definition ID
        $defResult = $this->dv->get('environmentvariabledefinitions', [
            'select' => ['environmentvariabledefinitionid'],
            'filter' => "schemaname eq 'cr6f2_BatchFolderPrefix'",
            'top'    => 1,
        ]);

        $defId = $defResult['value'][0]['environmentvariabledefinitionid'] ?? null;
        if (!$defId) {
            throw new \RuntimeException('Environment variable cr6f2_BatchFolderPrefix not found.');
        }

        // Then get the value
        $valResult = $this->dv->get('environmentvariablevalues', [
            'select' => ['value'],
            'filter' => "_environmentvariabledefinitionid_value eq {$defId}",
            'top'    => 1,
        ]);

        $value = $valResult['value'][0]['value'] ?? null;
        if (!$value) {
            throw new \RuntimeException('Environment variable cr6f2_BatchFolderPrefix has no value.');
        }

        return $value;
    }
}
