from sqlalchemy import and_, case, or_
from sqlalchemy.orm import aliased
from sqlalchemy.sql import func
from db import db
from models.campaign import Campaign, CampaignTypeGroup, CampaignTypes, CampaignPlatforms, campaign_platforms_links


class ProjectMarketingMixRepository:
    VIRTUAL_LINKED_DIGITAL_CATEGORY_ID = 999
    VIRTUAL_LINKED_DIGITAL_CATEGORY_NAME = "Linked Digital"
    OTHER_CATEGORY_ID = 4
    DIGITAL_CATEGORY_ID = 1

    def get_marketing_mix_data_for_project_id(self, project_id: int):
        other_category = aliased(CampaignTypeGroup)
        digital_category_condition = and_(
            Campaign.source.in_(["facebook", "google"]), CampaignTypeGroup.id == self.DIGITAL_CATEGORY_ID
        )
        groups_data = (
            db.session.query(
                case(
                    [
                        (digital_category_condition, self.VIRTUAL_LINKED_DIGITAL_CATEGORY_ID),
                        (Campaign.type_id.is_(None), other_category.id),
                    ],
                    else_=CampaignTypeGroup.id,
                ).label("group_id"),
                case(
                    [
                        (digital_category_condition, self.VIRTUAL_LINKED_DIGITAL_CATEGORY_NAME),
                        (Campaign.type_id.is_(None), other_category.name),
                    ],
                    else_=CampaignTypeGroup.name,
                ).label("group_name"),
                Campaign.id.label("campaign_id"),
            )
            .select_from(Campaign)
            .outerjoin(CampaignTypes, CampaignTypes.id == Campaign.type_id)
            .outerjoin(CampaignTypeGroup, CampaignTypeGroup.id == CampaignTypes.group_id)
            .join(other_category, other_category.id == self.OTHER_CATEGORY_ID)
            .filter(Campaign.project_id == project_id)
            .subquery()
        )

        campaign_platforms_data = (
            db.session.query(
                CampaignPlatforms.id.label("platform_id"),
                CampaignPlatforms.name.label("platform_name"),
                Campaign.id.label("campaign_id"),
            )
            .select_from(Campaign)
            .outerjoin(campaign_platforms_links, campaign_platforms_links.c.campaign_id == Campaign.id)
            .outerjoin(CampaignPlatforms, CampaignPlatforms.id == campaign_platforms_links.c.platform_id)
            .filter(Campaign.project_id == project_id)
            .subquery()
        )

        campaign_platforms_count_data = (
            db.session.query(
                case([(func.count(CampaignPlatforms.id) > 0, func.count(CampaignPlatforms.id))], else_=1).label(
                    "count"
                ),
                Campaign.id.label("campaign_id"),
            )
            .select_from(Campaign)
            .outerjoin(campaign_platforms_links, campaign_platforms_links.c.campaign_id == Campaign.id)
            .outerjoin(CampaignPlatforms, CampaignPlatforms.id == campaign_platforms_links.c.platform_id)
            .filter(Campaign.project_id == project_id)
            .group_by(Campaign.id)
            .subquery()
        )

        return (
            db.session.query(
                groups_data.c.group_id.label("category_id"),
                groups_data.c.group_name.label("category_name"),
                campaign_platforms_data.c.platform_id.label("platform_id"),
                campaign_platforms_data.c.platform_name.label("platform_name"),
                func.sum(
                    func.coalesce(
                        func.nullif(Campaign.budget_spend / campaign_platforms_count_data.c.count, 0),
                        Campaign.planned_budget / campaign_platforms_count_data.c.count,
                    )
                ).label("budget"),
            )
            .select_from(Campaign)
            .join(groups_data, groups_data.c.campaign_id == Campaign.id)
            .join(campaign_platforms_data, campaign_platforms_data.c.campaign_id == Campaign.id)
            .join(campaign_platforms_count_data, campaign_platforms_count_data.c.campaign_id == Campaign.id)
            .filter(Campaign.project_id == project_id)
            .filter(Campaign.is_deleted.is_(False))
            .filter(or_(Campaign.budget_spend > 0, Campaign.planned_budget > 0))
            .group_by(
                groups_data.c.group_id,
                groups_data.c.group_name,
                campaign_platforms_data.c.platform_id,
                campaign_platforms_data.c.platform_name,
            )
            .order_by(groups_data.c.group_id)
            .all()
        )