feat(council): implement user sorting functionality and enhance user list display

8 files changed, 142 insertions, 10 deletions

diff --git a/garden/src/pages/council/users.tsx b/garden/src/pages/council/users.tsx
index 4077dde..285c7b3 100644
--- a/garden/src/pages/council/users.tsx
+++ b/garden/src/pages/council/users.tsx
@@ -30,6 +30,11 @@ export default function CouncilUsers() {
     return new Date(date).toLocaleDateString();
   }
 
+  function sortIndicator(field: string) {
+    if (council.sortField() !== field) return "";
+    return council.sortOrder() === "asc" ? " \u25B2" : " \u25BC";
+  }
+
   return (
     <section>
       <h2 class="page-title">Users</h2>
@@ -46,11 +51,11 @@ export default function CouncilUsers() {
 
       <div class="council-grid">
         <div class="council-grid-header">
-          <span>User</span>
-          <span>Email</span>
-          <span>Role</span>
+          <span class="council-sortable" onClick={() => council.toggleSort("display_name")}>User{sortIndicator("display_name")}</span>
+          <span class="council-sortable" onClick={() => council.toggleSort("email")}>Email{sortIndicator("email")}</span>
+          <span class="council-sortable" onClick={() => council.toggleSort("role")}>Role{sortIndicator("role")}</span>
           <span>Status</span>
-          <span>Joined</span>
+          <span class="council-sortable" onClick={() => council.toggleSort("created_at")}>Joined{sortIndicator("created_at")}</span>
         </div>
         <Show when={!council.loading()} fallback={
           <div class="council-grid-empty">Loading...</div>
diff --git a/garden/src/store/council.ts b/garden/src/store/council.ts
index 3ffe36a..559ecb8 100644
--- a/garden/src/store/council.ts
+++ b/garden/src/store/council.ts
@@ -9,10 +9,17 @@ const [page, setPage] = createSignal(1);
 const [totalPages, setTotalPages] = createSignal(0);
 const [loading, setLoading] = createSignal(false);
 const [search, setSearch] = createSignal("");
+const [sortField, setSortField] = createSignal("created_at");
+const [sortOrder, setSortOrder] = createSignal<"asc" | "desc">("desc");
 
 async function loadUsers(p = 1, q = "") {
   setLoading(true);
-  const params = new URLSearchParams({ page: String(p), per_page: "20" });
+  const params = new URLSearchParams({
+    page: String(p),
+    per_page: "20",
+    sort: sortField(),
+    order: sortOrder(),
+  });
   if (q) params.set("search", q);
 
   const response = await api<PaginatedResponse<AdminUser>>(`/council/users?${params}`, {
@@ -28,6 +35,16 @@ async function loadUsers(p = 1, q = "") {
   setLoading(false);
 }
 
+function toggleSort(field: string) {
+  if (sortField() === field) {
+    setSortOrder(sortOrder() === "asc" ? "desc" : "asc");
+  } else {
+    setSortField(field);
+    setSortOrder("desc");
+  }
+  loadUsers(1, search());
+}
+
 export const council = {
   users,
   total,
@@ -37,4 +54,7 @@ export const council = {
   search,
   setSearch,
   loadUsers,
+  sortField,
+  sortOrder,
+  toggleSort,
 };
\ No newline at end of file
diff --git a/garden/src/styles/council.css b/garden/src/styles/council.css
index 3a23336..d623cab 100644
--- a/garden/src/styles/council.css
+++ b/garden/src/styles/council.css
@@ -43,6 +43,16 @@
   border-bottom: 1px solid var(--color-border);
 }
 
+.council-sortable {
+  cursor: pointer;
+  -webkit-user-select: none;
+  user-select: none;
+}
+
+.council-sortable:hover {
+  color: var(--color-text);
+}
+
 .council-grid-row {
   border-bottom: 1px solid var(--color-border);
   color: var(--color-text);
diff --git a/scripts/seed.sh b/scripts/seed.sh
new file mode 100755
index 0000000..59971dc
--- /dev/null
+++ b/scripts/seed.sh
@@ -0,0 +1,61 @@
+#!/bin/bash
+set -euo pipefail
+
+DB_PATH="shrine/pagoda.db"
+
+if [ ! -f "$DB_PATH" ]; then
+  echo "Database not found at $DB_PATH"
+  exit 1
+fi
+
+HASH=$(npx -y bcryptjs-cli password 2>/dev/null)
+OWNER_DATE=$(date -v-4m +"%Y-%m-%dT%H:%M:%SZ" 2>/dev/null || date -d "4 months ago" +"%Y-%m-%dT%H:%M:%SZ")
+
+echo "Generating 99 unique citizens..."
+npx -y @faker-js/cli firstName >/dev/null 2>&1
+FAKER_MODULES=$(find ~/.npm/_npx -path "*/@faker-js/cli/bin/faker.js" -print -quit 2>/dev/null)
+FAKER_MODULES="${FAKER_MODULES%/@faker-js/cli/bin/faker.js}"
+
+NAMES=$(NODE_PATH="$FAKER_MODULES" node -e "
+var f = require('@faker-js/faker').faker;
+var now = Date.now();
+var threeMonthsAgo = new Date(now);
+threeMonthsAgo.setMonth(threeMonthsAgo.getMonth() - 3);
+var range = now - threeMonthsAgo.getTime();
+var seen = new Set();
+while (seen.size < 99) {
+  var first = f.person.firstName();
+  var username = first.toLowerCase().replace(/[^a-z]/g, '');
+  if (username.length >= 3 && username.length <= 32 && !seen.has(username)) {
+    seen.add(username);
+    var last = f.person.lastName();
+    var joinDate = new Date(threeMonthsAgo.getTime() + Math.random() * range).toISOString().replace(/\.\d{3}Z/, 'Z');
+    console.log(username + '|' + first + ' ' + last + '|' + joinDate);
+  }
+}
+")
+
+SQL_FILE=$(mktemp)
+trap "rm -f $SQL_FILE" EXIT
+
+printf '%s' "INSERT OR IGNORE INTO users (username, email, password_hash, display_name, role, email_verified, created_at, updated_at) VALUES " > "$SQL_FILE"
+printf '%s' "('cr', '[email protected]', '${HASH}', 'Bobby', 'owner', 1, '${OWNER_DATE}', '${OWNER_DATE}')" >> "$SQL_FILE"
+
+COUNT=0
+
+while IFS='|' read -r USERNAME DISPLAY JOIN_DATE; do
+  DISPLAY=$(echo "$DISPLAY" | sed "s/'/''/g")
+
+  printf '%s' ", ('${USERNAME}', '${USERNAME}@pagoda.local', '${HASH}', '${DISPLAY}', 'member', 1, '${JOIN_DATE}', '${JOIN_DATE}')" >> "$SQL_FILE"
+
+  COUNT=$((COUNT + 1))
+  echo "  [$COUNT/99] $USERNAME ($DISPLAY)"
+done <<< "$NAMES"
+
+echo ";" >> "$SQL_FILE"
+
+echo "Inserting into database..."
+sqlite3 "$DB_PATH" < "$SQL_FILE"
+
+TOTAL=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM users;")
+echo "Done. Total users: $TOTAL"
\ No newline at end of file
diff --git a/shrine/controllers/council.go b/shrine/controllers/council.go
index 970ae73..43f2459 100644
--- a/shrine/controllers/council.go
+++ b/shrine/controllers/council.go
@@ -10,11 +10,14 @@ import (
 	"github.com/gofiber/fiber/v2"
 )
 
+var userSortColumns = []string{"username", "display_name", "email", "role", "created_at"}
+
 func ListUsersController(context *fiber.Ctx) error {
 	pagination := meta.Paginate(context)
+	sorting := meta.Sort(context, userSortColumns, "created_at")
 	search, _ := meta.Request(context).Query("search")
 
-	items, total := services.ListUsers(pagination, search)
+	items, total := services.ListUsers(pagination, sorting, search)
 	return shortcuts.Success(context, pagination.Response(items, total))
 }
 
diff --git a/shrine/repositories/user.go b/shrine/repositories/user.go
index 8495a7c..73973da 100644
--- a/shrine/repositories/user.go
+++ b/shrine/repositories/user.go
@@ -71,7 +71,7 @@ func OnlineCitizens(limit int) []models.User {
 	return users
 }
 
-func ListUsers(p meta.Pagination, search string) ([]models.User, int64) {
+func ListUsers(pagination meta.Pagination, sorting meta.Sorting, search string) ([]models.User, int64) {
 	var users []models.User
 	var total int64
 
@@ -83,7 +83,7 @@ func ListUsers(p meta.Pagination, search string) ([]models.User, int64) {
 	}
 
 	query.Count(&total)
-	p.Apply(query.Order("created_at desc")).Find(&users)
+	pagination.Apply(sorting.Apply(query)).Find(&users)
 
 	return users, total
 }
\ No newline at end of file
diff --git a/shrine/services/council.go b/shrine/services/council.go
index c3b4d6b..7c19b72 100644
--- a/shrine/services/council.go
+++ b/shrine/services/council.go
@@ -21,8 +21,8 @@ import (
 	"time"
 )
 
-func ListUsers(pagination meta.Pagination, search string) ([]user.AdminUserResponse, int64) {
-	citizens, total := repositories.ListUsers(pagination, search)
+func ListUsers(pagination meta.Pagination, sorting meta.Sorting, search string) ([]user.AdminUserResponse, int64) {
+	citizens, total := repositories.ListUsers(pagination, sorting, search)
 
 	items := make([]user.AdminUserResponse, len(citizens))
 	for index, citizen := range citizens {
diff --git a/shrine/utils/meta/pagination.go b/shrine/utils/meta/pagination.go
index c4afb03..8e5f00e 100644
--- a/shrine/utils/meta/pagination.go
+++ b/shrine/utils/meta/pagination.go
@@ -13,6 +13,11 @@ type Pagination struct {
 	PerPage int
 }
 
+type Sorting struct {
+	Field     string
+	Direction string
+}
+
 func Paginate(context *fiber.Ctx) Pagination {
 	request := Request(context)
 
@@ -31,10 +36,38 @@ func Paginate(context *fiber.Ctx) Pagination {
 	return Pagination{Page: page, PerPage: perPage}
 }
 
+func Sort(context *fiber.Ctx, allowed []string, fallback string) Sorting {
+	request := Request(context)
+
+	field, _ := request.Query("sort")
+	direction, _ := request.Query("order")
+
+	valid := false
+	for _, column := range allowed {
+		if field == column {
+			valid = true
+			break
+		}
+	}
+	if !valid {
+		field = fallback
+	}
+
+	if direction != "asc" && direction != "desc" {
+		direction = "desc"
+	}
+
+	return Sorting{Field: field, Direction: direction}
+}
+
 func (self Pagination) Apply(query *gorm.DB) *gorm.DB {
 	return query.Offset((self.Page - 1) * self.PerPage).Limit(self.PerPage)
 }
 
+func (self Sorting) Apply(query *gorm.DB) *gorm.DB {
+	return query.Order(self.Field + " " + self.Direction)
+}
+
 func (self Pagination) Response(items any, total int64) common.PaginatedResponse {
 	totalPages := int(total) / self.PerPage
 	if int(total)%self.PerPage > 0 {