"""Service to retrieve individual coverage records from their respective tables."""
from typing import Dict, Any, Optional, List
from sqlmodel import Session, select

from app.services.coverage_mapper import coverage_mapper


class CoverageRetrievalService:
    """Service to retrieve coverage records from individual tables."""
    
    def __init__(self, session: Session):
        self.session = session
    
    def get_coverage_records_by_business(self, business_name: str, owner_email: str) -> Dict[str, List[Any]]:
        """Get all coverage records for a business across all coverage types."""
        coverage_records = {}
        
        for coverage_type in coverage_mapper.get_supported_coverages():
            model_class = coverage_mapper.get_model_for_coverage(coverage_type)
            if model_class:
                records = self._get_records_for_coverage(model_class, business_name, owner_email)
                if records:
                    coverage_records[coverage_type] = records
        
        return coverage_records
    
    def get_coverage_record_by_type(self, coverage_type: str, business_name: str, owner_email: str) -> Optional[Any]:
        """Get coverage record for specific coverage type."""
        model_class = coverage_mapper.get_model_for_coverage(coverage_type)
        if not model_class:
            return None
        
        records = self._get_records_for_coverage(model_class, business_name, owner_email)
        return records[0] if records else None
    
    def _get_records_for_coverage(self, model_class, business_name: str, owner_email: str) -> List[Any]:
        """Get records from specific coverage table."""
        try:
            # Try different field combinations based on model structure
            stmt = None
            
            # Check if model has business_name field
            if hasattr(model_class, 'business_name'):
                stmt = select(model_class).where(model_class.business_name == business_name)
            elif hasattr(model_class, 'company_name'):
                stmt = select(model_class).where(model_class.company_name == business_name)
            
            # Add email filter if available
            if stmt is not None:
                if hasattr(model_class, 'email_address'):
                    stmt = stmt.where(model_class.email_address == owner_email)
                elif hasattr(model_class, 'contact_email'):
                    stmt = stmt.where(model_class.contact_email == owner_email)
            
            if stmt is not None:
                return list(self.session.exec(stmt).all())
            
        except Exception:
            pass
        
        return []
    
    def get_all_coverage_types_for_business(self, business_name: str) -> List[str]:
        """Get list of coverage types that have records for a business."""
        coverage_types = []
        
        for coverage_type in coverage_mapper.get_supported_coverages():
            model_class = coverage_mapper.get_model_for_coverage(coverage_type)
            if model_class:
                try:
                    stmt = None
                    if hasattr(model_class, 'business_name'):
                        stmt = select(model_class).where(model_class.business_name == business_name)
                    elif hasattr(model_class, 'company_name'):
                        stmt = select(model_class).where(model_class.company_name == business_name)
                    
                    if stmt is not None:
                        result = self.session.exec(stmt).first()
                        if result:
                            coverage_types.append(coverage_type)
                except Exception:
                    continue
        
        return coverage_types