import locale
import math
from collections import defaultdict
from decimal import Decimal

from aspen import Response
from gittip import db, CARDINALS

^L
_suspicious = db.fetchall("""

    SELECT username
         , balance
         , ctime
      FROM participants
     WHERE is_suspicious
       AND ctime < '2012-11-05'::timestamptz
  ORDER BY ctime

""")
if _suspicious is None:
    _suspicious = []


def suspicious():
    for person in _suspicious:
        person['gives_to'] = db.fetchall("""

            SELECT DISTINCT tippee AS username
                 , is_suspicious
                 , tips.ctime
              FROM tips
              JOIN participants p
                ON tippee = p.username
             WHERE tipper=%s
               AND tips.ctime < '2012-11-05'::timestamptz
          ORDER BY tips.ctime


        """, (person['username'],))
        person['receives_from'] = db.fetchall("""

            SELECT DISTINCT tipper AS username
                 , is_suspicious
                 , tips.ctime
              FROM tips
              JOIN participants p
                ON tipper = p.username
             WHERE tippee=%s
               AND tips.ctime < '2012-11-05'::timestamptz
          ORDER BY tips.ctime

        """, (person['username'],))

        person['transfers'] = list(db.fetchall("""

            SELECT tippee
                 , tipper
                 , amount
                 , timestamp
              FROM transfers
             WHERE tipper=%s OR tippee=%s
               AND timestamp < '2012-11-05'::timestamptz
          ORDER BY timestamp


        """, (person['username'], person['username'])))

        person['exchanges'] = list(db.fetchall("""

            SELECT amount, fee, timestamp
              FROM exchanges
             WHERE participant=%s
               AND timestamp < '2012-11-05'::timestamptz
          ORDER BY timestamp


        """, (person['username'],)))

        yield person
suspicious = list(suspicious())
names = set([person['username'] for person in suspicious])

total_charged = 0
total_withdrawn = 0
gittip_fees = 0
balanced_fees = 0
total_escrowed = 0
total_legit = 0
total_fraudulent = 0

bystanders = defaultdict(lambda: [Decimal('0.00')])

earliest = None
latest = None

for person in suspicious:
    total_escrowed += person['balance']
    for transfer in person['transfers']:
        if transfer['tippee'] not in names:
            total_legit += transfer['amount']
            bystanders[transfer['tippee']][0] += transfer['amount']
    for exchange in person['exchanges']:
        total_fraudulent += 1
        amount, fee = exchange['amount'], exchange['fee']
        if amount > 0:
            if earliest is None or earliest > exchange['timestamp']:
                earliest = exchange['timestamp']
            if latest is None or latest < exchange['timestamp']:
                latest = exchange['timestamp']
            amount_charged = exchange['amount'] + fee
            total_charged += amount_charged
            balanced_fee = (amount_charged * Decimal('0.029')) + Decimal('0.30')
            balanced_fee = Decimal(balanced_fee)
        else:
            total_withdrawn += abs(exchange['amount'])
            balanced_fee = Decimal('0.25')
        balanced_fees += balanced_fee
        gittip_fees += fee - balanced_fee
assert total_legit == total_charged - total_withdrawn - gittip_fees - balanced_fees - total_escrowed

nweeks = CARDINALS[((latest.date() - earliest.date()).days / 7) + 1]

overall = db.fetchone("""

    SELECT sum(amount + fee), count(amount)
      FROM exchanges
     WHERE amount > 0
       AND timestamp > %s
       AND timestamp < '2012-11-01'::timestamptz

""", (earliest.date(),))
overall_volume = overall['sum']
overall_transactions = overall['count']
percentage_stolen = (total_charged / overall_volume) * 100
percentage_fraudulent = (total_fraudulent / float(overall_transactions)) * 100


pad = lambda d, n: locale.format("%.2f", d, grouping=True).rjust(n).replace(' ', '&nbsp;')

w = 9
total_charged = pad(total_charged, w)
total_withdrawn = pad(total_withdrawn, w)
gittip_fees = pad(gittip_fees, w)
balanced_fees = pad(balanced_fees, w)
total_escrowed = pad(total_escrowed, w)
total_legit = pad(total_legit, w)
overall_volume = pad(overall_volume, w)
percentage_stolen = pad(percentage_stolen, w)

overall_transactions = pad(overall_transactions, w)[:-3] + ('&nbsp;' * 3)
total_fraudulent = pad(total_fraudulent, w)[:-3] + ('&nbsp;' * 3)
percentage_fraudulent = pad(percentage_fraudulent, w)


bystander_balances = db.fetchall("""

    SELECT username, balance FROM participants WHERE username = ANY(%s)

""", (list(bystanders),))
if bystander_balances is None:
    bystander_balances = []
for row in bystander_balances:
    bystanders[row['username']].append(row['balance'])

bystanders = sorted(bystanders.items(), key=lambda i: i[1], reverse=True)

fmtdate = lambda d: str(d)[:10]

title = "The Delpan Incident"

^L
{% extends templates/base.html %}
{% block box %}
<div class="as-content left">

    <p>Gittip's first <a href="./">fraud incident</a> surfaced when an
    unknown user named delpan appeared among the top receivers on the site.
    This page provides data and collects information related to the
    incident.</p>


    <h2>Status</h2>

    <p>This incident is <b>closed</b>.</p>


    <h2>Related</h2>

    <ul>

        <li>GitHub ticket: &ldquo;<a
            href="https://github.com/gittip/www.gittip.com/issues/329">make sure
            "delpan" is not stealing money :-)</a>&rdquo;</li>

        <li>Blog post: &ldquo;<a
            href="http://blog.gittip.com/post/35057426257/stolen-money-on-gittip-part-1">Stolen
            Money on Gittip, Part 1</a>&rdquo;</li>

        <li>Hacker News discussion: &ldquo;<a
        href="http://news.ycombinator.com/item?id=4743954">Stolen Money on
        Gittip, Part 1</a>&rdquo;</li>

        <li>Vice Motherboard: &ldquo;<a
        href="http://motherboard.vice.com/2012/11/6/thieves-launder-money-by-crowdfunding-themselves--2">Thieves
        Launder Money by Crowdfunding Themselves</a>&rdquo;</li>

        <li>Secure Computing Magazine: &ldquo;<a
        href="http://www.scmagazine.com.au/News/322118,fraudsters-launder-cash-though-grants-startup.aspx?utm_source=feed&utm_medium=rss&utm_campaign=SC+Magazine+All+Articles+feed">Fraudsters
        launder cash though grants startup</a>&rdquo;</li>

        <li>Blog post: &ldquo;<a
        href="http://blog.gittip.com/post/35314128322/the-delpan-incident">The
        Delpan Incident</a>&rdquo; (Part 2 of the above)</li>

        <li>Hacker News discussion: &ldquo;<a
        href="http://news.ycombinator.com/item?id=4763317">The Delpan
        Incident</a>&rdquo;</li>

    </ul>

</div>
{% end %}
{% block page %}
<style>
    TH {
        font-weight: normal;
        text-align: center;
        padding: 0;
    }
    TD {
        text-align: left;
        vertical-align: top;
    }
    .summary TH {
        text-align: left;
        padding-right: 12pt;
    }
    .summary B {
        display: block;
        padding-bottom: 12pt;
    }
    .details TH { vertical-align: bottom; }
    .details TD { vertical-align: top; }
    .details TD, .details TH {
        text-align: left;
        border-bottom: 1px solid #B2A196;
        white-space: nowrap;
        padding: 0pt;
        font-size: 10pt;
        line-height: 13pt;
    }
    .balance {
    }
    .amount {
        text-align: center;
        padding-right: 6pt;
        font-family: Lucida Mono, monospace;
    }
    .details .tips,
    .details .transfers,
    .details .exchanges {
        padding-left: 12pt;
    }
    .date {
        font-family: Lucida Mono, monospace;
        font-size: smaller;
        color: #999;
        float: right;
    }
    .no-float {
        float: none;
    }
    .suspicious {
        font-weight: bold;
        color: red;
    }
    .charge {
        color: red;
    }
</style>
<div class="col0">

<h2>Summary</h2>

<p>The fraudulent credit card transactions associated with this incident
occured over a period of <b>{{ nweeks }} weeks</b>, from
{{ earliest.strftime("%B %d") }} to {{ latest.strftime("%B %d, %Y") }}.</p>

<table class="summary">
    <tr><th>Withdrawn to a Bank Account</th>
        <td class="amount">${{ total_withdrawn }}&nbsp;</td></tr>
    <tr><th>Given to Innocent Bystanders</th>
        <td class="amount">${{ total_legit }}&nbsp;</td></tr>
    <tr><th>Still Escrowed in Gittip</th>
        <td class="amount">${{ total_escrowed }}&nbsp;</td></tr>
    <tr><th>Fees - Balanced</th>
        <td class="amount">${{ balanced_fees }}&nbsp;</td></tr>
    <tr><th>Fees - Gittip</th>
        <td class="amount">${{ gittip_fees }}&nbsp;</td></tr>
    <tr><th><b>Total Stolen</b></th>
        <td class="amount"><b>${{ total_charged }}&nbsp;</b></td></tr>
    <tr><th>Overall Volume <span class="help">during the incident</span></th>
        <td class="amount">${{ overall_volume }}&nbsp;</td></tr>
    <tr><th><b>Percentage Stolen</b></th>
        <td class="amount"><b>&nbsp;{{ percentage_stolen }}%</b></td></tr>
    <tr><th>Total Credit Card Transactions</th>
        <td class="amount">&nbsp;{{ overall_transactions }}&nbsp;</td></tr>
    <tr><th>Fraudulent Transactions</th>
        <td class="amount">&nbsp;{{ total_fraudulent }}&nbsp;</td></tr>
    <tr><th><b>Percentage Fraudulent</b></th>
        <td class="amount"><b>&nbsp;{{ percentage_fraudulent }}%</b></td></tr>
</table>


<h2>Innocent Bystanders (N = {{ len(bystanders) }})</h2>

<p>These people inadvertently received stolen money as part of this
incident.</p>

<table class="summary">
    <tr><th></th><th>Received</th>{% if user.ADMIN %}<th>Balance</th>{% end %}</tr>
    {% for name, (amount, balance) in bystanders %}
    <tr>
        <th><a href="/{{ name }}/">{{ name }}</a></th>
        <td class="amount">${{ pad(amount, 9) }}</td>
        {% if user.ADMIN %}<td class="amount">${{ pad(balance, 7) }}</td>{% end %}
    </tr>
    {% end %}
    <tr>
        <td><b>Total</b></td>
        <td class="amount"><b>${{ total_legit }}</b></td>
        {% if user.ADMIN %}<td></td>{% end %}
    </tr>
</table>
</div>

<table class="details centered">
    <tr>
        <td colspan="5">
            <h2>Suspicious Accounts (N = {{ len(suspicious) }})</h2>
        </td>
    </tr>
    <tr>
        <th></th>
        <th class="balance">Balance</th>
        <th class="tips">Tip Graph</th>
        <th class="transfers">Transfers</th>
        <th class="exchanges">Exchanges &amp; Fees<br />
        <span class="help">Negative means bank deposit,<br />positive means credit card charge.</span></th>
    </tr>
    {% for row in suspicious %}
    <tr>
        <td><a href="/{{ row['username'] }}/">{{ row['username'] }}</a><br />
            <span class="date no-float">{{ fmtdate(row['ctime']) }}</span></td>
        <td class="balance amount">{{ pad(row['balance'], 5) }}</td>
        <td class="tips">
            {% for person in row['gives_to'] %}
                &rarr;<a href="/{{ person['username'] }}/"{% if person['is_suspicious'] %} class="suspicious"{% end %}>{{ person['username'] }}</a><br />
            {% end %}
            {% for person in row['receives_from'] %}
                &larr;<a href="/{{ person['username'] }}/"{% if person['is_suspicious'] %} class="suspicious"{% end %}>{{ person['username'] }}</a><br />
            {% end %}
        </td>
        <td class="transfers">
            {% for transfer in row['transfers'] %}
            {% if row['username'] == transfer['tipper'] %}
            <span class="amount">{{ pad(transfer['amount'], 5)}}</span>&rarr;
            <a href="/{{ transfer['tippee'] }}/"{% if transfer['tippee'] in names %}
                class="suspicious"{% end %}>{{ transfer['tippee'] }}</a>
            {% else %}
            <span class="amount">{{ pad(transfer['amount'], 5) }}</span>&larr;
            <a href="/{{ transfer['tipper'] }}/"{% if transfer['tipper'] in names %}
                class="suspicious"{% end %}>{{ transfer['tipper'] }}</a>
            {% end %}
            <span class="date">
                {{ str(transfer['timestamp'])[:10] }}
            </span>
            <br />
            {% end %}
        </td>
        <td class="exchanges">
            {% for exchange in row['exchanges'] %}
            <span class="amount{% if exchange['amount'] < 0 %} charge{% end %}">
                {{ pad(exchange['amount'], 6) }}
                {{ pad(exchange['fee'], 4) }}
            </span>
            <span class="date">
                {{ str(exchange['timestamp'])[:10] }}
            </span>
            <br />
            {% end %}
        </td>
    </tr>
    {% end %}
</table>
{% end %}