"""Customers and their live service connections."""
from datetime import date

from sqlalchemy import Date, ForeignKey, Index, Numeric, String, Text, text
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db.base_class import Base, SoftDeleteMixin, TimestampMixin
from app.models.enums import ConnectionStatus, pg_enum


class Customer(Base, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "customers"

    id: Mapped[int] = mapped_column(primary_key=True)
    customer_code: Mapped[str] = mapped_column(String(30), nullable=False, index=True)
    full_name: Mapped[str] = mapped_column(String(150), nullable=False)
    phone: Mapped[str] = mapped_column(String(20), nullable=False, index=True)
    alt_phone: Mapped[str | None] = mapped_column(String(20), nullable=True)
    nid: Mapped[str | None] = mapped_column(String(30), nullable=True, index=True)
    email: Mapped[str | None] = mapped_column(String(255), nullable=True)
    address: Mapped[str] = mapped_column(Text, nullable=False)
    area_id: Mapped[int] = mapped_column(
        ForeignKey("areas.id", ondelete="RESTRICT"), nullable=False, index=True
    )

    area = relationship("Area", lazy="joined")
    connections: Mapped[list["Connection"]] = relationship(
        back_populates="customer", cascade="all, delete-orphan"
    )

    __table_args__ = (
        Index(
            "uq_customers_customer_code_active",
            "customer_code",
            unique=True,
            postgresql_where=text("is_deleted = false"),
        ),
        Index("ix_customers_full_name", "full_name"),
    )


class Connection(Base, TimestampMixin, SoftDeleteMixin):
    """An installed service line. Created when an application reaches 'installed'."""

    __tablename__ = "connections"

    id: Mapped[int] = mapped_column(primary_key=True)
    connection_code: Mapped[str] = mapped_column(String(30), nullable=False, index=True)
    customer_id: Mapped[int] = mapped_column(
        ForeignKey("customers.id", ondelete="RESTRICT"), nullable=False, index=True
    )
    application_id: Mapped[int | None] = mapped_column(
        ForeignKey("connection_applications.id", ondelete="SET NULL"), nullable=True
    )
    area_id: Mapped[int] = mapped_column(
        ForeignKey("areas.id", ondelete="RESTRICT"), nullable=False, index=True
    )
    status: Mapped[ConnectionStatus] = mapped_column(
        pg_enum(ConnectionStatus, "connection_status"),
        nullable=False,
        default=ConnectionStatus.ACTIVE,
        server_default=ConnectionStatus.ACTIVE.value,
        index=True,
    )
    activated_on: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    terminated_on: Mapped[date | None] = mapped_column(Date, nullable=True)
    monthly_bill: Mapped[float | None] = mapped_column(Numeric(10, 2), nullable=True)
    installed_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    __table_args__ = (
        Index(
            "uq_connections_connection_code_active",
            "connection_code",
            unique=True,
            postgresql_where=text("is_deleted = false"),
        ),
        # One live connection per application; a soft-deleted one releases the link.
        Index(
            "uq_connections_application_id_active",
            "application_id",
            unique=True,
            postgresql_where=text("is_deleted = false AND application_id IS NOT NULL"),
        ),
    )

    customer: Mapped["Customer"] = relationship(back_populates="connections", lazy="joined")
    area = relationship("Area", lazy="joined")
    application = relationship("ConnectionApplication", back_populates="connection", lazy="selectin")
    complaints = relationship("Complaint", back_populates="connection")