#! /usr/bin/env python
import psycopg2

try:
    from StringIO import StringIO
except ImportError:
    from io import StringIO

conn = psycopg2.connect(database='postgres', user='postgres', password='')
cur = conn.cursor()
rounds = range(14, 69)


class PlanetRankTables:
    basename = 'planet_ranks_round_'
    for i in rounds:
        try:
            cur.execute(
                """CREATE TABLE IF NOT EXISTS round_%d_planet_rank (
                    planet_rank integer, ruler_name text, planet_name text,
                    nickname text, alliance_name text,
                    planet_score integer DEFAULT 0, planet_value integer DEFAULT 0,
                    planet_xp integer DEFAULT 0, planet_size integer DEFAULT 0,
                    planet_race text, planet_coords text
                );"""
                % i)
        except IOError:
            print('ERROR: something went wrong creating the tables.')
        try:
            io = open(str('dumps/planet_ranks_round_%d.csv' % i), 'r')
            cur.copy_from(io, str('round_%d_planet_rank' % i), sep=',')
        except IOError:
            print('ERROR: something went wrong inserting the data.')
    conn.commit()


class PlanetRanks:
    for i in rounds:
        try:
            cur.execute(
                """ALTER TABLE round_%d_planet_rank ADD COLUMN round_number integer;"""
                % i)
            cur.execute(
                """UPDATE round_{0}_planet_rank SET round_number = {1};"""
                    .format(i, i))
        except IOError:
            print('ERROR: something went wrong altering the data.')
        try:
            cur.execute(
                """CREATE TABLE IF NOT EXISTS planetrankstmp (
                    planet_rank integer, ruler_name text, planet_name text,
                    nickname text, alliance_name text,
                    planet_score integer DEFAULT 0, planet_value integer DEFAULT 0,
                    planet_xp integer DEFAULT 0, planet_size integer DEFAULT 0,
                    planet_race text, planet_coords text, round_number integer
                );""")
            cur.execute(
                """INSERT INTO planetrankstmp SELECT * FROM round_%d_planet_rank ORDER BY planet_rank ASC;"""
                % i)
            cur.execute(
                """INSERT INTO planetranks SELECT DISTINCT * FROM planetrankstmp ORDER BY round_number ASC, planet_rank ASC;"""
            )
            cur.execute("DROP TABLE planetrankstmp")
        except IOError:
            print('ERROR: something went wrong merging the data.')
    conn.commit()
    cur.close()
    conn.close()