-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.11.0.7065
-- --------------------------------------------------------



-- Dumping database structure for nursing_school_db
CREATE DATABASE IF NOT EXISTS `nursing_school_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `nursing_school_db`;

-- Dumping structure for table nursing_school_db.activity_logs
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` text DEFAULT NULL,
  `ip_address` varchar(50) DEFAULT NULL,
  `machine_name` varchar(100) DEFAULT NULL,
  `log_time` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`log_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `activity_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.activity_logs: ~2 rows (approximately)
INSERT INTO `activity_logs` (`log_id`, `user_id`, `action`, `ip_address`, `machine_name`, `log_time`) VALUES
	(4, 1, 'Added new user \'Collins Agyei\' with username \'nanaagyei\'.', '::1', '::1', '2026-02-26 14:21:27'),
	(5, 1, 'Deleted user_id 3', '::1', '::1', '2026-02-26 14:21:42');

-- Dumping structure for table nursing_school_db.courses
CREATE TABLE IF NOT EXISTS `courses` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `course_code` varchar(20) NOT NULL,
  `course_title` varchar(255) NOT NULL,
  `default_credit_hour` int(11) NOT NULL,
  `default_weeks` int(11) DEFAULT 16,
  `is_clinical` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`course_id`),
  UNIQUE KEY `course_code` (`course_code`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.courses: ~45 rows (approximately)
INSERT INTO `courses` (`course_id`, `course_code`, `course_title`, `default_credit_hour`, `default_weeks`, `is_clinical`) VALUES
	(1, 'RMD 111', 'Anatomy and Physiology I', 3, 16, 0),
	(2, 'TCM 111', 'Therapeutic Communication', 3, 16, 0),
	(3, 'RMD 112', 'Basic Nursing', 3, 16, 0),
	(4, 'MIP 111', 'Microbiology & Infection Prevention/Control', 2, 16, 0),
	(5, 'ADJ 111', 'Professional Adjustment in Nursing', 2, 16, 0),
	(6, 'NMI 111', 'Nursing and Midwifery Informatics', 2, 16, 0),
	(7, 'SNM 111', 'Introductory Sociology', 2, 16, 0),
	(8, 'CLIN 111', 'CLINICAL EXPERIENCE', 3, 6, 0),
	(9, 'RMD 121', 'Anatomy and Physiology II', 3, 16, 0),
	(10, 'NTD 211', 'Nutrition and Dietetics', 2, 16, 0),
	(11, 'RMD 122', 'Health Promotion', 3, 16, 0),
	(12, 'RMD 123', 'Personal and Environmental Health', 2, 16, 0),
	(13, 'FED 121', 'First Aid, Emergency and Disaster Management', 3, 16, 0),
	(14, 'PNM 121', 'Introductory Psychology', 2, 16, 0),
	(15, 'STA 121', 'Statistics', 2, 16, 0),
	(16, 'ISL 321', 'Introduction to Sign Language', 1, 16, 0),
	(17, 'CLIN 1212', 'CLINICAL EXPERIENCE', 3, 6, 0),
	(18, 'RMD 211', 'Anatomy and Physiology of the Human Reproductive System and the Foetus I', 2, 16, 0),
	(19, 'RMD 213', 'Advanced Nursing', 3, 16, 0),
	(20, 'RMD 216', 'Physiology and Management of Normal Pregnancy', 3, 16, 0),
	(21, 'RMD 214', 'Medical Nursing', 3, 16, 0),
	(22, 'RMD 212', 'Pharmacology, Therapeutics and Pharmacovigilance', 2, 16, 0),
	(23, 'RMD 215', 'Surgical Nursing', 3, 16, 0),
	(24, 'IFL 321', 'Introductory French Language', 1, 16, 0),
	(25, 'CLIN 211', 'CLINICAL EXPERIENCE', 3, 6, 0),
	(26, 'RES 221', 'Research Methods', 3, 16, 0),
	(27, 'RMD 221', 'Physiology and Management of Normal Labour', 3, 16, 0),
	(28, 'RMD 224', 'Anatomy and Physiology of the Human Reproductive System and the Foetus II', 2, 16, 0),
	(29, 'RMD 223', 'Physiology and Management of the High Risk Neonate', 3, 16, 0),
	(30, 'RMD 225', 'Community Midwifery', 3, 16, 0),
	(31, 'RMD 222', 'Physiology and Management of Normal Puerperium and the Neonate', 3, 16, 0),
	(32, 'CLIN 221', 'CLINICAL EXPERIENCE', 3, 6, 0),
	(33, 'RMD 312', 'Physiology and Management of Abnormal Labour', 3, 16, 0),
	(34, 'SCM 311', 'Supply Chain Management', 2, 16, 0),
	(35, 'RMD 313', 'Management of Abnormal Puerperium', 2, 16, 0),
	(36, 'RMD 311', 'Physiology and Management of Abnormal Pregnancy', 3, 16, 0),
	(37, 'PSN 321', 'Psychiatry Nursing', 3, 16, 0),
	(38, 'RMD 315', 'Family Planning', 3, 16, 0),
	(39, 'CLIN 311', 'CLINICAL EXPERIENCE', 3, 6, 0),
	(40, 'RMD 316', 'Family Centered Maternity Care Study', 3, 16, 0),
	(41, 'RMD 323', 'Advanced Midwifery and Theatre Nursing', 2, 16, 0),
	(42, 'RMD 322', 'Gynaecological Conditions, and STIs', 2, 16, 0),
	(43, 'PHN 321', 'Public Health Nursing', 3, 16, 0),
	(44, 'RMD 321', 'Management and Administration in Midwifery', 3, 16, 0),
	(45, 'RSE 321', 'Relationship Marketing Strategy and Entrepreneurship', 3, 16, 0);

-- Dumping structure for table nursing_school_db.course_aliases
CREATE TABLE IF NOT EXISTS `course_aliases` (
  `alias_id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NOT NULL,
  `alias_name` varchar(100) NOT NULL,
  PRIMARY KEY (`alias_id`),
  KEY `course_id` (`course_id`),
  CONSTRAINT `course_aliases_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.course_aliases: ~187 rows (approximately)
INSERT INTO `course_aliases` (`alias_id`, `course_id`, `alias_name`) VALUES
	(1, 1, 'Anatomy I'),
	(2, 1, 'RMD111'),
	(3, 1, 'Anatomy & Physiology I'),
	(4, 1, 'ANAT1'),
	(5, 1, 'ANATOMY&PHY I'),
	(6, 2, 'Therapeutic Comm'),
	(7, 2, 'TCM111'),
	(8, 2, 'Therapeutic Communication'),
	(9, 2, 'COMM1'),
	(10, 2, 'THERAPUETIC COM.'),
	(11, 3, 'Basic Nursing'),
	(12, 3, 'RMD112'),
	(13, 3, 'Nursing Basics'),
	(14, 3, 'BNURS'),
	(15, 3, ' BASIC NURSING'),
	(16, 4, 'Microbiology'),
	(17, 4, 'MIP111'),
	(18, 4, 'Infection Prevention'),
	(19, 4, 'MICRO'),
	(20, 4, 'MICROBIOLOGY&IPR'),
	(21, 5, 'Prof Adjustment'),
	(22, 5, 'ADJ111'),
	(23, 5, 'Professional Adjustment'),
	(24, 5, 'PADJ'),
	(25, 5, 'PROF. ADJ'),
	(26, 6, 'Nursing Informatics'),
	(27, 6, 'NMI111'),
	(28, 6, 'Informatics'),
	(29, 6, 'NINF'),
	(30, 6, 'INFORMATICS'),
	(31, 7, 'Sociology I'),
	(32, 7, 'SNM111'),
	(33, 7, 'Intro Sociology'),
	(34, 7, 'SOC1'),
	(35, 7, 'SOCIOLOGY'),
	(36, 8, 'Clinical Exp I'),
	(37, 8, 'CLIN111'),
	(38, 8, 'Clinical Experience I'),
	(39, 8, 'CLIN1'),
	(40, 9, 'Anatomy II'),
	(41, 9, 'RMD121'),
	(42, 9, 'Anatomy & Physiology II'),
	(43, 9, 'ANAT2'),
	(44, 10, 'Nutrition'),
	(45, 10, 'NTD211'),
	(46, 10, 'Nutrition & Dietetics'),
	(47, 10, 'NUTR'),
	(48, 11, 'Health Promo'),
	(49, 11, 'RMD122'),
	(50, 11, 'Health Promotion'),
	(51, 11, 'HPROM'),
	(52, 12, 'Personal Health'),
	(53, 12, 'RMD123'),
	(54, 12, 'Personal & Env Health'),
	(55, 12, 'PHENV'),
	(56, 13, 'First Aid'),
	(57, 13, 'FED121'),
	(58, 13, 'Emergency Management'),
	(59, 13, 'FAID'),
	(60, 14, 'Intro Psychology'),
	(61, 14, 'PNM121'),
	(62, 14, 'Psychology I'),
	(63, 14, 'PSYCH1'),
	(64, 15, 'Statistics'),
	(65, 15, 'STA121'),
	(66, 15, 'Statistics I'),
	(67, 15, 'STAT1'),
	(68, 16, 'Sign Language'),
	(69, 16, 'ISL321'),
	(70, 16, 'Intro Sign Lang'),
	(71, 16, 'ISIGN'),
	(72, 17, 'Clinical Exp II'),
	(73, 17, 'CLIN1212'),
	(74, 17, 'Clinical Experience II'),
	(75, 17, 'CLIN2'),
	(76, 18, 'Anatomy Repro I'),
	(77, 18, 'RMD211'),
	(78, 18, 'Anatomy & Physiology Reproductive I'),
	(79, 18, 'ANATREP1'),
	(80, 19, 'Advanced Nursing'),
	(81, 19, 'RMD213'),
	(82, 19, 'Adv Nursing'),
	(83, 19, 'ADVNUR'),
	(84, 20, 'Physiology Pregnancy'),
	(85, 20, 'RMD216'),
	(86, 20, 'Physiology & Management of Normal Pregnancy'),
	(87, 20, 'PHYSNP'),
	(88, 21, 'Medical Nursing'),
	(89, 21, 'RMD214'),
	(90, 21, 'Med Nursing'),
	(91, 21, 'MEDNUR'),
	(92, 22, 'Pharmacology'),
	(93, 22, 'RMD212'),
	(94, 22, 'Pharm & Therapeutics'),
	(95, 22, 'PHARM'),
	(96, 23, 'Surgical Nursing'),
	(97, 23, 'RMD215'),
	(98, 23, 'Surg Nursing'),
	(99, 23, 'SURGNUR'),
	(100, 24, 'Intro French'),
	(101, 24, 'IFL321'),
	(102, 24, 'French Language I'),
	(103, 24, 'FRENCH1'),
	(104, 25, 'Clinical Exp III'),
	(105, 25, 'CLIN211'),
	(106, 25, 'Clinical Experience III'),
	(107, 25, 'CLIN3'),
	(108, 26, 'Research Methods'),
	(109, 26, 'RES221'),
	(110, 26, 'Research I'),
	(111, 26, 'RESMETH'),
	(112, 27, 'Physiology Labour'),
	(113, 27, 'RMD221'),
	(114, 27, 'Physiology & Labour'),
	(115, 27, 'PHYSLAB'),
	(116, 28, 'Anatomy Repro II'),
	(117, 28, 'RMD224'),
	(118, 28, 'Anatomy & Physiology Reproductive II'),
	(119, 28, 'ANATREP2'),
	(120, 29, 'High Risk Neonate'),
	(121, 29, 'RMD223'),
	(122, 29, 'High Risk Neonate Care'),
	(123, 29, 'HRNEO'),
	(124, 30, 'Community Midwifery'),
	(125, 30, 'RMD225'),
	(126, 30, 'Comm Midwifery'),
	(127, 30, 'COMMID'),
	(128, 31, 'Normal Puerperium'),
	(129, 31, 'RMD222'),
	(130, 31, 'Physiology Normal Puerperium & Neonate'),
	(131, 31, 'NORMPUER'),
	(132, 32, 'Clinical Exp IV'),
	(133, 32, 'CLIN221'),
	(134, 32, 'Clinical Experience IV'),
	(135, 32, 'CLIN4'),
	(136, 33, 'Abnormal Labour'),
	(137, 33, 'RMD312'),
	(138, 33, 'Physiology & Management Abnormal Labour'),
	(139, 33, 'ABNLAB'),
	(140, 34, 'Supply Chain'),
	(141, 34, 'SCM311'),
	(142, 34, 'Supply Chain Management'),
	(143, 34, 'SCMGT'),
	(144, 35, 'Abnormal Puerperium'),
	(145, 35, 'RMD313'),
	(146, 35, 'Management Abnormal Puerperium'),
	(147, 35, 'ABNPUER'),
	(148, 36, 'Abnormal Pregnancy'),
	(149, 36, 'RMD311'),
	(150, 36, 'Physiology & Management Abnormal Pregnancy'),
	(151, 36, 'ABNPREG'),
	(152, 37, 'Psychiatry Nursing'),
	(153, 37, 'PSN321'),
	(154, 37, 'Psych Nursing'),
	(155, 37, 'PSYNUR'),
	(156, 38, 'Family Planning'),
	(157, 38, 'RMD315'),
	(158, 38, 'Family Planning & Reproductive Health'),
	(159, 38, 'FAMPLAN'),
	(160, 39, 'Clinical Exp V'),
	(161, 39, 'CLIN311'),
	(162, 39, 'Clinical Experience V'),
	(163, 39, 'CLIN5'),
	(164, 40, 'Family Maternity Care'),
	(165, 40, 'RMD316'),
	(166, 40, 'Family Centered Maternity Care Study'),
	(167, 40, 'FAMMAT'),
	(168, 41, 'Advanced Midwifery'),
	(169, 41, 'RMD323'),
	(170, 41, 'Advanced Midwifery & Theatre Nursing'),
	(171, 41, 'ADVMID'),
	(172, 42, 'Gyn Conditions'),
	(173, 42, 'RMD322'),
	(174, 42, 'Gynaecological Conditions & STIs'),
	(175, 42, 'GYNCND'),
	(176, 43, 'Public Health'),
	(177, 43, 'PHN321'),
	(178, 43, 'Public Health Nursing'),
	(179, 43, 'PUBHLTH'),
	(180, 44, 'Midwifery Mgmt'),
	(181, 44, 'RMD321'),
	(182, 44, 'Management & Admin in Midwifery'),
	(183, 44, 'MIDMGMT'),
	(184, 45, 'Relationship Marketing'),
	(185, 45, 'RSE321'),
	(186, 45, 'Relationship Marketing & Entrepreneurship'),
	(187, 45, 'RELMKT');

-- Dumping structure for table nursing_school_db.course_offerings
CREATE TABLE IF NOT EXISTS `course_offerings` (
  `offering_id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NOT NULL,
  `level_id` int(11) NOT NULL,
  `semester_id` int(11) NOT NULL,
  PRIMARY KEY (`offering_id`),
  UNIQUE KEY `course_id` (`course_id`,`level_id`,`semester_id`),
  KEY `level_id` (`level_id`),
  KEY `semester_id` (`semester_id`),
  CONSTRAINT `course_offerings_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`),
  CONSTRAINT `course_offerings_ibfk_2` FOREIGN KEY (`level_id`) REFERENCES `levels` (`level_id`),
  CONSTRAINT `course_offerings_ibfk_3` FOREIGN KEY (`semester_id`) REFERENCES `semesters` (`semester_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.course_offerings: ~45 rows (approximately)
INSERT INTO `course_offerings` (`offering_id`, `course_id`, `level_id`, `semester_id`) VALUES
	(1, 1, 1, 1),
	(2, 2, 1, 1),
	(3, 3, 1, 1),
	(4, 4, 1, 1),
	(5, 5, 1, 1),
	(6, 6, 1, 1),
	(7, 7, 1, 1),
	(8, 8, 1, 1),
	(9, 9, 1, 2),
	(10, 10, 1, 2),
	(11, 11, 1, 2),
	(12, 12, 1, 2),
	(13, 13, 1, 2),
	(14, 14, 1, 2),
	(15, 15, 1, 2),
	(16, 16, 1, 2),
	(17, 17, 1, 2),
	(18, 18, 2, 1),
	(19, 19, 2, 1),
	(20, 20, 2, 1),
	(21, 21, 2, 1),
	(22, 22, 2, 1),
	(23, 23, 2, 1),
	(24, 24, 2, 1),
	(25, 25, 2, 1),
	(26, 26, 2, 2),
	(27, 27, 2, 2),
	(28, 28, 2, 2),
	(29, 29, 2, 2),
	(30, 30, 2, 2),
	(31, 31, 2, 2),
	(32, 32, 2, 2),
	(33, 33, 3, 1),
	(34, 34, 3, 1),
	(35, 35, 3, 1),
	(36, 36, 3, 1),
	(37, 37, 3, 1),
	(38, 38, 3, 1),
	(39, 39, 3, 1),
	(40, 40, 3, 2),
	(41, 41, 3, 2),
	(42, 42, 3, 2),
	(43, 43, 3, 2),
	(44, 44, 3, 2),
	(45, 45, 3, 2);

-- Dumping structure for table nursing_school_db.levels
CREATE TABLE IF NOT EXISTS `levels` (
  `level_id` int(11) NOT NULL AUTO_INCREMENT,
  `level_name` varchar(20) NOT NULL,
  PRIMARY KEY (`level_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.levels: ~3 rows (approximately)
INSERT INTO `levels` (`level_id`, `level_name`) VALUES
	(1, 'Year 1'),
	(2, 'Year 2'),
	(3, 'Year 3');

-- Dumping data for table nursing_school_db.results: ~0 rows (approximately)

-- Dumping structure for table nursing_school_db.roles
CREATE TABLE IF NOT EXISTS `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `role_name` (`role_name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.roles: ~6 rows (approximately)
INSERT INTO `roles` (`role_id`, `role_name`, `description`, `created_at`) VALUES
	(1, 'SuperAdmin', 'Full system access', '2026-02-16 22:45:57'),
	(2, 'Registrar', 'Can upload and modify results', '2026-02-16 22:45:57'),
	(3, 'Lecturer', 'Can upload results only', '2026-02-16 22:45:57'),
	(4, 'Viewer', 'Can only view/query results', '2026-02-16 22:45:57'),
	(5, 'Principal', 'Can only view/query results', '2026-02-26 10:41:02'),
	(6, 'Academic_Head', 'Can upload and modify results', '2026-02-26 13:46:50');

-- Dumping structure for table nursing_school_db.role_print_limits
CREATE TABLE IF NOT EXISTS `role_print_limits` (
  `role_name` varchar(100) NOT NULL,
  `max_prints` int(11) NOT NULL,
  PRIMARY KEY (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.role_print_limits: ~7 rows (approximately)
INSERT INTO `role_print_limits` (`role_name`, `max_prints`) VALUES
	('academic_head', 2000),
	('admin', 800),
	('Lecturer', 0),
	('principal', 8888),
	('Registrar', 0),
	('SuperAdmin', 9999),
	('Viewer', 0);

-- Dumping structure for table nursing_school_db.semesters
CREATE TABLE IF NOT EXISTS `semesters` (
  `semester_id` int(11) NOT NULL AUTO_INCREMENT,
  `semester_name` varchar(20) NOT NULL,
  PRIMARY KEY (`semester_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.semesters: ~2 rows (approximately)
INSERT INTO `semesters` (`semester_id`, `semester_name`) VALUES
	(1, 'Semester 1'),
	(2, 'Semester 2');

-- Dumping data for table nursing_school_db.semester_gpa: ~0 rows (approximately)

-- Dumping structure for table nursing_school_db.students
CREATE TABLE IF NOT EXISTS `students` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `matric_no` varchar(50) NOT NULL,
  `full_name` varchar(200) NOT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `admission_year` int(11) DEFAULT NULL,
  `graduation_year` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`student_id`),
  UNIQUE KEY `matric_no` (`matric_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.students: ~0 rows (approximately)

-- Dumping structure for table nursing_school_db.system_settings
CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) DEFAULT NULL,
  `school_address` varchar(255) DEFAULT NULL,
  `school_phone` varchar(100) DEFAULT NULL,
  `school_email` varchar(150) DEFAULT NULL,
  `principal_name` varchar(150) DEFAULT NULL,
  `logo_path` varchar(255) DEFAULT NULL,
  `watermark_path` varchar(255) DEFAULT NULL,
  `school_Domain` varchar(255) DEFAULT NULL,
  `system_setting_col1` varchar(255) DEFAULT NULL,
  `system_setting_col2` varchar(255) DEFAULT NULL,
  `system_setting_col3` varchar(255) DEFAULT NULL,
  `system_setting_col4` varchar(255) DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `digital_signature_path` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`setting_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.system_settings: ~0 rows (approximately)
INSERT INTO `system_settings` (`setting_id`, `school_name`, `school_address`, `school_phone`, `school_email`, `principal_name`, `logo_path`, `watermark_path`, `school_Domain`, `system_setting_col1`, `system_setting_col2`, `system_setting_col3`, `system_setting_col4`, `updated_at`, `digital_signature_path`) VALUES
	(1, 'S.D.A. NURSING AND MIDWIFERY TRAINING COLLEGE, ASANTA', 'Asanta, Ghana', '+233 xxx xxx xxx', 'info@sdacollege.edu.gh', 'Collins Agyei Darkwah', 'Images/schoolCrest_color.png', 'Images/schoolCrest_color.png', 'asantanursing.com', NULL, NULL, NULL, NULL, '2026-02-26 06:35:42', 'Images/Principal_signature.png');

-- Dumping structure for table nursing_school_db.transcripts
CREATE TABLE IF NOT EXISTS `transcripts` (
  `transcript_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `transcript_code` varchar(100) DEFAULT NULL,
  `verification_hash` varchar(255) DEFAULT NULL,
  `final_cgpa` decimal(4,2) DEFAULT NULL,
  `class_obtained` varchar(50) DEFAULT NULL,
  `generated_by` int(11) DEFAULT NULL,
  `generated_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`transcript_id`),
  UNIQUE KEY `transcript_code` (`transcript_code`),
  KEY `student_id` (`student_id`),
  KEY `generated_by` (`generated_by`),
  CONSTRAINT `transcripts_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`),
  CONSTRAINT `transcripts_ibfk_2` FOREIGN KEY (`generated_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.transcripts: ~0 rows (approximately)

-- Dumping structure for table nursing_school_db.transcript_log
CREATE TABLE IF NOT EXISTS `transcript_log` (
  `transcript_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `serial_number` varchar(50) NOT NULL,
  `printed_date` datetime NOT NULL,
  `is_reprint` tinyint(1) DEFAULT 0,
  `verification_hash` varchar(128) DEFAULT NULL,
  `print_count` int(11) DEFAULT 1,
  `printed_by` int(11) NOT NULL,
  `revoked` tinyint(1) NOT NULL DEFAULT 0,
  `revoked_by` int(11) DEFAULT NULL,
  `revoked_date` datetime DEFAULT NULL,
  `minute_stamp` varchar(20) NOT NULL,
  PRIMARY KEY (`transcript_id`),
  KEY `printed_by` (`printed_by`),
  KEY `idx_student_minute` (`student_id`,`minute_stamp`),
  CONSTRAINT `transcript_log_ibfk_1` FOREIGN KEY (`printed_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table nursing_school_db.transcript_log: ~0 rows (approximately)

-- Dumping structure for table nursing_school_db.transcript_print_logs
CREATE TABLE IF NOT EXISTS `transcript_print_logs` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `printed_by` int(11) NOT NULL,
  `user_role` varchar(100) DEFAULT NULL,
  `print_hash` varchar(255) DEFAULT NULL,
  `printed_on` datetime DEFAULT current_timestamp(),
  `ip_address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`log_id`),
  KEY `student_id` (`student_id`),
  KEY `printed_by` (`printed_by`),
  CONSTRAINT `transcript_print_logs_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`),
  CONSTRAINT `transcript_print_logs_ibfk_2` FOREIGN KEY (`printed_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

