CREATE DATABASE IF NOT EXISTS DOCUMENT_SEARCH;

USE DOCUMENT_SEARCH;

-- Documents table
CREATE TABLE IF NOT EXISTS DOCUMENTS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    TITLE VARCHAR(255) NOT NULL,
    CONTENT TEXT NOT NULL,
    SOURCE VARCHAR(255),
    DOC_TYPE VARCHAR(50),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Embeddings table - store as JSON since MySQL doesn't have a vector type
CREATE TABLE IF NOT EXISTS EMBEDDINGS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    DOCUMENT_ID INT NOT NULL,
    EMBEDDING JSON NOT NULL,
    FOREIGN KEY (DOCUMENT_ID) REFERENCES DOCUMENTS(ID) ON DELETE CASCADE
);

-- Search history
CREATE TABLE IF NOT EXISTS SEARCH_LOGS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    QUERY TEXT NOT NULL,
    RESULTS JSON,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);