from datetime import datetime

from sqlalchemy import BigInteger, String, JSON, UniqueConstraint, Index, ForeignKey
from sqlalchemy.dialects.mysql import DATETIME as MYSQL_DATETIME, TINYINT
from sqlalchemy.orm import Mapped, mapped_column

from app.core.models.base import Base, TimestampMixin, TenantMixin


class PosLane(Base, TenantMixin, TimestampMixin):
    __tablename__ = "pos_lanes"
    __table_args__ = (
        UniqueConstraint("tenant_id", "lane_id", name="uk_pos_lanes_tenant_lane"),
        Index("ix_pos_lanes_active", "tenant_id", "is_active"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    store_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    lane_id: Mapped[str] = mapped_column(String(80), nullable=False)
    lane_name: Mapped[str | None] = mapped_column(String(120), nullable=True)
    token_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)
    last_seen_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    store_code: Mapped[str | None] = mapped_column(String(40), nullable=True)
    store_timezone: Mapped[str | None] = mapped_column(String(64), nullable=True)
    pairing_code_hash: Mapped[str | None] = mapped_column(String(64), nullable=True)
    pairing_expires_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    pairing_consumed_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    store_country: Mapped[str | None] = mapped_column(String(2), nullable=True)
    store_province: Mapped[str | None] = mapped_column(String(80), nullable=True)


class PosOrderSync(Base, TenantMixin, TimestampMixin):
    __tablename__ = "pos_order_sync"
    __table_args__ = (
        UniqueConstraint("tenant_id", "idempotency_key", name="uk_pos_sync_tenant_idempotency"),
        UniqueConstraint("tenant_id", "local_order_no", name="uk_pos_sync_tenant_local_order"),
        Index("ix_pos_sync_lane_status", "tenant_id", "lane_id", "sync_status"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    lane_id: Mapped[str] = mapped_column(String(80), nullable=False)
    local_order_no: Mapped[str] = mapped_column(String(80), nullable=False)
    idempotency_key: Mapped[str] = mapped_column(String(160), nullable=False)
    payload_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    cloud_order_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("orders.id", ondelete="SET NULL"), nullable=True, index=True
    )
    sync_status: Mapped[str] = mapped_column(String(30), nullable=False, default="synced")
    conflict_reason: Mapped[str | None] = mapped_column(String(120), nullable=True)
    payload_json: Mapped[dict | None] = mapped_column(JSON, nullable=True)
    price_adjustments_json: Mapped[dict | None] = mapped_column(JSON, nullable=True)
    price_override_evidence: Mapped[dict | None] = mapped_column(JSON, nullable=True)


class PosCatalogueRevision(Base, TenantMixin, TimestampMixin):
    __tablename__ = "pos_catalogue_revision"
    __table_args__ = (
        UniqueConstraint("tenant_id", name="uk_pos_catalogue_revision_tenant"),
        {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4", "mysql_collate": "utf8mb4_unicode_ci"},
    )
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    revision: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)