<reponame>vincentmele/betfair
import psycopg2
from psycopg2.extras import DictCursor
import datetime

from typing import List, Dict

from betfairlightweight.resources import MarketBook

import utils


class DB:
    def __init__(self, connection_str: str = None, psql_dsn = None):
        self.connection_str = connection_str if connection_str else None
        if psql_dsn:
            self.conn = psycopg2.connect(**psql_dsn,  # use ~/.pgpass
                                             cursor_factory=DictCursor)    
        else:
            self.conn = None
        self._connect()
        self.table_name = 'inplay_history'

    def _connect(self):
        if self.conn is None or not self._is_alive():
            try:
                self.conn = psycopg2.connect(self.connection_str)
            except Exception as e:
                print('Error connecting to timescaledb postgres instance. ' + str(e))
                raise

    def _is_alive(self):
        try:
            with self.conn.cursor() as cur:
                cur.execute('SELECT 1')
            return True
        except psycopg2.OperationalError:
            return False

    def data_exists(self, race_id):
        cur = self.conn.cursor()
        cur.execute("SELECT id FROM snapshots WHERE id = %s", (race_id,))
        return cur.fetchone() is not None

    def insert_data(self, table):
        return NotImplemented

    def insert_checkpoint(self, race_id: int, milliseconds_to_start: int, data: str, countrycode: str = None, eventtype: int = None):
        params = {}
        cur = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        sql = "INSERT INTO snapshots (id, milliseconds_to_start, data, countrycode, eventtype) " \
              "VALUES (%(id)s, %(milliseconds_to_start)s, %(data)s, %(countrycode)s, %(eventtype)s) " \
              "ON CONFLICT DO NOTHING"
        params['id'] = race_id
        params['milliseconds_to_art'] = milliseconds_to_start
        params['data'] = data
        params['countrycode'] = countrycode
        params['eventtype'] = eventtype
        cur.execute(sql, params)
        self.conn.commit()

    def get_checkpoints(self, race_id: int):
        output = []
        params = {}
        with self.conn.cursor() as cur:
            sql = "SELECT * from snapshots WHERE id = (%(id)s) order by milliseconds_to_start asc"
            params['id'] = race_id
            cur.execute(sql, params)
            for row in cur.fetchall():
                output.append(MarketBook(**row['data']))
        return output


    def get_race_ids(self, event_id: int = 4339,
                     start_time: datetime.datetime = None, end_time: datetime.datetime = None,
                     limit = None, offset = None
                     ) -> List:

        params = {}
        sql = f"SELECT DISTINCT id from {self.table_name} WHERE "

        if start_time and end_time:
            sql += "time > %(start_time)s and time < %(end_time)s AND "
            params['start_time'] = start_time
            params['end_time'] = end_time

        if event_id:
            sql += "cast(jsonb_path_query_first(data, '$.mc.marketDefinition.eventTypeId')->>0 as int4) = %(event_id)s "  # 4339 is greyhounds, 7 is horses
            params['event_id'] = event_id

        sql += "order by id asc "

        if limit:
            sql += "LIMIT %(limit)s "
            params['limit'] = limit

        if offset:
            sql += "OFFSET %(offset)s"
            params['offset'] = offset

        print(f'debug: {sql}')
        with self.conn.cursor() as cur:
            cur.execute(sql, params)
            races = cur.fetchall()

        return [r[0] for r in races]

import psycopg2
from psycopg2.extras import DictCursor
import datetime

from typing import List, Dict

from betfairlightweight.resources import MarketBook

import utils


class DB:
    def __init__(self, connection_str: str = None, psql_dsn = None):
        self.connection_str = connection_str if connection_str else None
        if psql_dsn:
            self.conn = psycopg2.connect(**psql_dsn,  # use ~/.pgpass
                                             cursor_factory=DictCursor)    
        else:
            self.conn = None
        self._connect()
        self.table_name = 'inplay_history'

    def _connect(self):
        if self.conn is None or not self._is_alive():
            try:
                self.conn = psycopg2.connect(self.connection_str)
            except Exception as e:
                print('Error connecting to timescaledb postgres instance. ' + str(e))
                raise

    def _is_alive(self):
        try:
            with self.conn.cursor() as cur:
                cur.execute('SELECT 1')
            return True
        except psycopg2.OperationalError:
            return False

    def data_exists(self, race_id):
        cur = self.conn.cursor()
        cur.execute("SELECT id FROM snapshots WHERE id = %s", (race_id,))
        return cur.fetchone() is not None

    def insert_data(self, table):
        return NotImplemented

    def insert_checkpoint(self, race_id: int, milliseconds_to_start: int, data: str, countrycode: str = None, eventtype: int = None):
        params = {}
        cur = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        sql = "INSERT INTO snapshots (id, milliseconds_to_start, data, countrycode, eventtype) " \
              "VALUES (%(id)s, %(milliseconds_to_start)s, %(data)s, %(countrycode)s, %(eventtype)s) " \
              "ON CONFLICT DO NOTHING"
        params['id'] = race_id
        params['milliseconds_to_art'] = milliseconds_to_start
        params['data'] = data
        params['countrycode'] = countrycode
        params['eventtype'] = eventtype
        cur.execute(sql, params)
        self.conn.commit()

    def get_checkpoints(self, race_id: int):
        output = []
        params = {}
        with self.conn.cursor() as cur:
            sql = "SELECT * from snapshots WHERE id = (%(id)s) order by milliseconds_to_start asc"
            params['id'] = race_id
            cur.execute(sql, params)
            for row in cur.fetchall():
                output.append(MarketBook(**row['data']))
        return output


    def get_race_ids(self, event_id: int = 4339,
                     start_time: datetime.datetime = None, end_time: datetime.datetime = None,
                     limit = None, offset = None
                     ) -> List:

        params = {}
        sql = f"SELECT DISTINCT id from {self.table_name} WHERE "

        if start_time and end_time:
            sql += "time > %(start_time)s and time < %(end_time)s AND "
            params['start_time'] = start_time
            params['end_time'] = end_time

        if event_id:
            sql += "cast(jsonb_path_query_first(data, '$.mc.marketDefinition.eventTypeId')->>0 as int4) = %(event_id)s "  # 4339 is greyhounds, 7 is horses
            params['event_id'] = event_id

        sql += "order by id asc "

        if limit:
            sql += "LIMIT %(limit)s "
            params['limit'] = limit

        if offset:
            sql += "OFFSET %(offset)s"
            params['offset'] = offset

        print(f'debug: {sql}')
        with self.conn.cursor() as cur:
            cur.execute(sql, params)
            races = cur.fetchall()

        return [r[0] for r in races]
# use ~/.pgpass # 4339 is greyhounds, 7 is horses