import psycopg2
import sqlite3

# Establish PostgreSQL connection
DB_NAME = 'axajhxor'
DB_USER = 'axajhxor'
DB_PW = '<PASSWORD>'
DB_HOST = 'rajje.db.elephantsql.com'

conn = psycopg2.connect(
    dbname=DB_NAME,
    user=DB_USER,
    password=<PASSWORD>,
    host=DB_HOST)
# print(type(conn))

cur = conn.cursor()
# print(type(cur))

# Establish sqlite3 connection
sl_conn = sqlite3.connect("data/rpg_db_original.sqlite3")
sl_curs = sl_conn.cursor()
# print(type(sl_conn))
# print(type(sl_curs))
# row_count = 'SELECT COUNT(*) FROM charactercreator_character'
# print(sl_curs.execute(row_count).fetchall()} characters)


###################### INSERTS ###########################


# Get and insert data for charcreator_characters
get_characters = 'SELECT * FROM charactercreator_character'
chars = sl_curs.execute(get_characters).fetchall()

# show_tables = """
# SELECT *
# FROM pg_catalog.pg_tables
# WHERE pg_tables.schemaname != 'pg_catalog' AND 'pg_tables.schemename' != 'information_schema'
# """
# cur.execute(show_tables)
# print(cur.fetchall())
# print(chars[0])

# ## Create characters table
# cur.execute(
#     """
#     CREATE TABLE cc_characters (
#     character_id SERIAL PRIMARY KEY,
#     name VARCHAR(30) NOT NULL,
#     level INTEGER NOT NULL,
#     exp INTEGER NOT NULL,
#     hp INTEGER NOT NULL,
#     strength INTEGER NOT NULL,
#     intelligence INTEGER NOT NULL,
#     dexterity INTEGER NOT NULL,
#     wisdom INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# for character in chars:
#     insert_character = """
#     INSERT INTO cc_characters
#     (name, level, exp, hp, strength, intelligence, dexterity, wisdom)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM cc_character')
# cur.fetchall()


###################### SUBCLASS: CLERIC #####################

# Create cleric table
# cur.execute(
#     """
#     CREATE TABLE clerics (
#     character_ptr_id SERIAL PRIMARY KEY,
#     using_shield INTEGER NOT NULL,
#     mana INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# Get and insert data for clerics
get_characters = 'SELECT * FROM charactercreator_cleric'
chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO clerics
#     (using_shield, mana)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM clerics')
# cur.fetchall()


###################### SUBCLASS: MAGE #####################

# Create mage table
# cur.execute(
#     """
#     CREATE TABLE mages (
#     character_ptr_id SERIAL PRIMARY KEY,
#     has_pet INTEGER NOT NULL,
#     mana INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# Get and insert data for mages
get_characters = 'SELECT * FROM charactercreator_mage'
chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO mages
#     (has_pet, mana)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM mages')
# cur.fetchall()


###################### SUBCLASS: THIEF #####################

# Create thieves table
# cur.execute(
#     """
#     CREATE TABLE thieves (
#     character_ptr_id SERIAL PRIMARY KEY,
#     is_sneaking INTEGER NOT NULL,
#     energy INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# ### Get and insert data for thieves
# get_characters = 'SELECT * FROM charactercreator_thief'
# chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO thieves
#     (is_sneaking, energy)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM thieves')
# cur.fetchall()


###################### SUBCLASS: FIGTHER #####################

# Create fighters table
# cur.execute(
#     """
#     CREATE TABLE fighters (
#     character_ptr_id SERIAL PRIMARY KEY,
#     using_shield INTEGER NOT NULL,
#     rage INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# ### Get and insert data for fighters
# get_characters = 'SELECT * FROM charactercreator_fighter'
# chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO fighters
#     (using_shield, rage)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM fighters')
# cur.fetchall()


###################### SUBCLASS: NECROMANCERS #####################

# Create necromancers table
# cur.execute(
#     """
#     CREATE TABLE necromancers (
#     character_ptr_id SERIAL PRIMARY KEY,
#     talisman_charged INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# # Get and insert data for necromancers
# get_characters = 'SELECT * FROM charactercreator_necromancer'
# chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO necromancers
#     (talisman_charged)
#     VALUES (1);"""
#     # + str(character[1]) + ";"
#     # breakpoint()
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM necromancers')
# cur.fetchall()
# # Was actually unable to get this one to work -
# # continued to get error
# # regarding line 198 having a
# # SyntaxError: at or near "1"

# # Came back and resolved by directly passing in
# # (1) instead of slicing through character tuple.
# # Works in this case bc all values were 1, but
# # not sure if this is a scalable solution ...


###################### ITEMS #####################


# Get and insert data for items
# get_itms = 'SELECT * FROM armory_item'
# itms = sl_curs.execute(get_itms).fetchall()

# for itm in itms:
#     insert_item = """
#     INSERT INTO items
#     (name, "value", weight)
#     VALUES """ + str(itm[1:]) + ";"
#     cur.execute(insert_item)
# conn.commit()

# cur.execute('SELECT * FROM items')
# cur.fetchall()


###################### SUBCLASS: WEAPONS #####################


# # Get and insert data for weapons
# get_itms = 'SELECT * FROM armory_weapon'
# itms = sl_curs.execute(get_itms).fetchall()

# for itm in itms:
#     insert_item = """
#     INSERT INTO weapons
#     (power)
#     VALUES (0);"""
#     # + str(itm[1]) + ";"
#     # breakpoint()
#     cur.execute(insert_item)
# # breakpoint()
# conn.commit()

# cur.execute('SELECT * FROM weapons')
# cur.fetchall()

# Ran into same error as necromancer subclass - must have
# something to do with itm[1:]

# All values were 0, so for some reason the solution
# was to directly pass in 0 as the value, instead
# of slicing itm tuple


###################### INVENTORY #####################


# Get and insert data for char_inventory
# get_itms = 'SELECT * FROM charactercreator_character_inventory'
# itms = sl_curs.execute(get_itms).fetchall()

# for itm in itms:
#     insert_item = """
#     INSERT INTO char_inventory
#     (character_id, item_id)
#     VALUES """ + str(itm[1:]) + ";"
#     cur.execute(insert_item)
# conn.commit()

# cur.execute('SELECT * FROM char_inventory')
# cur.fetchall()

import psycopg2
import sqlite3

# Establish PostgreSQL connection
DB_NAME = 'axajhxor'
DB_USER = 'axajhxor'
DB_PW = '<PASSWORD>'
DB_HOST = 'rajje.db.elephantsql.com'

conn = psycopg2.connect(
    dbname=DB_NAME,
    user=DB_USER,
    password=<PASSWORD>,
    host=DB_HOST)
# print(type(conn))

cur = conn.cursor()
# print(type(cur))

# Establish sqlite3 connection
sl_conn = sqlite3.connect("data/rpg_db_original.sqlite3")
sl_curs = sl_conn.cursor()
# print(type(sl_conn))
# print(type(sl_curs))
# row_count = 'SELECT COUNT(*) FROM charactercreator_character'
# print(sl_curs.execute(row_count).fetchall()} characters)


###################### INSERTS ###########################


# Get and insert data for charcreator_characters
get_characters = 'SELECT * FROM charactercreator_character'
chars = sl_curs.execute(get_characters).fetchall()

# show_tables = """
# SELECT *
# FROM pg_catalog.pg_tables
# WHERE pg_tables.schemaname != 'pg_catalog' AND 'pg_tables.schemename' != 'information_schema'
# """
# cur.execute(show_tables)
# print(cur.fetchall())
# print(chars[0])

# ## Create characters table
# cur.execute(
#     """
#     CREATE TABLE cc_characters (
#     character_id SERIAL PRIMARY KEY,
#     name VARCHAR(30) NOT NULL,
#     level INTEGER NOT NULL,
#     exp INTEGER NOT NULL,
#     hp INTEGER NOT NULL,
#     strength INTEGER NOT NULL,
#     intelligence INTEGER NOT NULL,
#     dexterity INTEGER NOT NULL,
#     wisdom INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# for character in chars:
#     insert_character = """
#     INSERT INTO cc_characters
#     (name, level, exp, hp, strength, intelligence, dexterity, wisdom)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM cc_character')
# cur.fetchall()


###################### SUBCLASS: CLERIC #####################

# Create cleric table
# cur.execute(
#     """
#     CREATE TABLE clerics (
#     character_ptr_id SERIAL PRIMARY KEY,
#     using_shield INTEGER NOT NULL,
#     mana INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# Get and insert data for clerics
get_characters = 'SELECT * FROM charactercreator_cleric'
chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO clerics
#     (using_shield, mana)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM clerics')
# cur.fetchall()


###################### SUBCLASS: MAGE #####################

# Create mage table
# cur.execute(
#     """
#     CREATE TABLE mages (
#     character_ptr_id SERIAL PRIMARY KEY,
#     has_pet INTEGER NOT NULL,
#     mana INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# Get and insert data for mages
get_characters = 'SELECT * FROM charactercreator_mage'
chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO mages
#     (has_pet, mana)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM mages')
# cur.fetchall()


###################### SUBCLASS: THIEF #####################

# Create thieves table
# cur.execute(
#     """
#     CREATE TABLE thieves (
#     character_ptr_id SERIAL PRIMARY KEY,
#     is_sneaking INTEGER NOT NULL,
#     energy INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# ### Get and insert data for thieves
# get_characters = 'SELECT * FROM charactercreator_thief'
# chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO thieves
#     (is_sneaking, energy)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM thieves')
# cur.fetchall()


###################### SUBCLASS: FIGTHER #####################

# Create fighters table
# cur.execute(
#     """
#     CREATE TABLE fighters (
#     character_ptr_id SERIAL PRIMARY KEY,
#     using_shield INTEGER NOT NULL,
#     rage INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# ### Get and insert data for fighters
# get_characters = 'SELECT * FROM charactercreator_fighter'
# chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO fighters
#     (using_shield, rage)
#     VALUES """ + str(character[1:]) + ";"
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM fighters')
# cur.fetchall()


###################### SUBCLASS: NECROMANCERS #####################

# Create necromancers table
# cur.execute(
#     """
#     CREATE TABLE necromancers (
#     character_ptr_id SERIAL PRIMARY KEY,
#     talisman_charged INTEGER NOT NULL
#     );
#     """
# )
# conn.commit()

# # Get and insert data for necromancers
# get_characters = 'SELECT * FROM charactercreator_necromancer'
# chars = sl_curs.execute(get_characters).fetchall()

# for character in chars:
#     insert_character = """
#     INSERT INTO necromancers
#     (talisman_charged)
#     VALUES (1);"""
#     # + str(character[1]) + ";"
#     # breakpoint()
#     cur.execute(insert_character)
# conn.commit()

# cur.execute('SELECT * FROM necromancers')
# cur.fetchall()
# # Was actually unable to get this one to work -
# # continued to get error
# # regarding line 198 having a
# # SyntaxError: at or near "1"

# # Came back and resolved by directly passing in
# # (1) instead of slicing through character tuple.
# # Works in this case bc all values were 1, but
# # not sure if this is a scalable solution ...


###################### ITEMS #####################


# Get and insert data for items
# get_itms = 'SELECT * FROM armory_item'
# itms = sl_curs.execute(get_itms).fetchall()

# for itm in itms:
#     insert_item = """
#     INSERT INTO items
#     (name, "value", weight)
#     VALUES """ + str(itm[1:]) + ";"
#     cur.execute(insert_item)
# conn.commit()

# cur.execute('SELECT * FROM items')
# cur.fetchall()


###################### SUBCLASS: WEAPONS #####################


# # Get and insert data for weapons
# get_itms = 'SELECT * FROM armory_weapon'
# itms = sl_curs.execute(get_itms).fetchall()

# for itm in itms:
#     insert_item = """
#     INSERT INTO weapons
#     (power)
#     VALUES (0);"""
#     # + str(itm[1]) + ";"
#     # breakpoint()
#     cur.execute(insert_item)
# # breakpoint()
# conn.commit()

# cur.execute('SELECT * FROM weapons')
# cur.fetchall()

# Ran into same error as necromancer subclass - must have
# something to do with itm[1:]

# All values were 0, so for some reason the solution
# was to directly pass in 0 as the value, instead
# of slicing itm tuple


###################### INVENTORY #####################


# Get and insert data for char_inventory
# get_itms = 'SELECT * FROM charactercreator_character_inventory'
# itms = sl_curs.execute(get_itms).fetchall()

# for itm in itms:
#     insert_item = """
#     INSERT INTO char_inventory
#     (character_id, item_id)
#     VALUES """ + str(itm[1:]) + ";"
#     cur.execute(insert_item)
# conn.commit()

# cur.execute('SELECT * FROM char_inventory')
# cur.fetchall()

# Establish PostgreSQL connection # print(type(conn)) # print(type(cur)) # Establish sqlite3 connection # print(type(sl_conn)) # print(type(sl_curs)) # row_count = 'SELECT COUNT(*) FROM charactercreator_character' # print(sl_curs.execute(row_count).fetchall()} characters) ###################### INSERTS ########################### # Get and insert data for charcreator_characters # show_tables = """ # SELECT * # FROM pg_catalog.pg_tables # WHERE pg_tables.schemaname != 'pg_catalog' AND 'pg_tables.schemename' != 'information_schema' # """ # cur.execute(show_tables) # print(cur.fetchall()) # print(chars[0]) # ## Create characters table # cur.execute( #     """ #     CREATE TABLE cc_characters ( #     character_id SERIAL PRIMARY KEY, #     name VARCHAR(30) NOT NULL, #     level INTEGER NOT NULL, #     exp INTEGER NOT NULL, #     hp INTEGER NOT NULL, #     strength INTEGER NOT NULL, #     intelligence INTEGER NOT NULL, #     dexterity INTEGER NOT NULL, #     wisdom INTEGER NOT NULL #     ); #     """ # ) # conn.commit() # for character in chars: #     insert_character = """ #     INSERT INTO cc_characters #     (name, level, exp, hp, strength, intelligence, dexterity, wisdom) #     VALUES """ + str(character[1:]) + ";" #     cur.execute(insert_character) # conn.commit() # cur.execute('SELECT * FROM cc_character') # cur.fetchall() ###################### SUBCLASS: CLERIC ##################### # Create cleric table # cur.execute( #     """ #     CREATE TABLE clerics ( #     character_ptr_id SERIAL PRIMARY KEY, #     using_shield INTEGER NOT NULL, #     mana INTEGER NOT NULL #     ); #     """ # ) # conn.commit() # Get and insert data for clerics # for character in chars: #     insert_character = """ #     INSERT INTO clerics #     (using_shield, mana) #     VALUES """ + str(character[1:]) + ";" #     cur.execute(insert_character) # conn.commit() # cur.execute('SELECT * FROM clerics') # cur.fetchall() ###################### SUBCLASS: MAGE ##################### # Create mage table # cur.execute( #     """ #     CREATE TABLE mages ( #     character_ptr_id SERIAL PRIMARY KEY, #     has_pet INTEGER NOT NULL, #     mana INTEGER NOT NULL #     ); #     """ # ) # conn.commit() # Get and insert data for mages # for character in chars: #     insert_character = """ #     INSERT INTO mages #     (has_pet, mana) #     VALUES """ + str(character[1:]) + ";" #     cur.execute(insert_character) # conn.commit() # cur.execute('SELECT * FROM mages') # cur.fetchall() ###################### SUBCLASS: THIEF ##################### # Create thieves table # cur.execute( #     """ #     CREATE TABLE thieves ( #     character_ptr_id SERIAL PRIMARY KEY, #     is_sneaking INTEGER NOT NULL, #     energy INTEGER NOT NULL #     ); #     """ # ) # conn.commit() # ### Get and insert data for thieves # get_characters = 'SELECT * FROM charactercreator_thief' # chars = sl_curs.execute(get_characters).fetchall() # for character in chars: #     insert_character = """ #     INSERT INTO thieves #     (is_sneaking, energy) #     VALUES """ + str(character[1:]) + ";" #     cur.execute(insert_character) # conn.commit() # cur.execute('SELECT * FROM thieves') # cur.fetchall() ###################### SUBCLASS: FIGTHER ##################### # Create fighters table # cur.execute( #     """ #     CREATE TABLE fighters ( #     character_ptr_id SERIAL PRIMARY KEY, #     using_shield INTEGER NOT NULL, #     rage INTEGER NOT NULL #     ); #     """ # ) # conn.commit() # ### Get and insert data for fighters # get_characters = 'SELECT * FROM charactercreator_fighter' # chars = sl_curs.execute(get_characters).fetchall() # for character in chars: #     insert_character = """ #     INSERT INTO fighters #     (using_shield, rage) #     VALUES """ + str(character[1:]) + ";" #     cur.execute(insert_character) # conn.commit() # cur.execute('SELECT * FROM fighters') # cur.fetchall() ###################### SUBCLASS: NECROMANCERS ##################### # Create necromancers table # cur.execute( #     """ #     CREATE TABLE necromancers ( #     character_ptr_id SERIAL PRIMARY KEY, #     talisman_charged INTEGER NOT NULL #     ); #     """ # ) # conn.commit() # # Get and insert data for necromancers # get_characters = 'SELECT * FROM charactercreator_necromancer' # chars = sl_curs.execute(get_characters).fetchall() # for character in chars: #     insert_character = """ #     INSERT INTO necromancers #     (talisman_charged) #     VALUES (1);""" #     # + str(character[1]) + ";" #     # breakpoint() #     cur.execute(insert_character) # conn.commit() # cur.execute('SELECT * FROM necromancers') # cur.fetchall() # # Was actually unable to get this one to work - # # continued to get error # # regarding line 198 having a # # SyntaxError: at or near "1" # # Came back and resolved by directly passing in # # (1) instead of slicing through character tuple. # # Works in this case bc all values were 1, but # # not sure if this is a scalable solution ... ###################### ITEMS ##################### # Get and insert data for items # get_itms = 'SELECT * FROM armory_item' # itms = sl_curs.execute(get_itms).fetchall() # for itm in itms: #     insert_item = """ #     INSERT INTO items #     (name, "value", weight) #     VALUES """ + str(itm[1:]) + ";" #     cur.execute(insert_item) # conn.commit() # cur.execute('SELECT * FROM items') # cur.fetchall() ###################### SUBCLASS: WEAPONS ##################### # # Get and insert data for weapons # get_itms = 'SELECT * FROM armory_weapon' # itms = sl_curs.execute(get_itms).fetchall() # for itm in itms: #     insert_item = """ #     INSERT INTO weapons #     (power) #     VALUES (0);""" #     # + str(itm[1]) + ";" #     # breakpoint() #     cur.execute(insert_item) # # breakpoint() # conn.commit() # cur.execute('SELECT * FROM weapons') # cur.fetchall() # Ran into same error as necromancer subclass - must have # something to do with itm[1:] # All values were 0, so for some reason the solution # was to directly pass in 0 as the value, instead # of slicing itm tuple ###################### INVENTORY ##################### # Get and insert data for char_inventory # get_itms = 'SELECT * FROM charactercreator_character_inventory' # itms = sl_curs.execute(get_itms).fetchall() # for itm in itms: #     insert_item = """ #     INSERT INTO char_inventory #     (character_id, item_id) #     VALUES """ + str(itm[1:]) + ";" #     cur.execute(insert_item) # conn.commit() # cur.execute('SELECT * FROM char_inventory') # cur.fetchall()