from typing import Optional, List

import sqlalchemy

from db import db
from sqlalchemy import and_, func, distinct, cast
from models import (
    ArtistMomentType,
    RawPRSProjects,
    Artist,
    RawPRSPurchaseOrders,
    RawPRSBudgets,
    CampaignProvider,
    PRSPurchaseOrder,
    ArtistMoment,
    PRSProjectBudget,
    Campaign,
    User,
    Project,
    Gender,
    ProjectTargetItem,
    Polymorphable,
)
from models.raw_prs_purchase_orders_map import RawPRSPurchaseOrdersMap
from models.project_entity_type import ProjectEntityType, ProjectEntityAddType

PRS_USER_EMAIL = "prs@prs.com"


class PRSImportRepository:
    def get_moment_type(self, moment_type_id: int):
        return db.session.query(ArtistMomentType).filter(ArtistMomentType.id == moment_type_id).one()

    def get_project_by_id(self, project_id: int) -> Optional[Project]:
        return db.session.query(Project).filter(Project.id == project_id).one_or_none()

    def get_artist_from_prs_project(self, sub_project_cd):
        return (
            db.session.query(Artist)
            .join(RawPRSProjects, RawPRSProjects.sub_project_cd == sub_project_cd)
            .filter(Artist.external_id == func.concat("GRAS_", RawPRSProjects.recording_project_artist_num))
            .one()
        )

    def get_campaign_genders_by_ids(self, genders_ids: List[int]) -> List[Gender]:
        return db.session.query(Gender).filter(Gender.id.in_(genders_ids)).all()

    def get_raw_prs_purchase_order_count(self):
        return (
            db.session.query(
                func.distinct(
                    RawPRSPurchaseOrders.po_num,
                    RawPRSPurchaseOrders.budget_group,
                    RawPRSPurchaseOrders.budget_category,
                    func.trim(RawPRSPurchaseOrders.po_status),
                    func.trim(RawPRSPurchaseOrders.vendor_name),
                    func.trim(RawPRSPurchaseOrders.blanket),
                    func.trim(RawPRSPurchaseOrders.blanket_name),
                    RawPRSPurchaseOrders.blanket_po_id,
                    RawPRSPurchaseOrders.sub_project_cd,
                    func.trim(RawPRSPurchaseOrders.is_under_uncommited_blanket),
                )
            )
            .filter(RawPRSPurchaseOrders.po_status.notin_(["Failed", "Rejected", "Removed"]))
            .count()
        )

    def get_raw_prs_purchase_order(self, limit: int, offset: int):
        aggregated_subquery = (
            db.session.query(
                RawPRSPurchaseOrders.po_num.label("po_num"),
                RawPRSPurchaseOrders.budget_group.label("budget_group"),
                RawPRSPurchaseOrders.budget_category.label("budget_category"),
                func.min(RawPRSPurchaseOrders.po_created_on).label("po_created_on"),
                func.min(RawPRSPurchaseOrders.po_modified_on).label("po_modified_on"),
                func.trim(RawPRSPurchaseOrders.po_status).label("po_status"),
                func.trim(RawPRSPurchaseOrders.vendor_name).label("vendor_name"),
                func.trim(RawPRSPurchaseOrders.blanket).label("blanket"),
                func.trim(RawPRSPurchaseOrders.blanket_name).label("blanket_name"),
                RawPRSPurchaseOrders.blanket_po_id.label("blanket_po_id"),
                RawPRSPurchaseOrders.sub_project_cd.label("sub_project_cd"),
                func.trim(RawPRSPurchaseOrders.is_under_uncommited_blanket).label("is_under_uncommited_blanket"),
                func.sum(RawPRSPurchaseOrders.po_total).label("po_total"),
                func.sum(RawPRSPurchaseOrders.po_total_paid).label("po_total_paid"),
                func.STRING_AGG(distinct(RawPRSPurchaseOrders.item_desc), "\n").label("item_desc"),
                func.STRING_AGG(distinct(RawPRSPurchaseOrders.item_full_desc), "\n").label("item_full_desc"),
            )
            .select_from(RawPRSPurchaseOrders)
            .filter(RawPRSPurchaseOrders.po_status.notin_(["Failed", "Rejected", "Removed"]))
            .group_by(
                RawPRSPurchaseOrders.po_num,
                RawPRSPurchaseOrders.budget_group,
                RawPRSPurchaseOrders.budget_category,
                func.trim(RawPRSPurchaseOrders.po_status),
                func.trim(RawPRSPurchaseOrders.vendor_name),
                func.trim(RawPRSPurchaseOrders.blanket),
                func.trim(RawPRSPurchaseOrders.blanket_name),
                RawPRSPurchaseOrders.blanket_po_id,
                RawPRSPurchaseOrders.sub_project_cd,
                func.trim(RawPRSPurchaseOrders.is_under_uncommited_blanket),
            )
            .limit(limit)
            .offset(offset)
            .cte("aggregated_subquery")
        )

        return (
            db.session.query(
                PRSPurchaseOrder,
                cast(aggregated_subquery.c.po_num, sqlalchemy.String).label("po_num"),
                aggregated_subquery.c.budget_group.label("budget_group"),
                aggregated_subquery.c.budget_category.label("budget_category"),
                aggregated_subquery.c.po_total.label("po_total"),
                aggregated_subquery.c.po_total_paid.label("po_total_paid"),
                aggregated_subquery.c.item_desc.label("item_desc"),
                aggregated_subquery.c.item_full_desc.label("item_full_desc"),
                aggregated_subquery.c.po_created_on.label("po_created_on"),
                aggregated_subquery.c.po_modified_on.label("po_modified_on"),
                aggregated_subquery.c.po_status.label("po_status"),
                aggregated_subquery.c.vendor_name.label("vendor_name"),
                aggregated_subquery.c.blanket.label("blanket"),
                aggregated_subquery.c.blanket_name.label("blanket_name"),
                aggregated_subquery.c.blanket_po_id.label("blanket_po_id"),
                aggregated_subquery.c.sub_project_cd.label("sub_project_cd"),
                aggregated_subquery.c.is_under_uncommited_blanket.label("is_under_uncommited_blanket"),
                RawPRSPurchaseOrdersMap.mapping_entity_type.label("mapping_entity_type"),
                RawPRSPurchaseOrdersMap.campaign_taxonomy.label("campaign_taxonomy"),
                RawPRSPurchaseOrdersMap.moment_type_id.label("moment_type_id"),
                RawPRSPurchaseOrdersMap.moment_group_id.label("moment_group_id"),
                Project.id.label("project_id"),
                Artist.id.label("artist_id"),
                Artist.external_id.label("artist_external_id"),
                func.coalesce(CampaignProvider.id, 1).label("provider_id"),
            )
            .select_from(aggregated_subquery)
            .join(Project, aggregated_subquery.c.sub_project_cd == Project.prs_project_code)
            .outerjoin(
                CampaignProvider,
                func.lower(CampaignProvider.name) == func.lower(aggregated_subquery.c.vendor_name)
            )
            .outerjoin(
                PRSPurchaseOrder,
                and_(
                    PRSPurchaseOrder.po_number == cast(aggregated_subquery.c.po_num, sqlalchemy.String),
                    PRSPurchaseOrder.project_id == Project.id,
                    PRSPurchaseOrder.group_id == aggregated_subquery.c.budget_group,
                    PRSPurchaseOrder.category_id == aggregated_subquery.c.budget_category
                )
            )
            .join(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.project_id == Project.id,
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                    ProjectTargetItem.add_type == ProjectEntityAddType.LOCKED.value
                )
            )
            .join(Polymorphable, Polymorphable.id == ProjectTargetItem.entity_id)
            .join(Artist, Artist.id == Polymorphable.id)
            .outerjoin(
                RawPRSPurchaseOrdersMap,
                and_(
                    Project.label_id == RawPRSPurchaseOrdersMap.label_id,
                    aggregated_subquery.c.budget_group == RawPRSPurchaseOrdersMap.budget_group_id,
                    aggregated_subquery.c.budget_category == RawPRSPurchaseOrdersMap.budget_category_id,
                ),
            )
            .limit(limit)
            .offset(offset)
            .all()
        )

    def get_raw_prs_budgets(self):
        return (
            db.session.query(
                RawPRSBudgets.group_id.label("group_id"),
                RawPRSBudgets.form_id.label("form_id"),
                RawPRSBudgets.phase_id.label("phase_id"),
                RawPRSBudgets.category_id.label("category_id"),
                RawPRSBudgets.proposed_am.label("proposed_am"),
                RawPRSBudgets.sub_project_cd.label("sub_project_cd"),
                RawPRSBudgets.created_at.label("created_at"),
                Project.id.label("project_id"),
            )
            .join(Project, RawPRSBudgets.sub_project_cd == Project.prs_project_code)
            .all()
        )

    def get_new_providers(self) -> List[str]:
        return (
            db.session.query(distinct(RawPRSPurchaseOrders.vendor_name))
            .outerjoin(
                CampaignProvider,
                func.lower(CampaignProvider.name) == func.lower(RawPRSPurchaseOrders.vendor_name)
            )
            .filter(CampaignProvider.id.is_(None), RawPRSPurchaseOrders.vendor_name.isnot(None))
            .filter(RawPRSPurchaseOrders.po_status.notin_(["Failed", "Rejected", "Removed"]))
            .all()
        )

    def create_provider_with_name(self, name):
        return CampaignProvider(name=name, is_prs_vendor=True)

    def get_purchase_order(self, po_number, project_id, group_id, category_id):
        return (
            db.session.query(PRSPurchaseOrder)
            .filter_by(po_number=str(po_number), project_id=project_id, group_id=group_id, category_id=category_id)
            .one_or_none()
        )

    def get_moment_by_po_id(self, po_id: int):
        return db.session.query(ArtistMoment).filter(ArtistMoment.purchase_order_id == po_id).one_or_none()

    def get_moment_by_external_key(self, key: str):
        return db.session.query(ArtistMoment).filter(ArtistMoment.external_key == key).one_or_none()

    def get_budget_by_external_key(self, external_key: str):
        return db.session.query(PRSProjectBudget).filter(PRSProjectBudget.external_key == external_key).one_or_none()

    def get_campaign_by_po_id(self, po_id: int):
        return db.session.query(Campaign).filter(Campaign.purchase_order_id == po_id).one_or_none()

    def get_prs_user(self) -> User:
        return (
            db.session.query(User).filter(and_(User.email == PRS_USER_EMAIL, User.is_service_account.is_(True))).one()
        )