"""changed db english

Revision ID: 122110744562
Revises: bb7499198bfa
Create Date: 2018-05-19 13:42:30.381840

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

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


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('chat_status_list',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('description', sa.String(length=15), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('crime_list',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('description', sa.String(length=50), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('hour_list',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('description', sa.String(length=15), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('map',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('polygon', sa.JSON(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('reference_info_list',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('description', sa.String(length=20), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('report_status_list',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('description', sa.String(length=15), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('sex_list',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('description', sa.String(length=15), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('user',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('username', sa.String(length=20), nullable=True),
    sa.Column('password', sa.String(length=128), nullable=True),
    sa.Column('email', sa.String(length=100), nullable=True),
    sa.Column('is_admin', sa.Boolean(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email')
    )
    op.create_index(op.f('ix_user_username'), 'user', ['username'], unique=True)
    op.create_table('zone',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=35), nullable=True),
    sa.Column('coordinates', sa.JSON(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('person',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=50), nullable=True),
    sa.Column('birthdate', sa.DateTime(), nullable=True),
    sa.Column('sex_id', sa.Integer(), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['sex_id'], ['sex_list.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('victim',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=50), nullable=True),
    sa.Column('age', sa.Integer(), nullable=True),
    sa.Column('sex_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['sex_id'], ['sex_list.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('report',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('date', sa.DateTime(), nullable=True),
    sa.Column('crime_id', sa.Integer(), nullable=True),
    sa.Column('agent_id', sa.Integer(), nullable=True),
    sa.Column('crime_hour_id', sa.Integer(), nullable=True),
    sa.Column('latitude', sa.Float(), nullable=True),
    sa.Column('longitude', sa.Float(), nullable=True),
    sa.Column('zone', sa.Integer(), nullable=True),
    sa.Column('status_id', sa.Integer(), nullable=True),
    sa.Column('details', sa.Text(), nullable=True),
    sa.Column('victim_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['agent_id'], ['user.id'], ),
    sa.ForeignKeyConstraint(['crime_hour_id'], ['hour_list.id'], ),
    sa.ForeignKeyConstraint(['crime_id'], ['crime_list.id'], ),
    sa.ForeignKeyConstraint(['status_id'], ['report_status_list.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
    sa.ForeignKeyConstraint(['victim_id'], ['victim.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.add_column('chat', sa.Column('agent_id', sa.Integer(), nullable=True))
    op.add_column('chat', sa.Column('request_time', sa.DateTime(), nullable=True))
    op.add_column('chat', sa.Column('response_time', sa.DateTime(), nullable=True))
    op.add_column('chat', sa.Column('status_id', sa.Integer(), nullable=True))
    op.add_column('chat', sa.Column('user_id', sa.Integer(), nullable=True))
    op.drop_constraint('chat_operador_id_fkey', 'chat', type_='foreignkey')
    op.drop_constraint('chat_usuario_id_fkey', 'chat', type_='foreignkey')
    op.drop_constraint('chat_estado_id_fkey', 'chat', type_='foreignkey')
    op.create_foreign_key(None, 'chat', 'chat_status_list', ['status_id'], ['id'])
    op.create_foreign_key(None, 'chat', 'user', ['agent_id'], ['id'])
    op.create_foreign_key(None, 'chat', 'user', ['user_id'], ['id'])
    op.drop_column('chat', 'operador_id')
    op.drop_column('chat', 'hora_respuesta')
    op.drop_column('chat', 'estado_id')
    op.drop_column('chat', 'usuario_id')
    op.drop_column('chat', 'hora_peticion')
    op.drop_table('mapa')
    op.drop_table('sector')
    op.drop_table('reporte')
    op.drop_table('difunto')
    op.drop_index('ix_persona_username', table_name='persona')
    op.drop_table('operador')
    op.drop_table('usuario')
    op.drop_table('persona')
    op.drop_table('catalogo_horario')
    op.drop_table('catalogo_fuente_info')
    op.drop_table('catalogo_estado_chat')
    op.drop_table('catalogo_estado_reporte')
    op.drop_table('catalogo_delito')
    op.drop_table('catalogo_sexo')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('chat', sa.Column('hora_peticion', postgresql.TIMESTAMP(), autoincrement=False, nullable=True))
    op.add_column('chat', sa.Column('usuario_id', sa.INTEGER(), autoincrement=False, nullable=True))
    op.add_column('chat', sa.Column('estado_id', sa.INTEGER(), autoincrement=False, nullable=True))
    op.add_column('chat', sa.Column('hora_respuesta', postgresql.TIMESTAMP(), autoincrement=False, nullable=True))
    op.add_column('chat', sa.Column('operador_id', sa.INTEGER(), autoincrement=False, nullable=True))
    op.drop_constraint(None, 'chat', type_='foreignkey')
    op.drop_constraint(None, 'chat', type_='foreignkey')
    op.drop_constraint(None, 'chat', type_='foreignkey')
    op.create_foreign_key('chat_estado_id_fkey', 'chat', 'catalogo_estado_chat', ['estado_id'], ['id'])
    op.create_foreign_key('chat_usuario_id_fkey', 'chat', 'usuario', ['usuario_id'], ['id'])
    op.create_foreign_key('chat_operador_id_fkey', 'chat', 'operador', ['operador_id'], ['id'])
    op.drop_column('chat', 'user_id')
    op.drop_column('chat', 'status_id')
    op.drop_column('chat', 'response_time')
    op.drop_column('chat', 'request_time')
    op.drop_column('chat', 'agent_id')
    op.create_table('catalogo_sexo',
    sa.Column('id', sa.INTEGER(), server_default=sa.text("nextval('catalogo_sexo_id_seq'::regclass)"), nullable=False),
    sa.Column('descripcion', sa.VARCHAR(length=15), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='catalogo_sexo_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('catalogo_estado_chat',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('descripcion', sa.VARCHAR(length=15), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='catalogo_estado_chat_pkey')
    )
    op.create_table('reporte',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('usuario_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('fecha', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('delito_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('operador_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('hora_delito_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('sector', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('estado_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('detalles', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('difunto_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('latitud', postgresql.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('longitud', postgresql.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['delito_id'], ['catalogo_delito.id'], name='reporte_delito_id_fkey'),
    sa.ForeignKeyConstraint(['difunto_id'], ['difunto.id'], name='reporte_difunto_id_fkey'),
    sa.ForeignKeyConstraint(['estado_id'], ['catalogo_estado_reporte.id'], name='reporte_estado_id_fkey'),
    sa.ForeignKeyConstraint(['hora_delito_id'], ['catalogo_horario.id'], name='reporte_hora_delito_id_fkey'),
    sa.ForeignKeyConstraint(['operador_id'], ['operador.id'], name='reporte_operador_id_fkey'),
    sa.ForeignKeyConstraint(['usuario_id'], ['usuario.id'], name='reporte_usuario_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='reporte_pkey')
    )
    op.create_table('catalogo_estado_reporte',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('descripcion', sa.VARCHAR(length=15), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='catalogo_estado_reporte_pkey')
    )
    op.create_table('catalogo_delito',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('descripcion', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='catalogo_delito_pkey')
    )
    op.create_table('mapa',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('poligono', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='mapa_pkey')
    )
    op.create_table('sector',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('nombre', sa.VARCHAR(length=35), autoincrement=False, nullable=True),
    sa.Column('coordenadas', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='sector_pkey')
    )
    op.create_table('usuario',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('fecha_nac', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('sexo_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('correo', sa.VARCHAR(length=100), autoincrement=False, nullable=True),
    sa.Column('persona_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['persona_id'], ['persona.id'], name='usuario_persona_id_fkey'),
    sa.ForeignKeyConstraint(['sexo_id'], ['catalogo_sexo.id'], name='usuario_sexo_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='usuario_pkey'),
    sa.UniqueConstraint('correo', name='usuario_correo_key')
    )
    op.create_table('persona',
    sa.Column('id', sa.INTEGER(), server_default=sa.text("nextval('persona_id_seq'::regclass)"), nullable=False),
    sa.Column('nombre', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('username', sa.VARCHAR(length=20), autoincrement=False, nullable=True),
    sa.Column('contrasena', sa.VARCHAR(length=128), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='persona_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_index('ix_persona_username', 'persona', ['username'], unique=True)
    op.create_table('difunto',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('nombre', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('edad', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('sexo_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['sexo_id'], ['catalogo_sexo.id'], name='difunto_sexo_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='difunto_pkey')
    )
    op.create_table('catalogo_fuente_info',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('descripcion', sa.VARCHAR(length=20), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='catalogo_fuente_info_pkey')
    )
    op.create_table('catalogo_horario',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('descripcion', sa.VARCHAR(length=15), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='catalogo_horario_pkey')
    )
    op.create_table('operador',
    sa.Column('id', sa.INTEGER(), nullable=False),
    sa.Column('persona_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['persona_id'], ['persona.id'], name='operador_persona_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='operador_pkey')
    )
    op.drop_table('report')
    op.drop_table('victim')
    op.drop_table('person')
    op.drop_table('zone')
    op.drop_index(op.f('ix_user_username'), table_name='user')
    op.drop_table('user')
    op.drop_table('sex_list')
    op.drop_table('report_status_list')
    op.drop_table('reference_info_list')
    op.drop_table('map')
    op.drop_table('hour_list')
    op.drop_table('crime_list')
    op.drop_table('chat_status_list')
    # ### end Alembic commands ###
