"""
Service functions for biotope code operations.
"""
from typing import List, Tuple, Optional
from django.db import connection
from .models import BiotopeCode


def get_hierarchical_title(biotope_code: str) -> List[Tuple[int, str]]:
    """
    Get hierarchical titles for a biotope code.
    
    Args:
        biotope_code: The biotope code (e.g., 'LR.HLR.MusB.Cht.Cht')
    
    Returns:
        List of tuples (level, title) in hierarchical order from top to bottom
        Example: [
            (2, 'Littoral rock (and other hard substrata)'),
            (3, 'High energy littoral rock'),
            (4, 'Mussel and/or barnacle communities'),
            (5, 'Chthamalus spp. on exposed eulittoral rock'),
            (6, 'Chthamalus spp. on exposed upper eulittoral rock'),
        ]
    """
    if not biotope_code:
        return []
    
    # Parse the code to get all parent codes
    parts = biotope_code.split('.')
    codes_to_lookup = []
    
    # Build codes at each level: LR, LR.HLR, LR.HLR.MusB, etc.
    for i in range(len(parts)):
        code = '.'.join(parts[:i+1])
        codes_to_lookup.append(code)
    
    # Fetch all codes in one query
    biotope_codes = BiotopeCode.objects.filter(code__in=codes_to_lookup).order_by('level')
    
    # Build result list
    result = []
    for bc in biotope_codes:
        result.append((bc.level, bc.title))
    
    return result


def format_hierarchical_title(biotope_code: str, include_last_detail: bool = True) -> dict:
    """
    Format hierarchical title with metadata for display.
    
    Args:
        biotope_code: The biotope code (e.g., 'LR.HLR.MusB.Cht.Cht')
        include_last_detail: Whether to include the last (most detailed) level
    
    Returns:
        Dictionary with formatted title information:
        {
            'main_titles': List of (level, title) for main hierarchy,
            'detail_title': Tuple (level, title) for the most detailed level,
            'all_titles': List of all titles in order
        }
    """
    hierarchical = get_hierarchical_title(biotope_code)
    
    if not hierarchical:
        return {
            'main_titles': [],
            'detail_title': None,
            'all_titles': []
        }
    
    if not include_last_detail or len(hierarchical) <= 1:
        return {
            'main_titles': hierarchical,
            'detail_title': None,
            'all_titles': [title for _, title in hierarchical]
        }
    
    # Split main titles and detail title
    main_titles = hierarchical[:-1]
    detail_title = hierarchical[-1]
    
    return {
        'main_titles': main_titles,
        'detail_title': detail_title,
        'all_titles': [title for _, title in hierarchical]
    }


def get_site_centroid(site_id: float) -> Optional[Tuple[float, float]]:
    """
    Get the centroid (latitude, longitude) of a site geometry.
    
    Args:
        site_id: The site ID
    
    Returns:
        Tuple of (latitude, longitude) in WGS84 (EPSG:4326), or None if not found
    """
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT 
                    ST_Y(ST_Transform(ST_Centroid(ST_SetSRID(geom, 27700)), 4326)) as lat,
                    ST_X(ST_Transform(ST_Centroid(ST_SetSRID(geom, 27700)), 4326)) as lon
                FROM gwynedd_nrw_ph1_intertidal_biotope_sites
                WHERE siteid = %s AND geom IS NOT NULL;
            """, [site_id])
            result = cursor.fetchone()
            
            if result and result[0] and result[1]:
                return (float(result[0]), float(result[1]))
    except Exception:
        pass
    
    return None


def get_site_image_url(site_id: float, width: int = 400, height: int = 300, zoom: int = 15) -> Optional[str]:
    """
    Generate a static satellite/aerial image URL for a site.
    
    Uses OpenStreetMap-based services that don't require API keys.
    For better quality, you can configure Mapbox or Google Maps API keys.
    
    Args:
        site_id: The site ID
        width: Image width in pixels (default: 400)
        height: Image height in pixels (default: 300)
        zoom: Map zoom level (default: 15, range 1-20)
    
    Returns:
        URL to static satellite/aerial image, or None if coordinates not found
    """
    centroid = get_site_centroid(site_id)
    if not centroid:
        return None
    
    lat, lon = centroid
    
    # Using OpenStreetMap-based static map service (no API key required)
    # StaticMap.org provides satellite imagery from various sources
    # Format: https://staticmap.openstreetmap.de/staticmap.php?center=LAT,LON&zoom=ZOOM&size=WxH&maptype=mapnik
    
    # For satellite/aerial view, we can use:
    # Option 1: OpenStreetMap with satellite-like styling (hybrid)
    # Option 2: Mapbox if API key is configured (better quality)
    # Option 3: StaticMap.org with satellite tiles
    
    # Using a service that provides aerial/satellite imagery
    # Note: This uses OpenStreetMap which may not have satellite imagery in all areas
    # For UK coastal areas, Bing Maps or Mapbox would be better but require API keys
    
    # StaticMap.org with satellite layer
    url = f"https://staticmap.openstreetmap.de/staticmap.php?center={lat},{lon}&zoom={zoom}&size={width}x{height}&maptype=mapnik"
    
    # Alternative: Use Mapbox if configured (better satellite imagery)
    # Uncomment and configure MAPBOX_ACCESS_TOKEN in settings if you have one
    # from django.conf import settings
    # mapbox_token = getattr(settings, 'MAPBOX_ACCESS_TOKEN', None)
    # if mapbox_token:
    #     url = f"https://api.mapbox.com/styles/v1/mapbox/satellite-v9/static/{lon},{lat},{zoom}/{width}x{height}?access_token={mapbox_token}"
    
    return url