-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 27, 2020 at 04:07 AM
-- Server version: 10.4.11-MariaDB
-- PHP Version: 7.4.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ps2020`
--

-- --------------------------------------------------------

--
-- Table structure for table `feedback`
--

CREATE TABLE `feedback` (
  `feedback_id` smallint(6) NOT NULL,
  `user_id` int(11) NOT NULL COMMENT '0=public-user | otherwise refer to ''user_id''',
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `message` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `function`
--

CREATE TABLE `function` (
  `function_id` tinyint(4) NOT NULL,
  `function_name` varchar(255) NOT NULL,
  `function_subTitle` varchar(255) NOT NULL,
  `function_url` varchar(255) NOT NULL,
  `function_img` varchar(255) NOT NULL COMMENT 'directory of the img',
  `function_ic` varchar(255) NOT NULL,
  `accountType` varchar(255) NOT NULL COMMENT 'add '','' in between for multi-role | single=5 | multi=5,2'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `function`
--

INSERT INTO `function` (`function_id`, `function_name`, `function_subTitle`, `function_url`, `function_img`, `function_ic`, `accountType`) VALUES
(1, 'Users', 'add/edit users', 'manage-user.php', '', 'fa fa-users', '5'),
(2, 'School Statistics', 'view records', 'school-list.php', '', 'fas fa-list', '5,4'),
(3, 'Schools', 'add/edit schools', 'manage-school.php', '', 'fas fa-school', '5'),
(4, 'Class', 'add/edit classes', 'manage-class.php', '', 'fas fa-object-group', '5,3,2'),
(5, 'Teachers', 'add/edit teachers', 'manage-teacher.php', '', 'fas fa-chalkboard-teacher', '5,3'),
(6, 'Students', 'add/edit students', 'manage-student.php', '', 'fas fa-user-friends', '5,3,2'),
(7, 'Survey Completion', 'student list', 'survey-completion.php', '', 'fas fa-poll-h', '5,3,2'),
(8, 'Inbox', 'Messages/Feedback', 'feedback.php', '', 'fa fa-envelope-o', '5'),
(9, 'Site Setting', 'geese', 'site-setting.php', '', 'fa fa-cogs', '5');

-- --------------------------------------------------------

--
-- Table structure for table `school`
--

CREATE TABLE `school` (
  `school_id` int(11) NOT NULL,
  `school_name` varchar(255) NOT NULL,
  `specific_sAdminCode` varchar(255) NOT NULL,
  `specific_teacherCode` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `school_class`
--

CREATE TABLE `school_class` (
  `class_id` int(11) NOT NULL,
  `class_name` varchar(255) NOT NULL,
  `school_id` int(11) NOT NULL COMMENT 'refer to table ''school''',
  `teacher_id` int(11) NOT NULL COMMENT 'refer to ''user_id'' |  ''role'' = 2',
  `specific_classCode` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `survey`
--

CREATE TABLE `survey` (
  `survey_id` tinyint(11) NOT NULL,
  `order_id` tinyint(11) NOT NULL,
  `survey_name` varchar(255) NOT NULL,
  `survey_url` varchar(255) NOT NULL,
  `survey_exempt` tinyint(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `survey`
--

INSERT INTO `survey` (`survey_id`, `order_id`, `survey_name`, `survey_url`, `survey_exempt`) VALUES
(1, 1, 'Demographic', 'https://ps2020.cdms.westernsydney.edu.au/redcap/surveys/?s=AP3PJFJR83', 1),
(2, 2, 'Environmental Observations', 'https://ps2020.cdms.westernsydney.edu.au/redcap/surveys/?s=8XEND3994T', 0),
(3, 3, 'Experience-Based', 'https://ps2020.cdms.westernsydney.edu.au/redcap/surveys/?s=RPX4HXJRCP', 0);

-- --------------------------------------------------------

--
-- Table structure for table `user_account`
--

CREATE TABLE `user_account` (
  `user_id` int(11) NOT NULL,
  `username` char(30) NOT NULL,
  `password` char(64) NOT NULL,
  `email` varchar(255) NOT NULL,
  `accountType` tinyint(11) NOT NULL,
  `school_id` int(11) NOT NULL COMMENT '0=unallocated | ''school_id'' refer to table',
  `class_id` int(11) NOT NULL COMMENT '0=unallocated | ''class_id'' refer to table',
  `created` datetime NOT NULL,
  `lastLogin` datetime NOT NULL,
  `verifyCode` varchar(255) NOT NULL COMMENT '0=verified | this field cannot be ''NULL''',
  `completed_demographic` tinyint(4) NOT NULL,
  `completed_surveys` smallint(11) NOT NULL COMMENT 'increment when every time user completed a survey',
  `active` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user_account`
--

INSERT INTO `user_account` (`user_id`, `username`, `password`, `email`, `accountType`, `school_id`, `class_id`, `created`, `lastLogin`, `verifyCode`, `completed_demographic`, `completed_surveys`, `active`) VALUES
(1, 'geese', '3c75d0853829874e843d95b109be132857e652257a63bbcb0ff06f8fe8c78964', 'a.dadich@westernsydney.edu.au', 5, 0, 0, '2020-10-23 00:22:52', '2020-10-23 00:22:52', '0', 0, 0, 1);

-- --------------------------------------------------------

--
-- Table structure for table `user_role`
--

CREATE TABLE `user_role` (
  `role_id` tinyint(11) NOT NULL,
  `role` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user_role`
--

INSERT INTO `user_role` (`role_id`, `role`) VALUES
(0, 'Participant'),
(1, 'Student\r\n'),
(2, 'Teacher'),
(3, 'School-Admin'),
(4, 'Researcher'),
(5, 'Super-Admin');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `feedback`
--
ALTER TABLE `feedback`
  ADD PRIMARY KEY (`feedback_id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `function`
--
ALTER TABLE `function`
  ADD PRIMARY KEY (`function_id`);

--
-- Indexes for table `school`
--
ALTER TABLE `school`
  ADD PRIMARY KEY (`school_id`);

--
-- Indexes for table `school_class`
--
ALTER TABLE `school_class`
  ADD PRIMARY KEY (`class_id`),
  ADD KEY `school_id` (`school_id`),
  ADD KEY `teacher_id` (`teacher_id`);

--
-- Indexes for table `survey`
--
ALTER TABLE `survey`
  ADD PRIMARY KEY (`survey_id`);

--
-- Indexes for table `user_account`
--
ALTER TABLE `user_account`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY `accountType` (`accountType`);

--
-- Indexes for table `user_role`
--
ALTER TABLE `user_role`
  ADD PRIMARY KEY (`role_id`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `feedback`
--
ALTER TABLE `feedback`
  ADD CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user_account` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `school_class`
--
ALTER TABLE `school_class`
  ADD CONSTRAINT `school_class_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`school_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `school_class_ibfk_2` FOREIGN KEY (`teacher_id`) REFERENCES `user_account` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
