import pyodbc
import random

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=ACER-MOBILE\SQLEXPRESS;DATABASE=HockeyTeamAccounting')

# Using a DSN, but providing a password as well

# Create a cursor from the connection
cities_countries = { "Україна" : ["Рівне","Маріуполь","Київ","Харків", "Дніпро","Кременчуг","Запоріжжя"],
                    "Білорусь": ["Вітебськ", "Мінськ","Брест","Гомель","Орша","    Могильов"," Мозир"],
                    "США" : ["Юта","Нью-Йорк","Фінікс","Анкоридж", "Мемфіс","Сан-Антоніо","Лас-Вегас"],
                     "Канада": ["Оттава", "Едмунтон","Вінніпег","Ванкувер","Галіфакс","Монреаль","Шербрук","Лондон"]
                     }
teams_name_bu = ("Динамо","Леви","Арсенал","Адмірал","Бобри","Рисі","Зубри","Вовки")
teams_name_сu = ("Пінгвіни","Індіанці","Койоти","Грізлі","Бобри","Броненосці","Вовки")
date_range = range(1950,2010)
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Team')
print("Hello")
for row in cursor:
    print(row)

#cursor.execute("Truncate table Team")
rowcount = 0

while rowcount < 100:
    for country in cities_countries.keys():
        cursor.execute("SELECT Max(team_id) FROM Team")
        rowcount = cursor.fetchone()[0]
        print(str(rowcount) +" **** " )
        if rowcount == 100:
            break
        try:
            team_name = ""
            if country in ["Україна","Білорусь"]:
                team_name = random.choice(teams_name_bu)
            else:
                team_name = random.choice(teams_name_сu)
            cursor.execute("insert into Team(team_country,team_city,team_name,team_fondation_year) "
                           "values(?,?,?,?)",country,random.choice(cities_countries[country]),team_name ,random.choice(date_range))
            cursor.commit()
        except Exception as ex:
            print(ex)
    cursor.execute("SELECT MAX (team_id) FROM Team")
    print(rowcount)
    rowcount = cursor.fetchone()[0]
cursor.execute("SELECT COUNT (team_id) FROM Team")

rowcount = cursor.fetchone()[0]
print(rowcount)

cursor.execute('SELECT * FROM Team')
name_bu = ["Петро","Василь","Пилип","Данило", "Руслан", "Ярослав" ,"Всеволод", "Микита"]
surname_bu = ["Шевченко", "Василенко" ,"Дмитренко", "Іваненко", "Пилипенко", "Биков", "Стогній", "Пилипчук"]
name_cu = ["Джон","Джек","Саймон" "Раймонд", "Мет", "Теодор" ,"Лео", "Чарльз"]
surname_cu = ["Сноу", "Доу" ,"Кін", "Ремзі", "Вальс", "Кауфман", "Ріман", "МакАртур"]
cursor.execute("SELECT COUNT (*) FROM Team")
rowcount = cursor.fetchone()[0]
cursor.execute("SELECT team_id,team_country From Team")
import datetime
for row in cursor.fetchall():
        print(str(row.team_id) + "  "+ row.team_country)
        notcorrect = True
        while notcorrect:
            try:
                trainer_name = ""
                trainer_surname = ""
                if row.team_country  in ["Україна", "Білорусь"]:
                    trainer_name = random.choice(name_bu)
                    trainer_surname = random.choice(surname_bu)
                else:
                    trainer_name = random.choice(name_cu)
                    trainer_surname = random.choice(surname_cu)
                cursor.execute("insert into Trainer(trainer_name,trainer_surname,trainer_country,birth_date,current_team_id) "
                               "values(?,?,?,?,?)", trainer_name, trainer_surname, row.team_country,datetime.datetime(1960, 5, 17),row.team_id)
                cursor.commit()
                notcorrect = False
            except Exception as ex:
                notcorrect = True
                print(ex)

cursor.execute("SELECT COUNT (*) FROM Team")
print(cursor.fetchone()[0])


stadion_names_bu = ["Олімпійський","Першотравневий","Словянський","Квітневий","Янтар","Вишневий","Імені Валерія Харламова"]
stadion_names_cu = ["імені Вашингтона","імені Сміта","імені Вейна Ґрецкі","імені Боббі Орра"]
for country in cities_countries.keys():
    for city in  cities_countries[country]:
        stad_name = ""
        if country in ["Україна","Білорусь"]:
            stad_name = random.choice(stadion_names_bu)
        else:
            stad_name = random.choice(stadion_names_cu)
        cursor.execute("insert into Stadion(stadion_name,stadion_country,stadion_city) "
                       "values(?,?,?)", stad_name,country,city)
        cursor.commit()
import itertools
from faker import Faker
import radar
import copy
def add_score():
    general_score = range(0, 11)
    general_home = random.choice(general_score)
    general_guest = random.choice(general_score)
    if general_home != general_guest:
        return (general_home, general_guest, 0, 0, 0, 0)
    overtime_score = range(0,6)
    overtime_home = random.choice(overtime_score)
    overtime_guest = random.choice(overtime_score)
    if  overtime_guest!= overtime_home:
        return (general_home, general_guest, overtime_home, overtime_guest, 0, 0)
    bullit_home = 0
    bullit_guest = 0
    choose =  range(3,6)
    while  bullit_home ==  bullit_guest:
        bullit_home = random.choice(choose)
        bullit_guest = random.choice(choose)

    return (general_home,general_guest,overtime_home,overtime_guest,bullit_home,bullit_guest)

def create_matches_for_tournament( cursor,country,tournament_id,start_time,end_time):
    print(country)
    cursor.execute('SELECT max(game_id) FROM Game')
    print(cursor.fetchone()[0])
    teams_ids = []
    try:
        cursor.execute('SELECT team_id FROM Team Where team_country = ?',country)
        t = []
        for r in cursor:
            #print(r.team_id)
            t.append(r.team_id)

        temp = list(itertools.combinations(t,2))
        teams_ids = temp[:]#combination
        for it in temp:
            teams_ids.append((it[1],it[0]))
        random.shuffle(teams_ids)
        #print(teams_ids)
    except Exception as e:
        print("haha")
        print(e)
    for ids in teams_ids:
        try:

            cursor.execute('Select * FROM Team Where team_id = ?', ids[0])

            team_city = ""
            for r in cursor:
                team_city = r.team_city
            #print( team_city)
            cursor.execute('SELECT stadion_id FROM Stadion Where stadion_city = ?', team_city)
            stadion_id = [r.stadion_id   for r in cursor][0]

            cursor.execute('SELECT trainer_id FROM Trainer Where current_team_id = ?', ids[0])
            home_trainer_id = [r.trainer_id for r in cursor][0]

            cursor.execute('SELECT trainer_id FROM Trainer Where current_team_id = ?', ids[1])
            guest_trainer_id = [r.trainer_id for r in cursor][0]
            #, "
            #" home_team_score,guest_team_score,overtime_home_team_score,overtime_guest_team_score,bullits_home_team_score,bullits_guest_team_score
            #print(tournament_id)
            print("hello")
            home_score_general,guest_score_general,overtime_home_score,overtime_guest_score,bullits_home,bullits_guest = add_score()
            print(str(home_score_general)+ " "+str(guest_score_general)+" "+str(overtime_home_score)+" "+str(overtime_guest_score)+" "+str(bullits_home)+" "+str(bullits_guest))
            cursor.execute("insert into Game(tournament_id,home_team_id,guest_team_id,stadion_id,home_team_trainer_id,guest_team_trainer_id,start_time,"
                           " home_team_score,guest_team_score,overtime_home_team_score,overtime_guest_team_score,bullits_home_team_score,bullits_guest_team_score) "
                           "values(?,?,?,?,?,?,?, ?,?,?,?,?,?)", tournament_id, ids[0],ids[1],stadion_id,home_trainer_id,guest_trainer_id,radar.random_datetime(
                start=start_time,
                stop=end_time
            ),home_score_general,guest_score_general,overtime_home_score,overtime_guest_score,bullits_home,bullits_guest)
            cursor.commit()
        except Exception as e:

            print(e)



import datetime
tournament_name = {"Хокейна Ліга США":"локальний","Канадська Хокейна Ліга":"локальний","Українська Хокейна Ліга":"локальний","Білоруська Хокейна Ліга":"локальний"}
countries = ["США","Канада","Білорусь","Україна"]
count = 0
for tournament_name,tournament_status in tournament_name.items():
    cursor.execute("insert into Tournament(tournament_name,tournament_status,tournament_start,tournament_finish) "
                   "values(?,?,?,?)", tournament_name, tournament_status, datetime.datetime(2018, 10, 1),datetime.datetime(2019, 4, 1))
    cursor.commit()

    create_matches_for_tournament(cursor, countries[count], count+1, datetime.datetime(2018, 10, 1),datetime.datetime(2019, 4, 1))
    count += 1