Orderer/model.py

import json
import os
import random
import re
import sqlite3 as db
import uuid
from datetime import datetime
from logging import INFO
from math import floor
from shutil import copyfile
from typing import Optional, Dict

from passlib.handlers.sha2_crypt import sha256_crypt

import db_setup
from game import CURRENCY_NAME, logger, DB_NAME, MIN_INTEREST_INTERVAL, MRO_NAME, BANK_NAME
from util import random_chars

DBName = str
connections: Dict[DBName, db.Connection] = {}
current_connection: Optional[db.Connection] = None
current_cursor: Optional[db.Cursor] = None
current_db_name: Optional[DBName] = None
current_user_id: Optional[int] = None


def execute(sql, parameters=()):
    if not re.search(r"(?i)\s*SELECT", sql):
        logger.info(sql, 'sql_query', data=json.dumps(parameters))
    return current_cursor.execute(sql, parameters)


def executemany(sql, parameters=()):
    if not re.search(r"(?i)\s*SELECT", sql):
        logger.info(sql, 'sql_query_many', data=json.dumps(parameters))
    return current_cursor.executemany(sql, parameters)


def valid_db_name(name):
    return re.match(r"[a-z0-9.-]{0,20}", name)


def query_save_name():
    while True:
        # save_name = input('Name of the database (You can also enter a new filename here): ')
        save_name = DB_NAME
        if valid_db_name(save_name):
            return save_name
        else:
            print('Must match "[a-z0-9.-]{0,20}"')


def connect(db_name=None, create_if_not_exists=False):
    """
    connects to the database with the given name, if it exists
    if the database does not exist an exception is raised
        (unless create_if_not_exists is true, then the database is created)
    if there is already a connection to this database, that connection is used
    :return: the connection and the connections' cursor
    """
    if db_name is None:
        db_name = query_save_name()
    if not db_name.endswith('.db'):
        db_name += '.db'
    db_name = db_name.lower()
    if not os.path.isfile(db_name) and not create_if_not_exists:
        raise FileNotFoundError('There is no database with this name.')
    creating_new_db = not os.path.isfile(db_name)
    if db_name not in connections:
        try:
            db_connection = db.connect(db_name, check_same_thread=False)
            db_setup.create_functions(db_connection)
            db_setup.set_pragmas(db_connection.cursor())
            # connection.text_factory = lambda x: x.encode('latin-1')
        except db.Error as e:
            print("Database error %s:" % e.args[0])
            raise
        connections[db_name] = db_connection
    global current_connection
    global current_db_name
    global current_cursor
    current_connection = connections[db_name]
    current_cursor = connections[db_name].cursor()
    current_db_name = db_name
    if creating_new_db:
        try:
            if os.path.isfile('/test-db/' + db_name):
                print('Using test database containing fake data')
                copyfile('/test-db/' + db_name, db_name)
            else:
                logger.log('Creating database', INFO, 'database_creation')
                logger.commit()
                setup()
        except Exception:
            if current_connection is not None:
                current_connection.rollback()
            if db_name in connections:
                disconnect(db_name, rollback=True)
            os.remove(db_name)
            current_connection = None
            current_cursor = None
            current_db_name = None
            raise


def disconnect(connection_name, rollback=True):
    global connections
    if connection_name not in connections:
        raise ValueError('Invalid connection')
    if rollback:
        connections[connection_name].rollback()
    else:
        connections[connection_name].commit()
    connections[connection_name].close()
    del connections[connection_name]


def setup():
    db_setup.setup(current_cursor)


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

    return current_cursor.fetchone()[0]


def login(username, password):
    execute('''
                SELECT rowid, password, salt
                FROM users
                WHERE username = ?
                ''', (username,))
    data = current_cursor.fetchone()
    if not data:
        return None
    user_id, hashed_password, salt = data
    # 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):
    salt = str(uuid.uuid4())
    hashed_password = sha256_crypt.using(rounds=100000).encrypt(str(password) + salt)
    connect()
    if username == '':
        return False
    if password == '':
        return False
    execute('''
                INSERT INTO users 
                (username, password, salt)
                VALUES (? , ?, ?)
                ''', (username, hashed_password, salt))
    if game_key != '':
        if valid_key(game_key):
            activate_key(game_key, get_user_id_by_name(username))
    own(get_user_id_by_name(username), CURRENCY_NAME)
    return True


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

    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):
    if amount < 0:
        raise AssertionError('Can not send negative amount')

    bank_id_ = bank_id()
    if from_user_id != bank_id_ and not is_bond_of_user(ownable_id, from_user_id):
        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))

    if to_user_id != bank_id_ and not is_bond_of_user(ownable_id, to_user_id):
        execute('''
                    UPDATE ownership
                    SET amount = amount + ?
                    WHERE user_id = ?
                    AND ownable_id = ?
                    ''', (amount, to_user_id, ownable_id,))
    return True


def valid_key(key):
    execute('''
                SELECT key
                FROM keys
                WHERE used_by_user_id IS NULL
                AND key = ?
                ''', (key,))

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


def new_session(user_id):
    session_id = str(uuid.uuid4())

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

    return session_id


def save_key(key):
    execute('''
                INSERT INTO keys 
                (key)
                VALUES (?)
                ''', (key,))


def drop_old_sessions():
    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):
    execute('''
                SELECT rowid
                FROM users
                WHERE username = ?
                ''', (username,))

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


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

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


def get_user_id_by_name(username):
    execute('''
        SELECT users.rowid
        FROM users
        WHERE username = ?
        ''', (username,))

    return current_cursor.fetchone()[0]


def get_user_ownership(user_id):
    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.01 OR ownership.ownable_id = ?)
        AND ownership.ownable_id = ownables.rowid
        ORDER BY ownables.rowid ASC
        ''', (currency_id(), user_id, currency_id(),))

    return current_cursor.fetchall()


def activate_key(key, user_id):
    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():
    execute('''
        SELECT users.rowid
        FROM users
        WHERE username = ?
        ''', (BANK_NAME,))

    return current_cursor.fetchone()[0]


def valid_session_id(session_id):
    execute('''
                SELECT rowid
                FROM sessions
                WHERE session_id = ?
                ''', (session_id,))

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


def get_user_orders(user_id):
    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 current_cursor.fetchall()


def get_user_loans(user_id):
    execute('''
        SELECT 
            rowid, 
            total_amount,
            amount,
            interest_rate
        FROM loans
        WHERE user_id is ?
        ORDER BY rowid ASC
        ''', (user_id,))

    return current_cursor.fetchall()


def bonds():
    execute('''
        SELECT 
            name,
            coupon,
            datetime(maturity_dt, 'unixepoch', 'localtime'),
            username           
        FROM bonds
        JOIN ownables o on bonds.ownable_id = o.rowid
        JOIN users issuer on bonds.issuer_id = issuer.rowid
        ORDER BY coupon * (maturity_dt - ?) DESC
        ''', (current_db_timestamp(),))

    return current_cursor.fetchall()


def get_ownable_orders(user_id, ownable_id):
    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 current_cursor.fetchall()


def sell_ordered_amount(user_id, ownable_id):
    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 current_cursor.fetchone()[0]


def available_amount(user_id, ownable_id):
    execute('''
                SELECT amount
                FROM ownership
                WHERE user_id = ?
                AND ownable_id = ?
                ''', (user_id, ownable_id))

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


def is_bond_of_user(ownable_id, user_id):
    execute('''
    SELECT EXISTS(
        SELECT * FROM bonds 
        WHERE ownable_id = ?
        AND issuer_id = ?
    )
    ''', (ownable_id, user_id,))

    return current_cursor.fetchone()[0]


def user_has_at_least_available(amount, user_id, ownable_id):
    if is_bond_of_user(ownable_id, user_id):
        return True

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

    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 current_cursor.fetchone():
        return True
    else:
        return False


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

    return current_cursor.fetchall()


def ownable_name_exists(name):
    execute('''
                SELECT rowid
                FROM ownables
                WHERE name = ?
                ''', (name,))

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


def new_stock(expiry, name=None):
    while name is None:
        name = random_chars(6)
        if ownable_name_exists(name):
            name = None

    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,
               ioc=False)
    return name


def ownable_id_by_name(ownable_name):
    execute('''
        SELECT rowid
        FROM ownables
        WHERE name = ?
        ''', (ownable_name,))

    return current_cursor.fetchone()[0]


def get_ownership_id(ownable_id, user_id):
    execute('''
        SELECT rowid
        FROM ownership
        WHERE ownable_id = ?
        AND user_id = ?
        ''', (ownable_id, user_id,))

    return current_cursor.fetchone()[0]


def currency_id():
    execute('''
        SELECT rowid
        FROM ownables
        WHERE name = ?
        ''', (CURRENCY_NAME,))

    return current_cursor.fetchone()[0]


def mro_id():
    execute('''
        SELECT rowid
        FROM ownables
        WHERE name = ?
        ''', (MRO_NAME,))

    return current_cursor.fetchone()[0]


def user_money(user_id):
    execute('''
        SELECT amount
        FROM ownership
        WHERE user_id = ?
        AND ownable_id = ?
        ''', (user_id, currency_id()))

    return current_cursor.fetchone()[0]


def delete_order(order_id, new_order_status):
    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,))

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


def current_value(ownable_id):
    if ownable_id == currency_id():
        return 1

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


def execute_orders(ownable_id):
    orders_traded = False
    while True:
        # find order to execute
        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 = current_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:
            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:
            amount = 0

        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
        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
            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:
            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):
    execute('''
        SELECT ownable_id
        FROM ownership
        WHERE rowid = ?
        ''', (ownership_id,))

    return current_cursor.fetchone()[0]


def ownable_name_by_id(ownable_id):
    execute('''
        SELECT name
        FROM ownables
        WHERE rowid = ?
        ''', (ownable_id,))

    return current_cursor.fetchone()[0]


def user_name_by_id(user_id):
    execute('''
        SELECT username
        FROM users
        WHERE rowid = ?
        ''', (user_id,))

    return current_cursor.fetchone()[0]


def bank_order(buy, ownable_id, limit, amount, expiry, ioc):
    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,
                ioc=ioc)
    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()

    execute('''
        SELECT datetime('now')
        ''')

    return current_cursor.fetchone()[0]


def current_db_timestamp():
    connect()

    execute('''
        SELECT CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)
        ''')

    return int(current_cursor.fetchone()[0])


def place_order(buy, ownership_id, limit, stop_loss, amount, expiry, ioc: bool):
    if isinstance(expiry, datetime):
        expiry = expiry.timestamp()
    execute(''' INSERT INTO orders 
                (buy, ownership_id, "limit", stop_loss, ordered_amount, expiry_dt, ioc)
                VALUES (?, ?, ?, ?, ?, ?, ?)
                ''', (buy, ownership_id, limit, stop_loss, amount, expiry, ioc))

    execute_orders(ownable_id_by_ownership_id(ownership_id))

    execute('''DELETE FROM orders WHERE ioc''')
    return True


def trades_on(ownable_id, limit):
    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 current_cursor.fetchall()


def trades(user_id, limit):
    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 current_cursor.fetchall()


def drop_expired_orders():
    execute('''
        SELECT rowid, ownership_id, * FROM orders 
        WHERE expiry_dt < CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)
        ''')

    data = current_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):
    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 current_cursor.fetchone():
        return True
    else:
        return False


def leaderboard():
    score_expression = '''
    SELECT (
        SELECT COALESCE(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
        ), 0)
        FROM ownership
        WHERE ownership.user_id = users.rowid)
        -
        ( SELECT COALESCE(SUM(
            amount
        ), 0)
        FROM loans
        WHERE loans.user_id = users.rowid)
    '''
    execute(f'''
        SELECT * 
        FROM ( -- one score for each user
            SELECT 
                username, 
                ({score_expression}) AS score
                FROM users
            WHERE users.username != ?
        ) AS scores
        ORDER BY score DESC
        LIMIT 50
        ''', (currency_id(), BANK_NAME))

    return current_cursor.fetchall()


def user_wealth(user_id):
    score_expression = '''
    SELECT (
        SELECT COALESCE(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
        ), 0)
        FROM ownership
        WHERE ownership.user_id = ?)
        -
        ( SELECT COALESCE(SUM(
            amount
        ), 0)
        FROM loans
        WHERE loans.user_id = ?)
    '''
    execute(f'''
    SELECT ({score_expression}) AS score
        ''', (currency_id(), user_id, user_id,))

    return current_cursor.fetchone()[0]


def change_password(session_id, password, salt):
    execute('''
                UPDATE users
                SET password = ?, salt= ?
                WHERE rowid = (SELECT user_id FROM sessions WHERE sessions.session_id = ?)
                ''', (password, salt, session_id,))


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


def delete_user(user_id):
    execute('''
        DELETE FROM sessions
        WHERE user_id = ?
        ''', (user_id,))

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

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

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

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

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


def delete_ownable(ownable_id):
    execute('''
        DELETE FROM transactions
        WHERE ownable_id = ?
        ''', (ownable_id,))

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

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

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

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

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


def hash_all_users_passwords():
    execute('''
        SELECT rowid, password, salt
        FROM users
        ''')

    users = current_cursor.fetchall()

    for user_id, pw, salt in users:
        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)
        execute('''
            UPDATE users
            SET password = ?
            WHERE rowid = ?
            ''', (pw, user_id,))


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


def abs_spread(ownable_id):
    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 current_cursor.fetchone()


def ownables():
    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 = current_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():
    execute('''
        DELETE FROM ownership 
        WHERE user_id = ?
        ''', (bank_id(),))


def cleanup():
    global connections
    global current_connection
    global current_cursor
    global current_db_name
    global current_user_id
    for name in connections:
        connections[name].rollback()
        connections[name].close()
    connections = []
    current_connection = None
    current_cursor = None
    current_db_name = None
    current_user_id = None


def ownable_ids():
    execute('''
        SELECT rowid FROM ownables
        ''')

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


def get_old_orders(user_id, include_executed, include_canceled, limit):
    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 current_cursor.fetchall()


def user_has_banking_license(user_id):
    execute('''
        SELECT EXISTS (SELECT * FROM banks WHERE user_id = ?)
        ''', (user_id,))

    return current_cursor.fetchone()[0]


def global_control_value(value_name):
    execute('''
        SELECT value
        FROM global_control_values
        WHERE value_name = ?
        AND dt = (SELECT MAX(dt) FROM global_control_values WHERE value_name = ?)
        ''', (value_name, value_name,))

    return current_cursor.fetchone()[0]


def global_control_values():
    execute('''
        SELECT value_name, value
        FROM global_control_values v1
        WHERE dt IN (SELECT MAX(dt) FROM global_control_values v2 GROUP BY v2.value_name)
        ''')

    return {
        row[0]: row[1] for row in current_cursor.fetchall()
    }


def assign_banking_licence(user_id):
    execute('''
        INSERT INTO banks(user_id)
        VALUES (?)
        ''', (user_id,))


def pay_bond_interest():
    current_dt = execute("SELECT CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)").fetchone()[0]
    sec_per_year = 3600 * 24 * 365
    interests = execute('''
    SELECT 
        SUM(amount * coupon * (MIN(CAST(? AS FLOAT), maturity_dt) - last_interest_pay_dt) / ?) AS interest_since_last_pay,
        o.user_id AS to_user_id,
        bonds.issuer_id AS from_user_id
    FROM bonds
    JOIN ownership o on bonds.ownable_id = o.ownable_id
    WHERE ? - last_interest_pay_dt > ? OR ? > maturity_dt -- every interval or when the bond expired
    AND amount != 0
    GROUP BY o.user_id, bonds.issuer_id
    ''', (current_dt, sec_per_year, current_dt, MIN_INTEREST_INTERVAL, current_dt)).fetchall()

    matured_bonds = execute('''
    SELECT 
        amount,
        o.user_id AS to_user_id,
        bonds.issuer_id AS from_user_id
    FROM bonds
    JOIN ownership o on bonds.ownable_id = o.ownable_id
    WHERE ? > maturity_dt
    ''', (current_dt,)).fetchall()

    # transfer the interest money
    for amount, to_user_id, from_user_id in interests:
        send_ownable(from_user_id, to_user_id, currency_id(), amount)

    # pay back matured bonds
    for amount, to_user_id, from_user_id in matured_bonds:
        send_ownable(from_user_id, to_user_id, currency_id(), amount)

    execute('''
    UPDATE bonds 
    SET last_interest_pay_dt = ?
    WHERE ? - last_interest_pay_dt > ?''', (current_dt, current_dt, MIN_INTEREST_INTERVAL,))

    # delete matured bonds
    execute('''
    DELETE FROM bonds 
    WHERE ? > maturity_dt
    ''', (current_dt,))


def pay_loan_interest():
    current_dt = execute("SELECT CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)").fetchone()[0]
    sec_per_year = 3600 * 24 * 365
    interests = execute('''
    SELECT 
        SUM(amount * interest_rate * (CAST(? AS FLOAT) - last_interest_pay_dt) / ?) AS interest_since_last_pay,
        user_id
    FROM loans
    WHERE ? - last_interest_pay_dt > ?
    GROUP BY user_id
    ''', (current_dt, sec_per_year, current_dt, MIN_INTEREST_INTERVAL)).fetchall()
    executemany(f'''
    UPDATE ownership
    SET amount = amount - ?
    WHERE ownable_id = {currency_id()}
    AND user_id = ?
    ''', interests)
    # noinspection SqlWithoutWhere
    execute('''
    UPDATE loans
    SET last_interest_pay_dt = ?
    WHERE ? - last_interest_pay_dt > ?
    ''', (current_dt, current_dt, MIN_INTEREST_INTERVAL,))


def loan_recipient_id(loan_id):
    execute('''
        SELECT user_id
        FROM loans
        WHERE rowid = ?
        ''', (loan_id,))

    return current_cursor.fetchone()[0]


def loan_remaining_amount(loan_id):
    execute('''
        SELECT amount
        FROM loans
        WHERE rowid = ?
        ''', (loan_id,))

    return current_cursor.fetchone()[0]


def repay_loan(loan_id, amount, known_user_id=None):
    if known_user_id is None:
        user_id = loan_recipient_id(loan_id)
    else:
        user_id = known_user_id
    send_ownable(user_id, bank_id(), currency_id(), amount)

    execute('''
        UPDATE loans
        SET amount = amount - ?
        WHERE rowid = ?
        ''', (amount, loan_id,))
    if loan_remaining_amount(loan_id) == 0:
        execute('''
            DELETE FROM loans
            WHERE rowid = ?
            ''', (loan_id,))


def take_out_personal_loan(user_id, amount):
    execute('''
        INSERT INTO loans(user_id, total_amount, amount, interest_rate) 
        VALUES (?, ?, ?, ?)
        ''', (user_id, amount, amount, global_control_value('personal_loan_interest_rate')))

    send_ownable(bank_id(), user_id, currency_id(), amount)


def loan_id_exists(loan_id):
    execute('''
        SELECT EXISTS (SELECT * FROM loans WHERE rowid = ?)
        ''', (loan_id,))

    return current_cursor.fetchone()[0]


def main_refinancing_operations():
    ...  # TODO


def issue_bond(user_id, ownable_name, coupon, maturity_dt):
    execute('''
    INSERT INTO ownables(name)
    VALUES (?)
    ''', (ownable_name,))
    execute('''
    INSERT INTO bonds(issuer_id, ownable_id, coupon, maturity_dt) 
    VALUES (?, (SELECT MAX(rowid) FROM ownables), ?, ?)
    ''', (user_id, coupon, maturity_dt))