"""CC expense pass-through tables (02 §3) — carried near-verbatim.

Statement paste -> parse -> assign to project -> auto-rules -> keyword
highlights -> billed onward. Lines gain invoice_line_id (same lock as
time entries) — NULL everywhere until the new invoicing feature sets it.
A batch is one card, one statement, one currency (02 §3).

Rebuild delta #8 applied: (batch_id, line_index) is now unique.
"""

import uuid
from datetime import date, datetime

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

from app.models.base import Base


class CcExpenseBatch(Base):
    __tablename__ = "cc_expense_batches"
    __table_args__ = (Index("ix_cc_expense_batches_tenant", "tenant_id"),)

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"))
    name: Mapped[str] = mapped_column(Text())
    currency: Mapped[str] = mapped_column(CHAR(3), default="CAD")
    created_by: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL")
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class CcExpenseLine(Base):
    __tablename__ = "cc_expense_lines"
    __table_args__ = (
        UniqueConstraint("batch_id", "line_index", name="uq_cc_expense_lines_batch_index"),
        Index(
            "ix_cc_expense_lines_project",
            "project_id",
            postgresql_where=text("project_id IS NOT NULL"),
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)
    batch_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("cc_expense_batches.id", ondelete="CASCADE")
    )
    line_index: Mapped[int] = mapped_column(Integer())  # paste order
    raw_line: Mapped[str] = mapped_column(Text())  # original line, audit/re-parse
    expense_date: Mapped[date | None] = mapped_column()  # NULL = parser failed
    description: Mapped[str | None] = mapped_column(Text())
    amount: Mapped[float | None] = mapped_column(Numeric(12, 2))
    balance: Mapped[float | None] = mapped_column(Numeric(12, 2))
    project_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("projects.id", ondelete="SET NULL")
    )
    assignment_description: Mapped[str | None] = mapped_column(Text())
    category: Mapped[str | None] = mapped_column(Text())
    # TRUE = filled by an auto-rule, not yet user-confirmed (renders orange)
    auto_assigned: Mapped[bool] = mapped_column(Boolean(), default=False)
    # same lock as time entries; starts NULL everywhere (05 step 2.8)
    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()
    )


class CcHighlightKeyword(Base):
    __tablename__ = "cc_highlight_keywords"
    __table_args__ = (
        Index(
            "uq_cc_highlight_keywords_tenant_kw",
            "tenant_id",
            text("lower(keyword)"),
            unique=True,
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"))
    # substring match against line descriptions; purely visual
    keyword: Mapped[str] = mapped_column(Text())
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class CcAutoRule(Base):
    __tablename__ = "cc_auto_rules"
    __table_args__ = (
        Index(
            "uq_cc_auto_rules_tenant_kw",
            "tenant_id",
            text("lower(keyword)"),
            unique=True,
        ),
        Index("ix_cc_auto_rules_project", "project_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"))
    keyword: Mapped[str] = mapped_column(Text())
    # deleting a project cascades to its rules (parity)
    project_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE")
    )
    assignment_description: Mapped[str | None] = mapped_column(Text())
    category: Mapped[str | None] = mapped_column(Text())
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )