from alembic import op
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker, make_transient

from portal.models.organization import Organization, UserOrganization, OrganizationLocale
from portal.models.organization import OrganizationIdentifier, OrganizationAddress, OrgTree
from portal.models.questionnaire_bank import QuestionnaireBank
from portal.models.tou import ToU
from portal.models.user_consent import UserConsent

"""empty message

Revision ID: b814b51681bf
Revises: 50c447197b26
Create Date: 2017-10-13 14:48:52.190149

"""

# revision identifiers, used by Alembic.
revision = 'b814b51681bf'
down_revision = '50c447197b26'

Session = sessionmaker()

def update_org_id(old_id, new_id, new_parent_id=None, new_name=None):
    bind = op.get_bind()
    session = Session(bind=bind)

    org = session.query(Organization).get(old_id)
    if not org:
        raise ValueError('organization with id {} not found'.format(old_id))
    OT = OrgTree()
    if len(OT.here_and_below_id(old_id)) > 1:
        raise ValueError('organization `{}` cannot be moved '
                         'before children'.format(old_id))

    make_transient(org)

    org.id = new_id
    if new_parent_id:
        org.partOf_id = new_parent_id
    if new_name:
        org.name = new_name
    session.add(org)
    session.commit()

    # update the related fields to point at new org

    for qb in session.query(QuestionnaireBank).filter_by(organization_id=old_id):
        qb.organization_id = new_id
    session.commit()

    for uc in session.query(UserConsent).filter_by(organization_id=old_id):
        uc.organization_id = new_id
    session.commit()

    for uo in session.query(UserOrganization).filter_by(organization_id=old_id):
        uo.organization_id = new_id
    session.commit()

    for tou in session.query(ToU).filter_by(organization_id=old_id):
        tou.organization_id = new_id
    session.commit()

    for oi in session.query(OrganizationIdentifier).filter_by(organization_id=old_id):
        oi.organization_id = new_id
    session.commit()

    for ol in session.query(OrganizationLocale).filter_by(organization_id=old_id):
        ol.organization_id = new_id
    session.commit()

    for oa in session.query(OrganizationAddress).filter_by(organization_id=old_id):
        oa.organization_id = new_id
    session.commit()

    session.execute('DELETE FROM organizations WHERE id = {}'.format(old_id))


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    update_org_id(21110, 21300, 21000, "Australian Prostate Cancer Research Centre - Queensland")
    update_org_id(21120, 21400, 21000, "Eastern Health")

    bind = op.get_bind()
    session = Session(bind=bind)

    session.execute("UPDATE organizations SET name = '{}' WHERE "
                    "id = {}".format("Australian Urology Associates (AUA)", 21100))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    update_org_id(21300, 21110, 21100, "AUA Child Site 1")
    update_org_id(21400, 21120, 21100, "AUA Child Site 2")

    bind = op.get_bind()
    session = Session(bind=bind)

    session.execute("UPDATE organizations SET name = '{}' WHERE "
                    "id = {}".format("AUA Recruiting Site", 21100))
    # ### end Alembic commands ###

@@ -873,75 +873,13 @@
    r
-aise ValueError('organization with id %7B%7D not found'.format(old_id))
+eturn
 %0A   

return when no org found