from datetime import datetime
from typing import Optional
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy import Integer, String, DateTime, Index, JSON, UniqueConstraint, func
from shared.db.base import Base


class ContainerRelation(Base):
    __tablename__ = "container_relations"

    __table_args__ = (
        UniqueConstraint(
            "parent_type", "parent_id", "child_type", "child_id",
            name="uq_container_relations_parent_child",
        ),
        Index("ix_container_relations_parent", "parent_type", "parent_id"),
        Index("ix_container_relations_child", "child_type", "child_id"),
        Index(
            "ix_container_relations_relation_type",
            "relation_type", "parent_type", "parent_id",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)

    parent_type: Mapped[str] = mapped_column(String(32), nullable=False)
    parent_id: Mapped[int] = mapped_column(Integer, nullable=False)
    child_type: Mapped[str] = mapped_column(String(32), nullable=False)
    child_id: Mapped[int] = mapped_column(Integer, nullable=False)

    relation_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=False)
    metadata_: Mapped[Optional[dict]] = mapped_column("metadata", JSON, nullable=True)

    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    label: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        nullable=False,
    )
    deleted_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
    )
