72 lines
3.1 KiB
SQL
72 lines
3.1 KiB
SQL
-- Create database if you haven't already
|
|
-- CREATE DATABASE IF NOT EXISTS `assignment_notifier` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
-- USE `assignment_notifier`;
|
|
|
|
-- --------------------------------------------------------
|
|
-- Table structure for table `users`
|
|
-- --------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`username` varchar(50) NOT NULL,
|
|
`password` varchar(255) NOT NULL,
|
|
`role` enum('admin','student') NOT NULL DEFAULT 'student',
|
|
`name` varchar(100) NOT NULL,
|
|
`line_token` varchar(100) DEFAULT NULL,
|
|
`telegram_chat_id` varchar(100) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `username` (`username`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Insert default admin and student
|
|
INSERT INTO `users` (`username`, `password`, `role`, `name`) VALUES
|
|
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'Administrator'), -- Password is 'password'
|
|
('student1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'student', 'Student One') -- Password is 'password'
|
|
ON DUPLICATE KEY UPDATE id=id;
|
|
|
|
-- --------------------------------------------------------
|
|
-- Table structure for table `assignments`
|
|
-- --------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `assignments` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`title` varchar(255) NOT NULL,
|
|
`description` text,
|
|
`due_date` date NOT NULL,
|
|
`student_id` int(11) NOT NULL,
|
|
`status` enum('pending','completed') NOT NULL DEFAULT 'pending',
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `student_id` (`student_id`),
|
|
CONSTRAINT `assignments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
-- Table structure for table `settings`
|
|
-- --------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `settings` (
|
|
`setting_key` varchar(50) NOT NULL,
|
|
`setting_value` text DEFAULT NULL,
|
|
PRIMARY KEY (`setting_key`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Insert default settings
|
|
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
|
|
('days_before_due', '3'),
|
|
('notification_time', '08:00'),
|
|
('footer_text', 'ระบบแจ้งเตือนส่งงาน'),
|
|
('notify_via_line', '1'),
|
|
('notify_via_telegram', '1')
|
|
ON DUPLICATE KEY UPDATE setting_key=setting_key;
|
|
|
|
-- --------------------------------------------------------
|
|
-- Table structure for table `activity_logs`
|
|
-- --------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `activity_logs` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` int(11) DEFAULT NULL,
|
|
`action` varchar(100) NOT NULL,
|
|
`details` text DEFAULT NULL,
|
|
`ip_address` varchar(45) DEFAULT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|