CREATE DATABASE IF NOT EXISTS attendr;
USE attendr;

START TRANSACTION;
CREATE TABLE attendance_records (
	id INTEGER NOT NULL, 
	session_id INTEGER NOT NULL, 
	student_id INTEGER NOT NULL, 
	marked_at DATETIME, 
	face_verified BOOLEAN, 
	location_verified BOOLEAN, 
	code_verified BOOLEAN, 
	student_latitude FLOAT, 
	student_longitude FLOAT, 
	distance_from_classroom FLOAT, 
	status VARCHAR(20), 
	PRIMARY KEY (id), 
	FOREIGN KEY(session_id) REFERENCES attendance_sessions (id), 
	FOREIGN KEY(student_id) REFERENCES students (id)
);
INSERT INTO `attendance_records` VALUES(1,2,1,'2025-11-22 16:39:17.926194',1,1,1,3.1222562,101.7636743,6.719226959803195314e+02,'present');
INSERT INTO `attendance_records` VALUES(2,3,1,'2025-11-22 16:46:07.075823',1,1,1,3.1222616,101.7636711,6.714557167968994236e+02,'present');
INSERT INTO `attendance_records` VALUES(3,6,1,'2025-11-22 17:12:17.627831',1,1,1,3.122261,101.7636698,6.715625427798626106e+02,'present');
INSERT INTO `attendance_records` VALUES(4,7,1,'2025-11-22 15:50:16.138101',1,1,1,0.0,0.0,10.0,'present');
INSERT INTO `attendance_records` VALUES(5,8,1,'2025-11-22 17:31:19.262173',1,1,1,3.1222586,101.7636816,6.714266242365291645e+02,'present');
CREATE TABLE attendance_sessions (
	id INTEGER NOT NULL, 
	course_name VARCHAR(100) NOT NULL, 
	classroom_id INTEGER NOT NULL, 
	lecturer_name VARCHAR(100) NOT NULL, 
	start_time DATETIME, 
	end_time DATETIME, 
	is_active BOOLEAN, 
	current_code VARCHAR(10), 
	code_generated_at DATETIME, 
	PRIMARY KEY (id), 
	FOREIGN KEY(classroom_id) REFERENCES classrooms (id)
);
INSERT INTO `attendance_sessions` VALUES(1,'AI',46,'ZHANG LING HE','2025-11-22 16:34:53.967623','2025-11-22 16:35:22.483428',0,'EAB1FQ','2025-11-22 16:34:53.977748');
INSERT INTO `attendance_sessions` VALUES(2,'AI',46,'ZHANG LING HE','2025-11-22 16:35:34.223978','2025-11-22 16:45:32.132931',0,'87JFTB','2025-11-22 16:45:23.271856');
INSERT INTO `attendance_sessions` VALUES(3,'AD',46,'ZHANG LING HE','2025-11-22 16:45:46.604739','2025-11-22 16:50:30.716018',0,'RJII20','2025-11-22 16:50:22.204453');
INSERT INTO `attendance_sessions` VALUES(4,'SECR3104 - Applications Development',46,'ZHANG LING HE','2025-11-22 17:04:10.912458','2025-11-22 17:04:36.114555',0,'86SONH','2025-11-22 17:04:32.116897');
INSERT INTO `attendance_sessions` VALUES(5,'SECR3104 - Applications Development',46,'ZHANG LING HE','2025-11-22 17:04:54.989793','2025-11-22 17:09:42.210274',0,'L8D01H','2025-11-22 17:09:33.982659');
INSERT INTO `attendance_sessions` VALUES(6,'SECJ3553 - Artificial Intelligence',46,'ZHANG LING HE','2025-11-22 17:12:01.124969','2025-11-22 17:26:36.046635',0,'446MC7','2025-11-22 17:26:33.821599');
INSERT INTO `attendance_sessions` VALUES(7,'TEST101 - Testing',1,'Test Lecturer','2025-11-22 15:20:16.131518','2025-11-22 16:20:16.131518',0,NULL,NULL);
INSERT INTO `attendance_sessions` VALUES(8,'SECR1013 - Digital Logic',46,'ZHANG LING HE','2025-11-22 17:30:15.307311','2025-11-22 17:31:23.154043',0,'D5DYN5','2025-11-22 17:31:15.654521');
CREATE TABLE classrooms (
	id INTEGER NOT NULL, 
	name VARCHAR(100) NOT NULL, 
	building VARCHAR(100), 
	latitude FLOAT NOT NULL, 
	longitude FLOAT NOT NULL, 
	radius_meters INTEGER, 
	PRIMARY KEY (id)
);
INSERT INTO `classrooms` VALUES(1,'N28-105-01','N28 - Faculty of Computing',1.5638,103.6388,2000);
INSERT INTO `classrooms` VALUES(2,'N28-116-02','N28 - Faculty of Computing',1.5639,103.6388,2000);
INSERT INTO `classrooms` VALUES(3,'N28-112-02','N28 - Faculty of Computing',1.564,103.6388,2000);
INSERT INTO `classrooms` VALUES(4,'N28-111-01','N28 - Faculty of Computing',1.5641,103.6388,2000);
INSERT INTO `classrooms` VALUES(5,'N28-106-01','N28 - Faculty of Computing',1.5642,103.6388,2000);
INSERT INTO `classrooms` VALUES(6,'N28-107-01','N28 - Faculty of Computing',1.5643,103.6388,2000);
INSERT INTO `classrooms` VALUES(7,'N28-108-01','N28 - Faculty of Computing',1.5644,103.6388,2000);
INSERT INTO `classrooms` VALUES(8,'N28-351-02','N28 - Level 3',1.5645,103.6388,2000);
INSERT INTO `classrooms` VALUES(9,'N28-301-01','N28 - Level 3',1.5646,103.6388,2000);
INSERT INTO `classrooms` VALUES(10,'N28-301-01B','N28 - Level 3',1.5647,103.6388,2000);
INSERT INTO `classrooms` VALUES(11,'N28A-BT1','N28A - Tutorial Block',1.5638,103.6389,2000);
INSERT INTO `classrooms` VALUES(12,'N28A-BT2','N28A - Tutorial Block',1.5639,103.6389,2000);
INSERT INTO `classrooms` VALUES(13,'N28A-BT3','N28A - Tutorial Block',1.564,103.6389,2000);
INSERT INTO `classrooms` VALUES(14,'N28A-BT4','N28A - Tutorial Block',1.5641,103.6389,2000);
INSERT INTO `classrooms` VALUES(15,'N28A-BK1','N28A - Tutorial Block',1.5642,103.6389,2000);
INSERT INTO `classrooms` VALUES(16,'N28A-BK2','N28A - Tutorial Block',1.5643,103.6389,2000);
INSERT INTO `classrooms` VALUES(17,'N28A-BK5','N28A - Tutorial Block',1.5644,103.6389,2000);
INSERT INTO `classrooms` VALUES(18,'N28A-BK6','N28A - Tutorial Block',1.5645,103.6389,2000);
INSERT INTO `classrooms` VALUES(19,'N28-502-01','N28 - Level 5',1.5646,103.6389,2000);
INSERT INTO `classrooms` VALUES(20,'N28-505-01','N28 - Level 5',1.5647,103.6389,2000);
INSERT INTO `classrooms` VALUES(21,'N28-506-01','N28 - Level 5',1.5638,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(22,'N28-512-01','N28 - Level 5',1.5639,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(23,'N28-516-01','N28 - Level 5',1.564,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(24,'N28-517-02','N28 - Level 5',1.5641,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(25,'N28-518-02','N28 - Level 5',1.5642,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(26,'N28-524-02','N28 - Level 5',1.5643,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(27,'N28-525-01','N28 - Level 5',1.5644,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(28,'N28-527-02','N28 - Level 5',1.5645,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(29,'N28A-02-33','N28A - Level 2',1.5646,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(30,'N28A-02-34','N28A - Level 2',1.5647,1.036390000000000101e+02,2000);
INSERT INTO `classrooms` VALUES(31,'N28-223-02','N28 - Level 2',1.5638,103.6391,2000);
INSERT INTO `classrooms` VALUES(32,'N28-204-01','N28 - Level 2',1.5639,103.6391,2000);
INSERT INTO `classrooms` VALUES(33,'N28-203-01','N28 - Level 2',1.564,103.6391,2000);
INSERT INTO `classrooms` VALUES(34,'N28-202-01','N28 - Level 2',1.5641,103.6391,2000);
INSERT INTO `classrooms` VALUES(35,'N28-330-01','N28 - Level 3',1.5642,103.6391,2000);
INSERT INTO `classrooms` VALUES(36,'N28-352-01','N28 - Level 3',1.5643,103.6391,2000);
INSERT INTO `classrooms` VALUES(37,'N28-321-02','N28 - Level 3',1.5644,103.6391,2000);
INSERT INTO `classrooms` VALUES(38,'N28-329-01','N28 - Level 3',1.5645,103.6391,2000);
INSERT INTO `classrooms` VALUES(39,'N28-350-03','N28 - Level 3',1.5646,103.6391,2000);
INSERT INTO `classrooms` VALUES(40,'N28-422-01','N28 - Level 4',1.5647,103.6391,2000);
INSERT INTO `classrooms` VALUES(41,'N28-423-01','N28 - Level 4',1.5638,103.6392,2000);
INSERT INTO `classrooms` VALUES(42,'L50-DK3','Block L50 - Centre Point',1.5639,103.6392,2000);
INSERT INTO `classrooms` VALUES(43,'P19-DK4','Block P19 - FKE',1.564,103.6392,2000);
INSERT INTO `classrooms` VALUES(44,'P19-DK5','Block P19 - FKE',1.5641,103.6392,2000);
INSERT INTO `classrooms` VALUES(45,'P19-DK6','Block P19 - FKE',1.5642,103.6392,2000);
INSERT INTO `classrooms` VALUES(46,'PUTERI-COURT','Puteri Court Condominium (Home Demo)',3.128024,101.765479,1000);
INSERT INTO `classrooms` VALUES(47,'Pejabat Pos UTM (KDSE)','W1, Kolej Datin Seri Endon, 81110 Johor Bahru',1.560662,103.638421,2000);
CREATE TABLE students (
	id INTEGER NOT NULL, 
	user_id INTEGER, 
	student_id VARCHAR(50) NOT NULL, 
	name VARCHAR(100) NOT NULL, 
	email VARCHAR(100), 
	face_encoding TEXT, 
	registered_at DATETIME, 
	is_active BOOLEAN, 
	PRIMARY KEY (id), 
	UNIQUE (user_id), 
	FOREIGN KEY(user_id) REFERENCES users (id), 
	UNIQUE (student_id), 
	UNIQUE (email)
);
INSERT INTO `students` VALUES(1,1,'B24CS0011','CHUA LIN WEI','chuawei@graduate.utm.my','[15.0, 180.0, 15.0, 242.0, 74.0, 75.0, 180.0, 242.0, 127.0, 136.0, 138.0, 179.0, 93.0, 68.0, 122.0, 148.0, 34.0, 224.0, 223.0, 39.0, 58.0, 228.0, 180.0, 183.0, 91.0, 67.0, 219.0, 4.0, 131.0, 147.0, 136.0, 58.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]','2025-11-22 16:33:50.673821',1);
CREATE TABLE users (
	id INTEGER NOT NULL, 
	matric_no VARCHAR(50) NOT NULL, 
	name VARCHAR(100) NOT NULL, 
	email VARCHAR(100), 
	password_hash VARCHAR(255) NOT NULL, 
	role VARCHAR(20) NOT NULL, 
	is_active BOOLEAN, 
	created_at DATETIME, 
	PRIMARY KEY (id), 
	UNIQUE (matric_no), 
	UNIQUE (email)
);
INSERT INTO `users` VALUES(1,'B24CS0011','CHUA LIN WEI','chuawei@graduate.utm.my','scrypt:32768:8:1$hE1y6RkAGBX68GXd$0f613cc449e9b0f9bf2be44cc9914af63d03bc6af78c96baf25081d085e8e64fdd001d01b286afe3cde65fba0ce980d5666c4a233057c7937f6924302e38cd84','student',1,'2025-11-22 16:33:49.914535');
INSERT INTO `users` VALUES(2,'L001','ZHANG LING HE','miaofifi146@gmail.com','scrypt:32768:8:1$0Lde9MkTKZHggMvp$fd98cbd8955f87ffc01b1e383429b350d50ff4c3e5b05ac7f39bf25dac99aa1f0858f8c5c562decc81c297fd5b1f98bbce7ec69027875bd9882eb052e07d777a','lecturer',1,'2025-11-22 16:34:27.215798');
INSERT INTO `users` VALUES(3,'L9999','Test Lecturer',NULL,'scrypt:32768:8:1$mVC87j8AGr8uHDsu$4b3a45675a2731d350460403556460e2ebbdd85fcdb204cc9ad81695a0f573f20c36405be345ead28baedc30f21f4cf3fc8e32c14bfc3b83f89d2d4a0edf74bf','lecturer',1,'2025-11-22 17:20:16.122417');
COMMIT;