from __future__ import annotations

from datetime import datetime

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

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


class XeroConnection(Base, TimestampMixin):
    __tablename__ = "xero_connections"
    __table_args__ = (
        UniqueConstraint("tenant_id", "xero_tenant_id", name="uk_xero_conn_tenant_org"),
        Index("ix_xero_conn_tenant_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)
    tenant_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tenants.id", ondelete="RESTRICT"), nullable=False)
    xero_tenant_id: Mapped[str] = mapped_column(String(80), nullable=False)
    tenant_name: Mapped[str | None] = mapped_column(String(200), nullable=True)
    access_token: Mapped[str] = mapped_column(Text, nullable=False)
    refresh_token: Mapped[str] = mapped_column(Text, nullable=False)
    expires_at: Mapped[datetime] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=False)
    scopes: Mapped[str | None] = mapped_column(String(500), nullable=True)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)


class XeroSyncState(Base, TimestampMixin):
    __tablename__ = "xero_sync_states"
    __table_args__ = (
        UniqueConstraint("tenant_id", "connection_id", "resource", name="uk_xero_state_resource"),
        {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4", "mysql_collate": "utf8mb4_unicode_ci"},
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tenants.id", ondelete="RESTRICT"), nullable=False)
    connection_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("xero_connections.id", ondelete="CASCADE"), nullable=False)
    resource: Mapped[str] = mapped_column(String(30), nullable=False)
    last_success_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    cursor_data: Mapped[dict | None] = mapped_column(JSON, nullable=True)


class XeroEntityMap(Base, TimestampMixin):
    __tablename__ = "xero_entity_maps"
    __table_args__ = (
        UniqueConstraint("tenant_id", "connection_id", "entity_type", "xero_id", name="uk_xero_map_remote"),
        UniqueConstraint("tenant_id", "connection_id", "entity_type", "local_id", name="uk_xero_map_local"),
        Index("ix_xero_map_lookup", "tenant_id", "entity_type", "xero_id"),
        {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4", "mysql_collate": "utf8mb4_unicode_ci"},
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tenants.id", ondelete="RESTRICT"), nullable=False)
    connection_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("xero_connections.id", ondelete="CASCADE"), nullable=False)
    entity_type: Mapped[str] = mapped_column(String(30), nullable=False)
    xero_id: Mapped[str] = mapped_column(String(80), nullable=False)
    local_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    xero_updated_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    extra_data: Mapped[dict | None] = mapped_column(JSON, nullable=True)


class XeroSyncLog(Base, TimestampMixin):
    __tablename__ = "xero_sync_logs"
    __table_args__ = (
        Index("ix_xero_log_tenant_resource", "tenant_id", "resource", "created_at"),
        {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4", "mysql_collate": "utf8mb4_unicode_ci"},
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tenants.id", ondelete="RESTRICT"), nullable=False)
    connection_id: Mapped[int | None] = mapped_column(BigInteger, ForeignKey("xero_connections.id", ondelete="SET NULL"), nullable=True)
    resource: Mapped[str] = mapped_column(String(30), nullable=False)
    mode: Mapped[str] = mapped_column(String(20), nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False)
    created_count: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    updated_count: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    skipped_count: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    failed_count: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    message: Mapped[str | None] = mapped_column(Text, nullable=True)
    detail: Mapped[dict | None] = mapped_column(JSON, nullable=True)