"""create experiments table
Revision ID: 97659da4900e
Revises: a7841c3b04d0
Create Date: 2021-11-15 18:18:47.732081
"""
import sqlalchemy as sa
import sqlalchemy_utils
from alembic import op


# revision identifiers, used by Alembic
revision = "97659da4900e"
down_revision = "a7841c3b04d0"
branch_labels = None
depends_on = None


def create_updated_at_trigger() -> None:
    op.execute(
        """
        CREATE OR REPLACE FUNCTION update_updated_at_column()
            RETURNS TRIGGER AS
        $$
        BEGIN
            NEW.updated_at = now();
            RETURN NEW;
        END;
        $$ language 'plpgsql';
        """
    )


def upgrade() -> None:
    create_updated_at_trigger()
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "experiments",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("organization_name", sa.Text(), nullable=False),
        sa.Column("model_name", sa.Text(), nullable=False),
        sa.Column("creator", sa.Text(), nullable=False),
        sa.Column("coordinator_ip", sqlalchemy_utils.types.ip_address.IPAddressType(length=50), nullable=True),
        sa.Column("coordinator_port", sa.Integer(), nullable=True),
        sa.Column("auth_server_public_key", sa.LargeBinary(), nullable=True),
        sa.Column("auth_server_private_key", sa.LargeBinary(), nullable=True),
        sa.Column("created_at", sa.TIMESTAMP(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.TIMESTAMP(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_experiments_model_name"), "experiments", ["model_name"], unique=False)
    op.create_index(op.f("ix_experiments_organization_name"), "experiments", ["organization_name"], unique=False)
    op.execute(
        """
        CREATE TRIGGER update_experiments_modtime
            BEFORE UPDATE
            ON experiments
            FOR EACH ROW
        EXECUTE PROCEDURE update_updated_at_column();
        """
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f("ix_experiments_organization_name"), table_name="experiments")
    op.drop_index(op.f("ix_experiments_model_name"), table_name="experiments")
    op.drop_index(op.f("ix_experiments_creator"), table_name="experiments")
    op.drop_table("experiments")
    op.execute("DROP FUNCTION update_updated_at_column")
    # ### end Alembic commands ###
