import { MigrateUpArgs, MigrateDownArgs, sql } from '@payloadcms/db-postgres'

/**
 * Migration: Add Full-Text Search Index for Posts
 *
 * This migration adds:
 * 1. A function to extract text from Lexical JSONB content
 * 2. A generated column (search_text) that concatenates searchable fields
 * 3. GIN indexes for both German and English full-text search
 *
 * The FTS can be activated by setting USE_FTS=true environment variable.
 * Without this flag, the search API uses ILIKE which works without these indexes.
 */

export async function up({ db }: MigrateUpArgs): Promise<void> {
  // 1. Create function to extract text from Lexical JSONB content
  await db.execute(sql`
    CREATE OR REPLACE FUNCTION extract_lexical_text(content jsonb)
    RETURNS text
    LANGUAGE sql
    IMMUTABLE
    PARALLEL SAFE
    AS $$
      SELECT COALESCE(
        string_agg(node_text, ' '),
        ''
      )
      FROM (
        SELECT jsonb_path_query(
          content,
          'strict $.root.**.text'
        )::text AS node_text
      ) extracted
      WHERE node_text IS NOT NULL
        AND node_text != 'null'
    $$;
  `)

  // 2. Add generated column for full searchable text
  // This combines title, excerpt, and extracted content text
  await db.execute(sql`
    ALTER TABLE posts
    ADD COLUMN IF NOT EXISTS search_text text
    GENERATED ALWAYS AS (
      COALESCE(title, '') || ' ' ||
      COALESCE(excerpt, '') || ' ' ||
      COALESCE(extract_lexical_text(content), '')
    ) STORED;
  `)

  // 3. Create GIN index for German full-text search
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS posts_search_german_idx
    ON posts
    USING GIN (to_tsvector('pg_catalog.german', search_text));
  `)

  // 4. Create GIN index for English full-text search
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS posts_search_english_idx
    ON posts
    USING GIN (to_tsvector('pg_catalog.english', search_text));
  `)

  // 5. Create btree index on title for faster ILIKE prefix searches
  // This is a fallback for when FTS is not used
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS posts_title_lower_idx
    ON posts (LOWER(title) varchar_pattern_ops);
  `)

  // Note: For even better ILIKE performance, pg_trgm extension can be enabled manually:
  // CREATE EXTENSION IF NOT EXISTS pg_trgm;
  // CREATE INDEX posts_title_trgm_idx ON posts USING GIN (title gin_trgm_ops);
}

export async function down({ db }: MigrateDownArgs): Promise<void> {
  // Remove indexes
  await db.execute(sql`
    DROP INDEX IF EXISTS posts_title_lower_idx;
  `)

  await db.execute(sql`
    DROP INDEX IF EXISTS posts_title_trgm_idx;
  `)

  await db.execute(sql`
    DROP INDEX IF EXISTS posts_search_english_idx;
  `)

  await db.execute(sql`
    DROP INDEX IF EXISTS posts_search_german_idx;
  `)

  // Remove generated column
  await db.execute(sql`
    ALTER TABLE posts
    DROP COLUMN IF EXISTS search_text;
  `)

  // Remove function
  await db.execute(sql`
    DROP FUNCTION IF EXISTS extract_lexical_text(jsonb);
  `)
}
