from datetime import date, datetime, time
from decimal import Decimal

from sqlalchemy import Date, DateTime, ForeignKey, Integer, Numeric, String, Time, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.models.base import Base


class PosUploadBatch(Base):
    __tablename__ = "pos_upload_batch"

    id: Mapped[int] = mapped_column(primary_key=True)
    spbu_id: Mapped[int] = mapped_column(
        ForeignKey("master_spbu.id", ondelete="CASCADE"), index=True
    )
    produk_id: Mapped[int] = mapped_column(
        ForeignKey("master_produk.id", ondelete="RESTRICT"), index=True
    )
    uploaded_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("master_user.id", ondelete="SET NULL"), nullable=True, index=True
    )
    tanggal_mulai: Mapped[date] = mapped_column(Date, nullable=False)
    tanggal_selesai: Mapped[date] = mapped_column(Date, nullable=False)
    total_rows: Mapped[int] = mapped_column(Integer, default=0)
    total_volume: Mapped[Decimal] = mapped_column(Numeric(15, 3), default=Decimal("0.000"))
    total_nilai: Mapped[Decimal] = mapped_column(Numeric(15, 2), default=Decimal("0.00"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)

    # Relationships
    transaksi: Mapped[list["PosTransaksiPertamina"]] = relationship(
        back_populates="batch", cascade="all, delete-orphan"
    )

    __table_args__ = (
        UniqueConstraint("spbu_id", "produk_id", "tanggal_mulai", "tanggal_selesai"),
    )


class PosTransaksiPertamina(Base):
    __tablename__ = "pos_transaksi_pertamina"

    id: Mapped[int] = mapped_column(primary_key=True)
    spbu_id: Mapped[int] = mapped_column(
        ForeignKey("master_spbu.id", ondelete="CASCADE"), index=True
    )
    batch_id: Mapped[int] = mapped_column(
        ForeignKey("pos_upload_batch.id", ondelete="CASCADE"), index=True
    )
    shift_id: Mapped[int | None] = mapped_column(
        ForeignKey("master_spbu_shift.id", ondelete="SET NULL"), nullable=True, index=True
    )
    tanggal: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    jam: Mapped[time] = mapped_column(Time, nullable=False)
    code_mor: Mapped[str | None] = mapped_column(String(50), nullable=True)
    province: Mapped[str | None] = mapped_column(String(100), nullable=True)
    city: Mapped[str | None] = mapped_column(String(100), nullable=True)
    no_spbu_pertamina: Mapped[str | None] = mapped_column(String(50), nullable=True)
    no_nozzle: Mapped[str | None] = mapped_column(String(50), nullable=True)
    no_dispenser: Mapped[str] = mapped_column(String(50), nullable=False)
    produk_raw: Mapped[str] = mapped_column(String(100), nullable=False)
    volume: Mapped[Decimal | None] = mapped_column(Numeric(15, 3), nullable=True)
    nilai: Mapped[Decimal | None] = mapped_column(Numeric(15, 2), nullable=True)
    operator_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    no_hp: Mapped[str | None] = mapped_column(String(20), nullable=True)
    delivery_type: Mapped[str | None] = mapped_column(String(50), nullable=True)
    plat_nomor: Mapped[str | None] = mapped_column(String(20), nullable=True, index=True)
    nik: Mapped[str | None] = mapped_column(String(20), nullable=True)
    instansi: Mapped[str | None] = mapped_column(String(255), nullable=True)
    timestamp: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    island_raw: Mapped[str | None] = mapped_column(String(100), nullable=True)
    nozzle_raw: Mapped[str | None] = mapped_column(String(100), nullable=True)

    # Relationships
    batch: Mapped["PosUploadBatch"] = relationship(back_populates="transaksi")

    __table_args__ = (
        UniqueConstraint("spbu_id", "timestamp", "no_dispenser"),
    )