import psycopg2
import datetime
import getpass
db = raw_input("database name:")
dbuser = raw_input("database username:")
psw = getpass.getpass()
conn = psycopg2.connect(host="localhost", user=dbuser, password=psw, dbname=db)
cr = conn.cursor()

try:
    pas_id_str = "giscedata.switching.c2.05,{0}"

    cr.execute("SELECT id FROM giscedata_switching_proces WHERE name = 'C2'")
    procesC2_id = cr.fetchone()[0]

    cr.execute("SELECT id FROM giscedata_switching_step "
               "WHERE name = '05' AND proces_id = %s", (procesC2_id, ))
    step05_id = cr.fetchone()[0]

    cr.execute("SELECT swstep.id, header.sw_id "
               "from giscedata_switching_c2_07 AS swstep "
               "JOIN giscedata_switching_step_header AS header "
               "ON swstep.header_id=header.id")

    for c207_id, sw_id in cr.fetchall():
        print "Copying C2-07 with id {0}".format(c207_id)
        cr.execute("SELECT * FROM giscedata_switching_c2_07 "
                   "WHERE id=%s", (c207_id, ))
        info = cr.fetchone()
        info = info[5:]
        new_info = []
        for i in range(len(info)):
            if isinstance(info[i], datetime.date):
                new_info.append(info[i].strftime("%Y-%m-%d"))
            elif isinstance(info[i], datetime.datetime):
                new_info.append(info[i].strftime("%Y-%m-%d %H:%M:%S"))
            elif isinstance(info[i], datetime.time):
                new_info.append(info[i].strftime("%H:%M:%S"))
            else:
                new_info.append(info[i])
        new_info = ("{}".format(tuple(new_info))).replace("None", "Null")
        # Check if there is already a C205
        cr.execute("SELECT id FROM giscedata_switching_c2_05 "
                   "WHERE header_id=%s", (info[6],))
        c205_id = cr.fetchone()
        if c205_id:
            print "  * There is already a C2-05 case with id {0}".format(c205_id[0])
            continue
        # Create C2-05 with C2-07 info
        cr.execute('INSERT INTO giscedata_switching_c2_05 '
                   '(tipus_activacio, data_activacio, tipus_contracte, '
                   'control_potencia, contracte_atr, periodicitat_facturacio, '
                   'header_id, "tarifaATR", hora_activacio, tipus_autoconsum, '
                   'tipus_telegestio, marca_medida_bt, kvas_trafo)'
                   'VALUES {0}'.format(new_info))
        # Get id of new C2-05
        cr.execute("SELECT id FROM giscedata_switching_c2_05 "
                   "WHERE header_id=%s", (info[6], ))
        c205_id = cr.fetchone()[0]
        # Create step_info which relates SW case with C2-05 step
        cr.execute("INSERT INTO giscedata_switching_step_info (step_id, sw_id, proces_id, pas_id)"
                   "VALUES (%s,%s,%s,%s)",
                   (step05_id, sw_id, procesC2_id, pas_id_str.format(c205_id)))
        # Update the step_id of SW if it is Null
        cr.execute("SELECT step_id FROM giscedata_switching "
                   "WHERE id=%s", (sw_id,))
        step_id = cr.fetchone()
        if not step_id or (step_id and step_id[0] is None):
            cr.execute("UPDATE giscedata_switching SET step_id=%s WHERE id=%s",
                       (step05_id, sw_id))
        print "  * New C2-05 with id={0}, switching case id={1}".format(c205_id, sw_id)

    conn.commit()

except Exception as e:
    conn.rollback()
    raise e

finally:
    cr.close()
    conn.close()