import "server-only";
import { cache } from "react";
import { notFound } from "next/navigation";
import { cacheLife } from "next/cache";
import { getDb, trails, walks, completions, accounts, type TrailRecord } from "@/data/db";
import { desc, eq, inArray, sql } from "drizzle-orm";
import { IdResolver } from "@atproto/identity";
import { AtUri } from "@atproto/syntax";
import { tagDid, tagHandle, tagAvatar } from "@/cache/tags";
import { getCurrentDid } from "@/auth";

// ============================================================================
// Types
// ============================================================================

export type User = {
  did: string;
  handle: string;
  avatar?: string;
};

export type ExternalEmbed = {
  uri: string;
  title?: string;
  description?: string;
  thumb?: string;
};

export type TrailStop = {
  tid: string;
  title: string;
  content: string;
  buttonText?: string;
  external?: ExternalEmbed;
  walkersHere: Promise<User[]>;
};

export type TrailCardData = {
  uri: string;
  rkey: string;
  creatorHandle: string;
  title: string;
  description: string;
  accentColor: string;
  backgroundColor: string;
  creator: User;
  stopsCount: number;
  createdAt: Date;
};

export type WalkCardData = {
  walkRkey: string;
  walkerDid: string;
  trailRkey: string;
  trailCreatorHandle: string;
  title: string;
  description: string;
  accentColor: string;
  backgroundColor: string;
  stops: Omit<TrailStop, "walkersHere">[];
  visitedStops: string[];
  updatedAt: Date;
};

export type TrailHeaderData = {
  uri: string;
  cid: string;
  rkey: string;
  title: string;
  description: string;
  stopsCount: number;
  accentColor: string;
  backgroundColor: string;
  creator: User;
  createdAt: Date;
};

export type TrailDetailData = {
  header: TrailHeaderData;
  stops: TrailStop[];
  yourWalk: {
    uri: string;
    visitedStops: string[];
    createdAt: Date;
    updatedAt: Date;
  } | null;
  walkers: Promise<Array<{ user: User; isYou: boolean; key: string }>>;
  completions: Promise<
    Array<{ user: User; timestamp: Date; completionUri: string; isYou: boolean }>
  >;
};

export type CompletedTrailData = {
  uri: string;
  rkey: string;
  title: string;
  description: string;
  accentColor: string;
  backgroundColor: string;
  stopsCount: number;
  creator: User;
  completedAt: Date;
};

// ============================================================================
// Account Status
// ============================================================================

async function requireActiveAccount(did: string): Promise<void> {
  const db = getDb();
  const [row] = await db
    .select({ active: accounts.active })
    .from(accounts)
    .where(eq(accounts.did, did))
    .limit(1);

  if (!row) return;

  if (row.active === 0) {
    notFound();
  }
}

// ============================================================================
// DID Resolution
// ============================================================================

const idResolver = new IdResolver();

const resolveDidToHandle = cache(async function resolveDidToHandle(did: string): Promise<string> {
  "use cache: redis";
  tagDid(did);

  const didDoc = await idResolver.did.resolve(did);
  for (const aka of didDoc?.alsoKnownAs ?? []) {
    if (typeof aka === "string" && aka.startsWith("at://")) {
      return aka.replace("at://", "");
    }
  }

  throw Error("Could not resolve did: " + did);
});

export const resolveHandleToDid = cache(async function resolveHandleToDid(
  handle: string,
): Promise<string> {
  "use cache: redis";
  tagHandle(handle);

  const did = await idResolver.handle.resolve(handle);
  if (did) {
    return did;
  }
  throw new Error(`Could not resolve handle: ${handle}`);
});

async function batchResolveHandles(dids: string[]): Promise<Map<string, string>> {
  const unique = [...new Set(dids)];
  const results = await Promise.all(unique.map((did) => resolveDidToHandle(did)));
  return new Map(unique.map((did, i) => [did, results[i]]));
}

const fetchBskyAvatar = cache(async function fetchBskyAvatar(
  did: string,
): Promise<string | undefined> {
  "use cache: redis";
  tagAvatar(did);

  const res = await fetch(
    `https://public.api.bsky.app/xrpc/app.bsky.actor.getProfile?actor=${encodeURIComponent(did)}`,
    { signal: AbortSignal.timeout(5000) },
  );
  if (!res.ok) return undefined;

  const profile = await res.json();
  return profile.avatar?.replace("/img/avatar/", "/img/avatar_thumbnail/");
});

export const tryFetchBskyAvatar = cache(async function tryFetchBskyAvatar(
  did: string,
): Promise<string | undefined> {
  try {
    return await fetchBskyAvatar(did);
  } catch {
    return undefined;
  }
});

async function batchResolveUsers(dids: string[]): Promise<Map<string, User>> {
  const unique = [...new Set(dids)];
  if (unique.length === 0) return new Map();

  const [handles, avatars] = await Promise.all([
    Promise.all(unique.map((did) => resolveDidToHandle(did))),
    Promise.all(unique.map((did) => tryFetchBskyAvatar(did))),
  ]);

  return new Map(unique.map((did, i) => [did, { did, handle: handles[i], avatar: avatars[i] }]));
}

function parseStopExternal(
  ext: TrailRecord["stops"][0]["external"],
  authorDid: string,
): ExternalEmbed | undefined {
  if (!ext) return undefined;
  return {
    uri: ext.uri,
    title: ext.title,
    description: ext.description,
    thumb: ext.thumb?.ref?.$link
      ? `https://cdn.bsky.app/img/feed_thumbnail/plain/${authorDid}/${ext.thumb.ref.$link}@jpeg`
      : undefined,
  };
}

// ============================================================================
// Current User
// ============================================================================

export const loadCurrentDid = cache(getCurrentDid);

export const loadCurrentUser = cache(async function loadCurrentUser(): Promise<User | null> {
  const did = await loadCurrentDid();
  if (!did) return null;

  const [handle, avatar] = await Promise.all([resolveDidToHandle(did), tryFetchBskyAvatar(did)]);
  return { did, handle, avatar };
});

// ============================================================================
// Trails List (Home Page)
// ============================================================================

const HALF_LIFE_HOURS = 12;
const DECAY_RATE = Math.log(2) / HALF_LIFE_HOURS; // ≈ 0.0578
const RECENT_ACTIVITY_WINDOW_HOURS = 3;
const GRACE_PERIOD_HOURS = 3; // new trails get exposure
const MIN_ACTIVITY_FOR_RANKING = 5; // need this many to rank in main tier

export const loadTrails = cache(async function loadTrails(): Promise<TrailCardData[]> {
  const db = getDb();

  const trailRows = await db.execute<{
    uri: string;
    cid: string;
    author_did: string;
    rkey: string;
    record: TrailRecord;
    created_at: Date;
    indexed_at: Date;
  }>(sql`
    WITH activity AS (
      SELECT trail_uri, author_did, created_at FROM walks
      UNION ALL
      SELECT trail_uri, author_did, created_at FROM completions
    )
    SELECT
      t.uri, t.cid, t.author_did, t.rkey, t.record, t.created_at, t.indexed_at
    FROM trails t
    LEFT JOIN activity a ON a.trail_uri = t.uri AND a.author_did != t.author_did
    LEFT JOIN accounts acc ON acc.did = t.author_did
    WHERE COALESCE(acc.active, 1) = 1
      AND COALESCE(acc.shadowban, 0) = 0
    GROUP BY t.uri, t.cid, t.author_did, t.rkey, t.record, t.created_at, t.indexed_at
    ORDER BY
      -- Tier: 0 = grace period (new), 1 = proven (enough activity), 2 = unproven
      CASE
        WHEN EXTRACT(EPOCH FROM (NOW() - t.created_at))/3600 < ${GRACE_PERIOD_HOURS}::float THEN 0
        WHEN COUNT(a.trail_uri) >= ${MIN_ACTIVITY_FOR_RANKING} THEN 1
        ELSE 2
      END,
      -- Score within tier: (1 + sqrt(recent_activity)) * recency_decay
      (1 + SQRT(SUM(CASE WHEN EXTRACT(EPOCH FROM (NOW() - a.created_at))/3600 < ${RECENT_ACTIVITY_WINDOW_HOURS}::float THEN 1 ELSE 0 END)))
       * EXP(${DECAY_RATE}::float * -1.0 * EXTRACT(EPOCH FROM (NOW() - GREATEST(t.created_at, COALESCE(MAX(a.created_at), t.created_at))))/3600)
       DESC
    LIMIT 100
  `);

  if (trailRows.rows.length === 0) return [];

  const authorDids = trailRows.rows.map((t) => t.author_did);
  const userMap = await batchResolveUsers(authorDids);

  return trailRows.rows.map((row) => {
    const creator = userMap.get(row.author_did)!;
    return {
      uri: row.uri,
      rkey: row.rkey,
      creatorHandle: creator.handle,
      title: row.record.title,
      description: row.record.description,
      accentColor: row.record.accentColor,
      backgroundColor: row.record.backgroundColor,
      creator,
      stopsCount: row.record.stops.length,
      createdAt: row.created_at,
    };
  });
});

export const loadTrailCardByUri = cache(async function loadTrailCardByUri(
  uri: string,
): Promise<TrailCardData | null> {
  const db = getDb();
  const parsed = new AtUri(uri);
  const did = parsed.host.startsWith("did:") ? parsed.host : await resolveHandleToDid(parsed.host);

  const trailRows = await db
    .select()
    .from(trails)
    .where(eq(trails.uri, `at://${did}/${parsed.collection}/${parsed.rkey}`))
    .limit(1);

  if (trailRows.length === 0) return null;

  const row = trailRows[0];
  const userMap = await batchResolveUsers([row.authorDid]);
  const creator = userMap.get(row.authorDid);
  if (!creator) return null;

  return {
    uri: row.uri,
    rkey: row.rkey,
    creatorHandle: creator.handle,
    title: row.record.title,
    description: row.record.description,
    accentColor: row.record.accentColor,
    backgroundColor: row.record.backgroundColor,
    creator,
    stopsCount: row.record.stops.length,
    createdAt: row.createdAt,
  };
});

export const loadTrailActiveWalkers = cache(async function loadTrailActiveWalkers(
  trailUri: string,
): Promise<User[]> {
  const db = getDb();

  const recentWalks = await db.execute<{
    author_did: string;
  }>(sql`
    SELECT author_did FROM (
      SELECT author_did,
        ROW_NUMBER() OVER (ORDER BY last_activity DESC) as rn
      FROM (
        SELECT DISTINCT ON (w.author_did) w.author_did,
          COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) as last_activity
        FROM walks w
        LEFT JOIN accounts a ON a.did = w.author_did
        WHERE w.trail_uri = ${trailUri}
          AND COALESCE(a.active, 1) = 1
        ORDER BY w.author_did, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
      ) unique_walkers
    ) ranked
    WHERE rn <= 5
  `);

  if (recentWalks.rows.length === 0) return [];

  const walkerDids = recentWalks.rows.map((w) => w.author_did);
  const walkerUserMap = await batchResolveUsers(walkerDids);

  return recentWalks.rows.map((row) => walkerUserMap.get(row.author_did)!).filter(Boolean);
});

// ============================================================================
// Trail Detail
// ============================================================================

async function loadTrailRecord(trailUri: string) {
  const db = getDb();
  const [row] = await db.select().from(trails).where(eq(trails.uri, trailUri)).limit(1);
  if (!row) throw new Error("Trail not found");
  return row;
}

async function loadUserWalk(trailUri: string, userDid: string | null) {
  if (!userDid) return null;
  const db = getDb();
  const result = await db.execute<{
    uri: string;
    record: typeof walks.$inferSelect.record;
    created_at: Date;
  }>(sql`
    SELECT uri, record, created_at
    FROM walks
    WHERE author_did = ${userDid} AND trail_uri = ${trailUri}
    ORDER BY COALESCE((record->>'updatedAt')::timestamptz, created_at) DESC
    LIMIT 1
  `);
  const row = result.rows[0];
  if (!row) return null;
  return {
    uri: row.uri,
    visitedStops: row.record.visitedStops,
    createdAt: new Date(row.created_at),
    updatedAt: row.record.updatedAt ? new Date(row.record.updatedAt) : new Date(row.created_at),
  };
}

type WalkData = {
  uri: string;
  cid: string;
  authorDid: string;
  rkey: string;
  trailUri: string;
  record: typeof walks.$inferSelect.record;
  createdAt: Date;
  indexedAt: Date;
};

type CompletionData = {
  uri: string;
  cid: string;
  authorDid: string;
  rkey: string;
  trailUri: string;
  record: typeof completions.$inferSelect.record;
  createdAt: Date;
  indexedAt: Date;
};

async function loadTrailWalks(
  trailUri: string,
): Promise<{ walksData: WalkData[]; walkUserMap: Map<string, User> }> {
  const db = getDb();

  const trailWalks = await db.execute<{
    uri: string;
    cid: string;
    author_did: string;
    rkey: string;
    trail_uri: string;
    record: typeof walks.$inferSelect.record;
    created_at: Date;
    indexed_at: Date;
  }>(sql`
    SELECT DISTINCT ON (w.author_did) w.uri, w.cid, w.author_did, w.rkey, w.trail_uri, w.record, w.created_at, w.indexed_at
    FROM walks w
    LEFT JOIN accounts a ON a.did = w.author_did
    WHERE w.trail_uri = ${trailUri}
      AND COALESCE(a.active, 1) = 1
    ORDER BY w.author_did, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
  `);

  const walksData = trailWalks.rows.map((row) => ({
    uri: row.uri,
    cid: row.cid,
    authorDid: row.author_did,
    rkey: row.rkey,
    trailUri: row.trail_uri,
    record: row.record,
    createdAt: new Date(row.created_at),
    indexedAt: new Date(row.indexed_at),
  }));

  const walkDids = walksData.map((w) => w.authorDid);
  const walkUserMap = await batchResolveUsers(walkDids);

  return { walksData, walkUserMap };
}

async function loadTrailCompletions(
  trailUri: string,
): Promise<{ completionsData: CompletionData[]; completionUserMap: Map<string, User> }> {
  const db = getDb();

  const trailCompletions = await db.execute<{
    uri: string;
    cid: string;
    author_did: string;
    rkey: string;
    trail_uri: string;
    record: typeof completions.$inferSelect.record;
    created_at: Date;
    indexed_at: Date;
  }>(sql`
    SELECT c.uri, c.cid, c.author_did, c.rkey, c.trail_uri, c.record, c.created_at, c.indexed_at
    FROM completions c
    LEFT JOIN accounts a ON a.did = c.author_did
    WHERE c.trail_uri = ${trailUri}
      AND COALESCE(a.active, 1) = 1
    ORDER BY c.created_at DESC
    LIMIT 100
  `);

  const completionsData = trailCompletions.rows.map((row) => ({
    uri: row.uri,
    cid: row.cid,
    authorDid: row.author_did,
    rkey: row.rkey,
    trailUri: row.trail_uri,
    record: row.record,
    createdAt: new Date(row.created_at),
    indexedAt: new Date(row.indexed_at),
  }));

  const completionDids = completionsData.map((c) => c.authorDid);
  const completionUserMap = await batchResolveUsers(completionDids);

  return { completionsData, completionUserMap };
}

function buildCompletionsList(
  completionsData: CompletionData[],
  userMap: Map<string, User>,
  currentUserDid: string | null,
) {
  return completionsData.map((c) => ({
    user: userMap.get(c.authorDid)!,
    timestamp: c.createdAt,
    completionUri: c.uri,
    isYou: c.authorDid === currentUserDid,
  }));
}

function buildWalkersList(
  walksData: WalkData[],
  completionsData: CompletionData[],
  walkUserMap: Map<string, User>,
  completionUserMap: Map<string, User>,
  currentUserDid: string | null,
) {
  const activityMap = new Map<
    string,
    { user: User; isYou: boolean; key: string; timestamp: Date }
  >();

  for (const c of completionsData) {
    activityMap.set(c.authorDid, {
      user: completionUserMap.get(c.authorDid)!,
      isYou: c.authorDid === currentUserDid,
      key: c.uri,
      timestamp: c.createdAt,
    });
  }

  for (const walk of walksData) {
    const timestamp = walk.record.updatedAt ? new Date(walk.record.updatedAt) : walk.createdAt;
    const existing = activityMap.get(walk.authorDid);
    if (!existing || timestamp > existing.timestamp) {
      activityMap.set(walk.authorDid, {
        user: walkUserMap.get(walk.authorDid)!,
        isYou: walk.authorDid === currentUserDid,
        key: walk.uri,
        timestamp,
      });
    }
  }

  return Array.from(activityMap.values())
    .sort((a, b) => b.timestamp.getTime() - a.timestamp.getTime())
    .map(({ timestamp: _, ...rest }) => rest);
}

function buildWalkersAtStopMap(walksData: WalkData[], userMap: Map<string, User>) {
  const stopMap = new Map<string, User[]>();
  for (const walk of walksData) {
    const visited = walk.record.visitedStops;
    if (visited.length > 0) {
      const currentStop = visited[visited.length - 1];
      const user = userMap.get(walk.authorDid);
      if (user) {
        const list = stopMap.get(currentStop) ?? [];
        list.push(user);
        stopMap.set(currentStop, list);
      }
    }
  }
  return stopMap;
}

export const loadTrailDetail = cache(async function loadTrailDetail(
  handle: string,
  rkey: string,
): Promise<TrailDetailData> {
  const currentUserDidPromise = loadCurrentDid();
  const creatorDidPromise = resolveHandleToDid(handle);

  const creatorDid = await creatorDidPromise;

  await requireActiveAccount(creatorDid);

  const trailUri = `at://${creatorDid}/app.sidetrail.trail/${rkey}`;

  const walksDataPromise = loadTrailWalks(trailUri);
  const completionsDataPromise = loadTrailCompletions(trailUri);

  const [trail, currentUserDid, creatorAvatar] = await Promise.all([
    loadTrailRecord(trailUri),
    currentUserDidPromise,
    tryFetchBskyAvatar(creatorDid),
  ]);

  const yourWalkPromise = loadUserWalk(trailUri, currentUserDid);

  const creator: User = { did: creatorDid, handle, avatar: creatorAvatar };

  const walkersAtStopPromise = walksDataPromise.then(({ walksData, walkUserMap }) =>
    buildWalkersAtStopMap(walksData, walkUserMap),
  );

  const completionsPromise = completionsDataPromise.then(({ completionsData, completionUserMap }) =>
    buildCompletionsList(completionsData, completionUserMap, currentUserDid),
  );

  const walkersPromise = Promise.all([walksDataPromise, completionsDataPromise]).then(
    ([{ walksData, walkUserMap }, { completionsData, completionUserMap }]) =>
      buildWalkersList(walksData, completionsData, walkUserMap, completionUserMap, currentUserDid),
  );

  const stops: TrailStop[] = trail.record.stops.map((stop) => ({
    tid: stop.tid,
    title: stop.title,
    content: stop.content,
    buttonText: stop.buttonText,
    external: parseStopExternal(stop.external, creatorDid),
    walkersHere: walkersAtStopPromise.then((m) => m.get(stop.tid) ?? []),
  }));

  return {
    header: {
      uri: trail.uri,
      cid: trail.cid,
      rkey,
      title: trail.record.title,
      description: trail.record.description,
      stopsCount: trail.record.stops.length,
      accentColor: trail.record.accentColor,
      backgroundColor: trail.record.backgroundColor,
      creator,
      createdAt: trail.createdAt,
    },
    stops,
    yourWalk: await yourWalkPromise,
    walkers: walkersPromise,
    completions: completionsPromise,
  };
});

export type TrailHeaderForOG = {
  title: string;
  description: string;
  stopsCount: number;
  accentColor: string;
  backgroundColor: string;
  creatorHandle: string;
};

export const loadTrailDetailForOG = cache(async function loadTrailDetailForOG(
  handle: string,
  rkey: string,
): Promise<TrailHeaderForOG> {
  "use cache";
  cacheLife("max");

  const creatorDid = await resolveHandleToDid(handle);
  const trailUri = `at://${creatorDid}/app.sidetrail.trail/${rkey}`;
  const trail = await loadTrailRecord(trailUri);

  return {
    title: trail.record.title,
    description: trail.record.description,
    stopsCount: trail.record.stops.length,
    accentColor: trail.record.accentColor,
    backgroundColor: trail.record.backgroundColor,
    creatorHandle: handle,
  };
});

// ============================================================================
// User's Walks
// ============================================================================

export const loadWalks = cache(async function loadWalks(userDid?: string): Promise<WalkCardData[]> {
  const did = userDid ?? (await loadCurrentDid());
  if (!did) return [];

  const db = getDb();

  const walksWithTrails = await db.execute<{
    walk_uri: string;
    walk_rkey: string;
    walk_record: typeof walks.$inferSelect.record;
    walk_created_at: Date;
    trail_uri: string;
    trail_rkey: string;
    trail_author_did: string;
    trail_record: TrailRecord;
  }>(sql`
    SELECT DISTINCT ON (w.trail_uri)
      w.uri as walk_uri, w.rkey as walk_rkey, w.record as walk_record, w.created_at as walk_created_at,
      t.uri as trail_uri, t.rkey as trail_rkey, t.author_did as trail_author_did, t.record as trail_record
    FROM walks w
    INNER JOIN trails t ON w.trail_uri = t.uri
    WHERE w.author_did = ${did}
    ORDER BY w.trail_uri, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
  `);

  if (walksWithTrails.rows.length === 0) return [];

  const creatorDids = walksWithTrails.rows.map((r) => r.trail_author_did);
  const handleMap = await batchResolveHandles(creatorDids);

  const sortedWalks = walksWithTrails.rows.sort((a, b) => {
    const aUpdated = a.walk_record.updatedAt
      ? new Date(a.walk_record.updatedAt)
      : new Date(a.walk_created_at);
    const bUpdated = b.walk_record.updatedAt
      ? new Date(b.walk_record.updatedAt)
      : new Date(b.walk_created_at);
    return bUpdated.getTime() - aUpdated.getTime();
  });

  return sortedWalks.map((row) => ({
    walkRkey: row.walk_rkey,
    walkerDid: did,
    trailRkey: row.trail_rkey,
    trailCreatorHandle: handleMap.get(row.trail_author_did) ?? row.trail_author_did,
    title: row.trail_record.title,
    description: row.trail_record.description,
    accentColor: row.trail_record.accentColor,
    backgroundColor: row.trail_record.backgroundColor,
    stops: row.trail_record.stops.map((stop) => ({
      tid: stop.tid,
      title: stop.title,
      content: stop.content,
      buttonText: stop.buttonText,
      external: parseStopExternal(stop.external, row.trail_author_did),
    })),
    visitedStops: row.walk_record.visitedStops,
    updatedAt: row.walk_record.updatedAt
      ? new Date(row.walk_record.updatedAt)
      : new Date(row.walk_created_at),
  }));
});

export const loadWalkingBadges = cache(async function loadWalkingBadges(): Promise<
  Array<{ accentColor: string; key: string }>
> {
  const currentDid = await loadCurrentDid();
  if (!currentDid) return [];

  const db = getDb();

  const walksWithTrails = await db.execute<{
    trail_uri: string;
    accent_color: string;
  }>(sql`
    SELECT trail_uri, accent_color FROM (
      SELECT DISTINCT ON (w.trail_uri)
        w.trail_uri,
        t.record->>'accentColor' as accent_color,
        COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) as last_activity
      FROM walks w
      INNER JOIN trails t ON w.trail_uri = t.uri
      WHERE w.author_did = ${currentDid}
      ORDER BY w.trail_uri, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
    ) unique_walks
    ORDER BY last_activity DESC
    LIMIT 3
  `);

  return walksWithTrails.rows.map((row) => ({
    key: row.trail_uri,
    accentColor: row.accent_color,
  }));
});

export const loadDraftsBadges = cache(async function loadDraftsBadges(): Promise<
  Array<{ accentColor: string; key: string }>
> {
  return [];
});

// ============================================================================
// User Published Trails
// ============================================================================

export const loadUserPublishedTrails = cache(async function loadUserPublishedTrails(
  handle: string,
): Promise<TrailCardData[]> {
  const userDid = await resolveHandleToDid(handle);

  await requireActiveAccount(userDid);

  const db = getDb();

  const userTrails = await db
    .select()
    .from(trails)
    .where(eq(trails.authorDid, userDid))
    .orderBy(desc(trails.createdAt))
    .limit(100);

  const user: User = { did: userDid, handle };

  return userTrails.map((trail) => ({
    uri: trail.uri,
    rkey: trail.rkey,
    creatorHandle: handle,
    title: trail.record.title,
    description: trail.record.description,
    accentColor: trail.record.accentColor,
    backgroundColor: trail.record.backgroundColor,
    creator: user,
    stopsCount: trail.record.stops.length,
    createdAt: trail.createdAt,
  }));
});

// ============================================================================
// User Completed Trails
// ============================================================================

export const loadUserCompletedTrails = cache(async function loadUserCompletedTrails(
  handle: string,
): Promise<CompletedTrailData[]> {
  const userDid = await resolveHandleToDid(handle);

  await requireActiveAccount(userDid);

  const db = getDb();

  const userCompletions = await db
    .select()
    .from(completions)
    .where(eq(completions.authorDid, userDid))
    .orderBy(desc(completions.createdAt))
    .limit(100);

  const completedTrailUris = [...new Set(userCompletions.map((c) => c.trailUri))];
  const completedTrailsData =
    completedTrailUris.length > 0
      ? await db.select().from(trails).where(inArray(trails.uri, completedTrailUris))
      : [];

  const trailDataMap = new Map(completedTrailsData.map((t) => [t.uri, t]));
  const creatorDids = completedTrailsData.map((t) => t.authorDid);
  const creatorUserMap = await batchResolveUsers(creatorDids);

  const seenUris = new Set<string>();
  const completedTrails: CompletedTrailData[] = [];
  for (const c of userCompletions) {
    if (seenUris.has(c.trailUri)) continue;
    seenUris.add(c.trailUri);

    const trail = trailDataMap.get(c.trailUri);
    if (!trail) continue;

    completedTrails.push({
      uri: trail.uri,
      rkey: trail.rkey,
      title: trail.record.title,
      description: trail.record.description,
      accentColor: trail.record.accentColor,
      backgroundColor: trail.record.backgroundColor,
      stopsCount: trail.record.stops.length,
      creator: creatorUserMap.get(trail.authorDid) ?? {
        did: trail.authorDid,
        handle: trail.authorDid,
      },
      completedAt: c.createdAt,
    });
  }

  return completedTrails;
});
