"""
SQLAlchemy models for document management system.
Maps to existing tables in file_sys_metadata.db
"""
from datetime import datetime
from typing import Optional, List
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime
from sqlalchemy.orm import relationship
from app.core.database import Base


class DocumentFolder(Base):
    """Model for folder entries - maps to existing 'folders' table"""
    __tablename__ = "folders"
    __table_args__ = {'extend_existing': True}
    id = Column(Integer, primary_key=True, autoincrement=True)
    folder_name = Column(String(500), nullable=False)
    folder_path = Column(String(1000), nullable=False, unique=True)
    parent_path = Column(String(1000), default="")
    full_path = Column(String(2000), default="")
    file_count = Column(Integer, default=0)
    created_date = Column(String(50))
    agent_id = Column(String(100), nullable=True)
    
    # Relationship to files in this folder
    files = relationship("DocumentFile", back_populates="folder", lazy="dynamic")
    
    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "folder_name": self.folder_name,
            "folder_path": self.folder_path,
            "parent_path": self.parent_path,
            "full_path": self.full_path,
            "file_count": self.file_count,
            "created_date": self.created_date,
            "agent_id": self.agent_id
        }


class DocumentFile(Base):
    """Model for file entries - maps to existing 'files' table"""
    __tablename__ = "files"
    __table_args__ = {'extend_existing': True}
    id = Column(String(100), primary_key=True)
    filename = Column(String(500), nullable=False)
    original_filename = Column(String(500), nullable=False)
    file_type = Column(String(50))
    file_path = Column(String(1000), nullable=False)
    folder_path = Column(String(1000), ForeignKey("folders.folder_path"), default="")
    relative_path = Column(String(1000), default="")
    upload_date = Column(String(50))
    file_size = Column(Integer)
    content_preview = Column(Text)
    full_content = Column(Text)
    image_path = Column(String(1000))
    thumbnail_path = Column(String(1000))
    agent_id = Column(String(100), nullable=True)
    
    # Relationship to folder
    folder = relationship("DocumentFolder", back_populates="files")
    
    # Relationship to extracted images
    extracted_images = relationship(
        "ExtractedImage", 
        back_populates="file",
        cascade="all, delete-orphan",
        lazy="dynamic"
    )
    
    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "filename": self.filename,
            "original_filename": self.original_filename,
            "file_type": self.file_type,
            "file_path": self.file_path,
            "folder_path": self.folder_path,
            "relative_path": self.relative_path,
            "upload_date": self.upload_date,
            "file_size": self.file_size,
            "content_preview": self.content_preview,
            "image_path": self.image_path,
            "thumbnail_path": self.thumbnail_path,
            "agent_id": self.agent_id
        }
    
    def to_tuple(self) -> tuple:
        """Convert to tuple format for backward compatibility with DatabaseManager"""
        return (
            self.id,
            self.filename,
            self.original_filename,
            self.file_type,
            self.file_path,
            self.folder_path,
            self.relative_path,
            self.upload_date,
            self.file_size,
            self.content_preview,
            self.full_content,
            self.image_path,
            self.thumbnail_path,
            self.agent_id
        )


class ExtractedImage(Base):
    """Model for extracted images - maps to existing 'extracted_images' table"""
    __tablename__ = "extracted_images"
    __table_args__ = {'extend_existing': True}
    id = Column(Integer, primary_key=True, autoincrement=True)
    file_id = Column(String(100), ForeignKey("files.id", ondelete="CASCADE"), nullable=False)
    image_path = Column(String(1000), nullable=False)
    image_type = Column(String(50))
    page_number = Column(Integer)
    upload_date = Column(String(50))
    agent_id = Column(String(100), nullable=True)
    
    # Relationship to parent file
    file = relationship("DocumentFile", back_populates="extracted_images")
    
    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "file_id": self.file_id,
            "image_path": self.image_path,
            "image_type": self.image_type,
            "page_number": self.page_number,
            "upload_date": self.upload_date,
            "agent_id": self.agent_id
        }
    
    def to_tuple(self) -> tuple:
        """Convert to tuple format for backward compatibility"""
        return (
            self.id,
            self.image_path,
            self.image_type,
            self.page_number,
            self.agent_id
        )
