db_init.sql

-- Questions table
-- public.questions definition

-- Drop table

-- DROP TABLE public.questions;


CREATE TABLE public.questions (
	question_id serial4 NOT NULL,
	question_set text NOT NULL,
	question text NOT NULL,
	"options" jsonb NOT NULL,
	answer text NOT NULL,
	answer_idx text NOT NULL,
	question_hash text NOT NULL,
	meta_info text NULL,
	answer_count int4 DEFAULT 0 NULL,
	created_at timestamptz DEFAULT now() NULL,
	"extraJ" jsonb NULL,
	other jsonb NULL,
	overflow jsonb NULL,
	user_id uuid DEFAULT auth.uid() NULL,
	src_file_id int8 NULL,
	src_file_content_hash text NULL,
	src_question_hash text NULL,
	src_question_uid text NULL,
	CONSTRAINT questions_pkey PRIMARY KEY (question_id)
);
 
CREATE INDEX IF NOT EXISTS idx_questions_question_set ON questions(question_set);
CREATE INDEX IF NOT EXISTS idx_questions_question_hash ON questions(question_hash);

-- User answers table
CREATE TABLE IF NOT EXISTS user_answers (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL,
  question_id INTEGER NOT NULL REFERENCES questions(question_id),
  question_hash TEXT NOT NULL,
  question_set TEXT NOT NULL,
  selected_option TEXT NOT NULL,
  is_correct BOOLEAN NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_user_answers_user_id ON user_answers(user_id);
CREATE INDEX IF NOT EXISTS idx_user_answers_question_id ON user_answers(question_id);

-- Question flags table
CREATE TABLE IF NOT EXISTS question_flags (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL,
  question_id INTEGER NOT NULL REFERENCES questions(question_id),
  question_hash TEXT NOT NULL,
  question_set TEXT NOT NULL,
  flag_reason TEXT NOT NULL,
  details TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_question_flags_question_id ON question_flags(question_id);

-- User fingerprints table
CREATE TABLE IF NOT EXISTS user_fingerprints (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL,
  fingerprint TEXT NOT NULL,
  ip_address TEXT,
  user_agent TEXT,
  ip_info_io JSONB,
  named TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_user_fingerprints_fingerprint ON user_fingerprints(fingerprint);
CREATE INDEX IF NOT EXISTS idx_user_fingerprints_user_id ON user_fingerprints(user_id);

-- Configuration table
CREATE TABLE IF NOT EXISTS configuration (
  id INTEGER PRIMARY KEY,
  question_sets JSONB NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create trigger function for updating answer count
CREATE OR REPLACE FUNCTION update_answer_count()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE questions
  SET answer_count = answer_count + 1
  WHERE question_id = NEW.question_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Drop trigger if exists and recreate
DROP TRIGGER IF EXISTS trigger_update_answer_count ON user_answers;

CREATE TRIGGER trigger_update_answer_count
AFTER INSERT ON user_answers
FOR EACH ROW
EXECUTE FUNCTION update_answer_count();

-- Insert default configuration
INSERT INTO configuration (id, question_sets)
VALUES (1, '[
  {"name": "medQA", "percentage": 50, "max_answers": null},
  {"name": "synthNew", "percentage": 50, "max_answers": 5}
]'::jsonb)
ON CONFLICT (id) DO NOTHING;

-- Enable aggregate functions for PostgREST/Supabase queries
ALTER ROLE authenticator SET pgrst.db_aggregates_enabled = 'true';
NOTIFY pgrst, 'reload config';

-- Add the new file_log table if it doesn't exist
CREATE TABLE IF NOT EXISTS public.file_log (
	id int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
	question_set text NOT NULL,
	description text NULL,
	created_at timestamptz DEFAULT now() NOT NULL,
	user_id uuid DEFAULT auth.uid() NULL,
	filename text NULL,
	content_hash text NULL,
	ftype text NULL,
	user_pubkey text NULL,
	signed_hash text NULL,
	raw_text text NULL,
	raw_bytes bytea NULL,
	CONSTRAINT file_log_pkey PRIMARY KEY (id)
);
      

create view IF NOT EXISTS   public.view_file_content_aggas as select q.src_file_content_hash, count(distinct question_id) distinct_id_count, count(distinct question_hash) question_hash_count  , ARRAY_AGG(distinct(q.user_id)) as users, ARRAY_AGG(distinct(f.question_set)) as questino_sets,  ARRAY_AGG(distinct(f.filename)) as filenames , ARRAY_AGG(distinct(f.description)) as descriptions    
from   questions  q  
left join  file_log  f  on q.src_file_content_hash=f.content_hash
  group by q.src_file_content_hash ;



-- public.test_scores definition

-- Drop table

-- DROP TABLE public.test_scores;

CREATE  TABLE if not exists  public.test_scores (
	id serial4 NOT NULL,
	evaluationrunid text NOT NULL,
	modeldid text NOT NULL,
	validatordid text NOT NULL,
	score numeric(5, 2) NOT NULL,
	metrics jsonb NULL,
	evaluationdid text NULL,
	hash text NULL,
	metadata json NULL,
	peer_reviews json NULL,
	promptcid text NULL,
	responsecid text NULL,
	promptdata text NULL,
	responsedata text NULL,
	correctresponse text NULL,
	repliedat int8 NULL,
	updatedat int8 NULL,
	promptedat int8 NULL,
	user_id uuid DEFAULT auth.uid() NULL,
	CONSTRAINT test_scores_pkey PRIMARY KEY (id)
);