import hashlib
import os
import pickle
from collections import defaultdict
from copy import deepcopy
import dateutil.parser as parser
import pytz
from bson import ObjectId
from datetime import timedelta, datetime, date
from pymongo.errors import DuplicateKeyError, BulkWriteError
from pymongo import InsertOne

from sgmtradingcore.core.enums import GsmCompetitions
from sgmtradingcore.core.ts_log import get_logger
from sgmtradingcore.core.trading_types import InstructionStatus, OrderStatus
from stratagemdataprocessing.data_api import find_football_fixtures, find_basketball_fixtures, find_tennis_fixtures, \
    get_match_info_single_sport
from stratagemdataprocessing.dbutils.mongo import MongoPersister
from stratagemdataprocessing.enums.odds import Sports
from stratagemdataprocessing.parsing.common.stickers import sticker_parts_from_sticker, parse_sticker
from stratagemdataprocessing.events.fixture_cache import FixtureCache


class BacktestConfig(object):

    def __init__(self, strategy_name, static_params, variable_params, short_name=None):
        self.strategy_name = strategy_name
        self.static_params = static_params
        self.variable_params = variable_params
        self.short_name = short_name
        self.object_id = None

    def to_document(self, dt):
        result = {
            'update_dt': dt,
            'strategy_name': self.strategy_name,
            'static_params': self.static_params,
            'variable_params': self.variable_params,
        }

        if self.short_name is not None:
            result['short_name'] = self.short_name

        return result

    @classmethod
    def from_document(cls, doc):
        config = cls(
            doc['strategy_name'], doc['static_params'], doc['variable_params'], doc.get('short_name', None)
        )
        config.object_id = doc['_id']


class BacktestOrder(object):
    """
    A single order created by the backtest which might or might not have settled.
    When settled also includes the pnl.
    """

    def __init__(self, sticker, is_back, odds, size, order_odds, order_size, details, placed_dt,
                 settled_dt=None, pnl=None):
        self.sticker = sticker
        self.is_back = is_back
        self.odds = odds
        self.size = size
        self.order_odds = order_odds
        self.order_size = order_size
        self.details = details
        self.placed_dt = placed_dt
        self.settled_dt = settled_dt
        self.pnl = pnl

    def to_document(self):
        result = {
            'sticker': self.sticker,
            'is_back': self.is_back,
            'odds': self.odds,
            'size': self.size,
            'order_odds': self.order_odds,
            'order_size': self.order_size,
            'details': self.details,
            'placed_dt': self.placed_dt
        }

        if self.settled_dt is not None:
            result['settled_dt'] = self.settled_dt
            result['pnl'] = self.pnl

        return result

    @classmethod
    def from_document(cls, doc):
        return cls(
            doc['sticker'], doc['is_back'], doc['odds'], doc['size'], doc['order_odds'],
            doc['order_size'], doc['details'], doc['placed_dt'], doc.get('settled_dt', None),
            doc.get('pnl', None)
        )


class BacktestResults(object):
    """
    The orders generated for an entire period of the backtest for a single
    strategy config id - this is the structure generated by the client code
    """

    def __init__(self, optimization_name, strategy_config_id, orders, short_name=None):
        self.optimization_name = optimization_name
        self.strategy_config_id = strategy_config_id
        self.orders = orders
        self.short_name = short_name

    def to_daily_results(self):
        orders_by_date = defaultdict(list)

        for order in self.orders:
            orders_by_date[order.placed_dt.date()].append(order)

        result = []
        for date_, orders in orders_by_date.items():
            result.append(
                BacktestDailyResults(self.optimization_name, self.strategy_config_id, date_, orders, self.short_name)
            )

        return result


class BacktestDailyResults(object):
    """
    The orders generated for a single day of the backtest for a single
    strategy config id
    """

    def __init__(self, optimization_name, strategy_config_id, date_, orders, short_name=None):
        self.optimization_name = optimization_name
        self.strategy_config_id = strategy_config_id
        self.date_ = date_
        self.orders = orders
        self.short_name = short_name

    def to_document(self, dt):
        result = {
            'update_dt': dt,
            'date': self.date_.strftime('%Y%m%d'),
            'optimization_name': self.optimization_name,
            'config_id': self.strategy_config_id,
            'orders': [order.to_document() for order in self.orders],
        }

        if self.short_name is not None:
            result['short_name'] = self.short_name

        return result

    @classmethod
    def from_document(cls, doc):
        orders = [BacktestOrder.from_document(order) for order in doc['orders']]
        return cls(
            doc['optimization_name'], doc['config_id'], datetime.strptime(doc['date'], '%Y%m%d').date(),
            orders, doc.get('short_name', None)
        )


class BacktestOptimizationRun(object):
    """
    A single run of the backtest as part of the optimization, including the
    strategy config if and the start and end dates
    """

    def __init__(self, strategy_config_id, start_date, end_date, score,
                 metrics=None, optimization_params=None, short_name=None):

        if metrics is None:
            metrics = {}
        if optimization_params is None:
            optimization_params = {}

        self.strategy_config_id = strategy_config_id
        self.start_date = start_date
        self.end_date = end_date
        self.short_name = short_name
        self.score = score
        self.metrics = metrics
        self.optimization_params = optimization_params

    def to_document(self, dt):
        result = {
            'update_dt': dt,
            'config_id': self.strategy_config_id,
            'start_date': self.start_date.strftime('%Y%m%d'),
            'end_date': self.end_date.strftime('%Y%m%d'),
            'score': self.score,
            'metrics': self.metrics,
            'optimization_params': self.optimization_params,
        }

        if self.short_name is not None:
            result['short_name'] = self.short_name

        return result

    @classmethod
    def from_document(cls, doc):
        return cls(
            doc['config_id'], datetime.strptime(doc['start_date'], '%Y%m%d').date(),
            datetime.strptime(doc['end_date'], '%Y%m%d').date(), doc['score'], doc.get('metrics', {}),
            doc.get('optimization_params', {}), doc.get('short_name', None)
        )

    def __eq__(self, other):
        return (
                isinstance(other, BacktestOptimizationRun) and
                self.strategy_config_id == other.strategy_config_id and
                self.start_date == other.start_date and
                self.end_date == other.end_date
        )

    def __hash__(self):
        return hash(self.strategy_config_id) ^ hash(self.start_date) ^ hash(self.end_date)


class BacktestOptimization(object):
    """
    Highest level results structure of an optimization, it should be created at the beginning
    and then populated with the each run of a parameter iteration and ultimately with the
    out of sample run using optimized parameters
    """

    def __init__(self, name, optimization_runs, out_of_sample_run):
        self.name = name
        self.optimization_runs = optimization_runs
        self.out_of_sample_run = out_of_sample_run

    def to_document(self, dt):
        result = {
            'update_dt': dt,
            'name': self.name,
            'optimization': [opt_run.to_document(dt) for opt_run in self.optimization_runs],
        }

        if self.out_of_sample_run is not None:
            result['out_of_sample'] = self.out_of_sample_run.to_document(dt)

        return result

    @classmethod
    def from_document(cls, doc):
        opt_runs = [BacktestOptimizationRun.from_document(opt_run) for opt_run in doc['optimization']]

        if 'out_of_sample' in doc:
            out_of_sample_run = BacktestOptimizationRun.from_document(doc['out_of_sample'])
        else:
            out_of_sample_run = None

        return cls(
            doc['name'], opt_runs, out_of_sample_run
        )


def _make_hashable(arg):
    if isinstance(arg, dict):
        return tuple(sorted({
                                k: _make_hashable(v) for k, v in arg.iteritems()
                            }.items()))

    elif isinstance(arg, list):
        return tuple(map(_make_hashable, arg))

    else:
        return arg


def generate_config_key(*args):
    return tuple(map(_make_hashable, args))


def load_configurations(conn, strategy_name, by_id=False):
    all_configs_iter = conn['backtest_configurations'].find({'strategy_name': strategy_name})

    result = {}

    if by_id:
        for config in all_configs_iter:
            result[config['_id']] = config
    else:
        for config in all_configs_iter:
            key = generate_config_key(config['static_params'], config['variable_params'])
            result[key] = config

    return result


def ensure_configurations(conn, strategy_name, configurations, dt):
    """
    Check if configurations exist otherwise insert them and populate
    each one with the mongo object id
    """

    all_configs = load_configurations(conn, strategy_name)
    to_insert = []

    for config in configurations:
        if config.strategy_name != strategy_name:
            raise ValueError('All configurations must be for the provided strategy name')

        key = generate_config_key(config.static_params, config.variable_params)

        if key in all_configs:
            config.object_id = all_configs[key]['_id']
        else:
            to_insert.append(config)

    if len(to_insert) > 0:
        docs = [config.to_document(dt) for config in to_insert]
        res = conn['backtest_configurations'].insert_many(docs)

        for i, object_id in enumerate(res.inserted_ids):
            to_insert[i].object_id = object_id


def save_run_results(conn, run_results, dt):
    """
    Save the results or a run, splitting them up by settlement date to
    make things more manageable and ultimately to allow efficient
    rerunning
    """
    results_daily = run_results.to_daily_results()

    if len(results_daily):
        for result in results_daily:
            doc = result.to_document(dt)
            conn['backtest_results'].update(
                {'config_id': doc['config_id'], 'date': doc['date'], 'optimization_name': doc['optimization_name']},
                doc, upsert=True
            )


def load_run_results(conn, optimization_name, strategy_config_id=None, short_name=None, dates=None):
    """
    Load the results of a run by id or short_name and optionally for a fixed
    set of dates (e.g. for an out of sample run or a run that is part of an optimization run).b
    The results are converted to a BacktestResults instance for use in client code.
    """

    if strategy_config_id is None and short_name is None:
        raise ValueError('Must provide at least a config id or short name')

    if short_name is not None:
        config = conn['backtest_configurations'].find({'short_name': short_name})
        config_id = config['_id']
    else:
        config_id = strategy_config_id

    results_query = {'optimization_name': optimization_name, 'config_id': config_id}
    if dates is not None:
        results_query['date'] = {'$in': [date_.strftime('%Y%m%d') for date_ in dates]}

    result_docs = conn['backtest_results'].find(results_query)

    orders = []
    for result_doc in result_docs:
        daily_result = BacktestDailyResults.from_document(result_doc)

        orders.extend(daily_result.orders)

    return BacktestResults(optimization_name, config_id, orders, short_name=short_name)


def _load_optimization_doc(conn, name, return_none=False):
    """
    Load the optimization object for a single run. This includes all the backtests ran
    under tha optimization without any order details, only score, metrics and some
    optimization process metadata is included
    """
    opt_results = list(conn['backtest_optimization'].find({'name': name}))

    if len(opt_results) == 0 and return_none:
        return None

    if len(opt_results) != 1:
        raise ValueError('Did not find unique document for optimization results for %s' % name)

    return opt_results[0]


def load_optimization(conn, name, return_none=False):
    opt_doc = _load_optimization_doc(conn, name, return_none=return_none)

    if opt_doc is None and return_none:
        return None

    return BacktestOptimization.from_document(opt_doc)


def save_optimization(conn, opt_results, dt):
    """
    Save or overwrite the results of an optimization, this should be used by the client code
    and in case of a distributed backtest by the driver program
    """
    doc = opt_results.to_document(dt)
    conn['backtest_optimization'].update({'name': opt_results.name}, doc, upsert=True)


def insert_optimization_runs(conn, optimization_name, runs, dt):
    """
    Add new runs to the optimization results object, generally expected
    to be done at the end of evaluating a generation
    """
    docs = [run.to_document(dt) for run in runs]

    conn['backtest_optimization'].update({'name': optimization_name}, {'$push': {'optimization': {'$each': docs}}})


def load_optimization_results(conn, name):
    """
    Load the results for an entire backtest optimization by looking up the
    runs made and from there getting the individual order results
    """
    opt_result = _load_optimization_doc(conn, name)

    if 'out_of_sample' in opt_result:
        out_of_sample_run = BacktestOptimizationRun.from_document(opt_result['out_of_sample'])

        out_of_sample_dates = []
        d = out_of_sample_run.start_date
        while d <= out_of_sample_run.end_date:
            out_of_sample_dates.append(d)
            d += timedelta(days=1)

        out_of_sample_results = load_run_results(
            conn, name, out_of_sample_run.strategy_config_id, dates=out_of_sample_dates)
    else:
        out_of_sample_results = None

    optimization_runs = [BacktestOptimizationRun.from_document(opt_doc) for opt_doc in opt_result['optimization']]
    optimization_results = {}

    for optimization_run in optimization_runs:
        optimization_dates = []
        d = optimization_run.start_date
        while d <= optimization_run.end_date:
            optimization_dates.append(d)
            d += timedelta(days=1)

        optimization_results[optimization_run] = load_run_results(
            conn, name, optimization_run.strategy_config_id, dates=optimization_dates)

    return {
        'out_of_sample': out_of_sample_results,
        'optimization': optimization_results,
    }


football_fixtures_coll = None


def _get_football_fixtures_coll():
    global football_fixtures_coll

    if football_fixtures_coll is None:
        football_fixtures_coll = MongoPersister.init_from_config('football', auto_connect=True).db.fixtures

    return football_fixtures_coll


def fetch_fixtures_ids(start_time, end_time, sports, use_cache=False):
    """
    Return a list of all the fixtures ids starting between start_time and end_time

    :returns [(kickoff, fixture_id)]
    e.g.
        return [(2017-04-03T14:45:00Z, 'GSM2564'). (2017-04-09T16:15:00Z, 'ENP2485751')]
    """

    if len(sports) == 0:
        return []

    logger = get_logger(__name__)
    logger.info(None, "Fetching fixture ids for {} days from {}. Cache: {}...".format(
        (end_time - start_time).days + 1, start_time.date(), use_cache))
    if use_cache:
        return _fetch_fixtures_ids_file_cached(start_time, end_time, sports)

    football_ids = []
    tennis_ids = []
    basketball_ids = []

    if Sports.FOOTBALL in sports:
        fixtures_collection = _get_football_fixtures_coll()

        if fixtures_collection is not None:
            fixtures_in_range = list(fixtures_collection.find(
                {'kick_off.utc.gsm.date_time': {'$gte': start_time, '$lt': end_time}},
                {'gsm_id': 1, 'kick_off': 1}
            ))

            football_ids = [
                (f[u'kick_off'][u'utc'][u'gsm'][u'date_time'].strftime('%Y-%m-%dT%H:%M:%SZ'), 'GSM' + str(f['gsm_id']))
                for f in fixtures_in_range
            ]

        else:
            fixtures = find_football_fixtures([int(c) for c in GsmCompetitions], start_time, end_time, batch_load=True)
            football_ids = [(f[u'kick_off'][u'utc'][u'gsm'][u'date_time'], 'GSM' + str(f['gsm_id']))
                            for f in fixtures]
    if Sports.TENNIS in sports:
        fixtures = find_tennis_fixtures(start_time, end_time, batch_load=False)
        tennis_ids = list()
        for f in fixtures:
            if f[u'details'][u'gameStarted'] != '-':
                tennis_ids.append((f[u'details'][u'gameStarted'], f['event_id']))
            else:
                tennis_ids.append((f[u'start_date'], f['event_id']))
        tennis_ids = list(set(tennis_ids))
    if Sports.BASKETBALL in sports:
        fixtures = find_basketball_fixtures(start_time, end_time)
        basketball_ids = [(f['start_date'], f['event_id']) for f in fixtures]
        # fixtures = find_basketball_fixtures([c for c in GsmCompetitions], start_time, end_time, batch_load=True)

    return football_ids + tennis_ids + basketball_ids


def daterange(start_date, end_date):
    for n in range(int((end_date - start_date).days) + 1):
        yield start_date + timedelta(n)


fixture_mem_cache = dict()


def _load_fixture_ids_from_file(start_time, end_time, sport, sport_dir):
    files = os.listdir(sport_dir)
    existing_dates = [f.split('_')[0] for f in files]

    for d in daterange(start_time.date(), end_time.date()):
        if str(d) not in existing_dates:
            fixtures_ids = fetch_fixtures_ids(datetime.combine(d, datetime.min.time()),
                                              datetime.combine(d, datetime.max.time()),
                                              [sport],
                                              use_cache=False)
            _store_fixture_ids_into_file(sport_dir, fixtures_ids, d, sport)

    fixtures_ids = []
    logger = get_logger(__name__)
    global fixture_mem_cache
    for d in daterange(start_time.date(), end_time.date()):
        filename = "{}_{}.pickle".format(str(d), str(sport))
        file_path = os.path.join(sport_dir, filename)
        if file_path in fixture_mem_cache:
            fixtures_ids.extend(fixture_mem_cache[file_path])
            continue
        logger.info(None, "Loading {}...".format(file_path))
        with open(file_path, 'rb') as handle:
            loaded = pickle.load(handle)
            fixtures_ids.extend(loaded)
            fixture_mem_cache[file_path] = loaded
    return fixtures_ids


def _store_fixture_ids_into_file(file_dir, fixtures_ids, result_date, sport):
    """
    :param fixtures_ids: type (datetime, id)
    :return:
    """
    logger = get_logger(__name__)
    logger.info(None, "Storing fixtures ids {}...".format(len(fixtures_ids)))
    filename = "{}_{}.pickle".format(str(result_date), str(sport))
    file_path = os.path.join(file_dir, filename)
    if os.path.exists(file_path):
        return
    with open(file_path, 'wb') as handle:
        pickle.dump(fixtures_ids, handle, protocol=pickle.HIGHEST_PROTOCOL)
    logger.info(None, "Written {} into {}".format(len(fixtures_ids), file_path))
    logger.info(None, "Storing done")


def _fetch_fixtures_ids_file_cached(start_time, end_time, sports, file_dir=None):
    """
    Cache in local memory fixture ids
    :return:
    """
    if len(sports) == 0:
        return []

    if file_dir is None:
        file_dir = os.path.expanduser('~/.mongocache/fixture_ids')
    if not os.path.exists(file_dir):
        os.makedirs(file_dir)

    fixtures_ids = []
    for sport in sports:
        sport_dir = os.path.join(file_dir, "sport_{}".format(sport))
        if not os.path.exists(sport_dir):
            os.makedirs(sport_dir)
        fixtures_ids.extend(_load_fixture_ids_from_file(start_time, end_time, sport, sport_dir))
    return fixtures_ids


class MongoDuplicateDayException(Exception):
    """
    Indicate that we are trying to store result for the same (day, strategy, config, mnemonic)
    """

    def __init__(self, msg):
        self.error = msg

    def __str__(self):
        return repr(self.error)


class MongoStrategyHelper(object):
    """
    Pre-made queries to interrogate mongo about strategies, backtesting and optimizations
    """

    def __init__(self, backtest_db='backtesting', realtime_db='strategies', results_coll='strategy_results',
                 runs_coll='strategy_runs',
                 conf_coll='strategy_configurations', scratch_coll='scratch', use_scalable_storage=True,
                 trading_db='trading', sports=None, fixture_cache=None):
        """
        Mongo db and collections names can ben changed, in case you need to store too much data and do not want to trash
        the default collections. It is advisable to change only backtest_db and leave the collection named as they are.

        :param backtest_db: URL to mongo db, a key in in settings.py E.g. 'backtesting_dev'. Used for results_coll
        :param realtime_db: mongo db settings, used for runs_coll, conf_coll, and scratch_coll
        :param results_coll: name of the mongo collection where to store the backtest results.
        :param runs_coll: every runs it stored here. IT tells when the strategy has started and with which config
        :param conf_coll: strategy configurations are stored here
        :param scratch_coll:
        """
        self.backtesting_conn = MongoPersister.init_from_config(backtest_db, auto_connect=True)
        self.realtime_conn = MongoPersister.init_from_config(realtime_db, auto_connect=True)
        self._config_coll = conf_coll
        self._backtest_results_coll = results_coll
        self._strategy_runs_coll = runs_coll
        self._scratch_coll = scratch_coll
        self._backtest_orders_coll = 'orders'
        self._backtest_instructions_coll = 'instructions'
        self._template_signals_coll = 'signals'

        self._sports = sports or [Sports.FOOTBALL, Sports.TENNIS, Sports.BASKETBALL]
        if Sports.CRYPTO in self._sports:
            if 'crypto' not in backtest_db:
                raise ValueError("backtest_db '{}' is not a crypto one".format(backtest_db))
        self._start_times = dict()  # {event_id: kickoff_time}

        self.trading_prod_conn = MongoPersister.init_from_config(trading_db, auto_connect=True)
        self._orders_coll = 'orders'
        self._instructions_coll = 'instructions'

        self._use_scalable_storage = use_scalable_storage

        self._fixture_cache = fixture_cache or FixtureCache()

        self._logger = get_logger(__name__)

    # ######################### NEW METHODS ######################### #
    def get_config_from_id(self, config_id_str):
        """
        Return configuration from configuration_id or None if not present.

        :param config_id_str: ObjectID string,
        :return: the configuration as stored in mongo as a dict, or None
        """
        return self.realtime_conn[self._config_coll].find_one({'_id': ObjectId(config_id_str)})

    def get_and_verify_config_from_id(self, config_id_str, strategy_name, strategy_desc):

        strategy_config = self.get_config_from_id(config_id_str)
        if strategy_config is None:
            raise ValueError('Backtest configuration {} does not exist for strategy {}/{}'.format(
                config_id_str, strategy_name, strategy_desc))
        else:
            if strategy_config['strategy_name'] != strategy_name or strategy_config['strategy_desc'] != strategy_desc:
                if strategy_config['strategy_name'] not in ['Analyst_FTOUG', 'Analyst_FTAHG'] and \
                        strategy_name not in ['Analyst_FTOUG', 'Analyst_FTAHG'] and \
                        strategy_config['strategy_desc'] != 'all':
                    raise ValueError('Config_id {} not suitable for {}/{}'.format(
                        config_id_str, strategy_name, strategy_desc))

        return strategy_config

    def get_days_already_run(self, strategy_name, strategy_desc, trading_user_id, strategy_code,
                             mnemonic, backtest_range, config_id=None):
        """
        Returns all the days already run for this range.

        Query is based on strategy_name, strategy_desc, mnemonic, range_name, config_id
        :param strategy_name:
        :param strategy_desc:
        :param trading_user_id:
        :param strategy_code:
        :param mnemonic:
        :param backtest_range: as returned by RunnableSimpleStrategyUnit.backtesting_date_ranges()
        :return: ['2017-05-02', '2016-05-01', ...]
        :param config_id:
        """
        if config_id is not None:
            config = self.get_config_from_id(config_id)
            if config is None:
                raise ValueError('config_id {} not present'.format(config_id))

        cmd = {
            'strategy_name': strategy_name,
            'strategy_desc': strategy_desc,
            'trading_user_id': trading_user_id,
            'strategy_code': strategy_code,
            'mnemonic': mnemonic,
            'range_name': backtest_range[3]
        }
        if config_id is not None:
            cmd.update({'config_id': config_id})

        self._logger.info(None, "Checking for dates in db with {}".format(cmd))

        res = self.backtesting_conn[self._backtest_results_coll].find(cmd, {'date': 1})
        dates = [el['date'] for el in res]

        dates.sort()
        self._logger.info(None, "Found results for {}".format([str(dd) for dd in dates]))

        return dates

    def get_strategy_config_id(self, strategy_name, strategy_desc, strategy_code, configs=None):
        """
        Return config_id of the selected configuration.
        Since this can be used by backtests and optimizations, strategy_name and strategy_desc are not enough
        to identify a configuration.
        :param strategy_name
        :param strategy_desc
        :param strategy_code
        :param configs: {} or nested dict of dicts
        :return config_id as a string
        """
        if not configs:
            configs = {}
        if configs == {}:
            query = {'strategy_name': strategy_name,
                     'strategy_desc': strategy_desc,
                     'strategy_code': strategy_code}
            proj = {'_id': 1}
            confs = [o for o in self.realtime_conn[self._config_coll].find(query, proj)]
            if len(confs) > 1:
                raise LookupError('{} strategies found with strategy_name:{}, strategy_desc:{}'.format(
                    len(confs), strategy_name, strategy_desc))
            return str(confs[0]['_id'])
        else:
            query = self._config_to_mongo_document(strategy_name, strategy_desc, strategy_code, configs,
                                                   datetime.now(pytz.utc), self.realtime_conn[self._scratch_coll])
            del query['update_dt']
            for k, v in query['params'].iteritems():
                query.update({"params." + k: v})
            del query['params']

            proj = {'_id': 1}
            out = self.realtime_conn[self._config_coll].find(deepcopy(query), proj)
            confs = [i for i in out]
            if len(confs) > 1:
                raise LookupError('{} strategies found with '
                                  'strategy_name:{}, strategy_desc:{}, strategy_code:{} '
                                  'options: {}\n Ids are: {}'.format(
                    len(confs), strategy_name, strategy_desc, strategy_code, configs,
                    [str(c['_id']) for c in confs]))
            if len(confs) == 0:
                return None
            return str(confs[0]['_id'])

    def get_strategy_config_id_from_hash(self, config_hash):
        """
        Return config_id of the selected configuration.
        Since this can be used by backtests and optimizations, strategy_name and strategy_desc are not enough
        to identify a configuration.
        :param config_hash: hash of the config
        :return config_id as a string
        """
        query = {'sha256': config_hash}
        out = self.realtime_conn[self._config_coll].find(deepcopy(query))
        confs = [i for i in out]
        if len(confs) == 0:
            return None
        return str(confs[0]['_id'])

    @staticmethod
    def _config_to_mongo_document(strategy_name, strategy_desc, strategy_code, in_config, update_datetime,
                                  scratch_collection):
        """
        Return an entry for mongo collection 'strategy_configurations'.

        The entry contains {'sha256': ...} which is hash(str(dict)) of the dict ordered by keys.

        """
        config = {
            'strategy_name': strategy_name,
            'strategy_desc': strategy_desc,
            'strategy_code': strategy_code,
            'params': deepcopy(in_config),
        }

        res = scratch_collection.insert_one(config)
        mongoified_config = [o for o in scratch_collection.find({'_id': res.inserted_id})][0]
        del mongoified_config['_id']

        ordered_dict = {}
        for k in sorted(mongoified_config['params'].keys()):
            ordered_dict[k] = mongoified_config['params'][k]

        dict_hash = {  # Hash is computed on this, where the params have been added to a new dict sorted by key
            'strategy_name': mongoified_config['strategy_name'],
            'strategy_desc': mongoified_config['strategy_desc'],
            'strategy_code': mongoified_config['strategy_code'],
            'params': ordered_dict,
        }
        sha256 = hashlib.sha256()

        sha256.update(str(dict_hash))  # note: this is order dependent
        digest = sha256.hexdigest()

        d = {
            'update_dt': update_datetime,
            'strategy_name': mongoified_config['strategy_name'],
            'strategy_desc': mongoified_config['strategy_desc'],
            'strategy_code': mongoified_config['strategy_code'],
            'params': ordered_dict,
        }
        # Set 'sha256' as the hash to avoid collisions
        d.update({'sha256': digest})

        return d

    @staticmethod
    def _backtest_result_to_mongo_document(strategy_run_id_str, strategy_name, strategy_desc, trading_user_id,
                                           strategy_code, mnemonic, run_start_time, orders_date, config_id_str,
                                           instructions_data, orders_data, backtest_range, use_scalable_storage=True):
        """
        Return an entry for mongo collection 'strategies_backtest_results'.

        An entry refer to a specific strategy run and a specific date.
        :param strategy_run_id_str: _id in 'strategy_runs' collections. type string
        :param strategy_name:
        :param strategy_desc:
        :param mnemonic: mnemonic name given at the start of the backtest. 'automatic' for automatic backtesting
        :param run_start_time: start time of the backtest. type datetime
        :param config_id_str: string with the config_id in the collection 'strategies_configurations_v2'
        :param orders_data: list of orders dict derived from backtest Order
        :param orders_date: date which the orders refer to. Type datetime.Date
        :param backtest_range: as returned by RunnableSimpleStrategyUnit.backtesting_date_ranges()
        :return: dict
        """
        date_string = orders_date.strftime('%Y-%m-%d')

        doc = {
            'update_dt': datetime.now(tz=pytz.utc),  # update time of the mongo doc
            'strategy_name': strategy_name,
            'strategy_desc': strategy_desc,
            'trading_user_id': trading_user_id,
            'strategy_code': strategy_code,
            'strategy_run_id': strategy_run_id_str,
            'config_id': str(config_id_str),
            'date': date_string,
            'range': backtest_range,
            'range_name': backtest_range[3],
            'run_start_time': run_start_time,
            'mnemonic': mnemonic,
        }
        if use_scalable_storage:
            doc.update({'n_orders': len(orders_data),
                        'n_instructions': len(instructions_data)})
        else:
            doc.update({'orders': orders_data,
                        'instructions': instructions_data})

        return doc

    @staticmethod
    def _strategy_run_to_mongo_document(strategy_name, strategy_desc, strategy_code, trading_user_id, mnemonic,
                                        start_datetime, config_id, config, is_prod, is_backtest, is_optimization,
                                        command_line, commit, env, backtest_config=None, run_duration=None):
        """
        Return an entry for mongo collection 'strategies_backtest_results_v2'.

        There has to be only one entry referring to a specific strategy run and a specific date.
        :param strategy_name:
        :param strategy_desc:
        :param strategy_code:
        :param mnemonic: mnemonic name given at the start of the strategy. 'automatic' for automatic backtesting
        :param start_datetime: start time of the strategy
        :param command_line: cmd line used to start the strategy
        :param config_id: string with the config_id in the collection 'strategies_configurations_v2'
        :param commit: git commit hash, to identify the code
        :param run_duration: timedelta or None
        :return: dict
        """
        d = {
            'update_dt': datetime.now(tz=pytz.utc),
            'strategy_name': strategy_name,
            'strategy_desc': strategy_desc,
            'trading_user_id': trading_user_id,
            'config_id': str(config_id),
            'config': config,  # Not necessary but replicated here for easier querying
            'start_time': start_datetime,
            'mnemonic': mnemonic,
            'is_prod': True if is_prod else False,
            'is_backtest': True if is_backtest else False,
            'is_optimization': True if is_optimization else False,
            'command_line': command_line,
            'commit': commit,
            'env': env,
        }
        if backtest_config is not None and not is_prod and (is_backtest or is_optimization):
            d.update({'backtest_config': backtest_config})
        if strategy_code is not None:
            d.update({'strategy_code': strategy_code})
        if run_duration is not None:
            d.update({'run_duration': run_duration.total_seconds()})

        return d

    def ensure_configurations(self, strategy_name, strategy_desc, strategy_code, configuration):
        """
        Check if configuration exists in mongo, if not insert it.

        :param strategy_name
        :param strategy_desc
        :param strategy_code
        :param configuration: dict of strategy params where the keys are strings. Can contains dict of dict.
        :return the Mongo objectID string representation of the configuration
        """

        config_id = self.get_strategy_config_id(strategy_name, strategy_desc, strategy_code, configuration)
        if config_id:
            return config_id

        update_datetime = datetime.now(tz=pytz.utc)
        doc = self._config_to_mongo_document(strategy_name, strategy_desc, strategy_code, configuration,
                                             update_datetime, self.realtime_conn[self._scratch_coll])

        try:
            res = self.realtime_conn[self._config_coll].insert_one(doc)
        except DuplicateKeyError as e:
            # Okay, this avoid concurrency problems
            config_id = self.get_strategy_config_id(strategy_name, strategy_desc, strategy_code, configuration)
            if config_id is None:
                config_id = self.get_strategy_config_id_from_hash(doc['sha256'])
            if config_id is None:
                raise ValueError("Canot find config for doc {}: error is {}".format(doc, e))
            return config_id

        return str(res.inserted_id)

    def log_strategy_run(self, strategy_name, strategy_desc, strategy_code, trading_user_id, config_id,
                         strategy_start_time, mnemonic,
                         is_prod, is_backtest, is_optimization, command_line, env, backtest_config=None,
                         run_duration=None):
        """
        Log in mongo the start of the strategy run.

        :return strategy_run_id in the self._strategy_runs_coll collection

        """
        config = self.get_config_from_id(config_id)
        if config is None:
            raise ValueError("config_id {} not present in Mongo".format(config_id))
        if not isinstance(strategy_start_time, datetime):
            raise ValueError("strategy_start_time is not a datetime.datetime")

        # TODO read commit from file instead
        commit = 'XXX'
        # if not is_prod:
        #     commit = subprocess.check_output(['git', 'rev-parse', 'HEAD']).strip()

        doc = self._strategy_run_to_mongo_document(strategy_name, strategy_desc, strategy_code, trading_user_id,
                                                   mnemonic, strategy_start_time, config_id, config,
                                                   is_prod, is_backtest, is_optimization, command_line, commit,
                                                   env, backtest_config, run_duration=run_duration)

        res = self.realtime_conn[self._strategy_runs_coll].insert_one(doc)

        return res.inserted_id

    def write_backtest_result_to_db(self, strategy_class, strategy_run_id, strategy_name, strategy_desc, strategy_code,
                                    trading_user_id, mnemonic, run_start_time, config_id, instructions_t, orders_t,
                                    backtest_range, start_valid_date, end_valid_date, use_cache, sports,
                                    check_before_writing=False):
        """
        Write results of a backtest into mongo.

        By default write an entry of backtest_range even if we have no orders for that day.

        :param strategy_class
        :param strategy_run_id:
        :param strategy_name:
        :param strategy_desc:
        :param strategy_code:
        :param trading_user_id:
        :param mnemonic: mnemonic name of the run.
        :param run_start_time: type datetime
        :param config_id:
        :param instructions_t: [(update_dt, Instruction)]
        :param orders_t: [(update_dt, Order)]
        :param backtest_range: as returned from RunnableSimpleStrategyUnit.backtesting_date_ranges()
        :param start_valid_date: datetime.date
        :param end_valid_date: datetime.date make sure all the dates between these two have one entry, at least with
                               zero orders
        :param use_cache: use locally cached version of fetch methods
        :param check_before_writing: if True check that we are not going to write a second set of results
                                     for the same (strategy, trading_user_id, day, config_id). If that is going to
                                     happen a MongoDuplicateDayException is raised
        """

        if sports == [Sports.CRYPTO]:
            instructions_by_date, orders_by_date = self._get_instructions_and_orders_by_date_crypto(
                strategy_class, strategy_run_id, strategy_name, strategy_desc, strategy_code,
                trading_user_id, mnemonic, run_start_time, config_id, instructions_t, orders_t,
                backtest_range, start_valid_date, end_valid_date, use_cache, sports)
        else:
            instructions_by_date, orders_by_date = self._get_instructions_and_orders_by_date_sport(
                strategy_class, strategy_run_id, strategy_name, strategy_desc, strategy_code,
                trading_user_id, mnemonic, run_start_time, config_id, instructions_t, orders_t,
                backtest_range, start_valid_date, end_valid_date, use_cache, sports)

        if check_before_writing:
            days_already_run = set(self.get_days_already_run(strategy_name, strategy_desc,
                                                             trading_user_id, strategy_code, mnemonic,
                                                             backtest_range,
                                                             config_id=str(config_id)))
            for single_date in daterange(start_valid_date, end_valid_date):
                if single_date.strftime('%Y-%m-%d') in days_already_run:
                    raise MongoDuplicateDayException("Failure while running subrange {} to {}"
                                                     "Day {} already present. Aborting. "
                                                     "No days have been written for this subrange.\n"
                                                     "strategy is config_id:{} {} {} {} {} "
                                                     "mnemonic:{} range_name{}".format(
                        start_valid_date, end_valid_date, single_date,
                        str(config_id), strategy_name, strategy_desc,
                        trading_user_id, strategy_code, mnemonic, backtest_range[3]
                    ))

        for single_date in daterange(start_valid_date, end_valid_date):
            instructions_data = instructions_by_date[single_date]
            orders_data = orders_by_date[single_date]
            self._logger.info(None, "Writing results for {}".format(single_date))

            self._write_backtest_result_to_db_one_day(str(strategy_run_id), strategy_name, strategy_desc,
                                                      strategy_code,
                                                      trading_user_id, mnemonic, run_start_time, single_date,
                                                      config_id,
                                                      instructions_data, orders_data, backtest_range)

    def _get_instructions_and_orders_by_date_sport(
            self, strategy_class, strategy_run_id, strategy_name, strategy_desc, strategy_code,
            trading_user_id, mnemonic, run_start_time, config_id, instructions_t, orders_t,
            backtest_range, start_valid_date, end_valid_date, use_cache, sports):
        fixtures_ids_by_date = {}
        for single_date in daterange(start_valid_date, end_valid_date):
            start_datetime = datetime.combine(single_date, datetime.min.time())
            end_datetime = datetime.combine(single_date, datetime.min.time()) + \
                           timedelta(days=1) - timedelta(milliseconds=1)
            fixtures = fetch_fixtures_ids(start_datetime, end_datetime, sports, use_cache)
            fixtures_ids_by_date[single_date] = set(f[1] for f in fixtures)

        orders_by_date = {}
        instructions_by_date = {}
        for single_date in daterange(start_valid_date, end_valid_date):
            allowed_instructions = [i for i in instructions_t if
                                    sticker_parts_from_sticker(i[1].sticker).scope[1] in fixtures_ids_by_date[
                                        single_date]]
            allowed_orders = [i for i in orders_t if
                              sticker_parts_from_sticker(i[1].sticker).scope[1] in fixtures_ids_by_date[single_date]]

            orders_by_date[single_date] = [strategy_class.get_dict_from_bet_states(o[1], o[0])
                                           for o in allowed_orders]
            instructions_by_date[single_date] = [strategy_class.get_dict_from_instruction(i[1], i[0])
                                                 for i in allowed_instructions]
        return instructions_by_date, orders_by_date

    def _get_instructions_and_orders_by_date_crypto(
            self, strategy_class, strategy_run_id, strategy_name, strategy_desc, strategy_code,
            trading_user_id, mnemonic, run_start_time, config_id, instructions_t, orders_t,
            backtest_range, start_valid_date, end_valid_date, use_cache, sports):

        orders_by_date = {}
        instructions_by_date = {}
        for single_date in daterange(start_valid_date, end_valid_date):
            allowed_instructions = [i for i in instructions_t if i[0].date() == single_date]
            allowed_orders = [i for i in orders_t if i[0].date() == single_date]

            orders_by_date[single_date] = [strategy_class.get_dict_from_bet_states(o[1], o[0])
                                           for o in allowed_orders]
            instructions_by_date[single_date] = [strategy_class.get_dict_from_instruction(i[1], i[0])
                                                 for i in allowed_instructions]
        return instructions_by_date, orders_by_date

    def delete_backtest_results(self, strategy_name, strategy_desc, trading_user_id, strategy_code, mnemonic,
                                start_date, end_date, range_name, config_id=None, strategy_run_id=None):
        """
        Delete the strategy backtest results for the selected range.

        :param strategy_name:
        :param strategy_desc:
        :param trading_user_id:
        :param strategy_code:
        :param mnemonic:
        :param start_date: type string '2017-02-26'
        :param end_date: type string '2017-02-25'
        :param range_name:
        :param config_id:
        :param strategy_run_id:
        :return: a pymongo.results.DeleteResult object
        """

        query_filter = {'strategy_name': strategy_name,
                        'strategy_desc': strategy_desc,
                        'trading_user_id': trading_user_id,
                        'strategy_code': strategy_code,
                        'mnemonic': mnemonic,
                        'range_name': range_name,
                        '$and': [
                            {'date': {'$gte': start_date}},
                            {'date': {'$lte': end_date}}
                        ],
                        }
        if strategy_run_id is not None:
            query_filter.update({'strategy_run_id': strategy_run_id})
        if config_id is not None:
            query_filter.update({'config_id': config_id})

        # valid with and without self._use_scalable_storage
        daily_results_ids = list()
        cursor = self.backtesting_conn[self._backtest_results_coll].find(query_filter)
        for daily_result in cursor:
            daily_results_ids.append(str(daily_result['_id']))
        cursor.close()

        # Delete strategy result before orders and instructions
        ret = self.backtesting_conn[self._backtest_results_coll].delete_many(query_filter)

        # Delete instructions and orders
        if len(daily_results_ids) > 0:
            instructions_filter = {'strategy_result_id': {'$in': daily_results_ids}}
            self.backtesting_conn[self._backtest_instructions_coll].delete_many(instructions_filter)
            self.backtesting_conn[self._backtest_orders_coll].delete_many(instructions_filter)

        return ret

    def delete_backtest_results_by(self, strategy_name=None, strategy_desc=None, strategy_code=None, mnemonic=None,
                                   trading_user_id=None, force=False):
        """
        Delete all strategy backtest results for the selected mnemonic.
        """

        query_filter = {}
        if strategy_name is not None:
            query_filter.update({'strategy_name': strategy_name})
        if strategy_desc is not None:
            query_filter.update({'strategy_desc': strategy_desc})
        if strategy_code is not None:
            query_filter.update({'strategy_code': strategy_code})
        if mnemonic is not None:
            query_filter.update({'mnemonic': mnemonic})
        if trading_user_id is not None:
            query_filter.update({'trading_user_id': trading_user_id})

        if query_filter == {}:
            raise ValueError("Nothing selected")

        # valid with and without self._use_scalable_storage
        daily_results_ids = list()
        cursor = self.backtesting_conn[self._backtest_results_coll].find(query_filter)
        for daily_result in cursor:
            daily_results_ids.append(str(daily_result['_id']))
        cursor.close()
        self._logger.info(None, "found {} entries".format(len(daily_results_ids)))
        if len(daily_results_ids) > 5000 and not force:
            raise ValueError("Are you sure you want to delete {} days? use force=True".format(len(daily_results_ids)))

        # Delete scalable storage instructions and orders
        if len(daily_results_ids) > 0:
            instructions_filter = {'strategy_result_id': {'$in': daily_results_ids}}
            a = self.backtesting_conn[self._backtest_instructions_coll].delete_many(instructions_filter)
            self._logger.info(None, "Deleted {} scalable instructions".format(a.deleted_count))
            a = self.backtesting_conn[self._backtest_orders_coll].delete_many(instructions_filter)
            self._logger.info(None, "Deleted {} scalable orders".format(a.deleted_count))

        # Delete strategy result before orders and instructions
        ret = self.backtesting_conn[self._backtest_results_coll].delete_many(query_filter)

        return ret

    def _get_order_and_instructions_docs(self, strategy_run_id_str, strategy_name, strategy_desc,
                                         trading_user_id, strategy_code, mnemonic, run_start_time, dt,
                                         config_id, instructions_data, orders_data, backtest_range,
                                         strategy_result_id):
        if not self._use_scalable_storage:
            raise ValueError("Only meaningful with scalable storage")

        for o in orders_data + instructions_data:
            o.update({'strategy_result_id': str(ObjectId(str(strategy_result_id)))})

        return orders_data, instructions_data

    def _write_backtest_result_to_db_one_day(self, strategy_run_id_str, strategy_name, strategy_desc, strategy_code,
                                             trading_user_id, mnemonic, run_start_time, dt, config_id,
                                             instructions_data, orders_data, backtest_range):
        """
        Write backtest result entry. Each entry refer to a single strategy_run and a single day.

        Write all the instructions placed in a day, and all the orders from those instructions, even if they are placed
        the next day.

        Each entry refer to a specific [strategy_run_id, date]
        :param dt: type Date
        :return:
        """
        # TODO when running with strategy_desc 'all' we might want to split by actual strategy_desc
        if not self._use_scalable_storage:

            doc = self._backtest_result_to_mongo_document(strategy_run_id_str, strategy_name, strategy_desc,
                                                          trading_user_id, strategy_code, mnemonic, run_start_time, dt,
                                                          config_id, instructions_data, orders_data, backtest_range,
                                                          use_scalable_storage=self._use_scalable_storage)

            self._logger.info(None, "Writing results for {}: i {}, o {}".format(
                dt, len(doc['instructions']), len(doc['orders'])))

            self.backtesting_conn[self._backtest_results_coll].insert_one(doc)
        else:
            doc = self._backtest_result_to_mongo_document(strategy_run_id_str, strategy_name, strategy_desc,
                                                          trading_user_id, strategy_code, mnemonic, run_start_time, dt,
                                                          config_id, instructions_data, orders_data, backtest_range,
                                                          use_scalable_storage=self._use_scalable_storage)

            self._logger.info(None, "Writing results for {}: i {}, o {}".format(
                dt, doc['n_instructions'], doc['n_orders']))

            res = self.backtesting_conn[self._backtest_results_coll].insert_one(doc)

            order_docs, instruction_docs = \
                self._get_order_and_instructions_docs(strategy_run_id_str, strategy_name,
                                                      strategy_desc,
                                                      trading_user_id, strategy_code, mnemonic, run_start_time, dt,
                                                      config_id, instructions_data, orders_data, backtest_range,
                                                      strategy_result_id=res.inserted_id)

            if len(order_docs) > 0:
                self.backtesting_conn[self._backtest_orders_coll].insert_many(order_docs)
            if len(instruction_docs) > 0:
                self.backtesting_conn[self._backtest_instructions_coll].insert_many(instruction_docs)

    def _load_orders_pickled(self, start, end, file_dir, strategy_name=None, strategy_desc=None, trading_user_id=None):
        files = os.listdir(file_dir)
        existing_dates = [f.split('.')[0] for f in files]

        for d in daterange(start.date(), end.date()):
            if str(d) not in existing_dates:
                orders = self.get_prod_orders_between_datetimes(datetime.combine(d, datetime.min.time()),
                                                                datetime.combine(d, datetime.max.time()))
                if d < datetime.now(tz=pytz.utc).date() - timedelta(days=2):
                    # Storing last 2 days is unsafe because they might still be unsettled
                    self._store_orders_pickled(file_dir, orders, [d])

        self._logger.info(None, "Loading {} days of orders {} to {}...".format((end - start).days, start, end))
        orders = []
        for d in daterange(start.date(), end.date()):
            filename = str(d) + ".pickle"
            file_path = os.path.join(file_dir, filename)
            self._logger.info(None, "Loading {}...".format(file_path))
            if d < datetime.now(tz=pytz.utc).date() - timedelta(days=2):
                with open(file_path, 'rb') as handle:
                    loaded = pickle.load(handle)
            else:
                loaded = self.get_prod_orders_between_datetimes(datetime.combine(d, datetime.min.time()),
                                                                datetime.combine(d, datetime.max.time()))
            loaded = [o for o in loaded if start <= o['placed_time'] <= end]
            if strategy_name is not None:
                loaded = [o for o in loaded if 'strategy' in o and o['strategy'] == strategy_name]
            if strategy_desc is not None:
                loaded = [o for o in loaded if 'strategy_descr' in o and o['strategy_descr'] == strategy_desc]
            if trading_user_id is not None:
                loaded = [o for o in loaded if 'trading_user_id' in o and o['trading_user_id'] == trading_user_id]
            orders.extend(loaded)
        self._logger.info(None, "Loading Done")
        return orders

    def _store_orders_pickled(self, file_dir, orders, days):
        """

        :param file_dir:
        :param orders:
        :param days: create empty files for these days
        :return:
        """
        self._logger.info(None, "Storing orders {}...".format(len(orders)))
        orders_by_date = defaultdict(list)
        for o in orders:
            orders_by_date[o['placed_time'].date()].append(o)
        for day in days:
            if day not in orders_by_date:
                orders_by_date[day] = []

        for k, v in orders_by_date.iteritems():
            filename = str(k) + ".pickle"
            file_path = os.path.join(file_dir, filename)
            if os.path.exists(file_path):
                continue
            with open(file_path, 'wb') as handle:
                pickle.dump(v, handle, protocol=pickle.HIGHEST_PROTOCOL)
            self._logger.info(None, "Written {} into {}".format(len(v), file_path))
        self._logger.info(None, "Storing done")

    def _get_prod_orders_between_datetimes_cached(self, start, end, strategy_name=None, strategy_desc=None,
                                                  trading_user_id=None, cache_dir=None):
        """

        Retrieve production orders placed between start and end. Orders are cached locally in files under ~/.mongocache
        """
        if cache_dir is None:
            cache_dir = os.path.expanduser('~/.mongocache')

        file_dir = "prod_orders"
        file_dir = os.path.join(cache_dir, file_dir)
        if not os.path.exists(file_dir):
            os.makedirs(file_dir)

        orders = self._load_orders_pickled(start, end, file_dir, strategy_name=strategy_name,
                                           strategy_desc=strategy_desc,
                                           trading_user_id=trading_user_id)

        return orders

    def get_prod_orders_between_datetimes(self, start, end, strategy_name=None, strategy_desc=None,
                                          trading_user_id=None, use_cache=False):
        """
        Returns all the orders placed between start and end.

        If strategy_name and strategy_desc are specified, limit the search to that strategy.

        PRod orders have no strategy_code
        :param start: type datetime.datetime
        :param end: type datetime.datetime
        :param strategy_name:
        :param strategy_desc:
        :param trading_user_id:
        :param use_cache: cache locally in files the orders
        :return:
        """
        if use_cache:
            return self._get_prod_orders_between_datetimes_cached(start, end, strategy_name, strategy_desc,
                                                                  trading_user_id)
        self._logger.info(None, "Fetching orders between {} and {}...".format(start, end))
        if not isinstance(start, datetime) or not isinstance(end, datetime):
            raise ValueError("Input is not datetime")

        query = {'$and': [
            {'placed_time': {'$gte': start}},
            {'placed_time': {'$lte': end}}
        ],
            # 'source': 'trading_system'  # Only fetch automatically placed bets
        }
        if strategy_name is not None:
            query.update({'strategy': strategy_name})
        if strategy_desc is not None:
            query.update({'strategy_descr': strategy_desc})
        if trading_user_id is not None:
            query.update({'trading_user_id': ObjectId(str(trading_user_id))})

        cursor = self.trading_prod_conn[self._orders_coll].find(query)
        orders = [o for o in cursor]
        cursor.close()
        self._logger.info(None, "Fetching done")
        return orders

    def get_prod_instructions_between_datetimes(self, start, end, strategy_name=None, strategy_desc=None,
                                                strategy_code=None, trading_user_id=None, algo_ids=None):
        """
        Returns all the instructions placed between start and end.

        If strategy_name and strategy_desc are specified, limit the search to that strategy.
        :param start: type datetime.datetime
        :param end: type datetime.datetime
        :param strategy_name:
        :param strategy_desc:
        :param strategy_code:
        :param trading_user_id:
        :param algo_ids: list
        :return:
        """

        if not isinstance(start, datetime) or not isinstance(end, datetime):
            raise ValueError("Input is not datetime")

        query = {'$and': [
            {'placed_time': {'$gte': start}},
            {'placed_time': {'$lte': end}}
        ],
            # 'source': 'trading_system',  # Only fetch automatically placed bets
        }
        if strategy_name is not None:
            query.update({'strategy': strategy_name})
        if strategy_desc is not None:
            query.update({'strategy_descr': strategy_desc})
        if strategy_code is not None:
            query.update({'details.strategy_code': strategy_code})
        if trading_user_id is not None:
            query.update({'trading_user_id': ObjectId(str(trading_user_id))})

        if algo_ids is not None:
            query.update({'algo_details.id': {'$in': algo_ids}})

        instructions = [o for o in self.trading_prod_conn[self._instructions_coll].find(query)]
        return instructions

    def get_prod_instructions_between_fixture_datetimes(self, start_dt, end_dt, strategy_name=None, strategy_desc=None,
                                                        strategy_code=None, trading_user_id=None, algo_ids=None):
        """
        Returns all the instructions placed for fixture with kickoff between start and end.

        If strategy_name and strategy_desc are specified, limit the search to that strategy.
        :param start: type datetime.datetime
        :param end: type datetime.datetime
        :param strategy_name:
        :param strategy_desc:
        :param strategy_code:
        :param trading_user_id:
        :param algo_ids: list
        :return:
        """

        if not isinstance(start_dt, datetime) or not isinstance(end_dt, datetime):
            raise ValueError("Input is not datetime")
        expanded_start_dt = start_dt - timedelta(days=6)
        expanded_end_dt = end_dt + timedelta(days=2)

        query = {'$and': [
            {'placed_time': {'$gte': expanded_start_dt}},
            {'placed_time': {'$lte': expanded_end_dt}}
        ],
            # 'source': 'trading_system',  # Only fetch automatically placed bets
        }
        if strategy_name is not None:
            query.update({'strategy': strategy_name})
        if strategy_desc is not None:
            query.update({'strategy_descr': strategy_desc})
        if strategy_code is not None:
            query.update({'details.strategy_code': strategy_code})
        if trading_user_id is not None:
            query.update({'trading_user_id': ObjectId(str(trading_user_id))})

        if algo_ids is not None:
            query.update({'algo_details.id': {'$in': algo_ids}})

        instructions = []
        for i_doc in self.trading_prod_conn[self._instructions_coll].find(query):
            _, (_, event_id), _, _, _ = parse_sticker(i_doc['sticker'])
            kickoff = self._fixture_cache.get_kickoff(event_id)
            if start_dt <= kickoff <= end_dt:
                instructions.append(i_doc)
            else:
                pass

        return instructions

    def get_prod_instructions_and_orders_between_fixture_datetimes(
            self, start_dt, end_dt, strategy_name=None, strategy_desc=None,
            strategy_code=None,
            trading_user_id=None, algo_ids=None):
        """
        Returns all the instructions placed for fixture with kickoff between start and end.
        """

        instructions = self.get_prod_instructions_between_fixture_datetimes(
            start_dt, end_dt,
            strategy_name=strategy_name, strategy_desc=strategy_desc, strategy_code=strategy_code,
            trading_user_id=trading_user_id, algo_ids=algo_ids)
        instruction_ids = [i['id'] for i in instructions]
        orders = self.get_prod_orders_by_instruction_ids(instruction_ids)

        return instructions, orders

    def get_prod_orders_by_instruction_ids(self, instruction_ids):
        query = {
            'instruction_id': {'$in': instruction_ids},
        }

        orders = [o for o in self.trading_prod_conn[self._orders_coll].find(query)]
        return orders

    def get_prod_instructions_by_ids(self, instruction_ids):
        """
        Returns all the instructions placed between start and end.

        If strategy_name and strategy_desc are specified, limit the search to that strategy.
        :param instruction_ids: type list
        :return:
        """

        query = {
            'id': {'$in': instruction_ids},
        }

        instructions = [o for o in self.trading_prod_conn[self._instructions_coll].find(query)]
        return {str(i['id']): i for i in instructions}

    def get_backtest_result_multiple_days(self, strategy_name, strategy_desc, trading_user_id, strategy_code,
                                          start_date, end_date, mnemonic=None, range_name=None, strategy_run_id=None,
                                          config_id=None, settled_only=False):
        """
        Return backtest result for multiple days.

        Each day of results contains all the orders and instructions for fixture starting on that day.

        Raise an exception if more than one results are returned, it should never happen.
        :param start_date: string like '2017-12-30'
        :param end_date: string like '2017-12-30'
        :param strategy_name:
        :param strategy_desc:
        :param trading_user_id
        :param strategy_code
        :param mnemonic
        :param range_name
        :param strategy_run_id: string
        :param config_id: string
        :return: one mongo document or None if not present
        """
        self._logger.info(None,
                          "Fetching {} days of backtesting results ({} {} {} {} {} config_id {}, strategy_run_id {})...".format(
                              (datetime.strptime(end_date, "%Y-%m-%d") - datetime.strptime(start_date,
                                                                                           "%Y-%m-%d")).days + 1,
                              strategy_name, strategy_desc, trading_user_id, strategy_code, mnemonic, config_id,
                              strategy_run_id))

        query = {'trading_user_id': trading_user_id,
                 '$and': [{'date': {'$gte': start_date}},
                          {'date': {'$lte': end_date}}]
                 }
        if strategy_name is not None:
            query.update({'strategy_name': strategy_name})
        if strategy_desc is not None:
            query.update({'strategy_desc': strategy_desc})
        if mnemonic is not None:
            query.update({'mnemonic': mnemonic})
        if range_name is not None:
            query.update({'range_name': range_name})
        if strategy_run_id is not None:
            query.update({'strategy_run_id': strategy_run_id})
        if strategy_code is not None:
            query.update({'strategy_code': strategy_code})
        if config_id is not None:
            query.update({'config_id': config_id})

        orders = []
        instructions = []
        backtests_cursor = self.backtesting_conn[
            self._backtest_results_coll].find(
            query, no_cursor_timeout=True)

        config_ids = set()
        config_ids_by_day = defaultdict(list)
        daily_results_ids = []
        for r in backtests_cursor:
            config_ids.add(r['config_id'])
            config_ids_by_day[r['date']].append(r['config_id'])
            if len(config_ids_by_day[r['date']]) > 1:
                raise ValueError("Two config_id loaded for the same days: {} has {}".format(
                    r['date'], config_ids_by_day[r['date']]))
            scalable_storage = 'n_orders' in r
            if scalable_storage:
                daily_results_ids.append(str(r['_id']))
            else:
                if 'orders' in r.keys():
                    orders.extend(r['orders'])
                if 'instructions' in r.keys():
                    instructions.extend(r['instructions'])
        backtests_cursor.close()
        self._logger.info(None, "Loaded {} different config_id".format(len(config_ids)))

        if len(daily_results_ids) > 0:
            instruction_filter = {
                'strategy_result_id': {'$in': daily_results_ids}
            }
            if settled_only:
                instruction_filter.update({'status': int(InstructionStatus.CLOSED)})

            order_filter = {
                'strategy_result_id': {'$in': daily_results_ids}
            }
            if settled_only:
                order_filter.update({'status': int(OrderStatus.SETTLED)})

            orders += [o for o in self.backtesting_conn[self._backtest_orders_coll].find(order_filter)]
            instructions += [o for o in
                             self.backtesting_conn[self._backtest_instructions_coll].find(instruction_filter)]

        self._logger.info(None, "Loaded {} instructions and {} orders".format(len(instructions), len(orders)))
        return instructions, orders

    def get_strategy_runs(self, strategy_name=None, strategy_desc=None, strategy_code=None, trading_user_id=None,
                          start_datetime=None, end_datetime=None, mnemonic=None, env=None, config_id=None, is_prod=None,
                          is_optimization=None, is_backtest=None, id=None):
        """
        Returns all the strategy runs between two datetimes.
        :return:
        """

        if id is not None:
            query = {'_id': ObjectId(str(id))}
        else:
            query = {}
            and_list = []
            if strategy_name is not None:
                query.update({'strategy_name': strategy_name})
            if strategy_desc is not None:
                query.update({'strategy_desc': strategy_desc})
            if strategy_code is not None:
                query.update({'strategy_code': strategy_code})
            if trading_user_id is not None:
                query.update({'trading_user_id': trading_user_id})
            if start_datetime is not None:
                and_list.append({'start_time': {'$gte': start_datetime}})
            if end_datetime is not None:
                and_list.append({'start_time': {'$lte': end_datetime}})
            if mnemonic is not None:
                query.update({'mnemonic': mnemonic})
            if env is not None:
                query.update({'env': env})
            if config_id is not None:
                query.update({'config_id': config_id})
            if is_prod is not None:
                query.update({'is_prod': is_prod})
            if is_optimization is not None:
                query.update({'is_optimization': is_optimization})
            if is_backtest is not None:
                query.update({'is_backtest': is_backtest})
            if strategy_code is not None:
                query.update({'strategy_code': strategy_code})

            if and_list:
                query.update({'$and': and_list})

        results = [o for o in self.realtime_conn[self._strategy_runs_coll].find(query)]

        return results

    def get_strategy_runs_between(self, start_datetime, end_datetime, is_realtime=None):
        """
        Returns all the strategy runs between two datetimes.
        :param is_realtime: if True fetch realtime only; if False backtest only; if None fetch both;
        :return:
        """

        query = {'$and': [
            {'start_time': {'$gte': start_datetime}},
            {'start_time': {'$lte': end_datetime}}],
        }
        if is_realtime is None:
            pass
        elif is_realtime:
            query.update({'env': {'$ne': 'backtest'}})
        else:
            query.update({'env': 'backtest'})

        results = [o for o in self.realtime_conn[self._strategy_runs_coll].find(query)]

        return sorted(results, key=lambda x: x['start_time'])

    def write_backtest_template_signals(self, strategy_run_id, strategy_name, strategy_desc, strategy_code,
                                        trading_user_id, mnemonic, config_id, signals_wrappers):
        """
        Ignore if the signals have already been stored
        :param signals_wrappers: [(timestamp, signal_wrapper)]

        signal_wrapper is like
        {
        'mnemonic': 'lorenzo_test',
        'config_id': '5a7993765f3ced5893d93617',
        'env': 'backtest',
        'strategy_run_id': '5a7994095f3ced5b828e583a'
        'signals': [{'is_back': True, 'name': 'BetSignal', 'event_id': u'ENP2285782',
                     'timestamp': '2016-11-02 17:00:00+00:00', 'sticker': u'BB-EENP2285782-FT12-1.BF',
                     'strategy_code': 'euro_test', 'value': -0.381, 'odds': 1.1, 'strategy_name':
                     'bball_pbp', 'strategy_desc': 'bball_pbp', 'id': '5a79941f5f3ced5b828e583b',
                     'signal_generator_name': 'HistoricalBasketballModelSignalGenerator'},
                    {'is_back': True, 'name': 'BetSignal', 'event_id': u'ENP2285783',
                     'timestamp': '2016-11-02 17:00:00+00:00', 'sticker': u'BB-EENP2285783-FTTP-U-158_5.BF',
                     'strategy_code': 'euro_test', 'value': -0.3, 'odds': 1.731585, 'strategy_name':
                     'bball_pbp', 'strategy_desc': 'bball_pbp', 'id': '5a79941f5f3ced5b828e583c',
                     'signal_generator_name': 'HistoricalBasketballModelSignalGenerator'}],
        }
        :param strategy_name:
        :param strategy_desc: desc of the strategy invocation (can be 'All' e.g. football analyst)
        :param strategy_code:
        :return:
        """
        num_signals = 0

        requests = list()
        nowtime = datetime.now(tz=pytz.utc)
        timestamp = None
        for timestamp, signals_wrapper in signals_wrappers:
            num_signals += len(signals_wrapper['signals'])
            docs = self._get_signal_mongo_docs(timestamp, nowtime, signals_wrapper,
                                               trading_user_id, mnemonic, config_id,
                                               strategy_name=strategy_name, strategy_desc=strategy_desc,
                                               strategy_code=strategy_code)
            for doc in docs:
                requests.append(InsertOne(doc))

        self._logger.info(None, "Writing signals : s {} w {} [{}, {}, {}, {}] {}".format(
            num_signals, len(signals_wrappers), strategy_name, strategy_desc, strategy_code, trading_user_id,
            timestamp))
        if len(requests):
            try:

                self.backtesting_conn[self._template_signals_coll].bulk_write(requests, ordered=False)
            except BulkWriteError:
                # catch duplicated _id
                pass

    def write_realtime_template_signals(self, strategy_run_id, strategy_name, strategy_desc, strategy_code,
                                        trading_user_id, mnemonic, config_id, signals):
        self.write_backtest_template_signals(strategy_run_id, strategy_name, strategy_desc, strategy_code,
                                             trading_user_id, mnemonic, config_id, signals)

    @staticmethod
    def _signal_from_signal_wrapper(signal_wrapper):
        """
        :return (datetime, dict): (datetime when it was generated, signal_representation)
        """
        return signal_wrapper['timestamp'], signal_wrapper['signal_repr']

    def get_template_signals_from_run_id(self, strategy_run_id):
        """

        :param strategy_run_id:
        :return: [(timestamp, signal_repr)]
        """
        query = {'strategy_run_id': strategy_run_id}

        cursor = self.backtesting_conn[self._template_signals_coll].find(query, no_cursor_timeout=True)

        signals = [self._signal_from_signal_wrapper(s) for s in cursor]
        cursor.close()
        return signals

    def get_template_signals(self, strategy_name, strategy_desc, strategy_code, mnemonic, trading_user_id=None,
                             start_date=None, end_date=None, strategy_run_id=None, config_id=None, env=None):
        """
        Return template signals.

        :param start_date: datetime.Date()
        :param end_date: datetime.Date()
        """
        self._logger.info(None, "Fetching signals ({} {} {} {} {} strategy_run_id {} config_id {})...".format(
            strategy_name, strategy_desc, trading_user_id, strategy_code, mnemonic, strategy_run_id, config_id))

        query = {'strategy_name': strategy_name,
                 'strategy_desc': strategy_desc,
                 'mnemonic': mnemonic,
                 }
        if strategy_code is not None:
            query.update({'strategy_code': strategy_code})
        if config_id is not None:
            query.update({'config_id': config_id})
        if strategy_run_id is not None:
            query.update({'strategy_run_id': strategy_run_id})
        if env is not None:
            query.update({'env': env})
        if trading_user_id is not None:
            query.update({'trading_user_id': trading_user_id})

        cursor = self.backtesting_conn[self._template_signals_coll].find(query, no_cursor_timeout=True)

        if start_date:
            start_dt = datetime.combine(start_date, datetime.min.time()).replace(tzinfo=pytz.utc)
        if end_date:
            end_dt = datetime.combine(end_date, datetime.max.time()).replace(tzinfo=pytz.utc)

        def accept_start(signal):
            return start_dt <= signal['kickoff_datetime']

        def accept_end(signal):
            return signal['kickoff_datetime'] <= end_dt

        def accept_both(signal):
            return start_dt <= signal['kickoff_datetime'] <= end_dt

        def accept_all(signal):
            return True

        if start_date and end_date:
            accept_f = accept_both
        elif start_date:
            accept_f = accept_start
        elif end_date:
            accept_f = accept_end
        else:
            accept_f = accept_all

        signals = [self._signal_from_signal_wrapper(s) for s in cursor if accept_f(s)]
        cursor.close()
        return signals

    def _get_signal_mongo_docs(self, timestamp, nowtime, signals_wrapper, trading_user_id, mnemonic, config_id,
                               strategy_name=None, strategy_desc=None, strategy_code=None):
        """

        :param timestamp: datetime at which the signal was emitted
        :param nowtime: time of now, to be used in update_dt
        :param signals_wrapper: format is like, (contains multiple signals for the same timestamp)
        {
        'mnemonic': 'lorenzo_test',
        'config_id': '5a7993765f3ced5893d93617',
        'strategy_code': 'euro_test',
        'env': 'backtest',
        'strategy_run_id': '5a7994095f3ced5b828e583a',
        'signals': [{'is_back': True, 'name': 'BetSignal', 'event_id': u'ENP2285782',
                     'timestamp': '2016-11-02 17:00:00+00:00', 'sticker': u'BB-EENP2285782-FT12-1.BF',
                     'strategy_code': 'euro_test', 'value': -0.381, 'odds': 1.1, 'strategy_name':
                     'bball_pbp', 'strategy_desc': 'bball_pbp', 'id': '5a79941f5f3ced5b828e583b',
                     'signal_generator_name': 'HistoricalBasketballModelSignalGenerator'},
                    {'is_back': True, 'name': 'BetSignal', 'event_id': u'ENP2285783',
                     'timestamp': '2016-11-02 17:00:00+00:00', 'sticker': u'BB-EENP2285783-FTTP-U-158_5.BF',
                     'strategy_code': 'euro_test', 'value': -0.3, 'odds': 1.731585, 'strategy_name':
                     'bball_pbp', 'strategy_desc': 'bball_pbp', 'id': '5a79941f5f3ced5b828e583c',
                     'signal_generator_name': 'HistoricalBasketballModelSignalGenerator'}],
        }
        :param trading_user_id: string
        :param mnemonic:  string
        :param config_id: string
        :return:
        """
        docs = []

        for signal in signals_wrapper['signals']:
            event_id = signal['event_id']
            if event_id not in self._start_times.keys():
                eid = int(event_id[3:])
                if self._sports is None:
                    raise ValueError("Need to set the sport")
                for sport in self._sports:
                    matches = get_match_info_single_sport(sport, eid)
                    for match in matches:
                        kick_off = match['start_date']
                        self._start_times[str(event_id)] = parser.parse(kick_off).replace(tzinfo=pytz.utc)

            docs.append({
                '_id': ObjectId(signal['id']),
                'strategy_run_id': signals_wrapper['strategy_run_id'],
                'signal_repr': signal,
                'update_dt': nowtime,
                'timestamp': timestamp,
                'kickoff_datetime': self._start_times[event_id],

                # Following informations are not necessary but nice to have
                'strategy_name': signal.get('strategy_name', None) or strategy_name,
                'strategy_desc': signal.get('strategy_desc', None) or strategy_desc,
                'strategy_code': signal.get('strategy_code', None) or strategy_code,
                'trading_user_id': trading_user_id,
                'mnemonic': mnemonic,
                'config_id': config_id,
                'env': signals_wrapper['env'],
            })
        return docs

    def delete_signals_from_run_id(self, strategy_run_id):
        query_filter = {'strategy_run_id': strategy_run_id}
        ret = self.backtesting_conn[self._template_signals_coll].delete_many(query_filter)
        return ret

    def delete_signals(self, strategy_name, strategy_desc, trading_user_id, strategy_code, mnemonic,
                       start_date=None, end_date=None, config_id=None):
        """
        Delete all the signals which refers to events with kickoff time between start_date and end_date

        :param strategy_name:
        :param strategy_desc:
        :param trading_user_id:
        :param strategy_code:
        :param mnemonic:
        :param start_date: datetime.Date
        :param end_date: datetime.Date
        :param config_id:
        :return: a pymongo.results.DeleteResult object
        """
        dt_queries = []

        if start_date:
            start_dt = datetime.combine(start_date, datetime.min.time()).replace(tzinfo=pytz.utc)
            dt_queries.append({'kickoff_datetime': {'$gte': start_dt}})
        if end_date:
            end_dt = datetime.combine(end_date, datetime.max.time()).replace(tzinfo=pytz.utc)
            dt_queries.append({'kickoff_datetime': {'$lte': end_dt}})

        query_filter = {'strategy_name': strategy_name,
                        'strategy_desc': strategy_desc,
                        'trading_user_id': trading_user_id,
                        'strategy_code': strategy_code,
                        'mnemonic': mnemonic,
                        }
        if config_id is not None:
            query_filter.update({'config_id': config_id})
        if len(dt_queries):
            query_filter.update({'$and': dt_queries})

        # Delete strategy result before orders and instructions
        ret = self.backtesting_conn[self._template_signals_coll].delete_many(query_filter)
        self._logger.info(None, "Deleted {} signals".format(ret.deleted_count))
        return ret


def main():
    from datetime import datetime

    helper = MongoStrategyHelper(sports=[Sports.TENNIS])
    # helper.get_strategy_runs(strategy_name='template', strategy_desc='bball_pbp',
    #                          strategy_code='nba_test', trading_user_id='552f6a9139fdca41ca28b01a',
    #                          start_datetime=datetime(2016, 12, 1), end_datetime=datetime(2016, 12, 5), is_backtest=True)

    # signals = [(datetime(2017, 6, 29, 0, 0, 9, 484000, tzinfo=pytz.utc),
    #             {'mnemonic': 'lorenzo_test', 'strategy_run_id': '5a662b425f3ced09e4e15f02',
    #              'timestamp': '2017-06-29 00:00:09.484000+00:00',
    #              'env': 'PROD',
    #              'signals': [
    #                  {'is_back': True, 'name': 'BetSignal', 'timestamp': '2017-06-29 00:00:09.484000+00:00',
    #                   'sticker': u'T-EENP2543228-FT12-A', 'strategy_code': None, 'value': -1, 'odds': 1.2,
    #                   'strategy_name': 'dummy_template', 'strategy_desc': 'crazy',
    #                   'id': '5a662b485f3ced09e4e15f03',
    #                   'event_id': 'ENP2543228',
    #                   'signal_generator_name': 'DummySignalGenerator'},
    #                  {'is_back': True, 'name': 'BetSignal', 'timestamp': '2017-06-29 00:00:09.484000+00:00',
    #                   'sticker': u'T-EENP2543228-FT12-A', 'strategy_code': None, 'value': -1, 'odds': 1.2,
    #                   'strategy_name': 'dummy_template', 'strategy_desc': 'crazy',
    #                   'id': '5a662b485f3ced09e4e15f01',
    #                   'event_id': 'ENP2543228',
    #                   'signal_generator_name': 'DummySignalGenerator'}]
    #              })
    #            ]
    #
    # strategy_run_id = '5a662b425f3ced09e4e15f02'
    # strategy_name = 'dummy_template'
    # strategy_desc = 'crazy'
    # strategy_code = None
    # trading_user_id = 'trading_user_id'
    # mnemonic = 'mnemonic'
    # config_id = '02020202'
    #
    # helper.write_backtest_template_signals(strategy_run_id, strategy_name, strategy_desc, strategy_code,
    #                                        trading_user_id, mnemonic, config_id, signals)
    # helper.write_backtest_template_signals(strategy_run_id, strategy_name, strategy_desc, strategy_code,
    #                                        trading_user_id, mnemonic, config_id, signals)
    #
    # # signals = helper.get_template_signals_from_run_id(strategy_run_id)
    #
    # signals = helper.get_template_signals(strategy_name, strategy_desc, strategy_code,
    #                                       mnemonic,
    #                                       # trading_user_id=trading_user_id,
    #                                       strategy_run_id=None,
    #                                       config_id=None,
    #                                       start_date=date(2016, 1, 1),
    #                                       end_date=date(2018, 1, 23),
    #                                       env='PROD'
    #                                       )
    #
    # # delete = helper.delete_signals(strategy_name, strategy_desc, trading_user_id, strategy_code, mnemonic,
    # #                                start_date=date(2016, 1, 1),
    # #                                end_date=date(2018, 1, 23),
    # #                                config_id=None)
    #
    # # delete = helper.delete_signals_from_run_id('5a68c1125f3ced18958356d6')
    #
    #
    # print signals
    mnemonics = [
        "arthur",
        "arthur121",
        "arthur23",
        "arthur24",
    ]

    for mnemonic in mnemonics:
        ret = helper.delete_backtest_results_by(mnemonic=mnemonic)
        print "deleted {} for mnemonic {}".format(ret.deleted_count, mnemonic)


if __name__ == '__main__':
    main()
