Orderer/model.py

import random
import re
import sqlite3 as db
import sys
import uuid

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

connection: db.Connection = None
cursor: db.Cursor = None
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)

            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 login(username, password):
    connect()

    # do not allow login as bank
    if password == '':
        return None

    cursor.execute('''
                SELECT rowid
                FROM users
                WHERE username = ?
                AND password = ?
                ''', (username, password))
    user_id = cursor.fetchone()
    if user_id:
        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))
    own(get_user_id_by_name(username), CURRENCY_NAME)
    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):
    cursor.execute('''
                WITH one_ownable_id AS (SELECT rowid FROM ownables WHERE name = ?),
                     one_user_id AS (SELECT ?)
                INSERT INTO ownership (user_id, ownable_id)
                SELECT *
                FROM one_user_id, one_ownable_id
                WHERE NOT EXISTS (
                    SELECT * FROM ownership
                    WHERE ownership.user_id IN one_user_id
                    AND ownership.ownable_id IN one_ownable_id
                )
                ''', (ownable_name, user_id,))


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

    if amount < 0:
        return False

    if from_user_id != bank_id():
        cursor.execute('''
                    UPDATE ownership
                    SET amount = amount - ?
                    WHERE user_id = ?
                    AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
                    ''', (amount, from_user_id, ownable_name,))
        if not cursor.fetchone():
            return False

    cursor.execute('''
                UPDATE ownership
                SET amount = amount + ?
                WHERE user_id = ?
                AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
                ''', (amount, to_user_id, ownable_name))
    if cursor.rowcount == 0:
        return False
    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[0], 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('''
                DELETE FROM sessions s1
                WHERE 
                    (SELECT COUNT(*) as newer
                     FROM sessions s2
                     WHERE s1.user_id = s2.user_id
                     AND s1.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 unused_keys():
    connect()

    cursor.execute('''
        SELECT key
        FROM keys
        WHERE used_by_user_id IS NULL
        ''')

    return [str(key[0]).strip().upper() for key in cursor.fetchall()]


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 ids is None:
        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
        FROM ownership, ownables
        WHERE user_id = ?
        AND ownership.ownable_id = ownables.rowid
        ''', (user_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,))

    if cursor.rowcount == 0:
        raise AssertionError
    send_ownable(bank_id(), user_id, CURRENCY_NAME, 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."limit", 
            CASE 
                WHEN orders."limit" IS NULL THEN NULL 
                WHEN orders.stop_loss THEN 'Yes'
                ELSE 'No'
            END, 
            orders.ordered_amount
        FROM orders, ownables, ownership
        WHERE ownership.user_id = ?
        AND ownership.ownable_id = ownables.rowid
        AND orders.ownership_id = ownership.rowid
        ORDER BY orders.buy DESC, ownables.name ASC
        ''', (user_id,))

    return cursor.fetchall()


def sell_ordered_amount(user_id, ownable_id):
    connect()

    # if ownable_id == currency_id():
    #     return 0

    cursor.execute('''
                SELECT SUM(orders.ordered_amount - orders.executed_amount)
                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 user_owns_at_least(amount, user_id, ownable_id):
    connect()

    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 *
        FROM news
        ORDER BY dt DESC
        LIMIT 20
        ''')

    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(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,))

    cursor.execute('''
        INSERT INTO news(title)
        VALUES (?)
        ''', ('A new stock can now be bought: ' + name,))
    if random.getrandbits(1):
        cursor.execute('''
            INSERT INTO news(title)
            VALUES (?)
            ''', ('Experts expect the price of ' + name + ' to fall',))
    else:
        cursor.execute('''
            INSERT INTO news(title)
            VALUES (?)
            ''', ('Experts expect the price of ' + name + ' to rise',))

    amount = random.randrange(100, 10000)
    price = random.randrange(10000, 20000) / amount
    bank_order(True,
               ownable_id_by_name(name),
               price,
               amount)
    return name


def new_stocks(count=1):
    return [new_stock() for _ in range(count)]


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 execute_orders(ownable_id):
    connect()
    executed_any = True
    while executed_any:
        executed_any = False
        # find order to execute
        cursor.execute('''
            SELECT buy_order.*, sell_order.*, buyer.*, seller.*
            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
                OR sell_order."limit" IS NULL
                OR (sell_order."limit" < buy_order."limit"
                    AND NOT sell_order.stop_loss
                    AND NOT buy_order.stop_loss))
            ORDER BY COALESCE(sell_order."limit", 0) ASC, 
                     COALESCE(buy_order."limit", 0) DESC
            LIMIT 1
            ''', (ownable_id, ownable_id,))

        matching_orders = cursor.fetchone()
        if not matching_orders:
            continue

        # TODO compute execution price, amount, buyer_id and seller_id from matching_orders
        price = -1

        if price < 0 or amount < 0:
            return AssertionError()

        # actually execute the order
        cursor.execute('''
            UPDATE ownership 
            SET amount = amount - ?
            WHERE user_id = ?
            ''', (price, buyer_id))
        if not cursor.fetchone():
            raise AssertionError()

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

        if not cursor.fetchone():
            raise AssertionError()
        cursor.execute('''
            UPDATE ownership 
            SET amount = amount + ?
            WHERE user_id = ?
            ''', (amount, buyer_id))

        if not cursor.fetchone():
            raise AssertionError()
        cursor.execute('''
            UPDATE ownership 
            SET amount = amount + ?
            WHERE user_id = ?
            ''', (price, seller_id))
        if not cursor.fetchone():
            raise AssertionError()
        cursor.execute('''
            UPDATE orders 
            SET executed_amount = executed_amount + ?
            WHERE rowid = ?
            OR rowid = ?
            ''', (amount, buy_order_id, sell_order_id))
        if not cursor.fetchone():
            raise AssertionError()

        executed_any = True

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

        # trigger stop loss orders
        cursor.execute('''
            UPDATE orders
            SET stop_loss = FALSE,
                "limit" = NULL
            WHERE stop_loss
            AND (buy AND "limit" > ?)
                OR (NOT buy AND "limit" < ?)
            (price, ownable_id, amount)
            VALUES(?, ?, ?)
            ''', (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 bank_order(buy, ownable_id, limit, amount):
    if limit is None:
        raise AssertionError()
    place_order(buy,
                get_ownership_id(ownable_id, bank_id()),
                limit,
                False,
                amount)


def place_order(buy, ownership_id, limit, stop_loss, amount):
    connect()

    cursor.execute('''
                INSERT INTO orders 
                (buy, ownership_id, "limit", stop_loss, ordered_amount)
                VALUES (?, ?, ?, ?, ?)
                ''', (buy, ownership_id, limit, stop_loss, amount))

    execute_orders(ownable_id_by_ownership_id(ownership_id))
    return True


def transactions(ownable_id):
    connect()

    cursor.execute('''
        SELECT dt, amount, price
        FROM transactions
        WHERE ownable_id = ?
        ORDER BY dt DESC
        ''', (ownable_id,))

    return cursor.fetchall()