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

START TRANSACTION;



--
-- Database: `booking`
--

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

--
-- Table structure for table `audit_logs`
--

CREATE TABLE `audit_logs` (
  `id` int(11) NOT NULL,
  `booking_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `action` varchar(60) NOT NULL,
  `details` varchar(600) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `audit_logs`
--

INSERT INTO `audit_logs` (`id`, `booking_id`, `actor_id`, `action`, `details`, `created_at`) VALUES
(1, 1, 2, 'created', 'New booking request submitted', '2025-08-19 08:27:55'),
(2, 1, 1, 'approved', '', '2025-08-19 08:28:25'),
(3, 1, 2, 'cancelled', 'Postponed', '2025-08-19 09:27:33'),
(4, 2, 2, 'created', 'New booking request submitted', '2025-08-25 03:42:43'),
(5, 2, 1, 'rejected', '', '2025-08-29 04:22:10'),
(6, 3, 2, 'created', 'New booking request submitted', '2025-08-29 08:37:42'),
(7, 3, 1, 'rejected', '', '2025-08-29 08:38:29'),
(8, 4, 2, 'created', 'New booking request submitted', '2025-08-29 08:56:56'),
(9, 4, 1, 'approved', '', '2025-08-29 09:01:04');

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

--
-- Table structure for table `blackout_dates`
--

CREATE TABLE `blackout_dates` (
  `id` int(11) NOT NULL,
  `hall_id` int(11) NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `reason` varchar(240) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ;

--
-- Dumping data for table `blackout_dates`
--

INSERT INTO `blackout_dates` (`id`, `hall_id`, `start_datetime`, `end_datetime`, `reason`, `created_by`, `created_at`) VALUES
(2, 1, '2025-08-21 09:00:00', '2025-08-21 12:30:00', 'GA Conference', 1, '2025-08-19 09:04:03');

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

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

CREATE TABLE `bookings` (
  `id` int(11) NOT NULL,
  `hall_id` int(11) NOT NULL,
  `requested_by` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `event_title` varchar(200) NOT NULL,
  `purpose` text DEFAULT NULL,
  `participants` int(11) NOT NULL DEFAULT 0,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `admin_comment` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ;

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

INSERT INTO `bookings` (`id`, `hall_id`, `requested_by`, `branch_id`, `event_title`, `purpose`, `participants`, `start_datetime`, `end_datetime`, `status`, `admin_comment`, `created_at`) VALUES
(1, 1, 2, 2, 'DCDC Meeting', 'DCDC Meeting with relavant parties', 60, '2025-08-20 09:00:00', '2025-08-20 10:30:00', 'cancelled', 'Postponed', '2025-08-19 08:27:55'),
(2, 1, 2, 2, 'DCDC Meeting', 'DCDC meeting with principals', 50, '2025-08-27 10:00:00', '2025-08-27 11:30:00', 'rejected', NULL, '2025-08-25 03:42:43'),
(3, 1, 2, 2, 'Meeting with principal', 'meeting with principal regards school regulation', 20, '2025-08-30 09:30:00', '2025-08-30 10:30:00', 'rejected', NULL, '2025-08-29 08:37:42'),
(4, 1, 2, 2, 'Meeting with principal', '', 10, '2025-08-29 14:30:00', '2025-08-29 15:30:00', 'approved', NULL, '2025-08-29 08:56:56');

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

--
-- Table structure for table `booking_equipment`
--

CREATE TABLE `booking_equipment` (
  `booking_id` int(11) NOT NULL,
  `equipment_id` int(11) NOT NULL,
  `qty_requested` int(11) NOT NULL DEFAULT 0
) ;

--
-- Dumping data for table `booking_equipment`
--

INSERT INTO `booking_equipment` (`booking_id`, `equipment_id`, `qty_requested`) VALUES
(1, 1, 1),
(1, 3, 2),
(1, 4, 1),
(2, 1, 1),
(2, 2, 1),
(2, 3, 2),
(3, 1, 1),
(3, 2, 1),
(3, 3, 1);

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

--
-- Table structure for table `branches`
--

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

--
-- Dumping data for table `branches`
--

INSERT INTO `branches` (`id`, `name`) VALUES
(1, 'Administration'),
(3, 'Finance'),
(4, 'Land'),
(2, 'Planning'),
(5, 'Samurdhi');

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

--
-- Table structure for table `equipment`
--

CREATE TABLE `equipment` (
  `id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `qty_available` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `equipment`
--

INSERT INTO `equipment` (`id`, `name`, `qty_available`) VALUES
(1, 'Projector', 1),
(2, 'Sound System', 1),
(3, 'Wireless Mic', 2),
(4, 'Whiteboard', 1);

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

--
-- Table structure for table `halls`
--

CREATE TABLE `halls` (
  `id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `capacity` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `halls`
--

INSERT INTO `halls` (`id`, `name`, `capacity`) VALUES
(1, 'Main Conference Hall', 120),
(2, 'Mini Conference Hall', 20);

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `body` varchar(600) DEFAULT NULL,
  `url` varchar(300) DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `user_id`, `title`, `body`, `url`, `is_read`, `created_at`) VALUES
(1, 1, 'New Booking Request', 'DCDC Meeting — Sample Staff • 2025-08-20 09:00 to 2025-08-20 10:30', './admin_requests.php', 1, '2025-08-19 08:27:55'),
(2, 2, 'Booking Approved', 'DCDC Meeting', './my_bookings.php', 1, '2025-08-19 08:28:25'),
(3, 1, 'New Booking Request', 'DCDC Meeting — Puvitharsan • 2025-08-27 10:00 to 2025-08-27 11:30', './admin_requests.php', 1, '2025-08-25 03:42:43'),
(4, 2, 'Booking Rejected', 'DCDC Meeting', './my_bookings.php', 1, '2025-08-29 04:22:10'),
(5, 1, 'New user registration pending', 'Sivan thurka <sivan@gmail.com> has requested access.', './users.php', 1, '2025-08-29 07:50:54'),
(6, 1, 'New user registration pending', 'Kajinthan <ragukaji00@gmail.com> has requested access.', './users.php', 1, '2025-08-29 08:34:36'),
(7, 1, 'New Booking Request', 'Meeting with principal — Puvitharsan • 2025-08-30 09:30 to 2025-08-30 10:30', './admin_requests.php', 1, '2025-08-29 08:37:42'),
(8, 2, 'Booking Rejected', 'Meeting with principal', './my_bookings.php', 1, '2025-08-29 08:38:29'),
(9, 1, 'New Booking Request', 'Meeting with principal — Puvitharsan • 2025-08-29 14:30 to 2025-08-29 15:30', './admin_requests.php', 1, '2025-08-29 08:56:56'),
(10, 2, 'Booking Approved', 'Meeting with principal', './my_bookings.php', 1, '2025-08-29 09:01:04'),
(11, 4, 'Account approved', 'Your account has been activated. You can now sign in.', './index.php', 0, '2025-08-29 09:31:27'),
(12, 2, 'Account deactivated', 'Your account has been deactivated by an administrator.', '', 0, '2025-08-29 09:32:33'),
(13, 2, 'Account approved', 'Your account has been activated. You can now sign in.', './index.php', 0, '2025-08-29 09:34:57');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_audit_booking` (`booking_id`),
  ADD KEY `idx_audit_actor` (`actor_id`);

--
-- Indexes for table `blackout_dates`
--
ALTER TABLE `blackout_dates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_blackout_hall_time` (`hall_id`,`start_datetime`,`end_datetime`),
  ADD KEY `fk_blackout_creator` (`created_by`);

--
-- Indexes for table `bookings`
--
ALTER TABLE `bookings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_booking_hall_time` (`hall_id`,`start_datetime`,`end_datetime`),
  ADD KEY `idx_booking_status` (`status`),
  ADD KEY `idx_booking_requested_by` (`requested_by`),
  ADD KEY `idx_booking_branch` (`branch_id`);

--
-- Indexes for table `booking_equipment`
--
ALTER TABLE `booking_equipment`
  ADD PRIMARY KEY (`booking_id`,`equipment_id`),
  ADD KEY `fk_be_equipment` (`equipment_id`);

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

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

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

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_notif_user_read` (`user_id`,`is_read`,`id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `blackout_dates`
--
ALTER TABLE `blackout_dates`
  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 for table `branches`
--
ALTER TABLE `branches`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD CONSTRAINT `fk_audit_actor` FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_audit_booking` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `blackout_dates`
--
ALTER TABLE `blackout_dates`
  ADD CONSTRAINT `fk_blackout_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_blackout_hall` FOREIGN KEY (`hall_id`) REFERENCES `halls` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `bookings`
--
ALTER TABLE `bookings`
  ADD CONSTRAINT `fk_booking_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_booking_hall` FOREIGN KEY (`hall_id`) REFERENCES `halls` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_booking_requester` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `booking_equipment`
--
ALTER TABLE `booking_equipment`
  ADD CONSTRAINT `fk_be_booking` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_be_equipment` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

