-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- 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;

--
-- Dumping data for table `feedback`
--

INSERT INTO `feedback` (`feedback_id`, `user_id`, `name`, `email`, `subject`, `type`, `message`) VALUES
(1, 6, 'Henry', 'henry.chunlai@gmail.com', '', 'feedback', 'dsad'),
(2, 6, 'Henry', 'henry.chunlai@gmail.com', '', 'feedback', 'dsad'),
(3, 6, 'Henry', 'henry.chunlai@gmail.com', '', 'feedback', 'dsad'),
(4, 6, 'Henry', 'henry.chunlai@gmail.com', '', 'feedback', 'dsad'),
(5, 6, 'Henry', 'henry.chunlai@gmail.com', '', 'feedback', 'dsad'),
(6, 6, 'Henry', 'henry.chunlai@gmail.com', '', 'feedback', 'dsad'),
(7, 6, 'Henry', 'henry.chunlai@gmail.com', '', 'feedback', 'dsad'),
(8, 6, 'Henry', 'henry.chunlai@gmail.com', '', 'feedback', 'dsad'),
(9, 6, 'Henry', 'henry.chunlai@gmail.com', '', 'feedback', 'samplesamplesamplesamplesamplesamplesamplesample'),
(10, 6, 'sample', 'sample@sample.com', 'sample', 'question', 'samplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesamplesample');

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

--
-- 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, 'Schools', 'add/edit schools', 'manage-school.php', '', 'fas fa-school', '5'),
(3, 'School Statistics', 'view records', 'school-list.php', '', 'fas fa-list', '5,4'),
(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;

--
-- Dumping data for table `school`
--

INSERT INTO `school` (`school_id`, `school_name`, `specific_sAdminCode`, `specific_teacherCode`) VALUES
(1, 'Parramatta High School', 'school', 'teacher'),
(2, 'Homebush Boys High School', 'school_hs_h', 'teacher_hs_h'),
(3, 'Strathfield Girls High School', 'school_hs_s', 'teacher_hs_s'),
(4, 'Canterbury High School', 'school_hs_c', 'teacher_hs_c');

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

--
-- 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;

--
-- Dumping data for table `school_class`
--

INSERT INTO `school_class` (`class_id`, `class_name`, `school_id`, `teacher_id`, `specific_classCode`) VALUES
(1, 'ps2020', 1, 1, 'cd_ps2020'),
(2, 'class 1', 2, 1, 'cd_class_1'),
(3, 'pa2004', 1, 3, 'pa2004'),
(4, 'pa2007', 2, 6, 'pa2007'),
(5, 'pa2066', 2, 6, 'pa2066');

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

--
-- 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_title` varchar(255) NOT NULL,
  `survey_url` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

INSERT INTO `survey` (`survey_id`, `order_id`, `survey_name`, `survey_title`, `survey_url`) VALUES
(1, 1, 'Adults Demographic', '', 'https://ps2020.cdms.westernsydney.edu.au/redcap/surveys/?s=8XEND3994T'),
(2, 2, 'Children Demographic', '', 'https://ps2020.cdms.westernsydney.edu.au/redcap/surveys/?s=44XNMEM4W3'),
(3, 3, 'Environmental Observations', '', 'https://ps2020.cdms.westernsydney.edu.au/redcap/surveys/?s=8XEND3994T'),
(4, 4, 'Experience-Based', '', 'https://ps2020.cdms.westernsydney.edu.au/redcap/surveys/?s=RPX4HXJRCP'),
(5, 5, 'Experience-Based (Demo)', '', 'https://ps2020.cdms.westernsydney.edu.au/redcap/surveys/?s=44XNMEM4W3');

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

--
-- 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, 'henry', '948ec410db57764da852c75cd7d60c0c4dfd2f01c2d2674f446336ab7a82fdfa', 'henry@mail.com', 2, 3, 0, '2020-09-05 10:54:55', '2020-09-05 10:54:55', '1', 0, 10, 0),
(2, 'user1', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'user1@mail.com', 1, 1, 3, '2020-09-05 10:52:35', '2020-09-05 10:52:35', '1', 0, 0, 1),
(3, 'user2', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'user2@mail.com', 2, 1, 0, '2020-09-08 23:52:18', '2020-09-08 23:52:18', '1', 0, 0, 1),
(4, 'user3', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'user3@mail.com', 3, 2, 0, '2020-09-08 23:52:28', '2020-09-08 23:52:28', '1', 0, 3, 1),
(5, 'user4', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'user4@mail.com', 4, 1, 0, '2020-09-08 23:52:32', '2020-09-08 23:52:32', '1', 0, 0, 1),
(6, 'user5', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'user5@mail.com', 5, 2, 0, '2020-09-08 23:52:32', '2020-09-08 23:52:32', '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;

