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

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

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

