Orderer/model.py

import random
import re
import sqlite3 as db
import sys
import uuid
from math import floor

from passlib.handlers.sha2_crypt import sha256_crypt

import db_setup
import trading_bot
from debug import debug
from game import CURRENCY_NAME
from util import random_chars, salt

# connection: db.Connection = None
# cursor: db.Cursor = None
connection = None  # no type annotations in python 3.5
cursor = None  # no type annotations in python 3.5
db_name = None


def query_save_name():
    global db_name
    if debug:
        db_name = 'test.db'
        return
    while True:
        save_name = input('Name of the savegame: ')
        if re.match(r"[A-Za-z0-9.-]{0,50}", save_name):
            db_name = save_name + '.db'
            return
        else:
            print('Must match "[A-Za-z0-9.-]{0,50}"')


def connect(reconnect=False):
    global connection
    global cursor
    global db_name
    if reconnect:
        connection.commit()
        connection.close()
        cursor = None
        connection = None
        db_name = None

    if connection is None or cursor is None:
        query_save_name()

        try:
            connection = db.connect(db_name)
            # connection.text_factory = lambda x: unicode(x, 'utf-8', 'ignore')

            cursor = connection.cursor()

        except db.Error as e:
            print("Database error %s:" % e.args[0])
            sys.exit(1)

        # finally:
        #     if con is not None:
        #         con.close()


def setup():
    connect()

    db_setup.setup(cursor)

    connection.commit()


def used_key_count():
    connect()

    cursor.execute('''
        SELECT COUNT(*) -- rarely executed, no index needed, O(n) query
        FROM keys
        WHERE used_by_user_id IS NOT NULL
        ''')

    return cursor.fetchone()[0]


def login(username, password):
    connect()

    # do not allow login as bank or with empty password
    if username == 'bank' and not debug:
        return None
    if password == '' and not debug:
        return None

    cursor.execute('''
                SELECT rowid, password
                FROM users
                WHERE username = ?
                ''', (username,))
    data = cursor.fetchone()
    if not data:
        return None
    hashed_password = data[1]
    user_id = data[0]
    # if a ValueError occurs here, then most likely a password that was stored as plain text
    if sha256_crypt.verify(password + salt, hashed_password):
        return new_session(user_id)
    else:
        return None


def register(username, password, game_key):
    connect()
    if username == '':
        return False
    if password == '':
        return False
    cursor.execute('''
                INSERT INTO users 
                (username, password)
                VALUES (? , ?)
                ''', (username, password))
    if game_key != '':
        if valid_key(game_key):
            activate_key(game_key, get_user_id_by_name(username))
    return True


def own(user_id, ownable_name, amount=0):
    if not isinstance(ownable_name, str):
        return AssertionError('A name must be a string.')

    cursor.execute('''
                INSERT OR IGNORE INTO ownership (user_id, ownable_id, amount)
                SELECT ?, (SELECT rowid FROM ownables WHERE name = ?), ?
                ''', (user_id, ownable_name, amount))


def send_ownable(from_user_id, to_user_id, ownable_id, amount):
    connect()

    if amount < 0:
        raise AssertionError('Can not send negative amount')

    cursor.execute('''
                UPDATE ownership
                SET amount = amount - ?
                WHERE user_id = ?
                AND ownable_id = ?
                ''', (amount, from_user_id, ownable_id,))

    own(to_user_id,ownable_name_by_id(ownable_id))

    cursor.execute('''
                UPDATE ownership
                SET amount = amount + ?
                WHERE user_id = ?
                AND ownable_id = ?
                ''', (amount, to_user_id, ownable_id,))
    return True


def valid_key(key):
    connect()

    cursor.execute('''
                SELECT key
                FROM keys
                WHERE used_by_user_id IS NULL
                AND key = ?
                ''', (key,))

    if cursor.fetchone():
        return True
    else:
        return False


def new_session(user_id):
    connect()

    session_id = str(uuid.uuid4())

    cursor.execute('''
                INSERT INTO SESSIONS 
                (user_id, session_id)
                VALUES (? , ?)
                ''', (user_id, session_id))

    return session_id


def save_key(key):
    connect()

    cursor.execute('''
                INSERT INTO keys 
                (key)
                VALUES (?)
                ''', (key,))


def drop_old_sessions():
    connect()

    cursor.execute(''' -- no need to optimize this very well
                DELETE FROM sessions
                WHERE 
                    (SELECT COUNT(*) as newer
                     FROM sessions s2
                     WHERE user_id = s2.user_id
                     AND rowid < s2.rowid) >= 10
                ''')


def user_exists(username):
    connect()

    cursor.execute('''
                SELECT rowid
                FROM users
                WHERE username = ?
                ''', (username,))

    if cursor.fetchone():
        return True
    else:
        return False


def get_user_id_by_session_id(session_id):
    connect()

    cursor.execute('''
        SELECT users.rowid
        FROM sessions, users
        WHERE sessions.session_id = ?
        AND users.rowid = sessions.user_id
        ''', (session_id,))

    ids = cursor.fetchone()
    if not ids:
        return False
    return ids[0]


def get_user_id_by_name(username):
    connect()

    cursor.execute('''
        SELECT users.rowid
        FROM users
        WHERE username = ?
        ''', (username,))

    return cursor.fetchone()[0]


def get_user_ownership(user_id):
    connect()

    cursor.execute('''
        SELECT 
            ownables.name, 
            ownership.amount, 
            COALESCE (
            CASE -- sum score for each of the users ownables
            WHEN ownership.ownable_id = ? THEN 1
            ELSE (SELECT price 
                  FROM transactions
                  WHERE ownable_id = ownership.ownable_id 
                  ORDER BY rowid DESC -- equivalent to ordering by dt
                  LIMIT 1)
            END, 0) AS price, 
            (SELECT MAX("limit") 
             FROM orders, ownership o2
             WHERE o2.rowid = orders.ownership_id
             AND o2.ownable_id = ownership.ownable_id
             AND buy
             AND NOT stop_loss) AS bid, 
            (SELECT MIN("limit") 
             FROM orders, ownership o2
             WHERE o2.rowid = orders.ownership_id
             AND o2.ownable_id = ownership.ownable_id
             AND NOT buy
             AND NOT stop_loss) AS ask
        FROM ownership, ownables
        WHERE user_id = ?
        AND (ownership.amount > 0 OR ownership.ownable_id = ?)
        AND ownership.ownable_id = ownables.rowid
        ORDER BY ownables.rowid ASC
        ''', (currency_id(), user_id, currency_id(),))

    return cursor.fetchall()


def activate_key(key, user_id):
    connect()
    cursor.execute('''
                UPDATE keys
                SET used_by_user_id = ?
                WHERE used_by_user_id IS NULL
                AND key = ?
                ''', (user_id, key,))

    send_ownable(bank_id(), user_id, currency_id(), 1000)


def bank_id():
    connect()

    cursor.execute('''
        SELECT users.rowid
        FROM users
        WHERE username = 'bank'
        ''')

    return cursor.fetchone()[0]


def valid_session_id(session_id):
    connect()

    cursor.execute('''
                SELECT rowid
                FROM sessions
                WHERE session_id = ?
                ''', (session_id,))

    if cursor.fetchone():
        return True
    else:
        return False


def get_user_orders(user_id):
    connect()

    cursor.execute('''
        SELECT 
            CASE 
                WHEN orders.buy  THEN 'Buy'
                ELSE 'Sell'
            END,
            ownables.name, 
            (orders.ordered_amount - orders.executed_amount) || '/' || orders.ordered_amount, 
            orders."limit", 
            CASE 
                WHEN orders."limit" IS NULL THEN NULL 
                WHEN orders.stop_loss THEN 'Yes'
                ELSE 'No'
            END, 
            datetime(orders.expiry_dt, 'localtime'),
            orders.rowid
        FROM orders, ownables, ownership
        WHERE ownership.user_id = ?
        AND ownership.ownable_id = ownables.rowid
        AND orders.ownership_id = ownership.rowid
        ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC
        ''', (user_id,))

    return cursor.fetchall()


def get_ownable_orders(user_id, ownable_id):
    connect()

    cursor.execute('''
        SELECT 
            CASE 
                WHEN ownership.user_id = ? THEN 'X'
                ELSE NULL
            END,
            CASE 
                WHEN orders.buy THEN 'Buy'
                ELSE 'Sell'
            END,
            ownables.name, 
            orders.ordered_amount - orders.executed_amount, 
            orders."limit", 
            datetime(orders.expiry_dt, 'localtime'),
            orders.rowid
        FROM orders, ownables, ownership
        WHERE ownership.ownable_id = ?
        AND ownership.ownable_id = ownables.rowid
        AND orders.ownership_id = ownership.rowid
        AND (orders.stop_loss IS NULL OR NOT orders.stop_loss)
        ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC
        ''', (user_id, ownable_id,))

    return cursor.fetchall()


def sell_ordered_amount(user_id, ownable_id):
    connect()

    cursor.execute('''
                SELECT COALESCE(SUM(orders.ordered_amount - orders.executed_amount),0)
                FROM orders, ownership
                WHERE ownership.rowid = orders.ownership_id
                AND ownership.user_id = ?
                AND ownership.ownable_id = ?
                AND NOT orders.buy
                ''', (user_id, ownable_id))

    return cursor.fetchone()[0]


def available_amount(user_id, ownable_id):
    connect()

    cursor.execute('''
                SELECT amount
                FROM ownership
                WHERE user_id = ?
                AND ownable_id = ?
                ''', (user_id, ownable_id))

    return cursor.fetchone()[0] - sell_ordered_amount(user_id, ownable_id)


def user_has_at_least_available(amount, user_id, ownable_id):
    connect()

    if not isinstance(amount, float) and not isinstance(amount, int):
        # comparison of float with strings does not work so well in sql
        raise AssertionError()

    cursor.execute('''
                SELECT rowid
                FROM ownership
                WHERE user_id = ?
                AND ownable_id = ?
                AND amount - ? >= ?
                ''', (user_id, ownable_id, sell_ordered_amount(user_id, ownable_id), amount))

    if cursor.fetchone():
        return True
    else:
        return False


def news():
    connect()

    cursor.execute('''
        SELECT dt, title FROM
            (SELECT *, rowid 
            FROM news
            ORDER BY rowid DESC -- equivalent to order by dt
            LIMIT 20) n
        ORDER BY rowid ASC -- equivalent to order by dt
        ''')

    return cursor.fetchall()


def ownable_name_exists(name):
    connect()

    cursor.execute('''
                SELECT rowid
                FROM ownables
                WHERE name = ?
                ''', (name,))

    if cursor.fetchone():
        return True
    else:
        return False


def new_stock(expiry, name=None):
    connect()

    while name is None:
        name = random_chars(6)
        if ownable_name_exists(name):
            name = None

    cursor.execute('''
        INSERT INTO ownables(name)
        VALUES (?)
        ''', (name,))

    new_news('A new stock can now be bought: ' + name)
    if random.getrandbits(1):
        new_news('Experts expect the price of ' + name + ' to fall')
    else:
        new_news('Experts expect the price of ' + name + ' to rise')

    amount = random.randrange(100, 10000)
    price = random.randrange(10000, 20000) / amount
    ownable_id = ownable_id_by_name(name)
    own(bank_id(), name, amount)
    bank_order(False,
               ownable_id,
               price,
               amount,
               expiry)
    return name


def ownable_id_by_name(ownable_name):
    connect()

    cursor.execute('''
        SELECT rowid
        FROM ownables
        WHERE name = ?
        ''', (ownable_name,))

    return cursor.fetchone()[0]


def get_ownership_id(ownable_id, user_id):
    connect()

    cursor.execute('''
        SELECT rowid
        FROM ownership
        WHERE ownable_id = ?
        AND user_id = ?
        ''', (ownable_id, user_id,))

    return cursor.fetchone()[0]


def currency_id():
    connect()

    cursor.execute('''
        SELECT rowid
        FROM ownables
        WHERE name = ?
        ''', (CURRENCY_NAME,))

    return cursor.fetchone()[0]


def user_money(user_id):
    connect()

    cursor.execute('''
        SELECT amount
        FROM ownership
        WHERE user_id = ?
        AND ownable_id = ?
        ''', (user_id, currency_id()))

    return cursor.fetchone()[0]


def delete_order(order_id, new_order_status):
    connect()

    cursor.execute('''
        INSERT INTO order_history
        (ownership_id, buy, "limit", ordered_amount, executed_amount, expiry_dt, status, order_id)
        SELECT 
            ownership_id, 
            buy, 
            "limit", 
            ordered_amount, 
            executed_amount, 
            expiry_dt, 
            ?, 
            rowid
        FROM orders
        WHERE rowid = ?
        ''', (new_order_status, order_id,))

    cursor.execute('''
        DELETE FROM orders
        WHERE rowid = ?
        ''', (order_id,))


def current_value(ownable_id):
    connect()

    if ownable_id == currency_id():
        return 1

    cursor.execute('''SELECT price 
                      FROM transactions
                      WHERE ownable_id = ?
                      ORDER BY rowid DESC -- equivalent to order by dt 
                      LIMIT 1
        ''', (ownable_id,))
    return cursor.fetchone()[0]


def execute_orders(ownable_id):
    connect()
    orders_traded = False
    while True:
        # find order to execute
        cursor.execute('''
            -- two best orders
            SELECT * FROM (
                SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
                FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
                WHERE buy_order.buy AND NOT sell_order.buy
                AND buyer.rowid = buy_order.ownership_id
                AND seller.rowid = sell_order.ownership_id
                AND buyer.ownable_id = ?
                AND seller.ownable_id = ?
                AND buy_order."limit" IS NULL
                AND sell_order."limit" IS NULL
                ORDER BY buy_order.rowid ASC,
                         sell_order.rowid ASC
                LIMIT 1)
            UNION ALL -- best buy orders
            SELECT * FROM (
                SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
                FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
                WHERE buy_order.buy AND NOT sell_order.buy
                AND buyer.rowid = buy_order.ownership_id
                AND seller.rowid = sell_order.ownership_id
                AND buyer.ownable_id = ?
                AND seller.ownable_id = ?
                AND buy_order."limit" IS NULL
                AND sell_order."limit" IS NOT NULL
                AND NOT sell_order.stop_loss
                ORDER BY sell_order."limit" ASC,
                         buy_order.rowid ASC,
                         sell_order.rowid ASC
                LIMIT 1)
            UNION ALL -- best sell orders
            SELECT * FROM (
                SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
                FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
                WHERE buy_order.buy AND NOT sell_order.buy
                AND buyer.rowid = buy_order.ownership_id
                AND seller.rowid = sell_order.ownership_id
                AND buyer.ownable_id = ?
                AND seller.ownable_id = ?
                AND buy_order."limit" IS NOT NULL
                AND NOT buy_order.stop_loss
                AND sell_order."limit" IS NULL
                ORDER BY buy_order."limit" DESC,
                         buy_order.rowid ASC,
                         sell_order.rowid ASC
                LIMIT 1)
            UNION ALL -- both limit orders
            SELECT * FROM (
                SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
                FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
                WHERE buy_order.buy AND NOT sell_order.buy
                AND buyer.rowid = buy_order.ownership_id
                AND seller.rowid = sell_order.ownership_id
                AND buyer.ownable_id = ?
                AND seller.ownable_id = ?
                AND buy_order."limit" IS NOT NULL
                AND sell_order."limit" IS NOT NULL
                AND sell_order."limit" <= buy_order."limit"
                AND NOT sell_order.stop_loss
                AND NOT buy_order.stop_loss
                ORDER BY buy_order."limit" DESC,
                         sell_order."limit" ASC,
                         buy_order.rowid ASC,
                         sell_order.rowid ASC
                LIMIT 1)
            LIMIT 1
            ''', tuple(ownable_id for _ in range(8)))

        matching_orders = cursor.fetchone()
        # return type: (ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt,
        #               ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt,
        #               user_id,user_id,rowid,rowid)

        if not matching_orders:
            if not orders_traded:
                break
            # check if the trading bot has any new offers to make
            new_order_was_placed = trading_bot.notify_order_traded(ownable_id)
            if new_order_was_placed:
                orders_traded = False
                continue
            else:
                break

        buy_ownership_id, _, buy_limit, _, buy_order_amount, buy_executed_amount, buy_expiry_dt, \
            sell_ownership_id, _, sell_limit, _, sell_order_amount, sell_executed_amount, sell_expiry_dt, \
            buyer_id, seller_id, buy_order_id, sell_order_id \
            = matching_orders

        if buy_limit is None and sell_limit is None:
            price = current_value(ownable_id)
        elif buy_limit is None:
            price = sell_limit
        elif sell_limit is None:
            price = buy_limit
        else:  # both not NULL
            # the price of the older order is used, just like in the real exchange
            if buy_order_id < sell_order_id:
                price = buy_limit
            else:
                price = sell_limit

        buyer_money = user_money(buyer_id)

        def _my_division(x, y):
            try:
                return floor(x / y)
            except ZeroDivisionError:
                return float('Inf')

        amount = min(buy_order_amount - buy_executed_amount,
                     sell_order_amount - sell_executed_amount,
                     _my_division(buyer_money, price))

        if amount == 0:  # probable because buyer has not enough money
            delete_order(buy_order_id, 'Unable to pay')
            continue

        buy_order_finished = (buy_order_amount - buy_executed_amount - amount <= 0) or (
                buyer_money - amount * price < price)
        sell_order_finished = (sell_order_amount - sell_executed_amount - amount <= 0)

        if price < 0 or amount <= 0:  # price of 0 is possible though unlikely
            return AssertionError()

        # actually execute the order, but the bank does not send or receive anything
        send_ownable(buyer_id, seller_id, currency_id(), price * amount)
        send_ownable(seller_id, buyer_id, ownable_id, amount)

        # update order execution state
        cursor.execute('''
            UPDATE orders 
            SET executed_amount = executed_amount + ?
            WHERE rowid = ?
            OR rowid = ?
            ''', (amount, buy_order_id, sell_order_id))

        if buy_order_finished:
            delete_order(buy_order_id, 'Executed')
            orders_traded = True
        if sell_order_finished:
            delete_order(sell_order_id, 'Executed')
            orders_traded = True

        if seller_id != buyer_id:  # prevent showing self-transactions
            cursor.execute('''
                INSERT INTO transactions
                (price, ownable_id, amount, buyer_id, seller_id)
                VALUES(?, ?, ?, ?, ?)
                ''', (price, ownable_id, amount, buyer_id, seller_id))

        # trigger stop-loss orders
        if buyer_id != seller_id:
            cursor.execute('''
                UPDATE orders
                SET stop_loss = NULL,
                    "limit" = NULL
                WHERE stop_loss IS NOT NULL
                AND stop_loss
                AND ? IN (SELECT ownable_id FROM ownership WHERE rowid = ownership_id)
                AND ((buy AND "limit" < ?) OR (NOT buy AND "limit" > ?))
                ''', (ownable_id, price, price,))


def ownable_id_by_ownership_id(ownership_id):
    connect()

    cursor.execute('''
        SELECT ownable_id
        FROM ownership
        WHERE rowid = ?
        ''', (ownership_id,))

    return cursor.fetchone()[0]


def ownable_name_by_id(ownable_id):
    connect()

    cursor.execute('''
        SELECT name
        FROM ownables
        WHERE rowid = ?
        ''', (ownable_id,))

    return cursor.fetchone()[0]


def bank_order(buy, ownable_id, limit, amount, expiry):
    if not limit:
        raise AssertionError('The bank does not give away anything.')
    place_order(buy,
                get_ownership_id(ownable_id, bank_id()),
                limit,
                False,
                amount,
                expiry)
    ownable_name = ownable_name_by_id(ownable_id)
    new_news('External investors are selling ' + ownable_name + ' atm')


def current_db_time():  # might differ from datetime.datetime.now() for time zone reasons
    connect()

    cursor.execute('''
        SELECT datetime('now')
        ''')

    return cursor.fetchone()[0]


def place_order(buy, ownership_id, limit, stop_loss, amount, expiry):
    connect()
    cursor.execute('''
                INSERT INTO orders 
                (buy, ownership_id, "limit", stop_loss, ordered_amount, expiry_dt)
                VALUES (?, ?, ?, ?, ?, ?)
                ''', (buy, ownership_id, limit, stop_loss, amount, expiry))

    execute_orders(ownable_id_by_ownership_id(ownership_id))
    return True


def trades_on(ownable_id, limit):
    connect()

    cursor.execute('''
        SELECT datetime(dt,'localtime'), amount, price
        FROM transactions
        WHERE ownable_id = ?
        ORDER BY rowid DESC -- equivalent to order by dt
        LIMIT ?
        ''', (ownable_id, limit,))

    return cursor.fetchall()


def trades(user_id, limit):
    connect()
    cursor.execute('''
        SELECT 
            (CASE WHEN seller_id = ? THEN 'Sell' ELSE 'Buy' END), 
            (SELECT name FROM ownables WHERE rowid = transactions.ownable_id), 
            amount, 
            price,
            datetime(dt,'localtime')
        FROM transactions
        WHERE seller_id = ? OR buyer_id = ?
        ORDER BY rowid DESC -- equivalent to order by dt
        LIMIT ?
        ''', (user_id, user_id, user_id, limit,))

    return cursor.fetchall()


def drop_expired_orders():
    connect()

    cursor.execute('''
        SELECT rowid, ownership_id, * FROM orders 
        WHERE expiry_dt < DATETIME('now')
        ''')

    data = cursor.fetchall()
    for order in data:
        order_id = order[0]
        delete_order(order_id, 'Expired')

    return data


def generate_keys(count=1):
    # source https://stackoverflow.com/questions/17049308/python-3-3-serial-key-generator-list-problems

    for i in range(count):
        key = '-'.join(random_chars(5) for _ in range(5))
        save_key(key)
        print(key)


def user_has_order_with_id(session_id, order_id):
    connect()

    cursor.execute('''
                SELECT orders.rowid
                FROM orders, ownership, sessions
                WHERE orders.rowid = ?
                AND sessions.session_id = ?
                AND sessions.user_id = ownership.user_id
                AND ownership.rowid = orders.ownership_id
                ''', (order_id, session_id,))

    if cursor.fetchone():
        return True
    else:
        return False


def leaderboard():
    connect()

    cursor.execute('''
        SELECT * 
        FROM ( -- one score for each user
            SELECT 
                username, 
                SUM(CASE -- sum score for each of the users ownables
                    WHEN ownership.ownable_id = ? THEN ownership.amount
                    ELSE ownership.amount * (SELECT price 
                                             FROM transactions
                                             WHERE ownable_id = ownership.ownable_id 
                                             ORDER BY rowid DESC -- equivalent to ordering by dt
                                             LIMIT 1)
                    END
                ) score
            FROM users, ownership
            WHERE ownership.user_id = users.rowid
            AND users.username != 'bank'
            GROUP BY users.rowid
        ) AS scores
        ORDER BY score DESC
        LIMIT 50
        ''', (currency_id(),))

    return cursor.fetchall()


def user_wealth(user_id):
    connect()

    cursor.execute('''
        SELECT SUM(
            CASE -- sum score for each of the users ownables
            WHEN ownership.ownable_id = ? THEN ownership.amount
            ELSE ownership.amount * (SELECT price 
                                     FROM transactions
                                     WHERE ownable_id = ownership.ownable_id 
                                     ORDER BY rowid DESC -- equivalent to ordering by dt
                                     LIMIT 1)
            END
        ) score
        FROM ownership
        WHERE ownership.user_id = ?
        ''', (currency_id(), user_id,))

    return cursor.fetchone()[0]


def change_password(session_id, password):
    connect()

    cursor.execute('''
                UPDATE users
                SET password = ?
                WHERE rowid = (SELECT user_id FROM sessions WHERE sessions.session_id = ?)
                ''', (password, session_id,))


def sign_out_user(session_id):
    connect()

    cursor.execute('''
        DELETE FROM sessions
        WHERE user_id = (SELECT user_id FROM sessions s2 WHERE s2.session_id = ?)
        ''', (session_id,))


def delete_user(user_id):
    connect()

    cursor.execute('''
        DELETE FROM sessions
        WHERE user_id = ?
        ''', (user_id,))

    cursor.execute('''
        DELETE FROM orders
        WHERE ownership_id IN (
            SELECT rowid FROM ownership WHERE user_id = ?)
        ''', (user_id,))

    cursor.execute('''
        DELETE FROM ownership
        WHERE user_id = ?
        ''', (user_id,))

    cursor.execute('''
        DELETE FROM keys
        WHERE used_by_user_id = ?
        ''', (user_id,))

    cursor.execute('''
        INSERT INTO news(title)
        VALUES ((SELECT username FROM users WHERE rowid = ?) || ' retired.')
        ''', (user_id,))

    cursor.execute('''
        DELETE FROM users
        WHERE rowid = ?
        ''', (user_id,))


def delete_ownable(ownable_id):
    connect()

    cursor.execute('''
        DELETE FROM transactions
        WHERE ownable_id = ?
        ''', (ownable_id,))

    cursor.execute('''
        DELETE FROM orders
        WHERE ownership_id IN (
            SELECT rowid FROM ownership WHERE ownable_id = ?)
        ''', (ownable_id,))

    cursor.execute('''
        DELETE FROM orders_history
        WHERE ownership_id IN (
            SELECT rowid FROM ownership WHERE ownable_id = ?)
        ''', (ownable_id,))

    # only delete empty ownerships
    cursor.execute('''
        DELETE FROM ownership
        WHERE ownable_id = ?
        AND amount = 0
        ''', (ownable_id,))

    cursor.execute('''
        INSERT INTO news(title)
        VALUES ((SELECT name FROM ownables WHERE rowid = ?) || ' can not be traded any more.')
        ''', (ownable_id,))

    cursor.execute('''
        DELETE FROM ownables
        WHERE rowid = ?
        ''', (ownable_id,))


def hash_all_users_passwords():
    connect()

    cursor.execute('''
        SELECT rowid, password 
        FROM users
        ''')

    users = cursor.fetchall()

    for user in users:
        user_id = user[0]
        pw = user[1]
        valid_hash = True
        try:
            sha256_crypt.verify('password' + salt, pw)
        except ValueError:
            valid_hash = False
        if valid_hash:
            raise AssertionError('There is already a hashed password in the database! Be careful what you are doing!')
        pw = sha256_crypt.encrypt(pw + salt)
        cursor.execute('''
            UPDATE users
            SET password = ?
            WHERE rowid = ?
            ''', (pw, user_id,))


def new_news(message):
    connect()
    cursor.execute('''
        INSERT INTO news(title)
        VALUES (?)
        ''', (message,))


def abs_spread(ownable_id):
    connect()

    cursor.execute('''
        SELECT 
            (SELECT MAX("limit") 
             FROM orders, ownership
             WHERE ownership.rowid = orders.ownership_id
             AND ownership.ownable_id = ?
             AND buy
             AND NOT stop_loss) AS bid, 
            (SELECT MIN("limit") 
             FROM orders, ownership
             WHERE ownership.rowid = orders.ownership_id
             AND ownership.ownable_id = ?
             AND NOT buy
             AND NOT stop_loss) AS ask
        ''', (ownable_id, ownable_id,))

    return cursor.fetchone()


def ownables():
    connect()

    cursor.execute('''
        SELECT name, course,
            (SELECT SUM(amount)
            FROM ownership
            WHERE ownership.ownable_id = ownables_with_course.rowid) market_size
        FROM (SELECT
                name, ownables.rowid,
                CASE WHEN ownables.rowid = ? 
                THEN 1
                ELSE (SELECT price
                      FROM transactions
                      WHERE ownable_id = ownables.rowid
                      ORDER BY rowid DESC -- equivalent to ordering by dt
                      LIMIT 1) END course
             FROM ownables) ownables_with_course
        ''', (currency_id(),))

    data = cursor.fetchall()

    for idx in range(len(data)):
        # compute market cap
        row = data[idx]
        if row[1] is None:
            market_cap = None
        elif row[2] is None:
            market_cap = None
        else:
            market_cap = row[1] * row[2]
        data[idx] = (row[0], row[1], market_cap)

    return data


def reset_bank():
    connect()
    cursor.execute('''
        DELETE FROM ownership 
        WHERE user_id = ?
        ''', (bank_id(),))


def cleanup():
    if connection is not None:
        connection.commit()
        connection.close()


def ownable_ids():
    connect()

    cursor.execute('''
        SELECT rowid FROM ownables
        ''')

    return [ownable_id[0] for ownable_id in cursor.fetchall()]


def get_old_orders(user_id, include_executed, include_canceled, limit):
    connect()
    cursor.execute('''
        SELECT 
            (CASE WHEN order_history.buy THEN 'Buy' ELSE 'Sell' END),
            ownables.name,
            (order_history.ordered_amount - order_history.executed_amount) || '/' || order_history.ordered_amount,
            order_history."limit",
            order_history.expiry_dt,
            order_history.order_id,
            order_history.status
        FROM order_history, ownership, ownables
        WHERE ownership.user_id = ?
        AND ownership.rowid = order_history.ownership_id
        AND ownables.rowid = ownership.ownable_id
        AND (
             (order_history.status = 'Executed' AND ?)
             OR 
             ((order_history.status = 'Expired' OR order_history.status = 'Canceled') AND ?)
            )
        ORDER BY order_history.rowid DESC -- equivalent to ordering by creation time
        LIMIT ?
        ''', (user_id, include_executed, include_canceled, limit))

    return cursor.fetchall()