database.py

import mysql.connector
from mysql.connector import Error

def create_connection():
    """Create a database connection to the MySQL database."""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='study_planner_db',
            user='root',
            password='@Awais123a', 
            port=3306                       
        )
        if connection.is_connected():
            return connection
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
        return None

def create_tables(connection):
    """Create the necessary tables in the database."""
    cursor = connection.cursor()
    try:
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS Students (
                ID INT AUTO_INCREMENT PRIMARY KEY,
                Name VARCHAR(255) NOT NULL,
                Email VARCHAR(255) UNIQUE NOT NULL,
                Password VARCHAR(255) NOT NULL
            );
        """)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS Subjects (
                Subject_ID INT AUTO_INCREMENT PRIMARY KEY,
                Name VARCHAR(255) NOT NULL,
                Difficulty_Level VARCHAR(50),
                Student_ID INT,
                FOREIGN KEY (Student_ID) REFERENCES Students(ID)
            );
        """)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS Study_History (
                Record_ID INT AUTO_INCREMENT PRIMARY KEY,
                Student_ID INT,
                Subject_ID INT,
                Date DATE NOT NULL,
                Hours_Studied FLOAT NOT NULL,
                FOREIGN KEY (Student_ID) REFERENCES Students(ID),
                FOREIGN KEY (Subject_ID) REFERENCES Subjects(Subject_ID)
            );
        """)
        print("Tables created successfully.")
    except Error as e:
        print(f"Error creating tables: {e}")
    finally:
        cursor.close()

if __name__ == '__main__':
    conn = create_connection()
    if conn:
        create_tables(conn)
        conn.close()
        print("Database setup is complete.")