"""empty message

Revision ID: e9545aad30c2
Revises: 
Create Date: 2018-11-20 11:43:07.108432

"""
from alembic import op, context
from flask import current_app
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'e9545aad30c2'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    # ### commands added by me
    op.create_table('segment',
    sa.Column('segment_id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('coordinate_origin', sa.Float()),
    sa.Column('coordinate_end', sa.Float()),
    sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
    sa.PrimaryKeyConstraint('segment_id')
    )
    # ### end commands added by me

    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('address',
    sa.Column('address_id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('house_name_or_number', sa.String(), nullable=False),
    sa.Column('street_name', sa.String(), nullable=False),
    sa.Column('city', sa.String(), nullable=False),
    sa.Column('county', sa.String(), nullable=False),
    sa.Column('country', sa.String(), nullable=False),
    sa.Column('postcode', sa.String(), nullable=False),
    sa.Column('segment_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['segment_id'], ['segment.segment_id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('address_id')
    ) 
    op.create_table('conveyancer',
    sa.Column('conveyancer_id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('x500_name', sa.String(), nullable=False),
    sa.Column('company_name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('conveyancer_id'),
    sa.UniqueConstraint('x500_name')
    )
    op.create_table('owner',
    sa.Column('identity', sa.Integer(), autoincrement=False, nullable=False),
    sa.Column('forename', sa.String(), nullable=False),
    sa.Column('surname', sa.String(), nullable=False),
    sa.Column('email', sa.String(), nullable=False),
    sa.Column('phone', sa.String(), nullable=False),
    sa.Column('owner_type', sa.String(), nullable=False),
    sa.Column('address_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['address_id'], ['address.address_id'], ),
    sa.PrimaryKeyConstraint('identity')
    )
    op.create_index(op.f('ix_owner_email'), 'owner', ['email'], unique=True)
    op.create_table('title',
    sa.Column('title_number', sa.String(), nullable=False),
    sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('lock', sa.DateTime(), nullable=True),
    sa.Column('owner_identity', sa.Integer(), nullable=False),
    sa.Column('address_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['address_id'], ['address.address_id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['owner_identity'], ['owner.identity'], ),
    sa.PrimaryKeyConstraint('title_number')
    )
    # ### end Alembic commands ###

    context.execute("GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE \"address\", \"conveyancer\", \"owner\", \
                    \"title\", \"segment\" TO " + current_app.config.get('APP_SQL_USERNAME'))
    context.execute("GRANT USAGE ON SEQUENCE \"address_address_id_seq\", \
                    \"conveyancer_conveyancer_id_seq\" TO " + current_app.config.get('APP_SQL_USERNAME'))


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('title')
    op.drop_index(op.f('ix_owner_email'), table_name='owner')
    op.drop_table('owner')
    op.drop_table('conveyancer')
    op.drop_table('address')
    op.drop_table('segment') # added by me
    # ### end Alembic commands ###
