#include <algorithm>
#include <cmath>
#include <cstdlib>
#include <iostream>
#include <limits>
#include <list>
#include <map>
#include <memory>
#include <random>
#include <sstream>
#include <string>
#include <vector>
#include <fstream>

using std::string;
using std::vector;
using std::map;
using std::cerr;
using std::cout;

vector<string> Split(const string& message, char deliminator) {
  std::istringstream stream(message);
  vector<string> result;
  string tmp;
  while (std::getline(stream, tmp, deliminator)) {
    result.emplace_back(tmp);
  }
  return result;
}

struct Player {
  Player(const string id, const int rank, const int desired_games)
      : id_(id), rank_(rank), desired_games_(desired_games) {}
  Player() = default;
  Player(const Player& other) = default;
  Player& operator=(const Player& other) = default;

  string id_;
  int rank_;
  int desired_games_;

  string DebugString() const {
    std::ostringstream output;
    output << "Player(id=" << id_ << ", rank=" << rank_
           << ", desired_games=" << desired_games_ << ")";
    return output.str();
  }

  string PrintRank() const {
    std::ostringstream output;
    output << std::abs(rank_) << (rank_ > 0 ? 'd' : 'k');
    return output.str();
  }

  string ToString() const {
    std::ostringstream output;
    output << id_ << " (" << std::abs(rank_) << (rank_ > 0 ? 'd' : 'k')
           << ")";
    return output.str();
  }
};

std::unique_ptr<Player> ParsePlayer(const vector<string>& data, int round) {
  const int kPlayerTokens = 7;
  if (data.size() < kPlayerTokens) {
    return nullptr;
  }
  string id = data[0];
  std::istringstream rank_stream(data[2]);
  int rank;
  rank_stream >> rank;
  char sign;
  rank_stream >> sign;
  if (sign != 'k' && sign != 'd') {
    return nullptr;
  }
  if (sign == 'k') {
    rank = -rank;
  }
  std::istringstream desired_games_stream(data[3 + round]);
  int desired_games;
  desired_games_stream >> desired_games;
  return std::unique_ptr<Player>(new Player(id, rank, desired_games));
}


int main() {
  const string kDate = "2015-09-01";
  map<string, Player> players;
  map<string, int> player_ids;
  int current_id = 1;
  string line;
  cerr << "Input players from sheet. End with an empty line.\n";
  while (std::getline(std::cin, line) && !line.empty()) {
    std::unique_ptr<Player> player = ParsePlayer(Split(line, '\t'), 1);
    if (player) {
      cerr << player->DebugString() << "\n";
      players[player->id_] = *player;
      player_ids[player->id_] = current_id++;
    } else {
      cerr << "Failed to parse player: " << line;
    }
  }

  cout << "insert into players (Pin_Player,Name,Rating) values ";
  bool first = true;
  for (const auto& e : players) {
    const Player& player = e.second;
    int id = player_ids[player.id_];
    if (first) {
      first = false;
    } else {
      cout << ",";
    }
    cout << "(" << id << ",\"" << player.id_ << "\"," << player.rank_ << ")";
  }
  cout << ";\n";

  cerr << "Input tournament code:\n";
  std::getline(std::cin, line);
  string tournament_code = line;
  cerr << "Tournament " << tournament_code << "\n";

  cerr << "Input results from sheet. End with an empty line.\n";
  cout << "insert into games (Tournament_Code,Game_Date,Pin_Player_1,"
       << "Color_1,Rank_1,Pin_Player_2,Color_2,Rank_2,Handicap,Komi,Result) values";
  first = true;
  while (std::getline(std::cin, line) && !line.empty()) {
    vector<string> data = Split(line, '\t');
    if (data.size() < 5) {
      cerr << "Error parsing result: " << line << "\n";
      continue;
    }
    string p1 = data[0];
    string p2 = data[1];
    std::istringstream handicap_stream(data[2]);
    char dropper;
    handicap_stream >> dropper;
    int handicap;
    handicap_stream >> handicap;
    int komi;
    std::istringstream komi_stream(data[3]);
    komi_stream >> komi;
    char result = data[4][0];
    if (result != 'W' && result != 'B') {
      cerr << "Error parsing result: " << line << "\n";
      continue;
    }
    if (first) {
      first = false;
    } else {
      cout << ",";
    }
    cout << "(";
    cout << "\"" << tournament_code << "\","
         << "\"" << kDate << "\","
         << player_ids[p1] << ","
         << "'W',"
         << "\"" << players[p1].PrintRank() << "\","
         << player_ids[p2] << ","
         << "'B',"
         << "\"" << players[p2].PrintRank() << "\","
         << handicap << ","
         << komi << ","
         << "\"" << result << "\"";
    cout << ")";
  }
  cout << ";";
}