"""Initiate tables

Revision ID: 1
Revises: 
Create Date: 2021-02-10 14:26:58.397210

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

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


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        'marker',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.Column('fluor', sa.String(), nullable=True),
        sa.Column('anti', sa.String(), nullable=True),
        sa.Column('duplicate', sa.String(), nullable=True),
        sa.Column('entry_at', sa.DateTime(timezone=True),
                  server_default=sa.func.current_timestamp(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_marker_name', 'marker',
                    [sa.text('lower(name)'), sa.text('lower(fluor)'),
                     sa.text('lower(anti)'), sa.text('lower(duplicate)')],
                    unique=True)
    op.create_table(
        'sample',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.Column('tag', sa.String(), nullable=True),
        sa.Column('annotation', sa.String(), nullable=True),
        sa.Column('entry_at', sa.DateTime(timezone=True),
                  server_default=sa.func.current_timestamp(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_sample_name', 'sample',
                    [sa.text('lower(name)'), sa.text('lower(tag)')], unique=True)
    op.create_table(
        'cell',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('sample_id', sa.Integer(), nullable=False),
        sa.Column('sample_cell_id', sa.Integer(), nullable=True),
        sa.Column('entry_at', sa.DateTime(timezone=True),
                  server_default=sa.func.current_timestamp(), nullable=True),
        sa.Column('features', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column('area', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('column_centroid', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('eccentricity', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('extent', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('major_axis_length', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('minor_axis_length', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('orientation', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('row_centroid', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('solidity', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('x_centroid', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.Column('y_centroid', sa.Numeric(precision=15, scale=4), nullable=True),
        sa.ForeignKeyConstraint(['sample_id'], ['sample.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_table(
        'marker_alias',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.Column('marker_id', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['marker_id'], ['marker.id'],
                                onupdate='CASCADE', ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_marker_alias', 'marker_alias',
                    [sa.text('lower(name)')], unique=True)
    op.create_table(
        'sample_marker_association',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('sample_id', sa.Integer(), nullable=True),
        sa.Column('marker_id', sa.Integer(), nullable=True),
        sa.Column('channel_number', sa.Integer(), nullable=True),
        sa.Column('cycle_number', sa.Integer(), nullable=True),
        sa.Column('entry_at', sa.DateTime(timezone=True),
                  server_default=sa.func.current_timestamp(), nullable=True),
        sa.ForeignKeyConstraint(['marker_id'], ['marker.id'],
                                onupdate='CASCADE'),
        sa.ForeignKeyConstraint(['sample_id'], ['sample.id'],
                                onupdate='CASCADE', ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_sample_marker_associate', 'sample_marker_association',
                    ['sample_id', 'marker_id', 'channel_number'], unique=True)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_sample_marker_associate',
                  table_name='sample_marker_association')
    op.drop_table('sample_marker_association')
    op.drop_index('ix_marker_alias', table_name='marker_alias')
    op.drop_table('marker_alias')
    op.drop_table('cell')
    op.drop_index('ix_sample_name', table_name='sample')
    op.drop_table('sample')
    op.drop_index('ix_marker_name', table_name='marker')
    op.drop_table('marker')
    # ### end Alembic commands ###
