import {groupBy} from '@crumbs/core/collections'
import {pickTagEmoji} from '@crumbs/core/emojis'
import {crossesAntimeridian, type Bbox} from '@crumbs/core/geo'
import {
  TagNameSchema,
  type Crumb,
  type CrumbEvent,
  type CrumbOrigin,
  type MapPlacePoint,
  type PlaceDraft,
  type PlaceRef,
  type TagCount,
  type TagSuggestions,
} from '@crumbs/core/schemas'
import {
  and,
  asc,
  eq,
  exists,
  gte,
  inArray,
  lte,
  ne,
  or,
  sql,
  type SQL,
} from 'drizzle-orm'
import {alias} from 'drizzle-orm/pg-core'
import {HTTPException} from 'hono/http-exception'

import {db, schema, type DbOrTx} from '../db/client'
import {keysetCursorWhere, keysetOrder, toPage} from '../lib/cursor'
import {firstOr404} from '../lib/require'
import {crumbImageUrl} from '../lib/source-image'
import {countInt} from '../lib/sql'
import {
  createProvisionalPlace,
  getPlace,
  resolvePlaceRef,
  toCrumbPlace,
} from './places'

type CrumbRow = typeof schema.crumbs.$inferSelect
type PlaceRow = typeof schema.places.$inferSelect
type EventRow = typeof schema.events.$inferSelect
type SourceRow = typeof schema.sources.$inferSelect

export type CrumbJoinedRow = {
  crumb: CrumbRow
  place: PlaceRow | null
  event: EventRow | null
  eventPlace: PlaceRow | null
  origin: SourceRow | null
}

const eventPlaces = alias(schema.places, 'event_places')

// every crumb read goes through this join: place/event/provenance in one query
export const crumbJoinedSelect = (executor: DbOrTx = db) =>
  executor
    .select({
      crumb: schema.crumbs,
      place: schema.places,
      event: schema.events,
      eventPlace: eventPlaces,
      origin: schema.sources,
    })
    .from(schema.crumbs)
    .leftJoin(
      schema.places,
      eq(schema.crumbs.placeId, schema.places.id),
    )
    .leftJoin(
      schema.events,
      eq(schema.crumbs.eventId, schema.events.id),
    )
    .leftJoin(eventPlaces, eq(schema.events.placeId, eventPlaces.id))
    .leftJoin(
      schema.sources,
      eq(schema.crumbs.sourceId, schema.sources.id),
    )

export const toEventResponse = (row: EventRow): CrumbEvent => ({
  id: row.id,
  name: row.name,
  startsAt: row.startsAt?.toISOString() ?? null,
  endsAt: row.endsAt?.toISOString() ?? null,
  url: row.url,
  status: row.status,
})

const toOriginResponse = (row: SourceRow): CrumbOrigin => ({
  id: row.id,
  kind: row.kind,
  url: row.url,
})

export const toCrumbResponse = (
  row: CrumbJoinedRow,
  tags: {name: string; emoji: string}[],
): Crumb => {
  // an event crumb's "where" is the event's venue
  const place = row.place ?? row.eventPlace
  return {
    id: row.crumb.id,
    authorId: row.crumb.authorId,
    name: row.crumb.name,
    note: row.crumb.note,
    imageUrl: crumbImageUrl(row.crumb, row.origin),
    source: row.crumb.source,
    status: row.crumb.status,
    plannedFor: row.crumb.plannedFor?.toISOString() ?? null,
    place: place ? toCrumbPlace(place) : null,
    event: row.event ? toEventResponse(row.event) : null,
    origin: row.origin ? toOriginResponse(row.origin) : null,
    tags,
    createdAt: row.crumb.createdAt.toISOString(),
    updatedAt: row.crumb.updatedAt.toISOString(),
  }
}

export const toCrumbResponses = async (
  rows: CrumbJoinedRow[],
): Promise<Crumb[]> => {
  const tagsByCrumb = await loadTagsByCrumb(
    rows.map((r) => r.crumb.id),
  )
  return rows.map((r) =>
    toCrumbResponse(r, tagsByCrumb.get(r.crumb.id) ?? []),
  )
}

// paged list of a set of authors' crumbs (newest first) via the shared
// keyset scheme, projected to responses. extraWhere narrows further
// (e.g. by place)
export const listAuthorCrumbs = async ({
  authorIds,
  limit,
  cursor,
  extraWhere,
}: {
  authorIds: string[]
  limit: number
  cursor?: string
  extraWhere?: SQL
}): Promise<{crumbs: Crumb[]; nextCursor: string | null}> => {
  const rows = await crumbJoinedSelect()
    .where(
      and(
        inArray(schema.crumbs.authorId, authorIds),
        extraWhere,
        keysetCursorWhere(
          schema.crumbs.createdAt,
          schema.crumbs.id,
          cursor,
        ),
      ),
    )
    .orderBy(
      ...keysetOrder(schema.crumbs.createdAt, schema.crumbs.id),
    )
    .limit(limit + 1)
  const {page, nextCursor} = toPage(rows, limit, (r) => ({
    createdAt: r.crumb.createdAt,
    id: r.crumb.id,
  }))
  return {crumbs: await toCrumbResponses(page), nextCursor}
}

// densest places win when a viewport holds more; the client shows a
// "zoom in" hint rather than silently dropping the rest
const MAP_PLACE_LIMIT = 1000

// viewport-scoped pins: the viewer's crumbs aggregated to one point per
// place inside the bbox, densest first, capped with a truncated flag
export const listMapPlaces = async (
  userId: string,
  bbox: Bbox,
  {tag, status}: {tag?: string; status?: 'planned' | 'done'},
): Promise<{places: MapPlacePoint[]; truncated: boolean}> => {
  const [minLng, minLat, maxLng, maxLat] = bbox
  const withinLng = crossesAntimeridian(bbox)
    ? or(
        gte(schema.places.longitude, minLng),
        lte(schema.places.longitude, maxLng),
      )
    : and(
        gte(schema.places.longitude, minLng),
        lte(schema.places.longitude, maxLng),
      )
  const rows = await db
    .select({
      placeId: schema.places.id,
      name: schema.places.name,
      latitude: schema.places.latitude,
      longitude: schema.places.longitude,
      category: schema.places.category,
      crumbCount: countInt().as('crumb_count'),
      // representative crumb for single-crumb places, so a tap can open
      // the crumb directly instead of a one-row list
      crumbId: sql<string>`min(${schema.crumbs.id}::text)`.as(
        'crumb_id',
      ),
    })
    .from(schema.crumbs)
    .innerJoin(
      schema.places,
      eq(schema.crumbs.placeId, schema.places.id),
    )
    .where(
      and(
        eq(schema.crumbs.authorId, userId),
        gte(schema.places.latitude, minLat),
        lte(schema.places.latitude, maxLat),
        withinLng,
        status ? eq(schema.crumbs.status, status) : undefined,
        tag
          ? exists(
              db
                .select({n: sql`1`})
                .from(schema.crumbTags)
                .innerJoin(
                  schema.tags,
                  eq(schema.crumbTags.tagId, schema.tags.id),
                )
                .where(
                  and(
                    eq(schema.crumbTags.crumbId, schema.crumbs.id),
                    eq(schema.tags.name, tag),
                  ),
                ),
            )
          : undefined,
      ),
    )
    .groupBy(schema.places.id)
    .orderBy(sql`crumb_count desc`)
    .limit(MAP_PLACE_LIMIT + 1)
  const truncated = rows.length > MAP_PLACE_LIMIT
  const page = truncated ? rows.slice(0, MAP_PLACE_LIMIT) : rows
  return {
    // latitude/longitude are non-null by the bbox where-clause above
    places: page.map(
      (r): MapPlacePoint => ({
        placeId: r.placeId,
        name: r.name,
        latitude: r.latitude!,
        longitude: r.longitude!,
        category: r.category,
        crumbCount: r.crumbCount,
        crumbId: r.crumbId,
      }),
    ),
    truncated,
  }
}

// "coming up" shows the soonest upcoming crumbs; a curated horizon, not a
// browsable archive, so the soonest N is the whole product surface
const UPCOMING_LIMIT = 100

// a crumb's forward date: an explicit plan, else its event's start. mirrors
// crumbPlannedDate on the client so the two agree on what "upcoming" means
const upcomingDate = sql<Date>`coalesce(
  case when ${schema.crumbs.status} = 'planned' then ${schema.crumbs.plannedFor} end,
  ${schema.events.startsAt}
)`

// upcoming crumbs (planned or dated, not done, today onward), soonest
// first — queried server-side so it sees every crumb, not just a recent page
export const listUpcomingCrumbs = async (
  userId: string,
): Promise<Crumb[]> => {
  const rows = await crumbJoinedSelect()
    .where(
      and(
        eq(schema.crumbs.authorId, userId),
        ne(schema.crumbs.status, 'done'),
        gte(upcomingDate, sql`date_trunc('day', now())`),
      ),
    )
    .orderBy(asc(upcomingDate), asc(schema.crumbs.id))
    .limit(UPCOMING_LIMIT)
  return toCrumbResponses(rows)
}

// crumb responses for a known id set, keyed by id; ordering stays with
// the caller
export const loadCrumbResponsesByIds = async (
  crumbIds: string[],
  executor: DbOrTx = db,
): Promise<Map<string, Crumb>> => {
  if (!crumbIds.length) return new Map()
  const rows = await crumbJoinedSelect(executor).where(
    inArray(schema.crumbs.id, crumbIds),
  )
  const crumbs = await toCrumbResponses(rows)
  return new Map(crumbs.map((c) => [c.id, c]))
}

export const getCrumbResponseById = async (
  id: string,
  executor: DbOrTx = db,
): Promise<Crumb> => {
  const crumb = (await loadCrumbResponsesByIds([id], executor)).get(
    id,
  )
  if (!crumb) throw new HTTPException(404, {message: 'not_found'})
  return crumb
}

// guard + load: the crumb must exist and belong to the user, else 404
export const requireOwnCrumb = async (
  id: string,
  userId: string,
): Promise<{id: string; name: string; imageRef: string | null}> =>
  firstOr404(
    await db
      .select({
        id: schema.crumbs.id,
        name: schema.crumbs.name,
        imageRef: schema.crumbs.imageRef,
      })
      .from(schema.crumbs)
      .where(
        and(
          eq(schema.crumbs.id, id),
          eq(schema.crumbs.authorId, userId),
        ),
      )
      .limit(1),
  )

// a referenced source must already exist; a bad ref is client error, not 404
export const requireSource = async (id: string) => {
  const [source] = await db
    .select({id: schema.sources.id})
    .from(schema.sources)
    .where(eq(schema.sources.id, id))
    .limit(1)
  if (!source)
    throw new HTTPException(400, {message: 'invalid_source'})
  return source.id
}

export type CrumbTargetInput = {
  placeId?: string
  placeRef?: PlaceRef
  placeDraft?: PlaceDraft
  eventId?: string
}

// resolve at most one target into entity fks; null = no target field given
export const resolveCrumbTarget = async (
  input: CrumbTargetInput,
): Promise<{
  placeId: string | null
  eventId: string | null
} | null> => {
  if (input.placeId) {
    const place = await getPlace(input.placeId)
    if (!place)
      throw new HTTPException(400, {message: 'invalid_place'})
    return {placeId: place.id, eventId: null}
  }
  if (input.placeRef) {
    const place = await resolvePlaceRef(input.placeRef)
    return {placeId: place.id, eventId: null}
  }
  if (input.placeDraft) {
    const place = await createProvisionalPlace(input.placeDraft)
    return {placeId: place.id, eventId: null}
  }
  if (input.eventId) {
    const [event] = await db
      .select({id: schema.events.id})
      .from(schema.events)
      .where(eq(schema.events.id, input.eventId))
      .limit(1)
    if (!event)
      throw new HTTPException(400, {message: 'invalid_event'})
    return {placeId: null, eventId: event.id}
  }
  return null
}

const upsertTags = async (
  names: string[],
  executor: DbOrTx = db,
): Promise<Map<string, {id: string; emoji: string}>> => {
  if (!names.length) return new Map()
  const unique = Array.from(new Set(names))
  await executor
    .insert(schema.tags)
    .values(unique.map((name) => ({name, emoji: pickTagEmoji(name)})))
    .onConflictDoNothing({target: schema.tags.name})
  const rows = await executor
    .select({
      id: schema.tags.id,
      name: schema.tags.name,
      emoji: schema.tags.emoji,
    })
    .from(schema.tags)
    .where(inArray(schema.tags.name, unique))
  return new Map(
    rows.map((r) => [r.name, {id: r.id, emoji: r.emoji}]),
  )
}

export const syncCrumbTags = async (
  executor: DbOrTx,
  crumbId: string,
  tagNames: string[],
  {replace}: {replace: boolean},
): Promise<{name: string; emoji: string}[]> => {
  const unique = [...new Set(tagNames)]
  if (replace) {
    await executor
      .delete(schema.crumbTags)
      .where(eq(schema.crumbTags.crumbId, crumbId))
  }
  const tags = await upsertTags(unique, executor)
  if (tags.size) {
    await executor.insert(schema.crumbTags).values(
      Array.from(tags.values()).map(({id: tagId}) => ({
        crumbId,
        tagId,
      })),
    )
  }
  return unique
    .filter((n) => tags.has(n))
    .map((n) => ({name: n, emoji: tags.get(n)!.emoji}))
}

// tag usage counts across an author's crumbs, most-used first
export const listTagCounts = async (
  userId: string,
): Promise<TagCount[]> =>
  db
    .select({
      name: schema.tags.name,
      emoji: schema.tags.emoji,
      count: countInt().as('count'),
    })
    .from(schema.crumbTags)
    .innerJoin(
      schema.crumbs,
      eq(schema.crumbTags.crumbId, schema.crumbs.id),
    )
    .innerJoin(
      schema.tags,
      eq(schema.crumbTags.tagId, schema.tags.id),
    )
    .where(eq(schema.crumbs.authorId, userId))
    .groupBy(schema.tags.name, schema.tags.emoji)
    .orderBy(sql`count desc`)
    // tags drive filter chips, not a browsable list; the long tail past a
    // couple hundred most-used tags never surfaces in the ui
    .limit(200)

// suggested tags for a new crumb: a place-category guess + recent tags
export const suggestTags = async (
  userId: string,
  category?: string,
): Promise<TagSuggestions> => {
  const fromPlaceCandidate = category
    ? TagNameSchema.safeParse(category)
    : null
  const fromPlace = fromPlaceCandidate?.success
    ? [fromPlaceCandidate.data]
    : []

  const recentRows = await db
    .select({
      name: schema.tags.name,
      lastUsed: sql<Date>`max(${schema.crumbTags.createdAt})`.as(
        'last_used',
      ),
    })
    .from(schema.crumbTags)
    .innerJoin(
      schema.crumbs,
      eq(schema.crumbTags.crumbId, schema.crumbs.id),
    )
    .innerJoin(
      schema.tags,
      eq(schema.crumbTags.tagId, schema.tags.id),
    )
    .where(eq(schema.crumbs.authorId, userId))
    .groupBy(schema.tags.name)
    .orderBy(sql`last_used desc`)
    .limit(10)

  const recent = recentRows
    .map((r) => r.name)
    .filter((n) => !fromPlace.includes(n))

  return {fromPlace, recent}
}

export const loadTagsByCrumb = async (
  crumbIds: string[],
): Promise<Map<string, {name: string; emoji: string}[]>> => {
  if (!crumbIds.length) return new Map()
  const rows = await db
    .select({
      crumbId: schema.crumbTags.crumbId,
      name: schema.tags.name,
      emoji: schema.tags.emoji,
      createdAt: schema.crumbTags.createdAt,
    })
    .from(schema.crumbTags)
    .innerJoin(
      schema.tags,
      eq(schema.crumbTags.tagId, schema.tags.id),
    )
    .where(inArray(schema.crumbTags.crumbId, crumbIds))
    .orderBy(schema.crumbTags.createdAt)
  return groupBy(
    rows,
    (r) => r.crumbId,
    (r) => ({name: r.name, emoji: r.emoji}),
  )
}
