from __future__ import annotations

from datetime import datetime
from typing import Optional

from sqlalchemy import (
    Boolean,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    String,
    Text,
    UniqueConstraint,
    text,
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import Mapped, mapped_column, relationship

from db.base import Base


class IngestRun(Base):
    __tablename__ = "ingest_run"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    source: Mapped[str] = mapped_column(String(32), nullable=False)
    file_name: Mapped[Optional[str]] = mapped_column(String(255))
    file_hash: Mapped[Optional[str]] = mapped_column(String(128))
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    finished_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    status: Mapped[str] = mapped_column(String(32), default="started")
    row_count: Mapped[Optional[int]] = mapped_column(Integer)
    notes: Mapped[Optional[str]] = mapped_column(Text)

    raw_rows = relationship("RawFeedRow", back_populates="ingest_run")
    product_snapshots = relationship("ProductSnapshot", back_populates="ingest_run")
    price_snapshots = relationship("SkuPriceSnapshot", back_populates="ingest_run")


class RawFeedRow(Base):
    __tablename__ = "raw_feed_row"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    ingest_run_id: Mapped[int] = mapped_column(ForeignKey("ingest_run.id"), nullable=False)
    row_num: Mapped[int] = mapped_column(Integer, nullable=False)
    sku: Mapped[Optional[str]] = mapped_column(String(128))
    payload: Mapped[dict] = mapped_column(JSONB, nullable=False)
    errors: Mapped[Optional[dict]] = mapped_column(JSONB)

    ingest_run = relationship("IngestRun", back_populates="raw_rows")


class ProductSnapshot(Base):
    __tablename__ = "product_snapshot"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    source: Mapped[str] = mapped_column(String(32), nullable=False, default="plytix")
    load_id: Mapped[int] = mapped_column(ForeignKey("ingest_run.id"), nullable=False)
    valid_from: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    valid_to: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    name: Mapped[Optional[str]] = mapped_column(Text)
    description: Mapped[Optional[str]] = mapped_column(Text)
    short_description: Mapped[Optional[str]] = mapped_column(Text)
    product_type: Mapped[Optional[str]] = mapped_column(String(64))
    attribute_set_code: Mapped[Optional[str]] = mapped_column(String(64))
    categories_raw: Mapped[Optional[str]] = mapped_column(Text)
    base_image: Mapped[Optional[str]] = mapped_column(Text)
    additional_images: Mapped[Optional[str]] = mapped_column(Text)
    variant_of: Mapped[Optional[str]] = mapped_column(String(128))
    variant_list: Mapped[Optional[str]] = mapped_column(Text)

    attrs: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    row_hash: Mapped[str] = mapped_column(String(64), nullable=False)

    ingest_run = relationship("IngestRun", back_populates="product_snapshots")


class SkuPriceSnapshot(Base):
    __tablename__ = "sku_price_snapshot"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    load_id: Mapped[int] = mapped_column(ForeignKey("ingest_run.id"), nullable=False)
    valid_from: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    valid_to: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    price: Mapped[Optional[float]] = mapped_column(Numeric(12, 4))
    msrp: Mapped[Optional[float]] = mapped_column(Numeric(12, 4))
    qty: Mapped[Optional[float]] = mapped_column(Numeric(14, 4))
    is_in_stock: Mapped[Optional[bool]] = mapped_column(Boolean)
    source_payload: Mapped[Optional[dict]] = mapped_column(JSONB)

    ingest_run = relationship("IngestRun", back_populates="price_snapshots")


class MagentoProductSourceSnapshot(Base):
    """Raw Magento product snapshot imported from CSV/API before normalization."""

    __tablename__ = "magento_product_source_snapshot"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[Optional[int]] = mapped_column(ForeignKey("magento_connections.id"), nullable=True)
    load_id: Mapped[int] = mapped_column(ForeignKey("ingest_run.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    valid_from: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    valid_to: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    magento_product_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    product_type: Mapped[Optional[str]] = mapped_column(String(64))
    attribute_set_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    status: Mapped[Optional[str]] = mapped_column(String(32))
    visibility: Mapped[Optional[str]] = mapped_column(String(32))
    price: Mapped[Optional[float]] = mapped_column(Numeric(12, 4), nullable=True)
    qty: Mapped[Optional[float]] = mapped_column(Numeric(14, 4), nullable=True)
    is_in_stock: Mapped[Optional[bool]] = mapped_column(Boolean, nullable=True)

    payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    column_names: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    row_hash: Mapped[str] = mapped_column(String(64), nullable=False)


class ShopifyProductSourceSnapshot(Base):
    """Raw Shopify product/variant snapshot imported from Admin API before normalization."""

    __tablename__ = "shopify_product_source_snapshot"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[Optional[int]] = mapped_column(ForeignKey("shopify_connections.id"), nullable=True)
    load_id: Mapped[int] = mapped_column(ForeignKey("ingest_run.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    valid_from: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    valid_to: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    shopify_product_id: Mapped[Optional[str]] = mapped_column(String(128))
    shopify_variant_id: Mapped[Optional[str]] = mapped_column(String(128))
    product_type: Mapped[Optional[str]] = mapped_column(String(128))
    status: Mapped[Optional[str]] = mapped_column(String(32))
    price: Mapped[Optional[float]] = mapped_column(Numeric(12, 4), nullable=True)

    payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    column_names: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    row_hash: Mapped[str] = mapped_column(String(64), nullable=False)


class PlytixProductSourceSnapshot(Base):
    """Raw Plytix product snapshot imported from CSV before normalization."""

    __tablename__ = "plytix_product_source_snapshot"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    load_id: Mapped[int] = mapped_column(ForeignKey("ingest_run.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    valid_from: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    valid_to: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    product_websites: Mapped[Optional[str]] = mapped_column(Text)
    product_type: Mapped[Optional[str]] = mapped_column(String(64))
    status: Mapped[Optional[str]] = mapped_column(String(32))
    price: Mapped[Optional[float]] = mapped_column(Numeric(12, 4), nullable=True)

    payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    column_names: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    row_hash: Mapped[str] = mapped_column(String(64), nullable=False)


class PlytixAttributeCatalog(Base):
    """Full Plytix attribute catalog/list used for cleanup and mapping decisions."""

    __tablename__ = "plytix_attribute_catalog"
    __table_args__ = (
        UniqueConstraint("source_label", "attribute_code", name="uq_plytix_attribute_catalog_source_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    source_label: Mapped[str] = mapped_column(String(128), nullable=False, default="plytix")
    attribute_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    label: Mapped[Optional[str]] = mapped_column(Text)
    data_type: Mapped[Optional[str]] = mapped_column(String(64))
    group_name: Mapped[Optional[str]] = mapped_column(Text)
    destination_hint: Mapped[Optional[str]] = mapped_column(String(32))
    is_required: Mapped[Optional[bool]] = mapped_column(Boolean)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    imported_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ProductSupplementalAttributeValue(Base):
    """Arbitrary SKU/column/value rows imported from mapping or audit CSV files."""

    __tablename__ = "product_supplemental_attribute_value"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    load_id: Mapped[int] = mapped_column(ForeignKey("ingest_run.id"), nullable=False)
    source_label: Mapped[str] = mapped_column(String(128), nullable=False)
    file_name: Mapped[Optional[str]] = mapped_column(String(255))
    row_num: Mapped[int] = mapped_column(Integer, nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    column_name: Mapped[str] = mapped_column(Text, nullable=False)
    column_key: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    value: Mapped[Optional[str]] = mapped_column(Text)
    destination_hint: Mapped[Optional[str]] = mapped_column(String(32))
    imported_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)


class MasterProduct(Base):
    """Canonical SKU row owned by this app once DB becomes source of truth."""

    __tablename__ = "master_product"
    __table_args__ = (UniqueConstraint("sku", name="uq_master_product_sku"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    source_item: Mapped[Optional[str]] = mapped_column(String(128))
    name: Mapped[Optional[str]] = mapped_column(Text)
    product_family: Mapped[Optional[str]] = mapped_column(String(64))
    category_l1: Mapped[Optional[str]] = mapped_column(Text)
    category_l2: Mapped[Optional[str]] = mapped_column(Text)
    category_l3: Mapped[Optional[str]] = mapped_column(Text)
    brand: Mapped[Optional[str]] = mapped_column(Text)
    manufacturer: Mapped[Optional[str]] = mapped_column(Text)
    collection: Mapped[Optional[str]] = mapped_column(Text)
    membership_mode: Mapped[str] = mapped_column(String(32), nullable=False, default="native", index=True)
    brand_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_brand.id"), nullable=True, index=True)
    assembly_mode_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_assembly_mode.id"), nullable=True, index=True)
    family_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_product_family.id"), nullable=True, index=True)
    product_type_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_product_type.id"), nullable=True, index=True)
    collection_registry_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_collection_registry.id"), nullable=True, index=True
    )
    base_sku: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    variant_group_code: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    item_size: Mapped[Optional[str]] = mapped_column(Text)
    item_style: Mapped[Optional[str]] = mapped_column(Text)
    assembly_type: Mapped[Optional[str]] = mapped_column(String(32))
    stocked: Mapped[Optional[bool]] = mapped_column(Boolean)
    status: Mapped[Optional[str]] = mapped_column(String(32))
    normalization_status: Mapped[str] = mapped_column(String(32), nullable=False, default="pending", index=True)
    normalized_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    row_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterProductAttributeValue(Base):
    __tablename__ = "master_product_attribute_value"
    __table_args__ = (
        UniqueConstraint("product_id", "attribute_code", name="uq_master_product_attr_product_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("master_product.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    attribute_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    value: Mapped[Optional[str]] = mapped_column(Text)
    source_label: Mapped[Optional[str]] = mapped_column(String(128))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterProductPrice(Base):
    __tablename__ = "master_product_price"
    __table_args__ = (
        UniqueConstraint("product_id", "price_type", name="uq_master_product_price_product_type"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("master_product.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    price_type: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    amount: Mapped[Optional[float]] = mapped_column(Numeric(12, 4), nullable=True)
    currency: Mapped[str] = mapped_column(String(8), nullable=False, default="USD")
    source_label: Mapped[Optional[str]] = mapped_column(String(128))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterProductLocationAvailability(Base):
    __tablename__ = "master_product_location_availability"
    __table_args__ = (
        UniqueConstraint("product_id", "location_code", name="uq_master_product_location_product_location"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("master_product.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    location_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_location_registry.id"), nullable=True, index=True
    )
    location_code: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    is_available: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    source_value: Mapped[Optional[str]] = mapped_column(Text)
    source_label: Mapped[Optional[str]] = mapped_column(String(128))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterProductImage(Base):
    """Gallery URLs linked to a master SKU (imported from Images CSV or master data)."""

    __tablename__ = "master_product_image"
    __table_args__ = (
        UniqueConstraint("product_id", "file_name", name="uq_master_product_image_product_file"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("master_product.id"), nullable=False, index=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    file_name: Mapped[str] = mapped_column(String(512), nullable=False)
    image_url: Mapped[str] = mapped_column(Text, nullable=False)
    image_role: Mapped[Optional[str]] = mapped_column(String(64), index=True)
    view_suffix: Mapped[Optional[str]] = mapped_column(String(64))
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    source_label: Mapped[Optional[str]] = mapped_column(String(128))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterProductRelation(Base):
    """Canonical parent/child links between master SKUs (channel-agnostic)."""

    __tablename__ = "master_product_relation"
    __table_args__ = (
        UniqueConstraint("parent_sku", "child_sku", name="uq_master_product_relation_parent_child"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    parent_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    child_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    option_mapping: Mapped[Optional[dict]] = mapped_column(JSONB)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    source_label: Mapped[Optional[str]] = mapped_column(String(128))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterProductAssociation(Base):
    """Merchandising links: related / upsell / cross-sell (channel-agnostic)."""

    __tablename__ = "master_product_association"
    __table_args__ = (
        UniqueConstraint(
            "source_sku",
            "target_sku",
            "link_type",
            name="uq_master_product_association_source_target_type",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    source_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    target_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    link_type: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    origin: Mapped[str] = mapped_column(String(32), nullable=False, default="auto")
    source_label: Mapped[Optional[str]] = mapped_column(String(128))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ProductChannelAssignment(Base):
    __tablename__ = "product_channel_assignment"
    __table_args__ = (
        UniqueConstraint("product_id", "channel_code", name="uq_product_channel_assignment_product_channel"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("master_product.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    assignment_status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    reason: Mapped[Optional[str]] = mapped_column(Text)
    payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ProductAttributeMapping(Base):
    __tablename__ = "product_attribute_mapping"
    __table_args__ = (
        UniqueConstraint(
            "source_label",
            "source_column_key",
            "target_scope",
            name="uq_product_attribute_mapping_source_target",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    source_label: Mapped[str] = mapped_column(String(128), nullable=False)
    source_column_name: Mapped[Optional[str]] = mapped_column(Text)
    source_column_key: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    canonical_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    canonical_label: Mapped[Optional[str]] = mapped_column(Text)
    target_scope: Mapped[str] = mapped_column(String(32), nullable=False, default="canonical")
    data_type: Mapped[str] = mapped_column(String(32), nullable=False, default="text")
    transform_rule: Mapped[Optional[dict]] = mapped_column(JSONB)
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterStaticFieldMapping(Base):
    """Wide 1:1 map: one master static attribute → channel attribute code per channel."""

    __tablename__ = "master_static_field_mapping"
    __table_args__ = (
        UniqueConstraint("master_attribute_code", name="uq_master_static_field_mapping_master_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    master_attribute_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    master_label: Mapped[Optional[str]] = mapped_column(Text)
    field_group: Mapped[str] = mapped_column(String(32), nullable=False, default="core")
    data_type: Mapped[str] = mapped_column(String(32), nullable=False, default="text")
    magento_attribute_code: Mapped[Optional[str]] = mapped_column(String(255))
    shopify_attribute_code: Mapped[Optional[str]] = mapped_column(String(255))
    plytix_attribute_code: Mapped[Optional[str]] = mapped_column(String(255))
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelAttributeAlias(Base):
    """Approved canonical-to-channel attribute aliases used by action plans and channel payloads."""

    __tablename__ = "channel_attribute_alias"
    __table_args__ = (
        UniqueConstraint(
            "channel_code",
            "canonical_code",
            "channel_attribute_code",
            name="uq_channel_attribute_alias_channel_canonical_attr",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    canonical_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    channel_attribute_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    mapping_scope: Mapped[str] = mapped_column(String(32), nullable=False, default="dynamic", index=True)
    action: Mapped[str] = mapped_column(String(32), nullable=False, default="use_existing")
    data_type: Mapped[Optional[str]] = mapped_column(String(32))
    transform_rule: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelConnection(Base):
    __tablename__ = "channel_connection"
    __table_args__ = (
        UniqueConstraint("channel_type", "channel_code", "environment", name="uq_channel_connection_type_code_env"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_type: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    display_name: Mapped[Optional[str]] = mapped_column(Text)
    environment: Mapped[str] = mapped_column(String(32), nullable=False, default="production")
    base_url: Mapped[Optional[str]] = mapped_column(Text)
    api_base_url: Mapped[Optional[str]] = mapped_column(Text)
    store_code: Mapped[Optional[str]] = mapped_column(String(255))
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    config: Mapped[Optional[dict]] = mapped_column(JSONB)
    capabilities: Mapped[Optional[dict]] = mapped_column(JSONB)
    last_verified_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_error: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelTransformRule(Base):
    __tablename__ = "channel_transform_rule"
    __table_args__ = (
        UniqueConstraint("channel_code", "target", "rule_name", name="uq_channel_transform_rule_channel_target_name"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    target: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    rule_name: Mapped[str] = mapped_column(String(128), nullable=False)
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    rule: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelSchedule(Base):
    __tablename__ = "channel_schedule"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_connection_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    channel_type: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    native_connection_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    task_type: Mapped[str] = mapped_column(String(32), nullable=False)
    cron_expression: Mapped[str] = mapped_column(String(128), nullable=False)
    mode: Mapped[str] = mapped_column(String(32), nullable=False, default="incremental")
    dry_run: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    is_enabled: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    next_run_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), index=True)
    locked_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    locked_by: Mapped[Optional[str]] = mapped_column(String(128))
    last_enqueued_job_id: Mapped[Optional[int]] = mapped_column(Integer)
    last_run_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_status: Mapped[Optional[str]] = mapped_column(String(32))
    last_error: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelJob(Base):
    __tablename__ = "channel_job"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    schedule_id: Mapped[Optional[int]] = mapped_column(ForeignKey("channel_schedule.id"), nullable=True, index=True)
    channel_connection_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    channel_type: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    native_connection_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    job_type: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="queued", index=True)
    dry_run: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    mode: Mapped[str] = mapped_column(String(32), nullable=False, default="incremental")
    requested_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, index=True)
    started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    finished_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    total_count: Mapped[Optional[int]] = mapped_column(Integer)
    success_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    error_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    result: Mapped[Optional[dict]] = mapped_column(JSONB)
    backend_queue_id: Mapped[Optional[int]] = mapped_column(Integer)
    backend_job_id: Mapped[Optional[int]] = mapped_column(Integer)
    attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    max_attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=3)
    locked_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    locked_by: Mapped[Optional[str]] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterTaxonomyJob(Base):
    """Durable queue for long-running master taxonomy operations (fix_all, bootstrap, etc.)."""

    __tablename__ = "master_taxonomy_job"

    id: Mapped[str] = mapped_column(String(36), primary_key=True)
    job_type: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="queued", index=True)
    params: Mapped[Optional[dict]] = mapped_column(JSONB)
    progress: Mapped[Optional[dict]] = mapped_column(JSONB)
    result: Mapped[Optional[dict]] = mapped_column(JSONB)
    error: Mapped[Optional[str]] = mapped_column(Text)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    requested_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, index=True)
    started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    finished_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    max_attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=3)
    locked_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    locked_by: Mapped[Optional[str]] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow
    )


class MasterProductOverride(Base):
    """Manual canonical-field overrides (e.g. SEO title/description) that win over master data downstream.

    channel_code='' applies to every channel; a channel-specific row beats the global one.
    """

    __tablename__ = "master_product_override"
    __table_args__ = (
        UniqueConstraint("sku", "channel_code", "attribute_code", name="uq_master_product_override_sku_channel_attr"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, default="", index=True)
    attribute_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    value: Mapped[Optional[str]] = mapped_column(Text)
    source_label: Mapped[Optional[str]] = mapped_column(String(128))
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ShopifyConnection(Base):
    """Shopify Admin API credentials per shop, managed from the dashboard. status is the kill switch."""

    __tablename__ = "shopify_connections"
    __table_args__ = (
        UniqueConstraint("shop_code", name="uq_shopify_connections_shop_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    shop_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    shop_domain: Mapped[str] = mapped_column(Text, nullable=False)
    api_version: Mapped[str] = mapped_column(String(32), nullable=False, default="2026-04")
    admin_access_token: Mapped[Optional[str]] = mapped_column(Text)
    client_id: Mapped[Optional[str]] = mapped_column(Text)
    client_secret: Mapped[Optional[str]] = mapped_column(Text)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    environment: Mapped[str] = mapped_column(String(32), nullable=False, default="production")
    notes: Mapped[Optional[str]] = mapped_column(Text)
    last_verified_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_error: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterAttributeDefinition(Base):
    __tablename__ = "master_attribute_definition"
    __table_args__ = (
        UniqueConstraint("attribute_code", name="uq_master_attribute_definition_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    attribute_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    label: Mapped[Optional[str]] = mapped_column(Text)
    product_family: Mapped[Optional[str]] = mapped_column(String(64))
    purpose: Mapped[str] = mapped_column(String(32), nullable=False, default="presentation")
    data_type: Mapped[str] = mapped_column(String(32), nullable=False, default="text")
    target_scopes: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_required: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterBrand(Base):
    """Canonical brand registry."""

    __tablename__ = "master_brand"
    __table_args__ = (UniqueConstraint("code", name="uq_master_brand_code"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    aliases: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterAssemblyMode(Base):
    """Canonical assembly mode registry (Assembled, RTA, etc.)."""

    __tablename__ = "master_assembly_mode"
    __table_args__ = (UniqueConstraint("code", name="uq_master_assembly_mode_code"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    aliases: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterLocationRegistry(Base):
    """Canonical warehouse/store/webstore registry used by normalized availability."""

    __tablename__ = "master_location_registry"
    __table_args__ = (UniqueConstraint("code", name="uq_master_location_registry_code"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    location_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="warehouse", index=True)
    brand_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_brand.id"), nullable=True, index=True)
    channel_code: Mapped[Optional[str]] = mapped_column(String(32), index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    aliases: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterProductFamily(Base):
    """Canonical product family registry (Kitchen Cabinets, Doors, …)."""

    __tablename__ = "master_product_family"
    __table_args__ = (UniqueConstraint("code", name="uq_master_product_family_code"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    aliases: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterProductType(Base):
    """Canonical product type registry (Base, Wall, …)."""

    __tablename__ = "master_product_type"
    __table_args__ = (
        UniqueConstraint("code", "family_id", name="uq_master_product_type_code_family"),
        Index(
            "uq_master_product_type_code_global",
            "code",
            unique=True,
            postgresql_where=text("family_id IS NULL"),
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    family_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_product_family.id"), nullable=True, index=True)
    code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    aliases: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterCollectionRegistry(Base):
    """Canonical collection/series registry."""

    __tablename__ = "master_collection_registry"
    __table_args__ = (UniqueConstraint("code", name="uq_master_collection_registry_code"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    path_slug: Mapped[Optional[str]] = mapped_column(String(512), index=True)
    brand_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_brand.id"), nullable=True, index=True)
    family_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_product_family.id"), nullable=True, index=True)
    aliases: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class CollectionCommerceProfile(Base):
    """Normalized collection-level commerce intent such as samples and fulfillment modes."""

    __tablename__ = "collection_commerce_profile"
    __table_args__ = (
        UniqueConstraint(
            "collection_registry_id",
            "channel_code",
            "connection_id",
            name="uq_collection_commerce_profile_scope",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    collection_registry_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_collection_registry.id"), nullable=True, index=True
    )
    category_l1: Mapped[Optional[str]] = mapped_column(Text)
    collection_name: Mapped[Optional[str]] = mapped_column(Text, index=True)
    channel_code: Mapped[Optional[str]] = mapped_column(String(32), index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    sample_master_sku: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    sample_category_path_slug: Mapped[Optional[str]] = mapped_column(String(512), index=True)
    availability_modes_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    appointment_url: Mapped[Optional[str]] = mapped_column(Text)
    sample_cta_label: Mapped[Optional[str]] = mapped_column(String(64))
    appointment_cta_label: Mapped[Optional[str]] = mapped_column(String(64))
    badges_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    landing_metadata_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class BrochureTaxonomyGroup(Base):
    """Brochure-derived taxonomy bucket awaiting or carrying canonical mapping."""

    __tablename__ = "brochure_taxonomy_group"
    __table_args__ = (
        UniqueConstraint(
            "collection_registry_id",
            "category_l1",
            "brochure_l1_label",
            "brochure_l2_label",
            "brochure_leaf_label",
            name="uq_brochure_taxonomy_group_scope",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    collection_registry_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_collection_registry.id"), nullable=True, index=True
    )
    brochure_collection_code: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    brochure_file_name: Mapped[Optional[str]] = mapped_column(String(255), index=True)
    category_l1: Mapped[str] = mapped_column(Text, nullable=False)
    brochure_l1_label: Mapped[Optional[str]] = mapped_column(Text)
    brochure_l2_label: Mapped[Optional[str]] = mapped_column(Text)
    brochure_leaf_label: Mapped[Optional[str]] = mapped_column(Text)
    brochure_path_text: Mapped[str] = mapped_column(Text, nullable=False)
    canonical_taxonomy_path_slug: Mapped[Optional[str]] = mapped_column(String(512), index=True)
    canonical_taxonomy_path_label: Mapped[Optional[str]] = mapped_column(Text)
    mapping_status: Mapped[str] = mapped_column(String(32), nullable=False, default="unmapped", index=True)
    mapping_source: Mapped[Optional[str]] = mapped_column(String(64))
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class BrochureTaxonomyGroupSku(Base):
    """SKU membership declared by a brochure taxonomy group."""

    __tablename__ = "brochure_taxonomy_group_sku"
    __table_args__ = (
        UniqueConstraint("group_id", "brochure_sku", name="uq_brochure_taxonomy_group_sku"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    group_id: Mapped[int] = mapped_column(ForeignKey("brochure_taxonomy_group.id"), nullable=False, index=True)
    brochure_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    expected_master_sku: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    assignment_status: Mapped[str] = mapped_column(String(32), nullable=False, default="active", index=True)
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ManualTaxonomyAssignment(Base):
    """Reusable manual source-SKU to taxonomy assignment rules for outbound channels."""

    __tablename__ = "manual_taxonomy_assignment"
    __table_args__ = (
        UniqueConstraint("source_sku", "canonical_taxonomy_path_slug", name="uq_manual_taxonomy_assignment_source_path"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    source_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    canonical_taxonomy_path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    canonical_taxonomy_path_label: Mapped[Optional[str]] = mapped_column(Text)
    shopping_l1: Mapped[Optional[str]] = mapped_column(String(255), index=True)
    shopping_l2: Mapped[Optional[str]] = mapped_column(String(512), index=True)
    l2_category: Mapped[Optional[str]] = mapped_column(Text)
    l3_category: Mapped[Optional[str]] = mapped_column(Text)
    assignment_status: Mapped[str] = mapped_column(String(32), nullable=False, default="active", index=True)
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ManualTaxonomyAssignmentCollection(Base):
    """Collection expansion rows hanging off a manual taxonomy assignment rule."""

    __tablename__ = "manual_taxonomy_assignment_collection"
    __table_args__ = (
        UniqueConstraint("assignment_id", "collection_code", name="uq_manual_taxonomy_assignment_collection_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    assignment_id: Mapped[int] = mapped_column(ForeignKey("manual_taxonomy_assignment.id"), nullable=False, index=True)
    collection_registry_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_collection_registry.id"), nullable=True, index=True
    )
    collection_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    source_collection_code: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    sku_prefix: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    collection_name: Mapped[Optional[str]] = mapped_column(Text)
    collection_path_slug: Mapped[Optional[str]] = mapped_column(String(512), index=True)
    shopping_collection: Mapped[Optional[str]] = mapped_column(String(255), index=True)
    master_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    alias_codes: Mapped[Optional[dict]] = mapped_column(JSONB)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ManualAttributeLockRule(Base):
    """Locked attribute rules keyed by canonical taxonomy path and optional collection."""

    __tablename__ = "manual_attribute_lock_rule"
    __table_args__ = (
        UniqueConstraint(
            "canonical_taxonomy_path_slug",
            "path_match_kind",
            "collection_code",
            name="uq_manual_attribute_lock_rule_scope",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    canonical_taxonomy_path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    path_match_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="exact", index=True)
    collection_registry_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_collection_registry.id"), nullable=True, index=True
    )
    collection_code: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    collection_name: Mapped[Optional[str]] = mapped_column(Text)
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100, index=True)
    rule_status: Mapped[str] = mapped_column(String(32), nullable=False, default="active", index=True)
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ManualAttributeLockTarget(Base):
    """Forced attribute values emitted by a manual attribute lock rule."""

    __tablename__ = "manual_attribute_lock_target"
    __table_args__ = (
        UniqueConstraint("rule_id", "attribute_code", name="uq_manual_attribute_lock_target_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    rule_id: Mapped[int] = mapped_column(ForeignKey("manual_attribute_lock_rule.id"), nullable=False, index=True)
    attribute_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    assignment_mode: Mapped[str] = mapped_column(String(32), nullable=False, default="force", index=True)
    attribute_value: Mapped[Optional[str]] = mapped_column(Text)
    attribute_values_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_locked: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ManualAttributeVocabulary(Base):
    """Locked allowed-value vocabulary for a master attribute (pollution guard)."""

    __tablename__ = "manual_attribute_vocabulary"
    __table_args__ = (UniqueConstraint("attribute_code", name="uq_manual_attribute_vocabulary_code"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    attribute_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    label: Mapped[Optional[str]] = mapped_column(Text)
    is_locked: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)
    unknown_policy: Mapped[str] = mapped_column(String(32), nullable=False, default="drop")
    covered_codes_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ManualAttributeVocabularyValue(Base):
    """Canonical allowed value (+ aliases) for a locked attribute vocabulary."""

    __tablename__ = "manual_attribute_vocabulary_value"
    __table_args__ = (
        UniqueConstraint("vocabulary_id", "value", name="uq_manual_attribute_vocabulary_value"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    vocabulary_id: Mapped[int] = mapped_column(
        ForeignKey("manual_attribute_vocabulary.id"), nullable=False, index=True
    )
    value: Mapped[str] = mapped_column(Text, nullable=False)
    aliases_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    raw_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterCatalogIntentRun(Base):
    """Staged master catalog intent preview/confirm pipeline state."""

    __tablename__ = "master_catalog_intent_run"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="preview", index=True)
    source_label: Mapped[Optional[str]] = mapped_column(String(128))
    preview_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    result_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    sku_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    confirmed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterTaxonomyNode(Base):
    """Hierarchical master taxonomy (categories + collections) with parent/child structure."""

    __tablename__ = "master_taxonomy_node"
    __table_args__ = (UniqueConstraint("path_slug", name="uq_master_taxonomy_node_path_slug"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    parent_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_taxonomy_node.id"), nullable=True, index=True)
    node_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="category", index=True)
    code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    collection_registry_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_collection_registry.id"), nullable=True, index=True
    )
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterTaxonomyPathAlias(Base):
    """Redirect an alternate taxonomy path slug to a canonical master_taxonomy_node."""

    __tablename__ = "master_taxonomy_path_alias"
    __table_args__ = (UniqueConstraint("alias_path_slug", name="uq_master_taxonomy_path_alias_slug"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    alias_path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    canonical_node_id: Mapped[int] = mapped_column(ForeignKey("master_taxonomy_node.id"), nullable=False, index=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)


class MasterTaxonomyChannelLink(Base):
    """Remote Magento category / Shopify collection link for a master taxonomy node."""

    __tablename__ = "master_taxonomy_channel_link"
    __table_args__ = (
        UniqueConstraint(
            "taxonomy_node_id",
            "channel_code",
            "connection_id",
            "taxonomy_kind",
            name="uq_master_taxonomy_channel_link",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    taxonomy_node_id: Mapped[int] = mapped_column(ForeignKey("master_taxonomy_node.id"), nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    taxonomy_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="category")
    remote_id: Mapped[str] = mapped_column(String(255), nullable=False)
    remote_parent_id: Mapped[Optional[str]] = mapped_column(String(255))
    remote_path: Mapped[Optional[str]] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class CatalogNormalizationProfile(Base):
    """Normalization profile/vertical such as Kitchen Cabinets or Doors."""

    __tablename__ = "catalog_normalization_profile"
    __table_args__ = (UniqueConstraint("code", name="uq_catalog_normalization_profile_code"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    family_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_product_family.id"), nullable=True, index=True)
    config_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class CatalogNormalizationRule(Base):
    """Channel-neutral normalization rule such as ACH -> Anna Caramel Harvest traits."""

    __tablename__ = "catalog_normalization_rule"
    __table_args__ = (UniqueConstraint("rule_code", name="uq_catalog_normalization_rule_code"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    profile_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("catalog_normalization_profile.id"), nullable=True, index=True
    )
    rule_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    scope_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="sku_prefix", index=True)
    scope_value: Mapped[Optional[str]] = mapped_column(String(255), index=True)
    field_name: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    match_operator: Mapped[str] = mapped_column(String(32), nullable=False, default="equals")
    match_value: Mapped[Optional[str]] = mapped_column(Text)
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    stop_processing: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    rule_status: Mapped[str] = mapped_column(String(32), nullable=False, default="active", index=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class CatalogNormalizationRuleTarget(Base):
    """Field/value assignments emitted by a normalization rule."""

    __tablename__ = "catalog_normalization_rule_target"
    __table_args__ = (
        UniqueConstraint("rule_id", "target_field", name="uq_catalog_normalization_rule_target_field"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    rule_id: Mapped[int] = mapped_column(ForeignKey("catalog_normalization_rule.id"), nullable=False, index=True)
    target_field: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    target_value: Mapped[Optional[str]] = mapped_column(Text)
    target_code: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    target_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class CatalogValueAlias(Base):
    """Maps dirty source labels to stable canonical codes."""

    __tablename__ = "catalog_value_alias"
    __table_args__ = (
        UniqueConstraint("entity_kind", "field_name", "alias_value", name="uq_catalog_value_alias_entity_field_value"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    entity_kind: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    field_name: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    alias_value: Mapped[str] = mapped_column(Text, nullable=False)
    canonical_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    canonical_label: Mapped[Optional[str]] = mapped_column(Text)
    match_operator: Mapped[str] = mapped_column(String(32), nullable=False, default="equals")
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class CatalogResolvedField(Base):
    """Resolved normalized value per SKU/field, used as outbound source of truth."""

    __tablename__ = "catalog_resolved_field"
    __table_args__ = (
        UniqueConstraint("sku", "field_name", name="uq_catalog_resolved_field_sku_field"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    master_product_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_product.id"), nullable=True, index=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    field_name: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    raw_value: Mapped[Optional[str]] = mapped_column(Text)
    normalized_value: Mapped[Optional[str]] = mapped_column(Text)
    canonical_code: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    source_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="rule", index=True)
    source_rule_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("catalog_normalization_rule.id"), nullable=True, index=True
    )
    resolution_status: Mapped[str] = mapped_column(String(32), nullable=False, default="resolved", index=True)
    confidence: Mapped[Optional[float]] = mapped_column(Numeric(5, 4))
    payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class CatalogReviewQueueItem(Base):
    """Flagged normalization issues that require operator review before publish."""

    __tablename__ = "catalog_review_queue_item"
    __table_args__ = (
        Index("ix_catalog_review_queue_status_severity", "status", "severity"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    master_product_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_product.id"), nullable=True, index=True)
    sku: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    field_name: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    issue_code: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    severity: Mapped[str] = mapped_column(String(16), nullable=False, default="error")
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="open", index=True)
    raw_value: Mapped[Optional[str]] = mapped_column(Text)
    proposed_value: Mapped[Optional[str]] = mapped_column(Text)
    source_rule_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("catalog_normalization_rule.id"), nullable=True, index=True
    )
    context_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    assigned_to: Mapped[Optional[str]] = mapped_column(String(128))
    resolved_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelCatalogPolicy(Base):
    """Per-channel/store publication policy for scope filters, RTA handling, and variation projection."""

    __tablename__ = "channel_catalog_policy"
    __table_args__ = (
        UniqueConstraint("channel_code", "connection_id", name="uq_channel_catalog_policy_channel_connection"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    policy_name: Mapped[Optional[str]] = mapped_column(String(128))
    publish_mode: Mapped[str] = mapped_column(String(32), nullable=False, default="selected_scope")
    variation_mode: Mapped[str] = mapped_column(String(32), nullable=False, default="simple_only")
    shell_product_mode: Mapped[str] = mapped_column(String(32), nullable=False, default="none")
    shell_sku_template: Mapped[Optional[str]] = mapped_column(String(255))
    publish_shell_products: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    exclude_rta: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    include_assembled: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    include_accessories_without_brand: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    max_axes: Mapped[Optional[int]] = mapped_column(Integer)
    default_allowed_axes: Mapped[Optional[dict]] = mapped_column(JSONB)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelCatalogPolicyScope(Base):
    """Allow/deny rules for families, collections, brands, assembly modes, or product types per policy."""

    __tablename__ = "channel_catalog_policy_scope"
    __table_args__ = (
        UniqueConstraint(
            "policy_id",
            "scope_kind",
            "scope_code",
            "action",
            name="uq_channel_catalog_policy_scope_rule",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    policy_id: Mapped[int] = mapped_column(ForeignKey("channel_catalog_policy.id"), nullable=False, index=True)
    scope_kind: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    scope_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    action: Mapped[str] = mapped_column(String(16), nullable=False, default="include")
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelVariationPolicy(Base):
    """Variation projection overrides per channel/store and scope."""

    __tablename__ = "channel_variation_policy"
    __table_args__ = (
        UniqueConstraint(
            "channel_code",
            "connection_id",
            "scope_kind",
            "scope_code",
            name="uq_channel_variation_policy_scope",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    scope_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="global", index=True)
    scope_code: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    variation_mode: Mapped[str] = mapped_column(String(32), nullable=False, default="simple_only")
    allowed_axes: Mapped[Optional[dict]] = mapped_column(JSONB)
    axis_priority: Mapped[Optional[dict]] = mapped_column(JSONB)
    max_axes: Mapped[Optional[int]] = mapped_column(Integer)
    shell_product_required: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    shell_sku_template: Mapped[Optional[str]] = mapped_column(String(255))
    parent_group_strategy: Mapped[Optional[str]] = mapped_column(String(64))
    notes: Mapped[Optional[str]] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelShellProduct(Base):
    """Generated parent/shell SKU registry for channel-specific variation projections."""

    __tablename__ = "channel_shell_product"
    __table_args__ = (
        UniqueConstraint("channel_code", "connection_id", "shell_sku", name="uq_channel_shell_product_sku"),
        UniqueConstraint(
            "channel_code",
            "connection_id",
            "variant_group_code",
            "assembly_mode_id",
            name="uq_channel_shell_product_group",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    policy_id: Mapped[Optional[int]] = mapped_column(ForeignKey("channel_catalog_policy.id"), nullable=True, index=True)
    variation_policy_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("channel_variation_policy.id"), nullable=True, index=True
    )
    shell_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    shell_name: Mapped[Optional[str]] = mapped_column(Text)
    base_sku: Mapped[Optional[str]] = mapped_column(String(128), index=True)
    variant_group_code: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    assembly_mode_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_assembly_mode.id"), nullable=True, index=True)
    family_id: Mapped[Optional[int]] = mapped_column(ForeignKey("master_product_family.id"), nullable=True, index=True)
    collection_registry_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_collection_registry.id"), nullable=True, index=True
    )
    shell_status: Mapped[str] = mapped_column(String(32), nullable=False, default="planned", index=True)
    remote_id: Mapped[Optional[str]] = mapped_column(String(255))
    payload_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelProjectionRun(Base):
    """Materialization run for precomputed channel projection rows."""

    __tablename__ = "channel_projection_run"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    scope_key: Mapped[str] = mapped_column(String(255), nullable=False, default="assigned")
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="ready", index=True)
    product_structure: Mapped[Optional[str]] = mapped_column(String(32))
    sku_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    invalidated_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    notes: Mapped[Optional[str]] = mapped_column(Text)
    meta_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelProjectionRow(Base):
    """Precomputed per-SKU channel projection row used by fast outbound pushes."""

    __tablename__ = "channel_projection_row"
    __table_args__ = (
        UniqueConstraint("channel_code", "connection_id", "master_sku", name="uq_channel_projection_row_master"),
        UniqueConstraint("channel_code", "connection_id", "channel_sku", name="uq_channel_projection_row_channel"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    run_id: Mapped[Optional[int]] = mapped_column(ForeignKey("channel_projection_run.id"), nullable=True, index=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    master_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    channel_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    product_role: Mapped[str] = mapped_column(String(32), nullable=False, default="standalone", index=True)
    product_structure: Mapped[Optional[str]] = mapped_column(String(32))
    projection_status: Mapped[str] = mapped_column(String(32), nullable=False, default="ready", index=True)
    canonical_fields_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    channel_fields_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    relation_fields_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    association_fields_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    taxonomy_targets_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    prices_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    override_codes_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    price: Mapped[Optional[float]] = mapped_column(Numeric(12, 4))
    payload_hash: Mapped[Optional[str]] = mapped_column(String(64), index=True)
    source_row_hash: Mapped[Optional[str]] = mapped_column(String(64), index=True)
    normalized_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    published_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelSkuPrefixMapping(Base):
    """Maps master SKU prefix → channel SKU prefix (e.g. HPW* → HD-CW* on Shopify)."""

    __tablename__ = "channel_sku_prefix_mapping"
    __table_args__ = (
        Index(
            "uq_channel_sku_prefix_channel_master_global",
            "channel_code",
            "master_prefix",
            unique=True,
            postgresql_where=text("connection_id IS NULL"),
        ),
        Index(
            "uq_channel_sku_prefix_master_connection",
            "master_prefix",
            "connection_id",
            unique=True,
            postgresql_where=text("connection_id IS NOT NULL"),
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    master_prefix: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    channel_prefix: Mapped[str] = mapped_column(String(64), nullable=False)
    mapping_status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelSkuMapping(Base):
    """Maps canonical master SKU to the SKU string used on a specific channel."""

    __tablename__ = "channel_sku_mapping"
    __table_args__ = (
        Index(
            "uq_channel_sku_mapping_master_channel_global",
            "master_sku",
            "channel_code",
            unique=True,
            postgresql_where=text("connection_id IS NULL"),
        ),
        Index(
            "uq_channel_sku_mapping_channel_sku_global",
            "channel_code",
            "channel_sku",
            unique=True,
            postgresql_where=text("connection_id IS NULL"),
        ),
        Index(
            "uq_channel_sku_mapping_master_connection",
            "master_sku",
            "connection_id",
            unique=True,
            postgresql_where=text("connection_id IS NOT NULL"),
        ),
        Index(
            "uq_channel_sku_mapping_channel_sku_connection",
            "channel_sku",
            "connection_id",
            unique=True,
            postgresql_where=text("connection_id IS NOT NULL"),
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    master_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    channel_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    remote_id: Mapped[Optional[str]] = mapped_column(String(255))
    mapping_status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    match_source: Mapped[Optional[str]] = mapped_column(String(64))
    notes: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelPublishState(Base):
    __tablename__ = "channel_publish_state"
    __table_args__ = (
        UniqueConstraint("sku", "channel_code", name="uq_channel_publish_state_sku_channel"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    remote_id: Mapped[Optional[str]] = mapped_column(String(255))
    payload_hash: Mapped[Optional[str]] = mapped_column(String(64))
    last_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    last_published_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_status: Mapped[Optional[str]] = mapped_column(String(32))
    last_error: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelPipelineReadiness(Base):
    """Cached pre-push readiness report (computed async, served to the dashboard)."""

    __tablename__ = "channel_pipeline_readiness"
    __table_args__ = (
        UniqueConstraint("channel_code", "scope_key", name="uq_channel_pipeline_readiness_scope"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    scope_key: Mapped[str] = mapped_column(String(255), nullable=False)
    only_assigned: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    magento_connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    shopify_connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="missing", index=True)
    report: Mapped[Optional[dict]] = mapped_column(JSONB)
    last_error: Mapped[Optional[str]] = mapped_column(Text)
    refresh_requested_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), index=True)
    refresh_started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    computed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    locked_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    locked_by: Mapped[Optional[str]] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class AttributeDef(Base):
    __tablename__ = "attribute_def"

    attribute_code: Mapped[str] = mapped_column(String(128), primary_key=True)
    frontend_label: Mapped[Optional[str]] = mapped_column(String(255))
    type: Mapped[Optional[str]] = mapped_column(String(64))
    is_required: Mapped[bool] = mapped_column(Boolean, default=False)
    allows_free_text: Mapped[bool] = mapped_column(Boolean, default=False)
    options_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)


class AttributeOption(Base):
    __tablename__ = "attribute_option"
    __table_args__ = (UniqueConstraint("attribute_code", "option_value", name="uq_attr_option"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    attribute_code: Mapped[str] = mapped_column(ForeignKey("attribute_def.attribute_code"), nullable=False)
    option_value: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)


class AttributeDiscovery(Base):
    __tablename__ = "attribute_discovery"
    __table_args__ = (UniqueConstraint("attribute_code", "new_value", name="uq_attr_discovery_value"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    ingest_run_id: Mapped[int] = mapped_column(ForeignKey("ingest_run.id"), nullable=False)
    sku: Mapped[Optional[str]] = mapped_column(String(128))
    attribute_code: Mapped[str] = mapped_column(String(128), nullable=False)
    new_value: Mapped[str] = mapped_column(Text, nullable=False)
    source: Mapped[Optional[str]] = mapped_column(String(64))
    first_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    status: Mapped[str] = mapped_column(String(32), default="new")
    notes: Mapped[Optional[str]] = mapped_column(Text)


class ExportJob(Base):
    __tablename__ = "export_job"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    requested_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    as_of_timestamp: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    plytix_run_id: Mapped[Optional[int]] = mapped_column(ForeignKey("ingest_run.id"))
    netsuite_run_id: Mapped[Optional[int]] = mapped_column(ForeignKey("ingest_run.id"))
    status: Mapped[str] = mapped_column(String(32), default="started")
    artifact_path: Mapped[Optional[str]] = mapped_column(Text)
    stats: Mapped[Optional[dict]] = mapped_column(JSONB)


class ExportRow(Base):
    __tablename__ = "export_row"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    export_job_id: Mapped[int] = mapped_column(ForeignKey("export_job.id"), nullable=False)
    sku: Mapped[Optional[str]] = mapped_column(String(128))
    row_payload: Mapped[dict] = mapped_column(JSONB, nullable=False)
    quarantine_reason: Mapped[Optional[str]] = mapped_column(Text)


class PlytixFileQueue(Base):
    __tablename__ = "plytix_file_queue"
    __table_args__ = (UniqueConstraint("file_path", "file_mtime", name="uq_plytix_file_queue_path_mtime"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    label: Mapped[str] = mapped_column(String(128), nullable=False)
    file_path: Mapped[str] = mapped_column(Text, nullable=False)
    file_name: Mapped[str] = mapped_column(String(255), nullable=False)
    file_mtime: Mapped[float] = mapped_column(Numeric(20, 6), nullable=False)
    file_size: Mapped[Optional[int]] = mapped_column(Integer)
    status: Mapped[str] = mapped_column(String(32), default="new")
    last_error: Mapped[Optional[str]] = mapped_column(Text)
    discovered_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    processing_started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    processed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))


class SkuOverride(Base):
    __tablename__ = "sku_override"
    __table_args__ = (UniqueConstraint("sku", "field", name="uq_sku_override"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    field: Mapped[str] = mapped_column(String(128), nullable=False)
    value: Mapped[str] = mapped_column(Text, nullable=False)
    source: Mapped[Optional[str]] = mapped_column(String(64))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)


class ImageShortUrl(Base):
    __tablename__ = "image_short_url"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    slug: Mapped[str] = mapped_column(String(12), nullable=False, unique=True, index=True)
    destination_url: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)


class MagentoConnection(Base):
    __tablename__ = "magento_connections"
    __table_args__ = (
        UniqueConstraint("tenant_id", "store_base_url", "environment", name="uq_magento_connections_tenant_store_env"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[Optional[str]] = mapped_column(String(64))
    store_base_url: Mapped[str] = mapped_column(Text, nullable=False)
    magento_api_base_url: Mapped[Optional[str]] = mapped_column(Text)
    internal_api_base_url: Mapped[Optional[str]] = mapped_column(Text)
    internal_host_header: Mapped[Optional[str]] = mapped_column(String(255))
    prefer_internal_api: Mapped[bool] = mapped_column(Boolean, default=False)
    rest_base_path: Mapped[str] = mapped_column(String(64), default="V1")
    store_code: Mapped[Optional[str]] = mapped_column(String(64))
    environment: Mapped[str] = mapped_column(String(32), nullable=False)
    consumer_key: Mapped[str] = mapped_column(String(255), nullable=False)
    consumer_secret: Mapped[str] = mapped_column(Text, nullable=False)
    access_token: Mapped[str] = mapped_column(Text, nullable=False)
    access_token_secret: Mapped[str] = mapped_column(Text, nullable=False)
    scopes: Mapped[Optional[dict]] = mapped_column(JSONB)
    status: Mapped[str] = mapped_column(String(32), default="active")
    magento_version: Mapped[Optional[str]] = mapped_column(Text)
    is_msi_enabled: Mapped[Optional[bool]] = mapped_column(Boolean)
    default_website_id: Mapped[Optional[int]] = mapped_column(Integer)
    default_store_id: Mapped[Optional[int]] = mapped_column(Integer)
    protected_fields: Mapped[Optional[dict]] = mapped_column(JSONB)
    signature_method: Mapped[str] = mapped_column(String(32), default="HMAC-SHA1")
    last_verified_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_error: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MagentoSyncJob(Base):
    __tablename__ = "magento_sync_jobs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="started")
    dry_run: Mapped[bool] = mapped_column(Boolean, default=False)
    requested_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    finished_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    total_count: Mapped[Optional[int]] = mapped_column(Integer)
    success_count: Mapped[int] = mapped_column(Integer, default=0)
    error_count: Mapped[int] = mapped_column(Integer, default=0)
    notes: Mapped[Optional[str]] = mapped_column(Text)


class MagentoSyncItem(Base):
    __tablename__ = "magento_sync_items"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    job_id: Mapped[int] = mapped_column(ForeignKey("magento_sync_jobs.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    action: Mapped[Optional[str]] = mapped_column(String(16))
    payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    status: Mapped[str] = mapped_column(String(32), default="pending")
    error: Mapped[Optional[str]] = mapped_column(Text)
    magento_response: Mapped[Optional[dict]] = mapped_column(JSONB)
    attempts: Mapped[int] = mapped_column(Integer, default=0)
    duration_ms: Mapped[Optional[int]] = mapped_column(Integer)
    http_status: Mapped[Optional[int]] = mapped_column(Integer)
    idempotency_key: Mapped[Optional[str]] = mapped_column(Text)
    remote_image_urls: Mapped[Optional[dict]] = mapped_column(JSONB)
    version_id: Mapped[Optional[int]] = mapped_column(ForeignKey("magento_product_versions.id"), nullable=True)
    mode: Mapped[Optional[str]] = mapped_column(String(16), default="sync")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)


class MagentoMediaMap(Base):
    __tablename__ = "magento_media_map"
    __table_args__ = (
        UniqueConstraint("connection_id", "sku", "remote_url", name="uq_magento_media_map_conn_sku_url"),
        UniqueConstraint("connection_id", "sku", "content_sha256", name="uq_magento_media_map_conn_sku_sha256"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    remote_url: Mapped[str] = mapped_column(Text, nullable=False)
    content_sha256: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    magento_entry_id: Mapped[Optional[int]] = mapped_column(Integer)
    magento_file: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MagentoCatalogState(Base):
    """Pulled Magento product state: price, media, etc. Magento as source of truth."""

    __tablename__ = "magento_catalog_state"
    __table_args__ = (UniqueConstraint("connection_id", "sku", name="uq_magento_catalog_state_conn_sku"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    magento_product_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    updated_at_magento: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    price: Mapped[Optional[float]] = mapped_column(Numeric(12, 4), nullable=True)
    special_price: Mapped[Optional[float]] = mapped_column(Numeric(12, 4), nullable=True)
    media_files: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    media_hash: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    payload_hash: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    last_pulled_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)


class PlytixFeedEvent(Base):
    __tablename__ = "plytix_feed_events"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    label: Mapped[str] = mapped_column(String(128), nullable=False)
    type: Mapped[str] = mapped_column(String(32), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    payload: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)


class MagentoSyncPending(Base):
    """Connection-scoped pending work for Magento. UPSERT on plan; process in order. Survives restart."""
    __tablename__ = "magento_sync_pending"
    __table_args__ = (
        UniqueConstraint("connection_id", "sku", "action", name="uq_magento_sync_pending_conn_sku_action"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    action: Mapped[str] = mapped_column(String(16), nullable=False)
    payload: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    status: Mapped[str] = mapped_column(String(32), default="pending")
    pass_number: Mapped[int] = mapped_column(Integer, default=1)
    idempotency_key: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    duration_ms: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    http_status: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MagentoSyncQueue(Base):
    __tablename__ = "magento_sync_queue"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    label: Mapped[str] = mapped_column(String(128), nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="queued")
    queued_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    finished_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    last_error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    options: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    attempts: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    max_attempts: Mapped[int] = mapped_column(Integer, default=3, server_default="3")
    job_id: Mapped[Optional[int]] = mapped_column(ForeignKey("magento_sync_jobs.id"), nullable=True)


class MagentoAttributeCache(Base):
    """Cached Magento attribute metadata for configurable option creation."""

    __tablename__ = "magento_attribute_cache"
    __table_args__ = (
        UniqueConstraint("connection_id", "attribute_code", name="uq_magento_attribute_cache_conn_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    attribute_code: Mapped[str] = mapped_column(Text, nullable=False)
    attribute_id: Mapped[int] = mapped_column(Integer, nullable=False)
    frontend_label: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    frontend_input: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    backend_type: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    is_user_defined: Mapped[Optional[bool]] = mapped_column(Boolean, nullable=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoAttributeOptionCache(Base):
    """Cached Magento attribute options: label -> value_index for configurable variations."""

    __tablename__ = "magento_attribute_option_cache"
    __table_args__ = (
        UniqueConstraint(
            "connection_id", "attribute_code", "option_label_norm",
            name="uq_magento_attribute_option_cache_conn_code_norm",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    attribute_code: Mapped[str] = mapped_column(Text, nullable=False)
    option_label: Mapped[str] = mapped_column(Text, nullable=False)
    option_label_norm: Mapped[str] = mapped_column(Text, nullable=False)
    value_index: Mapped[int] = mapped_column(Integer, nullable=False)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoCategoryCache(Base):
    """Cached Magento category tree for resolving category paths to IDs."""

    __tablename__ = "magento_category_cache"
    __table_args__ = (
        UniqueConstraint("connection_id", "magento_category_id", name="uq_magento_category_cache_conn_catid"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    magento_category_id: Mapped[int] = mapped_column(Integer, nullable=False)
    parent_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    path: Mapped[str] = mapped_column(Text, nullable=False)
    path_names: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    level: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoSyncState(Base):
    """One row per connection+sku for incremental sync hashes."""

    __tablename__ = "magento_sync_state"
    __table_args__ = (
        UniqueConstraint("connection_id", "sku", name="uq_magento_sync_state_connection_sku"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    data_hash: Mapped[Optional[str]] = mapped_column(String(64))
    images_hash: Mapped[Optional[str]] = mapped_column(String(64))
    relations_hash: Mapped[Optional[str]] = mapped_column(String(64))
    associations_hash: Mapped[Optional[str]] = mapped_column(String(64))
    last_seen_in_feed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_pushed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_error: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MagentoSyncNotification(Base):
    __tablename__ = "magento_sync_notifications"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    job_id: Mapped[int] = mapped_column(ForeignKey("magento_sync_jobs.id"), nullable=False)
    channel: Mapped[str] = mapped_column(String(32), default="email")
    to_email: Mapped[str] = mapped_column(String(255), nullable=False)
    subject: Mapped[str] = mapped_column(Text, nullable=False)
    body: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="queued")
    error: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    sent_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))


class MagentoProductVersion(Base):
    """Versioned product payload snapshots for rollback."""

    __tablename__ = "magento_product_versions"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    job_id: Mapped[Optional[int]] = mapped_column(ForeignKey("magento_sync_jobs.id"), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    source: Mapped[str] = mapped_column(String(32), default="sync")
    status: Mapped[str] = mapped_column(String(16), default="applied", server_default="applied")
    data_hash: Mapped[Optional[str]] = mapped_column(String(64))
    images_hash: Mapped[Optional[str]] = mapped_column(String(64))
    relations_hash: Mapped[Optional[str]] = mapped_column(String(64))
    product_payload_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    media_payload_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    relations_payload_json: Mapped[Optional[dict]] = mapped_column(JSONB)


class MagentoStoreRegistry(Base):
    """Magento store/website structure from baseline sync."""

    __tablename__ = "magento_store_registry"
    __table_args__ = (
        UniqueConstraint("connection_id", "website_id", "store_id", name="uq_store_registry_conn_web_store"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    website_id: Mapped[int] = mapped_column(Integer, nullable=False)
    website_code: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    website_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    store_group_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    store_group_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    store_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    store_code: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    store_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    is_default: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    raw_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoCategoryRegistry(Base):
    """Magento category tree from baseline sync."""

    __tablename__ = "magento_category_registry"
    __table_args__ = (
        UniqueConstraint("connection_id", "category_id", name="uq_category_registry_conn_catid"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    category_id: Mapped[int] = mapped_column(Integer, nullable=False)
    parent_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    path: Mapped[str] = mapped_column(Text, nullable=False)
    path_names: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    level: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoAttributeRegistry(Base):
    """Magento attribute metadata from baseline sync."""

    __tablename__ = "magento_attribute_registry"
    __table_args__ = (
        UniqueConstraint("connection_id", "attribute_code", name="uq_attribute_registry_conn_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    attribute_code: Mapped[str] = mapped_column(Text, nullable=False)
    attribute_id: Mapped[int] = mapped_column(Integer, nullable=False)
    frontend_label: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    frontend_input: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    backend_type: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    is_user_defined: Mapped[Optional[bool]] = mapped_column(Boolean, nullable=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoAttributeOptionRegistry(Base):
    """Magento attribute option from baseline sync: label → value_index."""

    __tablename__ = "magento_attribute_option_registry"
    __table_args__ = (
        UniqueConstraint("connection_id", "attribute_code", "option_label_norm", name="uq_attr_opt_registry_conn_code_norm"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    attribute_code: Mapped[str] = mapped_column(Text, nullable=False)
    option_label: Mapped[str] = mapped_column(Text, nullable=False)
    option_label_norm: Mapped[str] = mapped_column(Text, nullable=False)
    value_index: Mapped[int] = mapped_column(Integer, nullable=False)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class ShopifyCollectionRegistry(Base):
    """Shopify manual/smart collections pulled from Admin GraphQL."""

    __tablename__ = "shopify_collection_registry"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[Optional[int]] = mapped_column(ForeignKey("shopify_connections.id"), nullable=True)
    shop_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    collection_id: Mapped[str] = mapped_column(String(255), nullable=False)
    handle: Mapped[Optional[str]] = mapped_column(String(255))
    title: Mapped[str] = mapped_column(Text, nullable=False)
    collection_type: Mapped[str] = mapped_column(String(32), nullable=False, default="manual")
    rules_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    product_count: Mapped[Optional[int]] = mapped_column(Integer)
    raw_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class ShopifyTaxonomyRegistry(Base):
    """Shopify Standard Product Taxonomy categories (global, pulled via Admin GraphQL)."""

    __tablename__ = "shopify_taxonomy_registry"
    __table_args__ = (
        UniqueConstraint("taxonomy_id", name="uq_shopify_taxonomy_registry_taxonomy_id"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    taxonomy_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    full_name: Mapped[str] = mapped_column(Text, nullable=False)
    parent_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    level: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_leaf: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    is_root: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    search_term: Mapped[Optional[str]] = mapped_column(String(128))
    raw_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class ProductChannelTaxonomyAssignment(Base):
    """Per-SKU channel taxonomy placement (Magento category ids, Shopify product category, collections)."""

    __tablename__ = "product_channel_taxonomy_assignment"
    __table_args__ = (
        UniqueConstraint(
            "master_sku",
            "channel_code",
            "connection_id",
            "taxonomy_kind",
            "remote_id",
            name="uq_product_channel_taxonomy_assignment",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    master_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    taxonomy_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="category")
    remote_id: Mapped[str] = mapped_column(String(255), nullable=False)
    remote_path: Mapped[Optional[str]] = mapped_column(Text)
    assignment_status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    match_source: Mapped[Optional[str]] = mapped_column(String(64))
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelListingPath(Base):
    """Master-owned PLP URL canonical (hub / facet / combo listing paths)."""

    __tablename__ = "channel_listing_path"
    __table_args__ = (UniqueConstraint("path_slug", name="uq_channel_listing_path_slug"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    path_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="facet")
    title: Mapped[str] = mapped_column(Text, nullable=False)
    parent_path_slug: Mapped[Optional[str]] = mapped_column(String(512), nullable=True, index=True)
    hub_path_slug: Mapped[Optional[str]] = mapped_column(String(512), nullable=True)
    facet_terms: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelListingPathTarget(Base):
    """Maps a PLP path to Magento category / Shopify collection per channel."""

    __tablename__ = "channel_listing_path_target"
    __table_args__ = (
        UniqueConstraint(
            "listing_path_id",
            "channel_code",
            "connection_id",
            "taxonomy_kind",
            "remote_id",
            name="uq_channel_listing_path_target",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    listing_path_id: Mapped[int] = mapped_column(ForeignKey("channel_listing_path.id"), nullable=False, index=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    taxonomy_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="category")
    remote_id: Mapped[str] = mapped_column(String(255), nullable=False)
    remote_path: Mapped[Optional[str]] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ListingIntersectionRule(Base):
    """Generic SEO intersection rule: base/group listing + one facet option."""

    __tablename__ = "listing_intersection_rule"
    __table_args__ = (
        UniqueConstraint("seo_path_slug", name="uq_listing_intersection_rule_seo_path_slug"),
        Index("ix_listing_intersection_rule_base_group", "base_path_slug", "group_path_slug"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    base_path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    group_path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    group_kind: Mapped[str] = mapped_column(String(64), nullable=False, default="collection")
    facet_attribute: Mapped[str] = mapped_column(String(128), nullable=False)
    facet_label: Mapped[str] = mapped_column(Text, nullable=False)
    facet_value: Mapped[str] = mapped_column(String(255), nullable=False)
    facet_slug: Mapped[str] = mapped_column(String(255), nullable=False)
    seo_path_slug: Mapped[str] = mapped_column(String(768), nullable=False, index=True)
    title: Mapped[str] = mapped_column(Text, nullable=False)
    listing_path_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("channel_listing_path.id"), nullable=True, index=True
    )
    target_listing_path_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("channel_listing_path.id"), nullable=True, index=True
    )
    filter_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    channel_payload: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_indexable: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ProductListingPathAssignment(Base):
    """Assign master SKUs to PLP listing paths (multi-path per SKU)."""

    __tablename__ = "product_listing_path_assignment"
    __table_args__ = (
        UniqueConstraint("master_sku", "listing_path_id", name="uq_product_listing_path_assignment"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    master_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    listing_path_id: Mapped[int] = mapped_column(ForeignKey("channel_listing_path.id"), nullable=False, index=True)
    assignment_status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    match_source: Mapped[Optional[str]] = mapped_column(String(64))
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class CollectionLandingPage(Base):
    """Presentation metadata for collection/category landing pages."""

    __tablename__ = "collection_landing_page"
    __table_args__ = (
        UniqueConstraint("path_slug", name="uq_collection_landing_page_path_slug"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    page_type: Mapped[str] = mapped_column(String(32), nullable=False, default="collection")
    category_l1: Mapped[Optional[str]] = mapped_column(Text)
    collection: Mapped[Optional[str]] = mapped_column(Text)
    collection_registry_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_collection_registry.id"), nullable=True, index=True
    )
    brand: Mapped[Optional[str]] = mapped_column(Text)
    title: Mapped[str] = mapped_column(Text, nullable=False)
    subtitle: Mapped[Optional[str]] = mapped_column(Text)
    short_description: Mapped[Optional[str]] = mapped_column(Text)
    description_html: Mapped[Optional[str]] = mapped_column(Text)
    specifications_html: Mapped[Optional[str]] = mapped_column(Text)
    materials_html: Mapped[Optional[str]] = mapped_column(Text)
    assembly_html: Mapped[Optional[str]] = mapped_column(Text)
    pdf_url: Mapped[Optional[str]] = mapped_column(Text)
    thumbnail_image_url: Mapped[Optional[str]] = mapped_column(Text)
    category_icon_url: Mapped[Optional[str]] = mapped_column(Text)
    hero_images: Mapped[Optional[dict]] = mapped_column(JSONB)
    badges: Mapped[Optional[dict]] = mapped_column(JSONB)
    feature_bullets: Mapped[Optional[dict]] = mapped_column(JSONB)
    cta_rows: Mapped[Optional[dict]] = mapped_column(JSONB)
    matching_collection_slugs: Mapped[Optional[dict]] = mapped_column(JSONB)
    sku_prefixes: Mapped[Optional[dict]] = mapped_column(JSONB)
    start_shopping_config: Mapped[Optional[dict]] = mapped_column(JSONB)
    parent_path_slug: Mapped[Optional[str]] = mapped_column(String(512), index=True)
    starting_price: Mapped[Optional[float]] = mapped_column(Numeric(12, 4))
    compare_at_price: Mapped[Optional[float]] = mapped_column(Numeric(12, 4))
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class CollectionSkuOverride(Base):
    """Manual include/exclude overrides for collection ↔ SKU membership."""

    __tablename__ = "collection_sku_override"
    __table_args__ = (
        UniqueConstraint("path_slug", "master_sku", name="uq_collection_sku_override_path_sku"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    master_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    action: Mapped[str] = mapped_column(String(16), nullable=False)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class MasterProductCollectionMembership(Base):
    """Explicit shared-SKU membership for additional collection pages."""

    __tablename__ = "master_product_collection_membership"
    __table_args__ = (
        UniqueConstraint("master_sku", "path_slug", name="uq_master_product_collection_membership_sku_path"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    master_sku: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    path_slug: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    collection_registry_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("master_collection_registry.id"), nullable=True, index=True
    )
    collection_name: Mapped[Optional[str]] = mapped_column(Text)
    source_label: Mapped[Optional[str]] = mapped_column(String(128))
    notes: Mapped[Optional[str]] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ChannelTaxonomyMapping(Base):
    """Master category/collection path → channel taxonomy (category id, collection gid, etc.)."""

    __tablename__ = "channel_taxonomy_mapping"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    channel_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    connection_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    master_category_l1: Mapped[Optional[str]] = mapped_column(Text)
    master_category_l2: Mapped[Optional[str]] = mapped_column(Text)
    master_category_l3: Mapped[Optional[str]] = mapped_column(Text)
    master_collection: Mapped[Optional[str]] = mapped_column(Text)
    master_path_key: Mapped[str] = mapped_column(String(512), nullable=False, index=True)
    channel_taxonomy_kind: Mapped[str] = mapped_column(String(32), nullable=False, default="collection")
    channel_remote_id: Mapped[Optional[str]] = mapped_column(String(255))
    channel_handle_or_path: Mapped[Optional[str]] = mapped_column(Text)
    transform_rule: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)


class ShopifyAttributeRegistry(Base):
    """Shopify field/metafield/option metadata pulled directly from Admin GraphQL."""

    __tablename__ = "shopify_attribute_registry"
    __table_args__ = (
        UniqueConstraint("shop_code", "owner_type", "attribute_code", name="uq_shopify_attr_registry_shop_owner_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    shop_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    owner_type: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    attribute_code: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    name: Mapped[Optional[str]] = mapped_column(Text)
    namespace: Mapped[Optional[str]] = mapped_column(String(255))
    key: Mapped[Optional[str]] = mapped_column(String(255))
    data_type: Mapped[Optional[str]] = mapped_column(String(128))
    is_standard: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    raw_json: Mapped[Optional[dict]] = mapped_column(JSONB)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoAttributeSetRegistry(Base):
    """Magento attribute sets from baseline sync."""

    __tablename__ = "magento_attribute_set_registry"
    __table_args__ = (
        UniqueConstraint("connection_id", "attribute_set_id", name="uq_attr_set_registry_conn_setid"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    attribute_set_id: Mapped[int] = mapped_column(Integer, nullable=False)
    attribute_set_name: Mapped[str] = mapped_column(Text, nullable=False)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoAttributeSetAttributes(Base):
    """Attributes assigned to an attribute set (set_id -> attribute_code, attribute_id)."""

    __tablename__ = "magento_attribute_set_attributes"
    __table_args__ = (
        UniqueConstraint("connection_id", "attribute_set_id", "attribute_code", name="uq_attr_set_attrs_conn_set_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    attribute_set_id: Mapped[int] = mapped_column(Integer, nullable=False)
    attribute_id: Mapped[int] = mapped_column(Integer, nullable=False)
    attribute_code: Mapped[str] = mapped_column(Text, nullable=False)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoSourceRegistry(Base):
    """MSI sources (warehouses) from baseline sync. GET /V1/inventory/sources."""

    __tablename__ = "magento_source_registry"
    __table_args__ = (
        UniqueConstraint("connection_id", "source_code", name="uq_magento_source_registry_conn_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    source_code: Mapped[str] = mapped_column(String(64), nullable=False)
    name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    enabled: Mapped[Optional[bool]] = mapped_column(Boolean, nullable=True)
    country_id: Mapped[Optional[str]] = mapped_column(String(8), nullable=True)
    postcode: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoStockRegistry(Base):
    """MSI stocks (sales-channel aggregations) from baseline sync. GET /V1/inventory/stocks."""

    __tablename__ = "magento_stock_registry"
    __table_args__ = (
        UniqueConstraint("connection_id", "stock_id", name="uq_magento_stock_registry_conn_stock"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    stock_id: Mapped[int] = mapped_column(Integer, nullable=False)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    sales_channels_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoStockSourceLinks(Base):
    """Stock–source assignments from baseline sync. GET /V1/inventory/stock-source-links."""

    __tablename__ = "magento_stock_source_links"
    __table_args__ = (
        UniqueConstraint(
            "connection_id", "stock_id", "source_code",
            name="uq_magento_stock_source_links_conn_stock_src",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    stock_id: Mapped[int] = mapped_column(Integer, nullable=False)
    source_code: Mapped[str] = mapped_column(String(64), nullable=False)
    priority: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class MagentoBaselineSyncRun(Base):
    """Tracks baseline sync executions for gating product syncs."""

    __tablename__ = "magento_baseline_sync_runs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    finished_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    status: Mapped[str] = mapped_column(Text, nullable=False, default="running")
    error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    fetched_counts: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)


class MagentoDeletionReview(Base):
    """SKUs flagged for deletion from Magento after disappearing from Plytix feed."""

    __tablename__ = "magento_deletion_review"
    __table_args__ = (
        UniqueConstraint("connection_id", "sku", "ingest_run_id", name="uq_magento_deletion_review_conn_sku_run"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    product_type: Mapped[Optional[str]] = mapped_column(String(32))  # "simple" or "configurable"
    parent_sku: Mapped[Optional[str]] = mapped_column(String(128))   # set if child simple
    ingest_run_id: Mapped[int] = mapped_column(ForeignKey("ingest_run.id"), nullable=False)
    flagged_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    status: Mapped[str] = mapped_column(String(32), default="pending")  # pending / approved / rejected
    decided_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    executed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    execution_result: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)


class MagentoProductOverride(Base):
    __tablename__ = "magento_product_override"
    __table_args__ = (
        UniqueConstraint("connection_id", "sku", "field", name="uq_magento_product_override_conn_sku_field"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    connection_id: Mapped[int] = mapped_column(ForeignKey("magento_connections.id"), nullable=False)
    field: Mapped[str] = mapped_column(String(128), nullable=False)
    value: Mapped[str] = mapped_column(Text, nullable=False)
    magento_last_seen_updated_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_pushed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    sync_lock_reason: Mapped[Optional[str]] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)