from sqlalchemy import String, Integer, Index, ForeignKey
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.core.models.base import Base


class Country(Base):
    __tablename__ = "countries"
    __table_args__ = {
        "mysql_engine": "InnoDB",
        "mysql_charset": "utf8mb4",
        "mysql_collate": "utf8mb4_unicode_ci",
    }

    code: Mapped[str] = mapped_column(String(2), primary_key=True)
    name_zh: Mapped[str] = mapped_column(String(100), nullable=False)
    name_en: Mapped[str] = mapped_column(String(100), nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)

    provinces: Mapped[list["CountryProvince"]] = relationship(
        "CountryProvince", back_populates="country", order_by="CountryProvince.sort_order"
    )


class CountryProvince(Base):
    __tablename__ = "country_provinces"
    __table_args__ = (
        Index("ix_province_country", "country_code"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    country_code: Mapped[str] = mapped_column(String(2), ForeignKey("countries.code", ondelete="CASCADE"), nullable=False)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    country: Mapped["Country"] = relationship("Country", back_populates="provinces")