backend/app/models.py

from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, Float
from sqlalchemy.orm import relationship
from datetime import datetime
from app.database import Base

class RFP(Base):
    __tablename__ = "rfps"
    
    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(500), nullable=False)
    description = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    questions = relationship("Question", back_populates="rfp", cascade="all, delete-orphan")

class Question(Base):
    __tablename__ = "questions"
    
    id = Column(Integer, primary_key=True, index=True)
    rfp_id = Column(Integer, ForeignKey("rfps.id"), nullable=False)
    section = Column(String(200))
    question_id = Column(String(50))  # Custom question ID like "Q1.1"
    question_text = Column(Text, nullable=False)
    answer_text = Column(Text, nullable=False)
    page_start = Column(Integer)
    page_end = Column(Integer)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    rfp = relationship("RFP", back_populates="questions")
    embedding = relationship("QuestionEmbedding", back_populates="question", uselist=False, cascade="all, delete-orphan")

class QuestionEmbedding(Base):
    __tablename__ = "question_embeddings"
    
    id = Column(Integer, primary_key=True, index=True)
    question_id = Column(Integer, ForeignKey("questions.id"), nullable=False, unique=True)
    embedding = Column(Text, nullable=False)  # JSON serialized numpy array
    
    question = relationship("Question", back_populates="embedding")

class UploadedRFP(Base):
    __tablename__ = "uploaded_rfps"
    
    id = Column(Integer, primary_key=True, index=True)
    filename = Column(String(500), nullable=False)
    rfp_name = Column(String(500))  # Friendly name for the RFP
    file_path = Column(String(1000), nullable=False)
    uploaded_at = Column(DateTime, default=datetime.utcnow)
    text_content = Column(Text)  # Extracted text from first 3 pages or full document