-- phpMyAdmin SQL Dump -- version 5.2.3 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Nov 06, 2026 at 09:36 AM -- Server version: 11.4.13-MariaDB-cll-lve -- PHP Version: 8.4.27 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: `fdwaksdk_AirDB` -- -- -------------------------------------------------------- -- -- Table structure for table `amenities` -- CREATE TABLE `amenities` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(100) NOT NULL, `icon` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `bookings` -- CREATE TABLE `bookings` ( `id` bigint(20) UNSIGNED NOT NULL, `listing_id` bigint(20) UNSIGNED NOT NULL, `guest_id` bigint(20) UNSIGNED NOT NULL, `check_in` date NOT NULL, `check_out` date NOT NULL, `guests` smallint(5) UNSIGNED NOT NULL, `total_price` decimal(10,2) NOT NULL, `status` enum('pending','confirmed','cancelled','completed') NOT NULL DEFAULT 'pending', `stripe_payment_id` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `listings` -- CREATE TABLE `listings` ( `id` bigint(20) UNSIGNED NOT NULL, `host_id` bigint(20) UNSIGNED NOT NULL, `title` varchar(255) NOT NULL, `description` text NOT NULL, `type` enum('apartment','house','room','villa','cabin') NOT NULL, `address` varchar(500) NOT NULL, `city` varchar(150) NOT NULL, `country` varchar(150) NOT NULL, `lat` decimal(10,7) DEFAULT NULL, `lng` decimal(10,7) DEFAULT NULL, `price_per_night` decimal(10,2) NOT NULL, `currency` char(3) NOT NULL DEFAULT 'EUR', `max_guests` smallint(5) UNSIGNED NOT NULL, `bedrooms` tinyint(3) UNSIGNED NOT NULL, `beds` tinyint(3) UNSIGNED NOT NULL, `bathrooms` tinyint(3) UNSIGNED NOT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `listing_amenity` -- CREATE TABLE `listing_amenity` ( `listing_id` bigint(20) UNSIGNED NOT NULL, `amenity_id` bigint(20) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `listing_images` -- CREATE TABLE `listing_images` ( `id` bigint(20) UNSIGNED NOT NULL, `listing_id` bigint(20) UNSIGNED NOT NULL, `url` varchar(500) NOT NULL, `sort_order` tinyint(3) UNSIGNED NOT NULL DEFAULT 0, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `messages` -- CREATE TABLE `messages` ( `id` bigint(20) UNSIGNED NOT NULL, `sender_id` bigint(20) UNSIGNED NOT NULL, `receiver_id` bigint(20) UNSIGNED NOT NULL, `booking_id` bigint(20) UNSIGNED DEFAULT NULL, `content` text NOT NULL, `read_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `reviews` -- CREATE TABLE `reviews` ( `id` bigint(20) UNSIGNED NOT NULL, `booking_id` bigint(20) UNSIGNED NOT NULL, `listing_id` bigint(20) UNSIGNED NOT NULL, `author_id` bigint(20) UNSIGNED NOT NULL, `rating` tinyint(3) UNSIGNED NOT NULL, `comment` text NOT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ; -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `password_hash` varchar(255) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `avatar_url` varchar(500) DEFAULT NULL, `role` enum('guest','host','admin') NOT NULL DEFAULT 'guest', `email_verified_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `wishlist_items` -- CREATE TABLE `wishlist_items` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `listing_id` bigint(20) UNSIGNED NOT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Indexes for dumped tables -- -- -- Indexes for table `amenities` -- ALTER TABLE `amenities` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`); -- -- Indexes for table `bookings` -- ALTER TABLE `bookings` ADD PRIMARY KEY (`id`), ADD KEY `idx_bookings_listing_dates` (`listing_id`,`check_in`,`check_out`), ADD KEY `idx_bookings_guest` (`guest_id`), ADD KEY `idx_bookings_status` (`status`); -- -- Indexes for table `listings` -- ALTER TABLE `listings` ADD PRIMARY KEY (`id`), ADD KEY `fk_listings_host` (`host_id`), ADD KEY `idx_listings_city` (`city`), ADD KEY `idx_listings_price` (`price_per_night`), ADD KEY `idx_listings_type` (`type`), ADD KEY `idx_listings_active` (`is_active`); -- -- Indexes for table `listing_amenity` -- ALTER TABLE `listing_amenity` ADD PRIMARY KEY (`listing_id`,`amenity_id`), ADD KEY `fk_la_amenity` (`amenity_id`); -- -- Indexes for table `listing_images` -- ALTER TABLE `listing_images` ADD PRIMARY KEY (`id`), ADD KEY `idx_images_listing` (`listing_id`,`sort_order`); -- -- Indexes for table `messages` -- ALTER TABLE `messages` ADD PRIMARY KEY (`id`), ADD KEY `fk_msg_receiver` (`receiver_id`), ADD KEY `fk_msg_booking` (`booking_id`), ADD KEY `idx_msg_conversation` (`sender_id`,`receiver_id`,`created_at`); -- -- Indexes for table `reviews` -- ALTER TABLE `reviews` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `booking_id` (`booking_id`), ADD KEY `fk_reviews_author` (`author_id`), ADD KEY `idx_reviews_listing` (`listing_id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `email` (`email`), ADD KEY `idx_users_role` (`role`); -- -- Indexes for table `wishlist_items` -- ALTER TABLE `wishlist_items` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uq_wish` (`user_id`,`listing_id`), ADD KEY `fk_wish_listing` (`listing_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `amenities` -- ALTER TABLE `amenities` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `bookings` -- ALTER TABLE `bookings` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `listings` -- ALTER TABLE `listings` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `listing_images` -- ALTER TABLE `listing_images` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `messages` -- ALTER TABLE `messages` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `reviews` -- ALTER TABLE `reviews` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `wishlist_items` -- ALTER TABLE `wishlist_items` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- Constraints for dumped tables -- -- -- Constraints for table `bookings` -- ALTER TABLE `bookings` ADD CONSTRAINT `fk_bookings_guest` FOREIGN KEY (`guest_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `fk_bookings_listing` FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`) ON DELETE CASCADE; -- -- Constraints for table `listings` -- ALTER TABLE `listings` ADD CONSTRAINT `fk_listings_host` FOREIGN KEY (`host_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `listing_amenity` -- ALTER TABLE `listing_amenity` ADD CONSTRAINT `fk_la_amenity` FOREIGN KEY (`amenity_id`) REFERENCES `amenities` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `fk_la_listing` FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`) ON DELETE CASCADE; -- -- Constraints for table `listing_images` -- ALTER TABLE `listing_images` ADD CONSTRAINT `fk_images_listing` FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`) ON DELETE CASCADE; -- -- Constraints for table `messages` -- ALTER TABLE `messages` ADD CONSTRAINT `fk_msg_booking` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE SET NULL, ADD CONSTRAINT `fk_msg_receiver` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `fk_msg_sender` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `reviews` -- ALTER TABLE `reviews` ADD CONSTRAINT `fk_reviews_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `fk_reviews_booking` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `fk_reviews_listing` FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`) ON DELETE CASCADE; -- -- Constraints for table `wishlist_items` -- ALTER TABLE `wishlist_items` ADD CONSTRAINT `fk_wish_listing` FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `fk_wish_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`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 */;