backend/app/models.py

from sqlalchemy import Column, Integer, String, Float, DateTime, Text, ForeignKey, Boolean
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func

from .db import Base


class User(Base):
    """
    Stores user information from Firebase or OTP authentication.
    """
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=False)
    name = Column(String, nullable=True)
    picture = Column(String, nullable=True)
    
    # Auth method: 'firebase' or 'otp'
    auth_method = Column(String, default='firebase')
    
    # Firebase-specific
    firebase_uid = Column(String, unique=True, index=True, nullable=True)
    
    # OTP-specific
    email_verified = Column(Boolean, default=False)
    
    created_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
    )

    # Relationship to extraction records (explicitly specify user_id as the foreign key)
    # Note: primaryjoin must be specified because ExtractionRecord has multiple foreign keys to User
    extractions = relationship(
        "ExtractionRecord", 
        back_populates="user",
        primaryjoin="User.id == ExtractionRecord.user_id"
    )
    
    # Relationship to API keys (newly added for API key authentication)
    api_keys = relationship(
        "APIKey",
        back_populates="user",
        cascade="all, delete-orphan"
    )


class ExtractionRecord(Base):
    """
    Stores one extraction run so the History page can show past jobs.
    We'll fill it from the /api/extract endpoint later.
    """

    __tablename__ = "extractions"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)

    file_name = Column(String, index=True)
    file_type = Column(String)
    file_size = Column(String)

    status = Column(String)              # "completed" | "failed"
    confidence = Column(Float)           # overall confidence (0–100)
    fields_extracted = Column(Integer)   # number of fields extracted
    total_time_ms = Column(Integer)      # total processing time in ms

    raw_output = Column(Text)            # JSON string from the model
    file_base64 = Column(Text, nullable=True)  # Base64 encoded original file for preview
    error_message = Column(Text, nullable=True)

    created_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
    )

    # Relationship to user (explicitly specify user_id as the foreign key)
    # Note: primaryjoin must be specified because ExtractionRecord has multiple foreign keys to User
    user = relationship(
        "User", 
        back_populates="extractions",
        primaryjoin="ExtractionRecord.user_id == User.id"
    )
    
    # Track if this extraction was shared (original extraction ID)
    shared_from_extraction_id = Column(Integer, ForeignKey("extractions.id"), nullable=True, index=True)
    shared_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=True, index=True)


class ShareToken(Base):
    """
    Stores share tokens for sharing extractions with other users.
    """
    __tablename__ = "share_tokens"
    
    id = Column(Integer, primary_key=True, index=True)
    token = Column(String, unique=True, index=True, nullable=False)  # Unique share token
    extraction_id = Column(Integer, ForeignKey("extractions.id"), nullable=False, index=True)
    sender_user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    recipient_email = Column(String, nullable=True, index=True)  # Nullable for public share links
    expires_at = Column(DateTime(timezone=True), nullable=True)  # Optional expiration
    accessed = Column(Boolean, default=False)  # Track if link was accessed
    accessed_at = Column(DateTime(timezone=True), nullable=True)
    accessed_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    
    created_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
    )


class APIKey(Base):
    """
    Stores API keys for external application authentication.
    API keys are hashed before storage for security.
    """
    __tablename__ = "api_keys"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    name = Column(String, nullable=False)  # User-friendly name for the API key
    key_hash = Column(String, unique=True, index=True, nullable=False)  # Hashed API key
    key_prefix = Column(String, nullable=False)  # First 8 chars of key for display (e.g., "sk_live_")
    is_active = Column(Boolean, default=True, nullable=False)
    last_used_at = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
    )
    
    # Relationship to user
    user = relationship(
        "User",
        back_populates="api_keys"
    )