import sqlite3
import pandas as pd
from PIL import Image
from helper_fns import pil_to_binary

# Function to add a new student to the database
def add_student(db, firstname, lastname, matric_no, image_path):
    # Open the image from the given path
    image = Image.open(image_path)
    
    # Convert the PIL image to binary
    image_binary = pil_to_binary(image)
    
    # Connect to the existing database
    conn = sqlite3.connect(db)
    c = conn.cursor()
    
    # Insert the new data into the table
    c.execute("INSERT INTO students (first_name, last_name, matric_no, image) VALUES (?, ?, ?, ?)",
              (firstname, lastname, matric_no, image_binary))
    
    # Save (commit) the changes and close the connection
    conn.commit()
    conn.close()


def create_db(db_name, df):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()

    # Create the table if it doesn't exist
    c.execute('''CREATE TABLE IF NOT EXISTS students
             (first_name TEXT, last_name TEXT, matric_no TEXT, image BLOB)''')

    for _, row in df.iterrows():
        add_student(
        db_name, 
        row['first_name'],
        row['last_name'],
        row['matric_no'],
        row['image_path']
        )


def get_student_row(db_path, last_name, matric_no):
    """
    Retrieve a row from the database based on the last name and matric number.
    
    Parameters:
    db_path (str): Path to the SQLite database file.
    last_name (str): The last name of the student.
    matric_no (str): The matriculation number of the student.
    
    Returns:
    tuple: The row matching the last name and matric number, or None if not found.
    """
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    query = """
    SELECT * FROM students
    WHERE last_name = ? AND matric_no = ?
    """
    cursor.execute(query, (last_name, matric_no))
    row = cursor.fetchone()

    conn.close()
    if row:
        columns = [col[0] for col in cursor.description]
        return dict(zip(columns, row))
    else:
        return None


if __name__ == '__main__':
    create_db('students_database.db', pd.read_csv('students_df.csv'))