--
-- Host: localhost    Database: user119
-- ------------------------------------------------------


--
-- Table structure for table `countries`
--

CREATE TABLE `countries` (
  `country_id` int unsigned NOT NULL AUTO_INCREMENT,
  `country_name` varchar(50) NOT NULL,
  `visa_required` tinyint(1) DEFAULT '0',
  `description` text,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `countries`
--

INSERT INTO `countries` VALUES (1,'Турция',0,'Популярное направление для пляжного отдыха'),(2,'Италия',1,'Страна с богатой историей и культурой'),(3,'Египет',1,'Отличные отели на Красном море');

--
-- Table structure for table `resorts`
--

CREATE TABLE `resorts` (
  `resort_id` int unsigned NOT NULL AUTO_INCREMENT,
  `country_id` int unsigned NOT NULL,
  `resort_name` varchar(50) NOT NULL,
  `climate` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`resort_id`),
  KEY `country_id` (`country_id`),
  CONSTRAINT `resorts_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `resorts`
--

INSERT INTO `resorts` VALUES (1,1,'Анталия','Средиземноморский'),(2,1,'Бодрум','Средиземноморский'),(3,2,'Римини','Умеренный'),(4,3,'Хургада','Пустынный'),(5,2,'yaru','teplo'),(6,1,'Тур','норм'),(7,1,'тур2','норм2');

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `role_id` int unsigned NOT NULL AUTO_INCREMENT,
  `role_name` varchar(20) NOT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` VALUES (1,'guest'),(2,'user'),(3,'admin');

--
-- Table structure for table `tours`
--

CREATE TABLE `tours` (
  `tour_id` int unsigned NOT NULL AUTO_INCREMENT,
  `resort_id` int unsigned NOT NULL,
  `departure_date` date NOT NULL,
  `return_date` date NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `available_seats` int unsigned NOT NULL,
  `hotel_name` varchar(100) DEFAULT NULL,
  `hotel_stars` tinyint unsigned DEFAULT NULL,
  PRIMARY KEY (`tour_id`),
  KEY `resort_id` (`resort_id`),
  CONSTRAINT `tours_ibfk_1` FOREIGN KEY (`resort_id`) REFERENCES `resorts` (`resort_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tours`
--

INSERT INTO `tours` VALUES (1,1,'2023-06-15','2023-06-29',45000.00,10,'Sunrise Resort',4),(2,1,'2023-07-01','2023-07-15',52000.00,15,'Sea View Hotel',5),(4,3,'2023-07-10','2023-07-24',68000.00,12,'Adriatic Hotel',3),(7,3,'2025-04-04','2025-04-27',1200.00,2,'Sunrise Resort',5);


