"""Change navigation bar relationship.

Revision ID: f8fcd4bb38cc
Revises: 94f861ea7006
Create Date: 2017-02-11 13:57:03.925474

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# revision identifiers, used by Alembic.
revision = 'f8fcd4bb38cc'
down_revision = '94f861ea7006'

Base = declarative_base()

class Page(Base):
    __tablename__ = 'page'

    id = sa.Column(sa.Integer, primary_key=True)
    path = sa.Column(sa.String(200), unique=True)
    needs_paid = sa.Column(sa.Boolean)
    type = sa.Column(sa.String(256))

class NavigationEntry(Base):
    __tablename__ = 'nagivation_entry'

    id = sa.Column(sa.Integer, primary_key=True)
    parent_id = sa.Column(sa.Integer, sa.ForeignKey('nagivation_entry.id'))
    page_id = sa.Column(sa.Integer, sa.ForeignKey('page.id'))
    nl_title = sa.Column(sa.String(256))
    en_title = sa.Column(sa.String(256))
    url = sa.Column(sa.String(256))
    external = sa.Column(sa.Boolean)
    activity_list = sa.Column(sa.Boolean)
    position = sa.Column(sa.Integer)

def upgrade():
    conn = op.get_bind()

    Session = sessionmaker()
    session = Session(bind=conn)

    op.add_column('nagivation_entry', sa.Column('page_id', sa.Integer(), nullable=True))
    op.create_foreign_key(op.f('fk_nagivation_entry_page_id_page'), 'nagivation_entry', 'page',
                          ['page_id'], ['id'])

    nav_entries = session.query(NavigationEntry).all()
    for nav_entry in nav_entries:
        page = session.query(Page).filter(Page.path == nav_entry.url.strip('/')).first()
        if page is None:
            continue

        nav_entry.page_id = page.id
        nav_entry.url = None
        session.add(nav_entry)
    session.commit()

    op.drop_constraint('page_ibfk_2', 'page', type_='foreignkey')
    op.drop_column('page', 'navigation_entry_id')


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('page', sa.Column('navigation_entry_id', mysql.INTEGER(display_width=11),
                                    autoincrement=False, nullable=True))
    op.create_foreign_key('page_ibfk_2', 'page', 'nagivation_entry', ['navigation_entry_id'],
                          ['id'])
    op.drop_constraint(op.f('fk_nagivation_entry_page_id_page'), 'nagivation_entry',
                       type_='foreignkey')
    op.drop_column('nagivation_entry', 'page_id')
    ### end Alembic commands ###
