"""
HPPOtpToken model — SMS OTP records for HPP authorization (HWHPP-304).

A new row is inserted each time the customer requests an OTP send.
The PRD allows max 3 sends per payment request and max 5 wrong verify
attempts before the OTP is locked out — both limits are enforced in
services.py by querying send_count / attempt_count on the latest token.

Token tables intentionally omit deleted_at (they are never soft-deleted;
expired/used tokens are kept for audit purposes).
"""

from datetime import datetime
from typing import Optional, TYPE_CHECKING

from sqlalchemy import Boolean, ForeignKey, Index, Integer, String, DateTime
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql import func

from src.apps.base.models.base import Base

if TYPE_CHECKING:
    from src.apps.payment_requests.models.payment_request import PaymentRequest


class HPPOtpToken(Base):
    """
    HPPOtpToken Model: ORM class for the hpp_otp_tokens table.

    Stores one row per OTP send attempt. The hashed_otp field holds a
    bcrypt hash of the 6-digit code (same library as user passwords).
    """

    __tablename__ = "hpp_otp_tokens"

    __table_args__ = (
        # Primary lookup: active OTP for a given payment request.
        Index("ix_hpp_otp_tokens_payment_request_id", "payment_request_id"),
        # Composite index for the "find active, non-expired, non-used" query pattern
        # used in otp/verify and otp/send endpoints:
        #   WHERE payment_request_id = :id
        #     AND used_at IS NULL
        #     AND expires_at > now()
        Index(
            "ix_hpp_otp_tokens_active_lookup",
            "payment_request_id",
            "used_at",
            "expires_at",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True, autoincrement=True)

    payment_request_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("payment_requests.id"), nullable=False
    )

    # bcrypt hash of the 6-digit OTP.  Never store plain-text OTP.
    hashed_otp: Mapped[str] = mapped_column(String(255), nullable=False)

    # Last 4 digits of the phone number the OTP was sent to, kept for
    # display in audit logs and the verified_phone_last4 auth field.
    phone_last4: Mapped[str] = mapped_column(String(4), nullable=False)

    # OTP expiry — set to now() + 10 minutes at creation time (service layer).
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)

    # Stamped when the OTP is successfully verified.
    used_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)

    # Number of times the customer has clicked "Send Code" for this token row.
    # Note: each re-send creates a NEW token row, so this field tracks how many
    # times a single code was delivered (retransmit of same OTP, e.g. on SMS
    # delivery failure — currently always 1; incremented only if the same hash
    # is resent without regenerating).  The per-payment-request send limit (3)
    # is enforced by counting rows in services.py.
    send_count: Mapped[int] = mapped_column(Integer, default=1, nullable=False)

    # Number of incorrect verify attempts against this specific OTP row.
    # Locked out at 5 (enforced in services.py).
    attempt_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)

    # Set true when the customer successfully enters the correct OTP.
    is_verified: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )

    # Relationships
    payment_request: Mapped["PaymentRequest"] = relationship(
        "PaymentRequest", back_populates="otp_tokens"
    )