"""autogenerate_name_for_existed_UK_campaigns

Revision ID: 558fafc55e6b
Revises: 616c4ad2b4a5
Create Date: 2022-05-30 09:00:02.339481

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '558fafc55e6b'
down_revision = '616c4ad2b4a5'
branch_labels = None
depends_on = None


bind = op.get_bind()


def get_locked_primary_artists_for_project_id(project_id):
    return bind.execute(f"""
        select string_agg(replace(A.name, ' ', ''), '/')
        from "Artist" AS A
            join "ProjectTargetItem" AS PTI ON PTI.project_id = {project_id}
                and PTI.entity_type = 0
                and PTI.add_type = 2
                and PTI.is_deleted = False
            join "Polymorphable" AS PY ON A.id = PY.id AND PY.id = PTI.entity_id
        group by A.name;
    """).fetchone()

def get_territories_codes_by_campaign_id(campaign_id):
    return bind.execute(f"""
    select string_agg(replace(T.code, 'GB', 'UK'), '/')
    from "Territory" AS T
        join "CampaignTerritory" AS CT ON CT.territory_id = T.id AND CT.campaign_id = {campaign_id}
    group by T.code;
""").fetchone()

def get_campaign_placements_by_campaign_id(campaign_id):
    return bind.execute(f"""
        select string_agg(replace(CP.name, ' ', ''), '/')
        from "CampaignPlacements" AS CP
            join "CampaignPlacementsLinks" AS CPL ON CPL.placement_id = CP.id and CPL.campaign_id = {campaign_id}
        group by CP.name;
    """).fetchone()

def get_campaign_goal_abbreviation_by_objective_id(objective_id):
    return bind.execute(f"""
        select replace(replace(replace(CG.name, 'Engagement', 'EN'), 'Acquisition', 'AW'), 'Consumption', 'DR')
        from "CampaignGoal" AS CG
            join "CampaignObjective" AS CO ON CO.goal_id = CG.id and CO.id = {objective_id}
        group by CG.name;
    """).fetchone()

def build_campaign_name(
        project_id,
        project_code,
        label_abbreviation,
        platforms,
        campaign_id: int,
        start_month: str,
        objective_id
):
    territories_codes = get_territories_codes_by_campaign_id(campaign_id)
    objective = None
    if objective_id:
        objective = get_campaign_goal_abbreviation_by_objective_id(objective_id)
    artists_names = get_locked_primary_artists_for_project_id(project_id)
    creative_format = get_campaign_placements_by_campaign_id(campaign_id)

    data_list = [
        project_code,
        label_abbreviation,
        artists_names[0] if artists_names else "ArtistName",
        "ReleaseType",
        "ReleaseName",
        platforms if platforms else "Platforms",
        objective[0] if objective else "Objective",
        start_month,
        "Targeting",
        territories_codes[0] if territories_codes else "Territories",
        "CreativeDescription",
        creative_format[0] if creative_format else "CreativeFormat",
        "VideoLength",
        str(campaign_id)
    ]
    return '_'.join([parameter for parameter in data_list])

def upgrade():
    campaigns = bind.execute("""
            select C.id                             AS campaign_id,
                to_char(C.start_date, 'Mon')        AS month,
                C.project_id                        AS project_id,
                C.objective_id                      AS objective_id,
                P.gras_project_code                 AS project_code,
                L.abbreviation                      AS label_abbreviation,
                string_agg(replace(CP.name, ' ', ''), '/') AS platforms
            from "Campaign" AS C
                join "Project" AS P ON C.project_id = P.id
                join "Label" AS L ON P.label_id = L.id
                join "CampaignPlatformsLinks" ON "CampaignPlatformsLinks".campaign_id = C.id
                join "CampaignPlatforms" AS CP ON CP.id = "CampaignPlatformsLinks".platform_id
            where L.country = 'UK' AND (C.source = 'dec' OR C.source is null)
            group by C.id, L.abbreviation, P.gras_project_code;
    """).fetchall()
    for campaign_id, month, project_id, objective_id, project_code, label_abbreviation, platforms in campaigns:
        name = build_campaign_name(
            project_id,
            project_code,
            label_abbreviation,
            platforms,
            campaign_id,
            month,
            objective_id
        )
        op.execute("""
        UPDATE "Campaign" SET name = '{name}', name_autogenerated = true WHERE id = '{id}'
        """.format(name=name, id=campaign_id))


def downgrade():
    pass