setup_database.sql

-- Enable the required extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Drop the existing table if it exists
DROP TABLE IF EXISTS public."BloodTestData";

-- Create the BloodTestData table with the correct schema
CREATE TABLE public."BloodTestData" (
  id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "clerkUserId" TEXT,
  content TEXT NOT NULL,
  embedding vector(384),
  metadata JSONB,
  "accessType" TEXT DEFAULT 'user',
  "createdAt" TIMESTAMPTZ DEFAULT NOW(),
  "updatedAt" TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes
CREATE INDEX "BloodTestData_clerkUserId_idx" ON public."BloodTestData"("clerkUserId");
CREATE INDEX "BloodTestData_accessType_idx" ON public."BloodTestData"("accessType");

-- Grant necessary permissions to the authenticated role
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT ALL ON TABLE public."BloodTestData" TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public."BloodTestData_id_seq" TO authenticated;

-- Create the match_blood_test_data function
CREATE OR REPLACE FUNCTION match_blood_test_data(
  query_embedding vector(384),
  match_threshold float,
  match_count int,
  clerk_user_id text,
  include_global boolean
)
RETURNS TABLE (
  id bigint,
  content text,
  metadata jsonb,
  similarity float
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    bt.id,
    bt.content,
    bt.metadata,
    1 - (bt.embedding <=> query_embedding) AS similarity
  FROM
    public."BloodTestData" bt
  WHERE
    (bt."clerkUserId" = clerk_user_id OR (include_global AND bt."accessType" = 'global'))
    AND 1 - (bt.embedding <=> query_embedding) > match_threshold
  ORDER BY
    bt.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;

-- Grant execute permission on the function to the authenticated role
GRANT EXECUTE ON FUNCTION match_blood_test_data TO authenticated;