"""remove_project_owner

Revision ID: bd8852a6cf9a
Revises: e2cc39f66d0c
Create Date: 2022-11-30 16:15:13.783838

"""
from alembic import op
import sqlalchemy as sa
from migrations.utils import get_table
from models.user_project import UserProjectRoles
from migrations.fixtures.delphi_tables.v6.data_refresher import DelphiTablesDataRefresher as DelphiTablesDataRefresherv6
from migrations.fixtures.delphi_tables.v7.data_refresher import DelphiTablesDataRefresher as DelphiTablesDataRefresherv7
from migrations.fixtures.delphi_tables.views import DelphiViewsV6, DelphiViewsV7

# revision identifiers, used by Alembic.
revision = 'bd8852a6cf9a'
down_revision = '841bd7438704'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    delphi_views = DelphiViewsV6(bind)
    delphi_views.drop()

    delphi_refresherv6 = DelphiTablesDataRefresherv6()
    delphi_refresherv6.drop_triggers(bind)

    user_project_table = get_table(bind, "UserProject")
    op.add_column('Project', sa.Column('is_claimed', sa.Boolean(), nullable=False, server_default="FALSE"))

    project_table = get_table(bind, "Project")
    op.execute(project_table.update().where(project_table.c.owner_id.isnot(None)).values(is_claimed=True))

    owners = bind.execute(
        '''
            SELECT "Project".id, "Project".owner_id
            FROM "Project"
            WHERE "Project".owner_id IS not NULL;
        '''
    ).fetchall()

    user_id = bind.execute('''SELECT "User".id FROM "User" where "User".external_id = 'dec' ''').fetchall()
    user_id = user_id.pop()

    # adding owners as editors to the projects they were owners in
    editors = []
    for project_id, owner_id in owners:
        editors.append({
            "project_id": project_id,
            "user_id": owner_id,
            "role": 2,  # old Editor
            "shared_by": user_id[0],
            "editable": False,
        })

    op.bulk_insert(user_project_table, editors)

    delphi_refresherv7 = DelphiTablesDataRefresherv7()
    delphi_refresherv7.refresh(bind)

    op.drop_column('Project', 'owner_id')


def downgrade():
    bind = op.get_bind()
    delphi_views = DelphiViewsV7(bind)
    delphi_views.drop()

    delphi_refresherv7 = DelphiTablesDataRefresherv7()
    delphi_refresherv7.drop_triggers(bind)

    op.add_column('Project', sa.Column('owner_id', sa.Integer(), nullable=False, server_default="1"))
    op.drop_column('Project', 'owner_id')

    delphi_refresherv6 = DelphiTablesDataRefresherv6()
    delphi_refresherv6.refresh(bind)