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

class CampaignTrigger(BaseTrigger):
    table = "Campaign"
    function_name = "log_campaign_changes"

    def on_insert(self):
        return f"""
            IF (NEW.source is NOT NULL AND NEW.external_id is NOT NULL) THEN
                {self.__insert_campaign()};
                {self.__insert_campaign_platforms()};
                {self.__phase_campaigns_updates()}
            END IF;
        """

    def on_update(self):
        return f"""
            IF (NEW.external_id is NOT NULL AND OLD.is_deleted IS FALSE) THEN
                IF (OLD.is_deleted IS FALSE AND NEW.is_deleted IS TRUE) THEN
                    DELETE FROM linkfire_link_campaign WHERE campaign_id = campaign_dsp_id(OLD.source::text, OLD.external_id);
                    DELETE FROM campaigns WHERE id = campaign_dsp_id(NEW.source::text, NEW.external_id);
                    DELETE FROM project_campaigns WHERE campaign_id = campaign_dsp_id(NEW.source::text, NEW.external_id);
                    DELETE FROM phase_campaigns WHERE campaign_id = campaign_dsp_id(NEW.source::text, NEW.external_id);
                ELSE
                    IF (OLD.external_id IS NULL AND NEW.external_id IS NOT NULL AND (NEW.source = 'dec' OR NEW.source = 'PRS')) THEN
                        {self.__insert_campaign(is_pending='FALSE')};
                        {self.__insert_campaign_platforms()};
                        {self.__set_campaign_types()};
                        {self.__phase_campaigns_updates()}
                        {self.__insert_project_campaign()};
                        {self.__insert_linkfire_campaigns()};
                    ELSE
                        UPDATE campaigns
                            SET name = subquery.name,
                            budget = subquery.budget,
                            spend = subquery.spend,
                            provider_id = subquery.provider_id,
                            start_date = subquery.start_date,
                            end_date = subquery.end_date,
                            is_pending = subquery.is_pending,
                            objective_id = subquery.objective_id,
                            decibel_label_id = subquery.decibel_label_id,
                            category_id = subquery.category_id,
                            sub_category_id = subquery.sub_category_id
                        FROM ({CampaignsQuery.select_campaign()}) as subquery
                        WHERE campaigns.id = campaign_dsp_id(NEW.source::text, NEW.external_id);

                        {self.__phase_campaigns_updates()}
                    END IF;
                END IF;
            ELSIF (OLD.external_id IS NOT NULL AND NEW.external_id IS NULL) THEN
                DELETE FROM linkfire_link_campaign WHERE campaign_id = campaign_dsp_id(OLD.source::text, OLD.external_id);
                DELETE FROM campaigns WHERE id = campaign_dsp_id(OLD.source::text, OLD.external_id);
                DELETE FROM project_campaigns WHERE campaign_id = campaign_dsp_id(OLD.source::text, OLD.external_id);
                DELETE FROM phase_campaigns WHERE campaign_id = campaign_dsp_id(OLD.source::text, OLD.external_id);
                DELETE FROM campaign_platforms WHERE campaign_id = campaign_dsp_id(OLD.source::text, OLD.external_id);
            END IF;
        """

    def __insert_campaign(self, is_pending='TRUE'):
        return f"""
            INSERT INTO campaigns(id, name, budget, spend, provider_id, start_date, end_date, objective_id, is_pending, decibel_label_id)
                VALUES(
                    campaign_dsp_id(NEW.source::text, NEW.external_id),
                    NEW.name,
                    NEW.planned_budget,
                    NEW.budget_spend,
                    NEW.provider_id,
                    NEW.start_date,
                    NEW.end_date,
                    NEW.objective_id,
                    {is_pending},
                    COALESCE(
                        (SELECT label_id FROM "Project" WHERE id = NEW.project_id),
                        (SELECT label_id FROM "MarketingAccount" WHERE external_id::text = NEW.external_marketing_account_id::text)
                    )
            )
        """

    def __insert_campaign_platforms(self):
        return """
            INSERT INTO campaign_platforms(campaign_id, name)
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                        campaign_type_value.name
                FROM "Campaign" campaign
                JOIN "CampaignType" campaign_type ON campaign_type.campaign_id = campaign.id
                JOIN "CampaignTypeCategory" campaign_type_category ON campaign_type_category.id = campaign_type.category_id AND campaign_type_category.name::text = 'Platforms'::text
                JOIN "CampaignType" platform_campaign_type ON platform_campaign_type.campaign_id = campaign.id AND platform_campaign_type.category_id = campaign_type_category.id
                JOIN "CampaignTypeValue" campaign_type_value ON campaign_type_value.id = platform_campaign_type.value_id
                WHERE campaign.id = NEW.id
                GROUP BY campaign.id, campaign_type_value.name
        """

    def __phase_campaigns_updates(self):
        return f"""
            IF EXISTS (SELECT 1 FROM phase_campaigns WHERE campaign_id = campaign_dsp_id(NEW.source::text, NEW.external_id)) THEN
                DELETE FROM phase_campaigns
                    WHERE campaign_id = campaign_dsp_id(NEW.source::text, NEW.external_id);
            END IF;

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

    def __insert_project_campaign(self):
        return f"""
            INSERT INTO project_campaigns(project_id, campaign_id)
                VALUES(
                    (SELECT dsp_id('PRS', prs_project_code) FROM "Project" WHERE id = NEW.project_id),
                    campaign_dsp_id(NEW.source::text, NEW.external_id)
                )
        """

    def __insert_linkfire_campaigns(self):
        return f"""
            INSERT INTO linkfire_link_campaign(campaign_id, link_id)
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                       linkfire_link.link_id::uuid AS link_id
                FROM "LinkfireCampaign" AS linkfire_campaign
                JOIN "Linkfire" AS linkfire_link ON linkfire_campaign.linkfire_id = linkfire_link.id
                JOIN "Campaign" AS campaign ON linkfire_campaign.campaign_id = campaign.id
                WHERE campaign.external_id IS NOT NULL
                    AND campaign.is_deleted IS FALSE
                    AND campaign.id = NEW.id
        """

    def __set_campaign_types(self):
        return f'''
            UPDATE campaigns
            SET category_id = cp.category_id,
                sub_category_id = cp.sub_category_id
            FROM (
                SELECT campaign_dsp_id(cp.source::text, cp.external_id) as campaign_id,
                       ctv.id AS sub_category_id,
                       ctv.group_id AS category_id
                FROM "Campaign" cp
                JOIN "CampaignType" ct ON cp.id = ct.campaign_id
                JOIN "CampaignTypeValue" ctv ON ct.value_id = ctv.id AND ctv.group_id IS NOT NULL
                WHERE cp.id = NEW.id
                GROUP BY cp.id, ctv.id
            ) as cp
            WHERE campaigns.id = cp.campaign_id
        '''