LibriController.php

<?php
declare(strict_types=1);

namespace App\Controllers;

use mysqli;
use Psr\Http\Message\ResponseInterface as Response;
use Psr\Http\Message\ServerRequestInterface as Request;
use App\Support\SecureLogger;
use App\Support\Csv;
use App\Support\LibraryThingInstaller;
use App\Models\SeriesRepository;
use Slim\Exception\HttpNotFoundException;

class LibriController
{
    /**
     * Centralized whitelist of allowed domains for external cover image downloads
     * Used by both downloadExternalCover() and isUrlAllowed()
     */
    private const COVER_ALLOWED_DOMAINS = [
        // Google Books
        'books.google.com',
        'books.google.it',
        'images.google.com',
        // Open Library
        'covers.openlibrary.org',
        // Italian bookstores
        'www.libreriauniversitaria.it',
        'img.libreriauniversitaria.it',
        'img2.libreriauniversitaria.it',
        'img3.libreriauniversitaria.it',
        'cdn.mondadoristore.it',
        'www.lafeltrinelli.it',
        // Amazon
        'images.amazon.com',
        'images-na.ssl-images-amazon.com',
        'images-eu.ssl-images-amazon.com',
        // Ubik Libri
        'www.ubiklibri.it',
        'ubiklibri.it',
        // Internet Archive — covers.openlibrary.org 302-redirects here (issue #173)
        'archive.org',
    ];

    /**
     * Wide suffix allow-list for cover hosts (issue #173). Exact-host matching kept
     * blocking legitimate sources whose CDN host is dynamic — most notably
     * covers.openlibrary.org, which 302s to archive.org / iaNNNNNN.us.archive.org.
     * These suffixes cover the major book-cover CDNs. SSRF is still bounded by the
     * private/reserved-IP check, the image-content validation and the size cap below.
     */
    private const COVER_ALLOWED_SUFFIXES = [
        '.archive.org',
        '.openlibrary.org',
        '.googleusercontent.com', '.ggpht.com', '.gstatic.com', '.googleapis.com', '.google.com',
        '.ssl-images-amazon.com', '.media-amazon.com', '.images-amazon.com', '.amazonaws.com',
        '.cloudfront.net', '.akamaized.net', '.akamaihd.net', '.fastly.net', '.cloudflare.com',
        '.cloudinary.com', '.imgix.net', '.wikimedia.org',
        '.gr-assets.com', '.images-bn.com', '.bookshop.org',
        '.libreriauniversitaria.it', '.mondadoristore.it', '.lafeltrinelli.it', '.ibs.it', '.ubiklibri.it',
    ];

    /**
     * Whether a host may be fetched as a cover source: exact match in
     * COVER_ALLOWED_DOMAINS, or ends with one of the wide COVER_ALLOWED_SUFFIXES.
     */
    private static function coverHostAllowed(string $host): bool
    {
        if ($host === '') {
            return false;
        }
        $host = strtolower($host);
        if (\in_array($host, self::COVER_ALLOWED_DOMAINS, true)) {
            return true;
        }
        foreach (self::COVER_ALLOWED_SUFFIXES as $suffix) {
            if (str_ends_with($host, $suffix)) {
                return true;
            }
        }
        return false;
    }

    /**
     * Maximum file size for cover downloads (5MB)
     */
    private const MAX_COVER_SIZE = 5 * 1024 * 1024;

    /**
     * Default values for LibraryThing fields (27 unique fields)
     * Used by both store() and update() to avoid duplication
     */
    private const LIBRARYTHING_FIELDS_DEFAULTS = [
        'review' => null,
        'rating' => null,
        'comment' => null,
        'private_comment' => null,
        'physical_description' => null,
        'dewey_wording' => null,
        'lccn' => null,
        'lc_classification' => null,
        'other_call_number' => null,
        'entry_date' => null,
        'date_started' => null,
        'date_read' => null,
        'bcid' => null,
        'barcode' => null,
        'oclc' => null,
        'work_id' => null,
        'original_languages' => null,
        'source' => null,
        'from_where' => null,
        'lending_patron' => null,
        'lending_status' => null,
        'lending_start' => null,
        'lending_end' => null,
        'value' => null,
        'condition_lt' => null,
    ];

    /**
     * Get the storage directory path
     * Centralized configuration for storage location
     */
    private function getStoragePath(): string
    {
        return __DIR__ . '/../../storage';
    }

    /**
     * Get the uploads directory path for covers
     * Centralized configuration for upload location
     */
    private function getCoversUploadPath(): string
    {
        return __DIR__ . '/../../public/uploads/copertine';
    }

    /**
     * Get the relative URL path for covers (from web root)
     */
    private function getCoversUrlPath(): string
    {
        return '/uploads/copertine';
    }

    private function syncSeriesMetadataFromBookForm(mysqli $db, int $bookId, array $fields, array $data): void
    {
        try {
            (new SeriesRepository($db))->syncBookFromForm($bookId, $fields, $data);
        } catch (\Throwable $e) {
            SecureLogger::warning('LibriController::syncSeriesMetadataFromBookForm failed', [
                'error' => $e->getMessage(),
                'libro_id' => $bookId,
                'collana' => $fields['collana'] ?? '',
            ]);
        }
    }

    /**
     * Download external cover image and save locally
     *
     * @param string|null $url External URL or local path
     * @return string|null Local URL path on success, original URL on failure, null/empty passed through
     */
    private function downloadExternalCover(?string $url): ?string
    {
        if ($url === null || $url === '' || !preg_match('#^https?://#i', $url)) {
            return $url; // Not an external URL, return as-is
        }

        // Fix HTML-encoded ampersands from Google Books API URLs
        $url = html_entity_decode($url, ENT_QUOTES | ENT_HTML5, 'UTF-8');

        $parsedUrl = parse_url($url);
        $host = strtolower($parsedUrl['host'] ?? '');

        // Initial host: wide cover allow-list (exact + suffix). The real SSRF
        // boundary is the public-IP check after each request, not this list.
        if (!self::coverHostAllowed($host)) {
            \App\Support\SecureLogger::warning('Cover download from non-whitelisted domain', ['url' => $url, 'host' => $host]);
            return $url; // Return original URL if domain not allowed
        }

        try {
            // SSRF-safe fetch: redirects are followed MANUALLY, each hop's host is
            // resolved to a validated PUBLIC IP and pinned via CURLOPT_RESOLVE, with a
            // size cap and image validation (App\Support\SsrfGuard). This is what lets
            // the cover allow-list stay wide (issue #173) without an SSRF: a wide host
            // can still never make the server reach an internal/reserved IP, on any hop.
            $fetched = \App\Support\SsrfGuard::fetchImage($url, self::MAX_COVER_SIZE);
            if ($fetched === null) {
                \App\Support\SecureLogger::warning('Cover not saved locally: SSRF-safe fetch blocked or failed', ['url' => $url]);
                return $url; // keep the original URL when we cannot safely localise it
            }
            [$imageData, $mimeType] = $fetched;

            $extension = match ($mimeType) {
                'image/jpeg', 'image/jpg' => 'jpg',
                'image/png' => 'png',
                default => null,
            };

            if ($extension === null) {
                return $url; // Unsupported format
            }

            // Ensure upload directory exists
            $uploadDir = $this->getCoversUploadPath();
            if (!is_dir($uploadDir)) {
                mkdir($uploadDir, 0755, true);
            }

            // Generate unique filename and save
            $filename = 'copertina_' . uniqid('', true) . '.' . $extension;
            $filepath = $uploadDir . '/' . $filename;

            // Create image resource and save
            $image = imagecreatefromstring($imageData);
            if ($image === false) {
                SecureLogger::warning('Cover image processing failed: imagecreatefromstring returned false', [
                    'url' => $url,
                    'data_length' => strlen($imageData),
                ]);
                return $url;
            }

            $saveResult = match ($extension) {
                'png' => imagepng($image, $filepath, 9),
                default => imagejpeg($image, $filepath, 85),
            };
            imagedestroy($image);

            if (!$saveResult) {
                SecureLogger::warning('Cover image save failed', [
                    'url' => $url,
                    'filepath' => $filepath,
                    'extension' => $extension,
                ]);
                return $url;
            }

            chmod($filepath, 0644);
            \App\Support\SecureLogger::info('Cover downloaded successfully', ['url' => $url, 'local' => $filename]);

            return $this->getCoversUrlPath() . '/' . $filename;

        } catch (\Throwable $e) {
            \App\Support\SecureLogger::error('Cover download exception', ['url' => $url, 'error' => $e->getMessage()]);
            return $url; // Return original URL on any error
        }
    }

    /**
     * Normalize text fields by removing MARC-8 control characters and collapsing whitespace
     * MARC-8 uses characters like NSB (0x88, 0x98) and NSE (0x89, 0x9C) for non-sorting blocks
     * These appear as invisible characters or ? when stored in MySQL with UTF-8
     */
    private function normalizeText(?string $text): string
    {
        if ($text === null || $text === '') {
            return '';
        }
        // Remove C1 control characters (U+0080–U+009F) including MARC-8 NSB/NSE markers.
        // MUST use /u flag to match Unicode code points, not raw bytes — without it,
        // continuation bytes in multi-byte UTF-8 chars like Ø (0xC3 0x98) get stripped.
        $text = preg_replace('/[\x{0080}-\x{009F}]/u', '', $text) ?? $text;
        // Collapse multiple whitespace into single space and trim
        $text = trim(preg_replace('/\s+/u', ' ', $text) ?? $text);
        return $text;
    }

    /**
     * Normalize and validate ISSN. Returns canonical XXXX-XXXX format, null for empty, or throws on invalid.
     * @throws \InvalidArgumentException if ISSN format is invalid
     */
    private function normalizeIssn(?string $raw): ?string
    {
        $issnRaw = trim((string) $raw);
        if ($issnRaw === '') {
            return null;
        }
        $issnCompact = strtoupper(str_replace('-', '', preg_replace('/\s+/', '', $issnRaw)));
        if (!preg_match('/^\d{7}[\dX]$/', $issnCompact)) {
            throw new \InvalidArgumentException('invalid_issn');
        }
        return substr($issnCompact, 0, 4) . '-' . substr($issnCompact, 4, 4);
    }

    /**
     * Rotate log files to prevent unlimited growth
     * Keeps only last 7 days of logs, max 10MB per file
     */
    private function rotateLogFile(string $logFile): void
    {
        if (!file_exists($logFile)) {
            return;
        }

        $maxSize = 10 * 1024 * 1024; // 10MB
        $maxAge = 7 * 24 * 60 * 60;  // 7 days

        // Check size
        if (filesize($logFile) > $maxSize) {
            // Rotate by renaming
            $rotated = $logFile . '.' . date('Y-m-d_His');
            rename($logFile, $rotated);
        }

        // Clean old rotated logs
        $dir = dirname($logFile);
        $basename = basename($logFile);
        $pattern = $dir . '/' . $basename . '.*';
        foreach (glob($pattern) as $oldLog) {
            if (filemtime($oldLog) < time() - $maxAge) {
                // nosemgrep: php.lang.security.unlink-use.unlink-use -- glob() restricted to the app log directory; rotated-log names are app-generated, not user input
                unlink($oldLog);
            }
        }
    }

    private function logCoverDebug(string $label, array $data): void
    {
        if (getenv('APP_ENV') === 'development') {
            $logDir = $this->getStoragePath();

            // Create directory if not exists
            if (!is_dir($logDir)) {
                mkdir($logDir, 0755, true);
            }

            $file = $logDir . '/cover_debug.log';

            // Rotate log before writing
            $this->rotateLogFile($file);

            // Enhanced sanitization - remove any sensitive data
            $sanitized = $data;
            $sensitiveKeys = ['password', 'token', 'csrf_token', 'api_key', 'secret', 'auth'];
            foreach ($sensitiveKeys as $key) {
                unset($sanitized[$key]);
            }

            $line = date('Y-m-d H:i:s') . " [$label] " . json_encode($sanitized, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . "\n";

            // Use LOCK_EX to prevent race conditions
            file_put_contents($file, $line, FILE_APPEND | LOCK_EX);
        }
    }

    /**
     * Strip HTML tags and limit publisher name length.
     */
    private function sanitizePublisherName(string $name): string
    {
        $name = trim(strip_tags($name));
        if (mb_strlen($name) > 255) {
            $name = mb_substr($name, 0, 255);
        }
        return $name;
    }

    public function index(Request $request, Response $response, mysqli $db): Response
    {
        $repo = new \App\Models\BookRepository($db);
        $libri = $repo->listWithAuthors(100);

        // Resolve genre names for URL filter display
        $params = $request->getQueryParams();
        $genreFilterName = '';
        $subgenreFilterName = '';
        $genreId = (int) ($params['genere'] ?? $params['genere_filter'] ?? 0);
        $subgenreId = (int) ($params['sottogenere'] ?? $params['sottogenere_filter'] ?? 0);
        if ($genreId > 0 || $subgenreId > 0) {
            $lookupId = $subgenreId > 0 ? $subgenreId : $genreId;
            $stmt = $db->prepare('SELECT nome FROM generi WHERE id = ?');
            if ($stmt) {
                $stmt->bind_param('i', $lookupId);
                $stmt->execute();
                $row = $stmt->get_result()->fetch_assoc();
                if ($subgenreId > 0) {
                    $subgenreFilterName = $row['nome'] ?? '';
                } else {
                    $genreFilterName = $row['nome'] ?? '';
                }
                $stmt->close();
            }
            if ($genreId > 0 && $subgenreId > 0) {
                $stmt2 = $db->prepare('SELECT nome FROM generi WHERE id = ?');
                if ($stmt2) {
                    $stmt2->bind_param('i', $genreId);
                    $stmt2->execute();
                    $row2 = $stmt2->get_result()->fetch_assoc();
                    $genreFilterName = $row2['nome'] ?? '';
                    $stmt2->close();
                }
            }
        }

        ob_start();
        $articleHintState = \App\Support\PeriodicalArticlesHint::state($db);
        $data = ['libri' => $libri, 'genreFilterName' => $genreFilterName, 'subgenreFilterName' => $subgenreFilterName];
        // extract($data);
        require __DIR__ . '/../Views/libri/index.php';
        $content = ob_get_clean();

        ob_start();
        require __DIR__ . '/../Views/layout.php';
        $html = ob_get_clean();

        $response->getBody()->write($html);
        return $response;
    }

    public function show(Request $request, Response $response, mysqli $db, int $id): Response
    {
        $repo = new \App\Models\BookRepository($db);
        $libro = $repo->getById($id);
        if (!$libro) {
            throw new HttpNotFoundException($request);
        }
        $loanRepo = new \App\Models\LoanRepository($db);
        $activeLoan = $loanRepo->getActiveLoanByBook($id);

        // Recupera tutte le copie del libro con informazioni sui prestiti
        $copyRepo = new \App\Models\CopyRepository($db);
        $copie = $copyRepo->getByBookId($id);
        $copySchedule = $copyRepo->getScheduleByBookId($id);

        // Borrower identity (name/email/reading history) is GDPR-sensitive and must
        // only be exposed to admin/staff. Standard/premium patrons can reach this
        // page (route allows them) but must see availability WITHOUT any borrower PII.
        $currentUserRole = $_SESSION['user']['tipo_utente'] ?? '';
        $isAdminOrStaff = \App\Support\Permissions::can('loans.manage');

        // Redact borrower PII from the active loan for non-admin/staff viewers,
        // preserving the loan-existence/date fields needed for availability display.
        if (!$isAdminOrStaff && !empty($activeLoan)) {
            foreach (['utente_nome', 'utente_cognome', 'utente_email', 'utente_id'] as $piiField) {
                unset($activeLoan[$piiField]);
            }
        }

        // Redact per-copy current-borrower PII for non-admin/staff viewers.
        if (!$isAdminOrStaff) {
            foreach ($copie as $ci => $copia) {
                foreach (['utente_nome', 'utente_cognome', 'utente_email', 'utente_id'] as $piiField) {
                    unset($copie[$ci][$piiField]);
                }
            }
        }

        // Get loan history for this book — contains borrower PII, so only fetch it
        // for admin/staff. Non-admin viewers get an empty history (block hidden).
        $loanHistory = [];
        if ($isAdminOrStaff) {
            $loanHistoryQuery = "
                SELECT
                    p.id,
                    p.data_prestito,
                    p.data_scadenza,
                    p.data_restituzione,
                    p.stato,
                    p.renewals,
                    p.note,
                    u.nome as utente_nome,
                    u.cognome as utente_cognome,
                    u.email as utente_email,
                    u.id as utente_id,
                    staff.nome as staff_nome,
                    staff.cognome as staff_cognome
                FROM prestiti p
                LEFT JOIN utenti u ON p.utente_id = u.id
                LEFT JOIN utenti staff ON p.processed_by = staff.id
                WHERE p.libro_id = ?
                ORDER BY p.data_prestito DESC
            ";
            $stmt = $db->prepare($loanHistoryQuery);
            $stmt->bind_param('i', $id);
            $stmt->execute();
            $result = $stmt->get_result();
            while ($row = $result->fetch_assoc()) {
                $loanHistory[] = $row;
            }
            $stmt->close();
        }

        // Active reservations for this book (admin/staff only - contains PII)
        $activeReservations = [];

        if ($isAdminOrStaff) {
            $resStmt = $db->prepare("
                SELECT r.id, r.data_inizio_richiesta, r.data_fine_richiesta, r.data_scadenza_prenotazione,
                       r.stato, r.queue_position,
                       u.nome, u.cognome, u.email
                FROM prenotazioni r
                JOIN utenti u ON u.id = r.utente_id
                WHERE r.libro_id = ? AND r.stato = 'attiva'
                ORDER BY r.data_inizio_richiesta IS NULL, r.data_inizio_richiesta ASC, r.id ASC
            ");
            $resStmt->bind_param('i', $id);
            $resStmt->execute();
            $resResult = $resStmt->get_result();
            while ($row = $resResult->fetch_assoc()) {
                $activeReservations[] = $row;
            }
            $resStmt->close();
        }

        // Multi-volume: check if this book is a parent work or a volume
        // No INFORMATION_SCHEMA check needed — volumi table is in schema.sql and migrate_0.5.1.sql.
        // If prepare() fails (table missing on unmigrated DB), the if($stmt) guard handles it.
        $volumes = [];
        $parentWork = null;

        // Volumes of this work (this book is the parent)
        $volStmt = $db->prepare("
            SELECT v.numero_volume, v.titolo_volume, l.id, l.titolo, l.isbn13, l.isbn10,
                   (SELECT " . \App\Support\AuthorName::displaySql('a') . " FROM libri_autori la JOIN autori a ON la.autore_id = a.id
                    WHERE la.libro_id = l.id AND la.ruolo = 'principale' LIMIT 1) AS autore
            FROM volumi v
            JOIN libri l ON v.volume_id = l.id AND l.deleted_at IS NULL
            WHERE v.opera_id = ?
            ORDER BY v.numero_volume
        ");
        if ($volStmt) {
            $volStmt->bind_param('i', $id);
            $volStmt->execute();
            $volRes = $volStmt->get_result();
            while ($row = $volRes->fetch_assoc()) {
                $volumes[] = $row;
            }
            $volStmt->close();
        }

        // Check if this book is a volume of another work
        $parentStmt = $db->prepare("
            SELECT v.numero_volume, v.titolo_volume, l.id, l.titolo
            FROM volumi v
            JOIN libri l ON v.opera_id = l.id AND l.deleted_at IS NULL
            WHERE v.volume_id = ?
            LIMIT 1
        ");
        if ($parentStmt) {
            $parentStmt->bind_param('i', $id);
            $parentStmt->execute();
            $parentRes = $parentStmt->get_result();
            $parentWork = $parentRes->fetch_assoc() ?: null;
            $parentStmt->close();
        }

        $libraryThingInstalled = LibraryThingInstaller::isInstalled($db);

        // Renewal cap is admin-configurable (loans.max_renewals); pass it to the
        // view so the displayed limit and the "Renew" button gate match the
        // server-side check in PrestitiController::renew (#157).
        $loanSettingsRepo = new \App\Models\SettingsRepository($db);
        // Validate numeric BEFORE casting: a non-numeric stored value (e.g. "abc")
        // would cast to 0, silently flipping the cap to "no renewals" instead of
        // falling back to the safe default. Only a genuine number is honoured.
        $rawMaxRenewals = $loanSettingsRepo->get('loans', 'max_renewals', '3');
        $maxRenewals = is_numeric($rawMaxRenewals) ? (int) $rawMaxRenewals : 3;
        if ($maxRenewals < 0) {
            $maxRenewals = 3;
        }

        // Issue #374: the audit timeline is staff-only. This route is also
        // reachable by patrons, so do not even query audit/operator data unless
        // the same role gate used for borrower PII has passed.
        $activityFeed = ['items' => [], 'page' => 1, 'pages' => 1, 'total' => 0];
        if ($isAdminOrStaff) {
            $query = $request->getQueryParams();
            $rawActivityPage = $query['book_activity_page'] ?? 1;
            $activityPage = is_scalar($rawActivityPage)
                && preg_match('/^[1-9]\d*$/D', (string) $rawActivityPage) === 1
                ? (int) $rawActivityPage
                : 1;
            $rawActivityQ = $query['activity_q'] ?? '';
            $activityQ = is_string($rawActivityQ) ? mb_substr(trim($rawActivityQ), 0, 100) : '';
            $rawActivityType = $query['activity_type'] ?? '';
            $activityType = is_string($rawActivityType)
                && in_array($rawActivityType, \App\Support\ActivityLog::TYPES, true)
                ? $rawActivityType
                : '';
            $activityFeed = \App\Support\ActivityLog::forBook(
                $db,
                $id,
                $activityPage,
                20,
                $activityQ !== '' ? $activityQ : null,
                $activityType !== '' ? $activityType : null
            );
        }

        ob_start();
        // extract([
        //     'libro' => $libro,
        //     'activeLoan' => $activeLoan,
        //     'bookPath' => $bookPath,
        //     'loanHistory' => $loanHistory,
        //     'libraryThingInstalled' => $libraryThingInstalled,
        // ]);
        require __DIR__ . '/../Views/libri/scheda_libro.php';
        $content = ob_get_clean();
        ob_start();
        require __DIR__ . '/../Views/layout.php';
        $html = ob_get_clean();
        $response->getBody()->write($html);
        return $response;
    }

    public function createForm(Request $request, Response $response, mysqli $db): Response
    {
        // For select boxes, load minimal lists
        $editRepo = new \App\Models\PublisherRepository($db);
        $autRepo = new \App\Models\AuthorRepository($db);
        $editori = $editRepo->listBasic();
        $autori = $autRepo->listBasic(500);
        $colRepo = new \App\Models\CollocationRepository($db);
        $taxRepo = new \App\Models\TaxonomyRepository($db);
        $scaffali = $colRepo->getScaffali();
        $mensole = $colRepo->getMensole();
        $generi = $taxRepo->genres();
        $sottogeneri = $taxRepo->subgenres();
        $libraryThingInstalled = \App\Support\LibraryThingInstaller::isInstalled($db);
        $articleHintState = \App\Support\PeriodicalArticlesHint::state($db);
        ob_start();
        // Variables are available in the template scope via require
        require __DIR__ . '/../Views/libri/crea_libro.php';
        $content = ob_get_clean();
        ob_start();
        require __DIR__ . '/../Views/layout.php';
        $html = ob_get_clean();
        $response->getBody()->write($html);
        return $response;
    }

    /**
     * Normalize picker payloads at the HTTP boundary. Empty/zero/garbage values
     * emitted by an unenhanced multi-select are ignored here; BookRepository
     * remains fail-fast when an invalid id reaches its internal API directly.
     *
     * @return list<int>
     */
    private function positiveIntegerIds(mixed $values): array
    {
        $ids = [];
        foreach ((array) $values as $value) {
            if (is_int($value)) {
                $id = $value;
            } elseif (is_string($value) && preg_match('/^\s*[0-9]+\s*$/D', $value) === 1) {
                $id = (int) trim($value);
            } else {
                continue;
            }
            if ($id > 0) {
                $ids[$id] = $id;
            }
        }
        return array_values($ids);
    }

    /**
     * Resolve a contributor role's picker submission into a flat list of author
     * ids (issue #237). Existing selections arrive as `<role>_ids[]`; brand-new
     * names as `<role>_new[]`, which are find-or-created just like authors.
     * Shared by store() and update() for illustratori/traduttori/curatori/coloristi.
     *
     * @param array<string,mixed> $data
     * @return list<int>
     */
    private function resolveContributorIds(mysqli $db, array $data, string $roleKey): array
    {
        $ids = $this->positiveIntegerIds($data[$roleKey . '_ids'] ?? []);
        $newNames = (array)($data[$roleKey . '_new'] ?? []);
        if ($newNames !== []) {
            $authRepo = new \App\Models\AuthorRepository($db);
            foreach ($newNames as $name) {
                $name = trim((string)$name);
                if ($name === '') {
                    continue;
                }
                $existing = $authRepo->findByCanonicalName($name);
                $ids[] = $existing ?? $authRepo->create([
                    'nome' => $name, 'pseudonimo' => '', 'data_nascita' => null,
                    'data_morte' => null, 'nazionalita' => '', 'biografia' => '', 'sito_web' => '',
                ]);
            }
        }

        // Backward-compatible ingestion path: callers predating the entity
        // pickers still submit singular free-text fields (and the ISBN scraper
        // submits scraped_translator/scraped_illustrator). Convert those names
        // to entities as well. The marker makes the current admin form
        // authoritative: if a librarian removes a scraped chip, its stale
        // hidden scraped_* value must not silently add it back on submit.
        $pickerIsAuthoritative = (string)($data['contributors_entity_picker'] ?? '') === '1';
        if (!$pickerIsAuthoritative) {
            $legacySources = [
                'illustratori' => ['illustratore', 'scraped_illustrator'],
                'traduttori'   => ['traduttore', 'scraped_translator'],
                'curatori'     => ['curatore'],
                'coloristi'    => ['colorista'],
            ];
            foreach ($legacySources[$roleKey] ?? [] as $sourceKey) {
                $raw = $data[$sourceKey] ?? '';
                if (!is_scalar($raw) || trim((string)$raw) === '') {
                    continue;
                }
                $resolved = \App\Support\ContributorSync::resolveNameIds($db, (string)$raw);
                $ids = array_merge($ids, $resolved['ids']);
            }
        }
        return array_values(array_unique(array_filter($ids, static fn($i) => $i > 0)));
    }

    /**
     * Build one bounded advisory-lock name per identifier value. Field names are
     * intentionally irrelevant: ISBN-13 and EAN are cross-compared by the
     * duplicate query, so the same value must map to the same lock whichever
     * field carried it. Sorting makes multi-lock acquisition deadlock-safe.
     *
     * @param array<string,string> $codes
     * @return list<string>
     */
    private static function bookIdentifierLockNames(string $databaseName, array $codes): array
    {
        if ($databaseName === '') {
            throw new \RuntimeException('Unable to resolve the current database for identifier locking.');
        }

        $values = [];
        foreach ($codes as $value) {
            // Identifier input is ASCII by definition. Normalise the ISBN-10
            // check digit as well: the database collation compares x/X as the
            // same value, so their advisory-lock identity must do the same.
            $normalized = strtoupper(trim((string) $value));
            if ($normalized !== '') {
                $values[$normalized] = true;
            }
        }
        $values = array_keys($values);
        sort($values, SORT_STRING);

        return array_map(
            static fn(string $value): string => 'pinakes-book-id:' . md5($databaseName . "\0" . $value),
            $values
        );
    }

    /** @param array<string,string> $codes @return list<string> */
    private function acquireBookIdentifierLocks(mysqli $db, array $codes): array
    {
        $databaseName = '';
        $databaseResult = $db->query('SELECT DATABASE()');
        if ($databaseResult instanceof \mysqli_result) {
            $databaseName = (string) ($databaseResult->fetch_row()[0] ?? '');
            $databaseResult->free();
        }

        $lockNames = self::bookIdentifierLockNames($databaseName, $codes);
        $acquired = [];
        try {
            foreach ($lockNames as $lockName) {
                $stmt = $db->prepare('SELECT GET_LOCK(?, 10)');
                if ($stmt === false) {
                    throw new \RuntimeException('Unable to prepare book-identifier lock.');
                }
                $stmt->bind_param('s', $lockName);
                try {
                    if (!$stmt->execute()) {
                        throw new \RuntimeException('Unable to acquire book-identifier lock: ' . $stmt->error);
                    }
                    $result = $stmt->get_result();
                    $locked = $result instanceof \mysqli_result
                        && (int) ($result->fetch_row()[0] ?? 0) === 1;
                } finally {
                    $stmt->close();
                }
                if (!$locked) {
                    throw new \RuntimeException('Timed out while waiting for a book-identifier lock.');
                }
                $acquired[] = $lockName;
            }
            return $acquired;
        } catch (\Throwable $e) {
            $this->releaseBookIdentifierLocks($db, $acquired);
            throw $e;
        }
    }

    /** @param list<string> $lockNames */
    private function releaseBookIdentifierLocks(mysqli $db, array $lockNames): void
    {
        foreach (array_reverse($lockNames) as $lockName) {
            try {
                $stmt = $db->prepare('SELECT RELEASE_LOCK(?)');
                if ($stmt === false) {
                    continue;
                }
                $stmt->bind_param('s', $lockName);
                try {
                    $stmt->execute();
                } finally {
                    $stmt->close();
                }
            } catch (\Throwable $e) {
                // Advisory locks are connection-scoped and a broken connection
                // releases them server-side. Never mask the original save error.
            }
        }
    }

    /**
     * Create a book from the admin form: validates input, resolves authors and
     * publishers (multi-publisher, issue #143), handles cover + scraping data,
     * then persists via BookRepository.
     */
    public function store(Request $request, Response $response, mysqli $db): Response
    {
        $data = $this->parseRequestBody($request);
        if ($data === null) {
            $_SESSION['error_message'] = __('Impossibile leggere i dati del modulo. Riprova.');
            return $response->withHeader('Location', url('/admin/books/create'))->withStatus(302);
        }
        if (!\App\Support\DigitalAttachments::mayEditSubmission($data)) { return $response->withStatus(403); }
        // CSRF validated by CsrfMiddleware

        // SECURITY: Debug logging rimosso per prevenire information disclosure
        // Il logging dettagliato è disponibile solo in ambiente development tramite AppLog

        // Merge all supported fields with defaults
        $fields = [
            'titolo' => '',
            'sottotitolo' => '',
            'isbn10' => '',
            'isbn13' => '',
            'ean' => '',
            'issn' => '',
            'genere_id' => 0,
            'sottogenere_id' => 0,
            'editore_id' => 0,
            'data_acquisizione' => null,
            'tipo_acquisizione' => '',
            'descrizione' => '',
            'parole_chiave' => '',
            'formato' => '',
            'peso' => null,
            'dimensioni' => '',
            'prezzo' => null,
            'copie_totali' => 1,
            'copie_disponibili' => 1,
            'numero_inventario' => '',
            'classificazione_dewey' => '',
            'collana' => '',
            'numero_serie' => '',
            'note_varie' => '',
            'file_url' => '',
            'audio_url' => '',
            'copertina_url' => '',
            'scaffale_id' => 0,
            'mensola_id' => 0,
            'posizione_progressiva' => 0,
            'posizione_id' => 0,
            'collocazione' => '',
            'stato' => '',
            'lingua' => '',
            'anno_pubblicazione' => null,
            'edizione' => '',
            'luogo_pubblicazione' => '',
            'data_pubblicazione' => '',
            'numero_pagine' => null,
        ];

        // Merge LibraryThing fields defaults only if plugin installed
        if (LibraryThingInstaller::isInstalled($db)) {
            $fields = array_merge($fields, self::LIBRARYTHING_FIELDS_DEFAULTS);
        }
        foreach ($fields as $k => $v) {
            if (array_key_exists($k, $data))
                $fields[$k] = $data[$k];
        }

        try {
            $fields = \App\Support\DigitalAttachments::applySubmission($fields, $data);
        } catch (\InvalidArgumentException $e) {
            $response->getBody()->write(json_encode(['error' => 'validation', 'message' => $e->getMessage()], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
        }

        // Normalize text fields to remove MARC-8 control characters and collapse whitespace
        $fields['titolo'] = $this->normalizeText($fields['titolo']);
        $fields['sottotitolo'] = $this->normalizeText($fields['sottotitolo']);

        // Validate title is not empty after normalization
        if (trim($fields['titolo']) === '') {
            $response->getBody()->write(json_encode([
                'error' => true,
                'message' => __('Il titolo del libro è obbligatorio.'),
            ], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(422)->withHeader('Content-Type', 'application/json');
        }

        // Merge scraped subtitle and notes if present
        $subtitleFromScrape = trim((string) ($data['subtitle'] ?? ''));
        if ($subtitleFromScrape !== '') {
            $fields['sottotitolo'] = $this->normalizeText($subtitleFromScrape);
        }

        $notesParts = [];
        $currentNotes = trim((string) ($fields['note_varie'] ?? ''));
        if ($currentNotes !== '') {
            $notesParts[] = $currentNotes;
        }
        $notesFromScrape = trim((string) ($data['notes'] ?? ''));
        if ($notesFromScrape !== '') {
            $notesParts[] = $notesFromScrape;
        }
        $tipologiaScrape = trim((string) ($data['scraped_tipologia'] ?? ''));
        if ($tipologiaScrape !== '') {
            $notesParts[] = 'Tipologia: ' . $tipologiaScrape;
        }
        if ($notesParts) {
            $uniqueNotes = [];
            foreach ($notesParts as $part) {
                $clean = trim($part);
                if ($clean === '')
                    continue;
                $exists = false;
                foreach ($uniqueNotes as $existing) {
                    if (strcasecmp($existing, $clean) === 0) {
                        $exists = true;
                        break;
                    }
                }
                if (!$exists) {
                    $uniqueNotes[] = $clean;
                }
            }
            $fields['note_varie'] = implode("\n", $uniqueNotes);
        }

        // Sanitize ISBN/EAN: strip spaces and dashes (server-side safety)
        foreach (['isbn10', 'isbn13', 'ean'] as $codeKey) {
            if (isset($fields[$codeKey])) {
                $rawValue = (string) $fields[$codeKey];

                // Input validation: prevent ReDoS by checking length before regex
                // ISBN-10: max 13 chars (10 digits + 3 separators), ISBN-13: max 17 chars (13 digits + 4 separators), EAN: max 13
                $maxLength = ($codeKey === 'isbn10') ? 13 : (($codeKey === 'isbn13') ? 17 : 13);
                if (strlen($rawValue) > $maxLength) {
                    $rawValue = substr($rawValue, 0, $maxLength);
                }

                $fields[$codeKey] = preg_replace('/[\s-]+/', '', $rawValue);
            }
        }

        // Normalize + validate ISSN (canonical format: XXXX-XXXX)
        if (isset($fields['issn'])) {
            try {
                $fields['issn'] = $this->normalizeIssn($fields['issn']);
            } catch (\InvalidArgumentException $e) {
                $_SESSION['error_message'] = __('ISSN non valido. Il formato corretto è XXXX-XXXX (8 cifre, l\'ultima può essere X).');
                return $response->withHeader('Location', url('/admin/books/create'))->withStatus(302);
            }
        }

        // Convert empty ISBN/EAN to NULL for UNIQUE constraint compatibility
        foreach (['isbn10', 'isbn13', 'ean'] as $codeKey) {
            if (isset($fields[$codeKey]) && $fields[$codeKey] === '') {
                $fields[$codeKey] = null;
            }
        }

        // Convert 0 to NULL for optional foreign keys to avoid constraint failures
        $fields['editore_id'] = empty($fields['editore_id']) || $fields['editore_id'] == 0 ? null : (int) $fields['editore_id'];
        $fields['genere_id'] = empty($fields['genere_id']) || $fields['genere_id'] == 0 ? null : (int) $fields['genere_id'];
        $fields['sottogenere_id'] = empty($fields['sottogenere_id']) || $fields['sottogenere_id'] == 0 ? null : (int) $fields['sottogenere_id'];
        // Reject non-scalar or non-integer input BEFORE casting: (int) "abc" is 0
        // and (int) (non-empty array) is 1, both of which would slip past the
        // 0..9999 bounds as a silent, wrong copy count. Only a genuine integer
        // string is honoured; anything else falls back to zero copies.
        $rawCopie = $fields['copie_totali'] ?? 0;
        $fields['copie_totali'] = (is_scalar($rawCopie) && preg_match('/^\d+$/', trim((string) $rawCopie)) === 1)
            ? (int) trim((string) $rawCopie)
            : 0;
        if ($fields['copie_totali'] > 9999) {
            $fields['copie_totali'] = 9999;
        }
        // In creazione, copie_disponibili = copie_totali (le copie sono tutte nuove e disponibili)
        $fields['copie_disponibili'] = $fields['copie_totali'];
        $fields['scaffale_id'] = empty($fields['scaffale_id']) ? null : (int) $fields['scaffale_id'];
        $fields['mensola_id'] = empty($fields['mensola_id']) ? null : (int) $fields['mensola_id'];
        $fields['posizione_progressiva'] = isset($fields['posizione_progressiva']) && $fields['posizione_progressiva'] !== '' ? (int) $fields['posizione_progressiva'] : null;
        $fields['posizione_id'] = null;
        $fields['peso'] = $fields['peso'] !== null && $fields['peso'] !== '' ? (float) $fields['peso'] : null;
        $fields['prezzo'] = $fields['prezzo'] !== null && $fields['prezzo'] !== '' ? (float) $fields['prezzo'] : null;
        $numPagineRaw = $fields['numero_pagine'] ?? null;
        $numPagine = filter_var($numPagineRaw, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]);
        $fields['numero_pagine'] = ($numPagine === false) ? null : $numPagine;
        // The form posts the same scraped URL in both copertina_url and
        // scraped_cover_url; this flag lets the scraped-cover branch skip the
        // redundant second download that would orphan a file on disk (#F009).
        $scrapedCoverAlreadySaved = false;
        // A localised external cover is written before the DB transaction. Keep
        // its path so an atomic book/copies rollback can remove that request's
        // file as well as its database rows.
        $coverCreatedBeforeAtomicInsert = '';
        if ($fields['copertina_url'] === '' || $fields['copertina_url'] === null) {
            $fields['copertina_url'] = null;
        } else {
            // Auto-download external cover URLs
            $originalCoverUrl = (string) $fields['copertina_url'];
            $fields['copertina_url'] = $this->downloadExternalCover($fields['copertina_url']);
            if (preg_match('#^https?://#i', $originalCoverUrl) === 1
                && is_string($fields['copertina_url'])
                && strpos($fields['copertina_url'], '/uploads/copertine/') === 0) {
                $coverCreatedBeforeAtomicInsert = $fields['copertina_url'];
            }
            if (is_string($fields['copertina_url'])
                && strpos($fields['copertina_url'], '/uploads/copertine/') === 0
                && isset($data['scraped_cover_url'])
                && (string) $data['scraped_cover_url'] === $originalCoverUrl) {
                $scrapedCoverAlreadySaved = true;
            }
        }

        // Ensure hierarchical consistency between genere_id (parent) and sottogenere_id (child)
        try {
            $genRepoTmp = new \App\Models\GenereRepository($db);
            if (!empty($fields['sottogenere_id'])) {
                $sub = $genRepoTmp->getById((int) $fields['sottogenere_id']);
                if ($sub && !empty($sub['parent_id'])) {
                    $fields['genere_id'] = (int) $sub['parent_id'];
                }
            } elseif (!empty($fields['genere_id'])) {
                // If a leaf has been posted as genere, promote its parent
                $g = $genRepoTmp->getById((int) $fields['genere_id']);
                if ($g && !empty($g['parent_id'])) {
                    // Posted value is actually a child; move it to sottogenere and set its parent as genere
                    $fields['sottogenere_id'] = (int) $fields['genere_id'];
                    $fields['genere_id'] = (int) $g['parent_id'];
                }
            }
        } catch (\Throwable $e) {
            // fail-safe: ignore and continue
        }


        // DEBUG: Log field processing for store method
        // SECURITY: Logging disabilitato in produzione per prevenire information disclosure
        if (getenv('APP_ENV') === 'development') {
            $debugDir = $this->getStoragePath();
            if (!is_dir($debugDir)) {
                @mkdir($debugDir, 0775, true);
            }
            $debugFile = $debugDir . '/field_debug.log';
            $debugEntry = "FIELD PROCESSING (STORE):\n";
            foreach ($fields as $key => $value) {
                $type = gettype($value);
                $displayValue = $value === null ? 'NULL' : (string) $value;
                if (strlen($displayValue) > 100)
                    $displayValue = substr($displayValue, 0, 100) . '...';
                $debugEntry .= "  {$key} ({$type}): '{$displayValue}'\n";
            }
            @file_put_contents($debugFile, $debugEntry, FILE_APPEND | LOCK_EX);
        }

        // Duplicate check on identifiers (EAN/ISBN) with advisory lock to prevent race conditions
        $codes = [];
        foreach (['isbn10', 'isbn13', 'ean'] as $k) {
            $v = trim((string) ($fields[$k] ?? ''));
            if ($v !== '') {
                $codes[$k] = $v;
            }
        }

        // Acquire one schema-scoped advisory lock per normalized identifier.
        // Create and update deliberately share this protocol: locking the whole
        // submitted tuple would not serialize partially-overlapping sets (for
        // example isbn13=X versus ean=X), while different create/update prefixes
        // would not serialize at all.
        $lockKeys = [];
        if (!empty($codes)) {
            try {
                $lockKeys = $this->acquireBookIdentifierLocks($db, $codes);
            } catch (\Throwable $e) {
                SecureLogger::error('LibriController::store identifier lock failed: ' . $e->getMessage());
                $response->getBody()->write(json_encode([
                    'error' => 'lock_error',
                    'message' => __('Errore interno durante acquisizione lock.')
                ], JSON_UNESCAPED_UNICODE));
                return $response->withStatus(503)->withHeader('Content-Type', 'application/json');
            }
        }

        try {
            // Perform duplicate check within the lock
            // IMPORTANT: Check ALL values against ALL identifier fields (isbn10, isbn13, ean)
            // because EAN-13 and ISBN-13 are identical for books
            if (!empty($codes)) {
                // Get unique values from all identifier fields
                $allValues = array_unique(array_values($codes));
                $placeholders = implode(',', array_fill(0, count($allValues), '?'));
                $types = str_repeat('s', count($allValues) * 3); // 3 fields to check
                $params = array_merge($allValues, $allValues, $allValues); // Same values for each field
                $inClause = "({$placeholders})";

                $sql = "SELECT l.id, l.titolo, l.isbn10, l.isbn13, l.ean, l.collocazione, l.scaffale_id, l.mensola_id, l.posizione_progressiva,
                        s.codice as scaffale_codice, m.numero_livello as mensola_livello
                        FROM libri l
                        LEFT JOIN scaffali s ON l.scaffale_id = s.id
                        LEFT JOIN mensole m ON l.mensola_id = m.id
                        WHERE (l.isbn10 IN {$inClause}
                           OR l.isbn13 IN {$inClause}
                           OR l.ean IN {$inClause})
                          AND l.deleted_at IS NULL
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param($types, ...$params);
                $stmt->execute();
                $dup = $stmt->get_result()->fetch_assoc();
                if ($dup) {
                    // Build location string
                    $location = '';
                    if (!empty($dup['scaffale_codice']) && !empty($dup['mensola_livello']) && !empty($dup['posizione_progressiva'])) {
                        $location = $dup['scaffale_codice'] . '.' . $dup['mensola_livello'] . '.' . $dup['posizione_progressiva'];
                    } elseif (!empty($dup['collocazione'])) {
                        $location = $dup['collocazione'];
                    }

                    // Return JSON with duplicate book info for frontend to handle
                    $response->getBody()->write(json_encode([
                        'error' => 'duplicate',
                        'message' => __('Esiste già un libro con lo stesso identificatore (ISBN/EAN).'),
                        'existing_book' => [
                            'id' => (int) $dup['id'],
                            'title' => (string) ($dup['titolo'] ?? ''),
                            'isbn10' => (string) ($dup['isbn10'] ?? ''),
                            'isbn13' => (string) ($dup['isbn13'] ?? ''),
                            'ean' => (string) ($dup['ean'] ?? ''),
                            'location' => $location
                        ]
                    ], JSON_UNESCAPED_UNICODE));
                    return $response->withStatus(409)->withHeader('Content-Type', 'application/json');
                }
            }

            $repo = new \App\Models\BookRepository($db);
            $fields['autori_ids'] = $this->positiveIntegerIds($data['autori_ids'] ?? []);
            // Contributor roles as entities (issue #237): illustrator/translator/
            // curator/colorist pickers, resolved to author ids (new names created).
            $contributorPickersReady = (string)($data['contributors_entity_picker'] ?? '') === '1';
            foreach (['illustratori', 'traduttori', 'curatori', 'coloristi'] as $contributorRole) {
                $resolvedContributorIds = $this->resolveContributorIds($db, $data, $contributorRole);
                if ($contributorPickersReady || $resolvedContributorIds !== []) {
                    $fields[$contributorRole . '_ids'] = $resolvedContributorIds;
                }
            }

            // Get scraped author bio if available
            $scrapedAuthorBio = trim((string) ($data['scraped_author_bio'] ?? ''));

            // Gestione autori nuovi da creare (con controllo duplicati normalizzati)
            if (!empty($data['autori_new'])) {
                $authRepo = new \App\Models\AuthorRepository($db);
                foreach ((array) $data['autori_new'] as $nomeCompleto) {
                    $nomeCompleto = trim((string) $nomeCompleto);
                    if ($nomeCompleto !== '') {
                        // Check if author already exists (handles "Levi, Primo" vs "Primo Levi")
                        $existingId = $authRepo->findByCanonicalName($nomeCompleto);
                        if ($existingId) {
                            $fields['autori_ids'][] = $existingId;
                            // Update bio if existing author has no bio and we have scraped one
                            if ($scrapedAuthorBio !== '') {
                                $this->updateAuthorBioIfEmpty($db, $existingId, $scrapedAuthorBio);
                            }
                        } else {
                            $authorId = $authRepo->create([
                                'nome' => $nomeCompleto,
                                'pseudonimo' => '',
                                'data_nascita' => null,
                                'data_morte' => null,
                                'nazionalita' => '',
                                'biografia' => $scrapedAuthorBio,
                                'sito_web' => ''
                            ]);
                            $fields['autori_ids'][] = $authorId;
                        }
                    }
                }
            }

            // Auto-create author from scraped data if no authors selected
            if (empty($fields['autori_ids']) && !empty($data['scraped_author'])) {
                $authRepo = new \App\Models\AuthorRepository($db);
                $scrapedAuthor = trim((string) $data['scraped_author']);
                if ($scrapedAuthor !== '') {
                    $found = $authRepo->findByCanonicalName($scrapedAuthor);
                    if ($found) {
                        $fields['autori_ids'][] = $found;
                        // Update bio if existing author has no bio and we have scraped one
                        if ($scrapedAuthorBio !== '') {
                            $this->updateAuthorBioIfEmpty($db, $found, $scrapedAuthorBio);
                        }
                    } else {
                        $authorId = $authRepo->create([
                            'nome' => $scrapedAuthor,
                            'pseudonimo' => '',
                            'data_nascita' => null,
                            'data_morte' => null,
                            'nazionalita' => '',
                            'biografia' => $scrapedAuthorBio,
                            'sito_web' => ''
                        ]);
                        $fields['autori_ids'][] = $authorId;
                    }
                }
            }

            // Update bio for the FIRST selected author if they have no bio
            // Scraped bio is from the book's primary author, don't apply to all authors
            if ($scrapedAuthorBio !== '' && !empty($fields['autori_ids'])) {
                $firstAuthorId = (int) reset($fields['autori_ids']);
                if ($firstAuthorId > 0) {
                    $this->updateAuthorBioIfEmpty($db, $firstAuthorId, $scrapedAuthorBio);
                }
            }

            // Handle publisher auto-creation from manual entry or scraped data
            if ((int) $fields['editore_id'] === 0) {
                $pubRepo = new \App\Models\PublisherRepository($db);
                $publisherName = '';

                // First try manual entry (editore_search field)
                if (!empty($data['editore_search'])) {
                    $publisherName = trim((string) $data['editore_search']);
                }
                // Fall back to scraped data
                elseif (!empty($data['scraped_publisher'])) {
                    $publisherName = trim((string) $data['scraped_publisher']);
                }

                $publisherName = $this->sanitizePublisherName($publisherName);

                if ($publisherName !== '') {
                    $found = $pubRepo->findByName($publisherName);
                    if ($found) {
                        $fields['editore_id'] = $found;
                    } else {
                        $fields['editore_id'] = $pubRepo->create(['nome' => $publisherName, 'sito_web' => '']);
                    }
                }
            }

            // Multi-publisher resolution (issue #143): existing ids + new names
            // → ordered, deduplicated id list; libri.editore_id stays the primary.
            $fields['editori_ids'] = $this->resolvePublisherIds($db, $data, $fields['editore_id'] ?? null);
            $fields['editore_id'] = $fields['editori_ids'][0] ?? ($fields['editore_id'] ?? null);

            // Handle genere auto-creation from manual entry
            if ((int) $fields['genere_id'] === 0 && !empty($data['genere_search'])) {
                $genereRepo = new \App\Models\GenereRepository($db);
                $genereName = trim((string) $data['genere_search']);

                if ($genereName !== '') {
                    $found = $genereRepo->findByName($genereName);
                    if ($found) {
                        $fields['genere_id'] = $found;
                    } else {
                        $fields['genere_id'] = $genereRepo->create(['nome' => $genereName]);
                    }
                }
            }

            // Handle sottogenere auto-creation from manual entry
            if ((int) $fields['sottogenere_id'] === 0 && !empty($data['sottogenere_search'])) {
                $genereRepo = new \App\Models\GenereRepository($db);
                $sottogenereName = trim((string) $data['sottogenere_search']);

                if ($sottogenereName !== '') {
                    // If we have a parent genere, use it
                    $parent_id = !empty($fields['genere_id']) ? (int) $fields['genere_id'] : null;

                    $found = $genereRepo->findByName($sottogenereName, $parent_id);
                    if ($found) {
                        $fields['sottogenere_id'] = $found;
                    } else {
                        $fields['sottogenere_id'] = $genereRepo->create([
                            'nome' => $sottogenereName,
                            'parent_id' => $parent_id
                        ]);
                    }
                }
            }
            $collRepo = new \App\Models\CollocationRepository($db);
            if ($fields['scaffale_id'] && $fields['mensola_id']) {
                $pos = $fields['posizione_progressiva'] ?? null;
                if ($pos === null || $pos <= 0) {
                    $pos = $collRepo->computeNextProgressiva($fields['scaffale_id'], $fields['mensola_id']);
                }
                $fields['posizione_progressiva'] = $pos;
                $fields['collocazione'] = $collRepo->buildCollocazioneString($fields['scaffale_id'], $fields['mensola_id'], $pos);
            } else {
                $fields['posizione_progressiva'] = null;
                $fields['collocazione'] = $fields['collocazione'] ?? '';
            }

            $fields = \App\Support\Hooks::apply('book.form.save', $fields, [$data, null]);
            $fields = \App\Support\DigitalAttachments::applySubmission($fields, $data);

            // Plugin hook: Before book save
            \App\Support\Hooks::do('book.save.before', [$fields, null]);

            // Atomic create: the book row and its initial physical copies must
            // persist together or not at all — a copy-creation failure used to
            // leave an orphan book with no/partial holdings. The transaction
            // wraps ONLY createBasic() + createManyForBook() + the SQL-only
            // availability reconciliation. createBasic detects the open
            // transaction via its savepoint probe and nests with SAVEPOINT;
            // DataIntegrity is explicitly told that this transaction belongs
            // to the caller. No plugin hook fires inside it — book.save.after
            // handlers (e.g. book-club) can open their own transaction, which
            // under mysqli would implicitly commit the enclosing transaction
            // and silently destroy this atomicity. Hooks and series/LT metadata
            // therefore run strictly after the commit.
            if (!$db->begin_transaction()) {
                throw new \RuntimeException('Database error: unable to begin book create transaction');
            }
            try {
                $id = $repo->createBasic($fields);

                // Genera copie fisiche del libro
                $copyRepo = new \App\Models\CopyRepository($db);
                $copieTotali = (int) $fields['copie_totali'];
                $baseInventario = \App\Models\CopyRepository::inventoryBase($id, $fields['numero_inventario'] ?? null);

                // Create the requested holding set with one atomic multi-row INSERT:
                // a request for three copies must never leave a partial 1/3 or 2/3
                // result if one inventory code fails. Codes stay uniform (-C1, -C2,
                // ...) and collision-free through the repository allocator.
                $createdCopies = $copyRepo->createManyForBook(
                    $id,
                    $baseInventario,
                    $copieTotali,
                    'disponibile',
                    __('Copia %d di %d')
                );
                if ($createdCopies !== $copieTotali) {
                    throw new \RuntimeException('Unable to create the requested physical copies.');
                }

                // Counters and canonical state are part of the same invariant as
                // the book and its holdings. If reconciliation fails, rolling
                // back here prevents a committed book whose summary/API fields
                // disagree with the physical copies just created.
                $availabilityUpdated = (new \App\Support\DataIntegrity($db))
                    ->recalculateBookAvailability($id, true);
                if (!$availabilityUpdated) {
                    throw new \RuntimeException('Unable to recalculate availability for the new book.');
                }

                if (!$db->commit()) {
                    throw new \RuntimeException('Database error: unable to commit book create transaction');
                }
            } catch (\Throwable $atomicCreateError) {
                try {
                    $db->rollback();
                } catch (\Throwable $rollbackError) {
                    // best-effort — a dropped connection must not mask the original error
                }
                // The cover download is filesystem I/O and cannot participate in
                // mysqli's transaction. Delete only the file localised by this
                // request; raw external URLs and pre-existing local files no-op.
                try {
                    $this->deleteLocalCoverFile($coverCreatedBeforeAtomicInsert);
                } catch (\Throwable $coverCleanupError) {
                    \App\Support\SecureLogger::warning('Unable to clean up cover after atomic book rollback', [
                        'error' => $coverCleanupError->getMessage(),
                    ]);
                }
                \App\Support\SecureLogger::error('LibriController::store atomic book+copies create failed', [
                    'error' => $atomicCreateError->getMessage(),
                ]);
                throw $atomicCreateError;
            }

            $this->syncSeriesMetadataFromBookForm($db, $id, $fields, $data);

            // Handle LibraryThing fields visibility preferences
            if (LibraryThingInstaller::isInstalled($db)
                && isset($data['lt_visibility']) && is_array($data['lt_visibility'])) {
                $this->saveLtVisibility($db, $id, $data['lt_visibility']);
            }

            // Plugin hook: After book save
            \App\Support\Hooks::do('book.save.after', [$id, $fields]);

            // Persist all fields first, then apply an explicitly chosen cover
            // (file upload or scraped URL) on top so it isn't reverted by the
            // field update (mirrors update(); see #165).
            // Optionals (numero_pagine, ean, data_pubblicazione, traduttore)
            // Merge normalized $fields over $data so NULL isbn/ean values are preserved
            (new \App\Models\BookRepository($db))->updateOptionals($id, array_merge($data, $fields));
            // Every identifier write is now committed and visible. Release before
            // cover I/O/search-index work so a duplicate request can fail fast
            // instead of waiting behind unrelated post-save processing.
            $this->releaseBookIdentifierLocks($db, $lockKeys);
            $lockKeys = [];
            // The cover persisted just above may be a local file that
            // downloadExternalCover() saved during this submit; capture it so
            // it can be cleaned up if the branches below replace it (#F002).
            $intermediateCover = (string) ($fields['copertina_url'] ?? '');

            // Handle simple cover upload (wins over a scraped URL when both present)
            $coverFileUploaded = false;
            if (!empty($_FILES['copertina']) && ($_FILES['copertina']['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_NO_FILE) {
                $this->handleCoverUpload($db, $id, $_FILES['copertina']);
                $coverFileUploaded = true;
            }
            // A pending cover removal (remove_cover=1) wins over a stale
            // scraped_cover_url, mirroring update() for symmetry (#F007). A new
            // book has no prior cover, so this is purely defensive on the create
            // path. Reuse the same === '1' comparison as update()'s removal branch.
            $removeCoverRequested = (isset($data['remove_cover']) && $data['remove_cover'] === '1');
            if (!$coverFileUploaded && !$removeCoverRequested && !$scrapedCoverAlreadySaved && !empty($data['scraped_cover_url'])) {
                $this->handleCoverUrl($db, $id, (string) $data['scraped_cover_url']);
            }

            // A file upload (or a different scraped URL) above may have just
            // replaced the cover that downloadExternalCover() saved during this
            // same submit, leaving that intermediate file orphaned on disk
            // (#F002). deleteLocalCoverFile() already no-ops when the
            // intermediate IS the final cover (pure-scrape path) or isn't a
            // local /uploads/copertine/ file.
            if (!$removeCoverRequested) {
                $finalCover = $this->currentCoverUrl($db, $id);
                $this->deleteLocalCoverFile($intermediateCover, $finalCover);
            }

            // Rebuild the denormalized FULLTEXT search_index now that the book row
            // (createBasic + updateOptionals) and its author links (syncAuthors) are
            // all persisted and readable.
            \App\Support\SearchIndexBuilder::rebuild($db, (int) $id);

            // Invalidate cached public pages (home dataset, catalog counts/facets)
            \App\Support\ContentCache::booksChanged();

            $createdSnapshot = \App\Support\ActivityLog::loadBookSnapshot($db, (int) $id);
            \App\Support\ActivityLog::recordBookEvent(
                $db,
                (int) $id,
                'inserimento',
                'edit',
                'book.created',
                [],
                $createdSnapshot,
                bookTitle: (string) ($createdSnapshot['titolo'] ?? $fields['titolo'] ?? ''),
                source: 'manual'
            );

            // Set a success message in the session
            $_SESSION['success_message'] = __('Libro aggiunto con successo!');

            return $response->withHeader('Location', url('/admin/books/' . $id))->withStatus(302);

        } finally {
            $this->releaseBookIdentifierLocks($db, $lockKeys);
        }
    }

    public function editForm(Request $request, Response $response, mysqli $db, int $id): Response
    {
        $repo = new \App\Models\BookRepository($db);
        $libro = $repo->getById($id);
        if (!$libro) {
            throw new HttpNotFoundException($request);
        }
        $editRepo = new \App\Models\PublisherRepository($db);
        $autRepo = new \App\Models\AuthorRepository($db);
        $editori = $editRepo->listBasic();
        $autori = $autRepo->listBasic(500);
        $colRepo = new \App\Models\CollocationRepository($db);
        $taxRepo = new \App\Models\TaxonomyRepository($db);
        $scaffali = $colRepo->getScaffali();
        $mensole = $colRepo->getMensole();
        $generi = $taxRepo->genres();
        $sottogeneri = $taxRepo->subgenres();
        $libraryThingInstalled = \App\Support\LibraryThingInstaller::isInstalled($db);
        $articleHintState = \App\Support\PeriodicalArticlesHint::state($db);
        ob_start();
        // Variables are available in the template scope via require
        require __DIR__ . '/../Views/libri/modifica_libro.php';
        $content = ob_get_clean();
        ob_start();
        require __DIR__ . '/../Views/layout.php';
        $html = ob_get_clean();
        $response->getBody()->write($html);
        return $response;
    }

    /**
     * Update an existing book from the admin form: same validation and
     * author/publisher resolution as store() (multi-publisher, issue #143),
     * then persists the changes via BookRepository.
     */
    public function update(Request $request, Response $response, mysqli $db, int $id): Response
    {
        // Snapshotted before any work, so the success message at the end can
        // tell an error THIS request raised (a cover/plugin step writing the
        // flash) from a stale one left by an earlier request that nobody has
        // rendered yet — the latter must not swallow "book updated".
        $errorBeforeUpdate = $_SESSION['error_message'] ?? null;

        $data = $this->parseRequestBody($request);
        if ($data === null) {
            $_SESSION['error_message'] = __('Impossibile leggere i dati del modulo. Riprova.');
            return $response->withHeader('Location', url('/admin/books/edit/' . $id))->withStatus(302);
        }
        // CSRF validated by CsrfMiddleware
        $repo = new \App\Models\BookRepository($db);
        $currentBook = $repo->getById($id);
        if (!$currentBook) {
            throw new HttpNotFoundException($request);
        }
        if (!\App\Support\DigitalAttachments::mayEditSubmission($data, $currentBook)) { return $response->withStatus(403); }
        if (!\App\Support\Permissions::can('digital.manage')) {
            $data['file_url'] = $currentBook['file_url'] ?? '';
            $data['audio_url'] = $currentBook['audio_url'] ?? '';
        }
        $fields = [
            'titolo' => '',
            'sottotitolo' => '',
            'isbn10' => '',
            'isbn13' => '',
            'ean' => '',
            'issn' => '',
            'genere_id' => 0,
            'sottogenere_id' => 0,
            'editore_id' => 0,
            'data_acquisizione' => null,
            'tipo_acquisizione' => '',
            'descrizione' => '',
            'parole_chiave' => '',
            'formato' => '',
            'peso' => null,
            'dimensioni' => '',
            'prezzo' => null,
            'copie_totali' => 1,
            'copie_disponibili' => 1,
            'numero_inventario' => '',
            'classificazione_dewey' => '',
            'collana' => '',
            'numero_serie' => '',
            'note_varie' => '',
            'file_url' => '',
            'audio_url' => '',
            'copertina_url' => '',
            'scaffale_id' => 0,
            'mensola_id' => 0,
            'posizione_progressiva' => 0,
            'posizione_id' => 0,
            'collocazione' => '',
            'stato' => '',
            'lingua' => '',
            'anno_pubblicazione' => null,
            'edizione' => '',
            'luogo_pubblicazione' => '',
            'data_pubblicazione' => '',
            'numero_pagine' => null,
        ];

        // Merge LibraryThing fields defaults only if plugin installed
        if (LibraryThingInstaller::isInstalled($db)) {
            $fields = array_merge($fields, self::LIBRARYTHING_FIELDS_DEFAULTS);
        }
        foreach ($fields as $k => $v) {
            if (array_key_exists($k, $data))
                $fields[$k] = $data[$k];
        }

        try {
            $fields = \App\Support\DigitalAttachments::applySubmission($fields, $data);
        } catch (\InvalidArgumentException $e) {
            $response->getBody()->write(json_encode(['error' => 'validation', 'message' => $e->getMessage()], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
        }

        // Normalize text fields to remove MARC-8 control characters and collapse whitespace
        $fields['titolo'] = $this->normalizeText($fields['titolo']);
        $fields['sottotitolo'] = $this->normalizeText($fields['sottotitolo']);

        // Sanitize ISBN/EAN on update as well
        foreach (['isbn10', 'isbn13', 'ean'] as $codeKey) {
            if (isset($fields[$codeKey])) {
                $rawValue = (string) $fields[$codeKey];

                // Input validation: prevent ReDoS by checking length before regex
                // ISBN-10: max 13 chars (10 digits + 3 separators), ISBN-13: max 17 chars (13 digits + 4 separators), EAN: max 13
                $maxLength = ($codeKey === 'isbn10') ? 13 : (($codeKey === 'isbn13') ? 17 : 13);
                if (strlen($rawValue) > $maxLength) {
                    $rawValue = substr($rawValue, 0, $maxLength);
                }

                $fields[$codeKey] = preg_replace('/[\s-]+/', '', $rawValue);
            }
        }

        // Normalize + validate ISSN on update (same logic as create)
        if (isset($fields['issn'])) {
            try {
                $fields['issn'] = $this->normalizeIssn($fields['issn']);
            } catch (\InvalidArgumentException $e) {
                $_SESSION['error_message'] = __('ISSN non valido. Il formato corretto è XXXX-XXXX (8 cifre, l\'ultima può essere X).');
                return $response->withHeader('Location', url('/admin/books/edit/' . $id))->withStatus(302);
            }
        }

        // Merge scraped subtitle and notes if present
        $subtitleFromScrape = trim((string) ($data['subtitle'] ?? ''));
        if ($subtitleFromScrape !== '' && trim((string) $fields['sottotitolo']) === '') {
            $fields['sottotitolo'] = $this->normalizeText($subtitleFromScrape);
        }

        $notesParts = [];
        $currentNotes = trim((string) ($fields['note_varie'] ?? ''));
        if ($currentNotes !== '') {
            $notesParts[] = $currentNotes;
        }
        $notesFromScrape = trim((string) ($data['notes'] ?? ''));
        if ($notesFromScrape !== '') {
            $notesParts[] = $notesFromScrape;
        }
        $tipologiaScrape = trim((string) ($data['scraped_tipologia'] ?? ''));
        if ($tipologiaScrape !== '') {
            $notesParts[] = 'Tipologia: ' . $tipologiaScrape;
        }
        if ($notesParts) {
            $uniqueNotes = [];
            foreach ($notesParts as $part) {
                $clean = trim($part);
                if ($clean === '')
                    continue;
                $exists = false;
                foreach ($uniqueNotes as $existing) {
                    if (strcasecmp($existing, $clean) === 0) {
                        $exists = true;
                        break;
                    }
                }
                if (!$exists) {
                    $uniqueNotes[] = $clean;
                }
            }
            $fields['note_varie'] = implode("\n", $uniqueNotes);
        }

        // Convert empty ISBN/EAN to NULL for UNIQUE constraint compatibility
        foreach (['isbn10', 'isbn13', 'ean'] as $codeKey) {
            if (isset($fields[$codeKey]) && $fields[$codeKey] === '') {
                $fields[$codeKey] = null;
            }
        }

        // Convert 0 to NULL for optional foreign keys to avoid constraint failures
        $fields['editore_id'] = empty($fields['editore_id']) || $fields['editore_id'] == 0 ? null : (int) $fields['editore_id'];
        $fields['genere_id'] = empty($fields['genere_id']) || $fields['genere_id'] == 0 ? null : (int) $fields['genere_id'];
        $fields['sottogenere_id'] = empty($fields['sottogenere_id']) || $fields['sottogenere_id'] == 0 ? null : (int) $fields['sottogenere_id'];
        // Copies are managed individually from the book summary (#physical-copies);
        // the edit form's "Copie in circolazione" field is read-only. Ignore any
        // submitted copie_totali — derive it from the copie table — so a crafted
        // POST that bypasses the client-side readonly cannot drive the copy
        // reconciliation below to silently add or delete copies. The derived value
        // matches libri.copie_totali (same out-of-circulation exclusion as
        // DataIntegrity), so the add/remove branches become guaranteed no-ops.
        $copyRepoCount = new \App\Models\CopyRepository($db);
        $fields['copie_totali'] = $copyRepoCount->countInCirculationByBookId($id);

        // Non aggiorniamo disponibilità/stato dall'utente: sono derivati dalle copie.
        unset($fields['copie_disponibili']);
        unset($fields['stato']);

        $fields['scaffale_id'] = empty($fields['scaffale_id']) || $fields['scaffale_id'] == 0 ? null : (int) $fields['scaffale_id'];
        $fields['mensola_id'] = empty($fields['mensola_id']) || $fields['mensola_id'] == 0 ? null : (int) $fields['mensola_id'];
        $fields['posizione_progressiva'] = isset($fields['posizione_progressiva']) && $fields['posizione_progressiva'] !== '' ? (int) $fields['posizione_progressiva'] : null;
        $fields['posizione_id'] = null;
        $fields['peso'] = $fields['peso'] !== null && $fields['peso'] !== '' ? (float) $fields['peso'] : null;
        $fields['prezzo'] = $fields['prezzo'] !== null && $fields['prezzo'] !== '' ? (float) $fields['prezzo'] : null;
        $numPagineRaw = $fields['numero_pagine'] ?? null;
        $numPagine = filter_var($numPagineRaw, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]);
        $fields['numero_pagine'] = ($numPagine === false) ? null : $numPagine;

        // When a scraped cover is saved, the form posts the SAME external URL in
        // both copertina_url and scraped_cover_url. downloadExternalCover() below
        // already fetches+stores it locally; this flag lets the scraped-cover
        // branch skip re-downloading the identical image, which otherwise leaves
        // an orphaned file on disk and wastes a network round-trip (#F009).
        $scrapedCoverAlreadySaved = false;

        // Gestione rimozione copertina
        if (isset($data['remove_cover']) && $data['remove_cover'] === '1') {
            // Cancella il file della copertina esistente se presente
            if (!empty($currentBook['copertina_url'])) {
                $oldCoverPath = $currentBook['copertina_url'];
                // Solo se è un file locale (non URL esterno)
                if (strpos($oldCoverPath, '/uploads/copertine/') === 0) {
                    // SECURITY FIX #2 (CRITICAL): Prevent path traversal attacks
                    $requestedPath = __DIR__ . '/../../public' . $oldCoverPath;
                    $safePath = realpath($requestedPath);
                    $baseDir = realpath($this->getCoversUploadPath());

                    // Ensure resolved path is within allowed directory
                    if ($safePath && $baseDir && strpos($safePath, $baseDir) === 0 && file_exists($safePath)) {
                        // nosemgrep: php.lang.security.unlink-use.unlink-use -- path confined by the realpath() containment check above (SECURITY FIX #2), not user input
                        unlink($safePath);
                        $this->logCoverDebug('cover.deleted', ['path' => $oldCoverPath]);
                    } else {
                        $this->logCoverDebug('cover.delete.security_block', [
                            'requested' => $oldCoverPath,
                            'resolved' => $safePath,
                            'baseDir' => $baseDir
                        ]);
                    }
                }
            }
            // Imposta a NULL per rimuovere dal database
            $fields['copertina_url'] = null;
        } elseif ($fields['copertina_url'] === '' || $fields['copertina_url'] === null) {
            $fields['copertina_url'] = null;
        } else {
            // Auto-download external cover URLs
            $originalCoverUrl = (string) $fields['copertina_url'];
            $fields['copertina_url'] = $this->downloadExternalCover($fields['copertina_url']);
            // If the download produced a local file AND the scraped-cover branch
            // would re-fetch the very same URL, mark it already saved so we don't
            // download it twice and orphan the first file (#F009). When the domain
            // isn't whitelisted, downloadExternalCover returns the URL unchanged
            // (no local file) and the flag stays false, so handleCoverUrl() — with
            // its own SSRF-guarded fetch — still runs and saves the cover.
            if (is_string($fields['copertina_url'])
                && strpos($fields['copertina_url'], '/uploads/copertine/') === 0
                && isset($data['scraped_cover_url'])
                && (string) $data['scraped_cover_url'] === $originalCoverUrl) {
                $scrapedCoverAlreadySaved = true;
            } elseif (is_string($fields['copertina_url'])
                && $fields['copertina_url'] !== ''
                && strpos($fields['copertina_url'], '/uploads/copertine/') !== 0) {
                // #F006: downloadExternalCover() returned a RAW external URL — the
                // download failed (non-whitelisted domain / SSRF block / HTTP/network
                // error). Never repoint the book to a possibly-dead remote URL: keep
                // the existing cover. A whitelisted scraped_cover_url, if present,
                // still gets its own SSRF-guarded fetch via handleCoverUrl() below;
                // if that can't fetch either, the current working cover stays.
                $fields['copertina_url'] = ($currentBook['copertina_url'] ?? '') !== ''
                    ? (string) $currentBook['copertina_url']
                    : null;
            }
        }

        // Ensure hierarchical consistency between genere_id and sottogenere_id also on update
        try {
            $genRepoTmp = new \App\Models\GenereRepository($db);
            if (!empty($fields['sottogenere_id'])) {
                $sub = $genRepoTmp->getById((int) $fields['sottogenere_id']);
                if ($sub && !empty($sub['parent_id'])) {
                    $fields['genere_id'] = (int) $sub['parent_id'];
                }
            } elseif (!empty($fields['genere_id'])) {
                $g = $genRepoTmp->getById((int) $fields['genere_id']);
                if ($g && !empty($g['parent_id'])) {
                    $fields['sottogenere_id'] = (int) $fields['genere_id'];
                    $fields['genere_id'] = (int) $g['parent_id'];
                }
            }
        } catch (\Throwable $e) { /* ignore */
        }

        // Duplicate check on update (exclude current record) with advisory lock to prevent race conditions
        $codes = [];
        foreach (['isbn10', 'isbn13', 'ean'] as $k) {
            $v = trim((string) ($fields[$k] ?? ''));
            if ($v !== '') {
                $codes[$k] = $v;
            }
        }

        // Same per-identifier protocol as store(): create/update and partially
        // overlapping ISBN/EAN sets must contend on the same lock names.
        $lockKeys = [];
        if (!empty($codes)) {
            try {
                $lockKeys = $this->acquireBookIdentifierLocks($db, $codes);
            } catch (\Throwable $e) {
                SecureLogger::error('LibriController::update identifier lock failed: ' . $e->getMessage());
                $_SESSION['error_message'] = __('Errore del server. Riprova.');
                return $response->withHeader('Location', url('/admin/books/edit/' . $id))->withStatus(302);
            }
        }

        try {
            // Perform duplicate check within the lock
            // IMPORTANT: Check ALL values against ALL identifier fields (isbn10, isbn13, ean)
            // because EAN-13 and ISBN-13 are identical for books
            if (!empty($codes)) {
                // Get unique values from all identifier fields
                $allValues = array_unique(array_values($codes));
                $placeholders = implode(',', array_fill(0, count($allValues), '?'));
                $types = str_repeat('s', count($allValues) * 3) . 'i'; // 3 fields + book id
                $params = array_merge($allValues, $allValues, $allValues, [$id]);
                $inClause = "({$placeholders})";

                $sql = "SELECT l.id, l.titolo, l.isbn10, l.isbn13, l.ean, l.collocazione,
                               s.codice AS scaffale_codice, m.numero_livello AS mensola_livello, l.posizione_progressiva
                        FROM libri l
                        LEFT JOIN scaffali s ON l.scaffale_id = s.id
                        LEFT JOIN mensole m ON l.mensola_id = m.id
                        WHERE (l.isbn10 IN {$inClause}
                           OR l.isbn13 IN {$inClause}
                           OR l.ean IN {$inClause})
                          AND l.id <> ?
                          AND l.deleted_at IS NULL
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param($types, ...$params);
                $stmt->execute();
                $dup = $stmt->get_result()->fetch_assoc();
                if ($dup) {
                    // Build location string
                    $location = '';
                    if (!empty($dup['scaffale_codice']) && !empty($dup['mensola_livello']) && !empty($dup['posizione_progressiva'])) {
                        $location = $dup['scaffale_codice'] . '.' . $dup['mensola_livello'] . '.' . $dup['posizione_progressiva'];
                    } elseif (!empty($dup['collocazione'])) {
                        $location = $dup['collocazione'];
                    }

                    // Return JSON with duplicate book info for frontend to handle
                    $response->getBody()->write(json_encode([
                        'error' => 'duplicate',
                        'message' => __('Esiste già un altro libro con lo stesso identificatore (ISBN/EAN).'),
                        'existing_book' => [
                            'id' => (int) $dup['id'],
                            'title' => (string) ($dup['titolo'] ?? ''),
                            'isbn10' => (string) ($dup['isbn10'] ?? ''),
                            'isbn13' => (string) ($dup['isbn13'] ?? ''),
                            'ean' => (string) ($dup['ean'] ?? ''),
                            'location' => $location
                        ]
                    ], JSON_UNESCAPED_UNICODE));
                    return $response->withStatus(409)->withHeader('Content-Type', 'application/json');
                }
            }
            $fields['autori_ids'] = $this->positiveIntegerIds($data['autori_ids'] ?? []);
            // Contributor roles as entities (issue #237): illustrator/translator/
            // curator/colorist pickers, resolved to author ids (new names created).
            $contributorPickersReady = (string)($data['contributors_entity_picker'] ?? '') === '1';
            foreach (['illustratori', 'traduttori', 'curatori', 'coloristi'] as $contributorRole) {
                $resolvedContributorIds = $this->resolveContributorIds($db, $data, $contributorRole);
                if ($contributorPickersReady || $resolvedContributorIds !== []) {
                    $fields[$contributorRole . '_ids'] = $resolvedContributorIds;
                }
            }

            $collRepo = new \App\Models\CollocationRepository($db);
            if ($fields['scaffale_id'] && $fields['mensola_id']) {
                $pos = $fields['posizione_progressiva'] ?? null;
                if ($pos === null || $pos <= 0) {
                    $pos = $collRepo->computeNextProgressiva($fields['scaffale_id'], $fields['mensola_id'], $id);
                }
                $fields['posizione_progressiva'] = $pos;
                $fields['collocazione'] = $collRepo->buildCollocazioneString($fields['scaffale_id'], $fields['mensola_id'], $pos);
            } else {
                $fields['posizione_progressiva'] = null;
                $fields['collocazione'] = '';
            }

            // Get scraped author bio if available (for update method)
            $scrapedAuthorBioUpdate = trim((string) ($data['scraped_author_bio'] ?? ''));

            // Gestione autori nuovi da creare (con controllo duplicati normalizzati)
            if (!empty($data['autori_new'])) {
                $authRepo = new \App\Models\AuthorRepository($db);
                foreach ((array) $data['autori_new'] as $nomeCompleto) {
                    $nomeCompleto = trim((string) $nomeCompleto);
                    if ($nomeCompleto !== '') {
                        // Check if author already exists (handles "Levi, Primo" vs "Primo Levi")
                        $existingId = $authRepo->findByCanonicalName($nomeCompleto);
                        if ($existingId) {
                            $fields['autori_ids'][] = $existingId;
                            // Update bio if existing author has no bio and we have scraped one
                            if ($scrapedAuthorBioUpdate !== '') {
                                $this->updateAuthorBioIfEmpty($db, $existingId, $scrapedAuthorBioUpdate);
                            }
                        } else {
                            $authorId = $authRepo->create([
                                'nome' => $nomeCompleto,
                                'pseudonimo' => '',
                                'data_nascita' => null,
                                'data_morte' => null,
                                'nazionalita' => '',
                                'biografia' => $scrapedAuthorBioUpdate,
                                'sito_web' => ''
                            ]);
                            $fields['autori_ids'][] = $authorId;
                        }
                    }
                }
            }

            // Auto-create author from scraped data if no authors selected
            if (empty($fields['autori_ids']) && !empty($data['scraped_author'])) {
                $authRepo = new \App\Models\AuthorRepository($db);
                $scrapedAuthor = trim((string) $data['scraped_author']);
                if ($scrapedAuthor !== '') {
                    $found = $authRepo->findByCanonicalName($scrapedAuthor);
                    if ($found) {
                        $fields['autori_ids'][] = $found;
                        // Update bio if existing author has no bio and we have scraped one
                        if ($scrapedAuthorBioUpdate !== '') {
                            $this->updateAuthorBioIfEmpty($db, $found, $scrapedAuthorBioUpdate);
                        }
                    } else {
                        $authorId = $authRepo->create([
                            'nome' => $scrapedAuthor,
                            'pseudonimo' => '',
                            'data_nascita' => null,
                            'data_morte' => null,
                            'nazionalita' => '',
                            'biografia' => $scrapedAuthorBioUpdate,
                            'sito_web' => ''
                        ]);
                        $fields['autori_ids'][] = $authorId;
                    }
                }
            }

            // Update bio for the FIRST selected author if they have no bio
            // Scraped bio is from the book's primary author, don't apply to all authors
            if ($scrapedAuthorBioUpdate !== '' && !empty($fields['autori_ids'])) {
                $firstAuthorIdUpdate = (int) reset($fields['autori_ids']);
                if ($firstAuthorIdUpdate > 0) {
                    $this->updateAuthorBioIfEmpty($db, $firstAuthorIdUpdate, $scrapedAuthorBioUpdate);
                }
            }

            // Handle publisher auto-creation from manual entry or scraped data
            if ((int) $fields['editore_id'] === 0) {
                $pubRepo = new \App\Models\PublisherRepository($db);
                $publisherName = '';

                // First try manual entry (editore_search field)
                if (!empty($data['editore_search'])) {
                    $publisherName = trim((string) $data['editore_search']);
                }
                // Fall back to scraped data
                elseif (!empty($data['scraped_publisher'])) {
                    $publisherName = trim((string) $data['scraped_publisher']);
                }

                $publisherName = $this->sanitizePublisherName($publisherName);

                if ($publisherName !== '') {
                    $found = $pubRepo->findByName($publisherName);
                    if ($found) {
                        $fields['editore_id'] = $found;
                    } else {
                        $fields['editore_id'] = $pubRepo->create(['nome' => $publisherName, 'sito_web' => '']);
                    }
                }
            }

            // Multi-publisher resolution (issue #143): existing ids + new names
            // → ordered, deduplicated id list; libri.editore_id stays the primary.
            $fields['editori_ids'] = $this->resolvePublisherIds($db, $data, $fields['editore_id'] ?? null);
            $fields['editore_id'] = $fields['editori_ids'][0] ?? ($fields['editore_id'] ?? null);

            // Handle genere auto-creation from manual entry
            if ((int) $fields['genere_id'] === 0 && !empty($data['genere_search'])) {
                $genereRepo = new \App\Models\GenereRepository($db);
                $genereName = trim((string) $data['genere_search']);

                if ($genereName !== '') {
                    $found = $genereRepo->findByName($genereName);
                    if ($found) {
                        $fields['genere_id'] = $found;
                    } else {
                        $fields['genere_id'] = $genereRepo->create(['nome' => $genereName]);
                    }
                }
            }

            // Handle sottogenere auto-creation from manual entry
            if ((int) $fields['sottogenere_id'] === 0 && !empty($data['sottogenere_search'])) {
                $genereRepo = new \App\Models\GenereRepository($db);
                $sottogenereName = trim((string) $data['sottogenere_search']);

                if ($sottogenereName !== '') {
                    // If we have a parent genere, use it
                    $parent_id = !empty($fields['genere_id']) ? (int) $fields['genere_id'] : null;

                    $found = $genereRepo->findByName($sottogenereName, $parent_id);
                    if ($found) {
                        $fields['sottogenere_id'] = $found;
                    } else {
                        $fields['sottogenere_id'] = $genereRepo->create([
                            'nome' => $sottogenereName,
                            'parent_id' => $parent_id
                        ]);
                    }
                }
            }

            $fields = \App\Support\Hooks::apply('book.form.save', $fields, [$data, $id]);
            $fields = \App\Support\DigitalAttachments::applySubmission($fields, $data);

            // Plugin hook: Before book save (update)
            \App\Support\Hooks::do('book.save.before', [$fields, $id]);

            $repo->updateBasic($id, $fields);
            $this->syncSeriesMetadataFromBookForm($db, $id, $fields, $data);

            // Handle LibraryThing fields visibility preferences
            if (LibraryThingInstaller::isInstalled($db)
                && isset($data['lt_visibility']) && is_array($data['lt_visibility'])) {
                $this->saveLtVisibility($db, $id, $data['lt_visibility']);
            }

            // Plugin hook: After book save (update)
            \App\Support\Hooks::do('book.save.after', [$id, $fields]);

            // Ricalcola disponibilità dopo aver modificato le copie
            $integrity = new \App\Support\DataIntegrity($db);
            $integrity->recalculateBookAvailability($id);

            // Persist all fields first. An explicitly chosen NEW cover (a direct
            // file upload, or a scraped / alternative URL) is then applied AFTER
            // this update, so it replaces the existing cover in place instead of
            // being reverted by it — no "save, remove the old cover, save again"
            // dance to swap an auto-imported cover (#165).
            // Merge normalized $fields over $data so NULL isbn/ean values are preserved
            (new \App\Models\BookRepository($db))->updateOptionals($id, array_merge($data, $fields));
            $this->releaseBookIdentifierLocks($db, $lockKeys);
            $lockKeys = [];
            // The cover persisted just above may be a local file that
            // downloadExternalCover() saved during this submit; capture it so
            // the cleanup below can also remove it when a file upload or a
            // different scraped URL replaces it (#F002).
            $intermediateCover = (string) ($fields['copertina_url'] ?? '');

            $coverFileUploaded = false;
            if (!empty($_FILES['copertina']) && ($_FILES['copertina']['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_NO_FILE) {
                $this->handleCoverUpload($db, $id, $_FILES['copertina']);
                $coverFileUploaded = true;
            }
            // A scraped / alternative cover URL applies only when the user did NOT
            // upload a file in this submit — a direct upload is the explicit choice.
            // A pending cover removal (remove_cover=1) also wins over a stale
            // scraped_cover_url so the cover isn't silently re-added (#F007). Reuse
            // the exact same comparison as the removal branch above (line 1419):
            // match the string '1' explicitly — the hidden field defaults to '0',
            // so a strict === check avoids any truthiness ambiguity.
            $removeCoverRequested = (isset($data['remove_cover']) && $data['remove_cover'] === '1');
            if (!$coverFileUploaded && !$removeCoverRequested && !$scrapedCoverAlreadySaved && !empty($data['scraped_cover_url'])) {
                $this->handleCoverUrl($db, $id, (string) $data['scraped_cover_url']);
            }

            // Replacing a cover writes a new uniquely-named file and repoints the
            // DB, leaving the previous local file orphaned (#166 review). Compare
            // the cover captured before this update against the final one and
            // delete the old local file when it actually changed. The remove_cover
            // branch already unlinks its file, so skip it here.
            if (!$removeCoverRequested) {
                $finalCover = $this->currentCoverUrl($db, $id);
                $this->deleteLocalCoverFile((string) ($currentBook['copertina_url'] ?? ''), $finalCover);
                // Also clean up the intermediate file downloadExternalCover()
                // saved during this submit when a file upload / scraped URL
                // replaced it (#F002); no-ops when intermediate === final.
                $this->deleteLocalCoverFile($intermediateCover, $finalCover);
            }

            // Rebuild the denormalized FULLTEXT search_index now that the book row
            // (updateBasic + updateOptionals) and its re-synced author links are
            // all persisted and readable.
            \App\Support\SearchIndexBuilder::rebuild($db, (int) $id);

            // Set a success message in the session
            \App\Support\ContentCache::booksChanged();

            $updatedSnapshot = \App\Support\ActivityLog::loadBookSnapshot($db, $id);
            \App\Support\ActivityLog::recordBookEvent(
                $db,
                $id,
                'aggiornamento',
                'edit',
                'book.updated',
                \App\Support\ActivityLog::bookSnapshot($currentBook),
                $updatedSnapshot,
                bookTitle: (string) ($updatedSnapshot['titolo'] ?? $currentBook['titolo'] ?? ''),
                source: 'manual'
            );

            if (($_SESSION['error_message'] ?? null) === $errorBeforeUpdate) {
                $_SESSION['success_message'] = __('Libro aggiornato con successo!');
            }

            return $response->withHeader('Location', url('/admin/books/' . $id))->withStatus(302);

        } finally {
            $this->releaseBookIdentifierLocks($db, $lockKeys);
        }
    }

    private function handleCoverUrl(mysqli $db, int $bookId, string $url): void
    {
        if (!$url)
            return;

        // Fix HTML-encoded ampersands from Google Books API URLs
        $url = html_entity_decode($url, ENT_QUOTES | ENT_HTML5, 'UTF-8');

        $this->logCoverDebug('handleCoverUrl.start', ['bookId' => $bookId, 'url' => $url]);

        // Security: Validate URL against whitelist for external downloads
        if (!$this->isUrlAllowed($url)) {
            $this->logCoverDebug('handleCoverUrl.security.blocked', ['bookId' => $bookId, 'url' => $url]);
            return;
        }

        // Case 1: local path already in /uploads/copertine => just persist it (normalize leading slash)
        if (strpos($url, '/uploads/copertine/') === 0 || strpos($url, 'uploads/copertine/') === 0) {
            if (strpos($url, '/') !== 0) {
                $url = '/' . $url;
            }
            $stmt = $db->prepare('UPDATE libri SET copertina_url=?, updated_at=NOW() WHERE id=?');
            $stmt->bind_param('si', $url, $bookId);
            $stmt->execute();
            $this->logCoverDebug('handleCoverUrl.local.persist', ['bookId' => $bookId, 'stored' => $url]);
            return;
        }

        // Case 2: absolute URL
        if (strpos($url, 'http') === 0) {
            // If it points to our own uploads path, just persist the relative path
            $path = parse_url($url, PHP_URL_PATH) ?: '';
            if ($path && (strpos($path, '/uploads/copertine/') === 0 || strpos($path, 'uploads/copertine/') === 0)) {
                if (strpos($path, '/') !== 0) {
                    $path = '/' . $path;
                }
                $stmt = $db->prepare('UPDATE libri SET copertina_url=?, updated_at=NOW() WHERE id=?');
                $stmt->bind_param('si', $path, $bookId);
                $stmt->execute();
                $this->logCoverDebug('handleCoverUrl.absolute.local', ['bookId' => $bookId, 'stored' => $path]);
                return;
            }

            // Otherwise, download and save locally

            // SECURITY FIX #1 (CRITICAL): Prevent SSRF via DNS rebinding
            // Resolve hostname to IP and validate it's not a private/reserved IP
            $host = parse_url($url, PHP_URL_HOST);
            if ($host) {
                $ip = gethostbyname($host);

                // Block private IP ranges (RFC 1918), link-local, loopback, and reserved ranges
                if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE) === false) {
                    $this->logCoverDebug('handleCoverUrl.security.private_ip_blocked', [
                        'bookId' => $bookId,
                        'url' => $url,
                        'host' => $host,
                        'resolved_ip' => $ip
                    ]);
                    return;
                }
            }

            // SECURITY FIX: DoS Prevention - Check Content-Length BEFORE downloading
            // Prevent memory exhaustion attacks by verifying file size before streaming

            // Step 1: HEAD request to check Content-Length
            $ch = curl_init($url);
            curl_setopt_array($ch, [
                CURLOPT_NOBODY => true,  // Only headers, no body
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_FOLLOWLOCATION => false,
                CURLOPT_TIMEOUT => 5,
                CURLOPT_SSL_VERIFYPEER => true,
                CURLOPT_SSL_VERIFYHOST => 2,
                CURLOPT_USERAGENT => 'BibliotecaBot/1.0'
            ]);
            if (defined('CURLOPT_PROTOCOLS_STR')) {
                curl_setopt($ch, CURLOPT_PROTOCOLS_STR, 'https,http');
            } else {
                curl_setopt($ch, CURLOPT_PROTOCOLS, CURLPROTO_HTTPS | CURLPROTO_HTTP);
            }
            curl_exec($ch);
            $contentLength = curl_getinfo($ch, CURLINFO_CONTENT_LENGTH_DOWNLOAD);
            $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            /* curl_close(): no-op since PHP 8.0, deprecated 8.5 */

            // Reject if size is known and exceeds limit (2MB)
            if ($contentLength > 0 && $contentLength > 2 * 1024 * 1024) {
                $this->logCoverDebug('handleCoverUrl.security.size_exceeded_precheck', [
                    'bookId' => $bookId,
                    'url' => $url,
                    'size' => $contentLength
                ]);
                return;
            }

            // Reject if HEAD request failed
            if ($httpCode >= 400) {
                $this->logCoverDebug('handleCoverUrl.download.head_fail', [
                    'bookId' => $bookId,
                    'url' => $url,
                    'httpCode' => $httpCode
                ]);
                return;
            }

            // Step 2: Download with streaming and size limit enforcement
            $ch = curl_init($url);
            curl_setopt_array($ch, [
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_FOLLOWLOCATION => false,  // Prevent redirect bypass
                CURLOPT_TIMEOUT => 10,
                CURLOPT_SSL_VERIFYPEER => true,
                CURLOPT_SSL_VERIFYHOST => 2,
                CURLOPT_USERAGENT => 'BibliotecaBot/1.0',
                CURLOPT_BUFFERSIZE => 128 * 1024,  // 128KB chunks
                CURLOPT_NOPROGRESS => false,
                CURLOPT_PROGRESSFUNCTION => function ($resource, $download_size, $downloaded, $upload_size, $uploaded) {
                    // Abort download if it exceeds 2MB during streaming
                    return ($downloaded > 2 * 1024 * 1024) ? 1 : 0;
                }
            ]);
            if (defined('CURLOPT_PROTOCOLS_STR')) {
                curl_setopt($ch, CURLOPT_PROTOCOLS_STR, 'https,http');
            } else {
                curl_setopt($ch, CURLOPT_PROTOCOLS, CURLPROTO_HTTPS | CURLPROTO_HTTP);
            }
            $img = curl_exec($ch);
            $curlError = curl_errno($ch);
            $curlHttpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            /* curl_close(): no-op since PHP 8.0, deprecated 8.5 */

            // Handle abort from progress function
            if ($curlError === CURLE_ABORTED_BY_CALLBACK) {
                $this->logCoverDebug('handleCoverUrl.security.size_exceeded_during_download', [
                    'bookId' => $bookId,
                    'url' => $url
                ]);
                return;
            }

            // Handle download failure
            if ($img === false || $curlError !== 0 || $curlHttpCode >= 400) {
                $this->logCoverDebug('handleCoverUrl.download.fail', [
                    'bookId' => $bookId,
                    'url' => $url,
                    'curlError' => $curlError,
                    'httpCode' => $curlHttpCode
                ]);
                return;
            }

            // Final size check (defense in depth)
            if (strlen($img) > 2 * 1024 * 1024) {
                $this->logCoverDebug('handleCoverUrl.security.size_exceeded_final', [
                    'bookId' => $bookId,
                    'url' => $url,
                    'size' => strlen($img)
                ]);
                return;
            }

            // Security: Validate MIME type of downloaded content
            if (!$this->isValidImageMimeType($img)) {
                $this->logCoverDebug('handleCoverUrl.security.invalid_mime', ['bookId' => $bookId, 'url' => $url]);
                return;
            }
            $dir = $this->getCoversUploadPath() . '/';
            if (!is_dir($dir)) {
                if (!mkdir($dir, 0755, true)) {
                    $this->logCoverDebug('handleCoverUrl.fail.mkdir', ['bookId' => $bookId, 'dir' => $dir]);
                    return;
                }
            }
            // Determine extension from magic bytes, not URL (defense against URL spoofing)
            $ext = $this->getExtensionFromMagicBytes($img) ?? 'jpg';
            $name = 'libro_' . $bookId . '_' . time() . '.' . $ext;
            $dst = $dir . $name;
            if (file_put_contents($dst, $img) !== false) {
                $cover = $this->getCoversUrlPath() . '/' . $name;
                $stmt = $db->prepare('UPDATE libri SET copertina_url=?, updated_at=NOW() WHERE id=?');
                $stmt->bind_param('si', $cover, $bookId);
                $stmt->execute();
                $this->logCoverDebug('handleCoverUrl.download.ok', ['bookId' => $bookId, 'stored' => $cover]);
            }
        }
    }

    private function handleCoverUpload(mysqli $db, int $bookId, array $file): void
    {
        // Security: Do NOT trust $file['type'] from client - always validate actual content

        // 1. Verify this is an uploaded file
        $tmpPath = $file['tmp_name'] ?? '';
        if (!is_uploaded_file($tmpPath)) {
            $this->logCoverDebug('handleCoverUpload.skip.not_uploaded', ['bookId' => $bookId]);
            return;
        }

        // 2. Check file size
        if (($file['size'] ?? 0) > 2 * 1024 * 1024 || ($file['size'] ?? 0) === 0) {
            $this->logCoverDebug('handleCoverUpload.skip.size', ['bookId' => $bookId, 'size' => $file['size'] ?? 0]);
            return;
        }

        // 3. Read actual file content
        $content = @file_get_contents($tmpPath);
        if ($content === false || $content === '') {
            $this->logCoverDebug('handleCoverUpload.skip.read_fail', ['bookId' => $bookId]);
            return;
        }

        // 4. Validate MIME type using magic bytes (NOT $file['type'])
        if (!$this->isValidImageMimeType($content)) {
            $this->logCoverDebug('handleCoverUpload.skip.invalid_magic', ['bookId' => $bookId]);
            return;
        }

        // 5. Get safe extension based on magic bytes (NOT from user-supplied filename)
        $ext = $this->getExtensionFromMagicBytes($content);
        if ($ext === null) {
            $this->logCoverDebug('handleCoverUpload.skip.unknown_format', ['bookId' => $bookId]);
            return;
        }

        // 6. Save file with safe name
        $dir = $this->getCoversUploadPath() . '/';
        if (!is_dir($dir)) {
            if (!mkdir($dir, 0755, true)) {
                $this->logCoverDebug('handleCoverUpload.fail.mkdir', ['bookId' => $bookId, 'dir' => $dir]);
                return;
            }
        }
        $name = 'libro_' . $bookId . '_' . time() . '.' . $ext;
        $dst = $dir . $name;

        if (file_put_contents($dst, $content) !== false) {
            $url = $this->getCoversUrlPath() . '/' . $name;
            $stmt = $db->prepare('UPDATE libri SET copertina_url=?, updated_at=NOW() WHERE id=?');
            $stmt->bind_param('si', $url, $bookId);
            $stmt->execute();
            $this->logCoverDebug('handleCoverUpload.ok', ['bookId' => $bookId, 'stored' => $url]);
        } else {
            $this->logCoverDebug('handleCoverUpload.fail', ['bookId' => $bookId]);
        }
    }

    /**
     * Read the current local/remote cover URL for a book (empty string if none).
     */
    private function currentCoverUrl(mysqli $db, int $bookId): string
    {
        $stmt = $db->prepare('SELECT copertina_url FROM libri WHERE id=? AND deleted_at IS NULL');
        if (!$stmt) {
            return '';
        }
        $stmt->bind_param('i', $bookId);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return (string) ($row['copertina_url'] ?? '');
    }

    /**
     * Delete a previously-stored LOCAL cover file when it is being replaced.
     * No-op for external URLs, empty values, or when the old path equals the
     * new one. Uses the same realpath-containment guard as the remove-cover
     * branch so it can only ever unlink inside public/uploads/copertine.
     */
    private function deleteLocalCoverFile(string $oldUrl, ?string $newUrl = null): void
    {
        if ($oldUrl === '' || strpos($oldUrl, '/uploads/copertine/') !== 0) {
            return;
        }
        if ($newUrl !== null && $oldUrl === $newUrl) {
            return;
        }
        // The replacement must itself be a DURABLE LOCAL file before the old
        // one is removed: when the final cover is a raw external URL (the
        // download bailed — non-whitelisted domain, SSRF block, network/HTTP
        // error — and the raw URL was persisted) or the local file is missing,
        // keep the old working cover. A harmless orphan on disk beats losing
        // the only working cover to a possibly-dead remote URL (#F006).
        if ($newUrl !== null
            && (strpos($newUrl, '/uploads/copertine/') !== 0
                || !file_exists(__DIR__ . '/../../public' . $newUrl))) {
            return;
        }
        $safePath = realpath(__DIR__ . '/../../public' . $oldUrl);
        $baseDir = realpath($this->getCoversUploadPath());
        if ($safePath && $baseDir && strpos($safePath, $baseDir) === 0 && file_exists($safePath)) {
            // nosemgrep: php.lang.security.unlink-use.unlink-use -- path confined by the realpath() containment check above, not user input
            unlink($safePath);
            $this->logCoverDebug('cover.old.deleted', ['path' => $oldUrl]);
        } else {
            $this->logCoverDebug('cover.old.delete.security_block', [
                'requested' => $oldUrl,
                'resolved' => $safePath,
                'baseDir' => $baseDir,
            ]);
        }
    }

    /**
     * Add a volume relationship (parent work → child volume).
     */
    public function addVolume(Request $request, Response $response, mysqli $db): Response
    {
        $data = json_decode((string) $request->getBody(), true) ?: [];
        $operaId = (int) ($data['opera_id'] ?? 0);
        $volumeId = (int) ($data['volume_id'] ?? 0);
        $numero = (int) ($data['numero_volume'] ?? 1);

        if ($operaId <= 0 || $volumeId <= 0 || $operaId === $volumeId || $numero < 1) {
            $response->getBody()->write(json_encode(['error' => true, 'message' => __('Parametri non validi')], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
        }

        // Verify both books exist and are not soft-deleted
        $bookCheck = $db->prepare("SELECT COUNT(*) AS cnt FROM libri WHERE id IN (?, ?) AND deleted_at IS NULL");
        if (!$bookCheck) {
            $response->getBody()->write(json_encode(['error' => true, 'message' => __('Errore database')], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }
        $bookCheck->bind_param('ii', $operaId, $volumeId);
        $bookCheck->execute();
        $bookRow = $bookCheck->get_result()->fetch_assoc();
        $bookCheck->close();
        if ((int) ($bookRow['cnt'] ?? 0) !== 2) {
            $response->getBody()->write(json_encode(['error' => true, 'message' => __('Libro non valido o eliminato')], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
        }

        // Prevent cycles: walk ancestor chain from opera_id to detect if volume_id is an ancestor
        $ancestor = $operaId;
        $visited = [$operaId => true];
        while ($ancestor > 0) {
            $cycleCheck = $db->prepare("SELECT opera_id FROM volumi WHERE volume_id = ? LIMIT 1");
            if (!$cycleCheck) {
                break;
            }
            $cycleCheck->bind_param('i', $ancestor);
            $cycleCheck->execute();
            $row = $cycleCheck->get_result()->fetch_assoc();
            $cycleCheck->close();
            if (!$row) {
                break;
            }
            $ancestor = (int) $row['opera_id'];
            if ($ancestor === $volumeId) {
                $response->getBody()->write(json_encode(['error' => true, 'message' => __('Relazione ciclica: questo libro è già opera padre del libro selezionato')], JSON_UNESCAPED_UNICODE));
                return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
            }
            if (isset($visited[$ancestor])) {
                break; // Already in a cycle in existing data, stop walking
            }
            $visited[$ancestor] = true;
        }

        $stmt = $db->prepare("INSERT INTO volumi (opera_id, volume_id, numero_volume) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE opera_id = VALUES(opera_id), numero_volume = VALUES(numero_volume)");
        if (!$stmt) {
            $response->getBody()->write(json_encode(['error' => true, 'message' => __('Errore database')], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }
        $stmt->bind_param('iii', $operaId, $volumeId, $numero);
        $success = $stmt->execute();
        $stmt->close();

        if (!$success) {
            \App\Support\SecureLogger::error('addVolume failed', ['db_error' => $db->error]);
            $response->getBody()->write(json_encode(['error' => true, 'message' => __('Errore durante il salvataggio')], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }

        $response->getBody()->write(json_encode(['success' => true], JSON_UNESCAPED_UNICODE));
        return $response->withHeader('Content-Type', 'application/json');
    }

    /**
     * Remove a volume relationship.
     */
    public function removeVolume(Request $request, Response $response, mysqli $db): Response
    {
        $data = json_decode((string) $request->getBody(), true) ?: [];
        $operaId = (int) ($data['opera_id'] ?? 0);
        $volumeId = (int) ($data['volume_id'] ?? 0);

        if ($operaId <= 0 || $volumeId <= 0) {
            $response->getBody()->write(json_encode(['error' => true, 'message' => __('Parametri non validi')], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
        }

        $stmt = $db->prepare("DELETE FROM volumi WHERE opera_id = ? AND volume_id = ?");
        if (!$stmt) {
            \App\Support\SecureLogger::error('removeVolume prepare failed', ['db_error' => $db->error]);
            $response->getBody()->write(json_encode(['error' => true, 'message' => __('Errore database')], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }
        $stmt->bind_param('ii', $operaId, $volumeId);
        $ok = $stmt->execute();
        $stmt->close();

        if (!$ok) {
            \App\Support\SecureLogger::error('removeVolume execute failed', ['db_error' => $db->error, 'opera_id' => $operaId, 'volume_id' => $volumeId]);
            $response->getBody()->write(json_encode(['error' => true, 'message' => __('Errore database')], JSON_UNESCAPED_UNICODE));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }

        $response->getBody()->write(json_encode(['success' => true], JSON_UNESCAPED_UNICODE));
        return $response->withHeader('Content-Type', 'application/json');
    }

    public function delete(Request $request, Response $response, mysqli $db, int $id): Response
    {
        // CSRF validated by CsrfMiddleware

        // Case 10: Prevent deletion if there are active loans/reservations
        // Check prestiti table (approved loans only)
        $stmt = $db->prepare("
            SELECT COUNT(*) as count
            FROM prestiti
            WHERE libro_id = ?
            AND attivo = 1
            AND stato IN ('in_corso', 'da_ritirare', 'prenotato', 'in_ritardo')
        ");
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result ? $result->fetch_assoc() : null;
        $prestitiCount = (int) ($row['count'] ?? 0);
        $stmt->close();

        // Also check prenotazioni table (waitlist entries with stato='attiva')
        $stmt = $db->prepare("
            SELECT COUNT(*) as count
            FROM prenotazioni
            WHERE libro_id = ?
            AND stato = 'attiva'
        ");
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result ? $result->fetch_assoc() : null;
        $prenotazioniCount = (int) ($row['count'] ?? 0);
        $stmt->close();

        if ($prestitiCount > 0 || $prenotazioniCount > 0) {
            $_SESSION['error_message'] = __('Impossibile eliminare il libro: ci sono prestiti o prenotazioni attive. Termina prima i prestiti/prenotazioni.');
            return $response->withHeader('Location', url('/admin/books/' . $id))->withStatus(302);
        }

        $repo = new \App\Models\BookRepository($db);
        $deletedSnapshot = \App\Support\ActivityLog::loadBookSnapshot($db, $id);
        $deleted = $repo->delete($id);
        if (!$deleted) {
            $_SESSION['error_message'] = __('Errore durante l\'eliminazione del libro. Riprova.');
            return $response->withHeader('Location', url('/admin/books/' . $id))->withStatus(302);
        }
        \App\Support\ContentCache::booksChanged();
        \App\Support\ActivityLog::recordBookEvent(
            $db,
            $id,
            'cancellazione',
            'edit',
            'book.deleted',
            $deletedSnapshot,
            [],
            bookTitle: (string) ($deletedSnapshot['titolo'] ?? ''),
            source: 'manual'
        );
        return $response->withHeader('Location', url('/admin/books'))->withStatus(302);
    }

    /**
     * Security: Validate URL against whitelist for external image downloads
     * Uses centralized COVER_ALLOWED_DOMAINS constant
     */
    private function isUrlAllowed(string $url): bool
    {
        // Allow local paths
        if (strpos($url, '/uploads/') === 0 || strpos($url, 'uploads/') === 0) {
            return true;
        }

        // Only allow external downloads if URL starts with http/https
        if (strpos($url, 'http') !== 0) {
            return false;
        }

        $host = parse_url($url, PHP_URL_HOST);
        if (!$host) {
            return false;
        }

        // Block localhost, private IPs, and internal networks
        if (
            \in_array($host, ['localhost', '127.0.0.1', '::1']) ||
            preg_match('/^(10\.|172\.(1[6-9]|2[0-9]|3[01])\.|192\.168\.)/', $host)
        ) {
            return false;
        }

        return self::coverHostAllowed(strtolower($host));
    }

    /**
     * Security: Validate MIME type of downloaded image content
     */
    private function isValidImageMimeType(string $content): bool
    {
        if (strlen($content) < 12) {
            return false;
        }

        // SECURITY FIX #3 (CRITICAL): Block SVG files (can contain JavaScript)
        // SVG files can start with <?xml, <svg, or <!DOCTYPE
        $contentStart = substr($content, 0, 100);
        if (
            substr($contentStart, 0, 5) === '<?xml' ||
            stripos($contentStart, '<svg') !== false ||
            stripos($contentStart, '<!DOCTYPE svg') !== false
        ) {
            return false;
        }

        // Check magic bytes for common image formats
        $magicBytes = substr($content, 0, 12);

        // JPEG: FF D8 FF
        if (substr($magicBytes, 0, 3) === "\xFF\xD8\xFF") {
            return true;
        }

        // PNG: 89 50 4E 47 0D 0A 1A 0A
        if (substr($magicBytes, 0, 8) === "\x89PNG\x0D\x0A\x1A\x0A") {
            return true;
        }

        // GIF: 47 49 46 38 (GIF8)
        if (substr($magicBytes, 0, 4) === "GIF8") {
            return true;
        }

        // WebP: 52 49 46 46 + 57 45 42 50 (RIFF + WEBP)
        if (substr($magicBytes, 0, 4) === "RIFF" && substr($magicBytes, 8, 4) === "WEBP") {
            return true;
        }

        // AVIF: ftypavif or ftypavis (offset 4-12)
        if (strpos(substr($content, 4, 8), 'ftyp') === 0) {
            $brand = substr($content, 8, 4);
            if ($brand === 'avif' || $brand === 'avis') {
                return true;
            }
        }

        // BMP: 42 4D (BM in ASCII)
        if (substr($magicBytes, 0, 2) === "\x42\x4D") {
            return true;
        }

        return false;
    }

    /**
     * Get file extension from magic bytes (binary signature)
     * Security: Determines actual file type, not based on user-supplied name
     */
    private function getExtensionFromMagicBytes(string $content): ?string
    {
        if (strlen($content) < 4) {
            return null;
        }

        $magic = substr($content, 0, 12);

        // JPEG: FF D8 FF
        if (substr($magic, 0, 3) === "\xFF\xD8\xFF") {
            return 'jpg';
        }

        // PNG: 89 50 4E 47 0D 0A 1A 0A
        if (strlen($magic) >= 8 && substr($magic, 0, 8) === "\x89PNG\x0D\x0A\x1A\x0A") {
            return 'png';
        }

        // GIF: GIF8
        if (substr($magic, 0, 4) === "GIF8") {
            return 'gif';
        }

        // WebP: RIFF + WEBP
        if (strlen($magic) >= 12 && substr($magic, 0, 4) === "RIFF" && substr($magic, 8, 4) === "WEBP") {
            return 'webp';
        }

        // AVIF: ftypavif or ftypavis (offset 4-12)
        if (strlen($content) >= 12 && strpos(substr($content, 4, 8), 'ftyp') === 0) {
            $brand = substr($content, 8, 4);
            if ($brand === 'avif' || $brand === 'avis') {
                return 'avif';
            }
        }

        // BMP: 42 4D (BM in ASCII)
        if (substr($magic, 0, 2) === "\x42\x4D") {
            return 'bmp';
        }

        return null;
    }

    /**
     * Fetch cover for a book via scraping (if missing)
     */
    public function fetchCover(Request $request, Response $response, mysqli $db, int $id): Response
    {
        // Increase execution time for bulk operations
        set_time_limit(120);

        $repo = new \App\Models\BookRepository($db);
        $libro = $repo->getById($id);

        if (!$libro) {
            $response->getBody()->write(json_encode(['success' => false, 'error' => __('Libro non trovato')]));
            return $response->withHeader('Content-Type', 'application/json')->withStatus(404);
        }

        // Check if already has a LOCAL cover (not placeholder, not remote URL)
        // AND the file actually exists on disk. The DB column and the filesystem
        // can drift (manual delete, failed partial restore, broken download): when
        // the column points to a path that's gone, fall through to re-fetch
        // instead of misreporting "already_has_cover" and leaving the book
        // permanently uncovered.
        //
        // Path-traversal hardening (CodeRabbit suggestion): two layers of defence.
        // 1) basename() strips any directory components — `../../etc/passwd`
        //    collapses to `passwd`, so the candidate path is always inside the
        //    covers directory by construction.
        // 2) realpath() containment check resolves symlinks and confirms the
        //    final path still lives under $baseDir. If a malicious symlink
        //    inside covers/ pointed elsewhere, realpath() would expose it and
        //    str_starts_with() would catch it. Mirrors the pattern already
        //    used in the delete path (L1384-1390) so future contributors find
        //    a single consistent shape across read and write file ops.
        if (!empty($libro['copertina_url'])
            && strpos($libro['copertina_url'], 'placeholder.jpg') === false
            && !preg_match('#^https?://#i', $libro['copertina_url'])
        ) {
            $baseDir = realpath($this->getCoversUploadPath());
            $candidate = null;
            if ($baseDir !== false) {
                $candidate = $baseDir . DIRECTORY_SEPARATOR . basename($libro['copertina_url']);
                $resolved  = realpath($candidate);
                if ($resolved !== false
                    && str_starts_with($resolved, $baseDir . DIRECTORY_SEPARATOR)
                    && is_file($resolved)
                ) {
                    $response->getBody()->write(json_encode(['success' => true, 'fetched' => false, 'reason' => 'already_has_cover']));
                    return $response->withHeader('Content-Type', 'application/json');
                }
            }
            // File missing or outside covers dir → log and continue to re-download
            \App\Support\SecureLogger::warning('[LibriController] fetchCover: cover_url in DB but file missing/unreachable on disk, re-fetching', [
                'book_id' => $id,
                'db_url'  => $libro['copertina_url'],
                'expected_path' => $candidate,
            ]);
        }

        // Get ISBN for scraping
        $isbn = $libro['isbn13'] ?? $libro['isbn10'] ?? $libro['ean'] ?? '';
        if (empty($isbn)) {
            $response->getBody()->write(json_encode(['success' => true, 'fetched' => false, 'reason' => 'no_isbn']));
            return $response->withHeader('Content-Type', 'application/json');
        }

        // Call scraping directly via ScrapeController (avoid auth issues with HTTP call)
        $scrapeController = new \App\Controllers\ScrapeController();

        // Create a mock request with ISBN parameter
        $mockRequest = $request->withQueryParams(['isbn' => $isbn]);

        // Create a new response object for the scraping call
        $scrapeResponse = new \Slim\Psr7\Response();

        // Call byIsbn directly
        $scrapeResponse = $scrapeController->byIsbn($mockRequest, $scrapeResponse);

        // Extract and parse the response
        $httpCode = $scrapeResponse->getStatusCode();
        $scrapeResult = (string) $scrapeResponse->getBody();

        // Handle different response codes
        if ($httpCode === 400) {
            // Invalid ISBN - skip this book (not an error, just no valid ISBN)
            $response->getBody()->write(json_encode(['success' => true, 'fetched' => false, 'reason' => 'invalid_isbn']));
            return $response->withHeader('Content-Type', 'application/json');
        }

        if ($httpCode === 404) {
            // Book not found in any scraping source - not an error
            $response->getBody()->write(json_encode(['success' => true, 'fetched' => false, 'reason' => 'no_cover_found']));
            return $response->withHeader('Content-Type', 'application/json');
        }

        if ($httpCode !== 200 || !$scrapeResult) {
            // Real error from scraper (500, 503, etc.)
            $response->getBody()->write(json_encode(['success' => false, 'error' => __('Scraping fallito')]));
            return $response->withHeader('Content-Type', 'application/json')->withStatus(502);
        }

        $scrapeData = json_decode($scrapeResult, true);
        if (!$scrapeData || empty($scrapeData['image'])) {
            $response->getBody()->write(json_encode(['success' => true, 'fetched' => false, 'reason' => 'no_cover_found']));
            return $response->withHeader('Content-Type', 'application/json');
        }

        // Download and save the cover image using CoverController (SSRF protection, domain whitelist)
        $imageUrl = $scrapeData['image'];

        try {
            $coverController = new \App\Controllers\CoverController();
            $coverData = $coverController->downloadFromUrl($imageUrl);

            if (empty($coverData['file_url'])) {
                $response->getBody()->write(json_encode(['success' => false, 'error' => __('Download copertina fallito')]));
                return $response->withHeader('Content-Type', 'application/json')->withStatus(502);
            }

            $coverUrl = $coverData['file_url'];
        } catch (\Throwable $e) {
            \App\Support\SecureLogger::warning('[LibriController] fetchCover download failed: ' . $e->getMessage(), [
                'book_id' => $id,
                'image_url' => $imageUrl,
            ]);
            $response->getBody()->write(json_encode(['success' => false, 'error' => __('Download copertina fallito')]));
            return $response->withHeader('Content-Type', 'application/json')->withStatus(502);
        }

        // Update book record with local cover URL
        $stmt = $db->prepare("UPDATE libri SET copertina_url = ? WHERE id = ?");
        $stmt->bind_param('si', $coverUrl, $id);
        $stmt->execute();
        $stmt->close();

        // The cover URL is part of the cached book-detail DTO and catalog rows
        // (#387): invalidate so the new cover shows without waiting for the TTL.
        // Deferred so a bulk cover fetch that hits this endpoint repeatedly
        // collapses to a single generation bump + edge purge at shutdown
        // instead of one per book.
        \App\Support\ContentCache::deferBooksChanged();

        $response->getBody()->write(json_encode(['success' => true, 'fetched' => true, 'cover_url' => $coverUrl]));
        return $response->withHeader('Content-Type', 'application/json');
    }

    public function generateLabelPDF(Request $request, Response $response, mysqli $db, int $id): Response
    {
        $repo = new \App\Models\BookRepository($db);
        $libro = $repo->getById($id);

        if (!$libro) {
            throw new HttpNotFoundException($request);
        }

        // Get application name and label settings from settings
        $settings = $this->resolveLabelSettings($db);
        $appName = $settings['appName'];
        $labelWidth = $settings['labelWidth'];
        $labelHeight = $settings['labelHeight'];
        $orientation = $settings['orientation'];
        $libro = $this->applyLabelContentSettings($libro, $settings);
        if (!$settings['showAppName']) {
            $appName = '';
        }

        // Get collocazione data
        $collocazione = $this->resolveCollocazione($db, $libro);

        // Create PDF with TCPDF using configured dimensions
        $pdf = new \TCPDF($orientation, 'mm', [$labelWidth, $labelHeight], true, 'UTF-8', false);

        // Document settings
        $pdf->SetCreator($appName);
        $pdf->SetAuthor($appName);
        $pdf->SetTitle('Etichetta - ' . $libro['titolo']);

        // Remove default header/footer
        $pdf->setPrintHeader(false);
        $pdf->setPrintFooter(false);

        // Calculate scaled dimensions based on label size
        $isPortrait = $labelHeight > $labelWidth;

        // Set dynamic margins based on label size (proportional to width)
        $margin = max(1, min(3, $labelWidth * 0.05));
        $pdf->SetMargins($margin, $margin, $margin);
        $pdf->SetAutoPageBreak(false, 0);

        // Add page
        $pdf->AddPage();

        // Calculate available space
        // Fold the configurable padding (#238) into the inset so the proportional
        // renderer lays content out inside the reduced, padded area.
        $padding = (float) $settings['padding'];
        $margin += $padding;
        $availableWidth = max(1.0, $labelWidth - ($margin * 2));
        $availableHeight = max(1.0, $labelHeight - ($margin * 2));

        // Handle autori
        $autoriStr = $this->buildAuthorString($libro);

        // Barcode data
        $barcodePayload = $this->prepareBarcodePayload($libro, $libro['isbn13'] ?? $libro['ean'] ?? $libro['isbn10'] ?? '');

        // Position text
        $positionText = '';
        if (!empty($libro['scaffale_codice']) && !empty($libro['mensola_livello']) && !empty($libro['posizione_progressiva'])) {
            $positionText = $libro['scaffale_codice'] . '.' . $libro['mensola_livello'] . '.' . $libro['posizione_progressiva'];
        } elseif (isset($libro['posizione_progressiva']) && $libro['posizione_progressiva'] > 0) {
            $positionText = 'Pos. ' . $libro['posizione_progressiva'];
        }

        // One proportional renderer for both orientations: content scales to fill
        // the label area instead of sitting fixed-size amid growing margins (#238).
        $this->renderScaledLabel($pdf, $appName, $libro, $autoriStr, $collocazione, $barcodePayload, $positionText, $availableWidth, $availableHeight, $margin);

        // Output PDF
        $pdfContent = $pdf->Output('', 'S');

        $response->getBody()->write($pdfContent);
        return $response
            ->withHeader('Content-Type', 'application/pdf')
            ->withHeader('Content-Disposition', 'inline; filename="etichetta_' . $id . '.pdf"');
    }

    /**
     * Generate a multi-label PDF with ONE label per physical copy of a book.
     * Each label carries that copy's numero_inventario as human-readable text
     * AND as a scannable Code128 barcode (reuses the same label machinery as the
     * book-level label, only the barcode payload/text is the per-copy code).
     */
    public function generateCopyLabelsPDF(Request $request, Response $response, mysqli $db, int $id): Response
    {
        $repo = new \App\Models\BookRepository($db);
        $libro = $repo->getById($id);

        if (!$libro) {
            throw new HttpNotFoundException($request);
        }

        // Load all copies of this book. `copie` has no deleted_at (it CASCADEs on
        // libri delete), so the soft-delete guard lives on the joined `libri` row.
        $copie = [];
        $copyId = filter_var(($request->getQueryParams()['copy_id'] ?? null), FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]);
        $copyFilter = $copyId !== false ? ' AND c.id = ?' : '';
        $copiesStmt = $db->prepare("
            SELECT c.*
            FROM copie c
            JOIN libri l ON l.id = c.libro_id
            WHERE c.libro_id = ? AND l.deleted_at IS NULL{$copyFilter}
            ORDER BY c.numero_inventario ASC
        ");
        if ($copyFilter !== '') {
            $copyIdInt = (int) $copyId;
            $copiesStmt->bind_param('ii', $id, $copyIdInt);
        } else {
            $copiesStmt->bind_param('i', $id);
        }
        $copiesStmt->execute();
        $copiesResult = $copiesStmt->get_result();
        while ($copyRow = $copiesResult->fetch_assoc()) {
            $copie[] = $copyRow;
        }
        $copiesStmt->close();
        $copie = \App\Models\CopyRepository::sortByInventoryNumber($copie);

        if (count($copie) === 0) {
            $_SESSION['error_message'] = __('Nessuna copia disponibile per la stampa delle etichette.');
            return $response->withHeader('Location', url('/admin/books/' . $id))->withStatus(302);
        }

        // Get application name and label settings from settings
        $settings = $this->resolveLabelSettings($db);
        $appName = $settings['appName'];
        $labelWidth = $settings['labelWidth'];
        $labelHeight = $settings['labelHeight'];
        $orientation = $settings['orientation'];

        $libro = $this->applyLabelContentSettings($libro, $settings);
        if (!$settings['showAppName']) {
            $appName = '';
        }

        // Collocazione is the same for every copy of the book
        $collocazione = $this->resolveCollocazione($db, $libro);

        // Author string (same for every copy)
        $autoriStr = $this->buildAuthorString($libro);

        // Position text fallback (used when no full collocazione is available)
        $positionText = '';
        if (!empty($libro['scaffale_codice']) && !empty($libro['mensola_livello']) && !empty($libro['posizione_progressiva'])) {
            $positionText = $libro['scaffale_codice'] . '.' . $libro['mensola_livello'] . '.' . $libro['posizione_progressiva'];
        } elseif (isset($libro['posizione_progressiva']) && $libro['posizione_progressiva'] > 0) {
            $positionText = 'Pos. ' . $libro['posizione_progressiva'];
        }

        $pdf = new \TCPDF($orientation, 'mm', [$labelWidth, $labelHeight], true, 'UTF-8', false);
        $pdf->SetCreator($appName);
        $pdf->SetAuthor($appName);
        $pdf->SetTitle('Etichette copie - ' . $libro['titolo']);
        $pdf->setPrintHeader(false);
        $pdf->setPrintFooter(false);

        $isPortrait = $labelHeight > $labelWidth;
        $margin = max(1, min(3, $labelWidth * 0.05));
        $pdf->SetMargins($margin, $margin, $margin);
        $pdf->SetAutoPageBreak(false, 0);

        // Fold the configurable padding (#238) into the inset so the proportional
        // renderer lays content out inside the reduced, padded area.
        $padding = (float) $settings['padding'];
        $margin += $padding;
        $availableWidth = max(1.0, $labelWidth - ($margin * 2));
        $availableHeight = max(1.0, $labelHeight - ($margin * 2));

        foreach ($copie as $copia) {
            $numeroInventario = (string) ($copia['numero_inventario'] ?? '');

            // Per-copy barcode: the inventory code as a scannable Code128 symbol,
            // with the same code repeated as human-readable text under the bars.
            $barcodePayload = ['value' => $numeroInventario, 'type' => 'C128'];

            $pdf->AddPage();
            $this->renderScaledLabel($pdf, $appName, $libro, $autoriStr, $collocazione, $barcodePayload, $positionText, $availableWidth, $availableHeight, $margin, $numeroInventario);
        }

        $pdfContent = $pdf->Output('', 'S');
        $response->getBody()->write($pdfContent);
        return $response
            ->withHeader('Content-Type', 'application/pdf')
            ->withHeader('Content-Disposition', 'inline; filename="etichette_copie_' . $id . ($copyFilter !== '' ? '_' . (int) $copyId : '') . '.pdf"');
    }

    /**
     * Load the label configuration shared by the book- and copy-level label PDFs:
     * application name plus the clamped label width/height (mm) and derived
     * orientation. Single source of truth so both label routes read settings identically.
     *
     * @return array{appName: string, labelWidth: int, labelHeight: int, padding: float, orientation: string, showAppName: bool, showTitle: bool, showSubtitle: bool, showAuthorPublisher: bool, showDewey: bool}
     */
    private function resolveLabelSettings(mysqli $db): array
    {
        $settingsRepo = new \App\Models\SettingsRepository($db);
        $appName = $settingsRepo->get('app', 'name', 'Biblioteca');

        // Get label dimensions from settings (default to 25x38mm)
        $labelWidth = (int) ($settingsRepo->get('label', 'width', (string) \App\Support\ConfigStore::get('label.width', 25)));
        $labelHeight = (int) ($settingsRepo->get('label', 'height', (string) \App\Support\ConfigStore::get('label.height', 38)));

        // Ensure dimensions are within reasonable bounds
        if ($labelWidth < 10 || $labelWidth > 100)
            $labelWidth = 25;
        if ($labelHeight < 10 || $labelHeight > 100)
            $labelHeight = 38;

        // Configurable extra padding (mm) applied on every side, on top of the
        // automatic margin — lets staff fine-tune the printable inset per label
        // type (#238). Clamped so it can never swallow the whole label.
        $padding = (float) ($settingsRepo->get('label', 'padding', '0'));
        $maxPad = min($labelWidth, $labelHeight) / 3;
        if ($padding < 0) {
            $padding = 0.0;
        } elseif ($padding > $maxPad) {
            $padding = $maxPad;
        }

        // Determine orientation based on dimensions
        return [
            'appName' => $appName,
            'labelWidth' => $labelWidth,
            'labelHeight' => $labelHeight,
            'padding' => $padding,
            'orientation' => $labelWidth > $labelHeight ? 'L' : 'P',
            'showAppName' => $settingsRepo->get('label', 'show_app_name', '1') === '1',
            'showTitle' => $settingsRepo->get('label', 'show_title', '1') === '1',
            'showSubtitle' => $settingsRepo->get('label', 'show_subtitle', '1') === '1',
            'showAuthorPublisher' => $settingsRepo->get('label', 'show_author_publisher', '1') === '1',
            'showDewey' => $settingsRepo->get('label', 'show_dewey', '1') === '1',
        ];
    }

    /** Apply optional label fields without changing the shared renderers. */
    private function applyLabelContentSettings(array $libro, array $settings): array
    {
        $titleParts = [];
        if ($settings['showTitle'] && !empty($libro['titolo'])) {
            $titleParts[] = (string) $libro['titolo'];
        }
        if ($settings['showSubtitle'] && !empty($libro['sottotitolo'])) {
            $titleParts[] = (string) $libro['sottotitolo'];
        }
        $libro['titolo'] = implode(' — ', $titleParts);
        if (!$settings['showAuthorPublisher']) {
            $libro['autori'] = [];
            $libro['editore_nome'] = '';
        } else {
            $libro['label_include_publisher'] = true;
        }
        if (!$settings['showDewey']) {
            $libro['classificazione_dewey'] = '';
        }
        return $libro;
    }

    /**
     * Build the "scaffale.livello.posizione" collocazione string for a book, or ''
     * when the book is not fully shelved. Shared by book- and copy-level labels
     * (collocazione is identical for every copy of a book).
     */
    private function resolveCollocazione(mysqli $db, array $libro): string
    {
        $collocazione = '';
        if (!empty($libro['scaffale_id']) && !empty($libro['mensola_id']) && !empty($libro['posizione_progressiva'])) {
            $stmt = $db->prepare("SELECT s.codice as scaffale_codice, m.numero_livello
                                  FROM scaffali s, mensole m
                                  WHERE s.id = ? AND m.id = ?");
            $scaffaleId = (int) $libro['scaffale_id'];
            $mensolaId = (int) $libro['mensola_id'];
            $stmt->bind_param('ii', $scaffaleId, $mensolaId);
            $stmt->execute();
            $result = $stmt->get_result();
            if ($row = $result->fetch_assoc()) {
                $collocazione = $row['scaffale_codice'] . '.' . $row['numero_livello'] . '.' . $libro['posizione_progressiva'];
            }
            $stmt->close();
        }
        return $collocazione;
    }

    /**
     * Flatten a book's authors into a comma-separated display string. Accepts either
     * the array-of-authors shape (each with a 'nome') or a pre-joined string.
     * Shared by book- and copy-level labels (author string is identical per copy).
     */
    private function buildAuthorString(array $libro): string
    {
        $autoriStr = '';
        if (!empty($libro['autori'])) {
            if (is_array($libro['autori'])) {
                $autoriStr = implode(', ', array_map(function ($a) {
                    return $a['nome'] ?? '';
                }, $libro['autori']));
            } else {
                $autoriStr = (string) $libro['autori'];
            }
        }
        if (!empty($libro['label_include_publisher']) && !empty($libro['editore_nome'])) {
            $publisher = (string) $libro['editore_nome'];
            $autoriStr = $autoriStr !== '' ? $autoriStr . ' — ' . $publisher : $publisher;
        }
        return $autoriStr;
    }

    /**
     * Proportional label renderer (#238): lays the active content out as a stack
     * of weighted blocks that together fill the whole label area, so text and the
     * barcode scale with the label's real dimensions instead of staying a fixed
     * size surrounded by growing margins. Works for both portrait and landscape.
     *
     * Each text block auto-fits: its font starts from the height allocated to the
     * block, then shrinks until the text fits the width (never overflows). The
     * barcode fills the width and its allocated height. Blocks are centered.
     */
    private function renderScaledLabel($pdf, string $appName, array $libro, string $autoriStr, string $collocazione, array $barcode, string $positionText, float $availableWidth, float $availableHeight, float $margin, ?string $barcodeTextOverride = null): void
    {
        if ($availableWidth <= 0 || $availableHeight <= 0) {
            return;
        }
        $family = 'dejavusans';

        // Build the ordered list of active blocks with a relative weight (how much
        // of the height each should claim). The barcode dominates.
        $blocks = [];

        $appName = trim($appName);
        if ($appName !== '') {
            // Secondary info → lighter weight so the title stays the prominent line.
            $blocks[] = ['type' => 'text', 'text' => $appName, 'style' => 'B', 'weight' => 0.8];
        }

        $titolo = trim((string) ($libro['titolo'] ?? ''));
        if ($titolo !== '') {
            $blocks[] = ['type' => 'text', 'text' => $titolo, 'style' => 'B', 'weight' => 1.5];
        }

        $info = trim($autoriStr);
        if ($info !== '') {
            $blocks[] = ['type' => 'text', 'text' => $info, 'style' => '', 'weight' => 1.0];
        }

        $hasBarcode = !empty($barcode['value']);
        if ($hasBarcode) {
            $blocks[] = ['type' => 'barcode', 'weight' => 3.0];
            $eanText = (string) ($barcodeTextOverride ?? ($libro['ean'] ?? $libro['isbn13'] ?? $libro['isbn10'] ?? ''));
            $eanText = trim($eanText);
            if ($eanText !== '') {
                $blocks[] = ['type' => 'text', 'text' => $eanText, 'style' => '', 'weight' => 0.7];
            }
        }

        $dewey = trim((string) ($libro['classificazione_dewey'] ?? ''));
        if ($dewey !== '') {
            $blocks[] = ['type' => 'text', 'text' => 'Dewey: ' . $dewey, 'style' => 'I', 'weight' => 0.7];
        }

        $posColText = trim((string) ($collocazione !== '' ? $collocazione : $positionText));
        if ($posColText !== '') {
            $blocks[] = ['type' => 'text', 'text' => $posColText, 'style' => 'B', 'weight' => 1.0];
        }

        $n = count($blocks);
        if ($n === 0) {
            return;
        }

        // Inter-block gap proportional to height; distribute the rest by weight so
        // the stack always fills exactly the available height (→ true centering).
        $gap = $availableHeight * 0.03;
        $gapsTotal = $gap * ($n - 1);
        $usableHeight = max(0.1, $availableHeight - $gapsTotal);
        $totalWeight = 0.0;
        foreach ($blocks as $b) {
            $totalWeight += $b['weight'];
        }

        $x = $margin;
        $y = $margin;
        foreach ($blocks as $i => $b) {
            $blockHeight = $usableHeight * ($b['weight'] / $totalWeight);

            if ($b['type'] === 'barcode') {
                // Fill the width; the allocated height scales with the label.
                $barcodeWidth = $availableWidth;
                $barcodeX = $margin;
                // stretch (no fitwidth) so the bars actually fill the given width —
                // fitwidth would cap them at the symbol's natural width on wide labels.
                $pdf->write1DBarcode(
                    $barcode['value'],
                    $barcode['type'],
                    $barcodeX,
                    $y,
                    $barcodeWidth,
                    $blockHeight,
                    0.4,
                    ['stretch' => true],
                    'N'
                );
            } else {
                // Auto-fit font: start from the block height (mm→pt via /0.3528,
                // ×0.68 for cap height + a little breathing room).
                $fontPt = ($blockHeight / 0.3528) * 0.68;
                $fontPt = max(3.0, min(60.0, $fontPt));
                $pdf->SetFont($family, $b['style'], $fontPt);
                // Wrap instead of clip: a long line (e.g. title + subtitle on a
                // narrow label) flows onto extra lines, and the font shrinks only
                // until the wrapped text fits the block's height — so text never
                // overflows the label and never gets cut off horizontally.
                while ($fontPt > 3.0 && $pdf->getStringHeight($availableWidth, $b['text']) > $blockHeight) {
                    $fontPt -= 0.5;
                    $pdf->SetFont($family, $b['style'], $fontPt);
                }
                // Vertically + horizontally centered within the block; MultiCell
                // wraps on word boundaries and 'M' valign centers the wrapped run.
                $pdf->SetXY($x, $y);
                $pdf->MultiCell($availableWidth, $blockHeight, $b['text'], 0, 'C', false, 0, $x, $y, true, 0, false, true, $blockHeight, 'M');
            }

            $y += $blockHeight;
            if ($i < $n - 1) {
                $y += $gap;
            }
        }
    }

    private function prepareBarcodePayload(array $libro, string $rawValue): array
    {
        $normalized = strtoupper(trim($rawValue));
        $digits = preg_replace('/[^0-9]/', '', $normalized);

        if ($digits && preg_match('/^\d{13}$/', $digits)) {
            return ['value' => $digits, 'type' => 'EAN13'];
        }

        if ($digits && preg_match('/^\d{12}$/', $digits)) {
            return ['value' => $digits . $this->calculateEan13CheckDigit($digits), 'type' => 'EAN13'];
        }

        // Convert ISBN-10 (with possible X) to EAN-13 by prefixing 978 and recomputing the check digit
        if ($normalized && preg_match('/^\d{9}[\dX]$/i', $normalized)) {
            $isbnDigits = substr($normalized, 0, 9); // drop ISBN-10 check digit
            $base = '978' . $isbnDigits;
            return ['value' => $base . $this->calculateEan13CheckDigit($base), 'type' => 'EAN13'];
        }

        if ($digits && preg_match('/^\d{8}$/', $digits)) {
            return ['value' => $digits, 'type' => 'EAN8'];
        }

        $fallback = 'LIB-' . str_pad((string) ($libro['id'] ?? 0), 6, '0', STR_PAD_LEFT);
        return ['value' => $fallback, 'type' => 'C128'];
    }

    private function calculateEan13CheckDigit(string $digits): int
    {
        $sum = 0;
        $length = strlen($digits);
        for ($i = 0; $i < $length; $i++) {
            $num = (int) $digits[$i];
            $sum += ($i % 2 === 0) ? $num : $num * 3;
        }
        $mod = $sum % 10;
        return $mod === 0 ? 0 : 10 - $mod;
    }

    /**
     * Export libri to CSV in import-compatible format
     * Supports multiple formats and delimiters
     */
    public function exportCsv(Request $request, Response $response, mysqli $db): Response
    {
        // Get filters and options from query parameters
        $params = $request->getQueryParams();
        $search = $params['search'] ?? '';
        $stato = $params['stato'] ?? '';
        $editoreId = isset($params['editore_id']) && is_numeric($params['editore_id']) ? (int) $params['editore_id'] : 0;
        $genereId = isset($params['genere_id']) && is_numeric($params['genere_id']) ? (int) $params['genere_id'] : 0;
        $autoreId = isset($params['autore_id']) && is_numeric($params['autore_id']) ? (int) $params['autore_id'] : 0;

        // Selected IDs filter (from "Export selected" button)
        $idsParam = $params['ids'] ?? '';
        $idsRequested = array_key_exists('ids', $params);
        $selectedIds = [];

        if (is_array($idsParam)) {
            $flat = [];
            array_walk_recursive($idsParam, static function ($value) use (&$flat): void {
                if (is_scalar($value)) {
                    $flat[] = (string) $value;
                }
            });
            $idsParam = implode(',', $flat);
        } elseif (!is_string($idsParam)) {
            $idsParam = '';
        }

        if ($idsParam !== '') {
            $selectedIds = array_values(array_unique(array_filter(
                array_map('intval', explode(',', $idsParam)),
                static fn (int $id): bool => $id > 0
            )));
            // The selected-export path caps at 1000 IDs. Rather than silently
            // shipping a partial CSV that looks complete, refuse the export and
            // tell the user to narrow the selection (the UI accumulates IDs
            // across pages, so this is a reachable state).
            if (count($selectedIds) > 1000) {
                SecureLogger::error(__('Export selezione troppo grande'), [
                    'requested' => count($selectedIds),
                ]);
                $_SESSION['error_message'] = sprintf(
                    __('Selezione troppo grande per l\'esportazione: %d libri (massimo 1000). Riduci la selezione e riprova.'),
                    count($selectedIds)
                );
                return $response->withHeader('Location', url('/admin/books'))->withStatus(302);
            }
        }

        // Export format options
        $format = $params['format'] ?? 'standard'; // standard, librarything
        $delimiter = $params['delimiter'] ?? ';'; // ;, comma, tab

        // Normalize delimiter parameter
        if ($delimiter === 'comma') {
            $delimiter = ',';
        } elseif ($delimiter === 'tab') {
            $delimiter = "\t";
        }

        // Build WHERE clause based on filters
        $whereClauses = [];
        $bindTypes = '';
        $bindValues = [];

        // Global search filter
        if (!empty($search)) {
            $whereClauses[] = "(l.titolo LIKE ? OR l.sottotitolo LIKE ? OR l.descrizione LIKE ? OR l.isbn13 LIKE ? OR l.isbn10 LIKE ? OR a.nome LIKE ? OR e.nome LIKE ?)";
            $searchParam = "%{$search}%";
            for ($i = 0; $i < 7; $i++) {
                $bindTypes .= 's';
                $bindValues[] = $searchParam;
            }
        }

        // Status filter
        if (!empty($stato)) {
            $whereClauses[] = "l.stato = ?";
            $bindTypes .= 's';
            $bindValues[] = $stato;
        }

        // Whether the multi-publisher junction exists (pre-migration safety):
        // gates both the editore filter and the editori_nomi SELECT column.
        $hasJunction = \App\Support\SchemaInfo::hasLibriEditori($db);

        // Editore filter — match primary (libri.editore_id) or any secondary
        // publisher in the multi-publisher junction (libri_editori, issue #143).
        if ($editoreId > 0) {
            if ($hasJunction) {
                $whereClauses[] = "(l.editore_id = ? OR EXISTS (SELECT 1 FROM libri_editori le WHERE le.libro_id = l.id AND le.editore_id = ?))";
                $bindTypes .= 'ii';
                $bindValues[] = $editoreId;
                $bindValues[] = $editoreId;
            } else {
                $whereClauses[] = "l.editore_id = ?";
                $bindTypes .= 'i';
                $bindValues[] = $editoreId;
            }
        }

        // Genere filter
        if ($genereId > 0) {
            $whereClauses[] = "l.genere_id = ?";
            $bindTypes .= 'i';
            $bindValues[] = $genereId;
        }

        // Autore filter
        if ($autoreId > 0) {
            // Filter the book set without restricting the aggregate join: the
            // exported row must still contain every creator/contributor, not
            // only the author used to find the book.
            $whereClauses[] = "EXISTS (SELECT 1 FROM libri_autori la_filter WHERE la_filter.libro_id = l.id AND la_filter.autore_id = ?)";
            $bindTypes .= 'i';
            $bindValues[] = $autoreId;
        }

        // Build the query. CI-SOFT-DELETE-EXEMPT: l.deleted_at IS NULL is appended to this exact export before prepare/query.
        $query = "
            SELECT
                l.*,
                GROUP_CONCAT(DISTINCT CASE WHEN la.ruolo = 'principale' THEN a.nome END
                             ORDER BY la.ordine_credito, a.nome SEPARATOR ';') as autori_nomi,
                GROUP_CONCAT(DISTINCT CASE WHEN la.ruolo = 'co-autore' THEN a.nome END
                             ORDER BY la.ordine_credito, a.nome SEPARATOR ';') as coautori_nomi,
                GROUP_CONCAT(DISTINCT CASE WHEN la.ruolo = 'traduttore' THEN a.nome END
                             ORDER BY la.ordine_credito, a.nome SEPARATOR ';') as traduttori_nomi,
                GROUP_CONCAT(DISTINCT CASE WHEN la.ruolo = 'illustratore' THEN a.nome END
                             ORDER BY la.ordine_credito, a.nome SEPARATOR ';') as illustratori_nomi,
                GROUP_CONCAT(DISTINCT CASE WHEN la.ruolo = 'curatore' THEN a.nome END
                             ORDER BY la.ordine_credito, a.nome SEPARATOR ';') as curatori_nomi,
                GROUP_CONCAT(DISTINCT CASE WHEN la.ruolo = 'colorista' THEN a.nome END
                             ORDER BY la.ordine_credito, a.nome SEPARATOR ';') as coloristi_nomi,
                e.nome as editore_nome,
                " . ($hasJunction
                    ? "(SELECT GROUP_CONCAT(e2.nome ORDER BY le.ordine, e2.nome SEPARATOR ';')
                          FROM libri_editori le JOIN editori e2 ON e2.id = le.editore_id
                         WHERE le.libro_id = l.id)"
                    : "e.nome") . " as editori_nomi,
                g.nome as genere_nome
            FROM libri l
            LEFT JOIN libri_autori la ON l.id = la.libro_id
            LEFT JOIN autori a ON la.autore_id = a.id
            LEFT JOIN editori e ON l.editore_id = e.id
            LEFT JOIN generi g ON l.genere_id = g.id
        ";

        if (!empty($selectedIds)) {
            $placeholders = implode(',', array_fill(0, count($selectedIds), '?'));
            $whereClauses[] = "l.id IN ($placeholders)";
            $bindTypes .= str_repeat('i', count($selectedIds));
            $bindValues = array_merge($bindValues, $selectedIds);
        } elseif ($idsRequested) {
            // Fail closed: explicit selected-export with no valid IDs returns empty dataset
            $whereClauses[] = "1 = 0";
        }

        $whereClauses[] = "l.deleted_at IS NULL";

        // The LibraryThing column set is a third party's fixed schema with no
        // place for the request flag, so a wanted title exported there would
        // arrive at the destination indistinguishable from a holding. Excluding
        // it is the honest representation; the standard CSV carries the flag
        // instead (see $exportsDesiderata below).
        if ($format === 'librarything') {
            $whereClauses[] = \App\Support\BookVisibility::catalogue($db, 'l');
        }

        $query .= " WHERE " . implode(' AND ', $whereClauses);

        $query .= " GROUP BY l.id ORDER BY l.id DESC";

        // Raise GROUP_CONCAT limit (MySQL default 1024 bytes) so long author /
        // publisher lists in autori_nomi / editori_nomi are not silently
        // truncated mid-name on export — same precaution as SearchIndexBuilder.
        $db->query("SET SESSION group_concat_max_len = 1000000");

        // Execute query with prepared statement if filters are applied
        if (!empty($bindValues)) {
            $stmt = $db->prepare($query);
            $refs = [];
            foreach ($bindValues as $key => $value) {
                $refs[$key] = &$bindValues[$key];
            }
            array_unshift($refs, $bindTypes);
            call_user_func_array([$stmt, 'bind_param'], $refs);
            $stmt->execute();
            $result = $stmt->get_result();
        } else {
            $result = $db->query($query);
        }

        $libri = [];
        while ($row = $result->fetch_assoc()) {
            $libri[] = $row;
        }

        if (isset($stmt)) {
            $stmt->close();
        }

        // OPTIMIZATION: Use php://temp with memory limit to handle large datasets
        // Stream data instead of building giant string in memory
        $stream = fopen('php://temp/maxmemory:' . (5 * 1024 * 1024), 'r+'); // 5MB limit

        // UTF-8 BOM
        fwrite($stream, "\xEF\xBB\xBF");

        // Probed once: the column only exists where the desiderata plugin has
        // run its schema step.
        $exportsDesiderata = $format !== 'librarything' && \App\Support\BookVisibility::hasDesiderata($db);

        // CSV headers based on format
        if ($format === 'librarything') {
            $headers = $this->getLibraryThingHeaders();
        } else {
            // Standard format (default)
            $headers = [
                'id',
                'isbn10',
                'isbn13',
                'ean',
                'titolo',
                'sottotitolo',
                'descrizione',
                'autori',
                'co_autori',
                'editore',
                'anno_pubblicazione',
                'lingua',
                'edizione',
                'numero_pagine',
                'genere',
                'formato',
                'tipo_media',
                'prezzo',
                'copie_totali',
                'collana',
                'numero_serie',
                'traduttore',
                'illustratore',
                'curatore',
                'colorista',
                'classificazione_dewey',
                'parole_chiave'
            ];
            // Appended ONLY when the column exists, so an export from an
            // installation without the desiderata plugin stays byte-identical
            // and any consumer positioning by index keeps working.
            if ($exportsDesiderata) {
                $headers[] = 'is_desiderata';
            }
        }

        // formulaPrefix "'" neutralizes CSV injection: user-controlled fields
        // (titolo, autori, editore, parole_chiave…) starting with = + - @ are
        // prefixed so spreadsheet clients don't evaluate them as formulas.
        //
        // The LibraryThing export is a machine round-trip format (TSV re-imported
        // into LibraryThing/Pinakes), NOT a spreadsheet view: EscapeFormula would
        // prepend "'" to legitimate values starting with -, @, = or a tab,
        // corrupting the round-trip and diverging from the dedicated
        // LibraryThingImportController::exportLibraryThing (which does not prefix).
        // So the formula guard applies only to the human-facing standard CSV.
        $formulaPrefix = $format === 'librarything' ? null : "'";
        $writer = Csv::writerToStream($stream, $delimiter, $formulaPrefix);
        $writer->insertOne($headers);

        $rowCount = 0;
        foreach ($libri as $libro) {
            // Use anno_pubblicazione directly (SMALLINT UNSIGNED type in DB, range 0-65535)
            $anno = $libro['anno_pubblicazione'] ?? '';

            if ($format === 'librarything') {
                $row = $this->formatLibraryThingRow($libro, $anno);
            } else {
                // Standard format (default)
                $descrizione = $this->normalizeDescriptionForCsv((string) ($libro['descrizione'] ?? ''));
                $row = [
                    $libro['id'] ?? '',
                    $libro['isbn10'] ?? '',
                    $libro['isbn13'] ?? '',
                    $libro['ean'] ?? '',
                    $libro['titolo'] ?? '',
                    $libro['sottotitolo'] ?? '',
                    $descrizione,
                    $libro['autori_nomi'] ?? '',
                    $libro['coautori_nomi'] ?? '',
                    ($libro['editori_nomi'] ?? '') !== '' ? $libro['editori_nomi'] : ($libro['editore_nome'] ?? ''),
                    $anno,
                    $libro['lingua'] ?? '',
                    $libro['edizione'] ?? '',
                    $libro['numero_pagine'] ?? '',
                    $libro['genere_nome'] ?? '',
                    $libro['formato'] ?? '',
                    $libro['tipo_media'] ?? '',
                    $libro['prezzo'] ?? '',
                    $libro['copie_totali'] ?? '1',
                    $libro['collana'] ?? '',
                    $libro['numero_serie'] ?? '',
                    ($libro['traduttori_nomi'] ?? '') !== '' ? $libro['traduttori_nomi'] : ($libro['traduttore'] ?? ''),
                    ($libro['illustratori_nomi'] ?? '') !== '' ? $libro['illustratori_nomi'] : ($libro['illustratore'] ?? ''),
                    ($libro['curatori_nomi'] ?? '') !== '' ? $libro['curatori_nomi'] : ($libro['curatore'] ?? ''),
                    $libro['coloristi_nomi'] ?? '',
                    $libro['classificazione_dewey'] ?? '',
                    $libro['parole_chiave'] ?? ''
                ];
                if ($exportsDesiderata) {
                    $row[] = (int) ($libro['is_desiderata'] ?? 0);
                }
            }

            $writer->insertOne($row);

            // OPTIMIZATION: Garbage collection every 1000 rows to prevent memory buildup
            if (++$rowCount % 1000 === 0) {
                gc_collect_cycles();
            }
        }

        rewind($stream);

        $filename = 'libri_export_' . date('Y-m-d_His') . '.csv';

        return $response
            ->withHeader('Content-Type', 'text/csv; charset=UTF-8')
            ->withHeader('Content-Disposition', 'attachment; filename="' . $filename . '"')
            ->withHeader('Cache-Control', 'no-cache, no-store, must-revalidate')
            ->withHeader('Pragma', 'no-cache')
            ->withHeader('Expires', '0')
            ->withHeader('X-Content-Type-Options', 'nosniff')
            ->withHeader('X-Frame-Options', 'DENY')
            ->withHeader('Content-Security-Policy', "default-src 'none'")
            ->withBody(new \Slim\Psr7\Stream($stream));
    }

    /**
     * Get LibraryThing CSV headers
     *
     * @return array Headers in LibraryThing format
     */
    private function getLibraryThingHeaders(): array
    {
        return [
            'Book Id',
            'Title',
            'Sort Character',
            'Primary Author',
            'Primary Author Role',
            'Secondary Author',
            'Secondary Author Roles',
            'Publication',
            'Date',
            'Review',
            'Rating',
            'Comment',
            'Private Comment',
            'Summary',
            'Media',
            'Physical Description',
            'Weight',
            'Height',
            'Thickness',
            'Length',
            'Dimensions',
            'Page Count',
            'LCCN',
            'Acquired',
            'Date Started',
            'Date Read',
            'Barcode',
            'BCID',
            'Tags',
            'Collections',
            'Languages',
            'Original Languages',
            'LC Classification',
            'ISBN',
            'ISBNs',
            'Subjects',
            'Dewey Decimal',
            'Dewey Wording',
            'Other Call Number',
            'Copies',
            'Source',
            'Entry Date',
            'From Where',
            'OCLC',
            'Work id',
            'Lending Patron',
            'Lending Status',
            'Lending Start',
            'Lending End',
            'List Price',
            'Purchase Price',
            'Value',
            'Condition',
            'ISSN'
        ];
    }

    private function normalizeDescriptionForCsv(string $html): string
    {
        // Single source of truth for HTML→plain description projection.
        return (string) \App\Support\DescriptionText::toPlain($html);
    }

    /**
     * Format book row in LibraryThing format
     *
     * @param array $libro Book data from database
     * @param string $anno Publication year
     * @return array Row data in LibraryThing format
     */
    private function formatLibraryThingRow(array $libro, string $anno): array
    {
        // LibraryThing has one primary-author cell plus parallel secondary
        // author/role lists. Keep every principal after the first as an
        // unqualified secondary creator, and encode all other relational roles
        // explicitly so an export/import cycle does not promote co-authors or
        // discard contributors.
        $principalAuthors = \App\Support\ContributorSync::splitNames((string) ($libro['autori_nomi'] ?? ''));
        $primaryAuthor = array_shift($principalAuthors) ?? '';
        $secondaryAuthors = $principalAuthors;
        $secondaryAuthorRoles = array_fill(0, count($secondaryAuthors), '');
        $roleSources = [
            'Co-author' => (string) ($libro['coautori_nomi'] ?? ''),
            'Translator' => (string) (($libro['traduttori_nomi'] ?? '') !== '' ? $libro['traduttori_nomi'] : ($libro['traduttore'] ?? '')),
            'Illustrator' => (string) (($libro['illustratori_nomi'] ?? '') !== '' ? $libro['illustratori_nomi'] : ($libro['illustratore'] ?? '')),
            'Editor' => (string) (($libro['curatori_nomi'] ?? '') !== '' ? $libro['curatori_nomi'] : ($libro['curatore'] ?? '')),
            'Colorist' => (string) ($libro['coloristi_nomi'] ?? ''),
        ];
        foreach ($roleSources as $role => $rawNames) {
            foreach (\App\Support\ContributorSync::splitNames($rawNames) as $name) {
                $secondaryAuthors[] = $name;
                $secondaryAuthorRoles[] = $role;
            }
        }
        $secondaryAuthor = implode('; ', $secondaryAuthors);
        $secondaryAuthorRole = implode('; ', $secondaryAuthorRoles);

        // Map formato to Media
        $formatoMap = [
            'cartaceo' => 'Libro cartaceo',
            'ebook' => 'eBook',
            'audiolibro' => 'Audiobook',
            'rivista' => 'Magazine',
        ];
        $media = $formatoMap[$libro['formato'] ?? 'cartaceo'] ?? 'Libro cartaceo';

        // Build publication string (City, Publisher, Year)
        $publication = '';
        if (!empty($libro['editore_nome'])) {
            $publication = $libro['editore_nome'];
            if (!empty($anno)) {
                $publication .= ', ' . $anno;
            }
        }

        // Map lingua to Languages
        $linguaMap = [
            'italiano' => 'Italian',
            'inglese' => 'English',
            'francese' => 'French',
            'tedesco' => 'German',
            'spagnolo' => 'Spanish',
        ];
        $language = $linguaMap[$libro['lingua'] ?? 'italiano'] ?? ucfirst($libro['lingua'] ?? 'Italian');

        // Build ISBNs (combine isbn10 and isbn13)
        $isbns = [];
        if (!empty($libro['isbn13'])) {
            $isbns[] = $libro['isbn13'];
        }
        if (!empty($libro['isbn10'])) {
            $isbns[] = $libro['isbn10'];
        }
        $isbnString = !empty($isbns) ? '[' . implode(', ', $isbns) . ']' : '';

        // Calculate Sort Character from first significant character of title
        $sortChar = '';
        if (!empty($libro['titolo'])) {
            $title = trim($libro['titolo']);
            if ($title !== '') {
                $sortChar = mb_strtoupper(mb_substr($title, 0, 1, 'UTF-8'), 'UTF-8');
            }
        }

        return [
            $libro['id'] ?? '',                                    // Book Id
            $libro['titolo'] ?? '',                                // Title
            $sortChar,                                             // Sort Character
            $primaryAuthor,                                        // Primary Author
            '',                                                    // Primary Author Role
            $secondaryAuthor,                                      // Secondary Author
            $secondaryAuthorRole,                                  // Secondary Author Roles
            $publication,                                          // Publication
            $anno,                                                 // Date
            $libro['review'] ?? '',                                // Review
            $libro['rating'] ?? '',                                // Rating
            $libro['comment'] ?? '',                               // Comment
            $libro['private_comment'] ?? '',                       // Private Comment
            $this->normalizeDescriptionForCsv((string) ($libro['descrizione'] ?? '')), // Summary
            $media,                                                // Media
            $libro['physical_description'] ?? '',                  // Physical Description
            $libro['peso'] ?? '',                                  // Weight
            '',                                                    // Height
            '',                                                    // Thickness
            '',                                                    // Length
            $libro['dimensioni'] ?? '',                            // Dimensions
            $libro['numero_pagine'] ?? '',                         // Page Count
            $libro['lccn'] ?? '',                                  // LCCN
            $libro['data_acquisizione'] ?? '',                     // Acquired
            $libro['date_started'] ?? '',                          // Date Started
            $libro['date_read'] ?? '',                             // Date Read
            $libro['barcode'] ?? $libro['ean'] ?? '',              // Barcode
            $libro['bcid'] ?? '',                                  // BCID
            $libro['parole_chiave'] ?? '',                         // Tags
            $libro['collana'] ?? '',                               // Collections
            $language,                                             // Languages
            $libro['original_languages'] ?? '',                    // Original Languages
            $libro['lc_classification'] ?? '',                     // LC Classification
            $libro['isbn13'] ?? $libro['isbn10'] ?? '',            // ISBN
            $isbnString,                                           // ISBNs
            $libro['genere_nome'] ?? '',                           // Subjects
            $libro['classificazione_dewey'] ?? '',                 // Dewey Decimal
            $libro['dewey_wording'] ?? '',                         // Dewey Wording
            $libro['other_call_number'] ?? '',                     // Other Call Number
            $libro['copie_totali'] ?? '1',                         // Copies
            $libro['source'] ?? '',                                // Source
            $libro['entry_date'] ?? '',                            // Entry Date
            $libro['from_where'] ?? '',                            // From Where
            $libro['oclc'] ?? '',                                  // OCLC
            $libro['work_id'] ?? '',                               // Work id
            $libro['lending_patron'] ?? '',                        // Lending Patron
            $libro['lending_status'] ?? '',                        // Lending Status
            $libro['lending_start'] ?? '',                         // Lending Start
            $libro['lending_end'] ?? '',                           // Lending End
            $libro['prezzo'] ?? '',                                // List Price
            '',                                                    // Purchase Price
            $libro['value'] ?? '',                                 // Value
            $libro['condition_lt'] ?? '',                          // Condition
            $libro['issn'] ?? ''                                   // ISSN
        ];
    }

    /**
     * Sync book covers using scraping plugins (Open Library, Scraper Pro, Scraper API)
     */
    public function syncCovers(Request $request, Response $response, mysqli $db): Response
    {
        set_time_limit(600); // 10 minutes max

        $synced = 0;
        $skipped = 0;
        $errors = 0;

        // Find all books with ISBN but without LOCAL cover (missing, placeholder, or remote URL).
        // The SQL filter catches the obvious cases (NULL, empty, placeholder, http://...);
        // an additional filesystem check below catches the trickier case of a DB
        // column pointing to a local path whose file has been deleted/lost.
        $query = "SELECT id, isbn13, isbn10, titolo, copertina_url
                  FROM libri
                  WHERE (isbn13 IS NOT NULL AND isbn13 != '' OR isbn10 IS NOT NULL AND isbn10 != '')
                    AND deleted_at IS NULL
                  ORDER BY id DESC";

        $result = $db->query($query);
        $books = [];
        // Resolve covers dir once. realpath() returns false if the directory
        // doesn't exist yet (fresh install before any cover was downloaded);
        // in that case any local cover_url is unreachable, so every book
        // with a non-remote URL becomes a re-fetch candidate — exactly what
        // we want on first sync.
        $baseDir = realpath($this->getCoversUploadPath());
        while ($row = $result->fetch_assoc()) {
            $url = (string) ($row['copertina_url'] ?? '');
            $needsCover = $url === ''
                || strpos($url, 'placeholder.jpg') !== false
                || preg_match('#^https?://#i', $url) === 1;
            if (!$needsCover) {
                // Has a local cover_url — verify the file actually exists AND
                // resolves inside $baseDir (defence-in-depth: basename() already
                // strips path components, realpath()+str_starts_with() catches
                // any symlink that points outside the covers dir).
                if ($baseDir === false) {
                    $needsCover = true; // covers dir gone → treat all as missing
                } else {
                    $candidate = $baseDir . DIRECTORY_SEPARATOR . basename($url);
                    $resolved  = realpath($candidate);
                    if ($resolved === false
                        || !str_starts_with($resolved, $baseDir . DIRECTORY_SEPARATOR)
                        || !is_file($resolved)
                    ) {
                        $needsCover = true;
                    }
                }
            }
            if ($needsCover) {
                $books[] = $row;
            }
        }

        error_log("[Cover Sync] Found " . count($books) . " books without covers");

        $coverController = new \App\Controllers\CoverController();

        foreach ($books as $book) {
            $isbn = $book['isbn13'] ?: $book['isbn10'];

            if (!$isbn) {
                $skipped++;
                continue;
            }

            try {
                error_log("[Cover Sync] Attempting to scrape cover for book ID {$book['id']}, ISBN: $isbn");

                // Use scraping controller to get book data
                $scrapedData = $this->scrapeBookCover($isbn);

                if (!empty($scrapedData['image'])) {
                    // Download cover locally, never store remote URLs
                    try {
                        $coverData = $coverController->downloadFromUrl($scrapedData['image']);

                        if (!empty($coverData['file_url'])) {
                            $stmt = $db->prepare("UPDATE libri SET copertina_url = ? WHERE id = ?");
                            $stmt->bind_param('si', $coverData['file_url'], $book['id']);
                            $stmt->execute();
                            $stmt->close();

                            // Cover is in the cached book-detail DTO / catalog rows
                            // (#387). Defer collapses the whole bulk sync into a
                            // single invalidation at shutdown.
                            \App\Support\ContentCache::deferBooksChanged();

                            $synced++;
                            error_log("[Cover Sync] Cover downloaded for book ID {$book['id']}: {$coverData['file_url']}");
                        } else {
                            $errors++;
                            error_log("[Cover Sync] Cover download returned empty for book ID {$book['id']}");
                        }
                    } catch (\Throwable $e) {
                        $errors++;
                        error_log("[Cover Sync] Cover download failed for book ID {$book['id']}: " . $e->getMessage());
                    }
                } else {
                    $errors++;
                    error_log("[Cover Sync] No cover found for book ID {$book['id']}, ISBN: $isbn");
                }

                // Rate limiting: wait 2 seconds between requests
                sleep(2);

            } catch (\Throwable $e) {
                $errors++;
                error_log("[Cover Sync] Error scraping book ID {$book['id']}: " . $e->getMessage());
            }
        }

        $response->getBody()->write(json_encode([
            'success' => true,
            'synced' => $synced,
            'skipped' => $skipped,
            'errors' => $errors,
            'total' => count($books)
        ]));

        return $response->withHeader('Content-Type', 'application/json');
    }

    /**
     * Scrape book cover from online services
     * Uses centralized scraping service with hooks system
     */
    private function scrapeBookCover(string $isbn): array
    {
        // Use centralized scraping service for cover only
        return \App\Support\ScrapingService::scrapeBookCover($isbn, 3);
    }

    /**
     * Update author biography if currently empty
     * Used to populate bio from scraped data without overwriting existing content
     */
    private function updateAuthorBioIfEmpty(\mysqli $db, int $authorId, string $bio): void
    {
        if ($bio === '' || $authorId <= 0) {
            return;
        }

        // Decode HTML entities for consistency with AuthorRepository::create()
        $bio = \App\Support\HtmlHelper::decode($bio);

        // Atomic update: only set bio if currently empty (prevents TOCTOU race condition)
        // Use TRIM to also catch biographies containing only whitespace
        $stmt = $db->prepare("UPDATE autori SET biografia = ? WHERE id = ? AND (biografia IS NULL OR TRIM(biografia) = '')");
        if (!$stmt) {
            return;
        }

        $stmt->bind_param('si', $bio, $authorId);
        $stmt->execute();
        $affectedRows = $stmt->affected_rows;
        $stmt->close();

        if ($affectedRows > 0) {
            error_log("[LibriController] Updated author bio for ID $authorId from scraping");
        }
    }

    /**
     * Resolve the multi-publisher selection (issue #143) into an ordered,
     * deduplicated list of publisher ids. Combines existing ids (editori_ids[])
     * with newly typed names (editori_new[], find-or-create), and falls back to
     * the single primary publisher already resolved from the legacy
     * editore_search field or from scraping.
     *
     * @param array<string,mixed> $data
     * @return list<int>
     */
    private function resolvePublisherIds(\mysqli $db, array $data, ?int $primaryEditoreId): array
    {
        // Client-supplied existing ids are untrusted: validate them against the
        // `editori` table before use (an unknown id would FK-fail the editore_id
        // UPDATE in createBasic/updateBasic).
        $clientIds = [];
        foreach ((array) ($data['editori_ids'] ?? []) as $rawId) {
            $id = (int) $rawId;
            if ($id > 0) {
                $clientIds[] = $id;
            }
        }
        $ids = $this->filterExistingPublisherIds($db, array_values(array_unique($clientIds)));

        // Newly typed publishers are find-or-created here, so their ids are valid
        // by construction.
        if (!empty($data['editori_new'])) {
            $pubRepo = new \App\Models\PublisherRepository($db);
            foreach ((array) $data['editori_new'] as $nome) {
                $nome = $this->sanitizePublisherName(trim((string) $nome));
                if ($nome === '') {
                    continue;
                }
                $found = $pubRepo->findByName($nome);
                $ids[] = $found ?? $pubRepo->create(['nome' => $nome, 'sito_web' => '']);
            }
        }

        // Back-compat: no multi-select used, but a single publisher was resolved
        // (legacy editore_search field or scraping) → keep it if it still exists.
        if ($ids === [] && $primaryEditoreId !== null && $primaryEditoreId > 0
            && $this->filterExistingPublisherIds($db, [$primaryEditoreId]) !== []) {
            $ids[] = $primaryEditoreId;
        }

        return array_values(array_unique(array_map('intval', $ids)));
    }

    /**
     * Filter a list of publisher ids down to those that actually exist in
     * `editori`, preserving the input order.
     *
     * @param list<int> $ids
     * @return list<int>
     */
    private function filterExistingPublisherIds(\mysqli $db, array $ids): array
    {
        if ($ids === []) {
            return [];
        }
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $stmt = $db->prepare("SELECT id FROM editori WHERE id IN ($placeholders)");
        if ($stmt === false) {
            return [];
        }
        $stmt->bind_param(str_repeat('i', count($ids)), ...$ids);
        $stmt->execute();
        $res = $stmt->get_result();
        $valid = [];
        if ($res instanceof \mysqli_result) {
            while ($row = $res->fetch_assoc()) {
                $valid[] = (int) $row['id'];
            }
            $res->free();
        }
        $stmt->close();

        return array_values(array_filter($ids, static fn(int $i): bool => in_array($i, $valid, true)));
    }

    /**
     * Save LibraryThing fields visibility preferences with whitelist validation
     *
     * @param \mysqli $db Database connection
     * @param int $id Book ID
     * @param array $ltVisibilityInput User-supplied visibility data
     * @return void
     */
    private function saveLtVisibility(\mysqli $db, int $id, array $ltVisibilityInput): void
    {
        // Define whitelist of allowed LibraryThing field names
        $allowedLtFields = array_keys(LibraryThingInstaller::getLibraryThingFields());

        $ltVisibility = [];
        foreach ($ltVisibilityInput as $field => $value) {
            // Only include fields that are in the whitelist
            if (in_array($field, $allowedLtFields, true) && $value === '1') {
                $ltVisibility[$field] = true;
            }
        }

        // Save as JSON
        try {
            $visibilityJson = !empty($ltVisibility) ? json_encode($ltVisibility, JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR) : null;
        } catch (\JsonException $e) {
            SecureLogger::error('JSON encoding failed for LibraryThing visibility: ' . $e->getMessage(), [
                'book_id' => $id,
            ]);
            throw new \RuntimeException('Failed to save LibraryThing field visibility', 0, $e);
        }

        $stmt = $db->prepare("UPDATE libri SET lt_fields_visibility = ? WHERE id = ?");
        $stmt->bind_param('si', $visibilityJson, $id);
        $stmt->execute();
        $stmt->close();
    }

    /**
     * @return array<string, mixed>|null Returns null if body cannot be parsed
     */
    private function parseRequestBody(Request $request): ?array
    {
        $parsed = $request->getParsedBody();
        if ($parsed !== null) {
            return (array) $parsed;
        }
        $body = (string) $request->getBody();
        if ($body === '') {
            return null;
        }
        $decoded = json_decode($body, true);
        if (!is_array($decoded)) {
            SecureLogger::warning('parseRequestBody: JSON decode failed', [
                'error' => json_last_error_msg(),
                'body_length' => strlen($body),
            ]);
            return null;
        }
        return $decoded;
    }
}