backend/app/models/tender_detail.py

from sqlalchemy import Column, String, DateTime, JSON, Text, ForeignKey
from app.database import Base
from datetime import datetime

class TenderDetailTabModel(Base):
    """Store extracted detail tabs from tender pages"""
    __tablename__ = "tender_detail_tabs"
    
    id = Column(String(100), primary_key=True)  # "{tender_code}_{tab_name}"
    tender_code = Column(String(50), ForeignKey('tenders.code'), index=True)
    tab_name = Column(String(100))  # Preguntas, Historial, Apertura, Adjudicación, Antecedentes, etc.
    tab_type = Column(String(50))  # questions, history, opening, adjudication, attachments, criteria
    content_summary = Column(Text)  # Summary of tab content
    tab_metadata = Column(JSON, nullable=True)  # Tab-specific data (counts, dates, etc.)
    attachment_urls = Column(JSON, nullable=True)  # List of attachment URLs for this tab
    last_fetched = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    html_content = Column(Text, nullable=True)  # Optional: store raw HTML for later parsing

class TenderAttachmentDetailModel(Base):
    """Detailed information about tender attachments"""
    __tablename__ = "tender_attachment_details"
    
    id = Column(String(100), primary_key=True)  # Unique hash of URL
    tender_code = Column(String(50), ForeignKey('tenders.code'), index=True)
    attachment_name = Column(String(255), index=True)
    attachment_url = Column(Text)
    tab_category = Column(String(100))  # Administrativo, Técnico, Económico, etc.
    file_type = Column(String(50))  # PDF, DOC, XLS, etc.
    estimated_size = Column(String(50), nullable=True)  # For reference
    last_updated = Column(DateTime, default=datetime.utcnow)
    is_accessible = Column(JSON, nullable=True)  # Track if URL is still valid