CREATE TABLE IF NOT EXISTS library_documents (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title_en VARCHAR(255) DEFAULT NULL,
    title_ar VARCHAR(255) DEFAULT NULL,
    author_name VARCHAR(255) DEFAULT NULL,
    document_language ENUM('en', 'ar', 'bilingual') NOT NULL DEFAULT 'bilingual',
    visibility ENUM('public', 'private') NOT NULL DEFAULT 'public',
    document_type VARCHAR(50) NOT NULL DEFAULT 'pdf',
    file_name VARCHAR(255) DEFAULT NULL,
    file_path VARCHAR(255) DEFAULT NULL,
    file_size_kb INT UNSIGNED DEFAULT NULL,
    description_en TEXT DEFAULT NULL,
    description_ar TEXT DEFAULT NULL,
    summary_text TEXT DEFAULT NULL,
    tags VARCHAR(255) DEFAULT NULL,
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    view_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_library_visibility_created (visibility, created_at),
    KEY idx_library_language_visibility (document_language, visibility)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;