<?php
// api/news_common.php
// Shared PostgreSQL helpers for Rockview Campus News.

declare(strict_types=1);

require_once __DIR__ . '/session_auth.php';

function rvu_news_json($payload, int $status = 200): void {
    http_response_code($status);
    header('Content-Type: application/json; charset=utf-8');

    // Campus News is a live feed. Do not let browsers, proxies, or Flutter Web
    // reuse a stale GET response after a newer article has been published.
    header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
    header('Pragma: no-cache');
    header('Expires: 0');

    echo json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE);
    exit;
}

function rvu_news_input(): array {
    $raw = file_get_contents('php://input');
    if ($raw === false || trim($raw) === '') return [];
    $decoded = json_decode($raw, true);
    if (!is_array($decoded)) {
        rvu_news_json(['detail' => 'Invalid JSON request body.'], 400);
    }
    return $decoded;
}

function rvu_news_require_user(PDO $pdo): array {
    $user = rvu_current_user($pdo);
    if (!$user) {
        rvu_news_json(['detail' => 'Your session is invalid. Please sign in again.'], 401);
    }
    return $user;
}

function rvu_news_role_is_admin(?string $role): bool {
    $role = strtolower(trim((string)$role));
    return in_array($role, ['admin', 'administrator', 'superadmin', 'super_admin'], true);
}

function rvu_news_can_manage(array $user, int $authorId): bool {
    return (int)$user['id'] === $authorId || rvu_news_role_is_admin($user['role'] ?? '');
}

function rvu_news_bool($value): bool {
    return $value === true || $value === 1 || $value === '1' || $value === 't' || $value === 'true';
}

function rvu_ensure_news_tables(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS campus_news (
        id BIGSERIAL PRIMARY KEY,
        author_id BIGINT NOT NULL,
        title VARCHAR(180) NOT NULL,
        body TEXT NOT NULL,
        category VARCHAR(60) NOT NULL DEFAULT 'General',
        campus VARCHAR(180) NULL,
        school VARCHAR(180) NULL,
        department VARCHAR(180) NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'published',
        is_trending BOOLEAN NOT NULL DEFAULT FALSE,
        trending_notified_at TIMESTAMPTZ NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        published_at TIMESTAMPTZ NULL,
        CONSTRAINT campus_news_status_check CHECK (status IN ('published','draft'))
    )");

    // Keep existing Campus News installations compatible with trending posts.
    $pdo->exec("ALTER TABLE campus_news ADD COLUMN IF NOT EXISTS is_trending BOOLEAN NOT NULL DEFAULT FALSE");
    $pdo->exec("ALTER TABLE campus_news ADD COLUMN IF NOT EXISTS trending_notified_at TIMESTAMPTZ NULL");

    $pdo->exec("CREATE TABLE IF NOT EXISTS campus_news_images (
        id BIGSERIAL PRIMARY KEY,
        news_id BIGINT NOT NULL REFERENCES campus_news(id) ON DELETE CASCADE,
        image_path TEXT NOT NULL,
        thumbnail_path TEXT NULL,
        position INTEGER NOT NULL DEFAULT 0,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    )");

    $pdo->exec("ALTER TABLE campus_news_images ADD COLUMN IF NOT EXISTS thumbnail_path TEXT NULL");

    $pdo->exec("CREATE TABLE IF NOT EXISTS campus_news_videos (
        id BIGSERIAL PRIMARY KEY,
        news_id BIGINT NOT NULL REFERENCES campus_news(id) ON DELETE CASCADE,
        video_path TEXT NOT NULL,
        original_name VARCHAR(255) NOT NULL DEFAULT 'Campus video',
        mime_type VARCHAR(120) NOT NULL DEFAULT 'video/mp4',
        size_bytes BIGINT NOT NULL DEFAULT 0,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT campus_news_videos_one_per_post UNIQUE (news_id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS campus_news_comments (
        id BIGSERIAL PRIMARY KEY,
        news_id BIGINT NOT NULL REFERENCES campus_news(id) ON DELETE CASCADE,
        user_id BIGINT NOT NULL,
        parent_comment_id BIGINT NULL REFERENCES campus_news_comments(id) ON DELETE CASCADE,
        comment TEXT NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    )");

    $pdo->exec("ALTER TABLE campus_news_comments ADD COLUMN IF NOT EXISTS parent_comment_id BIGINT NULL");
    try {
        $pdo->exec("ALTER TABLE campus_news_comments ADD CONSTRAINT campus_news_comments_parent_fk FOREIGN KEY (parent_comment_id) REFERENCES campus_news_comments(id) ON DELETE CASCADE");
    } catch (Throwable $e) {
        // Constraint already exists or legacy data prevents adding it; the column remains usable.
    }

    $pdo->exec("CREATE TABLE IF NOT EXISTS campus_news_likes (
        id BIGSERIAL PRIMARY KEY,
        news_id BIGINT NOT NULL REFERENCES campus_news(id) ON DELETE CASCADE,
        user_id BIGINT NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT campus_news_likes_unique UNIQUE (news_id, user_id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS campus_news_comment_mentions (
        id BIGSERIAL PRIMARY KEY,
        comment_id BIGINT NOT NULL REFERENCES campus_news_comments(id) ON DELETE CASCADE,
        mentioned_user_id BIGINT NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT campus_news_comment_mentions_unique UNIQUE (comment_id, mentioned_user_id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS campus_news_notifications (
        id BIGSERIAL PRIMARY KEY,
        recipient_user_id BIGINT NOT NULL,
        actor_user_id BIGINT NULL,
        news_id BIGINT NULL REFERENCES campus_news(id) ON DELETE CASCADE,
        comment_id BIGINT NULL REFERENCES campus_news_comments(id) ON DELETE CASCADE,
        type VARCHAR(40) NOT NULL DEFAULT 'comment',
        is_read BOOLEAN NOT NULL DEFAULT FALSE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    )");

    // IMPORTANT: CREATE TABLE IF NOT EXISTS does not add new columns to an
    // already-existing legacy table. These ALTER statements make upgrades
    // idempotent and prevent the notification index/query from failing.
    $pdo->exec("ALTER TABLE campus_news_notifications ADD COLUMN IF NOT EXISTS recipient_user_id BIGINT NULL");
    $pdo->exec("ALTER TABLE campus_news_notifications ADD COLUMN IF NOT EXISTS actor_user_id BIGINT NULL");
    $pdo->exec("ALTER TABLE campus_news_notifications ADD COLUMN IF NOT EXISTS news_id BIGINT NULL");
    $pdo->exec("ALTER TABLE campus_news_notifications ADD COLUMN IF NOT EXISTS comment_id BIGINT NULL");
    $pdo->exec("ALTER TABLE campus_news_notifications ADD COLUMN IF NOT EXISTS type VARCHAR(40) NOT NULL DEFAULT 'comment'");
    $pdo->exec("ALTER TABLE campus_news_notifications ADD COLUMN IF NOT EXISTS is_read BOOLEAN NOT NULL DEFAULT FALSE");
    $pdo->exec("ALTER TABLE campus_news_notifications ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP");

    // Migrate common legacy column names when an older notification table exists.
    $notificationColumnsStmt = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = 'campus_news_notifications'");
    $notificationColumns = $notificationColumnsStmt ? $notificationColumnsStmt->fetchAll(PDO::FETCH_COLUMN) : [];

    if (in_array('user_id', $notificationColumns, true)) {
        // Older Rockview notification tables used user_id as the recipient.
        // Keep the data, migrate it to recipient_user_id, and remove the old
        // NOT NULL requirement. Without this, new-style inserts fail with
        // SQLSTATE 23502 because they do not naturally populate legacy user_id.
        $pdo->exec("UPDATE campus_news_notifications SET recipient_user_id = user_id WHERE recipient_user_id IS NULL AND user_id IS NOT NULL");
        try {
            $pdo->exec("ALTER TABLE campus_news_notifications ALTER COLUMN user_id DROP NOT NULL");
        } catch (Throwable $e) {
            // rvu_news_create_notification() below also writes user_id when the
            // legacy column exists, so comments still work even if the database
            // account is not allowed to alter the column constraint.
        }
    } elseif (in_array('recipient_id', $notificationColumns, true)) {
        $pdo->exec("UPDATE campus_news_notifications SET recipient_user_id = recipient_id WHERE recipient_user_id IS NULL");
    }

    if (in_array('actor_id', $notificationColumns, true)) {
        // Legacy notification tables used actor_id. Keep existing data and
        // remove the old NOT NULL requirement because the current schema uses
        // actor_user_id. The insert helper below writes both columns as an
        // additional compatibility measure.
        $pdo->exec("UPDATE campus_news_notifications SET actor_user_id = actor_id WHERE actor_user_id IS NULL AND actor_id IS NOT NULL");
        try {
            $pdo->exec("ALTER TABLE campus_news_notifications ALTER COLUMN actor_id DROP NOT NULL");
        } catch (Throwable $e) {
            // If ALTER permission is restricted, the insert helper still
            // populates actor_id whenever the legacy column exists.
        }
    }

    if (in_array('post_id', $notificationColumns, true)) {
        $pdo->exec("UPDATE campus_news_notifications SET news_id = post_id WHERE news_id IS NULL AND post_id IS NOT NULL");
        try {
            $pdo->exec("ALTER TABLE campus_news_notifications ALTER COLUMN post_id DROP NOT NULL");
        } catch (Throwable $e) {}
    } elseif (in_array('article_id', $notificationColumns, true)) {
        $pdo->exec("UPDATE campus_news_notifications SET news_id = article_id WHERE news_id IS NULL AND article_id IS NOT NULL");
        try {
            $pdo->exec("ALTER TABLE campus_news_notifications ALTER COLUMN article_id DROP NOT NULL");
        } catch (Throwable $e) {}
    }

    if (in_array('notification_type', $notificationColumns, true)) {
        $pdo->exec("UPDATE campus_news_notifications SET type = notification_type WHERE (type IS NULL OR type = 'comment') AND notification_type IS NOT NULL");
        try {
            $pdo->exec("ALTER TABLE campus_news_notifications ALTER COLUMN notification_type DROP NOT NULL");
        } catch (Throwable $e) {}
    }

    // Older installations may constrain notification types to a shorter list.
    // Add the trending type while retaining every existing type already in use.
    $typeConstraintStmt = $pdo->prepare("SELECT pg_get_constraintdef(c.oid)
        FROM pg_constraint c
        JOIN pg_class t ON t.oid = c.conrelid
        JOIN pg_namespace n ON n.oid = t.relnamespace
        WHERE n.nspname = current_schema()
          AND t.relname = 'campus_news_notifications'
          AND c.conname = 'campus_news_notifications_type_check'
          AND c.contype = 'c'");
    $typeConstraintStmt->execute();
    $typeConstraint = $typeConstraintStmt->fetchColumn();
    if (is_string($typeConstraint) && stripos($typeConstraint, 'trending') === false) {
        $typeValuesStmt = $pdo->query("SELECT DISTINCT type FROM campus_news_notifications WHERE type IS NOT NULL");
        $typeValues = $typeValuesStmt ? $typeValuesStmt->fetchAll(PDO::FETCH_COLUMN) : [];
        $typeValues = array_values(array_unique(array_merge(
            array_map('strval', $typeValues),
            ['comment', 'reply', 'mention', 'like', 'trending']
        )));
        $quotedTypeValues = array_map(
            static fn(string $value): string => "'" . str_replace("'", "''", $value) . "'",
            $typeValues
        );
        $pdo->exec('ALTER TABLE campus_news_notifications DROP CONSTRAINT campus_news_notifications_type_check');
        $pdo->exec('ALTER TABLE campus_news_notifications ADD CONSTRAINT campus_news_notifications_type_check CHECK (type IN (' . implode(', ', $quotedTypeValues) . '))');
    }

    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_status_published ON campus_news (status, published_at DESC)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_author ON campus_news (author_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_campus ON campus_news (campus)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_images_news ON campus_news_images (news_id, position)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_videos_news ON campus_news_videos (news_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_comments_news ON campus_news_comments (news_id, created_at)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_comments_parent ON campus_news_comments (parent_comment_id, created_at)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_likes_news ON campus_news_likes (news_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_mentions_user ON campus_news_comment_mentions (mentioned_user_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_campus_news_notifications_recipient ON campus_news_notifications (recipient_user_id, is_read, created_at DESC)");
}

function rvu_news_normalize_row(array $row): array {
    $row['id'] = isset($row['id']) ? (int)$row['id'] : 0;
    $row['author_id'] = isset($row['author_id']) ? (int)$row['author_id'] : 0;
    $row['like_count'] = isset($row['like_count']) ? (int)$row['like_count'] : 0;
    $row['comment_count'] = isset($row['comment_count']) ? (int)$row['comment_count'] : 0;
    $row['liked_by_me'] = rvu_news_bool($row['liked_by_me'] ?? false);
    $row['is_trending'] = rvu_news_bool($row['is_trending'] ?? false);

    if (isset($row['images']) && is_string($row['images'])) {
        $decoded = json_decode($row['images'], true);
        $row['images'] = is_array($decoded) ? $decoded : [];
    } elseif (!isset($row['images']) || !is_array($row['images'])) {
        $row['images'] = [];
    }

    if (isset($row['video']) && is_string($row['video'])) {
        $decoded = json_decode($row['video'], true);
        $row['video'] = is_array($decoded) ? $decoded : null;
    } elseif (!isset($row['video']) || !is_array($row['video'])) {
        $row['video'] = null;
    }

    return $row;
}

function rvu_news_fetch_article(PDO $pdo, int $newsId, int $viewerId): ?array {
    $sql = "SELECT
        n.id, n.author_id, n.title, n.body, n.category, n.campus, n.school, n.department,
        n.status, n.is_trending, n.created_at, n.updated_at, n.published_at,
        u.full_name AS author_name,
        u.role AS author_role,
        u.profile_picture AS author_profile_picture,
        (SELECT COUNT(*) FROM campus_news_likes l WHERE l.news_id = n.id) AS like_count,
        (SELECT COUNT(*) FROM campus_news_comments c WHERE c.news_id = n.id) AS comment_count,
        EXISTS(SELECT 1 FROM campus_news_likes ml WHERE ml.news_id = n.id AND ml.user_id = ?) AS liked_by_me,
        COALESCE((
            SELECT json_agg(
                json_build_object(
                    'id', i.id,
                    'path', i.image_path,
                    'thumbnail_path', COALESCE(i.thumbnail_path, '')
                ) ORDER BY i.position, i.id
            )
            FROM campus_news_images i WHERE i.news_id = n.id
        ), '[]'::json) AS images,
        (
            SELECT json_build_object(
                'id', v.id,
                'path', v.video_path,
                'file_name', v.original_name,
                'mime_type', v.mime_type,
                'size_bytes', v.size_bytes,
                'created_at', v.created_at
            )
            FROM campus_news_videos v
            WHERE v.news_id = n.id
            LIMIT 1
        ) AS video
      FROM campus_news n
      INNER JOIN users u ON u.id = n.author_id
      WHERE n.id = ?
      LIMIT 1";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([$viewerId, $newsId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ? rvu_news_normalize_row($row) : null;
}

function rvu_news_fetch_comment(PDO $pdo, int $commentId): ?array {
    $sql = "SELECT
        c.id, c.news_id, c.user_id, c.parent_comment_id, c.comment, c.created_at,
        u.full_name AS author_name,
        u.role AS author_role,
        u.profile_picture AS author_profile_picture,
        COALESCE(pu.full_name, '') AS reply_to_author_name
      FROM campus_news_comments c
      INNER JOIN users u ON u.id = c.user_id
      LEFT JOIN campus_news_comments pc ON pc.id = c.parent_comment_id
      LEFT JOIN users pu ON pu.id = pc.user_id
      WHERE c.id = ?
      LIMIT 1";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$commentId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$row) return null;
    $row['id'] = (int)$row['id'];
    $row['news_id'] = (int)$row['news_id'];
    $row['user_id'] = (int)$row['user_id'];
    $row['parent_comment_id'] = $row['parent_comment_id'] !== null ? (int)$row['parent_comment_id'] : null;
    return $row;
}

function rvu_news_fetch_comments(PDO $pdo, int $newsId): array {
    $sql = "SELECT
        c.id, c.news_id, c.user_id, c.parent_comment_id, c.comment, c.created_at,
        u.full_name AS author_name,
        u.role AS author_role,
        u.profile_picture AS author_profile_picture,
        COALESCE(pu.full_name, '') AS reply_to_author_name
      FROM campus_news_comments c
      INNER JOIN users u ON u.id = c.user_id
      LEFT JOIN campus_news_comments pc ON pc.id = c.parent_comment_id
      LEFT JOIN users pu ON pu.id = pc.user_id
      WHERE c.news_id = ?
      ORDER BY c.created_at ASC, c.id ASC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$newsId]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    foreach ($rows as &$row) {
        $row['id'] = (int)$row['id'];
        $row['news_id'] = (int)$row['news_id'];
        $row['user_id'] = (int)$row['user_id'];
        $row['parent_comment_id'] = $row['parent_comment_id'] !== null ? (int)$row['parent_comment_id'] : null;
    }
    unset($row);
    return $rows;
}

function rvu_news_create_notification(
    PDO $pdo,
    int $recipientUserId,
    ?int $actorUserId,
    ?int $newsId,
    ?int $commentId,
    string $type
): void {
    if ($recipientUserId <= 0) return;
    if ($actorUserId !== null && $recipientUserId === $actorUserId) return;

    /*
     * Rockview has had more than one notification-table layout over time.
     * Current columns are:
     *   recipient_user_id, actor_user_id, news_id, comment_id, type
     *
     * Older installations may still contain NOT NULL aliases such as:
     *   user_id, actor_id, post_id/article_id, notification_type
     *
     * PostgreSQL validates every NOT NULL column even when it is not named in
     * the INSERT statement. Therefore we populate BOTH the current and legacy
     * aliases whenever those old columns are present. This prevents the
     * sequence of SQLSTATE 23502 errors (first user_id, then actor_id, etc.).
     */
    static $columns = null;
    if ($columns === null) {
    $columnStmt = $pdo->query(
        "SELECT column_name
           FROM information_schema.columns
          WHERE table_schema = current_schema()
            AND table_name = 'campus_news_notifications'"
    );
    $columns = $columnStmt ? $columnStmt->fetchAll(PDO::FETCH_COLUMN) : [];
    }

    $insertColumns = [
        'recipient_user_id',
        'actor_user_id',
        'news_id',
        'comment_id',
        'type',
    ];
    $insertValues = [
        $recipientUserId,
        $actorUserId,
        $newsId,
        $commentId,
        substr($type, 0, 40),
    ];

    if (in_array('user_id', $columns, true)) {
        $insertColumns[] = 'user_id';
        $insertValues[] = $recipientUserId;
    }

    if (in_array('actor_id', $columns, true)) {
        $insertColumns[] = 'actor_id';
        // All user-generated news events have an actor. The recipient fallback
        // is only for a legacy NOT NULL actor_id on a system-generated event.
        $insertValues[] = $actorUserId ?? $recipientUserId;
    }

    if (in_array('post_id', $columns, true)) {
        $insertColumns[] = 'post_id';
        $insertValues[] = $newsId;
    }

    if (in_array('article_id', $columns, true)) {
        $insertColumns[] = 'article_id';
        $insertValues[] = $newsId;
    }

    if (in_array('notification_type', $columns, true)) {
        $insertColumns[] = 'notification_type';
        $insertValues[] = substr($type, 0, 40);
    }

    $placeholders = implode(', ', array_fill(0, count($insertColumns), '?'));
    $sql = 'INSERT INTO campus_news_notifications ('
         . implode(', ', $insertColumns)
         . ') VALUES (' . $placeholders . ')';

    $stmt = $pdo->prepare($sql);
    $stmt->execute($insertValues);
}

/** Notify campus users once when a published article becomes trending. */
function rvu_news_notify_trending(PDO $pdo, int $newsId, int $actorUserId): void {
    $startedTransaction = !$pdo->inTransaction();
    if ($startedTransaction) $pdo->beginTransaction();
    try {
        // The conditional update makes notification delivery idempotent if a
        // queued publish is retried or two requests update the same article.
        $claim = $pdo->prepare("UPDATE campus_news
            SET trending_notified_at = CURRENT_TIMESTAMP
            WHERE id = ? AND status = 'published' AND is_trending = TRUE
              AND trending_notified_at IS NULL
            RETURNING id");
        $claim->execute([$newsId]);
        if ($claim->fetchColumn()) {
            $users = $pdo->prepare('SELECT id FROM users WHERE id <> ? ORDER BY id');
            $users->execute([$actorUserId]);
            while ($recipientId = $users->fetchColumn()) {
                rvu_news_create_notification($pdo, (int)$recipientId, $actorUserId, $newsId, null, 'trending');
            }
        }
        if ($startedTransaction) $pdo->commit();
    } catch (Throwable $e) {
        if ($startedTransaction && $pdo->inTransaction()) $pdo->rollBack();
        throw $e;
    }
}

function rvu_news_fetch_notifications(PDO $pdo, int $recipientUserId, int $limit = 30): array {
    $limit = max(1, min(100, $limit));
    $sql = "SELECT
        n.id, n.news_id, n.comment_id, n.actor_user_id, n.type, n.is_read, n.created_at,
        COALESCE(u.full_name, 'Rockview User') AS actor_name,
        COALESCE(u.profile_picture, '') AS actor_profile_picture,
        COALESCE(news.title, '') AS article_title,
        COALESCE(c.comment, '') AS comment_preview
      FROM campus_news_notifications n
      LEFT JOIN users u ON u.id = n.actor_user_id
      LEFT JOIN campus_news news ON news.id = n.news_id
      LEFT JOIN campus_news_comments c ON c.id = n.comment_id
      WHERE n.recipient_user_id = ?
      ORDER BY n.created_at DESC, n.id DESC
      LIMIT {$limit}";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$recipientUserId]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    foreach ($rows as &$row) {
        $row['id'] = (int)$row['id'];
        $row['news_id'] = $row['news_id'] !== null ? (int)$row['news_id'] : 0;
        $row['comment_id'] = $row['comment_id'] !== null ? (int)$row['comment_id'] : null;
        $row['actor_user_id'] = $row['actor_user_id'] !== null ? (int)$row['actor_user_id'] : null;
        $row['is_read'] = rvu_news_bool($row['is_read']);
    }
    unset($row);
    return $rows;
}

function rvu_news_unread_notification_count(PDO $pdo, int $recipientUserId): int {
    $stmt = $pdo->prepare('SELECT COUNT(*) FROM campus_news_notifications WHERE recipient_user_id = ? AND is_read = FALSE');
    $stmt->execute([$recipientUserId]);
    return (int)$stmt->fetchColumn();
}

function rvu_news_safe_relative_path(string $path): string {
    $path = str_replace('\\', '/', trim($path));
    $path = ltrim($path, '/');
    $parts = [];
    foreach (explode('/', $path) as $part) {
        if ($part === '' || $part === '.') continue;
        if ($part === '..') continue;
        $parts[] = $part;
    }
    return implode('/', $parts);
}

function rvu_news_absolute_media_path(string $relative): ?string {
    $clean = rvu_news_safe_relative_path($relative);
    if ($clean === '' || !str_starts_with($clean, 'uploads/news/')) return null;
    $root = realpath(__DIR__ . '/..');
    if (!$root) return null;
    return $root . DIRECTORY_SEPARATOR . str_replace('/', DIRECTORY_SEPARATOR, $clean);
}

function rvu_news_delete_media_file(string $relative): void {
    $full = rvu_news_absolute_media_path($relative);
    if ($full && is_file($full)) @unlink($full);
}

function rvu_news_download_secret(): string {
    $value = getenv('RVU_DOWNLOAD_SECRET');
    if ($value !== false && trim($value) !== '') return trim($value);
    // Functional local-development fallback. Set RVU_DOWNLOAD_SECRET in production.
    return 'rockview-campus-news-local-download-secret-change-me';
}

function rvu_news_download_signature(int $newsId, string $type, int $expires): string {
    return hash_hmac('sha256', $newsId . '|' . $type . '|' . $expires, rvu_news_download_secret());
}

function rvu_news_base_url(): string {
    $https = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off');
    $scheme = $https ? 'https' : 'http';
    $host = $_SERVER['HTTP_HOST'] ?? '127.0.0.1';
    $script = str_replace('\\', '/', $_SERVER['SCRIPT_NAME'] ?? '/api/news_download_ticket.php');
    $basePath = preg_replace('#/api/[^/]+$#', '', $script);
    return rtrim($scheme . '://' . $host . $basePath, '/');
}
