def upgrade():
    exec_sql_file('bd7c504431e9-upgrade.sql')
    op.create_table('registry_item',
        sa.Column('id', postgresql.UUID, nullable=False, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('registry_id', postgresql.UUID, nullable=False),
        sa.Column('meta', postgresql.JSONB(), nullable=True),
        sa.Column('data', postgresql.JSONB(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.ForeignKeyConstraint(name='fkey_item_registry', columns=['registry_id'], refcolumns=['datastore.registry.id']),
        schema='datastore'
    )
    create_json_exists('meta', 'status', 'registry_item', 'datastore')
    create_json_index('meta', 'status', 'registry_item', 'datastore')
    create_json_exists('data', 'itemTitle', 'registry_item', 'datastore')
    create_json_index('data', 'itemTitle', 'registry_item', 'datastore')
def upgrade():

    op.execute('CREATE SCHEMA datastore')

    op.create_table('data_element',
        sa.Column('id', postgresql.UUID, nullable=False, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('name', sa.Text(), nullable=False),
        sa.Column('data', postgresql.JSONB(), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name'),
        schema='datastore'
    )

    op.create_table('object_schema',
        sa.Column('id', postgresql.UUID, nullable=False, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('name', sa.Text(), nullable=False),
        sa.Column('display_name', sa.Text(), nullable=True),
        sa.Column('data', postgresql.JSONB(), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name'),
        schema='datastore'
    )
    op.create_table('registry',
        sa.Column('id', postgresql.UUID, nullable=False, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('user', postgresql.UUID, nullable=False),
        sa.Column('meta', postgresql.JSONB(), nullable=True),
        sa.Column('data', postgresql.JSONB(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.ForeignKeyConstraint(name='fkey_registry_user', columns=['user'], refcolumns=['auth._user.id']),
        schema='datastore'
    )
    create_json_exists('meta', 'created', 'registry', 'datastore')
    create_json_exists('meta', 'status', 'registry', 'datastore')
    create_json_exists('data', 'itemTitle', 'registry', 'datastore')
    create_json_index('meta', 'status', 'registry', 'datastore')
    create_json_index('data', 'itemTitle', 'registry', 'datastore')

    exec_sql_file('a2af8d8c9676-populate-schema-elements.sql')
def upgrade():
    op.execute("CREATE SCHEMA auth")
    op.create_table(
        "_user",
        sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
        sa.Column("meta", postgresql.JSONB(), nullable=True),
        sa.Column("data", postgresql.JSONB(), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        schema="auth",
    )
    create_json_exists("meta", "created", "_user", "auth")
    create_json_index("data", "displayName", "_user", "auth", unique=True)
    create_json_index("data", "emailAddress", "_user", "auth", unique=True)
    create_json_exists("data", "displayName", "_user", "auth")
    create_json_minlen("data", "displayName", "_user", "auth", 4)
    create_json_exists("data", "emailAddress", "_user", "auth")
    create_json_exists("data", "firstName", "_user", "auth")
    create_json_exists("data", "lastName", "_user", "auth")
    create_json_exists("data", "city", "_user", "auth")
    create_json_exists("data", "state", "_user", "auth")
    create_json_minlen("data", "state", "_user", "auth", 2)
    create_json_maxlen("data", "state", "_user", "auth", 2)
    create_json_exists("meta", "hash", "_user", "auth")
    create_json_minlen("meta", "hash", "_user", "auth", 8)

    op.create_table(
        "_roles",
        sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
        sa.Column("created", TIMESTAMP, server_default=func.now(), nullable=True),
        sa.Column("name", sa.Text(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("name"),
        schema="auth",
    )

    op.create_table(
        "_permissions",
        sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
        sa.Column("created", TIMESTAMP, server_default=func.now(), nullable=True),
        sa.Column("name", sa.Text(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("name"),
        schema="auth",
    )

    op.create_table(
        "_role_permissions",
        sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
        sa.Column("role", postgresql.UUID, nullable=False),
        sa.Column("permission", postgresql.UUID, nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(name="fkey_roleperms_role", columns=["role"], refcolumns=["auth._roles.id"]),
        sa.ForeignKeyConstraint(
            name="fkey_roleperms_perms", columns=["permission"], refcolumns=["auth._permissions.id"]
        ),
        schema="auth",
    )

    op.create_table(
        "_user_roles",
        sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
        sa.Column("user_id", postgresql.UUID, nullable=False),
        sa.Column("role", postgresql.UUID, nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(name="fkey_userroles_role", columns=["role"], refcolumns=["auth._roles.id"]),
        sa.ForeignKeyConstraint(name="fkey_userroles_user", columns=["user_id"], refcolumns=["auth._user.id"]),
        schema="auth",
    )
    exec_sql_file("e4b39da215a9-populate-auth-tables.sql")
def downgrade():
    exec_sql_file('bd7c504431e9-downgrade.sql')