-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `lab_booking_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `batches`
--

CREATE TABLE `batches` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `program` varchar(100) DEFAULT NULL,
  `year` varchar(20) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `batches`
--

INSERT INTO `batches` (`id`, `name`, `program`, `year`, `is_active`) VALUES
(1, 'Diploma 2025 A', 'Diploma in Nursing', '2025', 0),
(2, 'Certificate 2025 B', 'Certificate in Midwifery', '2025', 0),
(3, 'D21', 'Public Health Nursing', '2025/2026', 1),
(4, 'C26', 'Nurse Assistant Preventive', '2025/2026', 1),
(5, 'D19', 'Public Health Nursing', '2023/2024', 1),
(6, 'D20', 'Public Health Nursing', '2024/2025', 1);

-- --------------------------------------------------------

--
-- Table structure for table `batch_courses`
--

CREATE TABLE `batch_courses` (
  `id` int(11) NOT NULL,
  `batch_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `bookings`
--

CREATE TABLE `bookings` (
  `id` int(11) NOT NULL,
  `tutor_id` int(11) NOT NULL,
  `lab_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `batch_id` int(11) DEFAULT NULL,
  `purpose` enum('Quiz','Mid-semester','Exam','Practical','Other') DEFAULT 'Quiz',
  `expected_students` int(11) DEFAULT NULL,
  `sections` int(11) DEFAULT 1,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected','completed','cancelled') NOT NULL DEFAULT 'pending',
  `approval_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `bookings`
--

INSERT INTO `bookings` (`id`, `tutor_id`, `lab_id`, `course_id`, `batch_id`, `purpose`, `expected_students`, `sections`, `start_time`, `end_time`, `notes`, `status`, `approval_by`, `created_at`) VALUES
(1, 2, 1, 1, 1, 'Quiz', 250, 1, '2025-12-11 17:44:00', '2025-12-12 16:44:00', 'quiz', 'approved', 1, '2025-12-10 16:45:06'),
(2, 2, 3, 3, 1, 'Quiz', 260, 2, '2025-12-11 09:00:00', '2025-12-11 09:30:00', 'quiz', 'rejected', 1, '2025-12-10 17:29:29'),
(3, 2, 3, 4, 5, 'Quiz', 250, 2, '2025-12-11 10:30:00', '2025-12-11 23:30:00', NULL, 'pending', NULL, '2025-12-10 18:12:05');

-- --------------------------------------------------------

--
-- Table structure for table `courses`
--

CREATE TABLE `courses` (
  `id` int(11) NOT NULL,
  `code` varchar(50) NOT NULL,
  `name` varchar(150) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `courses`
--

INSERT INTO `courses` (`id`, `code`, `name`, `department_id`, `is_active`) VALUES
(1, 'ANAT101', 'Anatomy I', 2, 1),
(2, 'PHYS201', 'Physiology II', 2, 1),
(3, 'ICT100', 'Intro to Computing', 1, 1),
(4, 'AUTO', 'Nursing Informatics', NULL, 1);

-- --------------------------------------------------------

--
-- Table structure for table `course_tutors`
--

CREATE TABLE `course_tutors` (
  `id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `tutor_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `departments`
--

CREATE TABLE `departments` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `departments`
--

INSERT INTO `departments` (`id`, `name`) VALUES
(1, 'ICT'),
(3, 'Midwifery'),
(2, 'Nursing');

-- --------------------------------------------------------

--
-- Table structure for table `labs`
--

CREATE TABLE `labs` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `capacity` int(11) NOT NULL DEFAULT 0,
  `status` enum('available','maintenance') NOT NULL DEFAULT 'available',
  `open_time` time DEFAULT '08:00:00',
  `close_time` time DEFAULT '17:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `labs`
--

INSERT INTO `labs` (`id`, `name`, `capacity`, `status`, `open_time`, `close_time`) VALUES
(1, 'Main Lab', 110, 'maintenance', '08:00:00', '17:00:00'),
(2, 'Annex Lab', 40, 'maintenance', '08:00:00', '17:00:00'),
(3, 'Exam Center', 150, 'available', '08:00:00', '17:00:00');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `batches`
--
ALTER TABLE `batches`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

--
-- Indexes for table `batch_courses`
--
ALTER TABLE `batch_courses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `batch_course` (`batch_id`,`course_id`);

--
-- Indexes for table `bookings`
--
ALTER TABLE `bookings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `lab_time_idx` (`lab_id`,`start_time`,`end_time`),
  ADD KEY `tutor_id` (`tutor_id`),
  ADD KEY `course_id` (`course_id`),
  ADD KEY `batch_id` (`batch_id`);

--
-- Indexes for table `courses`
--
ALTER TABLE `courses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`),
  ADD UNIQUE KEY `code_dept` (`code`,`department_id`),
  ADD KEY `department_id` (`department_id`);

--
-- Indexes for table `course_tutors`
--
ALTER TABLE `course_tutors`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `course_tutor` (`course_id`,`tutor_id`);

--
-- Indexes for table `departments`
--
ALTER TABLE `departments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

--
-- Indexes for table `labs`
--
ALTER TABLE `labs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `batches`
--
ALTER TABLE `batches`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `batch_courses`
--
ALTER TABLE `batch_courses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bookings`
--
ALTER TABLE `bookings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `courses`
--
ALTER TABLE `courses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `course_tutors`
--
ALTER TABLE `course_tutors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `departments`
--
ALTER TABLE `departments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `labs`
--
ALTER TABLE `labs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bookings`
--
ALTER TABLE `bookings`
  ADD CONSTRAINT `bookings_ibfk_1` FOREIGN KEY (`tutor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `bookings_ibfk_2` FOREIGN KEY (`lab_id`) REFERENCES `labs` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `bookings_ibfk_3` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `bookings_ibfk_4` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `courses`
--
ALTER TABLE `courses`
  ADD CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL;
COMMIT;

