from typing import Optional, Tuple, List, Dict, Any

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

from . import dto, models
from api.v1.modules.a76.transportation.catalog_parity import (
    transporter_fields_to_csv_row,
    validate_transporter_row_for_api,
)

logger = logging.getLogger(__name__)

TRANSPORTER_ID_SEQ = "a76.transporter_transporter_id_seq"


def allocate_transporter_id(db: Session) -> int:
    """Next surrogate transporter_id (sequence from migration ca7d3c4e8b2a)."""
    return db.execute(text(f"SELECT nextval('{TRANSPORTER_ID_SEQ}')")).scalar()


class TransporterService:
    """Service for Transporter CRUD operations with tenant support"""

    @staticmethod
    def get_all(
        db: Session,
        tenant_id: int,
        company_id: int,
        skip: int = 0,
        limit: int = 50,
        filters: Optional[Dict[str, Any]] = None,
    ) -> Tuple[List[models.Transporter], int]:
        """Get all transporters for a tenant/company with pagination"""
        query = db.query(models.Transporter).filter(
            models.Transporter.tenant_id == tenant_id,
            models.Transporter.company_id == company_id,
        )

        # Apply filters if provided
        if filters:
            if filters.get("transporter_key"):
                query = query.filter(
                    models.Transporter.transporter_key.ilike(f"%{filters['transporter_key']}%")
                )
            if filters.get("name"):
                query = query.filter(
                    models.Transporter.name.ilike(f"%{filters['name']}%")
                )
            if filters.get("rfc"):
                query = query.filter(
                    models.Transporter.rfc.ilike(f"%{filters['rfc']}%")
                )

        total = query.count()
        transporters = query.offset(skip).limit(limit).all()

        return transporters, total

    @staticmethod
    def get_by_id(
        db: Session, transporter_key: str, tenant_id: int, company_id: int
    ) -> Optional[models.Transporter]:
        """Get transporter by transporter_key (exact match after strip)."""
        key = (transporter_key or "").strip()
        if not key:
            return None
        return (
            db.query(models.Transporter)
            .filter(
                models.Transporter.transporter_key == key,
                models.Transporter.tenant_id == tenant_id,
                models.Transporter.company_id == company_id,
            )
            .first()
        )

    @staticmethod
    def get_by_id_ignore_case(
        db: Session, transporter_key: str, tenant_id: int, company_id: int
    ) -> Optional[models.Transporter]:
        """Get transporter by transporter_key (case-insensitive) for given tenant/company."""
        key = (transporter_key or "").strip()
        if not key:
            return None
        return (
            db.query(models.Transporter)
            .filter(
                func.upper(models.Transporter.transporter_key) == key.upper(),
                models.Transporter.tenant_id == tenant_id,
                models.Transporter.company_id == company_id,
            )
            .first()
        )

    @staticmethod
    def create(
        db: Session,
        transporter_data: dto.TransporterCreateDTO,
        tenant_id: int,
        company_id: int,
    ) -> models.Transporter:
        """Create a new transporter"""
        data = transporter_data.model_dump()
        validate_transporter_row_for_api(
            tenant_id,
            company_id,
            transporter_fields_to_csv_row(data),
            is_update=False,
            existing_transporter_keys=set(),
        )
        if data.get("transporter_id") is None:
            data["transporter_id"] = allocate_transporter_id(db)
        new_transporter = models.Transporter(
            **data, tenant_id=tenant_id, company_id=company_id
        )
        db.add(new_transporter)
        db.commit()
        db.refresh(new_transporter)
        return new_transporter

    @staticmethod
    def update(
        db: Session,
        transporter_key: str,
        tenant_id: int,
        transporter_data: dto.TransporterUpdateDTO,
        company_id: int,
    ) -> Optional[models.Transporter]:
        """Update a transporter"""
        transporter = TransporterService.get_by_id(
            db, transporter_key, tenant_id, company_id
        )
        if not transporter:
            return None

        # Update fields (excluding transporter_key as it's the primary key)
        update_data = transporter_data.model_dump(
            exclude_unset=True, exclude={"transporter_key"}
        )
        merged = {
            "transporter_key": transporter.transporter_key,
            "name": transporter.name,
            "short_name": transporter.short_name,
            "responsible": transporter.responsible,
            "rfc": transporter.rfc,
            "streets": transporter.streets,
            "postal_code": transporter.postal_code,
            "city": transporter.city,
            "state": transporter.state,
            "country": transporter.country,
            "loader_code": transporter.loader_code,
            "caat_code": transporter.caat_code,
            "transport_code": transporter.transport_code,
            "transport_interface_type": transporter.transport_interface_type,
            "ftp_server": transporter.ftp_server,
            "ftp_user": transporter.ftp_user,
            "ftp_password": transporter.ftp_password,
            "ftp_directory": transporter.ftp_directory,
            "filler_code": transporter.filler_code,
            "has_express_line": transporter.has_express_line,
        }
        merged.update(update_data)
        validate_transporter_row_for_api(
            tenant_id,
            company_id,
            transporter_fields_to_csv_row(merged),
            is_update=True,
            existing_transporter_keys={transporter_key.strip().upper()},
        )
        for field, value in update_data.items():
            setattr(transporter, field, value)

        db.commit()
        db.refresh(transporter)
        return transporter

    @staticmethod
    def delete(
        db: Session, transporter_key: str, tenant_id: int, company_id: int
    ) -> bool:
        """Delete a transporter"""
        transporter = TransporterService.get_by_id(
            db, transporter_key, tenant_id, company_id
        )
        if not transporter:
            return False

        db.delete(transporter)
        db.commit()
        return True
