CREATE EXTENSION IF NOT EXISTS vector;        -- Enable pgvector

# Store document chunks with embeddings
CREATE TABLE document_chunks (
    id SERIAL PRIMARY KEY,
    ticker VARCHAR(10) NOT NULL,              -- e.g., 'NVDA'
    document_type VARCHAR(20) NOT NULL,       -- '10-K', '10-Q', '8-K'
    filing_date DATE NOT NULL,
    chunk_index INTEGER NOT NULL,             -- Position in document
    content TEXT NOT NULL,                    -- The actual text chunk
    embedding vector(384),                    -- 384-dimensional embedding
    metadata JSONB,                           -- Extra context (section name, page, etc.)
    created_at TIMESTAMP DEFAULT NOW()
);

# Critical: Index for fast similarity search
CREATE INDEX ON document_chunks 
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

# Additional indexes for filtering
CREATE INDEX idx_ticker ON document_chunks(ticker);
CREATE INDEX idx_document_type ON document_chunks(document_type);

from langchain.text_splitter import RecursiveCharacterTextSplitter

def chunk_document(text: str, ticker: str, doc_type: str):
    """
    Chunk SEC filing into semantically coherent segments.
    """
    splitter = RecursiveCharacterTextSplitter(
        chunk_size=1000,           # Target ~1000 tokens
        chunk_overlap=200,         # 200 token overlap for context
        separators=["\n\n", "\n", ". ", " ", ""],  # Respect paragraph boundaries
        length_function=len
    )

    chunks = splitter.split_text(text)

    # Add metadata for each chunk
    chunk_objects = []
    for idx, chunk in enumerate(chunks):
        chunk_objects.append({
            "ticker": ticker,
            "document_type": doc_type,
            "chunk_index": idx,
            "content": chunk,
            "metadata": {
                "total_chunks": len(chunks),
                "position": f"{idx+1}/{len(chunks)}"
            }
        })

    return chunk_objects

from sentence_transformers import SentenceTransformer
from typing import List
import numpy as np

class EmbeddingService:
    def __init__(self):
        # Lazy initialization to avoid cold start overhead
        self._model = None

    @property
    def model(self):
        """Load model only when needed."""
        if self._model is None:
            self._model = SentenceTransformer('all-MiniLM-L6-v2')
        return self._model

    def generate_embeddings(self, texts: List[str]) -> np.ndarray:
        """
        Generate 384-dimensional embeddings for text chunks.

        Args:
            texts: List of text chunks to embed

        Returns:
            numpy array of shape (len(texts), 384)
        """
        # Batch processing for efficiency
        embeddings = self.model.encode(
            texts,
            batch_size=32,              # Process 32 chunks at once
            show_progress_bar=False,
            convert_to_numpy=True,
            normalize_embeddings=True   # L2 normalization for cosine similarity
        )

        return embeddings

from sqlalchemy import create_engine, text
from typing import List, Dict
import json

class VectorStore:
    def __init__(self, db_url: str):
        self.engine = create_engine(db_url)
        self.embedding_service = EmbeddingService()

    def add_documents(self, chunks: List[Dict]):
        """
        Add document chunks with embeddings to PostgreSQL.
        """
        # Extract text for embedding generation
        texts = [chunk["content"] for chunk in chunks]

        # Generate embeddings in batch
        embeddings = self.embedding_service.generate_embeddings(texts)

        # Insert into database
        with self.engine.connect() as conn:
            for chunk, embedding in zip(chunks, embeddings):
                conn.execute(
                    text("""
                        INSERT INTO document_chunks 
                        (ticker, document_type, filing_date, chunk_index, 
                         content, embedding, metadata)
                        VALUES 
                        (:ticker, :doc_type, :filing_date, :chunk_index,
                         :content, :embedding, :metadata)
                    """),
                    {
                        "ticker": chunk["ticker"],
                        "doc_type": chunk["document_type"],
                        "filing_date": chunk.get("filing_date"),
                        "chunk_index": chunk["chunk_index"],
                        "content": chunk["content"],
                        "embedding": embedding.tolist(),  # Convert numpy to list
                        "metadata": json.dumps(chunk["metadata"])
                    }
                )
            conn.commit()

def similarity_search(
    self, 
    query: str, 
    ticker: str = None,
    k: int = 5
) -> List[Dict]:
    """
    Find top-k most similar document chunks to query.

    Args:
        query: User's question
        ticker: Optional stock filter (e.g., 'NVDA')
        k: Number of results to return

    Returns:
        List of matching chunks with similarity scores
    """
    # Convert query to embedding
    query_embedding = self.embedding_service.generate_embeddings([query])[0]

    # Build SQL query
    sql = """
        SELECT 
            id,
            ticker,
            document_type,
            filing_date,
            content,
            metadata,
            1 - (embedding <=> :query_embedding) as similarity
        FROM document_chunks
        WHERE 1=1
    """

    params = {"query_embedding": query_embedding.tolist(), "k": k}

    # Optional ticker filter
    if ticker:
        sql += " AND ticker = :ticker"
        params["ticker"] = ticker

    sql += """
        ORDER BY embedding <=> :query_embedding
        LIMIT :k
    """

    with self.engine.connect() as conn:
        results = conn.execute(text(sql), params).fetchall()

    return [
        {
            "id": row[0],
            "ticker": row[1],
            "document_type": row[2],
            "filing_date": row[3],
            "content": row[4],
            "metadata": json.loads(row[5]),
            "similarity": float(row[6])
        }
        for row in results
    ]