from migrations.fixtures.delphi_tables.v1.triggers.base_trigger import BaseTrigger
from migrations.fixtures.delphi_tables.v5.triggers.queries.phase_campaigns_query import PhaseCampaignsQuery
from migrations.fixtures.delphi_tables.v4.triggers.queries.campaigns_query import CampaignsQuery


class ProjectCampaignTrigger(BaseTrigger):
    table = "ProjectCampaign"
    function_name = "log_project_campaign_changes"

    def on_insert(self):
        is_phases_campaigns_exists = PhaseCampaignsQuery.select_phase_campaigns(
            columns='1',
            where_clause="project.id = NEW.project_id AND campaign.id = NEW.campaign_id"
        )
        return f"""
            IF EXISTS ({self.__select_project_campaigns_query('1')}) THEN
                INSERT INTO project_campaigns(project_id, campaign_id) {self.__select_project_campaigns_query()};
                IF EXISTS ({is_phases_campaigns_exists}) THEN
                    INSERT INTO phase_campaigns(campaign_id, phase_id) {
                        PhaseCampaignsQuery.select_phase_campaigns(
                            where_clause="project.id = NEW.project_id AND campaign.id = NEW.campaign_id")
                    };
                END IF;
                UPDATE campaigns
                    SET is_pending = COALESCE(({self.__is_campaign_pending()}), FALSE)
                    WHERE id = ({CampaignsQuery.select_dsp_id_for_campaign(where_clause="campaign.id = NEW.campaign_id")});
            END IF;
        """

    def on_update(self):
        is_phases_campaigns_exists = PhaseCampaignsQuery.select_phase_campaigns(
            columns='1',
            where_clause="project.id = NEW.project_id AND campaign.id = NEW.campaign_id"
        )
        return f"""
            IF OLD.status in (0,1) and NEW.status = 2 THEN
                DELETE FROM project_campaigns
                    WHERE project_id = (SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) FROM "Project" project WHERE project.id = NEW.project_id)
                        AND campaign_id = ({CampaignsQuery.select_dsp_id_for_campaign(where_clause="campaign.id = NEW.campaign_id")});
                DELETE FROM phase_campaigns
                    WHERE campaign_id = ({CampaignsQuery.select_dsp_id_for_campaign(where_clause="campaign.id = NEW.campaign_id")});
                --- We need to switch back campaign end_date on reject flow
                {
                    self.__update_campaign(
                        updatable_columns=f'''
                            end_date = (SELECT campaign.end_date FROM "Campaign" campaign WHERE campaign.id = NEW.campaign_id)
                        ''',
                        campaign_id=CampaignsQuery.select_dsp_id_for_campaign(where_clause="campaign.id = NEW.campaign_id")
                    )
                }
            ELSIF OLD.status in (0,2) AND NEW.status = 1 THEN
                IF OLD.status = 2 THEN
                    INSERT INTO project_campaigns(project_id, campaign_id) {self.__select_project_campaigns_query()};
                    IF EXISTS ({is_phases_campaigns_exists}) THEN
                        INSERT INTO phase_campaigns(campaign_id, phase_id) {
                            PhaseCampaignsQuery.select_phase_campaigns(
                                where_clause="project.id = NEW.project_id AND campaign.id = NEW.campaign_id")
                        };
                    END IF;
                END IF;
                {
                    self.__update_campaign(
                        updatable_columns=f'''
                            end_date = COALESCE(
                                (SELECT campaign.end_date FROM "Campaign" campaign WHERE campaign.id = NEW.campaign_id),
                                (SELECT project.end_date FROM "Project" project WHERE project.id = NEW.project_id)
                            )
                        ''',
                        campaign_id=CampaignsQuery.select_dsp_id_for_campaign(where_clause="campaign.id = NEW.campaign_id")
                    )
                }
            END IF;

            IF OLD.status = 2 AND NEW.status = 0 THEN
                INSERT INTO project_campaigns(project_id, campaign_id) {self.__select_project_campaigns_query()};
            END IF;

            IF EXISTS (SELECT 1 FROM phase_campaigns WHERE campaign_id = (
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id)
                    FROM "Campaign" campaign
                    WHERE campaign.id = NEW.campaign_id
                )
            ) THEN
                DELETE FROM phase_campaigns
                    WHERE campaign_id = (
                        SELECT campaign_dsp_id(campaign.source::text, campaign.external_id)
                        FROM "Campaign" campaign
                        WHERE campaign.id = NEW.campaign_id
                    );
            END IF;

            {
                self.__update_campaign(
                    updatable_columns=f'''
                        is_pending = COALESCE(({self.__is_campaign_pending()}), FALSE)
                    ''',
                    campaign_id=CampaignsQuery.select_dsp_id_for_campaign(where_clause="campaign.id = NEW.campaign_id")
                )
            }

            INSERT INTO phase_campaigns(campaign_id, phase_id)
                {PhaseCampaignsQuery.select_phase_campaigns(where_clause="campaign.id = NEW.campaign_id")};
        """

    def on_delete(self):
        return f"""
            IF EXISTS ({self.__select_project_campaigns_query('1')}) THEN
                DELETE FROM project_campaigns
                    WHERE project_id = ({self.__select_project_campaigns_query("dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code))")})
                        AND campaign_id = ({self.__select_project_campaigns_query("campaign_dsp_id(campaign.source::text, campaign.external_id)")});
                DELETE FROM phase_campaigns
                    WHERE campaign_id = ({CampaignsQuery.select_dsp_id_for_campaign(where_clause="campaign.id = OLD.campaign_id")});
                UPDATE campaigns
                    SET
                    is_pending = COALESCE(({self.__is_campaign_pending(state='OLD')}), FALSE),
                    end_date = (SELECT campaign.end_date FROM "Campaign" campaign WHERE campaign.id = OLD.campaign_id)
                    WHERE id = ({CampaignsQuery.select_dsp_id_for_campaign(where_clause="campaign.id = OLD.campaign_id")});
            END IF;
        """

    def __select_project_campaigns_query(
        self,
        columns = """
            dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id,
            campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id
        """
    ):
        return f"""
            SELECT {columns}
            FROM "Project" project
            LEFT JOIN "ProjectCampaign" project_campaign ON (
                project_campaign.project_id = project.id AND
                (project_campaign.status = ANY (ARRAY[0, 1]))
            )
            JOIN "Campaign" campaign ON (
                campaign.external_id IS NOT NULL
                AND campaign.is_deleted IS FALSE
                AND (project_campaign.campaign_id = campaign.id OR campaign.project_id = project.id)
            )
            WHERE project.is_deleted IS FALSE
                AND project.owner_id IS NOT NULL
                AND campaign.external_id IS NOT NULL
                AND project.id = NEW.project_id
                AND campaign.id = NEW.campaign_id
            GROUP BY project.source, project.ccp_project_code, project.prs_project_code, campaign.source, campaign.external_id
        """

    def __update_campaign(
        self,
        updatable_columns="""
            is_pending = FALSE,
            end_date = COALESCE(
                (SELECT campaign.end_date FROM "Campaign" campaign WHERE campaign.id = NEW.campaign_id),
                (SELECT project.end_date FROM "Project" project WHERE project.id = NEW.project_id)
            )
        """,
        campaign_id=CampaignsQuery.select_dsp_id_for_campaign(where_clause="campaign.id = NEW.campaign_id")
    ):
        return f"""
            UPDATE campaigns
            SET {updatable_columns}
            WHERE campaigns.id = ({campaign_id});
        """

    def __is_campaign_pending(self, state="NEW"):
        return f"""
            SELECT min("ProjectCampaign".status) = 0 AS is_pending
            FROM "ProjectCampaign"
            WHERE "ProjectCampaign".campaign_id = {state}.campaign_id
            GROUP BY "ProjectCampaign".campaign_id
        """