"""questionnaire list sp

Revision ID: 0a7052e0253f
Revises: e968d868a097
Create Date: 2020-02-05 15:56:20.552056

"""
from alembic import op

from rdr_service.dao.alembic_utils import ReplaceableObject


# revision identifiers, used by Alembic.
revision = '0a7052e0253f'
down_revision = 'e968d868a097'
branch_labels = None
depends_on = None


sp_questionnaire_list = ReplaceableObject(
    "sp_get_questionnaire_list", """
        (IN participant_id int)
    SELECT
          qr.participant_id,
          c.value as questionnaire_name,          
          qr.created,
          qr.authored,          
          q.semantic_version,
          q.version,
          qr.language,
          qr.questionnaire_id,
          qr.questionnaire_response_id,
          c.code_id
    FROM questionnaire_response qr
      INNER JOIN questionnaire_concept qc
                 ON qr.questionnaire_id = qc.questionnaire_id
      INNER JOIN questionnaire q on q.questionnaire_id = qc.questionnaire_id and q.version = qc.questionnaire_version
      INNER JOIN code c on qc.code_id = c.code_id
    WHERE qr.participant_id = participant_id
    ORDER BY qr.authored;
""")

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


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



def upgrade_rdr():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_sp(sp_questionnaire_list)
    # ### end Alembic commands ###


def downgrade_rdr():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_sp(sp_questionnaire_list)
    # ### end Alembic commands ###


def upgrade_metrics():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade_metrics():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

def unittest_schemas():
    schemas = list()

    schemas.append("DROP PROCEDURE IF EXISTS `{0}`".format(sp_questionnaire_list.name))
    schemas.append(
        "CREATE PROCEDURE `{0}` {1}".format(sp_questionnaire_list.name, sp_questionnaire_list.sqltext)
    )

    return schemas
Create a stored proc to get the list of submitted questionnaires (#1568)