"""
This module contains queries related to export operations.
"""

from ast import literal_eval
from functools import cache
from typing import Any

from .... import logger
from ....constants import AuditTypes, RowReportCols, SnowFlakeColumns, Tables
from ....typings import AuditGroupID

logger = logger.new_logger(__name__)


class Export:
    """Class for queries related to export operations."""

    def __init__(self, client):
        self.client = client

    async def rows_table(self, audit_group_id: AuditGroupID) -> list[dict]:
        """Get row data required for the rows table export.

        Args:
            audit_group_id: Audit group ID for which to get the rows.
        """
        logger.debug("Getting rows table data for audit group {}", audit_group_id)
        query = f"""
            SELECT
                A.id,
                A.type,
                AR.row_idx,
                ART.type AS row_type,
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.UPC}') AS {RowReportCols.UPC},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.ISRC}') AS {RowReportCols.ISRC},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.NAME}') AS {RowReportCols.ARTIST},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.RELEASE_NAME}') AS {RowReportCols.RELEASE_NAME},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.TRACK_NAME}') AS {RowReportCols.TRACK_NAME},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.CONFLICTING_OWNERS}') AS {SnowFlakeColumns.CONFLICTING_OWNERS},
                JSON_EXTRACT(AR.data, '$.{SnowFlakeColumns.CONFLICTING_TERRITORIES}')
                AS {RowReportCols.CONFLICTING_TERRITORIES},
                JSON_EXTRACT(AR.data, '$.{SnowFlakeColumns.LIST_CONFLICTING_TERRITORIES}')
                AS {SnowFlakeColumns.LIST_CONFLICTING_TERRITORIES},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.MATCHED_LABEL_NAME}') AS
                {SnowFlakeColumns.MATCHED_LABEL_NAME},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.CHANNEL_DISPLAY_NAME}') AS
                {SnowFlakeColumns.CHANNEL_DISPLAY_NAME},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.VIDEO_PRIVACY_STATUS}') AS
                {SnowFlakeColumns.VIDEO_PRIVACY_STATUS},
                CAST(JSON_UNQUOTE(JSON_EXTRACT(AR.data, '$.{SnowFlakeColumns.VIEWS}')) AS UNSIGNED)
                 AS {SnowFlakeColumns.VIEWS},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.VIDEO_TITLE}') AS
                {SnowFlakeColumns.VIDEO_TITLE},
                IF(
                    JSON_VALUE(AR.data, '$.{SnowFlakeColumns.VIDEO_ID}') IS NOT NULL,
                    CONCAT('https://www.youtube.com/watch?v=', JSON_VALUE(AR.data,
                    '$.{SnowFlakeColumns.VIDEO_ID}')),
                    NULL
                ) AS {RowReportCols.VIDEO_LINK},
                JSON_VALUE(AR.data, '$.{SnowFlakeColumns.TIME_UPLOADED}') AS
                {SnowFlakeColumns.TIME_UPLOADED},
                JSON_UNQUOTE(JSON_EXTRACT(AR.data,
                '$.{SnowFlakeColumns.OTHER_OWNERS_CLAIMING}')) AS
                {SnowFlakeColumns.OTHER_OWNERS_CLAIMING}
            FROM {Tables.AUDITS_GROUPS} AG
            LEFT JOIN {Tables.AUDITS} A ON A.group = AG.id
            LEFT JOIN {Tables.AUDITS_ROWS} AR ON A.id = AR.audit
            LEFT JOIN {Tables.AUDITS_ROWS_ROWTYPES} ARR ON AR.id = ARR.row_id
            LEFT JOIN {Tables.AUDITS_ROWTYPES} ART ON ARR.rowtype_id = ART.id
            WHERE AG.id = %s
                AND AR.row_idx IS NOT NULL
            """
        rows = await self.client.db.execute_query_fetchall(query, (audit_group_id,))

        # Convert the conflicting territories list from a stringified JSON array
        # to a Python list. Handle cases where the list is None or "null" (None
        # if it's a non-SR audit, null if it's an SR audit but without any values in
        # the JSON array).
        none_or_null: set = {None, "null"}
        list_keys = [
            RowReportCols.CONFLICTING_TERRITORIES,
            RowReportCols.LIST_CONFLICTING_TERRITORIES,
        ]

        for row in rows:
            for list_key in list_keys:
                if row[list_key] in none_or_null:
                    row[list_key] = None
                    continue
                row[list_key] = _parsed_list(row[list_key])

            other_owners_claiming = row[SnowFlakeColumns.OTHER_OWNERS_CLAIMING]
            row[SnowFlakeColumns.OTHER_OWNERS_CLAIMING] = (
                None
                if other_owners_claiming in none_or_null
                else _to_list_non_empty(other_owners_claiming)
            )

        _parsed_list.cache_clear()
        _to_list_non_empty.cache_clear()
        return rows

    async def _get_sr_row_count(self, audit_group_id: AuditGroupID, where: str) -> int:
        """Get the count of rows for the SR audit of a given audit group and where
        clause.

        Args:
            audit_group_id: Audit group ID for which to get the row count.
            where: Where clause to filter the rows, as a string. Will be used in the
                query, appended to the WHERE keyword.
        """
        query = f"""
            SELECT COUNT(*) AS row_count
            FROM {Tables.AUDITS_GROUPS} AG
            LEFT JOIN {Tables.AUDITS} A ON A.group = AG.id
            LEFT JOIN {Tables.AUDITS_ROWS} AR ON AR.audit = A.id
            WHERE AG.id = %s
                AND A.type = '{AuditTypes.SR}'
                AND {where};
            """
        result = await self.client.db.execute_query_fetchone(query, (audit_group_id,))
        return next(iter(result.values()))

    async def get_actionable_conflict_count(self, audit_group_id: AuditGroupID) -> int:
        """Get the count of actionable conflicts, as included in the analysis
        report. Actionable conflicts match the contents of SR8.
        """
        where = f"JSON_VALUE(AR.data, '$.{SnowFlakeColumns.CONFLICTING_OWNERS}') IS NOT NULL"
        return await self._get_sr_row_count(audit_group_id, where)

    async def get_actionable_attached_conflict_count(
        self, audit_group_id: AuditGroupID
    ) -> int:
        """Get the count of actionable attached conflicts, as included in the analysis
        report. Actionable conflicts match the contents of SR4.
        """
        where = f"JSON_VALUE(AR.data, '$.{SnowFlakeColumns.IS_LOCKED}') = 'true'"
        return await self._get_sr_row_count(audit_group_id, where)


@cache
def _to_list_non_empty(value: str, sepator: str = ",") -> list[str]:
    """Convert a string to a list of strings. Empty strings are removed.

    Args:
        value: The string to convert to a list.
        sepator: The separator used in the string to split the values.
    """
    stripped_non_empty = (item.strip() for item in value.split(sepator) if item)
    return [item for item in stripped_non_empty if item]


@cache
def _parsed_list(literal_list: str) -> list[Any]:
    """Convert a stringified list to a Python list.

    Args:
        literal_list: The stringified list to convert to a Python list. E.g.
            '["item1", "item2"]'.
    """
    return sorted(literal_eval(literal_list))