from uuid import UUID

from psycopg import Connection
from psycopg.errors import UniqueViolation
from psycopg.rows import class_row
from src.__types.errors import ResourceDuplicatedError, ResourceNotFoundError
from src.__types.index import Tenant, User, UserWriteAttribute

DISPLAY_ID_UNIQUE_CONSTRAINT = "uq_users_tenant_id_display_id"
EMAIL_UNIQUE_CONSTRAINT = "uq_users_tenant_id_email"


class UserRepository:
    def __init__(self, tenant: Tenant, connection: Connection) -> None:
        self.tenant = tenant
        self.connection = connection

    def count(self) -> int:
        """Userの総数を取得する。Noneの場合は0を返す。"""
        with self.connection.cursor() as cur:
            cur.execute(
                """--sql
                SELECT COUNT(*) FROM users WHERE tenant_id = %(tenant_id)s
                """,
                {"tenant_id": self.tenant.id},
            )
            if count := cur.fetchone():
                return count[0]
            return 0

    def find(self, limit: int, offset: int) -> list[User]:
        """指定されたページのUserのリストを取得する。

        更新日時の降順、表示IDの昇順でソートして返す。
        """
        with self.connection.cursor(row_factory=class_row(User)) as cur:
            cur.execute(
                """--sql
                SELECT
                    id,
                    display_id,
                    display_name,
                    email
                FROM users
                WHERE
                    tenant_id = %(tenant_id)s
                ORDER BY updated_at DESC, display_id ASC
                LIMIT %(limit)s
                OFFSET %(offset)s
                """,
                {
                    "tenant_id": self.tenant.id,
                    "limit": limit,
                    "offset": offset,
                },
            )
            return list(cur.fetchall())

    def find_all(self) -> list[User]:
        """全てのUserのリストを取得する。

        更新日時の降順、表示IDの昇順でソートして返す。
        """
        with self.connection.cursor(row_factory=class_row(User)) as cur:
            cur.execute(
                """--sql
                SELECT
                    id,
                    display_id,
                    display_name,
                    email
                FROM users
                WHERE
                    tenant_id = %(tenant_id)s
                ORDER BY updated_at DESC, display_id ASC
                """,
                {
                    "tenant_id": self.tenant.id,
                },
            )
            return list(cur.fetchall())

    def find_duplicates(self, display_ids: list[str], emails: list[str]) -> list[User]:
        """指定されたdisplay_idまたはemailに一致する既存ユーザーを返す。"""
        with self.connection.cursor(row_factory=class_row(User)) as cur:
            cur.execute(
                """--sql
                SELECT
                    id,
                    display_id,
                    display_name,
                    email
                FROM users
                WHERE
                    tenant_id = %(tenant_id)s
                    AND (display_id = ANY(%(display_ids)s) OR email = ANY(%(emails)s))
                """,
                {
                    "tenant_id": self.tenant.id,
                    "display_ids": display_ids,
                    "emails": emails,
                },
            )
            return list(cur.fetchall())

    def get(self, user_id: UUID) -> User:
        """一意のUserを取得する。"""
        with self.connection.cursor(row_factory=class_row(User)) as cur:
            cur.execute(
                """--sql
                SELECT
                    id,
                    display_id,
                    display_name,
                    email
                FROM users
                WHERE
                    id = %(user_id)s
                    AND tenant_id = %(tenant_id)s
                """,
                {
                    "user_id": user_id,
                    "tenant_id": self.tenant.id,
                },
            )

            if user := cur.fetchone():
                return user
            raise ResourceNotFoundError(
                f"User with ID '{user_id}' for tenant ID {self.tenant.id} does not exist.",
            )

    def create(self, display_id: str, display_name: str, email: str) -> User:
        """ユーザーを作成する。"""
        with self.connection.cursor(row_factory=class_row(User)) as cur:
            try:
                cur.execute(
                    """--sql
                    INSERT INTO users (
                        tenant_id,
                        display_id,
                        display_name,
                        email
                    ) VALUES (
                        %(tenant_id)s,
                        %(display_id)s,
                        %(display_name)s,
                        %(email)s
                    )
                    RETURNING id, display_id, display_name, email
                    """,
                    {
                        "tenant_id": self.tenant.id,
                        "display_id": display_id,
                        "display_name": display_name,
                        "email": email,
                    },
                )
                if user := cur.fetchone():
                    return user

                raise RuntimeError("Failed to create user due to unknown database error.")
            except UniqueViolation as e:
                duplicated_field = self._get_duplicated_field(e)
                message_field = duplicated_field or "a field"

                raise ResourceDuplicatedError(
                    f"User with {message_field} for tenant ID {self.tenant.id} already exists.",
                    duplicated_field=duplicated_field,
                ) from e

    def bulk_create(self, users: list[UserWriteAttribute]) -> list[User]:
        """複数のユーザーを作成する。"""
        with self.connection.cursor(row_factory=class_row(User)) as cur:
            try:
                cur.execute(
                    """--sql
                    INSERT INTO users (
                        tenant_id,
                        display_id,
                        display_name,
                        email
                    ) SELECT
                        %(tenant_id)s,
                        u.display_id,
                        u.display_name,
                        u.email
                    FROM UNNEST(
                        %(display_ids)s::text[],
                        %(display_names)s::text[],
                        %(emails)s::text[]
                    ) AS u(display_id, display_name, email)
                    RETURNING id, display_id, display_name, email
                    """,
                    {
                        "tenant_id": self.tenant.id,
                        "display_ids": [u.display_id for u in users],
                        "display_names": [u.display_name for u in users],
                        "emails": [u.email for u in users],
                    },
                )
                return list(cur.fetchall())
            except UniqueViolation as e:
                duplicated_field = self._get_duplicated_field(e)
                message_field = duplicated_field or "a field"

                raise ResourceDuplicatedError(
                    f"User with {message_field} for tenant ID {self.tenant.id} already exists.",
                    duplicated_field=duplicated_field,
                ) from e

    def update(self, user_id: UUID, display_id: str, display_name: str, email: str | None) -> User:
        """ユーザーを更新する。"""
        with self.connection.cursor(row_factory=class_row(User)) as cur:
            try:
                cur.execute(
                    """--sql
                    UPDATE users
                    SET
                        display_id = %(display_id)s,
                        display_name = %(display_name)s,
                        email = %(email)s,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE
                        id = %(user_id)s
                        AND tenant_id = %(tenant_id)s
                    RETURNING id, display_id, display_name, email
                    """,
                    {
                        "user_id": user_id,
                        "tenant_id": self.tenant.id,
                        "display_id": display_id,
                        "display_name": display_name,
                        "email": email,
                    },
                )
                if user := cur.fetchone():
                    return user
                raise ResourceNotFoundError(
                    f"User with ID '{user_id}' for tenant ID {self.tenant.id} does not exist.",
                )
            except UniqueViolation as e:
                duplicated_field = self._get_duplicated_field(e)
                message_field = duplicated_field or "a field"

                raise ResourceDuplicatedError(
                    f"User with {message_field} for tenant ID {self.tenant.id} already exists.",
                    duplicated_field=duplicated_field,
                ) from e

    def bulk_delete(self, user_ids: list[UUID]) -> None:
        """複数のユーザーを削除する。"""
        with self.connection.cursor() as cur:
            cur.execute(
                """--sql
                DELETE FROM users
                WHERE
                    id = ANY(%(user_ids)s)
                    AND tenant_id = %(tenant_id)s
                """,
                {
                    "user_ids": user_ids,
                    "tenant_id": self.tenant.id,
                },
            )

    def _get_duplicated_field(self, error: UniqueViolation) -> str | None:
        """UniqueViolationエラーから重複しているフィールドと値を取得する。

        指定の制約名に該当しない場合はNoneを返す。
        """
        constraint_name = error.diag.constraint_name
        if constraint_name == DISPLAY_ID_UNIQUE_CONSTRAINT:
            return "display_id"
        if constraint_name == EMAIL_UNIQUE_CONSTRAINT:
            return "email"
        return None
