import model
from client_controller import _my_tabulate

model.connect()
# model.cursor.execute('''
#         EXPLAIN QUERY PLAN
#         SELECT users.rowid
#         FROM sessions, users
#         WHERE sessions.session_id = ?
#         AND users.rowid = sessions.user_id
#         '''.replace('?','1'))

model.cursor.execute('''
            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
                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 CASE WHEN sell_order."limit" IS NULL THEN 0 ELSE 1 END ASC, 
                     CASE WHEN buy_order."limit" IS NULL THEN 0 ELSE 1 END ASC, 
                     buy_order."limit" DESC,
                     sell_order."limit" ASC,
                     buy_order.ordered_amount - buy_order.executed_amount DESC,
                     sell_order.ordered_amount - sell_order.executed_amount DESC
            LIMIT 1
            ''', (4, 4,))

print(_my_tabulate(model.cursor.fetchall(),tablefmt='pipe'))