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

-- 1. LIETOTĀJU TABULA
-- Glabā gan studentus, gan pasniedzējus. Loma tiek noteikta ar 'role' lauku.
CREATE TABLE `users` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Lietotāja ID',
	`username` VARCHAR(50) NOT NULL COMMENT 'Lietotājvārds' COLLATE 'utf8mb4_unicode_ci',   -- Validācija atbilstoši RFC 5322 tiks veikta aplikācijā
	`email` VARCHAR(100) NOT NULL COMMENT 'Lietotāja e-pasts' COLLATE 'utf8mb4_unicode_ci', -- Glabāsies kā jaucējkods (password_hash)
	`password` VARCHAR(255) NOT NULL COMMENT 'Šifrēta parole' COLLATE 'utf8mb4_unicode_ci',
	`role` ENUM('student','teacher') NOT NULL COMMENT 'Lietotāja loma' COLLATE 'utf8mb4_unicode_ci',
	`created_at` TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP' COMMENT 'Izveides datums',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `username` (`username`) USING BTREE,
	UNIQUE INDEX `email` (`email`) USING BTREE
)
COMMENT='LIETOTĀJU TABULA'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

-- 2. UZDEVUMU TABULA
-- Pasniedzēja izveidotie mājasdarbi/projekti
CREATE TABLE `assignments` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Uzdevuma ID',
	`teacher_id` INT(10) NOT NULL COMMENT 'Pasniedzēja ID',                                         -- Kurš pasniedzējs izveidoja
	`title` VARCHAR(150) NOT NULL COMMENT 'Uzdevuma nosaukums' COLLATE 'utf8mb4_unicode_ci',        -- Validācija: garuma ierobežojumi aplikācijā
	`description` TEXT NOT NULL COMMENT 'Uzdevuma apraksts' COLLATE 'utf8mb4_unicode_ci',
	`due_date` DATETIME NOT NULL COMMENT 'Izpildes datums',                                         -- Validācija: nevar būt pagātnē (pārbauda PHP)
	`max_points` INT(10) NOT NULL DEFAULT '10' COMMENT 'Iegūstamie punkti (piem., 0-10 vai 0-100)', -- Validācija: diapazons (piem., 0-10 vai 0-100)
	`created_at` TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP' COMMENT 'Izveidošanas datums',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `teacher_id` (`teacher_id`) USING BTREE,
	CONSTRAINT `assignments_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)
COMMENT='UZDEVUMU TABULA'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

-- 3. IESNIEGUMU TABULA
-- Studentu augšupielādētie darbi
CREATE TABLE `submissions` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Iesnieguma ID',
	`assignment_id` INT(10) NOT NULL COMMENT 'Uzdevuma ID',                                                                         -- Saite uz uzdevumu
	`student_id` INT(10) NOT NULL COMMENT 'Studentu ID',                                                                            -- Saite uz studentu
	`file_path` VARCHAR(255) NOT NULL COMMENT 'Datņu atrašanās ceļš' COLLATE 'utf8mb4_unicode_ci',                                  -- Ceļš līdz failam servera mapē (uploads/)     
	`file_type` VARCHAR(50) NOT NULL COMMENT 'Datņu tips' COLLATE 'utf8mb4_unicode_ci',                                             -- Faila MIME tips (drošības validācijai)
	`submitted_at` TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP' COMMENT 'Iesniegšanas laiks',
	`status` ENUM('submitted','graded','returned') NULL DEFAULT 'submitted' COMMENT 'Darba statuss' COLLATE 'utf8mb4_unicode_ci',   -- Darba statuss
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `assignment_id` (`assignment_id`) USING BTREE,
	INDEX `student_id` (`student_id`) USING BTREE,
	CONSTRAINT `submissions_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `assignments` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE,
	CONSTRAINT `submissions_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)
COMMENT='IESNIEGUMU TABULA'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

-- 4. VĒRTĒJUMU UN REZULTĀTU TABULA
-- Pasniedzēja ieliktās atzīmes un komentāri
CREATE TABLE `grades` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Vērtējuma ID',                                    -- Viena atzīme uz vienu iesniegumu
	`submission_id` INT(10) NOT NULL COMMENT 'Uzdevuma ID',
	`teacher_id` INT(10) NOT NULL COMMENT 'Pasniedzēja ID',                                         -- Vērtējums (atbalsta, piemēram, 8.5 vai 95)
	`grade` DECIMAL(5,2) NOT NULL COMMENT 'Atzīme/ procenti',
	`comment` TEXT NULL DEFAULT NULL COMMENT 'Pasniedzēja komentāri' COLLATE 'utf8mb4_unicode_ci',  -- Pasniedzēja teksts atgriezeniskai saitei
	`graded_at` TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Izlikšanas laiks',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `submission_id` (`submission_id`) USING BTREE,
	INDEX `teacher_id` (`teacher_id`) USING BTREE,
	CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`submission_id`) REFERENCES `submissions` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE,
	CONSTRAINT `grades_ibfk_2` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)
COMMENT='VĒRTĒJUMU UN REZULTĀTU TABULA'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

-- 5. REZULTĀTU ATSAUKSOMJU FAILU TABULA (Papildfunkcija)
-- Ja pasniedzējs vēlas pievienot laboto failu atpakaļ kā atgriezenisko saiti
CREATE TABLE `feedback_files` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Atsaukšmju ID',
	`grade_id` INT(10) NOT NULL COMMENT 'Vērtējuma ID',
	`file_path` VARCHAR(255) NOT NULL COMMENT 'Datņu atrašanās ceļš' COLLATE 'utf8mb4_unicode_ci',  -- Ceļš līdz failam servera mapē (uploads/)     
	`uploaded_at` TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP' COMMENT 'Uzlikšanas laiks',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `grade_id` (`grade_id`) USING BTREE,
	CONSTRAINT `feedback_files_ibfk_1` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)
COMMENT='REZULTĀTU ATSAUKSOMJU FAILU TABULA'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
