import { ctx } from "context";
import {
  aliasedTable,
  and,
  asc,
  count,
  desc,
  eq,
  inArray,
  or,
  sql,
} from "drizzle-orm";
import { Hono } from "hono";
import { createAgent } from "lib/agent";
import { fetchBskyProfile } from "lib/bskyProfile";
import { withLikes } from "lib/trackLikes";
import { verifyToken } from "lib/verifyToken";
import { likeTrack, unLikeTrack } from "lovedtracks/lovedtracks.service";
import { requestCounter } from "metrics";
import * as R from "ramda";
import tables from "schema";
import type { SelectUser } from "schema/users";
import {
  createShout,
  likeShout,
  replyShout,
  unlikeShout,
} from "shouts/shouts.service";
import { shoutSchema } from "types/shout";
import type { Track } from "types/track";
import { dedupeTracksKeepLyrics } from "./utils";

const app = new Hono();

/** Shared media (gif/sticker/clip) + mention-facet columns selected alongside
 * every shout row. */
const shoutGifColumns = {
  gifUrl: tables.shouts.gifUrl,
  gifPreviewUrl: tables.shouts.gifPreviewUrl,
  gifAlt: tables.shouts.gifAlt,
  gifWidth: tables.shouts.gifWidth,
  gifHeight: tables.shouts.gifHeight,
  facets: tables.shouts.facets,
};

/** The same columns as `shoutGifColumns`, for spreading into a `groupBy()` —
 * Postgres requires every non-aggregated selected column to appear here. */
const shoutGifGroupBy = [
  tables.shouts.gifUrl,
  tables.shouts.gifPreviewUrl,
  tables.shouts.gifAlt,
  tables.shouts.gifWidth,
  tables.shouts.gifHeight,
  tables.shouts.facets,
];

app.get("/:did/likes", async (c) => {
  requestCounter.add(1, { method: "GET", route: "/users/:did/likes" });
  const did = c.req.param("did");
  const size = +c.req.query("size") || 10;
  const offset = +c.req.query("offset") || 0;

  const lovedTracks = await ctx.db
    .select()
    .from(tables.lovedTracks)
    .leftJoin(tables.tracks, eq(tables.lovedTracks.trackId, tables.tracks.id))
    .leftJoin(tables.users, eq(tables.lovedTracks.userId, tables.users.id))
    .where(or(eq(tables.users.did, did), eq(tables.users.handle, did)))
    .orderBy(desc(tables.lovedTracks.createdAt))
    .limit(size)
    .offset(offset)
    .execute();

  return c.json(lovedTracks);
});

app.get("/:handle/scrobbles", async (c) => {
  requestCounter.add(1, { method: "GET", route: "/users/:handle/scrobbles" });
  const handle = c.req.param("handle");

  const size = +c.req.query("size") || 10;
  const offset = +c.req.query("offset") || 0;

  const { data } = await ctx.analytics.post("library.getScrobbles", {
    user_did: handle,
    pagination: {
      skip: offset,
      take: size,
    },
  });

  return c.json(data);
});

app.get("/:did/albums", async (c) => {
  requestCounter.add(1, { method: "GET", route: "/users/:did/albums" });
  const did = c.req.param("did");
  const size = +c.req.query("size") || 10;
  const offset = +c.req.query("offset") || 0;

  const { data } = await ctx.analytics.post("library.getTopAlbums", {
    user_did: did,
    pagination: {
      skip: offset,
      take: size,
    },
  });

  return c.json(data.map((item) => ({ ...item, tags: [] })));
});

app.get("/:did/artists", async (c) => {
  requestCounter.add(1, { method: "GET", route: "/users/:did/artists" });
  const did = c.req.param("did");
  const size = +c.req.query("size") || 10;
  const offset = +c.req.query("offset") || 0;

  const { data } = await ctx.analytics.post("library.getTopArtists", {
    user_did: did,
    pagination: {
      skip: offset,
      take: size,
    },
  });

  return c.json(data.map((item) => ({ ...item, tags: [] })));
});

app.get("/:did/tracks", async (c) => {
  requestCounter.add(1, { method: "GET", route: "/users/:did/tracks" });
  const did = c.req.param("did");
  const size = +c.req.query("size") || 10;
  const offset = +c.req.query("offset") || 0;

  const { data } = await ctx.analytics.post("library.getTopTracks", {
    user_did: did,
    pagination: {
      skip: offset,
      take: size,
    },
  });

  return c.json(
    data.map((item) => ({
      ...item,
      tags: [],
    })),
  );
});

app.get("/:did/playlists", async (c) => {
  requestCounter.add(1, { method: "GET", route: "/users/:did/playlists" });
  const did = c.req.param("did");
  const size = +c.req.query("size") || 10;
  const offset = +c.req.query("offset") || 0;

  const results = await ctx.db
    .select({
      playlists: tables.playlists,
      trackCount: sql<number>`
      (SELECT COUNT(*)
       FROM ${tables.playlistTracks}
       WHERE ${tables.playlistTracks.playlistId} = ${tables.playlists.id}
      )`.as("trackCount"),
    })
    .from(tables.playlists)
    .leftJoin(tables.users, eq(tables.playlists.createdBy, tables.users.id))
    .where(or(eq(tables.users.did, did), eq(tables.users.handle, did)))
    .offset(offset)
    .limit(size)
    .execute();

  return c.json(
    results.map((x) => ({
      ...x.playlists,
      trackCount: +x.trackCount,
    })),
  );
});

app.get("/:did/app.rocksky.scrobble/:rkey", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.scrobble/:rkey",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const uri = `at://${did}/app.rocksky.scrobble/${rkey}`;

  const scrobble = await ctx.db
    .select()
    .from(tables.scrobbles)
    .leftJoin(tables.tracks, eq(tables.scrobbles.trackId, tables.tracks.id))
    .leftJoin(tables.users, eq(tables.scrobbles.userId, tables.users.id))
    .where(eq(tables.scrobbles.uri, uri))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!scrobble) {
    c.status(404);
    return c.text("Scrobble not found");
  }

  const [listeners, scrobbles] = await Promise.all([
    ctx.db
      .select({ count: sql<number>`COUNT(*)` })
      .from(tables.userTracks)
      .where(eq(tables.userTracks.trackId, scrobble.tracks.id))
      .execute()
      .then((rows) => rows[0].count),
    ctx.db
      .select({ count: sql<number>`COUNT(*)` })
      .from(tables.scrobbles)
      .where(eq(tables.scrobbles.trackId, scrobble.tracks.id))
      .execute()
      .then((rows) => rows[0].count),
  ]);

  return c.json({
    ...scrobble,
    id: scrobble.scrobbles.id,
    listeners: listeners || 1,
    scrobbles: scrobbles || 1,
    tags: [],
  });
});

app.get("/:did/app.rocksky.artist/:rkey", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.artist/:rkey",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const uri = `at://${did}/app.rocksky.artist/${rkey}`;

  const artist = await ctx.db
    .select()
    .from(tables.userArtists)
    .leftJoin(
      tables.artists,
      eq(tables.userArtists.artistId, tables.artists.id),
    )
    .where(or(eq(tables.userArtists.uri, uri), eq(tables.artists.uri, uri)))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!artist) {
    c.status(404);
    return c.text("Artist not found");
  }

  const [listeners, scrobbles] = await Promise.all([
    ctx.db
      .select({ count: sql<number>`COUNT(*)` })
      .from(tables.userArtists)
      .where(eq(tables.userArtists.artistId, artist.artists.id))
      .execute()
      .then((rows) => rows[0].count),
    ctx.db
      .select({ count: sql<number>`COUNT(*)` })
      .from(tables.scrobbles)
      .where(eq(tables.scrobbles.artistId, artist.artists.id))
      .execute()
      .then((rows) => rows[0].count),
  ]);

  return c.json({
    ...R.omit(["id"], artist.artists),
    id: artist.artists.id,
    listeners: listeners || 1,
    scrobbles: scrobbles || 1,
    tags: [],
  });
});

app.get("/:did/app.rocksky.album/:rkey", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.album/:rkey",
  });

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const uri = `at://${did}/app.rocksky.album/${rkey}`;

  const album = await ctx.db
    .select()
    .from(tables.userAlbums)
    .leftJoin(tables.albums, eq(tables.userAlbums.albumId, tables.albums.id))
    .where(or(eq(tables.userAlbums.uri, uri), eq(tables.albums.uri, uri)))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!album) {
    c.status(404);
    return c.text("Album not found");
  }

  const tracks = await ctx.db
    .select()
    .from(tables.albumTracks)
    .leftJoin(tables.tracks, eq(tables.albumTracks.trackId, tables.tracks.id))
    .where(eq(tables.albumTracks.albumId, album.albums.id))
    .orderBy(asc(tables.tracks.trackNumber))
    .execute();

  const [listeners, scrobbles] = await Promise.all([
    ctx.db
      .select({ count: sql<number>`COUNT(*)` })
      .from(tables.userAlbums)
      .where(eq(tables.userAlbums.albumId, album.albums.id))
      .execute()
      .then((rows) => rows[0].count),
    ctx.db
      .select({ count: sql<number>`COUNT(*)` })
      .from(tables.scrobbles)
      .where(eq(tables.scrobbles.albumId, album.albums.id))
      .execute()
      .then((rows) => rows[0].count),
  ]);

  return c.json({
    ...R.omit(["id"], album.albums),
    id: album.albums.id,
    listeners: listeners || 1,
    scrobbles: scrobbles || 1,
    label: tracks[0]?.tracks.label || "",
    tracks: dedupeTracksKeepLyrics(tracks.map((track) => track.tracks)).sort(
      (a, b) => a.track_number - b.track_number,
    ),
    tags: [],
  });
});

app.get("/:did/app.rocksky.song/:rkey", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.song/:rkey",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const uri = `at://${did}/app.rocksky.song/${rkey}`;

  const [_track, user_track] = await Promise.all([
    ctx.db
      .select()
      .from(tables.tracks)
      .where(eq(tables.tracks.uri, uri))
      .limit(1)
      .execute()
      .then((rows) => rows[0]),
    ctx.db
      .select()
      .from(tables.userTracks)
      .leftJoin(tables.tracks, eq(tables.userTracks.trackId, tables.tracks.id))
      .where(eq(tables.userTracks.uri, uri))
      .limit(1)
      .execute()
      .then((rows) => rows[0]),
  ]);
  const track = _track || user_track?.tracks;

  if (!track) {
    c.status(404);
    return c.text("Track not found");
  }

  const [listeners, scrobbles] = await Promise.all([
    ctx.db
      .select({ count: sql<number>`COUNT(*)` })
      .from(tables.userTracks)
      .where(eq(tables.userTracks.trackId, track.id))
      .execute()
      .then((rows) => rows[0].count),
    ctx.db
      .select({ count: sql<number>`COUNT(*)` })
      .from(tables.scrobbles)
      .where(eq(tables.scrobbles.trackId, track.id))
      .execute()
      .then((rows) => rows[0].count),
  ]);

  return c.json({
    ...R.omit(["id"], track),
    id: track.id,
    tags: [],
    listeners: listeners || 1,
    scrobbles: scrobbles || 1,
  });
});

app.get("/:did/app.rocksky.artist/:rkey/tracks", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.artist/:rkey/tracks",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const uri = `at://${did}/app.rocksky.artist/${rkey}`;
  const size = +c.req.query("size") || 10;
  const offset = +c.req.query("offset") || 0;

  const tracks = await ctx.db
    .select()
    .from(tables.artistTracks)
    .leftJoin(tables.tracks, eq(tables.artistTracks.trackId, tables.tracks.id))
    .where(eq(tables.artistTracks.artistId, uri)) // Assuming artist_id is the URI or ID; adjust if needed
    .orderBy(desc(tables.artistTracks.xataVersion))
    .limit(size)
    .offset(offset)
    .execute();

  return c.json(
    tracks.map((item) => ({
      ...R.omit(["id"], item.tracks),
      id: item.tracks.id,
      xata_version: item.artist_tracks.xataVersion,
    })),
  );
});

app.get("/:did/app.rocksky.artist/:rkey/albums", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.artist/:rkey/albums",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const uri = `at://${did}/app.rocksky.artist/${rkey}`;
  const size = +c.req.query("size") || 10;
  const offset = +c.req.query("offset") || 0;

  const albums = await ctx.db
    .select()
    .from(tables.artistAlbums)
    .leftJoin(tables.albums, eq(tables.artistAlbums.albumId, tables.albums.id))
    .where(eq(tables.artistAlbums.artistId, uri)) // Assuming artist_id is the URI or ID; adjust if needed
    .orderBy(desc(tables.artistAlbums.xataVersion))
    .limit(size)
    .offset(offset)
    .execute();

  return c.json(
    R.uniqBy(
      (item) => item.id,
      albums.map((item) => ({
        ...R.omit(["id"], item.albums),
        id: item.albums.id,
        xata_version: item.artist_albums.xataVersion,
      })),
    ),
  );
});

app.get("/:did/app.rocksky.playlist/:rkey", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.playlist/:rkey",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const uri = `at://${did}/app.rocksky.playlist/${rkey}`;

  const playlist = await ctx.db
    .select()
    .from(tables.playlists)
    .leftJoin(tables.users, eq(tables.playlists.createdBy, tables.users.id))
    .where(eq(tables.playlists.uri, uri))
    .execute();

  if (!playlist.length) {
    c.status(404);
    return c.text("Playlist not found");
  }

  const results = await ctx.db
    .select()
    .from(tables.playlistTracks)
    .leftJoin(
      tables.playlists,
      eq(tables.playlistTracks.playlistId, tables.playlists.id),
    )
    .leftJoin(
      tables.tracks,
      eq(tables.playlistTracks.trackId, tables.tracks.id),
    )
    .where(eq(tables.playlists.uri, uri))
    // Dropped a GROUP BY that listed all 41 selected columns by hand. There are
    // no aggregates and each entry joins one playlist and one track, so it
    // de-duplicated nothing — and since these tables have no PRIMARY KEY (Xata
    // uses unique indexes), Postgres can't infer functional dependency, so every
    // column added to the schema later broke the query.
    .orderBy(asc(tables.playlistTracks.createdAt))
    .execute();

  // Optional auth: the playlist is public, a valid token only decides whether
  // `liked` can be filled in. A bad token must not fail the read.
  let did_: string | undefined;
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();
  if (bearer && bearer !== "null") {
    try {
      did_ = (await verifyToken(bearer)).did;
    } catch {
      did_ = undefined;
    }
  }

  return c.json({
    ...playlist[0].playlists,
    curatedBy: playlist[0].users,
    tracks: await withLikes(
      ctx,
      results.map((x) => x.tracks),
      did_,
    ),
  });
});

app.get("/:did", async (c) => {
  requestCounter.add(1, { method: "GET", route: "/users/:did" });
  const did = c.req.param("did");
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();
  if (bearer && bearer !== "null") {
    const claims = await verifyToken(bearer);
    const agent = await createAgent(ctx.oauthClient, claims.did);

    if (agent) {
      const handle = await ctx.resolver.resolveDidToHandle(did);
      const resolved = await fetchBskyProfile(did, agent);

      if (handle) {
        await ctx.db
          .update(tables.users)
          .set({
            handle,
            // Only overwrite avatar/displayName when the lookup actually
            // returned them, so a failed/partial fetch never clobbers good data.
            ...(resolved.displayName !== undefined
              ? { displayName: resolved.displayName }
              : {}),
            ...(resolved.avatar !== undefined
              ? { avatar: resolved.avatar }
              : {}),
          })
          .where(eq(tables.users.did, did))
          .execute();

        const user = await ctx.db
          .select()
          .from(tables.users)
          .where(eq(tables.users.did, did))
          .limit(1)
          .execute()
          .then((rows) => rows[0]);

        ctx.nc.publish("rocksky.user", Buffer.from(JSON.stringify(user)));
      }
    }
  }

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(or(eq(tables.users.did, did), eq(tables.users.handle, did)))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!user) {
    c.status(404);
    return c.text("User not found");
  }

  return c.json(user);
});

app.post("/:did/app.rocksky.artist/:rkey/shouts", async (c) => {
  requestCounter.add(1, {
    method: "POST",
    route: "/users/:did/app.rocksky.artist/:rkey/shouts",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const body = await c.req.json();
  const parsed = shoutSchema.safeParse(body);

  if (parsed.error) {
    c.status(400);
    return c.text("Invalid shout data: " + parsed.error.message);
  }

  await createShout(
    ctx,
    parsed.data,
    `at://${did}/app.rocksky.artist/${rkey}`,
    user,
    agent,
  );
  return c.json({});
});

app.post("/:did/app.rocksky.album/:rkey/shouts", async (c) => {
  requestCounter.add(1, {
    method: "POST",
    route: "/users/:did/app.rocksky.album/:rkey/shouts",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const body = await c.req.json();
  const parsed = shoutSchema.safeParse(body);

  if (parsed.error) {
    c.status(400);
    return c.text("Invalid shout data: " + parsed.error.message);
  }

  await createShout(
    ctx,
    parsed.data,
    `at://${did}/app.rocksky.album/${rkey}`,
    user,
    agent,
  );
  return c.json({});
});

app.post("/:did/app.rocksky.song/:rkey/shouts", async (c) => {
  requestCounter.add(1, {
    method: "POST",
    route: "/users/:did/app.rocksky.song/:rkey/shouts",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const body = await c.req.json();

  const parsed = shoutSchema.safeParse(body);
  if (parsed.error) {
    c.status(400);
    return c.text("Invalid shout data: " + parsed.error.message);
  }

  await createShout(
    ctx,
    parsed.data,
    `at://${did}/app.rocksky.song/${rkey}`,
    user,
    agent,
  );

  return c.json({});
});

app.post("/:did/app.rocksky.scrobble/:rkey/shouts", async (c) => {
  requestCounter.add(1, {
    method: "POST",
    route: "/users/:did/app.rocksky.scrobble/:rkey/shouts",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const body = await c.req.json();

  const parsed = shoutSchema.safeParse(body);
  if (parsed.error) {
    c.status(400);
    return c.text("Invalid shout data: " + parsed.error.message);
  }

  await createShout(
    ctx,
    parsed.data,
    `at://${did}/app.rocksky.scrobble/${rkey}`,
    user,
    agent,
  );

  return c.json({});
});

app.post("/:did/shouts", async (c) => {
  requestCounter.add(1, { method: "POST", route: "/users/:did/shouts" });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const body = await c.req.json();
  const parsed = shoutSchema.safeParse(body);
  if (parsed.error) {
    c.status(400);
    return c.text("Invalid shout data: " + parsed.error.message);
  }

  const _user = await ctx.db
    .select()
    .from(tables.users)
    .where(or(eq(tables.users.did, did), eq(tables.users.handle, did)))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!_user) {
    c.status(404);
    return c.text("User not found");
  }

  await createShout(ctx, parsed.data, `at://${_user.did}`, user, agent);

  return c.json({});
});

app.post("/:did/app.rocksky.shout/:rkey/likes", async (c) => {
  requestCounter.add(1, {
    method: "POST",
    route: "/users/:did/app.rocksky.shout/:rkey/likes",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  await likeShout(ctx, `at://${did}/app.rocksky.shout/${rkey}`, user, agent);

  return c.json({});
});

app.delete("/:did/app.rocksky.shout/:rkey/likes", async (c) => {
  requestCounter.add(1, {
    method: "DELETE",
    route: "/users/:did/app.rocksky.shout/:rkey/likes",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  await unlikeShout(ctx, `at://${did}/app.rocksky.shout/${rkey}`, user, agent);

  return c.json({});
});

app.post("/:did/app.rocksky.song/:rkey/likes", async (c) => {
  requestCounter.add(1, {
    method: "POST",
    route: "/users/:did/app.rocksky.song/:rkey/likes",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const result = await ctx.db
    .select()
    .from(tables.tracks)
    .where(eq(tables.tracks.uri, `at://${did}/app.rocksky.song/${rkey}`))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  const track: Track = {
    title: result.title,
    artist: result.artist,
    album: result.album,
    albumArt: result.albumArt,
    albumArtist: result.albumArtist,
    trackNumber: result.trackNumber,
    duration: result.duration,
    composer: result.composer,
    lyrics: result.lyrics,
    discNumber: result.discNumber,
  };
  await likeTrack(ctx, track, user, agent);

  return c.json({});
});

app.delete("/:did/app.rocksky.song/:rkey/likes", async (c) => {
  requestCounter.add(1, {
    method: "DELETE",
    route: "/users/:did/app.rocksky.song/:rkey/likes",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const track = await ctx.db
    .select()
    .from(tables.tracks)
    .where(eq(tables.tracks.uri, `at://${did}/app.rocksky.song/${rkey}`))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!track) {
    c.status(404);
    return c.text("Track not found");
  }

  await unLikeTrack(ctx, track.sha256, user, agent);

  return c.json([]);
});

// Like by track id, for rows that have no app.rocksky.song record to address.
// A track ingested from a scrobble carries uri = NULL until it is published,
// and the like itself is keyed by sha256 anyway — the song URI was only ever a
// way to find the row.
app.post("/tracks/:trackId/likes", async (c) => {
  requestCounter.add(1, {
    method: "POST",
    route: "/users/tracks/:trackId/likes",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();
  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);
  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const result = await ctx.db
    .select()
    .from(tables.tracks)
    .where(eq(tables.tracks.id, c.req.param("trackId")))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!result) {
    c.status(404);
    return c.text("Track not found");
  }

  await likeTrack(
    ctx,
    {
      title: result.title,
      artist: result.artist,
      album: result.album,
      albumArt: result.albumArt,
      albumArtist: result.albumArtist,
      trackNumber: result.trackNumber,
      duration: result.duration,
      composer: result.composer,
      lyrics: result.lyrics,
      discNumber: result.discNumber,
    },
    user,
    agent,
  );

  return c.json({});
});

app.delete("/tracks/:trackId/likes", async (c) => {
  requestCounter.add(1, {
    method: "DELETE",
    route: "/users/tracks/:trackId/likes",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();
  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);
  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const track = await ctx.db
    .select()
    .from(tables.tracks)
    .where(eq(tables.tracks.id, c.req.param("trackId")))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!track) {
    c.status(404);
    return c.text("Track not found");
  }

  await unLikeTrack(ctx, track.sha256, user, agent);

  return c.json([]);
});

app.post("/:did/app.rocksky.shout/:rkey/replies", async (c) => {
  requestCounter.add(1, {
    method: "POST",
    route: "/users/:did/app.rocksky.shout/:rkey/replies",
  });
  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);
  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const body = await c.req.json();
  const parsed = shoutSchema.safeParse(body);

  if (parsed.error) {
    c.status(400);
    return c.text("Invalid shout data: " + parsed.error.message);
  }

  await replyShout(
    ctx,
    parsed.data,
    `at://${did}/app.rocksky.shout/${rkey}`,
    user,
    agent,
  );
  return c.json({});
});

app.get("/:did/app.rocksky.artist/:rkey/shouts", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.artist/:rkey/shouts",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  let user: SelectUser | undefined;
  if (bearer && bearer !== "null") {
    const payload = await verifyToken(bearer);

    user = await ctx.db
      .select()
      .from(tables.users)
      .where(eq(tables.users.did, payload.did))
      .limit(1)
      .execute()
      .then((rows) => rows[0]);
  }

  const shouts = await ctx.db
    .select({
      shouts: user
        ? {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            uri: tables.shouts.uri,
            parent: tables.shouts.parentId,
            likes: count(tables.shoutLikes.id).as("likes"),
            liked: sql<boolean>`
      EXISTS (
        SELECT 1
        FROM ${tables.shoutLikes}
        WHERE ${tables.shoutLikes}.shout_id = ${tables.shouts}.xata_id
          AND ${tables.shoutLikes}.user_id = ${user.id}
      )`.as("liked"),
          }
        : {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            parent: tables.shouts.parentId,
            uri: tables.shouts.uri,
            likes: count(tables.shoutLikes.id).as("likes"),
          },
      users: {
        id: tables.users.id,
        did: tables.users.did,
        handle: tables.users.handle,
        displayName: tables.users.displayName,
        avatar: tables.users.avatar,
      },
    })
    .from(tables.shouts)
    .leftJoin(tables.users, eq(tables.shouts.authorId, tables.users.id))
    .leftJoin(tables.artists, eq(tables.shouts.artistId, tables.artists.id))
    .leftJoin(
      tables.shoutLikes,
      eq(tables.shouts.id, tables.shoutLikes.shoutId),
    )
    .where(eq(tables.artists.uri, `at://${did}/app.rocksky.artist/${rkey}`))
    .groupBy(
      tables.shouts.id,
      tables.shouts.content,
      tables.shouts.createdAt,
      tables.shouts.uri,
      tables.shouts.parentId,
      ...shoutGifGroupBy,
      tables.users.id,
      tables.users.did,
      tables.users.handle,
      tables.users.displayName,
      tables.users.avatar,
    )
    .orderBy(desc(tables.shouts.createdAt))
    .execute();
  return c.json(shouts);
});

app.get("/:did/app.rocksky.album/:rkey/shouts", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.album/:rkey/shouts",
  });

  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  let user: SelectUser | undefined;
  if (bearer && bearer !== "null") {
    const payload = await verifyToken(bearer);

    user = await ctx.db
      .select()
      .from(tables.users)
      .where(eq(tables.users.did, payload.did))
      .limit(1)
      .execute()
      .then((rows) => rows[0]);
  }

  const shouts = await ctx.db
    .select({
      shouts: user
        ? {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            parent: tables.shouts.parentId,
            uri: tables.shouts.uri,
            likes: count(tables.shoutLikes.id).as("likes"),
            liked: sql<boolean>`
      EXISTS (
        SELECT 1
        FROM ${tables.shoutLikes}
        WHERE ${tables.shoutLikes}.shout_id = ${tables.shouts}.xata_id
          AND ${tables.shoutLikes}.user_id = ${user.id}
      )`.as("liked"),
          }
        : {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            parent: tables.shouts.parentId,
            uri: tables.shouts.uri,
            likes: count(tables.shoutLikes.id).as("likes"),
          },
      users: {
        id: tables.users.id,
        did: tables.users.did,
        handle: tables.users.handle,
        displayName: tables.users.displayName,
        avatar: tables.users.avatar,
      },
    })
    .from(tables.shouts)
    .leftJoin(tables.users, eq(tables.shouts.authorId, tables.users.id))
    .leftJoin(tables.albums, eq(tables.shouts.albumId, tables.albums.id))
    .leftJoin(
      tables.shoutLikes,
      eq(tables.shouts.id, tables.shoutLikes.shoutId),
    )
    .where(eq(tables.albums.uri, `at://${did}/app.rocksky.album/${rkey}`))
    .groupBy(
      tables.shouts.id,
      tables.shouts.content,
      tables.shouts.createdAt,
      tables.shouts.uri,
      tables.shouts.parentId,
      ...shoutGifGroupBy,
      tables.users.id,
      tables.users.did,
      tables.users.handle,
      tables.users.displayName,
      tables.users.avatar,
    )
    .orderBy(desc(tables.shouts.createdAt))
    .execute();

  return c.json(shouts);
});

app.get("/:did/app.rocksky.song/:rkey/shouts", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.song/:rkey/shouts",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  let user: SelectUser | undefined;
  if (bearer && bearer !== "null") {
    const payload = await verifyToken(bearer);

    user = await ctx.db
      .select()
      .from(tables.users)
      .where(eq(tables.users.did, payload.did))
      .limit(1)
      .execute()
      .then((rows) => rows[0]);
  }

  const shouts = await ctx.db
    .select({
      shouts: user
        ? {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            uri: tables.shouts.uri,
            parent: tables.shouts.parentId,
            likes: count(tables.shoutLikes.id).as("likes"),
            liked: sql<boolean>`
      EXISTS (
        SELECT 1
        FROM ${tables.shoutLikes}
        WHERE ${tables.shoutLikes}.shout_id = ${tables.shouts}.xata_id
          AND ${tables.shoutLikes}.user_id = ${user.id}
      )`.as("liked"),
          }
        : {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            uri: tables.shouts.uri,
            parent: tables.shouts.parentId,
            likes: count(tables.shoutLikes.id).as("likes"),
          },
      users: {
        id: tables.users.id,
        did: tables.users.did,
        handle: tables.users.handle,
        displayName: tables.users.displayName,
        avatar: tables.users.avatar,
      },
    })
    .from(tables.shouts)
    .leftJoin(tables.users, eq(tables.shouts.authorId, tables.users.id))
    .leftJoin(tables.tracks, eq(tables.shouts.trackId, tables.tracks.id))
    .leftJoin(
      tables.shoutLikes,
      eq(tables.shouts.id, tables.shoutLikes.shoutId),
    )
    .where(eq(tables.tracks.uri, `at://${did}/app.rocksky.song/${rkey}`))
    .groupBy(
      tables.shouts.id,
      tables.shouts.content,
      tables.shouts.createdAt,
      tables.shouts.uri,
      tables.shouts.parentId,
      ...shoutGifGroupBy,
      tables.users.id,
      tables.users.did,
      tables.users.handle,
      tables.users.displayName,
      tables.users.avatar,
    )
    .orderBy(desc(tables.shouts.createdAt))
    .execute();

  return c.json(shouts);
});

app.get("/:did/app.rocksky.scrobble/:rkey/shouts", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.scrobble/:rkey/shouts",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  let user: SelectUser | undefined;
  if (bearer && bearer !== "null") {
    const payload = await verifyToken(bearer);

    user = await ctx.db
      .select()
      .from(tables.users)
      .where(eq(tables.users.did, payload.did))
      .limit(1)
      .execute()
      .then((rows) => rows[0]);
  }

  const shouts = await ctx.db
    .select({
      shouts: user
        ? {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            uri: tables.shouts.uri,
            parent: tables.shouts.parentId,
            likes: count(tables.shoutLikes.id).as("likes"),
            liked: sql<boolean>`
        EXISTS (
          SELECT 1
          FROM ${tables.shoutLikes}
          WHERE ${tables.shoutLikes}.shout_id = ${tables.shouts}.xata_id
            AND ${tables.shoutLikes}.user_id = ${user.id}
        )`.as("liked"),
          }
        : {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            uri: tables.shouts.uri,
            parent: tables.shouts.parentId,
            likes: count(tables.shoutLikes.id).as("likes"),
          },
      users: {
        id: tables.users.id,
        did: tables.users.did,
        handle: tables.users.handle,
        displayName: tables.users.displayName,
        avatar: tables.users.avatar,
      },
    })
    .from(tables.shouts)
    .leftJoin(tables.users, eq(tables.shouts.authorId, tables.users.id))
    .leftJoin(
      tables.scrobbles,
      eq(tables.shouts.scrobbleId, tables.scrobbles.id),
    )
    .leftJoin(
      tables.shoutLikes,
      eq(tables.shouts.id, tables.shoutLikes.shoutId),
    )
    .where(eq(tables.scrobbles.uri, `at://${did}/app.rocksky.scrobble/${rkey}`))
    .groupBy(
      tables.shouts.id,
      tables.shouts.content,
      tables.shouts.createdAt,
      tables.shouts.uri,
      tables.shouts.parentId,
      ...shoutGifGroupBy,
      tables.users.id,
      tables.users.did,
      tables.users.handle,
      tables.users.displayName,
      tables.users.avatar,
    )
    .orderBy(desc(tables.shouts.createdAt))
    .execute();

  return c.json(shouts);
});

app.get("/:did/shouts", async (c) => {
  requestCounter.add(1, { method: "GET", route: "/users/:did/shouts" });
  const did = c.req.param("did");

  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  let user: SelectUser | undefined;
  if (bearer && bearer !== "null") {
    const payload = await verifyToken(bearer);

    user = await ctx.db
      .select()
      .from(tables.users)
      .where(eq(tables.users.did, payload.did))
      .limit(1)
      .execute()
      .then((rows) => rows[0]);
  }

  const shouts = await ctx.db
    .select({
      profileShouts: {
        id: tables.profileShouts.id,
        createdAt: tables.profileShouts.createdAt,
      },
      shouts: user
        ? {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            uri: tables.shouts.uri,
            parent: tables.shouts.parentId,
            likes: count(tables.shoutLikes.id).as("likes"),
            liked: sql<boolean>`
        EXISTS (
          SELECT 1
          FROM ${tables.shoutLikes}
          WHERE ${tables.shoutLikes}.shout_id = ${tables.shouts}.xata_id
            AND ${tables.shoutLikes}.user_id = ${user.id}
        )`.as("liked"),
            reported: sql<boolean>`
        EXISTS (
          SELECT 1
          FROM ${tables.shoutReports}
          WHERE ${tables.shoutReports}.shout_id = ${tables.shouts}.xata_id
            AND ${tables.shoutReports}.user_id = ${user.id}
        )`.as("reported"),
          }
        : {
            id: tables.shouts.id,
            content: tables.shouts.content,
            ...shoutGifColumns,
            createdAt: tables.shouts.createdAt,
            uri: tables.shouts.uri,
            parent: tables.shouts.parentId,
            likes: count(tables.shoutLikes.id).as("likes"),
          },
      users: {
        id: aliasedTable(tables.users, "authors").id,
        did: aliasedTable(tables.users, "authors").did,
        handle: aliasedTable(tables.users, "authors").handle,
        displayName: aliasedTable(tables.users, "authors").displayName,
        avatar: aliasedTable(tables.users, "authors").avatar,
      },
    })
    .from(tables.profileShouts)
    .where(or(eq(tables.users.did, did), eq(tables.users.handle, did)))
    .leftJoin(tables.shouts, eq(tables.profileShouts.shoutId, tables.shouts.id))
    .leftJoin(
      aliasedTable(tables.users, "authors"),
      eq(tables.shouts.authorId, aliasedTable(tables.users, "authors").id),
    )
    .leftJoin(tables.users, eq(tables.profileShouts.userId, tables.users.id))
    .leftJoin(
      tables.shoutLikes,
      eq(tables.shouts.id, tables.shoutLikes.shoutId),
    )
    .groupBy(
      tables.profileShouts.id,
      tables.profileShouts.createdAt,
      tables.shouts.id,
      tables.shouts.uri,
      tables.shouts.content,
      tables.shouts.createdAt,
      tables.shouts.parentId,
      ...shoutGifGroupBy,
      tables.users.id,
      tables.users.did,
      tables.users.handle,
      tables.users.displayName,
      tables.users.avatar,
      aliasedTable(tables.users, "authors").id,
      aliasedTable(tables.users, "authors").did,
      aliasedTable(tables.users, "authors").handle,
      aliasedTable(tables.users, "authors").displayName,
      aliasedTable(tables.users, "authors").avatar,
    )
    .orderBy(desc(tables.profileShouts.createdAt))
    .execute();

  return c.json(shouts);
});

app.get("/:did/app.rocksky.shout/:rkey/likes", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.shout/:rkey/likes",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const likes = await ctx.db
    .select()
    .from(tables.shoutLikes)
    .leftJoin(tables.users, eq(tables.shoutLikes.userId, tables.users.id))
    .leftJoin(tables.shouts, eq(tables.shoutLikes.shoutId, tables.shouts.id))
    .where(eq(tables.shouts.uri, `at://${did}/app.rocksky.shout/${rkey}`))
    .execute();
  return c.json(likes);
});

app.get("/:did/app.rocksky.shout/:rkey/replies", async (c) => {
  requestCounter.add(1, {
    method: "GET",
    route: "/users/:did/app.rocksky.shout/:rkey/replies",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");
  const shouts = await ctx.db
    .select()
    .from(tables.shouts)
    .leftJoin(tables.users, eq(tables.shouts.authorId, tables.users.id))
    .where(eq(tables.shouts.parentId, `at://${did}/app.rocksky.shout/${rkey}`))
    .orderBy(asc(tables.shouts.createdAt))
    .execute();
  return c.json(shouts);
});

app.get("/:did/stats", async (c) => {
  requestCounter.add(1, { method: "GET", route: "/users/:did/stats" });
  const did = c.req.param("did");

  const { data } = await ctx.analytics.post("library.getStats", {
    user_did: did,
  });

  return c.json({
    scrobbles: data.scrobbles,
    artists: data.artists,
    lovedTracks: data.loved_tracks,
    albums: data.albums,
    tracks: data.tracks,
  });
});

app.post("/:did/app.rocksky.shout/:rkey/report", async (c) => {
  requestCounter.add(1, {
    method: "POST",
    route: "/users/:did/app.rocksky.shout/:rkey/report",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const shout = await ctx.db
    .select()
    .from(tables.shouts)
    .where(eq(tables.shouts.uri, `at://${did}/app.rocksky.shout/${rkey}`))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!shout) {
    c.status(404);
    return c.text("Shout not found");
  }

  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const existingReport = await ctx.db
    .select()
    .from(tables.shoutReports)
    .where(
      and(
        eq(tables.shoutReports.userId, user.id),
        eq(tables.shoutReports.shoutId, shout.id),
      ),
    )
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (existingReport) {
    return c.json(existingReport);
  }

  const report = await ctx.db
    .insert(tables.shoutReports)
    .values({
      userId: user.id,
      shoutId: shout.id,
    })
    .returning()
    .execute()
    .then((rows) => rows[0]);

  return c.json(report);
});

app.delete("/:did/app.rocksky.shout/:rkey/report", async (c) => {
  requestCounter.add(1, {
    method: "DELETE",
    route: "/users/:did/app.rocksky.shout/:rkey/report",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const shout = await ctx.db
    .select()
    .from(tables.shouts)
    .where(eq(tables.shouts.uri, `at://${did}/app.rocksky.shout/${rkey}`))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!shout) {
    c.status(404);
    return c.text("Shout not found");
  }

  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const report = await ctx.db
    .select()
    .from(tables.shoutReports)
    .where(
      and(
        eq(tables.shoutReports.userId, user.id),
        eq(tables.shoutReports.shoutId, shout.id),
      ),
    )
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!report) {
    c.status(404);
    return c.text("Report not found");
  }

  if (report.userId !== user.id) {
    c.status(403);
    return c.text("Forbidden");
  }

  await ctx.db
    .delete(tables.shoutReports)
    .where(eq(tables.shoutReports.id, report.id))
    .execute();

  return c.json(report);
});

app.delete("/:did/app.rocksky.shout/:rkey", async (c) => {
  requestCounter.add(1, {
    method: "DELETE",
    route: "/users/:did/app.rocksky.shout/:rkey",
  });
  const did = c.req.param("did");
  const rkey = c.req.param("rkey");

  const bearer = (c.req.header("authorization") || "").split(" ")[1]?.trim();

  if (!bearer || bearer === "null") {
    c.status(401);
    return c.text("Unauthorized");
  }

  const payload = await verifyToken(bearer);
  const agent = await createAgent(ctx.oauthClient, payload.did);

  const user = await ctx.db
    .select()
    .from(tables.users)
    .where(eq(tables.users.did, payload.did))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!user) {
    c.status(401);
    return c.text("Unauthorized");
  }

  const shout = await ctx.db
    .select()
    .from(tables.shouts)
    .where(eq(tables.shouts.uri, `at://${did}/app.rocksky.shout/${rkey}`))
    .limit(1)
    .execute()
    .then((rows) => rows[0]);

  if (!shout) {
    c.status(404);
    return c.text("Shout not found");
  }

  if (shout.authorId !== user.id) {
    c.status(403);
    return c.text("Forbidden");
  }

  const replies = await ctx.db
    .select({
      replies: {
        id: tables.shouts.id,
      },
    })
    .from(tables.shouts)
    .where(eq(tables.shouts.parentId, shout.id))
    .execute();

  const replyIds = replies.map(({ replies: r }) => r.id);

  // Delete related records in the correct order
  await ctx.db
    .delete(tables.shoutLikes)
    .where(inArray(tables.shoutLikes.shoutId, replyIds))
    .execute();

  await ctx.db
    .delete(tables.shoutReports)
    .where(inArray(tables.shoutReports.shoutId, replyIds))
    .execute();

  await ctx.db
    .delete(tables.profileShouts)
    .where(eq(tables.profileShouts.shoutId, shout.id))
    .execute();

  await ctx.db
    .delete(tables.profileShouts)
    .where(inArray(tables.profileShouts.shoutId, replyIds))
    .execute();

  await ctx.db
    .delete(tables.shoutLikes)
    .where(eq(tables.shoutLikes.shoutId, shout.id))
    .execute();

  await ctx.db
    .delete(tables.shoutReports)
    .where(eq(tables.shoutReports.shoutId, shout.id))
    .execute();

  await ctx.db
    .delete(tables.shouts)
    .where(inArray(tables.shouts.id, replyIds))
    .execute();

  await ctx.db
    .delete(tables.shouts)
    .where(eq(tables.shouts.id, shout.id))
    .execute();

  await agent.com.atproto.repo.deleteRecord({
    repo: agent.assertDid,
    collection: "app.rocksky.shout",
    rkey: shout.uri.split("/").pop(),
  });

  return c.json(shout);
});

export default app;