-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 02, 2025 at 07:57 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `zoo_medan`
--

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

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `event_date` date NOT NULL,
  `image_url` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`id`, `title`, `content`, `event_date`, `image_url`) VALUES
(4, 'Festival Budaya Karo', 'Hadirilah Festival Budaya Karo yang diadakan pada Hari Minggu, Tanggal 17 Agustus 2025. Mari ramaikan Central Park Zoo and Resort Medan bersama keluarga dan teman tercinta di Hari Kemerdekaan Republik Indonesia. Akan ada Songkran Pesta Air dan dimeriahkan oleh Narta Siregar, Averiana Barus, Mey Permata, Jacky Raju Sembiring, Gundala-gundala Karo, dan Ndikar Karo.', '2025-08-17', '../images/budaya_karo.jpeg'),
(5, 'Drone Competition', 'Cinematic videos with drones. Total Price Rp2.250.000,-. ', '2025-08-17', '../images/drone_competition.jpeg'),
(6, 'Test', 'Test test test', '2025-09-25', '../images/zoo.png'),
(7, 'Hello World!', 'Hi, My Name is Retno Sekar Dinarsih', '2024-09-04', '../images/aviary.png');

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

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `order_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `total` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`id`, `order_date`, `total`) VALUES
(1, '2025-08-31 12:07:26', 360000),
(2, '2025-08-31 12:09:12', 200000),
(3, '2025-08-31 15:21:27', 290000),
(4, '2025-08-31 15:24:05', 300000),
(5, '2025-08-31 15:29:36', 200000),
(6, '2025-08-31 15:35:13', 160000),
(7, '2025-09-01 07:15:33', 100000),
(8, '2025-09-02 15:00:15', 230000),
(9, '2025-09-02 15:06:41', 300000),
(10, '2025-09-02 15:09:56', 160000),
(11, '2025-09-02 15:12:26', 200000),
(12, '2025-09-02 15:12:50', 120000),
(13, '2025-09-02 15:15:53', 200000),
(14, '2025-09-02 17:19:16', 560000);

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

--
-- Table structure for table `order_items`
--

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  `subtotal` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `order_items`
--

INSERT INTO `order_items` (`id`, `order_id`, `product_id`, `qty`, `subtotal`) VALUES
(1, 1, 4, 1, 70000),
(2, 1, 3, 2, 50000),
(3, 1, 1, 6, 240000),
(4, 2, 2, 4, 200000),
(5, 3, 3, 2, 50000),
(6, 3, 1, 6, 240000),
(7, 4, 2, 6, 300000),
(8, 5, 2, 4, 200000),
(9, 6, 1, 4, 160000),
(10, 7, 2, 2, 100000),
(11, 8, 1, 4, 160000),
(12, 8, 4, 1, 70000),
(13, 9, 2, 5, 250000),
(14, 9, 3, 2, 50000),
(15, 10, 1, 4, 160000),
(16, 11, 2, 4, 200000),
(17, 12, 1, 3, 120000),
(18, 13, 2, 4, 200000),
(19, 14, 5, 2, 200000),
(20, 14, 1, 9, 360000);

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

--
-- Table structure for table `products`
--

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

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`, `description`, `price`) VALUES
(1, 'Central Park Zoo Medan Tickets - Weekday', 'Includes: Exploring the entire zoo, discovering the museum, splashing in the water park and water boom, admiring the birds in the aviary, marveling at the beautiful fish in the aquarium and enjoying all regularly scheduled experiences. A valid entrance ticket is required for Zoo entrance for each visitor 2 years of age and older. Ticket valid on weekday within one year from date of purchase. No reservation required.', 40000),
(2, 'Central Park Zoo Medan Tickets - Weekend', 'Includes: Exploring the entire zoo, discovering the museum, splashing in the water park and water boom, admiring the birds in the aviary, marveling at the beautiful fish in the aquarium and enjoying all regularly scheduled experiences. A valid entrance ticket is required for Zoo entrance for each visitor 2 years of age and older. Ticket valid on weekend within one year from date of purchase. No reservation required.', 50000),
(3, 'Picnic Mat', 'Rental for picnic mat to enjoy your time in the zoo.', 25000),
(4, 'Small Gazebo', 'Rental for a small gazebo, perfect for small families or groups.', 70000),
(5, 'Large Gazebo', 'Rental for a large gazebo, great for big families or groups.', 100000);

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

--
-- Table structure for table `reservations`
--

CREATE TABLE `reservations` (
  `reservation_id` int(11) NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `phone_number` varchar(30) NOT NULL,
  `email` varchar(150) NOT NULL,
  `checkin_date` date NOT NULL,
  `checkout_date` date NOT NULL,
  `number_of_guest` int(11) NOT NULL,
  `total_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `reservations`
--

INSERT INTO `reservations` (`reservation_id`, `full_name`, `phone_number`, `email`, `checkin_date`, `checkout_date`, `number_of_guest`, `total_price`, `created_at`) VALUES
(1, 'Retno', '082385528181', 'excellniq@gmail.com', '2025-09-02', '2025-09-04', 4, 2400000.00, '2025-09-01 13:17:02'),
(2, 'Maha', '081062789287', 'maha@gmail.com', '2025-09-03', '2025-09-17', 1, 8800000.00, '2025-09-01 13:38:04'),
(3, 'Xila', '097865432358', 'xila@gmail.com', '2025-09-23', '2025-09-24', 4, 1200000.00, '2025-09-01 13:40:27'),
(4, 'Xila', '097865432358', 'xila@gmail.com', '2025-09-03', '2025-09-05', 1, 1200000.00, '2025-09-01 13:45:04'),
(5, 'Dara', '081062789287', 'dara@gmail.com', '2025-09-20', '2025-09-21', 3, 700000.00, '2025-09-01 13:50:45'),
(6, 'Dinar', '082384932134', 'dinar@gmail.com', '2025-09-03', '2025-09-11', 4, 5000000.00, '2025-09-02 16:07:13');

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

--
-- Table structure for table `reservation_rooms`
--

CREATE TABLE `reservation_rooms` (
  `id` int(11) NOT NULL,
  `reservation_id` int(11) NOT NULL,
  `room_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `reservation_rooms`
--

INSERT INTO `reservation_rooms` (`id`, `reservation_id`, `room_id`) VALUES
(1, 1, 1),
(2, 1, 10),
(3, 2, 2),
(4, 3, 8),
(5, 3, 12),
(6, 4, 6),
(7, 5, 3),
(8, 6, 7);

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

--
-- Table structure for table `reviews`
--

CREATE TABLE `reviews` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `rating` int(11) NOT NULL CHECK (`rating` between 1 and 5),
  `comment` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `reviews`
--

INSERT INTO `reviews` (`id`, `name`, `email`, `rating`, `comment`, `created_at`) VALUES
(1, 'Retno', 'excellniq@gmail.com', 5, 'Having a fun holiday with my family at Central Park Zoo Medan.', '2025-09-01 14:14:26'),
(2, 'Retno', 'excellniq@gmail.com', 5, 'Having a fun holiday with my family at Central Park Zoo Medan.', '2025-09-01 14:16:36'),
(3, 'Retno', 'excellniq@gmail.com', 5, 'Having a fun holiday with my family at Central Park Zoo Medan.', '2025-09-01 14:18:30'),
(4, 'Maha', 'maha@gmail.com', 4, 'Okay', '2025-09-02 16:41:49');

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

--
-- Table structure for table `rooms`
--

CREATE TABLE `rooms` (
  `room_id` int(11) NOT NULL,
  `room_type` varchar(100) NOT NULL,
  `room_number` int(11) NOT NULL,
  `status` enum('available','booked') NOT NULL DEFAULT 'available'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `rooms`
--

INSERT INTO `rooms` (`room_id`, `room_type`, `room_number`, `status`) VALUES
(1, 'Bamboo Cottage', 1, 'booked'),
(2, 'Bamboo Cottage', 2, 'booked'),
(3, 'Bamboo Cottage', 3, 'booked'),
(4, 'Bamboo Cottage', 4, 'available'),
(5, 'Bamboo Cottage', 5, 'available'),
(6, 'Bamboo Cottage', 6, 'booked'),
(7, 'Tropical Resort', 1, 'booked'),
(8, 'Tropical Resort', 2, 'booked'),
(9, 'Tropical Resort', 3, 'available'),
(10, 'Tropical Resort', 4, 'booked'),
(11, 'Tropical Resort', 5, 'available'),
(12, 'Tropical Resort', 6, 'booked');

--
-- Indexes for dumped tables
--

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

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

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `product_id` (`product_id`);

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

--
-- Indexes for table `reservations`
--
ALTER TABLE `reservations`
  ADD PRIMARY KEY (`reservation_id`);

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

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

--
-- Indexes for table `rooms`
--
ALTER TABLE `rooms`
  ADD PRIMARY KEY (`room_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `rooms`
--
ALTER TABLE `rooms`
  MODIFY `room_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `order_items`
--
ALTER TABLE `order_items`
  ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  ADD CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`);

--
-- Constraints for table `reservation_rooms`
--
ALTER TABLE `reservation_rooms`
  ADD CONSTRAINT `reservation_rooms_ibfk_1` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`reservation_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `reservation_rooms_ibfk_2` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`room_id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
