CREATE TABLE IF NOT EXISTS admin_login (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(20) NOT NULL,
    password VARCHAR(40) NOT NULL
);

INSERT INTO admin_login (user_name, password) VALUES
('admin', SHA1('password')) ;

CREATE TABLE IF NOT EXISTS classes (
    class_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(30) NOT NULL,
    subject_name VARCHAR(30) NOT NULL,
    meeting_dates VARCHAR(900) NOT NULL,
    student_ids VARCHAR(900) NOT NULL,
    teacher_id VARCHAR(50) NOT NULL,
    completed BOOL,
    FOREIGN KEY (teacher_id)
        REFERENCES teachers (teacher_id)
);

CREATE TABLE IF NOT EXISTS students (
    student_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name CHAR(30) NOT NULL,
    last_name CHAR(30) NOT NULL,
    profile_picture LONGBLOB NOT NULL
);

CREATE TABLE IF NOT EXISTS teachers (
    teacher_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name CHAR(30) NOT NULL,
    last_name CHAR(30) NOT NULL,
    profile_picture LONGBLOB NOT NULL,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(40) NOT NULL
);

CREATE TABLE IF NOT EXISTS attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT REFERENCES students (student_id),
    class_id INT,
    attendence_outcome CHAR,
    date CHAR
);