"""
Capa de servicio para lógica de negocio de empresa
"""

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

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

from core.config import settings
from core.s3_keys import tenant_company_prefix
from core.storage_s3 import delete_objects_with_prefix

from .dto import CompanyCreateDTO, CompanyResponseDTO, CompanyUpdateDTO
from .models import Company
from ...audit_log.services.service import AuditService
from ..units_of_measure.seed import seed as units_of_measure_seed
from ..fractions.historical_tariff_fractions.seed import seed as historical_tariff_fractions_seed
from ..fractions.warning_fractions.seed import seed as warning_fractions_seed
from ..sectors.seed import seed as sectors_seed
from core.context import get_user_context
from sqlalchemy import text

logger = logging.getLogger(__name__)


class CompanyService:
    """Servicio para gestión de empresa"""

    def __init__(self, db: Session):
        self.db = db

    # Métodos para TenantCRUDRoutes
    @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[Company], int]:
        """Get all companies for a tenant with pagination"""
        query = db.query(Company).filter(Company.tenant_id == tenant_id, Company.deleted_at.is_(None))

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

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

        return companies, total

    @staticmethod
    def get_by_id(
        db: Session, company_id: int, tenant_id: int, company_id_unused: int
    ) -> Optional[Company]:
        """Get company by ID"""
        return (
            db.query(Company)
            .filter(
                Company.id == company_id,
                Company.tenant_id == tenant_id,
                Company.deleted_at.is_(None)
            )
            .first()
        )

    # ESTE ES EL MÉTODO VIEJO QUE CAUSABA PROBLEMAS (Lo dejamos por si acaso)
    @staticmethod
    def create(
        db: Session,
        company_data: CompanyCreateDTO,
        tenant_id: int,
        company_id: int,
    ) -> Company:
        """Create a new company (MÉTODO GENÉRICO - NO USAR PARA CREACIÓN MANUAL)"""
        try:
            db_company = Company(
                **company_data.model_dump(exclude_unset=True),
                tenant_id=tenant_id
            )

            db.add(db_company)
            db.commit()
            db.refresh(db_company)

            return db_company

        except IntegrityError as e:
            db.rollback()
            logger.error(f"IntegrityError creating company: {str(e)}")
            raise HTTPException(
                status_code=400,
                detail="Company already exists",
            )
        except Exception as e:
            db.rollback()
            logger.error(f"Error creating company: {str(e)}")
            raise HTTPException(status_code=500, detail="Error creating company")


    # ==================== HELPERS FOR FIELD MAPPING ====================
    def _extract_company_fields(self, data: Dict[str, Any]) -> Dict[str, Any]:
        """Extrae campos que pertenecen a la tabla Company principal"""
        company_fields = [
            "name", "rfc", "curp", "main_activity", "program", "program_number",
            "prosec", "prosec_authorization", "sector1", "sector2", "sector3",
            "manufacturer_id", "broker_company", "responsible", "responsible_name",
            "responsible_last_name", "responsible_mother_last_name", "responsible_rfc",
            "position", "logo", "fiscal_deposit",
            "generate_barcodes_with_fiel", "order_format_type",
            "is_service_company", "client_name", "subassembly_mode", "previous_code",
            "active_labels", "active_fractions", "activate_caat", "trans_interface",
            "american_costs", "scaf_readonly", "parts_replacement", "activate_facmexame",
            "part_reference", "international_firm", "ftp_key", "sifra_path",
            "version_type", "sql_language", "balance_operation_mode", "inter_db_name",
            "seventh_amendment"
        ]
        return {k: v for k, v in data.items() if k in company_fields}

    def _extract_certification_fields(self, data: Dict[str, Any]) -> Dict[str, Any]:
        """Extrae campos que pertenecen a CompanyCertification"""
        cert_fields = [
            "is_certified_company", "certified_company_registration",
            "certified_company_start_date", "certified_company_end_date",
            "annex30_certification_date", "annex30_certification_number",
            "annex30_modality", "annex30_company_type", "annex30_renewal_date",
            "annex30_final_certification_date", "is_seciit_company", "is_oea_company", "ctpat_svi",
            "trusted_exporter_number", "neec_company"
        ]
        return {k: v for k, v in data.items() if k in cert_fields}

    def _extract_address_fields(self, data: Dict[str, Any], type_prefix: str) -> Dict[str, Any]:
        """Extrae campos de dirección con base en un prefijo (main_, ind1_, ind2_)"""
        fields = ["street", "exterior_number", "interior_number", "postal_code",
                  "neighborhood", "city", "municipality", "state", "country",
                  "phone", "fax", "email"]
        
        extracted = {}
        for f in fields:
            key = f"{type_prefix}_{f}"
            if key in data:
                extracted[f] = data[key]
        return extracted

    def _extract_prevalidator_fields(self, data: Dict[str, Any]) -> Dict[str, Any]:
        """Extrae campos que pertenecen a CompanyPrevalidator"""
        # Se mapean campos 'prev_*' a los nombres del modelo
        mapping = {
            "prev_customs": "customs",
            "prev_key": "key",
            "prev_patent": "patent",
            "prev_description": "description"
        }
        extracted = {}
        for dto_key, model_key in mapping.items():
            if dto_key in data:
                extracted[model_key] = data[dto_key]
        
        # Retrocompatibilidad con el campo prevalidator_key que ya estaba en el DTO
        if "prevalidator_key" in data and "key" not in extracted:
            extracted["key"] = data["prevalidator_key"]
            
        return extracted

    def _extract_vu_fields(self, data: Dict[str, Any]) -> Dict[str, Any]:
        """Extrae campos que pertenecen a CompanyVU (prefijo vu_)"""
        vu_fields = [
            "webservice_user", "webservice_password", "email", "figure_type",
            "central_path", "xml_files_path", "query_rfc", "validation_rfc",
            "configuration_source", "measurement_units"
        ]
        extracted = {}
        for f in vu_fields:
            key = f"vu_{f}"
            if key in data:
                extracted[f] = data[key]
        return extracted

    def _extract_electronic_agent_fields(self, data: Dict[str, Any]) -> Dict[str, Any]:
        """Extrae campos que pertenecen a CompanyElectronicAgent (prefijo ea_)"""
        ea_fields = [
            "input_folder", "output_folder", "send_mask", "response_mask",
            "response_extension", "counter_start", "counter_end", "counter_next"
        ]
        extracted = {}
        for f in ea_fields:
            key = f"ea_{f}"
            if key in data:
                extracted[f] = data[key]
        return extracted

    def _extract_cfdi_fields(self, data: Dict[str, Any]) -> Dict[str, Any]:
        """Extrae campos que pertenecen a CompanyCFDI (prefijo cfdi_)"""
        cfdi_fields = ["xml_save_path", "cfdi_app_path", "pac_app_path"]
        extracted = {}
        for f in cfdi_fields:
            key = f"cfdi_{f}"
            if key in data:
                extracted[f] = data[key]
        return extracted

    def _extract_digital_certificate_fields(self, data: Dict[str, Any], cert_prefix: str) -> Dict[str, Any]:
        """Extrae campos para un tipo específico de certificado (fiel, cfdi_cert, cancel)"""
        # Mapeo de prefijos DTO a nombres de modelo
        fields_map = {
            f"{cert_prefix}_cer": "cer_file_path",
            f"{cert_prefix}_key": "key_file_path",
            f"{cert_prefix}_pass": "password",
            f"{cert_prefix}_access": "access_key",
            f"{cert_prefix}_cer_exp": "cer_expiration_date",
            f"{cert_prefix}_key_exp": "key_expiration_date"
        }
        
        extracted = {}
        for dto_key, model_key in fields_map.items():
            if dto_key in data:
                extracted[model_key] = data[dto_key]
        
        if extracted:
            # Mapear prefijo al tipo real en base de datos
            model_type_map = {'fiel': 'fiel', 'cfdi_cert': 'cfdi', 'cancel': 'cancellation'}
            extracted['certificate_type'] = model_type_map.get(cert_prefix, cert_prefix)
            
        return extracted


    def flatten_company_dto(self, company: Company) -> Dict[str, Any]:
        """Flattens Company and its submodels into a single dict for DTO validation"""
        # 1. Base Company fields
        result = {
            k: getattr(company, k) 
            for k in company.__mapper__.c.keys()
        }
        
        # 2. Certification fields
        if company.certification:
            cert_fields = [
                "is_certified_company", "certified_company_registration",
                "certified_company_start_date", "certified_company_end_date",
                "annex30_certification_date", "annex30_certification_number",
                "annex30_modality", "annex30_company_type", "annex30_renewal_date",
                "annex30_final_certification_date", "is_seciit_company", "is_oea_company", "ctpat_svi",
                "trusted_exporter_number", "neec_company"
            ]
            for field in cert_fields:
                val = getattr(company.certification, field, None)
                if val is not None:
                    result[field] = val

        # 3. Addresses
        for addr in company.addresses:
            prefix = ""
            if addr.address_type == 'main': prefix = "main_"
            elif addr.address_type == 'industrial': prefix = "ind1_"
            elif addr.address_type == 'industrial2': prefix = "ind2_"
            
            if prefix:
                addr_fields = ["street", "exterior_number", "interior_number", "postal_code",
                             "neighborhood", "city", "municipality", "state", "country",
                             "phone", "fax", "email"]
                for f in addr_fields:
                    val = getattr(addr, f, None)
                    if val is not None:
                        result[f"{prefix}{f}"] = val

        # 4. Prevalidator fields
        if company.prevalidator:
            mapping = {"customs": "prev_customs", "key": "prev_key", 
                      "patent": "prev_patent", "description": "prev_description"}
            for model_f, dto_f in mapping.items():
                val = getattr(company.prevalidator, model_f, None)
                if val is not None:
                    result[dto_f] = val
            # Retrocompatibilidad
            if company.prevalidator.key:
                result["prevalidator_key"] = company.prevalidator.key

        # 5. VU fields
        if company.ventanilla_unica:
            f_list = ["webservice_user", "webservice_password", "email", "figure_type",
                     "central_path", "xml_files_path", "query_rfc", "validation_rfc",
                     "configuration_source", "measurement_units"]
            for f in f_list:
                val = getattr(company.ventanilla_unica, f, None)
                if val is not None:
                    result[f"vu_{f}"] = val

        # 6. Electronic Agent fields
        if company.electronic_agent:
            f_list = ["input_folder", "output_folder", "send_mask", "response_mask",
                     "response_extension", "counter_start", "counter_end", "counter_next"]
            for f in f_list:
                val = getattr(company.electronic_agent, f, None)
                if val is not None:
                    result[f"ea_{f}"] = val
        
        # 7. CFDI fields
        if company.cfdi:
            f_list = ["xml_save_path", "cfdi_app_path", "pac_app_path"]
            for f in f_list:
                val = getattr(company.cfdi, f, None)
                if val is not None:
                    result[f"cfdi_{f}"] = val
        
        # 8. Digital Certificates
        cert_type_map = {'fiel': 'fiel', 'cfdi': 'cfdi_cert', 'cancellation': 'cancel'}
        for dc in company.digital_certificates:
            prefix = cert_type_map.get(dc.certificate_type)
            if prefix:
                result[f"{prefix}_cer"] = dc.cer_file_path
                result[f"{prefix}_key"] = dc.key_file_path
                result[f"{prefix}_pass"] = dc.password
                result[f"{prefix}_access"] = dc.access_key
                result[f"{prefix}_cer_exp"] = dc.cer_expiration_date
                result[f"{prefix}_key_exp"] = dc.key_expiration_date
        
        return result


    # ==================== CRUD METHODS ====================

    def create_company_manually(self, data: CompanyCreateDTO, tenant_id: int, username: str = "System") -> Company:
        from .submodels.certification import CompanyCertification
        from .submodels.prevalidator import CompanyPrevalidator
        from .submodels.address import CompanyAddress
        from .submodels.vu import CompanyVU
        from .submodels.electronic_agent import CompanyElectronicAgent
        from .submodels.cfdi import CompanyCFDI

        try:
            # 1. Preparar datos
            obj_data = data.model_dump(exclude_unset=True)
            
            # 2. Extract fields
            company_data = self._extract_company_fields(obj_data)
            cert_data = self._extract_certification_fields(obj_data)
            preval_data = self._extract_prevalidator_fields(obj_data)
            vu_data = self._extract_vu_fields(obj_data)
            ea_data = self._extract_electronic_agent_fields(obj_data)
            cfdi_data = self._extract_cfdi_fields(obj_data)
            
            addr_main = self._extract_address_fields(obj_data, "main")
            addr_ind1 = self._extract_address_fields(obj_data, "ind1")
            addr_ind2 = self._extract_address_fields(obj_data, "ind2")
            
            fiel_data = self._extract_digital_certificate_fields(obj_data, "fiel")
            cfdi_cert_data = self._extract_digital_certificate_fields(obj_data, "cfdi_cert")
            cancel_cert_data = self._extract_digital_certificate_fields(obj_data, "cancel")


            # 3. Create Company
            db_company = Company(**company_data, tenant_id=tenant_id)
            self.db.add(db_company)
            self.db.flush()  # Generate ID

            # 4. Create submodels
            if cert_data:
                self.db.add(CompanyCertification(**cert_data, company_id=db_company.id))
            if preval_data:
                self.db.add(CompanyPrevalidator(**preval_data, company_id=db_company.id))
            if vu_data:
                self.db.add(CompanyVU(**vu_data, company_id=db_company.id))
            if ea_data:
                self.db.add(CompanyElectronicAgent(**ea_data, company_id=db_company.id))
            if cfdi_data:
                self.db.add(CompanyCFDI(**cfdi_data, company_id=db_company.id))
            
            # 5. Create Digital Certificates
            from .submodels.digital_certificate import CompanyDigitalCertificate
            for dc_data in [fiel_data, cfdi_cert_data, cancel_cert_data]:
                if dc_data:
                    self.db.add(CompanyDigitalCertificate(**dc_data, company_id=db_company.id))
            
            # 6. Create Addresses

            if addr_main:
                self.db.add(CompanyAddress(**addr_main, address_type='main', company_id=db_company.id))
            if addr_ind1:
                self.db.add(CompanyAddress(**addr_ind1, address_type='industrial', company_id=db_company.id))
            if addr_ind2:
                self.db.add(CompanyAddress(**addr_ind2, address_type='industrial2', company_id=db_company.id))

            # 7. Seed company data (tenant/company dependent)
            self._seed_company_data(self.db, tenant_id, db_company.id)

            # 8. Commit
            self.db.commit()

            self.db.refresh(db_company)
            
            # --- Audit Log ---
            try:
                # Si no se pasó un username explícito, intentar obtenerlo del contexto
                if username == "System":
                    ctx = get_user_context()
                    if ctx:
                        username = ctx.get("preferred_username") or ctx.get("email") or "System"

                # Preparamos la data para el log (aplanada)
                log_data = self.flatten_company_dto(db_company)
                
                AuditService.log_crud_operation(
                    db=self.db,
                    table_name="company",
                    operation_type="CREATE",
                    record_data=log_data,
                    username=username,
                    record_id=str(db_company.id),
                    company_id=db_company.id,
                    tenant_id=tenant_id
                )
            except Exception as e:
                logger.error(f"Error creating audit log for company creation: {e}")
            # -----------------

            return db_company
            
        except IntegrityError as e:
            self.db.rollback()
            logger.error(f"IntegrityError creating company manually: {str(e)}")
            raise HTTPException(status_code=400, detail="Error de integridad: Es posible que esta empresa ya exista.")
        except Exception as e:
            self.db.rollback()
            logger.error(f"Error creating company manually: {str(e)}")
            raise HTTPException(status_code=500, detail=f"Error creando empresa: {str(e)}")

    def update(
        self,
        db: Session,
        company_id: int,
        tenant_id: int,
        company_id_unused: int,
        company_data: CompanyUpdateDTO,
        username: str = "System",
    ) -> Optional[Company]:
        """Update a company"""
        from .submodels.certification import CompanyCertification
        from .submodels.prevalidator import CompanyPrevalidator
        from .submodels.address import CompanyAddress
        from .submodels.vu import CompanyVU
        from .submodels.electronic_agent import CompanyElectronicAgent
        from .submodels.cfdi import CompanyCFDI

        session = db if db else self.db
        company = self.get_by_id(session, company_id, tenant_id, company_id_unused)
        if not company: return None

        # --- Audit Log Prep ---
        old_values = {}
        try:
            # Capturamos estado actual para comparar
            # Usamos flatten_company_dto para tener una representación completa
            old_values = self.flatten_company_dto(company)
        except Exception as e:
             logger.error(f"Error prepping audit log (old values): {e}")
        # ----------------------

        update_data = company_data.model_dump(exclude_unset=True)
        
        # 1. Update Company fields
        company_fields = self._extract_company_fields(update_data)
        for field, value in company_fields.items():
            setattr(company, field, value)

        # 2. Update Certification
        cert_fields = self._extract_certification_fields(update_data)
        if cert_fields:
            if company.certification:
                for field, value in cert_fields.items(): setattr(company.certification, field, value)
            else:
                session.add(CompanyCertification(**cert_fields, company_id=company.id))

        # 3. Update Prevalidator
        preval_fields = self._extract_prevalidator_fields(update_data)
        if preval_fields:
            if company.prevalidator:
                for field, value in preval_fields.items(): setattr(company.prevalidator, field, value)
            else:
                session.add(CompanyPrevalidator(**preval_fields, company_id=company.id))

        # 4. Update VU
        vu_fields = self._extract_vu_fields(update_data)
        if vu_fields:
            if company.ventanilla_unica:
                for field, value in vu_fields.items(): setattr(company.ventanilla_unica, field, value)
            else:
                session.add(CompanyVU(**vu_fields, company_id=company.id))

        # 5. Update Electronic Agent
        ea_fields = self._extract_electronic_agent_fields(update_data)
        if ea_fields:
            if company.electronic_agent:
                for field, value in ea_fields.items(): setattr(company.electronic_agent, field, value)
            else:
                session.add(CompanyElectronicAgent(**ea_fields, company_id=company.id))

        # 6. Update CFDI
        cfdi_fields = self._extract_cfdi_fields(update_data)
        if cfdi_fields:
            if company.cfdi:
                for field, value in cfdi_fields.items(): setattr(company.cfdi, field, value)
            else:
                session.add(CompanyCFDI(**cfdi_fields, company_id=company.id))

        # 7. Update Digital Certificates
        from .submodels.digital_certificate import CompanyDigitalCertificate
        for p in ["fiel", "cfdi_cert", "cancel"]:
            dc_data = self._extract_digital_certificate_fields(update_data, p)
            if dc_data:
                m_type = dc_data['certificate_type']
                target = next((c for c in company.digital_certificates if c.certificate_type == m_type), None)
                if target:
                    for field, value in dc_data.items(): setattr(target, field, value)
                else:
                    session.add(CompanyDigitalCertificate(**dc_data, company_id=company.id))

        # 8. Update Addresses

        for prefix, addr_type in [("main", "main"), ("ind1", "industrial"), ("ind2", "industrial2")]:
            addr_data = self._extract_address_fields(update_data, prefix)
            if addr_data:
                # Buscar dirección existente de ese tipo
                target_addr = next((a for a in company.addresses if a.address_type == addr_type), None)
                if target_addr:
                    for field, value in addr_data.items(): setattr(target_addr, field, value)
                else:
                    session.add(CompanyAddress(**addr_data, address_type=addr_type, company_id=company.id))

        try:
            session.commit()
            session.refresh(company)

            # --- Audit Log ---
            try:
                # Si no se pasó un username explícito, intentar obtenerlo del contexto
                if username == "System":
                    ctx = get_user_context()
                    if ctx:
                        username = ctx.get("preferred_username") or ctx.get("email") or "System"

                new_values = self.flatten_company_dto(company)
                
                AuditService.log_crud_operation(
                    db=session,
                    table_name="company",
                    operation_type="UPDATE",
                    record_data=new_values, # Data más reciente
                    username=username,
                    record_id=str(company.id),
                    old_values=old_values,
                    new_values=new_values,
                    company_id=company.id,
                    tenant_id=tenant_id
                )
            except Exception as e:
                logger.error(f"Error creating audit log for company update: {e}")
            # -----------------

            return company

        except Exception as e:
            session.rollback()
            logger.error(f"Error updating company {company_id}: {str(e)}")
            raise HTTPException(status_code=500, detail=f"Error al actualizar la empresa: {str(e)}")

    @staticmethod
    def delete(
        db: Session, company_id: int, tenant_id: int, company_id_unused: int, username: str = "System"
    ) -> bool:
        """Delete a company"""
        company = CompanyService.get_by_id(db, company_id, tenant_id, company_id_unused)
        if not company:
            return False

        # --- Audit Log Prep ---
        record_data = {}
        try:
            service = CompanyService(db) # Instancia para usar métodos de instancia si fuera necesario, o usar estático si flatten lo fuera
            # flatten_company_dto es método de instancia en la definición actual, pero se está llamando aquí
            # Deberíamos instanciar el servicio o mover flatten a estático. 
            # Como flatten usa self solo para acceder a nada realmente del estado, podría ser estático, 
            # pero para no romper, instanciamos.
            record_data = service.flatten_company_dto(company)
        except Exception:
            pass
        # ----------------------

        try:
            # 1) Borrado lógico en base de datos
            company.deleted_at = datetime.utcnow()
            db.flush()

            # 2) Limpieza de objetos S3/MinIO asociados a la compañía
            if settings.use_s3_object_storage:
                try:
                    prefix = tenant_company_prefix(tenant_id, company_id)
                    delete_objects_with_prefix(prefix)
                except Exception as e:
                    # No bloquear la eliminación lógica si falla la limpieza de objetos
                    logger.error(
                        "Error deleting S3 objects for company %s (tenant %s): %s",
                        company_id,
                        tenant_id,
                        e,
                    )

            db.commit()

            # --- Audit Log ---
            try:
                # Context check
                if username == "System":
                    ctx = get_user_context()
                    if ctx:
                        username = ctx.get("preferred_username") or ctx.get("email") or "System"
                
                AuditService.log_crud_operation(
                    db=db,
                    table_name="company",
                    operation_type="DELETE",
                    record_data=record_data,
                    username=username,
                    record_id=str(company_id),
                    company_id=company_id,
                    tenant_id=tenant_id
                )
            except Exception as e:
                logger.error(f"Error creating audit log for company delete: {e}")
            # -----------------

            return True
        except IntegrityError as e:
            db.rollback()
            logger.error(f"IntegrityError deleting company {company_id}: {str(e)}")
            raise HTTPException(status_code=400, detail="No se puede eliminar la empresa porque tiene registros relacionados.")
        except Exception as e:
            db.rollback()
            logger.error(f"Error deleting company {company_id}: {str(e)}")
            raise HTTPException(status_code=500, detail="Error al eliminar la empresa")

    def upload_certificate(
        self,
        company_id: int,
        certificate_type: str,
        file_path: str,
        tenant_id: int
    ) -> Company:
        """
        Update a certificate path for a company
        certificate_type: fiel_cer, fiel_key, cfdi_cert_cer, cfdi_cert_key, cancel_cer, cancel_key
        """
        from .submodels.digital_certificate import CompanyDigitalCertificate
        
        company = self.get_by_id(self.db, company_id, tenant_id, 0)
        if not company:
            return None
            
        # Determinar el tipo de certificado (fiel, cfdi, cancellation) y el campo a actualizar (cer_file_path, key_file_path)
        cert_model_type = ""
        field_to_update = ""
        
        if certificate_type == "fiel_cer":
            cert_model_type = "fiel"
            field_to_update = "cer_file_path"
        elif certificate_type == "fiel_key":
            cert_model_type = "fiel"
            field_to_update = "key_file_path"
        elif certificate_type == "cfdi_cert_cer":
            cert_model_type = "cfdi"
            field_to_update = "cer_file_path"
        elif certificate_type == "cfdi_cert_key":
            cert_model_type = "cfdi"
            field_to_update = "key_file_path"
        elif certificate_type == "cancel_cer":
            cert_model_type = "cancellation"
            field_to_update = "cer_file_path"
        elif certificate_type == "cancel_key":
            cert_model_type = "cancellation"
            field_to_update = "key_file_path"
        else:
            raise ValueError(f"Invalid certificate type: {certificate_type}")
            
        # Buscar el registro de certificado existente
        target_cert = next((c for c in company.digital_certificates if c.certificate_type == cert_model_type), None)
        
        try:
            if target_cert:
                old_path = getattr(target_cert, field_to_update, None)
                if old_path and str(old_path).startswith("tenants/"):
                    from core.storage_s3 import delete_object_if_exists

                    delete_object_if_exists(str(old_path))
                # Si existe, actualizamos
                setattr(target_cert, field_to_update, file_path)
            else:
                # Si no existe, creamos uno nuevo
                new_cert_data = {
                    "certificate_type": cert_model_type,
                    "company_id": company.id,
                    field_to_update: file_path
                }
                new_cert = CompanyDigitalCertificate(**new_cert_data)
                self.db.add(new_cert)
                
            self.db.commit()
            self.db.refresh(company)
            return company
        except Exception as e:
            self.db.rollback()
            logger.error(f"Error uploading certificate: {str(e)}")
            raise HTTPException(status_code=500, detail=f"Error al guardar la referencia del certificado: {str(e)}")


    # Custom methods
    def _seed_company_data(self, db: Session, tenant_id: int, company_id: int):
        """Seeds tenant/company dependent data for a new company"""
        def format_value(val):
            if val is None or str(val).strip() == "" or str(val).upper() == "NONE":
                return "NULL"
            return f"'{str(val).replace(chr(39), chr(39)*2)}'"

        # 1. Units of Measure
        val_uom = ", ".join(
            [
                f"({format_value(code)}, {format_value(desc)}, {format_value(desc_en)}, "
                f"{format_value(customs)}, {format_value(american)}, {format_value(ace)}, {format_value(oma)}, {tenant_id}, {company_id})"
                for code, desc, desc_en, customs, american, ace, oma in units_of_measure_seed
            ]
        )
        
        db.execute(text("ALTER TABLE a76.units_of_measure DISABLE TRIGGER ALL;"))
        db.execute(text(f"INSERT INTO a76.units_of_measure (code, description, description_en, customs_code, american_code, ace_code, oma_code, tenant_id, company_id) VALUES {val_uom} ON CONFLICT (code, tenant_id, company_id) DO NOTHING;"))
        db.execute(text("ALTER TABLE a76.units_of_measure ENABLE TRIGGER ALL;"))

        # 2. Historical Tariff Fractions
        def format_bool(val):
            if val is None or str(val).strip() == "" or str(val).upper() == "NONE":
                return "NULL"
            return "TRUE" if str(val).upper() == "TRUE" else "FALSE"

        def format_timestamp(val):
            if val is None or str(val).strip() == "" or str(val).upper() == "NONE":
                return "NULL"
            return f"'{str(val)}'"

        values_historical = ", ".join(
            [
                f"({format_value(historical_fraction)}, {format_value(nico)}, {format_value(unit_measure)}, {format_value(country)}, "
                f"{format_value(fraction_type)}, {format_value(sector)}, {format_value(import_tax)}, "
                f"{format_value(export_tax)}, {format_timestamp(pub_date)}, {format_bool(is_immex)}, "
                f"{format_bool(normal_temp)}, {format_bool(services_temp)}, {format_bool(certified_temp)}, "
                f"{format_bool(by_log)}, {format_timestamp(end_date)}, "
                f"{tenant_id}, {company_id})"
                for (historical_fraction, nico, unit_measure, country, fraction_type, sector, import_tax, 
                        export_tax, pub_date, is_immex, normal_temp, services_temp, certified_temp, by_log, end_date) in historical_tariff_fractions_seed
            ]
        )

        if values_historical:
            db.execute(text("SET session_replication_role = replica;"))
            db.execute(text(f"""
                INSERT INTO a76.historical_tariff_fractions 
                (historical_fraction, nico, unit_of_measure_code, country, fraction_type, sector, 
                    import_tax_rate, export_tax_rate, publication_date, is_immex, 
                    normal_temporality, services_temporality, certified_temporality, by_log, end_date,
                    tenant_id, company_id)
                VALUES {values_historical}
                ON CONFLICT DO NOTHING;
            """))
            db.execute(text("SET session_replication_role = DEFAULT;"))

        # 3. Warning Fractions
        values_warning = ", ".join(
            [
                f"({format_value(fraction)}, {format_value(description)}, {format_value(warning_type)}, {tenant_id}, {company_id})"
                for fraction, description, warning_type in warning_fractions_seed
            ]
        )

        if values_warning:
            db.execute(text("ALTER TABLE public.warning_fractions DISABLE TRIGGER ALL;"))
            db.execute(text(f"""
                INSERT INTO public.warning_fractions
                (fraction, description, warning_type, tenant_id, company_id)
                VALUES {values_warning}
                ON CONFLICT (fraction, company_id) DO NOTHING;
            """))
            db.execute(text("ALTER TABLE public.warning_fractions ENABLE TRIGGER ALL;"))

        # 4. Sectors
        values_sectors = ", ".join(
            [
                f"({format_value(key)}, {format_value(description)}, {str(authorized).upper()}, {tenant_id}, {company_id})"
                for key, description, authorized in sectors_seed
            ]
        )

        if values_sectors:
            db.execute(text(f"""
                INSERT INTO a76.sectors (key, description, authorized, tenant_id, company_id)
                VALUES {values_sectors}
                ON CONFLICT (key, tenant_id, company_id) DO NOTHING;
            """))

    def get_companies_by_tenant(self, tenant_id: int) -> List[Company]:
        """Get all companies for a tenant"""
        return (
            self.db.query(Company)
            .filter(Company.tenant_id == tenant_id, Company.deleted_at.is_(None))
            .order_by(Company.name)
            .all()
        )

    def get_companies_for_session_user(
        self,
        keycloak_user_id: Optional[str],
        tenant_id_from_token: Optional[int],
    ) -> List[Company]:
        """
        Compañías visibles para el usuario: unión de (a) todas las del tenant si el
        token/Hub aporta tenant_id, y (b) compañías con membership RBAC o user_tenants.

        Permite usuarios sin tenant_id en el JWT pero con roles asignados en la app.
        """
        from core.security import collect_company_ids_from_app_membership

        company_ids: Set[int] = set(
            collect_company_ids_from_app_membership(
                self.db, keycloak_user_id or ""
            )
        )
        if tenant_id_from_token is not None:
            for c in self.get_companies_by_tenant(int(tenant_id_from_token)):
                company_ids.add(c.id)
        if not company_ids:
            return []
        return (
            self.db.query(Company)
            .filter(Company.id.in_(company_ids), Company.deleted_at.is_(None))
            .order_by(Company.name)
            .all()
        )

    def exists_company(self, tenant_id: int) -> bool:
        """Check if a company exists for a tenant"""
        return (
            self.db.query(Company)
            .filter(Company.tenant_id == tenant_id, Company.deleted_at.is_(None))
            .first()
            is not None
        )