import {bboxAroundKm} from '@crumbs/core/geo'
import type {Coordinate, RecommendedCrumb} from '@crumbs/core/schemas'
import {sql} from 'drizzle-orm'

import {db} from '../db/client'
import {haversineKmSql} from '../lib/sql'
import {loadCrumbResponsesByIds} from './crumbs'
import {hydrateProfiles} from './friends'
import {loadReactionSummaries} from './trail-reactions'

// scoring tunables: a place scores the sum of each friend's single best
// crumb there (status-weighted, gently recency-decayed), damped by distance
const STATUS_WEIGHT_DONE = 3
const STATUS_WEIGHT_PLANNED = 1.5
const STATUS_WEIGHT_SAVED = 1
const DISTANCE_HALF_LIFE_KM = 3
const RECENCY_HALF_LIFE_DAYS = 180
const RECENCY_FLOOR = 0.25
const DEFAULT_RADIUS_KM = 10
const DEFAULT_LIMIT = 10

type RecommendationRow = {
  crumb_id: string
  distance_km: number
}

// nearby places ranked by accepted friends' crumb activity, each surfaced as
// its standout crumb: per friend a place counts once (their best crumb there)
// so distinct friends stack while one friend repeating does not; the crumb
// shown is the place's most-reacted, then strongest status + recency. merged
// places redirect to their canonical winner; places the viewer already
// visited drop out
export const recommendFriendCrumbs = async (opts: {
  viewerId: string
  friendIds: string[]
  center: Coordinate
  radiusKm?: number
  limit?: number
}): Promise<RecommendedCrumb[]> => {
  const {
    viewerId,
    friendIds,
    center,
    radiusKm = DEFAULT_RADIUS_KM,
    limit = DEFAULT_LIMIT,
  } = opts
  if (!friendIds.length) return []

  const [minLng, minLat, maxLng, maxLat] = bboxAroundKm(
    center,
    radiusKm,
  )
  const friendIdList = sql.join(
    friendIds.map((id) => sql`${id}`),
    sql`, `,
  )
  const km = haversineKmSql(sql`p.latitude`, sql`p.longitude`, center)

  const rows = await db.execute(sql`
    with crumb_signal as (
      select
        c.id as crumb_id,
        coalesce(cp.merged_into_id, cp.id) as place_id,
        c.author_id,
        c.created_at,
        (case c.status
          when 'done' then ${STATUS_WEIGHT_DONE}
          when 'planned' then ${STATUS_WEIGHT_PLANNED}
          else ${STATUS_WEIGHT_SAVED}
        end)
        * greatest(
            exp(-ln(2) * extract(epoch from now() - c.created_at)
              / (86400.0 * ${RECENCY_HALF_LIFE_DAYS})),
            ${RECENCY_FLOOR}
          ) as weight,
        (select count(*) from crumb_reactions r where r.crumb_id = c.id)
          as reaction_count
      from crumbs c
      join places cp on cp.id = c.place_id
      where c.author_id in (${friendIdList})
        and c.place_id is not null
    ),
    friend_best as (
      select place_id, author_id, max(weight) as weight
      from crumb_signal
      group by place_id, author_id
    ),
    place_rank as (
      select place_id, sum(weight) as signal
      from friend_best
      group by place_id
    ),
    top_crumb as (
      select distinct on (place_id) place_id, crumb_id
      from crumb_signal
      order by place_id, reaction_count desc, weight desc,
        created_at desc, crumb_id
    )
    select
      tc.crumb_id,
      d.km as distance_km
    from places p
    join place_rank pr on pr.place_id = p.id
    join top_crumb tc on tc.place_id = p.id
    cross join lateral (select ${km} as km) d
    where p.status <> 'merged'
      and p.latitude between ${minLat} and ${maxLat}
      and p.longitude between ${minLng} and ${maxLng}
      and d.km <= ${radiusKm}
      and not exists (
        select 1 from crumbs mine
        join places mp on mp.id = mine.place_id
        where mine.author_id = ${viewerId}
          and mine.status = 'done'
          and coalesce(mp.merged_into_id, mp.id) = p.id
      )
    order by
      pr.signal * exp(-ln(2) * d.km / ${DISTANCE_HALF_LIFE_KM}) desc,
      d.km asc, p.id asc
    limit ${limit}
  `)
  // bun-sql's execute drops the row generic; the raw query is the boundary
  const ranked = [...rows] as RecommendationRow[]
  if (!ranked.length) return []

  const crumbIds = ranked.map((row) => row.crumb_id)
  const [crumbById, reactionsByCrumb] = await Promise.all([
    loadCrumbResponsesByIds(crumbIds),
    loadReactionSummaries(viewerId, crumbIds),
  ])
  const authorIds = [
    ...new Set([...crumbById.values()].map((c) => c.authorId)),
  ]
  const profiles = await hydrateProfiles(viewerId, authorIds)

  return ranked.flatMap((row) => {
    const crumb = crumbById.get(row.crumb_id)
    const author = crumb && profiles.get(crumb.authorId)
    if (!crumb || !author) return []
    return [
      {
        crumb,
        author,
        reactions: reactionsByCrumb.get(row.crumb_id) ?? [],
        distanceKm: row.distance_km,
      },
    ]
  })
}
