from typing import Optional, List
from sqlalchemy import and_
from sqlalchemy.orm import joinedload, aliased, Query

from models.campaign_provider import CampaignProvider
from utils import sorting_utils
from db import db
from models import PRSPurchaseOrder, Project, Campaign, CampaignTypes
from purchase_orders.schemas import BlanketPurchaseOrdersListQueryParams


class ProjectsRepository:

    po_sort_map = {
        "id": (PRSPurchaseOrder.id,),
        "name": (PRSPurchaseOrder.name, PRSPurchaseOrder.id),
        "status": (PRSPurchaseOrder.status, PRSPurchaseOrder.name, PRSPurchaseOrder.id),
        "total": (PRSPurchaseOrder.total_amount, PRSPurchaseOrder.name, PRSPurchaseOrder.id),
        "paid": (PRSPurchaseOrder.paid_amount, PRSPurchaseOrder.name, PRSPurchaseOrder.id),
        "createdAt": (PRSPurchaseOrder.created_date, PRSPurchaseOrder.name, PRSPurchaseOrder.id),
        "modifiedAt": (PRSPurchaseOrder.last_modified_date, PRSPurchaseOrder.name, PRSPurchaseOrder.id),
    }

    def get_purchase_order_details(self, purchase_order_id: int) -> Optional[PRSPurchaseOrder]:
        return (
            db.session.query(PRSPurchaseOrder)
            .join(Project, PRSPurchaseOrder.project)
            .filter(PRSPurchaseOrder.id == purchase_order_id)
            .filter(Project.is_deleted.is_(False))
            .options(
                joinedload(PRSPurchaseOrder.campaigns).joinedload(Campaign.type).joinedload(CampaignTypes.group),
                joinedload(PRSPurchaseOrder.prs_budget_group),
                joinedload(PRSPurchaseOrder.prs_budget_category),
            )
            .one_or_none()
        )

    def get_providers(self, project_id: int) -> List[CampaignProvider]:
        return (
            db.session.query(CampaignProvider)
            .join(
                PRSPurchaseOrder,
                and_(CampaignProvider.id == PRSPurchaseOrder.provider_id, PRSPurchaseOrder.project_id == project_id),
            )
            .distinct()
            .order_by(CampaignProvider.name)
            .all()
        )

    def get_purchase_orders_list(
        self, filters: BlanketPurchaseOrdersListQueryParams, project_id: int
    ) -> (List[PRSPurchaseOrder], int):
        base_query = (
            db.session.query(PRSPurchaseOrder)
            .join(Project, PRSPurchaseOrder.project)
            .options(
                joinedload(PRSPurchaseOrder.campaigns).joinedload(Campaign.type).joinedload(CampaignTypes.group),
                joinedload(PRSPurchaseOrder.prs_budget_group),
                joinedload(PRSPurchaseOrder.prs_budget_category),
                joinedload(PRSPurchaseOrder.child_purchase_orders)
            )
            .filter(
                Project.is_deleted.is_(False),
                Project.id == project_id,
                PRSPurchaseOrder.parent_po_number.is_(None)
            )
        )

        base_query = self.__apply_filters(base_query, filters)
        sort_expression = sorting_utils.sorting_expression(self.po_sort_map, filters.sort)
        items = base_query.order_by(*sort_expression).limit(filters.limit).offset(filters.offset).all()
        total = base_query.count()
        return items, total

    def get_purchase_orders_blankets(
        self, filters: BlanketPurchaseOrdersListQueryParams, purchase_order_id: Optional[int]
    ) -> (List[PRSPurchaseOrder], int):
        blanket_po = aliased(PRSPurchaseOrder)
        base_query = (
            db.session.query(PRSPurchaseOrder)
            .join(
                blanket_po,
                and_(
                    blanket_po.project_id == PRSPurchaseOrder.project_id,
                    blanket_po.po_number == PRSPurchaseOrder.parent_po_number,
                    blanket_po.id == purchase_order_id,
                ),
            )
            .join(Project, blanket_po.project)
            .options(
                joinedload(PRSPurchaseOrder.campaigns).joinedload(Campaign.type).joinedload(CampaignTypes.group),
                joinedload(PRSPurchaseOrder.prs_budget_group),
                joinedload(PRSPurchaseOrder.prs_budget_category),
            )
            .filter(Project.is_deleted.is_(False))
        )

        base_query = self.__apply_filters(base_query, filters)
        sort_expression = sorting_utils.sorting_expression(self.po_sort_map, filters.sort)
        items = base_query.order_by(*sort_expression).limit(filters.limit).offset(filters.offset).all()
        total = base_query.count()
        return items, total

    def __apply_filters(self, base_query, filters) -> Query:
        if filters.provider is not None:
            base_query = base_query.filter(PRSPurchaseOrder.provider_id == filters.provider)
        if filters.status is not None:
            base_query = base_query.filter(PRSPurchaseOrder.status == filters.status)
        if filters.search is not None:
            found_ids = PRSPurchaseOrder.search(expression=filters.search)
            base_query = base_query.filter(PRSPurchaseOrder.id.in_(found_ids))
        return base_query