-- Adminer 4.8.1 MySQL 5.7.27-0ubuntu0.16.04.1 dump

SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('test','homework','lesson','other') COLLATE utf8_czech_ci NOT NULL DEFAULT 'other',
  `title` varchar(150) COLLATE utf8_czech_ci NOT NULL,
  `description` text COLLATE utf8_czech_ci,
  `start_at` datetime NOT NULL,
  `end_at` datetime DEFAULT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `teacher_id` (`teacher_id`),
  KEY `created_by` (`created_by`),
  KEY `idx_start_at` (`start_at`),
  KEY `idx_subject_teacher` (`subject_id`,`teacher_id`),
  CONSTRAINT `events_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE SET NULL,
  CONSTRAINT `events_ibfk_2` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `events_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `events` (`id`, `type`, `title`, `description`, `start_at`, `end_at`, `subject_id`, `teacher_id`, `created_by`) VALUES
(1,	'test',	'Písemka – rovnice',	'Krátký test (20 min).',	'2026-03-05 08:17:31',	NULL,	1,	2,	2),
(2,	'homework',	'Úkol – sloh (1 strana)',	'Odevzdat do 23:59.',	'2026-03-06 08:17:31',	NULL,	2,	1,	1),
(3,	'lesson',	'Opakování – minulý čas',	'Příprava na příští týden.',	'2026-03-04 08:17:31',	NULL,	3,	1,	1),
(4,	'other',	'Konzultace (dobrovolná)',	'Po vyučování.',	'2026-03-07 08:17:31',	NULL,	1,	2,	2),
(5,	'test',	'Maturitní zkoušení',	'Knihy z maturitního seznamu',	'2026-03-12 08:00:00',	'2026-03-12 08:45:00',	2,	1,	2),
(6,	'test',	'Písemka - intergrály',	NULL,	'2026-03-27 08:00:00',	'2026-03-27 09:00:00',	1,	2,	2),
(7,	'test',	'Písemka',	'aoskdpokp',	'2026-04-01 09:15:00',	'2026-04-01 10:10:00',	1,	2,	2);

CREATE TABLE `subjects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(80) COLLATE utf8_czech_ci NOT NULL,
  `color` varchar(20) COLLATE utf8_czech_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `subjects` (`id`, `name`, `color`, `created_at`) VALUES
(1,	'Matematika',	'#d52d2b',	'2026-03-03 07:17:31'),
(2,	'Český jazyk',	'#147b2b',	'2026-03-03 07:17:31'),
(3,	'Angličtina',	'#196DC2',	'2026-03-03 07:17:31'),
(4,	'ICT',	'#D6B711',	'2026-03-03 07:17:31');

CREATE TABLE `teacher_subjects` (
  `teacher_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  PRIMARY KEY (`teacher_id`,`subject_id`),
  KEY `subject_id` (`subject_id`),
  CONSTRAINT `teacher_subjects_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `teacher_subjects_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `teacher_subjects` (`teacher_id`, `subject_id`) VALUES
(2,	1),
(1,	2),
(1,	3),
(2,	4);

CREATE TABLE `timetable_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day_of_week` tinyint(4) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `room` varchar(30) COLLATE utf8_czech_ci DEFAULT NULL,
  `subject_id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `subject_id` (`subject_id`),
  KEY `teacher_id` (`teacher_id`),
  KEY `idx_day_time` (`day_of_week`,`start_time`),
  CONSTRAINT `timetable_entries_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`),
  CONSTRAINT `timetable_entries_ibfk_2` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `timetable_entries` (`id`, `day_of_week`, `start_time`, `end_time`, `room`, `subject_id`, `teacher_id`) VALUES
(1,	1,	'08:00:00',	'08:45:00',	'A101',	1,	2),
(2,	1,	'08:55:00',	'09:40:00',	'A101',	1,	2),
(3,	1,	'09:55:00',	'10:40:00',	'B203',	2,	1),
(4,	1,	'10:50:00',	'11:35:00',	'C301',	4,	2),
(5,	2,	'08:00:00',	'08:45:00',	'B203',	3,	1),
(6,	2,	'08:55:00',	'09:40:00',	'B203',	3,	1),
(7,	2,	'09:55:00',	'10:40:00',	'A101',	1,	2),
(8,	3,	'08:00:00',	'08:45:00',	'C301',	4,	2),
(9,	3,	'08:55:00',	'09:40:00',	'C301',	4,	2),
(10,	3,	'09:55:00',	'10:40:00',	'B203',	2,	1),
(11,	4,	'08:00:00',	'08:45:00',	'B203',	2,	1),
(12,	4,	'08:55:00',	'09:40:00',	'B203',	2,	1),
(13,	4,	'09:55:00',	'10:40:00',	'A101',	1,	2),
(14,	5,	'08:00:00',	'08:45:00',	'B203',	3,	1),
(15,	5,	'08:55:00',	'09:40:00',	'C301',	4,	2);

-- 2026-03-31 06:22:27
