-- 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''',
  `subject` varchar(255) NOT NULL,
  `comment` varchar(255) NOT NULL,
  `suggestion` varchar(255) 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, 'Manage Users', 'add/edit users', 'manage-users.php', '', '', '5'),
(2, 'Manage Schools', 'add/edit schools', 'manage-schools.php', '', '', '5'),
(3, 'School List', 'view records', 'school-list.php', '', '', '5,4'),
(4, 'Class Group', 'add/edit classes', 'class.php', '', '', '5,3,2'),
(5, 'Manage Teachers', 'add/edit teachers', 'manage-teachers.php', '', '', '5,3'),
(6, 'Manage Students', 'add/edit students', 'manage-students.php', '', '', '5,3,2'),
(7, 'Survey Completion', 'student list', 'survey-completion.php', '', '', '5,3,2'),
(8, 'Site Setting', 'geese', 'siteSetting.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_staffCode` 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_staffCode`, `specific_teacherCode`) VALUES
(1, 'Henry High School', 'henry32', 'teacher'),
(2, 'Karl High School', 'karl32', 'teacher'),
(3, 'Tanuj High School', 'tanuj32', 'teacher'),
(4, 'Harsh High School', 'harsh32', 'teacher');

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

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

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

--
-- 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

INSERT INTO `survey` (`survey_id`, `order_id`, `survey_name`, `survey_url`) VALUES
(1, 1, 'Environmental Observations', ''),
(2, 2, 'Experience-Based', ''),
(3, 3, 'asd', ''),
(4, 4, 'asd', ''),
(5, 5, 'afdsf', ''),
(6, 6, 'fdas', ''),
(7, 7, 'asfas', ''),
(8, 8, 'test', ''),
(9, 9, 'test', ''),
(10, 10, 'test', ''),
(11, 11, 'test', ''),
(12, 12, 'test2', '');

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

--
-- 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_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_surveys`, `active`) VALUES
(1, 'henry', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'henry@mail.com', 0, 1, 0, '2020-09-05 10:54:55', '2020-09-05 10:54:55', '1', 10, 1),
(2, 'user1', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'user1@mail.com', 1, 2, 0, '2020-09-05 10:52:35', '2020-09-05 10:52:35', '1', 0, 1),
(3, 'user2', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'user2@mail.com', 2, 3, 0, '2020-09-08 23:52:18', '2020-09-08 23:52:18', '1', 0, 1),
(4, 'user3', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'user3@mail.com', 3, 4, 0, '2020-09-08 23:52:28', '2020-09-08 23:52:28', '1', 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, 1),
(6, 'user5', '406ab09a88fa9d3ed39bd3a99f2cf84a449a86ed9fed487af9c37c9a39ec8b25', 'user5@mail.com', 5, 2, 0, '2020-09-08 23:52:41', '2020-09-08 23:52:41', '1', 0, 1),
(7, 'test', 'd9b5f58f0b38198293971865a14074f59eba3e82595becbe86ae51f1d9f1f65e', 'testing@mail.com', 3, 3, 0, '2020-09-16 06:22:45', '2020-09-16 06:22:45', '0', 5, 1),
(8, 'test1', 'd9b5f58f0b38198293971865a14074f59eba3e82595becbe86ae51f1d9f1f65e', 'test1@mail.com', 2, 4, 0, '2020-09-16 06:39:04', '2020-09-16 06:39:04', '0', 0, 1),
(9, 'user6', '2b65aa7087006f7797831c290ca01559f6fc52c493f29f569e4dfa4b18b63cfe', 'user6@mail.com', 0, 0, 0, '2020-09-16 17:06:08', '2020-09-16 17:06:08', '0', 0, 0),
(10, 'user7', '2b65aa7087006f7797831c290ca01559f6fc52c493f29f569e4dfa4b18b63cfe', 'user7@mail.com', 0, 0, 0, '2020-09-16 18:50:11', '2020-09-16 18:50:11', 'l2SRO8FBdX', 0, 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_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;

