"""Query FACT_CHARTS for the latest Spotify daily chart date available in Insights."""
import boto3
from cryptography.hazmat.primitives import serialization
from snowflake import connector

import config

logger = config.logger
_secrets = boto3.client('secretsmanager')

_QUERY = """
    SELECT MAX(fc.chart_date)::VARCHAR AS latest_date
    FROM FACTS.PROD.FACT_CHARTS fc
    INNER JOIN FACTS.PROD.DIM_CHART dc ON dc.chartid = fc.chartid
    WHERE dc.platform = 'spotify'
      AND dc.frequency = 'daily'
"""


def get_latest_fact_chart_date() -> str | None:
    """Return the latest chart_date in FACT_CHARTS for Spotify daily charts."""
    conn_params = _get_connection_params()
    with connector.connect(**conn_params) as conn:
        with conn.cursor(connector.DictCursor) as cur:
            cur.execute(_QUERY)
            row = cur.fetchone()
            date_val = row['LATEST_DATE'] if row else None
            logger.info(f'FACT_CHARTS latest Spotify daily chart date: {date_val}')
            return date_val


def _get_connection_params() -> dict:
    private_key_pem = _get_secret('SNOWFLAKE_PRIVATE_KEY')

    private_key = serialization.load_pem_private_key(
        private_key_pem.encode(),
        password=None,
    ).private_bytes(
        encoding=serialization.Encoding.DER,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption(),
    )

    return {
        'account': config.SNOWFLAKE_ACCOUNT,
        'user': config.SNOWFLAKE_USER,
        'private_key': private_key,
        'database': 'FACTS',
        'schema': 'PROD',
        'warehouse': 'PROD_ETL_WAREHOUSE',
    }


def _get_secret(key: str) -> str:
    resp = _secrets.get_secret_value(SecretId=f'{config.SNOWFLAKE_SECRETS_PATH}/{key}')
    return resp['SecretString']