<?php
/**
 * Helper Functions untuk Multi OPAC Path
 * Path: plugins/multi_opac_path/src/helpers.php
 * Version: 1.1.1 (Fixed Query Join Duplicates & Enhanced Filters)
 */

if (!defined('INDEX_AUTH')) {
    die("Direct access not allowed");
}

use SLiMS\DB;

// ============================================================
// ==================== FUNGSI DASAR (1-9) ====================
// ============================================================

// 1. GET LIBRARY BY SLUG
function getLibraryBySlug($slug)
{
    if (empty($slug)) return null;
    
    $db = DB::getInstance();
    $stmt = $db->prepare("SELECT * FROM mst_library WHERE url_slug = ?");
    $stmt->execute([$slug]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// 2. GET ALL LIBRARIES
function getAllLibraries()
{
    $db = DB::getInstance();
    $stmt = $db->query("SELECT library_id, library_name, url_slug FROM mst_library ORDER BY library_name");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// 3. GET CONTENT BY LIBRARY
function getContentByLibrary($library_id = null, $limit = 10)
{
    $db = DB::getInstance();
    $limitInt = max(1, (int)$limit);
    
    if (!empty($library_id)) {
        $stmt = $db->prepare("
            SELECT * FROM content 
            WHERE library_id = ? 
            ORDER BY content_id DESC 
            LIMIT " . $limitInt
        );
        $stmt->execute([(int)$library_id]);
    } else {
        $stmt = $db->query("
            SELECT * FROM content 
            WHERE library_id IS NULL OR library_id = 0 
            ORDER BY content_id DESC 
            LIMIT " . $limitInt
        );
    }
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// 4. GET BIBLIO BY LIBRARY
function getBibliosByLibrary($library_id = null, $limit = 10)
{
    $db = DB::getInstance();
    $limitInt = max(1, (int)$limit);
    
    if (!empty($library_id)) {
        $check = $db->query("SHOW COLUMNS FROM biblio LIKE 'library_id'");
        if ($check && $check->rowCount() > 0) {
            $stmt = $db->prepare("
                SELECT biblio_id, title, image 
                FROM biblio 
                WHERE library_id = ? 
                ORDER BY biblio_id DESC 
                LIMIT " . $limitInt
            );
            $stmt->execute([(int)$library_id]);
        } else {
            $stmt = $db->query("
                SELECT biblio_id, title, image 
                FROM biblio 
                ORDER BY biblio_id DESC 
                LIMIT " . $limitInt
            );
        }
    } else {
        $stmt = $db->query("
            SELECT biblio_id, title, image 
            FROM biblio 
            ORDER BY biblio_id DESC 
            LIMIT " . $limitInt
        );
    }
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// 5. GET LIBRARIANS BY LIBRARY
function getLibrariansByLibrary($library_id = null, $limit = 50)
{
    $db = DB::getInstance();
    $limitInt = max(1, (int)$limit);
    
    $check = $db->query("SHOW COLUMNS FROM user LIKE 'show_in_opac'");
    $hasShowInOpac = ($check && $check->rowCount() > 0);
    
    $safeUserCondition = "user_id != 1 AND LOWER(username) != 'admin'";
    
    if (!empty($library_id)) {
        if ($hasShowInOpac) {
            $stmt = $db->prepare("
                SELECT user_id, username, realname, email, user_image, user_type, library_id
                FROM user 
                WHERE library_id = ? 
                  AND $safeUserCondition
                  AND show_in_opac = 1
                ORDER BY realname ASC 
                LIMIT " . $limitInt
            );
            $stmt->execute([(int)$library_id]);
        } else {
            $stmt = $db->prepare("
                SELECT user_id, username, realname, email, user_image, user_type, library_id
                FROM user 
                WHERE library_id = ? 
                  AND $safeUserCondition
                ORDER BY realname ASC 
                LIMIT " . $limitInt
            );
            $stmt->execute([(int)$library_id]);
        }
    } else {
        if ($hasShowInOpac) {
            $stmt = $db->query("
                SELECT user_id, username, realname, email, user_image, user_type, library_id
                FROM user 
                WHERE $safeUserCondition
                  AND show_in_opac = 1
                ORDER BY realname ASC 
                LIMIT " . $limitInt
            );
        } else {
            $stmt = $db->query("
                SELECT user_id, username, realname, email, user_image, user_type, library_id
                FROM user 
                WHERE $safeUserCondition
                ORDER BY realname ASC 
                LIMIT " . $limitInt
            );
        }
    }
    
    return $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
}

// 6. GET MULTI OPAC CONFIG
function getMultiOpacConfig()
{
    $db = DB::getInstance();
    $stmt = $db->query("SELECT * FROM multi_opac_config WHERE library_id IS NULL LIMIT 1");
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// 7. UPDATE MULTI OPAC CONFIG
function updateMultiOpacConfig($data)
{
    $db = DB::getInstance();
    $allowedFields = ['auto_create_theme', 'default_theme', 'enable_library_switcher', 'base_path', 'show_library_badge'];
    
    $setParts = [];
    $params = [];
    
    foreach ($data as $key => $value) {
        if (in_array($key, $allowedFields)) {
            $setParts[] = "$key = ?";
            $params[] = $value;
        }
    }
    
    if (empty($setParts)) {
        return false;
    }
    
    $params[] = date('Y-m-d H:i:s');
    $sql = "UPDATE multi_opac_config SET " . implode(', ', $setParts) . ", updated_at = ? WHERE library_id IS NULL";
    $stmt = $db->prepare($sql);
    return $stmt->execute($params);
}

// 8. VALIDATE SLUG
function validateSlug($slug)
{
    if (empty($slug)) return false;
    return preg_match('/^[a-z0-9-]+$/i', $slug);
}

// 9. GET LIBRARY NAME BY ID
function getLibraryNameById($library_id)
{
    if (empty($library_id)) return 'Portal Utama Multi-OPAC';
    
    $db = DB::getInstance();
    $stmt = $db->prepare("SELECT library_name FROM mst_library WHERE library_id = ?");
    $stmt->execute([(int)$library_id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['library_name'] ?? 'Perpustakaan';
}

// ============================================================
// ==================== FUNGSI SESSION (10-11) =================
// ============================================================

// 10. SET LIBRARY SESSION
function setLibrarySession($library_id)
{
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    
    if (!isset($_SESSION['multi_opac'])) {
        $_SESSION['multi_opac'] = [];
    }
    
    if (empty($library_id)) {
        unset($_SESSION['multi_opac']['library_id']);
        unset($_SESSION['multi_opac']['library_slug']);
        return;
    }

    $_SESSION['multi_opac']['library_id'] = $library_id;
    
    $db = DB::getInstance();
    $stmt = $db->prepare("SELECT url_slug FROM mst_library WHERE library_id = ?");
    $stmt->execute([(int)$library_id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        $_SESSION['multi_opac']['library_slug']      = $row['url_slug'];
        $_SESSION['multi_opac']['last_library_slug'] = $row['url_slug'];
    }
}

// 11. GET LIBRARY SESSION
function getLibrarySession()
{
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    return $_SESSION['multi_opac']['library_id'] ?? null;
}

// 11a. GET LIBRARY SLUG SESSION
function getLibrarySlugSession()
{
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    return $_SESSION['multi_opac']['library_slug'] ?? null;
}

// ============================================================
// ==================== FUNGSI TEMA (12-14) ====================
// ============================================================

// 12. SAVE THEME TO DATABASE
function saveTheme($library_id, $theme_data)
{
    $db = DB::getInstance();
    
    $check = $db->query("SHOW TABLES LIKE 'multi_opac_theme_settings'");
    if ($check->rowCount() == 0) {
        $db->exec("
            CREATE TABLE IF NOT EXISTS `multi_opac_theme_settings` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `library_id` int(11) NOT NULL,
                `primary_color` varchar(50) DEFAULT '#007bff',
                `secondary_color` varchar(50) DEFAULT '#6c757d',
                `header_text` varchar(255) DEFAULT NULL,
                `footer_text` varchar(255) DEFAULT NULL,
                `logo` varchar(255) DEFAULT NULL,
                `updated_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`),
                UNIQUE KEY `library_id` (`library_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    }
    
    $stmtCheck = $db->prepare("SELECT logo FROM multi_opac_theme_settings WHERE library_id = ?");
    $stmtCheck->execute([$library_id]);
    $existing = $stmtCheck->fetch(PDO::FETCH_ASSOC);

    $logoToSave = !empty($theme_data['logo']) ? $theme_data['logo'] : ($existing['logo'] ?? null);

    if ($existing) {
        $stmt = $db->prepare("
            UPDATE multi_opac_theme_settings 
            SET primary_color = ?, 
                secondary_color = ?, 
                header_text = ?, 
                footer_text = ?, 
                logo = ?, 
                updated_at = NOW()
            WHERE library_id = ?
        ");
        return $stmt->execute([
            $theme_data['primary_color'],
            $theme_data['secondary_color'],
            $theme_data['header_text'],
            $theme_data['footer_text'],
            $logoToSave,
            $library_id
        ]);
    } else {
        $stmt = $db->prepare("
            INSERT INTO multi_opac_theme_settings 
            (library_id, primary_color, secondary_color, header_text, footer_text, logo, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, NOW())
        ");
        return $stmt->execute([
            $library_id,
            $theme_data['primary_color'],
            $theme_data['secondary_color'],
            $theme_data['header_text'],
            $theme_data['footer_text'],
            $logoToSave
        ]);
    }
}

// 13. GET THEME FROM DATABASE
function getTheme($library_id)
{
    if (empty($library_id)) return null;

    $db = DB::getInstance();
    $check = $db->query("SHOW TABLES LIKE 'multi_opac_theme_settings'");
    if ($check->rowCount() == 0) {
        return null;
    }
    
    $stmt = $db->prepare("SELECT * FROM multi_opac_theme_settings WHERE library_id = ?");
    $stmt->execute([$library_id]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($result) {
        return [
            'primary_color'   => $result['primary_color'] ?? '#007bff',
            'secondary_color' => $result['secondary_color'] ?? '#6c757d',
            'header_text'     => $result['header_text'] ?? '',
            'footer_text'     => $result['footer_text'] ?? '',
            'logo'            => $result['logo'] ?? ''
        ];
    }
    
    return null;
}

// 14. GET ALL THEMES
function getAllThemes()
{
    $db = DB::getInstance();
    $check = $db->query("SHOW TABLES LIKE 'multi_opac_theme_settings'");
    if ($check->rowCount() == 0) {
        return [];
    }
    
    $stmt = $db->query("SELECT * FROM multi_opac_theme_settings ORDER BY library_id");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// ============================================================
// ==================== FUNGSI SEARCH (15) =====================
// ============================================================

// 15. SEARCH BIBLIO BY LIBRARY
function searchBibliosByLibrary($keywords, $library_id = null, $limit = 20, $offset = 0, $filters = [])
{
    $db = DB::getInstance();
    
    $limit = max(1, (int)$limit);
    $offset = max(0, (int)$offset);
    
    $sql = "
        SELECT 
            b.biblio_id, 
            b.title, 
            b.image, 
            b.isbn_issn, 
            b.publish_year,
            b.call_number,
            mp.publisher_name as publisher,
            mg.gmd_name as gmd,
            mc.coll_type_name as colltype,
            GROUP_CONCAT(DISTINCT ma.author_name SEPARATOR ' - ') AS author,
            GROUP_CONCAT(DISTINCT mt.topic SEPARATOR ', ') AS topic
        FROM biblio b
        LEFT JOIN mst_publisher mp ON b.publisher_id = mp.publisher_id
        LEFT JOIN mst_gmd mg ON b.gmd_id = mg.gmd_id
        LEFT JOIN item i ON i.biblio_id = b.biblio_id
        LEFT JOIN mst_coll_type mc ON i.coll_type_id = mc.coll_type_id
        LEFT JOIN biblio_author ba ON ba.biblio_id = b.biblio_id
        LEFT JOIN mst_author ma ON ba.author_id = ma.author_id
        LEFT JOIN biblio_topic bt ON bt.biblio_id = b.biblio_id
        LEFT JOIN mst_topic mt ON bt.topic_id = mt.topic_id
    ";
    
    $where = [];
    $params = [];
    
    if (!empty($keywords)) {
        $kw = '%' . $keywords . '%';
        $where[] = "(b.title LIKE ? OR ma.author_name LIKE ? OR mt.topic LIKE ? OR b.isbn_issn LIKE ? OR b.call_number LIKE ?)";
        $params[] = $kw;
        $params[] = $kw;
        $params[] = $kw;
        $params[] = $kw;
        $params[] = $kw; 
    }
    
    if ($library_id) {
        $where[] = "b.library_id = ?";
        $params[] = (int)$library_id;
    }
    
    if (!empty($filters['year_from'])) {
        $where[] = "b.publish_year >= ?";
        $params[] = (int)$filters['year_from'];
    }
    if (!empty($filters['year_to'])) {
        $where[] = "b.publish_year <= ?";
        $params[] = (int)$filters['year_to'];
    }
    
    if (!empty($filters['colltype'])) {
        $where[] = "i.coll_type_id = ?";
        $params[] = (int)$filters['colltype'];
    }
    
    if (!empty($filters['gmd'])) {
        $where[] = "b.gmd_id = ?";
        $params[] = (int)$filters['gmd'];
    }
    
    if (!empty($filters['location'])) {
        $where[] = "i.location = ?";
        $params[] = $filters['location'];
    }
    
    if (!empty($filters['language'])) {
        $where[] = "b.language_id = ?";
        $params[] = $filters['language'];
    }
    
    if (!empty($where)) {
        $sql .= " WHERE " . implode(" AND ", $where);
    }
    
    $sql .= " GROUP BY b.biblio_id";
    $sql .= " ORDER BY b.biblio_id DESC";
    $sql .= " LIMIT " . $limit . " OFFSET " . $offset;
    
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// ============================================================
// ==================== FUNGSI STATISTIK (16-17) ===============
// ============================================================

// 16. GET POPULAR COLLECTIONS BY LIBRARY
function getPopularCollections($library_id = null, $limit = 5)
{
    $db = DB::getInstance();
    $limit = max(1, (int)$limit);
    $results = [];
    
    static $hasItemId = null;
    static $hasLibraryId = null;
    
    try {
        if ($hasItemId === null) {
            $checkItem = $db->query("SHOW COLUMNS FROM loan LIKE 'item_id'");
            $hasItemId = ($checkItem && $checkItem->rowCount() > 0);
        }
        
        if ($hasLibraryId === null) {
            $checkLib = $db->query("SHOW COLUMNS FROM biblio LIKE 'library_id'");
            $hasLibraryId = ($checkLib && $checkLib->rowCount() > 0);
        }
        
        if ($hasItemId) {
            if ($library_id && $hasLibraryId) {
                $stmt = $db->prepare("
                    SELECT 
                        b.biblio_id, 
                        b.title, 
                        b.image, 
                        COUNT(l.loan_id) as loan_count
                    FROM biblio b
                    LEFT JOIN item i ON b.biblio_id = i.biblio_id
                    LEFT JOIN loan l ON i.item_id = l.item_id
                    WHERE b.library_id = ?
                    GROUP BY b.biblio_id
                    ORDER BY loan_count DESC
                    LIMIT " . $limit
                );
                $stmt->execute([(int)$library_id]);
            } else {
                $stmt = $db->prepare("
                    SELECT 
                        b.biblio_id, 
                        b.title, 
                        b.image, 
                        COUNT(l.loan_id) as loan_count
                    FROM biblio b
                    LEFT JOIN item i ON b.biblio_id = i.biblio_id
                    LEFT JOIN loan l ON i.item_id = l.item_id
                    GROUP BY b.biblio_id
                    ORDER BY loan_count DESC
                    LIMIT " . $limit
                );
                $stmt->execute();
            }
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        } else {
            $stmt = $db->prepare("
                SELECT b.biblio_id, b.title, b.image, COUNT(l.biblio_id) as loan_count
                FROM biblio b
                LEFT JOIN loan l ON b.biblio_id = l.biblio_id
                " . ($library_id && $hasLibraryId ? "WHERE b.library_id = ?" : "") . "
                GROUP BY b.biblio_id
                ORDER BY loan_count DESC
                LIMIT " . $limit
            );
            if ($library_id && $hasLibraryId) {
                $stmt->execute([(int)$library_id]);
            } else {
                $stmt->execute();
            }
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
    } catch (Exception $e) {
        error_log("getPopularCollections error: " . $e->getMessage());
    }
    
    return $results;
}

// 17. GET TOP READERS BY LIBRARY
function getTopReaders($library_id = null, $limit = 5)
{
    $db = DB::getInstance();
    $results = [];
    
    try {
        $checkMember = $db->query("SHOW COLUMNS FROM member LIKE 'library_id'");
        $hasMemberLibraryId = ($checkMember && $checkMember->rowCount() > 0);
        
        if ($library_id && $hasMemberLibraryId) {
            $stmt = $db->prepare("
                SELECT 
                    m.member_id, 
                    m.member_name, 
                    COUNT(l.loan_id) as loan_count
                FROM member m
                LEFT JOIN loan l ON m.member_id = l.member_id
                WHERE m.library_id = ?
                GROUP BY m.member_id
                ORDER BY loan_count DESC
                LIMIT " . intval($limit)
            );
            $stmt->execute([(int)$library_id]);
        } else {
            $stmt = $db->query("
                SELECT 
                    m.member_id, 
                    m.member_name, 
                    COUNT(l.loan_id) as loan_count
                FROM member m
                LEFT JOIN loan l ON m.member_id = l.member_id
                GROUP BY m.member_id
                ORDER BY loan_count DESC
                LIMIT " . intval($limit)
            );
        }
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Exception $e) {
        error_log("getTopReaders error: " . $e->getMessage());
    }
    
    return $results;
}

// ============================================================
// ==================== FUNGSI TAMBAHAN (18-23) ================
// ============================================================

// 18. GET CONTENT BY LIBRARY AND TYPE
function getContentByLibraryAndType($library_id, $type = 'information', $limit = 10)
{
    $db = DB::getInstance();
    $limitInt = max(1, (int)$limit);
    
    $check = $db->query("SHOW COLUMNS FROM content LIKE 'content_type'");
    $hasType = ($check && $check->rowCount() > 0);
    
    if (!empty($library_id)) {
        if ($hasType) {
            $stmt = $db->prepare("
                SELECT * FROM content 
                WHERE library_id = ? 
                  AND content_type = ?
                ORDER BY content_id DESC 
                LIMIT " . $limitInt
            );
            $stmt->execute([(int)$library_id, $type]);
        } else {
            $stmt = $db->prepare("
                SELECT * FROM content 
                WHERE library_id = ?
                  AND (content_title LIKE ? OR content_desc LIKE ?)
                ORDER BY content_id DESC 
                LIMIT " . $limitInt
            );
            $like = '%' . $type . '%';
            $stmt->execute([(int)$library_id, $like, $like]);
        }
    } else {
        if ($hasType) {
            $stmt = $db->prepare("
                SELECT * FROM content 
                WHERE (library_id IS NULL OR library_id = 0) 
                  AND content_type = ?
                ORDER BY content_id DESC 
                LIMIT " . $limitInt
            );
            $stmt->execute([$type]);
        } else {
            $stmt = $db->prepare("
                SELECT * FROM content 
                WHERE (library_id IS NULL OR library_id = 0)
                  AND (content_title LIKE ? OR content_desc LIKE ?)
                ORDER BY content_id DESC 
                LIMIT " . $limitInt
            );
            $like = '%' . $type . '%';
            $stmt->execute([$like, $like]);
        }
    }
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// 19. ADVANCED SEARCH BIBLIOS
function advancedSearchBiblios($params = [], $library_id = null, $limit = 20, $offset = 0)
{
    $db = DB::getInstance();
    
    $sql = "
        SELECT 
            b.biblio_id, 
            b.title, 
            b.image, 
            b.isbn_issn, 
            b.publish_year,
            b.publisher_id,
            b.gmd_id,
            i.coll_type_id,
            b.language_id,
            b.library_id,
            mp.publisher_name as publisher,
            mg.gmd_name as gmd,
            mc.coll_type_name AS colltype,
            GROUP_CONCAT(DISTINCT ma.author_name SEPARATOR ' - ') AS author,
            GROUP_CONCAT(DISTINCT mt.topic SEPARATOR ', ') AS topic
        FROM biblio b
        LEFT JOIN mst_publisher mp ON b.publisher_id = mp.publisher_id
        LEFT JOIN mst_gmd mg ON b.gmd_id = mg.gmd_id
        LEFT JOIN item i ON i.biblio_id = b.biblio_id
        LEFT JOIN mst_coll_type mc ON i.coll_type_id = mc.coll_type_id
        LEFT JOIN biblio_author ba ON ba.biblio_id = b.biblio_id
        LEFT JOIN mst_author ma ON ba.author_id = ma.author_id
        LEFT JOIN biblio_topic bt ON bt.biblio_id = b.biblio_id
        LEFT JOIN mst_topic mt ON bt.topic_id = mt.topic_id
    ";
    
    $where = [];
    $values = [];
    
    if ($library_id) {
        $where[] = "b.library_id = ?";
        $values[] = (int)$library_id;
    }
    
    if (!empty($params['title'])) {
        $where[] = "b.title LIKE ?";
        $values[] = '%' . $params['title'] . '%';
    }
    
    if (!empty($params['author'])) {
        $where[] = "ma.author_name LIKE ?";
        $values[] = '%' . $params['author'] . '%';
    }
    
    if (!empty($params['publisher'])) {
        $where[] = "mp.publisher_name LIKE ?";
        $values[] = '%' . $params['publisher'] . '%';
    }
    
    if (!empty($params['isbn'])) {
        $where[] = "b.isbn_issn LIKE ?";
        $values[] = '%' . $params['isbn'] . '%';
    }
    
    if (!empty($params['year_from'])) {
        $where[] = "b.publish_year >= ?";
        $values[] = (int)$params['year_from'];
    }
    
    if (!empty($params['year_to'])) {
        $where[] = "b.publish_year <= ?";
        $values[] = (int)$params['year_to'];
    }
    
    if (!empty($params['gmd_id'])) {
        $where[] = "b.gmd_id = ?";
        $values[] = (int)$params['gmd_id'];
    }
    
    if (!empty($params['coll_type_id'])) {
        $where[] = "i.coll_type_id = ?";
        $values[] = (int)$params['coll_type_id'];
    }
    
    if (!empty($params['language'])) {
        $where[] = "b.language_id = ?";
        $values[] = $params['language'];
    }
    
    if (!empty($where)) {
        $sql .= " WHERE " . implode(" AND ", $where);
    }
    
    $sql .= " GROUP BY b.biblio_id";
    $sql .= " ORDER BY b.biblio_id DESC";
    $sql .= " LIMIT " . intval($limit) . " OFFSET " . intval($offset);
    
    $stmt = $db->prepare($sql);
    $stmt->execute($values);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// 20. GET FILTER OPTIONS FOR SIDEBAR
function getFilterOptions($library_id = null)
{
    $db = DB::getInstance();
    $options = [];
    
    // 1. Collection Types
    if ($library_id) {
        $stmt = $db->prepare("
            SELECT DISTINCT c.coll_type_id, c.coll_type_name
            FROM mst_coll_type c
            INNER JOIN item i ON i.coll_type_id = c.coll_type_id
            INNER JOIN biblio b ON b.biblio_id = i.biblio_id
            WHERE b.library_id = ?
            ORDER BY c.coll_type_name
        ");
        $stmt->execute([(int)$library_id]);
    } else {
        $stmt = $db->query("SELECT coll_type_id, coll_type_name FROM mst_coll_type ORDER BY coll_type_name");
    }
    $options['colltypes'] = $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
    
    // 2. GMD
    if ($library_id) {
        $stmt = $db->prepare("
            SELECT DISTINCT g.gmd_id, g.gmd_name 
            FROM mst_gmd g
            INNER JOIN biblio b ON b.gmd_id = g.gmd_id
            WHERE b.library_id = ?
            ORDER BY g.gmd_name
        ");
        $stmt->execute([(int)$library_id]);
    } else {
        $stmt = $db->query("SELECT gmd_id, gmd_name FROM mst_gmd ORDER BY gmd_name");
    }
    $options['gmds'] = $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
    
    // 3. Years
    if ($library_id) {
        $stmt = $db->prepare("
            SELECT DISTINCT publish_year 
            FROM biblio 
            WHERE library_id = ? AND publish_year IS NOT NULL AND publish_year != ''
            ORDER BY publish_year DESC
        ");
        $stmt->execute([(int)$library_id]);
    } else {
        $stmt = $db->query("
            SELECT DISTINCT publish_year 
            FROM biblio 
            WHERE publish_year IS NOT NULL AND publish_year != ''
            ORDER BY publish_year DESC
        ");
    }
    $options['years'] = $stmt ? $stmt->fetchAll(PDO::FETCH_COLUMN) : [];
    
    // 4. Languages
    if ($library_id) {
        $stmt = $db->prepare("
            SELECT DISTINCT language_id
            FROM biblio
            WHERE library_id = ? AND language_id IS NOT NULL AND language_id != ''
            ORDER BY language_id
        ");
        $stmt->execute([(int)$library_id]);
    } else {
        $stmt = $db->query("
            SELECT DISTINCT language_id 
            FROM biblio 
            WHERE language_id IS NOT NULL AND language_id != ''
            ORDER BY language_id
        ");
    }
    $options['languages'] = $stmt ? $stmt->fetchAll(PDO::FETCH_COLUMN) : [];
    
    // 5. Locations
    if ($library_id) {
        $stmt = $db->prepare("
            SELECT DISTINCT location 
            FROM item i
            INNER JOIN biblio b ON b.biblio_id = i.biblio_id
            WHERE b.library_id = ? AND location IS NOT NULL AND location != ''
            ORDER BY location
        ");
        $stmt->execute([(int)$library_id]);
    } else {
        $stmt = $db->query("
            SELECT DISTINCT location 
            FROM item 
            WHERE location IS NOT NULL AND location != ''
            ORDER BY location
        ");
    }
    $options['locations'] = $stmt ? $stmt->fetchAll(PDO::FETCH_COLUMN) : [];
    
    return $options;
}

// 21. BUILD LIBRARY URL
function buildLibraryUrl($page = 'multi-opac', $lib_slug = null, $params = [])
{
    if ($lib_slug === null) {
        $lib_slug = getLibrarySlugSession();
    }
    
    $urlParams = ['p' => $page];
    
    if (!empty($lib_slug) && validateSlug($lib_slug)) {
        $urlParams['lib'] = $lib_slug;
    }
    
    if (!empty($params) && is_array($params)) {
        $urlParams = array_merge($urlParams, $params);
    }
    
    return 'index.php?' . http_build_query($urlParams);
}

// 22. GET LIBRARY SLUG FROM ID
function getLibrarySlugById($library_id)
{
    if (empty($library_id)) return null;
    
    $db = DB::getInstance();
    $stmt = $db->prepare("SELECT url_slug FROM mst_library WHERE library_id = ?");
    $stmt->execute([(int)$library_id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['url_slug'] ?? null;
}

// 23. GET CONTENT TYPES
function getContentTypes()
{
    return [
        'information'  => 'Informasi',
        'help'         => 'Bantuan',
        'news'         => 'Berita',
        'announcement' => 'Pengumuman',
        'librarian'    => 'Pustakawan'
    ];
}

// ============================================================
// ==================== FUNGSI UTILITY (24-25) ================
// ============================================================

// 24. GENERATE CSRF TOKEN
function generateCsrfToken()
{
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    
    if (empty($_SESSION['csrf_token'])) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
    }
    
    return $_SESSION['csrf_token'];
}

// 25. VALIDATE CSRF TOKEN
function validateCsrfToken($token)
{
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    
    return !empty($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $token);
}

// ============================================================
// ==================== FUNGSI TOPIC (26) =====================
// ============================================================

/**
 * 26. GET TOP TOPICS BY LIBRARY
 * Mengambil daftar topik/subjek terbanyak berdasarkan perpustakaan
 */
function getTopTopicsByLibrary($library_id = null, $limit = 10)
{
    $db = DB::getInstance();
    $limit = max(1, (int)$limit);
    
    try {
        if ($library_id) {
            $stmt = $db->prepare("
                SELECT mt.topic_id, mt.topic, COUNT(bt.biblio_id) as total
                FROM mst_topic mt
                INNER JOIN biblio_topic bt ON bt.topic_id = mt.topic_id
                INNER JOIN biblio b ON b.biblio_id = bt.biblio_id
                WHERE b.library_id = ?
                GROUP BY mt.topic_id, mt.topic
                ORDER BY total DESC
                LIMIT " . $limit
            );
            $stmt->execute([(int)$library_id]);
        } else {
            $stmt = $db->query("
                SELECT mt.topic_id, mt.topic, COUNT(bt.biblio_id) as total
                FROM mst_topic mt
                INNER JOIN biblio_topic bt ON bt.topic_id = mt.topic_id
                GROUP BY mt.topic_id, mt.topic
                ORDER BY total DESC
                LIMIT " . $limit
            );
        }
        return $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
    } catch (Exception $e) {
        error_log("getTopTopicsByLibrary error: " . $e->getMessage());
        return [];
    }
}