 -- Основная таблица с метаданными запроса
CREATE TABLE generation_requests (
    id UUID PRIMARY KEY,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    document_type VARCHAR(1000) NOT NULL,
    project VARCHAR(1000) NOT NULL,
    page_name VARCHAR(1000) NOT NULL,
    attached_link VARCHAR(1000)
);
-- Таблица входных данных
CREATE TABLE generation_inputs (
    request_id UUID PRIMARY KEY REFERENCES generation_requests(id) ON DELETE CASCADE,
    user_question TEXT,
    jira_text TEXT,
    previous_generations_summary TEXT,
    few_shot_examples JSONB
);
-- Таблица контекстных данных
CREATE TABLE generation_contexts (
    request_id UUID PRIMARY KEY REFERENCES generation_requests(id) ON DELETE CASCADE,
    document_context JSONB,
    table_context TEXT,
    graph_context TEXT
);
-- Таблица результатов
CREATE TABLE generation_results (
    request_id UUID PRIMARY KEY REFERENCES generation_requests(id) ON DELETE CASCADE,
    generated_answer TEXT,
    timings JSONB,
    metrics JSONB
);
-- Таблица правок пользователей
CREATE TABLE generation_edits (
    edit_id UUID PRIMARY KEY,
    request_id UUID NOT NULL REFERENCES generation_requests(id) ON DELETE CASCADE,
    edited_answer TEXT NOT NULL,
    user_question TEXT,
    timestamp TIMESTAMPTZ DEFAULT NOW()
);
-- Таблица оценок пользователей
CREATE TABLE generation_ratings (
    edit_id UUID PRIMARY KEY REFERENCES generation_edits(edit_id) ON DELETE CASCADE,
    user_rating INTEGER
);

-- Индекс для ускорения поиска по request_id
CREATE INDEX idx_generation_edits_request_id ON generation_edits(request_id);

\c postgres;

-- Создаем новую базу данных для истории
CREATE DATABASE "webui-history";

\c "webui-history";

-- Создаем таблицы в новой базе данных
CREATE TABLE document_history (
    id UUID PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    template_type VARCHAR(255) NOT NULL,
    page_name TEXT NOT NULL,
    project_name VARCHAR(255) NOT NULL,
    jira_filename VARCHAR(255),
    response TEXT NOT NULL,
    request_id VARCHAR(255),
    edit_id VARCHAR(255),
    attached_link TEXT,
    context_documents JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Создание индексов для оптимизации поиска
CREATE INDEX idx_document_history_username ON document_history(username);
CREATE INDEX idx_document_history_project_page ON document_history(project_name, page_name);
CREATE INDEX idx_document_history_template ON document_history(template_type);

-- Создание таблицы для хранения версий документов
CREATE TABLE document_versions (
    id SERIAL PRIMARY KEY,
    doc_id UUID NOT NULL REFERENCES document_history(id) ON DELETE CASCADE,
    version INT NOT NULL,
    content TEXT NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    template_type VARCHAR(255) NOT NULL,
    page_name TEXT NOT NULL,
    request_id VARCHAR(255),
    edit_id VARCHAR(255),
    source VARCHAR(100) NOT NULL,
    user_question TEXT,
    attached_link TEXT,
    context_documents JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_doc_version UNIQUE(doc_id, version)
);

-- Создание индексов для оптимизации поиска версий
CREATE INDEX idx_document_versions_doc_id ON document_versions(doc_id);
CREATE INDEX idx_document_versions_edit_id ON document_versions(edit_id);
