"""create_eodp_tables

Revision ID: 12ab4670015f
Revises: 1b84a49aa4e2
Create Date: 2020-05-04 14:55:50.362346

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

# revision identifiers, used by Alembic.
revision = '12ab4670015f'
down_revision = '1b84a49aa4e2'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('taxa',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('holes',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('site_id', sa.Integer(), nullable=True),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('latitude', sa.Float(), nullable=True),
    sa.Column('longitude', sa.Float(), nullable=True),
    sa.Column('penetration_dsf', sa.Float(), nullable=True),
    sa.Column('cored_interval', sa.Float(), nullable=True),
    sa.Column('recovered_length', sa.Float(), nullable=True),
    sa.Column('recovery_percent', sa.Float(), nullable=True),
    sa.Column('drilled_interval', sa.Float(), nullable=True),
    sa.Column('drilled_intervals', sa.Integer(), nullable=True),
    sa.Column('total_cores', sa.Integer(), nullable=True),
    sa.Column('apc_cores', sa.Integer(), nullable=True),
    sa.Column('hlapc_cores', sa.Integer(), nullable=True),
    sa.Column('xcb_cores', sa.Integer(), nullable=True),
    sa.Column('rcb_cores', sa.Integer(), nullable=True),
    sa.Column('other_cores', sa.Integer(), nullable=True),
    sa.Column('date_started', sa.DateTime(), nullable=True),
    sa.Column('date_finished', sa.DateTime(), nullable=True),
    sa.Column('time_on_hole', sa.Float(), nullable=True),
    sa.Column('comments', sa.Text(), nullable=True),
    sa.Column('seafloor_depth_drf', sa.Float(), nullable=True),
    sa.Column('seafloor_depth_estimation_method', sa.String(), nullable=True),
    sa.Column('rig_floor_to_sea_level', sa.Float(), nullable=True),
    sa.ForeignKeyConstraint(['site_id'], ['sites.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('cores',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('hole_id', sa.Integer(), nullable=True),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('type', sa.String(), nullable=True),
    sa.Column('top_depth_drilled_dsf', sa.Float(), nullable=True),
    sa.Column('bottom_depth_drilled_dsf', sa.Float(), nullable=True),
    sa.Column('advanced', sa.Float(), nullable=True),
    sa.Column('recovered_length', sa.Float(), nullable=True),
    sa.Column('curated_length', sa.Float(), nullable=True),
    sa.Column('top_depth_cored_csf', sa.Float(), nullable=True),
    sa.Column('bottom_depth_recovered', sa.Float(), nullable=True),
    sa.Column('recovery', sa.Float(), nullable=True),
    sa.Column('time_on_deck', sa.DateTime(), nullable=True),
    sa.Column('sections', sa.Integer(), nullable=True),
    sa.Column('label_id', sa.String(), nullable=True),
    sa.ForeignKeyConstraint(['hole_id'], ['holes.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('sections',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('core_id', sa.Integer(), nullable=True),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('recovered_length', sa.Float(), nullable=True),
    sa.Column('curated_length', sa.Float(), nullable=True),
    sa.Column('top_depth_csf_a', sa.Float(), nullable=True),
    sa.Column('bottom_depth_csf_a', sa.Float(), nullable=True),
    sa.Column('top_depth_csf_b', sa.Float(), nullable=True),
    sa.Column('bottom_depth_csf_b', sa.Float(), nullable=True),
    sa.Column('top_depth_ccsf', sa.Float(), nullable=True),
    sa.Column('bottom_depth_ccsf', sa.Float(), nullable=True),
    sa.Column('text_id_section', sa.String(), nullable=True),
    sa.Column('text_id_archive_half', sa.String(), nullable=True),
    sa.Column('text_id_working_half', sa.String(), nullable=True),
    sa.Column('catwalk_samples', sa.Integer(), nullable=True),
    sa.Column('section_half_samples', sa.Integer(), nullable=True),
    sa.Column('comments', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['core_id'], ['cores.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('samples',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('section_id', sa.Integer(), nullable=True),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('aw', sa.String(), nullable=True),
    sa.Column('original_sample_id', sa.Integer(), nullable=True),
    sa.Column('top', sa.Float(), nullable=True),
    sa.Column('bottom', sa.Float(), nullable=True),
    sa.Column('top_depth', sa.Float(), nullable=True),
    sa.Column('bottom_depth', sa.Float(), nullable=True),
    sa.Column('principal_lithology_prefix', sa.String(), nullable=True),
    sa.Column('principal_lithology_name', sa.String(), nullable=True),
    sa.Column('principal_lithology_suffix', sa.String(), nullable=True),
    sa.Column('minor_lithology_prefix', sa.String(), nullable=True),
    sa.Column('minor_lithology_name', sa.String(), nullable=True),
    sa.Column('minor_lithology_suffix', sa.String(), nullable=True),
    sa.Column('sampled_date', sa.DateTime(), nullable=True),
    sa.Column('raw_data', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.ForeignKeyConstraint(['section_id'], ['sections.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('samples_taxa',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('sample_id', sa.Integer(), nullable=True),
    sa.Column('taxon_id', sa.Integer(), nullable=True),
    sa.Column('code', sa.String(), nullable=True),
    sa.ForeignKeyConstraint(['sample_id'], ['samples.id'], ),
    sa.ForeignKeyConstraint(['taxon_id'], ['taxa.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('samples_taxa')
    op.drop_table('samples')
    op.drop_table('sections')
    op.drop_table('cores')
    op.drop_table('holes')
    op.drop_table('taxa')
    # ### end Alembic commands ###
