create_db.mysql

CREATE DATABASE IF NOT EXISTS Astral;
USE Astral; -- Switch to our newly created database.

-- We need a table to store all courses supported in this
-- institution
CREATE TABLE IF NOT EXISTS Courses (
        CourseID varchar(32) NOT NULL, /* MD5 Sum of Course, Subject Code, Regulation */
        Course varchar(10) NOT NULL,
        SubjectCode varchar(10) NOT NULL,
	Regulation int NOT NULL,
        Title varchar(100),
        Description varchar(500),
        Syllabus varchar(500),
	PRIMARY KEY (CourseID)
);

-- We need a table to store basic info on all the class sites.
CREATE TABLE IF NOT EXISTS ClassSites (
        ClassID varchar(32) NOT NULL, /* MD5 sum of CourseID,
                                                    Year,
                                                    Season and UserIncharge. */
        CourseID varchar(32) NOT NULL, 
        Year varchar(10) NOT NULL,
        Season varchar(10) NOT NULL,
        ClassPage varchar(100),
        UserIncharge varchar(50),
	Lecture varchar(100),
	Timings varchar(100),
	PRIMARY KEY (ClassID)
);

-- We need a table to store all announcements
CREATE TABLE IF NOT EXISTS Announcements (
        ClassID varchar(32) NOT NULL,
        MsgID varchar(32) NOT NULL,  /* MD5 sum of timestamp and classid */
	Msg varchar(200),
        MsgTimeStamp timestamp NOT NULL,
	Author varchar(100),
        PRIMARY KEY (MsgID)
);

CREATE TABLE IF NOT EXISTS Materials (
	ClassID varchar(32) NOT NULL,
	MaterialID varchar(32) NOT NULL, /* MD5 sum of ClassID + MaterialTitle + MaterialTimestamp */
	MaterialTitle varchar(200),
	MaterialDescription varchar(500),
	MaterialTimestamp timestamp NOT NULL,
	PRIMARY KEY (MaterialID)
);

-- We need a table to store the login credentials for all
-- users.
-- Like professors, administrator and head of department(tentative).
CREATE TABLE IF NOT EXISTS Users (
        UserID varchar(50) NOT NULL,
        LegalName varchar(100),
        EmailID varchar(50),
        PwdHash varchar(32) NOT NULL,
        PRIMARY KEY (UserID)
);

CREATE TABLE IF NOT EXISTS SiteConfig (
	ValueKey varchar(30) NOT NULL,
	Value varchar(200),
        PRIMARY KEY (ValueKey)
);

-- Insert the default admin password
INSERT INTO `Users` (UserID , LegalName, PwdHash ) 
VALUES ('administrator', 'Administrator' , '200ceb26807d6bf99fd6f4f0d1ca54d4');

-- Insert default value for site config
INSERT INTO `SiteConfig` 
VALUES ('basicInfo',  '{"fromAcademicYear":"2020","toAcademicYear":"2021"}');

SELECT 'Database setup completed successfully!' AS 'Astral'; -- Hackaround for print.