def upgrade(pyramid_env):
    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        dups = list(db.execute(
            """SELECT array_agg(id) FROM sub_graph_idea_association
                GROUP BY idea_id, sub_graph_id HAVING count(id) > 1"""))
        if dups:
            extras = list(chain(*[l[1:] for l in dups]))
            db.execute(
                'DELETE FROM sub_graph_idea_association WHERE id IN (%s)' % (
                    ','.join(extras)))
        dups = list(db.execute(
            """SELECT array_agg(id) FROM sub_graph_idea_link_association
                GROUP BY idea_link_id, sub_graph_id HAVING count(id) > 1"""))
        if dups:
            extras = list(chain(*[l[1:] for l in dups]))
            db.execute(
                'DELETE FROM sub_graph_idea_link_association WHERE id IN (%s)' % (
                    ','.join(extras)))

    with context.begin_transaction():
        op.create_unique_constraint(
            "%s_%s_sub_graph_idea_association_UNQC_idea_id_sub_graph_id" % (
                config.get('db_schema'), config.get('db_user')),
            "sub_graph_idea_association", ["idea_id", "sub_graph_id"])
        op.create_unique_constraint(
            "%s_%s_sub_graph_idea_link_association_UNQC_idea_link_id_sub_graph_id" % (
                config.get('db_schema'), config.get('db_user')),
            "sub_graph_idea_link_association", ["idea_link_id", "sub_graph_id"])

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.drop_index("%s_%s_ix_social_auth_code_code" % (
                config.get('db_schema'), config.get('db_user')),
            "social_auth_code")
        op.drop_constraint(
            "%s_%s_social_auth_nonce_UNQC_server_url_timestamp_salt" % (
                config.get('db_schema'), config.get('db_user')),
            "social_auth_nonce")
        op.drop_constraint(
            "%s_%s_social_auth_code_UNQC_code_email" % (
                config.get('db_schema'), config.get('db_user')),
            "social_auth_code")
        op.drop_constraint(
            "%s_%s_social_auth_association_UNQC_server_url_handle" % (
                config.get('db_schema'), config.get('db_user')),
            "social_auth_association")
        op.drop_constraint(
            "%s_%s_social_auth_account_UNQC_provider_id_uid" % (
                config.get('db_schema'), config.get('db_user')),
            "social_auth_account")
        op.drop_table('social_auth_nonce')
        op.drop_table('social_auth_code')
        op.drop_table('social_auth_association')
        op.drop_table('social_auth_account')
        op.execute("DELETE FROM abstract_agent_account"
                   " WHERE type = 'social_auth_account'")

def upgrade(pyramid_env):
    schema = config.get('db_schema')+"."+config.get('db_user')
    with context.begin_transaction():
        from assembl.models.notification import *
        #No one should have active notifications yet
        op.execute(
            '''DELETE FROM notification''')
        op.add_column('notification',
            sa.Column('sqla_type',
                      String,
                      nullable=False,
                      index=True))
        op.create_table('notification_on_post',
            sa.Column('id',
                       sa.Integer,
                       sa.ForeignKey(
                            'notification.id',
                             ondelete="CASCADE",
                             onupdate="CASCADE"),
                      primary_key=True),
            sa.Column('post_id',
                       sa.Integer,
                       sa.ForeignKey(
                            'post.id',
                             ondelete="CASCADE",
                             onupdate="CASCADE"),
                nullable=False),
            )
        op.drop_column('notification', 'event_source_type')
        op.drop_column('notification', 'event_source_object_id')

def downgrade(pyramid_env):
    from assembl import models as m
    db = m.get_session_maker()()
    with context.begin_transaction():
        op.create_table(
            "user_language_preference_temp",
            sa.Column("id", sa.Integer, primary_key=True),
            sa.Column("user_id", sa.Integer),
            sa.Column("lang_code", sa.String),
            sa.Column("preferred_order", sa.Integer),
            sa.Column("locale_id", sa.Integer),
            sa.Column("explicitly_defined", sa.Boolean, server_default="0"))

    with transaction.manager:
        from assembl.models.auth import LanguagePreferenceOrder
        op.execute("""INSERT INTO user_language_preference_temp
            (id, user_id, locale_id, preferred_order)
            SELECT id, user_id, locale_id, source_of_evidence
            FROM  user_language_preference""")
        locale_ids = db.execute(
            """SELECT DISTINCT locale_id, locale.code
            FROM user_language_preference
            JOIN locale ON (locale.id=locale_id)""")
        for locale_id, locale_name in locale_ids:
            op.execute("UPDATE user_language_preference_temp SET lang_code = '%s' WHERE locale_id = %d" % (
                locale_name, locale_id))
        op.execute("""UPDATE user_language_preference_temp
            SET explicitly_defined = 1 WHERE preferred_order = %d""" % (LanguagePreferenceOrder.Explicit,))
        op.execute("DELETE FROM user_language_preference")
        mark_changed()

    with context.begin_transaction():
        op.add_column(
            'user_language_preference', sa.Column(
                'explicitly_defined', sa.Boolean, nullable=False, server_default=TextClause("0")))
        op.add_column(
            'user_language_preference', sa.Column(
                'lang_code', sa.String(), nullable=False, server_default=""))
        op.drop_index(
            '%s_%s_user_language_preference_UNQC_user_id_locale_id' % (
                config.get('db_schema'), config.get('db_user')))


        op.create_index(
            '%s_%s_user_language_preference_UNQC_user_id_lang_code' % (
                config.get('db_schema'), config.get('db_user')),
            'user_language_preference', ['user_id', 'lang_code'], unique=True)

        op.drop_column('user_language_preference', 'source_of_evidence')
        op.drop_column('user_language_preference', 'translate_to')
        op.drop_column('user_language_preference', 'locale_id')
    with transaction.manager:
        op.execute("""INSERT INTO user_language_preference
            (id, user_id, lang_code, preferred_order, explicitly_defined)
            SELECT id, user_id, lang_code, preferred_order, explicitly_defined
            FROM  user_language_preference_temp""")
        mark_changed()
    with context.begin_transaction():
        op.drop_table("user_language_preference_temp")

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.drop_constraint(
            "_".join((
                config.get('db_schema'),
                config.get('db_user'),
                "preferences_UNQC_name")),
            "preferences")

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.create_unique_constraint(
            "_".join((
                config.get('db_schema'),
                config.get('db_user'),
                "preferences_UNQC_name")),
            "preferences", ["name"])

def connect():
    global _es
    if _es is None:
        port = config.get('elasticsearch_port', '9200')
        server = config.get('elasticsearch_host', 'localhost') + ':' + port
        auth = config.get('elastic_search_basic_auth', None)
        _es = Elasticsearch(server, **{'http_auth': a for a in (auth,) if a})
    return _es

def downgrade(pyramid_env):
    if not using_virtuoso():
        return
    full_schema = '.'.join((config.get('db_schema'), config.get('db_user')))
    with context.begin_transaction():
        for seqname in history_sequences:
            op.execute(
                "SELECT sequence_set('{0}', sequence_set('{1}.{0}', 0, 1), 0)"
                .format(seqname, full_schema))

def upgrade(pyramid_env):
    # 4082 is the virtuoso maximum for VARCHAR. It refuses to modify to VARCHAR
    schema, user = config.get('db_schema'), config.get('db_user')
    with context.begin_transaction():
        for table, column, nullable in url_columns:
            op.execute(
                "alter table %s.%s.%s modify column %s varchar(4082) %s" % (
                    schema, user, table, column,
                    "" if nullable else " NOT NULL"))

 def __table_args__(cls):
     schema, user = config.get('db_schema'), config.get('db_user')
     return (UniqueConstraint(
         getattr(cls, cls.target_id_name),
         cls.namespace,
         cls.key,
         cls.user_id,
         name="%s_%s_%s_unique_constraint" % (
             schema, user, cls.__tablename__)),)

 def get_base_url(self):
     """ Abstracted so that we can support virtual hosts or communities in 
     the future and access the urls when we can't rely on pyramid's current
     request (such as when celery generates notifications
     """
     from assembl.lib import config
     port = config.get('public_port')
     portString = (':'+port) if port != 80 else ''
     return 'http://'+config.get('public_hostname')+portString

def get_curl_query(query):
    auth = config.get('elastic_search_basic_auth', '')
    if auth:
        auth = '-u ' + auth
    return "curl {} -XGET '{}:{}/_search?pretty' -d '{}'".format(
        auth,
        config.get('elasticsearch_host', 'localhost'),
        config.get('elasticsearch_port', '9200'),
        json.dumps(query).replace("'", "\\u0027"))

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.execute("""
UPDATE  post p
SET     parent_id = COALESCE(
    (
        SELECT new_post_parent.id AS new_post_parent_id
        FROM post AS post_to_correct
        JOIN post AS bad_post_parent ON (post_to_correct.parent_id = bad_post_parent.id)
        JOIN post AS new_post_parent ON (new_post_parent.message_id = bad_post_parent.message_id AND new_post_parent.id <> bad_post_parent.id)
        WHERE post_to_correct.parent_id IN (
          SELECT MAX(post.id) as max_post_id 
          FROM imported_post 
          JOIN post ON (post.id=imported_post.id) 
          GROUP BY message_id, source_id
          HAVING COUNT(post.id)>1
          )
        AND p.id = post_to_correct.id
    ),
    p.parent_id
)
        """)
        op.execute("""
DELETE FROM post
WHERE post.id IN (
    SELECT MAX(post.id) as max_post_id 
    FROM imported_post 
    JOIN post ON (post.id=imported_post.id) 
    GROUP BY message_id, source_id 
    HAVING COUNT(post.id)>1
)
        """)
        op.add_column(
            'imported_post', sa.Column(
                'source_post_id',
                sa.Unicode(),
                nullable=False,
                index=True,
                )
            )
        op.execute("""
UPDATE  imported_post p
SET     source_post_id = (
SELECT message_id
FROM post
WHERE p.id = post.id
)
        """)
        op.create_unique_constraint(config.get('db_schema')+"_"+config.get('db_user')+"_imported_post_UNQC_source_post_id_source_id", "imported_post", ["source_post_id","source_id"])
        pass

    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        pass

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.drop_constraint(
            "%s_%s_user_language_preference_UNQC_user_id_locale_id" % (
                config.get('db_schema'), config.get('db_user')),
            "user_language_preference")
        op.create_unique_constraint(
            "%s_%s_user_language_preference_UNQC_user_id_locale_id_source_of_evidence" % (
                config.get('db_schema'), config.get('db_user')),
            "user_language_preference", ["user_id", "locale_id", "source_of_evidence"])

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.drop_constraint(
            "%s_%s_sub_graph_idea_association_UNQC_idea_id_sub_graph_id" % (
                config.get('db_schema'), config.get('db_user')),
            "sub_graph_idea_association")
        op.drop_constraint(
            "%s_%s_sub_graph_idea_link_association_UNQC_idea_link_id_sub_graph_id" % (
                config.get('db_schema'), config.get('db_user')),
            "sub_graph_idea_link_association")

def upgrade(pyramid_env):
    from assembl.models.notification import (
        NotificationSubscription, NotificationSubscriptionClasses)
    with context.begin_transaction():
        tname = "notification_subscription"
        cname = 'ck_%s_%s_%s_notification_subscription_classes' % (
            config.get('db_schema'), config.get('db_user'), tname)
        op.drop_constraint(cname, tname)
        op.create_check_constraint(
            cname, tname, NotificationSubscription.type.in_(
                NotificationSubscriptionClasses.values()))

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.create_table(
            'import_record',
            sa.Column('id', sa.Integer, primary_key=True),
            sa.Column('discussion_id', sa.Integer, sa.ForeignKey("discussion.id"), nullable=False),
            sa.Column('external_iri_id', IRI_ID, nullable=False),
            sa.Column('internal_iri_id', IRI_ID, nullable=False, index=True),
            sa.Column('server_iri_id', IRI_ID, nullable=False),
            sa.Column('last_modified', sa.DateTime, default=datetime.utcnow))
        op.create_unique_constraint(config.get('db_schema')+"_"+config.get('db_user')+"_import_record_UNQC_discussion_id_external_iri_id",
            "import_record", ["discussion_id","external_iri_id"])

def delete_boolean_constraint(db, table, column):
    # The CHECK constraints are generally unnamed. 
    # Dropping the column does not delete the constraint. WHY????
    username = config.get('db_user')
    schema = config.get('db_schema')
    constraints = list(db.execute("select c_text, c_mode from db.dba.sys_constraints where c_table = '%s.%s.%s'" % (
        schema, username, table)))
    for constraint_name, constraint_code in constraints:
        # column name substring would be annoying...
        if column in constraint_code:
            db.execute('alter table "%s"."%s"."%s" drop constraint "%s"' % (
                schema, username, table, constraint_name))

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.drop_constraint(
            "%s_%s_social_auth_account_UNQC_provider_id_provider_domain_uid" % (
                config.get('db_schema'), config.get('db_user')),
            "social_auth_account")
        op.drop_column("social_auth_account", "provider_domain")
        op.add_column(
            "social_auth_account",
            sa.Column("domain", sa.String(200)))
        op.create_unique_constraint(
            "%s_%s_social_auth_account_UNQC_provider_id_uid" % (
                config.get('db_schema'), config.get('db_user')),
            "social_auth_account", ["provider_id", "uid"])

def upgrade(pyramid_env):
    with context.begin_transaction():
        try:
            op.drop_index('ix_document_uri_id', 'document')
        except:
            try:
                op.drop_index('ix_%s_%s_document_uri_id' % (
                    config.get('db_schema'), config.get('db_user')),
                    'document')
            except:
                pass

        op.execute(
            "CREATE UNIQUE INDEX ix_document_discussion_id_uri_id ON %s.%s.document (discussion_id, uri_id)" % (
                config.get('db_schema'), config.get('db_user')))