import psycopg2
from psycopg2 import sql
import random
from random import randint
import string
import os

script_folder=os.path.dirname(__file__)
project_folder=os.path.dirname(script_folder)
raw_data_folder=project_folder+'/raw_data/'

potential_string   = 'dbug.sh_potenziale'
opponents_string   = 'dbug.sh_contraria'

contractors_input  = open(raw_data_folder+'Contractors.txt','r')
contractors_list   = [ i.strip().split() for i in contractors_input.readlines()]
contractors_input.close()

potentials_input   = open(raw_data_folder+'Potentials.txt','r')
potentials_list    = [ i.strip().split() for i in potentials_input.readlines()]
potentials_input.close()

opponents_input    = open(raw_data_folder+'Opposites.txt','r')
opponents_list     = [ i.strip().split() for i in opponents_input.readlines()]
opponents_input.close()

conn=psycopg2.connect('dbname=DBug user=postgres')
curs=conn.cursor()

#SH_POTENZIALE SH_CONTRARIA: INSERTION

potentials_opponents_array   = [potentials_list,opponents_list]
potentials_opponents_strings = [potential_string,opponents_string]

ind=0
for i in potentials_opponents_array:
    for j in i:

        curs.execute("insert into %s values (%%s, %%s)" % potentials_opponents_strings[ind],[j[0], j[1]])
    ind=ind+1
    

#SH_CONTRAENTE: PGP-PUBLIC-KEY GENERATION + INSERTION

for i in contractors_list:

    i.append(''.join(random.choices(string.ascii_uppercase + string.digits +string.ascii_lowercase, k=761)))
    i.append(randint(30,40))


for j in contractors_list:

    curs.execute("insert into dbug.sh_contraente values (%s, %s, %s, %s)", [j[0],j[1],j[2],j[3]])


"""

alternative way to select
        #curs.execute(sql.SQL("select into {} values (%s, %s)").format(sql.Identifier(potentials_opponents_strings[ind])),[j[0], j[1]])

"""

conn.commit()
curs.close()
conn.close()