-- Školní kalendář: tabulky + ukázková data
-- Pozn.: Pokud už tabulky existují, smaž je ručně nebo použij DROP TABLE IF EXISTS.


CREATE TABLE subjects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  color VARCHAR(20) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE teacher_subjects (
  teacher_id INT NOT NULL,
  subject_id INT NOT NULL,
  PRIMARY KEY (teacher_id, subject_id),
  FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE timetable_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  day_of_week TINYINT NOT NULL, -- 1=Po ... 7=Ne
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  room VARCHAR(30) NULL,
  subject_id INT NOT NULL,
  teacher_id INT NOT NULL,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE RESTRICT,
  FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_day_time (day_of_week, start_time)
) ENGINE=InnoDB;

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('test','homework','lesson','other') NOT NULL DEFAULT 'other',
  title VARCHAR(150) NOT NULL,
  description TEXT NULL,
  start_at DATETIME NOT NULL,
  end_at DATETIME NULL,
  subject_id INT NULL,
  teacher_id INT NULL,
  created_by INT NULL,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE SET NULL,
  FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_start_at (start_at),
  INDEX idx_subject_teacher (subject_id, teacher_id)
) ENGINE=InnoDB;

CREATE TABLE reminders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  event_id INT NULL,
  text VARCHAR(255) NOT NULL,
  remind_at DATETIME NOT NULL,
  is_done TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE SET NULL,
  INDEX idx_user_remind (user_id, remind_at)
) ENGINE=InnoDB;

INSERT INTO subjects (id, name, color) VALUES
(1, 'Matematika',  '#4F46E5'),
(2, 'Český jazyk', '#EF4444'),
(3, 'Angličtina',  '#10B981'),
(4, 'ICT',         '#F59E0B');

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

INSERT INTO timetable_entries (day_of_week, start_time, end_time, room, subject_id, teacher_id) VALUES
(1, '08:00:00', '08:45:00', 'A101', 1, 2),
(1, '08:55:00', '09:40:00', 'A101', 1, 2),
(1, '09:55:00', '10:40:00', 'B203', 2, 1),
(1, '10:50:00', '11:35:00', 'C301', 4, 2),

(2, '08:00:00', '08:45:00', 'B203', 3, 1),
(2, '08:55:00', '09:40:00', 'B203', 3, 1),
(2, '09:55:00', '10:40:00', 'A101', 1, 2),

(3, '08:00:00', '08:45:00', 'C301', 4, 2),
(3, '08:55:00', '09:40:00', 'C301', 4, 2),
(3, '09:55:00', '10:40:00', 'B203', 2, 1),

(4, '08:00:00', '08:45:00', 'B203', 2, 1),
(4, '08:55:00', '09:40:00', 'B203', 2, 1),
(4, '09:55:00', '10:40:00', 'A101', 1, 2),

(5, '08:00:00', '08:45:00', 'B203', 3, 1),
(5, '08:55:00', '09:40:00', 'C301', 4, 2);

-- pár událostí okolo dneška (uprav si podle potřeby)
INSERT INTO events (type, title, description, start_at, end_at, subject_id, teacher_id, created_by) VALUES
('test',     'Písemka – rovnice',        'Krátký test (20 min).',          DATE_ADD(NOW(), INTERVAL 2 DAY), NULL, 1, 2, 2),
('homework', 'Úkol – sloh (1 strana)',   'Odevzdat do 23:59.',             DATE_ADD(NOW(), INTERVAL 3 DAY), NULL, 2, 1, 1),
('lesson',   'Opakování – minulý čas',   'Příprava na příští týden.',      DATE_ADD(NOW(), INTERVAL 1 DAY), NULL, 3, 1, 1),
('other',    'Konzultace (dobrovolná)',  'Po vyučování.',                  DATE_ADD(NOW(), INTERVAL 4 DAY), NULL, 1, 2, 2);

INSERT INTO reminders (user_id, event_id, text, remind_at, is_done) VALUES
(3, 1, 'Nezapomeň kalkulačku.', DATE_ADD(NOW(), INTERVAL 2 DAY), 0),
(3, 2, 'Dopsat sloh a zkontrolovat pravopis.', DATE_ADD(NOW(), INTERVAL 3 DAY), 0),
(4, NULL, 'Připravit sešit na zítřejší školu.', DATE_ADD(NOW(), INTERVAL 1 DAY), 1);
