def upgrade():

    op.create_table('monthly_billing',
                    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.Column('month', sa.String(), nullable=False),
                    sa.Column('year', sa.Float(), nullable=False),
                    sa.Column('notification_type',
                              postgresql.ENUM('email', 'sms', 'letter', name='notification_type', create_type=False),
                              nullable=False),
                    sa.Column('monthly_totals', postgresql.JSON(), nullable=False),
                    sa.Column('updated_at', sa.DateTime, nullable=False),
                    sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
                    sa.PrimaryKeyConstraint('id')
                    )
    op.create_index(op.f('ix_monthly_billing_service_id'), 'monthly_billing', ['service_id'], unique=False)
    op.create_index(op.f('uix_monthly_billing'), 'monthly_billing', ['service_id', 'month', 'year', 'notification_type'], unique=True) 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('monthly_billing',
    sa.Column('id', postgresql.UUID(), autoincrement=False, nullable=False),
    sa.Column('service_id', postgresql.UUID(), autoincrement=False, nullable=False),
    sa.Column('notification_type', postgresql.ENUM('email', 'sms', 'letter', name='notification_type'), autoincrement=False, nullable=False),
    sa.Column('monthly_totals', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=False),
    sa.Column('updated_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
    sa.Column('start_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
    sa.Column('end_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['service_id'], ['services.id'], name='monthly_billing_service_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='monthly_billing_pkey'),
    sa.UniqueConstraint('service_id', 'start_date', 'notification_type', name='uix_monthly_billing')
    )
    op.create_index('ix_monthly_billing_service_id', 'monthly_billing', ['service_id'], unique=False)
    # ### end Alembic commands ### 

def test_json(self):
        Base = declarative_base()

        class J(Base):
            __tablename__ = "j"
            id = Column("id", Integer, primary_key=True)
            json = Column("_json", JSON, default={})
            field = index_property("json", "field")

        j = J(json={"a": 1, "b": 2})
        assert_raises(AttributeError, lambda: j.field)
        j.field = "test"
        eq_(j.field, "test")
        eq_(j.json, {"a": 1, "b": 2, "field": "test"})

        j2 = J(field="test")
        eq_(j2.json, {"field": "test"})
        eq_(j2.field, "test") 

def test_get_default_value(self):
        Base = declarative_base()

        class J(Base):
            __tablename__ = "j"
            id = Column(Integer, primary_key=True)
            json = Column(JSON, default={})
            default = index_property("json", "field", default="default")
            none = index_property("json", "field", default=None)

        j = J()
        assert j.json is None

        assert j.default == "default"
        assert j.none is None
        j.json = {}
        assert j.default == "default"
        assert j.none is None
        j.default = None
        assert j.default is None
        assert j.none is None
        j.none = 10
        assert j.default == 10
        assert j.none == 10 

def bind_expression(self, value):
        return cast(value, sqltypes.JSON) 

def bind_expression(self, value):
        return cast(value, JSON) 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('feed_post_meta',
    sa.Column('id', sa.BigInteger(), nullable=False),
    sa.Column('contentid', sa.Text(), nullable=False),
    sa.Column('meta', JSON(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_feed_post_meta_contentid'), 'feed_post_meta', ['contentid'], unique=True)
    ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('matched_profile_pictures', postgresql.JSON(astext_type=sa.Text()), nullable=True))
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('ussd_menu',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('authorising_user_id', sa.Integer(), nullable=True),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('updated', sa.DateTime(), nullable=True),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('description', sa.String(), nullable=True),
    sa.Column('parent_id', sa.Integer(), nullable=True),
    sa.Column('display_key', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_ussd_menu_name'), 'ussd_menu', ['name'], unique=True)
    op.create_table('ussd_session',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('authorising_user_id', sa.Integer(), nullable=True),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('updated', sa.DateTime(), nullable=True),
    sa.Column('session_id', sa.String(), nullable=False),
    sa.Column('service_code', sa.String(), nullable=False),
    sa.Column('msisdn', sa.String(), nullable=False),
    sa.Column('user_input', sa.String(), nullable=True),
    sa.Column('state', sa.String(), nullable=False),
    sa.Column('session_data', postgresql.JSON(astext_type=sa.Text()), nullable=True),
    sa.Column('ussd_menu_id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
    sa.ForeignKeyConstraint(['ussd_menu_id'], ['ussd_menu.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_ussd_session_session_id'), 'ussd_session', ['session_id'], unique=True)
    op.drop_index('ix_ussd_sessions_session_id', table_name='ussd_sessions')
    op.drop_table('ussd_sessions')
    op.drop_index('ix_ussd_menus_name', table_name='ussd_menus')
    op.drop_table('ussd_menus')
    # ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('ussd_menus',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('authorising_user_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('created', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('updated', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('name', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('description', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('parent_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('display_key', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('id', name='ussd_menus_pkey')
    )
    op.create_index('ix_ussd_menus_name', 'ussd_menus', ['name'], unique=True)
    op.create_table('ussd_sessions',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('authorising_user_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('created', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('updated', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('session_id', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('service_code', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('msisdn', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('user_input', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('ussd_menu_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('state', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('session_data', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name='ussd_sessions_pkey')
    )
    op.create_index('ix_ussd_sessions_session_id', 'ussd_sessions', ['session_id'], unique=True)
    op.drop_index(op.f('ix_ussd_session_session_id'), table_name='ussd_session')
    op.drop_table('ussd_session')
    op.drop_index(op.f('ix_ussd_menu_name'), table_name='ussd_menu')
    op.drop_table('ussd_menu')
    # ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('custom_attributes', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True))
    op.drop_column('user', 'preferred_language')
    op.drop_table('custom_attribute_user_storage')
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('transfer_usage',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('authorising_user_id', sa.Integer(), nullable=True),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('updated', sa.DateTime(), nullable=True),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('translations', postgresql.JSON(astext_type=sa.Text()), nullable=True),
    sa.Column('icon', sa.String(), nullable=True),
    sa.Column('priority', sa.Integer(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('kyc_application', sa.Column('kyc_actions', postgresql.JSON(astext_type=sa.Text()), nullable=True))
    op.add_column('kyc_application', sa.Column('trulioo_id', sa.String(), nullable=True))
    op.add_column('kyc_application', sa.Column('user_id', sa.Integer(), nullable=True))
    op.create_foreign_key(None, 'kyc_application', 'user', ['user_id'], ['id'])
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('custom_attributes', postgresql.JSON(astext_type=sa.Text()), nullable=True))
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('ussd_menus',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('authorising_user_id', sa.Integer(), nullable=True),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('updated', sa.DateTime(), nullable=True),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('description', sa.String(), nullable=True),
    sa.Column('parent_id', sa.Integer(), nullable=True),
    sa.Column('display_text_en', sa.String(), nullable=False),
    sa.Column('display_text_sw', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_ussd_menus_name'), 'ussd_menus', ['name'], unique=True)
    op.create_table('ussd_sessions',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('authorising_user_id', sa.Integer(), nullable=True),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('updated', sa.DateTime(), nullable=True),
    sa.Column('session_id', sa.String(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('service_code', sa.String(), nullable=False),
    sa.Column('msisdn', sa.String(), nullable=False),
    sa.Column('user_input', sa.String(), nullable=True),
    sa.Column('ussd_menu_id', sa.Integer(), nullable=False),
    sa.Column('state', sa.String(), nullable=False),
    sa.Column('sessions_data', postgresql.JSON(astext_type=sa.Text()), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_ussd_sessions_session_id'), 'ussd_sessions', ['session_id'], unique=True)
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('saved_filter',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('authorising_user_id', sa.Integer(), nullable=True),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('updated', sa.DateTime(), nullable=True),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('filter', postgresql.JSON(astext_type=sa.Text()), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.add_column('user', sa.Column('_is_view', sa.Boolean(), nullable=True))
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('ussd_sessions', sa.Column('session_data', postgresql.JSON(astext_type=sa.Text()), nullable=True))
    op.drop_column('ussd_sessions', 'sessions_data')
    # ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('ussd_sessions', sa.Column('sessions_data', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True))
    op.drop_column('ussd_sessions', 'session_data')
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('credit_transfer', sa.Column('transfer_use', postgresql.JSON(astext_type=sa.Text()), nullable=True))
    # ### end Alembic commands ### 

def is_column_json(self, name: str) -> bool:
        """ Is the column a JSON column """
        raise NotImplementedError 

def __getitem__(self, name: str) -> Union[ColumnProperty, BinaryExpression]:
        column_name, path = _dot_notation(name)
        col = super(DotColumnsBag, self).__getitem__(column_name)
        # JSON path
        if path:
            if self.is_column_json(column_name):
                col = col[path].astext
            else:
                raise KeyError(name)
        return col 

def get_column_name(self, name: str) -> str:
        """ Get a column name, not a JSON path """
        return get_plain_column_name(name) 

def get_column(self, name: str) -> ColumnProperty:
        """ Get a column, not a JSON path """
        return self[get_plain_column_name(name)] 

def get_invalid_names(self, names: Iterable[str]) -> Set[str]:
        # First, validate easy names
        invalid = super(DotColumnsBag, self).get_invalid_names(names)  #type: set
        # Next, among those invalid ones, give those with dot-notation a second change: they
        # might be JSON columns' fields!
        invalid -= {name
                    for name in invalid
                    if self.is_column_json(name)
                    }
        return invalid 

def __contains__(self, name: str) -> bool:
        # Simple
        if name in self._names:
            return True
        # It might be a JSON column. Try it
        if get_plain_column_name(name) in self._json_column_names:
            return True
        # Nope. Nothing worked
        return False 

def get_invalid_names(self, names: Iterable[str]) -> Set[str]:
        # This method is copy-paste from ColumnsBag
        # First, validate easy names
        invalid = super(CombinedBag, self).get_invalid_names(names)  # type: set
        # Next, among those invalid ones, give those with dot-notation a second change: they
        # might be JSON columns' fields!
        invalid -= {name
                    for name in invalid
                    if get_plain_column_name(name) in self._json_column_names
                    }
        return invalid 

def _is_column_json(col: MapperProperty) -> bool:
    """ Is the column a PostgreSql JSON column? """
    return isinstance(_get_column_type(col), (pg.JSON, pg.JSONB)) 

def _dot_notation(name: str) -> Tuple[str, List[str]]:
    """ Split a property name that's using dot-notation.

    This is used to navigate the internals of JSON types:

        "json_column.property.property"
    """
    path = name.split('.')
    return path[0], path[1:] 

def build_trafaret(sa_type, **kwargs):

    if isinstance(sa_type, sa.sql.sqltypes.Enum):
        trafaret = t.Enum(*sa_type.enums, **kwargs)

    # check for Text should be before String
    elif isinstance(sa_type, sa.sql.sqltypes.Text):
        trafaret = t.String(**kwargs)

    elif isinstance(sa_type, sa.sql.sqltypes.String):
        trafaret = t.String(max_length=sa_type.length, **kwargs)

    elif isinstance(sa_type, sa.sql.sqltypes.Integer):
        trafaret = t.ToInt(**kwargs)

    elif isinstance(sa_type, sa.sql.sqltypes.Float):
        trafaret = t.ToFloat(**kwargs)

    elif isinstance(sa_type, sa.sql.sqltypes.DateTime):
        trafaret = DateTime(**kwargs)  # RFC3339

    elif isinstance(sa_type, sa.sql.sqltypes.Date):
        trafaret = DateTime(**kwargs)  # RFC3339

    elif isinstance(sa_type, sa.sql.sqltypes.Boolean):
        trafaret = t.ToBool(**kwargs)

    # Add PG related JSON and ARRAY
    elif isinstance(sa_type, postgresql.JSON):
        trafaret = AnyDict | t.List(AnyDict)

    # Add PG related JSON and ARRAY
    elif isinstance(sa_type, postgresql.ARRAY):
        item_trafaret = build_trafaret(sa_type.item_type)
        trafaret = t.List(item_trafaret)

    else:
        type_ = str(sa_type)
        msg = 'Validator for type {} not implemented'.format(type_)
        raise NotImplementedError(msg)
    return trafaret 

def test_get_type_of_fields(resources):
    table = sa.Table(
        'Test', sa.MetaData(),
        sa.Column('integer', sa.Integer, primary_key=True),
        sa.Column('text', sa.Text),
        sa.Column('float', sa.Float),
        sa.Column('date', sa.Date),
        sa.Column('boolean', sa.Boolean),
        sa.Column('json', postgresql.JSON),
    )
    fields = ['integer', 'text', 'float', 'date', 'boolean', 'json', ]

    data_type_fields = resources.get_type_of_fields(fields, table)
    expected_type_fields = {
        'integer': rc.TEXT_FIELD.value,
        'text': rc.TEXT_FIELD.value,
        'float': rc.NUMBER_FIELD.value,
        'date': rc.DATE_FIELD.value,
        'boolean': rc.BOOLEAN_FIELD.value,
        'json': rc.JSON_FIELD.value,
    }

    assert data_type_fields == expected_type_fields

    fields = None
    data_type_fields = resources.get_type_of_fields(fields, table)
    expected_type_fields = {
        'integer': rc.TEXT_FIELD.value,
    }

    assert data_type_fields == expected_type_fields


# TODO: added Mongo