from datetime import date
from typing import List, Optional
from sqlalchemy import and_, func, extract, or_
from models.campaign import CampaignSourceType, CampaignPlatforms, campaign_platforms_links
from models.projects import ProjectCampaignStatus, Project
from db import db
from models.projects import ProjectEntityType
from models import (
    Artist,
    Label,
    ProjectCampaign,
    Campaign,
    MarketingAccount,
    ProjectTargetItem,
    Polymorphable,
    ProjectProductFamily,
)
from projects.schemas import ProjectListQueryParams
from shared.query_builders.projects_query_builder import DynamicFields
from shared.query_builders.unclaimed_projects_query_builder import UnclaimedProjectsQueryBuilder


class UnclaimedProjectsRepository:
    def get_projects(self, external_id: str) -> Optional[Project]:
        return (
            db.session.query(Project)
            .filter(Project.gras_project_code == external_id, Project.is_deleted.is_(False))
            .filter(Project.is_claimed.is_(False))
            .all()
        )

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

    def get_label_by_external_id(self, external_id: str) -> Label:
        return db.session.query(Label).filter(Label.rep_owner_key == external_id).one()

    def get_unclaimed_project_list(
        self, user_id: int, params: ProjectListQueryParams, artist_external_id: Optional[str] = None
    ) -> List[Project]:
        builder = UnclaimedProjectsQueryBuilder(user_id)
        builder.dynamic_fields = [DynamicFields.START_DATE, DynamicFields.TARGET_ITEMS]
        self.__apply_filters(builder, params, artist_external_id)
        fetched_projects = builder.items_query().all()
        return fetched_projects

    def get_all_unclaimed_project(
        self, artist_external_id: Optional[str] = None, use_pagination: Optional[bool] = True
    ) -> List[Project]:
        builder = UnclaimedProjectsQueryBuilder()
        builder.dynamic_fields = []
        if artist_external_id:
            builder.filtered_by_artist(artist_external_id, prs_only=True)
        return builder.only_unclaimed().items_query(use_pagination=use_pagination).all()

    def __apply_filters(
        self,
        builder: UnclaimedProjectsQueryBuilder,
        params: ProjectListQueryParams,
        artist_external_id: Optional[str] = None,
    ):
        if params.freeText is not None:
            builder.filtered_by_search(params.freeText)
        if params.type is not None:
            builder.filtered_by_type(params.type)
        if params.statuses is not None:
            builder.filter_by_statuses(params.statuses)
        if params.earliestStartDate is not None and params.latestEndDate is not None:
            builder.filtered_by_date_range(params.earliestStartDate, params.latestEndDate)
        if params.labels:
            builder.filtered_by_labels(params.labels)
        if artist_external_id:
            builder.filtered_by_artist(artist_external_id)

        (
            builder.only_accessible_projects()
            .only_unclaimed()
            .offset_by(params.offset)
            .limit_to(params.limit)
            .sort_by(params.sort)
        )

    def get_projects_with_artists(
        self, artists_ids: List[str], start_date: date, marketing_account_id: str
    ) -> List[Project]:
        sec_in_day = 60 * 60 * 24
        time_diff = func.trunc(
            (extract("epoch", start_date) - extract("epoch", Project.initial_start_date)) / sec_in_day
        )

        return (
            db.session.query(Project)
            .join(
                ProjectTargetItem,
                and_(ProjectTargetItem.project_id == Project.id, ProjectTargetItem.is_deleted.is_(False)),
            )
            .join(Polymorphable, Polymorphable.id == ProjectTargetItem.entity_id)
            .join(Artist, and_(Artist.id == ProjectTargetItem.entity_id))
            .join(Label, Project.label_id == Label.id)
            .join(
                MarketingAccount,
                and_(MarketingAccount.label_id == Label.id, MarketingAccount.external_id == str(marketing_account_id)),
            )
            .outerjoin(ProjectCampaign)
            .filter(
                and_(
                    Artist.external_id.in_(artists_ids),
                    or_(
                        ProjectCampaign.status != ProjectCampaignStatus.REJECTED.value, ProjectCampaign.status.is_(None)
                    ).self_group(),
                    time_diff > 0,
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                    Project.is_deleted.is_(False),
                    Project.is_claimed.is_(False),
                )
            )
            .order_by(time_diff.asc())
            .all()
        )

    def get_available_gras_projects_ids(self) -> List[str]:
        projects = (
            db.session.query(func.distinct(Project.gras_project_code).label("gras_project_code"))
            .outerjoin(ProjectProductFamily, ProjectProductFamily.project_id == Project.id)
            .filter(
                Project.is_deleted.is_(False),
                Project.gras_project_code.isnot(None),
                ProjectProductFamily.id.is_(None)
            )
            .all()
        )
        return [project.gras_project_code for project in projects]