"""Data availability and store outage helpers."""
import json
from datetime import datetime, timedelta, timezone
from typing import Dict, List

import pytz
from ddtrace import tracer

from analytics.config import FEEDS
from analytics.connectors import snowflake
from analytics.constants import cache
from analytics.features import is_insights_published_max_available_date_enabled
from analytics.utils.cache import cache_in_redis

SQLLoader = snowflake.SQLLoader(__file__)

FIELD_STREAMS = "streams"
FIELD_SKIPS_SAVES = "skips_saves"
FIELD_STORE_ID = "store_id"
FIELD_FEED_ID = "feed_id"
FIELD_TYPES = "types"

STORE_OUTAGE_FIELDS = [FIELD_FEED_ID, FIELD_STORE_ID, FIELD_STREAMS, FIELD_SKIPS_SAVES]


@tracer.wrap(name="get_feed_outages")
@cache_in_redis(
    ttl=cache.ONE_DAY,
    key=cache.REDIS_KEY_FEED_OUTAGES,
    concatenate_key_with_args=True,
)
def get_feed_outages(available_feeds) -> Dict[str, dict]:
    """Get feeds which are currently experiencing outages.

    Returns:
        map of: str(feed id) -> dict - containing feed outage information

    """
    sql = SQLLoader.load_query("store_outages")
    records = snowflake.fetchall(sql, {"feed_ids": list(available_feeds.keys())})
    # Key by str(feed_id): cache_in_redis round-trips the result through JSON,
    # which coerces dict keys to strings. Keying by str here keeps the cache-miss
    # and cache-hit shapes identical so get_feeds_with_outages can look up
    # consistently (otherwise a cache hit silently drops every outage block).
    return {
        str(r[0]): {
            "feed_id": r[0],
            "store_id": r[1],
            "has_streams_outage": r[2],
            "has_skips_saves_outage": r[3],
        }
        for r in records
        if r[2] or r[3]
    }


def _calculate_update_status(store_high_water_mark, feed_id, available_feeds):
    pacific_tz = pytz.timezone("America/Los_Angeles")
    # current date in Snowflake is America/Los_Angeles:
    # https://docs.snowflake.com/en/sql-reference/parameters#timezone

    today_pdt = datetime.now(timezone.utc).astimezone(pacific_tz).date()
    store_high_water_mark_pdt = datetime.strptime(
        store_high_water_mark, "%Y-%m-%d"
    ).date()
    updated_threshold_days = timedelta(
        days=available_feeds[feed_id]["update_threshold_days"]
    )

    return (
        "UPDATED"
        if today_pdt - store_high_water_mark_pdt <= updated_threshold_days
        else "WAITING_FOR_UPDATES"
    )


@cache_in_redis(
    ttl=cache.FIVE_MINUTES,
    key=cache.REDIS_KEY_FEED_OUTAGES_V2,
    concatenate_key_with_args=True,
)
def get_feed_outages_v2(available_feeds) -> List[dict]:
    """Get feeds which are currently experiencing outages.

    Returns:
        map of: feed id -> dict - containing feed outage information

    """
    sql = SQLLoader.load_query("store_outages_by_feed_distributor")
    records = snowflake.fetchall(sql, {"feed_ids": list(available_feeds.keys())})
    result = [
        {
            "distributor": r[0],
            "feed_name": r[1],
            "feed_id": int(r[2]),
            "store_id": int(r[3]),
            "store_high_water_mark": r[4],
            "update_status": _calculate_update_status(r[4], int(r[2]), available_feeds),
            "streaming_stores_high_watermark": r[5],
            "missing_dates_before_store_high_watermark": sorted(json.loads(r[6]))
            if r[6]
            else [],
            "has_streams_outage": r[7],
        }
        for r in records
    ]
    return result


def _get_outage_types(feed_outage: dict) -> List[str]:
    types = []

    if feed_outage.get("has_streams_outage"):
        types.append(FIELD_STREAMS)
    if feed_outage.get("has_skips_saves_outage"):
        types.append(FIELD_SKIPS_SAVES)

    return types


def get_available_feeds() -> Dict[int, dict]:
    """Returns all available feeds accessible to user."""
    return dict(FEEDS)


def get_feeds_with_outages() -> Dict[int, dict]:
    """Return config.FEEDS with appended outage data."""

    available_feeds = get_available_feeds()
    feed_outages = get_feed_outages(available_feeds)

    feeds = {}

    for feed_id, feed in available_feeds.items():
        feed_outage = feed_outages.get(str(feed_id))

        if feed_outage:
            feeds[feed_id] = {
                **feed,
                "error": {
                    "types": _get_outage_types(feed_outage),
                    "code": "unreliable",
                },
            }
        else:
            feeds[feed_id] = {**feed}

    return feeds


def get_feeds_with_outages_v2() -> Dict[str, List[dict]]:
    """Return config.FEEDS with appended outage data.

    Returns:
        Dict with keys:
            - feed_statuses: List of feed status dicts
            - high_water_marks: List of watermark dicts per distributor
    """

    available_feeds = get_available_feeds()
    feed_statuses_from_snowflake = get_feed_outages_v2(available_feeds)

    feed_statuses = []
    # Extract unique watermarks per distributor (one watermark per distributor)
    watermarks_by_distributor = {}

    for feed in feed_statuses_from_snowflake:
        # Extract watermark for this distributor if not already captured
        distributor = feed["distributor"]
        if distributor not in watermarks_by_distributor:
            watermarks_by_distributor[distributor] = feed[
                "streaming_stores_high_watermark"
            ]

        if feed["has_streams_outage"]:
            feed_statuses.append(
                {
                    **feed,
                    "error": {
                        "types": _get_outage_types(feed),
                        "code": "unreliable",
                    },
                }
            )
        else:
            feed_statuses.append({**feed})

    feed_statuses = sorted(
        feed_statuses, key=lambda x: (x["distributor"], x["feed_id"])
    )
    high_water_marks = [
        {
            "distributor": dist,
            "type": "STREAMING_STORES",
            "high_water_mark": watermarks_by_distributor.get(dist),
        }
        for dist in ["sme", "theorchard", "awal"]
    ]

    return {"feed_statuses": feed_statuses, "high_water_marks": high_water_marks}


@tracer.wrap(name="get_outage_stores")
@cache_in_redis(
    ttl=cache.ONE_DAY,
    key=cache.REDIS_KEY_STORE_OUTAGES,
    concatenate_key_with_args=True,
)
def get_outage_stores(available_feeds):
    """Get stores which are currently experiencing outages.

    Returns:
        list: List of unreliable stores.

    """
    sql = SQLLoader.load_query("store_outages")

    records = snowflake.fetchall(sql, {"feed_ids": list(available_feeds.keys())})

    def _map_to_store(record):
        store = dict(zip(STORE_OUTAGE_FIELDS, record))
        types = []

        if store.get(FIELD_STREAMS):
            types.append(FIELD_STREAMS)
        if store.get(FIELD_SKIPS_SAVES):
            types.append(FIELD_SKIPS_SAVES)

        if not types:
            return None

        return {
            FIELD_STORE_ID: store.get(FIELD_STORE_ID),
            FIELD_FEED_ID: store.get(FIELD_FEED_ID),
            FIELD_TYPES: types,
        }

    return [store for store in map(_map_to_store, records) if store is not None]


@tracer.wrap(name="get_max_avaialble_date")
@cache_in_redis(
    ttl=cache.ONE_DAY,
    key=cache.REDIS_KEY_MAX_AVAILABLE_DATE,
    concatenate_key_with_args=True,
)
def get_max_available_date(available_feeds):
    """Get max available date for stores.

    With the insights_published_max_available_date flag on, reads the
    *_PUBLISHED availability snapshot — re-pointed by dbt only after the full
    upgrade_main_pipeline swap batch — so the date can never run ahead of the
    published streams/metrics tables mid-pipeline (GO-4885). The Redis key does
    not encode the flag: both sources agree outside pipeline run windows, and
    the pipeline flushes this cache after every run.

    Returns:
        Datetime date object

    """
    query_name = (
        "store_high_water_mark_published"
        if is_insights_published_max_available_date_enabled()
        else "store_high_water_mark"
    )
    sql = SQLLoader.load_query(query_name)

    max_available_date = snowflake.fetchall(
        sql, {"feed_ids": list(available_feeds.keys())}
    )

    return max_available_date[0][0].strftime("%Y-%m-%d")


@tracer.wrap(name="get_downloads_max_available_date")
@cache_in_redis(
    ttl=cache.ONE_HOUR,
    key=cache.REDIS_KEY_DOWNLOADS_MAX_AVAILABLE_DATE,
    concatenate_key_with_args=True,
)
def get_downloads_max_available_date(available_feeds):
    """Get max available date for stores.

    Returns:
        Datetime date object

    """
    sql = SQLLoader.load_query("downloads_stores_high_water_mark")

    max_available_date = snowflake.fetchall(
        sql, {"feed_ids": list(available_feeds.keys())}
    )

    return max_available_date[0][0].strftime("%Y-%m-%d")


@tracer.wrap(name="get_videos_max_available_date")
@cache_in_redis(
    ttl=cache.ONE_DAY,
    key=cache.REDIS_KEY_VIDEOS_MAX_AVAILABLE_DATE,
    concatenate_key_with_args=True,
)
def get_videos_max_available_date(available_feeds):
    """Get max available date for stores.

    Returns:
        Datetime date object

    """
    sql = SQLLoader.load_query("video_stores_high_water_mark")

    max_available_date = snowflake.fetchall(
        sql, {"feed_ids": list(available_feeds.keys())}
    )

    return max_available_date[0][0].strftime("%Y-%m-%d")