from datetime import datetime, timedelta, timezone
import uuid
from typing import List, Optional

from flask import g
from sqlalchemy.orm import aliased, joinedload
from sqlalchemy import and_, func, case, or_

from db import db
from models import Project
from models.user import User
from models.campaign_approval import ApprovalRequest, ApprovalRequestCampaign
from models.campaign import Campaign
from models.user_project import UserProject, UserProjectRoles
from utils import session_utils
from handlers.campaign_status_handler import CampaignWorkflowStatus


class MediaPlanCampaignsRepository:

    def delete_media_plan_campaign(self, campaign: Campaign, media_plan_id: int, user_id: int, project_id: int):
        db.session.query(Campaign).filter_by(id=campaign.id).update(
            {"is_deleted": True, "media_plan_phase_id": None, "order_in_phase": None}
        )
        db.session.query(Project).filter_by(id=project_id).update({"edit_user_id": user_id})
        db.session.flush()

    def shift_campaigns_order(self, media_plan_phase_id, order_in_phase, is_deletion: bool):
        order_value = Campaign.order_in_phase - 1 if is_deletion else Campaign.order_in_phase + 1
        (
            db.session.query(Campaign)
            .filter(
                Campaign.media_plan_phase_id == media_plan_phase_id,
                Campaign.order_in_phase >= order_in_phase,
            )
            .update({Campaign.order_in_phase: order_value})
        )

    def get_manual_campaigns_in_phase(self, phase_id):
        return (
            db.session.query(Campaign)
            .filter(
                Campaign.media_plan_phase_id == phase_id,
                Campaign.is_deleted.is_(False),
            )
            .all()
        )

    def create_approve_request(self, media_plan_id, reviewer_id, message=None) -> ApprovalRequest:
        request = ApprovalRequest()
        request.media_plan_id = media_plan_id
        request.requester_id = g.user_id
        request.reviewer_id = reviewer_id
        if message is not None:
            request.message = message
        db.session.add(request)

        return request

    def delete_active_approval_request_campaigns_by_uuid(self, uuids: List[uuid.UUID]):
        (
            db.session.query(ApprovalRequestCampaign)
            .filter(
                ApprovalRequestCampaign.campaign_uuid.in_(uuids),
                ApprovalRequestCampaign.is_deleted.is_(False),
                ApprovalRequestCampaign.completed_at.is_(None)
            )
            .update({"is_deleted": True}, synchronize_session=False)
        )

    def create_approve_request_campaigns(self, request_id, campaigns_uuids: List) -> List[ApprovalRequestCampaign]:
        now = datetime.now(timezone.utc)
        expiration_date = now + timedelta(days=14)
        approval_request_campaigns = [
            ApprovalRequestCampaign(
                approval_request_id=request_id,
                campaign_uuid=_uuid,
                expiration_date=expiration_date
            )
             for _uuid in campaigns_uuids
        ]
        db.session.add_all(
            approval_request_campaigns
        )
        return approval_request_campaigns

    def get_campaigns_for_approval_requester_id(self, requester_id, reviewer_id, phase_id):
        return (
            db.session.query(Campaign)
            .join(ApprovalRequestCampaign, ApprovalRequestCampaign.campaign_uuid == Campaign.uuid)
            .join(ApprovalRequest, ApprovalRequestCampaign.approval_request_id == ApprovalRequest.id)
            .filter(
                and_(
                    Campaign.workflow_status == CampaignWorkflowStatus.PENDING_APPROVAL.value,
                    Campaign.media_plan_phase_id == phase_id,
                    ApprovalRequest.requester_id == requester_id,
                    ApprovalRequest.reviewer_id == reviewer_id,
                    ApprovalRequestCampaign.expiration_date > datetime.now(),
                    ApprovalRequestCampaign.is_deleted.is_(False),
                    ApprovalRequestCampaign.completed_at.is_(None),
                )
            )
            .all()
        )

    def get_requester_ids(self, media_plan_id: int, reviewer_id: int):
        return (
            db.session.query(ApprovalRequest.requester_id)
            .join(ApprovalRequestCampaign, ApprovalRequestCampaign.approval_request_id == ApprovalRequest.id)
            .filter(
                and_(
                    ApprovalRequest.reviewer_id == reviewer_id,
                    ApprovalRequest.media_plan_id == media_plan_id,
                    ApprovalRequestCampaign.expiration_date > datetime.now(),
                    ApprovalRequestCampaign.completed_at.is_(None),
                    ApprovalRequestCampaign.is_deleted.is_(False),
                )
            )
            .group_by(ApprovalRequest.requester_id)
            .all()
        )

    def get_campaigns_by_uuids_with_approval_data(self, campaign_uuids: List[str], project_id) -> List[Campaign]:
        return (
            db.session.query(Campaign)
            .filter(Campaign.uuid.in_(campaign_uuids))
            .filter(Campaign.project_id == project_id)
            .options(joinedload(Campaign.approval_request_campaigns))
            .all()
        )

    def get_approval_campaign_counts_by_user(self, media_plan_id, campaign_uuids, approval_request_campaign_ids):
        reviewer = aliased(User)
        approved_count = func.count(
            case([(and_(ApprovalRequestCampaign.is_approved.is_(True),
                        ApprovalRequestCampaign.campaign_uuid.in_(campaign_uuids)), 1)])
        ).label("approved_count")
        disapproved_count = func.count(
            case([(and_(ApprovalRequestCampaign.is_approved.is_(False),
                        ApprovalRequestCampaign.campaign_uuid.in_(campaign_uuids)), 1)])
        ).label("disapproved_count")
        not_completed_count = func.count(
            case([(and_(ApprovalRequestCampaign.completed_at.is_(None),
                        ApprovalRequestCampaign.campaign_uuid.notin_(campaign_uuids)), 1)])
        ).label("not_completed_count")
        return (
            db.session.query(
                User.email.label("requester_email"),
                User.name.label("requester_name"),
                reviewer.name.label("reviewer_name"),
                approved_count,
                disapproved_count,
                not_completed_count,
            )
            .select_from(ApprovalRequestCampaign)
            .join(ApprovalRequest, ApprovalRequestCampaign.approval_request)
            .join(User, ApprovalRequest.requester)
            .join(reviewer, reviewer.id == ApprovalRequest.reviewer_id)
            .filter(ApprovalRequestCampaign.id.in_(approval_request_campaign_ids))
            .having(or_(disapproved_count > 0, approved_count > 0))
            .group_by(User.id, reviewer.id)
            .all()
        )

    def add_user_approval_role(self, project_id: int, approver_id: int):
        already_approver = db.session.query(
            db.session.query(UserProject)
            .filter_by(user_id=approver_id, project_id=project_id, role=UserProjectRoles.APPROVER.value)
            .exists()
        ).scalar()
        if not already_approver:
            user_project = UserProject(
                project_id=project_id,
                user_id=approver_id,
                shared_by=g.user_id,
                role=UserProjectRoles.APPROVER.value
            )
            session_utils.session_add(user_project)

    def get_campaign_by_uuid_with_approval_data(self, campaign_uuid: str) -> Optional[Campaign]:
        return (
            db.session.query(Campaign)
            .filter(Campaign.uuid == campaign_uuid)
            .options(
                joinedload(Campaign.approval_request_campaigns).joinedload(ApprovalRequestCampaign.approval_request)
            ).one_or_none()
        )