from db import db
from workers.base_worker import BaseWorker


class PRSProjectBudgetsRecalculationWorker(BaseWorker):
    def execute(self, *args):
        self.logger.info("PRS Budgets Recalculating", "Started")
        db.session.execute(
            """
        WITH project_stats AS (
            SELECT
                   P.id as project_id,
                   COALESCE(SUM(budget.project_budget), 0) as project_budget,
                   COALESCE(SUM(allocations.allocated_budget), 0) as allocated_budget
            FROM "Project" P
                LEFT OUTER JOIN (
                    SELECT P.id as project_id, SUM(PPB.value) as project_budget
                    FROM "Project" P
                        JOIN "PRSProjectBudget" PPB on P.id = PPB.project_id
                    GROUP BY P.id
                    ) budget on P.id = budget.project_id
                LEFT OUTER JOIN (
                    SELECT P.id as project_id, SUM(PPO.total_amount) as allocated_budget
                    FROM "Project" P
                        JOIN "PRSPurchaseOrder" PPO on
                        (
                            P.id = PPO.project_id AND
                            PPO.parent_po_number is Null AND
                            PPO.is_under_uncommited_blanket IS FALSE
                        )
                    GROUP BY P.id
                ) allocations on P.id = allocations.project_id
            WHERE P.source = 'PRS'
            GROUP BY P.id
        )

        UPDATE "Project" SET budget = project_stats.project_budget, allocation = project_stats.allocated_budget
        FROM project_stats WHERE "Project".id = project_stats.project_id;

        """
        )

        db.session.commit()
        db.session.close()
        self.logger.success("PRS Budgets Recalculating", "Finished!")