# # step1: import sqlite3
# import sqlite3
# from employee import Employee

# # step2: create a connect to the file or memory with connect method as below ans assign with a variable
# conn = sqlite3.connect('employee.db')

# # //with this cursor method now we can use sql commands
# c = conn.cursor()

# # c.execute("""CREATE TABLE employees (
# #     first text,
# #     last text,
# #     pay integer
# # )""")

# emp_1 = Employee('John', 'Doe', 80000)
# emp_1 = Employee('Jane', 'Doe', 90000)

# print(emp_1.first)
# print(emp_1.last)
# print(emp_1.pay)

# # c.execute("INSERT INTO employees VALUES ('Corey', 'Schafer', 50000)")

# # conn.commit()

# c.execute("SELECT * FROM employees WHERE last='Schafer'")

# print(c.fetchall())

# conn.commit()

# conn.close()



# // My Practise

import sqlite3
from employee import Employee

conn = sqlite3.connect(':memory:')

c = conn.cursor()

c.execute("""CREATE TABLE employees(
           first text,
           last text,
           pay integer
            )""")

def insert_emp(emp):
    with conn:
        c.execute("INSERT INTO employees VALUES (:first, :last, :pay)", {'first': emp.first, 'last': emp.last, 'pay': emp.pay})

def get_emps_by_name(lastname):
    c.execute("SELECT * from employees WHERE last=:last", {'last': lastname})
    return c.fetchall()        

def update_pay(emp, pay):
    with conn:
        c.execute("""UPDATE employees SET pay=:pay WHERE first=:first AND last=:last""",
        {'first':emp.first, 'last':emp.last, 'pay': pay})    

def remove_emp(emp):
    with conn:
        c.execute("DELETE from employees WHERE first=:first AND last=:last",
        {'first': emp.first, 'last': emp.last})

emp_1 = Employee('John', 'Doe', 80000)
emp_2 = Employee('Jane', 'Doe', 90000)

insert_emp(emp_1)
insert_emp(emp_2)

emps = get_emps_by_name('Doe')
print(emps)

update_pay(emp_2, 95000)
remove_emp(emp_1)

emps = get_emps_by_name('Doe')
print(emps)

conn.close()