<?php
// IndexGram — General helper functions

// ── Settings ──────────────────────────────────────────────────────────────────
function get_setting(string $key, string $default = ''): string {
    static $cache = [];
    if (!isset($cache[$key])) {
        $stmt = db()->prepare("SELECT value FROM settings WHERE key = ?");
        $stmt->execute([$key]);
        $row = $stmt->fetch();
        $cache[$key] = ($row !== false) ? (string)$row['value'] : $default;
    }
    return $cache[$key];
}

function set_setting(string $key, string $value): void {
    db()->prepare("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)")
        ->execute([$key, $value]);
}

function get_all_settings(): array {
    $rows = db()->query("SELECT key, value FROM settings")->fetchAll();
    $out  = [];
    foreach ($rows as $r) $out[$r['key']] = $r['value'];
    return $out;
}

// ── URL helpers ────────────────────────────────────────────────────────────────
function base_url(string $path = ''): string {
    static $base = '';
    if ($base === '') {
        try { $b = get_setting('base_url', ''); }
        catch (Exception $e) { $b = ''; }
        $base = rtrim($b ?: AUTO_BASE_URL, '/');
    }
    return $base . '/' . ltrim($path, '/');
}

function topic_url(string $topicSlug): string {
    return base_url('topic/' . rawurlencode($topicSlug));
}

function doc_url(string $topicSlug, string $docSlug): string {
    return base_url('topic/' . rawurlencode($topicSlug) . '/' . rawurlencode($docSlug));
}

function print_url(string $topicSlug, string $docSlug): string {
    return base_url('print/' . rawurlencode($topicSlug) . '/' . rawurlencode($docSlug));
}

function rss_url(string $topicSlug = ''): string {
    return $topicSlug ? base_url('rss/' . rawurlencode($topicSlug)) : base_url('rss');
}

function upload_url(string $file, string $type = 'images'): string {
    return base_url('uploads/' . $type . '/' . rawurlencode($file));
}

// ── Slug ───────────────────────────────────────────────────────────────────────
function make_slug(string $text): string {
    $text = strtolower(trim($text));
    $text = preg_replace('/[^a-z0-9]+/', '-', $text);
    return trim($text, '-');
}

// ── Date ───────────────────────────────────────────────────────────────────────
function fmt_date(string $dt, bool $time = false): string {
    $ts = strtotime($dt);
    return $ts ? date($time ? 'F j, Y g:i A' : 'F j, Y', $ts) : $dt;
}

// ── Topics ─────────────────────────────────────────────────────────────────────
/** Return all topics or those at a specific level (null = top-level only). */
function get_topics(?int $parentId = null, bool $all = false): array {
    if ($all) {
        return db()->query("SELECT * FROM topics ORDER BY sort_order ASC, title ASC")->fetchAll();
    }
    if ($parentId === null) {
        return db()->query("SELECT * FROM topics WHERE parent_id IS NULL ORDER BY sort_order ASC, title ASC")->fetchAll();
    }
    $stmt = db()->prepare("SELECT * FROM topics WHERE parent_id = ? ORDER BY sort_order ASC, title ASC");
    $stmt->execute([$parentId]);
    return $stmt->fetchAll();
}

/** Return direct sub-topics of a topic. */
function get_subtopics(int $parentId): array {
    $stmt = db()->prepare("SELECT * FROM topics WHERE parent_id = ? ORDER BY sort_order ASC, title ASC");
    $stmt->execute([$parentId]);
    return $stmt->fetchAll();
}

/** Return topic depth: 1 = top-level, 2 = sub-topic, 3 = sub-sub-topic. */
function get_topic_depth(int $topicId): int {
    $t = get_topic_by_id($topicId);
    if (!$t || $t['parent_id'] === null) return 1;
    $p = get_topic_by_id((int)$t['parent_id']);
    if (!$p || $p['parent_id'] === null) return 2;
    return 3;
}

/** Return ancestor chain [root, ..., parent] for breadcrumbs. */
function get_topic_ancestors(int $topicId): array {
    $ancestors = [];
    $current   = get_topic_by_id($topicId);
    while ($current && $current['parent_id'] !== null) {
        $parent = get_topic_by_id((int)$current['parent_id']);
        if ($parent) array_unshift($ancestors, $parent);
        $current = $parent;
    }
    return $ancestors;
}

function get_topic(string $slug): ?array {
    $stmt = db()->prepare("SELECT * FROM topics WHERE slug = ?");
    $stmt->execute([$slug]);
    return $stmt->fetch() ?: null;
}

function get_topic_by_id(int $id): ?array {
    static $cache = [];
    if (!isset($cache[$id])) {
        $stmt = db()->prepare("SELECT * FROM topics WHERE id = ?");
        $stmt->execute([$id]);
        $cache[$id] = $stmt->fetch() ?: null;
    }
    return $cache[$id];
}

// ── Documents ──────────────────────────────────────────────────────────────────
function get_documents(int $topicId, string $status = 'published'): array {
    if ($status === 'all') {
        $stmt = db()->prepare(
            "SELECT d.*, u.username AS author_name FROM documents d
             LEFT JOIN users u ON d.author_id = u.id
             WHERE d.topic_id = ? ORDER BY d.updated_at DESC"
        );
        $stmt->execute([$topicId]);
    } else {
        $stmt = db()->prepare(
            "SELECT d.*, u.username AS author_name FROM documents d
             LEFT JOIN users u ON d.author_id = u.id
             WHERE d.topic_id = ? AND d.status = ? ORDER BY d.updated_at DESC"
        );
        $stmt->execute([$topicId, $status]);
    }
    return $stmt->fetchAll();
}

function get_document(int $topicId, string $slug): ?array {
    $stmt = db()->prepare(
        "SELECT d.*, u.username AS author_name, t.slug AS topic_slug, t.title AS topic_title
         FROM documents d
         LEFT JOIN users u ON d.author_id = u.id
         LEFT JOIN topics t ON d.topic_id = t.id
         WHERE d.topic_id = ? AND d.slug = ?"
    );
    $stmt->execute([$topicId, $slug]);
    return $stmt->fetch() ?: null;
}

function get_document_by_id(int $id): ?array {
    $stmt = db()->prepare(
        "SELECT d.*, u.username AS author_name, t.slug AS topic_slug, t.title AS topic_title
         FROM documents d
         LEFT JOIN users u ON d.author_id = u.id
         LEFT JOIN topics t ON d.topic_id = t.id
         WHERE d.id = ?"
    );
    $stmt->execute([$id]);
    return $stmt->fetch() ?: null;
}

function get_recent_documents(int $limit = 10, string $status = 'published'): array {
    $stmt = db()->prepare(
        "SELECT d.*, t.slug AS topic_slug, t.title AS topic_title
         FROM documents d JOIN topics t ON d.topic_id = t.id
         WHERE d.status = ? ORDER BY d.updated_at DESC LIMIT ?"
    );
    $stmt->execute([$status, $limit]);
    return $stmt->fetchAll();
}

// ── Revisions ──────────────────────────────────────────────────────────────────
function get_revisions(int $documentId): array {
    $stmt = db()->prepare(
        "SELECT r.*, u.username FROM document_revisions r
         LEFT JOIN users u ON r.changed_by = u.id
         WHERE r.document_id = ? ORDER BY r.created_at DESC"
    );
    $stmt->execute([$documentId]);
    return $stmt->fetchAll();
}

function save_revision(int $documentId, string $title, string $content, int $changedBy, string $note = ''): void {
    db()->prepare(
        "INSERT INTO document_revisions (document_id, title, content, changed_by, change_note)
         VALUES (?,?,?,?,?)"
    )->execute([$documentId, $title, $content, $changedBy, $note]);
}

// ── Search Index (FTS5) ────────────────────────────────────────────────────────
function fts5_available(): bool {
    static $avail = null;
    if ($avail === null) {
        $avail = get_setting('fts5_enabled', '0') === '1';
    }
    return $avail;
}

/** Build a safe FTS5 MATCH query from raw user input. */
function fts5_query(string $q): string {
    // Strip FTS5 special characters; split into words; prefix-match each
    $q     = preg_replace('/[^\p{L}\p{N}\s]/u', ' ', $q);
    $words = array_filter(preg_split('/\s+/u', trim($q)));
    if (empty($words)) return '""';
    return implode(' ', array_map(fn($w) => '"' . str_replace('"', '', $w) . '"*', $words));
}

/** Update (or insert) a document's entry in the FTS5 search index. */
function update_search_index(int $docId): void {
    if (!fts5_available()) return;
    $d = db()->prepare("SELECT id, title, meta_description, content, status FROM documents WHERE id = ?");
    $d->execute([$docId]);
    $row = $d->fetch();
    if (!$row) return;
    db()->prepare("DELETE FROM search_index WHERE document_id = ?")->execute([$docId]);
    db()->prepare(
        "INSERT INTO search_index (title, meta_description, content, document_id, status) VALUES (?,?,?,?,?)"
    )->execute([$row['title'], $row['meta_description'], $row['content'], $row['id'], $row['status']]);
}

/** Remove a document from the FTS5 search index. */
function delete_from_search_index(int $docId): void {
    if (!fts5_available()) return;
    db()->prepare("DELETE FROM search_index WHERE document_id = ?")->execute([$docId]);
}

/** Rebuild the entire FTS5 search index from scratch. */
function rebuild_search_index(): void {
    if (!fts5_available()) return;
    db()->exec("DELETE FROM search_index");
    $rows = db()->query("SELECT id, title, meta_description, content, status FROM documents")->fetchAll();
    $ins  = db()->prepare("INSERT INTO search_index (title, meta_description, content, document_id, status) VALUES (?,?,?,?,?)");
    foreach ($rows as $r) {
        $ins->execute([$r['title'], $r['meta_description'], $r['content'], $r['id'], $r['status']]);
    }
}

// ── Audience Tags feature flag ─────────────────────────────────────────────────
function audience_tags_enabled(): bool {
    return get_setting('audience_tags_enabled', '1') === '1';
}

// ── Tags (Audience) ────────────────────────────────────────────────────────────
function get_tags(): array {
    return db()->query("SELECT * FROM tags ORDER BY name ASC")->fetchAll();
}

function get_document_tags(int $documentId): array {
    $stmt = db()->prepare(
        "SELECT t.* FROM tags t JOIN document_tags dt ON t.id = dt.tag_id WHERE dt.document_id = ?"
    );
    $stmt->execute([$documentId]);
    return $stmt->fetchAll();
}

function set_document_tags(int $documentId, array $tagIds): void {
    db()->prepare("DELETE FROM document_tags WHERE document_id = ?")->execute([$documentId]);
    $ins = db()->prepare("INSERT OR IGNORE INTO document_tags (document_id, tag_id) VALUES (?, ?)");
    foreach ($tagIds as $tid) { $ins->execute([$documentId, (int)$tid]); }
}

// ── Generic Tags ───────────────────────────────────────────────────────────────
function get_generic_tags(): array {
    return db()->query("SELECT * FROM generic_tags ORDER BY name ASC")->fetchAll();
}

function get_document_generic_tags(int $documentId): array {
    $stmt = db()->prepare(
        "SELECT gt.* FROM generic_tags gt
         JOIN document_generic_tags dgt ON gt.id = dgt.tag_id
         WHERE dgt.document_id = ?"
    );
    $stmt->execute([$documentId]);
    return $stmt->fetchAll();
}

function set_document_generic_tags(int $documentId, array $tagIds): void {
    db()->prepare("DELETE FROM document_generic_tags WHERE document_id = ?")->execute([$documentId]);
    $ins = db()->prepare("INSERT OR IGNORE INTO document_generic_tags (document_id, tag_id) VALUES (?, ?)");
    foreach ($tagIds as $tid) { $ins->execute([$documentId, (int)$tid]); }
}

// ── Themes ─────────────────────────────────────────────────────────────────────
function get_active_theme(): ?array {
    return db()->query("SELECT * FROM themes WHERE is_active = 1 LIMIT 1")->fetch() ?: null;
}

function get_all_themes(): array {
    return db()->query("SELECT id, name, is_active, created_at FROM themes ORDER BY name ASC")->fetchAll();
}

function activate_theme(int $id): void {
    db()->exec("UPDATE themes SET is_active = 0");
    db()->prepare("UPDATE themes SET is_active = 1 WHERE id = ?")->execute([$id]);
    set_setting('active_theme', (string)$id);
}

// ── Page view tracking ──────────────────────────────────────────────────────────

/** Known bot UA fragments — checked case-insensitively. */
const BOT_PATTERNS = [
    'bot','crawler','spider','slurp','baidu','yandex','duckduck','sogou',
    'exabot','facebot','ia_archiver','archive.org','semrush','ahrefs',
    'mj12bot','dotbot','rogerbot','screaming frog','lighthouse','pagespeed',
    'headlesschrome','phantomjs','wget','libwww','python-requests','python-urllib',
    'curl','go-http','java/','okhttp','axios','node-fetch','scrapy',
];

function is_bot(): bool {
    $ua = strtolower($_SERVER['HTTP_USER_AGENT'] ?? '');
    if ($ua === '') return true; // no UA = treat as bot
    foreach (BOT_PATTERNS as $p) {
        if (str_contains($ua, $p)) return true;
    }
    return false;
}

/**
 * Record a human page view for a document.
 * Uses IP hash + date for unique tracking (privacy-safe, no raw IP stored).
 */
function record_page_view(int $docId): void {
    if (is_bot()) return;
    $ipHash = hash('sha256', ($_SERVER['REMOTE_ADDR'] ?? 'unknown') . date('Y-m-d'));
    $date   = date('Y-m-d');
    db()->prepare(
        "INSERT INTO page_views (doc_id, ip_hash, viewed_date) VALUES (?, ?, ?)"
    )->execute([$docId, $ipHash, $date]);
}

function get_doc_hit_stats(int $docId): array {
    $row = db()->prepare(
        "SELECT COUNT(*) AS total, COUNT(DISTINCT ip_hash || viewed_date) AS unique_hits
         FROM page_views WHERE doc_id = ?"
    );
    $row->execute([$docId]);
    return $row->fetch() ?: ['total' => 0, 'unique_hits' => 0];
}

function get_site_hit_totals(): array {
    $row = db()->query(
        "SELECT COUNT(*) AS total, COUNT(DISTINCT ip_hash || viewed_date || doc_id) AS unique_hits
         FROM page_views"
    )->fetch();
    return $row ?: ['total' => 0, 'unique_hits' => 0];
}

/** Returns published documents ranked by total hits, with hit stats attached. */
function get_docs_by_hits(int $limit = 50): array {
    $stmt = db()->prepare(
        "SELECT d.id, d.title, d.slug, d.status,
                t.slug AS topic_slug, t.title AS topic_title,
                COUNT(pv.id) AS total_hits,
                COUNT(DISTINCT pv.ip_hash || pv.viewed_date) AS unique_hits
         FROM documents d
         JOIN topics t ON d.topic_id = t.id
         LEFT JOIN page_views pv ON pv.doc_id = d.id
         WHERE d.status = 'published'
         GROUP BY d.id
         ORDER BY total_hits DESC
         LIMIT ?"
    );
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}

// ── Output helpers ─────────────────────────────────────────────────────────────
function h(string $s): string { return htmlspecialchars($s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8'); }

function active_theme_css_url(): string {
    return base_url('theme.css.php');
}

// Build meta tags array from settings + page overrides.
// All string values are stored as raw text — HTML-encoding happens only in the template.
function build_meta(array $overrides = []): array {
    return array_merge([
        'title'       => get_setting('site_title', SITE_NAME),
        'description' => get_setting('site_description'),
        'keywords'    => get_setting('site_keywords'),
        'author'      => get_setting('site_author', SITE_NAME),
        'og_title'    => get_setting('og_title') ?: get_setting('site_title', SITE_NAME),
        'og_desc'     => get_setting('og_description') ?: get_setting('site_description'),
        'og_image'    => get_setting('og_image'),   // filename in uploads/og/
        'og_type'     => get_setting('og_type', 'website'),
        'og_url'      => '',   // empty = auto-detect from REQUEST_URI in header.php
        'favicon'     => get_setting('favicon'),
        'logo'        => get_setting('logo'),       // filename in uploads/images/
        'footer'      => get_setting('footer_text'),
        'base_url'    => base_url(),
    ], $overrides);
}

// ── Users ──────────────────────────────────────────────────────────────────────
function get_users(): array {
    return db()->query(
        "SELECT u.*, i.token AS invite_token FROM users u
         LEFT JOIN invites i ON i.used_by = u.id ORDER BY u.created_at DESC"
    )->fetchAll();
}

function create_user(string $username, string $email, string $password, string $role, ?int $invitedBy): int {
    $hash = password_hash($password, PASSWORD_DEFAULT);
    db()->prepare(
        "INSERT INTO users (username, email, password_hash, role, invited_by) VALUES (?,?,?,?,?)"
    )->execute([$username, $email, $hash, $role, $invitedBy]);
    return (int)db()->lastInsertId();
}

// ── Invites ────────────────────────────────────────────────────────────────────
function get_invite(string $token): ?array {
    $stmt = db()->prepare(
        "SELECT i.*, u.username AS creator_name FROM invites i
         LEFT JOIN users u ON i.created_by = u.id WHERE i.token = ?"
    );
    $stmt->execute([$token]);
    return $stmt->fetch() ?: null;
}

function validate_invite(array $invite, string $email = ''): bool {
    if ($invite['use_count'] >= $invite['max_uses']) return false;
    if ($invite['expires_at'] && strtotime($invite['expires_at']) < time()) return false;
    if (!empty($invite['email']) && strtolower($invite['email']) !== strtolower($email)) return false;
    return true;
}

function use_invite(string $token, int $userId): void {
    db()->prepare(
        "UPDATE invites SET use_count = use_count + 1, used_by = ?, used_at = CURRENT_TIMESTAMP WHERE token = ?"
    )->execute([$userId, $token]);
}

// ── Static pages ───────────────────────────────────────────────────────────────
function get_page(string $slug): ?array {
    $stmt = db()->prepare("SELECT * FROM pages WHERE slug = ?");
    $stmt->execute([$slug]);
    return $stmt->fetch() ?: null;
}

function get_all_pages(): array {
    return db()->query("SELECT * FROM pages ORDER BY sort_order ASC, id ASC")->fetchAll();
}

function get_nav_pages(): array {
    return db()->query(
        "SELECT * FROM pages WHERE enabled = 1 AND show_in_nav = 1 AND (parent_id IS NULL OR parent_id = 0) ORDER BY COALESCE(nav_position,999999) ASC, sort_order ASC, id ASC"
    )->fetchAll();
}

/** Returns all top-level topics and nav-visible top-level pages merged and sorted
 *  by nav_position (nulls last). Each item has an extra 'item_type' key: 'topic'|'page'. */
function get_nav_items(): array {
    $topics = db()->query(
        "SELECT *, 'topic' AS item_type FROM topics WHERE parent_id IS NULL ORDER BY COALESCE(nav_position,999999) ASC, sort_order ASC, title ASC"
    )->fetchAll();
    $pages = db()->query(
        "SELECT *, 'page' AS item_type FROM pages WHERE enabled=1 AND show_in_nav=1 AND (parent_id IS NULL OR parent_id=0) AND slug != 'landing' ORDER BY COALESCE(nav_position,999999) ASC, sort_order ASC, id ASC"
    )->fetchAll();
    $items = array_merge($topics, $pages);
    usort($items, function ($a, $b) {
        $ap = ($a['nav_position'] !== null && $a['nav_position'] !== '') ? (int)$a['nav_position'] : PHP_INT_MAX;
        $bp = ($b['nav_position'] !== null && $b['nav_position'] !== '') ? (int)$b['nav_position'] : PHP_INT_MAX;
        return $ap !== $bp ? $ap - $bp : (int)($a['sort_order'] ?? 0) - (int)($b['sort_order'] ?? 0);
    });
    return $items;
}

function get_page_by_id(int $id): ?array {
    static $cache = [];
    if (!array_key_exists($id, $cache)) {
        $stmt = db()->prepare("SELECT * FROM pages WHERE id = ?");
        $stmt->execute([$id]);
        $cache[$id] = $stmt->fetch() ?: null;
    }
    return $cache[$id];
}

function get_subpages(int $parentId, bool $allStatuses = false): array {
    if ($allStatuses) {
        $stmt = db()->prepare("SELECT * FROM pages WHERE parent_id = ? ORDER BY sort_order ASC, id ASC");
    } else {
        // Returns all enabled sub-pages; caller filters show_in_nav for nav context
        $stmt = db()->prepare("SELECT * FROM pages WHERE parent_id = ? AND enabled = 1 ORDER BY sort_order ASC, id ASC");
    }
    $stmt->execute([$parentId]);
    return $stmt->fetchAll();
}

// ── Donation ───────────────────────────────────────────────────────────────────
function donate_enabled(): bool {
    if (get_setting('donate_enabled', '0') !== '1') return false;
    return get_setting('donate_paypal_link') !== ''
        || get_setting('donate_paypal_email') !== '';
}

function donate_url(): string {
    // Direct link takes priority
    $direct = get_setting('donate_paypal_link');
    if ($direct !== '') return $direct;
    // Fall back to email-based PayPal donate URL
    $email    = rawurlencode(get_setting('donate_paypal_email'));
    $currency = rawurlencode(get_setting('donate_currency', 'USD'));
    $name     = rawurlencode(get_setting('site_title', 'Donate'));
    $amount   = get_setting('donate_amount');
    $url      = 'https://www.paypal.com/donate/?business=' . $email
              . '&currency_code=' . $currency
              . '&item_name=' . $name;
    if ($amount !== '') $url .= '&amount=' . rawurlencode($amount);
    return $url;
}

// ── Nav links ──────────────────────────────────────────────────────────────────
function get_nav_links(): array {
    return db()->query("SELECT * FROM nav_links ORDER BY sort_order ASC, id ASC")->fetchAll();
}

// ── Subscribe helpers ──────────────────────────────────────────────────────────
function subscribe_enabled(): bool {
    return get_setting('subscribe_enabled', '0') === '1';
}

function subscribe_optional_fields(): array {
    $raw = get_setting('subscribe_fields', '');
    return $raw ? array_filter(array_map('trim', explode(',', $raw))) : [];
}

// ── Access control ─────────────────────────────────────────────────────────────
function get_document_by_access_token(string $token): ?array {
    if (!$token) return null;
    $stmt = db()->prepare(
        "SELECT d.*, u.username AS author_name, t.slug AS topic_slug, t.title AS topic_title
         FROM documents d
         LEFT JOIN users u ON d.author_id = u.id
         LEFT JOIN topics t ON d.topic_id = t.id
         WHERE d.access_token = ? AND d.status = 'published'"
    );
    $stmt->execute([$token]);
    return $stmt->fetch() ?: null;
}

// ── Contributor Topic Restrictions ────────────────────────────────────────────
/** Returns topic IDs the contributor is restricted to (empty array = all topics allowed). */
function get_contributor_topic_ids(int $userId): array {
    $stmt = db()->prepare("SELECT topic_id FROM contributor_topic_restrictions WHERE user_id = ?");
    $stmt->execute([$userId]);
    return array_map('intval', array_column($stmt->fetchAll(), 'topic_id'));
}

function set_contributor_topic_ids(int $userId, array $topicIds): void {
    db()->prepare("DELETE FROM contributor_topic_restrictions WHERE user_id = ?")->execute([$userId]);
    $ins = db()->prepare("INSERT OR IGNORE INTO contributor_topic_restrictions (user_id, topic_id) VALUES (?,?)");
    foreach ($topicIds as $tid) { $ins->execute([$userId, (int)$tid]); }
}

/** Returns true if the contributor can post to the given topic (no restrictions = all allowed). */
function contributor_can_post_to_topic(int $userId, int $topicId): bool {
    $allowed = get_contributor_topic_ids($userId);
    return empty($allowed) || in_array($topicId, $allowed, true);
}