import sqlite3
from time import sleep

with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    cursor.execute("DROP TABLE IF EXISTS empleados;")
    query = """CREATE TABLE IF NOT EXISTS empleados(
                id integer PRIMARY KEY,
                nombre text NOT NULL,
                depto text NOT NULL,
                salario integer);"""
    cursor.execute(query)
    print('1)\n'+query+'\n')
    query = """INSERT INTO empleados(id,nombre,depto,salario) 
               VALUES("1","Bob","Ventas","25000")"""
    print('2)\n'+query+'\n')
    cursor.execute(query)
    db.commit()

with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    query = "INSERT INTO empleados(id,nombre,depto,salario) VALUES(?,?,?,?)"
    print('3)\n'+query+'\n')
    new_id = input("Ingresa el ID: ")
    new_name = input("Ingresa el nombre: ")
    new_dept = input("Ingresa el departamento: ")
    new_salary = input("Enter salary: ")
    cursor.execute(query, (new_id,new_name,new_dept,new_salary))
    db.commit()
    query = "SELECT * FROM empleados"
    cursor.execute(query)
    print('4)\n'+query+'\n')
    print(cursor.fetchall())

with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    query = "SELECT * FROM empleados"
    print('5)\n'+query+'\n')
    cursor.execute(query)
    for x in cursor.fetchall():
        print(x)

with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    query = "SELECT * FROM empleados ORDER BY nombre"
    print('6)\n'+query+'\n')
    cursor.execute(query)
    print("ID".rjust(4), "NOMBRE".ljust(10), "DEPTO".ljust(8), "SUELDO".rjust(6))
    for x in cursor.fetchall():
        print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8), str(x[3]).rjust(6))
    
with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    query = "SELECT * FROM empleados WHERE salario>20000"
    print('7)\n'+query+'\n')
    cursor.execute(query)
    print("ID".rjust(4), "NOMBRE".ljust(10), "DEPTO".ljust(8), "SUELDO".rjust(6))
    for x in cursor.fetchall():
        print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8), str(x[3]).rjust(6))
    query = "SELECT * FROM empleados WHERE depto='Ventas'"
    print('8)\n'+query+'\n')
    cursor.execute(query)
    print("ID".rjust(4), "NOMBRE".ljust(10), "DEPTO".ljust(8), "SUELDO".rjust(6))
    for x in cursor.fetchall():
        print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8), str(x[3]).rjust(6))

with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    cursor.execute("DROP TABLE IF EXISTS deptos;")
    query = """CREATE TABLE IF NOT EXISTS deptos(
                id integer PRIMARY KEY,
                admin text NOT NULL,
                depto text NOT NULL);"""
    cursor.execute(query)
    sleep(0.1)
    query = """INSERT INTO deptos(id,admin,depto)
               VALUES("1","Rob","Ventas"),("2", "Zerio","TI")"""
    cursor.execute(query)
    query = """SELECT empleados.id, empleados.nombre, deptos.admin
        FROM empleados, deptos WHERE empleados.depto=deptos.depto
        AND empleados.salario >20000"""
    print('9)\n'+query+'\n')
    cursor.execute(query)
    print("ID".rjust(4), "NOMBRE".ljust(10), "ADMIN".ljust(8))
    for x in cursor.fetchall():
        print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8))

with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    query = "SELECT * FROM empleados WHERE depto=?"
    print('10)\n'+query+'\n')
    que_depto = input("Ingresa un departmento (Ventas o TI): ")
    cursor.execute(query,[que_depto])
    print("ID".rjust(4), "NOMBRE".ljust(10), "DEPTO".ljust(8), "SUELDO".rjust(6))
    for x in cursor.fetchall():
        print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8), str(x[3]).rjust(6))

with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    query = """SELECT empleados.id, empleados.nombre, deptos.admin
            FROM empleados, deptos WHERE empleados.depto=deptos.depto;"""
    print('11)\n'+query+'\n')
    print("ID".rjust(4), "NOMBRE".ljust(10), "ADMIN".ljust(8))
    for x in cursor.fetchall():
        print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8))

with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    query = "UPDATE empleados SET nombre='Tony' WHERE id=1"
    print('11)\n'+query+'\n')
    cursor.execute(query)
    db.commit()

with sqlite3.connect("./db/compania.db") as db:
    cursor = db.cursor()
    cursor.execute("SELECT * FROM empleados WHERE id=1")
    print(cursor.fetchone())
    query = "DELETE FROM empleados WHERE id=1"
    print('12)\n'+query+'\n')
    cursor.execute(query)
    db.commit()