"""
Service layer for Sectors (GSectores) — a76 tenant-scoped catalog.
"""

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

from fastapi import HTTPException
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session
from sqlalchemy import or_

from . import dto, models

logger = logging.getLogger(__name__)


class SectorService:
    """Service for Sector 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.Sector], int]:
        """Get all sectors for a tenant/company with pagination"""
        query = db.query(models.Sector).filter(
            models.Sector.tenant_id == tenant_id,
            models.Sector.company_id == company_id,
        )

        if filters:
            if filters.get("search"):
                search_term = f"%{filters['search']}%"
                query = query.filter(
                    or_(
                        models.Sector.key.ilike(search_term),
                        models.Sector.description.ilike(search_term)
                    )
                )
            if filters.get("key"):
                query = query.filter(
                    models.Sector.key.ilike(f"%{filters['key']}%")
                )
            if filters.get("description"):
                query = query.filter(
                    models.Sector.description.ilike(f"%{filters['description']}%")
                )

        total = query.count()
        sectors = query.order_by(models.Sector.key).offset(skip).limit(limit).all()

        return sectors, total

    @staticmethod
    def get_by_id(
        db: Session, sector_id: int, tenant_id: int, company_id: int
    ) -> Optional[models.Sector]:
        """Get sector by ID"""
        return (
            db.query(models.Sector)
            .filter(
                models.Sector.id == sector_id,
                models.Sector.tenant_id == tenant_id,
                models.Sector.company_id == company_id,
            )
            .first()
        )

    @staticmethod
    def create(
        db: Session,
        sector_data: dto.SectorCreateDTO,
        tenant_id: int,
        company_id: int,
    ) -> models.Sector:
        """Create a new sector"""
        new_sector = models.Sector(
            **sector_data.model_dump(), tenant_id=tenant_id, company_id=company_id
        )
        db.add(new_sector)
        try:
            db.commit()
            db.refresh(new_sector)
            return new_sector
        except IntegrityError as e:
            db.rollback()
            logger.error(f"IntegrityError creating sector: {str(e)}")
            raise HTTPException(
                status_code=400,
                detail="Ya existe un sector con esa clave para esta empresa.",
            )

    @staticmethod
    def update(
        db: Session,
        sector_id: int,
        tenant_id: int,
        sector_data: dto.SectorUpdateDTO,
        company_id: int,
    ) -> Optional[models.Sector]:
        """Update a sector"""
        sector = SectorService.get_by_id(db, sector_id, tenant_id, company_id)
        if not sector:
            return None

        update_data = sector_data.model_dump(exclude_unset=True)
        for field, value in update_data.items():
            setattr(sector, field, value)

        try:
            db.commit()
            db.refresh(sector)
            return sector
        except IntegrityError as e:
            db.rollback()
            logger.error(f"IntegrityError updating sector {sector_id}: {str(e)}")
            raise HTTPException(
                status_code=400,
                detail="Ya existe un sector con esa clave para esta empresa.",
            )

    @staticmethod
    def delete(
        db: Session, sector_id: int, tenant_id: int, company_id: int
    ) -> bool:
        """Delete a sector"""
        sector = SectorService.get_by_id(db, sector_id, tenant_id, company_id)
        if not sector:
            return False

        try:
            db.delete(sector)
            db.commit()
            return True
        except IntegrityError as e:
            db.rollback()
            logger.error(f"IntegrityError deleting sector {sector_id}: {str(e)}")
            if "foreign key constraint" in str(e).lower():
                raise HTTPException(
                    status_code=400,
                    detail="No se puede eliminar el sector porque tiene registros relacionados.",
                )
            raise HTTPException(status_code=400, detail="Error al eliminar el sector.")
        except Exception as e:
            db.rollback()
            logger.error(f"Error deleting sector {sector_id}: {str(e)}")
            raise HTTPException(status_code=500, detail="Error al eliminar el sector.")
