import json
import sys

from lib.team import Team

if 'lib.match' not in sys.modules:
    from lib.match import Match


class User:
    def __init__(self, idt=None, name=None, email=None, phone=None):
        self.id = idt
        self.name = name
        self.email = email
        self.phone = phone
        self.role = 0
        self.notifications = None
        self.records = None

        self.id_league = 0
        self.id_team = 0

        self.league_bets = None
        self.league_bets_matches = None
        self.league_points = 0
        self.league_rank = 0

        self.europe = 0
        self.europe_round = None
        self.europe_scores = None
        self.europe_scorers = None
        self.europe_bets_matches = None

        self.bob_rank = 0
        self.bob_points = 0
        self.bob_details = None

    def set_league_points(self, bet_mday, points, db):
        stmt = """
        UPDATE users_month
        SET league_points_{} = league_points_{} + :points, league_points_total = league_points_total + :points
        WHERE id = :id
        """.format(bet_mday, bet_mday)
        args = {'points': points, 'id': self.id}
        db.exec(stmt, args)

    def set_europe_points(self, europe_round, points, db):
        stmt = """
        UPDATE users_month
        SET europe_points_{} = europe_points_{} + :points
        WHERE id = :id
        """.format(europe_round, europe_round)
        args = {'points': points, 'id': self.id}
        db.exec(stmt, args)

    def set_league_bets(self, id_month, db):
        stmt = """
        UPDATE users_month 
        SET league_bets_1 = :bets_1, league_bets_2 = :bets_2, league_bets_3 = :bets_3, league_bets_4 = :bets_4
        WHERE id_user = :id_user AND id_month = :id_month
        """
        args = {'bets_{}'.format(mday): ''.join(self.league_bets[mday]) for mday in range(1, 5)}
        args.update({'id_user': self.id, 'id_month': id_month})
        db.exec(stmt, args)

    def set_europe_bets(self, id_month, db):
        stmt = """
        UPDATE users_month 
        SET europe_scores_{round} = :bet_scores, europe_scorers_{round} = :bet_scorers
        WHERE id_user = :id_user AND id_month = :id_month
        """.format(round=self.europe_round)
        args = {
            'bet_scores': json.dumps(self.europe_scores), 'bet_scorers': json.dumps(self.europe_scorers),
            'id_user': self.id, 'id_month': id_month
        }
        db.exec(stmt, args)

    def store_records(self, db):
        stmt = """
        UPDATE users
        SET records = :records
        WHERE id = :id
        """
        args = {'records': json.dumps(self.records), 'id': self.id}
        db.exec(stmt, args)

    def store_bob_rank(self, db):
        stmt = """
        UPDATE users
        SET bob_rank_old = bob_rank, bob_rank = :bob_rank, bob_points = :bob_points, bob_details = :bob_details
        WHERE id = :id
        """
        args = {
            'bob_rank': self.bob_rank, 'bob_points': self.bob_points, 'bob_details': json.dumps(self.bob_details),
            'id': self.id
        }
        db.exec(stmt, args)

    @staticmethod
    def get_users(db):
        for row in db.query('SELECT id, username, email, phone FROM users'):
            yield User(idt=row['id'], name=row['username'], email=row['email'], phone=row['phone'])

    @staticmethod
    def get_users_league_bets(db, interval_day=1, admin=False):
        stmt = """
        SELECT id_user, league_bets_1, league_bets_2, league_bets_3, league_bets_4, username, role, notifications,
        bets.id_league, bets.id_month, bets.mday, bets.league_id, bets.league_mday,
        matches.id AS id_match, matches.idof10, matches.start_date, matches.id_home, matches.id_away,
        home.rank AS home_rank, home.coeff AS home_coeff, away.rank AS away_rank, away.coeff AS away_coeff
        FROM users_month
        INNER JOIN users ON users.id = users_month.id_user
        INNER JOIN bets ON bets.id_league = users_month.id_league AND bets.id_month = users_month.id_month
        INNER JOIN matches ON matches.id_league = bets.league_id AND matches.mday = bets.league_mday
        INNER JOIN league_teams AS home ON home.id_team = matches.id_home AND home.id_league = matches.id_league
        INNER JOIN league_teams AS away ON away.id_team = matches.id_away AND away.id_league = matches.id_league
        WHERE bets.open = 1 AND matches.status = 0 AND matches.start_date < NOW() + INTERVAL {} DAY {}
        ORDER BY users_month.id_league ASC, users_month.id_user ASC, matches.start_date ASC
        """.format(int(interval_day), 'AND users.role = 2' if admin else '')
        id_user = None
        user = None
        for row in db.query(stmt):
            if row['id_user'] != id_user:
                if user is not None:
                    yield user
                user = User(idt=row['id_user'], name=row['username'])
                user.role = row['role']
                user.notifications = json.loads(row['notifications'])
                user.league_bets = {mday: [x for x in row['league_bets_{}'.format(mday)]] for mday in range(1, 5)}
                user.league_bets_matches = {mday: list() for mday in range(1, 5)}
            match = Match(idt=row['id_match'])
            match.idof10 = row['idof10']
            match.home = Team(idt=row['id_home'])
            match.away = Team(idt=row['id_away'])
            match.home.rank = row['home_rank']
            match.away.rank = row['away_rank']
            match.home.coeff = row['home_coeff']
            match.away.coeff = row['away_coeff']
            match.start_date = row['start_date']
            match.id_month = row['id_month']
            user.league_bets_matches[row['mday']].append(match)
            id_user = row['id_user']
        if user is not None:
            yield user

    @staticmethod
    def get_users_europe_bets(db, interval_day=1, admin=False):
        stmt = """
        SELECT id_user, europe_scores_quarter, europe_scorers_quarter, europe_scores_semi, europe_scorers_semi, 
        europe_scores_final, europe_scorers_final, username, role, notifications,
        bets.id_league, bets.id_month, bets.europe_round, bets.europe_matches,
        matches.id AS id_match, matches.start_date, matches.id_home, matches.id_away,
        home.staff AS home_staff, away.staff AS away_staff
        FROM users_month
        INNER JOIN users ON users.id = users_month.id_user
        INNER JOIN bets ON bets.europe_round = users_month.europe_round AND bets.id_month = users_month.id_month
        INNER JOIN matches ON matches.id IN (
            SUBSTRING_INDEX(bets.europe_matches, ',', 1), 
            SUBSTRING_INDEX(SUBSTRING_INDEX(bets.europe_matches, ',', 2), ',', -1),
            SUBSTRING_INDEX(SUBSTRING_INDEX(bets.europe_matches, ',', 3), ',', -1),
            SUBSTRING_INDEX(bets.europe_matches, ',', -1)
        )
        INNER JOIN teams AS home ON home.id = matches.id_home
        INNER JOIN teams AS away ON away.id = matches.id_away
        WHERE bets.open = 1 AND users_month.europe > 0 AND users_month.europe_round != ''
        AND matches.start_date > NOW() AND matches.start_date < NOW() + INTERVAL {} DAY {}
        ORDER BY users_month.europe ASC, users_month.id_user ASC, matches.start_date ASC
        """.format(int(interval_day), 'AND users.role = 2' if admin else '')
        id_user = None
        user = None
        for row in db.query(stmt):
            if row['id_user'] != id_user:
                if user is not None:
                    yield user
                user = User(idt=row['id_user'], name=row['username'])
                user.role = row['role']
                user.notifications = json.loads(row['notifications'])
                user.europe_round = row['europe_round']
                user.europe_scores = json.loads(row['europe_scores_{}'.format(row['europe_round'])])
                user.europe_scorers = json.loads(row['europe_scorers_{}'.format(row['europe_round'])])
                user.europe_bets_matches = list()
            match = Match(idt=row['id_match'])
            match.idof4 = row['europe_matches'].split(',').index(str(match.id))
            match.home = Team(idt=row['id_home'])
            match.away = Team(idt=row['id_away'])
            match.home.staff = json.loads(row['home_staff'])
            match.away.staff = json.loads(row['away_staff'])
            match.start_date = row['start_date']
            match.id_month = row['id_month']
            user.europe_bets_matches.append(match)
            id_user = row['id_user']
        if user is not None:
            yield user

    @staticmethod
    def get_users_results(month, db):
        stmt = """
        SELECT id_user, id_league, id_team, league_points_total, league_rank, europe, europe_round,
        username, records
        FROM users_month
        INNER JOIN users ON users.id = users_month.id_user
        WHERE id_month = :id_month
        """
        args = {'id_month': month.id}
        for row in db.query(stmt, args):
            user = User(idt=row['id_user'], name=row['username'])
            user.id_league = row['id_league']
            user.id_team = row['id_team']
            user.league_points = row['league_points_total']
            user.league_rank = row['league_rank']
            user.europe = row['europe']
            user.europe_round = row['europe_round']
            user.records = json.loads(row['records'])
            yield user