-- ============================================================
-- The New Sepals School System Titara
-- Class Diary / Homework Module Migration
-- Safe additive migration. Existing data is not removed.
-- Target: MariaDB / MySQL
-- ============================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `diary_teacher_assignments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `session_id` int(10) UNSIGNED NOT NULL,
  `class_id` int(10) UNSIGNED NOT NULL,
  `section_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 means all sections',
  `subject_id` int(10) UNSIGNED NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_diary_teacher_assignment` (`user_id`,`session_id`,`class_id`,`section_id`,`subject_id`),
  KEY `idx_diary_assignment_user` (`user_id`,`status`),
  KEY `idx_diary_assignment_target` (`session_id`,`class_id`,`section_id`,`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `class_diary` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_key` varchar(64) DEFAULT NULL COMMENT 'Groups copies published to multiple sections',
  `session_id` int(10) UNSIGNED NOT NULL,
  `class_id` int(10) UNSIGNED NOT NULL,
  `section_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 means all sections',
  `subject_id` int(10) UNSIGNED NOT NULL,
  `diary_date` date NOT NULL,
  `classwork` text DEFAULT NULL,
  `homework` text DEFAULT NULL,
  `test_quiz` text DEFAULT NULL,
  `important_note` text DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('draft','published') NOT NULL DEFAULT 'draft',
  `published_at` datetime DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_diary_target_date` (`session_id`,`class_id`,`section_id`,`diary_date`,`status`),
  KEY `idx_diary_subject` (`subject_id`,`diary_date`),
  KEY `idx_diary_creator` (`created_by`,`diary_date`),
  KEY `idx_diary_group` (`group_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `diary_attachments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `diary_id` bigint(20) UNSIGNED NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `stored_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `file_size` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `uploaded_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_diary_attachment_diary` (`diary_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `diary_reads` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) UNSIGNED NOT NULL,
  `student_id` int(10) UNSIGNED NOT NULL,
  `diary_id` bigint(20) UNSIGNED NOT NULL,
  `read_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_diary_read` (`parent_id`,`student_id`,`diary_id`),
  KEY `idx_diary_reads_parent` (`parent_id`,`student_id`,`read_at`),
  KEY `idx_diary_reads_diary` (`diary_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permissions. INSERT IGNORE keeps this migration safe to run again.
INSERT IGNORE INTO `permissions` (`permission_key`,`permission_name`,`module`)
VALUES
('diary.view','View Class Diary','Class Diary'),
('diary.manage','Create and Manage Class Diary','Class Diary'),
('diary.assign','Assign Teachers to Diary Classes','Class Diary');

-- Teacher: only diary view/manage by default.
INSERT IGNORE INTO `role_permissions` (`role`,`permission_id`)
SELECT 'teacher', `id` FROM `permissions` WHERE `permission_key`='diary.view';
INSERT IGNORE INTO `role_permissions` (`role`,`permission_id`)
SELECT 'teacher', `id` FROM `permissions` WHERE `permission_key`='diary.manage';

-- Admin and Principal: full diary module access.
INSERT IGNORE INTO `role_permissions` (`role`,`permission_id`)
SELECT 'admin', `id` FROM `permissions` WHERE `permission_key` IN ('diary.view','diary.manage','diary.assign');
INSERT IGNORE INTO `role_permissions` (`role`,`permission_id`)
SELECT 'principal', `id` FROM `permissions` WHERE `permission_key` IN ('diary.view','diary.manage','diary.assign');

-- Super Admin bypasses permission checks in the application and needs no rows here.