code:
# -*- coding: utf-8 -*-

"""
 (c) 2015 - Copyright Red Hat Inc

 Authors:
   Pierre-Yves Chibon <pingou@pingoured.fr>

"""

import datetime
import hashlib
import json
import os
import shutil
import subprocess
import tempfile
import re

import pygit2
import werkzeug

from sqlalchemy.exc import SQLAlchemyError

import pagure
import pagure.exceptions
import pagure.lib
import pagure.lib.notify
from pagure.lib import model
from pagure.lib.repo import PagureRepo

# pylint: disable=R0913,E1101,R0914

def commit_to_patch(repo_obj, commits):
    ''' For a given commit (PyGit2 commit object) of a specified git repo,
    returns a string representation of the changes the commit did in a
    format that allows it to be used as patch.
    '''
    if not isinstance(commits, list):
        commits = [commits]

    patch = ""
    for cnt, commit in enumerate(commits):
        if commit.parents:
            diff = commit.tree.diff_to_tree()

            parent = repo_obj.revparse_single('%s^' % commit.oid.hex)
            diff = repo_obj.diff(parent, commit)
        else:
            # First commit in the repo
            diff = commit.tree.diff_to_tree(swap=True)

        subject = message = ''
        if '\n' in commit.message:
            subject, message = commit.message.split('\n', 1)
        else:
            subject = commit.message

        if len(commits) > 1:
            subject = '[PATCH %s/%s] %s' % (cnt + 1, len(commits), subject)

        patch += u"""From {commit} Mon Sep 17 00:00:00 2001
From: {author_name} <{author_email}>
Date: {date}
Subject: {subject}

{msg}
---

{patch}
""".format(commit=commit.oid.hex,
           author_name=commit.author.name,
           author_email=commit.author.email,
           date=datetime.datetime.utcfromtimestamp(
               commit.commit_time).strftime('%b %d %Y %H:%M:%S +0000'),
           subject=subject,
           msg=message,
           patch=diff.patch)
    return patch

def write_gitolite_acls(session, configfile):
    ''' Generate the configuration file for gitolite for all projects
    on the forge.
    '''
    config = []
    groups = {}
    for project in session.query(model.Project).all():
        for group in project.groups:
            if group.group_name not in groups:
                groups[group.group_name] = [
                    user.username for user in group.users]

        for repos in ['repos', 'docs/', 'tickets/', 'requests/']:
            if repos == 'repos':
                repos = ''

            config.append('repo %s%s' % (repos, project.fullname))
            if repos not in ['tickets/', 'requests/']:
                config.append('  R   = @all')
            if project.groups:
                config.append('  RW+ = @%s' % ' @'.join([
                    group.group_name for group in project.groups]))
            config.append('  RW+ = %s' % project.user.user)
            for user in project.users:
                if user != project.user:
                    config.append('  RW+ = %s' % user.user)
            config.append('')

    with open(configfile, 'w') as stream:
        for key, users in groups.iteritems():
            stream.write('@%s   = %s\n' % (key, ' '.join(users)))
        stream.write('\n')

        for row in config:
            stream.write(row + '\n')

def generate_gitolite_acls():
    """ Generate the gitolite configuration file for all repos
    """
    pagure.lib.git.write_gitolite_acls(
        pagure.SESSION, pagure.APP.config['GITOLITE_CONFIG'])

    gitolite_folder = pagure.APP.config.get('GITOLITE_HOME', None)
    gitolite_version = pagure.APP.config.get('GITOLITE_VERSION', 3)
    if gitolite_folder:
        if gitolite_version == 2:
            cmd = 'GL_RC=%s GL_BINDIR=%s gl-compile-conf' % (
                pagure.APP.config.get('GL_RC'),
                pagure.APP.config.get('GL_BINDIR')
            )
        elif gitolite_version == 3:
            cmd = 'HOME=%s gitolite compile && HOME=%s gitolite trigger '\
                'POST_COMPILE' % (
                    pagure.APP.config.get('GITOLITE_HOME'),
                    pagure.APP.config.get('GITOLITE_HOME')
                )
        else:
            raise pagure.exceptions.PagureException(
                'Non-supported gitolite version "%s"' % gitolite_version
            )
        subprocess.Popen(
            cmd,
            shell=True,
            stdout=subprocess.PIPE,
            stderr=subprocess.PIPE,
            cwd=gitolite_folder
        )

def update_git(obj, repo, repofolder):
    """ Update the given issue in its git.

    This method forks the provided repo, add/edit the issue whose file name
    is defined by the uid field of the issue and if there are additions/
    changes commit them and push them back to the original repo.

    """

    if not repofolder:
        return

    # Get the fork
    repopath = os.path.join(repofolder, repo.path)

    # Clone the repo into a temp folder
    newpath = tempfile.mkdtemp(prefix='pagure-')
    new_repo = pygit2.clone_repository(repopath, newpath)

    file_path = os.path.join(newpath, obj.uid)

    # Get the current index
    index = new_repo.index

    # Are we adding files
    added = False
    if not os.path.exists(file_path):
        added = True

    # Write down what changed
    with open(file_path, 'w') as stream:
        stream.write(json.dumps(
            obj.to_json(), sort_keys=True, indent=4,
            separators=(',', ': ')))

    # Retrieve the list of files that changed
    diff = new_repo.diff()
    files = []
    for p in diff:
        if hasattr(p, 'new_file_path'):
            files.append(p.new_file_path)
        elif hasattr(p, 'delta'):
            files.append(p.delta.new_file.path)

    # Add the changes to the index
    if added:
        index.add(obj.uid)
    for filename in files:
        index.add(filename)

    # If not change, return
    if not files and not added:
        shutil.rmtree(newpath)
        return

    # See if there is a parent to this commit
    parent = None
    try:
        parent = new_repo.head.get_object().oid
    except pygit2.GitError:
        pass

    parents = []
    if parent:
        parents.append(parent)

    # Author/commiter will always be this one
    author = pygit2.Signature(name='pagure', email='pagure')

    # Actually commit
    new_repo.create_commit(
        'refs/heads/master',
        author,
        author,
        'Updated %s %s: %s' % (obj.isa, obj.uid, obj.title),
        new_repo.index.write_tree(),
        parents)
    index.write()

    # Push to origin
    ori_remote = new_repo.remotes[0]
    master_ref = new_repo.lookup_reference('HEAD').resolve()
    refname = '%s:%s' % (master_ref.name, master_ref.name)

    PagureRepo.push(ori_remote, refname)

    # Remove the clone
    shutil.rmtree(newpath)

def clean_git(obj, repo, repofolder):
    """ Update the given issue remove it from its git.

    """

    if not repofolder:
        return

    # Get the fork
    repopath = os.path.join(repofolder, repo.path)

    # Clone the repo into a temp folder
    newpath = tempfile.mkdtemp(prefix='pagure-')
    new_repo = pygit2.clone_repository(repopath, newpath)

    file_path = os.path.join(newpath, obj.uid)

    # Get the current index
    index = new_repo.index

    # Are we adding files
    if not os.path.exists(file_path):
        shutil.rmtree(newpath)
        return

    # Remove the file
    os.unlink(file_path)

    # Add the changes to the index
    index.remove(obj.uid)

    # See if there is a parent to this commit
    parent = None
    if not new_repo.is_empty:
        parent = new_repo.head.get_object().oid

    parents = []
    if parent:
        parents.append(parent)

    # Author/commiter will always be this one
    author = pygit2.Signature(name='pagure', email='pagure')

    # Actually commit
    new_repo.create_commit(
        'refs/heads/master',
        author,
        author,
        'Removed %s %s: %s' % (obj.isa, obj.uid, obj.title),
        new_repo.index.write_tree(),
        parents)
    index.write()

    # Push to origin
    ori_remote = new_repo.remotes[0]
    master_ref = new_repo.lookup_reference('HEAD').resolve()
    refname = '%s:%s' % (master_ref.name, master_ref.name)

    PagureRepo.push(ori_remote, refname)

    # Remove the clone
    shutil.rmtree(newpath)

def get_user_from_json(session, jsondata, key='user'):
    """ From the given json blob, retrieve the user info and search for it
    in the db and create the user if it does not already exist.
    """
    user = None

    username = fullname = useremails = default_email = None

    data = jsondata.get(key, None)

    if data:
        username = data.get('name')
        fullname = data.get('fullname')
        useremails = data.get('emails')
        default_email = data.get('default_email')

    if not default_email and useremails:
        default_email = useremails[0]

    if not username and not useremails:
        return

    user = pagure.lib.search_user(session, username=username)
    if not user:
        for email in useremails:
            user = pagure.lib.search_user(session, email=email)
            if user:
                break

    if not user:
        user = pagure.lib.set_up_user(
            session=session,
            username=username,
            fullname=fullname or username,
            default_email=default_email,
            emails=useremails,
            keydir=pagure.APP.config.get('GITOLITE_KEYDIR', None),
        )
        session.commit()

    return user

def get_project_from_json(
        session, jsondata,
        gitfolder, docfolder, ticketfolder, requestfolder):
    """ From the given json blob, retrieve the project info and search for
    it in the db and create the projec if it does not already exist.
    """
    project = None

    user = get_user_from_json(session, jsondata)
    name = jsondata.get('name')
    project_user = None
    if jsondata.get('parent'):
        project_user = user.username
    project = pagure.lib.get_project(session, name, user=project_user)

    if not project:
        parent = None
        if jsondata.get('parent'):
            parent = get_project_from_json(
                session, jsondata.get('parent'),
                gitfolder, docfolder, ticketfolder, requestfolder)

            pagure.lib.fork_project(
                session=session,
                repo=parent,
                gitfolder=pagure.APP.config['GIT_FOLDER'],
                docfolder=pagure.APP.config['DOCS_FOLDER'],
                ticketfolder=pagure.APP.config['TICKETS_FOLDER'],
                requestfolder=pagure.APP.config['REQUESTS_FOLDER'],
                user=user.username)

        else:
            pagure.lib.new_project(
                session,
                user=user.username,
                name=name,
                description=jsondata.get('description'),
                parent_id=parent.id if parent else None,
                blacklist=pagure.APP.config.get('BLACKLISTED_PROJECTS', []),
                gitfolder=os.path.join(gitfolder, 'forks', user.username)
                    if parent else gitfolder,
                docfolder=docfolder,
                ticketfolder=ticketfolder,
                requestfolder=requestfolder,
                prevent_40_chars=pagure.APP.config.get(
                    'OLD_VIEW_COMMIT_ENABLED', False),
            )

        session.commit()
        project = pagure.lib.get_project(session, name, user=user.username)
        tags = jsondata.get('tags', None)
        if tags:
            pagure.lib.add_tag_obj(
                session, project, tags=tags, user=user.username,
                ticketfolder=None)

    return project

def update_ticket_from_git(
        session, reponame, username, issue_uid, json_data):
    """ Update the specified issue (identified by its unique identifier)
    with the data present in the json blob provided.

    :arg session: the session to connect to the database with.
    :arg repo: the name of the project to update
    :arg issue_uid: the unique identifier of the issue to update
    :arg json_data: the json representation of the issue taken from the git
        and used to update the data in the database.

    """

    repo = pagure.lib.get_project(session, reponame, user=username)
    if not repo:
        raise pagure.exceptions.PagureException(
            'Unknown repo %s of username: %s' % (reponame, username))

    user = get_user_from_json(session, json_data)

    issue = pagure.lib.get_issue_by_uid(session, issue_uid=issue_uid)
    if not issue:
        # Create new issue
        pagure.lib.new_issue(
            session,
            repo=repo,
            title=json_data.get('title'),
            content=json_data.get('content'),
            user=user.username,
            ticketfolder=None,
            issue_id=json_data.get('id'),
            issue_uid=issue_uid,
            private=json_data.get('private'),
            status=json_data.get('status'),
            date_created=datetime.datetime.utcfromtimestamp(
                float(json_data.get('date_created'))),
            notify=False,
        )

    else:
        # Edit existing issue
        pagure.lib.edit_issue(
            session,
            issue=issue,
            ticketfolder=None,
            user=user.username,
            title=json_data.get('title'),
            content=json_data.get('content'),
            status=json_data.get('status'),
            private=json_data.get('private'),
        )
    session.commit()

    issue = pagure.lib.get_issue_by_uid(session, issue_uid=issue_uid)

    # Update tags
    tags = json_data.get('tags', [])
    pagure.lib.update_tags(
        session, issue, tags, username=user.user, ticketfolder=None)

    # Update assignee
    assignee = get_user_from_json(session, json_data, key='assignee')
    if assignee:
        pagure.lib.add_issue_assignee(
            session, issue, assignee.username,
            user=user.user, ticketfolder=None, notify=False)

    # Update depends
    depends = json_data.get('depends', [])
    pagure.lib.update_dependency_issue(
        session, issue.project, issue, depends,
        username=user.user, ticketfolder=None)

    # Update blocks
    blocks = json_data.get('blocks', [])
    pagure.lib.update_blocked_issue(
        session, issue.project, issue, blocks,
        username=user.user, ticketfolder=None)

    for comment in json_data['comments']:
        user = get_user_from_json(session, comment)
        commentobj = pagure.lib.get_issue_comment(
            session, issue_uid, comment['id'])
        if not commentobj:
            pagure.lib.add_issue_comment(
                session,
                issue=issue,
                comment=comment['comment'],
                user=user.username,
                ticketfolder=None,
                notify=False,
                date_created=datetime.datetime.utcfromtimestamp(
                    float(comment['date_created'])),
            )
    session.commit()

def update_request_from_git(
        session, reponame, username, request_uid, json_data,
        gitfolder, docfolder, ticketfolder, requestfolder):
    """ Update the specified request (identified by its unique identifier)
    with the data present in the json blob provided.

    :arg session: the session to connect to the database with.
    :arg repo: the name of the project to update
    :arg username: the username to find the repo, is not None for forked
        projects
    :arg request_uid: the unique identifier of the issue to update
    :arg json_data: the json representation of the issue taken from the git
        and used to update the data in the database.

    """

    repo = pagure.lib.get_project(session, reponame, user=username)
    if not repo:
        raise pagure.exceptions.PagureException(
            'Unknown repo %s of username: %s' % (reponame, username))

    user = get_user_from_json(session, json_data)

    request = pagure.lib.get_request_by_uid(
        session, request_uid=request_uid)

    if not request:
        repo_from = get_project_from_json(
            session, json_data.get('repo_from'),
            gitfolder, docfolder, ticketfolder, requestfolder
        )

        repo_to = get_project_from_json(
            session, json_data.get('project'),
            gitfolder, docfolder, ticketfolder, requestfolder
        )

        status = json_data.get('status')
        if str(status).lower() == 'true':
            status = 'Open'
        elif str(status).lower() == 'false':
            status = 'Merged'

        # Create new request
        pagure.lib.new_pull_request(
            session,
            repo_from=repo_from,
            branch_from=json_data.get('branch_from'),
            repo_to=repo_to if repo_to else None,
            remote_git=json_data.get('remote_git'),
            branch_to=json_data.get('branch'),
            title=json_data.get('title'),
            user=user.username,
            requestuid=json_data.get('uid'),
            requestid=json_data.get('id'),
            status=status,
            requestfolder=None,
            notify=False,
        )
        session.commit()

    request = pagure.lib.get_request_by_uid(
        session, request_uid=request_uid)

    # Update start and stop commits
    request.commit_start = json_data.get('commit_start')
    request.commit_stop = json_data.get('commit_stop')

    # Update assignee
    assignee = get_user_from_json(session, json_data, key='assignee')
    if assignee:
        pagure.lib.add_pull_request_assignee(
            session, request, assignee.username,
            user=user.user, requestfolder=None)

    for comment in json_data['comments']:
        user = get_user_from_json(session, comment)
        commentobj = pagure.lib.get_request_comment(
            session, request_uid, comment['id'])
        if not commentobj:
            pagure.lib.add_pull_request_comment(
                session,
                request,
                commit=comment['commit'],
                tree_id=comment.get('tree_id') or None,
                filename=comment['filename'],
                row=comment['line'],
                comment=comment['comment'],
                user=user.username,
                requestfolder=None,
                notify=False,
            )
    session.commit()

def add_file_to_git(repo, issue, ticketfolder, user, filename, filestream):
    ''' Add a given file to the specified ticket git repository.

    :arg repo: the Project object from the database
    :arg ticketfolder: the folder on the filesystem where the git repo for
        tickets are stored
    :arg user: the user object with its username and email
    :arg filename: the name of the file to save
    :arg filestream: the actual content of the file

    '''

    if not ticketfolder:
        return

    # Prefix the filename with a timestamp:
    filename = '%s-%s' % (
        hashlib.sha256(filestream.read()).hexdigest(),
        werkzeug.secure_filename(filename)
    )

    # Get the fork
    repopath = os.path.join(ticketfolder, repo.path)

    # Clone the repo into a temp folder
    newpath = tempfile.mkdtemp(prefix='pagure-')
    new_repo = pygit2.clone_repository(repopath, newpath)

    folder_path = os.path.join(newpath, 'files')
    file_path = os.path.join(folder_path, filename)

    # Get the current index
    index = new_repo.index

    # Are we adding files
    added = False
    if not os.path.exists(file_path):
        added = True
    else:
        # File exists, remove the clone and return
        shutil.rmtree(newpath)
        return os.path.join('files', filename)

    if not os.path.exists(folder_path):
        os.mkdir(folder_path)

    # Write down what changed
    filestream.seek(0)
    with open(file_path, 'w') as stream:
        stream.write(filestream.read())

    # Retrieve the list of files that changed
    diff = new_repo.diff()
    files = [patch.new_file_path for patch in diff]

    # Add the changes to the index
    if added:
        index.add(os.path.join('files', filename))
    for filename in files:
        index.add(filename)

    # If not change, return
    if not files and not added:
        shutil.rmtree(newpath)
        return

    # See if there is a parent to this commit
    parent = None
    try:
        parent = new_repo.head.get_object().oid
    except pygit2.GitError:
        pass

    parents = []
    if parent:
        parents.append(parent)

    # Author/commiter will always be this one
    author = pygit2.Signature(
        name=user.username.encode('utf-8'),
        email=user.email.encode('utf-8')
    )

    # Actually commit
    new_repo.create_commit(
        'refs/heads/master',
        author,
        author,
        'Add file %s to ticket %s: %s' % (filename, issue.uid, issue.title),
        new_repo.index.write_tree(),
        parents)
    index.write()

    # Push to origin
    ori_remote = new_repo.remotes[0]
    master_ref = new_repo.lookup_reference('HEAD').resolve()
    refname = '%s:%s' % (master_ref.name, master_ref.name)

    PagureRepo.push(ori_remote, refname)

    # Remove the clone
    shutil.rmtree(newpath)

    return os.path.join('files', filename)

def update_file_in_git(
        repo, branch, branchto, filename, content, message, user, email):
    ''' Update a specific file in the specified repository with the content
    given and commit the change under the user's name.

    :arg repo: the Project object from the database
    :arg filename: the name of the file to save
    :arg content: the new content of the file
    :arg message: the message of the git commit
    :arg user: the user object with its username and email

    '''

    # Get the fork
    repopath = pagure.get_repo_path(repo)

    # Clone the repo into a temp folder
    newpath = tempfile.mkdtemp(prefix='pagure-')
    new_repo = pygit2.clone_repository(
        repopath, newpath, checkout_branch=branch)

    file_path = os.path.join(newpath, filename)

    # Get the current index
    index = new_repo.index

    # Write down what changed
    with open(file_path, 'w') as stream:
        stream.write(content.replace('\r', '').encode('utf-8'))

    # Retrieve the list of files that changed
    diff = new_repo.diff()
    files = []
    for p in diff:
        if hasattr(p, 'new_file_path'):
            files.append(p.new_file_path)
        elif hasattr(p, 'delta'):
            files.append(p.delta.new_file.path)

    # Add the changes to the index
    added = False
    for filename in files:
        added = True
        index.add(filename)

    # If not change, return
    if not files and not added:
        shutil.rmtree(newpath)
        return

    # See if there is a parent to this commit
    branch_ref = get_branch_ref(new_repo, branch)
    parent = branch_ref.get_object()

    # See if we need to create the branch
    nbranch_ref = None
    if branchto not in new_repo.listall_branches():
        nbranch_ref = new_repo.create_branch(branchto, parent)

    parents = []
    if parent:
        parents.append(parent.hex)

    # Author/commiter will always be this one
    author = pygit2.Signature(
        name=user.username.encode('utf-8'),
        email=email.encode('utf-8')
    )

    # Actually commit
    new_repo.create_commit(
        nbranch_ref.name if nbranch_ref else branch_ref.name,
        author,
        author,
        message.strip(),
        new_repo.index.write_tree(),
        parents)
    index.write()

    # Push to origin
    ori_remote = new_repo.remotes[0]
    refname = '%s:refs/heads/%s' % (
        nbranch_ref.name if nbranch_ref else branch_ref.name,
        branchto)

    try:
        PagureRepo.push(ori_remote, refname)
    except pygit2.GitError as err:  # pragma: no cover
        shutil.rmtree(newpath)
        raise pagure.exceptions.PagureException(
            'Commit could not be done: %s' % err)

    # Remove the clone
    shutil.rmtree(newpath)

    return os.path.join('files', filename)

def read_output(cmd, abspath, input=None, keepends=False, **kw):
    """ Read the output from the given command to run """
    if input:
        stdin = subprocess.PIPE
    else:
        stdin = None
    procs = subprocess.Popen(
        cmd,
        stdin=stdin,
        stdout=subprocess.PIPE,
        stderr=subprocess.PIPE,
        cwd=abspath,
        **kw)
    (out, err) = procs.communicate(input)
    retcode = procs.wait()
    if retcode:
        print 'ERROR: %s =-- %s' % (cmd, retcode)
        print out
        print err
    if not keepends:
        out = out.rstrip('\n\r')
    return out

def read_git_output(args, abspath, input=None, keepends=False, **kw):
    """Read the output of a Git command."""

    return read_output(
        ['git'] + args, abspath, input=input, keepends=keepends, **kw)

def read_git_lines(args, abspath, keepends=False, **kw):
    """Return the lines output by Git command.

    Return as single lines, with newlines stripped off."""

    return read_git_output(
        args, abspath, keepends=keepends, **kw
    ).splitlines(keepends)

def get_revs_between(oldrev, newrev, abspath, refname, forced=False):
    """ Yield revisions between HEAD and BASE. """

    cmd = ['rev-list', '%s...%s' % (oldrev, newrev)]
    if forced:
        head = get_default_branch(abspath)
        cmd.append('^%s' % head)
    if set(newrev) == set('0'):
        cmd = ['rev-list', '%s' % oldrev]
    elif set(oldrev) == set('0') or set(oldrev) == set('^0'):
        head = get_default_branch(abspath)
        cmd = ['rev-list', '%s' % newrev, '^%s' % head]
        if head in refname:
            cmd = ['rev-list', '%s' % newrev]
    return pagure.lib.git.read_git_lines(cmd, abspath)

def is_forced_push(oldrev, newrev, abspath):
    """ Returns wether there was a force push between HEAD and BASE.
    Doc: http://stackoverflow.com/a/12258773
    """

    # Returns if there was any commits deleted in the changeset
    cmd = ['rev-list', '%s' % oldrev, '^%s' % newrev]
    out = pagure.lib.git.read_git_lines(cmd, abspath)
    return len(out) > 0

def get_base_revision(torev, fromrev, abspath):
    """ Return the base revision between HEAD and BASE.
    This is useful in case of force-push.
    """
    cmd = ['merge-base', fromrev, torev]
    return pagure.lib.git.read_git_lines(cmd, abspath)

def get_default_branch(abspath):
    """ Return the default branch of a repo. """
    cmd = ['rev-parse', '--abbrev-ref', 'HEAD']
    out = pagure.lib.git.read_git_lines(cmd, abspath)
    if out:
        return out[0]
    else:
        return 'master'

def get_author(commit, abspath):
    ''' Return the name of the person that authored the commit. '''
    user = pagure.lib.git.read_git_lines(
        ['log', '-1', '--pretty=format:"%an"', commit],
        abspath)[0].replace('"', '')
    return user

def get_author_email(commit, abspath):
    ''' Return the email of the person that authored the commit. '''
    user = pagure.lib.git.read_git_lines(
        ['log', '-1', '--pretty=format:"%ae"', commit],
        abspath)[0].replace('"', '')
    return user

def get_repo_name(abspath):
    ''' Return the name of the git repo based on its path.
    '''
    repo_name = '.'.join(abspath.split(os.path.sep)[-1].split('.')[:-1])
    return repo_name

def get_username(abspath):
    ''' Return the username of the git repo based on its path.
    '''
    username = None
    repo = os.path.abspath(os.path.join(abspath, '..'))
    if pagure.APP.config['FORK_FOLDER'] in repo:
        username = repo.split(pagure.APP.config['FORK_FOLDER'])[1]
        if username.startswith('/'):
            username = username[1:]
    return username

def get_branch_ref(repo, branchname):
    ''' Return the reference to the specified branch or raises an exception.
    '''
    location = pygit2.GIT_BRANCH_LOCAL
    if branchname not in repo.listall_branches():
        branchname = 'origin/%s' % branchname
        location = pygit2.GIT_BRANCH_REMOTE
    branch_ref = repo.lookup_branch(branchname, location).resolve()

    if not branch_ref:
        raise pagure.exceptions.PagureException(
            'No refs found for %s' % branchname)
    return branch_ref

def merge_pull_request(
        session, request, username, request_folder, domerge=True):
    ''' Merge the specified pull-request.
    '''
    if request.remote:
        # Get the fork
        repopath = pagure.get_remote_repo_path(
            request.remote_git, request.branch_from)
    else:
        # Get the fork
        repopath = pagure.get_repo_path(request.project_from)

    fork_obj = PagureRepo(repopath)

    # Get the original repo
    parentpath = pagure.get_repo_path(request.project)

    # Clone the original repo into a temp folder
    newpath = tempfile.mkdtemp(prefix='pagure-pr-merge')
    new_repo = pygit2.clone_repository(parentpath, newpath)

    # Update the start and stop commits in the DB, one last time
    diff_commits = diff_pull_request(
        session, request, fork_obj, PagureRepo(parentpath),
        requestfolder=request_folder, with_diff=False)[0]

    if request.project.settings.get(
            'Enforce_signed-off_commits_in_pull-request', False):
        for commit in diff_commits:
            if 'signed-off-by' not in commit.message.lower():
                shutil.rmtree(newpath)
                raise pagure.exceptions.PagureException(
                    'This repo enforces that all commits are '
                    'signed off by their author. ')

    # Checkout the correct branch
    branch_ref = get_branch_ref(new_repo, request.branch)
    if not branch_ref:
        shutil.rmtree(newpath)
        raise pagure.exceptions.BranchNotFoundException(
            'Branch %s could not be found in the repo %s' % (
                request.branch, request.project.fullname
            ))

    new_repo.checkout(branch_ref)

    branch = get_branch_ref(fork_obj, request.branch_from)
    if not branch:
        shutil.rmtree(newpath)
        raise pagure.exceptions.BranchNotFoundException(
            'Branch %s could not be found in the repo %s' % (
                request.branch_from, request.project_from.fullname
                if request.project_from else request.remote_git
            ))

    repo_commit = fork_obj[branch.get_object().hex]

    ori_remote = new_repo.remotes[0]
    # Add the fork as remote repo
    reponame = '%s_%s' % (request.user.user, request.uid)

    remote = new_repo.create_remote(reponame, repopath)

    # Fetch the commits
    remote.fetch()

    merge = new_repo.merge(repo_commit.oid)
    if merge is None:
        mergecode = new_repo.merge_analysis(repo_commit.oid)[0]

    refname = '%s:refs/heads/%s' % (branch_ref.name, request.branch)
    if (
            (merge is not None and merge.is_uptodate)
            or
            (merge is None and
             mergecode & pygit2.GIT_MERGE_ANALYSIS_UP_TO_DATE)):

        if domerge:
            pagure.lib.close_pull_request(
                session, request, username,
                requestfolder=request_folder)
            shutil.rmtree(newpath)
            try:
                session.commit()
            except SQLAlchemyError as err:  # pragma: no cover
                session.rollback()
                pagure.APP.logger.exception(err)
                raise pagure.exceptions.PagureException(
                    'Could not close this pull-request')
            raise pagure.exceptions.PagureException(
                'Nothing to do, changes were already merged')
        else:
            request.merge_status = 'NO_CHANGE'
            session.commit()
            shutil.rmtree(newpath)
            return 'NO_CHANGE'

    elif (
            (merge is not None and merge.is_fastforward)
            or
            (merge is None and
             mergecode & pygit2.GIT_MERGE_ANALYSIS_FASTFORWARD)):

        if domerge:
            if not request.project.settings.get('always_merge', False):
                if merge is not None:
                    # This is depending on the pygit2 version
                    branch_ref.target = merge.fastforward_oid
                elif merge is None and mergecode is not None:
                    branch_ref.set_target(repo_commit.oid.hex)
            else:
                tree = new_repo.index.write_tree()
                head = new_repo.lookup_reference('HEAD').get_object()
                user_obj = pagure.lib.__get_user(session, username)
                author = pygit2.Signature(
                    user_obj.fullname.encode('utf-8'),
                    user_obj.default_email.encode('utf-8'))
                new_repo.create_commit(
                    'refs/heads/%s' % request.branch,
                    author,
                    author,
                    'Merge #%s `%s`' % (request.id, request.title),
                    tree,
                    [head.hex, repo_commit.oid.hex])

            PagureRepo.push(ori_remote, refname)
        else:
            request.merge_status = 'FFORWARD'
            session.commit()
            shutil.rmtree(newpath)
            return 'FFORWARD'

    else:
        tree = None
        try:
            tree = new_repo.index.write_tree()
        except pygit2.GitError:
            shutil.rmtree(newpath)
            if domerge:
                raise pagure.exceptions.PagureException('Merge conflicts!')
            else:
                request.merge_status = 'CONFLICTS'
                session.commit()
                return 'CONFLICTS'

        if domerge:
            head = new_repo.lookup_reference('HEAD').get_object()
            user_obj = pagure.lib.__get_user(session, username)
            author = pygit2.Signature(
                user_obj.fullname.encode('utf-8'),
                user_obj.default_email.encode('utf-8'))
            new_repo.create_commit(
                'refs/heads/%s' % request.branch,
                author,
                author,
                'Merge #%s `%s`' % (request.id, request.title),
                tree,
                [head.hex, repo_commit.oid.hex])
            PagureRepo.push(ori_remote, refname)

        else:
            request.merge_status = 'MERGE'
            session.commit()
            shutil.rmtree(newpath)
            return 'MERGE'

    # Update status
    pagure.lib.close_pull_request(
        session, request, username,
        requestfolder=request_folder,
    )
    try:
        # Reset the merge_status of all opened PR to refresh their cache
        pagure.lib.reset_status_pull_request(session, request.project)
        session.commit()
    except SQLAlchemyError as err:  # pragma: no cover
        session.rollback()
        pagure.APP.logger.exception(err)
        shutil.rmtree(newpath)
        raise pagure.exceptions.PagureException(
            'Could not update this pull-request in the database')
    shutil.rmtree(newpath)

    return 'Changes merged!'

def diff_pull_request(
        session, request, repo_obj, orig_repo, requestfolder,
        with_diff=True):
    """ Returns the diff and the list of commits between the two git repos
    mentionned in the given pull-request.
    """

    commitid = None
    diff = None
    diff_commits = []
    branch = repo_obj.lookup_branch(request.branch_from)
    if branch:
        commitid = branch.get_object().hex

    if not repo_obj.is_empty and not orig_repo.is_empty:
        # Pull-request open
        master_commits = [
            commit.oid.hex
            for commit in orig_repo.walk(
                orig_repo.lookup_branch(request.branch).get_object().hex,
                pygit2.GIT_SORT_TIME)
        ]
        for commit in repo_obj.walk(commitid, pygit2.GIT_SORT_TIME):
            if request.status and commit.oid.hex in master_commits:
                break
            diff_commits.append(commit)

        if request.status and diff_commits:
            first_commit = repo_obj[diff_commits[-1].oid.hex]
            # Check if we can still rely on the merge_status
            commenttext = None
            if request.commit_start != first_commit.oid.hex or\
                    request.commit_stop != diff_commits[0].oid.hex:
                request.merge_status = None
                if request.commit_start:
                    new_commits_count = 0
                    commenttext = ""
                    for i in diff_commits:
                        if i.oid.hex == request.commit_stop:
                            break
                        new_commits_count = new_commits_count + 1
                        commenttext = '%s * %s\n' % (commenttext, i.message.strip().split('\n')[0])
                    if new_commits_count == 1:
                        commenttext = "**%d new commit added**\n\n%s" % (new_commits_count, commenttext)
                    else:
                        commenttext = "**%d new commits added**\n\n%s" % (new_commits_count, commenttext)
                if request.commit_start and \
                        request.commit_start != first_commit.oid.hex:
                    commenttext = 'rebased'
            request.commit_start = first_commit.oid.hex
            request.commit_stop = diff_commits[0].oid.hex
            session.add(request)
            session.commit()
            if commenttext:
                pagure.lib.add_pull_request_comment(
                    session, request,
                    commit=None, tree_id=None, filename=None, row=None,
                    comment='%s' % commenttext,
                    user=request.user.username,
                    requestfolder=requestfolder,
                    notify=False, notification=True
                )
                session.commit()
            pagure.lib.git.update_git(
                request, repo=request.project,
                repofolder=requestfolder)

        if diff_commits and with_diff:
            diff = repo_obj.diff(
                repo_obj.revparse_single(diff_commits[-1].parents[0].oid.hex),
                repo_obj.revparse_single(diff_commits[0].oid.hex)
            )

    elif orig_repo.is_empty and not repo_obj.is_empty:
        for commit in repo_obj.walk(commitid, pygit2.GIT_SORT_TIME):
            diff_commits.append(commit)
        if request.status and diff_commits:
            first_commit = repo_obj[diff_commits[-1].oid.hex]
            # Check if we can still rely on the merge_status
            if request.commit_start != first_commit.oid.hex or\
                    request.commit_stop != diff_commits[0].oid.hex:
                request.merge_status = None
            request.commit_start = first_commit.oid.hex
            request.commit_stop = diff_commits[0].oid.hex
            session.add(request)
            session.commit()
            pagure.lib.git.update_git(
                request, repo=request.project,
                repofolder=requestfolder)

        repo_commit = repo_obj[request.commit_stop]
        if with_diff:
            diff = repo_commit.tree.diff_to_tree(swap=True)
    else:
        raise pagure.exceptions.PagureException(
            'Fork is empty, there are no commits to request pulling')

    return (diff_commits, diff)

def get_git_tags(project):
    """ Returns the list of tags created in the git repositorie of the
    specified project.
    """
    repopath = pagure.get_repo_path(project)
    repo_obj = PagureRepo(repopath)

    tags = [
        tag.split('refs/tags/')[1]
        for tag in repo_obj.listall_references()
        if 'refs/tags/' in tag
    ]

    return tags

def get_git_tags_objects(project):
    """ Returns the list of references of the tags created in the git
    repositorie the specified project.
    The list is sorted using the time of the commit associated to the tag """
    repopath = pagure.get_repo_path(project)
    repo_obj = PagureRepo(repopath)
    tags = {}
    for tag in repo_obj.listall_references():
        if 'refs/tags/' in tag and repo_obj.lookup_reference(tag):
            commit_time = ""
            theobject = repo_obj[repo_obj.lookup_reference(tag).target]
            objecttype = ""
            if isinstance(theobject, pygit2.Tag):
                commit_time = theobject.get_object().commit_time
                objecttype = "tag"
            elif isinstance(theobject, pygit2.Commit):
                commit_time = theobject.commit_time
                objecttype = "commit"

            tags[commit_time] = {
                "object": repo_obj[repo_obj.lookup_reference(tag).target],
                "tagname": tag.replace("refs/tags/",""),
                "date": commit_time,
                "objecttype": objecttype,
                "head_msg": None,
                "body_msg": None,
            }
            if objecttype == 'tag':
                head_msg, _, body_msg = tags[commit_time][
                    "object"].message.partition('\n')
                if body_msg.strip().endswith('\n-----END PGP SIGNATURE-----'):
                    body_msg = body_msg.rsplit(
                        '-----BEGIN PGP SIGNATURE-----', 1)[0].strip()
                tags[commit_time]["head_msg"] = head_msg
                tags[commit_time]["body_msg"] = body_msg
    sorted_tags = []

    for tag in sorted(tags, reverse=True):
        sorted_tags.append(tags[tag])

    return sorted_tags

repo_name: bill/auger

license: mit


code:
# -*- coding: utf-8 -*-

"""
 (c) 2015 - Copyright Red Hat Inc

 Authors:
   Pierre-Yves Chibon <pingou@pingoured.fr>

"""

import re

import pagure.exceptions

FIXES = [
    re.compile(r'(?:.*\s+)?fixe?[sd]?:?\s*?#(\d+)', re.I),
    re.compile(r'(?:.*\s+)?fixe?[sd]?:?\s*?https?://.*/(\w+)/(?:issue|pull-request)/(\d+)', re.I),
    re.compile(r'(?:.*\s+)?merge?[sd]?:?\s*?#(\d+)', re.I),
    re.compile(r'(?:.*\s+)?merge?[sd]?:?\s*?https?://.*/(\w+)/(?:issue|pull-request)/(\d+)', re.I),
    re.compile(r'(?:.*\s+)?close?[sd]?:?\s*?#(\d+)', re.I),
    re.compile(r'(?:.*\s+)?close?[sd]?:?\s*?https?://.*/(\w+)/(?:issue|pull-request)/(\d+)', re.I),
]

RELATES = [
    re.compile(r'(?:.*\s+)?relate[sd]?:?\s*?(?:to)?\s*?#(\d+)', re.I),
    re.compile(r'(?:.*\s+)?relate[sd]?:?\s?#(\d+)', re.I),
    re.compile(
        r'(?:.*\s+)?relate[sd]?:?\s*?(?:to)?\s*?https?://.*/(\w+)/issue/(\d+)',
        re.I),
]

def get_relation(session, reponame, username, text, reftype='relates',
                 include_prs=False):
    ''' For a given text, searches using regex if the text contains
    reference to another issue in this project or another one.

    Returns the list of issues referenced (possibly empty).
    If include_prs=True, it may also contain pull requests (may still
    be empty).

    By default it searches for references of type: `relates`, for example:
    ``this commits relates to #2``.
    Another reference type is: `fixes` refering to text including for
    example: ``this commits fixes #3``.

    '''

    repo = pagure.lib.get_project(session, reponame, user=username)
    if not repo:
        return []

    regex = RELATES
    if reftype == 'fixes':
        regex = FIXES

    relations = []
    for motif in regex:
        relid = None
        project = None
        if motif.match(text):
            if len(motif.match(text).groups()) >= 2:
                relid = motif.match(text).group(2)
                project = motif.match(text).group(1)
            else:
                relid = motif.match(text).group(1)

        if relid:
            relation = pagure.lib.search_issues(
                session, repo=repo, issueid=relid)

            if relation is None and include_prs:
                relation = pagure.lib.search_pull_requests(
                    session, project_id=repo.id, requestid=relid)

            if relation is None or relation.project.name not in [project,
                                                                 repo.name]:
                continue

            if relation not in relations:
                relations.append(relation)

    return relations


code:
# -*- coding: utf-8 -*-

"""
 (c) 2015-2016 - Copyright Red Hat Inc

 Authors:
   Pierre-Yves Chibon <pingou@pingoured.fr>
   Farhaan Bukhsh <farhaan.bukhsh@gmail.com>

"""

import random
import string
import bcrypt

import hashlib
import pagure
from pagure.lib import model
from kitchen.text.converters import to_unicode, to_bytes
from cryptography.hazmat.primitives import constant_time

def id_generator(size=15, chars=string.ascii_uppercase + string.digits):
    """ Generates a random identifier for the given size and using the
    specified characters.
    If no size is specified, it uses 15 as default.
    If no characters are specified, it uses ascii char upper case and
    digits.
    :arg size: the size of the identifier to return.
    :arg chars: the list of characters that can be used in the
        idenfitier.
    """
    return ''.join(random.choice(chars) for x in range(size))

def get_session_by_visitkey(session, sessionid):
    ''' Return a specified VisitUser via its session identifier (visit_key).

    :arg session: the session with which to connect to the database.

    '''
    query = session.query(
        model.PagureUserVisit
    ).filter(
        model.PagureUserVisit.visit_key == sessionid
    )

    return query.first()

def get_users_by_group(session, group):
    ''' Return the list of users for a specified group.

    :arg session: the session with which to connect to the database.

    '''
    query = session.query(
        model.User
    ).filter(
        model.User.id == model.PagureUserGroup.user_id
    ).filter(
        model.PagureUserGroup.group_id == model.PagureGroup.id
    ).filter(
        model.PagureGroup.group_name == group
    ).order_by(
        model.User.user
    )

    return query.all()

def generate_hashed_value(password):
    """ Generate hash value for password
    """
    return '$2$' + bcrypt.hashpw(to_unicode(password), bcrypt.gensalt())

def check_password(entered_password, user_password, seed=None):
    """ Version checking and returning the password
    """
    if not user_password.count('$') >= 2:
        raise pagure.exceptions.PagureException(
            'Password of unknown version found in the database'
        )

    _, version, user_password = user_password.split('$', 2)

    if version == '2':
        password = bcrypt.hashpw(to_unicode(entered_password), user_password)

    elif version == '1':
        password = '%s%s' % (to_unicode(entered_password), seed)
        password = hashlib.sha512(password).hexdigest()

    else:
        raise pagure.exceptions.PagureException(
            'Password of unknown version found in the database'
        )

    return constant_time.bytes_eq(
        to_bytes(password), to_bytes(user_password))


code:
# -*- coding: utf-8 -*-

"""
 (c) 2014-2015 - Copyright Red Hat Inc

 Authors:
   Pierre-Yves Chibon <pingou@pingoured.fr>

"""

__requires__ = ['SQLAlchemy >= 0.8', 'jinja2 >= 2.4']
import pkg_resources

import datetime
import logging
import json
import operator

import sqlalchemy as sa

from sqlalchemy import create_engine, MetaData
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import relation

CONVENTION = {
    "ix": 'ix_%(table_name)s_%(column_0_label)s',
    # Checks are currently buggy and prevent us from naming them correctly
    #"ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "%(table_name)s_%(column_0_name)s_fkey",
    "pk": "%(table_name)s_pkey",
    "uq": "%(table_name)s_%(column_0_name)s_key",
}

BASE = declarative_base(metadata=MetaData(naming_convention=CONVENTION))

ERROR_LOG = logging.getLogger('pagure.model')

# pylint: disable=C0103,R0903,W0232,E1101

def create_tables(db_url, alembic_ini=None, acls=None, debug=False):
    """ Create the tables in the database using the information from the
    url obtained.

    :arg db_url, URL used to connect to the database. The URL contains
        information with regards to the database engine, the host to
        connect to, the user and password and the database name.
          ie: <engine>://<user>:<password>@<host>/<dbname>
    :kwarg alembic_ini, path to the alembic ini file. This is necessary
        to be able to use alembic correctly, but not for the unit-tests.
    :kwarg debug, a boolean specifying wether we should have the verbose
        output of sqlalchemy or not.
    :return a session that can be used to query the database.

    """
    if db_url.startswith('sqlite'):
        engine = create_engine(db_url, echo=debug)
    else:
        engine = create_engine(db_url, echo=debug, client_encoding='utf8')

    from pagure.ui.plugins import get_plugin_tables
    get_plugin_tables()
    BASE.metadata.create_all(engine)
    # engine.execute(collection_package_create_view(driver=engine.driver))
    if db_url.startswith('sqlite:'):
        # Ignore the warning about con_record
        # pylint: disable=W0613
        def _fk_pragma_on_connect(dbapi_con, con_record):  # pragma: no cover
            ''' Tries to enforce referential constraints on sqlite. '''
            dbapi_con.execute('pragma foreign_keys=ON')
        sa.event.listen(engine, 'connect', _fk_pragma_on_connect)

    if alembic_ini is not None:  # pragma: no cover
        # then, load the Alembic configuration and generate the
        # version table, "stamping" it with the most recent rev:

        # Ignore the warning missing alembic
        # pylint: disable=F0401
        from alembic.config import Config
        from alembic import command
        alembic_cfg = Config(alembic_ini)
        command.stamp(alembic_cfg, "head")

    scopedsession = scoped_session(sessionmaker(bind=engine))
    BASE.metadata.bind = scopedsession
    # Insert the default data into the db
    create_default_status(scopedsession, acls=acls)
    return scopedsession

def create_default_status(session, acls=None):
    """ Insert the defaults status in the status tables.
    """

    for status in ['Open', 'Invalid', 'Insufficient data', 'Fixed', 'Duplicate']:
        ticket_stat = StatusIssue(status=status)
        session.add(ticket_stat)
        try:
            session.commit()
        except SQLAlchemyError:  # pragma: no cover
            session.rollback()
            ERROR_LOG.debug('Status %s could not be added', ticket_stat)

    for status in ['Open', 'Closed', 'Merged']:
        pr_stat = StatusPullRequest(status=status)
        session.add(pr_stat)
        try:
            session.commit()
        except SQLAlchemyError:  # pragma: no cover
            session.rollback()
            ERROR_LOG.debug('Status %s could not be added', pr_stat)

    for grptype in ['user', 'admin']:
        grp_type = PagureGroupType(group_type=grptype)
        session.add(grp_type)
        try:
            session.commit()
        except SQLAlchemyError:  # pragma: no cover
            session.rollback()
            ERROR_LOG.debug('Type %s could not be added', grptype)

    for acl in sorted(acls) or {}:
        item = ACL(
            name=acl,
            description=acls[acl]
        )
        session.add(item)
        try:
            session.commit()
        except SQLAlchemyError:  # pragma: no cover
            session.rollback()
            ERROR_LOG.debug('ACL %s could not be added', acl)

class StatusIssue(BASE):
    """ Stores the status a ticket can have.

    Table -- status_issue
    """
    __tablename__ = 'status_issue'

    id = sa.Column(sa.Integer, primary_key=True)
    status = sa.Column(sa.String(255), nullable=False, unique=True)

class StatusPullRequest(BASE):
    """ Stores the status a pull-request can have.

    Table -- status_issue
    """
    __tablename__ = 'status_pull_requests'

    id = sa.Column(sa.Integer, primary_key=True)
    status = sa.Column(sa.String(255), nullable=False, unique=True)

class User(BASE):
    """ Stores information about users.

    Table -- users
    """

    __tablename__ = 'users'
    id = sa.Column(sa.Integer, primary_key=True)
    user = sa.Column(sa.String(255), nullable=False, unique=True, index=True)
    fullname = sa.Column(sa.String(255), nullable=False, index=True)
    public_ssh_key = sa.Column(sa.Text, nullable=True)
    default_email = sa.Column(sa.Text, nullable=False)

    password = sa.Column(sa.Text, nullable=True)
    token = sa.Column(sa.String(50), nullable=True)
    created = sa.Column(
        sa.DateTime,
        nullable=False,
        default=sa.func.now())
    updated_on = sa.Column(
        sa.DateTime,
        nullable=False,
        default=sa.func.now(),
        onupdate=sa.func.now())

    # Relations
    group_objs = relation(
        "PagureGroup",
        secondary="pagure_user_group",
        primaryjoin="users.c.id==pagure_user_group.c.user_id",
        secondaryjoin="pagure_group.c.id==pagure_user_group.c.group_id",
        backref="users",
    )
    session = relation("PagureUserVisit", backref="user")

    @property
    def username(self):
        ''' Return the username. '''
        return self.user

    @property
    def groups(self):
        ''' Return the list of Group.group_name in which the user is. '''
        return [group.group_name for group in self.group_objs]

    def __repr__(self):
        ''' Return a string representation of this object. '''

        return 'User: %s - name %s' % (self.id, self.user)

    def to_json(self, public=False):
        ''' Return a representation of the User in a dictionnary. '''
        output = {
            'name': self.user,
            'fullname': self.fullname,
        }
        if not public:
            output['default_email'] = self.default_email
            output['emails'] = [email.email for email in self.emails]

        return output

class UserEmail(BASE):
    """ Stores email information about the users.

    Table -- user_emails
    """

    __tablename__ = 'user_emails'
    id = sa.Column(sa.Integer, primary_key=True)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    email = sa.Column(sa.String(255), nullable=False, unique=True)

    user = relation(
        'User', foreign_keys=[user_id], remote_side=[User.id],
        backref=backref(
            'emails', cascade="delete, delete-orphan", single_parent=True
        )
    )

class UserEmailPending(BASE):
    """ Stores email information about the users.

    Table -- user_emails_pending
    """

    __tablename__ = 'user_emails_pending'
    id = sa.Column(sa.Integer, primary_key=True)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    email = sa.Column(sa.String(255), nullable=False, unique=True)
    token = sa.Column(sa.String(50), nullable=True)
    created = sa.Column(
        sa.DateTime,
        nullable=False,
        default=sa.func.now())

    user = relation(
        'User', foreign_keys=[user_id], remote_side=[User.id],
        backref=backref(
            'emails_pending',
            cascade="delete, delete-orphan",
            single_parent=True
        )
    )

class Project(BASE):
    """ Stores the projects.

    Table -- projects
    """

    __tablename__ = 'projects'

    id = sa.Column(sa.Integer, primary_key=True)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    name = sa.Column(sa.String(255), nullable=False, index=True)
    description = sa.Column(sa.Text, nullable=True)
    url = sa.Column(sa.Text, nullable=True)
    _settings = sa.Column(sa.Text, nullable=True)
    hook_token = sa.Column(sa.String(40), nullable=False, unique=True)
    avatar_email = sa.Column(sa.Text, nullable=True)
    parent_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'projects.id', onupdate='CASCADE',
        ),
        nullable=True)
    _priorities = sa.Column(sa.Text, nullable=True)
    _milestones = sa.Column(sa.Text, nullable=True)

    date_created = sa.Column(sa.DateTime, nullable=False,
                             default=datetime.datetime.utcnow)

    parent = relation('Project', remote_side=[id], backref='forks')
    user = relation('User', foreign_keys=[user_id],
                    remote_side=[User.id], backref='projects')

    users = relation(
        'User',
        secondary="user_projects",
        primaryjoin="projects.c.id==user_projects.c.project_id",
        secondaryjoin="users.c.id==user_projects.c.user_id",
        backref='co_projects'
    )

    groups = relation(
        "PagureGroup",
        secondary="projects_groups",
        primaryjoin="projects.c.id==projects_groups.c.project_id",
        secondaryjoin="pagure_group.c.id==projects_groups.c.group_id",
        backref="projects",
    )

    unwatchers = relation(
        "Watcher",
        primaryjoin="and_(Project.id==Watcher.project_id, "
        "Watcher.watch=='0')"
    )

    @property
    def path(self):
        ''' Return the name of the git repo on the filesystem. '''
        return '%s.git' % self.fullname

    @property
    def is_fork(self):
        ''' Return a boolean specifying if the project is a fork or not '''
        return self.parent_id is not None

    @property
    def fullname(self):
        ''' Return the name of the git repo as user/project if it is a
        project forked, otherwise it returns the project name.
        '''
        str_name = self.name
        if self.parent_id:
            str_name = "forks/%s/%s" % (self.user.user, str_name)
        return str_name

    @property
    def tags_text(self):
        ''' Return the list of tags in a simple text form. '''
        return [tag.tag for tag in self.tags]

    @property
    def settings(self):
        """ Return the dict stored as string in the database as an actual
        dict object.
        """
        default = {
            'issue_tracker': True,
            'project_documentation': False,
            'pull_requests': True,
            'Only_assignee_can_merge_pull-request': False,
            'Minimum_score_to_merge_pull-request': -1,
            'Web-hooks': None,
            'Enforce_signed-off_commits_in_pull-request': False,
            'always_merge': False,
        }

        if self._settings:
            current = json.loads(self._settings)
            # Update the current dict with the new keys
            for key in default:
                if key not in current:
                    current[key] = default[key]
                elif key == 'Minimum_score_to_merge_pull-request':
                    current[key] = int(current[key])
                elif str(current[key]).lower() in ['true', 'y']:
                    current[key] = True
            return current
        else:
            return default

    @settings.setter
    def settings(self, settings):
        ''' Ensures the settings are properly saved. '''
        self._settings = json.dumps(settings)

    @property
    def milestones(self):
        """ Return the dict stored as string in the database as an actual
        dict object.
        """
        milestones = {}

        if self._milestones:
            milestones = json.loads(self._milestones)

        return milestones

    @milestones.setter
    def milestones(self, milestones):
        ''' Ensures the milestones are properly saved. '''
        self._milestones = json.dumps(milestones)

    @property
    def priorities(self):
        """ Return the dict stored as string in the database as an actual
        dict object.
        """
        priorities = {}

        if self._priorities:
            priorities = json.loads(self._priorities)

        return priorities

    @priorities.setter
    def priorities(self, priorities):
        ''' Ensures the priorities are properly saved. '''
        self._priorities = json.dumps(priorities)

    @property
    def open_requests(self):
        ''' Returns the number of open pull-requests for this project. '''
        return BASE.metadata.bind.query(
            PullRequest
        ).filter(
            self.id == PullRequest.project_id
        ).filter(
            PullRequest.status == 'Open'
        ).count()

    @property
    def open_tickets(self):
        ''' Returns the number of open tickets for this project. '''
        return BASE.metadata.bind.query(
            Issue
        ).filter(
            self.id == Issue.project_id
        ).filter(
            Issue.status == 'Open'
        ).count()

    @property
    def open_tickets_public(self):
        ''' Returns the number of open tickets for this project. '''
        return BASE.metadata.bind.query(
            Issue
        ).filter(
            self.id == Issue.project_id
        ).filter(
            Issue.status == 'Open'
        ).filter(
            Issue.private == False
        ).count()

    def to_json(self, public=False, api=False):
        ''' Return a representation of the project as JSON.
        '''

        output = {
            'id': self.id,
            'name': self.name,
            'description': self.description,
            'parent': self.parent.to_json(
                public=public, api=api) if self.parent else None,
            'date_created': self.date_created.strftime('%s'),
            'user': self.user.to_json(public=public),
            'tags': self.tags_text,
            'priorities': self.priorities,
        }
        if not api:
            output['settings'] = self.settings

        return output

class ProjectUser(BASE):
    """ Stores the user of a projects.

    Table -- user_projects
    """

    __tablename__ = 'user_projects'
    __table_args__ = (
        sa.UniqueConstraint('project_id', 'user_id'),
    )

    id = sa.Column(sa.Integer, primary_key=True)
    project_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'projects.id', onupdate='CASCADE',
        ),
        nullable=False)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)

class Issue(BASE):
    """ Stores the issues reported on a project.

    Table -- issues
    """

    __tablename__ = 'issues'

    id = sa.Column(sa.Integer, primary_key=True)
    uid = sa.Column(sa.String(32), unique=True, nullable=False)
    project_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'projects.id', onupdate='CASCADE',
        ),
        primary_key=True)
    title = sa.Column(
        sa.Text,
        nullable=False)
    content = sa.Column(
        sa.Text(),
        nullable=False)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    assignee_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=True,
        index=True)
    status = sa.Column(
        sa.String(255),
        sa.ForeignKey(
            'status_issue.status', onupdate='CASCADE',
        ),
        default='Open',
        nullable=False)
    private = sa.Column(sa.Boolean, nullable=False, default=False)
    priority = sa.Column(sa.Integer, nullable=True, default=None)

    date_created = sa.Column(sa.DateTime, nullable=False,
                             default=datetime.datetime.utcnow)

    closed_at = sa.Column(sa.DateTime, nullable=True)

    project = relation(
        'Project', foreign_keys=[project_id], remote_side=[Project.id],
        backref=backref(
            'issues', cascade="delete, delete-orphan", single_parent=True)
        )

    user = relation('User', foreign_keys=[user_id],
                    remote_side=[User.id], backref='issues')
    assignee = relation('User', foreign_keys=[assignee_id],
                        remote_side=[User.id], backref='assigned_issues')

    parents = relation(
        "Issue",
        secondary="issue_to_issue",
        primaryjoin="issues.c.uid==issue_to_issue.c.child_issue_id",
        secondaryjoin="issue_to_issue.c.parent_issue_id==issues.c.uid",
        backref="children",
    )

    def __repr__(self):
        return 'Issue(%s, project:%s, user:%s, title:%s)' % (
            self.id, self.project.name, self.user.user, self.title
        )

    @property
    def isa(self):
        ''' A string to allow finding out that this is an issue. '''
        return 'issue'

    @property
    def mail_id(self):
        ''' Return a unique reprensetation of the issue as string that
        can be used when sending emails.
        '''
        return '%s-ticket-%s@pagure' % (self.project.name, self.uid)

    @property
    def tags_text(self):
        ''' Return the list of tags in a simple text form. '''
        return [tag.tag for tag in self.tags]

    @property
    def depends_text(self):
        ''' Return the list of issue this issue depends on in simple text. '''
        return [issue.id for issue in self.children]

    @property
    def blocks_text(self):
        ''' Return the list of issue this issue blocks on in simple text. '''
        return [issue.id for issue in self.parents]

    @property
    def user_comments(self):
        ''' Return user comments only, filter it from notifications
        '''
        return [comment for comment in self.comments if not comment.notification]

    def to_json(self, public=False, with_comments=True):
        ''' Returns a dictionary representation of the issue.

        '''
        output = {
            'id': self.id,
            'title': self.title,
            'content': self.content,
            'status': self.status,
            'date_created': self.date_created.strftime('%s'),
            'closed_at': self.closed_at.strftime(
                '%s') if self.closed_at else None,
            'user': self.user.to_json(public=public),
            'private': self.private,
            'tags': self.tags_text,
            'depends': [str(item) for item in self.depends_text],
            'blocks': [str(item) for item in self.blocks_text],
            'assignee': self.assignee.to_json(
                public=public) if self.assignee else None,
            'priority': self.priority,
        }

        comments = []
        if with_comments:
            for comment in self.comments:
                comments.append(comment.to_json(public=public))

        output['comments'] = comments

        return output

class IssueToIssue(BASE):
    """ Stores the parent/child relationship between two issues.

    Table -- issue_to_issue
    """

    __tablename__ = 'issue_to_issue'

    parent_issue_id = sa.Column(
        sa.String(32),
        sa.ForeignKey(
            'issues.uid', ondelete='CASCADE', onupdate='CASCADE',
        ),
        primary_key=True)
    child_issue_id = sa.Column(
        sa.String(32),
        sa.ForeignKey(
            'issues.uid', ondelete='CASCADE', onupdate='CASCADE',
        ),
        primary_key=True)

class IssueComment(BASE):
    """ Stores the comments made on a commit/file.

    Table -- issue_comments
    """

    __tablename__ = 'issue_comments'

    id = sa.Column(sa.Integer, primary_key=True)
    issue_uid = sa.Column(
        sa.String(32),
        sa.ForeignKey(
            'issues.uid', ondelete='CASCADE', onupdate='CASCADE',
        ),
        index=True)
    comment = sa.Column(
        sa.Text(),
        nullable=False)
    parent_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'issue_comments.id', onupdate='CASCADE',
        ),
        nullable=True)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)

    notification = sa.Column(sa.Boolean, default=False, nullable=False)
    edited_on = sa.Column(sa.DateTime, nullable=True)
    editor_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=True)

    date_created = sa.Column(sa.DateTime, nullable=False,
                             default=datetime.datetime.utcnow)

    issue = relation(
        'Issue', foreign_keys=[issue_uid], remote_side=[Issue.uid],
        backref=backref(
            'comments', cascade="delete, delete-orphan",
            order_by="IssueComment.date_created"
        ),
    )
    user = relation('User', foreign_keys=[user_id],
                    remote_side=[User.id], backref='comment_issues')
    editor = relation('User', foreign_keys=[editor_id],
                         remote_side=[User.id])

    @property
    def mail_id(self):
        ''' Return a unique reprensetation of the issue as string that
        can be used when sending emails.
        '''
        return '%s-ticket-%s-%s@pagure' % (
            self.issue.project.name, self.issue.uid, self.id)

    @property
    def parent(self):
        ''' Return the parent, in this case the issue object. '''
        return self.issue

    def to_json(self, public=False):
        ''' Returns a dictionary representation of the issue.

        '''
        output = {
            'id': self.id,
            'comment': self.comment,
            'parent': self.parent_id,
            'date_created': self.date_created.strftime('%s'),
            'user': self.user.to_json(public=public),
            'edited_on': self.edited_on.strftime('%s') if self.edited_on else None,
            'editor': self.editor.to_json(public=public) if self.editor_id else None,
            'notification': self.notification,
        }
        return output

class Tag(BASE):
    """ Stores the tags.

    Table -- tags
    """

    __tablename__ = 'tags'

    tag = sa.Column(sa.String(255), primary_key=True)
    date_created = sa.Column(sa.DateTime, nullable=False,
                             default=datetime.datetime.utcnow)

class TagIssue(BASE):
    """ Stores the tag associated with an issue.

    Table -- tags_issues
    """

    __tablename__ = 'tags_issues'

    tag = sa.Column(
        sa.String(255),
        sa.ForeignKey(
            'tags.tag', ondelete='CASCADE', onupdate='CASCADE',
        ),
        primary_key=True)
    issue_uid = sa.Column(
        sa.String(32),
        sa.ForeignKey(
            'issues.uid', ondelete='CASCADE', onupdate='CASCADE',
        ),
        primary_key=True)
    date_created = sa.Column(sa.DateTime, nullable=False,
                             default=datetime.datetime.utcnow)

    issue = relation(
        'Issue', foreign_keys=[issue_uid], remote_side=[Issue.uid],
        backref=backref(
            'tags', cascade="delete, delete-orphan", single_parent=True)
        )

    def __repr__(self):
        return 'TagIssue(issue:%s, tag:%s)' % (self.issue.id, self.tag)

class TagProject(BASE):
    """ Stores the tag associated with a project.

    Table -- tags_projects
    """

    __tablename__ = 'tags_projects'

    tag = sa.Column(
        sa.String(255),
        sa.ForeignKey(
            'tags.tag', ondelete='CASCADE', onupdate='CASCADE',
        ),
        primary_key=True)
    project_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'projects.id', ondelete='CASCADE', onupdate='CASCADE',
        ),
        primary_key=True)
    date_created = sa.Column(sa.DateTime, nullable=False,
                             default=datetime.datetime.utcnow)

    project = relation(
        'Project', foreign_keys=[project_id], remote_side=[Project.id],
        backref=backref(
            'tags', cascade="delete, delete-orphan", single_parent=True)
        )

    def __repr__(self):
        return 'TagProject(project:%s, tag:%s)' % (
            self.project.fullname, self.tag)

class PullRequest(BASE):
    """ Stores the pull requests created on a project.

    Table -- pull_requests
    """

    __tablename__ = 'pull_requests'

    id = sa.Column(sa.Integer, primary_key=True)
    uid = sa.Column(sa.String(32), unique=True, nullable=False)
    title = sa.Column(
        sa.Text,
        nullable=False)
    project_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'projects.id', ondelete='CASCADE', onupdate='CASCADE',
        ),
        primary_key=True)
    branch = sa.Column(
        sa.Text(),
        nullable=False)
    project_id_from = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'projects.id', ondelete='CASCADE', onupdate='CASCADE',
        ),
        nullable=True)
    remote_git = sa.Column(
        sa.Text(),
        nullable=True)
    branch_from = sa.Column(
        sa.Text(),
        nullable=False)
    commit_start = sa.Column(
        sa.Text(),
        nullable=True)
    commit_stop = sa.Column(
        sa.Text(),
        nullable=True)
    initial_comment = sa.Column(
        sa.Text(),
        nullable=True)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    assignee_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=True,
        index=True)
    merge_status = sa.Column(
        sa.Enum(
            'NO_CHANGE', 'FFORWARD', 'CONFLICTS', 'MERGE',
            name='merge_status_enum',
        ),
        nullable=True)

    status = sa.Column(
        sa.String(255),
        sa.ForeignKey(
            'status_pull_requests.status', onupdate='CASCADE',
        ),
        default='Open',
        nullable=False)
    closed_by_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=True)
    closed_at = sa.Column(
        sa.DateTime,
        nullable=True)

    date_created = sa.Column(sa.DateTime, nullable=False,
                             default=datetime.datetime.utcnow)
    updated_on = sa.Column(
        sa.DateTime,
        nullable=False,
        default=sa.func.now(),
        onupdate=sa.func.now())

    __table_args__ = (
        sa.CheckConstraint(
            'NOT(project_id_from IS NULL AND remote_git IS NULL)',
        ),
    )

    project = relation(
        'Project', foreign_keys=[project_id], remote_side=[Project.id],
        backref=backref(
            'requests', cascade="delete, delete-orphan",
        ),
        single_parent=True)
    project_from = relation(
        'Project', foreign_keys=[project_id_from], remote_side=[Project.id])

    user = relation('User', foreign_keys=[user_id],
                    remote_side=[User.id], backref='pull_requests')
    assignee = relation('User', foreign_keys=[assignee_id],
                        remote_side=[User.id], backref='assigned_requests')
    closed_by = relation('User', foreign_keys=[closed_by_id],
                         remote_side=[User.id], backref='closed_requests')

    def __repr__(self):
        return 'PullRequest(%s, project:%s, user:%s, title:%s)' % (
            self.id, self.project.name, self.user.user, self.title
        )

    @property
    def isa(self):
        ''' A string to allow finding out that this is an pull-request. '''
        return 'pull-request'

    @property
    def mail_id(self):
        ''' Return a unique reprensetation of the issue as string that
        can be used when sending emails.
        '''
        return '%s-pull-request-%s@pagure' % (self.project.name, self.uid)

    @property
    def discussion(self):
        ''' Return the list of comments related to the pull-request itself,
        ie: not related to a specific commit.
        '''
        return [
            comment
            for comment in self.comments
            if not comment.commit_id
        ]

    @property
    def score(self):
        ''' Return the review score of the pull-request by checking the
        number of +1, -1, :thumbup: and :thumbdown: in the comment of the
        pull-request.
        This includes only the main comments not the inline ones.

        An user can only give one +1 and one -1.
        '''
        positive = set()
        negative = set()
        for comment in self.discussion:
            for word in ['+1', ':thumbsup:']:
                if word in comment.comment:
                    positive.add(comment.user_id)
                    break
            for word in ['-1', ':thumbsdown:']:
                if word in comment.comment:
                    negative.add(comment.user_id)
                    break

        return len(positive) - len(negative)

    @property
    def remote(self):
        ''' Return whether the current PullRequest is a remote pull-request
        or not.
        '''
        return self.remote_git is not None

    @property
    def user_comments(self):
        ''' Return user comments only, filter it from notifications
        '''
        return [comment for comment in self.comments if not comment.notification]

    def to_json(self, public=False, api=False, with_comments=True):
        ''' Returns a dictionnary representation of the pull-request.

        '''
        output = {
            'id': self.id,
            'uid': self.uid,
            'title': self.title,
            'branch': self.branch,
            'project': self.project.to_json(public=public, api=api),
            'branch_from': self.branch_from,
            'repo_from': self.project_from.to_json(
                public=public, api=api) if self.project_from else None,
            'remote_git': self.remote_git,
            'date_created': self.date_created.strftime('%s'),
            'updated_on': self.updated_on.strftime('%s'),
            'closed_at': self.closed_at.strftime(
                '%s') if self.closed_at else None,
            'user': self.user.to_json(public=public),
            'assignee': self.assignee.to_json(
                public=public) if self.assignee else None,
            'status': self.status,
            'commit_start': self.commit_start,
            'commit_stop': self.commit_stop,
            'closed_by': self.closed_by.to_json(
                public=public) if self.closed_by else None,
            'initial_comment': self.initial_comment,
        }

        comments = []
        if with_comments:
            for comment in self.comments:
                comments.append(comment.to_json(public=public))

        output['comments'] = comments

        return output

class PullRequestComment(BASE):
    """ Stores the comments made on a pull-request.

    Table -- pull_request_comments
    """

    __tablename__ = 'pull_request_comments'

    id = sa.Column(sa.Integer, primary_key=True)
    pull_request_uid = sa.Column(
        sa.String(32),
        sa.ForeignKey(
            'pull_requests.uid', ondelete='CASCADE', onupdate='CASCADE',
        ),
        nullable=False)
    commit_id = sa.Column(
        sa.String(40),
        nullable=True,
        index=True)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    filename = sa.Column(
        sa.Text,
        nullable=True)
    line = sa.Column(
        sa.Integer,
        nullable=True)
    tree_id = sa.Column(
        sa.String(40),
        nullable=True)
    comment = sa.Column(
        sa.Text(),
        nullable=False)
    parent_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'pull_request_comments.id', onupdate='CASCADE',
        ),
        nullable=True)
    notification = sa.Column(sa.Boolean, default=False, nullable=False)
    edited_on = sa.Column(sa.DateTime, nullable=True)
    editor_id = sa.Column(
        sa.Integer,
        sa.ForeignKey('users.id', onupdate='CASCADE'),
        nullable=True)

    date_created = sa.Column(sa.DateTime, nullable=False,
                             default=datetime.datetime.utcnow)

    user = relation('User', foreign_keys=[user_id],
                    remote_side=[User.id],
                    backref=backref(
                        'pull_request_comments',
                        order_by="PullRequestComment.date_created"))
    pull_request = relation(
        'PullRequest',
        backref=backref(
            'comments', cascade="delete, delete-orphan",
            order_by="PullRequestComment.date_created"
        ),
        foreign_keys=[pull_request_uid],
        remote_side=[PullRequest.uid])
    editor = relation('User', foreign_keys=[editor_id],
                         remote_side=[User.id])

    @property
    def mail_id(self):
        ''' Return a unique reprensetation of the issue as string that
        can be used when sending emails.
        '''
        return '%s-pull-request-%s-%s@pagure' % (
            self.pull_request.project.name, self.pull_request.uid, self.id)

    @property
    def parent(self):
        ''' Return the parent, in this case the pull_request object. '''
        return self.pull_request

    def to_json(self, public=False):
        return {
            'id': self.id,
            'commit': self.commit_id,
            'tree': self.tree_id,
            'filename': self.filename,
            'line': self.line,
            'comment': self.comment,
            'parent': self.parent_id,
            'date_created': self.date_created.strftime('%s'),
            'user': self.user.to_json(public=public),
            'edited_on': self.edited_on.strftime('%s') if self.edited_on else None,
            'editor': self.editor.to_json(public=public) if self.editor_id else None,
            'notification': self.notification,
        }

class PullRequestFlag(BASE):
    """ Stores the flags attached to a pull-request.

    Table -- pull_request_flags
    """

    __tablename__ = 'pull_request_flags'

    id = sa.Column(sa.Integer, primary_key=True)
    uid = sa.Column(sa.String(32), unique=True, nullable=False)
    pull_request_uid = sa.Column(
        sa.String(32),
        sa.ForeignKey(
            'pull_requests.uid', ondelete='CASCADE', onupdate='CASCADE',
        ),
        nullable=False)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    username = sa.Column(
        sa.Text(),
        nullable=False)
    percent = sa.Column(
        sa.Integer(),
        nullable=False)
    comment = sa.Column(
        sa.Text(),
        nullable=False)
    url = sa.Column(
        sa.Text(),
        nullable=False)

    date_created = sa.Column(sa.DateTime, nullable=False,
                             default=datetime.datetime.utcnow)

    user = relation('User', foreign_keys=[user_id],
                    remote_side=[User.id],
                    backref=backref(
                        'pull_request_flags',
                        order_by="PullRequestFlag.date_created"))

    pull_request = relation(
        'PullRequest',
        backref=backref(
            'flags', cascade="delete, delete-orphan",
        ),
        foreign_keys=[pull_request_uid],
        remote_side=[PullRequest.uid])

    def to_json(self, public=False):
        ''' Returns a dictionnary representation of the pull-request.

        '''
        output = {
            'uid': self.uid,
            'pull_request_uid': self.pull_request_uid,
            'username': self.username,
            'percent': self.percent,
            'comment': self.comment,
            'url': self.url,
            'date_created': self.date_created.strftime('%s'),
            'user': self.user.to_json(public=public),
        }

        return output

class PagureGroupType(BASE):
    """
    A list of the type a group can have definition.
    """

    # names like "Group", "Order" and "User" are reserved words in SQL
    # so we set the name to something safe for SQL
    __tablename__ = 'pagure_group_type'

    group_type = sa.Column(sa.String(16), primary_key=True)
    created = sa.Column(
        sa.DateTime, nullable=False, default=datetime.datetime.utcnow)

    def __repr__(self):
        ''' Return a string representation of this object. '''

        return 'GroupType: %s' % (self.group_type)

class PagureGroup(BASE):
    """
    An ultra-simple group definition.
    """

    # names like "Group", "Order" and "User" are reserved words in SQL
    # so we set the name to something safe for SQL
    __tablename__ = 'pagure_group'

    id = sa.Column(sa.Integer, primary_key=True)
    group_name = sa.Column(sa.String(16), nullable=False, unique=True)
    group_type = sa.Column(
        sa.String(16),
        sa.ForeignKey(
            'pagure_group_type.group_type',
        ),
        default='user',
        nullable=False)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    created = sa.Column(
        sa.DateTime, nullable=False, default=datetime.datetime.utcnow)

    creator = relation(
        'User',
        foreign_keys=[user_id],
        remote_side=[User.id],
        backref=backref('groups_created')
    )

    def __repr__(self):
        ''' Return a string representation of this object. '''

        return 'Group: %s - name %s' % (self.id, self.group_name)

class ProjectGroup(BASE):
    """
    Association table linking the projects table to the pagure_group table.
    This allow linking projects to groups.
    """

    __tablename__ = 'projects_groups'

    project_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'projects.id', onupdate='CASCADE', ondelete='CASCADE',
        ),
        primary_key=True)
    group_id = sa.Column(
        sa.Integer, sa.ForeignKey(
            'pagure_group.id',
        ),
        primary_key=True)

    # Constraints
    __table_args__ = (
        sa.UniqueConstraint(
            'project_id', 'group_id'),
    )

class Watcher(BASE):
    """ Stores the user of a projects.

    Table -- watchers
    """

    __tablename__ = 'watchers'
    __table_args__ = (
        sa.UniqueConstraint('project_id', 'user_id'),
    )

    id = sa.Column(sa.Integer, primary_key=True)
    project_id = sa.Column(
        sa.Integer,
        sa.ForeignKey('projects.id', onupdate='CASCADE'),
        nullable=False)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey('users.id', onupdate='CASCADE'),
        nullable=False,
        index=True)
    watch = sa.Column(
        sa.Boolean,
        nullable=False)

    user = relation(
        'User', foreign_keys=[user_id], remote_side=[User.id],
        backref=backref(
            'watchers', cascade="delete, delete-orphan"
        ),
    )

    project = relation(
        'Project', foreign_keys=[project_id], remote_side=[Project.id],
        backref=backref(
            'watchers', cascade="delete, delete-orphan",
        ),
    )

#
# Class and tables specific for the API/token access
#

class ACL(BASE):
    """
    Table listing all the rights a token can be given
    """

    __tablename__ = 'acls'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(32), unique=True, nullable=False)
    description = sa.Column(sa.Text(), nullable=False)
    created = sa.Column(
        sa.DateTime, nullable=False, default=datetime.datetime.utcnow)

    def __repr__(self):
        ''' Return a string representation of this object. '''

        return 'ACL: %s - name %s' % (self.id, self.name)

class Token(BASE):
    """
    Table listing all the tokens per user and per project
    """

    __tablename__ = 'tokens'

    id = sa.Column(sa.String(64), primary_key=True)
    user_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'users.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    project_id = sa.Column(
        sa.Integer,
        sa.ForeignKey(
            'projects.id', onupdate='CASCADE',
        ),
        nullable=False,
        index=True)
    expiration = sa.Column(
        sa.DateTime, nullable=False, default=datetime.datetime.utcnow)
    created = sa.Column(
        sa.DateTime, nullable=False, default=datetime.datetime.utcnow)

    acls = relation(
        "ACL",
        secondary="tokens_acls",
        primaryjoin="tokens.c.id==tokens_acls.c.token_id",
        secondaryjoin="acls.c.id==tokens_acls.c.acl_id",
    )

    user = relation(
        'User',
        backref=backref(
            'tokens', cascade="delete, delete-orphan",
            order_by="Token.created"
        ),
        foreign_keys=[user_id],
        remote_side=[User.id])

    project = relation(
        'Project',
        backref=backref(
            'tokens', cascade="delete, delete-orphan",
        ),
        foreign_keys=[project_id],
        remote_side=[Project.id])

    def __repr__(self):
        ''' Return a string representation of this object. '''

        return 'Token: %s - name %s' % (self.id, self.expiration)

    @property
    def expired(self):
        ''' Returns wether a token has expired or not. '''
        if datetime.datetime.utcnow().date() >= self.expiration.date():
            return True
        else:
            return False

    @property
    def acls_list(self):
        ''' Return a list containing the name of each ACLs this token has.
        '''
        return sorted([str(acl.name) for acl in self.acls])

    @property
    def acls_list_pretty(self):
        '''
        Return a list containing the description of each ACLs this token has.
        '''
        return [acl.description for acl in sorted(
            self.acls, key=operator.attrgetter('name'))]

class TokenAcl(BASE):
    """
    Association table linking the tokens table to the acls table.
    This allow linking token to acl.
    """

    __tablename__ = 'tokens_acls'

    token_id = sa.Column(
        sa.String(64), sa.ForeignKey(
            'tokens.id',
        ),
        primary_key=True)
    acl_id = sa.Column(
        sa.Integer, sa.ForeignKey(
            'acls.id',
        ),
        primary_key=True)

    # Constraints
    __table_args__ = (
        sa.UniqueConstraint(
            'token_id', 'acl_id'),
    )

# ##########################################################
# These classes are only used if you're using the `local`
#                  authentication method
# ##########################################################

class PagureUserVisit(BASE):
    """
    Table storing the visits of the user.
    """

    __tablename__ = 'pagure_user_visit'

    id = sa.Column(sa.Integer, primary_key=True)
    user_id = sa.Column(
        sa.Integer, sa.ForeignKey(
            'users.id',
        ),
        nullable=False)
    visit_key = sa.Column(
        sa.String(40), nullable=False, unique=True, index=True)
    user_ip = sa.Column(sa.String(50), nullable=False)
    created = sa.Column(
        sa.DateTime, nullable=False, default=datetime.datetime.utcnow)
    expiry = sa.Column(sa.DateTime)

class PagureUserGroup(BASE):
    """
    Association table linking the mm_user table to the mm_group table.
    This allow linking users to groups.
    """

    __tablename__ = 'pagure_user_group'

    user_id = sa.Column(
        sa.Integer, sa.ForeignKey(
            'users.id',
        ),
        primary_key=True)
    group_id = sa.Column(
        sa.Integer, sa.ForeignKey(
            'pagure_group.id',
        ),
        primary_key=True)

    # Constraints
    __table_args__ = (
        sa.UniqueConstraint(
            'user_id', 'group_id'),
    )