-- RAG Pipeline Automation Database Schema
-- Automated document processing and ingestion for RAG system

-- Document storage table
CREATE TABLE IF NOT EXISTS rag_pipeline_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    vertical_id VARCHAR(100) DEFAULT 'general',
    metadata JSON,
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    status ENUM('queued', 'processing', 'completed', 'failed') DEFAULT 'queued',
    processing_stats JSON,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_vertical (vertical_id),
    INDEX idx_priority (priority),
    INDEX idx_created_at (created_at)
);

-- Processing queue table
CREATE TABLE IF NOT EXISTS rag_pipeline_queue (
    id INT PRIMARY KEY AUTO_INCREMENT,
    document_id INT NOT NULL,
    status ENUM('queued', 'processing', 'completed', 'failed') DEFAULT 'queued',
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    retry_count INT DEFAULT 0,
    last_attempt TIMESTAMP NULL,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES rag_pipeline_documents(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_document (document_id)
);

-- Document processing logs
CREATE TABLE IF NOT EXISTS rag_pipeline_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    document_id INT,
    action VARCHAR(100) NOT NULL,
    message TEXT,
    severity ENUM('info', 'warning', 'error') DEFAULT 'info',
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES rag_pipeline_documents(id) ON DELETE CASCADE,
    INDEX idx_document (document_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);

-- RAG collections management
CREATE TABLE IF NOT EXISTS rag_collections (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    vertical_id VARCHAR(100),
    document_count INT DEFAULT 0,
    total_chunks INT DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_vertical (vertical_id),
    INDEX idx_slug (slug)
);

-- Document to collection mapping
CREATE TABLE IF NOT EXISTS rag_document_collections (
    id INT PRIMARY KEY AUTO_INCREMENT,
    document_id INT NOT NULL,
    collection_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES rag_pipeline_documents(id) ON DELETE CASCADE,
    FOREIGN KEY (collection_id) REFERENCES rag_collections(id) ON DELETE CASCADE,
    UNIQUE KEY unique_doc_collection (document_id, collection_id)
);

-- Processing metrics and analytics
CREATE TABLE IF NOT EXISTS rag_pipeline_metrics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    metric_date DATE NOT NULL,
    documents_processed INT DEFAULT 0,
    documents_failed INT DEFAULT 0,
    avg_processing_time DECIMAL(10,2) DEFAULT 0,
    total_chunks_created INT DEFAULT 0,
    total_embeddings_generated INT DEFAULT 0,
    queue_size INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_date (metric_date),
    INDEX idx_date (metric_date)
);

-- Insert default collections
INSERT IGNORE INTO rag_collections (name, slug, description, vertical_id) VALUES
('General Knowledge', 'general', 'General purpose knowledge base', 'general'),
('Cannabis Industry', 'cannabis_docs', 'Cannabis industry knowledge and regulations', 'cannabis'),
('Cryptocurrency & Forex', 'crypto_forex_docs', 'Crypto and forex trading knowledge', 'crypto_forex'),
('Real Estate', 'real_estate_docs', 'Real estate investment and market knowledge', 'real-estate');

-- Sample pipeline metrics
INSERT IGNORE INTO rag_pipeline_metrics (metric_date, documents_processed, total_chunks_created)
SELECT CURDATE(), 0, 0 FROM dual WHERE NOT EXISTS (
    SELECT 1 FROM rag_pipeline_metrics WHERE metric_date = CURDATE()
);
