"""高级运费规则：存储层模型。

规则（advanced_shipping_rules）可读写；修订（revisions）只增不改，作为审计与回滚依据；
隐藏边（hides）与影子事件（shadow_events）为辅助表。报价引擎/公式求值见后续任务。
"""
from decimal import Decimal

from sqlalchemy import (
    BigInteger, DECIMAL, ForeignKeyConstraint, Index, Integer, JSON, String, UniqueConstraint,
)
from sqlalchemy.dialects.mysql import TINYINT

from sqlalchemy.orm import Mapped, mapped_column

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

_TBL = {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4",
        "mysql_collate": "utf8mb4_unicode_ci"}


class AdvancedShippingRule(Base, TenantMixin, TimestampMixin):
    """一条高级运费规则。刻意不对 (tenant_id, shipping_method_id) 加唯一约束——
    同一运费方案允许挂多条规则（聚合场景，见 quote_group）。"""
    __tablename__ = "advanced_shipping_rules"
    __table_args__ = (
        UniqueConstraint("tenant_id", "id", name="uk_asr_tenant_id"),
        Index("ix_asr_tenant_priority", "tenant_id", "enabled", "priority"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    shipping_method_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    quote_group: Mapped[str | None] = mapped_column(
        String(60), nullable=True,
        comment="聚合分组 key；同组规则共享 shipping_method_id + delivery_mode",
    )
    aggregate_strategy: Mapped[str] = mapped_column(String(20), nullable=False, default="independent")
    delivery_mode: Mapped[str] = mapped_column(String(20), nullable=False, default="standard")
    presentation: Mapped[dict] = mapped_column(
        JSON, nullable=False,
        comment="展示文案语言映射，如 {'en-NZ': {'name': ..}, 'zh-CN': {'name': ..}}",
    )
    conditions: Mapped[dict] = mapped_column(JSON, nullable=False, default=dict)
    pricing: Mapped[dict] = mapped_column(JSON, nullable=False, default=dict)
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    enabled: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    current_revision_no: Mapped[int] = mapped_column(
        Integer, nullable=False, default=1,
        comment="当前修订号，对应 advanced_shipping_rule_revisions 中最新记录",
    )
    from_legacy_conversion: Mapped[int] = mapped_column(
        TINYINT(1), nullable=False, default=0,
        comment="1=由 legacy 运费转换生成。转换重跑的幂等标记，刻意放在列上而不是 "
                "pricing JSON 里——JSON 客户端可写，一次 PUT 就能把标记抹掉，"
                "下次转换会重复建规则",
    )


class AdvancedShippingRuleRevision(Base, TenantMixin, TimestampMixin):
    """规则修订快照。只增不改：不提供 UPDATE/DELETE，回滚通过写入新修订实现。
    rule_id 被删除时按 RESTRICT 拒绝——修订是审计轨迹，不可随规则一起消失。"""
    __tablename__ = "advanced_shipping_rule_revisions"
    __table_args__ = (
        UniqueConstraint("tenant_id", "rule_id", "revision_no", name="uk_asr_rev_tenant_rule_no"),
        Index("ix_asr_rev_tenant_rule", "tenant_id", "rule_id", "revision_no"),
        ForeignKeyConstraint(
            ["tenant_id", "rule_id"],
            ["advanced_shipping_rules.tenant_id", "advanced_shipping_rules.id"],
            ondelete="RESTRICT", name="fk_asr_rev_rule",
        ),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    rule_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    revision_no: Mapped[int] = mapped_column(Integer, nullable=False)
    snapshot: Mapped[dict] = mapped_column(JSON, nullable=False, comment="该修订下规则的完整字段快照")
    changed_by: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    change_note: Mapped[str | None] = mapped_column(String(500), nullable=True)


class AdvancedShippingRuleHide(Base, TenantMixin, TimestampMixin):
    """规则隐藏边：rule_id 命中时隐藏 hidden_rule_id。每条规则最多 20 条边（Pydantic 层校验）。
    两端都是本租户内的规则；任一端所属规则被删除都级联清理隐藏边（非审计数据）。"""
    __tablename__ = "advanced_shipping_rule_hides"
    __table_args__ = (
        UniqueConstraint("tenant_id", "rule_id", "hidden_rule_id", name="uk_asr_hide_tenant_pair"),
        Index("ix_asr_hide_tenant_rule", "tenant_id", "rule_id"),
        ForeignKeyConstraint(
            ["tenant_id", "rule_id"],
            ["advanced_shipping_rules.tenant_id", "advanced_shipping_rules.id"],
            ondelete="CASCADE", name="fk_asr_hide_rule",
        ),
        ForeignKeyConstraint(
            ["tenant_id", "hidden_rule_id"],
            ["advanced_shipping_rules.tenant_id", "advanced_shipping_rules.id"],
            ondelete="CASCADE", name="fk_asr_hide_hidden_rule",
        ),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    rule_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    hidden_rule_id: Mapped[int] = mapped_column(BigInteger, nullable=False)


class AdvancedShippingShadowEvent(Base, TenantMixin, TimestampMixin):
    """Shadow 模式下记录候选报价与现行报价的差异，仅用于观测，不影响实际结算。"""
    __tablename__ = "advanced_shipping_shadow_events"
    __table_args__ = (
        Index("ix_asr_shadow_tenant_time", "tenant_id", "created_at", "id"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    shipping_method_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    rule_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    quote_group: Mapped[str | None] = mapped_column(String(60), nullable=True)
    event_type: Mapped[str] = mapped_column(String(40), nullable=False)
    legacy_amount: Mapped[Decimal | None] = mapped_column(DECIMAL(16, 4), nullable=True)
    candidate_amount: Mapped[Decimal | None] = mapped_column(DECIMAL(16, 4), nullable=True)
    payload: Mapped[dict | None] = mapped_column(JSON, nullable=True)


class AdvancedShippingSettings(Base, TenantMixin, TimestampMixin):
    """租户级插件配置，独立于 plugin_configs.config JSON 以便强校验（同 InventorySettings 模式）。"""
    __tablename__ = "advanced_shipping_settings"
    __table_args__ = (UniqueConstraint("tenant_id", name="uk_asr_settings_tenant"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    mode: Mapped[str] = mapped_column(
        String(20), nullable=False, default="shadow",
        comment="shadow=影子模式（首次启用固定为此）/enforced=正式生效计费",
    )
    operating_timezone: Mapped[str] = mapped_column(String(64), nullable=False, default="Pacific/Auckland")
    cache_version: Mapped[int] = mapped_column(
        Integer, nullable=False, default=1,
        comment="报价缓存 key 版本号，规则变更时递增使旧缓存失效",
    )