from typing import List

import sqlalchemy

from db import db
from sqlalchemy import and_
from models.ccp.raw_ccp_purchase_orders import RawCCPPurchaseOrders
from models.projects import Project
from models.prs_budget_group import PRSBudgetGroup
from models.ccp.raw_ccp_budgets import RawCCPBudgets
from models.prs_budget_category import PRSBudgetCategory
from models.prs_purchase_order import PRSPurchaseOrder
from models.ccp.raw_ccp_expense_hierarchy import RawCCPExpenseHierarchy
from sqlalchemy import cast, Integer, func, distinct
from models import CampaignProvider


class CCPImportRepository:
    def get_raw_ccp_budgets(self):
        return (
            db.session.query(
                Project.id.label("project_id"),
                PRSBudgetGroup.id.label("group_id"),
                PRSBudgetCategory.id.label("category_id"),
                RawCCPBudgets.total_project_amount.label("total_project_amount"),
                RawCCPBudgets.creation_date.label("created_at"),
                RawCCPBudgets.project_budget_code.label("project_budget_code"),
                RawCCPBudgets.currency.label("currency"),
            )
            .select_from(RawCCPBudgets)
            .join(Project, Project.ccp_project_code == RawCCPBudgets.project_budget_code)
            .join(PRSBudgetGroup, PRSBudgetGroup.id == cast(RawCCPBudgets.commodity_lvl_2, Integer))
            .join(PRSBudgetCategory, PRSBudgetCategory.id == cast(RawCCPBudgets.commodity_lvl_3, Integer))
            .all()
        )

    def get_purchase_order(self, project_id: int, po_number: str, po_line_item: int):
        return (
            db.session.query(PRSPurchaseOrder)
            .filter(
                PRSPurchaseOrder.project_id == project_id,
                PRSPurchaseOrder.po_number == po_number,
                PRSPurchaseOrder.po_line_item == po_line_item,
            )
            .one_or_none()
        )

    def get_new_providers(self) -> List[str]:
        return (
            db.session.query(distinct(func.trim(func.lower(RawCCPPurchaseOrders.supplier))))
            .outerjoin(
                CampaignProvider,
                func.lower(CampaignProvider.name) == func.trim(func.lower(RawCCPPurchaseOrders.supplier))
            )
            .filter(CampaignProvider.id.is_(None), func.trim(func.lower(RawCCPPurchaseOrders.supplier)).isnot(None))
            .all()
        )

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

    def aggregated_subquery(self, limit: int, offset: int):
        return (
            db.session.query(
                RawCCPPurchaseOrders.po_id.label("po_num"),
                RawCCPPurchaseOrders.project_budget_code.label("sub_project_cd"),
                cast(RawCCPPurchaseOrders.po_line, Integer).label("po_line_item"),
                cast(RawCCPPurchaseOrders.commodity_lvl_2, Integer).label("budget_group"),
                cast(RawCCPExpenseHierarchy.commodity_lvl_3, Integer).label("budget_category"),
                func.min(RawCCPPurchaseOrders.creation_date).label("po_created_on"),
                func.max(RawCCPPurchaseOrders.last_modified_date).label("po_modified_on"),
                func.trim(RawCCPPurchaseOrders.status).label("po_status"),
                func.coalesce(func.sum(RawCCPPurchaseOrders.line_item_total_amount), 0).label("po_total"),
                func.trim(RawCCPPurchaseOrders.line_item_total_amount_currency).label("po_total_currency"),
                func.coalesce(func.sum(RawCCPPurchaseOrders.paid_line_item_amount), 0).label("po_total_paid"),
                func.coalesce(
                    func.trim(RawCCPPurchaseOrders.paid_line_item_amount_currency),
                    func.trim(RawCCPPurchaseOrders.line_item_total_amount_currency),
                ).label("po_total_paid_currency"),
                func.trim(RawCCPPurchaseOrders.supplier).label("vendor_name"),
                func.STRING_AGG(distinct(RawCCPPurchaseOrders.short_description), " ").label("name"),
            )
            .select_from(RawCCPPurchaseOrders)
            .join(
                RawCCPExpenseHierarchy,
                and_(
                    RawCCPExpenseHierarchy.expense_type == RawCCPPurchaseOrders.expense_type,
                    RawCCPExpenseHierarchy.commodity_lvl_2 == RawCCPPurchaseOrders.commodity_lvl_2,
                )
            )
            .filter(func.trim(RawCCPPurchaseOrders.status).notin_(["Failed", "Rejected", "Removed"]))
            .group_by(
                RawCCPPurchaseOrders.po_id,
                RawCCPPurchaseOrders.project_budget_code,
                cast(RawCCPPurchaseOrders.commodity_lvl_2, Integer),
                cast(RawCCPExpenseHierarchy.commodity_lvl_3, Integer),
                RawCCPPurchaseOrders.po_line,
                func.trim(RawCCPPurchaseOrders.status),
                func.trim(RawCCPPurchaseOrders.line_item_total_amount_currency),
                func.coalesce(
                    func.trim(RawCCPPurchaseOrders.paid_line_item_amount_currency),
                    func.trim(RawCCPPurchaseOrders.line_item_total_amount_currency),
                ),
                func.trim(RawCCPPurchaseOrders.supplier),
            )
            .limit(limit)
            .offset(offset)
            .cte("aggregated_data")
        )

    def get_raw_ccp_purchase_order_count(self):
        return db.session.query(func.distinct(RawCCPPurchaseOrders.po_id, RawCCPPurchaseOrders.po_line)).count()

    def get_raw_ccp_purchase_order(self, limit: int, offset: int):
        aggregated_subquery = self.aggregated_subquery(limit, offset)
        return (
            db.session.query(
                PRSPurchaseOrder,
                aggregated_subquery.c.po_num.label("po_num"),
                aggregated_subquery.c.po_line_item.label("po_line_item"),
                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.po_total.label("po_total"),
                aggregated_subquery.c.po_total_currency.label("po_total_currency"),
                aggregated_subquery.c.po_total_paid.label("po_total_paid"),
                aggregated_subquery.c.po_total_paid_currency.label("po_total_paid_currency"),
                aggregated_subquery.c.vendor_name.label("vendor_name"),
                aggregated_subquery.c.name.label("name"),
                PRSBudgetGroup.id.label("budget_group_id"),
                PRSBudgetCategory.id.label("budget_category_id"),
                Project.id.label("project_id"),
                func.coalesce(CampaignProvider.id, 1).label("provider_id"),
            )
            .select_from(aggregated_subquery)
            .join(Project, Project.ccp_project_code == aggregated_subquery.c.sub_project_cd)
            .outerjoin(
                CampaignProvider,
                func.lower(CampaignProvider.name) == func.lower(aggregated_subquery.c.vendor_name)
            )
            .join(PRSBudgetGroup, PRSBudgetGroup.id == aggregated_subquery.c.budget_group)
            .join(PRSBudgetCategory, PRSBudgetCategory.id == aggregated_subquery.c.budget_category)
            .outerjoin(
                PRSPurchaseOrder,
                and_(
                    PRSPurchaseOrder.po_number == cast(aggregated_subquery.c.po_num, sqlalchemy.String),
                    PRSPurchaseOrder.project_id == Project.id,
                    PRSPurchaseOrder.group_id == PRSBudgetGroup.id,
                    PRSPurchaseOrder.category_id == PRSBudgetCategory.id,
                    PRSPurchaseOrder.po_line_item == aggregated_subquery.c.po_line_item
                )
            )
            .limit(limit)
            .offset(offset)
            .all()
        )

    def truncate_raw_ccp_purchase_orders(self):
        db.session.execute('TRUNCATE "RawCCPPurchaseOrders" RESTART IDENTITY CASCADE')
        db.session.commit()

    def count_raw_ccp_purchase_orders(self):
        return (
            db.session.query(func.count(RawCCPPurchaseOrders.po_id)).scalar()
        )