DROP FUNCTION IF EXISTS match_products(vector, double precision, integer);

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;

CREATE TABLE "public"."products" (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) UNIQUE NOT NULL,
  embedding VECTOR(3072) NOT NULL,
  price NUMERIC NOT NULL
);

CREATE OR REPLACE FUNCTION match_products(
  query_embedding VECTOR(3072),
  match_threshold FLOAT,
  match_count INT
)
RETURNS TABLE (
  id BIGINT,
  sku VARCHAR,
  name VARCHAR,
  price NUMERIC,
  similarity FLOAT
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    p.id,
    p.sku,
    p.name,
    p.price,
    1 - (p.embedding <-> query_embedding) AS similarity
  FROM
    products p
  WHERE
    1 - (p.embedding <-> query_embedding) > match_threshold
  ORDER BY
    p.embedding <-> query_embedding
  LIMIT match_count;
END;
$$;