
CREATE TABLE `academic_periods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tahun_ajaran` varchar(20) NOT NULL,
  `semester` enum('1','2') NOT NULL DEFAULT '1',
  `deadline` date DEFAULT NULL,
  `is_active` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `activity_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `file_id` int(10) unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `activity_log_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `admin_doc_submissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `period_id` int(10) unsigned NOT NULL,
  `doc_type_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `drive_file_id` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `status` enum('submitted','revision','approved') NOT NULL DEFAULT 'submitted',
  `admin_note` text DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_doc_submissions_doc_type_id_foreign` (`doc_type_id`),
  KEY `period_id` (`period_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `admin_doc_submissions_doc_type_id_foreign` FOREIGN KEY (`doc_type_id`) REFERENCES `admin_doc_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `admin_doc_submissions_period_id_foreign` FOREIGN KEY (`period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `admin_doc_submissions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `admin_doc_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `is_required` tinyint(3) unsigned NOT NULL DEFAULT 1,
  `sort_order` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `calendar_events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `event_type` enum('libur','akademik','sekolah','pimpinan','deadline') NOT NULL DEFAULT 'sekolah',
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `all_day` tinyint(1) NOT NULL DEFAULT 1,
  `location` varchar(200) DEFAULT NULL,
  `target_role` varchar(50) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `calendar_events_created_by_foreign` (`created_by`),
  KEY `start_date` (`start_date`),
  KEY `event_type` (`event_type`),
  CONSTRAINT `calendar_events_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `conversation_participants` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `conversation_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `last_read_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `conversation_id_user_id` (`conversation_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `conversation_participants_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conversation_participants_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `conversations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` enum('direct','group','broadcast') NOT NULL DEFAULT 'direct',
  `title` varchar(200) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `last_message_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `conversations_created_by_foreign` (`created_by`),
  KEY `last_message_at` (`last_message_at`),
  CONSTRAINT `conversations_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `diploma_files` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int(10) unsigned NOT NULL,
  `drive_file_id` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `uploaded_by` int(10) unsigned DEFAULT NULL,
  `uploaded_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `diploma_files_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `files` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `drive_file_id` varchar(150) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `size` bigint(20) NOT NULL DEFAULT 0,
  `drive_folder_id` varchar(100) DEFAULT NULL,
  `is_shared` tinyint(1) NOT NULL DEFAULT 0,
  `uploaded_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `files_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `messages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `conversation_id` int(10) unsigned NOT NULL,
  `sender_id` int(10) unsigned DEFAULT NULL,
  `body` text NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `messages_sender_id_foreign` (`sender_id`),
  KEY `conversation_id_id` (`conversation_id`,`id`),
  CONSTRAINT `messages_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `messages_sender_id_foreign` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `migrations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `version` varchar(255) NOT NULL,
  `class` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `namespace` varchar(255) NOT NULL,
  `time` int(11) NOT NULL,
  `batch` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `notifications` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `type` varchar(50) NOT NULL DEFAULT 'info',
  `title` varchar(255) NOT NULL,
  `message` text DEFAULT NULL,
  `link` varchar(255) DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `user_id_is_read` (`user_id`,`is_read`),
  CONSTRAINT `notifications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `period_doc_deadlines` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `period_id` int(10) unsigned NOT NULL,
  `doc_type_id` int(10) unsigned NOT NULL,
  `deadline` date NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `period_id_doc_type_id` (`period_id`,`doc_type_id`),
  KEY `period_doc_deadlines_doc_type_id_foreign` (`doc_type_id`),
  CONSTRAINT `period_doc_deadlines_doc_type_id_foreign` FOREIGN KEY (`doc_type_id`) REFERENCES `admin_doc_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `period_doc_deadlines_period_id_foreign` FOREIGN KEY (`period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `shared_document_recipients` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `document_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `document_id_user_id` (`document_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `shared_document_recipients_document_id_foreign` FOREIGN KEY (`document_id`) REFERENCES `shared_documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `shared_document_recipients_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `shared_documents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `doc_number` varchar(120) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `category` enum('sk','surat_tugas','edaran','lainnya') NOT NULL DEFAULT 'sk',
  `audience` enum('all','selected') NOT NULL DEFAULT 'all',
  `target_role` varchar(50) DEFAULT NULL,
  `issued_date` date DEFAULT NULL,
  `drive_file_id` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `uploaded_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `shared_documents_uploaded_by_foreign` (`uploaded_by`),
  KEY `audience` (`audience`),
  KEY `issued_date` (`issued_date`),
  CONSTRAINT `shared_documents_uploaded_by_foreign` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `students` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nisn` varchar(20) DEFAULT NULL,
  `nis` varchar(20) DEFAULT NULL,
  `nama` varchar(150) NOT NULL,
  `tempat_lahir` varchar(100) DEFAULT NULL,
  `tanggal_lahir` date DEFAULT NULL,
  `jenis_kelamin` enum('L','P') NOT NULL DEFAULT 'L',
  `nama_orangtua` varchar(150) DEFAULT NULL,
  `tahun_lulus` year(4) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nisn` (`nisn`),
  KEY `nis` (`nis`),
  KEY `tahun_lulus` (`tahun_lulus`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;




INSERT INTO `migrations` (`id`, `version`, `class`, `group`, `namespace`, `time`, `batch`) VALUES (1,'2026-04-16-000001','App\\Database\\Migrations\\CreateUsersTable','default','App',1776328966,1),(2,'2026-04-16-000002','App\\Database\\Migrations\\CreateCategoriesTable','default','App',1776328966,1),(3,'2026-04-16-000003','App\\Database\\Migrations\\CreateFilesTable','default','App',1776328966,1),(4,'2026-04-16-000004','App\\Database\\Migrations\\CreateActivityLogTable','default','App',1776328966,1),(5,'2026-04-20-000005','App\\Database\\Migrations\\CreateStudentsTable','default','App',1776657185,2),(6,'2026-04-20-000006','App\\Database\\Migrations\\CreateDiplomaFilesTable','default','App',1776657185,2),(7,'2026-05-18-000007','App\\Database\\Migrations\\CreateAcademicPeriodsTable','default','App',1779083817,3),(8,'2026-05-18-000008','App\\Database\\Migrations\\CreateAdminDocTypesTable','default','App',1779083817,3),(9,'2026-05-18-000009','App\\Database\\Migrations\\CreateAdminDocSubmissionsTable','default','App',1779083817,3),(11,'2026-05-19-000010','App\\Database\\Migrations\\ExpandUserRoles','default','App',1779166193,4),(12,'2026-05-19-000011','App\\Database\\Migrations\\CreateNotificationsTable','default','App',1779166193,4),(13,'2026-08-12-000012','App\\Database\\Migrations\\AddGoogleRefreshTokenToUsers','default','App',1786483007,5),(14,'2026-08-12-000013','App\\Database\\Migrations\\CreateCalendarEventsTable','default','App',1786483921,6),(15,'2026-08-12-000014','App\\Database\\Migrations\\CreateMessagingTables','default','App',1786483921,6),(16,'2026-08-12-000015','App\\Database\\Migrations\\AllowNullGoogleId','default','App',1786485803,7),(17,'2026-08-12-000016','App\\Database\\Migrations\\CreatePeriodDocDeadlinesTable','default','App',1786488298,8),(18,'2026-08-12-000017','App\\Database\\Migrations\\CreateSharedDocumentsTable','default','App',1786510101,9);

INSERT INTO `categories` (`id`, `name`, `slug`, `icon`, `sort_order`) VALUES (1,'SK (Surat Keputusan)','sk','file-text',1),(2,'Surat Tugas','surat-tugas','send',2),(3,'RPP / Modul Ajar','rpp','book-open',3),(4,'Sertifikat & Piagam','sertifikat','award',4),(5,'Dokumen Kepegawaian','kepegawaian','users',5),(6,'Dokumen Keuangan','keuangan','dollar-sign',6),(7,'Lain-lain','lainnya','folder',7),(8,'Dokumen Kurikulum','dokumen-kurikulum','book-open',11);

INSERT INTO `admin_doc_types` (`id`, `name`, `description`, `is_required`, `sort_order`) VALUES (9,'Perangkat Pembelajaran','Lengkap dari Cover, Lembar Pengesahan, CP, ATP, Prota, Prosem, Modul Ajar',1,0),(10,'Soal ASTS Ganjil','Format Dokumen Word',1,1),(11,'Kisi-Kisi ASTS Ganjil','',1,2),(13,'Soal ASAS Ganjil','',1,3),(14,'Kisi-Kisi ASAS Ganjil','',1,4);


