"""update_product_families_relation

Revision ID: e71c2b434237
Revises: 7b65c710b258
Create Date: 2021-03-19 10:48:07.538831

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
from sqlalchemy import select

revision = 'e71c2b434237'
down_revision = '7b65c710b258'
branch_labels = None
depends_on = None

def get_table(bind, table_name: str):
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    return meta.tables[table_name]

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        'GRASProjectProductFamily',
        sa.Column('id', sa.BIGINT(), server_default=sa.text('nextval(\'"GRASProjectProductFamily_id_seq"\'::regclass)'), autoincrement=True, nullable=False),
        sa.Column('gras_project_id', sa.BigInteger(), nullable=False),
        sa.Column('product_family_id', sa.BigInteger(), nullable=False),
        sa.ForeignKeyConstraint(['gras_project_id'], ['GRASProject.id'],
                                name=op.f('fk_GRASProjectProductFamily_gras_project_id_GRASProject'), ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['product_family_id'], ['ProductFamily.id'],
                                name=op.f('fk_GRASProjectProductFamily_gras_project_id_ProductFamily'),
                                ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_GRASProjectProductFamily')),
        sa.UniqueConstraint('product_family_id', 'gras_project_id', name='uq_GRASProjectProductFamily_gras_project_id_product_family_id')
    )

    bind = op.get_bind()
    product_families = bind.execute(f'''SELECT id, gras_project_id FROM "ProductFamily"''').fetchall()
    gras_project_product_families = get_table(bind, "GRASProjectProductFamily")
    for id, gras_project_id in product_families:
        op.execute(gras_project_product_families.insert().values(product_family_id=id, gras_project_id=gras_project_id))
    op.drop_column('ProductFamily', 'gras_project_id')


def downgrade():
    op.add_column('ProductFamily', sa.Column('gras_project_id', sa.BigInteger(), nullable=True))
    bind = op.get_bind()
    product_family = get_table(bind, "ProductFamily")

    product_families = bind.execute(
        f'''SELECT product_family_id, gras_project_id FROM "GRASProjectProductFamily"'''
    ).fetchall()
    for product_family_id, gras_project_id in product_families:
        op.execute(
            product_family.update().where(product_family.c.id == product_family_id).values(
                gras_project_id=gras_project_id
            )
        )
    op.alter_column('ProductFamily', 'gras_project_id', existing_type=sa.BigInteger(), nullable=False)
    op.create_foreign_key(op.f('fk_ProductFamily_gras_project_id_GRASProject'), 'ProductFamily', 'GRASProject', ['gras_project_id'], ['id'])
    op.drop_table("GRASProjectProductFamily")