--
-- Host: localhost    Database: 360homesub
-- ------------------------------------------------------


--
-- Table structure for table `amenities`
--

CREATE TABLE `amenities` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `amenities`
--


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

CREATE TABLE `bookings` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `property_id` bigint(20) NOT NULL,
  `guest_id` bigint(20) NOT NULL,
  `host_id` bigint(20) NOT NULL,
  `check_in` date NOT NULL,
  `check_out` date NOT NULL,
  `nights` int(11) NOT NULL,
  `adults` int(11) DEFAULT 1,
  `children` int(11) DEFAULT 0,
  `rooms` int(11) DEFAULT 1,
  `rent_amount` decimal(10,2) DEFAULT NULL,
  `caution_fee` decimal(10,2) DEFAULT NULL,
  `service_fee` decimal(10,2) DEFAULT NULL,
  `tax_amount` decimal(10,2) DEFAULT NULL,
  `total_amount` decimal(10,2) DEFAULT NULL,
  `status` enum('pending','approved','rejected','paid') DEFAULT 'pending',
  `rejection_reason` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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


--
-- Table structure for table `kyc`
--

CREATE TABLE `kyc` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `country` varchar(100) NOT NULL,
  `identity_type` enum('passport','national_id','drivers_license') NOT NULL,
  `id_front` varchar(255) NOT NULL,
  `id_back` varchar(255) NOT NULL,
  `selfie` varchar(255) NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `admin_note` text DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `kyc_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `kyc`
--


--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sender_id` bigint(20) NOT NULL,
  `receiver_id` bigint(20) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `sender_id` (`sender_id`),
  KEY `receiver_id` (`receiver_id`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `messages`
--

INSERT INTO `messages` VALUES (1,2,1,'Hi John, welcome! Let me know if you have any questions about the apartment.',0,'2026-01-27 11:27:49'),(2,1,2,'Thanks Jane! It looks great. I might have a question about parking later.',1,'2026-01-27 11:27:49'),(3,2,1,'No problem, parking is included. Enjoy your stay!',0,'2026-01-27 11:27:49'),(4,2,1,'Just wanted to check if you have settled in well.',0,'2026-01-27 11:27:49'),(5,9,3,'tyujkl\n',0,'2026-02-02 12:16:14');

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

CREATE TABLE `notifications` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `message` varchar(255) NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `notifications` VALUES (1,1,'Your booking for \"Luxury Downtown Apartment\" has been confirmed.',0,'2026-01-27 11:27:49'),(2,1,'A new property \"Modern Studio Loft\" is available near you.',0,'2026-01-27 11:27:49'),(3,1,'Welcome to 360 HomeHub! Complete your profile to get started.',1,'2026-01-27 11:27:49'),(4,1,'Your password was changed successfully.',1,'2026-01-27 11:27:49'),(5,1,'You have a new message from Jane Smith.',0,'2026-01-27 11:27:49'),(6,3,'tyhujkl',0,'2026-02-02 12:16:10'),(7,1,'test notifications',0,'2026-02-02 12:18:34'),(8,2,'test notifications',0,'2026-02-02 12:18:34'),(9,3,'test notifications',0,'2026-02-02 12:18:34'),(10,4,'test notifications',0,'2026-02-02 12:18:34'),(11,6,'test notifications',0,'2026-02-02 12:18:34'),(12,8,'test notifications',0,'2026-02-02 12:18:34'),(13,9,'test notifications',0,'2026-02-02 12:18:34'),(14,13,'.dmdd\n',0,'2026-02-02 13:05:03');

--
-- Table structure for table `otps`
--

CREATE TABLE `otps` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `code` varchar(6) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `otps_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `otps`
--

INSERT INTO `otps` VALUES (2,6,'042458','2026-01-27 12:53:38',0),(3,8,'606322','2026-01-27 12:53:46',0),(4,9,'781961','2026-01-27 13:02:28',1);

--
-- Table structure for table `properties`
--

CREATE TABLE `properties` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `host_id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` enum('apartment','house','studio','duplex','hotel') NOT NULL,
  `space_type` enum('whole','private','shared') NOT NULL,
  `guests_max` int(11) NOT NULL DEFAULT 1,
  `price` decimal(10,2) NOT NULL,
  `price_type` enum('night','week','month') DEFAULT 'night',
  `bedrooms` int(11) NOT NULL,
  `beds` int(11) NOT NULL DEFAULT 1,
  `bathrooms` int(11) NOT NULL,
  `area` int(11) DEFAULT NULL,
  `booking_type` enum('instant','request') DEFAULT 'request',
  `free_cancellation` tinyint(1) DEFAULT 0,
  `amenities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`amenities`)),
  `address` varchar(255) NOT NULL,
  `apartment_suite` varchar(100) DEFAULT NULL,
  `city` varchar(100) NOT NULL,
  `state` varchar(100) NOT NULL,
  `zip_code` varchar(20) NOT NULL,
  `country` varchar(100) NOT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `onboarding_step` int(11) DEFAULT 1,
  `status` enum('draft','published','archived') DEFAULT 'draft',
  `check_in_time` time DEFAULT NULL,
  `check_out_time` time DEFAULT NULL,
  `pets_allowed` tinyint(1) DEFAULT 0,
  `age_restriction` tinyint(1) DEFAULT 0,
  `smoking_allowed` tinyint(1) DEFAULT 0,
  `extra_rules` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`extra_rules`)),
  `cancellation_policy` enum('flexible','moderate','strict') DEFAULT 'flexible',
  `availability_type` enum('default','manual') DEFAULT 'default',
  `instant_booking` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `host_id` (`host_id`),
  CONSTRAINT `properties_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `properties`
--

INSERT INTO `properties` VALUES (1,2,'Luxury Downtown Apartment','A beautiful apartment in the heart of the city, offering stunning views and modern amenities.','apartment','whole',1,250.00,'night',2,1,2,NULL,'instant',1,'[\"wifi\", \"pool\", \"gym\", \"ac\"]','123 Main St',NULL,'New York','NY','10001','USA',40.71380000,-74.00700000,'2026-01-27 11:27:49',1,'published',NULL,NULL,0,0,0,NULL,'flexible','default',0),(2,2,'Cozy Suburban House','A charming house in a quiet suburban neighborhood, perfect for families.','house','whole',1,350.00,'night',4,1,3,NULL,'request',1,'[\"wifi\", \"kitchen\", \"parking\", \"garden\"]','456 Oak Ave',NULL,'White Plains','NY','10601','USA',41.03390000,-73.76290000,'2026-01-27 11:27:49',1,'draft',NULL,NULL,0,0,0,NULL,'flexible','default',0),(3,4,'Modern Studio Loft','A stylish and compact studio in a trendy arts district.','studio','whole',1,150.00,'night',1,1,1,NULL,'instant',0,'[\"wifi\", \"ac\", \"kitchenette\"]','789 Art St',NULL,'Brooklyn','NY','11201','USA',40.69250000,-73.99050000,'2026-01-27 11:27:49',1,'draft',NULL,NULL,0,0,0,NULL,'flexible','default',0),(4,4,'Spacious Duplex with Rooftop','A large duplex with a private rooftop terrace, great for entertaining.','duplex','whole',1,450.00,'night',3,1,2,NULL,'request',1,'[\"wifi\", \"pool\", \"rooftop\", \"ac\"]','101 Sky High Rd',NULL,'New York','NY','10002','USA',40.71450000,-73.99850000,'2026-01-27 11:27:49',1,'draft',NULL,NULL,0,0,0,NULL,'flexible','default',0),(5,2,'Boutique Hotel Room','A private room in a centrally located boutique hotel.','hotel','whole',1,180.00,'night',1,1,1,NULL,'instant',0,'[\"wifi\", \"room_service\", \"gym\"]','212 Central Ave',NULL,'New York','NY','10003','USA',40.73080000,-73.99730000,'2026-01-27 11:27:49',1,'draft',NULL,NULL,0,0,0,NULL,'flexible','default',0);

--
-- Table structure for table `property_amenities`
--

CREATE TABLE `property_amenities` (
  `property_id` bigint(20) DEFAULT NULL,
  `amenity_id` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `property_amenities`
--


--
-- Table structure for table `property_availability`
--

CREATE TABLE `property_availability` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `property_id` bigint(20) NOT NULL,
  `date` date NOT NULL,
  `is_available` tinyint(1) DEFAULT 1,
  `price_override` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `property_id` (`property_id`),
  CONSTRAINT `property_availability_ibfk_1` FOREIGN KEY (`property_id`) REFERENCES `properties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `property_availability`
--


--
-- Table structure for table `property_images`
--

CREATE TABLE `property_images` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `property_id` bigint(20) NOT NULL,
  `media_url` varchar(255) NOT NULL,
  `media_type` enum('image','video') DEFAULT 'image',
  `description` varchar(255) DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `property_id` (`property_id`),
  CONSTRAINT `property_images_ibfk_1` FOREIGN KEY (`property_id`) REFERENCES `properties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `property_images`
--

INSERT INTO `property_images` VALUES (1,1,'https://source.unsplash.com/random/800x600?apartment,interior','image',NULL,'2026-01-27 11:27:49'),(2,1,'https://source.unsplash.com/random/800x600?apartment,livingroom','image',NULL,'2026-01-27 11:27:49'),(3,2,'https://source.unsplash.com/random/800x600?house,exterior','image',NULL,'2026-01-27 11:27:49'),(4,2,'https://source.unsplash.com/random/800x600?house,kitchen','image',NULL,'2026-01-27 11:27:49'),(5,3,'https://source.unsplash.com/random/800x600?studio,loft','image',NULL,'2026-01-27 11:27:49'),(6,4,'https://source.unsplash.com/random/800x600?duplex,rooftop','image',NULL,'2026-01-27 11:27:49'),(7,5,'https://source.unsplash.com/random/800x600?hotel,room','image',NULL,'2026-01-27 11:27:49');

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `booking_id` bigint(20) DEFAULT NULL,
  `reference` varchar(255) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `gateway` enum('paystack','flutterwave') NOT NULL,
  `status` enum('pending','success','failed') DEFAULT 'pending',
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `transactions`
--



