#!/usr/bin/python
# coding=utf-8


def loadFile(filename):
  f = open(filename, 'r');
  tab = [];
  for line in f:
    line = line.replace('\'',' ');
    line = line.strip('\n');
    arr = line.split('\t');
    tab.append(arr);
  f.close();
  return tab;

def dictList(tab):
  head = tab[0];
  tab.remove(head);
  lst = [];
  for row in tab:
    d = {};
    for colNo in range(len(row)):
      d[head[colNo]] = row[colNo];
    lst.append(d);
  return lst;

def makeSetFromKey(dic, key):
  s = set();
  for line in dic:
    s.add(line[key]);
  return s;

def makeTableFromSet(table,k, s, rest):
  insertion = "INSERT INTO "+table+" ("+k;
  for i in rest:
    insertion += ", " + i;
  insertion += ") VALUES";
  for t in s:
    insertion += "\n  ('" + t + "'"
    for i in rest:
      insertion += ", '" + rest[i] + "'";
    insertion += "),";
  insertion = insertion.strip(',') + ";\n\n";
  return insertion;

def makePairWithSetAndDic(st, dic, keyName, keyVal):
  l = {};
  for name in st:
    for d in dic:
      if name == d[keyName]:
        l[name] = d[keyVal];
  return l;
  
def makeTableFromPair(table, pair, k1, to_query, table_query, col_query):
  insertion = "INSERT INTO " + table + "(" + k1 + ", " + to_query + ") VALUES";
  for k in pair:
    insertion += "\n  ('" + k + "', ";
    insertion += "(SELECT " + to_query;
    insertion += " FROM " + table_query;
    insertion += " WHERE " + col_query + "='" + pair[k] + "')"
    insertion += "),";
  insertion = insertion.strip(',') + ";\n\n";
  return insertion;

dateNum = {"mai":"5", "juin":"6", "juil.":"7", "août":"8"};
def dateFormat(date):
  date_part = date.split(' ');
  day = date_part[0];
  monthName = date_part[1];
  year = date_part[2];
  return year + "-" + dateNum[monthName] + "-" + day;
  
def redoID(dic):
  sequence = 1
  for row in dic:
    row["NO"] = str(sequence);
    sequence += 1;

def greatPart(dic, keyToCol, keyToQuery, bidon, tableName):
  insertion = "INSERT INTO " + tableName + " (";
  for k in keyToCol:
    insertion += keyToCol[k] + ",";
  for k in bidon:
    insertion += k + ",";
  for k in keyToQuery:
    insertion += keyToQuery[k][3] + ",";
  insertion = insertion.strip(',') + ") VALUES";
  for row in dic:
    insertion += "\n  ("
    for col in  keyToCol:
      ins = row[col];
      if col == "DATE":
        ins = dateFormat(ins);
      insertion += "'" + ins + "',";
    for col in bidon:
      insertion += "'" + bidon[col] + "',";
    for col in keyToQuery:
      insertion += "(SELECT " + keyToQuery[col][0];
      insertion += " FROM " + keyToQuery[col][1];
      insertion += " WHERE " + keyToQuery[col][2] + "='" + row[col] + "'";
      insertion += "),";
    insertion = insertion.strip(',') + "),";
  return insertion.strip(',')+";\n\n";

#code pour baseballsherbrooke
tab = loadFile('matchs2012.csv');
dic = dictList(tab);

marqueur = makeSetFromKey(dic,"Marqueur");
arbitre_but = makeSetFromKey(dic,"Arbitre Buts");
arbitre_marbre = makeSetFromKey(dic,"Arbitre Marbre");
arbitre = arbitre_marbre | arbitre_but;
terrain = makeSetFromKey(dic, "TERRAIN");
equipe_local = makeSetFromKey(dic,"LOCAL");
equipe_visite = makeSetFromKey(dic, "VISIT");
equipe_local_cat = makePairWithSetAndDic(equipe_local,dic,"LOCAL","CATEGORIE");
equipe_visite_cat = makePairWithSetAndDic(equipe_visite,dic,"VISIT","CATEGORIE");
equipe_cat = dict(equipe_local_cat, **equipe_visite_cat);
categorie = makeSetFromKey(dic, "CATEGORIE");
status = makeSetFromKey(dic,"Statut");

adresse_bidon = "333, rue du pas ici";
telephone_bidon = "888 888 8888";
courriel_bidon = "adresse@domaine.ext";

redoID(dic);

f = open("tuple.sql",'w');

f.write(makeTableFromSet("partie_status","partie_status_nom", status,{}));
f.write(makeTableFromSet("categorie", "categorie_nom", categorie, {}));
f.write(makeTableFromSet("terrain","terrain_nom", terrain, {"terrain_adresse":adresse_bidon, "ville_numero":"1"}));
f.write(makeTableFromSet("marqueur","marqueur_nom", marqueur, {"marqueur_prenom":"<==", "marqueur_telephone":telephone_bidon, "marqueur_adresse":adresse_bidon, "marqueur_courriel":courriel_bidon}));
f.write(makeTableFromSet("arbitre","arbitre_nom",arbitre, {"arbitre_prenom":"<==", "arbitre_telephone":telephone_bidon, "arbitre_adresse":adresse_bidon, "arbitre_courriel":courriel_bidon}));
f.write(makeTableFromPair("equipe", equipe_cat, "equipe_nom", "categorie_numero", "categorie", "categorie_nom"));

correction = "DELETE FROM equipe WHERE equipe_numero=68;\n";
correction += "DELETE FROM equipe WHERE equipe_numero=29;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=12;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=21;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=22;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=37;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=47;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=9;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=28;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=15;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=10;\n";
correction += "DELETE FROM arbitre WHERE arbitre_numero=35;\n";
correction += "DELETE FROM terrain WHERE terrain_numero=9;\n\n"
f.write(correction);

mess = {"VISIT":("equipe_numero","equipe","equipe_nom","equipe_numero_visite"),
        "LOCAL":("equipe_numero","equipe","equipe_nom","equipe_numero_local"),
        "TERRAIN":("terrain_numero","terrain","terrain_nom","partie_terrain"),
        "Statut":("partie_status_numero", "partie_status","partie_status_nom","partie_status"),
        "CATEGORIE":("categorie_numero", "categorie","categorie_nom","partie_categorie")};

f.write(greatPart(dic, {"NO":"partie_numero","DATE":"partie_date", "HRE":"partie_heure_debut_prevue"},  mess, {},"partie"));

mess = {"Arbitre Marbre":("arbitre_numero", "arbitre", "arbitre_nom"),
        "Arbitre Buts":("arbitre_numero", "arbitre", "arbitre_nom"),
        "Marqueur":("marqueur_numero", "marqueur", "marqueur_nom")};

f.write(greatPart(dic, {"NO":"partie_numero"}, {"Marqueur":("marqueur_numero", "marqueur", "marqueur_nom", "marqueur_numero")}, {"marqueur_position_numero":"1"}, "marqueur_partie"));
f.write(greatPart(dic, {"NO":"partie_numero"}, {"Arbitre Marbre":("arbitre_numero", "arbitre", "arbitre_nom", "arbitre_numero"), },{"arbitre_position_numero":"1"},"arbitre_partie"));
f.write(greatPart(dic, {"NO":"partie_numero"}, {"Arbitre Buts":("arbitre_numero", "arbitre", "arbitre_nom", "arbitre_numero") },{"arbitre_position_numero":"2"},"arbitre_partie"));

f.close();