from migrations.fixtures.delphi_tables.v1.triggers.base_trigger import BaseTrigger
from migrations.fixtures.delphi_tables.v2.triggers.queries.user_projects_queries import UserProjectsQueries

class ProjectTrigger(BaseTrigger):
    table = "Project"
    function_name = "log_project_changes"

    def on_update(self):
        return f"""
            IF (NEW.prs_project_code IS NOT NULL AND NEW.gras_project_code IS NOT NULL AND OLD.is_deleted IS FALSE) THEN
                IF (OLD.owner_id IS NULL AND NEW.owner_id IS NOT NULL) THEN
                    {self.__insert_queries()}
                    {self.__delete_user_projects()};
                    {self.__insert_user_project()};
                ELSIF (OLD.is_deleted IS FALSE AND NEW.is_deleted IS TRUE) THEN
                    {self.__delete_queries()}
                ELSE
                    {self.__update_project()};
                    IF (OLD.end_date <> NEW.end_date) THEN
                        {self.__update_phases()};
                    END IF;
                    IF (OLD.owner_id <> NEW.owner_id) THEN
                        {self.__delete_user_projects()};
                        {self.__insert_user_project(
                            where_clause="project.is_deleted IS FALSE AND project.id = NEW.id"
                        )};
                    END IF;
                END IF;
            END IF;
        """

    def __insert_queries(self):
        return f"""
            -- INSERT PROJECS
            INSERT INTO projects(id, gras_id, name, budget, decibel_label_id, is_confidential, end_date)
                VALUES(
                    dsp_id('PRS'::text, NEW.prs_project_code),
                    dsp_id('GRAS'::text, NEW.gras_project_code),
                    NEW.name,
                    NEW.budget,
                    NEW.label_id,
                    NEW.is_confidential,
                    NEW.end_date
                );
            -- INSERT PROJECT_ARTISTS
            INSERT INTO project_artists
                SELECT dsp_id('PRS'::text, project.prs_project_code) AS project_id, artist.external_id AS artist_id
                FROM "Project" project
                JOIN "ProjectTargetItem" project_target_item ON (
                    project_target_item.project_id = project.id AND
                    project_target_item.is_deleted IS FALSE AND
                    project_target_item.entity_type = 0
                )
                JOIN "Polymorphable" polymorphable ON polymorphable.id = project_target_item.entity_id
                JOIN "Artist" artist ON artist.id = polymorphable.id
                WHERE project.is_deleted IS FALSE AND project.owner_id IS NOT NULL AND project.id = NEW.id
                GROUP BY project.prs_project_code, artist.external_id;
            -- INSERT PROJECT_CAMPAIGNS
            {self.__insert_project_campaigns()}
        """

    def __insert_user_project(self, where_clause="project.is_deleted IS FALSE AND project.id = NEW.id"):
        return f"""
            INSERT INTO user_projects(project_id, user_id)
                {UserProjectsQueries.get_user_projects(where_clause=where_clause)}
        """

    def __insert_project_campaigns(self):
        return """
            INSERT INTO project_campaigns(project_id, campaign_id)
                SELECT dsp_id('PRS'::text, NEW.prs_project_code) as project_id,
                       campaign_dsp_id(campaign.source::text, campaign.external_id) as campaign_id
                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.id
                    AND campaign.source in ('facebook', 'google')
                GROUP BY project.prs_project_code, campaign.source, campaign.external_id;
        """

    def __delete_queries(self):
        return f"""
            -- DELETES ALL STAFF RELATED TO PROJECT(and approved campaigns also)
            DELETE FROM linkfire_link_project WHERE project_id = dsp_id('PRS'::text, OLD.prs_project_code);
            DELETE FROM projects WHERE id = dsp_id('PRS'::text, OLD.prs_project_code);
            DELETE FROM project_artists WHERE project_id = dsp_id('PRS'::text, NEW.prs_project_code);
            DELETE FROM project_campaigns WHERE project_id = dsp_id('PRS'::text, NEW.prs_project_code);
            DELETE FROM phase_campaigns
                WHERE phase_id IN (
                    SELECT phase.id
                    FROM "ProjectPhase" phase
                    WHERE phase.project_id = NEW.id
                );
            DELETE FROM phases WHERE phases.project_id = dsp_id('PRS'::text, NEW.prs_project_code);
            DELETE FROM campaigns WHERE id IN (
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id)
                FROM "Campaign" campaign
                JOIN "ProjectCampaign" pc ON pc.campaign_id = campaign.id AND pc.status = 1 AND pc.project_id = NEW.id
                WHERE pc.project_id = NEW.id
            );
            {self.__delete_user_projects()};
        """

    def __update_project(self):
        return """
            UPDATE projects
                SET name = NEW.name,
                    budget = NEW.budget,
                    end_date = NEW.end_date,
                    decibel_label_id = NEW.label_id,
                    start_date = (
                        SELECT min("ProjectPhase".start_date)
                        FROM "ProjectPhase"
                        WHERE "ProjectPhase".project_id = NEW.id
                        GROUP BY "ProjectPhase".project_id
                    ),
                    is_confidential = NEW.is_confidential
            WHERE projects.id = dsp_id('PRS'::text, NEW.prs_project_code)
        """


    def __delete_user_projects(self, where_clause="project_id = dsp_id('PRS'::text, NEW.prs_project_code)"):
        return f"""
            DELETE FROM user_projects WHERE {where_clause}
        """

    def __update_phases(self):
        return f"""
           DELETE FROM phases WHERE phases.project_id = dsp_id('PRS'::text, NEW.prs_project_code);

           INSERT INTO phases(id, name, project_id, start_date, end_date)
                SELECT project_phase.id,
                       project_phase.name,
                       dsp_id('PRS'::text, project.prs_project_code) AS project_id,
                       project_phase.start_date,
                        GREATEST(COALESCE(
                            CASE
                                WHEN lead(project_phase.project_id) OVER (ORDER BY project_phase.project_id, project_phase."order") = project_phase.project_id
                                    THEN lead(project_phase.start_date - '1 day'::interval) OVER (ORDER BY project_phase.project_id, project_phase."order")
                                ELSE NULL::timestamp without time zone
                            END, project.end_date::timestamp without time zone)::date, project_phase.start_date
                        ) AS end_date
                FROM "ProjectPhase" project_phase
                JOIN "Project" project ON (
                    project.id = project_phase.project_id AND project.is_deleted IS FALSE AND project.owner_id IS NOT NULL
                )
                WHERE project.id = NEW.id
                GROUP BY project.prs_project_code,
                         project_phase.id,
                         project_phase.name,
                         project_phase.start_date,
                         project.end_date
        """