from typing import List

from sqlalchemy import exc, and_
from sqlalchemy.orm import (
    joinedload,
    load_only,
    contains_eager,
)

from app.extensions.database import session
from app.extensions.utils.log_helper import logger_
from app.extensions.utils.time_helper import get_server_timestamp
from app.persistence.model import (
    PostCategoryModel,
    PostModel,
    PostAttachmentModel,
    BannedWordModel,
    PostLikeStatusModel,
    CommentModel,
    CommentLikeStatusModel,
)
from core.domains.community.dto.community_dto import (
    CreatePostDto,
    CreatePostAttachmentDto,
    UpdatePostDto,
    UpdatePostLikeStatusDto,
    CreateCommentDto,
    UpdateCommentDto,
    UpdateCommentLikeStatusDto,
)
from core.domains.community.entity.community_entity import (
    PostCategoryEntity,
    PostEntity,
    FeedEntity,
    FeedCommentEntity,
    NoticeEntity,
)
from core.domains.community.enum.community_enum import (
    PostCategoryEnum,
    LastUserActionEnum,
)
from core.exceptions import NotUniqueErrorException

logger = logger_.getLogger(__name__)


class CommunityRepository:
    def commit_transaction(self) -> None:
        try:
            session.commit()
        except exc.IntegrityError as e:
            logger.error(f"[CommunityRepository][commit_transaction] error : {e}")
            session.rollback()
            raise NotUniqueErrorException

    def get_post_by_id(self, id: int) -> PostEntity | None:
        query = (
            session.query(PostModel)
            .options(joinedload(PostModel.post_category, innerjoin=True))
            .options(joinedload(PostModel.comments))
            .options(joinedload(PostModel.post_like_statuses))
            .join(
                PostAttachmentModel,
                PostAttachmentModel.post_id == PostModel.id,
                isouter=True,
            )
            .filter(PostModel.id == id)
            .order_by(PostAttachmentModel.id)
        )
        query_set = query.first()

        if not query_set:
            return None

        return query_set.to_entity()

    def get_post_categories(self) -> List[PostCategoryEntity]:
        post_categories = [
            PostCategoryEnum.NOTICE.value,
        ]

        query_set = (
            session.query(PostCategoryModel)
            .filter_by(is_available=True,)
            .filter(~PostCategoryModel.id.in_(post_categories))
            .order_by(PostCategoryModel.id)
            .all()
        )
        return [result.to_entity() for result in query_set]

    def get_banned_word(self) -> List:
        query = session.query(BannedWordModel)
        banned_words = query.options(load_only("word")).all()

        return [banned_word.word for banned_word in banned_words]

    def create_post(self, dto: CreatePostDto) -> int | None:
        try:
            model = PostModel(
                user_id=dto.user_id,
                post_category_id=dto.post_category_id,
                title=dto.title,
                body=dto.body,
                last_user_action=LastUserActionEnum.CREATE.value,
                last_user_action_at=get_server_timestamp(),
            )
            session.add(model)
            session.flush()

            return model.id
        except exc.IntegrityError as e:
            logger.error(
                f"[CommunityRepository][create_post] user_id : {dto.user_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def create_post_attachment(self, dto: CreatePostAttachmentDto) -> None:
        try:
            model = PostAttachmentModel(
                post_id=dto.post_id,
                uuid=dto.uuid,
                file_name=dto.file_name,
                path=dto.path,
                extension=dto.extension,
            )
            session.add(model)
            session.flush()
        except exc.IntegrityError as e:
            logger.error(
                f"[CommunityRepository][create_post_attachment] post_id : {dto.post_id} error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def update_post(self, dto: UpdatePostDto) -> None:
        try:
            session.query(PostModel).filter_by(id=dto.post_id).update(
                {
                    "post_category_id": dto.post_category_id,
                    "body": dto.body,
                    "last_user_action": LastUserActionEnum.UPDATE.value,
                    "last_user_action_at": get_server_timestamp(),
                }
            )
            session.flush()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][update_post] post_id : {dto.post_id} error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def delete_post_attachments(self, delete_files: List[int]) -> None:
        try:
            session.query(PostAttachmentModel).filter(
                PostAttachmentModel.id.in_(delete_files)
            ).update(
                {"is_available": False,}
            )
            session.flush()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][delete_post_attachments] delete_files : {delete_files} error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def delete_post(self, post_id: int) -> None:
        try:
            session.query(PostModel).filter_by(id=post_id).update(
                {
                    "is_deleted": True,
                    "last_user_action": LastUserActionEnum.DELETE.value,
                    "last_user_action_at": get_server_timestamp(),
                }
            )
            session.commit()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][delete_post] post_id : {post_id} error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def is_post_like_status(self, dto: UpdatePostLikeStatusDto) -> bool:
        query = session.query(
            PostLikeStatusModel.query.filter(
                PostLikeStatusModel.post_id == dto.post_id,
                PostLikeStatusModel.user_id == dto.user_id,
            ).exists()
        )
        query_set = query.scalar()
        return query_set

    def create_post_like_status(self, dto: UpdatePostLikeStatusDto) -> None:
        try:
            model = PostLikeStatusModel(
                post_id=dto.post_id, user_id=dto.user_id, is_liked=dto.is_liked,
            )
            session.add(model)
            session.commit()
        except exc.IntegrityError as e:
            logger.error(
                f"[CommunityRepository][create_post_like_status] post_id : {dto.post_id}, user_id : {dto.user_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def update_post_like_status(self, dto: UpdatePostLikeStatusDto) -> None:
        try:
            session.query(PostLikeStatusModel).filter_by(
                post_id=dto.post_id, user_id=dto.user_id
            ).update({"is_liked": dto.is_liked})
            session.commit()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][update_post_like_status] post_id : {dto.post_id}, user_id : {dto.user_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def plus_like_count_for_post(self, dto: UpdatePostLikeStatusDto) -> None:
        try:
            session.query(PostModel).filter_by(id=dto.post_id).update(
                {"like_count": PostModel.like_count + 1}
            )
            session.commit()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][plus_like_count_for_post] post_id : {dto.post_id}, user_id : {dto.user_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def minus_like_count_for_post(self, dto: UpdatePostLikeStatusDto) -> None:
        try:
            session.query(PostModel).filter_by(id=dto.post_id).update(
                {"like_count": PostModel.like_count - 1}
            )
            session.commit()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][minus_like_count_for_post] post_id : {dto.post_id}, user_id : {dto.user_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def get_comment_by_id(self, id: int) -> PostEntity | None:
        query = (
            session.query(CommentModel)
            .options(joinedload(CommentModel.comment_like_statuses))
            .filter_by(id=id)
        )
        query_set = query.first()

        if not query_set:
            return None

        return query_set.to_entity()

    def get_comment_by_post_id(
        self, user_id: int, post_id: int, writer_id: int
    ) -> List[FeedCommentEntity] | None:
        query = (
            session.query(CommentModel)
            .join(
                CommentLikeStatusModel,
                and_(
                    CommentModel.id == CommentLikeStatusModel.comment_id,
                    CommentLikeStatusModel.user_id == user_id,
                    CommentLikeStatusModel.is_liked == True,
                ),
                isouter=True,
            )
            .options(contains_eager(CommentModel.comment_like_statuses))
            .filter(CommentModel.post_id == post_id)
            .order_by(CommentModel.id)
        )
        query_set = query.all()

        if not query_set:
            return None

        return [
            result.to_feed_comment_entity(writer_id=writer_id, reader_id=user_id)
            for result in query_set
        ]

    def create_comment(self, dto: CreateCommentDto) -> int:
        try:
            model = CommentModel(
                post_id=dto.post_id,
                user_id=dto.user_id,
                body=dto.body,
                last_user_action=LastUserActionEnum.CREATE.value,
                last_user_action_at=get_server_timestamp(),
            )
            session.add(model)
            session.commit()

            return model.id
        except exc.IntegrityError as e:
            logger.error(
                f"[CommunityRepository][create_comment] user_id : {dto.user_id}, post_id : {dto.post_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def update_comment(self, dto: UpdateCommentDto) -> None:
        try:
            session.query(CommentModel).filter_by(id=dto.comment_id).update(
                {
                    "body": dto.body,
                    "last_user_action": LastUserActionEnum.UPDATE.value,
                    "last_user_action_at": get_server_timestamp(),
                }
            )
            session.commit()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][update_comment] comment_id : {dto.comment_id} error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def delete_comment(self, comment_id: int) -> None:
        try:
            session.query(CommentModel).filter_by(id=comment_id).update(
                {
                    "is_deleted": True,
                    "last_user_action": LastUserActionEnum.DELETE.value,
                    "last_user_action_at": get_server_timestamp(),
                }
            )
            session.commit()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][delete_comment] comment_id : {comment_id} error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def is_comment_like_status(self, dto: UpdateCommentLikeStatusDto) -> bool:
        query = session.query(
            CommentLikeStatusModel.query.filter(
                CommentLikeStatusModel.comment_id == dto.comment_id,
                CommentLikeStatusModel.user_id == dto.user_id,
            ).exists()
        )
        query_set = query.scalar()
        return query_set

    def create_comment_like_status(self, dto: UpdateCommentLikeStatusDto) -> None:
        try:
            model = CommentLikeStatusModel(
                comment_id=dto.comment_id, user_id=dto.user_id, is_liked=dto.is_liked,
            )
            session.add(model)
            session.commit()
        except exc.IntegrityError as e:
            logger.error(
                f"[CommunityRepository][create_comment_like_status] comment_id : {dto.comment_id}, user_id : {dto.user_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def update_comment_like_status(self, dto: UpdateCommentLikeStatusDto) -> None:
        try:
            session.query(CommentLikeStatusModel).filter_by(
                comment_id=dto.comment_id, user_id=dto.user_id
            ).update({"is_liked": dto.is_liked})
            session.commit()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][update_comment_like_status] comment_id : {dto.comment_id}, user_id : {dto.user_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def plus_like_count_for_comment(self, dto: UpdateCommentLikeStatusDto) -> None:
        try:
            session.query(CommentModel).filter_by(id=dto.comment_id).update(
                {"like_count": CommentModel.like_count + 1}
            )
            session.commit()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][plus_like_count_for_comment] comment_id : {dto.comment_id}, user_id : {dto.user_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def minus_like_count_for_comment(self, dto: UpdateCommentLikeStatusDto) -> None:
        try:
            session.query(CommentModel).filter_by(id=dto.comment_id).update(
                {"like_count": CommentModel.like_count - 1}
            )
            session.commit()
        except exc.IntegrityError as e:
            session.rollback()
            logger.error(
                f"[CommunityRepository][minus_like_count_for_comment] comment_id : {dto.comment_id}, user_id : {dto.user_id}, error : {e}"
            )
            session.rollback()
            raise NotUniqueErrorException

    def get_feeds(
        self, post_category_id: int, user_id: int, previous_id: int | None
    ) -> List[FeedEntity] | None:
        offset = 10
        view_limit = 2
        current_loop_cnt = 0
        max_loop_cnt = 3

        filters = list()
        previous_id_filters = list()
        filters.append(PostModel.is_deleted == False)
        filters.append(PostModel.post_category_id != PostCategoryEnum.NOTICE.value)

        if post_category_id != PostCategoryEnum.ALL.value:
            filters.append(PostModel.post_category_id == post_category_id)
            offset = 100

        while True:
            current_loop_cnt += 1

            if previous_id:
                previous_id_filters = [
                    # previous_id = 1 -> 1보다 크고, 3보다 작은 post_id
                    and_(
                        PostModel.id < previous_id, PostModel.id >= previous_id - offset
                    )
                ]

            query = (
                session.query(PostModel)
                .options(joinedload(PostModel.post_category, innerjoin=True))
                .join(
                    PostAttachmentModel,
                    and_(
                        PostAttachmentModel.post_id == PostModel.id,
                        PostAttachmentModel.is_available == True,
                    ),
                    isouter=True,
                )
                .options(contains_eager(PostModel.post_attachments))
                .join(
                    PostLikeStatusModel,
                    and_(
                        PostLikeStatusModel.post_id == PostModel.id,
                        PostLikeStatusModel.user_id == user_id,
                        PostLikeStatusModel.is_liked == True,
                    ),
                    isouter=True,
                )
                .options(contains_eager(PostModel.post_like_statuses))
                .options(joinedload(PostModel.user, innerjoin=True))
                .options(joinedload("user.user_profile"))
                .options(joinedload("user.plant_profile"))
                .join(
                    CommentModel,
                    and_(
                        PostModel.id == CommentModel.post_id,
                        CommentModel.is_deleted == False,
                    ),
                    isouter=True,
                )
                .options(contains_eager(PostModel.comments))
                .join(
                    CommentLikeStatusModel,
                    and_(
                        CommentModel.id == CommentLikeStatusModel.comment_id,
                        CommentLikeStatusModel.user_id == user_id,
                        CommentLikeStatusModel.is_liked == True,
                    ),
                    isouter=True,
                )
                .options(contains_eager("comments.comment_like_statuses"))
                .options(joinedload("comments.user.plant_profile"))
                .filter(*filters)
                .filter(*previous_id_filters)
                .order_by(PostModel.id.desc(), PostAttachmentModel.id, CommentModel.id)
                .limit(1000)
            )

            query_set = query.all()

            if current_loop_cnt >= max_loop_cnt:
                break

            if len(query_set) < view_limit:
                offset += offset
                continue
            else:
                break

        if not query_set:
            return None

        results = list()
        for index, query in enumerate(query_set):
            if index >= view_limit:
                break

            results.append(query.to_feed_entity(reader_id=user_id))

        return results

    def get_notices(
        self, post_category_id: int, user_id: int, previous_id: int | None
    ) -> List[NoticeEntity] | None:
        offset = 10
        view_limit = 2
        current_loop_cnt = 0
        max_loop_cnt = 3

        filters = list()
        previous_id_filters = list()
        filters.append(PostModel.is_deleted == False)
        filters.append(PostModel.post_category_id == post_category_id)

        while True:
            current_loop_cnt += 1

            if previous_id:
                previous_id_filters = [
                    # previous_id = 1 -> 1보다 크고, 3보다 작은 post_id
                    and_(
                        PostModel.id < previous_id, PostModel.id >= previous_id - offset
                    )
                ]

            query = (
                session.query(PostModel)
                .options(joinedload(PostModel.post_category, innerjoin=True))
                .join(
                    PostAttachmentModel,
                    and_(
                        PostAttachmentModel.post_id == PostModel.id,
                        PostAttachmentModel.is_available == True,
                    ),
                    isouter=True,
                )
                .options(contains_eager(PostModel.post_attachments))
                .join(
                    PostLikeStatusModel,
                    and_(
                        PostLikeStatusModel.post_id == PostModel.id,
                        PostLikeStatusModel.user_id == user_id,
                        PostLikeStatusModel.is_liked == True,
                    ),
                    isouter=True,
                )
                .options(contains_eager(PostModel.post_like_statuses))
                .options(joinedload(PostModel.user, innerjoin=True))
                .options(joinedload("user.user_profile"))
                .options(joinedload("user.plant_profile"))
                .join(
                    CommentModel,
                    and_(
                        PostModel.id == CommentModel.post_id,
                        CommentModel.is_deleted == False,
                    ),
                    isouter=True,
                )
                .options(contains_eager(PostModel.comments))
                .join(
                    CommentLikeStatusModel,
                    and_(
                        CommentModel.id == CommentLikeStatusModel.comment_id,
                        CommentLikeStatusModel.user_id == user_id,
                        CommentLikeStatusModel.is_liked == True,
                    ),
                    isouter=True,
                )
                .options(contains_eager("comments.comment_like_statuses"))
                .options(joinedload("comments.user.plant_profile"))
                .filter(*filters)
                .filter(*previous_id_filters)
                .order_by(PostModel.id.desc(), PostAttachmentModel.id, CommentModel.id)
                .limit(1000)
            )

            query_set = query.all()

            if current_loop_cnt >= max_loop_cnt:
                break

            if len(query_set) < view_limit:
                offset += offset
                continue
            else:
                break

        if not query_set:
            return None

        results = list()
        for index, query in enumerate(query_set):
            if index >= view_limit:
                break

            results.append(query.to_notice_entity())

        return results

    def get_push_feed(self, post_id: int, user_id: int) -> List[FeedEntity] | None:
        view_limit = 1

        filters = list()
        filters.append(PostModel.is_deleted == False)
        filters.append(PostModel.id == post_id)

        query = (
            session.query(PostModel)
            .options(joinedload(PostModel.post_category, innerjoin=True))
            .join(
                PostAttachmentModel,
                and_(
                    PostAttachmentModel.post_id == PostModel.id,
                    PostAttachmentModel.is_available == True,
                ),
                isouter=True,
            )
            .options(contains_eager(PostModel.post_attachments))
            .join(
                PostLikeStatusModel,
                and_(
                    PostLikeStatusModel.post_id == PostModel.id,
                    PostLikeStatusModel.user_id == user_id,
                    PostLikeStatusModel.is_liked == True,
                ),
                isouter=True,
            )
            .options(contains_eager(PostModel.post_like_statuses))
            .options(joinedload(PostModel.user, innerjoin=True))
            .options(joinedload("user.user_profile"))
            .options(joinedload("user.plant_profile"))
            .join(
                CommentModel,
                and_(
                    PostModel.id == CommentModel.post_id,
                    CommentModel.is_deleted == False,
                ),
                isouter=True,
            )
            .options(contains_eager(PostModel.comments))
            .join(
                CommentLikeStatusModel,
                and_(
                    CommentModel.id == CommentLikeStatusModel.comment_id,
                    CommentLikeStatusModel.user_id == user_id,
                    CommentLikeStatusModel.is_liked == True,
                ),
                isouter=True,
            )
            .options(contains_eager("comments.comment_like_statuses"))
            .options(joinedload("comments.user.plant_profile"))
            .filter(*filters)
            .order_by(PostModel.id.desc(), PostAttachmentModel.id, CommentModel.id)
            .limit(1000)
        )

        query_set = query.all()

        if not query_set:
            return None

        results = list()
        for index, query in enumerate(query_set):
            if index >= view_limit:
                break

            results.append(query.to_feed_entity(reader_id=user_id))

        return results
