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

/**
 * FTS5 migration for SQLite — creates virtual table + triggers to sync.
 * Host runs this AFTER the main content_entries table exists.
 */
export const contentFts5MigrationSql = `
CREATE VIRTUAL TABLE IF NOT EXISTS content_entries_fts USING fts5(
  title,
  body,
  content='content_entries',
  content_rowid='rowid'
);

-- Triggers to keep FTS index in sync
CREATE TRIGGER IF NOT EXISTS content_entries_fts_ai AFTER INSERT ON content_entries BEGIN
  INSERT INTO content_entries_fts(rowid, title, body)
  VALUES (new.rowid, new.title, new.body);
END;

CREATE TRIGGER IF NOT EXISTS content_entries_fts_ad AFTER DELETE ON content_entries BEGIN
  INSERT INTO content_entries_fts(content_entries_fts, rowid, title, body)
  VALUES ('delete', old.rowid, old.title, old.body);
END;

CREATE TRIGGER IF NOT EXISTS content_entries_fts_au AFTER UPDATE ON content_entries BEGIN
  INSERT INTO content_entries_fts(content_entries_fts, rowid, title, body)
  VALUES ('delete', old.rowid, old.title, old.body);
  INSERT INTO content_entries_fts(rowid, title, body)
  VALUES (new.rowid, new.title, new.body);
END;
`.trim()

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

export function createContentSqliteSearchProvider(opts?: {
  types?: string[]
}): SearchProvider<ContentSearchCtx> {
  const types = opts?.types

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

    // Escape FTS5 special chars and wrap in quotes for phrase matching
    const sanitized = query.replace(/["'[\]]/g, ' ').trim()
    if (!sanitized) return { hits: [], total: 0 }

    // Build visibility predicate (reuse from store.ts)
    const vis = visibilityPredicate(ctx.viewer)

    // Type filter
    let typeFilter = drizzle.sql`1=1`
    if (types && types.length > 0) {
      const typeList = types.map(t => `'${t.replace(/'/g, "''")}'`).join(',')
      typeFilter = drizzle.sql.raw(`ce.type IN (${typeList})`)
    }

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

    // FTS5 query with bm25 ranking
    const result = await ctx.db.execute(drizzle.sql`
      SELECT
        ce.id,
        ce.title,
        -bm25(content_entries_fts) AS rank,
        snippet(content_entries_fts, 1, '<mark>', '</mark>', '...', 30) AS snippet_html,
        (SELECT COUNT(*) 
         FROM content_entries_fts fts2
         JOIN content_entries ce2 ON ce2.rowid = fts2.rowid
         WHERE fts2 MATCH ${sanitized}
           AND ${vis ? vis : drizzle.sql`1=1`}
           AND ${typeFilter}
        ) AS total
      FROM content_entries_fts fts
      JOIN content_entries ce ON ce.rowid = fts.rowid
      WHERE fts MATCH ${sanitized}
        AND ${vis ? vis : drizzle.sql`1=1`}
        AND ${typeFilter}
      ORDER BY rank DESC, ce.published_at DESC
      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 ?? []
}