from datetime import datetime
from typing import Optional, List
from sqlalchemy import and_
from db import db
from models.media_plan_phases import MediaPlanPhase
from models.campaign import Campaign
from models.campaign_approval import ApprovalRequestCampaign, ApprovalRequest


class MediaPlanPhasesRepository:
    def get_media_plan_phase_by_id(self, phase_id: int) -> Optional[MediaPlanPhase]:
        return (
            db.session.query(MediaPlanPhase)
            .filter(MediaPlanPhase.id == phase_id)
            .one_or_none()
        )

    def get_media_plan_phases(self, media_plan_id: int) -> List[MediaPlanPhase]:
        return (
            db.session.query(MediaPlanPhase)
            .filter(MediaPlanPhase.media_plan_id == media_plan_id)
            .order_by(MediaPlanPhase.order)
            .all()
        )

    def get_last_media_plan_phase(self, media_plan_id: int) -> MediaPlanPhase:
        return (
            db.session.query(MediaPlanPhase)
            .filter(MediaPlanPhase.media_plan_id == media_plan_id)
            .order_by(MediaPlanPhase.order.desc())
            .first()
        )

    def shift_phases(self, media_plan_id: int, start_order: int = 0, increase=True, shift_size: int = 1):
        order_value = MediaPlanPhase.order + shift_size if increase else MediaPlanPhase.order - shift_size
        (
            db.session.query(MediaPlanPhase)
            .filter(
                MediaPlanPhase.media_plan_id == media_plan_id,
                MediaPlanPhase.order >= start_order,
            )
            .update({MediaPlanPhase.order: order_value})
        )
        db.session.flush()

    def update_phase(self, phase_id, name: str) -> MediaPlanPhase:
        phase = self.get_media_plan_phase_by_id(phase_id)
        phase.name = name
        db.session.commit()
        return phase

    def add_new_phase(self, media_plan_id: int, name: str, order: int) -> MediaPlanPhase:
        phase = MediaPlanPhase(
            media_plan_id=media_plan_id, name=name, order=order
        )
        db.session.add(phase)
        return phase

    def delete_phase(self, phase_id: int):
        db.session.query(MediaPlanPhase).filter(MediaPlanPhase.id == phase_id).delete()

    def get_phases_for_approval_requester_id(self, media_plan_id, reviewer_id, requester_id):
        return (
            db.session.query(MediaPlanPhase)
            .join(Campaign, Campaign.media_plan_phase_id == MediaPlanPhase.id)
            .join(ApprovalRequestCampaign, Campaign.uuid == ApprovalRequestCampaign.campaign_uuid)
            .join(ApprovalRequest, ApprovalRequest.id == ApprovalRequestCampaign.approval_request_id)
            .filter(
                and_(
                    MediaPlanPhase.media_plan_id == media_plan_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 phase_exist(self, phase_id: int):
        return db.session.query(
            db.session.query(MediaPlanPhase)
            .filter(MediaPlanPhase.id == phase_id)
            .exists()
        ).scalar()