from typing import ClassVar
from uuid import UUID

from psycopg import sql
from src.__types.index import GroupBy
from src.const import SERVICE_SUM_KEY
from src.domain.entities.quota_allocation import QuotaAllocationStatus


class QuotaUsageAggregateQueryBuilder:
    """クォータ使用実績集計クエリを構築するクラス"""

    ROLLUP_COLUMN = SERVICE_SUM_KEY

    _TABLES: ClassVar[dict[str, sql.Identifier]] = {
        "quota_usages": sql.Identifier("quota_usages"),
        "quota_allocations": sql.Identifier("quota_allocations"),
        "usage_events": sql.Identifier("usage_events"),
        "cte_quota_usages": sql.Identifier("cte_quota_usages"),
    }
    _SELECT_COLUMN_MAP: ClassVar[dict[str, sql.Composed]] = {
        "service": sql.SQL("COALESCE({t}.service, {rollup_column})").format(
            t=_TABLES["usage_events"],
            rollup_column=sql.Literal(ROLLUP_COLUMN),
        ),  # rollupで集計したキーの合計はnullになるので代替
        "year": sql.SQL("EXTRACT(YEAR FROM {t}.timestamp)::int").format(t=_TABLES["usage_events"]),
        "month": sql.SQL("EXTRACT(MONTH FROM {t}.timestamp)::int").format(t=_TABLES["usage_events"]),
        "used": sql.SQL("COALESCE(SUM({cte}.unit), 0)").format(cte=_TABLES["cte_quota_usages"]),
    }

    _WHERE_CONDITION_MAP: ClassVar[dict[str, sql.Composed]] = {
        "tenant_id": sql.SQL("{t}.tenant_id = %(tenant_id)s").format(t=_TABLES["usage_events"]),
        "year": sql.SQL("EXTRACT(YEAR FROM {t}.timestamp) = %(year)s").format(t=_TABLES["usage_events"]),
    }

    _GROUP_BY_COLUMN_MAP: ClassVar[dict[str, sql.Composed]] = {
        "service": sql.SQL("rollup({t}.service)").format(t=_TABLES["usage_events"]),  # サービス合計を含む
        "year": sql.SQL("year").format(t=_TABLES["usage_events"]),
        "month": sql.SQL("month").format(t=_TABLES["usage_events"]),
    }

    def __init__(
        self,
        tenant_id: UUID,
        status: list[QuotaAllocationStatus],
        group_by: list[GroupBy],
        year: int | None = None,
    ) -> None:
        self.tenant_id = tenant_id
        self.status = status
        self.group_by = group_by
        self.year = year
        self.parameters: list[dict[str, str | list[str]]] = []

    def build(self) -> tuple[sql.Composed, dict[str, str | list[str]]]:
        query = sql.SQL("").join(
            [
                self._build_with_clause(),
                self._build_select_clause(),
                self._build_from_clause(),
                self._build_where_clause(),
                self._build_group_by_clause(),
                self._build_order_by_clause(),
            ],
        )

        return query, self._validate_parameters()

    def _validate_parameters(self) -> dict[str, str | list[str]]:
        # パラメータの重複チェック
        parameters_dict = {}
        for param in self.parameters:
            for key, value in param.items():
                if key in parameters_dict:
                    if parameters_dict[key] != value:
                        raise ValueError(f"Parameter '{key}' has conflicting values: {parameters_dict[key]} vs {value}")
                else:
                    parameters_dict[key] = value
        return parameters_dict

    def _build_with_clause(self) -> sql.Composed:
        self.parameters.append({"tenant_id": str(self.tenant_id)})
        if len(self.status) > 0:
            self.parameters.append({"status": [s.value for s in self.status]})
            query = sql.SQL(
                """--sql
                SELECT
                    qu.usage_event_id,
                    qu.unit
                FROM {quota_usages} AS qu
                INNER JOIN {quota_allocations} AS qa
                ON qu.quota_allocation_id = qa.id
                WHERE
                    qu.tenant_id = %(tenant_id)s
                    AND qu.quota_allocation_id IS NOT NULL
                    AND qa.status = ANY(%(status)s)
                """,
            ).format(
                quota_usages=self._TABLES["quota_usages"],
                quota_allocations=self._TABLES["quota_allocations"],
            )
        else:
            query = sql.SQL(
                """--sql
                SELECT
                    qu.usage_event_id,
                    qu.unit
                FROM {quota_usages} AS qu
                WHERE
                    qu.tenant_id = %(tenant_id)s
                    AND qu.quota_allocation_id IS NOT NULL
                """,
            ).format(
                quota_usages=self._TABLES["quota_usages"],
            )
        return sql.SQL(
            """--sql
            WITH {cte_name} AS (
                {query}
            )
            """,
        ).format(
            cte_name=self._TABLES["cte_quota_usages"],
            query=query,
        )

    def _build_select_clause(self) -> sql.Composed:
        columns = [
            sql.SQL(
                """--sql
                {col} AS {alias}""",
            ).format(
                col=self._SELECT_COLUMN_MAP[key],
                alias=sql.Identifier(key),
            )
            for key in self.group_by
        ]
        columns.append(
            sql.SQL(
                """--sql
                {col} AS {alias}
                """,
            ).format(
                col=self._SELECT_COLUMN_MAP["used"],
                alias=sql.Identifier("used"),
            ),
        )
        return sql.SQL(
            """--sql
            SELECT
                {columns}
            """,
        ).format(columns=sql.SQL(",").join(columns))

    def _build_from_clause(self) -> sql.Composed:
        return sql.SQL(
            """--sql
            FROM {cte_name}
            LEFT JOIN {usage_events} ON {cte_name}.usage_event_id = {usage_events}.id
            """,
        ).format(
            cte_name=self._TABLES["cte_quota_usages"],
            usage_events=self._TABLES["usage_events"],
        )

    def _build_where_clause(self) -> sql.Composed:
        self.parameters.append({"tenant_id": str(self.tenant_id)})
        conditions = [self._WHERE_CONDITION_MAP["tenant_id"]]

        if self.year:
            self.parameters.append({"year": str(self.year)})
            conditions.append(self._WHERE_CONDITION_MAP["year"])

        return sql.SQL(
            """--sql
            WHERE
            {conditions}
            """,
        ).format(
            conditions=sql.SQL(
                """--sql
                AND """,
            ).join(conditions),
        )

    def _build_group_by_clause(self) -> sql.Composed:
        columns = [self._GROUP_BY_COLUMN_MAP[key] for key in self.group_by]
        return sql.SQL(
            """--sql
            GROUP BY {columns}
            """,
        ).format(columns=sql.SQL(", ").join(columns))

    def _build_order_by_clause(self) -> sql.Composed:
        order_keys = sorted(
            self.group_by,
            key=lambda x: ["year", "month", "service"].index(x),
        )
        columns = [sql.Identifier(key) for key in order_keys]
        return sql.SQL(
            """--sql
            ORDER BY {columns}
            """,
        ).format(columns=sql.SQL(", ").join(columns))
