"""Imported billing-panel customers (Vision / Tajri), grouped by provider + POP.

Read-only mirror of the maXim billing panels. Rows are upserted by the import
script (backend/scripts/import_billing_customers.py), never created via the API.
Passwords are PPPoE credentials the panels themselves store in clear text;
this table mirrors them so staff can look a customer up in one place.
"""
from sqlalchemy import Index, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from app.db.base_class import Base, TimestampMixin

PROVIDER_TAJRI = "tajri"
PROVIDER_VISION = "vision"

PROVIDER_LABELS = {
    PROVIDER_TAJRI: "Tajri Communication",
    PROVIDER_VISION: "VISION Technologies Limited",
}


class BillingCustomer(Base, TimestampMixin):
    __tablename__ = "billing_customers"

    id: Mapped[int] = mapped_column(primary_key=True)
    # 'tajri' (admin.tajricommunication.com) or 'vision' (billing.visiontechnologiesltd.com)
    provider: Mapped[str] = mapped_column(String(20), nullable=False, index=True)
    pop: Mapped[str] = mapped_column(String(120), nullable=False, index=True)
    # Billing-panel customer id (the `cid` the panels use in detail links).
    external_id: Mapped[str] = mapped_column(String(30), nullable=False, index=True)

    cust_id: Mapped[str | None] = mapped_column(String(30), nullable=True)
    username: Mapped[str | None] = mapped_column(String(80), nullable=True, index=True)
    password: Mapped[str | None] = mapped_column(String(80), nullable=True)
    name: Mapped[str] = mapped_column(String(150), nullable=False, default="")
    package: Mapped[str | None] = mapped_column(String(40), nullable=True)
    balance: Mapped[str | None] = mapped_column(String(40), nullable=True)
    exp_date: Mapped[str | None] = mapped_column(String(60), nullable=True)
    last_logout: Mapped[str | None] = mapped_column(String(40), nullable=True)
    mobile: Mapped[str | None] = mapped_column(String(30), nullable=True, index=True)
    created: Mapped[str | None] = mapped_column(String(40), nullable=True)
    status: Mapped[str | None] = mapped_column(String(30), nullable=True, index=True)
    flat_level: Mapped[str | None] = mapped_column(String(120), nullable=True)
    house: Mapped[str | None] = mapped_column(String(120), nullable=True)
    road: Mapped[str | None] = mapped_column(String(120), nullable=True)
    area: Mapped[str | None] = mapped_column(String(120), nullable=True)
    ip: Mapped[str | None] = mapped_column(String(60), nullable=True)
    group: Mapped[str | None] = mapped_column(String(60), nullable=True)
    net_bill: Mapped[str | None] = mapped_column(String(40), nullable=True)
    vlan_box: Mapped[str | None] = mapped_column(String(80), nullable=True)
    nid: Mapped[str | None] = mapped_column(String(40), nullable=True)
    email: Mapped[str | None] = mapped_column(String(255), nullable=True)
    discount: Mapped[str | None] = mapped_column(String(40), nullable=True)
    bill_cycle: Mapped[str | None] = mapped_column(String(40), nullable=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)
    district: Mapped[str | None] = mapped_column(String(80), nullable=True)
    thana: Mapped[str | None] = mapped_column(String(80), nullable=True)

    __table_args__ = (
        UniqueConstraint("provider", "external_id", name="uq_billing_customers_provider_external"),
        Index("ix_billing_customers_provider_pop", "provider", "pop"),
    )