main/init-db/01-init-postgis.sql

-- Loo lihtsalt UUID ilma extensionita
CREATE TABLE articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    abstract TEXT,
    content TEXT,
    authors TEXT[],
    published_date DATE,
    doi TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE article_embeddings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    article_id UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    embedding_model TEXT,
    vector_data BYTEA,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indeksid
CREATE INDEX idx_articles_title ON articles USING GIN(to_tsvector('english', title));
CREATE INDEX idx_articles_abstract ON articles USING GIN(to_tsvector('english', abstract));
CREATE INDEX idx_article_embeddings_article_id ON article_embeddings(article_id);

-- Õigused
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ai_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ai_user;