"""

Revision ID: 0260_remove_dvla_organisation
Revises: 0259_remove_service_postage
Create Date: 2019-02-12 17:39:02.517571

"""
import sqlalchemy as sa
from alembic import op

revision = "0260_remove_dvla_organisation"
down_revision = "0259_remove_service_postage"


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column("services", "dvla_organisation_id")
    op.drop_column("services_history", "dvla_organisation_id")
    op.drop_table("dvla_organisation")
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "dvla_organisation",
        sa.Column("id", sa.VARCHAR(), autoincrement=False, nullable=False),
        sa.Column("name", sa.VARCHAR(length=255), autoincrement=False, nullable=False),
        sa.Column("filename", sa.VARCHAR(length=255), autoincrement=False, nullable=False),
        sa.PrimaryKeyConstraint("id", name="dvla_organisation_pkey"),
    )
    # can't repopulate the services, but we can put the static data back in dvla_organisation
    op.execute(
        """
        INSERT INTO dvla_organisation VALUES
        ('001', 'HM Government', 'hm-government'),
        ('002', 'Office of the Public Guardian', 'opg'),
        ('003', 'Department for Work and Pensions', 'dwp'),
        ('004', 'Government Equalities Office', 'geo'),
        ('005', 'Companies House', 'ch'),
        ('006', 'DWP (Welsh)', 'dwp-welsh'),
        ('007', 'Department for Communities', 'dept-for-communities'),
        ('008', 'Marine Management Organisation', 'mmo'),
        ('009', 'HM Passport Office', 'hmpo'),
        ('010', 'Disclosure and Barring Service', 'dbs'),
        ('500', 'Land Registry', 'hm-land-registry'),
        ('501', 'Environment Agency (PDF letters ONLY)', 'ea'),
        ('502', 'Welsh Revenue Authority', 'wra'),
        ('503', 'East Riding of Yorkshire Council', 'eryc'),
        ('504', 'Rother District Council', 'rother'),
        ('505', 'CADW', 'cadw'),
        ('506', 'Tyne and Wear Fire and Rescue Service', 'twfrs'),
        ('507', 'Thames Valley Police', 'thames-valley-police'),
        ('508', 'Ofgem', 'ofgem'),
        ('509', 'Hackney Council', 'hackney'),
        ('510', 'Pension Wise', 'pension-wise'),
        ('511', 'NHS', 'nhs'),
        ('512', 'Vale of Glamorgan', 'vale-of-glamorgan'),
        ('513', 'Rother and Wealden', 'wdc'),
        ('514', 'Brighton and Hove city council', 'brighton-hove'),
        ('515', 'ACAS', 'acas'),
        ('516', 'Worcestershire County Council', 'worcestershire'),
        ('517', 'Buckinghamshire County Council', 'buckinghamshire'),
        ('518', 'Bournemouth Borough Council', 'bournemouth'),
        ('519', 'Hampshire County Council', 'hants'),
        ('520', 'Neath Port Talbot Council', 'npt'),
        ('521', 'North Somerset Council', 'north-somerset'),
        ('522', 'Anglesey Council', 'anglesey'),
        ('523', 'Angus Council', 'angus'),
        ('524', 'Cheshire East Council', 'cheshire-east'),
        ('525', 'Newham Council', 'newham'),
        ('526', 'Warwickshire Council', 'warwickshire'),
        ('527', 'Natural Resources Wales', 'natural-resources-wales'),
        ('528', 'North Yorkshire Council', 'north-yorkshire'),
        ('529', 'Redbridge Council', 'redbridge'),
        ('530', 'Wigan Council', 'wigan')
    """
    )

    op.add_column(
        "services_history",
        sa.Column(
            "dvla_organisation_id",
            sa.VARCHAR(),
            server_default=sa.text("'001'::character varying"),
            autoincrement=False,
            nullable=False,
        ),
    )
    op.add_column(
        "services",
        sa.Column(
            "dvla_organisation_id",
            sa.VARCHAR(),
            server_default=sa.text("'001'::character varying"),
            autoincrement=False,
            nullable=False,
        ),
    )

    op.create_index(
        "ix_services_history_dvla_organisation_id", "services_history", ["dvla_organisation_id"], unique=False
    )
    op.create_foreign_key(
        "services_dvla_organisation_id_fkey", "services", "dvla_organisation", ["dvla_organisation_id"], ["id"]
    )
    op.create_index("ix_services_dvla_organisation_id", "services", ["dvla_organisation_id"], unique=False)
    # ### end Alembic commands ###
