#!/usr/bin/env python -u
# -*- coding: utf-8 -*-
"""
Copyright 2013 Jacek Markowski, jacek87markowski@gmail.com

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
"""

import sqlite3
from csv import reader
import os
import locale
import codecs
import unicodedata
locale.setlocale(locale.LC_ALL, "C")
import platform
system = platform.system()
if system == 'Windows':
    from pyfann_win import libfann
elif system == 'Linux':
    from pyfann import libfann
else:
    from pyfann import libfann
from bb_shared import Shared
from PySide import QtGui



class Database(Shared):
    ''' SQL base'''
    def __init__(self, parent=None):
        '''Creates all nessesary tables in sql'''
        Shared.__init__(self)
        self.mybase = sqlite3.connect(':memory:')
        self.relations_base = self.mybase.cursor()
        self.stop_action = 0 # when 1 stops simulation , export
        #This function creates sql database and tables#
        self.relations_base.execute('''CREATE TABLE results
        (id INTEGER PRIMARY KEY,
        date_txt txt,
        date_num FLOAT,
        home TEXT,
        away TEXT,
        gHomeEnd INTEGER,
        gAwayEnd INTEGER,
        odd_home FLOAT,
        odd_draw FLOAT,
        odd_away FLOAT
        fake TEXT NOT NULL DEFAULT "-")''')
        self.relations_base.execute('''CREATE TABLE league
        (id INTEGER PRIMARY KEY,
        team TEXT,
        matches INTEGER DEFAULT 000.0,
        matchesHome INTEGER DEFAULT 000.0,
        matchesAway INTEGER DEFAULT 000.0,
        points INTEGER DEFAULT 0,
        pointsHome INTEGER DEFAULT 0,
        pointsAway INTEGER DEFAULT 0,
        form INTEGER DEFAULT 000.0,
        formHome INTEGER DEFAULT 000.0,
        formAway INTEGER DEFAULT 000.0,
        pointsBB FLOAT DEFAULT 0.000,
        pointsBBHome FLOAT DEFAULT 000.0,
        pointsBBAway FLOAT DEFAULT 000.0,
        formBB FLOAT DEFAULT 000.0,
        formBBHome FLOAT DEFAULT 000.0,
        formBBAway FLOAT DEFAULT 000.0,
        wins INTEGER DEFAULT 000.0,
        draws INTEGER DEFAULT 000.0,
        loses INTEGER DEFAULT 000.0,
        winhome INTEGER DEFAULT 000.0,
        drawhome INTEGER DEFAULT 000.0,
        losehome INTEGER DEFAULT 000.0,
        winaway INTEGER DEFAULT 000.0,
        drawaway INTEGER VARCHAR(2) NOT NULL DEFAULT 0,
        loseaway INTEGER VARCHAR(2) NOT NULL DEFAULT 0,
        goalsscored INTEGER NOT NULL DEFAULT 0,
        goalslost INTEGER NOT NULL DEFAULT 0,
        goalsscoredhome INTEGER NOT NULL DEFAULT 0,
        goalslosthome INTEGER NOT NULL DEFAULT 0,
        goalsscoredaway INTEGER NOT NULL DEFAULT 0,
        goalslostaway INTEGER NOT NULL DEFAULT 0,
        mowins FLOAT DEFAULT 0.0,
        moloses FLOAT DEFAULT 0.0,
        diffgwins FLOAT DEFAULT 0.0,
        diffgloses FLOAT DEFAULT 0.0,
        mowinsHome FLOAT DEFAULT 0.0,
        molosesHome FLOAT DEFAULT 0.0,
        diffgwinsHome FLOAT DEFAULT 0.0,
        diffglosesHome FLOAT DEFAULT 0.0,
        mowinsAway FLOAT DEFAULT 0.0,
        molosesAway FLOAT DEFAULT 0.0,
        diffgwinsAway FLOAT DEFAULT 0.0,
        diffglosesAway FLOAT DEFAULT 0.0,
        f1 INTEGER DEFAULT 000.0,
        f2 INTEGER DEFAULT 000.0,
        f3 INTEGER DEFAULT 000.0,
        f4 INTEGER DEFAULT 000.0,
        f1Home INTEGER DEFAULT 000.0,
        f2Home INTEGER DEFAULT 000.0,
        f1Away INTEGER DEFAULT 000.0,
        f2Away INTEGER DEFAULT 000.0,
        f1BB FLOAT DEFAULT 000.0,
        f2BB FLOAT DEFAULT 000.0,
        f3BB FLOAT DEFAULT 000.0,
        f4BB FLOAT DEFAULT 000.0,
        f1BBHome FLOAT DEFAULT 000.0,
        f2BBHome FLOAT DEFAULT 000.0,
        f1BBAway FLOAT DEFAULT 000.0,
        f2BBAway FLOAT DEFAULT 000.0,
        f1op TEXT,
        f2op TEXT,
        f3op TEXT,
        f4op TEXT,
        f1opHome TEXT,
        f2opHome TEXT,
        f1opAway TEXT,
        f2opAway TEXT,
        bts INTEGER DEFAULT 000.0,
        btsHome INTEGER DEFAULT 000.0,
        btsAway INTEGER DEFAULT 000.0,
        over25 INTEGER DEFAULT 000.0,
        over25Home INTEGER DEFAULT 000.0,
        over25Away INTEGER DEFAULT 000.0,
        under25 INTEGER DEFAULT 000.0,
        under25Home INTEGER DEFAULT 000.0,
        under25Away INTEGER DEFAULT 000.0,
        fake TEXT NOT NULL DEFAULT "-")''')
        self.relations_base.execute('''CREATE TABLE series
        (id INTEGER PRIMARY KEY,
        team TEXT,
        series_wins INTEGER NOT NULL DEFAULT 0,
        series_draws INTEGER NOT NULL DEFAULT 0,
        series_loses INTEGER NOT NULL DEFAULT 0,
        series_winshome INTEGER NOT NULL DEFAULT 0,
        series_drawshome INTEGER NOT NULL DEFAULT 0,
        series_loseshome INTEGER NOT NULL DEFAULT 0,
        series_winsaway INTEGER NOT NULL DEFAULT 0,
        series_drawsaway INTEGER NOT NULL DEFAULT 0,
        series_losesaway INTEGER NOT NULL DEFAULT 0,
        series_noloses INTEGER NOT NULL DEFAULT 0,
        series_noloseshome INTEGER NOT NULL DEFAULT 0,
        series_nolosesaway INTEGER NOT NULL DEFAULT 0,
        series_nowins INTEGER NOT NULL DEFAULT 0,
        series_nowinshome INTEGER NOT NULL DEFAULT 0,
        series_nowinsaway INTEGER NOT NULL DEFAULT 0,
        series_nodraws INTEGER NOT NULL DEFAULT 0,
        series_nodrawshome INTEGER NOT NULL DEFAULT 0,
        series_nodrawsaway INTEGER NOT NULL DEFAULT 0,
        series_bts INTEGER DEFAULT 000.0,
        series_btsHome INTEGER DEFAULT 000.0,
        series_btsAway INTEGER DEFAULT 000.0,
        series_over25 INTEGER DEFAULT 000.0,
        series_over25Home INTEGER DEFAULT 000.0,
        series_over25Away INTEGER DEFAULT 000.0,
        series_under25 INTEGER DEFAULT 000.0,
        series_under25Home INTEGER DEFAULT 000.0,
        series_under25Away INTEGER DEFAULT 000.0)''')
        self.relations_base.execute('''CREATE TABLE scaled
        (id INTEGER PRIMARY KEY,
        team TEXT,
        matches FLOAT DEFAULT 000.0,
        points FLOAT DEFAULT 000.0,
        pointsHome FLOAT DEFAULT 000.0,
        pointsAway FLOAT DEFAULT 000.0,
        pointsBB FLOAT DEFAULT 0.000,
        pointsBBHome FLOAT DEFAULT 000.0,
        pointsBBAway FLOAT DEFAULT 000.0,
        form FLOAT DEFAULT 000.0,
        formHome FLOAT DEFAULT 000.0,
        formAway FLOAT DEFAULT 000.0,
        formBB FLOAT DEFAULT 000.0,
        formBBHome FLOAT DEFAULT 000.0,
        formBBAway FLOAT DEFAULT 000.0,
        points_b FLOAT DEFAULT 000.0,
        pointsHome_b FLOAT DEFAULT 000.0,
        pointsAway_b FLOAT DEFAULT 000.0,
        pointsBB_b FLOAT DEFAULT 0.000,
        pointsBBHome_b FLOAT DEFAULT 000.0,
        pointsBBAway_b FLOAT DEFAULT 000.0,
        form_b FLOAT DEFAULT 000.0,
        formHome_b FLOAT DEFAULT 000.0,
        formAway_b FLOAT DEFAULT 000.0,
        formBB_b FLOAT DEFAULT 000.0,
        formBBHome_b FLOAT DEFAULT 000.0,
        formBBAway_b FLOAT DEFAULT 000.0,
        winhome FLOAT DEFAULT 000.0,
        drawhome FLOAT DEFAULT 000.0,
        losehome FLOAT DEFAULT 000.0,
        winaway FLOAT DEFAULT 000.0,
        drawaway FLOAT DEFAULT 000.0,
        loseaway FLOAT DEFAULT 000.0,
        winhome_b FLOAT DEFAULT 000.0,
        drawhome_b FLOAT DEFAULT 000.0,
        losehome_b FLOAT DEFAULT 000.0,
        winaway_b FLOAT DEFAULT 000.0,
        drawaway_b FLOAT DEFAULT 000.0,
        loseaway_b FLOAT DEFAULT 000.0,
        goalsscored FLOAT NOT NULL DEFAULT 0,
        goalslost FLOAT NOT NULL DEFAULT 0,
        goalsscoredhome FLOAT NOT NULL DEFAULT 0,
        goalslosthome FLOAT NOT NULL DEFAULT 0,
        goalsscoredaway FLOAT NOT NULL DEFAULT 0,
        goalslostaway FLOAT NOT NULL DEFAULT 0,
        goalsscored_b FLOAT NOT NULL DEFAULT 0,
        goalslost_b FLOAT NOT NULL DEFAULT 0,
        goalsscoredhome_b FLOAT NOT NULL DEFAULT 0,
        goalslosthome_b FLOAT NOT NULL DEFAULT 0,
        goalsscoredaway_b FLOAT NOT NULL DEFAULT 0,
        goalslostaway_b FLOAT NOT NULL DEFAULT 0,
        mowins FLOAT DEFAULT 0.0,
        moloses FLOAT DEFAULT 0.0,
        mowinsHome FLOAT DEFAULT 0.0,
        molosesHome FLOAT DEFAULT 0.0,
        mowinsAway FLOAT DEFAULT 0.0,
        molosesAway FLOAT DEFAULT 0.0,
        f1 FLOAT DEFAULT 000.0,
        f2 FLOAT DEFAULT 000.0,
        f3 FLOAT DEFAULT 000.0,
        f4 FLOAT DEFAULT 000.0,
        f1Home FLOAT DEFAULT 000.0,
        f2Home FLOAT DEFAULT 000.0,
        f1Away FLOAT DEFAULT 000.0,
        f2Away FLOAT DEFAULT 000.0,
        f1BB FLOAT DEFAULT 000.0,
        f2BB FLOAT DEFAULT 000.0,
        f3BB FLOAT DEFAULT 000.0,
        f4BB FLOAT DEFAULT 000.0,
        f1BBHome FLOAT DEFAULT 000.0,
        f2BBHome FLOAT DEFAULT 000.0,
        f1BBAway FLOAT DEFAULT 000.0,
        f2BBAway FLOAT DEFAULT 000.0,
        bts INTEGER DEFAULT 000.0,
        btsHome INTEGER DEFAULT 000.0,
        btsAway INTEGER DEFAULT 000.0,
        over25 INTEGER DEFAULT 000.0,
        over25Home INTEGER DEFAULT 000.0,
        over25Away INTEGER DEFAULT 000.0,
        under25 INTEGER DEFAULT 000.0,
        under25Home INTEGER DEFAULT 000.0,
        under25Away INTEGER DEFAULT 000.0,
        series_wins FLOAT DEFAULT 000.0,
        series_draws FLOAT DEFAULT 000.0,
        series_loses FLOAT DEFAULT 000.0,
        series_winshome FLOAT DEFAULT 000.0,
        series_drawshome FLOAT DEFAULT 000.0,
        series_loseshome FLOAT DEFAULT 000.0,
        series_winsaway FLOAT DEFAULT 000.0,
        series_drawsaway FLOAT DEFAULT 000.0,
        series_losesaway FLOAT DEFAULT 000.0,
        series_noloses FLOAT DEFAULT 000.0,
        series_noloseshome FLOAT DEFAULT 000.0,
        series_nolosesaway FLOAT DEFAULT 000.0,
        series_nowins FLOAT DEFAULT 000.0,
        series_nowinshome FLOAT DEFAULT 000.0,
        series_nowinsaway FLOAT DEFAULT 000.0,
        series_nodraws FLOAT DEFAULT 000.0,
        series_nodrawshome FLOAT DEFAULT 000.0,
        series_nodrawsaway FLOAT DEFAULT 000.0,
        series_bts INTEGER DEFAULT 000.0,
        series_btsHome INTEGER DEFAULT 000.0,
        series_btsAway INTEGER DEFAULT 000.0,
        series_over25 INTEGER DEFAULT 000.0,
        series_over25Home INTEGER DEFAULT 000.0,
        series_over25Away INTEGER DEFAULT 000.0,
        series_under25 INTEGER DEFAULT 000.0,
        series_under25Home INTEGER DEFAULT 000.0,
        series_under25Away INTEGER DEFAULT 000.0,
        fake TEXT NOT NULL DEFAULT "-")''')
        self.relations_base.execute('''CREATE TABLE odds
        (id INTEGER PRIMARY KEY,
        name TETX,
        odd_home FLOAT DEFAULT 000.0,
        odd_draw FLOAT DEFAULT 000.0,
        odd_away FLOAT DEFAULT 000.0)''')
    def load_csv(self, folder, name, expt_name = None, r_min = 5,
                 r_max = 50, mode=0,net = None):
        '''mode:
        0-normal
        1-export
        2-simulation'''
        print net
       # with open(os.path.join('tmp','')+'comm','w') as comm:
       #     # communicates with export manager
       #     comm.write('')
        self.clear_tables()
        teams = self.return_teams(folder, name)
        for team in teams:
            item = team[0]
            self.relations_base.execute('''INSERT INTO league(team)
                                    VALUES(?)''', [(item)])
            self.relations_base.execute('''INSERT INTO series(team)
                                    VALUES(?)''', [(item)])
            self.relations_base.execute('''INSERT INTO scaled(team)
                                    VALUES(?)''', [(item)])
        self.relations_base.execute('''INSERT INTO odds(name,odd_home,odd_draw,odd_away)
                                    VALUES("odds",0.0,0.0,0.0)''')
        # Selecting all matches from database to process
        results = self.relations_base.execute('''SELECT
                                date_txt,
                                home,
                                away,
                                gHomeEnd,
                                gAwayEnd,
                                odd_home,
                                odd_draw,
                                odd_away
                                FROM results WHERE NOT gHomeEnd='NULL'
                                ORDER BY date_num ASC
                                ''')
        results = results.fetchall()
        # Processing selected matches
        teams_num = len(teams)
        self.match_group = 0
        self.match_group_date = 0
        index = 0
        for i in results:
            day, home, away, fth, fta,odd_home,odd_draw,odd_away = i[:]
            rounds_m = self.relations_base.execute('''SELECT
            max(matches) FROM league''')
            rounds = rounds_m.fetchone()
            rounds = rounds[0]
            if mode == 1: #export
                #with open(os.path.join('tmp','')+'comm','r') as comm:
                # communicates with export manager
                #    comm_var = comm.readline()
                # if comm_var != '':
                #    break
                self.scale_odds(home,away,day)
                if r_min <= rounds <= r_max:
                    index += 1
                    self.scale_group_check(day)
                    if self.match_group == 1:
                        self.scale_group(teams_num)
                       # with open(os.path.join('tmp','')+'print','w') as export_print_file:
                       #     print '==== Scaling====', day
                       #     export_print_file.write('Process data :'+day+' Round %d'%rounds)
                        line =  '==== Scaling====' + day + ' Round:' + str(rounds)
                        QtGui.QApplication.processEvents()
                        self.gui.text_export.append(line)
                    self.export('tmp', home, away, rounds, fth, fta)
                if rounds <= r_max:
                    self.process_csv(i)
            if self.stop_action == 1:
                break
            if mode == 2: # simulation
                if r_min <= rounds <= r_max:
                    self.scale_group_check(day)
                    if self.match_group == 1:
                        self.scale_group(teams_num)
                    self.simulation_prediction(home, away, net,mode=0)
                    self.simulation_filters(home, away)
                    ### used in simulation module
                    self.date = day
                    self.home = home
                    self.away = away
                    self.fth = fth
                    self.fta = fta
                    self.batch_print() # simulation module
                if rounds <= r_max:
                    self.process_csv(i)
            if mode == 0:
                self.process_csv(i)
                # final scale for predicting in stats window
        if mode==0:
            self.scale_group(teams_num)

    def scale_odds(self, home,away,date,min_value=1,max_value=15):
        ''' Scales data to range(-1,1)'''
        odds = self.relations_base.execute('''SELECT odd_home,odd_draw,odd_away
        FROM results WHERE home="%s" AND away="%s" AND date_txt="%s"'''%(home,away,date))
        odd_home,odd_draw,odd_away = odds.fetchone()
        scaled_1 = 2.0*(float(odd_home)-min_value)/(max_value-min_value)-1
        scaled_x = 2.0*(float(odd_draw)-min_value)/(max_value-min_value)-1
        scaled_2 = 2.0*(float(odd_away)-min_value)/(max_value-min_value)-1
        val = [scaled_1,scaled_x,scaled_2]
        for i in range(0,len(val)):
            if val[i] > 1:
                val[i]=1
            elif val[i] < 1:
                val[i]=-1
        self.relations_base.execute('''UPDATE odds SET odd_home=?,odd_draw=?,odd_away=?''',(scaled_1,scaled_x,scaled_2))

    def batch_print(self):
        ''' Used in simulator app'''
        pass
    def export(self, expt_name, home, away, rounds, fth, fta):
        ''' Exports data for learning'''
        with open(os.path.join('tmp', '')+'export', 'a') as save:
            scaled_h = self.relations_base.execute('''SELECT
            matches,
            points,
            pointsHome,
            pointsBB,
            pointsBBHome,
            form,
            formHome,
            formBB,
            formBBHome,
            points_b,
            pointsHome_b,
            pointsBB_b,
            pointsBBHome_b,
            form_b,
            formHome_b,
            formBB_b,
            formBBHome_b,
            winhome,
            drawhome,
            losehome,
            winhome_b,
            drawhome_b,
            losehome_b,
            goalsscored,
            goalslost,
            goalsscoredhome,
            goalslosthome,
            goalsscored_b,
            goalslost_b,
            goalsscoredhome_b,
            goalslosthome_b,
            mowins,
            moloses,
            mowinsHome,
            molosesHome,
            f1,
            f2,
            f3,
            f4,
            f1Home,
            f2Home,
            f1BB,
            f2BB,
            f3BB,
            f4BB,
            f1BBHome,
            f2BBHome,
            bts,
            btsHome,
            over25,
            over25Home,
            under25,
            under25Home,
            series_wins,
            series_draws,
            series_loses,
            series_winshome,
            series_drawshome,
            series_loseshome,
            series_noloses,
            series_noloseshome,
            series_nowins,
            series_nowinshome,
            series_nodraws,
            series_nodrawshome,
            series_bts,
            series_btsHome,
            series_over25,
            series_over25Home,
            series_under25,
            series_under25Home
            FROM scaled
            WHERE team="%s"'''%home)
            scaled_h = scaled_h.fetchone()
            scaled_h = str(scaled_h[:])
            scaled_a = self.relations_base.execute('''SELECT
            matches,
            points,
            pointsAway,
            pointsBB,
            pointsBBAway,
            form,
            formAway,
            formBB,
            formBBAway,
            points_b,
            pointsAway_b,
            pointsBB_b,
            pointsBBAway_b,
            form_b,
            formAway_b,
            formBB_b,
            formBBAway_b,
            winaway,
            drawaway,
            loseaway,
            winaway_b,
            drawaway_b,
            loseaway_b,
            goalsscored,
            goalslost,
            goalsscoredaway,
            goalslostaway,
            goalsscored_b,
            goalslost_b,
            goalsscoredaway_b,
            goalslostaway_b,
            mowins,
            moloses,
            mowinsAway,
            molosesAway,
            f1,
            f2,
            f3,
            f4,
            f1Away,
            f2Away,
            f1BB,
            f2BB,
            f3BB,
            f4BB,
            f1BBAway,
            f2BBAway,
            bts,
            btsAway,
            over25,
            over25Away,
            under25,
            under25Away,
            series_wins,