

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

--
-- Table structure for table `access_logs`
--

CREATE TABLE `access_logs` (
  `id` int NOT NULL,
  `booking_id` int NOT NULL,
  `access_datetime` datetime NOT NULL,
  `access_result` enum('granted','denied') NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ----

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

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

CREATE TABLE `bookings` (
  `id` int NOT NULL,
  `room_id` varchar(20) NOT NULL,
  `guest_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `arrival_datetime` datetime NOT NULL,
  `departure_datetime` datetime NOT NULL,
  `access_code` varchar(10) NOT NULL,
  `notes` text,
  `status` enum('active','completed','cancelled') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `entry_point_id` varchar(255) NOT NULL,
  `positions` varchar(255) NOT NULL,
  `room_position` int DEFAULT NULL,
  `codes_sent` tinyint(1) NOT NULL DEFAULT '0'
) ----

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

INSERT INTO `bookings` (`id`, `room_id`, `guest_name`, `email`, `phone`, `arrival_datetime`, `departure_datetime`, `access_code`, `notes`, `status`, `created_at`, `updated_at`, `entry_point_id`, `positions`, `room_position`, `codes_sent`) VALUES
(1, 'room1', 'FRANCIS MAGOMERE', 'magomerefrancis61@gmail.com', '0796381603', '2025-03-09 12:59:00', '2025-03-10 12:59:00', '960873', NULL, 'active', '2025-03-09 13:00:02', '2025-03-20 21:22:01', '', '', NULL, 0),
(5, 'room1', 'FRANCIS MAGOMERE', 'magomerefrancis61@gmail.com', '', '2025-03-21 00:43:00', '2025-03-26 02:43:00', '00100', 'sdfrd', 'active', '2025-03-20 21:43:55', '2025-03-20 21:43:55', 'entry3,entry1', '2,2', NULL, 0),
(6, 'room1', 'peter', 'Juma@gmail.com', '', '2025-03-21 02:56:00', '2025-03-26 02:56:00', '412499', 'ssa', 'active', '2025-03-20 21:57:09', '2025-03-20 21:57:09', 'entry3,entry1', '2,3', 1, 0),
(7, 'room3', 'm,nsdfkjdsn dsfjk', 'magomerefrancis61@gmail.com', '04123111444', '2025-03-22 03:02:00', '2025-03-23 01:03:00', '343558', '4232', 'active', '2025-03-20 22:00:52', '2025-03-20 22:00:52', 'entry3', '5', 4, 0),
(8, 'room1', 'Jckfkf', 'fnnffj@gmail.com', '0796381603', '2025-03-26 10:02:00', '2025-03-27 06:02:00', '816812', 'Fkfkf', 'active', '2025-03-21 07:03:10', '2025-03-21 07:03:10', 'entry3,entry1,entry2', '23,2,3', 2, 0),
(9, 'room7', 'Test', 'Test@gmail.com', '0796381603', '2025-03-21 11:11:00', '2025-03-22 04:15:00', '940903', 'ghfgh', 'active', '2025-03-21 10:10:23', '2025-03-21 10:12:29', 'entry44', '12', 12, 1);

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

--
-- Table structure for table `entry_points`
--

CREATE TABLE `entry_points` (
  `id` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'IPv4 or IPv6 address'
) ----

--
-- Dumping data for table `entry_points`
--

INSERT INTO `entry_points` (`id`, `name`, `description`, `created_at`, `updated_at`, `ip_address`) VALUES
('entry1', 'Main Entrance', 'Front door at the main lobby', '2025-03-19 12:29:44', '2025-03-19 12:29:44', NULL),
('entry2', 'Side Entrance', 'Side entrance near the parking lot', '2025-03-19 12:29:44', '2025-03-19 12:29:44', NULL),
('entry3', 'Back Entrance', 'Back entrance near the garden', '2025-03-19 12:29:44', '2025-03-19 12:29:44', NULL),
('entry4', 'Backdoor', 'good Door', '2025-03-21 09:16:56', '2025-03-21 09:16:56', NULL),
('entry44', 'Backdoor', 'dfgf', '2025-03-21 09:30:09', '2025-03-21 09:30:09', '192.168.180.22'),
('ttt', 'tttt', 'tttt', '2025-03-21 09:17:15', '2025-03-21 09:17:15', NULL);

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

--
-- Table structure for table `entry_point_pins`
--

CREATE TABLE `entry_point_pins` (
  `id` int NOT NULL,
  `entry_point_id` varchar(20) NOT NULL,
  `position` int NOT NULL,
  `pin_code` varchar(10) NOT NULL,
  `booking_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `access_logs`
--
ALTER TABLE `access_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `booking_id` (`booking_id`);

--
-- Indexes for table `bookings`
--
ALTER TABLE `bookings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `room_id` (`room_id`);

--
-- Indexes for table `entry_points`
--
ALTER TABLE `entry_points`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `entry_point_pins`
--
ALTER TABLE `entry_point_pins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_entry_position` (`entry_point_id`,`position`),
  ADD KEY `booking_id` (`booking_id`);



--
-- Indexesss for table `room_entry_points`
--
ALTER TABLE `room_entry_points`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_room_entry` (`room_id`,`entry_point_id`),
  ADD KEY `entry_point_id` (`entry_point_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `access_logs`
--
ALTER TABLE `access_logs`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bookings`
--
ALTER TABLE `bookings`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `entry_point_pins`
--
ALTER TABLE `entry_point_pins`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `room_entry_points`
--
ALTER TABLE `room_entry_points`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `access_logs`
--
ALTER TABLE `access_logs`
  ADD CONSTRAINT `access_logs_ibfk_1` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `bookings`
--
ALTER TABLE `bookings`
  ADD CONSTRAINT `bookings_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE RESTRICT;

--
-- Constraints for table `entry_point_pins`
--
ALTER TABLE `entry_point_pins`
  ADD CONSTRAINT `entry_point_pins_ibfk_1` FOREIGN KEY (`entry_point_id`) REFERENCES `entry_points` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `entry_point_pins_ibfk_2` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `room_entry_points`
--
ALTER TABLE `room_entry_points`
  ADD CONSTRAINT `room_entry_points_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `room_entry_points_ibfk_2` FOREIGN KEY (`entry_point_id`) REFERENCES `entry_points` (`id`) ON DELETE CASCADE;
COMMIT;

ALTER TABLE bookings ADD COLUMN codes_cleared TINYINT(1) NOT NULL DEFAULT 0;