from datetime import datetime

from sqlalchemy import (
    String, Integer, Float, Boolean, DateTime, Text, ForeignKey, JSON,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from backend.database.connection import Base


class YouTubeUploadBatch(Base):
    __tablename__ = "youtube_upload_batches"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(255), default="")
    folder_path: Mapped[str] = mapped_column(Text)
    start_datetime: Mapped[datetime] = mapped_column(DateTime)
    interval_minutes: Mapped[int] = mapped_column(Integer, default=120)

    # Default metadata for all items in this batch
    default_description: Mapped[str] = mapped_column(Text, default="")
    default_tags: Mapped[str] = mapped_column(Text, default="")  # comma-separated
    default_category_id: Mapped[str] = mapped_column(String(10), default="22")  # People & Blogs
    default_privacy: Mapped[str] = mapped_column(String(20), default="private")
    use_publish_at: Mapped[bool] = mapped_column(Boolean, default=True)

    # Progress counters
    total_files: Mapped[int] = mapped_column(Integer, default=0)
    uploaded_count: Mapped[int] = mapped_column(Integer, default=0)
    failed_count: Mapped[int] = mapped_column(Integer, default=0)
    skipped_count: Mapped[int] = mapped_column(Integer, default=0)

    # pending | active | paused | completed | cancelled
    status: Mapped[str] = mapped_column(String(20), default="pending", index=True)

    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow, onupdate=datetime.utcnow,
    )

    items: Mapped[list["YouTubeUploadItem"]] = relationship(
        back_populates="batch", cascade="all, delete-orphan",
        order_by="YouTubeUploadItem.sort_order",
    )

    def to_dict(self, include_items: bool = False) -> dict:
        d = {
            "id": self.id,
            "name": self.name,
            "folder_path": self.folder_path,
            "start_datetime": self.start_datetime.isoformat() if self.start_datetime else None,
            "interval_minutes": self.interval_minutes,
            "default_description": self.default_description,
            "default_tags": self.default_tags,
            "default_category_id": self.default_category_id,
            "default_privacy": self.default_privacy,
            "use_publish_at": self.use_publish_at,
            "total_files": self.total_files,
            "uploaded_count": self.uploaded_count,
            "failed_count": self.failed_count,
            "skipped_count": self.skipped_count,
            "status": self.status,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }
        if include_items:
            d["items"] = [item.to_dict() for item in self.items]
        return d


class YouTubeUploadItem(Base):
    __tablename__ = "youtube_upload_items"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    batch_id: Mapped[int] = mapped_column(ForeignKey("youtube_upload_batches.id"), index=True)
    filename: Mapped[str] = mapped_column(String(500))
    file_path: Mapped[str] = mapped_column(Text)
    file_size_bytes: Mapped[int] = mapped_column(Integer, default=0)
    sort_order: Mapped[int] = mapped_column(Integer, default=0)

    # Metadata overrides (if empty, use batch defaults)
    title: Mapped[str] = mapped_column(String(100), default="")
    description: Mapped[str] = mapped_column(Text, default="")
    tags: Mapped[str] = mapped_column(Text, default="")  # comma-separated
    category_id: Mapped[str] = mapped_column(String(10), default="")

    # Calculated schedule time: start_datetime + sort_order * interval_minutes
    scheduled_time: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)

    # YouTube response
    youtube_video_id: Mapped[str] = mapped_column(String(20), default="")
    youtube_url: Mapped[str] = mapped_column(Text, default="")

    # pending | uploading | uploaded | failed | skipped | cancelled
    status: Mapped[str] = mapped_column(String(20), default="pending", index=True)
    error_message: Mapped[str] = mapped_column(Text, default="")
    retry_count: Mapped[int] = mapped_column(Integer, default=0)
    max_retries: Mapped[int] = mapped_column(Integer, default=3)

    uploaded_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    batch: Mapped["YouTubeUploadBatch"] = relationship(back_populates="items")

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "batch_id": self.batch_id,
            "filename": self.filename,
            "file_path": self.file_path,
            "file_size_bytes": self.file_size_bytes,
            "sort_order": self.sort_order,
            "title": self.title,
            "description": self.description,
            "tags": self.tags,
            "category_id": self.category_id,
            "scheduled_time": self.scheduled_time.isoformat() if self.scheduled_time else None,
            "youtube_video_id": self.youtube_video_id,
            "youtube_url": self.youtube_url,
            "status": self.status,
            "error_message": self.error_message,
            "retry_count": self.retry_count,
            "max_retries": self.max_retries,
            "uploaded_at": self.uploaded_at.isoformat() if self.uploaded_at else None,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }
