import psycopg2
import psycopg2.extras
#import pandas as pd


def save_to_db_known(date,name,email):
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')

    cur=conn.cursor()
    conn.cursor()
    
    #cur.execute('CREATE TABLE known_prospects (id SERIAL PRIMARY KEY, date VARCHAR, name VARCHAR(50), email VARCHAR);')

    insert_script= 'INSERT INTO known_prospects (date, name, email) VALUES (%s,%s,%s)'
    insert_value=(date,name,email)
    cur.execute(insert_script,insert_value)
    print('Data inserted successfully')
    conn.commit()
    cur.close()
    conn.close()

    #save_to_db_known('2020-10-10','Scott','scott_wright@currently.com')

def save_to_db_unknown(date,name,email):
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')

    cur=conn.cursor()
    conn.cursor()
    
    #cur.execute('CREATE TABLE unknown_prospects (id SERIAL PRIMARY KEY, date VARCHAR, name VARCHAR(50), email VARCHAR);')

    insert_script= 'INSERT INTO unknown_prospects (date, name, email) VALUES (%s,%s,%s)'
    insert_value=(date,name,email)
    cur.execute(insert_script,insert_value)
    print('Data inserted successfully')
    conn.commit()
    cur.close()
    conn.close()

    #save_to_db_unknown('2020-10-10','Scott','scott_wright@currentlycom')

def save_to_db_realtors(date,name,email):
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')

    cur=conn.cursor()
    conn.cursor()
    
    #cur.execute('CREATE TABLE realtors (id SERIAL PRIMARY KEY, date VARCHAR, name VARCHAR(50), email VARCHAR);')

    insert_script= 'INSERT INTO realtors (date, name, email) VALUES (%s,%s,%s)'
    insert_value=(date,name,email)
    cur.execute(insert_script,insert_value)
    print('Data inserted successfully')
    conn.commit()
    cur.close()
    conn.close()

#save_to_db_realtors('5/3','Scott','scott_wright@currently.com')

def save_to_db_insurance(date,name,email,address):
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')

    cur=conn.cursor()
    conn.cursor()
    
    #cur.execute('CREATE TABLE insurance (id SERIAL PRIMARY KEY, date VARCHAR, name VARCHAR(50), email VARCHAR, address VARCHAR;')

    insert_script= 'INSERT INTO insurance (date, name, email, address) VALUES (%s,%s,%s,%s)'
    insert_value=(date,name,email,address)
    cur.execute(insert_script,insert_value)
    print('Data inserted successfully')
    conn.commit()
    cur.close()
    conn.close()

    #save_to_db_insurance('5/10','Brandon','scott_wright@currently.com','123 Main St')

def save_to_db_bankers(date,name,email):
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')
    #how to an email in python
    cur=conn.cursor()
    conn.cursor()
    
    #cur.execute('CREATE TABLE bankers (id SERIAL PRIMARY KEY, date VARCHAR, name VARCHAR(50), email VARCHAR);')

    insert_script= 'INSERT INTO bankers (date, name, email) VALUES (%s,%s,%s)'
    insert_value=(date,name,email)
    cur.execute(insert_script,insert_value)
    print('Data inserted successfully')
    conn.commit()
    cur.close()
    conn.close()

#save_to_db_bankers('5/3','Scott','scott_wright@currently.com')

# def save_to_db_birthday(date,name,email):
#     conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')
#     cur=conn.cursor()
#     conn.cursor()
    
#     cur.execute('CREATE TABLE birthday (id SERIAL PRIMARY KEY, date VARCHAR, name VARCHAR(50), email VARCHAR);')

#     insert_script= 'INSERT INTO birthday (date, name, email) VALUES (%s,%s,%s)'
#     insert_value=(date,name,email)
#     cur.execute(insert_script,insert_value)
#     print('Data inserted successfully')
#     conn.commit()
#     cur.close()
#     conn.close()

#save_to_db_birthday('12/30','Scott','scott_wright@currently.com')

def add_column_to_insurance_table():
    conn = psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')
    cur = conn.cursor()

    # Alter the table by adding a new column named "address" with data type VARCHAR(255)
    cur.execute('ALTER TABLE insurance ADD COLUMN address VARCHAR(50)')

    conn.commit()
    cur.close()
    conn.close()

#add_column_to_insurance_table()                    

def get_data():
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')
    cur=conn.cursor()
    cur.execute("SELECT * FROM known_prospects;")
    data=(cur.fetchall())
    cur.close()
    
    return data

def get_data_unknown():
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')
    cur=conn.cursor()
    cur.execute("SELECT * FROM unknown_prospects;")
    data=(cur.fetchall())
    cur.close()
    
    return data

def get_data_realtors():
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')
    cur=conn.cursor()
    cur.execute("SELECT * FROM realtors;")
    data=(cur.fetchall())
    cur.close()
    
    return data

def get_data_insurance():
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')
    cur=conn.cursor()
    cur.execute("SELECT * FROM insurance;")
    data=(cur.fetchall())
    cur.close()
    
    return data

def get_data_bankers():
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')
    cur=conn.cursor()
    cur.execute("SELECT * FROM bankers;")
    data=(cur.fetchall())
    cur.close()
    
    return data

def get_data_birthday():
    conn= psycopg2.connect('postgres://qyqfclhd:0fYqqyr1sQsb00YWU_bNLsvrz3YJA74x@lallah.db.elephantsql.com/qyqfclhd')
    cur=conn.cursor()
    cur.execute("SELECT * FROM birthday;")
    data=(cur.fetchall())
    cur.close()
    
    return data

get_data_birthday()