'Given an AccountElsewhere or a tuple (platform_name, user_id),
associate an elsewhere account.
Returns None or raises NeedConfirmation.
This method associates an account on another platform (GitHub, Twitter,
etc.) with the given Gratipay participant. Every account elsewhere has an
associated Gratipay participant account, even if its only a stub
participant.
In certain circumstances, we want to present the user with a
confirmation before proceeding to transfer the account elsewhere to
the new Gratipay account; NeedConfirmation is the signal to request
confirmation. If it was the last account elsewhere connected to the old
Gratipay account, then we absorb the old Gratipay account into the new one,
effectively archiving the old account.
Here\'s what absorbing means:
- consolidated tips to and fro are set up for the new participant
Amounts are summed, so if alice tips bob $1 and carl $1, and
then bob absorbs carl, then alice tips bob $2(!) and carl $0.
And if bob tips alice $1 and carl tips alice $1, and then bob
absorbs carl, then bob tips alice $2(!) and carl tips alice $0.
The ctime of each new consolidated tip is the older of the two
tips that are being consolidated.
If alice tips bob $1, and alice absorbs bob, then alice tips
bob $0.
If alice tips bob $1, and bob absorbs alice, then alice tips
bob $0.
- all tips to and from the other participant are set to zero
- the absorbed username is released for reuse
- the absorption is recorded in an absorptions table
This is done in one transaction.'

def take_over(self, account, have_confirmation=False):

    if isinstance(account, AccountElsewhere):
        (platform, user_id) = (account.platform, account.user_id)
    else:
        (platform, user_id) = account
    CREATE_TEMP_TABLE_FOR_UNIQUE_TIPS = u'\n\n                                CREATE    TEMP    TABLE    __temp_unique_tips    ON    COMMIT    drop    AS\n\n                                                --    Get    all    the    latest    tips    from    everyone    to    everyone.\n\n                                                SELECT    ctime,    tipper,    tippee,    amount,    is_funded\n                                                        FROM    current_tips\n                                                    WHERE    amount    >    0;\n\n                                '
    CONSOLIDATE_TIPS_RECEIVING = u"\n\n                                                --    Create    a    new    set    of    tips,    one    for    each    current    tip    *to*    either\n                                                --    the    dead    or    the    live    account.    If    a    user    was    tipping    both    the\n                                                --    dead    and    the    live    account,    then    we    create    one    new    combined    tip\n                                                --    to    the    live    account    (via    the    GROUP    BY    and    sum()).\n\n                                                INSERT    INTO    tips    (ctime,    tipper,    tippee,    amount,    is_funded)\n\n                                                                    SELECT    min(ctime),    tipper,    %(live)s    AS    tippee,    sum(amount),    bool_and(is_funded)\n\n                                                                            FROM    __temp_unique_tips\n\n                                                                        WHERE    (tippee    =    %(dead)s    OR    tippee    =    %(live)s)\n                                                                                                --    Include    tips    *to*    either    the    dead    or    live    account.\n\n                                                                AND    NOT    (tipper    =    %(dead)s    OR    tipper    =    %(live)s)\n                                                                                                --    Don't    include    tips    *from*    the    dead    or    live    account,\n                                                                                                --    lest    we    convert    cross-tipping    to    self-tipping.\n\n                                                            GROUP    BY    tipper\n\n                                "
    CONSOLIDATE_TIPS_GIVING = u"\n\n                                                --    Create    a    new    set    of    tips,    one    for    each    current    tip    *from*    either\n                                                --    the    dead    or    the    live    account.    If    both    the    dead    and    the    live\n                                                --    account    were    tipping    a    given    user,    then    we    create    one    new\n                                                --    combined    tip    from    the    live    account    (via    the    GROUP    BY    and    sum()).\n\n                                                INSERT    INTO    tips    (ctime,    tipper,    tippee,    amount)\n\n                                                                    SELECT    min(ctime),    %(live)s    AS    tipper,    tippee,    sum(amount)\n\n                                                                            FROM    __temp_unique_tips\n\n                                                                        WHERE    (tipper    =    %(dead)s    OR    tipper    =    %(live)s)\n                                                                                                --    Include    tips    *from*    either    the    dead    or    live    account.\n\n                                                                AND    NOT    (tippee    =    %(dead)s    OR    tippee    =    %(live)s)\n                                                                                                --    Don't    include    tips    *to*    the    dead    or    live    account,\n                                                                                                --    lest    we    convert    cross-tipping    to    self-tipping.\n\n                                                            GROUP    BY    tippee\n\n                                "
    ZERO_OUT_OLD_TIPS_RECEIVING = u'\n\n                                                INSERT    INTO    tips    (ctime,    tipper,    tippee,    amount)\n\n                                                                SELECT    ctime,    tipper,    tippee,    0    AS    amount\n                                                                        FROM    __temp_unique_tips\n                                                                    WHERE    tippee=%s\n\n                                '
    ZERO_OUT_OLD_TIPS_GIVING = u'\n\n                                                INSERT    INTO    tips    (ctime,    tipper,    tippee,    amount)\n\n                                                                SELECT    ctime,    tipper,    tippee,    0    AS    amount\n                                                                        FROM    __temp_unique_tips\n                                                                    WHERE    tipper=%s\n\n                                '
    TRANSFER_BALANCE_1 = u'\n\n                                                UPDATE    participants\n                                                            SET    balance    =    (balance    -    %(balance)s)\n                                                    WHERE    username=%(dead)s\n                                    RETURNING    balance;\n\n                                '
    TRANSFER_BALANCE_2 = u"\n\n                                                INSERT    INTO    transfers    (tipper,    tippee,    amount,    context)\n                                                SELECT    %(dead)s,    %(live)s,    %(balance)s,    'take-over'\n                                                    WHERE    %(balance)s    >    0;\n\n                                                UPDATE    participants\n                                                            SET    balance    =    (balance    +    %(balance)s)\n                                                    WHERE    username=%(live)s\n                                    RETURNING    balance;\n\n                                "
    MERGE_EMAIL_ADDRESSES = u'\n\n                                                WITH    emails_to_keep    AS    (\n                                                                                    SELECT    DISTINCT    ON    (address)    id\n                                                                                            FROM    emails\n                                                                                        WHERE    participant_id    IN    (%(dead)s,    %(live)s)\n                                                                            ORDER    BY    address,    verification_end,    verification_start    DESC\n                                                                    )\n                                                DELETE    FROM    emails\n                                                    WHERE    participant_id    IN    (%(dead)s,    %(live)s)\n                                                            AND    id    NOT    IN    (SELECT    id    FROM    emails_to_keep);\n\n                                                UPDATE    emails\n                                                            SET    participant_id    =    %(live)s\n                                                    WHERE    participant_id    =    %(dead)s;\n\n                                '
    new_balance = None
    with self.db.get_cursor() as cursor:
        elsewhere = cursor.one(u'\n\n                                                                SELECT    elsewhere.*::elsewhere_with_participant\n                                                                        FROM    elsewhere\n                                                                        JOIN    participants    ON    participant=participants.username\n                                                                    WHERE    elsewhere.platform=%s    AND    elsewhere.user_id=%s\n\n                                                ', (platform, user_id), default=NotSane)
        other = elsewhere.participant
        if (self.username == other.username):
            return
        if other.list_identity_metadata():
            raise WontTakeOverWithIdentities()
        other_is_a_real_participant = other.is_claimed
        nelsewhere = len(other.get_elsewhere_logins(cursor))
        this_is_others_last_login_account = (nelsewhere <= 1)
        nparticipants = cursor.one(u'SELECT    count(*)    FROM    elsewhere    WHERE    participant=%s    AND    platform=%s', (self.username, platform))
        assert (nparticipants in (0, 1))
        we_already_have_that_kind_of_account = (nparticipants == 1)
        if (elsewhere.is_team and we_already_have_that_kind_of_account):
            if (len(self.get_accounts_elsewhere()) == 1):
                raise TeamCantBeOnlyAuth
        need_confirmation = NeedConfirmation(other_is_a_real_participant, this_is_others_last_login_account, we_already_have_that_kind_of_account)
        if (need_confirmation and (not have_confirmation)):
            raise need_confirmation
        if we_already_have_that_kind_of_account:
            new_stub_username = safely_reserve_a_username(cursor)
            cursor.run(u'UPDATE    elsewhere    SET    participant=%s    WHERE    platform=%s    AND    participant=%s', (new_stub_username, platform, self.username))
        cursor.run(u'UPDATE    elsewhere    SET    participant=%s    WHERE    platform=%s    AND    user_id=%s', (self.username, platform, user_id))
        if this_is_others_last_login_account:
            other.clear_takes(cursor)
            (x, y) = (self.username, other.username)
            cursor.run(CREATE_TEMP_TABLE_FOR_UNIQUE_TIPS)
            cursor.run(CONSOLIDATE_TIPS_RECEIVING, dict(live=x, dead=y))
            cursor.run(CONSOLIDATE_TIPS_GIVING, dict(live=x, dead=y))
            cursor.run(ZERO_OUT_OLD_TIPS_RECEIVING, (other.username,))
            cursor.run(ZERO_OUT_OLD_TIPS_GIVING, (other.username,))
            other_balance = other.balance
            args = dict(live=x, dead=y, balance=other_balance)
            archive_balance = cursor.one(TRANSFER_BALANCE_1, args)
            other.set_attributes(balance=archive_balance)
            new_balance = cursor.one(TRANSFER_BALANCE_2, args)
            cursor.run(MERGE_EMAIL_ADDRESSES, dict(live=self.id, dead=other.id))
            route_ids = cursor.all(u'UPDATE    exchange_routes    SET    participant=%s    WHERE    participant=%s    RETURNING    id', (self.id, other.id))
            cursor.run(u'UPDATE    teams    SET    owner=%s    WHERE    owner=%s', (self.username, other.username))
            cursor.run(u'DELETE    FROM    elsewhere    WHERE    participant=%s', (y,))
            archive_username = other.archive(cursor)
            cursor.run(u'INSERT    INTO    absorptions    (absorbed_was,    absorbed_by,    archived_as)    VALUES    (%s,    %s,    %s)', (other.username, self.username, archive_username))
            self.app.add_event(cursor, u'participant', dict(action=u'take-over', id=self.id, values=dict(other_id=other.id, exchange_routes=route_ids)))
    if (new_balance is not None):
        self.set_attributes(balance=new_balance)
    self.update_avatar()
    self.update_taking()
    self.update_giving()
