-- ============================================================
-- CLASSROOM MANAGEMENT SYSTEM - skema database (MySQL 8 / MariaDB 10.4+)
-- Dibuat dari model SQLAlchemy (backend/app/models/models.py).
-- Impor lewat phpMyAdmin (tab Import) atau: mysql -u root -p < classroom_management_db.sql
-- Catatan: server juga membuat tabel otomatis saat pertama dijalankan; file ini untuk
-- instalasi manual / peninjauan. Pastikan CMS_DATABASE_URL mengarah ke database ini.
-- ============================================================

CREATE DATABASE IF NOT EXISTS classroom_management_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE classroom_management_db;

SET FOREIGN_KEY_CHECKS=0;


CREATE TABLE activity_logs (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	actor_type VARCHAR(10) NOT NULL, 
	actor_id INTEGER, 
	actor_name VARCHAR(120) NOT NULL, 
	action VARCHAR(60) NOT NULL, 
	computer_id INTEGER, 
	computer_name VARCHAR(120), 
	status VARCHAR(10) NOT NULL, 
	detail JSON, 
	reason VARCHAR(255), 
	ip_address VARCHAR(45), 
	created_at DATETIME NOT NULL, 
	prev_hash VARCHAR(64), 
	row_hash VARCHAR(64), 
	PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_activity_logs_action ON activity_logs (action);
CREATE INDEX ix_activity_logs_computer_id ON activity_logs (computer_id);
CREATE INDEX ix_activity_logs_created_at ON activity_logs (created_at);


CREATE TABLE administrators (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	username VARCHAR(64) NOT NULL, 
	full_name VARCHAR(120) NOT NULL, 
	email VARCHAR(160), 
	password_hash VARCHAR(255) NOT NULL, 
	is_active BOOL NOT NULL, 
	last_login_at DATETIME, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE application_policies (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(100) NOT NULL, 
	mode VARCHAR(10) NOT NULL, 
	blocked_apps JSON NOT NULL, 
	allowed_apps JSON NOT NULL, 
	block_store BOOL NOT NULL, 
	block_installers BOOL NOT NULL, 
	is_active BOOL NOT NULL, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE browser_policies (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(100) NOT NULL, 
	max_tabs INTEGER NOT NULL, 
	allowed_browsers JSON NOT NULL, 
	homepage VARCHAR(255), 
	block_incognito BOOL NOT NULL, 
	downloads_require_approval BOOL NOT NULL, 
	exam_mode BOOL NOT NULL, 
	is_active BOOL NOT NULL, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE internet_policies (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(100) NOT NULL, 
	mode VARCHAR(12) NOT NULL, 
	block_social BOOL NOT NULL, 
	block_games BOOL NOT NULL, 
	block_entertainment BOOL NOT NULL, 
	is_active BOOL NOT NULL, 
	created_by VARCHAR(120), 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE policy_assignments (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	policy_type VARCHAR(12) NOT NULL, 
	policy_id INTEGER NOT NULL, 
	target_type VARCHAR(12) NOT NULL, 
	target_id INTEGER NOT NULL, 
	created_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT uq_assign UNIQUE (policy_type, target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_assign_target ON policy_assignments (target_type, target_id);


CREATE TABLE refresh_tokens (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	token_hash VARCHAR(64) NOT NULL, 
	`role` VARCHAR(10) NOT NULL, 
	user_id INTEGER NOT NULL, 
	expires_at DATETIME NOT NULL, 
	revoked BOOL NOT NULL, 
	created_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE system_settings (
	`key` VARCHAR(80) NOT NULL, 
	value TEXT, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE teachers (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	username VARCHAR(64) NOT NULL, 
	full_name VARCHAR(120) NOT NULL, 
	nip VARCHAR(40), 
	email VARCHAR(160), 
	password_hash VARCHAR(255) NOT NULL, 
	permissions JSON NOT NULL, 
	is_active BOOL NOT NULL, 
	last_login_at DATETIME, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE allowed_websites (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	policy_id INTEGER NOT NULL, 
	domain VARCHAR(190) NOT NULL, 
	category VARCHAR(30), 
	created_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT uq_allowed_policy_domain UNIQUE (policy_id, domain), 
	FOREIGN KEY(policy_id) REFERENCES internet_policies (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_allowed_websites_policy_id ON allowed_websites (policy_id);


CREATE TABLE blocked_websites (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	policy_id INTEGER NOT NULL, 
	domain VARCHAR(190) NOT NULL, 
	category VARCHAR(30), 
	created_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT uq_blocked_policy_domain UNIQUE (policy_id, domain), 
	FOREIGN KEY(policy_id) REFERENCES internet_policies (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_blocked_websites_policy_id ON blocked_websites (policy_id);


CREATE TABLE classrooms (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(80) NOT NULL, 
	description VARCHAR(255), 
	teacher_id INTEGER, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (name), 
	FOREIGN KEY(teacher_id) REFERENCES teachers (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE computers (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	device_uid VARCHAR(64) NOT NULL, 
	hostname VARCHAR(120) NOT NULL, 
	label VARCHAR(120), 
	ip_address VARCHAR(45), 
	mac_address VARCHAR(17), 
	windows_user VARCHAR(120), 
	os_name VARCHAR(160), 
	agent_version VARCHAR(20), 
	classroom_id INTEGER, 
	is_online BOOL NOT NULL, 
	usage_state VARCHAR(20) NOT NULL, 
	is_locked BOOL NOT NULL, 
	internet_override VARCHAR(20), 
	exam_lock BOOL NOT NULL, 
	metrics JSON, 
	last_seen_at DATETIME, 
	policy_hash VARCHAR(64), 
	policy_applied_at DATETIME, 
	released BOOL NOT NULL, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (device_uid), 
	FOREIGN KEY(classroom_id) REFERENCES classrooms (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_computers_classroom_id ON computers (classroom_id);
CREATE INDEX ix_computers_is_online ON computers (is_online);


CREATE TABLE application_requests (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	computer_id INTEGER NOT NULL, 
	student_name VARCHAR(120), 
	app_name VARCHAR(255) NOT NULL, 
	request_type VARCHAR(12) NOT NULL, 
	detail VARCHAR(500), 
	status ENUM('menunggu','disetujui','ditolak','kedaluwarsa') NOT NULL, 
	permit_minutes INTEGER, 
	decision_note VARCHAR(255), 
	decided_by VARCHAR(120), 
	decided_at DATETIME, 
	requested_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(computer_id) REFERENCES computers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_application_requests_computer_id ON application_requests (computer_id);
CREATE INDEX ix_application_requests_requested_at ON application_requests (requested_at);
CREATE INDEX ix_application_requests_status ON application_requests (status);


CREATE TABLE computer_commands (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	computer_id INTEGER NOT NULL, 
	action VARCHAR(40) NOT NULL, 
	params JSON NOT NULL, 
	status VARCHAR(12) NOT NULL, 
	result TEXT, 
	reason VARCHAR(255), 
	issued_by_role VARCHAR(10) NOT NULL, 
	issued_by_id INTEGER, 
	issued_by_name VARCHAR(120) NOT NULL, 
	created_at DATETIME NOT NULL, 
	sent_at DATETIME, 
	completed_at DATETIME, 
	expires_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(computer_id) REFERENCES computers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_computer_commands_computer_id ON computer_commands (computer_id);
CREATE INDEX ix_computer_commands_created_at ON computer_commands (created_at);
CREATE INDEX ix_computer_commands_status ON computer_commands (status);


CREATE TABLE computer_sessions (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	computer_id INTEGER NOT NULL, 
	kind VARCHAR(20) NOT NULL, 
	windows_user VARCHAR(120), 
	started_by VARCHAR(120), 
	started_at DATETIME NOT NULL, 
	ended_at DATETIME, 
	PRIMARY KEY (id), 
	FOREIGN KEY(computer_id) REFERENCES computers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_computer_sessions_computer_id ON computer_sessions (computer_id);


CREATE TABLE device_tokens (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	computer_id INTEGER NOT NULL, 
	token_hash VARCHAR(64) NOT NULL, 
	expires_at DATETIME NOT NULL, 
	revoked BOOL NOT NULL, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(computer_id) REFERENCES computers (id) ON DELETE CASCADE, 
	UNIQUE (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_device_tokens_computer_id ON device_tokens (computer_id);


CREATE TABLE installed_applications (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	computer_id INTEGER NOT NULL, 
	name VARCHAR(255) NOT NULL, 
	version VARCHAR(80), 
	publisher VARCHAR(160), 
	install_date VARCHAR(20), 
	first_seen_at DATETIME NOT NULL, 
	reported_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(computer_id) REFERENCES computers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_installed_applications_computer_id ON installed_applications (computer_id);


CREATE TABLE notifications (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	type VARCHAR(30) NOT NULL, 
	title VARCHAR(160) NOT NULL, 
	message VARCHAR(500), 
	severity VARCHAR(10) NOT NULL, 
	computer_id INTEGER, 
	classroom_id INTEGER, 
	is_read BOOL NOT NULL, 
	created_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(computer_id) REFERENCES computers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_notifications_classroom_id ON notifications (classroom_id);
CREATE INDEX ix_notifications_computer_id ON notifications (computer_id);
CREATE INDEX ix_notifications_created_at ON notifications (created_at);
CREATE INDEX ix_notifications_type ON notifications (type);


CREATE TABLE schedules (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(100) NOT NULL, 
	kind VARCHAR(14) NOT NULL, 
	classroom_id INTEGER, 
	computer_id INTEGER, 
	days JSON NOT NULL, 
	start_time VARCHAR(5) NOT NULL, 
	end_time VARCHAR(5) NOT NULL, 
	policy_id INTEGER, 
	is_active BOOL NOT NULL, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(classroom_id) REFERENCES classrooms (id) ON DELETE CASCADE, 
	FOREIGN KEY(computer_id) REFERENCES computers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_schedules_classroom_id ON schedules (classroom_id);
CREATE INDEX ix_schedules_computer_id ON schedules (computer_id);


CREATE TABLE students (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	nis VARCHAR(40), 
	full_name VARCHAR(120) NOT NULL, 
	classroom_id INTEGER, 
	computer_id INTEGER, 
	is_active BOOL NOT NULL, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (nis), 
	FOREIGN KEY(classroom_id) REFERENCES classrooms (id) ON DELETE SET NULL, 
	FOREIGN KEY(computer_id) REFERENCES computers (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_students_classroom_id ON students (classroom_id);
CREATE INDEX ix_students_computer_id ON students (computer_id);


CREATE TABLE download_requests (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	computer_id INTEGER NOT NULL, 
	student_id INTEGER, 
	student_name VARCHAR(120), 
	file_name VARCHAR(255) NOT NULL, 
	file_size BIGINT, 
	file_type VARCHAR(120), 
	source_url TEXT NOT NULL, 
	browser VARCHAR(20), 
	status ENUM('menunggu','disetujui','ditolak','kedaluwarsa') NOT NULL, 
	auto_decision BOOL NOT NULL, 
	decision_note VARCHAR(255), 
	decided_by VARCHAR(120), 
	decided_at DATETIME, 
	requested_at DATETIME NOT NULL, 
	expires_at DATETIME NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(computer_id) REFERENCES computers (id) ON DELETE CASCADE, 
	FOREIGN KEY(student_id) REFERENCES students (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_download_requests_computer_id ON download_requests (computer_id);
CREATE INDEX ix_download_requests_requested_at ON download_requests (requested_at);
CREATE INDEX ix_download_requests_status ON download_requests (status);


CREATE TABLE download_approvals (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	request_id INTEGER NOT NULL, 
	approved_by VARCHAR(120) NOT NULL, 
	approved_at DATETIME NOT NULL, 
	valid_until DATETIME NOT NULL, 
	single_use BOOL NOT NULL, 
	used_at DATETIME, 
	PRIMARY KEY (id), 
	UNIQUE (request_id), 
	FOREIGN KEY(request_id) REFERENCES download_requests (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;