#!/bin/sh

# wcrooms - list all rooms with their members
# Usage: ./wcrooms [-d db_path]

set -eu

DB="fanr.sqlite"
ULID_LEN=26

while getopts "d:h" opt; do
  case "$opt" in
    d) DB="$OPTARG" ;;
    h)
      cat <<EOF
Usage: $0 [-d db_path]

List all rooms in the database with their members.

Options:
  -d db_path    SQLite database path (default: fanr.sqlite)
  -h            Show this help

Output format:
  <room_id> <room_name> <member_usernames...>
EOF
      exit 0
      ;;
    *) exit 1 ;;
  esac
done

if [ ! -f "$DB" ]; then
  echo "Error: Database '$DB' not found" >&2
  exit 1
fi

command -v sqlite3 >/dev/null 2>&1 || {
  echo "Error: sqlite3 not found" >&2
  exit 1
}

sqlite3 "$DB" -batch <<EOF
WITH room_members_list AS (
  SELECT
    r.id,
    r.name,
    GROUP_CONCAT(u.username, ' ') as members
  FROM rooms r
  LEFT JOIN room_members rm ON r.id = rm.room_id
  LEFT JOIN users u ON rm.user_id = u.id
  GROUP BY r.id, r.name
)
SELECT
  printf('%-*s %s %s', $ULID_LEN, id, name, COALESCE(members, ''))
FROM room_members_list
ORDER BY name;
EOF