text: """
  
  The most important object in the Gratipay object model is Participant, and the
  second most important one is Ccommunity. There are a few others, but those are
  the most important two. Participant, in particular, is at the center of
  everything on Gratipay.
  
  """
  from contextlib import contextmanager
  
  from postgres import Postgres
  import psycopg2.extras
  
  
  @contextmanager
  def just_yield(obj):
      yield obj
  
  
  class GratipayDB(Postgres):
  
      def get_cursor(self, cursor=None, **kw):
          if cursor:
              if kw:
                  raise ValueError('cannot change options when reusing a cursor')
              return just_yield(cursor)
          return super(GratipayDB, self).get_cursor(**kw)
  
      def self_check(self):
          with self.get_cursor() as cursor:
              check_db(cursor)
  
  
  def check_db(cursor):
      """Runs all available self checks on the given cursor.
      """
      _check_balances(cursor)
      _check_no_team_balances(cursor)
      _check_tips(cursor)
      _check_orphans(cursor)
      _check_orphans_no_tips(cursor)
      _check_paydays_volumes(cursor)
  
  
  def _check_tips(cursor):
      """
      Checks that there are no rows in tips with duplicate (tipper, tippee, mtime).
  
      https://github.com/gratipay/gratipay.com/issues/1704
      """
      conflicting_tips = cursor.one("""
          SELECT count(*)
            FROM
               (
                  SELECT * FROM tips
                  EXCEPT
                  SELECT DISTINCT ON(tipper, tippee, mtime) *
                    FROM tips
                ORDER BY tipper, tippee, mtime
                ) AS foo
      """)
      assert conflicting_tips == 0
  
  
  def _check_balances(cursor):
      """
      Recalculates balances for all participants from transfers and exchanges.
  
      https://github.com/gratipay/gratipay.com/issues/1118
      """
      b = cursor.all("""
          select p.username, expected, balance as actual
            from (
              select username, sum(a) as expected
                from (
                        select participant as username, sum(amount) as a
                          from exchanges
                         where amount > 0
                           and (status is null or status = 'succeeded')
                      group by participant
  
                         union all
  
                        select participant as username, sum(amount-fee) as a
                          from exchanges
                         where amount < 0
                           and (status is null or status <> 'failed')
                      group by participant
  
                         union all
  
                        select tipper as username, sum(-amount) as a
                          from transfers
                      group by tipper
  
                         union all
  
                        select participant as username, sum(amount) as a
                          from payments
                         where direction='to-participant'
                      group by participant
  
                         union all
  
                        select participant as username, sum(-amount) as a
                          from payments
                         where direction='to-team'
                      group by participant
  
                         union all
  
                        select tippee as username, sum(amount) as a
                          from transfers
                      group by tippee
                      ) as foo
              group by username
            ) as foo2
          join participants p on p.username = foo2.username
          where expected <> p.balance
      """)
      assert len(b) == 0, "conflicting balances: {}".format(b)
  
  def _check_no_team_balances(cursor):
      if cursor.one("select exists (select * from paydays where ts_end < ts_start) as running"):
          # payday is running
          return
      teams = cursor.all("""
          SELECT t.slug, balance
            FROM (
                  SELECT team, sum(delta) as balance
                    FROM (
                          SELECT team, sum(-amount) AS delta
                            FROM payments
                           WHERE direction='to-participant'
                        GROUP BY team
  
                           UNION ALL
  
                          SELECT team, sum(amount) AS delta
                            FROM payments
                           WHERE direction='to-team'
                        GROUP BY team
                         ) AS foo
                GROUP BY team
                 ) AS foo2
            JOIN teams t ON t.slug = foo2.team
           WHERE balance <> 0
      """)
      assert len(teams) == 0, "teams with non-zero balance: {}".format(teams)
  
  
  def _check_orphans(cursor):
      """
      Finds participants that
          * does not have corresponding elsewhere account
          * have not been absorbed by other participant
  
      These are broken because new participants arise from elsewhere
      and elsewhere is detached only by take over which makes a note
      in absorptions if it removes the last elsewhere account.
  
      Especially bad case is when also claimed_time is set because
      there must have been elsewhere account attached and used to sign in.
  
      https://github.com/gratipay/gratipay.com/issues/617
      """
      orphans = cursor.all("""
          select username
             from participants
            where not exists (select * from elsewhere where elsewhere.participant=username)
              and not exists (select * from absorptions where archived_as=username)
      """)
      assert len(orphans) == 0, "missing elsewheres: {}".format(list(orphans))
  
  
  def _check_orphans_no_tips(cursor):
      """
      Finds participants
          * without elsewhere account attached
          * having non zero outstanding tip
  
      This should not happen because when we remove the last elsewhere account
      in take_over we also zero out all tips.
      """
      orphans_with_tips = cursor.all("""
          WITH valid_tips AS (SELECT * FROM current_tips WHERE amount > 0)
          SELECT username
            FROM (SELECT tipper AS username FROM valid_tips
                  UNION
                  SELECT tippee AS username FROM valid_tips) foo
           WHERE NOT EXISTS (SELECT 1 FROM elsewhere WHERE participant=username)
      """)
      assert len(orphans_with_tips) == 0, orphans_with_tips
  
  
  def _check_paydays_volumes(cursor):
      """
      Recalculate *_volume fields in paydays table using exchanges table.
      """
      if cursor.one("select exists (select * from paydays where ts_end < ts_start) as running"):
          # payday is running
          return
      charge_volume = cursor.all("""
          select * from (
              select id, ts_start, charge_volume, (
                      select coalesce(sum(amount+fee), 0)
                      from exchanges
                      where timestamp > ts_start
                      and timestamp < ts_end
                      and amount > 0
                      and recorder is null
                      and (status is null or status <> 'failed')
                  ) as ref
              from paydays
              order by id
          ) as foo
          where charge_volume != ref
      """)
      assert len(charge_volume) == 0
  
      charge_fees_volume = cursor.all("""
          select * from (
              select id, ts_start, charge_fees_volume, (
                      select coalesce(sum(fee), 0)
                      from exchanges
                      where timestamp > ts_start
                      and timestamp < ts_end
                      and amount > 0
                      and recorder is null
                      and (status is null or status <> 'failed')
                  ) as ref
              from paydays
              order by id
          ) as foo
          where charge_fees_volume != ref
      """)
      assert len(charge_fees_volume) == 0
  
      ach_volume = cursor.all("""
          select * from (
              select id, ts_start, ach_volume, (
                      select coalesce(sum(amount), 0)
                      from exchanges
                      where timestamp > ts_start
                      and timestamp < ts_end
                      and amount < 0
                      and recorder is null
                  ) as ref
              from paydays
              order by id
          ) as foo
          where ach_volume != ref
      """)
      assert len(ach_volume) == 0
  
      ach_fees_volume = cursor.all("""
          select * from (
              select id, ts_start, ach_fees_volume, (
                      select coalesce(sum(fee), 0)
                      from exchanges
                      where timestamp > ts_start
                      and timestamp < ts_end
                      and amount < 0
                      and recorder is null
                  ) as ref
              from paydays
              order by id
          ) as foo
          where ach_fees_volume != ref
      """)
      assert len(ach_fees_volume) == 0
  
  
  def add_event(c, type, payload):
      SQL = """
          INSERT INTO events (type, payload)
          VALUES (%s, %s)
      """
      c.run(SQL, (type, psycopg2.extras.Json(payload)))
  