__tablename__ = 'changeset_statuses'

    __table_args__ = (

        Index('cs_revision_idx', 'revision'),

        Index('cs_version_idx', 'version'),

        Index('cs_pull_request_id_idx', 'pull_request_id'),

        Index('cs_changeset_comment_id_idx', 'changeset_comment_id'),

        Index('cs_pull_request_id_user_id_version_idx', 'pull_request_id', 'user_id', 'version'),

        Index('cs_repo_id_pull_request_id_idx', 'repo_id', 'pull_request_id'),

        UniqueConstraint('repo_id', 'revision', 'version'),

        _table_args_default_dict,

    )

    STATUS_NOT_REVIEWED = DEFAULT = 'not_reviewed'

    STATUS_APPROVED = 'approved'

    STATUS_REJECTED = 'rejected' # is shown as "Not approved" - TODO: change database content / scheme

    STATUS_UNDER_REVIEW = 'under_review'

    STATUSES = [

        (STATUS_NOT_REVIEWED, _("Not reviewed")),  # (no icon) and default

        (STATUS_UNDER_REVIEW, _("Under review")),

        (STATUS_REJECTED, _("Not approved")),

        (STATUS_APPROVED, _("Approved")),

    ]

    STATUSES_DICT = dict(STATUSES)

    changeset_status_id = Column(Integer(), primary_key=True)

    repo_id = Column(Integer(), ForeignKey('repositories.repo_id'), nullable=False)

    user_id = Column(Integer(), ForeignKey('users.user_id'), nullable=False)

    revision = Column(String(40), nullable=True)

    status = Column(String(128), nullable=False, default=DEFAULT)

    comment_id = Column('changeset_comment_id', Integer(), ForeignKey('changeset_comments.comment_id'), nullable=False)

    modified_at = Column(DateTime(), nullable=False, default=datetime.datetime.now)

    version = Column(Integer(), nullable=False, default=0)

    pull_request_id = Column(Integer(), ForeignKey('pull_requests.pull_request_id'), nullable=True)

    author = relationship('User')

    repo = relationship('Repository')

    comment = relationship('ChangesetComment')

    pull_request = relationship('PullRequest')

    def __repr__(self):

        return "<%s %r by %r>" % (

            self.__class__.__name__,

            self.status, self.author

        )

    @classmethod

    def get_status_lbl(cls, value):

    @property

    def status_lbl(self):

        return ChangesetStatus.get_status_lbl(self.status)

    def __json__(self):

        return dict(

            status=self.status,

            modified_at=self.modified_at.replace(microsecond=0),

            reviewer=self.author.username,

            )

class PullRequest(meta.Base, BaseDbModel):

    __tablename__ = 'pull_requests'

    __table_args__ = (

        Index('pr_org_repo_id_idx', 'org_repo_id'),

        Index('pr_other_repo_id_idx', 'other_repo_id'),

        _table_args_default_dict,

    )

    # values for .status

    STATUS_NEW = 'new'

    STATUS_CLOSED = 'closed'

    pull_request_id = Column(Integer(), primary_key=True)

    title = Column(Unicode(255), nullable=False)

    description = Column(UnicodeText(), nullable=False)

    status = Column(Unicode(255), nullable=False, default=STATUS_NEW) # only for closedness, not approve/reject/etc

    created_on = Column(DateTime(timezone=False), nullable=False, default=datetime.datetime.now)

    updated_on = Column(DateTime(timezone=False), nullable=False, default=datetime.datetime.now)

    owner_id = Column('user_id', Integer(), ForeignKey('users.user_id'), nullable=False)

    _revisions = Column('revisions', UnicodeText(), nullable=False)

    org_repo_id = Column(Integer(), ForeignKey('repositories.repo_id'), nullable=False)

    org_ref = Column(Unicode(255), nullable=False)

    other_repo_id = Column(Integer(), ForeignKey('repositories.repo_id'), nullable=False)

    other_ref = Column(Unicode(255), nullable=False)

    @hybrid_property

    def revisions(self):

        return self._revisions.split(':')

    @revisions.setter

    def revisions(self, val):

        self._revisions = ':'.join(val)

    @property

    def org_ref_parts(self):

class EmailNotificationModel(object):

    TYPE_CHANGESET_COMMENT = NotificationModel.TYPE_CHANGESET_COMMENT

    TYPE_MESSAGE = NotificationModel.TYPE_MESSAGE # only used for testing

    # NotificationModel.TYPE_MENTION is not used

    TYPE_PASSWORD_RESET = 'password_link'

    TYPE_REGISTRATION = NotificationModel.TYPE_REGISTRATION

    TYPE_PULL_REQUEST = NotificationModel.TYPE_PULL_REQUEST

    TYPE_PULL_REQUEST_COMMENT = NotificationModel.TYPE_PULL_REQUEST_COMMENT

    TYPE_DEFAULT = 'default'

    def __init__(self):

        super(EmailNotificationModel, self).__init__()

        self._tmpl_lookup = app_globals.mako_lookup

        self.email_types = {

            self.TYPE_CHANGESET_COMMENT: 'changeset_comment',

            self.TYPE_PASSWORD_RESET: 'password_reset',

            self.TYPE_REGISTRATION: 'registration',

            self.TYPE_DEFAULT: 'default',

            self.TYPE_PULL_REQUEST: 'pull_request',

            self.TYPE_PULL_REQUEST_COMMENT: 'pull_request_comment',

        }

        self._subj_map = {

            self.TYPE_CHANGESET_COMMENT: _('[Comment] %(repo_name)s changeset %(short_id)s "%(message_short)s" on %(branch)s by %(cs_author_username)s'),

            self.TYPE_MESSAGE: 'Test Message',

            # self.TYPE_PASSWORD_RESET

            self.TYPE_REGISTRATION: _('New user %(new_username)s registered'),

            # self.TYPE_DEFAULT

            self.TYPE_PULL_REQUEST: _('[Review] %(repo_name)s PR %(pr_nice_id)s "%(pr_title_short)s" from %(pr_source_branch)s by %(pr_owner_username)s'),

            self.TYPE_PULL_REQUEST_COMMENT: _('[Comment] %(repo_name)s PR %(pr_nice_id)s "%(pr_title_short)s" from %(pr_source_branch)s by %(pr_owner_username)s'),

        }

    def get_email_description(self, type_, **kwargs):

        """

        return subject for email based on given type

        """

        tmpl = self._subj_map[type_]

        try:

            subj = tmpl % kwargs

        except KeyError as e:

            log.error('error generating email subject for %r from %s: %s', type_, ', '.join(self._subj_map), e)

            raise

        # gmail doesn't do proper threading but will ignore leading square

        # bracket content ... so that is where we put status info

        bracket_tags = []

        status_change = kwargs.get('status_change')

        if status_change:

        if kwargs.get('closing_pr'):

            bracket_tags.append(_('Closing'))

        if bracket_tags:

            if subj.startswith('['):

                subj = '[' + ', '.join(bracket_tags) + ': ' + subj[1:]

            else:

                subj = '[' + ', '.join(bracket_tags) + '] ' + subj

        return subj

    def get_email_tmpl(self, type_, content_type, **kwargs):

        """

        return generated template for email based on given type

        """

        base = 'email/' + self.email_types.get(type_, self.email_types[self.TYPE_DEFAULT]) + '.' + content_type

        email_template = self._tmpl_lookup.get_template(base)

        # translator and helpers inject

        _kwargs = {'_': _,

                   'webutils': webutils,

                   'c': c}

        _kwargs.update(kwargs)

        if content_type == 'html':

            _kwargs.update({

                "color_text": "#202020",

                "color_emph": "#395fa0",

                "color_link": "#395fa0",

                "color_border": "#ddd",

                "color_background_grey": "#f9f9f9",

                "color_button": "#395fa0",

                "monospace_style": "font-family:Lucida Console,Consolas,Monaco,Inconsolata,Liberation Mono,monospace",

                "sans_style": "font-family:Helvetica,Arial,sans-serif",

                })

            _kwargs.update({

                "default_style": "%(sans_style)s;font-weight:200;font-size:14px;line-height:17px;color:%(color_text)s" % _kwargs,

                "comment_style": "%(monospace_style)s;white-space:pre-wrap" % _kwargs,

                "data_style": "border:%(color_border)s 1px solid;background:%(color_background_grey)s" % _kwargs,

                "emph_style": "font-weight:600;color:%(color_emph)s" % _kwargs,

                "link_style": "color:%(color_link)s;text-decoration:none" % _kwargs,

                "link_text_style": "color:%(color_text)s;text-decoration:none;border:%(color_border)s 1px solid;background:%(color_background_grey)s" % _kwargs,

                })

        log.debug('rendering tmpl %s with kwargs %s', base, _kwargs)

        return email_template.render_unicode(**_kwargs)

@celerylib.task

def send_email(recipients, subject, body='', html_body='', headers=None, from_name=None):

    """

    Sends an email with defined parameters from the .ini files.