"""empty message

Revision ID: 01b5b95b092d
Revises: 036b62df80cc
Create Date: 2018-02-16 10:15:45.728018

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
from sqlalchemy import PrimaryKeyConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

revision = "01b5b95b092d"
down_revision = "<KEY>"
branch_labels = None
depends_on = None

organisation_map = {
    "Ipsos MORI / NHS Digital": "PB1202",
    "Department for Digital, Culture, Media & Sport": "D5",
    "Office for National Statistics": "D303",
    "Sport England": "PB190",
    "NHS Digital": "PB1202",
    "Public Health England": "EA480",
    "Department for Digital, Culture, Media and Sport": "D5",
    "Ipsos MORI / NHS England": "PB481",
    "Department for Work and Pensions": "D10",
}

frequency_map = {
    "Unconfirmed": 11,
    "Ad hoc": 10,
    "Every 10 years": 11,
    "N/A – statistics are not published but shared with other government departments who have permission to access the data": 11,
    "Every 2 years": 6,
    "Monthly": 1,
    "Quarterly": 2,
    "Annual": 5,
}

Session = sessionmaker()
Base = declarative_base()


class SecondarySources(Base):

    __tablename__ = "page"

    guid = sa.Column(sa.String(255), nullable=False)
    version = sa.Column(sa.String(), nullable=False)

    __table_args__ = (PrimaryKeyConstraint("guid", "version", name="page_guid_version_pk"),)

    secondary_source_1_publisher_text = sa.Column(sa.TEXT)
    secondary_source_1_frequency = sa.Column(sa.TEXT)

    secondary_source_2_publisher_text = sa.Column(sa.TEXT)
    secondary_source_2_frequency = sa.Column(sa.TEXT)


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    op.alter_column(
        "page", "secondary_source_1_publisher", nullable=True, new_column_name="secondary_source_1_publisher_text"
    )

    op.alter_column(
        "page", "secondary_source_2_publisher", nullable=True, new_column_name="secondary_source_2_publisher_text"
    )

    op.add_column("page", sa.Column("secondary_source_1_frequency_id", sa.Integer(), nullable=True))
    op.add_column("page", sa.Column("secondary_source_1_frequency_other", sa.String(length=255), nullable=True))
    op.add_column("page", sa.Column("secondary_source_1_publisher_id", sa.String(length=255), nullable=True))
    op.add_column("page", sa.Column("secondary_source_2_frequency_id", sa.Integer(), nullable=True))
    op.add_column("page", sa.Column("secondary_source_2_frequency_other", sa.String(length=255), nullable=True))
    op.add_column("page", sa.Column("secondary_source_2_publisher_id", sa.String(length=255), nullable=True))
    op.create_foreign_key(
        "frequency_secondary_source_2_fkey", "page", "frequency_of_release", ["secondary_source_2_frequency_id"], ["id"]
    )
    op.create_foreign_key(
        "frequency_secondary_source_1_fkey", "page", "frequency_of_release", ["secondary_source_1_frequency_id"], ["id"]
    )
    op.create_foreign_key(
        "organisation_secondary_source_2_fkey", "page", "organisation", ["secondary_source_2_publisher_id"], ["id"]
    )
    op.create_foreign_key(
        "organisation_secondary_source_1_fkey", "page", "organisation", ["secondary_source_1_publisher_id"], ["id"]
    )

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

    for s in session.query(SecondarySources):

        if s.secondary_source_1_publisher_text:
            secondary_source_1_publisher_id = organisation_map.get(s.secondary_source_1_publisher_text)
        else:
            secondary_source_1_publisher_id = None

        if s.secondary_source_1_frequency:
            secondary_source_1_frequency_id = frequency_map.get(s.secondary_source_1_frequency)
            secondary_source_1_frequency_other = (
                s.secondary_source_1_frequency if secondary_source_1_frequency_id == 11 else None
            )
        else:
            secondary_source_1_frequency_id = None
            secondary_source_1_frequency_other = None

        if s.secondary_source_2_publisher_text:
            secondary_source_2_publisher_id = organisation_map.get(s.secondary_source_2_publisher_text)
        else:
            secondary_source_2_publisher_id = None

        if s.secondary_source_2_frequency:
            secondary_source_2_frequency_id = frequency_map.get(s.secondary_source_2_frequency)
            secondary_source_2_frequency_other = (
                s.secondary_source_2_frequency if secondary_source_2_frequency_id == 11 else None
            )
        else:
            secondary_source_2_frequency_id = None
            secondary_source_2_frequency_other = None

        update = """
            UPDATE page SET
              secondary_source_1_publisher_id = '%s',
              secondary_source_1_frequency_id = '%s',
              secondary_source_1_frequency_other = '%s',
              secondary_source_2_publisher_id = '%s',
              secondary_source_2_frequency_id = '%s',
              secondary_source_2_frequency_other = '%s'
            WHERE guid = '%s'
            AND VERSION = '%s'
        """ % (
            secondary_source_1_publisher_id,
            secondary_source_1_frequency_id,
            secondary_source_1_frequency_other,
            secondary_source_2_publisher_id,
            secondary_source_2_frequency_id,
            secondary_source_2_frequency_other,
            s.guid,
            s.version,
        )

        update = update.replace("'None'", "null")
        op.execute(update)

    session.commit()
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint("organisation_secondary_source_1_fkey", "page", type_="foreignkey")
    op.drop_constraint("organisation_secondary_source_2_fkey", "page", type_="foreignkey")
    op.drop_constraint("frequency_secondary_source_1_fkey", "page", type_="foreignkey")
    op.drop_constraint("frequency_secondary_source_2_fkey", "page", type_="foreignkey")
    op.drop_column("page", "secondary_source_2_publisher_id")
    op.drop_column("page", "secondary_source_2_frequency_other")
    op.drop_column("page", "secondary_source_2_frequency_id")
    op.drop_column("page", "secondary_source_1_publisher_id")
    op.drop_column("page", "secondary_source_1_frequency_other")
    op.drop_column("page", "secondary_source_1_frequency_id")
    op.alter_column(
        "page", "secondary_source_1_publisher_text", nullable=True, new_column_name="secondary_source_1_publisher"
    )
    op.alter_column(
        "page", "secondary_source_2_publisher_text", nullable=True, new_column_name="secondary_source_2_publisher"
    )
    # ### end Alembic commands ###
