"""Prevent duplicate customer rows (walk-in, name/email/phone per branch)."""
from __future__ import annotations

from typing import Optional

from sqlalchemy import func, or_, text
from sqlalchemy.orm import Session

from app.constants.walkin_customer import WALKIN_CUSTOMER_ID
from app.models.branch import Branch
from app.models.sales import Customer

RESERVED_WALKIN_NAMES = frozenset(
    {
        "walk-in customer",
        "walkin customer",
        "walk in customer",
    }
)


def normalize_customer_name(name: Optional[str]) -> str:
    return " ".join((name or "").strip().lower().split())


def is_reserved_walkin_name(name: Optional[str]) -> bool:
    return normalize_customer_name(name) in RESERVED_WALKIN_NAMES


def _company_branch_filter(query, db: Session, company_id: Optional[str]):
    if not company_id:
        return query
    branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
    return query.filter(
        or_(Customer.branch_id.in_(branch_ids_sq), Customer.branch_id.is_(None))
    )


def assert_walkin_singleton(
    db: Session,
    *,
    customer_type: Optional[str],
    name: Optional[str],
    exclude_id: Optional[str] = None,
) -> None:
    """Only the canonical system walk-in may exist."""
    if exclude_id == WALKIN_CUSTOMER_ID:
        return

    ctype = (customer_type or "").strip().lower()
    if ctype == "walk_in":
        raise ValueError(
            "Walk-in customers are created automatically by the system and cannot be added manually."
        )

    if is_reserved_walkin_name(name):
        raise ValueError(
            'The name "Walk-in Customer" is reserved for the system POS account.'
        )

    existing_walkin = (
        db.query(Customer.id)
        .filter(Customer.customer_type == "walk_in", Customer.id != WALKIN_CUSTOMER_ID)
        .first()
    )
    if existing_walkin:
        raise ValueError(
            "A walk-in customer record already exists. Use the system Walk-in Customer only."
        )


def find_active_duplicate(
    db: Session,
    *,
    branch_id: Optional[str],
    name: Optional[str] = None,
    email: Optional[str] = None,
    phone: Optional[str] = None,
    exclude_id: Optional[str] = None,
    company_id: Optional[str] = None,
) -> Optional[str]:
    """Return a human-readable reason if an active duplicate exists."""
    query = db.query(Customer).filter(
        Customer.active == True,
        Customer.customer_type != "walk_in",
    )
    query = _company_branch_filter(query, db, company_id)

    if exclude_id:
        query = query.filter(Customer.id != exclude_id)

    if branch_id:
        query = query.filter(Customer.branch_id == branch_id)

    normalized_name = normalize_customer_name(name) if name else None
    if normalized_name:
        dup = query.filter(
            func.lower(func.trim(Customer.name)) == normalized_name
        ).first()
        if dup:
            return f'An active customer named "{dup.name}" already exists for this branch.'

    email_key = (email or "").strip().lower()
    if email_key:
        dup = query.filter(func.lower(func.trim(Customer.email)) == email_key).first()
        if dup:
            return f'An active customer with email "{dup.email}" already exists for this branch.'

    phone_key = "".join(ch for ch in (phone or "") if ch.isdigit())
    if phone_key:
        dup = query.filter(
            text(
                "regexp_replace(coalesce(customers.phone, ''), '[^0-9]', '', 'g') = :phone_key"
            ).bindparams(phone_key=phone_key)
        ).first()
        if dup:
            return f'An active customer with phone "{dup.phone}" already exists for this branch.'

    return None


def validate_customer_write(
    db: Session,
    *,
    name: str,
    branch_id: Optional[str],
    customer_type: Optional[str] = "retail",
    email: Optional[str] = None,
    phone: Optional[str] = None,
    exclude_id: Optional[str] = None,
    company_id: Optional[str] = None,
) -> None:
    """Raise ValueError when a duplicate or reserved customer would be created."""
    assert_walkin_singleton(
        db,
        customer_type=customer_type,
        name=name,
        exclude_id=exclude_id,
    )

    reason = find_active_duplicate(
        db,
        branch_id=branch_id,
        name=name,
        email=email,
        phone=phone,
        exclude_id=exclude_id,
        company_id=company_id,
    )
    if reason:
        raise ValueError(reason)


def integrity_error_to_message(error_msg: str) -> Optional[str]:
    """Map PostgreSQL unique-index violations to user-facing text."""
    lower = error_msg.lower()
    if "uq_customers_one_walk_in" in lower or (
        "unique" in lower and "walk_in" in lower
    ):
        return "Only one Walk-in Customer is allowed in the system."
    if "uq_customers_active_branch_name" in lower:
        return "A customer with this name already exists for this branch."
    if "uq_customers_active_branch_email" in lower:
        return "A customer with this email already exists for this branch."
    if "uq_customers_active_branch_phone" in lower:
        return "A customer with this phone number already exists for this branch."
    return None
