'Return a list of packages on the named ``manager`` for which the
participant has verified an email address on Gratipay, along with the
current package owner on Gratipay (if any).
:param string manager: the name of the package manager on which to look
for potential packages
:return: a list of (:py:class:`~gratipay.models.package.Package`,
:py:class:`Participant`, is_primary, email_address) tuples, where the
participant is the one who has already claimed the package (or ``None``),
and the email address is the single best match (primary, then
alphabetically first from among non-primary verified addresses)'

def get_packages_for_claiming(self, manager):

    return self.db.all(u'\n\n                                                WITH    verified_emails    AS    (\n                                                                SELECT    e.address\n                                                                                    ,    e.address    =    p.email_address    is_primary\n                                                                        FROM    emails    e\n                                                    LEFT    JOIN    participants    p\n                                                                                ON    p.id    =    e.participant_id\n                                                                    WHERE    e.participant_id=%s\n                                                                            AND    e.verified    is    true\n                                                )\n                                                SELECT    pkg.*::packages                                                                        package\n                                                                    ,    p.*::participants                                                                claimed_by\n                                                                    ,    (SELECT    is_primary\n                                                                                        FROM    verified_emails\n                                                                                    WHERE    address    =    ANY(emails)\n                                                                        ORDER    BY    is_primary    DESC,    address\n                                                                                    LIMIT    1)                                                                                            email_address_is_primary\n                                                                    ,    (SELECT    address\n                                                                                        FROM    verified_emails\n                                                                                    WHERE    address    =    ANY(emails)\n                                                                        ORDER    BY    is_primary    DESC,    address\n                                                                                    LIMIT    1)                                                                                            email_address\n                                                        FROM    packages    pkg\n                                    LEFT    JOIN    teams_to_packages    tp\n                                                                ON    pkg.id    =    tp.package_id\n                                    LEFT    JOIN    teams    t\n                                                                ON    t.id    =    tp.team_id\n                                    LEFT    JOIN    participants    p\n                                                                ON    t.owner    =    p.username\n                                                    WHERE    package_manager=%s\n                                                            AND    pkg.emails    &&    array(SELECT    address    FROM    verified_emails)\n                                        ORDER    BY    email_address_is_primary    DESC\n                                                                    ,    email_address    ASC\n                                                                    ,    pkg.name    ASC\n\n                                ', (self.id, manager))
