import {
  DEFAULT_PAGE_SIZE,
  TRAIL_POSTS_PREVIEW,
  type Trail,
  type TrailViewerRole,
} from '@crumbs/core/schemas'
import {
  and,
  asc,
  desc,
  eq,
  exists,
  inArray,
  isNull,
  ne,
  or,
  sql,
  type SQL,
} from 'drizzle-orm'
import {HTTPException} from 'hono/http-exception'

import {db, schema} from '../db/client'
import {keysetCursorWhere, keysetOrder, toPage} from '../lib/cursor'
import {firstOr404} from '../lib/require'
import {publicImageUrl} from '../lib/source-image'
import {countInt} from '../lib/sql'
import {crumbJoinedSelect, toCrumbResponses} from './crumbs'
import {
  listFriendIds,
  listProfiles,
  requireViewableAuthor,
} from './friends'
import {listTrailPosts} from './trail-posts'
import {loadReactionSummaries} from './trail-reactions'
import {isAcceptedCollaborator} from './visibility'

type TrailRow = typeof schema.trails.$inferSelect

export type TrailRole = 'owner' | 'collaborator'

// quick-access is a glanceable strip, not a list; a small cap is plenty
const QUICK_TRAILS_LIMIT = 50

type MemberRow = {handle: string; avatarRef: string | null}

// accepted collaborators, oldest first, capped for avatar stacks
const memberRowsSql = sql<MemberRow[]>`(
  select coalesce(
    json_agg(json_build_object('handle', handle, 'avatarRef', avatar_ref)),
    '[]'::json
  ) from (
    select u.handle, u.avatar_ref
    from trail_collaborators tc
    join users u on u.id = tc.user_id
    where tc.trail_id = ${schema.trails.id}
      and tc.status = 'accepted'
    order by tc.created_at
    limit 4
  ) members
)`

// cover fallback: the first crumb (by position) with an effective image,
// mirroring crumbImageUrl's precedence (manual > source display > asset)
export const trailCoverFallbackRefSql = sql<string | null>`(
  select coalesce(c.image_ref, s.image_ref, s.asset_ref)
  from trail_crumbs tc
  join crumbs c on c.id = tc.crumb_id
  left join sources s on s.id = c.source_id
  where tc.trail_id = ${schema.trails.id}
    and coalesce(c.image_ref, s.image_ref, s.asset_ref) is not null
  order by tc.position
  limit 1
)`

// shared summary projection (counts, viewer pin/unseen state, member stack)
// up to the group-by; callers add their own where/order/limit
const trailSummarySelect = (viewerId: string, crumbId?: string) =>
  db
    .select({
      trail: schema.trails,
      authorHandle: schema.users.handle,
      authorAvatarRef: schema.users.avatarRef,
      crumbCount: countInt(schema.trailCrumbs.crumbId).as(
        'crumb_count',
      ),
      containsCrumb: crumbId
        ? sql<boolean>`bool_or(${schema.trailCrumbs.crumbId} = ${crumbId})`.as(
            'contains_crumb',
          )
        : sql<boolean>`false`.as('contains_crumb'),
      pinned:
        sql<boolean>`${schema.trailUserStates.pinnedAt} is not null`.as(
          'pinned',
        ),
      unseenCount: sql<number>`(count(*) filter (
        where ${schema.trailUserStates.lastViewedAt} is not null
          and ${schema.trailCrumbs.createdAt} > ${schema.trailUserStates.lastViewedAt}
      ) + coalesce((
        select count(*) from trail_posts p
        where p.trail_id = ${schema.trails.id}
          and p.author_id <> ${viewerId}
          and ${schema.trailUserStates.lastViewedAt} is not null
          and p.created_at > ${schema.trailUserStates.lastViewedAt}
      ), 0))::int`.as('unseen_count'),
      members: memberRowsSql.as('members'),
      coverFallbackRef: trailCoverFallbackRefSql.as(
        'cover_fallback_ref',
      ),
    })
    .from(schema.trails)
    .innerJoin(
      schema.users,
      eq(schema.users.id, schema.trails.authorId),
    )
    .leftJoin(
      schema.trailCrumbs,
      eq(schema.trailCrumbs.trailId, schema.trails.id),
    )
    .leftJoin(
      schema.trailUserStates,
      and(
        eq(schema.trailUserStates.trailId, schema.trails.id),
        eq(schema.trailUserStates.userId, viewerId),
      ),
    )
    .groupBy(
      schema.trails.id,
      schema.users.handle,
      schema.users.avatarRef,
      schema.trailUserStates.pinnedAt,
      schema.trailUserStates.lastViewedAt,
    )

// inferred from the projection itself so the two can never drift
type SummaryRow = Awaited<
  ReturnType<typeof trailSummarySelect>
>[number]

const toSummary = (r: SummaryRow, withContains: boolean) =>
  toTrailSummary(r.trail, toAuthor(r), r.crumbCount, {
    containsCrumb: withContains
      ? Boolean(r.containsCrumb)
      : undefined,
    pinned: Boolean(r.pinned),
    unseenCount: r.unseenCount,
    members: r.members,
    coverFallbackRef: r.coverFallbackRef,
  })

// keyset-paginated trail summaries, newest first. subjectId scopes whose
// trails are listed; viewerId scopes the pin/unseen state joined onto them —
// they only coincide when a user lists their own trails
export const listTrailSummaries = async ({
  subjectId,
  viewerId,
  crumbId,
  extraWhere,
  includeShared = false,
  limit = DEFAULT_PAGE_SIZE,
  cursor,
}: {
  subjectId: string
  viewerId: string
  crumbId?: string
  extraWhere?: SQL
  includeShared?: boolean
  limit?: number
  cursor?: string
}) => {
  const ownWhere = eq(schema.trails.authorId, subjectId)
  const accessWhere = includeShared
    ? or(ownWhere, isAcceptedCollaborator(subjectId))
    : ownWhere

  const rows = await trailSummarySelect(viewerId, crumbId)
    .where(
      and(
        accessWhere,
        extraWhere,
        keysetCursorWhere(
          schema.trails.createdAt,
          schema.trails.id,
          cursor,
        ),
      ),
    )
    .orderBy(
      ...keysetOrder(schema.trails.createdAt, schema.trails.id),
    )
    .limit(limit + 1)

  const {page, nextCursor} = toPage(rows, limit, (r) => ({
    createdAt: r.trail.createdAt,
    id: r.trail.id,
  }))
  return {
    trails: page.map((r) => toSummary(r, crumbId != null)),
    nextCursor,
  }
}

// summaries for a known id set with the viewer's pin/unseen state, keyed by
// id; ordering stays with the caller
export const loadTrailSummariesByIds = async (
  viewerId: string,
  trailIds: string[],
): Promise<Map<string, Trail>> => {
  if (!trailIds.length) return new Map()
  const rows = await trailSummarySelect(viewerId).where(
    inArray(schema.trails.id, trailIds),
  )
  return new Map(rows.map((r) => [r.trail.id, toSummary(r, false)]))
}

// bounded quick-access strip for home: trails the user pinned or that
// others shared with them, pinned first. never paginated — a deliberately
// small set, unlike the full index
export const listQuickTrails = async (userId: string) => {
  const rows = await trailSummarySelect(userId)
    .where(
      and(
        or(
          eq(schema.trails.authorId, userId),
          isAcceptedCollaborator(userId),
        ),
        or(
          sql`${schema.trailUserStates.pinnedAt} is not null`,
          ne(schema.trails.authorId, userId),
        ),
      ),
    )
    .orderBy(
      sql`${schema.trailUserStates.pinnedAt} desc nulls last`,
      desc(schema.trails.updatedAt),
    )
    .limit(QUICK_TRAILS_LIMIT)
  return rows.map((r) => toSummary(r, false))
}

type TrailDetailRow = {
  trail: TrailRow
  authorHandle: string
  authorAvatarRef: string | null
  coverFallbackRef: string | null
}

// the full trail screen payload: summary plus ordered crumbs with the
// viewer's reactions, collaborators, pin state and a posts preview; a
// member's visit also marks the trail seen
export const getTrailDetail = async (
  {
    trail,
    authorHandle,
    authorAvatarRef,
    coverFallbackRef,
  }: TrailDetailRow,
  role: TrailRole | null,
  viewerId: string,
) => {
  const viewerRole: TrailViewerRole = role ?? 'viewer'

  const [crumbRows, collaboratorRows, stateRows, {posts}] =
    await Promise.all([
      crumbJoinedSelect()
        .innerJoin(
          schema.trailCrumbs,
          eq(schema.trailCrumbs.crumbId, schema.crumbs.id),
        )
        .where(eq(schema.trailCrumbs.trailId, trail.id))
        .orderBy(asc(schema.trailCrumbs.position)),
      db
        .select({userId: schema.trailCollaborators.userId})
        .from(schema.trailCollaborators)
        .where(
          and(
            eq(schema.trailCollaborators.trailId, trail.id),
            eq(schema.trailCollaborators.status, 'accepted'),
          ),
        )
        .orderBy(asc(schema.trailCollaborators.createdAt)),
      db
        .select({pinnedAt: schema.trailUserStates.pinnedAt})
        .from(schema.trailUserStates)
        .where(
          and(
            eq(schema.trailUserStates.trailId, trail.id),
            eq(schema.trailUserStates.userId, viewerId),
          ),
        )
        .limit(1),
      listTrailPosts(trail.id, {
        viewerId,
        limit: TRAIL_POSTS_PREVIEW,
      }),
    ])
  const pinned = stateRows[0]?.pinnedAt != null

  const [crumbResponses, collaborators, reactionsByCrumb] =
    await Promise.all([
      toCrumbResponses(crumbRows),
      listProfiles(
        viewerId,
        collaboratorRows.map((r) => r.userId),
      ),
      loadReactionSummaries(
        viewerId,
        crumbRows.map((r) => r.crumb.id),
        {trailId: trail.id},
      ),
    ])
  const crumbs = crumbResponses.map((crumb) => ({
    ...crumb,
    reactions: reactionsByCrumb.get(crumb.id) ?? [],
  }))

  if (role) await touchTrailView(trail.id, viewerId)

  return {
    ...toTrailSummary(
      trail,
      {handle: authorHandle, avatarRef: authorAvatarRef},
      crumbs.length,
      {pinned, coverFallbackRef},
    ),
    crumbs,
    collaborators,
    viewerRole,
    posts,
  }
}

type TrailSummaryExtras = {
  containsCrumb?: boolean
  pinned?: boolean
  unseenCount?: number
  members?: MemberRow[]
  coverFallbackRef?: string | null
}

export type TrailAuthor = {handle: string; avatarRef: string | null}

const toAuthor = (r: {
  authorHandle: string
  authorAvatarRef: string | null
}): TrailAuthor => ({
  handle: r.authorHandle,
  avatarRef: r.authorAvatarRef,
})

export const toTrailSummary = (
  row: TrailRow,
  author: TrailAuthor,
  crumbCount: number,
  extras: TrailSummaryExtras = {},
): Trail => ({
  id: row.id,
  authorId: row.authorId,
  authorHandle: author.handle,
  authorAvatarUrl: publicImageUrl(author.avatarRef),
  name: row.name,
  description: row.description,
  coverImageUrl: publicImageUrl(
    row.coverImageRef ?? extras.coverFallbackRef ?? null,
  ),
  crumbCount,
  ...(extras.containsCrumb !== undefined && {
    containsCrumb: extras.containsCrumb,
  }),
  pinned: extras.pinned ?? false,
  unseenCount: extras.unseenCount ?? 0,
  members: (extras.members ?? []).map((m) => ({
    handle: m.handle,
    avatarUrl: publicImageUrl(m.avatarRef),
  })),
  createdAt: row.createdAt.toISOString(),
  updatedAt: row.updatedAt.toISOString(),
})

export const getTrailRole = async (
  trail: TrailRow,
  userId: string,
): Promise<TrailRole | null> => {
  if (trail.authorId === userId) return 'owner'
  const [row] = await db
    .select({id: schema.trailCollaborators.id})
    .from(schema.trailCollaborators)
    .where(
      and(
        eq(schema.trailCollaborators.trailId, trail.id),
        eq(schema.trailCollaborators.userId, userId),
        eq(schema.trailCollaborators.status, 'accepted'),
      ),
    )
    .limit(1)
  return row ? 'collaborator' : null
}

// members (owner or accepted collaborator) may modify trail contents
export const requireTrailMember = async (
  trailId: string,
  userId: string,
): Promise<{trail: TrailRow; role: TrailRole}> => {
  const [trail] = await db
    .select()
    .from(schema.trails)
    .where(eq(schema.trails.id, trailId))
    .limit(1)
  if (!trail) throw new HTTPException(404, {message: 'not_found'})
  const role = await getTrailRole(trail, userId)
  if (!role) throw new HTTPException(404, {message: 'not_found'})
  return {trail, role}
}

// trail read visibility: members see it by role; anyone else must pass the
// friend-viewable check on the author and reads as a plain viewer (a null
// role). returns the joined display row so getTrailDetail needs no re-fetch
export const requireTrailViewer = async (
  trailId: string,
  userId: string,
): Promise<{row: TrailDetailRow; role: TrailRole | null}> => {
  const [row] = await db
    .select({
      trail: schema.trails,
      authorHandle: schema.users.handle,
      authorAvatarRef: schema.users.avatarRef,
      coverFallbackRef: trailCoverFallbackRefSql,
    })
    .from(schema.trails)
    .innerJoin(
      schema.users,
      eq(schema.users.id, schema.trails.authorId),
    )
    .where(eq(schema.trails.id, trailId))
    .limit(1)
  if (!row) throw new HTTPException(404, {message: 'not_found'})
  const role = await getTrailRole(row.trail, userId)
  if (!role) await requireViewableAuthor(userId, row.trail.authorId)
  return {row, role}
}

// guard + load for cover writes: the trail must exist and belong to
// the user, else 404
export const requireOwnTrailCover = async (
  id: string,
  userId: string,
): Promise<{coverImageRef: string | null}> =>
  firstOr404(
    await db
      .select({coverImageRef: schema.trails.coverImageRef})
      .from(schema.trails)
      .where(
        and(
          eq(schema.trails.id, id),
          eq(schema.trails.authorId, userId),
        ),
      )
      .limit(1),
  )

// non-owners may only delete rows they authored; a missing row passes so
// the delete's own 404 stays the source of truth
export const requireOwnerOrRowAuthor = async (
  role: TrailRole,
  userId: string,
  loadAuthorId: () => Promise<string | undefined>,
): Promise<void> => {
  if (role === 'owner') return
  const authorId = await loadAuthorId()
  if (authorId !== undefined && authorId !== userId)
    throw new HTTPException(403, {message: 'forbidden'})
}

// live (unrevoked) share link with the trail it opens, or null
export const findActiveShareLink = async (token: string) => {
  const [row] = await db
    .select({
      trail: schema.trails,
      coverFallbackRef: trailCoverFallbackRefSql,
      createdBy: schema.trailShareLinks.createdBy,
    })
    .from(schema.trailShareLinks)
    .innerJoin(
      schema.trails,
      eq(schema.trails.id, schema.trailShareLinks.trailId),
    )
    .where(
      and(
        eq(schema.trailShareLinks.token, token),
        isNull(schema.trailShareLinks.revokedAt),
      ),
    )
    .limit(1)
  return row ?? null
}

export const touchTrailView = async (
  trailId: string,
  userId: string,
): Promise<void> => {
  await db
    .insert(schema.trailUserStates)
    .values({trailId, userId, lastViewedAt: new Date()})
    .onConflictDoUpdate({
      target: [
        schema.trailUserStates.trailId,
        schema.trailUserStates.userId,
      ],
      set: {lastViewedAt: new Date()},
    })
}

// owner + accepted collaborators, for mention resolution and push fan-out
export const listTrailMembers = async (trail: {
  id: string
  authorId: string
}): Promise<{id: string; handle: string}[]> =>
  db
    .select({id: schema.users.id, handle: schema.users.handle})
    .from(schema.users)
    .where(
      or(
        eq(schema.users.id, trail.authorId),
        exists(
          db
            .select({id: schema.trailCollaborators.id})
            .from(schema.trailCollaborators)
            .where(
              and(
                eq(schema.trailCollaborators.trailId, trail.id),
                eq(schema.trailCollaborators.userId, schema.users.id),
                eq(schema.trailCollaborators.status, 'accepted'),
              ),
            ),
        ),
      ),
    )

// trails containing a crumb that the viewer may read — the same rule as
// requireTrailViewer (member, or friend of the trail's author), applied as
// a filter. newest first; unpaged, a crumb belongs to few trails
export const listTrailsContainingCrumb = async (
  viewerId: string,
  crumbId: string,
): Promise<Trail[]> => {
  const friendIds = await listFriendIds(viewerId)
  const rows = await trailSummarySelect(viewerId)
    .where(
      and(
        exists(
          db
            .select({n: sql`1`})
            .from(schema.trailCrumbs)
            .where(
              and(
                eq(schema.trailCrumbs.trailId, schema.trails.id),
                eq(schema.trailCrumbs.crumbId, crumbId),
              ),
            ),
        ),
        or(
          eq(schema.trails.authorId, viewerId),
          isAcceptedCollaborator(viewerId),
          friendIds.length
            ? inArray(schema.trails.authorId, friendIds)
            : undefined,
        ),
      ),
    )
    .orderBy(
      ...keysetOrder(schema.trails.createdAt, schema.trails.id),
    )
  return rows.map((r) => toSummary(r, false))
}
