CREATE DATABASE lectures_vector_db;
\c lectures_vector_db;
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE lecture_repre (
    id SERIAL PRIMARY KEY,
    content VARCHAR NOT NULL,
    embedding vector(768),
    audio_embedding vector(384)
);

-- tabels in lectures_app_db
\c lectures_app_db;

CREATE TABLE lectures (
    id SERIAL PRIMARY KEY,
    text VARCHAR NOT NULL,
    summary VARCHAR NOT NULL,
    duration_seconds INT NOT NULL,
    lecturer VARCHAR(32),
    voice_language VARCHAR(32),
    slides_language VARCHAR(32)
);


CREATE TABLE images (
    id SERIAL PRIMARY KEY,
    image_url VARCHAR(512) NOT NULL,
    image_type VARCHAR(512),
    FOREIGN KEY (lecture_id) REFERENCES lectures_app_db.public.lectures(id)
);

CREATE TABLE topics (
    id SERIAL PRIMARY KEY,
    topic_category VARCHAR(256) NOT NULL
);


CREATE TABLE lecture_topics (
    id SERIAL PRIMARY KEY,
    FOREIGN KEY (lecture_id) REFERENCES lectures_app_db.public.lectures(id),
    FOREIGN KEY (topic_id) REFERENCES lectures_app_db.public.topics(id),
    topic_probability FLOAT
);


CREATE TABLE lectures (
    id SERIAL PRIMARY KEY,
    text VARCHAR NOT NULL,
    summary VARCHAR NOT NULL,
    duration_seconds INT NOT NULL,
    lecturer VARCHAR(32),
    voice_language VARCHAR(32),
    slides_langauge VARCHAR(32)
);

CREATE TABLE topics (
    id SERIAL PRIMARY KEY,
    topic_category VARCHAR(256) NOT NULL
);


CREATE TABLE visual_doc_understanding_data (
    id SERIAL PRIMARY KEY,
    vdu_output VARCHAR,
    vdu_model VARCHAR(256),
    FOREIGN KEY (lecture_id) REFERENCES lectures_app_db.public.lectures(id)
);
