import * as drizzle from 'drizzle-orm'
import type { SQL } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import type { SearchProvider } from '@platform-modules/search'
import { type Actor } from './authz.js'
import { contentEntries, type ContentSchema, type ContentTransaction } from './schema.js'
import type { ContentSearchAdapter } from './query.js'
import { visibilityPredicate } from './store.js'

/** Additive, idempotent-guarded FTS DDL — host splits on `;` and runs per-statement (neon-http). */
export const contentSearchMigrationSql = (table = 'content_entries', config = 'english'): string =>
  `
ALTER TABLE ${table} ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('${config}', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('${config}', regexp_replace(coalesce(body, ''), '<[^>]*>', ' ', 'g')), 'B')
  ) STORED;
CREATE INDEX IF NOT EXISTS content_entries_search_idx ON ${table} USING GIN (search_vector);
`.trim()

export type ContentSearchCtx = { db: Querier<ContentSchema>; viewer?: Actor | null }

function regconfigLiteral(config: string): SQL {
  return drizzle.sql.raw(`'${config.replace(/'/g, "''")}'`)
}

export function createContentSearchProvider(opts?: {
  textSearchConfig?: string
  types?: string[]
}): SearchProvider<ContentSearchCtx> {
  const cfg = opts?.textSearchConfig ?? 'english'
  const cfgLit = regconfigLiteral(cfg)
  // Optional content-type allowlist — SQL-level (never a JS post-filter; preserves
  // correct `total`/pagination). Only uniformly-addressable types belong in a result
  // set, since `Hit` carries no route and the host maps hit→URL by type.
  const types = opts?.types

  return async (query, ctx, window) => {
    if (!query.trim()) return { hits: [], total: 0 }

    const tsq = drizzle.sql`to_tsquery(${cfgLit}, ${query})`
    const match = drizzle.sql`search_vector @@ ${tsq}`
    const vis = visibilityPredicate(ctx.viewer)
    const conditions: SQL[] = [match]
    if (vis) conditions.unshift(vis)
    if (types && types.length > 0) conditions.push(drizzle.inArray(contentEntries.type, types))
    const where = conditions.length === 1 ? conditions[0]! : drizzle.and(...conditions)!

    type Row = {
      id: string
      title: string
      rank: number
      snippet_html: string
      total: number
    }

    const result = await ctx.db.execute(drizzle.sql`
      SELECT
        id,
        title,
        ts_rank(search_vector, ${tsq}) AS rank,
        ts_headline(
          ${cfgLit},
          regexp_replace(body, '<[^>]*>', ' ', 'g'),
          ${tsq},
          'StartSel=«,StopSel=»,MaxWords=30,MinWords=15,ShortWord=3'
        ) AS snippet_html,
        count(*) OVER()::int AS total
      FROM content_entries
      WHERE ${where}
      ORDER BY rank DESC, published_at DESC NULLS LAST
      LIMIT ${window.limit} OFFSET ${window.offset}
    `)

    const rows = normalizeExecuteRows<Row>(result)
    const total = rows[0]?.total ?? 0

    return {
      hits: rows.map((row) => ({
        entityType: 'content',
        id: row.id,
        rank: row.rank,
        title: row.title,
        snippetHtml: row.snippet_html,
      })),
      total,
    }
  }
}

function normalizeExecuteRows<T>(result: unknown): T[] {
  if (Array.isArray(result)) return result as T[]
  const rows = (result as { rows?: T[] } | null)?.rows
  return rows ?? []
}

/** Canonical T5 search adapter: caller supplies the already-authorized SQL predicate. */
export function createContentPostgresSearchAdapter(opts?: { textSearchConfig?: string }): ContentSearchAdapter {
  const cfgLit = regconfigLiteral(opts?.textSearchConfig ?? 'english')
  return {
    async searchPage(db: ContentTransaction, input, authorizedPredicate) {
      const query = input.search?.trim()
      if (!query) return { ids: [], totalItems: 0, rank: {} }
      const page = input.page ?? 1
      const pageSize = input.pageSize ?? 20
      const offset = (page - 1) * pageSize
      const tsq = drizzle.sql`websearch_to_tsquery(${cfgLit}, ${query})`
      const vector = drizzle.sql`(
        setweight(to_tsvector(${cfgLit}, coalesce(title, '')), 'A') ||
        setweight(to_tsvector(${cfgLit}, coalesce(slug, '')), 'A') ||
        setweight(to_tsvector(${cfgLit}, coalesce(excerpt, '')), 'B') ||
        setweight(to_tsvector(${cfgLit}, regexp_replace(coalesce(body, ''), '<[^>]*>', ' ', 'g')), 'C')
      )`
      type Row = { id: string | null; rank: number | string | null; total: number | string }
      const rows = await db.execute<Row>(drizzle.sql`
        WITH matches AS (
          SELECT id, ts_rank(${vector}, ${tsq}) AS rank
          FROM content_entries
          WHERE ${authorizedPredicate} AND ${vector} @@ ${tsq}
        ), page_rows AS (
          SELECT id, rank FROM matches
          ORDER BY rank DESC, id ASC
          LIMIT ${pageSize} OFFSET ${offset}
        ), counted AS (
          SELECT COUNT(*)::int AS total FROM matches
        )
        SELECT page_rows.id, page_rows.rank, counted.total
        FROM counted LEFT JOIN page_rows ON TRUE
        ORDER BY page_rows.rank DESC NULLS LAST, page_rows.id ASC
      `)
      const totalItems = Number(rows[0]?.total ?? 0)
      const ids: string[] = []
      const rank: Record<string, number> = Object.create(null) as Record<string, number>
      for (const row of rows) {
        if (row.id === null) continue
        ids.push(row.id)
        rank[row.id] = Number(row.rank ?? 0)
      }
      return { ids, totalItems, rank }
    },
  }
}