import sqlite3
import os

def initialize_database(clear_data=False):
    db_path = os.path.abspath(os.path.join(os.path.dirname(__file__), "../college.db"))
    print(f"Initializing database at: {db_path}")
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Create tables
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS student (
        student_id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT,
        last_name TEXT,
        date_of_birth DATE,
        email TEXT,
        phone_number TEXT,
        major TEXT,
        year_of_enrollment INTEGER
    );""")

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS employee (
        employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT,
        last_name TEXT,
        email TEXT,
        department TEXT,
        position TEXT,
        salary REAL,
        date_of_joining DATE
    );""")

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS course (
        course_id INTEGER PRIMARY KEY AUTOINCREMENT,
        course_name TEXT,
        course_code TEXT,
        instructor_id INTEGER,
        department TEXT,
        credits INTEGER,
        semester TEXT,
        FOREIGN KEY (instructor_id) REFERENCES employee(employee_id)
    );""")

    if clear_data:
        cursor.execute("DELETE FROM course;")
        cursor.execute("DELETE FROM student;")
        cursor.execute("DELETE FROM employee;")

    # Insert sample data
    students = [
        ("Alice", "Johnson", "2001-06-15", "alice@example.com", "1234567890", "Computer Science", 2019),
        ("Bob", "Smith", "2000-04-22", "bob@example.com", "0987654321", "Mechanical Engineering", 2018),
        ("Charlie", "Brown", "2002-01-10", "charlie@example.com", "1112223333", "ECE", 2020),
        ("Daisy", "Miller", "2001-12-12", "daisy@example.com", "2223334444", "Civil", 2019),
        ("Ethan", "Lee", "2000-05-05", "ethan@example.com", "3334445555", "IT", 2018)
    ]
    cursor.executemany("INSERT INTO student (first_name, last_name, date_of_birth, email, phone_number, major, year_of_enrollment) VALUES (?, ?, ?, ?, ?, ?, ?);", students)

    employees = [
        ("Drake", "Miller", "drake@example.com", "CSE", "Professor", 75000.00, "2015-08-01"),
        ("Laura", "White", "laura@example.com", "ECE", "Assistant Professor", 65000.00, "2018-07-10"),
        ("James", "Hall", "james@example.com", "IT", "Lecturer", 55000.00, "2019-01-15")
    ]
    cursor.executemany("INSERT INTO employee (first_name, last_name, email, department, position, salary, date_of_joining) VALUES (?, ?, ?, ?, ?, ?, ?);", employees)

    courses = [
        ("Database Systems", "CS301", 1, "CSE", 4, "Fall"),
        ("Digital Electronics", "EC202", 2, "ECE", 3, "Spring"),
        ("Web Technologies", "IT301", 3, "IT", 4, "Spring")
    ]
    cursor.executemany("INSERT INTO course (course_name, course_code, instructor_id, department, credits, semester) VALUES (?, ?, ?, ?, ?, ?);", courses)

    conn.commit()
    conn.close()
    print("✅ Database initialized and sample data inserted.")