def upgrade(pyramid_env):
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        orphans = db.query(m.Post).filter(m.Post.ancestry != '', m.Post.parent_id == None).all()
        for p in orphans:
            p.parent_id = int(p.ancestry.split(',')[-2])

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column(
            'agent_email_account',
            sa.Column("preferred", sa.SmallInteger,
                      default=False, server_default='0'))
    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()

    with transaction.manager:
        # get from previous values
        db.execute("""UPDATE agent_email_account SET preferred=(
            SELECT abstract_agent_account.preferred
            FROM abstract_agent_account
            WHERE abstract_agent_account.id = agent_email_account.id
            AND abstract_agent_account."type" = 'agent_email_account')""")
        # Force update, transaction manager saw nothing
        aaa = db.query(m.Role).first()
        flag_modified(aaa, 'name')

    with context.begin_transaction():
        db.execute('ALTER TABLE agent_email_account ADD CHECK (preferred IN (0, 1))')
        op.drop_column(
            'abstract_agent_account', "preferred")

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:
        for d in db.query(m.Discussion).all():
            d.get_participant_template()

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 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:
        for u in db.query(m.User).all():
            u.creation_date = to_utc(u.creation_date).replace(tzinfo=None)
            if u.last_login:
                u.last_login = to_utc(u.last_login).replace(tzinfo=None)
        for w in db.query(m.CreativitySessionWidget).all():
            settings = w.settings_json
            change = False
            for notification in settings.get('notifications', []):
                try:
                    start = datetime.strptime(
                        notification['start'], ISO_8601_FORMAT)
                    notification['start'] = datetime.strftime(
                        to_utc(start), ISO_8601_FORMAT)
                    change = True
                    end = notification.get('end', None)
                    if end:
                        end = datetime.strptime(end, ISO_8601_FORMAT)
                        notification['end'] = datetime.strftime(
                            to_utc(end), ISO_8601_FORMAT)
                except (ValueError, TypeError, KeyError):
                    pass
            if change:
                w.settings_json = settings

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column('content', sa.Column('tombstone_date', sa.DateTime))


    from assembl import models as m
    from assembl.auth import P_DELETE_MY_POST, P_DELETE_POST, P_ADD_POST, P_MODERATE
    from pyramid.security import Authenticated, Everyone

    db = m.get_session_maker()()
    with transaction.manager:
    	# Give the P_DELETE_MY_POST permission to every role which already has the P_ADD_POST permission
        p_add_post = db.query(m.Permission).filter_by(name=P_ADD_POST).one()
        p_delete_my_post = db.query(m.Permission).filter_by(name=P_DELETE_MY_POST).one()

        dps = db.query(m.DiscussionPermission).filter_by(
        	permission_id=p_add_post.id)
        for dp in dps:
            db.add(m.DiscussionPermission(
                discussion_id = dp.discussion_id,
                role_id = dp.role_id,
                permission_id = p_delete_my_post.id))

        # Give the P_DELETE_POST permission to every role which already has the P_MODERATE permission
        p_moderate = db.query(m.Permission).filter_by(name=P_MODERATE).one()
        p_delete_post = db.query(m.Permission).filter_by(name=P_DELETE_POST).one()

        dps2 = db.query(m.DiscussionPermission).filter_by(
        	permission_id=p_moderate.id)
        for dp in dps2:
            db.add(m.DiscussionPermission(
                discussion_id = dp.discussion_id,
                role_id = dp.role_id,
                permission_id = p_delete_post.id))

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:
        synths = db.query(m.Synthesis).join(
            m.SubGraphIdeaAssociation, m.Idea, m.SynthesisPost).filter(
            (m.SynthesisPost.id != None) & (m.Idea.tombstone_date == None)).all()
        # reuse idea snapshots in this one case
        for synth in synths:
            snapshots = {}
            for assoc in synth.idea_assocs:
                idea = assoc.idea
                assoc.idea = idea.copy(True)
                snapshots[idea.id] = assoc.idea
                assoc.idea.tombstone_date = synth.creation_date
            # AND change the links. Sigh.
            synth.db.flush()
            snapshots = {id: idea.id for (id, idea) in snapshots.iteritems()}
            for link in synth.idea_links:
                assert link.is_tombstone
                id = link.source_id
                link.source_id = snapshots.get(id, id)
                id = link.target_id
                link.target_id = snapshots.get(id, id)

def upgrade(pyramid_env):
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        fk = next(iter(
            m.TimelineEvent.__table__.c.previous_event_id.foreign_keys))
        rebuild_fkey(db, fk)

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column('file', sa.Column('file_identity', sa.String(64), index=True))

    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()
    hash_fs = get_hashfs()
    with transaction.manager:
        # document.creation_date?
        for fid, title, creation_date, data in db.execute(
                """SELECT file.id, document.title, document.creation_date, file.data
                FROM file JOIN document using(id)"""):
            data = BytesIO(data)
            data.seek(0)
            parts = title.split('.')
            extension = parts[-1] if len(parts) > 1 else None
            address = hash_fs.put(data, extension)
            creation_date = creation_date or datetime.now()
            creation_date = timegm(creation_date.timetuple())
            if address.is_duplicate:
                creation_date = min(creation_date, path.getmtime(address.abspath))
            utime(address.abspath, (creation_date, creation_date))
            db.execute("UPDATE file SET file_identity='%s' WHERE id=%d" % (address.id, fid))
        mark_changed()

    with context.begin_transaction():
        op.drop_column('file', 'data')
    op.execute('vacuum full', {'isolation_level':'AUTOCOMMIT'})

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.execute(
            'UPDATE langstring_entry SET value=NULL WHERE length("value")=0')

    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        data = list(db.execute(
            """SELECT content.subject_id, content.id
            FROM content
            WHERE subject_id in (
                SELECT subject_id FROM content
                GROUP BY subject_id HAVING count(id) > 1)"""))
        data.sort()
        original_ls = None
        original_ls_id = None
        for subject_id, content_id in data:
            if original_ls_id != subject_id:
                original_ls_id = subject_id
                original_ls = m.LangString.get(subject_id)
                continue
            new_langstring = original_ls.clone(db)
            db.flush()
            db.execute("UPDATE content SET subject_id = %d WHERE id = %d" % (
                new_langstring.id, content_id))

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:
        # create landing_page_module_type table
        op.create_table(
            'landing_page_module_type',
            sa.Column('id', sa.Integer, primary_key=True),
            sa.Column('type', sa.String(60), nullable=False),
            sa.Column('identifier', sa.String(30), nullable=False),
            sa.Column("title_id", sa.Integer, sa.ForeignKey("langstring.id")),
            sa.Column('default_order', sa.Float, nullable=False),
            sa.Column('editable_order', sa.Boolean, default=True),
            sa.Column('required', sa.Boolean, default=False),
            sa.schema.UniqueConstraint("title_id")
        )
        # create landing_page_module table
        op.create_table(
            'landing_page_module',
            sa.Column('id', sa.Integer, primary_key=True),
            sa.Column('type', sa.String(60), nullable=False),
            sa.Column('discussion_id', sa.Integer,
                      sa.ForeignKey("discussion.id", ondelete="CASCADE", onupdate="CASCADE")),
            sa.Column("module_type_id", sa.Integer,
                      sa.ForeignKey("landing_page_module_type.id", ondelete="CASCADE", onupdate="CASCADE")),
            sa.Column('configuration', sa.UnicodeText, nullable=False),
            sa.Column('order', sa.Float, nullable=False),
            sa.Column('enabled', sa.Boolean, default=False)
        )

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.create_table(
            'agent_status_in_discussion',
            sa.Column('id', sa.Integer, primary_key=True),
            sa.Column('discussion_id', sa.Integer, sa.ForeignKey(
                "discussion.id", ondelete='CASCADE', onupdate='CASCADE')),
            sa.Column('profile_id', sa.Integer, sa.ForeignKey(
                "agent_profile.id", ondelete='CASCADE', onupdate='CASCADE')),
            sa.Column('last_visit', sa.DateTime),
            sa.Column('first_visit', sa.DateTime),
            sa.Column('first_subscribed', sa.DateTime),
            sa.Column('last_unsubscribed', sa.DateTime),
            sa.Column('user_created_on_this_discussion', sa.Boolean,
                      server_default='0'),
            sa.schema.UniqueConstraint('discussion_id', 'profile_id')
            )

    # Do stuff with the app's models here.
    from assembl import models as m
    from assembl.auth import R_PARTICIPANT
    db = m.get_session_maker()()
    now = datetime.utcnow()
    with transaction.manager:
        for (user_id, discussion_id) in db.query(
                m.LocalUserRole.user_id, m.LocalUserRole.discussion_id).join(
                m.Role).filter(m.Role.name == R_PARTICIPANT).distinct().all():
            db.add(m.AgentStatusInDiscussion(
                profile_id=user_id, discussion_id=discussion_id,
                first_visit=now, last_visit=now, first_subscribed=now))

def upgrade(pyramid_env):
    from assembl import models as m
    db = m.get_session_maker()()
    doc_re = re.compile(u'/data/Discussion/(?P<discussion_id>\d+)/documents/(?P<document_id>\d+)/data')
    with transaction.manager:
        # take the first sysadmin as creator
        sysadmin_role = db.query(m.Role).filter(m.Role.name == R_SYSADMIN).first()
        creator_id = m.User.default_db.query(m.User).join(
            m.User.roles).filter(m.Role.id == sysadmin_role.id)[0:1][0].id
        for thematic in db.query(m.Thematic).all():
            if thematic.video_html_code:
                result = re.match(doc_re, thematic.video_html_code)
                if result:
                    discussion_id = result.group('discussion_id')
                    document_id = result.group('document_id')

                    new_attachment = m.IdeaAttachment(
                        idea=thematic,
                        document_id=document_id,
                        discussion_id=discussion_id,
                        creator_id=creator_id,
                        title=u'',
                        attachmentPurpose=m.AttachmentPurpose.MEDIA_ATTACHMENT.value
                    )

                    db.add(new_attachment)
                    thematic.video_html_code = u''

        db.flush()

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column(
            'thematic',
            sa.Column('title_id', sa.Integer,
                sa.ForeignKey('langstring.id')))
        op.add_column(
            'thematic',
            sa.Column('description_id', sa.Integer,
                sa.ForeignKey('langstring.id')))
        op.add_column(
            'question',
            sa.Column('title_id', sa.Integer,
                sa.ForeignKey('langstring.id')))

    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        db.execute("UPDATE thematic SET title_id = (SELECT title_id FROM idea WHERE id=thematic.id AND sqla_type='thematic')")
        db.execute("UPDATE thematic SET description_id = (SELECT description_id FROM idea WHERE id=thematic.id AND sqla_type='thematic')")
        db.execute("UPDATE question SET title_id = (SELECT title_id FROM idea WHERE id=question.id AND sqla_type='question')")
        mark_changed()

    with context.begin_transaction():
        op.drop_column('idea', 'title_id')
        op.drop_column('idea', 'description_id')

def downgrade(pyramid_env):
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        changes = []
        for (id, name, values) in db.execute(
                'SELECT id, name, values FROM preferences'):
            values = loads(values or '{}')
            if 'default_permissions' in values:
                found = False
                for role, permissions in list(values['default_permissions'].items()):
                    try:
                        permissions.remove(P_OVERRIDE_SOCIAL_AUTOLOGIN)
                        values['default_permissions'][role] = permissions
                        found = True
                    except ValueError:
                        continue
                if found:
                    changes.append({'id': id, 'pref_json': dumps(values)})
        if changes:
            db.bulk_update_mappings(m.Preferences.__mapper__, changes)
        (permission_id,) = db.execute(
            "SELECT id FROM permission WHERE name='%s'" % (
                P_OVERRIDE_SOCIAL_AUTOLOGIN)).first()
        db.execute("DELETE FROM discussion_permission WHERE permission_id="+str(permission_id))
        db.execute("DELETE FROM permission WHERE id="+str(permission_id))
        mark_changed()

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column('idea_message_column', sa.Column('header_id',
            sa.Integer(), sa.ForeignKey('langstring.id')))

    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        columns = db.query(m.IdeaMessageColumn).all()
        for column in columns:
            synthesis = column.get_column_synthesis()
            if synthesis is not None:
                header = synthesis.body.clone()
                # we need to clone here, otherwise the langstring is deleted with db.delete(synthesis)
                # because of the delete-orphan on the relationship and result to an Integrity error
                # because the langstring is still referenced from idea_message_column table.
                db.add(header)
                db.flush()
                # we can't use here: column.header_id = header.id
                # the mapper doesn't now about header_id and the change
                # will not be committed
                db.execute("""update idea_message_column set header_id = %d
                    where id = %d""" % (header.id, column.id))
                mark_changed()
                db.delete(synthesis)

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.create_table(
            "feed_posts_source",
            sa.Column(
                "id",
                sa.Integer,
                sa.ForeignKey("post_source.id", ondelete="CASCADE", onupdate="CASCADE"),
                primary_key=True,
            ),
            sa.Column("url", sa.String(1024), nullable=False),
            sa.Column("parser_full_class_name", sa.String(512), nullable=False),
        )
        op.create_table(
            "weblink_user",
            sa.Column(
                "id",
                sa.Integer,
                sa.ForeignKey("abstract_agent_account.id", ondelete="CASCADE", onupdate="CASCADE"),
                primary_key=True,
            ),
            sa.Column("user_link", sa.String(1024), unique=True),
        )

    # 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.create_table(
            'edgesense_drupal_source',
            sa.Column('id', sa.Integer, sa.ForeignKey(
                      'post_source.id',
                      onupdate='CASCADE',
                      ondelete='CASCADE'), primary_key=True),
            sa.Column('node_source', sa.String(1024), nullable=False),
            sa.Column('node_root', sa.String(200)),
            sa.Column('user_source', sa.String(1024), nullable=False),
            sa.Column('comment_source', sa.String(1024), nullable=False),
            sa.Column('post_id_prepend', sa.String(100), nullable=False)
            )

        op.create_table(
            'source_specific_account',
            sa.Column('id', sa.Integer, sa.ForeignKey(
                      'abstract_agent_account.id',
                      onupdate='CASCADE',
                      ondelete='CASCADE'), primary_key=True),
            sa.Column('user_info', sa.Text),
            sa.Column('user_link', sa.String(1024)),
            sa.Column('user_id', sa.String(15), nullable=False),
            sa.Column('source_id', sa.Integer, sa.ForeignKey(
                      'edgesense_drupal_source.id', onupdate='CASCADE',
                      ondelete='CASCADE'), nullable=False),
            )

    # 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):
    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        # take the first sysadmin as creator
        sysadmin_role = db.query(m.Role).filter(m.Role.name == R_SYSADMIN).first()
        creator_id = m.User.default_db.query(m.User).join(
            m.User.roles).filter(m.Role.id == sysadmin_role.id)[0:1][0].id
        columns_headers = dict(list(db.execute(
            "SELECT id, header_id FROM idea_message_column")))
        columns = db.query(m.IdeaMessageColumn).all()
        for column in columns:
            synthesis = column.get_column_synthesis()
            header_id = columns_headers.get(column.id, None)
            if header_id is not None and synthesis is None:
                name_en = column.name.closest_entry('en') or column.name.first_original()
                name_fr = column.name.closest_entry('fr') or column.name.first_original()
                subject_ls = m.LangString.create(u"Synthesis: {}".format(name_en.value), 'en')
                subject_ls.add_value(u"Synthèse : {}".format(name_fr.value), 'fr')
                body_ls = m.LangString.get(header_id)  # don't clone, reuse the same langstring
                column.create_column_synthesis(
                    subject=subject_ls,
                    body=body_ls,
                    creator_id=creator_id)

    with context.begin_transaction():
        op.drop_column('idea_message_column', 'header_id')

def upgrade(pyramid_env):
    with context.begin_transaction():
        pass

    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        langstring_ids = db.query(m.LangStringEntry.langstring_id).join(
            m.LangStringEntry.locale).filter(
            sa.func.length(m.LangStringEntry.value) == 0,
            ~m.LangStringEntry.is_machine_translated).order_by(
                m.LangStringEntry.langstring_id).all()
        langstring_ids = [str(id) for (id,) in langstring_ids]

        if langstring_ids:
            first = langstring_ids.pop(0)
            assert first == str(m.LangString.EMPTY_ID)
            while len(langstring_ids):
                subs = ", ".join(langstring_ids[:100])
                db.execute("UPDATE content SET subject_id = %s WHERE subject_id IN (%s)" % (first, subs))
                db.execute("UPDATE content SET body_id = %s WHERE body_id IN (%s)" % (first, subs))
                db.execute("DELETE FROM langstring_entry WHERE langstring_id IN (%s)" % (subs,))
                db.execute("DELETE FROM langstring WHERE id IN (%s)" % (subs,))
                langstring_ids = langstring_ids[100:]
            mark_changed()