This script has to be run as an administrator at the nextcloud server and will export the starred articles of a nextcloud user to a markdown document.

#!/bin/bash

# Script to query starred news articles from Nextcloud database and format as markdown

set -euo pipefail

# Set UTF-8 encoding to handle international characters properly
export LC_ALL=C.UTF-8
export LANG=C.UTF-8

# Function to display usage
usage() {
    echo "Usage: $0 [OPTIONS]"
    echo "Options:"
    echo "  -u, --user USER     Specify the user ID (required)"
    echo "  -h, --help          Show this help message"
    echo ""
    echo "Description:"
    echo "  Queries the Nextcloud database for starred news articles and outputs them in markdown format."
    exit 1
}

# Default values
USER_ID=""

# Parse command line arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        -u|--user)
            USER_ID="$2"
            shift 2
            ;;
        -h|--help)
            usage
            ;;
        *)
            echo "Unknown option: $1"
            usage
            ;;
    esac
done

# Check if USER_ID is provided
if [[ -z "$USER_ID" ]]; then
    echo "Error: User ID is required. Use -u or --user to specify the user."
    echo ""
    usage
fi

# MySQL query to get starred articles with tab-separated values instead of JSON
QUERY="SELECT
    COALESCE(news_items.title, 'Untitled') as article_title,
    COALESCE(news_feeds.title, 'Unknown Feed') as feed_title,
    COALESCE(news_items.url, '') as article_uri
FROM news_items
LEFT JOIN news_feeds ON news_items.feed_id = news_feeds.id
WHERE news_items.starred = 1 AND news_feeds.user_id = '$USER_ID';"

echo "# Starred News Articles for User: $USER_ID"
echo ""
echo "Generated on: $(date)"
echo ""

# Execute the MySQL query and process the results with proper charset
mysql_output=$(sudo mysql --silent --default-character-set=utf8mb4 nextcloud -e "$QUERY" 2>/dev/null || {
    echo "Error: Failed to execute MySQL query. Please check:"
    echo "- MySQL service is running"
    echo "- You have sudo privileges"
    echo "- The 'nextcloud' database exists"
    echo "- The tables 'news_items' and 'news_feeds' exist"
    exit 1
})

# Check if we got any results
if [[ -z "$mysql_output" ]]; then
    echo "No starred articles found for user '$USER_ID'."
    exit 0
fi

counter=1
while IFS=$'\t' read -r article_title feed_title article_uri; do
    # Skip header line if present
    if [[ "$article_title" == "article_title" ]]; then
        continue
    fi

    if [[ -n "$article_title" ]]; then
        # Sanitize values for markdown output
        article_title=${article_title//\[/\\[}
        article_title=${article_title//\]/\\]}
        feed_title=${feed_title//\[/\\[}
        feed_title=${feed_title//\]/\\]}

        # Handle empty URI
        if [[ -z "$article_uri" ]]; then
            article_uri="#"
        fi

        # Output in markdown format
        echo "$counter. [$feed_title - $article_title]($article_uri)"

        ((counter++))
    fi
done <<< "$mysql_output"

echo ""
echo "Total starred articles: $((counter - 1))"