"""Snowflake integration for enriching subscription data."""

from typing import Any

from snowflake_connector.etl_connector import SnowflakeSQLExecutor
import config

subscription_info_query = """
      SELECT
          FS.ID AS FAN_SUBSCRIPTION_ID,
          SF_ML.ID AS MAILING_LIST_ID__C,
          SF_S.FAN_ID_C AS FAN_ID__C,
          SF_S.ID AS SUBSCRIPTION_ID__C
      FROM PREFERENCE_CENTER.{schema}.FAN_SUBSCRIPTION AS FS
      INNER JOIN PREFERENCE_CENTER.{schema}.FAN_PROFILE AS FP
          ON FS.PROFILE_ID = FP.ID
      INNER JOIN PREFERENCE_CENTER.{schema}.FAN_MAILING_LIST AS FML
          ON FS.MAILING_LIST_ID = FML.ID
      INNER JOIN DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.ARTIST_C SF_A
          ON FML.CRM_ARTIST_ID = SF_A.ID
      INNER JOIN DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.TLA_C AS SF_TLA
          ON FML.CRM_ARTIST_ID = SF_TLA.ARTIST_ID_C
      INNER JOIN DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.MAILING_LIST_C AS SF_ML
          ON SF_TLA.ID = SF_ML.TLA_ID_C
      INNER JOIN DELPHI_CRM_DATA.RAW_SALESFORCE_SALES_CLOUD.SUBSCRIPTION_C AS SF_S
          ON SF_ML.ID = SF_S.MAILING_LIST_ID_C AND FP.CRM_ID = SF_S.FAN_ID_C
      WHERE FS.ID in (%(subscription_ids)s)
      ;
""".format(schema=config.settings.snowflake_schema)


def get_subscriptions(subscription_ids: list[str]) -> list[dict[str, Any]]:
    """Fetch subscription details from Snowflake for given subscription IDs.

    Joins data from fan subscription tables with Salesforce CRM data to enrich
    subscription information needed for Salesforce updates.

    Args:
        subscription_ids: List of fan subscription IDs to query

    Returns:
        List of dictionaries containing enriched subscription data with keys:
            - FAN_SUBSCRIPTION_ID: Internal subscription ID
            - MAILING_LIST_ID__C: Salesforce mailing list ID
            - FAN_ID__C: Salesforce fan ID
            - SUBSCRIPTION_ID__C: Salesforce subscription ID
    """
    with SnowflakeSQLExecutor(config.settings.snowflake_config.dict()) as sf_executor:
        return sf_executor.fetchall(subscription_info_query, {'subscription_ids': subscription_ids}, dict_cursor=True)