from datetime import datetime, timezone

from fastapi import HTTPException, status
from sqlalchemy.orm import Session

from ..accounts.models import Account
from ..cases import service as cases_service
from ..catalogs.data import INCOTERM_CODES
from ..common.folios import next_folio
from ..contacts.models import Contact
from ..opportunities.models import Opportunity
from ..suppliers.models import Supplier
from .dto import (
    RateRequestCreate,
    RateRequestUpdate,
    ServiceRequestContactInput,
    ServiceRequestCreate,
    ServiceRequestFromOpportunityInput,
    ServiceRequestUpdate,
)
from .models import RateRequest, ServiceRequest


def _exists(db: Session, model, _id: int | None, tenant_id: int, company_id: int) -> bool:
    if _id is None:
        return True
    return (
        db.query(model.id)
        .filter(
            model.id == _id,
            model.tenant_id == tenant_id,
            model.company_id == company_id,
            model.deleted_at.is_(None),
        )
        .first()
        is not None
    )


def _validate_request_refs(db: Session, data: dict, tenant_id: int, company_id: int) -> None:
    if not _exists(db, Account, data.get("account_id"), tenant_id, company_id):
        raise HTTPException(status_code=status.HTTP_422_UNPROCESSABLE_ENTITY, detail="El cliente asociado no existe")
    if not _exists(db, Contact, data.get("contact_id"), tenant_id, company_id):
        raise HTTPException(status_code=status.HTTP_422_UNPROCESSABLE_ENTITY, detail="El contacto asociado no existe")
    if not _exists(db, Supplier, data.get("destination_agent_id"), tenant_id, company_id):
        raise HTTPException(status_code=status.HTTP_422_UNPROCESSABLE_ENTITY, detail="El agente en destino no existe")
    if not _exists(db, Opportunity, data.get("opportunity_id"), tenant_id, company_id):
        raise HTTPException(status_code=status.HTTP_422_UNPROCESSABLE_ENTITY, detail="La oportunidad asociada no existe")
    incoterm = data.get("incoterm")
    if incoterm and incoterm not in INCOTERM_CODES:
        raise HTTPException(
            status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
            detail=f"Incoterm inválido: usa uno del catálogo ({', '.join(sorted(INCOTERM_CODES))})",
        )


# ----- Service requests (RFQ) -----

def get_service_requests(
    db: Session,
    tenant_id: int,
    company_id: int,
    search: str | None = None,
    req_status: str | None = None,
    operation_type: str | None = None,
    account_id: int | None = None,
) -> list[ServiceRequest]:
    query = db.query(ServiceRequest).filter(
        ServiceRequest.tenant_id == tenant_id,
        ServiceRequest.company_id == company_id,
        ServiceRequest.deleted_at.is_(None),
    )
    if req_status:
        query = query.filter(ServiceRequest.status == req_status)
    if operation_type:
        query = query.filter(ServiceRequest.operation_type == operation_type)
    if account_id is not None:
        query = query.filter(ServiceRequest.account_id == account_id)
    if search:
        pattern = f"%{search}%"
        query = query.filter(
            ServiceRequest.reference.ilike(pattern)
            | ServiceRequest.origin.ilike(pattern)
            | ServiceRequest.destination.ilike(pattern)
        )
    return query.order_by(ServiceRequest.created_at.desc()).all()


def get_service_request(db: Session, request_id: int, tenant_id: int, company_id: int) -> ServiceRequest:
    obj = (
        db.query(ServiceRequest)
        .filter(
            ServiceRequest.id == request_id,
            ServiceRequest.tenant_id == tenant_id,
            ServiceRequest.company_id == company_id,
            ServiceRequest.deleted_at.is_(None),
        )
        .first()
    )
    if not obj:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Solicitud no encontrada")
    return obj


def create_service_request(
    db: Session, payload: ServiceRequestCreate, tenant_id: int, company_id: int, user_id: str | None = None
) -> ServiceRequest:
    data = payload.model_dump()
    _validate_request_refs(db, data, tenant_id, company_id)
    obj = ServiceRequest(**data, tenant_id=tenant_id, company_id=company_id, created_by=user_id, updated_by=user_id)
    # Folio S... auto-generado (mensual) si no viene uno explícito
    if not obj.reference:
        obj.reference = next_folio(db, tenant_id, company_id, "S", obj.operation_type)
    # Expediente: normalmente nace en la oportunidad; si la solicitud es directa, se mintea aquí
    if not obj.case_id:
        case = cases_service.create_case(
            db, tenant_id, company_id, account_id=obj.account_id, title=obj.reference, stage="solicitud", user_id=user_id,
        )
        obj.case_id = case.id
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


def update_service_request(
    db: Session, request_id: int, payload: ServiceRequestUpdate, tenant_id: int, company_id: int, user_id: str | None = None
) -> ServiceRequest:
    obj = get_service_request(db, request_id, tenant_id, company_id)
    data = payload.model_dump(exclude_unset=True)
    _validate_request_refs(db, data, tenant_id, company_id)
    for field, value in data.items():
        setattr(obj, field, value)
    obj.updated_by = user_id
    db.commit()
    db.refresh(obj)
    return obj


def delete_service_request(db: Session, request_id: int, tenant_id: int, company_id: int) -> None:
    obj = get_service_request(db, request_id, tenant_id, company_id)
    obj.deleted_at = datetime.now(timezone.utc)
    db.commit()


def register_contact(
    db: Session, request_id: int, payload: ServiceRequestContactInput, tenant_id: int, company_id: int,
    user_id: str | None = None,
) -> ServiceRequest:
    """Registra el contacto al cliente como etapa del flujo comercial (R-C-04)."""
    obj = get_service_request(db, request_id, tenant_id, company_id)
    obj.first_contact_at = datetime.now(timezone.utc)
    obj.first_contact_notes = payload.notes
    if obj.status == "nueva":
        obj.status = "contacto"
    obj.updated_by = user_id
    db.commit()
    db.refresh(obj)
    return obj


def create_from_opportunity(
    db: Session, opportunity_id: int, payload: ServiceRequestFromOpportunityInput,
    tenant_id: int, company_id: int, user_id: str | None = None,
) -> ServiceRequest:
    """Convierte una oportunidad del embudo en una solicitud/RFQ enlazada (R-C-02).

    Da continuidad al hilo comercial: el embudo (primer contacto) queda ligado a la
    cadena RFQ → cotización → embarque vía ``opportunity_id``.
    """
    opp = (
        db.query(Opportunity)
        .filter(
            Opportunity.id == opportunity_id,
            Opportunity.tenant_id == tenant_id,
            Opportunity.company_id == company_id,
            Opportunity.deleted_at.is_(None),
        )
        .first()
    )
    if not opp:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Oportunidad no encontrada")

    # Idempotente: si la oportunidad ya se convirtió, devuelve la misma solicitud
    if opp.converted_service_request_id:
        existing = get_service_request(db, opp.converted_service_request_id, tenant_id, company_id)
        return existing

    # La dirección impo/expo se hereda de la oportunidad (respaldo: el payload)
    operation_type = opp.operation_type or payload.operation_type
    if not operation_type:
        raise HTTPException(
            status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
            detail="Define la dirección (importación/exportación) en la oportunidad para convertirla",
        )
    obj = ServiceRequest(
        account_id=opp.account_id,
        contact_id=opp.contact_id,
        opportunity_id=opp.id,
        operation_type=operation_type,
        transport_mode=payload.transport_mode,
        service_type=payload.service_type,
        incoterm=payload.incoterm,
        origin=payload.origin,
        destination=payload.destination,
        status="nueva",
        notes=payload.notes,
        owner_user_id=opp.owner_user_id,
        reference=next_folio(db, tenant_id, company_id, "S", operation_type),
        case_id=opp.case_id,
        tenant_id=tenant_id,
        company_id=company_id,
        created_by=user_id,
        updated_by=user_id,
    )
    db.add(obj)
    db.flush()
    # Expediente heredado de la oportunidad (fallback si la oportunidad es antigua sin expediente)
    if not obj.case_id:
        obj.case_id = cases_service.create_case(
            db, tenant_id, company_id, account_id=opp.account_id, title=obj.reference, stage="solicitud", user_id=user_id,
        ).id
        opp.case_id = obj.case_id
    cases_service.advance_stage(db, obj.case_id, "solicitud")
    # Back-link para cerrar el ciclo Oportunidad→Solicitud (y garantizar idempotencia)
    opp.converted_service_request_id = obj.id
    db.commit()
    db.refresh(obj)
    return obj


def reopen_for_requote(
    db: Session, request_id: int, tenant_id: int, company_id: int, user_id: str | None = None
) -> ServiceRequest:
    """Reabre una solicitud rechazada para volver a cotizar (R-C-12)."""
    obj = get_service_request(db, request_id, tenant_id, company_id)
    if obj.status not in ("rechazada", "cotizada"):
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Solo una solicitud rechazada o cotizada puede reabrirse para re-cotizar",
        )
    obj.status = "en_analisis"
    obj.updated_by = user_id
    db.commit()
    db.refresh(obj)
    return obj


# ----- Rate requests -----

def get_rate_requests(
    db: Session, tenant_id: int, company_id: int, service_request_id: int | None = None
) -> list[RateRequest]:
    query = db.query(RateRequest).filter(
        RateRequest.tenant_id == tenant_id,
        RateRequest.company_id == company_id,
        RateRequest.deleted_at.is_(None),
    )
    if service_request_id is not None:
        query = query.filter(RateRequest.service_request_id == service_request_id)
    return query.order_by(RateRequest.id.asc()).all()


def get_rate_request(db: Session, rate_id: int, tenant_id: int, company_id: int) -> RateRequest:
    obj = (
        db.query(RateRequest)
        .filter(
            RateRequest.id == rate_id,
            RateRequest.tenant_id == tenant_id,
            RateRequest.company_id == company_id,
            RateRequest.deleted_at.is_(None),
        )
        .first()
    )
    if not obj:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Solicitud de tarifa no encontrada")
    return obj


def create_rate_request(db: Session, payload: RateRequestCreate, tenant_id: int, company_id: int) -> RateRequest:
    data = payload.model_dump()
    # La solicitud de servicio debe existir en el tenant/company
    get_service_request(db, data["service_request_id"], tenant_id, company_id)
    if not _exists(db, Supplier, data.get("supplier_id"), tenant_id, company_id):
        raise HTTPException(status_code=status.HTTP_422_UNPROCESSABLE_ENTITY, detail="El proveedor no existe")
    obj = RateRequest(**data, tenant_id=tenant_id, company_id=company_id)
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


def update_rate_request(
    db: Session, rate_id: int, payload: RateRequestUpdate, tenant_id: int, company_id: int
) -> RateRequest:
    obj = get_rate_request(db, rate_id, tenant_id, company_id)
    data = payload.model_dump(exclude_unset=True)
    if "supplier_id" in data and not _exists(db, Supplier, data["supplier_id"], tenant_id, company_id):
        raise HTTPException(status_code=status.HTTP_422_UNPROCESSABLE_ENTITY, detail="El proveedor no existe")
    for field, value in data.items():
        setattr(obj, field, value)
    db.commit()
    db.refresh(obj)
    return obj


def delete_rate_request(db: Session, rate_id: int, tenant_id: int, company_id: int) -> None:
    obj = get_rate_request(db, rate_id, tenant_id, company_id)
    obj.deleted_at = datetime.now(timezone.utc)
    db.commit()
