"""import_batches + time_entries — the heart of the app (02 §3).

time_entries carries party-based attribution (worker_party_id +
via_engagement_id), the immutable raw_* import payload (the as-imported
operator/client/project text triple — provenance on resolved rows, the
ONLY identity for unassigned/Blocked rows), and locked-at-write-time
money stamps with per-side currency (cost_currency / billout_currency —
the two billing hops can legitimately differ, 07 #21).

start_at/end_at are DELIBERATELY timezone-naive (07 #17): source
wall-clock preserved exactly; source_timezone records where it was
recorded (stamped at import where known, used never until R2+).

invoice_line_id is THE lock (ADR #038 semantics carried): attached to an
invoice ⇒ immutable.
"""

import uuid
from datetime import datetime

from sqlalchemy import (
    CHAR,
    Boolean,
    CheckConstraint,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    Text,
    Uuid,
    func,
    text,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base

#: The CHECK admits the historical values verbatim (toggl/backfill rows
#: migrate with provenance intact); the importer only ever WRITES
#: clockify | manual (02 §3).
ENTRY_SOURCES = ("clockify", "toggl", "manual", "backfill")


class ImportBatch(Base):
    __tablename__ = "import_batches"
    __table_args__ = (
        CheckConstraint(
            "source IS NULL OR source IN ('clockify', 'toggl', 'manual', 'backfill')",
            name="ck_import_batches_source",
        ),
        Index("ix_import_batches_tenant_date", "tenant_id", "imported_at"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"))
    source: Mapped[str | None] = mapped_column(Text())
    filename: Mapped[str | None] = mapped_column(Text())
    name: Mapped[str | None] = mapped_column(Text())
    notes: Mapped[str | None] = mapped_column(Text())
    row_count: Mapped[int] = mapped_column(Integer(), default=0)
    # load-bearing: importer-scoped visibility + batch-list ordering (05 §2.5)
    imported_by: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL")
    )
    imported_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class TimeEntry(Base):
    __tablename__ = "time_entries"
    __table_args__ = (
        CheckConstraint(
            "source IN ('clockify', 'toggl', 'manual', 'backfill')",
            name="ck_time_entries_source",
        ),
        # a money stamp is an (amount, currency) pair — never one without
        # the other (02 §3: the currency is part of the stamp)
        CheckConstraint(
            "(cost_amount IS NULL) = (cost_currency IS NULL)",
            name="ck_time_entries_cost_currency_pair",
        ),
        CheckConstraint(
            "(billout_amount IS NULL) = (billout_currency IS NULL)",
            name="ck_time_entries_billout_currency_pair",
        ),
        Index("ix_time_entries_tenant_start", "tenant_id", "start_at"),
        Index(
            "ix_time_entries_tenant_project_start", "tenant_id", "project_id", "start_at"
        ),
        Index("ix_time_entries_tenant_source_email", "tenant_id", "source_user_email"),
        Index("ix_time_entries_project", "project_id"),
        Index("ix_time_entries_worker_party", "worker_party_id"),
        Index("ix_time_entries_import", "import_id"),
        Index(
            "ix_time_entries_invoice_line",
            "invoice_line_id",
            postgresql_where=text("invoice_line_id IS NOT NULL"),
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"))
    # NULL = unassigned -> "Blocked" status, same semantics as today
    project_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("projects.id", ondelete="SET NULL")
    )
    # who did the work (person party; replaces team_member_id)
    worker_party_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("parties.id", ondelete="SET NULL")
    )
    # the engagement the work flows through; nullable for tenant-internal work
    via_engagement_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("engagements.id", ondelete="SET NULL")
    )
    source: Mapped[str] = mapped_column(Text())
    # tz the wall-clock was recorded in, where known (07 #17 amendment)
    source_timezone: Mapped[str | None] = mapped_column(Text())
    import_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("import_batches.id", ondelete="SET NULL")
    )
    source_user_name: Mapped[str | None] = mapped_column(Text())
    source_user_email: Mapped[str | None] = mapped_column(Text())
    # the immutable as-imported text triple (raw_* import payload).
    # Display/grouping/joins for resolved rows use FKs, full stop.
    raw_operator: Mapped[str | None] = mapped_column(Text())
    raw_client: Mapped[str | None] = mapped_column(Text())
    raw_project: Mapped[str | None] = mapped_column(Text())
    description: Mapped[str | None] = mapped_column(Text())
    billable: Mapped[bool | None] = mapped_column(Boolean())
    # timezone-NAIVE by design (07 #17) — source wall-clock preserved
    start_at: Mapped[datetime] = mapped_column(DateTime(timezone=False))
    end_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=False))
    duration_seconds: Mapped[int | None] = mapped_column(Integer())
    # raw import provenance — carried at source precision (4dp)
    source_rate: Mapped[float | None] = mapped_column(Numeric(12, 4))
    source_amount: Mapped[float | None] = mapped_column(Numeric(12, 4))
    # locked-at-write-time stamps (ADR #014 invariant carries verbatim):
    # stamped from compensation_terms at write; never silently rewritten.
    # NULL = never priced; 0.00 = priced at zero (judgment #19).
    cost_amount: Mapped[float | None] = mapped_column(Numeric(12, 2))
    cost_currency: Mapped[str | None] = mapped_column(CHAR(3))
    billout_amount: Mapped[float | None] = mapped_column(Numeric(12, 2))
    billout_currency: Mapped[str | None] = mapped_column(CHAR(3))
    # THE lock: attached to an invoice => immutable (ADR #038 semantics)
    invoice_line_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("invoice_lines.id", ondelete="SET NULL")
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )