from sqlalchemy import and_, func
from sqlalchemy.orm import selectinload

from db import db
from models import ReleaseType
from models.campaign_territories import CampaignTerritory
from models.media_plan_phases import MediaPlanPhase
from models.campaign import Campaign
from models.projects import Project
from models.media_plans import MediaPlan

from media_plan.schemas import MediaPlanReleaseDetailsUpdate
from utils.exceptions import ValidationError


class MediaPlanRepository:
    def media_plan_exist(self, project_id: int, media_plan_id: int):
        return db.session.query(
            db.session.query(MediaPlan)
                .filter(MediaPlan.project_id == project_id, MediaPlan.id == media_plan_id)
                .exists()
        ).scalar()

    def project_have_media_plan(self, project_id: int):
        return db.session.query(
            db.session.query(MediaPlan)
                .filter(MediaPlan.project_id == project_id)
                .exists()
        ).scalar()

    def get_last_media_plan(self, project_id: int) -> MediaPlan:
        return (
            db.session.query(MediaPlan)
            .filter(MediaPlan.project_id == project_id)
            .order_by(MediaPlan.order.desc())
            .first()
        )

    def add_new_media_plan(self, project_id: int, name: str, order: int, create_user_id: int) -> MediaPlan:
        media_plan = MediaPlan(
            project_id=project_id, name=name, order=order, create_user_id=create_user_id
        )
        db.session.add(media_plan)
        return media_plan

    def shift_media_plan_order(self, media_plan_id: int, order: int, is_deletion: bool):
        order_value = MediaPlan.order - 1 if is_deletion else MediaPlan.order + 1
        (
            db.session.query(MediaPlan)
            .filter(
                MediaPlan.id == media_plan_id,
                MediaPlan.order >= order,
            )
            .update({MediaPlan.order: order_value})
        )

    def delete_media_plan(self, media_plan_id: int):
        db.session.query(MediaPlan).filter(MediaPlan.id == media_plan_id).delete()

    def delete_media_plan_phases(self, media_plan_id: int):
        db.session.query(MediaPlanPhase).filter(MediaPlanPhase.media_plan_id == media_plan_id).delete()

    def delete_media_campaigns(self, media_plan_id: int):
        (
            db.session.query(Campaign)
            .filter(Campaign.id.in_(
                db.session.query(Campaign.id)
                .join(
                    MediaPlanPhase,
                    and_(
                        Campaign.media_plan_phase_id == MediaPlanPhase.id,
                        MediaPlanPhase.media_plan_id == media_plan_id,
                    )
                )
                .all()
            ))
            .update(
                {Campaign.media_plan_phase_id: None, Campaign.is_deleted: True},
                synchronize_session=False
            )
        )

    def media_plan_count(self, project_id):
        return (
            db.session.query(func.count(MediaPlan.id))
            .filter(MediaPlan.project_id == project_id)
            .scalar()
        )

    def get_project_by_media_plan_id(self, media_plan_id: int):
        return (
            db.session.query(Project)
            .join(MediaPlan, and_(MediaPlan.project_id == Project.id, MediaPlan.id == media_plan_id))
            .one()
        )

    def get_media_plan_by_id(self, media_plan_id: int) -> MediaPlan:
        return db.session.query(MediaPlan).filter(MediaPlan.id == media_plan_id).one_or_none()

    def get_media_plan_with_all_data_by_id(self, media_plan_id: int):
        eager_load_campaigns = selectinload(MediaPlan.phases).selectinload(MediaPlanPhase.campaigns)
        return (
            db.session.query(MediaPlan)
            .filter(MediaPlan.id == media_plan_id)
            .options(
                eager_load_campaigns.selectinload(Campaign.territories).joinedload(CampaignTerritory.territory),
                eager_load_campaigns.joinedload(Campaign.ad_creative_links),
                eager_load_campaigns.joinedload(Campaign.platforms),
                eager_load_campaigns.joinedload(Campaign.placements),
                eager_load_campaigns.joinedload(Campaign.objective),
                eager_load_campaigns.joinedload(Campaign.provider),
                eager_load_campaigns.joinedload(Campaign.type),
                eager_load_campaigns.joinedload(Campaign.genders),
             )
            .one_or_none()
        )

    def get_media_plans_by_project_id(self, project_id: int):
        return (
            db.session.query(MediaPlan)
            .select_from(Project)
            .join(MediaPlan, MediaPlan.project_id == Project.id)
            .filter(Project.id == project_id)
            .order_by(MediaPlan.order)
            .all()
        )

    def update_media_plan(self, media_plan, name: str) -> MediaPlan:
        if name:
            media_plan.name = name
            db.session.commit()
        return media_plan


    def release_type_exist(self, release_type_id):
        return db.session.query(
            db.session.query(ReleaseType)
            .filter(ReleaseType.id == release_type_id)
            .exists()
        ).scalar()

    def update_release_details(self, media_plan_id, params: MediaPlanReleaseDetailsUpdate):
        media_plan = self.get_media_plan_by_id(media_plan_id)
        if params.name != "missing":
            media_plan.release_name = params.name
        if params.type != "missing":
            if params.type is not None and not self.release_type_exist(params.type):
                raise ValidationError(f"Release type with id {params.type} not exist")
            media_plan.release_type_id = params.type
        db.session.flush()
        return media_plan