"""
HPP CRUD operations.

Pure database-access layer — no business logic here.
All business logic lives in services.py.
"""

import logging
from datetime import datetime, timezone
from typing import List, Optional

from sqlalchemy import select, func
from sqlalchemy.orm import Session

from src.apps.hpp.models.hpp_session import HPPSession
from src.apps.hpp.models.hpp_otp_token import HPPOtpToken
from src.apps.hpp.models.hpp_retry_token import HPPRetryToken
from src.apps.payment_requests.models.payment_request_links import PaymentRequestLinks

logger = logging.getLogger(__name__)


# ─── HPP Sessions ─────────────────────────────────────────────────────────────

def get_hpp_session_by_id(db: Session, session_id: int) -> Optional[HPPSession]:
    """Fetch a single HPPSession by primary key."""
    stmt = select(HPPSession).where(
        HPPSession.id == session_id,
        HPPSession.deleted_at.is_(None),
    )
    return db.execute(stmt).scalar_one_or_none()


def create_hpp_session(
    db: Session,
    payment_request_id: int,
    token: str,
    ip_address: Optional[str] = None,
    user_agent: Optional[str] = None,
) -> HPPSession:
    """
    Insert a new HPPSession row.

    Does NOT commit — caller is responsible for committing.
    """
    session = HPPSession(
        payment_request_id=payment_request_id,
        token=token,
        ip_address=ip_address,
        user_agent=user_agent,
        initiated_at=datetime.now(timezone.utc),
    )
    db.add(session)
    db.flush()
    return session


def stamp_hpp_session_submitted(db: Session, session_id: int) -> Optional[HPPSession]:
    """
    Set submitted_at on an existing HPPSession.

    Does NOT commit — caller is responsible for committing.
    """
    stmt = select(HPPSession).where(HPPSession.id == session_id)
    hpp_session = db.execute(stmt).scalar_one_or_none()
    if hpp_session:
        hpp_session.submitted_at = datetime.now(timezone.utc)
        db.flush()
    return hpp_session


def update_hpp_session_geo(
    db: Session,
    session_id: int,
    geo_city: Optional[str],
    geo_region: Optional[str],
    geo_country: Optional[str],
    geo_isp: Optional[str],
) -> None:
    """
    Update geo fields on an HPPSession.  Called after async GeoIP lookup.

    Does NOT commit — caller is responsible for committing.
    """
    stmt = select(HPPSession).where(HPPSession.id == session_id)
    hpp_session = db.execute(stmt).scalar_one_or_none()
    if hpp_session:
        hpp_session.geo_city = geo_city
        hpp_session.geo_region = geo_region
        hpp_session.geo_country = geo_country
        hpp_session.geo_isp = geo_isp
        db.flush()


# ─── Payment Request Links ────────────────────────────────────────────────────

def get_link_by_token(db: Session, token: str) -> Optional[PaymentRequestLinks]:
    """
    Fetch a PaymentRequestLinks row by token.

    Does NOT filter by status/expiry — caller must validate those.
    """
    stmt = select(PaymentRequestLinks).where(PaymentRequestLinks.token == token)
    return db.execute(stmt).scalar_one_or_none()


def get_links_for_payment_request(
    db: Session, payment_request_id: int
) -> List[PaymentRequestLinks]:
    """
    List all payment links for a given payment request (ordered newest-first).
    """
    stmt = (
        select(PaymentRequestLinks)
        .where(PaymentRequestLinks.payment_request_id == payment_request_id)
        .order_by(PaymentRequestLinks.id.desc())
    )
    return list(db.execute(stmt).scalars().all())


def get_link_by_id(db: Session, link_id: int) -> Optional[PaymentRequestLinks]:
    """Fetch a single PaymentRequestLinks row by primary key."""
    stmt = select(PaymentRequestLinks).where(PaymentRequestLinks.id == link_id)
    return db.execute(stmt).scalar_one_or_none()


def mark_link_used(db: Session, link: PaymentRequestLinks) -> PaymentRequestLinks:
    """
    Set link.status = 'USED', link.used_at = now(), link.is_expired = True.

    Does NOT commit.
    """
    link.status = "USED"
    link.used_at = datetime.now(timezone.utc)
    link.is_expired = True
    db.flush()
    return link


def mark_link_revoked(db: Session, link: PaymentRequestLinks) -> PaymentRequestLinks:
    """
    Set link.status = 'REVOKED', link.revoked_at = now(), link.is_expired = True.

    Does NOT commit.
    """
    link.status = "REVOKED"
    link.revoked_at = datetime.now(timezone.utc)
    link.is_expired = True
    db.flush()
    return link


def revoke_all_pending_links(db: Session, payment_request_id: int) -> int:
    """
    Set all PENDING links for a given payment request to REVOKED.

    Called before creating a new link in resend_payment_link so that only one
    active link exists at a time.

    Does NOT commit — caller is responsible.

    Returns:
        Number of links revoked.
    """
    stmt = select(PaymentRequestLinks).where(
        PaymentRequestLinks.payment_request_id == payment_request_id,
        PaymentRequestLinks.status == "PENDING",
    )
    pending_links = list(db.execute(stmt).scalars().all())
    now = datetime.now(timezone.utc)
    for link in pending_links:
        link.status = "REVOKED"
        link.revoked_at = now
        link.is_expired = True
    if pending_links:
        db.flush()
    return len(pending_links)


def increment_link_click_count(db: Session, link: PaymentRequestLinks) -> None:
    """
    Increment click_count and update last_clicked_at.

    Does NOT commit.
    """
    link.click_count = (link.click_count or 0) + 1
    link.last_clicked_at = datetime.now(timezone.utc)
    db.flush()


# ─── OTP Tokens ───────────────────────────────────────────────────────────────

def get_send_count_for_payment_request(db: Session, payment_request_id: int) -> int:
    """
    Return the total number of OTP tokens ever created for a given payment request.

    Used to enforce the max-3-sends-per-payment-request limit.
    """
    stmt = select(func.count(HPPOtpToken.id)).where(
        HPPOtpToken.payment_request_id == payment_request_id
    )
    result = db.execute(stmt).scalar()
    return result or 0


def get_active_otp(db: Session, payment_request_id: int) -> Optional[HPPOtpToken]:
    """
    Return the most recently created, non-expired, non-used OTP for a payment request.
    """
    now = datetime.now(timezone.utc)
    stmt = (
        select(HPPOtpToken)
        .where(
            HPPOtpToken.payment_request_id == payment_request_id,
            HPPOtpToken.used_at.is_(None),
            HPPOtpToken.expires_at > now,
        )
        .order_by(HPPOtpToken.id.desc())
        .limit(1)
    )
    return db.execute(stmt).scalar_one_or_none()


def create_otp_token(
    db: Session,
    payment_request_id: int,
    hashed_otp: str,
    phone_last4: str,
    expires_at: datetime,
) -> HPPOtpToken:
    """
    Insert a new HPPOtpToken row.

    Does NOT commit — caller is responsible.
    """
    token = HPPOtpToken(
        payment_request_id=payment_request_id,
        hashed_otp=hashed_otp,
        phone_last4=phone_last4,
        expires_at=expires_at,
        send_count=1,
        attempt_count=0,
        is_verified=False,
    )
    db.add(token)
    db.flush()
    return token


def increment_otp_attempt(db: Session, otp_token: HPPOtpToken) -> HPPOtpToken:
    """
    Increment attempt_count on an OTP token after a failed verification.

    Does NOT commit.
    """
    otp_token.attempt_count = (otp_token.attempt_count or 0) + 1
    db.flush()
    return otp_token


def mark_otp_verified(db: Session, otp_token: HPPOtpToken) -> HPPOtpToken:
    """
    Mark an OTP token as verified.

    Does NOT commit.
    """
    otp_token.is_verified = True
    otp_token.used_at = datetime.now(timezone.utc)
    db.flush()
    return otp_token


# ─── Retry Tokens ─────────────────────────────────────────────────────────────

def get_retry_token(db: Session, token: str) -> Optional[HPPRetryToken]:
    """Fetch a retry token by its URL-safe string."""
    stmt = select(HPPRetryToken).where(HPPRetryToken.token == token)
    return db.execute(stmt).scalar_one_or_none()


def get_active_retry_tokens_for_transaction(
    db: Session, transaction_id: int
) -> List[HPPRetryToken]:
    """
    Return all active (non-used) retry tokens for a given failed transaction.

    Used to enforce the 3-attempt limit per transaction.
    """
    stmt = select(HPPRetryToken).where(
        HPPRetryToken.transaction_id == transaction_id,
        HPPRetryToken.used_at.is_(None),
    )
    return list(db.execute(stmt).scalars().all())


def create_retry_token(
    db: Session,
    transaction_id: int,
    payment_request_id: int,
    token: str,
    expires_at: datetime,
) -> HPPRetryToken:
    """
    Insert a new HPPRetryToken row.

    Does NOT commit — caller is responsible.
    """
    retry_token = HPPRetryToken(
        transaction_id=transaction_id,
        payment_request_id=payment_request_id,
        token=token,
        expires_at=expires_at,
        attempt_count=0,
    )
    db.add(retry_token)
    db.flush()
    return retry_token


def mark_retry_token_used(
    db: Session, retry_token: HPPRetryToken
) -> HPPRetryToken:
    """
    Stamp used_at on a retry token.

    Does NOT commit.
    """
    retry_token.used_at = datetime.now(timezone.utc)
    db.flush()
    return retry_token


def increment_retry_attempt(
    db: Session, retry_token: HPPRetryToken
) -> HPPRetryToken:
    """
    Increment attempt_count on a retry token after a failed attempt.

    Does NOT commit.
    """
    retry_token.attempt_count = (retry_token.attempt_count or 0) + 1
    db.flush()
    return retry_token