query.sql

-- Drop existing tables if they exist to ensure a clean slate
DROP TABLE IF EXISTS "feedbacks" CASCADE;
DROP TABLE IF EXISTS "elements" CASCADE;
DROP TABLE IF EXISTS "steps" CASCADE;
DROP TABLE IF EXISTS "threads" CASCADE;
DROP TABLE IF EXISTS "users" CASCADE;

-- Create Users Table
CREATE TABLE IF NOT EXISTS "users" (
    "id" UUID PRIMARY KEY,
    "identifier" VARCHAR(255) UNIQUE NOT NULL,
    "createdAt" TIMESTAMP WITH TIME ZONE,
    "metadata" JSONB
);

-- Create Threads Table
CREATE TABLE IF NOT EXISTS "threads" (
    "id" VARCHAR(255) PRIMARY KEY,
    "name" VARCHAR(255),
    "userId" UUID REFERENCES "users"("id") ON DELETE SET NULL,
    "userIdentifier" VARCHAR(255),
    "createdAt" TIMESTAMP WITH TIME ZONE,
    "metadata" JSONB,
    "tags" TEXT[],
    "deletedAt" TIMESTAMP WITH TIME ZONE
);
CREATE INDEX IF NOT EXISTS "thread_userId_idx" ON "threads"("userId");

-- Create Steps Table
CREATE TABLE IF NOT EXISTS "steps" (
    "id" VARCHAR(255) PRIMARY KEY,
    "name" VARCHAR(255),
    "type" VARCHAR(255) NOT NULL,
    "threadId" VARCHAR(255) REFERENCES "threads"("id") ON DELETE CASCADE,
    "parentId" VARCHAR(255),
    "streaming" BOOLEAN,
    "waitForAnswer" BOOLEAN,
    "isError" BOOLEAN,
    "metadata" JSONB,
    "tags" TEXT[],
    "input" TEXT,
    "output" TEXT,
    "createdAt" TIMESTAMP WITH TIME ZONE,
    "start" TIMESTAMP WITH TIME ZONE,
    "end" TIMESTAMP WITH TIME ZONE,
    "generation" JSONB,
    "showInput" VARCHAR(255),
    "language" VARCHAR(255),
    "indent" INTEGER,
    "defaultOpen" BOOLEAN -- Added missing column
);
CREATE INDEX IF NOT EXISTS "step_threadId_idx" ON "steps"("threadId");

-- Create Elements Table
CREATE TABLE IF NOT EXISTS "elements" (
    "id" VARCHAR(255) PRIMARY KEY,
    "name" VARCHAR(255),
    "type" VARCHAR(255),
    "threadId" VARCHAR(255) REFERENCES "threads"("id") ON DELETE CASCADE,
    "forId" VARCHAR(255),
    "url" TEXT,
    "objectKey" TEXT,
    "chainlitKey" TEXT,
    "display" VARCHAR(50),
    "size" VARCHAR(50),
    "language" VARCHAR(50),
    "page" INTEGER,
    "mime" VARCHAR(100),
    "props" JSONB
);
CREATE INDEX IF NOT EXISTS "element_threadId_idx" ON "elements"("threadId");

-- Create Feedbacks Table
CREATE TABLE IF NOT EXISTS "feedbacks" (
    "id" UUID PRIMARY KEY,
    "forId" VARCHAR(255) NOT NULL,
    "threadId" VARCHAR(255),
    "value" INTEGER NOT NULL CHECK (value IN (0, 1)),
    "comment" TEXT
);
CREATE INDEX IF NOT EXISTS "feedback_forId_idx" ON "feedbacks"("forId");