#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""外部市場來源與報價的正規化 ORM models。"""

from datetime import datetime, timedelta, timezone

from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import relationship

from database.models import Base


TAIPEI_TZ = timezone(timedelta(hours=8))


def taipei_now():
    return datetime.now(TAIPEI_TZ).replace(tzinfo=None)


class ExternalMarketSource(Base):
    """外部市場資料來源，例如 MOMO 參考價、蝦皮 API、酷澎 CSV。"""

    __tablename__ = "external_market_sources"

    id = Column(Integer, primary_key=True, autoincrement=True)
    code = Column(String(80), unique=True, nullable=False, index=True)
    display_name = Column(String(160), nullable=False)
    platform_code = Column(String(80), nullable=False, index=True)
    source_kind = Column(String(60), nullable=False, index=True)
    status = Column(String(40), default="paused", nullable=False, index=True)
    enabled = Column(Boolean, default=False, nullable=False)
    write_enabled = Column(Boolean, default=False, nullable=False)
    allowed_input_methods_json = Column(Text)
    quality_policy_json = Column(Text)
    plain_note = Column(Text)
    created_at = Column(DateTime, default=taipei_now, nullable=False)
    updated_at = Column(DateTime, default=taipei_now, onupdate=taipei_now, nullable=False)

    offers = relationship("ExternalOffer", back_populates="source")

    __table_args__ = (
        Index("idx_external_market_sources_status", "status", "enabled"),
    )


class ExternalOffer(Base):
    """正規化後的外部商品報價。"""

    __tablename__ = "external_offers"

    id = Column(Integer, primary_key=True, autoincrement=True)
    source_code = Column(String(80), ForeignKey("external_market_sources.code"), nullable=False, index=True)
    platform_code = Column(String(80), nullable=False, index=True)
    source_product_id = Column(String(220), nullable=False, index=True)
    source_offer_key = Column(String(260), nullable=False)
    title = Column(Text, nullable=False)
    brand = Column(String(180), index=True)
    category_text = Column(String(320), index=True)
    product_url = Column(Text)
    image_url = Column(Text)
    price = Column(Float)
    original_price = Column(Float)
    currency = Column(String(12), default="TWD", nullable=False)
    stock_status = Column(String(80), index=True)
    sold_count = Column(Integer)
    rating = Column(Float)
    review_count = Column(Integer)
    observed_at = Column(DateTime, default=taipei_now, nullable=False, index=True)
    expires_at = Column(DateTime, index=True)
    ingestion_method = Column(String(60), nullable=False, index=True)
    connector_key = Column(String(120), index=True)
    pchome_product_id = Column(String(120), index=True)
    momo_sku = Column(String(80), index=True)
    match_status = Column(String(40), default="unmatched", nullable=False, index=True)
    quality_score = Column(Float, default=0.0, nullable=False)
    data_quality_status = Column(String(40), default="needs_review", nullable=False, index=True)
    quality_notes_json = Column(Text)
    raw_payload_json = Column(Text)
    created_at = Column(DateTime, default=taipei_now, nullable=False)
    updated_at = Column(DateTime, default=taipei_now, onupdate=taipei_now, nullable=False)

    source = relationship("ExternalMarketSource", back_populates="offers")

    __table_args__ = (
        UniqueConstraint(
            "source_code",
            "source_product_id",
            "observed_at",
            "ingestion_method",
            name="uq_external_offer_source_product_observed",
        ),
        Index("idx_external_offers_source_seen", "source_code", "observed_at"),
        Index("idx_external_offers_platform_product", "platform_code", "source_product_id"),
        Index("idx_external_offers_pchome_product", "pchome_product_id", "source_code"),
        Index("idx_external_offers_match_quality", "match_status", "data_quality_status", "quality_score"),
    )