"""create_artists_teams

Revision ID: f6735ec22d02
Revises: 4f880c1a4ead
Create Date: 2021-07-28 12:41:02.400600

"""
from operator import and_
from typing import List

from alembic import op
from sqlalchemy import select
from sqlalchemy.dialects.postgresql import insert

from migrations.utils import get_table

# revision identifiers, used by Alembic.
revision = 'f6735ec22d02'
down_revision = '0c3d5bb8c11a'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    result_tuple = bind.execute('''
        SELECT ARRAY_AGG(owner_id), label_id, A.id FROM "Project"
            JOIN "ProjectTargetItem" PTI on "Project".id = PTI.project_id
            JOIN "Polymorphable" P on P.id = PTI.entity_id
            JOIN "Artist" A on P.id = A.id
        WHERE owner_id is not null
        GROUP BY label_id, A.id
    ''').fetchall()

    artist_team_table = get_table(bind, "ArtistTeam")
    artist_team_user_table = get_table(bind, "ArtistTeamUser")
    user_project_table = get_table(bind, "UserProject")
    user_project_group_table = get_table(bind, "UserProjectCampaignTypeGroup")
    project_table = get_table(bind, "Project")
    user_table = get_table(bind, "User")

    decibel_user_id = bind.execute(
        select([user_table.c.id])
        .select_from(user_table)
        .where(and_(user_table.c.email == 'decibel@dec.com', user_table.c.is_service_account.is_(True)))
    ).fetchone()[0]

    def fetch_projects_for_artist(artist_id: int, label: int) -> List[int]:
        result_tuple = bind.execute(f'''
            SELECT DISTINCT P.id FROM "Project" P
                JOIN "ProjectTargetItem" PTI on (
                    P.id = PTI.project_id AND
                    PTI.entity_type = 0 AND
                    PTI.add_type = 2 AND
                    PTI.is_deleted is FALSE
                )
                JOIN "Polymorphable" Pol on Pol.id = PTI.entity_id
                JOIN "Artist" A on Pol.id = A.id
            WHERE A.id = {artist_id}
                  AND P.label_id = {label}
                  AND P.is_confidential is FALSE
                  AND (A.name != 'Unknown' AND A.name NOT ILIKE '%%Various%%')
        ''').fetchall()

        return [res[0] for res in result_tuple]


    def assign_user_to_team(user_id, artist_team_id, role):
        bind.execute(
            insert(artist_team_user_table)
            .values(artist_team_id=artist_team_id, user_id=user_id, role=role)
            .on_conflict_do_nothing()
        )

    def create_team(artist_id: int, label_id: int) -> int:
        return bind.execute(
            insert(artist_team_table)
            .values(artist_id=artist_id, label_id=label_id)
            .returning(artist_team_table.c.id)
            .on_conflict_do_nothing()
        ).fetchone()[0]

    def assign_user_to_project(user_id: int, project_id: int, role: int):
        result_id = bind.execute(
            insert(user_project_table)
            .values(user_id=user_id, project_id=project_id, shared_by=decibel_user_id, role=role)
            .returning(user_project_table.c.id)
            .on_conflict_do_nothing()
        ).fetchone()[0]

        bind.execute(user_project_group_table.insert().values(user_project_id=result_id, campaign_type_group_id=1))
        bind.execute(user_project_group_table.insert().values(user_project_id=result_id, campaign_type_group_id=2))
        bind.execute(user_project_group_table.insert().values(user_project_id=result_id, campaign_type_group_id=3))

    for item in result_tuple:
        owners = item[0]
        label = item[1]
        artist_id = item[2]

        team_id = create_team(artist_id, label)
        assumed_owner = owners[0]
        other_users = owners[1:]

        projects = fetch_projects_for_artist(artist_id, label)

        if projects:
            if len(projects) != 1:
                select_condition = f"prj.id in {tuple(projects)}"
            else:
                select_condition = f"prj.id = {projects[0]}"

            bind.execute(f'''
                WITH prj_dates AS (
                    SELECT COALESCE(MAX(camp.end_date), MAX(camp.start_date), max(phases.start_date), CURRENT_DATE) AS end_date,
                    prj.id as project_id
                    FROM "Project" AS prj
                    LEFT JOIN "Campaign" AS camp ON camp.project_id = prj.id AND camp.is_deleted IS FALSE
                    LEFT JOIN  "ProjectPhase" as phases on prj.id = phases.project_id
                    WHERE prj.is_deleted IS FALSE AND {select_condition}
                    GROUP BY prj.id
                )
                UPDATE "Project" AS prj
                SET name = prj.prs_title,
                    owner_id = {assumed_owner},
                    end_date = prj_dates.end_date
                FROM prj_dates
                WHERE {select_condition} AND prj_dates.project_id = prj.id
            ''')
            bind.execute(f"""
                INSERT INTO "ProjectPhase"(project_id, name, "order", start_date) (
                    SELECT prj.id,
                           'Phase 1',
                           0,
                           COALESCE(MIN(camp.start_date), CURRENT_DATE)
                    FROM "Project" AS prj
                    LEFT JOIN "ProjectPhase" prj_ph ON prj_ph.project_id = prj.id
                    LEFT JOIN "Campaign" AS camp ON camp.project_id = prj.id AND camp.is_deleted IS FALSE
                    WHERE prj.is_deleted IS FALSE AND prj_ph.id IS NULL AND {select_condition}
                    GROUP BY prj.id
                )
            """)

        # Now owner role id - 1
        assign_user_to_team(assumed_owner, team_id, 1)

        for project in projects:
            for user in other_users:
                assign_user_to_project(user, project, 2)


def downgrade():
    pass