operations.py

import sqlite3
from getpass import getpass

# ---------------- DATABASE CONNECTION ----------------
conn = sqlite3.connect("company.db")
cursor = conn.cursor()

# ---------------- TABLE CREATION ----------------
cursor.execute("""
CREATE TABLE IF NOT EXISTS users(
    username TEXT PRIMARY KEY,
    password TEXT NOT NULL
)
""")

cursor.execute("""
CREATE TABLE IF NOT EXISTS department(
    dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
    dept_name TEXT UNIQUE NOT NULL
)
""")

cursor.execute("""
CREATE TABLE IF NOT EXISTS employee(
    emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    salary REAL NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY(dept_id) REFERENCES department(dept_id)
)
""")

# ---------------- DEFAULT DATA (NO DUPLICATES) ----------------

# default admin
cursor.execute("INSERT OR IGNORE INTO users VALUES ('admin','admin123')")

# default departments
default_departments = ['HR', 'IT', 'Finance']
for d in default_departments:
    cursor.execute("INSERT OR IGNORE INTO department(dept_name) VALUES(?)", (d,))


# ---------------- EMPLOYEE OPERATIONS ----------------
def view_employees():
    print("\n------ EMPLOYEE DETAILS ------")
    cursor.execute("""
        SELECT e.emp_id, e.name, e.salary, d.dept_name
        FROM employee e
        LEFT JOIN department d ON e.dept_id=d.dept_id
    """)
    rows = cursor.fetchall()
    if not rows:
        print("No employees found")
    for r in rows:
        print(r)


def add_employee():
    print("\n------ ADD EMPLOYEE ------")
    name = input("Enter Name: ")
    salary = float(input("Enter Salary: "))
    dept_id = int(input("Enter Department ID: "))

    cursor.execute("SELECT * FROM department WHERE dept_id=?", (dept_id,))
    if cursor.fetchone() is None:
        print("Department not found\n")
        return

    cursor.execute("INSERT INTO employee(name,salary,dept_id) VALUES(?,?,?)",
                   (name, salary, dept_id))
    conn.commit()
    print("Employee added\n")


def update_salary():
    print("\n------ UPDATE SALARY ------")
    emp_id = int(input("Enter Employee ID: "))
    new_salary = float(input("Enter New Salary: "))

    cursor.execute("UPDATE employee SET salary=? WHERE emp_id=?",
                   (new_salary, emp_id))
    conn.commit()

    if cursor.rowcount == 0:
        print("Employee not found\n")
    else:
        print("Salary updated\n")


def update_employee_department():
    print("\n------ UPDATE EMPLOYEE DEPARTMENT ------")
    emp_id = int(input("Enter Employee ID: "))
    dept_id = int(input("Enter New Department ID: "))

    cursor.execute("SELECT * FROM department WHERE dept_id=?", (dept_id,))
    if cursor.fetchone() is None:
        print("Department not found\n")
        return

    cursor.execute("UPDATE employee SET dept_id=? WHERE emp_id=?",
                   (dept_id, emp_id))
    conn.commit()
    print("Department updated\n")


def delete_employee():
    print("\n------ DELETE EMPLOYEE ------")
    emp_id = int(input("Enter Employee ID: "))
    cursor.execute("DELETE FROM employee WHERE emp_id=?", (emp_id,))
    conn.commit()
    print("Employee deleted\n")


# ---------------- DEPARTMENT OPERATIONS ----------------
def view_departments():
    print("\n------ DEPARTMENTS ------")
    cursor.execute("SELECT * FROM department")
    for r in cursor.fetchall():
        print(r)


def add_department():
    print("\n------ ADD DEPARTMENT ------")
    dept_name = input("Enter Department Name: ")
    cursor.execute("INSERT OR IGNORE INTO department(dept_name) VALUES(?)",
                   (dept_name,))
    conn.commit()
    print("Department added\n")


# ---------------- USER OPERATIONS ----------------
def login():
    print("\n------ LOGIN ------")
    username = input("Enter username: ")
    password = getpass("Enter password: ")

    cursor.execute(
        "SELECT * FROM users WHERE username=? AND password=?",
        (username, password)
    )

    return cursor.fetchone() is not None


def add_user():
    print("\n------ ADD USER ------")
    username = input("Enter new username: ")
    password = getpass("Enter password: ")

    cursor.execute("INSERT INTO users VALUES(?,?)", (username, password))
    conn.commit()
    print("User added\n")
# ---------------- Analysis and Insights ----------------
def nth_highest_salary():
    n=int(input('Enter the n value:'))
    cursor.execute("""SELECT * FROM employee e WHERE (SELECT count(distinct e1.salary) FROM employee e1 WHERE e1.salary>=e.salary)=? """,(n,))
    print(cursor.fetchall())
    conn.commit()
def nth_lowest_salary():
    n=int(input('Enter the n value:'))
    cursor.execute("""SELECT * FROM employee e WHERE (SELECT count(distinct e1.salary) FROM employee e1 WHERE e1.salary<=e.salary)=? """,(n,))
    print(cursor.fetchall())
    conn.commit()
def no_of_emp_in_deptartments():
    cursor.execute("""SELECT d.dept_name ,count(e.emp_id) FROM employee e join department d on e.dept_id=d.dept_id GROUP BY e.dept_id """)
    print(cursor.fetchall())
    conn.commit()
def  employeename_departmentname():
    cursor.execute("""SELECT e.name,d.dept_name FROM employee e join department d on e.dept_id=d.dept_id """)
    rows = cursor.fetchall()
    if not rows:
        print("No employees found")
    for r in rows:
        print(r)
    conn.commit()