import {moveItem} from '@crumbs/core/collections'
import {asc, eq, sql} from 'drizzle-orm'
import {HTTPException} from 'hono/http-exception'

import {db, schema, type DbOrTx} from '../db/client'
import {rethrowUniqueAs} from '../lib/sql'
import {isAcceptedCollaborator} from './visibility'

// crumb-in-trail membership writes: append, reorder, and the auto-file
// suggestion. all position math runs under the trail row lock

// bumping updatedAt first locks the trail row, serializing concurrent
// content writes so position math cannot race
const lockTrail = (tx: DbOrTx, trailId: string) =>
  tx
    .update(schema.trails)
    .set({updatedAt: new Date()})
    .where(eq(schema.trails.id, trailId))

// file a crumb at the end of the trail, returning its position; the
// (trail, crumb) unique index rejects a duplicate as already_in_trail
export const appendTrailCrumb = async (
  trailId: string,
  crumbId: string,
  addedBy: string,
): Promise<number> =>
  db.transaction(async (tx) => {
    await lockTrail(tx, trailId)

    const [inserted] = await tx
      .insert(schema.trailCrumbs)
      .values({
        trailId,
        crumbId,
        addedBy,
        position: sql<number>`coalesce(
          (select max(${schema.trailCrumbs.position}) + 1
           from ${schema.trailCrumbs}
           where ${schema.trailCrumbs.trailId} = ${trailId}),
          0
        )`,
      })
      .returning({position: schema.trailCrumbs.position})
      .catch(rethrowUniqueAs('already_in_trail'))
    return inserted!.position
  })

export const reorderTrailCrumb = async (
  trailId: string,
  crumbId: string,
  position: number,
): Promise<void> =>
  db.transaction(async (tx) => {
    await lockTrail(tx, trailId)

    const rows = await tx
      .select({crumbId: schema.trailCrumbs.crumbId})
      .from(schema.trailCrumbs)
      .where(eq(schema.trailCrumbs.trailId, trailId))
      .orderBy(asc(schema.trailCrumbs.position))
    const order = rows.map((r) => r.crumbId)
    const from = order.indexOf(crumbId)
    if (from === -1)
      throw new HTTPException(404, {message: 'not_found'})

    const next = moveItem(order, from, position)
    if (next.every((cid, i) => cid === order[i])) return

    // shift everything out of the (trail_id, position) unique
    // index's way, then renumber to the new order
    await tx
      .update(schema.trailCrumbs)
      .set({
        position: sql`-(${schema.trailCrumbs.position} + 1)`,
      })
      .where(eq(schema.trailCrumbs.trailId, trailId))
    await tx.execute(sql`
      update trail_crumbs set position = v.position
      from (values ${sql.join(
        next.map((cid, i) => sql`(${cid}::uuid, ${i}::int)`),
        sql`, `,
      )}) as v(crumb_id, position)
      where trail_crumbs.trail_id = ${trailId}
        and trail_crumbs.crumb_id = v.crumb_id
    `)
  })

// programmatic auto-file suggestion: member trails whose crumbs share tags
// with this one, best match first. membership derives from the shared
// collaborator predicate, never re-spelled here
export const suggestTrailForCrumb = async (
  userId: string,
  crumbId: string,
): Promise<{id: string; name: string} | null> => {
  const rows = await db.execute(sql`
    select ${schema.trails.id}, ${schema.trails.name}, count(*)::int as score
    from ${schema.trails}
    join ${schema.trailCrumbs} on ${schema.trailCrumbs.trailId} = ${schema.trails.id}
    join ${schema.crumbTags} on ${schema.crumbTags.crumbId} = ${schema.trailCrumbs.crumbId}
    where ${schema.crumbTags.tagId} in (
        select ${schema.crumbTags.tagId} from ${schema.crumbTags}
        where ${schema.crumbTags.crumbId} = ${crumbId}
      )
      and (
        ${schema.trails.authorId} = ${userId}
        or ${isAcceptedCollaborator(userId)}
      )
      and not exists (
        select 1 from trail_crumbs tc2
        where tc2.trail_id = ${schema.trails.id}
          and tc2.crumb_id = ${crumbId}
      )
    group by ${schema.trails.id}, ${schema.trails.name}
    order by score desc, ${schema.trails.updatedAt} desc
    limit 1
  `)
  // bun-sql's execute drops the row generic; the raw query is the boundary
  const [suggestion] = [...rows] as {id: string; name: string}[]
  return suggestion
    ? {id: suggestion.id, name: suggestion.name}
    : null
}
