from typing import Any, Dict, Optional, List
from db.snowflake_client import SnowflakeClient


# Initialize database clients
snowflake_client = SnowflakeClient()

def get_delivery_history(
        env: Optional[str] = None,
        sr_ids: Optional[List[str]] = None,
        sr_version_ids: Optional[List[str]] = None,
        service: Optional[str] = None,
        execution_type: Optional[List[str]] = None,
        event_type: Optional[List[str]] = None) -> Dict[str, Any]:
    """
    Get delivery history for sound recordings with optional filtering.

    Args:
        env: Environment name (optional) - used in table path
        sr_ids: List of sound recording IDs to filter by (optional)
        sr_version_ids: List of sound recording version IDs to filter by (optional)
        service: Service name to filter by (optional)
        execution_type: List of execution types to filter by (optional). Valid values: 'METADATA_UPDATE', 'FULL_DELIVERY', 'TAKEDOWN_DELIVERY'
        sta
    Get delivery history for sound recordings with optional filtering.

    Args:
        env: Environment name (optional) - used in table path
        sr_ids: List of sound recording IDs to filter by (optional)
        sr_version_ids: List of sound recording version IDs to filter by (optional)
        service: Service name to filter by (optional)
        execution_type: List of execution types to filter by (optional). Valid values: 'METADATA_UPDATE', 'FULL_DELIVERY', 'TAKEDOWN_DELIVERY'
        event_type: List of statuses to filter by (optional). Valid values: 'success', 'start', 'not_eligible', 'failure'

    Returns:
        Delivery history records matching the criteria

    Returns:
        Delivery history records matching the criteria
    """
    try:
        # Default to 'prod' environment if not specified
        environment = env or "prod"

        # Build the base query
        query = f"""
        SELECT *
        FROM facts.{environment}.orchard_sound_recording_delivery_history
        WHERE 1=1
        """

        params = []

        # Add optional WHERE conditions
        if sr_ids:
            # Create placeholders for each ID
            placeholders = ",".join(["%s" for _ in sr_ids])
            query += f" AND sound_recording_id IN ({placeholders})"
            params.extend(sr_ids)

        if sr_version_ids:
            placeholders = ",".join(["%s" for _ in sr_version_ids])
            query += f" AND sound_recording_version_id IN ({placeholders})"
            params.extend(sr_version_ids)

        if service:
            query += " AND service = %s"
            params.append(service)

        if execution_type:
            placeholders = ",".join(["%s" for _ in execution_type])
            query += f" AND execution_type IN ({placeholders})"
            params.extend(execution_type)

        if event_type:
            placeholders = ",".join(["%s" for _ in event_type])
            query += f" AND event_type IN ({placeholders})"
            params.extend(event_type)

        # Execute query
        results = snowflake_client.query(query, tuple(params) if params else None)

        return {
            "status": "success",
            "count": len(results),
            "env": environment,
            "filters": {
                "sr_ids": sr_ids,
                "sr_version_ids": sr_version_ids,
                "service": service,
                "execution_type": execution_type,
                "event_type": event_type
            },
            "results": results
        }
    except Exception as e:
        return {"status": "error", "message": str(e)}