backend/database/schema.sql

-- Schema for MultiSource-RAG
-- (Database creation is handled by the cloud provider)

CREATE TABLE IF NOT EXISTS sources (
    id VARCHAR(36) PRIMARY KEY,
    type VARCHAR(20) NOT NULL,
    title VARCHAR(500) NOT NULL,
    origin TEXT,
    language VARCHAR(10) DEFAULT 'EN',
    status VARCHAR(20) DEFAULT 'pending',
    chunk_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS chunks (
    id VARCHAR(36) PRIMARY KEY,
    source_id VARCHAR(36),
    chunk_index INT,
    chunk_text TEXT,
    chunk_type VARCHAR(20) DEFAULT 'text',
    page_number INT DEFAULT NULL,
    timestamp_s INT DEFAULT NULL,
    unified_metadata JSON DEFAULT NULL,
    url_ref TEXT DEFAULT NULL,
    FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS chat_history (
    id VARCHAR(36) PRIMARY KEY,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    sources_used JSON DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    conversation_id VARCHAR(36) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS conversations (
    id VARCHAR(36) PRIMARY KEY,
    title VARCHAR(500) DEFAULT 'New Chat',
    conv_type VARCHAR(20) DEFAULT 'general',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS image_jobs (
    image_id VARCHAR(36) PRIMARY KEY,
    source_id VARCHAR(36) NULL,
    image_data LONGBLOB,
    caption TEXT,
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance (Standard syntax)
CREATE INDEX idx_chunks_source ON chunks(source_id);
CREATE INDEX idx_chunks_type ON chunks(chunk_type);
CREATE INDEX idx_sources_type ON sources(type);
CREATE INDEX idx_chat_created ON chat_history(created_at);