from __future__ import annotations

from datetime import datetime

from sqlalchemy import (
    BigInteger,
    Boolean,
    CheckConstraint,
    Computed,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    SmallInteger,
    String,
    Text,
    UniqueConstraint,
    text,
)
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.orm import Mapped, mapped_column

from db.base import Base


class V2Category(Base):
    __tablename__ = "v2_category"
    __table_args__ = (
        CheckConstraint(
            "node_type IN ('hub','category','collection')",
            name="v2_category_node_type_valid",
        ),
        Index(
            "uq_v2_category_no_duplicate_siblings",
            "dedupe_scope",
            "node_type",
            "normalized_name",
            unique=True,
            postgresql_where=text("merged_into_id IS NULL"),
        ),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    parent_id: Mapped[int | None] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"))
    level: Mapped[int | None] = mapped_column(Integer)
    node_type: Mapped[str] = mapped_column(String(32), nullable=False, server_default="category")
    created_by: Mapped[str | None] = mapped_column(Text)
    merged_into_id: Mapped[int | None] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"))
    normalized_name: Mapped[str] = mapped_column(
        Text,
        Computed(r"lower(regexp_replace(trim(name), '\s+', ' ', 'g'))", persisted=True),
        nullable=False,
    )
    dedupe_scope: Mapped[int] = mapped_column(
        BigInteger,
        Computed("COALESCE(parent_id, 0)", persisted=True),
        nullable=False,
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=text("now()"),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=text("now()"),
    )


class V2Attribute(Base):
    __tablename__ = "v2_attribute"
    __table_args__ = (
        UniqueConstraint("code", name="uq_v2_attribute_code"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(255), nullable=False)
    label: Mapped[str] = mapped_column(Text, nullable=False)
    data_type: Mapped[str] = mapped_column(String(64), nullable=False)
    is_variant_axis: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=text("false"))
    is_channel_required: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=text("true"))
    applies_to_product_types: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default=text("'[]'::jsonb"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2AttributeValue(Base):
    __tablename__ = "v2_attribute_value"
    __table_args__ = (
        UniqueConstraint("attribute_id", "value_code", name="uq_v2_attribute_value_attr_code"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    attribute_id: Mapped[int] = mapped_column(ForeignKey("v2_attribute.id", ondelete="CASCADE"), nullable=False)
    value_code: Mapped[str] = mapped_column(String(255), nullable=False)
    display_label: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2Product(Base):
    __tablename__ = "v2_product"
    __table_args__ = (
        CheckConstraint(
            "product_type IN ('simple','configurable','bundle')",
            name="v2_product_type_valid",
        ),
        CheckConstraint(
            "lifecycle_status IN ('draft','active','disabled','archived')",
            name="v2_product_lifecycle_status_valid",
        ),
        UniqueConstraint("sku", name="uq_v2_product_sku"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    sku: Mapped[str] = mapped_column(String(128), nullable=False)
    product_type: Mapped[str] = mapped_column(String(32), nullable=False)
    lifecycle_status: Mapped[str] = mapped_column(String(32), nullable=False, server_default="draft")
    title: Mapped[str] = mapped_column(Text, nullable=False)
    slug: Mapped[str | None] = mapped_column(Text)
    seo_title: Mapped[str | None] = mapped_column(Text)
    seo_description: Mapped[str | None] = mapped_column(Text)
    description: Mapped[str | None] = mapped_column(Text)
    primary_category_id: Mapped[int | None] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"))
    shopping_l1_category_id: Mapped[int | None] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"))
    shopping_l2_category_id: Mapped[int | None] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"))
    # Typed UI hints only; structural bundle/configurable facts belong in dedicated relation tables.
    capabilities: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default=text("'{}'::jsonb"))
    attributes: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default=text("'{}'::jsonb"))
    source: Mapped[str] = mapped_column(Text, nullable=False, server_default="manual")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2ProductCategory(Base):
    __tablename__ = "v2_product_category"

    product_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True)
    category_id: Mapped[int] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"), primary_key=True)


class V2ConfigurableLink(Base):
    __tablename__ = "v2_configurable_link"

    parent_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True)
    child_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True)
    axis_attributes: Mapped[dict] = mapped_column(JSONB, nullable=False)


class V2BundleComponent(Base):
    __tablename__ = "v2_bundle_component"

    bundle_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True)
    component_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="RESTRICT"), primary_key=True)
    qty: Mapped[float] = mapped_column(Numeric(12, 4), nullable=False, server_default=text("1"))
    is_required: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=text("true"))
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default=text("0"))


class V2Override(Base):
    __tablename__ = "v2_override"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), nullable=False)
    channel: Mapped[str | None] = mapped_column(Text)
    field_path: Mapped[str] = mapped_column(Text, nullable=False)
    value: Mapped[dict] = mapped_column(JSONB, nullable=False)
    created_by: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2ChannelAttributeMapping(Base):
    __tablename__ = "v2_channel_attribute_mapping"
    __table_args__ = (
        UniqueConstraint("channel", "canonical_attribute_id", name="uq_v2_channel_attribute_mapping"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    channel: Mapped[str] = mapped_column(String(64), nullable=False)
    canonical_attribute_id: Mapped[int] = mapped_column(ForeignKey("v2_attribute.id", ondelete="CASCADE"), nullable=False)
    channel_attribute_code: Mapped[str | None] = mapped_column(Text)
    channel_attribute_id: Mapped[str | None] = mapped_column(Text)
    sync_mode: Mapped[str | None] = mapped_column(String(64))
    is_optional: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=text("false"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2ChannelAttributeValueMapping(Base):
    __tablename__ = "v2_channel_attribute_value_mapping"
    __table_args__ = (
        UniqueConstraint("channel", "canonical_attribute_value_id", name="uq_v2_channel_attribute_value_mapping"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    channel: Mapped[str] = mapped_column(String(64), nullable=False)
    canonical_attribute_value_id: Mapped[int] = mapped_column(ForeignKey("v2_attribute_value.id", ondelete="CASCADE"), nullable=False)
    channel_option_id: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2MediaAsset(Base):
    __tablename__ = "v2_media_asset"
    __table_args__ = (
        UniqueConstraint("product_id", "r2_key", name="uq_v2_media_asset_product_r2_key"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), nullable=False)
    r2_key: Mapped[str] = mapped_column(Text, nullable=False)
    public_url: Mapped[str | None] = mapped_column(Text)
    sha256: Mapped[str] = mapped_column(String(128), nullable=False)
    mime_type: Mapped[str] = mapped_column(String(255), nullable=False)
    width: Mapped[int | None] = mapped_column(Integer)
    height: Mapped[int | None] = mapped_column(Integer)
    alt_text: Mapped[str | None] = mapped_column(Text)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default=text("0"))
    role_flags: Mapped[list] = mapped_column(JSONB, nullable=False, server_default=text("'[]'::jsonb"))
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=text("true"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2ProductCollection(Base):
    __tablename__ = "v2_product_collection"

    product_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True)
    collection_id: Mapped[int] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"), primary_key=True)
    is_primary: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=text("true"))


class V2ShoppingAttributeOptionMap(Base):
    __tablename__ = "v2_shopping_attribute_option_map"
    __table_args__ = (
        CheckConstraint(
            "shopping_attribute IN ('shopping_l1','shopping_l2','shopping_collection')",
            name="v2_shopping_attribute_option_map_attribute_valid",
        ),
        CheckConstraint(
            "status IN ('active','deprecated')",
            name="v2_shopping_attribute_option_map_status_valid",
        ),
        UniqueConstraint(
            "channel",
            "shopping_attribute",
            "category_id",
            name="uq_v2_shopping_attribute_option_map",
        ),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    channel: Mapped[str] = mapped_column(String(64), nullable=False)
    shopping_attribute: Mapped[str] = mapped_column(String(64), nullable=False)
    category_id: Mapped[int] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=False)
    channel_option_id: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, server_default="active")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2DuplicateReviewQueue(Base):
    __tablename__ = "v2_duplicate_review_queue"
    __table_args__ = (
        CheckConstraint(
            "status IN ('pending','confirmed_duplicate','not_duplicate')",
            name="v2_duplicate_review_queue_status_valid",
        ),
        UniqueConstraint("category_id_a", "category_id_b", name="uq_v2_duplicate_review_queue_pair"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    category_id_a: Mapped[int] = mapped_column(ForeignKey("v2_category.id", ondelete="CASCADE"), nullable=False)
    category_id_b: Mapped[int] = mapped_column(ForeignKey("v2_category.id", ondelete="CASCADE"), nullable=False)
    similarity_score: Mapped[float] = mapped_column(Float, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, server_default="pending")
    reviewed_by: Mapped[str | None] = mapped_column(Text)
    reviewed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2ShoppingCascadeJob(Base):
    __tablename__ = "v2_shopping_cascade_job"
    __table_args__ = (
        CheckConstraint(
            "operation IN ('rename','merge','reparent','deprecate')",
            name="v2_shopping_cascade_job_operation_valid",
        ),
        CheckConstraint(
            "status IN ('pending','running','completed','failed')",
            name="v2_shopping_cascade_job_status_valid",
        ),
    )

    id: Mapped[str] = mapped_column(UUID(as_uuid=False), primary_key=True, server_default=text("gen_random_uuid()"))
    operation: Mapped[str] = mapped_column(String(32), nullable=False)
    source_category_id: Mapped[int] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=False)
    target_category_id: Mapped[int | None] = mapped_column(ForeignKey("v2_category.id", ondelete="RESTRICT"))
    delete_downstream_option: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=text("false"))
    status: Mapped[str] = mapped_column(String(32), nullable=False, server_default="pending")
    batch_id: Mapped[str] = mapped_column(UUID(as_uuid=False), nullable=False, server_default=text("gen_random_uuid()"))
    affected_product_count: Mapped[int | None] = mapped_column(Integer)
    created_by: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class V2ChangeEvent(Base):
    __tablename__ = "v2_change_event"
    __table_args__ = (
        CheckConstraint(
            "domain IN ('product_data','media','relations')",
            name="v2_change_event_domain_valid",
        ),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), nullable=False)
    domain: Mapped[str] = mapped_column(String(32), nullable=False)
    change_type: Mapped[str] = mapped_column(String(64), nullable=False)
    payload: Mapped[dict] = mapped_column(JSONB, nullable=False)
    batch_id: Mapped[str | None] = mapped_column(UUID(as_uuid=False))
    triggered_by: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2SyncJob(Base):
    __tablename__ = "v2_sync_job"
    __table_args__ = (
        CheckConstraint(
            "priority_lane IN ('urgent','normal','bulk')",
            name="v2_sync_job_priority_lane_valid",
        ),
        CheckConstraint(
            "status IN ('pending','running','completed','failed','cancelled')",
            name="v2_sync_job_status_valid",
        ),
    )

    id: Mapped[str] = mapped_column(UUID(as_uuid=False), primary_key=True, server_default=text("gen_random_uuid()"))
    source: Mapped[str] = mapped_column(Text, nullable=False)
    channel: Mapped[str | None] = mapped_column(String(64))
    domain: Mapped[str | None] = mapped_column(String(32))
    batch_id: Mapped[str | None] = mapped_column(UUID(as_uuid=False))
    priority_lane: Mapped[str] = mapped_column(String(16), nullable=False, server_default="normal")
    status: Mapped[str] = mapped_column(String(32), nullable=False, server_default="pending")
    triggered_by: Mapped[str] = mapped_column(Text, nullable=False)
    item_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default=text("0"))
    success_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default=text("0"))
    failure_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default=text("0"))
    blocked_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default=text("0"))
    started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))
    job_metadata: Mapped[dict] = mapped_column("metadata", JSONB, nullable=False, server_default=text("'{}'::jsonb"))


class _V2QueueMixin:
    sync_job_id: Mapped[str | None] = mapped_column(ForeignKey("v2_sync_job.id", ondelete="SET NULL"))
    product_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), nullable=False)
    channel: Mapped[str] = mapped_column(String(64), nullable=False)
    priority: Mapped[int] = mapped_column(SmallInteger, nullable=False, server_default=text("5"))
    priority_lane: Mapped[str] = mapped_column(String(16), nullable=False, server_default="normal")
    status: Mapped[str] = mapped_column(String(32), nullable=False, server_default="pending")
    block_reason: Mapped[str | None] = mapped_column(Text)
    payload: Mapped[dict] = mapped_column(JSONB, nullable=False)
    attempt_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default=text("0"))
    max_attempts: Mapped[int] = mapped_column(Integer, nullable=False, server_default=text("5"))
    last_error: Mapped[str | None] = mapped_column(Text)
    last_response: Mapped[dict | None] = mapped_column(JSONB)
    locked_by: Mapped[str | None] = mapped_column(Text)
    locked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    coalesce_key: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))
    scheduled_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2QueueProductData(_V2QueueMixin, Base):
    __tablename__ = "v2_queue_product_data"
    __table_args__ = (
        CheckConstraint(
            "priority_lane IN ('urgent','normal','bulk')",
            name="v2_queue_product_data_priority_lane_valid",
        ),
        CheckConstraint(
            "status IN ('pending','processing','blocked','failed','dead_letter','done')",
            name="v2_queue_product_data_status_valid",
        ),
        Index(
            "uq_v2_queue_product_data_pending_coalesce",
            "coalesce_key",
            unique=True,
            postgresql_where=text("status = 'pending'"),
        ),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)


class V2QueueMedia(_V2QueueMixin, Base):
    __tablename__ = "v2_queue_media"
    __table_args__ = (
        CheckConstraint(
            "priority_lane IN ('urgent','normal','bulk')",
            name="v2_queue_media_priority_lane_valid",
        ),
        CheckConstraint(
            "status IN ('pending','processing','blocked','failed','dead_letter','done')",
            name="v2_queue_media_status_valid",
        ),
        Index(
            "uq_v2_queue_media_pending_coalesce",
            "coalesce_key",
            unique=True,
            postgresql_where=text("status = 'pending'"),
        ),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)


class V2QueueRelations(_V2QueueMixin, Base):
    __tablename__ = "v2_queue_relations"
    __table_args__ = (
        CheckConstraint(
            "priority_lane IN ('urgent','normal','bulk')",
            name="v2_queue_relations_priority_lane_valid",
        ),
        CheckConstraint(
            "status IN ('pending','processing','blocked','failed','dead_letter','done')",
            name="v2_queue_relations_status_valid",
        ),
        Index(
            "uq_v2_queue_relations_pending_coalesce",
            "coalesce_key",
            unique=True,
            postgresql_where=text("status = 'pending'"),
        ),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)


class V2ChannelPublishState(Base):
    __tablename__ = "v2_channel_publish_state"
    __table_args__ = (
        CheckConstraint(
            "domain IN ('product_data','media','relations')",
            name="v2_channel_publish_state_domain_valid",
        ),
        CheckConstraint(
            "status IN ('pending','processing','blocked','failed','verified')",
            name="v2_channel_publish_state_status_valid",
        ),
        CheckConstraint(
            "verification_mode IN ('full','sampled','escalated') OR verification_mode IS NULL",
            name="v2_channel_publish_state_verification_mode_valid",
        ),
    )

    product_id: Mapped[int] = mapped_column(ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True)
    channel: Mapped[str] = mapped_column(String(64), primary_key=True)
    domain: Mapped[str] = mapped_column(String(32), primary_key=True)
    status: Mapped[str] = mapped_column(String(32), nullable=False, server_default="pending")
    block_reason: Mapped[str | None] = mapped_column(Text)
    channel_ref_id: Mapped[str | None] = mapped_column(Text)
    last_event_id: Mapped[int | None] = mapped_column(ForeignKey("v2_change_event.id", ondelete="SET NULL"))
    last_queue_item_id: Mapped[int | None] = mapped_column(BigInteger)
    last_pushed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    last_verified_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    verification_mode: Mapped[str | None] = mapped_column(String(32))
    verification_result: Mapped[dict | None] = mapped_column(JSONB)
    last_error: Mapped[str | None] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2StagingBatch(Base):
    __tablename__ = "v2_staging_batch"
    __table_args__ = (
        CheckConstraint(
            "status IN ('processing','needs_review','promotable','completed')",
            name="v2_staging_batch_status_valid",
        ),
    )

    id: Mapped[str] = mapped_column(UUID(as_uuid=False), primary_key=True, server_default=text("gen_random_uuid()"))
    filename: Mapped[str] = mapped_column(Text, nullable=False)
    uploaded_by: Mapped[str] = mapped_column(Text, nullable=False)
    row_count: Mapped[int] = mapped_column(Integer, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, server_default="processing")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))


class V2StagingRow(Base):
    __tablename__ = "v2_staging_row"
    __table_args__ = (
        CheckConstraint(
            "status IN ('pending','blocked','resolved','promoted','skipped')",
            name="v2_staging_row_status_valid",
        ),
        UniqueConstraint("batch_id", "row_number", name="uq_v2_staging_row_batch_row_number"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    batch_id: Mapped[str] = mapped_column(ForeignKey("v2_staging_batch.id", ondelete="CASCADE"), nullable=False)
    row_number: Mapped[int] = mapped_column(Integer, nullable=False)
    raw_data: Mapped[dict] = mapped_column(JSONB, nullable=False)
    normalized_data: Mapped[dict | None] = mapped_column(JSONB)
    resolved_data: Mapped[dict | None] = mapped_column(JSONB)
    status: Mapped[str] = mapped_column(String(32), nullable=False, server_default="pending")
    blocking_fields: Mapped[list | None] = mapped_column(JSONB)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=text("now()"))