CREATE OR REPLACE FUNCTION hybrid_search_quizzes(
  query_text TEXT,
  query_embedding VECTOR(1536),
  user_id UUID,
  match_count INT DEFAULT 10,
  full_text_weight FLOAT DEFAULT 1.0,
  semantic_weight FLOAT DEFAULT 1.0,
  rrf_k INT DEFAULT 60
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  description TEXT,
  topic TEXT[],
  tags TEXT[],
  type TEXT,
  language TEXT,
  created_at TIMESTAMPTZ,
  questions_count SMALLINT,
  user_score SMALLINT,
  correct_answers_count SMALLINT,
  generated_tags TEXT[],
  generated_categories TEXT[]
)
LANGUAGE SQL
AS $$
WITH full_text AS (
  SELECT
    id,
    ROW_NUMBER() OVER(
      ORDER BY 
        ts_rank_cd(
          setweight(to_tsvector('english', title), 'A') ||
          setweight(to_tsvector('english', COALESCE(description, '')), 'B') ||
          setweight(to_tsvector('english', array_to_string(topic, ' ')), 'C'),
          websearch_to_tsquery(query_text)
        ) DESC
    ) AS rank_ix
  FROM
    quizzes
  WHERE
    user_id = hybrid_search_quizzes.user_id
    AND (
      setweight(to_tsvector('english', title), 'A') ||
      setweight(to_tsvector('english', COALESCE(description, '')), 'B') ||
      setweight(to_tsvector('english', array_to_string(topic, ' ')), 'C')
    ) @@ websearch_to_tsquery(query_text)
  ORDER BY rank_ix
  LIMIT LEAST(match_count * 2, 100)
),
semantic AS (
  SELECT
    id,
    ROW_NUMBER() OVER (ORDER BY embedding <#> query_embedding) AS rank_ix
  FROM
    quizzes
  WHERE
    user_id = hybrid_search_quizzes.user_id
    AND embedding IS NOT NULL
  ORDER BY rank_ix
  LIMIT LEAST(match_count * 2, 100)
)
SELECT
  q.id,
  q.title,
  q.description,
  q.topic,
  q.tags,
  q.type::TEXT,
  q.language,
  q.created_at,
  q.questions_count,
  q.user_score,
  q.correct_answers_count,
  q.generated_tags,
  q.generated_categories
FROM
  full_text ft
  FULL OUTER JOIN semantic sem ON ft.id = sem.id
  JOIN quizzes q ON COALESCE(ft.id, sem.id) = q.id
WHERE
  q.user_id = hybrid_search_quizzes.user_id
ORDER BY
  (COALESCE(1.0 / (rrf_k + ft.rank_ix), 0.0) * full_text_weight +
   COALESCE(1.0 / (rrf_k + sem.rank_ix), 0.0) * semantic_weight) DESC
LIMIT
  match_count;
$$;

// Generate embedding for the search query
const openai = createOpenAI({
  apiKey: c.env.OPENAI_API_KEY,
});
const { embedding } = await embed({
  model: openai.embedding("text-embedding-3-small"),
  value: query,
});

// In the queue handler
const textToEmbed = [
  quiz.title,
  quiz.description || '',
  quiz.topic.join(' '),
  (quiz.tags || []).join(' ')
].join(' ');

const embeddingResponse = await openai.embeddings.create({
  model: 'text-embedding-3-small',
  input: textToEmbed,
  dimensions: 1536,
});

const embedding = embeddingResponse.data[0].embedding;

// Update the quiz with the embedding
await db.update(quizzes)
  .set({ embedding })
  .where(eq(quizzes.id, quizId));

ORDER BY
  (COALESCE(1.0 / (rrf_k + ft.rank_ix), 0.0) * full_text_weight +
   COALESCE(1.0 / (rrf_k + sem.rank_ix), 0.0) * semantic_weight) DESC

.post(
  "/search",
  describeRoute({
    tags: ["History"],
    summary: "Search user's quiz history",
    description: "Search user's quiz history with optional filtering",
    validateResponse: true,
    responses: {
      200: {
        description: "Quiz history retrieved successfully",
        content: {
          "application/json": {
            schema: resolver(quizHistoryResponseSchema),
          },
        },
      },
    },
  }),
  zValidator(
    "json",
    z.object({
      query: z.string(),
      page: z.number().default(1),
      limit: z.number().default(10),
    }),
  ),
  async (c) => {
    const { query, page, limit } = c.req.valid("json");

    const supabase = getSupabase(c);
    const {
      data: { user },
    } = await supabase.auth.getUser();

    const db = await createDb(c);

    // Generate embedding for the search query
    const openai = createOpenAI({
      apiKey: c.env.OPENAI_API_KEY,
    });
    const { embedding } = await embed({
      model: openai.embedding("text-embedding-3-small"),
      value: query,
    });

    const userQuizzes = await db.transaction(async (tx) => {
      // Execute the hybrid search
      const searchResults = await tx.execute(sql`
        SELECT 
          q.*,
          m.user_score AS "multiplayerScore",
          m.correct_answers_count AS "multiplayerCorrect",
          (q.questions_count - q.correct_answers_count) AS "incorrect"
        FROM hybrid_search_quizzes(
          ${query},
          ${sql.raw(`'[${embedding.join(",")}]'::vector(1536)`)},
          ${user!.id}::uuid,
          ${limit},
          1.0,
          1.0,
          60
        ) AS q
        LEFT JOIN multiplayer_quiz_submissions m ON m.quiz_id = q.id
        LIMIT ${limit}
        OFFSET ${(page - 1) * limit}
      `);

      // Format results for the frontend
      const quizResults = searchResults.map((quiz) => {
        // Format logic here...
      });

      return {
        data: quizResults,
        pagination: {
          page,
          limit,
          totalItems: Number(totalCount),
          totalPages: Math.ceil(Number(totalCount) / limit),
          hasNextPage: page * limit < Number(totalCount),
          hasPreviousPage: page > 1,
        },
      };
    });

    return c.json(userQuizzes);
  },
),

const handleSearch = async (e: React.FormEvent) => {
  e.preventDefault();
  if (!searchQuery.trim()) {
    fetchQuizHistory();
    return;
  }

  setIsSearching(true);
  setIsLoading(true);

  try {
    const client = createApiClient();
    const response = await client.api.v1.history.search.$post({
      json: {
        query: searchQuery,
        page: pagination.page,
        limit: pagination.limit,
      },
    });

    const responseData = await response.json();
    setQuizHistory(responseData.data);
    setPagination(responseData.pagination);
  } catch (error) {
    console.error("Search error:", error);
    toast({
      title: "Search failed",
      description: "Could not search quizzes. Please try again.",
      variant: "destructive",
    });
  } finally {
    setIsLoading(false);
  }
};