""" Adding SQL helper functions

Revision ID: 0d570ef8292d
Revises: 3b9dffe063a6
Create Date: 2020-07-29 18:22:24.057600

"""

# revision identifiers, used by Alembic.
revision = '0d570ef8292d'
down_revision = '3b9dffe063a6'
branch_labels = None
depends_on = None

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

def upgrade(engine_name):
    globals()["upgrade_%s" % engine_name]()


def downgrade(engine_name):
    globals()["downgrade_%s" % engine_name]()





def upgrade_data_broker():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute("""
        CREATE OR REPLACE function is_date(str text) returns boolean AS $$
        BEGIN
            perform CAST(str AS DATE);
            return TRUE;
        EXCEPTION WHEN others THEN
            return FALSE;
        END;
        $$ LANGUAGE plpgsql;
    """)
    op.execute("""
        CREATE OR REPLACE function is_zero(NUMERIC) returns INTEGER AS $$
        BEGIN
            perform CAST($1 AS NUMERIC);
            CASE WHEN $1 <> 0
                THEN return 1;
                ELSE return 0;
            END CASE;
        EXCEPTION WHEN others THEN
            return 0;
        END;
        $$ LANGUAGE plpgsql;
    """)
    # ### end Alembic commands ###


def downgrade_data_broker():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute(""" DROP FUNCTION IF EXISTS is_date(TEXT) """)
    op.execute(""" DROP FUNCTION IF EXISTS is_zero(NUMERIC) """)
    # ### end Alembic commands ###

