import gradio as gr
import sqlite3
from PIL import Image
import os
import pandas as pd

# Database setup
DB_PATH = "students.db"
os.makedirs("uploads", exist_ok=True)

def setup_database():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            age INTEGER,
            image_path TEXT
        )
    """)
    conn.commit()
    conn.close()

def register_student(name, age, image_file):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Save image to the uploads directory
    if image_file is not None:
        image_path = f"uploads/{name.replace(' ', '_')}_{os.path.basename(image_file)}"
        os.rename(image_file, image_path)  # Move file to uploads directory
    else:
        image_path = None

    cursor.execute("INSERT INTO students (name, age, image_path) VALUES (?, ?, ?)", (name, age, image_path))
    conn.commit()
    conn.close()
    return f"Student {name} registered successfully!"

def view_students():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, age, image_path FROM students")
    records = cursor.fetchall()
    conn.close()

    # Format data for display
    if records:
        data = []
        for record in records:
            student_id, name, age, image_path = record
            if image_path and os.path.exists(image_path):
                image = Image.open(image_path)
            else:
                image = None
            data.append({"ID": student_id, "Name": name, "Age": age, "Image": image})
        return pd.DataFrame(data).drop(columns=["Image"]).to_string(), data  # Display text table without images
    else:
        return "No records found.", None

# Initialize the database
setup_database()

# Gradio Interface
with gr.Blocks() as demo:
    with gr.Row():
        gr.Markdown("### Student Registration")

    with gr.Row():
        name_input = gr.Textbox(label="Name", placeholder="Enter the student's name")
        age_input = gr.Number(label="Age", precision=0)
        image_input = gr.Image(label="Upload Image", type="filepath")

    register_button = gr.Button("Register Student")
    output = gr.Textbox(label="Status")

    register_button.click(
        register_student,
        inputs=[name_input, age_input, image_input],
        outputs=output,
    )

    with gr.Row():
        gr.Markdown("### View Registered Students")

    view_button = gr.Button("View Records")
    record_output = gr.Textbox(label="Student Records")
    image_output = gr.Image(label="Images", type="pil", visible=False)  # For displaying images if needed

    def view_records_handler():
        records, images = view_students()
        return records

    view_button.click(
        view_records_handler,
        inputs=[],
        outputs=record_output,
    )

# Launch the Gradio app
demo.launch()