<?php

declare(strict_types=1);

if (session_status() !== PHP_SESSION_ACTIVE) {
    session_start();
}

require_once __DIR__ . '/../db/config.php';
require_once __DIR__ . '/../ai/LocalAIApi.php';

function library_get_language(): string
{
    // Check GET
    if (isset($_GET['lang']) && in_array($_GET['lang'], ['en', 'ar'])) {
        $_SESSION['lang'] = $_GET['lang'];
    }
    
    // Check Session or Default
    return $_SESSION['lang'] ?? 'en';
}

function library_is_arabic_text(?string $text): bool
{
    if ($text === null) {
        return false;
    }

    return preg_match('/\p{Arabic}/u', $text) === 1;
}

function library_text_lang(?string $text, ?string $fallback = null): string
{
    if (library_is_arabic_text($text)) {
        return 'ar';
    }

    return $fallback ?? 'en';
}

function library_text_dir(?string $text, ?string $fallback = null): string
{
    return library_text_lang($text, $fallback) === 'ar' ? 'rtl' : 'ltr';
}

function library_trans(string $key, ?string $lang = null): string
{
    $lang = $lang ?? library_get_language();
    
    $translations = [
        'en' => [
            'catalog' => 'Catalog',
            'admin_studio' => 'Admin Studio',
            'nabd_library' => 'Nabd Library',
            'tagline' => 'Arabic · English e-library',
            'search' => 'Search...', 
            'switch_lang' => 'العربية',
            'switch_lang_code' => 'ar',
            'browse_public' => 'Browse public catalog',
            'open_admin' => 'Open Admin Studio',
            'upload_docs' => 'Upload documents, publish public/private titles, read online, and request AI summaries.',
            'mvp_label' => 'Bilingual reader MVP',
            'admin_panel' => 'Admin Panel',
            'dashboard' => 'Dashboard',
            'material_entry' => 'Material Entry',
            'categories' => 'Categories',
            'subcategories' => 'Subcategories',
            'types' => 'Types',
            'return_to_site' => 'Return to Site',
            'total_documents' => 'Total Documents',
            'public_titles' => 'Public Titles',
            'total_downloads' => 'Total Downloads',
            'recent_documents' => 'Recent Documents',
            'title_author' => 'Title / Author',
            'type_category' => 'Type / Category',
            'visibility' => 'Visibility',
            'actions' => 'Actions',
            'view' => 'View',
            'showing_last_10' => 'Showing last 10 uploads',
            'manage_documents' => 'Manage Documents',
            'manage_documents_desc' => 'Manage your library documents, upload new materials, and edit metadata including bilingual titles, summaries, and more.',
            'create_new' => 'Create New',
            'edit' => 'Edit',
            'delete' => 'Delete',
            'save' => 'Save',
            'cancel' => 'Cancel',
            'name_en' => 'English Name',
            'name_ar' => 'Arabic Name',
            'description_en' => 'English Description',
            'description_ar' => 'Arabic Description',
            'confirm_delete' => 'Are you sure you want to delete this item?',
            'manage_categories_desc' => 'Manage document categories.',
            'add_new_category' => 'Add New Category',
            'search_placeholder' => 'Search...',
            'clear' => 'Clear',
            'name' => 'Name',
            'no_categories_found' => 'No categories found.',
            'add_new_document' => 'Add New Material',
            'search_docs_placeholder' => 'Search by title or author...',
            'id' => 'ID',
            'cover' => 'Cover',
            'year' => 'Year',
            'publisher' => 'Publisher',
            'country' => 'Country',
            'page_count' => 'Total Pages',
            'summary_en' => 'Summary (English)',
            'summary_ar' => 'Summary (Arabic)',
            'cover_image' => 'Front Cover Image',
            'document_file' => 'Document File',
            'visibility_permissions' => 'Visibility & Permissions',
            'download' => 'Download',
            'print' => 'Print',
            'copy' => 'Copy',
            'author' => 'Author',
            'select_type' => 'Select Type...',
            'select_category' => 'Select Category...',
            'select_subcategory' => 'Select Subcategory...',
            'required_new_docs' => 'Required for new documents.',
            'leave_empty_keep' => 'Leave empty to keep existing file.',
            'save_changes' => 'Save Changes',
            'manage_subcategories_desc' => 'Manage document subcategories.',
            'add_new_subcategory' => 'Add New Subcategory',
            'no_subcategories_found' => 'No subcategories found.',
            'category' => 'Category',
            'manage_types_desc' => 'Manage document types.',
            'add_new_type' => 'Add New Type',
            'no_types_found' => 'No types found.',
            'previous' => 'Previous',
            'next' => 'Next',
            'showing_page' => 'Showing page',
            'of' => 'of'
        ],
        'ar' => [
            'catalog' => 'الفهرس',
            'admin_studio' => 'استوديو الإدارة',
            'nabd_library' => 'مكتبة نبض',
            'tagline' => 'مكتبة إلكترونية عربية · إنجليزية',
            'search' => 'بحث...',
            'switch_lang' => 'English',
            'switch_lang_code' => 'en',
            'browse_public' => 'تصفح الفهرس العام',
            'open_admin' => 'فتح استوديو الإدارة',
            'upload_docs' => 'ارفع المستندات، انشر العناوين العامة/الخاصة، اقرأ عبر الإنترنت، واطلب ملخصات الذكاء الاصطناعي.',
            'mvp_label' => 'نموذج القارئ ثنائي اللغة',
            'admin_panel' => 'لوحة التحكم',
            'dashboard' => 'لوحة القيادة',
            'material_entry' => 'إدخال المواد',
            'categories' => 'الفئات',
            'subcategories' => 'الفئات الفرعية',
            'types' => 'الأنواع',
            'return_to_site' => 'العودة للموقع',
            'total_documents' => 'إجمالي المستندات',
            'public_titles' => 'العناوين العامة',
            'total_downloads' => 'إجمالي التنزيلات',
            'recent_documents' => 'أحدث المستندات',
            'title_author' => 'العنوان / المؤلف',
            'type_category' => 'النوع / الفئة',
            'visibility' => 'الظهور',
            'actions' => 'الإجراءات',
            'view' => 'عرض',
            'showing_last_10' => 'عرض آخر 10 ملفات',
            'manage_documents' => 'إدارة المستندات',
            'manage_documents_desc' => 'إدارة مستندات المكتبة، رفع مواد جديدة، وتعديل البيانات الوصفية بما في ذلك العناوين والملخصات ثنائية اللغة.',
            'create_new' => 'إنشاء جديد',
            'edit' => 'تعديل',
            'delete' => 'حذف',
            'save' => 'حفظ',
            'cancel' => 'إلغاء',
            'name_en' => 'الاسم (إنجليزي)',
            'name_ar' => 'الاسم (عربي)',
            'description_en' => 'الوصف (إنجليزي)',
            'description_ar' => 'الوصف (عربي)',
            'confirm_delete' => 'هل أنت متأكد من حذف هذا العنصر؟',
            'manage_categories_desc' => 'إدارة فئات المستندات.',
            'add_new_category' => 'إضافة فئة جديدة',
            'search_placeholder' => 'بحث...',
            'clear' => 'مسح',
            'name' => 'الاسم',
            'no_categories_found' => 'لم يتم العثور على فئات.',
            'add_new_document' => 'إضافة مادة جديدة',
            'search_docs_placeholder' => 'بحث بالعنوان أو المؤلف...',
            'id' => 'المعرف',
            'cover' => 'الغلاف',
            'year' => 'السنة',
            'publisher' => 'الناشر',
            'country' => 'الدولة',
            'page_count' => 'عدد الصفحات',
            'summary_en' => 'الملخص (إنجليزي)',
            'summary_ar' => 'الملخص (عربي)',
            'cover_image' => 'صورة الغلاف الأمامي',
            'document_file' => 'ملف المستند',
            'visibility_permissions' => 'الظهور والصلاحيات',
            'download' => 'تنزيل',
            'print' => 'طباعة',
            'copy' => 'نسخ',
            'author' => 'المؤلف',
            'select_type' => 'اختر النوع...',
            'select_category' => 'اختر الفئة...',
            'select_subcategory' => 'اختر الفئة الفرعية...',
            'required_new_docs' => 'مطلوب للمستندات الجديدة.',
            'leave_empty_keep' => 'اتركه فارغاً للاحتفاظ بالملف الحالي.',
            'save_changes' => 'حفظ التغييرات',
            'manage_subcategories_desc' => 'إدارة الفئات الفرعية للمستندات.',
            'add_new_subcategory' => 'إضافة فئة فرعية جديدة',
            'no_subcategories_found' => 'لم يتم العثور على فئات فرعية.',
            'category' => 'الفئة',
            'manage_types_desc' => 'إدارة أنواع المستندات.',
            'add_new_type' => 'إضافة نوع جديد',
            'no_types_found' => 'لم يتم العثور على أنواع.',
            'previous' => 'السابق',
            'next' => 'التالي',
            'showing_page' => 'عرض صفحة',
            'of' => 'من'
        ]
    ];

    return $translations[$lang][$key] ?? $key;
}

function library_bootstrap(): void
{
    static $booted = false;
    if ($booted) {
        return;
    }

    $migrationPath = __DIR__ . '/../db/migrations/001_library_documents.sql';
    if (is_file($migrationPath)) {
        $sql = file_get_contents($migrationPath);
        if (is_string($sql) && trim($sql) !== '') {
            db()->exec($sql);
        }
    }

    // Run new migrations if needed
    $migration2Path = __DIR__ . '/../db/migrations/002_add_library_metadata.sql';
    if (is_file($migration2Path)) {
        // Simple check if columns exist
        $exists = db()->query("SHOW COLUMNS FROM library_documents LIKE 'category_ar'")->fetch();
        if (!$exists) {
            $sql = file_get_contents($migration2Path);
            db()->exec($sql);
        }
    }

    $migration3Path = __DIR__ . '/../db/migrations/003_normalize_categories.sql';
    if (is_file($migration3Path)) {
        // Simple check if table exists
        $exists = db()->query("SHOW TABLES LIKE 'library_categories'")->fetch();
        if (!$exists) {
            $sql = file_get_contents($migration3Path);
            db()->exec($sql);
        }
    }

    $migration4Path = __DIR__ . '/../db/migrations/004_create_types_table.sql';
    if (is_file($migration4Path)) {
        // Simple check if table exists
        $exists = db()->query("SHOW TABLES LIKE 'library_types'")->fetch();
        if (!$exists) {
            $sql = file_get_contents($migration4Path);
            db()->exec($sql);
        }
    }

    $migration5Path = __DIR__ . '/../db/migrations/005_update_document_fields.sql';
    if (is_file($migration5Path)) {
        // Check if column exists
        $exists = db()->query("SHOW COLUMNS FROM library_documents LIKE 'cover_image_path'")->fetch();
        if (!$exists) {
            $sql = file_get_contents($migration5Path);
            db()->exec($sql);
        }
    }

    $migration6Path = __DIR__ . '/../db/migrations/006_add_author_column.sql';
    if (is_file($migration6Path)) {
        // Check if column exists
        $exists = db()->query("SHOW COLUMNS FROM library_documents LIKE 'author'")->fetch();
        if (!$exists) {
            $sql = file_get_contents($migration6Path);
            db()->exec($sql);
        }
    }

    $uploadDir = __DIR__ . '/../uploads/library';
    if (!is_dir($uploadDir)) {
        mkdir($uploadDir, 0775, true);
    }
    
    $coverDir = __DIR__ . '/../uploads/covers';
    if (!is_dir($coverDir)) {
        mkdir($coverDir, 0775, true);
    }

    library_seed_demo_documents();
    $booted = true;
}

function h(?string $value): string
{
    return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
}

function library_project_meta(): array
{
    return [
        'name' => $_SERVER['PROJECT_NAME'] ?? 'Nabd Library',
        'description' => $_SERVER['PROJECT_DESCRIPTION'] ?? 'Bilingual electronic library for Arabic and English documents, online reading, and AI-assisted summaries.',
        'image' => $_SERVER['PROJECT_IMAGE_URL'] ?? '',
    ];
}

function library_set_flash(string $type, string $message): void
{
    $_SESSION['library_flash'][] = ['type' => $type, 'message' => $message];
}

function library_get_flashes(): array
{
    $flashes = $_SESSION['library_flash'] ?? [];
    unset($_SESSION['library_flash']);
    return is_array($flashes) ? $flashes : [];
}

function library_seed_demo_documents(): void
{
    $count = (int) (db()->query('SELECT COUNT(*) FROM library_documents')->fetchColumn() ?: 0);
    if ($count > 0) {
        return;
    }

    $pdfRelative = 'uploads/library/demo-library-guide.pdf';
    $txtRelative = 'uploads/library/demo-bilingual-notes.txt';
    $pdfAbsolute = __DIR__ . '/../' . $pdfRelative;
    $txtAbsolute = __DIR__ . '/../' . $txtRelative;
}

function library_old(string $key, string $default = ''): string
{
    return isset($_POST[$key]) ? trim((string) $_POST[$key]) : $default;
}

function library_document_type_label(string $type): string
{
    $map = [
        'pdf' => 'PDF reader',
        'txt' => 'Text note',
        'doc' => 'Word document',
        'docx' => 'Word document',
        'ppt' => 'PowerPoint',
        'pptx' => 'PowerPoint',
    ];
    return $map[strtolower($type)] ?? strtoupper($type);
}

function library_language_label(string $lang): string
{
    $map = [
        'en' => 'English',
        'ar' => 'Arabic',
        'bilingual' => 'Bilingual',
    ];
    return $map[$lang] ?? 'Unknown';
}

function library_visibility_label(string $visibility): string
{
    return $visibility === 'private' ? 'Private / login' : 'Public';
}

function library_allowed_extensions(): array
{
    return [
        'pdf' => 'PDF reader',
        'txt' => 'Text note',
        'doc' => 'Word document',
        'docx' => 'Word document',
        'ppt' => 'PowerPoint',
        'pptx' => 'PowerPoint',
    ];
}

function library_file_url(string $path): string
{
    if ($path === '') return '';
    if ($path[0] !== '/') {
        return '/' . $path;
    }
    return $path;
}

function library_can_preview(array $doc): bool
{
    // Only PDFs are supported for inline preview in this version
    return strtolower((string) ($doc['document_type'] ?? '')) === 'pdf';
}

function library_increment_views(int $id): void
{
    library_bootstrap();
    $stmt = db()->prepare('UPDATE library_documents SET view_count = view_count + 1 WHERE id = ?');
    $stmt->execute([$id]);
}

function library_generate_summary(int $id): array
{
    library_bootstrap();
    $doc = library_fetch_document($id);
    if (!$doc) {
        return ['success' => false, 'message' => 'Document not found.'];
    }

    // 1. Try to extract text from the file
    $text = '';
    $filePath = __DIR__ . '/../' . ($doc['file_path'] ?? '');
    
    if (file_exists($filePath)) {
        $ext = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));
        if ($ext === 'pdf') {
            // pdftotext is available via poppler-utils
            // -layout preserves structure
            // -f 1 -l 20 limits to first 20 pages (usually sufficient for summary)
            $cmd = sprintf("pdftotext -layout -f 1 -l 20 %s -", escapeshellarg($filePath));
            $text = shell_exec($cmd);
        } elseif (in_array($ext, ['txt', 'md', 'csv', 'json'])) {
            $text = file_get_contents($filePath);
        }
    }
    
    $text = trim((string)$text);

    // 2. Fallback to description/title if extraction failed or empty
    if ($text === '') {
        $descEn = trim((string) ($doc['description_en'] ?? ''));
        $descAr = trim((string) ($doc['description_ar'] ?? ''));
        $titleEn = trim((string) ($doc['title_en'] ?? ''));
        $titleAr = trim((string) ($doc['title_ar'] ?? ''));
        
        $text = "Title (EN): $titleEn\nTitle (AR): $titleAr\n\nDescription (EN): $descEn\nDescription (AR): $descAr";
    }

    // 3. Truncate text to avoid token limits (approx 20k chars)
    if (strlen($text) > 20000) {
        $text = substr($text, 0, 20000) . "\n... [truncated]";
    }

    // Use HEREDOC for cleaner string with quotes
    $prompt = <<<PROMPT
You are a bilingual summarization assistant.
Please summarize the following document content into:
1. A concise paragraph in English (summary_en).
2. A concise paragraph in Arabic (summary_ar).

Document Content:
"""
$text
"""

Return ONLY valid JSON with keys 'summary_en' and 'summary_ar'. Do not include markdown formatting.
PROMPT;

    // 4. Call AI (using 'input' key for LocalAIApi compatibility)
    $resp = LocalAIApi::createResponse([
        'model' => 'gpt-5-mini',
        'input' => [
            ['role' => 'system', 'content' => 'You are a helpful bilingual assistant that outputs JSON.'],
            ['role' => 'user', 'content' => $prompt],
        ]
    ]);

    if (empty($resp['success'])) {
        return ['success' => false, 'message' => 'AI request failed: ' . ($resp['error'] ?? 'Unknown error')];
    }

    $outputText = LocalAIApi::extractText($resp);
    
    // 5. Parse JSON
    $sumEn = '';
    $sumAr = '';

    // Attempt to parse JSON from response
    $jsonStart = strpos($outputText, '{');
    $jsonEnd = strrpos($outputText, '}');
    if ($jsonStart !== false && $jsonEnd !== false) {
        $jsonStr = substr($outputText, $jsonStart, $jsonEnd - $jsonStart + 1);
        $data = json_decode($jsonStr, true);
        if (is_array($data)) {
            $sumEn = $data['summary_en'] ?? '';
            $sumAr = $data['summary_ar'] ?? '';
        }
    }
    
    // Fallback if JSON parsing failed
    if (!$sumEn && !$sumAr) {
        $sumEn = $outputText; // Just dump the text
        $sumAr = '';
    }

    // 6. Update DB
    $stmt = db()->prepare('UPDATE library_documents SET summary_text = ?, summary_en = ?, summary_ar = ? WHERE id = ?');
    $combined = "English: $sumEn\n\nArabic: $sumAr";
    $stmt->execute([$combined, $sumEn, $sumAr, $id]);

    return ['success' => true, 'message' => 'Summary generated successfully from document content.'];
}

// --- Category Functions ---

function library_get_categories(string $search = ''): array
{
    // Backward compatibility
    $result = library_get_categories_paginated($search);
    return $result['data'];
}

function library_get_categories_paginated(string $search = '', int $limit = 0, int $offset = 0): array
{
    library_bootstrap();
    $sql = 'SELECT * FROM library_categories WHERE 1=1';
    $countSql = 'SELECT COUNT(*) FROM library_categories WHERE 1=1';
    
    $params = [];
    if ($search !== '') {
        $clause = ' AND (name_en LIKE ? OR name_ar LIKE ?)';
        $sql .= $clause;
        $countSql .= $clause;
        $params[] = "%$search%";
        $params[] = "%$search%";
    }
    
    $sql .= ' ORDER BY name_en ASC';
    
    if ($limit > 0) {
        $sql .= ' LIMIT ' . (int)$limit . ' OFFSET ' . (int)$offset;
    }
    
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    $data = $stmt->fetchAll() ?: [];
    
    $stmtCount = db()->prepare($countSql);
    $stmtCount->execute($params);
    $total = (int)$stmtCount->fetchColumn();

    return ['data' => $data, 'total' => $total];
}

function library_get_subcategories(?int $categoryId = null, string $search = ''): array
{
    // Backward compatibility
    $result = library_get_subcategories_paginated($categoryId, $search);
    return $result['data'];
}

function library_get_subcategories_paginated(?int $categoryId = null, string $search = '', int $limit = 0, int $offset = 0): array
{
    library_bootstrap();
    $sql = 'SELECT * FROM library_subcategories WHERE 1=1';
    $countSql = 'SELECT COUNT(*) FROM library_subcategories WHERE 1=1';
    $params = [];
    
    if ($categoryId !== null) {
        $clause = ' AND category_id = ?';
        $sql .= $clause;
        $countSql .= $clause;
        $params[] = $categoryId;
    }
    
    if ($search !== '') {
        $clause = ' AND (name_en LIKE ? OR name_ar LIKE ?)';
        $sql .= $clause;
        $countSql .= $clause;
        $params[] = "%$search%";
        $params[] = "%$search%";
    }
    
    $sql .= ' ORDER BY name_en ASC';
    
    if ($limit > 0) {
        $sql .= ' LIMIT ' . (int)$limit . ' OFFSET ' . (int)$offset;
    }
    
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    $data = $stmt->fetchAll() ?: [];
    
    $stmtCount = db()->prepare($countSql);
    $stmtCount->execute($params);
    $total = (int)$stmtCount->fetchColumn();

    return ['data' => $data, 'total' => $total];
}

function library_create_category(string $nameEn, string $nameAr): int
{
    library_bootstrap();
    $stmt = db()->prepare('INSERT INTO library_categories (name_en, name_ar) VALUES (?, ?)');
    $stmt->execute([$nameEn, $nameAr]);
    return (int) db()->lastInsertId();
}

function library_create_subcategory(int $categoryId, string $nameEn, string $nameAr): int
{
    library_bootstrap();
    $stmt = db()->prepare('INSERT INTO library_subcategories (category_id, name_en, name_ar) VALUES (?, ?, ?)');
    $stmt->execute([$categoryId, $nameEn, $nameAr]);
    return (int) db()->lastInsertId();
}

function library_get_category_by_id(int $id): ?array
{
    library_bootstrap();
    $stmt = db()->prepare('SELECT * FROM library_categories WHERE id = ?');
    $stmt->execute([$id]);
    return $stmt->fetch() ?: null;
}

function library_get_subcategory_by_id(int $id): ?array
{
    library_bootstrap();
    $stmt = db()->prepare('SELECT * FROM library_subcategories WHERE id = ?');
    $stmt->execute([$id]);
    return $stmt->fetch() ?: null;
}

function library_update_category(int $id, string $nameEn, string $nameAr): void
{
    library_bootstrap();
    $stmt = db()->prepare('UPDATE library_categories SET name_en = ?, name_ar = ? WHERE id = ?');
    $stmt->execute([$nameEn, $nameAr, $id]);
}

function library_delete_category(int $id): void
{
    library_bootstrap();
    $stmt = db()->prepare('DELETE FROM library_categories WHERE id = ?');
    $stmt->execute([$id]);
}

function library_update_subcategory(int $id, int $categoryId, string $nameEn, string $nameAr): void
{
    library_bootstrap();
    $stmt = db()->prepare('UPDATE library_subcategories SET category_id = ?, name_en = ?, name_ar = ? WHERE id = ?');
    $stmt->execute([$categoryId, $nameEn, $nameAr, $id]);
}

function library_delete_subcategory(int $id): void
{
    library_bootstrap();
    $stmt = db()->prepare('DELETE FROM library_subcategories WHERE id = ?');
    $stmt->execute([$id]);
}

// --- End Category Functions ---

// --- Type Functions ---

function library_get_types(string $search = ''): array
{
    $result = library_get_types_paginated($search);
    return $result['data'];
}

function library_get_types_paginated(string $search = '', int $limit = 0, int $offset = 0): array
{
    library_bootstrap();
    $sql = 'SELECT * FROM library_types WHERE 1=1';
    $countSql = 'SELECT COUNT(*) FROM library_types WHERE 1=1';
    $params = [];
    
    if ($search !== '') {
        $clause = ' AND (name_en LIKE ? OR name_ar LIKE ?)';
        $sql .= $clause;
        $countSql .= $clause;
        $params[] = "%$search%";
        $params[] = "%$search%";
    }
    
    $sql .= ' ORDER BY name_en ASC';
    
    if ($limit > 0) {
        $sql .= ' LIMIT ' . (int)$limit . ' OFFSET ' . (int)$offset;
    }

    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    $data = $stmt->fetchAll() ?: [];
    
    $stmtCount = db()->prepare($countSql);
    $stmtCount->execute($params);
    $total = (int)$stmtCount->fetchColumn();

    return ['data' => $data, 'total' => $total];
}

function library_create_type(string $nameEn, string $nameAr): int
{
    library_bootstrap();
    $stmt = db()->prepare('INSERT INTO library_types (name_en, name_ar) VALUES (?, ?)');
    $stmt->execute([$nameEn, $nameAr]);
    return (int) db()->lastInsertId();
}

function library_get_type_by_id(int $id): ?array
{
    library_bootstrap();
    $stmt = db()->prepare('SELECT * FROM library_types WHERE id = ?');
    $stmt->execute([$id]);
    return $stmt->fetch() ?: null;
}

function library_update_type(int $id, string $nameEn, string $nameAr): void
{
    library_bootstrap();
    $stmt = db()->prepare('UPDATE library_types SET name_en = ?, name_ar = ? WHERE id = ?');
    $stmt->execute([$nameEn, $nameAr, $id]);
}

function library_delete_type(int $id): void
{
    library_bootstrap();
    $stmt = db()->prepare('DELETE FROM library_types WHERE id = ?');
    $stmt->execute([$id]);
}

// --- End Type Functions ---

function library_fetch_documents(bool $publicOnly = false, array $filters = []): array
{
    // Backward compatibility
    $result = library_fetch_documents_paginated($publicOnly, $filters);
    return $result['data'];
}

function library_fetch_documents_paginated(bool $publicOnly = false, array $filters = [], int $limit = 0, int $offset = 0): array
{
    library_bootstrap();

    $sql = 'SELECT d.*, 
            c.name_en as cat_en, c.name_ar as cat_ar, 
            sc.name_en as sub_en, sc.name_ar as sub_ar,
            t.name_en as type_en, t.name_ar as type_ar
            FROM library_documents d 
            LEFT JOIN library_categories c ON d.category_id = c.id
            LEFT JOIN library_subcategories sc ON d.subcategory_id = sc.id
            LEFT JOIN library_types t ON d.type_id = t.id
            WHERE 1=1';
    
    $countSql = 'SELECT COUNT(*) FROM library_documents d 
            LEFT JOIN library_categories c ON d.category_id = c.id
            LEFT JOIN library_subcategories sc ON d.subcategory_id = sc.id
            LEFT JOIN library_types t ON d.type_id = t.id
            WHERE 1=1';
            
    $params = [];

    if ($publicOnly) {
        $clause = ' AND d.visibility = :visibility';
        $sql .= $clause;
        $countSql .= $clause;
        $params[':visibility'] = 'public';
    }

    // Apply Search Filters
    if (!empty($filters['q'])) {
        $q = trim($filters['q']);
        $clause = ' AND (d.title_en LIKE :q OR d.title_ar LIKE :q OR d.author LIKE :q OR d.description_en LIKE :q OR d.description_ar LIKE :q OR d.summary_en LIKE :q)';
        $sql .= $clause;
        $countSql .= $clause;
        $params[':q'] = "%$q%";
    }

    $sql .= ' ORDER BY d.is_featured DESC, d.created_at DESC';
    
    if ($limit > 0) {
        $sql .= ' LIMIT ' . (int)$limit . ' OFFSET ' . (int)$offset;
    }
    
    $stmt = db()->prepare($sql);
    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value);
    }
    $stmt->execute();
    $data = $stmt->fetchAll() ?: [];
    
    $stmtCount = db()->prepare($countSql);
    foreach ($params as $key => $value) {
        $stmtCount->bindValue($key, $value);
    }
    $stmtCount->execute();
    $total = (int)$stmtCount->fetchColumn();

    return ['data' => $data, 'total' => $total];
}

function library_fetch_document(int $id, bool $publicOnly = false): ?array
{
    library_bootstrap();

    $sql = 'SELECT d.*, 
            c.name_en as cat_en, c.name_ar as cat_ar, 
            sc.name_en as sub_en, sc.name_ar as sub_ar,
            t.name_en as type_en, t.name_ar as type_ar
            FROM library_documents d 
            LEFT JOIN library_categories c ON d.category_id = c.id
            LEFT JOIN library_subcategories sc ON d.subcategory_id = sc.id
            LEFT JOIN library_types t ON d.type_id = t.id
            WHERE d.id = :id';
    $params = [':id' => $id];

    if ($publicOnly) {
        $sql .= ' AND d.visibility = :visibility';
        $params[':visibility'] = 'public';
    }

    $stmt = db()->prepare($sql);
    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value);
    }
    $stmt->execute();

    return $stmt->fetch() ?: null;
}

function library_recent_documents(int $limit = 3, bool $publicOnly = false): array
{
    library_bootstrap();

    $sql = 'SELECT * FROM library_documents WHERE 1=1';
    if ($publicOnly) {
        $sql .= ' AND visibility = "public"';
    }
    $sql .= ' ORDER BY created_at DESC LIMIT ' . (int)$limit;

    $stmt = db()->query($sql);
    return $stmt ? $stmt->fetchAll() : [];
}

function library_catalog_metrics(): array
{
    library_bootstrap();

    $sql = 'SELECT
        COUNT(*) AS total_count,
        SUM(CASE WHEN visibility = "public" THEN 1 ELSE 0 END) AS public_count,
        SUM(CASE WHEN visibility = "private" THEN 1 ELSE 0 END) AS private_count,
        SUM(CASE WHEN summary_text IS NOT NULL THEN 1 ELSE 0 END) AS summarized_count
    FROM library_documents';

    $row = db()->query($sql)->fetch() ?: [];

    return [
        'total_count' => (int) ($row['total_count'] ?? 0),
        'public_count' => (int) ($row['public_count'] ?? 0),
        'private_count' => (int) ($row['private_count'] ?? 0),
        'summarized_count' => (int) ($row['summarized_count'] ?? 0),
    ];
}

function library_handle_uploaded_file(array $file): array
{
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
        throw new RuntimeException('Please upload a document file.');
    }

    $originalName = (string) ($file['name'] ?? '');
    $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
    $allowed = library_allowed_extensions();
    if (!isset($allowed[$extension])) {
        throw new RuntimeException('Unsupported file type.');
    }

    $size = (int) ($file['size'] ?? 0);
    if ($size <= 0 || $size > 12 * 1024 * 1024) {
        throw new RuntimeException('File must be smaller than 12 MB.');
    }

    $safeBase = preg_replace('/[^a-zA-Z0-9_-]+/', '-', pathinfo($originalName, PATHINFO_FILENAME)) ?: 'document';
    $storedName = strtolower(date('YmdHis') . '-' . $safeBase . '-' . bin2hex(random_bytes(4)) . '.' . $extension);
    $relativePath = 'uploads/library/' . $storedName;
    $absolutePath = __DIR__ . '/../' . $relativePath;

    if (!move_uploaded_file((string) $file['tmp_name'], $absolutePath)) {
        throw new RuntimeException('Unable to save the uploaded file.');
    }

    return [
        'file_name' => $originalName,
        'file_path' => $relativePath,
        'document_type' => $extension,
        'file_size_kb' => (int) ceil($size / 1024),
    ];
}

function library_handle_cover_image(array $file): ?string
{
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
        return null;
    }

    $originalName = (string) ($file['name'] ?? '');
    $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
    $allowed = ['jpg', 'jpeg', 'png', 'webp', 'gif'];
    if (!in_array($extension, $allowed)) {
        throw new RuntimeException('Unsupported cover image type. Allowed: jpg, png, webp, gif.');
    }

    $size = (int) ($file['size'] ?? 0);
    if ($size <= 0 || $size > 5 * 1024 * 1024) {
        throw new RuntimeException('Cover image must be smaller than 5 MB.');
    }

    $safeBase = preg_replace('/[^a-zA-Z0-9_-]+/', '-', pathinfo($originalName, PATHINFO_FILENAME)) ?: 'cover';
    $storedName = strtolower(date('YmdHis') . '-' . $safeBase . '-' . bin2hex(random_bytes(4)) . '.' . $extension);
    $relativePath = 'uploads/covers/' . $storedName;
    $absolutePath = __DIR__ . '/../' . $relativePath;

    if (!move_uploaded_file((string) $file['tmp_name'], $absolutePath)) {
        throw new RuntimeException('Unable to save the cover image.');
    }

    return $relativePath;
}

function library_create_document(array $payload, array $file, array $coverFile = []): int
{
    library_bootstrap();

    $titleEn = trim((string) ($payload['title_en'] ?? ''));
    $titleAr = trim((string) ($payload['title_ar'] ?? ''));
    
    // Process IDs
    $categoryId = !empty($payload['category_id']) ? (int)$payload['category_id'] : null;
    $subcategoryId = !empty($payload['subcategory_id']) ? (int)$payload['subcategory_id'] : null;
    $typeId = !empty($payload['type_id']) ? (int)$payload['type_id'] : null;
    
    // Fetch names for backward compatibility if needed, or just store IDs
    $categoryName = '';
    $categoryNameAr = '';
    $subName = '';
    $subNameAr = '';

    if ($categoryId) {
        $cat = library_get_category_by_id($categoryId);
        if ($cat) {
            $categoryName = $cat['name_en'];
            $categoryNameAr = $cat['name_ar'];
        }
    }
    if ($subcategoryId) {
        $sub = library_get_subcategory_by_id($subcategoryId);
        if ($sub) {
            $subName = $sub['name_en'];
            $subNameAr = $sub['name_ar'];
        }
    }

    $visibility = (string) ($payload['visibility'] ?? 'public');
    $allow_download = !empty($payload['allow_download']) ? 1 : 0;
    $allow_print = !empty($payload['allow_print']) ? 1 : 0;
    $allow_copy = !empty($payload['allow_copy']) ? 1 : 0;

    // New Fields
    $publisher = trim((string) ($payload['publisher'] ?? ''));
    $publishYear = !empty($payload['publish_year']) ? (int)$payload['publish_year'] : null;
    $author = trim((string) ($payload['author'] ?? ''));
    $country = trim((string) ($payload['country'] ?? ''));
    $pageCount = !empty($payload['page_count']) ? (int)$payload['page_count'] : null;
    $summaryEn = trim((string) ($payload['summary_en'] ?? ''));
    $summaryAr = trim((string) ($payload['summary_ar'] ?? ''));
    $descriptionEn = trim((string) ($payload['description_en'] ?? ''));
    $descriptionAr = trim((string) ($payload['description_ar'] ?? ''));

    $fileData = library_handle_uploaded_file(file: $file);
    $coverPath = library_handle_cover_image($coverFile);

    $stmt = db()->prepare('INSERT INTO library_documents (
        title_en, title_ar, 
        category, category_ar, sub_category, sub_category_ar, 
        category_id, subcategory_id,
        visibility, document_type,
        file_name, file_path, file_size_kb, allow_download, allow_print, allow_copy,
        cover_image_path, publisher, publish_year, author, country, type_id, page_count, summary_en, summary_ar,
        description_en, description_ar
    ) VALUES (
        :title_en, :title_ar, 
        :category, :category__ar, :sub_category, :sub_category_ar, 
        :category_id, :subcategory_id,
        :visibility, :document_type,
        :file_name, :file_path, :file_size_kb, :allow_download, :allow_print, :allow_copy,
        :cover_image_path, :publisher, :publish_year, :author, :country, :type_id, :page_count, :summary_en, :summary_ar,
        :description_en, :description_ar
    )');

    $stmt->execute([
        ':title_en' => $titleEn ?: null,
        ':title_ar' => $titleAr ?: null,
        ':category' => $categoryName ?: null,
        ':category_ar' => $categoryNameAr ?: null,
        ':sub_category' => $subName ?: null,
        ':sub_category_ar' => $subNameAr ?: null,
        ':category_id' => $categoryId,
        ':subcategory_id' => $subcategoryId,
        ':visibility' => $visibility,
        ':document_type' => $fileData['document_type'],
        ':file_name' => $fileData['file_name'],
        ':file_path' => $fileData['file_path'],
        ':file_size_kb' => $fileData['file_size_kb'],
        ':allow_download' => $allow_download,
        ':allow_print' => $allow_print,
        ':allow_copy' => $allow_copy,
        ':cover_image_path' => $coverPath,
        ':publisher' => $publisher ?: null,
        ':publish_year' => $publishYear,
        ':author' => $author ?: null,
        ':country' => $country ?: null,
        ':type_id' => $typeId,
        ':page_count' => $pageCount,
        ':summary_en' => $summaryEn ?: null,
        ':summary_ar' => $summaryAr ?: null,
        ':description_en' => $descriptionEn ?: null,
        ':description_ar' => $descriptionAr ?: null,
    ]);

    return (int) db()->lastInsertId();
}

function library_update_document(int $id, array $payload, array $file = [], array $coverFile = []): void
{
    library_bootstrap();

    // Fetch existing document to keep file if not replaced
    $existing = library_fetch_document($id);
    if (!$existing) {
        throw new RuntimeException('Document not found.');
    }

    $titleEn = trim((string) ($payload['title_en'] ?? ''));
    $titleAr = trim((string) ($payload['title_ar'] ?? ''));
    
    // Process IDs
    $categoryId = !empty($payload['category_id']) ? (int)$payload['category_id'] : null;
    $subcategoryId = !empty($payload['subcategory_id']) ? (int)$payload['subcategory_id'] : null;
    $typeId = !empty($payload['type_id']) ? (int)$payload['type_id'] : null;
    
    $categoryName = '';
    $categoryNameAr = '';
    $subName = '';
    $subNameAr = '';

    if ($categoryId) {
        $cat = library_get_category_by_id($categoryId);
        if ($cat) {
            $categoryName = $cat['name_en'];
            $categoryNameAr = $cat['name_ar'];
        }
    }
    if ($subcategoryId) {
        $sub = library_get_subcategory_by_id($subcategoryId);
        if ($sub) {
            $subName = $sub['name_en'];
            $subNameAr = $sub['name_ar'];
        }
    }

    $visibility = (string) ($payload['visibility'] ?? 'public');
    $allow_download = !empty($payload['allow_download']) ? 1 : 0;
    $allow_print = !empty($payload['allow_print']) ? 1 : 0;
    $allow_copy = !empty($payload['allow_copy']) ? 1 : 0;

    $publisher = trim((string) ($payload['publisher'] ?? ''));
    $publishYear = !empty($payload['publish_year']) ? (int)$payload['publish_year'] : null;
    $author = trim((string) ($payload['author'] ?? ''));
    $country = trim((string) ($payload['country'] ?? ''));
    $pageCount = !empty($payload['page_count']) ? (int)$payload['page_count'] : null;
    $summaryEn = trim((string) ($payload['summary_en'] ?? ''));
    $summaryAr = trim((string) ($payload['summary_ar'] ?? ''));
    $descriptionEn = trim((string) ($payload['description_en'] ?? ''));
    $descriptionAr = trim((string) ($payload['description_ar'] ?? ''));

    // Handle File Update
    $fileData = null;
    if (!empty($file['name'])) {
        $fileData = library_handle_uploaded_file($file);
    }

    // Handle Cover Update
    $coverPath = null;
    if (!empty($coverFile['name'])) {
        $coverPath = library_handle_cover_image($coverFile);
    }

    $sql = 'UPDATE library_documents SET 
        title_en = :title_en, title_ar = :title_ar, 
        category = :category, category_ar = :category_ar, sub_category = :sub_category, sub_category_ar = :sub_category_ar, 
        category_id = :category_id, subcategory_id = :subcategory_id,
        visibility = :visibility,
        allow_download = :allow_download, allow_print = :allow_print, allow_copy = :allow_copy,
        publisher = :publisher, publish_year = :publish_year, author = :author, country = :country, 
        type_id = :type_id, page_count = :page_count, summary_en = :summary_en, summary_ar = :summary_ar,
        description_en = :description_en, description_ar = :description_ar';
    
    $params = [
        ':title_en' => $titleEn ?: null,
        ':title_ar' => $titleAr ?: null,
        ':category' => $categoryName ?: null,
        ':category_ar' => $categoryNameAr ?: null,
        ':sub_category' => $subName ?: null,
        ':sub_category_ar' => $subNameAr ?: null,
        ':category_id' => $categoryId,
        ':subcategory_id' => $subcategoryId,
        ':visibility' => $visibility,
        ':allow_download' => $allow_download,
        ':allow_print' => $allow_print,
        ':allow_copy' => $allow_copy,
        ':publisher' => $publisher ?: null,
        ':publish_year' => $publishYear,
        ':author' => $author ?: null,
        ':country' => $country ?: null,
        ':type_id' => $typeId,
        ':page_count' => $pageCount,
        ':summary_en' => $summaryEn ?: null,
        ':summary_ar' => $summaryAr ?: null,
        ':description_en' => $descriptionEn ?: null,
        ':description_ar' => $descriptionAr ?: null,
        ':id' => $id,
    ];

    if ($fileData) {
        $sql .= ', document_type = :document_type, file_name = :file_name, file_path = :file_path, file_size_kb = :file_size_kb';
        $params[':document_type'] = $fileData['document_type'];
        $params[':file_name'] = $fileData['file_name'];
        $params[':file_path'] = $fileData['file_path'];
        $params[':file_size_kb'] = $fileData['file_size_kb'];
    }

    if ($coverPath) {
        $sql .= ', cover_image_path = :cover_image_path';
        $params[':cover_image_path'] = $coverPath;
    }

    $sql .= ' WHERE id = :id';

    $stmt = db()->prepare($sql);
    $stmt->execute($params);
}

function library_delete_document(int $id): void
{
    library_bootstrap();
    // Optionally delete files, but for safety we might keep them or delete them.
    // For now, just delete DB record.
    $stmt = db()->prepare('DELETE FROM library_documents WHERE id = ?');
    $stmt->execute([$id]);
}

function library_render_pagination(int $currentPage, int $totalPages, string $baseUrl): void
{
    if ($totalPages <= 1) {
        return;
    }
    
    $queryParts = parse_url($baseUrl, PHP_URL_QUERY);
    parse_str($queryParts ?? '', $queryParams);
    
    $buildUrl = function(int $page) use ($baseUrl, $queryParams) {
        $queryParams['page'] = $page;
        $path = parse_url($baseUrl, PHP_URL_PATH);
        return $path . '?' . http_build_query($queryParams);
    };

    echo '<nav aria-label="Page navigation" class="mt-4">';
    echo '<ul class="pagination justify-content-center">';
    
    // Previous
    $prevClass = $currentPage <= 1 ? 'disabled' : '';
    echo '<li class="page-item ' . $prevClass . '">';
    echo '<a class="page-link" href="' . h($buildUrl($currentPage - 1)) . '">' . library_trans('previous') . '</a>';
    echo '</li>';
    
    // Numbers
    $start = max(1, $currentPage - 2);
    $end = min($totalPages, $currentPage + 2);
    
    if ($start > 1) {
        echo '<li class="page-item"><a class="page-link" href="' . h($buildUrl(1)) . '">1</a></li>';
        if ($start > 2) echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
    }
    
    for ($i = $start; $i <= $end; $i++) {
        $active = $i === $currentPage ? 'active' : '';
        echo '<li class="page-item ' . $active . '">';
        echo '<a class="page-link" href="' . h($buildUrl($i)) . '">' . $i . '</a>';
        echo '</li>';
    }
    
    if ($end < $totalPages) {
        if ($end < $totalPages - 1) echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
        echo '<li class="page-item"><a class="page-link" href="' . h($buildUrl($totalPages)) . '">' . $totalPages . '</a></li>';
    }
    
    // Next
    $nextClass = $currentPage >= $totalPages ? 'disabled' : '';
    echo '<li class="page-item ' . $nextClass . '">';
    echo '<a class="page-link" href="' . h($buildUrl($currentPage + 1)) . '">' . library_trans('next') . '</a>';
    echo '</li>';
    
    echo '</ul>';
    echo '<div class="text-center text-secondary small mt-2">' . library_trans('showing_page') . ' ' . $currentPage . ' ' . library_trans('of') . ' ' . $totalPages . '</div>';
    echo '</nav>';
}
